X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FDBIx%2FClass%2FManual%2FCookbook.pod;h=b13b05a28831aa72fb3a0d0d8f22bb4b80ba2c06;hb=8935bae328ce157ac8917317badc5d2bfdd106c7;hp=c1fd262dc8544a25d9fbcb05b28e3dca886792aa;hpb=25af00d72b25cddcce6cccf1524cdcacfa032cb0;p=dbsrgits%2FDBIx-Class.git diff --git a/lib/DBIx/Class/Manual/Cookbook.pod b/lib/DBIx/Class/Manual/Cookbook.pod index c1fd262..b13b05a 100644 --- a/lib/DBIx/Class/Manual/Cookbook.pod +++ b/lib/DBIx/Class/Manual/Cookbook.pod @@ -1,132 +1,1931 @@ =head1 NAME -DBIx::Class::Manual::Cookbook - Misc recipes +DBIx::Class::Manual::Cookbook - Miscellaneous recipes -=head1 DESCRIPTION +=head1 SEARCHING -Things that could be handy +=head2 Paged results -=head1 RECIPES +When you expect a large number of results, you can ask L for a +paged resultset, which will fetch only a defined number of records at a time: -=head2 Disconnecting cleanly + my $rs = $schema->resultset('Artist')->search( + undef, + { + page => 1, # page to return (defaults to 1) + rows => 10, # number of results per page + }, + ); -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: + return $rs->all(); # all records for page 1 - $SIG{INT} = sub { - __PACKAGE__->storage->dbh->disconnect; - }; +You can get a L object for the resultset (suitable for use +in e.g. a template) using the C method: + + return $rs->pager(); + +=head2 Complex WHERE clauses + +Sometimes you need to formulate a query using specific operators: + + my @albums = $schema->resultset('Album')->search({ + artist => { 'like', '%Lamb%' }, + title => { 'like', '%Fear of Fours%' }, + }); + +This results in something like the following C clause: + + WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%' + +Other queries might require slightly more complex logic: + + my @albums = $schema->resultset('Album')->search({ + -or => [ + -and => [ + artist => { 'like', '%Smashing Pumpkins%' }, + title => 'Siamese Dream', + ], + artist => 'Starchildren', + ], + }); + +This results in the following C clause: + + WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' ) + OR artist = 'Starchildren' + +For more information on generating complex queries, see +L. + +=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. +The recommended way to accomplish this is by defining a separate ResultSource +for your query. You can then inject complete SQL statements using a scalar +reference (this is a feature of L). + +Say you want to run a complex custom query on your user data, here's what +you have to add to your User class: + + package My::Schema::Result::User; + + use base qw/DBIx::Class/; + + # ->load_components, ->table, ->add_columns, etc. + + # Make a new ResultSource based on the User class + my $source = __PACKAGE__->result_source_instance(); + my $new_source = $source->new( $source ); + $new_source->source_name( 'UserFriendsComplex' ); + + # Hand in your query as a scalar reference + # It will be added as a sub-select after FROM, + # so pay attention to the surrounding brackets! + $new_source->name( \<register_extra_source( 'UserFriendsComplex' => $new_source ); + +Next, you can execute your complex query using bind parameters like this: + + my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {}, + { + bind => [ 12345, 12345 ] + } + ) ]; + +... and you'll get back a perfect L (except, of course, +that you cannot modify the rows it contains, ie. cannot call L, +L, ... on it). + +If you prefer to have the definitions of these custom ResultSources in separate +files (instead of stuffing all of them into the same resultset class), you can +achieve the same with subclassing the resultset class and defining the +ResultSource there: + + package My::Schema::Result::UserFriendsComplex; + + use My::Schema::Result::User; + use base qw/My::Schema::Result::User/; + + __PACKAGE__->table('dummy'); # currently must be called before anything else + + # Hand in your query as a scalar reference + # It will be added as a sub-select after FROM, + # so pay attention to the surrounding brackets! + __PACKAGE__->name( \< to specify which ones you need. This is useful to avoid +loading columns with large amounts of data that you aren't about to +use anyway: + + my $rs = $schema->resultset('Artist')->search( + undef, + { + columns => [qw/ name /] + } + ); + + # Equivalent SQL: + # SELECT artist.name FROM artist + +This is a shortcut for C and C. + +=head2 Using database functions or stored procedures + +The combination of C to +specify the source for your column value (e.g. a column name, function, or +stored procedure name). You then use C to set the column name you will use +to access the returned value: + + my $rs = $schema->resultset('Artist')->search( + {}, + { + select => [ 'name', { LENGTH => 'name' } ], + as => [qw/ name name_length /], + } + ); + + # Equivalent SQL: + # 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: + + my $artist = $rs->first(); + my $name = $artist->name(); + +If on the other hand the alias does not correspond to an existing column, you +have to fetch the value using the C accessor: + + my $name_length = $artist->get_column('name_length'); + +If you don't like using C, you can always create an accessor for +any of your aliases using either of these: + + # Define accessor manually: + sub name_length { shift->get_column('name_length'); } + + # Or use DBIx::Class::AccessorGroup: + __PACKAGE__->mk_group_accessors('column' => 'name_length'); + +=head2 SELECT DISTINCT with multiple columns + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/artistid name rank/ ], + distinct => 1 + } + ); + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/artistid name rank/ ], + group_by => [ qw/artistid name rank/ ], + } + ); + + # Equivalent SQL: + # SELECT me.artistid, me.name, me.rank + # FROM artist me + # GROUP BY artistid, name, rank + +=head2 SELECT COUNT(DISTINCT colname) + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/name/ ], + distinct => 1 + } + ); + + my $rs = $schema->resultset('Artist')->search( + {}, + { + columns => [ qw/name/ ], + group_by => [ qw/name/ ], + } + ); + + 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: + + my $rs = $schema->resultset('Artist')->search( + {}, + { + join => [qw/ cds /], + select => [ 'name', { count => 'cds.id' } ], + as => [qw/ name cd_count /], + group_by => [qw/ name /] + } + ); + + # Equivalent SQL: + # SELECT name, COUNT( cd.id ) FROM artist + # LEFT JOIN cd ON artist.id = cd.artist + # GROUP BY name + +Please see L documentation if you +are in any way unsure about the use of the attributes above (C< join +>, C< select >, C< as > and C< group_by >). + +=head2 Subqueries (EXPERIMENTAL) + +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, + }); + +=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( + { artistid => { '=' => \'me.artistid' } }, + { 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 artistid = me.artistid + ) + +=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 +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: + + # class definition as normal + __PACKAGE__->load_components(qw/ 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 + +Using SQL functions on the left hand side of a comparison is generally +not a good idea since it requires a scan of the entire table. However, +it can be accomplished with C when necessary. + +If you do not have quoting on, simply include the function in your search +specification as you would any column: + + $rs->search({ 'YEAR(date_of_birth)' => 1979 }); + +With quoting on, or for a more portable solution, use the C +attribute: + + $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' }); + +=begin hidden + +(When the bind args ordering bug is fixed, this technique will be better +and can replace the one above.) + +With quoting on, or for a more portable solution, use the C and +C attributes: + + $rs->search({}, { + where => \'YEAR(date_of_birth) = ?', + bind => [ 1979 ] + }); + +=end hidden + +=head1 JOINS AND PREFETCHING =head2 Using joins and prefetch -See L. +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: -=head2 Transactions + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => 'artist', # join the artist table + } + ); -As of version 0.04001, there is improved transaction support in -L. Here is an example of the recommended way to use it: - - my $obj = Genus->find(12); - eval { - MyDB->txn_begin; - $obj->add_to_species({ name => 'troglodyte' }); - $obj->wings(2); - $obj->update; - cromulate($obj); # can have a nested transation - MyDB->txn_commit; - }; - if ($@) { eval { MyDB->txn_rollback } } # rollback might fail, too + # Equivalent SQL: + # SELECT cd.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' -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 transactions (for databases that -support them) will hopefully be added in the future. +If required, you can now sort on any column in the related tables by including +it in your C attribute: -=head2 Many-to-many relationships + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => 'artist', + order_by => [qw/ artist.name /] + } + ); -This is not as easy as it could be, but it's possible. Here's an example to -illustrate: + # Equivalent SQL: + # SELECT cd.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name - # Set up inherited connection information - package MyApp::DBIC; - use base qw/DBIx::Class/; +Note that the C attribute should only be used when you need to search or +sort using columns in a related table. Joining related tables when you only +need columns from the main table will make performance worse! - __PACKAGE__->load_components(qw/PK::Auto::SQLite Core DB/); - __PACKAGE__->connection(...); +Now let's say you want to display a list of CDs, each with the name of the +artist. The following will work fine: - # Set up a class for the 'authors' table - package MyApp::DBIC::Author; - use base qw/MyApp::DBIC/; + while (my $cd = $rs->next) { + print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; + } - __PACKAGE__->table('authors'); - __PACKAGE__->add_columns(qw/authID first_name last_name/); - __PACKAGE__->set_primary_key(qw/authID/); +There is a problem however. We have searched both the C and C tables +in our main query, but we have only returned data from the C table. To get +the artist name for any of the CD objects returned, L will go back +to the database: - # Define relationship to the link table - __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'authID'); + SELECT artist.* FROM artist WHERE artist.id = ? - # Create the accessor for books from the ::Author class - sub books { - my ($self) = @_; - return MyApp::DBIC::Book->search( - { 'b2a.authID' => $self->authID }, # WHERE clause - { join => 'b2a' } # join condition (part of search attrs) - # 'b2a' refers to the relationship named earlier in the Author class. - # 'b2a.authID' refers to the authID column of the b2a relationship, - # which becomes accessible in the search by being joined. - ); - } +A statement like the one above will run for each and every CD returned by our +main query. Five CDs, five extra queries. A hundred CDs, one hundred extra +queries! - # define the link table class - package MyApp::DBIC::Book2Author; - use base qw/MyApp::DBIC/; +Thankfully, L has a C attribute to solve this problem. +This allows you to fetch results from related tables in advance: - __PACKAGE__->table('book2author'); - __PACKAGE__->add_columns(qw/bookID authID/); - __PACKAGE__->set_primary_key(qw/bookID authID/); + my $rs = $schema->resultset('CD')->search( + { + 'artist.name' => 'Bob Marley' + }, + { + join => 'artist', + order_by => [qw/ artist.name /], + prefetch => 'artist' # return artist data too! + } + ); - __PACKAGE__->belongs_to('authID' => 'MyApp::DBIC::Author'); - __PACKAGE__->belongs_to('bookID' => 'MyApp::DBIC::Book'); + # Equivalent SQL (note SELECT from both "cd" and "artist"): + # SELECT cd.*, artist.* FROM cd + # JOIN artist ON cd.artist = artist.id + # WHERE artist.name = 'Bob Marley' + # ORDER BY artist.name - package MyApp::DBIC::Book; - use base qw/MyApp::DBIC/; +The code to print the CD list remains the same: - __PACKAGE__->table('books'); - __PACKAGE__->add_columns(qw/bookID title edition isbn publisher year/); - __PACKAGE__->set_primary_key(qw/bookID/); - - __PACKAGE__->has_many('b2a' => 'MyApp::DBIC::Book2Author', 'bookID'); + while (my $cd = $rs->next) { + print "CD: " . $cd->title . ", Artist: " . $cd->artist->name; + } - sub authors { - my ($self) = @_; - return MyApp::DBIC::Author->search( - { 'b2a.bookID' => $self->bookID }, # WHERE clause - { join => 'b2a' }); # join condition (part of search attrs) - } +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. - # So the above search returns an author record where the bookID field of the - # book2author table equals the bookID of the books (using the bookID - # relationship table +Also note that C should only be used when you know you will +definitely use data from a related table. Pre-fetching related tables when you +only need columns from the main table will make performance worse! -=head2 Setting default values +=head2 Multiple joins -It's as simple as overriding the C method. Note the use of C. +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: - sub new { - my( $class, $attrs ) = @_; - - $attrs->{ foo } = 'bar' unless defined $attrs->{ foo }; - - $class->next::method( $attrs ); + # 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 +contain a specific string: + + # Relationships defined elsewhere: + # Artist->has_many('cds' => 'CD', 'artist'); + # CD->has_one('liner_notes' => 'LinerNotes', 'cd'); + + my $rs = $schema->resultset('Artist')->search( + { + 'liner_notes.notes' => { 'like', '%some text%' }, + }, + { + join => { + 'cds' => 'liner_notes' + } + } + ); + + # Equivalent SQL: + # SELECT artist.* FROM artist + # 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 +want to reduce the number of Artists returned based on who wrote the liner +notes: + + # Relationship defined elsewhere: + # LinerNotes->belongs_to('author' => 'Person'); + + my $rs = $schema->resultset('Artist')->search( + { + 'liner_notes.notes' => { 'like', '%some text%' }, + 'author.name' => 'A. Writer' + }, + { + join => { + 'cds' => { + 'liner_notes' => 'author' + } + } } + ); + + # Equivalent SQL: + # SELECT artist.* FROM artist + # 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 + +C can be nested more than one relationship +deep using the same syntax as a multi-step join: + + my $rs = $schema->resultset('Tag')->search( + {}, + { + prefetch => { + cd => 'artist' + } + } + ); + + # Equivalent SQL: + # SELECT tag.*, cd.*, artist.* FROM tag + # JOIN cd ON tag.cd = cd.id + # JOIN artist ON cd.artist = artist.id + +Now accessing our C and C relationships does not need additional +SQL statements: + + my $tag = $rs->first; + print $tag->cd->artist->name; + +=head1 ROW-LEVEL OPERATIONS + +=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 Getting the value of the primary key for the last database insert + +AKA getting last_insert_id + +Thanks to the core component PK::Auto, this is straightforward: + + my $foo = $rs->create(\%blah); + # do more stuff + my $id = $foo->id; # foo->my_primary_key_field will also work. + +If you are not using autoincrementing primary keys, this will probably +not work, but then you already know the value of the last primary key anyway. =head2 Stringification -Deploy the standard stringification technique by using the C module. Replace -C with the column/method of your choice. +Employ the standard stringification technique by using the C +module. + +To make an object stringify itself as a single column, use something +like this (replace C with the column/method of your choice): + + use overload '""' => sub { shift->name}, fallback => 1; + +For more complex stringification, you can use an anonymous subroutine: + + use overload '""' => sub { $_[0]->name . ", " . + $_[0]->address }, fallback => 1; + +=head3 Stringification Example + +Suppose we have two tables: C and C. The table +specifications are: + + Product(id, Description, category) + Category(id, Description) + +C is a foreign key into the Category table. + +If you have a Product object C<$obj> and write something like + + print $obj->category + +things will not work as expected. + +To obtain, for example, the category description, you should add this +method to the class defining the Category table: + + use overload "" => sub { + my $self = shift; + + return $self->Description; + }, fallback => 1; + +=head2 Want to know if find_or_create found or created a row? + +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 + +AKA adding additional relationships/methods/etc. to a model for a +specific usage of the (shared) model. + +B + + package My::App::Schema; + + use base DBIx::Class::Schema; + + # load subclassed classes from My::App::Schema::Result/ResultSet + __PACKAGE__->load_namespaces; + + # load classes from shared model + load_classes({ + 'My::Shared::Model::Result' => [qw/ + Foo + Bar + /]}); + + 1; + +B + + package My::App::Schema::Result::Baz; + + use strict; + use warnings; + use base My::Shared::Model::Result::Baz; + + # 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 +seperate proxy-class files for this. We would be copying all the user +methods into the Admin class. There is a cleaner way to accomplish +this. + +Overriding the C method within the User proxy-class +gives us the effect we want. This method is called by +L when inflating a result from storage. So we +grab the object being returned, inspect the values we are looking for, +bless it if it's an admin object, and then return it. See the example +below: + +B + + package 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/; + + ### 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 ; + } + + 1; + + + package My::Schema::Result::User::Admin; + + use strict; + use warnings; + use base qw/My::Schema::Result::User/; + + 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'); + +=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 + +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. - use overload '""' => 'foo', fallback => 1; +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'} ); + +=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 'DBIx::Class'; + __PACKAGE__->load_components('Core'); + __PACKAGE__->table('user'); + __PACKAGE__->add_columns(qw/id name/); + __PACKAGE__->set_primary_key('id'); + __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user'); + __PACKAGE__->many_to_many('addresses' => 'user_address', 'address'); + + package My::UserAddress; + use base 'DBIx::Class'; + __PACKAGE__->load_components('Core'); + __PACKAGE__->table('user_address'); + __PACKAGE__->add_columns(qw/user address/); + __PACKAGE__->set_primary_key(qw/user address/); + __PACKAGE__->belongs_to('user' => 'My::User'); + __PACKAGE__->belongs_to('address' => 'My::Address'); + + package My::Address; + use base 'DBIx::Class'; + __PACKAGE__->load_components('Core'); + __PACKAGE__->table('address'); + __PACKAGE__->add_columns(qw/id street town area_code country/); + __PACKAGE__->set_primary_key('id'); + __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address'); + __PACKAGE__->many_to_many('users' => 'user_address', 'user'); + + $rs = $user->addresses(); # get all addresses for a user + $rs = $address->users(); # get all users for an address + +=head2 Relationships across DB schemas + +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. + +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/); + + __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause + + __PACKAGE__->add_columns(qw/ artistid name /); + __PACKAGE__->set_primary_key('artistid'); + __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; + + $source->name($name); + } + } + + 1; + +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. + +To use this facility, simply add or modify the \%attr hashref that is passed to +L, as follows: + + my $schema + = MyDatabase::Schema->connect( + $dsn, + $user, + $pass, + { + schema_name_postfix => '_dev' + # ... Other options as desired ... + }) + +Obviously, one could accomplish even more advanced mapping via a hash map or a +callback routine. + +=head1 TRANSACTIONS + +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: + + 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. Support for savepoints and for true nested +transactions (for databases that support them) will hopefully be added +in the future. + +=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_tables => 1}); + +To import created .sql files using the mysql client: + + mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql + +To create C conversion scripts to update a database to a +newer version of your schema at a later point, first set a new +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'; + __PACKAGE__->load_components("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 + + # get a sequence value + select => [ 'A_SEQ.nextval' ], + + # get create table sql + select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ], + + # get a random num between 0 and 100 + select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ], + + # what year is it? + select => [ { 'extract' => [ \'year from sysdate' ] } ], + + # do some math + select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}], + + # which day of the week were you born on? + select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}], + + # select 16 rows from dual + select => [ "'hello'" ], + as => [ 'world' ], + group_by => [ 'cube( 1, 2, 3, 4 )' ], + + + +=head2 Adding Indexes And Functions To Your SQL + +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): + + package My::Schema::Result::Artist; + + __PACKAGE__->table('artist'); + __PACKAGE__->add_columns(id => { ... }, name => { ... }) + + sub sqlt_deploy_hook { + my ($self, $sqlt_table) = @_; + + $sqlt_table->add_index(name => 'idx_name', fields => ['name']); + } + + 1; + +Sometimes you might want to change the index depending on the type of the +database for which SQL is being generated: + + my ($db_type = $sqlt_table->schema->translator->producer_type) + =~ s/^SQL::Translator::Producer:://; + +You can also add hooks to the schema level to stop certain tables being +created: + + package My::Schema; + + ... + + sub sqlt_deploy_hook { + my ($self, $sqlt_schema) = @_; + + $sqlt_schema->drop_table('table_name'); + } + +You could also add views, procedures or triggers to the output using +L, +L or +L. + + +=head2 Schema versioning + +The following example shows simplistically how you might use DBIx::Class to +deploy versioned schemas to your customers. The basic process is as follows: + +=over 4 + +=item 1. + +Create a DBIx::Class schema + +=item 2. + +Save the schema + +=item 3. + +Deploy to customers + +=item 4. + +Modify schema to change functionality + +=item 5. + +Deploy update to customers + +=back + +B + +This can either be done manually, or generated from an existing database as +described under L + +B + +Call L as above under L. + +B + +There are several ways you could deploy your schema. These are probably +beyond the scope of this recipe, but might include: + +=over 4 + +=item 1. + +Require customer to apply manually using their RDBMS. + +=item 2. + +Package along with your app, making database dump/schema update/tests +all part of your install. =back + +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 and the conversion scripts as +in L. + +B + +Add the L schema component to your +Schema class. This will add a new table to your database called +C which will keep track of which version is installed +and warn if the user trys to run a newer schema version than the +database thinks it has. + +Alternatively, you can send the conversion sql scripts to your +customers as above. + +=head2 Setting quoting for the generated SQL. + +If the database contains column names with spaces and/or reserved words, they +need to be quoted in the SQL queries. This is done using: + + __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] ); + __PACKAGE__->storage->sql_maker->name_sep('.'); + +The first sets the quote characters. Either a pair of matching +brackets, or a C<"> or C<'>: + + __PACKAGE__->storage->sql_maker->quote_char('"'); + +Check the documentation of your database for the correct quote +characters to use. C needs to be set to allow the SQL +generator to put the quotes the correct place. + +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 => '.' + } + ) + +=head2 Setting limit dialect for SQL::Abstract::Limit + +In some cases, SQL::Abstract::Limit cannot determine the dialect of +the remote SQL server by looking at the database handle. This is a +common problem when using the DBD::JDBC, since the DBD-driver only +know that in has a Java-driver available, not which JDBC driver the +Java component has loaded. This specifically sets the limit_dialect +to Microsoft SQL-server (See more names in SQL::Abstract::Limit +-documentation. + + __PACKAGE__->storage->sql_maker->limit_dialect('mssql'); + +The JDBC bridge is one way of getting access to a MSSQL server from a platform +that Microsoft doesn't deliver native client libraries for. (e.g. Linux) + +The limit dialect can also be set at connect time by specifying a +C key in the final hash as shown above. + +=head2 Working with PostgreSQL array types + +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: + + $resultset->create({ + numbers => [1, 2, 3] + }); + + $row->update( + { + numbers => [1, 2, 3] + }, + { + day => '2008-11-24' + } + ); + +In conditions (eg. 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; + + my $translator = SQL::Translator->new( + debug => $debug || 0, + trace => $trace || 0, + no_comments => $no_comments || 0, + show_warnings => $show_warnings || 0, + add_drop_table => $add_drop_table || 0, + validate => $validate || 0, + parser_args => { + 'DBIx::Schema' => $schema, + }, + producer_args => { + 'prefix' => 'My::Schema', + }, + ); + + $translator->parser('SQL::Translator::Parser::DBIx::Class'); + $translator->producer('SQL::Translator::Producer::DBIx::Class::File'); + + my $output = $translator->translate(@args) or die + "Error: " . $translator->error; + + print $output; + +You could use L to search for all subclasses in the MyDB::* +namespace, which is currently left as an exercise for the reader. + +=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. + +=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 +C. You would like to make changes to C and have +C be automagically set to the value of C squared. +You can accomplish this by overriding C: + + sub store_column { + my ( $self, $name, $value ) = @_; + if ($name eq 'number') { + $self->squared($value * $value); + } + $self->next::method($name, $value); + } + +Note that the hard work is done by the call to C, which +redispatches your call to store_column in the superclass(es). + +=head2 Automatically creating related objects + +You might have a class C which has many Cs. Further, if you +want to create a C object every time you insert an C object. +You can accomplish this by overriding C on your objects: + + sub insert { + my ( $self, @args ) = @_; + $self->next::method(@args); + $self->cds->new({})->fill_from_artist($self)->insert; + return $self; + } + +where C is a method you specify in C which sets +values in C based on the data in the C object you pass in. + +=head2 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. +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 Profiling + +When you enable L's debugging it prints the SQL +executed as well as notifications of query completion and transaction +begin/commit. If you'd like to profile the SQL you can subclass the +L class and write your own profiling +mechanism: + + package My::Profiler; + use strict; + + use base 'DBIx::Class::Storage::Statistics'; + + use Time::HiRes qw(time); + + my $start; + + sub query_start { + my $self = shift(); + my $sql = shift(); + my $params = @_; + + $self->print("Executing $sql: ".join(', ', @params)."\n"); + $start = time(); + } + + sub query_end { + my $self = shift(); + my $sql = shift(); + my @params = @_; + + my $elapsed = sprintf("%0.4f", time() - $start); + $self->print("Execution took $elapsed seconds.\n"); + $start = undef; + } + + 1; + +You can then install that class as the debugging object: + + __PACKAGE__->storage->debugobj(new My::Profiler()); + __PACKAGE__->storage->debug(1); + +A more complicated example might involve storing each execution of SQL in an +array: + + sub query_end { + my $self = shift(); + my $sql = shift(); + my @params = @_; + + my $elapsed = time() - $start; + push(@{ $calls{$sql} }, { + params => \@params, + elapsed => $elapsed + }); + } + +You could then create average, high and low execution times for an SQL +statement and dig down to see if certain parameters cause aberrant behavior. +You might want to check out L as well. + +=head1 STARTUP SPEED + +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. + +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 Statically Define Your Schema + +If you are using +L to build the +classes dynamically based on the database schema then there will be a +significant startup delay. + +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). + +=head2 Move Common Startup into a Base Class + +Typically L result classes start off with + + use base qw/DBIx::Class/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/); + +If this preamble is moved into a common base class:- + + package MyDBICbase; + + use base qw/DBIx::Class/; + __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/); + 1; + +and each result class then uses this as a base:- + + use base qw/MyDBICbase/; + +then the load_components is only performed once, which can result in a +considerable startup speedup for schemas with many classes. + +=head2 Explicitly List Schema Result Classes + +The schema class will normally contain + + __PACKAGE__->load_classes(); + +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:- + + __PACKAGE__->load_classes(qw/ CD Artist Track /); + +If you are instead using the L +syntax to load the appropriate classes there is not a direct alternative +avoiding L. + +=head1 MEMORY USAGE + +=head2 Cached statements + +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: + + # 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 it's appropriate, you can simply clear these statements, automatically deallocating them in the +database: + + my $kids = $schema->storage->dbh->{CachedKids}; + delete @{$kids}{keys %$kids} if scalar keys %$kids > 100; + +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: + + 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