--- /dev/null
+#! /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;
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) {
$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'";
}
my @iso = sort keys %iso;
+my $lastbox = "#nil";
+sub set_box ($) {
+ my ($box) = @_;
+ $box //= "#nil";
+ if ($box ne $lastbox) { print "!box $box\n"; $lastbox = $box; }
+}
+
for my $name (sort { $set_path{$a}[0] cmp $set_path{$b}[0] }
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";
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"; }
}
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
my ($id, $name, $ndisc) = (undef, undef, -1);
my @path;
+my %box;
sub flush_set () {
defined $id or return;
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";
$id = undef; @path = (); %box = ();
}
+my $curbox = "#nil";
LINE: while (<>) {
chomp;
} 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";