diff options
author | Yorhel <git@yorhel.nl> | 2009-03-14 13:33:45 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-03-14 13:33:45 +0100 |
commit | f7dc3ee5d6673250304de925088f29da11708b8b (patch) | |
tree | 315479c0ef47999785185d2e863a462ba3ed539d /util/updates | |
parent | 351a48f316e5c25a2b6da03c5890c29db11f89ad (diff) |
Added tag states for pending/deleted/accepted
Diffstat (limited to 'util/updates')
-rw-r--r-- | util/updates/update_2.3.sql | 20 |
1 files changed, 7 insertions, 13 deletions
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; |