path: root/data/notes
diff options
authorYorhel <>2011-02-08 11:46:38 +0100
committerYorhel <>2011-02-08 11:48:12 +0100
commit5360e67f5808c397a1e931405d47c2f031a95a11 (patch)
tree9e7e6470e60aa90f52024538423afc1eb9de8f01 /data/notes
parent64577401e2466911d1a2230f1431714f1d872759 (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/notes')
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 (
+ 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:
+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
+ 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
+ on 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 @@
+Last modified: 2010-02-06
+Status: Implemented
++ = implemented
+- = planned
++ 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
+- 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:
+- 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.
+- "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: 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 (
+ 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 @@
+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
+ Releases don't have catalog numbers or EAN codes
+ 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