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

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

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

<head>
<title>DBIx::Class (aka DBIC)</title>
<!-- metadata -->
<meta name="contributor" content="youcan[64]netzgesta[46]de" />
<meta name="publisher" content="s5.netzgesta.de" />
<meta name="description" content="S5 1.3 is a very flexible and lightweight slide show system available for anyone to use (including transitions and scalable fonts and images)" />
<meta name="keywords" content="S5, version 1.3, slide show, presentation-mode, projection-mode, powerpoint-like, scala-like, keynote-like, incremental display, scalable fonts, scalable images, transitions, notes, osf, xoxo, css, javascript, xhtml, public domain" />

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

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

<div class="presentation">

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

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

   <div class="slide">
      <h1>What's up guys?</h1>
      <div class="notes">
         <ul>
            <li>How many people have 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>
            </ul></li>
         </ul>
      </div>
   </div>

   <div class="slide">
      <h1>DBIx::Class?</h1>
      <ul>
         <li>ORM (object relational mapper)</li>
         <li>SQL &lt;-&gt; OO (using objects instead of SQL)</li>
         <li>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>
   </div>

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

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

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

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

   <div class="slide">
      <h1>DBIC: Create</h1>
<pre>my $book = $book_rs-&gt;create({
   title     =&gt; 'A book title',
   author_id =&gt; $author_id,
});</pre>
      <p>Don't need to work to pair placeholders and values</p>
   </div>

   <div class="slide">
      <h1>DBIC: Create</h1>
<pre>my $pratchett = $author_rs-&gt;create({
   name =&gt; 'Terry Pratchett',
});</pre>
   </div>

   <div class="slide">
      <h1>DBIC: Create</h1>
<pre>my $book = $pratchett-&gt;create_related(
  <strong>books</strong> =&gt; {
   title =&gt; 'Another Discworld book',
});</pre>
<strong>or</strong>
<pre>my $book = $pratchett-&gt;add_to_<strong>books</strong>({
   title =&gt; 'Another Discworld book',
});</pre>
		<p>Automaticaly fills in foreign key for you</p>
   </div>

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

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

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

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

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

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

   <div class="slide">
      <h1>DBIC: Read</h1>
<pre>my $resultset = $book_rs-&gt;search({
   author =&gt; $author_id,
});</pre>
      <p>Search takes SQL::Abstract formatted queries</p>
      <pre>&gt; perldoc SQL::Abstract</p>
   </div>

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

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

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

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

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

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

   <div class="slide">
      <h1>Creating models</h1>
   </div>

   <div class="slide">
<pre>package Foo::Schema::Result::Author;
__PACKAGE__-&gt;table('authors');
__PACKAGE__-&gt;add_columns(
  id =&gt; {
    data_type         =&gt; 'int',
    is_auto_increment =&gt; 1
  },
  title =&gt; {
    data_type   =&gt; 'varchar',
    is_nullable =&gt; 1,
    size        =&gt; 255,
  },
);
__PACKAGE__-&gt;set_primary_key('id');
__PACKAGE__-&gt;has_many( books =&gt;
   'Foo::Schema::Result::Book', 'author_id'
);
1;
</pre>
   </div>

   <div class="slide">
<pre style="float: left; font-size: 80%;">package Foo::Schema::Result::Book;
use strict; use warnings;
__PACKAGE__-&gt;table('books');
__PACKAGE__-&gt;add_columns(
  id =&gt; {
    data_type         =&gt; 'int',
    is_auto_increment =&gt; 1
  },
  name =&gt; {
    data_type   =&gt; 'varchar',
    is_nullable =&gt; 1,
    size        =&gt; 255,
  },
</pre>
<pre style="float: left; font-size: 80%;">
  author_id =&gt; {
    data_type   =&gt; 'int',
    size        =&gt; 8,
  },
);
__PACKAGE__-&gt;set_primary_key('id');
__PACKAGE__-&gt;belongs_to( author =&gt;
   'Foo::Schema::Result::Author', 'author_id'
);
1;
</pre>
   </div>

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

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

