SQL: Allow tags_vn.uid to be NULL for deleted users

This fixes the issue of tag votes getting deleted when a user account is
deleted, despite these votes being part of the kept "database
contributions" mentioned in the privacy policy.

7 files changed, 21 insertions, 54 deletions

diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index ed3ea9fe..e412e10f 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -5,7 +5,7 @@ use strict;
 use warnings;
 use Exporter 'import';
 
-our @EXPORT = qw|dbTagGet dbTTTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagStats dbTagWipeVotes|;
+our @EXPORT = qw|dbTagGet dbTTTree dbTagEdit dbTagAdd dbTagMerge dbTagStats dbTagWipeVotes|;
 
 
 # %options->{ id noid name search state searchable applicable page results what sort reverse  }
@@ -170,47 +170,6 @@ sub dbTagMerge {
 }
 
 
-# Directly fetch rows from tags_vn
-# Options: vid uid tag page results what sort reverse
-# What: details
-sub dbTagLinks {
-  my($self, %o) = @_;
-  $o{results} ||= 999;
-  $o{page}    ||= 1;
-  $o{what}    ||= '';
-
-  my %where = (
-    $o{vid} ? ('tv.vid = ?' => $o{vid}) : (),
-    $o{uid} ? ('tv.uid = ?' => $o{uid}) : (),
-    $o{tag} ? ('tv.tag = ?' => $o{tag}) : (),
-  );
-
-  my @select = (
-    qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler tv.ignore|, "EXTRACT('epoch' from tv.date) AS date",
-    $o{what} =~ /details/ ? (qw|v.title t.name|, VNWeb::DB::sql_user()) : (),
-  );
-
-  my @join = $o{what} =~ /details/ ? (
-    'JOIN vn v ON v.id = tv.vid',
-    'JOIN users u ON u.id = tv.uid',
-    'JOIN tags t ON t.id = tv.tag'
-  ) : ();
-
-  my $order = !$o{sort} ? '' : 'ORDER BY '.{
-    username => 'u.username',
-    date     => 'tv.date',
-    title    => 'v.title',
-    tag      => 't.name',
-  }->{$o{sort}}.($o{reverse} ? ' DESC' : ' ASC');
-
-  my($r, $np) = $self->dbPage(\%o,
-    'SELECT !s FROM tags_vn tv !s !W !s',
-    join(', ', @select), join(' ', @join), \%where, $order
-  );
-  return wantarray ? ($r, $np) : $r;
-}
-
-
 # Fetch all tags related to a VN
 # Argument: %options->{ vid minrating state results what page sort reverse }
 # sort: name, rating
