SQL: Added tags_vn.ignore column and updated queries to respect this

This is the first step in adding support for overruling tag votes by
moderators.

Also removed some unused options from dbTagStats(); the
tag-vote-stats-by-user pages have been removed in the previous VNDB
update, which was the only page using these additional options.

5 files changed, 21 insertions, 35 deletions

diff --git a/Makefile b/Makefile
index 4afd0f59..01c78da3 100644
--- a/Makefile
+++ b/Makefile
@@ -38,7 +38,7 @@
 
 
 .PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart\
-	sql-import update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16
+	sql-import update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17
 
 all: dirs js skins robots data/config.pl
 
@@ -163,3 +163,8 @@ update-2.16: all
 	$(multi-stop)
 	${runpsql} < util/updates/update_2.16.sql
 	$(multi-start)
+
+update-2.17: all
+	$(multi-stop)
+	${runpsql} < util/updates/update_2.16.sql
+	$(multi-start)
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index b3e16960..93705de0 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -235,26 +235,16 @@ sub dbTagLinkEdit {
 }
 
 
-# Fetch all tags related to a VN or User
-# Argument: %options->{ uid vid minrating results what page sort reverse }
-# what: vns
-# sort: name, count, rating
+# Fetch all tags related to a VN
+# Argument: %options->{ vid minrating results what page sort reverse }
+# sort: name, rating
 sub dbTagStats {
   my($self, %o) = @_;
   $o{results} ||= 10;
   $o{page}  ||= 1;
-  $o{what}  ||= '';
-
-  my %where = (
-    $o{uid} ? (
-      'tv.uid = ?' => $o{uid} ) : (),
-    $o{vid} ? (
-      'tv.vid = ?' => $o{vid} ) : (),
-  );
 
   my $order = sprintf {
     name => 't.name %s',
-    count => 'count(*) %s',
     rating => 'avg(tv.vote) %s',
   }->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC';
 
@@ -262,32 +252,13 @@ sub dbTagStats {
     SELECT t.id, t.name, count(*) as cnt, avg(tv.vote) as rating, COALESCE(avg(tv.spoiler), 0) as spoiler
       FROM tags t
       JOIN tags_vn tv ON tv.tag = t.id
-      !W
+      WHERE tv.vid = ? AND NOT tv.ignore
       GROUP BY t.id, t.name
       !s
       ORDER BY !s|,
-    \%where, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '',
-    $order
+    $o{vid}, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '', $order
   );
 
-  if(@$r && $o{what} =~ /vns/ && $o{uid}) {
-    my %r = map {
-      $_->{vns} = [];
-      ($_->{id}, $_->{vns})
-    } @$r;
-
-    push @{$r{$_->{tag}}}, $_ for (@{$self->dbAll(q|
-      SELECT tv.tag, tv.vote, tv.spoiler, vr.vid, vr.title, vr.original
-        FROM tags_vn tv
-        JOIN vn v ON v.id = tv.vid
-        JOIN vn_rev vr ON vr.id = v.latest
-        WHERE tv.uid = ?
-          AND tv.tag IN(!l)
-        ORDER BY vr.title ASC|,
-      $o{uid}, [ keys %r ]
-    )});
-  }
-
   return wantarray ? ($r, $np) : $r;
 }
 
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 0248c69a..2f87bdb9 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -103,6 +103,7 @@ BEGIN
     WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS (
         SELECT 15, tag, vid, uid, vote, spoiler, false
         FROM tags_vn
+       WHERE NOT ignore
       UNION ALL
         SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta
         FROM tags_vn_all ta
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 676d6400..2a3a394a 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -217,6 +217,7 @@ CREATE TABLE tags_vn (
   vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
   spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
   date timestamptz NOT NULL DEFAULT NOW(),
+  ignore boolean NOT NULL DEFAULT false,
   PRIMARY KEY(tag, vid, uid)
 );
 
diff --git a/util/updates/update_2.17.sql b/util/updates/update_2.17.sql
new file mode 100644
index 00000000..54487d0a
--- /dev/null
+++ b/util/updates/update_2.17.sql
@@ -0,0 +1,8 @@
+
+-- tag overrule feature
+ALTER TABLE tags_vn ADD COLUMN ignore boolean NOT NULL DEFAULT false;
+
+
+-- load new function(s)
+\i util/sql/func.sql
+