Changeset View
Changeset View
Standalone 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 |