summaryrefslogtreecommitdiff
path: root/sql/func.sql
blob: 94d442fa45c2963b2502d3fc0f27e3f9ecbca084 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
-- A small note on the function naming scheme:
--   edit_*      -> revision insertion abstraction functions
--   *_notify    -> functions issuing a PgSQL NOTIFY statement
--   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)
-- 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;

-- 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 $$
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
END;
$$ LANGUAGE SQL;


-- update_vncache(id) - updates some c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
  UPDATE vn SET
    c_released = COALESCE((
      SELECT MIN(r.released)
        FROM releases r
        JOIN releases_vn rv ON r.id = rv.id
       WHERE rv.vid = $1
         AND r.type <> 'trial'
         AND r.hidden = FALSE
         AND r.released <> 0
      GROUP BY rv.vid
    ), 0),
    c_olang = ARRAY(
      SELECT rl.lang
        FROM releases_lang rl
        JOIN releases r ON r.id = rl.id
        JOIN releases_vn rv ON r.id = rv.id
       WHERE rv.vid = $1
         AND NOT r.hidden
         AND r.released > 0
         AND NOT EXISTS(
            SELECT 1
              FROM releases r2
              JOIN releases_vn rv2 ON r2.id = rv2.id
             WHERE rv2.vid = $1
               AND NOT r2.hidden
               AND r2.released > 0
               AND r2.released < r.released
         )
       GROUP BY rl.lang
       ORDER BY rl.lang
    ),
    c_languages = ARRAY(
      SELECT rl.lang
        FROM releases_lang 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 r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
         AND r.hidden = FALSE
      GROUP BY rl.lang
      ORDER BY rl.lang
    ),
    c_platforms = ARRAY(
      SELECT rp.platform
        FROM releases_platforms rp
        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 r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
        AND r.hidden = FALSE
      GROUP BY rp.platform
      ORDER BY rp.platform
    )
  WHERE id = $1;
$$ LANGUAGE sql;


-- Update vn.c_popularity, c_rating, c_votecount, c_pop_rank and c_rat_rank
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, rating) AS ( -- Ratings and vote counts
    SELECT vid, COALESCE(COUNT(uid), 0),
           COALESCE(
              ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) /
              ((SELECT avgcount FROM avgcount) + COUNT(uid)::real),
           0)
      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, popularity, pop_rank, rat_rank) AS ( -- Combined stats
    SELECT v.id, COALESCE(r.rating, 0), COALESCE(r.count, 0)
         , p.win/(SELECT MAX(win) FROM popularities)
         , CASE WHEN p.win    IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, p.win    DESC NULLS LAST) END
         , CASE WHEN r.rating IS NULL THEN NULL ELSE rank() OVER(ORDER BY hidden, r.rating DESC NULLS LAST) END
      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
  )
  UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity, c_pop_rank = pop_rank, c_rat_rank = rat_rank FROM stats WHERE id = vid;
$$ LANGUAGE SQL;



-- c_weight = if not_referenced then 0 else lower(c_votecount) -> higher(c_weight) && higher(*_stddev) -> higher(c_weight)
--
-- Current algorithm:
--
--   votes_weight = 2 ^ max(0, 14 - c_votecount)   -> exponential weight between 1 and 2^13 (~16k)
--   (sexual|violence)_weight = (stddev/max_stddev)^2 * 100
--   weight = votes_weight + sexual_weight + violence_weight
--
-- This isn't very grounded in theory, I've no clue how statistics work. I
-- suspect confidence intervals/levels are more appropriate for this use case.
CREATE OR REPLACE FUNCTION update_images_cache(vndbid) RETURNS void AS $$
BEGIN
  UPDATE images
     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
    FROM (
      SELECT s.*,
             CASE 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 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
                 , avg(sexual)   FILTER(WHERE NOT iv.ignore) AS sexual_avg,   stddev_pop(sexual)   FILTER(WHERE NOT iv.ignore) AS sexual_stddev
                 , avg(violence) FILTER(WHERE NOT iv.ignore) AS violence_avg, stddev_pop(violence) FILTER(WHERE NOT iv.ignore) AS violence_stddev
              FROM images i
              LEFT JOIN image_votes iv ON iv.id = i.id
              LEFT JOIN users u ON u.id = iv.uid
             WHERE ($1 IS NULL OR i.id = $1)
               AND (u.id IS NULL OR u.perm_imgvote)
             GROUP BY i.id
        ) s
    ) weights
   WHERE weights.id = images.id;
