3 DBIx::Class::Manual::Features - A boatload of DBIC features with links to respective documentation
9 Currently there are 88 people listed as contributors to DBIC. That ranges
10 from documentation help, to test help, to added features, to entire database
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
33 L<DBIC|DBIx::Class> over raw SQL.
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 DBIC Specific Features
221 These things may be in other ORM's, but they are very specific, so doubtful
225 Create a database from your L<DBIC schema|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 L<DBIC schema|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 quicky 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 it's 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 L<DBIC|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>, L<DBIx::ClassResultSet/search_related>, and L<DBIx::Class::ResultSet/get_column>.
484 =head2 bonus rel methods
486 my $book = $author->create_related(
488 title => 'Another Discworld book',
492 my $book2 = $pratchett->add_to_books({
493 title => 'MOAR Discworld book',
496 See L<DBIx::Class::Relationship::Base/create_related> and L<DBIx::Class::Relationship::Base/add_to_$rel>
498 Note that it automatically fills in foreign key for you
500 =head2 Excellent Transaction Support
502 $schema->txn_do(sub {
506 $schema->txn_begin; # <-- low level
510 See L<DBIx::Class::Schema/txn_do>, L<DBIx::Class::Schema/txn_begin>,
511 and L<DBIx::Class::Schema/txn_commit>.
515 package Frew::Schema::Result::Book;
520 use base 'DBIx::Class::Core';
522 use DateTime::Format::MySQL;
526 __PACKAGE__->load_components('InflateColumn');
528 __PACKAGE__->inflate_column(
530 inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
531 deflate => sub { shift->ymd },
535 See L<DBIx::Class::InflateColumn>, L<DBIx::Class::InflateColumn/inflate_column>, and
536 L<DBIx::Class::InflateColumn::DateTime>.
538 =head2 InflateColumn: deflation
540 $book->date_published(DateTime->now);
543 =head2 InflateColumn: inflation
545 say $book->date_published->month_abbr; # Nov
549 package Frew::Schema::Result::Book;
554 use base 'DBIx::Class::Core';
558 __PACKAGE__->load_components('FilterColumn');
560 __PACKAGE__->filter_column(
562 to_storage => 'to_metric',
563 from_storage => 'to_imperial',
567 sub to_metric { $_[1] * .305 }
568 sub to_imperial { $_[1] * 3.28 }
570 See L<DBIx::Class::FilterColumn> and L<DBIx::Class::FilterColumn/filter_column>
572 =head2 ResultSetColumn
574 my $rsc = $schema->resultset('Book')->get_column('price');
581 See L<DBIx::Class::ResultSetColumn>
585 my @res = $rs->search(undef, {
593 qw(price genre max_price avg_price)
595 group_by => [qw(price genre)],
598 say $_->price . ' ' . $_->genre;
599 say $_->get_column('max_price');
600 say $_->get_column('avg_price');
603 See L<DBIx::Class::ResultSet/select>, L<DBIx::Class::ResultSet/as>, and
604 L<DBIx::Class::ResultSet/group_by>
608 =item Careful, get_column can basicaly mean B<three> things
610 =item private in which case you should use an accessor
612 =item public for what there is no accessor for
614 =item public for get resultset column (prev example)
621 result_class => 'DBIx::Class::ResultClass::HashRefInflator',
624 See L<DBIx::Class::ResultSet/result_class> and L<DBIx::Class::ResultClass::HashRefInflator>.
632 =item Great for quick debugging
634 =item Great for performance tuning (we went from 2m to < 3s)
638 =head2 Subquery Support
640 my $inner_query = $schema->resultset('Artist')
642 name => [ 'Billy Joel', 'Brittany Spears' ],
643 })->get_column('id')->as_query;
645 my $rs = $schema->resultset('CD')->search({
646 artist_id => { -in => $inner_query },
649 See L<DBIx::Class::Manual::Cookbook/Subqueries>
651 =head2 Bare SQL w/ Placeholders
654 # !!! SQL INJECTION VECTOR
655 price => \"price + $inc", # DON'T DO THIS
661 price => \['price + ?', [inc => $inc]],
664 See L<SQL::Abstract/Literal_SQL_with_placeholders_and_bind_values_(subqueries)>