DBIx::Customで「SQLを生成」するためのメソッドの紹介

DBIx::Customで「SQLを生成」するためのメソッドを紹介します。

select文で利用する列名を簡単に生成する - column

select文で利用する列名の部分を簡単に生成するにはcolumnメソッドを使用します。

my $column = $dbi->column(book => [qw/author title/]);

次のような列名が生成されます。

"book"."author" as "book.author", "book"."title" as "book.title"

columnメソッドは重複を少なく記述するのに役立ちます。

列名のエイリアスに利用されている区切り文字を変更することもできます。

$dbi->separator('-');

区切り文字を「-」に変更した場合は次のような列名が生成されます。

"book"."author" as "book-author", "book"."title" as "book-title"

selectメソッドでcolumnを利用する

selectメソッドのcolumnオプションの中でもcolumnメソッドを利用することができます。

my $result = $dbi->select(
  table => 'book',
  column => [
    $dbi->column(book => [qw/title author/])
  ]
);

でもcolumnを記述するのはわずらわしいので、ハッシュリファレンスを利用するとcolumnメソッドを使ったのと同じ意味になるので、こちらを使うのが良いでしょう。

my $result = $dbi->select(
  table => 'book',
  column => [
    {book => [qw/title author/]}
  ]
);

テーブル名の修飾のない列名を生成する - mycolumn

mycolumnメソッドを利用すると、テーブル名の修飾のない列名を簡単に生成することができます。

my $mycolumn = $dbi->mycolumn(book => ['author', 'title']);

次のような列名が生成されます。

book.author as author,
book.title as title

これはselectメソッドのcolumnオプションで使用することが想定されています。

$dbi->select(column => [
  $dbi->mycolumn(book => ['author', 'title'])
]);

insert文の中のvaluesの部分を動的に生成する - values_clause

insert分の中のvaluesの部分を動的に生成するにはvalues_clauseメソッドを使用します。

my $values_clause = $dbi->values_clause({title => 'a', age => 2});

これは次のような文字列になります。

(title, author) values (title = :title, age = :age);

insert文を作るのに利用することができます。

my $insert_sql = "insert into book $values_clause";

update文で値を代入する部分を動的に生成する - assign_clause

update文で値を代入する部分を動的に生成するにはassign_clauseメソッドを使用します。

my $param = {id => 1, title => 'Perl'};
my $assign_clause = $dbi->assign_clause($param);

以下のように展開されます。

id = :id, title = :title

次のようにしてupdate文を作成することができます。

my $update_sql = "update book set $assign_clause";

このように作成したSQLはexecuteメソッドで実行することができます。

$dbi->execute($update_sql, $param);

予約語のためのクォートを変更する - quote

予約語のためのクォートを変更するにはquoteメソッドを使用します。

$dbi->quote('"');
$dbi->quote('[]');

クォートを1文字で設定することもできますし、ペアで設定することもできます。

quoteのデフォルト値はデータベースに応じて、自動的に設定されますので、通常は設定する必要はありません。

[データベース]                        [quoteの値]
MySQL                                 `

ODBC経由の接続
(Microsoft SQL ServerとAccessを想定)  []

それ以外                              "

テーブル名や列名は自動的にquoteの値を利用してクォートされます。

たとえばMySQLで以下のようなselect

$dbi->select(
  table => 'book',
  column => [
    {book => ['author']}
  ]
);

を実行すると、次のようなSQLが実行されます。

select book.author as `book.author` from `book`;

テーブル名と列名の区切り文字を変更する - separator / DBIx::Custom

テーブル名と列名の区切り文字を変更するにはseparatorメソッドを使用します。

$dbi->separator('-');

デフォルトの区切り文字は「.」です。

columnメソッドに与える影響

separatorに指定された文字は、columnメソッドが返す文字列の中で列名のエイリアスとして利用されます。

columnメソッドの場合は以下のようになります。

my $column = $dbi->column(book => [qw/title author/]);

戻り値の文字列に含まれる列名のエイリアスは「テーブル名-列名」のようになります。

"book"."title" as "book-title", "book"."author" as "book-author"

selectメソッドのcolumnオプションに与える影響

selectメソッドのcolumnオプションで、ハッシュリファレンスを利用した場合にもテーブル名と列名の区切り文字としてseparatorに指定した文字が利用されます。

my $result = $dbi->select(
  table => 'book',
  column => [
    {book => [qw/title author/]}
  ]
);

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

select "book"."title" as "book-title", "book"."author" as "book-author" from "book"

separatorが役立つ場合

たとえば、データベースから取得したデータをHTMLに埋め込む場合にとても役に立ちます。DOMを操作する場合にはjQueryがデファクトスタンダードになっています。ですが、デフォルトの「.」の区切り文字だとjQueryはドットをエスケープしない限りは正しくその文字列を扱うことができません。

区切り文字を「-」に変更すれば、そのまま扱うことができるので、HTMLと親和させて使うことができます。

like演算子のための値を生成する like_value

like演算子を使ったときの値は大抵「%Perl%」のように左右を「%」ではさむと思います。like_valueは%を左右につけるための変換を行う関数(コードリファレンス)です。

my $like_value = $dbi->like_value->("Perl");

この例では「%Perl%」という文字列を取得することができます。

現在時刻を生成するサブルーチンの登録

現在時刻を生成するサブルーチンはnow属性で設定することができます。

$dbi->now(
  sub {
    my ($sec, $min, $hour, $mday, $mon, $year) = localtime;
    $mon++;
    $year += 1900;
    return sprintf("%04d/%02d/%02d %02d:%02d:%02d");
  }
);

デフォルトでは現在時刻を「2011-10-14 05:05:27」という形式で返却するサブルーチンが登録されています。

また現在時刻の取得にデータベースの関数を利用することもでき、その場合はスカラのリファレンスで指定します。

$dbi->now(\"now()");

この現在時刻はinsertメソッドのctimeオプションとmtimeオプション, updateメソッドのmtimeオプションで利用されます。