DBIX::Class (aka DBIC)

for (advanced) beginners

Authors

Originally Leo Lapworth @ LPW 2009

Matthew S. Trout

Justin D. Hunter

Arthur Axel "fREW" Schmidt

assumptions

You know a little about Perl and using objects

You know a little bit about databases and using foreign keys

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)

point of note

"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it." - Brian W. Kernighan

This talk is about making it easy so we are less likely to get confused

Examples Table Setup

• Authors
• Books

authors table

CREATE TABLE authors(
   id   int(8) primary key auto_increment,
   name varchar(255)
) engine = InnoDB DEFAULT CHARSET=utf8;

tips

Name tables as simple plurals (add an S) - makes relationships easier to understand

Use a character set (UTF8) from the start (for international characters)

authors table

CREATE TABLE authors(
   id   int(8) primary key auto_increment,
   name varchar(255)
) engine = InnoDB DEFAULT CHARSET=utf8;

books table

CREATE TABLE books(
   id        int(8) primary key auto_increment,
   title     varchar(255),
   author_id int(8),foreign key (author)
      references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;

tips

Name link fields as singular

Ensure foreign key is the same type and size in both tables

books table

CREATE TABLE books(
   id        int(8) primary key auto_increment,
   title     varchar(255),
   author_id int(8),foreign key (author)
      references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;

CRUD compared

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

Manual (SQL)

SQL: Create

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

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

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";
}

SQL: Update

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

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

SQL: Delete

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

$delete->execute($book_id);

DBIx::Class

DBIC: Create

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

Look ma, no SQL!

DBIC: Create

my $pratchett = $author_model->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',
});

DBIC: Read

DBIx::Class - Lots of ways to do the same thing...

"There is more than one way to do it (TIMTOWTDI, usually pronounced "Tim Toady") is a Perl motto"

DBIC: Read

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

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

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

DBIC: Read

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

DBIC: Read

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

Search takes SQL::Abstract formatted queries

> perldoc SQL::Abstract

   

DBIC: Update

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

DBIC: Delete

$book->delete;

Creating models

package Foo::Schema::Result::Author;
use strict; use warnings;
__PACKAGE__->table('authors');
__PACKAGE__->add_columns(
  id => {
    data_type => 'int',
    size      => 8,
  },
  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',
    size      => 8,
  },
  name => {
    data_type   => 'varchar',
    is_nullable => 1,
    size        => 255,
  },
  author_id => {
    data_type   => 'int',
    size        => 8,
    is_nullable => 1, # <-- probably should be 0
  },
);
__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_model->search({ name => 'Douglas Adams' })->single;
my $book_rs      = $book_model->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_model
  ->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`;

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_model->search({
   name => 'Douglas Adams',
})->single;
$author->add_to_books({
   title => 'A new book',
});

my $author = $author_model->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 $model = $c->model('Schema::Foo');
  my $author_model = $model->resultset('Authors');

}

1;