state => { abbreviation => 'ME' },
city => { name => 'Lowell' },
},
+});
</pre>
<div class="notes">
<ul>
<div class="slide">
<h1>SQL::Abstract</h1>
- <pre>my $resultset = $book_rs->search({
- name => { -like => "%$nick%" },
- });</pre>
+<pre>my $resultset = $book_rs->search({
+ name => { -like => "%$nick%" },
+});</pre>
<ul class="incremental">
<li>(kinda) introspectible</li>
<li>Prettier than SQL</li>
<pre>package MyApp::Schema::ResultSet::Book;
use base 'DBIx::Class::ResultSet';
sub good {
- my $self = shift;
- $self->search({
+ $_[0]->search({
rating => { '>=' => 4 },
})
};
sub cheap {
- my $self = shift;
- $self->search({
+ $_[0]->search({
price => { '<=' => 5}
})
};
</div>
<div class="slide">
- <h1>DEBUGGING</h1>
- <pre>DBIC_TRACE=1 ./your_script.pl</pre>
+ <h1>search_related</h1>
+ <pre>*get example from work*</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>
+ <h1>bonus rel methods</h1>
+<pre>my $book = $author->create_related(
+ <strong>books</strong> => {
+ title => 'Another Discworld book',
+});
- <div class="slide">
- <h1>overloading</h1>
-<pre>Foo::Schema::Result::Book
-Foo::Schema::ResultSet::Book
-Foo::Schema::Result::Author
-Foo::Schema::ResultSet::Book</pre>
+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>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;
-}
+ <h1>Excellent Transaction Support</h1>
+<pre>$schema->txn_do(sub {
+ ...
+});
-1;</pre>
- </div>
+my $guard = $schema->txn_scope_guard;
+# ...
+$guard->commit;
- <div class="slide">
- <h1>Result::</h1>
-<pre>print $book->isbn;</pre>
+$schema->txn_begin; # <-- low level
+# ...
+$schema->txn_commit;
+</pre>
</div>
<div class="slide">
- <h1>Result:: (inflating)</h1>
+ <h1>InflateColumn</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) },
</div>
<div class="slide">
- <h1>Result:: (deflating)</h1>
+ <h1>InflateColumn: deflation</h1>
<pre>$book->date_published(DateTime->now);
$book->update;</pre>
</div>
<div class="slide">
- <h1>Result:: (inflating)</h1>
+ <h1>InflateColumn: inflation</h1>
<pre>my $date_published = $book->date_published;
print $date_published->month_abbr;</pre>
</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->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_rs->search({ name => 'Douglas Adams' })->single;
-$book_rs = $book_rs->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_rs
- ->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;
+ <h1>FilterColumn</h1>
+<pre>package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
+# Result code here
+__PACKAGE__->load_components('FilterColumn');
-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>
- <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__->has_many( author_and_books =>
- '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__->belongs_to(
- book =>
- 'Foo::Schema::Result::Book', 'book_id'
+__PACKAGE__-><strong>filter_column</strong>(
+ <strong>length</strong> => {
+ to_storage => 'to_metric',
+ from_storage => 'to_imperial',
+ },
);
-</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" />
+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 Foo::Schema::<strong>Result::Book</strong>;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- authors => 'author_and_books', 'author'
-);
-
-1;
+ <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 Foo::Schema::<strong>Result::Book</strong>;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- authors <strong># Accessor name</strong>
- => "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__->many_to_many(
- "books" <strong># Accessor Name</strong>
- => "author_and_books", <strong># has_many accessor_name</strong>
- 'book' <strong># foreign relationship name</strong>
-);
-
-1;
-</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>#!perl
-use Foo::Schema;
-my $schema = Foo::Schema->connect(...);
-my $author_rs = $schema->resultset('Authors');
-my $author = $author_rs->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>
- </div>
-
- <div class="slide">
- <h1>using many_to_many</h1>
- <pre>my $author = $author_rs->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>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 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>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>
+ <h1>Subquery Support</h1>
+<pre> my $inside_rs = $schema->resultset('Artist')->search({
+ name => [ 'Billy Joel', 'Brittany Spears' ],
+});
- <div class="slide">
- <h1>bonus slides!</h1>
+my $rs = $schema->resultset('CD')->search({
+ artist_id => { -in => $inside_rs->get_column('id')->as_query },
+});</pre>
</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>
+ <h1>Bare SQL w/ Placeholders</h1>
+<pre>$rs->update({
+ price => \"price + $inc", # !!! SQL INJECTION VECTOR
+});
+
+$rs->update({
+ price => \['price + ?', [inc => $inc]],
+});
+</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>',
-);
-
-1;</pre>
- <p>Keep your Schema in a <em>separate</em> package from your Catalyst application</p>
+ <h1>Questions?</h1>
</div>
<div class="slide">
- <h1>Catalyst</h1>
-<pre>sub action_name : Local {
- my ($self, $c) = @_;
-
- my $schema = $c->model('Schema::Foo');
- my $author_rs = $schema->resultset('Authors');
-
-}
-
-1;</pre>
+ <h1>END</h1>
</div>
-
</div>
</body>
</html>
projects / dbsrgits/dbix-class-introduction-presentation.git / commitdiff