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>Amiri Barksdale</h4>
80 <h4>Justin D. Hunter</h4>
81 <h4>Arthur Axel "fREW" Schmidt</h4>
85 <h1>What's up guys?</h1>
88 <li>How many people have used any ORM?<ul>
98 <li>(N)Hibernate?</li>
106 <p>The purpose of this talk is to show you as many features of
107 DBIx::Class in 40 minutes so that when you need to do something with
108 it later you will know what's possible</p>
112 <h1>DBIx::Class?</h1>
114 <li>ORM (object relational mapper)</li>
115 <li>SQL <-> OO (using objects instead of SQL)</li>
116 <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
122 <p>These are reasons that are not technical or inherent to
123 the code of DBIC, but are totally awesome things about it.</p>
127 <h1>Large Community</h1>
128 <p>Currently there are 88 people listed as contributors to DBIC. That
129 ranges from documentation help, to test help, to added features,
130 to entire database support.</p>
134 <h1>Active Community</h1>
135 <p>Currently (June 9, 2010) 6 active branches (commited to in the last two weeks) in git. Last release (0.08122) had 14 new features, and 16 bug fixes. Of course that <a href="http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=Changes">ebbs and flows</a>.</p>
139 <h1>Responsive Community</h1>
140 <ul class="incremental">
141 <li>needed MSSQL Order by support, they helped me add support</li>
142 <li>generally very welcoming of people willing to help</li>
148 <p>These are things that are in most other ORMs, but are still reasons
149 to use DBIC over raw SQL.</p>
154 <p>The vast majority of code should run on all databases without needing tweaking</p>
159 <ul class="incremental">
160 <li><strong>C</strong> - Create</li>
161 <li><strong>R</strong> - Read</li>
162 <li><strong>U</strong> - Update</li>
163 <li><strong>D</strong> - Delete</li>
169 <pre>my $sth = $dbh->prepare('
176 'A book title', $author_id
181 <h1>DBIC: Create</h1>
182 <pre>my $book = $book_rs->create({
183 title => 'A book title',
184 author_id => $author_id,
186 <ul class="incremental">
187 <li>No need to pair placeholders and values</li>
193 <pre>my $sth = $dbh->prepare('
195 authors.name as author_name
197 WHERE books.author = authors.id
203 <pre>while( my $book = $sth->fetchrow_hashref() ) {
207 . $book->{author_name}
214 <pre>my $book = $book_rs->find($book_id);
216 my $book = $book_rs->search({
217 title => 'A book title',
218 }, { rows => 1 })->single;
220 my @books = $book_rs->search({
221 author => $author_id,
223 <ul class="incremental">
230 <pre>while( my $book = $books_rs->next ) {
234 . $book->author->name
241 <pre>my $update = $dbh->prepare('
248 'New title',<strong>$book_id</strong>
253 <h1>DBIC: Update</h1>
254 <pre>$book->update({
255 title => 'New title',
261 <pre>my $delete = $dbh->prepare('
266 $delete->execute(<strong>$book_id</strong>);</pre>
270 <h1>DBIC: Delete</h1>
271 <pre>$book->delete;</pre>
275 <h1>OO Overidability</h1>
276 <ul class="incremental">
277 <li>Override new if you want to do validation</li>
278 <li>Override delete if you want to disable deletion</li>
279 <li>and on and on</li>
282 <p>I got yelled at about this before by people, so
283 we don't get EVERYTHING from OO, but we do get a lot
289 <h1>Convenience Methods</h1>
290 <ul class="incremental">
291 <li>find_or_create</li>
292 <li>create_or_update</li>
297 <h1>Non-column methods</h1>
298 <p>Need a method to get a user's gravatar URL? Add a
299 gravatar_url method to their Result class</p>
303 <h1>RELATIONSHIPS</h1>
304 <ul class="incremental">
305 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#belongs_to">belongs_to</a></li>
306 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_many">has_many</a></li>
307 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#might_have">might_have</a></li>
308 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_one">has_one</a></li>
309 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#many_to_many">many_to_many</a> (technically not a relationship)</li>
310 <li>SET AND FORGET</li>
315 <h1>DBIx::Class Specific Features</h1>
316 <p>These things may be in other ORM's, but they are very specific, so doubtful</p>
322 <pre>my $schema = Foo::Schema->connect($dsn, $user, $pass);
325 <p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
329 <h1>Schema::Loader</h1>
331 <pre>package Foo::Schema;
332 use strict; use warnings;
333 use base 'DBIx::Class::Schema::Loader';
334 __PACKAGE__->loader_options({
336 debug => $ENV{DBIC_TRACE},
342 my $schema = Foo::Schema->connect($dsn, $user, $pass);
348 <p>Made for inserting lots of rows very quicky into database</p>
349 <pre>$schema->populate([ Users =>
350 [qw( username password )],
351 [qw( frew >=4char$ )],
356 <ul class="incremental">
357 <li>I use this to <a href="http://blog.afoolishmanifesto.com/archives/1255">export our whole (200M~) db to SQLite</a></li>
363 <p>Create an object and all of it's related objects all at once</p>
364 <pre>$schema->resultset('Author')->create({
365 name => 'Stephen King',
366 books => [{ title => 'The Dark Tower' }],
368 street => '123 Turtle Back Lane',
369 state => { abbreviation => 'ME' },
370 city => { name => 'Lowell' },
375 <li>books is a has_many</li>
376 <li>address is a belongs_to which in turn belongs to state and city each</li>
377 <li>for this to work right state and city must mark abbreviation and name as unique</li>
384 <p>DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made
385 to allow extensions to nearly every part of it.</p>
389 <h1>Extensible: DBIC::Helpers</h1>
390 <ul class="incremental">
391 <li>DBIx::Class::Helper::ResultSet::IgnoreWantarray</li>
392 <li>DBIx::Class::Helper::ResultSet::Random</li>
393 <li>DBIx::Class::Helper::ResultSet::SetOperations</li>
394 <li>DBIx::Class::Helper::Row::JoinTable</li>
395 <li>DBIx::Class::Helper::Row::NumifyGet</li>
396 <li>DBIx::Class::Helper::Row::SubClass</li>
397 <li>DBIx::Class::Helper::Row::ToJSON</li>
402 <h1>Extensible: DBIC::TimeStamp</h1>
403 <ul class="incremental">
405 <li>set_on_create</li>
406 <li>set_on_update</li>
411 <h1>Extensible: DBIx::Class::Schema::KiokuDB</h1>
412 <ul class="incremental">
413 <li>Kioku is the new hotness</li>
414 <li>Mix RDBMS with Object DB</li>
415 <li>beta ( == sexy )</li>
420 <h1>SQL::Abstract</h1>
421 <pre>my $resultset = $book_rs->search({
422 name => { -like => "%$nick%" },
424 <ul class="incremental">
425 <li>(kinda) introspectible</li>
426 <li>Prettier than SQL</li>
431 <h1>Result vs ResultSet</h1>
432 <ul class="incremental">
433 <li>Result == Row</li>
434 <li>ResultSet == Query</li>
435 <li>(less important but...)</li>
436 <li>ResultSource == Table</li>
437 <li>Storage == Database</li>
442 <h1>ResultSet methods</h1>
443 <pre>package MyApp::Schema::ResultSet::Book;
444 use base 'DBIx::Class::ResultSet';
448 rating => { '>=' => 4 },
454 price => { '<=' => 5}
460 <ul class="incremental">
461 <li>All searches should be ResultSet methods</li>
462 <li>Name has obvious meaning</li>
467 <h1>ResultSet method in Action</h1>
468 <pre>$schema->resultset('Book')->good</pre>
472 <h1>ResultSet Chaining</h1>
473 <pre>$schema->resultset('Book')
482 <pre>DBIC_TRACE=1 ./your_script.pl</pre>
486 <h1>SQL - debugging</h1>
487 <pre>INSERT INTO authors (name)
488 VALUES (?): 'Douglas Adams'
490 INSERT INTO books (author, title)
491 VALUES (?, ?): '5', '42'</pre>
496 <pre>Foo::Schema::Result::Book
497 Foo::Schema::ResultSet::Book
498 Foo::Schema::Result::Author
499 Foo::Schema::ResultSet::Book</pre>
504 <pre>package Foo::Schema::Result::Book;
505 use base 'DBIx::Class::Core';
514 # search amazon or something
515 my $api = Amazon::API->book({
516 title => $self->title
519 return $api->isbn;
527 <pre>print $book->isbn;</pre>
531 <h1>Result:: (inflating)</h1>
532 <pre>package Foo::Schema::Result::Book;
533 use base 'DBIx::Class::Core';
539 __PACKAGE__->load_components('InflateColumn');
540 use DateTime::Format::MySQL;
542 __PACKAGE__-><strong>inflate_column</strong>(
543 <strong>date_published</strong> => {
544 inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
545 deflate => sub { shift->ymd},
548 # Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
552 <h1>Result:: (deflating)</h1>
553 <pre>$book->date_published(DateTime->now);
554 $book->update;</pre>
558 <h1>Result:: (inflating)</h1>
559 <pre>my $date_published = $book->date_published;
560 print $date_published->month_abbr;</pre>
562 <strong><em>Nov</em></strong>
566 <h1>ResultSets::</h1>
567 <pre>package Foo::Schema::ResultSet::Books;
568 use base 'DBIx::Class::ResultSet';
569 sub the_ultimate_books {
571 return $self->search({ title => { -like => '%42%' } });
574 my ( $self, $author ) = @_;
575 return $self->search({ author => $author->id })
582 <h1>ResultSets::</h1>
583 <pre>use Foo::Schema;
584 my $schema = Foo::Schema->connect(...);
585 my $book_rs = Foo::Schema->resultset('Book');
586 my $book_search = $book_rs->the_ultimate_books;
587 my @books = $book_search->all;</pre>
591 <h1>ResultSets: Chaining</h1>
593 my $book_rs = $schema->resultset('Book');
594 my $author_rs = $schema->resultset('Author');
595 my $author = $author_rs->search({ name => 'Douglas Adams' })->single;
596 $book_rs = $book_rs->the_ultimate_books->by_author($author);
597 my @books = $book_rs->all;</pre>
601 <h1>ResultSets: Chaining</h1>
602 <pre>$book_rs = $schema->resultset('Book')
603 ->the_ultimate_books
604 ->by_author($author);</pre>
607 <pre>my $book_rs = $schema->resultset('Book')
608 ->the_ultimate_books;
609 $book_rs = $book_rs->by_author($author);</pre>
612 # SELECT me.id, me.title, me.date_published, me.author
614 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
615 # WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
619 <h1>ResultSets: Chaining</h1>
620 <pre>my $rs = $book_rs
621 ->category('childrens')
622 ->by_author($author)
623 ->published_after('1812')
624 ->first_page_contains('once upon')
625 ->rating_greater_than(4);
627 my @books = $rs->all;</pre>
631 <h1>overloading before new record</h1>
632 <pre>package Foo::Schema::Result::Author;
633 use base 'DBIx::Class::Core';
636 my ( $class, $attrs ) = @_;
638 my $new = $class->next::method($attrs);
645 <h1>Relationships</h1>
649 <h1>Multiple Authors</h1>
650 <p>We want to allow a book to be by more than one author</p>
654 <h1>a few relationships</h1>
655 <img src="img/afewrels.png" />
660 <pre>CREATE TABLE author_and_books(
663 foreign key (book_id) references books(id),
664 foreign key (author_id) references authors(id)
665 ) engine = InnoDB DEFAULT CHARSET=utf8;
667 ALTER TABLE `books` DROP `author_id`;</pre>
672 <img src="img/hasmany1.png" />
677 <pre>package Foo::Schema::<strong>Result::Book</strong>;
679 __PACKAGE__->has_many( author_and_books =>
680 'Foo::Schema::Result::Author_Book', 'book_id'
686 <img src="img/belongsto1.png" />
691 <pre>package Foo::Schema::<strong>Result::Author_Book</strong>;
693 __PACKAGE__->belongs_to(
695 'Foo::Schema::Result::Book', 'book_id'
701 <h1>same for Authors</h1>
702 <img src="img/authors.png" />
706 <h1>many_to_many</h1>
707 <img src="img/m2m.png" />
711 <h1>many_to_many</h1>
712 <pre>package Foo::Schema::<strong>Result::Book</strong>;
713 use base 'DBIx::Class::Core';
715 __PACKAGE__->many_to_many(
716 authors => 'author_and_books', 'author'
724 <h1>many_to_many</h1>
725 <pre>package Foo::Schema::<strong>Result::Book</strong>;
726 use base 'DBIx::Class::Core';
728 __PACKAGE__->many_to_many(
729 authors <strong># Accessor name</strong>
730 => "author_and_books", <strong># has_many</strong>
731 'author' <strong># foreign relationship name</strong>
738 <h1>many_to_many</h1>
739 <pre>package Foo::Schema::Result::Author;
740 use base 'DBIx::Class::Core';
742 __PACKAGE__->many_to_many(
743 "books" <strong># Accessor Name</strong>
744 => "author_and_books", <strong># has_many accessor_name</strong>
745 'book' <strong># foreign relationship name</strong>
753 <h1>Using many_to_many</h1>
756 my $schema = Foo::Schema->connect(...);
757 my $author_rs = $schema->resultset('Authors');
758 my $author = $author_rs->search({
759 name => 'Douglas Adams',
761 $author->add_to_books({
762 title => 'A new book',
767 <h1>using many_to_many</h1>
768 <pre>my $author = $author_rs->search({
769 name => 'Douglas Adams',
771 <strong>$author->add_to_books({
772 title => 'A new book',
775 # SELECT me.id, me.name FROM authors me
776 # WHERE ( name = ? ): 'Douglas Adams';
777 # INSERT INTO books (title) VALUES (?): 'A new book';
778 # INSERT INTO author_and_books (author, book)
779 # VALUES (?, ?): '5', '2';</pre>
783 <h1>using many_to_many</h1>
784 <pre>$author->add_to_books($book);
786 $book->add_to_authors($author_1);
787 $book->add_to_authors($author_2);</pre>
792 <p>Read them closely!</p>
796 <h1>error messages</h1>
797 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external
798 class definition for 'Foo::Schema::Result::Authors': Can't locate object
799 method "many_to_many" via package "Foo::Schema::Result::Author" at
800 lib/Foo/Schema/Result/Authors.pm line 9.Compilation failed in require at
801 /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
805 <h1>error messages</h1>
806 <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external
807 class definition for 'Foo::Schema::Result::Authors': Can't locate object
808 method "many_to_many" via package "Foo::Schema::<strong>Result::Author</strong>" at
809 lib/Foo/Schema/<strong>Result/Authors.pm</strong> line 9.Compilation failed in require at
810 /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
816 <li>Turn on debugging</li>
817 <li>Read error messages (sometimes useful!)</li>
818 <li>Check field names</li>
819 <li>Check package names</li>
820 <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
825 <h1>LOTS more Features</h1>
827 <li>FilterColumn</li>
828 <li>Transactions</li>
829 <li>HashRefInflator</li>
831 <li>ResultSetColumn</li>
832 <li>Aggregate Queries</li>
837 <h1>bonus slides!</h1>
841 <h1>Template Toolkit</h1>
843 <li><pre>[% author.books.count %]</pre> not working?</li>
844 <li>TT all methods are called in list context</li>
845 <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
846 <li><em>Available for all relationships</em></li>
852 <pre>package Your::App::Model::<strong>Foo</strong>;
853 use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
858 __PACKAGE__->config(
859 schema_class => '<strong>Foo::Schema</strong>',
863 <p>Keep your Schema in a <em>separate</em> package from your Catalyst application</p>
868 <pre>sub action_name : Local {
871 my $schema = $c->model('Schema::Foo');
872 my $author_rs = $schema->resultset('Authors');