SQL: Revision insertion abstraction for release entries

Also added a little sanity checking on the edit_(vn|release) table,
and added a default value for releases_rev.released.

5 files changed, 110 insertions, 53 deletions

diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index b1f79705..4b14d485 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -27,17 +27,16 @@ sub dbStats {
 sub dbItemEdit {
   my($self, $type, $oid, %o) = @_;
 
-  die "Only VNs are supported at this moment!" if $type ne 'v';
-  $self->dbExec('SELECT edit_!s_init(?)',
-    {qw|v vn r releases p producers|}->{$type}, $oid);
+  my $fun = {qw|v vn r release p producer|}->{$type};
+  $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
   $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?',
     $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum});
 
   $self->dbVNRevisionInsert(      \%o) if $type eq 'v';
   #$self->dbProducerRevisionInsert(\%o) if $type eq 'p';
-  #$self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
+  $self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
 
-  return $self->dbRow('SELECT * FROM edit_vn_commit()');
+  return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
 }
 
 
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 161242bc..a2f62a63 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -156,47 +156,47 @@ sub dbReleaseGet {
 }
 
 
-# inserts a release revision, called from dbItemEdit() or dbItemAdd()
-# Arguments: global revision, item id, { columns in releases_rev + languages + vn + producers + media + platforms }
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in releases_rev + languages + vn + producers + media + platforms }
 sub dbReleaseRevisionInsert {
-  my($self, $cid, $rid, $o) = @_;
-
-  $self->dbExec(q|
-    INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, website, released,
-        notes, minage, type, patch, resolution, voiced, freeware, doujin, ani_story, ani_ero)
-      VALUES (!l)|,
-    [ $cid, $rid, @$o{qw| title original gtin catalog website released
-        notes minage type patch resolution voiced freeware doujin ani_story ani_ero|} ]);
-
-  $self->dbExec(q|
-    INSERT INTO releases_lang (rid, lang)
-      VALUES (?, ?)|,
-    $cid, $_
-  ) for (@{$o->{languages}});
-
-  $self->dbExec(q|
-    INSERT INTO releases_producers (rid, pid, developer, publisher)
-      VALUES (?, ?, ?, ?)|,
-    $cid, $_->[0], $_->[1]?1:0, $_->[2]?1:0
-  ) for (@{$o->{producers}});
-
-  $self->dbExec(q|
-    INSERT INTO releases_platforms (rid, platform)
-      VALUES (?, ?)|,
-    $cid, $_
-  ) for (@{$o->{platforms}});
-
-  $self->dbExec(q|
-    INSERT INTO releases_vn (rid, vid)
-      VALUES (?, ?)|,
-    $cid, $_
-  ) for (@{$o->{vn}});
-
-  $self->dbExec(q|
-    INSERT INTO releases_media (rid, medium, qty)
-      VALUES (?, ?, ?)|,
-    $cid, $_->[0], $_->[1]
-  ) for (@{$o->{media}});
+  my($self, $o) = @_;
+
+  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;
+
+  if($o->{languages}) {
+    $self->dbExec('DELETE FROM edit_release_lang');
+    my $q = join ',', map '(?)', @{$o->{languages}};
+    $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
+  }
+
+  if($o->{producers}) {
+    $self->dbExec('DELETE FROM edit_release_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;
+  }
+
+  if($o->{platforms}) {
+    $self->dbExec('DELETE FROM edit_release_platforms');
+    my $q = join ',', map '(?)', @{$o->{platforms}};
+    $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
+  }
+
+  if($o->{vn}) {
+    $self->dbExec('DELETE FROM edit_release_vn');
+    my $q = join ',', map '(?)', @{$o->{vn}};
+    $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
+  }
+
+  if($o->{media}) {
+    $self->dbExec('DELETE FROM edit_release_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;
+  }
 }
 
 
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 724ecf4e..7c365af7 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -348,7 +348,7 @@ sub edit {
     }
 
     if(!$frm->{_err}) {
-      my %opts = (
+      my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef,
         (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released
           notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|),
         minage    => $frm->{minage} < 0 ? undef : $frm->{minage},
@@ -357,11 +357,7 @@ sub edit {
         media     => $media,
       );
 
-      $rev = 1;
-      ($rev) = $self->dbItemEdit(r => $rid, %opts) if !$copy && $rid;
-      ($rid) = $self->dbItemAdd(r => %opts) if $copy || !$rid;
-
-      return $self->resRedirect("/r$rid.$rev", 'post');
+      return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post');
     }
   }
 
diff --git a/util/dump.sql b/util/dump.sql
index 4c6a4648..dc910a7f 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -127,7 +127,7 @@ CREATE TABLE releases_rev (
   original varchar(250) NOT NULL DEFAULT '',
   type release_type NOT NULL DEFAULT 'complete',
   website varchar(250) NOT NULL DEFAULT '',
-  released integer NOT NULL,
+  released integer NOT NULL DEFAULT 0,
   notes text NOT NULL DEFAULT '',
   minage smallint,
   gtin bigint NOT NULL DEFAULT 0,
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
index d955a77b..4ae4160b 100644
--- a/util/updates/update_2.10.sql
+++ b/util/updates/update_2.10.sql
@@ -191,7 +191,8 @@ UPDATE users SET
   ), 0);
 
 
