Index: /trunk/install/perl/foltialib.pl =================================================================== --- /trunk/install/perl/foltialib.pl (リビジョン 83) +++ /trunk/install/perl/foltialib.pl (リビジョン 94) @@ -4,5 +4,5 @@ $path = $0; $path =~ s/foltialib.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -13,10 +13,8 @@ use DBI; use DBD::Pg; - - - $DBDriv=$main::DBDriv; - $DBHost=$main::DBHost; - $DBPort=$main::DBPort; - $DBName=$main::DBName; +use DBD::SQLite; +use POSIX qw(strftime); + +$DSN=$main::DSN; $DBUser=$main::DBUser; $DBPass=""; @@ -47,5 +45,5 @@ sub writelog{ my $messages = $_[0]; -my $timestump = `date +%Y/%m/%d_%H:%M:%S`; + my $timestump = strftime("%Y/%m/%d_%H:%M:%S", localtime); chomp($timestump); if ($debugmode == 1){ @@ -146,9 +144,8 @@ my $stationname = $_[0] ; my $stationid ; -my $DBQuery = "SELECT count(*) FROM foltia_station WHERE stationname = '$item{ChName}'"; my $sth; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'foltialib.getstationid.1'}); + $sth->execute($item{'ChName'}); my @stationcount; @stationcount= $sth->fetchrow_array; @@ -156,7 +153,6 @@ if ($stationcount[0] == 1){ #チャンネルID取得 -$DBQuery = "SELECT stationid,stationname FROM foltia_station WHERE stationname = '$item{ChName}'"; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'foltialib.getstationid.2'}); + $sth->execute($item{'ChName'}); @stationinfo= $sth->fetchrow_array; #局ID @@ -166,6 +162,5 @@ }elsif($stationcount[0] == 0){ #新規登録 -$DBQuery = "SELECT max(stationid) FROM foltia_station"; - $sth = $dbh->prepare($DBQuery); + $sth = $dbh->prepare($stmt{'foltialib.getstationid.3'}); $sth->execute(); @stationinfo= $sth->fetchrow_array; @@ -174,10 +169,8 @@ ##$DBQuery = "insert into foltia_station values ('$stationid' ,'$item{ChName}','0','','','','','','')"; #新規局追加時は非受信局をデフォルトに -$DBQuery = "insert into foltia_station (stationid , stationname ,stationrecch ) values ('$stationid' ,'$item{ChName}','-10')"; - - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'foltialib.getstationid.4'}); + $sth->execute($stationid, $item{'ChName'}, -10); #print "Add station;$DBQuery\n"; -&writelog("foltialib Add station;$DBQuery"); + &writelog("foltialib Add station;$stmt{'foltialib.getstationid.4'}"); }else{ @@ -287,9 +280,8 @@ } -my $DBQuery = "SELECT pid FROM foltia_subtitle WHERE m2pfilename = '$m2pfilename' LIMIT 1 "; my $sth; -$sth = $dbh->prepare($DBQuery); -$sth->execute(); -#print "$DBQuery\n"; + $sth = $dbh->prepare($stmt{'foltialib.getpidbympegfilename.1'}); + $sth->execute($m2pfilename); +#print "$stmt{'foltialib.getpidbympegfilename.1'}\n"; my @pidinfo = $sth->fetchrow_array; my $pid = $pidinfo[0]; @@ -312,8 +304,7 @@ if ($updatestatus > 0 ){ -my $DBQuery = "UPDATE foltia_subtitle SET filestatus = $updatestatus , lastupdate = now() WHERE pid = $pid "; my $sth; -$sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'foltialib.changefilestatus.1'}); + $sth->execute($updatestatus, $pid); return 1; }else{ @@ -351,8 +342,7 @@ } -my $DBQuery = "SELECT filestatus FROM foltia_subtitle WHERE pid = $pid "; my $sth; -$sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'foltialib.getfilestatus.1'}); + $sth->execute($pid); my @statusinfo = $sth->fetchrow_array; Index: /trunk/install/perl/folprep.pl =================================================================== --- /trunk/install/perl/folprep.pl (リビジョン 1) +++ /trunk/install/perl/folprep.pl (リビジョン 94) @@ -23,5 +23,5 @@ $path = $0; $path =~ s/folprep.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } Index: /trunk/install/perl/changestbch.pl =================================================================== --- /trunk/install/perl/changestbch.pl (リビジョン 1) +++ /trunk/install/perl/changestbch.pl (リビジョン 94) @@ -26,8 +26,9 @@ use DBI; use DBD::Pg; +use DBD::SQLite; $path = $0; $path =~ s/changestbch.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -54,11 +55,8 @@ # pidから局(送出コマンド)調べる #DB初期化 - 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;; + $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; -$DBQuery = "SELECT foltia_station.tunertype,foltia_station.tunerch ,foltia_station.stationrecch ,foltia_station.stationid FROM foltia_subtitle,foltia_station WHERE foltia_subtitle.stationid = foltia_station.stationid AND foltia_subtitle.pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'changestbch.1'}); + $sth->execute($pid); @chstatus = $sth->fetchrow_array; $tunertype = $chstatus[0]; Index: /trunk/install/perl/updatem2pfiletable.pl =================================================================== --- /trunk/install/perl/updatem2pfiletable.pl (リビジョン 83) +++ /trunk/install/perl/updatem2pfiletable.pl (リビジョン 94) @@ -16,26 +16,24 @@ use DBI; use DBD::Pg; +use DBD::SQLite; $path = $0; $path =~ s/updatem2pfiletable.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } require "foltialib.pl"; - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; $dbh->{AutoCommit} = 0; #　ひとまず消す -$query = "DELETE FROM foltia_m2pfiles "; - $sth = $dbh->prepare($query); +$sth = $dbh->prepare($stmt{'updatem2pfiletable.1'}); $sth->execute(); while ($file = glob("$recfolderpath/*.m2?")) { $file =~ s/$recfolderpath\///; -$query = "insert into foltia_m2pfiles values ('$file')"; -$oserr = $dbh->do($query); + $sth = $dbh->prepare($stmt{'updatem2pfiletable.2'}); + $sth->execute($file); # print "$file\n"; }#while @@ -46,6 +44,5 @@ #　ひとまず消す -$query = "DELETE FROM foltia_mp4files "; - $sth = $dbh->prepare($query); +$sth = $dbh->prepare($stmt{'updatem2pfiletable.3'}); $sth->execute(); @@ -58,6 +55,6 @@ $filetid =~ s/[^0-9]//g; if (($filetid ne "" )&& ($fileline[2] ne "" )){ - $query = "insert into foltia_mp4files values ('$filetid','$fileline[2]')"; - $oserr = $dbh->do($query); + $sth = $dbh->prepare($stmt{'updatem2pfiletable.4'}); + $oserr = $sth->execute($filetid, $fileline[2]); #print "$filetid;$fileline[2];$query\n" # http://www.atmarkit.co.jp/fnetwork/rensai/sql03/sql1.html Index: /trunk/install/perl/envpolicyupdate.pl =================================================================== --- /trunk/install/perl/envpolicyupdate.pl (リビジョン 70) +++ /trunk/install/perl/envpolicyupdate.pl (リビジョン 94) @@ -16,8 +16,9 @@ use DBI; use DBD::Pg; +use DBD::SQLite; $path = $0; $path =~ s/envpolicyupdate.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -33,11 +34,7 @@ eval "$returnparam\n"; - 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;; + $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; -$DBQuery = "SELECT userclass,name,passwd1 FROM foltia_envpolicy "; - - $envph = $dbh->prepare($DBQuery); + $envph = $dbh->prepare($stmt{'envpolicyupdate.1'}); $envph->execute(); Index: /trunk/install/perl/db/Pg.pl =================================================================== --- /trunk/install/perl/db/Pg.pl (リビジョン 94) +++ /trunk/install/perl/db/Pg.pl (リビジョン 94) @@ -0,0 +1,79 @@ + +%main::stmt = ( +'addatq.1' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ?", +'addatq.2' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ? AND stationid = ?", +'addatq.3' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ? AND stationid = '0'", +'addatq.addcue.1' => "SELECT * FROM foltia_tvrecord WHERE tid = ?", +'addatq.addcue.2' => "SELECT * FROM foltia_tvrecord WHERE tid = ? AND stationid = ?", +'addatq.addcue.3' => "SELECT * from foltia_subtitle WHERE tid = ? AND startdatetime > ? AND startdatetime < ?", +'addatq.addcue.4' => "SELECT stationid , stationrecch FROM foltia_station where stationid = ?", +'addatq.addcue.5' => "SELECT * from foltia_subtitle WHERE tid = ? AND stationid = ? AND startdatetime > ? AND startdatetime < ?", +'addatq.addcue.6' => "SELECT stationid , stationrecch FROM foltia_station where stationid = ?", + +'addpidatq.1' => "SELECT count(*) FROM foltia_subtitle WHERE pid = ?", +'addpidatq.2' => "SELECT bitrate,digital FROM foltia_tvrecord , foltia_subtitle WHERE foltia_tvrecord.tid = foltia_subtitle.tid AND pid = ?", +'addpidatq.3' => "SELECT stationrecch, digitalch, digitalstationband ,foltia_station.stationid FROM foltia_station,foltia_subtitle WHERE foltia_subtitle.pid = ? AND foltia_subtitle.stationid = foltia_station.stationid", +'addpidatq.4' => "SELECT * FROM foltia_subtitle WHERE pid = ?", + +'changestbch.1' => "SELECT foltia_station.tunertype, foltia_station.tunerch, foltia_station.stationrecch, foltia_station.stationid FROM foltia_subtitle, foltia_station WHERE foltia_subtitle.stationid = foltia_station.stationid AND foltia_subtitle.pid = ?", + +'envpolicyupdate.1' => "SELECT userclass,name,passwd1 FROM foltia_envpolicy", + +'foltialib.getstationid.1' => "SELECT count(*) FROM foltia_station WHERE stationname = ?", +'foltialib.getstationid.2' => "SELECT stationid,stationname FROM foltia_station WHERE stationname = ?", +'foltialib.getstationid.3' => "SELECT max(stationid) FROM foltia_station", +'foltialib.getstationid.4' => "INSERT INTO foltia_station (stationid, stationname, stationrecch) VALUES (?, ?, ?)", +'foltialib.getpidbympegfilename.1' => "SELECT pid FROM foltia_subtitle WHERE m2pfilename = ? LIMIT 1", +'foltialib.changefilestatus.1' => "UPDATE foltia_subtitle SET filestatus = ?, lastupdate = now() WHERE pid = ?", +'foltialib.getfilestatus.1' => "SELECT filestatus FROM foltia_subtitle WHERE pid = ?", + +'getxml2db.1' => "SELECT count(*) FROM foltia_program WHERE tid = ?", +'getxml2db.2' => "INSERT into foltia_program VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", +'getxml2db.3' => "SELECT title FROM foltia_program WHERE tid = ?", +'getxml2db.4' => "UPDATE foltia_program SET title = ? where tid = ?", +'getxml2db.5' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND pid = ?", +'getxml2db.6' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?", +'getxml2db.7' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?", +'getxml2db.8' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", +'getxml2db.9' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", + +'ipodtranscode.1' => "SELECT foltia_subtitle.pid,foltia_subtitle.tid,foltia_subtitle.m2pfilename,filestatus,foltia_program.aspect,foltia_subtitle.countno FROM foltia_subtitle, foltia_program, foltia_m2pfiles WHERE filestatus >= ? AND filestatus < ? AND foltia_program.tid = foltia_subtitle.TID AND foltia_program.PSP = 1 AND foltia_m2pfiles.m2pfilename = foltia_subtitle.m2pfilename ORDER BY enddatetime ASC LIMIT 1", +'ipodtranscode.2' => "SELECT title, countno, subtitle FROM foltia_program, foltia_subtitle WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_subtitle.pid = ?", +'ipodtranscode.updatemp4file.1' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?", +'ipodtranscode.updatemp4file.2' => "INSERT INTO foltia_mp4files VALUES (?, ?)", + +'ipodtranscode.counttranscodefiles.1' => "SELECT count(*) FROM foltia_subtitle, foltia_program, foltia_m2pfiles WHERE filestatus >= ? AND filestatus < ? AND foltia_program.tid = foltia_subtitle.TID AND foltia_program.PSP = 1 AND foltia_m2pfiles.m2pfilename = foltia_subtitle.m2pfilename", + +'mklocalizeddir.1' => "SELECT title FROM foltia_program where tid = ?", + +'recwrap.1' => "UPDATE foltia_subtitle SET m2pfilename = ? WHERE pid = ?", +'recwrap.2' => "INSERT into foltia_m2pfiles VALUES (?)", +'recwrap.3' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?", +'recwrap.4' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?", +'recwrap.5' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?", +'recwrap.6' => "INSERT into foltia_mp4files VALUES (?, ?)", +'recwrap.7' => "SELECT foltia_subtitle.tid,foltia_subtitle.countno,foltia_subtitle.subtitle,foltia_subtitle.startdatetime ,foltia_subtitle.enddatetime ,foltia_subtitle.lengthmin ,foltia_tvrecord.bitrate , foltia_subtitle.startoffset , foltia_subtitle.pid ,foltia_tvrecord.digital FROM foltia_subtitle ,foltia_tvrecord WHERE foltia_tvrecord.tid = foltia_subtitle.tid AND foltia_tvrecord.tid = ? AND foltia_subtitle.startdatetime = ? AND foltia_tvrecord.digital = 1", + +'schedulecheck.1' => "SELECT count(*) FROM foltia_tvrecord", +'schedulecheck.2' => "SELECT tid ,stationid FROM foltia_tvrecord", + +'singletranscode.1' => "SELECT pid FROM foltia_subtitle WHERE m2pfilename = ?", +'singletranscode.2' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND countno = ?", +'singletranscode.3' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ?", +'singletranscode.4' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?", +'singletranscode.5' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?", +'singletranscode.6' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?", +'singletranscode.7' => "INSERT into foltia_mp4files values (?, ?)", + +'updatem2pfiletable.1' => "DELETE FROM foltia_m2pfiles", +'updatem2pfiletable.2' => "INSERT into foltia_m2pfiles values (?)", +'updatem2pfiletable.3' => "DELETE FROM foltia_mp4files", +'updatem2pfiletable.4' => "INSERT into foltia_mp4files values (?, ?)", + +'xmltv2foltia.chkerase.1' => "DELETE FROM foltia_epg WHERE startdatetime > ? AND ontvchannel = ?", +'xmltv2foltia.registdb.1' => "SELECT max(epgid) FROM foltia_epg", +'xmltv2foltia.registdb.2' => "INSERT INTO foltia_epg VALUES (?, ?, ?, ?, ?, ?, ?, ?)", + +); + +1; Index: /trunk/install/perl/db/SQLite.pl =================================================================== --- /trunk/install/perl/db/SQLite.pl (リビジョン 94) +++ /trunk/install/perl/db/SQLite.pl (リビジョン 94) @@ -0,0 +1,6 @@ + +require 'db/Pg.pl'; + +$stmt{'foltialib.changefilestatus.1'} = "UPDATE foltia_subtitle SET filestatus = ?, lastupdate = datetime('now', 'localtime') WHERE pid = ?"; + +1; Index: /trunk/install/perl/foltia_conf1.pl.template =================================================================== --- /trunk/install/perl/foltia_conf1.pl.template (リビジョン 83) +++ /trunk/install/perl/foltia_conf1.pl.template (リビジョン 94) @@ -28,8 +28,13 @@ #以下はデフォルトでインストールしてればいじらなくてもいい -$main::DBDriv="Pg"; -$main::DBHost="localhost"; -$main::DBPort="5432"; -$main::DBName="foltia"; + +## for postgresql +#$main::DSN="dbi:Pg:dbname=foltia;host=localhost;port=5432"; +#require 'db/Pg.pl'; + +## for sqlite +$main::DSN="dbi:SQLite:dbname=/home/foltia/foltia.sqlite"; +require 'db/SQLite.pl'; + $main::DBUser="foltia"; $main::DBPass=""; Index: /trunk/install/perl/digitaltvrecording.pl =================================================================== --- /trunk/install/perl/digitaltvrecording.pl (リビジョン 91) +++ /trunk/install/perl/digitaltvrecording.pl (リビジョン 94) @@ -25,5 +25,5 @@ $path = $0; $path =~ s/digitaltvrecording.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -112,5 +112,5 @@ &writelog("digitaltvrecording: DEBUG FILENAME ne null \$outputfile $outputfile "); }else{ - $outputfile .= `date +%Y%m%d-%H%M --date "1 min "`; + $outputfile .= strftime("%Y%m%d-%H%M", localtime(time + 60)); chomp($outputfile); $outputfile .= ".m2t"; @@ -193,5 +193,5 @@ } }else{ # エラー b25とrecfriioがありません - &writelog("digitaltvrecording :ERROR :recfriio or b25 not found. You must install $toolpath/b25 and $toolpath/recfriio."); + &writelog("digitaltvrecording :ERROR :recfriio or b25 not found. You must install $toolpath/perl/tool/b25 and $toolpath/perl/tool/recfriio."); exit 1; } @@ -387,5 +387,5 @@ } }else{ # エラー recpt1がありません - &writelog("digitaltvrecording :ERROR :recpt1 not found. You must install $toolpath/b25 and $toolpath/recpt1."); + &writelog("digitaltvrecording :ERROR :recpt1 not found. You must install $toolpath/tool/b25 and $toolpath/tool/recpt1."); $errorflag = 1; } @@ -409,5 +409,5 @@ } }else{ # エラー recfriioがありません - &writelog("digitaltvrecording :ERROR :recfriio not found. You must install $toolpath/b25 and $toolpath/recfriio."); + &writelog("digitaltvrecording :ERROR :recfriio not found. You must install $toolpath/perl/tool/b25 and $toolpath/perl/tool/recfriio."); exit 1; } Index: /trunk/install/perl/singletranscode.pl =================================================================== --- /trunk/install/perl/singletranscode.pl (リビジョン 1) +++ /trunk/install/perl/singletranscode.pl (リビジョン 94) @@ -28,4 +28,5 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Schedule::At; use Time::Local; @@ -34,5 +35,5 @@ $path = $0; $path =~ s/singletranscode.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -63,15 +64,11 @@ #PSPトラコン必要かどうか - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; if ($ARGV[1] != ""){ $pid = $ARGV[1] ; }else{ -$DBQuery = "SELECT pid FROM foltia_subtitle WHERE m2pfilename = '$ARGV[0]' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'singletranscode.1'}); + $sth->execute($ARGV[0]); @pidarray = $sth->fetchrow_array; unless ($pidarray[0] == "" ){ @@ -86,13 +83,11 @@ #　追加部分 -$query = "SELECT count(*) FROM foltia_subtitle WHERE tid = '$tid' AND countno = '$countno' "; - $sth = $dbh->prepare($query); - $sth->execute(); +$sth = $dbh->prepare($stmt{'singletranscode.2'}); +$sth->execute($tid, $countno); @subticount= $sth->fetchrow_array; unless ($subticount[0] >= 1){ -$query = "SELECT count(*) FROM foltia_subtitle WHERE tid = '$tid' "; - $sth = $dbh->prepare($query); - $sth->execute(); + $sth = $dbh->prepare($stmt{'singletranscode.3'}); + $sth->execute($tid); @subticount= $sth->fetchrow_array; @@ -120,7 +115,6 @@ # PSP ------------------------------------------------------ #PSPトラコン必要かどうか -$DBQuery = "SELECT psp,aspect,title FROM foltia_program WHERE tid = '$tid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); +$sth = $dbh->prepare($stmt{'singletranscode.4'}); +$sth->execute($tid); @psptrcn= $sth->fetchrow_array; if ($psptrcn[0] == 1 ){#トラコン番組 @@ -227,5 +221,5 @@ my $newestmp4filename = `cd $pspdirname ; ls -t *.MP4 | head -1`; if ($newestmp4filename =~ /M4V$tid/){ - $nowcountno = $' ; + $nowcountno = $' ;#' $nowcountno++; $pspfilnameft = sprintf("%02d",$nowcountno); @@ -261,7 +255,6 @@ #最適化 -$DBQuery = "SELECT subtitle FROM foltia_subtitle WHERE tid = '$tid' AND countno = '$countno' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'singletranscode.5'}); + $sth->execute($tid, $countno); @programtitle = $sth->fetchrow_array; @@ -285,5 +278,5 @@ if (-e "$pspdirname/M4V".$pspfilname.".THM"){ -$timestamp =`date "+%Y%m%d-%H%M%S"`; + $timestamp = strftime("%Y%m%d-%H%M%S", localtime); chomp $timestamp; system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/M4V".$pspfilname.".THM.".$timestamp.".THM"); @@ -297,26 +290,16 @@ system("rm -rf $pspdirname/0000000*.jpg "); - - - # MP4ファイル名をPIDレコードに書き込み unless ($pid eq ""){ - $DBQuery = " - UPDATE foltia_subtitle - SET PSPfilename = 'M4V$pspfilname.MP4' - WHERE pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("singletranscode UPDATEsubtitleDB $DBQuery"); + $sth = $dbh->prepare($stmt{'singletranscode.6'}); + $sth->execute("M4V$pspfilname.MP4", $pid); + &writelog("singletranscode UPDATEsubtitleDB $stmt{'singletranscode.6'}"); }else{ &writelog("singletranscode PID not found"); } # MP4ファイル名をfoltia_mp4files挿入 - $DBQuery = "insert into foltia_mp4files values ('$tid','M4V$pspfilname.MP4') "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("singletranscode UPDATEmp4DB $DBQuery"); + $sth = $dbh->prepare($stmt{'singletranscode.7'}); + $sth->execute($tid, "M4V$pspfilname.MP4"); + &writelog("singletranscode UPDATEmp4DB $stmt{'singletranscode.7'}"); }#PSPトラコンあり - - Index: /trunk/install/perl/captureimagemaker.pl =================================================================== --- /trunk/install/perl/captureimagemaker.pl (リビジョン 83) +++ /trunk/install/perl/captureimagemaker.pl (リビジョン 94) @@ -14,5 +14,5 @@ $path = $0; $path =~ s/captureimagemaker.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -56,5 +56,5 @@ $date =~ s/[^0-9]//ig; if ($date eq "" ){ - $date = `date +%Y%m%d` + $date = strftime("%Y%m%d", localtime); } # print "DATE:$date\n"; @@ -65,5 +65,5 @@ $time =~ s/[^0-9]//ig; if ($time eq "" ){ - $time = `date +%H%M` + $time = strftime("%H%M", localtime); } # print "TIME:$time\n"; @@ -119,9 +119,19 @@ #　10秒ごとに if ($filename =~ /m2t$/){ - &writelog("captureimagemaker DEBUG mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf scale=192:108 -ao null -sstep 9 -v 3 $recfolderpath/$filename"); - system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf scale=192:108 -ao null -sstep 9 -v 3 $recfolderpath/$filename"); + &writelog("captureimagemaker DEBUG mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf scale=192:108 -ao null -sstep 9 $recfolderpath/$filename"); + system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf scale=192:108 -ao null -sstep 9 $recfolderpath/$filename"); + if(-e "$capimgdirname/$captureimgdir/00000001.jpg" ){ #$capimgdirname/$captureimgdir/があったらなにもしない + }else{ #空っぽなら再試行 + &writelog("captureimagemaker DEBUG RETRY mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf framestep=300step,scale=192:108 -ao null $recfolderpath/$filename"); + system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf framestep=300step,scale=192:108 -ao null $recfolderpath/$filename"); + } + }else{ &writelog("captureimagemaker DEBUG mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf crop=690:460:12:10,scale=160:120 -ao null -sstep 9 -v 3 $recfolderpath/$filename"); - system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf crop=690:460:12:10,scale=160:120 -ao null -sstep 9 -v 3 $recfolderpath/$filename"); + system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf crop=690:460:12:10,scale=160:120 -ao null -sstep 9 $recfolderpath/$filename"); + if(-e "$capimgdirname/$captureimgdir/00000001.jpg" ){ #$capimgdirname/ + }else{ + system ("mplayer -ss 00:00:10 -vo jpeg:outdir=$capimgdirname/$captureimgdir/ -vf framestep=300step,crop=690:460:12:10,scale=160:120 -ao null $recfolderpath/$filename"); + } } Index: /trunk/install/perl/tvrecording.pl =================================================================== --- /trunk/install/perl/tvrecording.pl (リビジョン 83) +++ /trunk/install/perl/tvrecording.pl (リビジョン 94) @@ -29,5 +29,5 @@ $path = $0; $path =~ s/tvrecording.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -301,5 +301,5 @@ # $outputfile .= "$ARGV[3]"; # }else{ -# $outputfile .= `date +%Y%m%d-%H%M --date "1 min "`; +# $outputfile .= strftime("%Y%m%d-%H%M", localtime(time + 60)); # } $outputfile = $ARGV[3]; @@ -308,8 +308,8 @@ $outputfile = $outputpath.$outputfile ; # $outputfile .= "$ARGV[3]"; -# $outputfile .= `date +%Y%m%d-%H%M --date "1 min "`; +# $outputfile .= strftime("%Y%m%d-%H%M", localtime(time + 60)); &writelog("tvrecording: DEBUG ARGV[2] ne null \$outputfile $outputfile "); }else{ - $outputfile .= `date +%Y%m%d-%H%M --date "1 min "`; + $outputfile .= strftime("%Y%m%d-%H%M", localtime(time + 60)); chomp($outputfile); $outputfile .= ".m2p"; Index: /trunk/install/perl/getxml2db.pl =================================================================== --- /trunk/install/perl/getxml2db.pl (リビジョン 82) +++ /trunk/install/perl/getxml2db.pl (リビジョン 94) @@ -19,9 +19,10 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Digest::MD5 qw(md5_hex); $path = $0; $path =~ s/getxml2db.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -48,11 +49,7 @@ } +$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;; - -#$dbh->{AutoCommit} = 0; +$dbh->{AutoCommit} = 0; # If-Modified-Since使うように変更#2008/11/14 @@ -105,5 +102,6 @@ $programtitlename =~ s/\>\;/>/gi; $programtitlename =~ s/\&\;/\&/gi; -$programtitle = $dbh->quote($programtitlename); +# $programtitle = $dbh->quote($programtitlename); + $programtitle = $programtitlename; Jcode::convert(\$item{ChName},'euc'); @@ -115,5 +113,5 @@ $programSubTitle =~ s/\>\;/>/gi; $programSubTitle =~ s/\&\;/\&/gi; -$programSubTitle = $dbh->quote($programSubTitle); +# $programSubTitle = $dbh->quote($programSubTitle); $offsetmin = $item{StOffset}/60; @@ -127,7 +125,6 @@ #サブタイトル追加------------------------------------------------- #番組があるか確認 -$DBQuery = "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'"; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'getxml2db.1'}); + $sth->execute($item{TID}); @titlecount= $sth->fetchrow_array; @@ -137,25 +134,19 @@ #200412012359 $nomalstarttime = substr($sttime,8,4); -$DBQuery = "insert into foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')"; -# $sth = $dbh->prepare($DBQuery); -# $sth->execute(); -$oserr = $dbh->do($DBQuery); -&writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle"); - + $sth = $dbh->prepare($stmt{'getxml2db.2'}); + $oserr = $sth->execute($item{TID}, $programtitle, '', $nomalstarttime, $length, '', '', 3, 1, '', ''); + &writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle"); }else{ #2006/2/26 #あったら、タイトル確認して -$DBQuery = "SELECT title FROM foltia_program WHERE tid = '$item{TID}'"; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'getxml2db.3'}); + $sth->execute($item{TID}); @titlearray = $sth->fetchrow_array; #更新などされてたらupdate #print "$titlearray[0] / $programtitle\n"; if ($titlearray[0] ne "$programtitlename" ){ - $DBQuery = "UPDATE foltia_program SET title = $programtitle where tid = '$item{TID}' "; -# $sth = $dbh->prepare($DBQuery); -# $sth->execute(); - $oserr = $dbh->do($DBQuery); + $sth = $dbh->prepare($stmt{'getxml2db.4'}); + $oserr = $sth->execute($programtitle, $item{TID}); &writelog("getxml2db UPDATE TV Progtam:$item{TID}:$programtitle"); }#end if update @@ -164,7 +155,6 @@ #PIDがあるか確認 -$DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'getxml2db.5'}); + $sth->execute($item{'TID'}, $item{'PID'}); @subticount= $sth->fetchrow_array; if ($subticount[0] >= 1){ @@ -174,31 +164,10 @@ #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} == ""){ - - $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}' "; - + $sth = $dbh->prepare($stmt{'getxml2db.6'}); + $oserr = $sth->execute($stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $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(); - $oserr = $dbh->do($DBQuery); - + $sth = $dbh->prepare($stmt{'getxml2db.7'}); + $oserr = $sth->execute($stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $item{'PID'}); + } }else{ #なければ追加 @@ -207,14 +176,11 @@ #そのままキューに入る形で if ($item{Count} eq ""){ - $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; + $sth = $dbh->prepare($stmt{'getxml2db.8'}); + $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); }else{ - $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; + $sth = $dbh->prepare($stmt{'getxml2db.9'}); + $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); } -# $sth = $dbh->prepare($DBQuery); -# $sth->execute(); - $oserr = $dbh->do($DBQuery); - } - #print "$DBQuery\n\n\n"; @@ -226,6 +192,4 @@ }#foreach -#$oserr = $dbh->commit; +$oserr = $dbh->commit; ## $dbh->disconnect(); - - Index: /trunk/install/perl/schedulecheck.pl =================================================================== --- /trunk/install/perl/schedulecheck.pl (リビジョン 1) +++ /trunk/install/perl/schedulecheck.pl (リビジョン 94) @@ -14,4 +14,5 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Schedule::At; use Time::Local; @@ -19,5 +20,5 @@ $path = $0; $path =~ s/schedulecheck.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -29,17 +30,12 @@ #予約番組探し -$now = &epoch2foldate(`date +%s`); +$now = &epoch2foldate(time()); $now = &epoch2foldate($now); $checkrangetime = $now + 15*60;#15分後まで $checkrangetime = &epoch2foldate($checkrangetime); - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; -$DBQuery = "SELECT count(*) FROM foltia_tvrecord "; - - - $sth = $dbh->prepare($DBQuery); +$sth = $dbh->prepare($stmt{'schedulecheck.1'}); $sth->execute(); @titlecount= $sth->fetchrow_array; @@ -48,7 +44,5 @@ exit; }else{ - -$DBQuery = "SELECT tid ,stationid FROM foltia_tvrecord "; - $sth = $dbh->prepare($DBQuery); + $sth = $dbh->prepare($stmt{'schedulecheck.2'}); $sth->execute(); while (($tid,$stationid ) = $sth->fetchrow_array()) { Index: /trunk/install/perl/addpidatq.pl =================================================================== --- /trunk/install/perl/addpidatq.pl (リビジョン 83) +++ /trunk/install/perl/addpidatq.pl (リビジョン 94) @@ -14,4 +14,5 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Schedule::At; use Time::Local; @@ -19,5 +20,5 @@ $path = $0; $path =~ s/addpidatq.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -36,18 +37,13 @@ #DB検索(PID) - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; -$DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); +$sth = $dbh->prepare($stmt{'addpidatq.1'}); +$sth->execute($pid); @titlecount= $sth->fetchrow_array; if ($titlecount[0] == 1 ){ - -$DBQuery = "SELECT bitrate,digital FROM foltia_tvrecord , foltia_subtitle WHERE foltia_tvrecord.tid = foltia_subtitle.tid AND pid='$pid' "; - $sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'addpidatq.2'}); + $sth->execute($pid); @titlecount= $sth->fetchrow_array; $bitrate = $titlecount[0];#ビットレート取得 @@ -59,15 +55,13 @@ #PID抽出 -$now = &epoch2foldate(`date +%s`); + $now = &epoch2foldate(time()); #stationIDからrecch -$DBQuery = "SELECT stationrecch,digitalch ,digitalstationband ,foltia_station.stationid FROM foltia_station,foltia_subtitle WHERE foltia_subtitle.pid = '$pid' AND foltia_subtitle.stationid = foltia_station.stationid "; - - $stationh = $dbh->prepare($DBQuery); - $stationh->execute(); -@stationl = $stationh->fetchrow_array; + $stationh = $dbh->prepare($stmt{'addpidatq.3'}); + $stationh->execute($pid); + @stationl = $stationh->fetchrow_array(); $recch = $stationl[0]; if ($recch eq ""){ - &writelog("addpidatq ERROR recch is NULL:$DBQuery."); + &writelog("addpidatq ERROR recch is NULL:$stmt{'addpidatq.3'}."); exit 1; } @@ -82,7 +76,6 @@ $digitalstationband = 0; } -$DBQuery = "SELECT * FROM foltia_subtitle WHERE pid='$pid' "; - $sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'addpidatq.4'}); + $sth->execute($pid); ($pid , $tid , Index: /trunk/install/perl/xmltv2foltia.pl =================================================================== --- /trunk/install/perl/xmltv2foltia.pl (リビジョン 38) +++ /trunk/install/perl/xmltv2foltia.pl (リビジョン 94) @@ -30,8 +30,9 @@ use DBI; use DBD::Pg; +use DBD::SQLite; $path = $0; $path =~ s/xmltv2foltia.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -40,11 +41,10 @@ $currentworkdate = "" ; $currentworkch = "" ; -$today =`date "+%Y%m%d"`; -$todaytime =`date "+%Y%m%d%H%M"`; +$today = strftime("%Y%m%d", localtime); +$todaytime = strftime("%Y%m%d%H%M", localtime); # DB Connect - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; +$dbh->{AutoCommit} = 0; while(<>){ @@ -187,13 +187,13 @@ }elsif(/<\/programme>/){ #登録処理はココで -#&writelog("xmltv2foltia DEBUG call chkerase $item{start},$item{channel}"); - -&chkerase($item{start},$item{channel}); -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} @@ -216,4 +216,9 @@ }# while +$dbh->commit; + +#end +################ + sub chkerase{ # xmltvからきた日付とチャンネルをfoltia epgと比較 @@ -232,8 +237,7 @@ # 新規に入る予定の未来の番組表、全部いったん消す # $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"); + $sth = $dbh->prepare($stmt{'xmltv2foltia.chkerase.1'}); + $sth->execute($todaytime, $ontvepgchannel); + &writelog("xmltv2foltia DELETE EPG $epgstartdatetime:$stmt{'xmltv2foltia.chkerase.1'}"); #$currentworkdate = "$today"; $currentworkch = $ontvepgchannel ; @@ -261,6 +265,5 @@ if($foltiastarttime > $todaytime){ - my $DBQuery = "SELECT max(epgid) FROM foltia_epg "; - $sth = $dbh->prepare($DBQuery); + $sth = $dbh->prepare($stmt{'xmltv2foltia.registdb.1'}); $sth->execute(); @currentepgid = $sth->fetchrow_array; @@ -274,18 +277,16 @@ #&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); - -$DBQuery = "insert into foltia_epg values ($newepgid,$foltiastarttime,$foltiaendtime,$lengthmin,$channel,$title,$desc,$category)"; -# $DBQuery = $dbh->quote($DBQuery); - $sth = $dbh->prepare($DBQuery); - $sth->execute(); - +# $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"; # &writelog("xmltv2foltia DEBUG $DBQuery"); Index: /trunk/install/perl/mklocalizeddir.pl =================================================================== --- /trunk/install/perl/mklocalizeddir.pl (リビジョン 1) +++ /trunk/install/perl/mklocalizeddir.pl (リビジョン 94) @@ -16,9 +16,9 @@ use DBI; use DBD::Pg; - +use DBD::SQLite; $path = $0; $path =~ s/mklocalizeddir.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -43,12 +43,9 @@ #接続 - 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;; + $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; #検索 -$DBQuery = "select title from foltia_program where tid=$tid "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'mklocalizeddir.1'}); + $sth->execute($tid); @subticount= $sth->fetchrow_array; $title = $subticount[0] ; Index: /trunk/install/perl/ipodtranscode.pl =================================================================== --- /trunk/install/perl/ipodtranscode.pl (リビジョン 83) +++ /trunk/install/perl/ipodtranscode.pl (リビジョン 94) @@ -13,9 +13,10 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Jcode; $path = $0; $path =~ s/ipodtranscode.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -35,7 +36,5 @@ #DB初期化 - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; # タイトル取得 @@ -57,16 +56,9 @@ while ($counttranscodefiles >= 1){ - -$DBQuery = "SELECT foltia_subtitle.pid,foltia_subtitle.tid,foltia_subtitle.m2pfilename,filestatus,foltia_program.aspect ,foltia_subtitle.countno -FROM foltia_subtitle, foltia_program, foltia_m2pfiles -WHERE filestatus >= $FILESTATUSRECEND AND filestatus < $FILESTATUSTRANSCODECOMPLETE AND foltia_program.tid = foltia_subtitle.TID AND foltia_program.PSP = 1 AND foltia_m2pfiles.m2pfilename = foltia_subtitle.m2pfilename -ORDER BY enddatetime ASC -LIMIT 1 "; - -$sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'ipodtranscode.1'}); + $sth->execute($FILESTATUSRECEND, $FILESTATUSTRANSCODECOMPLETE, ); @dbparam = $sth->fetchrow_array; #print "$dbparam[0],$dbparam[1],$dbparam[2],$dbparam[3],$dbparam[4],$dbparam[5]\n"; -&writelog("ipodtranscode DEBUG $DBQuery"); +#&writelog("ipodtranscode DEBUG $DBQuery"); &writelog("ipodtranscode DEBUG $dbparam[0],$dbparam[1],$dbparam[2],$dbparam[3],$dbparam[4],$dbparam[5]"); $pid = $dbparam[0]; @@ -86,11 +78,6 @@ # タイトル取得 if ($pid ne ""){ - -$DBQuery = "SELECT title , countno , subtitle -FROM foltia_program, foltia_subtitle -WHERE foltia_program.tid = foltia_subtitle.tid -AND foltia_subtitle.pid = $pid "; -$sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'ipodtranscode.2'}); + $sth->execute($pid); @programtitle = $sth->fetchrow_array; $programtitle[0] =~ s/\"/\\"/gi; @@ -147,6 +134,8 @@ } + $filenamebody = $inputmpeg2 ; $filenamebody =~ s/.m2t$|.ts$|.m2p$|.mpg$//gi; + #デジタルかアナログか if ($inputmpeg2 =~ /m2t$|ts$/i){ @@ -174,7 +163,11 @@ $ffmpegencopt = " -s 360x202 -deinterlace -r 24.00 -vcodec libx264 -g 300 -b 330000 -level 13 -loop 1 -sc_threshold 60 -partp4x4 1 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; }elsif($trconqty == 2){ - $ffmpegencopt = " -s 480x272 -deinterlace -r 29.97 -vcodec libx264 -g 300 -b 400000 -level 13 -loop 1 -sc_threshold 60 -partp4x4 1 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; +# $ffmpegencopt = " -s 480x272 -deinterlace -r 29.97 -vcodec libx264 -g 300 -b 400000 -level 13 -loop 1 -sc_threshold 60 -partp4x4 1 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; +# for ffmpeg 0.5 or later + $ffmpegencopt = " -s 480x272 -deinterlace -r 29.97 -vcodec libx264 -vpre default -g 300 -b 400000 -level 13 -sc_threshold 60 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; }elsif($trconqty == 3){#640x352 - $ffmpegencopt = " -s 640x352 -deinterlace -r 29.97 -vcodec libx264 -g 100 -b 600000 -level 13 -loop 1 -sc_threshold 60 -partp4x4 1 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; +# $ffmpegencopt = " -s 640x352 -deinterlace -r 29.97 -vcodec libx264 -g 100 -b 600000 -level 13 -loop 1 -sc_threshold 60 -partp4x4 1 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; +# for ffmpeg 0.5 or later + $ffmpegencopt = " -s 640x352 -deinterlace -r 29.97 -vcodec libx264 -vpre default -g 100 -b 600000 -level 13 -sc_threshold 60 -rc_eq 'blurCplx^(1-qComp)' -refs 3 -maxrate 700000 -async 50 -f h264 $filenamebody.264"; } &changefilestatus($pid,$FILESTATUSTRANSCODEFFMPEG); @@ -246,41 +239,58 @@ # MP4ビルド unlink("${filenamebody}.base.mp4"); - &changefilestatus($pid,$FILESTATUSTRANSCODEMP4BOX); - &writelog("ipodtranscode MP4Box $filenamebody"); - system ("cd $recfolderpath ; MP4Box -fps 29.97 -add $filenamebody.264 -new $filenamebody.base.mp4"); -#$exit_value = $? >> 8; -#$signal_num = $? & 127; -#$dumped_core = $? & 128; -#&writelog("ipodtranscode DEBUG MP4Box -fps 29.97 -add:$exit_value:$signal_num:$dumped_core"); - - if (-e "$filenamebody.base.mp4"){ - system ("cd $recfolderpath ; MP4Box -add $filenamebody.aac $filenamebody.base.mp4"); -#$exit_value = $? >> 8; -#$signal_num = $? & 127; -#$dumped_core = $? & 128; -#&writelog("ipodtranscode DEBUG MP4Box -add $filenamebody.aac:$exit_value:$signal_num:$dumped_core"); + if (-e "$toolpath/perl/tool/MP4Box"){ + &changefilestatus($pid,$FILESTATUSTRANSCODEMP4BOX); + &writelog("ipodtranscode MP4Box $filenamebody"); +# system ("cd $recfolderpath ; MP4Box -fps 29.97 -add $filenamebody.264 -new $filenamebody.base.mp4"); + system ("cd $recfolderpath ;$toolpath/perl/tool/MP4Box -fps 29.97 -add $filenamebody.264 -new $filenamebody.base.mp4"); + $exit_value = $? >> 8; + $signal_num = $? & 127; + $dumped_core = $? & 128; + &writelog("ipodtranscode DEBUG MP4Box -fps 29.97 -add $filenamebody.264 -new $filenamebody.base.mp4:$exit_value:$signal_num:$dumped_core"); + + + if (-e "$filenamebody.base.mp4"){ +# system ("cd $recfolderpath ; MP4Box -add $filenamebody.aac $filenamebody.base.mp4"); + system ("cd $recfolderpath ;$toolpath/perl/tool/MP4Box -add $filenamebody.aac $filenamebody.base.mp4"); + $exit_value = $? >> 8; + $signal_num = $? & 127; + $dumped_core = $? & 128; + &writelog("ipodtranscode DEBUG MP4Box -add $filenamebody.aac:$exit_value:$signal_num:$dumped_core"); + }else{ + $filelist = `ls -lhtr $recfolderpath/${filenamebody}*`; + $debugenv = `env`; + &writelog("ipodtranscode ERR File not exist.$debugenv.$filelist ;$filenamebody.base.mp4;$filelist;cd $recfolderpath ;$toolpath/perl/tool/MP4Box -fps 29.97 -add $filenamebody.264 -new $filenamebody.base.mp4"); + } }else{ - &writelog("ipodtranscode ERR File not exist.$filenamebody.base.mp4"); - } - -} - -if ($filestatus <= $FILESTATUSTRANSCODEATOM){ - unlink("${mp4outdir}MAQ${mp4filenamestring}.MP4"); - # iPodヘッダ付加 - &changefilestatus($pid,$FILESTATUSTRANSCODEATOM); - &writelog("ipodtranscode ATOM $filenamebody"); - #system ("/usr/local/bin/ffmpeg -y -i $filenamebody.base.mp4 -vcodec copy -acodec copy -f ipod ${mp4outdir}MAQ${mp4filenamestring}.MP4"); - system ("cd $recfolderpath ; MP4Box -ipod $filenamebody.base.mp4"); -$exit_value = $? >> 8; -$signal_num = $? & 127; -$dumped_core = $? & 128; -&writelog("ipodtranscode DEBUG MP4Box -ipod:$exit_value:$signal_num:$dumped_core"); - system("mv $filenamebody.base.mp4 ${mp4outdir}MAQ${mp4filenamestring}.MP4"); - &writelog("ipodtranscode mv $filenamebody.base.mp4 ${mp4outdir}MAQ${mp4filenamestring}.MP4"); -# ipodtranscode mv /home/foltia/php/tv/1329-21-20080829-0017.base.mp4 /home/foltia/php/tv/1329.localized/mp4/MAQ-/home/foltia/php/tv/1329-21-20080829-0017.MP4 - + &writelog("ipodtranscode WARN; Pls. install $toolpath/perl/tool/MP4Box"); + } +#} + +#if ($filestatus <= $FILESTATUSTRANSCODEATOM){ + if (-e "$toolpath/perl/tool/MP4Box"){ + # iPodヘッダ付加 +# &changefilestatus($pid,$FILESTATUSTRANSCODEATOM); + &writelog("ipodtranscode ATOM $filenamebody"); + #system ("/usr/local/bin/ffmpeg -y -i $filenamebody.base.mp4 -vcodec copy -acodec copy -f ipod ${mp4outdir}MAQ${mp4filenamestring}.MP4"); +# system ("cd $recfolderpath ; MP4Box -ipod $filenamebody.base.mp4"); + system ("cd $recfolderpath ; $toolpath/perl/tool/MP4Box -ipod $filenamebody.base.mp4"); + $exit_value = $? >> 8; + $signal_num = $? & 127; + $dumped_core = $? & 128; + &writelog("ipodtranscode DEBUG MP4Box -ipod $filenamebody.base.mp4:$exit_value:$signal_num:$dumped_core"); + if (-e "$filenamebody.base.mp4"){ + unlink("${mp4outdir}MAQ${mp4filenamestring}.MP4"); + system("mv $filenamebody.base.mp4 ${mp4outdir}MAQ${mp4filenamestring}.MP4"); + &writelog("ipodtranscode mv $filenamebody.base.mp4 ${mp4outdir}MAQ${mp4filenamestring}.MP4"); + }else{ + &writelog("ipodtranscode ERR $filenamebody.base.mp4 Not found."); + } + # ipodtranscode mv /home/foltia/php/tv/1329-21-20080829-0017.base.mp4 /home/foltia/php/tv/1329.localized/mp4/MAQ-/home/foltia/php/tv/1329-21-20080829-0017.MP4 + }else{ + &writelog("ipodtranscode WARN; Pls. install $toolpath/perl/tool/MP4Box"); + } } if ($filestatus <= $FILESTATUSTRANSCODECOMPLETE){ + if (-e "${mp4outdir}MAQ${mp4filenamestring}.MP4"){ # 中間ファイル消す &changefilestatus($pid,$FILESTATUSTRANSCODECOMPLETE); @@ -293,5 +303,7 @@ &updatemp4file(); - + }else{ + &writelog("ipodtranscode ERR ; Fail MAQ${mp4filenamestring}.MP4"); + } } @@ -384,19 +396,28 @@ &writelog("ipodtranscode DEBUG thmfilename $thmfilename"); -system ("mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $outputfilename"); - -&writelog("ipodtranscode DEBUG mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $outputfilename"); - -if (-e "$pspdirname/$thmfilename"){ -$timestamp =`date "+%Y%m%d-%H%M%S"`; -chomp $timestamp; - system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename".$timestamp.".THM"); +#system ("mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $outputfilename"); +# +#&writelog("ipodtranscode DEBUG mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $outputfilename"); +if($outputfilename =~ /.m2t$/){ +#ハイビジョンTS +system ("mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -vf framestep=300step,scale=160:90,expand=160:120 -frames 1 $outputfilename"); +&writelog("ipodtranscode DEBUG mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -vf framestep=300step,scale=160:90,expand=160:120 -frames 1 $outputfilename"); }else{ - system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename"); -} -&writelog("ipodtranscode DEBUG convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename"); - -system("rm -rf $pspdirname/0000000*.jpg "); -&writelog("ipodtranscode DEBUG rm -rf $pspdirname/0000000*.jpg"); +#アナログ +system ("mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -vf framestep=300step,scale=165:126,crop=160:120 -frames 1 $outputfilename"); +&writelog("ipodtranscode DEBUG mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -vf framestep=300step,scale=165:126,crop=160:120 -frames 1 $outputfilename"); +} +#if (-e "$pspdirname/$thmfilename"){ +# $timestamp = strftime("%Y%m%d-%H%M%S", localtime); +#chomp $timestamp; +# system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename".$timestamp.".THM"); +#}else{ +# system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename"); +#} +#&writelog("ipodtranscode DEBUG convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename"); + +#system("rm -rf $pspdirname/0000000*.jpg "); +#&writelog("ipodtranscode DEBUG rm -rf $pspdirname/0000000*.jpg"); +system("mv $pspdirname/00000001.jpg $pspdirname/$thmfilename"); }#endsub makethumbnail @@ -407,14 +428,12 @@ if (-e "${mp4outdir}MAQ${mp4filenamestring}.MP4"){ # MP4ファイル名をPIDレコードに書き込み - $DBQuery = "UPDATE foltia_subtitle SET PSPfilename = '$mp4filename' WHERE pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("ipodtranscode UPDATEsubtitleDB $DBQuery"); + $sth = $dbh->prepare($stmt{'ipodtranscode.updatemp4file.1'}); + $sth->execute($mp4filename, $pid); + &writelog("ipodtranscode UPDATEsubtitleDB $stmt{'ipodtranscode.updatemp4file.1'}"); # MP4ファイル名をfoltia_mp4files挿入 - $DBQuery = "insert into foltia_mp4files values ('$tid','$mp4filename') "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("ipodtranscode UPDATEmp4DB $DBQuery"); + $sth = $dbh->prepare($stmt{'ipodtranscode.updatemp4file.2'}); + $sth->execute($tid, $mp4filename); + &writelog("ipodtranscode UPDATEmp4DB $stmt{'ipodtranscode.updatemp4file.2'}"); &changefilestatus($pid,$FILESTATUSALLCOMPLETE); @@ -427,8 +446,6 @@ sub counttranscodefiles(){ -my $DBQuery = "SELECT count(*) FROM foltia_subtitle, foltia_program, foltia_m2pfiles -WHERE filestatus >= $FILESTATUSRECEND AND filestatus < $FILESTATUSTRANSCODECOMPLETE AND foltia_program.tid = foltia_subtitle.TID AND foltia_program.PSP = 1 AND foltia_m2pfiles.m2pfilename = foltia_subtitle.m2pfilename "; -$sth = $dbh->prepare($DBQuery); -$sth->execute(); + $sth = $dbh->prepare($stmt{'ipodtranscode.counttranscodefiles.1'}); + $sth->execute($FILESTATUSRECEND, $FILESTATUSTRANSCODECOMPLETE); my @titlecount= $sth->fetchrow_array; Index: /trunk/install/perl/addatq.pl =================================================================== --- /trunk/install/perl/addatq.pl (リビジョン 1) +++ /trunk/install/perl/addatq.pl (リビジョン 94) @@ -16,4 +16,5 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Schedule::At; use Time::Local; @@ -21,5 +22,5 @@ $path = $0; $path =~ s/addatq.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -38,15 +39,13 @@ #DB検索(TIDとStationIDからPIDへ) - $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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; if ($station == 0){ - $DBQuery = "SELECT count(*) FROM foltia_tvrecord WHERE tid = '$tid' "; + $sth = $dbh->prepare($stmt{'addatq.1'}); + $sth->execute($tid); }else{ - $DBQuery = "SELECT count(*) FROM foltia_tvrecord WHERE tid = '$tid' AND stationid = '$station' "; + $sth = $dbh->prepare($stmt{'addatq.2'}); + $sth->execute($tid, $station); } - $sth = $dbh->prepare($DBQuery); - $sth->execute(); @titlecount = $sth->fetchrow_array; #件数数える @@ -54,8 +53,7 @@ #2以上だったら if ($titlecount[0] >= 2){ - #全曲取りが含まれているか調べる - $DBQuery = "SELECT count(*) FROM foltia_tvrecord WHERE tid = '$tid' AND stationid ='0' "; - $kth = $dbh->prepare($DBQuery); - $kth->execute(); + #全局録りが含まれているか調べる + $kth = $dbh->prepare($stmt{'addatq.3'}); + $kth->execute($tid); @reservecounts = $kth->fetchrow_array; @@ -63,10 +61,10 @@ if($tid == 0){ #今回の引き数がSID 0だったら - #全局取りだけ予約 -# &writelog("addatq DEBUG; ALL STATION RESERVE. TID=$tid SID=$station $titlecount[0] match:$DBQuery"); + #全局録りだけ予約 +# &writelog("addatq DEBUG; ALL STATION RESERVE. TID=$tid SID=$station $titlecount[0] match:$stmt{'addatq.3'}"); &addcue; }else{ #ほかの全局録画addatqが予約入れてくれるからなにもしない -# &writelog("addatq DEBUG; SKIP OPERSTION. TID=$tid SID=$station $titlecount[0] match:$DBQuery"); +# &writelog("addatq DEBUG; SKIP OPERSTION. TID=$tid SID=$station $titlecount[0] match:$stmt{'addatq.3'}"); exit; }#end if ふくまれていたら @@ -75,5 +73,5 @@ &addcue; }else{ -&writelog("addatq error; reserve impossible . TID=$tid SID=$station $titlecount[0] match:$DBQuery"); + &writelog("addatq error; reserve impossible . TID=$tid SID=$station $titlecount[0] match:$stmt{'addatq.3'}"); } @@ -82,5 +80,5 @@ # & addcue; # }else{ -#&writelog("addatq error record TID=$tid SID=$station $titlecount[0] match:$DBQuery"); +#&writelog("addatq error record TID=$tid SID=$station $titlecount[0] match:$stmt{'addatq.3'}"); #} @@ -88,33 +86,30 @@ if ($station == 0){ - $DBQuery = "SELECT * FROM foltia_tvrecord WHERE tid = '$tid' "; + $sth = $dbh->prepare($stmt{'addatq.addcue.1'}); + $sth->execute($tid); }else{ - $DBQuery = "SELECT * FROM foltia_tvrecord WHERE tid = '$tid' AND stationid = '$station' "; + $sth = $dbh->prepare($stmt{'addatq.addcue.2'}); + $sth->execute($tid, $station); } - $sth = $dbh->prepare($DBQuery); -$sth->execute(); @titlecount= $sth->fetchrow_array; $bitrate = $titlecount[2];#ビットレート取得 #PID抽出 -$now = &epoch2foldate(`date +%s`); -$twodaysafter = &epoch2foldate(`date +%s` + (60 * 60 * 24 * 2)); + $now = &epoch2foldate(time()); + $twodaysafter = &epoch2foldate(time() + (60 * 60 * 24 * 2)); #キュー入れは直近2日後まで if ($station == 0 ){ - $DBQuery = " -SELECT * from foltia_subtitle WHERE tid = '$tid' AND startdatetime > '$now' AND startdatetime < '$twodaysafter' "; + $sth = $dbh->prepare($stmt{'addatq.addcue.3'}); + $sth->execute($tid, $now, $twodaysafter); }else{ - $DBQuery = " -SELECT * from foltia_subtitle WHERE tid = '$tid' AND stationid = '$station' AND startdatetime > '$now' AND startdatetime < '$twodaysafter' "; #stationIDからrecch -$getrecchquery="SELECT stationid , stationrecch FROM foltia_station where stationid = '$station' "; - $stationh = $dbh->prepare($getrecchquery); - $stationh->execute(); + $stationh = $dbh->prepare($stmt{'addatq.addcue.4'}); + $stationh->execute($station); @stationl = $stationh->fetchrow_array; $recch = $stationl[1]; -} - $sth = $dbh->prepare($DBQuery); - $sth->execute(); + $sth = $dbh->prepare($stmt{'addatq.addcue.5'}); + $sth->execute($tid, $station, $now, $twodaysafter); + } while (($pid , @@ -131,7 +126,6 @@ if ($station == 0 ){ #stationIDからrecch -$getrecchquery="SELECT stationid , stationrecch FROM foltia_station where stationid = '$stationid' "; - $stationh = $dbh->prepare($getrecchquery); - $stationh->execute(); + $stationh = $dbh->prepare($stmt{'addatq.addcue.6'}); + $stationh->execute($stationid); @stationl = $stationh->fetchrow_array; $recch = $stationl[1]; Index: /trunk/install/perl/deletemovie.pl =================================================================== --- /trunk/install/perl/deletemovie.pl (リビジョン 83) +++ /trunk/install/perl/deletemovie.pl (リビジョン 94) @@ -17,5 +17,5 @@ $path = $0; $path =~ s/deletemovie.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } Index: /trunk/install/perl/recwrap.pl =================================================================== --- /trunk/install/perl/recwrap.pl (リビジョン 91) +++ /trunk/install/perl/recwrap.pl (リビジョン 94) @@ -15,4 +15,5 @@ use DBI; use DBD::Pg; +use DBD::SQLite; use Schedule::At; use Time::Local; @@ -21,5 +22,5 @@ $path = $0; $path =~ s/recwrap.pl$//i; -if ($pwd ne "./"){ +if ($path ne "./"){ push( @INC, "$path"); } @@ -46,7 +47,5 @@ #DB初期化 - 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;; +$dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; @@ -57,5 +56,5 @@ } -$outputfile = `date +%Y%m%d-%H%M --date "1 min "`; +$outputfile = strftime("%Y%m%d-%H%M", localtime(time + 60)); chomp($outputfile); @@ -77,5 +76,5 @@ &writelog("recwrap RECSTART DIGITAL $digitalstationband $digitalch $reclength $stationid 0 $outputfilename $tid $countno friio"); #録画 -$starttime = (`date +%s`); + $starttime = time(); $oserr = system("$toolpath/perl/digitaltvrecording.pl $digitalstationband $digitalch $reclength $stationid 0 $outputfilename $tid $countno friio"); $oserr = $oserr / 256; @@ -119,5 +118,5 @@ #録画 #system("$toolpath/perl/tvrecording.pl $recch $reclength 0 $outputfile $bitrate $tid $countno"); -$starttime = (`date +%s`); + $starttime = time(); $oserr = system("$toolpath/perl/tvrecording.pl $recch $reclength 0 $outputfilename $bitrate $tid $countno"); @@ -131,7 +130,7 @@ #デバイスビジーで即死してないか検出 -$now = (`date +%s`); +$now = time(); if ($now < $starttime + 100){ #録画プロセス起動してから100秒以内に戻ってきてたら - $retrycounter == 0; + $retrycounter = 0; while($now < $starttime + 100){ if($retrycounter >= 5){ @@ -141,10 +140,10 @@ &writelog("recwrap retry recording $now $starttime"); #アナログ録画 -$starttime = (`date +%s`); + $starttime = time(); if($outputfilename =~ /.m2t$/){ $outputfilename =~ s/.m2t$/.m2p/; } $oserr = system("$toolpath/perl/tvrecording.pl $recch $reclength N $outputfilename $bitrate $tid $countno"); -$now = (`date +%s`); + $now = time(); $oserr = $oserr / 256; if ($oserr == 1){ @@ -160,15 +159,13 @@ # m2pファイル名をPIDレコードに書き込み - $DBQuery = "UPDATE foltia_subtitle SET m2pfilename = '$outputfilename' WHERE pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("recwrap DEBUG UPDATEDB $DBQuery"); +$sth = $dbh->prepare($stmt{'recwrap.1'}); +$sth->execute($outputfilename, $pid); +&writelog("recwrap DEBUG UPDATEDB $stmt{'recwrap.1'}"); &changefilestatus($pid,$FILESTATUSRECEND); # m2pファイル名をPIDレコードに書き込み - $DBQuery = "insert into foltia_m2pfiles values ('$outputfilename')"; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("recwrap DEBUG UPDATEDB $DBQuery"); +$sth = $dbh->prepare($stmt{'recwrap.2'}); +$sth->execute($outputfilename); +&writelog("recwrap DEBUG UPDATEDB $stmt{'recwrap.2'}"); # Starlight breaker向けキャプチャ画像作成 @@ -181,10 +178,8 @@ - # MPEG4 ------------------------------------------------------ #MPEG4トラコン必要かどうか -$DBQuery = "SELECT psp,aspect,title FROM foltia_program WHERE tid = '$tid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); +$sth = $dbh->prepare($stmt{'recwrap.3'}); +$sth->execute($tid); @psptrcn= $sth->fetchrow_array; if ($psptrcn[0] == 1 ){#トラコン番組 @@ -192,240 +187,8 @@ exec ("$toolpath/perl/ipodtranscode.pl"); exit; -# -# ここから下は旧エンコード#2008/12/23 -# 新エンコードはDBを見て未完了MPEG2を順次トラコン処理、 -# 分散エンコードもきっとラクチンに対応可能 -# 新エンコードではXviD/M4VスタイルとPSPファイル名対応を廃止 - -&changefilestatus($pid,80); -#MPEG4ムービーディレクトリがあるかどうか - -#TIDが100以上の3桁の場合はそのまま -my $pspfilnamehd = ""; - -$pspfilnamehd = $tid; -&makemp4dir($tid); -$pspdirname = "$tid.localized/"; -$pspdirname = $recfolderpath."/".$pspdirname; - -#なければ作る -#unless (-e $pspdirname ){ -# system("$toolpath/perl/mklocalizeddir.pl $tid"); -# #&writelog("recwrap mkdir $pspdirname"); -#} -$pspdirname = "$tid.localized/mp4/"; -$pspdirname = $recfolderpath."/".$pspdirname; -#なければ作る -#unless (-e $pspdirname ){ -# mkdir $pspdirname ,0777; -# #&writelog("recwrap mkdir $pspdirname"); -#} - -#ファイル名決定 -if ($mp4filenamestyle == 1){# 1;よりわかりやすいファイル名 - $pspfilname = $mp4newstylefilename ; - -}else{##0:PSP ファームウェアver.2.80より前と互換性を持つファイル名 -#・フォルダ名[100MNV01]の100の部分は変更可(100〜999)。 -# MP_ROOT ━ 100MNV01 ┳ M4V00001.MP4（動画） -#┃ 　 ┗ M4V00001.THM（サムネイル）※必須ではない - -#ファイル名決定 -#ファイル名決定 #新アルゴリズム -#TID 0000-3599まで[3桁] -#話数 00-999まで[2桁] - -my $pspfilnameft = ""; -my $pspfilnameyearhd = ""; -my $pspfilnameyearft = ""; - -$btid = $tid % 3600; -# print "$btid\n"; - -if($btid >= 0 && $btid < 1000){ - - $pspfilnamehd = sprintf("%03d",$btid); - -}elsif ($btid >= 1000 && $btid < 3600){ - $pspfilnameyearhd = substr($btid, 0, 2); - $pspfilnameyearhd =~ s/10/A/; - $pspfilnameyearhd =~ s/11/B/; - $pspfilnameyearhd =~ s/12/C/; - $pspfilnameyearhd =~ s/13/D/; - $pspfilnameyearhd =~ s/14/E/; - $pspfilnameyearhd =~ s/15/F/; - $pspfilnameyearhd =~ s/16/G/; - $pspfilnameyearhd =~ s/17/H/; - $pspfilnameyearhd =~ s/18/I/; - $pspfilnameyearhd =~ s/19/J/; - $pspfilnameyearhd =~ s/20/K/; - $pspfilnameyearhd =~ s/21/L/; - $pspfilnameyearhd =~ s/22/M/; - $pspfilnameyearhd =~ s/23/N/; - $pspfilnameyearhd =~ s/24/O/; - $pspfilnameyearhd =~ s/25/P/; - $pspfilnameyearhd =~ s/26/Q/; - $pspfilnameyearhd =~ s/27/R/; - $pspfilnameyearhd =~ s/28/S/; - $pspfilnameyearhd =~ s/29/T/; - $pspfilnameyearhd =~ s/30/U/; - $pspfilnameyearhd =~ s/31/V/; - $pspfilnameyearhd =~ s/32/W/; - $pspfilnameyearhd =~ s/33/X/; - $pspfilnameyearhd =~ s/34/Y/; - $pspfilnameyearhd =~ s/35/Z/; - -$pspfilnameyearft = substr($btid, 2, 2); -$pspfilnameyearft = sprintf("%02d",$pspfilnameyearft); -$pspfilnamehd = $pspfilnameyearhd . $pspfilnameyearft; - -} - -# 話数 -if (0 < $countno && $countno < 100 ){ -# 2桁 - $pspfilnameft = sprintf("%02d",$countno); -}elsif(100 <= $countno && $countno < 1000 ){ -# 3桁 - $pspfilnameft = sprintf("%03d",$countno); # 話数3桁 - $pspfilnamehd = substr($pspfilnamehd, 0, 2); # TID 二桁　後ろ1バイト落とし -}elsif(1000 <= $countno && $countno < 10000 ){ -# 4桁 - $pspfilnameft = sprintf("%04d",$countno); # 話数4桁 - $pspfilnamehd = substr($pspfilnamehd, 0, 1); # TID 1桁　後ろ2バイト落とし - - -}elsif($countno == 0){ -#タイムスタンプが最新のMP4ファイル名取得 -my $newestmp4filename = `cd $pspdirname ; ls -t *.MP4 | head -1`; - if ($newestmp4filename =~ /M4V$tid/){ - $nowcountno = $' ; - $nowcountno++; - $pspfilnameft = sprintf("%02d",$nowcountno); - while (-e "$pspdirname/M4V".$pspfilnamehd.$pspfilnameft.".MP4"){ - $nowcountno++; - $pspfilnameft = sprintf("%02d",$nowcountno); - print "File exist:$nowcountno\n"; - } -#print "NeXT\n"; -}else{ -# 0の場合　週番号を100から引いたもの -# week number of year with Monday as first day of week (01..53) -#だったけど常に0に -# my $weeno = `date "+%V"`; -# $weeno = 100 - $weeno ; -# $pspfilnameft = sprintf("%02d",$weeno); - $pspfilnameft = sprintf("%02d",0); -#print "WEEKNO\n"; -} - -} - -my $pspfilname = $pspfilnamehd.$pspfilnameft ; -# print "$pspfilname($pspfilnamehd/$pspfilnameft)\n"; -}# endif MP4ファイル名が新styleなら -#2006/12/03_10:30:24 recwrap TRCNSTART vfr4psp.sh /home/foltia/php/tv/591-87-20061203-1000.m2p -591-87-20061203-1000 /home/foltia/php/tv/591.localized/mp4/ 3 - - -# トラコンキューイング #2007/7/10 -my $trcnprocesses = ""; -my $cpucores = `ls /proc/acpi/processor | wc -l`; -$cpucores =~ s/[^0-9]//gi; -unless ($cpucores >= 1 ){ - $cpucores = 1; -} -do { - $trcnprocesses = `ps ax | grep ffmpeg | grep -v grep | wc -l `; - $trcnprocesses =~ s/[^0-9]//gi; - # 既にトラコンプロセスが走っているなら適当に待機 - if ($trcnprocesses >= $cpucores){ - if (-e "/proc/uptime" ){ - $loadaverage = `uptime`; - chomp($loadaverage); - }else{ - $loadaverage = ""; - } - &writelog("recwrap TRCN WAITING :$trcnprocesses / $cpucores :$outputfilename $loadaverage"); - sleep 113; - sleep ($recch)*5; - } -} until ($trcnprocesses < $cpucores); - - -if (($trconqty eq "")||($trconqty == 0 )){ - &writelog("recwrap TRCNSTART vfr4psp.sh $recfolderpath/$outputfilename $pspfilname $pspdirname $psptrcn[1]"); - system("$toolpath/perl/transcode/vfr4psp.sh $recfolderpath/$outputfilename $pspfilname $pspdirname $psptrcn[1]"); - &writelog("recwrap TRCNEND vfr4psp.sh $recfolderpath/$outputfilename $pspfilname $pspdirname $psptrcn[1]"); - #最適化 - $DBQuery = "SELECT subtitle FROM foltia_subtitle WHERE tid = '$tid' AND countno = '$countno' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); - @programtitle = $sth->fetchrow_array; - if ( $countno == "0" ){ - $pspcountno = ""; - }else{ - $pspcountno = $countno ; - } - &writelog("recwrap OPTIMIZE mp4psp -p $pspdirname/M4V$pspfilname.MP4 -t '$psptrcn[2] $pspcountno $programtitle[0]' "); - Jcode::convert(\$programtitle[0],'euc'); - system ("/usr/local/bin/mp4psp -p $pspdirname/M4V$pspfilname.MP4 -t '$psptrcn[2] $pspcountno $programtitle[0]'") ; -$mp4filename = "M4V${pspfilname}.MP4"; -$thmfilename = "M4V${pspfilname}.THM"; -}else{# #2006/12/6 新エンコーダ - - &writelog("recwrap TRCNSTART ipodtranscode.pl $recfolderpath/$outputfilename $pspfilname $pspdirname $pid $psptrcn[1]"); - system("$toolpath/perl/ipodtranscode.pl $recfolderpath/$outputfilename $pspfilname $pspdirname $pid $psptrcn[1]"); - &writelog("recwrap TRCNEND ipodtranscode.pl $recfolderpath/$outputfilename $pspfilname $pspdirname $psptrcn[1]"); - - if($trconqty >= 2){#H.264/AVCなら - $mp4filename = "MAQ${pspfilname}.MP4"; - $thmfilename = "MAQ${pspfilname}.THM"; - }else{ - $mp4filename = "M4V${pspfilname}.MP4"; - $thmfilename = "M4V${pspfilname}.THM"; - } -} - -#サムネール - -# mplayer -ss 00:01:20 -vo jpeg:outdir=/home/foltia/php/tv/443MNV01 -ao null -sstep 1 -frames 3 -v 3 /home/foltia/php/tv/443-07-20050218-0030.m2p -#2005/02/22_18:30:05 recwrap TRCNSTART vfr4psp.sh /home/foltia/php/tv/447-21-20050222-1800.m2p 44721 /home/foltia/php/tv/447MNV01 3 -&writelog("recwrap THAMJ mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $recfolderpath/$outputfilename "); -system ("mplayer -ss 00:01:20 -vo jpeg:outdir=$pspdirname -ao null -sstep 1 -frames 3 -v 3 $recfolderpath/$outputfilename"); -&writelog("recwrap THAMI convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/M4V$pspdirname.THM "); - -if (-e "$pspdirname/$thmfilename"){ -$timestamp =`date "+%Y%m%d-%H%M%S"`; -chomp $timestamp; - system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename".$timestamp.".THM"); - -}else{ - system("convert -crop 160x120+1+3 -resize 165x126\! $pspdirname/00000002.jpg $pspdirname/$thmfilename"); -} -# rm -rf 00000001.jpg -# convert -resize 160x120\! 00000002.jpg M4V44307.THM -# rm -rf 00000002.jpg -system("rm -rf $pspdirname/0000000*.jpg "); - - - - -# MP4ファイル名をPIDレコードに書き込み - $DBQuery = "UPDATE foltia_subtitle SET PSPfilename = '$mp4filename' WHERE pid = '$pid' "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("recwrap UPDATEsubtitleDB $DBQuery"); - -# MP4ファイル名をfoltia_mp4files挿入 - $DBQuery = "insert into foltia_mp4files values ('$tid','$mp4filename') "; - $sth = $dbh->prepare($DBQuery); - $sth->execute(); -&writelog("recwrap UPDATEmp4DB $DBQuery"); - -&changefilestatus($pid,200); }#PSPトラコンあり sub continuousrecordingcheck(){ -my $now = `date +%s --date "2 min "`; + my $now = time() + 60 * 2; &writelog("recwrap DEBUG continuousrecordingcheck() now $now"); my @processes =`ps ax | grep recfriio`; @@ -474,16 +237,8 @@ $startdatetime = $startdate.$filenameparts[0]; #DBから録画中番組のデータ探す - $DBQuery = " -SELECT foltia_subtitle.tid,foltia_subtitle.countno,foltia_subtitle.subtitle,foltia_subtitle.startdatetime ,foltia_subtitle.enddatetime ,foltia_subtitle.lengthmin ,foltia_tvrecord.bitrate , foltia_subtitle.startoffset , foltia_subtitle.pid ,foltia_tvrecord.digital -FROM foltia_subtitle ,foltia_tvrecord -WHERE -foltia_tvrecord.tid = foltia_subtitle.tid AND -foltia_tvrecord.tid = $tid AND -foltia_subtitle.startdatetime = $startdatetime AND -foltia_tvrecord.digital = 1"; - &writelog("recwrap DEBUG continuousrecordingcheck() $DBQuery"); - $sth = $dbh->prepare($DBQuery); + &writelog("recwrap DEBUG continuousrecordingcheck() $stmt{'recwrap.7'}"); + $sth = $dbh->prepare($stmt{'recwrap.7'}); &writelog("recwrap DEBUG continuousrecordingcheck() prepare"); - $sth->execute(); + $sth->execute($tid, $startdatetime); &writelog("recwrap DEBUG continuousrecordingcheck() execute"); @recfile = $sth->fetchrow_array; Index: /trunk/install/php/reservecomp.php =================================================================== --- /trunk/install/php/reservecomp.php (リビジョン 83) +++ /trunk/install/php/reservecomp.php (リビジョン 94) @@ -67,12 +67,11 @@ //タイトル取得 - $query = "select title from foltia_program where tid='$tid'"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { + $query = "select title from foltia_program where tid = ? "; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { $title = "(未登録)"; }else{ - $rowdata = pg_fetch_row($rs, 0); $title = htmlspecialchars($rowdata[0]); } @@ -130,11 +129,10 @@ } $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +$rowdata = $rs->fetch(); +if (! $rowdata) { echo("放映予定はいまのところありません
"); } else{ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); ?> @@ -155,14 +153,11 @@ \n"); - /* pg_fetch_row で一行取り出す */ - $rowdata = pg_fetch_row($rs, $row); - for ($col = 0; $col < $maxcols; $col++) { /* 列に対応 */ echo("\n"); } echo("\n"); - } + } while ($rowdata = $rs->fetch()); }//end if ?> @@ -177,38 +172,31 @@ //既存が予約あって、新着が全局予約だったら if ($station ==0){ - $query = " -SELECT - * -FROM foltia_tvrecord -WHERE tid = '$tid' -"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - if ($maxrows > 0){ //既存局を消す $query = "DELETE FROM foltia_tvrecord -WHERE tid = '$tid' -"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - } +WHERE tid = ? +"; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid)); }//endif $query = " SELECT - * +count(*) FROM foltia_tvrecord -WHERE tid = '$tid' AND stationid = '$station' -"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - +WHERE tid = ? AND stationid = ? +"; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid,$station)); + $maxrows = $rs->fetchColumn(0); if ($maxrows == 0) { //新規追加 - $query = "INSERT INTO foltia_tvrecord values ('$tid','$station','$bitrate','$usedigital')"; - $rs = m_query($con, $query, "DB書き込みに失敗しました"); + $query = "INSERT INTO foltia_tvrecord values (?,?,?,?)"; +// $rs = m_query($con, $query, "DB書き込みに失敗しました"); + $rs = sql_query($con, $query, "DB書き込みに失敗しました",array($tid,$station,$bitrate,$usedigital)); }else{//修正　(ビットレート) $query = "UPDATE foltia_tvrecord SET - bitrate = '$bitrate' , digital = '$usedigital' WHERE tid = '$tid' AND stationid = '$station' "; - $rs = m_query($con, $query, "DB書き込みに失敗しました"); + bitrate = ? , digital = ? WHERE tid = ? AND stationid = ? "; +// $rs = m_query($con, $query, "DB書き込みに失敗しました"); + $rs = sql_query($con, $query, "DB書き込みに失敗しました",array( $bitrate, $usedigital , $tid , $station )); } Index: /trunk/install/php/folcast.php =================================================================== --- /trunk/install/php/folcast.php (リビジョン 70) +++ /trunk/install/php/folcast.php (リビジョン 94) @@ -51,5 +51,5 @@ $query = " SELECT foltia_program.tid,foltia_program.title, -foltia_subtitle.countno , foltia_subtitle.subtitle , foltia_subtitle.startdatetime, foltia_subtitle.pspfilename,foltia_subtitle.lengthmin,foltia_subtitle.enddatetime FROM foltia_subtitle , foltia_program WHERE \"pspfilename\" ~~ 'M%%' AND foltia_program.tid = foltia_subtitle.tid AND foltia_program.tid = $tid +foltia_subtitle.countno , foltia_subtitle.subtitle , foltia_subtitle.startdatetime, foltia_subtitle.pspfilename,foltia_subtitle.lengthmin,foltia_subtitle.enddatetime FROM foltia_subtitle , foltia_program WHERE \"pspfilename\" LIKE 'M%%' AND foltia_program.tid = foltia_subtitle.tid AND foltia_program.tid = $tid ORDER BY \"enddatetime\" DESC offset 0 limit $max @@ -59,8 +59,9 @@ SELECT foltia_program.tid,foltia_program.title FROM foltia_program -WHERE foltia_program.tid = $tid +WHERE foltia_program.tid = ? "; - $titlers = m_query($con, $query, "DBクエリに失敗しました"); - $rowdata = pg_fetch_row($titlers, 0); +// $titlers = m_query($con, $query, "DBクエリに失敗しました"); + $titlers = sql_query($con, $query, "DBクエリに失敗しました",array($tid)); + $rowdata = $titlers->fetch(); $rsstitle = $rowdata[1]; }else{ @@ -68,6 +69,6 @@ $query = " SELECT foltia_program.tid,foltia_program.title, -foltia_subtitle.countno , foltia_subtitle.subtitle , foltia_subtitle.startdatetime, foltia_subtitle.pspfilename,foltia_subtitle.lengthmin,foltia_subtitle.enddatetime FROM foltia_subtitle , foltia_program WHERE \"pspfilename\" ~~ 'M%%' AND foltia_program.tid = foltia_subtitle.tid ORDER BY \"enddatetime\" DESC -offset 0 limit $max +foltia_subtitle.countno , foltia_subtitle.subtitle , foltia_subtitle.startdatetime, foltia_subtitle.pspfilename,foltia_subtitle.lengthmin,foltia_subtitle.enddatetime FROM foltia_subtitle , foltia_program WHERE \"pspfilename\" LIKE 'M%%' AND foltia_program.tid = foltia_subtitle.tid ORDER BY \"enddatetime\" DESC +offset 0 limit ? "; $rsstitle = "新規録画"; @@ -96,14 +97,12 @@ print $header; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($max)); +$rowdata = $rs->fetch(); -if ($maxrows == 0) { +if (! $rowdata) { //die_exit("No items"); }else{ - -for ($row = 0; $row < $maxrows; $row++) { - $rowdata = pg_fetch_row($rs, $row); - + do { //$title = mb_convert_encoding($rowdata[1],"UTF-8", "EUC-JP"); $tid = $rowdata[0]; @@ -163,5 +162,5 @@ print $item ; -}//for + } while ($rowdata = $rs->fetch()); //do }//if Index: /trunk/install/php/m.php =================================================================== --- /trunk/install/php/m.php (リビジョン 83) +++ /trunk/install/php/m.php (リビジョン 94) @@ -97,12 +97,9 @@ SELECT stationname FROM foltia_station -WHERE stationid = $recstid"; - $stationvalid = m_query($con, $query, "DBクエリに失敗しました"); - $stationcount = pg_num_rows($stationvalid); - - if ($stationcount == 1){ - $recstationname = pg_fetch_row($stationvalid, 0); - //valid - }else{ +WHERE stationid = ? "; +// $stationvalid = m_query($con, $query, "DBクエリに失敗しました"); + $stationvalid = sql_query($con, $query, "DBクエリに失敗しました",array($recstid)); + $recstationname = $stationvalid->fetch(); + if (! $recstationname) { $errflag = 3; $errmsg = "放送局設定が異常です。"; @@ -128,21 +125,25 @@ //min pidを探す $query = "SELECT min(pid) FROM foltia_subtitle "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - if ($maxrows == 0){ - $insertpid = -1 ; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました"); + $rowdata = $rs->fetch(); + if (! $rowdata) { + $insertpid = -1 ; }else{ - $rowdata = pg_fetch_row($rs, 0); - $insertpid = $rowdata[0]; - $insertpid-- ; + if ($rowdata[0] > 0) { + $insertpid = -1 ; + }else{ + $insertpid = $rowdata[0]; + $insertpid-- ; + } } // next 話数を探す $query = "SELECT max(countno) FROM foltia_subtitle WHERE tid = 0"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - if ($maxrows == 0){ +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました"); + $rowdata = $rs->fetch(); + if (! $rowdata) { $nextcno = 1 ; }else{ - $rowdata = pg_fetch_row($rs, 0); $nextcno = $rowdata[0]; $nextcno++ ; @@ -159,8 +160,9 @@ insert into foltia_subtitle (pid ,tid ,stationid , countno ,subtitle , startdatetime ,enddatetime ,startoffset , lengthmin , epgaddedby ) - values ( '$insertpid','0','$recstid', - '$nextcno','$pname','$startdatetime','$enddatetime','0' ,'$lengthmin', '$memberid')"; + values ( ?,'0',?,?,?,?,?,'0',?,?)"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); +// $rs = m_query($con, $query, "DBクエリに失敗しました"); +//print "【DEBUG】$insertpid,$recstid,$nextcno,$pname,$startdatetime,$enddatetime ,$lengthmin,$memberid
\n"; + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($insertpid,$recstid,$nextcno,$pname,$startdatetime,$enddatetime ,$lengthmin,$memberid)); //addatq.pl @@ -168,6 +170,21 @@ //引数　TID チャンネルID //echo("$toolpath/perl/addatq.pl $tid $station"); - - $oserr = system("$toolpath/perl/addatq.pl 0 0"); + exec("$toolpath/perl/addatq.pl 0 0"); + $oserr = system("$toolpath/perl/addatq.pl 0 0"); + //--------------------------------------------------- + if ($oserr){ + print "[DEBUG]$oserr 「$toolpath/perl/addatq.pl 0 0」
\n"; + }else{ + print "[DEBUG]exec addatq.pl false 「$toolpath/perl/addatq.pl 0 0」
\n"; + + $oserr = system("$toolpath/perl/perltestscript.pl"); + if ($oserr){ + print "[DEBUG]exec perltestscript.pl $oserr
\n"; + }else{ + print "[DEBUG]exec perltestscript.pl false
\n"; + } + + } + //----------------------------------------------------- }else{ print "EPG予約を行う権限がありません。"; @@ -200,5 +217,5 @@ }//　初回表示かデータ処理か ?> - +

