データベースとプログラムの連携 | Mojolicious入門

1. 
   
   Perl
   
   
   
›
2. 
   
   Mojolicious
   
   
   

　データベースの基礎を学習した後は、プログラムからデータベースにアクセスする方法を学びましょう。

DBD::SQLiteのインストール

　まず最初にSQLiteを使えるようにする必要があります。Windowsの場合はcpanコマンドを使って、Linux, Mac OS Xの場合は、cpanmコマンドを使ってDBD::SQLiteモジュールをインストールします。

# Windows
cpan DBD::SQLite

# Linux, Mac OS X
cpanm DBD::SQLite

DBIx::Connectorのインストール

　Perlにはデータベースを操作する場合にDBIを使うようにと解説されていることが多いですが、Webサイトを作成する場合は、DBIをそのまま使ってはいけません。Webサイトを作成する場合は、プリフォークサーバーで本番環境において配置するのですが、DBIはデータベースとの接続の管理をしてくれませんので、接続が途切れるという不具合がおきます。

　ですから、DBIにデータベースとの接続を自動的に管理してくれる機能がつけられたモジュールを利用する必要があります。今回はDBIの機能に、接続の自動管理機能がついた、DBIx::Connectorというモジュールを使います。このようなモジュールはコネクションマネージャーと呼ばれるので覚えておきましょう。

　DBIx::Connectorをインストールします。

# Windows
cpan DBIx::Connector

# Linux, Mac OS X
cpanm DBIx::Connector

データベースへの接続

　ではDBIx::Connectorを使って、データベースにアクセスしてみましょう。まず最初にDBIx::Connectorオブジェクトを作成します。DBIx::Connectorオブジェクトを作成するにはnewメソッドを使用します。

use DBIx::Connector;

# 接続の作成
my $dsn = 'dbi:SQLite:dbname=test.db';
my $conn = DBIx::Connector->new(
  $dsn, # データソース名
  undef, # ユーザー名
  undef, # パスワード
  # DBIのオプション
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1
  }
);

　DBIx::Connectorのnewの引数は、DBIのconnectメソッドの引数と同じです。newの第1引数はデータソースと呼ばれるデータベースに接続するための情報を指定します。フォーマットは少し特殊ですが覚えてしまいましょう。

データソースの中で接続するデータベース名を指定することができます。SQLiteではデータベースは単純にファイルとして作成されるのでデータベース名として好きな名前を指定します。戻り値にはデータベースハンドルと呼ばれるデータベースと通信するためのオブジェクトが返されます。

　これを実行するとカレントディレクトリにtest.dbというファイルができているはずです。

ユーザ名とパスワードを指定して接続する

　SQLiteで作成したデータベースへの接続にはユーザ名やパスワードは必要ありません。ですがMySQLやPostgreSQLやOracleなどの標準的なデータベースではユーザ名とパスワードを接続のときに指定する必要があります。

　DBIを使用してユーザ名とパスワードを指定して接続するには次のようにします。第2引数にユーザ名、第3引数にパスワードを指定します。

my $conn = DBIx::Connector->new($dsn, $user, $password);

オプションの解説

　DBIx::Connectorのnewメソッドの第4引数にはDBIのオプションを指定することができます。

DBIx::Connector->new($dsn, $user, $password, {opt1 => $val1, opt2 => $val2, ..});

　DBIのデフォルトの動作はエラーが発生した場合に警告を標準エラー出力に出力するようになっています。

　ですが警告ではなく、エラーメッセージを出力してプログラムを終了するほうがよりよいように思います。

　警告を出力しないようにするには次のようにします。

PrintError => 0

　またエラーメッセージを出力してプログラムを終了するようにするには次のようにします。

RaiseError => 1

　またDBIにはAutoCommitと呼ばれるオプションがあります。これはコミットと呼ばれる処理を自動的に行うか行わないかを決めるオプションです。データベースのドライバによってデフォルトの値が異なる可能性がありますので明示的に指定しておいたほうが良いと思います。わたしの場合は自動的にコミットするようにしておいてトランザクションが必要になった場合にAutoCommitを0に設定します。

　ですのでこのようなことを踏まえてデータベースに接続する場合は次のようなサンプルのような記述にしておくのがよいと思います。

データベースハンドルの取得

　データベースにアクセスするためには、コネクションマネージャーからデータベースハンドルを取得する必要があります。データベースハンドルはdbhメソッドで取得できます。

# データベースハンドルの取得
my $dbh  = $conn->dbh;

　これを使って、データベースへアクセスを行います。

SQLの実行

　SQLとはテーブルを作成したり、データベースにデータを登録したり、データを取り出したりするために使用する問い合わせ言語のことです。基本的なSQLについてはこれから解説します。ここではまずSQLを実行する方法を解説します。

　SQLを実行する方法には2通りの方法があります。

1. doメソッドを使用して直接実行する方法
2. prepareメソッドでステートメントハンドルを準備してからexecuteメソッドで実行する方法

　二つの方法がありますが、原則として2番目の方法を使います。Webサイトではユーザーから受け取ったデータをデータベースに挿入することになりますが、一番目の方法は、セキュリティによる危険がありますので、2番目の方法を使うようにしてください。

　今回は2番目の方法だけ解説します。

レコードの追加 insert

　テーブルにレコードを挿入するにはinsert文を使用します。

