Changeset View
Changeset View
Standalone View
Standalone View
swh/provenance/sql/30-schema.sql
| -- psql variables to get the current database flavor | -- psql variables to get the current database flavor | ||||
| select swh_get_dbflavor() = 'with-path' as dbflavor_with_path \gset | |||||
| create table dbversion | create table dbversion | ||||
| ( | ( | ||||
| version int primary key, | version int primary key, | ||||
| release timestamptz, | release timestamptz, | ||||
| description text | description text | ||||
| ); | ); | ||||
| Show All 20 Lines | |||||
| ); | ); | ||||
| 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 content_early_in_rev | create table content_early_in_rev | ||||
| ( | ( | ||||
| blob bigint not null, -- internal identifier of the content blob | blob bigint not null, -- internal identifier of the content blob | ||||
| rev bigint not null -- internal identifier of the revision where the blob appears for the first time | rev bigint not null, -- internal identifier of the revision where the blob appears for the first time | ||||
| \if :dbflavor_with_path | |||||
| , | |||||
| loc bigint not null -- location of the content relative to the revision root directory | loc bigint not null -- location of the content relative to the revision root directory | ||||
| \endif | |||||
| -- 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_early_in_rev.blob is 'Content internal identifier'; | comment on column content_early_in_rev.blob is 'Content internal identifier'; | ||||
| comment on column content_early_in_rev.rev is 'Revision internal identifier'; | comment on column content_early_in_rev.rev is 'Revision internal identifier'; | ||||
| \if :dbflavor_with_path | |||||
| comment on column content_early_in_rev.loc is 'Location of content in revision'; | comment on column content_early_in_rev.loc is 'Location of content in revision'; | ||||
| \endif | |||||
| create table content_in_dir | create table content_in_dir | ||||
| ( | ( | ||||
| blob bigint not null, -- internal identifier of the content blob | blob bigint not null, -- internal identifier of the content blob | ||||
| dir bigint not null -- internal identifier of the directory containing the blob | dir bigint not null, -- internal identifier of the directory containing the blob | ||||
| \if :dbflavor_with_path | |||||
| , | |||||
| loc bigint not null -- location of the content relative to its parent directory in the isochrone frontier | loc bigint not null -- location of the content relative to its parent directory in the isochrone frontier | ||||
| \endif | |||||
| -- 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_dir.blob is 'Content internal identifier'; | comment on column content_in_dir.blob is 'Content internal identifier'; | ||||
| comment on column content_in_dir.dir is 'Directory internal identifier'; | comment on column content_in_dir.dir is 'Directory internal identifier'; | ||||
| \if :dbflavor_with_path | |||||
| comment on column content_in_dir.loc is 'Location of content in directory'; | comment on column content_in_dir.loc is 'Location of content in directory'; | ||||
| \endif | |||||
| 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 not null -- 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 directory_in_rev | create table directory_in_rev | ||||
| ( | ( | ||||
| dir bigint not null, -- internal identifier of the directory appearing in the revision | dir bigint not null, -- internal identifier of the directory appearing in the revision | ||||
| rev bigint not null -- internal identifier of the revision containing the directory | rev bigint not null, -- internal identifier of the revision containing the directory | ||||
| \if :dbflavor_with_path | |||||
| , | |||||
| loc bigint not null -- location of the directory relative to the revision root directory | loc bigint not null -- location of the directory relative to the revision root directory | ||||
| \endif | |||||
| -- 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_rev.dir is 'Directory internal identifier'; | comment on column directory_in_rev.dir is 'Directory internal identifier'; | ||||
| comment on column directory_in_rev.rev is 'Revision internal identifier'; | comment on column directory_in_rev.rev is 'Revision internal identifier'; | ||||
| \if :dbflavor_with_path | |||||
| comment on column directory_in_rev.loc is 'Location of directory in revision'; | comment on column directory_in_rev.loc is 'Location of directory in revision'; | ||||
| \endif | |||||
| create table origin | create table origin | ||||
| ( | ( | ||||
| id bigserial primary key, -- internal identifier of the origin | id bigserial primary key, -- internal identifier of the origin | ||||
| url unix_path unique not null -- url of the origin | url unix_path 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.url is 'URL of the origin'; | comment on column origin.url is 'URL of the origin'; | ||||
| Show All 28 Lines | ( | ||||
| org bigint not null, -- internal identifier of the origin that points to the revision | org bigint not null, -- internal identifier of the origin that points to the revision | ||||
| primary key (rev, org) | primary key (rev, org) | ||||
| -- 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_org.rev is 'Revision internal identifier'; | comment on column revision_in_org.rev is 'Revision internal identifier'; | ||||
| comment on column revision_in_org.org is 'Origin internal identifier'; | comment on column revision_in_org.org is 'Origin internal identifier'; | ||||
| \if :dbflavor_with_path | |||||
| 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'; | ||||
| \endif | |||||