summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--ChangeLog19
-rw-r--r--data/docs/14
-rw-r--r--data/docs/1045
-rw-r--r--data/docs/213
-rw-r--r--data/docs/42
-rw-r--r--data/docs/917
-rw-r--r--data/docs/index1
-rw-r--r--data/global.pl14
-rw-r--r--data/style.css102
-rw-r--r--lib/Multi/IRC.pm11
-rw-r--r--lib/Multi/Maintenance.pm23
-rw-r--r--lib/VNDB/DB/Discussions.pm58
-rw-r--r--lib/VNDB/DB/Misc.pm12
-rw-r--r--lib/VNDB/DB/Producers.pm11
-rw-r--r--lib/VNDB/DB/Releases.pm10
-rw-r--r--lib/VNDB/DB/Tags.pm218
-rw-r--r--lib/VNDB/DB/Users.pm2
-rw-r--r--lib/VNDB/Func.pm57
-rw-r--r--lib/VNDB/Handler/Discussions.pm70
-rw-r--r--lib/VNDB/Handler/Misc.pm12
-rw-r--r--lib/VNDB/Handler/Producers.pm15
-rw-r--r--lib/VNDB/Handler/Releases.pm44
-rw-r--r--lib/VNDB/Handler/Tags.pm706
-rw-r--r--lib/VNDB/Handler/ULists.pm33
-rw-r--r--lib/VNDB/Handler/Users.pm20
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm2
-rw-r--r--lib/VNDB/Handler/VNEdit.pm30
-rw-r--r--lib/VNDB/Handler/VNPage.pm43
-rw-r--r--lib/VNDB/Util/CommonHTML.pm34
-rw-r--r--lib/VNDB/Util/FormHTML.pm18
-rw-r--r--lib/VNDB/Util/LayoutHTML.pm20
-rw-r--r--static/f/forms.js214
-rw-r--r--static/f/script.js139
-rw-r--r--static/s/seinarukana/conf2
-rw-r--r--static/s/term/conf2
-rwxr-xr-xutil/dbgraph.pl5
-rw-r--r--util/dump.sql193
-rw-r--r--util/updates/update_2.3.sql205
38 files changed, 2095 insertions, 331 deletions
diff --git a/ChangeLog b/ChangeLog
index 28d23ccb..d9512765 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,4 +1,19 @@
-2.2 - ?
+2.3 - 2009-04-01
+ - No page reload needed when changing rlist status from vn page
+ - Random VN quotes to the footer of every page
+ - Fixed case-sensitivity for BBCode
+ - Homepage shows platform icons for releases
+ - Don't show deleted items on /u+ recent changes
+ - Catalog number field to release entries
+ - Aliases field to producers
+ - Various small improvements to the BBcode
+ - Various bugfixes
+ - Experimental tagging system
+ - Renamed thread tags to boards
+ - Tiny skin fixes
+ - Tagging system
+
+2.2 - 2009-01-16
- Additional custom CSS field to user profile
- Search dropdown calls the return function automatically on select
- Revised the media list
@@ -39,7 +54,7 @@
- Global statistics are cached in stats_cache
- URL changes:
- /p and /v don't work anymore, use /[pv]/all
- - /u/list/* -> /u/* and /u/list -> /u/list/all
+ - /u/list/* -> /u/* and /u/list -> /u/all
- Revert URL changed from /x99/edit?rev=1 to /x99.1/edit
- /v+/stats and /v+/scr moved into /v+
- Functionality changes:
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/10 b/data/docs/10
new file mode 100644
index 00000000..30baafae
--- /dev/null
+++ b/data/docs/10
@@ -0,0 +1,45 @@
+:TITLE:Tagging system
+:INC:notfinished
+:INC:index
+
+
+:SUB:Introduction
+<i>TODO</i>
+
+
+:SUB:Tag voting
+<p>
+ While everyone is pretty much free to link any tag to any visual novel, please keep the following
+ in mind while voting:
+</p>
+<ul>
+ <li>Read the description of each tag before you link it to a VN, some tags may not always mean what
+ you think they do.</li>
+ <li>Try to be as specific as you can. For example, if a game has drama elements, rather than voting
+ on the "Drama" tag, try to look for a more specific child tag that describes what kind of drama a
+ player can expect.</li>
+ <li>A rating between -3 and 3 can be given to a tag. A negative rating indicates that you disagree
+ with a tag, with -1 being "I wouldn't say this tag really applies" and -3 meaning you fully disagree
+ and the tag really doesn't apply at all. A positive rating works the opposite way, and indicates
+ that you do agree with it.</li>
+ <li>Some tags can act as spoilers for the plot of a VN, this can be indicated by voting on the spoiler
+ status. It is highly recommended to not leave this as "Neutral", and really try to give an indication
+ of whether the tag is not a spoiler at all or does spoil a bit.</li>
+</ul>
+
+
+:SUB:Tag guidelines
+<p>
+ New tags can be requested by everyone, but they'll have to be accepted by a moderator before
+ the tags will be browsable. The following rules apply:
+</p>
+<ul>
+ <li>Tag names should be self-describing. While the meaning of a tag can be obvious if you look
+ at its place within the tag tree, when displayed on a VN page only their name is visible and
+ not the parent tags. The name should thus fully describe what the tag means and should be used
+ for. For example: a tag to describe a male protagonist should be fully named "Male protagonist",
+ even if its parent tag is "Characters &gt; Protagonist".</li>
+ <li>Tags should be used to describe objective information about a VN. Subjective tags like
+ "Awesome protagonist", which would have a different meaning for each individual, are not allowed.</li>
+</ul>
+
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/4 b/data/docs/4
index 2282bb0d..927b7d2d 100644
--- a/data/docs/4
+++ b/data/docs/4
@@ -24,6 +24,8 @@
</dd><dt>Original name</dt><dd>
If the Name (above) has been romanised, make sure to mention the original (most likely
the Japanese) name here.
+ </dd><dt>Aliases</dt><dd>
+ Other names the producer is known as. Multiple aliases should be separated by a comma.
</dd><dt>Primary language</dt><dd>
The language the the producer works in most of the time. By default, it is Japanese.
</dd><dt>Website</dt><dd>
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/docs/index b/data/docs/index
index e121ebfb..79d751af 100644
--- a/data/docs/index
+++ b/data/docs/index
@@ -5,6 +5,7 @@
<li><a href="/d2">Visual Novels</a></li>
<li><a href="/d3">Releases</a></li>
<li><a href="/d4">Producers</a></li>
+ <li><a href="/d10">Tags</a></li>
<li><b>About VNDB</b></li>
<li><a href="/d9">Discussion board</a></li>
<li><a href="/d6">FAQ</a></li>
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 6ac89743..042beb33 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;
@@ -107,11 +108,13 @@ b.spoiler, b.spoiler a {
font-weight: normal;
}
b.spoiler_shown { font-weight: normal }
-div.quote {
+#maincontent div.quote {
padding: 1px 5px;
margin: 0px 10px;
color: $grayedout$;
+ border: none;
border-left: 1px dotted $border$;
+ text-align: left;
}
@@ -358,7 +361,7 @@ b.future {
#maincontent .mainbox.threelayout {
float: left;
width: 31%;
- height: 170px;
+ height: 200px;
margin: 21px 10px -10px 0;
padding: 2px;
overflow: hidden;
@@ -508,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; }
@@ -535,7 +538,7 @@ div.discussions td.tc4 { width: 170px; }
div.vndetails {
margin: 0 auto;
- width: 700px;
+ width: 800px;
}
div.vnimg {
float: left;
@@ -553,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;
@@ -571,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 }
@@ -588,21 +598,25 @@ 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 }
+#tagops a.sec { border-left: 1px solid $border$; padding-left: 10px }
+#maincontent #tagops a.tsel { color: $maintext$; }
-b.patch { font-weight: normal; color: $grayedout$ }
.releases table, #screenshots table {
width: 100%;
}
@@ -935,8 +949,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; }
@@ -963,6 +977,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 { padding-left: 30px!important }
+#tagtable .tc3 { border-right: 1px solid $border$; padding-right: 30px!important; text-align: right; padding-left: 10px!important }
+#tagtable .tc4 { padding-left: 30px!important; }
+#tagtable .tc4 i { font-style: normal; font-size: 8px }
+#tagtable .tc5 { text-align: right; padding-right: 30px!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 .tc3 { padding: 0 }
+#tagtable .tc3 select { width: 90px; height: 15px; border: 0; margin: 0; font-size: 11px; background-color: $_blendbg$; text-align: right }
+#tagtable .odd .tc3 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 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',
) : (),
);
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 4c71d061..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...
@@ -93,6 +93,7 @@ sub bb2html {
my $raw = shift;
my $maxlength = shift;
$raw =~ s/\r//g;
+ $raw =~ s/\n{5,}/\n\n/g;
return '' if !$raw && $raw ne "0";
my($result, $length, $rmnewline, @open) = ('', 0, 0, 'first');
@@ -111,32 +112,32 @@ sub bb2html {
next if !defined $_;
next if $_ eq '';
- $rmnewline = s/\n//g if $rmnewline;
- next if $_ eq '';
+ # (note to self: stop using unreadable hacks like these!)
+ $rmnewline-- && $_ eq "\n" && next if $rmnewline;
my $lit = $_;
if($open[$#open] ne 'raw') {
- if ($_ eq '[raw]') { push @open, 'raw'; next }
- elsif ($_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next }
- elsif ($_ eq '[quote]') {
+ if (lc$_ eq '[raw]') { push @open, 'raw'; next }
+ elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next }
+ elsif (lc$_ eq '[quote]') {
push @open, 'quote';
$result .= '<div class="quote">' if !$maxlength;
- $rmnewline++;
+ $rmnewline = 1;
next
- } elsif ($_ eq '[/spoiler]') {
+ } elsif (lc$_ eq '[/spoiler]') {
if($open[$#open] eq 'spoiler') {
$result .= '</b>';
pop @open;
}
next;
- } elsif ($_ eq '[/quote]') {
+ } elsif (lc$_ eq '[/quote]') {
if($open[$#open] eq 'quote') {
$result .= '</div>' if !$maxlength;
- $rmnewline++;
+ $rmnewline = 1;
pop @open;
}
next;
- } elsif($_ eq '[/url]') {
+ } elsif(lc$_ eq '[/url]') {
if($open[$#open] eq 'url') {
$result .= '</a>';
pop @open;
@@ -155,13 +156,13 @@ 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 .= $_;
next;
}
- } elsif($_ eq '[/raw]') {
+ } elsif(lc$_ eq '[/raw]') {
pop @open if $open[$#open] eq 'raw';
next;
}
@@ -237,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 a0260cc8..45a2e24e 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");
}
@@ -368,7 +368,7 @@ sub _threadlist {
td class => 'tc3';
lit userstr $o->{fuid}, $o->{fusername};
end;
- td class => 'tc4', rowspan => 2;
+ td class => 'tc4';
lit userstr $o->{luid}, $o->{lusername};
lit ' @ ';
a href => "/t$o->{id}.$o->{count}";
@@ -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/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 39af8155..a9a5ad14 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -144,12 +144,14 @@ sub homepage {
# Upcoming releases
div class => 'mainbox threelayout';
h1 'Upcoming releases';
- my $upcoming = $self->dbReleaseGet(results => 10, unreleased => 1);
+ my $upcoming = $self->dbReleaseGet(results => 10, unreleased => 1, what => 'platforms');
ul;
for (@$upcoming) {
li;
lit datestr $_->{released};
txt ' ';
+ cssicon $_, $self->{platforms}{$_} for (@{$_->{platforms}});
+ txt ' ';
a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30;
end;
}
@@ -159,12 +161,14 @@ sub homepage {
# Just released
div class => 'mainbox threelayout last';
h1 'Just released';
- my $justrel = $self->dbReleaseGet(results => 10, order => 'rr.released DESC', unreleased => 0);
+ my $justrel = $self->dbReleaseGet(results => 10, order => 'rr.released DESC', unreleased => 0, what => 'platforms');
ul;
for (@$justrel) {
li;
lit datestr $_->{released};
txt ' ';
+ cssicon $_, $self->{platforms}{$_} for (@{$_->{platforms}});
+ txt ' ';
a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30;
end;
}
@@ -195,7 +199,7 @@ sub history {
my $obj = $type eq 'u' ? $self->dbUserGet(uid => $id)->[0] :
$type eq 'p' ? $self->dbProducerGet(id => $id)->[0] :
$type eq 'r' ? $self->dbReleaseGet(id => $id)->[0] :
- $self->dbVNGet(id => $id)->[0];
+ $type eq 'v' ? $self->dbVNGet(id => $id)->[0] : undef;
my $title = $type ? 'Edit history of '.($obj->{title} || $obj->{name} || $obj->{username}) : 'Recent changes';
return 404 if $type && !$obj->{id};
@@ -258,7 +262,7 @@ sub history {
}
if($type eq 'v') {
p class => 'browseopts';
- a !$f->{r} ? (class => 'optselected') : (), href => $u->(r => 0), 'Exclude';
+ a !$f->{r} ? (class => 'optselected') : (), href => $u->(r => 0), 'Exclude edits of releases';
a $f->{r} ? (class => 'optselected') : (), href => $u->(r => 1), 'Include edits of releases';
end;
}
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 0efd2f47..7f43dd78 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -21,7 +21,7 @@ sub page {
my $p = $self->dbProducerGet(
id => $pid,
- what => 'vn'.($rev ? ' changes' : ''),
+ what => 'vn extended'.($rev ? ' changes' : ''),
$rev ? ( rev => $rev ) : ()
)->[0];
return 404 if !$p->{id};
@@ -31,11 +31,12 @@ sub page {
return if $self->htmlHiddenMessage('p', $p);
if($rev) {
- my $prev = $rev && $rev > 1 && $self->dbProducerGet(id => $pid, rev => $rev-1, what => 'changes')->[0];
+ my $prev = $rev && $rev > 1 && $self->dbProducerGet(id => $pid, rev => $rev-1, what => 'changes extended')->[0];
$self->htmlRevision('p', $prev, $p,
[ type => 'Type', serialize => sub { $self->{producer_types}{$_[0]} } ],
[ name => 'Name (romaji)', diff => 1 ],
[ original => 'Original name', diff => 1 ],
+ [ alias => 'Aliases', diff => 1 ],
[ lang => 'Language', serialize => sub { "$_[0] ($self->{languages}{$_[0]})" } ],
[ website => 'Website', diff => 1 ],
[ desc => 'Description', diff => 1 ],
@@ -48,6 +49,7 @@ sub page {
h2 class => 'alttitle', $p->{original} if $p->{original};
p class => 'center';
txt "$self->{languages}{$p->{lang}} \L$self->{producer_types}{$p->{type}}";
+ txt "\na.k.a. $p->{alias}" if $p->{alias};
if($p->{website}) {
txt "\n";
a href => $p->{website}, $p->{website};
@@ -87,14 +89,14 @@ sub page {
sub edit {
my($self, $pid, $rev) = @_;
- my $p = $pid && $self->dbProducerGet(id => $pid, what => 'changes', $rev ? (rev => $rev) : ())->[0];
+ my $p = $pid && $self->dbProducerGet(id => $pid, what => 'changes extended', $rev ? (rev => $rev) : ())->[0];
return 404 if $pid && !$p->{id};
$rev = undef if !$p || $p->{cid} == $p->{latest};
return $self->htmlDenied if !$self->authCan('edit')
|| $pid && ($p->{locked} && !$self->authCan('lock') || $p->{hidden} && !$self->authCan('del'));
- my %b4 = !$pid ? () : map { $_ => $p->{$_} } qw|type name original lang website desc|;
+ my %b4 = !$pid ? () : map { $_ => $p->{$_} } qw|type name original lang website desc alias|;
my $frm;
if($self->reqMethod eq 'POST') {
@@ -102,6 +104,7 @@ sub edit {
{ name => 'type', enum => [ keys %{$self->{producer_types}} ] },
{ name => 'name', maxlength => 200 },
{ name => 'original', required => 0, maxlength => 200, default => '' },
+ { name => 'alias', required => 0, maxlength => 500, default => '' },
{ name => 'lang', enum => [ keys %{$self->{languages}} ] },
{ name => 'website', required => 0, template => 'url', default => '' },
{ name => 'desc', required => 0, maxlength => 5000, default => '' },
@@ -137,6 +140,8 @@ sub edit {
[ input => name => 'Name (romaji)', short => 'name' ],
[ input => name => 'Original name', short => 'original' ],
[ static => content => q|The original name of the producer, leave blank if it is already in the Latin alphabet.| ],
+ [ input => name => 'Aliases', short => 'alias', width => 400 ],
+ [ static => content => q|(Un)official aliases, separated by a comma.| ],
[ select => name => 'Primary language', short => 'lang',
options => [ map [ $_, "$_ ($self->{languages}{$_})" ], sort keys %{$self->{languages}} ] ],
[ input => name => 'Website', short => 'website' ],
@@ -193,7 +198,7 @@ sub list {
for ($perlist*$c..($perlist*($c+1))-1) {
li;
cssicon 'lang '.$list->[$_]{lang}, $self->{languages}{$list->[$_]{lang}};
- a href => "/p$list->[$_]{id}", $list->[$_]{name};
+ a href => "/p$list->[$_]{id}", title => $list->[$_]{original}, $list->[$_]{name};
end;
}
end;
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 38a1fa02..8cd2eb43 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -21,7 +21,7 @@ sub page {
my $r = $self->dbReleaseGet(
id => $rid,
- what => 'vn producers platforms media'.($rev ? ' changes' : ''),
+ what => 'vn extended producers platforms media'.($rev ? ' changes' : ''),
$rev ? (rev => $rev) : (),
)->[0];
return 404 if !$r->{id};
@@ -33,7 +33,7 @@ sub page {
if($rev) {
my $prev = $rev && $rev > 1 && $self->dbReleaseGet(
id => $rid, rev => $rev-1,
- what => 'vn producers platforms media changes'
+ what => 'vn extended producers platforms media changes'
)->[0];
$self->htmlRevision('r', $prev, $r,
[ vn => 'Relations', join => '<br />', split => sub {
@@ -44,6 +44,7 @@ sub page {
[ title => 'Title (romaji)', diff => 1 ],
[ original => 'Original title', diff => 1 ],
[ gtin => 'JAN/UPC/EAN', serialize => sub { $_[0]||'[none]' } ],
+ [ catalog => 'Catalog number', serialize => sub { $_[0]||'[none]' } ],
[ language => 'Language', serialize => sub { $self->{languages}{$_[0]} } ],
[ website => 'Website', ],
[ released => 'Release date', htmlize => sub { datestr $_[0] } ],
@@ -86,7 +87,17 @@ sub _infotable {
my $i = 0;
Tr ++$i % 2 ? (class => 'odd') : ();
- td class => 'key', 'Title';
+ td class => 'key', 'Relation';
+ td;
+ for (@{$r->{vn}}) {
+ a href => "/v$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 60;
+ br if $_ != $r->{vn}[$#{$r->{vn}}];
+ }
+ end;
+ end;
+
+ Tr ++$i % 2 ? (class => 'odd') : ();
+ td 'Title';
td $r->{title};
end;
@@ -98,16 +109,6 @@ sub _infotable {
}
Tr ++$i % 2 ? (class => 'odd') : ();
- td 'Relation';
- td;
- for (@{$r->{vn}}) {
- a href => "/v$_->{vid}", title => $_->{original}||$_->{title}, shorten $_->{title}, 60;
- br if $_ != $r->{vn}[$#{$r->{vn}}];
- }
- end;
- end;
-
- Tr ++$i % 2 ? (class => 'odd') : ();
td 'Type';
td;
my $type = $self->{release_types}[$r->{type}];
@@ -181,6 +182,13 @@ sub _infotable {
end;
}
+ if($r->{catalog}) {
+ Tr ++$i % 2 ? (class => 'odd') : ();
+ td 'Catalog no.';
+ td $r->{catalog};
+ end;
+ }
+
if($r->{website}) {
Tr ++$i % 2 ? (class => 'odd') : ();
td 'Links';
@@ -227,7 +235,7 @@ sub edit {
$rid = 0;
}
- my $r = $rid && $self->dbReleaseGet(id => $rid, what => 'vn producers platforms media changes', $rev ? (rev => $rev) : ())->[0];
+ my $r = $rid && $self->dbReleaseGet(id => $rid, what => 'vn extended producers platforms media changes', $rev ? (rev => $rev) : ())->[0];
return 404 if $rid && !$r->{id};
$rev = undef if !$r || $r->{cid} == $r->{latest};
@@ -239,7 +247,7 @@ sub edit {
my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }];
my %b4 = !$rid ? () : (
- (map { $_ => $r->{$_} } qw|type title original gtin language website notes minage platforms patch|),
+ (map { $_ => $r->{$_} } qw|type title original gtin catalog language website notes minage platforms patch|),
released => $r->{released} =~ /^([0-9]{4})([0-9]{2})([0-9]{2})$/ ? [ $1, $2, $3 ] : [ 0, 0, 0 ],
media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}),
producers => join('|||', map "$_->{id},$_->{name}", sort { $a->{id} <=> $b->{id} } @{$r->{producers}}),
@@ -255,6 +263,7 @@ sub edit {
{ name => 'original', required => 0, default => '', maxlength => 250 },
{ name => 'gtin', required => 0, default => '0',
func => [ \&gtintype, 'Not a valid JAN/UPC/EAN code' ] },
+ { name => 'catalog', required => 0, default => '', maxlength => 50 },
{ name => 'language', enum => [ keys %{$self->{languages}} ] },
{ name => 'website', required => 0, default => '', template => 'url' },
{ name => 'released', required => 0, default => 0, multi => 1, template => 'int' },
@@ -284,7 +293,7 @@ sub edit {
!grep !/^(released|platforms|producers|vn)$/ && $frm->{$_} ne $b4{$_}, keys %b4;
my %opts = (
- (map { $_ => $frm->{$_} } qw| type title original gtin language website notes minage platforms editsum patch|),
+ (map { $_ => $frm->{$_} } qw| type title original gtin catalog language website notes minage platforms editsum patch|),
vn => $new_vn,
producers => $producers,
media => $media,
@@ -322,13 +331,14 @@ sub _form {
"General info" => [
[ select => short => 'type', name => 'Type',
options => [ map [ $_, $self->{release_types}[$_] ], 0..$#{$self->{release_types}} ] ],
- [ check => short => 'patch', name => 'This release is a patch to an other release.' ],
+ [ check => short => 'patch', name => 'This release is a patch to another release.' ],
[ input => short => 'title', name => 'Title (romaji)', width => 300 ],
[ input => short => 'original', name => 'Original title', width => 300 ],
[ static => content => 'The original title of this release, leave blank if it already is in the Latin alphabet.' ],
[ select => short => 'language', name => 'Language',
options => [ map [ $_, "$_ ($self->{languages}{$_})" ], sort keys %{$self->{languages}} ] ],
[ input => short => 'gtin', name => 'JAN/UPC/EAN' ],
+ [ input => short => 'catalog', name => 'Catalog number' ],
[ input => short => 'website', name => 'Official website' ],
[ static => label => 'Release date', content => sub {
Select id => 'released', name => 'released';
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
new file mode 100644
index 00000000..e83fcbf7
--- /dev/null
+++ b/lib/VNDB/Handler/Tags.pm
@@ -0,0 +1,706 @@
+
+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,
+ qr{g/debug}, \&tagtree,
+);
+
+
+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 => 'description';
+ 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} = $par->{name};
+ $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, regex => [ qr/^[^,]+$/, 'A comma is not allowed in tag names' ] },
+ { name => 'state', required => 0, default => 0, enum => [ 0..2 ] },
+ { name => 'meta', required => 0, default => 0 },
+ { name => 'alias', required => 0, maxlength => 1024, default => '', regex => [ qr/^[^,]+$/s, 'No comma allowed in aliases' ] },
+ { 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, results => 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;
+ lit 'Make sure you have read the <a href="/g10">guidelines</a>!';
+ end;
+ li "Don't forget to hit the submit button on the bottom of the page to make your changes permanent.";
+ 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', 'You';
+ td colspan => 2, class => 'tc3_1', 'Others';
+ 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', $m->{vote}||0;
+ td class => 'tc3', defined $m->{spoiler} ? $m->{spoiler} : -1;
+ td class => 'tc4';
+ 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 => 'tc5', sprintf '%.2f', $t->{spoiler};
+ 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>&#9656;</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>&#9656;</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';
+ a class => 'addnew', href => "/g/new", ($self->authCan('tagmod')?'Create':'Request').' new tag' if $self->authCan('tag');
+ 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, state => 2);
+ 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;
+}
+
+
+sub tagtree {
+ my $self = shift;
+
+ return 404 if !$self->authCan('tagmod');
+
+ $self->htmlHeader(title => '[DEBUG] The complete tag tree');
+ div class => 'mainbox';
+ h1 '[DEBUG] The complete tag tree';
+
+ div style => 'margin-left: 10px';
+ my $t = $self->dbTagTree(0, -1, 1);
+ my $lvl = $t->[0]{lvl} + 1;
+ for (@$t) {
+ map ul(style => 'margin-left: 15px; list-style-type: none'), 1..($lvl-$_->{lvl}) if $lvl > $_->{lvl};
+ map end, 1..($_->{lvl}-$lvl) if $lvl < $_->{lvl};
+ $lvl = $_->{lvl};
+ li;
+ txt '> ';
+ a href => "/g$_->{tag}", $_->{name};
+ end;
+ }
+ map end, 0..($t->[0]{lvl}-$lvl);
+ end;
+ end;
+ $self->htmlFooter;
+}
+
+
+1;
diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm
index f86cba83..8f2091db 100644
--- a/lib/VNDB/Handler/ULists.pm
+++ b/lib/VNDB/Handler/ULists.pm
@@ -3,7 +3,7 @@ package VNDB::Handler::ULists;
use strict;
use warnings;
-use YAWF ':html';
+use YAWF ':html', ':xml';
use VNDB::Func;
@@ -11,6 +11,7 @@ YAWF::register(
qr{v([1-9]\d*)/vote}, \&vnvote,
qr{v([1-9]\d*)/wish}, \&vnwish,
qr{r([1-9]\d*)/list}, \&rlist,
+ qr{xml/rlist.xml}, \&rlist,
qr{u([1-9]\d*)/wish}, \&wishlist,
qr{u([1-9]\d*)/list}, \&vnlist,
);
@@ -55,6 +56,15 @@ sub vnwish {
sub rlist {
my($self, $id) = @_;
+ my $rid = $id;
+ if(!$rid) {
+ my $f = $self->formValidate(
+ { name => 'id', required => 1, template => 'int' }
+ );
+ return 404 if $f->{_err};
+ $rid = $f->{id};
+ }
+
my $uid = $self->authInfo->{id};
return $self->htmlDenied() if !$uid;
@@ -63,15 +73,24 @@ sub rlist {
);
return 404 if $f->{_err};
- $self->dbVNListDel($uid, $id) if $f->{e} eq 'del';
+ $self->dbVNListDel($uid, $rid) if $f->{e} eq 'del';
$self->dbVNListAdd(
- rid => $id,
+ rid => $rid,
uid => $uid,
$f->{e} =~ /^([rv])(\d+)$/ && $1 eq 'r' ? (rstat => $2) : (vstat => $2)
) if $f->{e} ne 'del';
- (my $ref = $self->reqHeader('Referer')||"/r$id") =~ s/^\Q$self->{url}//;
- $self->resRedirect($ref, 'temp');
+ if($id) {
+ (my $ref = $self->reqHeader('Referer')||"/r$id") =~ s/^\Q$self->{url}//;
+ $self->resRedirect($ref, 'temp');
+ } else {
+ $self->resHeader('Content-type' => 'text/xml');
+ my $st = $self->dbVNListGet(uid => $self->authInfo->{id}, rid => [$rid])->[0];
+ xml;
+ tag 'rlist', uid => $self->authInfo->{id}, rid => $rid;
+ txt $st ? liststat $st : '--';
+ end;
+ }
}
@@ -85,7 +104,7 @@ sub wishlist {
my $f = $self->formValidate(
{ name => 'p', required => 0, default => 1, template => 'int' },
{ name => 'o', required => 0, default => 'a', enum => [ 'a', 'd' ] },
- { name => 's', required => 0, default => 'title', enum => [qw|title added|] },
+ { name => 's', required => 0, default => 'title', enum => [qw|title added wstat|] },
{ name => 'f', required => 0, default => -1, enum => [ -1..$#{$self->{wishlist_status}} ] },
);
return 404 if $f->{_err};
@@ -139,7 +158,7 @@ sub wishlist {
sorturl => "/u$uid/wish?f=$f->{f}",
header => [
[ Title => 'title' ],
- [ Priority => '' ],
+ [ Priority => 'wstat' ],
[ Added => 'added' ],
],
row => sub {
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 2b83b4a5..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',
@@ -110,7 +116,7 @@ sub userpage {
}
if($u->{c_changes}) {
- my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5);
+ my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5, hidden => 1);
h1 class => 'boxtitle';
a href => "/u$uid/hist", 'Recent changes';
end;
@@ -367,8 +373,8 @@ sub edit {
qq|and wishlist (<a href="/u$uid/wish">/u$uid/wish</a>)| ],
[ check => short => 'flags_nsfw', name => 'Disable warnings for images that are not safe for work.' ],
[ select => short => 'skin', name => 'Prefered skin', width => 300, options => [
- map [ $_ eq $self->{skin_default} ? '' : $_, $self->{skins}{$_} ], sort { $self->{skins}{$a} cmp $self->{skins}{$b} } keys %{$self->{skins}} ] ],
- [ textarea => short => 'customcss', name => 'Additional CSS' ],
+ map [ $_ eq $self->{skin_default} ? '' : $_, $self->{skins}{$_}.($self->debug?" [$_]":'') ], sort { $self->{skins}{$a} cmp $self->{skins}{$b} } keys %{$self->{skins}} ] ],
+ [ textarea => short => 'customcss', name => 'Additional <a href="http://en.wikipedia.org/wiki/Cascading_Style_Sheets">CSS</a>' ],
]);
$self->htmlFooter;
}
@@ -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/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index d7cacaa0..f50a8500 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -34,7 +34,7 @@ sub list {
if($q) {
# VNDBID
return $self->resRedirect('/'.$1.$2.(!$3 ? '' : $1 eq 'd' ? '#'.$3 : '.'.$3), 'temp')
- if $q =~ /^([vrptud])([0-9]+)(?:\.([0-9]+))?$/;
+ if $q =~ /^([gvrptud])([0-9]+)(?:\.([0-9]+))?$/;
if(!($q =~ s/^title://)) {
# categories
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..d5d1c0a1 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -125,16 +125,39 @@ 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, results => 999);
+ if(@$t) {
+ div id => 'tagops';
+ a href => '#', 'hide spoilers';
+ a href => '#', class => 'tsel', 'show minor spoilers';
+ a href => '#', 'spoil me!';
+ a href => '#', class => 'sec', 'summary';
+ a href => '#', 'all';
+ 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;
+ txt ' ';
+ }
+ end;
+ }
end;
_releases($self, $v, $r);
@@ -252,7 +275,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 +284,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 +432,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 75471830..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
@@ -151,7 +159,9 @@ sub htmlFormPart {
if(!$o{nolabel}) {
td class => 'label';
if($o{short} && $o{name}) {
- label for => $o{short}, $o{name} ;
+ label for => $o{short};
+ lit $o{name};
+ end;
} elsif($o{label}) {
txt $o{label};
} else {
@@ -251,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 ccf2a9c6..fcd088a4 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,6 +88,7 @@ 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;
@@ -141,6 +143,14 @@ sub _menu {
sub htmlFooter {
my $self = shift;
div id => 'footer';
+
+ my $q = $self->dbRandomQuote;
+ if($q && $q->{vid}) {
+ lit '"';
+ a href => "/v$q->{vid}", style => 'text-decoration: none', $q->{quote};
+ txt qq|"\n|;
+ }
+
txt "vndb $self->{version} | ";
a href => '/d7', 'about us';
txt ' | ';
@@ -149,14 +159,6 @@ sub htmlFooter {
a href => $self->{source_url}, 'source';
end;
end; # /div maincontent
- if($self->debug) {
- div id => 'debug';
- h2 'This is not VNDB!';
- txt 'The real VNDB is ';
- a href => 'http://vndb.org/', 'here';
- txt '.';
- end;
- }
end; # /body
end; # /html
@@ -165,7 +167,7 @@ sub htmlFooter {
lit "\n<!--\n SQL Queries:\n";
for (@{$self->{_YAWF}{DB}{queries}}) {
my $q = !ref $_->[0] ? $_->[0] :
- $_->[0][0].(exists $_->[0][1] ? ' | "'.join('", "', @{$_->[0]}[1..$#{$_->[0]}]).'"' : '');
+ $_->[0][0].(exists $_->[0][1] ? ' | "'.join('", "', map defined()?$_:'NULL', @{$_->[0]}[1..$#{$_->[0]}]).'"' : '');
$q =~ s/^\s//g;
lit sprintf " [%6.2fms] %s\n", $_->[1]*1000, $q;
}
diff --git a/static/f/forms.js b/static/f/forms.js
index e25a880c..b9770203 100644
--- a/static/f/forms.js
+++ b/static/f/forms.js
@@ -7,77 +7,6 @@ function qq(v) {
function shorten(v, l) {
return qq(v.length > l ? v.substr(0, l-3)+'...' : v);
}
-var http_request = false;
-function ajax(url, func) {
- if(http_request)
- http_request.abort();
- http_request = (window.ActiveXObject) ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest();
- if(http_request == null) {
- alert("Your browse does not support the functionality this website requires.");
- return;
- }
- http_request.onreadystatechange = function() {
- if(!http_request || http_request.readyState != 4 || !http_request.responseText)
- return;
- if(http_request.status != 200)
- return alert('Whoops, error! :(');
- func(http_request);
- };
- url += (url.indexOf('?')>=0 ? ';' : '?')+(Math.floor(Math.random()*999)+1);
- http_request.open('GET', url, true);
- http_request.send(null);
-}
-
-
-
-
- /************************\
- * 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;
-}
@@ -176,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;
}
@@ -880,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[2], parseInt(o[2].innerHTML));
+ tglVoteBar(o[1], parseInt(o[1].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')
+ +'&nbsp;</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+')">&nbsp;</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');
+ tglVoteBar(td, 2);
+ td.className = 'tc2';
+ tr.appendChild(td);
+ td = document.createElement('td');
+ tglSpoiler(td, -1);
+ td.className = 'tc3';
+ tr.appendChild(td);
+ td = document.createElement('td');
+ td.className = 'tc4';
+ td.innerHTML = '-';
+ tr.appendChild(td);
+ td = document.createElement('td');
+ td.innerHTML = '-';
+ 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')[1].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 68d40f75..b555c662 100644
--- a/static/f/script.js
+++ b/static/f/script.js
@@ -12,6 +12,45 @@ if(document.addEventListener)document.addEventListener("DOMCont"
function(){if(/loaded|complete/.test(document.readyState)){
clearInterval(t);f()}},10);window.onload=f;}
+var http_request = false;
+function ajax(url, func) {
+ if(http_request)
+ http_request.abort();
+ http_request = (window.ActiveXObject) ? new ActiveXObject('Microsoft.XMLHTTP') : new XMLHttpRequest();
+ if(http_request == null) {
+ alert("Your browse does not support the functionality this website requires.");
+ return;
+ }
+ http_request.onreadystatechange = function() {
+ if(!http_request || http_request.readyState != 4 || !http_request.responseText)
+ return;
+ if(http_request.status != 200)
+ return alert('Whoops, error! :(');
+ func(http_request);
+ };
+ url += (url.indexOf('?')>=0 ? ';' : '?')+(Math.floor(Math.random()*999)+1);
+ http_request.open('GET', url, true);
+ 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;
+}
+
+
@@ -37,7 +76,7 @@ function searchInit() {
l = x('advoptions').getElementsByTagName('input');
for(i=0;i<l.length;i++)
if(l[i].id.substr(0,5) == 'lang_' || l[i].id.substr(0,5) == 'plat_')
- l[i].onclick = function() {
+ l[i].onclick = function() {
searchParse(0, this.parentNode.getElementsByTagName('acronym')[0].title);
};
@@ -221,17 +260,19 @@ function vlDropDown(e) {
} while(o = o.offsetParent);
ddx -= 185;
- var cu = '/r'+tg.id.substr(6)+'/list?e=';
+ var cu = tg.id.substr(6);
var st = tg.innerHTML.split(' / ');
+ if(st[0].indexOf('loading') >= 0)
+ return;
var r = '<ul><li><b>Release status</b></li>';
for(var i=0;i<rstat.length;i++)
- r += st[0] && st[0].indexOf(rstat[i]) >= 0 ? '<li><i>'+rstat[i]+'</i></li>' : '<li><a href="'+cu+'r'+i+'">'+rstat[i]+'</a></li>';
+ r += st[0] && st[0].indexOf(rstat[i]) >= 0 ? '<li><i>'+rstat[i]+'</i></li>' : '<li><a href="#" onclick="return vlMod('+cu+',\'r'+i+'\')">'+rstat[i]+'</a></li>';
r += '</ul><ul><li><b>Play status</b></li>';
for(var i=0;i<vstat.length;i++)
- r += st[1] && st[1].indexOf(vstat[i]) >= 0 ? '<li><i>'+vstat[i]+'</i></li>' : '<li><a href="'+cu+'v'+i+'">'+vstat[i]+'</a></li>';
+ r += st[1] && st[1].indexOf(vstat[i]) >= 0 ? '<li><i>'+vstat[i]+'</i></li>' : '<li><a href="#" onclick="return vlMod('+cu+',\'v'+i+'\')">'+vstat[i]+'</a></li>';
r += '</ul>';
if(tg.innerHTML != '--')
- r += '<ul class="full"><li><a href="'+cu+'del">Remove from VN list</a></li></ul>';
+ r += '<ul class="full"><li><a href="#" onclick="return vlMod('+cu+',\'del\')">Remove from VN list</a></li></ul>';
o = document.createElement('div');
o.id = 'vldd';
@@ -243,6 +284,15 @@ function vlDropDown(e) {
}
}
+function vlMod(rid, act) {
+ document.body.removeChild(x('vldd'));
+ x('rlsel_'+rid).innerHTML = '<b class="patch">loading...</b>';
+ ajax('/xml/rlist.xml?id='+rid+';e='+act, function(hr) {
+ x('rlsel_'+rid).innerHTML = hr.responseXML.getElementsByTagName('rlist')[0].firstChild.nodeValue;
+ });
+ return false;
+}
+
@@ -296,6 +346,49 @@ function jtSel(which, nolink) {
+/* Tag VN spoilers */
+/* lvl = null to not change lvl, lim = null to not change limit */
+function tvsSet(lvl, lim) {
+ var l = x('tagops').getElementsByTagName('a');
+ for(var i=0;i<l.length;i++) {
+ if(i < 3) {
+ if(lvl == null) { /* determine level */
+ if(l[i].className.indexOf('tsel') >= 0)
+ lvl = i;
+ } else { /* set level */
+ 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/, '');
+ }
+ } else {
+ if(lim == null) { /* determine limit */
+ if(l[i].className.indexOf('tsel') >= 0)
+ lim = i == 3;
+ } else { /* set limit */
+ if((i == 3) == lim && l[i].className.indexOf('tsel') < 0)
+ l[i].className += ' tsel';
+ else if((i == 3) != lim && l[i].className.indexOf('tsel') >= 0)
+ l[i].className = l[i].className.replace(/tsel/, '');
+ }
+ }
+ }
+
+ l = x('vntags').getElementsByTagName('span');
+ lim = lim ? 15 : 999;
+ var s=0;
+ for(i=0;i<l.length;i++) {
+ if((lvl < l[i].className.substr(6, 1) || s>=lim) && l[i].className.indexOf('hidden') < 0)
+ l[i].className += ' hidden';
+ if(lvl >= l[i].className.substr(6, 1) && ++s<=lim && 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() {
@@ -362,7 +455,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');
@@ -443,8 +538,28 @@ 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');
+ var sel = 0;
+ for(var i=0;i<l.length;i++)
+ if(l[i] == this) {
+ if(i < 3) {
+ tvsSet(i, null);
+ setCookie('tagspoil', i);
+ } else
+ tvsSet(null, i==3?true:false);
+ }
+ return false;
+ };
+ tvsSet(readCookie('tagspoil'), true);
+ }
+
// Javascript tabs
- if(x('jt_select'))
+ if(x('jt_select'))
jtInit();
// spoiler tags
@@ -455,9 +570,15 @@ DOMLoad(function() {
l[i].onmouseout = function() { this.className = 'spoiler' };
}
+ // Are we really vndb?
+ if(location.hostname != 'vndb.org') {
+ var d = document.createElement('div');
+ d.setAttribute('id', 'debug');
+ d.innerHTML = '<h2>This is not VNDB!</h2>The real VNDB is <a href="http://vndb.org/">here</a>.';
+ document.body.appendChild(d);
+ }
+
// forms.js
- if(x('categories'))
- catLoad();
if(x('relations'))
relLoad();
if(x('jt_box_screenshots'))
@@ -468,6 +589,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/static/s/seinarukana/conf b/static/s/seinarukana/conf
index 6c2fb3a5..11f1f145 100644
--- a/static/s/seinarukana/conf
+++ b/static/s/seinarukana/conf
@@ -6,7 +6,7 @@ name Seinarukana (white)
// text
maintext #131838 // primary text color (also used for the menu links)
grayedout #fc8e77 //#fcdfd9 // color used for grayed-out/non-important things
-standout #a5e2f2 // color of 'stand-out' text
+standout #e93d71 //#a5e2f2 // color of 'stand-out' text
link #5a5fc7 //#9fa1c7 // primary link color (not used for the menu links)
statok #424d81 // generic 'ok' text color (used for vnlist statuses & category browser)
statnok #a43462 // generoc 'not ok' text color (used for above, and as border for NSFW screenshots)
diff --git a/static/s/term/conf b/static/s/term/conf
index 19bb5c80..64530d2c 100644
--- a/static/s/term/conf
+++ b/static/s/term/conf
@@ -2,7 +2,7 @@ name Neon (black)
// text
maintext #0f0
-grayedout #444
+grayedout #aaa
standout #f00
link #ff0
statok #0c0
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index be76bca9..b9800a0a 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -20,8 +20,9 @@ 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 |],
- 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache |],
+ '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 |],
);
my %tables; # table_name => [ [ col1, pri ], ... ]
diff --git a/util/dump.sql b/util/dump.sql
index f23d6d62..5003c6e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -58,9 +58,19 @@ CREATE TABLE producers_rev (
original varchar(200) NOT NULL DEFAULT '',
website varchar(250) NOT NULL DEFAULT '',
lang varchar NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT ''
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT ''
);
+
+-- quotes
+CREATE TABLE quotes (
+ vid integer NOT NULL,
+ quote varchar(250) NOT NULL,
+ PRIMARY KEY(vid, quote)
+);
+
+
-- releases
CREATE TABLE releases (
id SERIAL NOT NULL PRIMARY KEY,
@@ -104,7 +114,8 @@ CREATE TABLE releases_rev (
notes text NOT NULL DEFAULT '',
minage smallint NOT NULL DEFAULT -1,
gtin bigint NOT NULL DEFAULT 0,
- patch boolean NOT NULL DEFAULT FALSE
+ patch boolean NOT NULL DEFAULT FALSE,
+ catalog varchar(50) NOT NULL DEFAULT ''
);
-- releases_vn
@@ -144,6 +155,49 @@ 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)
+);
+
+-- tags_vn_bayesian
+CREATE TABLE tags_vn_bayesian (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
-- threads
CREATE TABLE threads (
id SERIAL NOT NULL PRIMARY KEY,
@@ -165,8 +219,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,
@@ -188,7 +242,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
@@ -285,7 +340,6 @@ CREATE TABLE wlists (
-
-----------------------------------------------
-- F O R E I G N K E Y C H E C K I N G --
-----------------------------------------------
@@ -296,6 +350,7 @@ ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes
ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
@@ -307,10 +362,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;
@@ -428,6 +489,115 @@ 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 state = 2 AND 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_bayesian
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY 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 TEMPORARY 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) and serialized into a table
+ DROP INDEX IF EXISTS tags_vn_bayesian_tag;
+ TRUNCATE tags_vn_bayesian;
+ INSERT INTO tags_vn_bayesian
+ SELECT tag, vid, COUNT(uid) 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;
+ CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+ -- now perform the bayesian ranking calculation
+ UPDATE tags_vn_bayesian tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+ -- and update the VN count in the tags table as well
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
@@ -446,12 +616,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;
@@ -459,6 +635,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
new file mode 100644
index 00000000..1a8c9571
--- /dev/null
+++ b/util/updates/update_2.3.sql
@@ -0,0 +1,205 @@
+
+-- some random VN quotes
+CREATE TABLE quotes (
+ vid integer NOT NULL REFERENCES vn (id),
+ quote varchar(250) NOT NULL,
+ PRIMARY KEY(vid, quote)
+) WITHOUT OIDS;
+
+
+-- catalog numbers for releases
+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 TABLE tags_vn_bayesian (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+) 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 state = 2 AND 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;
+
+-- updates tags_vn_bayesian with rankings of tags
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY 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 TEMPORARY VIEW tags_vn_grouped AS
+ SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+ FROM tags_vn_all GROUP BY tag, vid, uid;
+ -- grouped by (tag, vid) and serialized into a table
+ DROP INDEX IF EXISTS tags_vn_bayesian_tag;
+ TRUNCATE tags_vn_bayesian;
+ INSERT INTO tags_vn_bayesian
+ SELECT tag, vid, COUNT(uid) 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;
+ CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+ -- now perform the bayesian ranking calculation
+ UPDATE tags_vn_bayesian tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+ -- and update the VN count in the tags table as well
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian 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;
+