Introduction to DBIx::Lite - Kyoto.pm tech talk #2

Transcript

1. Introduction to DBIx::Lite id:motemen
2. me •id:motemen • motemen ඒإث • Application engineer •DBIx::MoCo maintainer
3. ORM and me •DBIx::MoCo •DBIx::Skinny •Data::Model •Teng
4. DBIx::Lite •AUTHOR cpan:AAR •“Chained and minimal ORM” •cf. DBIx::Class
5. SYNOPSIS
6. Preparation •No classes required to declare •e.g. schemas, row classes •Just use
7. 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', }, );
8. CRUD •Call table() to start building query •Returns ResultSet object my $entries = $dbix->table('entries');
9. 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
10. 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' )
11. 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' )
12. Components
13. •DBIx::Lite •DBIx::Lite::ResultSet •DBIx::Lite::Row •DBIx::Lite::Schema •DBIx::Lite::Schema::Table
14. DBIx::Lite •Represents a database (connection) •$dbix->{connector} •isa DBIx::Connector •$dbix->{schema} •isa DBIx::Lite::Schema •Generates ResultSet (next)
15. 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;
16. 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
17. DBIL::Row •Represents a database row •Simple structure •$row->{data}, $row->{dbix_lite} •$row->hashref •No inflates/deflates •AUTOLOAD method names
18. DBIL::Row •Row operation methods •$row->update(\%cols) •$row->delete •pk needed in schema (next)
19. DBIL::Schema & DBIL::Schema::Table •Represents metadata of tables •(Auto-increment) primary keys •Row classes, ResultSet classes •Has-a, has-many relationships
20. 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');
21. 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' )
22. Other features
23. 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
24. 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 )
25. Raw DBI access •Internally uses DBIx::Connector#run my $now = $dbix->dbh_do(sub { $_->selectcol_arrayref('SELECT NOW()')->[0] });
26. CAVEATS •“SELECT me.* FROM …” queries •Sometimes need to prefix “me.” to field names •Row classes are not automatically require’d
27. HACKING •Auto-upgrade connection to master •Need to alter DBIx::Lite and produced DBIL::ResultSet •Use Role::Tiny •gist:3378389
28. Sum up •Simple APIs •ResultSet •No need to declare classes, easy to start •Extending may need tricks
29. Questions?