DBIx::Class::Manual::Features(3) User Contributed Perl Documentation DBIx::Class::Manual::Features(3)
NAME
DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation
META
Large Community
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.
Active Community
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>.)
Responsive Community
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
", $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
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::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 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)
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
perl v5.18.2 2014-01-05 DBIx::Class::Manual::Features(3)