X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=8082ebfe623d03473021ad5cd447c8e9f5fd29b4;hb=8d5a66b88fb9a4670fa09380621f4483f011d591;hp=d1e559caf517891729601e103108c61da8a56029;hpb=b46642502590b35e47f30ed5b3584e94ba4f27af;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index d1e559c..8082ebf 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -37,7 +37,10 @@ Sometimes you need to formulate a query using specific operators: This results in something like the following C clause: - WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%' + WHERE artist LIKE ? AND title LIKE ? + +And the following bind values for the placeholders: C<'%Lamb%'>, C<'%Fear of +Fours%'>. Other queries might require slightly more complex logic: @@ -103,22 +106,22 @@ Sometimes you have to run arbitrary SQL because your query is too complex 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 -L for your query. +This is accomplished by defining a +L for your query, +almost like you would define a regular ResultSource. package My::Schema::Result::UserFriendsComplex; use strict; use warnings; - use base qw/DBIx::Class/; - - use DBIx::Class::ResultSource::View; + use base qw/DBIx::Class::Core/; - __PACKAGE__->load_components('Core'); __PACKAGE__->table_class('DBIx::Class::ResultSource::View'); # ->table, ->add_columns, etc. + # do not attempt to deploy() this view __PACKAGE__->result_source_instance->is_virtual(1); + __PACKAGE__->result_source_instance->view_definition(q[ SELECT u.* FROM user u INNER JOIN user_friends f ON u.id = f.user_id @@ -143,6 +146,21 @@ L, ... on it). Note that you cannot have bind parameters unless is_virtual is set to true. +=over + +=item * NOTE + +If you're using the old deprecated C<< $rsrc_instance->name(\'( SELECT ...') >> +method for custom SQL execution, you are highly encouraged to update your code +to use a virtual view as above. If you do not want to change your code, and just +want to suppress the deprecation warning when you call +L, add this line to your source definition, so that +C will exclude this "table": + + sub sqlt_deploy_hook { $_[1]->schema->drop_table ($_[1]) } + +=back + =head2 Using specific columns When you only want specific columns from a table, you can use @@ -183,12 +201,33 @@ to access the returned value: # SELECT name name, LENGTH( name ) # FROM artist -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: +Note that the C attribute B with the sql +syntax C< SELECT foo AS bar > (see the documentation in +L). You can control the C part of the +generated SQL via the C<-as> field attribute as follows: + + my $rs = $schema->resultset('Artist')->search( + {}, + { + join => 'cds', + distinct => 1, + '+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ], + '+as' => [qw/num_cds/], + order_by => { -desc => 'amount_of_cds' }, + } + ); + + # Equivalent SQL + # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds + # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid + # GROUP BY me.artistid, me.name, me.rank, me.charfield + # ORDER BY amount_of_cds DESC + + +If your alias exists as a column in your base class (i.e. it was added with +L), 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(); @@ -207,6 +246,8 @@ any of your aliases using either of these: # Or use DBIx::Class::AccessorGroup: __PACKAGE__->mk_group_accessors('column' => 'name_length'); +See also L. + =head2 SELECT DISTINCT with multiple columns my $rs = $schema->resultset('Artist')->search( @@ -276,7 +317,7 @@ 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 >). -=head2 Subqueries (EXPERIMENTAL) +=head2 Subqueries You can write subqueries relatively easily in DBIC. @@ -294,7 +335,7 @@ B: You have to explicitly use '=' when doing an equality comparison. The following will B work: my $rs = $schema->resultset('CD')->search({ - artist_id => $inside_rs->get_column('id')->as_query, + artist_id => $inside_rs->get_column('id')->as_query, # does NOT work }); =head3 Support @@ -324,10 +365,6 @@ That creates the following SQL: WHERE artist_id = me.artist_id ) -=head3 EXPERIMENTAL - -Please note that subqueries are considered an experimental feature. - =head2 Predefined searches You can write your own L class by inheriting from it @@ -349,11 +386,16 @@ and defining often used searches as methods: 1; -To use your resultset, first tell DBIx::Class to create an instance of it -for you, in your My::DBIC::Schema::CD class: +If you're using L, simply place the file +into the C directory next to your C directory, and it will +be automatically loaded. + +If however you are still using L, first tell +DBIx::Class to create an instance of the ResultSet class for you, in your +My::DBIC::Schema::CD class: # class definition as normal - __PACKAGE__->load_components(qw/ Core /); + use base 'DBIx::Class::Core'; __PACKAGE__->table('cd'); # tell DBIC to use the custom ResultSet class @@ -367,8 +409,10 @@ Then call your new method in your code: =head2 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, +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. (Unless your RDBMS +supports indexes on expressions - including return values of functions -, and +you create an index on the return value of the function in question.) However, it can be accomplished with C when necessary. If you do not have quoting on, simply include the function in your search @@ -376,25 +420,30 @@ 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: +With quoting on, or for a more portable solution, use literal SQL values with +placeholders: - $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' }); + $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]); -=begin hidden + # Equivalent SQL: + # SELECT * FROM employee WHERE YEAR(date_of_birth) = ? -(When the bind args ordering bug is fixed, this technique will be better -and can replace the one above.) + $rs->search({ + name => 'Bob', + -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ], + }); -With quoting on, or for a more portable solution, use the C and -C attributes: + # Equivalent SQL: + # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ? - $rs->search({}, { - where => \'YEAR(date_of_birth) = ?', - bind => [ 1979 ] - }); +Note: the C string in the C<< [ plain_value => 1979 ] >> part +should be either the same as the name of the column (do this if the type of the +return value of the function is the same as the type of the column) or +otherwise it's essentially a dummy string currently (use C as a +habit). It is used by L to handle special column types. -=end hidden +See also L. =head1 JOINS AND PREFETCHING @@ -793,13 +842,11 @@ B use strict; use warnings; - use base qw/DBIx::Class/; + use base qw/DBIx::Class::Core/; ### Define 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 @@ -885,6 +932,9 @@ B test.pl ### The statement below will print print "I can do admin stuff\n" if $admin->can('do_admin_stuff'); +Alternatively you can use L that implements +exactly the above functionality. + =head2 Skip row object creation for faster results DBIx::Class is not built for speed, it's built for convenience and @@ -1025,7 +1075,7 @@ create the relationship. To order C<< $book->pages >> by descending page_number, create the relation as follows: - __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} ); + __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } ); =head2 Filtering a relationship result set @@ -1038,8 +1088,7 @@ If you want to get a filtered result set, you can just add add to $attr as follo This is straightforward using L: package My::User; - use base 'DBIx::Class'; - __PACKAGE__->load_components('Core'); + use base 'DBIx::Class::Core'; __PACKAGE__->table('user'); __PACKAGE__->add_columns(qw/id name/); __PACKAGE__->set_primary_key('id'); @@ -1047,8 +1096,7 @@ This is straightforward using Lmany_to_many('addresses' => 'user_address', 'address'); package My::UserAddress; - use base 'DBIx::Class'; - __PACKAGE__->load_components('Core'); + use base 'DBIx::Class::Core'; __PACKAGE__->table('user_address'); __PACKAGE__->add_columns(qw/user address/); __PACKAGE__->set_primary_key(qw/user address/); @@ -1056,8 +1104,7 @@ This is straightforward using Lbelongs_to('address' => 'My::Address'); package My::Address; - use base 'DBIx::Class'; - __PACKAGE__->load_components('Core'); + use base 'DBIx::Class::Core'; __PACKAGE__->table('address'); __PACKAGE__->add_columns(qw/id street town area_code country/); __PACKAGE__->set_primary_key('id'); @@ -1067,6 +1114,16 @@ This is straightforward using Laddresses(); # get all addresses for a user $rs = $address->users(); # get all users for an address + my $address = $user->add_to_addresses( # returns a My::Address instance, + # NOT a My::UserAddress instance! + { + country => 'United Kingdom', + area_code => 'XYZ', + town => 'London', + street => 'Sesame', + } + ); + =head2 Relationships across DB schemas Mapping relationships across L @@ -1078,8 +1135,7 @@ To accomplish this one only needs to specify the DB schema name in the table declaration, like so... package MyDatabase::Main::Artist; - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/PK::Auto Core/); + use base qw/DBIx::Class::Core/; __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause @@ -1195,9 +1251,101 @@ example of the recommended way to use it: 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. +transaction to fail. + +=head2 Nested transactions and auto-savepoints + +If savepoints are supported by your RDBMS, it is possible to achieve true +nested transactions with minimal effort. To enable auto-savepoints via nested +transactions, supply the C<< auto_savepoint = 1 >> connection attribute. + +Here is an example of true nested transactions. In the example, we start a big +task which will create several rows. Generation of data for each row is a +fragile operation and might fail. If we fail creating something, depending on +the type of failure, we want to abort the whole task, or only skip the failed +row. + + my $schema = MySchema->connect("dbi:Pg:dbname=my_db"); + + # Start a transaction. Every database change from here on will only be + # commited into the database if the eval block succeeds. + eval { + $schema->txn_do(sub { + # SQL: BEGIN WORK; + + my $job = $schema->resultset('Job')->create({ name=> 'big job' }); + # SQL: INSERT INTO job ( name) VALUES ( 'big job' ); + + for (1..10) { + + # Start a nested transaction, which in fact sets a savepoint. + eval { + $schema->txn_do(sub { + # SQL: SAVEPOINT savepoint_0; + + my $thing = $schema->resultset('Thing')->create({ job=>$job->id }); + # SQL: INSERT INTO thing ( job) VALUES ( 1 ); + + if (rand > 0.8) { + # This will generate an error, thus setting $@ + + $thing->update({force_fail=>'foo'}); + # SQL: UPDATE thing SET force_fail = 'foo' + # WHERE ( id = 42 ); + } + }); + }; + if ($@) { + # SQL: ROLLBACK TO SAVEPOINT savepoint_0; + + # There was an error while creating a $thing. Depending on the error + # we want to abort the whole transaction, or only rollback the + # changes related to the creation of this $thing + + # Abort the whole job + if ($@ =~ /horrible_problem/) { + print "something horrible happend, aborting job!"; + die $@; # rethrow error + } + + # Ignore this $thing, report the error, and continue with the + # next $thing + print "Cannot create thing: $@"; + } + # There was no error, so save all changes since the last + # savepoint. + + # SQL: RELEASE SAVEPOINT savepoint_0; + } + }); + }; + if ($@) { + # There was an error while handling the $job. Rollback all changes + # since the transaction started, including the already commited + # ('released') savepoints. There will be neither a new $job nor any + # $thing entry in the database. + + # SQL: ROLLBACK; + + print "ERROR: $@\n"; + } + else { + # There was no error while handling the $job. Commit all changes. + # Only now other connections can see the newly created $job and + # @things. + + # SQL: COMMIT; + + print "Ok\n"; + } + +In this example it might be hard to see where the rollbacks, releases and +commits are happening, but it works just the same as for plain L<>: If +the C-block around C fails, a rollback is issued. If the C +succeeds, the transaction is committed (or the savepoint released). + +While you can get more fine-grained controll using C, C +and C, it is strongly recommended to use C with coderefs. =head1 SQL @@ -1234,7 +1382,7 @@ 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}); + $schema->deploy({ add_drop_table => 1}); To import created .sql files using the mysql client: @@ -1272,8 +1420,7 @@ Make a table class as you would for any other table package MyAppDB::Dual; use strict; use warnings; - use base 'DBIx::Class'; - __PACKAGE__->load_components("Core"); + use base 'DBIx::Class::Core'; __PACKAGE__->table("Dual"); __PACKAGE__->add_columns( "dummy", @@ -1480,18 +1627,18 @@ database thinks it has. Alternatively, you can send the conversion sql scripts to your customers as above. -=head2 Setting quoting for the generated SQL. +=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('.'); + $schema->storage->sql_maker->quote_char([ qw/[ ]/] ); + $schema->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('"'); + $schema->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 @@ -1510,6 +1657,17 @@ L: } ) +In some cases, quoting will be required for all users of a schema. To enforce +this, you can also overload the C method for your schema class: + + sub connection { + my $self = shift; + my $rv = $self->next::method( @_ ); + $rv->storage->sql_maker->quote_char([ qw/[ ]/ ]); + $rv->storage->sql_maker->name_sep('.'); + return $rv; + } + =head2 Setting limit dialect for SQL::Abstract::Limit In some cases, SQL::Abstract::Limit cannot determine the dialect of @@ -1847,15 +2005,15 @@ details on creating static schemas from a database). Typically L result classes start off with - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/); + use base qw/DBIx::Class::Core/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime/); If this preamble is moved into a common base class:- package MyDBICbase; - use base qw/DBIx::Class/; - __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/); + use base qw/DBIx::Class::Core/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime/); 1; and each result class then uses this as a base:-