放送日: @@ -215,5 +232,5 @@ 0 @@ -221,35 +238,33 @@ SELECT DISTINCT stationid,stationname,stationrecch ,digitalch FROM foltia_station -WHERE digitalch > 0 -ORDER BY \"stationid\" ASC"; - - $stations = m_query($con, $query, "DBクエリに失敗しました"); - $stationcount = pg_num_rows($stations); - -if ($stationcount > 0 ){ - for ($row = 0; $row < $stationcount ; $row++) { - $rowdata = pg_fetch_row($stations, $row); +WHERE digitalch > 0 +ORDER BY x ASC"; + +$stations = sql_query($con, $query, "DBクエリに失敗しました"); +$rowdata = $stations->fetch(); + +if ($rowdata) { + do { if ($recstid == $rowdata[0]){ - print " $rowdata[1] ($rowdata[2]ch)　\n"; + print " $rowdata[1] ($rowdata[2]ch / $rowdata[3]ch)　\n"; }else{ - print " $rowdata[1] ($rowdata[2]ch)　\n"; + print " $rowdata[1] ($rowdata[2]ch / $rowdata[3]ch)　\n"; } - } + } while ($rowdata = $stations->fetch()); }else{ print "放送局データベースが正しくセットアップされていません。録画可能局がありません"; } - +//外部入力チャンネル $query = " -SELECT stationid,stationname,stationrecch +SELECT stationid as x ,stationname,stationrecch FROM foltia_station WHERE stationrecch > -2 AND stationrecch < 1 -ORDER BY \"stationid\" ASC"; - - $stations = m_query($con, $query, "DBクエリに失敗しました"); - $stationcount = pg_num_rows($stations); - -if ($stationcount > 0 ){ - for ($row = 0; $row < $stationcount ; $row++) { - $rowdata = pg_fetch_row($stations, $row); +ORDER BY x ASC"; + +// $stations = m_query($con, $query, "DBクエリに失敗しました"); + $stations = sql_query($con, $query, "DBクエリに失敗しました"); +$rowdata = $stations->fetch(); +if ($rowdata) { + do { if ($rowdata[0] != 0){ if ($recstid == $rowdata[0]){ @@ -260,5 +275,5 @@ } - } + } while ($rowdata = $stations->fetch()); } /* Index: /trunk/install/php/foltia_config2.php.template =================================================================== --- /trunk/install/php/foltia_config2.php.template (リビジョン 83) +++ /trunk/install/php/foltia_config2.php.template (リビジョン 94) @@ -27,8 +27,6 @@ // データベース接続設定 - define("DATABASE_NAME", "foltia"); - define("DBHOST", "localhost"); - define("USER_NAME" , "foltia" ); - define("USER_PASSWORD", "passwd" ); +// define("DSN", "pgsql:host=localhost dbname=foltia user=foltia password= "); +define("DSN", "sqlite:/home/foltia/foltia.sqlite"); // $mylocalip = "192.168.0.177" ; //動いている機械のIPアドレス Index: /trunk/install/php/delreserve.php =================================================================== --- /trunk/install/php/delreserve.php (リビジョン 70) +++ /trunk/install/php/delreserve.php (リビジョン 94) @@ -44,10 +44,9 @@ "); } -$sid = getgetnumform(sid); + $sid = getgetnumform("sid"); if ($sid == "") { die_exit("局がありません
"); @@ -70,14 +69,13 @@ foltia_tvrecord.stationid FROM foltia_tvrecord , foltia_program , foltia_station -WHERE foltia_tvrecord.tid = foltia_program.tid AND foltia_tvrecord.stationid = foltia_station .stationid AND foltia_tvrecord.tid = $tid AND foltia_tvrecord.stationid = $sid "; - - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +WHERE foltia_tvrecord.tid = foltia_program.tid AND foltia_tvrecord.stationid = foltia_station .stationid AND foltia_tvrecord.tid = ? AND foltia_tvrecord.stationid = ? "; + +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid,$sid)); +$rowdata = $rs->fetch(); + +if (! $rowdata ) { die_exit("登録番組がありません
"); } - $rowdata = pg_fetch_row($rs, 0); - $tid = htmlspecialchars($rowdata[0]); $stationname = htmlspecialchars($rowdata[1]); @@ -107,6 +105,8 @@ DELETE FROM foltia_tvrecord -WHERE foltia_tvrecord.tid = $tid AND foltia_tvrecord.stationid = $sid "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); +WHERE foltia_tvrecord.tid = ? AND foltia_tvrecord.stationid = ? "; +$rs->closeCursor(); +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid,$sid)); } @@ -164,15 +164,14 @@ FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid - AND foltia_subtitle.startdatetime >= '$now' AND foltia_program.tid ='$tid' + AND foltia_subtitle.startdatetime >= ? AND foltia_program.tid = ? ORDER BY foltia_subtitle.startdatetime ASC "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($now,$tid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { echo("放映予定はありません
"); - } - else{ - $maxcols = pg_num_fields($rs); +}else{ + $maxcols = $rs->columnCount(); ?>

".htmlspecialchars($rowdata[$col])."
@@ -192,14 +191,11 @@ \n"); - /* pg_fetch_row で一行取り出す */ - $rowdata = pg_fetch_row($rs, $row); - for ($col = 0; $col < $maxcols; $col++) { /* 列に対応 */ echo("\n"); } echo("\n"); - } + } while ($row = $rs->fetch()); }//end if ?> Index: /trunk/install/php/deletemovie.php =================================================================== --- /trunk/install/php/deletemovie.php (リビジョン 76) +++ /trunk/install/php/deletemovie.php (リビジョン 94) @@ -88,8 +88,10 @@ FROM foltia_subtitle , foltia_program WHERE foltia_program.tid = foltia_subtitle.tid - AND foltia_subtitle.tid = $filesplit[0] + AND foltia_subtitle.tid = ? "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$rowdata = pg_fetch_row($rs, $row); +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($filesplit[0])); + $rall = $rs->fetchAll(); + $rowdata = $rall[$row]; //print" $fName./$rowdata[1]//$rowdata[2]
\n"; $title = $rowdata[1]; @@ -103,9 +105,11 @@ FROM foltia_subtitle , foltia_program WHERE foltia_program.tid = foltia_subtitle.tid - AND foltia_subtitle.tid = $filesplit[0] - AND foltia_subtitle.countno = $filesplit[1] + AND foltia_subtitle.tid = ? + AND foltia_subtitle.countno = ? "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$rowdata = pg_fetch_row($rs, $row); +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($filesplit[0] ,$filesplit[1])); + $rall = $rs->fetchAll(); + $rowdata = $rall[$row]; //print" $fName./$rowdata[1]/$rowdata[2]/$rowdata[3]
\n"; $title = $rowdata[1]; @@ -119,7 +123,4 @@ $count = htmlspecialchars($count); $subtitle = htmlspecialchars($subtitle); - -//-- - print " @@ -138,7 +139,8 @@ $query = " DELETE FROM foltia_m2pfiles -WHERE m2pfilename = '$fName' +WHERE m2pfilename = ? "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($fName)); //削除処理 Index: /trunk/install/php/showplaylist.php =================================================================== --- /trunk/install/php/showplaylist.php (リビジョン 83) +++ /trunk/install/php/showplaylist.php (リビジョン 94) @@ -115,8 +115,10 @@ FROM foltia_subtitle , foltia_program WHERE foltia_program.tid = foltia_subtitle.tid - AND foltia_subtitle.tid = $filesplit[0] -"; -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$rowdata = pg_fetch_row($rs, $row); + AND foltia_subtitle.tid = ? +"; +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($filesplit[0])); + $rall = $rs->fetchAll(); + $rowdata = $rall[$row]; //print" $fName./$rowdata[1]//$rowdata[2]
\n"; $title = $rowdata[1]; @@ -130,9 +132,11 @@ FROM foltia_subtitle , foltia_program WHERE foltia_program.tid = foltia_subtitle.tid - AND foltia_subtitle.tid = $filesplit[0] - AND foltia_subtitle.countno = $filesplit[1] -"; -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$rowdata = pg_fetch_row($rs, $row); + AND foltia_subtitle.tid = ? + AND foltia_subtitle.countno = ? +"; +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($filesplit[0],$filesplit[1])); + $rall = $rs->fetchAll(); + $rowdata = $rall[$row]; //print" $fName./$rowdata[1]/$rowdata[2]/$rowdata[3]
\n"; $title = $rowdata[1]; @@ -197,11 +201,9 @@ } -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$maxrows = pg_num_rows($rs); - -if ($maxrows > 0){ -for ($row = 0; $row < $maxrows; $row++) { -$rowdata = pg_fetch_row($rs, $row); - +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました"); +$rowdata = $rs->fetch(); +if ($rowdata) { + do { $tid = htmlspecialchars($rowdata[0]); $title = htmlspecialchars($rowdata[1]); @@ -235,5 +237,5 @@ print "\n "; -}//for + } while ($rowdata = $rs->fetch()); }else{ print " @@ -262,9 +264,8 @@ "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); -$maxrows = pg_num_rows($rs); - - -if ($maxrows > 0){ +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました"); + $rowdata = $rs->fetch(); + if ($rowdata) { print "

未読タイトルを表示します。
@@ -279,14 +280,11 @@ "; -for ($row = 0; $row < $maxrows; $row++) { -$rowdata = pg_fetch_row($rs, $row); - + do { $tid = htmlspecialchars($rowdata[0]); $title = htmlspecialchars($rowdata[1]); - print "

\n"; -}//for + } while ($rowdata = $rs->fetch()); print "
".htmlspecialchars($rowdata[$col])."
$tid$title
\n"; }//if maxrows Index: /trunk/install/php/reserveprogram.php =================================================================== --- /trunk/install/php/reserveprogram.php (リビジョン 83) +++ /trunk/install/php/reserveprogram.php (リビジョン 94) @@ -53,12 +53,12 @@ //タイトル取得 - $query = "select title from foltia_program where tid='$tid'"; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { + $query = "select title from foltia_program where tid = ? "; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { die_exit("登録番組がありません
"); } - $rowdata = pg_fetch_row($rs, 0); + $title = htmlspecialchars($rowdata[0]); ?> @@ -100,22 +100,19 @@ FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid - AND foltia_program.tid ='$tid' + AND foltia_program.tid = ? ORDER BY stationrecch DESC "; - - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +// $rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($tid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { echo("放映局情報がまだはいってません
"); } else{ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); echo("\n"); }//endif @@ -182,15 +179,15 @@ FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid - AND foltia_subtitle.startdatetime >= '$now' AND foltia_program.tid ='$tid' + AND foltia_subtitle.startdatetime >= ? AND foltia_program.tid = ? ORDER BY foltia_subtitle.startdatetime ASC "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +// $rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($now,$tid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { echo("放映予定はありません
"); } else{ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); ?> @@ -210,9 +207,6 @@ \n"); - /* pg_fetch_row で一行取り出す */ - $rowdata = pg_fetch_row($rs, $row); - for ($col = 0; $col < $maxcols; $col++) { /* 列に対応 */ if ($col == 3){ @@ -223,5 +217,5 @@ } echo("\n"); - } + } while ($rowdata = $rs->fetch()); }//end if ?> Index: /trunk/install/php/foltialib.php =================================================================== --- /trunk/install/php/foltialib.php (リビジョン 89) +++ /trunk/install/php/foltialib.php (リビジョン 94) @@ -93,14 +93,5 @@ } } - - /* LIKE 用の文字列のエスケープ */ - function escape_like($sql, $quote = TRUE) { - return ($quote ? "'" : "") . - str_replace(array("\\\\", "%" , "_" ), - array("\\\\\\\\", "\\\\%", "\\\\_"), - pg_escape_string($sql)) . - ($quote ? "'" : ""); - } - + /* SQL 文字列のエスケープ */ function escape_string($sql, $quote = FALSE) { @@ -108,8 +99,18 @@ return "null"; } + if (preg_match("/^pgsql/", DSN)){ return ($quote ? "'" : "") . pg_escape_string($sql) . ($quote ? "'" : ""); - } + }else if (preg_match("/^sqlite/", DSN)){ + /* return ($quote ? "'" : "") . + sqlite_escape_string($sql) . + ($quote ? "'" : ""); + */ + return($sql); + }else{ + return "null"; + } + } /* SQL 数値のエスケープ */ @@ -124,41 +125,55 @@ } - /* PostgreSQL サーバに接続 */ + /* DBに接続 */ function m_connect() { -/* $con = @pg_connect("host=".DBHOST ." dbname=".DATABASE_NAME . - " user=".USER_NAME . - " password=".USER_PASSWORD); -*/ - $con = @pg_pconnect("host=".DBHOST ." dbname=".DATABASE_NAME . - " user=".USER_NAME . - " password=".USER_PASSWORD); - - - if (!$con) { - die_exit("データベースに接続出来ませんでした。"); + try { + $dbh = new PDO(DSN); + $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); + return($dbh); + } catch (PDOException $e) { + die_exit($e->getMessage() . ": データベースに接続出来ませんでした。"); } /* データベースと、PHP の内部文字コードが違う場合 */ - return($con); } /* データベースとの接続を切り離す */ - function m_close($con) { - return @pg_close($con); - } - - /* SQL 文を実行 */ - function m_query($con, $query, $errmessage) { - $rtn = @pg_query($con, $query); - if (!$rtn) { +function m_close($dbh) { + return null; + } + +//旧関数　sql_queryに置き換え +function m_query($dbh, $query, $errmessage) { + try { + $rtn = $dbh->query($query); + return($rtn); + } catch (PDOException $e) { /* エラーメッセージに SQL 文を出すのはセキュリティ上良くない！！ */ $msg = $errmessage . "
\n" . - @pg_last_error($con) . "
\n" . + $e->getMessage() . "
\n" . + var_export($e->errorInfo, true) . "
\n" . "" . htmlspecialchars($query) . "\n"; - $rtn = @pg_query($con, "rollback");//04.4.8 - m_close($con); +// $dbh->rollBack(); + $dbh = null; die_exit($msg); } + } +/* SQL 文を実行 */ +function sql_query($dbh, $query, $errmessage,$paramarray) { + try { + $rtn = $dbh->prepare("$query"); + $rtn->execute($paramarray); return($rtn); + } catch (PDOException $e) { + /* エラーメッセージに SQL 文を出すのはセキュリティ上良くない！！ */ + $msg = $errmessage . "
\n" . + $e->getMessage() . "
\n" . + var_export($e->errorInfo, true) . "
\n" . + "" . htmlspecialchars($query) . + "\n"; +// $dbh->rollBack(); + $dbh = null; + die_exit($msg); + } } @@ -166,7 +181,8 @@ function m_showtable($rs) { /* 検索件数 */ - $maxrows = pg_num_rows($rs); + $maxrows = 0; - if ($maxrows == 0) { + $rowdata = $rs->fetch(); + if (! $rowdata) { echo("

