Differential D6473 Diff 23513 swh/provenance/sql/30-schema.sql

Changeset View

Standalone View

swh/provenance/sql/30-schema.sql

Show All 18 Lines	insert into dbversion(version, release, description)
values(1, now(), 'Work In Progress');		values(1, 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;

-- a relation destination ID (used for denormalized flavors: with-path vs. without-path)
\if :dbflavor_with_path
create type rel_dst as (id bigint, loc bigint);
\else
create domain rel_dst as bigint;
\endif

-- 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';

▲ Show 20 Lines • Show All 50 Lines • ▼ Show 20 Lines
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 root directory		location bigint -- location of the content relative to the revision's root directory
\else		\else
revision rel_dst[] -- internal reference of the revision (and location) 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
\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 rel_dst[] -- internal reference of the directory (and location) 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
\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 root directory		location bigint -- location of the directory relative to the revision's root directory
\else		\else
revision rel_dst[] -- internal reference of the revision (and location) 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
\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';
Show All 24 Lines