Skip to content
Fetching contributors…
Cannot retrieve contributors at this time
1254 lines (1084 sloc) 40.7 KB
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js ie6" lang="en"> <![endif]-->
<!--[if IE 7]> <html class="no-js ie7" lang="en"> <![endif]-->
<!--[if IE 8]> <html class="no-js ie8" lang="en"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>DBIx::Class (aka DBIC)</title>
<!-- Core and extension CSS files -->
<link rel="stylesheet" href="deck.js/core/deck.core.css">
<link rel="stylesheet" href="deck.js/extensions/goto/deck.goto.css">
<link rel="stylesheet" href="deck.js/extensions/menu/deck.menu.css">
<link rel="stylesheet" href="deck.js/extensions/navigation/deck.navigation.css">
<link rel="stylesheet" href="deck.js/extensions/status/deck.status.css">
<link rel="stylesheet" href="deck.js/extensions/hash/deck.hash.css">
<link href="SyntaxHighlighter/styles/shCoreRDark.css" rel="stylesheet" type="text/css" />
<link href="SyntaxHighlighter/styles/shRDark.css" rel="stylesheet" type="text/css" />
<!-- Theme CSS files (menu swaps these out) -->
<link rel="stylesheet" id="style-theme-link" href="deck.js/themes/style/neon.css">
<script src="deck.js/modernizr.custom.js"></script>
</head>
<body class="deck-container">
<div class="slide">
<h1>DBIx::Class</h1>
</div>
<div class="slide">
<h2>Contact Info</h2>
<ul>
<li>IRC: irc.perl.org #dbix-class</li>
<li>ML: <a href="http://lists.scsys.co.uk/mailman/listinfo/dbix-class">http://lists.scsys.co.uk/mailman/listinfo/dbix-class</a></li>
<li><a href="https://metacpan.org/module/DBIx::Class">DBIx::Class</a></li>
</ul>
</div>
<div class="slide">
<h2>Authors</h2>
<h4>Arthur Axel "fREW" Schmidt</h4>
</div>
<div class="slide">
<h2>What's up guys?</h2>
<ul>
<li class="slide">How many people have used any ORM?</li><ul class="slide">
<li>In Perl?<ul>
<li>DBIC?</li>
<li>Class::DBI?</li>
<li>Rose::DB?</li>
<li>Fey::ORM?</li>
<li>Others?</li>
</ul></li>
<li>AR?</li>
<li>(N)Hibernate?</li>
</ul></li>
</ul>
</div>
<div class="slide">
<h2>Purpose</h2>
<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>
<ul class="slide">
<li>Note: links in slides are so you can find docs for what I'm talking about later</li>
</ul>
</div>
<div class="slide">
<h2>DBIx::Class?</h2>
<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 (my correct opinion)</li>
</ul>
</div>
<div class="slide">
<h2>Meta</h2>
<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">
<h2>Large Community</h2>
<p>Currently there are 118 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">
<h2>Active Community</h2>
<p>Upcoming release (0.08197?) has 8 new features, and 26 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">
<h2>Responsive Community</h2>
<ul>
<li class="slide">needed MSSQL order-by support, they helped me add support</li>
<li class="slide">generally very welcoming of people willing to help</li>
</ul>
</div>
<div class="slide">
<h2>General ORM</h2>
<p>These are things that are in most (but not all!) other ORMs, but are still reasons
to use DBIC over raw SQL.</p>
</div>
<div class="slide">
<h2>Cross DB</h2>
<p>The vast majority of code should run on all databases without needing tweaking</p>
</div>
<div class="slide">
<h2>Basic CRUD</h2>
<ul class="slide">
<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">
<h2>SQL: Create</h2>
<pre class="brush: perl">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">
<h2>DBIC: Create</h2>
<pre class="brush: perl">my $book = $book_rs-&gt;create({
title =&gt; 'A book title',
author_id =&gt; $author_id,
});</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#create">create</a></li>
<li class="slide">No need to pair placeholders and values</li>
<li class="slide">Automatically gets autoincremented id for you</li>
<li class="slide">Transparently uses INSERT ... RETURNING for databases that support it</li>
</ul>
</div>
<div class="slide">
<h2>SQL: Read</h2>
<pre class="brush: perl">my $sth = $dbh-&gt;prepare('
SELECT title,
authors.name as author_name
FROM books, authors
WHERE books.author = authors.id
');
while( my $book = $sth-&gt;fetchrow_hashref() ) {
print 'Author of '
. $book-&gt;{title}
. ' is '
. $book-&gt;{author_name}
. "\n";
}</pre>
</div>
<div class="slide">
<h2>DBIC: Read</h2>
<pre class="brush: perl">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;
while( my $book = $books_rs-&gt;next ) {
print 'Author of '
. $book-&gt;title
. ' is '
. $book-&gt;author-&gt;name
. "\n";
}
</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#find">find</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#search">search</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#next">next</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#all">all</a></li>
<li class="slide">TMTOWTDI</li>
</ul>
</div>
<div class="slide">
<h2>SQL: Update</h2>
<pre class="brush: perl">my $update = $dbh-&gt;prepare('
UPDATE books
SET title = ?
WHERE id = ?
');
$update-&gt;execute(
'New title',$book_id
);</pre>
</div>
<div class="slide">
<h2>DBIC: Update</h2>
<pre class="brush: perl">$book-&gt;update({
title =&gt; 'New title',
});</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Row#update">update</a></li>
<li class="slide">Won't update unless value changes</li>
</ul>
</div>
<div class="slide">
<h2>SQL: Delete</h2>
<pre class="brush: perl">my $delete = $dbh-&gt;prepare('
DELETE FROM books
WHERE id = ?
');
$delete-&gt;execute($book_id);</pre>
</div>
<div class="slide">
<h2>DBIC: Delete</h2>
<pre class="brush: perl">$book-&gt;delete;</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Row#delete">delete</a></li>
</ul>
</div>
<div class="slide">
<h2>SQL: Search</h2>
<pre class="brush: perl">my $sth = $dbh-&gt;prepare('
SELECT title,
authors.name as author_name
FROM books
WHERE books.name LIKE "%monte cristo%" AND
books.topic = "jailbreak"
');
</pre>
</div>
<div class="slide">
<h2>DBIC: Search</h2>
<pre class="brush: perl">
my $book = $book_rs-&gt;search({
'me.name' =&gt; { -like =&gt; '%monte cristo%' },
'me.topic' =&gt; 'jailbreak',
})-&gt;next;
</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#search">search</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#next">next</a></li>
<li class="slide"><a href="http://metacpan.org/module/SQL::Abstract">SQL::Abstract</a></li>
<li class="slide">(kinda) introspectible</li>
<li class="slide">Objectively prettier than SQL</li>
</ul>
</div>
<div class="slide">
<h2>OO Overridability</h2>
<ul>
<li class="slide">Override new if you want to do validation</li>
<li class="slide">Override delete if you want to disable deletion</li>
<li class="slide">and on and on</li>
<li class="slide">
<p>Note: we don't get <strong>everything</strong> from OO, but I it's still pretty great without <a href="http://en.wikipedia.org/wiki/Nosql">Giving Up</a></p>
</li>
</ul>
</div>
<div class="slide">
<h2>Convenience Methods</h2>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#find_or_create">find_or_create</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#update_or_create">update_or_create</a></li>
</ul>
</div>
<div class="slide">
<h2>Non-column methods</h2>
<p>Need a method to get a user's gravatar URL? Add a
gravatar_url method to their Result class</p>
<pre class="brush: perl">
use Gravatar::URL ();
sub gravatar_url {
Gravatar::URL::gravatar_url({ email =&gt; $_[0]-&gt;email })
}
</pre>
</div>
<div class="slide">
<h2>RELATIONSHIPS</h2>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship#belongs_to">belongs_to</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship#has_many">has_many</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship#might_have">might_have</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship#has_one">has_one</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship#many_to_many">many_to_many</a> (technically not a relationship)</li>
<li class="slide">SET AND FORGET</li>
</ul>
</div>
<div class="slide">
<h2><a href="https://metacpan.org/module/DBIx::Class::Relationship::Base#SYNOPSIS">EXTENDED RELATIONSHIPS</a></h2>
<pre class="brush: perl"> __PACKAGE__-&gt;belongs_to(
output_device =&gt; 'My::Schema::Result::OutputDevice',
sub {
my $args = shift;
my $other = $args-&gt;{foreign_alias};
my $me = $args-&gt;{self_alias};
my $self = $args-&gt;{self_rowobj};
return ({
"$other.user" =&gt; { -ident =&gt; "$me.user" },
"$other.shared" =&gt; 1,
},
$self &amp;&amp; {
"$other.user" =&gt; $self-&gt;user,
"$other.shared" =&gt; 1,
});
},
);</pre>
</div>
<div class="slide">
<h2>-&gt;deploy</h2>
<p>Perl -&gt; DB</p>
<pre class="brush: perl">my $schema = Foo::Schema-&gt;connect(
$dsn, $user, $pass
);
$schema-&gt;deploy
</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#deploy">deploy</a></li>
<li class="slide">See also: <a href="http://metacpan.org/module/DBIx::Class::DeploymentHandler">DBIx::Class::DeploymentHandler</a>
</ul>
</div>
<div class="slide">
<h2>Schema::Loader</h2>
<p>DB -&gt; Perl</p>
<pre class="brush: perl">package Foo::Schema;
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>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema::Loader">DBIx::Class::Schema::Loader</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema::Loader::Base#CONSTRUCTOR_OPTIONS">loader_options</a></li>
</div>
<div class="slide">
<h2>Populate</h2>
<p>Made for inserting lots of rows very quicky into database</p>
<pre class="brush: perl">$schema-&gt;populate([ Users =&gt;
[qw( username password )],
[qw( frew &gt;=4char$ )],
[qw( ... )],
[qw( ... )],
);
</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#populate">populate</a></li>
<li class="slide">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">
<h2>Multicreate</h2>
<p>Create an object and all of it's related objects all at once</p>
<pre class="brush: perl">$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>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#create">create</a></li>
<li class="slide">books is a has_many</li>
<li class="slide">address is a belongs_to which in turn belongs to state and city each</li>
<li class="slide">for this to work right state and city must mark abbreviation and name as unique</li>
</ul>
</div>
<div class="slide">
<h2>Extensible</h2>
<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. (more slides about components later)</p>
</div>
<div class="slide">
<h2>Result vs ResultSet</h2>
<ul>
<li class="slide">Result == Row</li>
<li class="slide">ResultSet == Query Plan<ul>
<li class="slide">Internal Join Optimizer for all DB's (!!!)</li>
</ul></li>
<li class="slide">(less important but...)</li>
<li class="slide">ResultSource == Table</li>
<li class="slide">Storage == Database</li>
</ul>
</div>
<div class="slide">
<h2><a href="http://metacpan.org/module/DBIx::Class::Manual::Cookbook#Predefined_searches">ResultSet methods</a></h2>
<pre class="brush: perl">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}
})
}
</pre>
</div>
<div class="slide">
<h2>ResultSet method notes</h2>
<ul>
<li class="slide">All searches should be ResultSet methods</li>
<li class="slide">Name has obvious meaning</li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#current_source_alias">current_source_alias</a> helps things to work no matter what</li>
</ul>
</div>
<div class="slide">
<h2>ResultSet method in Action</h2>
<pre class="brush: perl">$schema-&gt;resultset('Book')-&gt;good</pre>
</div>
<div class="slide">
<h2>ResultSet Chaining</h2>
<pre class="brush: perl">$schema-&gt;resultset('Book')
-&gt;good
-&gt;cheap
-&gt;recent
</pre>
</div>
<div class="slide">
<h2>search_related</h2>
<pre class="brush: perl">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>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#related_resultset">related_resultset</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#search_related">search_related</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#get_column">get_column</a></li>
</div>
<div class="slide">
<h2>bonus rel methods</h2>
<pre class="brush: perl">my $book = $author-&gt;create_related(
books =&gt; {
title =&gt; 'Another Discworld book',
}
);
my $book2 = $pratchett-&gt;add_to_books({
title =&gt; 'MOAR Discworld book',
});</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship::Base#create_related">create_related</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Relationship::Base#add_to_$rel">add_to_<strong>books</strong></a></li>
<li class="slide">Automaticaly fills in foreign key for you</li>
</ul>
</div>
<div class="slide">
<h2>Excellent Transaction Support</h2>
<pre class="brush: perl">$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>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#txn_do">txn_do</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#txn_scope_guard">txn_scope_guard</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#txn_begin">txn_begin</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::Schema#txn_commit">txn_commit</a></li>
</ul>
</div>
<div class="slide">
<h2>InflateColumn</h2>
<pre class="brush: perl">package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
use DateTime::Format::MySQL;
# Result code here
__PACKAGE__-&gt;load_components('InflateColumn');
__PACKAGE__-&gt;inflate_column(
date_published =&gt; {
inflate =&gt; sub {
DateTime::Format::MySQL-&gt;parse_date(
shift
)
},
deflate =&gt; sub { shift-&gt;ymd },
},
);
# Automatic see: DBIC::InflateColumn::DateTime</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::InflateColumn">InflateColumn</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::InflateColumn#inflate_column">inflate_column</a></li>
</ul>
</div>
<div class="slide">
<h2>InflateColumn: deflation</h2>
<pre class="brush: perl">$book-&gt;date_published(DateTime-&gt;now);
$book-&gt;update;</pre>
</div>
<div class="slide">
<h2>InflateColumn: inflation</h2>
<pre class="brush: perl">say $book-&gt;date_published-&gt;month_abbr;</pre>
<strong><em>Nov</em></strong>
</div>
<div class="slide">
<h2>FilterColumn</h2>
<pre class="brush: perl">package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
# Result code here
__PACKAGE__-&gt;load_components('FilterColumn');
__PACKAGE__-&gt;filter_column(
length =&gt; {
to_storage =&gt; 'to_metric',
from_storage =&gt; 'to_imperial',
},
);
sub to_metric { $_[1] * .305 }
sub to_imperial { $_[1] * 3.28 }
</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::FilterColumn">FilterColumn</a></li>
<li class="slide"><strong><a href="http://metacpan.org/module/DBIx::Class::FilterColumn#filter_column">filter_column</a></strong></li>
</ul>
</div>
<div class="slide">
<h2><a href="http://metacpan.org/module/DBIx::Class::ResultSetColumn">ResultSetColumn</a></h2>
<pre class="brush: perl">my $rsc = $schema-&gt;resultset('Book')
-&gt;get_column('price');
$rsc-&gt;first;
$rsc-&gt;all;
$rsc-&gt;min;
$rsc-&gt;max;
$rsc-&gt;sum;
</pre>
</div>
<div class="slide">
<h2>Aggregates</h2>
<pre class="brush: perl">my @res = $rs-&gt;search({}, {
columns =&gt; [
'price',
'genre',
{ max_price =&gt; { max =&gt; price } },
{ avg_price =&gt; { avg =&gt; 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>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#columns">columns</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#group_by">group_by</a></li>
</ul>
</div>
<div class="slide">
<h2>Aggregates Notes</h2>
<ul>
<li class="slide">Careful, get_column can basicaly mean THREE things</li>
<li class="slide">private for get what you should use an accessor for</li>
<li class="slide">public for what there is no accessor for</li>
<li class="slide">public for get resultset column (prev slide)</li>
</ul>
</div>
<div class="slide">
<h2>HRI</h2>
<pre class="brush: perl">$rs-&gt;search({}, {
result_class =&gt;
'DBIx::Class::ResultClass::HashRefInflator',
});</pre>
<ul>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultSet#result_class">result_class</a></li>
<li class="slide"><a href="http://metacpan.org/module/DBIx::Class::ResultClass::HashRefInflator">DBIx::Class::ResultClass::HashRefInflator</a></li>
<li class="slide">Easy on memory</li>
<li class="slide">Mega fast</li>
<li class="slide">Great for quick debugging</li>
<li class="slide">Great for performance tuning (we went from 2m to &lt; 3s)</li>
</ul>
</div>
<div class="slide">
<h2><a href="http://metacpan.org/module/DBIx::Class::Manual::Cookbook#Subqueries">Subquery</a> Support</h2>
<pre class="brush: perl">my $inside_query = $schema-&gt;resultset('Artist')
-&gt;search({
name =&gt; [ 'Billy Joel', 'Iron Maiden' ],
})-&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">
<h2><a href="http://metacpan.org/module/SQL::Abstract#Literal_SQL_with_placeholders_and_bind_values_(subqueries)">Bare SQL w/ Placeholders</a></h2>
<pre class="brush: perl">$rs-&gt;update({
# !!! SQL INJECTION VECTOR
price =&gt; \"price + $inc",
});
$rs-&gt;update({
price =&gt; \['price + ?', [{} =&gt; $inc]],
});
</pre>
</div>
<div class="slide">
<h1>Advanced</h1>
</div>
<div class="slide">
<h2>Random example - SQL metaprogramming</h2>
<pre style="font-size:80%">
$shadow_rs->search({ shadow_val_title => 'stab' })->insert({
shadow_stage => 2,
shadow_timestamp => 12345678,
shadowed_lifecycle => \"(
SELECT COALESCE( MAX( shadowed_lifecycle ), 0 ) + 1 FROM $shadow_table
)",
rel_shadow_artists_lifecycle =>
$shadowed_cd->search_related('artist')
->search_related('shadows', {}, { rows => 1 })
->get_column('shadowed_lifecycle')
->as_query
,
});</pre>
</div>
<div class="slide">
<pre style="font-size:60%">
INSERT
INTO shadow_cds (
shadow_stage,
shadow_timestamp,
shadow_val_title,
shadow_lifecycle,
rel_shadw_artists_lifecycle
) VALUE (
'2',
'12345678',
'stab',
'2',
(
SELECT COALESCE( MAX( shadowed_lifecycle ), 0 ) + 1
FROM shadow_cds
),
(
SELECT shadows.shadowed_lifecycle
FROM artists me
JOIN shadow_artists shadows
ON shadows.shadowed_curpk_name = me.name
WHERE me.name = 'gaga'
LIMIT '1'
),
)
</div>
<div class="slide">
<h2>Loosely coupled abstractions</h2><ul>
<li> Query definition/manipulation framework (DBIx::Class::ResultSet)
<li> Storage I/O abstraction layer (DBIx::Class::Storage(::DBI ))
<li> Data-Source/Relationship Model abstraction framework (DBIx::Class::Schema/DBIx::Class::ResultSource)
<li> Retrieved result parser (DBIx::Class::Row / DBIx::Class::ResultClass::HashRefInflator / &lt;your own class&gt;)
</div>
<div class="slide">
<h2>Loosely coupled abstractions</h2>
<p><pre style="font-size:70%">
my $schema_instance # knows about its registered sources (load_namespaces,
= Schema::Class-&gt;connect(...); # load_classes, compose_namespace, etc), and about its
# storage instance (the storage IO abstraction)
my $result_source_instance
= $schema_instance-&gt;source('Artist') # knows how to refer to the actual data source
# when talking to storage, knows the source
# metadata, knows the default result inflation class
my $resultset_instance # query plan, holds a reference to the source and infers
= $result_sources_instance; # from it the storage-source name, and the default selection
( or $schema-&gt;resultset('Artist') # and/or restrictions from source instance metadata
# ALL OVERRIDABLE
my $row_object
= $resultset_instance-&gt;next # actual query execution, raw result is processed and returned
# according to the $resultset_instance settings
# CAN BE ANYTHING
</pre>
</div>
</div><div class="slide">
<h2>Query definition/manipulation framework (DBIx::Class::ResultSet)</h2><ul>
<li> Query plan (geared to a specific source)
<li> Immutable
<li> Able to express/override all query primitives
<li> Holds returned selection <i>parsing</i> metadata:
<pre class="brush: perl">
result_class
inflation-specification (as)
</pre>
<li> Inherits defaults from referenced Result Source (if any)
<li><b>EVERYTHING IS OVERRIDABLE</b>
</div>
<div class="slide">
<h2>Overridable/augmentable default selection</h2><ul>
<pre style="font-size:65%">
$rs-&gt;search({}, { '+columns' =&gt; ['foo', { max_bar =&gt; \'MAX(bar)' }] });
$rs-&gt;search({}, { select =&gt; 'multi_value_func', as =&gt; [qw(val1 val2)] });
</pre>
<li> Currently does not create accessors on the fly
</div><div class="slide">
<h2 style="font-size:81%">Overridable/augmentable default selection: AS IS NOT AS</h2><ul>
<li>Represents an inflation plan
<pre style="font-size:65%">
$artist_rs-&gt;search({}, { prefetch =&gt; { cds =&gt; 'tracks' } });
===
$artist_rs-&gt;search({}, {
join =&gt; { cds =&gt; 'tracks' },
'+columns' =&gt; {
'cds.title' =&gt; 'cds.title',
'cds.year' =&gt; 'cds.year',
'cds.tracks.title' =&gt; 'tracks.title',
'cds.tracks.pos' =&gt; 'tracks.pos',
},
&lt;some collapser magic that I can not yet sanely expose>
(in the works, promise!)
});
</pre>
<li>A source of limitless confusion
<li>columns/+columns design unfortunate
</div><div class="slide">
<h2>ResultSet operations (CRUD)</h2><ul style="font-size:90%">
<li> No-result CREATE/UPDATE/RETRIEVE
<pre class="brush: perl">
$rs-&gt;populate
$rs-&gt;update
$rs-&gt;delete
</pre>
<li> Low level RETRIEVE ($sth-&gt;selectall_arrayref/fetchrow_array)<br>
(epecially when you want to limit DBIC to a super-smart SQL generator)
<pre class="brush: perl">
$rs-&gt;cursor-&gt;next/all
</pre>
<li> Roundtrip Result-inflating CREATE/RETRIEVE
<pre class="brush: perl">
$rs-&gt;create
$rs-&gt;next/all
</pre>
<li> Inflation (what happens on -&gt;next/-&gt;all)
<pre class="brush: perl">
$rs-&gt;result_class-&gt;inflate_result(
$rs-&gt;cursor-&gt;next
+ as-specification from $rs
)
</pre>
</div>
<div class="slide">
<h2> Storage I/O abstraction layer (DBIx::Class::Storage(::DBI ))</h2><ul>
<li> Holds $dbh (almost like DBIx::Connector)
<li> Fork/thread and stale connection handling
<li> Transaction/savepoint handling
<li> Invokes correct DBIx::Class::Storage::SQLMaker subclass (sql dialects)
<li> Buggy DBD workarounds (over 8000 loc)
<li> Could be shared between multiple $schema's
<li> Much more on the abstraction front
</div>
<div class="slide">
<h2>DBIx::Class::Core / DBIx::Class::Row</h2><ul>
<li> Data accessors
<li> Relationship Accessors
<li> Hooks to ResultSource/Schema/ResultSet instances
<li> Handy corresponding row manipulation methods
<li> Plugins (components/roles/whateveryouwannacallit)
</div>
<div class="slide">
<h2>Write your own result class</h2><ul>
<li> DBIC layers have *NO EXPECTATIONS* of result format
</div>
<div class="slide">
<h2>The power of Extended Relationships</h2>
<p>Just a coderef, so you can...</p>
<ul>
<li class="slide">warn, to create deprecated rels</li>
<li class="slide">use dynamic (local) scope to have ghetto parameterized rels</li>
<li class="slide">anything else</li>
</ul>
</div>
<div class="slide">
<h2>DBIx::Class Components</h2>
<h4>NOT ALL ME, LOTS OF AUTHORS</h4>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Candy">::Candy</a></h2>
<p>Use this to avoid carpel tunnel (no more __PACKAGE__)</p>
<pre class="brush: perl">
package Lynx::SMS::Schema::Result::Price;
use Lynx::SMS::Schema::Candy; # special subclass
primary_column id =&gt; {
data_type =&gt; 'int',
is_auto_increment =&gt; 1,
};
column part_code =&gt; {
data_type =&gt; 'varchar',
size =&gt; 15,
};
column description =&gt; {
data_type =&gt; 'text',
is_serializable =&gt; 1,
};
column type_id =&gt; { data_type =&gt; 'int' };
column credits_per_year =&gt; { data_type =&gt; 'int' };
column list_price =&gt; { data_type =&gt; 'money' };
column gsa_price =&gt; { data_type =&gt; 'money' };
1;
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::TimeStamp">::TimeStamp</a></h2>
<p>USE THIS for any kind of timestamp (set on create or set on update)</p>
<p>Caveat: any bugs in DateTime are also in this</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::EncodedColumn">::EncodedColumn</a></h2>
<p>USE THIS for password columns</p>
<pre class="brush: perl">
use DBIx::Class::Candy -components =&gt; ['EncodedColumn'];
column password =&gt; {
is_serializable =&gt; 0,
data_type =&gt; 'CHAR',
size =&gt; 59,
encode_column =&gt; 1,
encode_class =&gt; 'Crypt::Eksblowfish::Bcrypt',
encode_args =&gt; { key_nul =&gt; 0, cost =&gt; 8 },
encode_check_method =&gt; 'check_password',
};
</pre>
<p>Later..</p>
<pre class="brush: perl">die "liar!" unless $user-&gt;check_password($input)</pre>
</div>
<div class="slide">
<h2>Detour: Context--</h2>
<pre class="brush: perl">my @foo = $rs-&gt;search(...)</pre>
<p>vs</p>
<pre class="brush: perl">my $foo = $rs-&gt;search(...)</pre>
<p>or more importantly...</p>
<pre class="brush: perl">$foo-&gt;bar($rs-&gt;search(...))</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::IgnoreWantarray">::Helper::ResultSet::IgnoreWantarray</a></h2>
<p>USE THIS for less surprising resultsets (must call -&gt;all)</p>
<pre class="brush: perl">
print_rs($rs-&gt;search(...)); # &lt;3
print_row($_) for $rs-&gt;search(...)-&gt;all; # &lt;3
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::OnColumnChange">::Helper::Row::OnColumnChange</a></h2>
<p>USE THIS to do something when a value changes (per column)</p>
<pre class="brush: perl">
use DBIx::Class::Candy -components =&gt; [
'Helper::Row::OnColumnChange'
];
after_column_change kind_of_id =&gt; {
txn_wrap =&gt; 1,
method =&gt; '_fix_for_updates',
};
sub _fix_for_updates {
my ( $self, $old, $new ) = @_;
$self-&gt;_set_materialized_path;
$_-&gt;_fix_for_updates for $self-&gt;direct_kinds-&gt;all
}
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::SetOperations">::Helper::ResultSet::SetOperations</a></h2>
<p>USE THIS to use UNION and other set ops with DBIC</p>
(excuse this insane example)
<pre class="brush: perl">
sub succeeded_computers {
my $self = shift;
my $c_links = $self-&gt;test_computer_links;
my $d_links = $self-&gt;test_device_links;
my $failed_ids =
$c_links-&gt;failed-&gt;related_resultset('computer')-&gt;union(
$c_links-&gt;untested-&gt;related_resultset('computer'),
)-&gt;union_with_devices(
$d_links-&gt;failed-&gt;related_resultset('device')-&gt;union(
$d_links-&gt;untested-&gt;related_resultset('device'),
)
)-&gt;get_column('id')-&gt;as_query;
$self-&gt;result_source-&gt;schema-&gt;resultset('Computer')-&gt;search({
id =&gt; {
-not_in =&gt; $failed_ids,
-in =&gt; $c_links-&gt;related_resultset('computer')-&gt;union_with_devices(
$d_links-&gt;related_resultset('device')
)-&gt;get_column('id')-&gt;as_query
}
});
}
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::RelationshipDWIM">::Helper::Row::RelationshipDWIM</a></h2>
<p>USE THIS to avoid carpel tunnel (has_many cars =&gt; 'Cars', ...)</p>
<pre class="brush: perl">
use DBIx::Class::Candy -components =&gt; [
'Helper::Row::RelationshipDWIM'
];
belongs_to type =&gt; '::Type', 'type_id';
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::StorageValues">::Helper::Row::StorageValues</a></h2>
<p>USE THIS to keep track of what's in the DB before you call update and after you use an accessor</p>
<pre class="brush: perl">
use DBIx::Class::Candy -components =&gt; [ 'Helper::Row::StorageValues' ];
# use OnColumnChange for this, just an example (has bug)
sub update {
my $self = shift;
log_trace {
"name changing from " . $self-&gt;storage_value('name') .
" to " . $self-&gt;name
};
$self-&gt;next::method(@_);
}
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::SubClass">::Helper::Row::SubClass</a></h2>
<p>USE THIS to subclass parts of your schema (deprecated by new versions of Class::C3::Componentised)</p>
<p>automatically makes sure to set the same table and tries it's best to copy rels</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::ToJSON">::Helper::Row::ToJSON</a></h2>
<p>USE THIS to generate a sensible TO_JSON method</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::CorrelateRelationship">::Helper::ResultSet::CorrelateRelationship</a></h2>
<p>USE THIS for getting aggregates (and other things)</p>
<p>see later slide for in depth example</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::SearchOr">::Helper::ResultSet::SearchOr</a></h2>
<p>USE THIS to reuse even more resultset methods</p>
<ul>
<li class="slide">search is "additive" (ANDs)</li>
<li class="slide">search_or is safely not additive</li>
<li class="slide">
<pre class="brush: perl">
$artists_rs-&gt;touring-&gt;has_record_label
</pre>
</li>
<li class="slide">
vs.
<pre class="brush: perl">
$artists_rs-&gt;search_or([
$artist_rs-&gt;touring
$artist_rs-&gt;has_record_label
])
</pre>
</li>
<li class="slide">
vs.
<pre class="brush: perl">
$artists_rs-&gt;touring-&gt;union([
$artist_rs-&gt;has_record_label
])
</pre>
</li>
</ul>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Schema::LintContents">::Helper::Schema::LintContents</a></h2>
<p>USE THIS to check if the contents of your database are in good shape</p>
<ul>
<li class="slide">Can check for duplicate PKs and UCs</li>
<li class="slide">Can check for broken FKs</li>
</ul>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::ResultClassDWIM">::Helper::ResultSet::ResultClassDWIM</a></h2>
<p>USE THIS to avoid carpel tunnel ( result_class =&gt; '::HRI' )</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::RemoveColumns">::Helper::ResultSet::RemoveColumns</a></h2>
<p>Use this to remove columns from selection in a succicnt fashion</p>
<pre class="brush: perl">
$rs-&gt;search(undef, {
remove_columns =&gt; ['password'],
})
</pre>
vs
<pre class="brush: perl">
$rs-&gt;search(undef, {
columns =&gt; [qw(id username email fullname ... )],
})
</pre>
<p>Not just less to type, also more robust</p>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::ResultSet::Random">::Helper::ResultSet::Random</a></h2>
<p>Use this to grab random rows from a resultset</p>
<pre class="brush: perl">
my $rand = $rs-&gt;rand(1)-&gt;next;
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::Helper::Row::JoinTable">::Helper::Row::JoinTable</a></h2>
<p>Use this to reduce boilerplate involved in creating join tables</p>
<pre class="brush: perl">
package MyApp::Schema::Result::Foo_Bar;
use DBIx::Class::Candy -components => ['Helper::Row::JoinTable'];
join_table {
left_class => 'Foo',
left_method => 'foo',
right_class => 'Bar',
right_method => 'bar',
};
</pre>
</div>
<div class="slide">
<h2><a href="http://beta.metacpan.org/module/DBIx::Class::DeploymentHandler">::DeploymentHandler</a></h2>
<ul>
<li class="slide">Generate DDL for migrations automatically with SQLT</li>
<li class="slide">Run Perl and raw SQL for migrations safely (with txns, durable(S::L), etc)</li>
<li class="slide">show example!</li>
</ul>
</div>
<div class="slide">
<h2>Useful ResultSet Methods</h2>
<ol>
<li class=slide>
<pre class="brush: perl">
sub with_all_computers_count {
my $self = shift;
$self-&gt;search(undef, {
'+columns' =&gt; {
all_computers_count =&gt;
$self-&gt;correlate('computers')-&gt;count_rs-&gt;as_query
},
})
}
</pre>
</li>
<li class=slide> <a href="http://metacpan.org/module/DBIx::Class::Helper::Row::ProxyResultSetMethod">::Helper::Row::ProxyResultSetMethod</a>
<pre class="brush: perl">
package MyApp::Schema::Result::Test;
use DBIx::Class::Candy -components => [
'Helper::Row::ProxyResultSetMethod',
];
proxy_resultset_method =&gt; 'all_computers_count';
</pre>
</li>
</ol>
</div>
<div class="slide">
<h1>Questions?</h1>
</div>
<div class="slide">
<h1>END</h1>
</div>
<a href="#" class="deck-prev-link" title="Previous">&#8592;</a>
<a href="#" class="deck-next-link" title="Next">&#8594;</a>
<p class="deck-status">
<span class="deck-status-current"></span>
/
<span class="deck-status-total"></span>
</p>
<form action="." method="get" class="goto-form">
<label for="goto-slide">Go to slide:</label>
<input type="number" name="slidenum" id="goto-slide">
<input type="submit" value="Go">
</form>
<a href="." title="Permalink to this slide" class="deck-permalink">#</a>
<script src="deck.js/jquery-1.7.min.js"></script>
<!-- Deck Core and extensions -->
<script src="deck.js/core/deck.core.js"></script>
<script src="deck.js/extensions/menu/deck.menu.js"></script>
<script src="deck.js/extensions/goto/deck.goto.js"></script>
<script src="deck.js/extensions/status/deck.status.js"></script>
<script src="deck.js/extensions/navigation/deck.navigation.js"></script>
<script src="deck.js/extensions/hash/deck.hash.js"></script>
<script type="text/javascript" src="SyntaxHighlighter/scripts/XRegExp.js"></script>
<script type="text/javascript" src="SyntaxHighlighter/scripts/shCore.js"></script>
<script type="text/javascript" src="SyntaxHighlighter/scripts/shBrushPerl.js"></script>
<!-- Specific to this page -->
<script src="index.js"></script>
</body>
</html>
Jump to Line
Something went wrong with that request. Please try again.