diff options
| -rw-r--r-- | lib/Multi/Maintenance.pm | 2 | ||||
| -rw-r--r-- | lib/VNDB/DB/Tags.pm | 3 | ||||
| -rw-r--r-- | util/sql/func.sql | 28 | ||||
| -rw-r--r-- | util/updates/update_20191007.sql | 4 |
4 files changed, 26 insertions, 11 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 1ef68dbf..0308d171 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -66,7 +66,7 @@ my %dailies = ( ) AS r(id)|, # takes about 15 seconds max, still OK - tagcache => 'SELECT tag_vn_calc()', + tagcache => 'SELECT tag_vn_calc(NULL)', # takes about 25 seconds, OK traitcache => 'SELECT traits_chars_calc()', diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 97852724..75be42d8 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -237,6 +237,9 @@ sub dbTagLinkEdit { # update $self->dbExec('UPDATE tags_vn SET vote = ?, spoiler = ?, date = NOW() WHERE tag = ? AND vid = ? AND uid = ?', $update->{$_}[0], $update->{$_}[1]<0?undef:$update->{$_}[1], $_, $vid, $uid) for (keys %$update); + + # Update cache + $self->dbExec('SELECT tag_vn_calc(?)', $vid); } diff --git a/util/sql/func.sql b/util/sql/func.sql index 57e0b43f..4375dfb3 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -123,12 +123,19 @@ $$ LANGUAGE plpgsql; --- recalculate tags_vn_inherit -CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +-- Recalculate tags_vn_inherit. +-- When a vid is given, only the tags for that vid will be updated. These +-- incremental updates do not affect tags.c_items, so that may still get +-- out-of-sync. +CREATE OR REPLACE FUNCTION tag_vn_calc(uvid integer) RETURNS void AS $$ BEGIN - DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; - TRUNCATE tags_vn_inherit; - -- populate tags_vn_inherit + IF uvid IS NULL THEN + DROP INDEX IF EXISTS tags_vn_inherit_tag_vid; + TRUNCATE tags_vn_inherit; + ELSE + DELETE FROM tags_vn_inherit WHERE vid = uvid; + END IF; + INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler) -- Group votes to generate a list of directly-upvoted (vid, tag) pairs. -- This is essentually the same as the tag listing on VN pages. @@ -138,6 +145,7 @@ BEGIN JOIN tags t ON t.id = tv.tag WHERE NOT tv.ignore AND t.state = 2 AND vid NOT IN(SELECT id FROM vn WHERE hidden) + AND (uvid IS NULL OR vid = uvid) GROUP BY tv.tag, tv.vid HAVING AVG(tv.vote) > 0 -- Add parent tags @@ -155,10 +163,12 @@ BEGIN FROM t_all WHERE tag IN(SELECT id FROM tags WHERE searchable) GROUP BY tag, vid; - -- 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_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + + IF uvid IS NULL THEN + CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid); + UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id); + END IF; + RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER; diff --git a/util/updates/update_20191007.sql b/util/updates/update_20191007.sql index 9d556478..291e94f6 100644 --- a/util/updates/update_20191007.sql +++ b/util/updates/update_20191007.sql @@ -2,6 +2,8 @@ ALTER TABLE tags_vn_inherit DROP COLUMN users; ALTER TABLE traits_chars DROP CONSTRAINT traits_chars_pkey; +DROP FUNCTION tag_vn_calc(); + \i util/sql/func.sql -SELECT tag_vn_calc(); +SELECT tag_vn_calc(NULL); SELECT traits_chars_calc(); |