You know a little about Perl and using objects
You know a little bit about databases and using foreign keys
"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
CREATE TABLE authors( id int(8) primary key auto_increment, name varchar(255) ) engine = InnoDB DEFAULT CHARSET=utf8;
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)
CREATE TABLE authors( id int(8) primary key auto_increment, name varchar(255) ) engine = InnoDB DEFAULT CHARSET=utf8;
CREATE 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;
Name link fields as singular
Check foreign key is the same field and type and size in both tables
CREATE 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;
my $sth = $dbh->prepare(' INSERT INTO books (title, author) values (?,?) '); $sth->execute( 'A book title', $author_id );
my $sth = $dbh->prepare(' INSERT INTO books (title, author) values (?,?) '); $sth->execute( 'A book title', $author_id );
my $sth = $dbh->prepare(' SELECT title, authors.name as author_name FROM books, authors WHERE books.author = authors.id ');
while( my $book = $sth->fetchrow_hashref() ) { print 'Author of ' . $book->{title} . ' is ' . $book->{author_name} . "\n"; }
my $update = $dbh->prepare(' UPDATE books SET title = ? WHERE id = ? '); $update->execute( 'New title',$book_id );
my $delete = $dbh->prepare(' DELETE FROM books WHERE id = ? '); $delete->execute($book_id);
my $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 it's empty/undef as the object returned will have an empty id, even if your field is auto increment.
my $book = $book_model->create({ title => 'A book title', author => $author_id, });
my $pratchett = $author_model->create({ name => 'Terry Pratchett', });
my $book = $pratchett->create_related( books => { title => 'Another Discworld book', });or
my $book = $pratchett->add_to_books({ title => 'Another Discworld book', });
my $book = $pratchett->create_related( books => { title => 'Another Discworld book', });or
my $book = $pratchett->add_to_books({ title => 'Another Discworld book', });
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"
my $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;
while( my $book = $books_rs->next() ) { print 'Author of ' . $book->title . ' is ' . $book->author->name . "\n"; }
my $books_rs = $book_model->search({ author => $author_id, });
Search takes SQL::Abstract formatted queries
> perldoc SQL::Abstract
$book->update({ title => 'New title', });
$book->delete;
Example of a DBIC Result
Example of a DBIC Result
too much typing! too much maintenance!
too much maintenance!
code for S::L here
Foo::DBIC::Result::Foo = an individual row
Foo::DBIC::ResultSet::Foo = searches / results
example usage code goes here
DBIC_TRACE=1 ./your_script.pl
Foo::DBIC::Result::Authors->table("authors"); Foo::DBIC::Result::Authors->add_columns( id => { data_type => "INT", default_value => undef, is_nullable => 0, size => 8 }, title => { data_type => "VARCHAR", default_value => undef, is_nullable => 1, size => 255, }, ); Foo::DBIC::Result::Authors->set_primary_key("id");
Foo::DBIC::Result::Books->table("books"); Foo::DBIC::Result::Books->add_columns( id => { data_type => "INT", default_value => undef, is_nullable => 0, size => 8 }, name => { data_type => "VARCHAR", default_value => undef, is_nullable => 1, size => 255, }, author => { data_type => "INT", default_value => undef, is_nullable => 1, size => 8 }, ); Foo::DBIC::Result::Books->set_primary_key("id");
Foo::DBIC::Result::Authors->has_many(books => "Foo::DBIC::Books", { "foreign.author" => "self.id" }); Foo::DBIC::Result::Books->belongs_to(author => "Foo::DBIC::Authors", { id => "author" });
INSERT INTO authors (name) VALUES (?): 'Douglas Adams' INSERT INTO books (author, title) VALUES (?, ?): '5', '42'
Foo::DBIC::Result::Books Foo::DBIC::ResultSet::Books Foo::DBIC::Result::Authors Foo::DBIC::ResultSet::Books
package Foo::DBIC::Result::Books; use base 'DBIx::Class'; use strict; use warnings; sub isbn { my $self = shift; # search amazon or something my $api = Amazon::API->book({ title => $self->title }); return $api->isbn; } 1;
print $book->isbn;
package Foo::DBIC::Result::Books; use base 'DBIx::Class'; use strict; use warnings; 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
package Foo::DBIC::Result::Books; use base 'DBIx::Class'; use strict; use warnings; 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 # Automatic see: DBIx::Class::InflateColumn::DateTime # Automatic see: DBIx::Class::InflateColumn::DateTime
$book->date_published(DateTime->now); $book->update;
my $date_published = $book->date_published; print $date_published->month_abbr;Nov
package Foo::DBIC::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;
package Foo::DBIC::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;
package Foo::DBIC::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;
use Foo::DBIC; my $book_model = Foo::DBIC->resultset('Books'); my $book_rs = $book_model->the_ultimate_books; my @books = $book_rs->all;
use Foo::DBIC; my $book_model = Foo::DBIC->resultset('Books'); my $author_model = Foo::DBIC->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;
my $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 ? ) ) ): '5', '%42%' # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
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;
package Foo::DBIC::Result::Authors; use base 'DBIx::Class'; sub new { my ( $class, $attrs ) = @_; # Mess with $attrs my $new = $class->next::method($attrs); return $new } 1;
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; ALTER TABLE `books` DROP `author`
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; ALTER TABLE `books` DROP `author`
package Foo::DBIC::Result::Books; __PACKAGE__->has_many( author_and_books => "Foo::DBIC::Result::AuthorAndBooks", { "foreign.book" => "self.id" }, ); # This is auto generated by Schema::Loader
package Foo::DBIC::Result::Books; __PACKAGE__->has_many( author_and_books => # name of accessor "Foo::DBIC::Result::AuthorAndBooks", # related class { "foreign.book" => "self.id" } # Relationship (magic often works if not # specified, but avoid!) );
package Foo::DBIC::Result::AuthorAndBooks; __PACKAGE__->belongs_to( book => # Accessor name "Foo::DBIC::Result::Books", # Related class { id => "book" } # relationship );
package Foo::DBIC::Result::Books; use base 'DBIx::Class'; __PACKAGE__->many_to_many( authors => "author_and_books", 'author' ); 1; # This is NOT auto generated by Schema::Loader
package Foo::DBIC::Result::Books; use base 'DBIx::Class'; __PACKAGE__->many_to_many( authors # Accessor name => "author_and_books", # has_many 'author' # foreign relationship name ); 1;
package Foo::DBIC::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
#!/usr/bin/perl use Foo::DBIC; my $author_model = Foo::DBIC->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);
Read them closely!
DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'Foo::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "Foo::DBIC::Result::Author" at lib/Foo/DBIC/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::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "Foo::DBIC::Result::Author" at lib/Foo/DBIC/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.
[% author.books.count %]not working?
[% author.books_rs.count %]scalar context
package Your::App::Model::Foo; use base qw(Catalyst::Model::DBIC::Schema); use strict; use warnings; __PACKAGE__->config( schema_class => 'Foo::DBIC', ); 1;
Keep your Schema in a separate package to your Catalyst application
sub action_name : Local { my ($self, $c) = @_; my $model = $c->model('DBIC::Foo'); my $author_model = $model->resultset('Authors'); } 1;