6 files changed, 273 insertions, 38 deletions

diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 1e626c57..b28bfeab 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -9,7 +9,7 @@ our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPos
 
 
 # Options: id, type, iid, results, page, what
-# What: tags, tagtitles, firstpost, lastpost
+# What: boards, boardtitles, firstpost, lastpost
 sub dbThreadGet {
   my($self, %o) = @_;
   $o{results} ||= 50;
@@ -23,9 +23,9 @@ sub dbThreadGet {
     !$o{id} ? (
       't.hidden = FALSE' => 0 ) : (),
     $o{type} && !$o{iid} ? (
-      't.id IN(SELECT tid FROM threads_tags WHERE type = ?)' => $o{type} ) : (),
+      't.id IN(SELECT tid FROM threads_boards WHERE type = ?)' => $o{type} ) : (),
     $o{type} && $o{iid} ? (
-      'tt.type = ?' => $o{type}, 'tt.iid = ?' => $o{iid} ) : (),
+      'tb.type = ?' => $o{type}, 'tb.iid = ?' => $o{iid} ) : (),
   );
 
   my @select = (
@@ -44,7 +44,7 @@ sub dbThreadGet {
       'JOIN users ul ON ul.id = tpl.uid'
     ) : (),
     $o{type} && $o{iid} ?
-      'JOIN threads_tags tt ON tt.tid = t.id' : (),
+      'JOIN threads_boards tb ON tb.tid = t.id' : (),
   );
 
   my($r, $np) = $self->dbPage(\%o, q|
@@ -56,30 +56,30 @@ sub dbThreadGet {
     join(', ', @select), join(' ', @join), \%where, $o{order}
   );
 
-  if($o{what} =~ /(tags|tagtitles)/ && $#$r >= 0) {
+  if($o{what} =~ /(boards|boardtitles)/ && $#$r >= 0) {
     my %r = map {
-      $r->[$_]{tags} = [];
+      $r->[$_]{boards} = [];
       ($r->[$_]{id}, $_)
     } 0..$#$r;
 
-    if($o{what} =~ /tags/) {
-      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{tags}}, [ $_->{type}, $_->{iid} ]) for (@{$self->dbAll(q|
+    if($o{what} =~ /boards/) {
+      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{boards}}, [ $_->{type}, $_->{iid} ]) for (@{$self->dbAll(q|
         SELECT tid, type, iid
-          FROM threads_tags
+          FROM threads_boards
           WHERE tid IN(!l)|,
         [ keys %r ]
       )});
     }
-    if($o{what} =~ /tagtitles/) {
-      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{tags}}, $_) for (@{$self->dbAll(q|
-        SELECT tt.tid, tt.type, tt.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original
-          FROM threads_tags tt
-          LEFT JOIN vn v ON tt.type = 'v' AND v.id = tt.iid
+    if($o{what} =~ /boardtitles/) {
+      ($_->{type}=~s/ +//||1) && push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
+        SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original
+          FROM threads_boards tb
+          LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid
           LEFT JOIN vn_rev vr ON vr.id = v.latest
-          LEFT JOIN producers p ON tt.type = 'p' AND p.id = tt.iid
+          LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid
           LEFT JOIN producers_rev pr ON pr.id = p.latest
-          LEFT JOIN users u ON tt.type = 'u' AND u.id = tt.iid
-          WHERE tt.tid IN(!l)|,
+          LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid
+          WHERE tb.tid IN(!l)|,
         [ keys %r ]
       )});
     }
@@ -89,7 +89,7 @@ sub dbThreadGet {
 }
 
 
-# id, %options->( title locked hidden tags }
+# id, %options->( title locked hidden boards }
 sub dbThreadEdit {
   my($self, $id, %o) = @_;
 
@@ -105,18 +105,18 @@ sub dbThreadEdit {
       WHERE id = ?|,
     \%set, $id);
 
-  if($o{tags}) {
-    $self->dbExec('DELETE FROM threads_tags WHERE tid = ?', $id);
+  if($o{boards}) {
+    $self->dbExec('DELETE FROM threads_boards WHERE tid = ?', $id);
     $self->dbExec(q|
-      INSERT INTO threads_tags (tid, type, iid)
+      INSERT INTO threads_boards (tid, type, iid)
         VALUES (?, ?, ?)|,
       $id, $_->[0], $_->[1]||0
-    ) for (@{$o{tags}});
+    ) for (@{$o{boards}});
   }
 }
 
 
-# %options->{ title hidden locked tags }
+# %options->{ title hidden locked boards }
 sub dbThreadAdd {
   my($self, %o) = @_;
 
@@ -128,23 +128,25 @@ sub dbThreadAdd {
   )->{id};
 
   $self->dbExec(q|
-    INSERT INTO threads_tags (tid, type, iid)
+    INSERT INTO threads_boards (tid, type, iid)
       VALUES (?, ?, ?)|,
     $id, $_->[0], $_->[1]||0
-  ) for (@{$o{tags}});
+  ) for (@{$o{boards}});
 
   return $id;
 }
 
 
-# Returns thread count of a specific item tag
+# Returns thread count of a specific item board
 # Arguments: type, iid
 sub dbThreadCount {
   my($self, $type, $iid) = @_;
   return $self->dbRow(q|
     SELECT COUNT(*) AS cnt
-      FROM threads_tags
-      WHERE type = ? AND iid = ?|,
+      FROM threads_boards tb
+      JOIN threads t ON t.id = tb.tid
+      WHERE tb.type = ? AND tb.iid = ?
+        AND t.hidden = FALSE|,
     $type, $iid)->{cnt};
 }
 
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 14ef45ac..c13a38d2 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw|
-  dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbLanguages
+  dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbLanguages dbRandomQuote
 |;
 
 
@@ -173,6 +173,16 @@ sub dbLanguages {
 }
 
 
+# Returns a random quote (hashref with keys = vid, quote)
+sub dbRandomQuote {
+  return $_[0]->dbRow(q|
+    SELECT vid, quote
+      FROM quotes
+      ORDER BY RANDOM()
+      LIMIT 1|);
+}
+
+
 
 
 1;
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index c7abd765..7a63d1a2 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -9,7 +9,7 @@ our @EXPORT = qw|dbProducerGet dbProducerEdit dbProducerAdd|;
 
 
 # options: results, page, id, search, char, rev
-# what: changes, vn
+# what: extended, changes, vn
 sub dbProducerGet {
   my $self = shift;
   my %o = (
@@ -27,7 +27,7 @@ sub dbProducerGet {
     $o{id} ? (
       'p.id = ?' => $o{id} ) : (),
     $o{search} ? (
-      '(pr.name ILIKE ? OR pr.original ILIKE ?)', [ '%%'.$o{search}.'%%', '%%'.$o{search}.'%%' ] ) : (),
+      '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..3 ] ) : (),
     $o{char} ? (
       'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (),
     defined $o{char} && !$o{char} ? (
@@ -41,7 +41,8 @@ sub dbProducerGet {
   push @join, 'JOIN changes c ON c.id = pr.id' if $o{what} =~ /changes/ || $o{rev};
   push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
 
-  my $select = 'p.id, p.locked, p.hidden, pr.type, pr.name, pr.original, pr.website, pr.lang, pr.desc';
+  my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang';
+  $select .= ', pr.desc, pr.alias, pr.website, p.hidden, p.locked' if $o{what} =~ /extended/;
   $select .= ', c.added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev' if $o{what} =~ /changes/;
 
   my($r, $np) = $self->dbPage(\%o, q|
@@ -105,9 +106,9 @@ sub dbProducerAdd {
 sub insert_rev {
   my($self, $cid, $pid, $o) = @_;
   $self->dbExec(q|
-    INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc")
+    INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc", alias)
       VALUES (!l)|,
-    [ $cid, $pid, @$o{qw| name original website type lang desc|} ]
+    [ $cid, $pid, @$o{qw| name original website type lang desc alias|} ]
   );
 }
 
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 52fea2b0..1df13894 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -10,7 +10,7 @@ our @EXPORT = qw|dbReleaseGet dbReleaseAdd dbReleaseEdit|;
 
 
 # Options: id vid rev order unreleased page results what
-# What: changes vn producers platforms media
+# What: extended changes vn producers platforms media
 sub dbReleaseGet {
   my($self, %o) = @_;
   $o{results} ||= 50;
@@ -41,7 +41,9 @@ sub dbReleaseGet {
   );
 
   my @select = (
-    qw|r.id r.locked r.hidden rr.title rr.original rr.gtin rr.language rr.website rr.released rr.notes rr.minage rr.type rr.patch|, 'rr.id AS cid',
+    qw|r.id rr.title rr.original rr.language rr.website rr.released rr.minage rr.type rr.patch|,
+    'rr.id AS cid',
+    $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin r.hidden r.locked| : (),
     $o{what} =~ /changes/ ? qw|c.added c.requester c.comments r.latest u.username c.rev| : (),
   );
 
@@ -136,9 +138,9 @@ sub insert_rev {
   my($self, $cid, $rid, $o) = @_;
 
   $self->dbExec(q|
-    INSERT INTO releases_rev (id, rid, title, original, gtin, language, website, released, notes, minage, type, patch)
+    INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, language, website, released, notes, minage, type, patch)
       VALUES (!l)|,
-    [ $cid, $rid, @$o{qw| title original gtin language website released notes minage type patch|} ]);
+    [ $cid, $rid, @$o{qw| title original gtin catalog language website released notes minage type patch|} ]);
 
   $self->dbExec(q|
     INSERT INTO releases_producers (rid, pid)
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
new file mode 100644
index 00000000..280f3f3e
--- /dev/null
+++ b/lib/VNDB/DB/Tags.pm
@@ -0,0 +1,218 @@
+
+package VNDB::DB::Tags;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats dbTagVNs|;
+
+
+# %options->{ id noid name search state meta page results order what }
+# what: parents childs(n) aliases
+sub dbTagGet {
+  my $self = shift;
+  my %o = (
+    order => 't.id ASC',
+    page => 1,
+    results => 10,
+    what => '',
+    @_
+  );
+
+  $o{search} =~ s/%//g if $o{search};
+
+  my %where = (
+    $o{id} ? (
+      't.id = ?' => $o{id} ) : (),
+    $o{noid} ? (
+      't.id <> ?' => $o{noid} ) : (),
+    $o{name} ? (
+      't.id = (SELECT id FROM tags LEFT JOIN tags_aliases ON id = tag WHERE lower(name) = ? OR lower(alias) = ? LIMIT 1)' => [ lc $o{name}, lc $o{name} ]) : (),
+    defined $o{state} && $o{state} != -1 ? (
+      't.state = ?' => $o{state} ) : (),
+    !defined $o{state} && !$o{id} && !$o{name} ? (
+      't.state <> 1' => 1 ) : (),
+    $o{search} ? (
+      't.id IN (SELECT id FROM tags LEFT JOIN tags_aliases ON id = tag WHERE name ILIKE ? OR alias ILIKE ?)' => [ "%$o{search}%", "%$o{search}%" ] ) : (),
+    defined $o{meta} ? (
+      't.meta = ?' => $o{meta}?1:0 ) : (),
+  );
+
+  my($r, $np) = $self->dbPage(\%o, q|
+    SELECT t.id, t.meta, t.name, t.description, t.added, t.state, t.c_vns
+      FROM tags t
+      !W
+      ORDER BY !s|,
+    \%where, $o{order}
+  );
+
+  if(@$r && $o{what} =~ /aliases/) {
+    my %r = map {
+      $_->{aliases} = [];
+      ($_->{id}, $_->{aliases})
+    } @$r;
+
+    push @{$r{$_->{tag}}}, $_->{alias} for (@{$self->dbAll(q|
+      SELECT tag, alias FROM tags_aliases WHERE tag IN(!l)|, [ keys %r ]
+    )});
+  }
+
+  if($o{what} =~ /parents\((\d+)\)/) {
+    $_->{parents} = $self->dbTagTree($_->{id}, $1, 0) for(@$r);
+  }
+
+  if($o{what} =~ /childs\((\d+)\)/) {
+    $_->{childs} = $self->dbTagTree($_->{id}, $1, 1) for(@$r);
+  }
+
+  return wantarray ? ($r, $np) : $r;
+}
+
+
+# plain interface to the tag_tree() stored procedure in pgsql
+sub dbTagTree {
+  my($self, $id, $lvl, $dir) = @_;
+  return $self->dbAll('SELECT * FROM tag_tree(?, ?, ?)', $id, $lvl||0, $dir?1:0);
+}
+
+
+# args: tag id, %options->{ columns in the tags table + parents + aliases }
+sub dbTagEdit {
+  my($self, $id, %o) = @_;
+
+  $self->dbExec('UPDATE tags !H WHERE id = ?', {
+    $o{upddate} ? ('added = ?' => time) : (),
+    map { +"$_ = ?" => $o{$_} } qw|name meta description state|
+  }, $id);
+  $self->dbExec('DELETE FROM tags_aliases WHERE tag = ?', $id);
+  $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
+  $self->dbExec('DELETE FROM tags_parents WHERE tag = ?', $id);
+  $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
+  $self->dbExec('DELETE FROM tags_vn WHERE tag = ?', $id) if $o{meta} || $o{state} == 1;
+}
+
+
+# same args as dbTagEdit, without the first tag id
+# returns the id of the new tag
+sub dbTagAdd {
+  my($self, %o) = @_;
+  my $id = $self->dbRow('INSERT INTO tags (name, meta, description, state) VALUES (!l) RETURNING id',
+    [ map $o{$_}, qw|name meta description state| ]
+  )->{id};
+  $self->dbExec('INSERT INTO tags_parents (tag, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
+  $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
+  return $id;
+}
+
+
+sub dbTagMerge {
+  my($self, $id, @merge) = @_;
+  $self->dbExec('UPDATE tags_vn SET tag = ? WHERE tag IN(!l)', $id, \@merge);
+  $self->dbExec('UPDATE tags_aliases SET tag = ? WHERE tag IN(!l)', $id, \@merge);
+  $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_->{name})
+    for (@{$self->dbAll('SELECT name FROM tags WHERE id IN(!l)', \@merge)});
+  $self->dbExec('DELETE FROM tags_parents WHERE tag IN(!l)', \@merge);
+  $self->dbExec('DELETE FROM tags WHERE id IN(!l)', \@merge);
+}
+
+
+# Directly fetch rows from tags_vn
+# Arguments: %options->{ vid uid tag }
+sub dbTagLinks {
+  my($self, %o) = @_;
+  return $self->dbAll(
+    'SELECT tag, vid, uid, vote, spoiler FROM tags_vn !W',
+    { map { +"$_ = ?" => $o{$_} } keys %o }
+  );
+}
+
+
+# Change a user's tags for a VN entry
+# Arguments: uid, vid, [ [ tag, vote, spoiler ], .. ]
+sub dbTagLinkEdit {
+  my($self, $uid, $vid, $tags) = @_;
+  $self->dbExec('DELETE FROM tags_vn WHERE vid = ? AND uid = ?', $vid, $uid);
+  $self->dbExec('INSERT INTO tags_vn (tag, vid, uid, vote, spoiler) VALUES (?, ?, ?, ?, ?)',
+    $_->[0], $vid, $uid, $_->[1], $_->[2] == -1 ? undef : $_->[2]
+  ) for (@$tags);
+}
+
+
+# Fetch all tags related to a VN or User
+# Argument: %options->{ uid vid minrating results what page order }
+# what: vns
+sub dbTagStats {
+  my($self, %o) = @_;
+  $o{results} ||= 10;
+  $o{page}  ||= 1;
+  $o{order} ||= 't.name ASC';
+  $o{what}  ||= '';
+
+  my %where = (
+    $o{uid} ? (
+      'tv.uid = ?' => $o{uid} ) : (),
+    $o{vid} ? (
+      'tv.vid = ?' => $o{vid} ) : (),
+  );
+  my($r, $np) = $self->dbPage(\%o, q|
+    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
+      GROUP BY t.id, t.name
+      !s
+      ORDER BY !s|,
+    \%where, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '',
+    $o{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;
+}
+
+
+# Fetch all VNs from a tag, including VNs from child tags, and provide ratings for them.
+# Argument: %options->{ tag order page results maxspoil }
+sub dbTagVNs {
+  my($self, %o) = @_;
+  $o{order} ||= 'tb.rating DESC';
+  $o{page} ||= 1;
+  $o{results} ||= 10;
+
+  my %where = (
+    'tag = ?' => $o{tag},
+    defined $o{maxspoil} ? (
+      'tb.spoiler <= ?' => $o{maxspoil} ) : (),
+  );
+
+  my($r, $np) = $self->dbPage(\%o, q|
+    SELECT tb.tag, tb.vid, tb.users, tb.rating, tb.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity
+      FROM tags_vn_bayesian tb
+      JOIN vn v ON v.id = tb.vid
+      JOIN vn_rev vr ON vr.id = v.latest
+      !W
+      ORDER BY !s|,
+    \%where, $o{order});
+  return wantarray ? ($r, $np) : $r;
+}
+
+1;
+
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 3a327197..3a93a9c5 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -48,6 +48,8 @@ sub dbUserGet {
       '(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount',
       '(SELECT COUNT(*) FROM threads_posts WHERE uid = u.id) AS postcount',
       '(SELECT COUNT(*) FROM threads_posts WHERE uid = u.id AND num = 1) AS threadcount',
+      '(SELECT COUNT(DISTINCT tag) FROM tags_vn WHERE uid = u.id) AS tagcount',
+      '(SELECT COUNT(DISTINCT vid) FROM tags_vn WHERE uid = u.id) AS tagvncount',
     ) : (),
   );