1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
4 <html xmlns="http://www.w3.org/1999/xhtml">
7 <title>DBIx::Class (aka DBIC)</title>
9 <meta name="contributor" content="youcan[64]netzgesta[46]de" />
10 <meta name="publisher" content="s5.netzgesta.de" />
11 <meta name="description" content="S5 1.3 is a very flexible and lightweight slide show system available for anyone to use (including transitions and scalable fonts and images)" />
12 <meta name="keywords" content="S5, version 1.3, slide show, presentation-mode, projection-mode, powerpoint-like, scala-like, keynote-like, incremental display, scalable fonts, scalable images, transitions, notes, osf, xoxo, css, javascript, xhtml, public domain" />
14 <meta name="generator" content="S5" />
15 <meta name="version" content="1" />
16 <meta name="subject" content="DBIx::Class" />
17 <meta name="author" content="See first slide" />
18 <meta name="company" content="N/A" />
19 <meta name="robots" content="index, follow" />
20 <meta name="revisit-after" content="7 days" />
21 <!-- meta additionally -->
22 <meta http-equiv="content-type" content="text/html; charset=utf-8" />
23 <meta http-equiv="Content-Script-Type" content="text/javascript" />
24 <meta http-equiv="Content-Style-Type" content="text/css" /><!-- configuration parameters -->
25 <meta name="defaultView" content="slideshow" />
26 <meta name="controlVis" content="hidden" />
27 <!-- configuration transition extension -->
28 <meta name="tranSitions" content="true" />
29 <meta name="fadeDuration" content="500" />
30 <meta name="incrDuration" content="250" />
31 <!-- configuration autoplay extension -->
32 <meta name="autoMatic" content="false" />
33 <meta name="playLoop" content="true" />
34 <meta name="playDelay" content="10" />
35 <!-- configuration audio extension -->
36 <meta name="audioSupport" content="false" />
37 <meta name="audioVolume" content="0" />
38 <meta name="audioError" content="false" />
39 <!-- configuration audio debug -->
40 <meta name="audioDebug" content="false" />
41 <!-- style sheet links -->
42 <link rel="stylesheet" href="ui/scala_utf/slides.css" type="text/css" media="projection" id="slideProj" />
43 <link rel="stylesheet" href="ui/scala_utf/outline.css" type="text/css" media="screen" id="outlineStyle" />
44 <link rel="stylesheet" href="ui/scala_utf/print.css" type="text/css" media="print" id="slidePrint" />
45 <link rel="stylesheet" href="ui/scala_utf/opera.css" type="text/css" media="projection" id="operaFix" />
46 <!-- embedded styles -->
47 <style type="text/css" media="all">
48 .imgcon {width: 100%; margin: 0 auto; padding: 0; text-align: center;}
49 #anim {width: 33%; height: 320px; position: relative;}
50 #anim img {position: absolute; top: 0px; left: 0px;}
55 <script src="ui/scala_utf/slides.js" type="text/javascript"></script>
60 <div id="controls"><!-- DO NOT EDIT --></div>
61 <div id="currentSlide"><!-- DO NOT EDIT --></div>
62 <div id="header"></div>
64 <h1>DBIx::Class Introduction</h1>
65 <h2>YAPC::NA 2010</h2>
69 <div class="presentation">
72 <h1>DBIX::Class (aka DBIC)</h1>
73 <h3>for (advanced) beginners</h3>
78 <h4>Originally Leo Lapworth @ LPW 2009</h4>
79 <h4>Matthew S. Trout</h4>
80 <h4>Justin D. Hunter</h4>
81 <h4>Arthur Axel "fREW" Schmidt</h4>
86 <p>You know a little about Perl and using objects</p>
87 <p>You know a little bit about databases and using foreign keys</p>
90 <li>How many people have designed a database with Foreign Keys?</li>
91 <li>How many people have used any ORM?<ul>
94 <li> Class::DBI? </li>
100 <li> DataMapper? </li>
101 <li>(N)Hibernate?</li>
108 <h1>DBIx::Class?</h1>
110 <li>ORM (object relational mapper)</li>
111 <li>SQL <-> OO (using objects instead of SQL)</li>
112 <li>Simple, powerful, complex, fab and confusing</li>
113 <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
118 <h1>point of note</h1>
119 <p><em>"Debugging is twice as hard as writing the code in the first
120 place. Therefore, if you write the code as cleverly as possible,
121 you are, by definition, not smart enough to debug it." - Brian
122 W. Kernighan</em></p>
124 <p>This talk is about making it easy so we are less likely to get
129 <h1>Examples Table Setup</h1>
134 <em>MySQL not recommended</em>
138 <h1>authors table</h1>
139 <pre>CREATE TABLE authors(
140 id int(8) primary key auto_increment,
142 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
147 <p>Name tables as simple plurals (<strong>add an S</strong>) - makes relationships easier to understand</p>
148 <p>Use a character set (<strong>UTF8</strong>) from the start (for international characters)</p>
152 <h1>authors table</h1>
153 <pre>CREATE TABLE author<strong>s</strong>(
154 id int(8) primary key auto_increment,
156 ) engine = <strong>InnoDB</strong> DEFAULT CHARSET=<strong>utf8</strong>;</pre>
161 <pre>CREATE TABLE books(
162 id int(8) primary key auto_increment,
164 author_id int(8),foreign key (author)
165 references authors(id)
166 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
172 <p>Name link fields as singular</p>
173 <p>Ensure foreign key is the same type and size in both tables</p>
178 <pre>CREATE TABLE books(
179 id int(8) primary key auto_increment,
181 author_id <strong>int(8)</strong>,<strong>foreign key (<em>author</em>)</strong>
182 <strong>references <em>authors(id)</em></strong>
183 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
187 <h1>CRUD compared</h1>
189 <li><strong>C</strong> - Create</li>
190 <li><strong>R</strong> - Read</li>
191 <li><strong>U</strong> - Update</li>
192 <li><strong>D</strong> - Delete</li>
197 <h1>Manual (SQL)</h1>
202 <pre>my $sth = $dbh->prepare('
209 'A book title', $author_id
215 <pre>my $sth = $dbh->prepare('
217 authors.name as author_name
219 WHERE books.author = authors.id
225 <pre>while( my $book = $sth->fetchrow_hashref() ) {
229 . $book->{author_name}
236 <pre>my $update = $dbh->prepare('
243 'New title',<strong>$book_id</strong>
249 <pre>my $delete = $dbh->prepare('
254 $delete->execute(<strong>$book_id</strong>);</pre>
262 <h1>DBIC: Create</h1>
263 <pre>my $book = $book_model->create({
264 title => 'A book title',
265 author => $author_id,
267 <p>Look ma, no SQL!</p>
271 <h1>DBIC: Create</h1>
272 <pre>my $pratchett = $author_model->create({
273 name => 'Terry Pratchett',
278 <h1>DBIC: Create</h1>
279 <pre>my $book = $pratchett->create_related(
280 <strong>books</strong> => {
281 title => 'Another Discworld book',
284 <pre>my $book = $pratchett->add_to_<strong>books</strong>({
285 title => 'Another Discworld book',
291 <p>DBIx::Class - Lots of ways to do the same thing...</p>
292 <p><em>"There is more than one way to do it (TIMTOWTDI, usually pronounced "Tim Toady") is a Perl motto"</em></p>
297 <pre>my $book = $book_model->find($book_id);
299 my $book = $book_model->search({
300 title => 'A book title',
301 }, { rows => 1 })->single;
303 my @books = $book_model->search({
304 author => $author_id,
310 <pre>while( my $book = $books_rs->next ) {
314 . $book->author->name
321 <pre>my $books_rs = $book_rs->search({
322 author => $author_id,
324 <p>Search takes SQL::Abstract formatted queries</p>
325 <pre>> perldoc SQL::Abstract</p>
329 <h1>DBIC: Update</h1>
330 <pre>$book->update({
331 title => 'New title',
336 <h1>DBIC: Delete</h1>
337 <pre>$book->delete;</pre>
341 <h1>Creating models</h1>
345 <pre>package Foo::Schema::Result::Author;
346 use strict; use warnings;
347 __PACKAGE__->table('authors');
348 __PACKAGE__->add_columns(
350 data_type => 'int',
354 data_type => 'varchar',
359 __PACKAGE__->set_primary_key('id');
360 __PACKAGE__->has_many( books =>
361 'Foo::Schema::Result::Book', 'author_id'
368 <pre>package Foo::Schema::Result::Book;
369 use strict; use warnings;
370 __PACKAGE__->table('books');
371 __PACKAGE__->add_columns(
373 data_type => 'int',
377 data_type => 'varchar',
382 data_type => 'int',
384 is_nullable => 1, # <-- probably should be 0
387 __PACKAGE__->set_primary_key('id');
388 __PACKAGE__->belongs_to( author =>
389 'Foo::Schema::Result::Author', 'author_id'
398 <pre>my $schema = Foo::Schema->connect($dsn, $user, $pass);
401 <p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
405 <h1>Schema::Loader</h1>
407 <pre>package Foo::Schema;
408 use strict; use warnings;
409 use base 'DBIx::Class::Schema::Loader';
410 __PACKAGE__->loader_options({
412 debug => $ENV{DBIC_TRACE},
418 my $schema = Foo::Schema->connect($dsn, $user, $pass);
423 <h1>Splitting Logic Cleanly</h1>
424 <p>Foo::Schema::Result::Bar = individual row</p>
425 <p>Foo::Schema::ResultSet::Bar = searches / table </p>
429 <h1>Using your Schema</h1>
431 use strict; use warnings;
434 my $schema = Foo::Schema->connect($dns, $user, $pass);
435 my $author_rs = $schema->resultset('Author');
436 my $author = $author_rs->create({
437 name => 'Douglas Adams',
439 my $book = $author->add_to_books({
447 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
451 <h1>SQL - debugging</h1>
452 <pre>INSERT INTO authors (name)
453 VALUES (?): 'Douglas Adams'
455 INSERT INTO books (author, title)
456 VALUES (?, ?): '5', '42'</pre>
461 <pre>Foo::Schema::Result::Book
462 Foo::Schema::ResultSet::Book
463 Foo::Schema::Result::Author
464 Foo::Schema::ResultSet::Book</pre>
469 <pre>package Foo::Schema::Result::Book;
470 use base 'DBIx::Class::Core';
479 # search amazon or something
480 my $api = Amazon::API->book({
481 title => $self->title
484 return $api->isbn;
492 <pre>print $book->isbn;</pre>
496 <h1>Result:: (inflating)</h1>
497 <pre>package Foo::Schema::Result::Book;
498 use base 'DBIx::Class::Core';
504 __PACKAGE__->load_components('InflateColumn');
505 use DateTime::Format::MySQL;
507 __PACKAGE__-><strong>inflate_column</strong>(
508 <strong>date_published</strong> => {
509 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
510 deflate => sub { shift->ymd},
513 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
517 <h1>Result:: (deflating)</h1>
518 <pre>$book->date_published(DateTime->now);
519 $book->update;</pre>
523 <h1>Result:: (inflating)</h1>
524 <pre>my $date_published = $book->date_published;
525 print $date_published->month_abbr;</pre>
527 <strong><em>Nov</em></strong>
531 <h1>ResultSets::</h1>
532 <pre>package Foo::Schema::ResultSet::Books;
533 use base 'DBIx::Class::ResultSet';
534 sub the_ultimate_books {
536 return $self->search({ title => { -like => '%42%' } });
539 my ( $self, $author ) = @_;
540 return $self->search({ author => $author->id })
547 <h1>ResultSets::</h1>
548 <pre>use Foo::Schema;
549 my $schema = Foo::Schema->connect(...);
550 my $book_rs = Foo::Schema->resultset('Book');
551 my $book_search = $book_rs->the_ultimate_books;
552 my @books = $book_search->all;</pre>
556 <h1>ResultSets: Chaining</h1>
558 my $book_rs = $schema->resultset('Book');
559 my $author_rs = $schema->resultset('Author');
560 my $author = $author_model->search({ name => 'Douglas Adams' })->single;
561 my $book_rs = $book_model->the_ultimate_books->by_author($author);
562 my @books = $book_rs->all;</pre>
566 <h1>ResultSets: Chaining</h1>
567 <pre>$book_rs = $schema->resultset('Book')
568 ->the_ultimate_books
569 ->by_author($author);</pre>
572 <pre>my $book_rs = $schema->resultset('Book')
573 ->the_ultimate_books;
574 $book_rs = $book_rs->by_author($author);</pre>
577 # SELECT me.id, me.title, me.date_published, me.author
579 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
580 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
584 <h1>ResultSets::chaining</h1>
585 <pre>my $rs = $book_model
586 ->category('childrens')
587 ->by_author($author)
588 ->published_after('1812')
589 ->first_page_contains('once upon')
590 ->rating_greater_than(4);
592 my @books = $rs->all;</pre>
596 <h1>overloading before new record</h1>
597 <pre>package Foo::Schema::Result::Author;
598 use base 'DBIx::Class::Core';
601 my ( $class, $attrs ) = @_;
603 my $new = $class->next::method($attrs);
610 <h1>Relationships</h1>
614 <h1>Multiple Authors</h1>
618 <h1>a few relationships</h1>
619 <img src="img/afewrels.png" />
624 <pre>CREATE TABLE author_and_books(
627 foreign key (book_id) references books(id),
628 foreign key (author_id) references authors(id)
629 ) engine = InnoDB DEFAULT CHARSET=utf8;
631 ALTER TABLE `books` DROP `author`;</pre>
636 <img src="img/hasmany1.png" />
641 <pre>package Foo::Schema::<strong>Result::Book</strong>;
643 __PACKAGE__->has_many( author_and_books =>
644 'Foo::Schema::Result::Author_Book', 'book_id'
650 <img src="img/belongsto1.png" />
655 <pre>package Foo::Schema::<strong>Result::Author_Book</strong>;
657 __PACKAGE__->belongs_to(
659 'Foo::Schema::Result::Book', 'book_id'
665 <h1>same for Authors</h1>
666 <img src="img/authors.png" />
670 <h1>many_to_many</h1>
671 <img src="img/m2m.png" />
675 <h1>many_to_many</h1>
676 <pre>package Foo::Schema::<strong>Result::Book</strong>;
677 use base 'DBIx::Class::Core';
679 __PACKAGE__->many_to_many(
680 authors => 'author_and_books', 'author'
688 <h1>many_to_many</h1>
689 <pre>package Foo::Schema::<strong>Result::Book</strong>;
690 use base 'DBIx::Class::Core';
692 __PACKAGE__->many_to_many(
693 authors <strong># Accessor name</strong>
694 => "author_and_books", <strong># has_many</strong>
695 'author' <strong># foreign relationship name</strong>
702 <h1>many_to_many</h1>
703 <pre>package Foo::Schema::Result::Author;
704 use base 'DBIx::Class::Core';
706 __PACKAGE__->many_to_many(
707 "books" <strong># Accessor Name</strong>
708 => "author_and_books", <strong># has_many accessor_name</strong>
709 'book' <strong># foreign relationship name</strong>
717 <h1>Using many_to_many</h1>
720 my $schema = Foo::Schema->connect(...);
721 my $author_rs = $schema->resultset('Authors');
722 my $author = $author_model->search({
723 name => 'Douglas Adams',
725 $author->add_to_books({
726 title => 'A new book',
731 <h1>using many_to_many</h1>
732 <pre>my $author = $author_model->search({
733 name => 'Douglas Adams',
735 <strong>$author->add_to_books({
736 title => 'A new book',
739 # SELECT me.id, me.name FROM authors me
740 # WHERE ( name = ? ): 'Douglas Adams';
741 # INSERT INTO books (title) VALUES (?): 'A new book';
742 # INSERT INTO author_and_books (author, book)
743 # VALUES (?, ?): '5', '2';</pre>
747 <h1>using many_to_many</h1>
748 <pre>$author->add_to_books($book);
750 $book->add_to_authors($author_1);
751 $book->add_to_authors($author_2);</pre>
756 <p>Read them closely!</p>
760 <h1>error messages</h1>
761 <pre>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.</pre>
765 <h1>error messages</h1>
766 <pre>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::<strong>Result::Author</strong>" at lib/Foo/Schema/<strong>Result/Authors.pm</strong> line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
772 <li>Turn on debugging</li>
773 <li>Read error messages (sometimes useful!)</li>
774 <li>Check field names</li>
775 <li>Check package names</li>
776 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
781 <h1>LOTS more Features</h1>
783 <li>FilterColumn</li>
784 <li>Transactions</li>
785 <li>HashRefInflator</li>
787 <li>ResultSetColumn</li>
792 <h1>bonus slides!</h1>
796 <h1>Template Toolkit</h1>
798 <li><pre>[% author.books.count %]</pre> not working?</li>
799 <li>TT all methods are called in list context</li>
800 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
801 <li><em>Available for all relationships</em></li>
807 <pre>package Your::App::Model::<strong>Foo</strong>;
808 use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
813 __PACKAGE__->config(
814 schema_class => '<strong>Foo::Schema</strong>',
818 <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
823 <pre>sub action_name : Local {
826 my $model = $c->model('Schema::Foo');
827 my $author_model = $model->resultset('Authors');