From 9cf3eef0e0a7bde10a0e1bb8c94c38b058e5121d Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 21 Oct 2015 02:19:15 +0200 Subject: SQL: Fix editing + func.sql + triggers.sql + autocreate editing funcs This changes quite a bit to the way the editing functions work. Because these functions are very repetitive and it's easy to keep things out of sync, I created a script to generate them automatically. I had to rename a few function and table names for consistency to make this work. Since database entries don't have a 'latest' column anymore, and since the order in which tables are updated doesn't have to be fixed, I dropped many of the SQL triggers and replaced them with a edit_committed() function which is called from edit_*_commit() and checks for stuff to be done. Don't forget to run 'make' before importing the update script. --- lib/VNDB/DB/Chars.pm | 10 +++++----- lib/VNDB/DB/Misc.pm | 13 ++++++------- lib/VNDB/DB/Producers.pm | 6 +++--- lib/VNDB/DB/Releases.pm | 22 +++++++++++----------- lib/VNDB/DB/Staff.pm | 14 +++++++------- 5 files changed, 32 insertions(+), 33 deletions(-) (limited to 'lib/VNDB/DB') diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index 1eaa732f..db9ae93b 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -165,15 +165,15 @@ sub dbCharRevisionInsert { my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), qw|name original alias desc image b_month b_day s_bust s_waist s_hip height weight bloodt gender main main_spoil|; - $self->dbExec('UPDATE edit_char !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_chars !H', \%set) if keys %set; if($o->{traits}) { - $self->dbExec('DELETE FROM edit_char_traits'); - $self->dbExec('INSERT INTO edit_char_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}}); + $self->dbExec('DELETE FROM edit_chars_traits'); + $self->dbExec('INSERT INTO edit_chars_traits (tid, spoil) VALUES (?,?)', $_->[0],$_->[1]) for (@{$o->{traits}}); } if($o->{vns}) { - $self->dbExec('DELETE FROM edit_char_vns'); - $self->dbExec('INSERT INTO edit_char_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}}); + $self->dbExec('DELETE FROM edit_chars_vns'); + $self->dbExec('INSERT INTO edit_chars_vns (vid, rid, spoil, role) VALUES(!l)', $_) for (@{$o->{vns}}); } } diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index efa0ab34..7c3a6e36 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -21,14 +21,13 @@ sub dbStats { # Inserts a new revision into the database -# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert } -# revision id = changes.id of the revision this edit is based on, undef to create a new DB item -# Returns: { iid, cid, rev } +# Arguments: type [vrp], itemid, rev, %options->{ editsum uid ihid ilock + db[item]RevisionInsert } +# rev = changes.rev of the revision this edit is based on, undef to create a new DB item +# Returns: { itemid, chid, rev } sub dbItemEdit { - my($self, $type, $oid, %o) = @_; + my($self, $type, $itemid, $rev, %o) = @_; - my $fun = {qw|v vn r release p producer c char s staff|}->{$type}; - $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); + $self->dbExec('SELECT edit_!s_init(?, ?)', $type, $itemid, $rev); $self->dbExec('UPDATE edit_revision !H', { 'requester = ?' => $o{uid}||$self->authInfo->{id}, 'ip = ?' => $self->reqIP, @@ -43,7 +42,7 @@ sub dbItemEdit { $self->dbCharRevisionInsert( \%o) if $type eq 'c'; $self->dbStaffRevisionInsert( \%o) if $type eq 's'; - return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun); + return $self->dbRow('SELECT * FROM edit_!s_commit()', $type); } diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index a9636282..929e333b 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -110,13 +110,13 @@ sub dbProducerRevisionInsert { my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), qw|name original website l_wp type lang desc alias|; - $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_producers !H', \%set) if keys %set; if($o->{relations}) { - $self->dbExec('DELETE FROM edit_producer_relations'); + $self->dbExec('DELETE FROM edit_producers_relations'); my $q = join ',', map '(?,?)', @{$o->{relations}}; my @q = map +($_->[1], $_->[0]), @{$o->{relations}}; - $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_producers_relations (pid, relation) VALUES $q", @q) if @q; } } diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index 0a8f5363..eafb84c0 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -203,38 +203,38 @@ sub dbReleaseRevisionInsert { my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (), qw|title original gtin catalog website released notes minage type patch resolution voiced freeware doujin ani_story ani_ero|; - $self->dbExec('UPDATE edit_release !H', \%set) if keys %set; + $self->dbExec('UPDATE edit_releases !H', \%set) if keys %set; if($o->{languages}) { - $self->dbExec('DELETE FROM edit_release_lang'); + $self->dbExec('DELETE FROM edit_releases_lang'); my $q = join ',', map '(?)', @{$o->{languages}}; - $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}}; + $self->dbExec("INSERT INTO edit_releases_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}}; } if($o->{producers}) { - $self->dbExec('DELETE FROM edit_release_producers'); + $self->dbExec('DELETE FROM edit_releases_producers'); my $q = join ',', map '(?,?,?)', @{$o->{producers}}; my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}}; - $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_releases_producers (pid, developer, publisher) VALUES $q", @q) if @q; } if($o->{platforms}) { - $self->dbExec('DELETE FROM edit_release_platforms'); + $self->dbExec('DELETE FROM edit_releases_platforms'); my $q = join ',', map '(?)', @{$o->{platforms}}; - $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}}; + $self->dbExec("INSERT INTO edit_releases_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}}; } if($o->{vn}) { - $self->dbExec('DELETE FROM edit_release_vn'); + $self->dbExec('DELETE FROM edit_releases_vn'); my $q = join ',', map '(?)', @{$o->{vn}}; - $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}}; + $self->dbExec("INSERT INTO edit_releases_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}}; } if($o->{media}) { - $self->dbExec('DELETE FROM edit_release_media'); + $self->dbExec('DELETE FROM edit_releases_media'); my $q = join ',', map '(?,?)', @{$o->{media}}; my @q = map +($_->[0], $_->[1]), @{$o->{media}}; - $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q; + $self->dbExec("INSERT INTO edit_releases_media (medium, qty) VALUES $q", @q) if @q; } } diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index 8c6badd6..fb472c72 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -152,15 +152,15 @@ sub _enrich { sub dbStaffRevisionInsert { my($self, $o) = @_; - $self->dbExec('DELETE FROM edit_staff_aliases'); + $self->dbExec('DELETE FROM edit_staff_alias'); if($o->{aid}) { $self->dbExec(q| - INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|, + INSERT INTO edit_staff_alias (aid, name, original) VALUES (?, ?, ?)|, $o->{aid}, $o->{name}, $o->{original}); } else { $o->{aid} = $self->dbRow(q| - INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|, - $o->{name}, $o->{original})->{id}; + INSERT INTO edit_staff_alias (name, original) VALUES (?, ?) RETURNING aid|, + $o->{name}, $o->{original})->{aid}; } my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), @@ -168,9 +168,9 @@ sub dbStaffRevisionInsert { $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff; for my $a (@{$o->{aliases}}) { if($a->{aid}) { - $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]); + $self->dbExec('INSERT INTO edit_staff_alias (aid, name, original) VALUES (!l)', [ @{$a}{qw|aid name orig|} ]); } else { - $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)', $a->{name}, $a->{orig}); + $self->dbExec('INSERT INTO edit_staff_alias (name, original) VALUES (?, ?)', $a->{name}, $a->{orig}); } } } @@ -183,7 +183,7 @@ sub dbStaffAliasIds { SELECT DISTINCT sa.aid FROM changes c JOIN staff_alias_hist sa ON sa.chid = c.id - WHERE c.type = \'s\' AND c.itemid = ?|, $sid); + WHERE c.type = 's' AND c.itemid = ?|, $sid); } 1; -- cgit v1.2.3