first draft of SQL schema changes

2 files changed, 76 insertions, 0 deletions

diff --git a/rust/migrations/2019-05-09-051834_v0.3/down.sql b/rust/migrations/2019-05-09-051834_v0.3/down.sql
new file mode 100644
index 00000000..bd060a69
--- /dev/null
+++ b/rust/migrations/2019-05-09-051834_v0.3/down.sql
@@ -0,0 +1,22 @@
+-- This file should undo anything in `up.sql`
+
+ALTER TABLE release_contrib 
+DROP COLUMN given_name,
+DROP COLUMN surname;
+
+ALTER TABLE release_rev
+RENAME COLUMN release_stage TO release_status;
+
+ALTER TABLE release_rev
+DROP COLUMN number,
+DROP COLUMN version,
+DROP COLUMN subtitle,
+DROP COLUMN withdrawn_state,
+DROP COLUMN withdrawn_date,
+DROP COLUMN withdrawn_year,
+DROP COLUMN mag_id,
+DROP COLUMN ark_id;
+
+ALTER TABLE webcapture_rev_cdx
+DROP COLUMN size_bytes,
+DROP COLUMN extra_json;
diff --git a/rust/migrations/2019-05-09-051834_v0.3/up.sql b/rust/migrations/2019-05-09-051834_v0.3/up.sql
new file mode 100644
index 00000000..d38e842a
--- /dev/null
+++ b/rust/migrations/2019-05-09-051834_v0.3/up.sql
@@ -0,0 +1,54 @@
+
+-- Set of schema additions, tweaks, and missing indices
+-- Part of v0.3.0 (May 2019) backwards-incompatible changes to the Fatcat API
+
+-------------------- Release -----------------------------------------------
+
+-- structured contrib author names (in addition to 'raw_name')
+ALTER TABLE release_contrib 
+ADD COLUMN given_name          TEXT CHECK (octet_length(raw_name) >= 1),
+ADD COLUMN surname             TEXT CHECK (octet_length(surname) >= 1);
+
+-- release_status => release_stage (clarifies meaning of field)
+ALTER TABLE release_rev
+RENAME COLUMN release_status TO release_stage;
+
+-- additional fields; withdrawl tracking; additional external identifiers
+ALTER TABLE release_rev
+ADD COLUMN number                TEXT CHECK (octet_length(number) >= 1),
+ADD COLUMN version               TEXT CHECK (octet_length(version) >= 1),
+ADD COLUMN subtitle              TEXT CHECK (octet_length(subtitle) >= 1),
+ADD COLUMN withdrawn_state       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);
+
+--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);
+
+-------------------- Web Capture -------------------------------------------
+
+ALTER TABLE webcapture_rev_cdx
+ADD COLUMN size_bytes          BIGINT,
+ADD COLUMN extra_json          JSONB;
+
+-------------------- Edit Indices ------------------------------------------
+
+-- there is already a UNIQ index on (editgroup_id, ident_id)
+CREATE INDEX IF NOT EXISTS creator_edit_ident_idx ON creator_edit(ident_id);
+CREATE INDEX IF NOT EXISTS container_edit_ident_idx ON container_edit(ident_id);
+CREATE INDEX IF NOT EXISTS file_edit_ident_idx ON file_edit(ident_id);
+CREATE INDEX IF NOT EXISTS fileset_edit_ident_idx ON fileset_edit(ident_id);
+CREATE INDEX IF NOT EXISTS webcapture_edit_ident_idx ON webcapture_edit(ident_id);
+CREATE INDEX IF NOT EXISTS release_edit_ident_idx ON release_edit(ident_id);
+CREATE INDEX IF NOT EXISTS work_edit_ident_idx ON work_edit(ident_id);
+
+-- TODO: not needed?
+-- CREATE INDEX IF NOT EXISTS creator_edit_editgroup_idx ON creator_edit(editgroup_id);
+-- CREATE INDEX IF NOT EXISTS container_edit_editgroup_idx ON container_edit(editgroup_id);
+-- CREATE INDEX IF NOT EXISTS file_edit_editgroup_idx ON file_edit(editgroup_id);
+-- CREATE INDEX IF NOT EXISTS fileset_edit_editgroup_idx ON fileset_edit(editgroup_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);