root/trunk/install/perl/getxml2db.pl

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