Differential D2988 Diff 11243 sql/upgrades/149.sql

Changeset View

Standalone View

sql/upgrades/149.sql

This file was added.

				-- SWH DB schema upgrade
				-- from_version: 148
				-- to_version: 149
				-- description: Implement extrinsic origin-metadata specification

				-- latest schema version
				insert into dbversion(version, release, description)
				values(149, now(), 'Work In Progress');

				-- metadata_fetcher

				alter table tool
				rename to metadata_fetcher;
				comment on table metadata_fetcher is 'Tools used to retrieve metadata';

				alter table metadata_fetcher
				rename column configuration to metadata;

				comment on column metadata_fetcher.id is 'Internal identifier of the fetcher';
				comment on column metadata_fetcher.name is 'Fetcher name';
				comment on column metadata_fetcher.version is 'Fetcher version';
				comment on column metadata_fetcher.metadata is 'Extra information about the fetcher';

				alter index tool_pkey
				rename to metadata_fetcher_pkey;
				create unique index metadata_fetcher_name_version
				on metadata_fetcher(name, version);

				drop index tool_tool_name_tool_version_tool_configuration_idx;
				-- was an index on (name, version, configuration)
				-- this is the name in production; in new setups it would be called tool_name_version_configuration_idx
				-- drop index tool_name_version_configuration_idx;

				-- metadata_authority

				alter table metadata_provider
				rename to metadata_authority;
				comment on table metadata_authority is 'Metadata authority information';

				drop index metadata_provider_provider_name_provider_url_idx;
				-- was an index on (provider_name, provider_url)

				alter table metadata_authority
				drop column provider_name;
				alter table metadata_authority
				rename column provider_type to type;
				alter table metadata_authority
				rename column provider_url to url;

				comment on column metadata_authority.id is 'Internal identifier of the authority';
				comment on column metadata_authority.type is 'Type of authority (deposit/forge/registry)';
				comment on column metadata_authority.url is 'Authority''s uri';
				comment on column metadata_authority.metadata is 'Other metadata about authority';

				alter index metadata_provider_pkey
				rename to metadata_authority_pkey;
				alter index metadata_provider_type_url
				rename to metadata_authority_type_url;

				-- origin_metadata

				alter table origin_metadata
				rename column provider_id to authority_id;
				alter table origin_metadata
				rename column tool_id to fetcher_id;
				alter table origin_metadata
				add column format text default 'sword-v2-atom-codemeta-v2-in-json';
				alter table origin_metadata
				rename column metadata to metadata_jsonb;
				alter table origin_metadata
				add column metadata bytea;

				-- migrates metadata_jsonb (a jsonb) to metadata (a bytea)
				--update origin_metadata
				ardumontUnsubmitted Not Done Inline Actions This currently fails on staging with latest 148. Trying out olasd's proposition works: update origin_metadata set metadata=convert_to(metadata_jsonb::text, 'utf-8'); ardumont: This currently fails on staging with latest 148. Trying out olasd's proposition works: ```…
				-- set metadata=metadata_jsonb::text::bytea;
				update origin_metadata
				set metadata=convert_to(metadata_jsonb::text, 'utf-8');

				create index origin_metadata_origin_authority_date
				on origin_metadata(origin_id, authority_id, discovery_date);

				drop index origin_metadata_origin_id_provider_id_tool_id_idx;
				-- was an index on (origin_id, provider_id, tool_id)

				alter table origin_metadata
				drop column metadata_jsonb;

				comment on column origin_metadata.authority_id is 'the metadata provider: github, openhub, deposit, etc.';
				comment on column origin_metadata.fetcher_id is 'the tool used for extracting metadata: loaders, crawlers, etc.';
				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';


				-- cleanup unused functions

				drop function swh_mktemp_tool;
				ardumontUnsubmitted Not Done Inline Actions Currently failing with: psql:149.sql:100: ERROR: function name "swh_origin_metadata_get_by_origin" is not unique As indeed, there are 2 functions matching this: swh=> \df swh_origin_metadata_get_by_origin List of functions Schema \| Name \| Result data type \| Argument data types \| Type --------+-----------------------------------+---------------------------------+---------------------+------ public \| swh_origin_metadata_get_by_origin \| SETOF origin_metadata_signature \| origin integer \| func public \| swh_origin_metadata_get_by_origin \| SETOF origin_metadata_signature \| origin text \| func (2 rows) Same for the next one `swh_origin_metadata_get_by_provider_type`: swh=> \df swh_origin_metadata_get_by_provider_type List of functions Schema \| Name \| Result data type \| Argument data types \| Type --------+------------------------------------------+---------------------------------+-------------------------------------+------ public \| swh_origin_metadata_get_by_provider_type \| SETOF origin_metadata_signature \| origin integer, type text \| func public \| swh_origin_metadata_get_by_provider_type \| SETOF origin_metadata_signature \| origin_url text, provider_type text \| func (2 rows) For the drop instruction to work, we need to provide the function signatures. Assuming we want to drop all functions, that means: drop function swh_origin_metadata_get_by_origin(text); drop function swh_origin_metadata_get_by_provider_type(text, text); drop function swh_origin_metadata_get_by_origin(int); drop function swh_origin_metadata_get_by_provider_type(int, text); Note: If we need to drop all functions as well, the func.sql file needs to be updated with those other functions to be dropped. ardumont: Currently failing with: ``` psql:149.sql:100: ERROR: function name…
				drop function swh_tool_add;

				drop function swh_origin_metadata_get_by_origin(text);
				drop function swh_origin_metadata_get_by_provider_type(text, text);

				drop function swh_origin_metadata_get_by_origin(int);
				drop function swh_origin_metadata_get_by_provider_type(int, text);

				drop type origin_metadata_signature;