</div>
<div class="slide">
+ <h1>Contact Info</h1>
+ <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="http://search.cpan.org/perldoc?DBIx::Class">DBIx::Class</a> yo</li>
+ </ul>
+ </div>
+
+ <div class="slide">
<h1>Authors</h1>
<h4>Originally Leo Lapworth @ LPW 2009</h4>
- <h4>Matthew S. Trout</h4>
+ <h4>Amiri Barksdale</h4>
<h4>Justin D. Hunter</h4>
<h4>Arthur Axel "fREW" Schmidt</h4>
</div>
<div class="slide">
- <h1>assumptions</h1>
- <p>You know a little about Perl and using objects</p>
- <p>You know a little bit about databases and using foreign keys</p>
- <div class="notes">
- <ul>
- <li>How many people have designed a database with Foreign Keys?</li>
- <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>
+ <h1>What's up guys?</h1>
+ <ul class="incremental">
+ <li>How many people have used any ORM?</li><ul class="incremental">
+ <li>In Perl?<ul class="incremental">
+ <li>DBIC?</li>
+ <li>Class::DBI?</li>
+ <li>Rose::DB?</li>
+ <li>Fey::ORM?</li>
+ <li>Others?</li>
</ul></li>
- </ul>
- </div>
- </div>
-
- <div class="slide">
- <h1>DBIx::Class?</h1>
- <ul>
- <li>ORM (object relational mapper)</li>
- <li>SQL <-> OO (using objects instead of SQL)</li>
- <li>Simple, powerful, complex, fab and confusing</li>
- <li>There are many ORMs, DBIx::Class just happens to be the best in Perl (personal opinion)</li>
+ <li>AR?</li>
+ <li>(N)Hibernate?</li>
+ </ul></li>
</ul>
</div>
<div class="slide">
- <h1>point of note</h1>
- <p><em>"Debugging is twice as hard as writing the code in the first
- place. Therefore, if you write the code as cleverly as possible,
- you are, by definition, not smart enough to debug it." - Brian
- W. Kernighan</em></p>
-
- <p>This talk is about making it easy so we are less likely to get
- confused</p>
+ <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>
+ <ul class="incremental">
+ <li>Note: links in slides are so you can find docs for what I'm talking about later</li>
+ </ul>
</div>
<div class="slide">
- <h1>Examples Table Setup</h1>
+ <h1>DBIx::Class?</h1>
<ul>
- <li>Authors</li>
- <li>Books</li>
+ <li>ORM (object relational mapper)</li>
+ <li>SQL <-> 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>
- <em>MySQL not recommended</em>
</div>
<div class="slide">
- <h1>authors table</h1>
-<pre>CREATE TABLE authors(
- id int(8) primary key auto_increment,
- name varchar(255)
-) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
+ <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>tips</h1>
- <p>Name tables as simple plurals (<strong>add an S</strong>) - makes relationships easier to understand</p>
- <p>Use a character set (<strong>UTF8</strong>) from the start (for international characters)</p>
+ <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>authors table</h1>
-<pre>CREATE TABLE author<strong>s</strong>(
- id int(8) primary key auto_increment,
- name varchar(255)
-) engine = <strong>InnoDB</strong> DEFAULT CHARSET=<strong>utf8</strong>;</pre>
+ <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>books table</h1>
-<pre>CREATE TABLE books(
- id int(8) primary key auto_increment,
- title varchar(255),
- author_id int(8),foreign key (author)
- references authors(id)
-) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
+ <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>tips</h1>
- <p>Name link fields as singular</p>
- <p>Ensure foreign key is the same type and size in both tables</p>
+ <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>books table</h1>
-<pre>CREATE TABLE books(
- id int(8) primary key auto_increment,
- title varchar(255),
- author_id <strong>int(8)</strong>,<strong>foreign key (<em>author</em>)</strong>
- <strong>references <em>authors(id)</em></strong>
-) engine = InnoDB DEFAULT CHARSET=utf8;</pre>
+ <h1>Cross DB</h1>
+ <p>The vast majority of code should run on all databases without needing tweaking</p>
</div>
<div class="slide">
- <h1>CRUD compared</h1>
- <ul>
+ <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>
</div>
<div class="slide">
- <h1>Manual (SQL)</h1>
- </div>
-
- <div class="slide">
<h1>SQL: Create</h1>
<pre>my $sth = $dbh->prepare('
INSERT INTO books
- (title, author)
+ (title, author_id)
values (?,?)
');
</div>
<div class="slide">
+ <h1>DBIC: Create</h1>
+<pre>my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#create">create</a>({
+ title => 'A book title',
+ author_id => $author_id,
+});</pre>
+ <ul class="incremental">
+ <li>No need to pair placeholders and values</li>
+ <li>Automatically gets autoincremented id for you</li>
+ <li>Transparently uses INSERT ... RETURNING for databases that support it</li>
+ </ul>
+ </div>
+
+ <div class="slide">
<h1>SQL: Read</h1>
<pre>my $sth = $dbh->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->fetchrow_hashref() ) {
+while( my $book = $sth->fetchrow_hashref() ) {
print 'Author of '
. $book->{title}
. ' is '
</div>
<div class="slide">
+ <h1>DBIC: Read</h1>
+<pre>my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#find">find</a>($book_id);
+
+my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search">search</a>({
+ title => 'A book title',
+}, { rows => 1 })-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#next">next</a>;
+
+my @books = $book_rs->search({
+ author => $author_id,
+})-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#all">all</a>;
+
+while( my $book = $books_rs->next ) {
+ print 'Author of '
+ . $book->title
+ . ' is '
+ . $book->author->name
+ . "\n";
+}
+</pre>
+ <ul class="incremental">
+ <li>TMTOWTDI</li>
+ </ul>
+ </div>
+
+ <div class="slide">
<h1>SQL: Update</h1>
<pre>my $update = $dbh->prepare('
UPDATE books
</div>
<div class="slide">
+ <h1>DBIC: Update</h1>
+<pre>$book-><a href="http://search.cpan.org/perldoc?DBIx::Class::Row#update">update</a>({
+ title => 'New title',
+});</pre>
+ <ul class="incremental">
+ <li>Won't update unless value changes</li>
+ </ul>
+ </div>
+
+ <div class="slide">
<h1>SQL: Delete</h1>
<pre>my $delete = $dbh->prepare('
DELETE FROM books
</div>
<div class="slide">
- <h1>DBIx::Class</h1>
- </div>
-
- <div class="slide">
- <h1>DBIC: Create</h1>
-<pre>my $book = $book_model->create({
- title => 'A book title',
- author => $author_id,
-});</pre>
- <p>Look ma, no SQL!</p>
- </div>
-
- <div class="slide">
- <h1>DBIC: Create</h1>
-<pre>my $pratchett = $author_model->create({
- name => 'Terry Pratchett',
-});</pre>
- </div>
-
- <div class="slide">
- <h1>DBIC: Create</h1>
-<pre>my $book = $pratchett->create_related(
- <strong>books</strong> => {
- title => 'Another Discworld book',
-});</pre>
-<strong>or</strong>
-<pre>my $book = $pratchett->add_to_<strong>books</strong>({
- title => 'Another Discworld book',
-});</pre>
- </div>
-
- <div class="slide">
- <h1>DBIC: Read</h1>
- <p>DBIx::Class - Lots of ways to do the same thing...</p>
- <p><em>"There is more than one way to do it (TIMTOWTDI, usually pronounced "Tim Toady") is a Perl motto"</em></p>
+ <h1>DBIC: Delete</h1>
+<pre>$book-><a href="http://search.cpan.org/perldoc?DBIx::Class::Row#delete">delete</a>;</pre>
</div>
<div class="slide">
- <h1>DBIC: Read</h1>
-<pre>my $book = $book_model->find($book_id);
-
-my $book = $book_model->search({
- title => 'A book title',
-}, { rows => 1 })->single;
-
-my @books = $book_model->search({
- author => $author_id,
-})->all;</pre>
+ <h1>SQL: Search</h1>
+<pre>my $sth = $dbh->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">
- <h1>DBIC: Read</h1>
-<pre>while( my $book = $books_rs->next ) {
- print 'Author of '
- . $book->title
- . ' is '
- . $book->author->name
- . "\n";
-}</pre>
+ <h1>DBIC: Search</h1>
+<pre>
+my $book = $book_rs-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search">search</a>({
+ 'me.name' => { -like => '%monte cristo%' },
+ 'me.topic' => 'jailbreak',
+})-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#next">next</a>;
+</pre>
+ <ul class="incremental">
+ <li><a href="http://search.cpan.org/perldoc?SQL::Abstract">SQL::Abstract</a></li>
+ <li>(kinda) introspectible</li>
+ <li>Prettier than SQL</li>
+ </ul>
</div>
<div class="slide">
- <h1>DBIC: Read</h1>
-<pre>my $books_rs = $book_rs->search({
- author => $author_id,
-});</pre>
- <p>Search takes SQL::Abstract formatted queries</p>
- <pre>> perldoc SQL::Abstract</p>
+ <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>DBIC: Update</h1>
-<pre>$book->update({
- title => 'New title',
-});</pre>
+ <h1>Convenience Methods</h1>
+ <ul class="incremental">
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#find_or_create">find_or_create</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#update_or_create">update_or_create</a></li>
+ </ul>
</div>
<div class="slide">
- <h1>DBIC: Delete</h1>
-<pre>$book->delete;</pre>
+ <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>Creating models</h1>
+ <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">
-<pre>package Foo::Schema::Result::Author;
-use strict; use warnings;
-__PACKAGE__->table('authors');
-__PACKAGE__->add_columns(
- id => {
- data_type => 'int',
- size => 8,
- },
- title => {
- data_type => 'varchar',
- is_nullable => 1,
- size => 255,
- },
-);
-__PACKAGE__->set_primary_key('id');
-__PACKAGE__->has_many( books =>
- 'Foo::Schema::Result::Book', 'author_id'
-);
-1;
-</pre>
+ <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">
-<pre>package Foo::Schema::Result::Book;
-use strict; use warnings;
-__PACKAGE__->table('books');
-__PACKAGE__->add_columns(
- id => {
- data_type => 'int',
- size => 8,
- },
- name => {
- data_type => 'varchar',
- is_nullable => 1,
- size => 255,
- },
- author_id => {
- data_type => 'int',
- size => 8,
- is_nullable => 1, # <-- probably should be 0
- },
-);
-__PACKAGE__->set_primary_key('id');
-__PACKAGE__->belongs_to( author =>
- 'Foo::Schema::Result::Author', 'author_id'
+ <h1>->deploy</h1>
+ <p>Perl -> DB</p>
+<pre>my $schema = Foo::Schema->connect(
+ $dsn, $user, $pass
);
-1;
+$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#deploy">deploy</a>
</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 -> Perl vs Perl -> DB</p>
+ <p>DB -> Perl</p>
<pre>package Foo::Schema;
use strict; use warnings;
-use base 'DBIx::Class::Schema::Loader';
-__PACKAGE__->loader_options({
+use base '<a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::Loader">DBIx::Class::Schema::Loader</a>';
+__PACKAGE__-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::Loader::Base#CONSTRUCTOR_OPTIONS">loader_options</a>({
naming => 'v7',
debug => $ENV{DBIC_TRACE},
});
# elsewhere...
-my $schema = Foo::Schema->connect($dsn, $user, $pass);
+my $schema = Foo::Schema->connect(
+ $dsn, $user, $pass
+);
</pre>
</div>
<div class="slide">
- <h1>Splitting Logic Cleanly</h1>
- <p>Foo::Schema::Result::Bar = individual row</p>
- <p>Foo::Schema::ResultSet::Bar = searches / table </p>
+ <h1>Populate</h1>
+ <p>Made for inserting lots of rows very quicky into database</p>
+<pre>$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#populate">populate</a>([ Users =>
+ [qw( username password )],
+ [qw( frew >=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>Using your Schema</h1>
-<pre>#!perl
-use strict; use warnings;
-use lib 'lib';
-use Foo::Schema;
-my $schema = Foo::Schema->connect($dns, $user, $pass);
-my $author_rs = $schema->resultset('Author');
-my $author = $author_rs->create({
- name => 'Douglas Adams',
-});
-my $book = $author->add_to_books({
- title => '42',
+ <h1>Multicreate</h1>
+ <p>Create an object and all of it's related objects all at once</p>
+<pre>$schema->resultset('Author')-><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#create">create</a>({
+ name => 'Stephen King',
+ books => [{ title => 'The Dark Tower' }],
+ address => {
+ street => '123 Turtle Back Lane',
+ state => { abbreviation => 'ME' },
+ city => { name => '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>DEBUGGING</h1>
- <pre>DBIC_TRACE=1 ./your_script.pl</pre>
- </div>
-
- <div class="slide">
- <h1>SQL - debugging</h1>
-<pre>INSERT INTO authors (name)
- VALUES (?): 'Douglas Adams'
-
-INSERT INTO books (author, title)
- VALUES (?, ?): '5', '42'</pre>
- </div>
-
- <div class="slide">
- <h1>overloading</h1>
-<pre>Foo::Schema::Result::Book
-Foo::Schema::ResultSet::Book
-Foo::Schema::Result::Author
-Foo::Schema::ResultSet::Book</pre>
- </div>
-
- <div class="slide">
- <h1>Result::</h1>
-<pre>package Foo::Schema::Result::Book;
-use base 'DBIx::Class::Core';
-use strict;
-use warnings;
-
-# Result code here
-
-sub isbn {
- my $self = shift;
-
- # search amazon or something
- my $api = Amazon::API->book({
- title => $self->title
- });
-
- return $api->isbn;
-}
-
-1;</pre>
+ <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>Result::</h1>
-<pre>print $book->isbn;</pre>
+ <h1>Extensible: DBIC::Helpers</h1>
+ <ul class="incremental">
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::IgnoreWantarray">DBIC::Helper::ResultSet::IgnoreWantarray</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::Random">DBIC::Helper::ResultSet::Random</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::SetOperations">DBIC::Helper::ResultSet::SetOperations</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::JoinTable">DBIC::Helper::Row::JoinTable</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::NumifyGet">DBIC::Helper::Row::NumifyGet</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::SubClass">DBIC::Helper::Row::SubClass</a></li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Helper::ResultSet::ToJSON">DBIC::Helper::Row::ToJSON</a></li>
+ </ul>
</div>
<div class="slide">
- <h1>Result:: (inflating)</h1>
-<pre>package Foo::Schema::Result::Book;
-use base 'DBIx::Class::Core';
-use strict;
-use warnings;
-
-# Result code here
-
-__PACKAGE__->load_components('InflateColumn');
-use DateTime::Format::MySQL;
-
-__PACKAGE__-><strong>inflate_column</strong>(
- <strong>date_published</strong> => {
- inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
- deflate => sub { shift->ymd},
- },
-);
-# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
+ <h1>Extensible: <a href="http://search.cpan.org/perldoc?DBIx::Class::TimeStamp">DBIC::TimeStamp</a></h1>
+ <ul class="incremental">
+ <li>Cross DB</li>
+ <li>set_on_create</li>
+ <li>set_on_update</li>
+ </ul>
</div>
<div class="slide">
- <h1>Result:: (deflating)</h1>
-<pre>$book->date_published(DateTime->now);
-$book->update;</pre>
+ <h1>Extensible: Kioku</h1>
+ <ul class="incremental">
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema::KiokuDB">DBIx::Class::Schema::KiokuDB</a></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>Result:: (inflating)</h1>
-<pre>my $date_published = $book->date_published;
-print $date_published->month_abbr;</pre>
-
-<strong><em>Nov</em></strong>
+ <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>ResultSets::</h1>
-<pre>package Foo::Schema::ResultSet::Books;
+ <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook#Predefined_searches">ResultSet methods</a></h1>
+<pre>package MyApp::Schema::ResultSet::Book;
use base 'DBIx::Class::ResultSet';
-sub the_ultimate_books {
+sub good {
my $self = shift;
- return $self->search({ title => { -like => '%42%' } });
-}
-sub by_author {
- my ( $self, $author ) = @_;
- return $self->search({ author => $author->id })
-}
-
-1;</pre>
- </div>
-
- <div class="slide">
- <h1>ResultSets::</h1>
-<pre>use Foo::Schema;
-my $schema = Foo::Schema->connect(...);
-my $book_rs = Foo::Schema->resultset('Book');
-my $book_search = $book_rs->the_ultimate_books;
-my @books = $book_search->all;</pre>
- </div>
-
- <div class="slide">
- <h1>ResultSets: Chaining</h1>
-<pre>
-my $book_rs = $schema->resultset('Book');
-my $author_rs = $schema->resultset('Author');
-my $author = $author_model->search({ name => 'Douglas Adams' })->single;
-my $book_rs = $book_model->the_ultimate_books->by_author($author);
-my @books = $book_rs->all;</pre>
- </div>
-
- <div class="slide">
- <h1>ResultSets: Chaining</h1>
-<pre>$book_rs = $schema->resultset('Book')
- ->the_ultimate_books
- ->by_author($author);</pre>
-or
-
-<pre>my $book_rs = $schema->resultset('Book')
- ->the_ultimate_books;
-$book_rs = $book_rs->by_author($author);</pre>
-<pre># Debug (SQL):
-
-# SELECT me.id, me.title, me.date_published, me.author
-# FROM books me
-# WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'
-# WHERE ( ( ( author = ? ) AND ( title LIKE ? ) ) ): '5', '%42%'</pre>
- </div>
-
- <div class="slide">
- <h1>ResultSets::chaining</h1>
-<pre>my $rs = $book_model
- ->category('childrens')
- ->by_author($author)
- ->published_after('1812')
- ->first_page_contains('once upon')
- ->rating_greater_than(4);
-
-my @books = $rs->all;</pre>
- </div>
-
- <div class="slide">
- <h1>overloading before new record</h1>
- <pre>package Foo::Schema::Result::Author;
-use base 'DBIx::Class::Core';
-
-sub new {
- my ( $class, $attrs ) = @_;
- # Mess with $attrs
- my $new = $class->next::method($attrs);
- return $new
-}
-
-1;</pre>
-
- <div class="slide">
- <h1>relationships</h1>
- </div>
-
- <div class="slide">
- <h1>multiple authors</h1>
- </div>
-
- <div class="slide">
- <h1>a few relationships</h1>
- (authors -- author_link_to_book -- books)
- </div>
-
- <div class="slide">
- <h1>a few relationships</h1>
- !
- </div>
-
- <div class="slide">
- <h1>new join table</h1>
-<pre>CREATE TABLE author_and_books(
- id int(8) primary key auto_increment,
- book int(8),
- author int(8),
- foreign key (book) references books(id),
- foreign key (author) references authors(id)
-) engine = InnoDB DEFAULT CHARSET=utf8;
-
-ALTER TABLE `books` DROP `author`</pre>
+ $self->search({
+ $self->current_source_alias .
+ '.rating' => { '>=' => 4 },
+ })
+};
+sub cheap {
+ my $self = shift;
+ $self->search({
+ $self->current_source_alias .
+ '.price' => { '<=' => 5}
+ })
+};
+# ...
+1;
+ </pre>
</div>
<div class="slide">
- <h1>new join table</h1>
-<pre>CREATE TABLE author_and_books(
- id int(8) primary key auto_increment,
- book int(8),
- author int(8),
- <strong>foreign key (book) references books(id),
- foreign key (author) references authors(id)</strong>
-) engine = InnoDB DEFAULT CHARSET=utf8;
-
-ALTER TABLE `books` DROP `author`</pre>
+ <h1>ResultSet method notes</h1>
+ <ul class="incremental">
+ <li>All searches should be ResultSet methods</li>
+ <li>Name has obvious meaning</li>
+ <li><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#current_source_alias">current_source_alias</a> helps things to work no matter what</li>
+ </ul>
</div>
<div class="slide">
- <h1>has_many</h1>
+ <h1>ResultSet method in Action</h1>
+ <pre>$schema->resultset('Book')->good</pre>
</div>
<div class="slide">
- <h1>has_many</h1>
-<pre>package Foo::Schema::<strong>Result::Books</strong>;
-
-__PACKAGE__->has_many( author_and_books => "Foo::Schema::Result::AuthorAndBooks",
- { "foreign.book" => "self.id" },
-);
-
-<strong># This is auto generated by Schema::Loader</strong></pre>
- </div>
-
- <div class="slide">
- <h1>has_many</h1>
-<pre>package Foo::Schema::<strong>Result::Books</strong>;
-
-__PACKAGE__->has_many(
-author_and_books => <strong># name of accessor</strong>
-"Foo::Schema::Result::AuthorAndBooks", <strong># related class</strong>
- { "foreign.book" => "self.id" } <strong># Relationship (magic often works if not
- # specified, but avoid!)</strong>
-);
+ <h1>ResultSet Chaining</h1>
+<pre>$schema->resultset('Book')
+ ->good
+ ->cheap
+ ->recent
</pre>
</div>
<div class="slide">
- <h1>belongs_to</h1>
- </div>
-
- <div class="slide">
- <h1>belongs_to</h1>
-<pre>package Foo::Schema::<strong>Result::AuthorAndBooks</strong>;
-
-__PACKAGE__->belongs_to(
- book => <strong># Accessor name</strong>
- "Foo::Schema::Result::Books", <strong># Related class</strong>
- { id => "book" } <strong># relationship</strong>
-);
+ <h1>search_related</h1>
+<pre>my $score = $schema->resultset('User')
+ ->search({'me.userid' => 'frew'})
+ -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#related_resultset">related_resultset</a>('access')
+ ->related_resultset('mgmt')
+ ->related_resultset('orders')
+ ->telephone
+ -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#search_related">search_related</a>( shops => {
+ 'shops.datecompleted' => {
+ -between => ['2009-10-01','2009-10-08']
+ }
+ })->completed
+ ->related_resultset('rpt_score')
+ -><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#get_column">get_column</a>('raw_scores')
+ ->first;
</pre>
</div>
<div class="slide">
- <h1>same for Authors</h1>
- </div>
-
- <div class="slide">
- <h1>with no coding...</h1>
- </div>
-
- <div class="slide">
- <h1>many_to_many</h1>
- </div>
-
- <div class="slide">
- <h1>many_to_many</h1>
- <pre>package Foo::Schema::<strong>Result::Book</strong>;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- authors => "author_and_books", 'author'
+ <h1>bonus rel methods</h1>
+<pre>my $book = $author-><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship::Base#create_related">create_related</a>(
+ <strong>books</strong> => {
+ title => 'Another Discworld book',
+ }
);
-1;
-
-<strong> # This is <em>NOT</em> auto generated by Schema::Loader </strong></pre>
+my $book2 = $pratchett-><a href="http://search.cpan.org/perldoc?DBIx::Class::Relationship::Base#add_to_$rel">add_to_<strong>books</strong></a>({
+ title => 'MOAR Discworld book',
+});</pre>
+ <ul class="incremental">
+ <li>Automaticaly fills in foreign key for you</li>
+ </ul>
</div>
<div class="slide">
- <h1>many_to_many</h1>
- <pre>package Foo::Schema::<strong>Result::Book</strong>;
-use base 'DBIx::Class::Core';
+ <h1>Excellent Transaction Support</h1>
+<pre>$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_do">txn_do</a>(sub {
+ ...
+});
-__PACKAGE__->many_to_many(
- authors <strong># Accessor name</strong>
- => "author_and_books", <strong># has_many</strong>
- 'author' <strong># foreign relationship name</strong>
-);
+my $guard = $schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_scope_guard">txn_scope_guard</a>;
+# ...
+$guard->commit;
-1;</pre>
+$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_begin">txn_begin</a>; # <-- low level
+# ...
+$schema-><a href="http://search.cpan.org/perldoc?DBIx::Class::Schema#txn_commit">txn_commit</a>;
+</pre>
</div>
<div class="slide">
- <h1>many_to_many</h1>
- <pre>package Foo::Schema::Result::Author;
+ <h1>InflateColumn</h1>
+<pre>package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- "books" <strong># Accessor Name</strong>
- => "author_and_books", <strong># has_many accessor_name</strong>
- 'book' <strong># foreign relationship name</strong>
+use DateTime::Format::MySQL;
+# Result code here
+__PACKAGE__->load_components('<a href="http://search.cpan.org/perldoc?DBIx::Class::InflateColumn">InflateColumn</a>');
+__PACKAGE__-><strong><a href="http://search.cpan.org/perldoc?DBIx::Class::InflateColumn#inflate_column">inflate_column</a></strong>(
+ <strong>date_published</strong> => {
+ inflate => sub {
+ DateTime::Format::MySQL->parse_date(
+ shift
+ )
+ },
+ deflate => sub { shift->ymd },
+ },
);
-
-1;
-
-<strong># This is <em>NOT</em> auto generated by Schema::Loader</strong></pre>
+# Automatic see: DBIC::InflateColumn::DateTime</pre>
</div>
<div class="slide">
- <h1>using many_to_many</h1>
- <pre>#!/usr/bin/perl
-
-use Foo::Schema;
-
-my $author_model = Foo::Schema->resultset('Authors');
-my $author = $author_model->search({
- name => 'Douglas Adams',
-})->single;
-$author->add_to_books({
- title => 'A new book',
-});</pre>
+ <h1>InflateColumn: deflation</h1>
+<pre>$book->date_published(DateTime->now);
+$book->update;</pre>
</div>
<div class="slide">
- <h1>using many_to_many</h1>
- <pre>my $author = $author_model->search({
- name => 'Douglas Adams',
-})->single;
-<strong>$author->add_to_books({
- title => 'A new book',
-});</strong>
+ <h1>InflateColumn: inflation</h1>
+<pre>say $book->date_published->month_abbr;</pre>
-# SELECT me.id, me.name FROM authors me
-# WHERE ( name = ? ): 'Douglas Adams';
-# INSERT INTO books (title) VALUES (?): 'A new book';
-# INSERT INTO author_and_books (author, book)
-# VALUES (?, ?): '5', '2';</pre>
+<strong><em>Nov</em></strong>
</div>
<div class="slide">
- <h1>using many_to_many</h1>
- <pre>$author->add_to_books($book);
+ <h1>FilterColumn</h1>
+<pre>package Foo::Schema::Result::Book;
+use base 'DBIx::Class::Core';
+# Result code here
+__PACKAGE__->load_components('<a href="http://search.cpan.org/perldoc?DBIx::Class::FilterColumn">FilterColumn</a>');
-$book->add_to_authors($author_1);
-$book->add_to_authors($author_2);</pre>
- </div>
+__PACKAGE__-><strong><a href="http://search.cpan.org/perldoc?DBIx::Class::FilterColumn#filter_column">filter_column</a></strong>(
+ <strong>length</strong> => {
+ to_storage => 'to_metric',
+ from_storage => 'to_imperial',
+ },
+);
- <div class="slide">
- <h1>in 16 lines of code</h1>
+sub to_metric { $_[1] * .305 }
+sub to_imperial { $_[1] * 3.28 }
</div>
<div class="slide">
- <h1>errors</h1>
- <p>Read them closely!</p>
+ <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSetColumn">ResultSetColumn</a></h1>
+<pre>my $rsc = $schema->resultset('Book')
+ ->get_column('price');
+$rsc->first;
+$rsc->all;
+$rsc->min;
+$rsc->max;
+$rsc->sum;
+</pre>
</div>
<div class="slide">
- <h1>error messages</h1>
- <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'Foo::Schema::Result::Authors': Can't locate object method "many_to_many" via package "Foo::Schema::Result::Author" at lib/Foo/Schema/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
+ <h1>Aggregates</h1>
+<pre>my @res = $rs->search({}, {
+ <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#select">select</a> => [
+ 'price',
+ 'genre',
+ { max => price },
+ { avg => price },
+ ],
+ <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#as">as</a> => [
+ qw(price genre max_price avg_price)
+ ],
+ <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#group_by">group_by</a> => [qw(price genre)],
+});
+for (@res) {
+ say $_->price . ' ' . $_->genre;
+ say $_->get_column('max_price');
+ say $_->get_column('min_price');
+}</pre>
</div>
<div class="slide">
- <h1>error messages</h1>
- <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'Foo::Schema::Result::Authors': Can't locate object method "many_to_many" via package "Foo::Schema::<strong>Result::Author</strong>" at lib/Foo/Schema/<strong>Result/Authors.pm</strong> line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
+ <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>errors</h1>
- <ul>
- <li>Turn on debugging</li>
- <li>Read error messages (sometimes useful!)</li>
- <li>Check field names</li>
- <li>Check package names</li>
- <li>Check which database you are connected to (dev/test/live?) - repeat above</li>
+ <h1>HRI</h1>
+<pre>$rs->search({}, {
+ <a href="http://search.cpan.org/perldoc?DBIx::Class::ResultSet#result_class">result_class</a> =>
+ '<a href="http://search.cpan.org/perldoc?DBIx::Class::ResultClass::HashRefInflator">DBIx::Class::ResultClass::HashRefInflator</a>',
+});</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 < 3s)</li>
</ul>
</div>
<div class="slide">
- <h1>bonus slides!</h1>
- </div>
+ <h1><a href="http://search.cpan.org/perldoc?DBIx::Class::Manual::Cookbook#Subqueries">Subquery</a> Support</h1>
+<pre>my $inside_query = $schema->resultset('Artist')
+ ->search({
+ name => [ 'Billy Joel', 'Brittany Spears' ],
+})->get_column('id')->as_query;
- <div class="slide">
- <h1>Template Toolkit</h1>
- <ul>
- <li><pre>[% author.books.count %]</pre> not working?</li>
- <li>TT all methods are called in list context</li>
- <li><pre>[% author.books<strong>_rs</strong>.count %]</pre> scalar context</li>
- <li><em>Available for all relationships</em></li>
- </ul>
+my $rs = $schema->resultset('CD')->search({
+ artist_id => { -in => $inside_query },
+});</pre>
</div>
<div class="slide">
- <h1>Catalyst</h1>
- <pre>package Your::App::Model::<strong>Foo</strong>;
-use base qw(<strong>Catalyst::Model::Schema::Schema</strong>);
-
-use strict;
-use warnings;
-
-__PACKAGE__->config(
- schema_class => '<strong>Foo::Schema</strong>',
-);
+ <h1><a href="http://search.cpan.org/perldoc?SQL::Abstract#Literal_SQL_with_placeholders_and_bind_values_(subqueries)">Bare SQL w/ Placeholders</a></h1>
+<pre>$rs->update({
+ # !!! SQL INJECTION VECTOR
+ price => \"price + $inc",
+});
-1;</pre>
- <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
+$rs->update({
+ price => \['price + ?', [inc => $inc]],
+});
+</pre>
</div>
<div class="slide">
- <h1>Catalyst</h1>
-<pre>sub action_name : Local {
- my ($self, $c) = @_;
-
- my $model = $c->model('Schema::Foo');
- my $author_model = $model->resultset('Authors');
-
-}
-
-1;</pre>
+ <h1>Questions?</h1>
</div>
+ <div class="slide">
+ <h1>END</h1>
+ </div>
</div>
</body>
</html>