Aniki クイックスタート

  • 16
    いいね
  • 0
    コメント

この記事は Perl5 Advent Calendar 2015 の8日目の記事です.
昨日は、 @songmuさんの Perl と Redis でした.

はじめに

今回は、Perl5 の O/R Mapper の Aniki について紹介したいと思います.
Aniki の特徴については、作者の @karupanerura1日目の記事 を参照してみてください.

環境の準備

Aniki は、perl 5.14 以降で動作します. ここでは、v5.22.0 を利用します.

$ perl -V:version  # => version='5.22.0'

Aniki をインストールします. ここでは、v0.84 に関する説明とします.

$ cpanm Aniki

RDBMS の準備をします. ここでは、MySQL 5.6 を利用します.

$ mysql -V
$ cpanm DBD::mysql

Schema の準備

$ mkdir -p aniki-sample
$ cd aniki-sample

install-aniki で、Anikiの雛形が用意できます.

$ install-aniki --lib=./lib Sample::DB
# =>
  Creating Sample::DB ... done
  lib/Sample/DB.pm syntax OK
  Creating Sample::DB::Schema ... done
  lib/Sample/DB/Schema.pm syntax OK
  Creating Sample::DB::Filter ... done
  lib/Sample/DB/Filter.pm syntax OK
  Creating Sample::DB::Result ... done
  lib/Sample/DB/Result.pm syntax OK
  Creating Sample::DB::Row ... done
  lib/Sample/DB/Row.pm syntax OK

Sample::DB::Schema を編集してみます.

Schema.pm
package Sample::DB::Schema;
use 5.014002;

use DBIx::Schema::DSL;

create_table 'user' => columns {
    integer 'id', primary_key, auto_increment;
    varchar 'name';
};

create_table 'friend' => columns {
    integer 'id', primary_key, auto_increment;
    integer 'user_id';
    integer 'another_user_id';

    belongs_to 'user';
    fk 'another_user_id' => 'user' => 'id';
};

1;

mysql に schema を流し込みます.

Sample::DB::Schema で外部キー制約を定義しましたが、no_fk_output で外部キー制約の ない schema を流し混んでいます.
Aniki の Relationship を利用するために、RDBMS側に外部キーが定義されている必要はありません.

$ mysqladmin -uroot create aniki_sample
$ perl -Ilib -MSample::DB::Schema -E 'say Sample::DB::Schema->no_fk_output' | mysql -uroot aniki_sample

データの挿入

適当なデータを挿入してみましょう.

insert.pl
use Sample::DB;
use DDP;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

p $db->insert(                 user => { name => 'foo' });
p $db->insert_and_fetch_id(    user => { name => 'bar' });
p $db->insert_and_fetch_row(   user => { name => 'baz' });
p $db->insert_and_emulate_row( user => { name => 'baz' });
$ perl -Ilib insert.pl
# =>
undef
2
Sample::DB::Row  {
    Parents       Aniki::Row
    public methods (1) : meta
    private methods (0)
    internals: {
        is_new       1,
        relay_data   {},
        row_data     {
            id     3,
            name   "baz"
        },
        table_name   "user"
    }
}
Sample::DB::Row  {
    Parents       Aniki::Row
    public methods (1) : meta
    private methods (0)
    internals: {
        is_new       1,
        relay_data   {},
        row_data     {
            id     4,
            name   "boo"
        },
        table_name   "user"
    }
}

以下、各挿入方法の違いを確かめる為、DBIx::QueryLog を用いて、発行されているクエリを見てみます.

$ cpanm DBIx::QueryLog
$ perl -Ilib -MDBIx::QueryLog insert.pl
# =>

[2015-12-08T13:18:21] [Aniki] [0.000803] INSERT INTO `user` (`name`) VALUES ('foo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
undef

[2015-12-08T13:18:21] [Aniki] [0.000319] INSERT INTO `user` (`name`) VALUES ('bar') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
6

[2015-12-08T13:18:21] [Aniki] [0.000343] INSERT INTO `user` (`name`) VALUES ('baz') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T13:18:21] [Aniki] [0.000179] SELECT `id`, `name` FROM `user` WHERE (`id` = 7) LIMIT 1 at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Row  {
    Parents       Aniki::Row
    public methods (1) : meta
    private methods (0)
    internals: {
        is_new       1,
        relay_data   {},
        row_data     {
            id     7,
            name   "baz"
        },
        table_name   "user"
    }
}

