root/trunk/install/perl/getxml2db.pl

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

getxml2db.pl:
If-Modified-Sinceの条件付きGETに対応。

  • 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 use Digest::MD5 qw(md5_hex);
22
23 $path = $0;
24 $path =~ s/getxml2db.pl$//i;
25 if ($pwd  ne "./"){
26 push( @INC, "$path");
27 }
28 require "foltialib.pl";
29
30
31 $processes =  &processfind("getxml2db.pl");
32 if ($processes > 1 ){
33 #print "process  found:$processes\n";
34 &writelog("getxml2db   processes exist. exit:");
35 exit;
36 }else{
37 #print "process NOT found:$processes\n";
38 &writelog("getxml2db  Normal launch.");
39 }
40
41 if ($ARGV[0]  eq "long"){
42         $uri="http://cal.syoboi.jp/cal_chk.php";
43         #$uri="http://syobocal.orz.hm/cal_chk.php";
44         &writelog("getxml2db  use long mode.");
45 }else{
46         $uri="http://cal.syoboi.jp/cal_chk.xml";
47         #$uri="http://syobocal.orz.hm/cal_chk.xml";
48 }
49
50
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;
57
58 # If-Modified-Since使うように変更#2008/11/14
59 my  $CacheDir = '/tmp/shobocal';
60 if (! -e $CacheDir) {
61         mkdir $CacheDir or die "cannot create $CacheDir: $!";
62 }
63 my $cache = sprintf("%s/%s.xml", $CacheDir, Digest::MD5::md5_hex($uri));
64 LWP::Simple::mirror($uri, $cache) or die "cannot get content from $uri";
65 open(SHOBO, "<$cache");
66 my (@line) = <SHOBO>;
67 close(SHOBO);
68 #my ($content) = get("$uri");
69 #if ($content eq ""){
70 #&writelog("getxml2db   no responce from $uri, exit:");
71 #       exit;#しょぼかるが落ちているなど
72 #}
73 #my (@line) = split(/\n/, $content);
74
75 foreach(@line){
76
77 Jcode::convert(\$_,'euc');
78
79 #<ProgItem PID="21543" TID="528" StTime="20041114213000" EdTime="20041114220000" ChName="AT-X" Count="4" StOffset="0" SubTitle="いやだよ、サヨナラ…" Title="おとぎストーリー 天使のしっぽ" ProgComment=""/>
80 if (/^<ProgItem /){
81 s/<ProgItem //i;
82 s/\"\/>/\" /i;
83 s/\"[\s]/\";\n/gio;
84 s/\'/\\'/gio;
85 s/\"/\'/gio;
86 #s/[\w]*=/\$item{$&}=/gio;
87 #s/\=}=/}=/gio;
88 s/(\w+)=/\$item{$1}=/gio;#by foltiaBBS
89
90 #$item{PID}='21543';
91 #$item{TID}='528';
92 #$item{StTime}='20041114213000';
93 #$item{EdTime}='20041114220000';
94 #$item{ChName}='AT-X';
95 #$item{Count}='4';
96 #$item{StOffset}='0';
97 #$item{SubTitle}='いやだよ、サヨナラ…';
98 #$item{Title}='おとぎストーリー 天使のしっぽ';
99 #$item{ProgComment}='';
100 eval("$_");
101 Jcode::convert(\$item{Title},'euc');
102
103 $programtitlename = $item{Title};
104 $programtitlename =~ s/\&lt\;/</gi;
105 $programtitlename =~ s/\&gt\;/>/gi;
106 $programtitlename =~ s/\&amp\;/\&/gi;
107 $programtitle = $dbh->quote($programtitlename);
108
109 Jcode::convert(\$item{ChName},'euc');
110 Jcode::convert(\$item{SubTitle},'euc');
111
112 #$programSubTitle = $dbh->quote($item{SubTitle});
113 $programSubTitle = $item{SubTitle};
114 $programSubTitle =~ s/\&lt\;/</gi;
115 $programSubTitle =~ s/\&gt\;/>/gi;
116 $programSubTitle =~ s/\&amp\;/\&/gi;
117 $programSubTitle = $dbh->quote($programSubTitle);
118
119 $offsetmin = $item{StOffset}/60;
120 $edtime = &syobocaldate2foltiadate($item{EdTime});
121 $sttime = &syobocaldate2foltiadate($item{StTime});
122 $length = &calclength($sttime,$edtime);
123 $recstartdate = &calcoffsetdate($sttime ,$offsetmin );
124 $recenddate = &calcoffsetdate($edtime ,$offsetmin );
125
126 $stationid = &getstationid($item{ChName});
127 #サブタイトル追加-------------------------------------------------
128 #番組があるか確認
129 $DBQuery =  "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'";
130          $sth = $dbh->prepare($DBQuery);
131         $sth->execute();
132  @titlecount= $sth->fetchrow_array;
133  
134  if ($titlecount[0] == 0){
135 #なければ追加
136
137 #200412012359
138 $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
145
146 }else{
147 #2006/2/26
148 #あったら、タイトル確認して
149 $DBQuery =  "SELECT title FROM foltia_program WHERE tid = '$item{TID}'";
150          $sth = $dbh->prepare($DBQuery);
151         $sth->execute();
152  @titlearray = $sth->fetchrow_array;
153 #更新などされてたらupdate
154 #print "$titlearray[0] / $programtitle\n";
155  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);
160         &writelog("getxml2db  UPDATE TV Progtam:$item{TID}:$programtitle");
161  }#end if update
162 }# end if TID
163
164
165 #PIDがあるか確認
166 $DBQuery =  "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
167          $sth = $dbh->prepare($DBQuery);
168         $sth->execute();
169  @subticount= $sth->fetchrow_array;
170  if ($subticount[0]  >= 1){
171         #PIDあったら上書き更新
172 #ここでこんなエラー出てる
173 #       DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bigint: "" at /home/foltia/perl/getxml2db.pl line 147.
174 #UPDATE  foltia_subtitle  SET stationid = '42',countno = '8',subtitle = '京都行きます' ,startdatetime = '200503010035'  ,enddatetime = '200503010050',startoffset  = '0' ,lengthmin = '15' WHERE tid = '550' AND pid =  '26000'
175 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
187 }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
203  }else{
204         #なければ追加
205         
206         #こっちに入る時刻はオフセットされた時刻!
207         #そのままキューに入る形で
208         if ($item{Count} eq ""){
209         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
210         }else{
211         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
212         }
213 #                $sth = $dbh->prepare($DBQuery);
214 #               $sth->execute();
215         $oserr = $dbh->do($DBQuery);
216
217 }
218
219
220 #print "$DBQuery\n\n\n";
221 #debug 20050803
222 #&writelog("getxml2db $DBQuery");
223
224
225 }#if
226 }#foreach
227
228 #$oserr = $dbh->commit;
229 ##      $dbh->disconnect();
230
231
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。
track feed