summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorBryan Newbold <bnewbold@robocracy.org>2021-10-12 16:46:54 -0700
committerBryan Newbold <bnewbold@robocracy.org>2021-10-13 16:21:30 -0700
commit31c96eb15af54c90e4d048dfe82cc580a0679a13 (patch)
tree57db7fc4634f7bd20ffa8e760d775ac0c59f7e59
parentd4351119368184a32f07bb197d923a87bab4552b (diff)
downloadfatcat-31c96eb15af54c90e4d048dfe82cc580a0679a13.tar.gz
fatcat-31c96eb15af54c90e4d048dfe82cc580a0679a13.zip
sql: v0.4 schema implementation (as diesel migration)
-rw-r--r--rust/migrations/2021-10-12-215817_v0.4/down.sql14
-rw-r--r--rust/migrations/2021-10-12-215817_v0.4/up.sql44
2 files changed, 58 insertions, 0 deletions
diff --git a/rust/migrations/2021-10-12-215817_v0.4/down.sql b/rust/migrations/2021-10-12-215817_v0.4/down.sql
new file mode 100644
index 00000000..d4d35681
--- /dev/null
+++ b/rust/migrations/2021-10-12-215817_v0.4/down.sql
@@ -0,0 +1,14 @@
+-- This file should undo anything in `up.sql`
+
+DROP INDEX IF EXISTS container_rev_issne_idx;
+DROP INDEX IF EXISTS container_rev_issnp_idx;
+
+ALTER TABLE container_rev
+DROP COLUMN issne,
+DROP COLUMN issnp,
+DROP COLUMN publication_status;
+
+ALTER TABLE fileset_rev_file
+DROP COLUMN mimetype;
+
+DELETE FROM release_rev_extid WHERE extid_type = 'hdl';
diff --git a/rust/migrations/2021-10-12-215817_v0.4/up.sql b/rust/migrations/2021-10-12-215817_v0.4/up.sql
new file mode 100644
index 00000000..c76da889
--- /dev/null
+++ b/rust/migrations/2021-10-12-215817_v0.4/up.sql
@@ -0,0 +1,44 @@
+-- Small SQL changes
+-- Part of v0.4.0 (October 2021) changes to the Fatcat API
+
+-------------------- Container ---------------------------------------------
+
+ALTER TABLE container_rev
+-- fixed size identifiers
+ADD COLUMN issne TEXT CHECK (octet_length(issne) = 9),
+ADD COLUMN issnp TEXT CHECK (octet_length(issnp) = 9),
+ADD COLUMN publication_status TEXT CHECK (octet_length(publication_status) >= 1);
+
+CREATE INDEX container_rev_issne_idx ON container_rev(issne);
+CREATE INDEX container_rev_issnp_idx ON container_rev(issnp);
+
+
+-------------------- Fileset -----------------------------------------------
+
+ALTER TABLE fileset_rev_file
+ADD COLUMN mimetype TEXT CHECK (octet_length(mimetype) >= 1);
+
+
+-------------------- Update Test Revs --------------------------------------
+-- IMPORTANT: don't create new entities here, only mutate existing
+
+BEGIN;
+
+UPDATE container_rev SET
+ issne = '1234-3333',
+ issnp = '1234-6666',
+ publication_status = 'active'
+WHERE id = '00000000-0000-0000-1111-FFF000000002';
+
+UPDATE container_rev SET
+ issne = '1549-1676',
+ issnp = '1549-1277',
+ publication_status = 'active'
+WHERE id = '00000000-0000-0000-1111-FFF000000003';
+
+INSERT INTO release_rev_extid (release_rev, extid_type, value) VALUES
+ ('00000000-0000-0000-4444-FFF000000002', 'hdl', '20.500.23456/abc/dummy');
+
+UPDATE fileset_rev_file SET mimetype = 'application/gzip' WHERE fileset_rev = '00000000-0000-0000-6666-fff000000003' and md5 = 'f4de91152c7ab9fdc2a128f962faebff';
+
+COMMIT;