From b2a25885cb81761a2b33014a00f843e76f4ce83c Mon Sep 17 00:00:00 2001 From: Mark Wooding Date: Wed, 6 Apr 2022 21:25:26 +0100 Subject: [PATCH] Playlist support, other randomness. --- cleanup | 62 +++++++++++ create-v3 | 75 ++++++++++--- lookup-dvd-id | 8 +- pldb | 337 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ report | 13 +-- set-dvd-ids | 30 ++++-- update | 38 +++---- updpldb | 114 ++++++++++++++++++++ upgrade-v2 | 9 +- upgrade-v3 | 19 ++-- 10 files changed, 638 insertions(+), 67 deletions(-) create mode 100755 cleanup create mode 100755 pldb create mode 100755 updpldb diff --git a/cleanup b/cleanup new file mode 100755 index 0000000..7720ce0 --- /dev/null +++ b/cleanup @@ -0,0 +1,62 @@ +#! /usr/bin/perl -w + +use autodie qw{:all}; +use open ":utf8"; +use strict; + +use DBI; +use Encode qw{encode_utf8 decode_utf8}; +use Getopt::Std; + +BEGIN { + binmode STDIN, ":utf8"; + binmode STDOUT, ":utf8"; + binmode STDERR, ":utf8"; +} + +(my $prog = $0) =~ s:^.*/::; +sub HELP_MESSAGE ($;@) { + my ($fh) = @_; + print $fh "usage: $prog [-f]\n"; +} + +my $bogusp = 0; +my %opt; +getopts("hf", \%opt) or $bogusp = 1; +if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; } +@ARGV == 0 or $bogusp = 1; +if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; } + +my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", + { AutoCommit => 0, + RaiseError => 1 }); + +for my $item (["media", "id", "title", + "playlist_entry", "media_id", "list_name"], + ["series", "name", "name", + "media", "series_name", "id"], + ["playlist", "name", "name", + "playlist_entry", "list_name", "entry"], + ["dvd_set", "id", "name", + "dvd_disc", "set_id", "disc"]) { + my ($table, $key, $name, $reftable, $refcol, $testcol) = @$item; + my $st = $DB->prepare + ("SELECT t.$name FROM $table AS t + LEFT JOIN $reftable AS r ON r.$refcol = t.$key + WHERE r.$testcol IS NULL"); + $st->execute; + my $any = 0; + while (my @r = $st->fetchrow_array) + { my ($rowname) = @r; print "$table: $rowname\n"; $any = 1; } + + if ($any && $opt{"f"}) { + my $n = $DB->do + ("DELETE FROM $table WHERE $key IN + (SELECT t.$key FROM $table AS t + LEFT JOIN $reftable AS r ON r.$refcol = t.$key + WHERE r.$testcol IS NULL)"); + print "$table: deleted $n\n"; + } +} + +$DB->commit; $DB->disconnect; diff --git a/create-v3 b/create-v3 index 7207622..8e68193 100755 --- a/create-v3 +++ b/create-v3 @@ -16,24 +16,73 @@ my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", $DB->do(" CREATE SEQUENCE dvd_set_id MINVALUE 0; - CREATE TABLE dvd_set - (id INTEGER - PRIMARY KEY - NOT NULL + (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); - + name TEXT UNIQUE NOT NULL, + n_disc INTEGER NOT NULL); CREATE TABLE dvd_disc - (set_id INTEGER NOT NULL, + (set_id INTEGER NOT NULL disc INTEGER NOT NULL, + path TEXT NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED, + disc_id TEXT DEFAULT NULL UNIQUE, + PRIMARY KEY (set_id, disc), + FOREIGN KEY (set_id) REFERENCES dvd_set(id) + ON DELETE CASCADE + ON UPDATE CASCADE); + + 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, - 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); + 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/lookup-dvd-id b/lookup-dvd-id index 6b5e1d4..a0522bb 100755 --- a/lookup-dvd-id +++ b/lookup-dvd-id @@ -28,10 +28,10 @@ my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", 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 $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) { diff --git a/pldb b/pldb new file mode 100755 index 0000000..2a16fe5 --- /dev/null +++ b/pldb @@ -0,0 +1,337 @@ +#! /usr/bin/perl -w + +use autodie qw{:all}; +use open ":utf8"; +use strict; + +use DBI; +use Encode qw{encode_utf8 decode_utf8}; +use Getopt::Std; + +BEGIN { + binmode STDIN, ":utf8"; + binmode STDOUT, ":utf8"; + binmode STDERR, ":utf8"; +} + +(my $prog = $0) =~ s:^.*/::; +my %CMD_HELP; my %CMD_FN; +my $CMD = undef; +sub defcmd ($$) { + my ($help, $fn) = @_; + $help =~ /^(\S+)/ or die "bad usage"; + my $cmd = $1; + $CMD_HELP{$cmd} = $help; + $CMD_FN{$cmd} = $fn; +} +sub HELP_MESSAGE ($;@) { + my ($fh) = @_; + if (defined $CMD) { + print $fh "usage: $prog $CMD_HELP{$CMD}\n"; + } else { + print $fh "usage: $prog CMD ARGS ...\n"; + for my $cmd (sort keys %CMD_HELP) { print $fh "\t$CMD_HELP{$cmd}\n"; } + } +} +sub bail_usage () { HELP_MESSAGE \*STDERR; exit 2; } + +my $DB = undef; +my $ROOT = "/mnt/dvd/archive"; + +sub db_connect (;$) { + my ($opts) = @_; $opts //= {}; + my %opts = (AutoCommit => 0, + RaiseError => 1, + ReadOnly => 1, + %$opts); + $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", \%opts); +} + +sub must_exist ($$@) { + my ($msg, $query, @args) = @_; + my $st = $DB->prepare($query); + $st->execute(@args); + my ($n) = $st->fetchrow_array; $st->finish; + $n or die $msg; +} +sub must_group ($) { + my ($group) = @_; + must_exist("unknown group `$group'", + "SELECT COUNT(*) FROM playlist_group WHERE name = ?", $group); +} +sub must_list ($) { + my ($list) = @_; + must_exist("unknown list `$list'", + "SELECT COUNT(*) FROM playlist WHERE name = ?", $list); +} +sub must_member ($$) { + my ($group, $list) = @_; + must_list $list; + must_exist("`$list' is not a member of `$group'", + "SELECT COUNT(*) FROM playlist_position + WHERE group_name = ? AND list_name = ?", + $group, $list); +} + +defcmd "newgroup NAME", sub { + @_ == 1 or bail_usage; + my ($name) = @_; + db_connect { ReadOnly => 0 }; + $DB->do("INSERT INTO playlist_group (name) VALUES (?)", undef, $name); +}; + +defcmd "rmgroup NAME", sub { + @_ == 1 or bail_usage; + my ($name) = @_; + db_connect { ReadOnly => 0 }; + must_group $name; + $DB->do("DELETE FROM playlist_group WHERE name = ?", undef, $name); +}; + +defcmd "groups", sub { + @_ == 0 or bail_usage; + db_connect; + my $st = $DB->prepare("SELECT name FROM playlist_group ORDER BY name"); + $st->execute; + while (my @r = $st->fetchrow_array) { my ($name) = @r; print "$name\n"; } +}; + +defcmd "lists", sub { + @_ == 0 or bail_usage; + db_connect; + my $st = $DB->prepare("SELECT name FROM playlist ORDER BY name"); + $st->execute; + while (my @r = $st->fetchrow_array) { my ($name) = @r; print "$name\n"; } +}; + +defcmd "edit GROUP LIST[=[+|-]POS]|-LIST ...", sub { + @_ >= 2 or bail_usage; + my ($group, @ops) = @_; + + db_connect { ReadOnly => 0 }; + must_group $group; + my $st_chk = $DB->prepare("SELECT n_entry FROM playlist WHERE name = ?"); + my $st_getpos = $DB->prepare + ("SELECT next_entry FROM playlist_position + WHERE group_name = ? AND list_name = ?"); + my $st_set = $DB->prepare + ("INSERT INTO playlist_position AS p (group_name, list_name, next_entry) + VALUES (?, ?, ?) + ON CONFLICT (group_name, list_name) + DO UPDATE SET next_entry = ? + WHERE p.group_name = ? AND p.list_name = ?"); + my $st_del = $DB->prepare + ("DELETE FROM playlist_position WHERE group_name = ? AND list_name = ?"); + + for my $op (@ops) { + if ($op =~ /^ - (.++) $/x) { + my ($list) = ($1); + must_list $list; must_member $group, $list; + $st_del->execute($group, $list); + } elsif ($op =~ /^ ([^=]++) (?: = ([-+])?+ (\d++))?+ $/x) { + my ($list, $rel, $pos) = ($1, $2, $3); + + $st_chk->execute($list); + my @r = $st_chk->fetchrow_array; @r or die "unknown list `$list'"; + my ($n) = @r; $st_chk->finish; + + $pos //= 0; + if (defined $rel) { + $st_getpos->execute($group, $list); + my @r = $st_getpos->fetchrow_array; + @r or die "`$list' is not a member of `$group'"; + my ($cur) = @r; $st_getpos->finish; + + if ($rel eq "+") { $pos = $cur + $pos; } + elsif ($rel eq "-") { $pos = $cur - $pos; } + } + 0 <= $pos && $pos < $n + or die "`$list' position $pos out of range 0 .. $n"; + $st_set->execute($group, $list, $pos, + $pos, $group, $list); + } else { + die "bad edit op `$op'"; + } + } +}; + +defcmd "next [-pu] [-o N] [-n N] GROUP [LIST]", sub { + local @ARGV = @_; + my $bogusp = 0; + my %opt; + getopts("o:n:pu", \%opt) or $bogusp = 1; + 1 <= @ARGV && @ARGV <= 2 or $bogusp = 1; + !defined $opt{"o"} || $opt{"o"} =~ /^[-+]?\d+$/ or $bogusp = 1; + !defined $opt{"n"} || $opt{"n"} =~ /^\d+$/ or $bogusp = 1; + my $o = $opt{"o"} // 0; + my $n = $opt{"n"} // 1; + if ($bogusp) { bail_usage; } + my ($group, $list) = @ARGV; + + db_connect; + must_group $group; + defined $list and must_member $group, $list; + + my $glen = 0; + if (!defined $list) { + my $st = $DB->prepare + ("SELECT MAX(LENGTH(list_name)) + FROM playlist_position + WHERE group_name = ?"); + $st->execute($group); ($glen) = $st->fetchrow_array; $st->finish; + } + + my %elen; my $elen_max = -1; + if ($opt{"p"}) { + my $q = + "SELECT p.list_name, MAX(e.entry) + FROM playlist_entry AS e + JOIN playlist_position AS p ON e.list_name = p.list_name + WHERE p.group_name = ? AND + p.next_entry + ? <= e.entry AND e.entry < p.next_entry + ?"; + my @arg = ($group, $o, $n + $o); + if (defined $list) { $q .= " AND p.list_name = ?"; push @arg, $list; } + $q .= " GROUP BY p.list_name"; + my $st = $DB->prepare($q); $st->execute(@arg); + while (my @r = $st->fetchrow_array) { + my ($l, $emax) = @r; + my $elen = $elen{$l} = defined $emax ? length $emax : -1; + $elen > $elen_max and $elen_max = $elen; + } + } + + my $q = + "SELECT p.list_name, p.next_entry, e.entry, c.n, s.title, m.title, + m.path, m.title_number, m.start_chapter, m.end_chapter + FROM playlist_position AS p + JOIN playlist_entry AS e ON p.list_name = e.list_name + JOIN media AS m ON e.media_id = m.id + JOIN series AS s ON m.series_name = s.name + JOIN (SELECT t.list_name, COUNT(*) AS n + FROM (SELECT DISTINCT e.list_name, m.series_name AS series_name + FROM playlist_entry AS e + JOIN media AS m ON e.media_id = m.id) AS t + GROUP BY t.list_name) AS c + ON e.list_name = c.list_name + WHERE p.group_name = ? AND + p.next_entry + ? <= e.entry AND e.entry < p.next_entry + ?"; + my @arg = ($group, $o, $n + $o); + if (defined $list) { $q .= " AND p.list_name = ?"; push @arg, $list; } + $q .= " ORDER BY p.list_name ASC, e.entry ASC"; + my $st_query = $DB->prepare($q); $st_query->execute(@arg); + my $prevlist = undef; + my @out; + + while (my @r = $st_query->fetchrow_array) { + my ($l, $pos, $i, $nseries, $stitle, $title, + $path, $ttn, $loch, $hich) = @r; + my $out; + if (!$opt{"u"}) { + $out = $nseries == 1 ? $title : "$stitle $title"; + } else { + my $scheme = $path =~ /\.iso$/ ? "dvd" : "file"; + my $frag; + if ($ttn == -1) { $frag = ""; } + elsif ($loch == -1) { $frag = "#$ttn"; } + elsif ($hich == -1) { $frag = "#$ttn:$loch"; } + else { my $hi = $hich - 1; $frag = "#$ttn:$loch-$ttn:$hi"; } + $path = encode_utf8 $path; + $path =~ s:([^-_\w.,!\$\%/]):sprintf "%%%02x", ord $1:eg; + $out = "$scheme://$ROOT/$path$frag"; + } + if ($opt{"p"}) { + my $pos = "[$i]"; + $out = sprintf "%*s %s", + ($n == 1 ? $elen_max : $elen{$l}) + 2, $pos, $out; + } + if ($n > 1) { $out = ($i == $pos ? "> " : " ") . $out; } + if (defined $list) { print $out, "\n"; } + elsif ($n == 1) { printf "%-*s %s\n", $glen, $l, $out; } + elsif (!defined $prevlist) { $prevlist = $l; @out = ($out); } + elsif (defined $prevlist && $l eq $prevlist) { push @out, $out; } + else { + print "$prevlist\n", map("\t$_\n", @out), "\n"; + $prevlist = $l; @out = ($out); + } + } + defined $prevlist and print "$prevlist\n", map "\t$_\n", @out; +}; + +defcmd "list LIST", sub { + local @ARGV = @_; + my $bogusp = 0; + my %opt; + getopts("g:r:", \%opt) or $bogusp = 1; + @ARGV == 1 or $bogusp = 1; + if ($bogusp) { bail_usage; } + my ($list) = @ARGV; + + db_connect; + must_list $list; + my $st_nseries = $DB->prepare + ("SELECT COUNT(*) FROM + (SELECT DISTINCT m.series_name + FROM playlist_entry AS e JOIN media AS m ON e.media_id = m.id + WHERE e.list_name = ?) AS _"); + my $st_pos = $DB->prepare + ("SELECT p.next_entry FROM playlist_position AS p + WHERE p.group_name = ? AND p.list_name = ?"); + $st_nseries->execute($list); + my ($nseries) = $st_nseries->fetchrow_array; $st_nseries->finish; + + my %pos; my $plen = -1; + if (defined $opt{"g"}) { + my @g = split /,/, $opt{"g"}; + for (@g) { + my ($pre, $g) = /^ (?: (.*?)=)? (.*) $/x; must_group $g; + $st_pos->execute($g, $list); my ($pos) = $st_pos->fetchrow_array; + my $t = $pos{$pos} = + (exists $pos{$pos} ? $pos{$pos} . "," : "") . + ($pre // (@g == 1 ? "" : $g)); + length $t > $plen and $plen = length $t; + } + } + + my $st_elen = $DB->prepare + ("SELECT MAX(entry) FROM playlist_entry WHERE list_name = ?"); + $st_elen->execute($list); + my ($emax) = $st_elen->fetchrow_array; $st_elen->finish; + my $elen = length $emax; + + my $q = + "SELECT e.entry, s.title, m.title + FROM playlist_entry AS e + JOIN media AS m ON e.media_id = m.id + JOIN series AS s ON m.series_name = s.name + WHERE e.list_name = ?"; + my @arg = ($list); + + if (defined $opt{"r"}) { + $opt{"r"} =~ /^ (\d+)? (?: - (\d+)?)? $/x + or die "invalid range expression `$opt{'r'}'"; + my ($lo, $hi) = ($1, $2); + if (defined $lo) { $q .= " AND ? <= e.entry"; push @arg, $lo; } + if (defined $hi) { $q .= " AND e.entry < ?"; push @arg, $hi; } + } + + $q .= " ORDER BY e.entry ASC"; + my $st_list = $DB->prepare($q); $st_list->execute(@arg); + my $msep = $plen < 0 ? "" : " "; + while (my @r = $st_list->fetchrow_array) { + my ($i, $stitle, $title) = @r; + $nseries == 1 or $title = "$stitle $title"; + my $m = exists $pos{$i} ? "$pos{$i}>" : ""; + printf "%*s%s%*s %s\n", $plen + 1, $m, $msep, $elen + 2, "[$i]", $title; + } +}; + +my $bogusp = 0; +my %opt; +getopts("h", \%opt) or $bogusp = 1; +if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; } +@ARGV >= 1 or $bogusp = 1; +if ($bogusp) { bail_usage; } + +$CMD = shift; $CMD_FN{$CMD} or die "unknown command `$CMD'"; +$CMD_FN{$CMD}(@ARGV); +if (defined $DB) { $DB->commit; $DB->disconnect; } diff --git a/report b/report index c7baf00..7fb00b7 100755 --- a/report +++ b/report @@ -26,20 +26,17 @@ find(sub { 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"); +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"); $st_set->execute; -SET: for (;;) { - my @r = $st_set->fetchrow_array; last SET unless @r; +while (my @r = $st_set->fetchrow_array) { my ($id, $name, $ndisc) = @r; my @path; $st_disc->execute($id); - DISC: for (;;) { - my @r = $st_disc->fetchrow_array; last DISC unless @r; + while (my @r = $st_disc->fetchrow_array) { my ($disc, $path) = @r; $disc == @path or die "bad disc sequence for `$name'"; push @path, $path; diff --git a/set-dvd-ids b/set-dvd-ids index 699751e..e4476fd 100755 --- a/set-dvd-ids +++ b/set-dvd-ids @@ -5,24 +5,38 @@ 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 [-a]\n"; +} my $ROOT = "/mnt/dvd/archive"; + +my $bogusp = 0; +my %opt; +getopts("ha", \%opt) or $bogusp = 1; +if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; } +@ARGV and $bogusp = 1; +if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; } + 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 = ?"); +my $search = $opt{"a"} ? "" : "WHERE d.disc_id IS NULL"; +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 + $search"); +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; +while (my @r = $st_query->fetchrow_array) { my ($name, $set_id, $disc, $path) = @r; open my $ph, "-|", "dvd-id", "-I", "--", "$ROOT/$path"; chomp (my $disc_id = <$ph>); diff --git a/update b/update index 279ac19..b2b60d8 100755 --- a/update +++ b/update @@ -16,27 +16,27 @@ 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_get_set = $DB->prepare + ("SELECT name, n_disc FROM dvd_set WHERE id = ?"); +my $st_add_set = $DB->prepare + ("INSERT INTO dvd_set (name, n_disc) VALUES (?, ?) RETURNING id"); +my $st_update_set = $DB->prepare + ("UPDATE dvd_set SET name = ?, n_disc = ? 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 $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 = ?, disc_id = NULL + 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; diff --git a/updpldb b/updpldb new file mode 100755 index 0000000..3dd4d75 --- /dev/null +++ b/updpldb @@ -0,0 +1,114 @@ +#! /usr/bin/perl -w + +use autodie qw{:all}; +use open ":utf8"; +use strict; + +use DBI; +use Encode qw{encode_utf8 decode_utf8}; +use Getopt::Std; + +BEGIN { + binmode STDIN, ":utf8"; + binmode STDOUT, ":utf8"; + binmode STDERR, ":utf8"; +} + +(my $prog = $0) =~ s:^.*/::; +sub HELP_MESSAGE ($;@) { + my ($fh) = @_; + print $fh "usage: $prog FILE ...\n"; +} + +my $bogusp = 0; +my %opt; +getopts("h", \%opt) or $bogusp = 1; +if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; } +@ARGV >= 1 or $bogusp = 1; +if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; } + +my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", + { AutoCommit => 0, + RaiseError => 1 }); + +my $R_STR = qr/ " (?: [^"\\]++ | \\ .)++ " /x; +my $R_INT = qr/ -?+ \d++ /x; +my $R_REAL = qr/ -?+ \d++ (?: \. \d++)?+ (?: [eE] [-+]?+ \d++)?+ /x; + +sub unquote ($) { + my ($s) = @_; + if ($s eq "-") { return undef; } + else { $s =~ s/^"(.*)"$/$1/; $s =~ s/\\(.)/$1/; return $s; } +} + +my $st_def_playlist = $DB->prepare + ("INSERT INTO playlist AS pl (name, n_entry) VALUES (?, 0) + ON CONFLICT (name) DO UPDATE SET name = pl.name WHERE pl.name = ?"); +my $st_finish_playlist = $DB->prepare + ("UPDATE playlist SET n_entry = ? WHERE name = ?"); +my $st_def_series = $DB->prepare + ("INSERT INTO series AS s (name, title) VALUES (?, ?) + ON CONFLICT (name) DO UPDATE SET title = ? WHERE s.name = ?"); + +my $st_def_media = $DB->prepare + ("INSERT INTO media AS m (path, title_number, start_chapter, end_chapter, + title, series_name, duration) + VALUES (?, ?, ?, ?, ?, ?, ?) + ON CONFLICT (path, title_number, start_chapter, end_chapter) + DO UPDATE SET title = ?, series_name = ?, duration = ? + WHERE m.path = ? AND m.title_number = ? AND + m.start_chapter = ? AND m.end_chapter = ? + RETURNING m.id"); + +my $st_def_entry = $DB->prepare + ("INSERT INTO playlist_entry AS e (list_name, entry, media_id) + VALUES (?, ?, ?) + ON CONFLICT (list_name, entry) + DO UPDATE SET media_id = ? + WHERE e.list_name = ? AND e.entry = ?"); +my $st_clear_entries = $DB->prepare + ("DELETE FROM playlist_entry WHERE list_name = ? AND entry >= ?"); + +my $playlist = undef; +my $index = 0; +sub wrap () { + defined $playlist or return; + $index or die "empty playlist"; + $st_finish_playlist->execute($index, $playlist); + $st_clear_entries->execute($playlist, $index); + $playlist = undef; $index = 0; +} +LINE: while (<>) { + chomp; + if (/^ \s* (?: ; | $) /x) { next LINE; } + elsif (/^ \s* LIST \s+ (\S+) \s* $/x) { + wrap; + $playlist = $1; + $st_def_playlist->execute($playlist, $playlist); + } elsif (!defined $playlist) { die "no playlist name"; } + elsif (/^ \s* SERIES \s+ (\S+) \s+ ($R_STR) \s* $/x) { + my ($stag, $title) = ($1, unquote($2)); + my $sname = $stag eq "-" ? $playlist : "$playlist/$stag"; + $st_def_series->execute($sname, $title, + $title, $sname); + } elsif (/^ \s* ENTRY + \s+ (\S+) \s+ ($R_STR) \s+ ($R_STR) + \s+ ($R_INT) \s+ ($R_INT) \s+ ($R_INT) + \s+ ($R_REAL) \s* $/x) { + my ($stag, $title, $path, $ttn, $loch, $hich, $dur) = + ($1, unquote($2), unquote($3), $4, $5, $6, $7); + + my $sname = $stag eq "-" ? $playlist : "$playlist/$stag"; + $st_def_media->execute($path, $ttn, $loch, $hich, $title, $sname, $dur, + $title, $sname, $dur, $path, $ttn, $loch, $hich); + my ($mid) = $st_def_media->fetchrow_array; + $st_def_media->finish; + + $st_def_entry->execute($playlist, $index, $mid, + $mid, $playlist, $index); + $index++; + } else { die "bad line $_"; } +} + +wrap; +$DB->commit; $DB->disconnect; diff --git a/upgrade-v2 b/upgrade-v2 index 1846791..f998f3c 100755 --- a/upgrade-v2 +++ b/upgrade-v2 @@ -10,12 +10,13 @@ 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"); +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 $st1 = $DB->prepare + ("INSERT INTO dvd (title, disc, path) VALUES (?, ?, ?)"); +while (my @r = $st->fetchrow_array) { my ($title, $ndisc, $path) = @r; print ";; $title [$ndisc]\n"; for (my $i = 0; $i < $ndisc; $i++) { $st1->execute($title, $i, $path); } diff --git a/upgrade-v3 b/upgrade-v3 index e3cc8bb..f2098c3 100755 --- a/upgrade-v3 +++ b/upgrade-v3 @@ -10,17 +10,14 @@ 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 (?, ?, ?, ?)"); +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 (;;) { -- 2.11.0