Merge 'trunk' into 'DBIx-Class-current'
[dbsrgits/DBIx-Class-Historic.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
bade79c4 73=head3 Using specific columns
faf62551 74
bade79c4 75When you only want selected columns from a table, you can use C<cols> to
76specify which ones you need:
faf62551 77
bade79c4 78 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 79 undef,
bade79c4 80 {
5e8b1b2a 81 columns => [qw/ name /]
bade79c4 82 }
83 );
faf62551 84
bade79c4 85 # Equivalent SQL:
86 # SELECT artist.name FROM artist
faf62551 87
bade79c4 88=head3 Using database functions or stored procedures
faf62551 89
bade79c4 90The combination of C<select> and C<as> can be used to return the result of a
91database function or stored procedure as a column value. You use C<select> to
92specify the source for your column value (e.g. a column name, function, or
93stored procedure name). You then use C<as> to set the column name you will use
94to access the returned value:
faf62551 95
bade79c4 96 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 97 undef,
bade79c4 98 {
99 select => [ 'name', { LENGTH => 'name' } ],
100 as => [qw/ name name_length /],
101 }
102 );
faf62551 103
bade79c4 104 # Equivalent SQL:
105 # SELECT name name, LENGTH( name ) name_length
106 # FROM artist
faf62551 107
108If your alias exists as a column in your base class (i.e. it was added with
bade79c4 109C<add_columns>), you just access it as normal. Our C<Artist> class has a C<name>
110column, so we just use the C<name> accessor:
faf62551 111
bade79c4 112 my $artist = $rs->first();
113 my $name = $artist->name();
faf62551 114
115If on the other hand the alias does not correspond to an existing column, you
bade79c4 116can get the value using the C<get_column> accessor:
faf62551 117
bade79c4 118 my $name_length = $artist->get_column('name_length');
faf62551 119
bade79c4 120If you don't like using C<get_column>, you can always create an accessor for
faf62551 121any of your aliases using either of these:
122
bade79c4 123 # Define accessor manually:
124 sub name_length { shift->get_column('name_length'); }
faf62551 125
bade79c4 126 # Or use DBIx::Class::AccessorGroup:
127 __PACKAGE__->mk_group_accessors('column' => 'name_length');
faf62551 128
bade79c4 129=head3 SELECT DISTINCT with multiple columns
faf62551 130
bade79c4 131 my $rs = $schema->resultset('Foo')->search(
5e8b1b2a 132 undef,
bade79c4 133 {
134 select => [
135 { distinct => [ $source->columns ] }
136 ],
137 as => [ $source->columns ]
138 }
139 );
faf62551 140
286f32b3 141 my $count = $rs->next->get_column('count');
142
bade79c4 143=head3 SELECT COUNT(DISTINCT colname)
6607ee1b 144
bade79c4 145 my $rs = $schema->resultset('Foo')->search(
5e8b1b2a 146 undef,
bade79c4 147 {
148 select => [
149 { count => { distinct => 'colname' } }
150 ],
151 as => [ 'count' ]
152 }
153 );
6607ee1b 154
bade79c4 155=head3 Grouping results
156
157L<DBIx::Class> supports C<GROUP BY> as follows:
158
159 my $rs = $schema->resultset('Artist')->search(
5e8b1b2a 160 undef,
bade79c4 161 {
162 join => [qw/ cds /],
163 select => [ 'name', { count => 'cds.cdid' } ],
164 as => [qw/ name cd_count /],
165 group_by => [qw/ name /]
166 }
167 );
6607ee1b 168
bade79c4 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
6607ee1b 173
74dc2edc 174=head3 Predefined searches
175
176You can write your own DBIx::Class::ResultSet class by inheriting from it
177and 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
195To use your resultset, first tell DBIx::Class to create an instance of it
196for you, in your My::DBIC::Schema::CD class:
197
198 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
199
200Then call your new method in your code:
201
202 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
203
204
d6ed6a67 205=head3 Predefined searches without writing a ResultSet class
206
207Alternatively you can automatically generate a DBIx::Class::ResultSet
208class by using the ResultSetManager component and tagging your method
209as 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
221Then call your method in the same way from your code:
222
223 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
224
87980de7 225=head2 Using joins and prefetch
226
bade79c4 227You can use the C<join> attribute to allow searching on, or sorting your
228results by, one or more columns in a related table. To return all CDs matching
229a particular artist name:
ea6309e2 230
bade79c4 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
245If required, you can now sort on any column in the related tables by including
246it 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 };
ea6309e2 257
bade79c4 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
ea6309e2 263
bade79c4 264Note that the C<join> attribute should only be used when you need to search or
265sort using columns in a related table. Joining related tables when you only
266need columns from the main table will make performance worse!
ea6309e2 267
bade79c4 268Now let's say you want to display a list of CDs, each with the name of the
269artist. The following will work fine:
ea6309e2 270
bade79c4 271 while (my $cd = $rs->next) {
272 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
273 }
ea6309e2 274
bade79c4 275There is a problem however. We have searched both the C<cd> and C<artist> tables
276in our main query, but we have only returned data from the C<cd> table. To get
277the artist name for any of the CD objects returned, L<DBIx::Class> will go back
278to the database:
ea6309e2 279
bade79c4 280 SELECT artist.* FROM artist WHERE artist.id = ?
ea6309e2 281
282A statement like the one above will run for each and every CD returned by our
283main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
284queries!
285
bade79c4 286Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
897342e4 287This allows you to fetch results from related tables in advance:
ea6309e2 288
bade79c4 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 );
ea6309e2 299
bade79c4 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
ea6309e2 305
306The code to print the CD list remains the same:
307
bade79c4 308 while (my $cd = $rs->next) {
309 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
310 }
ea6309e2 311
bade79c4 312L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
ea6309e2 313so no additional SQL statements are executed. You now have a much more
314efficient query.
315
bade79c4 316Note that as of L<DBIx::Class> 0.04, C<prefetch> cannot be used with
317C<has_many> relationships. You will get an error along the lines of "No
318accessor for prefetched ..." if you try.
ea6309e2 319
bade79c4 320Also note that C<prefetch> should only be used when you know you will
ea6309e2 321definitely use data from a related table. Pre-fetching related tables when you
322only need columns from the main table will make performance worse!
323
bade79c4 324=head3 Multi-step joins
ea6309e2 325
326Sometimes you want to join more than one relationship deep. In this example,
bade79c4 327we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
328contain 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 );
ea6309e2 344
bade79c4 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%'
ea6309e2 350
351Joins can be nested to an arbitrary level. So if we decide later that we
352want to reduce the number of Artists returned based on who wrote the liner
353notes:
354
bade79c4 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'
ea6309e2 367 }
bade79c4 368 }
369 }
370 );
ea6309e2 371
bade79c4 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'
87980de7 379
897342e4 380=head2 Multi-step prefetch
381
382From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
383deep using the same syntax as a multi-step join:
384
385 my $rs = $schema->resultset('Tag')->search(
5e8b1b2a 386 undef,
897342e4 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
399Now accessing our C<cd> and C<artist> relationships does not need additional
400SQL statements:
401
402 my $tag = $rs->first;
403 print $tag->cd->artist->name;
404
87980de7 405=head2 Transactions
406
407As of version 0.04001, there is improved transaction support in
181a28f4 408L<DBIx::Class::Storage::DBI> and L<DBIx::Class::Schema>. Here is an
409example of the recommended way to use it:
87980de7 410
181a28f4 411 my $genus = $schema->resultset('Genus')->find(12);
412
413 my $coderef1 = sub {
414 my ($schema, $genus, $code) = @_;
35d4fe78 415 $genus->add_to_species({ name => 'troglodyte' });
416 $genus->wings(2);
417 $genus->update;
880a1a0c 418 $schema->txn_do($code, $genus); # Can have a nested transaction
181a28f4 419 return $genus->species;
420 };
421
422 my $coderef2 = sub {
423 my ($genus) = @_;
424 $genus->extinct(1);
425 $genus->update;
35d4fe78 426 };
181a28f4 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();
35d4fe78 438 }
87980de7 439
181a28f4 440Nested transactions will work as expected. That is, only the outermost
441transaction will actually issue a commit to the $dbh, and a rollback
442at any level of any transaction will cause the entire nested
443transaction to fail. Support for savepoints and for true nested
40dbc108 444transactions (for databases that support them) will hopefully be added
445in the future.
ee38fa40 446
130c6439 447=head2 Many-to-many relationships
ee38fa40 448
ea6309e2 449This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
450
bade79c4 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
a00e1684 482
35d4fe78 483It's as simple as overriding the C<new> method. Note the use of
40dbc108 484C<next::method>.
a00e1684 485
35d4fe78 486 sub new {
487 my ( $class, $attrs ) = @_;
40dbc108 488
35d4fe78 489 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
40dbc108 490
35d4fe78 491 $class->next::method($attrs);
492 }
a00e1684 493
4a3c6354 494For more information about C<next::method>, look in the L<Class::C3>
495documentation. See also L<DBIx::Class::Manual::Component> for more
496ways to write your own base classes to do this.
497
498People looking for ways to do "triggers" with DBIx::Class are probably
499just looking for this.
500
25af00d7 501=head2 Stringification
502
40dbc108 503Employ the standard stringification technique by using the C<overload>
35d4fe78 504module. Replace C<foo> with the column/method of your choice.
25af00d7 505
35d4fe78 506 use overload '""' => 'foo', fallback => 1;
25af00d7 507
bade79c4 508=head2 Disconnecting cleanly
509
510If you find yourself quitting an app with Control-C a lot during
511development, you might like to put the following signal handler in
512your main database class to make sure it disconnects cleanly:
513
514 $SIG{INT} = sub {
6d1bf0a9 515 __PACKAGE__->storage->disconnect;
bade79c4 516 };
517
362500af 518=head2 Schema import/export
519
520This functionality requires you to have L<SQL::Translator> (also known as
521"SQL Fairy") installed.
522
523To 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
529To create a MySQL database from an existing L<DBIx::Class> schema, convert the
530schema to MySQL's dialect of SQL:
531
532 sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
533
534And import using the mysql client:
535
536 mysql -h "host" -D "database" -u "user" -p < Schema1.sql
537
b0a20454 538=head2 Easy migration from class-based to schema-based setup
539
540You 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
542of existing classes that you don't want to move by hand. Try this nifty script
543instead:
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,
c5f36986 559 },
b0a20454 560 producer_args => {
561 'prefix' => 'My::Schema',
c5f36986 562 },
b0a20454 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
573You could use L<Module::Find> to search for all subclasses in the MyDB::*
880a1a0c 574namespace, which is currently left as an exercise for the reader.
b0a20454 575
362500af 576=head2 Schema versioning
577
578The following example shows simplistically how you might use DBIx::Class to
579deploy versioned schemas to your customers. The basic process is as follows:
580
da4779ad 581=over 4
582
583=item 1.
584
585Create a DBIx::Class schema
586
587=item 2.
588
589Save the schema
590
591=item 3.
592
593Deploy to customers
594
595=item 4.
596
597Modify schema to change functionality
598
599=item 5.
600
601Deploy update to customers
602
603=back
362500af 604
605=head3 Create a DBIx::Class schema
606
607This can either be done manually, or generated from an existing database as
608described under C<Schema import/export>.
609
610=head3 Save the schema
611
612Use C<sqlt> to transform your schema into an SQL script suitable for your
613customer's database. E.g. for MySQL:
614
615 sqlt --from DBIx::Class
616 --to MySQL
617 --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
618
619If you need to target databases from multiple vendors, just generate an SQL
620script 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
628There are several ways you could deploy your schema. These are probably
629beyond the scope of this recipe, but might include:
630
da4779ad 631=over 4
632
633=item 1.
634
635Require customer to apply manually using their RDBMS.
636
637=item 2.
638
639Package along with your app, making database dump/schema update/tests
362500af 640all part of your install.
641
da4779ad 642=back
643
362500af 644=head3 Modify the schema to change functionality
645
646As your application evolves, it may be necessary to modify your schema to
647change functionality. Once the changes are made to your schema in DBIx::Class,
648export 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
654Next, use sqlt-diff to create an SQL script that will update the customer's
655database schema:
656
657 sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
658
659=head3 Deploy update to customers
660
661The schema update can be deployed to customers using the same method as before.
662
7be93b07 663=head2 Setting limit dialect for SQL::Abstract::Limit
664
665In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
666SQL-server by looking at the database-handle. This is a common problem when
667using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
668available, not which JDBC-driver the Java component has loaded.
669This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
670in SQL::Abstract::Limit -documentation.
671
672 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
673
674The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
675that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
676
2437a1e3 677=head2 Setting quotes for the generated SQL.
678
679If the database contains columnames with spaces and/or reserved words, the
680SQL-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
685The first sets the quotesymbols. If the quote i "symmetric" as " or '
686
687 __PACKAGE__->storage->sql_maker->quote_char('"');
688
880a1a0c 689is enough. If the left quote differs form the right quote, the first
2437a1e3 690notation should be used. name_sep needs to be set to allow the
691SQL generator to put the quotes the correct place.
692
40dbc108 693=cut