3 DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation
9 There are L<hundres of DBIC contributors|DBIx::Class/AUTHORS> listed in
10 F<AUTHORS>. That ranges from documentation help, to test help, to added
11 features, to entire database support.
13 =head2 Active Community
15 Currently (June 9, 2010) 6 active branches (committed to
16 in the last two weeks) in git. Last release (0.08122)
17 had 14 new features, and 16 bug fixes. Of course that
18 L<ebbs and flows|http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=Changes>.)
20 =head2 Responsive Community
24 =item I needed MSSQL order-by support; the community helped me add support
26 =item generally very welcoming of people willing to help
32 These are things that are in most other ORMs, but are still reasons to use
37 The vast majority of code should run on all databases without needing tweaking
55 my $sth = $dbh->prepare('
61 $sth->execute( 'A book title', $author_id );
65 my $book = $book_rs->create({
66 title => 'A book title',
67 author_id => $author_id,
70 See L<DBIx::Class::ResultSet/create>
74 =item No need to pair placeholders and values
76 =item Automatically gets autoincremented id for you
78 =item Transparently uses INSERT ... RETURNING for databases that support it
84 my $sth = $dbh->prepare('
86 authors.name as author_name
88 WHERE books.author = authors.id
91 while ( my $book = $sth->fetchrow_hashref ) {
92 say "Author of $book->{title} is $book->{author_name}";
97 my $book = $book_rs->find($book_id);
101 my $book = $book_rs->search({ title => 'A book title' }, { rows => 1 })->next;
105 my @books = $book_rs->search({ author => $author_id })->all;
109 while( my $book = $books_rs->next ) {
110 printf "Author of %s is %s\n", $book->title, $book->author->name;
113 See L<DBIx::Class::ResultSet/find>, L<DBIx::Class::ResultSet/search>, L<DBIx::Class::ResultSet/next>, and L<DBIx::Class::ResultSet/all>
119 my $update = $dbh->prepare('
125 $update->execute( 'New title', $book_id );
129 $book->update({ title => 'New title' });
131 See L<DBIx::Class::Row/update>
133 Will not update unless value changes
137 my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');
139 $delete->execute($book_id);
145 See L<DBIx::Class::Row/delete>
149 my $sth = $dbh->prepare('
151 authors.name as author_name
153 WHERE books.name LIKE "%monte cristo%" AND
154 books.topic = "jailbreak"
159 my $book = $book_rs->search({
160 'me.name' => { -like => '%monte cristo%' },
161 'me.topic' => 'jailbreak',
166 =item See L<SQL::Abstract>, L<DBIx::Class::ResultSet/next>, and L<DBIx::Class::ResultSet/search>
168 =item (kinda) introspectible
170 =item Prettier than SQL
174 =head2 OO Overridability
178 =item Override new if you want to do validation
180 =item Override delete if you want to disable deletion
186 =head2 Convenience Methods
190 =item L<DBIx::Class::ResultSet/find_or_create>
192 =item L<DBIx::Class::ResultSet/update_or_create>
196 =head2 Non-column methods
198 Need a method to get a user's gravatar URL? Add a C<gravatar_url> method to the
205 =item L<DBIx::Class::Relationship/belongs_to>
207 =item L<DBIx::Class::Relationship/has_many>
209 =item L<DBIx::Class::Relationship/might_have>
211 =item L<DBIx::Class::Relationship/has_one>
213 =item L<DBIx::Class::Relationship/many_to_many>
219 =head1 DBIx::Class Specific Features
221 These things may be in other ORM's, but they are very specific, so doubtful
225 Create a database from your DBIx::Class schema.
227 my $schema = Frew::Schema->connect( $dsn, $user, $pass );
231 See L<DBIx::Class::Schema/deploy>.
233 See also: L<DBIx::Class::DeploymentHandler>
235 =head2 Schema::Loader
237 Create a DBIx::Class schema from your database.
239 package Frew::Schema;
244 use base 'DBIx::Class::Schema::Loader';
246 __PACKAGE__->loader_options({
248 debug => $ENV{DBIC_TRACE},
255 my $schema = Frew::Schema->connect( $dsn, $user, $pass );
257 See L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base/CONSTRUCTOR OPTIONS>.
261 Made for inserting lots of rows very quickly into database
263 $schema->populate([ Users =>
264 [qw( username password )],
265 [qw( frew >=4char$ )],
270 See L<DBIx::Class::Schema/populate>
272 I use populate L<here|http://blog.afoolishmanifesto.com/archives/1255> to export our whole
277 Create an object and its related objects all at once
279 $schema->resultset('Author')->create({
280 name => 'Stephen King',
281 books => [{ title => 'The Dark Tower' }],
283 street => '123 Turtle Back Lane',
284 state => { abbreviation => 'ME' },
285 city => { name => 'Lowell' },
289 See L<DBIx::Class::ResultSet/create>
293 =item books is a has_many
295 =item address is a belongs_to which in turn belongs to state and city each
297 =item for this to work right state and city must mark abbreviation and name as unique
303 DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made to
304 allow extensions to nearly every part of it.
306 =head2 Extensibility example: DBIx::Class::Helpers
310 =item L<DBIx::Class::Helper::ResultSet::IgnoreWantarray>
312 =item L<DBIx::Class::Helper::ResultSet::Random>
314 =item L<DBIx::Class::Helper::ResultSet::SetOperations>
316 =item L<DBIx::Class::Helper::Row::JoinTable>
318 =item L<DBIx::Class::Helper::Row::NumifyGet>
320 =item L<DBIx::Class::Helper::Row::SubClass>
322 =item L<DBIx::Class::Helper::Row::ToJSON>
324 =item L<DBIx::Class::Helper::Row::StorageValues>
326 =item L<DBIx::Class::Helper::Row::OnColumnChange>
330 =head2 Extensibility example: DBIx::Class::TimeStamp
334 =item See L<DBIx::Class::TimeStamp>
344 =head2 Extensibility example: Kioku
348 =item See L<DBIx::Class::Schema::KiokuDB>
350 =item Kioku is the new hotness
352 =item Mix RDBMS with Object DB
356 =head2 Result vs ResultSet
362 =item ResultSet == Query Plan
366 =item Internal Join Optimizer for all DB's (!!!)
370 =item (less important but...)
372 =item ResultSource == Queryable collection of rows (Table, View, etc)
374 =item Storage == Database
376 =item Schema == associates a set of ResultSources with a Storage
380 =head2 ResultSet methods
382 package MyApp::Schema::ResultSet::Book;
387 use base 'DBIx::Class::ResultSet';
392 $self->current_source_alias . '.rating' => { '>=' => 4 }
399 $self->current_source_alias . '.price' => { '<=' => 5}
407 See L<DBIx::Class::Manual::Cookbook/Predefined searches>
411 =item All searches should be ResultSet methods
413 =item Name has obvious meaning
415 =item L<DBIx::Class::ResultSet/current_source_alias> helps things to work no matter what
419 =head2 ResultSet method in Action
421 $schema->resultset('Book')->good
423 =head2 ResultSet Chaining
425 $schema->resultset('Book')
430 =head2 search_related
432 my $score = $schema->resultset('User')
433 ->search({'me.userid' => 'frew'})
434 ->related_resultset('access')
435 ->related_resultset('mgmt')
436 ->related_resultset('orders')
438 ->search_related( shops => {
439 'shops.datecompleted' => {
440 -between => ['2009-10-01','2009-10-08']
443 ->related_resultset('rpt_score')
444 ->search(undef, { rows => 1})
445 ->get_column('raw_scores')
448 The SQL that this produces (with placeholders filled in for clarity's sake)
449 on our system (Microsoft SQL) is:
453 SELECT raw_scores, ROW_NUMBER() OVER (
459 SELECT rpt_score.raw_scores
462 ON access.userid = me.userid
464 ON mgmt.mgmtid = access.mgmtid
466 ON orders.mgmtid = mgmt.mgmtid
468 ON shops.orderno = orders.orderno
469 JOIN rpt_scores rpt_score
470 ON rpt_score.shopno = shops.shopno
472 datecompleted IS NOT NULL AND
474 (shops.datecompleted BETWEEN '2009-10-01' AND '2009-10-08') AND
475 (type = '1' AND me.userid = 'frew')
480 WHERE rno__row__index BETWEEN 1 AND 1
482 See: L<DBIx::Class::ResultSet/related_resultset>,
483 L<DBIx::Class::ResultSet/search_related>, and
484 L<DBIx::Class::ResultSet/get_column>.
486 =head2 bonus rel methods
488 my $book = $author->create_related(
490 title => 'Another Discworld book',
494 my $book2 = $pratchett->add_to_books({
495 title => 'MOAR Discworld book',
498 See L<DBIx::Class::Relationship::Base/create_related> and L<DBIx::Class::Relationship::Base/add_to_$rel>
500 Note that it automatically fills in foreign key for you
502 =head2 Excellent Transaction Support
504 $schema->txn_do(sub {
508 $schema->txn_begin; # <-- low level
512 See L<DBIx::Class::Schema/txn_do>, L<DBIx::Class::Schema/txn_begin>,
513 and L<DBIx::Class::Schema/txn_commit>.
517 package Frew::Schema::Result::Book;
522 use base 'DBIx::Class::Core';
524 use DateTime::Format::MySQL;
528 __PACKAGE__->load_components('InflateColumn');
530 __PACKAGE__->inflate_column(
532 inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
533 deflate => sub { shift->ymd },
537 See L<DBIx::Class::InflateColumn>, L<DBIx::Class::InflateColumn/inflate_column>, and
538 L<DBIx::Class::InflateColumn::DateTime>.
540 =head2 InflateColumn: deflation
542 $book->date_published(DateTime->now);
545 =head2 InflateColumn: inflation
547 say $book->date_published->month_abbr; # Nov
551 package Frew::Schema::Result::Book;
556 use base 'DBIx::Class::Core';
560 __PACKAGE__->load_components('FilterColumn');
562 __PACKAGE__->filter_column(
564 to_storage => 'to_metric',
565 from_storage => 'to_imperial',
569 sub to_metric { $_[1] * .305 }
570 sub to_imperial { $_[1] * 3.28 }
572 See L<DBIx::Class::FilterColumn> and L<DBIx::Class::FilterColumn/filter_column>
574 =head2 ResultSetColumn
576 my $rsc = $schema->resultset('Book')->get_column('price');
583 See L<DBIx::Class::ResultSetColumn>
587 my @res = $rs->search(undef, {
595 qw(price genre max_price avg_price)
597 group_by => [qw(price genre)],
600 say $_->price . ' ' . $_->genre;
601 say $_->get_column('max_price');
602 say $_->get_column('avg_price');
605 See L<DBIx::Class::ResultSet/select>, L<DBIx::Class::ResultSet/as>, and
606 L<DBIx::Class::ResultSet/group_by>
610 =item Careful, get_column can basically mean B<three> things
612 =item private in which case you should use an accessor
614 =item public for what there is no accessor for
616 =item public for get resultset column (prev example)
623 result_class => 'DBIx::Class::ResultClass::HashRefInflator',
626 See L<DBIx::Class::ResultSet/result_class> and L<DBIx::Class::ResultClass::HashRefInflator>.
634 =item Great for quick debugging
636 =item Great for performance tuning (we went from 2m to < 3s)
640 =head2 Subquery Support
642 my $inner_query = $schema->resultset('Artist')
644 name => [ 'Billy Joel', 'Brittany Spears' ],
645 })->get_column('id')->as_query;
647 my $rs = $schema->resultset('CD')->search({
648 artist_id => { -in => $inner_query },
651 See L<DBIx::Class::Manual::Cookbook/Subqueries>
653 =head2 Bare SQL w/ Placeholders
656 # !!! SQL INJECTION VECTOR
657 price => \"price + $inc", # DON'T DO THIS
663 price => \['price + ?', [inc => $inc]],
666 See L<SQL::Abstract/Literal SQL with placeholders and bind values (subqueries)>
668 =head1 FURTHER QUESTIONS?
670 Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
672 =head1 COPYRIGHT AND LICENSE
674 This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
675 by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
676 redistribute it and/or modify it under the same terms as the
677 L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.