<div class="slide">
<h1>DBIX::Class (aka DBIC)</h1>
<h3>for (advanced) beginners</h3>
- <h4>Leo Lapworth @ LPW 2009</h4>
- <h4><a href="http://leo.cuckoo.org/projects">http://leo.cuckoo.org/projects</a></h4>
- <div class="handout"></div>
</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>
+ <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>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>
- </ul>
+ <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>why this talk?</h1>
- <ul>
- <li>Help avoid mistakes I made!</li>
- <li>Help learn DBIx::Class faster</li>
- <li>Make your coding easier</li>
- </ul>
+ <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>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>
- </div>
-
- <div class="slide">
- <h1>table setup</h1>
- </div>
-
- <div class="slide">
- <h1>example...</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>
</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>(issue: Matt Trout "Tables should not be plural as gives you plurals for Result:: package names which represent a single row" - talk may be rewritten in future to reflect this as this is better once you understand the relationship setup - either way, consistency is important)</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 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>Check foreign key is the same field and 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 <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>manual: create</h1>
+ <h1>SQL: Create</h1>
<pre>my $sth = $dbh->prepare('
INSERT INTO books
- (title, author)
+ (title, author_id)
values (?,?)
');
</div>
<div class="slide">
- <h1>manual: create</h1>
-<pre>my $sth = $dbh->prepare('
- <strong>INSERT INTO books
- (title, author)
- values (?,?)</strong>
-');
-
-$sth->execute(
- 'A book title', <strong><em>$author_id</em></strong>
-);</pre>
+ <h1>DBIC: Create</h1>
+<pre>my $book = $book_rs->create({
+ 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>
+ </ul>
</div>
<div class="slide">
- <h1>manual: retrieve</h1>
+ <h1>SQL: Read</h1>
<pre>my $sth = $dbh->prepare('
SELECT title,
authors.name as author_name
</div>
<div class="slide">
- <h1>manual: retrieve</h1>
+ <h1>SQL: Read</h1>
<pre>while( my $book = $sth->fetchrow_hashref() ) {
print 'Author of '
. $book->{title}
</div>
<div class="slide">
- <h1>manual: update</h1>
-<pre>my $update = $dbh->prepare('
- UPDATE books
- SET title = ?
- WHERE id = ?
-');
-
-$update->execute(
- 'New title',<strong>$book_id</strong>
-);</pre>
- </div>
-
- <div class="slide">
- <h1>manual: delete</h1>
-<pre>my $delete = $dbh->prepare('
- DELETE FROM books
- WHERE id = ?
-');
-
-$delete->execute(<strong>$book_id</strong>);</pre>
- </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>
- <p><strong>Tip:</strong> do not pass in primary_key field, even if it's empty/undef as the object returned will have an empty id, even if your field is auto increment.</p>
- </div>
-
- <div class="slide">
- <h1>DBIC: create</h1>
-<pre>my $book = $book_model->create({
- title => 'A book title',
- author => <strong>$author_id</strong>,
-});</pre>
- </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(
- books => {
- title => 'Another Discworld book',
-});</pre>
-<strong>or</strong>
-<pre>my $book = $pratchett->add_to_books({
- title => 'Another Discworld book',
-});</pre>
- </div>
+ <h1>DBIC: Read</h1>
+<pre>my $book = $book_rs->find($book_id);
- <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: retrieve</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>
- </div>
-
- <div class="slide">
- <h1>DBIC: retrieve</h1>
-<pre>my $book = $book_model->find($book_id);
-
-my $book = $book_model->search({
+my $book = $book_rs->search({
title => 'A book title',
-})->single;
+}, { rows => 1 })->single;
-my @books = $book_model->search({
+my @books = $book_rs->search({
author => $author_id,
})->all;</pre>
+ <ul class="incremental">
+ <li>TMTOWTDI</li>
+ </ul>
</div>
<div class="slide">
- <h1>DBIC: retrieve</h1>
-<pre>while( my $book = $books_rs->next() ) {
+ <h1>DBIC: Read</h1>
+<pre>while( my $book = $books_rs->next ) {
print 'Author of '
. $book->title
. ' is '
</div>
<div class="slide">
- <h1>DBIC: retrieve</h1>
-<pre>my $books_rs = $book_model->search({
- author => $author_id,
-});</pre>
- <p>Search takes SQL::Abstract formatted queries</p>
- <pre>> perldoc SQL::Abstract</p>
+ <h1>SQL: Update</h1>
+<pre>my $update = $dbh->prepare('
+ UPDATE books
+ SET title = ?
+ WHERE id = ?
+');
+
+$update->execute(
+ 'New title',<strong>$book_id</strong>
+);</pre>
</div>
<div class="slide">
- <h1>DBIC: update</h1>
+ <h1>DBIC: Update</h1>
<pre>$book->update({
title => 'New title',
});</pre>
</div>
<div class="slide">
- <h1>DBIC: delete</h1>
-<pre>$book->delete;</pre>
- </div>
-
- <div class="slide">
- <h1>Creating models</h1>
- </div>
-
- <div class="slide">
- <pre>Example of a DBIC Result</pre>
- </div>
+ <h1>SQL: Delete</h1>
+<pre>my $delete = $dbh->prepare('
+ DELETE FROM books
+ WHERE id = ?
+');
- <div class="slide">
- <pre>Example of a DBIC Result</pre>
+$delete->execute(<strong>$book_id</strong>);</pre>
</div>
<div class="slide">
- <p>too much typing! too much maintenance!</p>
- <p>too much maintenance!</p>
+ <h1>DBIC: Delete</h1>
+<pre>$book->delete;</pre>
</div>
<div class="slide">
- <h1>Schema::Loader</h1>
- <pre>code for S::L here</pre>
+ <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>splitting logic cleanly</h1>
- <p>LPW::DBIC::Result::Foo = an individual row</p>
- <p>LPW::DBIC::ResultSet::Foo = searches / results</p>
+ <h1>Convenience Methods</h1>
+ <ul class="incremental">
+ <li>find_or_create</li>
+ <li>create_or_update</li>
+ </ul>
</div>
<div class="slide">
- <h1>using your Schema</h1>
- <pre>example usage code goes here</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>DEBUGGING</h1>
- <pre>DBIC_TRACE=1 ./your_script.pl</pre>
+ <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>Schema::Loader</h1>
-<pre>LPW::DBIC::Result::Authors->table("authors");
-LPW::DBIC::Result::Authors->add_columns(
- id => {
- data_type => "INT",
- default_value => undef,
- is_nullable => 0,
- size => 8
- },
- title => {
- data_type => "VARCHAR",
- default_value => undef,
- is_nullable => 1,
- size => 255,
- },
-);
-LPW::DBIC::Result::Authors->set_primary_key("id");</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">
- <h1>Schema::Loader</h1>
-<pre>LPW::DBIC::Result::Books->table("books");
-LPW::DBIC::Result::Books->add_columns(
- id => {
- data_type => "INT",
- default_value => undef,
- is_nullable => 0,
- size => 8
- },
- name => {
- data_type => "VARCHAR",
- default_value => undef,
- is_nullable => 1,
- size => 255,
- },
- author => {
- data_type => "INT",
- default_value => undef,
- is_nullable => 1,
- size => 8
- },
-);
-LPW::DBIC::Result::Books->set_primary_key("id");</pre>
+ <h1>->deploy</h1>
+ <p>Perl -> DB</p>
+<pre>my $schema = Foo::Schema->connect($dsn, $user, $pass);
+$schema->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>
-<pre>LPW::DBIC::Result::Authors->has_many(books => "LPW::DBIC::Books",
- { "foreign.author" => "self.id" });
-
-LPW::DBIC::Result::Books->belongs_to(author => "LPW::DBIC::Authors",
- { id => "author" });</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>LPW::DBIC::Result::Books
-LPW::DBIC::ResultSet::Books
-LPW::DBIC::Result::Authors
-LPW::DBIC::ResultSet::Books</pre>
- </div>
-
- <div class="slide">
- <h1>Result::</h1>
-<pre>package LPW::DBIC::Result::Books;
-use base 'DBIx::Class';
-use strict;
-use warnings;
-
-sub isbn {
- my $self = shift;
-
- # search amazon or something
- my $api = Amazon::API->book({
- title => $self->title
- });
-
- return $api->isbn;
-}
-
-1;</pre>
- </div>
-
- <div class="slide">
- <h1>Result::</h1>
-<pre>print $book->isbn;</pre>
- </div>
-
- <div class="slide">
- <h1>Result:: (inflating)</h1>
-<pre>package LPW::DBIC::Result::Books;
-use base 'DBIx::Class';
-use strict;
-use warnings;
+ <p>DB -> Perl</p>
+<pre>package Foo::Schema;
+use strict; use warnings;
+use base 'DBIx::Class::Schema::Loader';
+__PACKAGE__->loader_options({
+ naming => 'v7',
+ debug => $ENV{DBIC_TRACE},
+});
+1;
-use DateTime::Format::MySQL;
+# elsewhere...
-__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>
+my $schema = Foo::Schema->connect($dsn, $user, $pass);
+</pre>
</div>
<div class="slide">
- <h1>Result:: (inflating)</h1>
-<pre>package LPW::DBIC::Result::Books;
-use base 'DBIx::Class';
-use strict;
-use warnings;
-
-use DateTime::Format::MySQL;
-
-__PACKAGE__->inflate_column(
- date_published => {
- <strong>inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
- deflate => sub { shift->ymd},</strong>
- }
+ <h1>Populate</h1>
+ <p>Made for inserting lots of rows very quicky into database</p>
+<pre>$schema->populate([ Users =>
+ [qw( username password )],
+ [qw( frew >=4char$ )],
+ [qw( ... )],
+ [qw( ... )],
);
-# Automatic see: DBIx::Class::InflateColumn::DateTime
-# Automatic see: DBIx::Class::InflateColumn::DateTime
-# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
- </div>
-
- <div class="slide">
- <h1>Result:: (deflating)</h1>
-<pre>$book->date_published(DateTime->now);
-$book->update;</pre>
- </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>
- </div>
-
- <div class="slide">
- <h1>ResultSets::</h1>
-<pre>package LPW::DBIC::ResultSet::Books;
-use base 'DBIx::Class::ResultSet';
-sub the_ultimate_books {
- 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>package LPW::DBIC::<strong>ResultSet::Books</strong>;
-use base '<strong>DBIx::Class::ResultSet</strong>';
-sub the_ultimate_books {
- my $self = shift;
- <strong>return $self->search({ title => { -like => '%42%' } })</strong>
-}
-sub by_author {
- my ( $self, $author ) = @_;
- return $self->search({ author => $author->id })
-}
-
-1;</pre>
+</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>ResultSets::</h1>
-<pre>package LPW::DBIC::ResultSet::Books;
-use base 'DBIx::Class::ResultSet';
-sub the_ultimate_books {
- my $self = shift;
- return $self->search({ title => { -like => '%42%' } });
-}
-sub by_author {
- my ( $self, $author ) = @_;
- <strong>return $self->search({ author => $author->id })</strong>
-}
-
-1;</pre>
+ <h1>Multicreate</h1>
+ <p>Create an object and all of it's related objects all at once</p>
+<pre>$schema->resultset('Author')->create({
+ 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>ResultSets::</h1>
-<pre>use LPW::DBIC;
-my $book_model = LPW::DBIC->resultset('Books');
-my $book_rs = $book_model->the_ultimate_books;
-my @books = $book_rs->all;</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>ResultSets::chaining</h1>
-<pre>use LPW::DBIC;
-my $book_model = LPW::DBIC->resultset('Books');
-my $author_model = LPW::DBIC->resultset('Authors');
-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>
+ <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>ResultSets::chaining</h1>
-<pre>my $book_rs = $book_model
- ->the_ultimate_books
- ->by_author($author);</pre>
-or
-
-<pre>my $book_rs = $book_model
- ->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>
+ <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>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>
+ <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>overloading before new record</h1>
+ <h1>SQL::Abstract</h1>
+<pre>my $resultset = $book_rs->search({
+ name => { -like => "%$nick%" },
+});</pre>
+ <ul class="incremental">
+ <li>(kinda) introspectible</li>
+ <li>Prettier than SQL</li>
+ </ul>
</div>
<div class="slide">
- <h1>overloading before new record</h1>
- <pre>package LPW::DBIC::Result::Authors;
-use base 'DBIx::Class';
-
-sub new {
- my ( $class, $attrs ) = @_;
- # Mess with $attrs
- my $new = $class->next::method($attrs);
- return $new
-}
-
-1;</pre>
-
- <div class="slide">
- <h1>relationships</h1>
+ <h1>Result vs ResultSet</h1>
+ <ul class="incremental">
+ <li>Result == Row</li>
+ <li>ResultSet == Query</li>
+ <li>(less important but...)</li>
+ <li>ResultSource == Table</li>
+ <li>Storage == Database</li>
+ </ul>
</div>
<div class="slide">
- <h1>multiple authors</h1>
+ <h1>ResultSet methods</h1>
+<pre>package MyApp::Schema::ResultSet::Book;
+use base 'DBIx::Class::ResultSet';
+sub good {
+ $_[0]->search({
+ rating => { '>=' => 4 },
+ })
+};
+sub cheap {
+ $_[0]->search({
+ price => { '<=' => 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>a few relationships</h1>
- (authors -- author_link_to_book -- books)
+ <h1>ResultSet method in Action</h1>
+ <pre>$schema->resultset('Book')->good</pre>
</div>
<div class="slide">
- <h1>a few relationships</h1>
- !
+ <h1>ResultSet Chaining</h1>
+<pre>$schema->resultset('Book')
+ ->good
+ ->cheap
+ ->recent
+</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),
- foreign key (book) references books(id),
- foreign key (author) references authors(id)
-) engine = InnoDB DEFAULT CHARSET=utf8;
-
-ALTER TABLE `books` DROP `author`</pre>
+ <h1>search_related</h1>
+ <pre>*get example from work*</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>
- </div>
+ <h1>bonus rel methods</h1>
+<pre>my $book = $author->create_related(
+ <strong>books</strong> => {
+ title => 'Another Discworld book',
+});
- <div class="slide">
- <h1>has_many</h1>
+my $book2 = $pratchett->add_to_<strong>books</strong>({
+ title => 'MOAR Discworld book',
+});</pre>
+ <ul class="incremental">
+ <li>Automaticaly fills in foreign key for you</li>
+ </ul>
</div>
<div class="slide">
- <h1>has_many</h1>
-<pre>package LPW::DBIC::<strong>Result::Books</strong>;
+ <h1>Excellent Transaction Support</h1>
+<pre>$schema->txn_do(sub {
+ ...
+});
-__PACKAGE__->has_many( author_and_books => "LPW::DBIC::Result::AuthorAndBooks",
- { "foreign.book" => "self.id" },
-);
+my $guard = $schema->txn_scope_guard;
+# ...
+$guard->commit;
-<strong># This is auto generated by Schema::Loader</strong></pre>
- </div>
-
- <div class="slide">
- <h1>has_many</h1>
-<pre>package LPW::DBIC::<strong>Result::Books</strong>;
-
-__PACKAGE__->has_many(
-author_and_books => <strong># name of accessor</strong>
-"LPW::DBIC::Result::AuthorAndBooks", <strong># related class</strong>
- { "foreign.book" => "self.id" } <strong># Relationship (magic often works if not
- # specified, but avoid!)</strong>
-);
+$schema->txn_begin; # <-- low level
+# ...
+$schema->txn_commit;
</pre>
</div>
<div class="slide">
- <h1>belongs_to</h1>
- </div>
-
- <div class="slide">
- <h1>belongs_to</h1>
-<pre>package LPW::DBIC::<strong>Result::AuthorAndBooks</strong>;
-
-__PACKAGE__->belongs_to(
- book => <strong># Accessor name</strong>
- "LPW::DBIC::Result::Books", <strong># Related class</strong>
- { id => "book" } <strong># relationship</strong>
+ <h1>InflateColumn</h1>
+<pre>package Foo::Schema::Result::Book;
+use base 'DBIx::Class::Core';
+# 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},
+ },
);
-</pre>
+# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
</div>
<div class="slide">
- <h1>same for Authors</h1>
+ <h1>InflateColumn: deflation</h1>
+<pre>$book->date_published(DateTime->now);
+$book->update;</pre>
</div>
<div class="slide">
- <h1>with no coding...</h1>
- </div>
+ <h1>InflateColumn: inflation</h1>
+<pre>my $date_published = $book->date_published;
+print $date_published->month_abbr;</pre>
- <div class="slide">
- <h1>many_to_many</h1>
+<strong><em>Nov</em></strong>
</div>
<div class="slide">
- <h1>many_to_many</h1>
- <pre>package LPW::DBIC::<strong>Result::Books</strong>;
-use base 'DBIx::Class';
+ <h1>FilterColumn</h1>
+<pre>package Foo::Schema::Result::Book;
+use base 'DBIx::Class::Core';
+# Result code here
+__PACKAGE__->load_components('FilterColumn');
-__PACKAGE__->many_to_many(
- authors => "author_and_books", 'author'
+__PACKAGE__-><strong>filter_column</strong>(
+ <strong>length</strong> => {
+ to_storage => 'to_metric',
+ from_storage => 'to_imperial',
+ },
);
-1;
-
-<strong> # This is <em>NOT</em> auto generated by Schema::Loader </strong></pre>
+sub to_metric { $_[1] * .305 }
+sub to_imperial { $_[1] * 3.28 }
+# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
</div>
<div class="slide">
- <h1>many_to_many</h1>
- <pre>package LPW::DBIC::<strong>Result::Books</strong>;
-use base 'DBIx::Class';
-
-__PACKAGE__->many_to_many(
- authors <strong># Accessor name</strong>
- => "author_and_books", <strong># has_many</strong>
- 'author' <strong># foreign relationship name</strong>
-);
-
-1;</pre>
+ <h1>ResultSetColumn</h1>
+<pre>my $rsc = $schema->resultset('Book')->get_column('price');
+$rsc->min;
+$rsc->max;
+$rsc->sum;
+</pre>
</div>
<div class="slide">
- <h1>many_to_many</h1>
- <pre>package LPW::DBIC::Result::Authors;
-use base 'DBIx::Class';
-
-__PACKAGE__->many_to_many(
- "books" <strong># Accessor Name</strong>
- => "author_and_books", <strong># has_many accessor_name</strong>
- 'book' <strong># foreign relationship name</strong>
-);
-
-1;
-
-<strong># This is <em>NOT</em> auto generated by Schema::Loader</strong></pre>
+ <h1>Aggregates</h1>
+<pre>my @res = $rs->search({}, {
+ select => [qw(price genre), { max => price }, { avg => price }],
+ as => [qw(price genre max_price avg_price)],
+ group_by => [qw(price genre)],
+});
+for (@res) {
+ say $_->price . ' ' . $_->genre;
+ say $_->get_column('max_price');
+ say $_->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>using many_to_many</h1>
- <pre>#!/usr/bin/perl
-
-use LPW::DBIC;
-
-my $author_model = LPW::DBIC->resultset('Authors');
-my $author = $author_model->search({
- name => 'Douglas Adams',
-})->single;
-$author->add_to_books({
- title => 'A new book',
+ <h1>HRI</h1>
+<pre>$rs->search({}, {
+ result_class => '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 < 3s)</li>
+ </ul>
</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>
-
-# 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>
- </div>
-
- <div class="slide">
- <h1>using many_to_many</h1>
- <pre>$author->add_to_books($book);
-
-$book->add_to_authors($author_1);
-$book->add_to_authors($author_2);</pre>
- </div>
-
- <div class="slide">
- <h1>in 16 lines of code</h1>
- </div>
-
- <div class="slide">
- <h1>errors</h1>
- <p>Read them closely!</p>
- </div>
-
- <div class="slide">
- <h1>error messages</h1>
- <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'LPW::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "LPW::DBIC::Result::Author" at lib/LPW/DBIC/Result/Authors.pm line 9.Compilation failed in require at /Library/Perl/5.8.8/DBIx/Class/Schema/Loader/Base.pm line 292.</pre>
- </div>
-
- <div class="slide">
- <h1>error messages</h1>
- <pre>DBIx::Class::Schema::Loader::connection(): Failed to load external class definition for 'LPW::DBIC::Result::Authors': Can't locate object method "many_to_many" via package "LPW::DBIC::<strong>Result::Author</strong>" at lib/LPW/DBIC/<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>
- </div>
+ <h1>Subquery Support</h1>
+<pre> my $inside_rs = $schema->resultset('Artist')->search({
+ name => [ 'Billy Joel', 'Brittany Spears' ],
+});
- <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>
- </ul>
+my $rs = $schema->resultset('CD')->search({
+ artist_id => { -in => $inside_rs->get_column('id')->as_query },
+});</pre>
</div>
<div class="slide">
- <h1>bonus slides!</h1>
- </div>
+ <h1>Bare SQL w/ Placeholders</h1>
+<pre>$rs->update({
+ price => \"price + $inc", # !!! SQL INJECTION VECTOR
+});
- <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>
+$rs->update({
+ price => \['price + ?', [inc => $inc]],
+});
+</pre>
</div>
<div class="slide">
- <h1>Catalyst</h1>
- <pre>package Your::App::Model::<strong>LPW</strong>;
-use base qw(<strong>Catalyst::Model::DBIC::Schema</strong>);
-
-use strict;
-use warnings;
-
-__PACKAGE__->config(
- schema_class => '<strong>LPW::DBIC</strong>',
-);
-
-1;</pre>
- <p>Keep your Schema in a <em>separate</em> package to your Catalyst application</p>
+ <h1>Questions?</h1>
</div>
<div class="slide">
- <h1>Catalyst</h1>
-<pre>sub action_name : Local {
- my ($self, $c) = @_;
-
- my $model = $c->model('DBIC::LPW');
- my $author_model = $model->resultset('Authors');
-
-}
-
-1;</pre>
+ <h1>END</h1>
</div>
-
</div>
</body>
</html>
projects / dbsrgits/dbix-class-introduction-presentation.git / blobdiff