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>Example of a DBIC Result</pre>
349 <pre>Example of a DBIC Result</pre>
353 <p>too much typing! too much maintenance!</p>
357 <h1>Schema::Loader</h1>
358 <pre>code for S::L here</pre>
362 <h1>splitting logic cleanly</h1>
363 <p>Foo::Schema::Result::Foo = an individual row</p>
364 <p>Foo::Schema::ResultSet::Foo = searches / results</p>
368 <h1>using your Schema</h1>
369 <pre>example usage code goes here</pre>
374 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
378 <h1>Schema::Loader</h1>
379 <pre>Foo::Schema::Result::Authors->table("authors");
380 Foo::Schema::Result::Authors->add_columns(
382 data_type => "INT",
383 default_value => undef,
388 data_type => "VARCHAR",
389 default_value => undef,
394 Foo::Schema::Result::Authors->set_primary_key("id");</pre>
398 <h1>Schema::Loader</h1>
399 <pre>Foo::Schema::Result::Books->table("books");
400 Foo::Schema::Result::Books->add_columns(
402 data_type => "INT",
403 default_value => undef,
408 data_type => "VARCHAR",
409 default_value => undef,
414 data_type => "INT",
415 default_value => undef,
420 Foo::Schema::Result::Books->set_primary_key("id");</pre>
424 <h1>Schema::Loader</h1>
425 <pre>Foo::Schema::Result::Authors->has_many(books => "Foo::Schema::Books",
426 { "foreign.author" => "self.id" });
428 Foo::Schema::Result::Books->belongs_to(author => "Foo::Schema::Authors",
429 { id => "author" });</pre>
433 <h1>SQL - debugging</h1>
434 <pre>INSERT INTO authors (name)
435 VALUES (?): 'Douglas Adams'
437 INSERT INTO books (author, title)
438 VALUES (?, ?): '5', '42'</pre>
443 <pre>Foo::Schema::Result::Books
444 Foo::Schema::ResultSet::Books
445 Foo::Schema::Result::Authors
446 Foo::Schema::ResultSet::Books</pre>
451 <pre>package Foo::Schema::Result::Books;
452 use base 'DBIx::Class';
459 # search amazon or something
460 my $api = Amazon::API->book({
461 title => $self->title
464 return $api->isbn;
472 <pre>print $book->isbn;</pre>
476 <h1>Result:: (inflating)</h1>
477 <pre>package Foo::Schema::Result::Books;
478 use base 'DBIx::Class';
482 use DateTime::Format::MySQL;
484 __PACKAGE__-><strong>inflate_column</strong>(
485 <strong>date_published</strong> => {
486 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
487 deflate => sub { shift->ymd},
490 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
494 <h1>Result:: (inflating)</h1>
495 <pre>package Foo::Schema::Result::Books;
496 use base 'DBIx::Class';
500 use DateTime::Format::MySQL;
502 __PACKAGE__->inflate_column(
503 date_published => {
504 <strong>inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
505 deflate => sub { shift->ymd},</strong>
508 # Automatic see: DBIx::Class::InflateColumn::DateTime
509 # Automatic see: DBIx::Class::InflateColumn::DateTime
510 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
514 <h1>Result:: (deflating)</h1>
515 <pre>$book->date_published(DateTime->now);
516 $book->update;</pre>
520 <h1>Result:: (inflating)</h1>
521 <pre>my $date_published = $book->date_published;
522 print $date_published->month_abbr;</pre>
524 <strong><em>Nov</em></strong>
528 <h1>ResultSets::</h1>
529 <pre>package Foo::Schema::ResultSet::Books;
530 use base 'DBIx::Class::ResultSet';
531 sub the_ultimate_books {
533 return $self->search({ title => { -like => '%42%' } });
536 my ( $self, $author ) = @_;
537 return $self->search({ author => $author->id })
544 <h1>ResultSets::</h1>
545 <pre>package Foo::Schema::<strong>ResultSet::Books</strong>;
546 use base '<strong>DBIx::Class::ResultSet</strong>';
547 sub the_ultimate_books {
549 <strong>return $self->search({ title => { -like => '%42%' } })</strong>
552 my ( $self, $author ) = @_;
553 return $self->search({ author => $author->id })
560 <h1>ResultSets::</h1>
561 <pre>package Foo::Schema::ResultSet::Books;
562 use base 'DBIx::Class::ResultSet';
563 sub the_ultimate_books {
565 return $self->search({ title => { -like => '%42%' } });
568 my ( $self, $author ) = @_;
569 <strong>return $self->search({ author => $author->id })</strong>
576 <h1>ResultSets::</h1>
577 <pre>use Foo::Schema;
578 my $book_model = Foo::Schema->resultset('Books');
579 my $book_rs = $book_model->the_ultimate_books;
580 my @books = $book_rs->all;</pre>
584 <h1>ResultSets::chaining</h1>
585 <pre>use Foo::Schema;
586 my $book_model = Foo::Schema->resultset('Books');
587 my $author_model = Foo::Schema->resultset('Authors');
588 my $author = $author_model->search({ name => 'Douglas Adams' })->single;
589 my $book_rs = $book_model->the_ultimate_books->by_author($author);
590 my @books = $book_rs->all;</pre>
594 <h1>ResultSets::chaining</h1>
595 <pre>my $book_rs = $book_model
596 ->the_ultimate_books
597 ->by_author($author);</pre>
600 <pre>my $book_rs = $book_model
601 ->the_ultimate_books();
602 $book_rs = $book_rs->by_author($author);</pre>
605 # SELECT me.id, me.title, me.date_published, me.author
607 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
608 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
612 <h1>ResultSets::chaining</h1>
613 <pre>my $rs = $book_model
614 ->category('childrens')
615 ->by_author($author)
616 ->published_after('1812')
617 ->first_page_contains('once upon')
618 ->rating_greater_than(4);
620 my @books = $rs->all;</pre>
624 <h1>overloading before new record</h1>
628 <h1>overloading before new record</h1>
629 <pre>package Foo::Schema::Result::Authors;
630 use base 'DBIx::Class';
633 my ( $class, $attrs ) = @_;
635 my $new = $class->next::method($attrs);
642 <h1>relationships</h1>
646 <h1>multiple authors</h1>
650 <h1>a few relationships</h1>
651 (authors -- author_link_to_book -- books)
655 <h1>a few relationships</h1>
660 <h1>new join table</h1>
661 <pre>CREATE TABLE author_and_books(
662 id int(8) primary key auto_increment,
665 foreign key (book) references books(id),
666 foreign key (author) references authors(id)
667 ) engine = InnoDB DEFAULT CHARSET=utf8;
669 ALTER TABLE `books` DROP `author`</pre>
673 <h1>new join table</h1>
674 <pre>CREATE TABLE author_and_books(
675 id int(8) primary key auto_increment,
678 <strong>foreign key (book) references books(id),
679 foreign key (author) references authors(id)</strong>
680 ) engine = InnoDB DEFAULT CHARSET=utf8;
682 ALTER TABLE `books` DROP `author`</pre>
691 <pre>package Foo::Schema::<strong>Result::Books</strong>;
693 __PACKAGE__->has_many( author_and_books => "Foo::Schema::Result::AuthorAndBooks",
694 { "foreign.book" => "self.id" },
697 <strong># This is auto generated by Schema::Loader</strong></pre>
702 <pre>package Foo::Schema::<strong>Result::Books</strong>;
704 __PACKAGE__->has_many(
705 author_and_books => <strong># name of accessor</strong>
706 "Foo::Schema::Result::AuthorAndBooks", <strong># related class</strong>
707 { "foreign.book" => "self.id" } <strong># Relationship (magic often works if not
708 # specified, but avoid!)</strong>
719 <pre>package Foo::Schema::<strong>Result::AuthorAndBooks</strong>;
721 __PACKAGE__->belongs_to(
722 book => <strong># Accessor name</strong>
723 "Foo::Schema::Result::Books", <strong># Related class</strong>
724 { id => "book" } <strong># relationship</strong>
730 <h1>same for Authors</h1>
734 <h1>with no coding...</h1>
738 <h1>many_to_many</h1>
742 <h1>many_to_many</h1>
743 <pre>package Foo::Schema::<strong>Result::Books</strong>;
744 use base 'DBIx::Class';
746 __PACKAGE__->many_to_many(
747 authors => "author_and_books", 'author'
752 <strong> # This is <em>NOT</em> auto generated by Schema::Loader </strong></pre>
756 <h1>many_to_many</h1>
757 <pre>package Foo::Schema::<strong>Result::Books</strong>;
758 use base 'DBIx::Class';
760 __PACKAGE__->many_to_many(
761 authors <strong># Accessor name</strong>
762 => "author_and_books", <strong># has_many</strong>
763 'author' <strong># foreign relationship name</strong>
770 <h1>many_to_many</h1>
771 <pre>package Foo::Schema::Result::Authors;
772 use base 'DBIx::Class';
774 __PACKAGE__->many_to_many(
775 "books" <strong># Accessor Name</strong>
776 => "author_and_books", <strong># has_many accessor_name</strong>
777 'book' <strong># foreign relationship name</strong>
782 <strong># This is <em>NOT</em> auto generated by Schema::Loader</strong></pre>
786 <h1>using many_to_many</h1>
791 my $author_model = Foo::Schema->resultset('Authors');
792 my $author = $author_model->search({
793 name => 'Douglas Adams',
795 $author->add_to_books({
796 title => 'A new book',
801 <h1>using many_to_many</h1>
802 <pre>my $author = $author_model->search({
803 name => 'Douglas Adams',
805 <strong>$author->add_to_books({
806 title => 'A new book',
809 # SELECT me.id, me.name FROM authors me
810 # WHERE ( name = ? ): 'Douglas Adams';
811 # INSERT INTO books (title) VALUES (?): 'A new book';
812 # INSERT INTO author_and_books (author, book)
813 # VALUES (?, ?): '5', '2';</pre>
817 <h1>using many_to_many</h1>
818 <pre>$author->add_to_books($book);
820 $book->add_to_authors($author_1);
821 $book->add_to_authors($author_2);</pre>
825 <h1>in 16 lines of code</h1>
830 <p>Read them closely!</p>
834 <h1>error messages</h1>
835 <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>
839 <h1>error messages</h1>
840 <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>
846 <li>Turn on debugging</li>
847 <li>Read error messages (sometimes useful!)</li>
848 <li>Check field names</li>
849 <li>Check package names</li>
850 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
855 <h1>bonus slides!</h1>
859 <h1>Template Toolkit</h1>
861 <li><pre>[% author.books.count %]</pre> not working?</li>
862 <li>TT all methods are called in list context</li>
863 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
864 <li><em>Available for all relationships</em></li>
870 <pre>package Your::App::Model::<strong>Foo</strong>;
871 use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
876 __PACKAGE__->config(
877 schema_class => '<strong>Foo::Schema</strong>',
881 <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
886 <pre>sub action_name : Local {
889 my $model = $c->model('Schema::Foo');
890 my $author_model = $model->resultset('Authors');