From 14acb11fbceab6626284efa4a1b250f988a31076 Mon Sep 17 00:00:00 2001 From: Mark Wooding Date: Mon, 21 Mar 2022 02:02:41 +0000 Subject: [PATCH] Initial commit. --- .gitignore | 1 + create-v3 | 39 ++++++++++++++++++ lookup-dvd-id | 53 +++++++++++++++++++++++++ report | 89 +++++++++++++++++++++++++++++++++++++++++ set-dvd-ids | 34 ++++++++++++++++ update | 125 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ upgrade-v2 | 23 +++++++++++ upgrade-v3 | 40 +++++++++++++++++++ 8 files changed, 404 insertions(+) create mode 100644 .gitignore create mode 100755 create-v3 create mode 100755 lookup-dvd-id create mode 100755 report create mode 100755 set-dvd-ids create mode 100755 update create mode 100755 upgrade-v2 create mode 100755 upgrade-v3 diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..40ddb02 --- /dev/null +++ b/.gitignore @@ -0,0 +1 @@ +/PATHS diff --git a/create-v3 b/create-v3 new file mode 100755 index 0000000..7207622 --- /dev/null +++ b/create-v3 @@ -0,0 +1,39 @@ +#! /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; diff --git a/lookup-dvd-id b/lookup-dvd-id new file mode 100755 index 0000000..6b5e1d4 --- /dev/null +++ b/lookup-dvd-id @@ -0,0 +1,53 @@ +#! /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; diff --git a/report b/report new file mode 100755 index 0000000..c7baf00 --- /dev/null +++ b/report @@ -0,0 +1,89 @@ +#! /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; diff --git a/set-dvd-ids b/set-dvd-ids new file mode 100755 index 0000000..699751e --- /dev/null +++ b/set-dvd-ids @@ -0,0 +1,34 @@ +#! /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; diff --git a/update b/update new file mode 100755 index 0000000..0fadbcd --- /dev/null +++ b/update @@ -0,0 +1,125 @@ +#! /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; diff --git a/upgrade-v2 b/upgrade-v2 new file mode 100755 index 0000000..1846791 --- /dev/null +++ b/upgrade-v2 @@ -0,0 +1,23 @@ +#! /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; diff --git a/upgrade-v3 b/upgrade-v3 new file mode 100755 index 0000000..e3cc8bb --- /dev/null +++ b/upgrade-v3 @@ -0,0 +1,40 @@ +#! /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; -- 2.11.0