-
+-- set default on releases_rev.released, required for the revision insertion abstraction
+ALTER TABLE releases_rev ALTER COLUMN released SET DEFAULT 0;
 
 -- revision insertion abstraction
 -- IMPORTANT: these functions will need to be updated on each change in the DB structure
@@ -289,6 +290,9 @@ CREATE OR REPLACE FUNCTION edit_vn_commit() RETURNS edit_rettype AS $$
 DECLARE
   r edit_rettype;
 BEGIN
+  IF (SELECT COUNT(*) FROM edit_vn) <> 1 THEN
+    RAISE 'edit_vn must have exactly one row!';
+  END IF;
   SELECT INTO r * FROM edit_commit();
   INSERT INTO vn_rev SELECT r.cid, r.iid, title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM edit_vn;
   INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime;
@@ -300,3 +304,61 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+
+CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$
+BEGIN
+  -- temp. tables
+  CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release DROP COLUMN id;
+  ALTER TABLE edit_release DROP COLUMN rid;
+  CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release_lang DROP COLUMN rid;
+  CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release_media DROP COLUMN rid;
+  CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release_platforms DROP COLUMN rid;
+  CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release_producers DROP COLUMN rid;
+  CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+  ALTER TABLE edit_release_vn DROP COLUMN rid;
+  -- new release
+  IF cid IS NULL THEN
+    PERFORM edit_revtable('r', NULL);
+    INSERT INTO edit_release DEFAULT VALUES;
+  -- load revision
+  ELSE
+    PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid));
+    INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid;
+    INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid;
+    INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid;
+    INSERT INTO edit_release_platforms SELECT platform FROM releases_platforms WHERE rid = cid;
+    INSERT INTO edit_release_producers SELECT pid, developer, publisher FROM releases_producers WHERE rid = cid;
+    INSERT INTO edit_release_vn SELECT vid FROM releases_vn WHERE rid = cid;
+  END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$
+DECLARE
+  r edit_rettype;
+BEGIN
+  IF (SELECT COUNT(*) FROM edit_release) <> 1 THEN
+    RAISE 'edit_release must have exactly one row!';
+  ELSIF NOT EXISTS(SELECT 1 FROM edit_release_vn) THEN
+    RAISE 'edit_release_vn must have at least one row!';
+  END IF;
+  SELECT INTO r * FROM edit_commit();
+  INSERT INTO releases_rev SELECT r.cid, r.iid, title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM edit_release;
+  INSERT INTO releases_lang SELECT r.cid, lang FROM edit_release_lang;
+  INSERT INTO releases_media SELECT r.cid, medium, qty FROM edit_release_media;
+  INSERT INTO releases_platforms SELECT r.cid, platform FROM edit_release_platforms;
+  INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers;
+  INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn;
+  UPDATE releases SET latest = r.cid WHERE id = r.iid;
+  DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
+  RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+