[dbsrgits/dbix-class-introduction-presentation.git] / slideshow.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<title>DBIx::Class (aka DBIC)</title>
<!-- metadata -->
<meta name="contributor" content="youcan[64]netzgesta[46]de" />
<meta name="publisher" content="s5.netzgesta.de" />
<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)" />
<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" />

<meta name="generator" content="S5" />
<meta name="version" content="1" />
<meta name="subject" content="DBIx::Class" />
<meta name="author" content="See first slide" />
<meta name="company" content="N/A" />
<meta name="robots" content="index, follow" />
<meta name="revisit-after" content="7 days" />
<!-- meta additionally -->
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />
<meta http-equiv="Content-Style-Type" content="text/css" /><!-- configuration parameters -->
<meta name="defaultView" content="slideshow" />
<meta name="controlVis" content="hidden" />
<!-- configuration transition extension -->
<meta name="tranSitions" content="true" />
<meta name="fadeDuration" content="500" />
<meta name="incrDuration" content="250" />
<!-- configuration autoplay extension -->
<meta name="autoMatic" content="false" />
<meta name="playLoop" content="true" />
<meta name="playDelay" content="10" />
<!-- configuration audio extension -->
<meta name="audioSupport" content="false" />
<meta name="audioVolume" content="0" />
<meta name="audioError" content="false" />
<!-- configuration audio debug -->
<meta name="audioDebug" content="false" />
<!-- style sheet links -->
<link rel="stylesheet" href="ui/scala_utf/slides.css" type="text/css" media="projection" id="slideProj" />
<link rel="stylesheet" href="ui/scala_utf/outline.css" type="text/css" media="screen" id="outlineStyle" />
<link rel="stylesheet" href="ui/scala_utf/print.css" type="text/css" media="print" id="slidePrint" />
<link rel="stylesheet" href="ui/scala_utf/opera.css" type="text/css" media="projection" id="operaFix" />
<!-- embedded styles -->
<style type="text/css" media="all">
.imgcon {width: 100%; margin: 0 auto; padding: 0; text-align: center;}
#anim {width: 33%; height: 320px; position: relative;}
#anim img {position: absolute; top: 0px; left: 0px;}
.red {color: red;}
.grey {color: gray;}
</style>
<!-- S5 JS -->
<script src="ui/scala_utf/slides.js" type="text/javascript"></script>
</head>
<body>

<div class="layout">
   <div id="controls"><!-- DO NOT EDIT --></div>
   <div id="currentSlide"><!-- DO NOT EDIT --></div>
   <div id="header"></div>
   <div id="footer">
      <h1>DBIx::Class Introduction</h1>
      <h2>YAPC::NA 2010</h2>
   </div>
</div>

<div class="presentation">

   <div class="slide">
      <h1>DBIX::Class (aka DBIC)</h1>
      <h3>for (advanced) beginners</h3>
   </div>

   <div class="slide">
      <h1>Authors</h1>
      <h4>Originally Leo Lapworth @ LPW 2009</h4>
      <h4>Amiri Barksdale</h4>
      <h4>Justin D. Hunter</h4>
      <h4>Arthur Axel "fREW" Schmidt</h4>
   </div>

   <div class="slide">
      <h1>What's up guys?</h1>
      <div class="notes">
         <ul>
            <li>How many people have used any ORM?<ul>
               <li>In Perl?<ul>
                  <li>DBIC?</li>
                  <li>Class::DBI?</li>
                  <li>Rose::DB?</li>
                  <li>Fey?</li>
                  <li>Others?</li>
               </ul></li>
               <li>AR?</li>
               <li>DataMapper?</li>
               <li>(N)Hibernate?</li>
            </ul></li>
         </ul>
      </div>
   </div>

   <div class="slide">
      <h1>Purpose</h1>
      <p>The purpose of this talk is to show you as many features of
      DBIx::Class in 40 minutes so that when you need to do something with
      it later you will know what's possible</p>
   </div>

   <div class="slide">
      <h1>DBIx::Class?</h1>
      <ul>
         <li>ORM (object relational mapper)</li>
         <li>SQL &lt;-&gt; OO (using objects instead of SQL)</li>
         <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Meta</h1>
      <p>These are reasons that are not technical or inherent to
      the code of DBIC, but are totally awesome things about it.</p>
   </div>

   <div class="slide">
      <h1>Large Community</h1>
      <p>Currently there are 88 people listed as contributors to DBIC.  That
      ranges from documentation help, to test help, to added features,
      to entire database support.</p>
   </div>

   <div class="slide">
      <h1>Active Community</h1>
      <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>
   </div>

   <div class="slide">
      <h1>Responsive Community</h1>
      <ul class="incremental">
         <li>needed MSSQL Order by support, they helped me add support</li>
         <li>generally very welcoming of people willing to help</li>
      </ul>
   </div>

   <div class="slide">
      <h1>General ORM</h1>
      <p>These are things that are in most other ORMs, but are still reasons
      to use DBIC over raw SQL.</p>
   </div>

   <div class="slide">
      <h1>Cross DB</h1>
      <p>The vast majority of code should run on all databases without needing tweaking</p>
   </div>

   <div class="slide">
      <h1>Basic CRUD</h1>
      <ul class="incremental">
         <li><strong>C</strong> - Create</li>
         <li><strong>R</strong> - Read</li>
         <li><strong>U</strong> - Update</li>
         <li><strong>D</strong> - Delete</li>
      </ul>
   </div>

   <div class="slide">
      <h1>SQL: Create</h1>
