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

リビジョン 113, 8.7 kB (コミッタ: sorshi, コミット時期: 14 年 前)

PostgreSQLでEPG周りが動いていなかった部分を対応

Line 
1
2 %main::stmt = (
3 'addatq.1' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ?",
4 'addatq.2' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ? AND stationid = ?",
5 'addatq.3' => "SELECT count(*) FROM foltia_tvrecord WHERE tid = ? AND stationid = '0'",
6 'addatq.addcue.1' => "SELECT * FROM foltia_tvrecord WHERE tid = ?",
7 'addatq.addcue.2' => "SELECT * FROM foltia_tvrecord WHERE tid = ? AND stationid = ?",
8 'addatq.addcue.3' => "SELECT * from foltia_subtitle WHERE tid = ? AND startdatetime > ? AND startdatetime < ?",
9 'addatq.addcue.4' => "SELECT stationid , stationrecch FROM foltia_station where stationid = ?",
10 'addatq.addcue.5' => "SELECT * from foltia_subtitle WHERE tid = ? AND stationid = ? AND startdatetime > ? AND startdatetime < ?",
11 'addatq.addcue.6' => "SELECT stationid , stationrecch FROM foltia_station where stationid = ?",
12
13 'addpidatq.1' => "SELECT count(*) FROM foltia_subtitle WHERE pid = ?",
14 'addpidatq.2' => "SELECT bitrate,digital FROM foltia_tvrecord , foltia_subtitle WHERE foltia_tvrecord.tid = foltia_subtitle.tid AND pid = ?",
15 '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",
16 'addpidatq.4' => "SELECT * FROM foltia_subtitle WHERE pid = ?",
17
18 '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 = ?",
19
20 'envpolicyupdate.1' => "SELECT userclass,name,passwd1 FROM foltia_envpolicy",
21
22 'foltialib.getstationid.1' => "SELECT count(*) FROM foltia_station WHERE stationname = ?",
23 'foltialib.getstationid.2' => "SELECT stationid,stationname FROM foltia_station WHERE stationname = ?",
24 'foltialib.getstationid.3' => "SELECT max(stationid) FROM foltia_station",
25 'foltialib.getstationid.4' => "INSERT INTO foltia_station (stationid, stationname, stationrecch) VALUES (?, ?, ?)",
26 'foltialib.getpidbympegfilename.1' => "SELECT pid FROM foltia_subtitle WHERE m2pfilename = ? LIMIT 1",
27 'foltialib.changefilestatus.1' => "UPDATE foltia_subtitle SET filestatus = ?, lastupdate = now() WHERE pid = ?",
28 'foltialib.getfilestatus.1' => "SELECT filestatus FROM foltia_subtitle WHERE pid = ?",
29 'foltialib.pid2sid.1' => "SELECT stationid FROM foltia_subtitle WHERE pid = ?",
30
31 'getxml2db.1' => "SELECT count(*) FROM foltia_program WHERE tid = ?",
32 'getxml2db.2' => "INSERT into foltia_program VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,  ?, ?)",
33 'getxml2db.3' => "SELECT title FROM foltia_program WHERE tid = ?",
34 'getxml2db.4' => "UPDATE foltia_program SET title = ? where tid = ?",
35 'getxml2db.5' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND pid = ?",
36 'getxml2db.6' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?",
37 'getxml2db.7' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?",
38 'getxml2db.8' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
39 'getxml2db.9' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
40
41 '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",
42 'ipodtranscode.2' => "SELECT title, countno, subtitle FROM foltia_program, foltia_subtitle WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_subtitle.pid = ?",
43 'ipodtranscode.updatemp4file.1' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
44 'ipodtranscode.updatemp4file.2' => "INSERT INTO foltia_mp4files VALUES (?, ?)",
45
46 '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",
47
48 'mklocalizeddir.1' => "SELECT title FROM foltia_program where tid = ?",
49
50 'recwrap.1' => "UPDATE foltia_subtitle SET m2pfilename = ? WHERE pid = ?",
51 'recwrap.2' => "INSERT into foltia_m2pfiles VALUES (?)",
52 'recwrap.3' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?",
53 'recwrap.4' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?",
54 'recwrap.5' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
55 'recwrap.6' => "INSERT into foltia_mp4files VALUES (?, ?)",
56 '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",
57 'recwrap.8' => "SELECT stationid,stationname,stationrecch,tunertype FROM foltia_station WHERE stationid = ? ",
58
59 'schedulecheck.1' => "SELECT count(*) FROM foltia_tvrecord",
60 'schedulecheck.2' => "SELECT tid ,stationid FROM foltia_tvrecord",
61
62 'singletranscode.1' => "SELECT pid FROM foltia_subtitle WHERE m2pfilename = ?",
63 'singletranscode.2' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND countno = ?",
64 'singletranscode.3' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ?",
65 'singletranscode.4' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?",
66 'singletranscode.5' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?",
67 'singletranscode.6' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
68 'singletranscode.7' => "INSERT into foltia_mp4files values (?, ?)",
69
70 'updatem2pfiletable.1' => "DELETE FROM foltia_m2pfiles",
71 'updatem2pfiletable.2' => "INSERT into foltia_m2pfiles values (?)",
72 'updatem2pfiletable.3' => "DELETE FROM foltia_mp4files",
73 'updatem2pfiletable.4' => "INSERT into foltia_mp4files values (?, ?)",
74
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 ( nextval('foltia_epg_epgid_seq'), ?, ?, ?, ?, ?, ?, ?)" ,
78 'epgimport.1' => "SELECT count(*) FROM foltia_station WHERE stationid = ?" ,
79 'epgimport.2' => "SELECT digitalch,ontvcode FROM foltia_station WHERE stationid = ?" ,
80 'epgimport.3' => "SELECT digitalch,ontvcode FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 13 AND digitalch <= 62 ORDER BY digitalch ASC" ,
81 'epgimport.4' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 100 AND digitalch <= 222 ORDER BY digitalch ASC" ,
82 'epgimport.5' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 223 ORDER BY digitalch ASC" ,
83 'epgimport.6' => "SELECT
84  foltia_program.tid, stationname, foltia_program.title,
85  foltia_subtitle.countno, foltia_subtitle.subtitle,
86  foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin,
87  foltia_tvrecord.bitrate, foltia_subtitle.startoffset,
88  foltia_subtitle.pid, foltia_subtitle.epgaddedby,
89 foltia_tvrecord.digital
90 FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord
91 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
92 AND foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ?
93 UNION
94 SELECT
95  foltia_program.tid, stationname, foltia_program.title,
96  foltia_subtitle.countno, foltia_subtitle.subtitle,
97  foltia_subtitle.startdatetime, foltia_subtitle.lengthmin,
98  foltia_tvrecord.bitrate,  foltia_subtitle.startoffset,
99  foltia_subtitle.pid,  foltia_subtitle.epgaddedby,
100 foltia_tvrecord.digital
101 FROM foltia_tvrecord
102 LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid )
103 LEFT OUTER JOIN foltia_program on (foltia_tvrecord.tid = foltia_program.tid )
104 LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid )
105 WHERE foltia_tvrecord.stationid = 0 AND
106  foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ? " ,
107
108 );
109
110 1;
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。
track feed