--- /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,
+ ndisc TEXT);
+ CREATE UNIQUE INDEX dvd_set_by_name ON dvd_set (name);
+
+ CREATE TABLE dvd_disc
+ (set_id INTEGER NOT NULL,
+ disc INTEGER NOT NULL,
+ path TEXT NOT NULL,
+ disc_id TEXT DEFAULT NULL,
+ PRIMARY KEY (set_id, disc));
+ CREATE INDEX dvd_disc_by_path ON dvd_disc (path);
+ CREATE INDEX dvd_disc_by_id ON dvd_disc (disc_id);
+");
+
+$DB->commit; $DB->disconnect;
--- /dev/null
+#! /usr/bin/perl
+
+use open ":utf8";
+use strict;
+
+use DBI;
+use Getopt::Std;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+(my $prog = $0) =~ s:^.*/::;
+sub HELP_MESSAGE ($;@) {
+ my ($fh) = @_;
+ print $fh "usage: $prog [-ps] DISC-ID ...\n";
+}
+my $ROOT = "/mnt/dvd/archive";
+
+my $bogusp = 0;
+my %opt;
+getopts("hps", \%opt) or $bogusp = 1;
+if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; }
+@ARGV or $bogusp = 1;
+if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; }
+$opt{"p"} || $opt{"s"} or $opt{"p"} = 1;
+
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+ { AutoCommit => 0,
+ RaiseError => 1,
+ ReadOnly => 1 });
+
+my $st = $DB->prepare("SELECT s.name, d.disc, d.path
+ FROM dvd_disc AS d JOIN dvd_set AS s
+ ON d.set_id = s.id
+ WHERE d.disc_id = ?");
+
+my $rc = 0;
+for my $id (@ARGV) {
+ $st->execute($id);
+ my @r = $st->fetchrow_array; $st->finish;
+ if (!@r) { print STDERR "$prog: unknown id `$id'\n"; $rc = 1; }
+ else {
+ my ($name, $disc, $path) = @r;
+ @ARGV > 1 and print "$id: ";
+ if ($opt{"s"}) { print "$name (#$disc)"; }
+ if ($opt{"p"} && $opt{"s"}) { print " ["; }
+ if ($opt{"p"}) { print $path; }
+ if ($opt{"p"} && $opt{"s"}) { print "]"; }
+ print "\n";
+ }
+}
+
+$DB->disconnect;
+exit $rc;
--- /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,
+ ReadOnly => 1 });
+
+my %iso = ();
+find(sub {
+ if (/\.iso$/ && ! -l && -f) {
+ my $fn = decode_utf8 $File::Find::name;
+ $fn =~ s:^$ROOT/::;
+ $iso{$fn} = 1;
+ }
+ }, $ROOT);
+
+my %set_path;
+my %set_id;
+my $st_set = $DB->prepare("SELECT id, name, ndisc FROM dvd_set");
+my $st_disc = $DB->prepare("SELECT disc, path FROM dvd_disc
+ WHERE set_id = ?
+ ORDER BY disc");
+$st_set->execute;
+
+SET: for (;;) {
+ my @r = $st_set->fetchrow_array; last SET unless @r;
+ my ($id, $name, $ndisc) = @r;
+ my @path;
+
+ $st_disc->execute($id);
+ DISC: for (;;) {
+ my @r = $st_disc->fetchrow_array; last DISC unless @r;
+ my ($disc, $path) = @r;
+ $disc == @path or die "bad disc sequence for `$name'";
+ push @path, $path;
+ }
+ @path == $ndisc or die "wrong number of discs for `$name'";
+ $set_path{$name} = \@path; $set_id{$name} = $id;
+}
+
+for my $name (keys %set_path) {
+ my $allp = 1;
+ for my $path (@{$set_path{$name}}) {
+ if (defined $path && exists $iso{$path}) { delete $iso{$path}; }
+ else { $allp = 0; }
+ }
+ ##if ($allp) { delete $set_path{$name}; }
+}
+
+my @iso = sort keys %iso;
+for my $name (sort { $set_path{$a}[0] cmp $set_path{$b}[0] }
+ keys %set_path) {
+ my $paths = $set_path{$name};
+ my @unk;
+ while (@iso && $iso[0] lt $paths->[0]) { push @unk, shift @iso; }
+ if (@unk) {
+ print "[#UNK: *]\n";
+ for my $path (@unk) { print "\t", $path, "\n"; }
+ }
+ printf "[#%d: %d] %s\n", $set_id{$name}, scalar @$paths, $name;
+ my $i = 0;
+ for my $path (@$paths) {
+ $i++;
+ if (!defined $path) {
+ printf "\t!! (disc %d)\n", $i;
+ } else {
+ my $fn = "$ROOT/$path";
+ if (-f $fn && ! -l $fn) { print "\t" . $path . "\n"; }
+ else { print "\t!! ". $path . "\n"; }
+ }
+ }
+}
+
+if (@iso) {
+ print "[#UNK: *]\n";
+ for my $path (@iso) { print "\t", $path, "\n"; }
+}
+
+$DB->disconnect;
--- /dev/null
+#! /usr/bin/perl
+
+use autodie qw{:all};
+use open ":utf8";
+use strict;
+
+use DBI;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+my $ROOT = "/mnt/dvd/archive";
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+ { AutoCommit => 0,
+ RaiseError => 1 });
+
+my $st_query = $DB->prepare("SELECT s.name, d.set_id, d.disc, d.path
+ FROM dvd_disc AS d JOIN dvd_set AS s
+ ON d.set_id = s.id
+ WHERE d.disc_id IS NULL");
+my $st_store = $DB->prepare("UPDATE dvd_disc SET disc_id = ?
+ WHERE set_id = ? AND disc = ?");
+
+$st_query->execute;
+DISC: for (;;) {
+ my @r = $st_query->fetchrow_array; last DISC unless @r;
+ my ($name, $set_id, $disc, $path) = @r;
+ open my $ph, "-|", "dvd-id", "-I", "--", "$ROOT/$path";
+ chomp (my $disc_id = <$ph>);
+ close $ph;
+ $st_store->execute($disc_id, $set_id, $disc);
+ print ";; $name $disc: $disc_id\n";
+}
+
+$DB->commit; $DB->disconnect;
--- /dev/null
+#! /usr/bin/perl -w
+
+use open ":utf8";
+use strict;
+
+use DBI;
+
+BEGIN {
+ binmode STDIN, ":utf8";
+ binmode STDOUT, ":utf8";
+ binmode STDERR, ":utf8";
+}
+
+my $ROOT = "/mnt/dvd/archive";
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+ { AutoCommit => 0,
+ RaiseError => 1 });
+
+my $st_get_set = $DB->prepare("SELECT name, ndisc FROM dvd_set
+ WHERE id = ?");
+my $st_add_set = $DB->prepare("INSERT INTO dvd_set (name, ndisc)
+ VALUES (?, ?)
+ RETURNING id");
+my $st_update_set = $DB->prepare("UPDATE dvd_set
+ SET name = ?, ndisc = ?
+ WHERE id = ?");
+my $st_delete_set = $DB->prepare("DELETE FROM dvd_set WHERE id = ?");
+
+my $st_get_discs = $DB->prepare("SELECT disc, path 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 (?, ?, ?)");
+my $st_update_disc = $DB->prepare("UPDATE dvd_disc SET path = ?
+ 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("DELETE FROM dvd_disc WHERE set_id = ?");
+
+my ($id, $name, $ndisc) = (undef, undef, -1);
+my @path;
+
+sub flush_set () {
+ defined $id or return;
+
+ ##print ";; flush set: #$id: $ndisc\n";
+ ##for my $p (@path) { print ";;\t$p\n"; }
+
+ if ($ndisc eq "*") { $ndisc = @path; }
+ elsif (@path != $ndisc)
+ { die sprintf "wrong number of discs %d /= %d", scalar @path, $ndisc; }
+
+ my $min_ndisc;
+ if ($id eq "UNK") {
+ $id = undef; @path = ();
+ return;
+ } elsif ($id eq "NEW") {
+ $st_add_set->execute($name, $ndisc);
+ ($id) = $st_add_set->fetchrow_array; $st_add_set->finish;
+ $min_ndisc = 0;
+ } else {
+ $st_get_set->execute($id);
+ my ($old_name, $old_ndisc) = $st_get_set->fetchrow_array;
+ $st_get_set->finish;
+ $name ne $old_name || $ndisc ne $old_ndisc and
+ $st_update_set->execute($name, $ndisc, $id);
+
+ $min_ndisc = $ndisc < $old_ndisc ? $ndisc : $old_ndisc;
+ $st_get_discs->execute($id, 0, $min_ndisc);
+ my $i = 0;
+ DISC: for (;;) {
+ my @r = $st_get_discs->fetchrow_array; last DISC unless @r;
+ my ($disc, $old_path) = @r;
+ $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); }
+ }
+ $i == $min_ndisc or die "missing disc records";
+
+ $min_ndisc < $old_ndisc and
+ $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]); }
+
+ $id = undef; @path = ();
+}
+
+LINE: while (<>) {
+ chomp;
+
+ if (/^ \[ \# (\d+ | NEW | UNK) \s* : \s* (\d+ | \* | DEL) ]
+ \s* (\S .*)? $/x) {
+ flush_set;
+
+ ($id, $ndisc, $name) = ($1, $2, $3);
+ if ($ndisc eq "DEL") {
+ $id eq "NEW" || $id eq "UNK" and die "can't delete virtual records";
+ defined $name and die "name `$name' supplied with deletion request";
+ $st_delete_discs->execute($id);
+ $st_delete_set->execute($id);
+ $id = undef;
+ } elsif ($id eq "UNK") {
+ defined $name and die "can't name unknown records";
+ } else {
+ defined $name or die "missing name";
+ }
+ } elsif (/^ \s+ !! \s* (\S .*) $/x) {
+ my $path = $1;
+ defined $id or die "no active set";
+ push @path, undef;
+ } elsif (/^ \s+ (\S .*) $/x) {
+ my $path = $1;
+ defined $id or die "no active set";
+ -f "$ROOT/$path" && ! -l "$ROOT/$path" or die "file `$path' not found";
+ push @path, $path;
+ } elsif (/^ .* \S .* $/) {
+ die "unrecognized line `$_'";
+ }
+}
+
+$DB->commit; $DB->disconnect;
--- /dev/null
+#! /usr/bin/perl -w
+
+use open ":utf8";
+use strict;
+
+use DBI;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+ { AutoCommit => 0, RaiseError => 1 });
+
+my $st = $DB->prepare("SELECT title, ndisc, path FROM old_dvd ORDER BY title");
+$st->execute;
+
+my $st1 = $DB->prepare("INSERT INTO dvd (title, disc, path) VALUES (?, ?, ?)");
+ROW: for (;;) {
+ my @r = $st->fetchrow_array; last ROW unless @r;
+ my ($title, $ndisc, $path) = @r;
+ print ";; $title [$ndisc]\n";
+ for (my $i = 0; $i < $ndisc; $i++) { $st1->execute($title, $i, $path); }
+}
+$DB->commit; $DB->disconnect;
--- /dev/null
+#! /usr/bin/perl -w
+
+use open ":utf8";
+use strict;
+
+use DBI;
+
+BEGIN { binmode STDOUT, ":utf8"; }
+
+my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "",
+ { AutoCommit => 0, RaiseError => 1 });
+
+my $st_outer =
+ $DB->prepare("SELECT title, COUNT(*) FROM old_dvd GROUP BY title");
+my $st_inner =
+ $DB->prepare("SELECT disc, path, discid FROM old_dvd WHERE title = ?");
+my $st_add_set =
+ $DB->prepare("INSERT INTO dvd_set (name, ndisc)
+ VALUES (?, ?)
+ RETURNING id");
+my $st_add_disc =
+ $DB->prepare("INSERT INTO dvd_disc (set_id, disc, path, disc_id)
+ VALUES (?, ?, ?, ?)");
+$st_outer->execute;
+
+OUTER: for (;;) {
+ my @r = $st_outer->fetchrow_array; last OUTER unless @r;
+ my ($title, $ndisc) = @r;
+
+ $st_add_set->execute($title, $ndisc);
+ my ($set_id) = $st_add_set->fetchrow_array;
+
+ $st_inner->execute($title);
+ INNER: for (;;) {
+ my @r = $st_inner->fetchrow_array; last INNER unless @r;
+ my ($disc, $path, $disc_id) = @r;
+ $st_add_disc->execute($set_id, $disc, $path, $disc_id);
+ }
+}
+$DB->commit; $DB->disconnect;
~mdw / dvddb / commitdiff