diff options
Diffstat (limited to 'sql/func.sql')
-rw-r--r-- | sql/func.sql | 809 |
1 files changed, 615 insertions, 194 deletions
diff --git a/sql/func.sql b/sql/func.sql index 88277d97..de0a45c3 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -4,49 +4,250 @@ -- notify_* -> functions creating entries in the notifications table -- user_* -> functions to manage users and sessions -- update_* -> functions to update a cache --- *_update ^ (I should probably rename these to --- *_calc ^ the update_* scheme for consistency) +-- *_calc ^ (same, should prolly rename to the update_* scheme for consistency) -- I like to keep the nouns in functions singular, in contrast to the table -- naming scheme where nouns are always plural. But I'm not very consistent -- with that, either. --- strip_bb_tags(text) - simple utility function to aid full-text searching -CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$ - SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi'); -$$ LANGUAGE sql IMMUTABLE; +-- Handy function to format an ipinfo type for human consumption. +CREATE OR REPLACE FUNCTION fmtip(n ipinfo) RETURNS text AS $$ + SELECT COALESCE(COALESCE((n).country, 'X')||':'||(n).asn||COALESCE(':'||(n).as_name,'')||'/', (n).country||'/', '') + || abbrev((n).ip) + || CASE WHEN (n).anonymous_proxy THEN ' ANON' ELSE '' END + || CASE WHEN (n).sattelite_provider THEN ' SAT' ELSE '' END + || CASE WHEN (n).anycast THEN ' ANY' ELSE '' END + || CASE WHEN (n).drop THEN ' DROP' ELSE '' END +$$ LANGUAGE SQL IMMUTABLE; --- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and --- with an associated cost function to make it opaque to the query planner and --- ensure the query planner realizes that this function is _slow_. -CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$ + + +-- Helper function for `update_search()` +CREATE OR REPLACE FUNCTION update_search_terms(objid vndbid) RETURNS SETOF record AS $$ +DECLARE + e int; -- because I'm too lazy to write out 'NULL::int' every time. BEGIN - RETURN to_tsvector('english', public.strip_bb_tags(t)); -END; -$$ LANGUAGE plpgsql IMMUTABLE COST 500; - --- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers. -CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$ - -- The website doesn't require the [spoiler] tag to be closed, the outer replace catches that case. - SELECT regexp_replace(regexp_replace(t, '\[spoiler\].*?\[/spoiler\]', ' ', 'ig'), '\[spoiler\].*', ' ', 'i'); -$$ LANGUAGE sql IMMUTABLE; - - --- Assigns a score to the relevance of a substring match, intended for use in --- an ORDER BY clause. Exact matches are ordered first, prefix matches after --- that, and finally a normal substring match. Not particularly fast, but --- that's to be expected of naive substring searches. --- Pattern must be escaped for use as a LIKE pattern. -CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$ -SELECT CASE - WHEN str ILIKE pattern THEN 0 - WHEN str ILIKE pattern||'%' THEN 1 - WHEN str ILIKE '%'||pattern||'%' THEN 2 - ELSE 3 + CASE vndbid_type(objid) + WHEN 'v' THEN RETURN QUERY + SELECT e, 3, search_norm_term(title) FROM vn_titles WHERE id = objid + UNION ALL SELECT e, 3, search_norm_term(latin) FROM vn_titles WHERE id = objid + UNION ALL SELECT e, 2, search_norm_term(a) FROM vn, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id + -- Remove the various editions/version strings from release titles, + -- this reduces the index size and makes VN search more relevant. + -- People looking for editions should be using the release search. + UNION ALL SELECT e, 1, regexp_replace(search_norm_term(t), '(?: + 体験|ダウンロド|初回限定|初回|限定|通常|廉価|豪華|追加|コレクション + |パッケージ|ダウンロード|ベスト|復刻|新装|7対応|版|生産|リメイク + |first|press|limited|regular|standard|full|remake + |pack|package|boxed|download|complete|popular|premium|deluxe|collectors?|collection + |lowprice|price|free|best|thebest|cheap|budget|reprint|bundle|set|renewal|extended + |special|trial|demo|allages|voiced?|uncensored|web|patch|port|r18|18|earlyaccess + |cd|cdr|cdrom|dvdrom|dvd|dvdpg|disk|disc|steam|for + |(?:win|windows)(?:7|10|95)?|vista|pc9821|support(?:ed)? + |(?:parts?|vol|volumes?|chapters?|v|ver|versions?)(?:[0-9]+) + |editions?|version|production|thebest|append|scenario|dlc)+$', '', 'xg') + FROM ( + SELECT title FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = objid + UNION ALL + SELECT latin FROM releases r JOIN releases_vn rv ON rv.id = r.id JOIN releases_titles rt ON rt.id = r.id WHERE NOT r.hidden AND rv.vid = objid + ) r(t); + + WHEN 'r' THEN RETURN QUERY + SELECT e, 3, search_norm_term(title) FROM releases_titles WHERE id = objid + UNION ALL SELECT e, 3, search_norm_term(latin) FROM releases_titles WHERE id = objid + UNION ALL SELECT e, 1, gtin::text FROM releases WHERE id = objid AND gtin <> 0 + UNION ALL SELECT e, 1, search_norm_term(catalog) FROM releases WHERE id = objid AND catalog <> ''; + + WHEN 'c' THEN RETURN QUERY + SELECT e, 3, search_norm_term(name) FROM chars WHERE id = objid + UNION ALL SELECT e, 3, search_norm_term(latin) FROM chars WHERE id = objid + UNION ALL SELECT e, 2, search_norm_term(a) FROM chars, regexp_split_to_table(alias, E'\n') a(a) WHERE id = objid; + + WHEN 'p' THEN RETURN QUERY + SELECT e, 3, search_norm_term(name) FROM producers WHERE id = objid + UNION ALL SELECT e, 3, search_norm_term(latin) FROM producers WHERE id = objid + UNION ALL SELECT e, 2, search_norm_term(a) FROM producers, regexp_split_to_table(alias, E'\n') a(a) WHERE id = objid; + + WHEN 's' THEN RETURN QUERY + SELECT aid, 3, search_norm_term(name) FROM staff_alias WHERE id = objid + UNION ALL SELECT aid, 3, search_norm_term(latin) FROM staff_alias WHERE id = objid; + + WHEN 'g' THEN RETURN QUERY + SELECT e, 3, search_norm_term(name) FROM tags WHERE id = objid + UNION ALL SELECT e, 2, search_norm_term(a) FROM tags, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id; + + WHEN 'i' THEN RETURN QUERY + SELECT e, 3, search_norm_term(name) FROM traits WHERE id = objid + UNION ALL SELECT e, 2, search_norm_term(a) FROM traits, regexp_split_to_table(alias, E'\n') a(a) WHERE objid = id; + + ELSE RAISE 'unknown objid type'; + END CASE; END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION update_search(objid vndbid) RETURNS void AS $$ + WITH excluded(excluded) AS ( + -- VN, tag & trait search needs to support finding 'hidden' items, but for + -- other entry types we can safely exclude those from the search cache. + SELECT 1 + WHERE (vndbid_type(objid) = 'r' AND EXISTS(SELECT 1 FROM releases WHERE hidden AND id = objid)) + OR (vndbid_type(objid) = 'c' AND EXISTS(SELECT 1 FROM chars WHERE hidden AND id = objid)) + OR (vndbid_type(objid) = 'p' AND EXISTS(SELECT 1 FROM producers WHERE hidden AND id = objid)) + OR (vndbid_type(objid) = 's' AND EXISTS(SELECT 1 FROM staff WHERE hidden AND id = objid)) + ), uniq(subid, prio, label) AS ( + SELECT subid, MAX(prio)::smallint, label + FROM update_search_terms(objid) x (subid int, prio int, label text) + WHERE label IS NOT NULL AND label <> '' AND NOT EXISTS(SELECT 1 FROM excluded) + GROUP BY subid, label + ), terms(subid, prio, label) AS ( + -- It's possible for some entries to have no searchable terms at all, e.g. + -- when their titles only consists of characters that are normalized away. + -- In that case we still need to have at least one row in the search_cache + -- table for the id-based search to work. (Would be nicer to support + -- non-normalized search in those cases, but these cases aren't too common) + SELECT * FROM uniq + UNION ALL + SELECT NULL::int, 1, '' WHERE NOT EXISTS(SELECT 1 FROM excluded) AND NOT EXISTS(SELECT 1 FROM uniq) + ), n(subid, prio, label) AS ( + SELECT COALESCE(t.subid, o.subid), t.prio, COALESCE(t.label, o.label) + FROM terms t + FULL OUTER JOIN (SELECT subid, label FROM search_cache WHERE id = objid) o ON o.subid IS NOT DISTINCT FROM t.subid AND o.label = t.label + ) MERGE INTO search_cache o USING n ON o.id = objid AND (o.subid, o.label) IS NOT DISTINCT FROM (n.subid, n.label) + WHEN NOT MATCHED THEN INSERT (id, subid, prio, label) VALUES (objid, subid, n.prio, n.label) + WHEN MATCHED AND n.prio IS NULL THEN DELETE + WHEN MATCHED AND n.prio <> o.prio THEN UPDATE SET prio = n.prio; $$ LANGUAGE SQL; + +-- Helper function for the titleprefs functions below. +CREATE OR REPLACE FUNCTION titleprefs_swap(p titleprefs, lang language, title text, latin text) RETURNS text[] AS $$ + SELECT ARRAY[lang::text, CASE WHEN ( + CASE WHEN p.t1_lang = lang THEN p.t1_latin + WHEN p.t2_lang = lang THEN p.t2_latin + WHEN p.t3_lang = lang THEN p.t3_latin + WHEN p.t4_lang = lang THEN p.t4_latin ELSE p IS NULL OR p.to_latin END + ) THEN COALESCE(latin, title) ELSE title END, lang::text, CASE WHEN ( + CASE WHEN p.a1_lang = lang THEN p.a1_latin + WHEN p.a2_lang = lang THEN p.a2_latin + WHEN p.a3_lang = lang THEN p.a3_latin + WHEN p.a4_lang = lang THEN p.a4_latin ELSE p.ao_latin END + ) THEN COALESCE(latin, title) ELSE title END] +$$ LANGUAGE SQL STABLE; + + +-- This is a pure-SQL implementation of the title preference selection +-- algorithm in VNWeb::TitlePrefs. Given a preferences object, this function +-- returns a copy of the 'vn' table with two additional columns: +-- * title - Array of: main title language, main title, secondary title language, secondary title +-- * sorttitle - title to be used in ORDER BY clause +-- +-- The 'title' array format is (supposed to be) used pervasively through the +-- back-end code to order to easily pass around titles as a single object and +-- to support proper rendering of both the main & secondary title of each +-- entry. +-- +-- This function looks slow and messy, but it's been specifically written to be +-- transparent to the query planner and so that unused joins can be fully +-- optimized out during query execution. Even with that, it's better to avoid +-- this function in complex queries when possible because you may run into +-- bad query plans by hitting join_collapse_limit or from_collapse_limit. +-- (More info at https://dev.yorhel.nl/doc/vndbtitles) +CREATE OR REPLACE FUNCTION vnt(p titleprefs) RETURNS SETOF vnt AS $$ + -- The language selection logic below is specially written so that the planner can remove references to joined tables corresponding to NULL languages. + SELECT v.*, (CASE + WHEN p.t1_lang = t1.lang AND (NOT p.t1_official OR t1.official) AND (p.t1_official IS NOT NULL OR p.t1_lang = v.olang) THEN ARRAY[t1.lang::text, COALESCE(CASE WHEN p.t1_latin THEN t1.latin ELSE NULL END, t1.title)] + WHEN p.t2_lang = t2.lang AND (NOT p.t2_official OR t2.official) AND (p.t2_official IS NOT NULL OR p.t2_lang = v.olang) THEN ARRAY[t2.lang::text, COALESCE(CASE WHEN p.t2_latin THEN t2.latin ELSE NULL END, t2.title)] + WHEN p.t3_lang = t3.lang AND (NOT p.t3_official OR t3.official) AND (p.t3_official IS NOT NULL OR p.t3_lang = v.olang) THEN ARRAY[t3.lang::text, COALESCE(CASE WHEN p.t3_latin THEN t3.latin ELSE NULL END, t3.title)] + WHEN p.t4_lang = t4.lang AND (NOT p.t4_official OR t4.official) AND (p.t4_official IS NOT NULL OR p.t4_lang = v.olang) THEN ARRAY[t4.lang::text, COALESCE(CASE WHEN p.t4_latin THEN t4.latin ELSE NULL END, t4.title)] + ELSE ARRAY[v.olang::text, COALESCE(CASE WHEN p IS NULL OR p.to_latin THEN ol.latin ELSE NULL END, ol.title)] END + ) || (CASE + WHEN p.a1_lang = a1.lang AND (NOT p.a1_official OR a1.official) AND (p.a1_official IS NOT NULL OR p.a1_lang = v.olang) THEN ARRAY[a1.lang::text, COALESCE(CASE WHEN p.a1_latin THEN a1.latin ELSE NULL END, a1.title)] + WHEN p.a2_lang = a2.lang AND (NOT p.a2_official OR a2.official) AND (p.a2_official IS NOT NULL OR p.a2_lang = v.olang) THEN ARRAY[a2.lang::text, COALESCE(CASE WHEN p.a2_latin THEN a2.latin ELSE NULL END, a2.title)] + WHEN p.a3_lang = a3.lang AND (NOT p.a3_official OR a3.official) AND (p.a3_official IS NOT NULL OR p.a3_lang = v.olang) THEN ARRAY[a3.lang::text, COALESCE(CASE WHEN p.a3_latin THEN a3.latin ELSE NULL END, a3.title)] + WHEN p.a4_lang = a4.lang AND (NOT p.a4_official OR a4.official) AND (p.a4_official IS NOT NULL OR p.a4_lang = v.olang) THEN ARRAY[a4.lang::text, COALESCE(CASE WHEN p.a4_latin THEN a4.latin ELSE NULL END, a4.title)] + ELSE ARRAY[v.olang::text, COALESCE(CASE WHEN p.ao_latin THEN ol.latin ELSE NULL END, ol.title)] END) + , CASE + WHEN p.t1_lang = t1.lang AND (NOT p.t1_official OR t1.official) AND (p.t1_official IS NOT NULL OR p.t1_lang = v.olang) THEN COALESCE(t1.latin, t1.title) + WHEN p.t2_lang = t2.lang AND (NOT p.t2_official OR t2.official) AND (p.t2_official IS NOT NULL OR p.t2_lang = v.olang) THEN COALESCE(t2.latin, t2.title) + WHEN p.t3_lang = t3.lang AND (NOT p.t3_official OR t3.official) AND (p.t3_official IS NOT NULL OR p.t3_lang = v.olang) THEN COALESCE(t3.latin, t3.title) + WHEN p.t4_lang = t4.lang AND (NOT p.t4_official OR t4.official) AND (p.t4_official IS NOT NULL OR p.t4_lang = v.olang) THEN COALESCE(t4.latin, t4.title) + ELSE COALESCE(ol.latin, ol.title) END + FROM vn v + JOIN vn_titles ol ON ol.id = v.id AND ol.lang = v.olang + -- The COALESCE() below is kind of meaningless, but apparently the query planner can't optimize out JOINs with NULL conditions. + LEFT JOIN vn_titles t1 ON t1.id = v.id AND t1.lang = COALESCE(p.t1_lang, 'en') + LEFT JOIN vn_titles t2 ON t2.id = v.id AND t2.lang = COALESCE(p.t2_lang, 'en') + LEFT JOIN vn_titles t3 ON t3.id = v.id AND t3.lang = COALESCE(p.t3_lang, 'en') + LEFT JOIN vn_titles t4 ON t4.id = v.id AND t4.lang = COALESCE(p.t4_lang, 'en') + LEFT JOIN vn_titles a1 ON a1.id = v.id AND a1.lang = COALESCE(p.a1_lang, 'en') + LEFT JOIN vn_titles a2 ON a2.id = v.id AND a2.lang = COALESCE(p.a2_lang, 'en') + LEFT JOIN vn_titles a3 ON a3.id = v.id AND a3.lang = COALESCE(p.a3_lang, 'en') + LEFT JOIN vn_titles a4 ON a4.id = v.id AND a4.lang = COALESCE(p.a4_lang, 'en') +$$ LANGUAGE SQL STABLE; + + + +-- Same thing as vnt() +CREATE OR REPLACE FUNCTION releasest(p titleprefs) RETURNS SETOF releasest AS $$ + SELECT r.*, (CASE + WHEN p.t1_lang = t1.lang AND (p.t1_official IS NOT NULL OR p.t1_lang = r.olang) THEN ARRAY[t1.lang::text, COALESCE(CASE WHEN p.t1_latin THEN t1.latin ELSE NULL END, t1.title)] + WHEN p.t2_lang = t2.lang AND (p.t2_official IS NOT NULL OR p.t2_lang = r.olang) THEN ARRAY[t2.lang::text, COALESCE(CASE WHEN p.t2_latin THEN t2.latin ELSE NULL END, t2.title)] + WHEN p.t3_lang = t3.lang AND (p.t3_official IS NOT NULL OR p.t3_lang = r.olang) THEN ARRAY[t3.lang::text, COALESCE(CASE WHEN p.t3_latin THEN t3.latin ELSE NULL END, t3.title)] + WHEN p.t4_lang = t4.lang AND (p.t4_official IS NOT NULL OR p.t4_lang = r.olang) THEN ARRAY[t4.lang::text, COALESCE(CASE WHEN p.t4_latin THEN t4.latin ELSE NULL END, t4.title)] + ELSE ARRAY[r.olang::text, COALESCE(CASE WHEN p IS NULL OR p.to_latin THEN ol.latin ELSE NULL END, ol.title)] END + ) || (CASE + WHEN p.a1_lang = a1.lang AND (p.a1_official IS NOT NULL OR p.a1_lang = r.olang) THEN ARRAY[a1.lang::text, COALESCE(CASE WHEN p.a1_latin THEN a1.latin ELSE NULL END, a1.title)] + WHEN p.a2_lang = a2.lang AND (p.a2_official IS NOT NULL OR p.a2_lang = r.olang) THEN ARRAY[a2.lang::text, COALESCE(CASE WHEN p.a2_latin THEN a2.latin ELSE NULL END, a2.title)] + WHEN p.a3_lang = a3.lang AND (p.a3_official IS NOT NULL OR p.a3_lang = r.olang) THEN ARRAY[a3.lang::text, COALESCE(CASE WHEN p.a3_latin THEN a3.latin ELSE NULL END, a3.title)] + WHEN p.a4_lang = a4.lang AND (p.a4_official IS NOT NULL OR p.a4_lang = r.olang) THEN ARRAY[a4.lang::text, COALESCE(CASE WHEN p.a4_latin THEN a4.latin ELSE NULL END, a4.title)] + ELSE ARRAY[r.olang::text, COALESCE(CASE WHEN p.ao_latin THEN ol.latin ELSE NULL END, ol.title)] END) + , CASE + WHEN p.t1_lang = t1.lang AND (p.t1_official IS NOT NULL OR p.t1_lang = r.olang) THEN COALESCE(t1.latin, t1.title) + WHEN p.t2_lang = t2.lang AND (p.t2_official IS NOT NULL OR p.t2_lang = r.olang) THEN COALESCE(t2.latin, t2.title) + WHEN p.t3_lang = t3.lang AND (p.t3_official IS NOT NULL OR p.t3_lang = r.olang) THEN COALESCE(t3.latin, t3.title) + WHEN p.t4_lang = t4.lang AND (p.t4_official IS NOT NULL OR p.t4_lang = r.olang) THEN COALESCE(t4.latin, t4.title) + ELSE COALESCE(ol.latin, ol.title) END + FROM releases r + JOIN releases_titles ol ON ol.id = r.id AND ol.lang = r.olang + LEFT JOIN releases_titles t1 ON t1.id = r.id AND t1.lang = COALESCE(p.t1_lang, 'en') AND t1.title IS NOT NULL + LEFT JOIN releases_titles t2 ON t2.id = r.id AND t2.lang = COALESCE(p.t2_lang, 'en') AND t2.title IS NOT NULL + LEFT JOIN releases_titles t3 ON t3.id = r.id AND t3.lang = COALESCE(p.t3_lang, 'en') AND t3.title IS NOT NULL + LEFT JOIN releases_titles t4 ON t4.id = r.id AND t4.lang = COALESCE(p.t4_lang, 'en') AND t4.title IS NOT NULL + LEFT JOIN releases_titles a1 ON a1.id = r.id AND a1.lang = COALESCE(p.a1_lang, 'en') AND a1.title IS NOT NULL + LEFT JOIN releases_titles a2 ON a2.id = r.id AND a2.lang = COALESCE(p.a2_lang, 'en') AND a2.title IS NOT NULL + LEFT JOIN releases_titles a3 ON a3.id = r.id AND a3.lang = COALESCE(p.a3_lang, 'en') AND a3.title IS NOT NULL + LEFT JOIN releases_titles a4 ON a4.id = r.id AND a4.lang = COALESCE(p.a4_lang, 'en') AND a4.title IS NOT NULL +$$ LANGUAGE SQL STABLE; + + + +-- This one just flips the name/original columns around depending on +-- preferences, so is fast enough to use directly. +CREATE OR REPLACE FUNCTION producerst(p titleprefs) RETURNS SETOF producerst AS $$ + SELECT *, titleprefs_swap(p, lang, name, latin), COALESCE(latin, name) FROM producers +$$ LANGUAGE SQL STABLE; + + + +-- Same for charst +CREATE OR REPLACE FUNCTION charst(p titleprefs) RETURNS SETOF charst AS $$ + SELECT *, titleprefs_swap(p, c_lang, name, latin), COALESCE(latin, name) FROM chars +$$ LANGUAGE SQL STABLE; + + + +-- Same for staff_aliast +CREATE OR REPLACE FUNCTION staff_aliast(p titleprefs) RETURNS SETOF staff_aliast AS $$ + SELECT s.*, sa.aid, sa.name, sa.latin + , titleprefs_swap(p, s.lang, sa.name, sa.latin), COALESCE(sa.latin, sa.name) + FROM staff s + JOIN staff_alias sa ON sa.id = s.id +$$ LANGUAGE SQL STABLE; + + + -- update_vncache(id) - updates some c_* columns in the vn table CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$ UPDATE vn SET @@ -55,18 +256,19 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$ FROM releases r JOIN releases_vn rv ON r.id = rv.id WHERE rv.vid = $1 - AND r.type <> 'trial' + AND rv.rtype <> 'trial' AND r.hidden = FALSE AND r.released <> 0 + AND r.official GROUP BY rv.vid ), 0), c_languages = ARRAY( SELECT rl.lang - FROM releases_lang rl + FROM releases_titles rl JOIN releases r ON r.id = rl.id JOIN releases_vn rv ON r.id = rv.id WHERE rv.vid = $1 - AND r.type <> 'trial' + AND rv.rtype <> 'trial' AND NOT rl.mtl AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r.hidden = FALSE @@ -79,7 +281,7 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$ JOIN releases r ON rp.id = r.id JOIN releases_vn rv ON rp.id = rv.id WHERE rv.vid = $1 - AND r.type <> 'trial' + AND rv.rtype <> 'trial' AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r.hidden = FALSE GROUP BY rp.platform @@ -100,40 +302,55 @@ CREATE OR REPLACE FUNCTION update_vncache(vndbid) RETURNS void AS $$ $$ LANGUAGE sql; --- Update vn.c_popularity, c_rating, c_votecount, c_pop_rank, c_rat_rank and c_average +-- Update c_rating, c_votecount, c_pop_rank, c_rat_rank and c_average CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$ WITH votes(vid, uid, vote) AS ( -- List of all non-ignored VN votes SELECT vid, uid, vote FROM ulist_vns WHERE vote IS NOT NULL AND uid NOT IN(SELECT id FROM users WHERE ign_votes) - ), avgcount(avgcount) AS ( -- Average number of votes per VN - SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes ), avgavg(avgavg) AS ( -- Average vote average SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) x(a) ), ratings(vid, count, average, rating) AS ( -- Ratings and vote counts - SELECT vid, COALESCE(COUNT(uid), 0), (AVG(vote)*10)::smallint, - COALESCE( - ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) / - ((SELECT avgcount FROM avgcount) + COUNT(uid)::real), - 0) + SELECT vid, COUNT(uid), (AVG(vote)*10)::smallint, + -- Bayesian average B(a,p,votes) = (p * a + sum(votes)) / (p + count(votes)) + -- p = (1 - min(1, count(votes)/100)) * 7 i.e. linear interpolation from 7 to 0 for vote counts from 0 to 100. + -- a = Average vote average + ( (1 - LEAST(1, COUNT(uid)::real/100))*7 * (SELECT avgavg FROM avgavg) + SUM(vote) ) / + ( (1 - LEAST(1, COUNT(uid)::real/100))*7 + COUNT(uid) ) FROM votes GROUP BY vid - ), popularities(vid, win) AS ( -- Popularity scores (before normalization) - SELECT vid, SUM(rank) - FROM ( - SELECT uid, vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 FROM votes - ) x(uid, vid, rank) - GROUP BY vid - ), stats(vid, rating, count, average, popularity, pop_rank, rat_rank) AS ( -- Combined stats - SELECT v.id, (r.rating*10)::smallint, COALESCE(r.count, 0), r.average - , (p.win/(SELECT MAX(win) FROM popularities)*10000)::smallint - , CASE WHEN p.win IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, p.win DESC NULLS LAST) END + ), capped(vid, count, average, rating) AS ( -- Ratings, but capped + SELECT vid, count, average, CASE + WHEN count < 5 THEN NULL + WHEN count < 50 THEN LEAST(rating, (SELECT rating FROM ratings WHERE count >= 50 ORDER BY rating DESC LIMIT 1 OFFSET 101)) + WHEN count < 100 THEN LEAST(rating, (SELECT rating FROM ratings WHERE count >= 100 ORDER BY rating DESC LIMIT 1 OFFSET 51)) + ELSE rating END + FROM ratings + ), stats(vid, count, average, rating, rat_rank, pop_rank) AS ( -- Combined stats + SELECT v.id, COALESCE(r.count, 0), r.average, (r.rating*10)::smallint , CASE WHEN r.rating IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, r.rating DESC NULLS LAST) END + , rank() OVER(ORDER BY hidden, r.count DESC NULLS LAST) FROM vn v - LEFT JOIN ratings r ON r.vid = v.id - LEFT JOIN popularities p ON p.vid = v.id AND p.win > 0 + LEFT JOIN capped r ON r.vid = v.id ) - UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity, c_pop_rank = pop_rank, c_rat_rank = rat_rank, c_average = average + UPDATE vn SET c_rating = rating, c_votecount = count, c_pop_rank = pop_rank, c_rat_rank = rat_rank, c_average = average FROM stats - WHERE id = vid AND (c_rating, c_votecount, c_popularity, c_pop_rank, c_rat_rank, c_average) IS DISTINCT FROM (rating, count, popularity, pop_rank, rat_rank, average); + WHERE id = vid AND (c_rating, c_votecount, c_pop_rank, c_rat_rank, c_average) IS DISTINCT FROM (rating, count, pop_rank, rat_rank, average); +$$ LANGUAGE SQL; + + + +-- Updates vn.c_length and vn.c_lengthnum +CREATE OR REPLACE FUNCTION update_vn_length_cache(vndbid) RETURNS void AS $$ + WITH s (vid, cnt, len) AS ( + SELECT v.id, count(l.vid) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL AND v.devstatus <> 1) + , percentile_cont(0.5) WITHIN GROUP (ORDER BY l.length + (l.length/4 * (l.speed-1))) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL AND v.devstatus <> 1) + FROM vn v + LEFT JOIN vn_length_votes l ON l.vid = v.id AND l.speed IS NOT NULL AND NOT l.private + LEFT JOIN users u ON u.id = l.uid AND u.perm_lengthvote + WHERE ($1 IS NULL OR v.id = $1) + GROUP BY v.id + ) UPDATE vn SET c_lengthnum = cnt, c_length = len + FROM s + WHERE s.vid = id AND (c_lengthnum, c_length) IS DISTINCT FROM (cnt, len) $$ LANGUAGE SQL; @@ -154,20 +371,23 @@ BEGIN SET c_votecount = votecount, c_sexual_avg = sexual_avg, c_sexual_stddev = sexual_stddev , c_violence_avg = violence_avg, c_violence_stddev = violence_stddev, c_weight = weight, c_uids = uids FROM ( - SELECT s.*, - CASE WHEN EXISTS( + SELECT s.id, s.votecount, s.uids + , COALESCE(s.sexual_avg *100, 200) AS sexual_avg, COALESCE(s.sexual_stddev *100, 0) AS sexual_stddev + , COALESCE(s.violence_avg*100, 200) AS violence_avg, COALESCE(s.violence_stddev*100, 0) AS violence_stddev + , CASE WHEN s.votecount >= 15 THEN 1 -- Lock the weight at 1 at 15 votes, collecting more votes is just inefficient + WHEN EXISTS( SELECT 1 FROM vn v WHERE s.id BETWEEN 'cv1' AND vndbid_max('cv') AND NOT v.hidden AND v.image = s.id UNION ALL SELECT 1 FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE s.id BETWEEN 'sf1' AND vndbid_max('sf') AND NOT v.hidden AND vs.scr = s.id UNION ALL SELECT 1 FROM chars c WHERE s.id BETWEEN 'ch1' AND vndbid_max('ch') AND NOT c.hidden AND c.image = s.id ) - THEN ceil(pow(2, greatest(0, 14 - s.votecount)) + coalesce(pow(s.sexual_stddev, 2), 0)*100 + coalesce(pow(s.violence_stddev, 2), 0)*100)::real + THEN ceil(pow(2, greatest(0, 14 - s.votecount)) + coalesce(pow(s.sexual_stddev, 2), 0)*100 + coalesce(pow(s.violence_stddev, 2), 0)*100) ELSE 0 END AS weight FROM ( SELECT i.id, count(iv.id) AS votecount - , round(avg(sexual) FILTER(WHERE NOT iv.ignore), 2)::real AS sexual_avg - , round(avg(violence) FILTER(WHERE NOT iv.ignore), 2)::real AS violence_avg - , round(stddev_pop(sexual) FILTER(WHERE NOT iv.ignore), 2)::real AS sexual_stddev - , round(stddev_pop(violence) FILTER(WHERE NOT iv.ignore), 2)::real AS violence_stddev + , round(avg(sexual) FILTER(WHERE NOT iv.ignore), 2) AS sexual_avg + , round(avg(violence) FILTER(WHERE NOT iv.ignore), 2) AS violence_avg + , round(stddev_pop(sexual) FILTER(WHERE NOT iv.ignore), 2) AS sexual_stddev + , round(stddev_pop(violence) FILTER(WHERE NOT iv.ignore), 2) AS violence_stddev , coalesce(array_agg(u.id) FILTER(WHERE u.id IS NOT NULL), '{}') AS uids FROM images i LEFT JOIN image_votes iv ON iv.id = i.id @@ -188,8 +408,8 @@ CREATE OR REPLACE FUNCTION update_reviews_votes_cache(vndbid) RETURNS void AS $$ BEGIN WITH stats(id,up,down) AS ( SELECT r.id - , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE rv.vote AND u.ign_votes IS DISTINCT FROM true AND r2.id IS NULL), 0) - , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE NOT rv.vote AND u.ign_votes IS DISTINCT FROM true AND r2.id IS NULL), 0) + , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE rv.vote AND u.ign_votes IS DISTINCT FROM true AND (rv.overrule OR r2.id IS NULL)), 0) + , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE NOT rv.vote AND u.ign_votes IS DISTINCT FROM true AND (rv.overrule OR r2.id IS NULL)), 0) FROM reviews r LEFT JOIN reviews_votes rv ON rv.id = r.id LEFT JOIN users u ON u.id = rv.uid @@ -208,13 +428,12 @@ CREATE OR REPLACE FUNCTION update_users_ulist_stats(vndbid) RETURNS void AS $$ BEGIN WITH cnt(uid, votes, vns, wish) AS ( SELECT u.id - , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND uv.vote IS NOT NULL) -- Voted - , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist - , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id = 5) -- Wishlist + , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND uv.vote IS NOT NULL) -- Voted + , COUNT(uv.vid) FILTER (WHERE NOT uv.c_private AND NOT (uv.labels <@ ARRAY[5,6]::smallint[])) -- Labelled, but not wishlish/blacklist + , COUNT(uv.vid) FILTER (WHERE uwish.private IS NOT DISTINCT FROM false AND uv.labels && ARRAY[5::smallint]) -- Wishlist FROM users u - LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id - LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id - LEFT JOIN ulist_vns uv ON uv.uid = u.id AND uv.vid = uvl.vid + LEFT JOIN ulist_vns uv ON uv.uid = u.id + LEFT JOIN ulist_labels uwish ON uwish.uid = u.id AND uwish.id = 5 WHERE $1 IS NULL OR u.id = $1 GROUP BY u.id ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish @@ -224,82 +443,116 @@ $$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a n --- Recalculate tags_vn_inherit. +-- Update ulist_vns.c_private for a particular (user, vid). vid can be null to +-- update the cache for the all VNs in the user's list, user can also be null +-- to update the cache for everyone. +CREATE OR REPLACE FUNCTION update_users_ulist_private(vndbid, vndbid) RETURNS void AS $$ +BEGIN + WITH p(uid,vid,private) AS ( + SELECT uv.uid, uv.vid, COALESCE(bool_and(l.private), true) + FROM ulist_vns uv + LEFT JOIN unnest(uv.labels) x(id) ON true + LEFT JOIN ulist_labels l ON l.id = x.id AND l.uid = uv.uid + WHERE ($1 IS NULL OR uv.uid = $1) + AND ($2 IS NULL OR uv.vid = $2) + GROUP BY uv.uid, uv.vid + ) UPDATE ulist_vns SET c_private = p.private FROM p + WHERE ulist_vns.uid = p.uid AND ulist_vns.vid = p.vid AND ulist_vns.c_private <> p.private; +END; +$$ LANGUAGE plpgsql; + + + +-- Update 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_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. - 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 - 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 - FROM t_all - WHERE tag IN(SELECT id FROM tags WHERE searchable) - GROUP BY tag, vid; + -- tags_vn_direct + WITH new (tag, vid, rating, count, spoiler, lie) AS ( + -- Rows that we want + SELECT tv.tag, tv.vid + -- https://vndb.org/t13470.28 -> (z || 3) * ((x-y) / (x+y)) + -- No exception is made for the x==y case, a score of 0 seems better to me. + , (COALESCE(AVG(tv.vote) filter (where tv.vote > 0), 3) * SUM(sign(tv.vote)) / COUNT(tv.vote))::real + , LEAST( COUNT(tv.vote) filter (where tv.vote > 0), 32000 )::smallint + , 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) filter(where not lie) + 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 SUM(sign(tv.vote)) > 0 + ), n AS ( + -- Add existing rows from tags_vn_direct as NULLs, so we can delete them during merge + SELECT coalesce(a.tag, b.tag) AS tag, coalesce(a.vid, b.vid) AS vid, a.rating, a.count, a.spoiler, a.lie + FROM new a + FULL OUTER JOIN (SELECT tag, vid FROM tags_vn_direct WHERE uvid IS NULL OR vid = uvid) b on (a.tag, a.vid) = (b.tag, b.vid) + -- Now merge + ) MERGE INTO tags_vn_direct o USING n ON (n.tag, n.vid) = (o.tag, o.vid) + WHEN NOT MATCHED THEN INSERT (tag, vid, rating, count, spoiler, lie) VALUES (n.tag, n.vid, n.rating, (n)."count", n.spoiler, n.lie) + WHEN MATCHED AND n.rating IS NULL THEN DELETE + WHEN MATCHED AND (o.rating, o.count, o.spoiler, o.lie) IS DISTINCT FROM (n.rating, n.count, n.spoiler, n.lie) THEN + UPDATE SET rating = n.rating, count = n.count, spoiler = n.spoiler, lie = n.lie; + + -- tags_vn_inherit, based on the data from tags_vn_direct + WITH new (tag, vid, rating, spoiler, lie) AS ( + -- Add parent tags to tags_vn_direct + WITH RECURSIVE t_all(lvl, tag, vid, vote, spoiler, lie) AS ( + SELECT 15, tag, vid, rating, spoiler, lie + 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, ta.lie + FROM t_all ta + JOIN tags_parents tp ON tp.id = ta.tag + WHERE ta.lvl > 0 + -- Merge duplicates + ) SELECT tag, vid, AVG(vote)::real, MIN(spoiler), bool_and(lie) + FROM t_all + WHERE tag IN(SELECT id FROM tags WHERE searchable) + GROUP BY tag, vid + ), n AS ( + -- Add existing rows from tags_vn_inherit as NULLs, so we can delete them during merge + SELECT coalesce(a.tag, b.tag) AS tag, coalesce(a.vid, b.vid) AS vid, a.rating, a.spoiler, a.lie + FROM new a + FULL OUTER JOIN (SELECT tag, vid FROM tags_vn_inherit WHERE uvid IS NULL OR vid = uvid) b on (a.tag, a.vid) = (b.tag, b.vid) + -- Now merge + ) MERGE INTO tags_vn_inherit o USING n ON (n.tag, n.vid) = (o.tag, o.vid) + WHEN NOT MATCHED THEN INSERT (tag, vid, rating, spoiler, lie) VALUES (n.tag, n.vid, n.rating, n.spoiler, n.lie) + WHEN MATCHED AND n.rating IS NULL THEN DELETE + WHEN MATCHED AND (o.rating, o.spoiler, o.lie) IS DISTINCT FROM (n.rating, n.spoiler, n.lie) THEN + UPDATE SET rating = n.rating, spoiler = n.spoiler, lie = n.lie; 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; +$$ LANGUAGE plpgsql; -- Recalculate traits_chars. Pretty much same thing as tag_vn_calc(). CREATE OR REPLACE FUNCTION traits_chars_calc(ucid vndbid) RETURNS void AS $$ BEGIN - 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) + WITH new (tid, cid, spoil, lie) AS ( -- 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) - WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler) AS ( - SELECT 15, tid, ct.id, spoil + WITH RECURSIVE t_all(lvl, tid, cid, spoiler, lie) AS ( + SELECT 15, tid, ct.id, spoil, lie FROM chars_traits ct WHERE id NOT IN(SELECT id from chars WHERE hidden) AND (ucid IS NULL OR ct.id = ucid) AND NOT EXISTS (SELECT 1 FROM traits t WHERE t.id = ct.tid AND t.hidden) UNION ALL - SELECT lvl-1, tp.parent, tc.cid, tc.spoiler - FROM traits_chars_all tc + SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, tc.lie + FROM t_all tc JOIN traits_parents tp ON tp.id = tc.tid JOIN traits t ON t.id = tp.parent WHERE NOT t.hidden @@ -307,18 +560,45 @@ BEGIN ) -- now grouped by (tid, cid), with non-searchable traits filtered out SELECT tid, cid - , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler - FROM traits_chars_all + , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint + , bool_and(lie) + FROM t_all WHERE tid IN(SELECT id FROM traits WHERE searchable) - GROUP BY tid, cid; + GROUP BY tid, cid + ), n AS ( + -- Add existing rows from traits_chars as NULLs, so we can delete them during merge + SELECT coalesce(a.tid, b.tid) AS tid, coalesce(a.cid, b.cid) AS cid, a.spoil, a.lie + FROM new a + FULL OUTER JOIN (SELECT tid, cid FROM traits_chars WHERE ucid IS NULL OR cid = ucid) b on (a.tid, a.cid) = (b.tid, b.cid) + -- Now merge + ) MERGE INTO traits_chars o USING n ON (n.tid, n.cid) = (o.tid, o.cid) + WHEN NOT MATCHED THEN INSERT (tid, cid, spoil, lie) VALUES (n.tid, n.cid, n.spoil, n.lie) + WHEN MATCHED AND n.spoil IS NULL THEN DELETE + WHEN MATCHED AND (o.spoil, o.lie) IS DISTINCT FROM (n.spoil, n.lie) THEN + UPDATE SET spoil = n.spoil, lie = n.lie; 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; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION quotes_rand_calc() RETURNS void AS $$ + WITH q(id, vid, score) AS ( + SELECT id, vid, score FROM quotes q WHERE score > 0 AND NOT hidden AND EXISTS(SELECT 1 FROM vn v WHERE v.id = q.vid AND NOT v.hidden) + ), r(id,rand) AS ( + SELECT id, -- 'rand' is chosen such that each VN has an equal probability to be selected, regardless of how many quotes it has. + ( ((dense_rank() OVER (ORDER BY vid)) - 1)::real -- [0..n-1] cumulative count of distinct VNs + + ((sum(score) OVER (PARTITION BY vid ORDER BY id) - score)::float / (sum(score) OVER (PARTITION BY vid))) -- [0,1) cumulative normalized score of this quote + ) / (SELECT count(DISTINCT vid) FROM q) + FROM q + ), u AS ( + UPDATE quotes SET rand = NULL WHERE rand IS NOT NULL AND NOT EXISTS(SELECT 1 FROM r WHERE quotes.id = r.id) + ) UPDATE quotes SET rand = r.rand FROM r WHERE quotes.rand IS DISTINCT FROM r.rand AND r.id = quotes.id; +$$ LANGUAGE SQL; @@ -352,11 +632,13 @@ $$ LANGUAGE SQL; -- Returns generic information for almost every supported vndbid + num. -- Not currently supported: ch#, cv#, sf# +-- Some oddities: +-- * The given user title preferences are not used for explicit revisions. +-- * Trait names are prefixed with their group name ("Group > Trait"), but only for non-revisions. -- -- Returned fields: --- * title - Main/romanized title. +-- * title - Titles array, same format as returned by vnt(). -- For users this is their username, not displayname. --- * original - Original title (if applicable). Used in edit histories -- * uid - User who created/initiated this entry. Used in notification listings and reports -- * hidden - Whether this entry is 'hidden' or private. Used for the reporting function & framework_ object. -- For edits this info comes from the revision itself, not the final entry. @@ -364,38 +646,42 @@ $$ LANGUAGE SQL; -- 'hidden' means "partially visible if you know the ID, but not shown in regular listings". -- For threads it means "totally invisible, does not exist". -- * locked - Whether this entry is 'locked'. Used for the framework_ object. -CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title text, original text, uid vndbid, hidden boolean, locked boolean) AS $$ +CREATE OR REPLACE FUNCTION item_info(titleprefs, vndbid, int, out ret item_info_type) AS $$ BEGIN -- x# - IF $2 IS NULL THEN CASE vndbid_type($1) - WHEN 'v' THEN RETURN QUERY SELECT v.title ::text, v.original::text, NULL::vndbid, v.hidden, v.locked FROM vn v WHERE v.id = $1; - WHEN 'r' THEN RETURN QUERY SELECT r.title ::text, r.original::text, NULL::vndbid, r.hidden, r.locked FROM releases r WHERE r.id = $1; - WHEN 'p' THEN RETURN QUERY SELECT p.name ::text, p.original::text, NULL::vndbid, p.hidden, p.locked FROM producers p WHERE p.id = $1; - WHEN 'c' THEN RETURN QUERY SELECT c.name ::text, c.original::text, NULL::vndbid, c.hidden, c.locked FROM chars c WHERE c.id = $1; - WHEN 'd' THEN RETURN QUERY SELECT d.title ::text, NULL, NULL::vndbid, d.hidden, d.locked FROM docs d WHERE d.id = $1; - WHEN 'g' THEN RETURN QUERY SELECT g.name ::text, NULL, NULL::vndbid, g.hidden, g.locked FROM tags g WHERE g.id = $1; - WHEN 'i' THEN RETURN QUERY SELECT i.name ::text, NULL, NULL::vndbid, i.hidden, i.locked FROM traits i WHERE i.id = $1; - WHEN 's' THEN RETURN QUERY SELECT sa.name ::text, sa.original::text, NULL::vndbid, s.hidden, s.locked FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE s.id = $1; - WHEN 't' THEN RETURN QUERY SELECT t.title ::text, NULL, NULL::vndbid, t.hidden OR t.private, t.locked FROM threads t WHERE t.id = $1; - WHEN 'w' THEN RETURN QUERY SELECT v.title ::text, v.original::text, w.uid, w.c_flagged, w.locked FROM reviews w JOIN vn v ON v.id = w.vid WHERE w.id = $1; - WHEN 'u' THEN RETURN QUERY SELECT u.username::text, NULL, NULL::vndbid, FALSE, FALSE FROM users u WHERE u.id = $1; + IF $3 IS NULL THEN CASE vndbid_type($2) + WHEN 'v' THEN SELECT v.title, NULL::vndbid, v.hidden, v.locked INTO ret FROM vnt($1) v WHERE v.id = $2; + WHEN 'r' THEN SELECT r.title, NULL::vndbid, r.hidden, r.locked INTO ret FROM releasest($1) r WHERE r.id = $2; + WHEN 'p' THEN SELECT p.title, NULL::vndbid, p.hidden, p.locked INTO ret FROM producerst($1) p WHERE p.id = $2; + WHEN 'c' THEN SELECT c.title, NULL::vndbid, c.hidden, c.locked INTO ret FROM charst($1) c WHERE c.id = $2; + WHEN 'd' THEN SELECT ARRAY[NULL, d.title, NULL, d.title], NULL::vndbid, d.hidden, d.locked INTO ret FROM docs d WHERE d.id = $2; + WHEN 'g' THEN SELECT ARRAY[NULL, g.name, NULL, g.name], NULL::vndbid, g.hidden, g.locked INTO ret FROM tags g WHERE g.id = $2; + WHEN 'i' THEN SELECT ARRAY[NULL, COALESCE(g.name||' > ', '')||i.name, NULL, COALESCE(g.name||' > ', '')||i.name], NULL::vndbid, i.hidden, i.locked INTO ret FROM traits i LEFT JOIN traits g ON g.id = i.gid WHERE i.id = $2; + WHEN 's' THEN SELECT s.title, NULL::vndbid, s.hidden, s.locked INTO ret FROM staff_aliast($1) s WHERE s.id = $2 AND s.aid = s.main; + WHEN 't' THEN SELECT ARRAY[NULL, t.title, NULL, t.title], NULL::vndbid, t.hidden OR t.private, t.locked INTO ret FROM threads t WHERE t.id = $2; + WHEN 'w' THEN SELECT v.title, w.uid, w.c_flagged, w.locked INTO ret FROM reviews w JOIN vnt v ON v.id = w.vid WHERE w.id = $2; + WHEN 'u' THEN SELECT ARRAY[NULL, COALESCE(u.username, u.id::text), NULL, COALESCE(u.username, u.id::text)], NULL::vndbid, u.username IS NULL, FALSE INTO ret FROM users u WHERE u.id = $2; + ELSE NULL; END CASE; -- x#.# - ELSE CASE vndbid_type($1) - WHEN 'v' THEN RETURN QUERY SELECT v.title::text, v.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN vn_hist v ON h.id = v.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'r' THEN RETURN QUERY SELECT r.title::text, r.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN releases_hist r ON h.id = r.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'p' THEN RETURN QUERY SELECT p.name ::text, p.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'c' THEN RETURN QUERY SELECT c.name ::text, c.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN chars_hist c ON h.id = c.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'd' THEN RETURN QUERY SELECT d.title::text, NULL, h.requester, h.ihid, h.ilock FROM changes h JOIN docs_hist d ON h.id = d.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'g' THEN RETURN QUERY SELECT g.name ::text, NULL, h.requester, h.ihid, h.ilock FROM changes h JOIN tags_hist g ON h.id = g.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 'i' THEN RETURN QUERY SELECT i.name ::text, NULL, h.requester, h.ihid, h.ilock FROM changes h JOIN traits_hist i ON h.id = i.chid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 's' THEN RETURN QUERY SELECT sa.name::text, sa.original::text, h.requester, h.ihid, h.ilock FROM changes h JOIN staff_hist s ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.aid WHERE h.itemid = $1 AND h.rev = $2; - WHEN 't' THEN RETURN QUERY SELECT t.title::text, NULL, tp.uid, t.hidden OR t.private OR tp.hidden, t.locked FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE t.id = $1 AND tp.num = $2; - WHEN 'w' THEN RETURN QUERY SELECT v.title::text, v.original::text, wp.uid, w.c_flagged OR wp.hidden, w.locked FROM reviews w JOIN vn v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE w.id = $1 AND wp.num = $2; + ELSE CASE vndbid_type($2) + WHEN 'v' THEN SELECT ARRAY[v.olang::text, COALESCE(vo.latin, vo.title), v.olang::text, CASE WHEN vo.latin IS NULL THEN '' ELSE vo.title END], h.requester, h.ihid, h.ilock INTO ret + FROM changes h JOIN vn_hist v ON h.id = v.chid JOIN vn_titles_hist vo ON h.id = vo.chid AND vo.lang = v.olang WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'r' THEN SELECT ARRAY[r.olang::text, COALESCE(ro.latin, ro.title), r.olang::text, CASE WHEN ro.latin IS NULL THEN '' ELSE ro.title END], h.requester, h.ihid, h.ilock INTO ret + FROM changes h JOIN releases_hist r ON h.id = r.chid JOIN releases_titles_hist ro ON h.id = ro.chid AND ro.lang = r.olang WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'p' THEN SELECT ARRAY[p.lang::text, COALESCE(p.latin, p.name), p.lang::text, p.name], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'c' THEN SELECT ARRAY[cm.c_lang::text, COALESCE(c.latin, c.name), cm.c_lang::text, c.name], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN chars cm ON cm.id = h.itemid JOIN chars_hist c ON h.id = c.chid WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'd' THEN SELECT ARRAY[NULL, d.title, NULL, d.title ], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN docs_hist d ON h.id = d.chid WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'g' THEN SELECT ARRAY[NULL, g.name, NULL, g.name ], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN tags_hist g ON h.id = g.chid WHERE h.itemid = $2 AND h.rev = $3; + WHEN 'i' THEN SELECT ARRAY[NULL, i.name, NULL, i.name ], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN traits_hist i ON h.id = i.chid WHERE h.itemid = $2 AND h.rev = $3; + WHEN 's' THEN SELECT ARRAY[s.lang::text, COALESCE(sa.latin, sa.name), s.lang::text, sa.name], h.requester, h.ihid, h.ilock INTO ret FROM changes h JOIN staff_hist s ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.main WHERE h.itemid = $2 AND h.rev = $3; + WHEN 't' THEN SELECT ARRAY[NULL, t.title, NULL, t.title], tp.uid, t.hidden OR t.private OR tp.hidden IS NOT NULL, t.locked INTO ret FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE t.id = $2 AND tp.num = $3; + WHEN 'w' THEN SELECT v.title, wp.uid, w.c_flagged OR wp.hidden IS NOT NULL, w.locked INTO ret FROM reviews w JOIN vnt($1) v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE w.id = $2 AND wp.num = $3; + ELSE NULL; END CASE; END IF; END; -$$ LANGUAGE plpgsql ROWS 1; +$$ LANGUAGE plpgsql STABLE; @@ -414,7 +700,6 @@ BEGIN CREATE TEMPORARY TABLE edit_revision ( itemid vndbid, requester vndbid, - ip inet, comments text, ihid boolean, ilock boolean @@ -436,24 +721,15 @@ DECLARE BEGIN SELECT id INTO xoldchid FROM changes WHERE itemid = nitemid AND rev = nrev-1; - -- Set c_search to NULL and notify when - -- 1. A new VN entry is created - -- 2. The vn title/original/alias has changed - IF vndbid_type(nitemid) = 'v' THEN - IF -- 1. - xoldchid IS NULL OR - -- 2. - EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = nchid) - THEN - UPDATE vn SET c_search = NULL WHERE id = nitemid; - NOTIFY vnsearch; - END IF; + -- Update search_cache + IF vndbid_type(nitemid) IN('v','r','c','p','s','g','i') THEN + PERFORM update_search(nitemid); END IF; - -- Set related vn.c_search columns to NULL and notify when + -- Update search_cache for related VNs when -- 1. A new release is created -- 2. A release has been hidden or unhidden - -- 3. The release title/original has changed + -- 3. The releases_titles have changed -- 4. The releases_vn table differs from a previous revision IF vndbid_type(nitemid) = 'r' THEN IF -- 1. @@ -461,16 +737,43 @@ BEGIN -- 2. EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = nchid AND c2.id = xoldchid) OR -- 3. - EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = nchid) OR + EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = nchid) OR + EXISTS(SELECT title, latin FROM releases_titles_hist WHERE chid = nchid EXCEPT SELECT title, latin FROM releases_titles_hist WHERE chid = xoldchid) OR -- 4. EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = nchid) OR EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = nchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid) THEN - UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(nchid, xoldchid)); - NOTIFY vnsearch; + PERFORM update_search(vid) FROM releases_vn_hist WHERE chid IN(nchid, xoldchid); END IF; END IF; + -- Update drm.c_ref + IF vndbid_type(nitemid) = 'r' THEN + WITH + old (id) AS (SELECT r.drm FROM releases_drm_hist r, changes c WHERE r.chid = xoldchid AND c.id = xoldchid AND NOT c.ihid), + new (id) AS (SELECT r.drm FROM releases_drm_hist r, changes c WHERE r.chid = nchid AND c.id = nchid AND NOT c.ihid), + ins AS (UPDATE drm SET c_ref = c_ref + 1 WHERE id IN(SELECT id FROM new EXCEPT SELECT id FROM old)) + UPDATE drm SET c_ref = c_ref - 1 WHERE id IN(SELECT id FROM old EXCEPT SELECT id FROM new); + END IF; + + -- Update tags_vn_* when the VN's hidden flag is changed + IF vndbid_type(nitemid) = 'v' AND EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = nchid AND c2.id = xoldchid) THEN + PERFORM tag_vn_calc(nitemid); + END IF; + + -- Ensure chars.c_lang is updated when the related VN or char has been edited + -- (the cache also depends on vn.c_released but isn't run when that is updated; + -- not an issue, the c_released is only there as rare fallback) + IF vndbid_type(nitemid) IN('c','v') THEN + WITH x(id,lang) AS ( + SELECT DISTINCT ON (cv.id) cv.id, v.olang + FROM chars_vns cv + JOIN vn v ON v.id = cv.vid + WHERE cv.vid = nitemid OR cv.id = nitemid + ORDER BY cv.id, v.hidden, v.c_released + ) UPDATE chars c SET c_lang = x.lang FROM x WHERE c.id = x.id AND c.c_lang <> x.lang; + END IF; + -- Call update_vncache() for related VNs when a release has been created or edited -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity) IF vndbid_type(nitemid) = 'r' THEN @@ -662,47 +965,52 @@ CREATE OR REPLACE FUNCTION user_getscryptargs(vndbid) RETURNS bytea AS $$ $$ LANGUAGE SQL SECURITY DEFINER; --- Create a new web session for this user (uid, scryptpass, token) -CREATE OR REPLACE FUNCTION user_login(vndbid, bytea, bytea) RETURNS boolean AS $$ - INSERT INTO sessions (uid, token, expires, type) SELECT $1, $3, NOW() + '1 month', 'web' FROM users_shadow - WHERE length($2) = 46 AND length($3) = 20 - AND id = $1 AND passwd = $2 +-- Create a new session for this user (uid, type, scryptpass, token) +CREATE OR REPLACE FUNCTION user_login(vndbid, session_type, bytea, bytea) RETURNS boolean AS $$ + INSERT INTO sessions (uid, token, expires, type) SELECT $1, $4, NOW() + '1 month', $2 FROM users_shadow + WHERE length($3) = 46 AND length($4) = 20 + AND id = $1 AND passwd = $3 AND $2 IN('web', 'api') RETURNING true $$ LANGUAGE SQL SECURITY DEFINER; CREATE OR REPLACE FUNCTION user_logout(vndbid, bytea) RETURNS void AS $$ - DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'web' + DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type IN('web','api') $$ LANGUAGE SQL SECURITY DEFINER; --- Returns true if the given session token is valid. --- As a side effect, this also extends the expiration time of web sessions. -CREATE OR REPLACE FUNCTION user_isvalidsession(vndbid, bytea, session_type) RETURNS bool AS $$ +-- BIG WARNING: Do not use "IS NOT NULL" on the return value, it'll always +-- evaluate to false. Use 'IS DISTINCT FROM NULL' instead. +CREATE OR REPLACE FUNCTION user_validate_session(vndbid, bytea, session_type) RETURNS sessions AS $$ + -- Extends the expiration time of web and api sessions. UPDATE sessions SET expires = NOW() + '1 month' - WHERE uid = $1 AND token = $2 AND type = $3 AND $3 = 'web' + WHERE uid = $1 AND token = $2 AND type = $3 AND $3 IN('web', 'api') AND expires < NOW() + '1 month'::interval - '6 hours'::interval; - SELECT true FROM sessions WHERE uid = $1 AND token = $2 AND type = $3 AND expires > NOW(); + -- Update last use date for api2 sessions + UPDATE sessions SET expires = NOW() + WHERE uid = $1 AND token = $2 AND type = $3 AND $3 = 'api2' + AND (expires = added OR expires::date < 'today'::date); + SELECT * FROM sessions WHERE uid = $1 AND token = $2 AND type = $3 $$ LANGUAGE SQL SECURITY DEFINER; -- Used for duplicate email checks and user-by-email lookup for usermods. -CREATE OR REPLACE FUNCTION user_emailtoid(text) RETURNS SETOF vndbid AS $$ - SELECT id FROM users_shadow WHERE lower(mail) = lower($1) -$$ LANGUAGE SQL SECURITY DEFINER; +CREATE OR REPLACE FUNCTION user_emailtoid(text) RETURNS TABLE (uid vndbid, mail text) AS $$ + SELECT id, mail FROM users_shadow WHERE hash_email(mail) = hash_email($1) +$$ LANGUAGE SQL SECURITY DEFINER ROWS 1; --- Create a password reset token. args: email, token. Returns: user id. +-- Store a password reset token. args: email, token. Returns: user id, actual email. -- Doesn't work for usermods, otherwise an attacker could use this function to -- gain access to all user's emails by obtaining a reset token of a usermod. -- Ideally Postgres itself would send the user an email so that the application -- calling this function doesn't even get the token, and thus can't get access -- to someone's account. But alas, that'd require a separate process. -CREATE OR REPLACE FUNCTION user_resetpass(text, bytea) RETURNS vndbid AS $$ +CREATE OR REPLACE FUNCTION user_resetpass(text, bytea, OUT vndbid, OUT text) AS $$ INSERT INTO sessions (uid, token, expires, type) SELECT id, $2, NOW()+'1 week', 'pass' FROM users_shadow - WHERE lower(mail) = lower($1) AND length($2) = 20 AND NOT perm_usermod - RETURNING uid + WHERE hash_email(mail) = hash_email($1) AND length($2) = 20 AND NOT perm_usermod + RETURNING uid, mail $$ LANGUAGE SQL SECURITY DEFINER; @@ -717,7 +1025,7 @@ CREATE OR REPLACE FUNCTION user_setpass(vndbid, bytea, bytea) RETURNS boolean AS ) RETURNING id ), del AS( -- Not referenced, but still guaranteed to run - DELETE FROM sessions WHERE uid IN(SELECT id FROM upd) + DELETE FROM sessions WHERE uid IN(SELECT id FROM upd) AND type <> 'api2' ) SELECT true FROM upd $$ LANGUAGE SQL SECURITY DEFINER; @@ -740,6 +1048,15 @@ CREATE OR REPLACE FUNCTION user_getmail(vndbid, vndbid, bytea) RETURNS text AS $ $$ LANGUAGE SQL SECURITY DEFINER; +-- Set or unset delete_at for this user. +-- Args: uid, web-token, delete?. +CREATE OR REPLACE FUNCTION user_setdelete(vndbid, bytea, boolean) RETURNS void AS $$ + UPDATE users_shadow + SET delete_at = CASE WHEN $3 THEN NOW() + '1 week'::interval ELSE NULL END + WHERE id = $1 AND user_isauth($1, $1, $2) +$$ LANGUAGE SQL SECURITY DEFINER; + + -- Set a token to change a user's email address. -- Args: uid, web-token, new-email-token, email CREATE OR REPLACE FUNCTION user_setmail_token(vndbid, bytea, bytea, text) RETURNS void AS $$ @@ -774,3 +1091,107 @@ $$ LANGUAGE SQL SECURITY DEFINER; CREATE OR REPLACE FUNCTION user_admin_setmail(vndbid, vndbid, bytea, text) RETURNS void AS $$ UPDATE users_shadow SET mail = $4 WHERE id = $1 AND user_isauth(NULL, $2, $3) $$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_api2_tokens(vndbid, vndbid, bytea) RETURNS SETOF sessions AS $$ + SELECT * FROM sessions WHERE uid = $1 AND type = 'api2' AND user_isauth($1, $2, $3) +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_api2_set_token(vndbid, vndbid, bytea, bytea, text, boolean, boolean) RETURNS void AS $$ + INSERT INTO sessions (uid, type, expires, token, notes, listread, listwrite) + SELECT $1, 'api2', NOW(), $4, $5, $6, $7 + WHERE user_isauth($1, $2, $3) AND length($4) = 20 + ON CONFLICT (uid, token) DO UPDATE SET notes = $5, listread = $6, listwrite = $7 +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION user_api2_del_token(vndbid, vndbid, bytea, bytea) RETURNS void AS $$ + DELETE FROM sessions WHERE uid = $1 AND token = $4 AND user_isauth($1, $2, $3) +$$ LANGUAGE SQL SECURITY DEFINER; + + +CREATE OR REPLACE FUNCTION email_optout_check(text) RETURNS boolean AS $$ + SELECT EXISTS(SELECT 1 FROM email_optout WHERE mail = hash_email($1)) +$$ LANGUAGE SQL SECURITY DEFINER; + + +-- Delete a user account. +-- A 'hard' delete means that the row in the 'users' table is also deleted and +-- any database contributions referring to this user will refer to NULL +-- instead. +-- A non-'hard' delete still deletes all account information but keeps the row +-- in the users table, so that we are still able to audit their database +-- contributions. +-- 'hard' can be set to NULL to do a hard delete when the user has not made any +-- relevant contributions and a soft delete otherwise. +CREATE OR REPLACE FUNCTION user_delete(userid vndbid, hard boolean) RETURNS void AS $$ +BEGIN + -- References can be audited with: grep 'REFERENCES users' sql/tableattrs.sql + IF hard IS NULL THEN + SELECT INTO hard NOT ( + EXISTS(SELECT 1 FROM changes WHERE userid = requester) + OR EXISTS(SELECT 1 FROM changes_patrolled WHERE userid = uid) + OR EXISTS(SELECT 1 FROM images WHERE userid = uploader) + OR EXISTS(SELECT 1 FROM image_votes WHERE userid = uid) + OR EXISTS(SELECT 1 FROM quotes WHERE userid = addedby) + OR EXISTS(SELECT 1 FROM reports_log WHERE userid = uid) + OR EXISTS(SELECT 1 FROM reviews WHERE userid = uid) + OR EXISTS(SELECT 1 FROM reviews_posts WHERE userid = uid) + OR EXISTS(SELECT 1 FROM tags_vn WHERE userid = uid) + OR EXISTS(SELECT 1 FROM threads_posts WHERE userid = uid) + OR EXISTS(SELECT 1 FROM vn_length_votes WHERE userid = uid)); + END IF; + INSERT INTO email_optout (mail) + SELECT hash_email(mail) FROM users_shadow WHERE id = userid + ON CONFLICT (mail) DO NOTHING; + -- Account-related data. + -- (This is unnecessary for a hard delete due to the ON DELETE CASCADE + -- constraint actions, but we need this code anyway for the soft deletes) + DELETE FROM notification_subs WHERE uid = userid; + DELETE FROM notifications WHERE uid = userid; + DELETE FROM rlists WHERE uid = userid; + DELETE FROM saved_queries WHERE uid = userid; + DELETE FROM sessions WHERE uid = userid; + DELETE FROM ulist_labels WHERE uid = userid; + DELETE FROM ulist_vns WHERE uid = userid; + DELETE FROM users_prefs WHERE id = userid; + DELETE FROM users_prefs_tags WHERE id = userid; + DELETE FROM users_prefs_traits WHERE id = userid; + DELETE FROM users_shadow WHERE id = userid; + DELETE FROM users_traits WHERE id = userid; + DELETE FROM users_username_hist WHERE id = userid; + DELETE FROM vn_length_votes WHERE private AND uid = userid; + IF hard THEN + -- Delete votes that have been invalidated by a moderator, otherwise they will suddenly start counting again + DELETE FROM reviews_votes WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND ign_votes); + DELETE FROM threads_poll_votes WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND ign_votes); + DELETE FROM quotes_votes WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND ign_votes); + DELETE FROM tags_vn WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND perm_tag); + DELETE FROM vn_length_votes WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND perm_lengthvote); + DELETE FROM image_votes WHERE uid = userid AND NOT EXISTS(SELECT 1 FROM users WHERE id = userid AND perm_imgvote); + DELETE FROM users WHERE id = userid; + INSERT INTO audit_log (affected_uid, action) VALUES (userid, 'hard delete'); + ELSE + UPDATE users SET + notify_dbedit = DEFAULT, + notify_announce = DEFAULT, + notify_post = DEFAULT, + notify_comment = DEFAULT, + nodistract_noads = DEFAULT, + nodistract_nofancy = DEFAULT, + support_enabled = DEFAULT, + pubskin_enabled = DEFAULT, + username = DEFAULT, + uniname = DEFAULT + WHERE id = userid; + INSERT INTO audit_log (affected_uid, action) VALUES (userid, 'soft delete'); + END IF; +END +$$ LANGUAGE plpgsql; + + +-- Should be called from a cron, deletes user accounts with a delete_at in the past. +CREATE OR REPLACE FUNCTION user_delete() RETURNS int AS $$ + SELECT COUNT(*) FROM (SELECT user_delete(id, null) FROM users_shadow WHERE delete_at < NOW()) x +$$ LANGUAGE SQL SECURITY DEFINER; |