Differential D2879 Diff 10298 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(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