Go to Qiita Advent Calendar 2024 Top
LoginSignup
20
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

Perl 5Advent Calendar 2015

Day 8
@kfly8(小林 謙太)in株式会社モバイルファクトリー

Aniki クイックスタート

Last updated at Posted at 2015-12-08

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

はじめに

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

環境の準備

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

$ 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さんです!

20
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
20
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?