Changeset View
Changeset View
Standalone View
Standalone View
sql/swh-schema.sql
--- | --- | ||||
--- SQL implementation of the Software Heritage data model | --- SQL implementation of the Software Heritage data model | ||||
--- | --- | ||||
-- schema versions | -- schema versions | ||||
create table dbversion | create table dbversion | ||||
( | ( | ||||
version int primary key, | version int primary key, | ||||
release timestamptz, | release timestamptz, | ||||
description text | description text | ||||
); | ); | ||||
-- latest schema version | -- latest schema version | ||||
insert into dbversion(version, release, description) | insert into dbversion(version, release, description) | ||||
values(122, now(), 'Work In Progress'); | values(123, 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 308 Lines • ▼ Show 20 Lines | ( | ||||
target sha1_git not null, -- ref target, e.g., commit id | target sha1_git not null, -- ref target, e.g., commit id | ||||
target_type object_type not null, -- ref target type | target_type object_type not null, -- ref target type | ||||
visits bigint[] not null, -- the visits where that occurrence was valid. References | visits bigint[] not null, -- the visits where that occurrence was valid. References | ||||
-- origin_visit(visit), where o_h.origin = origin_visit.origin. | -- origin_visit(visit), where o_h.origin = origin_visit.origin. | ||||
object_id bigserial not null, -- short object identifier | object_id bigserial not null, -- short object identifier | ||||
snapshot_branch_id bigint | snapshot_branch_id bigint | ||||
); | ); | ||||
-- Materialized view of occurrence_history, storing the *current* value of each | |||||
-- branch, as last seen by SWH. | |||||
create table occurrence | |||||
( | |||||
origin bigint, | |||||
branch bytea not null, | |||||
target sha1_git not null, | |||||
target_type object_type not null | |||||
); | |||||
-- END legacy section (T830) | -- END legacy section (T830) | ||||
-- 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 | ||||
▲ Show 20 Lines • Show All 116 Lines • Show Last 20 Lines |