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(167, now(), 'Work In Progress'); | values(168, 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 245 Lines • ▼ Show 20 Lines | |||||
comment on table revision_history is 'Sequence of revision history with parent and position in history'; | comment on table revision_history is 'Sequence of revision history with parent and position in history'; | ||||
comment on column revision_history.id is 'Revision history git object sha1 checksum'; | comment on column revision_history.id is 'Revision history git object sha1 checksum'; | ||||
comment on column revision_history.parent_id is 'Parent revision git object identifier'; | comment on column revision_history.parent_id is 'Parent revision git object identifier'; | ||||
comment on column revision_history.parent_rank is 'Parent position in merge commits, 0-based'; | comment on column revision_history.parent_rank is 'Parent position in merge commits, 0-based'; | ||||
-- Crawling history of software origins visited by Software Heritage. Each | -- Crawling history of software origins visited by Software Heritage. Each | ||||
-- visit is a 3-way mapping between a software origin, a timestamp, and a | -- visit is a 3-way mapping between a software origin, a timestamp, and a | ||||
-- snapshot object capturing the full-state of the origin at visit time. | -- snapshot object capturing the full-state of the origin at visit time. | ||||
create table origin_visit | create table origin_visit | ||||
olasd: We'll want to add an index (unique) on these columns. | |||||
( | ( | ||||
origin bigint not null, | origin bigint not null, | ||||
visit bigint not null, | visit bigint not null, | ||||
date timestamptz not null, | date timestamptz not null, | ||||
type text not null | type text not null | ||||
); | ); | ||||
comment on column origin_visit.origin is 'Visited origin'; | comment on column origin_visit.origin is 'Visited origin'; | ||||
▲ Show 20 Lines • Show All 202 Lines • ▼ Show 20 Lines | |||||
comment on column object_counts_bucketed.line is 'Auto incremented idenitfier value'; | comment on column object_counts_bucketed.line is 'Auto incremented idenitfier value'; | ||||
comment on column object_counts_bucketed.object_type is 'Object type (''content'', ''directory'', ''revision'', | comment on column object_counts_bucketed.object_type is 'Object type (''content'', ''directory'', ''revision'', | ||||
''release'', ''snapshot'')'; | ''release'', ''snapshot'')'; | ||||
comment on column object_counts_bucketed.identifier is 'Common identifier for bucketed objects'; | comment on column object_counts_bucketed.identifier is 'Common identifier for bucketed objects'; | ||||
comment on column object_counts_bucketed.bucket_start is 'Lower bound (inclusive) for the bucket'; | comment on column object_counts_bucketed.bucket_start is 'Lower bound (inclusive) for the bucket'; | ||||
comment on column object_counts_bucketed.bucket_end is 'Upper bound (exclusive) for the bucket'; | comment on column object_counts_bucketed.bucket_end is 'Upper bound (exclusive) for the bucket'; | ||||
comment on column object_counts_bucketed.value is 'Count of objects in the bucket'; | comment on column object_counts_bucketed.value is 'Count of objects in the bucket'; | ||||
comment on column object_counts_bucketed.last_update is 'Last update for the object count in this bucket'; | comment on column object_counts_bucketed.last_update is 'Last update for the object count in this bucket'; | ||||
-- The ExtID (typ. original VCS) <-> swhid relation table | |||||
create table extid | |||||
( | |||||
extid_type text not null, | |||||
extid bytea not null, | |||||
target_type object_type not null, | |||||
target sha1_git not null | |||||
); | |||||
comment on table extid is 'Correspondance SWH object (SWHID) <-> original revision id (vcs id)'; | |||||
comment on column extid.extid_type is 'ExtID type'; | |||||
comment on column extid.extid is 'Intrinsic identifier of the object (e.g. hg revision)'; | |||||
comment on column extid.target_type is 'Type of SWHID of the referenced SWH object'; | |||||
comment on column extid.target is 'Value (hash) of SWHID of the refenced SWH object'; |
We'll want to add an index (unique) on these columns.