++ed by:
XIAOCANG FUKAI AZJADFTRE STAUGAARD

4 PAUSE users
7 non-PAUSE users.

Yuki Kimoto

NAME

DBIx::Custom::Guides::Ja - DBIx::Customの日本語ガイド

ガイド

DBIx::Customはデータベースへのクエリの発行を簡単に行うための クラスです。DBIx::ClassDBIx::Simpleと同じように DBIのラッパクラスになっています。

DBIx::CustomはO/Rマッパーではありません。O/Rマッパーは 便利ですが、O/Rマッパのたくさんの文法を覚える必要があります。 また、O/Rマッパによって生成されたSQLは非効率なことがあり、 生のSQLを発行しなければならないこともあります。

DBIx::CustomはO/RマッパとDBIの中間に位置するモジュールです。 DBIx::Customは柔軟なハッシュパラメータバインディングとフィルタリング のシステムを提供します。またSQLを簡単に実行するための、 insert(), update(), delete(),select()などの シュガーメソッドも提供します。

DBIx::CustomはSQLを尊重します。SQLはとても複雑で、美しくはありません。 けれども、SQLはデファクトスタンダードな技術です。 ですので、データベースを学ぶすべての人はSQLを知っています。 あなたがすでにSQLを知っているなら、DBIx::Customを使って 何かを行うために覚えることはとても少ないです。

では使い方を解説します。

1. データベースへの接続

DBIx::Customオブジェクトを生成し、データベースに接続するには connect()メソッドを使用します。

    use DBIx::Custom;
    my $dbi = DBIx::Custom->connect(data_source => "dbi:mysql:database=dbname",
                                    user => 'ken', password => '!LFKD%$&');

Data sourceのサンプル:

MySQL

    "dbi:mysql:database=$database"
    "dbi:mysql:database=$database;host=$hostname;port=$port"

SQLite

    "dbi:SQLite:dbname=$database"
    "dbi:SQLite:dbname=:memory:"

PostgreSQL

    "dbi:Pg:dbname=$dbname"

Oracle

    "dbi:Oracle:$dbname"
    "dbi:Oracle:host=$host;sid=$sid"

ODBC(Microsoft Access)

    "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=hoge.mdb"

ODBC(SQL Server)

   "dbi:ODBC:driver={SQL Server};Server=(local);database=test;Trusted_Connection=yes;AutoTranslate=No;"

DBIx::CustomDBIのラッパです。 DBIオブジェクトはdbhで取得することができます。

    my $dbh = $dbi->dbh;

データベースハンドル属性にはデフォルトで次のものが設定されます。

    $dbi->dbh->{RaiseError} = 1;
    $dbi->dbh->{PrintError} = 0;
    $dbi->dbh->{AutoCommit} = 1;

この設定を行っているので、致命的なエラーが起こると、 例外が発生しプログラムは終了します。 またクエリが発行されると自動的にコミットされます。

2. シュガーメソッド

DBIx::Customは、 insert()update()delete()select() のようなシュガーメソッドを持っています。 小さなことを行うのであれば、SQL文を 作成する必要はありません。

insert()

insertメソッドです。データベースにデータを挿入します。

    $dbi->insert(table  => 'books',
                 param  => {title => 'Perl', author => 'Ken'});

これは次のDBIの操作と同じです。

    my $sth = $dbh->prepare('insert into (title, author) values (?, ?);');
    $sth->execute('Perl', 'Ken');

update()

updateメソッドです。データベースのデータを更新します。

    $dbi->update(table  => 'books', 
                 param  => {title => 'Perl', author => 'Ken'}, 
                 where  => {id => 5});

これは次のDBIの操作と同じです。

    my $sth = $dbh->prepare(
        'update books set title = ?, author = ? where id = ?;');
    $sth->execute('Perl', 'Ken', 5);

updateメソッドは安全のため where句のないSQLを発行することを許可していません。 もしすべての行を更新したい場合は update_all()メソッドを使用してください。

    $dbi->update_all(table  => 'books', 
                     param  => {title => 'Perl', author => 'Ken'});

delete()

