From e1835977fea7be42b702275ac88e69921d82777d Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 25 Jan 2009 12:30:50 +0100 Subject: Random quote feature --- util/dump.sql | 10 ++++++++++ 1 file changed, 10 insertions(+) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index f23d6d62..3d937832 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -61,6 +61,15 @@ CREATE TABLE producers_rev ( "desc" text 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, @@ -296,6 +305,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; -- cgit v1.2.3 From 6089007c7b57c3ff158d6169ec22ad6d9325a7ec Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 12 Feb 2009 22:38:10 +0100 Subject: Catalog number field to release entries --- util/dump.sql | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 3d937832..470a8aeb 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -113,7 +113,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 -- cgit v1.2.3 From d03465b573c10b10819201bba8257e59362589f1 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 21 Feb 2009 19:00:12 +0100 Subject: Aliases field to producer entries --- util/dump.sql | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 470a8aeb..e3652a0f 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -58,7 +58,8 @@ 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 '' ); -- cgit v1.2.3 From 1b99dd8701ce572c8ac20b71b3eb9b471b69d788 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 22 Mar 2009 10:47:36 +0100 Subject: Incorporated update_2.3.sql into dump.sql and updated the dbgrapher script --- util/dump.sql | 179 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 1 file changed, 174 insertions(+), 5 deletions(-) (limited to 'util/dump.sql') 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 -- cgit v1.2.3 From 9765be8dc46d9f458fea8c548cde02e3dba7b920 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 22 Mar 2009 11:13:04 +0100 Subject: Fixed tiny SQL bug --- util/dump.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index 993baaa3..a9681c10 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -575,7 +575,7 @@ DECLARE 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 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; -- cgit v1.2.3 From 864bce475636282e31643cbbb8cab2fc9ac47ce0 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 28 Mar 2009 15:10:33 +0100 Subject: Fixed and re-organized tag<->vn calculation Fixed major performance bug caused by referencing the wrong table, moved all intermediate views to tag_vn_calc() as temporary views (similar to update_vnpopularity()) and renamed tags_vn_stored to tags_vn_bayesian. --- util/dump.sql | 70 ++++++++++++++++++++++++++++------------------------------- 1 file changed, 33 insertions(+), 37 deletions(-) (limited to 'util/dump.sql') diff --git a/util/dump.sql b/util/dump.sql index a9681c10..5003c6e8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -189,6 +189,15 @@ CREATE TABLE tags_vn ( 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, @@ -331,33 +340,6 @@ 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 -- ----------------------------------------------- @@ -586,18 +568,31 @@ END; $$ LANGUAGE plpgsql; --- recalculate tags_vn_stored +-- recalculate tags_vn_bayesian 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); + -- 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; @@ -606,6 +601,7 @@ SELECT tag_vn_calc(); + ----------------------- -- T R I G G E R S -- ----------------------- -- cgit v1.2.3