diff options
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/func.sql | 36 | ||||
-rw-r--r-- | util/updates/update_2.19.sql | 14 |
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(); + -- */ |