CGI::Applicationメモ
CGI::Applicationを使って、MySQLに登録されているデータに対して、「追加」、「削除」、「更新」、「一覧表示」する
シンプルなWebアプリケーションを作った時のメモです。
↓を参考にしました。
http://codezine.jp/article/detail/449
http://rt.air-nifty.com/pods/CGI/Application.html
★テーブルの構成
mysql> DESC music; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(4) | NO | PRI | NULL | | | name | varchar(100) | NO | | NULL | | | title | varchar(100) | NO | | NULL | | | comments | text | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
mysql> SELECT * FROM music; +----+-------------------------------------+----------------------+----------+ | id | name | title | comments | +----+-------------------------------------+----------------------+----------+ | 1 | Ramones | It's Alive | US | | 2 | Buzzcocks | Singles Going Steady | UK | | 3 | Vibrators | Pure Mania | UK | | 4 | Stiff Little Fingers | Inflammable Material | UK | | 5 | The Damned | Damned Damned Damned | UK | | 6 | Generation X | Generation X | UK | | 7 | X Ray Spex | Germfree Adolescents | UK | | 8 | Johnny Thunders & the Heartbreakers | L.A.M.F | US | | 9 | Slaughter & The Dogs | Do It Dog Style | UK | | 10 | The Undertones | The Undertones | UK | +----+-------------------------------------+----------------------+----------+ 10 rows in set (0.00 sec) mysql>
テーブル作成
mysql> CREATE TABLE music( -> id int(4) PRIMARY KEY NOT NULL, -> name varchar(100) NOT NULL, -> title varchar(100) NOT NULL, -> comments text -> ); Query OK, 0 rows affected (0.03 sec) mysql>
行の追加
mysql> INSERT INTO music VALUES ('1','Ramones', "It's Alive",'US'); mysql> INSERT INTO music VALUES ('2','Buzzcocks', 'Singles Going Steady','UK'); mysql> INSERT INTO music VALUES ('3','Vibrators', 'Pure Mania','UK'); mysql> INSERT INTO music VALUES ('4','Stiff Little Fingers', 'Inflammable Material','UK'); mysql> INSERT INTO music VALUES ('5','The Damned', 'Damned Damned Damned','UK'); mysql> INSERT INTO music VALUES ('6','Generation X', 'Generation X','UK'); mysql> INSERT INTO music VALUES ('7','X Ray Spex', 'Germfree Adolescents','UK'); mysql> INSERT INTO music VALUES ('8','Johnny Thunders & the Heartbreakers','L.A.M.F','US'); mysql> INSERT INTO music VALUES ('9','Slaughter & The Dogs', 'Do It Dog Style','UK'); mysql> INSERT INTO music VALUES ('10','The Undertones', 'The Undertones','UK');
★ファイルの構成
[root@ha-01 WebApp]# tree . |-- WebApp.pm |-- dbapp.pl |-- delete_input.tmpl |-- insert_input.tmpl |-- update_input.tmpl `-- view.tmpl 0 directories, 6 files [root@ha-01 WebApp]#
dbapp.pl
#!/usr/bin/perl use strict; use warnings; use WebApp; my $app = WebApp->new( PARAMS => { db_dsn => 'dbi:mysql:db1:localhost;mysql_read_default_file=/etc/my.cnf', db_user => 'user1', db_pass => 'abcd1234' } ); $app->run();
WebApp.pm
package WebApp; use strict; use warnings; use base qw(CGI::Application); use CGI::Application::Plugin::DBH qw(dbh_config dbh); use CGI::Application::Plugin::Forward; sub cgiapp_init { my $self = shift; $self->query->charset('UTF-8'); $self->dbh_config( $self->param('db_dsn'), $self->param('db_user'), $self->param('db_pass') ); } sub setup { my $self = shift; $self->error_mode('error'); $self->start_mode('view'); $self->run_modes( 'view' => 'view', 'insert_input' => 'insert_input', 'insert_finish' => 'insert_finish', 'update_input' => 'update_input', 'update_finish' => 'update_finish', 'delete_input' => 'delete_input', 'delete_finish' => 'delete_finish', ); } sub error { my($self, $err) = @_; return $err; } sub view { my($self, $err) = @_; my $sth = $self->dbh->prepare('SELECT * FROM music') or die ($DBI::errstr); $sth->execute() or die ($DBI::errstr); my @rows = (); while (my $hash_ref = $sth->fetchrow_hashref()) { push (@rows, $hash_ref); } my $tmpl = $self->load_tmpl('view.tmpl'); $tmpl->param(rows => \@rows, %$err); return $tmpl->output; } sub insert_input { my($self, $err) = @_; my $tmpl = $self->load_tmpl('insert_input.tmpl'); return $tmpl->output; } sub insert_finish { my $self = shift; my $id = $self->query->param('id'); my $title = $self->query->param('title'); my $name = $self->query->param('name'); my $comments= $self->query->param('comments'); my $query = "INSERT music VALUES('$id' , '$title' , '$name', '$comments')"; $self->dbh->do($query) or die ($DBI::errstr); return $self->forward('view', { 'info' => '追加しました'}); } sub update_input { my ($self, $err) = @_; my $hash_ref = $self->dbh->selectrow_hashref( 'SELECT * FROM music WHERE id = ? LIMIT 1',undef, $self->query->param('id') ) or die ($DBI::errstr); my $tmpl = $self->load_tmpl('update_input.tmpl'); $tmpl->param(%$hash_ref); return $tmpl->output; } sub update_finish { my $self = shift; $self->dbh->do( 'UPDATE music SET title = ?, name = ?, comments = ? WHERE id = ?', undef, $self->query->param('title'), $self->query->param('name'), $self->query->param('comments'), $self->query->param('id'), ) or die ($DBI::errstr); return $self->forward('view', {'info' => '更新完了'}); } sub delete_input { my ($self, $err) = @_; my $hash_ref = $self->dbh->selectrow_hashref( 'SELECT * FROM music WHERE id = ? LIMIT 1',undef, $self->query->param('id') ) or die ($DBI::errstr); my $tmpl = $self->load_tmpl('delete_input.tmpl'); $tmpl->param(%$hash_ref); return $tmpl->output; } sub delete_finish { my $self = shift; $self->dbh->do( 'DELETE FROM music WHERE id = ?', undef, $self->query->param('id') ) or die ($DBI::errstr); return $self->forward('view' , {'info' => '削除完了'}); } 1;
view.tmpl
<html> <head> <title>WebApp</title> </head> <body> <h1>70s punk bands</h1> <TMPL_IF NAME="info"><p class="info"><TMPL_VAR NAME="info"></p></TMPL_IF> <table border="1"> <tr> <td>ID</td> <td>タイトル</td> <td>アーティスト</td> <td>コメント</td> <td>編集</td> <td>削除</td> </tr> <TMPL_LOOP NAME="rows"> <tr> <td><TMPL_VAR NAME="id" ></td> <td><TMPL_VAR NAME="title" ></td> <td><TMPL_VAR NAME="name" ></td> <td><TMPL_VAR NAME="comments" ></td> <td> <form action="dbapp.pl"> <input type="hidden" name="rm" value="update_input"> <input type="hidden" name="id" value="<tmpl_var name="id">"> <input type="submit" value="編集"> </form> </td> <td> <form action="dbapp.pl"> <input type="hidden" name="rm" value="delete_input"> <input type="hidden" name="id" value="<tmpl_var name="id">"> <input type="submit" value="削除"> </form> </td> </tr> </TMPL_LOOP> </table> <form action="dbapp.pl"> <input type="hidden" name="rm" value="insert_input"> <input type="submit" value="追加"> </form> </body> </html>
insert_input.tmpl
<html> <head> <title>WebApp</title> </head> <h1>追加画面</h1> <body> <form action="dbapp.pl" method="post"> <input type="hidden" name="rm" value="insert_finish"> <table border="1"> <tr> <td>ID</td> <td>タイトル</td> <td>アーティスト</td> <td>コメント</td> </tr> <tr> <td><input type="text" name="id" value=""size="10"></td> <td><input type="text" name="name" size="10"></td> <td><input type="text" name="title" size="10"></td> <td><input type="text" name="comments" size="10"></td> </tr> </table> <input type="submit" value="完了"> <input type="reset" value="取消"> </form> <p><a href="dbapp.pl">戻る</a></p> </body> </html>
update_input.tmpl
<html> <head> <title>WebApp</title> </head> <body> <h1>編集画面</h1> <form action="dbapp.pl" method="post"> <input type="hidden" name="rm" value="update_finish"> ID: <input type="text" name="id" value="<TMPL_VAR NAME="id">"> タイトル: <input type="text" name="title" value="<TMPL_VAR NAME="title">"> アーティスト: <input type="text" name="name" value="<TMPL_VAR NAME="name">"> コメント: <input type="text" name="comments" value="<TMPL_VAR NAME="comments">"> </tr> <br> <input type="submit" value="完了"> <input type="reset" value="取消"> </form> <p><a href="dbapp.pl">戻る</a></p> </body> </html>
delete_input.tmpl
<html> <head> <title>WebApp</title> </head> <body> <h1>削除確認画面</h1> <table border="1"> <tr> <td>ID</td> <td>タイトル</td> <td>アーティスト</td> <td>コメント</td> </tr> <tr> <td><TMPL_VAR NAME="id" ></td> <td><TMPL_VAR NAME="title" ></td> <td><TMPL_VAR NAME="name" ></td> <td><TMPL_VAR NAME="comments" ></td> <form action="dbapp.pl" method="post"> <input type="hidden" name="rm" value="delete_finish"> <input type="hidden" name="id" value="<TMPL_VAR NAME="id">"> </tr> </table> <br> <input type="submit" value="確定"> </form> <p><a href="dbapp.pl">戻る</a></p> </body> </html>