diff options
-rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
-rw-r--r-- | util/sql/func.sql | 28 | ||||
-rw-r--r-- | util/updates/update_20191007.sql | 3 |
3 files changed, 23 insertions, 10 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 0308d171..e19c1890 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -69,7 +69,7 @@ my %dailies = ( tagcache => 'SELECT tag_vn_calc(NULL)', # takes about 25 seconds, OK - traitcache => 'SELECT traits_chars_calc()', + traitcache => 'SELECT traits_chars_calc(NULL)', # takes about 140 seconds, not really OK vnpopularity => 'SELECT update_vnpopularity()', 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); |