_execute throws execptions as opposed to returning undef, so check errors that way
[dbsrgits/DBIx-Class.git] / lib / DBIx / Class / Manual / Cookbook.pod
CommitLineData
3b44ccc6 1=head1 NAME
9c82c181 2
40dbc108 3DBIx::Class::Manual::Cookbook - Miscellaneous recipes
ee38fa40 4
40dbc108 5=head1 RECIPES
2913b2d3 6
bade79c4 7=head2 Searching
faf62551 8
bade79c4 9=head3 Paged results
faf62551 10
bade79c4 11When you expect a large number of results, you can ask L<DBIx::Class> for a
12paged resultset, which will fetch only a small number of records at a time:
faf62551 13
bade79c4 14 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 15 undef,
bade79c4 16 {
17 page => 1, # page to return (defaults to 1)
18 rows => 10, # number of results per page
19 },
20 );
faf62551 21
bade79c4 22 return $rs->all(); # all records for page 1
faf62551 23
bade79c4 24The C<page> attribute does not have to be specified in your search:
25
26 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 27 undef,
bade79c4 28 {
29 rows => 10,
30 }
31 );
faf62551 32
bade79c4 33 return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
faf62551 34
35In either of the above cases, you can return a L<Data::Page> object for the
bade79c4 36resultset (suitable for use in e.g. a template) using the C<pager> method:
faf62551 37
bade79c4 38 return $rs->pager();
faf62551 39
bade79c4 40=head3 Complex WHERE clauses
2913b2d3 41
40dbc108 42Sometimes you need to formulate a query using specific operators:
43
ea6309e2 44 my @albums = $schema->resultset('Album')->search({
35d4fe78 45 artist => { 'like', '%Lamb%' },
46 title => { 'like', '%Fear of Fours%' },
47 });
40dbc108 48
49This results in something like the following C<WHERE> clause:
50
35d4fe78 51 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
40dbc108 52
53Other queries might require slightly more complex logic:
54
ea6309e2 55 my @albums = $schema->resultset('Album')->search({
35d4fe78 56 -or => [
57 -and => [
58 artist => { 'like', '%Smashing Pumpkins%' },
59 title => 'Siamese Dream',
60 ],
61 artist => 'Starchildren',
62 ],
63 });
40dbc108 64
65This results in the following C<WHERE> clause:
66
35d4fe78 67 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
68 OR artist = 'Starchildren'
40dbc108 69
70For more information on generating complex queries, see
71L<SQL::Abstract/WHERE CLAUSES>.
ee38fa40 72
321d9634 73=head3 Arbitrary SQL through a custom ResultSource
74
75Sometimes you have to run arbitrary SQL because your query is too complex
76(e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to
77be optimized for your database in a special way, but you still want to
78get the results as a L<DBIx::Class::ResultSet>.
79The recommended way to accomplish this is by defining a separate ResultSource
80for your query. You can then inject complete SQL statements using a scalar
81reference (this is a feature of L<SQL::Abstract>).
82
83Say you want to run a complex custom query on your user data, here's what
84you have to add to your User class:
85
86 package My::Schema::User;
87
88 use base qw/DBIx::Class/;
89
90 # ->load_components, ->table, ->add_columns, etc.
91
92 # Make a new ResultSource based on the User class
93 my $source = __PACKAGE__->result_source_instance();
94 my $new_source = $source->new( $source );
95 $new_source->source_name( 'UserFriendsComplex' );
96
97 # Hand in your query as a scalar reference
98 # It will be added as a sub-select after FROM,
99 # so pay attention to the surrounding brackets!
100 $new_source->name( \<<SQL );
101 ( SELECT u.* FROM user u
102 INNER JOIN user_friends f ON u.id = f.user_id
103 WHERE f.friend_user_id = ?
104 UNION
105 SELECT u.* FROM user u
106 INNER JOIN user_friends f ON u.id = f.friend_user_id
107 WHERE f.user_id = ? )
108 SQL
109
110 # Finally, register your new ResultSource with your Schema
111 My::Schema->register_source( 'UserFriendsComplex' => $new_source );
112
113Next, you can execute your complex query using bind parameters like this:
114
115 my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {},
116 {
117 bind => [ 12345, 12345 ]
118 }
119 ) ];
120
121... and you'll get back a perfect L<DBIx::Class::ResultSet>.
122
bade79c4 123=head3 Using specific columns
faf62551 124
324572ca 125When you only want specific columns from a table, you can use
126C<columns> to specify which ones you need. This is useful to avoid
127loading columns with large amounts of data that you aren't about to
128use anyway:
faf62551 129
bade79c4 130 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 131 undef,
bade79c4 132 {
5e8b1b2a 133 columns => [qw/ name /]
bade79c4 134 }
135 );
faf62551 136
bade79c4 137 # Equivalent SQL:
138 # SELECT artist.name FROM artist
faf62551 139
324572ca 140This is a shortcut for C<select> and C<as>, see below. C<columns>
141cannot be used together with C<select> and C<as>.
142
bade79c4 143=head3 Using database functions or stored procedures
faf62551 144
bade79c4 145The combination of C<select> and C<as> can be used to return the result of a
146database function or stored procedure as a column value. You use C<select> to
147specify the source for your column value (e.g. a column name, function, or
148stored procedure name). You then use C<as> to set the column name you will use
149to access the returned value:
faf62551 150
bade79c4 151 my $rs = $schema->resultset('Artist')->search(
324572ca 152 {},
bade79c4 153 {
154 select => [ 'name', { LENGTH => 'name' } ],
155 as => [qw/ name name_length /],
156 }
157 );
faf62551 158
bade79c4 159 # Equivalent SQL:
98b65433 160 # SELECT name name, LENGTH( name )
bade79c4 161 # FROM artist
faf62551 162
d676881f 163Note that the C< as > attribute has absolutely nothing to with the sql
164syntax C< SELECT foo AS bar > (see the documentation in
165L<DBIx::Class::ResultSet/ATTRIBUTES>). If your alias exists as a
166column in your base class (i.e. it was added with C<add_columns>), you
167just access it as normal. Our C<Artist> class has a C<name> column, so
168we just use the C<name> accessor:
faf62551 169
bade79c4 170 my $artist = $rs->first();
171 my $name = $artist->name();
faf62551 172
173If on the other hand the alias does not correspond to an existing column, you
324572ca 174have to fetch the value using the C<get_column> accessor:
faf62551 175
bade79c4 176 my $name_length = $artist->get_column('name_length');
faf62551 177
bade79c4 178If you don't like using C<get_column>, you can always create an accessor for
faf62551 179any of your aliases using either of these:
180
bade79c4 181 # Define accessor manually:
182 sub name_length { shift->get_column('name_length'); }
faf62551 183
bade79c4 184 # Or use DBIx::Class::AccessorGroup:
185 __PACKAGE__->mk_group_accessors('column' => 'name_length');
faf62551 186
bade79c4 187=head3 SELECT DISTINCT with multiple columns
faf62551 188
bade79c4 189 my $rs = $schema->resultset('Foo')->search(
324572ca 190 {},
bade79c4 191 {
192 select => [
193 { distinct => [ $source->columns ] }
194 ],
d676881f 195 as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-)
bade79c4 196 }
197 );
faf62551 198
286f32b3 199 my $count = $rs->next->get_column('count');
200
bade79c4 201=head3 SELECT COUNT(DISTINCT colname)
6607ee1b 202
bade79c4 203 my $rs = $schema->resultset('Foo')->search(
324572ca 204 {},
bade79c4 205 {
206 select => [
207 { count => { distinct => 'colname' } }
208 ],
209 as => [ 'count' ]
210 }
211 );
6607ee1b 212
bade79c4 213=head3 Grouping results
214
215L<DBIx::Class> supports C<GROUP BY> as follows:
216
217 my $rs = $schema->resultset('Artist')->search(
324572ca 218 {},
bade79c4 219 {
220 join => [qw/ cds /],
221 select => [ 'name', { count => 'cds.cdid' } ],
222 as => [qw/ name cd_count /],
223 group_by => [qw/ name /]
224 }
225 );
6607ee1b 226
bade79c4 227 # Equivalent SQL:
228 # SELECT name, COUNT( cds.cdid ) FROM artist me
229 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
230 # GROUP BY name
6607ee1b 231
d676881f 232Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
233are in any way unsure about the use of the attributes above (C< join
234>, C< select >, C< as > and C< group_by >).
235
74dc2edc 236=head3 Predefined searches
237
324572ca 238You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
74dc2edc 239and define often used searches as methods:
240
241 package My::DBIC::ResultSet::CD;
242 use strict;
243 use warnings;
244 use base 'DBIx::Class::ResultSet';
245
246 sub search_cds_ordered {
247 my ($self) = @_;
248
249 return $self->search(
250 {},
251 { order_by => 'name DESC' },
252 );
253 }
254
255 1;
256
257To use your resultset, first tell DBIx::Class to create an instance of it
258for you, in your My::DBIC::Schema::CD class:
259
260 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
261
262Then call your new method in your code:
263
264 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
265
266
d6ed6a67 267=head3 Predefined searches without writing a ResultSet class
268
269Alternatively you can automatically generate a DBIx::Class::ResultSet
270class by using the ResultSetManager component and tagging your method
271as ResultSet:
272
273 __PACKAGE__->load_components(qw/ ResultSetManager Core /);
274
275 sub search_cds_ordered : ResultSet {
276 my ($self) = @_;
277 return $self->search(
278 {},
279 { order_by => 'name DESC' },
280 );
281 }
282
283Then call your method in the same way from your code:
284
285 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
286
87980de7 287=head2 Using joins and prefetch
288
bade79c4 289You can use the C<join> attribute to allow searching on, or sorting your
290results by, one or more columns in a related table. To return all CDs matching
291a particular artist name:
ea6309e2 292
bade79c4 293 my $rs = $schema->resultset('CD')->search(
294 {
295 'artist.name' => 'Bob Marley'
296 },
297 {
298 join => [qw/artist/], # join the artist table
299 }
300 );
301
302 # Equivalent SQL:
303 # SELECT cd.* FROM cd
304 # JOIN artist ON cd.artist = artist.id
305 # WHERE artist.name = 'Bob Marley'
306
307If required, you can now sort on any column in the related tables by including
308it in your C<order_by> attribute:
309
310 my $rs = $schema->resultset('CD')->search(
311 {
312 'artist.name' => 'Bob Marley'
313 },
314 {
315 join => [qw/ artist /],
316 order_by => [qw/ artist.name /]
317 }
318 };
ea6309e2 319
bade79c4 320 # Equivalent SQL:
321 # SELECT cd.* FROM cd
322 # JOIN artist ON cd.artist = artist.id
323 # WHERE artist.name = 'Bob Marley'
324 # ORDER BY artist.name
ea6309e2 325
bade79c4 326Note that the C<join> attribute should only be used when you need to search or
327sort using columns in a related table. Joining related tables when you only
328need columns from the main table will make performance worse!
ea6309e2 329
bade79c4 330Now let's say you want to display a list of CDs, each with the name of the
331artist. The following will work fine:
ea6309e2 332
bade79c4 333 while (my $cd = $rs->next) {
334 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
335 }
ea6309e2 336
bade79c4 337There is a problem however. We have searched both the C<cd> and C<artist> tables
338in our main query, but we have only returned data from the C<cd> table. To get
339the artist name for any of the CD objects returned, L<DBIx::Class> will go back
340to the database:
ea6309e2 341
bade79c4 342 SELECT artist.* FROM artist WHERE artist.id = ?
ea6309e2 343
344A statement like the one above will run for each and every CD returned by our
345main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
346queries!
347
bade79c4 348Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
897342e4 349This allows you to fetch results from related tables in advance:
ea6309e2 350
bade79c4 351 my $rs = $schema->resultset('CD')->search(
352 {
353 'artist.name' => 'Bob Marley'
354 },
355 {
356 join => [qw/ artist /],
357 order_by => [qw/ artist.name /],
358 prefetch => [qw/ artist /] # return artist data too!
359 }
360 );
ea6309e2 361
bade79c4 362 # Equivalent SQL (note SELECT from both "cd" and "artist"):
363 # SELECT cd.*, artist.* FROM cd
364 # JOIN artist ON cd.artist = artist.id
365 # WHERE artist.name = 'Bob Marley'
366 # ORDER BY artist.name
ea6309e2 367
368The code to print the CD list remains the same:
369
bade79c4 370 while (my $cd = $rs->next) {
371 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
372 }
ea6309e2 373
bade79c4 374L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
ea6309e2 375so no additional SQL statements are executed. You now have a much more
376efficient query.
377
77d6b403 378Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
379C<has_many> relationships.
ea6309e2 380
bade79c4 381Also note that C<prefetch> should only be used when you know you will
ea6309e2 382definitely use data from a related table. Pre-fetching related tables when you
383only need columns from the main table will make performance worse!
384
bade79c4 385=head3 Multi-step joins
ea6309e2 386
387Sometimes you want to join more than one relationship deep. In this example,
bade79c4 388we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
389contain a specific string:
390
391 # Relationships defined elsewhere:
392 # Artist->has_many('cds' => 'CD', 'artist');
393 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
394
395 my $rs = $schema->resultset('Artist')->search(
396 {
397 'liner_notes.notes' => { 'like', '%some text%' },
398 },
399 {
400 join => {
401 'cds' => 'liner_notes'
402 }
403 }
404 );
ea6309e2 405
bade79c4 406 # Equivalent SQL:
407 # SELECT artist.* FROM artist
408 # JOIN ( cd ON artist.id = cd.artist )
409 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
410 # WHERE liner_notes.notes LIKE '%some text%'
ea6309e2 411
412Joins can be nested to an arbitrary level. So if we decide later that we
413want to reduce the number of Artists returned based on who wrote the liner
414notes:
415
bade79c4 416 # Relationship defined elsewhere:
417 # LinerNotes->belongs_to('author' => 'Person');
418
419 my $rs = $schema->resultset('Artist')->search(
420 {
421 'liner_notes.notes' => { 'like', '%some text%' },
422 'author.name' => 'A. Writer'
423 },
424 {
425 join => {
426 'cds' => {
427 'liner_notes' => 'author'
ea6309e2 428 }
bade79c4 429 }
430 }
431 );
ea6309e2 432
bade79c4 433 # Equivalent SQL:
434 # SELECT artist.* FROM artist
435 # JOIN ( cd ON artist.id = cd.artist )
436 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
437 # JOIN ( author ON author.id = liner_notes.author )
438 # WHERE liner_notes.notes LIKE '%some text%'
439 # AND author.name = 'A. Writer'
87980de7 440
897342e4 441=head2 Multi-step prefetch
442
443From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
444deep using the same syntax as a multi-step join:
445
446 my $rs = $schema->resultset('Tag')->search(
ac2803ef 447 {},
897342e4 448 {
449 prefetch => {
450 cd => 'artist'
451 }
452 }
453 );
454
455 # Equivalent SQL:
456 # SELECT tag.*, cd.*, artist.* FROM tag
457 # JOIN cd ON tag.cd = cd.cdid
458 # JOIN artist ON cd.artist = artist.artistid
459
460Now accessing our C<cd> and C<artist> relationships does not need additional
461SQL statements:
462
463 my $tag = $rs->first;
464 print $tag->cd->artist->name;
465
ac2803ef 466=head2 Columns of data
467
468If you want to find the sum of a particular column there are several
469ways, the obvious one is to use search:
470
471 my $rs = $schema->resultset('Items')->search(
472 {},
473 {
474 select => [ { sum => 'Cost' } ],
d676881f 475 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL
ac2803ef 476 }
477 );
478 my $tc = $rs->first->get_column('total_cost');
479
480Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets
481returned when you ask the C<ResultSet> for a column using
482C<get_column>:
483
484 my $cost = $schema->resultset('Items')->get_column('Cost');
485 my $tc = $cost->sum;
486
487With this you can also do:
488
489 my $minvalue = $cost->min;
490 my $maxvalue = $cost->max;
491
492Or just iterate through the values of this column only:
493
494 while ( my $c = $cost->next ) {
495 print $c;
496 }
497
498 foreach my $c ($cost->all) {
499 print $c;
500 }
501
709353af 502C<ResultSetColumn> only has a limited number of built-in functions, if
503you need one that it doesn't have, then you can use the C<func> method
504instead:
505
506 my $avg = $cost->func('AVERAGE');
507
508This will cause the following SQL statement to be run:
509
510 SELECT AVERAGE(Cost) FROM Items me
511
512Which will of course only work if your database supports this function.
ac2803ef 513See L<DBIx::Class::ResultSetColumn> for more documentation.
514
acee4e4d 515=head2 Using relationships
516
517=head3 Create a new row in a related table
518
519 my $book->create_related('author', { name => 'Fred'});
520
521=head3 Search in a related table
522
523Only searches for books named 'Titanic' by the author in $author.
524
525 my $author->search_related('books', { name => 'Titanic' });
526
527=head3 Delete data in a related table
528
529Deletes only the book named Titanic by the author in $author.
530
531 my $author->delete_related('books', { name => 'Titanic' });
532
f8bad769 533=head3 Ordering a relationship result set
534
535If you always want a relation to be ordered, you can specify this when you
536create the relationship.
537
538To order C<< $book->pages >> by descending page_number.
539
540 Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
541
542
543
87980de7 544=head2 Transactions
545
546As of version 0.04001, there is improved transaction support in
85f78622 547L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
181a28f4 548example of the recommended way to use it:
87980de7 549
181a28f4 550 my $genus = $schema->resultset('Genus')->find(12);
551
70634260 552 my $coderef2 = sub {
553 $genus->extinct(1);
554 $genus->update;
555 };
556
181a28f4 557 my $coderef1 = sub {
35d4fe78 558 $genus->add_to_species({ name => 'troglodyte' });
559 $genus->wings(2);
560 $genus->update;
70634260 561 $schema->txn_do($coderef2); # Can have a nested transaction
181a28f4 562 return $genus->species;
563 };
564
181a28f4 565 my $rs;
566 eval {
70634260 567 $rs = $schema->txn_do($coderef1);
181a28f4 568 };
569
570 if ($@) { # Transaction failed
571 die "the sky is falling!" #
572 if ($@ =~ /Rollback failed/); # Rollback failed
573
574 deal_with_failed_transaction();
35d4fe78 575 }
87980de7 576
181a28f4 577Nested transactions will work as expected. That is, only the outermost
578transaction will actually issue a commit to the $dbh, and a rollback
579at any level of any transaction will cause the entire nested
580transaction to fail. Support for savepoints and for true nested
40dbc108 581transactions (for databases that support them) will hopefully be added
582in the future.
ee38fa40 583
130c6439 584=head2 Many-to-many relationships
ee38fa40 585
787d6a29 586This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
ea6309e2 587
bade79c4 588 package My::DB;
589 # ... set up connection ...
590
591 package My::User;
592 use base 'My::DB';
593 __PACKAGE__->table('user');
594 __PACKAGE__->add_columns(qw/id name/);
595 __PACKAGE__->set_primary_key('id');
596 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
597 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
598
599 package My::UserAddress;
600 use base 'My::DB';
601 __PACKAGE__->table('user_address');
602 __PACKAGE__->add_columns(qw/user address/);
603 __PACKAGE__->set_primary_key(qw/user address/);
604 __PACKAGE__->belongs_to('user' => 'My::User');
605 __PACKAGE__->belongs_to('address' => 'My::Address');
606
607 package My::Address;
608 use base 'My::DB';
609 __PACKAGE__->table('address');
610 __PACKAGE__->add_columns(qw/id street town area_code country/);
611 __PACKAGE__->set_primary_key('id');
612 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
613 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
614
615 $rs = $user->addresses(); # get all addresses for a user
616 $rs = $address->users(); # get all users for an address
617
618=head2 Setting default values for a row
a00e1684 619
35d4fe78 620It's as simple as overriding the C<new> method. Note the use of
40dbc108 621C<next::method>.
a00e1684 622
35d4fe78 623 sub new {
624 my ( $class, $attrs ) = @_;
40dbc108 625
35d4fe78 626 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
40dbc108 627
df65edd2 628 my $new = $class->next::method($attrs);
8b50216e 629
df65edd2 630 return $new;
35d4fe78 631 }
a00e1684 632
4a3c6354 633For more information about C<next::method>, look in the L<Class::C3>
634documentation. See also L<DBIx::Class::Manual::Component> for more
635ways to write your own base classes to do this.
636
637People looking for ways to do "triggers" with DBIx::Class are probably
638just looking for this.
639
25af00d7 640=head2 Stringification
641
40dbc108 642Employ the standard stringification technique by using the C<overload>
462bb847 643module.
644
645To make an object stringify itself as a single column, use something
646like this (replace C<foo> with the column/method of your choice):
25af00d7 647
0472cc04 648 use overload '""' => sub { shift->name}, fallback => 1;
25af00d7 649
462bb847 650For more complex stringification, you can use an anonymous subroutine:
651
652 use overload '""' => sub { $_[0]->name . ", " .
653 $_[0]->address }, fallback => 1;
654
324572ca 655=head3 Stringification Example
462bb847 656
657Suppose we have two tables: C<Product> and C<Category>. The table
658specifications are:
659
660 Product(id, Description, category)
661 Category(id, Description)
662
663C<category> is a foreign key into the Category table.
664
665If you have a Product object C<$obj> and write something like
666
667 print $obj->category
668
669things will not work as expected.
670
671To obtain, for example, the category description, you should add this
672method to the class defining the Category table:
673
674 use overload "" => sub {
675 my $self = shift;
676
677 return $self->Description;
77713550 678 }, fallback => 1;
462bb847 679
bade79c4 680=head2 Disconnecting cleanly
681
682If you find yourself quitting an app with Control-C a lot during
683development, you might like to put the following signal handler in
684your main database class to make sure it disconnects cleanly:
685
686 $SIG{INT} = sub {
6d1bf0a9 687 __PACKAGE__->storage->disconnect;
bade79c4 688 };
689
362500af 690=head2 Schema import/export
691
692This functionality requires you to have L<SQL::Translator> (also known as
693"SQL Fairy") installed.
694
695To create a DBIx::Class schema from an existing database:
696
697 sqlt --from DBI
698 --to DBIx::Class::File
699 --prefix "MySchema" > MySchema.pm
700
701To create a MySQL database from an existing L<DBIx::Class> schema, convert the
702schema to MySQL's dialect of SQL:
703
54c30987 704 sqlt --from SQL::Translator::Parser::DBIx::Class
705 --to MySQL
706 --DBIx::Class "MySchema.pm" > Schema1.sql
362500af 707
708And import using the mysql client:
709
710 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
711
b0a20454 712=head2 Easy migration from class-based to schema-based setup
713
714You want to start using the schema-based approach to L<DBIx::Class>
715(see L<SchemaIntro.pod>), but have an established class-based setup with lots
716of existing classes that you don't want to move by hand. Try this nifty script
717instead:
718
719 use MyDB;
720 use SQL::Translator;
721
722 my $schema = MyDB->schema_instance;
723
724 my $translator = SQL::Translator->new(
725 debug => $debug || 0,
726 trace => $trace || 0,
727 no_comments => $no_comments || 0,
728 show_warnings => $show_warnings || 0,
729 add_drop_table => $add_drop_table || 0,
730 validate => $validate || 0,
731 parser_args => {
732 'DBIx::Schema' => $schema,
c5f36986 733 },
b0a20454 734 producer_args => {
735 'prefix' => 'My::Schema',
c5f36986 736 },
b0a20454 737 );
738
d240abac 739 $translator->parser('SQL::Translator::Parser::DBIx::Class');
740 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
b0a20454 741
742 my $output = $translator->translate(@args) or die
743 "Error: " . $translator->error;
744
745 print $output;
746
747You could use L<Module::Find> to search for all subclasses in the MyDB::*
880a1a0c 748namespace, which is currently left as an exercise for the reader.
b0a20454 749
362500af 750=head2 Schema versioning
751
752The following example shows simplistically how you might use DBIx::Class to
753deploy versioned schemas to your customers. The basic process is as follows:
754
da4779ad 755=over 4
756
757=item 1.
758
759Create a DBIx::Class schema
760
761=item 2.
762
763Save the schema
764
765=item 3.
766
767Deploy to customers
768
769=item 4.
770
771Modify schema to change functionality
772
773=item 5.
774
775Deploy update to customers
776
777=back
362500af 778
779=head3 Create a DBIx::Class schema
780
781This can either be done manually, or generated from an existing database as
782described under C<Schema import/export>.
783
784=head3 Save the schema
785
786Use C<sqlt> to transform your schema into an SQL script suitable for your
787customer's database. E.g. for MySQL:
788
54c30987 789 sqlt --from SQL::Translator::Parser::DBIx::Class
362500af 790 --to MySQL
791 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
792
793If you need to target databases from multiple vendors, just generate an SQL
794script suitable for each. To support PostgreSQL too:
795
54c30987 796 sqlt --from SQL::Translator::DBIx::Class
362500af 797 --to PostgreSQL
798 --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
799
800=head3 Deploy to customers
801
802There are several ways you could deploy your schema. These are probably
803beyond the scope of this recipe, but might include:
804
da4779ad 805=over 4
806
807=item 1.
808
809Require customer to apply manually using their RDBMS.
810
811=item 2.
812
813Package along with your app, making database dump/schema update/tests
362500af 814all part of your install.
815
da4779ad 816=back
817
362500af 818=head3 Modify the schema to change functionality
819
820As your application evolves, it may be necessary to modify your schema to
821change functionality. Once the changes are made to your schema in DBIx::Class,
822export the modified schema as before, taking care not to overwrite the original:
823
54c30987 824 sqlt --from SQL::Translator::DBIx::Class
362500af 825 --to MySQL
826 --DBIx::Class "Anything.pm" > Schema2.mysql.sql
827
828Next, use sqlt-diff to create an SQL script that will update the customer's
829database schema:
830
831 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
832
833=head3 Deploy update to customers
834
835The schema update can be deployed to customers using the same method as before.
836
7be93b07 837=head2 Setting limit dialect for SQL::Abstract::Limit
838
324572ca 839In some cases, SQL::Abstract::Limit cannot determine the dialect of
840the remote SQL server by looking at the database handle. This is a
841common problem when using the DBD::JDBC, since the DBD-driver only
842know that in has a Java-driver available, not which JDBC driver the
843Java component has loaded. This specifically sets the limit_dialect
844to Microsoft SQL-server (See more names in SQL::Abstract::Limit
845-documentation.
7be93b07 846
847 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
848
324572ca 849The JDBC bridge is one way of getting access to a MSSQL server from a platform
7be93b07 850that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
851
324572ca 852=head2 Setting quoting for the generated SQL.
2437a1e3 853
324572ca 854If the database contains column names with spaces and/or reserved words, they
855need to be quoted in the SQL queries. This is done using:
2437a1e3 856
857 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
858 __PACKAGE__->storage->sql_maker->name_sep('.');
859
324572ca 860The first sets the quote characters. Either a pair of matching
861brackets, or a C<"> or C<'>:
2437a1e3 862
863 __PACKAGE__->storage->sql_maker->quote_char('"');
864
324572ca 865Check the documentation of your database for the correct quote
866characters to use. C<name_sep> needs to be set to allow the SQL
867generator to put the quotes the correct place.
2437a1e3 868
086b93a2 869=head2 Overloading methods
870
871L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
872method calls. You have to use calls to C<next::method> to overload methods.
873More information on using L<Class::C3> with L<DBIx::Class> can be found in
874L<DBIx::Class::Manual::Component>.
875
876=head3 Changing one field whenever another changes
877
878For example, say that you have three columns, C<id>, C<number>, and
879C<squared>. You would like to make changes to C<number> and have
880C<squared> be automagically set to the value of C<number> squared.
881You can accomplish this by overriding C<store_column>:
882
883 sub store_column {
884 my ( $self, $name, $value ) = @_;
885 if ($name eq 'number') {
886 $self->squared($value * $value);
887 }
888 $self->next::method($name, $value);
889 }
890
891Note that the hard work is done by the call to C<next::method>, which
324572ca 892redispatches your call to store_column in the superclass(es).
086b93a2 893
894=head3 Automatically creating related objects
895
324572ca 896You might have a class C<Artist> which has many C<CD>s. Further, if you
086b93a2 897want to create a C<CD> object every time you insert an C<Artist> object.
ccbebdbc 898You can accomplish this by overriding C<insert> on your objects:
086b93a2 899
900 sub insert {
ccbebdbc 901 my ( $self, @args ) = @_;
902 $self->next::method(@args);
086b93a2 903 $self->cds->new({})->fill_from_artist($self)->insert;
904 return $self;
905 }
906
907where C<fill_from_artist> is a method you specify in C<CD> which sets
908values in C<CD> based on the data in the C<Artist> object you pass in.
909
1def3451 910=head2 Debugging DBIx::Class objects with Data::Dumper
911
912L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
913be hard to find the pertinent data in all the data it can generate.
914Specifically, if one naively tries to use it like so,
915
916 use Data::Dumper;
917
918 my $cd = $schema->resultset('CD')->find(1);
919 print Dumper($cd);
920
921several pages worth of data from the CD object's schema and result source will
922be dumped to the screen. Since usually one is only interested in a few column
923values of the object, this is not very helpful.
924
925Luckily, it is possible to modify the data before L<Data::Dumper> outputs
926it. Simply define a hook that L<Data::Dumper> will call on the object before
927dumping it. For example,
928
929 package My::DB::CD;
930
931 sub _dumper_hook {
99fb1058 932 $_[0] = bless {
933 %{ $_[0] },
1def3451 934 result_source => undef,
99fb1058 935 }, ref($_[0]);
1def3451 936 }
937
938 [...]
939
940 use Data::Dumper;
941
22139027 942 local $Data::Dumper::Freezer = '_dumper_hook';
1def3451 943
944 my $cd = $schema->resultset('CD')->find(1);
945 print Dumper($cd);
946 # dumps $cd without its ResultSource
947
948If the structure of your schema is such that there is a common base class for
949all your table classes, simply put a method similar to C<_dumper_hook> in the
950base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
951will automagically clean up your data before printing it. See
952L<Data::Dumper/EXAMPLES> for more information.
953
1def3451 954=head2 Retrieving a row object's Schema
955
324572ca 956It is possible to get a Schema object from a row object like so:
1def3451 957
958 my $schema = $cd->result_source->schema;
324572ca 959 # use the schema as normal:
960 my $artist_rs = $schema->resultset('Artist');
1def3451 961
962This can be useful when you don't want to pass around a Schema object to every
963method.
964
4c248161 965=head2 Profiling
966
85f78622 967When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL
4c248161 968executed as well as notifications of query completion and transaction
969begin/commit. If you'd like to profile the SQL you can subclass the
970L<DBIx::Class::Storage::Statistics> class and write your own profiling
971mechanism:
972
973 package My::Profiler;
974 use strict;
975
976 use base 'DBIx::Class::Storage::Statistics';
977
978 use Time::HiRes qw(time);
979
980 my $start;
981
982 sub query_start {
983 my $self = shift();
984 my $sql = shift();
985 my $params = @_;
986
987 print "Executing $sql: ".join(', ', @params)."\n";
988 $start = time();
989 }
990
991 sub query_end {
992 my $self = shift();
993 my $sql = shift();
994 my @params = @_;
995
996 printf("Execution took %0.4f seconds.\n", time() - $start);
997 $start = undef;
998 }
999
1000 1;
1001
1002You can then install that class as the debugging object:
1003
1004 __PACKAGE__->storage()->debugobj(new My::Profiler());
1005 __PACKAGE__->storage()->debug(1);
1006
1007A more complicated example might involve storing each execution of SQL in an
1008array:
1009
1010 sub query_end {
1011 my $self = shift();
1012 my $sql = shift();
1013 my @params = @_;
1014
1015 my $elapsed = time() - $start;
1016 push(@{ $calls{$sql} }, {
1017 params => \@params,
1018 elapsed => $elapsed
1019 });
1020 }
1021
1022You could then create average, high and low execution times for an SQL
1023statement and dig down to see if certain parameters cause aberrant behavior.
1024
e8e9e5c7 1025=head2 Getting the value of the primary key for the last database insert
1026
74413b83 1027AKA getting last_insert_id
1028
e8e9e5c7 1029If you are using PK::Auto, this is straightforward:
1030
74413b83 1031 my $foo = $rs->create(\%blah);
e8e9e5c7 1032 # do more stuff
1033 my $id = $foo->id; # foo->my_primary_key_field will also work.
1034
1035If you are not using autoincrementing primary keys, this will probably
1036not work, but then you already know the value of the last primary key anyway.
1037
824f4422 1038=head2 Dynamic Sub-classing DBIx::Class proxy classes
1039(AKA multi-class object inflation from one table)
1040
324572ca 1041L<DBIx::Class> classes are proxy classes, therefore some different
1042techniques need to be employed for more than basic subclassing. In
1043this example we have a single user table that carries a boolean bit
1044for admin. We would like like to give the admin users
1045objects(L<DBIx::Class::Row>) the same methods as a regular user but
1046also special admin only methods. It doesn't make sense to create two
1047seperate proxy-class files for this. We would be copying all the user
1048methods into the Admin class. There is a cleaner way to accomplish
1049this.
1050
c6d147b6 1051Overriding the C<inflate_result> method within the User proxy-class
324572ca 1052gives us the effect we want. This method is called by
1053L<DBIx::Class::ResultSet> when inflating a result from storage. So we
1054grab the object being returned, inspect the values we are looking for,
1055bless it if it's an admin object, and then return it. See the example
1056below:
824f4422 1057
1058B<Schema Definition>
1059
1060 package DB::Schema;
1061
1062 use base qw/DBIx::Class::Schema/;
1063
1064 __PACKAGE__->load_classes(qw/User/);
1065
1066
1067B<Proxy-Class definitions>
1068
1069 package DB::Schema::User;
1070
1071 use strict;
1072 use warnings;
1073 use base qw/DBIx::Class/;
1074
1075 ### Defined what our admin class is for ensure_class_loaded
1076 my $admin_class = __PACKAGE__ . '::Admin';
1077
324572ca 1078 __PACKAGE__->load_components(qw/Core/);
824f4422 1079
1080 __PACKAGE__->table('users');
1081
1082 __PACKAGE__->add_columns(qw/user_id email password
1083 firstname lastname active
1084 admin/);
1085
1086 __PACKAGE__->set_primary_key('user_id');
1087
1088 sub inflate_result {
1089 my $self = shift;
1090 my $ret = $self->next::method(@_);
1091 if( $ret->admin ) {### If this is an admin rebless for extra functions
1092 $self->ensure_class_loaded( $admin_class );
1093 bless $ret, $admin_class;
1094 }
1095 return $ret;
1096 }
1097
1098 sub hello {
1099 print "I am a regular user.\n";
1100 return ;
1101 }
1102
1103
1104 package DB::Schema::User::Admin;
1105
1106 use strict;
1107 use warnings;
1108 use base qw/DB::Schema::User/;
1109
1110 sub hello
1111 {
1112 print "I am an admin.\n";
1113 return;
1114 }
1115
1116 sub do_admin_stuff
1117 {
1118 print "I am doing admin stuff\n";
1119 return ;
1120 }
1121
1122B<Test File> test.pl
1123
1124 use warnings;
1125 use strict;
1126 use DB::Schema;
1127
1128 my $user_data = { email => 'someguy@place.com',
1129 password => 'pass1',
1130 admin => 0 };
1131
1132 my $admin_data = { email => 'someadmin@adminplace.com',
1133 password => 'pass2',
1134 admin => 1 };
1135
1136 my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
1137
1138 $schema->resultset('User')->create( $user_data );
1139 $schema->resultset('User')->create( $admin_data );
1140
1141 ### Now we search for them
1142 my $user = $schema->resultset('User')->single( $user_data );
1143 my $admin = $schema->resultset('User')->single( $admin_data );
1144
1145 print ref $user, "\n";
1146 print ref $admin, "\n";
1147
1148 print $user->password , "\n"; # pass1
1149 print $admin->password , "\n";# pass2; inherited from User
1150 print $user->hello , "\n";# I am a regular user.
1151 print $admin->hello, "\n";# I am an admin.
1152
1153 ### The statement below will NOT print
1154 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
1155 ### The statement below will print
1156 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
1157
fe5cf259 1158=head2 Skip object creation for faster results
1159
1160DBIx::Class is not built for speed, it's built for convenience and
1161ease of use, but sometimes you just need to get the data, and skip the
137c657c 1162fancy objects.
1163
1164To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
1165
1166 my $rs = $schema->resultset('CD');
1167
1168 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
1169
1170 my $hash_ref = $rs->find(1);
1171
1172Wasn't that easy?
1173
685dad64 1174=head2 Get raw data for blindingly fast results
1175
1176If the C<inflate_result> solution above is not fast enough for you, you
1177can use a DBIx::Class to return values exactly as they come out of the
1178data base with none of the convenience methods wrapped round them.
1179
1180This is used like so:-
1181
1182 my $cursor = $rs->cursor
1183 while (my @vals = $cursor->next) {
1184 # use $val[0..n] here
1185 }
1186
1187You will need to map the array offsets to particular columns (you can
1188use the I<select> attribute of C<search()> to force ordering).
1189
9e4c2514 1190=head2 Want to know if find_or_create found or created a row?
1191
1192Just use C<find_or_new> instead, then check C<in_storage>:
fe5cf259 1193
9e4c2514 1194 my $obj = $rs->find_or_new({ blah => 'blarg' });
1195 unless ($obj->in_storage) {
1196 $obj->insert;
1197 # do whatever else you wanted if it was a new row
1198 }
1199
7aaec96c 1200=head3 Wrapping/overloading a column accessor
1201
1202Problem: Say you have a table "Camera" and want to associate a description
1203with each camera. For most cameras, you'll be able to generate the description from
1204the other columns. However, in a few special cases you may want to associate a
1205custom description with a camera.
1206
1207Solution:
1208
1209In your database schema, define a description field in the "Camera" table that
1210can contain text and null values.
1211
1212In DBIC, we'll overload the column accessor to provide a sane default if no
1213custom description is defined. The accessor will either return or generate the
1214description, depending on whether the field is null or not.
1215
1216First, in your "Camera" schema class, define the description field as follows:
1217
1218 __PACKAGE__->add_columns(description => { accessor => '_description' });
1219
1220Next, we'll define the accessor-wrapper subroutine:
1221
1222 sub description {
1223 my $self = shift;
1224
1225 # If there is an update to the column, we'll let the original accessor
1226 # deal with it.
1227 return $self->_description(@_) if @_;
1228
1229 # Fetch the column value.
1230 my $description = $self->_description;
1231
1232 # If there's something in the description field, then just return that.
1233 return $description if defined $description && length $descripton;
1234
1235 # Otherwise, generate a description.
1236 return $self->generate_description;
1237 }
1238
40dbc108 1239=cut