X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=a0bce5e04a0e4b539427187e916e8a65f6449d2d;hb=9e64dfbf92b14c3dac77cf4ed8a8842114cdbfe5;hp=40da3c7820bfe7bf034892b1bc583d54eac7834c;hpb=faf625513f86aaf6396a838cbfad890b36ccd00b;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index 40da3c7..a0bce5e 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 small 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: +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,277 +70,403 @@ This results in the following C clause: For more information on generating complex queries, see L. -=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: - - $SIG{INT} = sub { - __PACKAGE__->storage->dbh->disconnect; - }; +=head3 Using specific columns -=head2 Using cols +When you only want selected columns from a table, you can use C to +specify which ones you need: -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): - - $rs = $schema->resultset('Artist')->search( - {}, - { - cols => [qw/ name /] - } - ); + my $rs = $schema->resultset('Artist')->search( + undef, + { + columns => [qw/ name /] + } + ); - # e.g. - # SELECT artist.name FROM artist + # Equivalent SQL: + # SELECT artist.name FROM artist -=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( + undef, + { + 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 ) name_length + # 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: +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: +can get 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( + undef, + { + select => [ + { distinct => [ $source->columns ] } + ], + as => [ $source->columns ] + } + ); -=head2 Using joins and prefetch + my $count = $rs->next->get_column('count'); -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: +=head3 SELECT COUNT(DISTINCT colname) - 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 /] - } - }; + my $rs = $schema->resultset('Foo')->search( + undef, + { + select => [ + { count => { distinct => 'colname' } } + ], + as => [ 'count' ] + } + ); - # equivalent SQL: - # SELECT cd.* FROM cd - # JOIN artist ON cd.artist = artist.id - # WHERE artist.name = 'Bob Marley' - # ORDER BY artist.name +=head3 Grouping results -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! +L supports C as follows: -Now let's say you want to display a list of CDs, each with the name of -the artist. The following will work fine: + my $rs = $schema->resultset('Artist')->search( + undef, + { + join => [qw/ cds /], + select => [ 'name', { count => 'cds.cdid' } ], + as => [qw/ name cd_count /], + group_by => [qw/ name /] + } + ); - while (my $cd = $rs->next) { - print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; + # Equivalent SQL: + # SELECT name, COUNT( cds.cdid ) FROM artist me + # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) + # GROUP BY name + +=head3 Predefined searches + +You can write your own DBIx::Class::ResultSet 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: + + __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD'); + +Then call your new method in your code: + + my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); + + +=head3 Predefined searches without writing a ResultSet class + +Alternatively you can automatically generate a DBIx::Class::ResultSet +class by using the ResultSetManager component and tagging your method +as ResultSet: + + __PACKAGE__->load_components(qw/ ResultSetManager Core /); + + sub search_cds_ordered : ResultSet { + my ($self) = @_; + return $self->search( + {}, + { order_by => 'name DESC' }, + ); + } + +Then call your method in the same way from your code: + + my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); + +=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 -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: +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! - SELECT artist.* FROM artist WHERE artist.id = ? +Now let's say you want to display a list of CDs, each with the name of the +artist. The following will work fine: + + 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 = ? 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( + undef, + { + 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 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. @@ -346,37 +474,37 @@ in the future. This is straightforward using L: - package My::DB; - # set up connection here... - - 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::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 - -=head2 Setting default values + 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::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'); + + $rs = $user->addresses(); # get all addresses for a user + $rs = $address->users(); # get all users for an address + +=head2 Setting default values for a row It's as simple as overriding the C method. Note the use of C. @@ -389,6 +517,13 @@ C. $class->next::method($attrs); } +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 @@ -396,4 +531,475 @@ module. Replace C with the column/method of your choice. use overload '""' => 'foo', 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: + + $SIG{INT} = sub { + __PACKAGE__->storage->disconnect; + }; + +=head2 Schema import/export + +This functionality requires you to have L (also known as +"SQL Fairy") installed. + +To create a DBIx::Class schema from an existing database: + + sqlt --from DBI + --to DBIx::Class::File + --prefix "MySchema" > MySchema.pm + +To create a MySQL database from an existing L schema, convert the +schema to MySQL's dialect of SQL: + + sqlt --from DBIx::Class --to MySQL --DBIx::Class "MySchema.pm" > Schema1.sql + +And import using the mysql client: + + mysql -h "host" -D "database" -u "user" -p < Schema1.sql + +=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 + +Use C to transform your schema into an SQL script suitable for your +customer's database. E.g. for MySQL: + + sqlt --from DBIx::Class + --to MySQL + --DBIx::Class "MySchema.pm" > Schema1.mysql.sql + +If you need to target databases from multiple vendors, just generate an SQL +script suitable for each. To support PostgreSQL too: + + sqlt --from DBIx::Class + --to PostgreSQL + --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql + +=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 as before, taking care not to overwrite the original: + + sqlt --from DBIx::Class + --to MySQL + --DBIx::Class "Anything.pm" > Schema2.mysql.sql + +Next, use sqlt-diff to create an SQL script that will update the customer's +database schema: + + sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql + +=head3 Deploy update to customers + +The schema update can be deployed to customers using the same method as before. + +=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 (Se 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 quotes for the generated SQL. + +If the database contains columnames with spaces and/or reserved words, the +SQL-query needs to be quoted. This is done using: + + __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] ); + __PACKAGE__->storage->sql_maker->name_sep('.'); + +The first sets the quotesymbols. If the quote i "symmetric" as " or ' + + __PACKAGE__->storage->sql_maker->quote_char('"'); + +is enough. If the left quote differs form the right quote, the first +notation should be used. name_sep 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. 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 to the superclass(es). + +=head3 Automatically creating related objects + +You might have a class C which has many Cs. Further, 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; + my $artist_rs = $schema->resultset('Artist'); + # for example + +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 = @_; + + print "Executing $sql: ".join(', ', @params)."\n"; + $start = time(); + } + + sub query_end { + my $self = shift(); + my $sql = shift(); + my @params = @_; + + printf("Execution took %0.4f seconds.\n", time() - $start); + $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. + +=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. Running the test file below will confirm this works. + +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/PK::Auto 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'); + =cut