END; $$ LANGUAGE plpgsql;



-- Update reviews.c_up, c_down and c_flagged
CREATE OR REPLACE FUNCTION update_reviews_votes_cache(vndbid) RETURNS void AS $$
BEGIN
  WITH stats(id,up,down,flag) AS (
    SELECT r.id
         , COUNT(*) FILTER(WHERE rv.vote AND NOT u.ign_votes AND r2.id IS NULL)
         , COUNT(*) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes AND r2.id IS NULL)
           -- flag score = up-down < -10, overrule votes count for 10000 (this algorithm is subject to tuning)
         , COALESCE(
            SUM((CASE WHEN rv.vote THEN 1 ELSE -1 END)*(CASE WHEN rv.overrule THEN 10000 ELSE 1 END))
            FILTER(WHERE NOT u.ign_votes AND (r2.id IS NULL OR rv.overrule)),
           0) < -1000
      FROM reviews r
      LEFT JOIN reviews_votes rv ON rv.id = r.id
      LEFT JOIN users u ON u.id = rv.uid
      LEFT JOIN reviews r2 ON r2.vid = r.vid AND r2.uid = rv.uid
     WHERE $1 IS NULL OR r.id = $1
     GROUP BY r.id
  )
  UPDATE reviews SET c_up = up, c_down = down, c_flagged = flag
    FROM stats WHERE reviews.id = stats.id AND (reviews.c_up,reviews.c_down,reviews.c_flagged) <> (stats.up,stats.down,stats.flag);
END; $$ LANGUAGE plpgsql;



-- Update users.c_vns, c_votes and c_wish for one user (when given an id) or all users (when given NULL)
CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) 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
      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
     WHERE $1 IS NULL OR u.id = $1
     GROUP BY u.id
  ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish FROM cnt WHERE id = uid;
END;
$$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a new query plan at invocation time.



-- 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
  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 t.state = 2
           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.tag = 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;

  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;


-- Recalculate traits_chars. Pretty much same thing as tag_vn_calc().
CREATE OR REPLACE FUNCTION traits_chars_calc(ucid integer) 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)
    -- 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
          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
        JOIN traits_parents tp ON tp.trait = tc.tid
        JOIN traits t ON t.id = tp.parent
        WHERE t.state = 2
          AND tc.lvl > 0
    )
    -- 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
     WHERE tid IN(SELECT id FROM traits WHERE searchable)
     GROUP BY tid, cid;

  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;


-- Fully recalculate all rows in stats_cache
CREATE OR REPLACE FUNCTION update_stats_cache_full() RETURNS void AS $$
BEGIN
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn        WHERE hidden = FALSE) WHERE section = 'vn';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases  WHERE hidden = FALSE) WHERE section = 'releases';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars     WHERE hidden = FALSE) WHERE section = 'chars';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff     WHERE hidden = FALSE) WHERE section = 'staff';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags      WHERE state = 2)      WHERE section = 'tags';
  UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits    WHERE state = 2)      WHERE section = 'traits';
END;
$$ LANGUAGE plpgsql;


-- Create ulist labels for new users.
CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$
  INSERT INTO ulist_labels (uid, id, label, private)
       VALUES ($1, 1, 'Playing',   false),
              ($1, 2, 'Finished',  false),
              ($1, 3, 'Stalled',   false),
              ($1, 4, 'Dropped',   false),
              ($1, 5, 'Wishlist',  false),
              ($1, 6, 'Blacklist', false),
              ($1, 7, 'Voted',     false)
  ON CONFLICT (uid, id) DO NOTHING;
