Index: trunk/install/perl/xmltv2foltia.pl =================================================================== --- trunk/install/perl/xmltv2foltia.pl (リビジョン 94) +++ trunk/install/perl/xmltv2foltia.pl (リビジョン 111) @@ -4,16 +4,11 @@ # http://www.dcc-jpl.com/soft/foltia/ # -#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 -# +# xmltv2foltia.pl +# +# XMLTV日本語版形式のXMLを受け取り、EPGデータベースに挿入します。 +# アナログ時代はXMLTVを利用していましたが、現在はepgimport.plを使用します。 +# +# usage +# cat /tmp/__27-epg.xml | /home/foltia/perl/xmltv2foltia.pl # # DCC-JPL Japan/foltia project @@ -26,5 +21,5 @@ # 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; @@ -43,11 +38,15 @@ $today = strftime("%Y%m%d", localtime); $todaytime = strftime("%Y%m%d%H%M", localtime); +@deleteepgid = (); # DB Connect $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; -$dbh->{AutoCommit} = 0; 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"); @@ -69,12 +68,4 @@ eval("$_"); #print Dumper($_) ; - -}elsif(/commit; +&commitdb; + #end ################ -sub chkerase{ -# xmltvからきた日付とチャンネルをfoltia epgと比較 +sub replaceepg{ +#消すEPGのIDを配列に追加します my $foltiastarttime = $_[0]; # 14桁 my $ontvepgchannel = $_[1]; -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' "; - $sth = $dbh->prepare($stmt{'xmltv2foltia.chkerase.1'}); - $sth->execute($todaytime, $ontvepgchannel); - &writelog("xmltv2foltia DELETE EPG $epgstartdatetime:$stmt{'xmltv2foltia.chkerase.1'}"); -#$currentworkdate = "$today"; -$currentworkch = $ontvepgchannel ; -}else{ - &writelog("xmltv2foltia ERROR EPG INVALID:$epgstartdate:$today"); - #exit(); -}# endif xmltvtvから今日のデータが来ていれば -}#end if 初回起動なら - -} +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'}); +$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 + sub registdb{ my $foltiastarttime = $_[0]; @@ -258,50 +242,61 @@ #&writelog("xmltv2foltia DEBUG $foltiastarttime:$foltiaendtime"); - - $foltiastarttime = substr($foltiastarttime,0,12); $foltiaendtime = substr($foltiaendtime,0,12); -if($foltiastarttime > $todaytime){ - - $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($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++; +# } #&writelog("xmltv2foltia DEBUG $currentepgid[0] / $newepgid"); my $lengthmin = &calclength($foltiastarttime , $foltiaendtime); -# $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); - - $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"; - + +#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"; # &writelog("xmltv2foltia DEBUG $DBQuery"); - }else{ #&writelog("xmltv2foltia DEBUG SKIP $foltiastarttime:$foltiaendtime"); }#未来じゃなければ挿入しない +}#end sub registdb + +sub commitdb{ +$dbh->{AutoCommit} = 0; +$dbh->do('BEGIN'); +#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 : DELETE FROM foltia_epg WHERE epgid = $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 : INSERT INTO foltia_epg VALUES ( NULL , $foltiastarttime[$i],$foltiaendtime[$i], $lengthmin[$i], $channel[$i], $title[$i], $desc[$i], $category[$i])"); +}# end for +$dbh->do('COMMIT'); +$dbh->{AutoCommit} = 1; +}#end sub commitdb sub removetag(){