3 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
9 When you expect a large number of results, you can ask L<DBIx::Class> for a
10 paged resultset, which will fetch only a defined number of records at a time:
12 my $rs = $schema->resultset('Artist')->search(
15 page => 1, # page to return (defaults to 1)
16 rows => 10, # number of results per page
20 return $rs->all(); # all records for page 1
22 return $rs->page(2); # records for page 2
24 You can get a L<Data::Page> object for the resultset (suitable for use
25 in e.g. a template) using the C<pager> method:
29 =head2 Complex WHERE clauses
31 Sometimes you need to formulate a query using specific operators:
33 my @albums = $schema->resultset('Album')->search({
34 artist => { 'like', '%Lamb%' },
35 title => { 'like', '%Fear of Fours%' },
38 This results in something like the following C<WHERE> clause:
40 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
42 Other queries might require slightly more complex logic:
44 my @albums = $schema->resultset('Album')->search({
47 artist => { 'like', '%Smashing Pumpkins%' },
48 title => 'Siamese Dream',
50 artist => 'Starchildren',
54 This results in the following C<WHERE> clause:
56 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
57 OR artist = 'Starchildren'
59 For more information on generating complex queries, see
60 L<SQL::Abstract/WHERE CLAUSES>.
62 =head2 Retrieve one and only one row from a resultset
64 Sometimes you need only the first "top" row of a resultset. While this
65 can be easily done with L<< $rs->first|DBIx::Class::ResultSet/first
66 >>, it is suboptimal, as a full blown cursor for the resultset will be
67 created and then immediately destroyed after fetching the first row
68 object. L<< $rs->single|DBIx::Class::ResultSet/single >> is designed
69 specifically for this case - it will grab the first returned result
70 without even instantiating a cursor.
72 Before replacing all your calls to C<first()> with C<single()> please observe the
79 While single() takes a search condition just like search() does, it does
80 _not_ accept search attributes. However one can always chain a single() to
83 my $top_cd = $cd_rs->search({}, { order_by => 'rating' })->single;
88 Since single() is the engine behind find(), it is designed to fetch a
89 single row per database query. Thus a warning will be issued when the
90 underlying SELECT returns more than one row. Sometimes however this usage
91 is valid: i.e. we have an arbitrary number of cd's but only one of them is
92 at the top of the charts at any given time. If you know what you are doing,
93 you can silence the warning by explicitly limiting the resultset size:
95 my $top_cd = $cd_rs->search ({}, { order_by => 'rating', rows => 1 })->single;
99 =head2 Arbitrary SQL through a custom ResultSource
101 Sometimes you have to run arbitrary SQL because your query is too complex
102 (e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to
103 be optimized for your database in a special way, but you still want to
104 get the results as a L<DBIx::Class::ResultSet>.
106 The recommended way to accomplish this is by defining a separate
107 L<ResultSource::View|DBIx::Class::ResultSource::View> for your query.
109 package My::Schema::Result::UserFriendsComplex;
112 use base qw/DBIx::Class/;
114 __PACKAGE__->load_components('Core');
115 __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
117 # ->table, ->add_columns, etc.
119 __PACKAGE__->result_source_instance->is_virtual(1);
120 __PACKAGE__->result_source_instance->view_definition(q[
121 SELECT u.* FROM user u
122 INNER JOIN user_friends f ON u.id = f.user_id
123 WHERE f.friend_user_id = ?
125 SELECT u.* FROM user u
126 INNER JOIN user_friends f ON u.id = f.friend_user_id
130 Next, you can execute your complex query using bind parameters like this:
132 my $friends = $schema->resultset( 'UserFriendsComplex' )->search( {},
134 bind => [ 12345, 12345 ]
138 ... and you'll get back a perfect L<DBIx::Class::ResultSet> (except, of course,
139 that you cannot modify the rows it contains, ie. cannot call L</update>,
140 L</delete>, ... on it).
142 Note that you cannot have bind parameters unless is_virtual is set to true.
144 If you're using the old C<< $rsrc_instance->name(\'( SELECT ...') >> method for
145 custom SQL, you are highly encouraged to update your code to use a virtual view
146 as above. Otherwise add the following code so that on C<< ->deploy >> there is
147 no attempt to create a table with that name:
149 sub sqlt_deploy_hook { $_[1]->schema->drop_table ($_[1]) }
151 =head2 Using specific columns
153 When you only want specific columns from a table, you can use
154 C<columns> to specify which ones you need. This is useful to avoid
155 loading columns with large amounts of data that you aren't about to
158 my $rs = $schema->resultset('Artist')->search(
161 columns => [qw/ name /]
166 # SELECT artist.name FROM artist
168 This is a shortcut for C<select> and C<as>, see below. C<columns>
169 cannot be used together with C<select> and C<as>.
171 =head2 Using database functions or stored procedures
173 The combination of C<select> and C<as> can be used to return the result of a
174 database function or stored procedure as a column value. You use C<select> to
175 specify the source for your column value (e.g. a column name, function, or
176 stored procedure name). You then use C<as> to set the column name you will use
177 to access the returned value:
179 my $rs = $schema->resultset('Artist')->search(
182 select => [ 'name', { LENGTH => 'name' } ],
183 as => [qw/ name name_length /],
188 # SELECT name name, LENGTH( name )
191 Note that the C< as > attribute has absolutely nothing to with the sql
192 syntax C< SELECT foo AS bar > (see the documentation in
193 L<DBIx::Class::ResultSet/ATTRIBUTES>). If your alias exists as a
194 column in your base class (i.e. it was added with C<add_columns>), you
195 just access it as normal. Our C<Artist> class has a C<name> column, so
196 we just use the C<name> accessor:
198 my $artist = $rs->first();
199 my $name = $artist->name();
201 If on the other hand the alias does not correspond to an existing column, you
202 have to fetch the value using the C<get_column> accessor:
204 my $name_length = $artist->get_column('name_length');
206 If you don't like using C<get_column>, you can always create an accessor for
207 any of your aliases using either of these:
209 # Define accessor manually:
210 sub name_length { shift->get_column('name_length'); }
212 # Or use DBIx::Class::AccessorGroup:
213 __PACKAGE__->mk_group_accessors('column' => 'name_length');
215 =head2 SELECT DISTINCT with multiple columns
217 my $rs = $schema->resultset('Artist')->search(
220 columns => [ qw/artist_id name rank/ ],
225 my $rs = $schema->resultset('Artist')->search(
228 columns => [ qw/artist_id name rank/ ],
229 group_by => [ qw/artist_id name rank/ ],
234 # SELECT me.artist_id, me.name, me.rank
236 # GROUP BY artist_id, name, rank
238 =head2 SELECT COUNT(DISTINCT colname)
240 my $rs = $schema->resultset('Artist')->search(
243 columns => [ qw/name/ ],
248 my $rs = $schema->resultset('Artist')->search(
251 columns => [ qw/name/ ],
252 group_by => [ qw/name/ ],
256 my $count = $rs->count;
259 # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq:
261 =head2 Grouping results
263 L<DBIx::Class> supports C<GROUP BY> as follows:
265 my $rs = $schema->resultset('Artist')->search(
269 select => [ 'name', { count => 'cds.id' } ],
270 as => [qw/ name cd_count /],
271 group_by => [qw/ name /]
276 # SELECT name, COUNT( cd.id ) FROM artist
277 # LEFT JOIN cd ON artist.id = cd.artist
280 Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
281 are in any way unsure about the use of the attributes above (C< join
282 >, C< select >, C< as > and C< group_by >).
284 =head2 Subqueries (EXPERIMENTAL)
286 You can write subqueries relatively easily in DBIC.
288 my $inside_rs = $schema->resultset('Artist')->search({
289 name => [ 'Billy Joel', 'Brittany Spears' ],
292 my $rs = $schema->resultset('CD')->search({
293 artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
296 The usual operators ( =, !=, IN, NOT IN, etc) are supported.
298 B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
299 The following will B<not> work:
301 my $rs = $schema->resultset('CD')->search({
302 artist_id => $inside_rs->get_column('id')->as_query,
307 Subqueries are supported in the where clause (first hashref), and in the
308 from, select, and +select attributes.
310 =head3 Correlated subqueries
312 my $cdrs = $schema->resultset('CD');
313 my $rs = $cdrs->search({
315 '=' => $cdrs->search(
316 { artist_id => { '=' => \'me.artist_id' } },
318 )->get_column('year')->max_rs->as_query,
322 That creates the following SQL:
324 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
327 SELECT MAX(inner.year)
329 WHERE artist_id = me.artist_id
334 Please note that subqueries are considered an experimental feature.
336 =head2 Predefined searches
338 You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
339 and defining often used searches as methods:
341 package My::DBIC::ResultSet::CD;
344 use base 'DBIx::Class::ResultSet';
346 sub search_cds_ordered {
349 return $self->search(
351 { order_by => 'name DESC' },
357 To use your resultset, first tell DBIx::Class to create an instance of it
358 for you, in your My::DBIC::Schema::CD class:
360 # class definition as normal
361 __PACKAGE__->load_components(qw/ Core /);
362 __PACKAGE__->table('cd');
364 # tell DBIC to use the custom ResultSet class
365 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
367 Note that C<resultset_class> must be called after C<load_components> and C<table>, or you will get errors about missing methods.
369 Then call your new method in your code:
371 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
373 =head2 Using SQL functions on the left hand side of a comparison
375 Using SQL functions on the left hand side of a comparison is generally
376 not a good idea since it requires a scan of the entire table. However,
377 it can be accomplished with C<DBIx::Class> when necessary.
379 If you do not have quoting on, simply include the function in your search
380 specification as you would any column:
382 $rs->search({ 'YEAR(date_of_birth)' => 1979 });
384 With quoting on, or for a more portable solution, use the C<where>
387 $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' });
391 (When the bind args ordering bug is fixed, this technique will be better
392 and can replace the one above.)
394 With quoting on, or for a more portable solution, use the C<where> and
398 where => \'YEAR(date_of_birth) = ?',
404 =head1 JOINS AND PREFETCHING
406 =head2 Using joins and prefetch
408 You can use the C<join> attribute to allow searching on, or sorting your
409 results by, one or more columns in a related table.
411 This requires that you have defined the L<DBIx::Class::Relationship>. For example :
413 My::Schema::CD->has_many( artists => 'My::Schema::Artist', 'artist_id');
415 To return all CDs matching a particular artist name, you specify the name of the relationship ('artists'):
417 my $rs = $schema->resultset('CD')->search(
419 'artists.name' => 'Bob Marley'
422 join => 'artists', # join the artist table
427 # SELECT cd.* FROM cd
428 # JOIN artist ON cd.artist = artist.id
429 # WHERE artist.name = 'Bob Marley'
431 In that example both the join, and the condition use the relationship name rather than the table name
432 (see L<DBIx::Class::Manual::Joining> for more details on aliasing ).
434 If required, you can now sort on any column in the related tables by including
435 it in your C<order_by> attribute, (again using the aliased relation name rather than table name) :
437 my $rs = $schema->resultset('CD')->search(
439 'artists.name' => 'Bob Marley'
443 order_by => [qw/ artists.name /]
448 # SELECT cd.* FROM cd
449 # JOIN artist ON cd.artist = artist.id
450 # WHERE artist.name = 'Bob Marley'
451 # ORDER BY artist.name
453 Note that the C<join> attribute should only be used when you need to search or
454 sort using columns in a related table. Joining related tables when you only
455 need columns from the main table will make performance worse!
457 Now let's say you want to display a list of CDs, each with the name of the
458 artist. The following will work fine:
460 while (my $cd = $rs->next) {
461 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
464 There is a problem however. We have searched both the C<cd> and C<artist> tables
465 in our main query, but we have only returned data from the C<cd> table. To get
466 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
469 SELECT artist.* FROM artist WHERE artist.id = ?
471 A statement like the one above will run for each and every CD returned by our
472 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
475 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
476 This allows you to fetch results from related tables in advance:
478 my $rs = $schema->resultset('CD')->search(
480 'artists.name' => 'Bob Marley'
484 order_by => [qw/ artists.name /],
485 prefetch => 'artists' # return artist data too!
489 # Equivalent SQL (note SELECT from both "cd" and "artist"):
490 # SELECT cd.*, artist.* FROM cd
491 # JOIN artist ON cd.artist = artist.id
492 # WHERE artist.name = 'Bob Marley'
493 # ORDER BY artist.name
495 The code to print the CD list remains the same:
497 while (my $cd = $rs->next) {
498 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
501 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
502 so no additional SQL statements are executed. You now have a much more
505 Also note that C<prefetch> should only be used when you know you will
506 definitely use data from a related table. Pre-fetching related tables when you
507 only need columns from the main table will make performance worse!
509 =head2 Multiple joins
511 In the examples above, the C<join> attribute was a scalar. If you
512 pass an array reference instead, you can join to multiple tables. In
513 this example, we want to limit the search further, using
516 # Relationships defined elsewhere:
517 # CD->belongs_to('artist' => 'Artist');
518 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
519 my $rs = $schema->resultset('CD')->search(
521 'artist.name' => 'Bob Marley'
522 'liner_notes.notes' => { 'like', '%some text%' },
525 join => [qw/ artist liner_notes /],
526 order_by => [qw/ artist.name /],
531 # SELECT cd.*, artist.*, liner_notes.* FROM cd
532 # JOIN artist ON cd.artist = artist.id
533 # JOIN liner_notes ON cd.id = liner_notes.cd
534 # WHERE artist.name = 'Bob Marley'
535 # ORDER BY artist.name
537 =head2 Multi-step joins
539 Sometimes you want to join more than one relationship deep. In this example,
540 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
541 contain a specific string:
543 # Relationships defined elsewhere:
544 # Artist->has_many('cds' => 'CD', 'artist');
545 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
547 my $rs = $schema->resultset('Artist')->search(
549 'liner_notes.notes' => { 'like', '%some text%' },
553 'cds' => 'liner_notes'
559 # SELECT artist.* FROM artist
560 # LEFT JOIN cd ON artist.id = cd.artist
561 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
562 # WHERE liner_notes.notes LIKE '%some text%'
564 Joins can be nested to an arbitrary level. So if we decide later that we
565 want to reduce the number of Artists returned based on who wrote the liner
568 # Relationship defined elsewhere:
569 # LinerNotes->belongs_to('author' => 'Person');
571 my $rs = $schema->resultset('Artist')->search(
573 'liner_notes.notes' => { 'like', '%some text%' },
574 'author.name' => 'A. Writer'
579 'liner_notes' => 'author'
586 # SELECT artist.* FROM artist
587 # LEFT JOIN cd ON artist.id = cd.artist
588 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
589 # LEFT JOIN author ON author.id = liner_notes.author
590 # WHERE liner_notes.notes LIKE '%some text%'
591 # AND author.name = 'A. Writer'
593 =head2 Multi-step and multiple joins
595 With various combinations of array and hash references, you can join
596 tables in any combination you desire. For example, to join Artist to
597 CD and Concert, and join CD to LinerNotes:
599 # Relationships defined elsewhere:
600 # Artist->has_many('concerts' => 'Concert', 'artist');
602 my $rs = $schema->resultset('Artist')->search(
615 # SELECT artist.* FROM artist
616 # LEFT JOIN cd ON artist.id = cd.artist
617 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
618 # LEFT JOIN concert ON artist.id = concert.artist
620 =head2 Multi-step prefetch
622 C<prefetch> can be nested more than one relationship
623 deep using the same syntax as a multi-step join:
625 my $rs = $schema->resultset('Tag')->search(
635 # SELECT tag.*, cd.*, artist.* FROM tag
636 # JOIN cd ON tag.cd = cd.id
637 # JOIN artist ON cd.artist = artist.id
639 Now accessing our C<cd> and C<artist> relationships does not need additional
642 my $tag = $rs->first;
643 print $tag->cd->artist->name;
645 =head1 ROW-LEVEL OPERATIONS
647 =head2 Retrieving a row object's Schema
649 It is possible to get a Schema object from a row object like so:
651 my $schema = $cd->result_source->schema;
652 # use the schema as normal:
653 my $artist_rs = $schema->resultset('Artist');
655 This can be useful when you don't want to pass around a Schema object to every
658 =head2 Getting the value of the primary key for the last database insert
660 AKA getting last_insert_id
662 Thanks to the core component PK::Auto, this is straightforward:
664 my $foo = $rs->create(\%blah);
666 my $id = $foo->id; # foo->my_primary_key_field will also work.
668 If you are not using autoincrementing primary keys, this will probably
669 not work, but then you already know the value of the last primary key anyway.
671 =head2 Stringification
673 Employ the standard stringification technique by using the L<overload>
676 To make an object stringify itself as a single column, use something
677 like this (replace C<name> with the column/method of your choice):
679 use overload '""' => sub { shift->name}, fallback => 1;
681 For more complex stringification, you can use an anonymous subroutine:
683 use overload '""' => sub { $_[0]->name . ", " .
684 $_[0]->address }, fallback => 1;
686 =head3 Stringification Example
688 Suppose we have two tables: C<Product> and C<Category>. The table
691 Product(id, Description, category)
692 Category(id, Description)
694 C<category> is a foreign key into the Category table.
696 If you have a Product object C<$obj> and write something like
700 things will not work as expected.
702 To obtain, for example, the category description, you should add this
703 method to the class defining the Category table:
705 use overload "" => sub {
708 return $self->Description;
711 =head2 Want to know if find_or_create found or created a row?
713 Just use C<find_or_new> instead, then check C<in_storage>:
715 my $obj = $rs->find_or_new({ blah => 'blarg' });
716 unless ($obj->in_storage) {
718 # do whatever else you wanted if it was a new row
721 =head2 Static sub-classing DBIx::Class result classes
723 AKA adding additional relationships/methods/etc. to a model for a
724 specific usage of the (shared) model.
728 package My::App::Schema;
730 use base DBIx::Class::Schema;
732 # load subclassed classes from My::App::Schema::Result/ResultSet
733 __PACKAGE__->load_namespaces;
735 # load classes from shared model
737 'My::Shared::Model::Result' => [qw/
744 B<Result-Subclass definition>
746 package My::App::Schema::Result::Baz;
750 use base My::Shared::Model::Result::Baz;
752 # WARNING: Make sure you call table() again in your subclass,
753 # otherwise DBIx::Class::ResultSourceProxy::Table will not be called
754 # and the class name is not correctly registered as a source
755 __PACKAGE__->table('baz');
757 sub additional_method {
758 return "I'm an additional method only needed by this app";
763 =head2 Dynamic Sub-classing DBIx::Class proxy classes
765 AKA multi-class object inflation from one table
767 L<DBIx::Class> classes are proxy classes, therefore some different
768 techniques need to be employed for more than basic subclassing. In
769 this example we have a single user table that carries a boolean bit
770 for admin. We would like like to give the admin users
771 objects (L<DBIx::Class::Row>) the same methods as a regular user but
772 also special admin only methods. It doesn't make sense to create two
773 seperate proxy-class files for this. We would be copying all the user
774 methods into the Admin class. There is a cleaner way to accomplish
777 Overriding the C<inflate_result> method within the User proxy-class
778 gives us the effect we want. This method is called by
779 L<DBIx::Class::ResultSet> when inflating a result from storage. So we
780 grab the object being returned, inspect the values we are looking for,
781 bless it if it's an admin object, and then return it. See the example
788 use base qw/DBIx::Class::Schema/;
790 __PACKAGE__->load_namespaces;
795 B<Proxy-Class definitions>
797 package My::Schema::Result::User;
801 use base qw/DBIx::Class/;
803 ### Define what our admin class is, for ensure_class_loaded()
804 my $admin_class = __PACKAGE__ . '::Admin';
806 __PACKAGE__->load_components(qw/Core/);
808 __PACKAGE__->table('users');
810 __PACKAGE__->add_columns(qw/user_id email password
811 firstname lastname active
814 __PACKAGE__->set_primary_key('user_id');
818 my $ret = $self->next::method(@_);
819 if( $ret->admin ) {### If this is an admin, rebless for extra functions
820 $self->ensure_class_loaded( $admin_class );
821 bless $ret, $admin_class;
827 print "I am a regular user.\n";
834 package My::Schema::Result::User::Admin;
838 use base qw/My::Schema::Result::User/;
840 # This line is important
841 __PACKAGE__->table('users');
845 print "I am an admin.\n";
851 print "I am doing admin stuff\n";
863 my $user_data = { email => 'someguy@place.com',
867 my $admin_data = { email => 'someadmin@adminplace.com',
871 my $schema = My::Schema->connection('dbi:Pg:dbname=test');
873 $schema->resultset('User')->create( $user_data );
874 $schema->resultset('User')->create( $admin_data );
876 ### Now we search for them
877 my $user = $schema->resultset('User')->single( $user_data );
878 my $admin = $schema->resultset('User')->single( $admin_data );
880 print ref $user, "\n";
881 print ref $admin, "\n";
883 print $user->password , "\n"; # pass1
884 print $admin->password , "\n";# pass2; inherited from User
885 print $user->hello , "\n";# I am a regular user.
886 print $admin->hello, "\n";# I am an admin.
888 ### The statement below will NOT print
889 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
890 ### The statement below will print
891 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
893 =head2 Skip row object creation for faster results
895 DBIx::Class is not built for speed, it's built for convenience and
896 ease of use, but sometimes you just need to get the data, and skip the
899 To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
901 my $rs = $schema->resultset('CD');
903 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
905 my $hash_ref = $rs->find(1);
909 Beware, changing the Result class using
910 L<DBIx::Class::ResultSet/result_class> will replace any existing class
911 completely including any special components loaded using
912 load_components, eg L<DBIx::Class::InflateColumn::DateTime>.
914 =head2 Get raw data for blindingly fast results
916 If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
917 above is not fast enough for you, you can use a DBIx::Class to return values
918 exactly as they come out of the database with none of the convenience methods
921 This is used like so:
923 my $cursor = $rs->cursor
924 while (my @vals = $cursor->next) {
925 # use $val[0..n] here
928 You will need to map the array offsets to particular columns (you can
929 use the L<DBIx::Class::ResultSet/select> attribute of L<DBIx::Class::ResultSet/search> to force ordering).
931 =head1 RESULTSET OPERATIONS
933 =head2 Getting Schema from a ResultSet
935 To get the L<DBIx::Class::Schema> object from a ResultSet, do the following:
937 $rs->result_source->schema
939 =head2 Getting Columns Of Data
943 If you want to find the sum of a particular column there are several
944 ways, the obvious one is to use search:
946 my $rs = $schema->resultset('Items')->search(
949 select => [ { sum => 'Cost' } ],
950 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL
953 my $tc = $rs->first->get_column('total_cost');
955 Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets
956 returned when you ask the C<ResultSet> for a column using
959 my $cost = $schema->resultset('Items')->get_column('Cost');
962 With this you can also do:
964 my $minvalue = $cost->min;
965 my $maxvalue = $cost->max;
967 Or just iterate through the values of this column only:
969 while ( my $c = $cost->next ) {
973 foreach my $c ($cost->all) {
977 C<ResultSetColumn> only has a limited number of built-in functions. If
978 you need one that it doesn't have, then you can use the C<func> method
981 my $avg = $cost->func('AVERAGE');
983 This will cause the following SQL statement to be run:
985 SELECT AVERAGE(Cost) FROM Items me
987 Which will of course only work if your database supports this function.
988 See L<DBIx::Class::ResultSetColumn> for more documentation.
990 =head2 Creating a result set from a set of rows
992 Sometimes you have a (set of) row objects that you want to put into a
993 resultset without the need to hit the DB again. You can do that by using the
994 L<set_cache|DBIx::Class::Resultset/set_cache> method:
996 my @uploadable_groups;
997 while (my $group = $groups->next) {
998 if ($group->can_upload($self)) {
999 push @uploadable_groups, $group;
1002 my $new_rs = $self->result_source->resultset;
1003 $new_rs->set_cache(\@uploadable_groups);
1007 =head1 USING RELATIONSHIPS
1009 =head2 Create a new row in a related table
1011 my $author = $book->create_related('author', { name => 'Fred'});
1013 =head2 Search in a related table
1015 Only searches for books named 'Titanic' by the author in $author.
1017 my $books_rs = $author->search_related('books', { name => 'Titanic' });
1019 =head2 Delete data in a related table
1021 Deletes only the book named Titanic by the author in $author.
1023 $author->delete_related('books', { name => 'Titanic' });
1025 =head2 Ordering a relationship result set
1027 If you always want a relation to be ordered, you can specify this when you
1028 create the relationship.
1030 To order C<< $book->pages >> by descending page_number, create the relation
1033 __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
1035 =head2 Filtering a relationship result set
1037 If you want to get a filtered result set, you can just add add to $attr as follows:
1039 __PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } );
1041 =head2 Many-to-many relationships
1043 This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
1046 use base 'DBIx::Class';
1047 __PACKAGE__->load_components('Core');
1048 __PACKAGE__->table('user');
1049 __PACKAGE__->add_columns(qw/id name/);
1050 __PACKAGE__->set_primary_key('id');
1051 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
1052 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
1054 package My::UserAddress;
1055 use base 'DBIx::Class';
1056 __PACKAGE__->load_components('Core');
1057 __PACKAGE__->table('user_address');
1058 __PACKAGE__->add_columns(qw/user address/);
1059 __PACKAGE__->set_primary_key(qw/user address/);
1060 __PACKAGE__->belongs_to('user' => 'My::User');
1061 __PACKAGE__->belongs_to('address' => 'My::Address');
1063 package My::Address;
1064 use base 'DBIx::Class';
1065 __PACKAGE__->load_components('Core');
1066 __PACKAGE__->table('address');
1067 __PACKAGE__->add_columns(qw/id street town area_code country/);
1068 __PACKAGE__->set_primary_key('id');
1069 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
1070 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
1072 $rs = $user->addresses(); # get all addresses for a user
1073 $rs = $address->users(); # get all users for an address
1075 =head2 Relationships across DB schemas
1077 Mapping relationships across L<DB schemas|DBIx::Class::Manual::Glossary/DB schema>
1078 is easy as long as the schemas themselves are all accessible via the same DBI
1079 connection. In most cases, this means that they are on the same database host
1080 as each other and your connecting database user has the proper permissions to them.
1082 To accomplish this one only needs to specify the DB schema name in the table
1083 declaration, like so...
1085 package MyDatabase::Main::Artist;
1086 use base qw/DBIx::Class/;
1087 __PACKAGE__->load_components(qw/PK::Auto Core/);
1089 __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
1091 __PACKAGE__->add_columns(qw/ artist_id name /);
1092 __PACKAGE__->set_primary_key('artist_id');
1093 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
1097 Whatever string you specify there will be used to build the "FROM" clause in SQL
1100 The big drawback to this is you now have DB schema names hardcoded in your
1101 class files. This becomes especially troublesome if you have multiple instances
1102 of your application to support a change lifecycle (e.g. DEV, TEST, PROD) and
1103 the DB schemas are named based on the environment (e.g. database1_dev).
1105 However, one can dynamically "map" to the proper DB schema by overriding the
1106 L<connection|DBIx::Class::Schama/connection> method in your Schema class and
1107 building a renaming facility, like so:
1109 package MyDatabase::Schema;
1112 extends 'DBIx::Class::Schema';
1114 around connection => sub {
1115 my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ );
1117 my $postfix = delete $attr->{schema_name_postfix};
1122 $self->append_db_name($postfix);
1126 sub append_db_name {
1127 my ( $self, $postfix ) = @_;
1131 { $_->name =~ /^\w+\./mx }
1133 { $self->source($_) }
1136 foreach my $source (@sources_with_db) {
1137 my $name = $source->name;
1138 $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx;
1140 $source->name($name);
1146 By overridding the L<connection|DBIx::Class::Schama/connection>
1147 method and extracting a custom option from the provided \%attr hashref one can
1148 then simply iterate over all the Schema's ResultSources, renaming them as
1151 To use this facility, simply add or modify the \%attr hashref that is passed to
1152 L<connection|DBIx::Class::Schama/connect>, as follows:
1155 = MyDatabase::Schema->connect(
1160 schema_name_postfix => '_dev'
1161 # ... Other options as desired ...
1164 Obviously, one could accomplish even more advanced mapping via a hash map or a
1169 As of version 0.04001, there is improved transaction support in
1170 L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
1171 example of the recommended way to use it:
1173 my $genus = $schema->resultset('Genus')->find(12);
1175 my $coderef2 = sub {
1180 my $coderef1 = sub {
1181 $genus->add_to_species({ name => 'troglodyte' });
1184 $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit
1185 return $genus->species;
1190 $rs = $schema->txn_do($coderef1);
1193 if ($@) { # Transaction failed
1194 die "the sky is falling!" #
1195 if ($@ =~ /Rollback failed/); # Rollback failed
1197 deal_with_failed_transaction();
1200 Nested transactions will work as expected. That is, only the outermost
1201 transaction will actually issue a commit to the $dbh, and a rollback
1202 at any level of any transaction will cause the entire nested
1203 transaction to fail. Support for savepoints and for true nested
1204 transactions (for databases that support them) will hopefully be added
1209 =head2 Creating Schemas From An Existing Database
1211 L<DBIx::Class::Schema::Loader> will connect to a database and create a
1212 L<DBIx::Class::Schema> and associated sources by examining the database.
1214 The recommend way of achieving this is to use the
1215 L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method:
1217 perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
1218 -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
1220 This will create a tree of files rooted at C<./lib/My/Schema/> containing
1221 source definitions for all the tables found in the C<foo> database.
1223 =head2 Creating DDL SQL
1225 The following functionality requires you to have L<SQL::Translator>
1226 (also known as "SQL Fairy") installed.
1228 To create a set of database-specific .sql files for the above schema:
1230 my $schema = My::Schema->connect($dsn);
1231 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
1236 By default this will create schema files in the current directory, for
1237 MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm.
1239 To create a new database using the schema:
1241 my $schema = My::Schema->connect($dsn);
1242 $schema->deploy({ add_drop_tables => 1});
1244 To import created .sql files using the mysql client:
1246 mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql
1248 To create C<ALTER TABLE> conversion scripts to update a database to a
1249 newer version of your schema at a later point, first set a new
1250 C<$VERSION> in your Schema file, then:
1252 my $schema = My::Schema->connect($dsn);
1253 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
1259 This will produce new database-specific .sql files for the new version
1260 of the schema, plus scripts to convert from version 0.1 to 0.2. This
1261 requires that the files for 0.1 as created above are available in the
1262 given directory to diff against.
1264 =head2 Select from dual
1266 Dummy tables are needed by some databases to allow calling functions
1267 or expressions that aren't based on table content, for examples of how
1268 this applies to various database types, see:
1269 L<http://troels.arvin.dk/db/rdbms/#other-dummy_table>.
1271 Note: If you're using Oracles dual table don't B<ever> do anything
1272 other than a select, if you CRUD on your dual table you *will* break
1275 Make a table class as you would for any other table
1277 package MyAppDB::Dual;
1280 use base 'DBIx::Class';
1281 __PACKAGE__->load_components("Core");
1282 __PACKAGE__->table("Dual");
1283 __PACKAGE__->add_columns(
1285 { data_type => "VARCHAR2", is_nullable => 0, size => 1 },
1288 Once you've loaded your table class select from it using C<select>
1289 and C<as> instead of C<columns>
1291 my $rs = $schema->resultset('Dual')->search(undef,
1292 { select => [ 'sydate' ],
1297 All you have to do now is be careful how you access your resultset, the below
1298 will not work because there is no column called 'now' in the Dual table class
1300 while (my $dual = $rs->next) {
1301 print $dual->now."\n";
1303 # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23.
1305 You could of course use 'dummy' in C<as> instead of 'now', or C<add_columns> to
1306 your Dual class for whatever you wanted to select from dual, but that's just
1307 silly, instead use C<get_column>
1309 while (my $dual = $rs->next) {
1310 print $dual->get_column('now')."\n";
1315 my $cursor = $rs->cursor;
1316 while (my @vals = $cursor->next) {
1317 print $vals[0]."\n";
1320 In case you're going to use this "trick" together with L<DBIx::Class::Schema/deploy> or
1321 L<DBIx::Class::Schema/create_ddl_dir> a table called "dual" will be created in your
1322 current schema. This would overlap "sys.dual" and you could not fetch "sysdate" or
1323 "sequence.nextval" anymore from dual. To avoid this problem, just tell
1324 L<SQL::Translator> to not create table dual:
1327 add_drop_table => 1,
1328 parser_args => { sources => [ grep $_ ne 'Dual', schema->sources ] },
1330 $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args );
1332 Or use L<DBIx::Class::ResultClass::HashRefInflator>
1334 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
1335 while ( my $dual = $rs->next ) {
1336 print $dual->{now}."\n";
1339 Here are some example C<select> conditions to illustrate the different syntax
1340 you could use for doing stuff like
1341 C<oracles.heavily(nested(functions_can('take', 'lots'), OF), 'args')>
1343 # get a sequence value
1344 select => [ 'A_SEQ.nextval' ],
1346 # get create table sql
1347 select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ],
1349 # get a random num between 0 and 100
1350 select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ],
1353 select => [ { 'extract' => [ \'year from sysdate' ] } ],
1356 select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}],
1358 # which day of the week were you born on?
1359 select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}],
1361 # select 16 rows from dual
1362 select => [ "'hello'" ],
1364 group_by => [ 'cube( 1, 2, 3, 4 )' ],
1368 =head2 Adding Indexes And Functions To Your SQL
1370 Often you will want indexes on columns on your table to speed up searching. To
1371 do this, create a method called C<sqlt_deploy_hook> in the relevant source
1372 class (refer to the advanced
1373 L<callback system|DBIx::Class::ResultSource/sqlt_deploy_callback> if you wish
1374 to share a hook between multiple sources):
1376 package My::Schema::Result::Artist;
1378 __PACKAGE__->table('artist');
1379 __PACKAGE__->add_columns(id => { ... }, name => { ... })
1381 sub sqlt_deploy_hook {
1382 my ($self, $sqlt_table) = @_;
1384 $sqlt_table->add_index(name => 'idx_name', fields => ['name']);
1389 Sometimes you might want to change the index depending on the type of the
1390 database for which SQL is being generated:
1392 my ($db_type = $sqlt_table->schema->translator->producer_type)
1393 =~ s/^SQL::Translator::Producer:://;
1395 You can also add hooks to the schema level to stop certain tables being
1402 sub sqlt_deploy_hook {
1403 my ($self, $sqlt_schema) = @_;
1405 $sqlt_schema->drop_table('table_name');
1408 You could also add views, procedures or triggers to the output using
1409 L<SQL::Translator::Schema/add_view>,
1410 L<SQL::Translator::Schema/add_procedure> or
1411 L<SQL::Translator::Schema/add_trigger>.
1414 =head2 Schema versioning
1416 The following example shows simplistically how you might use DBIx::Class to
1417 deploy versioned schemas to your customers. The basic process is as follows:
1423 Create a DBIx::Class schema
1435 Modify schema to change functionality
1439 Deploy update to customers
1443 B<Create a DBIx::Class schema>
1445 This can either be done manually, or generated from an existing database as
1446 described under L</Creating Schemas From An Existing Database>
1450 Call L<DBIx::Class::Schema/create_ddl_dir> as above under L</Creating DDL SQL>.
1452 B<Deploy to customers>
1454 There are several ways you could deploy your schema. These are probably
1455 beyond the scope of this recipe, but might include:
1461 Require customer to apply manually using their RDBMS.
1465 Package along with your app, making database dump/schema update/tests
1466 all part of your install.
1470 B<Modify the schema to change functionality>
1472 As your application evolves, it may be necessary to modify your schema
1473 to change functionality. Once the changes are made to your schema in
1474 DBIx::Class, export the modified schema and the conversion scripts as
1475 in L</Creating DDL SQL>.
1477 B<Deploy update to customers>
1479 Add the L<DBIx::Class::Schema::Versioned> schema component to your
1480 Schema class. This will add a new table to your database called
1481 C<dbix_class_schema_vesion> which will keep track of which version is installed
1482 and warn if the user trys to run a newer schema version than the
1483 database thinks it has.
1485 Alternatively, you can send the conversion sql scripts to your
1488 =head2 Setting quoting for the generated SQL.
1490 If the database contains column names with spaces and/or reserved words, they
1491 need to be quoted in the SQL queries. This is done using:
1493 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
1494 __PACKAGE__->storage->sql_maker->name_sep('.');
1496 The first sets the quote characters. Either a pair of matching
1497 brackets, or a C<"> or C<'>:
1499 __PACKAGE__->storage->sql_maker->quote_char('"');
1501 Check the documentation of your database for the correct quote
1502 characters to use. C<name_sep> needs to be set to allow the SQL
1503 generator to put the quotes the correct place.
1505 In most cases you should set these as part of the arguments passed to
1506 L<DBIx::Class::Schema/connect>:
1508 my $schema = My::Schema->connect(
1518 =head2 Setting limit dialect for SQL::Abstract::Limit
1520 In some cases, SQL::Abstract::Limit cannot determine the dialect of
1521 the remote SQL server by looking at the database handle. This is a
1522 common problem when using the DBD::JDBC, since the DBD-driver only
1523 know that in has a Java-driver available, not which JDBC driver the
1524 Java component has loaded. This specifically sets the limit_dialect
1525 to Microsoft SQL-server (See more names in SQL::Abstract::Limit
1528 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
1530 The JDBC bridge is one way of getting access to a MSSQL server from a platform
1531 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
1533 The limit dialect can also be set at connect time by specifying a
1534 C<limit_dialect> key in the final hash as shown above.
1536 =head2 Working with PostgreSQL array types
1538 You can also assign values to PostgreSQL array columns by passing array
1539 references in the C<\%columns> (C<\%vals>) hashref of the
1540 L<DBIx::Class::ResultSet/create> and L<DBIx::Class::Row/update> family of
1543 $resultset->create({
1544 numbers => [1, 2, 3]
1549 numbers => [1, 2, 3]
1556 In conditions (eg. C<\%cond> in the L<DBIx::Class::ResultSet/search> family of
1557 methods) you cannot directly use array references (since this is interpreted as
1558 a list of values to be C<OR>ed), but you can use the following syntax to force
1559 passing them as bind values:
1563 numbers => \[ '= ?', [numbers => [1, 2, 3]] ]
1567 See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
1568 placeholders and bind values (subqueries)> for more explanation. Note that
1569 L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
1570 the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
1571 arrayrefs together with the column name, like this: C<< [column_name => value]
1574 =head1 BOOTSTRAPPING/MIGRATING
1576 =head2 Easy migration from class-based to schema-based setup
1578 You want to start using the schema-based approach to L<DBIx::Class>
1579 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
1580 of existing classes that you don't want to move by hand. Try this nifty script
1584 use SQL::Translator;
1586 my $schema = MyDB->schema_instance;
1588 my $translator = SQL::Translator->new(
1589 debug => $debug || 0,
1590 trace => $trace || 0,
1591 no_comments => $no_comments || 0,
1592 show_warnings => $show_warnings || 0,
1593 add_drop_table => $add_drop_table || 0,
1594 validate => $validate || 0,
1596 'DBIx::Schema' => $schema,
1599 'prefix' => 'My::Schema',
1603 $translator->parser('SQL::Translator::Parser::DBIx::Class');
1604 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
1606 my $output = $translator->translate(@args) or die
1607 "Error: " . $translator->error;
1611 You could use L<Module::Find> to search for all subclasses in the MyDB::*
1612 namespace, which is currently left as an exercise for the reader.
1614 =head1 OVERLOADING METHODS
1616 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
1617 method calls, useful for things like default values and triggers. You have to
1618 use calls to C<next::method> to overload methods. More information on using
1619 L<Class::C3> with L<DBIx::Class> can be found in
1620 L<DBIx::Class::Manual::Component>.
1622 =head2 Setting default values for a row
1624 It's as simple as overriding the C<new> method. Note the use of
1628 my ( $class, $attrs ) = @_;
1630 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
1632 my $new = $class->next::method($attrs);
1637 For more information about C<next::method>, look in the L<Class::C3>
1638 documentation. See also L<DBIx::Class::Manual::Component> for more
1639 ways to write your own base classes to do this.
1641 People looking for ways to do "triggers" with DBIx::Class are probably
1642 just looking for this.
1644 =head2 Changing one field whenever another changes
1646 For example, say that you have three columns, C<id>, C<number>, and
1647 C<squared>. You would like to make changes to C<number> and have
1648 C<squared> be automagically set to the value of C<number> squared.
1649 You can accomplish this by overriding C<store_column>:
1652 my ( $self, $name, $value ) = @_;
1653 if ($name eq 'number') {
1654 $self->squared($value * $value);
1656 $self->next::method($name, $value);
1659 Note that the hard work is done by the call to C<next::method>, which
1660 redispatches your call to store_column in the superclass(es).
1662 =head2 Automatically creating related objects
1664 You might have a class C<Artist> which has many C<CD>s. Further, you
1665 want to create a C<CD> object every time you insert an C<Artist> object.
1666 You can accomplish this by overriding C<insert> on your objects:
1669 my ( $self, @args ) = @_;
1670 $self->next::method(@args);
1671 $self->cds->new({})->fill_from_artist($self)->insert;
1675 where C<fill_from_artist> is a method you specify in C<CD> which sets
1676 values in C<CD> based on the data in the C<Artist> object you pass in.
1678 =head2 Wrapping/overloading a column accessor
1682 Say you have a table "Camera" and want to associate a description
1683 with each camera. For most cameras, you'll be able to generate the description from
1684 the other columns. However, in a few special cases you may want to associate a
1685 custom description with a camera.
1689 In your database schema, define a description field in the "Camera" table that
1690 can contain text and null values.
1692 In DBIC, we'll overload the column accessor to provide a sane default if no
1693 custom description is defined. The accessor will either return or generate the
1694 description, depending on whether the field is null or not.
1696 First, in your "Camera" schema class, define the description field as follows:
1698 __PACKAGE__->add_columns(description => { accessor => '_description' });
1700 Next, we'll define the accessor-wrapper subroutine:
1705 # If there is an update to the column, we'll let the original accessor
1707 return $self->_description(@_) if @_;
1709 # Fetch the column value.
1710 my $description = $self->_description;
1712 # If there's something in the description field, then just return that.
1713 return $description if defined $description && length $descripton;
1715 # Otherwise, generate a description.
1716 return $self->generate_description;
1719 =head1 DEBUGGING AND PROFILING
1721 =head2 DBIx::Class objects with Data::Dumper
1723 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
1724 be hard to find the pertinent data in all the data it can generate.
1725 Specifically, if one naively tries to use it like so,
1729 my $cd = $schema->resultset('CD')->find(1);
1732 several pages worth of data from the CD object's schema and result source will
1733 be dumped to the screen. Since usually one is only interested in a few column
1734 values of the object, this is not very helpful.
1736 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
1737 it. Simply define a hook that L<Data::Dumper> will call on the object before
1738 dumping it. For example,
1745 result_source => undef,
1753 local $Data::Dumper::Freezer = '_dumper_hook';
1755 my $cd = $schema->resultset('CD')->find(1);
1757 # dumps $cd without its ResultSource
1759 If the structure of your schema is such that there is a common base class for
1760 all your table classes, simply put a method similar to C<_dumper_hook> in the
1761 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
1762 will automagically clean up your data before printing it. See
1763 L<Data::Dumper/EXAMPLES> for more information.
1767 When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL
1768 executed as well as notifications of query completion and transaction
1769 begin/commit. If you'd like to profile the SQL you can subclass the
1770 L<DBIx::Class::Storage::Statistics> class and write your own profiling
1773 package My::Profiler;
1776 use base 'DBIx::Class::Storage::Statistics';
1778 use Time::HiRes qw(time);
1787 $self->print("Executing $sql: ".join(', ', @params)."\n");
1796 my $elapsed = sprintf("%0.4f", time() - $start);
1797 $self->print("Execution took $elapsed seconds.\n");
1803 You can then install that class as the debugging object:
1805 __PACKAGE__->storage->debugobj(new My::Profiler());
1806 __PACKAGE__->storage->debug(1);
1808 A more complicated example might involve storing each execution of SQL in an
1816 my $elapsed = time() - $start;
1817 push(@{ $calls{$sql} }, {
1823 You could then create average, high and low execution times for an SQL
1824 statement and dig down to see if certain parameters cause aberrant behavior.
1825 You might want to check out L<DBIx::Class::QueryLog> as well.
1827 =head1 STARTUP SPEED
1829 L<DBIx::Class|DBIx::Class> programs can have a significant startup delay
1830 as the ORM loads all the relevant classes. This section examines
1831 techniques for reducing the startup delay.
1833 These tips are are listed in order of decreasing effectiveness - so the
1834 first tip, if applicable, should have the greatest effect on your
1837 =head2 Statically Define Your Schema
1840 L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to build the
1841 classes dynamically based on the database schema then there will be a
1842 significant startup delay.
1844 For production use a statically defined schema (which can be generated
1845 using L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to dump
1846 the database schema once - see
1847 L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> and
1848 L<dump_directory|DBIx::Class::Schema::Loader/dump_directory> for more
1849 details on creating static schemas from a database).
1851 =head2 Move Common Startup into a Base Class
1853 Typically L<DBIx::Class> result classes start off with
1855 use base qw/DBIx::Class/;
1856 __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
1858 If this preamble is moved into a common base class:-
1862 use base qw/DBIx::Class/;
1863 __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
1866 and each result class then uses this as a base:-
1868 use base qw/MyDBICbase/;
1870 then the load_components is only performed once, which can result in a
1871 considerable startup speedup for schemas with many classes.
1873 =head2 Explicitly List Schema Result Classes
1875 The schema class will normally contain
1877 __PACKAGE__->load_classes();
1879 to load the result classes. This will use L<Module::Find|Module::Find>
1880 to find and load the appropriate modules. Explicitly defining the
1881 classes you wish to load will remove the overhead of
1882 L<Module::Find|Module::Find> and the related directory operations:
1884 __PACKAGE__->load_classes(qw/ CD Artist Track /);
1886 If you are instead using the L<load_namespaces|DBIx::Class::Schema/load_namespaces>
1887 syntax to load the appropriate classes there is not a direct alternative
1888 avoiding L<Module::Find|Module::Find>.
1892 =head2 Cached statements
1894 L<DBIx::Class> normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>.
1895 This is normally a good idea, but if too many statements are cached, the database may use too much
1896 memory and may eventually run out and fail entirely. If you suspect this may be the case, you may want
1897 to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash:
1899 # print all currently cached prepared statements
1900 print for keys %{$schema->storage->dbh->{CachedKids}};
1901 # get a count of currently cached prepared statements
1902 my $count = scalar keys %{$schema->storage->dbh->{CachedKids}};
1904 If it's appropriate, you can simply clear these statements, automatically deallocating them in the
1907 my $kids = $schema->storage->dbh->{CachedKids};
1908 delete @{$kids}{keys %$kids} if scalar keys %$kids > 100;
1910 But what you probably want is to expire unused statements and not those that are used frequently.
1911 You can accomplish this with L<Tie::Cache> or L<Tie::Cache::LRU>:
1915 my $schema = DB::Main->connect($dbi_dsn, $user, $pass, {
1916 on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 },