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