X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=c84df814b71dc0d910916e61470b1f28954418a3;hb=48580715af3072905f2c71dc27e7f70f21a11338;hp=4192ee3414669b4ee5c761547afc16d294f1dd49;hpb=0472cc0463f566c7d8d1b1fdf887fe6f60024560;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index 4192ee3..c84df81 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -1,15 +1,13 @@ -=head1 NAME +=head1 NAME DBIx::Class::Manual::Cookbook - Miscellaneous recipes -=head1 RECIPES +=head1 SEARCHING -=head2 Searching - -=head3 Paged results +=head2 Paged results 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: +paged resultset, which will fetch only a defined number of records at a time: my $rs = $schema->resultset('Artist')->search( undef, @@ -21,23 +19,14 @@ paged resultset, which will fetch only a small number of records at a time: return $rs->all(); # all records for page 1 -The C attribute does not have to be specified in your search: - - my $rs = $schema->resultset('Artist')->search( - undef, - { - rows => 10, - } - ); - - return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records + return $rs->page(2); # records for page 2 -In either of the above cases, you can return a L object for the -resultset (suitable for use in e.g. a template) using the C method: +You can get a L object for the resultset (suitable for use +in e.g. a template) using the C method: return $rs->pager(); -=head3 Complex WHERE clauses +=head2 Complex WHERE clauses Sometimes you need to formulate a query using specific operators: @@ -48,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: @@ -70,7 +62,106 @@ This results in the following C clause: For more information on generating complex queries, see L. -=head3 Using specific columns +=head2 Retrieve one and only one row from a resultset + +Sometimes you need only the first "top" row of a resultset. While this +can be easily done with L<< $rs->first|DBIx::Class::ResultSet/first +>>, it is suboptimal, as a full blown cursor for the resultset will be +created and then immediately destroyed after fetching the first row +object. L<< $rs->single|DBIx::Class::ResultSet/single >> is designed +specifically for this case - it will grab the first returned result +without even instantiating a cursor. + +Before replacing all your calls to C with C please observe the +following CAVEATS: + +=over + +=item * + +While single() takes a search condition just like search() does, it does +_not_ accept search attributes. However one can always chain a single() to +a search(): + + my $top_cd = $cd_rs->search({}, { order_by => 'rating' })->single; + + +=item * + +Since single() is the engine behind find(), it is designed to fetch a +single row per database query. Thus a warning will be issued when the +underlying SELECT returns more than one row. Sometimes however this usage +is valid: i.e. we have an arbitrary number of cd's but only one of them is +at the top of the charts at any given time. If you know what you are doing, +you can silence the warning by explicitly limiting the resultset size: + + my $top_cd = $cd_rs->search ({}, { order_by => 'rating', rows => 1 })->single; + +=back + +=head2 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. + +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::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 + WHERE f.friend_user_id = ? + UNION + SELECT u.* FROM user u + INNER JOIN user_friends f ON u.id = f.friend_user_id + WHERE f.user_id = ? + ]); + +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 (except, of course, +that you cannot modify the rows it contains, e.g. cannot call L, +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 C to specify which ones you need. This is useful to avoid @@ -90,7 +181,7 @@ use anyway: This is a shortcut for C and C. -=head3 Using database functions or stored procedures +=head2 Using database functions or stored procedures The combination of C to @@ -107,12 +198,36 @@ to access the returned value: ); # Equivalent SQL: - # SELECT name name, LENGTH( name ) name_length + # SELECT name name, LENGTH( name ) # FROM artist -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(); @@ -127,37 +242,59 @@ any of your aliases using either of these: # Define accessor manually: sub name_length { shift->get_column('name_length'); } - + # Or use DBIx::Class::AccessorGroup: __PACKAGE__->mk_group_accessors('column' => 'name_length'); -=head3 SELECT DISTINCT with multiple columns +See also L. + +=head2 SELECT DISTINCT with multiple columns - my $rs = $schema->resultset('Foo')->search( + my $rs = $schema->resultset('Artist')->search( {}, { - select => [ - { distinct => [ $source->columns ] } - ], - as => [ $source->columns ] + columns => [ qw/artist_id name rank/ ], + distinct => 1 + } + ); + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/artist_id name rank/ ], + group_by => [ qw/artist_id name rank/ ], } ); - my $count = $rs->next->get_column('count'); + # Equivalent SQL: + # SELECT me.artist_id, me.name, me.rank + # FROM artist me + # GROUP BY artist_id, name, rank -=head3 SELECT COUNT(DISTINCT colname) +=head2 SELECT COUNT(DISTINCT colname) - my $rs = $schema->resultset('Foo')->search( + my $rs = $schema->resultset('Artist')->search( {}, { - select => [ - { count => { distinct => 'colname' } } - ], - as => [ 'count' ] + columns => [ qw/name/ ], + distinct => 1 + } + ); + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/name/ ], + group_by => [ qw/name/ ], } ); -=head3 Grouping results + my $count = $rs->count; + + # Equivalent SQL: + # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq: + +=head2 Grouping results L supports C as follows: @@ -165,21 +302,73 @@ L supports C as follows: {}, { join => [qw/ cds /], - select => [ 'name', { count => 'cds.cdid' } ], + select => [ 'name', { count => 'cds.id' } ], as => [qw/ name cd_count /], group_by => [qw/ name /] } ); # Equivalent SQL: - # SELECT name, COUNT( cds.cdid ) FROM artist me - # LEFT JOIN cd cds ON ( cds.artist = me.artistid ) + # SELECT name, COUNT( cd.id ) FROM artist + # LEFT JOIN cd ON artist.id = cd.artist # GROUP BY name -=head3 Predefined searches +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 + +You can write subqueries relatively easily in DBIC. + + my $inside_rs = $schema->resultset('Artist')->search({ + name => [ 'Billy Joel', 'Brittany Spears' ], + }); + + my $rs = $schema->resultset('CD')->search({ + artist_id => { 'IN' => $inside_rs->get_column('id')->as_query }, + }); + +The usual operators ( =, !=, IN, NOT IN, etc.) are supported. + +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, # does NOT work + }); + +=head3 Support + +Subqueries are supported in the where clause (first hashref), and in the +from, select, and +select attributes. + +=head3 Correlated subqueries + + my $cdrs = $schema->resultset('CD'); + my $rs = $cdrs->search({ + year => { + '=' => $cdrs->search( + { artist_id => { '=' => \'me.artist_id' } }, + { alias => 'inner' } + )->get_column('year')->max_rs->as_query, + }, + }); + +That creates the following SQL: + + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track + FROM cd me + WHERE year = ( + SELECT MAX(inner.year) + FROM cd inner + WHERE artist_id = me.artist_id + ) + +=head2 Predefined searches You can write your own L class by inheriting from it -and define often used searches as methods: +and defining often used searches as methods: package My::DBIC::ResultSet::CD; use strict; @@ -197,48 +386,84 @@ and define 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 + use base 'DBIx::Class::Core'; + __PACKAGE__->table('cd'); + + # tell DBIC to use the custom ResultSet class __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD'); +Note that C must be called after C and C, or you will get errors about missing methods. + Then call your new method in your code: my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); +=head2 Using SQL functions on the left hand side of a comparison -=head3 Predefined searches without writing a ResultSet class +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. -Alternatively you can automatically generate a DBIx::Class::ResultSet -class by using the ResultSetManager component and tagging your method -as ResultSet: +If you do not have quoting on, simply include the function in your search +specification as you would any column: - __PACKAGE__->load_components(qw/ ResultSetManager Core /); + $rs->search({ 'YEAR(date_of_birth)' => 1979 }); - sub search_cds_ordered : ResultSet { - my ($self) = @_; - return $self->search( - {}, - { order_by => 'name DESC' }, - ); - } +With quoting on, or for a more portable solution, use literal SQL values with +placeholders: -Then call your method in the same way from your code: + $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]); - my $ordered_cds = $schema->resultset('CD')->search_cds_ordered(); + # Equivalent SQL: + # SELECT * FROM employee WHERE YEAR(date_of_birth) = ? + + $rs->search({ + name => 'Bob', + -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ], + }); + + # Equivalent SQL: + # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ? + +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. + +See also L. + +=head1 JOINS AND PREFETCHING =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: +results by, one or more columns in a related table. + +This requires that you have defined the L. For example : + + My::Schema::CD->has_many( artists => 'My::Schema::Artist', 'artist_id'); + +To return all CDs matching a particular artist name, you specify the name of the relationship ('artists'): my $rs = $schema->resultset('CD')->search( { - 'artist.name' => 'Bob Marley' + 'artists.name' => 'Bob Marley' }, { - join => [qw/artist/], # join the artist table + join => 'artists', # join the artist table } ); @@ -247,18 +472,21 @@ a particular artist name: # JOIN artist ON cd.artist = artist.id # WHERE artist.name = 'Bob Marley' +In that example both the join, and the condition use the relationship name rather than the table name +(see L for more details on aliasing ). + If required, you can now sort on any column in the related tables by including -it in your C attribute: +it in your C attribute, (again using the aliased relation name rather than table name) : my $rs = $schema->resultset('CD')->search( { - 'artist.name' => 'Bob Marley' + 'artists.name' => 'Bob Marley' }, { - join => [qw/ artist /], - order_by => [qw/ artist.name /] + join => 'artists', + order_by => [qw/ artists.name /] } - }; + ); # Equivalent SQL: # SELECT cd.* FROM cd @@ -293,12 +521,12 @@ This allows you to fetch results from related tables in advance: my $rs = $schema->resultset('CD')->search( { - 'artist.name' => 'Bob Marley' + 'artists.name' => 'Bob Marley' }, { - join => [qw/ artist /], - order_by => [qw/ artist.name /], - prefetch => [qw/ artist /] # return artist data too! + join => 'artists', + order_by => [qw/ artists.name /], + prefetch => 'artists' # return artist data too! } ); @@ -318,14 +546,39 @@ 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.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 only need columns from the main table will make performance worse! -=head3 Multi-step joins +=head2 Multiple joins + +In the examples above, the C attribute was a scalar. If you +pass an array reference instead, you can join to multiple tables. In +this example, we want to limit the search further, using +C: + + # Relationships defined elsewhere: + # CD->belongs_to('artist' => 'Artist'); + # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + 'liner_notes.notes' => { 'like', '%some text%' }, + }, + { + join => [qw/ artist liner_notes /], + order_by => [qw/ artist.name /], + } + ); + + # Equivalent SQL: + # SELECT cd.*, artist.*, liner_notes.* FROM cd + # JOIN artist ON cd.artist = artist.id + # JOIN liner_notes ON cd.id = liner_notes.cd + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name + +=head2 Multi-step joins Sometimes you want to join more than one relationship deep. In this example, we want to find all C objects who have Cs whose C @@ -348,8 +601,8 @@ contain a specific string: # Equivalent SQL: # SELECT artist.* FROM artist - # JOIN ( cd ON artist.id = cd.artist ) - # JOIN ( liner_notes ON cd.id = liner_notes.cd ) + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT 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 @@ -375,15 +628,42 @@ notes: # 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 ) + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT JOIN liner_notes ON cd.id = liner_notes.cd + # LEFT JOIN author ON author.id = liner_notes.author # WHERE liner_notes.notes LIKE '%some text%' # AND author.name = 'A. Writer' +=head2 Multi-step and multiple joins + +With various combinations of array and hash references, you can join +tables in any combination you desire. For example, to join Artist to +CD and Concert, and join CD to LinerNotes: + + # Relationships defined elsewhere: + # Artist->has_many('concerts' => 'Concert', 'artist'); + + my $rs = $schema->resultset('Artist')->search( + { }, + { + join => [ + { + cds => 'liner_notes' + }, + 'concerts' + ], + } + ); + + # Equivalent SQL: + # SELECT artist.* FROM artist + # LEFT JOIN cd ON artist.id = cd.artist + # LEFT JOIN liner_notes ON cd.id = liner_notes.cd + # LEFT JOIN concert ON artist.id = concert.artist + =head2 Multi-step prefetch -From 0.04999_05 onwards, C can be nested more than one relationship +C can be nested more than one relationship deep using the same syntax as a multi-step join: my $rs = $schema->resultset('Tag')->search( @@ -397,8 +677,8 @@ deep using the same syntax as a multi-step join: # Equivalent SQL: # SELECT tag.*, cd.*, artist.* FROM tag - # JOIN cd ON tag.cd = cd.cdid - # JOIN artist ON cd.artist = artist.artistid + # JOIN cd ON tag.cd = cd.id + # JOIN artist ON cd.artist = artist.id Now accessing our C and C relationships does not need additional SQL statements: @@ -406,133 +686,409 @@ SQL statements: my $tag = $rs->first; print $tag->cd->artist->name; -=head2 Columns of data +=head1 ROW-LEVEL OPERATIONS -If you want to find the sum of a particular column there are several -ways, the obvious one is to use search: +=head2 Retrieving a row object's Schema - my $rs = $schema->resultset('Items')->search( - {}, - { - select => [ { sum => 'Cost' } ], - as => [ 'total_cost' ], - } - ); - my $tc = $rs->first->get_column('total_cost'); +It is possible to get a Schema object from a row object like so: -Or, you can use the L, which gets -returned when you ask the C for a column using -C: + my $schema = $cd->result_source->schema; + # use the schema as normal: + my $artist_rs = $schema->resultset('Artist'); - my $cost = $schema->resultset('Items')->get_column('Cost'); - my $tc = $cost->sum; +This can be useful when you don't want to pass around a Schema object to every +method. -With this you can also do: +=head2 Getting the value of the primary key for the last database insert - my $minvalue = $cost->min; - my $maxvalue = $cost->max; +AKA getting last_insert_id -Or just iterate through the values of this column only: +Thanks to the core component PK::Auto, this is straightforward: - while ( my $c = $cost->next ) { - print $c; - } + my $foo = $rs->create(\%blah); + # do more stuff + my $id = $foo->id; # foo->my_primary_key_field will also work. - foreach my $c ($cost->all) { - print $c; - } +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. -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: +=head2 Stringification - my $avg = $cost->func('AVERAGE'); +Employ the standard stringification technique by using the L +module. -This will cause the following SQL statement to be run: +To make an object stringify itself as a single column, use something +like this (replace C with the column/method of your choice): - SELECT AVERAGE(Cost) FROM Items me + use overload '""' => sub { shift->name}, fallback => 1; -Which will of course only work if your database supports this function. -See L for more documentation. +For more complex stringification, you can use an anonymous subroutine: -=head2 Using relationships + use overload '""' => sub { $_[0]->name . ", " . + $_[0]->address }, fallback => 1; -=head3 Create a new row in a related table +=head3 Stringification Example - my $book->create_related('author', { name => 'Fred'}); +Suppose we have two tables: C and C. The table +specifications are: -=head3 Search in a related table + Product(id, Description, category) + Category(id, Description) -Only searches for books named 'Titanic' by the author in $author. +C is a foreign key into the Category table. - my $author->search_related('books', { name => 'Titanic' }); +If you have a Product object C<$obj> and write something like -=head3 Delete data in a related table + print $obj->category -Deletes only the book named Titanic by the author in $author. +things will not work as expected. - my $author->delete_related('books', { name => 'Titanic' }); +To obtain, for example, the category description, you should add this +method to the class defining the Category table: -=head3 Ordering a relationship result set + use overload "" => sub { + my $self = shift; -If you always want a relation to be ordered, you can specify this when you -create the relationship. + return $self->Description; + }, fallback => 1; -To order C<< $book->pages >> by descending page_number. +=head2 Want to know if find_or_create found or created a row? - Book->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} ); +Just use C instead, then check C: + my $obj = $rs->find_or_new({ blah => 'blarg' }); + unless ($obj->in_storage) { + $obj->insert; + # do whatever else you wanted if it was a new row + } +=head2 Static sub-classing DBIx::Class result classes -=head2 Transactions +AKA adding additional relationships/methods/etc. to a model for a +specific usage of the (shared) model. -As of version 0.04001, there is improved transaction support in -L and L. Here is an -example of the recommended way to use it: +B - my $genus = $schema->resultset('Genus')->find(12); + package My::App::Schema; - my $coderef2 = sub { - $genus->extinct(1); - $genus->update; - }; + use base 'DBIx::Class::Schema'; - my $coderef1 = sub { - $genus->add_to_species({ name => 'troglodyte' }); - $genus->wings(2); - $genus->update; - $schema->txn_do($coderef2); # Can have a nested transaction - return $genus->species; - }; + # load subclassed classes from My::App::Schema::Result/ResultSet + __PACKAGE__->load_namespaces; - my $rs; - eval { - $rs = $schema->txn_do($coderef1); - }; + # load classes from shared model + load_classes({ + 'My::Shared::Model::Result' => [qw/ + Foo + Bar + /]}); - if ($@) { # Transaction failed - die "the sky is falling!" # - if ($@ =~ /Rollback failed/); # Rollback failed + 1; - deal_with_failed_transaction(); - } +B -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. + package My::App::Schema::Result::Baz; -=head2 Many-to-many relationships + use strict; + use warnings; + use base 'My::Shared::Model::Result::Baz'; -This is straightforward using L: + # WARNING: Make sure you call table() again in your subclass, + # otherwise DBIx::Class::ResultSourceProxy::Table will not be called + # and the class name is not correctly registered as a source + __PACKAGE__->table('baz'); + + sub additional_method { + return "I'm an additional method only needed by this app"; + } + + 1; + +=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 +separate 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 My::Schema; + + use base qw/DBIx::Class::Schema/; + + __PACKAGE__->load_namespaces; + + 1; + + +B + + package My::Schema::Result::User; + + use strict; + use warnings; + use base qw/DBIx::Class::Core/; + + ### Define what our admin class is, for ensure_class_loaded() + my $admin_class = __PACKAGE__ . '::Admin'; + + __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 ; + } + + 1; + + + package My::Schema::Result::User::Admin; + + use strict; + use warnings; + use base qw/My::Schema::Result::User/; + + # This line is important + __PACKAGE__->table('users'); + + sub hello + { + print "I am an admin.\n"; + return; + } + + sub do_admin_stuff + { + print "I am doing admin stuff\n"; + return ; + } + + 1; + +B test.pl + + use warnings; + use strict; + use My::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 = My::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'); + +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 +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? + +Beware, changing the Result class using +L will replace any existing class +completely including any special components loaded using +load_components, eg L. + +=head2 Get raw data for blindingly fast results - package My::DB; - # ... set up connection ... +If the L solution +above is not fast enough for you, you can use a DBIx::Class to return values +exactly as they come out of the database 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 L attribute of L to force ordering). + +=head1 RESULTSET OPERATIONS + +=head2 Getting Schema from a ResultSet + +To get the L object from a ResultSet, do the following: + + $rs->result_source->schema + +=head2 Getting Columns Of Data + +AKA Aggregating 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 Creating a result set from a set of rows + +Sometimes you have a (set of) row objects that you want to put into a +resultset without the need to hit the DB again. You can do that by using the +L method: + + my @uploadable_groups; + while (my $group = $groups->next) { + if ($group->can_upload($self)) { + push @uploadable_groups, $group; + } + } + my $new_rs = $self->result_source->resultset; + $new_rs->set_cache(\@uploadable_groups); + return $new_rs; + + +=head1 USING RELATIONSHIPS + +=head2 Create a new row in a related table + + my $author = $book->create_related('author', { name => 'Fred'}); + +=head2 Search in a related table + +Only searches for books named 'Titanic' by the author in $author. + + my $books_rs = $author->search_related('books', { name => 'Titanic' }); + +=head2 Delete data in a related table + +Deletes only the book named Titanic by the author in $author. + + $author->delete_related('books', { name => 'Titanic' }); + +=head2 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, create the relation +as follows: + + __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } ); + +=head2 Filtering a relationship result set + +If you want to get a filtered result set, you can just add add to $attr as follows: + + __PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } ); + +=head2 Many-to-many relationships + +This is straightforward using L: package My::User; - use base 'My::DB'; + use base 'DBIx::Class::Core'; __PACKAGE__->table('user'); __PACKAGE__->add_columns(qw/id name/); __PACKAGE__->set_primary_key('id'); @@ -540,7 +1096,7 @@ This is straightforward using Lmany_to_many('addresses' => 'user_address', 'address'); package My::UserAddress; - use base 'My::DB'; + use base 'DBIx::Class::Core'; __PACKAGE__->table('user_address'); __PACKAGE__->add_columns(qw/user address/); __PACKAGE__->set_primary_key(qw/user address/); @@ -548,7 +1104,7 @@ This is straightforward using Lbelongs_to('address' => 'My::Address'); package My::Address; - use base 'My::DB'; + use base 'DBIx::Class::Core'; __PACKAGE__->table('address'); __PACKAGE__->add_columns(qw/id street town area_code country/); __PACKAGE__->set_primary_key('id'); @@ -558,135 +1114,444 @@ This is straightforward using Laddresses(); # get all addresses for a user $rs = $address->users(); # get all users for an address -=head2 Setting default values for a row + 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', + } + ); -It's as simple as overriding the C method. Note the use of -C. +=head2 Relationships across DB schemas - sub new { - my ( $class, $attrs ) = @_; +Mapping relationships across L +is easy as long as the schemas themselves are all accessible via the same DBI +connection. In most cases, this means that they are on the same database host +as each other and your connecting database user has the proper permissions to them. - $attrs->{foo} = 'bar' unless defined $attrs->{foo}; +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::Core/; + + __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause + + __PACKAGE__->add_columns(qw/ artist_id name /); + __PACKAGE__->set_primary_key('artist_id'); + __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd'); + + 1; + +Whatever string you specify there will be used to build the "FROM" clause in SQL +queries. + +The big drawback to this is you now have DB schema names hardcoded in your +class files. This becomes especially troublesome if you have multiple instances +of your application to support a change lifecycle (e.g. DEV, TEST, PROD) and +the DB schemas are named based on the environment (e.g. database1_dev). + +However, one can dynamically "map" to the proper DB schema by overriding the +L method in your Schema class and +building a renaming facility, like so: + + package MyDatabase::Schema; + use Moose; + + extends 'DBIx::Class::Schema'; + + around connection => sub { + my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ ); + + my $postfix = delete $attr->{schema_name_postfix}; + + $inner->(@_); + + if ( $postfix ) { + $self->append_db_name($postfix); + } + }; + + sub append_db_name { + my ( $self, $postfix ) = @_; + + my @sources_with_db + = grep + { $_->name =~ /^\w+\./mx } + map + { $self->source($_) } + $self->sources; + + foreach my $source (@sources_with_db) { + my $name = $source->name; + $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx; - $class->next::method($attrs); + $source->name($name); + } } -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. + 1; -People looking for ways to do "triggers" with DBIx::Class are probably -just looking for this. +By overridding the L +method and extracting a custom option from the provided \%attr hashref one can +then simply iterate over all the Schema's ResultSources, renaming them as +needed. -=head2 Stringification +To use this facility, simply add or modify the \%attr hashref that is passed to +L, as follows: -Employ the standard stringification technique by using the C -module. + my $schema + = MyDatabase::Schema->connect( + $dsn, + $user, + $pass, + { + schema_name_postfix => '_dev' + # ... Other options as desired ... + }) -To make an object stringify itself as a single column, use something -like this (replace C with the column/method of your choice): +Obviously, one could accomplish even more advanced mapping via a hash map or a +callback routine. - use overload '""' => sub { shift->name}, fallback => 1; +=head1 TRANSACTIONS -For more complex stringification, you can use an anonymous subroutine: +As of version 0.04001, there is improved transaction support in +L and L. Here is an +example of the recommended way to use it: - use overload '""' => sub { $_[0]->name . ", " . - $_[0]->address }, fallback => 1; + 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; + $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit + return $genus->species; + }; + + 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(); + } + +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. + +=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 + # committed 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 committed + # ('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 + +=head2 Creating Schemas From An Existing Database + +L will connect to a database and create a +L and associated sources by examining the database. + +The recommend way of achieving this is to use the +L method: + + 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" ])' + +This will create a tree of files rooted at C<./lib/My/Schema/> containing +source definitions for all the tables found in the C database. + +=head2 Creating DDL SQL + +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_table => 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 +C<$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 Select from dual + +Dummy tables are needed by some databases to allow calling functions +or expressions that aren't based on table content, for examples of how +this applies to various database types, see: +L. + +Note: If you're using Oracles dual table don't B do anything +other than a select, if you CRUD on your dual table you *will* break +your database. + +Make a table class as you would for any other table + + package MyAppDB::Dual; + use strict; + use warnings; + use base 'DBIx::Class::Core'; + __PACKAGE__->table("Dual"); + __PACKAGE__->add_columns( + "dummy", + { data_type => "VARCHAR2", is_nullable => 0, size => 1 }, + ); + +Once you've loaded your table class select from it using C conditions to illustrate the different syntax +you could use for doing stuff like +C -=head3 Stringification Example + # get a sequence value + select => [ 'A_SEQ.nextval' ], -Suppose we have two tables: C and C. The table -specifications are: + # get create table sql + select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ], - Product(id, Description, category) - Category(id, Description) + # get a random num between 0 and 100 + select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ], -C is a foreign key into the Category table. + # what year is it? + select => [ { 'extract' => [ \'year from sysdate' ] } ], -If you have a Product object C<$obj> and write something like + # do some math + select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}], - print $obj->category + # which day of the week were you born on? + select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}], -things will not work as expected. + # select 16 rows from dual + select => [ "'hello'" ], + as => [ 'world' ], + group_by => [ 'cube( 1, 2, 3, 4 )' ], -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 Adding Indexes And Functions To Your SQL -=head2 Disconnecting cleanly +Often you will want indexes on columns on your table to speed up searching. To +do this, create a method called C in the relevant source +class (refer to the advanced +L if you wish +to share a hook between multiple sources): -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: + package My::Schema::Result::Artist; - $SIG{INT} = sub { - __PACKAGE__->storage->disconnect; - }; + __PACKAGE__->table('artist'); + __PACKAGE__->add_columns(id => { ... }, name => { ... }) -=head2 Schema import/export + sub sqlt_deploy_hook { + my ($self, $sqlt_table) = @_; -This functionality requires you to have L (also known as -"SQL Fairy") installed. + $sqlt_table->add_index(name => 'idx_name', fields => ['name']); + } -To create a DBIx::Class schema from an existing database: + 1; - sqlt --from DBI - --to DBIx::Class::File - --prefix "MySchema" > MySchema.pm +Sometimes you might want to change the index depending on the type of the +database for which SQL is being generated: -To create a MySQL database from an existing L schema, convert the -schema to MySQL's dialect of SQL: + my ($db_type = $sqlt_table->schema->translator->producer_type) + =~ s/^SQL::Translator::Producer:://; - sqlt --from SQL::Translator::Parser::DBIx::Class - --to MySQL - --DBIx::Class "MySchema.pm" > Schema1.sql - -And import using the mysql client: +You can also add hooks to the schema level to stop certain tables being +created: - mysql -h "host" -D "database" -u "user" -p < Schema1.sql + package My::Schema; -=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: + sub sqlt_deploy_hook { + my ($self, $sqlt_schema) = @_; - 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; + $sqlt_schema->drop_table('table_name'); + } + +You could also add views, procedures or triggers to the output using +L, +L or +L. -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 @@ -717,28 +1582,16 @@ Deploy update to customers =back -=head3 Create a DBIx::Class schema +B 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 SQL::Translator::Parser::DBIx::Class - --to MySQL - --DBIx::Class "MySchema.pm" > Schema1.mysql.sql +described under L -If you need to target databases from multiple vendors, just generate an SQL -script suitable for each. To support PostgreSQL too: +B - sqlt --from SQL::Translator::DBIx::Class - --to PostgreSQL - --DBIx::Class "MySchema.pm" > Schema1.pgsql.sql +Call L as above under L. -=head3 Deploy to customers +B There are several ways you could deploy your schema. These are probably beyond the scope of this recipe, but might include: @@ -756,24 +1609,64 @@ all part of your install. =back -=head3 Modify the schema to change functionality +B -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: +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. - sqlt --from SQL::Translator::DBIx::Class - --to MySQL - --DBIx::Class "Anything.pm" > Schema2.mysql.sql +B -Next, use sqlt-diff to create an SQL script that will update the customer's -database schema: +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 tries to run a newer schema version than the +database thinks it has. - sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL > SchemaUpdate.mysql.sql +Alternatively, you can send the conversion SQL scripts to your +customers as above. + +=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: + + $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<'>: + + $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 +generator to put the quotes the correct place. + +In most cases you should set these as part of the arguments passed to +L: + + my $schema = My::Schema->connect( + 'dbi:mysql:my_db', + 'db_user', + 'db_password', + { + quote_char => '"', + name_sep => '.' + } + ) -=head3 Deploy update to customers +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: -The schema update can be deployed to customers using the same method as before. + 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 @@ -790,33 +1683,120 @@ to Microsoft SQL-server (See more names in SQL::Abstract::Limit 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. +The limit dialect can also be set at connect time by specifying a +C key in the final hash as shown above. -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: +=head2 Working with PostgreSQL array types - __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] ); - __PACKAGE__->storage->sql_maker->name_sep('.'); +You can also assign values to PostgreSQL array columns by passing array +references in the C<\%columns> (C<\%vals>) hashref of the +L and L family of +methods: -The first sets the quote characters. Either a pair of matching -brackets, or a C<"> or C<'>: - - __PACKAGE__->storage->sql_maker->quote_char('"'); + $resultset->create({ + numbers => [1, 2, 3] + }); -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. + $row->update( + { + numbers => [1, 2, 3] + }, + { + day => '2008-11-24' + } + ); + +In conditions (e.g. C<\%cond> in the L family of +methods) you cannot directly use array references (since this is interpreted as +a list of values to be Ced), but you can use the following syntax to force +passing them as bind values: + + $resultset->search( + { + numbers => \[ '= ?', [numbers => [1, 2, 3]] ] + } + ); + +See L and L for more explanation. Note that +L sets L to C, so you must pass +the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in +arrayrefs together with the column name, like this: C<< [column_name => value] +>>. + +=head1 BOOTSTRAPPING/MIGRATING + +=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; -=head2 Overloading methods + 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'); -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 + 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. + +=head1 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 +=head2 Setting default values for a row + +It's as simple as overriding the C method. Note the use of +C. + + sub new { + my ( $class, $attrs ) = @_; + + $attrs->{foo} = 'bar' unless defined $attrs->{foo}; + + 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 Changing one field whenever another changes -For example, say that you have three columns, C, C, and +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: @@ -832,9 +1812,9 @@ You can accomplish this by overriding C: 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 +=head2 Automatically creating related objects -You might have a class C which has many Cs. Further, if you +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: @@ -848,7 +1828,50 @@ You can accomplish this by overriding C on your objects: 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 +=head2 Wrapping/overloading a column accessor + +B + +Say you have a table "Camera" and want to associate a description +with each camera. For most cameras, you'll be able to generate the description from +the other columns. However, in a few special cases you may want to associate a +custom description with a camera. + +B + +In your database schema, define a description field in the "Camera" table that +can contain text and null values. + +In DBIC, we'll overload the column accessor to provide a sane default if no +custom description is defined. The accessor will either return or generate the +description, depending on whether the field is null or not. + +First, in your "Camera" schema class, define the description field as follows: + + __PACKAGE__->add_columns(description => { accessor => '_description' }); + +Next, we'll define the accessor-wrapper subroutine: + + sub description { + my $self = shift; + + # If there is an update to the column, we'll let the original accessor + # deal with it. + return $self->_description(@_) if @_; + + # Fetch the column value. + my $description = $self->_description; + + # If there's something in the description field, then just return that. + return $description if defined $description && length $descripton; + + # Otherwise, generate a description. + return $self->generate_description; + } + +=head1 DEBUGGING AND PROFILING + +=head2 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. @@ -892,20 +1915,9 @@ 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 +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 @@ -925,7 +1937,7 @@ mechanism: my $sql = shift(); my $params = @_; - print "Executing $sql: ".join(', ', @params)."\n"; + $self->print("Executing $sql: ".join(', ', @params)."\n"); $start = time(); } @@ -934,7 +1946,8 @@ mechanism: my $sql = shift(); my @params = @_; - printf("Execution took %0.4f seconds.\n", time() - $start); + my $elapsed = sprintf("%0.4f", time() - $start); + $self->print("Execution took $elapsed seconds.\n"); $start = undef; } @@ -942,8 +1955,8 @@ mechanism: You can then install that class as the debugging object: - __PACKAGE__->storage()->debugobj(new My::Profiler()); - __PACKAGE__->storage()->debug(1); + __PACKAGE__->storage->debugobj(new My::Profiler()); + __PACKAGE__->storage->debug(1); A more complicated example might involve storing each execution of SQL in an array: @@ -962,227 +1975,98 @@ array: 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: +=head1 STARTUP SPEED - my $foo = $rs->create(\%blah); - # do more stuff - my $id = $foo->id; # foo->my_primary_key_field will also work. +L programs can have a significant startup delay +as the ORM loads all the relevant classes. This section examines +techniques for reducing the startup delay. -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. +These tips are are listed in order of decreasing effectiveness - so the +first tip, if applicable, should have the greatest effect on your +application. -=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. +=head2 Statically Define Your Schema -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'); +If you are using +L to build the +classes dynamically based on the database schema then there will be a +significant startup delay. -=head2 Skip object creation for faster results +For production use a statically defined schema (which can be generated +using L to dump +the database schema once - see +L and +L for more +details on creating static schemas from a database). -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. Luckily this is also fairly easy using -C: +=head2 Move Common Startup into a Base Class - # Define a class which just returns the results as a hashref: - package My::HashRefInflator; +Typically L result classes start off with - ## $me is the hashref of cols/data from the immediate resultsource - ## $prefetch is a deep hashref of all the data from the prefetched - ## related sources. + use base qw/DBIx::Class::Core/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime/); - sub mk_hash { - my ($me, $rest) = @_; +If this preamble is moved into a common base class:- - return { %$me, - map { ($_ => mk_hash(@{$rest->{$_}})) } keys %$rest - }; - } + package MyDBICbase; - sub inflate_result { - my ($self, $source, $me, $prefetch) = @_; - return mk_hash($me, $prefetch); - } + use base qw/DBIx::Class::Core/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime/); + 1; - # Change the object inflation to a hashref for just this resultset: - $rs->result_class('My::HashRefInflator'); - - my $datahashref = $rs->next; - foreach my $col (keys %$datahashref) { - if(!ref($datahashref->{$col})) { - # It's a plain value - } - elsif(ref($datahashref->{$col} eq 'HASH')) { - # It's a related value in a hashref - } - } +and each result class then uses this as a base:- -=head2 Want to know if find_or_create found or created a row? + use base qw/MyDBICbase/; -Just use C instead, then check C: +then the load_components is only performed once, which can result in a +considerable startup speedup for schemas with many classes. - my $obj = $rs->find_or_new({ blah => 'blarg' }); - unless ($obj->in_storage) { - $obj->insert; - # do whatever else you wanted if it was a new row - } +=head2 Explicitly List Schema Result Classes -=head3 Wrapping/overloading a column accessor +The schema class will normally contain -Problem: Say you have a table "Camera" and want to associate a description -with each camera. For most cameras, you'll be able to generate the description from -the other columns. However, in a few special cases you may want to associate a -custom description with a camera. + __PACKAGE__->load_classes(); -Solution: +to load the result classes. This will use L +to find and load the appropriate modules. Explicitly defining the +classes you wish to load will remove the overhead of +L and the related directory operations: -In your database schema, define a description field in the "Camera" table that -can contain text and null values. + __PACKAGE__->load_classes(qw/ CD Artist Track /); -In DBIC, we'll overload the column accessor to provide a sane default if no -custom description is defined. The accessor will either return or generate the -description, depending on whether the field is null or not. +If you are instead using the L +syntax to load the appropriate classes there is not a direct alternative +avoiding L. -First, in your "Camera" schema class, define the description field as follows: +=head1 MEMORY USAGE - __PACKAGE__->add_columns(description => { accessor => '_description' }); +=head2 Cached statements -Next, we'll define the accessor-wrapper subroutine: +L normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>. +This is normally a good idea, but if too many statements are cached, the database may use too much +memory and may eventually run out and fail entirely. If you suspect this may be the case, you may want +to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash: - sub description { - my $self = shift; + # print all currently cached prepared statements + print for keys %{$schema->storage->dbh->{CachedKids}}; + # get a count of currently cached prepared statements + my $count = scalar keys %{$schema->storage->dbh->{CachedKids}}; - # If there is an update to the column, we'll let the original accessor - # deal with it. - return $self->_description(@_) if @_; +If it's appropriate, you can simply clear these statements, automatically deallocating them in the +database: - # Fetch the column value. - my $description = $self->_description; + my $kids = $schema->storage->dbh->{CachedKids}; + delete @{$kids}{keys %$kids} if scalar keys %$kids > 100; - # If there's something in the description field, then just return that. - return $description if defined $description && length $descripton; +But what you probably want is to expire unused statements and not those that are used frequently. +You can accomplish this with L or L: - # Otherwise, generate a description. - return $self->generate_description; - } + use Tie::Cache; + use DB::Main; + my $schema = DB::Main->connect($dbi_dsn, $user, $pass, { + on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 }, + }); =cut