aboutsummaryrefslogtreecommitdiffstats
path: root/rust/migrations/2019-05-09-051834_v0.3/up.sql
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2019-05-08 23:39:34 -0700
committerBryan Newbold <bnewbold@robocracy.org>2019-05-08 23:39:34 -0700
commit89b04e5f716ac486f42ea8c55976ce7231ef692e (patch)
tree7ee38537d3de3f4b7f3fdf5aa766cf9238481307 /rust/migrations/2019-05-09-051834_v0.3/up.sql
parent92b43d6537f1d406f25f807798e706c17d5daa18 (diff)
downloadfatcat-89b04e5f716ac486f42ea8c55976ce7231ef692e.tar.gz
fatcat-89b04e5f716ac486f42ea8c55976ce7231ef692e.zip
first draft of SQL schema changes
Diffstat (limited to 'rust/migrations/2019-05-09-051834_v0.3/up.sql')
-rw-r--r--rust/migrations/2019-05-09-051834_v0.3/up.sql54
1 files changed, 54 insertions, 0 deletions
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);