diff options
author | Yorhel <git@yorhel.nl> | 2011-02-08 11:46:38 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2011-02-08 11:48:12 +0100 |
commit | 5360e67f5808c397a1e931405d47c2f031a95a11 (patch) | |
tree | 9e7e6470e60aa90f52024538423afc1eb9de8f01 /data | |
parent | 64577401e2466911d1a2230f1431714f1d872759 (diff) |
Uploaded some design/implementation notes I had lying around
Used to be private, with the sole purpose of getting a good overview of
things for myself, but I guess these may also be useful to others.
Diffstat (limited to 'data')
-rw-r--r-- | data/notes/atom-feeds | 26 | ||||
-rw-r--r-- | data/notes/mylist-revamp | 86 | ||||
-rw-r--r-- | data/notes/notifications | 20 | ||||
-rw-r--r-- | data/notes/permanent-filters | 88 | ||||
-rw-r--r-- | data/notes/preferences | 117 | ||||
-rw-r--r-- | data/notes/sponsored-links | 108 | ||||
-rw-r--r-- | data/notes/tagmod-overrule | 55 |
7 files changed, 500 insertions, 0 deletions
diff --git a/data/notes/atom-feeds b/data/notes/atom-feeds new file mode 100644 index 00000000..f1be17e8 --- /dev/null +++ b/data/notes/atom-feeds @@ -0,0 +1,26 @@ +Atom Feeds + +Last modified: 2010-11-13 +Status: Implemented + + +New module: Multi::Feed +Automatically generates and updates the following feeds: + www/feeds/ + announcements.atom + Updated?: LISTEN 'newpost'; post.num = 1 and board = 'an' + (what about an edit of the annoucement title/content?) + changes.atom + Updated?: LISTEN 'changes' + posts.atom + Updated?: LISTEN 'newpost' + (what about edits of posts? title/contents can change...) + released.atom (not implemented) + Updated?: daily + LISTEN 'changes'; c.type = 'r' + (more restrictions can be added if the generation time of this feed is long) + +All feeds are updated once every 15 minutes; this is easier and less +error-prone than the above notify solutions that differ for each feed. +Assuming all feeds can be generated in one second, this takes +(1/(15*60))*100 = ~0.1% of server CPU time on average. + 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. + diff --git a/data/notes/notifications b/data/notes/notifications new file mode 100644 index 00000000..4743f6ee --- /dev/null +++ b/data/notes/notifications @@ -0,0 +1,20 @@ +Notifications + +Last modified: 2010-02-06 +Status: Implemented + + ++ = implemented +- = planned + +Always: ++ pm notify for a new post or thread in my discussion board ++ dbdel notify for the deletion of an entry I made or edited ++ listdel notify for the deletion of an entry I voted on / have in my release list / wishlist + +Option "Notify me about database entries I contributed to" (enabled by default) ++ dbedit notify for each edit of an entry I made or edited + +Option "Notify me for site announcements" (disabled by default - too many notifications otherwise) ++ announce notify for each new thread in the 'an' board + diff --git a/data/notes/permanent-filters b/data/notes/permanent-filters new file mode 100644 index 00000000..768a8a71 --- /dev/null +++ b/data/notes/permanent-filters @@ -0,0 +1,88 @@ +Permanent VN/release filters + +Last modified: 2011-01-01 +Status: Implemented + + +Storage: +- format: the usual filter string (as used in fil=X query string) +- location: users_prefs, key = filter_(vn|release) + + +How to fetch entries within Perl with the filters applied: + Special wrapper function for db(VN|Release)Get(), which does the following: + + # compatibility checking/converting + function check_compat(fil, save): + if filters_contain_old_stuff then + fil = convert_old_stuff(filters) + if save then + save_preference(filter_vn, serialize_filter(filters)) + end if + end if + return fil + + function filVNGet(fil_overwrite, opts): + if (not logged_in or not filter_preference) and not fil_overwrite then + return dbFunc(opts) + end if + + filters = check_compat(parse_filter(fil_overwrite || filter_preference), fil_overwrite?dontsave:save) + + # incorrect filters can trigger an error, catch such an error and remove + # the preference if that was what caused the error + if(fil_overwrite) # preferences can't cause the error + return dbFunc(filters + opts); + else + try + create_sql_savepoint() + return dbFunc(filters + opts) + error + rollback_to_sql_savepoint() + results = dbFunc(opts) + # if the previous call also fails, the next command won't be executed + delete_filters_preference() + return results + + A filReleaseGet() would do something similar. In fact, it might make sense + to combine it into a single function filFetchDB(type, fil, opts) + Filters can be disabled by adding a '<filter_name> => undef' to opts. + + +All cases where the current code calls dbVNGet() should be checked and +considered for replacing with the above fetching function. Some cases are: +VN: +- Random visual novels on homepage +- "Random visual novel" menu link +- VN browser + In this case the query string should overwrite preferences? Since + the preference is loaded in the filter selector as a default anyway +- Tag page VN listing + The tag_inc and tag_exc filters should be disabled here? +- Preferably also the random screenshots on the homepage. But this requires + some more code changes. +Release: +- "Upcoming releases" and "Just released" on homepage +- Release browser + Same note as VN browser above + + +Some cases that shouldn't be affected by the filter preferences: +- Edit histories +- User lists (votes, vnlist, wishlist) +- Tag link browser +- VN page release listing +- VN page relations listing +- Producer page VN/release listing +- Release page VN listing +- Database Statistics + (Even if they should, I wouldn't do it. Too heavy on server resources) + + +User interface considerations: +- An extra button "Save as default" will be added to the filter selector if + the visitor is logged in +- Ideally, there should be some indication that filters were applied to all + places where they are used, with the possibility of changing them. + (this is going to to be a pain to implement :-/) + diff --git a/data/notes/preferences b/data/notes/preferences new file mode 100644 index 00000000..0629e51f --- /dev/null +++ b/data/notes/preferences @@ -0,0 +1,117 @@ +User preference storage + +Last modified: 2011-02-06 +Status: Long-term plans / partially implemented + + +up = SQL: users_prefs +Preference old storage method Current storage method Can be changed at +- Interface language Browser or cookie: l10n Browser/up/cookie Perl: Link in main menu (explicit) +- Main skin SQL: users.skin up: skin Perl: Users' profile (explicit) +- Additional CSS SQL: users.customcss up: customcss Perl: Users' profile (explicit) +- NSFW toggle SQL: users.show_nsfw up: show_nsfw Perl: Users' profile (explicit) +- List is private SQL: users.show_list up: hide_list Perl: Users' profile (explicit) +- Notify on announce SQL: users.notify_announce up: notify_announce Perl: Users' notifications page (explicit) +- Notify on DB edit SQL: users.notify_dbedit up: notify_nodbedit Perl: Users' notifications page (explicit) +- Tag spoil level Cookie: tagspoil Cookie: tagspoil JS: VN pages, Tag pages, VN filter settings (all implicit) +- Tag VN page cat - Cookie: tagcat JS: VN pages (implicit) +- Producer page view Cookie: prodrelexpand Cookie: prodrelexpand JS: Producer pages (implicit) +- VN filters - up: filter_vn JS: VN filter settings (explicit) +- Release filters - up: filter_release JS: Release filter settings (explicit) + + +What do we want? +- Ideally, all preferences are saved explicitly. That is, the user can + indicate whether the change of a preference is temporary or should be saved + as the new default. +- Ideally, all preferences are stored on the server. This makes it easy to + convert the preference data on VNDB updates, without having to provide + backwards compatibility with old data. It also scales better than cookies. +- Preferably, you don't have to have an account to set or change preferences. + In the case of the interface language it's quite important that users don't + have to be logged in. For other preferences it's not very important, but I + don't really like the idea of forcing people to create an account. +- Preferably, the user can change each preference at the place where it makes + most sense: + - Default NSFW flag should be set when encountering an NSFW image + - Skin and custom CSS settings should be somewhere in the global page + layout (like the language setting currently is) + - The "my list is private" setting should be set when viewing your + wish/vote/VN list. + Although... this one might be okay on the profile page. + - Most other preferences already are at sensible locations + In particular, I don't like the idea of grouping all preferences on a + single "settings" or "profile" page. This is likely to become a mess (see + AniDB for a nice example), and users might not know something is available + as a preference (like how most users don't know VNDB has skins). +- Don't store everything in separate columns of the users table. Most users + don't actually change their preferences from the defaults, so only saving + the non-default settings will save a significant amount of space. Bloating + the users table with information that is only ever accessed by the user + itself is also a bad idea - this table is used in a lot of joins and can be + browsed on with the user list. + + +Concrete ideas: +- (done) + User preferences can be stored in a separate table: + -- incomplete list of preference keys + CREATE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', + 'hide_list', 'notify_nodbedit', 'notify_announce'); + CREATE TABLE users_prefs ( + uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, + key prefs_key NOT NULL, + value varchar NOT NULL, + PRIMARY KEY(uid, key) + ); + This doesn't store the data in a properly normalized fashion, but is likely + easier to work with anyway. +- (done) + Accessing the prefs table from Perl: + - authCheck() loads all of the users' preferences in a hash + - authPref($key) returns the value of the preference (from the hash) + - authPref($key, $val) sets the preference (in hash and DB) +- (done) + Keep the interface language setting as-is for anonymous visitors. + For logged-in users: + - Store the users' preferred language in the database instead of cookie. + - Contrary to the cookie: do not automatically remove the db preference + even if it's the same as what the browser requests. This is to ensure + that a user gets the same language even when switching PCs. + - When a user logs in and the l10n cookie is set, copy its value into the + DB and remove the cookie. + - Similar with logging out: copy l10n setting to cookie (but keep the DB) + "What language to use" checking order: database, cookie, browser +- (done - except some JS'ed preferences) + All other preferences can be moved to the users_prefs table. It is a lot of + work to correctly save and handle all preferences for anonymous visitors, + so let's stick with logged-in users for now to keep things simple. +- (done - at least the abstraction) + Some preferences need to be read and modified in Javascript. + Reading: + Add global JS variable using inline <script> to the bottom of the page, + before loading the global JS file, and store the required preferences in + there for the JS code to read. + Since some preferences are specific to some pages, add an option to + htmlFooter() to indicate which preferences need to be added. + Writing: + AJAX call to some .xml page. This will kind-of force the input method to + be explicit, since with AJAX you need some kind UI interaction to + indicate when the save is successful. Implicit saving is an especially + bad idea with this approach since that might make a lot of AJAX calls. +- Make implicit preference saving explicit: + - On producer pages, add a link 'Save as default' to the left of the + expand/collapse link when the user is logged in AND the current view is + different from the default. + - On VN pages: same for the spoiler level + - On Tag pages: same for spoiler level + - On VN filter settings: same for spoiler level + I'm not sure I like this idea... unless I can figure out a good abstraction + to nicely add those links with a single line of code. +- Remove "Don't hide NSFW" checkbox from profile page and add similar "Save + as default" links to the VN page. Close to the "show/hide NSFW" at the + screenshots and "Flagged as NSFW" note at the VN image. +- Add a "settings" icon to the user menu title box thing, and have it show a + CSS'ed window when clicked with settings for the skin and custom CSS. + Optionally with Javascripted previewing of the settings. + diff --git a/data/notes/sponsored-links b/data/notes/sponsored-links new file mode 100644 index 00000000..c31001d2 --- /dev/null +++ b/data/notes/sponsored-links @@ -0,0 +1,108 @@ +Advertisements + +Last modified: 2011-01 +Status: Long-term plans / nothing implemented yet + + +Idea: (semi-)large "Buy now" / "Download now" button on VN pages, linking +either to the product on a webshop or displaying a dropdown list with +available releases with links to webshops. + +A link to a webshop only appears if it has at least one release of the VN on +their site, and the link always points directly to the product page, not to +the search function or the homepage. + +A webshop link is internally linked to a release in the database, so we have +all kinds of information including whether it's a download or package, and in +what language it is. + +Preferably, the link also indicates the price and whether it is in stock. + + +Possible parties interested in advertising: +- J-List + Has an affiliate system that includes direct links + doesn't store JAN/UPC/catalog numbers +- Play-asia + Has an affiliate system that includes direct links + stores JAN, UPC, and catalog numbers +- Himeyashop / Erogeshop + Has no affiliate system but has shown interest in link exchanges in the past + Does not store JAN/UPC/catalog numbers + "Temporarily" closed, so probably not a good time to ask for ads? +- DLSite English + Seems to have an affiliate system, haven't really looked at it yet + Most releases don't even have a JAN code or catalog number +- MangaGamer + Rather specific "shop", but could count as one. + Has no affiliate system, but is planning to add one, as announced in + http://mangagamer.wordpress.com/2010/12/31/holidays-passing/ + Releases don't have catalog numbers or EAN codes +- Eroge-Europe.com + Seems to have an affiliate system, haven't really looked at it yet + Does not store JAN/UPC/catalog numbers +- PaletWeb + Has no affiliate system + Does have JAN codes for a few titles, but inconsistent +- CDJapan + Doesn't have that many VNs from what I've browsed, but still several + Has an affiliate system (seems to include direct links) + Has catalog numbers for most (all?) releases +- Hendane! + Does not seem to have many VNs (3 or 4?) + Has no affiliate system + Does not have JAN or catalog numbers + + +So who is going to update all those links? +Three possibilities: + +1. Automatically + By matching JAN/EAN/UPC or catalog numbers from our database with the + information on the webshop, and fetching the information necessary for the + links. + Since Play-asia is the only one storing that kind of information, this + will be rather specific. We can't really expect all other parties to + update their system, and for DLSite and MangaGamer it would involve + creating (official) catalog numbers for each entry - which would be easy + for MG, but certainly not for DLSite. + Even if a shop stores it, we'd need fast and up-to-date access to it. We + have several thousand JAN codes in the database. If we want to make sure + our information is accurate and up-to-date we'd have to check for the + availability of each release each day. Doing this will most likely require + the other party to update their site with an API providing this + information. I somehow doubt they would... + +2. Let the advertiser add and update the info + Add an admin interface to the site allowing advertisers to add links to + their shop to release entries - also allowing them to indicate the price + and stock availability. + Since advertisers benefit from these links, we can assume that, if they + agree to do this, they will keep the info up-to-date. + However, for some reason I don't think many advertisers would want to + invest that much time in advertising on a single site. + Instead of the advertiser itself, it would also be possible to look for a + dedicated user to do this for them. Though somehow I doubt we'd find + someone like that, and I don't feel like doing that myself. + +3. Let our users add and update the info + Add webshop links to release entries. Since the price and stock + availability tend to change over time and our dear users are either pretty + slow on the uptake or too lazy to update VNDB, we can forget about any + other information besides the links. :-( + It might, however, be possible to automatically fetch the price and stock + information anyway since we have the URLs, but in that case the webshop + should either allow us to crawl quite a lot or provide an alternative + method. + Since the list of webshops we link to is not a static one - shops can be + added or removed after a while - we can expect these links to be edited + quite often, which could make a mess with the edit histories. + Alternatively, we could do it VGMdb-like: allow users to simply manage + links where the release is sold, regardless of whether they are + advertising on VNDB or not. This would still make it possible to + special-case advertisers and give them special treatment or fetch + additional information. + +I would greatly prefer option #1, but since that's not very practical option +#3 (the VGMdb-like solution) is probably the best. + diff --git a/data/notes/tagmod-overrule b/data/notes/tagmod-overrule new file mode 100644 index 00000000..6209b79f --- /dev/null +++ b/data/notes/tagmod-overrule @@ -0,0 +1,55 @@ +Allow moderators to overrule a VN tag score + +Last modified: 2011-01-03 +Status: Implemented + + +SQL implementation #1: + Extra column to tags_vn: + ALTER TABLE tags_vn ADD COLUMN overrule boolean NOT NULL DEFAULT false; + There can only be one row in tags_vn with the same (tag, vid) combination + when one is set with overrule = true; this row then automatically indicates + the final score and spoiler setting. + - Pro: This way none of the final score calculating functions will have to be + modified, and this won't incur an extra performance penalty. + - Con: the votes of all other users for that tag and VN will have to be + removed. This makes overruling a VN a non-reversible operation. + - Determining whether a score was forced by a mod: bool_or(tv.overwrite) + - Regular voting on an overruled tag is simply not allowed + - An other mod should be able to remove the overruled vote and replace it + +SQL implementation #2: + Extra column to tags_vn: + ALTER TABLE tags_vn ADD COLUMN ignore boolean NOT NULL DEFAULT false; + Any tag vote with the ignore flag set is ignored in the score calculation. + When a moderator "overrules" a score, all votes with that (tag, vid) will + have ignore=true, except the mods own vote. + - Pro: Far more flexible than #1, can be used to ignore individual votes. + However, using it for anything other than overruling will make it very + hard or even impossible to reliably implement the overruling feature, so + we'll have avoid making use of this flexibility. + - Pro: Votes of other users don't have to be removed + - Pro: Users can still add votes to the tag (although it will be ignored) + - Con: Requires special coding to automatically set new votes on ignore + - Con: Requires modifying score calculation functions, possibly slower + - Determining whether a score was forced by a mod: bool_or(tv.ignore) + (Assumes we don't use the added flexibility) + +Let's go with #2. Will be slightly more work; but at least it's less prone to +irriversible moderation mistakes and more "friendly" to taggers. + + +UI changes: + Add extra 'overrule' checkbox to the 'you' column for moderators. + - Checking this will take over the mods' tagvote and spoiler level and + ignore the votes of all others. + - Unchecking it will de-overrule the score + - When an overruled vote is removed by the mod (setting '-' as vote), the + tag is de-overruled again. + + Add "overruled" indication to "others" column + - A red "!" next to the score column would work + - Simply indicates whether the score has been overruled by a mod + + Add "ignored" / "not counted" indication to tag link browser + |