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>
177 <h1>CRUD compared</h1>
179 <li><strong>C</strong> - Create</li>
180 <li><strong>R</strong> - Read</li>
181 <li><strong>U</strong> - Update</li>
182 <li><strong>D</strong> - Delete</li>
187 <h1>Manual (SQL)</h1>
192 <pre>my $sth = $dbh->prepare('
199 'A book title', $author_id
205 <pre>my $sth = $dbh->prepare('
207 authors.name as author_name
209 WHERE books.author = authors.id
215 <pre>while( my $book = $sth->fetchrow_hashref() ) {
219 . $book->{author_name}
226 <pre>my $update = $dbh->prepare('
233 'New title',<strong>$book_id</strong>
239 <pre>my $delete = $dbh->prepare('
244 $delete->execute(<strong>$book_id</strong>);</pre>
252 <h1>DBIC: Create</h1>
253 <pre>my $book = $book_rs->create({
254 title => 'A book title',
255 author_id => $author_id,
257 <p>Look ma, no SQL!</p>
261 <h1>DBIC: Create</h1>
262 <pre>my $pratchett = $author_rs->create({
263 name => 'Terry Pratchett',
268 <h1>DBIC: Create</h1>
269 <pre>my $book = $pratchett->create_related(
270 <strong>books</strong> => {
271 title => 'Another Discworld book',
274 <pre>my $book = $pratchett->add_to_<strong>books</strong>({
275 title => 'Another Discworld book',
281 <p>DBIx::Class - TIMTOWTDI</p>
286 <pre>my $book = $book_rs->find($book_id);
288 my $book = $book_rs->search({
289 title => 'A book title',
290 }, { rows => 1 })->single;
292 my @books = $book_rs->search({
293 author => $author_id,
299 <pre>while( my $book = $books_rs->next ) {
303 . $book->author->name
310 <pre>my $resultset = $book_rs->search({
311 author => $author_id,
313 <p>Search takes SQL::Abstract formatted queries</p>
314 <pre>> perldoc SQL::Abstract</p>
318 <h1>DBIC: Update</h1>
319 <pre>$book->update({
320 title => 'New title',
325 <h1>DBIC: Delete</h1>
326 <pre>$book->delete;</pre>
330 <h1>Creating models</h1>
334 <pre>package Foo::Schema::Result::Author;
335 __PACKAGE__->table('authors');
336 __PACKAGE__->add_columns(
338 data_type => 'int',
339 is_auto_increment => 1
342 data_type => 'varchar',
347 __PACKAGE__->set_primary_key('id');
348 __PACKAGE__->has_many( books =>
349 'Foo::Schema::Result::Book', 'author_id'
356 <pre style="float: left; font-size: 80%;">package Foo::Schema::Result::Book;
357 use strict; use warnings;
358 __PACKAGE__->table('books');
359 __PACKAGE__->add_columns(
361 data_type => 'int',
362 is_auto_increment => 1
365 data_type => 'varchar',
370 <pre style="float: left; font-size: 80%;">
372 data_type => 'int',
376 __PACKAGE__->set_primary_key('id');
377 __PACKAGE__->belongs_to( author =>
378 'Foo::Schema::Result::Author', 'author_id'
387 <pre>my $schema = Foo::Schema->connect($dsn, $user, $pass);
390 <p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
394 <h1>Schema::Loader</h1>
396 <pre>package Foo::Schema;
397 use strict; use warnings;
398 use base 'DBIx::Class::Schema::Loader';
399 __PACKAGE__->loader_options({
401 debug => $ENV{DBIC_TRACE},
407 my $schema = Foo::Schema->connect($dsn, $user, $pass);
412 <h1>Splitting Logic Cleanly</h1>
413 <p>Foo::Schema::Result::Bar = individual row</p>
414 <p>Foo::Schema::ResultSet::Bar = searches / table </p>
418 <h1>Using your Schema</h1>
420 use strict; use warnings;
423 my $schema = Foo::Schema->connect($dns, $user, $pass);
424 my $author_rs = $schema->resultset('Author');
425 my $author = $author_rs->create({
426 name => 'Douglas Adams',
428 my $book = $author->add_to_books({
436 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
440 <h1>SQL - debugging</h1>
441 <pre>INSERT INTO authors (name)
442 VALUES (?): 'Douglas Adams'
444 INSERT INTO books (author, title)
445 VALUES (?, ?): '5', '42'</pre>
450 <pre>Foo::Schema::Result::Book
451 Foo::Schema::ResultSet::Book
452 Foo::Schema::Result::Author
453 Foo::Schema::ResultSet::Book</pre>
458 <pre>package Foo::Schema::Result::Book;
459 use base 'DBIx::Class::Core';
468 # search amazon or something
469 my $api = Amazon::API->book({
470 title => $self->title
473 return $api->isbn;
481 <pre>print $book->isbn;</pre>
485 <h1>Result:: (inflating)</h1>
486 <pre>package Foo::Schema::Result::Book;
487 use base 'DBIx::Class::Core';
493 __PACKAGE__->load_components('InflateColumn');
494 use DateTime::Format::MySQL;
496 __PACKAGE__-><strong>inflate_column</strong>(
497 <strong>date_published</strong> => {
498 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
499 deflate => sub { shift->ymd},
502 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
506 <h1>Result:: (deflating)</h1>
507 <pre>$book->date_published(DateTime->now);
508 $book->update;</pre>
512 <h1>Result:: (inflating)</h1>
513 <pre>my $date_published = $book->date_published;
514 print $date_published->month_abbr;</pre>
516 <strong><em>Nov</em></strong>
520 <h1>ResultSets::</h1>
521 <pre>package Foo::Schema::ResultSet::Books;
522 use base 'DBIx::Class::ResultSet';
523 sub the_ultimate_books {
525 return $self->search({ title => { -like => '%42%' } });
528 my ( $self, $author ) = @_;
529 return $self->search({ author => $author->id })
536 <h1>ResultSets::</h1>
537 <pre>use Foo::Schema;
538 my $schema = Foo::Schema->connect(...);
539 my $book_rs = Foo::Schema->resultset('Book');
540 my $book_search = $book_rs->the_ultimate_books;
541 my @books = $book_search->all;</pre>
545 <h1>ResultSets: Chaining</h1>
547 my $book_rs = $schema->resultset('Book');
548 my $author_rs = $schema->resultset('Author');
549 my $author = $author_rs->search({ name => 'Douglas Adams' })->single;
550 $book_rs = $book_rs->the_ultimate_books->by_author($author);
551 my @books = $book_rs->all;</pre>
555 <h1>ResultSets: Chaining</h1>
556 <pre>$book_rs = $schema->resultset('Book')
557 ->the_ultimate_books
558 ->by_author($author);</pre>
561 <pre>my $book_rs = $schema->resultset('Book')
562 ->the_ultimate_books;
563 $book_rs = $book_rs->by_author($author);</pre>
566 # SELECT me.id, me.title, me.date_published, me.author
568 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
569 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
573 <h1>ResultSets: Chaining</h1>
574 <pre>my $rs = $book_rs
575 ->category('childrens')
576 ->by_author($author)
577 ->published_after('1812')
578 ->first_page_contains('once upon')
579 ->rating_greater_than(4);
581 my @books = $rs->all;</pre>
585 <h1>overloading before new record</h1>
586 <pre>package Foo::Schema::Result::Author;
587 use base 'DBIx::Class::Core';
590 my ( $class, $attrs ) = @_;
592 my $new = $class->next::method($attrs);
599 <h1>Relationships</h1>
603 <h1>Multiple Authors</h1>
604 <p>We want to allow a book to be by more than one author</p>
608 <h1>a few relationships</h1>
609 <img src="img/afewrels.png" />
614 <pre>CREATE TABLE author_and_books(
617 foreign key (book_id) references books(id),
618 foreign key (author_id) references authors(id)
619 ) engine = InnoDB DEFAULT CHARSET=utf8;
621 ALTER TABLE `books` DROP `author_id`;</pre>
626 <img src="img/hasmany1.png" />
631 <pre>package Foo::Schema::<strong>Result::Book</strong>;
633 __PACKAGE__->has_many( author_and_books =>
634 'Foo::Schema::Result::Author_Book', 'book_id'
640 <img src="img/belongsto1.png" />
645 <pre>package Foo::Schema::<strong>Result::Author_Book</strong>;
647 __PACKAGE__->belongs_to(
649 'Foo::Schema::Result::Book', 'book_id'
655 <h1>same for Authors</h1>
656 <img src="img/authors.png" />
660 <h1>many_to_many</h1>
661 <img src="img/m2m.png" />
665 <h1>many_to_many</h1>
666 <pre>package Foo::Schema::<strong>Result::Book</strong>;
667 use base 'DBIx::Class::Core';
669 __PACKAGE__->many_to_many(
670 authors => 'author_and_books', 'author'
678 <h1>many_to_many</h1>
679 <pre>package Foo::Schema::<strong>Result::Book</strong>;
680 use base 'DBIx::Class::Core';
682 __PACKAGE__->many_to_many(
683 authors <strong># Accessor name</strong>
684 => "author_and_books", <strong># has_many</strong>
685 'author' <strong># foreign relationship name</strong>
692 <h1>many_to_many</h1>
693 <pre>package Foo::Schema::Result::Author;
694 use base 'DBIx::Class::Core';
696 __PACKAGE__->many_to_many(
697 "books" <strong># Accessor Name</strong>
698 => "author_and_books", <strong># has_many accessor_name</strong>
699 'book' <strong># foreign relationship name</strong>
707 <h1>Using many_to_many</h1>
710 my $schema = Foo::Schema->connect(...);
711 my $author_rs = $schema->resultset('Authors');
712 my $author = $author_rs->search({
713 name => 'Douglas Adams',
715 $author->add_to_books({
716 title => 'A new book',
721 <h1>using many_to_many</h1>
722 <pre>my $author = $author_rs->search({
723 name => 'Douglas Adams',
725 <strong>$author->add_to_books({
726 title => 'A new book',
729 # SELECT me.id, me.name FROM authors me
730 # WHERE ( name = ? ): 'Douglas Adams';
731 # INSERT INTO books (title) VALUES (?): 'A new book';
732 # INSERT INTO author_and_books (author, book)
733 # VALUES (?, ?): '5', '2';</pre>
737 <h1>using many_to_many</h1>
738 <pre>$author->add_to_books($book);
740 $book->add_to_authors($author_1);
741 $book->add_to_authors($author_2);</pre>
746 <p>Read them closely!</p>
750 <h1>error messages</h1>
751 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external
752 class definition for 'Foo::Schema::Result::Authors': Can't locate object
753 method "many_to_many" via package "Foo::Schema::Result::Author" at
754 lib/Foo/Schema/Result/Authors.pm line 9.Compilation failed in require at
755 /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
759 <h1>error messages</h1>
760 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external
761 class definition for 'Foo::Schema::Result::Authors': Can't locate object
762 method "many_to_many" via package "Foo::Schema::<strong>Result::Author</strong>" at
763 lib/Foo/Schema/<strong>Result/Authors.pm</strong> line 9.Compilation failed in require at
764 /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
770 <li>Turn on debugging</li>
771 <li>Read error messages (sometimes useful!)</li>
772 <li>Check field names</li>
773 <li>Check package names</li>
774 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
779 <h1>LOTS more Features</h1>
781 <li>FilterColumn</li>
782 <li>Transactions</li>
783 <li>HashRefInflator</li>
785 <li>ResultSetColumn</li>
786 <li>Aggregate Queries</li>
791 <h1>bonus slides!</h1>
795 <h1>Template Toolkit</h1>
797 <li><pre>[% author.books.count %]</pre> not working?</li>
798 <li>TT all methods are called in list context</li>
799 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
800 <li><em>Available for all relationships</em></li>
806 <pre>package Your::App::Model::<strong>Foo</strong>;
807 use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
812 __PACKAGE__->config(
813 schema_class => '<strong>Foo::Schema</strong>',
817 <p>Keep your Schema in a <em>separate</em> package from your Catalyst application</p>
822 <pre>sub action_name : Local {
825 my $schema = $c->model('Schema::Foo');
826 my $author_rs = $schema->resultset('Authors');