summaryrefslogtreecommitdiff
path: root/data/notes/mylist-revamp
blob: 4335b7fd2dce309081c01cb2872f4f40c5608254 (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
RFC-01: Mylist revamp

Last modified: 2010-12-19
Status: Implemented


CREATE TABLE vnlists (
  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  vid integer NOT NULL REFERENCES vn (id),
  status smallint NOT NULL DEFAULT 0,
  added TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- likely not used, but whatever
  PRIMARY KEY(uid, vid)
);

-- after converting:
ALTER TABLE rlists DROP COLUMN vstat;
ALTER TABLE rlists ALTER COLUMN rstat RENAME TO status;

vnlist.status: Unknown / Playing / Finished / Stalled / Dropped


Converting from old rlists:
  vstat = X for all releases -> status = X
  vstat = (X\{unknown}) for all releases with vstat != unknown -> status = X
  vstat = (stalled, dropped) for all releases with vstat != unknown -> status = stalled
  vstat = (finished, stalled, dropped) for all releases with vstat != unknown -> status = finished
  vstat = (playing, ..) for all releases with vstat != unknown -> status = playing
Rephrased in easier terms:
  status = first_present([playing, finished, stalled, dropped, unknown], @vstat)
  Where first_present(<order>, <list>) returns the first item in <list> when using the order of <order>
  Since the statusses are coincidentally defined as an integer with a mapping
  in that order (with playing being the lowest number), we can simply say:
    status = min(@vstat without unknown) || unknown


Constraint:
  For each row in rlists, there should be at least one corresponding row in
  vnlists for at least one of the VNs linked to that release.
  This will significantly simplify the the "show my VN list" query, and gives
  the user the option to not add *all* VNs linked to the release to his list.

  Example: the "Infinity Plus" release can be in your rlist, even when only
    E17 is in your vnlist. As long as at least one of the infinity series is
    in your vnlist.

  How to enforce:
  - When a row is deleted from vnlists, also remove all rows from rlists that
    would otherwise not have a corresponding row in vnlists
  - When a row is inserted to rlists and there is not yet a corresponding row
    in vnlists, add a row in vnlists (with status=unknown) for each vn linked
    to the release.
    Alternatively it's possible to add only one of the linked vns, but since
    we can't decide for the user which one he wants, let's just add all of
    them.
  - Deleting a row from rlists or inserting a row to vnlists will never cause
    the constraint to be violated.
  - Strictly, updating rlists.rid or vnlists.vid should also trigger a check,
    but since those columns are never updated we can ignore that.

  How to implement:
  - Unfortunately it's not possible to use a real SQL CONSTRAINT for this,
    due to the complexity of the references.
  - SQL triggers would work. This is the easiest way to ensure the constraint
    is enforced even when rows are inserted/deleted in rlists or vnlists from
    within other triggers or constraints. (e.g. auto-delete vnlist entry when
    VN is hidden or something - bad idea but whatever :P)
    The triggers should probably be defined as CONSTRAINT TRIGGERs and be
    DEFFERABLE. CONSTRAINT TRIGGERs because otherwise the "ON DELETE CASCADE"
    on users.id might do too much work when a user is deleted. DEFFERABLE
    because otherwise one would have to be careful when adding rlists rows
    before vnlists rows. (Doesn't happen with the current code, but oh well)


"My VN List" table layout:
  H: |       |        | Title <sort>         | Status       | Releases* | Vote <sort> |
  V: | check | expand |          title       | status       | releases  | vote        |
  R: |       | check  | date | icons | title | <pad> status |           |             |
  F: | <all> | <all>  | <select> <select> <send>            | <expl>                  |
  C: |   1   |    2   |   3  |   4   |   5   |     6        |     7     |      8      |


Misc. things to keep in mind:
- Update 'listdel' notification to also check the vnlists table
- Allow users to remove rows from vnlists and rlists even when the
  corresponding vn/release entry is hidden.