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     {},
  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     {},
  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     {},
  80     {
  81       cols => [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     {},
  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     {},
 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     {},
 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     {},
 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     {},
 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>.  Here is an example of the recommended
 356 way to use it:
 357
 358   my $genus = Genus->find(12);
 359   eval {
 360     MyDB->txn_begin;
 361     $genus->add_to_species({ name => 'troglodyte' });
 362     $genus->wings(2);
 363     $genus->update;
 364     cromulate($genus); # Can have a nested transation
 365     MyDB->txn_commit;
 366   };
 367   if ($@) {
 368     # Rollback might fail, too
 369     eval {
 370       MyDB->txn_rollback
 371     };
 372   }
 373
 374 Currently, a nested commit will do nothing and a nested rollback will
 375 die.  The code at each level must be sure to call rollback in the case
 376 of an error, to ensure that the rollback will propagate to the top
 377 level and be issued.  Support for savepoints and for true nested
 378 transactions (for databases that support them) will hopefully be added
 379 in the future.
 380
 381 =head2 Many-to-many relationships
 382
 383 This is straightforward using L<DBIx::Class::Relationship::ManyToMany>:
 384
 385   package My::DB;
 386   # ... set up connection ...
 387
 388   package My::User;
 389   use base 'My::DB';
 390   __PACKAGE__->table('user');
 391   __PACKAGE__->add_columns(qw/id name/);
 392   __PACKAGE__->set_primary_key('id');
 393   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
 394   __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
 395
 396   package My::UserAddress;
 397   use base 'My::DB';
 398   __PACKAGE__->table('user_address');
 399   __PACKAGE__->add_columns(qw/user address/);
 400   __PACKAGE__->set_primary_key(qw/user address/);
 401   __PACKAGE__->belongs_to('user' => 'My::User');
 402   __PACKAGE__->belongs_to('address' => 'My::Address');
 403
 404   package My::Address;
 405   use base 'My::DB';
 406   __PACKAGE__->table('address');
 407   __PACKAGE__->add_columns(qw/id street town area_code country/);
 408   __PACKAGE__->set_primary_key('id');
 409   __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
 410   __PACKAGE__->many_to_many('users' => 'user_address', 'user');
 411
 412   $rs = $user->addresses(); # get all addresses for a user
 413   $rs = $address->users(); # get all users for an address
 414
 415 =head2 Setting default values for a row
 416
 417 It's as simple as overriding the C<new> method.  Note the use of
 418 C<next::method>.
 419
 420   sub new {
 421     my ( $class, $attrs ) = @_;
 422
 423     $attrs->{foo} = 'bar' unless defined $attrs->{foo};
 424
 425     $class->next::method($attrs);
 426   }
 427
 428 =head2 Stringification
 429
 430 Employ the standard stringification technique by using the C<overload>
 431 module.  Replace C<foo> with the column/method of your choice.
 432
 433   use overload '""' => 'foo', fallback => 1;
 434
 435 =head2 Disconnecting cleanly
 436
 437 If you find yourself quitting an app with Control-C a lot during
 438 development, you might like to put the following signal handler in
 439 your main database class to make sure it disconnects cleanly:
 440
 441   $SIG{INT} = sub {
 442     __PACKAGE__->storage->disconnect;
 443   };
 444
 445 =head2 Schema import/export
 446
 447 This functionality requires you to have L<SQL::Translator> (also known as
 448 "SQL Fairy") installed.
 449
 450 To create a DBIx::Class schema from an existing database:
 451
 452  sqlt --from DBI
 453       --to DBIx::Class::File
 454       --prefix "MySchema" > MySchema.pm
 455
 456 To create a MySQL database from an existing L<DBIx::Class> schema, convert the
 457 schema to MySQL's dialect of SQL:
 458
 459   sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql
 460
 461 And import using the mysql client:
 462
 463   mysql -h "host" -D "database" -u "user" -p < Schema1.sql
 464
 465 =head2 Easy migration from class-based to schema-based setup
 466
 467 You want to start using the schema-based approach to L<DBIx::Class>
 468 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
 469 of existing classes that you don't want to move by hand. Try this nifty script
 470 instead:
 471
 472   use MyDB;
 473   use SQL::Translator;
 474
 475   my $schema = MyDB->schema_instance;
 476
 477   my $translator           =  SQL::Translator->new(
 478       debug                => $debug          ||  0,
 479       trace                => $trace          ||  0,
 480       no_comments          => $no_comments    ||  0,
 481       show_warnings        => $show_warnings  ||  0,
 482       add_drop_table       => $add_drop_table ||  0,
 483       validate             => $validate       ||  0,
 484       parser_args          => {
 485          'DBIx::Schema'    => $schema,
 486                               }
 487       producer_args   => {
 488           'prefix'         => 'My::Schema',
 489                          }
 490   );
 491
 492   $translator->parser('DBIx::Class');
 493   $translator->producer('DBIx::Class::File');
 494
 495   my $output = $translator->translate(@args) or die
 496           "Error: " . $translator->error;
 497
 498   print $output;
 499
 500 You could use L<Module::Find> to search for all subclasses in the MyDB::*
 501 namespace, which is currently left as an excercise for the reader.
 502
 503 =head2 Schema versioning
 504
 505 The following example shows simplistically how you might use DBIx::Class to
 506 deploy versioned schemas to your customers. The basic process is as follows:
 507
 508 =over 4
 509
 510 =item 1.
 511
 512 Create a DBIx::Class schema
 513
 514 =item 2.
 515
 516 Save the schema
 517
 518 =item 3.
 519
 520 Deploy to customers
 521
 522 =item 4.
 523
 524 Modify schema to change functionality
 525
 526 =item 5.
 527
 528 Deploy update to customers
 529
 530 =back
 531
 532 =head3 Create a DBIx::Class schema
 533
 534 This can either be done manually, or generated from an existing database as
 535 described under C<Schema import/export>.
 536
 537 =head3 Save the schema
 538
 539 Use C<sqlt> to transform your schema into an SQL script suitable for your
 540 customer's database. E.g. for MySQL:
 541
 542   sqlt --from DBIx::Class
 543        --to MySQL
 544        --DBIx::Class "MySchema.pm" > Schema1.mysql.sql
 545
 546 If you need to target databases from multiple vendors, just generate an SQL
 547 script suitable for each. To support PostgreSQL too:
 548
 549   sqlt --from DBIx::Class
 550        --to PostgreSQL
 551        --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql
 552
 553 =head3 Deploy to customers
 554
 555 There are several ways you could deploy your schema. These are probably
 556 beyond the scope of this recipe, but might include:
 557
 558 =over 4
 559
 560 =item 1.
 561
 562 Require customer to apply manually using their RDBMS.
 563
 564 =item 2.
 565
 566 Package along with your app, making database dump/schema update/tests
 567 all part of your install.
 568
 569 =back
 570
 571 =head3 Modify the schema to change functionality
 572
 573 As your application evolves, it may be necessary to modify your schema to
 574 change functionality. Once the changes are made to your schema in DBIx::Class,
 575 export the modified schema as before, taking care not to overwrite the original:
 576
 577   sqlt --from DBIx::Class
 578        --to MySQL
 579        --DBIx::Class "Anything.pm" > Schema2.mysql.sql
 580
 581 Next, use sqlt-diff to create an SQL script that will update the customer's
 582 database schema:
 583
 584   sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql
 585
 586 =head3 Deploy update to customers
 587
 588 The schema update can be deployed to customers using the same method as before.
 589
 590 =head2 Setting limit dialect for SQL::Abstract::Limit
 591
 592 In some cases, SQL::Abstract::Limit cannot determine the dialect of the remote
 593 SQL-server by looking at the database-handle. This is a common problem when
 594 using the DBD::JDBC, since the DBD-driver only know that in has a Java-driver
 595 available, not which JDBC-driver the Java component has loaded.
 596 This specifically sets the limit_dialect to Microsoft SQL-server (Se more names
 597 in SQL::Abstract::Limit -documentation.
 598
 599   __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
 600
 601 The JDBC-bridge is one way of getting access to a MSSQL-server from a platform
 602 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
 603
 604 =head2 Setting quotes for the generated SQL.
 605
 606 If the database contains columnames with spaces and/or reserved words, the
 607 SQL-query needs to be quoted. This is done using:
 608
 609   __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
 610   __PACKAGE__->storage->sql_maker->name_sep('.');
 611
 612 The first sets the quotesymbols. If the quote i "symmetric" as " or '
 613
 614   __PACKAGE__->storage->sql_maker->quote_char('"');
 615
 616 is enough. If the left qoute differs form the right quote, the first
 617 notation should be used. name_sep needs to be set to allow the
 618 SQL generator to put the quotes the correct place.
 619
 620 =cut