チェンジセット 111: trunk/install/perl

差分発生行の前後
無視リスト:
コミット日時:
2010/08/17 01:12:56 (14 年前)
コミッタ:
sorshi
ログメッセージ:

・放送波からのEPG番組表取得に対応。
・foltia_epgテーブル構成変更
・folcast.phpがSQLiteでまったく動作していなかったのを修正

ファイル:

凡例:

変更無し
追加
削除
更新
コピー
移動

  • trunk/install/perl/cron_foltia_dayly.sh

    r66 r111  
    1010# 
    1111 
    12 # XMLTVをつかってEPG番組表インポート 
     12#デジタル放送から一週間分のEPGを取得 
     13/home/foltia/perl/epgimport.pl long 
     14 
     15# XMLTVをつかってEPG番組表インポート(アナログ専用旧仕様) 
    1316# 
    14 /usr/bin/perl  /usr/bin/tv_grab_jp | /home/foltia/perl/xmltv2foltia.pl 
     17#/usr/bin/perl  /usr/bin/tv_grab_jp | /home/foltia/perl/xmltv2foltia.pl 
    1518# 2つの局設定使うような場合 
    1619#/usr/bin/perl  /usr/bin/tv_grab_jp --config-file ~/.xmltv/tv_grab_jp.conf.jcom  | /home/foltia/perl/xmltv2foltia.pl 

  • trunk/install/perl/db/Pg.pl

    r97 r111  
    2727'foltialib.changefilestatus.1' => "UPDATE foltia_subtitle SET filestatus = ?, lastupdate = now() WHERE pid = ?", 
    2828'foltialib.getfilestatus.1' => "SELECT filestatus FROM foltia_subtitle WHERE pid = ?", 
     29'foltialib.pid2sid.1' => "SELECT stationid FROM foltia_subtitle WHERE pid = ?", 
    2930 
    3031'getxml2db.1' => "SELECT count(*) FROM foltia_program WHERE tid = ?", 
    …… 
    7273'updatem2pfiletable.4' => "INSERT into foltia_mp4files values (?, ?)", 
    7374 
    74 'xmltv2foltia.chkerase.1' => "DELETE FROM foltia_epg WHERE startdatetime > ? AND ontvchannel = ?", 
    75 'xmltv2foltia.registdb.1' => "SELECT max(epgid) FROM foltia_epg", 
    76 'xmltv2foltia.registdb.2' => "INSERT INTO foltia_epg VALUES (?, ?, ?, ?, ?, ?, ?, ?)", 
     75'xmltv2foltia.replaceepg.1' => "SELECT * FROM foltia_epg WHERE enddatetime > ? AND startdatetime < ? AND ontvchannel = ?", 
     76'xmltv2foltia.commitdb.1' => "DELETE FROM foltia_epg WHERE epgid = ?", 
     77'xmltv2foltia.commitdb.2' => "INSERT INTO foltia_epg VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)" , 
     78 
     79'epgimport.1' => "SELECT count(*) FROM foltia_station WHERE stationid = ?" , 
     80'epgimport.2' => "SELECT digitalch,ontvcode FROM foltia_station WHERE stationid = ?" , 
     81'epgimport.3' => "SELECT digitalch,ontvcode FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 13 AND digitalch <= 62 ORDER BY digitalch ASC" , 
     82'epgimport.4' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 100 AND digitalch <= 222 ORDER BY digitalch ASC" , 
     83'epgimport.5' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 223 ORDER BY digitalch ASC" , 
     84'epgimport.6' => "SELECT  
     85 foltia_program.tid, stationname, foltia_program.title, 
     86 foltia_subtitle.countno, foltia_subtitle.subtitle, 
     87 foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin, 
     88 foltia_tvrecord.bitrate, foltia_subtitle.startoffset, 
     89 foltia_subtitle.pid, foltia_subtitle.epgaddedby, 
     90foltia_tvrecord.digital  
     91FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord 
     92WHERE foltia_tvrecord.tid = foltia_program.tid AND foltia_tvrecord.stationid = foltia_station .stationid AND foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid 
     93AND foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ?  
     94UNION  
     95SELECT  
     96 foltia_program.tid, stationname, foltia_program.title, 
     97 foltia_subtitle.countno, foltia_subtitle.subtitle, 
     98 foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, 
     99 foltia_tvrecord.bitrate,  foltia_subtitle.startoffset, 
     100 foltia_subtitle.pid,  foltia_subtitle.epgaddedby, 
     101foltia_tvrecord.digital  
     102FROM foltia_tvrecord 
     103LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) 
     104LEFT OUTER JOIN foltia_program on (foltia_tvrecord.tid = foltia_program.tid ) 
     105LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) 
     106WHERE foltia_tvrecord.stationid = 0 AND 
     107 foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ? " , 
    77108 
    78109); 

  • trunk/install/perl/digitaltvrecording.pl

    r99 r111  
    3232$extendrecendsec = 10;                                                  #recording end second.  
    3333#$startupsleeptime = 52;                                        #process wait(MAX60sec) 
    34 $startupsleeptime = 27;                                       #process wait(MAX60sec) 
     34$startupsleeptime = 32;                                       #process wait(MAX60sec) 
    3535#------------------------------- 
    3636 
    …… 
    373373                if ($recch == 333) { 
    374374                        $recch = "CS16";#333ch：アニメシアターX(AT-X)  
    375                 }elsif($recch == 330){ 
    376                         $recch = "CS22";#330ch：キッズステーション  
     375                #}elsif($recch == 330){ 
     376                #       $recch = "CS22";#330ch：キッズステーション #HD化により2010/4変更  
     377                }elsif($recch == 335){ 
     378                        $recch = "CS8";#335ch：キッズステーション HD 
    377379                }elsif($recch == 332){ 
    378380                        $recch = "CS20";#332ch：アニマックス  

  • trunk/install/perl/folprep.pl

    r94 r111  
    2929require "foltialib.pl"; 
    3030 
    31 #XMLゲット & DB更新 
    32 system("$toolpath/perl/getxml2db.pl"); 
     31 
     32#PID探し 
     33my $pid = $ARGV[0]; 
    3334 
    3435#引き数がアルか? 
    35 $pid = $ARGV[0] ; 
    3636if ($pid eq "" ){ 
    3737        #引き数なし出実行されたら、終了 
    …… 
    4040} 
    4141 
    42 #PID探し 
    43 $pid = $ARGV[0]; 
     42my $stationid = ""; 
     43if ($pid <= 0){ 
     44        #EPG更新 & DB更新 
     45        $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; 
     46        $stationid = &pid2sid($pid); 
     47        system("$toolpath/perl/epgimport.pl $stationid"); 
     48}else{ 
     49        #XMLゲット & DB更新 
     50        system("$toolpath/perl/getxml2db.pl"); 
     51} 
    4452 
    4553#キュー再投入 
    46        &writelog("folprep  $toolpath/perl/addpidatq.pl $pid"); 
     54&writelog("folprep  $toolpath/perl/addpidatq.pl $pid"); 
    4755system("$toolpath/perl/addpidatq.pl $pid"); 
    4856 

  • trunk/install/perl/foltialib.pl

    r94 r111  
    381381}#endsub makemp4dir 
    382382 
    383  
    384  
    385  
     383sub pid2sid{ 
     384#番組IDからStation IDを取得 
     385my $pid = $_[0]; 
     386my $sth; 
     387    $sth = $dbh->prepare($stmt{'foltialib.pid2sid.1'}); 
     388    $sth->execute($pid); 
     389my @statusinfo = $sth->fetchrow_array; 
     390my $sid  = $statusinfo[0]; 
     391 
     392if ($sid eq ""){ 
     393        return  0 ; 
     394}else{ 
     395        return $sid; 
     396} 
     397 
     398}#end sub pid2sid 
    386399 
    387400 

  • trunk/install/perl/ipodtranscode.pl

    r97 r111  
    306306        # 中間ファイル消す 
    307307        &changefilestatus($pid,$FILESTATUSTRANSCODECOMPLETE); 
     308        &updatemp4file(); 
     309        }else{ 
     310                &writelog("ipodtranscode ERR ; Fail.Giving up!  MAQ${mp4filenamestring}.MP4"); 
     311                &changefilestatus($pid,999); 
     312        } 
    308313        unlink("${filenamebody}_HD.m2t"); 
    309314        unlink("${filenamebody}_tss.m2t"); 
    …… 
    311316        unlink("$filenamebody.wav"); 
    312317        unlink("$filenamebody.base.mp4"); 
    313          
    314         &updatemp4file(); 
    315         }else{ 
    316                 &writelog("ipodtranscode ERR ; Fail MAQ${mp4filenamestring}.MP4"); 
    317         } 
     318 
    318319} 
    319320 

  • trunk/install/perl/schedulecheck.pl

    r94 r111  
    5353}#while 
    5454 
    55  
     55#EPG更新 
     56system("$toolpath/perl/epgimport.pl"); 
    5657} 

  • trunk/install/perl/xmltv2foltia.pl

    r94 r111  
    44# http://www.dcc-jpl.com/soft/foltia/ 
    55# 
    6 #xmltv2foltia.pl  
    7 #XMLTV日本語版の出力するXMLを受け取り、EPGデータベースに挿入します。 
    8 # 
    9 #↓本家に取り込まれたっぽい(未確認) 
    10 # 
    11 #XMLTVは 
    12 # http://www.systemcreate-inc.com/gsxr/pc/mythtv.html#patches 
    13 #のパッチをあてたものを想定しています。オリジナルと比較して、サブタイトルや内容など 
    14 #より詳細な内容を取得できます。 
    15 # 
    16 # usage;perl  /usr/bin/tv_grab_jp | ./xmltv2foltia.pl  
    17 # 
     6# xmltv2foltia.pl  
     7# 
     8# XMLTV日本語版形式のXMLを受け取り、EPGデータベースに挿入します。 
     9# アナログ時代はXMLTVを利用していましたが、現在はepgimport.plを使用します。 
     10# 
     11# usage 
     12# cat /tmp/__27-epg.xml | /home/foltia/perl/xmltv2foltia.pl 
    1813# 
    1914# DCC-JPL Japan/foltia project 
    …… 
    2621# http://www.lr.pi.titech.ac.jp/~abekawa/perl/perl_unicode.html 
    2722use Jcode; 
    28 # use Data::Dumper;  
     23use Data::Dumper;  
    2924use Time::Local; 
    3025use DBI; 
    …… 
    4338$today = strftime("%Y%m%d", localtime); 
    4439$todaytime = strftime("%Y%m%d%H%M", localtime); 
     40@deleteepgid = (); 
    4541 
    4642# DB Connect 
    4743$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; 
    48 $dbh->{AutoCommit} = 0; 
    4944 
    5045while(<>){ 
    5146#print $_; 
     47s/\xef\xbd\x9e/\xe3\x80\x9c/g; #wavedash 
     48s/\xef\xbc\x8d/\xe2\x88\x92/g; #hyphenminus 
     49s/&#([0-9A-Fa-f]{2,6});/(chr($1))/eg; #'遊戯王5D&#039;s'とかの数値参照対応を 
     50 
    5251Jcode::convert(\$_,'euc','utf8'); 
    5352#    from_to($_, "utf8","euc-jp"); 
    …… 
    6968        eval("$_"); 
    7069#print Dumper($_) ; 
    71  
    72 }elsif(/<display-name lang=\"ja_JP/){ 
    73         s/^[\s]*//gio; 
    74         chomp(); 
    75         $channel{ja}  = &removetag($_); 
    76         #print Dumper($_) ; 
    77         #print "$result  \n"; 
    78  
    7970 
    8071}elsif(/<display-name lang=\"ja_JP/){ 
    …… 
    136127        chomp(); 
    137128        $item{title}  = &removetag($_); 
     129        $item{title} =~ s/【.*】//g;#【解】とか 
     130        $item{title} =~ s/\[.*\]//g;#[二]とか  
    138131        #print Dumper($_) ; 
    139132        #print "$result  \n"; 
    …… 
    188181#登録処理はココで 
    189182#&writelog("xmltv2foltia DEBUG call chkerase $item{'start'},$item{'channel'}"); 
    190  
    191         &chkerase($item{'start'}, $item{'channel'}); 
     183#旧仕様    #&chkerase($item{'start'}, $item{'channel'}); 
     184        &replaceepg($item{'start'}, $item{'channel'},$item{'stop'}); 
    192185        if ($item{'subtitle'} ne "" ){ 
    193186            $registdesc = $item{'subtitle'}." ".$item{'desc'}; 
    …… 
    215208}# endif 
    216209}# while 
    217  
    218 $dbh->commit; 
     210&commitdb; 
     211 
    219212 
    220213#end 
    221214################ 
    222215 
    223 sub chkerase{ 
    224 # xmltvからきた日付とチャンネルをfoltia epgと比較 
     216sub replaceepg{ 
     217#消すEPGのIDを配列に追加します 
    225218my $foltiastarttime = $_[0]; # 14桁 
    226219my $ontvepgchannel =  $_[1]; 
    227 my $epgstartdate = substr($foltiastarttime,0,8); # 8桁　20050807 
    228 my  @epgcounts = ""; 
    229 my $DBQuery = ""; 
    230  
    231 #if ($currentworkdate eq "" ){#初回起動なら 
    232 if ( $currentworkch ne $ontvepgchannel){ 
    233  
    234  
    235 if ($epgstartdate >= $today){# xmltvtvから今日以降のデータが来ていれば 
    236 my $epgstartdatetime = $today * 10000 ; # 200508070000 12桁 
    237 # 新規に入る予定の未来の番組表、全部いったん消す 
    238 # $DBQuery =  "DELETE from foltia_epg where startdatetime > $epgstartdatetime AND ontvchannel = '$ontvepgchannel' "; 
    239             $sth = $dbh->prepare($stmt{'xmltv2foltia.chkerase.1'}); 
    240             $sth->execute($todaytime, $ontvepgchannel); 
    241             &writelog("xmltv2foltia DELETE EPG $epgstartdatetime:$stmt{'xmltv2foltia.chkerase.1'}"); 
    242 #$currentworkdate = "$today"; 
    243 $currentworkch = $ontvepgchannel ; 
    244 }else{ 
    245         &writelog("xmltv2foltia ERROR EPG INVALID:$epgstartdate:$today"); 
    246         #exit(); 
    247 }# endif xmltvtvから今日のデータが来ていれば 
    248 }#end if 初回起動なら 
    249  
    250 } 
     220my $foltiaendtime = $_[2]; # 14桁 
     221my @data = (); 
     222 
     223$foltiastarttime = substr($foltiastarttime,0,12); # 12桁　200508072254 
     224$foltiaendtime   = substr($foltiaendtime,0,12); # 12桁　200508072355 
     225 
     226$sth = $dbh->prepare($stmt{'xmltv2foltia.replaceepg.1'}); 
     227$sth->execute($foltiastarttime , $foltiaendtime , $ontvepgchannel); 
     228 
     229while (@data = $sth->fetchrow_array()) { 
     230        push(@deleteepgid,$data[0]); 
     231        #&writelog("xmltv2foltia DEBUG push(\@deleteepgid,$data[0]);"); 
     232}#end while  
     233}#endsub replaceepg 
     234 
    251235sub registdb{ 
    252236my $foltiastarttime = $_[0]; 
    …… 
    258242 
    259243#&writelog("xmltv2foltia DEBUG $foltiastarttime:$foltiaendtime"); 
    260  
    261   
    262244$foltiastarttime = substr($foltiastarttime,0,12); 
    263245$foltiaendtime = substr($foltiaendtime,0,12); 
    264246 
    265 if($foltiastarttime > $todaytime){ 
    266          
    267       $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.1'}); 
    268               $sth->execute(); 
    269        @currentepgid = $sth->fetchrow_array; 
    270         
    271       if ($currentepgid[0] < 1 ){ 
    272               $newepgid = 1; 
    273       }else{ 
    274               $newepgid = $currentepgid[0];  
    275               $newepgid++;  
    276       } 
     247if($foltiaendtime > $todaytime){ 
     248# epgidはAUTOINCREMENTに変更した #2010/8/10  
     249#     $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.1'}); 
     250#             $sth->execute(); 
     251#      @currentepgid = $sth->fetchrow_array; 
     252#       
     253#     if ($currentepgid[0] < 1 ){ 
     254#             $newepgid = 1; 
     255#     }else{ 
     256#             $newepgid = $currentepgid[0];  
     257#             $newepgid++;  
     258#     } 
    277259#&writelog("xmltv2foltia DEBUG $currentepgid[0] /  $newepgid"); 
    278260my $lengthmin = &calclength($foltiastarttime , $foltiaendtime); 
    279 #       $newepgid = $dbh->quote($newepgid ); 
    280 #       $foltiastarttime = $dbh->quote($foltiastarttime); 
    281 #       $foltiaendtime = $dbh->quote($foltiaendtime ); 
    282 #       $lengthmin = $dbh->quote($lengthmin ); 
    283 #       $channel = $dbh->quote($channel ); 
    284 #       $title = $dbh->quote($title); 
    285 #       $desc = $dbh->quote($desc); 
    286 #       $category = $dbh->quote($category); 
    287  
    288         $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.2'}); 
    289         $sth->execute($newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category) || 
    290             warn "error: $newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category\n"; 
    291  
     261 
     262#print "xmltv2foltia DEBUG :INSERT INTO foltia_epg VALUES ($newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category)\n"; 
     263push (@foltiastarttime,$foltiastarttime); 
     264push (@foltiaendtime,$foltiaendtime);  
     265push (@lengthmin,$lengthmin);  
     266push (@channel,$channel);  
     267push (@title,$title);  
     268push (@desc,$desc); 
     269push (@category,$category); 
     270#       $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.2'}); 
     271#       $sth->execute($newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category) || warn "error: $newepgid, $foltiastarttime, $foltiaendtime, $lengthmin, $channel, $title, $desc, $category\n"; 
    292272# &writelog("xmltv2foltia DEBUG $DBQuery"); 
    293  
    294273}else{ 
    295274#&writelog("xmltv2foltia DEBUG SKIP $foltiastarttime:$foltiaendtime"); 
    296275}#未来じゃなければ挿入しない 
    297276 
     277}#end sub registdb 
     278 
     279sub commitdb{ 
     280$dbh->{AutoCommit} = 0; 
     281$dbh->do('BEGIN'); 
     282#print Dumper(\@dbarray); 
     283my $loopcount = @foltiastarttime; 
     284my $i = 0; 
     285 
     286#削除 
     287foreach $delid (@deleteepgid){ 
     288        $sth = $dbh->prepare($stmt{'xmltv2foltia.commitdb.1'}); 
     289        $sth->execute( $delid ) || warn "$delid\n"; 
     290#       &writelog("xmltv2foltia DEBUG : DELETE FROM foltia_epg WHERE epgid = $delid"); 
    298291} 
    299  
    300  
    301  
    302  
    303  
    304  
    305  
     292#追加 
     293for ($i=0;$i<$loopcount;$i++){ 
     294        $sth = $dbh->prepare($stmt{'xmltv2foltia.commitdb.2'}); 
     295        $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"; 
     296#&writelog("xmltv2foltia DEBUG : INSERT INTO foltia_epg VALUES ( NULL , $foltiastarttime[$i],$foltiaendtime[$i], $lengthmin[$i], $channel[$i], $title[$i], $desc[$i], $category[$i])"); 
     297}# end for 
     298$dbh->do('COMMIT'); 
     299$dbh->{AutoCommit} = 1; 
     300}#end sub commitdb 
    306301 
    307302sub removetag(){ 
track feed