diff options
author | Yorhel <git@yorhel.nl> | 2009-11-29 18:28:33 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-29 18:28:33 +0100 |
commit | 11290678d9b900baec7a92581a6f87cc70d329ea (patch) | |
tree | 9f45a43ce313fff9378646fc0736acf7ee3ff3e2 /lib | |
parent | be023476e67dc76a5fb1344af5b5cb9244d992e7 (diff) |
Tags: Replaced tag_tree() with WITH .. SELECT queries, and removed /g/debug
The return value of dbTagTree() is also somewhat easier to work with.
Diffstat (limited to 'lib')
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 35 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 62 |
2 files changed, 41 insertions, 56 deletions
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index d21ed5c4..478cd91c 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -73,21 +73,46 @@ sub dbTagGet { } if($o{what} =~ /parents\((\d+)\)/) { - $_->{parents} = $self->dbTagTree($_->{id}, $1, 0) for(@$r); + $_->{parents} = $self->dbTagTree($_->{id}, $1, 1) for(@$r); } if($o{what} =~ /childs\((\d+)\)/) { - $_->{childs} = $self->dbTagTree($_->{id}, $1, 1) for(@$r); + $_->{childs} = $self->dbTagTree($_->{id}, $1) for(@$r); } return wantarray ? ($r, $np) : $r; } -# plain interface to the tag_tree() stored procedure in pgsql +# Walks the tag tree +# 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, $dir) = @_; - return $self->dbAll('SELECT * FROM tag_tree(?, ?, ?)', $id, $lvl||0, $dir?1:0); + my($self, $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 + !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 + WHERE tt.lvl > 0 + AND t.state = 2 + ) SELECT 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') + ); + for my $i (@$r) { + $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ]; + } + my @r = grep !delete($_->{parent}), @$r; + return $id ? $r[0]{'sub'} : \@r; } diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 2a55fd8f..ce754bba 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -18,7 +18,6 @@ YAWF::register( qr{u([1-9]\d*)/tags}, \&usertags, qr{g}, \&tagindex, qr{xml/tags\.xml}, \&tagxml, - qr{g/debug}, \&tagtree, ); @@ -86,7 +85,7 @@ sub tagpage { a href => '/g', mt '_tagp_indexlink'; for ($p[$_], reverse @r) { txt ' > '; - a href => "/g$_->{tag}", $_->{name}; + a href => "/g$_->{id}", $_->{name}; } txt " > $t->{name}\n"; } @@ -133,38 +132,27 @@ sub tagpage { 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'; h1 mt $index ? '_tagp_tree' : '_tagp_childs'; ul class => 'tagtree'; - for my $p (sort { @{$b->{childs}} <=> @{$a->{childs}} } @tags) { + for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) { li; - a href => "/g$p->{tag}", $p->{name}; + a href => "/g$p->{id}", $p->{name}; b class => 'grayedout', " ($p->{c_vns})" if $p->{c_vns}; - end, next if !@{$p->{childs}}; + end, next if !@{$p->{'sub'}}; ul; - for (0..$#{$p->{childs}}) { - last if $_ >= 5 && @{$p->{childs}} > 6; + for (0..$#{$p->{'sub'}}) { + last if $_ >= 5 && @{$p->{'sub'}} > 6; li; txt '> '; - a href => "/g$p->{childs}[$_]{tag}", $p->{childs}[$_]{name}; - b class => 'grayedout', " ($p->{childs}[$_]{c_vns})" if $p->{childs}[$_]{c_vns}; + a href => "/g$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name}; + b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $p->{'sub'}[$_]{c_vns}; end; } - if(@{$p->{childs}} > 6) { + if(@{$p->{'sub'}} > 6) { li; txt '> '; - a href => "/g$p->{tag}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{childs}}-5; + a href => "/g$p->{id}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{'sub'}}-5; end; } end; @@ -536,7 +524,7 @@ sub tagindex { end; end; - my $t = $self->dbTagTree(0, 2, 1); + my $t = $self->dbTagTree(0, 2); _childtags($self, {childs => $t}, 1); table class => 'mainbox threelayout'; @@ -623,32 +611,4 @@ sub tagxml { } -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; |