summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-09-03 09:39:50 +0200
committerYorhel <git@yorhel.nl>2019-09-03 09:40:04 +0200
commitb5adee24115f784a114abb6f6f6a3e31fbd699c1 (patch)
tree680429ba516afc73099d003e44a04231b92a3030
parent1d4482b1463ca18eb787f76b28d7e01e832a3731 (diff)
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.
-rw-r--r--data/notes/atom-feeds26
-rw-r--r--data/notes/chardb578
-rw-r--r--data/notes/mylist-revamp86
-rw-r--r--data/notes/notifications20
-rw-r--r--data/notes/permanent-filters88
-rw-r--r--data/notes/preferences117
-rw-r--r--data/notes/sponsored-links106
-rw-r--r--data/notes/tagmod-overrule55
8 files changed, 0 insertions, 1076 deletions
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: <name>
- | Original name: <orig>
- | ...
- | 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:
- <VN title> <release dropdown> <spoiler checkbox> <role dropdown>
- 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(<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
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 '<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
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 <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
deleted file mode 100644
index 222553aa..00000000
--- a/data/notes/sponsored-links
+++ /dev/null
@@ -1,106 +0,0 @@
-Advertisements
-
-Last modified: 2011-04-10
-Status: Implemented / Implementation may differ from these notes
-
-
-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
-- DLSite English
- Has an affiliate system that includes direct links
- 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
-- PaletWeb
- Has no affiliate system
- Does have JAN codes for a few titles, but inconsistent
- Rather messy website... finding/updating links will be a chore
-- 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
-- Himeyashop / Erogeshop (out of business?)
- 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?
-- Eroge-Europe.com (out of business?)
- Seems to have an affiliate system, haven't really looked at it yet
- Does not store JAN/UPC/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.
-
diff --git a/data/notes/tagmod-overrule b/data/notes/tagmod-overrule
deleted file mode 100644
index 6209b79f..00000000
--- a/data/notes/tagmod-overrule
+++ /dev/null
@@ -1,55 +0,0 @@
-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
-