Changeset View
Changeset View
Standalone 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(148, 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 343 Lines • ▼ Show 20 Lines | |||||
| comment on column release.author is 'Author'; | comment on column release.author is 'Author'; | ||||
| comment on column release.synthetic is 'Indicates if created by Software Heritage'; | comment on column release.synthetic is 'Indicates if created by Software Heritage'; | ||||
| comment on column release.object_id is 'Object identifier'; | comment on column release.object_id is 'Object identifier'; | ||||
| comment on column release.target_type is 'Object type (''content'', ''directory'', ''revision'', | comment on column release.target_type is 'Object type (''content'', ''directory'', ''revision'', | ||||
| ''release'', ''snapshot'')'; | ''release'', ''snapshot'')'; | ||||
| comment on column release.date_neg_utc_offset is 'True indicates -0 UTC offset for release timestamp'; | comment on column release.date_neg_utc_offset is 'True indicates -0 UTC offset for release timestamp'; | ||||
| -- Tools | -- Tools | ||||
| create table tool | create table metadata_fetcher | ||||
| ( | ( | ||||
| id serial not null, | id serial not null, | ||||
| name text not null, | name text not null, | ||||
| version text not null, | version text not null, | ||||
| configuration jsonb | metadata jsonb not null | ||||
| ); | ); | ||||
| comment on table tool is 'Tool information'; | comment on table metadata_fetcher is 'Tools used to retrieve metadata'; | ||||
| comment on column tool.id is 'Tool identifier'; | comment on column metadata_fetcher.id is 'Internal identifier of the fetcher'; | ||||
| comment on column tool.version is 'Tool name'; | comment on column metadata_fetcher.name is 'Fetcher name'; | ||||
| comment on column tool.version is 'Tool version'; | comment on column metadata_fetcher.version is 'Fetcher version'; | ||||
| comment on column tool.configuration is 'Tool configuration: command line, flags, etc...'; | comment on column metadata_fetcher.metadata is 'Extra information about the fetcher'; | ||||
| create table metadata_provider | create table metadata_authority | ||||
| ( | ( | ||||
| id serial not null, | id serial not null, | ||||
| provider_name text not null, | type text not null, | ||||
| provider_type text not null, | url text not null, | ||||
| provider_url text, | metadata jsonb not null | ||||
| metadata jsonb | |||||
| ); | ); | ||||
| comment on table metadata_provider is 'Metadata provider information'; | comment on table metadata_authority is 'Metadata authority information'; | ||||
| comment on column metadata_provider.id is 'Provider''s identifier'; | comment on column metadata_authority.id is 'Internal identifier of the authority'; | ||||
| comment on column metadata_provider.provider_name is 'Provider''s name'; | comment on column metadata_authority.type is 'Type of authority (deposit/forge/registry)'; | ||||
| comment on column metadata_provider.provider_url is 'Provider''s url'; | comment on column metadata_authority.url is 'Authority''s uri'; | ||||
| comment on column metadata_provider.metadata is 'Other metadata about provider'; | comment on column metadata_authority.metadata is 'Other metadata about authority'; | ||||
| -- Discovery of metadata during a listing, loading, deposit or external_catalog of an origin | -- Discovery of metadata during a listing, loading, deposit or external_catalog of an origin | ||||
| -- also provides a translation to a defined json schema using a translation tool (tool_id) | -- also provides a translation to a defined json schema using a translation tool (tool_id) | ||||
| create table origin_metadata | create table origin_metadata | ||||
| ( | ( | ||||
| id bigserial not null, -- PK internal object identifier | id bigserial not null, -- PK internal object identifier | ||||
| origin_id bigint not null, -- references origin(id) | origin_id bigint not null, -- references origin(id) | ||||
| discovery_date timestamptz not null, -- when it was extracted | discovery_date timestamptz not null, -- when it was extracted | ||||
| provider_id bigint not null, -- ex: 'hal', 'lister-github', 'loader-github' | authority_id bigint not null, | ||||
| tool_id bigint not null, | fetcher_id bigint not null, | ||||
| metadata jsonb not null | format text not null, | ||||
| metadata bytea not null | |||||
| ); | ); | ||||
| comment on table origin_metadata is 'keeps all metadata found concerning an origin'; | comment on table origin_metadata is 'keeps all metadata found concerning an origin'; | ||||
| comment on column origin_metadata.id is 'the origin_metadata object''s id'; | comment on column origin_metadata.id is 'the origin_metadata object''s id'; | ||||
| comment on column origin_metadata.origin_id is 'the origin id for which the metadata was found'; | comment on column origin_metadata.origin_id is 'the origin id for which the metadata was found'; | ||||
| comment on column origin_metadata.discovery_date is 'the date of retrieval'; | comment on column origin_metadata.discovery_date is 'the date of retrieval'; | ||||
| comment on column origin_metadata.provider_id is 'the metadata provider: github, openhub, deposit, etc.'; | comment on column origin_metadata.authority_id is 'the metadata provider: github, openhub, deposit, etc.'; | ||||
| comment on column origin_metadata.tool_id is 'the tool used for extracting metadata: lister-github, etc.'; | comment on column origin_metadata.fetcher_id is 'the tool used for extracting metadata: loaders, crawlers, etc.'; | ||||
| comment on column origin_metadata.metadata is 'metadata in json format but with original terms'; | comment on column origin_metadata.format is 'name of the format of metadata, used by readers to interpret it.'; | ||||
| comment on column origin_metadata.metadata is 'original metadata in opaque format'; | |||||
| -- Keep a cache of object counts | -- Keep a cache of object counts | ||||
| create table object_counts | create table object_counts | ||||
| ( | ( | ||||
| object_type text, -- table for which we're counting objects (PK) | object_type text, -- table for which we're counting objects (PK) | ||||
| value bigint, -- count of objects in the table | value bigint, -- count of objects in the table | ||||
| last_update timestamptz, -- last update for the object count in this table | last_update timestamptz, -- last update for the object count in this table | ||||
| Show All 31 Lines | |||||