[2015-12-08T13:18:21] [Aniki] [0.000446] INSERT INTO `user` (`name`) VALUES ('boo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Row  {
    Parents       Aniki::Row
    public methods (1) : meta
    private methods (0)
    internals: {
        is_new       1,
        relay_data   {},
        row_data     {
            id     8,
            name   "boo"
        },
        table_name   "user"
    }
}
  • insert_and_fetch_id は、last_insert_id を取り出す分だけ、insert に違いがあります.
  • insert_and_fetch_row は、Row Object を生成するためのデータを、SELECT しています.
  • insert_and_emulate_row は、挿入されたデータから、Row Object を生成しています. SQLのTRIGGERなどを使っている場合、注意 が必要です.

つまり、Aniki の I/Fは、副作用がある場合は、明示するようになっています.
状況に応じて、使い分けします

データの検索

挿入してみたデータを検索してみましょう

select.pl
use Sample::DB;
use DDP;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

p $db->select(user => { name => 'foo' });
p $db->select_named('SELECT * FROM user WHERE name = :name', { name => 'bar' });
p $db->select_by_sql('SELECT * FROM user WHERE name LIKE "%oo"');
$ perl -Ilib -MDBIx::QueryLog select.pl

[2015-12-08T13:45:54] [Aniki] [0.000278] SELECT `id`, `name` FROM `user` WHERE (`name` = 'foo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Result  {
    Parents       Aniki::Result::Collection
    public methods (1) : meta
    private methods (0)
    internals: {
        row_datas              [
            [0] {
                id     1,
                name   "foo"
            },
            [1] {
                id     5,
                name   "foo"
            }
        ],
        suppress_row_objects   0,
        table_name             "user"
    }
}
[2015-12-08T13:45:54] [Aniki] [0.000239] SELECT * FROM user WHERE name = 'bar' at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Result  {
    Parents       Aniki::Result::Collection
    public methods (1) : meta
    private methods (0)
    internals: {
        row_datas              [
            [0] {
                id     2,
                name   "bar"
            },
            [1] {
                id     6,
                name   "bar"
            }
        ],
        suppress_row_objects   0,
        table_name             "user"
    }
}
[2015-12-08T13:45:54] [Aniki] [0.003963] SELECT * FROM user WHERE name LIKE "%oo" at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Result  {
    Parents       Aniki::Result::Collection
    public methods (1) : meta
    private methods (0)
    internals: {
        row_datas              [
            [0] {
                id     1,
                name   "foo"
            },
            [1] {
                id     4,
                name   "boo"
            },
            [2] {
                id     5,
                name   "foo"
            },
            [3] {
                id     8,
                name   "boo"
            }
        ],
        suppress_row_objects   0,
        table_name             "user"
    }
}

SELECT 文の組み立ては、SQL::Maker を用いて行っています.
詳しい検索オプションはそちらを参照すると良いです.
Aniki の特徴である Relationship に関しては、後述します.

データ更新/削除

更新も削除もどちらも変更のあった行数が返されます.
更新の場合、更新データ、更新条件を引数に渡し、
削除の場合、削除条件を渡します.

update-delete.pl
use Sample::DB;
use DDP;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

p $db->update( user => { name => 'booboo' }, { name => 'boo' });
p $db->select( user => { name => 'booboo' });

p $db->delete( user => { name => 'booboo' });
p $db->select( user => { name => 'booboo' });
$ perl -Ilib -MDBIx::QueryLog update_and_delete.pl

