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>Arthur Axel "fREW" Schmidt</h4>
81 <h4>Justin D. Hunter</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>why this talk?</h1>
120 <li>Help avoid mistakes I made!</li>
121 <li>Help learn DBIx::Class faster</li>
122 <li>Make your coding easier</li>
127 <h1>point of note</h1>
128 <p><em>"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</em></p>
129 <p>This talk is about making it easy so we are less likely to get confused</p>
145 <h1>authors table</h1>
146 <pre>CREATE TABLE authors(
147 id int(8) primary key auto_increment,
149 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
154 <p>Name tables as simple plurals (<strong>add an S</strong>) - makes relationships easier to understand</p>
155 <p>(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)</p>
156 <p>Use a character set (<strong>UTF8</strong>) from the start (for international characters)</p>
160 <h1>authors table</h1>
161 <pre>CREATE TABLE author<strong>s</strong>(
162 id int(8) primary key auto_increment,
164 ) engine = <strong>InnoDB</strong> DEFAULT CHARSET=<strong>utf8</strong>;</pre>
169 <pre>CREATE TABLE books(
170 id int(8) primary key auto_increment,
172 author int(8),foreign key (author)
173 references authors(id)
174 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
180 <p>Name link fields as singular</p>
181 <p>Check foreign key is the same field and type and size in both tables</p>
186 <pre>CREATE TABLE books(
187 id int(8) primary key auto_increment,
189 author <strong>int(8)</strong>,<strong>foreign key (<em>author</em>)</strong>
190 <strong>references <em>authors(id)</em></strong>
191 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
195 <h1>CRUD compared</h1>
197 <li><strong>C</strong> - Create</li>
198 <li><strong>R</strong> - Read</li>
199 <li><strong>U</strong> - Update</li>
200 <li><strong>D</strong> - Delete</li>
205 <h1>Manual (SQL)</h1>
209 <h1>manual: create</h1>
210 <pre>my $sth = $dbh->prepare('
217 'A book title', $author_id
222 <h1>manual: create</h1>
223 <pre>my $sth = $dbh->prepare('
224 <strong>INSERT INTO books
226 values (?,?)</strong>
230 'A book title', <strong><em>$author_id</em></strong>
235 <h1>manual: retrieve</h1>
236 <pre>my $sth = $dbh->prepare('
238 authors.name as author_name
240 WHERE books.author = authors.id
245 <h1>manual: retrieve</h1>
246 <pre>while( my $book = $sth->fetchrow_hashref() ) {
250 . $book->{author_name}
256 <h1>manual: update</h1>
257 <pre>my $update = $dbh->prepare('
264 'New title',<strong>$book_id</strong>
269 <h1>manual: delete</h1>
270 <pre>my $delete = $dbh->prepare('
275 $delete->execute(<strong>$book_id</strong>);</pre>
283 <h1>DBIC: create</h1>
284 <pre>my $book = $book_model->create({
285 title => 'A book title',
286 author => $author_id,
288 <p>Look ma, no SQL!</p>
289 <p><strong>Tip:</strong> 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.</p>
293 <h1>DBIC: create</h1>
294 <pre>my $book = $book_model->create({
295 title => 'A book title',
296 author => <strong>$author_id</strong>,
301 <h1>DBIC: create</h1>
302 <pre>my $pratchett = $author_model->create({
303 name => 'Terry Pratchett',
308 <h1>DBIC: create</h1>
309 <pre>my $book = $pratchett->create_related(
311 title => 'Another Discworld book',
314 <pre>my $book = $pratchett->add_to_books({
315 title => 'Another Discworld book',
320 <h1>DBIC: create</h1>
321 <pre>my $book = $pratchett->create_related(
322 <strong>books</strong> => {
323 title => 'Another Discworld book',
326 <pre>my $book = $pratchett->add_to_<strong>books</strong>({
327 title => 'Another Discworld book',
332 <h1>DBIC: retrieve</h1>
333 <p>DBIx::Class - Lots of ways to do the same thing...</p>
334 <p><em>"There is more than one way to do it (TIMTOWTDI, usually pronounced "Tim Toady") is a Perl motto"</em></p>
338 <h1>DBIC: retrieve</h1>
339 <pre>my $book = $book_model->find($book_id);
341 my $book = $book_model->search({
342 title => 'A book title',
345 my @books = $book_model->search({
346 author => $author_id,
351 <h1>DBIC: retrieve</h1>
352 <pre>while( my $book = $books_rs->next() ) {
356 . $book->author->name
362 <h1>DBIC: retrieve</h1>
363 <pre>my $books_rs = $book_model->search({
364 author => $author_id,
366 <p>Search takes SQL::Abstract formatted queries</p>
367 <pre>> perldoc SQL::Abstract</p>
371 <h1>DBIC: update</h1>
372 <pre>$book->update({
373 title => 'New title',
378 <h1>DBIC: delete</h1>
379 <pre>$book->delete;</pre>
383 <h1>Creating models</h1>
387 <pre>Example of a DBIC Result</pre>
391 <pre>Example of a DBIC Result</pre>
395 <p>too much typing! too much maintenance!</p>
396 <p>too much maintenance!</p>
400 <h1>Schema::Loader</h1>
401 <pre>code for S::L here</pre>
405 <h1>splitting logic cleanly</h1>
406 <p>Foo::Schema::Result::Foo = an individual row</p>
407 <p>Foo::Schema::ResultSet::Foo = searches / results</p>
411 <h1>using your Schema</h1>
412 <pre>example usage code goes here</pre>
417 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
421 <h1>Schema::Loader</h1>
422 <pre>Foo::Schema::Result::Authors->table("authors");
423 Foo::Schema::Result::Authors->add_columns(
425 data_type => "INT",
426 default_value => undef,
431 data_type => "VARCHAR",
432 default_value => undef,
437 Foo::Schema::Result::Authors->set_primary_key("id");</pre>
441 <h1>Schema::Loader</h1>
442 <pre>Foo::Schema::Result::Books->table("books");
443 Foo::Schema::Result::Books->add_columns(
445 data_type => "INT",
446 default_value => undef,
451 data_type => "VARCHAR",
452 default_value => undef,
457 data_type => "INT",
458 default_value => undef,
463 Foo::Schema::Result::Books->set_primary_key("id");</pre>
467 <h1>Schema::Loader</h1>
468 <pre>Foo::Schema::Result::Authors->has_many(books => "Foo::Schema::Books",
469 { "foreign.author" => "self.id" });
471 Foo::Schema::Result::Books->belongs_to(author => "Foo::Schema::Authors",
472 { id => "author" });</pre>
476 <h1>SQL - debugging</h1>
477 <pre>INSERT INTO authors (name)
478 VALUES (?): 'Douglas Adams'
480 INSERT INTO books (author, title)
481 VALUES (?, ?): '5', '42'</pre>
486 <pre>Foo::Schema::Result::Books
487 Foo::Schema::ResultSet::Books
488 Foo::Schema::Result::Authors
489 Foo::Schema::ResultSet::Books</pre>
494 <pre>package Foo::Schema::Result::Books;
495 use base 'DBIx::Class';
502 # search amazon or something
503 my $api = Amazon::API->book({
504 title => $self->title
507 return $api->isbn;
515 <pre>print $book->isbn;</pre>
519 <h1>Result:: (inflating)</h1>
520 <pre>package Foo::Schema::Result::Books;
521 use base 'DBIx::Class';
525 use DateTime::Format::MySQL;
527 __PACKAGE__-><strong>inflate_column</strong>(
528 <strong>date_published</strong> => {
529 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
530 deflate => sub { shift->ymd},
533 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
537 <h1>Result:: (inflating)</h1>
538 <pre>package Foo::Schema::Result::Books;
539 use base 'DBIx::Class';
543 use DateTime::Format::MySQL;
545 __PACKAGE__->inflate_column(
546 date_published => {
547 <strong>inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
548 deflate => sub { shift->ymd},</strong>
551 # Automatic see: DBIx::Class::InflateColumn::DateTime
552 # Automatic see: DBIx::Class::InflateColumn::DateTime
553 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
557 <h1>Result:: (deflating)</h1>
558 <pre>$book->date_published(DateTime->now);
559 $book->update;</pre>
563 <h1>Result:: (inflating)</h1>
564 <pre>my $date_published = $book->date_published;
565 print $date_published->month_abbr;</pre>
567 <strong><em>Nov</em></strong>
571 <h1>ResultSets::</h1>
572 <pre>package Foo::Schema::ResultSet::Books;
573 use base 'DBIx::Class::ResultSet';
574 sub the_ultimate_books {
576 return $self->search({ title => { -like => '%42%' } });
579 my ( $self, $author ) = @_;
580 return $self->search({ author => $author->id })
587 <h1>ResultSets::</h1>
588 <pre>package Foo::Schema::<strong>ResultSet::Books</strong>;
589 use base '<strong>DBIx::Class::ResultSet</strong>';
590 sub the_ultimate_books {
592 <strong>return $self->search({ title => { -like => '%42%' } })</strong>
595 my ( $self, $author ) = @_;
596 return $self->search({ author => $author->id })
603 <h1>ResultSets::</h1>
604 <pre>package Foo::Schema::ResultSet::Books;
605 use base 'DBIx::Class::ResultSet';
606 sub the_ultimate_books {
608 return $self->search({ title => { -like => '%42%' } });
611 my ( $self, $author ) = @_;
612 <strong>return $self->search({ author => $author->id })</strong>
619 <h1>ResultSets::</h1>
620 <pre>use Foo::Schema;
621 my $book_model = Foo::Schema->resultset('Books');
622 my $book_rs = $book_model->the_ultimate_books;
623 my @books = $book_rs->all;</pre>
627 <h1>ResultSets::chaining</h1>
628 <pre>use Foo::Schema;
629 my $book_model = Foo::Schema->resultset('Books');
630 my $author_model = Foo::Schema->resultset('Authors');
631 my $author = $author_model->search({ name => 'Douglas Adams' })->single;
632 my $book_rs = $book_model->the_ultimate_books->by_author($author);
633 my @books = $book_rs->all;</pre>
637 <h1>ResultSets::chaining</h1>
638 <pre>my $book_rs = $book_model
639 ->the_ultimate_books
640 ->by_author($author);</pre>
643 <pre>my $book_rs = $book_model
644 ->the_ultimate_books();
645 $book_rs = $book_rs->by_author($author);</pre>
648 # SELECT me.id, me.title, me.date_published, me.author
650 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
651 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
655 <h1>ResultSets::chaining</h1>
656 <pre>my $rs = $book_model
657 ->category('childrens')
658 ->by_author($author)
659 ->published_after('1812')
660 ->first_page_contains('once upon')
661 ->rating_greater_than(4);
663 my @books = $rs->all;</pre>
667 <h1>overloading before new record</h1>
671 <h1>overloading before new record</h1>
672 <pre>package Foo::Schema::Result::Authors;
673 use base 'DBIx::Class';
676 my ( $class, $attrs ) = @_;
678 my $new = $class->next::method($attrs);
685 <h1>relationships</h1>
689 <h1>multiple authors</h1>
693 <h1>a few relationships</h1>
694 (authors -- author_link_to_book -- books)
698 <h1>a few relationships</h1>
703 <h1>new join table</h1>
704 <pre>CREATE TABLE author_and_books(
705 id int(8) primary key auto_increment,
708 foreign key (book) references books(id),
709 foreign key (author) references authors(id)
710 ) engine = InnoDB DEFAULT CHARSET=utf8;
712 ALTER TABLE `books` DROP `author`</pre>
716 <h1>new join table</h1>
717 <pre>CREATE TABLE author_and_books(
718 id int(8) primary key auto_increment,
721 <strong>foreign key (book) references books(id),
722 foreign key (author) references authors(id)</strong>
723 ) engine = InnoDB DEFAULT CHARSET=utf8;
725 ALTER TABLE `books` DROP `author`</pre>
734 <pre>package Foo::Schema::<strong>Result::Books</strong>;
736 __PACKAGE__->has_many( author_and_books => "Foo::Schema::Result::AuthorAndBooks",
737 { "foreign.book" => "self.id" },
740 <strong># This is auto generated by Schema::Loader</strong></pre>
745 <pre>package Foo::Schema::<strong>Result::Books</strong>;
747 __PACKAGE__->has_many(
748 author_and_books => <strong># name of accessor</strong>
749 "Foo::Schema::Result::AuthorAndBooks", <strong># related class</strong>
750 { "foreign.book" => "self.id" } <strong># Relationship (magic often works if not
751 # specified, but avoid!)</strong>
762 <pre>package Foo::Schema::<strong>Result::AuthorAndBooks</strong>;
764 __PACKAGE__->belongs_to(
765 book => <strong># Accessor name</strong>
766 "Foo::Schema::Result::Books", <strong># Related class</strong>
767 { id => "book" } <strong># relationship</strong>
773 <h1>same for Authors</h1>
777 <h1>with no coding...</h1>
781 <h1>many_to_many</h1>
785 <h1>many_to_many</h1>
786 <pre>package Foo::Schema::<strong>Result::Books</strong>;
787 use base 'DBIx::Class';
789 __PACKAGE__->many_to_many(
790 authors => "author_and_books", 'author'
795 <strong> # This is <em>NOT</em> auto generated by Schema::Loader </strong></pre>
799 <h1>many_to_many</h1>
800 <pre>package Foo::Schema::<strong>Result::Books</strong>;
801 use base 'DBIx::Class';
803 __PACKAGE__->many_to_many(
804 authors <strong># Accessor name</strong>
805 => "author_and_books", <strong># has_many</strong>
806 'author' <strong># foreign relationship name</strong>
813 <h1>many_to_many</h1>
814 <pre>package Foo::Schema::Result::Authors;
815 use base 'DBIx::Class';
817 __PACKAGE__->many_to_many(
818 "books" <strong># Accessor Name</strong>
819 => "author_and_books", <strong># has_many accessor_name</strong>
820 'book' <strong># foreign relationship name</strong>
825 <strong># This is <em>NOT</em> auto generated by Schema::Loader</strong></pre>
829 <h1>using many_to_many</h1>
834 my $author_model = Foo::Schema->resultset('Authors');
835 my $author = $author_model->search({
836 name => 'Douglas Adams',
838 $author->add_to_books({
839 title => 'A new book',
844 <h1>using many_to_many</h1>
845 <pre>my $author = $author_model->search({
846 name => 'Douglas Adams',
848 <strong>$author->add_to_books({
849 title => 'A new book',
852 # SELECT me.id, me.name FROM authors me
853 # WHERE ( name = ? ): 'Douglas Adams';
854 # INSERT INTO books (title) VALUES (?): 'A new book';
855 # INSERT INTO author_and_books (author, book)
856 # VALUES (?, ?): '5', '2';</pre>
860 <h1>using many_to_many</h1>
861 <pre>$author->add_to_books($book);
863 $book->add_to_authors($author_1);
864 $book->add_to_authors($author_2);</pre>
868 <h1>in 16 lines of code</h1>
873 <p>Read them closely!</p>
877 <h1>error messages</h1>
878 <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>
882 <h1>error messages</h1>
883 <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>
889 <li>Turn on debugging</li>
890 <li>Read error messages (sometimes useful!)</li>
891 <li>Check field names</li>
892 <li>Check package names</li>
893 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
898 <h1>bonus slides!</h1>
902 <h1>Template Toolkit</h1>
904 <li><pre>[% author.books.count %]</pre> not working?</li>
905 <li>TT all methods are called in list context</li>
906 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
907 <li><em>Available for all relationships</em></li>
913 <pre>package Your::App::Model::<strong>Foo</strong>;
914 use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
919 __PACKAGE__->config(
920 schema_class => '<strong>Foo::Schema</strong>',
924 <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
929 <pre>sub action_name : Local {
932 my $model = $c->model('Schema::Foo');
933 my $author_model = $model->resultset('Authors');