チェンジセット 94: trunk/install/perl/getxml2db.pl
- コミット日時:
- 2010/02/28 22:28:26 (14 年前)
- ファイル:
凡例:
- 変更無し
- 追加
- 削除
- 更新
- コピー
- 移動
trunk/install/perl/getxml2db.pl
r82 r94 19 19 use DBI; 20 20 use DBD::Pg; 21 use DBD::SQLite; 21 22 use Digest::MD5 qw(md5_hex); 22 23 23 24 $path = $0; 24 25 $path =~ s/getxml2db.pl$//i; 25 if ($p wdne "./"){26 if ($path ne "./"){ 26 27 push( @INC, "$path"); 27 28 } … … 48 49 } 49 50 51 $dbh = DBI->connect($DSN,$DBUser,$DBPass) ||die $DBI::error;; 50 52 51 my $data_source = sprintf("dbi:%s:dbname=%s;host=%s;port=%d", 52 $DBDriv,$DBName,$DBHost,$DBPort); 53 54 $dbh = DBI->connect($data_source,$DBUser,$DBPass) ||die $DBI::error;; 55 56 #$dbh->{AutoCommit} = 0; 53 $dbh->{AutoCommit} = 0; 57 54 58 55 # If-Modified-Since使うように変更#2008/11/14 … … 105 102 $programtitlename =~ s/\>\;/>/gi; 106 103 $programtitlename =~ s/\&\;/\&/gi; 107 $programtitle = $dbh->quote($programtitlename); 104 # $programtitle = $dbh->quote($programtitlename); 105 $programtitle = $programtitlename; 108 106 109 107 Jcode::convert(\$item{ChName},'euc'); … … 115 113 $programSubTitle =~ s/\>\;/>/gi; 116 114 $programSubTitle =~ s/\&\;/\&/gi; 117 $programSubTitle = $dbh->quote($programSubTitle);115 # $programSubTitle = $dbh->quote($programSubTitle); 118 116 119 117 $offsetmin = $item{StOffset}/60; … … 127 125 #サブタイトル追加------------------------------------------------- 128 126 #番組があるか確認 129 $DBQuery = "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'"; 130 $sth = $dbh->prepare($DBQuery); 131 $sth->execute(); 127 $sth = $dbh->prepare($stmt{'getxml2db.1'}); 128 $sth->execute($item{TID}); 132 129 @titlecount= $sth->fetchrow_array; 133 130 … … 137 134 #200412012359 138 135 $nomalstarttime = substr($sttime,8,4); 139 $DBQuery = "insert into foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')";140 # $sth = $dbh->prepare($DBQuery);141 # $sth->execute();142 $oserr = $dbh->do($DBQuery);143 &writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle");144 136 145 137 $sth = $dbh->prepare($stmt{'getxml2db.2'}); 138 $oserr = $sth->execute($item{TID}, $programtitle, '', $nomalstarttime, $length, '', '', 3, 1, '', ''); 139 &writelog("getxml2db ADD TV Progtam:$item{TID}:$programtitle"); 146 140 }else{ 147 141 #2006/2/26 148 142 #あったら、タイトル確認して 149 $DBQuery = "SELECT title FROM foltia_program WHERE tid = '$item{TID}'"; 150 $sth = $dbh->prepare($DBQuery); 151 $sth->execute(); 143 $sth = $dbh->prepare($stmt{'getxml2db.3'}); 144 $sth->execute($item{TID}); 152 145 @titlearray = $sth->fetchrow_array; 153 146 #更新などされてたらupdate 154 147 #print "$titlearray[0] / $programtitle\n"; 155 148 if ($titlearray[0] ne "$programtitlename" ){ 156 $DBQuery = "UPDATE foltia_program SET title = $programtitle where tid = '$item{TID}' "; 157 # $sth = $dbh->prepare($DBQuery); 158 # $sth->execute(); 159 $oserr = $dbh->do($DBQuery); 149 $sth = $dbh->prepare($stmt{'getxml2db.4'}); 150 $oserr = $sth->execute($programtitle, $item{TID}); 160 151 &writelog("getxml2db UPDATE TV Progtam:$item{TID}:$programtitle"); 161 152 }#end if update … … 164 155 165 156 #PIDがあるか確認 166 $DBQuery = "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; 167 $sth = $dbh->prepare($DBQuery); 168 $sth->execute(); 157 $sth = $dbh->prepare($stmt{'getxml2db.5'}); 158 $sth->execute($item{'TID'}, $item{'PID'}); 169 159 @subticount= $sth->fetchrow_array; 170 160 if ($subticount[0] >= 1){ … … 174 164 #UPDATE foltia_subtitle SET stationid = '42',countno = '8',subtitle = '京都行きます' ,startdatetime = '200503010035' ,enddatetime = '200503010050',startoffset = '0' ,lengthmin = '15' WHERE tid = '550' AND pid = '26000' 175 165 if ($item{Count} == ""){ 176 177 $DBQuery = "UPDATE foltia_subtitle SET 178 stationid = '$stationid', 179 countno = null, 180 subtitle = $programSubTitle , 181 startdatetime = '$recstartdate' , 182 enddatetime = '$recenddate', 183 startoffset = '$offsetmin' , 184 lengthmin = '$length' 185 WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; 186 166 $sth = $dbh->prepare($stmt{'getxml2db.6'}); 167 $oserr = $sth->execute($stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $item{'PID'}); 187 168 }else{ 188 189 $DBQuery = "UPDATE foltia_subtitle SET 190 stationid = '$stationid', 191 countno = '$item{Count}', 192 subtitle = $programSubTitle , 193 startdatetime = '$recstartdate' , 194 enddatetime = '$recenddate', 195 startoffset = '$offsetmin' , 196 lengthmin = '$length' 197 WHERE tid = '$item{TID}' AND pid = '$item{PID}' "; 198 } 199 # $sth = $dbh->prepare($DBQuery); 200 # $sth->execute(); 201 $oserr = $dbh->do($DBQuery); 202 169 $sth = $dbh->prepare($stmt{'getxml2db.7'}); 170 $oserr = $sth->execute($stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length, $item{'TID'}, $item{'PID'}); 171 } 203 172 }else{ 204 173 #なければ追加 … … 207 176 #そのままキューに入る形で 208 177 if ($item{Count} eq ""){ 209 $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; 178 $sth = $dbh->prepare($stmt{'getxml2db.8'}); 179 $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, undef, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); 210 180 }else{ 211 $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')"; 181 $sth = $dbh->prepare($stmt{'getxml2db.9'}); 182 $oserr = $sth->execute($item{'PID'}, $item{'TID'}, $stationid, $item{'Count'}, $programSubTitle, $recstartdate, $recenddate, $offsetmin, $length); 212 183 } 213 # $sth = $dbh->prepare($DBQuery);214 # $sth->execute();215 $oserr = $dbh->do($DBQuery);216 217 184 } 218 219 185 220 186 #print "$DBQuery\n\n\n"; … … 226 192 }#foreach 227 193 228 #$oserr = $dbh->commit;194 $oserr = $dbh->commit; 229 195 ## $dbh->disconnect(); 230 231