[2015-12-08T13:56:35] [Aniki] [0.005751] UPDATE `user` SET `name` = 'booboo' WHERE (`name` = 'boo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
2

[2015-12-08T13:56:35] [Aniki] [0.000251] SELECT `id`, `name` FROM `user` WHERE (`name` = 'booboo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Result  {
    Parents       Aniki::Result::Collection
    public methods (1) : meta
    private methods (0)
    internals: {
        row_datas              [
            [0] {
                id     4,
                name   "booboo"
            },
            [1] {
                id     8,
                name   "booboo"
            }
        ],
        suppress_row_objects   0,
        table_name             "user"
    }
}

[2015-12-08T13:56:35] [Aniki] [0.001418] DELETE FROM `user` WHERE (`name` = 'booboo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
2

[2015-12-08T13:56:35] [Aniki] [0.000192] SELECT `id`, `name` FROM `user` WHERE (`name` = 'booboo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
Sample::DB::Result  {
    Parents       Aniki::Result::Collection
    public methods (1) : meta
    private methods (0)
    internals: {
        row_datas              [],
        suppress_row_objects   0,
        table_name             "user"
    }
}

Relationship

ここから Aniki の最大の特徴である Relationship について紹介します.

複数テーブルに Relationship がある場合、下記のように IN句で取り出すコードを書く場面があると思います.
これは、Aniki の Relationship の原始コードです.

my @friends    = SELECT * FROM friend;
my @user_ids   = map { $_->user_id } @friends;
my @users      = SELECT * FROM user IN (@user_ids);
my %user_map   = map { $_->id => $_ } @users;
for my $friend (@friends) {
   $friend->{relay_data}->{user} = $user_map{$friend->user_id}
}

Aniki は、Schema に Relationship を記述すれば、
上記のようなことを直感的に実現できます.

ひとまず、下準備で、foo と、bar,baz を friend にしてみます.

insert-friend.pl
use Sample::DB;
use DDP;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

my $foo = $db->select(user => { name => 'foo' }, { limit => 1 })->first;
my $bar = $db->select(user => { name => 'bar' }, { limit => 1 })->first;
my $baz = $db->select(user => { name => 'baz' }, { limit => 1 })->first;

insert_friend($db, $foo, $bar);
insert_friend($db, $foo, $baz);

sub insert_friend {
    my ($db, $user, $another) = @_;
    my $txn = $db->txn_manager->txn_scope;
    $db->insert(friend => { user_id => $user->id,    another_user_id => $another->id });
    $db->insert(friend => { user_id => $another->id, another_user_id => $user->id });
    $txn->commit;
}

どう Relationship が実現されているか見てみます

relationship.pl
use Test::More;
use Sample::DB;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

subtest 'foo friends' => sub {
    my $foo = $db->select(user => { name => 'foo' })->first;
    my $foo_friends = $db->select(friend => { user_id => $foo->id });

    is $foo_friends->count, 2;
    is $foo_friends->first->user->name,         'foo';
    is $foo_friends->first->another_user->name, 'bar';
    is $foo_friends->last->user->name,          'foo';
    is $foo_friends->last->another_user->name,  'baz';
};

done_testing;
$ perl -Ilib -MDBIx::QueryLog relationship.pl
    # Subtest: foo friends
[2015-12-08T15:25:15] [Aniki] [0.000264] SELECT `id`, `name` FROM `user` WHERE (`name` = 'foo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T15:25:15] [Aniki] [0.000183] SELECT `id`, `user_id`, `another_user_id` FROM `friend` WHERE (`user_id` = 1) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 1
[2015-12-08T15:25:15] [Aniki] [0.000177] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('1')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 2
[2015-12-08T15:25:15] [Aniki] [0.000133] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('2')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 3
[2015-12-08T15:25:15] [Aniki] [0.000126] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('1')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 4
[2015-12-08T15:25:15] [Aniki] [0.000124] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('3')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 5
    1..5
ok 1 - foo friends
1..1

上記のように、 friend->user, friend->another_user のようにして、friend の Relationship の user が利用できます.
デフォルトだと、クエリは都度発行されていますが、prefetch で効率よく発行できます.

use Test::More;
use Sample::DB;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

subtest 'foo friends' => sub {
    my $foo = $db->select(user => { name => 'foo' })->first;
    my $foo_friends = $db->select(friend => { user_id => $foo->id }, { prefetch => [qw/user another_user/] }); # prefetch user and another_user

    is $foo_friends->count, 2;
    is $foo_friends->first->user->name,         'foo';
    is $foo_friends->first->another_user->name, 'bar';
    is $foo_friends->last->user->name,          'foo';
    is $foo_friends->last->another_user->name,  'baz';
};

done_testing;
 perl -Ilib -MDBIx::QueryLog relationship-prefetch.pl
    # Subtest: foo friends
[2015-12-08T15:27:26] [Aniki] [0.000375] SELECT `id`, `name` FROM `user` WHERE (`name` = 'foo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T15:27:26] [Aniki] [0.000253] SELECT `id`, `user_id`, `another_user_id` FROM `friend` WHERE (`user_id` = 1) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T15:27:26] [Aniki] [0.000243] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('1','1')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T15:27:26] [Aniki] [0.000139] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('2','3')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 1
    ok 2
    ok 3
    ok 4
    ok 5
    1..5
ok 1 - foo friends
1..1

prefetch について、もう少し詳しくみてみます. user_score テーブルを追加します

Sample.pm
package Sample::DB::Schema;
use 5.014002;

use DBIx::Schema::DSL;

create_table 'user' => columns {
    integer 'id', primary_key, auto_increment;
    varchar 'name';
};

create_table 'user_score' => columns {
    integer 'user_id', primary_key;
    integer 'score';

    belongs_to 'user';
};

create_table 'friend' => columns {
    integer 'id', primary_key, auto_increment;
    integer 'user_id';
    integer 'another_user_id';

    belongs_to 'user';
    fk 'another_user_id' => 'user' => 'id';
};

1;
relationship-deep-prefetch.pl
use Test::More;
use Sample::DB;

my $db = Sample::DB->new(connect_info => ['dbi:mysql:dbname=aniki_sample', 'root', '']);

subtest 'foo friends' => sub {
    my $foo = $db->select(user => { name => 'foo' })->first;
    my $foo_friends = $db->select(friend => { user_id => $foo->id }, {
        prefetch => [{ user => [qw/user_score/] }, { another_user => [qw/user_score/]}] # prefetch!!
    });

    is $foo_friends->count, 2;
    is $foo_friends->first->user->name,         'foo';
    is $foo_friends->first->another_user->name, 'bar';
    is $foo_friends->last->user->name,          'foo';
    is $foo_friends->last->another_user->name,  'baz';

    is $foo_friends->first->user->user_score,         undef;
    is $foo_friends->first->another_user->user_score, undef;
};

done_testing;
$ perl -Ilib -MDBIx::QueryLog relationship-deep-prefetch.pl

    # Subtest: foo friends
[2015-12-08T16:42:12] [Aniki] [0.000261] SELECT `id`, `name` FROM `user` WHERE (`name` = 'foo') at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T16:42:12] [Aniki] [0.000187] SELECT `id`, `user_id`, `another_user_id` FROM `friend` WHERE (`user_id` = 1) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T16:42:12] [Aniki] [0.000150] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('1','1')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T16:42:12] [Aniki] [0.000113] SELECT `user_id`, `score` FROM `user_score` WHERE (`user_id` IN ('1')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T16:42:12] [Aniki] [0.000143] SELECT `id`, `name` FROM `user` WHERE (`id` IN ('2','3')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
[2015-12-08T16:42:12] [Aniki] [0.000122] SELECT `user_id`, `score` FROM `user_score` WHERE (`user_id` IN ('2','3')) at /Users/kfly8/.anyenv/envs/plenv/versions/5.22.0/lib/perl5/site_perl/5.22.0/Aniki.pm line 515
    ok 1
    ok 2
    ok 3
    ok 4
    ok 5
    ok 6
    ok 7
    1..7
ok 1 - foo friends
1..1

user_score まで prefetch していることがわかります.
つまり、prefetch は、relationship を再帰的に探索します.

  select(foo => {}, {
    prefetch => [{ bar => { baz => [qw/boo/] } }]
  })
  # =>
  # prefetch `bar` of foo relationship &
  # prefetch `baz` of bar relationship &
  # prefetch `boo` of baz relationship

prefetch に指定する relationship.name は、 基本、Aniki がSchema定義から類推してくれます. 例えば、以下のルールで、another_user が指定されました.

    fk 'another_user_id' => 'user' => 'id';

割り当て内容は、以下のように schema 情報にアクセスして確認できます:p

perl -Ilib -MSample::DB -MDDP -e 'p(Sample::DB->schema->get_table('friend')->relationships->rule)'

まとめ

  • Aniki はたよれるブラザー

明日は、 @shogo82148さんです!

この投稿は Perl 5 Advent Calendar 20158日目の記事です。