Aniki クイックスタート


この記事は [Perl5 Advent Calendar 2015](http://qiita.com/advent-calendar/2015/perl5) の8日目の記事です.
昨日は、 [@songmu](https://twitter.com/songmu)さんの [Perl と Redis](http://www.songmu.jp/riji/entry/2015-12-07-perl5-advent-calendar-redis.html) でした.

# はじめに

今回は、Perl5 の O/R Mapper の [Aniki](https://metacpan.org/pod/Aniki) について紹介したいと思います.
Aniki の特徴については、作者の [@karupanerura](https://twitter.com/karupanerura) の [1日目の記事](http://qiita.com/karupanerura/items/cf5c3a11ec823c8c4088) を参照してみてください.

# 環境の準備

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

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

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

```shell
$ cpanm Aniki
```

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

```shell
$ mysql -V
$ cpanm DBD::mysql
```

# Schema の準備

```shell
$ 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` を編集してみます.


```perl: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側に外部キーが定義されている必要はありません.

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

# データの挿入

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

```perl: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 => 'boo' });
```

```shell
$ 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などを使っている場合、[注意](https://metacpan.org/pod/Aniki#WARNING) が必要です.

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

# データの検索

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

```perl: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"');
```

```shell
$ 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](https://metacpan.org/pod/SQL%3A%3AMaker) を用いて行っています.
詳しい検索オプションはそちらを参照すると良いです.
Aniki の特徴である Relationship に関しては、後述します.

# データ更新/削除


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


```perl: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' });
```

```shell
$ 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 の原始コードです.

```perl
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 にしてみます.

```perl: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 が実現されているか見てみます

```perl: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;
```

```shell
$ 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 で効率よく発行できます.

```perl
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;
```

```shell
 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` テーブルを追加します


```perl: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;
```

```perl: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 を再帰的に探索します.

```perl
  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` が指定されました.

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

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

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

# まとめ

- Aniki はたよれるブラザー


明日は、 [@shogo82148](https://twitter.com/shogo82148)さんです!