チェンジセット 111: trunk/install/perl/db/Pg.pl

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

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

ファイル:

凡例:

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

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

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