# ステートメントハンドルの準備
my $sth = $dbh->prepare('insert into book (id, title, author) values (?, ?, ?)');

# SQLの実行
my @params = ('00000001', 'Perl', 'kimoto');
$sth->execute(@params);

　まず最初にprepare文で、ステートメントハンドルを準備します。パラメーターの部分は「?」を記述します。これはプレースホルダーと呼ばれます。

　SQLを実際に実行するには、ステートメントハンドルからexecuteメソッドを呼び出します。引数にはプレースホルダーに埋め込む値を配列で指定します。

　ユーザーから受け取った値を利用するときは、必ずプレースホルダーを利用してください。プレースホルダーを使えば、値は適切にクォートされますので、安全です。

レコードの更新 update

　テーブルにあるデータを更新するにはupdate文を使用します。

# ステートメントハンドルの準備
my $sth = $dbh->prepare('update book set title = ? where author = ?');

# SQLの実行
my @params = ('Ruby', 'taro',);
$sth->execute(@params);

レコードの削除 delete

　レコードを削除するにはdelete文を使用します。

# ステートメントハンドルの準備
my $sth = $dbh->prepare('delete from book where id = ?');

# SQLの実行
my @params = ('00000001');
$sth->execute(@params);

レコードの取得 select

　列を取得するにはselect文を使用します。他のSQL文と異なるのは、各行を取得するためにフェッチという処理を行うことです。

# ステートメントハンドルの準備
my $sth = $dbh->prepare('select * from book where title like ?');

# SQLの実行
my @params = ('%Perl%');
$sth->execute(@params);

# フェッチ
while (my $row =  $sth->fetchrow_hashref) {
  my $id = $row->{id};
  my $title = $row->{title};

  print "id: $id, title: $title\n";
}

　fetchrow_hashrefメソッドを使用すれば、各行のデータをハッシュリファレンスとして取得できます。すべての行を取得するまで、whileループの中で使用してすべての行を取得するまで繰り返します。

日本語の扱い

　プログラムの内部では内部文字列を利用して、外部へ出力するときは、バイト文字列に変換するということを、解説しましたね。データベースの場合もこれと同じ考えです。データベースにデータを渡すときは、内部文字列をバイト文字列に変換する必要があります。

　けれどもこれは、とてもわずらわしいですね。でも、これを自動で行ってくれるsqlite_unicodeというオプションがあるので、オプションさえ指定しておけば、後は何も考える必要はありません。

　接続するときにオプションとして指定するようにしましょう。

use DBIx::Connector;

# 接続の作成
my $dsn = 'dbi:SQLite:dbname=test.db';
my $conn = DBIx::Connector->new(
  $dsn, # データソース名
  undef, # ユーザー名
  undef, # パスワード
  # DBIのオプション
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
    sqlite_unicode => 1 # 内部文字列を自動的にUTF-8バイト文字列に変換
  }
);

サンプルコード

　では実際に動かすことのできるプログラムを書いてみましょう。SQLiteでは、データベースをメモリ上に作成することもできるので、試験が簡単です。データソース名の中で、「dbname」に「:memory:」を指定します。ソースコードはUTF-8で保存してください。

use strict;
use warnings;
use utf8;

use DBIx::Connector;
use Encode 'encode';

# 接続の作成
my $dsn = 'dbi:SQLite:dbname=:memory:';
my $conn = DBIx::Connector->new(
  $dsn, # データソース名
  undef, # ユーザー名
  undef, # パスワード
  # DBIのオプション
  {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
    sqlite_unicode => 1
  }
);

# データベースハンドルの取得
my $dbh  = $conn->dbh;

# テーブルの作成
my $create_table_sql = <<"EOS";
create table book (
  id primary key,
  title not null default '',
  author not null default ''
);
EOS
$dbh->do($create_table_sql);

# insertæ–‡
my $sth = $dbh->prepare('insert into book (id, title, author) values (?, ?, ?)');
$sth->execute('00000001', 'Perl', 'kimoto');

# updateæ–‡
$sth = $dbh->prepare('update book set title = ? where author = ?');
$sth->execute('Ruby', 'taro');

# deleteæ–‡
$sth = $dbh->prepare('delete from book where id = ?');
$sth->execute('00000001');

# insertæ–‡
$sth = $dbh->prepare('insert into book (id, title, author) values(?, ?, ?)');
$sth->execute('00000001', 'Perl Tutorial', '木本');
$sth->execute('00000002', 'Perl advantage', '健');
$sth->execute('00000003', 'Ruby Tutorial', '洋介');

# selectæ–‡
$sth = $dbh->prepare('select * from book where title like ?');
$sth->execute('%Perl%');

# フェッチ
while (my $row =  $sth->fetchrow_hashref) {
  my $id = $row->{id};
  my $title = $row->{title};
  my $author = $row->{author};

  my $line = "id: $id, title: $title, author: $author\n";
  
  # Windowsの場合はcp932で、それ以外のOSはUTF-8で出力
  my $enc = $^O eq 'MSWin32' ? 'cp932' : 'UTF-8';
  print encode($enc, $line);
}

　列「title」に「Perl」を含んでいる場合だけ、行の内容が表示されます。

id: 00000001, title: Perl Tutorial, author: 木本
id: 00000002, title: Perl advantage, author: 健