[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>DBIx::Class::Helper::ResultSet::IgnoreWantarray</li>
         <li>DBIx::Class::Helper::ResultSet::Random</li>
         <li>DBIx::Class::Helper::ResultSet::SetOperations</li>
         <li>DBIx::Class::Helper::Row::JoinTable</li>
         <li>DBIx::Class::Helper::Row::NumifyGet</li>
         <li>DBIx::Class::Helper::Row::SubClass</li>
         <li>DBIx::Class::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: DBIx::Class::Schema::KiokuDB</h1>
      <ul class="incremental">
         <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 {
   $_[0]-&gt;search({
      rating =&gt; { '&gt;=' =&gt; 4 },
   })
};
sub cheap {
   $_[0]-&gt;search({
      price =&gt; { '&lt;=' =&gt; 5}
   })
};
# ...
1;
      </pre>
      <ul class="incremental">
         <li>All searches should be ResultSet methods</li>
         <li>Name has obvious meaning</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 @test_rs = ($schema-&gt;resultset('User')-&gt;search({'me.userid' =&gt; 'marial'})
   -&gt;related_resultset('access')
   -&gt;related_resultset('mgmt')
   -&gt;related_resultset('orders')-&gt;search({'orders.reporttype' =&gt; 0})
   -&gt;related_resultset('shops')-&gt;search({'shops.status' =&gt; 'Completed', 'shops.datecompleted' =&gt; {-between =&gt; ['2009-10-01','2009-10-08']}})
   -&gt;related_resultset('rpt_score')-&gt;get_column('raw_scores')-&gt;first);
</pre>
   </div>

   <div class="slide">
      <h1>related_resultset</h1>
<pre>my @screens = $user-&gt;roles
   -&gt;related_resultset('role_permissions')
   -&gt;related_resultset('permission')
   -&gt;related_resultset('permission_screens')
   -&gt;related_resultset('screen')
   -&gt;search(undef, { distinct =&gt; 1 })-&gt;all;</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';
# Result code here
__PACKAGE__-&gt;load_components('InflateColumn');
use DateTime::Format::MySQL;
__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: DBIx::Class::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>my $date_published = $book-&gt;date_published;
print $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 }
# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
   </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; [qw(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>
      <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_rs = $schema-&gt;resultset('Artist')-&gt;search({
    name =&gt; [ 'Billy Joel', 'Brittany Spears' ],
});

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

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

$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>
	326	<p>Perl -> DB</p>
	327	<pre>my $schema = Foo::Schema->connect($dsn, $user, $pass);
	328	$schema->deploy
	329	</pre>
	330	<p>See also: <a href="http://search.cpan.org/perldoc?DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a></p>
	331	</div>
	332
	333	<div class="slide">
52d980cb	334	<h1>Schema::Loader</h1>
9158dee5	335	<p>DB -> Perl</p>
451b57c2	336	<pre>package Foo::Schema;
c7321880	337	use strict; use warnings;
	338	use base 'DBIx::Class::Schema::Loader';
	339	__PACKAGE__->loader_options({
	340	naming => 'v7',
	341	debug => $ENV{DBIC_TRACE},
	342	});
	343	1;
	344
	345	# elsewhere...
	346
451b57c2	347	my $schema = Foo::Schema->connect($dsn, $user, $pass);
c7321880	348	</pre>
52d980cb	349	</div>
	350
	351	<div class="slide">
da0b46fd	352	<h1>Populate</h1>
	353	<p>Made for inserting lots of rows very quicky into database</p>
	354	<pre>$schema->populate([ Users =>
	355	[qw( username password )],
	356	[qw( frew >=4char$ )],
	357	[qw( ... )],
	358	[qw( ... )],
	359	);
	360	</pre>
	361	<ul class="incremental">
	362	<li>I use this to <a href="http://blog.afoolishmanifesto.com/archives/1255">export our whole (200M~) db to SQLite</a></li>
	363	</ul>
52d980cb	364	</div>
	365
	366	<div class="slide">
da0b46fd	367	<h1>Multicreate</h1>
	368	<p>Create an object and all of it's related objects all at once</p>
	369	<pre>$schema->resultset('Author')->create({
	370	name => 'Stephen King',
	371	books => [{ title => 'The Dark Tower' }],
	372	address => {
	373	street => '123 Turtle Back Lane',
	374	state => { abbreviation => 'ME' },
	375	city => { name => 'Lowell' },
	376	},
7e77ca11	377	});
da0b46fd	378	</pre>
	379	<div class="notes">
	380	<ul>
	381	<li>books is a has_many</li>
	382	<li>address is a belongs_to which in turn belongs to state and city each</li>
	383	<li>for this to work right state and city must mark abbreviation and name as unique</li>
	384	</ul>
	385	</div>
	386	</div>
	387
	388	<div class="slide">
	389	<h1>Extensible</h1>
	390	<p>DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made
	391	to allow extensions to nearly every part of it.</p>
	392	</div>
	393
	394	<div class="slide">
	395	<h1>Extensible: DBIC::Helpers</h1>
	396	<ul class="incremental">
	397	<li>DBIx::Class::Helper::ResultSet::IgnoreWantarray</li>
	398	<li>DBIx::Class::Helper::ResultSet::Random</li>
	399	<li>DBIx::Class::Helper::ResultSet::SetOperations</li>
	400	<li>DBIx::Class::Helper::Row::JoinTable</li>
	401	<li>DBIx::Class::Helper::Row::NumifyGet</li>
	402	<li>DBIx::Class::Helper::Row::SubClass</li>
	403	<li>DBIx::Class::Helper::Row::ToJSON</li>
	404	</ul>
	405	</div>
	406
	407	<div class="slide">
	408	<h1>Extensible: DBIC::TimeStamp</h1>
	409	<ul class="incremental">
	410	<li>Cross DB</li>
	411	<li>set_on_create</li>
	412	<li>set_on_update</li>
	413	</ul>
	414	</div>
	415
	416	<div class="slide">
	417	<h1>Extensible: DBIx::Class::Schema::KiokuDB</h1>
	418	<ul class="incremental">
	419	<li>Kioku is the new hotness</li>
	420	<li>Mix RDBMS with Object DB</li>
	421	<li>beta ( == sexy )</li>
	422	</ul>
	423	</div>
	424
	425	<div class="slide">
	426	<h1>SQL::Abstract</h1>
7e77ca11	427	<pre>my $resultset = $book_rs->search({
	428	name => { -like => "%$nick%" },
	429	});</pre>
da0b46fd	430	<ul class="incremental">
	431	<li>(kinda) introspectible</li>
	432	<li>Prettier than SQL</li>
	433	</ul>
	434	</div>
	435
	436	<div class="slide">
	437	<h1>Result vs ResultSet</h1>
	438	<ul class="incremental">
	439	<li>Result == Row</li>
513517e4	440	<li>ResultSet == Query Plan<ul class="incremental">
	441	<li>Internal Join Optimizer for all DB's (!!!)</li>
	442	</ul></li>
da0b46fd	443	<li>(less important but...)</li>
	444	<li>ResultSource == Table</li>
	445	<li>Storage == Database</li>
	446	</ul>
	447	</div>
	448
	449	<div class="slide">
	450	<h1>ResultSet methods</h1>
	451	<pre>package MyApp::Schema::ResultSet::Book;
	452	use base 'DBIx::Class::ResultSet';
	453	sub good {
7e77ca11	454	$_[0]->search({
da0b46fd	455	rating => { '>=' => 4 },
	456	})
	457	};
	458	sub cheap {
7e77ca11	459	$_[0]->search({
da0b46fd	460	price => { '<=' => 5}
	461	})
	462	};
	463	# ...
	464	1;
	465	</pre>
	466	<ul class="incremental">
	467	<li>All searches should be ResultSet methods</li>
	468	<li>Name has obvious meaning</li>
	469	</ul>
	470	</div>
	471
	472	<div class="slide">
	473	<h1>ResultSet method in Action</h1>
	474	<pre>$schema->resultset('Book')->good</pre>
	475	</div>
	476
	477	<div class="slide">
	478	<h1>ResultSet Chaining</h1>
	479	<pre>$schema->resultset('Book')
	480	->good
	481	->cheap
	482	->recent
6e5edefe	483	</pre>
52d980cb	484	</div>
	485
	486	<div class="slide">
7e77ca11	487	<h1>search_related</h1>
3ab0ef72	488	<pre>my @test_rs = ($schema->resultset('User')->search({'me.userid' => 'marial'})
	489	->related_resultset('access')
	490	->related_resultset('mgmt')
	491	->related_resultset('orders')->search({'orders.reporttype' => 0})
	492	->related_resultset('shops')->search({'shops.status' => 'Completed', 'shops.datecompleted' => {-between => ['2009-10-01','2009-10-08']}})
	493	->related_resultset('rpt_score')->get_column('raw_scores')->first);
	494	</pre>
	495	</div>
	496
	497	<div class="slide">
	498	<h1>related_resultset</h1>
	499	<pre>my @screens = $user->roles
	500	->related_resultset('role_permissions')
	501	->related_resultset('permission')
	502	->related_resultset('permission_screens')
	503	->related_resultset('screen')
	504	->search(undef, { distinct => 1 })->all;</pre>
52d980cb	505	</div>
	506
	507	<div class="slide">
7e77ca11	508	<h1>bonus rel methods</h1>
	509	<pre>my $book = $author->create_related(
	510	<strong>books</strong> => {
	511	title => 'Another Discworld book',
	512	});
52d980cb	513
7e77ca11	514	my $book2 = $pratchett->add_to_<strong>books</strong>({
	515	title => 'MOAR Discworld book',
	516	});</pre>
	517	<ul class="incremental">
	518	<li>Automaticaly fills in foreign key for you</li>
	519	</ul>
52d980cb	520	</div>
	521
	522	<div class="slide">
7e77ca11	523	<h1>Excellent Transaction Support</h1>
	524	<pre>$schema->txn_do(sub {
	525	...
	526	});
52d980cb	527
7e77ca11	528	my $guard = $schema->txn_scope_guard;
	529	# ...
	530	$guard->commit;
52d980cb	531
7e77ca11	532	$schema->txn_begin; # <-- low level
	533	# ...
	534	$schema->txn_commit;
	535	</pre>
52d980cb	536	</div>
	537
	538	<div class="slide">
7e77ca11	539	<h1>InflateColumn</h1>
2a65778d	540	<pre>package Foo::Schema::Result::Book;
2a65778d	541	use base 'DBIx::Class::Core';
451b57c2	542	# Result code here
451b57c2	543	__PACKAGE__->load_components('InflateColumn');
52d980cb	544	use DateTime::Format::MySQL;
52d980cb	545	__PACKAGE__-><strong>inflate_column</strong>(
	546	<strong>date_published</strong> => {
	547	inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
	548	deflate => sub { shift->ymd},
451b57c2	549	},
52d980cb	550	);
	551	# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
	552	</div>
	553
	554	<div class="slide">
7e77ca11	555	<h1>InflateColumn: deflation</h1>
52d980cb	556	<pre>$book->date_published(DateTime->now);
	557	$book->update;</pre>
	558	</div>
	559
	560	<div class="slide">
7e77ca11	561	<h1>InflateColumn: inflation</h1>
52d980cb	562	<pre>my $date_published = $book->date_published;
	563	print $date_published->month_abbr;</pre>
	564
	565	<strong><em>Nov</em></strong>
	566	</div>
	567
	568	<div class="slide">
7e77ca11	569	<h1>FilterColumn</h1>
7e77ca11	570	<pre>package Foo::Schema::Result::Book;
2a65778d	571	use base 'DBIx::Class::Core';
7e77ca11	572	# Result code here
7e77ca11	573	__PACKAGE__->load_components('FilterColumn');
52d980cb	574
7e77ca11	575	__PACKAGE__-><strong>filter_column</strong>(
	576	<strong>length</strong> => {
	577	to_storage => 'to_metric',
	578	from_storage => 'to_imperial',
	579	},
52d980cb	580	);
52d980cb	581
7e77ca11	582	sub to_metric { $_[1] * .305 }
	583	sub to_imperial { $_[1] * 3.28 }
	584	# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
52d980cb	585	</div>
	586
	587	<div class="slide">
7e77ca11	588	<h1>ResultSetColumn</h1>
	589	<pre>my $rsc = $schema->resultset('Book')->get_column('price');
	590	$rsc->min;
	591	$rsc->max;
	592	$rsc->sum;
6548782a	593	</pre>
52d980cb	594	</div>
	595
	596	<div class="slide">
7e77ca11	597	<h1>Aggregates</h1>
	598	<pre>my @res = $rs->search({}, {
	599	select => [qw(price genre), { max => price }, { avg => price }],
	600	as => [qw(price genre max_price avg_price)],
	601	group_by => [qw(price genre)],
	602	});
	603	for (@res) {
	604	say $_->price . ' ' . $_->genre;
	605	say $_->get_column('max_price');
	606	say $_->get_column('min_price');
	607	}</pre>
	608	<ul class="incremental">
	609	<li>Careful, get_column can basicaly mean THREE things</li>
	610	<li>private for get what you should use an accessor for</li>
	611	<li>public for what there is no accessor for</li>
	612	<li>public for get resultset column (prev slide)</li>
	613	</ul>
52d980cb	614	</div>
	615
	616	<div class="slide">
7e77ca11	617	<h1>HRI</h1>
	618	<pre>$rs->search({}, {
	619	result_class => 'DBIx::Class::ResultClass::HashRefInflator',
52d980cb	620	});</pre>
7e77ca11	621	<ul class="incremental">
	622	<li>Easy on memory</li>
	623	<li>Mega fast</li>
	624	<li>Great for quick debugging</li>
	625	<li>Great for performance tuning (we went from 2m to < 3s)</li>
52d980cb	626	</ul>
	627	</div>
	628
	629	<div class="slide">
7e77ca11	630	<h1>Subquery Support</h1>
	631	<pre> my $inside_rs = $schema->resultset('Artist')->search({
	632	name => [ 'Billy Joel', 'Brittany Spears' ],
	633	});
9158dee5	634
7e77ca11	635	my $rs = $schema->resultset('CD')->search({
	636	artist_id => { -in => $inside_rs->get_column('id')->as_query },
	637	});</pre>
52d980cb	638	</div>
	639
	640	<div class="slide">
7e77ca11	641	<h1>Bare SQL w/ Placeholders</h1>
	642	<pre>$rs->update({
	643	price => \"price + $inc", # !!! SQL INJECTION VECTOR
	644	});
	645
	646	$rs->update({
	647	price => \['price + ?', [inc => $inc]],
	648	});
	649	</pre>
52d980cb	650	</div>
	651
	652	<div class="slide">
7e77ca11	653	<h1>Questions?</h1>
52d980cb	654	</div>
	655
	656	<div class="slide">
7e77ca11	657	<h1>END</h1>
52d980cb	658	</div>
52d980cb	659	</div>
	660	</body>
	661	</html>