summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--data/style.css8
-rw-r--r--lib/VNDB/DB/Tags.pm55
-rw-r--r--lib/VNDB/Handler/Tags.pm95
-rw-r--r--util/updates/update_2.3.sql89
4 files changed, 247 insertions, 0 deletions
diff --git a/data/style.css b/data/style.css
index 59ef3008..8d3cccef 100644
--- a/data/style.css
+++ b/data/style.css
@@ -963,6 +963,14 @@ ul#catselect li li.exc { background-position: 0px -33px; color: $statnok$; }
+/***** Tag page *****/
+
+.tagtree { margin-left: 20px; list-style-type: none }
+.tagtree ul { margin-left: 10px; list-style-type: none }
+
+
+
+
/***** Warning/Notice Box *****/
div.warning, div.notice {
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
new file mode 100644
index 00000000..5d0263fb
--- /dev/null
+++ b/lib/VNDB/DB/Tags.pm
@@ -0,0 +1,55 @@
+
+package VNDB::DB::Tags;
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|dbTagGet|;
+
+
+# %options->{ id page results order what }
+# what: parents childs(n)
+sub dbTagGet {
+ my $self = shift;
+ my %o = (
+ order => 't.id ASC',
+ page => 1,
+ results => 10,
+ what => '',
+ @_
+ );
+
+ my %where = (
+ $o{id} ? (
+ 't.id = ?' => $o{id} ) : (),
+ );
+
+ my($r, $np) = $self->dbPage(\%o, q|
+ SELECT t.id, t.meta, t.name, t.aliases, t.description
+ FROM tags t
+ !W
+ ORDER BY !s|,
+ \%where, $o{order}
+ );
+
+ if($o{what} =~ /parents/) {
+ $_->{parents} = $self->dbAll(q|SELECT lvl, tag, name FROM tag_tree(?, -1, false)|, $_->{id}) for (@$r);
+ }
+
+ if($o{what} =~ /childs\((\d+)\)/) {
+ $_->{childs} = $self->dbAll(q|SELECT lvl, tag, name FROM tag_tree(?, ?, true)|, $_->{id}, $1) for (@$r);
+ }
+
+ #if(@$r && $o{what} =~ /(?:parents)/) {
+ #my %r = map {
+ # ($r->[$_]{id}, $_)
+ #} 0..$#$r;
+ #}
+
+ return wantarray ? ($r, $np) : $r;
+}
+
+
+1;
+
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
new file mode 100644
index 00000000..0dfcc9bc
--- /dev/null
+++ b/lib/VNDB/Handler/Tags.pm
@@ -0,0 +1,95 @@
+
+package VNDB::Handler::Tags;
+
+
+use strict;
+use warnings;
+use YAWF ':html';
+use VNDB::Func;
+
+
+YAWF::register(
+ qr{g([1-9]\d*)}, \&tagpage,
+ qr{g}, \&tagtree,
+);
+
+
+sub tagpage {
+ my($self, $tag) = @_;
+
+ # fetch tag
+ my $t = $self->dbTagGet(id => $tag, what => 'parents childs(2)')->[0];
+ return 404 if !$t;
+
+ my $title = ($t->{meta} ? 'Meta tag: ' : 'Tag: ').$t->{name};
+ $self->htmlHeader(title => $title);
+ div class => 'mainbox';
+ h1 $title;
+ h2 class => 'alttitle', 'a.k.a. '.join(', ', split /\n/, $t->{aliases}) if $t->{aliases};
+
+ # TODO: handle multiple parents here
+ p;
+ a href => '/g', 'Tags';
+ for (sort { $a->{lvl} <=> $b->{lvl} } @{$t->{parents}}) {
+ txt ' > ';
+ a href => "/g$_->{tag}", $_->{name};
+ }
+ txt ' > '.$t->{name};
+ end;
+
+ if($t->{description}) {
+ p;
+ lit bb2html $t->{description};
+ end;
+ }
+
+ if(@{$t->{childs}}) {
+ ul class => 'tagtree';
+ li 'Child tags';
+ my $lvl = $t->{childs}[0]{lvl} + 1;
+ for (@{$t->{childs}}) {
+ map ul, 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->{childs}[0]{lvl}-$lvl);
+ end;
+ }
+
+ end;
+ $self->htmlFooter;
+}
+
+
+sub tagtree {
+ my $self = shift;
+
+ $self->htmlHeader(title => '[DEBUG] The complete tag tree');
+ div class => 'mainbox';
+ h1 '[DEBUG] The complete tag tree';
+
+ my $t = $self->dbAll('SELECT * FROM tag_tree(0, -1, true)');
+ ul class => 'tagtree';
+ li "This page won't make it to the final version. (At least, not in this form)\n\n";
+ my $lvl = $t->[0]{lvl} + 1;
+ for (@$t) {
+ map ul, 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/updates/update_2.3.sql b/util/updates/update_2.3.sql
index d43718c9..9ae226df 100644
--- a/util/updates/update_2.3.sql
+++ b/util/updates/update_2.3.sql
@@ -9,3 +9,92 @@ CREATE TABLE quotes (
-- catalog numbers for releases
ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT '';
+
+
+
+
+-- tagging system
+
+CREATE TABLE tags (
+ id SERIAL NOT NULL PRIMARY KEY,
+ name varchar(250) NOT NULL UNIQUE,
+ aliases text NOT NULL DEFAULT '',
+ description text NOT NULL DEFAULT '',
+ meta boolean NOT NULL DEFAULT FALSE
+) 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, -- -3..3 (0 isn't actually used...)
+ spoiler boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY(tag, vid, uid)
+) WITHOUT OIDS;
+
+
+CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text);
+
+-- 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
+ FROM tags t
+ WHERE 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
+ FROM tags_parents tp
+ JOIN tags t ON t.id = tp.tag
+ WHERE tp.parent = tag
+ 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
+ FROM tags_parents tp
+ JOIN tags t ON t.id = tp.parent
+ WHERE tp.tag = tag
+ 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;
+
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-18 11:31:32 +0000