diff options
author | Yorhel <git@yorhel.nl> | 2018-09-29 12:51:54 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2018-09-29 12:52:47 +0200 |
commit | 4e3081f530526601d82fbe792ea4c0f9bc9a9c9a (patch) | |
tree | 44eeb9323c7e1af549c5dfa6f540e9c37ef90a0a /util/sql | |
parent | 706f447961d18c36ffdc6be34a6eae40acd01574 (diff) |
Add default spoiler level to tags and use it in spoiler calculation
https://vndb.org/t11296.3
(And I forgot to add update_20180929.sql in the previous commit)
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 24 | ||||
-rw-r--r-- | util/sql/schema.sql | 3 |
2 files changed, 16 insertions, 11 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index e36d46e8..4d52d1bf 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -133,12 +133,13 @@ BEGIN -- All votes for all tags, including votes inherited by child tags if the -- parent tag itself does not have any votes. -- (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 - WHERE NOT ignore + WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, defaultspoil, meta) AS ( + SELECT 15, tv.tag, tv.vid, tv.uid, tv.vote, tv.spoiler, t.defaultspoil, false + FROM tags_vn tv + JOIN tags t ON t.id = tv.tag + WHERE NOT tv.ignore UNION ALL - SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta + SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.defaultspoil, t.meta FROM tags_vn_all ta JOIN tags_parents tp ON tp.tag = ta.tag JOIN tags t ON t.id = tp.parent @@ -148,15 +149,18 @@ BEGIN ) -- grouped by (tag, vid) SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating, - (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler + (CASE WHEN COUNT(spoiler) = 0 THEN defaultspoil + WHEN AVG(spoiler) > 1.3 THEN 2 + WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 + 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, AVG(spoiler)::real + SELECT tag, vid, uid, MAX(vote)::real, AVG(spoiler)::real, defaultspoil FROM tags_vn_all WHERE NOT meta - GROUP BY tag, vid, uid - ) AS t(tag, vid, uid, vote, spoiler) - GROUP BY tag, vid + GROUP BY tag, vid, uid, defaultspoil + ) AS t(tag, vid, uid, vote, spoiler, defaultspoil) + GROUP BY tag, vid, defaultspoil HAVING AVG(vote) > 0; -- recreate index CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); diff --git a/util/sql/schema.sql b/util/sql/schema.sql index b3669dd5..6c2d785b 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -461,7 +461,8 @@ CREATE TABLE tags ( state smallint NOT NULL DEFAULT 0, c_items integer NOT NULL DEFAULT 0, addedby integer NOT NULL DEFAULT 0, - cat tag_category NOT NULL DEFAULT 'cont' + cat tag_category NOT NULL DEFAULT 'cont', + defaultspoil smallint NOT NULL DEFAULT 0 ); -- tags_aliases |