introducing

DBIx::ObjectMapper

2010-10-16 YAPC::Asia Tokyo

自己紹介

Eisuke Oishi (大石英介)

PerlのORM

PerlのORM

Class::DBI、ActiveRecordの影響が大きい?

たぶん全部Active Recordパターン

開発動機

ORM使ってはいるものの、不満が多い

「The Vietnam of Computer Science(コンピューターサイエンスのベトナム戦争)」とか「one of the three usual cardinal sins of the aspiring perl programmer(野心に燃えるPerlプログラマの通常やってはいけない3つの大罪のうち1つ) 」っていわれてるけど、とりあえずやってみた

2009年12月頃開発開始

DBIx::ObjectMapperとは?

DBIx::ObjectMapperとは?

O/R マッパー

DBIx::ObjectMapperとは?

O/R マッパー

Data Mapper Pattern

DBIx::ObjectMapperとは?

O/R マッパー

Data Mapper Pattern

SQLAlchemyからいろいろ盗んでいる

Data Mapper Patternとは?

Data Mapper Patternとは?

Active Recordパターン

Data Mapper Patternとは?

Data Mapper Pattern

Active Recordパターンをより抽象化したものとも考えられる

Data Mapper Patternとは?

Data Mapper Patternとは?

Data Mapper Patternとは?

Data Mapper Patternとは?

Data Mapper Patternとは?

準備

            use DBIx::ObjectMapper;
            use DBIx::ObjectMapper::Engine::DBI;
            my $mapper = DBIx::ObjectMapper->new(
                engine => DBIx::ObjectMapper::Engine::DBI->new({
                    dsn      => 'DBI:SQLite:',
                    username => undef,
                    password => undef,
                }),
            );
          

準備

            CREATE TABLE users (
              id   INTEGER NOT NULL PRIMARY KEY,
              name TEXT
            );
          
            package User;
            use Any::Moose;

            has 'id'   => ( is => 'rw', isa => 'Int' );
            has 'name' => ( is => 'rw', isa => 'Str' );

            __PACKAGE__->meta->make_immutable;
          

準備

            my $user_table = $mapper->metadata->table(
               'users' => 'autoload'
            );

            $mapper->maps( $user_table => 'User' );
          

準備おわり

INSERT

            my $session = $mapper->begin_session( autocommit => 0 );

            my $user = User->new( name => 'username' );
            $session->add($user);
            $session->commit;
            # BEGIN;
            # INSERT INTO users ( name ) VALUES ('username');
            # COMMIT;
          

Get

Primary Keyからロードする

            my $session = $mapper->begin_session( autocommit => 0 );
            my $user = $session->get( 'User' => 1 );
            # SELECT users.id, users.name FROM users WHERE ( users.id = 1 );
            $user->id;
            $user->title;
          

UPADTE

            my $session = $mapper->begin_session( autocommit => 0 );
            my $user = $session->get( 'User' => 1 );
            # SELECT users.id, users.name FROM users WHERE ( users.id = 1 );
            $user->name('名前');
            $session->commit;
            # BEGIN;
            # UPDATE users SET name = '名前' WHERE ( users.id = 1 );
            # COMMIT;
          

DELETE

            my $session = $mapper->begin_session( autocommit => 0 );
            my $user = $session->get( 'User' => 1 );
            # SELECT users.id, users.name FROM users WHERE ( users.id = 1 );
            $session->delete($user);
            $session->commit;
            # BEGIN;
            # DELETE FROM users WHERE ( users.id = 1 );
            # COMMIT;
          

Sessionとは?

Sessionとは?

Mapperへの通話手段

データベースへの操作はSessionを経由してMapperに伝える

            my $session = $mapper->begin_session;
            $session->add($obj);
            $session->get( 'ClassName' => 'primary key' );
            $session->delete($obj);
          

Sessionとは?

データベースへ書き込み、更新を行う単位(トランザクション)



flush, autoflush



commit, autocommit

Sessionとは?

