summaryrefslogtreecommitdiff
path: root/util/updates/update_wip_lists.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-11-10 17:25:11 +0100
committerYorhel <git@yorhel.nl>2019-11-10 17:25:11 +0100
commita3318923ab711888df573015e3f6f58a692e67b6 (patch)
treefe7b0349b8ea96341e0d9d8e213404551fd5a86b /util/updates/update_wip_lists.sql
parent096b742b75cce0df7c41c3b9ff90fb9c610ad5da (diff)
ulist: Fix migration script to properly delete tables & handle concurrent changes
Diffstat (limited to 'util/updates/update_wip_lists.sql')
-rw-r--r--util/updates/update_wip_lists.sql9
1 files changed, 7 insertions, 2 deletions
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index 655cdb67..32b7c9fd 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -1,6 +1,6 @@
-- This script may be run multiple times while in beta, so clean up after ourselves.
-- (Or, uh, before ourselves, in this case...)
-DROP TABLE IF EXISTS ulist_vns, ulist_labels, ulist_vns_labels;
+DROP TABLE IF EXISTS ulist_vns, ulist_labels, ulist_vns_labels CASCADE;
DROP TRIGGER IF EXISTS ulist_labels_create ON users;
DROP FUNCTION IF EXISTS ulist_labels_create();
DROP FUNCTION IF EXISTS ulist_voted_label();
@@ -58,6 +58,10 @@ CREATE TABLE ulist_vns_labels (
\timing
+-- The following queries need a consistent view of the database.
+BEGIN;
+SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
INSERT INTO ulist_labels (uid, id, label, private)
SELECT id, 1, 'Playing', hide_list FROM users
UNION ALL SELECT id, 2, 'Finished', hide_list FROM users
@@ -97,7 +101,6 @@ INSERT INTO ulist_vns_labels (uid, vid, lbl)
UNION ALL SELECT uid, vid, 7 FROM votes;
-
ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
@@ -106,6 +109,8 @@ ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
+COMMIT;
+
\timing