From 24680d31a5a3433db5fd0140d9cb80b371659342 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Tue, 15 Feb 2011 16:13:11 +0100 Subject: chardb: Added char entry tables and updated the basic revision framework The Perl code and SQL-revisioning code only handles the name, original, alias and desc fields at the moment. There is a basic /i+ and /i+.+ page for testing, which should have all the functionality required for the revisioning framework. --- data/docs/12 | 5 ++++ data/docs/index | 1 + data/lang.txt | 20 ++++++------- data/notes/chardb | 8 ++++- lib/VNDB/DB/Chars.pm | 49 +++++++++++++++++++++++++++++++ lib/VNDB/DB/Misc.pm | 8 ++--- lib/VNDB/Handler/Chars.pm | 54 ++++++++++++++++++++++++++++++++++ lib/VNDB/Handler/Misc.pm | 3 +- lib/VNDB/Util/CommonHTML.pm | 19 +++++++----- util/sql/func.sql | 44 ++++++++++++++++++++++++++++ util/updates/update_2.19.sql | 70 +++++++++++++++++++++++++++++++++++++++++++- 11 files changed, 256 insertions(+), 25 deletions(-) create mode 100644 data/docs/12 create mode 100644 lib/VNDB/DB/Chars.pm create mode 100644 lib/VNDB/Handler/Chars.pm diff --git a/data/docs/12 b/data/docs/12 new file mode 100644 index 00000000..50bd98bc --- /dev/null +++ b/data/docs/12 @@ -0,0 +1,5 @@ +:TITLE:Adding/Editing Characters +:INC:index + +to-do + diff --git a/data/docs/index b/data/docs/index index 02c8f769..0c30f664 100644 --- a/data/docs/index +++ b/data/docs/index @@ -4,6 +4,7 @@
  • Visual Novels
  • Releases
  • Producers
    • +
  • Characters
  • Tags
  • About VNDB
  • Discussion board
    • diff --git a/data/lang.txt b/data/lang.txt index 3d488d2a..80838884 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -7687,11 +7687,11 @@ hu : Nézz bele a [url,_1,szerkesztési előzményekbe], hogy lásd milyen vált nl : Browse de [url,_1,geschiedenis] van dit item voor wijzigingen gerelateerd aan wat je wil doen. :_editmsg_msg_search -en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer]. -ru : [url,_1,Воспользуйтесь поиском], ведь вполне возможно, что у нас уже есть информация об [index,_2,этой новелле,этом выпуске,этой компании]. -cs : [url,_1,Prohledejte databázi], zda již nemáme informaci o [index,_2,této vizuální novele,tomto vydání,tomto producentovi]. -hu : [url,_1,Nézd át az adatbázist], hogy van-e már információ erről a [index,_2,visual novelről, kiadásról, készítőről]. -nl : [url,_1,Doorzoek de database] om te kijken of we al informatie hebben over deze [index,_2,visual novel,uitgave,producent]. +en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer,character]. +ru*: [url,_1,Воспользуйтесь поиском], ведь вполне возможно, что у нас уже есть информация об [index,_2,этой новелле,этом выпуске,этой компании]. +cs*: [url,_1,Prohledejte databázi], zda již nemáme informaci o [index,_2,této vizuální novele,tomto vydání,tomto producentovi]. +hu*: [url,_1,Nézd át az adatbázist], hogy van-e már információ erről a [index,_2,visual novelről, kiadásról, készítőről]. +nl : [url,_1,Doorzoek de database] om te kijken of we al informatie hebben over [index,_2,deze visual novel,deze uitgave,deze producent,dit kenmerk]. :_editmsg_revert_title en : Reverting @@ -7701,15 +7701,15 @@ hu : Visszaállítás nl : Terugdraaien :_editmsg_revert_msg -en : You are editing an old revision of this [index,_1,visual novel,release,producer]. +en : You are editing an old revision of this [index,_1,visual novel,release,producer,character]. If you save it, all changes made after this revision will be reverted! -ru : Вы правите старую редакцию страницы [index,_1,новеллы,выпуска,компании]. +ru*: Вы правите старую редакцию страницы [index,_1,новеллы,выпуска,компании]. Если вы сохраните её, все изменения, сделанные после этой правки будут утеряны! -cs : Editujete starší verzi [index,_1,této vizuální novely,tohoto vydání,tohoto producenta]. +cs*: Editujete starší verzi [index,_1,této vizuální novely,tohoto vydání,tohoto producenta]. Pokud ji uložíte, všechny změny provedené po této revizi budou vráceny! -hu : Te egy régi átjavítását szerkeszted ennek a [index,_1,visual novelnek, kiadásnak, készítőnek]. +hu*: Te egy régi átjavítását szerkeszted ennek a [index,_1,visual novelnek, kiadásnak, készítőnek]. Ha lemented, akkor minden szerkesztés amit ezután a javítás után végeztek megsemmisül! -nl : Je wijzigd een verouderde versie van deze [index,_1,visual novel,uitgave,producent]. +nl : Je wijzigd een verouderde versie van [index,_1,deze visual novel,deze uitgave,deze producent,dit kenmerk]. Als je dit opslaat, zullen alle wijzigingen na deze revisie teruggedraait worden. diff --git a/data/notes/chardb b/data/notes/chardb index 0a42bcd8..37dfa80d 100644 --- a/data/notes/chardb +++ b/data/notes/chardb @@ -1,6 +1,6 @@ Character Database -Last modified: 2011-02-13 +Last modified: 2011-02-14 Status: Draft / incomplete @@ -168,6 +168,11 @@ Status: Draft / incomplete 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): @@ -356,6 +361,7 @@ User interface considerations: The SQL schema: + (outdated, see /util/updates/update_2.19.sql instead) CREATE TABLE traits ( id SERIAL PRIMARY KEY, diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm new file mode 100644 index 00000000..a1866fcf --- /dev/null +++ b/lib/VNDB/DB/Chars.pm @@ -0,0 +1,49 @@ + +package VNDB::DB::Chars; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw|dbCharGet|; + + +# options: id rev what results page +# what: extended changes +sub dbCharGet { + my $self = shift; + my %o = ( + page => 1, + results => 10, + what => '', + @_ + ); + + my %where = ( + !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (), + $o{id} ? ( 'c.id = ?' => $o{id} ) : (), + $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (), + ); + + my @select = qw|c.id cr.name cr.original|; + push @select, qw|c.hidden c.locked cr.alias cr.desc| if $o{what} =~ /extended/; + push @select, qw|h.requester h.comments c.latest u.username h.rev h.ihid h.ilock|, "extract('epoch' from h.added) as added", 'cr.id AS cid' if $o{what} =~ /changes/; + + my @join; + push @join, $o{rev} ? 'JOIN chars c ON c.id = cr.cid' : 'JOIN chars c ON cr.id = c.latest'; + push @join, 'JOIN changes h ON h.id = cr.id' if $o{what} =~ /changes/ || $o{rev}; + push @join, 'JOIN users u ON u.id = h.requester' if $o{what} =~ /changes/; + + my($r, $np) = $self->dbPage(\%o, q| + SELECT !s + FROM chars_rev cr + !s + !W|, + join(', ', @select), join(' ', @join), \%where); + + return wantarray ? ($r, $np) : $r; +} + + +1; + diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index f9eb459a..5fdad302 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -27,7 +27,7 @@ sub dbStats { sub dbItemEdit { my($self, $type, $oid, %o) = @_; - my $fun = {qw|v vn r release p producer|}->{$type}; + my $fun = {qw|v vn r release p producer c char|}->{$type}; $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); $self->dbExec('UPDATE edit_revision !H', { 'requester = ?' => $o{uid}||$self->authInfo->{id}, @@ -59,10 +59,10 @@ sub dbRevisionGet { $o{what} ||= ''; $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid}; - my %tables = qw|v vn r releases p producers|; + my %tables = qw|v vn r releases p producers c chars|; # what types should we join? my @types = ( - !$o{type} ? ('v', 'r', 'p') : + !$o{type} ? ('v', 'r', 'p', 'c') : $o{type} ne 'v' ? $o{type} : $o{releases} ? ('v', 'r') : 'v' ); @@ -102,7 +102,7 @@ sub dbRevisionGet { $o{what} =~ /user/ ? 'u.username' : (), $o{what} =~ /item/ ? ( 'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid', - 'COALESCE('.join(', ', map $_ eq 'p' ? 'pr.name' : "${_}r.title", @types).') AS ititle', + 'COALESCE('.join(', ', map /[pc]/ ? "${_}r.name" : "${_}r.title", @types).') AS ititle', 'COALESCE('.join(', ', map "${_}r.original", @types).') AS ioriginal', ) : (), ); diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm new file mode 100644 index 00000000..2ad1daa3 --- /dev/null +++ b/lib/VNDB/Handler/Chars.pm @@ -0,0 +1,54 @@ + +package VNDB::Handler::Chars; + +use strict; +use warnings; +use TUWF ':html'; +use VNDB::Func; + + +TUWF::register( + qr{c([1-9]\d*)(?:\.([1-9]\d*))?} => \&page, +); + + +sub page { + my($self, $id, $rev) = @_; + + my $r = $self->dbCharGet( + id => $id, + what => 'extended'.($rev ? ' changes' : ''), + $rev ? ( rev => $rev ) : () + )->[0]; + return $self->resNotFound if !$r->{id}; + + $self->htmlHeader(title => $r->{name}); + $self->htmlMainTabs(c => $r); + return if $self->htmlHiddenMessage('c', $r); + + if($rev) { + my $prev = $rev && $rev > 1 && $self->dbCharGet(id => $id, rev => $rev-1, what => 'changes extended')->[0]; + $self->htmlRevision('c', $prev, $r, + [ name => diff => 1 ], + [ original => diff => 1 ], + [ alias => diff => qr/[ ,\n\.]/ ], + [ desc => diff => qr/[ ,\n\.]/ ], + ); + } + + div class => 'mainbox'; + $self->htmlItemMessage('c', $r); + h1 $r->{name}; + h2 class => 'alttitle', $r->{original} if $r->{original}; + if($r->{desc}) { + p class => 'description'; + lit bb2html($r->{desc}); + end; + } + end; + $self->htmlFooter; +} + + +1; + diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 062e3ae4..8e449153 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -11,7 +11,7 @@ use POSIX 'strftime'; TUWF::register( qr{}, \&homepage, - qr{(?:([upvr])([1-9]\d*)/)?hist}, \&history, + qr{(?:([upvrc])([1-9]\d*)/)?hist}, \&history, qr{d([1-9]\d*)}, \&docpage, qr{setlang}, \&setlang, qr{nospam}, \&nospam, @@ -206,6 +206,7 @@ sub history { my $obj = $type eq 'u' ? $self->dbUserGet(uid => $id, what => 'hide_list')->[0] : $type eq 'p' ? $self->dbProducerGet(id => $id)->[0] : $type eq 'r' ? $self->dbReleaseGet(id => $id)->[0] : + $type eq 'c' ? $self->dbCharGet(id => $id)->[0] : $type eq 'v' ? $self->dbVNGet(id => $id)->[0] : undef; my $title = mt $type ? ('_hist_title_item', $obj->{title} || $obj->{name} || $obj->{username}) : '_hist_title'; return $self->resNotFound if $type && !$obj->{id}; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index ba03aeac..0d794913 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -17,7 +17,7 @@ our @EXPORT = qw| # generates the "main tabs". These are the commonly used tabs for # 'objects', i.e. VN/producer/release entries and users -# Arguments: u/v/r/p/g/i, object, currently selected item (empty=main) +# Arguments: u/v/r/p/g/i/c, object, currently selected item (empty=main) sub htmlMainTabs { my($self, $type, $obj, $sel) = @_; $sel ||= ''; @@ -26,7 +26,7 @@ sub htmlMainTabs { return if $type eq 'g' && !$self->authCan('tagmod'); ul class => 'maintabs'; - if($type =~ /[uvrp]/) { + if($type =~ /[uvrpc]/) { li $sel eq 'hist' ? (class => 'tabselected') : (); a href => "/$id/hist", mt '_mtabs_hist'; end; @@ -73,6 +73,7 @@ sub htmlMainTabs { if( $type eq 'u' && ($self->authInfo->{id} && $obj->{id} == $self->authInfo->{id} || $self->authCan('usermod')) || $type =~ /[vrp]/ && $self->authCan('edit') && (!$obj->{locked} || $self->authCan('lock')) && (!$obj->{hidden} || $self->authCan('del')) + || $type eq 'c' && $self->authCan('charedit') && (!$obj->{locked} || $self->authCan('lock')) && (!$obj->{hidden} || $self->authCan('del')) || $type =~ /[gi]/ && $self->authCan('tagmod') ) { li $sel eq 'edit' ? (class => 'tabselected') : (); @@ -120,15 +121,16 @@ sub htmlDenied { # Generates message saying that the current item has been deleted, -# Arguments: [pvr], obj +# Arguments: [pvrc], obj # Returns 1 if the use doesn't have access to the page, 0 otherwise sub htmlHiddenMessage { my($self, $type, $obj) = @_; return 0 if !$obj->{hidden}; - my $board = $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; + my $board = $type eq 'c' ? 'db' : $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; # fetch edit summary (not present in $obj because the changes aren't fetched) my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments} : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments} + : $type eq 'c' ? $self->dbCharGet(id => $obj->{id}, what => 'changes')->[0]{comments} : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments}; div class => 'mainbox'; h1 $obj->{title}||$obj->{name}; @@ -147,7 +149,7 @@ sub htmlHiddenMessage { # Shows a revision, including diff if there is a previous revision. -# Arguments: v|p|r, old revision, new revision, @fields +# Arguments: v|p|r|c, old revision, new revision, @fields # Where @fields is a list of fields as arrayrefs with: # [ shortname, displayname, %options ], # Where %options: @@ -266,8 +268,8 @@ sub revdiff { # Arguments: v/r/p, obj sub htmlEditMessage { my($self, $type, $obj, $title, $copy) = @_; - my $num = {v => 0, r => 1, p => 2}->{$type}; - my $guidelines = {v => 2, r => 3, p => 4}->{$type}; + my $num = {v => 0, r => 1, p => 2, c => 3}->{$type}; + my $guidelines = {v => 2, r => 3, p => 4, c => 12}->{$type}; div class => 'mainbox'; h1 $title; @@ -303,9 +305,10 @@ sub htmlEditMessage { # Generates a small message when the user can't edit the item, # or the item is locked. -# Arguments: v/r/p, obj +# Arguments: v/r/p/c, obj sub htmlItemMessage { my($self, $type, $obj) = @_; + # $type isn't being used at all... oh well. if($obj->{locked}) { p class => 'locked', mt '_itemmsg_locked'; diff --git a/util/sql/func.sql b/util/sql/func.sql index 74f08147..4fc95165 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -164,6 +164,7 @@ BEGIN INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t, ( SELECT vid FROM vn_rev WHERE id = i UNION SELECT rid FROM releases_rev WHERE id = i + UNION SELECT cid FROM chars_rev WHERE id = i UNION SELECT pid FROM producers_rev WHERE id = i), COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) @@ -189,6 +190,7 @@ BEGIN JOIN ( SELECT id FROM vn_rev WHERE t = 'v' AND vid = i UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i + UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i ) x(id) ON x.id = c.id ORDER BY c.id DESC LIMIT 1; @@ -204,6 +206,7 @@ BEGIN WHEN 'v' THEN INSERT INTO vn (latest) VALUES (0) RETURNING id INTO r.iid; WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid; WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid; + WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid; END CASE; ELSE r.iid := i; @@ -370,6 +373,46 @@ $$ LANGUAGE plpgsql; +-- PLACEHOLDERS, not complete yet + +CREATE OR REPLACE FUNCTION edit_char_init(cid integer) RETURNS void AS $$ +BEGIN + BEGIN + CREATE TEMPORARY TABLE edit_char (LIKE chars_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_char DROP COLUMN id; + ALTER TABLE edit_char DROP COLUMN cid; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_char; + END; + PERFORM edit_revtable('c', cid); + -- new char + IF cid IS NULL THEN + INSERT INTO edit_char DEFAULT VALUES; + -- load revision + ELSE + INSERT INTO edit_char SELECT name, original, alias, image, "desc" FROM chars_rev WHERE id = cid; + END IF; +END; +$$ LANGUAGE plpgsql; + + + +CREATE OR REPLACE FUNCTION edit_char_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +BEGIN + IF (SELECT COUNT(*) FROM edit_char) <> 1 THEN + RAISE 'edit_char must have exactly one row!'; + END IF; + SELECT INTO r * FROM edit_commit(); + INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc" FROM edit_char; + UPDATE chars SET latest = r.cid WHERE id = r.iid; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + + ---------------------------------------------------------- @@ -819,3 +862,4 @@ BEGIN END; $$ LANGUAGE plpgsql; + diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql index 4bb7f547..43337ef9 100644 --- a/util/updates/update_2.19.sql +++ b/util/updates/update_2.19.sql @@ -24,8 +24,76 @@ CREATE TABLE traits_parents ( PRIMARY KEY(trait, parent) ); +CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); + + + +-- character database -> chars + +CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears'); + +CREATE TABLE chars ( + id SERIAL PRIMARY KEY, + latest integer NOT NULL DEFAULT 0, + 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, + s_waist smallint NOT NULL DEFAULT 0, + s_hip smallint NOT NULL DEFAULT 0, + b_month smallint NOT NULL DEFAULT 0, + b_day smallint NOT NULL DEFAULT 0, + height smallint NOT NULL DEFAULT 0, + weight smallint NOT NULL DEFAULT 0, + main integer REFERENCES chars (id), + main_spoil boolean NOT NULL DEFAULT false +); +ALTER TABLE chars ADD FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED; + +CREATE TABLE chars_traits ( + cid integer NOT NULL REFERENCES chars_rev (id), + tid integer NOT NULL REFERENCES traits (id), + spoil smallint NOT NULL DEFAULT 0, + 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), + spoil boolean NOT NULL DEFAULT false, + role char_role NOT NULL DEFAULT 'main', + PRIMARY KEY(cid, vid, rid) +); + + + +-- allow characters to be versioned using the changes table + +CREATE TYPE dbentry_type_tmp AS ENUM ('v', 'r', 'p', 'c'); +ALTER TABLE changes ALTER COLUMN "type" TYPE dbentry_type_tmp USING "type"::text::dbentry_type_tmp; +DROP FUNCTION edit_revtable(dbentry_type, integer); +DROP TYPE dbentry_type; +ALTER TYPE dbentry_type_tmp RENAME TO dbentry_type; + + +-- load the updated functions + \i util/sql/func.sql -CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +-- test +--SELECT edit_char_init(null); +--UPDATE edit_revision SET comments = 'New test entry', requester = 2, ip = '0.0.0.0'; +--UPDATE edit_char SET name = 'Phorni', original = 'フォーニ', "desc" = 'Sprite of Music'; +--SELECT edit_char_commit(); -- cgit v1.2.3