# elsewhere...

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

   <div class="slide">
      <h1>Splitting Logic Cleanly</h1>
      <p>Foo::Schema::Result::Bar = individual row</p>
      <p>Foo::Schema::ResultSet::Bar = searches / table </p>
   </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-&gt;connect($dns, $user, $pass);
my $author_rs = $schema-&gt;resultset('Author');
my $author    = $author_rs-&gt;create({
   name =&gt; 'Douglas Adams',
});
my $book = $author-&gt;add_to_books({
   title =&gt; '42',
});
</pre>
   </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-&gt;book({
      title =&gt; $self-&gt;title
   });

   return $api-&gt;isbn;
}

1;</pre>
   </div>

   <div class="slide">
      <h1>Result::</h1>
<pre>print $book-&gt;isbn;</pre>
   </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__-&gt;load_components('InflateColumn');
use DateTime::Format::MySQL;

__PACKAGE__-&gt;<strong>inflate_column</strong>(
   <strong>date_published</strong> =&gt; {
      inflate =&gt; sub { DateTime::Format::MySQL-&gt;parse_date(shift) },
      deflate =&gt; sub { shift-&gt;ymd},
   },
);
# Automatic see: DBIx::Class::InflateColumn::DateTime</pre>
   </div>

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

   <div class="slide">
      <h1>Result:: (inflating)</h1>
<pre>my $date_published = $book-&gt;date_published;
print $date_published-&gt;month_abbr;</pre>

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

   <div class="slide">
      <h1>ResultSets::</h1>
<pre>package Foo::Schema::ResultSet::Books;
use base 'DBIx::Class::ResultSet';
sub the_ultimate_books {
   my $self = shift;
   return $self-&gt;search({ title =&gt; { -like =&gt; '%42%' } });
}
sub by_author {
   my ( $self, $author ) = @_;
   return $self-&gt;search({ author =&gt; $author-&gt;id })
}

1;</pre>
   </div>

   <div class="slide">
      <h1>ResultSets::</h1>
<pre>use Foo::Schema;
my $schema = Foo::Schema-&gt;connect(...);
my $book_rs     = Foo::Schema-&gt;resultset('Book');
my $book_search = $book_rs-&gt;the_ultimate_books;
my @books       = $book_search-&gt;all;</pre>
   </div>

   <div class="slide">
      <h1>ResultSets: Chaining</h1>
<pre>
my $book_rs   = $schema-&gt;resultset('Book');
my $author_rs = $schema-&gt;resultset('Author');
my $author    = $author_rs-&gt;search({ name =&gt; 'Douglas Adams' })-&gt;single;
$book_rs      = $book_rs-&gt;the_ultimate_books-&gt;by_author($author);
my @books     = $book_rs-&gt;all;</pre>
   </div>

   <div class="slide">
      <h1>ResultSets: Chaining</h1>
<pre>$book_rs = $schema-&gt;resultset('Book')
  -&gt;the_ultimate_books
  -&gt;by_author($author);</pre>
or

<pre>my $book_rs = $schema-&gt;resultset('Book')
  -&gt;the_ultimate_books;
$book_rs = $book_rs-&gt;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_rs
  -&gt;category('childrens')
  -&gt;by_author($author)
  -&gt;published_after('1812')
  -&gt;first_page_contains('once upon')
  -&gt;rating_greater_than(4);

my @books = $rs-&gt;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-&gt;next::method($attrs);
   return $new
}

1;</pre>

   <div class="slide">
      <h1>Relationships</h1>
   </div>

   <div class="slide">
      <h1>Multiple Authors</h1>
      <p>We want to allow a book to be by more than one author</p>
   </div>

   <div class="slide">
      <h1>a few relationships</h1>
      <img src="img/afewrels.png" />
   </div>

   <div class="slide">
      <h1>Join Table</h1>
<pre>CREATE TABLE author_and_books(
  book_id    int(8),
  author_id  int(8),
  foreign key (book_id)     references books(id),
  foreign key (author_id)   references authors(id)
) engine = InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `books` DROP `author_id`;</pre>
   </div>

   <div class="slide">
      <h1>has_many</h1>
      <img src="img/hasmany1.png" />
   </div>

   <div class="slide">
      <h1>has_many</h1>
