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