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>
79 <li>IRC: irc.perl.org #dbix-class</li>
80 <li>ML: <a href="http://lists.scsys.co.uk/mailman/listinfo/dbix-class">http://lists.scsys.co.uk/mailman/listinfo/dbix-class</a></li>
81 <li><a href="http://search.cpan.org/perldoc?DBIx::Class">DBIx::Class</a> yo</li>
87 <h4>Originally Leo Lapworth @ LPW 2009</h4>
88 <h4>Amiri Barksdale</h4>
89 <h4>Justin D. Hunter</h4>
90 <h4>Arthur Axel "fREW" Schmidt</h4>
94 <h1>What's up guys?</h1>
95 <ul class="incremental">
96 <li>How many people have used any ORM?</li><ul class="incremental">
97 <li>In Perl?<ul class="incremental">
105 <li>(N)Hibernate?</li>
112 <p>The purpose of this talk is to show you as many features of
113 DBIx::Class in 40 minutes so that when you need to do something with
114 it later you will know what's possible</p>
115 <ul class="incremental">
116 <li>Note: links in slides are so you can find docs for what I'm talking about later</li>
121 <h1>DBIx::Class?</h1>
123 <li>ORM (object relational mapper)</li>
124 <li>SQL <-> OO (using objects instead of SQL)</li>
125 <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
131 <p>These are reasons that are not technical or inherent to
132 the code of DBIC, but are totally awesome things about it.</p>
136 <h1>Large Community</h1>
137 <p>Currently there are 88 people listed as contributors to DBIC. That
138 ranges from documentation help, to test help, to added features,
139 to entire database support.</p>
143 <h1>Active Community</h1>
144 <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>
148 <h1>Responsive Community</h1>
149 <ul class="incremental">
150 <li>needed MSSQL order-by support, they helped me add support</li>
151 <li>generally very welcoming of people willing to help</li>
157 <p>These are things that are in most other ORMs, but are still reasons
158 to use DBIC over raw SQL.</p>
163 <p>The vast majority of code should run on all databases without needing tweaking</p>
168 <ul class="incremental">
169 <li><strong>C</strong> - Create</li>
170 <li><strong>R</strong> - Read</li>
171 <li><strong>U</strong> - Update</li>
172 <li><strong>D</strong> - Delete</li>
178 <pre>my $sth = $dbh->prepare('
185 'A book title', $author_id
190 <h1>DBIC: Create</h1>
191 <pre>my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#create">create</a>({
192 title => 'A book title',
193 author_id => $author_id,
195 <ul class="incremental">
196 <li>No need to pair placeholders and values</li>
197 <li>Automatically gets autoincremented id for you</li>
198 <li>Transparently uses INSERT ... RETURNING for databases that support it</li>
204 <pre>my $sth = $dbh->prepare('
206 authors.name as author_name
208 WHERE books.author = authors.id
211 while( my $book = $sth->fetchrow_hashref() ) {
215 . $book->{author_name}
222 <pre>my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#find">find</a>($book_id);
224 my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search">search</a>({
225 title => 'A book title',
226 }, { rows => 1 })-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#next">next</a>;
228 my @books = $book_rs->search({
229 author => $author_id,
230 })-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#all">all</a>;
232 while( my $book = $books_rs->next ) {
236 . $book->author->name
240 <ul class="incremental">
247 <pre>my $update = $dbh->prepare('
254 'New title',<strong>$book_id</strong>
259 <h1>DBIC: Update</h1>
260 <pre>$book-><a href="http://search.cpan.org/perldoc?DBIx::Class::Row#update">update</a>({
261 title => 'New title',
263 <ul class="incremental">
264 <li>Won't update unless value changes</li>
270 <pre>my $delete = $dbh->prepare('
275 $delete->execute(<strong>$book_id</strong>);</pre>
279 <h1>DBIC: Delete</h1>
280 <pre>$book-><a href="http://search.cpan.org/perldoc?DBIx::Class::Row#delete">delete</a>;</pre>
285 <pre>my $sth = $dbh->prepare('
287 authors.name as author_name
289 WHERE books.name LIKE "%monte cristo%" AND
290 books.topic = "jailbreak"
296 <h1>DBIC: Search</h1>
298 my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search">search</a>({
299 'me.name' => { -like => '%monte cristo%' },
300 'me.topic' => 'jailbreak',
301 })-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#next">next</a>;
303 <ul class="incremental">
304 <li><a href="http://search.cpan.org/perldoc?SQL::Abstract">SQL::Abstract</a></li>
305 <li>(kinda) introspectible</li>
306 <li>Prettier than SQL</li>
311 <h1>OO Overidability</h1>
312 <ul class="incremental">
313 <li>Override new if you want to do validation</li>
314 <li>Override delete if you want to disable deletion</li>
315 <li>and on and on</li>
318 <p>I got yelled at about this before by people, so
319 we don't get EVERYTHING from OO, but we do get a lot
325 <h1>Convenience Methods</h1>
326 <ul class="incremental">
327 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#find_or_create">find_or_create</a></li>
328 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#update_or_create">update_or_create</a></li>
333 <h1>Non-column methods</h1>
334 <p>Need a method to get a user's gravatar URL? Add a
335 gravatar_url method to their Result class</p>
339 <h1>RELATIONSHIPS</h1>
340 <ul class="incremental">
341 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#belongs_to">belongs_to</a></li>
342 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_many">has_many</a></li>
343 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#might_have">might_have</a></li>
344 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_one">has_one</a></li>
345 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#many_to_many">many_to_many</a> (technically not a relationship)</li>
346 <li>SET AND FORGET</li>
351 <h1>DBIx::Class Specific Features</h1>
352 <p>These things may be in other ORM's, but they are very specific, so doubtful</p>
358 <pre>my $schema = Foo::Schema->connect(
361 $schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#deploy">deploy</a>
363 <p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
367 <h1>Schema::Loader</h1>
369 <pre>package Foo::Schema;
370 use strict; use warnings;
371 use base '<a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::Loader">DBIx::Class::Schema::Loader</a>';
372 __PACKAGE__-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::Loader::Base#CONSTRUCTOR_OPTIONS">loader_options</a>({
374 debug => $ENV{DBIC_TRACE},
380 my $schema = Foo::Schema->connect(
388 <p>Made for inserting lots of rows very quicky into database</p>
389 <pre>$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#populate">populate</a>([ Users =>
390 [qw( username password )],
391 [qw( frew >=4char$ )],
396 <ul class="incremental">
397 <li>I use this to <a href="http://blog.afoolishmanifesto.com/archives/1255">export our whole (200M~) db to SQLite</a></li>
403 <p>Create an object and all of it's related objects all at once</p>
404 <pre>$schema->resultset('Author')-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#create">create</a>({
405 name => 'Stephen King',
406 books => [{ title => 'The Dark Tower' }],
408 street => '123 Turtle Back Lane',
409 state => { abbreviation => 'ME' },
410 city => { name => 'Lowell' },
416 <li>books is a has_many</li>
417 <li>address is a belongs_to which in turn belongs to state and city each</li>
418 <li>for this to work right state and city must mark abbreviation and name as unique</li>
425 <p>DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made
426 to allow extensions to nearly every part of it.</p>
430 <h1>Extensible: DBIC::Helpers</h1>
431 <ul class="incremental">
432 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::IgnoreWantarray">DBIC::Helper::ResultSet::IgnoreWantarray</a></li>
433 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::Random">DBIC::Helper::ResultSet::Random</a></li>
434 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::SetOperations">DBIC::Helper::ResultSet::SetOperations</a></li>
435 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::JoinTable">DBIC::Helper::Row::JoinTable</a></li>
436 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::NumifyGet">DBIC::Helper::Row::NumifyGet</a></li>
437 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::SubClass">DBIC::Helper::Row::SubClass</a></li>
438 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::ToJSON">DBIC::Helper::Row::ToJSON</a></li>
443 <h1>Extensible: <a href="http://search.cpan.org/perldoc?DBIx::Class::TimeStamp">DBIC::TimeStamp</a></h1>
444 <ul class="incremental">
446 <li>set_on_create</li>
447 <li>set_on_update</li>
452 <h1>Extensible: Kioku</h1>
453 <ul class="incremental">
454 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::KiokuDB">DBIx::Class::Schema::KiokuDB</a></li>
455 <li>Kioku is the new hotness</li>
456 <li>Mix RDBMS with Object DB</li>
457 <li>beta ( == sexy )</li>
462 <h1>Result vs ResultSet</h1>
463 <ul class="incremental">
464 <li>Result == Row</li>
465 <li>ResultSet == Query Plan<ul class="incremental">
466 <li>Internal Join Optimizer for all DB's (!!!)</li>
468 <li>(less important but...)</li>
469 <li>ResultSource == Table</li>
470 <li>Storage == Database</li>
475 <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook#Predefined_searches">ResultSet methods</a></h1>
476 <pre>package MyApp::Schema::ResultSet::Book;
477 use base 'DBIx::Class::ResultSet';
481 $self->current_source_alias .
482 '.rating' => { '>=' => 4 },
488 $self->current_source_alias .
489 '.price' => { '<=' => 5}
498 <h1>ResultSet method notes</h1>
499 <ul class="incremental">
500 <li>All searches should be ResultSet methods</li>
501 <li>Name has obvious meaning</li>
502 <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#current_source_alias">current_source_alias</a> helps things to work no matter what</li>
507 <h1>ResultSet method in Action</h1>
508 <pre>$schema->resultset('Book')->good</pre>
512 <h1>ResultSet Chaining</h1>
513 <pre>$schema->resultset('Book')
521 <h1>search_related</h1>
522 <pre>my $score = $schema->resultset('User')
523 ->search({'me.userid' => 'frew'})
524 -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#related_resultset">related_resultset</a>('access')
525 ->related_resultset('mgmt')
526 ->related_resultset('orders')
528 -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search_related">search_related</a>( shops => {
529 'shops.datecompleted' => {
530 -between => ['2009-10-01','2009-10-08']
533 ->related_resultset('rpt_score')
534 -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#get_column">get_column</a>('raw_scores')
540 <h1>bonus rel methods</h1>
541 <pre>my $book = $author-><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship::Base#create_related">create_related</a>(
542 <strong>books</strong> => {
543 title => 'Another Discworld book',
547 my $book2 = $pratchett-><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship::Base#add_to_$rel">add_to_<strong>books</strong></a>({
548 title => 'MOAR Discworld book',
550 <ul class="incremental">
551 <li>Automaticaly fills in foreign key for you</li>
556 <h1>Excellent Transaction Support</h1>
557 <pre>$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_do">txn_do</a>(sub {
561 my $guard = $schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_scope_guard">txn_scope_guard</a>;
565 $schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_begin">txn_begin</a>; # <-- low level
567 $schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_commit">txn_commit</a>;
572 <h1>InflateColumn</h1>
573 <pre>package Foo::Schema::Result::Book;
574 use base 'DBIx::Class::Core';
575 use DateTime::Format::MySQL;
577 __PACKAGE__->load_components('<a href="http://search.cpan.org/perldoc?DBIx::Class::InflateColumn">InflateColumn</a>');
578 __PACKAGE__-><strong><a href="http://search.cpan.org/perldoc?DBIx::Class::InflateColumn#inflate_column">inflate_column</a></strong>(
579 <strong>date_published</strong> => {
581 DateTime::Format::MySQL->parse_date(
585 deflate => sub { shift->ymd },
588 # Automatic see: DBIC::InflateColumn::DateTime</pre>
592 <h1>InflateColumn: deflation</h1>
593 <pre>$book->date_published(DateTime->now);
594 $book->update;</pre>
598 <h1>InflateColumn: inflation</h1>
599 <pre>say $book->date_published->month_abbr;</pre>
601 <strong><em>Nov</em></strong>
605 <h1>FilterColumn</h1>
606 <pre>package Foo::Schema::Result::Book;
607 use base 'DBIx::Class::Core';
609 __PACKAGE__->load_components('<a href="http://search.cpan.org/perldoc?DBIx::Class::FilterColumn">FilterColumn</a>');
611 __PACKAGE__-><strong><a href="http://search.cpan.org/perldoc?DBIx::Class::FilterColumn#filter_column">filter_column</a></strong>(
612 <strong>length</strong> => {
613 to_storage => 'to_metric',
614 from_storage => 'to_imperial',
618 sub to_metric { $_[1] * .305 }
619 sub to_imperial { $_[1] * 3.28 }
623 <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSetColumn">ResultSetColumn</a></h1>
624 <pre>my $rsc = $schema->resultset('Book')
625 ->get_column('price');
636 <pre>my @res = $rs->search({}, {
637 <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#select">select</a> => [
643 <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#as">as</a> => [
644 qw(price genre max_price avg_price)
646 <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#group_by">group_by</a> => [qw(price genre)],
649 say $_->price . ' ' . $_->genre;
650 say $_->get_column('max_price');
651 say $_->get_column('min_price');
656 <h1>Aggregates Notes</h1>
657 <ul class="incremental">
658 <li>Careful, get_column can basicaly mean THREE things</li>
659 <li>private for get what you should use an accessor for</li>
660 <li>public for what there is no accessor for</li>
661 <li>public for get resultset column (prev slide)</li>
667 <pre>$rs->search({}, {
668 <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#result_class">result_class</a> =>
669 '<a href="http://search.cpan.org/perldoc?DBIx::Class::ResultClass::HashRefInflator">DBIx::Class::ResultClass::HashRefInflator</a>',
671 <ul class="incremental">
672 <li>Easy on memory</li>
674 <li>Great for quick debugging</li>
675 <li>Great for performance tuning (we went from 2m to < 3s)</li>
680 <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook#Subqueries">Subquery</a> Support</h1>
681 <pre>my $inside_query = $schema->resultset('Artist')
683 name => [ 'Billy Joel', 'Brittany Spears' ],
684 })->get_column('id')->as_query;
686 my $rs = $schema->resultset('CD')->search({
687 artist_id => { -in => $inside_query },
692 <h1><a href="http://search.cpan.org/perldoc?SQL::Abstract#Literal_SQL_with_placeholders_and_bind_values_(subqueries)">Bare SQL w/ Placeholders</a></h1>
693 <pre>$rs->update({
694 # !!! SQL INJECTION VECTOR
695 price => \"price + $inc",
699 price => \['price + ?', [inc => $inc]],