データベースへ書き込み、更新を行う単位(トランザクション)



デフォルトの動作

            my $session = $mapper->begin_session(
                # autocommit => 1,
                # autoflush  => 0,
            );
            $obj->name('なまえ');
            $session->add($obj2);
            $session->delete($obj3);

            undef($session);
            # UPDATE ...
            # INSERT ...
            # DELETE ...
          

Sessionとは?

データベースへ書き込み、更新を行う単位(トランザクション)



autocommit = 0

            my $session = $mapper->begin_session(
                autocommit => 0,
                autoflush  => 0,
            );

            # BEGIN;
            $obj->name('なまえ');
            $session->add($obj2);
            $session->delete($obj3);

            undef($session);
            # UPDATE ...
            # INSERT ...
            # DELETE ...
            # ROLLBACK;
          

Sessionとは?

データベースへ書き込み、更新を行う単位(トランザクション)



autocommit = 0

            my $session = $mapper->begin_session(
                autocommit => 0,
                autoflush  => 0,
            );

            # BEGIN;
            $obj->name('なまえ');
            $session->add($obj2);
            $session->delete($obj3);

            $session->commit;
            # UPDATE ...
            # INSERT ...
            # DELETE ...
            # COMMIT;
            undef($session);
          

Sessionとは?

データベースへ書き込み、更新を行う単位(トランザクション)



autocommit = 0, 任意にflushする

            my $session = $mapper->begin_session(
                autocommit => 0,
                autoflush  => 0,
            );

            # BEGIN;
            $obj->name('なまえ');
            $session->add($obj2);
            $session->delete($obj3);
            $session->flush;
            # UPDATE ...
            # INSERT ...
            # DELETE ...

            $session->commit;
            # COMMIT;
            undef($session);
          

Sessionとは?

キャッシュの有効範囲( no_cache, share_object )

Sessionの初期値をあらかじめ設定しておく

            use DBIx::ObjectMapper;
            use DBIx::ObjectMapper::Engine::DBI;
            my $mapper = DBIx::ObjectMapper->new(
                engine => DBIx::ObjectMapper::Engine::DBI->new({
                    dsn      => 'DBI:SQLite:',
                    username => undef,
                    password => undef,
                }),
                session_attr => {
                    autocommit => 0,
                    no_cache   => 1,
                },
            );


            my $session = $mapper->begin_session();
            # autocommit => 0, no_cache => 1
          

Search: 複数のオブジェクトを取得

            my $session = $mapper->begin_session( autocommit => 1 );

            my $attr = $mapper->attribute('User');
            my $it = $session->search('User')
                             ->filter( $attr->prop('id') > 10 )
                             ->execute;

            while( my $user = $it->next ) {
            # SELECT users.id, users.name FROM users WHERE ( users.id > 10 ) ORDER BY users.id;
                 $user->id;
                 $user->name;
            }
          

Search: 複数のオブジェクトを取得


  1. attributeを取得
  2. propertyオブジェトを比較
            my $attr = $mapper->attribute('User');
            $attr->prop('id') == 1;         # => users.id = 1
            $attr->prop('id') > 1;          # => users.id > 1
            $attr->prop('id') >= 1;         # => users.id >= 1
            $attr->prop('id') < 1;          # => users.id < 1
            $attr->prop('id') <= 1;         # => users.id <= 1
            $attr->prop('id') != 1;         # => users.id <> 1
            $attr->prop('id') == undef;     # => users.id IS NULL
            $attr->prop('id') != undef;     # => users.id IS NOT NULL
            $attr->prop('id') == [1,2,3,4]; # => users.id IN (1,2,3,4)

            $attr->prop('id')->between( 1,4 ); # => users.id BETWEEN 1 AND 4
            $attr->prop('name')->like( '%name%' );# => users.name LIKE '%name%'
            $attr->prop('name')->not_like( '%name%' );# => users.name NOT LIKE '%name%'

            $attr->prop('name')->op( '%%', 'あ' );# => users.name %% 'あ'
          

