diff options
-rw-r--r-- | data/lang.txt | 14 | ||||
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 8 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 43 | ||||
-rw-r--r-- | lib/VNDB/DB/Traits.pm | 39 | ||||
-rw-r--r-- | lib/VNDB/Func.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Chars.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 16 | ||||
-rw-r--r-- | lib/VNDB/Handler/Traits.pm | 74 | ||||
-rw-r--r-- | util/sql/func.sql | 36 | ||||
-rw-r--r-- | util/updates/update_2.19.sql | 14 |
10 files changed, 179 insertions, 73 deletions
diff --git a/data/lang.txt b/data/lang.txt index e45a8577..89558ec6 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -5289,6 +5289,20 @@ cs*: hu*: nl : nog [_1] [quant,_1,kenmerk,kenmerken] +:_traitp_charlist +en : Characters +ru*: +cs*: +hu*: +nl : Karakters + +:_traitp_nochars +en : This trait has not been linked to any characters yet, or they were hidden because of your spoiler settings. +ru*: +cs*: +hu*: +nl : Dit kenmerk is nog niet gekoppeld aan een karakter, of deze worden niet weergegeven in verband met je spoilerinstelling. + # Trait add/edit form (/i+/edit, /i+/add, /i/new) diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index e1f045a7..22ddd5d5 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -8,7 +8,7 @@ use Exporter 'import'; our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|; -# options: id rev what results page +# options: id rev traitspoil trait_inc trait_exc what results page # what: extended traits changes sub dbCharGet { my $self = shift; @@ -16,6 +16,7 @@ sub dbCharGet { page => 1, results => 10, what => '', + traitspoil => 0, @_ ); @@ -23,6 +24,11 @@ sub dbCharGet { !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (), $o{id} ? ( 'c.id = ?' => $o{id} ) : (), $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (), + $o{trait_inc} ? ( + 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)', + [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{traitspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (), + $o{trait_exc} ? ( + 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (), ); my @select = (qw|c.id cr.name cr.original|, 'cr.id AS cid'); diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 8ed4cec6..eee73f10 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -5,12 +5,12 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|; +our @EXPORT = qw|dbTagGet dbTTTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|; # %options->{ id noid name search state meta page results what sort reverse } # what: parents childs(n) aliases addedby -# sort: id name added vns +# sort: id name added items sub dbTagGet { my $self = shift; my %o = ( @@ -39,7 +39,7 @@ sub dbTagGet { 't.meta = ?' => $o{meta}?1:0 ) : (), ); my @select = ( - qw|t.id t.meta t.name t.description t.state t.cat t.c_vns|, + qw|t.id t.meta t.name t.description t.state t.cat t.c_items|, q|extract('epoch' from t.added) as added|, $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (), ); @@ -49,7 +49,7 @@ sub dbTagGet { id => 't.id %s', name => 't.name %s', added => 't.added %s', - vns => 't.c_vns %s', + items => 't.c_items %s', }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| @@ -73,40 +73,41 @@ sub dbTagGet { } if($o{what} =~ /parents\((\d+)\)/) { - $_->{parents} = $self->dbTagTree($_->{id}, $1, 1) for(@$r); + $_->{parents} = $self->dbTTTree(tag => $_->{id}, $1, 1) for(@$r); } if($o{what} =~ /childs\((\d+)\)/) { - $_->{childs} = $self->dbTagTree($_->{id}, $1) for(@$r); + $_->{childs} = $self->dbTTTree(tag => $_->{id}, $1) for(@$r); } return wantarray ? ($r, $np) : $r; } -# Walks the tag tree +# Walks the tag/trait tree +# type = tag | trait # id = tag to start with, or 0 to start with top-level tags # lvl = max. recursion level # back = false for parent->child, true for child->parent -# Returns: [ { id, name, c_vns, sub => [ { id, name, c_vns, sub => [..] }, .. ] }, .. ] -sub dbTagTree { - my($self, $id, $lvl, $back) = @_; +# Returns: [ { id, name, c_items, sub => [ { id, name, c_items, sub => [..] }, .. ] }, .. ] +sub dbTTTree { + my($self, $type, $id, $lvl, $back) = @_; $lvl ||= 15; - my $r = $self->dbAll(q| - WITH RECURSIVE tagtree(lvl, id, parent, name, c_vns) AS ( - SELECT ?::integer, id, 0, name, c_vns - FROM tags + my $r = $self->dbAll(qq| + WITH RECURSIVE thetree(lvl, id, parent, name, c_items) AS ( + SELECT ?::integer, id, 0, name, c_items + FROM ${type}s !W UNION ALL - SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_vns - FROM tagtree tt - JOIN tags_parents tp ON !s - JOIN tags t ON !s + SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_items + FROM thetree tt + JOIN ${type}s_parents tp ON !s + JOIN ${type}s t ON !s WHERE tt.lvl > 0 AND t.state = 2 - ) SELECT DISTINCT id, parent, name, c_vns FROM tagtree ORDER BY name|, $lvl, - $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM tags_parents WHERE tag = id)' => 1, 'state = 2' => 1}, - !$back ? ('tp.parent = tt.id', 't.id = tp.tag') : ('tp.tag = tt.id', 't.id = tp.parent') + ) SELECT DISTINCT id, parent, name, c_items FROM thetree ORDER BY name|, $lvl, + $id ? {'id = ?' => $id} : {"NOT EXISTS(SELECT 1 FROM ${type}s_parents WHERE $type = id)" => 1, 'state = 2' => 1}, + !$back ? ('tp.parent = tt.id', "t.id = tp.$type") : ("tp.$type = tt.id", 't.id = tp.parent') ); for my $i (@$r) { $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ]; diff --git a/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm index a501c942..0ee83f10 100644 --- a/lib/VNDB/DB/Traits.pm +++ b/lib/VNDB/DB/Traits.pm @@ -10,12 +10,12 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbTraitGet dbTraitTree dbTraitEdit dbTraitAdd|; +our @EXPORT = qw|dbTraitGet dbTraitEdit dbTraitAdd|; # Options: id what results page sort reverse # what: parents childs(n) addedby -# sort: id name groupname added +# sort: id name groupname added items sub dbTraitGet { my $self = shift; my %o = ( @@ -38,7 +38,7 @@ sub dbTraitGet { ); my @select = ( - qw|t.id t.meta t.name t.description t.state t.alias t."group" |, + qw|t.id t.meta t.name t.description t.state t.alias t."group" t.c_items|, 'tg.name AS groupname', q|extract('epoch' from t.added) as added|, $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (), ); @@ -50,6 +50,7 @@ sub dbTraitGet { name => 't.name %s', groupname => 'tg.name %s NULLS FIRST, t.name %1$s', added => 't.added %s', + items => 't.c_items %s', }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| @@ -62,45 +63,17 @@ sub dbTraitGet { ); if($o{what} =~ /parents\((\d+)\)/) { - $_->{parents} = $self->dbTraitTree($_->{id}, $1, 1) for(@$r); + $_->{parents} = $self->dbTTTree(trait => $_->{id}, $1, 1) for(@$r); } if($o{what} =~ /childs\((\d+)\)/) { - $_->{childs} = $self->dbTraitTree($_->{id}, $1) for(@$r); + $_->{childs} = $self->dbTTTree(trait => $_->{id}, $1) for(@$r); } return wantarray ? ($r, $np) : $r; } -# almost much equivalent to dbTagTree -sub dbTraitTree { - my($self, $id, $lvl, $back) = @_; - $lvl ||= 15; - my $r = $self->dbAll(q| - WITH RECURSIVE traittree(lvl, id, parent, name) AS ( - SELECT ?::integer, id, 0, name - FROM traits - !W - UNION ALL - SELECT tt.lvl-1, t.id, tt.id, t.name - FROM traittree tt - JOIN traits_parents tp ON !s - JOIN traits t ON !s - WHERE tt.lvl > 0 - AND t.state = 2 - ) SELECT DISTINCT id, parent, name FROM traittree ORDER BY name|, $lvl, - $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM traits_parents WHERE trait = id)' => 1, 'state = 2' => 1}, - !$back ? ('tp.parent = tt.id', 't.id = tp.trait') : ('tp.trait = tt.id', 't.id = tp.parent') - ); - for my $i (@$r) { - $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ]; - } - my @r = grep !delete($_->{parent}), @$r; - return $id ? $r[0]{'sub'} : \@r; -} - - # args: trait id, %options->{ columns in the traits table + parents } sub dbTraitEdit { my($self, $id, %o) = @_; diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 20ade586..edb6e3aa 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -147,7 +147,7 @@ sub childtags { for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) { li; a href => "/$type$p->{id}", $p->{name}; - b class => 'grayedout', " ($p->{c_vns})" if $type eq 'g' && $p->{c_vns}; + b class => 'grayedout', " ($p->{c_items})" if $p->{c_items}; end, next if !@{$p->{'sub'}}; ul; for (0..$#{$p->{'sub'}}) { @@ -155,7 +155,7 @@ sub childtags { li; txt '> '; a href => "/$type$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name}; - b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $type eq 'g' && $p->{'sub'}[$_]{c_vns}; + b class => 'grayedout', " ($p->{sub}[$_]{c_items})" if $p->{'sub'}[$_]{c_items}; end; } if(@{$p->{'sub'}} > 6) { diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm index b05d94d7..20be01b4 100644 --- a/lib/VNDB/Handler/Chars.pm +++ b/lib/VNDB/Handler/Chars.pm @@ -218,6 +218,10 @@ sub edit { $frm->{traits} = \@traits; my $nrev = $self->dbItemEdit(c => $id ? $r->{cid} : undef, %$frm); + + # TEMPORARY SOLUTION! I'll investigate more efficient solutions and incremental updates whenever I have more data + $self->dbExec('SELECT traits_chars_calc()'); + return $self->resRedirect("/c$nrev->{iid}.$nrev->{rev}", 'post'); } } diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 3d371a8a..2326ed17 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -33,12 +33,12 @@ sub tagpage { { get => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] }, { get => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, { get => 'p', required => 0, default => 1, template => 'int' }, - { get => 'm', required => 0, default => -1, enum => [qw|0 1 2|] }, + { get => 'm', required => 0, default => undef, enum => [qw|0 1 2|] }, { get => 'fil', required => 0 }, ); return $self->resNotFound if $f->{_err}; my $tagspoil = $self->reqCookie('tagspoil')||''; - $f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0 if $f->{m} == -1; + $f->{m} //= $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0; $f->{fil} //= $self->authPref('filter_vn'); my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->filFetchDB(vn => $f->{fil}, undef, { @@ -270,7 +270,7 @@ sub _set_childs_cat { } }; - my $childs = $self->dbTagTree($tag, 25); + my $childs = $self->dbTTTree(tag => $tag, 25); $e->($childs); } @@ -624,7 +624,7 @@ sub tagindex { end; end; - my $t = $self->dbTagTree(0, 2); + my $t = $self->dbTTTree(tag => 0, 2); childtags($self, mt('_tagidx_tree'), 'g', {childs => $t}); table class => 'mainbox threelayout'; @@ -649,13 +649,13 @@ sub tagindex { # Popular td; a class => 'addnew', href => "/g/links", mt '_tagidx_rawtags'; - $r = $self->dbTagGet(sort => 'vns', reverse => 1, meta => 0, results => 10); + $r = $self->dbTagGet(sort => 'items', reverse => 1, meta => 0, results => 10); h1 mt '_tagidx_popular'; ul; for (@$r) { li; a href => "/g$_->{id}", $_->{name}; - txt " ($_->{c_vns})"; + txt " ($_->{c_items})"; end; } end; @@ -702,14 +702,14 @@ sub fulltree { li; txt '> '; a href => "/g$_->{id}", $_->{name}; - b class => 'grayedout', " ($_->{c_vns})" if $_->{c_vns}; + b class => 'grayedout', " ($_->{c_items})" if $_->{c_items}; end; $e->($_->{sub}) if $_->{sub}; } end; }; - my $tags = $self->dbTagTree(0, 25); + my $tags = $self->dbTTTree(tag => 0, 25); $self->htmlHeader(title => '[DEBUG] Tag tree', noindex => 1); div class => 'mainbox'; h1 '[DEBUG] Tag tree'; diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm index f86fb4cc..9d02ec0d 100644 --- a/lib/VNDB/Handler/Traits.pm +++ b/lib/VNDB/Handler/Traits.pm @@ -24,6 +24,14 @@ sub traitpage { my $t = $self->dbTraitGet(id => $trait, what => 'parents(0) childs(2)')->[0]; return $self->resNotFound if !$t; + my $f = $self->formValidate( + { get => 'p', required => 0, default => 1, template => 'int' }, + { get => 'm', required => 0, default => undef, enum => [qw|0 1 2|] }, + ); + return $self->resNotFound if $f->{_err}; + my $tagspoil = $self->reqCookie('tagspoil')||''; + $f->{m} //= $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0; + my $title = mt '_traitp_title', $t->{meta}?0:1, $t->{name}; $self->htmlHeader(title => $title, noindex => $t->{state} != 2); $self->htmlMainTabs('i', $t); @@ -69,8 +77,50 @@ sub traitpage { childtags($self, mt('_traitp_childs'), 'i', $t) if @{$t->{childs}}; - # TODO: list of characters - + if(!$t->{meta} && $t->{state} == 2) { + my($chars, $np) = $self->dbCharGet( + trait_inc => $trait, + traitspoil => $f->{m}, + results => 50, page => $f->{p}, + ); + + div class => 'mainbox'; + h1 mt '_traitp_charlist'; + + p class => 'browseopts'; + # Q: tagp!? A: lazyness >_> + a href => "/i$trait?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0'; + a href => "/i$trait?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1'; + a href => "/i$trait?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2'; + end; + + if(!@$chars) { + p; br; br; txt mt '_traitp_nochars'; end; + } + # not really cached at the moment + # p; br; txt mt '_traitp_cached'; end; + end 'div'; + + # TODO: proper table with info and such + $self->htmlBrowse( + class => 'traitchars', + options => $f, + nextpage => $np, + items => $chars, + pageurl => "/i$trait?m=$f->{m}", + sorturl => "/i$trait?m=$f->{m}", + header => [ + [ 'Name' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr $n%2?(class => 'odd') : (); + td class => 'tc1'; a href => "/c$l->{id}", $l->{name}; end; + end; + }, + ) if @$chars; + } + $self->htmlFooter; } @@ -126,7 +176,10 @@ sub traitedit { $trait = $self->dbTraitAdd(%opts); } else { $self->dbTraitEdit($trait, %opts, upddate => $frm->{state} == 2 && $t->{state} != 2) if $trait; - _set_childs_group($self, $trait, $group||$trait) if ($group||0) != ($t->{group}); + _set_childs_group($self, $trait, $group||$trait) if ($group||0) != ($t->{group}||0); + + # TEMPORARY SOLUTION! I'll investigate more efficient solutions and incremental updates whenever I have more data + $self->dbExec('SELECT traits_chars_calc()'); } $self->resRedirect("/i$trait", 'post'); return; @@ -185,7 +238,7 @@ sub _set_childs_group { $e->($_->{sub}) if $_->{sub}; } }; - $e->($self->dbTraitTree($trait, 25)); + $e->($self->dbTTTree(trait => $trait, 25)); } @@ -272,7 +325,7 @@ sub traitindex { end; end; - my $t = $self->dbTraitTree(0, 2); + my $t = $self->dbTTTree(trait => 0, 2); childtags($self, mt('_traiti_tree'), 'i', {childs => $t}); table class => 'mainbox threelayout'; @@ -298,7 +351,16 @@ sub traitindex { # Popular td; h1 mt '_traiti_popular'; - p 'TODO'; + ul; + $r = $self->dbTraitGet(sort => 'items', reverse => 1, results => 10); + for (@$r) { + li; + b class => 'grayedout', $_->{groupname}.' / ' if $_->{group}; + a href => "/i$_->{id}", $_->{name}; + txt " ($_->{c_items})"; + end; + } + end; end; # Moderation queue diff --git a/util/sql/func.sql b/util/sql/func.sql index 0938f76d..d7081aef 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -127,13 +127,47 @@ BEGIN -- recreate index CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); -- and update the VN count in the tags table - UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; END; $$ LANGUAGE plpgsql; +-- recalculate traits_chars +CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$ +BEGIN + TRUNCATE traits_chars; + INSERT INTO traits_chars (tid, cid, spoil) + -- all char<->trait links of the latest revisions, including chars inherited from child traits + -- (also includes meta traits, because they could have a normal trait as parent) + WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS ( + SELECT 15, tid, c.id, spoil, false + FROM chars_traits ct + JOIN chars c ON c.latest = ct.cid + WHERE NOT c.hidden + UNION ALL + SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta + FROM traits_chars_all tc + JOIN traits_parents tp ON tp.trait = tc.tid + JOIN traits t ON t.id = tp.parent + WHERE t.state = 2 + AND tc.lvl > 0 + ) + -- now grouped by (tid, cid) and with meta traits filtered out + SELECT tid, cid, (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler + FROM traits_chars_all + WHERE NOT meta + GROUP BY tid, cid; + -- and update the VN count in the tags table + UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id); + RETURN; +END; +$$ LANGUAGE plpgsql; + + + + ---------------------------------------------------------- diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql index 14c3e30e..1332d666 100644 --- a/util/updates/update_2.19.sql +++ b/util/updates/update_2.19.sql @@ -12,7 +12,8 @@ CREATE TABLE traits ( added timestamptz NOT NULL DEFAULT NOW(), state smallint NOT NULL DEFAULT 0, addedby integer NOT NULL DEFAULT 0 REFERENCES users (id), - "group" integer + "group" integer, + c_items integer NOT NULL DEFAULT 0 ); ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id); @@ -24,6 +25,7 @@ CREATE TABLE traits_parents ( CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +ALTER TABLE tags RENAME c_vns TO c_items; -- character database -> chars @@ -75,6 +77,14 @@ CREATE TABLE chars_vns ( PRIMARY KEY(cid, vid, rid) ); +-- cache table +CREATE TABLE traits_chars ( + cid integer NOT NULL REFERENCES chars (id), + tid integer NOT NULL REFERENCES traits (id), + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(cid, tid) +); + CREATE SEQUENCE charimg_seq; @@ -144,5 +154,7 @@ UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more INSERT INTO edit_char_traits VALUES (4, 0), (12, 0); SELECT edit_char_commit(); +SELECT traits_chars_calc(); + -- */ |