summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-29 11:51:21 +0100
committerYorhel <git@yorhel.nl>2009-11-29 11:51:21 +0100
commitbe023476e67dc76a5fb1344af5b5cb9244d992e7 (patch)
tree88c8cbfad23485780f809bd8ca2cf513224074fa /util
parentbbfc0bb866b2ad327eb25a97d3624b9eb3a7631b (diff)
SQL: Rewrote tag_vn_calc() to use a WITH RECURSIVE .. SELECT query
This is more efficient, and doesn't require the tag_tree() or tag_vn_childs() stored procedures. Does require PostgreSQL 8.4+
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql59
-rw-r--r--util/updates/update_2.10.sql45
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;
+