Perlゼミ

1. Perl
›
2. モジュール
›
3. データベース
›
4. DBIx::Custom
›
5. here

DBIx::Customの「execute」メソッドで「SQLを実行」する

SQLを実行するにはexecuteメソッドを使用します。

# Execute SQL
my $result = $dbi->execute("select * from book");

戻り値はDBIx::Custom::Resultオブジェクトです。すべての行を取得するにはallメソッドを使用します。

# Fetch all rows
my $rows = $result->all;

名前つきプレースホルダー

DBIx::CustomはOracleでサポートされているような名前つきプレースホルダーをすべてのRDBMSでサポートします。

# Named placeholder
my $result = $dbi->execute("select * from book where title = :title",
  {title => 'Perl'});

名前つきプレースホルダは「:title」のようにコロンで始まります。埋め込む値はハッシュリファレンスで指定することができます。これは以下の実行文と同じ意味があります。

my $title = 'Perl';
my $sql = "select * from book where title = ?";
my $dbh = DBI->connect(...);
my $sth = $dbh->prepare($sql);
$sth->execute($title);

名前つきプレースホルダーは、パラメータの値の順番を気にする必要がないのでとても便利です。

同名の名前つきプレースホルダーを利用したい場合

名前つきプレースホルダーで同名の名前を利用した場合があります。たとえば開始日付と終了日付を指定したいような場合です。このような場合は次のように記述します。

# Use named placeholder more than once
my $result = $dbi->execute(
  "select * from book where date > :date and date < :date",
  {date => [3, 5]});

複数の値を渡したい場合は、パラメータの値を「date => [3, 5]」のように配列のリファレンスとして指定します。

名前つきプレースホルダーの簡略表記

名前つきプレースホルダーは「title = :title」のように同じ名前を二度繰り返すことがとても多いので、簡略表記が用意されています。

# プレースホルダーの簡略表記
:プレースホルダー名{演算子}

たとえば「:title{=}」は「title = :title」と同じ意味になります。他の演算子でも同様に利用することができます。

# Named placeholder suger syntax
:title{like}    ->  title like :title
:title{<}       ->  title < :title
:title{>}       ->  title > :title

これを利用すると最初のほうで紹介したexecuteのサンプルは次のように書き直すことができます。

# Named placeholder suger syntax example
my $result = $dbi->execute("select * from book where :title{=}",
  {title => 'Perl'});

テーブル名を含んだ列名を利用する

プレースホルダー名としてテーブル名を含んだ列名を利用することもできます。

my $result = $dbi->execute("select * from book where :book.title{=}",
  {'book.title' => 'Perl'});

executeメソッドのオプション

DBIx::Customにはさまざまなオプションを指定することができます。第３引数以降を利用して次のように記述することができます。

$dbi->execute($sql, $param, オプション名 => オプションの値, ...)

executeメソッドのオプション

executeメソッドのオプションの一覧です。

SQLの加工 after_build_sql

after_build_sqlを使うと、SQLが構築された後に、SQLを加工することができます。値としては、変換のためのコードリファレンスを指定します。

after_build_sql => $code_ref

以下は例です。実行したSQLの列数を取得するサンプルです。

$dbi->select(
  table => 'book',
  column => 'distinct(name)',
  after_build_sql => sub {
    "select count(*) from ($_[0]) as t1"
  }
);

次のようなSQLが実行されます。

<pre>
select count(*) from (select distinct(name) from book) as t1;

SQLの末尾に追加 append

appendオプションを使うとSQLの末尾にステートメントを追加することできます。

append => 'order by name'

バインドタイプの指定 bind_type

インサートをするデータがバイナリデータの場合は、そのことを明示的に指定しなければならない場合があります。たとえばSQLiteではBLOB型のデータを挿入したい場合はDBIを使う場合は次のように書く必要があります。

$sth->bind_param($pos, $value, DBI::SQL_BLOB);

これと同じことをexecuteメソッドで実行するにはbind_typeオプションを使用します。

# Bind type option
bind_type => {image => DBI::SQL_BLOB}
bind_type => [image => DBI::SQL_BLOB]
bind_type => [[qw/image audio/] => DBI::SQL_BLOB]

bind_typeの値はハッシュリファレンスで指定します。複数の列に同じ値を指定したい場合は配列のリファレンスを利用することもできます(3番目の方法)。

フィルターの指定 filter

filterオプションを利用すれば、パラメータの値をプレースホルダーに埋め込む前に値をフィルタリングすることができます。

# Filter option
filter => {title  => sub { uc $_[0] }}
filter => {title  => 'upper_case'}
filter => [[qw/title author/]  => sub { uc $_[0] }]

