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.

10 files changed, 51 insertions, 52 deletions

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;
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 524b5a04..98847b4c 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -344,8 +344,8 @@ sub edit {
       $_->[1]||=undef for (@vns);
       $frm->{vns} = \@vns;
 
-      my $nrev = $self->dbItemEdit(c => !$copy && $id ? $r->{cid} : undef, %$frm);
-      return $self->resRedirect("/c$nrev->{iid}.$nrev->{rev}", 'post');
+      my $nrev = $self->dbItemEdit(c => !$copy && $id ? ($r->{id}, $r->{rev}) : (undef, undef), %$frm);
+      return $self->resRedirect("/c$nrev->{itemid}.$nrev->{rev}", 'post');
     }
   }
 
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 628034c2..69000b27 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -247,16 +247,16 @@ sub edit {
 
       $frm->{relations} = $relations;
       $frm->{l_wp} = undef if !$frm->{l_wp};
-      my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm);
+      my $nrev = $self->dbItemEdit(p => $pid||undef, $pid ? $p->{rev} : undef, %$frm);
 
       # update reverse relations
       if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) {
         my %old = $pid ? (map { $_->{id} => $_->{relation} } @{$p->{relations}}) : ();
         my %new = map { $_->[1] => $_->[0] } @$relations;
-        _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
+        _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev});
       }
 
-      return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post');
+      return $self->resRedirect("/p$nrev->{itemid}.$nrev->{rev}", 'post');
     }
   }
 
@@ -333,7 +333,7 @@ sub _updreverse {
     my $r = $self->dbProducerGetRev(id => $i, what => 'relations')->[0];
     my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}};
     push @newrel, [ $upd{$i}, $pid ] if $upd{$i};
-    $self->dbItemEdit(p => $r->{cid},
+    $self->dbItemEdit(p => $i, $r->{rev},
       relations => \@newrel,
       editsum => "Reverse relation update caused by revision p$pid.$rev",
       uid => 1,
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 1c97eced..78476db2 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -350,7 +350,7 @@ sub edit {
     }
 
     if(!$frm->{_err}) {
-      my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef,
+      my $nrev = $self->dbItemEdit(r => !$copy && $rid ? ($r->{id}, $r->{rev}) : (undef, undef),
         (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released minage
           notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero ihid ilock|),
         vn        => $new_vn,
@@ -358,7 +358,7 @@ sub edit {
         media     => $media,
       );
 
-      return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post');
+      return $self->resRedirect("/r$nrev->{itemid}.$nrev->{rev}", 'post');
     }
   }
 
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
index f2e855ba..4bf6e63a 100644
--- a/lib/VNDB/Handler/Staff.pm
+++ b/lib/VNDB/Handler/Staff.pm
@@ -191,7 +191,7 @@ sub edit {
     (map { $_ => $s->{$_} } qw|name original gender lang desc l_wp l_site l_twitter l_anidb ihid ilock|),
     primary => $s->{aid},
     aliases => [
-      map +{ aid => $_->{id}, name => $_->{name}, orig => $_->{original} },
+      map +{ aid => $_->{aid}, name => $_->{name}, orig => $_->{original} },
       sort { $a->{name} cmp $b->{name} || $a->{original} cmp $b->{original} } @{$s->{aliases}}
     ],
   );
@@ -221,7 +221,7 @@ sub edit {
     );
 
     if(!$frm->{_err}) {
-      my %old_aliases = $sid ? ( map +($_->{id} => 1), @{$self->dbStaffAliasIds($sid)} ) : ();
+      my %old_aliases = $sid ? ( map +($_->{aid} => 1), @{$self->dbStaffAliasIds($sid)} ) : ();
       $frm->{primary} = 0 unless exists $old_aliases{$frm->{primary}};
 
       # reset aid to zero for newly added aliases.
@@ -233,8 +233,8 @@ sub edit {
       $frm->{desc}   = $self->bbSubstLinks($frm->{desc});
       return $self->resRedirect("/s$sid", 'post') if $sid && !form_compare(\%b4, $frm);
 
-      my $nrev = $self->dbItemEdit ('s' => $sid ? $s->{cid} : undef, %$frm);
-      return $self->resRedirect("/s$nrev->{iid}.$nrev->{rev}", 'post');
+      my $nrev = $self->dbItemEdit(s => $sid ? ($s->{id}, $s->{rev}) : (undef, undef), %$frm);
+      return $self->resRedirect("/s$nrev->{itemid}.$nrev->{rev}", 'post');
     }
   }
 
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index c383be78..07d1a916 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -77,7 +77,7 @@ sub addform {
 sub edit {
   my($self, $vid, $rev, $nosubmit) = @_;
 
-  my $v = $vid && $self->dbVNGet(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0];
+  my $v = $vid && $self->dbVNGetRev(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0];
   return $self->resNotFound if $vid && !$v->{id};
   $rev = undef if !$vid || $v->{lastrev};
 
@@ -176,7 +176,7 @@ sub edit {
       return $self->resRedirect("/v$vid", 'post') if $vid && !form_compare(\%b4, $frm);
 
       # perform the edit/add
-      my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef,
+      my $nrev = $self->dbItemEdit(v => $vid ? ($v->{id}, $v->{rev}) : (undef, undef),
         (map { $_ => $frm->{$_} } qw|title original image alias desc length l_wp l_encubed l_renai editsum img_nsfw ihid ilock credits seiyuu screenshots|),
         anime => [ keys %$anime ],
         relations => $relations,
@@ -186,10 +186,10 @@ sub edit {
       if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) {
         my %old = $vid ? (map +($_->{id} => [ $_->{relation}, $_->{official} ]), @{$v->{relations}}) : ();
         my %new = map +($_->[1] => [ $_->[0], $_->[2] ]), @$relations;
-        _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
+        _updreverse($self, \%old, \%new, $nrev->{itemid}, $nrev->{rev});
       }
 
-      return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
+      return $self->resRedirect("/v$nrev->{itemid}.$nrev->{rev}", 'post');
     }
   }
 
@@ -422,7 +422,7 @@ sub _form {
 
 # Update reverse relations and regenerate relation graph
 # Arguments: %old. %new, vid, rev
-#  %old,%new -> { vid2 => [ relation, official ], .. }
+#  %old,%new -> { vid => [ relation, official ], .. }
 #    from the perspective of vid
 #  rev is of the related edit
 sub _updreverse {
@@ -441,10 +441,10 @@ sub _updreverse {
 
   # edit all related VNs
   for my $i (keys %upd) {
-    my $r = $self->dbVNGet(id => $i, what => 'relations')->[0];
+    my $r = $self->dbVNGetRev(id => $i, what => 'relations')->[0];
     my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id}, $_->{official} ] : (), @{$r->{relations}};
     push @newrel, [ $upd{$i}[0], $vid, $upd{$i}[1] ] if $upd{$i};
-    $self->dbItemEdit(v => $r->{cid},
+    $self->dbItemEdit(v => $r->{id}, $r->{rev},
       relations => \@newrel,
       editsum => "Reverse relation update caused by revision v$vid.$rev",
       uid => 1, # Multi