Populate
Made for inserting lots of rows very quicky into database
-
$schema->populate([ Users =>
+$schema->populate([ Users =>
[qw( username password )],
- [qw( frew >=4char$ )],
+ [qw( frew >=4char$ )],
[qw( ... )],
[qw( ... )],
);
@@ -361,14 +365,15 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
Multicreate
Create an object and all of it's related objects all at once
-
$schema->resultset('Author')->create({
+$schema->resultset('Author')->create({
name => 'Stephen King',
books => [{ title => 'The Dark Tower' }],
address => {
street => '123 Turtle Back Lane',
state => { abbreviation => 'ME' },
- city => { name => 'Lowell' },
+ city => { name => 'Lowell' },
},
+});
@@ -388,18 +393,18 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
Extensible: DBIC::Helpers
-
Extensible: DBIC::TimeStamp
+
- Cross DB
- set_on_create
@@ -408,8 +413,9 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
-
Extensible: DBIx::Class::Schema::KiokuDB
+
Extensible: Kioku
+ - DBIx::Class::Schema::KiokuDB
- Kioku is the new hotness
- Mix RDBMS with Object DB
- beta ( == sexy )
@@ -417,10 +423,10 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
-
SQL::Abstract
-
my $resultset = $book_rs->search({
- name => { -like => "%$nick%" },
- });
+
+
my $resultset = $book_rs->search({
+ name => { -like => "%$nick%" },
+});
- (kinda) introspectible
- Prettier than SQL
@@ -431,7 +437,9 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
Result vs ResultSet
- Result == Row
- - ResultSet == Query
+ - ResultSet == Query Plan
+ - Internal Join Optimizer for all DB's (!!!)
+
- (less important but...)
- ResultSource == Table
- Storage == Database
@@ -439,27 +447,34 @@ my $schema = Foo::Schema->connect($dsn, $user, $pass);
-
ResultSet methods
+
package MyApp::Schema::ResultSet::Book;
use base 'DBIx::Class::ResultSet';
sub good {
my $self = shift;
$self->search({
- rating => { '>=' => 4 },
+ $self->current_source_alias .
+ '.rating' => { '>=' => 4 },
})
};
sub cheap {
my $self = shift;
$self->search({
- price => { '<=' => 5}
+ $self->current_source_alias .
+ '.price' => { '<=' => 5}
})
};
# ...
1;
+
+
ResultSet method notes
- All searches should be ResultSet methods
- Name has obvious meaning
+ - current_source_alias helps things to work no matter what
-
DEBUGGING
-
DBIC_TRACE=1 ./your_script.pl
+
search_related
+
my $score = $schema->resultset('User')
+ ->search({'me.userid' => 'frew'})
+ ->related_resultset('access')
+ ->related_resultset('mgmt')
+ ->related_resultset('orders')
+ ->telephone
+ ->search_related( shops => {
+ 'shops.datecompleted' => {
+ -between => ['2009-10-01','2009-10-08']
+ }
+ })->completed
+ ->related_resultset('rpt_score')
+ ->get_column('raw_scores')
+ ->first;
+
-
SQL - debugging
-
INSERT INTO authors (name)
- VALUES (?): 'Douglas Adams'
-
-INSERT INTO books (author, title)
- VALUES (?, ?): '5', '42'
-
bonus rel methods
+my $book = $author->create_related(
+ books => {
+ title => 'Another Discworld book',
+ }
+);
-
-
overloading
-
Foo::Schema::Result::Book
-Foo::Schema::ResultSet::Book
-Foo::Schema::Result::Author
-Foo::Schema::ResultSet::Book
+my $book2 = $pratchett->
add_to_books({
+ title => 'MOAR Discworld book',
+});
+
+ - Automaticaly fills in foreign key for you
+
-
Result::
-
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;
-}
+ Excellent Transaction Support
+$schema->txn_do(sub {
+ ...
+});
-1;
-
-
Result::
-
print $book->isbn;
+$schema->
txn_begin; # <-- low level
+# ...
+$schema->
txn_commit;
+
-
Result:: (inflating)
+
InflateColumn
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__->inflate_column(
+# Result code here
+__PACKAGE__->load_components('InflateColumn');
+__PACKAGE__->inflate_column(
date_published => {
- inflate => sub { DateTime::Format::MySQL->parse_date(shift) },
- deflate => sub { shift->ymd},
+ inflate => sub {
+ DateTime::Format::MySQL->parse_date(
+ shift
+ )
+ },
+ deflate => sub { shift->ymd },
},
);
-# Automatic see: DBIx::Class::InflateColumn::DateTime
+# Automatic see: DBIC::InflateColumn::DateTime
-
Result:: (deflating)
+
InflateColumn: deflation
$book->date_published(DateTime->now);
$book->update;
-
Result:: (inflating)
-
my $date_published = $book->date_published;
-print $date_published->month_abbr;
+
InflateColumn: inflation
+
say $book->date_published->month_abbr;
Nov
-
ResultSets::
-
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;
-
-
ResultSets::
-
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;
-
-
ResultSets: Chaining
-
-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;
-
-
ResultSets: Chaining
-
$book_rs = $schema->resultset('Book')
- ->the_ultimate_books
- ->by_author($author);
-or
-
-
my $book_rs = $schema->resultset('Book')
- ->the_ultimate_books;
-$book_rs = $book_rs->by_author($author);
-
# 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%'
-
-
ResultSets: Chaining
-
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;
-
-
overloading before new record
-
package Foo::Schema::Result::Author;
+ FilterColumn
+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;
-
-
-
Relationships
-
-
-
-
Multiple Authors
-
We want to allow a book to be by more than one author
-
-
a few relationships
-
-
-
Join Table
-
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`;
-
-
has_many
-
-
-
has_many
-
package Foo::Schema::Result::Book;
-
-__PACKAGE__->has_many( author_and_books =>
- 'Foo::Schema::Result::Author_Book', 'book_id'
-);
-
-
belongs_to
-
-
-
belongs_to
-
package Foo::Schema::Result::Author_Book;
-
-__PACKAGE__->belongs_to(
- book =>
- 'Foo::Schema::Result::Book', 'book_id'
+__PACKAGE__->filter_column(
+ length => {
+ to_storage => 'to_metric',
+ from_storage => 'to_imperial',
+ },
);
-
-
-
same for Authors
-
-
-
many_to_many
-
+sub to_metric { $_[1] * .305 }
+sub to_imperial { $_[1] * 3.28 }
-
many_to_many
-
package Foo::Schema::Result::Book;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- authors => 'author_and_books', 'author'
-);
-
-1;
+
+my $rsc = $schema->resultset('Book')
+ ->get_column('price');
+$rsc->first;
+$rsc->all;
+$rsc->min;
+$rsc->max;
+$rsc->sum;
-
many_to_many
-
package Foo::Schema::Result::Book;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- authors # Accessor name
- => "author_and_books", # has_many
- 'author' # foreign relationship name
-);
-
-1;
+
Aggregates
+
my @res = $rs->search({}, {
+ select => [
+ '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');
+}
-
many_to_many
-
package Foo::Schema::Result::Author;
-use base 'DBIx::Class::Core';
-
-__PACKAGE__->many_to_many(
- "books" # Accessor Name
- => "author_and_books", # has_many accessor_name
- 'book' # foreign relationship name
-);
-
-1;
-
+
Aggregates Notes
+
+ - Careful, get_column can basicaly mean THREE things
+ - private for get what you should use an accessor for
+ - public for what there is no accessor for
+ - public for get resultset column (prev slide)
+
-
Using many_to_many
-
#!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',
+ HRI
+$rs->search({}, {
+ result_class =>
+ 'DBIx::Class::ResultClass::HashRefInflator',
});
-
-
using many_to_many
-
my $author = $author_rs->search({
- name => 'Douglas Adams',
-})->single;
-$author->add_to_books({
- title => 'A new book',
-});
-
-# 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';
-
-
using many_to_many
-
$author->add_to_books($book);
-
-$book->add_to_authors($author_1);
-$book->add_to_authors($author_2);
-
-
errors
-
Read them closely!
-
-
error messages
-
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.
-
-
error messages
-
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.
-
-
errors
-
- - Turn on debugging
- - Read error messages (sometimes useful!)
- - Check field names
- - Check package names
- - Check which database you are connected to (dev/test/live?) - repeat above
+
+ - Easy on memory
+ - Mega fast
+ - Great for quick debugging
+ - Great for performance tuning (we went from 2m to < 3s)
-
LOTS more Features
-
- - FilterColumn
- - Transactions
- - HashRefInflator
- - Subqueries
- - ResultSetColumn
- - Aggregate Queries
-
-
my $inside_query = $schema->resultset('Artist')
+ ->search({
+ name => [ 'Billy Joel', 'Brittany Spears' ],
+})->get_column('id')->as_query;
-
-
bonus slides!
+my $rs = $schema->resultset('CD')->search({
+ artist_id => { -in => $inside_query },
+});
-
Template Toolkit
-
+
+
$rs->update({
+ # !!! SQL INJECTION VECTOR
+ price => \"price + $inc",
+});
+
+$rs->update({
+ price => \['price + ?', [inc => $inc]],
+});
+
-
Catalyst
-
package Your::App::Model::Foo;
-use base qw(Catalyst::Model::Schema::Schema);
-
-use strict;
-use warnings;
-
-__PACKAGE__->config(
- schema_class => 'Foo::Schema',
-);
-
-1;
-
Keep your Schema in a separate package from your Catalyst application
+
Questions?
-
Catalyst
-
sub action_name : Local {
- my ($self, $c) = @_;
-
- my $schema = $c->model('Schema::Foo');
- my $author_rs = $schema->resultset('Authors');
-
-}
-
-1;
+
END