summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-03-22 10:55:48 +0100
committerYorhel <git@yorhel.nl>2009-03-22 10:55:48 +0100
commit09f80833cef287a5a4f5cf1018b19f8c2db3341e (patch)
tree000e757f9b11a6ce069c13da9b648751d6b453b3 /util
parent5f744cdcc18e0d67df8162ffb76e752a6f7a4a9c (diff)
parent1b99dd8701ce572c8ac20b71b3eb9b471b69d788 (diff)
Merge branch 'tagging' into beta
Conflicts: lib/VNDB/DB/Discussions.pm util/updates/update_2.3.sql
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbgraph.pl3
-rw-r--r--util/dump.sql179
-rw-r--r--util/updates/update_2.3.sql189
3 files changed, 365 insertions, 6 deletions
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index 5fd52b75..b9800a0a 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -20,7 +20,8 @@ my %subgraphs = (
'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_vn |],
'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |],
'Users' => [qw| CCFFFF users votes vnlists rlists wlists |],
- 'Discussion board' => [qw| FFDCDC threads threads_tags threads_posts |],
+ 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |],
+ 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |],
'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes |],
);
diff --git a/util/dump.sql b/util/dump.sql
index e3652a0f..993baaa3 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -155,6 +155,40 @@ CREATE TABLE stats_cache (
count integer NOT NULL DEFAULT 0
);
+-- tags
+CREATE TABLE tags (
+ id SERIAL NOT NULL PRIMARY KEY,
+ name varchar(250) NOT NULL UNIQUE,
+ description text NOT NULL DEFAULT '',
+ meta boolean NOT NULL DEFAULT FALSE,
+ added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
+ state smallint NOT NULL DEFAULT 0,
+ c_vns integer NOT NULL DEFAULT 0
+);
+
+-- tags_aliases
+CREATE TABLE tags_aliases (
+ alias varchar(250) NOT NULL PRIMARY KEY,
+ tag integer NOT NULL,
+);
+
+-- tags_parents
+CREATE TABLE tags_parents (
+ tag integer NOT NULL,
+ parent integer NOT NULL,
+ PRIMARY KEY(tag, parent)
+);
+
+-- tags_vn
+CREATE TABLE tags_vn (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ uid integer NOT NULL,
+ vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
+ spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
+ PRIMARY KEY(tag, vid, uid)
+);
+
-- threads
CREATE TABLE threads (
id SERIAL NOT NULL PRIMARY KEY,
@@ -176,8 +210,8 @@ CREATE TABLE threads_posts (
PRIMARY KEY(tid, num)
);
--- threads_tags
-CREATE TABLE threads_tags (
+-- threads_boards
+CREATE TABLE threads_boards (
tid integer NOT NULL DEFAULT 0,
type character(2) NOT NULL DEFAULT 0,
iid integer NOT NULL DEFAULT 0,
@@ -199,7 +233,8 @@ CREATE TABLE users (
c_changes integer NOT NULL DEFAULT 0,
skin varchar(128) NOT NULL DEFAULT '',
customcss text NOT NULL DEFAULT '',
- ip inet NOT NULL DEFAULT '0.0.0.0'
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ c_tags integer NOT NULL DEFAULT 0
);
-- vn
@@ -296,6 +331,32 @@ CREATE TABLE wlists (
+-----------------
+-- V I E W S --
+-----------------
+
+
+CREATE OR REPLACE VIEW tags_vn_all AS
+ SELECT *
+ FROM tags_vn
+ UNION
+ SELECT *
+ FROM tag_vn_childs();
+
+CREATE OR REPLACE VIEW tags_vn_grouped AS
+ SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+ FROM tags_vn_all
+ GROUP BY tag, vid, uid;
+
+CREATE OR REPLACE VIEW tags_vn_inherited AS
+ SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating,
+ (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
+ FROM tags_vn_grouped
+ GROUP BY tag, vid;
+
+
+
+
-----------------------------------------------
-- F O R E I G N K E Y C H E C K I N G --
@@ -319,10 +380,16 @@ ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_r
ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_tags ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraph (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id) DEFERRABLE INITIALLY DEFERRED;
@@ -440,6 +507,101 @@ 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;
+
+
+-- returns all votes inherited by childs
+-- UNION this with tags_vn and you have all votes for all tags
+CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
+DECLARE
+ r tags_vn%rowtype;
+ i RECORD;
+ l RECORD;
+BEGIN
+ FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
+ FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
+ FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
+ RETURN NEXT r;
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+
+-- recalculate tags_vn_stored
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ DROP TABLE IF EXISTS tags_vn_stored;
+ CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
+ CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
+
+ UPDATE tags_vn_stored tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
+
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
@@ -458,12 +620,18 @@ BEGIN
ELSE
UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
END IF;
- ELSE
+ ELSIF TG_TABLE_NAME = 'changes' THEN
IF TG_OP = 'INSERT' THEN
UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
ELSE
UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
END IF;
+ ELSIF TG_TABLE_NAME = 'tags_vn' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
+ END IF;
END IF;
RETURN NULL;
END;
@@ -471,6 +639,7 @@ $$ LANGUAGE 'plpgsql';
CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-- the stats_cache table
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql
index 21db32ca..32b04b31 100644
--- a/util/updates/update_2.3.sql
+++ b/util/updates/update_2.3.sql
@@ -14,3 +14,192 @@ ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT '';
-- aliases field for producers
ALTER TABLE producers_rev ADD COLUMN alias varchar(500) NOT NULL DEFAULT '';
+
+
+-- tagging system
+
+CREATE TABLE tags (
+ id SERIAL NOT NULL PRIMARY KEY,
+ name varchar(250) NOT NULL UNIQUE,
+ description text NOT NULL DEFAULT '',
+ meta boolean NOT NULL DEFAULT FALSE,
+ added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()),
+ state smallint NOT NULL DEFAULT 0, -- 0: awaiting moderation, 1: deleted, 2: accepted
+ c_vns integer NOT NULL DEFAULT 0
+) WITHOUT OIDS;
+
+CREATE TABLE tags_aliases (
+ alias varchar(250) NOT NULL PRIMARY KEY,
+ tag integer NOT NULL REFERENCES tags (id) DEFERRABLE INITIALLY DEFERRED
+) 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 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
+ spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
+ PRIMARY KEY(tag, vid, uid)
+) WITHOUT OIDS;
+
+
+CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);
+
+-- 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, 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;
+
+-- returns all votes inherited by childs
+-- UNION this with tags_vn and you have all votes for all tags
+CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
+DECLARE
+ r tags_vn%rowtype;
+ i RECORD;
+ l RECORD;
+BEGIN
+ FOR l IN SElECT id FROM tags WHERE meta = FALSE AND AND state = 2 EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
+ FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
+ FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
+ RETURN NEXT r;
+ END LOOP;
+ END LOOP;
+ END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+
+-- all votes for all tags
+CREATE OR REPLACE VIEW tags_vn_all AS
+ SELECT *
+ FROM tags_vn
+ UNION
+ SELECT *
+ FROM tag_vn_childs();
+
+-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
+CREATE OR REPLACE VIEW tags_vn_grouped AS
+ SELECT tag, vid, uid, AVG(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+ FROM tags_vn_all
+ GROUP BY tag, vid, uid;
+
+-- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags)
+CREATE OR REPLACE VIEW tags_vn_inherited AS
+ SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating,
+ (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
+ FROM tags_vn_grouped
+ GROUP BY tag, vid;
+
+-- creates/updates a table with bayesian rankings of tags
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ DROP TABLE IF EXISTS tags_vn_stored;
+ CREATE TABLE tags_vn_stored AS SELECT * FROM tags_vn_inherited;
+ CREATE INDEX tags_vn_stored_tag ON tags_vn_stored (tag);
+
+ UPDATE tags_vn_stored tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_stored WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvs.tag) + users)::real;
+
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_stored WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
+
+
+-- Cache users tag vote count
+ALTER TABLE users ADD COLUMN c_tags integer NOT NULL DEFAULT 0;
+UPDATE users SET c_tags = (SELECT COUNT(*) FROM tags_vn WHERE uid = id);
+
+CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_TABLE_NAME = 'votes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'changes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
+ ELSE
+ UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'tags_vn' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+
+
+
+-- rename threads tags to boards
+ALTER TABLE threads_tags RENAME TO threads_boards;
+