From b5adee24115f784a114abb6f6f6a3e31fbd699c1 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Tue, 3 Sep 2019 09:39:50 +0200 Subject: Remove old notes, these are not updated and thus not useful They're interesting from a historical perspective, but that's what a git history is for. They're no substitute for actual documentation. --- data/notes/atom-feeds | 26 -- data/notes/chardb | 578 ------------------------------------------- data/notes/mylist-revamp | 86 ------- data/notes/notifications | 20 -- data/notes/permanent-filters | 88 ------- data/notes/preferences | 117 --------- data/notes/sponsored-links | 106 -------- data/notes/tagmod-overrule | 55 ---- 8 files changed, 1076 deletions(-) delete mode 100644 data/notes/atom-feeds delete mode 100644 data/notes/chardb delete mode 100644 data/notes/mylist-revamp delete mode 100644 data/notes/notifications delete mode 100644 data/notes/permanent-filters delete mode 100644 data/notes/preferences delete mode 100644 data/notes/sponsored-links delete mode 100644 data/notes/tagmod-overrule (limited to 'data/notes') diff --git a/data/notes/atom-feeds b/data/notes/atom-feeds deleted file mode 100644 index f1be17e8..00000000 --- a/data/notes/atom-feeds +++ /dev/null @@ -1,26 +0,0 @@ -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/chardb b/data/notes/chardb deleted file mode 100644 index 37dfa80d..00000000 --- a/data/notes/chardb +++ /dev/null @@ -1,578 +0,0 @@ -Character Database - -Last modified: 2011-02-14 -Status: Draft / incomplete - - -*GLOBAL* data layout (ignoring any UI stuff or implementation details): - - Format: (in case it's not obvious) - - field with a single value - - field with multiple values - - subfield - field for each value in the above field - - New DB item: Trait (not versioned, moderated; similar to tags) - - name - - aliases - - description - - parents (multiple parents; similar to tags) - - state (new/approved/deleted; similar to tags) - - added by (similar to tags) - - New DB item: Character (versioned) (without instances) - - name - - original name - - aliases / nicknames - - image - - description - - 3 sizes - - height and weight - - birthday (day and month only; year rarely known and hardly practical) - - list of traits - - trait ID - - spoiler flag - - list of VNs - - VN id - - release id - - spoiler flag ("the fact that this character appears in this game is a spoiler") - - role - - link to "main" character + spoiler indication - - (UNUSED) New DB item: Character (versioned) (instance idea) - - aliases / nicknames (misc. not names of instances) - - default instance - - instances (at least one for each character) - - name - - original name - - image - - description - - spoiler flag - - 3 sizes - - age? birthday? - - list of traits - - trait ID - - spoiler level - - list of VNs - - VN id - - role (protagonist, primary character, side character, appears in) - - list of releases (none implies all) - - - "Same character" spoiler problems: - Case 1: (relatively common) - The characters themselves are not spoilers, but the fact that they are the same is. - Examples (ROT13): - Symphonic Rain: Cubeav & Nevrggn - Fate/stay night: Nepure & Fuvebh - How to handle: - With instances: no solution found yet, other than not using instances - Without instances: character relation with spoiler flag set - - Case 2: (not very uncommon) - The entire existence of a character is a spoiler. - Examples (ROT13): - Ever17: Oyvpx Jvaxry - Aoishiro: Arxngn? Lnfhuvzr? - How to handle: - With instances: make the instance a spoiler (would take care of everything) - Without instances: the relation with the VN should be marked a spoiler - - Case 3: (pretty uncommon) - What appears to be a single character turns out to be multiple characters in the end. - (this one sucks...) - Examples: - Ever17: Xvq (Ubxhgb & Elbtb), Lbh ('Nxv naq 'Uneh) - How to handle: - With instances: (ugly, but does the trick) - make one character entry with two or three instances: one with what - the game wants you to believe and use other instances for the actual - characters (set to spoiler). - Without instances: - make a separate entry for each character that the game wants you to - believe is a single character, and separate entries for the actual - characters. link them together and to VNs with spoiler markins. - - - Traits vs. fields: - - Preferably, we'd put as much data in traits, since these are flexible - - However, we do want to have some basic information (e.g. gender, apparent - age) to be specified early on (e.g. with a dropdown selection). And we also - want them to be nicely ordered on the charpage (e.g. Gender: male). - - It would be nice if it were also possible to limit the selection of some - tags to only one for a specific category. E.g. A character can only have - one "Gender" trait. But this isn't all that important, since I doubt users - are *that* stupid and traits are part of the characters' revisioning - system, which means everything can be moderated quite easily. - - "Displaying everything nice on a characters' page"-solutions: - 1. Table-layout with a "Parent: trait1, trait2" listing, where parent is - the top trait (i.e. without parents). Traits with multiple parents will - have to be listed multiple times. - 2. Same as above, but add a boolean flag "category" to the traits. The - traits with this flag set will be used in the table instead of the - top-traits. This allows more flexibility of the trait tree, but is more - complex to implement. - - - Linking "same character"s together (without instances): - - It is possible to handle this with a regular char<->char "is the same as" - relation. This can become annoying when there are many entries that are - the same. For example, if there are four characters (A-D), then there are - many different ways to link these together with that relation: - "Linked list"-style: A=B; B=A,C; C=B,D; D=C - "Binary Tree"-style: A=B,C; B=A,D; C=A; D=B - "Everything"-style: A=B,C,D; B=A,C,D; C=A,B,D; D=A,B,C - That is quite annoying, both to the user and in the code. :-( - On the upside, since every relation has a "spoiler" option, this does allow - some flexibility: A=B and C=D may be spoilers, but B=C does not have to be. - - An alternative approach: "parent"->"child" relations (let's call them - main->guise relations (thank you AniDB), since the CS terminology fails - here). - To explain: each "guise" character can be linked to a single "main" - character (with spoiler flag). As a restriction, this "main" character can - not itself be linked to an other character as "guise" again. This gives us - an easy structure to work with. With the above example, using "A" as "main" - character (and "->" is "links to"): A; B->A; C->A, D->A. - This approach is actually extremely similar to the idea of using instances: - the data structure created with these links is equivalent to the structure - with instances. The main differences are the implementation and the idea - that the "instances" themselves are centric rather than their "main - character". This idea also prevents the issue of "same character spoiling: - case 1". - - - Misc. questions: - - How to handle cases where in an original release a character only played a - side role, while in a later release this character would get a route? - This isn't entirely uncommon... Possible solutions: - 1. Allow a character to be linked to the same VN more than once with - different role and different releases. This solves the problem, but would - make it hard to generate a nice overview of all characters in a VN - (covering all its releases). - 2. Move the "role" field as a subfield of the release links. This is - probably a better idea... - But I'm going with option 1 anyway, since is easier and more efficient. - - Similar to the above, are there cases where in an original release the - existance of a character is a spoiler, while in a later release it is not? - Can't think if any... - - "Has route" indication? - This obviously doesn't work for all VNs, since routes are not always linked - to characters. In the case that it does work, it should probably be a - subfield of the release links (see the "role" thing above). - Better yet, we should have a "route database". For the future. >_> - - "Age" field? Bad idea? - - May differ per release even when nothing else changed (hello JAST USA). - Can be handled by adding a new character and linking and stuff, but isn't - worth the trouble) - - Conflicting information. For example: When heroines are 1st grade high - school but it is stated in the beginning that "all characters are at - least 18 years old" - - Just a bad idea in general, since there are quite a few fuckable - 10-years-olds, and explicitely stating that is not a very nice thing. - - External links? - - Wikipedia (en)? - - AniDB - - Animecharacterdatabase.com (crappy site, but people seem to use it?) - - MLA? - - - Traits vs. VN tags (not very important for now): - - Ideally, some character traits would imply VN tags. - - For example: a VN that has a character linked to it with the "vampire" - trait, the VN should have the "Vampire" tag. - - Correctly implementing this would be hard, but it is possible to get Multi - to add auto-votes with some rules. This would require traits to have an - "implies" relation with tags. - - "Level" setting can be partly determined from the importance of the role of - the character. Though this will just be a wild guess. - - "Spoiler" setting is inherited, though unknown which level it should have. - The following might work: - tag spoiler = 0 - tag spoiler +1 if the character-VN link is a spoiler - tag spoiler +1 if the tag-character link is a spoiler - - - Ever17 example (with instances, SPOILERS): - These examples are not technically correct, since the two "You"s are two - separate characters, and there is no such thing as "Kid". But the following - example would do the trick in a way that is neither spoilerous, nor - completely wrong. - - You: - alias: You, Nakkyu - Instance #1: - | name: Yubiseiharukana Tanaka - | VNs: v17, all releases, primary character - | spoiler: no - Instance #2: - | name: Yubiseiakikana Tanaka - | VNs: v17, all releases, side character - | spoiler: yes - - Kid: - alias: Kid - Instance #1: - | name: Shounen - | description: Doesn't even remember his own name, dumbass! Protagonist in the 2034 routes. - | VNs: v17, all releases, protagonist - | spoiler: no - Instance #2: - | name: Ryogo Kaburaki - | description: "kid" in 2017, "Takeshi" in 2034 - | VNs: v17, all releases, primary character - | spoiler: yes - Instance #3: - | name: Hokuto - | description: "kid" in 2034, son of Tsugumi and Takeshi - | VNs: v17, all releases, protagonist - | spoiler: yes - - Without instances: make a separate character entry for each of the above - instances and set the appropriate spoiler flags. - - - - -The term "instance": - To make things extra confusing, the term "instance" has two meanings: - 1. In the above part (global data layout) and in Maou's original draft, it - refers to a special database entry of type "instance", separate from the type - "character". - 2. In the rest of this document, I'm using it to mean a character entry which - has the "main character" field set. In the global data layout I used the term - "guise", but I believe "instance" is better. - - - - -User interface considerations: - - Pages to consider: - - trait page - - trait edit - - trait listing / overview - - character page - - character edit - - character listing / search - - VN page (list of characters) - - Trait page: - URI: /i+ - Similar to tag page: basic description + listing of characters. - The listing of characters includes all characters linked to child traits. - (same as with tags) - - Trait edit: - URI: /i+/edit, /i/new - The regular add/edit form. - - What to do with the linked characters when a trait is marked as deleted - or meta? Batch-edit all character entries to remove the trait? Sounds - painful... - - Trait listing / overview: - URI: /i - Just be creative with this, can be similar to the tag overview. - - Character page: - URI: /c+ - If the character is not an instance (i.e. it has no "main character"): - Display the information of the requested character, followed by that of - all instances linked to it. (spoilerous instances are hidden by default). - If the character is an instance: - - Display the information of the requested character, followed by a link - to its main character? (if it's not a spoiler of course). - - Or make no distinction between "Main character" and "instance", and - simply display all information or the main character and its other - instances on the same page? (similar to the main character page). This - is sligtly counter-intuitive when the relation between the instance and - its main character is a spoiler: in that case all the related entries - would be hidden, rather than only those of which their relation is a - spoiler. (See global data layout -> same character linking above for a - discussion). - Information display for a single character: - Name - (orig name) - [image] [table] - [description] - table: - | Name: - | Original name: - | ... - | Visual novels: Role - VN title - | VN title 2 - | > Role - release title - | > Role - other releases - | Trait group #1: Trait1, trait2, .. - | Trait group #2: .. - - Character edit: - URI: /c+/edit - This requires some thinking... - - Batch-edit-with-instances: - Instances more often than not share quite a bit of information with each - other. When editing a character that is an instance or has instances, the - edit page should preferably contain the char-edit-form for all related - characters, and automatically link fields that are the same for all - instances together. E.g. each field could have a checkbox indicating - "same as main character", in which case editing the field in the main - character would update that of the instances as well. - This is slightly more annoying with traits, since this should be done on - a per-trait basis. - - VN-linking: - Just mirror the structure of the chars_traits table: - - A VN can be added more than once to select other releases. This isn't all - that intuitive, but is simple to implement and does the job. - - Trait linking interface? How will it work together with the - batch-edit-with-instances and copying over traits from the main - character? - - Character listing / search: - URI: /c - Nothing special. - - VN page (list of characters): - URI: /v+ (stays the same, obviously) - Add tabs above the "Releases" box with two items: "Main" and "Characters". - "Main": Displays the usual "Releases" / "User stats" / "Screenshots" boxes - "Characters": Displays a list of characters linked to that VN, - getchu-style. Spoilerous characters are hidden by default. - JS-tabs vs. new URI: - JS tabs are nicer, but require all character information to be sent with - each pageview on the VN page. This is heavy on the server and slows down - page loading. This can be avoided by loading the character data using - AJAX when the tab is opened, but I'm not very fond of using AJAX in this - way. So in that sense a separate URI may be a better idea. E.g. /v+/chars - Listing: - Order by role: protag -> main chars -> side -> etc - Display image + quite a bit of information for protag + main chars, - followed by a plain and simple (table) listing of "other characters". - - Misc. stuff: - - Do we want to be able to search for VNs that have a character with a - certain trait? For example, to get a listing of all VNs that have a - "vampire" character. This will be very heavy on the server if it were - implemented without some form of caching, and may not be very useful if - you can't set other constraints as well (e.g. it must be a main character - in the VN). - People will definitely complain if they can't search on their "Genius - protagonist" tag anymore. >_> - - - - -The SQL schema: - (outdated, see /util/updates/update_2.19.sql instead) - - CREATE TABLE traits ( - id SERIAL PRIMARY KEY, - name varchar(250) NOT NULL UNIQUE, - description text NOT NULL DEFAULT '', - meta boolean NOT NULL DEFAULT false, - added timestamptz NOT NULL DEFAULT NOW(), - state smallint NOT NULL DEFAULT 0, - addedby integer NOT NULL DEFAULT 0 REFERENCES users (id) - ); - - CREATE TABLE traits_aliases ( - alias varchar(250) NOT NULL PRIMARY KEY, - trait integer NOT NULL REFERENCES traits (id) - ); - - CREATE TABLE traits_parents ( - trait integer NOT NULL REFERENCES traits (id), - parent integer NOT NULL REFERENCES traits (id), - PRIMARY KEY(trait, parent) - ); - - CREATE TABLE chars ( - id SERIAL PRIMARY KEY, - latest integer NOT NULL DEFAULT 0 REFERENCES chars_rev (id), - locked boolean NOT NULL DEFAULT FALSE, - hidden boolean NOT NULL DEFAULT FALSE - ); - - CREATE TABLE chars_rev ( - id integer NOT NULL PRIMARY KEY REFERENCES changes (id), - cid integer NOT NULL REFERENCES chars (id), - name varchar(250) NOT NULL DEFAULT '', - original varchar(250) NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '', - image integer NOT NULL DEFAULT 0, - "desc" text NOT NULL DEFAULT '', - s_bust smallint NOT NULL DEFAULT 0, -- in cm - s_waist smallint NOT NULL DEFAULT 0, - s_hip smallint NOT NULL DEFAULT 0, - b_month smallint NOT NULL DEFAULT 0, -- birthday - b_day smallint NOT NULL DEFAULT 0, - height smallint NOT NULL DEFAULT 0, -- in cm - weight smallint NOT NULL DEFAULT 0, -- in kg - main integer REFERENCES chars (id), - main_spoil boolean NOT NULL DEFAULT false - ); - - CREATE TABLE chars_traits ( - cid integer NOT NULL REFERENCES chars_rev (id), - tid integer NOT NULL REFERENCES traits (id), - spoil boolean NOT NULL DEFAULT false, -- boolean or smallint? - PRIMARY KEY(cid, tid) - ); - - CREATE TABLE chars_vns ( - cid integer NOT NULL REFERENCES chars_rev (id), - vid integer NOT NULL REFERENCES vn (id), - rid integer REFERENCES releases (id), -- NULL = "all releases" - spoil boolean NOT NULL DEFAULT false, - role char_role NOT NULL DEFAULT 'main', - PRIMARY KEY(cid, vid, rid) - ); - - -- this one is probably required to speed up character-by-trait search. - -- Similar to chars_traits, but has two differences: - -- 1. all parent tags are included here - -- 2. unversioned. i.e. it links to the chars table instead of chars_rev. - CREATE TABLE chars_traits_inherit ( - cid integer NOT NULL REFERENCES chars (id), - tid integer NOT NULL REFERENCES traits (id), - spoil boolean NOT NULL DEFAULT false, -- boolean or smallint? - PRIMARY KEY(cid, tid) - ); - - - - - - -The original Maou draft (which I use as a sort of guideline / inspiration): - - Each game has a list of characters. - - Each "character" has: - 0. an ID (cXXX) - 1. a name (mandatory) - 2. original (kanji/kana) - 3. alias, nicknames - 4. a portray/pic (if so desired, move to instance) - 5. a list of instances (mandatory) - - Instances: - 0. an ID (iXXX) - 1. a vn (mandatory) - 2. a list of releases - 3. a description - what it says on the label - 4. a traitlist - 5. a commentlist - - Traits(have spoilerratings and inheritance, but are not votable): - 1st Trait (mandatory when creating): Role - protagonist, Heroine, Side Character, Antagonist - 2nd Trait ( " " " ): Sex - male, female, both? - following traits should describe the character, our current character tags could prolly be converted for that - - commentlist: People can post (short) opinions about a character quickly... - Users can edit/delete their own entries, mods can edit/delete everything. - - Q: Why instances? - A: Characters often reappear in other games by the same company - works by - age are just one example. More often that not, they change between their - appearances - most obviously, their role changes, but their other - characteristics may also change to do artistic license, different PoV or - (gasp) character development. The alternative to this would be to add each - version as a new character and then link them together, but the end-result - would look rather silly for longer series IMHO. - - Q: How would adding a character work? How would adding new instances work? - A: When adding a character you'd create the first instance together with the - character. When you decide to add another instance, you start with the - currently selected instance as base - so you'd just have to make the - necessary adjustments. - - Q: How would characters be displayed? - A: VN Characterlist (part of/accessible from the VN page): All characters of - the VN together, ordered Protagonist > Heroine > Sidekick > Antagonist, with - the applicable instance only. - Characterpage: Just a single character (think release page), toggle/dropdown - menu/whatever to switch between instances. Hide traits above a set - spoilerlevel (as we're doing with tags already). - - Q: How would searching work? - A: Enter a combination of traits you're looking for (with exclusions - find - all swordwielding heroines that DO NOT have the "has rapescene" trait), get a - list of games that have the characters (instances) in question. If the trait - in question is a spoiler, the charactername shouldn't be displayed, else it - should be presented together with the VN. - - Q: What about Seiyuu? - A: Just link to the corresponding entry in the StaffDB... <_< - - - Release-sensitive Instances: - Normally, the list contains all releases of the VN in question. (If a new - release is added it will be added to that list per default (if there are - multiple instances for that VN, it should be selectable from a dropdown - menu). Adding new instances for releases would work identical to adding a - new instance for another VN (with the difference that when you add an - instance to a release, all other instance remove said VN from their list). - If a character has multiple instances for a single VN, the applicable - releases would be displayed in the character list and the user would be - able to switch between the instances (similar to the character page). - The advantage of this is that it covers everything. The drawback is that it - is more work intensive and complex. - NOTE: Add only new instances for full versions, NOT for trials - - List of Relations: - 5 possibilities: - Instance <-> Instance, display on a simple map - if they're multiple instances, they're all displayed together with (<-> - same character) relations between them - Instance <-> Instance, display on dynamic map - display each character once, but allow to switch between the various - instances while viewing the map, with the relations being redrawn based on - the relations the new instance (and instances being replaced/added based - where necessary) - Instance <-> Instance. display release-centric - first, display the relations of the instances that belong to the release - and connect them. Ignore any that don't belong to the release. Then draw - relations to instances which aren't part of the current VN. Allow to switch - between the various releases with different relation map easily) - Instance <-> Instance, display instances as a single character, - colourcode relations depending on what instances it applies to (with legend - for which colour belongs to what release/instances) - Character <-> Character, what it says - simplistic, not suitable for longer series and more complex universes - - Examples: - c1 - NAME: SAKURAI KEI - ORIGINAL: 櫻井螢 - ALIAS: - PORTRAY: (prolly taken from the getchu page) - INSTANCES: i1, i2 - - i1 - VN: v548 - RELEASES: r1132, r1133 - DESCRIPTION: 5th Seat, Leonhard August. - TRAITS: Antagonist Heroine, Female, Long Hair, Black Hair, Coodere, Lacks - Ending, Has Optional Sex eroscene, Swordwielding, Immortal (Spoiler 1), - Maou's Harem - COMMENTS: Maou(One of my favourite heroines, sadly she's lacking a route.) - - i2 - VN: v548 - RELEASES: r3228 - DESCRIPTION: 5th Seat, Leonhard August. - TRAITS: Antagonist Heroine, Female, Long Hair, Black Hair, Coodere, Has - Ending, Has Sex Scenes, Swordwielding, Immortal (Spoiler 1), Maou's Harem - COMMENTS: Maou(Perfect), RandomPerson(Interesting character) - - really short traittree: - Traits(Role(Protagonist, Heroine (Antagonist Heroine - also child of - Antagonist)), Sidekick, Antagonist), Gender (Male, Female, Both, Other), - Appearance(Hair(Long Hair, Black Hair)), Personality(Deretypes(classic - Tsundere, Tsundere, Deredere, Coodere)), Significance(Routes(Has End(Has - True End), Lacks End), Has Sex Scenes(Has Insignificant/Optional - eroscene, has rapescene (has unavoidable rapescene))), Relation(Sister, - Senpai, Osananajimi), Vocation(Fighting (Swordwielding, Knight), Hacker), - Other(Idiot Friend, Immortal, Maou's Harem) - - Ok, just kidding about the Harem thing <_< - 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(, ) returns the first item in when using the order of - 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 | Status | Releases* | Vote | - V: | check | expand | title | status | releases | vote | - R: | | check | date | icons | title | status | | | - F: | | | | | - 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 deleted file mode 100644 index 4743f6ee..00000000 --- a/data/notes/notifications +++ /dev/null @@ -1,20 +0,0 @@ -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 deleted file mode 100644 index 768a8a71..00000000 --- a/data/notes/permanent-filters +++ /dev/null @@ -1,88 +0,0 @@ -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 ' => 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 deleted file mode 100644 index 0629e51f..00000000 --- a/data/notes/preferences +++ /dev/null @@ -1,117 +0,0 @@ -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