Search: 複数のオブジェクトを取得


AND, ORの定義


AND

            my $it = $session->search('User')
                   ->filter(
                       $attr->prop('id') > 10,
                       $attr->prop('name')->like('%name%'),
                   );
            # id > 10 AND name LIKE '%name%'
          

OR

            my $it = $session->search('User')
                   ->filter(
                       {
                           OR => [
                               $attr->prop('id') > 10,
                               $attr->prop('name')->like('%name%'),
                           ],
                       },
                   );
            # id > 10 OR name LIKE '%name%'
          

Search: 複数のオブジェクトを取得

メソッドチェーン

            my $session = $mapper->begin_session( autocommit => 1 );

            my $attr = $mapper->attribute('User');
            my $id = $attr->prop('id');
            my $name = $attr->prop('name');

            my $query = $session->search('User')
              ->filter( $id == [1,2,3,4] )
              ->order_by( $id );

            $query->add_filter( $name->like('%name%') );
                  ->add_order_by( $name->desc );

            my $it = $query->execute;

            while( my $user = $it->next ) {
                 # .....
            }
          

Arel?

Search: 複数のオブジェクトを取得

メソッドチェーン


メソッド



終端メソッド

Search: 複数のオブジェクトを取得

Pager

            my $session = $mapper->begin_session( autocommit => 1 );
            my $attr = $mapper->attribute('User');

            my $query = $session->search('User')
                                ->limit(10)
                                ->order_by( $attr->prop('id') );

            my ( $it, $pager ) = $query->page(1);
            # ref($pager) eq 'Data::Page';

            while( my $user = $it->next ) {
                # ....
            }

          

Relationship

has_many

            CREATE TABLE addresses (
              id INTEGER NOT NULL PRIMARY KEY,
              user_id INTEGER NOT NULL REFERENCES users(id),
              address TEXT
            );
          
            package Address;
            use Any::Moose;

            has 'id'      => ( is => 'rw', isa => 'Int' );
            has 'user_id' => ( is => 'rw', isa => 'Int' );
            has 'address' => ( is => 'rw' );

            __PACKAGE__->meta->make_immutable;
          

Relationship

has_many

            package User;
            use Any::Moose;

            has 'id'        => ( is => 'rw', isa => 'Int' );
            has 'name'      => ( is => 'rw', isa => 'Str' );
            has 'addresses' => ( is => 'rw', isa => 'Maybe[ArrayRef[Address]]' );

            __PACKAGE__->meta->make_immutable;
          
            my $address_table = $mapper->metadata->table(
                'addresses' => 'autoload' );
            $mapper->maps( $address_table => 'Address' );
            $mapper->maps(
                $user_table => 'User',
                attributes  => {
                    properties => {
                        addresses => {
                            isa => $mapper->relation(
                                has_many => 'Address',
                                { order_by => $address_table->c('id') }
                            ),
                        }
                    }
                }
            );
          

Relationship

has_many

            my $session = $mapper->begin_session( autocommit => 0 );
            my $user = $session->get( 'User' => 1 );
            # SELECT users.id, users.title FROM users WHERE users.id = 1;

            my $addresses = $user->addresses;
            # SELECT addresses.id, addresses.user_id, addresses.address FROM addresses WHERE addresses.user_id = 1 ORDER BY addresses.id
            for my $address ( @$addresses ) {
               $address->address;
            }
          

Relationship

belongs_to

            package Address;
            use Any::Moose;

            has 'id'      => ( is => 'rw', isa => 'Int' );
            has 'user_id' => ( is => 'rw', isa => 'Int' );
            has 'address' => ( is => 'rw' );

            has 'user'    => ( is => 'rw', isa => 'User' );

            __PACKAGE__->meta->make_immutable;
          
            my $address_table = $mapper->metadata->table('addresses');
            $mapper->maps(
                $address_table => 'Address',
                attributes => {
                    properties => {
                        user => {
                            isa => $mapper->relation(
                               'belongs_to' => 'User'
                            ),
                        }
                    }
                },
            );
          

