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)