summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2018-09-29 12:51:54 +0200
committerYorhel <git@yorhel.nl>2018-09-29 12:52:47 +0200
commit4e3081f530526601d82fbe792ea4c0f9bc9a9c9a (patch)
tree44eeb9323c7e1af549c5dfa6f540e9c37ef90a0a /util/sql
parent706f447961d18c36ffdc6be34a6eae40acd01574 (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.sql24
-rw-r--r--util/sql/schema.sql3
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