-- SWH DB schema upgrade
-- from_version: 148
-- to_version: 149
-- description: Implement extrinsic origin-metadata specification
begin;
-- 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;
create unique index metadata_authority_type_url
on 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
-- 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;
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;