Differential D3342 Diff 11892 swh/storage/sql/30-swh-schema.sql

Changeset 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(154, now(), 'Work In Progress');			values(155, 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 246 Lines • ▼ Show 20 Lines
	-- 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
	(			(
	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_state not null,
	metadata jsonb,
	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 '(Deprecated) Visit status';
	comment on column origin_visit.metadata is '(Deprecated) Optional origin visit metadata';
	comment on column origin_visit.snapshot is '(Deprecated) Optional snapshot of the origin visit. It can be partial.';


	-- Crawling history of software origin visits by Software Heritage. Each			-- Crawling history of software origin visits by Software Heritage. Each
	-- visit see its history change through new origin visit status updates			-- visit see its history change through new origin visit status updates
	create table origin_visit_status			create table origin_visit_status
	(			(
	origin bigint not null,			origin bigint not null,
	visit bigint not null,			visit bigint not null,
	▲ Show 20 Lines • Show All 185 Lines • Show Last 20 Lines