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>