summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-29 18:28:33 +0100
committerYorhel <git@yorhel.nl>2009-11-29 18:28:33 +0100
commit11290678d9b900baec7a92581a6f87cc70d329ea (patch)
tree9f45a43ce313fff9378646fc0736acf7ee3ff3e2
parentbe023476e67dc76a5fb1344af5b5cb9244d992e7 (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.
-rw-r--r--ChangeLog2
-rw-r--r--lib/VNDB/DB/Tags.pm35
-rw-r--r--lib/VNDB/Handler/Tags.pm62
-rw-r--r--util/dump.sql60
-rw-r--r--util/updates/update_2.10.sql4
5 files changed, 46 insertions, 117 deletions
diff --git a/ChangeLog b/ChangeLog
index 26336cf2..f4d09bff 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -5,6 +5,8 @@ git - ?
- Abstracted all ORDER BY clauses in the DB abstraction layer
- Show language flags on release lists on the homepage
- Allow hiding of NSFW cover even if NSFW warning is disabled
+ - Removed /g/debug
+ - Replaced recursive stored procedures with WITH .. SELECT queries
2.9 - 2009-11-16
- Fixed another bug with the calculation of tags_vn_bayesian.spoiler
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;
diff --git a/util/dump.sql b/util/dump.sql
index cad84ac2..bf13c115 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -491,66 +491,6 @@ 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;
-
-
-- recalculate tags_vn_inherit
CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
BEGIN
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
index 74b1a8a5..04b37bbf 100644
--- a/util/updates/update_2.10.sql
+++ b/util/updates/update_2.10.sql
@@ -52,6 +52,8 @@ $$ LANGUAGE plpgsql;
SELECT tag_vn_calc();
--- tag_vn_childs() is not used anymore
+-- remove unused functions
DROP FUNCTION tag_vn_childs() CASCADE;
+DROP FUNCTION tag_tree(integer, integer, boolean);
+DROP TYPE tag_tree_item;