summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rw-r--r--util/sql/func.sql36
-rw-r--r--util/updates/update_2.19.sql14
2 files changed, 48 insertions, 2 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 0938f76d..d7081aef 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -127,13 +127,47 @@ BEGIN
-- recreate index
CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
-- and update the VN count in the tags table
- UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
+ UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
RETURN;
END;
$$ LANGUAGE plpgsql;
+-- recalculate traits_chars
+CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$
+BEGIN
+ TRUNCATE traits_chars;
+ INSERT INTO traits_chars (tid, cid, spoil)
+ -- all char<->trait links of the latest revisions, including chars inherited from child traits
+ -- (also includes meta traits, because they could have a normal trait as parent)
+ WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS (
+ SELECT 15, tid, c.id, spoil, false
+ FROM chars_traits ct
+ JOIN chars c ON c.latest = ct.cid
+ WHERE NOT c.hidden
+ UNION ALL
+ SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta
+ FROM traits_chars_all tc
+ JOIN traits_parents tp ON tp.trait = tc.tid
+ JOIN traits t ON t.id = tp.parent
+ WHERE t.state = 2
+ AND tc.lvl > 0
+ )
+ -- now grouped by (tid, cid) and with meta traits filtered out
+ SELECT tid, cid, (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
+ FROM traits_chars_all
+ WHERE NOT meta
+ GROUP BY tid, cid;
+ -- and update the VN count in the tags table
+ UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
----------------------------------------------------------
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 14c3e30e..1332d666 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -12,7 +12,8 @@ CREATE TABLE traits (
added timestamptz NOT NULL DEFAULT NOW(),
state smallint NOT NULL DEFAULT 0,
addedby integer NOT NULL DEFAULT 0 REFERENCES users (id),
- "group" integer
+ "group" integer,
+ c_items integer NOT NULL DEFAULT 0
);
ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
@@ -24,6 +25,7 @@ CREATE TABLE traits_parents (
CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+ALTER TABLE tags RENAME c_vns TO c_items;
-- character database -> chars
@@ -75,6 +77,14 @@ CREATE TABLE chars_vns (
PRIMARY KEY(cid, vid, rid)
);
+-- cache table
+CREATE TABLE traits_chars (
+ cid integer NOT NULL REFERENCES chars (id),
+ tid integer NOT NULL REFERENCES traits (id),
+ spoil smallint NOT NULL DEFAULT 0,
+ PRIMARY KEY(cid, tid)
+);
+
CREATE SEQUENCE charimg_seq;
@@ -144,5 +154,7 @@ UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more
INSERT INTO edit_char_traits VALUES (4, 0), (12, 0);
SELECT edit_char_commit();
+SELECT traits_chars_calc();
+
-- */