Changeset View
Changeset View
Standalone View
Standalone View
sql/swh-schema.sql
--- | --- | ||||
--- Software Heritage Data Model | --- Software Heritage Data Model | ||||
--- | --- | ||||
-- drop schema if exists swh cascade; | -- drop schema if exists swh cascade; | ||||
-- create schema swh; | -- create schema swh; | ||||
-- set search_path to swh; | -- set search_path to swh; | ||||
create table dbversion | create table dbversion | ||||
( | ( | ||||
version int primary key, | version int primary key, | ||||
release timestamptz, | release timestamptz, | ||||
description text | description text | ||||
); | ); | ||||
insert into dbversion(version, release, description) | insert into dbversion(version, release, description) | ||||
values(74, now(), 'Work In Progress'); | values(75, now(), 'Work In Progress'); | ||||
-- a SHA1 checksum (not necessarily originating from Git) | -- a SHA1 checksum (not necessarily originating from Git) | ||||
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 SHA1 checksum | -- a Git object ID, i.e., a 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 421 Lines • ▼ Show 20 Lines | ( | ||||
parent_id sha1_git, | parent_id sha1_git, | ||||
parent_rank int not null default 0, | parent_rank int not null default 0, | ||||
-- parent position in merge commits, 0-based | -- parent position in merge commits, 0-based | ||||
primary key (id, parent_rank) | primary key (id, parent_rank) | ||||
); | ); | ||||
create index on revision_history(parent_id); | create index on revision_history(parent_id); | ||||
create type origin_visit_status as enum ( | |||||
'ongoing', | |||||
'full', | |||||
'partial' | |||||
); | |||||
comment on type origin_visit_status IS 'Possible visit status'; | |||||
-- The timestamps at which Software Heritage has made a visit of the given origin. | -- The timestamps at which Software Heritage has made a visit of the given origin. | ||||
create table origin_visit | create table origin_visit | ||||
( | ( | ||||
origin bigint not null references origin(id), | origin bigint not null references origin(id), | ||||
visit bigint not null, | visit bigint not null, | ||||
date timestamptz not null, | date timestamptz not null, | ||||
status origin_visit_status not null, | |||||
primary key (origin, visit) | primary key (origin, visit) | ||||
); | ); | ||||
comment on column origin_visit.origin is 'Visited origin'; | |||||
comment on column origin_visit.visit is 'Visit number the visit occurred for that origin'; | |||||
comment on column origin_visit.date is 'Visit date for that origin'; | |||||
comment on column origin_visit.status is 'Visit status for that origin'; | |||||
create index on origin_visit(date); | create index on origin_visit(date); | ||||
-- Asynchronous notification of new origin visits | -- Asynchronous notification of new origin visits | ||||
create function notify_new_origin_visit() | create function notify_new_origin_visit() | ||||
returns trigger | returns trigger | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
begin | begin | ||||
▲ Show 20 Lines • Show All 86 Lines • Show Last 20 Lines |