summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-08-09 11:18:35 +0200
committerYorhel <git@yorhel.nl>2021-08-09 11:18:37 +0200
commite20a25f8070c255d8daea49151378d0aca3523ff (patch)
treea6eb3ac8ac6096f6a1b3fb4701d932c3cf37b634 /util
parent02448a7dd976344d29f698b84027686a6e64c673 (diff)
VNLengthVote: Add unique vote id to handle editing deleted user's votes
I forgot that my wonderful UNIQUE index trick on (vid,uid) just doesn't work if you want to have the ability to edit individual vote entries after someone's account has been deleted. And I took this opportunity to recreate the vn_length_votes table with a saner layout.
Diffstat (limited to 'util')
-rw-r--r--util/updates/2021-08-09b-vnlength-primarykey.sql28
1 files changed, 28 insertions, 0 deletions
diff --git a/util/updates/2021-08-09b-vnlength-primarykey.sql b/util/updates/2021-08-09b-vnlength-primarykey.sql
new file mode 100644
index 00000000..5bb1df32
--- /dev/null
+++ b/util/updates/2021-08-09b-vnlength-primarykey.sql
@@ -0,0 +1,28 @@
+-- Recreate the vn_length_votes table to cleanly add a primary key and for more efficient storage.
+-- The table layout had gotten messy with all the recent edits.
+BEGIN;
+DROP INDEX vn_length_votes_pkey;
+DROP INDEX vn_length_votes_uid;
+ALTER TABLE vn_length_votes RENAME TO vn_length_votes_tmp;
+
+CREATE TABLE vn_length_votes (
+ id SERIAL PRIMARY KEY,
+ vid vndbid NOT NULL, -- [pub]
+ date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
+ length smallint NOT NULL, -- [pub] minutes
+ speed smallint NOT NULL, -- [pub] 0=slow, 1=normal, 2=fast
+ uid vndbid, -- [pub]
+ ignore boolean NOT NULL DEFAULT false, -- [pub]
+ rid vndbid[] NOT NULL, -- [pub]
+ notes text NOT NULL DEFAULT '' -- [pub]
+);
+
+INSERT INTO vn_length_votes (vid,date,uid,length,speed,ignore,rid,notes)
+ SELECT vid,date,uid,length,speed,ignore,rid,notes FROM vn_length_votes_tmp;
+
+CREATE UNIQUE INDEX vn_length_votes_vid_uid ON vn_length_votes (vid, uid);
+CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid);
+ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_length_votes ADD CONSTRAINT vn_length_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
+COMMIT;
+\i sql/perms.sql