Pro Yearly is on sale from $80 to $50! »

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

9b741203feda475cbeae8b384de9f415?s=47 motemen
August 18, 2012

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

9b741203feda475cbeae8b384de9f415?s=128

motemen

August 18, 2012
Tweet

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?