Index: trunk/install/perl/getxml2db.pl =================================================================== --- trunk/install/perl/getxml2db.pl (リビジョン 109) +++ trunk/install/perl/getxml2db.pl (リビジョン 1) @@ -19,10 +19,8 @@ use DBI; use DBD::Pg; -use DBD::SQLite; -use Digest::MD5 qw(md5_hex); $path = $0; $path =~ s/getxml2db.pl$//i; -if ($path ne "./"){ +if ($pwd ne "./"){ push( @INC, "$path"); } @@ -40,43 +38,28 @@ } -# http://sites.google.com/site/syobocal/spec/cal_chk-php -#if ($ARGV[0] eq "long"){ -# $uri="http://cal.syoboi.jp/cal_chk.php"; -# #$uri="http://syobocal.orz.hm/cal_chk.php"; -# &writelog("getxml2db use long mode."); -#}else{ -# $uri="http://cal.syoboi.jp/cal_chk.xml"; -# #$uri="http://syobocal.orz.hm/cal_chk.xml"; -#} -$uri = "http://cal.syoboi.jp/cal_chk.php?days="; -$uri .= ($ARGV[0] eq "long")? 14: 7; - -$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; - -$dbh->{AutoCommit} = 0; - -# If-Modified-Since使うように変更#2008/11/14 -my $CacheDir = '/tmp/shobocal'; -if (! -e $CacheDir) { - mkdir $CacheDir or die "cannot create $CacheDir: $!"; -} -my $cache = sprintf("%s/%s.xml", $CacheDir, Digest::MD5::md5_hex($uri)); -LWP::Simple::mirror($uri, $cache) or die "cannot get content from $uri"; -open(SHOBO, "<$cache"); -my (@line) = ; -close(SHOBO); -#my ($content) = get("$uri"); -#if ($content eq ""){ -#&writelog("getxml2db no responce from $uri, exit:"); -# exit;#しょぼかるが落ちているなど -#} -#my (@line) = split(/\n/, $content); +if ($ARGV[0] eq "long"){ + $uri="http://cal.syoboi.jp/cal_chk.php"; + &writelog("getxml2db use long mode."); +}else{ + $uri="http://cal.syoboi.jp/cal_chk.xml"; +} + + + 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;; + +my ($content) = get("$uri"); +if ($content eq ""){ +&writelog("getxml2db no responce from $uri, exit:"); + exit;#しょぼかるが落ちているなど +} + +my (@line) = split(/\n/, $content); foreach(@line){ -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'); + +Jcode::convert(\$_,'euc'); # @@ -85,9 +68,7 @@ s/\"\/>/\" /i; s/\"[\s]/\";\n/gio; -s/\'/\\'/gio; s/\"/\'/gio; -#s/[\w]*=/\$item{$&}=/gio; -#s/\=}=/}=/gio; -s/(\w+)=/\$item{$1}=/gio;#by foltiaBBS +s/[\w]*=/\$item{$&}=/gio; +s/\=}=/}=/gio; #$item{PID}='21543'; @@ -102,35 +83,37 @@ #$item{ProgComment}=''; eval("$_"); -#Jcode::convert(\$item{Title},'euc'); - +Jcode::convert(\$item{Title},'euc'); $programtitlename = $item{Title}; -$programtitlename =~ s/\<\;//gi; -$programtitlename =~ s/\&\;/\&/gi; -# $programtitle = $dbh->quote($programtitlename); - $programtitle = $programtitlename; - -#Jcode::convert(\$item{ChName},'euc'); -#Jcode::convert(\$item{SubTitle},'euc'); - -#$programSubTitle = $dbh->quote($item{SubTitle}); -$programSubTitle = $item{SubTitle}; +$programtitle = $dbh->quote($item{Title}); +#print "$item{Title}\n"; +#print "$item{TID}\n"; +Jcode::convert(\$item{ChName},'euc'); +#print "$item{ChName}\n"; +Jcode::convert(\$item{SubTitle},'euc'); +$programSubTitle = $dbh->quote($item{SubTitle}); $programSubTitle =~ s/\<\;//gi; -$programSubTitle =~ s/\&\;/\&/gi; -# $programSubTitle = $dbh->quote($programSubTitle); - + +#print "$item{SubTitle}\n"; +#print "$item{Count}\n"; $offsetmin = $item{StOffset}/60; +#print "Offset:$offsetmin (min)\n"; +#print "$item{EdTime}/$item{StTime}\n"; $edtime = &syobocaldate2foltiadate($item{EdTime}); $sttime = &syobocaldate2foltiadate($item{StTime}); +#print "$sttime-$edtime\n"; $length = &calclength($sttime,$edtime); $recstartdate = &calcoffsetdate($sttime ,$offsetmin ); $recenddate = &calcoffsetdate($edtime ,$offsetmin ); +#print "$recstartdate-$recenddate\n"; +#print "Length:$length(min)\n"; $stationid = &getstationid($item{ChName}); +#print "StationID:$stationid \n"; #サブタイトル追加------------------------------------------------- #番組があるか確認 - $sth = $dbh->prepare($stmt{'getxml2db.1'}); - $sth->execute($item{TID}); +$DBQuery = "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'"; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); @titlecount= $sth->fetchrow_array; @@ -140,19 +123,23 @@ #200412012359 $nomalstarttime = substr($sttime,8,4); - - $sth = $dbh->prepare($stmt{'getxml2db.2'}); - $oserr = $sth->execute($item{TID}, $programtitle, '', $nomalstarttime, $length, '', '', 3, 1, '', ''); - &writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle"); +$DBQuery = "insert into foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')"; + $sth = $dbh->prepare($DBQuery); +$sth->execute(); +&writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle"); + + }else{ #2006/2/26 #あったら、タイトル確認して - $sth = $dbh->prepare($stmt{'getxml2db.3'}); - $sth->execute($item{TID}); +$DBQuery = "SELECT title FROM foltia_program WHERE tid = '$item{TID}'"; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); @titlearray = $sth->fetchrow_array; #更新などされてたらupdate #print "$titlearray[0] / $programtitle\n"; if ($titlearray[0] ne "$programtitlename" ){ - $sth = $dbh->prepare($stmt{'getxml2db.4'}); - $oserr = $sth->execute($programtitle, $item{TID}); + $DBQuery = "UPDATE foltia_program SET title = $programtitle where tid = '$item{TID}' "; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); &writelog("getxml2db UPDATE TV Progtam:$item{TID}:$programtitle"); }#end if update @@ -161,6 +148,7 @@ #PIDがあるか確認 - $sth = $dbh->prepare($stmt{'getxml2db.5'}); - $sth->execute($item{'TID'}, $item{'PID'}); +$DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; + $sth = $dbh->prepare($DBQuery); + $sth->execute(); @subticount= $sth->fetchrow_array; if ($subticount[0] >= 1){ @@ -170,10 +158,30 @@ #UPDATE foltia_subtitle SET stationid = '42',countno = '8',subtitle = '京都行きます' ,startdatetime = '200503010035' ,enddatetime = '200503010050',startoffset = '0' ,lengthmin = '15' WHERE tid = '550' AND pid = '26000' if ($item{Count} == ""){ - $sth = $dbh->prepare($stmt{'getxml2db.6'}); - $oserr = $sth->execute($stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $item{'PID'}); -}else{ - $sth = $dbh->prepare($stmt{'getxml2db.7'}); - $oserr = $sth->execute($stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $item{'PID'}); - } + + $DBQuery = "UPDATE foltia_subtitle SET + stationid = '$stationid', + countno = null, + subtitle = $programSubTitle , + startdatetime = '$recstartdate' , + enddatetime = '$recenddate', + startoffset = '$offsetmin' , + lengthmin = '$length' + WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; + +}else{ + + $DBQuery = "UPDATE foltia_subtitle SET + stationid = '$stationid', + countno = '$item{Count}', + subtitle = $programSubTitle , + startdatetime = '$recstartdate' , + enddatetime = '$recenddate', + startoffset = '$offsetmin' , + lengthmin = '$length' + WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; +} + $sth = $dbh->prepare($DBQuery); + $sth->execute(); + # @subtitledata= $sth->fetchrow_array; }else{ #なければ追加 @@ -182,11 +190,13 @@ #そのままキューに入る形で if ($item{Count} eq ""){ - $sth = $dbh->prepare($stmt{'getxml2db.8'}); - $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); + $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; }else{ - $sth = $dbh->prepare($stmt{'getxml2db.9'}); - $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); + $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; } -} + $sth = $dbh->prepare($DBQuery); + $sth->execute(); + # @subtitledata= $sth->fetchrow_array; +} + #print "$DBQuery\n\n\n"; @@ -198,4 +208,6 @@ }#foreach -$oserr = $dbh->commit; + ## $dbh->disconnect(); + +