summaryrefslogtreecommitdiff
path: root/util/updates/update_2.3.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.3.sql')
-rw-r--r--util/updates/update_2.3.sql205
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;
+