Introduction to DBIx::Lite id:motemen

me •id:motemen • motemen ඒإث • Application engineer •DBIx::MoCo maintainer

ORM and me •DBIx::MoCo •DBIx::Skinny •Data::Model •Teng

DBIx::Lite •AUTHOR cpan:AAR •“Chained and minimal ORM” •cf. DBIx::Class

SYNOPSIS

Preparation •No classes required to declare •e.g. schemas, row classes •Just use

Initialization •Passing $dbh •Using DBIx::Connector my $dbix = DBIx::Lite->new(dbh => $dbh); my $dbix = DBIx::Lite->connect( 'dbi:mysql:dbname=...', $username, $password, { mysql_enable_utf8 => 1, RootClass => 'DBIx::Sunny', }, );

CRUD •Call table() to start building query •Returns ResultSet object my $entries = $dbix->table('entries');

SELECT •Build ResultSet by method chain •Finally retrieve row objects my $entries_rs = $dbix->table('entries') ->search({ author_id => 1 }) ->order_by('created_at'); my @entries = $entries_rs->all; # SELECT me.* FROM entries AS me WHERE ( author_id = '1' ) ORDER BY created_at my $entry = $entries_rs->limit(1)->single; # SELECT me.* FROM entries AS me WHERE ( author_id = '1' ) ORDER BY created_at LIMIT 1 OFFSET 0

INSERT •If the table has an auto- incremented pkey, LAST_INSERT_ID is filled in my $entry = $dbix->table('entries')->insert({ author_id => 47, body => '…', created_at => time(), }); # INSERT INTO entries ( author_id, body, created_at) VALUES ( '47', '…', '1345196410' )

UPDATE/DELETE $dbix->table('entries') ->search({ id => [ 2, 3, 5 ] }) ->update({ body => '…' }); # UPDATE entries AS me SET body = '…' WHERE ( ( id = '2' OR id = '3' OR id = '5' ) ) $dbix->schema->table('entries')->pk('id'); $entry->update({ body => '…' }); # UPDATE entries AS me SET body = '…' WHERE ( id = '1' )

Components

•DBIx::Lite •DBIx::Lite::ResultSet •DBIx::Lite::Row •DBIx::Lite::Schema •DBIx::Lite::Schema::Table

DBIx::Lite •Represents a database (connection) •$dbix->{connector} •isa DBIx::Connector •$dbix->{schema} •isa DBIx::Lite::Schema •Generates ResultSet (next)

DBIL::ResultSet •Represents a set of database rows •Has most major methods •Does not hold actual data in itself •To retrieve row objects: my @all = $rs->all; my $row = $rs->single;

DBIL::ResultSet •Chain methods to build specific result set •search() uses SQL::Abstract •Multiple search()’es joined by “AND” $rs = $dbix->table(…); $rs = $rs->search(\%where); # WHERE $rs = $rs->order_by($col); # ORDER BY $rs = $rs->limit($n); # LIMIT $rs = $rs->select(@cols); # SELECT

DBIL::Row •Represents a database row •Simple structure •$row->{data}, $row->{dbix_lite} •$row->hashref •No inflates/deflates •AUTOLOAD method names

DBIL::Row •Row operation methods •$row->update(\%cols) •$row->delete •pk needed in schema (next)

DBIL::Schema & DBIL::Schema::Table •Represents metadata of tables •(Auto-increment) primary keys •Row classes, ResultSet classes •Has-a, has-many relationships

DBIL::Schema & DBIL::Schema::Table •Setting primary key lets row objects’ update methods to work •Use autopk() for auto-inc pk •Give row objects custom methods from the package $dbix->schema->table('entries')->pk('id'); $dbix->schema->table('entries') ->class('My::Row::Entry');

DBIL::Schema & DBIL::Schema::Table •Register has-many relations •ResultSet gains relation method $dbix->schema->one_to_many( 'authors.id' => 'entries.author_id' ); my @entries = $dbix->table('authors') ->search({ name => 'motemen' }) ->entries->all; # SELECT entries.* FROM authors AS me INNER JOIN entries ON ( me.id = entries.author_id ) WHERE ( name = 'motemen' )

Other features

Paging •Paging by offset/limit my $entries_rs = $dbix->table('entries') ->rows_per_page(10)->page(3); $entries_rs->all; # SELECT COUNT(*) FROM entries AS me LIMIT 10 OFFSET 0 # SELECT me.* FROM entries AS me LIMIT 10 OFFSET 20 my $pager = $entries_rs->pager; # => Data::Page

JOIN •Use $rs->select_also() to fetch joined tables $dbix->table('entries') ->left_join('authors', { author_id => 'id' }) ->select_also([ 'authors.name' => 'author_name' ]) ->all; # SELECT me.*, authors.name AS `author_name` FROM entries AS me LEFT OUTER JOIN authors ON ( me.author_id = authors.id )

Raw DBI access •Internally uses DBIx::Connector#run my $now = $dbix->dbh_do(sub { $_->selectcol_arrayref('SELECT NOW()')->[0] });

CAVEATS •“SELECT me.* FROM …” queries •Sometimes need to prefix “me.” to field names •Row classes are not automatically require’d

HACKING •Auto-upgrade connection to master •Need to alter DBIx::Lite and produced DBIL::ResultSet •Use Role::Tiny •gist:3378389

Sum up •Simple APIs •ResultSet •No need to declare classes, easy to start •Extending may need tricks

Questions?