X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=e4b9aa14f836677e7496dd64aed4b9814eacd1bf;hb=46a05fd450f4848c6dc5f1302001571a79f4728e;hp=59df7df5608e7d83634b16c837dc642b0b547b3b;hpb=da4779ad043121cc4a8d1bc99ade71e70407bd72;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index 59df7df..e4b9aa1 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -12,7 +12,7 @@ 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: my $rs = $schema->resultset('Artist')->search( - {}, + undef, { page => 1, # page to return (defaults to 1) rows => 10, # number of results per page @@ -24,7 +24,7 @@ paged resultset, which will fetch only a small number of records at a time: The C attribute does not have to be specified in your search: my $rs = $schema->resultset('Artist')->search( - {}, + undef, { rows => 10, } @@ -76,9 +76,9 @@ When you only want selected columns from a table, you can use C to specify which ones you need: my $rs = $schema->resultset('Artist')->search( - {}, + undef, { - cols => [qw/ name /] + columns => [qw/ name /] } ); @@ -94,7 +94,7 @@ stored procedure name). You then use C to set the column name you will use to access the returned value: my $rs = $schema->resultset('Artist')->search( - {}, + undef, { select => [ 'name', { LENGTH => 'name' } ], as => [qw/ name name_length /], @@ -129,7 +129,7 @@ any of your aliases using either of these: =head3 SELECT DISTINCT with multiple columns my $rs = $schema->resultset('Foo')->search( - {}, + undef, { select => [ { distinct => [ $source->columns ] } @@ -138,10 +138,12 @@ any of your aliases using either of these: } ); + my $count = $rs->next->get_column('count'); + =head3 SELECT COUNT(DISTINCT colname) my $rs = $schema->resultset('Foo')->search( - {}, + undef, { select => [ { count => { distinct => 'colname' } } @@ -155,7 +157,7 @@ any of your aliases using either of these: L supports C as follows: my $rs = $schema->resultset('Artist')->search( - {}, + undef, { join => [qw/ cds /], select => [ 'name', { count => 'cds.cdid' } ], @@ -169,6 +171,57 @@ L supports C as follows: # 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 @@ -260,9 +313,8 @@ 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 L 0.04, C cannot be used with -C 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. 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 @@ -330,7 +382,7 @@ 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' @@ -349,32 +401,61 @@ 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' }); + =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. @@ -425,6 +506,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 @@ -439,7 +527,7 @@ 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; + __PACKAGE__->storage->disconnect; }; =head2 Schema import/export @@ -483,14 +571,14 @@ instead: validate => $validate || 0, parser_args => { 'DBIx::Schema' => $schema, - } + }, producer_args => { 'prefix' => 'My::Schema', - } + }, ); - $translator->parser('DBIx::Class'); - $translator->producer('DBIx::Class::File'); + $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; @@ -498,7 +586,7 @@ instead: print $output; You could use L to search for all subclasses in the MyDB::* -namespace, which is currently left as an excercise for the reader. +namespace, which is currently left as an exercise for the reader. =head2 Schema versioning @@ -601,4 +689,189 @@ in SQL::Abstract::Limit -documentation. 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. + =cut