summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-10-07 11:22:01 +0200
committerYorhel <git@yorhel.nl>2019-10-07 12:09:56 +0200
commit4844a42cb554253340391fdbdc5a7634ee287b1e (patch)
treeba5d2c1245aae74e894a3fcb052b92dff20acbd3 /util/sql
parent65ed955890c7ee7250a2bce4467c8c092c1bbbbe (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.sql28
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