summaryrefslogtreecommitdiff
path: root/util/updates/update_wip_lists.sql
blob: 7d8c4b82e7592bf83eb8054c1f483c77f6f7f0d7 (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
-- This script may be run multiple times while in beta, so clean up after ourselves.
-- (Or, uh, before ourselves, in this case...)
DROP TABLE IF EXISTS ulist_vns, ulist_labels, ulist_vns_labels CASCADE;
DROP TRIGGER IF EXISTS ulist_labels_create ON users;
DROP FUNCTION IF EXISTS ulist_labels_create();
DROP FUNCTION IF EXISTS ulist_voted_label();




-- Replaces the current vnlists, votes and wlists tables
CREATE TABLE ulist_vns (
    uid       integer NOT NULL, -- users.id
    vid       integer NOT NULL, -- vn.id
    added     timestamptz NOT NULL DEFAULT NOW(),
    lastmod   timestamptz NOT NULL DEFAULT NOW(), -- updated when anything in this row has changed?
    vote_date timestamptz, -- Used for "recent votes" - also updated when vote has changed?
    vote      smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100),
    started   date,
    finished  date,
    notes     text NOT NULL DEFAULT '',
    PRIMARY KEY(uid, vid)
);

CREATE TABLE ulist_labels (
    uid      integer NOT NULL, -- user.id
    id       integer NOT NULL, -- 0 < builtin < 10 <= custom, ids are reused
    label    text NOT NULL,
    private  boolean NOT NULL,
    PRIMARY KEY(uid, id)
);

CREATE TABLE ulist_vns_labels (
    uid integer NOT NULL, -- user.id
    lbl integer NOT NULL,
    vid integer NOT NULL, -- vn.id
    PRIMARY KEY(uid, lbl, vid)
    -- (uid, lbl) REFERENCES ulist_labels (uid, id) ON DELETE CASCADE
    -- (uid, vid) REFERENCES ulist (uid, vid) ON DELETE CASCADE
    -- Do we want a 'when has this label been applied' timestamp?
);

-- When is a row in ulist 'public'? i.e. When it is visible in a VNs recent votes and in the user's VN list?
--
--  EXISTS(SELECT 1 FROM ulist_vn_label uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uid = ulist.uid AND vid = ulist.vid AND NOT ul.private)
--
-- That is: It is public when it has been assigned at least one non-private label.
--
-- This means that, during the conversion of old lists to this new format, all
-- vns with an 'unknown' status (= old 'unknown' status or voted but not in
-- vnlist/wlist) from users who have not hidden their list should be assigned
-- to a new non-private label.
--
-- The "Don't allow others to see my [..] list" profile option becomes obsolete
-- with this label-based private flag.



\timing

-- The following queries need a consistent view of the database.
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

INSERT INTO ulist_labels (uid, id, label, private)
              SELECT id,  1, 'Playing',   hide_list FROM users
    UNION ALL SELECT id,  2, 'Finished',  hide_list FROM users
    UNION ALL SELECT id,  3, 'Stalled',   hide_list FROM users
    UNION ALL SELECT id,  4, 'Dropped',   hide_list FROM users
    UNION ALL SELECT id,  5, 'Wishlist',  hide_list FROM users
    UNION ALL SELECT id,  6, 'Blacklist', hide_list FROM users
    UNION ALL SELECT id,  7, 'Voted',     hide_list FROM users
    UNION ALL SELECT id, 10, 'Wishlist-High',   hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 0)
    UNION ALL SELECT id, 11, 'Wishlist-Medium', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 1)
    UNION ALL SELECT id, 12, 'Wishlist-Low',    hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 2);

INSERT INTO ulist_vns (uid, vid, added, lastmod, vote_date, vote, notes)
    SELECT COALESCE(wl.uid, vl.uid, vo.uid, ro.uid)
         , COALESCE(wl.vid, vl.vid, vo.vid, ro.vid)
         , LEAST(wl.added, vl.added, vo.date, ro.added)
         , GREATEST(wl.added, vl.added, vo.date, ro.added)
         , vo.date, vo.vote
         , COALESCE(vl.notes, '')
      FROM wlists wl
      FULL JOIN vnlists vl ON vl.uid = wl.uid AND vl.vid = wl.vid
      FULL JOIN votes   vo ON vo.uid = COALESCE(wl.uid, vl.uid) AND vo.vid = COALESCE(wl.vid, vl.vid)
      FULL JOIN ( -- It used to be possible to have items in rlists without a corresponding entry in vnlists, so also merge rows from there.
        SELECT rl.uid, rv.vid, MAX(rl.added)
          FROM rlists rl
          JOIN releases_vn rv ON rv.id = rl.rid
         GROUP BY rl.uid, rv.vid
      ) ro (uid, vid, added) ON ro.uid = COALESCE(wl.uid, vl.uid, vo.uid) AND ro.vid = COALESCE(wl.vid, vl.vid, vo.vid);

INSERT INTO ulist_vns_labels (uid, vid, lbl)
              SELECT uid, vid,  5 FROM wlists WHERE wstat <> 3 -- All wishlisted items except the blacklist
    UNION ALL SELECT uid, vid, 10 FROM wlists WHERE wstat = 0 -- Wishlist-High
    UNION ALL SELECT uid, vid, 11 FROM wlists WHERE wstat = 1 -- Wishlist-Medium
    UNION ALL SELECT uid, vid, 12 FROM wlists WHERE wstat = 2 -- Wishlist-Low
    UNION ALL SELECT uid, vid,  6 FROM wlists WHERE wstat = 3 -- Blacklist
    UNION ALL SELECT uid, vid, status FROM vnlists WHERE status <> 0 -- Playing/Finished/Stalled/Dropped
    UNION ALL SELECT uid, vid,  7 FROM votes;


ALTER TABLE ulist_vns                ADD CONSTRAINT ulist_vns_uid_fkey                 FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns                ADD CONSTRAINT ulist_vns_vid_fkey                 FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE ulist_labels             ADD CONSTRAINT ulist_labels_uid_fkey              FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_fkey          FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_vid_fkey          FOREIGN KEY (vid)       REFERENCES vn            (id);
ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey      FOREIGN KEY (uid,lbl)   REFERENCES ulist_labels  (uid,id) ON DELETE CASCADE;
ALTER TABLE ulist_vns_labels         ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey      FOREIGN KEY (uid,vid)   REFERENCES ulist_vns     (uid,vid) ON DELETE CASCADE;

COMMIT;

\timing

DROP FUNCTION update_vnpopularity();

ALTER TABLE users ADD COLUMN c_vns  integer NOT NULL DEFAULT 0;
ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;

DROP TRIGGER users_votes_update ON votes;
DROP TRIGGER update_vnlist_rlist ON rlists;

\i util/sql/func.sql
\i util/sql/triggers.sql
\i util/sql/perms.sql

\timing
SELECT update_users_ulist_stats(NULL);
CREATE        INDEX ulist_vns_voted        ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL;
CREATE        INDEX users_ign_votes        ON users (id) WHERE ign_votes;


-- Can be done later:
-- DROP TABLE wlists, vnlists, votes;