Relationship

belongs_to

            my $session = $mapper->begin_session( autocommit => 0 );
            my $address = $session->get( 'Address' => 1 );
            # SELECT address.id, address.address, address.user_id FROM address WHERE address.id = 1;

            $address->user_id;
            $address->user;
            # SELECT users.id, users.name FROM users WHERE users.id = 1
            $address->user->name;
          

Relationship

has_one

            CREATE TABLE profile (
              id INTEGER NOT NULL PRIMARY KEY REFERENCES users(id),
              body_text TEXT
            );
          
            package Profile;
            use Any::Moose;

            has 'id'        => ( is => 'rw', isa => 'Int' );
            has 'body_text' => ( is => 'rw' );

            __PACKAGE__->meta->make_immutable;
          
            package User;
            use Any::Moose;

            has 'id'        => ( is => 'rw', isa => 'Int' );
            has 'name'      => ( is => 'rw', isa => 'Str' );
            has 'addresses' => ( is => 'rw', isa => 'Maybe[ArrayRef[Address]]' );
            has 'profile'   => ( is => 'rw', isa => 'Maybe[Profile]' );

            __PACKAGE__->meta->make_immutable;
          

Relationship

has_one

            my $profile_table = $mapper->metadata->t(
                'profile' => 'autoload'
            );
            $mapper->maps( $profile_table => 'Profile' );

            $mapper->maps(
                $user_table => 'User',
                attributes  => {
                    properties => {
                        addresses => {
                            isa => $mapper->relation(
                                has_many => 'Address',
                                { order_by => $address_table->c('id') }
                            ),
                        },
                        profile => {
                            isa => $mapper->relation(
                                has_one => 'Profile'
                            )
                        },
                    }
                }
            );
          

Relationship

many_to_many

            CREATE TABLE issues (
                id INTEGER NOT NULL PRIMARY KEY,
                title TEXT
            );

            CREATE TABLE user_issues (
                user_id INTEGER NOT NULL REFERENCES users(id),
                issue_id  INTEGER NOT NULL REFERENCES issues(id)
            );
          

Relationship

many_to_many

            my $issue_table = $mapper->metadata->t('issues' => 'autoload');
            my $user_issue_table = $mapper->metadata->t(
                'user_issues' => 'autoload');

            $mapper->maps(
                $issue_table => 'Issue',
                constructor => { auto => 1 },
                accessors   => { auto => 1 },
            ); # 自動でIssueクラスが作成される

            $mapper->maps(
                $user_table => 'User',
                attributes  => {
                    properties => {
                        issues => {
                            isa => $mapper->relation(
                                many_to_many
                                  => $user_issue_table => 'Issue',
                            )
                        }
                    }
                }
            );
          

Relationship

many_to_many

            my $session = $mapper->begin_session( autocommit => 0 );
            my $user = $session->get( 'User' => 1 );
            my $issues = $user->issues;
            for my $issue (@$issues) {
                $issue->title;
            }

            push @$issues, Issue->new( title => 'new issue' );
            $session->commit;
            # INSERT INTO issues ( title ) VALUES ('new issue');
            # INSERT INTO user_issues ( issue_id, user_id ) VALUES (2,1)
          

Relationship

Self-relationship

            CREATE TABLE comments (
                id INTEGER NOT NULL PRIMARY KEY,
                comment TEXT,
                reply_to INTEGER REFERENCES comments(id)
            );
          
            my $comment_table = $mapper->metadata->table('comments');
            $mapper->maps(
                $comment_table => 'Comment',
                constructor => { auto => 1 },
                accessors => { auto => 1 },
                attributes => {
                    properties => {
                        replies => {
                            isa => $mapper->relation(
                                'has_many' => 'Comment'
                            ),
                        },
                        parent => {
                            isa => $mapper->relation(
                                'belongs_to' => 'Comment'
                            ),
                        }
                    }
                }
            );
          

