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