$$ LANGUAGE SQL;


-- Returns the title and (where applicable) uid of the user who created the thing for almost every supported vndbid + num.
-- While a function like this would be super useful in many places, it's too slow to be used in large or popular listings.
-- A VIEW that can be joined would offer much better optimization possibilities, but I've not managed to write that in a performant way yet.
-- A MATERIALIZED VIEW would likely be the fastest approach, but keeping that up-to-date seems like a pain.
--
-- Not currently supported: i#, g#, u#, ch#, cv#, sf#
CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title text, uid int) AS $$
  -- x#.#
            SELECT v.title,  h.requester FROM changes h JOIN vn_hist v        ON h.id = v.chid WHERE h.type = 'v' AND vndbid_type($1) = 'v' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  UNION ALL SELECT r.title,  h.requester FROM changes h JOIN releases_hist r  ON h.id = r.chid WHERE h.type = 'r' AND vndbid_type($1) = 'r' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  UNION ALL SELECT p.name,   h.requester FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.type = 'p' AND vndbid_type($1) = 'p' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  UNION ALL SELECT c.name,   h.requester FROM changes h JOIN chars_hist c     ON h.id = c.chid WHERE h.type = 'c' AND vndbid_type($1) = 'c' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  UNION ALL SELECT d.title,  h.requester FROM changes h JOIN docs_hist d      ON h.id = d.chid WHERE h.type = 'd' AND vndbid_type($1) = 'd' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  UNION ALL SELECT sa.name,  h.requester 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.type = 's' AND vndbid_type($1) = 's' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
  -- x#
  UNION ALL SELECT title,   NULL   FROM vn        WHERE vndbid_type($1) = 'v' AND id = vndbid_num($1) AND $2 IS NULL
  UNION ALL SELECT title,   NULL   FROM releases  WHERE vndbid_type($1) = 'r' AND id = vndbid_num($1) AND $2 IS NULL
  UNION ALL SELECT name,    NULL   FROM producers WHERE vndbid_type($1) = 'p' AND id = vndbid_num($1) AND $2 IS NULL
  UNION ALL SELECT name,    NULL   FROM chars     WHERE vndbid_type($1) = 'c' AND id = vndbid_num($1) AND $2 IS NULL
  UNION ALL SELECT title,   NULL   FROM docs      WHERE vndbid_type($1) = 'd' AND id = vndbid_num($1) AND $2 IS NULL
  UNION ALL SELECT sa.name, NULL   FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE vndbid_type($1) = 's' AND s.id = vndbid_num($1) AND $2 IS NOT NULL AND $2 IS NULL
  -- t#
  UNION ALL SELECT title,   NULL   FROM threads WHERE vndbid_type($1) = 't' AND id = $1 AND $2 IS NULL
  -- t#.#
  UNION ALL SELECT t.title, tp.uid FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE vndbid_type($1) = 't' AND t.id = $1 AND $2 IS NOT NULL AND tp.num = $2
  -- w#
  UNION ALL SELECT v.title, w.uid  FROM reviews w JOIN vn v ON v.id = w.vid WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NULL
  -- w#.#
  UNION ALL SELECT v.title, wp.uid FROM reviews w JOIN vn v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NOT NULL AND wp.num = $2
$$ LANGUAGE SQL ROWS 1;




----------------------------------------------------------
--           revision insertion abstraction             --
----------------------------------------------------------

-- The two functions below are utility functions used by the item-specific functions in editfunc.sql

-- create temporary table for generic revision info, and returns the chid of the revision being edited (or NULL).
CREATE OR REPLACE FUNCTION edit_revtable(xtype dbentry_type, xitemid integer, xrev integer) RETURNS integer AS $$
DECLARE
  ret integer;
  x record;
