1=head1 NAME 2 3DBIx::Class::Manual::Cookbook - Miscellaneous recipes 4 5=head1 SEARCHING 6 7=head2 Paged results 8 9When you expect a large number of results, you can ask L<DBIx::Class> for a 10paged resultset, which will fetch only a defined number of records at a time: 11 12 my $rs = $schema->resultset('Artist')->search( 13 undef, 14 { 15 page => 1, # page to return (defaults to 1) 16 rows => 10, # number of results per page 17 }, 18 ); 19 20 return $rs->all(); # all records for page 1 21 22 return $rs->page(2); # records for page 2 23 24You can get a L<Data::Page> object for the resultset (suitable for use 25in e.g. a template) using the C<pager> method: 26 27 return $rs->pager(); 28 29=head2 Complex WHERE clauses 30 31Sometimes you need to formulate a query using specific operators: 32 33 my @albums = $schema->resultset('Album')->search({ 34 artist => { 'like', '%Lamb%' }, 35 title => { 'like', '%Fear of Fours%' }, 36 }); 37 38This results in something like the following C<WHERE> clause: 39 40 WHERE artist LIKE ? AND title LIKE ? 41 42And the following bind values for the placeholders: C<'%Lamb%'>, C<'%Fear of 43Fours%'>. 44 45Other queries might require slightly more complex logic: 46 47 my @albums = $schema->resultset('Album')->search({ 48 -or => [ 49 -and => [ 50 artist => { 'like', '%Smashing Pumpkins%' }, 51 title => 'Siamese Dream', 52 ], 53 artist => 'Starchildren', 54 ], 55 }); 56 57This results in the following C<WHERE> clause: 58 59 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' ) 60 OR artist = 'Starchildren' 61 62For more information on generating complex queries, see 63L<SQL::Abstract/WHERE CLAUSES>. 64 65=head2 Retrieve one and only one row from a resultset 66 67Sometimes you need only the first "top" row of a resultset. While this 68can be easily done with L<< $rs->first|DBIx::Class::ResultSet/first 69>>, it is suboptimal, as a full blown cursor for the resultset will be 70created and then immediately destroyed after fetching the first row 71object. L<< $rs->single|DBIx::Class::ResultSet/single >> is designed 72specifically for this case - it will grab the first returned result 73without even instantiating a cursor. 74 75Before replacing all your calls to C<first()> with C<single()> please observe the 76following CAVEATS: 77 78=over 79 80=item * 81 82While single() takes a search condition just like search() does, it does 83_not_ accept search attributes. However one can always chain a single() to 84a search(): 85 86 my $top_cd = $cd_rs->search({}, { order_by => 'rating' })->single; 87 88 89=item * 90 91Since single() is the engine behind find(), it is designed to fetch a 92single row per database query. Thus a warning will be issued when the 93underlying SELECT returns more than one row. Sometimes however this usage 94is valid: i.e. we have an arbitrary number of cd's but only one of them is 95at the top of the charts at any given time. If you know what you are doing, 96you can silence the warning by explicitly limiting the resultset size: 97 98 my $top_cd = $cd_rs->search ({}, { order_by => 'rating', rows => 1 })->single; 99 100=back 101 102=head2 Arbitrary SQL through a custom ResultSource 103 104Sometimes you have to run arbitrary SQL because your query is too complex 105(e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to 106be optimized for your database in a special way, but you still want to 107get the results as a L<DBIx::Class::ResultSet>. 108 109This is accomplished by defining a 110L<ResultSource::View|DBIx::Class::ResultSource::View> for your query, 111almost like you would define a regular ResultSource. 112 113 package My::Schema::Result::UserFriendsComplex; 114 use strict; 115 use warnings; 116 use base qw/DBIx::Class::Core/; 117 118 __PACKAGE__->table_class('DBIx::Class::ResultSource::View'); 119 120 # ->table, ->add_columns, etc. 121 122 # do not attempt to deploy() this view 123 __PACKAGE__->result_source_instance->is_virtual(1); 124 125 __PACKAGE__->result_source_instance->view_definition(q[ 126 SELECT u.* FROM user u 127 INNER JOIN user_friends f ON u.id = f.user_id 128 WHERE f.friend_user_id = ? 129 UNION 130 SELECT u.* FROM user u 131 INNER JOIN user_friends f ON u.id = f.friend_user_id 132 WHERE f.user_id = ? 133 ]); 134 135Next, you can execute your complex query using bind parameters like this: 136 137 my $friends = $schema->resultset( 'UserFriendsComplex' )->search( {}, 138 { 139 bind => [ 12345, 12345 ] 140 } 141 ); 142 143... and you'll get back a perfect L<DBIx::Class::ResultSet> (except, of course, 144that you cannot modify the rows it contains, e.g. cannot call L</update>, 145L</delete>, ... on it). 146 147Note that you cannot have bind parameters unless is_virtual is set to true. 148 149=over 150 151=item * NOTE 152 153If you're using the old deprecated C<< $rsrc_instance->name(\'( SELECT ...') >> 154method for custom SQL execution, you are highly encouraged to update your code 155to use a virtual view as above. If you do not want to change your code, and just 156want to suppress the deprecation warning when you call 157L<DBIx::Class::Schema/deploy>, add this line to your source definition, so that 158C<deploy> will exclude this "table": 159 160 sub sqlt_deploy_hook { $_[1]->schema->drop_table ($_[1]) } 161 162=back 163 164=head2 Using specific columns 165 166When you only want specific columns from a table, you can use 167C<columns> to specify which ones you need. This is useful to avoid 168loading columns with large amounts of data that you aren't about to 169use anyway: 170 171 my $rs = $schema->resultset('Artist')->search( 172 undef, 173 { 174 columns => [qw/ name /] 175 } 176 ); 177 178 # Equivalent SQL: 179 # SELECT artist.name FROM artist 180 181This is a shortcut for C<select> and C<as>, see below. C<columns> 182cannot be used together with C<select> and C<as>. 183 184=head2 Using database functions or stored procedures 185 186The combination of C<select> and C<as> can be used to return the result of a 187database function or stored procedure as a column value. You use C<select> to 188specify the source for your column value (e.g. a column name, function, or 189stored procedure name). You then use C<as> to set the column name you will use 190to access the returned value: 191 192 my $rs = $schema->resultset('Artist')->search( 193 {}, 194 { 195 select => [ 'name', { LENGTH => 'name' } ], 196 as => [qw/ name name_length /], 197 } 198 ); 199 200 # Equivalent SQL: 201 # SELECT name name, LENGTH( name ) 202 # FROM artist 203 204Note that the C<as> attribute B<has absolutely nothing to do> with the SQL 205syntax C< SELECT foo AS bar > (see the documentation in 206L<DBIx::Class::ResultSet/ATTRIBUTES>). You can control the C<AS> part of the 207generated SQL via the C<-as> field attribute as follows: 208 209 my $rs = $schema->resultset('Artist')->search( 210 {}, 211 { 212 join => 'cds', 213 distinct => 1, 214 '+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ], 215 '+as' => [qw/num_cds/], 216 order_by => { -desc => 'amount_of_cds' }, 217 } 218 ); 219 220 # Equivalent SQL 221 # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds 222 # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid 223 # GROUP BY me.artistid, me.name, me.rank, me.charfield 224 # ORDER BY amount_of_cds DESC 225 226 227If your alias exists as a column in your base class (i.e. it was added with 228L<add_columns|DBIx::Class::ResultSource/add_columns>), you just access it as 229normal. Our C<Artist> class has a C<name> column, so we just use the C<name> 230accessor: 231 232 my $artist = $rs->first(); 233 my $name = $artist->name(); 234 235If on the other hand the alias does not correspond to an existing column, you 236have to fetch the value using the C<get_column> accessor: 237 238 my $name_length = $artist->get_column('name_length'); 239 240If you don't like using C<get_column>, you can always create an accessor for 241any of your aliases using either of these: 242 243 # Define accessor manually: 244 sub name_length { shift->get_column('name_length'); } 245 246 # Or use DBIx::Class::AccessorGroup: 247 __PACKAGE__->mk_group_accessors('column' => 'name_length'); 248 249See also L</Using SQL functions on the left hand side of a comparison>. 250 251=head2 SELECT DISTINCT with multiple columns 252 253 my $rs = $schema->resultset('Artist')->search( 254 {}, 255 { 256 columns => [ qw/artist_id name rank/ ], 257 distinct => 1 258 } 259 ); 260 261 my $rs = $schema->resultset('Artist')->search( 262 {}, 263 { 264 columns => [ qw/artist_id name rank/ ], 265 group_by => [ qw/artist_id name rank/ ], 266 } 267 ); 268 269 # Equivalent SQL: 270 # SELECT me.artist_id, me.name, me.rank 271 # FROM artist me 272 # GROUP BY artist_id, name, rank 273 274=head2 SELECT COUNT(DISTINCT colname) 275 276 my $rs = $schema->resultset('Artist')->search( 277 {}, 278 { 279 columns => [ qw/name/ ], 280 distinct => 1 281 } 282 ); 283 284 my $rs = $schema->resultset('Artist')->search( 285 {}, 286 { 287 columns => [ qw/name/ ], 288 group_by => [ qw/name/ ], 289 } 290 ); 291 292 my $count = $rs->count; 293 294 # Equivalent SQL: 295 # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq: 296 297=head2 Grouping results 298 299L<DBIx::Class> supports C<GROUP BY> as follows: 300 301 my $rs = $schema->resultset('Artist')->search( 302 {}, 303 { 304 join => [qw/ cds /], 305 select => [ 'name', { count => 'cds.id' } ], 306 as => [qw/ name cd_count /], 307 group_by => [qw/ name /] 308 } 309 ); 310 311 # Equivalent SQL: 312 # SELECT name, COUNT( cd.id ) FROM artist 313 # LEFT JOIN cd ON artist.id = cd.artist 314 # GROUP BY name 315 316Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you 317are in any way unsure about the use of the attributes above (C< join 318>, C< select >, C< as > and C< group_by >). 319 320=head2 Subqueries 321 322You can write subqueries relatively easily in DBIC. 323 324 my $inside_rs = $schema->resultset('Artist')->search({ 325 name => [ 'Billy Joel', 'Brittany Spears' ], 326 }); 327 328 my $rs = $schema->resultset('CD')->search({ 329 artist_id => { 'IN' => $inside_rs->get_column('id')->as_query }, 330 }); 331 332The usual operators ( =, !=, IN, NOT IN, etc.) are supported. 333 334B<NOTE>: You have to explicitly use '=' when doing an equality comparison. 335The following will B<not> work: 336 337 my $rs = $schema->resultset('CD')->search({ 338 artist_id => $inside_rs->get_column('id')->as_query, # does NOT work 339 }); 340 341=head3 Support 342 343Subqueries are supported in the where clause (first hashref), and in the 344from, select, and +select attributes. 345 346=head3 Correlated subqueries 347 348 my $cdrs = $schema->resultset('CD'); 349 my $rs = $cdrs->search({ 350 year => { 351 '=' => $cdrs->search( 352 { artist_id => { '=' => \'me.artist_id' } }, 353 { alias => 'inner' } 354 )->get_column('year')->max_rs->as_query, 355 }, 356 }); 357 358That creates the following SQL: 359 360 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track 361 FROM cd me 362 WHERE year = ( 363 SELECT MAX(inner.year) 364 FROM cd inner 365 WHERE artist_id = me.artist_id 366 ) 367 368=head2 Predefined searches 369 370You can write your own L<DBIx::Class::ResultSet> class by inheriting from it 371and defining often used searches as methods: 372 373 package My::DBIC::ResultSet::CD; 374 use strict; 375 use warnings; 376 use base 'DBIx::Class::ResultSet'; 377 378 sub search_cds_ordered { 379 my ($self) = @_; 380 381 return $self->search( 382 {}, 383 { order_by => 'name DESC' }, 384 ); 385 } 386 387 1; 388 389If you're using L<DBIx::Class::Schema/load_namespaces>, simply place the file 390into the C<ResultSet> directory next to your C<Result> directory, and it will 391be automatically loaded. 392 393If however you are still using L<DBIx::Class::Schema/load_classes>, first tell 394DBIx::Class to create an instance of the ResultSet class for you, in your 395My::DBIC::Schema::CD class: 396 397 # class definition as normal 398 use base 'DBIx::Class::Core'; 399 __PACKAGE__->table('cd'); 400 401 # tell DBIC to use the custom ResultSet class 402 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD'); 403 404Note that C<resultset_class> must be called after C<load_components> and C<table>, or you will get errors about missing methods. 405 406Then call your new method in your code: 407 408 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); 409 410=head2 Using SQL functions on the left hand side of a comparison 411 412Using SQL functions on the left hand side of a comparison is generally not a 413good idea since it requires a scan of the entire table. (Unless your RDBMS 414supports indexes on expressions - including return values of functions - and 415you create an index on the return value of the function in question.) However, 416it can be accomplished with C<DBIx::Class> when necessary. 417 418If you do not have quoting on, simply include the function in your search 419specification as you would any column: 420 421 $rs->search({ 'YEAR(date_of_birth)' => 1979 }); 422 423With quoting on, or for a more portable solution, use literal SQL values with 424placeholders: 425 426 $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]); 427 428 # Equivalent SQL: 429 # SELECT * FROM employee WHERE YEAR(date_of_birth) = ? 430 431 $rs->search({ 432 name => 'Bob', 433 -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ], 434 }); 435 436 # Equivalent SQL: 437 # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ? 438 439Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part 440should be either the same as the name of the column (do this if the type of the 441return value of the function is the same as the type of the column) or 442otherwise it's essentially a dummy string currently (use C<plain_value> as a 443habit). It is used by L<DBIx::Class> to handle special column types. 444 445See also L<SQL::Abstract/Literal SQL with placeholders and bind values 446(subqueries)>. 447 448=head1 JOINS AND PREFETCHING 449 450=head2 Using joins and prefetch 451 452You can use the C<join> attribute to allow searching on, or sorting your 453results by, one or more columns in a related table. 454 455This requires that you have defined the L<DBIx::Class::Relationship>. For example : 456 457 My::Schema::CD->has_many( artists => 'My::Schema::Artist', 'artist_id'); 458 459To return all CDs matching a particular artist name, you specify the name of the relationship ('artists'): 460 461 my $rs = $schema->resultset('CD')->search( 462 { 463 'artists.name' => 'Bob Marley' 464 }, 465 { 466 join => 'artists', # join the artist table 467 } 468 ); 469 470 # Equivalent SQL: 471 # SELECT cd.* FROM cd 472 # JOIN artist ON cd.artist = artist.id 473 # WHERE artist.name = 'Bob Marley' 474 475In that example both the join, and the condition use the relationship name rather than the table name 476(see L<DBIx::Class::Manual::Joining> for more details on aliasing ). 477 478If required, you can now sort on any column in the related tables by including 479it in your C<order_by> attribute, (again using the aliased relation name rather than table name) : 480 481 my $rs = $schema->resultset('CD')->search( 482 { 483 'artists.name' => 'Bob Marley' 484 }, 485 { 486 join => 'artists', 487 order_by => [qw/ artists.name /] 488 } 489 ); 490 491 # Equivalent SQL: 492 # SELECT cd.* FROM cd 493 # JOIN artist ON cd.artist = artist.id 494 # WHERE artist.name = 'Bob Marley' 495 # ORDER BY artist.name 496 497Note that the C<join> attribute should only be used when you need to search or 498sort using columns in a related table. Joining related tables when you only 499need columns from the main table will make performance worse! 500 501Now let's say you want to display a list of CDs, each with the name of the 502artist. The following will work fine: 503 504 while (my $cd = $rs->next) { 505 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; 506 } 507 508There is a problem however. We have searched both the C<cd> and C<artist> tables 509in our main query, but we have only returned data from the C<cd> table. To get 510the artist name for any of the CD objects returned, L<DBIx::Class> will go back 511to the database: 512 513 SELECT artist.* FROM artist WHERE artist.id = ? 514 515A statement like the one above will run for each and every CD returned by our 516main query. Five CDs, five extra queries. A hundred CDs, one hundred extra 517queries! 518 519Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem. 520This allows you to fetch results from related tables in advance: 521 522 my $rs = $schema->resultset('CD')->search( 523 { 524 'artists.name' => 'Bob Marley' 525 }, 526 { 527 join => 'artists', 528 order_by => [qw/ artists.name /], 529 prefetch => 'artists' # return artist data too! 530 } 531 ); 532 533 # Equivalent SQL (note SELECT from both "cd" and "artist"): 534 # SELECT cd.*, artist.* FROM cd 535 # JOIN artist ON cd.artist = artist.id 536 # WHERE artist.name = 'Bob Marley' 537 # ORDER BY artist.name 538 539The code to print the CD list remains the same: 540 541 while (my $cd = $rs->next) { 542 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; 543 } 544 545L<DBIx::Class> has now prefetched all matching data from the C<artist> table, 546so no additional SQL statements are executed. You now have a much more 547efficient query. 548 549Also note that C<prefetch> should only be used when you know you will 550definitely use data from a related table. Pre-fetching related tables when you 551only need columns from the main table will make performance worse! 552 553=head2 Multiple joins 554 555In the examples above, the C<join> attribute was a scalar. If you 556pass an array reference instead, you can join to multiple tables. In 557this example, we want to limit the search further, using 558C<LinerNotes>: 559 560 # Relationships defined elsewhere: 561 # CD->belongs_to('artist' => 'Artist'); 562 # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); 563 my $rs = $schema->resultset('CD')->search( 564 { 565 'artist.name' => 'Bob Marley' 566 'liner_notes.notes' => { 'like', '%some text%' }, 567 }, 568 { 569 join => [qw/ artist liner_notes /], 570 order_by => [qw/ artist.name /], 571 } 572 ); 573 574 # Equivalent SQL: 575 # SELECT cd.*, artist.*, liner_notes.* FROM cd 576 # JOIN artist ON cd.artist = artist.id 577 # JOIN liner_notes ON cd.id = liner_notes.cd 578 # WHERE artist.name = 'Bob Marley' 579 # ORDER BY artist.name 580 581=head2 Multi-step joins 582 583Sometimes you want to join more than one relationship deep. In this example, 584we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes> 585contain a specific string: 586 587 # Relationships defined elsewhere: 588 # Artist->has_many('cds' => 'CD', 'artist'); 589 # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); 590 591 my $rs = $schema->resultset('Artist')->search( 592 { 593 'liner_notes.notes' => { 'like', '%some text%' }, 594 }, 595 { 596 join => { 597 'cds' => 'liner_notes' 598 } 599 } 600 ); 601 602 # Equivalent SQL: 603 # SELECT artist.* FROM artist 604 # LEFT JOIN cd ON artist.id = cd.artist 605 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd 606 # WHERE liner_notes.notes LIKE '%some text%' 607 608Joins can be nested to an arbitrary level. So if we decide later that we 609want to reduce the number of Artists returned based on who wrote the liner 610notes: 611 612 # Relationship defined elsewhere: 613 # LinerNotes->belongs_to('author' => 'Person'); 614 615 my $rs = $schema->resultset('Artist')->search( 616 { 617 'liner_notes.notes' => { 'like', '%some text%' }, 618 'author.name' => 'A. Writer' 619 }, 620 { 621 join => { 622 'cds' => { 623 'liner_notes' => 'author' 624 } 625 } 626 } 627 ); 628 629 # Equivalent SQL: 630 # SELECT artist.* FROM artist 631 # LEFT JOIN cd ON artist.id = cd.artist 632 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd 633 # LEFT JOIN author ON author.id = liner_notes.author 634 # WHERE liner_notes.notes LIKE '%some text%' 635 # AND author.name = 'A. Writer' 636 637=head2 Multi-step and multiple joins 638 639With various combinations of array and hash references, you can join 640tables in any combination you desire. For example, to join Artist to 641CD and Concert, and join CD to LinerNotes: 642 643 # Relationships defined elsewhere: 644 # Artist->has_many('concerts' => 'Concert', 'artist'); 645 646 my $rs = $schema->resultset('Artist')->search( 647 { }, 648 { 649 join => [ 650 { 651 cds => 'liner_notes' 652 }, 653 'concerts' 654 ], 655 } 656 ); 657 658 # Equivalent SQL: 659 # SELECT artist.* FROM artist 660 # LEFT JOIN cd ON artist.id = cd.artist 661 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd 662 # LEFT JOIN concert ON artist.id = concert.artist 663 664=head2 Multi-step prefetch 665 666C<prefetch> can be nested more than one relationship 667deep using the same syntax as a multi-step join: 668 669 my $rs = $schema->resultset('Tag')->search( 670 {}, 671 { 672 prefetch => { 673 cd => 'artist' 674 } 675 } 676 ); 677 678 # Equivalent SQL: 679 # SELECT tag.*, cd.*, artist.* FROM tag 680 # JOIN cd ON tag.cd = cd.id 681 # JOIN artist ON cd.artist = artist.id 682 683Now accessing our C<cd> and C<artist> relationships does not need additional 684SQL statements: 685 686 my $tag = $rs->first; 687 print $tag->cd->artist->name; 688 689=head1 ROW-LEVEL OPERATIONS 690 691=head2 Retrieving a row object's Schema 692 693It is possible to get a Schema object from a row object like so: 694 695 my $schema = $cd->result_source->schema; 696 # use the schema as normal: 697 my $artist_rs = $schema->resultset('Artist'); 698 699This can be useful when you don't want to pass around a Schema object to every 700method. 701 702=head2 Getting the value of the primary key for the last database insert 703 704AKA getting last_insert_id 705 706Thanks to the core component PK::Auto, this is straightforward: 707 708 my $foo = $rs->create(\%blah); 709 # do more stuff 710 my $id = $foo->id; # foo->my_primary_key_field will also work. 711 712If you are not using autoincrementing primary keys, this will probably 713not work, but then you already know the value of the last primary key anyway. 714 715=head2 Stringification 716 717Employ the standard stringification technique by using the L<overload> 718module. 719 720To make an object stringify itself as a single column, use something 721like this (replace C<name> with the column/method of your choice): 722 723 use overload '""' => sub { shift->name}, fallback => 1; 724 725For more complex stringification, you can use an anonymous subroutine: 726 727 use overload '""' => sub { $_[0]->name . ", " . 728 $_[0]->address }, fallback => 1; 729 730=head3 Stringification Example 731 732Suppose we have two tables: C<Product> and C<Category>. The table 733specifications are: 734 735 Product(id, Description, category) 736 Category(id, Description) 737 738C<category> is a foreign key into the Category table. 739 740If you have a Product object C<$obj> and write something like 741 742 print $obj->category 743 744things will not work as expected. 745 746To obtain, for example, the category description, you should add this 747method to the class defining the Category table: 748 749 use overload "" => sub { 750 my $self = shift; 751 752 return $self->Description; 753 }, fallback => 1; 754 755=head2 Want to know if find_or_create found or created a row? 756 757Just use C<find_or_new> instead, then check C<in_storage>: 758 759 my $obj = $rs->find_or_new({ blah => 'blarg' }); 760 unless ($obj->in_storage) { 761 $obj->insert; 762 # do whatever else you wanted if it was a new row 763 } 764 765=head2 Static sub-classing DBIx::Class result classes 766 767AKA adding additional relationships/methods/etc. to a model for a 768specific usage of the (shared) model. 769 770B<Schema definition> 771 772 package My::App::Schema; 773 774 use base 'DBIx::Class::Schema'; 775 776 # load subclassed classes from My::App::Schema::Result/ResultSet 777 __PACKAGE__->load_namespaces; 778 779 # load classes from shared model 780 load_classes({ 781 'My::Shared::Model::Result' => [qw/ 782 Foo 783 Bar 784 /]}); 785 786 1; 787 788B<Result-Subclass definition> 789 790 package My::App::Schema::Result::Baz; 791 792 use strict; 793 use warnings; 794 use base 'My::Shared::Model::Result::Baz'; 795 796 # WARNING: Make sure you call table() again in your subclass, 797 # otherwise DBIx::Class::ResultSourceProxy::Table will not be called 798 # and the class name is not correctly registered as a source 799 __PACKAGE__->table('baz'); 800 801 sub additional_method { 802 return "I'm an additional method only needed by this app"; 803 } 804 805 1; 806 807=head2 Dynamic Sub-classing DBIx::Class proxy classes 808 809AKA multi-class object inflation from one table 810 811L<DBIx::Class> classes are proxy classes, therefore some different 812techniques need to be employed for more than basic subclassing. In 813this example we have a single user table that carries a boolean bit 814for admin. We would like like to give the admin users 815objects (L<DBIx::Class::Row>) the same methods as a regular user but 816also special admin only methods. It doesn't make sense to create two 817separate proxy-class files for this. We would be copying all the user 818methods into the Admin class. There is a cleaner way to accomplish 819this. 820 821Overriding the C<inflate_result> method within the User proxy-class 822gives us the effect we want. This method is called by 823L<DBIx::Class::ResultSet> when inflating a result from storage. So we 824grab the object being returned, inspect the values we are looking for, 825bless it if it's an admin object, and then return it. See the example 826below: 827 828B<Schema Definition> 829 830 package My::Schema; 831 832 use base qw/DBIx::Class::Schema/; 833 834 __PACKAGE__->load_namespaces; 835 836 1; 837 838 839B<Proxy-Class definitions> 840 841 package My::Schema::Result::User; 842 843 use strict; 844 use warnings; 845 use base qw/DBIx::Class::Core/; 846 847 ### Define what our admin class is, for ensure_class_loaded() 848 my $admin_class = __PACKAGE__ . '::Admin'; 849 850 __PACKAGE__->table('users'); 851 852 __PACKAGE__->add_columns(qw/user_id email password 853 firstname lastname active 854 admin/); 855 856 __PACKAGE__->set_primary_key('user_id'); 857 858 sub inflate_result { 859 my $self = shift; 860 my $ret = $self->next::method(@_); 861 if( $ret->admin ) {### If this is an admin, rebless for extra functions 862 $self->ensure_class_loaded( $admin_class ); 863 bless $ret, $admin_class; 864 } 865 return $ret; 866 } 867 868 sub hello { 869 print "I am a regular user.\n"; 870 return ; 871 } 872 873 1; 874 875 876 package My::Schema::Result::User::Admin; 877 878 use strict; 879 use warnings; 880 use base qw/My::Schema::Result::User/; 881 882 # This line is important 883 __PACKAGE__->table('users'); 884 885 sub hello 886 { 887 print "I am an admin.\n"; 888 return; 889 } 890 891 sub do_admin_stuff 892 { 893 print "I am doing admin stuff\n"; 894 return ; 895 } 896 897 1; 898 899B<Test File> test.pl 900 901 use warnings; 902 use strict; 903 use My::Schema; 904 905 my $user_data = { email => 'someguy@place.com', 906 password => 'pass1', 907 admin => 0 }; 908 909 my $admin_data = { email => 'someadmin@adminplace.com', 910 password => 'pass2', 911 admin => 1 }; 912 913 my $schema = My::Schema->connection('dbi:Pg:dbname=test'); 914 915 $schema->resultset('User')->create( $user_data ); 916 $schema->resultset('User')->create( $admin_data ); 917 918 ### Now we search for them 919 my $user = $schema->resultset('User')->single( $user_data ); 920 my $admin = $schema->resultset('User')->single( $admin_data ); 921 922 print ref $user, "\n"; 923 print ref $admin, "\n"; 924 925 print $user->password , "\n"; # pass1 926 print $admin->password , "\n";# pass2; inherited from User 927 print $user->hello , "\n";# I am a regular user. 928 print $admin->hello, "\n";# I am an admin. 929 930 ### The statement below will NOT print 931 print "I can do admin stuff\n" if $user->can('do_admin_stuff'); 932 ### The statement below will print 933 print "I can do admin stuff\n" if $admin->can('do_admin_stuff'); 934 935Alternatively you can use L<DBIx::Class::DynamicSubclass> that implements 936exactly the above functionality. 937 938=head2 Skip row object creation for faster results 939 940DBIx::Class is not built for speed, it's built for convenience and 941ease of use, but sometimes you just need to get the data, and skip the 942fancy objects. 943 944To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>. 945 946 my $rs = $schema->resultset('CD'); 947 948 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); 949 950 my $hash_ref = $rs->find(1); 951 952Wasn't that easy? 953 954Beware, changing the Result class using 955L<DBIx::Class::ResultSet/result_class> will replace any existing class 956completely including any special components loaded using 957load_components, eg L<DBIx::Class::InflateColumn::DateTime>. 958 959=head2 Get raw data for blindingly fast results 960 961If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution 962above is not fast enough for you, you can use a DBIx::Class to return values 963exactly as they come out of the database with none of the convenience methods 964wrapped round them. 965 966This is used like so: 967 968 my $cursor = $rs->cursor 969 while (my @vals = $cursor->next) { 970 # use $val[0..n] here 971 } 972 973You will need to map the array offsets to particular columns (you can 974use the L<DBIx::Class::ResultSet/select> attribute of L<DBIx::Class::ResultSet/search> to force ordering). 975 976=head1 RESULTSET OPERATIONS 977 978=head2 Getting Schema from a ResultSet 979 980To get the L<DBIx::Class::Schema> object from a ResultSet, do the following: 981 982 $rs->result_source->schema 983 984=head2 Getting Columns Of Data 985 986AKA Aggregating Data 987 988If you want to find the sum of a particular column there are several 989ways, the obvious one is to use search: 990 991 my $rs = $schema->resultset('Items')->search( 992 {}, 993 { 994 select => [ { sum => 'Cost' } ], 995 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL 996 } 997 ); 998 my $tc = $rs->first->get_column('total_cost'); 999 1000Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets 1001returned when you ask the C<ResultSet> for a column using 1002C<get_column>: 1003 1004 my $cost = $schema->resultset('Items')->get_column('Cost'); 1005 my $tc = $cost->sum; 1006 1007With this you can also do: 1008 1009 my $minvalue = $cost->min; 1010 my $maxvalue = $cost->max; 1011 1012Or just iterate through the values of this column only: 1013 1014 while ( my $c = $cost->next ) { 1015 print $c; 1016 } 1017 1018 foreach my $c ($cost->all) { 1019 print $c; 1020 } 1021 1022C<ResultSetColumn> only has a limited number of built-in functions. If 1023you need one that it doesn't have, then you can use the C<func> method 1024instead: 1025 1026 my $avg = $cost->func('AVERAGE'); 1027 1028This will cause the following SQL statement to be run: 1029 1030 SELECT AVERAGE(Cost) FROM Items me 1031 1032Which will of course only work if your database supports this function. 1033See L<DBIx::Class::ResultSetColumn> for more documentation. 1034 1035=head2 Creating a result set from a set of rows 1036 1037Sometimes you have a (set of) row objects that you want to put into a 1038resultset without the need to hit the DB again. You can do that by using the 1039L<set_cache|DBIx::Class::Resultset/set_cache> method: 1040 1041 my @uploadable_groups; 1042 while (my $group = $groups->next) { 1043 if ($group->can_upload($self)) { 1044 push @uploadable_groups, $group; 1045 } 1046 } 1047 my $new_rs = $self->result_source->resultset; 1048 $new_rs->set_cache(\@uploadable_groups); 1049 return $new_rs; 1050 1051 1052=head1 USING RELATIONSHIPS 1053 1054=head2 Create a new row in a related table 1055 1056 my $author = $book->create_related('author', { name => 'Fred'}); 1057 1058=head2 Search in a related table 1059 1060Only searches for books named 'Titanic' by the author in $author. 1061 1062 my $books_rs = $author->search_related('books', { name => 'Titanic' }); 1063 1064=head2 Delete data in a related table 1065 1066Deletes only the book named Titanic by the author in $author. 1067 1068 $author->delete_related('books', { name => 'Titanic' }); 1069 1070=head2 Ordering a relationship result set 1071 1072If you always want a relation to be ordered, you can specify this when you 1073create the relationship. 1074 1075To order C<< $book->pages >> by descending page_number, create the relation 1076as follows: 1077 1078 __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } ); 1079 1080=head2 Filtering a relationship result set 1081 1082If you want to get a filtered result set, you can just add add to $attr as follows: 1083 1084 __PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } ); 1085 1086=head2 Many-to-many relationships 1087 1088This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>: 1089 1090 package My::User; 1091 use base 'DBIx::Class::Core'; 1092 __PACKAGE__->table('user'); 1093 __PACKAGE__->add_columns(qw/id name/); 1094 __PACKAGE__->set_primary_key('id'); 1095 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user'); 1096 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address'); 1097 1098 package My::UserAddress; 1099 use base 'DBIx::Class::Core'; 1100 __PACKAGE__->table('user_address'); 1101 __PACKAGE__->add_columns(qw/user address/); 1102 __PACKAGE__->set_primary_key(qw/user address/); 1103 __PACKAGE__->belongs_to('user' => 'My::User'); 1104 __PACKAGE__->belongs_to('address' => 'My::Address'); 1105 1106 package My::Address; 1107 use base 'DBIx::Class::Core'; 1108 __PACKAGE__->table('address'); 1109 __PACKAGE__->add_columns(qw/id street town area_code country/); 1110 __PACKAGE__->set_primary_key('id'); 1111 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address'); 1112 __PACKAGE__->many_to_many('users' => 'user_address', 'user'); 1113 1114 $rs = $user->addresses(); # get all addresses for a user 1115 $rs = $address->users(); # get all users for an address 1116 1117 my $address = $user->add_to_addresses( # returns a My::Address instance, 1118 # NOT a My::UserAddress instance! 1119 { 1120 country => 'United Kingdom', 1121 area_code => 'XYZ', 1122 town => 'London', 1123 street => 'Sesame', 1124 } 1125 ); 1126 1127=head2 Relationships across DB schemas 1128 1129Mapping relationships across L<DB schemas|DBIx::Class::Manual::Glossary/DB schema> 1130is easy as long as the schemas themselves are all accessible via the same DBI 1131connection. In most cases, this means that they are on the same database host 1132as each other and your connecting database user has the proper permissions to them. 1133 1134To accomplish this one only needs to specify the DB schema name in the table 1135declaration, like so... 1136 1137 package MyDatabase::Main::Artist; 1138 use base qw/DBIx::Class::Core/; 1139 1140 __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause 1141 1142 __PACKAGE__->add_columns(qw/ artist_id name /); 1143 __PACKAGE__->set_primary_key('artist_id'); 1144 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd'); 1145 1146 1; 1147 1148Whatever string you specify there will be used to build the "FROM" clause in SQL 1149queries. 1150 1151The big drawback to this is you now have DB schema names hardcoded in your 1152class files. This becomes especially troublesome if you have multiple instances 1153of your application to support a change lifecycle (e.g. DEV, TEST, PROD) and 1154the DB schemas are named based on the environment (e.g. database1_dev). 1155 1156However, one can dynamically "map" to the proper DB schema by overriding the 1157L<connection|DBIx::Class::Schama/connection> method in your Schema class and 1158building a renaming facility, like so: 1159 1160 package MyDatabase::Schema; 1161 use Moose; 1162 1163 extends 'DBIx::Class::Schema'; 1164 1165 around connection => sub { 1166 my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ ); 1167 1168 my $postfix = delete $attr->{schema_name_postfix}; 1169 1170 $inner->(@_); 1171 1172 if ( $postfix ) { 1173 $self->append_db_name($postfix); 1174 } 1175 }; 1176 1177 sub append_db_name { 1178 my ( $self, $postfix ) = @_; 1179 1180 my @sources_with_db 1181 = grep 1182 { $_->name =~ /^\w+\./mx } 1183 map 1184 { $self->source($_) } 1185 $self->sources; 1186 1187 foreach my $source (@sources_with_db) { 1188 my $name = $source->name; 1189 $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx; 1190 1191 $source->name($name); 1192 } 1193 } 1194 1195 1; 1196 1197By overridding the L<connection|DBIx::Class::Schama/connection> 1198method and extracting a custom option from the provided \%attr hashref one can 1199then simply iterate over all the Schema's ResultSources, renaming them as 1200needed. 1201 1202To use this facility, simply add or modify the \%attr hashref that is passed to 1203L<connection|DBIx::Class::Schama/connect>, as follows: 1204 1205 my $schema 1206 = MyDatabase::Schema->connect( 1207 $dsn, 1208 $user, 1209 $pass, 1210 { 1211 schema_name_postfix => '_dev' 1212 # ... Other options as desired ... 1213 }) 1214 1215Obviously, one could accomplish even more advanced mapping via a hash map or a 1216callback routine. 1217 1218=head1 TRANSACTIONS 1219 1220As of version 0.04001, there is improved transaction support in 1221L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an 1222example of the recommended way to use it: 1223 1224 my $genus = $schema->resultset('Genus')->find(12); 1225 1226 my $coderef2 = sub { 1227 $genus->extinct(1); 1228 $genus->update; 1229 }; 1230 1231 my $coderef1 = sub { 1232 $genus->add_to_species({ name => 'troglodyte' }); 1233 $genus->wings(2); 1234 $genus->update; 1235 $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit 1236 return $genus->species; 1237 }; 1238 1239 my $rs; 1240 eval { 1241 $rs = $schema->txn_do($coderef1); 1242 }; 1243 1244 if ($@) { # Transaction failed 1245 die "the sky is falling!" # 1246 if ($@ =~ /Rollback failed/); # Rollback failed 1247 1248 deal_with_failed_transaction(); 1249 } 1250 1251Nested transactions will work as expected. That is, only the outermost 1252transaction will actually issue a commit to the $dbh, and a rollback 1253at any level of any transaction will cause the entire nested 1254transaction to fail. 1255 1256=head2 Nested transactions and auto-savepoints 1257 1258If savepoints are supported by your RDBMS, it is possible to achieve true 1259nested transactions with minimal effort. To enable auto-savepoints via nested 1260transactions, supply the C<< auto_savepoint = 1 >> connection attribute. 1261 1262Here is an example of true nested transactions. In the example, we start a big 1263task which will create several rows. Generation of data for each row is a 1264fragile operation and might fail. If we fail creating something, depending on 1265the type of failure, we want to abort the whole task, or only skip the failed 1266row. 1267 1268 my $schema = MySchema->connect("dbi:Pg:dbname=my_db"); 1269 1270 # Start a transaction. Every database change from here on will only be 1271 # committed into the database if the eval block succeeds. 1272 eval { 1273 $schema->txn_do(sub { 1274 # SQL: BEGIN WORK; 1275 1276 my $job = $schema->resultset('Job')->create({ name=> 'big job' }); 1277 # SQL: INSERT INTO job ( name) VALUES ( 'big job' ); 1278 1279 for (1..10) { 1280 1281 # Start a nested transaction, which in fact sets a savepoint. 1282 eval { 1283 $schema->txn_do(sub { 1284 # SQL: SAVEPOINT savepoint_0; 1285 1286 my $thing = $schema->resultset('Thing')->create({ job=>$job->id }); 1287 # SQL: INSERT INTO thing ( job) VALUES ( 1 ); 1288 1289 if (rand > 0.8) { 1290 # This will generate an error, thus setting $@ 1291 1292 $thing->update({force_fail=>'foo'}); 1293 # SQL: UPDATE thing SET force_fail = 'foo' 1294 # WHERE ( id = 42 ); 1295 } 1296 }); 1297 }; 1298 if ($@) { 1299 # SQL: ROLLBACK TO SAVEPOINT savepoint_0; 1300 1301 # There was an error while creating a $thing. Depending on the error 1302 # we want to abort the whole transaction, or only rollback the 1303 # changes related to the creation of this $thing 1304 1305 # Abort the whole job 1306 if ($@ =~ /horrible_problem/) { 1307 print "something horrible happend, aborting job!"; 1308 die $@; # rethrow error 1309 } 1310 1311 # Ignore this $thing, report the error, and continue with the 1312 # next $thing 1313 print "Cannot create thing: $@"; 1314 } 1315 # There was no error, so save all changes since the last 1316 # savepoint. 1317 1318 # SQL: RELEASE SAVEPOINT savepoint_0; 1319 } 1320 }); 1321 }; 1322 if ($@) { 1323 # There was an error while handling the $job. Rollback all changes 1324 # since the transaction started, including the already committed 1325 # ('released') savepoints. There will be neither a new $job nor any 1326 # $thing entry in the database. 1327 1328 # SQL: ROLLBACK; 1329 1330 print "ERROR: $@\n"; 1331 } 1332 else { 1333 # There was no error while handling the $job. Commit all changes. 1334 # Only now other connections can see the newly created $job and 1335 # @things. 1336 1337 # SQL: COMMIT; 1338 1339 print "Ok\n"; 1340 } 1341 1342In this example it might be hard to see where the rollbacks, releases and 1343commits are happening, but it works just the same as for plain L<<txn_do>>: If 1344the C<eval>-block around C<txn_do> fails, a rollback is issued. If the C<eval> 1345succeeds, the transaction is committed (or the savepoint released). 1346 1347While you can get more fine-grained controll using C<svp_begin>, C<svp_release> 1348and C<svp_rollback>, it is strongly recommended to use C<txn_do> with coderefs. 1349 1350=head1 SQL 1351 1352=head2 Creating Schemas From An Existing Database 1353 1354L<DBIx::Class::Schema::Loader> will connect to a database and create a 1355L<DBIx::Class::Schema> and associated sources by examining the database. 1356 1357The recommend way of achieving this is to use the 1358L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method: 1359 1360 perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \ 1361 -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])' 1362 1363This will create a tree of files rooted at C<./lib/My/Schema/> containing 1364source definitions for all the tables found in the C<foo> database. 1365 1366=head2 Creating DDL SQL 1367 1368The following functionality requires you to have L<SQL::Translator> 1369(also known as "SQL Fairy") installed. 1370 1371To create a set of database-specific .sql files for the above schema: 1372 1373 my $schema = My::Schema->connect($dsn); 1374 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'], 1375 '0.1', 1376 './dbscriptdir/' 1377 ); 1378 1379By default this will create schema files in the current directory, for 1380MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm. 1381 1382To create a new database using the schema: 1383 1384 my $schema = My::Schema->connect($dsn); 1385 $schema->deploy({ add_drop_table => 1}); 1386 1387To import created .sql files using the mysql client: 1388 1389 mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql 1390 1391To create C<ALTER TABLE> conversion scripts to update a database to a 1392newer version of your schema at a later point, first set a new 1393C<$VERSION> in your Schema file, then: 1394 1395 my $schema = My::Schema->connect($dsn); 1396 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'], 1397 '0.2', 1398 '/dbscriptdir/', 1399 '0.1' 1400 ); 1401 1402This will produce new database-specific .sql files for the new version 1403of the schema, plus scripts to convert from version 0.1 to 0.2. This 1404requires that the files for 0.1 as created above are available in the 1405given directory to diff against. 1406 1407=head2 Select from dual 1408 1409Dummy tables are needed by some databases to allow calling functions 1410or expressions that aren't based on table content, for examples of how 1411this applies to various database types, see: 1412L<http://troels.arvin.dk/db/rdbms/#other-dummy_table>. 1413 1414Note: If you're using Oracles dual table don't B<ever> do anything 1415other than a select, if you CRUD on your dual table you *will* break 1416your database. 1417 1418Make a table class as you would for any other table 1419 1420 package MyAppDB::Dual; 1421 use strict; 1422 use warnings; 1423 use base 'DBIx::Class::Core'; 1424 __PACKAGE__->table("Dual"); 1425 __PACKAGE__->add_columns( 1426 "dummy", 1427 { data_type => "VARCHAR2", is_nullable => 0, size => 1 }, 1428 ); 1429 1430Once you've loaded your table class select from it using C<select> 1431and C<as> instead of C<columns> 1432 1433 my $rs = $schema->resultset('Dual')->search(undef, 1434 { select => [ 'sydate' ], 1435 as => [ 'now' ] 1436 }, 1437 ); 1438 1439All you have to do now is be careful how you access your resultset, the below 1440will not work because there is no column called 'now' in the Dual table class 1441 1442 while (my $dual = $rs->next) { 1443 print $dual->now."\n"; 1444 } 1445 # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23. 1446 1447You could of course use 'dummy' in C<as> instead of 'now', or C<add_columns> to 1448your Dual class for whatever you wanted to select from dual, but that's just 1449silly, instead use C<get_column> 1450 1451 while (my $dual = $rs->next) { 1452 print $dual->get_column('now')."\n"; 1453 } 1454 1455Or use C<cursor> 1456 1457 my $cursor = $rs->cursor; 1458 while (my @vals = $cursor->next) { 1459 print $vals[0]."\n"; 1460 } 1461 1462In case you're going to use this "trick" together with L<DBIx::Class::Schema/deploy> or 1463L<DBIx::Class::Schema/create_ddl_dir> a table called "dual" will be created in your 1464current schema. This would overlap "sys.dual" and you could not fetch "sysdate" or 1465"sequence.nextval" anymore from dual. To avoid this problem, just tell 1466L<SQL::Translator> to not create table dual: 1467 1468 my $sqlt_args = { 1469 add_drop_table => 1, 1470 parser_args => { sources => [ grep $_ ne 'Dual', schema->sources ] }, 1471 }; 1472 $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args ); 1473 1474Or use L<DBIx::Class::ResultClass::HashRefInflator> 1475 1476 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); 1477 while ( my $dual = $rs->next ) { 1478 print $dual->{now}."\n"; 1479 } 1480 1481Here are some example C<select> conditions to illustrate the different syntax 1482you could use for doing stuff like 1483C<oracles.heavily(nested(functions_can('take', 'lots'), OF), 'args')> 1484 1485 # get a sequence value 1486 select => [ 'A_SEQ.nextval' ], 1487 1488 # get create table sql 1489 select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ], 1490 1491 # get a random num between 0 and 100 1492 select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ], 1493 1494 # what year is it? 1495 select => [ { 'extract' => [ \'year from sysdate' ] } ], 1496 1497 # do some math 1498 select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}], 1499 1500 # which day of the week were you born on? 1501 select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}], 1502 1503 # select 16 rows from dual 1504 select => [ "'hello'" ], 1505 as => [ 'world' ], 1506 group_by => [ 'cube( 1, 2, 3, 4 )' ], 1507 1508 1509 1510=head2 Adding Indexes And Functions To Your SQL 1511 1512Often you will want indexes on columns on your table to speed up searching. To 1513do this, create a method called C<sqlt_deploy_hook> in the relevant source 1514class (refer to the advanced 1515L<callback system|DBIx::Class::ResultSource/sqlt_deploy_callback> if you wish 1516to share a hook between multiple sources): 1517 1518 package My::Schema::Result::Artist; 1519 1520 __PACKAGE__->table('artist'); 1521 __PACKAGE__->add_columns(id => { ... }, name => { ... }) 1522 1523 sub sqlt_deploy_hook { 1524 my ($self, $sqlt_table) = @_; 1525 1526 $sqlt_table->add_index(name => 'idx_name', fields => ['name']); 1527 } 1528 1529 1; 1530 1531Sometimes you might want to change the index depending on the type of the 1532database for which SQL is being generated: 1533 1534 my ($db_type = $sqlt_table->schema->translator->producer_type) 1535 =~ s/^SQL::Translator::Producer:://; 1536 1537You can also add hooks to the schema level to stop certain tables being 1538created: 1539 1540 package My::Schema; 1541 1542 ... 1543 1544 sub sqlt_deploy_hook { 1545 my ($self, $sqlt_schema) = @_; 1546 1547 $sqlt_schema->drop_table('table_name'); 1548 } 1549 1550You could also add views, procedures or triggers to the output using 1551L<SQL::Translator::Schema/add_view>, 1552L<SQL::Translator::Schema/add_procedure> or 1553L<SQL::Translator::Schema/add_trigger>. 1554 1555 1556=head2 Schema versioning 1557 1558The following example shows simplistically how you might use DBIx::Class to 1559deploy versioned schemas to your customers. The basic process is as follows: 1560 1561=over 4 1562 1563=item 1. 1564 1565Create a DBIx::Class schema 1566 1567=item 2. 1568 1569Save the schema 1570 1571=item 3. 1572 1573Deploy to customers 1574 1575=item 4. 1576 1577Modify schema to change functionality 1578 1579=item 5. 1580 1581Deploy update to customers 1582 1583=back 1584 1585B<Create a DBIx::Class schema> 1586 1587This can either be done manually, or generated from an existing database as 1588described under L</Creating Schemas From An Existing Database> 1589 1590B<Save the schema> 1591 1592Call L<DBIx::Class::Schema/create_ddl_dir> as above under L</Creating DDL SQL>. 1593 1594B<Deploy to customers> 1595 1596There are several ways you could deploy your schema. These are probably 1597beyond the scope of this recipe, but might include: 1598 1599=over 4 1600 1601=item 1. 1602 1603Require customer to apply manually using their RDBMS. 1604 1605=item 2. 1606 1607Package along with your app, making database dump/schema update/tests 1608all part of your install. 1609 1610=back 1611 1612B<Modify the schema to change functionality> 1613 1614As your application evolves, it may be necessary to modify your schema 1615to change functionality. Once the changes are made to your schema in 1616DBIx::Class, export the modified schema and the conversion scripts as 1617in L</Creating DDL SQL>. 1618 1619B<Deploy update to customers> 1620 1621Add the L<DBIx::Class::Schema::Versioned> schema component to your 1622Schema class. This will add a new table to your database called 1623C<dbix_class_schema_vesion> which will keep track of which version is installed 1624and warn if the user tries to run a newer schema version than the 1625database thinks it has. 1626 1627Alternatively, you can send the conversion SQL scripts to your 1628customers as above. 1629 1630=head2 Setting quoting for the generated SQL 1631 1632If the database contains column names with spaces and/or reserved words, they 1633need to be quoted in the SQL queries. This is done using: 1634 1635 $schema->storage->sql_maker->quote_char([ qw/[ ]/] ); 1636 $schema->storage->sql_maker->name_sep('.'); 1637 1638The first sets the quote characters. Either a pair of matching 1639brackets, or a C<"> or C<'>: 1640 1641 $schema->storage->sql_maker->quote_char('"'); 1642 1643Check the documentation of your database for the correct quote 1644characters to use. C<name_sep> needs to be set to allow the SQL 1645generator to put the quotes the correct place. 1646 1647In most cases you should set these as part of the arguments passed to 1648L<DBIx::Class::Schema/connect>: 1649 1650 my $schema = My::Schema->connect( 1651 'dbi:mysql:my_db', 1652 'db_user', 1653 'db_password', 1654 { 1655 quote_char => '"', 1656 name_sep => '.' 1657 } 1658 ) 1659 1660In some cases, quoting will be required for all users of a schema. To enforce 1661this, you can also overload the C<connection> method for your schema class: 1662 1663 sub connection { 1664 my $self = shift; 1665 my $rv = $self->next::method( @_ ); 1666 $rv->storage->sql_maker->quote_char([ qw/[ ]/ ]); 1667 $rv->storage->sql_maker->name_sep('.'); 1668 return $rv; 1669 } 1670 1671=head2 Setting limit dialect for SQL::Abstract::Limit 1672 1673In some cases, SQL::Abstract::Limit cannot determine the dialect of 1674the remote SQL server by looking at the database handle. This is a 1675common problem when using the DBD::JDBC, since the DBD-driver only 1676know that in has a Java-driver available, not which JDBC driver the 1677Java component has loaded. This specifically sets the limit_dialect 1678to Microsoft SQL-server (See more names in SQL::Abstract::Limit 1679-documentation. 1680 1681 __PACKAGE__->storage->sql_maker->limit_dialect('mssql'); 1682 1683The JDBC bridge is one way of getting access to a MSSQL server from a platform 1684that Microsoft doesn't deliver native client libraries for. (e.g. Linux) 1685 1686The limit dialect can also be set at connect time by specifying a 1687C<limit_dialect> key in the final hash as shown above. 1688 1689=head2 Working with PostgreSQL array types 1690 1691You can also assign values to PostgreSQL array columns by passing array 1692references in the C<\%columns> (C<\%vals>) hashref of the 1693L<DBIx::Class::ResultSet/create> and L<DBIx::Class::Row/update> family of 1694methods: 1695 1696 $resultset->create({ 1697 numbers => [1, 2, 3] 1698 }); 1699 1700 $row->update( 1701 { 1702 numbers => [1, 2, 3] 1703 }, 1704 { 1705 day => '2008-11-24' 1706 } 1707 ); 1708 1709In conditions (e.g. C<\%cond> in the L<DBIx::Class::ResultSet/search> family of 1710methods) you cannot directly use array references (since this is interpreted as 1711a list of values to be C<OR>ed), but you can use the following syntax to force 1712passing them as bind values: 1713 1714 $resultset->search( 1715 { 1716 numbers => \[ '= ?', [numbers => [1, 2, 3]] ] 1717 } 1718 ); 1719 1720See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with 1721placeholders and bind values (subqueries)> for more explanation. Note that 1722L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass 1723the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in 1724arrayrefs together with the column name, like this: C<< [column_name => value] 1725>>. 1726 1727=head1 BOOTSTRAPPING/MIGRATING 1728 1729=head2 Easy migration from class-based to schema-based setup 1730 1731You want to start using the schema-based approach to L<DBIx::Class> 1732(see L<SchemaIntro.pod>), but have an established class-based setup with lots 1733of existing classes that you don't want to move by hand. Try this nifty script 1734instead: 1735 1736 use MyDB; 1737 use SQL::Translator; 1738 1739 my $schema = MyDB->schema_instance; 1740 1741 my $translator = SQL::Translator->new( 1742 debug => $debug || 0, 1743 trace => $trace || 0, 1744 no_comments => $no_comments || 0, 1745 show_warnings => $show_warnings || 0, 1746 add_drop_table => $add_drop_table || 0, 1747 validate => $validate || 0, 1748 parser_args => { 1749 'DBIx::Schema' => $schema, 1750 }, 1751 producer_args => { 1752 'prefix' => 'My::Schema', 1753 }, 1754 ); 1755 1756 $translator->parser('SQL::Translator::Parser::DBIx::Class'); 1757 $translator->producer('SQL::Translator::Producer::DBIx::Class::File'); 1758 1759 my $output = $translator->translate(@args) or die 1760 "Error: " . $translator->error; 1761 1762 print $output; 1763 1764You could use L<Module::Find> to search for all subclasses in the MyDB::* 1765namespace, which is currently left as an exercise for the reader. 1766 1767=head1 OVERLOADING METHODS 1768 1769L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of 1770method calls, useful for things like default values and triggers. You have to 1771use calls to C<next::method> to overload methods. More information on using 1772L<Class::C3> with L<DBIx::Class> can be found in 1773L<DBIx::Class::Manual::Component>. 1774 1775=head2 Setting default values for a row 1776 1777It's as simple as overriding the C<new> method. Note the use of 1778C<next::method>. 1779 1780 sub new { 1781 my ( $class, $attrs ) = @_; 1782 1783 $attrs->{foo} = 'bar' unless defined $attrs->{foo}; 1784 1785 my $new = $class->next::method($attrs); 1786 1787 return $new; 1788 } 1789 1790For more information about C<next::method>, look in the L<Class::C3> 1791documentation. See also L<DBIx::Class::Manual::Component> for more 1792ways to write your own base classes to do this. 1793 1794People looking for ways to do "triggers" with DBIx::Class are probably 1795just looking for this. 1796 1797=head2 Changing one field whenever another changes 1798 1799For example, say that you have three columns, C<id>, C<number>, and 1800C<squared>. You would like to make changes to C<number> and have 1801C<squared> be automagically set to the value of C<number> squared. 1802You can accomplish this by overriding C<store_column>: 1803 1804 sub store_column { 1805 my ( $self, $name, $value ) = @_; 1806 if ($name eq 'number') { 1807 $self->squared($value * $value); 1808 } 1809 $self->next::method($name, $value); 1810 } 1811 1812Note that the hard work is done by the call to C<next::method>, which 1813redispatches your call to store_column in the superclass(es). 1814 1815=head2 Automatically creating related objects 1816 1817You might have a class C<Artist> which has many C<CD>s. Further, you 1818want to create a C<CD> object every time you insert an C<Artist> object. 1819You can accomplish this by overriding C<insert> on your objects: 1820 1821 sub insert { 1822 my ( $self, @args ) = @_; 1823 $self->next::method(@args); 1824 $self->cds->new({})->fill_from_artist($self)->insert; 1825 return $self; 1826 } 1827 1828where C<fill_from_artist> is a method you specify in C<CD> which sets 1829values in C<CD> based on the data in the C<Artist> object you pass in. 1830 1831=head2 Wrapping/overloading a column accessor 1832 1833B<Problem:> 1834 1835Say you have a table "Camera" and want to associate a description 1836with each camera. For most cameras, you'll be able to generate the description from 1837the other columns. However, in a few special cases you may want to associate a 1838custom description with a camera. 1839 1840B<Solution:> 1841 1842In your database schema, define a description field in the "Camera" table that 1843can contain text and null values. 1844 1845In DBIC, we'll overload the column accessor to provide a sane default if no 1846custom description is defined. The accessor will either return or generate the 1847description, depending on whether the field is null or not. 1848 1849First, in your "Camera" schema class, define the description field as follows: 1850 1851 __PACKAGE__->add_columns(description => { accessor => '_description' }); 1852 1853Next, we'll define the accessor-wrapper subroutine: 1854 1855 sub description { 1856 my $self = shift; 1857 1858 # If there is an update to the column, we'll let the original accessor 1859 # deal with it. 1860 return $self->_description(@_) if @_; 1861 1862 # Fetch the column value. 1863 my $description = $self->_description; 1864 1865 # If there's something in the description field, then just return that. 1866 return $description if defined $description && length $descripton; 1867 1868 # Otherwise, generate a description. 1869 return $self->generate_description; 1870 } 1871 1872=head1 DEBUGGING AND PROFILING 1873 1874=head2 DBIx::Class objects with Data::Dumper 1875 1876L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can 1877be hard to find the pertinent data in all the data it can generate. 1878Specifically, if one naively tries to use it like so, 1879 1880 use Data::Dumper; 1881 1882 my $cd = $schema->resultset('CD')->find(1); 1883 print Dumper($cd); 1884 1885several pages worth of data from the CD object's schema and result source will 1886be dumped to the screen. Since usually one is only interested in a few column 1887values of the object, this is not very helpful. 1888 1889Luckily, it is possible to modify the data before L<Data::Dumper> outputs 1890it. Simply define a hook that L<Data::Dumper> will call on the object before 1891dumping it. For example, 1892 1893 package My::DB::CD; 1894 1895 sub _dumper_hook { 1896 $_[0] = bless { 1897 %{ $_[0] }, 1898 result_source => undef, 1899 }, ref($_[0]); 1900 } 1901 1902 [...] 1903 1904 use Data::Dumper; 1905 1906 local $Data::Dumper::Freezer = '_dumper_hook'; 1907 1908 my $cd = $schema->resultset('CD')->find(1); 1909 print Dumper($cd); 1910 # dumps $cd without its ResultSource 1911 1912If the structure of your schema is such that there is a common base class for 1913all your table classes, simply put a method similar to C<_dumper_hook> in the 1914base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper> 1915will automagically clean up your data before printing it. See 1916L<Data::Dumper/EXAMPLES> for more information. 1917 1918=head2 Profiling 1919 1920When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL 1921executed as well as notifications of query completion and transaction 1922begin/commit. If you'd like to profile the SQL you can subclass the 1923L<DBIx::Class::Storage::Statistics> class and write your own profiling 1924mechanism: 1925 1926 package My::Profiler; 1927 use strict; 1928 1929 use base 'DBIx::Class::Storage::Statistics'; 1930 1931 use Time::HiRes qw(time); 1932 1933 my $start; 1934 1935 sub query_start { 1936 my $self = shift(); 1937 my $sql = shift(); 1938 my $params = @_; 1939 1940 $self->print("Executing $sql: ".join(', ', @params)."\n"); 1941 $start = time(); 1942 } 1943 1944 sub query_end { 1945 my $self = shift(); 1946 my $sql = shift(); 1947 my @params = @_; 1948 1949 my $elapsed = sprintf("%0.4f", time() - $start); 1950 $self->print("Execution took $elapsed seconds.\n"); 1951 $start = undef; 1952 } 1953 1954 1; 1955 1956You can then install that class as the debugging object: 1957 1958 __PACKAGE__->storage->debugobj(new My::Profiler()); 1959 __PACKAGE__->storage->debug(1); 1960 1961A more complicated example might involve storing each execution of SQL in an 1962array: 1963 1964 sub query_end { 1965 my $self = shift(); 1966 my $sql = shift(); 1967 my @params = @_; 1968 1969 my $elapsed = time() - $start; 1970 push(@{ $calls{$sql} }, { 1971 params => \@params, 1972 elapsed => $elapsed 1973 }); 1974 } 1975 1976You could then create average, high and low execution times for an SQL 1977statement and dig down to see if certain parameters cause aberrant behavior. 1978You might want to check out L<DBIx::Class::QueryLog> as well. 1979 1980=head1 STARTUP SPEED 1981 1982L<DBIx::Class|DBIx::Class> programs can have a significant startup delay 1983as the ORM loads all the relevant classes. This section examines 1984techniques for reducing the startup delay. 1985 1986These tips are are listed in order of decreasing effectiveness - so the 1987first tip, if applicable, should have the greatest effect on your 1988application. 1989 1990=head2 Statically Define Your Schema 1991 1992If you are using 1993L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to build the 1994classes dynamically based on the database schema then there will be a 1995significant startup delay. 1996 1997For production use a statically defined schema (which can be generated 1998using L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to dump 1999the database schema once - see 2000L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> and 2001L<dump_directory|DBIx::Class::Schema::Loader/dump_directory> for more 2002details on creating static schemas from a database). 2003 2004=head2 Move Common Startup into a Base Class 2005 2006Typically L<DBIx::Class> result classes start off with 2007 2008 use base qw/DBIx::Class::Core/; 2009 __PACKAGE__->load_components(qw/InflateColumn::DateTime/); 2010 2011If this preamble is moved into a common base class:- 2012 2013 package MyDBICbase; 2014 2015 use base qw/DBIx::Class::Core/; 2016 __PACKAGE__->load_components(qw/InflateColumn::DateTime/); 2017 1; 2018 2019and each result class then uses this as a base:- 2020 2021 use base qw/MyDBICbase/; 2022 2023then the load_components is only performed once, which can result in a 2024considerable startup speedup for schemas with many classes. 2025 2026=head2 Explicitly List Schema Result Classes 2027 2028The schema class will normally contain 2029 2030 __PACKAGE__->load_classes(); 2031 2032to load the result classes. This will use L<Module::Find|Module::Find> 2033to find and load the appropriate modules. Explicitly defining the 2034classes you wish to load will remove the overhead of 2035L<Module::Find|Module::Find> and the related directory operations: 2036 2037 __PACKAGE__->load_classes(qw/ CD Artist Track /); 2038 2039If you are instead using the L<load_namespaces|DBIx::Class::Schema/load_namespaces> 2040syntax to load the appropriate classes there is not a direct alternative 2041avoiding L<Module::Find|Module::Find>. 2042 2043=head1 MEMORY USAGE 2044 2045=head2 Cached statements 2046 2047L<DBIx::Class> normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>. 2048This is normally a good idea, but if too many statements are cached, the database may use too much 2049memory and may eventually run out and fail entirely. If you suspect this may be the case, you may want 2050to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash: 2051 2052 # print all currently cached prepared statements 2053 print for keys %{$schema->storage->dbh->{CachedKids}}; 2054 # get a count of currently cached prepared statements 2055 my $count = scalar keys %{$schema->storage->dbh->{CachedKids}}; 2056 2057If it's appropriate, you can simply clear these statements, automatically deallocating them in the 2058database: 2059 2060 my $kids = $schema->storage->dbh->{CachedKids}; 2061 delete @{$kids}{keys %$kids} if scalar keys %$kids > 100; 2062 2063But what you probably want is to expire unused statements and not those that are used frequently. 2064You can accomplish this with L<Tie::Cache> or L<Tie::Cache::LRU>: 2065 2066 use Tie::Cache; 2067 use DB::Main; 2068 my $schema = DB::Main->connect($dbi_dsn, $user, $pass, { 2069 on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 }, 2070 }); 2071 2072=cut 2073