diff options
author | Yorhel <git@yorhel.nl> | 2019-10-07 11:22:01 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-07 12:09:56 +0200 |
commit | 4844a42cb554253340391fdbdc5a7634ee287b1e (patch) | |
tree | ba5d2c1245aae74e894a3fcb052b92dff20acbd3 /util/sql | |
parent | 65ed955890c7ee7250a2bce4467c8c092c1bbbbe (diff) |
SQL: Perform incremental updates on traits_chars when characters are edited
Same thing as 65ed955890c7ee7250a2bce4467c8c092c1bbbbe, with the same
limitations (except this does take hiding/unhiding characters into
account).
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 28 |
1 files changed, 20 insertions, 8 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql index 4375dfb3..98614b69 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -174,11 +174,16 @@ END; $$ LANGUAGE plpgsql SECURITY DEFINER; --- recalculate traits_chars -CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$ +-- Recalculate traits_chars. Pretty much same thing as tag_vn_calc(). +CREATE OR REPLACE FUNCTION traits_chars_calc(ucid integer) RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS traits_chars_tid; - TRUNCATE traits_chars; + IF ucid IS NULL THEN + DROP INDEX IF EXISTS traits_chars_tid; + TRUNCATE traits_chars; + ELSE + DELETE FROM traits_chars WHERE cid = ucid; + END IF; + INSERT INTO traits_chars (tid, cid, spoil) -- all char<->trait links of the latest revisions, including chars inherited from child traits. -- (also includes non-searchable traits, because they could have a searchable trait as parent) @@ -186,6 +191,7 @@ BEGIN SELECT 15, tid, ct.id, spoil FROM chars_traits ct WHERE id NOT IN(SELECT id from chars WHERE hidden) + AND (ucid IS NULL OR ct.id = ucid) UNION ALL SELECT lvl-1, tp.parent, tc.cid, tc.spoiler FROM traits_chars_all tc @@ -200,10 +206,11 @@ BEGIN FROM traits_chars_all WHERE tid IN(SELECT id FROM traits WHERE searchable) GROUP BY tid, cid; - -- recreate index - CREATE INDEX traits_chars_tid ON traits_chars (tid); - -- and update the VN count in the tags table - UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id); + + IF ucid IS NULL THEN + CREATE INDEX traits_chars_tid ON traits_chars (tid); + UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id); + END IF; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; @@ -382,6 +389,11 @@ BEGIN ) AS v(vid); END IF; + -- Call traits_chars_calc() for characters to update the traits cache + IF xtype = 'c' THEN + PERFORM traits_chars_calc(xedit.itemid); + END IF; + -- Call notify_dbdel() if an entry has been deleted -- Call notify_listdel() if a vn/release entry has been deleted IF xoldchid IS NOT NULL |