From: Mark Wooding Date: Tue, 14 May 2024 01:19:23 +0000 (+0100) Subject: create-v4, report, update: Keep track of which box each disc is in. X-Git-Url: https://www.chiark.greenend.org.uk/ucgi/~mdw/git/dvddb/commitdiff_plain/b2e7e49c532efc7369f1dc7a9befa3b193de8825?ds=inline create-v4, report, update: Keep track of which box each disc is in. --- diff --git a/create-v4 b/create-v4 new file mode 100755 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/report b/report index 7fb00b7..29d1d7e 100755 --- 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,18 @@ for my $name (keys %set_path) { } 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"; @@ -72,6 +82,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 79c6a35..7fdef51 100755 --- 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";