DATABASE_NAME, USER_NAME, PASSWORDは適当に読み替えてください。
MySQLのテーブル作成時に、da_channel, da_event, da_genreのプライマリキーに
NOT NULL AUTO_INCREMENTを追加しました。
--- epg.pl.orig 2009-01-25 16:32:56.000000000 +0900
+++ epg.pl 2012-02-18 12:03:30.000000000 +0900
@@ -190,7 +190,7 @@
and
? <= dv_start_time
and
- dv_start_time < datetime(?, ?)
+ dv_start_time < addtime(?, ?)
END
my $sth = $dbh->prepare($sql);
@@ -218,7 +218,7 @@
update
da_event
set
- dv_duration = time(julianday(?) - julianday(dv_start_time) - 0.5)
+ dv_duration = timediff(?, dv_start_time)
where
dv_service_id = ?
and
@@ -226,7 +226,7 @@
and
dv_start_time < ?
and
- ? < datetime(dv_start_time, dv_duration)
+ ? < addtime(dv_start_time, dv_duration)
END
my $udh = $dbh->prepare($sql);
@@ -325,8 +325,8 @@
}
sub dbiConnect {
- my $dsn = 'DBI:SQLite:dbname=epg.db';
- my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoCommit => 0});
+ my $dsn = 'DBI:mysql:database=DATABASE_NAME;host=localhost';
+ my $dbh = DBI->connect($dsn, 'USER_NAME', 'PASSWORD', {RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1});
return $dbh;
}
epg.cgiは、次のようになりました。
サブクエリが遅かったので、distinctをつけましたが影響あるのか分かりません。まあジャンルなので。
--- epg.cgi.orig 2009-01-25 15:38:34.000000000 +0900
+++ epg.cgi 2012-02-18 13:00:08.000000000 +0900
@@ -3,9 +3,11 @@
use strict;
use DBI;
use CGI;
+use utf8;
+
+my $dsn = 'DBI:mysql:database=DATABASE_NAME;host=localhost';
+my $dbh = DBI->connect($dsn, 'USER_NAME', 'PASSWORD', {RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1});
-my $dsn = 'DBI:SQLite:dbname=../oneseg24/epg.db';
-my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 1, AutoCommit => 0});
my @tm = localtime(time - 60*60*4); $tm[5] += 1900; $tm[4]++;
my $q = new CGI;
@@ -33,6 +35,7 @@
'#553333',
);
+binmode(STDOUT,":utf8");
print "Content-type: text/html\n\n";
print <<END;
@@ -126,12 +129,12 @@
p.dv_name,
p.dv_text,
- (select
+ (select distinct
g1.mg_name
from
- da_genre g
+ da_genre as g
left outer join
- ma_genre1 g1
+ ma_genre1 as g1
on
g1.mg_level1 = g.mg_level1
where
@@ -145,21 +148,21 @@
c.dc_id,
c.dc_ch,
- strftime('%s', p.dv_start_time),
- strftime('%s', datetime(p.dv_start_time, p.dv_duration)),
- strftime('%Y%m%d-%H%M%S', p.dv_start_time),
- strftime('%H%M%S', datetime(p.dv_start_time, p.dv_duration)),
- strftime('%H', p.dv_start_time),
- strftime('%M', p.dv_start_time),
-
- julianday(p.dv_start_time) - julianday(?),
- p.dv_start_time <= datetime('now', 'localtime'),
- datetime('now', 'localtime') < datetime(p.dv_start_time, p.dv_duration)
+ unix_timestamp(p.dv_start_time),
+ unix_timestamp(addtime(p.dv_start_time, p.dv_duration)),
+ date_format(p.dv_start_time, '%Y%m%d-%H%i%s'),
+ date_format(addtime(p.dv_start_time, p.dv_duration), '%H%i%s'),
+ date_format(p.dv_start_time, '%H'),
+ date_format(p.dv_start_time, '%i'),
+
+ datediff(p.dv_start_time, ?),
+ p.dv_start_time <= localtime(),
+ localtime() < addtime(p.dv_start_time, p.dv_duration)
from
- da_event p
+ da_event as p
inner join
- da_channel c
+ da_channel as c
on
c.dc_network_id = p.dv_original_network_id
and
@@ -167,9 +170,9 @@
where
c.dc_id = ?
and
- datetime(?, '4 hours') <= p.dv_start_time
+ date_add(?, INTERVAL 4 hour) <= p.dv_start_time
and
- p.dv_start_time < datetime(?, '29 hours')
+ p.dv_start_time < date_add(?, INTERVAL 29 hour)
order by
p.dv_start_time
END
スタイルシートとかでスクロールできるようにした方がいいのかも。
