diff options
Diffstat (limited to 'util/dump.sql')
-rw-r--r-- | util/dump.sql | 193 |
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 |