diff options
author | Yorhel <git@yorhel.nl> | 2021-08-09 11:18:35 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-08-09 11:18:37 +0200 |
commit | e20a25f8070c255d8daea49151378d0aca3523ff (patch) | |
tree | a6eb3ac8ac6096f6a1b3fb4701d932c3cf37b634 /util | |
parent | 02448a7dd976344d29f698b84027686a6e64c673 (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.sql | 28 |
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 |