BEGIN
  BEGIN
    CREATE TEMPORARY TABLE edit_revision (
      type dbentry_type NOT NULL,
      itemid integer,
      requester integer,
      ip inet,
      comments text,
      ihid boolean,
      ilock boolean
    );
  EXCEPTION WHEN duplicate_table THEN
    TRUNCATE edit_revision;
  END;
  SELECT INTO x id, ihid, ilock FROM changes c WHERE type = xtype AND itemid = xitemid AND rev = xrev;
  INSERT INTO edit_revision (type, itemid, ihid, ilock) VALUES (xtype, xitemid, COALESCE(x.ihid, FALSE), COALESCE(x.ilock, FALSE));
  RETURN x.id;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
DECLARE
  ret edit_rettype;
  xtype dbentry_type;
BEGIN
  SELECT type INTO xtype FROM edit_revision;
  SELECT itemid INTO ret.itemid FROM edit_revision;
  -- figure out revision number
  SELECT MAX(rev)+1 INTO ret.rev FROM changes WHERE type = xtype AND itemid = ret.itemid;
  SELECT COALESCE(ret.rev, 1) INTO ret.rev;
  -- insert DB item
  IF ret.itemid IS NULL THEN
    CASE xtype
      WHEN 'v' THEN INSERT INTO vn        DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'r' THEN INSERT INTO releases  DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'c' THEN INSERT INTO chars     DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 's' THEN INSERT INTO staff     DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'd' THEN INSERT INTO docs      DEFAULT VALUES RETURNING id INTO ret.itemid;
    END CASE;
  END IF;
  -- insert change
  INSERT INTO changes (type, itemid, rev, requester, ip, comments, ihid, ilock)
    SELECT type, ret.itemid, ret.rev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO ret.chid;
  RETURN ret;
END;
$$ LANGUAGE plpgsql;



-- Check for stuff to be done when an item has been changed
CREATE OR REPLACE FUNCTION edit_committed(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
DECLARE
  xoldchid integer;
BEGIN
  SELECT id INTO xoldchid FROM changes WHERE type = xtype AND itemid = xedit.itemid AND rev = xedit.rev-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 xtype = '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 = xedit.chid)
    THEN
      UPDATE vn SET c_search = NULL WHERE id = xedit.itemid;
      NOTIFY vnsearch;
    END IF;
  END IF;

  -- Set related vn.c_search columns to NULL and notify when
  -- 1. A new release is created
  -- 2. A release has been hidden or unhidden
  -- 3. The release title/original has changed
  -- 4. The releases_vn table differs from a previous revision
  IF xtype = 'r' THEN
    IF -- 1.
       xoldchid IS NULL OR
       -- 2.
       EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = xedit.chid 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 = xedit.chid) OR
       -- 4.
       EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid   EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid) OR
       EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid 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(xedit.chid, xoldchid));
      NOTIFY vnsearch;
    END IF;
  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 xtype = 'r' THEN
    PERFORM update_vncache(vid) FROM (
      SELECT DISTINCT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid)
    ) 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;

  -- Create edit notifications
  INSERT INTO notifications (uid, ntype, iid, num)
       SELECT n.uid, n.ntype, n.iid, n.num FROM changes c, notify(vndbid(c.type::text, c.itemid), c.rev, c.requester) n WHERE c.id = xedit.chid;

  -- Make sure all visual novels linked to a release have a corresponding entry
  -- in ulist_vns for users who have the release in rlists. This is action (3) in
  -- update_vnlist_rlist().
  IF xtype = 'r' AND xoldchid IS NOT NULL
  THEN
    INSERT INTO ulist_vns (uid, vid)
      SELECT rl.uid, rv.vid FROM rlists rl JOIN releases_vn rv ON rv.id = rl.rid WHERE rl.rid = xedit.itemid
    ON CONFLICT (uid, vid) DO NOTHING;
  END IF;

  -- Call update_images_cache() where appropriate
  IF xtype = 'c'
  THEN
    PERFORM update_images_cache(image) FROM chars_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
  END IF;
  IF xtype = 'v'
  THEN
    PERFORM update_images_cache(image) FROM vn_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
    PERFORM update_images_cache(scr) FROM vn_screenshots_hist WHERE chid IN(xoldchid,xedit.chid);
  END IF;
