diff options
-rw-r--r-- | Makefile | 7 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 39 | ||||
-rw-r--r-- | util/sql/func.sql | 1 | ||||
-rw-r--r-- | util/sql/schema.sql | 1 | ||||
-rw-r--r-- | util/updates/update_2.17.sql | 8 |
5 files changed, 21 insertions, 35 deletions
@@ -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 + |