<pre>package Foo::Schema::<strong>Result::Book</strong>;

__PACKAGE__-&gt;has_many( author_and_books =&gt;
   'Foo::Schema::Result::Author_Book', 'book_id'
);
   </div>

   <div class="slide">
      <h1>belongs_to</h1>
      <img src="img/belongsto1.png" />
   </div>

   <div class="slide">
      <h1>belongs_to</h1>
<pre>package Foo::Schema::<strong>Result::Author_Book</strong>;

__PACKAGE__-&gt;belongs_to(
   book =&gt;
   'Foo::Schema::Result::Book', 'book_id'
);
</pre>
   </div>

   <div class="slide">
      <h1>same for Authors</h1>
      <img src="img/authors.png" />
   </div>

   <div class="slide">
      <h1>many_to_many</h1>
      <img src="img/m2m.png" />
   </div>

   <div class="slide">
      <h1>many_to_many</h1>
      <pre>package Foo::Schema::<strong>Result::Book</strong>;
use base 'DBIx::Class::Core';

__PACKAGE__-&gt;many_to_many(
   authors =&gt; 'author_and_books', 'author'
);

1;
</pre>
   </div>

   <div class="slide">
      <h1>many_to_many</h1>
      <pre>package Foo::Schema::<strong>Result::Book</strong>;
use base 'DBIx::Class::Core';

__PACKAGE__-&gt;many_to_many(
   authors <strong># Accessor name</strong>
   =&gt; "author_and_books", <strong># has_many</strong>
   'author' <strong># foreign relationship name</strong>
);

1;</pre>
   </div>

   <div class="slide">
      <h1>many_to_many</h1>
      <pre>package Foo::Schema::Result::Author;
use base 'DBIx::Class::Core';

__PACKAGE__-&gt;many_to_many(
   "books" <strong># Accessor Name</strong>
   =&gt; "author_and_books", <strong># has_many accessor_name</strong>
   'book' <strong># foreign relationship name</strong>
);

1;
</pre>
   </div>

   <div class="slide">
      <h1>Using many_to_many</h1>
      <pre>#!perl
use Foo::Schema;
my $schema    = Foo::Schema-&gt;connect(...);
my $author_rs = $schema-&gt;resultset('Authors');
my $author    = $author_rs-&gt;search({
   name =&gt; 'Douglas Adams',
})-&gt;single;
$author-&gt;add_to_books({
   title =&gt; 'A new book',
});</pre>
   </div>

   <div class="slide">
      <h1>using many_to_many</h1>
      <pre>my $author = $author_rs-&gt;search({
   name =&gt; 'Douglas Adams',
})-&gt;single;
<strong>$author-&gt;add_to_books({
   title =&gt; '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-&gt;add_to_books($book);

$book-&gt;add_to_authors($author_1);
$book-&gt;add_to_authors($author_2);</pre>
   </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 '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>
   </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>
   </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>
      </ul>
   </div>

   <div class="slide">
      <h1>LOTS more Features</h1>
      <ul>
         <li>FilterColumn</li>
         <li>Transactions</li>
         <li>HashRefInflator</li>
         <li>Subqueries</li>
         <li>ResultSetColumn</li>
         <li>Aggregate Queries</li>
      </ul>
   </div>

   <div class="slide">
      <h1>bonus slides!</h1>
   </div>

   <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>
   </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__-&gt;config(
  schema_class =&gt; '<strong>Foo::Schema</strong>',
);

1;</pre>
   <p>Keep your Schema in a <em>separate</em> package from your Catalyst application</p>
   </div>

   <div class="slide">
      <h1>Catalyst</h1>
<pre>sub action_name : Local {
  my ($self, $c) = @_;

  my $schema = $c-&gt;model('Schema::Foo');
  my $author_rs = $schema-&gt;resultset('Authors');

}

1;</pre>
   </div>

</div>
</body>
</html>