summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2022-10-22 16:04:33 +0200
committerYorhel <git@yorhel.nl>2022-10-22 16:04:35 +0200
commit138944ced5a32236afa479c418a33dc7ae8cf9b5 (patch)
tree9b7a820ee1fa9a4fad4bbcca717f95f66d012c44 /sql
parentbc9b1e24b65cf0151a71ade5aa498db528d4b9a3 (diff)
AdvSearch: Add option to exclude lie tags
https://vndb.org/t18886.24 The compact form encoding is a big ugly mess, I wouldn't be surprised if there's bugs.
Diffstat (limited to 'sql')
-rw-r--r--sql/func.sql10
-rw-r--r--sql/schema.sql3
2 files changed, 7 insertions, 6 deletions
diff --git a/sql/func.sql b/sql/func.sql
index 26f477bd..adc8ec07 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -296,20 +296,20 @@ BEGIN
DELETE FROM tags_vn_inherit WHERE vid = uvid;
END IF;
- INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler)
+ INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler, lie)
-- Add parent tags to each row in tags_vn_direct
- WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler) AS (
- SELECT 15, tag, vid, rating, spoiler
+ WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler, lie) AS (
+ SELECT 15, tag, vid, rating, spoiler, lie
FROM tags_vn_direct
WHERE (uvid IS NULL OR vid = uvid)
UNION ALL
- SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler
+ SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler, ta.lie
FROM t_all ta
JOIN tags_parents tp ON tp.id = ta.tag
WHERE ta.lvl > 0
)
-- Merge duplicates
- SELECT tag, vid, AVG(vote), MIN(spoiler)
+ SELECT tag, vid, AVG(vote), MIN(spoiler), bool_and(lie)
FROM t_all
WHERE tag IN(SELECT id FROM tags WHERE searchable)
GROUP BY tag, vid;
diff --git a/sql/schema.sql b/sql/schema.sql
index 3c16d031..de4311df 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -882,7 +882,8 @@ CREATE TABLE tags_vn_inherit (
tag vndbid NOT NULL,
vid vndbid NOT NULL,
rating real NOT NULL,
- spoiler smallint NOT NULL
+ spoiler smallint NOT NULL,
+ lie boolean NOT NULL
);
-- threads