root/trunk/install/perl/getxml2db.pl

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

initial import

  • 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/[\w]*=/\$item{$&}=/gio;
72 s/\=}=/}=/gio;
73
74 #$item{PID}='21543';
75 #$item{TID}='528';
76 #$item{StTime}='20041114213000';
77 #$item{EdTime}='20041114220000';
78 #$item{ChName}='AT-X';
79 #$item{Count}='4';
80 #$item{StOffset}='0';
81 #$item{SubTitle}='いやだよ、サヨナラ…';
82 #$item{Title}='おとぎストーリー 天使のしっぽ';
83 #$item{ProgComment}='';
84 eval("$_");
85 Jcode::convert(\$item{Title},'euc');
86 $programtitlename = $item{Title};
87 $programtitle = $dbh->quote($item{Title});
88 #print "$item{Title}\n";
89 #print "$item{TID}\n";
90 Jcode::convert(\$item{ChName},'euc');
91 #print "$item{ChName}\n";
92 Jcode::convert(\$item{SubTitle},'euc');
93 $programSubTitle = $dbh->quote($item{SubTitle});
94 $programSubTitle =~ s/\&lt\;/</gi;
95 $programSubTitle =~ s/\&gt\;/>/gi;
96
97 #print "$item{SubTitle}\n";
98 #print "$item{Count}\n";
99 $offsetmin = $item{StOffset}/60;
100 #print "Offset:$offsetmin  (min)\n";
101 #print "$item{EdTime}/$item{StTime}\n";
102 $edtime = &syobocaldate2foltiadate($item{EdTime});
103 $sttime = &syobocaldate2foltiadate($item{StTime});
104 #print "$sttime-$edtime\n";
105 $length = &calclength($sttime,$edtime);
106 $recstartdate = &calcoffsetdate($sttime ,$offsetmin );
107 $recenddate = &calcoffsetdate($edtime ,$offsetmin );
108 #print "$recstartdate-$recenddate\n";
109 #print "Length:$length(min)\n";
110
111 $stationid = &getstationid($item{ChName});
112 #print "StationID:$stationid \n";
113 #サブタイトル追加-------------------------------------------------
114 #番組があるか確認
115 $DBQuery =  "SELECT count(*) FROM foltia_program WHERE tid = '$item{TID}'";
116          $sth = $dbh->prepare($DBQuery);
117         $sth->execute();
118  @titlecount= $sth->fetchrow_array;
119  
120  if ($titlecount[0] == 0){
121 #なければ追加
122
123 #200412012359
124 $nomalstarttime = substr($sttime,8,4);
125 $DBQuery =  "insert into  foltia_program values ($item{TID},$programtitle,'','$nomalstarttime','$length','','','3','1','')";
126  $sth = $dbh->prepare($DBQuery);
127 $sth->execute();
128 &writelog("getxml2db  ADD TV Progtam:$item{TID}:$programtitle");
129
130
131 }else{
132 #2006/2/26
133 #あったら、タイトル確認して
134 $DBQuery =  "SELECT title FROM foltia_program WHERE tid = '$item{TID}'";
135          $sth = $dbh->prepare($DBQuery);
136         $sth->execute();
137  @titlearray = $sth->fetchrow_array;
138 #更新などされてたらupdate
139 #print "$titlearray[0] / $programtitle\n";
140  if ($titlearray[0] ne "$programtitlename" ){
141         $DBQuery =  "UPDATE  foltia_program  SET        title = $programtitle where  tid = '$item{TID}' ";
142           $sth = $dbh->prepare($DBQuery);
143         $sth->execute();
144         &writelog("getxml2db  UPDATE TV Progtam:$item{TID}:$programtitle");
145  }#end if update
146 }# end if TID
147
148
149 #PIDがあるか確認
150 $DBQuery =  "SELECT count(*) FROM foltia_subtitle WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
151          $sth = $dbh->prepare($DBQuery);
152         $sth->execute();
153  @subticount= $sth->fetchrow_array;
154  if ($subticount[0]  >= 1){
155         #PIDあったら上書き更新
156 #ここでこんなエラー出てる
157 #       DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bigint: "" at /home/foltia/perl/getxml2db.pl line 147.
158 #UPDATE  foltia_subtitle  SET stationid = '42',countno = '8',subtitle = '京都行きます' ,startdatetime = '200503010035'  ,enddatetime = '200503010050',startoffset  = '0' ,lengthmin = '15' WHERE tid = '550' AND pid =  '26000'
159 if ($item{Count} == ""){
160
161         $DBQuery =  "UPDATE  foltia_subtitle  SET
162         stationid = '$stationid',
163         countno =  null,
164         subtitle = $programSubTitle ,
165         startdatetime = '$recstartdate'  ,
166         enddatetime = '$recenddate',
167         startoffset  = '$offsetmin' ,
168         lengthmin = '$length'
169         WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
170
171 }else{
172
173         $DBQuery =  "UPDATE  foltia_subtitle  SET
174         stationid = '$stationid',
175         countno = '$item{Count}',
176         subtitle = $programSubTitle ,
177         startdatetime = '$recstartdate'  ,
178         enddatetime = '$recenddate',
179         startoffset  = '$offsetmin' ,
180         lengthmin = '$length'
181         WHERE tid = '$item{TID}' AND pid =  '$item{PID}' ";
182 }
183                  $sth = $dbh->prepare($DBQuery);
184                 $sth->execute();
185         #       @subtitledata= $sth->fetchrow_array;
186  }else{
187         #なければ追加
188         
189         #こっちに入る時刻はオフセットされた時刻!
190         #そのままキューに入る形で
191         if ($item{Count} eq ""){
192         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid',null,$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
193         }else{
194         $DBQuery = "insert into foltia_subtitle values ( '$item{PID}','$item{TID}','$stationid','$item{Count}',$programSubTitle,'$recstartdate','$recenddate','$offsetmin' ,'$length')";
195         }
196                  $sth = $dbh->prepare($DBQuery);
197                 $sth->execute();
198         # @subtitledata= $sth->fetchrow_array;
199 }
200
201
202 #print "$DBQuery\n\n\n";
203 #debug 20050803
204 #&writelog("getxml2db $DBQuery");
205
206
207 }#if
208 }#foreach
209
210
211 ##      $dbh->disconnect();
212
213
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。
track feed