Relationship

Self-relationship

            my $session = $mapper->begin_session( autocommit => 0 );
            my $first = Comment->new( comment => 'first comment' );
            $session->add($first);
            $session->commit;
            # INSERT INTO comments ( comment ) VALUES ('first comment');

            my $second = Comment->new(
                comment => 'second comment',
                reply_to => $first->id,
            );
            # SELECT comments.id, comments.comment, comments.reply_to FROM comments WHERE ( comments.id = 1 )

            $session->add($second);
            $session->commit;
            # INSERT INTO comments ( comment, reply_to ) VALUES ('second comment',1);

            for my $reply ( @{$first->replies} ) {
            # SELECT comments.comment, comments.reply_to, comments.id FROM comments WHERE ( comments.reply_to = 1 ) ORDER BY comments.id
                $reply->comment;
                $reply->parent->comment; # cache
            }
          

Relationship

Joinを使った問い合わせ

            my $session = $mapper->begin_session( autocommit => 0 );
            my $attr = $mapper->attribute('User');
            my $it = $session->search('User')->filter(
                $attr->prop('addresses.address')->like('東京都%') )->execute;

            while ( my $user = $it->next ) {
            # SELECT users.name, users.id FROM users LEFT OUTER JOIN address AS addresses ON ( addresses.user_id = users.id ) WHERE ( addresses.address LIKE '東京都%' ) GROUP BY users.name, users.id

            }
          

Relationを設定している属性にドット(.)をつけて、その先の属性を参照できる(addresses => Address => address )

ただし、Relation先のデータはとってこない

Relationship

Eager Loading

リレーションのデータまで一気に1回で取得


search

            my $attr = $mapper->attribute('User');
            my $it = $session->search('User')
                ->eagerload( $attr->prop('addresses') )
                ->execute;
            while( my $u = $it->next ) {
            # SELECT users.name, users.id, addresses.id, addresses.user_id, addresses.address FROM users LEFT OUTER JOIN address AS addresses ON ( addresses.user_id = users.id )
                $u->id;
                $u->addresses;
            }
          

get

            my $session = $mapper->begin_session( autocommit => 0 );
            $session->get( 'User' => 1, { eagerload => 'addresses' } );
            # SELECT users.name, users.id, addresses.id, addresses.user_id, addresses.address FROM users LEFT OUTER JOIN address AS addresses ON ( addresses.user_id = users.id ) WHERE ( users.id = ? )
          

mappingあれこれ

mapsの引数その1

constructor

            $mapper->maps(
                $table_metadata => 'ClassName',
                constructor => {
                    name => 'new',
                    arg_type => 'HASHREF',
                    auto => 0,
                }
            );
          

mappingあれこれ

mapsの引数その2

attributes

            $mapper->maps(
                $table_metadata => 'ClassName',
                attributes => {
                    prefix => '',
                    include => [],
                    exclude => [],
                    properties => +{},
                }
            );
          

mappingあれこれ

mapsの引数その2

attributes => properties

            $mapper->maps(
                $table_metadata => 'ClassName',
                attributes => {
                    properties => +{
                        col => {
                            isa   => $table_metadata->column('col'),
                            getter => 'col',
                            setter => 'col',
                            lazy  => 0,
                        }
                    },
                }
            );
          

mappingあれこれ

mapsの引数その3

accessors

            $mapper->maps(
                $table_metadata => 'ClassName',
                accessors => {
                    auto => 0,
                    do_replace => 0,
                    exclude => [],
                    generic_getter => '',
                    generic_setter => '',
                }
            )
          

mappingあれこれ

リストを要求するコンストラクタの場合

            package User;
            use strict;
            use warnings;
            use base qw(Class::Accessor::Fast);
            __PACAKGE__->mk_accessors(qw(id name));

            sub new {
                my $class = shift;
                my ( $id, $title ) = @_;
                bless {
                    id   => $id,
                    name => $name,
                }, $class;
            }

            1;
          
            $mapper->maps(
                $user_table => 'User',
                contstructor => { arg_type => 'ARRAY' },
                attributes => {
                    properties => [
                        { isa => $user_table->c('id') },
                        { isa => $user_table->c('name') },
                    ]
                }
            );
          

