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.
Extensibilityexample: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
Extensibilityexample:DBIx::Class::TimeStamp
See DBIx::Class::TimeStamp
Cross DB
set_on_create
set_on_update
Extensibilityexample:Kioku
See DBIx::Class::Schema::KiokuDB
Kioku is the new hotness
Mix RDBMS with Object DB
ResultvsResultSet
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
ResultSetmethods
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
ResultSetmethodinAction
$schema->resultset('Book')->good
ResultSetChaining
$schema->resultset('Book')
->good
->cheap
->recent
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')
->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::Class::ResultSet, and
"get_column" in DBIx::Class::ResultSet.
bonusrelmethods
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
ExcellentTransactionSupport
$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 basically 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)
SubquerySupport
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
BareSQLw/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::Classic
Install Now
PNG Icons