Merge 'trunk' into 'DBIx-Class-current'
[dbsrgits/DBIx-Class-Historic.git] / lib / DBIx / Class / Manual / Cookbook.pod
1 =head1 NAME 
2
3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
4
5 =head1 RECIPES
6
7 =head2 Searching
8
9 =head3 Paged results
10
11 When you expect a large number of results, you can ask L<DBIx::Class> for a
12 paged resultset, which will fetch only a small number of records at a time:
13
14   my $rs = $schema->resultset('Artist')->search(
15     undef,
16     {
17       page => 1,  # page to return (defaults to 1)
18       rows => 10, # number of results per page
19     },
20   );
21
22   return $rs->all(); # all records for page 1
23
24 The C<page> attribute does not have to be specified in your search:
25
26   my $rs = $schema->resultset('Artist')->search(
27     undef,
28     {
29       rows => 10,
30     }
31   );
32
33   return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
34
35 In either of the above cases, you can return a L<Data::Page> object for the
36 resultset (suitable for use in e.g. a template) using the C<pager> method:
37
38   return $rs->pager();
39
40 =head3 Complex WHERE clauses
41
42 Sometimes you need to formulate a query using specific operators:
43
44   my @albums = $schema->resultset('Album')->search({
45     artist => { 'like', '%Lamb%' },
46     title  => { 'like', '%Fear of Fours%' },
47   });
48
49 This results in something like the following C<WHERE> clause:
50
51   WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
52
53 Other queries might require slightly more complex logic:
54
55   my @albums = $schema->resultset('Album')->search({
56     -or => [
57       -and => [
58         artist => { 'like', '%Smashing Pumpkins%' },
59         title  => 'Siamese Dream',
60       ],
61       artist => 'Starchildren',
62     ],
63   });
64
65 This results in the following C<WHERE> clause:
66
67   WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
68     OR artist = 'Starchildren'
69
70 For more information on generating complex queries, see
71 L<SQL::Abstract/WHERE CLAUSES>.
72
73 =head3 Using specific columns
74
75 When you only want selected columns from a table, you can use C<cols> to
76 specify which ones you need:
77
78   my $rs = $schema->resultset('Artist')->search(
79     undef,
80     {
81       columns => [qw/ name /]
82     }
83   );
84
85   # Equivalent SQL:
86   # SELECT artist.name FROM artist
87
88 =head3 Using database functions or stored procedures
89
90 The combination of C<select> and C<as> can be used to return the result of a
91 database function or stored procedure as a column value. You use C<select> to
92 specify the source for your column value (e.g. a column name, function, or
93 stored procedure name). You then use C<as> to set the column name you will use
94 to access the returned value:
95
96   my $rs = $schema->resultset('Artist')->search(
97     undef,
98     {
99       select => [ 'name', { LENGTH => 'name' } ],
100       as     => [qw/ name name_length /],
101     }
102   );
103
104   # Equivalent SQL:
105   # SELECT name name, LENGTH( name ) name_length
106   # FROM artist
107
108 If your alias exists as a column in your base class (i.e. it was added with
109 C<add_columns>), you just access it as normal. Our C<Artist> class has a C<name>
110 column, so we just use the C<name> accessor:
111
112   my $artist = $rs->first();
113   my $name = $artist->name();
114
115 If on the other hand the alias does not correspond to an existing column, you
116 can get the value using the C<get_column> accessor:
117
118   my $name_length = $artist->get_column('name_length');
119
120 If you don't like using C<get_column>, you can always create an accessor for
121 any of your aliases using either of these:
122
123   # Define accessor manually:
124   sub name_length { shift->get_column('name_length'); }
125     
126   # Or use DBIx::Class::AccessorGroup:
127   __PACKAGE__->mk_group_accessors('column' => 'name_length');
128
129 =head3 SELECT DISTINCT with multiple columns
130
131   my $rs = $schema->resultset('Foo')->search(
132     undef,
133     {
134       select => [
135         { distinct => [ $source->columns ] }
136       ],
137       as => [ $source->columns ]
138     }
139   );
140
141   my $count = $rs->next->get_column('count');
142
143 =head3 SELECT COUNT(DISTINCT colname)
144
145   my $rs = $schema->resultset('Foo')->search(
146     undef,
147     {
148       select => [
149         { count => { distinct => 'colname' } }
150       ],
151       as => [ 'count' ]
152     }
153   );
154
155 =head3 Grouping results
156
157 L<DBIx::Class> supports C<GROUP BY> as follows:
158
159   my $rs = $schema->resultset('Artist')->search(
160     undef,
161     {
162       join     => [qw/ cds /],
163       select   => [ 'name', { count => 'cds.cdid' } ],
164       as       => [qw/ name cd_count /],
165       group_by => [qw/ name /]
166     }
167   );
168
169   # Equivalent SQL:
170   # SELECT name, COUNT( cds.cdid ) FROM artist me
171   # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
172   # GROUP BY name
173
174 =head3 Predefined searches
175
176 You can write your own DBIx::Class::ResultSet class by inheriting from it
177 and define often used searches as methods:
178
179   package My::DBIC::ResultSet::CD;
180   use strict;
181   use warnings;
182   use base 'DBIx::Class::ResultSet';
183
184   sub search_cds_ordered {
185       my ($self) = @_;
186
187       return $self->search(
188           {},
189           { order_by => 'name DESC' },
190       );
191   }
192
193   1;
194
195 To use your resultset, first tell DBIx::Class to create an instance of it
196 for you, in your My::DBIC::Schema::CD class:
197
198   __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
199
200 Then call your new method in your code:
201
202    my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
203
204
205 =head3 Predefined searches without writing a ResultSet class
206
207 Alternatively you can automatically generate a DBIx::Class::ResultSet
208 class by using the ResultSetManager component and tagging your method
209 as ResultSet:
210
211   __PACKAGE__->load_components(qw/ ResultSetManager Core /);
212
213   sub search_cds_ordered : ResultSet {
214       my ($self) = @_;
215       return $self->search(
216           {},
217           { order_by => 'name DESC' },
218       );
219   } 
220
221 Then call your method in the same way from your code:
222
223    my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
224
225 =head2 Using joins and prefetch
226
227 You can use the C<join> attribute to allow searching on, or sorting your
228 results by, one or more columns in a related table. To return all CDs matching
229 a particular artist name:
230
231   my $rs = $schema->resultset('CD')->search(
232     {
233       'artist.name' => 'Bob Marley'    
234     },
235     {
236       join => [qw/artist/], # join the artist table
237     }
238   );
239
240   # Equivalent SQL:
241   # SELECT cd.* FROM cd
242   # JOIN artist ON cd.artist = artist.id
243   # WHERE artist.name = 'Bob Marley'
244
245 If required, you can now sort on any column in the related tables by including
246 it in your C<order_by> attribute:
247
248   my $rs = $schema->resultset('CD')->search(
249     {
250       'artist.name' => 'Bob Marley'
251     },
252     {
253       join     => [qw/ artist /],
254       order_by => [qw/ artist.name /]
255     }
256   };
257
258   # Equivalent SQL:
259   # SELECT cd.* FROM cd
260   # JOIN artist ON cd.artist = artist.id
261   # WHERE artist.name = 'Bob Marley'
262   # ORDER BY artist.name
263
264 Note that the C<join> attribute should only be used when you need to search or
265 sort using columns in a related table. Joining related tables when you only
266 need columns from the main table will make performance worse!
267
268 Now let's say you want to display a list of CDs, each with the name of the
269 artist. The following will work fine:
270
271   while (my $cd = $rs->next) {
272     print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
273   }
274
275 There is a problem however. We have searched both the C<cd> and C<artist> tables
276 in our main query, but we have only returned data from the C<cd> table. To get
277 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
278 to the database:
279
280   SELECT artist.* FROM artist WHERE artist.id = ?
281
282 A statement like the one above will run for each and every CD returned by our
283 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
284 queries!
285
286 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
287 This allows you to fetch results from related tables in advance:
288
289   my $rs = $schema->resultset('CD')->search(
290     {
291       'artist.name' => 'Bob Marley'
292     },
293     {
294       join     => [qw/ artist /],
295       order_by => [qw/ artist.name /],
296       prefetch => [qw/ artist /] # return artist data too!
297     }
298   );
299
300   # Equivalent SQL (note SELECT from both "cd" and "artist"):
301   # SELECT cd.*, artist.* FROM cd
302   # JOIN artist ON cd.artist = artist.id
303   # WHERE artist.name = 'Bob Marley'
304   # ORDER BY artist.name
305
306 The code to print the CD list remains the same:
307
308   while (my $cd = $rs->next) {
309     print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
310   }
311
312 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
313 so no additional SQL statements are executed. You now have a much more
314 efficient query.
315
316 Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
317 C<has_many> relationships. You will get an error along the lines of "No
318 accessor for prefetched ..." if you try.
319
320 Also note that C<prefetch> should only be used when you know you will
321 definitely use data from a related table. Pre-fetching related tables when you
322 only need columns from the main table will make performance worse!
323
324 =head3 Multi-step joins
325
326 Sometimes you want to join more than one relationship deep. In this example,
327 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
328 contain a specific string:
329
330   # Relationships defined elsewhere:
331   # Artist->has_many('cds' => 'CD', 'artist');
332   # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
333
334   my $rs = $schema->resultset('Artist')->search(
335     {
336       'liner_notes.notes' => { 'like', '%some text%' },
337     },
338     {
339       join => {
340         'cds' => 'liner_notes'
341       }
342     }
343   );
344
345   # Equivalent SQL:
346   # SELECT artist.* FROM artist
347   # JOIN ( cd ON artist.id = cd.artist )
348   # JOIN ( liner_notes ON cd.id = liner_notes.cd )
349   # WHERE liner_notes.notes LIKE '%some text%'
350
351 Joins can be nested to an arbitrary level. So if we decide later that we
352 want to reduce the number of Artists returned based on who wrote the liner
353 notes:
354
355   # Relationship defined elsewhere:
356   # LinerNotes->belongs_to('author' => 'Person');
357
358   my $rs = $schema->resultset('Artist')->search(
359     {
360       'liner_notes.notes' => { 'like', '%some text%' },
361       'author.name' => 'A. Writer'
362     },
363     {
364       join => {
365         'cds' => {
366           'liner_notes' => 'author'
367         }
368       }
369     }
370   );
371
372   # Equivalent SQL:
373   # SELECT artist.* FROM artist
374   # JOIN ( cd ON artist.id = cd.artist )
375   # JOIN ( liner_notes ON cd.id = liner_notes.cd )
376   # JOIN ( author ON author.id = liner_notes.author )
377   # WHERE liner_notes.notes LIKE '%some text%'
378   # AND author.name = 'A. Writer'
379
380 =head2 Multi-step prefetch
381
382 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
383 deep using the same syntax as a multi-step join:
384
385   my $rs = $schema->resultset('Tag')->search(
386     undef,
387     {
388       prefetch => {
389         cd => 'artist'
390       }
391     }
392   );
393
394   # Equivalent SQL:
395   # SELECT tag.*, cd.*, artist.* FROM tag
396   # JOIN cd ON tag.cd = cd.cdid
397   # JOIN artist ON cd.artist = artist.artistid
398
399 Now accessing our C<cd> and C<artist> relationships does not need additional
400 SQL statements:
401
402   my $tag = $rs->first;
403   print $tag->cd->artist->name;
404
405 =head2 Transactions
406
407 As of version 0.04001, there is improved transaction support in
408 L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>.  Here is an
409 example of the recommended way to use it:
410
411   my $genus = $schema->resultset('Genus')->find(12);
412
413   my $coderef1 = sub {
414     my ($schema, $genus, $code) = @_;
415     $genus->add_to_species({ name => 'troglodyte' });
416     $genus->wings(2);
417     $genus->update;
418     $schema->txn_do($code, $genus); # Can have a nested transaction
419     return $genus->species;
420   };
421
422   my $coderef2 = sub {
423     my ($genus) = @_;
424     $genus->extinct(1);
425     $genus->update;
426   };
427
428   my $rs;
429   eval {
430     $rs = $schema->txn_do($coderef1, $schema, $genus, $coderef2);
431   };
432
433   if ($@) {                             # Transaction failed
434     die "the sky is falling!"           #
435       if ($@ =~ /Rollback failed/);     # Rollback failed
436
437     deal_with_failed_transaction();
438   }
439
440 Nested transactions will work as expected. That is, only the outermost
441 transaction will actually issue a commit to the $dbh, and a rollback
442 at any level of any transaction will cause the entire nested
443 transaction to fail. Support for savepoints and for true nested
444 transactions (for databases that support them) will hopefully be added
445 in the future.
446
447 =head2 Many-to-many relationships
448
449 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
450
451   package My::DB;
452   # ... set up connection ...
453
454   package My::User;
455   use base 'My::DB';
456   __PACKAGE__->table('user');
457   __PACKAGE__->add_columns(qw/id name/);
458   __PACKAGE__->set_primary_key('id');
459   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
460   __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
461
462   package My::UserAddress;
463   use base 'My::DB';
464   __PACKAGE__->table('user_address');
465   __PACKAGE__->add_columns(qw/user address/);
466   __PACKAGE__->set_primary_key(qw/user address/);
467   __PACKAGE__->belongs_to('user' => 'My::User');
468   __PACKAGE__->belongs_to('address' => 'My::Address');
469
470   package My::Address;
471   use base 'My::DB';
472   __PACKAGE__->table('address');
473   __PACKAGE__->add_columns(qw/id street town area_code country/);
474   __PACKAGE__->set_primary_key('id');
475   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
476   __PACKAGE__->many_to_many('users' => 'user_address', 'user');
477
478   $rs = $user->addresses(); # get all addresses for a user
479   $rs = $address->users(); # get all users for an address
480
481 =head2 Setting default values for a row
482
483 It's as simple as overriding the C<new> method.  Note the use of
484 C<next::method>.
485
486   sub new {
487     my ( $class, $attrs ) = @_;
488
489     $attrs->{foo} = 'bar' unless defined $attrs->{foo};
490
491     $class->next::method($attrs);
492   }
493
494 For more information about C<next::method>, look in the L<Class::C3> 
495 documentation. See also L<DBIx::Class::Manual::Component> for more
496 ways to write your own base classes to do this.
497
498 People looking for ways to do "triggers" with DBIx::Class are probably
499 just looking for this.
500
501 =head2 Stringification
502
503 Employ the standard stringification technique by using the C<overload>
504 module.  Replace C<foo> with the column/method of your choice.
505
506   use overload '""' => 'foo', fallback => 1;
507
508 =head2 Disconnecting cleanly
509
510 If you find yourself quitting an app with Control-C a lot during
511 development, you might like to put the following signal handler in
512 your main database class to make sure it disconnects cleanly:
513
514   $SIG{INT} = sub {
515     __PACKAGE__->storage->disconnect;
516   };
517
518 =head2 Schema import/export
519
520 This functionality requires you to have L<SQL::Translator> (also known as
521 "SQL Fairy") installed.
522
523 To create a DBIx::Class schema from an existing database:
524
525  sqlt --from DBI
526       --to DBIx::Class::File
527       --prefix "MySchema" > MySchema.pm
528
529 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
530 schema to MySQL's dialect of SQL:
531
532   sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
533   
534 And import using the mysql client:
535
536   mysql -h "host" -D "database" -u "user" -p < Schema1.sql
537
538 =head2 Easy migration from class-based to schema-based setup
539
540 You want to start using the schema-based approach to L<DBIx::Class>
541 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
542 of existing classes that you don't want to move by hand. Try this nifty script
543 instead:
544
545   use MyDB;
546   use SQL::Translator;
547   
548   my $schema = MyDB->schema_instance;
549   
550   my $translator           =  SQL::Translator->new( 
551       debug                => $debug          ||  0,
552       trace                => $trace          ||  0,
553       no_comments          => $no_comments    ||  0,
554       show_warnings        => $show_warnings  ||  0,
555       add_drop_table       => $add_drop_table ||  0,
556       validate             => $validate       ||  0,
557       parser_args          => {
558          'DBIx::Schema'    => $schema,
559                               },
560       producer_args   => {
561           'prefix'         => 'My::Schema',
562                          },
563   );
564   
565   $translator->parser('DBIx::Class');
566   $translator->producer('DBIx::Class::File');
567   
568   my $output = $translator->translate(@args) or die
569           "Error: " . $translator->error;
570   
571   print $output;
572
573 You could use L<Module::Find> to search for all subclasses in the MyDB::*
574 namespace, which is currently left as an exercise for the reader.
575
576 =head2 Schema versioning
577
578 The following example shows simplistically how you might use DBIx::Class to
579 deploy versioned schemas to your customers. The basic process is as follows:
580
581 =over 4
582
583 =item 1.
584
585 Create a DBIx::Class schema
586
587 =item 2.
588
589 Save the schema
590
591 =item 3.
592
593 Deploy to customers
594
595 =item 4.
596
597 Modify schema to change functionality
598
599 =item 5.
600
601 Deploy update to customers
602
603 =back
604
605 =head3 Create a DBIx::Class schema
606
607 This can either be done manually, or generated from an existing database as
608 described under C<Schema import/export>.
609
610 =head3 Save the schema
611
612 Use C<sqlt> to transform your schema into an SQL script suitable for your
613 customer's database. E.g. for MySQL:
614
615   sqlt --from DBIx::Class
616        --to MySQL
617        --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
618
619 If you need to target databases from multiple vendors, just generate an SQL
620 script suitable for each. To support PostgreSQL too:
621
622   sqlt --from DBIx::Class
623        --to PostgreSQL
624        --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
625
626 =head3 Deploy to customers
627
628 There are several ways you could deploy your schema. These are probably
629 beyond the scope of this recipe, but might include:
630
631 =over 4
632
633 =item 1.
634
635 Require customer to apply manually using their RDBMS.
636
637 =item 2.
638
639 Package along with your app, making database dump/schema update/tests
640 all part of your install.
641
642 =back
643
644 =head3 Modify the schema to change functionality
645
646 As your application evolves, it may be necessary to modify your schema to
647 change functionality. Once the changes are made to your schema in DBIx::Class,
648 export the modified schema as before, taking care not to overwrite the original:
649
650   sqlt --from DBIx::Class
651        --to MySQL
652        --DBIx::Class "Anything.pm" > Schema2.mysql.sql
653
654 Next, use sqlt-diff to create an SQL script that will update the customer's
655 database schema:
656
657   sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
658
659 =head3 Deploy update to customers
660
661 The schema update can be deployed to customers using the same method as before.
662
663 =head2 Setting limit dialect for SQL::Abstract::Limit
664
665 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
666 SQL-server by looking at the database-handle. This is a common problem when
667 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
668 available, not which JDBC-driver the Java component has loaded.
669 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
670 in SQL::Abstract::Limit -documentation.
671
672   __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
673
674 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
675 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
676
677 =head2 Setting quotes for the generated SQL. 
678
679 If the database contains columnames with spaces and/or reserved words, the
680 SQL-query needs to be quoted. This is done using:
681
682   __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
683   __PACKAGE__->storage->sql_maker->name_sep('.');
684
685 The first sets the quotesymbols. If the quote i "symmetric" as " or '
686   
687   __PACKAGE__->storage->sql_maker->quote_char('"');
688
689 is enough. If the left quote differs form the right quote, the first 
690 notation should be used. name_sep needs to be set to allow the 
691 SQL generator to put the quotes the correct place. 
692
693 =cut