summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-02-14 18:08:26 +0100
committerYorhel <git@yorhel.nl>2009-02-14 18:08:26 +0100
commite79211c11fa51e6978a74f6fbf0bba68a33a4f4b (patch)
tree3ef933fa278fabd0873c52e4261d74fd415b53db /util
parent6089007c7b57c3ff158d6169ec22ad6d9325a7ec (diff)
Made a start on the tagging system
Mostly playing around with the possibilities, tag page layout & database scheme are far from final
Diffstat (limited to 'util')
-rw-r--r--util/updates/update_2.3.sql89
1 files changed, 89 insertions, 0 deletions
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;
+
+