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 The C<page> attribute does not have to be specified in your search:
24 my $rs = $schema->resultset('Artist')->search(
31 return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
33 In either of the above cases, you can get a L<Data::Page> object for the
34 resultset (suitable for use in e.g. a template) using the C<pager> method:
38 =head2 Complex WHERE clauses
40 Sometimes you need to formulate a query using specific operators:
42 my @albums = $schema->resultset('Album')->search({
43 artist => { 'like', '%Lamb%' },
44 title => { 'like', '%Fear of Fours%' },
47 This results in something like the following C<WHERE> clause:
49 WHERE artist LIKE '%Lamb%' AND title LIKE '%Fear of Fours%'
51 Other queries might require slightly more complex logic:
53 my @albums = $schema->resultset('Album')->search({
56 artist => { 'like', '%Smashing Pumpkins%' },
57 title => 'Siamese Dream',
59 artist => 'Starchildren',
63 This results in the following C<WHERE> clause:
65 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
66 OR artist = 'Starchildren'
68 For more information on generating complex queries, see
69 L<SQL::Abstract/WHERE CLAUSES>.
71 =head2 Arbitrary SQL through a custom ResultSource
73 Sometimes you have to run arbitrary SQL because your query is too complex
74 (e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.) or has to
75 be optimized for your database in a special way, but you still want to
76 get the results as a L<DBIx::Class::ResultSet>.
77 The recommended way to accomplish this is by defining a separate ResultSource
78 for your query. You can then inject complete SQL statements using a scalar
79 reference (this is a feature of L<SQL::Abstract>).
81 Say you want to run a complex custom query on your user data, here's what
82 you have to add to your User class:
84 package My::Schema::User;
86 use base qw/DBIx::Class/;
88 # ->load_components, ->table, ->add_columns, etc.
90 # Make a new ResultSource based on the User class
91 my $source = __PACKAGE__->result_source_instance();
92 my $new_source = $source->new( $source );
93 $new_source->source_name( 'UserFriendsComplex' );
95 # Hand in your query as a scalar reference
96 # It will be added as a sub-select after FROM,
97 # so pay attention to the surrounding brackets!
98 $new_source->name( \<<SQL );
99 ( SELECT u.* FROM user u
100 INNER JOIN user_friends f ON u.id = f.user_id
101 WHERE f.friend_user_id = ?
103 SELECT u.* FROM user u
104 INNER JOIN user_friends f ON u.id = f.friend_user_id
105 WHERE f.user_id = ? )
108 # Finally, register your new ResultSource with your Schema
109 My::Schema->register_source( 'UserFriendsComplex' => $new_source );
111 Next, you can execute your complex query using bind parameters like this:
113 my $friends = [ $schema->resultset( 'UserFriendsComplex' )->search( {},
115 bind => [ 12345, 12345 ]
119 ... and you'll get back a perfect L<DBIx::Class::ResultSet>.
121 =head2 Using specific columns
123 When you only want specific columns from a table, you can use
124 C<columns> to specify which ones you need. This is useful to avoid
125 loading columns with large amounts of data that you aren't about to
128 my $rs = $schema->resultset('Artist')->search(
131 columns => [qw/ name /]
136 # SELECT artist.name FROM artist
138 This is a shortcut for C<select> and C<as>, see below. C<columns>
139 cannot be used together with C<select> and C<as>.
141 =head2 Using database functions or stored procedures
143 The combination of C<select> and C<as> can be used to return the result of a
144 database function or stored procedure as a column value. You use C<select> to
145 specify the source for your column value (e.g. a column name, function, or
146 stored procedure name). You then use C<as> to set the column name you will use
147 to access the returned value:
149 my $rs = $schema->resultset('Artist')->search(
152 select => [ 'name', { LENGTH => 'name' } ],
153 as => [qw/ name name_length /],
158 # SELECT name name, LENGTH( name )
161 Note that the C< as > attribute has absolutely nothing to with the sql
162 syntax C< SELECT foo AS bar > (see the documentation in
163 L<DBIx::Class::ResultSet/ATTRIBUTES>). If your alias exists as a
164 column in your base class (i.e. it was added with C<add_columns>), you
165 just access it as normal. Our C<Artist> class has a C<name> column, so
166 we just use the C<name> accessor:
168 my $artist = $rs->first();
169 my $name = $artist->name();
171 If on the other hand the alias does not correspond to an existing column, you
172 have to fetch the value using the C<get_column> accessor:
174 my $name_length = $artist->get_column('name_length');
176 If you don't like using C<get_column>, you can always create an accessor for
177 any of your aliases using either of these:
179 # Define accessor manually:
180 sub name_length { shift->get_column('name_length'); }
182 # Or use DBIx::Class::AccessorGroup:
183 __PACKAGE__->mk_group_accessors('column' => 'name_length');
185 =head2 SELECT DISTINCT with multiple columns
187 my $rs = $schema->resultset('Foo')->search(
191 { distinct => [ $source->columns ] }
193 as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-)
197 my $count = $rs->next->get_column('count');
199 =head2 SELECT COUNT(DISTINCT colname)
201 my $rs = $schema->resultset('Foo')->search(
205 { count => { distinct => 'colname' } }
211 =head2 Grouping results
213 L<DBIx::Class> supports C<GROUP BY> as follows:
215 my $rs = $schema->resultset('Artist')->search(
219 select => [ 'name', { count => 'cds.cdid' } ],
220 as => [qw/ name cd_count /],
221 group_by => [qw/ name /]
226 # SELECT name, COUNT( cds.cdid ) FROM artist me
227 # LEFT JOIN cd cds ON ( cds.artist = me.artistid )
230 Please see L<DBIx::Class::ResultSet/ATTRIBUTES> documentation if you
231 are in any way unsure about the use of the attributes above (C< join
232 >, C< select >, C< as > and C< group_by >).
234 =head2 Predefined searches
236 You can write your own L<DBIx::Class::ResultSet> class by inheriting from it
237 and define often used searches as methods:
239 package My::DBIC::ResultSet::CD;
242 use base 'DBIx::Class::ResultSet';
244 sub search_cds_ordered {
247 return $self->search(
249 { order_by => 'name DESC' },
255 To use your resultset, first tell DBIx::Class to create an instance of it
256 for you, in your My::DBIC::Schema::CD class:
258 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
260 Then call your new method in your code:
262 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
264 =head2 Using SQL functions on the left hand side of a comparison
266 Using SQL functions on the left hand side of a comparison is generally
267 not a good idea since it requires a scan of the entire table. However,
268 it can be accomplished with C<DBIx::Class> when necessary.
270 If you do not have quoting on, simply include the function in your search
271 specification as you would any column:
273 $rs->search({ 'YEAR(date_of_birth)' => 1979 });
275 With quoting on, or for a more portable solution, use the C<where>
278 $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' });
282 (When the bind args ordering bug is fixed, this technique will be better
283 and can replace the one above.)
285 With quoting on, or for a more portable solution, use the C<where> and
289 where => \'YEAR(date_of_birth) = ?',
295 =head1 JOINS AND PREFETCHING
297 =head2 Using joins and prefetch
299 You can use the C<join> attribute to allow searching on, or sorting your
300 results by, one or more columns in a related table. To return all CDs matching
301 a particular artist name:
303 my $rs = $schema->resultset('CD')->search(
305 'artist.name' => 'Bob Marley'
308 join => [qw/artist/], # join the artist table
313 # SELECT cd.* FROM cd
314 # JOIN artist ON cd.artist = artist.id
315 # WHERE artist.name = 'Bob Marley'
317 If required, you can now sort on any column in the related tables by including
318 it in your C<order_by> attribute:
320 my $rs = $schema->resultset('CD')->search(
322 'artist.name' => 'Bob Marley'
325 join => [qw/ artist /],
326 order_by => [qw/ artist.name /]
331 # SELECT cd.* FROM cd
332 # JOIN artist ON cd.artist = artist.id
333 # WHERE artist.name = 'Bob Marley'
334 # ORDER BY artist.name
336 Note that the C<join> attribute should only be used when you need to search or
337 sort using columns in a related table. Joining related tables when you only
338 need columns from the main table will make performance worse!
340 Now let's say you want to display a list of CDs, each with the name of the
341 artist. The following will work fine:
343 while (my $cd = $rs->next) {
344 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
347 There is a problem however. We have searched both the C<cd> and C<artist> tables
348 in our main query, but we have only returned data from the C<cd> table. To get
349 the artist name for any of the CD objects returned, L<DBIx::Class> will go back
352 SELECT artist.* FROM artist WHERE artist.id = ?
354 A statement like the one above will run for each and every CD returned by our
355 main query. Five CDs, five extra queries. A hundred CDs, one hundred extra
358 Thankfully, L<DBIx::Class> has a C<prefetch> attribute to solve this problem.
359 This allows you to fetch results from related tables in advance:
361 my $rs = $schema->resultset('CD')->search(
363 'artist.name' => 'Bob Marley'
366 join => [qw/ artist /],
367 order_by => [qw/ artist.name /],
368 prefetch => [qw/ artist /] # return artist data too!
372 # Equivalent SQL (note SELECT from both "cd" and "artist"):
373 # SELECT cd.*, artist.* FROM cd
374 # JOIN artist ON cd.artist = artist.id
375 # WHERE artist.name = 'Bob Marley'
376 # ORDER BY artist.name
378 The code to print the CD list remains the same:
380 while (my $cd = $rs->next) {
381 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
384 L<DBIx::Class> has now prefetched all matching data from the C<artist> table,
385 so no additional SQL statements are executed. You now have a much more
388 Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
389 C<has_many> relationships.
391 Also note that C<prefetch> should only be used when you know you will
392 definitely use data from a related table. Pre-fetching related tables when you
393 only need columns from the main table will make performance worse!
395 =head2 Multi-step joins
397 Sometimes you want to join more than one relationship deep. In this example,
398 we want to find all C<Artist> objects who have C<CD>s whose C<LinerNotes>
399 contain a specific string:
401 # Relationships defined elsewhere:
402 # Artist->has_many('cds' => 'CD', 'artist');
403 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
405 my $rs = $schema->resultset('Artist')->search(
407 'liner_notes.notes' => { 'like', '%some text%' },
411 'cds' => 'liner_notes'
417 # SELECT artist.* FROM artist
418 # JOIN ( cd ON artist.id = cd.artist )
419 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
420 # WHERE liner_notes.notes LIKE '%some text%'
422 Joins can be nested to an arbitrary level. So if we decide later that we
423 want to reduce the number of Artists returned based on who wrote the liner
426 # Relationship defined elsewhere:
427 # LinerNotes->belongs_to('author' => 'Person');
429 my $rs = $schema->resultset('Artist')->search(
431 'liner_notes.notes' => { 'like', '%some text%' },
432 'author.name' => 'A. Writer'
437 'liner_notes' => 'author'
444 # SELECT artist.* FROM artist
445 # JOIN ( cd ON artist.id = cd.artist )
446 # JOIN ( liner_notes ON cd.id = liner_notes.cd )
447 # JOIN ( author ON author.id = liner_notes.author )
448 # WHERE liner_notes.notes LIKE '%some text%'
449 # AND author.name = 'A. Writer'
451 =head2 Multi-step prefetch
453 From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
454 deep using the same syntax as a multi-step join:
456 my $rs = $schema->resultset('Tag')->search(
466 # SELECT tag.*, cd.*, artist.* FROM tag
467 # JOIN cd ON tag.cd = cd.cdid
468 # JOIN artist ON cd.artist = artist.artistid
470 Now accessing our C<cd> and C<artist> relationships does not need additional
473 my $tag = $rs->first;
474 print $tag->cd->artist->name;
476 =head1 ROW-LEVEL OPERATIONS
478 =head2 Retrieving a row object's Schema
480 It is possible to get a Schema object from a row object like so:
482 my $schema = $cd->result_source->schema;
483 # use the schema as normal:
484 my $artist_rs = $schema->resultset('Artist');
486 This can be useful when you don't want to pass around a Schema object to every
489 =head2 Getting the value of the primary key for the last database insert
491 AKA getting last_insert_id
493 If you are using PK::Auto (which is a core component as of 0.07), this is
496 my $foo = $rs->create(\%blah);
498 my $id = $foo->id; # foo->my_primary_key_field will also work.
500 If you are not using autoincrementing primary keys, this will probably
501 not work, but then you already know the value of the last primary key anyway.
503 =head2 Stringification
505 Employ the standard stringification technique by using the C<overload>
508 To make an object stringify itself as a single column, use something
509 like this (replace C<foo> with the column/method of your choice):
511 use overload '""' => sub { shift->name}, fallback => 1;
513 For more complex stringification, you can use an anonymous subroutine:
515 use overload '""' => sub { $_[0]->name . ", " .
516 $_[0]->address }, fallback => 1;
518 =head3 Stringification Example
520 Suppose we have two tables: C<Product> and C<Category>. The table
523 Product(id, Description, category)
524 Category(id, Description)
526 C<category> is a foreign key into the Category table.
528 If you have a Product object C<$obj> and write something like
532 things will not work as expected.
534 To obtain, for example, the category description, you should add this
535 method to the class defining the Category table:
537 use overload "" => sub {
540 return $self->Description;
543 =head2 Want to know if find_or_create found or created a row?
545 Just use C<find_or_new> instead, then check C<in_storage>:
547 my $obj = $rs->find_or_new({ blah => 'blarg' });
548 unless ($obj->in_storage) {
550 # do whatever else you wanted if it was a new row
553 =head2 Dynamic Sub-classing DBIx::Class proxy classes
555 AKA multi-class object inflation from one table
557 L<DBIx::Class> classes are proxy classes, therefore some different
558 techniques need to be employed for more than basic subclassing. In
559 this example we have a single user table that carries a boolean bit
560 for admin. We would like like to give the admin users
561 objects(L<DBIx::Class::Row>) the same methods as a regular user but
562 also special admin only methods. It doesn't make sense to create two
563 seperate proxy-class files for this. We would be copying all the user
564 methods into the Admin class. There is a cleaner way to accomplish
567 Overriding the C<inflate_result> method within the User proxy-class
568 gives us the effect we want. This method is called by
569 L<DBIx::Class::ResultSet> when inflating a result from storage. So we
570 grab the object being returned, inspect the values we are looking for,
571 bless it if it's an admin object, and then return it. See the example
578 use base qw/DBIx::Class::Schema/;
580 __PACKAGE__->load_classes(qw/User/);
583 B<Proxy-Class definitions>
585 package DB::Schema::User;
589 use base qw/DBIx::Class/;
591 ### Defined what our admin class is for ensure_class_loaded
592 my $admin_class = __PACKAGE__ . '::Admin';
594 __PACKAGE__->load_components(qw/Core/);
596 __PACKAGE__->table('users');
598 __PACKAGE__->add_columns(qw/user_id email password
599 firstname lastname active
602 __PACKAGE__->set_primary_key('user_id');
606 my $ret = $self->next::method(@_);
607 if( $ret->admin ) {### If this is an admin rebless for extra functions
608 $self->ensure_class_loaded( $admin_class );
609 bless $ret, $admin_class;
615 print "I am a regular user.\n";
620 package DB::Schema::User::Admin;
624 use base qw/DB::Schema::User/;
628 print "I am an admin.\n";
634 print "I am doing admin stuff\n";
644 my $user_data = { email => 'someguy@place.com',
648 my $admin_data = { email => 'someadmin@adminplace.com',
652 my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
654 $schema->resultset('User')->create( $user_data );
655 $schema->resultset('User')->create( $admin_data );
657 ### Now we search for them
658 my $user = $schema->resultset('User')->single( $user_data );
659 my $admin = $schema->resultset('User')->single( $admin_data );
661 print ref $user, "\n";
662 print ref $admin, "\n";
664 print $user->password , "\n"; # pass1
665 print $admin->password , "\n";# pass2; inherited from User
666 print $user->hello , "\n";# I am a regular user.
667 print $admin->hello, "\n";# I am an admin.
669 ### The statement below will NOT print
670 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
671 ### The statement below will print
672 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
674 =head2 Skip object creation for faster results
676 DBIx::Class is not built for speed, it's built for convenience and
677 ease of use, but sometimes you just need to get the data, and skip the
680 To do this simply use L<DBIx::Class::ResultClass::HashRefInflator>.
682 my $rs = $schema->resultset('CD');
684 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
686 my $hash_ref = $rs->find(1);
690 =head2 Get raw data for blindingly fast results
692 If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
693 above is not fast enough for you, you can use a DBIx::Class to return values
694 exactly as they come out of the data base with none of the convenience methods
697 This is used like so:-
699 my $cursor = $rs->cursor
700 while (my @vals = $cursor->next) {
701 # use $val[0..n] here
704 You will need to map the array offsets to particular columns (you can
705 use the I<select> attribute of C<search()> to force ordering).
707 =head1 RESULTSET OPERATIONS
709 =head2 Getting Schema from a ResultSet
711 To get the schema object from a result set, do the following:
713 $rs->result_source->schema
715 =head2 Getting Columns Of Data
719 If you want to find the sum of a particular column there are several
720 ways, the obvious one is to use search:
722 my $rs = $schema->resultset('Items')->search(
725 select => [ { sum => 'Cost' } ],
726 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL
729 my $tc = $rs->first->get_column('total_cost');
731 Or, you can use the L<DBIx::Class::ResultSetColumn>, which gets
732 returned when you ask the C<ResultSet> for a column using
735 my $cost = $schema->resultset('Items')->get_column('Cost');
738 With this you can also do:
740 my $minvalue = $cost->min;
741 my $maxvalue = $cost->max;
743 Or just iterate through the values of this column only:
745 while ( my $c = $cost->next ) {
749 foreach my $c ($cost->all) {
753 C<ResultSetColumn> only has a limited number of built-in functions, if
754 you need one that it doesn't have, then you can use the C<func> method
757 my $avg = $cost->func('AVERAGE');
759 This will cause the following SQL statement to be run:
761 SELECT AVERAGE(Cost) FROM Items me
763 Which will of course only work if your database supports this function.
764 See L<DBIx::Class::ResultSetColumn> for more documentation.
766 =head2 Creating a result set from a set of rows
768 Sometimes you have a (set of) row objects that you want to put into a
769 resultset without the need to hit the DB again. You can do that by using the
770 L<set_cache|DBIx::Class::Resultset/set_cache> method:
773 while (my $group = $groups->next) {
774 if ($group->can_upload($self)) {
775 push @uploadable_groups, $group;
778 my $new_rs = $self->result_source->resultset;
779 $new_rs->set_cache(\@uploadable_groups);
783 =head1 USING RELATIONSHIPS
785 =head2 Create a new row in a related table
787 my $author = $book->create_related('author', { name => 'Fred'});
789 =head2 Search in a related table
791 Only searches for books named 'Titanic' by the author in $author.
793 my $books_rs = $author->search_related('books', { name => 'Titanic' });
795 =head2 Delete data in a related table
797 Deletes only the book named Titanic by the author in $author.
799 $author->delete_related('books', { name => 'Titanic' });
801 =head2 Ordering a relationship result set
803 If you always want a relation to be ordered, you can specify this when you
804 create the relationship.
806 To order C<< $book->pages >> by descending page_number, create the relation
809 __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
811 =head2 Many-to-many relationships
813 This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
816 use base 'DBIx::Class';
817 __PACKAGE__->load_components('Core');
818 __PACKAGE__->table('user');
819 __PACKAGE__->add_columns(qw/id name/);
820 __PACKAGE__->set_primary_key('id');
821 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
822 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
824 package My::UserAddress;
825 use base 'DBIx::Class';
826 __PACKAGE__->load_components('Core');
827 __PACKAGE__->table('user_address');
828 __PACKAGE__->add_columns(qw/user address/);
829 __PACKAGE__->set_primary_key(qw/user address/);
830 __PACKAGE__->belongs_to('user' => 'My::User');
831 __PACKAGE__->belongs_to('address' => 'My::Address');
834 use base 'DBIx::Class';
835 __PACKAGE__->load_components('Core');
836 __PACKAGE__->table('address');
837 __PACKAGE__->add_columns(qw/id street town area_code country/);
838 __PACKAGE__->set_primary_key('id');
839 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
840 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
842 $rs = $user->addresses(); # get all addresses for a user
843 $rs = $address->users(); # get all users for an address
847 As of version 0.04001, there is improved transaction support in
848 L<DBIx::Class::Storage> and L<DBIx::Class::Schema>. Here is an
849 example of the recommended way to use it:
851 my $genus = $schema->resultset('Genus')->find(12);
859 $genus->add_to_species({ name => 'troglodyte' });
862 $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit
863 return $genus->species;
868 $rs = $schema->txn_do($coderef1);
871 if ($@) { # Transaction failed
872 die "the sky is falling!" #
873 if ($@ =~ /Rollback failed/); # Rollback failed
875 deal_with_failed_transaction();
878 Nested transactions will work as expected. That is, only the outermost
879 transaction will actually issue a commit to the $dbh, and a rollback
880 at any level of any transaction will cause the entire nested
881 transaction to fail. Support for savepoints and for true nested
882 transactions (for databases that support them) will hopefully be added
887 =head2 Creating Schemas From An Existing Database
889 L<DBIx::Class::Schema::Loader> will connect to a database and create a
890 L<DBIx::Class::Schema> and associated sources by examining the database.
892 The recommend way of achieving this is to use the
893 L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method:
895 perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
896 -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
898 This will create a tree of files rooted at C<./lib/My/Schema/> containing
899 source definitions for all the tables found in the C<foo> database.
901 =head2 Creating DDL SQL
903 The following functionality requires you to have L<SQL::Translator>
904 (also known as "SQL Fairy") installed.
906 To create a set of database-specific .sql files for the above schema:
908 my $schema = My::Schema->connect($dsn);
909 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
914 By default this will create schema files in the current directory, for
915 MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm.
917 To create a new database using the schema:
919 my $schema = My::Schema->connect($dsn);
920 $schema->deploy({ add_drop_tables => 1});
922 To import created .sql files using the mysql client:
924 mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql
926 To create C<ALTER TABLE> conversion scripts to update a database to a
927 newer version of your schema at a later point, first set a new
928 C<$VERSION> in your Schema file, then:
930 my $schema = My::Schema->connect($dsn);
931 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
937 This will produce new database-specific .sql files for the new version
938 of the schema, plus scripts to convert from version 0.1 to 0.2. This
939 requires that the files for 0.1 as created above are available in the
940 given directory to diff against.
942 =head2 Select from dual
944 Dummy tables are needed by some databases to allow calling functions
945 or expressions that aren't based on table content, for examples of how
946 this applies to various database types, see:
947 L<http://troels.arvin.dk/db/rdbms/#other-dummy_table>.
949 Note: If you're using Oracles dual table don't B<ever> do anything
950 other than a select, if you CRUD on your dual table you *will* break
953 Make a table class as you would for any other table
955 package MyAppDB::Dual;
958 use base 'DBIx::Class';
959 __PACKAGE__->load_components("Core");
960 __PACKAGE__->table("Dual");
961 __PACKAGE__->add_columns(
963 { data_type => "VARCHAR2", is_nullable => 0, size => 1 },
966 Once you've loaded your table class select from it using C<select>
967 and C<as> instead of C<columns>
969 my $rs = $schema->resultset('Dual')->search(undef,
970 { select => [ 'sydate' ],
975 All you have to do now is be careful how you access your resultset, the below
976 will not work because there is no column called 'now' in the Dual table class
978 while (my $dual = $rs->next) {
979 print $dual->now."\n";
981 # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23.
983 You could of course use 'dummy' in C<as> instead of 'now', or C<add_columns> to
984 your Dual class for whatever you wanted to select from dual, but that's just
985 silly, instead use C<get_column>
987 while (my $dual = $rs->next) {
988 print $dual->get_column('now')."\n";
993 my $cursor = $rs->cursor;
994 while (my @vals = $cursor->next) {
998 Or use L<DBIx::Class::ResultClass::HashRefInflator>
1000 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
1001 while ( my $dual = $rs->next ) {
1002 print $dual->{now}."\n";
1005 Here are some example C<select> conditions to illustrate the different syntax
1006 you could use for doing stuff like
1007 C<oracles.heavily(nested(functions_can('take', 'lots'), OF), 'args')>
1009 # get a sequence value
1010 select => [ 'A_SEQ.nextval' ],
1012 # get create table sql
1013 select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ],
1015 # get a random num between 0 and 100
1016 select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ],
1019 select => [ { 'extract' => [ \'year from sysdate' ] } ],
1022 select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}],
1024 # which day of the week were you born on?
1025 select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}],
1027 # select 16 rows from dual
1028 select => [ "'hello'" ],
1030 group_by => [ 'cube( 1, 2, 3, 4 )' ],
1034 =head2 Adding Indexes And Functions To Your SQL
1036 Often you will want indexes on columns on your table to speed up searching. To
1037 do this, create a method called C<sqlt_deploy_hook> in the relevant source
1040 package My::Schema::Artist;
1042 __PACKAGE__->table('artist');
1043 __PACKAGE__->add_columns(id => { ... }, name => { ... })
1045 sub sqlt_deploy_hook {
1046 my ($self, $sqlt_table) = @_;
1048 $sqlt_table->add_index(name => 'idx_name', fields => ['name']);
1053 Sometimes you might want to change the index depending on the type of the
1054 database for which SQL is being generated:
1056 my ($db_type = $sqlt_table->schema->translator->producer_type)
1057 =~ s/^SQL::Translator::Producer:://;
1059 You can also add hooks to the schema level to stop certain tables being
1066 sub sqlt_deploy_hook {
1067 my ($self, $sqlt_schema) = @_;
1069 $sqlt_schema->drop_table('table_name');
1072 You could also add views or procedures to the output using
1073 L<SQL::Translator::Schema/add_view> or
1074 L<SQL::Translator::Schema/add_procedure>.
1076 =head2 Schema versioning
1078 The following example shows simplistically how you might use DBIx::Class to
1079 deploy versioned schemas to your customers. The basic process is as follows:
1085 Create a DBIx::Class schema
1097 Modify schema to change functionality
1101 Deploy update to customers
1105 B<Create a DBIx::Class schema>
1107 This can either be done manually, or generated from an existing database as
1108 described under L</Creating Schemas From An Existing Database>
1112 Call L<DBIx::Class::Schema/create_ddl_dir> as above under L</Creating DDL SQL>.
1114 B<Deploy to customers>
1116 There are several ways you could deploy your schema. These are probably
1117 beyond the scope of this recipe, but might include:
1123 Require customer to apply manually using their RDBMS.
1127 Package along with your app, making database dump/schema update/tests
1128 all part of your install.
1132 B<Modify the schema to change functionality>
1134 As your application evolves, it may be necessary to modify your schema
1135 to change functionality. Once the changes are made to your schema in
1136 DBIx::Class, export the modified schema and the conversion scripts as
1137 in L</Creating DDL SQL>.
1139 B<Deploy update to customers>
1141 Add the L<DBIx::Class::Schema::Versioned> schema component to your
1142 Schema class. This will add a new table to your database called
1143 C<dbix_class_schema_vesion> which will keep track of which version is installed
1144 and warn if the user trys to run a newer schema version than the
1145 database thinks it has.
1147 Alternatively, you can send the conversion sql scripts to your
1150 =head2 Setting quoting for the generated SQL.
1152 If the database contains column names with spaces and/or reserved words, they
1153 need to be quoted in the SQL queries. This is done using:
1155 __PACKAGE__->storage->sql_maker->quote_char([ qw/[ ]/] );
1156 __PACKAGE__->storage->sql_maker->name_sep('.');
1158 The first sets the quote characters. Either a pair of matching
1159 brackets, or a C<"> or C<'>:
1161 __PACKAGE__->storage->sql_maker->quote_char('"');
1163 Check the documentation of your database for the correct quote
1164 characters to use. C<name_sep> needs to be set to allow the SQL
1165 generator to put the quotes the correct place.
1167 In most cases you should set these as part of the arguments passed to
1168 L<DBIx::Class::Schema/conect>:
1170 my $schema = My::Schema->connect(
1180 =head2 Setting limit dialect for SQL::Abstract::Limit
1182 In some cases, SQL::Abstract::Limit cannot determine the dialect of
1183 the remote SQL server by looking at the database handle. This is a
1184 common problem when using the DBD::JDBC, since the DBD-driver only
1185 know that in has a Java-driver available, not which JDBC driver the
1186 Java component has loaded. This specifically sets the limit_dialect
1187 to Microsoft SQL-server (See more names in SQL::Abstract::Limit
1190 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
1192 The JDBC bridge is one way of getting access to a MSSQL server from a platform
1193 that Microsoft doesn't deliver native client libraries for. (e.g. Linux)
1195 The limit dialect can also be set at connect time by specifying a
1196 C<limit_dialect> key in the final hash as shown above.
1198 =head1 BOOTSTRAPPING/MIGRATING
1200 =head2 Easy migration from class-based to schema-based setup
1202 You want to start using the schema-based approach to L<DBIx::Class>
1203 (see L<SchemaIntro.pod>), but have an established class-based setup with lots
1204 of existing classes that you don't want to move by hand. Try this nifty script
1208 use SQL::Translator;
1210 my $schema = MyDB->schema_instance;
1212 my $translator = SQL::Translator->new(
1213 debug => $debug || 0,
1214 trace => $trace || 0,
1215 no_comments => $no_comments || 0,
1216 show_warnings => $show_warnings || 0,
1217 add_drop_table => $add_drop_table || 0,
1218 validate => $validate || 0,
1220 'DBIx::Schema' => $schema,
1223 'prefix' => 'My::Schema',
1227 $translator->parser('SQL::Translator::Parser::DBIx::Class');
1228 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
1230 my $output = $translator->translate(@args) or die
1231 "Error: " . $translator->error;
1235 You could use L<Module::Find> to search for all subclasses in the MyDB::*
1236 namespace, which is currently left as an exercise for the reader.
1238 =head1 OVERLOADING METHODS
1240 L<DBIx::Class> uses the L<Class::C3> package, which provides for redispatch of
1241 method calls, useful for things like default values and triggers. You have to
1242 use calls to C<next::method> to overload methods. More information on using
1243 L<Class::C3> with L<DBIx::Class> can be found in
1244 L<DBIx::Class::Manual::Component>.
1246 =head2 Setting default values for a row
1248 It's as simple as overriding the C<new> method. Note the use of
1252 my ( $class, $attrs ) = @_;
1254 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
1256 my $new = $class->next::method($attrs);
1261 For more information about C<next::method>, look in the L<Class::C3>
1262 documentation. See also L<DBIx::Class::Manual::Component> for more
1263 ways to write your own base classes to do this.
1265 People looking for ways to do "triggers" with DBIx::Class are probably
1266 just looking for this.
1268 =head2 Changing one field whenever another changes
1270 For example, say that you have three columns, C<id>, C<number>, and
1271 C<squared>. You would like to make changes to C<number> and have
1272 C<squared> be automagically set to the value of C<number> squared.
1273 You can accomplish this by overriding C<store_column>:
1276 my ( $self, $name, $value ) = @_;
1277 if ($name eq 'number') {
1278 $self->squared($value * $value);
1280 $self->next::method($name, $value);
1283 Note that the hard work is done by the call to C<next::method>, which
1284 redispatches your call to store_column in the superclass(es).
1286 =head2 Automatically creating related objects
1288 You might have a class C<Artist> which has many C<CD>s. Further, if you
1289 want to create a C<CD> object every time you insert an C<Artist> object.
1290 You can accomplish this by overriding C<insert> on your objects:
1293 my ( $self, @args ) = @_;
1294 $self->next::method(@args);
1295 $self->cds->new({})->fill_from_artist($self)->insert;
1299 where C<fill_from_artist> is a method you specify in C<CD> which sets
1300 values in C<CD> based on the data in the C<Artist> object you pass in.
1302 =head2 Wrapping/overloading a column accessor
1306 Say you have a table "Camera" and want to associate a description
1307 with each camera. For most cameras, you'll be able to generate the description from
1308 the other columns. However, in a few special cases you may want to associate a
1309 custom description with a camera.
1313 In your database schema, define a description field in the "Camera" table that
1314 can contain text and null values.
1316 In DBIC, we'll overload the column accessor to provide a sane default if no
1317 custom description is defined. The accessor will either return or generate the
1318 description, depending on whether the field is null or not.
1320 First, in your "Camera" schema class, define the description field as follows:
1322 __PACKAGE__->add_columns(description => { accessor => '_description' });
1324 Next, we'll define the accessor-wrapper subroutine:
1329 # If there is an update to the column, we'll let the original accessor
1331 return $self->_description(@_) if @_;
1333 # Fetch the column value.
1334 my $description = $self->_description;
1336 # If there's something in the description field, then just return that.
1337 return $description if defined $description && length $descripton;
1339 # Otherwise, generate a description.
1340 return $self->generate_description;
1343 =head1 DEBUGGING AND PROFILING
1345 =head2 DBIx::Class objects with Data::Dumper
1347 L<Data::Dumper> can be a very useful tool for debugging, but sometimes it can
1348 be hard to find the pertinent data in all the data it can generate.
1349 Specifically, if one naively tries to use it like so,
1353 my $cd = $schema->resultset('CD')->find(1);
1356 several pages worth of data from the CD object's schema and result source will
1357 be dumped to the screen. Since usually one is only interested in a few column
1358 values of the object, this is not very helpful.
1360 Luckily, it is possible to modify the data before L<Data::Dumper> outputs
1361 it. Simply define a hook that L<Data::Dumper> will call on the object before
1362 dumping it. For example,
1369 result_source => undef,
1377 local $Data::Dumper::Freezer = '_dumper_hook';
1379 my $cd = $schema->resultset('CD')->find(1);
1381 # dumps $cd without its ResultSource
1383 If the structure of your schema is such that there is a common base class for
1384 all your table classes, simply put a method similar to C<_dumper_hook> in the
1385 base class and set C<$Data::Dumper::Freezer> to its name and L<Data::Dumper>
1386 will automagically clean up your data before printing it. See
1387 L<Data::Dumper/EXAMPLES> for more information.
1391 When you enable L<DBIx::Class::Storage>'s debugging it prints the SQL
1392 executed as well as notifications of query completion and transaction
1393 begin/commit. If you'd like to profile the SQL you can subclass the
1394 L<DBIx::Class::Storage::Statistics> class and write your own profiling
1397 package My::Profiler;
1400 use base 'DBIx::Class::Storage::Statistics';
1402 use Time::HiRes qw(time);
1411 $self->print("Executing $sql: ".join(', ', @params)."\n");
1420 my $elapsed = sprintf("%0.4f", time() - $start);
1421 $self->print("Execution took $elapsed seconds.\n");
1427 You can then install that class as the debugging object:
1429 __PACKAGE__->storage->debugobj(new My::Profiler());
1430 __PACKAGE__->storage->debug(1);
1432 A more complicated example might involve storing each execution of SQL in an
1440 my $elapsed = time() - $start;
1441 push(@{ $calls{$sql} }, {
1447 You could then create average, high and low execution times for an SQL
1448 statement and dig down to see if certain parameters cause aberrant behavior.
1449 You might want to check out L<DBIx::Class::QueryLog> as well.