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>
74 <h4>Leo Lapworth @ LPW 2009</h4>
75 <h4><a href="http://leo.cuckoo.org/projects">http://leo.cuckoo.org/projects</a></h4>
76 <div class="handout"></div>
81 <p>You know a little about Perl and using objects</p>
82 <p>You know a little bit about databases and using foreign keys</p>
88 <li>ORM (object relational mapper)</li>
89 <li>SQL <-> OO (using objects instead of SQL)</li>
90 <li>Simple, powerful, complex, fab and confusing</li>
91 <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
96 <h1>why this talk?</h1>
98 <li>Help avoid mistakes I made!</li>
99 <li>Help learn DBIx::Class faster</li>
100 <li>Make your coding easier</li>
105 <h1>point of note</h1>
106 <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>
107 <p>This talk is about making it easy so we are less likely to get confused</p>
123 <h1>authors table</h1>
124 <pre>CREATE TABLE authors(
125 id int(8) primary key auto_increment,
127 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
132 <p>Name tables as simple plurals (<strong>add an S</strong>) - makes relationships easier to understand</p>
133 <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>
134 <p>Use a character set (<strong>UTF8</strong>) from the start (for international characters)</p>
138 <h1>authors table</h1>
139 <pre>CREATE TABLE author<strong>s</strong>(
140 id int(8) primary key auto_increment,
142 ) engine = <strong>InnoDB</strong> DEFAULT CHARSET=<strong>utf8</strong>;</pre>
147 <pre>CREATE TABLE books(
148 id int(8) primary key auto_increment,
150 author int(8),foreign key (author)
151 references authors(id)
152 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
158 <p>Name link fields as singular</p>
159 <p>Check foreign key is the same field and type and size in both tables</p>
164 <pre>CREATE TABLE books(
165 id int(8) primary key auto_increment,
167 author <strong>int(8)</strong>,<strong>foreign key (<em>author</em>)</strong>
168 <strong>references <em>authors(id)</em></strong>
169 ) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
173 <h1>CRUD compared</h1>
175 <li><strong>C</strong> - Create</li>
176 <li><strong>R</strong> - Read</li>
177 <li><strong>U</strong> - Update</li>
178 <li><strong>D</strong> - Delete</li>
183 <h1>Manual (SQL)</h1>
187 <h1>manual: create</h1>
188 <pre>my $sth = $dbh->prepare('
195 'A book title', $author_id
200 <h1>manual: create</h1>
201 <pre>my $sth = $dbh->prepare('
202 <strong>INSERT INTO books
204 values (?,?)</strong>
208 'A book title', <strong><em>$author_id</em></strong>
213 <h1>manual: retrieve</h1>
214 <pre>my $sth = $dbh->prepare('
216 authors.name as author_name
218 WHERE books.author = authors.id
223 <h1>manual: retrieve</h1>
224 <pre>while( my $book = $sth->fetchrow_hashref() ) {
228 . $book->{author_name}
234 <h1>manual: update</h1>
235 <pre>my $update = $dbh->prepare('
242 'New title',<strong>$book_id</strong>
247 <h1>manual: delete</h1>
248 <pre>my $delete = $dbh->prepare('
253 $delete->execute(<strong>$book_id</strong>);</pre>
261 <h1>DBIC: create</h1>
262 <pre>my $book = $book_model->create({
263 title => 'A book title',
264 author => $author_id,
266 <p>Look ma, no SQL!</p>
267 <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>
271 <h1>DBIC: create</h1>
272 <pre>my $book = $book_model->create({
273 title => 'A book title',
274 author => <strong>$author_id</strong>,
279 <h1>DBIC: create</h1>
280 <pre>my $pratchett = $author_model->create({
281 name => 'Terry Pratchett',
286 <h1>DBIC: create</h1>
287 <pre>my $book = $pratchett->create_related(
289 title => 'Another Discworld book',
292 <pre>my $book = $pratchett->add_to_books({
293 title => 'Another Discworld book',
298 <h1>DBIC: create</h1>
299 <pre>my $book = $pratchett->create_related(
300 <strong>books</strong> => {
301 title => 'Another Discworld book',
304 <pre>my $book = $pratchett->add_to_<strong>books</strong>({
305 title => 'Another Discworld book',
310 <h1>DBIC: retrieve</h1>
311 <p>DBIx::Class - Lots of ways to do the same thing...</p>
312 <p><em>"There is more than one way to do it (TIMTOWTDI, usually pronounced "Tim Toady") is a Perl motto"</em></p>
316 <h1>DBIC: retrieve</h1>
317 <pre>my $book = $book_model->find($book_id);
319 my $book = $book_model->search({
320 title => 'A book title',
323 my @books = $book_model->search({
324 author => $author_id,
329 <h1>DBIC: retrieve</h1>
330 <pre>while( my $book = $books_rs->next() ) {
334 . $book->author->name
340 <h1>DBIC: retrieve</h1>
341 <pre>my $books_rs = $book_model->search({
342 author => $author_id,
344 <p>Search takes SQL::Abstract formatted queries</p>
345 <pre>> perldoc SQL::Abstract</p>
349 <h1>DBIC: update</h1>
350 <pre>$book->update({
351 title => 'New title',
356 <h1>DBIC: delete</h1>
357 <pre>$book->delete;</pre>
361 <h1>Creating models</h1>
365 <pre>Example of a DBIC Result</pre>
369 <pre>Example of a DBIC Result</pre>
373 <p>too much typing! too much maintenance!</p>
374 <p>too much maintenance!</p>
378 <h1>Schema::Loader</h1>
379 <pre>code for S::L here</pre>
383 <h1>splitting logic cleanly</h1>
384 <p>LPW::DBIC::Result::Foo = an individual row</p>
385 <p>LPW::DBIC::ResultSet::Foo = searches / results</p>
389 <h1>using your Schema</h1>
390 <pre>example usage code goes here</pre>
395 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
399 <h1>Schema::Loader</h1>
400 <pre>LPW::DBIC::Result::Authors->table("authors");
401 LPW::DBIC::Result::Authors->add_columns(
403 data_type => "INT",
404 default_value => undef,
409 data_type => "VARCHAR",
410 default_value => undef,
415 LPW::DBIC::Result::Authors->set_primary_key("id");</pre>
419 <h1>Schema::Loader</h1>
420 <pre>LPW::DBIC::Result::Books->table("books");
421 LPW::DBIC::Result::Books->add_columns(
423 data_type => "INT",
424 default_value => undef,
429 data_type => "VARCHAR",
430 default_value => undef,
435 data_type => "INT",
436 default_value => undef,
441 LPW::DBIC::Result::Books->set_primary_key("id");</pre>
445 <h1>Schema::Loader</h1>
446 <pre>LPW::DBIC::Result::Authors->has_many(books => "LPW::DBIC::Books",
447 { "foreign.author" => "self.id" });
449 LPW::DBIC::Result::Books->belongs_to(author => "LPW::DBIC::Authors",
450 { id => "author" });</pre>
454 <h1>SQL - debugging</h1>
455 <pre>INSERT INTO authors (name)
456 VALUES (?): 'Douglas Adams'
458 INSERT INTO books (author, title)
459 VALUES (?, ?): '5', '42'</pre>
464 <pre>LPW::DBIC::Result::Books
465 LPW::DBIC::ResultSet::Books
466 LPW::DBIC::Result::Authors
467 LPW::DBIC::ResultSet::Books</pre>
472 <pre>package LPW::DBIC::Result::Books;
473 use base 'DBIx::Class';
480 # search amazon or something
481 my $api = Amazon::API->book({
482 title => $self->title
485 return $api->isbn;
493 <pre>print $book->isbn;</pre>
497 <h1>Result:: (inflating)</h1>
498 <pre>package LPW::DBIC::Result::Books;
499 use base 'DBIx::Class';
503 use DateTime::Format::MySQL;
505 __PACKAGE__-><strong>inflate_column</strong>(
506 <strong>date_published</strong> => {
507 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
508 deflate => sub { shift->ymd},
511 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
515 <h1>Result:: (inflating)</h1>
516 <pre>package LPW::DBIC::Result::Books;
517 use base 'DBIx::Class';
521 use DateTime::Format::MySQL;
523 __PACKAGE__->inflate_column(
524 date_published => {
525 <strong>inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
526 deflate => sub { shift->ymd},</strong>
529 # Automatic see: DBIx::Class::InflateColumn::DateTime
530 # Automatic see: DBIx::Class::InflateColumn::DateTime
531 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
535 <h1>Result:: (deflating)</h1>
536 <pre>$book->date_published(DateTime->now);
537 $book->update;</pre>
541 <h1>Result:: (inflating)</h1>
542 <pre>my $date_published = $book->date_published;
543 print $date_published->month_abbr;</pre>
545 <strong><em>Nov</em></strong>
549 <h1>ResultSets::</h1>
550 <pre>package LPW::DBIC::ResultSet::Books;
551 use base 'DBIx::Class::ResultSet';
552 sub the_ultimate_books {
554 return $self->search({ title => { -like => '%42%' } });
557 my ( $self, $author ) = @_;
558 return $self->search({ author => $author->id })
565 <h1>ResultSets::</h1>
566 <pre>package LPW::DBIC::<strong>ResultSet::Books</strong>;
567 use base '<strong>DBIx::Class::ResultSet</strong>';
568 sub the_ultimate_books {
570 <strong>return $self->search({ title => { -like => '%42%' } })</strong>
573 my ( $self, $author ) = @_;
574 return $self->search({ author => $author->id })
581 <h1>ResultSets::</h1>
582 <pre>package LPW::DBIC::ResultSet::Books;
583 use base 'DBIx::Class::ResultSet';
584 sub the_ultimate_books {
586 return $self->search({ title => { -like => '%42%' } });
589 my ( $self, $author ) = @_;
590 <strong>return $self->search({ author => $author->id })</strong>
597 <h1>ResultSets::</h1>
599 my $book_model = LPW::DBIC->resultset('Books');
600 my $book_rs = $book_model->the_ultimate_books;
601 my @books = $book_rs->all;</pre>
605 <h1>ResultSets::chaining</h1>
607 my $book_model = LPW::DBIC->resultset('Books');
608 my $author_model = LPW::DBIC->resultset('Authors');
609 my $author = $author_model->search({ name => 'Douglas Adams' })->single;
610 my $book_rs = $book_model->the_ultimate_books->by_author($author);
611 my @books = $book_rs->all;</pre>
615 <h1>ResultSets::chaining</h1>
616 <pre>my $book_rs = $book_model
617 ->the_ultimate_books
618 ->by_author($author);</pre>
621 <pre>my $book_rs = $book_model
622 ->the_ultimate_books();
623 $book_rs = $book_rs->by_author($author);</pre>
626 # SELECT me.id, me.title, me.date_published, me.author
628 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
629 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
633 <h1>ResultSets::chaining</h1>
634 <pre>my $rs = $book_model
635 ->category('childrens')
636 ->by_author($author)
637 ->published_after('1812')
638 ->first_page_contains('once upon')
639 ->rating_greater_than(4);
641 my @books = $rs->all;</pre>
645 <h1>overloading before new record</h1>
649 <h1>overloading before new record</h1>
650 <pre>package LPW::DBIC::Result::Authors;
651 use base 'DBIx::Class';
654 my ( $class, $attrs ) = @_;
656 my $new = $class->next::method($attrs);
663 <h1>relationships</h1>
667 <h1>multiple authors</h1>
671 <h1>a few relationships</h1>
672 (authors -- author_link_to_book -- books)
676 <h1>a few relationships</h1>
681 <h1>new join table</h1>
682 <pre>CREATE TABLE author_and_books(
683 id int(8) primary key auto_increment,
686 foreign key (book) references books(id),
687 foreign key (author) references authors(id)
688 ) engine = InnoDB DEFAULT CHARSET=utf8;
690 ALTER TABLE `books` DROP `author`</pre>
694 <h1>new join table</h1>
695 <pre>CREATE TABLE author_and_books(
696 id int(8) primary key auto_increment,
699 <strong>foreign key (book) references books(id),
700 foreign key (author) references authors(id)</strong>
701 ) engine = InnoDB DEFAULT CHARSET=utf8;
703 ALTER TABLE `books` DROP `author`</pre>
712 <pre>package LPW::DBIC::<strong>Result::Books</strong>;
714 __PACKAGE__->has_many( author_and_books => "LPW::DBIC::Result::AuthorAndBooks",
715 { "foreign.book" => "self.id" },
718 <strong># This is auto generated by Schema::Loader</strong></pre>
723 <pre>package LPW::DBIC::<strong>Result::Books</strong>;
725 __PACKAGE__->has_many(
726 author_and_books => <strong># name of accessor</strong>
727 "LPW::DBIC::Result::AuthorAndBooks", <strong># related class</strong>
728 { "foreign.book" => "self.id" } <strong># Relationship (magic often works if not
729 # specified, but avoid!)</strong>
740 <pre>package LPW::DBIC::<strong>Result::AuthorAndBooks</strong>;
742 __PACKAGE__->belongs_to(
743 book => <strong># Accessor name</strong>
744 "LPW::DBIC::Result::Books", <strong># Related class</strong>
745 { id => "book" } <strong># relationship</strong>
751 <h1>same for Authors</h1>
755 <h1>with no coding...</h1>
759 <h1>many_to_many</h1>
763 <h1>many_to_many</h1>
764 <pre>package LPW::DBIC::<strong>Result::Books</strong>;
765 use base 'DBIx::Class';
767 __PACKAGE__->many_to_many(
768 authors => "author_and_books", 'author'
773 <strong> # This is <em>NOT</em> auto generated by Schema::Loader </strong></pre>
777 <h1>many_to_many</h1>
778 <pre>package LPW::DBIC::<strong>Result::Books</strong>;
779 use base 'DBIx::Class';
781 __PACKAGE__->many_to_many(
782 authors <strong># Accessor name</strong>
783 => "author_and_books", <strong># has_many</strong>
784 'author' <strong># foreign relationship name</strong>
791 <h1>many_to_many</h1>
792 <pre>package LPW::DBIC::Result::Authors;
793 use base 'DBIx::Class';
795 __PACKAGE__->many_to_many(
796 "books" <strong># Accessor Name</strong>
797 => "author_and_books", <strong># has_many accessor_name</strong>
798 'book' <strong># foreign relationship name</strong>
803 <strong># This is <em>NOT</em> auto generated by Schema::Loader</strong></pre>
807 <h1>using many_to_many</h1>
812 my $author_model = LPW::DBIC->resultset('Authors');
813 my $author = $author_model->search({
814 name => 'Douglas Adams',
816 $author->add_to_books({
817 title => 'A new book',
822 <h1>using many_to_many</h1>
823 <pre>my $author = $author_model->search({
824 name => 'Douglas Adams',
826 <strong>$author->add_to_books({
827 title => 'A new book',
830 # SELECT me.id, me.name FROM authors me
831 # WHERE ( name = ? ): 'Douglas Adams';
832 # INSERT INTO books (title) VALUES (?): 'A new book';
833 # INSERT INTO author_and_books (author, book)
834 # VALUES (?, ?): '5', '2';</pre>
838 <h1>using many_to_many</h1>
839 <pre>$author->add_to_books($book);
841 $book->add_to_authors($author_1);
842 $book->add_to_authors($author_2);</pre>
846 <h1>in 16 lines of code</h1>
851 <p>Read them closely!</p>
855 <h1>error messages</h1>
856 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'LPW::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "LPW::DBIC::Result::Author" at lib/LPW/DBIC/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
860 <h1>error messages</h1>
861 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'LPW::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "LPW::DBIC::<strong>Result::Author</strong>" at lib/LPW/DBIC/<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>
867 <li>Turn on debugging</li>
868 <li>Read error messages (sometimes useful!)</li>
869 <li>Check field names</li>
870 <li>Check package names</li>
871 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
876 <h1>bonus slides!</h1>
880 <h1>Template Toolkit</h1>
882 <li><pre>[% author.books.count %]</pre> not working?</li>
883 <li>TT all methods are called in list context</li>
884 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
885 <li><em>Available for all relationships</em></li>
891 <pre>package Your::App::Model::<strong>LPW</strong>;
892 use base qw(<strong>Catalyst::Model::DBIC::Schema</strong>);
897 __PACKAGE__->config(
898 schema_class => '<strong>LPW::DBIC</strong>',
902 <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
907 <pre>sub action_name : Local {
910 my $model = $c->model('DBIC::LPW');
911 my $author_model = $model->resultset('Authors');