mappingあれこれ

Array Referenceを要求するコンストラクタの場合

            package User;
            use strict;
            use warnings;
            use base qw(Class::Accessor::Fast);
            __PACAKGE__->mk_accessors(qw(id name));

            sub new {
                my $class = shift;
                my ( $id, $title ) = @{$_[0]};
                bless {
                    id   => $id,
                    name => $name,
                }, $class;
            }

            1;
          
            $mapper->maps(
                $user_table => 'User',
                contstructor => { arg_type => 'ARRAYREF' },
                attributes => {
                    properties => [
                        { isa => $user_table->c('id') },
                        { isa => $user_table->c('name') },
                    ]
                }
            );
          

mappingあれこれ

CGI.pm likeなアクセッサ

            package User;
            use strict;
            use warnings;
            use base qw(Class::Accessor::Fast);

            sub param {
                my $self = shift;
                if( @_ == 2 ) {
                    $self->{$_[0]} = $_[1];
                }
                elsif( @_ == 1 ) {
                    $self->{$_[0]};
                }
            }

            1;
          
            $mapper->maps(
                $user_table => 'User',
                accessors => {
                     generic_getter => 'param',
                     generic_setter => 'param',
                },
            );
          

mappingあれこれ

自動にクラスを作成

            $mapper->maps(
                $user_table => 'User',
                contstructor => { auto => 1 },
                accessors    => { auto => 1 },
            );
          

継承(Inheritance)

Single Table Inheritance

            CREATE TABLE users (
              id   INTEGER NOT NULL PRIMARY KEY,
              name TEXT,
              type TEXT
            );
          
            package User;
            use Any::Moose;

            has 'id'   => ( is => 'rw', isa => 'Int' );
            has 'name' => ( is => 'rw', isa => 'Str' );
            has 'type' => ( is => 'rw', isa => 'Maybe[Str]' );

            __PACKAGE__->meta->make_immutable;
          

継承(Inheritance)

Single Table Inheritance

            package User::Gold;
            use Any::Moose;
            extends 'User';

            __PACKAGE__->meta->make_immutable;
          
            package User::Silver;
            use Any::Moose;
            extends 'User';

            __PACKAGE__->meta->make_immutable;
          

継承(Inheritance)

Single Table Inheritance

            $mapper->maps(
                $user_table => 'User',
                polymorphic_on => 'type',
            );
          
            $mapper->maps(
                $user_table => 'User::Gold',
                polymorphic_identity => 'gold',
                inherits => 'User',
            );
          
            $mapper->maps(
                $user_table => 'User::Silver',
                polymorphic_identity => 'silver',
                inherits => 'User',
            );
          

!mappingの設定は単一継承

継承(Inheritance)

Single Table Inheritance

            my $session = $mapper->begin_session( autocommit => 0 );
            $session->add( User->new( name => 'normal user' ) );
            $session->add( User::Gold->new( name => 'gold user' ) );
            $session->add( User::Silver->new( name => 'silver user' ) );
            $session->commit;
            # INSERT INTO users ( name ) VALUES ( 'normal user' );
            # INSERT INTO users ( name, type ) VALUES ( 'gold user', 'gold');
            # INSERT INTO users ( name, type ) VALUES ( 'silver user', 'silver');
          

継承(Inheritance)

Single Table Inheritance

            my $session = $mapper->begin_session( autocommit => 0 );

            my @all    = @{ $session->search('User')->execute };
            # SELECT users.name, users.type, users.id FROM users;

            my @gold   = @{ $session->search('User::Gold')->execute };
            # SELECT users.name, users.type, users.id FROM users WHERE ( users.type = 'gold';

            my @silver = @{ $session->search('User::Silver')->execute };
            # SELECT users.name, users.type, users.id FROM users WHERE ( users.type = 'silver';
          

