Differential D1010 Diff 3244 swh/indexer/sql/30-swh-schema.sql

Changeset View

Standalone View

swh/indexer/sql/30-swh-schema.sql

	---			---
	--- Software Heritage Indexers Data Model			--- Software Heritage Indexers 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(118, now(), 'Work In Progress');			values(119, now(), 'Work In Progress');
	-- Computing metadata on sha1's contents			-- Computing metadata on sha1's contents

	-- 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);

	▲ Show 20 Lines • Show All 87 Lines • ▼ Show 20 Lines
	comment on column content_metadata.indexer_configuration_id is 'tool used for translation';			comment on column content_metadata.indexer_configuration_id is 'tool used for translation';

	-- The table revision_metadata provides a minimal set of intrinsic metadata			-- The table revision_metadata provides a minimal set of intrinsic metadata
	-- detected with the detection tool (indexer_configuration_id) and aggregated			-- detected with the detection tool (indexer_configuration_id) and aggregated
	-- from the content_metadata translation.			-- from the content_metadata translation.
	create table revision_metadata(			create table revision_metadata(
	id sha1_git not null,			id sha1_git not null,
	translated_metadata jsonb not null,			translated_metadata jsonb not null,
	indexer_configuration_id bigint not null			indexer_configuration_id bigint not null,
				mappings text array not null
	);			);

	comment on table revision_metadata is 'metadata semantically detected and translated in a revision';			comment on table revision_metadata is 'metadata semantically detected and translated in a revision';
	comment on column revision_metadata.id is 'sha1_git of revision';			comment on column revision_metadata.id is 'sha1_git of revision';
	comment on column revision_metadata.translated_metadata is 'result of detection and translation with defined format';			comment on column revision_metadata.translated_metadata is 'result of detection and translation with defined format';
	comment on column revision_metadata.indexer_configuration_id is 'tool used for detection';			comment on column revision_metadata.indexer_configuration_id is 'tool used for detection';

	create table origin_intrinsic_metadata(			create table origin_intrinsic_metadata(
	origin_id bigserial not null,			origin_id bigserial not null,
	metadata jsonb,			metadata jsonb,
	indexer_configuration_id bigint not null,			indexer_configuration_id bigint not null,
	from_revision sha1_git not null,			from_revision sha1_git not null,
	metadata_tsvector tsvector			metadata_tsvector tsvector,
				ardumontUnsubmitted Done Inline Actions please, update the schema version and add the migration script that alters the schema. ardumont: please, update the schema version and add the migration script that alters the schema.
				mappings text array not null
	);			);

	comment on table origin_intrinsic_metadata is 'keeps intrinsic metadata for an origin';			comment on table origin_intrinsic_metadata is 'keeps intrinsic metadata for an origin';
	comment on column origin_intrinsic_metadata.origin_id is 'the entry id in origin';			comment on column origin_intrinsic_metadata.origin_id is 'the entry id in origin';
	comment on column origin_intrinsic_metadata.metadata is 'metadata extracted from a revision';			comment on column origin_intrinsic_metadata.metadata is 'metadata extracted from a revision';
	comment on column origin_intrinsic_metadata.indexer_configuration_id is 'tool used to generate this metadata';			comment on column origin_intrinsic_metadata.indexer_configuration_id is 'tool used to generate this metadata';
	comment on column origin_intrinsic_metadata.from_revision is 'sha1 of the revision this metadata was copied from.';			comment on column origin_intrinsic_metadata.from_revision is 'sha1 of the revision this metadata was copied from.';