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.

4 files changed, 48 insertions, 15 deletions

diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index e64a620a..53e147f4 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -59,7 +59,7 @@ sub listing_ {
                 };
                 td_ class => 'tc6', sub { lit_ bb_format $_->{notes}, inline => 1 };
                 td_ class => 'tc7', sub {
-                    select_ name => "$_->{vid}-$_->{uid}", sub {
+                    select_ name => "lv$_->{id}", sub {
                         option_ value => '', '--';
                         option_ value => 's0', 'slow';
                         option_ value => 's1', 'normal';
@@ -123,7 +123,7 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub {
     my $count = tuwf->dbVali('SELECT COUNT(*) FROM vn_length_votes l WHERE', $where);
 
     my $lst = tuwf->dbPagei({results => $opt->{s}->results, page => $opt->{p}},
-      'SELECT l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, '
+      'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, '
             , sql_totime('l.date'), 'AS date, l.ignore OR u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore',
               $mode ne 'u' ? (', ', sql_user()) : (),
               $mode ne 'v' ? ', v.title, v.original' : (), '
@@ -153,18 +153,22 @@ TUWF::post '/lengthvotes-edit', sub {
 
     my @actions;
     for my $k (tuwf->reqPosts) {
-        next if $k !~ /^(?<vid>$RE{vid})-(?<uid>$RE{uid})$/;
-        my $where = { vid => $+{vid}, uid => $+{uid} };
+        next if $k !~ /^lv$RE{num}$/;
+        my $id = $+{num};
         my $act = tuwf->reqPost($k);
         next if !$act;
-        push @actions, "$k-$act";
-        tuwf->dbExeci('UPDATE vn_length_votes SET ignore = true WHERE', $where) if $act eq 'ign';
-        tuwf->dbExeci('UPDATE vn_length_votes SET ignore = false WHERE', $where) if $act eq 'noign';
-        tuwf->dbExeci('UPDATE vn_length_votes SET speed = 0 WHERE', $where) if $act eq 's0';
-        tuwf->dbExeci('UPDATE vn_length_votes SET speed = 1 WHERE', $where) if $act eq 's1';
-        tuwf->dbExeci('UPDATE vn_length_votes SET speed =', \2, 'WHERE', $where) if $act eq 's2';
+        my $r = tuwf->dbRowi('
+            UPDATE vn_length_votes SET',
+              $act eq 'ign' ? 'ignore = true' :
+            $act eq 'noign' ? 'ignore = false' :
+               $act eq 's0' ? 'speed = 0' :
+               $act eq 's1' ? 'speed = 1' :
+               $act eq 's2' ? ('speed =', \2) : die,
+           'WHERE id =', \$id, 'RETURNING vid, uid'
+        );
+        push @actions, "$r->{vid}-".($r->{uid}//'anon')."-$act";
     }
-    auth->audit(undef, 'lengthvote-edit', join ', ', sort @actions) if @actions;
+    auth->audit(undef, 'lengthvote edit', join ', ', sort @actions) if @actions;
     tuwf->resRedirect(tuwf->reqPost('url'), 'post');
 };
 
diff --git a/sql/schema.sql b/sql/schema.sql
index d97051df..e3a957f0 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -1153,14 +1153,15 @@ CREATE TABLE vn_staff_hist (
 
 -- vn_length_votes
 CREATE TABLE vn_length_votes (
+  id         integer PRIMARY KEY,
   vid        vndbid NOT NULL, -- [pub]
   date       timestamptz NOT NULL DEFAULT NOW(), -- [pub]
-  uid        vndbid, -- [pub]
   length     smallint NOT NULL, -- [pub] minutes
   speed      smallint NOT NULL, -- [pub] 0=slow, 1=normal, 2=fast
-  notes      text NOT NULL DEFAULT '', -- [pub]
+  uid        vndbid, -- [pub]
   ignore     boolean NOT NULL DEFAULT false, -- [pub]
-  rid        vndbid[] NOT NULL -- [pub]
+  rid        vndbid[] NOT NULL, -- [pub]
+  notes      text NOT NULL DEFAULT '' -- [pub]
 );
 
 -- wikidata
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index 0cbb8043..f984a4e4 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -154,7 +154,7 @@ CREATE        INDEX vn_screenshots_scr     ON vn_screenshots (scr);
 CREATE        INDEX vn_seiyuu_aid          ON vn_seiyuu (aid); -- Only used on /s+?
 CREATE        INDEX vn_seiyuu_cid          ON vn_seiyuu (cid); -- Only used on /c+?
 CREATE        INDEX vn_staff_aid           ON vn_staff (aid);
-CREATE UNIQUE INDEX vn_length_votes_pkey   ON vn_length_votes (vid, uid);
+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);
 CREATE UNIQUE INDEX changes_itemrev        ON changes (itemid, rev);
 CREATE UNIQUE INDEX chars_vns_pkey         ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier.
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