diff options
author | Yorhel <git@yorhel.nl> | 2009-04-01 08:49:59 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-04-01 08:49:59 +0200 |
commit | 33c6bd207473e8ce6ddfc0d19b34ccb139aa8521 (patch) | |
tree | d5714b79b3e02c77d78f43a1f4b34f7194068b14 /util/updates/update_2.3.sql | |
parent | 61c4d04980dc0376cf0032364a5952f8d828837f (diff) | |
parent | f35175b04fc9f54680ad2578a994177e4d280b09 (diff) |
Merge branch 'beta'2.3
Conflicts:
data/docs/4
Diffstat (limited to 'util/updates/update_2.3.sql')
-rw-r--r-- | util/updates/update_2.3.sql | 205 |
1 files changed, 205 insertions, 0 deletions
diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql new file mode 100644 index 00000000..1a8c9571 --- /dev/null +++ b/util/updates/update_2.3.sql @@ -0,0 +1,205 @@ + +-- some random VN quotes +CREATE TABLE quotes ( + vid integer NOT NULL REFERENCES vn (id), + quote varchar(250) NOT NULL, + PRIMARY KEY(vid, quote) +) WITHOUT OIDS; + + +-- catalog numbers for releases +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 TABLE tags_vn_bayesian ( + tag integer NOT NULL, + vid integer NOT NULL, + users integer NOT NULL, + rating real NOT NULL, + spoiler smallint NOT NULL +) 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 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; + +-- updates tags_vn_bayesian with rankings of tags +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, MAX(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(); + + + +-- 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; + |