dbix::class introduction - 2010
DESCRIPTION
If your not using an ORM (object relational mapper) and are still writing SQL by hand, here's what you need to know. An introduction into DBIx::Class and some of the concepts and goodies you should be aware off.TRANSCRIPT
![Page 1: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/1.jpg)
DBIx::Class (aka DBIC)for (advanced) beginners
Leo Lapworth @ YAPC::EU 2010
http://leo.cuckoo.org/projects/
![Page 2: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/2.jpg)
assumptions
You know a little about Perl and using objects
You know a little bit about databases and using foreign keys
![Page 3: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/3.jpg)
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)
![Page 4: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/4.jpg)
why this talk?
• Help avoid mistakes I made!
• Help learn DBIx::Class faster
• Make your coding easier
![Page 5: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/5.jpg)
table setup
![Page 6: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/6.jpg)
example...
Books
Authors
![Page 7: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/7.jpg)
authors table
CREATE TABLE authors(
id int(8) primary key auto_increment,
name varchar(255)
) engine = InnoDB DEFAULT CHARSET=utf8;
![Page 8: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/8.jpg)
tips
Name tables as simple plurals (add an S) - makes relationships easier to understand
(issue: Matt Trout "Tables should not be plural as gives you plurals for Result:: package names which represent a single row" - talk may be rewritten in future to reflect this as this is better once you understand the relationship setup - either way, consistency is important)
Use a character set (UTF8) from the start (for international characters)
![Page 9: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/9.jpg)
authors table
CREATE TABLE authors(
id int(8) primary key auto_increment,
name varchar(255)
) engine = InnoDB DEFAULT CHARSET=utf8;
![Page 10: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/10.jpg)
books tableCREATE TABLE books(
id int(8) primary key auto_increment,
title varchar(255),
author int(8),
foreign key (author)
references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;
![Page 11: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/11.jpg)
tips
Name link fields as singular
Check foreign key is the same field type and size in both tables
![Page 12: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/12.jpg)
books tableCREATE TABLE books(
id int(8) primary key auto_increment,
title varchar(255),
author int(8),
foreign key (author)
references authors(id)) engine = InnoDB DEFAULT CHARSET=utf8;
![Page 13: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/13.jpg)
CRUD comparedC - CreateR - RetrieveU - UpdateD - Delete
![Page 14: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/14.jpg)
Manual (SQL)
![Page 15: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/15.jpg)
manual: createmy $sth = $dbh->prepare('
INSERT INTO books
(title, author)
values (?,?)
');
$sth->execute( 'A book title',$author_id);
![Page 16: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/16.jpg)
manual: createmy $sth = $dbh->prepare('
INSERT INTO books
(title, author)
values (?,?)
');
$sth->execute(
'A book title',$author_id);
![Page 17: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/17.jpg)
manual: retrievemy $sth = $dbh->prepare('
SELECT title,
authors.name as author_name
FROM books, authors
WHERE books.author = authors.id
');
![Page 18: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/18.jpg)
manual: retrievewhile( my $book = $sth->fetchrow_hashref() ) {
print 'Author of '
. $book->{title}
. ' is '
. $book->{author_name}
. "\n";
}
![Page 19: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/19.jpg)
manual: updatemy $update = $dbh->prepare('
UPDATE books
SET title = ?
WHERE id = ?
');
$update->execute(
'New title',$book_id);
![Page 20: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/20.jpg)
manual: deletemy $delete = $dbh->prepare('
DELETE FROM books
WHERE id = ?
');
$delete->execute($book_id);
![Page 21: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/21.jpg)
DBIx::Class
![Page 22: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/22.jpg)
DBIC: createmy $book = $book_model->create({
title => 'A book title',
author => $author_id,
});
Look ma, no SQL!
Tip: do not pass in primary_key field, even if its empty/undef as the object returned will have an empty id, even if your field is auto increment.
![Page 23: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/23.jpg)
DBIC: createmy $book = $book_model->create({
title => 'A book title',
author => $author_id,});
![Page 24: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/24.jpg)
DBIC: createmy $pratchett = $author_model->create({
name => 'Terry Pratchett',
});
![Page 25: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/25.jpg)
DBIC: createmy $book = $pratchett->create_related(
'books', {
title => 'Another Discworld book',
});
or
my $book = $pratchett->add_to_books({
title => 'Another Discworld book',
});
![Page 26: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/26.jpg)
DBIC: createmy $book = $pratchett->create_related(
'books', {
title => 'Another Discworld book',
});
or
my $book = $pratchett->add_to_books({
title => 'Another Discworld book',
});
![Page 27: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/27.jpg)
DBIC: retrieve
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"
![Page 28: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/28.jpg)
DBIC: retrievemy $book = $book_model->find($book_id);
my $book = $book_model->search({
title => 'A book title',
})->single();
my @books = $book_model->search({
author => $author_id,
})->all();
![Page 29: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/29.jpg)
DBIC: retrievewhile( my $book = $books_rs->next() ) {
print 'Author of '
. $book->title()
. ' is '
. $book->author()->name()
. "\n";
}
![Page 30: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/30.jpg)
DBIC: retrievemy $books_rs = $book_model->search({
author => $author_id,
});
Search takes SQL::Abstract formatted queries> perldoc SQL::Abstract
![Page 31: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/31.jpg)
DBIC: update$book->update({
title => 'New title',
});
![Page 32: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/32.jpg)
DBIC: delete$book->delete();
![Page 33: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/33.jpg)
Creating schemas
![Page 34: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/34.jpg)
![Page 35: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/35.jpg)
![Page 36: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/36.jpg)
too much typing!
too much maintenance!
![Page 37: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/37.jpg)
Schema::Loader
Database introspection -> Code
![Page 38: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/38.jpg)
Use namespaces
![Page 39: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/39.jpg)
Use NamespacesSplits logic cleanly
Bookstore::Schema::Result::X
= an individual row
Bookstore::Schema:: ResultSet::X
= searches / results
![Page 40: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/40.jpg)
![Page 41: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/41.jpg)
![Page 42: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/42.jpg)
![Page 43: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/43.jpg)
You can edit this line
![Page 44: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/44.jpg)
Connection details
![Page 45: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/45.jpg)
using your Schema
![Page 46: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/46.jpg)
![Page 47: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/47.jpg)
![Page 48: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/48.jpg)
DEBUGGING
DBIC_TRACE=1 ./your_script.pl
![Page 49: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/49.jpg)
SQL - debugging
INSERT INTO authors (name) VALUES (?): 'Douglas Adams'
INSERT INTO books (author, title) VALUES (?, ?): '1', '42'
![Page 50: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/50.jpg)
overloading
Bookstore::Schema::Result::Books
Bookstore::Schema::ResultSet::Books
Bookstore::Schema::Result::Authors
Bookstore::Schema::ResultSet::Authors
![Page 51: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/51.jpg)
Result::package Bookstore::Schema::Result::Books;use base 'DBIx::Class';
#...
# Created by DBIx::Class::Schema::Loader v0.04005 @ 2010-08-01 09:19:14# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ta+cEh31lDfqcue3OmUCfQ
sub isbn {my $self = shift;
# search amazon or somethingmy $api = Amazon::API->book({ title => $self->title() });
return $api->isbn();}
1;
![Page 52: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/52.jpg)
Result::package Bookstore::Schema::Result::Books;use base 'DBIx::Class';
#...
# Created by DBIx::Class::Schema::Loader v0.04005 @ 2010-08-01 09:19:14# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:ta+cEh31lDfqcue3OmUCfQ
sub isbn {my $self = shift;
# search amazon or somethingmy $api = Amazon::API->book({ title => $self->title() });
return $api->isbn();}
1;
![Page 53: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/53.jpg)
Result::print $book->isbn();
![Page 54: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/54.jpg)
Result:: (inflating)package Bookstore::Schema::Result::Books;use base 'DBIx::Class';
#...
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
![Page 55: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/55.jpg)
Result:: (inflating)package Bookstore::Schema::Result::Books;use base 'DBIx::Class';
#...
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
![Page 56: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/56.jpg)
Result:: (deflating)$book->date_published(DateTime->now);
$book->update();
2008-12-31
![Page 57: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/57.jpg)
Result:: (inflating)
my $date_published = $book->date_published()print $date_published->month_abbr();
Nov
![Page 58: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/58.jpg)
ResultSets::package Bookstore::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;
![Page 59: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/59.jpg)
ResultSets::package Bookstore::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(), } );}
![Page 60: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/60.jpg)
ResultSets::package Bookstore::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;
![Page 61: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/61.jpg)
ResultSets::use Bookstore::Schema;
my $book_model = Bookstore::Schema->resultset('Books');
my $book_rs = $book_model->the_ultimate_books();
my @books = $book_rs->all();
![Page 62: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/62.jpg)
ResultSets::chaininguse Bookstore::Schema;
my $book_model = Bookstore::Schema->resultset('Books');my $author_model = Bookstore::Schema->resultset('Authors');
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();
![Page 63: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/63.jpg)
ResultSets::chainingmy $book_rs = $book_model
->the_ultimate_books() ->by_author($author);
or
my $book_rs = $book_model ->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 ? ) ) ): '1', '%42%'
![Page 64: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/64.jpg)
ResultSets::chainingmy $rs = $book_model
->category('childrens') ->by_author($author) ->published_after('1812') ->first_page_contains('once upon') ->rating_greater_than(4);
my @books = $rs->all();
![Page 65: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/65.jpg)
overloading before new record
![Page 66: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/66.jpg)
overloading before new record
package Bookstore::Schema::Result::Authors;use base 'DBIx::Class';
sub new { my ( $class, $attrs ) = @_;
# Mess with $attrs
my $new = $class->next::method($attrs); return $new;}
1;
![Page 67: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/67.jpg)
relationships
![Page 68: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/68.jpg)
multiple authors
![Page 69: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/69.jpg)
a few relationships
Authors BooksAuthors_and_Books
has_many has_many
belongs_to belongs_to
many_to_many
![Page 70: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/70.jpg)
a few relationships
!
![Page 71: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/71.jpg)
new join tableCREATE TABLE author_and_books( id int(8) primary key auto_increment, book ! int(8), author int(8),
foreign key (book) references books(id), foreign key (author) references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `books` DROP COLUMN `author`;
![Page 72: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/72.jpg)
CREATE TABLE author_and_books( id int(8) primary key auto_increment, book ! int(8), author int(8),
foreign key (book) references books(id), foreign key (author) references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;
new join table
![Page 73: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/73.jpg)
has_many
Books Authors_and_Books
has_many
belongs_to
![Page 74: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/74.jpg)
has_manypackage Bookstore::Schema::Result::Books;
__PACKAGE__->has_many(
"author_and_books", "Bookstore::Schema::Result::AuthorAndBooks",
{ "foreign.book" => "self.id" },
);
# This is auto generated by Schema::Loader
![Page 75: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/75.jpg)
has_manypackage Bookstore::Schema::Result::Books;
__PACKAGE__->has_many(
"author_and_books", # Name of accessor "Bookstore::Schema::Result::AuthorAndBooks", # Related class { "foreign.book" => "self.id" }, # Relationship (magic often works if not # specified, but avoid!));
![Page 76: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/76.jpg)
belongs_to
Books Authors_and_Books
has_many
belongs_to
![Page 77: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/77.jpg)
belongs_topackage Bookstore::Schema::Result::AuthorAndBooks;
__PACKAGE__->belongs_to( "book", "Bookstore::Schema::Result::Books", { id => "book" });
# This is auto generated by Schema::Loader
![Page 78: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/78.jpg)
belongs_topackage Bookstore::Schema::Result::AuthorAndBooks;
__PACKAGE__->belongs_to( "book", # Accessor name "Bookstore::Schema::Result::Books",
# Related class { id => "book" } # Relationship);
![Page 79: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/79.jpg)
same for Authors
Authors Authors_and_Books
has_many
belongs_to
![Page 80: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/80.jpg)
with no coding...
Authors BooksAuthors_and_Books
has_many has_many
belongs_to belongs_to
![Page 81: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/81.jpg)
many_to_many
Authors BooksAuthors_and_Books
has_many has_many
belongs_to belongs_to
many_to_many
![Page 82: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/82.jpg)
many_to_manypackage Bookstore::Schema::Result::Books;use base 'DBIx::Class';
__PACKAGE__->many_to_many( "authors"
=> "author_and_books",
'author');
1;
# This is NOT auto generated by Schema::Loader
![Page 83: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/83.jpg)
many_to_manypackage Bookstore::Schema::Result::Books;use base 'DBIx::Class';
__PACKAGE__->many_to_many( "authors" # Accessor Name => "author_and_books", # has_many accessor_name 'author' # foreign relationship name);
1;
![Page 84: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/84.jpg)
many_to_manypackage Bookstore::Schema::Result::Authors;use base 'DBIx::Class';
__PACKAGE__->many_to_many( "books" # Accessor Name => "author_and_books", # has_many accessor_name 'book' # foreign relationship name);
1;
# This is NOT auto generated by Schema::Loader
![Page 85: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/85.jpg)
using many_to_many#!/usr/bin/perl
use Bookstore::Schema;
my $author_model = Bookstore::Schema->resultset('Authors');
my $author = $author_model->search({name => 'Douglas Adams',
})->single();
$author->add_to_books({title => 'A new book',
});
![Page 86: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/86.jpg)
using many_to_manymy $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';
![Page 87: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/87.jpg)
using many_to_many$author->add_to_books($book);
$book->add_to_authors($author_1);
$book->add_to_authors($author_2);
![Page 88: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/88.jpg)
in 16 lines of code
Authors BooksAuthors_and_Books
has_many has_many
belongs_to belongs_to
many_to_many
![Page 89: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/89.jpg)
errors
Read them closely!
![Page 90: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/90.jpg)
error messagesDBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'Bookstore::Schema::Result::Authors': Can't locate object method "many_to_many" via package "Bookstore::Schema::Result::Author" at lib/Bookstore/Schema/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.
![Page 91: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/91.jpg)
error messagesDBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'Bookstore::Schema::Result::Authors': Can't locate object method "many_to_many" via package "Bookstore::Schema::Result::Author" at lib/Bookstore/Schema/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.
![Page 92: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/92.jpg)
errors
• Turn on debugging
• Read error messages (sometimes useful!)
• Check field names
• Check package names
• Check which database you are connected to (development/test/live?) - repeat above
![Page 93: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/93.jpg)
thanks
http://leo.cuckoo.org/projects/
Time for bonus slides?
![Page 94: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/94.jpg)
Template Toolkit
• [% author.books.count %] not working?
• TT all methods are called in list context
• [% author.books_rs.count %] scalar context
Available for all relationships
![Page 95: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/95.jpg)
Catalystpackage Your::App::Model::Bookstore;use base qw(Catalyst::Model::DBIC::Schema);
use strict;use warnings;
__PACKAGE__->config( schema_class => 'Bookstore::Schema',);
1;
![Page 96: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/96.jpg)
Catalystpackage Your::App::Model::Bookstore;use base qw(Catalyst::Model::DBIC::Schema);
use strict;use warnings;
__PACKAGE__->config( schema_class => 'Bookstore::Schema',);
1;
Keep your Scheme in a separate package to your Catalyst application
![Page 97: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/97.jpg)
Catalystsub action_name : Local { my ($self, $c) = @_;
my $model = $c->model('Bookstore'); my $author_model = $model->resultset('Authors'); }
1;
![Page 98: DBIx::Class introduction - 2010](https://reader033.vdocuments.us/reader033/viewer/2022051817/5479c23c5906b51b358b45d5/html5/thumbnails/98.jpg)
thanks!
http://leo.cuckoo.org/projects/