swh-archiver-schema.sql
No OneTemporary
Actions

Size

1 KB

Subscribers

None

swh-archiver-schema.sql
View Options

	-- In order to archive the content of the object storage, add
	-- some tables to keep trace of what have already been archived.

	create table dbversion
	(
	version int primary key,
	release timestamptz,
	description text
	);

	comment on table dbversion is 'Schema update tracking';

	INSERT INTO dbversion(version, release, description)
	VALUES(5, now(), 'Work In Progress');

	CREATE TABLE archive (
	id text PRIMARY KEY
	);

	comment on table archive is 'Possible archives';
	comment on column archive.id is 'Short identifier for the archive';

	CREATE TYPE archive_status AS ENUM (
	'missing',
	'ongoing',
	'present',
	'corrupted'
	);

	comment on type archive_status is 'Status of a given archive';

	-- a SHA1 checksum (not necessarily originating from Git)
	CREATE DOMAIN sha1 AS bytea CHECK (LENGTH(VALUE) = 20);

	CREATE TABLE content_archive (
	content_id sha1 primary key,
	copies jsonb,
	num_present int default null
	);

	create index on content_archive(num_present);

	comment on table content_archive is 'Referencing the status and whereabouts of a content';
	comment on column content_archive.content_id is 'content identifier';
	comment on column content_archive.copies is 'map archive_id -> { "status": archive_status, "mtime": epoch timestamp }';
	comment on column content_archive.num_present is 'Number of copies marked as present (cache updated via trigger)';

	-- Keep the num_copies cache updated
	CREATE FUNCTION update_num_present() RETURNS TRIGGER AS $$
	BEGIN
	NEW.num_present := (select count(*) from jsonb_each(NEW.copies) where value->>'status' = 'present');
	RETURN new;
	END;
	$$ LANGUAGE PLPGSQL;

	CREATE TRIGGER update_num_present
	BEFORE INSERT OR UPDATE OF copies ON content_archive
	FOR EACH ROW
	EXECUTE PROCEDURE update_num_present();

File Metadata

Mime Type: text/plain
Expires: Thu, Jul 3, 11:48 AM (3 d, 8 h ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3267578

swh-archiver-schema.sqlNo OneTemporaryActions

swh-archiver-schema.sqlView Options

File Metadata

Event Timeline

swh-archiver-schema.sql
No OneTemporary
Actions

swh-archiver-schema.sql
View Options