END;
$$ LANGUAGE plpgsql;




----------------------------------------------------------
--                notification functions                --
----------------------------------------------------------


-- Called after a certain event has occurred (new edit, post, etc).
--  'iid' and 'num' identify the item that has been created.
--  'uid' indicates who created the item, providing an easy method of not creating a notification for that user.
--     (can technically be fetched with a DB lookup, too)
--
-- TODO: Don't create a notification if the user still has an unread notification on the same item, but lower 'num'?
CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS TABLE (uid integer, ntype notification_ntype[], iid vndbid, num int) AS $$
  SELECT uid, array_agg(ntype), $1, $2
    FROM (

      -- pm
      SELECT 'pm'::notification_ntype, tb.iid
        FROM threads_boards tb
       WHERE vndbid_type($1) = 't' AND tb.tid = $1 AND tb.type = 'u'

      -- dbdel
      UNION
      SELECT 'dbdel', c_all.requester
        FROM changes c_cur, changes c_all, changes c_pre
       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2   -- Current edit
         AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed
         AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1) -- All edits on this entry, to see whom to notify
         AND c_cur.ihid AND NOT c_pre.ihid
         AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd')

      -- listdel
      UNION
      SELECT 'listdel', u.uid
        FROM changes c_cur, changes c_pre,
             ( SELECT uid FROM ulist_vns WHERE vndbid_type($1) = 'v' AND vid = vndbid_num($1) -- TODO: Could use an index on ulist_vns.vid
               UNION ALL
               SELECT uid FROM rlists    WHERE vndbid_type($1) = 'r' AND rid = vndbid_num($1) -- TODO: Could also use an index, but the rlists table isn't that large so it's still okay
             ) u(uid)
       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2   -- Current edit
         AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed
         AND c_cur.ihid AND NOT c_pre.ihid
         AND $2 > 1 AND vndbid_type($1) IN('v','r')

      -- dbedit
      UNION
      SELECT 'dbedit', c_all.requester
        FROM changes c_cur, changes c_all
        JOIN users u ON u.id = c_all.requester
       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2
         AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1)
         AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd')
         AND c_cur.requester <> 1 -- exclude edits by Multi
         AND u.notify_dbedit

      -- announce
      UNION
      SELECT 'announce', u.id
        FROM threads t
        JOIN threads_boards tb ON tb.tid = t.id
        JOIN users u ON u.notify_announce
       WHERE vndbid_type($1) = 't' AND $2 = 1 AND t.id = $1 AND tb.type = 'an'

      -- post
      -- TODO: Should this also include comments on reviews the user has commented on?
      UNION
      SELECT 'post', u.id
        FROM threads t, threads_posts tp
        JOIN users u ON tp.uid = u.id
       WHERE t.id = $1 AND tp.tid = $1 AND vndbid_type($1) = 't' AND $2 > 1 AND NOT t.private AND NOT t.hidden AND u.notify_post

      -- comment
      UNION
      SELECT 'comment', u.id
        FROM reviews w
        JOIN users u ON w.uid = u.id
       WHERE w.id = $1 AND vndbid_type($1) = 'w' AND $2 IS NOT NULL AND u.notify_comment

    ) AS noti(ntype, uid)
   WHERE uid <> $3
     AND uid <> 1 -- No announcements for Multi
   GROUP BY uid;
$$ LANGUAGE SQL;




----------------------------------------------------------
--                    user management                   --
----------------------------------------------------------
-- XXX: These functions run with the permissions of the 'vndb' user.


-- Returns the raw scrypt parameters (N, r, p and salt) for this user, in order
-- to create an encrypted pass. Returns NULL if this user does not have a valid
-- password.
CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$
  SELECT
    CASE WHEN length(passwd) = 46 THEN substring(passwd from 1 for 14) ELSE NULL END
  FROM users WHERE id = $1
