root/trunk/install/perl/getxml2db.pl

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

エスケープもれの修正。

  • svn:executable 属性の設定値:
Line 
1 #!/usr/bin/perl
2 #
3 # Anime recording system foltia
4 # http://www.dcc-jpl.com/soft/foltia/
5 #
6 # usage :getxml2db [long]
7 #しょぼいカレンダー<http://cal.syoboi.jp/>から番組データXMLを取得しfoltia DBにインポートする
8 #
9 #オプション
10 #long:2週間分取り込む。このモードで一日一回回せばよいでしょう。
11 #
12 # DCC-JPL Japan/foltia project
13 #
14 #
15
16 use LWP::Simple;
17 use Jcode;
18 use Time::Local;
19 use DBI;
20 use DBD::Pg;
21
22 $path = $0;
23 $path =~ s/getxml2db.pl$//i;
24 if ($pwd  ne "./"){
25 push( @INC, "$path");
26 }
27 require "foltialib.pl";
28
29
30 $processes =  &processfind("getxml2db.pl");
31 if ($processes > 1 ){
32 #print "process  found:$processes\n";
33 &writelog("getxml2db   processes exist. exit:");
34 exit;
35 }else{
36 #print "process NOT found:$processes\n";
37 &writelog("getxml2db  Normal launch.");
38 }
39
40 if ($ARGV[0]  eq "long"){
41         $uri="http://cal.syoboi.jp/cal_chk.php";
42         &writelog("getxml2db  use long mode.");
43 }else{
44         $uri="http://cal.syoboi.jp/cal_chk.xml";
45 }
46
47
48         my $data_source = sprintf("dbi:%s:dbname=%s;host=%s;port=%d",
49                 $DBDriv,$DBName,$DBHost,$DBPort);
50
51          $dbh = DBI->connect($data_source,$DBUser,$DBPass) ||die $DBI::error;;
52
53 my ($content) = get("$uri");
54 if ($content eq ""){
55 &writelog("getxml2db   no responce from $uri, exit:");
56         exit;#しょぼかるが落ちているなど
57 }
58
59 my (@line) = split(/\n/, $content);
60
61 foreach(@line){
62
63 Jcode::convert(\$_,'euc');
64
65 #<ProgItem PID="21543" TID="528" StTime="20041114213000" EdTime="20041114220000" ChName="AT-X" Count="4" StOffset="0" SubTitle="いやだよ、サヨナラ…" Title="おとぎストーリー 天使のしっぽ" ProgComment=""/>
66 if (/^<ProgItem /){
67 s/<ProgItem //i;
68 s/\"\/>/\" /i;
69 s/\"[\s]/\";\n/gio;
70 s/\'/\\'/gio;
71 s/\"/\'/gio;
72 s/[\w]*=/\$item{$&}=/gio;
73 s/\=}=/}=/gio;
74
75 #$item{PID}='21543';
76 #$item{TID}='528';
77 #$item{StTime}='20041114213000';
78 #$item{EdTime}='20041114220000';
79 #$item{ChName}='AT-X';
80 #$item{Count}='4';
81 #$item{StOffset}='0';
82 #$item{SubTitle}='いやだよ、サヨナラ…';
83 #$item{Title}='おとぎストーリー 天使のしっぽ';
84 #$item{ProgComment}='';
85 eval("$_");
86 Jcode::convert(\$item{Title},'euc');
87 $programtitlename = $item{Title};
88 $programtitle = $dbh->quote($item{Title});
89 #print "$item{Title}\n";
90 #print "$item{TID}\n";
91 Jcode::convert(\$item{ChName},'euc');
92 #print "$item{ChName}\n";
93 Jcode::convert(\$item{SubTitle},'euc');
94 $programSubTitle = $dbh->quote($item{SubTitle});
95 $programSubTitle =~ s/\&lt\;/</gi;
96 $programSubTitle =~ s/\&gt\;/>/gi;
97
98 #print "$item{SubTitle}\n";
99 #print "$item{Count}\n";
100 $offsetmin = $item{StOffset}/60;
101 #print "Offset:$offsetmin  (min)\n";
102 #print "$item{EdTime}/$item{StTime}\n";
103 $edtime = &syobocaldate2foltiadate($item{EdTime});
104 $sttime = &syobocaldate2foltiadate($item{StTime});
105 #print "$sttime-$edtime\n";
106 $length = &calclength($sttime,$edtime);
107 $recstartdate = &calcoffsetdate($sttime ,$offsetmin );
108 $recenddate = &calcoffsetdate($edtime ,$offsetmin );
109 #print "$recstartdate-$recenddate\n";
110 #print "Length:$length(min)\n";
111
112 $stationid = &getstationid($item{ChName});
113 #print "StationID:$stationid \n";
114 #サブタイトル追加-------------------------------------------------
115 #番組があるか確認
116 $DBQuery =  "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'";
117          $sth = $dbh->prepare($DBQuery);
118         $sth->execute();
119  @titlecount= $sth->fetchrow_array;
120  
121  if ($titlecount[0] == 0){
122 #なければ追加
123
124 #200412012359
125 $nomalstarttime = substr($sttime,8,4);
126 $DBQuery =  "insert into  foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')";
127  $sth = $dbh->prepare($DBQuery);
128 $sth->execute();
129 &writelog("getxml2db  ADD TV Progtam:$item{TID}:$programtitle");
130
131
132 }else{
133 #2006/2/26
134 #あったら、タイトル確認して
135 $DBQuery =  "SELECT title FROM foltia_program WHERE tid = '$item{TID}'";
136          $sth = $dbh->prepare($DBQuery);
137         $sth->execute();
138  @titlearray = $sth->fetchrow_array;
139 #更新などされてたらupdate
140 #print "$titlearray[0] / $programtitle\n";
141  if ($titlearray[0] ne "$programtitlename" ){
142         $DBQuery =  "UPDATE  foltia_program  SET        title = $programtitle where  tid = '$item{TID}' ";
143           $sth = $dbh->prepare($DBQuery);
144         $sth->execute();
145         &writelog("getxml2db  UPDATE TV Progtam:$item{TID}:$programtitle");
146  }#end if update
147 }# end if TID
148
149
150 #PIDがあるか確認
151 $DBQuery =  "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
152          $sth = $dbh->prepare($DBQuery);
153         $sth->execute();
154  @subticount= $sth->fetchrow_array;
155  if ($subticount[0]  >= 1){
156         #PIDあったら上書き更新
157 #ここでこんなエラー出てる
158 #       DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bigint: "" at /home/foltia/perl/getxml2db.pl line 147.
159 #UPDATE  foltia_subtitle  SET stationid = '42',countno = '8',subtitle = '京都行きます' ,startdatetime = '200503010035'  ,enddatetime = '200503010050',startoffset  = '0' ,lengthmin = '15' WHERE tid = '550' AND pid =  '26000'
160 if ($item{Count} == ""){
161
162         $DBQuery =  "UPDATE  foltia_subtitle  SET
163         stationid = '$stationid',
164         countno =  null,
165         subtitle = $programSubTitle ,
166         startdatetime = '$recstartdate'  ,
167         enddatetime = '$recenddate',
168         startoffset  = '$offsetmin' ,
169         lengthmin = '$length'
170         WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
171
172 }else{
173
174         $DBQuery =  "UPDATE  foltia_subtitle  SET
175         stationid = '$stationid',
176         countno = '$item{Count}',
177         subtitle = $programSubTitle ,
178         startdatetime = '$recstartdate'  ,
179         enddatetime = '$recenddate',
180         startoffset  = '$offsetmin' ,
181         lengthmin = '$length'
182         WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
183 }
184                  $sth = $dbh->prepare($DBQuery);
185                 $sth->execute();
186         #       @subtitledata= $sth->fetchrow_array;
187  }else{
188         #なければ追加
189         
190         #こっちに入る時刻はオフセットされた時刻!
191         #そのままキューに入る形で
192         if ($item{Count} eq ""){
193         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
194         }else{
195         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
196         }
197                  $sth = $dbh->prepare($DBQuery);
198                 $sth->execute();
199         # @subtitledata= $sth->fetchrow_array;
200 }
201
202
203 #print "$DBQuery\n\n\n";
204 #debug 20050803
205 #&writelog("getxml2db $DBQuery");
206
207
208 }#if
209 }#foreach
210
211
212 ##      $dbh->disconnect();
213
214
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。
track feed