フィルタは「{列名 => フィルタ(サブルーチンあるいはフィルタ名)}」というフォーマットで記述します。フィルタ名で利用した場合はregister_filterメソッドを使ってあらかじめフィルタを登録しておく必要があります。

複数の列名に対して、同じフィルタを利用したい場合は、配列のリファレンスを使って指定することもできます(3番目の方法)

ID指定 id

idオプションを指定すると、primary_keyが設定されている場合に、列名を指定せずにパラメータを指定することができます。

id => 4
id => [4, 5]

たとえばprimary_keyに[id1, id2]が設定されてしたとすればid => [4, 5]は、executeの第二引数であるパラメーターに{id1 => 4, id2 => 5}を指定したことと同じになります。

$dbi->execute(
  "select * from book where id1 = :id1 and id2 = :id2",
  {},
  primary_key => ['id1', 'id2'],
  id => [4, 5],
);

上記のexecuteは、以下と同じになります。

$dbi->execute(
  "select * from book where id1 = :id1 and id2 = :id2",
  {id1 => 4, id2 => 5}
);

クエリオブジェクトの取得 query

queryオプションに1を設定するとSQLを実行する代わりに戻り値として、DBIx::Custom::Queryオブジェクトを取得することができます。

query => 1

DBIx::Custom::Queryオブジェクトは、どのようなクエリが実際に実行されているかを調べたるのに便利です。

my $sql = $query->sql;
my $columns = $query->columns;

クエリオブジェクトをexecuteメソッドに渡すことは廃止予定の機能になっていますので利用しないでください。これは潜在的に非常にバグの多い実装でした。実行速度を上昇させるには代わりにreuseオプションを使用してください。

プライマリーキーの設定 primary_key

idオプションで利用するprimary_keyを設定します。

primary_key => 'id'
primary_key => ['id1', 'id2']

クエリの再利用 reuse

reuseオプションを使用すると、同一のSQLを連続して実行する場合に実行速度を上昇させることができます2～3倍のスピードになることが確認されています。クエリを保存するためのハッシュリファレンスを引数として指定します。

reuse => $hash_ref

以下はサンプルです。

my $queries = {};
for my $param (@params) {
  $dbi->execute($sql, $row, reuse => $queries);
}

もちろんinsert, update, delete, selectメソッドでも使用することができます。

my $queries = {};
for my $row (@rows) {
  $dbi->insert($row, table => 'book', reuse => $queries);
}

テーブル名 table

tableオプションでテーブル名を指定することができます。

table => 'author'

一般的にはexecuteメソッドでは、指定したSQLが実行されるのでtableオプションを指定する必要はありません。

tableオプションは次のような効果を持ちます。もし名前付きプレースホルダーの列名が完全修飾名で与えられなかった場合は、指定したテーブル名で、列名が完全修飾されます。またパラメータで利用されている列名も、完全修飾されます。

$dbi->execute("select * from book where title = :title and author = :author",
  {title => 'Perl', author => 'Ken'}, table => 'book');

上記のexecuteは以下と同じ意味になります。

$dbi->execute(
  "select * from book where title = :book.title and author = :book.author",
  {'book.title' => 'Perl', 'book.author' => 'Ken'});

tableオプションはtype_ruleのinto1やinto2が設定されている場合に利用するためのものです。なぜなら、完全修飾名でなければどのテーブルの列名なのかが判断できないので、type_ruleによるフィルタリングがうまくいきません。

ですから、type_ruleでinto1やinto2を設定しているときにだけ意識する必要があります。

テーブルのエイリアス table_alias

table_aliasオプションで、テーブルのエイリアスを設定することができます。

table_alias => {worker => 'user'} # {エイリアス名 => テーブル名}

キーはエイリアス名、値はテーブル名になります。テーブルのエイリアスを設定しておくと、Cメソッドで指定しinto1とinto2のフィルタリングの効果が、エイリアス名でも機能するようになります。

この例ではuserテーブルの列に設定したCとCのフィルタが、workerテーブルの列でも有効になります。

タイプルールを無効にする type_rule_off

type_rule_offオプションを使うと、type_ruleで設定したinto1とinto2のフィルタを無効にすることができます。

type_rule_off => 1

from1とfrom2のフィルタには何の影響も与えません。

タイプルール1を無効にする type_rule1_off

type_rule1_offオプションを使うと、type_ruleで設定したinto1のフィルタを無効にすることができます。

type_rule1_off => 1

from1とfrom2のフィルタには何の影響も与えません。

タイプルール2を無効にする type_rule2_off

type_rule2_offオプションを使うと、type_ruleで設定したinto2のフィルタを無効にすることができます。

type_rule2_off => 1

from1とfrom2のフィルタには何の影響も与えません。