$$ LANGUAGE SQL SECURITY DEFINER;


-- Create a new web session for this user (uid, scryptpass, token)
CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$
  INSERT INTO sessions (uid, token, expires, type) SELECT $1, $3, NOW() + '1 month', 'web' FROM users
   WHERE length($2) = 46 AND length($3) = 20
     AND id = $1 AND passwd = $2
  RETURNING true
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$
  DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'web'
$$ 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(integer, bytea, session_type) RETURNS bool AS $$
  UPDATE sessions SET expires = NOW() + '1 month'
   WHERE uid = $1 AND token = $2 AND type = $3 AND $3 = 'web'
     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();
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_emailexists(text, integer) RETURNS boolean AS $$
  SELECT true FROM users WHERE lower(mail) = lower($1) AND ($2 IS NULL OR id <> $2) LIMIT 1
$$ LANGUAGE SQL SECURITY DEFINER;


-- Create a password reset token. args: email, token. Returns: user id.
-- 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 integer AS $$
  INSERT INTO sessions (uid, token, expires, type)
    SELECT id, $2, NOW()+'1 week', 'pass' FROM users
     WHERE lower(mail) = lower($1) AND length($2) = 20 AND NOT perm_usermod
    RETURNING uid
$$ LANGUAGE SQL SECURITY DEFINER;


-- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_reset_token, new_pass
CREATE OR REPLACE FUNCTION user_setpass(integer, bytea, bytea) RETURNS boolean AS $$
  WITH upd(id) AS (
    UPDATE users SET passwd = $3
     WHERE id = $1
       AND length($3) = 46
       AND (    (passwd = $2 AND length($2) = 46)
             OR EXISTS(SELECT 1 FROM sessions WHERE uid = $1 AND token = $2 AND type = 'pass' AND expires > NOW())
           )
    RETURNING id
  ), del AS( -- Not referenced, but still guaranteed to run
    DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
  )
  SELECT true FROM upd
$$ LANGUAGE SQL SECURITY DEFINER;


-- Internal function, used to verify whether user ($2 with session $3) is
-- allowed to access sensitive data from user $1.
CREATE OR REPLACE FUNCTION user_isauth(integer, integer, bytea) RETURNS boolean AS $$
  SELECT true FROM users
   WHERE id = $2
     AND EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3 AND type = 'web')
     AND ($2 = $1 OR perm_usermod)
$$ LANGUAGE SQL;


-- uid of user email to get, uid currently logged in, session token of currently logged in.
-- Ensures that only the user itself or a useradmin can get someone's email address.
CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS $$
  SELECT mail FROM users WHERE id = $1 AND user_isauth($1, $2, $3)
$$ 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(integer, bytea, bytea, text) RETURNS void AS $$
  INSERT INTO sessions (uid, token, expires, type, mail)
    SELECT id, $3, NOW()+'1 week', 'mail', $4 FROM users
     WHERE id = $1 AND user_isauth($1, $1, $2) AND length($3) = 20
$$ LANGUAGE SQL SECURITY DEFINER;


-- Actually change a user's email address, given a valid token.
CREATE OR REPLACE FUNCTION user_setmail_confirm(integer, bytea) RETURNS boolean AS $$
  WITH u(mail) AS (
    DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'mail' AND expires > NOW() RETURNING mail
  )
  UPDATE users SET mail = (SELECT mail FROM u) WHERE id = $1 AND EXISTS(SELECT 1 FROM u) RETURNING true;
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_setperm_usermod(integer, integer, bytea, boolean) RETURNS void AS $$
  UPDATE users SET perm_usermod = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RETURNS void AS $$
  WITH upd(id) AS (
    UPDATE users SET passwd = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) AND length($4) = 46 RETURNING id
  )
  DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_admin_setmail(integer, integer, bytea, text) RETURNS void AS $$
  UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
$$ LANGUAGE SQL SECURITY DEFINER;