データが存在しません

\n"); return 0; @@ -174,5 +190,5 @@ /* フィールド数 */ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); ?>
@@ -183,5 +199,6 @@ for ($col = 1; $col < $maxcols; $col++) { /* pg_field_name() はフィールド名を返す */ - $f_name = htmlspecialchars(pg_field_name($rs, $col)); + $meta = $rs->getColumnMeta($col); + $f_name = htmlspecialchars($meta["name"]); echo("\n"); } @@ -192,8 +209,8 @@ \n"); - /* pg_fetch_row で一行取り出す */ - $rowdata = pg_fetch_row($rs, $row); /* １列目にリンクを張る */ echo(" @@ -212,8 +229,10 @@ } - /* 指定したコードのデータを表示 */ - function m_viewdata($con, $code) { - /* コードに該当するデータを検索 */ - $query = " + +function m_viewdata($dbh, $code) { + +/*これ使ってないよね? + + $query = " select p.code ,p.name @@ -222,27 +241,28 @@ ,s.name as job ,p.profile - ,to_char(p.editdate, 'YYYY/MM/DD HH24:MI:SS') as editdate + ,datetime(p.editdate) as editdate from inet_profile p left join inet_job s on p.job = s.code where p.code = $code"; - $rs = m_query($con, $query, "個人情報の取得に失敗しました。"); - if (pg_num_rows($rs) == 0) { + $rs = m_query($dbh, $query, "個人情報の取得に失敗しました。"); + $rowdata = $rs->fetch(); + if (! $rowdata) { echo("

データが存在しません

\n"); return FALSE; } - /* フィールド数 */ - $maxcols = pg_num_fields($rs); - /* 先頭行 */ - $rowdata = pg_fetch_row($rs, 0); + // フィールド数 + $maxcols = $rs->columnCount(); ?>
$f_name\n"); - } + } while ($rowdata = $rs->fetch()); ?>
- + getColumnMeta(1); ?> + - + getColumnMeta($col); ?> - + @@ -250,8 +270,8 @@
fetch(); + if (! $rowdata) { print("番組データがありません
"); }else{ @@ -301,8 +321,5 @@ //print "\n"; print "\n"; @@ -429,6 +446,8 @@ global $recfolderpath,$recfolderpath; - exec ( "df -h $recfolderpath | grep $recfolderpath", $hdfreearea); - $freearea = preg_split ("/[\s,]+/", $hdfreearea[0]); +// exec ( "df -h $recfolderpath | grep $recfolderpath", $hdfreearea); +// $freearea = preg_split ("/[\s,]+/", $hdfreearea[0]); + exec ( "df -hP $recfolderpath", $hdfreearea); + $freearea = preg_split ("/[\s,]+/", $hdfreearea[count($hdfreearea)-1]); return $freearea; @@ -557,18 +576,20 @@ "; $useraccount = m_query($con, $query, "DBクエリに失敗しました"); - $useraccountrows = pg_num_rows($useraccount); - - if ($useraccountrows == 1 ){ - $rowdata = pg_fetch_row($useraccount, 0); + $rowdata = $useraccount->fetch(); + if (! $rowdata) { + header("HTTP/1.0 401 Unauthorized"); + redirectlogin(); + } + $memberid = $rowdata[0]; $userclass = $rowdata[1]; $username = $rowdata[2]; $dbpasswd = $rowdata[3]; - }else{ + + $rowdata = $useraccount->fetch(); + if ($rowdata) { header("HTTP/1.0 401 Unauthorized"); - //print ""; redirectlogin(); - }//end if - + } // passwdをdbから取りだし @@ -627,13 +648,17 @@ "; $useraccount = m_query($con, $query, "DBクエリに失敗しました"); - $useraccountrows = pg_num_rows($useraccount); - - if ($useraccountrows == 1 ){ - $rowdata = pg_fetch_row($useraccount, 0); - //$userclass = $rowdata[1]; - return ($rowdata[1]); - }else{ - return (99);//エラー - }//end if + $rowdata = $useraccount->fetch(); + if (! $rowdata) { + return (99); + } + + $userclass = $rowdata[1]; + + $rowdata = $useraccount->fetch(); + if ($rowdata) { + return (99); + } + + return ($userclass); }else{ @@ -655,13 +680,17 @@ "; $useraccount = m_query($con, $query, "DBクエリに失敗しました"); - $useraccountrows = pg_num_rows($useraccount); - - if ($useraccountrows == 1 ){ - $rowdata = pg_fetch_row($useraccount, 0); - //$userclass = $rowdata[1]; - return ($rowdata[0]); - }else{ + $rowdata = $useraccount->fetch(); + if (! $rowdata) { return (-1);//エラー - }//end if + } + + $memberid = $rowdata[0]; + + $rowdata = $useraccount->fetch(); + if ($rowdata) { + return (-1); + } + + return ($memberid); }else{ @@ -682,18 +711,21 @@ "; $useraccount = m_query($con, $query, "DBクエリに失敗しました"); - $useraccountrows = pg_num_rows($useraccount); - - if ($useraccountrows == 1 ){ - $rowdata = pg_fetch_row($useraccount, 0); - return ($rowdata[2]); + $rowdata = $useraccount->fetch(); + if (! $rowdata) { + return ("");//エラー + } + + $name = $rowdata[2]; + + $rowdata = $useraccount->fetch(); + if ($rowdata) { + return (""); + } + + return ($name); + }else{ - return ("");//エラー + return (""); }//end if - -}else{ - return (""); -}//end if - - }//end function getmemberid2name Index: /trunk/install/php/selectcaptureimage.php =================================================================== --- /trunk/install/php/selectcaptureimage.php (リビジョン 85) +++ /trunk/install/php/selectcaptureimage.php (リビジョン 94) @@ -74,10 +74,11 @@ FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid - AND foltia_subtitle.pid = '$pid' + AND foltia_subtitle.pid = ? "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); -$rows = pg_num_rows($rs); -if ($rows == 0){ +// $rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($pid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { print "

キャプチャ画像

@@ -87,6 +88,4 @@ }else{ -$rowdata = pg_fetch_row($rs, 0); - print "

キャプチャ画像

Index: /trunk/install/php/viewepg.php =================================================================== --- /trunk/install/php/viewepg.php (リビジョン 91) +++ /trunk/install/php/viewepg.php (リビジョン 94) @@ -91,9 +91,8 @@ $maxdisplay = 8; - $query = "SELECT stationid, stationname, stationrecch, ontvcode FROM foltia_station WHERE \"ontvcode\" ~~ '%ontvjapan%' - "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - +$query = "SELECT count(*) FROM foltia_station WHERE \"ontvcode\" LIKE '%ontvjapan%'"; +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました"); +$maxrows = $rs->fetchColumn(0); if ($maxrows > $maxdisplay){ $pages = ceil($maxrows / $maxdisplay) ; @@ -138,13 +137,14 @@ $query = "SELECT stationid, stationname, stationrecch, ontvcode FROM foltia_station -WHERE \"ontvcode\" ~~ '%ontvjapan%' +WHERE \"ontvcode\" LIKE '%ontvjapan%' ORDER BY stationid ASC , stationrecch -OFFSET $offset LIMIT $maxdisplay +LIMIT ? OFFSET ? "; -$slistrs = m_query($con, $query, "DBクエリに失敗しました"); -$stations = pg_num_rows($slistrs); -for ($i=0 ; $i < $stations ; $i++){ - $rowdata = pg_fetch_row($slistrs, $i); - $stationhash[$i] = $rowdata[3] ; + +//$slistrs = m_query($con, $query, "DBクエリに失敗しました"); +$slistrs = sql_query($con, $query, "DBクエリに失敗しました",array($maxdisplay,$offset)); +while ($rowdata = $slistrs->fetch()) { + $stationhash[] = $rowdata[3]; + $snames[] = $rowdata[1]; // headder } @@ -158,34 +158,33 @@ SELECT ontvcode FROM foltia_station - WHERE \"ontvcode\" ~~ '%ontvjapan%' + WHERE \"ontvcode\" LIKE '%ontvjapan%' ORDER BY stationid ASC , stationrecch - OFFSET $offset LIMIT $maxdisplay + LIMIT ? OFFSET ? ) -AND startdatetime >= $start -AND startdatetime < $epgend +AND startdatetime >= ? +AND startdatetime < ? ORDER BY foltia_epg.startdatetime ASC "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($maxdisplay,$offset,$start,$epgend)); //print "$query
\n"; -$colmnums = pg_num_rows($rs); -if ($colmnums == 0){ +$rowdata = $rs->fetch(); +if (! $rowdata) { //番組データがない $colmnums = 2; }else{ - for ($i=0 ; $i < $colmnums ; $i++){ - $rowdata = pg_fetch_row($rs, $i); - $timetablehash["$rowdata[0]"] = ($i + 1); + $colmnums = 0; + do { + $colmnums++; + $timetablehash[$rowdata[0]] = $colmnums; // print "$rowdata[0]:$i+1
\n"; - } + } while ($rowdata = $rs->fetch()); } //print "colmnums $colmnums
\n"; //・局ごとに縦に配列入れていく -for ($j=0 ; $j < $stations ; $j++){ - $rowdata = pg_fetch_row($slistrs, $j); - $stationname = $rowdata[3]; - +foreach ($stationhash as $stationname) { $epgstart = $start ; $epgend = calcendtime($start , (8*60)); @@ -193,23 +192,18 @@ SELECT startdatetime , enddatetime , lengthmin , epgtitle , epgdesc , epgcategory ,ontvchannel ,epgid , epgcategory FROM foltia_epg -WHERE foltia_epg.ontvchannel = '$stationname' AND -enddatetime > $epgstart AND -startdatetime < $epgend +WHERE foltia_epg.ontvchannel = ? AND +enddatetime > ? AND +startdatetime < ? ORDER BY foltia_epg.startdatetime ASC "; - $statiodh = m_query($con, $query, "DBクエリに失敗しました"); - $maxrowsstation = pg_num_rows($statiodh); - -//print "maxrowsstation $maxrowsstation stationname $stationname
\n"; - -if ($maxrowsstation == 0) { + +// $statiodh = m_query($con, $query, "DBクエリに失敗しました"); + $statiodh = sql_query($con, $query, "DBクエリに失敗しました",array($stationname,$epgstart,$epgend)); + $stationrowdata = $statiodh->fetch(); + if (! $stationrowdata) { //print("番組データがありません
"); $item[0]["$stationname"] = ">番組データがありません"; }else{ - -for ($srow = 0; $srow < $maxrowsstation ; $srow++) { - -$stationrowdata = pg_fetch_row($statiodh, $srow); - + do { $printstarttime = substr($stationrowdata[0],8,2) . ":" . substr($stationrowdata[0],10,2); $tdclass = "t".substr($stationrowdata[0],8,2) . substr($stationrowdata[0],10,2); @@ -235,5 +229,5 @@ }//if -}//for + } while ($stationrowdata = $statiodh->fetch()); }//if @@ -291,13 +285,11 @@ //ヘッダ -for ($i=0;$i<$stations;$i++){ - $rowdata = pg_fetch_row($slistrs, $i); - print "".htmlspecialchars($rowdata[1])."" ; +foreach ($snames as $s) { + print "".htmlspecialchars($s)."" ; } //本体 for ($l = 0 ;$l < $colmnums; $l++){ print ""; - for ($m = 0 ; $m < $stations ; $m++ ){ - $stationname = $stationhash[$m]; + foreach ($stationhash as $stationname) { print_r($item[$l]["$stationname"]); } Index: /trunk/install/php/listreserve.php =================================================================== --- /trunk/install/php/listreserve.php (リビジョン 90) +++ /trunk/install/php/listreserve.php (リビジョン 94) @@ -62,32 +62,20 @@ $query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_subtitle.epgaddedby , + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_subtitle.epgaddedby, foltia_tvrecord.digital FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord WHERE 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 -AND foltia_subtitle.enddatetime >= '$now' +AND foltia_subtitle.enddatetime >= ? UNION SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_subtitle.epgaddedby , + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_subtitle.epgaddedby, foltia_tvrecord.digital FROM foltia_tvrecord @@ -96,10 +84,9 @@ LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) WHERE foltia_tvrecord.stationid = 0 AND -foltia_subtitle.enddatetime >= '$now' ORDER BY \"startdatetime\" ASC + foltia_subtitle.enddatetime >= ? ORDER BY x ASC "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($now,$now)); //チューナー数 @@ -122,11 +109,10 @@ fetch(); + if (! $rowdata) { print "番組データがありません
\n"; }else{ - - /* フィールド数 */ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); ?> @@ -149,8 +135,7 @@ \n"); - /* pg_fetch_row で一行取り出す */ - $rowdata = pg_fetch_row($rs, $row); + $pid = htmlspecialchars($rowdata[9]); @@ -168,32 +153,20 @@ $query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_tvrecord.digital + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_tvrecord.digital FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord WHERE 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 -AND foltia_subtitle.enddatetime > '$rowdata[5]' -AND foltia_subtitle.startdatetime < '$endtime' +AND foltia_subtitle.enddatetime > ? +AND foltia_subtitle.startdatetime < ? UNION SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_tvrecord.digital + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_tvrecord.digital FROM foltia_tvrecord LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) @@ -201,10 +174,12 @@ LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) WHERE foltia_tvrecord.stationid = 0 AND -foltia_subtitle.enddatetime > '$rowdata[5]' -AND foltia_subtitle.startdatetime < '$endtime' - "; +foltia_subtitle.enddatetime > ? +AND foltia_subtitle.startdatetime < ? +"; $rclass = ""; - $overlap = m_query($con, $query, "DBクエリに失敗しました"); - $overlapmaxrows = pg_num_rows($overlap); +// $overlap = m_query($con, $query, "DBクエリに失敗しました"); + $overlap = sql_query($con, $query, "DBクエリに失敗しました",array($rowdata[5],$endtime,$rowdata[5],$endtime)); + $owrowall = $overlap->fetchAll(); + $overlapmaxrows = count($owrowall); if ($overlapmaxrows > ($recunits) ){ @@ -212,5 +187,5 @@ for ($rrow = 0; $rrow < $overlapmaxrows ; $rrow++) { - $owrowdata = pg_fetch_array($overlap, $rrow); + $owrowdata = $owrowall[$rrow]; $owtimeline[ $owrowdata['startdatetime'] ] = $owtimeline[ $owrowdata['startdatetime'] ] +1; @@ -237,33 +212,21 @@ $query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_tvrecord.digital + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_tvrecord.digital FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord WHERE 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 -AND foltia_subtitle.enddatetime > '$rowdata[5]' -AND foltia_subtitle.startdatetime < '$endtime' +AND foltia_subtitle.enddatetime > ? +AND foltia_subtitle.startdatetime < ? AND (foltia_station.stationrecch = '0' OR foltia_station.stationrecch = '-1' ) UNION SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid , -foltia_tvrecord.digital + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.startoffset, + foltia_subtitle.pid, foltia_tvrecord.digital FROM foltia_tvrecord LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) @@ -271,11 +234,12 @@ LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) WHERE foltia_tvrecord.stationid = 0 AND -foltia_subtitle.enddatetime > '$rowdata[5]' -AND foltia_subtitle.startdatetime < '$endtime' +foltia_subtitle.enddatetime > ? +AND foltia_subtitle.startdatetime < ? AND (foltia_station.stationrecch = '0' OR foltia_station.stationrecch = '-1' ) - "; - $eoverlap = m_query($con, $query, "DBクエリに失敗しました"); - $eoverlapmaxrows = pg_num_rows($eoverlap); +// $eoverlap = m_query($con, $query, "DBクエリに失敗しました"); + $eoverlap = sql_query($con, $query, "DBクエリに失敗しました",array($rowdata[5], $endtime,$rowdata[5], $endtime)); + $eowrowall = $eoverlap->fetchAll(); + $eoverlapmaxrows = count($eowrowall); if ($eoverlapmaxrows > ($externalinputs) ){ @@ -283,5 +247,5 @@ for ($erow = 0; $erow < $eoverlapmaxrows ; $erow++) { - $eowrowdata = pg_fetch_array($eoverlap, $erow); + $eowrowdata = $eowrowall[$erow]; $eowtimeline[ $eowrowdata['startdatetime'] ] = $eowtimeline[ $eowrowdata['startdatetime'] ] +1; @@ -359,5 +323,5 @@ echo("
\n"); echo("\n"); - } + } while ($rowdata = $rs->fetch()); ?> @@ -374,14 +338,9 @@ fetch(); +if (! $rowdata) { //なければなにもしない }else{ - $maxcols = pg_num_fields($rs); + $maxcols = $rs->columnCount(); ?> @@ -417,8 +376,5 @@ "); }//if tid 0 - }//for + } while ($rowdata = $rs->fetch()); }//else ?> Index: /trunk/install/php/index.php =================================================================== --- /trunk/install/php/index.php (リビジョン 83) +++ /trunk/install/php/index.php (リビジョン 94) @@ -67,34 +67,29 @@ WHERE foltia_tvrecord.tid = foltia_program.tid AND foltia_program.tid = foltia_subtitle.tid -AND foltia_subtitle.enddatetime >= '$now' +AND foltia_subtitle.enddatetime >= ? ORDER BY \"startdatetime\" ASC LIMIT 1000 "; - $reservedrssametid = m_query($con, $query, "DBクエリに失敗しました"); - $reservedmaxrowssameid = pg_num_rows($reservedrssametid); - - if ($reservedmaxrowssameid > 0 ){ - for ($rrow = 0; $rrow < $reservedmaxrowssameid ; $rrow++) { - $rowdata = pg_fetch_row($reservedrssametid, $rrow); +// $reservedrssametid = m_query($con, $query, "DBクエリに失敗しました"); +$reservedrssametid = sql_query($con, $query, "DBクエリに失敗しました",array($now)); +$rowdata = $reservedrssametid->fetch(); +if ($rowdata) { + do { $reservedpidsametid[] = $rowdata[7]; - } + } while ($rowdata = $reservedrssametid->fetch()); + $rowdata = ""; - $rrow = ""; }else{ - $reservedpidsametid = "" ; + $reservedpidsametid = array(); }//end if +$reservedrssametid->closeCursor(); //録画番組検索 $query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.pid + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.pid FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord WHERE 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 @@ -102,13 +97,8 @@ UNION SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.pid + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.pid FROM foltia_tvrecord LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) @@ -116,18 +106,41 @@ LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) WHERE foltia_tvrecord.stationid = 0 AND -foltia_subtitle.enddatetime >= '$now' ORDER BY \"startdatetime\" ASC -LIMIT 1000 - "; - - $reservedrs = m_query($con, $query, "DBクエリに失敗しました"); - $reservedmaxrows = pg_num_rows($reservedrs); - - if ($reservedmaxrows > 0 ){ - for ($rrow = 0; $rrow < $reservedmaxrows ; $rrow++) { - $rowdata = pg_fetch_row($reservedrs, $rrow); + foltia_subtitle.enddatetime >= '$now' ORDER BY x ASC +LIMIT 1000 + "; + +//$reservedrs = m_query($con, $query, "DBクエリに失敗しました"); +$query = " +SELECT + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.pid +FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord +WHERE 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 +AND foltia_subtitle.enddatetime >= ? +UNION +SELECT + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_tvrecord.bitrate, foltia_subtitle.pid +FROM foltia_tvrecord +LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) +LEFT OUTER JOIN foltia_program on (foltia_tvrecord.tid = foltia_program.tid ) +LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) +WHERE foltia_tvrecord.stationid = 0 AND + foltia_subtitle.enddatetime >= ? ORDER BY x ASC +LIMIT 1000 + "; +$reservedrs = sql_query($con, $query, "DBクエリに失敗しました",array($now,$now)); + +$rowdata = $reservedrs->fetch(); +if ($rowdata) { + do { $reservedpid[] = $rowdata[8]; - } + } while ($rowdata = $reservedrs->fetch()); }else{ - $reservedpid = ""; + $reservedpid = array(); }//end if @@ -138,13 +151,8 @@ $query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_subtitle.pid , -foltia_subtitle.startoffset + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_subtitle.pid, foltia_subtitle.startoffset FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid @@ -153,20 +161,23 @@ LIMIT 1000 "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - +$query = " + SELECT + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_subtitle.pid, foltia_subtitle.startoffset +FROM foltia_subtitle , foltia_program ,foltia_station +WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid + AND foltia_subtitle.enddatetime >= ? AND foltia_subtitle.countno = '1' +ORDER BY foltia_subtitle.startdatetime ASC +LIMIT 1000 + "; }else{ - $query = " +$query = " SELECT -foltia_program .tid, -stationname, -foltia_program .title, -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_subtitle.pid , -foltia_subtitle.startoffset + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_subtitle.pid, foltia_subtitle.startoffset FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid @@ -175,10 +186,22 @@ LIMIT 1000 "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - +$query = " + SELECT + foltia_program.tid, stationname, foltia_program.title, + foltia_subtitle.countno, foltia_subtitle.subtitle, + foltia_subtitle.startdatetime, foltia_subtitle.lengthmin, + foltia_subtitle.pid, foltia_subtitle.startoffset +FROM foltia_subtitle , foltia_program ,foltia_station +WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid + AND foltia_subtitle.enddatetime >= ? +ORDER BY foltia_subtitle.startdatetime ASC +LIMIT 1000 + "; }//end if -if ($maxrows == 0) { +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($now)); +$rowdata = $rs->fetch(); +if (! $rowdata) { header("Status: 404 Not Found",TRUE,404); printtitle(); @@ -211,5 +234,5 @@ columnCount(); ?>
@@ -230,8 +253,5 @@ \n"); - } + } while ($rowdata = $rs->fetch()); ?> Index: /trunk/install/php/StarlightBreaker/sb-edit.php =================================================================== --- /trunk/install/php/StarlightBreaker/sb-edit.php (リビジョン 88) +++ /trunk/install/php/StarlightBreaker/sb-edit.php (リビジョン 94) @@ -73,8 +73,8 @@ FROM foltia_subtitle , foltia_program ,foltia_station WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_station.stationid = foltia_subtitle.stationid - AND foltia_subtitle.pid = '$pid' - - "; - $rs = m_query($con, $query, "DBクエリに失敗しました"); +AND foltia_subtitle.pid = ? +"; +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($pid)); $rows = pg_num_rows($rs); if ($rows == 0){ Index: /trunk/install/php/reserveepg.php =================================================================== --- /trunk/install/php/reserveepg.php (リビジョン 70) +++ /trunk/install/php/reserveepg.php (リビジョン 94) @@ -56,13 +56,13 @@ stationname , stationrecch ,stationid FROM foltia_epg , foltia_station - WHERE epgid='$epgid' AND foltia_station.ontvcode = foltia_epg.ontvchannel + WHERE epgid = ? AND foltia_station.ontvcode = foltia_epg.ontvchannel ";//4812 - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - - if ($maxrows == 0) { +// $rs = m_query($con, $query, "DBクエリに失敗しました"); + $rs = sql_query($con, $query, "DBクエリに失敗しました",array($epgid)); +$rowdata = $rs->fetch(); +if (! $rowdata) { die_exit("登録番組がありません
"); } - $rowdata = pg_fetch_row($rs, 0); + //$title = htmlspecialchars($rowdata[0]); ?> @@ -136,33 +136,21 @@ $query = " SELECT -foltia_program .title, -foltia_program .tid, -stationname, -foltia_station.stationid , -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid + foltia_program.title, foltia_program.tid, stationname, + foltia_station.stationid, foltia_subtitle.countno, + foltia_subtitle.subtitle, foltia_subtitle.startdatetime, + foltia_subtitle.lengthmin, foltia_tvrecord.bitrate, + foltia_subtitle.startoffset, foltia_subtitle.pid FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord WHERE 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 -AND foltia_subtitle.startdatetime ='$startfoltime' -AND foltia_subtitle.enddatetime = '$endfoltime' -AND foltia_station.stationid = '$stationid' +AND foltia_subtitle.startdatetime = ? +AND foltia_subtitle.enddatetime = ? +AND foltia_station.stationid = ? UNION SELECT -foltia_program .title, -foltia_program .tid, -stationname, -foltia_station.stationid , -foltia_subtitle.countno, -foltia_subtitle.subtitle, -foltia_subtitle.startdatetime , -foltia_subtitle.lengthmin , -foltia_tvrecord.bitrate , -foltia_subtitle.startoffset , -foltia_subtitle.pid + foltia_program.title, foltia_program.tid, stationname, + foltia_station.stationid, foltia_subtitle.countno, + foltia_subtitle.subtitle, foltia_subtitle.startdatetime, + foltia_subtitle.lengthmin, foltia_tvrecord.bitrate, + foltia_subtitle.startoffset, foltia_subtitle.pid FROM foltia_tvrecord LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid ) @@ -170,23 +158,20 @@ LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid ) WHERE foltia_tvrecord.stationid = 0 -AND foltia_subtitle.startdatetime ='$startfoltime' -AND foltia_subtitle.enddatetime = '$endfoltime' -AND foltia_station.stationid = '$stationid' +AND foltia_subtitle.startdatetime = ? +AND foltia_subtitle.enddatetime = ? +AND foltia_station.stationid = ? "; - - - $rs = m_query($con, $query, "DBクエリに失敗しました"); - $maxrows = pg_num_rows($rs); - //print ""; print "\n"; - if ($maxrows == 0) { +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました",array($startfoltime,$endfoltime,$stationid,$startfoltime,$endfoltime,$stationid)); +$chkoverwrap = $rs->fetch(); +if (! $chkoverwrap) { //重複なし print "
\n"; }else{ - $chkoverwrap = pg_fetch_row($rs, 0); $prereservedtitle = htmlspecialchars($chkoverwrap[0]); $tid = htmlspecialchars($chkoverwrap[1]); Index: /trunk/install/php/showlib.php =================================================================== --- /trunk/install/php/showlib.php (リビジョン 83) +++ /trunk/install/php/showlib.php (リビジョン 94) @@ -84,9 +84,8 @@ "; -$rs = m_query($con, $query, "DBクエリに失敗しました"); - -$maxrows = pg_num_rows($rs); - -if ($maxrows > 0 ){ +//$rs = m_query($con, $query, "DBクエリに失敗しました"); +$rs = sql_query($con, $query, "DBクエリに失敗しました"); +$rowdata = $rs->fetch(); +if ($rowdata) { if(ereg("iPhone",$useragent)){ print "