summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-02-13 12:06:47 +0100
committerYorhel <git@yorhel.nl>2011-02-13 12:06:47 +0100
commit359627f10128f36f10b1d4714601fc33c3ce3c8a (patch)
treee800c67207f4156315c9b2343cbca4bc7f9b9a0f
parent5829b57d55b4173592af1a75f3f014f9a9ae2594 (diff)
chardb: Added notes file and started implementing the traits
-rw-r--r--Makefile8
-rw-r--r--data/lang.txt76
-rw-r--r--data/notes/chardb569
-rw-r--r--lib/VNDB/DB/Traits.pm138
-rw-r--r--lib/VNDB/Func.pm71
-rw-r--r--lib/VNDB/Handler/Tags.pm68
-rw-r--r--lib/VNDB/Handler/Traits.pm82
-rw-r--r--util/sql/schema.sql2
-rw-r--r--util/updates/update_2.19.sql26
9 files changed, 972 insertions, 68 deletions
diff --git a/Makefile b/Makefile
index 16246871..a03efb2f 100644
--- a/Makefile
+++ b/Makefile
@@ -38,7 +38,8 @@
.PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart sql-import\
- update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17 update-2.18
+ update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17\
+ update-2.18 update-2.19
all: dirs js skins robots data/config.pl
@@ -173,3 +174,8 @@ update-2.18: all
$(multi-stop)
${runpsql} < util/updates/update_2.18.sql
$(multi-start)
+
+update-2.19: all
+ $(multi-stop)
+ ${runpsql} < util/updates/update_2.19.sql
+ $(multi-start)
diff --git a/data/lang.txt b/data/lang.txt
index 4e340d19..d3120f4b 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -4735,6 +4735,82 @@ nl : Geweigerde tags
#############################################################################
+## Trait page ##
+#############################################################################
+# Handler::Traits
+
+
+# Trait page (/i+)
+
+:_traitp_title
+en : [index,_1,Meta trait,Trait]: [_2]
+ru*:
+cs*:
+hu*:
+nl : [index,_1,Metakenmerk,Kenmerk]: [_2]
+
+:_traitp_del_title
+en : Trait deleted
+ru*:
+cs*:
+hu*:
+nl : Verwijderd kenmerk
+
+:_traitp_del_msg
+en : This trait has been removed from the database, and cannot be used or re-added.
+ File a request on the [url,/t/db,discussion board] if you disagree with this.
+ru*:
+cs*:
+hu*:
+nl : Dit kenmerk is verwijderd uit de database, en kan hier niet gebruikt worden.
+ Open een topic op het [url,/t/db,forum] als je het hier niet mee eens bent.
+
+:_traitp_pending_title
+en : Waiting for approval
+ru : Ждёт одобрения
+cs : Čeká na schválení
+hu : Megerősítésre vár
+nl : Wacht op goedkeuring
+
+:_traitp_pending_msg
+en : This trait is waiting for a moderator to approve it.
+ru*:
+cs*:
+hu*:
+nl : Dit kenmerk is nog niet goedgekeurd door een moderator.
+
+:_traitp_indexlink
+en : Traits
+ru*:
+cs*:
+hu*:
+nl : Kenmerken
+
+:_traitp_aliases
+en : Aliases
+ru : Прочие названия
+cs : Aliasy
+hu : Más nevek
+nl : Aliassen
+
+:_traitp_childs
+en : Child traits
+ru*:
+cs*:
+hu*:
+nl : Subkenmerken
+
+:_traitp_more
+en : [_1] more [quant,_1,trait,traits]
+ru*:
+cs*:
+hu*:
+nl : nog [_1] [quant,_1,kenmerk,kenmerken]
+
+
+
+
+#############################################################################
## Personal User Lists ##
#############################################################################
# Handler::ULists
diff --git a/data/notes/chardb b/data/notes/chardb
new file mode 100644
index 00000000..4f828ae8
--- /dev/null
+++ b/data/notes/chardb
@@ -0,0 +1,569 @@
+Character Database
+
+Last modified: 2011-02-13
+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.
+
+
+ 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.
+
+ 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:
+
+ 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/lib/VNDB/DB/Traits.pm b/lib/VNDB/DB/Traits.pm
new file mode 100644
index 00000000..81ac95e1
--- /dev/null
+++ b/lib/VNDB/DB/Traits.pm
@@ -0,0 +1,138 @@
+
+package VNDB::DB::Traits;
+
+# This module is for a large part a copy of VNDB::DB::Tags. I could have chosen
+# to modify that module to work for both traits and tags but that would have
+# complicated the code, so I chose to maintain two versions with similar
+# functionality instead.
+
+use strict;
+use warnings;
+use Exporter 'import';
+
+our @EXPORT = qw|dbTraitGet dbTraitTree dbTraitEdit dbTraitAdd|;
+
+
+# Options: id what results page sort reverse
+# what: parents childs(n) aliases addedby
+# sort: id name added
+sub dbTraitGet {
+ my $self = shift;
+ my %o = (
+ page => 1,
+ results => 10,
+ what => '',
+ @_,
+ );
+
+ my %where = (
+ $o{id} ? ('t.id = ?' => $o{id}) : (),
+ );
+
+ my @select = (
+ qw|t.id t.meta t.name t.description t.state|,
+ q|extract('epoch' from t.added) as added|,
+ $o{what} =~ /addedby/ ? ('t.addedby', 'u.username') : (),
+ );
+ my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : ();
+
+ my $order = sprintf {
+ id => 't.id %s',
+ name => 't.name %s',
+ added => 't.added %s',
+ }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
+
+ my($r, $np) = $self->dbPage(\%o, q|
+ SELECT !s
+ FROM traits t
+ !s
+ !W
+ ORDER BY !s|,
+ join(', ', @select), join(' ', @join), \%where, $order
+ );
+
+ if(@$r && $o{what} =~ /aliases/) {
+ my %r = map {
+ $_->{aliases} = [];
+ ($_->{id}, $_->{aliases})
+ } @$r;
+
+ push @{$r{$_->{trait}}}, $_->{alias} for (@{$self->dbAll(q|
+ SELECT trait, alias FROM traits_aliases WHERE trait IN(!l)|, [ keys %r ]
+ )});
+ }
+
+ if($o{what} =~ /parents\((\d+)\)/) {
+ $_->{parents} = $self->dbTraitTree($_->{id}, $1, 1) for(@$r);
+ }
+
+ if($o{what} =~ /childs\((\d+)\)/) {
+ $_->{childs} = $self->dbTraitTree($_->{id}, $1) for(@$r);
+ }
+
+ return wantarray ? ($r, $np) : $r;
+}
+
+
+# almost much equivalent to dbTagTree
+sub dbTraitTree {
+ my($self, $id, $lvl, $back) = @_;
+ $lvl ||= 15;
+ my $r = $self->dbAll(q|
+ WITH RECURSIVE traittree(lvl, id, parent, name) AS (
+ SELECT ?::integer, id, 0, name
+ FROM traits
+ !W
+ UNION ALL
+ SELECT tt.lvl-1, t.id, tt.id, t.name
+ FROM traittree tt
+ JOIN traits_parents tp ON !s
+ JOIN traits t ON !s
+ WHERE tt.lvl > 0
+ AND t.state = 2
+ ) SELECT DISTINCT id, parent, name FROM traittree ORDER BY name|, $lvl,
+ $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM traits_parents WHERE trait = id)' => 1, 'state = 2' => 1},
+ !$back ? ('tp.parent = tt.id', 't.id = tp.trait') : ('tp.trait = tt.id', 't.id = tp.parent')
+ );
+ for my $i (@$r) {
+ $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ];
+ }
+ my @r = grep !delete($_->{parent}), @$r;
+ return $id ? $r[0]{'sub'} : \@r;
+}
+
+
+# args: trait id, %options->{ columns in the traits table + parents + aliases }
+sub dbTraitEdit {
+ my($self, $id, %o) = @_;
+
+ $self->dbExec('UPDATE traits !H WHERE id = ?', {
+ $o{upddate} ? ('added = NOW()' => 1) : (),
+ map exists($o{$_}) ? ("$_ = ?" => $o{$_}) : (), qw|name meta description state|
+ }, $id);
+ if($o{aliases}) {
+ $self->dbExec('DELETE FROM traits_aliases WHERE trait = ?', $id);
+ $self->dbExec('INSERT INTO traits_aliases (trait, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
+ }
+ if($o{parents}) {
+ $self->dbExec('DELETE FROM traits_parents WHERE trait = ?', $id);
+ $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
+ }
+}
+
+
+# same args as dbTraitEdit, without the first trait id
+# returns the id of the new trait
+sub dbTraitAdd {
+ my($self, %o) = @_;
+ my $id = $self->dbRow('INSERT INTO traits (name, meta, description, state, addedby) VALUES (!l, ?) RETURNING id',
+ [ map $o{$_}, qw|name meta description state| ], $o{addedby}||$self->authInfo->{id}
+ )->{id};
+ $self->dbExec('INSERT INTO traits_parents (trait, parent) VALUES (?, ?)', $id, $_) for(@{$o{parents}});
+ $self->dbExec('INSERT INTO traits_aliases (trait, alias) VALUES (?, ?)', $id, $_) for (@{$o{aliases}});
+ return $id;
+}
+
+
+1;
+
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 435f0fec..20ade586 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -7,7 +7,7 @@ use TUWF ':html';
use Exporter 'import';
use POSIX 'strftime', 'ceil', 'floor';
use VNDBUtil;
-our @EXPORT = (@VNDBUtil::EXPORT, qw| clearfloat cssicon tagscore mt minage fil_parse fil_serialize |);
+our @EXPORT = (@VNDBUtil::EXPORT, qw| clearfloat cssicon tagscore mt minage fil_parse fil_serialize parenttags childtags |);
# three ways to represent the same information
@@ -105,5 +105,74 @@ sub fil_serialize {
} grep defined($fil->{$_}), keys %$fil;
}
+
+# generates a parent tags/traits listing
+sub parenttags {
+ my($t, $index, $type) = @_;
+ p;
+ my @p = _parenttags(@{$t->{parents}});
+ for my $p (@p ? @p : []) {
+ a href => "/$type", $index; #mt '_tagp_indexlink';
+ for (reverse @$p) {
+ txt ' > ';
+ a href => "/$type$_->{id}", $_->{name};
+ }
+ txt " > $t->{name}";
+ br;
+ }
+ end 'p';
+}
+
+# arg: tag/trait hashref
+# returns: [ [ tag1, tag2, tag3 ], [ tag1, tag2, tag5 ] ]
+sub _parenttags {
+ my @r;
+ for my $t (@_) {
+ for (@{$t->{'sub'}}) {
+ push @r, [ $t, @$_ ] for _parenttags($_);
+ }
+ push @r, [$t] if !@{$t->{'sub'}};
+ }
+ return @r;
+}
+
+
+# a child tags/traits box
+sub childtags {
+ my($self, $title, $type, $t) = @_;
+
+ div class => 'mainbox';
+ h1 $title;
+ ul class => 'tagtree';
+ for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) {
+ li;
+ a href => "/$type$p->{id}", $p->{name};
+ b class => 'grayedout', " ($p->{c_vns})" if $type eq 'g' && $p->{c_vns};
+ end, next if !@{$p->{'sub'}};
+ ul;
+ for (0..$#{$p->{'sub'}}) {
+ last if $_ >= 5 && @{$p->{'sub'}} > 6;
+ li;
+ txt '> ';
+ a href => "/$type$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name};
+ b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $type eq 'g' && $p->{'sub'}[$_]{c_vns};
+ end;
+ }
+ if(@{$p->{'sub'}} > 6) {
+ li;
+ txt '> ';
+ a href => "/$type$p->{id}", style => 'font-style: italic', mt $type eq 'g' ? '_tagp_moretags' : '_traitp_more', @{$p->{'sub'}}-5;
+ end;
+ }
+ end;
+ end 'li';
+ }
+ end 'ul';
+ clearfloat;
+ br;
+ end 'div';
+}
+
+
1;
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index cff5d980..6e48cc26 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -78,18 +78,7 @@ sub tagpage {
a class => 'addnew', href => "/g$tag/add", mt '_tagp_addchild' if $self->authCan('tag') && $t->{state} != 1;
h1 $title;
- p;
- my @p = _parenttags(@{$t->{parents}});
- for my $p (@p ? @p : []) {
- a href => '/g', mt '_tagp_indexlink';
- for (reverse @$p) {
- txt ' > ';
- a href => "/g$_->{id}", $_->{name};
- }
- txt " > $t->{name}";
- br;
- }
- end 'p';
+ parenttags($t, mt('_tagp_indexlink'), 'g');
if($t->{description}) {
p class => 'description';
@@ -110,7 +99,7 @@ sub tagpage {
}
end 'div';
- _childtags($self, $t) if @{$t->{childs}};
+ childtags($self, mt('_tagp_childs'), 'g', $t) if @{$t->{childs}};
if(!$t->{meta} && $t->{state} == 2) {
form action => "/g$t->{id}", 'accept-charset' => 'UTF-8', method => 'get';
@@ -142,57 +131,6 @@ sub tagpage {
}
-# arg: tag hashref
-# returns: [ [ tag1, tag2, tag3 ], [ tag1, tag2, tag5 ] ]
-sub _parenttags {
- my @r;
- for my $t (@_) {
- for (@{$t->{'sub'}}) {
- push @r, [ $t, @$_ ] for _parenttags($_);
- }
- push @r, [$t] if !@{$t->{'sub'}};
- }
- return @r;
-}
-
-
-# used for on both /g and /g+
-sub _childtags {
- my($self, $t, $index) = @_;
-
- div class => 'mainbox';
- h1 mt $index ? '_tagp_tree' : '_tagp_childs';
- ul class => 'tagtree';
- for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) {
- li;
- a href => "/g$p->{id}", $p->{name};
- b class => 'grayedout', " ($p->{c_vns})" if $p->{c_vns};
- end, next if !@{$p->{'sub'}};
- ul;
- for (0..$#{$p->{'sub'}}) {
- last if $_ >= 5 && @{$p->{'sub'}} > 6;
- li;
- txt '> ';
- a href => "/g$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name};
- b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $p->{'sub'}[$_]{c_vns};
- end;
- }
- if(@{$p->{'sub'}} > 6) {
- li;
- txt '> ';
- a href => "/g$p->{id}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{'sub'}}-5;
- end;
- }
- end;
- end 'li';
- }
- end 'ul';
- clearfloat;
- br;
- end 'div';
-}
-
-
sub tagedit {
my($self, $tag, $act) = @_;
@@ -687,7 +625,7 @@ sub tagindex {
end;
my $t = $self->dbTagTree(0, 2);
- _childtags($self, {childs => $t}, 1);
+ childtags($self, mt('_tagp_tree'), 'g', {childs => $t});
table class => 'mainbox threelayout';
Tr;
diff --git a/lib/VNDB/Handler/Traits.pm b/lib/VNDB/Handler/Traits.pm
new file mode 100644
index 00000000..640af81e
--- /dev/null
+++ b/lib/VNDB/Handler/Traits.pm
@@ -0,0 +1,82 @@
+
+package VNDB::Handler::Traits;
+
+use strict;
+use warnings;
+use TUWF ':html';
+use VNDB::Func;
+
+
+TUWF::register(
+ qr{i([1-9]\d*)}, \&traitpage,
+);
+
+
+sub traitpage {
+ my($self, $trait) = @_;
+
+ my $t = $self->dbTraitGet(id => $trait, what => 'parents(0) childs(2) aliases')->[0];
+ return $self->resNotFound if !$t;
+
+ my $title = mt '_traitp_title', $t->{meta}?0:1, $t->{name};
+ $self->htmlHeader(title => $title, noindex => $t->{state} != 2);
+
+ if($t->{state} != 2) {
+ div class => 'mainbox';
+ h1 $title;
+ if($t->{state} == 1) {
+ div class => 'warning';
+ h2 mt '_traitp_del_title';
+ p;
+ lit mt '_traitp_del_msg';
+ end;
+ end;
+ } else {
+ div class => 'notice';
+ h2 mt '_traitp_pending_title';
+ p mt '_traitp_pending_msg';
+ end;
+ }
+ end 'div';
+ }
+
+ div class => 'mainbox';
+ h1 $title;
+
+ parenttags($t, mt('_traitp_indexlink'), 'i');
+
+ if($t->{description}) {
+ p class => 'description';
+ lit bb2html $t->{description};
+ end;
+ }
+ if(@{$t->{aliases}}) {
+ p class => 'center';
+ b mt('_traitp_aliases');
+ br;
+ lit xml_escape($_).'<br />' for (@{$t->{aliases}});
+ end;
+ }
+ end 'div';
+
+ childtags($self, mt('_traitp_childs'), 'i', $t) if @{$t->{childs}};
+
+ # TODO: list of characters
+
+ $self->htmlFooter;
+}
+
+
+1;
+
+__END__
+
+Simple test database:
+
+ INSERT INTO traits (name, description, state, meta, addedby) VALUES
+ ('Blood Type', 'Describes the blood type of the character', 2, true, 2),
+ ('Blood Type O', '', 2, true, 2),
+ ('Blood Type B', '', 2, true, 2);
+ INSERT INTO traits_parents (trait, parent) VALUES (2, 1), (3, 1);
+
+
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index dd2b2ffd..452983e0 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -200,7 +200,7 @@ CREATE TABLE tags (
-- tags_aliases
CREATE TABLE tags_aliases (
alias varchar(250) NOT NULL PRIMARY KEY,
- tag integer NOT NULL,
+ tag integer NOT NULL
);
-- tags_parents
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
new file mode 100644
index 00000000..9819f685
--- /dev/null
+++ b/util/updates/update_2.19.sql
@@ -0,0 +1,26 @@
+-- These changes have not been synced with the /util/sql/ files yet.
+
+
+-- character database -> traits
+
+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)
+);
+