DBIx::Class::Manual::Features − A boatload of DBIx::Class features with links to respective documentation
Large Community
There are hundres of DBIC contributors listed in AUTHORS . 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
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
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::Class::ResultSet, 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
Check the list of additional DBIC resources.
This module is free software copyright by the DBIx::Class ( DBIC ) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.