deleteメソッドです。データベースのデータを削除します。

    $dbi->delete(table  => 'books',
                 where  => {author => 'Ken'});

これは次のDBIの操作と同じです。

    my $sth = $dbh->prepare('delete from books where id = ?;');
    $sth->execute('Ken');

deleteメソッドは安全のため where句のないSQLを発行することを許可していません。 もしすべての行を削除したい場合は delete_all()メソッドを使用してください。

    $dbi->delete_all(table  => 'books');

select()

selectメソッドです。テーブル名だけを指定しています。

    my $result = $dbi->select(table => 'books');

これは次のDBIの操作と同じです。

    my $sth = $dbh->prepare('select * from books;);
    $sth->execute;

select()メソッドの戻り値はDBIx::Custom::Result オブジェクトです。fetchメソッドを使用して 行をフェッチすることができます。

    while (my $row = $result->fetch) {
        my $title  = $row->[0];
        my $author = $row->[1];
    }

次のselectは行の名前とwhere句を指定したものです。

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

次のDBIの操作と同じです。

    my $sth = $dbh->prepare(
        'select author, title from books where author = ?;');
    $sht->execute('Ken');

テーブルをjoinしたい場合はrelationを使用します。

    my $result = $dbi->select(
        table    => ['books', 'rental'],
        column   => ['books.name as book_name']
        relation => {'books.id' => 'rental.book_id'}
    );

次のDBIの操作と同じです。

    my $sth = $dbh->prepare(
        'select books.name as book_name from books, rental' .
        'where books.id = rental.book_id;');
    $sth->execute;

SQL文の末尾に文字列を追加したい場合は<append>オプションを使用します。

    my $result = $dbi->select(
        table  => 'books',
        where  => {author => 'Ken'},
        append => 'order by price limit 5',
    );

次のDBIの操作と同じです。

    my $sth = $dbh->prepare(
        'select * books where author = ? order by price limit 5;');
    $sth->execute;

appendオプションは、insert()update()update_all() delete()selectメソッドで使用することが できます。

この後のフィルタリングの解説で詳しく扱いますが、値をフィルタリングしたい 場合はfilterオプションを使用することができます。

    $dbi->insert(table  => 'books',
                 param  => {title => 'Perl', author => 'Ken'});
                 filter => {title  => 'encode_utf8',
                            author => 'encode_utf8'});

filterオプションは、insert()update()update_all() delete()selectメソッドで使用することが できます。

select()メソッドのwhereオプションではハッシュの代わりに タグを利用することもできます。これによって柔軟な 条件を指定することができます。

    # Select, more flexible where
    my $result = $dbi->select(
        table  => 'books',
        where  => ['{= author} and {like title}', 
                   {author => 'Ken', title => '%Perl%'}]
    );

タグについては以降で解説します。

3. 行のフェッチ

select()メソッドの戻り値であるDBIx::Custom::Result には行をフェッチするためのさまざまなメソッドが 用意されています。 (このセクションの解説では「配列」は「配列のリファレンス」を 「ハッシュ」は「ハッシュのリファレンス」を意味しますので 注意してください。)

fetch

一行フェッチして配列に格納します。

    while (my $row = $result->fetch) {
        my $author = $row->[0];
        my $title  = $row->[1];
    }

fetch_first

一行だけフェッチして配列に格納します。

    my $row = $result->fetch_first;

フェッチが終わった後は、ステートメントハンドルからfinish() メソッドが呼び出されてそれ以上フェッチできなくなります。

fetch_multi

複数行をフェッチして配列の配列に格納します。

    while (my $rows = $result->fetch_multi(5)) {
        my $first_author  = $rows->[0][0];
        my $first_title   = $rows->[0][1];
        my $second_author = $rows->[1][0];
        my $second_value  = $rows->[1][1];
    }

fetch_all

すべての行をフェッチして配列の配列に格納します。

    my $rows = $result->fetch_all;

fetch_hash

一行フェッチしてハッシュに格納します。

    while (my $row = $result->fetch_hash) {
        my $title  = $row->{title};
        my $author = $row->{author};
    }

fetch_hash_first

一行だけフェッチしてハッシュに格納します。

    my $row = $result->fetch_hash_first;

