diff options
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 58 | ||||
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 12 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 10 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 218 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 2 |
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', ) : (), ); |