X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=2fdb3ad103d5f26926ad1959c8a8de551935784f;hb=c385ecea5ec4297f269bcc2b8db8e08f5772196d;hp=9bdce36e70ea5c8fe903f554670f3006b2242121;hpb=6607ee1b42ae26851e8bd633f6d4f1edbd3b8b4c;p=dbsrgits%2FDBIx-Class-Historic.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index 9bdce36..2fdb3ad 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -4,38 +4,40 @@ DBIx::Class::Manual::Cookbook - Miscellaneous recipes =head1 RECIPES -=head2 Paged results +=head2 Searching -When you expect a large number of results, you can ask DBIx::Class for a paged -resultset, which will fetch only a small number of records at a time: +=head3 Paged results - $rs = $schema->resultset('Artist')->search( - {}, - { - page => 1, # page to return (defaults to 1) - rows => 10, # number of results per page - }, - ); +When you expect a large number of results, you can ask L for a +paged resultset, which will fetch only a defined number of records at a time: - $rs->all(); # return all records for page 1 + my $rs = $schema->resultset('Artist')->search( + undef, + { + page => 1, # page to return (defaults to 1) + rows => 10, # number of results per page + }, + ); -The "page" attribute does not have to be specified in your search: + return $rs->all(); # all records for page 1 - $rs = $schema->resultset('Artist')->search( - {}, - { - rows => 10, - } - ); +The C attribute does not have to be specified in your search: + + my $rs = $schema->resultset('Artist')->search( + undef, + { + rows => 10, + } + ); - $rs->page(1); # return DBIx::Class::ResultSet containing first 10 records + return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records -In either of the above cases, you can return a L object for the -resultset (suitable for use in a TT template etc) using the pager() method: +In either of the above cases, you can get a L object for the +resultset (suitable for use in e.g. a template) using the C method: - $pager = $rs->pager(); + return $rs->pager(); -=head2 Complex searches +=head3 Complex WHERE clauses Sometimes you need to formulate a query using specific operators: @@ -68,334 +70,562 @@ This results in the following C clause: For more information on generating complex queries, see L. -=head2 Disconnecting cleanly +=head3 Arbitrary SQL through a custom ResultSource + +Sometimes you have to run arbitrary SQL because your query is too complex +(e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to +be optimized for your database in a special way, but you still want to +get the results as a L. +The recommended way to accomplish this is by defining a separate ResultSource +for your query. You can then inject complete SQL statements using a scalar +reference (this is a feature of L). + +Say you want to run a complex custom query on your user data, here's what +you have to add to your User class: + + package My::Schema::User; + + use base qw/DBIx::Class/; + + # ->load_components, ->table, ->add_columns, etc. + + # Make a new ResultSource based on the User class + my $source = __PACKAGE__->result_source_instance(); + my $new_source = $source->new( $source ); + $new_source->source_name( 'UserFriendsComplex' ); + + # Hand in your query as a scalar reference + # It will be added as a sub-select after FROM, + # so pay attention to the surrounding brackets! + $new_source->name( \<register_source( 'UserFriendsComplex' => $new_source ); + +Next, you can execute your complex query using bind parameters like this: + + my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {}, + { + bind => [ 12345, 12345 ] + } + ) ]; + +... and you'll get back a perfect L. -If you find yourself quitting an app with Control-C a lot during -development, you might like to put the following signal handler in -your main database class to make sure it disconnects cleanly: +=head3 Using specific columns - $SIG{INT} = sub { - __PACKAGE__->storage->dbh->disconnect; - }; - -=head2 Using cols +When you only want specific columns from a table, you can use +C to specify which ones you need. This is useful to avoid +loading columns with large amounts of data that you aren't about to +use anyway: -When you only want selected columns from a table, you can use "cols" to -specify which ones you need (you could also use "select", but "cols" is the -recommended way): + my $rs = $schema->resultset('Artist')->search( + undef, + { + columns => [qw/ name /] + } + ); - $rs = $schema->resultset('Artist')->search( - {}, - { - cols => [qw/ name /] - } - ); + # Equivalent SQL: + # SELECT artist.name FROM artist - # e.g. - # SELECT artist.name FROM artist +This is a shortcut for C and C. -=head2 Using select and as +=head3 Using database functions or stored procedures -The combination of "select" and "as" is probably most useful when you want to -return the result of a function or stored procedure as a column value. You use -"select" to specify the source for your column value (e.g. a column name, -function or stored procedure name). You then use "as" to set the column name -you will use to access the returned value: +The combination of C to +specify the source for your column value (e.g. a column name, function, or +stored procedure name). You then use C to set the column name you will use +to access the returned value: - $rs = $schema->resultset('Artist')->search( - {}, - { - select => [ 'name', { LENGTH => 'name' } ], - as => [qw/ name name_length /], - } - ); + my $rs = $schema->resultset('Artist')->search( + {}, + { + select => [ 'name', { LENGTH => 'name' } ], + as => [qw/ name name_length /], + } + ); - # e.g. - # SELECT name name, LENGTH( name ) name_length - # FROM artist + # Equivalent SQL: + # SELECT name name, LENGTH( name ) + # FROM artist -If your alias exists as a column in your base class (i.e. it was added with -add_columns()), you just access it as normal. Our Artist class has a "name" -column, so we just use the "name" accessor: +Note that the C< as > attribute has absolutely nothing to with the sql +syntax C< SELECT foo AS bar > (see the documentation in +L). If your alias exists as a +column in your base class (i.e. it was added with C), you +just access it as normal. Our C class has a C column, so +we just use the C accessor: - my $artist = $rs->first(); - my $name = $artist->name(); + my $artist = $rs->first(); + my $name = $artist->name(); If on the other hand the alias does not correspond to an existing column, you -can get the value using the get_column() accessor: +have to fetch the value using the C accessor: - my $name_length = $artist->get_column('name_length'); + my $name_length = $artist->get_column('name_length'); -If you don't like using "get_column()", you can always create an accessor for +If you don't like using C, you can always create an accessor for any of your aliases using either of these: - # define accessor manually - sub name_length { shift->get_column('name_length'); } + # Define accessor manually: + sub name_length { shift->get_column('name_length'); } - # or use DBIx::Class::AccessorGroup - __PACKAGE__->mk_group_accessors('column' => 'name_length'); - -=head2 SELECT DISTINCT with multiple columns - - $rs = $schema->resultset('Foo')->search( - {}, - { - select => [ - { distinct => [ $source->columns ] } - ], - as => [ $source->columns ] - } - ); + # Or use DBIx::Class::AccessorGroup: + __PACKAGE__->mk_group_accessors('column' => 'name_length'); -=head2 SELECT COUNT(DISTINCT colname) +=head3 SELECT DISTINCT with multiple columns - $rs = $schema->resultset('Foo')->search( - {}, - { - select => [ - { count => { distinct => 'colname' } } - ], - as => [ 'count' ] - } - ); + my $rs = $schema->resultset('Foo')->search( + {}, + { + select => [ + { distinct => [ $source->columns ] } + ], + as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-) + } + ); -=head2 Grouping results + my $count = $rs->next->get_column('count'); -DBIx::Class supports GROUP BY as follows: +=head3 SELECT COUNT(DISTINCT colname) - $rs = $schema->resultset('Artist')->search( - {}, - { - join => [qw/ cds /], - select => [ 'name', { count => 'cds.cdid' } ], - as => [qw/ name cd_count /], - group_by => [qw/ name /] - } - ); + my $rs = $schema->resultset('Foo')->search( + {}, + { + select => [ + { count => { distinct => 'colname' } } + ], + as => [ 'count' ] + } + ); - # e.g. - # SELECT name, COUNT( cds.cdid ) FROM artist me - # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) - # GROUP BY name +=head3 Grouping results -=head2 Using joins and prefetch +L supports C as follows: -You can use the "join" attribute to allow searching on, or sorting your -results by, one or more columns in a related table. To return -all CDs matching a particular artist name: + my $rs = $schema->resultset('Artist')->search( + {}, + { + join => [qw/ cds /], + select => [ 'name', { count => 'cds.cdid' } ], + as => [qw/ name cd_count /], + group_by => [qw/ name /] + } + ); - my $rs = $schema->resultset('CD')->search( - { - 'artist.name' => 'Bob Marley' - }, - { - join => [qw/artist/], # join the artist table - } - ); - - # equivalent SQL: - # SELECT cd.* FROM cd - # JOIN artist ON cd.artist = artist.id - # WHERE artist.name = 'Bob Marley' - -If required, you can now sort on any column in the related table(s) by -including it in your "order_by" attribute: - - my $rs = $schema->resultset('CD')->search( - { - 'artist.name' => 'Bob Marley' - }, - { - join => [qw/ artist /], - order_by => [qw/ artist.name /] - } - }; + # Equivalent SQL: + # SELECT name, COUNT( cds.cdid ) FROM artist me + # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) + # GROUP BY name - # equivalent SQL: - # SELECT cd.* FROM cd - # JOIN artist ON cd.artist = artist.id - # WHERE artist.name = 'Bob Marley' - # ORDER BY artist.name +Please see L documentation if you +are in any way unsure about the use of the attributes above (C< join +>, C< select >, C< as > and C< group_by >). -Note that the "join" attribute should only be used when you need to search or -sort using columns in a related table. Joining related tables when you -only need columns from the main table will make performance worse! +=head3 Predefined searches + +You can write your own L class by inheriting from it +and define often used searches as methods: + + package My::DBIC::ResultSet::CD; + use strict; + use warnings; + use base 'DBIx::Class::ResultSet'; + + sub search_cds_ordered { + my ($self) = @_; + + return $self->search( + {}, + { order_by => 'name DESC' }, + ); + } + + 1; + +To use your resultset, first tell DBIx::Class to create an instance of it +for you, in your My::DBIC::Schema::CD class: -Now let's say you want to display a list of CDs, each with the name of -the artist. The following will work fine: + __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD'); - while (my $cd = $rs->next) { - print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; +Then call your new method in your code: + + my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); + +=head3 Using SQL functions on the left hand side of a comparison + +Using SQL functions on the left hand side of a comparison is generally +not a good idea since it requires a scan of the entire table. However, +it can be accomplished with C when necessary. + +If you do not have quoting on, simply include the function in your search +specification as you would any column: + + $rs->search({ 'YEAR(date_of_birth)' => 1979 }); + +With quoting on, or for a more portable solution, use the C +attribute: + + $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' }); + +=begin hidden + +(When the bind args ordering bug is fixed, this technique will be better +and can replace the one above.) + +With quoting on, or for a more portable solution, use the C and +C attributes: + + $rs->search({}, { + where => \'YEAR(date_of_birth) = ?', + bind => [ 1979 ] + }); + +=end hidden + +=head2 Using joins and prefetch + +You can use the C attribute to allow searching on, or sorting your +results by, one or more columns in a related table. To return all CDs matching +a particular artist name: + + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => [qw/artist/], # join the artist table } + ); + + # Equivalent SQL: + # SELECT cd.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' + +If required, you can now sort on any column in the related tables by including +it in your C attribute: + + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => [qw/ artist /], + order_by => [qw/ artist.name /] + } + ); + + # Equivalent SQL: + # SELECT cd.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name + +Note that the C attribute should only be used when you need to search or +sort using columns in a related table. Joining related tables when you only +need columns from the main table will make performance worse! + +Now let's say you want to display a list of CDs, each with the name of the +artist. The following will work fine: -There is a problem however. We have searched both cd and artist tables in our -main query, but we have only returned data from the cd table. To get the artist -name for any of the CD objects returned, DBIx::Class will go back to the -database: + while (my $cd = $rs->next) { + print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; + } + +There is a problem however. We have searched both the C and C tables +in our main query, but we have only returned data from the C table. To get +the artist name for any of the CD objects returned, L will go back +to the database: - SELECT artist.* FROM artist WHERE artist.id = ? + SELECT artist.* FROM artist WHERE artist.id = ? A statement like the one above will run for each and every CD returned by our main query. Five CDs, five extra queries. A hundred CDs, one hundred extra queries! -Thankfully, DBIx::Class has a "prefetch" attribute to solve this problem. This -allows you to fetch results from a related table as well as the main table -for your class: - - my $rs = $schema->resultset('CD')->search( - { - 'artist.name' => 'Bob Marley' - }, - { - join => [qw/ artist /], - order_by => [qw/ artist.name /], - prefetch => [qw/ artist /] # return artist data too! - } - ); +Thankfully, L has a C attribute to solve this problem. +This allows you to fetch results from related tables in advance: + + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => [qw/ artist /], + order_by => [qw/ artist.name /], + prefetch => [qw/ artist /] # return artist data too! + } + ); - # equivalent SQL (note SELECT from both "cd" and "artist") - # SELECT cd.*, artist.* FROM cd - # JOIN artist ON cd.artist = artist.id - # WHERE artist.name = 'Bob Marley' - # ORDER BY artist.name + # Equivalent SQL (note SELECT from both "cd" and "artist"): + # SELECT cd.*, artist.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name The code to print the CD list remains the same: - while (my $cd = $rs->next) { - print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; - } + while (my $cd = $rs->next) { + print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; + } -DBIx::Class has now prefetched all matching data from the "artist" table, +L has now prefetched all matching data from the C table, so no additional SQL statements are executed. You now have a much more efficient query. -Note that as of DBIx::Class 0.04, "prefetch" cannot be used with has_many -relationships. You will get an error along the lines of "No accessor for -prefetched ..." if you try. +Note that as of L 0.05999_01, C I be used with +C relationships. -Note that "prefetch" should only be used when you know you will +Also note that C should only be used when you know you will definitely use data from a related table. Pre-fetching related tables when you only need columns from the main table will make performance worse! -=head2 Multi-step joins +=head3 Multi-step joins Sometimes you want to join more than one relationship deep. In this example, -we want to find all Artist objects who have CDs whose LinerNotes contain a -specific string: - - # Artist->has_many('cds' => 'CD', 'artist'); - # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); - - $rs = $schema->resultset('Artist')->search( - { - 'liner_notes.notes' => { 'like', '%some text%' }, - }, - { - join => { - 'cds' => 'liner_notes' - } - } - ); +we want to find all C objects who have Cs whose C +contain a specific string: + + # Relationships defined elsewhere: + # Artist->has_many('cds' => 'CD', 'artist'); + # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); + + my $rs = $schema->resultset('Artist')->search( + { + 'liner_notes.notes' => { 'like', '%some text%' }, + }, + { + join => { + 'cds' => 'liner_notes' + } + } + ); - # equivalent SQL - # SELECT artist.* FROM artist - # JOIN ( cd ON artist.id = cd.artist ) - # JOIN ( liner_notes ON cd.id = liner_notes.cd ) - # WHERE liner_notes.notes LIKE '%some text%' + # Equivalent SQL: + # SELECT artist.* FROM artist + # JOIN ( cd ON artist.id = cd.artist ) + # JOIN ( liner_notes ON cd.id = liner_notes.cd ) + # WHERE liner_notes.notes LIKE '%some text%' Joins can be nested to an arbitrary level. So if we decide later that we want to reduce the number of Artists returned based on who wrote the liner notes: - # LinerNotes->belongs_to('author' => 'Person'); - - $rs = $schema->resultset('Artist')->search( - { - 'liner_notes.notes' => { 'like', '%some text%' }, - 'author.name' => 'A. Writer' - }, - { - join => { - 'cds' => { - 'liner_notes' => 'author' - } - } + # Relationship defined elsewhere: + # LinerNotes->belongs_to('author' => 'Person'); + + my $rs = $schema->resultset('Artist')->search( + { + 'liner_notes.notes' => { 'like', '%some text%' }, + 'author.name' => 'A. Writer' + }, + { + join => { + 'cds' => { + 'liner_notes' => 'author' } - ); + } + } + ); + + # Equivalent SQL: + # SELECT artist.* FROM artist + # JOIN ( cd ON artist.id = cd.artist ) + # JOIN ( liner_notes ON cd.id = liner_notes.cd ) + # JOIN ( author ON author.id = liner_notes.author ) + # WHERE liner_notes.notes LIKE '%some text%' + # AND author.name = 'A. Writer' + +=head2 Multi-step prefetch + +From 0.04999_05 onwards, C can be nested more than one relationship +deep using the same syntax as a multi-step join: + + my $rs = $schema->resultset('Tag')->search( + {}, + { + prefetch => { + cd => 'artist' + } + } + ); + + # Equivalent SQL: + # SELECT tag.*, cd.*, artist.* FROM tag + # JOIN cd ON tag.cd = cd.cdid + # JOIN artist ON cd.artist = artist.artistid + +Now accessing our C and C relationships does not need additional +SQL statements: + + my $tag = $rs->first; + print $tag->cd->artist->name; + +=head2 Columns of data + +If you want to find the sum of a particular column there are several +ways, the obvious one is to use search: + + my $rs = $schema->resultset('Items')->search( + {}, + { + select => [ { sum => 'Cost' } ], + as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL + } + ); + my $tc = $rs->first->get_column('total_cost'); + +Or, you can use the L, which gets +returned when you ask the C for a column using +C: + + my $cost = $schema->resultset('Items')->get_column('Cost'); + my $tc = $cost->sum; + +With this you can also do: + + my $minvalue = $cost->min; + my $maxvalue = $cost->max; + +Or just iterate through the values of this column only: + + while ( my $c = $cost->next ) { + print $c; + } + + foreach my $c ($cost->all) { + print $c; + } + +C only has a limited number of built-in functions, if +you need one that it doesn't have, then you can use the C method +instead: + + my $avg = $cost->func('AVERAGE'); + +This will cause the following SQL statement to be run: + + SELECT AVERAGE(Cost) FROM Items me + +Which will of course only work if your database supports this function. +See L for more documentation. + +=head2 Using relationships + +=head3 Create a new row in a related table + + my $book->create_related('author', { name => 'Fred'}); + +=head3 Search in a related table + +Only searches for books named 'Titanic' by the author in $author. + + my $author->search_related('books', { name => 'Titanic' }); + +=head3 Delete data in a related table + +Deletes only the book named Titanic by the author in $author. + + my $author->delete_related('books', { name => 'Titanic' }); + +=head3 Ordering a relationship result set + +If you always want a relation to be ordered, you can specify this when you +create the relationship. + +To order C<< $book->pages >> by descending page_number. + + Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} ); + - # equivalent SQL - # SELECT artist.* FROM artist - # JOIN ( cd ON artist.id = cd.artist ) - # JOIN ( liner_notes ON cd.id = liner_notes.cd ) - # JOIN ( author ON author.id = liner_notes.author ) - # WHERE liner_notes.notes LIKE '%some text%' - # AND author.name = 'A. Writer' =head2 Transactions As of version 0.04001, there is improved transaction support in -L. Here is an example of the recommended -way to use it: +L and L. Here is an +example of the recommended way to use it: - my $genus = Genus->find(12); - eval { - MyDB->txn_begin; + my $genus = $schema->resultset('Genus')->find(12); + + my $coderef2 = sub { + $genus->extinct(1); + $genus->update; + }; + + my $coderef1 = sub { $genus->add_to_species({ name => 'troglodyte' }); $genus->wings(2); $genus->update; - cromulate($genus); # Can have a nested transation - MyDB->txn_commit; + $schema->txn_do($coderef2); # Can have a nested transaction + return $genus->species; }; - if ($@) { - # Rollback might fail, too - eval { - MyDB->txn_rollback - }; + + my $rs; + eval { + $rs = $schema->txn_do($coderef1); + }; + + if ($@) { # Transaction failed + die "the sky is falling!" # + if ($@ =~ /Rollback failed/); # Rollback failed + + deal_with_failed_transaction(); } -Currently, a nested commit will do nothing and a nested rollback will -die. The code at each level must be sure to call rollback in the case -of an error, to ensure that the rollback will propagate to the top -level and be issued. Support for savepoints and for true nested +Nested transactions will work as expected. That is, only the outermost +transaction will actually issue a commit to the $dbh, and a rollback +at any level of any transaction will cause the entire nested +transaction to fail. Support for savepoints and for true nested transactions (for databases that support them) will hopefully be added in the future. =head2 Many-to-many relationships -This is straightforward using L: +This is straightforward using L: - package My::DB; - # set up connection here... + package My::DB; + # ... set up connection ... - package My::User; - use base 'My::DB'; - __PACKAGE__->table('user'); - __PACKAGE__->add_columns(qw/id name/); - __PACKAGE__->set_primary_key('id'); - __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user'); - __PACKAGE__->many_to_many('addresses' => 'user_address', 'address'); + package My::User; + use base 'My::DB'; + __PACKAGE__->table('user'); + __PACKAGE__->add_columns(qw/id name/); + __PACKAGE__->set_primary_key('id'); + __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user'); + __PACKAGE__->many_to_many('addresses' => 'user_address', 'address'); - package My::UserAddress; - use base 'My::DB'; - __PACKAGE__->table('user_address'); - __PACKAGE__->add_columns(qw/user address/); - __PACKAGE__->set_primary_key(qw/user address/); - __PACKAGE__->belongs_to('user' => 'My::User'); - __PACKAGE__->belongs_to('address' => 'My::Address'); + package My::UserAddress; + use base 'My::DB'; + __PACKAGE__->table('user_address'); + __PACKAGE__->add_columns(qw/user address/); + __PACKAGE__->set_primary_key(qw/user address/); + __PACKAGE__->belongs_to('user' => 'My::User'); + __PACKAGE__->belongs_to('address' => 'My::Address'); - package My::Address; - use base 'My::DB'; - __PACKAGE__->table('address'); - __PACKAGE__->add_columns(qw/id street town area_code country/); - __PACKAGE__->set_primary_key('id'); - __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address'); - __PACKAGE__->many_to_many('users' => 'user_address', 'user'); + package My::Address; + use base 'My::DB'; + __PACKAGE__->table('address'); + __PACKAGE__->add_columns(qw/id street town area_code country/); + __PACKAGE__->set_primary_key('id'); + __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address'); + __PACKAGE__->many_to_many('users' => 'user_address', 'user'); - $rs = $user->addresses(); # get all addresses for a user - $rs = $address->users(); # get all users for an address + $rs = $user->addresses(); # get all addresses for a user + $rs = $address->users(); # get all users for an address -=head2 Setting default values +=head2 Setting default values for a row It's as simple as overriding the C method. Note the use of C. @@ -405,14 +635,631 @@ C. $attrs->{foo} = 'bar' unless defined $attrs->{foo}; - $class->next::method($attrs); + my $new = $class->next::method($attrs); + + return $new; } +For more information about C, look in the L +documentation. See also L for more +ways to write your own base classes to do this. + +People looking for ways to do "triggers" with DBIx::Class are probably +just looking for this. + =head2 Stringification Employ the standard stringification technique by using the C -module. Replace C with the column/method of your choice. +module. + +To make an object stringify itself as a single column, use something +like this (replace C with the column/method of your choice): + + use overload '""' => sub { shift->name}, fallback => 1; + +For more complex stringification, you can use an anonymous subroutine: + + use overload '""' => sub { $_[0]->name . ", " . + $_[0]->address }, fallback => 1; + +=head3 Stringification Example + +Suppose we have two tables: C and C. The table +specifications are: + + Product(id, Description, category) + Category(id, Description) + +C is a foreign key into the Category table. + +If you have a Product object C<$obj> and write something like + + print $obj->category + +things will not work as expected. + +To obtain, for example, the category description, you should add this +method to the class defining the Category table: + + use overload "" => sub { + my $self = shift; + + return $self->Description; + }, fallback => 1; + +=head2 Disconnecting cleanly + +If you find yourself quitting an app with Control-C a lot during +development, you might like to put the following signal handler in +your main database class to make sure it disconnects cleanly: - use overload '""' => 'foo', fallback => 1; + $SIG{INT} = sub { + __PACKAGE__->storage->disconnect; + }; + +=head2 Schema import/export + +To create a DBIx::Class schema from an existing database, use +L's C: + + perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])' + +The following functionality requires you to have L +(also known as "SQL Fairy") installed. + +To create a set of database-specific .sql files for the above schema: + + my $schema = My::Schema->connect($dsn); + $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'], + '0.1', + '/dbscriptdir/' + ); + +By default this will create schema files in the current directory, for +MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm. + +To create a new database using the schema: + + my $schema = My::Schema->connect($dsn); + $schema->deploy({ add_drop_tables => 1}); + +To import created .sql files using the mysql client: + + mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql + +To create C conversion scripts to update a database to a +newer version of your schema at a later point, first set a new +$VERSION in your Schema file, then: + + my $schema = My::Schema->connect($dsn); + $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'], + '0.2', + '/dbscriptdir/', + '0.1' + ); + +This will produce new database-specific .sql files for the new version +of the schema, plus scripts to convert from version 0.1 to 0.2. This +requires that the files for 0.1 as created above are available in the +given directory to diff against. + + +=head2 Easy migration from class-based to schema-based setup + +You want to start using the schema-based approach to L +(see L), but have an established class-based setup with lots +of existing classes that you don't want to move by hand. Try this nifty script +instead: + + use MyDB; + use SQL::Translator; + + my $schema = MyDB->schema_instance; + + my $translator = SQL::Translator->new( + debug => $debug || 0, + trace => $trace || 0, + no_comments => $no_comments || 0, + show_warnings => $show_warnings || 0, + add_drop_table => $add_drop_table || 0, + validate => $validate || 0, + parser_args => { + 'DBIx::Schema' => $schema, + }, + producer_args => { + 'prefix' => 'My::Schema', + }, + ); + + $translator->parser('SQL::Translator::Parser::DBIx::Class'); + $translator->producer('SQL::Translator::Producer::DBIx::Class::File'); + + my $output = $translator->translate(@args) or die + "Error: " . $translator->error; + + print $output; + +You could use L to search for all subclasses in the MyDB::* +namespace, which is currently left as an exercise for the reader. + +=head2 Schema versioning + +The following example shows simplistically how you might use DBIx::Class to +deploy versioned schemas to your customers. The basic process is as follows: + +=over 4 + +=item 1. + +Create a DBIx::Class schema + +=item 2. + +Save the schema + +=item 3. + +Deploy to customers + +=item 4. + +Modify schema to change functionality + +=item 5. + +Deploy update to customers + +=back + +=head3 Create a DBIx::Class schema + +This can either be done manually, or generated from an existing database as +described under C. + +=head3 Save the schema + +Call L as above under L. + +=head3 Deploy to customers + +There are several ways you could deploy your schema. These are probably +beyond the scope of this recipe, but might include: + +=over 4 + +=item 1. + +Require customer to apply manually using their RDBMS. + +=item 2. + +Package along with your app, making database dump/schema update/tests +all part of your install. + +=back + +=head3 Modify the schema to change functionality + +As your application evolves, it may be necessary to modify your schema +to change functionality. Once the changes are made to your schema in +DBIx::Class, export the modified schema and the conversion scripts as +in L. + +=head3 Deploy update to customers + +Add the L schema component to your +Schema class. This will add a new table to your database called +C which will keep track of which version is installed +and warn if the user trys to run a newer schema version than the +database thinks it has. + +Alternatively, you can send the conversion sql scripts to your +customers as above. + +=head2 Setting limit dialect for SQL::Abstract::Limit + +In some cases, SQL::Abstract::Limit cannot determine the dialect of +the remote SQL server by looking at the database handle. This is a +common problem when using the DBD::JDBC, since the DBD-driver only +know that in has a Java-driver available, not which JDBC driver the +Java component has loaded. This specifically sets the limit_dialect +to Microsoft SQL-server (See more names in SQL::Abstract::Limit +-documentation. + + __PACKAGE__->storage->sql_maker->limit_dialect('mssql'); + +The JDBC bridge is one way of getting access to a MSSQL server from a platform +that Microsoft doesn't deliver native client libraries for. (e.g. Linux) + +=head2 Setting quoting for the generated SQL. + +If the database contains column names with spaces and/or reserved words, they +need to be quoted in the SQL queries. This is done using: + + __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] ); + __PACKAGE__->storage->sql_maker->name_sep('.'); + +The first sets the quote characters. Either a pair of matching +brackets, or a C<"> or C<'>: + + __PACKAGE__->storage->sql_maker->quote_char('"'); + +Check the documentation of your database for the correct quote +characters to use. C needs to be set to allow the SQL +generator to put the quotes the correct place. + +=head2 Overloading methods + +L uses the L package, which provides for redispatch of +method calls, useful for things like default values and triggers. You have to +use calls to C to overload methods. More information on using +L with L can be found in +L. + +=head3 Changing one field whenever another changes + +For example, say that you have three columns, C, C, and +C. You would like to make changes to C and have +C be automagically set to the value of C squared. +You can accomplish this by overriding C: + + sub store_column { + my ( $self, $name, $value ) = @_; + if ($name eq 'number') { + $self->squared($value * $value); + } + $self->next::method($name, $value); + } + +Note that the hard work is done by the call to C, which +redispatches your call to store_column in the superclass(es). + +=head3 Automatically creating related objects + +You might have a class C which has many Cs. Further, if you +want to create a C object every time you insert an C object. +You can accomplish this by overriding C on your objects: + + sub insert { + my ( $self, @args ) = @_; + $self->next::method(@args); + $self->cds->new({})->fill_from_artist($self)->insert; + return $self; + } + +where C is a method you specify in C which sets +values in C based on the data in the C object you pass in. + +=head2 Debugging DBIx::Class objects with Data::Dumper + +L can be a very useful tool for debugging, but sometimes it can +be hard to find the pertinent data in all the data it can generate. +Specifically, if one naively tries to use it like so, + + use Data::Dumper; + + my $cd = $schema->resultset('CD')->find(1); + print Dumper($cd); + +several pages worth of data from the CD object's schema and result source will +be dumped to the screen. Since usually one is only interested in a few column +values of the object, this is not very helpful. + +Luckily, it is possible to modify the data before L outputs +it. Simply define a hook that L will call on the object before +dumping it. For example, + + package My::DB::CD; + + sub _dumper_hook { + $_[0] = bless { + %{ $_[0] }, + result_source => undef, + }, ref($_[0]); + } + + [...] + + use Data::Dumper; + + local $Data::Dumper::Freezer = '_dumper_hook'; + + my $cd = $schema->resultset('CD')->find(1); + print Dumper($cd); + # dumps $cd without its ResultSource + +If the structure of your schema is such that there is a common base class for +all your table classes, simply put a method similar to C<_dumper_hook> in the +base class and set C<$Data::Dumper::Freezer> to its name and L +will automagically clean up your data before printing it. See +L for more information. + +=head2 Retrieving a row object's Schema + +It is possible to get a Schema object from a row object like so: + + my $schema = $cd->result_source->schema; + # use the schema as normal: + my $artist_rs = $schema->resultset('Artist'); + +This can be useful when you don't want to pass around a Schema object to every +method. + +=head2 Profiling + +When you enable L's debugging it prints the SQL +executed as well as notifications of query completion and transaction +begin/commit. If you'd like to profile the SQL you can subclass the +L class and write your own profiling +mechanism: + + package My::Profiler; + use strict; + + use base 'DBIx::Class::Storage::Statistics'; + + use Time::HiRes qw(time); + + my $start; + + sub query_start { + my $self = shift(); + my $sql = shift(); + my $params = @_; + + $self->print("Executing $sql: ".join(', ', @params)."\n"); + $start = time(); + } + + sub query_end { + my $self = shift(); + my $sql = shift(); + my @params = @_; + + my $elapsed = sprintf("%0.4f", time() - $start); + $self->print("Execution took $elapsed seconds.\n"); + $start = undef; + } + + 1; + +You can then install that class as the debugging object: + + __PACKAGE__->storage->debugobj(new My::Profiler()); + __PACKAGE__->storage->debug(1); + +A more complicated example might involve storing each execution of SQL in an +array: + + sub query_end { + my $self = shift(); + my $sql = shift(); + my @params = @_; + + my $elapsed = time() - $start; + push(@{ $calls{$sql} }, { + params => \@params, + elapsed => $elapsed + }); + } + +You could then create average, high and low execution times for an SQL +statement and dig down to see if certain parameters cause aberrant behavior. +You might want to check out L as well. + +=head2 Getting the value of the primary key for the last database insert + +AKA getting last_insert_id + +If you are using PK::Auto, this is straightforward: + + my $foo = $rs->create(\%blah); + # do more stuff + my $id = $foo->id; # foo->my_primary_key_field will also work. + +If you are not using autoincrementing primary keys, this will probably +not work, but then you already know the value of the last primary key anyway. + +=head2 Dynamic Sub-classing DBIx::Class proxy classes +(AKA multi-class object inflation from one table) + +L classes are proxy classes, therefore some different +techniques need to be employed for more than basic subclassing. In +this example we have a single user table that carries a boolean bit +for admin. We would like like to give the admin users +objects(L) the same methods as a regular user but +also special admin only methods. It doesn't make sense to create two +seperate proxy-class files for this. We would be copying all the user +methods into the Admin class. There is a cleaner way to accomplish +this. + +Overriding the C method within the User proxy-class +gives us the effect we want. This method is called by +L when inflating a result from storage. So we +grab the object being returned, inspect the values we are looking for, +bless it if it's an admin object, and then return it. See the example +below: + +B + + package DB::Schema; + + use base qw/DBIx::Class::Schema/; + + __PACKAGE__->load_classes(qw/User/); + + +B + + package DB::Schema::User; + + use strict; + use warnings; + use base qw/DBIx::Class/; + + ### Defined what our admin class is for ensure_class_loaded + my $admin_class = __PACKAGE__ . '::Admin'; + + __PACKAGE__->load_components(qw/Core/); + + __PACKAGE__->table('users'); + + __PACKAGE__->add_columns(qw/user_id email password + firstname lastname active + admin/); + + __PACKAGE__->set_primary_key('user_id'); + + sub inflate_result { + my $self = shift; + my $ret = $self->next::method(@_); + if( $ret->admin ) {### If this is an admin rebless for extra functions + $self->ensure_class_loaded( $admin_class ); + bless $ret, $admin_class; + } + return $ret; + } + + sub hello { + print "I am a regular user.\n"; + return ; + } + + + package DB::Schema::User::Admin; + + use strict; + use warnings; + use base qw/DB::Schema::User/; + + sub hello + { + print "I am an admin.\n"; + return; + } + + sub do_admin_stuff + { + print "I am doing admin stuff\n"; + return ; + } + +B test.pl + + use warnings; + use strict; + use DB::Schema; + + my $user_data = { email => 'someguy@place.com', + password => 'pass1', + admin => 0 }; + + my $admin_data = { email => 'someadmin@adminplace.com', + password => 'pass2', + admin => 1 }; + + my $schema = DB::Schema->connection('dbi:Pg:dbname=test'); + + $schema->resultset('User')->create( $user_data ); + $schema->resultset('User')->create( $admin_data ); + + ### Now we search for them + my $user = $schema->resultset('User')->single( $user_data ); + my $admin = $schema->resultset('User')->single( $admin_data ); + + print ref $user, "\n"; + print ref $admin, "\n"; + + print $user->password , "\n"; # pass1 + print $admin->password , "\n";# pass2; inherited from User + print $user->hello , "\n";# I am a regular user. + print $admin->hello, "\n";# I am an admin. + + ### The statement below will NOT print + print "I can do admin stuff\n" if $user->can('do_admin_stuff'); + ### The statement below will print + print "I can do admin stuff\n" if $admin->can('do_admin_stuff'); + +=head2 Skip object creation for faster results + +DBIx::Class is not built for speed, it's built for convenience and +ease of use, but sometimes you just need to get the data, and skip the +fancy objects. + +To do this simply use L. + + my $rs = $schema->resultset('CD'); + + $rs->result_class('DBIx::Class::ResultClass::HashRefInflator'); + + my $hash_ref = $rs->find(1); + +Wasn't that easy? + +=head2 Get raw data for blindingly fast results + +If the C solution above is not fast enough for you, you +can use a DBIx::Class to return values exactly as they come out of the +data base with none of the convenience methods wrapped round them. + +This is used like so:- + + my $cursor = $rs->cursor + while (my @vals = $cursor->next) { + # use $val[0..n] here + } + +You will need to map the array offsets to particular columns (you can +use the I