From 4844a42cb554253340391fdbdc5a7634ee287b1e Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Mon, 7 Oct 2019 11:22:01 +0200
Subject: 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).
---
 util/sql/func.sql                | 28 ++++++++++++++++++++--------
 util/updates/update_20191007.sql |  3 ++-
 2 files changed, 22 insertions(+), 9 deletions(-)

(limited to 'util')

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
diff --git a/util/updates/update_20191007.sql b/util/updates/update_20191007.sql
index 291e94f6..d2047e9c 100644
--- a/util/updates/update_20191007.sql
+++ b/util/updates/update_20191007.sql
@@ -3,7 +3,8 @@ ALTER TABLE tags_vn_inherit DROP COLUMN users;
 ALTER TABLE traits_chars DROP CONSTRAINT traits_chars_pkey;
 
 DROP FUNCTION tag_vn_calc();
+DROP FUNCTION traits_chars_calc();
 
 \i util/sql/func.sql
 SELECT tag_vn_calc(NULL);
-SELECT traits_chars_calc();
+SELECT traits_chars_calc(NULL);
-- 
cgit v1.2.3