字幕検索 その2

字幕検索のつづき、データの入れ込み部分についてです。

字幕データの取り出しは、ぱぱネット（仮）さんのdumpeit最新版20110718を使わせてもらうことにしました。 TSの切り出しやEPGの取出しができたりと高機能なのでreq.plやepg.plを置き換えるのがスマートな気がしますが、既にts.plやepg.plを弄っちゃってるので、字幕データの取り出し機能だけ利用することにしました。

次のようなオプションでdumpeitを実行すると、拡張子が.subの字幕ファイルが生成されるので、その内容をDBに取り込むことにします。

dumpeit -f TSファイル

字幕ファイルからDBへのデータ取り込みは、次のように処理しています。

my $sql = make_sql('/path/to/xxxx.sub');
if (defined $sql) {
        store_data($sql);
}

sub make_sql()
{
        my ($file) = shift @_;

        if (!-e $file or -z $file) {
                return undef;
        }

        if ($file !~ /(\d{8})_Ch(\d+)\.sub$/) {
                return undef;
        }
        my $dt = $1;
        my $ch = $2;

        my $t = Time::Piece->strptime($dt, "%y%m%d%H");
        my $date = $t->ymd;


        my @sql = ();
        open my $fh, "<", $file or do {
                return undef;
        };

        my ($t1, $t2, $dv_id) = (undef, undef, undef);
        while (my $line = <$fh>) {
                chomp($line);
                my ($tmp, $msg) = split(/\s/, $line, 2);
                my ($tm, $pid_s) = split(/\./, $tmp);

                my $t = Time::Piece->strptime("$date $tm", "%F %T");

                if (!defined $dv_id or !($t1 <= $t and $t <= $t2)) {
                        my ($id, $stime, $etime) = find_event($ch, "$date $tm");
                        if (defined $id) {
                                $t1 = Time::Piece->strptime($stime, "%F %T");
                                $t2 = Time::Piece->strptime($etime, "%F %T");
                        }
                        $dv_id = $id;
                }

                push @sql, sprintf "($ch, '$date $tm', $pid_s, '$msg', %s)", $dv_id ? $dv_id : "NULL";
        }
        close($fh);

        if ($#sql < 0) {
                return undef;
        }

        my $sql_header = 'INSERT INTO da_caption ';
        $sql_header .= '(dc_ch, dp_start_time, dp_pid, dp_text, dv_id) VALUES ';

        return $sql_header . join(',', @sql);
}

データをそのまま入れているだけですが、検索結果を表示するときに番組情報を調べやすくするために、次の処理で番組データとの関連を追加しています。

sub find_event
{
        my ($ch, $date) = @_;

my $sql = <<END;
select
        e.dv_id,
        e.dv_start_time,
        addtime(e.dv_start_time, e.dv_duration)
from
        da_event as e
        inner join
                da_channel as c
        on
                e.dv_service_id = c.dc_service_id
where
        e.dv_start_time <= ?
and
        ? < addtime(e.dv_start_time, e.dv_duration)
and
        c.dc_ch = ?
order by
        e.dv_start_time
limit
        1
END

        my $dbh = dbiConnect();

        my ($sth, $rv) = ();
        eval {
                $sth = $dbh->prepare($sql);
                $rv = $sth->execute($date, $date, $ch);
        };

        return undef if ($@);
        my @events = ();
        while (my $ref = $sth->fetch()) {
                my ($dv_id, $start_time, $end_time) = @$ref;
                push(@events, [$dv_id, $start_time, $end_time]);
        }

        dbiDisconnect($dbh);

        if ($#events < 0) {
                return undef;
        }

        return @{$events[0]};
}

コミット処理は、次のようにしています。
字幕データ用のテーブルは、MyISAMなので意味が無い気がしますが、その他のテーブルはInnoDBで利用しているので処理を共通化しています。　

sub store_data
{
        my ($sql) = shift @_;

        my $dbh = dbiConnect();

        my $retry=3;
        while ($retry--) {
                eval {
                        my $ddh = $dbh->prepare($sql);
                        my $rv = $ddh->execute();

                        $dbh->commit();
                };
                last if (!$@);
                my $rnd = int(rand(10)) +1;
                my $st = 100000 * $rnd;
                printf STDERR "sleep %.1fs, retry(%d)", $st/1000000, $retry;
                usleep($st);
        }
        if ($retry < 0) {
                print STDERR "store-caption was give up.";
        }

        dbiDisconnect($dbh);

        return !$@;
}

あまり考えないでコーディングを始めてしまうので、テーブル構成がイマイチとか後から思ったりしたのですが…遊びなので結局そのままです。

こんな感じでスクリプト一つ作り、一時間毎にTSデータから字幕データをDBに取り込んで、約24日分の番組情報のデータ量は以下のようになっています。　

mysql> select count(*) from da_caption;
+----------+
| count(*) |
+----------+
|  1195794 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from da_event;
+----------+
| count(*) |
+----------+
|     8436 |
+----------+
1 row in set (0.00 sec)

mysql>

この状態で字幕検索を行っても、今のところ一秒以内に結果が返ってくるので、CGIからの読み出しでもストレス無く使えています。