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
These are reasons that are not technical or inherent to the code of DBIC, but are totally awesome things about it.
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.
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 ebbs and flows.
These are things that are in most other ORMs, but are still reasons to use DBIC over raw SQL.
The vast majority of code should run on all databases without needing tweaking
my $sth = $dbh->prepare('
INSERT INTO books
(title, author_id)
values (?,?)
');
$sth->execute(
'A book title', $author_id
);
my $book = $book_rs->create({ title => 'A book title', author_id => $author_id, });
my $sth = $dbh->prepare('
SELECT title,
authors.name as author_name
FROM books, authors
WHERE books.author = authors.id
');
while( my $book = $sth->fetchrow_hashref() ) {
print 'Author of '
. $book->{title}
. ' is '
. $book->{author_name}
. "\n";
}
my $book = $book_rs->find($book_id); my $book = $book_rs->search({ title => 'A book title', }, { rows => 1 })->next; my @books = $book_rs->search({ author => $author_id, })->all; while( my $book = $books_rs->next ) { print 'Author of ' . $book->title . ' is ' . $book->author->name . "\n"; }
my $update = $dbh->prepare('
UPDATE books
SET title = ?
WHERE id = ?
');
$update->execute(
'New title',$book_id
);
my $delete = $dbh->prepare('
DELETE FROM books
WHERE id = ?
');
$delete->execute($book_id);
$book->delete;
my $sth = $dbh->prepare('
SELECT title,
authors.name as author_name
FROM books
WHERE books.name LIKE "%monte cristo%" AND
books.topic = "jailbreak"
');
my $book = $book_rs->search({ 'me.name' => { -like => '%monte cristo%' }, 'me.topic' => 'jailbreak', })->next;
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
Need a method to get a user's gravatar URL? Add a gravatar_url method to their Result class
These things may be in other ORM's, but they are very specific, so doubtful
Perl -> DB
my $schema = Foo::Schema->connect( $dsn, $user, $pass ); $schema->deploy
See also: DBIx::Class::DeploymentHandler
DB -> Perl
package Foo::Schema; use strict; use warnings; use base 'DBIx::Class::Schema::Loader'; __PACKAGE__->loader_options({ naming => 'v7', debug => $ENV{DBIC_TRACE}, }); 1; # elsewhere... my $schema = Foo::Schema->connect( $dsn, $user, $pass );
Made for inserting lots of rows very quicky into database
$schema->populate([ Users => [qw( username password )], [qw( frew >=4char$ )], [qw( ... )], [qw( ... )], );
Create an object and all of it's related objects all at once
$schema->resultset('Author')->create({
name => 'Stephen King',
books => [{ title => 'The Dark Tower' }],
address => {
street => '123 Turtle Back Lane',
state => { abbreviation => 'ME' },
city => { name => 'Lowell' },
},
});
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.
package MyApp::Schema::ResultSet::Book;
use base 'DBIx::Class::ResultSet';
sub good {
my $self = shift;
$self->search({
$self->current_source_alias .
'.rating' => { '>=' => 4 },
})
};
sub cheap {
my $self = shift;
$self->search({
$self->current_source_alias .
'.price' => { '<=' => 5}
})
};
# ...
1;
$schema->resultset('Book')->good
$schema->resultset('Book')
->good
->cheap
->recent
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;
my $book = $author->create_related( books => { title => 'Another Discworld book', } ); my $book2 = $pratchett->add_to_books({ title => 'MOAR Discworld book', });
$schema->txn_do(sub { ... }); my $guard = $schema->txn_scope_guard; # ... $guard->commit; $schema->txn_begin; # <-- low level # ... $schema->txn_commit;
package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
use DateTime::Format::MySQL;
# Result code here
__PACKAGE__->load_components('InflateColumn');
__PACKAGE__->inflate_column(
date_published => {
inflate => sub {
DateTime::Format::MySQL->parse_date(
shift
)
},
deflate => sub { shift->ymd },
},
);
# Automatic see: DBIC::InflateColumn::DateTime
$book->date_published(DateTime->now); $book->update;
say $book->date_published->month_abbr;Nov
package Foo::Schema::Result::Book;
use base 'DBIx::Class::Core';
# Result code here
__PACKAGE__->load_components('FilterColumn');
__PACKAGE__->filter_column(
length => {
to_storage => 'to_metric',
from_storage => 'to_imperial',
},
);
sub to_metric { $_[1] * .305 }
sub to_imperial { $_[1] * 3.28 }
my $rsc = $schema->resultset('Book')
->get_column('price');
$rsc->first;
$rsc->all;
$rsc->min;
$rsc->max;
$rsc->sum;
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');
}
$rs->search({}, {
result_class =>
'DBIx::Class::ResultClass::HashRefInflator',
});
my $inside_query = $schema->resultset('Artist')
->search({
name => [ 'Billy Joel', 'Brittany Spears' ],
})->get_column('id')->as_query;
my $rs = $schema->resultset('CD')->search({
artist_id => { -in => $inside_query },
});
$rs->update({
# !!! SQL INJECTION VECTOR
price => \"price + $inc",
});
$rs->update({
price => \['price + ?', [inc => $inc]],
});