<pre>my $sth = $dbh-&gt;prepare('
   INSERT INTO books
   (title, author_id)
   values (?,?)
');

$sth-&gt;execute(
  'A book title', $author_id
);</pre>
   </div>

   <div class="slide">
      <h1>DBIC: Create</h1>
<pre>my $book = $book_rs-&gt;create({
   title     =&gt; 'A book title',
   author_id =&gt; $author_id,
});</pre>
      <ul class="incremental">
         <li>No need to pair placeholders and values</li>
         <li>Automatically gets autoincremented id for you</li>
         <li>Transparrently uses INSERT ... RETURNING for databases that support it</li>
      </ul>
   </div>

   <div class="slide">
      <h1>SQL: Read</h1>
<pre>my $sth = $dbh-&gt;prepare('
   SELECT title,
   authors.name as author_name
   FROM books, authors
   WHERE books.author = authors.id
');</pre>
   </div>

   <div class="slide">
      <h1>SQL: Read</h1>
<pre>while( my $book = $sth-&gt;fetchrow_hashref() ) {
  print 'Author of '
     . $book-&gt;{title}
     . ' is '
     . $book-&gt;{author_name}
     . "\n";
}</pre>
   </div>

   <div class="slide">
      <h1>DBIC: Read</h1>
<pre>my $book = $book_rs-&gt;find($book_id);

my $book = $book_rs-&gt;search({
   title =&gt; 'A book title',
}, { rows =&gt; 1 })-&gt;next;

my @books = $book_rs-&gt;search({
   author =&gt; $author_id,
})-&gt;all;</pre>
      <ul class="incremental">
         <li>TMTOWTDI</li>
      </ul>
   </div>

   <div class="slide">
      <h1>DBIC: Read</h1>
<pre>while( my $book = $books_rs-&gt;next ) {
 print 'Author of '
    . $book-&gt;title
    . ' is '
    . $book-&gt;author-&gt;name
    . "\n";
}</pre>
   </div>

   <div class="slide">
      <h1>SQL: Update</h1>
<pre>my $update = $dbh-&gt;prepare('
   UPDATE books
   SET title = ?
   WHERE id = ?
');

$update-&gt;execute(
  'New title',<strong>$book_id</strong>
);</pre>
   </div>

   <div class="slide">
      <h1>DBIC: Update</h1>
<pre>$book-&gt;update({
  title =&gt; 'New title',
});</pre>
      <ul class="incremental">
         <li>Lazy Update</li>
      </ul>
   </div>

   <div class="slide">
      <h1>SQL: Delete</h1>
<pre>my $delete = $dbh-&gt;prepare('
   DELETE FROM books
   WHERE id = ?
');

$delete-&gt;execute(<strong>$book_id</strong>);</pre>
   </div>

   <div class="slide">
      <h1>DBIC: Delete</h1>
<pre>$book-&gt;delete;</pre>
   </div>

   <div class="slide">
      <h1>OO Overidability</h1>
      <ul class="incremental">
         <li>Override new if you want to do validation</li>
         <li>Override delete if you want to disable deletion</li>
         <li>and on and on</li>
      </ul>
      <div class="notes">
         <p>I got yelled at about this before by people, so
         we don't get EVERYTHING from OO, but we do get a lot
         so :-P</p>
      </div>
   </div>

   <div class="slide">
      <h1>Convenience Methods</h1>
      <ul class="incremental">
         <li>find_or_create</li>
         <li>create_or_update</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Non-column methods</h1>
      <p>Need a method to get a user's gravatar URL?  Add a
      gravatar_url method to their Result class</p>
   </div>

   <div class="slide">
      <h1>RELATIONSHIPS</h1>
      <ul class="incremental">
         <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#belongs_to">belongs_to</a></li>
         <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_many">has_many</a></li>
         <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#might_have">might_have</a></li>
         <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_one">has_one</a></li>
         <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#many_to_many">many_to_many</a> (technically not a relationship)</li>
         <li>SET AND FORGET</li>
      </ul>
   </div>

   <div class="slide">
      <h1>DBIx::Class Specific Features</h1>
      <p>These things may be in other ORM's, but they are very specific, so doubtful</p>
   </div>

   <div class="slide">
      <h1>-&gt;deploy</h1>
      <p>Perl -&gt; DB</p>
<pre>my $schema = Foo::Schema-&gt;connect(
   $dsn, $user, $pass
);
$schema-&gt;deploy
</pre>
<p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
   </div>

   <div class="slide">
      <h1>Schema::Loader</h1>
      <p>DB -&gt; Perl</p>
<pre>package Foo::Schema;
use strict; use warnings;
use base 'DBIx::Class::Schema::Loader';
__PACKAGE__-&gt;loader_options({
   naming =&gt; 'v7',
   debug  =&gt; $ENV{DBIC_TRACE},
});
1;

# elsewhere...

my $schema = Foo::Schema-&gt;connect(
   $dsn, $user, $pass
);
</pre>
   </div>

   <div class="slide">
      <h1>Populate</h1>
      <p>Made for inserting lots of rows very quicky into database</p>
<pre>$schema-&gt;populate([ Users =&gt;
   [qw( username password )],
   [qw( frew     &gt;=4char$ )],
   [qw(      ...          )],
   [qw(      ...          )],
);
</pre>
      <ul class="incremental">
         <li>I use this to <a href="http://blog.afoolishmanifesto.com/archives/1255">export our whole (200M~) db to SQLite</a></li>
      </ul>
   </div>

   <div class="slide">
      <h1>Multicreate</h1>
      <p>Create an object and all of it's related objects all at once</p>
<pre>$schema-&gt;resultset('Author')-&gt;create({
   name =&gt; 'Stephen King',
   books =&gt; [{ title =&gt; 'The Dark Tower' }],
   address =&gt; {
      street =&gt; '123 Turtle Back Lane',
      state  =&gt; { abbreviation =&gt; 'ME' },
      city   =&gt; { name =&gt; 'Lowell'     },
   },
});
</pre>
      <div class="notes">
         <ul>
            <li>books is a has_many</li>
            <li>address is a belongs_to which in turn belongs to state and city each</li>
            <li>for this to work right state and city must mark abbreviation and name as unique</li>
         </ul>
      </div>
   </div>

   <div class="slide">
      <h1>Extensible</h1>
      <p>DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made
      to allow extensions to nearly every part of it.</p>
   </div>

   <div class="slide">
      <h1>Extensible: DBIC::Helpers</h1>
      <ul class="incremental">
         <li>DBIC::Helper::ResultSet::IgnoreWantarray</li>
         <li>DBIC::Helper::ResultSet::Random</li>
         <li>DBIC::Helper::ResultSet::SetOperations</li>
         <li>DBIC::Helper::Row::JoinTable</li>
         <li>DBIC::Helper::Row::NumifyGet</li>
         <li>DBIC::Helper::Row::SubClass</li>
         <li>DBIC::Helper::Row::ToJSON</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Extensible: DBIC::TimeStamp</h1>
      <ul class="incremental">
         <li>Cross DB</li>
         <li>set_on_create</li>
         <li>set_on_update</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Extensible: Kioku</h1>
      <ul class="incremental">
         <li>DBIx::Class::Schema::KiokuDB</li>
         <li>Kioku is the new hotness</li>
         <li>Mix RDBMS with Object DB</li>
         <li>beta ( == sexy )</li>
      </ul>
   </div>

   <div class="slide">
      <h1>SQL::Abstract</h1>
<pre>my $resultset = $book_rs-&gt;search({
   name =&gt; { -like =&gt; "%$nick%" },
});</pre>
      <ul class="incremental">
         <li>(kinda) introspectible</li>
         <li>Prettier than SQL</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Result vs ResultSet</h1>
         <ul class="incremental">
            <li>Result == Row</li>
            <li>ResultSet == Query Plan<ul class="incremental">
               <li>Internal Join Optimizer for all DB's (!!!)</li>
            </ul></li>
            <li>(less important but...)</li>
            <li>ResultSource == Table</li>
            <li>Storage == Database</li>
         </ul>
   </div>

   <div class="slide">
      <h1>ResultSet methods</h1>
<pre>package MyApp::Schema::ResultSet::Book;
use base 'DBIx::Class::ResultSet';
sub good {
   my $self = shift;
   $self-&gt;search({
      $self-&gt;current_source_alias .
         '.rating' =&gt; { '&gt;=' =&gt; 4 },
   })
};
sub cheap {
   my $self = shift;
   $self-&gt;search({
      $self-&gt;current_source_alias .
         '.price' =&gt; { '&lt;=' =&gt; 5}
   })
};
# ...
1;
      </pre>
   </div>

   <div class="slide">
      <h1>ResultSet method notes</h1>
      <ul class="incremental">
         <li>All searches should be ResultSet methods</li>
         <li>Name has obvious meaning</li>
         <li>current_source_alias helps things to work no matter what</li>
      </ul>
   </div>

   <div class="slide">
      <h1>ResultSet method in Action</h1>
      <pre>$schema-&gt;resultset('Book')-&gt;good</pre>
   </div>

   <div class="slide">
      <h1>ResultSet Chaining</h1>
<pre>$schema-&gt;resultset('Book')
   -&gt;good
   -&gt;cheap
   -&gt;recent
</pre>
   </div>

   <div class="slide">
      <h1>search_related</h1>
<pre>my $score = $schema-&gt;resultset('User')
   -&gt;search({'me.userid' =&gt; 'frew'})
   -&gt;related_resultset('access')
   -&gt;related_resultset('mgmt')
   -&gt;related_resultset('orders')
   -&gt;telephone
   -&gt;search_related( shops =&gt; {
      'shops.datecompleted' =&gt; {
         -between =&gt; ['2009-10-01','2009-10-08']
      }
   })-&gt;completed
   -&gt;related_resultset('rpt_score')
   -&gt;get_column('raw_scores')
   -&gt;first;
</pre>
   </div>

   <div class="slide">
      <h1>bonus rel methods</h1>
<pre>my $book = $author-&gt;create_related(
   <strong>books</strong> =&gt; {
      title =&gt; 'Another Discworld book',
   }
);

my $book2 = $pratchett-&gt;add_to_<strong>books</strong>({
   title =&gt; 'MOAR Discworld book',
});</pre>
      <ul class="incremental">
         <li>Automaticaly fills in foreign key for you</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Excellent Transaction Support</h1>
<pre>$schema-&gt;txn_do(sub {
   ...
});

my $guard = $schema-&gt;txn_scope_guard;
# ...
$guard-&gt;commit;

$schema-&gt;txn_begin; # &lt;-- low level
# ...
$schema-&gt;txn_commit;
</pre>
   </div>

   <div class="slide">
      <h1>InflateColumn</h1>
<pre>package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
use DateTime::Format::MySQL;
# Result code here
__PACKAGE__-&gt;load_components('InflateColumn');
__PACKAGE__-&gt;<strong>inflate_column</strong>(
   <strong>date_published</strong> =&gt; {
      inflate =&gt; sub {
         DateTime::Format::MySQL-&gt;parse_date(
            shift
         )
      },
      deflate =&gt; sub { shift-&gt;ymd },
   },
);
# Automatic see: DBIC::InflateColumn::DateTime</pre>
   </div>

   <div class="slide">
      <h1>InflateColumn: deflation</h1>
<pre>$book-&gt;date_published(DateTime-&gt;now);
$book-&gt;update;</pre>
   </div>

   <div class="slide">
      <h1>InflateColumn: inflation</h1>
<pre>say $book-&gt;date_published-&gt;month_abbr;</pre>

<strong><em>Nov</em></strong>
   </div>

   <div class="slide">
      <h1>FilterColumn</h1>
<pre>package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
# Result code here
__PACKAGE__-&gt;load_components('FilterColumn');

__PACKAGE__-&gt;<strong>filter_column</strong>(
   <strong>length</strong> =&gt; {
      to_storage   =&gt; 'to_metric',
      from_storage =&gt; 'to_imperial',
   },
);

sub to_metric   { $_[1] * .305 }
sub to_imperial { $_[1] * 3.28 }
   </div>

   <div class="slide">
      <h1>ResultSetColumn</h1>
<pre>my $rsc = $schema-&gt;resultset('Book')
   -&gt;get_column('price');
$rsc-&gt;min;
$rsc-&gt;max;
$rsc-&gt;sum;
</pre>
   </div>

   <div class="slide">
      <h1>Aggregates</h1>
<pre>my @res = $rs-&gt;search({}, {
   select   =&gt; [
      'price',
      'genre',
      { max =&gt; price },
      { avg =&gt; price },
   ],
   as       =&gt; [
      qw(price genre max_price avg_price)
   ],
   group_by =&gt; [qw(price genre)],
});
for (@res) {
   say $_-&gt;price . ' ' . $_-&gt;genre;
   say $_-&gt;get_column('max_price');
   say $_-&gt;get_column('min_price');
}</pre>
   </div>

   <div class="slide">
      <h1>Aggregates Notes</h1>
      <ul class="incremental">
         <li>Careful, get_column can basicaly mean THREE things</li>
         <li>private for get what you should use an accessor for</li>
         <li>public for what there is no accessor for</li>
         <li>public for get resultset column (prev slide)</li>
      </ul>
   </div>

   <div class="slide">
      <h1>HRI</h1>
<pre>$rs-&gt;search({}, {
  result_class =&gt;
    'DBIx::Class::ResultClass::HashRefInflator',
});</pre>
      <ul class="incremental">
         <li>Easy on memory</li>
         <li>Mega fast</li>
         <li>Great for quick debugging</li>
         <li>Great for performance tuning (we went from 2m to &lt; 3s)</li>
      </ul>
   </div>

   <div class="slide">
      <h1>Subquery Support</h1>
<pre>my $inside_query = $schema-&gt;resultset('Artist')
   -&gt;search({
    name =&gt; [ 'Billy Joel', 'Brittany Spears' ],
})-&gt;get_column('id')-&gt;as_query;

my $rs = $schema-&gt;resultset('CD')-&gt;search({
    artist_id =&gt; { -in =&gt; $inside_query },
});</pre>
   </div>

   <div class="slide">
      <h1>Bare SQL w/ Placeholders</h1>
<pre>$rs-&gt;update({
   # !!! SQL INJECTION VECTOR
   price =&gt; \"price + $inc",
});

$rs-&gt;update({
   price =&gt; \['price + ?', [inc =&gt; $inc]],
});
</pre>
   </div>

   <div class="slide">
      <h1>Questions?</h1>
   </div>

   <div class="slide">
      <h1>END</h1>
   </div>
</div>
</body>
</html>
Commit	Line	Data
52d980cb	1	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	2	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
	3
	4	<html xmlns="http://www.w3.org/1999/xhtml">
	5
	6	<head>
	7	<title>DBIx::Class (aka DBIC)</title>
	8	<!-- metadata -->
	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" />
	13
	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;}
	51	.red {color: red;}
	52	.grey {color: gray;}
	53	</style>
	54	<!-- S5 JS -->
	55	<script src="ui/scala_utf/slides.js" type="text/javascript"></script>
	56	</head>
	57	<body>
	58
	59	<div class="layout">
	60	<div id="controls"><!-- DO NOT EDIT --></div>
	61	<div id="currentSlide"><!-- DO NOT EDIT --></div>
	62	<div id="header"></div>
	63	<div id="footer">
	64	<h1>DBIx::Class Introduction</h1>
65	<h2>YAPC::NA 2010</h2>
66	</div>
67	</div>
68
69	<div class="presentation">
70
71	<div class="slide">
72	<h1>DBIX::Class (aka DBIC)</h1>
73	<h3>for (advanced) beginners</h3>
0cffcad7	74	</div>
	75
	76	<div class="slide">
	77	<h1>Authors</h1>
	78	<h4>Originally Leo Lapworth @ LPW 2009</h4>
da0b46fd	79	<h4>Amiri Barksdale</h4>
0cffcad7	80	<h4>Justin D. Hunter</h4>
5cac74c7	81	<h4>Arthur Axel "fREW" Schmidt</h4>
52d980cb	82	</div>
	83
	84	<div class="slide">
7365a110	85	<h1>What's up guys?</h1>
0cffcad7	86	<div class="notes">
0cffcad7	87	<ul>
0cffcad7	88	<li>How many people have used any ORM?<ul>
	89	<li>In Perl?<ul>
	90	<li>DBIC?</li>
da0b46fd	91	<li>Class::DBI?</li>
	92	<li>Rose::DB?</li>
	93	<li>Fey?</li>
	94	<li>Others?</li>
0cffcad7	95	</ul></li>
da0b46fd	96	<li>AR?</li>
da0b46fd	97	<li>DataMapper?</li>
0cffcad7	98	<li>(N)Hibernate?</li>
	99	</ul></li>
	100	</ul>
	101	</div>
52d980cb	102	</div>
	103
	104	<div class="slide">
da0b46fd	105	<h1>Purpose</h1>
	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>
	109	</div>
	110
	111	<div class="slide">
52d980cb	112	<h1>DBIx::Class?</h1>
	113	<ul>
	114	<li>ORM (object relational mapper)</li>
	115	<li>SQL <-> OO (using objects instead of SQL)</li>
52d980cb	116	<li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
	117	</ul>
	118	</div>
	119
	120	<div class="slide">
da0b46fd	121	<h1>Meta</h1>
	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>
	124	</div>
	125
	126	<div class="slide">
	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>
	131	</div>
	132
	133	<div class="slide">
	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>
	136	</div>
	137
	138	<div class="slide">
	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>
	143	</ul>
	144	</div>
	145
	146	<div class="slide">
	147	<h1>General ORM</h1>
	148	<p>These are things that are in most other ORMs, but are still reasons
	149	to use DBIC over raw SQL.</p>
	150	</div>
	151
	152	<div class="slide">
	153	<h1>Cross DB</h1>
	154	<p>The vast majority of code should run on all databases without needing tweaking</p>
52d980cb	155	</div>
	156
	157	<div class="slide">
7365a110	158	<h1>Basic CRUD</h1>
da0b46fd	159	<ul class="incremental">
52d980cb	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>
	164	</ul>
	165	</div>
	166
	167	<div class="slide">
5cac74c7	168	<h1>SQL: Create</h1>
52d980cb	169	<pre>my $sth = $dbh->prepare('
52d980cb	170	INSERT INTO books
01b7b88c	171	(title, author_id)
52d980cb	172	values (?,?)
	173	');
	174
	175	$sth->execute(
	176	'A book title', $author_id
	177	);</pre>
	178	</div>
	179
	180	<div class="slide">
5cac74c7	181	<h1>DBIC: Create</h1>
01b7b88c	182	<pre>my $book = $book_rs->create({
	183	title => 'A book title',
	184	author_id => $author_id,
52d980cb	185	});</pre>
da0b46fd	186	<ul class="incremental">
da0b46fd	187	<li>No need to pair placeholders and values</li>
ec535d03	188	<li>Automatically gets autoincremented id for you</li>
513517e4	189	<li>Transparrently uses INSERT ... RETURNING for databases that support it</li>
da0b46fd	190	</ul>
52d980cb	191	</div>
	192
	193	<div class="slide">
7365a110	194	<h1>SQL: Read</h1>
	195	<pre>my $sth = $dbh->prepare('
	196	SELECT title,
	197	authors.name as author_name
	198	FROM books, authors
	199	WHERE books.author = authors.id
	200	');</pre>
	201	</div>
	202
	203	<div class="slide">
	204	<h1>SQL: Read</h1>
	205	<pre>while( my $book = $sth->fetchrow_hashref() ) {
	206	print 'Author of '
	207	. $book->{title}
	208	. ' is '
	209	. $book->{author_name}
	210	. "\n";
	211	}</pre>
52d980cb	212	</div>
	213
	214	<div class="slide">
5cac74c7	215	<h1>DBIC: Read</h1>
01b7b88c	216	<pre>my $book = $book_rs->find($book_id);
52d980cb	217
01b7b88c	218	my $book = $book_rs->search({
52d980cb	219	title => 'A book title',
3ab0ef72	220	}, { rows => 1 })->next;
52d980cb	221
01b7b88c	222	my @books = $book_rs->search({
52d980cb	223	author => $author_id,
52d980cb	224	})->all;</pre>
da0b46fd	225	<ul class="incremental">
	226	<li>TMTOWTDI</li>
	227	</ul>
52d980cb	228	</div>
	229
	230	<div class="slide">
5cac74c7	231	<h1>DBIC: Read</h1>
5cac74c7	232	<pre>while( my $book = $books_rs->next ) {
52d980cb	233	print 'Author of '
	234	. $book->title
	235	. ' is '
	236	. $book->author->name
	237	. "\n";
	238	}</pre>
	239	</div>
	240
	241	<div class="slide">
7365a110	242	<h1>SQL: Update</h1>
	243	<pre>my $update = $dbh->prepare('
	244	UPDATE books
	245	SET title = ?
	246	WHERE id = ?
	247	');
	248
	249	$update->execute(
	250	'New title',<strong>$book_id</strong>
	251	);</pre>
	252	</div>
	253
	254	<div class="slide">
5cac74c7	255	<h1>DBIC: Update</h1>
52d980cb	256	<pre>$book->update({
	257	title => 'New title',
	258	});</pre>
513517e4	259	<ul class="incremental">
	260	<li>Lazy Update</li>
	261	</ul>
52d980cb	262	</div>
	263
	264	<div class="slide">
7365a110	265	<h1>SQL: Delete</h1>
	266	<pre>my $delete = $dbh->prepare('
	267	DELETE FROM books
	268	WHERE id = ?
	269	');
	270
	271	$delete->execute(<strong>$book_id</strong>);</pre>
	272	</div>
	273
	274	<div class="slide">
5cac74c7	275	<h1>DBIC: Delete</h1>
52d980cb	276	<pre>$book->delete;</pre>
	277	</div>
	278
	279	<div class="slide">
da0b46fd	280	<h1>OO Overidability</h1>
	281	<ul class="incremental">
	282	<li>Override new if you want to do validation</li>
	283	<li>Override delete if you want to disable deletion</li>
	284	<li>and on and on</li>
	285	</ul>
	286	<div class="notes">
	287	<p>I got yelled at about this before by people, so
	288	we don't get EVERYTHING from OO, but we do get a lot
	289	so :-P</p>
	290	</div>
52d980cb	291	</div>
	292
	293	<div class="slide">
da0b46fd	294	<h1>Convenience Methods</h1>
	295	<ul class="incremental">
	296	<li>find_or_create</li>
	297	<li>create_or_update</li>
	298	</ul>
492be2ae	299	</div>
	300
	301	<div class="slide">
da0b46fd	302	<h1>Non-column methods</h1>
	303	<p>Need a method to get a user's gravatar URL? Add a
	304	gravatar_url method to their Result class</p>
	305	</div>
	306
	307	<div class="slide">
	308	<h1>RELATIONSHIPS</h1>
	309	<ul class="incremental">
	310	<li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#belongs_to">belongs_to</a></li>
	311	<li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_many">has_many</a></li>
	312	<li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#might_have">might_have</a></li>
	313	<li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#has_one">has_one</a></li>
	314	<li><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship#many_to_many">many_to_many</a> (technically not a relationship)</li>
	315	<li>SET AND FORGET</li>
	316	</ul>
	317	</div>
	318
	319	<div class="slide">
	320	<h1>DBIx::Class Specific Features</h1>
	321	<p>These things may be in other ORM's, but they are very specific, so doubtful</p>
52d980cb	322	</div>
	323
	324	<div class="slide">
9158dee5	325	<h1>->deploy</h1>
9158dee5	326	<p>Perl -> DB</p>
d1ed303d	327	<pre>my $schema = Foo::Schema->connect(
	328	$dsn, $user, $pass
	329	);
9158dee5	330	$schema->deploy
	331	</pre>
	332	<p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
	333	</div>
	334
	335	<div class="slide">
52d980cb	336	<h1>Schema::Loader</h1>
9158dee5	337	<p>DB -> Perl</p>
451b57c2	338	<pre>package Foo::Schema;
c7321880	339	use strict; use warnings;
	340	use base 'DBIx::Class::Schema::Loader';
	341	__PACKAGE__->loader_options({
	342	naming => 'v7',
	343	debug => $ENV{DBIC_TRACE},
	344	});
	345	1;
	346
	347	# elsewhere...
	348
d1ed303d	349	my $schema = Foo::Schema->connect(
	350	$dsn, $user, $pass
	351	);
c7321880	352	</pre>
52d980cb	353	</div>
	354
	355	<div class="slide">
da0b46fd	356	<h1>Populate</h1>
	357	<p>Made for inserting lots of rows very quicky into database</p>
	358	<pre>$schema->populate([ Users =>
	359	[qw( username password )],
d1ed303d	360	[qw( frew >=4char$ )],
da0b46fd	361	[qw( ... )],
	362	[qw( ... )],
	363	);
	364	</pre>
	365	<ul class="incremental">
	366	<li>I use this to <a href="http://blog.afoolishmanifesto.com/archives/1255">export our whole (200M~) db to SQLite</a></li>
	367	</ul>
52d980cb	368	</div>
	369
	370	<div class="slide">
da0b46fd	371	<h1>Multicreate</h1>
	372	<p>Create an object and all of it's related objects all at once</p>
	373	<pre>$schema->resultset('Author')->create({
	374	name => 'Stephen King',
	375	books => [{ title => 'The Dark Tower' }],
	376	address => {
	377	street => '123 Turtle Back Lane',
	378	state => { abbreviation => 'ME' },
d1ed303d	379	city => { name => 'Lowell' },
da0b46fd	380	},
7e77ca11	381	});
da0b46fd	382	</pre>
	383	<div class="notes">
	384	<ul>
	385	<li>books is a has_many</li>
	386	<li>address is a belongs_to which in turn belongs to state and city each</li>
	387	<li>for this to work right state and city must mark abbreviation and name as unique</li>
	388	</ul>
	389	</div>
	390	</div>
	391
	392	<div class="slide">
	393	<h1>Extensible</h1>
	394	<p>DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made
	395	to allow extensions to nearly every part of it.</p>
	396	</div>
	397
	398	<div class="slide">
	399	<h1>Extensible: DBIC::Helpers</h1>
	400	<ul class="incremental">
d1ed303d	401	<li>DBIC::Helper::ResultSet::IgnoreWantarray</li>
	402	<li>DBIC::Helper::ResultSet::Random</li>
	403	<li>DBIC::Helper::ResultSet::SetOperations</li>
	404	<li>DBIC::Helper::Row::JoinTable</li>
	405	<li>DBIC::Helper::Row::NumifyGet</li>
	406	<li>DBIC::Helper::Row::SubClass</li>
	407	<li>DBIC::Helper::Row::ToJSON</li>
da0b46fd	408	</ul>
	409	</div>
	410
	411	<div class="slide">
	412	<h1>Extensible: DBIC::TimeStamp</h1>
	413	<ul class="incremental">
	414	<li>Cross DB</li>
	415	<li>set_on_create</li>
	416	<li>set_on_update</li>
	417	</ul>
	418	</div>
	419
	420	<div class="slide">
d1ed303d	421	<h1>Extensible: Kioku</h1>
da0b46fd	422	<ul class="incremental">
d1ed303d	423	<li>DBIx::Class::Schema::KiokuDB</li>
da0b46fd	424	<li>Kioku is the new hotness</li>
	425	<li>Mix RDBMS with Object DB</li>
	426	<li>beta ( == sexy )</li>
	427	</ul>
	428	</div>
	429
	430	<div class="slide">
	431	<h1>SQL::Abstract</h1>
7e77ca11	432	<pre>my $resultset = $book_rs->search({
	433	name => { -like => "%$nick%" },
	434	});</pre>
da0b46fd	435	<ul class="incremental">
	436	<li>(kinda) introspectible</li>
	437	<li>Prettier than SQL</li>
	438	</ul>
	439	</div>
	440
	441	<div class="slide">
	442	<h1>Result vs ResultSet</h1>
	443	<ul class="incremental">
	444	<li>Result == Row</li>
513517e4	445	<li>ResultSet == Query Plan<ul class="incremental">
	446	<li>Internal Join Optimizer for all DB's (!!!)</li>
	447	</ul></li>
da0b46fd	448	<li>(less important but...)</li>
	449	<li>ResultSource == Table</li>
	450	<li>Storage == Database</li>
	451	</ul>
	452	</div>
	453
	454	<div class="slide">
	455	<h1>ResultSet methods</h1>
	456	<pre>package MyApp::Schema::ResultSet::Book;
	457	use base 'DBIx::Class::ResultSet';
	458	sub good {
d1ed303d	459	my $self = shift;
	460	$self->search({
	461	$self->current_source_alias .
	462	'.rating' => { '>=' => 4 },
da0b46fd	463	})
	464	};
	465	sub cheap {
d1ed303d	466	my $self = shift;
	467	$self->search({
	468	$self->current_source_alias .
	469	'.price' => { '<=' => 5}
da0b46fd	470	})
	471	};
	472	# ...
	473	1;
	474	</pre>
d1ed303d	475	</div>
	476
	477	<div class="slide">
	478	<h1>ResultSet method notes</h1>
da0b46fd	479	<ul class="incremental">
	480	<li>All searches should be ResultSet methods</li>
	481	<li>Name has obvious meaning</li>
d1ed303d	482	<li>current_source_alias helps things to work no matter what</li>
da0b46fd	483	</ul>
	484	</div>
	485
	486	<div class="slide">
	487	<h1>ResultSet method in Action</h1>
	488	<pre>$schema->resultset('Book')->good</pre>
	489	</div>
	490
	491	<div class="slide">
	492	<h1>ResultSet Chaining</h1>
	493	<pre>$schema->resultset('Book')
	494	->good
	495	->cheap
	496	->recent
6e5edefe	497	</pre>
52d980cb	498	</div>
	499
	500	<div class="slide">
7e77ca11	501	<h1>search_related</h1>
d1ed303d	502	<pre>my $score = $schema->resultset('User')
d1ed303d	503	->search({'me.userid' => 'frew'})
3ab0ef72	504	->related_resultset('access')
3ab0ef72	505	->related_resultset('mgmt')
d1ed303d	506	->related_resultset('orders')
	507	->telephone
	508	->search_related( shops => {
	509	'shops.datecompleted' => {
	510	-between => ['2009-10-01','2009-10-08']
	511	}
	512	})->completed
	513	->related_resultset('rpt_score')
	514	->get_column('raw_scores')
	515	->first;
3ab0ef72	516	</pre>
	517	</div>
	518
	519	<div class="slide">
7e77ca11	520	<h1>bonus rel methods</h1>
7e77ca11	521	<pre>my $book = $author->create_related(
d1ed303d	522	<strong>books</strong> => {
	523	title => 'Another Discworld book',
	524	}
	525	);
52d980cb	526
7e77ca11	527	my $book2 = $pratchett->add_to_<strong>books</strong>({
	528	title => 'MOAR Discworld book',
	529	});</pre>
	530	<ul class="incremental">
	531	<li>Automaticaly fills in foreign key for you</li>
	532	</ul>
52d980cb	533	</div>
	534
	535	<div class="slide">
7e77ca11	536	<h1>Excellent Transaction Support</h1>
	537	<pre>$schema->txn_do(sub {
	538	...
	539	});
52d980cb	540
7e77ca11	541	my $guard = $schema->txn_scope_guard;
	542	# ...
	543	$guard->commit;
52d980cb	544
7e77ca11	545	$schema->txn_begin; # <-- low level
	546	# ...
	547	$schema->txn_commit;
	548	</pre>
52d980cb	549	</div>
	550
	551	<div class="slide">
7e77ca11	552	<h1>InflateColumn</h1>
2a65778d	553	<pre>package Foo::Schema::Result::Book;
2a65778d	554	use base 'DBIx::Class::Core';
d1ed303d	555	use DateTime::Format::MySQL;
451b57c2	556	# Result code here
451b57c2	557	__PACKAGE__->load_components('InflateColumn');
52d980cb	558	__PACKAGE__-><strong>inflate_column</strong>(
52d980cb	559	<strong>date_published</strong> => {
d1ed303d	560	inflate => sub {
	561	DateTime::Format::MySQL->parse_date(
	562	shift
	563	)
	564	},
	565	deflate => sub { shift->ymd },
451b57c2	566	},
52d980cb	567	);
d1ed303d	568	# Automatic see: DBIC::InflateColumn::DateTime</pre>
52d980cb	569	</div>
	570
	571	<div class="slide">
7e77ca11	572	<h1>InflateColumn: deflation</h1>
52d980cb	573	<pre>$book->date_published(DateTime->now);
	574	$book->update;</pre>
	575	</div>
	576
	577	<div class="slide">
7e77ca11	578	<h1>InflateColumn: inflation</h1>
d1ed303d	579	<pre>say $book->date_published->month_abbr;</pre>
52d980cb	580
	581	<strong><em>Nov</em></strong>
	582	</div>
	583
	584	<div class="slide">
7e77ca11	585	<h1>FilterColumn</h1>
7e77ca11	586	<pre>package Foo::Schema::Result::Book;
2a65778d	587	use base 'DBIx::Class::Core';
7e77ca11	588	# Result code here
7e77ca11	589	__PACKAGE__->load_components('FilterColumn');
52d980cb	590
7e77ca11	591	__PACKAGE__-><strong>filter_column</strong>(
	592	<strong>length</strong> => {
	593	to_storage => 'to_metric',
	594	from_storage => 'to_imperial',
	595	},
52d980cb	596	);
52d980cb	597
7e77ca11	598	sub to_metric { $_[1] * .305 }
7e77ca11	599	sub to_imperial { $_[1] * 3.28 }
52d980cb	600	</div>
	601
	602	<div class="slide">
7e77ca11	603	<h1>ResultSetColumn</h1>
d1ed303d	604	<pre>my $rsc = $schema->resultset('Book')
d1ed303d	605	->get_column('price');
7e77ca11	606	$rsc->min;
	607	$rsc->max;
	608	$rsc->sum;
6548782a	609	</pre>
52d980cb	610	</div>
	611
	612	<div class="slide">
7e77ca11	613	<h1>Aggregates</h1>
7e77ca11	614	<pre>my @res = $rs->search({}, {
d1ed303d	615	select => [
	616	'price',
	617	'genre',
	618	{ max => price },
	619	{ avg => price },
	620	],
	621	as => [
	622	qw(price genre max_price avg_price)
	623	],
7e77ca11	624	group_by => [qw(price genre)],
	625	});
	626	for (@res) {
	627	say $_->price . ' ' . $_->genre;
	628	say $_->get_column('max_price');
	629	say $_->get_column('min_price');
	630	}</pre>
d1ed303d	631	</div>
	632
	633	<div class="slide">
	634	<h1>Aggregates Notes</h1>
7e77ca11	635	<ul class="incremental">
	636	<li>Careful, get_column can basicaly mean THREE things</li>
	637	<li>private for get what you should use an accessor for</li>
	638	<li>public for what there is no accessor for</li>
	639	<li>public for get resultset column (prev slide)</li>
	640	</ul>
52d980cb	641	</div>
	642
	643	<div class="slide">
7e77ca11	644	<h1>HRI</h1>
7e77ca11	645	<pre>$rs->search({}, {
d1ed303d	646	result_class =>
d1ed303d	647	'DBIx::Class::ResultClass::HashRefInflator',
52d980cb	648	});</pre>
7e77ca11	649	<ul class="incremental">
	650	<li>Easy on memory</li>
	651	<li>Mega fast</li>
	652	<li>Great for quick debugging</li>
	653	<li>Great for performance tuning (we went from 2m to < 3s)</li>
52d980cb	654	</ul>
	655	</div>
	656
	657	<div class="slide">
7e77ca11	658	<h1>Subquery Support</h1>
d1ed303d	659	<pre>my $inside_query = $schema->resultset('Artist')
d1ed303d	660	->search({
7e77ca11	661	name => [ 'Billy Joel', 'Brittany Spears' ],
d1ed303d	662	})->get_column('id')->as_query;
9158dee5	663
7e77ca11	664	my $rs = $schema->resultset('CD')->search({
d1ed303d	665	artist_id => { -in => $inside_query },
7e77ca11	666	});</pre>
52d980cb	667	</div>
	668
	669	<div class="slide">
7e77ca11	670	<h1>Bare SQL w/ Placeholders</h1>
7e77ca11	671	<pre>$rs->update({
d1ed303d	672	# !!! SQL INJECTION VECTOR
d1ed303d	673	price => \"price + $inc",
7e77ca11	674	});
	675
	676	$rs->update({
	677	price => \['price + ?', [inc => $inc]],
	678	});
	679	</pre>
52d980cb	680	</div>
	681
	682	<div class="slide">
7e77ca11	683	<h1>Questions?</h1>
52d980cb	684	</div>
	685
	686	<div class="slide">
7e77ca11	687	<h1>END</h1>
52d980cb	688	</div>
52d980cb	689	</div>
	690	</body>
	691	</html>