Changeset View
Changeset View
Standalone View
Standalone View
swh/provenance/sql/30-schema.sql
| Show All 21 Lines | |||||
| -- 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; | ||||
| -- 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 not null -- timestamp of the revision where the blob appears early | date timestamptz -- 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 not null -- max timestamp among those of the directory children's | date timestamptz -- max timestamp among those of the directory children's | ||||
| ); | ); | ||||
| 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 | ||||
| Show All 23 Lines | |||||
| ); | ); | ||||
| 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 | ||||
| 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 root directory | ||||
| -- foreign key (blob) references content (id), | -- foreign key (blob) references content (id), | ||||
| -- foreign key (rev) references revision (id), | -- foreign key (rev) references revision (id), | ||||
| -- foreign key (loc) references location (id) | -- foreign key (loc) 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'; | ||||
| 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'; | ||||
| 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 | ||||
| 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 | ||||
| -- foreign key (blob) references content (id), | -- foreign key (blob) references content (id), | ||||
| -- foreign key (dir) references directory (id), | -- foreign key (dir) references directory (id), | ||||
| -- foreign key (loc) references location (id) | -- foreign key (loc) 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'; | ||||
| 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'; | ||||
| 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 | ||||
| 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 root directory | ||||
| -- foreign key (dir) references directory (id), | -- foreign key (dir) references directory (id), | ||||
| -- foreign key (rev) references revision (id), | -- foreign key (rev) references revision (id), | ||||
| -- foreign key (loc) references location (id) | -- foreign key (loc) 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'; | ||||
| 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 directory in revision'; | comment on column directory_in_revision.location is 'Location of directory in revision'; | ||||
| 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 (rev) references revision (id), | -- foreign key (rev) references revision (id), | ||||
| -- foreign key (org) references origin (id) | -- foreign key (org) 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'; | ||||