Index: trunk/install/perl/xmltv2foltia.pl =================================================================== --- trunk/install/perl/xmltv2foltia.pl (リビジョン 134) +++ trunk/install/perl/xmltv2foltia.pl (リビジョン 1) @@ -4,11 +4,13 @@ # http://www.dcc-jpl.com/soft/foltia/ # -# xmltv2foltia.pl -# -# XMLTV日本語版形式のXMLを受け取り、EPGデータベースに挿入します。 -# アナログ時代はXMLTVを利用していましたが、現在はepgimport.plを使用します。 -# -# usage -# cat /tmp/__27-epg.xml | /home/foltia/perl/xmltv2foltia.pl +#xmltv2foltia.pl +#XMLTV日本語版の出力するXMLを受け取り、EPGデータベースに挿入します。 +#XMLTVは +# http://www.systemcreate-inc.com/gsxr/pc/mythtv.html#patches +#のパッチをあてたものを想定しています。オリジナルと比較して、サブタイトルや内容など +#より秀才な内容を取得できます。 +# +# usage;perl /usr/bin/tv_grab_jp | ./xmltv2foltia.pl +# # # DCC-JPL Japan/foltia project @@ -16,18 +18,17 @@ # -#use LWP::Simple; +use LWP::Simple; #use Encode qw(from_to); #use encoding 'euc-jp', STDIN=>'utf8', STDOUT=>'euc-jp' ; # 標準入力:utf8 # http://www.lr.pi.titech.ac.jp/~abekawa/perl/perl_unicode.html use Jcode; -#use Data::Dumper; +# use Data::Dumper; use Time::Local; use DBI; use DBD::Pg; -use DBD::SQLite; $path = $0; $path =~ s/xmltv2foltia.pl$//i; -if ($path ne "./"){ +if ($pwd ne "./"){ push( @INC, "$path"); } @@ -36,17 +37,14 @@ $currentworkdate = "" ; $currentworkch = "" ; -$today = strftime("%Y%m%d", localtime); -$todaytime = strftime("%Y%m%d%H%M", localtime); -@deleteepgid = (); +$today =`date "+%Y%m%d"`; +$todaytime =`date "+%Y%m%d%H%M"`; # DB Connect -$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; + my $data_source = sprintf("dbi:%s:dbname=%s;host=%s;port=%d", + $DBDriv,$DBName,$DBHost,$DBPort); + $dbh = DBI->connect($data_source,$DBUser,$DBPass) ||die $DBI::error;; while(<>){ #print $_; -s/\xef\xbd\x9e/\xe3\x80\x9c/g; #wavedash -s/\xef\xbc\x8d/\xe2\x88\x92/g; #hyphenminus -s/&#([0-9A-Fa-f]{2,6});/(chr($1))/eg; #'遊戯王5D's'とかの数値参照対応を - Jcode::convert(\$_,'euc','utf8'); # from_to($_, "utf8","euc-jp"); @@ -68,4 +66,12 @@ eval("$_"); #print Dumper($_) ; + +}elsif(/【無】こんなミニ番組があり、タイトル空白になってしまうことがある - $item{title} = $titlebackup; - } #print Dumper($_) ; #print "$result \n"; @@ -150,37 +147,4 @@ chomp(); $item{category} = &removetag($_); - - if ($item{category} =~ /情報/){ - $item{category} = "information"; - }elsif ($item{category} =~ /趣味・実用/){ - $item{category} = "hobby"; - }elsif ($item{category} =~ /教育/){ - $item{category} = "education"; - }elsif ($item{category} =~ /音楽/){ - $item{category} = "music"; - }elsif ($item{category} =~ /演劇/){ - $item{category} = "stage"; - }elsif ($item{category} =~ /映画/){ - $item{category} = "cinema"; - }elsif ($item{category} =~ /バラエティ/){ - $item{category} = "variety"; - }elsif ($item{category} =~ /ニュース・報道/){ - $item{category} = "news"; - }elsif ($item{category} =~ /ドラマ/){ - $item{category} = "drama"; - }elsif ($item{category} =~ /ドキュメンタリー・教養/){ - $item{category} = "documentary"; - }elsif ($item{category} =~ /スポーツ/){ - $item{category} = "sports"; - }elsif ($item{category} =~ /キッズ/){ - $item{category} = "kids"; - }elsif ($item{category} =~ /アニメ・特撮/){ - $item{category} = "anime"; - }elsif ($item{category} =~ /その他/){ - $item{category} = "etc"; - }else{ - $item{category} = "etc"; - } - #print Dumper($_) ; #print "$result \n"; @@ -189,13 +153,13 @@ }elsif(/<\/programme>/){ #登録処理はココで -#&writelog("xmltv2foltia DEBUG call chkerase $item{'start'},$item{'channel'}"); -#旧仕様 #&chkerase($item{'start'}, $item{'channel'}); - &replaceepg($item{'start'}, $item{'channel'},$item{'stop'}); - if ($item{'subtitle'} ne "" ){ - $registdesc = $item{'subtitle'}." ".$item{'desc'}; +#&writelog("xmltv2foltia DEBUG call chkerase $item{start},$item{channel}"); + +&chkerase($item{start},$item{channel}); +if ($item{subtitle} ne "" ){ + $registdesc = $item{subtitle}." ".$item{desc}; }else{ - $registdesc = $item{'desc'}; + $registdesc = $item{desc}; } - ®istdb($item{'start'},$item{'stop'},$item{'channel'},$item{'title'},$registdesc ,$item{'category'}); +®istdb($item{start},$item{stop},$item{channel},$item{title},$registdesc ,$item{category}); # print "$item{start} @@ -217,38 +181,34 @@ }# endif }# while -&commitdb; - - -#end -################ - -sub replaceepg{ -#消すEPGのIDを配列に追加します + +sub chkerase{ +# xmltvからきた日付とチャンネルをfoltia epgと比較 my $foltiastarttime = $_[0]; # 14桁 my $ontvepgchannel = $_[1]; -my $foltiaendtime = $_[2]; # 14桁 -my @data = (); - -$foltiastarttime = substr($foltiastarttime,0,12); # 12桁　200508072254 -$foltiaendtime = substr($foltiaendtime,0,12); # 12桁　200508072355 - -$sth = $dbh->prepare($stmt{'xmltv2foltia.replaceepg.1'}); -my $now = &epoch2foldate(time()); -$sth->execute($foltiastarttime , $foltiaendtime , $ontvepgchannel); -while (@data = $sth->fetchrow_array()) { - push(@deleteepgid,$data[0]); - #&writelog("xmltv2foltia DEBUG push(\@deleteepgid,$data[0]);"); -}#end while - -#上書きを消す -$sth = $dbh->prepare($stmt{'xmltv2foltia.replaceepg.2'}); -$sth->execute($foltiastarttime , $foltiaendtime , $ontvepgchannel); -while (@data = $sth->fetchrow_array()) { - push(@deleteepgid,$data[0]); - #&writelog("xmltv2foltia DEBUG push(\@deleteepgid,$data[0]);"); -}#end while - -}#endsub replaceepg - +my $epgstartdate = substr($foltiastarttime,0,8); # 8桁　20050807 +my @epgcounts = ""; +my $DBQuery = ""; + +#if ($currentworkdate eq "" ){#初回起動なら +if ( $currentworkch ne $ontvepgchannel){ + + +if ($epgstartdate >= $today){# xmltvtvから今日以降のデータが来ていれば +my $epgstartdatetime = $today * 10000 ; # 200508070000 12桁 +# 新規に入る予定の未来の番組表、全部いったん消す +# $DBQuery = "DELETE from foltia_epg where startdatetime > $epgstartdatetime AND ontvchannel = '$ontvepgchannel' "; + $DBQuery = "DELETE from foltia_epg where startdatetime > $todaytime AND ontvchannel = '$ontvepgchannel' "; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); +&writelog("xmltv2foltia DELETE EPG $epgstartdatetime:$DBQuery"); +#$currentworkdate = "$today"; +$currentworkch = $ontvepgchannel ; +}else{ + &writelog("xmltv2foltia ERROR EPG INVALID:$epgstartdate:$today"); + #exit(); +}# endif xmltvtvから今日のデータが来ていれば +}#end if 初回起動なら + +} sub registdb{ my $foltiastarttime = $_[0]; @@ -259,67 +219,54 @@ my $category = $_[5]; -#Encode::JP::H2Z::z2h(\$string); -$title = jcode($title)->tr('Ａ-Ｚａ-ｚ０-９！＃＄％＆（）＊＋，−．／：；＜＝＞？＠［＼］＾＿｀｛｜｝　','A-Za-z0-9!#$%&()*+,-./:;<=>?@[\]^_`{|} '); -$desc = jcode($desc)->tr('Ａ-Ｚａ-ｚ０-９！＃＄％＆（）＊＋，−．／：；＜＝＞？＠［＼］＾＿｀｛｜｝　','A-Za-z0-9!#$%&()*+,-./:;<=>?@[\]^_`{|} '); -#$title = jcode($title)->tr('Ａ-Ｚａ-ｚ０-９！＃＄％＆（）＊＋，−．／：；＜＝＞？＠［＼］＾＿｀｛｜｝','A-Za-z0-9!#$%&()*+,-./:;<=>?@[\]^_`{|}'); -#$desc = jcode($desc)->tr('Ａ-Ｚａ-ｚ０-９！＃＄％＆（）＊＋，−．／：；＜＝＞？＠［＼］＾＿｀｛｜｝','A-Za-z0-9!#$%&()*+,-./:;<=>?@[\]^_`{|}'); - #&writelog("xmltv2foltia DEBUG $foltiastarttime:$foltiaendtime"); + + $foltiastarttime = substr($foltiastarttime,0,12); $foltiaendtime = substr($foltiaendtime,0,12); -#if($foltiaendtime > $todaytime){#電波に乗ってきた情報は無条件更新 -# epgidはAUTOINCREMENTに変更した #2010/8/10 -# $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.1'}); -# $sth->execute(); -# @currentepgid = $sth->fetchrow_array; -# -# if ($currentepgid[0] < 1 ){ -# $newepgid = 1; -# }else{ -# $newepgid = $currentepgid[0]; -# $newepgid++; -# } +if($foltiastarttime > $todaytime){ + + my $DBQuery = "SELECT max(epgid) FROM foltia_epg "; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); + @currentepgid = $sth->fetchrow_array; + + if ($currentepgid[0] < 1 ){ + $newepgid = 1; + }else{ + $newepgid = $currentepgid[0]; + $newepgid++; + } #&writelog("xmltv2foltia DEBUG $currentepgid[0] / $newepgid"); my $lengthmin = &calclength($foltiastarttime , $foltiaendtime); - -#print "xmltv2foltia DEBUG :INSERT INTO foltia_epg VALUES ($newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category)\n"; -push (@foltiastarttime,$foltiastarttime); -push (@foltiaendtime,$foltiaendtime); -push (@lengthmin,$lengthmin); -push (@channel,$channel); -push (@title,$title); -push (@desc,$desc); -push (@category,$category); -# $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.2'}); -# $sth->execute($newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category) || warn "error: $newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category\n"; +$newepgid = $dbh->quote($newepgid ); +$foltiastarttime = $dbh->quote($foltiastarttime); +$foltiaendtime = $dbh->quote($foltiaendtime ); +$lengthmin = $dbh->quote($lengthmin ); +$channel = $dbh->quote($channel ); +$title = $dbh->quote($title); +$desc = $dbh->quote($desc); +$category = $dbh->quote($category); + +$DBQuery = "insert into foltia_epg values ($newepgid,$foltiastarttime,$foltiaendtime,$lengthmin,$channel,$title,$desc,$category)"; +# $DBQuery = $dbh->quote($DBQuery); + $sth = $dbh->prepare($DBQuery); + $sth->execute(); + + # &writelog("xmltv2foltia DEBUG $DBQuery"); -#}else{ + +}else{ #&writelog("xmltv2foltia DEBUG SKIP $foltiastarttime:$foltiaendtime"); -#}#未来じゃなければ挿入しない - -}#end sub registdb - -sub commitdb{ -$dbh->{AutoCommit} = 0; -#print Dumper(\@dbarray); -my $loopcount = @foltiastarttime; -my $i = 0; - -#削除 -foreach $delid (@deleteepgid){ - $sth = $dbh->prepare($stmt{'xmltv2foltia.commitdb.1'}); - $sth->execute( $delid ) || warn "$delid\n"; - #&writelog("xmltv2foltia DEBUG $stmt{'xmltv2foltia.commitdb.1'}/$delid"); +}#未来じゃなければ挿入しない + } -#追加 -for ($i=0;$i<$loopcount;$i++){ - $sth = $dbh->prepare($stmt{'xmltv2foltia.commitdb.2'}); - $sth->execute( $foltiastarttime[$i],$foltiaendtime[$i], $lengthmin[$i], $channel[$i], $title[$i], $desc[$i], $category[$i]) || warn "error: $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category\n"; - #&writelog("xmltv2foltia DEBUG $stmt{'xmltv2foltia.commitdb.2'}/$foltiastarttime[$i],$foltiaendtime[$i], $lengthmin[$i], $channel[$i], $title[$i], $desc[$i], $category[$i]"); -}# end for -$dbh->commit; -$dbh->{AutoCommit} = 1; -}#end sub commitdb + + + + + + + sub removetag(){