NAME¶
DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to
respective documentation
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 (committed 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
<
http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=Changes>.)
- I needed MSSQL order-by support; the community helped me
add support
- generally very welcoming of people willing to help
General ORM¶
These are things that are in most other ORMs, but are still reasons to use DBIC
over raw SQL.
Cross DB¶
The vast majority of code should run on all databases without needing tweaking
Basic CRUD¶
- C - Create
- R - Retrieve
- U - Update
- D - Delete
SQL: Create¶
my $sth = $dbh->prepare('
INSERT INTO books
(title, author_id)
values (?,?)
');
$sth->execute( 'A book title', $author_id );
DBIC: Create¶
my $book = $book_rs->create({
title => 'A book title',
author_id => $author_id,
});
See "create" in DBIx::Class::ResultSet
- No need to pair placeholders and values
- Automatically gets autoincremented id for you
- Transparently uses INSERT ... RETURNING for databases that
support it
SQL: Read¶
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 ) {
say "Author of $book->{title} is $book->{author_name}";
}
DBIC: Read¶
my $book = $book_rs->find($book_id);
or
my $book = $book_rs->search({ title => 'A book title' }, { rows => 1 })->next;
or
my @books = $book_rs->search({ author => $author_id })->all;
or
while( my $book = $books_rs->next ) {
printf "Author of %s is %s\n", $book->title, $book->author->name;
}
See "find" in DBIx::Class::ResultSet, "search" in
DBIx::Class::ResultSet, "next" in DBIx::Class::ResultSet, and
"all" in DBIx::Class::ResultSet
TMTOWTDI!
SQL: Update¶
my $update = $dbh->prepare('
UPDATE books
SET title = ?
WHERE id = ?
');
$update->execute( 'New title', $book_id );
DBIC: Update¶
$book->update({ title => 'New title' });
See "update" in DBIx::Class::Row
Will not update unless value changes
SQL: Delete¶
my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');
$delete->execute($book_id);
DBIC: Delete¶
$book->delete
See "delete" in DBIx::Class::Row
SQL: Search¶
my $sth = $dbh->prepare('
SELECT title,
authors.name as author_name
FROM books
WHERE books.name LIKE "%monte cristo%" AND
books.topic = "jailbreak"
');
DBIC: Search¶
my $book = $book_rs->search({
'me.name' => { -like => '%monte cristo%' },
'me.topic' => 'jailbreak',
})->next;
- See SQL::Abstract, "next" in
DBIx::Class::ResultSet, and "search" in
DBIx::Class::ResultSet
- (kinda) introspectible
- Prettier than SQL
OO Overridability¶
- Override new if you want to do validation
- Override delete if you want to disable deletion
- and on and on
Convenience Methods¶
- "find_or_create" in DBIx::Class::ResultSet
- "update_or_create" in DBIx::Class::ResultSet
Non-column methods¶
Need a method to get a user's gravatar URL? Add a "gravatar_url"
method to the Result class
RELATIONSHIPS¶
- "belongs_to" in DBIx::Class::Relationship
- "has_many" in DBIx::Class::Relationship
- "might_have" in DBIx::Class::Relationship
- "has_one" in DBIx::Class::Relationship
- "many_to_many" in DBIx::Class::Relationship
- SET AND FORGET
DBIx::Class Specific Features¶
These things may be in other ORM's, but they are very specific, so doubtful
->deploy¶
Create a database from your DBIx::Class schema.
my $schema = Frew::Schema->connect( $dsn, $user, $pass );
$schema->deploy
See "deploy" in DBIx::Class::Schema.
See also: DBIx::Class::DeploymentHandler
Schema::Loader¶
Create a DBIx::Class schema from your database.
package Frew::Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema::Loader';
__PACKAGE__->loader_options({
naming => 'v7',
debug => $ENV{DBIC_TRACE},
});
1;
# elsewhere...
my $schema = Frew::Schema->connect( $dsn, $user, $pass );
See DBIx::Class::Schema::Loader and "CONSTRUCTOR_OPTIONS" in
DBIx::Class::Schema::Loader::Base.
Populate¶
Made for inserting lots of rows very quickly into database
$schema->populate([ Users =>
[qw( username password )],
[qw( frew >=4char$ )],
[qw( ... )],
[qw( ... )],
);
See "populate" in DBIx::Class::Schema
I use populate here <
http://blog.afoolishmanifesto.com/archives/1255> to
export our whole (200M~) db to SQLite
Multicreate¶
Create an object and its 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' },
},
});
See "create" in DBIx::Class::ResultSet
- books is a has_many
- address is a belongs_to which in turn belongs to state and
city each
- for this to work right state and city must mark
abbreviation and name as unique
Extensible¶
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.
Extensibility example: DBIx::Class::Helpers¶
- DBIx::Class::Helper::ResultSet::IgnoreWantarray
- DBIx::Class::Helper::ResultSet::Random
- DBIx::Class::Helper::ResultSet::SetOperations
- DBIx::Class::Helper::Row::JoinTable
- DBIx::Class::Helper::Row::NumifyGet
- DBIx::Class::Helper::Row::SubClass
- DBIx::Class::Helper::Row::ToJSON
- DBIx::Class::Helper::Row::StorageValues
- DBIx::Class::Helper::Row::OnColumnChange
Extensibility example: DBIx::Class::TimeStamp¶
- See DBIx::Class::TimeStamp
- Cross DB
- set_on_create
- set_on_update
Extensibility example: Kioku¶
- See DBIx::Class::Schema::KiokuDB
- Kioku is the new hotness
- Mix RDBMS with Object DB
Result vs ResultSet¶
- Result == Row
- ResultSet == Query Plan
- Internal Join Optimizer for all DB's (!!!)
- (less important but...)
- ResultSource == Queryable collection of rows (Table, View,
etc)
- Storage == Database
- Schema == associates a set of ResultSources with a
Storage
ResultSet methods¶
package MyApp::Schema::ResultSet::Book;
use strict;
use warnings;
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;
See "Predefined searches" in DBIx::Class::Manual::Cookbook
- All searches should be ResultSet methods
- Name has obvious meaning
- "current_source_alias" in DBIx::Class::ResultSet
helps things to work no matter what
ResultSet method in Action¶
$schema->resultset('Book')->good
ResultSet Chaining¶
$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')
->search(undef, { rows => 1})
->get_column('raw_scores')
->next;
The SQL that this produces (with placeholders filled in for clarity's sake) on
our system (Microsoft SQL) is:
SELECT raw_scores
FROM (
SELECT raw_scores, ROW_NUMBER() OVER (
ORDER BY (
SELECT (1)
)
) AS rno__row__index
FROM (
SELECT rpt_score.raw_scores
FROM users me
JOIN access access
ON access.userid = me.userid
JOIN mgmt mgmt
ON mgmt.mgmtid = access.mgmtid
JOIN [order] orders
ON orders.mgmtid = mgmt.mgmtid
JOIN shop shops
ON shops.orderno = orders.orderno
JOIN rpt_scores rpt_score
ON rpt_score.shopno = shops.shopno
WHERE (
datecompleted IS NOT NULL AND
(
(shops.datecompleted BETWEEN '2009-10-01' AND '2009-10-08') AND
(type = '1' AND me.userid = 'frew')
)
)
) rpt_score
) rpt_score
WHERE rno__row__index BETWEEN 1 AND 1
See: "related_resultset" in DBIx::Class::ResultSet,
"search_related" in DBIx::ClassResultSet, and "get_column"
in DBIx::Class::ResultSet.
bonus rel methods¶
my $book = $author->create_related(
books => {
title => 'Another Discworld book',
}
);
my $book2 = $pratchett->add_to_books({
title => 'MOAR Discworld book',
});
See "create_related" in DBIx::Class::Relationship::Base and
"add_to_$rel" in DBIx::Class::Relationship::Base
Note that it automatically fills in foreign key for you
Excellent Transaction Support¶
$schema->txn_do(sub {
...
});
$schema->txn_begin; # <-- low level
# ...
$schema->txn_commit;
See "txn_do" in DBIx::Class::Schema, "txn_begin" in
DBIx::Class::Schema, and "txn_commit" in DBIx::Class::Schema.
InflateColumn¶
package Frew::Schema::Result::Book;
use strict;
use warnings;
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 },
},
);
See DBIx::Class::InflateColumn, "inflate_column" in
DBIx::Class::InflateColumn, and DBIx::Class::InflateColumn::DateTime.
InflateColumn: deflation¶
$book->date_published(DateTime->now);
$book->update;
InflateColumn: inflation¶
say $book->date_published->month_abbr; # Nov
FilterColumn¶
package Frew::Schema::Result::Book;
use strict;
use warnings;
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 }
See DBIx::Class::FilterColumn and "filter_column" in
DBIx::Class::FilterColumn
ResultSetColumn¶
my $rsc = $schema->resultset('Book')->get_column('price');
$rsc->first;
$rsc->all;
$rsc->min;
$rsc->max;
$rsc->sum;
See DBIx::Class::ResultSetColumn
Aggregates¶
my @res = $rs->search(undef, {
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('avg_price');
}
See "select" in DBIx::Class::ResultSet, "as" in
DBIx::Class::ResultSet, and "group_by" in DBIx::Class::ResultSet
- Careful, get_column can basicaly mean three
things
- private in which case you should use an accessor
- public for what there is no accessor for
- public for get resultset column (prev example)
HRI¶
$rs->search(undef, {
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
});
See "result_class" in DBIx::Class::ResultSet and
DBIx::Class::ResultClass::HashRefInflator.
- Easy on memory
- Mega fast
- Great for quick debugging
- Great for performance tuning (we went from 2m to <
3s)
Subquery Support¶
my $inner_query = $schema->resultset('Artist')
->search({
name => [ 'Billy Joel', 'Brittany Spears' ],
})->get_column('id')->as_query;
my $rs = $schema->resultset('CD')->search({
artist_id => { -in => $inner_query },
});
See "Subqueries" in DBIx::Class::Manual::Cookbook
Bare SQL w/ Placeholders¶
$rs->update({
# !!! SQL INJECTION VECTOR
price => \"price + $inc", # DON'T DO THIS
});
Better:
$rs->update({
price => \['price + ?', [inc => $inc]],
});
See "Literal_SQL_with_placeholders_and_bind_values_(subqueries)" in
SQL::Abstract