Changeset View
Changeset View
Standalone View
Standalone View
swh/storage/sql/30-swh-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(146, now(), 'Work In Progress'); | values(147, 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 248 Lines • ▼ Show 20 Lines | |||||
-- 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 | ||||
( | ( | ||||
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, | ||||
-- remove those when done migrating the schema | |||||
status origin_visit_status not null, | status origin_visit_status not null, | ||||
metadata jsonb, | metadata jsonb, | ||||
snapshot sha1_git | snapshot sha1_git | ||||
); | ); | ||||
comment on column origin_visit.origin is 'Visited origin'; | comment on column origin_visit.origin is 'Visited origin'; | ||||
comment on column origin_visit.visit is 'Sequential visit number for the origin'; | comment on column origin_visit.visit is 'Sequential visit number for the origin'; | ||||
comment on column origin_visit.date is 'Visit timestamp'; | comment on column origin_visit.date is 'Visit timestamp'; | ||||
comment on column origin_visit.type is 'Type of loader that did the visit (hg, git, ...)'; | comment on column origin_visit.type is 'Type of loader that did the visit (hg, git, ...)'; | ||||
comment on column origin_visit.status is 'Visit result'; | comment on column origin_visit.status is 'Visit result'; | ||||
comment on column origin_visit.metadata is 'Origin metadata at visit time'; | comment on column origin_visit.metadata is 'Origin metadata at visit time'; | ||||
comment on column origin_visit.snapshot is 'Origin snapshot at visit time'; | comment on column origin_visit.snapshot is 'Origin snapshot at visit time'; | ||||
-- Crawling history of software origin visits by Software Heritage. Each | |||||
-- visit see its history change through new origin visit updates | |||||
create table origin_visit_update | |||||
( | |||||
id bigserial not null, -- TODO: Decide if we keep that or not | |||||
origin bigint not null, | |||||
visit bigint not null, | |||||
date timestamptz not null, | |||||
status origin_visit_status not null, | |||||
metadata jsonb, | |||||
snapshot sha1_git | |||||
); | |||||
comment on column origin_visit_update.id is 'visit update id'; | |||||
comment on column origin_visit_update.origin is 'origin concerned by the visit update'; | |||||
comment on column origin_visit_update.visit is 'visit concerned by the visit update'; | |||||
comment on column origin_visit_update.date is 'Visit update timestamp'; | |||||
comment on column origin_visit_update.status is 'Visit update status'; | |||||
comment on column origin_visit_update.metadata is 'Origin metadata at visit update time'; | |||||
comment on column origin_visit_update.snapshot is 'Origin snapshot at visit update time'; | |||||
-- A snapshot represents the entire state of a software origin as crawled by | -- A snapshot represents the entire state of a software origin as crawled by | ||||
-- Software Heritage. This table is a simple mapping between (public) intrinsic | -- Software Heritage. This table is a simple mapping between (public) intrinsic | ||||
-- snapshot identifiers and (private) numeric sequential identifiers. | -- snapshot identifiers and (private) numeric sequential identifiers. | ||||
create table snapshot | create table snapshot | ||||
( | ( | ||||
object_id bigserial not null, -- PK internal object identifier | object_id bigserial not null, -- PK internal object identifier | ||||
id sha1_git not null -- snapshot intrinsic identifier | id sha1_git not null -- snapshot intrinsic identifier | ||||
); | ); | ||||
▲ Show 20 Lines • Show All 162 Lines • Show Last 20 Lines |