Differential D2938 Diff 10933 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,
	status origin_visit_status not null,			-- remove those when done migrating the schema
				status origin_visit_state not null,
	metadata jsonb,			metadata jsonb,
				vlorentzUnsubmitted Not Done Inline Actions ?? vlorentz: ??
				ardumontAuthorUnsubmitted Done Inline Actions i don't get the question ;) the enum got renamed to origin_visit_state? ardumont: i don't get the question ;) the enum got renamed to origin_visit_state?
				vlorentzUnsubmitted Not Done Inline Actions But why do we still have this field on `origin_visit`? vlorentz: But why do we still have this field on `origin_visit`?
				ardumontAuthorUnsubmitted Done Inline Actions ah ok, because that's our initial work plan [1] [1] we initially said we'd remove it when we are done migrating T2310 (i can't link to the "work plan" paragraph :/) ardumont: ah ok, because that's our initial work plan [1] [1] we initially said we'd remove it when we…
				vlorentzUnsubmitted Not Done Inline Actions hmm, yeah, ok vlorentz: hmm, yeah, ok
	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 '(Deprecated) Visit status';
	comment on column origin_visit.metadata is 'Origin metadata at visit time';			comment on column origin_visit.metadata is '(Deprecated) Optional origin visit metadata';
	comment on column origin_visit.snapshot is 'Origin snapshot at visit time';			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
				-- visit see its history change through new origin visit status updates
				create table origin_visit_status
				(
				origin bigint not null,
				visit bigint not null,
				date timestamptz not null,
				status origin_visit_state not null,
				metadata jsonb,
				snapshot sha1_git
				);

				comment on column origin_visit_status.origin is 'Origin concerned by the visit update';
				comment on column origin_visit_status.visit is 'Visit concerned by the visit update';
				comment on column origin_visit_status.date is 'Visit update timestamp';
				comment on column origin_visit_status.status is 'Visit status (ongoing, failed, full)';
				comment on column origin_visit_status.metadata is 'Optional origin visit metadata';
				comment on column origin_visit_status.snapshot is 'Optional, possibly partial, snapshot of the origin visit. It can be partial.';


	-- 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 164 Lines • Show Last 20 Lines