--- /dev/null
+#! /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;
$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;
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) {
--- /dev/null
+#! /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; }
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;
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>);
{ 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;
--- /dev/null
+#! /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;
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); }
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 (;;) {
~mdw / dvddb / commitdiff