Differential D6712 Diff 24717 swh/provenance/sql/30-schema.sql

Changeset View

Standalone View

swh/provenance/sql/30-schema.sql

	Show All 10 Lines

	comment on table dbversion is 'Details of current db version';			comment on table dbversion is 'Details of current db version';
	comment on column dbversion.version is 'SQL schema version';			comment on column dbversion.version is 'SQL schema version';
	comment on column dbversion.release is 'Version deployment timestamp';			comment on column dbversion.release is 'Version deployment timestamp';
	comment on column dbversion.description is 'Release description';			comment on column dbversion.description is 'Release description';

	-- latest schema version			-- latest schema version
	insert into dbversion(version, release, description)			insert into dbversion(version, release, description)
	values(1, now(), 'Work In Progress');			values(2, now(), 'Work In Progress');

	-- a Git object ID, i.e., a Git-style salted SHA1 checksum			-- a Git object ID, i.e., a Git-style salted SHA1 checksum
	create domain sha1_git as bytea check (length(value) = 20);			create domain sha1_git as bytea check (length(value) = 20);

	-- UNIX path (absolute, relative, individual path component, etc.)			-- UNIX path (absolute, relative, individual path component, etc.)
	create domain unix_path as bytea;			create domain unix_path as bytea;

	-- relation filter options for querying			-- relation filter options for querying
	create type rel_flt as enum (			create type rel_flt as enum (
	'filter-src',			'filter-src',
	'filter-dst',			'filter-dst',
	'no-filter'			'no-filter'
	);			);
	comment on type rel_flt is 'Relation get filter types';			comment on type rel_flt is 'Relation get filter types';

	-- entity tables			-- entity tables
	create table content			create table content
	(			(
	id bigserial primary key, -- internal identifier of the content blob			id bigserial primary key, -- internal identifier of the content blob
	sha1 sha1_git unique not null, -- intrinsic identifier of the content blob			sha1 sha1_git unique not null, -- intrinsic identifier of the content blob
	date timestamptz -- timestamp of the revision where the blob appears early			date timestamptz not null -- timestamp of the revision where the blob appears early
	);			);
	comment on column content.id is 'Content internal identifier';			comment on column content.id is 'Content internal identifier';
	comment on column content.sha1 is 'Content intrinsic identifier';			comment on column content.sha1 is 'Content intrinsic identifier';
	comment on column content.date is 'Earliest timestamp for the content (first seen time)';			comment on column content.date is 'Earliest timestamp for the content (first seen time)';

	create table directory			create table directory
	(			(
	id bigserial primary key, -- internal identifier of the directory appearing in an isochrone inner frontier			id bigserial primary key, -- internal identifier of the directory appearing in an isochrone inner frontier
	sha1 sha1_git unique not null, -- intrinsic identifier of the directory			sha1 sha1_git unique not null, -- intrinsic identifier of the directory
	date timestamptz -- max timestamp among those of the directory children's			date timestamptz not null, -- max timestamp among those of the directory children's
				flat boolean not null default false -- flag acknowledging if the directory is flattenned in the model
	);			);
	comment on column directory.id is 'Directory internal identifier';			comment on column directory.id is 'Directory internal identifier';
	comment on column directory.sha1 is 'Directory intrinsic identifier';			comment on column directory.sha1 is 'Directory intrinsic identifier';
	comment on column directory.date is 'Latest timestamp for the content in the directory';			comment on column directory.date is 'Latest timestamp for the content in the directory';

	create table revision			create table revision
	(			(
	id bigserial primary key, -- internal identifier of the revision			id bigserial primary key, -- internal identifier of the revision
	sha1 sha1_git unique not null, -- intrinsic identifier of the revision			sha1 sha1_git unique not null, -- intrinsic identifier of the revision
	date timestamptz, -- timestamp of the revision			date timestamptz, -- timestamp of the revision
	origin bigint -- id of the preferred origin			origin bigint -- id of the preferred origin
	-- foreign key (origin) references origin (id)			-- foreign key (origin) references origin (id)
	);			);
	comment on column revision.id is 'Revision internal identifier';			comment on column revision.id is 'Revision internal identifier';
	comment on column revision.sha1 is 'Revision intrinsic identifier';			comment on column revision.sha1 is 'Revision intrinsic identifier';
	comment on column revision.date is 'Revision timestamp';			comment on column revision.date is 'Revision timestamp';
	comment on column revision.origin is 'preferred origin for the revision';			comment on column revision.origin is 'preferred origin for the revision';

	create table location			create table location
	(			(
	id bigserial primary key, -- internal identifier of the location			id bigserial primary key, -- internal identifier of the location
	path unix_path unique not null -- path to the location			path unix_path unique not null -- path to the location
	);			);
	comment on column location.id is 'Location internal identifier';			comment on column location.id is 'Location internal identifier';
	comment on column location.path is 'Path to the location';			comment on column location.path is 'Path to the location';

	create table origin			create table origin
	(			(
	id bigserial primary key, -- internal identifier of the origin			id bigserial primary key, -- internal identifier of the origin
	sha1 sha1_git unique not null, -- intrinsic identifier of the origin			sha1 sha1_git unique not null, -- intrinsic identifier of the origin
	url text unique not null -- url of the origin			url text unique not null -- url of the origin
	);			);
	comment on column origin.id is 'Origin internal identifier';			comment on column origin.id is 'Origin internal identifier';
	comment on column origin.sha1 is 'Origin intrinsic identifier';			comment on column origin.sha1 is 'Origin intrinsic identifier';
	comment on column origin.url is 'URL of the origin';			comment on column origin.url is 'URL of the origin';

	-- relation tables			-- relation tables
	create table content_in_revision			create table content_in_revision
	(			(
	content bigint not null, -- internal identifier of the content blob			content bigint not null, -- internal identifier of the content blob
	\if :dbflavor_norm			\if :dbflavor_norm
	revision bigint not null, -- internal identifier of the revision where the blob appears for the first time			revision bigint not null, -- internal identifier of the revision where the blob appears for the first time
	location bigint -- location of the content relative to the revision's root directory			location bigint -- location of the content relative to the revision's root directory
	\else			\else
	revision bigint[], -- internal identifiers of the revisions where the blob appears for the first time			revision bigint[], -- internal identifiers of the revisions where the blob appears for the first time
	location bigint[] -- locations of the content relative to the revisions' root directory			location bigint[] -- locations of the content relative to the revisions' root directory
	\endif			\endif
	-- foreign key (content) references content (id),			-- foreign key (content) references content (id),
	-- foreign key (revision) references revision (id),			-- foreign key (revision) references revision (id),
	-- foreign key (location) references location (id)			-- foreign key (location) references location (id)
	);			);
	comment on column content_in_revision.content is 'Content internal identifier';			comment on column content_in_revision.content is 'Content internal identifier';
	\if :dbflavor_norm			\if :dbflavor_norm
	comment on column content_in_revision.revision is 'Revision internal identifier';			comment on column content_in_revision.revision is 'Revision internal identifier';
	comment on column content_in_revision.location is 'Location of content in revision';			comment on column content_in_revision.location is 'Location of content in revision';
	\else			\else
	comment on column content_in_revision.revision is 'Revision/location internal identifiers';			comment on column content_in_revision.revision is 'Revision/location internal identifiers';
	\endif			\endif

	create table content_in_directory			create table content_in_directory
	(			(
	content bigint not null, -- internal identifier of the content blob			content bigint not null, -- internal identifier of the content blob
	\if :dbflavor_norm			\if :dbflavor_norm
	directory bigint not null, -- internal identifier of the directory containing the blob			directory bigint not null, -- internal identifier of the directory containing the blob
	location bigint -- location of the content relative to its parent directory in the isochrone frontier			location bigint -- location of the content relative to its parent directory in the isochrone frontier
	\else			\else
	directory bigint[], -- internal reference of the directories containing the blob			directory bigint[], -- internal reference of the directories containing the blob
	location bigint[] -- locations of the content relative to its parent directories in the isochrone frontier			location bigint[] -- locations of the content relative to its parent directories in the isochrone frontier
	\endif			\endif
	-- foreign key (content) references content (id),			-- foreign key (content) references content (id),
	-- foreign key (directory) references directory (id),			-- foreign key (directory) references directory (id),
	-- foreign key (location) references location (id)			-- foreign key (location) references location (id)
	);			);
	comment on column content_in_directory.content is 'Content internal identifier';			comment on column content_in_directory.content is 'Content internal identifier';
	\if :dbflavor_norm			\if :dbflavor_norm
	comment on column content_in_directory.directory is 'Directory internal identifier';			comment on column content_in_directory.directory is 'Directory internal identifier';
	comment on column content_in_directory.location is 'Location of content in directory';			comment on column content_in_directory.location is 'Location of content in directory';
	\else			\else
	comment on column content_in_directory.directory is 'Directory/location internal identifiers';			comment on column content_in_directory.directory is 'Directory/location internal identifiers';
	\endif			\endif

	create table directory_in_revision			create table directory_in_revision
	(			(
	directory bigint not null, -- internal identifier of the directory appearing in the revision			directory bigint not null, -- internal identifier of the directory appearing in the revision
	\if :dbflavor_norm			\if :dbflavor_norm
	revision bigint not null, -- internal identifier of the revision containing the directory			revision bigint not null, -- internal identifier of the revision containing the directory
	location bigint -- location of the directory relative to the revision's root directory			location bigint -- location of the directory relative to the revision's root directory
	\else			\else
	revision bigint[], -- internal identifiers of the revisions containing the directory			revision bigint[], -- internal identifiers of the revisions containing the directory
	location bigint[] -- locations of the directory relative to the revisions' root directory			location bigint[] -- locations of the directory relative to the revisions' root directory
	\endif			\endif
	-- foreign key (directory) references directory (id),			-- foreign key (directory) references directory (id),
	-- foreign key (revision) references revision (id),			-- foreign key (revision) references revision (id),
	-- foreign key (location) references location (id)			-- foreign key (location) references location (id)
	);			);
	comment on column directory_in_revision.directory is 'Directory internal identifier';			comment on column directory_in_revision.directory is 'Directory internal identifier';
	\if :dbflavor_norm			\if :dbflavor_norm
	comment on column directory_in_revision.revision is 'Revision internal identifier';			comment on column directory_in_revision.revision is 'Revision internal identifier';
	comment on column directory_in_revision.location is 'Location of content in revision';			comment on column directory_in_revision.location is 'Location of content in revision';
	\else			\else
	comment on column directory_in_revision.revision is 'Revision/location internal identifiers';			comment on column directory_in_revision.revision is 'Revision/location internal identifiers';
	\endif			\endif

	create table revision_in_origin			create table revision_in_origin
	(			(
	revision bigint not null, -- internal identifier of the revision poined by the origin			revision bigint not null, -- internal identifier of the revision poined by the origin
	origin bigint not null -- internal identifier of the origin that points to the revision			origin bigint not null -- internal identifier of the origin that points to the revision
	-- foreign key (revision) references revision (id),			-- foreign key (revision) references revision (id),
	-- foreign key (origin) references origin (id)			-- foreign key (origin) references origin (id)
	);			);
	comment on column revision_in_origin.revision is 'Revision internal identifier';			comment on column revision_in_origin.revision is 'Revision internal identifier';
	comment on column revision_in_origin.origin is 'Origin internal identifier';			comment on column revision_in_origin.origin is 'Origin internal identifier';

	create table revision_before_revision			create table revision_before_revision
	(			(
	prev bigserial not null, -- internal identifier of the source revision			prev bigserial not null, -- internal identifier of the source revision
	next bigserial not null -- internal identifier of the destination revision			next bigserial not null -- internal identifier of the destination revision
	-- foreign key (prev) references revision (id),			-- foreign key (prev) references revision (id),
	-- foreign key (next) references revision (id)			-- foreign key (next) references revision (id)
	);			);
	comment on column revision_before_revision.prev is 'Source revision internal identifier';			comment on column revision_before_revision.prev is 'Source revision internal identifier';
	comment on column revision_before_revision.next is 'Destination revision internal identifier';			comment on column revision_before_revision.next is 'Destination revision internal identifier';