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/dbgraph.pl | 2 +- util/dump.sql | 10 ++++++++++ util/updates/update_2.3.sql | 7 +++++++ 3 files changed, 18 insertions(+), 1 deletion(-) create mode 100644 util/updates/update_2.3.sql (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index be76bca9..5fd52b75 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -21,7 +21,7 @@ my %subgraphs = ( 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |], 'Users' => [qw| CCFFFF users votes vnlists rlists wlists |], 'Discussion board' => [qw| FFDCDC threads threads_tags threads_posts |], - 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache |], + 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes |], ); my %tables; # table_name => [ [ col1, pri ], ... ] 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; diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql new file mode 100644 index 00000000..4faeba5a --- /dev/null +++ b/util/updates/update_2.3.sql @@ -0,0 +1,7 @@ + +CREATE TABLE quotes ( + vid integer NOT NULL REFERENCES vn (id), + quote varchar(250) NOT NULL, + PRIMARY KEY(vid, quote) +) WITHOUT OIDS; + -- 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 ++- util/updates/update_2.3.sql | 4 ++++ 2 files changed, 6 insertions(+), 1 deletion(-) (limited to 'util') 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 diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 4faeba5a..d43718c9 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -1,7 +1,11 @@ +-- 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 ''; -- cgit v1.2.3 From e79211c11fa51e6978a74f6fbf0bba68a33a4f4b Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Feb 2009 18:08:26 +0100 Subject: Made a start on the tagging system Mostly playing around with the possibilities, tag page layout & database scheme are far from final --- util/updates/update_2.3.sql | 89 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 89 insertions(+) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index d43718c9..9ae226df 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -9,3 +9,92 @@ CREATE TABLE quotes ( -- catalog numbers for releases ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT ''; + + + + +-- tagging system + +CREATE TABLE tags ( + id SERIAL NOT NULL PRIMARY KEY, + name varchar(250) NOT NULL UNIQUE, + aliases text NOT NULL DEFAULT '', + description text NOT NULL DEFAULT '', + meta boolean NOT NULL DEFAULT FALSE +) 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, -- -3..3 (0 isn't actually used...) + spoiler boolean NOT NULL DEFAULT FALSE, + PRIMARY KEY(tag, vid, uid) +) WITHOUT OIDS; + + +CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text); + +-- 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 + FROM tags t + WHERE 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 + FROM tags_parents tp + JOIN tags t ON t.id = tp.tag + WHERE tp.parent = tag + 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 + FROM tags_parents tp + JOIN tags t ON t.id = tp.parent + WHERE tp.tag = tag + 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; + + -- 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 ++- util/updates/update_2.3.sql | 5 +++++ 2 files changed, 7 insertions(+), 1 deletion(-) (limited to 'util') 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 '' ); diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index d43718c9..21db32ca 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -9,3 +9,8 @@ CREATE TABLE quotes ( -- 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 ''; + -- cgit v1.2.3 From 8e8bf25368ce0e8371e3aa45ce75c560819ed92b Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 22 Feb 2009 12:32:57 +0100 Subject: Very basic, unfinished and non-working interface for VN/tag linking But it already starts to look like something that might work. --- util/updates/update_2.3.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 9ae226df..ce8ce966 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -33,8 +33,8 @@ 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, -- -3..3 (0 isn't actually used...) - spoiler boolean NOT NULL DEFAULT FALSE, + 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; -- cgit v1.2.3 From e0390c46122dd2d4d14b7b7f2eeee27b679c133e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 22 Feb 2009 13:46:03 +0100 Subject: Renaming tags.aliases to tags.alias Consistent with all other aliases field --- util/updates/update_2.3.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index ce8ce966..249d1ef7 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -18,7 +18,7 @@ ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT ''; CREATE TABLE tags ( id SERIAL NOT NULL PRIMARY KEY, name varchar(250) NOT NULL UNIQUE, - aliases text NOT NULL DEFAULT '', + alias text NOT NULL DEFAULT '', description text NOT NULL DEFAULT '', meta boolean NOT NULL DEFAULT FALSE ) WITHOUT OIDS; -- cgit v1.2.3 From eaa8088edb36565d5dd232a624003f3aaef9ee9e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 1 Mar 2009 16:43:16 +0100 Subject: Wrote some SQL magic to fetch a list of VNs related to a tag ...taking into account the votes on child tags and one user voting on multiple child tags. Doing this realtime is slow... very slow. Tried playing around with storing the ratings in a normal table for caching, but don't have enough data to do proper benchmarks and determine the fastest method yet. --- util/updates/update_2.3.sql | 63 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 63 insertions(+) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 249d1ef7..da43c8e1 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -97,4 +97,67 @@ BEGIN 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 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; + +-- all votes for all tags +CREATE OR REPLACE 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 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), so we now finally have a list of VN entries for a tag (including inherited tags) +CREATE OR REPLACE VIEW tags_vn_inherited AS + SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating, AVG(spoiler)::real AS spoiler + FROM tags_vn_grouped + GROUP BY tag, vid; + +-- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN +-- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating +CREATE OR REPLACE VIEW tags_vn_bayesian AS + SELECT tag, vid, users, + ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating ) + / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating, + spoiler -- <- some kind of bayesian average for the spoiler status? or is AVG() good enough? + FROM tags_vn_inherited tvi; + + +-- creates/updates a table eqvuivalent to 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); + -- The following method may be faster on larger DBs, because tag_vn_childs() only has to be called once + --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; + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT tag_vn_calc(); + -- cgit v1.2.3 From 0680666a8659e75a90d581f8f3a0e3172c16bcd1 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 7 Mar 2009 00:01:34 +0100 Subject: Adding list of VNs to tag pages --- util/updates/update_2.3.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index da43c8e1..533a6581 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -105,7 +105,7 @@ DECLARE i RECORD; l RECORD; BEGIN - FOR l IN SElECT id FROM tags WHERE EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP + FOR l IN SElECT id FROM tags WHERE meta = FALSE 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 a9749950a04175cfed998ba9f98fb89a3de1544c Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 7 Mar 2009 15:11:58 +0100 Subject: Hiding spoilers on tag pages --- util/updates/update_2.3.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 533a6581..02a32106 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -141,7 +141,7 @@ CREATE OR REPLACE VIEW tags_vn_bayesian AS SELECT tag, vid, users, ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating ) / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating, - spoiler -- <- some kind of bayesian average for the spoiler status? or is AVG() good enough? + (CASE WHEN spoiler < 0.7 THEN 0 WHEN spoiler > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler FROM tags_vn_inherited tvi; -- cgit v1.2.3 From 64eadc1c58c931b7309cf3c405f9a600f67fb8aa Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 8 Mar 2009 11:36:31 +0100 Subject: Tag stats on user pages and list --- util/updates/update_2.3.sql | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 02a32106..4c60791b 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -161,3 +161,35 @@ $$ 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(); + -- cgit v1.2.3 From bfd587563ae9fede8299bf97dfea1e07c2d4fefe Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 8 Mar 2009 12:20:02 +0100 Subject: Added caching of tags_vn_bayesian and the VN count for tags Updated hourly by Multi. May want to look for a better way to update this cache, because I'm afraid the current tags_vn_calc() is going to perform very badly on larger databases. --- util/updates/update_2.3.sql | 36 ++++++++++++++++++++---------------- 1 file changed, 20 insertions(+), 16 deletions(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 4c60791b..b0662302 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -20,7 +20,8 @@ CREATE TABLE tags ( name varchar(250) NOT NULL UNIQUE, alias text NOT NULL DEFAULT '', description text NOT NULL DEFAULT '', - meta boolean NOT NULL DEFAULT FALSE + meta boolean NOT NULL DEFAULT FALSE, + c_vns integer NOT NULL DEFAULT 0 ) WITHOUT OIDS; CREATE TABLE tags_parents ( @@ -39,7 +40,7 @@ CREATE TABLE tags_vn ( ) WITHOUT OIDS; -CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text); +CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer); -- tag: tag to start with, -- lvl: recursion level @@ -51,7 +52,7 @@ DECLARE BEGIN IF dir AND tag = 0 THEN FOR r IN - SELECT lvl, t.id, t.name + SELECT lvl, t.id, t.name, t.c_vns FROM tags t WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id) ORDER BY t.name @@ -65,7 +66,7 @@ BEGIN END LOOP; ELSIF dir THEN FOR r IN - SELECT lvl, tp.tag, t.name + 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 @@ -80,7 +81,7 @@ BEGIN END LOOP; ELSE FOR r IN - SELECT lvl, tp.parent, t.name + 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 @@ -131,18 +132,19 @@ CREATE OR REPLACE VIEW tags_vn_grouped AS -- grouped by (tag, vid), so we now finally have a list of VN entries for a tag (including inherited tags) CREATE OR REPLACE VIEW tags_vn_inherited AS - SELECT tag, vid, COUNT(uid)::real AS users, AVG(vote)::real AS rating, AVG(spoiler)::real AS spoiler + 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; -- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN -- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating -CREATE OR REPLACE VIEW tags_vn_bayesian AS - SELECT tag, vid, users, - ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating ) - / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating, - (CASE WHEN spoiler < 0.7 THEN 0 WHEN spoiler > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler - FROM tags_vn_inherited tvi; +--CREATE OR REPLACE VIEW tags_vn_bayesian AS +-- SELECT tag, vid, users, +-- ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating ) +-- / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating, +-- spoiler +-- FROM tags_vn_inherited tvi; -- creates/updates a table eqvuivalent to tags_vn_bayesian @@ -151,10 +153,12 @@ 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); - -- The following method may be faster on larger DBs, because tag_vn_childs() only has to be called once - --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_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; -- cgit v1.2.3 From f7dc3ee5d6673250304de925088f29da11708b8b Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Mar 2009 13:33:45 +0100 Subject: Added tag states for pending/deleted/accepted --- util/updates/update_2.3.sql | 20 +++++++------------- 1 file changed, 7 insertions(+), 13 deletions(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index b0662302..46e2f241 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -21,6 +21,8 @@ CREATE TABLE tags ( alias text NOT NULL DEFAULT '', 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; @@ -54,7 +56,7 @@ BEGIN FOR r IN SELECT lvl, t.id, t.name, t.c_vns FROM tags t - WHERE NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id) + 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; @@ -70,6 +72,7 @@ BEGIN 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; @@ -85,6 +88,7 @@ BEGIN 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; @@ -106,7 +110,7 @@ DECLARE i RECORD; l RECORD; BEGIN - FOR l IN SElECT id FROM tags WHERE meta = FALSE AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP + 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; @@ -137,17 +141,7 @@ CREATE OR REPLACE VIEW tags_vn_inherited AS FROM tags_vn_grouped GROUP BY tag, vid; --- bayesian average on the above view, to provide better rankings as to how much a tag applies to a VN --- details of the calculation @ http://www.thebroth.com/blog/118/bayesian-rating ---CREATE OR REPLACE VIEW tags_vn_bayesian AS --- SELECT tag, vid, users, --- ( (SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users*rating ) --- / ( (SELECT AVG(users)::real FROM tags_vn_inherited WHERE tag = tvi.tag) + users )::real AS rating, --- spoiler --- FROM tags_vn_inherited tvi; - - --- creates/updates a table eqvuivalent to tags_vn_bayesian +-- creates/updates a table with bayesian rankings of tags CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ BEGIN DROP TABLE IF EXISTS tags_vn_stored; -- cgit v1.2.3 From c47a479253d16dba9653c9ce86f86849d0d5f03e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Mar 2009 16:16:58 +0100 Subject: Separated table for tag aliases + proper checking of unique aliases and names --- util/updates/update_2.3.sql | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 46e2f241..79625714 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -18,7 +18,6 @@ ALTER TABLE releases_rev ADD COLUMN catalog varchar(50) NOT NULL DEFAULT ''; CREATE TABLE tags ( id SERIAL NOT NULL PRIMARY KEY, name varchar(250) NOT NULL UNIQUE, - alias text NOT NULL DEFAULT '', description text NOT NULL DEFAULT '', meta boolean NOT NULL DEFAULT FALSE, added bigint NOT NULL DEFAULT DATE_PART('epoch'::text, NOW()), @@ -26,6 +25,11 @@ CREATE TABLE tags ( 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, -- cgit v1.2.3 From 79687d7aa222e1ea8d6401d2aaa81c95e58c5b85 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 21 Mar 2009 14:51:18 +0100 Subject: Replacing all occurences of 'discussion tags' with 'discussion boards' Which is a more accurate description, and doesn't confuse with the tagging system. Note than even all internal uses of the word 'tag' have been replaced, as I'm not a huge fan of different terminology in the code and UI. This update might break some things related to the discussion board. --- util/updates/update_2.3.sql | 5 +++++ 1 file changed, 5 insertions(+) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 79625714..3853bac1 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -195,3 +195,8 @@ $$ 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; + -- 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/dbgraph.pl | 3 +- util/dump.sql | 179 ++++++++++++++++++++++++++++++++++++++++++++++++++++++-- 2 files changed, 176 insertions(+), 6 deletions(-) (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 5fd52b75..b9800a0a 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -20,7 +20,8 @@ my %subgraphs = ( 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_vn |], 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_categories vn_anime vn_screenshots |], 'Users' => [qw| CCFFFF users votes vnlists rlists wlists |], - 'Discussion board' => [qw| FFDCDC threads threads_tags threads_posts |], + 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], + 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], 'Misc' => [qw| F5F5F5 changes anime screenshots relgraph stats_cache quotes |], ); 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 +- util/updates/update_2.3.sql | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'util') 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; diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 32b04b31..25c6da21 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -117,7 +117,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 +++++++++++++++++++++------------------------ util/updates/update_2.3.sql | 62 +++++++++++++++++++-------------------- 2 files changed, 64 insertions(+), 68 deletions(-) (limited to 'util') 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 -- ----------------------- diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index 25c6da21..db7a2ca7 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -48,6 +48,14 @@ CREATE TABLE tags_vn ( 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); @@ -127,39 +135,31 @@ BEGIN END; $$ LANGUAGE plpgsql; --- all votes for all tags -CREATE OR REPLACE 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 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), so we now finally have a list of VN entries for a tag (including inherited tags) -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; - --- creates/updates a table with bayesian rankings of tags +-- updates tags_vn_bayesian with rankings of tags 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; -- cgit v1.2.3 From 09ace0e1e97f9e92f8d4f29a7a9b2556263ef02b Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 30 Mar 2009 18:52:47 +0200 Subject: Tiny bugfix in the tag<->vn calculation Use a user's highest vote on a child tag in the calculation of the rating of a parent, rather than the average. --- util/updates/update_2.3.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'util') diff --git a/util/updates/update_2.3.sql b/util/updates/update_2.3.sql index db7a2ca7..1a8c9571 100644 --- a/util/updates/update_2.3.sql +++ b/util/updates/update_2.3.sql @@ -143,7 +143,7 @@ BEGIN 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 + 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; -- cgit v1.2.3