Differential D4986 Diff 17931 swh/storage/sql/30-schema.sql

Changeset View

Standalone View

swh/storage/sql/30-schema.sql

	Show All 30 Lines
	-- a blake2 checksum			-- a blake2 checksum
	create domain blake2s256 as bytea check (length(value) = 32);			create domain blake2s256 as bytea check (length(value) = 32);

	-- UNIX path (absolute, relative, individual path component, etc.)			-- UNIX path (absolute, relative, individual path component, etc.)
	create domain unix_path as bytea;			create domain unix_path as bytea;

	-- a set of UNIX-like access permissions, as manipulated by, e.g., chmod			-- a set of UNIX-like access permissions, as manipulated by, e.g., chmod
	create domain file_perms as int;			create domain file_perms as int;

	-- an SWHID			-- a SWHID
				olasdUnsubmitted Not Done Inline Actions for any pronounciation that I can think of, this should probably be "a SWHID" :-) olasd: for any pronounciation that I can think of, this should probably be "a SWHID" :-)
	create domain swhid as text check (value ~ '^swh:[0-9]+:.*');			create type swhid as (version int, object_type swhid_type, object_id bytea);


	-- Checksums about actual file content. Note that the content itself is not			-- Checksums about actual file content. Note that the content itself is not
	-- stored in the DB, but on external (key-value) storage. A single checksum is			-- stored in the DB, but on external (key-value) storage. A single checksum is
	-- used as key there, but the other can be used to verify that we do not inject			-- used as key there, but the other can be used to verify that we do not inject
	-- content collisions not knowingly.			-- content collisions not knowingly.
	create table content			create table content
	(			(
	▲ Show 20 Lines • Show All 375 Lines • ▼ Show 20 Lines
	comment on column metadata_authority.type is 'Type of authority (deposit_client/forge/registry)';			comment on column metadata_authority.type is 'Type of authority (deposit_client/forge/registry)';
	comment on column metadata_authority.url is 'Authority''s uri';			comment on column metadata_authority.url is 'Authority''s uri';
	comment on column metadata_authority.metadata is 'Other metadata about authority';			comment on column metadata_authority.metadata is 'Other metadata about authority';


	-- Extrinsic metadata on a DAG objects and origins.			-- Extrinsic metadata on a DAG objects and origins.
	create table raw_extrinsic_metadata			create table raw_extrinsic_metadata
	(			(
	type text not null,			target swhid not null,
	target text not null,

	-- metadata source			-- metadata source
	authority_id bigint not null,			authority_id bigint not null,
	fetcher_id bigint not null,			fetcher_id bigint not null,
	discovery_date timestamptz not null,			discovery_date timestamptz not null,

	-- metadata itself			-- metadata itself
	format text not null,			format text not null,
	metadata bytea not null,			metadata bytea not null,

	-- context			-- context
	origin text,			origin swhid,
	visit bigint,			visit bigint,
	snapshot swhid,			snapshot swhid,
	release swhid,			release swhid,
	revision swhid,			revision swhid,
	path bytea,			path bytea,
	directory swhid			directory swhid
	);			);

	comment on table raw_extrinsic_metadata is 'keeps all metadata found concerning an object';			comment on table raw_extrinsic_metadata is 'keeps all metadata found concerning an object';
	comment on column raw_extrinsic_metadata.type is 'the type of object (content/directory/revision/release/snapshot/origin) the metadata is on';
	comment on column raw_extrinsic_metadata.target is 'the SWHID or origin URL for which the metadata was found';			comment on column raw_extrinsic_metadata.target is 'the SWHID or origin URL for which the metadata was found';
	comment on column raw_extrinsic_metadata.discovery_date is 'the date of retrieval';			comment on column raw_extrinsic_metadata.discovery_date is 'the date of retrieval';
	comment on column raw_extrinsic_metadata.authority_id is 'the metadata provider: github, openhub, deposit, etc.';			comment on column raw_extrinsic_metadata.authority_id is 'the metadata provider: github, openhub, deposit, etc.';
	comment on column raw_extrinsic_metadata.fetcher_id is 'the tool used for extracting metadata: loaders, crawlers, etc.';			comment on column raw_extrinsic_metadata.fetcher_id is 'the tool used for extracting metadata: loaders, crawlers, etc.';
	comment on column raw_extrinsic_metadata.format is 'name of the format of metadata, used by readers to interpret it.';			comment on column raw_extrinsic_metadata.format is 'name of the format of metadata, used by readers to interpret it.';
	comment on column raw_extrinsic_metadata.metadata is 'original metadata in opaque format';			comment on column raw_extrinsic_metadata.metadata is 'original metadata in opaque format';


	Show All 37 Lines