diff options
-rw-r--r-- | data/docs/1 | 4 | ||||
-rw-r--r-- | data/docs/2 | 13 | ||||
-rw-r--r-- | data/docs/9 | 17 | ||||
-rw-r--r-- | data/global.pl | 14 | ||||
-rw-r--r-- | data/style.css | 95 | ||||
-rw-r--r-- | lib/Multi/IRC.pm | 11 | ||||
-rw-r--r-- | lib/Multi/Maintenance.pm | 23 | ||||
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 58 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 218 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Func.pm | 34 | ||||
-rw-r--r-- | lib/VNDB/Handler/Discussions.pm | 68 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 673 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 14 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 30 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 40 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 34 | ||||
-rw-r--r-- | lib/VNDB/Util/FormHTML.pm | 14 | ||||
-rw-r--r-- | lib/VNDB/Util/LayoutHTML.pm | 4 | ||||
-rw-r--r-- | static/f/forms.js | 194 | ||||
-rw-r--r-- | static/f/script.js | 62 | ||||
-rwxr-xr-x | util/dbgraph.pl | 3 | ||||
-rw-r--r-- | util/dump.sql | 179 | ||||
-rw-r--r-- | util/updates/update_2.3.sql | 189 |
24 files changed, 1766 insertions, 227 deletions
diff --git a/data/docs/1 b/data/docs/1 index 835d2070..c6d7914c 100644 --- a/data/docs/1 +++ b/data/docs/1 @@ -1,4 +1,8 @@ :TITLE:Categories +<div class="warning"> + The category system has been replaced with a new tagging system. This page + is still available for reference, but will be removed in a later site update. +</div> :INC:index diff --git a/data/docs/2 b/data/docs/2 index c6428c55..2f7095df 100644 --- a/data/docs/2 +++ b/data/docs/2 @@ -63,15 +63,10 @@ :SUB:Categories -<p> - It is possible to select relevant categories for visual novels, where the relevance - can be ranked using a number from 0 to 3, where 0 indicates the category does not - apply at all, and 3 means that the category is very dominant in the game.<br /> - See <a href="/d1">Categories</a> for descriptions about each category.<br /> - Just as with the <i>Length</i> field, this information is very subjective. Just - use the category descriptions and common sense to determine which categories to - select and what relevance to apply. -</p> +<i> + The category system has been replaced with tags. This section is a placeholder to preserve + numbering. +</i> :SUB:Image diff --git a/data/docs/9 b/data/docs/9 index fd6da75e..f5ee46ab 100644 --- a/data/docs/9 +++ b/data/docs/9 @@ -5,29 +5,30 @@ :SUB:Introduction <p> VNDB has a nicely integrated discussion board which can be used for, well, - discussions. As we're not using any popular and freely available forum software + discussions. As we're not using any popular or freely available forum software and have instead written something by ourselves, this discussion board has a few slight differences with the popular boards you're used to. </p> -:SUB:Tags +:SUB:Boards <p> - To make sure interested people can find your post, all threads have 'tags' - that define what the discussion is about. It's possible to add more than - one tag to a thread. The following tags can be used: + To make sure interested people can find your post, all threads have relations + to one or more 'boards' that define what the discussion is about. This is similar + to boards on other forums, but here every item in the DB has its own board, and + it's possible to link a thread to more than one board. The following boards can be used: </p> <dl> <dt>db</dt><dd> - VNDB Discussions. This is a general tag for threads not about any specific + VNDB Discussions. This is a general board for threads not about any specific entry in the database. </dd><dt>v#</dt><dd> - For discussions about a particular visual novel. The tag <i>v17</i>, for example, + For discussions about a particular visual novel. The board <i>v17</i>, for example, is used for all threads related to <a href="/v17">v17</a>. </dd><dt>p#</dt><dd> Same as <i>v#</i>, but for producers. </dd><dt>u#</dt><dd> - The <i>u#</i> tag can be used to notify a user on this site about something + The <i>u#</i> board can be used to notify a user on this site about something he/she must see or to discuss about an edit he/she has made. This is similar to the 'private message' feature of most sites, except it's not 'private'... </dd><dt>an</dt><dd> diff --git a/data/global.pl b/data/global.pl index 2bb84e08..739578bf 100644 --- a/data/global.pl +++ b/data/global.pl @@ -26,12 +26,12 @@ our %S = (%S, sharedmem_key => 'VNDB', user_ranks => [ # rankname allowed actions # DB number - [qw| visitor hist |], # 0 - [qw| banned hist |], # 1 - [qw| loser hist board |], # 2 - [qw| user hist board edit |], # 3 - [qw| mod hist board boardmod edit mod lock del |], # 4 - [qw| admin hist board boardmod edit mod lock del usermod |], # 5 + [qw| visitor hist |], # 0 + [qw| banned hist |], # 1 + [qw| loser hist board |], # 2 + [qw| user hist board edit tag |], # 3 + [qw| mod hist board boardmod edit tag mod lock del tagmod |], # 4 + [qw| admin hist board boardmod edit tag mod lock del tagmod usermod |], # 5 ], languages => { cs => q|Czech|, @@ -58,7 +58,7 @@ our %S = (%S, in => 'Individual', ng => 'Amateur group', }, - discussion_tags => { + discussion_boards => { an => 'Announcements', # 0 - usage restricted to boardmods db => 'VNDB Discussions', # 0 v => 'Visual novels', # vid diff --git a/data/style.css b/data/style.css index dbdcbafd..7ee010ef 100644 --- a/data/style.css +++ b/data/style.css @@ -86,6 +86,7 @@ p.locked { font-style: italic; margin: 0!important; } +b.grayedout { font-weight: normal; color: $grayedout$ } #maincontent h2 b { font: 8pt "Tahoma"; font-weight: normal; @@ -510,11 +511,11 @@ div.mainbox.discussions td.tc4 { div.mainbox.discussions a.locked { text-decoration: line-through; } -div.mainbox.discussions td.tags { +div.mainbox.discussions td.boards { padding-top: 0; padding-left: 60px; } -div.mainbox.discussions td.tags a { +div.mainbox.discussions td.boards a { color: $grayedout$!important } div.discussions td.tc2 { width: 50px; } @@ -537,7 +538,7 @@ div.discussions td.tc4 { width: 170px; } div.vndetails { margin: 0 auto; - width: 700px; + width: 800px; } div.vnimg { float: left; @@ -555,13 +556,19 @@ div.vnimg p { padding: 0px; margin: 0; } +.vndesc h2 { + margin: 5px 0 0 0!important; +} +.vndesc p { + padding: 0 0 0 5px; +} img#nsfw_hid { display: none; cursor: pointer; } div.vndetails table { float: left; - width: 430px; + width: 530px; } div.vndetails table td.key { width: 80px; @@ -573,6 +580,7 @@ div.vndetails table dt { div.vndetails table dd { margin-left: 90px; } + .catlvl_0, .catlvl_1, .catlvl_2, .catlvl_3 { font-style: normal; } .catlvl_1 { color: $catlevel1$!important } .catlvl_2 { color: $catlevel2$!important } @@ -590,21 +598,24 @@ div.vndetails td.anime b { font-weight: normal; padding-right: 4px; } -div.vndescription { - clear: left; - padding: 5px 0 0 0; -} -div.vndescription h2 { - margin: 0 30px!important; +div#vntags { + margin: 15px 30px 0 30px; border-top: 1px solid $border$; - padding: 3px 70px; - font-weight: bold!important; + padding: 1px 5% 0 5%; + text-align: center; } -div.vndescription p { - margin: 0 100px!important; +#vntags span { white-space: nowrap; margin-left: 15px; } +#vntags span.hidden { display: none } +#vntags b { color: $grayedout$; font-weight: normal; font-size: 8px } +#tagops { + float: right; + text-align: right; + width: auto; + margin: 0 30px; } +#tagops a { margin: 0 0 0 10px; border: 0; outline: none } +#maincontent #tagops a.tsel { color: $maintext$; } -b.patch { font-weight: normal; color: $grayedout$ } .releases table, #screenshots table { width: 100%; } @@ -937,8 +948,8 @@ ul#catselect li li.exc { background-position: 0px -33px; color: $statnok$; } /***** User VN list browser ******/ -.rlist .relhid { } -.rlist .relhid_but, #relhidall { cursor: pointer } +.relhid_but, #relhidall { cursor: pointer } +.relhid_but i, #relhidall i { font-style: normal } .browse.rlist .tc2 { width: 100px; } .browse.rlist .tc3 { width: 70px; } .browse.rlist .relhid .tc1 { padding-left: 40px; width: 70px; } @@ -965,6 +976,56 @@ ul#catselect li li.exc { background-position: 0px -33px; color: $statnok$; } +/***** Tag page *****/ + +.tagtree { margin-left: 20px; margin-top: -20px; list-style-type: none; } +.tagtree li { float: left; width: 200px; margin-top: 10px; } +.tagtree li li { float: none; width: auto; margin-top: 0; } +.tagtree ul { margin-left: 10px; list-style-type: none; } +.tagvnlist .tc1 { width: 105px; } +.tagvnlist .tc1 i { font-style: normal; font-size: 8px } +.tagvnlist .tc3 { text-align: right; padding: 0; } +.tagvnlist .tc4 { padding: 0; } +.tagvnlist .tc6 { text-align: right; padding-right: 10px; } + +/***** Tag list *****/ + +.browse.taglist .tc1 { width: 80px } + +/***** User tag list *****/ +.browse.tagstats .tc1 { width: 80px } +.browse.tagstats .tc1_2 { padding-left: 15px } + +/***** VN tagmod *****/ + +#jt_box_tags .formtable table td { padding: 1px 5px } +#tagtable tfoot td { padding-top: 20px!important; } +#tagtable .tc2_1 { border-right: 1px solid $border$; border-left: 1px solid $border$; width: 150px; text-align: center } +#tagtable .tc3_1 { border-left: 1px solid $border$; width: 150px; text-align: center } +#tagtable .tc1 { min-width: 200px; border-right: 1px solid $border$ } +#tagtable .tc2 i { font-style: normal; font-size: 8px } +#tagtable .tc2 { padding-left: 30px!important } +#tagtable .tc3 { border-right: 1px solid $border$; padding-right: 30px!important; text-align: right } +#tagtable .tc4 { padding-left: 30px!important; } +#tagtable .tc5 { text-align: right; padding-right: 30px!important; padding-left: 10px!important } +.taglvl { display: block; float: left; width: 8px; height: 12px; border: 1px solid $border$; font-size: 1px; color: $maintext$!important } +.taglvl0 { width: 15px; border: none!important; font-size: 10px; text-align: center; } +div.taglvl0 { font-size: 8px; width: 20px!important } +div.taglvl { border: none!important; width: 10px; height: 14px } +a.taglvl:hover { border-bottom: 1px solid transparent!important } +.taglvlsel.taglvl-3 { background-color: #f00; border-color: #f00 } +.taglvlsel.taglvl-2 { background-color: #f40; border-color: #f40 } +.taglvlsel.taglvl-1 { background-color: #f80; border-color: #f80 } +.taglvlsel.taglvl1 { background-color: #cf0; border-color: #cf0 } +.taglvlsel.taglvl2 { background-color: #8f0; border-color: #8f0 } +.taglvlsel.taglvl3 { background-color: #0f0; border-color: #0f0 } +#jt_box_tags #tagtable .tc5 { padding: 0 } +#tagtable .tc5 select { width: 90px; height: 15px; border: 0; margin: 0; font-size: 11px; background-color: $_blendbg$; text-align: right } +#tagtable .odd .tc5 select { background-color: $secbg$ } + + + + /***** Warning/Notice Box *****/ div.warning, div.notice { diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 224b35d4..e408a3cc 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -57,7 +57,7 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('irc'); - $_[KERNEL]->call(core => register => qr/^ircnotify ([vrpt][0-9]+\.[0-9]+)$/, 'ircnotify'); + $_[KERNEL]->call(core => register => qr/^ircnotify ([vrptg][0-9]+(?:\.[0-9]+)?)$/, 'ircnotify'); $_[HEAP]{irc}->plugin_add( Logger => POE::Component::IRC::Plugin::Logger->new( @@ -167,7 +167,7 @@ sub vndbid { # dest, msg, force for (keys %{$_[HEAP]{log}}); # Four possible options: - # 1. [tvpru]+ -> item/user/thread (nf) + # 1. [tvprug]+ -> item/user/thread/tag (nf) # 2. [vprt]+.+ -> revision/reply (ef) # 3. d+ -> documentation page (nf) # 4. d+.+ -> documentation page # section (sf) @@ -184,7 +184,7 @@ sub vndbid { # dest, msg, force for (split /[, ]/, $m) { next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things push @id, /^(?:.*[^\w]|)([dvprt])([1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, $3 ] # matches 2 and 4 - : /^(?:.*[^\w]|)([dvprtu])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # matches 1 and 3 + : /^(?:.*[^\w]|)([dvprtug])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # matches 1 and 3 } # loop through the matched IDs and search the database @@ -194,13 +194,14 @@ sub vndbid { # dest, msg, force next if $_[HEAP]{log}{$t.$id.'.'.$rev} && !$_[ARG2]; $_[HEAP]{log}{$t.$id.'.'.$rev} = time; - # option 1: item/user/thread - if($t =~ /[vprtu]/ && !$rev) { + # option 1: item/user/thread/tag + if($t =~ /[vprtug]/ && !$rev) { my $s = $Multi::SQL->prepare( $t eq 'v' ? 'SELECT vr.title FROM vn_rev vr JOIN vn v ON v.latest = vr.id WHERE v.id = ?' : $t eq 'u' ? 'SELECT u.username AS title FROM users u WHERE u.id = ?' : $t eq 'p' ? 'SELECT pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = ?' : $t eq 't' ? 'SELECT title FROM threads WHERE id = ?' : + $t eq 'g' ? 'SELECT name AS title FROM tags WHERE id = ?' : 'SELECT rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = ?' ); $s->execute($id); diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index e9c9b337..51c6cec8 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -18,7 +18,7 @@ sub spawn { my $p = shift; POE::Session->create( package_states => [ - $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity |], + $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity tagcache |], ], ); } @@ -26,8 +26,10 @@ sub spawn { sub _start { $_[KERNEL]->alias_set('maintenance'); - $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity))+)$/, 'cmd_maintenance'); - + $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity|tagcache))+)$/, 'cmd_maintenance'); + + # recalculate tag<->vn cache each hour (better do this once every 24 hours when the DB grows) + $_[KERNEL]->post(core => addcron => '0 * * * *', 'maintenance tagcache'); # Perform some maintenance functions every day on 0:00 $_[KERNEL]->post(core => addcron => '0 0 * * *', 'maintenance vncache integrity unkanime vnpopularity'); # update caches and rotate logs every 1st day of the month at 0:05 @@ -64,6 +66,12 @@ sub usercache { FROM changes WHERE requester = users.id GROUP BY requester + ), 0), + c_tags = COALESCE( + (SELECT COUNT(tag) + FROM tags_vn + WHERE uid = users.id + GROUP BY uid ), 0) |); } @@ -132,7 +140,7 @@ sub unkanime { WHERE a.lastfetch < 0|); $q->execute(); my $r = $q->fetchall_arrayref([]); - my %aid = map { + my %aid = map { my $a=$_; $a->[1] => join(',', map { $a->[1] == $_->[1] ? $_->[0] : () } @$r) } @$r; @@ -177,6 +185,13 @@ sub vnpopularity { } +sub tagcache { + my $S = [gettimeofday]; + $Multi::SQL->do(q|SELECT tag_vn_calc()|); + $_[KERNEL]->call(core => log => 3 => '(Re)calculated tags_vn_stored in %.2fs', tv_interval($S)); +} + + 1; diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index 8e476f55..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,24 +128,24 @@ 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 tt - JOIN threads t ON t.id = tt.tid - WHERE tt.type = ? AND tt.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/Tags.pm b/lib/VNDB/DB/Tags.pm new file mode 100644 index 00000000..a24495cf --- /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_stored 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', ) : (), ); diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 8a5c11d4..0a5a9038 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -5,8 +5,8 @@ use strict; use warnings; use YAWF ':html'; use Exporter 'import'; -use POSIX 'strftime'; -our @EXPORT = qw| shorten age date datestr monthstr userstr bb2html gtintype liststat clearfloat cssicon |; +use POSIX 'strftime', 'ceil', 'floor'; +our @EXPORT = qw| shorten age date datestr monthstr userstr bb2html gtintype liststat clearfloat cssicon tagscore|; # I would've done this as a #define if this was C... @@ -156,7 +156,7 @@ sub bb2html { next; } elsif(!grep(/url/, @open) && ( s{^(.*[^\w]|)([tdvpr][1-9][0-9]*)\.([1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2.$3">$2.$3</a>|.$e->($4)}e || - s{^(.*[^\w]|)([tduvpr][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) { + s{^(.*[^\w]|)([tdvprug][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) { $length += length $lit; last if $maxlength && $length > $maxlength; $result .= $_; @@ -238,5 +238,33 @@ sub cssicon { } +# Tag score in html tags, argument: score, users +sub tagscore { + my $s = shift; + div class => 'taglvl', style => sprintf('width: %.0fpx', ($s-floor($s))*10), ' ' if $s < 0 && $s-floor($s) > 0; + for(-3..3) { + div(class => "taglvl taglvl0", sprintf '%.1f', $s), next if !$_; + if($_ < 0) { + if($s > 0 || floor($s) > $_) { + div class => "taglvl taglvl$_", ' '; + } elsif(floor($s) != $_) { + div class => "taglvl taglvl$_ taglvlsel", ' '; + } else { + div class => "taglvl taglvl$_ taglvlsel", style => sprintf('width: %.0fpx', 10-($s-$_)*10), ' '; + } + } else { + if($s < 0 || ceil($s) < $_) { + div class => "taglvl taglvl$_", ' '; + } elsif(ceil($s) != $_) { + div class => "taglvl taglvl$_ taglvlsel", ' '; + } else { + div class => "taglvl taglvl$_ taglvlsel", style => sprintf('width: %.0fpx', 10-($_-$s)*10), ' '; + } + } + } + div class => 'taglvl', style => sprintf('width: %.0fpx', (ceil($s)-$s)*10), ' ' if $s > 0 && ceil($s)-$s > 0; +} + + 1; diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index 5f247bd4..214753e8 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -11,7 +11,7 @@ use VNDB::Func; YAWF::register( qr{t([1-9]\d*)(?:/([1-9]\d*))?} => \&thread, qr{t([1-9]\d*)\.([1-9]\d*)} => \&redirect, - qr{t/(db|an|[vpu])([1-9]\d*)?} => \&tagbrowse, + qr{t/(db|an|[vpu])([1-9]\d*)?} => \&board, qr{t([1-9]\d*)/reply} => \&edit, qr{t([1-9]\d*)\.([1-9]\d*)/edit} => \&edit, qr{t/(db|an|[vpu])([1-9]\d*)?/new} => \&edit, @@ -23,7 +23,7 @@ sub thread { my($self, $tid, $page) = @_; $page ||= 1; - my $t = $self->dbThreadGet(id => $tid, what => 'tagtitles')->[0]; + my $t = $self->dbThreadGet(id => $tid, what => 'boardtitles')->[0]; return 404 if !$t->{id} || $t->{hidden} && !$self->authCan('boardmod'); my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page); @@ -35,9 +35,9 @@ sub thread { h1 $t->{title}; h2 'Posted in'; ul; - for (sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$t->{tags}}) { + for (sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$t->{boards}}) { li; - a href => "/t/$_->{type}", $self->{discussion_tags}{$_->{type}}; + a href => "/t/$_->{type}", $self->{discussion_boards}{$_->{type}}; if($_->{iid}) { txt ' > '; a style => 'font-weight: bold', href => "/t/$_->{type}$_->{iid}", "$_->{type}$_->{iid}"; @@ -129,17 +129,17 @@ sub edit { my($self, $tid, $num) = @_; $num ||= 0; - # in case we start a new thread, parse tag - my $tag = ''; + # in case we start a new thread, parse boards + my $board = ''; if($tid !~ /^\d+$/) { return 404 if $tid =~ /(db|an)/ && $num || $tid =~ /[vpu]/ && !$num; - $tag = $tid.($num||''); + $board = $tid.($num||''); $tid = 0; $num = 0; } # get thread and post, if any - my $t = $tid && $self->dbThreadGet(id => $tid, what => 'tags')->[0]; + my $t = $tid && $self->dbThreadGet(id => $tid, what => 'boards')->[0]; return 404 if $tid && !$t->{id}; my $p = $num && $self->dbPostGet(tid => $tid, num => $num)->[0]; @@ -156,7 +156,7 @@ sub edit { $frm = $self->formValidate( !$tid || $num == 1 ? ( { name => 'title', maxlength => 50 }, - { name => 'tags', maxlength => 50 }, + { name => 'boards', maxlength => 50 }, ) : (), $self->authCan('boardmod') ? ( { name => 'locked', required => 0 }, @@ -166,14 +166,14 @@ sub edit { { name => 'msg', maxlenght => 5000 }, ); - # parse and validate the tags - my @tags; - if(!$frm->{_err} && $frm->{tags}) { - for (split /[ ,]/, $frm->{tags}) { + # parse and validate the boards + my @boards; + if(!$frm->{_err} && $frm->{boards}) { + for (split /[ ,]/, $frm->{boards}) { my($ty, $id) = ($1, $2) if /^([a-z]{1,2})([0-9]*)$/; - push @tags, [ $ty, $id ]; - push @{$frm->{_err}}, [ 'tags', 'wrongtag', $_ ] if - !$ty || !$self->{discussion_tags}{$ty} + push @boards, [ $ty, $id ]; + push @{$frm->{_err}}, [ 'boards', 'wrongboard', $_ ] if + !$ty || !$self->{discussion_boards}{$ty} || $ty eq 'an' && ($id || !$self->authCan('boardmod')) || $ty eq 'db' && $id || $ty eq 'v' && (!$id || !$self->dbVNGet(id => $id)->[0]{id}) @@ -189,7 +189,7 @@ sub edit { if(!$tid || $num == 1) { my %thread = ( title => $frm->{title}, - tags => \@tags, + boards => \@boards, hidden => $frm->{hidden}, locked => $frm->{locked}, ); @@ -217,27 +217,27 @@ sub edit { $frm->{msg} ||= $p->{msg}; $frm->{hidden} = $p->{hidden} if $num != 1 && !exists $frm->{hidden}; if($num == 1) { - $frm->{tags} ||= join ' ', sort map $_->[1]?$_->[0].$_->[1]:$_->[0], @{$t->{tags}}; + $frm->{boards} ||= join ' ', sort map $_->[1]?$_->[0].$_->[1]:$_->[0], @{$t->{boards}}; $frm->{title} ||= $t->{title}; $frm->{locked} = $t->{locked} if !exists $frm->{locked}; $frm->{hidden} = $t->{hidden} if !exists $frm->{hidden}; } } - $frm->{tags} ||= $tag; + $frm->{boards} ||= $board; $frm->{nolastmod} = 1 if $num && $self->authCan('boardmod') && !exists $frm->{nolastmod}; # generate html my $title = !$tid ? 'Start new thread' : !$num ? 'Reply to '.$t->{title} : 'Edit post'; - my $url = !$tid ? "/t/$tag/new" : !$num ? "/t$tid/reply" : "/t$tid.$num/edit"; + my $url = !$tid ? "/t/$board/new" : !$num ? "/t$tid/reply" : "/t$tid.$num/edit"; $self->htmlHeader(title => $title, noindex => 1); $self->htmlForm({ frm => $frm, action => $url }, $title => [ [ static => label => 'Username', content => userstr($self->authInfo->{id}, $self->authInfo->{username}) ], !$tid || $num == 1 ? ( [ input => short => 'title', name => 'Thread title' ], - [ input => short => 'tags', name => 'Tags' ], - [ static => content => 'Read <a href="/d9.2">d9.2</a> for information about how to use tags' ], + [ input => short => 'boards', name => 'Board(s)' ], + [ static => content => 'Read <a href="/d9.2">d9.2</a> for information about how to specify boards' ], $self->authCan('boardmod') ? ( [ check => name => 'Locked', short => 'locked' ], ) : (), @@ -257,7 +257,7 @@ sub edit { } -sub tagbrowse { +sub board { my($self, $type, $iid) = @_; $iid ||= ''; return 404 if $type =~ /(db|an)/ && $iid; @@ -273,14 +273,14 @@ sub tagbrowse { $self->dbVNGet(id => $iid)->[0]; return 404 if $iid && !$obj; my $ititle = $obj && ($obj->{title}||$obj->{name}||$obj->{username}); - my $title = !$obj ? $self->{discussion_tags}{$type} : 'Related discussions for '.$ititle; + my $title = !$obj ? $self->{discussion_boards}{$type} : 'Related discussions for '.$ititle; my($list, $np) = $self->dbThreadGet( type => $type, $iid ? (iid => $iid) : (), results => 50, page => $f->{p}, - what => 'firstpost lastpost tagtitles', + what => 'firstpost lastpost boardtitles', order => $type eq 'an' ? 't.id DESC' : 'tpl.date DESC', ); @@ -292,7 +292,7 @@ sub tagbrowse { p; a href => '/t', 'Discussion board'; txt ' > '; - a href => "/t/$type", $self->{discussion_tags}{$type}; + a href => "/t/$type", $self->{discussion_boards}{$type}; if($iid) { txt ' > '; a style => 'font-weight: bold', href => "/t/$type$iid", "$type$iid"; @@ -324,7 +324,7 @@ sub index { div class => 'mainbox'; h1 'Discussion board index'; p class => 'browseopts'; - a href => '/t/'.$_, $self->{discussion_tags}{$_} + a href => '/t/'.$_, $self->{discussion_boards}{$_} for (qw|an db v p u|); end; end; @@ -334,11 +334,11 @@ sub index { type => $_, results => 5, page => 1, - what => 'firstpost lastpost tagtitles', + what => 'firstpost lastpost boardtitles', order => 'tpl.date DESC', ); h1 class => 'boxtitle'; - a href => "/t/$_", $self->{discussion_tags}{$_}; + a href => "/t/$_", $self->{discussion_boards}{$_}; end; _threadlist($self, $list, {p=>1}, 0, "/t"); } @@ -377,17 +377,17 @@ sub _threadlist { end; end; Tr $n % 2 ? ( class => 'odd' ) : (); - td colspan => 4, class => 'tags'; + td colspan => 4, class => 'boards'; txt ' > '; my $i = 1; - for(sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$o->{tags}}) { + for(sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$o->{boards}}) { last if $i++ > 5; txt ', ' if $i > 2; a href => "/t/$_->{type}".($_->{iid}||''), - title => $_->{original}||$self->{discussion_tags}{$_->{type}}, - shorten $_->{title}||$self->{discussion_tags}{$_->{type}}, 30; + title => $_->{original}||$self->{discussion_boards}{$_->{type}}, + shorten $_->{title}||$self->{discussion_boards}{$_->{type}}, 30; } - txt ', ...' if @{$o->{tags}} > 5; + txt ', ...' if @{$o->{boards}} > 5; end; end; } diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm new file mode 100644 index 00000000..0be1ba10 --- /dev/null +++ b/lib/VNDB/Handler/Tags.pm @@ -0,0 +1,673 @@ + +package VNDB::Handler::Tags; + + +use strict; +use warnings; +use YAWF ':html', ':xml'; +use VNDB::Func; + + +YAWF::register( + qr{g([1-9]\d*)}, \&tagpage, + qr{g([1-9]\d*)/(edit)}, \&tagedit, + qr{g([1-9]\d*)/(add)}, \&tagedit, + qr{g/new}, \&tagedit, + qr{g/list}, \&taglist, + qr{v([1-9]\d*)/tagmod}, \&vntagmod, + qr{u([1-9]\d*)/tags}, \&usertags, + qr{g}, \&tagindex, + qr{xml/tags\.xml}, \&tagxml, +); + + +sub tagpage { + my($self, $tag) = @_; + + my $t = $self->dbTagGet(id => $tag, what => 'parents(0) childs(2) aliases')->[0]; + return 404 if !$t; + + my $f = $self->formValidate( + { name => 's', required => 0, default => 'score', enum => [ qw|score title rel pop| ] }, + { name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, + { name => 'p', required => 0, default => 1, template => 'int' }, + { name => 'm', required => 0, default => -1, enum => [qw|0 1 2|] }, + ); + return 404 if $f->{_err}; + my $tagspoil = $self->reqCookie('tagspoil'); + $f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 1 if $f->{m} == -1; + + my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbTagVNs( + tag => $tag, + order => {score=>'tb.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC'), + page => $f->{p}, + results => 50, + maxspoil => $f->{m}, + ); + + my $title = ($t->{meta} ? 'Meta tag: ' : 'Tag: ').$t->{name}; + $self->htmlHeader(title => $title); + $self->htmlMainTabs('g', $t); + + if($t->{state} != 2) { + div class => 'mainbox'; + h1 "Tag: $t->{name}"; + if($t->{state} == 1) { + div class => 'warning'; + h2 'Tag deleted'; + p; + lit qq|This tag has been removed from the database, and cannot be used or re-added.|. + qq| File a request on the <a href="/t/db">discussion board</a> if you disagree with this.|; + end; + end; + } else { + div class => 'notice'; + h2 'Waiting for approval'; + p 'This tag is waiting for a moderator to approve it. You can still use it to tag VNs as you would with a normal tag.'; + end; + } + end; + return $self->htmlFooter if $t->{state} == 1 && !$self->authCan('tagmod'); + } + + div class => 'mainbox'; + a class => 'addnew', href => "/g$tag/add", ($self->authCan('tagmod')?'Create':'Request').' child tag' if $self->authCan('tag'); + h1 $title; + + p; + my @p = @{$t->{parents}}; + my @r; + for (0..$#p) { + if($_ && $p[$_-1]{lvl} < $p[$_]{lvl}) { + pop @r for (1..($p[$_]{lvl}-$p[$_-1]{lvl})); + } + if($_ < $#p && $p[$_+1]{lvl} < $p[$_]{lvl}) { + push @r, $p[$_]; + } elsif($#p == $_ || $p[$_+1]{lvl} >= $p[$_]{lvl}) { + a href => '/g', 'Tags'; + for ($p[$_], reverse @r) { + txt ' > '; + a href => "/g$_->{tag}", $_->{name}; + } + txt " > $t->{name}\n"; + } + } + if(!@p) { + a href => '/g', 'Tags'; + txt " > $t->{name}\n"; + } + end; + + if($t->{description}) { + p class => 'center'; + lit bb2html $t->{description}; + end; + } + if(@{$t->{aliases}}) { + p class => 'center'; + b "Aliases:\n"; + txt "$_\n" for (@{$t->{aliases}}); + end; + } + end; + + _childtags($self, $t) if @{$t->{childs}}; + _vnlist($self, $t, $f, $list, $np) if !$t->{meta} && $t->{state} == 2; + + $self->htmlFooter; +} + +# used for on both /g and /g+ +sub _childtags { + my($self, $t, $index) = @_; + + my @l = @{$t->{childs}}; + my @tags; + for (0..$#l) { + if($l[$_]{lvl} == $l[0]{lvl}) { + $l[$_]{childs} = []; + push @tags, $l[$_]; + } else { + push @{$tags[$#tags]{childs}}, $l[$_]; + } + } + + div class => 'mainbox'; + if(!$index) { + h1 'Child tags'; + } else { + h1 'Tag tree'; + } + ul class => 'tagtree'; + for my $p (sort { @{$b->{childs}} <=> @{$a->{childs}} } @tags) { + li; + a href => "/g$p->{tag}", $p->{name}; + b class => 'grayedout', " ($p->{c_vns})" if $p->{c_vns}; + end, next if !@{$p->{childs}}; + ul; + for (0..$#{$p->{childs}}) { + last if $_ >= 5 && @{$p->{childs}} > 6; + li; + txt '> '; + a href => "/g$p->{childs}[$_]{tag}", $p->{childs}[$_]{name}; + b class => 'grayedout', " ($p->{childs}[$_]{c_vns})" if $p->{childs}[$_]{c_vns}; + end; + } + if(@{$p->{childs}} > 6) { + li; + txt '> '; + a href => "/g$p->{tag}", style => 'font-style: italic', sprintf '%d more tags...', @{$p->{childs}}-5; + end; + } + end; + end; + } + end; + clearfloat; + br; + end; +} + +sub _vnlist { + my($self, $t, $f, $list, $np) = @_; + div class => 'mainbox'; + h1 'Visual novels'; + p class => 'browseopts'; + a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", 'Hide spoilers'; + a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", 'Show minor spoilers'; + a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", 'Show major spoilers'; + end; + if(!@$list) { + p "\n\nThis tag has not been linked to any visual novels yet, or they were hidden because of the spoiler settings."; + } + end; + return if !@$list; + $self->htmlBrowse( + class => 'tagvnlist', + items => $list, + options => $f, + nextpage => $np, + pageurl => "/g$t->{id}?m=$f->{m};o=$f->{o};s=$f->{s}", + sorturl => "/g$t->{id}?m=$f->{m}", + header => [ + [ 'Score', 'score' ], + [ 'Title', 'title' ], + [ '', 0 ], + [ '', 0 ], + [ 'Released', 'rel' ], + [ 'Popularity', 'pop' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr $n % 2 ? (class => 'odd') : (); + td class => 'tc1'; + tagscore $l->{rating}; + i sprintf '(%d)', $l->{users}; + end; + td class => 'tc2'; + a href => '/v'.$l->{vid}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100; + end; + td class => 'tc3'; + $_ ne 'oth' && cssicon $_, $self->{platforms}{$_} + for (sort split /\//, $l->{c_platforms}); + end; + td class => 'tc4'; + cssicon "lang $_", $self->{languages}{$_} + for (reverse sort split /\//, $l->{c_languages}); + end; + td class => 'tc5'; + lit monthstr $l->{c_released}; + end; + td class => 'tc6', sprintf '%.2f', $l->{c_popularity}*100; + end; + } + ); +} + + +sub tagedit { + my($self, $tag, $act) = @_; + + my($frm, $par); + if($act && $act eq 'add') { + $par = $self->dbTagGet(id => $tag)->[0]; + return 404 if !$par; + $frm->{parents} = $tag; + $tag = undef; + } + + return $self->htmlDenied if !$self->authCan('tag') || $tag && !$self->authCan('tagmod'); + + my $t = $tag && $self->dbTagGet(id => $tag, what => 'parents(1) aliases')->[0]; + return 404 if $tag && !$t; + + if($self->reqMethod eq 'POST') { + $frm = $self->formValidate( + { name => 'name', required => 1, maxlength => 250 }, + { name => 'state', required => 0, default => 0, enum => [ 0..2 ] }, + { name => 'meta', required => 0, default => 0 }, + { name => 'alias', required => 0, maxlength => 1024, default => '' }, + { name => 'description', required => 0, maxlength => 1024, default => '' }, + { name => 'parents', required => 0, default => '' }, + { name => 'merge', required => 0, default => '' }, + ); + my @aliases = split /[\t\s]*\n[\t\s]*/, $frm->{alias}; + my @parents = split /[\t\s]*,[\t\s]*/, $frm->{parents}; + my @merge = split /[\t\s]*,[\t\s]*/, $frm->{merge}; + if(!$frm->{_err}) { + my $c = $self->dbTagGet(name => $frm->{name}, noid => $tag); + push @{$frm->{_err}}, [ 'name', 'tagexists', $c->[0] ] if @$c; + for (@aliases) { + $c = $self->dbTagGet(name => $_, noid => $tag); + push @{$frm->{_err}}, [ 'alias', 'tagexists', $c->[0] ] if @$c; + } + for(@parents, @merge) { + my $c = $self->dbTagGet(name => $_, noid => $tag); + push @{$frm->{_err}}, [ 'parents', 'func', [ 0, "Tag '$_' not found." ]] if !@$c; + $_ = $c->[0]{id}; + } + } + if(!$frm->{_err}) { + $frm->{state} = $frm->{meta} = 0 if !$self->authCan('tagmod'); + my %opts = ( + name => $frm->{name}, + state => $frm->{state}, + description => $frm->{description}, + meta => $frm->{meta}?1:0, + aliases => \@aliases, + parents => \@parents, + ); + if(!$tag) { + $tag = $self->dbTagAdd(%opts); + $self->multiCmd("ircnotify g$tag"); + } else { + $self->dbTagEdit($tag, %opts, upddate => $frm->{state} == 2 && $t->{state} != 2); + } + $self->dbTagMerge($tag, @merge) if $self->authCan('tagmod') && @merge; + $self->resRedirect("/g$tag", 'post'); + return; + } + } + + if($tag) { + $frm->{$_} ||= $t->{$_} for (qw|name meta description state|); + $frm->{alias} ||= join "\n", @{$t->{aliases}}; + $frm->{parents} ||= join ', ', map $_->{name}, @{$t->{parents}}; + } + + my $title = $par ? "Add child tag to $par->{name}" : $tag ? "Edit tag: $t->{name}" : 'Add new tag'; + $self->htmlHeader(title => $title, noindex => 1); + $self->htmlMainTabs('g', $par || $t, 'edit') if $t || $par; + + if(!$self->authCan('tagmod')) { + div class => 'mainbox'; + h1 'Requesting new tag'; + div class => 'notice'; + h2 'Your tag must be approved'; + p 'Because all tags have to be approved by moderators, it can take a while before it '. + 'will show up in the tag list or on visual novel pages. You can still vote on tag even if '. + 'it has not been approved yet, though.'; + end; + end; + } + + $self->htmlForm({ frm => $frm, action => $par ? "/g$par->{id}/add" : $tag ? "/g$tag/edit" : '/g/new' }, $title => [ + [ input => short => 'name', name => 'Primary name' ], + $self->authCan('tagmod') ? ( + [ select => short => 'state', name => 'State', options => [ + [ 0, 'Awaiting moderation' ], [ 1, 'Deleted/hidden' ], [ 2, 'Approved' ] ] ], + [ checkbox => short => 'meta', name => 'This is a meta-tag (only to be used as parent for other tags, not for linking to VN entries)' ], + $tag ? + [ static => content => 'WARNING: Checking this option or selecting "Deleted" as state will permanently delete all existing VN relations!' ] : (), + ) : (), + [ textarea => short => 'alias', name => "Aliases\n(separated by newlines)", cols => 30, rows => 4 ], + [ textarea => short => 'description', name => 'Description' ], + [ static => content => 'What should the tag be used for? Having a good description helps users choose which tags to link to a VN.' ], + [ input => short => 'parents', name => 'Parent tags' ], + [ static => content => "Comma separated list of tag names to be used as parent for this tag." ], + $self->authCan('tagmod') ? ( + [ part => title => 'Merge tags' ], + [ input => short => 'merge', name => 'Tags to merge' ], + [ static => content => 'Comma separated list of tag names to merge into this one.' + .' All votes and aliases/names will be moved over to this tag, and the old tags will be deleted.' + .' Just leave this field empty if you don\'t intend to do a merge.' + .'<br />WARNING: this action cannot be undone!' ], + ) : (), + ]); + $self->htmlFooter; +} + + +sub taglist { + my $self = shift; + + my $f = $self->formValidate( + { name => 's', required => 0, default => 'name', enum => ['added', 'name'] }, + { name => 'o', required => 0, default => 'a', enum => ['a', 'd'] }, + { name => 'p', required => 0, default => 1, template => 'int' }, + { name => 't', required => 0, default => -1, enum => [ -1..2 ] }, + { name => 'q', required => 0, default => '' }, + ); + $f->{t} = 0 if !$self->authCan('tagmod') && $f->{t} == 1; + return 404 if $f->{_err}; + + my($t, $np) = $self->dbTagGet( + order => $f->{s}.($f->{o}eq'd'?' DESC':' ASC'), + page => $f->{p}, + results => 50, + $f->{t} != -1 || $self->authCan('tagmod') ? ( + state => $f->{t} ) : (), + search => $f->{q}, + ); + + my $title = $f->{t} == -1 ? 'Browse tags' : $f->{t} == 0 ? 'Tags awaiting moderation' : $f->{t} == 1 ? 'Deleted tags' : 'All visible tags'; + $self->htmlHeader(title => $title); + div class => 'mainbox'; + h1 $title; + form class => 'search', action => '/g/list', 'accept-charset' => 'UTF-8', method => 'get'; + fieldset; + input type => 'hidden', name => 't', value => $f->{t}; + input type => 'text', name => 'q', id => 'q', class => 'text', value => $f->{q}; + input type => 'submit', class => 'submit', value => 'Search!'; + end; + end; + p class => 'browseopts'; + a href => "/g/list?q=$f->{q};t=-1", $f->{t} == -1 ? (class => 'optselected') : (), 'All'; + a href => "/g/list?q=$f->{q};t=0", $f->{t} == 0 ? (class => 'optselected') : (), 'Awaiting moderation'; + a href => "/g/list?q=$f->{q};t=1", $f->{t} == 1 ? (class => 'optselected') : (), 'Deleted' if $self->authCan('tagmod'); + a href => "/g/list?q=$f->{q};t=2", $f->{t} == 2 ? (class => 'optselected') : (), 'Accepted'; + end; + if(!@$t) { + p 'No results found'; + } + end; + if(@$t) { + $self->htmlBrowse( + class => 'taglist', + options => $f, + nextpage => $np, + items => $t, + pageurl => "/g/list?t=$f->{t};q=$f->{q};s=$f->{s};o=$f->{o}", + sorturl => "/g/list?t=$f->{t};q=$f->{q}", + header => [ + [ 'Created', 'added' ], + [ 'Tag', 'name' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr $n % 2 ? (class => 'odd') : (); + td class => 'tc1', age $l->{added}; + td class => 'tc3'; + a href => "/g$l->{id}", $l->{name}; + if($f->{t} == -1) { + b class => 'grayedout', ' awaiting moderation' if $l->{state} == 0; + b class => 'grayedout', ' deleted' if $l->{state} == 1; + } + end; + end; + } + ); + } + $self->htmlFooter; +} + + +sub vntagmod { + my($self, $vid) = @_; + + my $v = $self->dbVNGet(id => $vid)->[0]; + return 404 if !$v; + + return $self->htmlDenied if !$self->authCan('tag'); + + if($self->reqMethod eq 'POST') { + my $frm = $self->formValidate( + { name => 'taglinks', required => 0, default => '', maxlength => 10240, regex => [ qr/^[1-9][0-9]*,-?[1-3],-?[0-2]( [1-9][0-9]*,-?[1-3],-?[0-2])*$/, 'meh' ] } + ); + return 404 if $frm->{_err}; + $self->dbTagLinkEdit($self->authInfo->{id}, $vid, [ map [ split /,/ ], split / /, $frm->{taglinks}]); + } + + my $my = $self->dbTagLinks(vid => $vid, uid => $self->authInfo->{id}); + my $tags = $self->dbTagStats(vid => $vid, result => 9999); + + my $frm; + + $self->htmlHeader(title => "Add/remove tags for $v->{title}", noindex => 1, js => 'forms'); + $self->htmlMainTabs('v', $v, 'tagmod'); + div class => 'mainbox'; + h1 "Add/remove tags for $v->{title}"; + div class => 'notice'; + h2 'Tagging'; + ul; + li "Don't forget to hit the submit button on the bottom of the page after changing anything here!"; + li 'Tag guidelines?'; + li 'Some tag information on the site is cached, it can take up to an hour for your changes to be visible everywhere.'; + end; + end; + end; + $self->htmlForm({ frm => $frm, action => "/v$vid/tagmod", hitsubmit => 1 }, 'Tags' => [ + [ hidden => short => 'taglinks', value => '' ], + [ static => nolabel => 1, content => sub { + table id => 'tagtable'; + thead; + Tr; + td ''; + td colspan => 2, class => 'tc2_1', 'Others'; + td colspan => 2, class => 'tc3_1', 'You'; + end; + Tr; + my $i=0; + td class => 'tc'.++$i, $_ for(qw|Tag Rating Spoiler Rating Spoiler|); + end; + end; + tfoot; Tr; + td colspan => 5; + input type => 'text', class => 'text', name => 'addtag', value => ''; + input type => 'button', class => 'submit', value => 'Add tag'; + br; + p; + lit 'Check the <a href="/g">tag list</a> to browse all available tags.'. + '<br />Can\'t find what you\'re looking for? <a href="/g/new">Request a new tag</a>.'; + end; + end; + end; end; + tbody; + for my $t (sort { $a->{name} cmp $b->{name} } @$tags) { + my $m = (grep $_->{tag} == $t->{id}, @$my)[0] || {}; + Tr; + td class => 'tc1'; + a href => "/g$t->{id}", $t->{name}; + end; + td class => 'tc2'; + tagscore !$m->{vote} ? $t->{rating} : $t->{cnt} == 1 ? 0 : ($t->{rating}*$t->{cnt} - $m->{vote}) / ($t->{cnt}-1); + i ' ('.($t->{cnt} - ($m->{vote} ? 1 : 0)).')'; + end; + td class => 'tc3', sprintf '%.2f', $t->{spoiler}; + td class => 'tc4', $m->{vote}||0; + td class => 'tc5', defined $m->{spoiler} ? $m->{spoiler} : -1; + end; + } + end; + end; + } ], + ]); + $self->htmlFooter; +} + + +sub usertags { + my($self, $uid) = @_; + + my $u = $self->dbUserGet(uid => $uid)->[0]; + return 404 if !$u; + + my $f = $self->formValidate( + { name => 's', required => 0, default => 'cnt', enum => [ qw|cnt name| ] }, + { name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, + { name => 'p', required => 0, default => 1, template => 'int' }, + ); + return 404 if $f->{_err}; + + # TODO: might want to use AJAX to load the VN list on request + my($list, $np) = $self->dbTagStats( + uid => $uid, + page => $f->{p}, + order => ($f->{s}eq'cnt'?'COUNT(*)':'name').($f->{o}eq'a'?' ASC':' DESC'), + what => 'vns', + ); + + $self->htmlHeader(title => "Tags by $u->{username}", noindex => 1); + $self->htmlMainTabs('u', $u, 'tags'); + div class => 'mainbox'; + h1 "Tags by $u->{username}"; + if(@$list) { + p 'Warning: spoilery tags are not hidden in this list!'; + } else { + p "$u->{username} doesn't seem to have used the tagging system yet..."; + } + end; + + if(@$list) { + $self->htmlBrowse( + class => 'tagstats', + options => $f, + nextpage => $np, + items => $list, + pageurl => "/u$u->{id}/tags?s=$f->{s};o=$f->{o}", + sorturl => "/u$u->{id}/tags", + header => [ + sub { + td class => 'tc1'; + b id => 'relhidall'; + lit '<i>▸</i> #VNs '; + end; + lit $f->{s} eq 'cnt' && $f->{o} eq 'a' ? "\x{25B4}" : qq|<a href="/u$u->{id}/tags?o=a;s=cnt">\x{25B4}</a>|; + lit $f->{s} eq 'cnt' && $f->{o} eq 'd' ? "\x{25BE}" : qq|<a href="/u$u->{id}/tags?o=d;s=cnt">\x{25BE}</a>|; + end; + }, + [ 'Tag', 'name' ], + [ ' ', '' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr $n % 2 ? (class => 'odd') : (); + td class => 'tc1 relhid_but', id => "tag$l->{id}"; + lit "<i>▸</i> $l->{cnt}"; + end; + td class => 'tc2', colspan => 2; + a href => "/g$l->{id}", $l->{name}; + end; + end; + for(@{$l->{vns}}) { + Tr class => "relhid tag$l->{id}"; + td class => 'tc1_1'; + tagscore $_->{vote}; + end; + td class => 'tc1_2'; + a href => "/v$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 50; + end; + td class => 'tc1_3', !defined $_->{spoiler} ? ' ' : ['No spoiler', 'Minor spoiler', 'Major spoiler']->[$_->{spoiler}]; + end; + } + }, + ); + } + $self->htmlFooter; +} + + +sub tagindex { + my $self = shift; + + $self->htmlHeader(title => 'Browse tags'); + div class => 'mainbox'; + h1 'Search tags'; + form class => 'search', action => '/g/list', 'accept-charset' => 'UTF-8', method => 'get'; + fieldset; + input type => 'text', name => 'q', id => 'q', class => 'text'; + input type => 'submit', class => 'submit', value => 'Search!'; + end; + end; + end; + + my $t = $self->dbTagTree(0, 2, 1); + _childtags($self, {childs => $t}, 1); + + # Recently added + div class => 'mainbox threelayout'; + a class => 'right', href => '/g/list', 'Browse all tags'; + my $r = $self->dbTagGet(order => 'added DESC', results => 10); + h1 'Recently added'; + ul; + for (@$r) { + li; + txt age $_->{added}; + txt ' '; + a href => "/g$_->{id}", $_->{name}; + end; + } + end; + end; + + # Popular + div class => 'mainbox threelayout'; + $r = $self->dbTagGet(order => 'c_vns DESC', meta => 0, results => 10); + h1 'Popular tags'; + ul; + for (@$r) { + li; + a href => "/g$_->{id}", $_->{name}; + txt " ($_->{c_vns})"; + end; + } + end; + end; + + # Moderation queue + div class => 'mainbox threelayout last'; + a class => 'right', href => '/g/list?t=0;o=d;s=added', 'Moderation queue'; + h1 'Awaiting moderation'; + $r = $self->dbTagGet(state => 0, order => 'added DESC', results => 10); + if(@$r) { + ul; + for (@$r) { + li; + txt age $_->{added}; + txt ' '; + a href => "/g$_->{id}", $_->{name}; + end; + } + end; + } else { + p 'Moderation queue empty! yay!'; + } + end; + clearfloat; + $self->htmlFooter; +} + + +sub tagxml { + my $self = shift; + + my $q = $self->formValidate({ name => 'q', maxlength => 500 }); + return 404 if $q->{_err}; + $q = $q->{q}; + + my($list, $np) = $self->dbTagGet( + $q =~ /^g([1-9]\d*)/ ? (id => $1) : $q =~ /^name:(.+)$/ ? (name => $1) : (search => $q), + results => 10, + page => 1, + ); + + $self->resHeader('Content-type' => 'text/xml; charset=UTF-8'); + xml; + tag 'tags', more => $np ? 'yes' : 'no', query => $q; + for(@$list) { + tag 'item', id => $_->{id}, meta => $_->{meta} ? 'yes' : 'no', state => $_->{state}, $_->{name}; + } + end; +} + +1; diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index f34943ba..3bbcbf78 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -86,6 +86,12 @@ sub userpage { end; Tr ++$i % 2 ? (class => 'odd') : (); + td 'Tags'; + td !$u->{c_tags} ? '-' : sprintf '%d votes on %d distinct tags and %d visual novels', + $u->{c_tags}, $u->{tagcount}, $u->{tagvncount}; + end; + + Tr ++$i % 2 ? (class => 'odd') : (); td 'List stats'; td !$u->{show_list} ? 'hidden' : sprintf '%d release%s of %d visual novel%s', @@ -417,7 +423,7 @@ sub list { my($self, $char) = @_; my $f = $self->formValidate( - { name => 's', required => 0, default => 'username', enum => [ qw|username registered votes changes| ] }, + { name => 's', required => 0, default => 'username', enum => [ qw|username registered votes changes tags| ] }, { name => 'o', required => 0, default => 'a', enum => [ 'a','d' ] }, { name => 'p', required => 0, default => 1, template => 'int' }, ); @@ -435,7 +441,7 @@ sub list { end; my($list, $np) = $self->dbUserGet( - order => ($f->{s} eq 'changes' ? 'c_' : $f->{s} eq 'votes' ? 'NOT show_list, c_' : '').$f->{s}.($f->{o} eq 'a' ? ' ASC' : ' DESC'), + order => ($f->{s} eq 'changes' || $f->{s} eq 'tags' ? 'c_' : $f->{s} eq 'votes' ? 'NOT show_list, c_' : '').$f->{s}.($f->{o} eq 'a' ? ' ASC' : ' DESC'), $char ne 'all' ? ( firstchar => $char ) : (), results => 50, @@ -453,6 +459,7 @@ sub list { [ 'Registered', 'registered' ], [ 'Votes', 'votes' ], [ 'Edits', 'changes' ], + [ 'Tags', 'tags' ], ], row => sub { my($s, $n, $l) = @_; @@ -468,6 +475,9 @@ sub list { td class => 'tc4'; lit !$l->{c_changes} ? 0 : qq|<a href="/u$l->{id}/hist">$l->{c_changes}</a>|; end; + td class => 'tc5'; + lit !$l->{c_tags} ? 0 : qq|<a href="/u$l->{id}/tags">$l->{c_tags}</a>|; + end; end; }, ); diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index 6790ae6e..8363b500 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -27,7 +27,6 @@ sub edit { my %b4 = !$vid ? () : ( (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw|), anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}), - categories => join(',', map $_->[0].$_->[1], sort { $a->[0] cmp $b->[0] } @{$v->{categories}}), relations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{title}, sort { $a->{id} <=> $b->{id} } @{$v->{relations}}), screenshots => join(' ', map sprintf('%d,%d,%d', $_->{id}, $_->{nsfw}?1:0, $_->{rid}), @{$v->{screenshots}}), ); @@ -45,7 +44,6 @@ sub edit { { name => 'l_renai', required => 0, default => '', maxlength => 100 }, { name => 'l_vnn', required => 0, default => 0, template => 'int' }, { name => 'anime', required => 0, default => '' }, - { name => 'categories', required => 0, default => '', maxlength => 1000 }, { name => 'img_nsfw', required => 0, default => 0 }, { name => 'relations', required => 0, default => '', maxlength => 5000 }, { name => 'screenshots', required => 0, default => '', maxlength => 1000 }, @@ -58,7 +56,6 @@ sub edit { if(!$frm->{_err}) { # parse and re-sort fields that have multiple representations of the same information my $anime = [ grep /^[0-9]+$/, split /[ ,]+/, $frm->{anime} ]; - my $categories = [ map { [ substr($_,0,3), substr($_,3,1) ] } split /,/, $frm->{categories} ]; my $relations = [ map { /^([0-9]+),([0-9]+),(.+)$/ && (!$vid || $2 != $vid) ? [ $1, $2, $3 ] : () } split /\|\|\|/, $frm->{relations} ]; my $screenshots = [ map /^[0-9]+,[01],[0-9]+$/ ? [split /,/] : (), split / +/, $frm->{screenshots} ]; @@ -75,7 +72,7 @@ sub edit { my %args = ( (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|), anime => $anime, - categories => $categories, + categories => $v->{categories}, relations => $relations, image => $image, screenshots => $screenshots, @@ -181,31 +178,6 @@ sub _form { |], ], - 'Categories' => [ - [ hidden => short => 'categories' ], - [ static => nolabel => 1, content => sub { - lit 'Please read the <a href="/d1">category descriptions</a> before modifying categories!<br /><br />'; - ul; - for my $c (qw| e g t p h l s |) { - $c !~ /[thl]/ ? li : br; - txt $self->{categories}{$c}[0]; - a href => "/d1#$self->{categories}{$c}[2]", class => 'help', '?'; - ul; - for (sort keys %{$self->{categories}{$c}[1]}) { - li; - a href => "#", id => "cat_$c$_"; - b id => "b_$c$_", '-'; - txt ' '.$self->{categories}{$c}[1]{$_}; - end; - end; - } - end; - end if $c !~ /[gph]/; - } - end; - }], - ], - 'Image' => [ [ static => nolabel => 1, content => sub { div class => 'img'; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 6c336160..56fcc70d 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -125,16 +125,36 @@ sub page { _anime($self, \$i, $v) if @{$v->{anime}}; _useroptions($self, \$i, $v) if $self->authInfo->{id}; - end; - end; + Tr; + td class => 'vndesc', colspan => 2; + h2 'Description'; + p; + lit bb2html $v->{desc}; + end; + end; + end; - # description - div class => 'vndescription'; - h2 'Description'; - p; - lit bb2html $v->{desc}; end; end; + clearfloat; + + # tags + my $t = $self->dbTagStats(vid => $v->{id}, order => 'avg(tv.vote) DESC', minrating => 0); + if(@$t) { + div id => 'tagops'; + a href => '#', 'hide spoilers'; + a href => '#', class => 'tsel', 'show minor spoilers'; + a href => '#', 'all tags'; + end; + div id => 'vntags'; + for (@$t) { + span class => sprintf 'tagspl%.0f %s', $_->{spoiler}, $_->{spoiler} > 1 ? 'hidden' : ''; + a href => "/g$_->{id}", style => sprintf('font-size: %dpx', $_->{rating}*3.5+6), $_->{name}; + b class => 'grayedout', sprintf ' %.1f', $_->{rating}; + end; + } + end; + } end; _releases($self, $v, $r); @@ -252,7 +272,8 @@ sub _categories { Tr ++$$i % 2 ? (class => 'odd') : (); td 'Categories'; td; - dl; + dl id => 'vncats', style => 'display: none'; + dt 'Note:'; dd "The category system is outdated, please use tags instead.\n\n"; for (@cat) { dt shift(@$_).':'; dd; @@ -260,6 +281,7 @@ sub _categories { end; } end; + a href => '#', onclick => "document.getElementById('vncats').style.display='';this.style.display='none';return false", 'Show categories'; end; end; } @@ -407,7 +429,7 @@ sub _releases { end; td class => 'tc4'; a href => "/r$rel->{id}", title => $rel->{original}||$rel->{title}, $rel->{title}; - b class => 'patch', ' (patch)' if $rel->{patch}; + b class => 'grayedout', ' (patch)' if $rel->{patch}; end; td class => 'tc5'; if($self->authInfo->{id}) { diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 6b6725c6..6e50632d 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -17,18 +17,22 @@ our @EXPORT = qw| # generates the "main tabs". These are the commonly used tabs for # 'objects', i.e. VN/producer/release entries and users -# Arguments: u/v/r/p, object, currently selected item (empty=main) +# Arguments: u/v/r/p/g, object, currently selected item (empty=main) sub htmlMainTabs { my($self, $type, $obj, $sel) = @_; $sel ||= ''; my $id = $type.$obj->{id}; + return if $type eq 'g' && !$self->authCan('tagmod'); + ul class => 'maintabs'; - li $sel eq 'hist' ? (class => 'tabselected') : (); - a href => "/$id/hist", 'history'; - end; + if($type =~ /[uvrp]/) { + li $sel eq 'hist' ? (class => 'tabselected') : (); + a href => "/$id/hist", 'history'; + end; + } - if($type ne 'r') { + if($type =~ /[uvp]/) { my $cnt = $self->dbThreadCount($type, $obj->{id}); li $sel eq 'disc' ? (class => 'tabselected') : (); a href => "/t/$id", "discussions ($cnt)"; @@ -43,22 +47,34 @@ sub htmlMainTabs { li $sel eq 'list' ? (class => 'tabselected') : (); a href => "/$id/list", 'list'; end; + + li $sel eq 'tags' ? (class => 'tabselected') : (); + a href => "/$id/tags", 'tags'; + end; + } + + if($type eq 'v' && $self->authCan('tag')) { + li $sel eq 'tagmod' ? (class => 'tabselected') : (); + a href => "/$id/tagmod", 'modify tags'; + end; } - if($type eq 'u' && ($self->authInfo->{id} && $obj->{id} == $self->authInfo->{id} || $self->authCan('usermod')) - || $type ne 'u' && $self->authCan('edit') && (!$obj->{locked} || $self->authCan('lock')) && (!$obj->{hidden} || $self->authCan('del'))) { + if( $type eq 'u' && ($self->authInfo->{id} && $obj->{id} == $self->authInfo->{id} || $self->authCan('usermod')) + || $type =~ /[vrp]/ && $self->authCan('edit') && (!$obj->{locked} || $self->authCan('lock')) && (!$obj->{hidden} || $self->authCan('del')) + || $type eq 'g' && $self->authCan('tagmod') + ) { li $sel eq 'edit' ? (class => 'tabselected') : (); a href => "/$id/edit", 'edit'; end; } - if($type ne 'u' && $self->authCan('del')) { + if($type =~ /[vrp]/ && $self->authCan('del')) { li; a href => "/$id/hide", $obj->{hidden} ? 'unhide' : 'hide'; end; } - if($type ne 'u' && $self->authCan('lock')) { + if($type =~ /[vrp]/ && $self->authCan('lock')) { li; a href => "/$id/lock", $obj->{locked} ? 'unlock' : 'lock'; end; diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index fdd325ac..17de6663 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -14,6 +14,7 @@ my %formerr_names = ( alias => 'Aliases', anime => 'Anime', desc => 'Description', + description => 'Description', editsum => 'Edit summary', gtin => 'JAN/EAN/UPC', lang => 'Language', @@ -33,7 +34,7 @@ my %formerr_names = ( platforms => 'Platforms', producers => 'Producers', released => 'Release date', - tags => 'Tags', + boards => 'Boards', title => 'Title', type => 'Type', usrname => 'Username', @@ -79,7 +80,14 @@ sub htmlFormError { li sprintf '%s should have at least %d characters', $field, $rule if $type eq 'minlength'; li sprintf '%s: only %d characters allowed', $field, $rule if $type eq 'maxlength'; li sprintf '%s must be one of the following: %s', $field, join ', ', @$rule if $type eq 'enum'; - li sprintf 'Wrong tag: %s', $rule if $type eq 'wrongtag'; + li sprintf 'Wrong board: %s', $rule if $type eq 'wrongboard'; + if($type eq 'tagexists') { + li; + lit $rule->{state} != 1 ? qq|Tag <a href="/g$rule->{id}">$rule->{name}</a> already exists!| + : qq|A tag <a href="/g$rule->{id}">with the same name</a> has been deleted in the past,| + .qq| please use <a href="/t/db">the discussion board</a> if you want it to be re-added.|; + end; + } li $rule->[1] if $type eq 'func' || $type eq 'regex'; if($type eq 'template') { li sprintf @@ -253,7 +261,9 @@ sub htmlForm { end; br; } + b "Don't forget! -> " if $options->{hitsubmit}; input type => 'submit', value => 'Submit', class => 'submit'; + b ' <-' if $options->{hitsubmit}; end; end; diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index cef1d8af..fed09ce2 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -57,6 +57,7 @@ sub _menu { div; a href => '/', 'Home'; br; a href => '/v/all', 'Visual novels'; br; + a href => '/g', 'Tags'; br; a href => '/p/all', 'Producers'; br; a href => '/u/all', 'Users'; br; a href => '/hist', 'Recent changes'; br; @@ -87,9 +88,12 @@ sub _menu { a href => "$uid/wish", 'My Wishlist'; br; a href => "/t$uid", 'My Messages'; br; a href => "$uid/hist", 'My Recent Changes'; br; + a href => "$uid/tags", 'My Tags'; br; br; a href => '/v/new', 'Add Visual Novel'; br; a href => '/p/new', 'Add Producer'; br; + if($self->authCan('tagmod')) { + a href => '/g/new', 'Add Tag'; br; } br; a href => '/u/logout', 'Logout'; end; diff --git a/static/f/forms.js b/static/f/forms.js index 3a09dc88..193b36d8 100644 --- a/static/f/forms.js +++ b/static/f/forms.js @@ -11,57 +11,6 @@ function shorten(v, l) { - /************************\ - * C A T E G O R I E S * - \************************/ - - -function catLoad() { - var i; - var cats=[]; - var ct = x('categories'); - var l = ct.value.split(','); - for(i=0;i<l.length;i++) - cats[l[i].substr(0,3)] = Math.floor(l[i].substr(3,1)); - - l = x('jt_box_categories').getElementsByTagName('a'); - for(i=0;i<l.length;i++) { - if(l[i].id.substr(0, 4) != 'cat_') - continue; - catSet(l[i].id.substr(4), cats[l[i].id.substr(4)]||0); - l[i].onclick = function() { - var c = this.id.substr(4); - if(!cats[c]) cats[c] = 0; - if(c.substr(0,1) == 'p' || c == 'gaa' || c == 'gab' || c.substr(0,1) == 'h' || c.substr(0,1) == 'l' || c.substr(0,1) == 't') { - if(cats[c]++) - cats[c] = 0; - } else if(++cats[c] == 4) - cats[c] = 0; - catSet(c, cats[c]); - - // has to be ordered before serializing! - var r;l=[];i=0; - for(r in cats) - l[i++] = r; - l = l.sort(); - r=''; - for(i=0;i<l.length;i++) - if(cats[l[i]] > 0) - r+=(r?',':'')+l[i]+cats[l[i]]; - ct.value = r; - return false; - }; - } -} - -function catSet(id, rnk) { - x('cat_'+id).className = 'catlvl_'+rnk; - x('b_'+id).innerHTML = rnk; -} - - - - /***********************************\ @@ -156,8 +105,10 @@ function dsSearch(obj) { // show/hide the ds_box div if(obj.value.length < 2) { - if(b) + if(b) { b.style.top = '-500px'; + b.innerHTML = '<b>Loading...</b>'; + } obj.selectedId = 0; return; } @@ -860,3 +811,142 @@ function vnpSerialize(type) { } + + + + + /****************************************************\ + * V I S U A L N O V E L T A G L I N K I N G * + \****************************************************/ + + +function tglLoad() { + var n = x('tagtable').getElementsByTagName('tfoot')[0].getElementsByTagName('input'); + dsInit(n[0], '/xml/tags.xml?q=', function(item, tr) { + var td = document.createElement('td'); + td.innerHTML = shorten(item.firstChild.nodeValue, 40); + if(item.getAttribute('meta') == 'yes') + td.innerHTML += ' <b class="grayedout">meta</b>'; + else if(item.getAttribute('state') == 0) + td.innerHTML += ' <b class="grayedout">awaiting moderation</b>'; + tr.appendChild(td); + }, function(item) { + return item.firstChild.nodeValue; + }, tglAdd); + n[1].onclick = tglAdd; + + tglStripe(); + var l = x('tagtable').getElementsByTagName('tbody')[0].getElementsByTagName('tr'); + for(var i=0; i<l.length;i++) { + var o = l[i].getElementsByTagName('td'); + tglSpoiler(o[4], parseInt(o[4].innerHTML)); + tglVoteBar(o[3], parseInt(o[3].innerHTML)); + } +} + +function tglSpoiler(obj, spoil) { + var r = '<select onchange="tglSerialize()">'; + for(var i=-1; i<=2; i++) + r += '<option value="'+i+'"'+(spoil==i?' selected="selected"':'')+'>' + +(i == -1 ? 'neutral' : i == 0 ? 'no spoiler' : i == 1 ? 'minor spoiler' : 'major spoiler') + +' </option>'; + obj.innerHTML = r+'</select>'; +} + +function tglVoteBar(obj, vote) { + var r = ''; + for(var i=-3;i<=3;i++) + r += '<a href="#" class="taglvl taglvl'+i+'" onmouseover="tglVoteBarSel(this, '+i+')"' + + ' onmouseout="tglVoteBarSel(this, '+vote+')" onclick="return tglVoteBar(this.parentNode, '+i+')"> </a>'; + obj.innerHTML = r; + tglVoteBarSel(obj, vote); + tglSerialize(); + return false; +} + +function tglVoteBarSel(obj, vote) { + if(obj.className.indexOf('taglvl') >= 0) + obj = obj.parentNode; + var l = obj.getElementsByTagName('a'); + var num; + for(var i=0; i<l.length; i++) { + if((num = l[i].className.replace(/^.*taglvl(-?[0-3]).*$/, "$1")) == l[i].className) + continue; + if(num == 0) + l[i].innerHTML = vote == 0 ? '-' : vote; + else if(num<0&&vote<=num || num>0&&vote>=num) { + if(l[i].className.indexOf('taglvlsel') < 0) + l[i].className += ' taglvlsel'; + } else + if(l[i].className.indexOf('taglvlsel') >= 0) + l[i].className = l[i].className.replace(/taglvlsel/, ''); + } +} + +function tglAdd() { + var n = x('tagtable').getElementsByTagName('tfoot')[0].getElementsByTagName('input'); + n[0].disabled = n[1].disabled = true; + n[1].value = 'loading...'; + ajax('/xml/tags.xml?q=name:'+encodeURIComponent(n[0].value), function(hr) { + n[0].disabled = n[1].disabled = false; + n[1].value = 'Add tag'; + n[0].value = ''; + + var items = hr.responseXML.getElementsByTagName('item'); + if(items.length < 1) + return alert('Item not found!'); + if(items[0].getAttribute('meta') == 'yes') + return alert('Can\'t use meta tags here!'); + var name = items[0].firstChild.nodeValue; + var l = x('tagtable').getElementsByTagName('a'); + for(var i=0; i<l.length; i++) + if(l[i].innerHTML == shorten(name, 40)) + return alert('Tag is already present!'); + + var tr = document.createElement('tr'); + var td = document.createElement('td'); + td.innerHTML = '<a href="/g'+items[0].getAttribute('id')+'">'+name+'</a>'; + td.className = 'tc1'; + tr.appendChild(td); + td = document.createElement('td'); + td.className = 'tc2'; + td.innerHTML = '-'; + tr.appendChild(td); + td = document.createElement('td'); + td.innerHTML = '-'; + td.className = 'tc3'; + tr.appendChild(td); + td = document.createElement('td'); + tglVoteBar(td, 2); + td.className = 'tc4'; + tr.appendChild(td); + td = document.createElement('td'); + tglSpoiler(td, -1); + td.className = 'tc5'; + tr.appendChild(td); + x('tagtable').getElementsByTagName('tbody')[0].appendChild(tr); + tglStripe(); + tglSerialize(); + }); +} + +function tglStripe() { + var l = x('tagtable').getElementsByTagName('tbody')[0].getElementsByTagName('tr'); + for(var i=0;i<l.length;i++) + l[i].className = i%2 ? 'odd' : ''; +} + +function tglSerialize() { + var r = ''; + var l = x('tagtable').getElementsByTagName('tbody')[0].getElementsByTagName('tr'); + for(var i=0; i<l.length;i++) { + var lnk = l[i].getElementsByTagName('a')[0].href; + var vt = l[i].getElementsByTagName('td')[3].getElementsByTagName('a'); + var id; + if((id = lnk.replace(/^.*g([1-9][0-9]*)$/, "$1")) != lnk && vt.length > 3 && vt[3].innerHTML != '-') + r += (r?' ':'')+id+','+vt[3].innerHTML+','+(l[i].getElementsByTagName('select')[0].selectedIndex-1); + } + x('taglinks').value = r; +} + + diff --git a/static/f/script.js b/static/f/script.js index e434eede..6a08f3cb 100644 --- a/static/f/script.js +++ b/static/f/script.js @@ -33,6 +33,24 @@ function ajax(url, func) { http_request.send(null); } +function setCookie(n,v) { + var date = new Date(); + date.setTime(date.getTime()+(365*24*60*60*1000)); + document.cookie = n+'='+v+'; expires='+date.toGMTString()+'; path=/'; +} +function readCookie(n) { + var l = document.cookie.split(';'); + for(var i=0; i<l.length; i++) { + var c = l[i]; + while(c.charAt(0) == ' ') + c = c.substring(1,c.length); + if(c.indexOf(n+'=') == 0) + return c.substring(n.length+1,c.length); + } + return null; +} + + @@ -328,6 +346,28 @@ function jtSel(which, nolink) { +/* Tag VN spoilers */ +function tvsSet(lvl) { + var l = x('tagops').getElementsByTagName('a'); + for(var i=0;i<l.length;i++) { + if(i == lvl && l[i].className.indexOf('tsel') < 0) + l[i].className += ' tsel'; + else if(i != lvl && l[i].className.indexOf('tsel') >= 0) + l[i].className = l[i].className.replace(/tsel/, ''); + } + l = x('vntags').getElementsByTagName('span'); + for(i=0;i<l.length;i++) { + if(lvl < l[i].className.substr(6, 1) && l[i].className.indexOf('hidden') < 0) + l[i].className += ' hidden'; + else if(lvl >= l[i].className.substr(6, 1) && l[i].className.indexOf('hidden') >= 0) + l[i].className = l[i].className.replace(/hidden/, ''); + } + return false; +} + + + + /* O N L O A D E V E N T */ DOMLoad(function() { @@ -394,7 +434,9 @@ DOMLoad(function() { if(this.selectedIndex != 0) location.href = location.href.replace(/\.[0-9]+/, '')+'/list?e='+this.options[this.selectedIndex].value; }; + // User VN list + // (might want to make this a bit more generic, as it's now also used for the user tag list) i = x('relhidall'); if(i) { var l = document.getElementsByTagName('tr'); @@ -475,6 +517,22 @@ DOMLoad(function() { break; } + // VN tag spoiler options + if(x('tagops')) { + l = x('tagops').getElementsByTagName('a'); + for(i=0;i<l.length;i++) + l[i].onclick = function() { + var l = x('tagops').getElementsByTagName('a'); + for(var i=0;i<l.length;i++) + if(l[i] == this) { + tvsSet(i); + setCookie('tagspoil', i); + } + return false; + }; + tvsSet(readCookie('tagspoil')||0); + } + // Javascript tabs if(x('jt_select')) jtInit(); @@ -496,8 +554,6 @@ DOMLoad(function() { } // forms.js - if(x('categories')) - catLoad(); if(x('relations')) relLoad(); if(x('jt_box_screenshots')) @@ -508,6 +564,8 @@ DOMLoad(function() { vnpLoad('vn'); if(x('jt_box_producers')) vnpLoad('producers'); + if(x('taglinks')) + tglLoad(); // spam protection on all forms if(document.forms.length >= 1) diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 5fd52b75..b9800a0a 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -20,7 +20,8 @@ my %subgraphs = ( 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_vn |], 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |], 'Users' => [qw| CCFFFF users votes vnlists rlists wlists |], - 'Discussion board' => [qw| FFDCDC threads threads_tags threads_posts |], + 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], + 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes |], ); diff --git a/util/dump.sql b/util/dump.sql index e3652a0f..993baaa3 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -155,6 +155,40 @@ CREATE TABLE stats_cache ( count integer NOT NULL DEFAULT 0 ); +-- tags +CREATE TABLE tags ( + id SERIAL NOT NULL PRIMARY KEY, + name varchar(250) NOT NULL UNIQUE, + description text NOT NULL DEFAULT '', + meta boolean NOT NULL DEFAULT FALSE, + added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()), + state smallint NOT NULL DEFAULT 0, + c_vns integer NOT NULL DEFAULT 0 +); + +-- tags_aliases +CREATE TABLE tags_aliases ( + alias varchar(250) NOT NULL PRIMARY KEY, + tag integer NOT NULL, +); + +-- tags_parents +CREATE TABLE tags_parents ( + tag integer NOT NULL, + parent integer NOT NULL, + PRIMARY KEY(tag, parent) +); + +-- tags_vn +CREATE TABLE tags_vn ( + tag integer NOT NULL, + vid integer NOT NULL, + uid integer NOT NULL, + vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), + spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), + PRIMARY KEY(tag, vid, uid) +); + -- threads CREATE TABLE threads ( id SERIAL NOT NULL PRIMARY KEY, @@ -176,8 +210,8 @@ CREATE TABLE threads_posts ( PRIMARY KEY(tid, num) ); --- threads_tags -CREATE TABLE threads_tags ( +-- threads_boards +CREATE TABLE threads_boards ( tid integer NOT NULL DEFAULT 0, type character(2) NOT NULL DEFAULT 0, iid integer NOT NULL DEFAULT 0, @@ -199,7 +233,8 @@ CREATE TABLE users ( c_changes integer NOT NULL DEFAULT 0, skin varchar(128) NOT NULL DEFAULT '', customcss text NOT NULL DEFAULT '', - ip inet NOT NULL DEFAULT '0.0.0.0' + ip inet NOT NULL DEFAULT '0.0.0.0', + c_tags integer NOT NULL DEFAULT 0 ); -- vn @@ -296,6 +331,32 @@ CREATE TABLE wlists ( +----------------- +-- V I E W S -- +----------------- + + +CREATE OR REPLACE VIEW tags_vn_all AS + SELECT * + FROM tags_vn + UNION + SELECT * + FROM tag_vn_childs(); + +CREATE OR REPLACE VIEW tags_vn_grouped AS + SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler + FROM tags_vn_all + GROUP BY tag, vid, uid; + +CREATE OR REPLACE VIEW tags_vn_inherited AS + SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating, + (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler + FROM tags_vn_grouped + GROUP BY tag, vid; + + + + ----------------------------------------------- -- F O R E I G N K E Y C H E C K I N G -- @@ -319,10 +380,16 @@ ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_r ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE threads_tags ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id) DEFERRABLE INITIALLY DEFERRED; @@ -440,6 +507,101 @@ END; $$ LANGUAGE plpgsql; +-- tag: tag to start with, +-- lvl: recursion level +-- dir: direction, true = parent->child, false = child->parent +CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer); +CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$ +DECLARE + r tag_tree_item%rowtype; + r2 tag_tree_item%rowtype; +BEGIN + IF dir AND tag = 0 THEN + FOR r IN + SELECT lvl, t.id, t.name, t.c_vns + FROM tags t + WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id) + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + ELSIF dir THEN + FOR r IN + SELECT lvl, tp.tag, t.name, t.c_vns + FROM tags_parents tp + JOIN tags t ON t.id = tp.tag + WHERE tp.parent = tag + AND state = 2 + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + ELSE + FOR r IN + SELECT lvl, tp.parent, t.name, t.c_vns + FROM tags_parents tp + JOIN tags t ON t.id = tp.parent + WHERE tp.tag = tag + AND state = 2 + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + + +-- returns all votes inherited by childs +-- UNION this with tags_vn and you have all votes for all tags +CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$ +DECLARE + r tags_vn%rowtype; + i RECORD; + l RECORD; +BEGIN + FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP + FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP + FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP + RETURN NEXT r; + END LOOP; + END LOOP; + END LOOP; +END; +$$ LANGUAGE plpgsql; + + +-- recalculate tags_vn_stored +CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +BEGIN + DROP TABLE IF EXISTS tags_vn_stored; + CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited; + CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag); + + UPDATE tags_vn_stored tvs SET rating = + ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating) + / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real; + + UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id); + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT tag_vn_calc(); @@ -458,12 +620,18 @@ BEGIN ELSE UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid; END IF; - ELSE + ELSIF TG_TABLE_NAME = 'changes' THEN IF TG_OP = 'INSERT' THEN UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; ELSE UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; END IF; + ELSIF TG_TABLE_NAME = 'tags_vn' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; + END IF; END IF; RETURN NULL; END; @@ -471,6 +639,7 @@ $$ LANGUAGE 'plpgsql'; CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); +CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); -- the stats_cache table diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 21db32ca..32b04b31 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -14,3 +14,192 @@ ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT ''; -- aliases field for producers ALTER TABLE producers_rev ADD COLUMN alias varchar(500) NOT NULL DEFAULT ''; + + +-- tagging system + +CREATE TABLE tags ( + id SERIAL NOT NULL PRIMARY KEY, + name varchar(250) NOT NULL UNIQUE, + description text NOT NULL DEFAULT '', + meta boolean NOT NULL DEFAULT FALSE, + added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()), + state smallint NOT NULL DEFAULT 0, -- 0: awaiting moderation, 1: deleted, 2: accepted + c_vns integer NOT NULL DEFAULT 0 +) WITHOUT OIDS; + +CREATE TABLE tags_aliases ( + alias varchar(250) NOT NULL PRIMARY KEY, + tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED +) WITHOUT OIDS; + +CREATE TABLE tags_parents ( + tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED, + parent integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED, + PRIMARY KEY(tag, parent) +) WITHOUT OIDS; + +CREATE TABLE tags_vn ( + tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED, + vid integer NOT NULL REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED, + uid integer NOT NULL REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED, + vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), + spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), + PRIMARY KEY(tag, vid, uid) +) WITHOUT OIDS; + + +CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer); + +-- tag: tag to start with, +-- lvl: recursion level +-- dir: direction, true = parent->child, false = child->parent +CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$ +DECLARE + r tag_tree_item%rowtype; + r2 tag_tree_item%rowtype; +BEGIN + IF dir AND tag = 0 THEN + FOR r IN + SELECT lvl, t.id, t.name, t.c_vns + FROM tags t + WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id) + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + ELSIF dir THEN + FOR r IN + SELECT lvl, tp.tag, t.name, t.c_vns + FROM tags_parents tp + JOIN tags t ON t.id = tp.tag + WHERE tp.parent = tag + AND state = 2 + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + ELSE + FOR r IN + SELECT lvl, tp.parent, t.name, t.c_vns + FROM tags_parents tp + JOIN tags t ON t.id = tp.parent + WHERE tp.tag = tag + AND state = 2 + ORDER BY t.name + LOOP + RETURN NEXT r; + IF lvl-1 <> 0 THEN + FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP + RETURN NEXT r2; + END LOOP; + END IF; + END LOOP; + END IF; +END; +$$ LANGUAGE plpgsql; + +-- returns all votes inherited by childs +-- UNION this with tags_vn and you have all votes for all tags +CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$ +DECLARE + r tags_vn%rowtype; + i RECORD; + l RECORD; +BEGIN + FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP + FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP + FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP + RETURN NEXT r; + END LOOP; + END LOOP; + END LOOP; +END; +$$ LANGUAGE plpgsql; + +-- all votes for all tags +CREATE OR REPLACE VIEW tags_vn_all AS + SELECT * + FROM tags_vn + UNION + SELECT * + FROM tag_vn_childs(); + +-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry +CREATE OR REPLACE VIEW tags_vn_grouped AS + SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler + FROM tags_vn_all + GROUP BY tag, vid, uid; + +-- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags) +CREATE OR REPLACE VIEW tags_vn_inherited AS + SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating, + (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler + FROM tags_vn_grouped + GROUP BY tag, vid; + +-- creates/updates a table with bayesian rankings of tags +CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +BEGIN + DROP TABLE IF EXISTS tags_vn_stored; + CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited; + CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag); + + UPDATE tags_vn_stored tvs SET rating = + ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating) + / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real; + + UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id); + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT tag_vn_calc(); + + + +-- Cache users tag vote count +ALTER TABLE users ADD COLUMN c_tags integer NOT NULL DEFAULT 0; +UPDATE users SET c_tags = (SELECT COUNT(*) FROM tags_vn WHERE uid = id); + +CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$ +BEGIN + IF TG_TABLE_NAME = 'votes' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid; + END IF; + ELSIF TG_TABLE_NAME = 'changes' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester; + ELSE + UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester; + END IF; + ELSIF TG_TABLE_NAME = 'tags_vn' THEN + IF TG_OP = 'INSERT' THEN + UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid; + ELSE + UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid; + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE 'plpgsql'; + +CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); + + + +-- rename threads tags to boards +ALTER TABLE threads_tags RENAME TO threads_boards; + |