From b07f92545eaf112db735903dac8f7809fe62440a Mon Sep 17 00:00:00 2001 From: Bryan Newbold Date: Fri, 10 May 2019 15:03:46 -0700 Subject: schema: move release ext_ids to their own table and sub-entity Some identifiers are kept on the main release_rev table to minimize impact to the existing database. --- fatcat-openapi2.yml | 63 +++++++++++++------------ rust/migrations/2019-05-09-051834_v0.3/down.sql | 7 +-- rust/migrations/2019-05-09-051834_v0.3/up.sql | 37 +++++++++++++-- 3 files changed, 70 insertions(+), 37 deletions(-) diff --git a/fatcat-openapi2.yml b/fatcat-openapi2.yml index 389ba24e..696d8495 100644 --- a/fatcat-openapi2.yml +++ b/fatcat-openapi2.yml @@ -319,6 +319,8 @@ definitions: release_entity: type: object # required for creation: title + required: + - ext_ids properties: <<: *ENTITYPROPS title: @@ -376,30 +378,33 @@ definitions: type: integer example: 2014 format: int64 - doi: - type: string - #format: custom - example: "10.1234/abcde.789" - wikidata_qid: - type: string - isbn13: - type: string - #format: custom - pmid: - type: string - pmcid: - type: string - core_id: - type: string - #format: custom - arxiv_id: - type: string - jstor_id: - type: string - ark_id: - type: string - mag_id: - type: string + ext_ids: + type: object + properties: + doi: + type: string + #format: custom + example: "10.1234/abcde.789" + wikidata_qid: + type: string + isbn13: + type: string + #format: custom + pmid: + type: string + pmcid: + type: string + core: + type: string + #format: custom + arxiv: + type: string + jstor: + type: string + ark: + type: string + mag: + type: string volume: type: string issue: @@ -2285,23 +2290,23 @@ paths: in: query type: string required: false - - name: core_id + - name: core in: query type: string required: false - - name: arxiv_id + - name: arxiv in: query type: string required: false - - name: jstor_id + - name: jstor in: query type: string required: false - - name: ark_id + - name: ark in: query type: string required: false - - name: mag_id + - name: mag in: query type: string required: false diff --git a/rust/migrations/2019-05-09-051834_v0.3/down.sql b/rust/migrations/2019-05-09-051834_v0.3/down.sql index 65e818bf..612cdbcc 100644 --- a/rust/migrations/2019-05-09-051834_v0.3/down.sql +++ b/rust/migrations/2019-05-09-051834_v0.3/down.sql @@ -13,9 +13,10 @@ DROP COLUMN version, DROP COLUMN subtitle, DROP COLUMN withdrawn_status, DROP COLUMN withdrawn_date, -DROP COLUMN withdrawn_year, -DROP COLUMN mag_id, -DROP COLUMN ark_id; +DROP COLUMN withdrawn_year; + +DROP INDEX IF EXISTS release_rev_extid_type_value_idx; +DROP TABLE release_rev_extid; ALTER TABLE webcapture_rev_cdx DROP COLUMN size_bytes; diff --git a/rust/migrations/2019-05-09-051834_v0.3/up.sql b/rust/migrations/2019-05-09-051834_v0.3/up.sql index e5e88793..aca02c85 100644 --- a/rust/migrations/2019-05-09-051834_v0.3/up.sql +++ b/rust/migrations/2019-05-09-051834_v0.3/up.sql @@ -20,12 +20,27 @@ ADD COLUMN version TEXT CHECK (octet_length(version) >= 1), ADD COLUMN subtitle TEXT CHECK (octet_length(subtitle) >= 1), ADD COLUMN withdrawn_status TEXT, -- TODO: enum? ADD COLUMN withdrawn_date DATE, -ADD COLUMN withdrawn_year BIGINT, -ADD COLUMN mag_id TEXT CHECK (octet_length(mag_id) >= 1), -ADD COLUMN ark_id TEXT CHECK (octet_length(ark_id) >= 5); +ADD COLUMN withdrawn_year BIGINT; -CREATE INDEX IF NOT EXISTS release_rev_mag_idx ON release_rev(mag_id); -CREATE INDEX IF NOT EXISTS release_rev_ark_idx ON release_rev(mag_id); +-- create new, separate +CREATE TABLE release_rev_extid ( + release_rev UUID REFERENCES release_rev(id) NOT NULL, + extid_type TEXT NOT NULL CHECK (octet_length(extid_type) >= 1), + value TEXT NOT NULL CHECK (octet_length(value) >= 1), + PRIMARY KEY(release_rev, extid_type) +); + +CREATE INDEX release_rev_extid_type_value_idx ON release_rev_extid(extid_type, value); + +-- remove now-unused identifier columns +DROP INDEX IF EXISTS release_rev_isbn13_idx; +DROP INDEX IF EXISTS release_rev_arxiv_idx; +DROP INDEX IF EXISTS release_rev_jstor_idx; + +ALTER TABLE release_rev +DROP COLUMN isbn13, +DROP COLUMN arxiv_id, +DROP COLUMN jstor_id; -------------------- Web Capture ------------------------------------------- @@ -51,3 +66,15 @@ CREATE INDEX IF NOT EXISTS work_edit_ident_idx ON work_edit(ident_id); -- CREATE INDEX IF NOT EXISTS webcapture_edit_editgroup_idx ON webcapture_edit(editgroup_id); -- CREATE INDEX IF NOT EXISTS release_edit_editgroup_idx ON release_edit(editgroup_id); -- CREATE INDEX IF NOT EXISTS work_edit_editgroup_idx ON work_edit(editgroup_id); + + +-------------------- Update Test Revs -------------------------------------- + +-- IMPORTANT: don't create new entities here, only mutate existing + +INSERT INTO release_rev_extid (release_rev, extid_type, value) VALUES + ('00000000-0000-0000-4444-FFF000000002', 'isbn13', '978-3-16-148410-0'), + ('00000000-0000-0000-4444-FFF000000002', 'arxiv', '1905.03769v1'), + ('00000000-0000-0000-4444-FFF000000002', 'jstor', '1819117828'), + ('00000000-0000-0000-4444-FFF000000002', 'ark', 'ark:/asdf/924'), + ('00000000-0000-0000-4444-FFF000000002', 'mag', '992489213'); -- cgit v1.2.3