update: Actually update the last entry in the file.

Not quite sure how this one slipped by.

diff --git a/create-v4 b/create-v4
new file mode 100755 (executable)
index 0000000..d8ddaf5
--- /dev/null
+++ b/create-v4
@@ -0,0 +1,90 @@
+#! /usr/bin/perl -w
+
+use open ":utf8";
+use strict;
+
+use DBI;
+use Encode qw{encode_utf8 decode_utf8};
+use File::Find;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+my $ROOT = "/mnt/dvd/archive";
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+                     { AutoCommit => 0,
+                       RaiseError => 1 });
+
+$DB->do("
+       CREATE SEQUENCE dvd_set_id MINVALUE 0;
+       CREATE TABLE dvd_set
+               (id INTEGER PRIMARY KEY NOT NULL
+                       DEFAULT (nextval('dvd_set_id')),
+                name TEXT UNIQUE NOT NULL,
+                n_disc INTEGER NOT NULL);
+       CREATE TABLE dvd_disc
+               (set_id INTEGER NOT NULL
+                disc INTEGER NOT NULL,
+                path TEXT NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED,
+                disc_id TEXT DEFAULT NULL UNIQUE,
+                box TEXT DEFAULT NULL,
+                PRIMARY KEY (set_id, disc),
+                FOREIGN KEY (set_id) REFERENCES dvd_set(id)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+       CREATE INDEX dvd_disc_by_box ON dvd_disc (box);
+
+       CREATE TABLE series
+               (name TEXT PRIMARY KEY NOT NULL,
+                title TEXT NOT NULL);
+
+       CREATE SEQUENCE media_id MINVALUE 0;
+       CREATE TABLE media
+               (id INTEGER PRIMARY KEY NOT NULL
+                       DEFAULT (nextval('media_id')),
+                path TEXT NOT NULL,
+                title_number INTEGER NOT NULL DEFAULT (-1),
+                start_chapter INTEGER NOT NULL DEFAULT (-1),
+                end_chapter INTEGER NOT NULL DEFAULT (-1),
+                duration REAL NOT NULL,
+                title TEXT NOT NULL,
+                series_name TEXT NOT NULL,
+                UNIQUE (path, title_number, start_chapter, end_chapter),
+                FOREIGN KEY (series_name) REFERENCES series(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+
+       CREATE TABLE playlist
+               (name TEXT UNIQUE NOT NULL,
+                n_entry INTEGER NOT NULL);
+       CREATE TABLE playlist_entry
+               (list_name TEXT NOT NULL,
+                entry INTEGER NOT NULL,
+                media_id INTEGER NOT NULL,
+                PRIMARY KEY (list_name, entry),
+                FOREIGN KEY (list_name) REFERENCES playlist(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (media_id) REFERENCES media(id)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+
+       CREATE TABLE playlist_group
+               (name TEXT PRIMARY KEY NOT NULL);
+       CREATE TABLE playlist_position
+               (group_name TEXT NOT NULL,
+                list_name TEXT NOT NULL,
+                next_entry INTEGER NOT NULL,
+                PRIMARY KEY (group_name, list_name),
+                FOREIGN KEY (group_name) REFERENCES playlist_group(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (list_name) REFERENCES playlist(name)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE,
+                FOREIGN KEY (list_name, next_entry)
+                       REFERENCES playlist_entry(list_name, entry)
+                       ON DELETE CASCADE
+                       ON UPDATE CASCADE);
+");
+
+$DB->commit; $DB->disconnect;

diff --git a/pldb b/pldb
index 7f59373..40c67e0 100755 (executable)
--- a/pldb
+++ b/pldb
@@ -260,11 +260,11 @@ defcmd "next [-pu] [-o N] [-n N] GROUP [LIST]", sub {
   defined $prevlist and print "$prevlist\n", map "\t$_\n", @out;
 };
 
-defcmd "list [-g [MARK=]GROUP,...] [-r [LO][-[HI]],...] LIST", sub {
+defcmd "list [-g [MARK=]GROUP,...] [-r [LO][-[HI]],...] [-w WIN] LIST", sub {
   local @ARGV = @_;
   my $bogusp = 0;
   my %opt;
-  getopts("g:r:", \%opt) or $bogusp = 1;
+  getopts("g:r:w:", \%opt) or $bogusp = 1;
   @ARGV == 1 or $bogusp = 1;
   if ($bogusp) { bail_usage; }
   my ($list) = @ARGV;
@@ -285,9 +285,10 @@ defcmd "list [-g [MARK=]GROUP,...] [-r [LO][-[HI]],...] LIST", sub {
   my %pos; my $plen = -1;
   if (defined $opt{"g"}) {
     my @g = split /,/, $opt{"g"};
-    for (@g) {
+    GROUP: for (@g) {
       my ($pre, $g) = /^ (?: (.*?)=)? (.*) $/x; must_group $g;
       $st_pos->execute($g, $list); my ($pos) = $st_pos->fetchrow_array;
+      next GROUP unless defined $pos;
       my $t = $pos{$pos} =
        (exists $pos{$pos} ? $pos{$pos} . "," : "") .
        ($pre // (@g == 1 ? "" : $g));
@@ -320,6 +321,12 @@ defcmd "list [-g [MARK=]GROUP,...] [-r [LO][-[HI]],...] LIST", sub {
     }
   }
 
+  if (defined $opt{"w"}) {
+    (my $w = $opt{"w"}) =~ /^\d+$/ or die "bad window size `$opt{w}'";
+    %pos or die "no positions";
+    for my $pos (keys %pos) { push @r, [$pos - $w, $pos + $w + 1] }
+  }
+
   if (@r) {
     my ($curr_lo, $curr_hi) = (undef, undef);
     my @newr;

diff --git a/report b/report
index 7fb00b7..566c162 100755 (executable)
--- a/report
+++ b/report
@@ -26,9 +26,10 @@ find(sub {
 
 my %set_path;
 my %set_id;
+my %box;
 my $st_set = $DB->prepare("SELECT id, name, n_disc FROM dvd_set");
 my $st_disc = $DB->prepare
-  ("SELECT disc, path FROM dvd_disc WHERE set_id = ? ORDER BY disc");
+  ("SELECT disc, path, box FROM dvd_disc WHERE set_id = ? ORDER BY disc");
 $st_set->execute;
 
 while (my @r = $st_set->fetchrow_array) {
@@ -37,8 +38,9 @@ while (my @r = $st_set->fetchrow_array) {
 
   $st_disc->execute($id);
   while (my @r = $st_disc->fetchrow_array) {
-    my ($disc, $path) = @r;
+    my ($disc, $path, $box) = @r;
     $disc == @path or die "bad disc sequence for `$name'";
+    $box{$path} = $box if defined $box;
     push @path, $path;
   }
   @path == $ndisc or die "wrong number of discs for `$name'";
@@ -55,10 +57,37 @@ for my $name (keys %set_path) {
 }
 
 my @iso = sort keys %iso;
-for my $name (sort { $set_path{$a}[0] cmp $set_path{$b}[0] }
+my $lastbox = "#nil";
+sub set_box ($) {
+  my ($box) = @_;
+  $box //= "#nil";
+  if ($box ne $lastbox) { print "!box $box\n"; $lastbox = $box; }
+}
+
+sub path_key ($) {
+  my ($p) = @_;
+  if ($p =~ m{^ ([a-z]) / ([^/]+) ((?: / .*)?) $}ix) {
+    my $init = $1;
+    my $focus = $2;
+    my $tail = $3;
+    my $head = undef;
+    if ($focus =~ /^ (the | a ) \s+ (\S .*) $/x)
+      { $head = $1; $focus = $2; }
+    if (lc $init ne lc substr($focus, 0, 1) &&
+       $focus =~ /^ (.*) \s+ ($init .*) $/x)
+      { $head .= (defined $head && " ") . $1; $focus = $2; }
+    if (defined $head) { return "$init/$focus, $head$tail"; }
+  }
+  return $p;
+}
+
+for my $name (sort { my $pa = $set_path{$a}[0]; my $ba = $box{$pa} // "~~~";
+                    my $pb = $set_path{$b}[0]; my $bb = $box{$pb} // "~~~";
+                    $ba cmp $bb || path_key $pa cmp path_key $pb }
                keys %set_path) {
   my $paths = $set_path{$name};
   my @unk;
+  set_box $box{$paths->[0]};
   while (@iso && $iso[0] lt $paths->[0]) { push @unk, shift @iso; }
   if (@unk) {
     print "[#UNK: *]\n";
@@ -72,6 +101,7 @@ for my $name (sort { $set_path{$a}[0] cmp $set_path{$b}[0] }
       printf "\t!! (disc %d)\n", $i;
     } else {
       my $fn = "$ROOT/$path";
+      set_box $box{$path};
       if (-f $fn && ! -l $fn) { print "\t" . $path . "\n"; }
       else { print "\t!! ". $path . "\n"; }
     }

diff --git a/update b/update
index b2b60d8..e2a07fa 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";
 
@@ -83,17 +86,23 @@ sub flush_set () {
       $st_delete_disc_range->execute($id, $min_ndisc, $old_ndisc);
   }
 
-  for (my $i = $min_ndisc; $i < $ndisc; $i++)
-    { $st_add_disc->execute($id, $i, $path[$i]); }
+  for (my $i = $min_ndisc; $i < $ndisc; $i++) {
+    my $box = $box{$path[$i]};
+    $st_add_disc->execute($id, $i, $path[$i],
+                         $box eq "#nil" ? undef : $box);
+  }
 
-  $id = undef; @path = ();
+  $id = undef; @path = (); %box = ();
 }
 
+my $curbox = "#nil";
 LINE: while (<>) {
   chomp;
 
-  if (/^ \[ \# (\d+ | NEW | UNK) \s* : \s* (\d+ | \* | DEL) ]
-       \s* (\S .*)? $/x) {
+  if (/^ \s* (\; .*)? $/x) {
+    next LINE;
+  } elsif (/^ \[ \# (\d+ | NEW | UNK) \s* : \s* (\d+ | \* | DEL) ]
+             \s* (\S .*)? $/x) {
     flush_set;
 
     ($id, $ndisc, $name) = ($1, $2, $3);
@@ -108,13 +117,16 @@ 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";
-    push @path, $path;
-  } elsif (/^ .* \S .* $/) {
+    push @path, $path; $box{$path} = $curbox;
+  } else {
     die "unrecognized line `$_'";
   }
 }
 
+flush_set;
 $DB->commit; $DB->disconnect;