継承(Inheritance)

Single Table Inheritance

            my $session = $mapper->begin_session( autocommit => 0 );

            my @all = @{ $session->search('User')
                                 ->with_polymorphic('*')->execute };
            # SELECT users.name, users.type, users.id FROM users;
          
            (
              bless({ id => 1, name => "normal user", type => undef }, "User"),
              bless({ id => 2, name => "gold user", type => "gold" }, "User::Gold"),
              bless({ id => 3, name => "silver user", type => "silver" }, "User::Silver"),
            )
          

継承(Inheritance)

Class Table Inheritance

            CREATE TABLE gold_users (
                id INTEGER NOT NULL PRIMARY KEY,
                point INTEGER NOT NULL,
                FOREIGN KEY(id) REFERENCES users(id)
            );
          
            my $gold_user_table = $mapper->metadata->table(
                'gold_users' => 'autoload'
            );

            $mapper->maps(
                $gold_user_table => 'User::Gold',
                polymorphic_identity => 'gold',
                inherits => 'User',
            );
          

継承(Inheritance)

Class Table Inheritance

            package User::Gold;
            use Any::Moose;
            extends 'User';

            has 'point' => ( is => 'rw', point => 'Int' );

            __PACKAGE__->meta->make_immutable;
          
            my $session = $mapper->begin_session( autocommit => 0 );
            $session->add(
                User::Gold->new( name => 'gold user', point => 100 ) );
            $session->commit;
            # INSERT INTO users ( name, type ) VALUES ('gold user', 'gold');)
            # INSERT INTO gold_users ( id, point ) VALUES (2, 100);
          

継承(Inheritance)

Class Table Inheritance

            my $session = $mapper->begin_session( autocommit => 0 );

            my @all = @{ $session->search('User')
                                 ->with_polymorphic('*')->execute };
            # SELECT users.name, users.type, users.id, gold_users.point FROM users LEFT OUTER JOIN gold_users ON ( gold_users.id = users.id )
          

Sinble Tabel Inheritanceと
Class Table Inheritance

Metadata

Metadataとは?

データベーススキーマを保持している

MapperはMetadataを経由してEngineと通信している



Metadataでできること

Metadata

スキーマを上書き

Foreign Keyを定義したいけど、データベースには定義されていない場合

            use DBIx::ObjectMapper::Metadata::Sugar qw(:all);

            $mapper->metadata->table(
                'addresses' => [
                    Col( 'user_id' => ForeignKey( 'users' => 'id' ) ),
                ] => { autoload => 1 },
            );
          

Metadata

スキーマを上書き

カラムのタイプを上書き

            use DBIx::ObjectMapper::Metadata::Sugar qw(:all);

            $mapper->metadata->table(
                'user' => [
                    Col( 'yaml_data' => Yaml ), # TEXT
                    Col( 'stotorable_data' => Mush ), # TEXT
                    Col( 'myuri' => Uri ), # VARCHAR(256)
                ] => { autoload => 1 },
            );
          

Metadata:::Table::Column::TypeMapで自動で定義



Metadata:::Table::Column::Type::

Array, BigInt, Binary, Bit, Boolean, Date, Datetime, Float, Int, Interval, Mush, Numeric, SmallInt, String, Text, Time, Undef, Uri, Yaml

Metadata

スキーマを上書き

Default,OnUpdate

            use DBIx::ObjectMapper::Metadata::Sugar qw(:all);
            $mapper->metadata->table(
                'user' => [
                    Col( created => Default{ DateTime->now() } ),
                    Col( modified => OnUpdate{ DateTime->now() } ),
                ]
            );
          

FromStorage, ToStorage (inflate, deflate)

            use DBIx::ObjectMapper::Metadata::Sugar qw(:all);
            $mapper->metadata->table(
                'user' => [
                    Col( modified =>
                            OnUpdate{ DateTime->now() },
                            FromStorage { ... CODE ... },
                            ToStorage { ... CODE ... },
                    ),
                ]
            );
          

