summaryrefslogtreecommitdiff
path: root/util/dump.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-03-22 10:47:36 +0100
committerYorhel <git@yorhel.nl>2009-03-22 10:47:36 +0100
commit1b99dd8701ce572c8ac20b71b3eb9b471b69d788 (patch)
tree878b60dc7bba0c8292ae0a7ee219a6436039f6ff /util/dump.sql
parentbaf792ae90548b780f76d2b883bebc37c45e670c (diff)
Incorporated update_2.3.sql into dump.sql and updated the dbgrapher script
Diffstat (limited to 'util/dump.sql')
-rw-r--r--util/dump.sql179
1 files changed, 174 insertions, 5 deletions
diff --git a/util/dump.sql b/util/dump.sql
index 470a8aeb..26294511 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -154,6 +154,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,
@@ -175,8 +209,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,
@@ -198,7 +232,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
@@ -295,6 +330,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 --
@@ -318,10 +379,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;
@@ -439,6 +506,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();
@@ -457,12 +619,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;
@@ -470,6 +638,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