DBIX::Class (aka DBIC)

for (advanced) beginners

Authors

Originally Leo Lapworth @ LPW 2009

Matthew S. Trout

Justin D. Hunter

Arthur Axel "fREW" Schmidt

What's up guys?

DBIx::Class?

• ORM (object relational mapper)
• SQL <-> OO (using objects instead of SQL)
• Simple, powerful, complex, fab and confusing
• There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)

Purpose

The purpose of this talk is to show you as many features of DBIx::Class in 40 minutes so that when you need to do something with it later you will know what's possible

Basic CRUD

• C - Create
• R - Read
• U - Update
• D - Delete

SQL: Create

my $sth = $dbh->prepare('
   INSERT INTO books
   (title, author_id)
   values (?,?)
');

$sth->execute(
  'A book title', $author_id
);

DBIC: Create

my $book = $book_rs->create({
   title     => 'A book title',
   author_id => $author_id,
});

Don't need to work to pair placeholders and values

DBIC: Create

my $pratchett = $author_rs->create({
   name => 'Terry Pratchett',
});

DBIC: Create

my $book = $pratchett->create_related(
  books => {
   title => 'Another Discworld book',
});

my $book = $pratchett->add_to_books({
   title => 'Another Discworld book',
});

Automaticaly fills in foreign key for you

SQL: Read

my $sth = $dbh->prepare('
   SELECT title,
   authors.name as author_name
   FROM books, authors
   WHERE books.author = authors.id
');

SQL: Read

while( my $book = $sth->fetchrow_hashref() ) {
  print 'Author of '
     . $book->{title}
     . ' is '
     . $book->{author_name}
     . "\n";
}

DBIC: Read

my $book = $book_rs->find($book_id);

my $book = $book_rs->search({
   title => 'A book title',
}, { rows => 1 })->single;

my @books = $book_rs->search({
   author => $author_id,
})->all;

TMTOWTDI

DBIC: Read

while( my $book = $books_rs->next ) {
 print 'Author of '
    . $book->title
    . ' is '
    . $book->author->name
    . "\n";
}

DBIC: Read

my $resultset = $book_rs->search({
   author => $author_id,
});

Search takes SQL::Abstract formatted queries

> perldoc SQL::Abstract

   

SQL: Update

my $update = $dbh->prepare('
   UPDATE books
   SET title = ?
   WHERE id = ?
');

$update->execute(
  'New title',$book_id
);

DBIC: Update

$book->update({
  title => 'New title',
});

SQL: Delete

my $delete = $dbh->prepare('
   DELETE FROM books
   WHERE id = ?
');

$delete->execute($book_id);

DBIC: Delete

$book->delete;

Creating models

package Foo::Schema::Result::Author;
__PACKAGE__->table('authors');
__PACKAGE__->add_columns(
  id => {
    data_type         => 'int',
    is_auto_increment => 1
  },
  title => {
    data_type   => 'varchar',
    is_nullable => 1,
    size        => 255,
  },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many( books =>
   'Foo::Schema::Result::Book', 'author_id'
);
1;

package Foo::Schema::Result::Book;
use strict; use warnings;
__PACKAGE__->table('books');
__PACKAGE__->add_columns(
  id => {
    data_type         => 'int',
    is_auto_increment => 1
  },
  name => {
    data_type   => 'varchar',
    is_nullable => 1,
    size        => 255,
  },

  author_id => {
    data_type   => 'int',
    size        => 8,
  },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to( author =>
   'Foo::Schema::Result::Author', 'author_id'
);
1;

->deploy

Perl -> DB

my $schema = Foo::Schema->connect($dsn, $user, $pass);
$schema->deploy

See also: DBIx::Class::DeploymentHandler

Schema::Loader

DB -> Perl

package Foo::Schema;
use strict; use warnings;
use base 'DBIx::Class::Schema::Loader';
__PACKAGE__->loader_options({
   naming => 'v7',
   debug  => $ENV{DBIC_TRACE},
});
1;

# elsewhere...

my $schema = Foo::Schema->connect($dsn, $user, $pass);

Splitting Logic Cleanly

Foo::Schema::Result::Bar = individual row

Foo::Schema::ResultSet::Bar = searches / table

Using your Schema

#!perl
use strict; use warnings;
use lib 'lib';
use Foo::Schema;
my $schema = Foo::Schema->connect($dns, $user, $pass);
my $author_rs = $schema->resultset('Author');
my $author    = $author_rs->create({
   name => 'Douglas Adams',
});
my $book = $author->add_to_books({
   title => '42',
});

DEBUGGING

DBIC_TRACE=1 ./your_script.pl

SQL - debugging

INSERT INTO authors (name)
   VALUES (?): 'Douglas Adams'

INSERT INTO books (author, title)
   VALUES (?, ?): '5', '42'

overloading

Foo::Schema::Result::Book
Foo::Schema::ResultSet::Book
Foo::Schema::Result::Author
Foo::Schema::ResultSet::Book

Result::

package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
use strict;
use warnings;

# Result code here

sub isbn {
   my $self = shift;

   # search amazon or something
   my $api = Amazon::API->book({
      title => $self->title
   });

   return $api->isbn;
}

1;

Result::

print $book->isbn;

Result:: (inflating)

package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
use strict;
use warnings;

# Result code here

__PACKAGE__->load_components('InflateColumn');
use DateTime::Format::MySQL;

__PACKAGE__->inflate_column(
   date_published => {
      inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
      deflate => sub { shift->ymd},
   },
);
# Automatic see: DBIx::Class::InflateColumn::DateTime

Result:: (deflating)

$book->date_published(DateTime->now);
$book->update;

Result:: (inflating)

my $date_published = $book->date_published;
print $date_published->month_abbr;

ResultSets::

package Foo::Schema::ResultSet::Books;
use base 'DBIx::Class::ResultSet';
sub the_ultimate_books {
   my $self = shift;
   return $self->search({ title => { -like => '%42%' } });
}
sub by_author {
   my ( $self, $author ) = @_;
   return $self->search({ author => $author->id })
}

1;

ResultSets::

use Foo::Schema;
my $schema = Foo::Schema->connect(...);
my $book_rs     = Foo::Schema->resultset('Book');
my $book_search = $book_rs->the_ultimate_books;
my @books       = $book_search->all;

ResultSets: Chaining

my $book_rs   = $schema->resultset('Book');
my $author_rs = $schema->resultset('Author');
my $author    = $author_rs->search({ name => 'Douglas Adams' })->single;
$book_rs      = $book_rs->the_ultimate_books->by_author($author);
my @books     = $book_rs->all;

ResultSets: Chaining

$book_rs = $schema->resultset('Book')
  ->the_ultimate_books
  ->by_author($author);

my $book_rs = $schema->resultset('Book')
  ->the_ultimate_books;
$book_rs = $book_rs->by_author($author);

# Debug (SQL):

# SELECT me.id, me.title, me.date_published, me.author
#   FROM books me
#   WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
#   WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'

ResultSets: Chaining

my $rs = $book_rs
  ->category('childrens')
  ->by_author($author)
  ->published_after('1812')
  ->first_page_contains('once upon')
  ->rating_greater_than(4);

my @books = $rs->all;

overloading before new record

package Foo::Schema::Result::Author;
use base 'DBIx::Class::Core';

sub new {
   my ( $class, $attrs ) = @_;
   # Mess with $attrs
   my $new = $class->next::method($attrs);
   return $new
}

1;

a few relationships

CREATE TABLE author_and_books(
  book_id    int(8),
  author_id  int(8),
  foreign key (book_id)     references books(id),
  foreign key (author_id)   references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `books` DROP `author_id`;

has_many

package Foo::Schema::Result::Book;

__PACKAGE__->has_many( author_and_books =>
   'Foo::Schema::Result::Author_Book', 'book_id'
);
   

belongs_to

package Foo::Schema::Result::Author_Book;

__PACKAGE__->belongs_to(
   book =>
   'Foo::Schema::Result::Book', 'book_id'
);

same for Authors

many_to_many

package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';

__PACKAGE__->many_to_many(
   authors => 'author_and_books', 'author'
);

1;

package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';

__PACKAGE__->many_to_many(
   authors # Accessor name
   => "author_and_books", # has_many
   'author' # foreign relationship name
);

1;

package Foo::Schema::Result::Author;
use base 'DBIx::Class::Core';

__PACKAGE__->many_to_many(
   "books" # Accessor Name
   => "author_and_books", # has_many accessor_name
   'book' # foreign relationship name
);

1;

#!perl
use Foo::Schema;
my $schema    = Foo::Schema->connect(...);
my $author_rs = $schema->resultset('Authors');
my $author    = $author_rs->search({
   name => 'Douglas Adams',
})->single;
$author->add_to_books({
   title => 'A new book',
});

my $author = $author_rs->search({
   name => 'Douglas Adams',
})->single;
$author->add_to_books({
   title => 'A new book',
});

# SELECT me.id, me.name FROM authors me
#     WHERE ( name = ? ): 'Douglas Adams';
# INSERT INTO books (title) VALUES (?): 'A new book';
# INSERT INTO author_and_books (author, book)
#     VALUES (?, ?): '5', '2';

$author->add_to_books($book);

$book->add_to_authors($author_1);
$book->add_to_authors($author_2);

DBIx::Class::Schema::Loader::connection(): Failed to load external
class definition for 'Foo::Schema::Result::Authors': Can't locate object
method "many_to_many" via package "Foo::Schema::Result::Author" at
lib/Foo/Schema/Result/Authors.pm line 9.Compilation failed in require at
/Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.

DBIx::Class::Schema::Loader::connection(): Failed to load external
class definition for 'Foo::Schema::Result::Authors': Can't locate object
method "many_to_many" via package "Foo::Schema::Result::Author" at
lib/Foo/Schema/Result/Authors.pm line 9.Compilation failed in require at
/Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.

package Your::App::Model::Foo;
use base qw(Catalyst::Model::Schema::Schema);

use strict;
use warnings;

__PACKAGE__->config(
  schema_class => 'Foo::Schema',
);

1;

sub action_name : Local {
  my ($self, $c) = @_;

  my $schema = $c->model('Schema::Foo');
  my $author_rs = $schema->resultset('Authors');

}

1;