diff options
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 59 | ||||
-rw-r--r-- | util/updates/update_2.10.sql | 45 |
2 files changed, 59 insertions, 45 deletions
diff --git a/util/dump.sql b/util/dump.sql index a1a04aef..cad84ac2 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -551,46 +551,41 @@ 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_inherit 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_inherit_tag; TRUNCATE tags_vn_inherit; + -- populate tags_vn_inherit INSERT INTO tags_vn_inherit - 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 + -- all votes for all tags, including votes inherited by child tags + -- (also includes meta tags, because they could have a normal tag as parent) + WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS ( + SELECT 15, tag, vid, uid, vote, spoiler, false + FROM tags_vn + UNION ALL + SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta + FROM tags_vn_all ta + JOIN tags_parents tp ON tp.tag = ta.tag + JOIN tags t ON t.id = tp.parent + WHERE t.state = 2 + AND ta.lvl > 0 + ) + -- grouped by (tag, vid) + 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 ( + -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags) + SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real + FROM tags_vn_all + WHERE NOT meta + GROUP BY tag, vid, uid + ) AS t(tag, vid, uid, vote, spoiler) GROUP BY tag, vid - HAVING AVG(vote) > 0; + HAVING AVG(vote) > 0; + -- recreate index CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag); - -- and update the VN count in the tags table as well + -- and update the VN count in the tags table UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; END; diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql index dada4889..74b1a8a5 100644 --- a/util/updates/update_2.10.sql +++ b/util/updates/update_2.10.sql @@ -9,26 +9,42 @@ CREATE TABLE tags_vn_inherit ( spoiler smallint NOT NULL ); + +-- more efficient version of tag_vn_calc() 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_inherit_tag; TRUNCATE tags_vn_inherit; + -- populate tags_vn_inherit INSERT INTO tags_vn_inherit - 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 + -- all votes for all tags, including votes inherited by child tags + -- (also includes meta tags, because they could have a normal tag as parent) + WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS ( + SELECT 15, tag, vid, uid, vote, spoiler, false + FROM tags_vn + UNION ALL + SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta + FROM tags_vn_all ta + JOIN tags_parents tp ON tp.tag = ta.tag + JOIN tags t ON t.id = tp.parent + WHERE t.state = 2 + AND ta.lvl > 0 + ) + -- grouped by (tag, vid) + 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 ( + -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags) + SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real + FROM tags_vn_all + WHERE NOT meta + GROUP BY tag, vid, uid + ) AS t(tag, vid, uid, vote, spoiler) GROUP BY tag, vid - HAVING AVG(vote) > 0; + HAVING AVG(vote) > 0; + -- recreate index CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag); - -- and update the VN count in the tags table as well + -- and update the VN count in the tags table UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); RETURN; END; @@ -36,3 +52,6 @@ $$ LANGUAGE plpgsql; SELECT tag_vn_calc(); +-- tag_vn_childs() is not used anymore +DROP FUNCTION tag_vn_childs() CASCADE; + |