フェッチが終わった後は、ステートメントハンドルからfinish() メソッドが呼び出されてそれ以上フェッチできなくなります。

fetch_hash_multi

複数行をフェッチしてハッシュの配列に格納します。

    while (my $rows = $result->fetch_hash_multi(5)) {
        my $first_title   = $rows->[0]{title};
        my $first_author  = $rows->[0]{author};
        my $second_title  = $rows->[1]{title};
        my $second_author = $rows->[1]{author};
    }

fetch_all

すべての行をフェッチしてハッシュの配列に格納します。

    my $rows = $result->fetch_hash_all;

DBIのステートメントハンドルに直接アクセスしたい場合は <sth>を使用します。

    my $sth = $result->sth;

4. ハッシュパラメタバインド

DBIx::Customはハッシュパラメタバインドを提供します。

まず最初にDBIによる通常のパラメタバインドをご覧ください。

    use DBI;
    my $dbh = DBI->connect(...);
    my $sth = $dbh->prepare(
        "select * from books where author = ? and title like ?;"
    );
    $sth->execute('Ken', '%Perl%');

これはデータベースシステムがSQLをキャッシュすることができ、 パラメータは自動的にクォートされるので、 パフォーマンス面でも、セキュリティ面でも とても良い方法です。

DBIx::Customはこれを改善して、ハッシュで パラメタを指定できるようにしました。

    my $result = $dbi->execute(
        "select * from books where {= author} and {like title};"
        param => {author => 'Ken', title => '%Perl%'}
    );

{= author}{like title}はタグと呼ばれます。 タグは内部ではプレースホルダを含む文字列に置き換えられます。

    select * from books where {= author} and {like title}

という文は以下のSQLに置き換えられます。

    select * from books where author = ? and title like ?;

このようにタグを使ってSQL文を表現するのがDBIx::Customの 特徴です。以下のタグが利用可能です。

    [TAG]                       [REPLACED]
    {? NAME}               ->   ?
    {= NAME}               ->   NAME = ?
    {<> NAME}              ->   NAME <> ?
    
    {< NAME}               ->   NAME < ?
    {> NAME}               ->   NAME > ?
    {>= NAME}              ->   NAME >= ?
    {<= NAME}              ->   NAME <= ?
    
    {like NAME}            ->   NAME like ?
    {in NAME COUNT}        ->   NAME in [?, ?, ..]
    
    {insert_param NAME1 NAME2}   ->   (NAME1, NAME2) values (?, ?)
    {update_param NAME1 NAME2}   ->   set NAME1 = ?, NAME2 = ?

これらの変換はDBIx::Custom::QueryBuilderによって行われます。

{}は予約語です。これらの文字を使いたい場合は 「\」を使ってエスケープする必要があります。 '\'はPerlのエスケープ文字なので、 エスケープするためには'\\'と書く必要があることに注意 してください。

    'select * from books \\{ something statement \\}'

5. フィルタリング

パラメタバインド時のフィルタリング

データベースに登録するデータをフィルタリングしたい場合 があります。たとえば、内部文字列で文字列を保持している場合は データベースにデータを登録する前に、バイト文字列に変換する 必要があります。DBIx::Customのフィルタリングシステムは あるデータを他のデータに変換するのを手助けしてくれます。

フィルタリングを利用するにはまず、 register_filter()メソッドを使用して フィルタを登録しておく必要があります。

    $dbi->register_filter(
        to_upper_case => sub {
            my $value = shift;
            return uc $value;
        }
    );

デフォルトのフィルタとしてencode_utf8decode_utf8 が登録されています。

