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.

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');