summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/VNWeb/VN/Page.pm23
-rw-r--r--sql/func.sql55
-rw-r--r--sql/perms.sql1
-rw-r--r--sql/schema.sql9
-rw-r--r--sql/tableattrs.sql2
-rw-r--r--util/updates/2022-08-03-tags_vn_direct.sql10
6 files changed, 66 insertions, 34 deletions
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 884f4864..77e4883b 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -35,20 +35,17 @@ sub enrich_vn {
);
enrich_extlinks r => $v->{releases};
- $v->{reviews} = tuwf->dbRowi('SELECT COUNT(*) FILTER(WHERE isfull) AS full, COUNT(*) FILTER(WHERE NOT isfull) AS mini, COUNT(*) AS total FROM reviews WHERE NOT c_flagged AND vid =', \$v->{id});
-
- $v->{tags} = tuwf->dbAlli("
- SELECT t.id, t.name, t.cat, avg(tv.vote) as rating
- , coalesce(avg(tv.spoiler), t.defaultspoil) as spoiler
- , count(lie) filter(where lie) > 0 AND count(lie) filter (where lie) >= count(lie)>>1 AS lie
+ $v->{reviews} = tuwf->dbRowi('
+ SELECT COUNT(*) FILTER(WHERE isfull) AS full, COUNT(*) FILTER(WHERE NOT isfull) AS mini, COUNT(*) AS total
+ FROM reviews
+ WHERE NOT c_flagged AND vid =', \$v->{id}
+ );
+ $v->{tags} = tuwf->dbAlli('
+ SELECT t.id, t.name, t.cat, tv.rating, tv.spoiler, tv.lie
FROM tags t
- JOIN tags_vn tv ON tv.tag = t.id
- LEFT JOIN users u ON u.id = tv.uid
- WHERE NOT t.hidden AND tv.vid =", \$v->{id}, "
- AND NOT tv.ignore AND (u.id IS NULL OR u.perm_tag)
- GROUP BY t.id, t.name, t.cat
- HAVING avg(tv.vote) > 0
- ORDER BY rating DESC, t.name"
+ JOIN tags_vn_direct tv ON t.id = tv.tag
+ WHERE tv.vid =', \$v->{id}, '
+ ORDER BY rating DESC, t.name'
);
}
diff --git a/sql/func.sql b/sql/func.sql
index c9559cff..842d477b 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -235,13 +235,37 @@ $$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a n
--- Recalculate tags_vn_inherit.
+-- Recalculate tags_vn_direct & 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 vndbid) RETURNS void AS $$
BEGIN
IF uvid IS NULL THEN
+ DROP INDEX IF EXISTS tags_vn_direct_tag_vid;
+ DROP INDEX IF EXISTS tags_vn_direct_vid;
+ TRUNCATE tags_vn_direct;
+ ELSE
+ DELETE FROM tags_vn_direct WHERE vid = uvid;
+ END IF;
+
+ INSERT INTO tags_vn_direct (tag, vid, rating, spoiler, lie)
+ SELECT tv.tag, tv.vid, avg(tv.vote)
+ , CASE WHEN COUNT(spoiler) = 0 THEN MIN(t.defaultspoil) WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.4 THEN 1 ELSE 0 END
+ , count(lie) filter(where lie) > 0 AND count(lie) filter (where lie) >= count(lie)>>1
+ FROM tags_vn tv
+ JOIN tags t ON t.id = tv.tag
+ LEFT JOIN users u ON u.id = tv.uid
+ WHERE NOT t.hidden
+ AND NOT tv.ignore AND (u.id IS NULL OR u.perm_tag)
+ 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;
+
+ IF uvid IS NULL THEN
+ CREATE INDEX tags_vn_direct_tag_vid ON tags_vn_direct (tag, vid);
+ CREATE INDEX tags_vn_direct_vid ON tags_vn_direct (vid);
DROP INDEX IF EXISTS tags_vn_inherit_tag_vid;
TRUNCATE tags_vn_inherit;
ELSE
@@ -249,31 +273,19 @@ BEGIN
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.
- WITH RECURSIVE t_avg(tag, vid, vote, spoiler) AS (
- SELECT tv.tag, tv.vid, AVG(tv.vote)::real, CASE WHEN COUNT(tv.spoiler) = 0 THEN MIN(t.defaultspoil) ELSE AVG(tv.spoiler)::real END
- FROM tags_vn tv
- JOIN tags t ON t.id = tv.tag
- LEFT JOIN users u ON u.id = tv.uid
- WHERE NOT tv.ignore AND NOT t.hidden
- AND (u.id IS NULL OR u.perm_tag)
- 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
- ), t_all(lvl, tag, vid, vote, spoiler) AS (
- SELECT 15, * FROM t_avg
+ -- Add parent tags to each row in tags_vn_direct
+ WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler) AS (
+ SELECT 15, tag, vid, rating, spoiler
+ FROM tags_vn_direct
+ WHERE (uvid IS NULL OR vid = uvid)
UNION ALL
SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler
FROM t_all ta
JOIN tags_parents tp ON tp.id = ta.tag
WHERE ta.lvl > 0
)
- -- Merge
- SELECT tag, vid, AVG(vote)
- , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.4 THEN 1 ELSE 0 END)::smallint
+ -- Merge duplicates
+ SELECT tag, vid, AVG(vote), MIN(spoiler)
FROM t_all
WHERE tag IN(SELECT id FROM tags WHERE searchable)
GROUP BY tag, vid;
@@ -452,9 +464,10 @@ DECLARE
BEGIN
SELECT id INTO xoldchid FROM changes WHERE itemid = nitemid AND rev = nrev-1;
- -- Update c_search
+ -- Update vn.c_search and tags_vn_*
IF vndbid_type(nitemid) = 'v' THEN
UPDATE vn SET c_search = search_gen_vn(id) WHERE id = nitemid;
+ PERFORM tag_vn_calc(nitemid); -- actually only necessary when the hidden flag is changed
END IF;
-- Update vn.c_search when
diff --git a/sql/perms.sql b/sql/perms.sql
index 0ea27afa..816a45c6 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -64,6 +64,7 @@ GRANT SELECT, INSERT ON tags_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON tags_parents TO vndb_site;
GRANT SELECT, INSERT ON tags_parents_hist TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn TO vndb_site;
+GRANT SELECT ON tags_vn_direct TO vndb_site;
GRANT SELECT ON tags_vn_inherit TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON threads TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON threads_boards TO vndb_site;
diff --git a/sql/schema.sql b/sql/schema.sql
index 31a706ef..99916231 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -845,6 +845,15 @@ CREATE TABLE tags_vn (
lie boolean -- [pub] implies spoiler=0
);
+-- tags_vn_direct
+CREATE TABLE tags_vn_direct (
+ tag vndbid NOT NULL,
+ vid vndbid NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL,
+ lie boolean NOT NULL
+);
+
-- tags_vn_inherit
CREATE TABLE tags_vn_inherit (
tag vndbid NOT NULL,
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index f4220808..1fc957c3 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -22,6 +22,8 @@ CREATE INDEX staff_alias_id ON staff_alias (id);
CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid);
CREATE UNIQUE INDEX threads_boards_pkey ON threads_boards (tid,type,COALESCE(iid, 'r1')); -- 'r1' is an invalid board id
CREATE INDEX tags_vn_date ON tags_vn (date);
+CREATE INDEX tags_vn_direct_tag_vid ON tags_vn_direct (tag, vid);
+CREATE INDEX tags_vn_direct_vid ON tags_vn_direct (vid);
CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
CREATE INDEX tags_vn_uid ON tags_vn (uid) WHERE uid IS NOT NULL;
CREATE INDEX tags_vn_vid ON tags_vn (vid);
diff --git a/util/updates/2022-08-03-tags_vn_direct.sql b/util/updates/2022-08-03-tags_vn_direct.sql
new file mode 100644
index 00000000..e8a2445c
--- /dev/null
+++ b/util/updates/2022-08-03-tags_vn_direct.sql
@@ -0,0 +1,10 @@
+CREATE TABLE tags_vn_direct (
+ tag vndbid NOT NULL,
+ vid vndbid NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL,
+ lie boolean NOT NULL
+);
+\i sql/func.sql
+\i sql/perms.sql
+SELECT tag_vn_calc(NULL);
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 05:35:01 +0000