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