Changeset View
Standalone View
sql/upgrades/148.sql
- This file was added.
-- SWH DB schema upgrade | |||||
-- from_version: 147 | |||||
-- to_version: 148 | |||||
-- description: Implement extrinsic origin-metadata specification | |||||
-- latest schema version | |||||
insert into dbversion(version, release, description) | |||||
values(148, now(), 'Work In Progress'); | |||||
-- metadata_fetcher | |||||
alter table tool rename 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) | |||||
-- metadata_authority | |||||
alter table metadata_provider rename metadata_authority; | |||||
comment on table metadata_authority is 'Metadata authority information'; | |||||
alter table metadata_authority | |||||
drop column provider_name; | |||||
moranegg: I remember we had a discussion about the about the `provider_name` and that it's redundant to… | |||||
Done Inline Actionsthe name is not needed as an identifier, and everything else can go in the metadata if we need it, but I don't think we do. vlorentz: the name is not needed as an identifier, and everything else can go in the metadata if we need… | |||||
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); | |||||
drop index metadata_provider_provider_name_provider_url_idx; | |||||
-- was an index on (provider_name, provider_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-json'; | |||||
Done Inline Actionsthe metadata in sword v2 is actually atomPub with the option to add other vocabularies (we chose to add CodeMeta to it) moranegg: the metadata in sword v2 is actually atomPub with the option to add other vocabularies (we… | |||||
alter table origin_metadata | |||||
rename column metadata to metadata_jsonb; | |||||
alter table origin_metadata | |||||
add column metadata; | |||||
create index concurrently 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) | |||||
Done Inline Actionsfrom my understanding, the change here is droping the index adding tool_id and having a similar index on discovery_date. moranegg: from my understanding, the change here is droping the index adding `tool_id` and having a… | |||||
-- migrate metadata_jsonb (a jsonb) to metadata (a bytea) with an external process | |||||
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; | |||||
drop function swh_origin_metadata_get_by_provider_type; | |||||
drop type origin_metadata_signature; | |||||
Done Inline ActionsI'm commenting here that the changes above should be all identified in swh-deposit before landing. moranegg: I'm commenting here that the changes above should be all identified in `swh-deposit` before… | |||||
Not Done Inline ActionsYes, and now thanks to your review and remarks on D3045, there is even a test which checks those are fine. ardumont: Yes, and now thanks to your review and remarks on D3045, there is even a test which checks… |
I remember we had a discussion about the about the provider_name and that it's redundant to url. are we sure that we don't need a distinction between name and url?