Metadata

メタデータからデータベースを操作する

insert

            my $metadata = $mapper->metadata;
            my $user_table = $metadata->table('users');

            # insert
            $metadata->insert
                     ->into('users')
                     ->values( name => 'user1' )
                     ->execute;
            # or
            $user_table->insert->values( name => 'user2' )->execute;

          

Metadata

メタデータからデータベースを操作する

update

            my $metadata = $mapper->metadata;
            my $user_table = $metadata->table('users');

            $metadata->update->table('users')
                             ->set( name => 'ユーザ1')
                             ->where( $user_table->c('id') == 1 )
                             ->execute();

            $user_table->update->set( name => 'ユーザ1')
                               ->where( $user_table->c('id') == 1 )
                               ->execute;
          

Metadata

メタデータからデータベースを操作する

delete

            my $metadata = $mapper->metadata;
            my $user_table = $metadata->table('users');

            $metadata->delete->table('users')
                             ->where( $user_table->c('id') == 1 )
                             ->execute();

            $user_table->delete
                       ->where( $user_table->c('id') == 1 )
                       ->execute;
          

Metadata

メタデータからデータベースを操作する

select

            my $metadata = $mapper->metadata;
            my $user_table = $metadata->table('users');

            my $it = $metadata->select
                              ->table('users')
                              ->where( $user_table->c('id') == 1 )
                              ->execute();
            # or
            my $it = $user_table->select
                       ->where( $user_table->c('id') == 1 )
                       ->execute;

            while( my $result = $it->next ) {
            # SELECT * FROM users WHERE users.id = 1;
                $result->{id};
                $result->{name};
            }
          

Metadata

メタデータからデータベースを操作する

select2

            my $metadata = $mapper->metadata;
            my $user_table = $metadata->table('users');

            my $it = $user_table->select
                       ->column( $user_table->c('name') )
                       ->where( $user_table->c('id') == 1 )
                       ->execute;

            while( my $result = $it->next ) {
            # SELECT users.name FROM users WHERE users.id = 1;
                $result->{name};
            }
          

Metadata

メタデータからデータベースを操作する

find

            my $user_table = $mapper->metadata->table('users');

            my $user = $user_table->find(1);
            $user->{id};
            $user->{name};
          

Metadata

メタデータからデータベースを操作する

count

            my $user_table = $mapper->metadata->table('users');
            $user_table->select
                       ->column({count=>'*'})
                       ->first->{count};

            my $cnt = $user_table->count->execute;
          

Metadata

メタデータからデータベースを操作する

count

            my $user_table = $mapper->metadata->table('users');
            $user_table->select
                       ->column({count=>'*'})
                       ->first->{count};

            my $cnt = $user_table->count->execute;
          

Metadata

メタデータからデータベースを操作する

select - join

            my $user_table = $mapper->metadata->table('users');
            my $addr_table = $mapper->metadata->table('addresses');

            my $query = $user_table->select
                     ->join([
                        $addr_tables => [
                            $address->c('person') == $person->c('id')
                        ]
                     ])
                     ->column( @{$user_table->columns} )
                     ->order_by( $user_table->c('id') );

            $query->add_column(@{$addr_table->columns});

            my $it = $query->execute;
            while( my $result = $it->next ) {
                $result->{id};
                $result->{name};
                $result->{addresses}{address};
                $result->{addresses}{user_id};
            }
          

Metadata

メタデータからデータベースを操作する

select - subquery

            my $it = $user_table->select->where(
                $user_table->c('id')->in(
                     $user_table->select->column( $user_table->c('id') )
                )
            )->execute;
          

Metadata

メタデータからデータベースを操作する

まとめ

今回紹介できなかった機能

ご清聴ありがとうございました

是非使ってみてください!

バグ報告、質問、パッチ等々は