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

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

チェンジセット130での更新がSQL直書きだったのを修正

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 'foltialib.mp4filename2tid.1' => "SELECT tid FROM foltia_subtitle WHERE PSPfilename = ?",
31
32 'getxml2db.1' => "SELECT count(*) FROM foltia_program WHERE tid = ?",
33 'getxml2db.2' => "INSERT into foltia_program VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?,  ?, ?)",
34 'getxml2db.3' => "SELECT title FROM foltia_program WHERE tid = ?",
35 'getxml2db.4' => "UPDATE foltia_program SET title = ? where tid = ?",
36 'getxml2db.5' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND pid = ?",
37 'getxml2db.6' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?",
38 'getxml2db.7' => "UPDATE foltia_subtitle SET stationid = ?, countno = ?, subtitle = ?, startdatetime = ?, enddatetime = ?, startoffset = ?, lengthmin = ? WHERE tid = ? AND pid = ?",
39 'getxml2db.8' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
40 'getxml2db.9' => "INSERT into foltia_subtitle (pid, tid, stationid, countno, subtitle, startdatetime, enddatetime, startoffset, lengthmin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
41
42 '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",
43 'ipodtranscode.2' => "SELECT title, countno, subtitle FROM foltia_program, foltia_subtitle WHERE foltia_program.tid = foltia_subtitle.tid AND foltia_subtitle.pid = ?",
44 'ipodtranscode.updatemp4file.1' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
45 'ipodtranscode.updatemp4file.2' => "INSERT INTO foltia_mp4files VALUES (?, ?)",
46
47 '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",
48
49 'mklocalizeddir.1' => "SELECT title FROM foltia_program where tid = ?",
50
51 'recwrap.1' => "UPDATE foltia_subtitle SET m2pfilename = ? WHERE pid = ?",
52 'recwrap.2' => "INSERT into foltia_m2pfiles VALUES (?)",
53 'recwrap.3' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?",
54 'recwrap.4' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?",
55 'recwrap.5' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
56 'recwrap.6' => "INSERT into foltia_mp4files VALUES (?, ?)",
57 '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",
58 'recwrap.8' => "SELECT stationid,stationname,stationrecch,tunertype FROM foltia_station WHERE stationid = ? ",
59
60 'schedulecheck.1' => "SELECT count(*) FROM foltia_tvrecord",
61 'schedulecheck.2' => "SELECT tid ,stationid FROM foltia_tvrecord",
62
63 'singletranscode.1' => "SELECT pid FROM foltia_subtitle WHERE m2pfilename = ?",
64 'singletranscode.2' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ? AND countno = ?",
65 'singletranscode.3' => "SELECT count(*) FROM foltia_subtitle WHERE tid = ?",
66 'singletranscode.4' => "SELECT psp,aspect,title FROM foltia_program WHERE tid = ?",
67 'singletranscode.5' => "SELECT subtitle FROM foltia_subtitle WHERE tid = ? AND countno = ?",
68 'singletranscode.6' => "UPDATE foltia_subtitle SET PSPfilename = ? WHERE pid = ?",
69 'singletranscode.7' => "INSERT into foltia_mp4files values (?, ?)",
70
71 'updatem2pfiletable.1' => "DELETE FROM foltia_m2pfiles",
72 'updatem2pfiletable.2' => "INSERT into foltia_m2pfiles values (?)",
73 'updatem2pfiletable.3' => "DELETE FROM foltia_mp4files",
74 'updatem2pfiletable.4' => "INSERT into foltia_mp4files values (?, ?)",
75
76 'xmltv2foltia.replaceepg.1' => "SELECT * FROM foltia_epg WHERE enddatetime > ? AND startdatetime < ? AND ontvchannel = ?",
77 'xmltv2foltia.replaceepg.2' => "SELECT * FROM foltia_epg WHERE startdatetime = ? AND enddatetime = ? AND ontvchannel = ? ",
78 'xmltv2foltia.commitdb.1' => "DELETE FROM foltia_epg WHERE epgid = ?",
79 'xmltv2foltia.commitdb.2' => "INSERT INTO foltia_epg VALUES ( nextval('foltia_epg_epgid_seq'), ?, ?, ?, ?, ?, ?, ?)" ,
80 'epgimport.1' => "SELECT count(*) FROM foltia_station WHERE stationid = ?" ,
81 'epgimport.2' => "SELECT digitalch,ontvcode FROM foltia_station WHERE stationid = ?" ,
82 'epgimport.3' => "SELECT digitalch,ontvcode FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 13 AND digitalch <= 62 ORDER BY digitalch ASC" ,
83 'epgimport.4' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 100 AND digitalch <= 222" ,
84 'epgimport.5' => "SELECT count(*) FROM foltia_station WHERE ontvcode is not NULL AND digitalch >= 223" ,
85 'epgimport.6' => "SELECT
86  foltia_program.tid, stationname, foltia_program.title,
87  foltia_subtitle.countno, foltia_subtitle.subtitle,
88  foltia_subtitle.startdatetime as x, foltia_subtitle.lengthmin,
89  foltia_tvrecord.bitrate, foltia_subtitle.startoffset,
90  foltia_subtitle.pid, foltia_subtitle.epgaddedby,
91 foltia_tvrecord.digital
92 FROM foltia_subtitle , foltia_program ,foltia_station ,foltia_tvrecord
93 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
94 AND foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ?
95 UNION
96 SELECT
97  foltia_program.tid, stationname, foltia_program.title,
98  foltia_subtitle.countno, foltia_subtitle.subtitle,
99  foltia_subtitle.startdatetime, foltia_subtitle.lengthmin,
100  foltia_tvrecord.bitrate,  foltia_subtitle.startoffset,
101  foltia_subtitle.pid,  foltia_subtitle.epgaddedby,
102 foltia_tvrecord.digital
103 FROM foltia_tvrecord
104 LEFT OUTER JOIN foltia_subtitle on (foltia_tvrecord.tid = foltia_subtitle.tid )
105 LEFT OUTER JOIN foltia_program on (foltia_tvrecord.tid = foltia_program.tid )
106 LEFT OUTER JOIN foltia_station on (foltia_subtitle.stationid = foltia_station.stationid )
107 WHERE foltia_tvrecord.stationid = 0 AND
108  foltia_subtitle.enddatetime >= ? AND foltia_subtitle.startdatetime < ? " ,
109
110 );
111
112 1;
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。
track feed