create-v4, report, update: Keep track of which box each disc is in.

[dvddb] / update

diff --git a/update b/update
index 79c6a35..7fdef51 100755 (executable)
--- a/update
+++ b/update
@@ -25,14 +25,13 @@ my $st_update_set = $DB->prepare
 my $st_delete_set = $DB->prepare("DELETE FROM dvd_set WHERE id = ?");
 
 my $st_get_discs = $DB->prepare
-  ("SELECT disc, path FROM dvd_disc
+  ("SELECT disc, path, box FROM dvd_disc
     WHERE set_id = ? AND ? <= disc AND disc < ?
     ORDER BY disc");
 my $st_add_disc = $DB->prepare
-  ("INSERT INTO dvd_disc (set_id, disc, path) VALUES (?, ?, ?)");
+  ("INSERT INTO dvd_disc (set_id, disc, path, box) VALUES (?, ?, ?, ?)");
 my $st_update_disc = $DB->prepare
-  ("UPDATE dvd_disc SET path = ?, disc_id = NULL
-    WHERE set_id = ? AND disc = ?");
+  ("UPDATE dvd_disc SET path = ?, box = ? WHERE set_id = ? AND disc = ?");
 my $st_delete_disc_range = $DB->prepare
   ("DELETE FROM dvd_disc WHERE set_id = ? AND ? <= disc AND disc < ?");
 my $st_delete_discs = $DB->prepare
@@ -40,6 +39,7 @@ my $st_delete_discs = $DB->prepare
 
 my ($id, $name, $ndisc) = (undef, undef, -1);
 my @path;
+my %box;
 
 sub flush_set () {
   defined $id or return;
@@ -71,11 +71,14 @@ sub flush_set () {
     my $i = 0;
     DISC: for (;;) {
       my @r = $st_get_discs->fetchrow_array; last DISC unless @r;
-      my ($disc, $old_path) = @r;
+      my ($disc, $old_path, $old_box) = @r; $old_box //= "#nil";
       $disc == $i or die "unexpected disc number";
-      my $path = $path[$i++];
-      if (defined $path && (!defined $old_path || $path ne $old_path))
-       { $st_update_disc->execute($path, $id, $disc); }
+      my $path = $path[$i++]; my $box = $box{$path};
+      if (defined $path &&
+         (!defined $old_path || $path ne $old_path || $old_box ne $box)) {
+       $st_update_disc->execute($path, $box eq "#nil" ? undef : $box,
+                                $id, $disc);
+      }
     }
     $i == $min_ndisc or die "missing disc records";
 
@@ -92,6 +95,7 @@ sub flush_set () {
   $id = undef; @path = (); %box = ();
 }
 
+my $curbox = "#nil";
 LINE: while (<>) {
   chomp;
 
@@ -113,6 +117,8 @@ LINE: while (<>) {
     } else {
       defined $name or die "missing name";
     }
+  } elsif (/^ \s* \! \s* box \s+ (\S .*) $/x) {
+    $curbox = $1;
   } elsif (/^ \s+ (?: !! \s*)? (\S .*) $/x) {
     my $path = $1;
     defined $id or die "no active set";