Differential D23 Diff 74 sql/swh-schema.sql

Changeset 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(68, now(), 'Work In Progress');		values(69, 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 395 Lines • ▼ Show 20 Lines	(
name bytea,		name bytea,
comment bytea,		comment bytea,
author bigint references person(id),		author bigint references person(id),
synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar)		synthetic boolean not null default false, -- true if synthetic (cf. swh-loader-tar)
object_id bigserial		object_id bigserial
);		);

create index on release(target, target_type);		create index on release(target, target_type);


		-- In order to archive the content of the object storage, add
		-- some tables to keep trace of what have already been archived.

		CREATE DOMAIN archive_id AS TEXT;

		CREATE TABLE archives (
		id archive_id PRIMARY KEY,
		url TEXT
		);

		CREATE TYPE archive_status AS ENUM (
		'missing',
		'ongoing',
		'present'
		);

		CREATE TABLE content_archive (
		content_id sha1 REFERENCES content(sha1),
		archive_id archive_id REFERENCES archives(id),
		status archive_status,
		mtime timestamptz,
		PRIMARY KEY (content_id, archive_id)
		);