summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-04-01 08:49:59 +0200
committerYorhel <git@yorhel.nl>2009-04-01 08:49:59 +0200
commit33c6bd207473e8ce6ddfc0d19b34ccb139aa8521 (patch)
treed5714b79b3e02c77d78f43a1f4b34f7194068b14 /util/dump.sql
parent61c4d04980dc0376cf0032364a5952f8d828837f (diff)
parentf35175b04fc9f54680ad2578a994177e4d280b09 (diff)
Merge branch 'beta'2.3
Conflicts: data/docs/4
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql193
1 files changed, 185 insertions, 8 deletions
diff --git a/util/dump.sql b/util/dump.sql
index f23d6d62..5003c6e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -58,9 +58,19 @@ CREATE TABLE producers_rev (
original varchar(200) NOT NULL DEFAULT '',
website varchar(250) NOT NULL DEFAULT '',
lang varchar NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT ''
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT ''
);
+
+-- quotes
+CREATE TABLE quotes (
+ vid integer NOT NULL,
+ quote varchar(250) NOT NULL,
+ PRIMARY KEY(vid, quote)
+);
+
+
-- releases
CREATE TABLE releases (
id SERIAL NOT NULL PRIMARY KEY,
@@ -104,7 +114,8 @@ CREATE TABLE releases_rev (
notes text NOT NULL DEFAULT '',
minage smallint NOT NULL DEFAULT -1,
gtin bigint NOT NULL DEFAULT 0,
- patch boolean NOT NULL DEFAULT FALSE
+ patch boolean NOT NULL DEFAULT FALSE,
+ catalog varchar(50) NOT NULL DEFAULT ''
);
-- releases_vn
@@ -144,6 +155,49 @@ 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)
+);
+
+-- tags_vn_bayesian
+CREATE TABLE tags_vn_bayesian (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
-- threads
CREATE TABLE threads (
id SERIAL NOT NULL PRIMARY KEY,
@@ -165,8 +219,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,
@@ -188,7 +242,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
@@ -285,7 +340,6 @@ CREATE TABLE wlists (
-
-----------------------------------------------
-- F O R E I G N K E Y C H E C K I N G --
-----------------------------------------------
@@ -296,6 +350,7 @@ ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes
ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
@@ -307,10 +362,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;
@@ -428,6 +489,115 @@ 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 state = 2 AND 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_bayesian
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY 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 TEMPORARY 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) and serialized into a table
+ DROP INDEX IF EXISTS tags_vn_bayesian_tag;
+ TRUNCATE tags_vn_bayesian;
+ INSERT INTO tags_vn_bayesian
+ SELECT tag, vid, COUNT(uid) 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;
+ CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+ -- now perform the bayesian ranking calculation
+ UPDATE tags_vn_bayesian tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+ -- and update the VN count in the tags table as well
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
@@ -446,12 +616,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;
@@ -459,6 +635,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