| 1 | #! /usr/bin/perl -w |
| 2 | |
| 3 | use autodie qw{:all}; |
| 4 | use open ":utf8"; |
| 5 | use strict; |
| 6 | |
| 7 | use DBI; |
| 8 | use Encode qw{encode_utf8 decode_utf8}; |
| 9 | use Getopt::Std; |
| 10 | |
| 11 | BEGIN { |
| 12 | binmode STDIN, ":utf8"; |
| 13 | binmode STDOUT, ":utf8"; |
| 14 | binmode STDERR, ":utf8"; |
| 15 | } |
| 16 | |
| 17 | (my $prog = $0) =~ s:^.*/::; |
| 18 | sub HELP_MESSAGE ($;@) { |
| 19 | my ($fh) = @_; |
| 20 | print $fh "usage: $prog FILE ...\n"; |
| 21 | } |
| 22 | |
| 23 | my $bogusp = 0; |
| 24 | my %opt; |
| 25 | getopts("h", \%opt) or $bogusp = 1; |
| 26 | if ($opt{"h"}) { HELP_MESSAGE \*STDOUT; exit 0; } |
| 27 | @ARGV >= 1 or $bogusp = 1; |
| 28 | if ($bogusp) { HELP_MESSAGE \*STDERR; exit 2; } |
| 29 | |
| 30 | my $DB = DBI->connect("dbi:Pg:host=roadstar", "", "", |
| 31 | { AutoCommit => 0, |
| 32 | RaiseError => 1 }); |
| 33 | |
| 34 | my $R_STR = qr/ " (?: [^"\\]++ | \\ .)++ " /x; |
| 35 | my $R_INT = qr/ -?+ \d++ /x; |
| 36 | my $R_REAL = qr/ -?+ \d++ (?: \. \d++)?+ (?: [eE] [-+]?+ \d++)?+ /x; |
| 37 | |
| 38 | sub unquote ($) { |
| 39 | my ($s) = @_; |
| 40 | if ($s eq "-") { return undef; } |
| 41 | else { $s =~ s/^"(.*)"$/$1/; $s =~ s/\\(.)/$1/; return $s; } |
| 42 | } |
| 43 | |
| 44 | my $st_def_playlist = $DB->prepare |
| 45 | ("INSERT INTO playlist AS pl (name, n_entry) VALUES (?, 0) |
| 46 | ON CONFLICT (name) DO UPDATE SET name = pl.name WHERE pl.name = ?"); |
| 47 | my $st_finish_playlist = $DB->prepare |
| 48 | ("UPDATE playlist SET n_entry = ? WHERE name = ?"); |
| 49 | my $st_def_series = $DB->prepare |
| 50 | ("INSERT INTO series AS s (name, title) VALUES (?, ?) |
| 51 | ON CONFLICT (name) DO UPDATE SET title = ? WHERE s.name = ?"); |
| 52 | |
| 53 | my $st_def_media = $DB->prepare |
| 54 | ("INSERT INTO media AS m (path, title_number, start_chapter, end_chapter, |
| 55 | title, series_name, duration) |
| 56 | VALUES (?, ?, ?, ?, ?, ?, ?) |
| 57 | ON CONFLICT (path, title_number, start_chapter, end_chapter) |
| 58 | DO UPDATE SET title = ?, series_name = ?, duration = ? |
| 59 | WHERE m.path = ? AND m.title_number = ? AND |
| 60 | m.start_chapter = ? AND m.end_chapter = ? |
| 61 | RETURNING m.id"); |
| 62 | |
| 63 | my $st_def_entry = $DB->prepare |
| 64 | ("INSERT INTO playlist_entry AS e (list_name, entry, media_id) |
| 65 | VALUES (?, ?, ?) |
| 66 | ON CONFLICT (list_name, entry) |
| 67 | DO UPDATE SET media_id = ? |
| 68 | WHERE e.list_name = ? AND e.entry = ?"); |
| 69 | my $st_clear_entries = $DB->prepare |
| 70 | ("DELETE FROM playlist_entry WHERE list_name = ? AND entry >= ?"); |
| 71 | |
| 72 | my $playlist = undef; |
| 73 | my $index = 0; |
| 74 | sub wrap () { |
| 75 | defined $playlist or return; |
| 76 | $index or die "empty playlist"; |
| 77 | $st_finish_playlist->execute($index, $playlist); |
| 78 | $st_clear_entries->execute($playlist, $index); |
| 79 | $playlist = undef; $index = 0; |
| 80 | } |
| 81 | LINE: while (<>) { |
| 82 | chomp; |
| 83 | if (/^ \s* (?: ; | $) /x) { next LINE; } |
| 84 | elsif (/^ \s* LIST \s+ (\S+) \s* $/x) { |
| 85 | wrap; |
| 86 | $playlist = $1; |
| 87 | $st_def_playlist->execute($playlist, $playlist); |
| 88 | } elsif (!defined $playlist) { die "no playlist name"; } |
| 89 | elsif (/^ \s* SERIES \s+ (\S+) \s+ ($R_STR) \s* $/x) { |
| 90 | my ($stag, $title) = ($1, unquote($2)); |
| 91 | my $sname = $stag eq "-" ? $playlist : "$playlist/$stag"; |
| 92 | $st_def_series->execute($sname, $title, |
| 93 | $title, $sname); |
| 94 | } elsif (/^ \s* ENTRY |
| 95 | \s+ (\S+) \s+ ($R_STR) \s+ ($R_STR) |
| 96 | \s+ ($R_INT) \s+ ($R_INT) \s+ ($R_INT) |
| 97 | \s+ ($R_REAL) \s* $/x) { |
| 98 | my ($stag, $title, $path, $ttn, $loch, $hich, $dur) = |
| 99 | ($1, unquote($2), unquote($3), $4, $5, $6, $7); |
| 100 | |
| 101 | my $sname = $stag eq "-" ? $playlist : "$playlist/$stag"; |
| 102 | $st_def_media->execute($path, $ttn, $loch, $hich, $title, $sname, $dur, |
| 103 | $title, $sname, $dur, $path, $ttn, $loch, $hich); |
| 104 | my ($mid) = $st_def_media->fetchrow_array; |
| 105 | $st_def_media->finish; |
| 106 | |
| 107 | $st_def_entry->execute($playlist, $index, $mid, |
| 108 | $mid, $playlist, $index); |
| 109 | $index++; |
| 110 | } else { die "bad line $_"; } |
| 111 | } |
| 112 | |
| 113 | wrap; |
| 114 | $DB->commit; $DB->disconnect; |