diff options
Diffstat (limited to 'data/notes/mylist-revamp')
-rw-r--r-- | data/notes/mylist-revamp | 86 |
1 files changed, 86 insertions, 0 deletions
diff --git a/data/notes/mylist-revamp b/data/notes/mylist-revamp new file mode 100644 index 00000000..4335b7fd --- /dev/null +++ b/data/notes/mylist-revamp @@ -0,0 +1,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. + |