Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/sql/30-schema.sql
| Show All 11 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(178, now(), 'Work In Progress'); | values(180, now(), 'Work In Progress'); | ||||
| -- a SHA1 checksum | -- a SHA1 checksum | ||||
| create domain sha1 as bytea check (length(value) = 20); | create domain sha1 as bytea check (length(value) = 20); | ||||
| -- 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); | ||||
| -- a SHA256 checksum | -- a SHA256 checksum | ||||
| ▲ Show 20 Lines • Show All 103 Lines • ▼ Show 20 Lines | |||||
| -- Synonyms/mappings: | -- Synonyms/mappings: | ||||
| -- * git: tree | -- * git: tree | ||||
| create table directory | create table directory | ||||
| ( | ( | ||||
| id sha1_git not null, | id sha1_git not null, | ||||
| dir_entries bigint[], -- sub-directories, reference directory_entry_dir | dir_entries bigint[], -- sub-directories, reference directory_entry_dir | ||||
| file_entries bigint[], -- contained files, reference directory_entry_file | file_entries bigint[], -- contained files, reference directory_entry_file | ||||
| rev_entries bigint[], -- mounted revisions, reference directory_entry_rev | rev_entries bigint[], -- mounted revisions, reference directory_entry_rev | ||||
| object_id bigserial -- short object identifier | object_id bigserial, -- short object identifier | ||||
| raw_manifest bytea -- git manifest of the object, if it cannot be represented using only the other fields | |||||
| ); | ); | ||||
| comment on table directory is 'Contents of a directory, synonymous to tree (git)'; | comment on table directory is 'Contents of a directory, synonymous to tree (git)'; | ||||
| comment on column directory.id is 'Git object sha1 hash'; | comment on column directory.id is 'Git object sha1 hash'; | ||||
| comment on column directory.dir_entries is 'Sub-directories, reference directory_entry_dir'; | comment on column directory.dir_entries is 'Sub-directories, reference directory_entry_dir'; | ||||
| comment on column directory.file_entries is 'Contained files, reference directory_entry_file'; | comment on column directory.file_entries is 'Contained files, reference directory_entry_file'; | ||||
| comment on column directory.rev_entries is 'Mounted revisions, reference directory_entry_rev'; | comment on column directory.rev_entries is 'Mounted revisions, reference directory_entry_rev'; | ||||
| comment on column directory.object_id is 'Short object identifier'; | comment on column directory.object_id is 'Short object identifier'; | ||||
| comment on column directory.raw_manifest is 'git manifest of the object, if it cannot be represented using only the other fields'; | |||||
| -- A directory entry pointing to a (sub-)directory. | -- A directory entry pointing to a (sub-)directory. | ||||
| create table directory_entry_dir | create table directory_entry_dir | ||||
| ( | ( | ||||
| id bigserial, | id bigserial, | ||||
| target sha1_git not null, -- id of target directory | target sha1_git not null, -- id of target directory | ||||
| name unix_path not null, -- path name, relative to containing dir | name unix_path not null, -- path name, relative to containing dir | ||||
| ▲ Show 20 Lines • Show All 65 Lines • ▼ Show 20 Lines | |||||
| -- | -- | ||||
| -- Revisions are organized as DAGs. Each revision points to 0, 1, or more (in | -- Revisions are organized as DAGs. Each revision points to 0, 1, or more (in | ||||
| -- case of merges) parent revisions. Each revision points to a directory, i.e., | -- case of merges) parent revisions. Each revision points to a directory, i.e., | ||||
| -- a file-system tree containing files and directories. | -- a file-system tree containing files and directories. | ||||
| create table revision | create table revision | ||||
| ( | ( | ||||
| id sha1_git not null, | id sha1_git not null, | ||||
| date timestamptz, | date timestamptz, | ||||
| date_offset smallint, | date_offset smallint, | ||||
| committer_date timestamptz, | committer_date timestamptz, | ||||
| committer_date_offset smallint, | committer_date_offset smallint, | ||||
| type revision_type not null, | type revision_type not null, | ||||
olasd: Please move these fields at the end of the table, so schemas are consistent between updated… | |||||
| directory sha1_git, -- source code 'root' directory | directory sha1_git, -- source code 'root' directory | ||||
| message bytea, | message bytea, | ||||
| author bigint, | author bigint, | ||||
| committer bigint, | committer bigint, | ||||
| synthetic boolean not null default false, -- true iff revision has been created by Software Heritage | synthetic boolean not null default false, -- true iff revision has been created by Software Heritage | ||||
| metadata jsonb, -- extra metadata (tarball checksums, extra commit information, etc...) | metadata jsonb, -- extra metadata (tarball checksums, extra commit information, etc...) | ||||
| object_id bigserial, | object_id bigserial, | ||||
| date_neg_utc_offset boolean, | date_neg_utc_offset boolean, | ||||
| committer_date_neg_utc_offset boolean, | committer_date_neg_utc_offset boolean, | ||||
| extra_headers bytea[][] not null -- extra headers (used in hash computation) | extra_headers bytea[][] not null, -- extra headers (used in hash computation) | ||||
| date_offset_bytes bytea, | |||||
| committer_date_offset_bytes bytea, | |||||
| raw_manifest bytea -- git manifest of the object, if it cannot be represented using only the other fields | |||||
| ); | ); | ||||
| comment on table revision is 'A revision represents the state of a source code tree at a specific point in time'; | comment on table revision is 'A revision represents the state of a source code tree at a specific point in time'; | ||||
| comment on column revision.id is 'Git-style SHA1 commit identifier'; | comment on column revision.id is 'Git-style SHA1 commit identifier'; | ||||
| comment on column revision.date is 'Author timestamp as UNIX epoch'; | comment on column revision.date is 'Author timestamp as UNIX epoch'; | ||||
| comment on column revision.date_offset is 'Author timestamp timezone, as minute offsets from UTC'; | comment on column revision.date_offset is 'Author timestamp timezone, as minute offsets from UTC'; | ||||
| comment on column revision.date_neg_utc_offset is 'True indicates a -0 UTC offset on author timestamp'; | comment on column revision.date_neg_utc_offset is 'True indicates a -0 UTC offset on author timestamp'; | ||||
| comment on column revision.committer_date is 'Committer timestamp as UNIX epoch'; | comment on column revision.committer_date is 'Committer timestamp as UNIX epoch'; | ||||
| comment on column revision.committer_date_offset is 'Committer timestamp timezone, as minute offsets from UTC'; | comment on column revision.committer_date_offset is 'Committer timestamp timezone, as minute offsets from UTC'; | ||||
| comment on column revision.committer_date_neg_utc_offset is 'True indicates a -0 UTC offset on committer timestamp'; | comment on column revision.committer_date_neg_utc_offset is 'True indicates a -0 UTC offset on committer timestamp'; | ||||
| comment on column revision.type is 'Type of revision'; | comment on column revision.type is 'Type of revision'; | ||||
| comment on column revision.directory is 'Directory identifier'; | comment on column revision.directory is 'Directory identifier'; | ||||
| comment on column revision.message is 'Commit message'; | comment on column revision.message is 'Commit message'; | ||||
| comment on column revision.author is 'Author identity'; | comment on column revision.author is 'Author identity'; | ||||
| comment on column revision.committer is 'Committer identity'; | comment on column revision.committer is 'Committer identity'; | ||||
| comment on column revision.synthetic is 'True iff revision has been synthesized by Software Heritage'; | comment on column revision.synthetic is 'True iff revision has been synthesized by Software Heritage'; | ||||
| comment on column revision.metadata is 'Extra revision metadata'; | comment on column revision.metadata is 'Extra revision metadata'; | ||||
| comment on column revision.object_id is 'Non-intrinsic, sequential object identifier'; | comment on column revision.object_id is 'Non-intrinsic, sequential object identifier'; | ||||
| comment on column revision.extra_headers is 'Extra revision headers; used in revision hash computation'; | comment on column revision.extra_headers is 'Extra revision headers; used in revision hash computation'; | ||||
| comment on column revision.date_offset_bytes is 'Raw git representation of the timezone, as an offset from UTC. It should follow this format: ``+HHMM`` or ``-HHMM``'; | |||||
| comment on column revision.committer_date_offset_bytes is 'Raw git representation of the timezone, as an offset from UTC. It should follow this format: ``+HHMM`` or ``-HHMM``'; | |||||
| comment on column revision.raw_manifest is 'git manifest of the object, if it cannot be represented using only the other fields'; | |||||
| -- either this table or the sha1_git[] column on the revision table | -- either this table or the sha1_git[] column on the revision table | ||||
| create table revision_history | create table revision_history | ||||
| ( | ( | ||||
| id sha1_git not null, | id sha1_git not null, | ||||
| parent_id sha1_git not null, | parent_id sha1_git not null, | ||||
| parent_rank int not null default 0 | parent_rank int not null default 0 | ||||
| ▲ Show 20 Lines • Show All 102 Lines • ▼ Show 20 Lines | ( | ||||
| date timestamptz, | date timestamptz, | ||||
| date_offset smallint, | date_offset smallint, | ||||
| name bytea, | name bytea, | ||||
| comment bytea, | comment bytea, | ||||
| author bigint, | author bigint, | ||||
| synthetic boolean not null default false, -- true iff release has been created by Software Heritage | synthetic boolean not null default false, -- true iff release has been created by Software Heritage | ||||
| object_id bigserial, | object_id bigserial, | ||||
| target_type object_type not null, | target_type object_type not null, | ||||
| date_neg_utc_offset boolean | date_neg_utc_offset boolean, | ||||
| date_offset_bytes bytea, | |||||
| raw_manifest bytea | |||||
| ); | ); | ||||
| comment on table release is 'Details of a software release, synonymous with | comment on table release is 'Details of a software release, synonymous with | ||||
| a tag (git) or version number (tarball)'; | a tag (git) or version number (tarball)'; | ||||
| comment on column release.id is 'Release git identifier'; | comment on column release.id is 'Release git identifier'; | ||||
| comment on column release.target is 'Target git identifier'; | comment on column release.target is 'Target git identifier'; | ||||
| comment on column release.date is 'Release timestamp'; | comment on column release.date is 'Release timestamp'; | ||||
| comment on column release.date_offset is 'Timestamp offset from UTC'; | comment on column release.date_offset is 'Timestamp offset from UTC'; | ||||
| comment on column release.name is 'Name'; | comment on column release.name is 'Name'; | ||||
| comment on column release.comment is 'Comment'; | comment on column release.comment is 'Comment'; | ||||
| comment on column release.author is 'Author'; | comment on column release.author is 'Author'; | ||||
| comment on column release.synthetic is 'Indicates if created by Software Heritage'; | comment on column release.synthetic is 'Indicates if created by Software Heritage'; | ||||
| comment on column release.object_id is 'Object identifier'; | comment on column release.object_id is 'Object identifier'; | ||||
| comment on column release.target_type is 'Object type (''content'', ''directory'', ''revision'', | comment on column release.target_type is 'Object type (''content'', ''directory'', ''revision'', | ||||
| ''release'', ''snapshot'')'; | ''release'', ''snapshot'')'; | ||||
| comment on column release.date_neg_utc_offset is 'True indicates -0 UTC offset for release timestamp'; | comment on column release.date_neg_utc_offset is 'True indicates -0 UTC offset for release timestamp'; | ||||
| comment on column release.date_offset_bytes is 'Raw git representation of the timezone, as an offset from UTC. It should follow this format: ``+HHMM`` or ``-HHMM``'; | |||||
| comment on column release.raw_manifest is 'git manifest of the object, if it cannot be represented using only the other fields'; | |||||
| -- Tools | -- Tools | ||||
| create table metadata_fetcher | create table metadata_fetcher | ||||
| ( | ( | ||||
| id serial not null, | id serial not null, | ||||
| name text not null, | name text not null, | ||||
| version text not null | version text not null | ||||
| ); | ); | ||||
| ▲ Show 20 Lines • Show All 112 Lines • Show Last 20 Lines | |||||
Please move these fields at the end of the table, so schemas are consistent between updated databases and ones created from scratch. (It makes no functional difference, but it's sometimes useful to be able to diff pg schema dumps)