diff --git a/lib/VNWeb/Tags/Links.pm b/lib/VNWeb/Tags/Links.pm
index e3f74aa6..6e7dc06b 100644
--- a/lib/VNWeb/Tags/Links.pm
+++ b/lib/VNWeb/Tags/Links.pm
@@ -23,7 +23,7 @@ sub listing_ {
                 my $i = $_;
                 td_ class => 'tc1', fmtdate $i->{date};
                 td_ class => 'tc2', sub {
-                    a_ href => $url->(u => $i->{uid}, p=>undef), class => 'setfil', '> ' if !defined $opt->{u};
+                    a_ href => $url->(u => $i->{uid}, p=>undef), class => 'setfil', '> ' if $i->{uid} && !defined $opt->{u};
                     user_ $i;
                 };
                 td_ class => 'tc3', sub { tagscore_ $i->{vote}, $i->{ignore} };
@@ -70,7 +70,7 @@ TUWF::get qr{/g/links}, sub {
         SELECT tv.vid, tv.uid, tv.tag, tv.vote, tv.spoiler,', sql_totime('tv.date'), 'as date, tv.ignore, tv.notes, v.title,', sql_user(), ', t.name
           FROM tags_vn tv
           JOIN vn v ON v.id = tv.vid
-          JOIN users u ON u.id = tv.uid
+          LEFT JOIN users u ON u.id = tv.uid
           JOIN tags t ON t.id = tv.tag
          WHERE', $where, '
          ORDER BY', { date => 'tv.date', tag => 't.name' }->{$opt->{s}}, { a => 'ASC', d => 'DESC' }->{$opt->{o}}
diff --git a/lib/VNWeb/VN/Tagmod.pm b/lib/VNWeb/VN/Tagmod.pm
index 73a44805..078a04bc 100644
--- a/lib/VNWeb/VN/Tagmod.pm
+++ b/lib/VNWeb/VN/Tagmod.pm
@@ -52,8 +52,8 @@ elm_api Tagmod => $FORM_OUT, $FORM_IN, sub {
     # Add & update tags
     for(@$tags) {
         my $row = { uid => auth->uid, vid => $id, tag => $_->{id}, vote => $_->{vote}, spoiler => $_->{spoil}, ignore => ($_->{overruled} && !$_->{overrule})?1:0, notes => $_->{notes} };
-        tuwf->dbExeci('INSERT INTO tags_vn', $row, 'ON CONFLICT (uid, vid, tag) DO UPDATE SET', $row);
-        tuwf->dbExeci('UPDATE tags_vn SET ignore = TRUE WHERE uid <>', \auth->uid, 'AND vid =', \$id, 'AND tag =', \$_->{id}) if $_->{overrule};
+        tuwf->dbExeci('INSERT INTO tags_vn', $row, 'ON CONFLICT (uid, tag, vid) DO UPDATE SET', $row);
+        tuwf->dbExeci('UPDATE tags_vn SET ignore = TRUE WHERE uid IS DISTINCT FROM (', \auth->uid, ') AND vid =', \$id, 'AND tag =', \$_->{id}) if $_->{overrule};
     }
 
     # Make sure to reset the ignore flag when a moderator removes an overruled vote.
@@ -83,7 +83,7 @@ TUWF::get qr{/$RE{vid}/tagmod}, sub {
     );
     enrich_merge id => sub { sql 'SELECT tag AS id, vote, spoiler AS spoil, ignore, notes FROM tags_vn WHERE', { uid => auth->uid, vid => $v->{id} } }, $tags;
     enrich othnotes => id => tag => sub {
-        sql('SELECT tv.tag, ', sql_user(), ', tv.notes FROM tags_vn tv JOIN users u ON u.id = tv.uid WHERE tv.notes <> \'\' AND uid <>', \auth->uid, 'AND vid=', \$v->{id})
+        sql('SELECT tv.tag, ', sql_user(), ', tv.notes FROM tags_vn tv JOIN users u ON u.id = tv.uid WHERE tv.notes <> \'\' AND uid IS DISTINCT FROM (', \auth->uid, ') AND vid=', \$v->{id})
     }, $tags;
 
     for(@$tags) {
diff --git a/util/dbdump.pl b/util/dbdump.pl
index 640ff6fc..8f0cc5f8 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -76,7 +76,7 @@ my %tables = (
     tags                => { where => 'state = 2' },
     tags_aliases        => { where => 'tag IN(SELECT id FROM tags WHERE state = 2)' },
     tags_parents        => { where => 'tag IN(SELECT id FROM tags WHERE state = 2)' },
-    tags_vn             => { where => 'tag IN(SELECT id FROM tags WHERE state = 2) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
+    tags_vn             => { where => 'tag IN(SELECT id FROM tags WHERE state = 2) AND vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'tag, vid, uid, date' },
     traits              => { where => 'state = 2' },
     traits_parents      => { where => 'trait IN(SELECT id FROM traits WHERE state = 2)' },
     ulist_labels        => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' },
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 6b4ffd21..eafa4428 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -605,13 +605,12 @@ CREATE TABLE tags_parents (
 CREATE TABLE tags_vn (
   tag integer NOT NULL, -- [pub]
   vid integer NOT NULL, -- [pub]
-  uid integer NOT NULL, -- [pub]
+  uid integer, -- [pub]
   vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), -- [pub]
   spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), -- [pub]
   date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
   ignore boolean NOT NULL DEFAULT false, -- [pub]
-  notes text NOT NULL DEFAULT '', -- [pub]
-  PRIMARY KEY(tag, vid, uid)
+  notes text NOT NULL DEFAULT '' -- [pub]
 );
 
 -- tags_vn_inherit
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 781e6aad..5c894a85 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -54,7 +54,7 @@ ALTER TABLE tags_parents             ADD CONSTRAINT tags_parents_tag_fkey
 ALTER TABLE tags_parents             ADD CONSTRAINT tags_parents_parent_fkey           FOREIGN KEY (parent)    REFERENCES tags          (id);
 ALTER TABLE tags_vn                  ADD CONSTRAINT tags_vn_tag_fkey                   FOREIGN KEY (tag)       REFERENCES tags          (id);
 ALTER TABLE tags_vn                  ADD CONSTRAINT tags_vn_vid_fkey                   FOREIGN KEY (vid)       REFERENCES vn            (id);
-ALTER TABLE tags_vn                  ADD CONSTRAINT tags_vn_uid_fkey                   FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
+ALTER TABLE tags_vn                  ADD CONSTRAINT tags_vn_uid_fkey                   FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE threads                  ADD CONSTRAINT threads_id_fkey                    FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE threads_poll_options     ADD CONSTRAINT threads_poll_options_tid_fkey      FOREIGN KEY (tid)       REFERENCES threads       (id) ON DELETE CASCADE;
 ALTER TABLE threads_poll_votes       ADD CONSTRAINT threads_poll_votes_tid_fkey        FOREIGN KEY (tid)       REFERENCES threads       (id) ON DELETE CASCADE;
@@ -111,11 +111,12 @@ CREATE        INDEX notifications_uid      ON notifications (uid);
 CREATE        INDEX releases_producers_pid ON releases_producers (pid);
 CREATE        INDEX releases_vn_vid        ON releases_vn (vid);
 CREATE        INDEX staff_alias_id         ON staff_alias (id);
+CREATE UNIQUE INDEX tags_vn_pkey           ON tags_vn (tag,vid,uid);
 CREATE        INDEX tags_vn_date           ON tags_vn (date);
 CREATE        INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
-CREATE        INDEX tags_vn_uid            ON tags_vn (uid);
-CREATE        INDEX shop_playasia__gtin    ON shop_playasia (gtin);
+CREATE        INDEX tags_vn_uid            ON tags_vn (uid) WHERE uid IS NOT NULL;
 CREATE        INDEX tags_vn_vid            ON tags_vn (vid);
+CREATE        INDEX shop_playasia__gtin    ON shop_playasia (gtin);
 CREATE        INDEX threads_posts_date     ON threads_posts (date);
 CREATE        INDEX threads_posts_ts       ON threads_posts USING gin(bb_tsvector(msg));
 CREATE        INDEX threads_posts_uid      ON threads_posts (uid); -- Only really used on /u+ pages to get stats
diff --git a/util/updates/2020-02-21-tags-vn-null-users.sql b/util/updates/2020-02-21-tags-vn-null-users.sql
new file mode 100644
index 00000000..4a645071
--- /dev/null
+++ b/util/updates/2020-02-21-tags-vn-null-users.sql
@@ -0,0 +1,8 @@
+ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_pkey;
+ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey;
+ALTER TABLE tags_vn ALTER COLUMN uid DROP NOT NULL;
+CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid);
+DROP INDEX tags_vn_uid;
+CREATE INDEX tags_vn_uid ON tags_vn (uid) WHERE uid IS NOT NULL;
+UPDATE tags_vn SET uid = 0 WHERE uid IN(0,1);
+ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;