登録されているフィルタはexecute()メソッドのfilterオプション で指定することができます。

    my $result = $dbi->execute(
        "select * from books where {= author} and {like title};"
        param  => {author => 'Ken', title => '%Perl%'},
        filter => {author => 'to_upper_case, title => 'encode_utf8'}
    );

この例ではauthorの値はバインドされるときに大文字に変換され、 titleの値はバイト文字列に変換されます。

filterオプションは insert()update()update_all(), delete()select() メソッドにおいても使用することができます。

    # insert() with filter option
    $dbi->insert(table  => 'books',
                 param  => {title => 'Perl', author => 'Ken'},
                 filter => {title => 'encode_utf8'});
    
    # select() with filter option
    my $result = $dbi->select(
        table  => 'books',
        column => [qw/author title/],
        where  => {author => 'Ken'},
        append => 'order by id limit 1',
        filter => {title => 'encode_utf8'}
    );

filterで指定されたフィルタはそれぞれのパラメータの値に働きますが、 すべてのパラメータのためのデフォルトのフィルタを設定することもできます。

    $dbi->default_bind_filter('encode_utf8');

filter()オプションで指定されたフィルタは、 デフォルトのフィルタを上書きます。

    $dbi->default_bind_filter('encode_utf8');
    $dbi->insert(
        table  => 'books',
        param  => {title => 'Perl', author => 'Ken', price => 1000},
        filter => {author => 'to_upper_case', price => undef}
    );

titleの値にはデフォルトのフィルタであるencode_utf8が適用され authorの値にはto_upper_caseが適用されます。 priceにはundefを設定しているのでフィルタが解除されます。

フィルタのサンプル

MySQL

    # Time::Piece object to DATETIME format
    tp_to_datetime => sub {
        return shift->strftime('%Y-%m-%d %H:%M:%S');
    }
    
    # Time::Piece object to DATE format
    tp_to_date => sub {
        return shift->strftime('%Y-%m-%d');
    }
    
    # DATETIME to Time::Piece object
    datetime_to_tp => sub {
        return Time::Piece->strptime(shift, '%Y-%m-%d %H:%M:%S');
    }
    
    # DATE to Time::Piece object
    date_to_tp => sub {
        return Time::Piece->strptime(shift, '%Y-%m-%d');
    }

SQLite

    # Time::Piece object to DATETIME format
    tp_to_datetime => sub {
        return shift->strftime('%Y-%m-%d %H:%M:%S');
    }
    
    # Time::Piece object to DATE format
    tp_to_date => sub {
        return shift->strftime('%Y-%m-%d');
    }
    
    # DATETIME to Time::Piece object
    datetime_to_tp => sub {
        return Time::Piece->strptime(shift, $FORMATS->{db_datetime});
    }
    
    # DATE to Time::Piece object
    date_to_tp => sub {
        return Time::Piece->strptime(shift, $FORMATS->{db_date});
    }

行のフェッチ時のフィルタリング

行をフェッチするときのフィルタも設定することができます。 これはDBIx::Custom::Resultクラスのfilterメソッドを使って 行います。

    my $result = $dbi->select(table => 'books');
    $result->filter({title => 'decode_utf8', author => 'to_upper_case'});

フィルタはそれぞれの列の値に作用しますが、 すべての列のためのデフォルのトフィルタを設定することもできます。

    $dbi->default_fetch_filter('decode_utf8');

filterメソッドで設定されたフィルタはデフォルトのフィルタを上書きます。

    $dbi->default_fetch_filter('decode_utf8');
    my $result = $dbi->select(
        table => 'books',
        columns => ['title', 'author', 'price']
    );
    $result->filter({author => 'to_upper_case', price => undef});

titleの値にはデフォルトのフィルタであるdecode_utf8が適用され authorの値にはto_upper_caseが適用されます。 priceにはundefを設定しているのでフィルタが解除されます。

フェッチのためのフィルタにおいて、 たとえ、列名が大文字を含む場合であっても 列名は小文字であることに注意してください。 これはデータベースシステムに依存させないための要件です。

6. パフォーマンスの改善

フィルタのチェックを無効にする

フィルタのチェックがデフォルトでは有効になっています。 これは正しいフィルタ名が指定されているかどうかをチェック するためのものですが、ときにはパフォーマンスに影響を 及ぼす可能性があります。

フィルタのチェックを無効にするにはfilter_check に0を設定してください。

    $dbi->filter_check(0);

シュガーメソッドを使わない

もしinsert()メソッドを使用してインサートを実行した場合、 必要なパフォーマンスを得られない場合があるかもしれません。 insert()メソッドは、SQL文とステートメントハンドルを 毎回作成するためすこし遅いです。

そのような場合は、create_query()メソッドによって クエリを用意しておくことができます。

    my $query = $dbi->create_query(
        "insert into books {insert_param title author};"
    );

戻り値はDBIx::Custom::Queryオブジェクトです。 このオブジェクトはSQL文とパラメータバインド時の列名を 保持しています。またステートメントハンドルも保持しています。

    {
        sql     => 'insert into books (title, author) values (?, ?);',
        columns => ['title', 'author'],
        sth     => $sth
    }

クエリオブジェクトを使って繰り返し実行するには次のようにします。

    my $inputs = [
        {title => 'Perl',      author => 'Ken'},
        {title => 'Good days', author => 'Mike'}
    ];
    
    foreach my $input (@$inputs) {
        $dbi->execute($query, $input);
    }

executeメソッドの第一引数にクエリオブジェトを渡すことができます。 これはinsert()メソッドよりも高速です。

キャッシング

execute()メソッドはデフォルトでSQL文への解析結果をキャッシュします。

    $dbi->cache(1);

キャッシングはメモリ上で行われますが、cache_method() を使って変更することができます。これはキャッシュされるデータの保存と取得 のためのメソッドを定義するのに利用されます。

    $dbi->cache_method(sub {
        sub {
            my $self = shift;
            
            $self->{_cached} ||= {};
            
            # Set cache
            if (@_ > 1) {
                $self->{_cached}{$_[0]} = $_[1] 
            }
            
            # Get cache
            else {
                return $self->{_cached}{$_[0]}
            }
        }
    });

最初の引数はDBIx::Customです。第二引数は、 "select * from books where {= title} and {= author};"; のようなSQL文の源です。 第三引数は {sql => "select * from books where title = ? and author = ?", columns => ['title', 'author']} のような解析後のデータです。これはハッシュリファレンスである必要 があります。 もし引数がふたつより大きい場合は、このメソッドはキャッシュを保存 するのに利用されます。そうでない場合はキャッシュを取得するのに 利用されます。

7. その他の機能

トランザクション

トランザクションを便利に利用するために、 begin_work()commit()rollback() という三つのメソッドが容易されています。 これはDBIの同名のメソッドと同じ機能を持ちます。

    $dbi->begin_work;
    
    eval {
        $dbi->update(...);
        $dbi->update(...);
    };
    
    if ($@) {
        $dbi->rollback;
    }
    else {
        $dbi->commit;
    }

selectメソッドの結果クラスの変更

必要ならばselect()メソッドの結果クラスを変更することができます。

    package Your::Result;
    use base 'DBIx::Custom::Result';
    
    sub some_method { ... }

    1;
    
    package main;
    
    use Your::Result;
    
    my $dbi = DBIx::Custom->connect(...);
    $dbi->result_class('Your::Result');

DBIx::Custom::QueryBuilderの機能の拡張

新しいタグが欲しい場合はDBIx::Custom::QueryBuilderの機能を拡張 することができます。

    my $dbi = DBIx::Custom->connect(...);
    $dbi->query_builder->register_tag_processor(
        name => sub {
           ...
        }
    );

ヘルパーメソッドの登録

ヘルパーメソッドを登録することができます。

    $dbi->helper(
        update_or_insert => sub {
            my $self = shift;
            # do something
        },
        find_or_create   => sub {
            my $self = shift;
            # do something
        }
    );

<helper()>メソッドで登録したメソッドは DBIx::Customオブジェクトから直接呼び出すことができます。

    $dbi->update_or_insert;
    $dbi->find_or_create;

ユーティリティメソッド(実験的)

expandメソッドを使用すると次のようなハッシュに含まれる テーブル名と列名を結合することができます。

    my %expanded = $dbi->expand(\%source);

以下のハッシュ

    {books => {title => 'Perl', author => 'Ken'}}

は次のように展開されます。

    ('books.title' => 'Perl', 'books.author' => 'Ken')

これはテーブル名を含むselect文で利用すると便利です。

    my $param = {title => 'Perl', author => '%Ken%'};
    $dbi->execute(
        'select * from books where {= books.title} && {like books.author};',
        param => {$dbi->expand({books => $param})}
    );