1DBIx::Class::Manual::CoUoskebrooCko(n3t)ributed Perl DocDuBmIexn:t:aCtliaosns::Manual::Cookbook(3)
2
6 DBIx::Class::Manual::Cookbook - Miscellaneous recipes
7
9 Paged results
10 When you expect a large number of results, you can ask DBIx::Class for
11 a paged resultset, which will fetch only a defined number of records at
12 a time:
13 my $rs = $schema->resultset('Artist')->search(
15 undef,
16 {
17 page => 1, # page to return (defaults to 1)
18 rows => 10, # number of results per page
19 },
20 );
21 return $rs->all(); # all records for page 1
23 return $rs->page(2); # records for page 2
25 You can get a Data::Page object for the resultset (suitable for use in
27 e.g. a template) using the "pager" method:
28 return $rs->pager();
30 Complex WHERE clauses
32 Sometimes you need to formulate a query using specific operators:
33 my @albums = $schema->resultset('Album')->search({
35 artist => { 'like', '%Lamb%' },
36 title => { 'like', '%Fear of Fours%' },
37 });
38 This results in something like the following "WHERE" clause:
40 WHERE artist LIKE ? AND title LIKE ?
42 And the following bind values for the placeholders: '%Lamb%', '%Fear of
44 Fours%'.
45 Other queries might require slightly more complex logic:
47 my @albums = $schema->resultset('Album')->search({
49 -or => [
50 -and => [
51 artist => { 'like', '%Smashing Pumpkins%' },
52 title => 'Siamese Dream',
53 ],
54 artist => 'Starchildren',
55 ],
56 });
57 This results in the following "WHERE" clause:
59 WHERE ( artist LIKE '%Smashing Pumpkins%' AND title = 'Siamese Dream' )
61 OR artist = 'Starchildren'
62 For more information on generating complex queries, see "WHERE CLAUSES"
64 in SQL::Abstract.
65 Retrieve one and only one row from a resultset
67 Sometimes you need only the first "top" row of a resultset. While this
68 can be easily done with $rs->first, it is suboptimal, as a full blown
69 cursor for the resultset will be created and then immediately destroyed
70 after fetching the first row object. $rs->single is designed
71 specifically for this case - it will grab the first returned result
72 without even instantiating a cursor.
73 Before replacing all your calls to "first()" with "single()" please
75 observe the following CAVEATS:
76 · While single() takes a search condition just like search() does, it
78 does _not_ accept search attributes. However one can always chain a
79 single() to a search():
80 my $top_cd = $cd_rs->search({}, { order_by => 'rating' })->single;
82 · Since single() is the engine behind find(), it is designed to fetch
84 a single row per database query. Thus a warning will be issued when
85 the underlying SELECT returns more than one row. Sometimes however
86 this usage is valid: i.e. we have an arbitrary number of cd's but
87 only one of them is at the top of the charts at any given time. If
88 you know what you are doing, you can silence the warning by
89 explicitly limiting the resultset size:
90 my $top_cd = $cd_rs->search ({}, { order_by => 'rating', rows => 1 })->single;
92 Arbitrary SQL through a custom ResultSource
94 Sometimes you have to run arbitrary SQL because your query is too
95 complex (e.g. it contains Unions, Sub-Selects, Stored Procedures, etc.)
96 or has to be optimized for your database in a special way, but you
97 still want to get the results as a DBIx::Class::ResultSet.
98 This is accomplished by defining a ResultSource::View for your query,
100 almost like you would define a regular ResultSource.
101 package My::Schema::Result::UserFriendsComplex;
103 use strict;
104 use warnings;
105 use base qw/DBIx::Class::Core/;
106 __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
108 # ->table, ->add_columns, etc.
110 # do not attempt to deploy() this view
112 __PACKAGE__->result_source_instance->is_virtual(1);
113 __PACKAGE__->result_source_instance->view_definition(q[
115 SELECT u.* FROM user u
116 INNER JOIN user_friends f ON u.id = f.user_id
117 WHERE f.friend_user_id = ?
118 UNION
119 SELECT u.* FROM user u
120 INNER JOIN user_friends f ON u.id = f.friend_user_id
121 WHERE f.user_id = ?
122 ]);
123 Next, you can execute your complex query using bind parameters like
125 this:
126 my $friends = $schema->resultset( 'UserFriendsComplex' )->search( {},
128 {
129 bind => [ 12345, 12345 ]
130 }
131 );
132 ... and you'll get back a perfect DBIx::Class::ResultSet (except, of
134 course, that you cannot modify the rows it contains, e.g. cannot call
135 "update", "delete", ... on it).
136 Note that you cannot have bind parameters unless is_virtual is set to
138 true.
139 · NOTE
141 If you're using the old deprecated "$rsrc_instance->name(\'( SELECT
143 ...')" method for custom SQL execution, you are highly encouraged
144 to update your code to use a virtual view as above. If you do not
145 want to change your code, and just want to suppress the deprecation
146 warning when you call "deploy" in DBIx::Class::Schema, add this
147 line to your source definition, so that "deploy" will exclude this
148 "table":
149 sub sqlt_deploy_hook { $_[1]->schema->drop_table ($_[1]) }
151 Using specific columns
153 When you only want specific columns from a table, you can use "columns"
154 to specify which ones you need. This is useful to avoid loading columns
155 with large amounts of data that you aren't about to use anyway:
156 my $rs = $schema->resultset('Artist')->search(
158 undef,
159 {
160 columns => [qw/ name /]
161 }
162 );
163 # Equivalent SQL:
165 # SELECT artist.name FROM artist
166 This is a shortcut for "select" and "as", see below. "columns" cannot
168 be used together with "select" and "as".
169 Using database functions or stored procedures
171 The combination of "select" and "as" can be used to return the result
172 of a database function or stored procedure as a column value. You use
173 "select" to specify the source for your column value (e.g. a column
174 name, function, or stored procedure name). You then use "as" to set the
175 column name you will use to access the returned value:
176 my $rs = $schema->resultset('Artist')->search(
178 {},
179 {
180 select => [ 'name', { LENGTH => 'name' } ],
181 as => [qw/ name name_length /],
182 }
183 );
184 # Equivalent SQL:
186 # SELECT name name, LENGTH( name )
187 # FROM artist
188 Note that the "as" attribute has absolutely nothing to do with the SQL
190 syntax " SELECT foo AS bar " (see the documentation in "ATTRIBUTES" in
191 DBIx::Class::ResultSet). You can control the "AS" part of the generated
192 SQL via the "-as" field attribute as follows:
193 my $rs = $schema->resultset('Artist')->search(
195 {},
196 {
197 join => 'cds',
198 distinct => 1,
199 '+select' => [ { count => 'cds.cdid', -as => 'amount_of_cds' } ],
200 '+as' => [qw/num_cds/],
201 order_by => { -desc => 'amount_of_cds' },
202 }
203 );
204 # Equivalent SQL
206 # SELECT me.artistid, me.name, me.rank, me.charfield, COUNT( cds.cdid ) AS amount_of_cds
207 # FROM artist me LEFT JOIN cd cds ON cds.artist = me.artistid
208 # GROUP BY me.artistid, me.name, me.rank, me.charfield
209 # ORDER BY amount_of_cds DESC
210 If your alias exists as a column in your base class (i.e. it was added
212 with add_columns), you just access it as normal. Our "Artist" class has
213 a "name" column, so we just use the "name" accessor:
214 my $artist = $rs->first();
216 my $name = $artist->name();
217 If on the other hand the alias does not correspond to an existing
219 column, you have to fetch the value using the "get_column" accessor:
220 my $name_length = $artist->get_column('name_length');
222 If you don't like using "get_column", you can always create an accessor
224 for any of your aliases using either of these:
225 # Define accessor manually:
227 sub name_length { shift->get_column('name_length'); }
228 # Or use DBIx::Class::AccessorGroup:
230 __PACKAGE__->mk_group_accessors('column' => 'name_length');
231 See also "Using SQL functions on the left hand side of a comparison".
233 SELECT DISTINCT with multiple columns
235 my $rs = $schema->resultset('Artist')->search(
236 {},
237 {
238 columns => [ qw/artist_id name rank/ ],
239 distinct => 1
240 }
241 );
242 my $rs = $schema->resultset('Artist')->search(
244 {},
245 {
246 columns => [ qw/artist_id name rank/ ],
247 group_by => [ qw/artist_id name rank/ ],
248 }
249 );
250 # Equivalent SQL:
252 # SELECT me.artist_id, me.name, me.rank
253 # FROM artist me
254 # GROUP BY artist_id, name, rank
255 SELECT COUNT(DISTINCT colname)
257 my $rs = $schema->resultset('Artist')->search(
258 {},
259 {
260 columns => [ qw/name/ ],
261 distinct => 1
262 }
263 );
264 my $rs = $schema->resultset('Artist')->search(
266 {},
267 {
268 columns => [ qw/name/ ],
269 group_by => [ qw/name/ ],
270 }
271 );
272 my $count = $rs->count;
274 # Equivalent SQL:
276 # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq:
277 Grouping results
279 DBIx::Class supports "GROUP BY" as follows:
280 my $rs = $schema->resultset('Artist')->search(
282 {},
283 {
284 join => [qw/ cds /],
285 select => [ 'name', { count => 'cds.id' } ],
286 as => [qw/ name cd_count /],
287 group_by => [qw/ name /]
288 }
289 );
290 # Equivalent SQL:
292 # SELECT name, COUNT( cd.id ) FROM artist
293 # LEFT JOIN cd ON artist.id = cd.artist
294 # GROUP BY name
295 Please see "ATTRIBUTES" in DBIx::Class::ResultSet documentation if you
297 are in any way unsure about the use of the attributes above (" join ",
298 " select ", " as " and " group_by ").
299 Subqueries
301 You can write subqueries relatively easily in DBIC.
302 my $inside_rs = $schema->resultset('Artist')->search({
304 name => [ 'Billy Joel', 'Brittany Spears' ],
305 });
306 my $rs = $schema->resultset('CD')->search({
308 artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
309 });
310 The usual operators ( =, !=, IN, NOT IN, etc.) are supported.
312 NOTE: You have to explicitly use '=' when doing an equality comparison.
314 The following will not work:
315 my $rs = $schema->resultset('CD')->search({
317 artist_id => $inside_rs->get_column('id')->as_query, # does NOT work
318 });
319 Support
321 Subqueries are supported in the where clause (first hashref), and in
323 the from, select, and +select attributes.
324 Correlated subqueries
326 my $cdrs = $schema->resultset('CD');
328 my $rs = $cdrs->search({
329 year => {
330 '=' => $cdrs->search(
331 { artist_id => { '=' => \'me.artist_id' } },
332 { alias => 'inner' }
333 )->get_column('year')->max_rs->as_query,
334 },
335 });
336 That creates the following SQL:
338 SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
340 FROM cd me
341 WHERE year = (
342 SELECT MAX(inner.year)
343 FROM cd inner
344 WHERE artist_id = me.artist_id
345 )
346 Predefined searches
348 You can write your own DBIx::Class::ResultSet class by inheriting from
349 it and defining often used searches as methods:
350 package My::DBIC::ResultSet::CD;
352 use strict;
353 use warnings;
354 use base 'DBIx::Class::ResultSet';
355 sub search_cds_ordered {
357 my ($self) = @_;
358 return $self->search(
360 {},
361 { order_by => 'name DESC' },
362 );
363 }
364 1;
366 If you're using "load_namespaces" in DBIx::Class::Schema, simply place
368 the file into the "ResultSet" directory next to your "Result"
369 directory, and it will be automatically loaded.
370 If however you are still using "load_classes" in DBIx::Class::Schema,
372 first tell DBIx::Class to create an instance of the ResultSet class for
373 you, in your My::DBIC::Schema::CD class:
374 # class definition as normal
376 use base 'DBIx::Class::Core';
377 __PACKAGE__->table('cd');
378 # tell DBIC to use the custom ResultSet class
380 __PACKAGE__->resultset_class('My::DBIC::ResultSet::CD');
381 Note that "resultset_class" must be called after "load_components" and
383 "table", or you will get errors about missing methods.
384 Then call your new method in your code:
386 my $ordered_cds = $schema->resultset('CD')->search_cds_ordered();
388 Using SQL functions on the left hand side of a comparison
390 Using SQL functions on the left hand side of a comparison is generally
391 not a good idea since it requires a scan of the entire table. (Unless
392 your RDBMS supports indexes on expressions - including return values of
393 functions - and you create an index on the return value of the function
394 in question.) However, it can be accomplished with "DBIx::Class" when
395 necessary.
396 If you do not have quoting on, simply include the function in your
398 search specification as you would any column:
399 $rs->search({ 'YEAR(date_of_birth)' => 1979 });
401 With quoting on, or for a more portable solution, use literal SQL
403 values with placeholders:
404 $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
406 # Equivalent SQL:
408 # SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
409 $rs->search({
411 name => 'Bob',
412 -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
413 });
414 # Equivalent SQL:
416 # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
417 Note: the "plain_value" string in the "[ plain_value => 1979 ]" part
419 should be either the same as the name of the column (do this if the
420 type of the return value of the function is the same as the type of the
421 column) or otherwise it's essentially a dummy string currently (use
422 "plain_value" as a habit). It is used by DBIx::Class to handle special
423 column types.
424 See also "Literal SQL with placeholders and bind values (subqueries)"
426 in SQL::Abstract.
427
429 Using joins and prefetch
430 You can use the "join" attribute to allow searching on, or sorting your
431 results by, one or more columns in a related table.
432 This requires that you have defined the DBIx::Class::Relationship. For
434 example :
435 My::Schema::CD->has_many( artists => 'My::Schema::Artist', 'artist_id');
437 To return all CDs matching a particular artist name, you specify the
439 name of the relationship ('artists'):
440 my $rs = $schema->resultset('CD')->search(
442 {
443 'artists.name' => 'Bob Marley'
444 },
445 {
446 join => 'artists', # join the artist table
447 }
448 );
449 # Equivalent SQL:
451 # SELECT cd.* FROM cd
452 # JOIN artist ON cd.artist = artist.id
453 # WHERE artist.name = 'Bob Marley'
454 In that example both the join, and the condition use the relationship
456 name rather than the table name (see DBIx::Class::Manual::Joining for
457 more details on aliasing ).
458 If required, you can now sort on any column in the related tables by
460 including it in your "order_by" attribute, (again using the aliased
461 relation name rather than table name) :
462 my $rs = $schema->resultset('CD')->search(
464 {
465 'artists.name' => 'Bob Marley'
466 },
467 {
468 join => 'artists',
469 order_by => [qw/ artists.name /]
470 }
471 );
472 # Equivalent SQL:
474 # SELECT cd.* FROM cd
475 # JOIN artist ON cd.artist = artist.id
476 # WHERE artist.name = 'Bob Marley'
477 # ORDER BY artist.name
478 Note that the "join" attribute should only be used when you need to
480 search or sort using columns in a related table. Joining related tables
481 when you only need columns from the main table will make performance
482 worse!
483 Now let's say you want to display a list of CDs, each with the name of
485 the artist. The following will work fine:
486 while (my $cd = $rs->next) {
488 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
489 }
490 There is a problem however. We have searched both the "cd" and "artist"
492 tables in our main query, but we have only returned data from the "cd"
493 table. To get the artist name for any of the CD objects returned,
494 DBIx::Class will go back to the database:
495 SELECT artist.* FROM artist WHERE artist.id = ?
497 A statement like the one above will run for each and every CD returned
499 by our main query. Five CDs, five extra queries. A hundred CDs, one
500 hundred extra queries!
501 Thankfully, DBIx::Class has a "prefetch" attribute to solve this
503 problem. This allows you to fetch results from related tables in
504 advance:
505 my $rs = $schema->resultset('CD')->search(
507 {
508 'artists.name' => 'Bob Marley'
509 },
510 {
511 join => 'artists',
512 order_by => [qw/ artists.name /],
513 prefetch => 'artists' # return artist data too!
514 }
515 );
516 # Equivalent SQL (note SELECT from both "cd" and "artist"):
518 # SELECT cd.*, artist.* FROM cd
519 # JOIN artist ON cd.artist = artist.id
520 # WHERE artist.name = 'Bob Marley'
521 # ORDER BY artist.name
522 The code to print the CD list remains the same:
524 while (my $cd = $rs->next) {
526 print "CD: " . $cd->title . ", Artist: " . $cd->artist->name;
527 }
528 DBIx::Class has now prefetched all matching data from the "artist"
530 table, so no additional SQL statements are executed. You now have a
531 much more efficient query.
532 Also note that "prefetch" should only be used when you know you will
534 definitely use data from a related table. Pre-fetching related tables
535 when you only need columns from the main table will make performance
536 worse!
537 Multiple joins
539 In the examples above, the "join" attribute was a scalar. If you pass
540 an array reference instead, you can join to multiple tables. In this
541 example, we want to limit the search further, using "LinerNotes":
542 # Relationships defined elsewhere:
544 # CD->belongs_to('artist' => 'Artist');
545 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
546 my $rs = $schema->resultset('CD')->search(
547 {
548 'artist.name' => 'Bob Marley'
549 'liner_notes.notes' => { 'like', '%some text%' },
550 },
551 {
552 join => [qw/ artist liner_notes /],
553 order_by => [qw/ artist.name /],
554 }
555 );
556 # Equivalent SQL:
558 # SELECT cd.*, artist.*, liner_notes.* FROM cd
559 # JOIN artist ON cd.artist = artist.id
560 # JOIN liner_notes ON cd.id = liner_notes.cd
561 # WHERE artist.name = 'Bob Marley'
562 # ORDER BY artist.name
563 Multi-step joins
565 Sometimes you want to join more than one relationship deep. In this
566 example, we want to find all "Artist" objects who have "CD"s whose
567 "LinerNotes" contain a specific string:
568 # Relationships defined elsewhere:
570 # Artist->has_many('cds' => 'CD', 'artist');
571 # CD->has_one('liner_notes' => 'LinerNotes', 'cd');
572 my $rs = $schema->resultset('Artist')->search(
574 {
575 'liner_notes.notes' => { 'like', '%some text%' },
576 },
577 {
578 join => {
579 'cds' => 'liner_notes'
580 }
581 }
582 );
583 # Equivalent SQL:
585 # SELECT artist.* FROM artist
586 # LEFT JOIN cd ON artist.id = cd.artist
587 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
588 # WHERE liner_notes.notes LIKE '%some text%'
589 Joins can be nested to an arbitrary level. So if we decide later that
591 we want to reduce the number of Artists returned based on who wrote the
592 liner notes:
593 # Relationship defined elsewhere:
595 # LinerNotes->belongs_to('author' => 'Person');
596 my $rs = $schema->resultset('Artist')->search(
598 {
599 'liner_notes.notes' => { 'like', '%some text%' },
600 'author.name' => 'A. Writer'
601 },
602 {
603 join => {
604 'cds' => {
605 'liner_notes' => 'author'
606 }
607 }
608 }
609 );
610 # Equivalent SQL:
612 # SELECT artist.* FROM artist
613 # LEFT JOIN cd ON artist.id = cd.artist
614 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
615 # LEFT JOIN author ON author.id = liner_notes.author
616 # WHERE liner_notes.notes LIKE '%some text%'
617 # AND author.name = 'A. Writer'
618 Multi-step and multiple joins
620 With various combinations of array and hash references, you can join
621 tables in any combination you desire. For example, to join Artist to
622 CD and Concert, and join CD to LinerNotes:
623 # Relationships defined elsewhere:
625 # Artist->has_many('concerts' => 'Concert', 'artist');
626 my $rs = $schema->resultset('Artist')->search(
628 { },
629 {
630 join => [
631 {
632 cds => 'liner_notes'
633 },
634 'concerts'
635 ],
636 }
637 );
638 # Equivalent SQL:
640 # SELECT artist.* FROM artist
641 # LEFT JOIN cd ON artist.id = cd.artist
642 # LEFT JOIN liner_notes ON cd.id = liner_notes.cd
643 # LEFT JOIN concert ON artist.id = concert.artist
644 Multi-step prefetch
646 "prefetch" can be nested more than one relationship deep using the same
647 syntax as a multi-step join:
648 my $rs = $schema->resultset('Tag')->search(
650 {},
651 {
652 prefetch => {
653 cd => 'artist'
654 }
655 }
656 );
657 # Equivalent SQL:
659 # SELECT tag.*, cd.*, artist.* FROM tag
660 # JOIN cd ON tag.cd = cd.id
661 # JOIN artist ON cd.artist = artist.id
662 Now accessing our "cd" and "artist" relationships does not need
664 additional SQL statements:
665 my $tag = $rs->first;
667 print $tag->cd->artist->name;
668
670 Retrieving a row object's Schema
671 It is possible to get a Schema object from a row object like so:
672 my $schema = $cd->result_source->schema;
674 # use the schema as normal:
675 my $artist_rs = $schema->resultset('Artist');
676 This can be useful when you don't want to pass around a Schema object
678 to every method.
679 Getting the value of the primary key for the last database insert
681 AKA getting last_insert_id
682 Thanks to the core component PK::Auto, this is straightforward:
684 my $foo = $rs->create(\%blah);
686 # do more stuff
687 my $id = $foo->id; # foo->my_primary_key_field will also work.
688 If you are not using autoincrementing primary keys, this will probably
690 not work, but then you already know the value of the last primary key
691 anyway.
692 Stringification
694 Employ the standard stringification technique by using the overload
695 module.
696 To make an object stringify itself as a single column, use something
698 like this (replace "name" with the column/method of your choice):
699 use overload '""' => sub { shift->name}, fallback => 1;
701 For more complex stringification, you can use an anonymous subroutine:
703 use overload '""' => sub { $_[0]->name . ", " .
705 $_[0]->address }, fallback => 1;
706 Stringification Example
708 Suppose we have two tables: "Product" and "Category". The table
710 specifications are:
711 Product(id, Description, category)
713 Category(id, Description)
714 "category" is a foreign key into the Category table.
716 If you have a Product object $obj and write something like
718 print $obj->category
720 things will not work as expected.
722 To obtain, for example, the category description, you should add this
724 method to the class defining the Category table:
725 use overload "" => sub {
727 my $self = shift;
728 return $self->Description;
730 }, fallback => 1;
731 Want to know if find_or_create found or created a row?
733 Just use "find_or_new" instead, then check "in_storage":
734 my $obj = $rs->find_or_new({ blah => 'blarg' });
736 unless ($obj->in_storage) {
737 $obj->insert;
738 # do whatever else you wanted if it was a new row
739 }
740 Static sub-classing DBIx::Class result classes
742 AKA adding additional relationships/methods/etc. to a model for a
743 specific usage of the (shared) model.
744 Schema definition
746 package My::App::Schema;
748 use base 'DBIx::Class::Schema';
750 # load subclassed classes from My::App::Schema::Result/ResultSet
752 __PACKAGE__->load_namespaces;
753 # load classes from shared model
755 load_classes({
756 'My::Shared::Model::Result' => [qw/
757 Foo
758 Bar
759 /]});
760 1;
762 Result-Subclass definition
764 package My::App::Schema::Result::Baz;
766 use strict;
768 use warnings;
769 use base 'My::Shared::Model::Result::Baz';
770 # WARNING: Make sure you call table() again in your subclass,
772 # otherwise DBIx::Class::ResultSourceProxy::Table will not be called
773 # and the class name is not correctly registered as a source
774 __PACKAGE__->table('baz');
775 sub additional_method {
777 return "I'm an additional method only needed by this app";
778 }
779 1;
781 Dynamic Sub-classing DBIx::Class proxy classes
783 AKA multi-class object inflation from one table
784 DBIx::Class classes are proxy classes, therefore some different
786 techniques need to be employed for more than basic subclassing. In
787 this example we have a single user table that carries a boolean bit for
788 admin. We would like like to give the admin users objects
789 (DBIx::Class::Row) the same methods as a regular user but also special
790 admin only methods. It doesn't make sense to create two separate
791 proxy-class files for this. We would be copying all the user methods
792 into the Admin class. There is a cleaner way to accomplish this.
793 Overriding the "inflate_result" method within the User proxy-class
795 gives us the effect we want. This method is called by
796 DBIx::Class::ResultSet when inflating a result from storage. So we
797 grab the object being returned, inspect the values we are looking for,
798 bless it if it's an admin object, and then return it. See the example
799 below:
800 Schema Definition
802 package My::Schema;
804 use base qw/DBIx::Class::Schema/;
806 __PACKAGE__->load_namespaces;
808 1;
810 Proxy-Class definitions
812 package My::Schema::Result::User;
814 use strict;
816 use warnings;
817 use base qw/DBIx::Class::Core/;
818 ### Define what our admin class is, for ensure_class_loaded()
820 my $admin_class = __PACKAGE__ . '::Admin';
821 __PACKAGE__->table('users');
823 __PACKAGE__->add_columns(qw/user_id email password
825 firstname lastname active
826 admin/);
827 __PACKAGE__->set_primary_key('user_id');
829 sub inflate_result {
831 my $self = shift;
832 my $ret = $self->next::method(@_);
833 if( $ret->admin ) {### If this is an admin, rebless for extra functions
834 $self->ensure_class_loaded( $admin_class );
835 bless $ret, $admin_class;
836 }
837 return $ret;
838 }
839 sub hello {
841 print "I am a regular user.\n";
842 return ;
843 }
844 1;
846 package My::Schema::Result::User::Admin;
849 use strict;
851 use warnings;
852 use base qw/My::Schema::Result::User/;
853 # This line is important
855 __PACKAGE__->table('users');
856 sub hello
858 {
859 print "I am an admin.\n";
860 return;
861 }
862 sub do_admin_stuff
864 {
865 print "I am doing admin stuff\n";
866 return ;
867 }
868 1;
870 Test File test.pl
872 use warnings;
874 use strict;
875 use My::Schema;
876 my $user_data = { email => 'someguy@place.com',
878 password => 'pass1',
879 admin => 0 };
880 my $admin_data = { email => 'someadmin@adminplace.com',
882 password => 'pass2',
883 admin => 1 };
884 my $schema = My::Schema->connection('dbi:Pg:dbname=test');
886 $schema->resultset('User')->create( $user_data );
888 $schema->resultset('User')->create( $admin_data );
889 ### Now we search for them
891 my $user = $schema->resultset('User')->single( $user_data );
892 my $admin = $schema->resultset('User')->single( $admin_data );
893 print ref $user, "\n";
895 print ref $admin, "\n";
896 print $user->password , "\n"; # pass1
898 print $admin->password , "\n";# pass2; inherited from User
899 print $user->hello , "\n";# I am a regular user.
900 print $admin->hello, "\n";# I am an admin.
901 ### The statement below will NOT print
903 print "I can do admin stuff\n" if $user->can('do_admin_stuff');
904 ### The statement below will print
905 print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
906 Alternatively you can use DBIx::Class::DynamicSubclass that implements
908 exactly the above functionality.
909 Skip row object creation for faster results
911 DBIx::Class is not built for speed, it's built for convenience and ease
912 of use, but sometimes you just need to get the data, and skip the fancy
913 objects.
914 To do this simply use DBIx::Class::ResultClass::HashRefInflator.
916 my $rs = $schema->resultset('CD');
918 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
920 my $hash_ref = $rs->find(1);
922 Wasn't that easy?
924 Beware, changing the Result class using "result_class" in
926 DBIx::Class::ResultSet will replace any existing class completely
927 including any special components loaded using load_components, eg
928 DBIx::Class::InflateColumn::DateTime.
929 Get raw data for blindingly fast results
931 If the HashRefInflator solution above is not fast enough for you, you
932 can use a DBIx::Class to return values exactly as they come out of the
933 database with none of the convenience methods wrapped round them.
934 This is used like so:
936 my $cursor = $rs->cursor
938 while (my @vals = $cursor->next) {
939 # use $val[0..n] here
940 }
941 You will need to map the array offsets to particular columns (you can
943 use the "select" in DBIx::Class::ResultSet attribute of "search" in
944 DBIx::Class::ResultSet to force ordering).
945
947 Getting Schema from a ResultSet
948 To get the DBIx::Class::Schema object from a ResultSet, do the
949 following:
950 $rs->result_source->schema
952 Getting Columns Of Data
954 AKA Aggregating Data
955 If you want to find the sum of a particular column there are several
957 ways, the obvious one is to use search:
958 my $rs = $schema->resultset('Items')->search(
960 {},
961 {
962 select => [ { sum => 'Cost' } ],
963 as => [ 'total_cost' ], # remember this 'as' is for DBIx::Class::ResultSet not SQL
964 }
965 );
966 my $tc = $rs->first->get_column('total_cost');
967 Or, you can use the DBIx::Class::ResultSetColumn, which gets returned
969 when you ask the "ResultSet" for a column using "get_column":
970 my $cost = $schema->resultset('Items')->get_column('Cost');
972 my $tc = $cost->sum;
973 With this you can also do:
975 my $minvalue = $cost->min;
977 my $maxvalue = $cost->max;
978 Or just iterate through the values of this column only:
980 while ( my $c = $cost->next ) {
982 print $c;
983 }
984 foreach my $c ($cost->all) {
986 print $c;
987 }
988 "ResultSetColumn" only has a limited number of built-in functions. If
990 you need one that it doesn't have, then you can use the "func" method
991 instead:
992 my $avg = $cost->func('AVERAGE');
994 This will cause the following SQL statement to be run:
996 SELECT AVERAGE(Cost) FROM Items me
998 Which will of course only work if your database supports this function.
1000 See DBIx::Class::ResultSetColumn for more documentation.
1001 Creating a result set from a set of rows
1003 Sometimes you have a (set of) row objects that you want to put into a
1004 resultset without the need to hit the DB again. You can do that by
1005 using the set_cache method:
1006 my @uploadable_groups;
1008 while (my $group = $groups->next) {
1009 if ($group->can_upload($self)) {
1010 push @uploadable_groups, $group;
1011 }
1012 }
1013 my $new_rs = $self->result_source->resultset;
1014 $new_rs->set_cache(\@uploadable_groups);
1015 return $new_rs;
1016
1018 Create a new row in a related table
1019 my $author = $book->create_related('author', { name => 'Fred'});
1020 Search in a related table
1022 Only searches for books named 'Titanic' by the author in $author.
1023 my $books_rs = $author->search_related('books', { name => 'Titanic' });
1025 Delete data in a related table
1027 Deletes only the book named Titanic by the author in $author.
1028 $author->delete_related('books', { name => 'Titanic' });
1030 Ordering a relationship result set
1032 If you always want a relation to be ordered, you can specify this when
1033 you create the relationship.
1034 To order "$book->pages" by descending page_number, create the relation
1036 as follows:
1037 __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } );
1039 Filtering a relationship result set
1041 If you want to get a filtered result set, you can just add add to $attr
1042 as follows:
1043 __PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } );
1045 Many-to-many relationships
1047 This is straightforward using ManyToMany:
1048 package My::User;
1050 use base 'DBIx::Class::Core';
1051 __PACKAGE__->table('user');
1052 __PACKAGE__->add_columns(qw/id name/);
1053 __PACKAGE__->set_primary_key('id');
1054 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'user');
1055 __PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
1056 package My::UserAddress;
1058 use base 'DBIx::Class::Core';
1059 __PACKAGE__->table('user_address');
1060 __PACKAGE__->add_columns(qw/user address/);
1061 __PACKAGE__->set_primary_key(qw/user address/);
1062 __PACKAGE__->belongs_to('user' => 'My::User');
1063 __PACKAGE__->belongs_to('address' => 'My::Address');
1064 package My::Address;
1066 use base 'DBIx::Class::Core';
1067 __PACKAGE__->table('address');
1068 __PACKAGE__->add_columns(qw/id street town area_code country/);
1069 __PACKAGE__->set_primary_key('id');
1070 __PACKAGE__->has_many('user_address' => 'My::UserAddress', 'address');
1071 __PACKAGE__->many_to_many('users' => 'user_address', 'user');
1072 $rs = $user->addresses(); # get all addresses for a user
1074 $rs = $address->users(); # get all users for an address
1075 my $address = $user->add_to_addresses( # returns a My::Address instance,
1077 # NOT a My::UserAddress instance!
1078 {
1079 country => 'United Kingdom',
1080 area_code => 'XYZ',
1081 town => 'London',
1082 street => 'Sesame',
1083 }
1084 );
1085 Relationships across DB schemas
1087 Mapping relationships across DB schemas is easy as long as the schemas
1088 themselves are all accessible via the same DBI connection. In most
1089 cases, this means that they are on the same database host as each other
1090 and your connecting database user has the proper permissions to them.
1091 To accomplish this one only needs to specify the DB schema name in the
1093 table declaration, like so...
1094 package MyDatabase::Main::Artist;
1096 use base qw/DBIx::Class::Core/;
1097 __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
1099 __PACKAGE__->add_columns(qw/ artist_id name /);
1101 __PACKAGE__->set_primary_key('artist_id');
1102 __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
1103 1;
1105 Whatever string you specify there will be used to build the "FROM"
1107 clause in SQL queries.
1108 The big drawback to this is you now have DB schema names hardcoded in
1110 your class files. This becomes especially troublesome if you have
1111 multiple instances of your application to support a change lifecycle
1112 (e.g. DEV, TEST, PROD) and the DB schemas are named based on the
1113 environment (e.g. database1_dev).
1114 However, one can dynamically "map" to the proper DB schema by
1116 overriding the connection method in your Schema class and building a
1117 renaming facility, like so:
1118 package MyDatabase::Schema;
1120 use Moose;
1121 extends 'DBIx::Class::Schema';
1123 around connection => sub {
1125 my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ );
1126 my $postfix = delete $attr->{schema_name_postfix};
1128 $inner->(@_);
1130 if ( $postfix ) {
1132 $self->append_db_name($postfix);
1133 }
1134 };
1135 sub append_db_name {
1137 my ( $self, $postfix ) = @_;
1138 my @sources_with_db
1140 = grep
1141 { $_->name =~ /^\w+\./mx }
1142 map
1143 { $self->source($_) }
1144 $self->sources;
1145 foreach my $source (@sources_with_db) {
1147 my $name = $source->name;
1148 $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx;
1149 $source->name($name);
1151 }
1152 }
1153 1;
1155 By overridding the connection method and extracting a custom option
1157 from the provided \%attr hashref one can then simply iterate over all
1158 the Schema's ResultSources, renaming them as needed.
1159 To use this facility, simply add or modify the \%attr hashref that is
1161 passed to connection, as follows:
1162 my $schema
1164 = MyDatabase::Schema->connect(
1165 $dsn,
1166 $user,
1167 $pass,
1168 {
1169 schema_name_postfix => '_dev'
1170 # ... Other options as desired ...
1171 })
1172 Obviously, one could accomplish even more advanced mapping via a hash
1174 map or a callback routine.
1175
1177 As of version 0.04001, there is improved transaction support in
1178 DBIx::Class::Storage and DBIx::Class::Schema. Here is an example of
1179 the recommended way to use it:
1180 my $genus = $schema->resultset('Genus')->find(12);
1182 my $coderef2 = sub {
1184 $genus->extinct(1);
1185 $genus->update;
1186 };
1187 my $coderef1 = sub {
1189 $genus->add_to_species({ name => 'troglodyte' });
1190 $genus->wings(2);
1191 $genus->update;
1192 $schema->txn_do($coderef2); # Can have a nested transaction. Only the outer will actualy commit
1193 return $genus->species;
1194 };
1195 my $rs;
1197 eval {
1198 $rs = $schema->txn_do($coderef1);
1199 };
1200 if ($@) { # Transaction failed
1202 die "the sky is falling!" #
1203 if ($@ =~ /Rollback failed/); # Rollback failed
1204 deal_with_failed_transaction();
1206 }
1207 Nested transactions will work as expected. That is, only the outermost
1209 transaction will actually issue a commit to the $dbh, and a rollback at
1210 any level of any transaction will cause the entire nested transaction
1211 to fail.
1212 Nested transactions and auto-savepoints
1214 If savepoints are supported by your RDBMS, it is possible to achieve
1215 true nested transactions with minimal effort. To enable auto-savepoints
1216 via nested transactions, supply the "auto_savepoint = 1" connection
1217 attribute.
1218 Here is an example of true nested transactions. In the example, we
1220 start a big task which will create several rows. Generation of data for
1221 each row is a fragile operation and might fail. If we fail creating
1222 something, depending on the type of failure, we want to abort the whole
1223 task, or only skip the failed row.
1224 my $schema = MySchema->connect("dbi:Pg:dbname=my_db");
1226 # Start a transaction. Every database change from here on will only be
1228 # committed into the database if the eval block succeeds.
1229 eval {
1230 $schema->txn_do(sub {
1231 # SQL: BEGIN WORK;
1232 my $job = $schema->resultset('Job')->create({ name=> 'big job' });
1234 # SQL: INSERT INTO job ( name) VALUES ( 'big job' );
1235 for (1..10) {
1237 # Start a nested transaction, which in fact sets a savepoint.
1239 eval {
1240 $schema->txn_do(sub {
1241 # SQL: SAVEPOINT savepoint_0;
1242 my $thing = $schema->resultset('Thing')->create({ job=>$job->id });
1244 # SQL: INSERT INTO thing ( job) VALUES ( 1 );
1245 if (rand > 0.8) {
1247 # This will generate an error, thus setting $@
1248 $thing->update({force_fail=>'foo'});
1250 # SQL: UPDATE thing SET force_fail = 'foo'
1251 # WHERE ( id = 42 );
1252 }
1253 });
1254 };
1255 if ($@) {
1256 # SQL: ROLLBACK TO SAVEPOINT savepoint_0;
1257 # There was an error while creating a $thing. Depending on the error
1259 # we want to abort the whole transaction, or only rollback the
1260 # changes related to the creation of this $thing
1261 # Abort the whole job
1263 if ($@ =~ /horrible_problem/) {
1264 print "something horrible happend, aborting job!";
1265 die $@; # rethrow error
1266 }
1267 # Ignore this $thing, report the error, and continue with the
1269 # next $thing
1270 print "Cannot create thing: $@";
1271 }
1272 # There was no error, so save all changes since the last
1273 # savepoint.
1274 # SQL: RELEASE SAVEPOINT savepoint_0;
1276 }
1277 });
1278 };
1279 if ($@) {
1280 # There was an error while handling the $job. Rollback all changes
1281 # since the transaction started, including the already committed
1282 # ('released') savepoints. There will be neither a new $job nor any
1283 # $thing entry in the database.
1284 # SQL: ROLLBACK;
1286 print "ERROR: $@\n";
1288 }
1289 else {
1290 # There was no error while handling the $job. Commit all changes.
1291 # Only now other connections can see the newly created $job and
1292 # @things.
1293 # SQL: COMMIT;
1295 print "Ok\n";
1297 }
1298 In this example it might be hard to see where the rollbacks, releases
1300 and commits are happening, but it works just the same as for plain
1301 <txn_do>: If the "eval"-block around "txn_do" fails, a rollback is
1302 issued. If the "eval" succeeds, the transaction is committed (or the
1303 savepoint released).
1304 While you can get more fine-grained controll using "svp_begin",
1306 "svp_release" and "svp_rollback", it is strongly recommended to use
1307 "txn_do" with coderefs.
1308
1310 Creating Schemas From An Existing Database
1311 DBIx::Class::Schema::Loader will connect to a database and create a
1312 DBIx::Class::Schema and associated sources by examining the database.
1313 The recommend way of achieving this is to use the make_schema_at
1315 method:
1316 perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
1318 -e 'make_schema_at("My::Schema", { debug => 1 }, [ "dbi:Pg:dbname=foo","postgres" ])'
1319 This will create a tree of files rooted at "./lib/My/Schema/"
1321 containing source definitions for all the tables found in the "foo"
1322 database.
1323 Creating DDL SQL
1325 The following functionality requires you to have SQL::Translator (also
1326 known as "SQL Fairy") installed.
1327 To create a set of database-specific .sql files for the above schema:
1329 my $schema = My::Schema->connect($dsn);
1331 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
1332 '0.1',
1333 './dbscriptdir/'
1334 );
1335 By default this will create schema files in the current directory, for
1337 MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm.
1338 To create a new database using the schema:
1340 my $schema = My::Schema->connect($dsn);
1342 $schema->deploy({ add_drop_table => 1});
1343 To import created .sql files using the mysql client:
1345 mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql
1347 To create "ALTER TABLE" conversion scripts to update a database to a
1349 newer version of your schema at a later point, first set a new $VERSION
1350 in your Schema file, then:
1351 my $schema = My::Schema->connect($dsn);
1353 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
1354 '0.2',
1355 '/dbscriptdir/',
1356 '0.1'
1357 );
1358 This will produce new database-specific .sql files for the new version
1360 of the schema, plus scripts to convert from version 0.1 to 0.2. This
1361 requires that the files for 0.1 as created above are available in the
1362 given directory to diff against.
1363 Select from dual
1365 Dummy tables are needed by some databases to allow calling functions or
1366 expressions that aren't based on table content, for examples of how
1367 this applies to various database types, see:
1368 http://troels.arvin.dk/db/rdbms/#other-dummy_table
1369 <http://troels.arvin.dk/db/rdbms/#other-dummy_table>.
1370 Note: If you're using Oracles dual table don't ever do anything other
1372 than a select, if you CRUD on your dual table you *will* break your
1373 database.
1374 Make a table class as you would for any other table
1376 package MyAppDB::Dual;
1378 use strict;
1379 use warnings;
1380 use base 'DBIx::Class::Core';
1381 __PACKAGE__->table("Dual");
1382 __PACKAGE__->add_columns(
1383 "dummy",
1384 { data_type => "VARCHAR2", is_nullable => 0, size => 1 },
1385 );
1386 Once you've loaded your table class select from it using "select" and
1388 "as" instead of "columns"
1389 my $rs = $schema->resultset('Dual')->search(undef,
1391 { select => [ 'sydate' ],
1392 as => [ 'now' ]
1393 },
1394 );
1395 All you have to do now is be careful how you access your resultset, the
1397 below will not work because there is no column called 'now' in the Dual
1398 table class
1399 while (my $dual = $rs->next) {
1401 print $dual->now."\n";
1402 }
1403 # Can't locate object method "now" via package "MyAppDB::Dual" at headshot.pl line 23.
1404 You could of course use 'dummy' in "as" instead of 'now', or
1406 "add_columns" to your Dual class for whatever you wanted to select from
1407 dual, but that's just silly, instead use "get_column"
1408 while (my $dual = $rs->next) {
1410 print $dual->get_column('now')."\n";
1411 }
1412 Or use "cursor"
1414 my $cursor = $rs->cursor;
1416 while (my @vals = $cursor->next) {
1417 print $vals[0]."\n";
1418 }
1419 In case you're going to use this "trick" together with "deploy" in
1421 DBIx::Class::Schema or "create_ddl_dir" in DBIx::Class::Schema a table
1422 called "dual" will be created in your current schema. This would
1423 overlap "sys.dual" and you could not fetch "sysdate" or
1424 "sequence.nextval" anymore from dual. To avoid this problem, just tell
1425 SQL::Translator to not create table dual:
1426 my $sqlt_args = {
1428 add_drop_table => 1,
1429 parser_args => { sources => [ grep $_ ne 'Dual', schema->sources ] },
1430 };
1431 $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args );
1432 Or use DBIx::Class::ResultClass::HashRefInflator
1434 $rs->result_class('DBIx::Class::ResultClass::HashRefInflator');
1436 while ( my $dual = $rs->next ) {
1437 print $dual->{now}."\n";
1438 }
1439 Here are some example "select" conditions to illustrate the different
1441 syntax you could use for doing stuff like
1442 "oracles.heavily(nested(functions_can('take', 'lots'), OF), 'args')"
1443 # get a sequence value
1445 select => [ 'A_SEQ.nextval' ],
1446 # get create table sql
1448 select => [ { 'dbms_metadata.get_ddl' => [ "'TABLE'", "'ARTIST'" ]} ],
1449 # get a random num between 0 and 100
1451 select => [ { "trunc" => [ { "dbms_random.value" => [0,100] } ]} ],
1452 # what year is it?
1454 select => [ { 'extract' => [ \'year from sysdate' ] } ],
1455 # do some math
1457 select => [ {'round' => [{'cos' => [ \'180 * 3.14159265359/180' ]}]}],
1458 # which day of the week were you born on?
1460 select => [{'to_char' => [{'to_date' => [ "'25-DEC-1980'", "'dd-mon-yyyy'" ]}, "'day'"]}],
1461 # select 16 rows from dual
1463 select => [ "'hello'" ],
1464 as => [ 'world' ],
1465 group_by => [ 'cube( 1, 2, 3, 4 )' ],
1466 Adding Indexes And Functions To Your SQL
1468 Often you will want indexes on columns on your table to speed up
1469 searching. To do this, create a method called "sqlt_deploy_hook" in the
1470 relevant source class (refer to the advanced callback system if you
1471 wish to share a hook between multiple sources):
1472 package My::Schema::Result::Artist;
1474 __PACKAGE__->table('artist');
1476 __PACKAGE__->add_columns(id => { ... }, name => { ... })
1477 sub sqlt_deploy_hook {
1479 my ($self, $sqlt_table) = @_;
1480 $sqlt_table->add_index(name => 'idx_name', fields => ['name']);
1482 }
1483 1;
1485 Sometimes you might want to change the index depending on the type of
1487 the database for which SQL is being generated:
1488 my ($db_type = $sqlt_table->schema->translator->producer_type)
1490 =~ s/^SQL::Translator::Producer:://;
1491 You can also add hooks to the schema level to stop certain tables being
1493 created:
1494 package My::Schema;
1496 ...
1498 sub sqlt_deploy_hook {
1500 my ($self, $sqlt_schema) = @_;
1501 $sqlt_schema->drop_table('table_name');
1503 }
1504 You could also add views, procedures or triggers to the output using
1506 "add_view" in SQL::Translator::Schema, "add_procedure" in
1507 SQL::Translator::Schema or "add_trigger" in SQL::Translator::Schema.
1508 Schema versioning
1510 The following example shows simplistically how you might use
1511 DBIx::Class to deploy versioned schemas to your customers. The basic
1512 process is as follows:
1513 1. Create a DBIx::Class schema
1515 2. Save the schema
1517 3. Deploy to customers
1519 4. Modify schema to change functionality
1521 5. Deploy update to customers
1523 Create a DBIx::Class schema
1525 This can either be done manually, or generated from an existing
1527 database as described under "Creating Schemas From An Existing
1528 Database"
1529 Save the schema
1531 Call "create_ddl_dir" in DBIx::Class::Schema as above under "Creating
1533 DDL SQL".
1534 Deploy to customers
1536 There are several ways you could deploy your schema. These are probably
1538 beyond the scope of this recipe, but might include:
1539 1. Require customer to apply manually using their RDBMS.
1541 2. Package along with your app, making database dump/schema
1543 update/tests all part of your install.
1544 Modify the schema to change functionality
1546 As your application evolves, it may be necessary to modify your schema
1548 to change functionality. Once the changes are made to your schema in
1549 DBIx::Class, export the modified schema and the conversion scripts as
1550 in "Creating DDL SQL".
1551 Deploy update to customers
1553 Add the DBIx::Class::Schema::Versioned schema component to your Schema
1555 class. This will add a new table to your database called
1556 "dbix_class_schema_vesion" which will keep track of which version is
1557 installed and warn if the user tries to run a newer schema version than
1558 the database thinks it has.
1559 Alternatively, you can send the conversion SQL scripts to your
1561 customers as above.
1562 Setting quoting for the generated SQL
1564 If the database contains column names with spaces and/or reserved
1565 words, they need to be quoted in the SQL queries. This is done using:
1566 $schema->storage->sql_maker->quote_char([ qw/[ ]/] );
1568 $schema->storage->sql_maker->name_sep('.');
1569 The first sets the quote characters. Either a pair of matching
1571 brackets, or a """ or "'":
1572 $schema->storage->sql_maker->quote_char('"');
1574 Check the documentation of your database for the correct quote
1576 characters to use. "name_sep" needs to be set to allow the SQL
1577 generator to put the quotes the correct place.
1578 In most cases you should set these as part of the arguments passed to
1580 "connect" in DBIx::Class::Schema:
1581 my $schema = My::Schema->connect(
1583 'dbi:mysql:my_db',
1584 'db_user',
1585 'db_password',
1586 {
1587 quote_char => '"',
1588 name_sep => '.'
1589 }
1590 )
1591 In some cases, quoting will be required for all users of a schema. To
1593 enforce this, you can also overload the "connection" method for your
1594 schema class:
1595 sub connection {
1597 my $self = shift;
1598 my $rv = $self->next::method( @_ );
1599 $rv->storage->sql_maker->quote_char([ qw/[ ]/ ]);
1600 $rv->storage->sql_maker->name_sep('.');
1601 return $rv;
1602 }
1603 Setting limit dialect for SQL::Abstract::Limit
1605 In some cases, SQL::Abstract::Limit cannot determine the dialect of the
1606 remote SQL server by looking at the database handle. This is a common
1607 problem when using the DBD::JDBC, since the DBD-driver only know that
1608 in has a Java-driver available, not which JDBC driver the Java
1609 component has loaded. This specifically sets the limit_dialect to
1610 Microsoft SQL-server (See more names in SQL::Abstract::Limit
1611 -documentation.
1612 __PACKAGE__->storage->sql_maker->limit_dialect('mssql');
1614 The JDBC bridge is one way of getting access to a MSSQL server from a
1616 platform that Microsoft doesn't deliver native client libraries for.
1617 (e.g. Linux)
1618 The limit dialect can also be set at connect time by specifying a
1620 "limit_dialect" key in the final hash as shown above.
1621 Working with PostgreSQL array types
1623 You can also assign values to PostgreSQL array columns by passing array
1624 references in the "\%columns" ("\%vals") hashref of the "create" in
1625 DBIx::Class::ResultSet and "update" in DBIx::Class::Row family of
1626 methods:
1627 $resultset->create({
1629 numbers => [1, 2, 3]
1630 });
1631 $row->update(
1633 {
1634 numbers => [1, 2, 3]
1635 },
1636 {
1637 day => '2008-11-24'
1638 }
1639 );
1640 In conditions (e.g. "\%cond" in the "search" in DBIx::Class::ResultSet
1642 family of methods) you cannot directly use array references (since this
1643 is interpreted as a list of values to be "OR"ed), but you can use the
1644 following syntax to force passing them as bind values:
1645 $resultset->search(
1647 {
1648 numbers => \[ '= ?', [numbers => [1, 2, 3]] ]
1649 }
1650 );
1651 See "array_datatypes" in SQL::Abstract and "Literal SQL with
1653 placeholders and bind values (subqueries)" in SQL::Abstract for more
1654 explanation. Note that DBIx::Class sets "bindtype" in SQL::Abstract to
1655 "columns", so you must pass the bind values (the "[1, 2, 3]" arrayref
1656 in the above example) wrapped in arrayrefs together with the column
1657 name, like this: "[column_name => value]".
1658
1660 Easy migration from class-based to schema-based setup
1661 You want to start using the schema-based approach to DBIx::Class (see
1662 SchemaIntro.pod), but have an established class-based setup with lots
1663 of existing classes that you don't want to move by hand. Try this nifty
1664 script instead:
1665 use MyDB;
1667 use SQL::Translator;
1668 my $schema = MyDB->schema_instance;
1670 my $translator = SQL::Translator->new(
1672 debug => $debug || 0,
1673 trace => $trace || 0,
1674 no_comments => $no_comments || 0,
1675 show_warnings => $show_warnings || 0,
1676 add_drop_table => $add_drop_table || 0,
1677 validate => $validate || 0,
1678 parser_args => {
1679 'DBIx::Schema' => $schema,
1680 },
1681 producer_args => {
1682 'prefix' => 'My::Schema',
1683 },
1684 );
1685 $translator->parser('SQL::Translator::Parser::DBIx::Class');
1687 $translator->producer('SQL::Translator::Producer::DBIx::Class::File');
1688 my $output = $translator->translate(@args) or die
1690 "Error: " . $translator->error;
1691 print $output;
1693 You could use Module::Find to search for all subclasses in the MyDB::*
1695 namespace, which is currently left as an exercise for the reader.
1696
1698 DBIx::Class uses the Class::C3 package, which provides for redispatch
1699 of method calls, useful for things like default values and triggers.
1700 You have to use calls to "next::method" to overload methods. More
1701 information on using Class::C3 with DBIx::Class can be found in
1702 DBIx::Class::Manual::Component.
1703 Setting default values for a row
1705 It's as simple as overriding the "new" method. Note the use of
1706 "next::method".
1707 sub new {
1709 my ( $class, $attrs ) = @_;
1710 $attrs->{foo} = 'bar' unless defined $attrs->{foo};
1712 my $new = $class->next::method($attrs);
1714 return $new;
1716 }
1717 For more information about "next::method", look in the Class::C3
1719 documentation. See also DBIx::Class::Manual::Component for more ways to
1720 write your own base classes to do this.
1721 People looking for ways to do "triggers" with DBIx::Class are probably
1723 just looking for this.
1724 Changing one field whenever another changes
1726 For example, say that you have three columns, "id", "number", and
1727 "squared". You would like to make changes to "number" and have
1728 "squared" be automagically set to the value of "number" squared. You
1729 can accomplish this by overriding "store_column":
1730 sub store_column {
1732 my ( $self, $name, $value ) = @_;
1733 if ($name eq 'number') {
1734 $self->squared($value * $value);
1735 }
1736 $self->next::method($name, $value);
1737 }
1738 Note that the hard work is done by the call to "next::method", which
1740 redispatches your call to store_column in the superclass(es).
1741 Automatically creating related objects
1743 You might have a class "Artist" which has many "CD"s. Further, you
1744 want to create a "CD" object every time you insert an "Artist" object.
1745 You can accomplish this by overriding "insert" on your objects:
1746 sub insert {
1748 my ( $self, @args ) = @_;
1749 $self->next::method(@args);
1750 $self->cds->new({})->fill_from_artist($self)->insert;
1751 return $self;
1752 }
1753 where "fill_from_artist" is a method you specify in "CD" which sets
1755 values in "CD" based on the data in the "Artist" object you pass in.
1756 Wrapping/overloading a column accessor
1758 Problem:
1759 Say you have a table "Camera" and want to associate a description with
1761 each camera. For most cameras, you'll be able to generate the
1762 description from the other columns. However, in a few special cases you
1763 may want to associate a custom description with a camera.
1764 Solution:
1766 In your database schema, define a description field in the "Camera"
1768 table that can contain text and null values.
1769 In DBIC, we'll overload the column accessor to provide a sane default
1771 if no custom description is defined. The accessor will either return or
1772 generate the description, depending on whether the field is null or
1773 not.
1774 First, in your "Camera" schema class, define the description field as
1776 follows:
1777 __PACKAGE__->add_columns(description => { accessor => '_description' });
1779 Next, we'll define the accessor-wrapper subroutine:
1781 sub description {
1783 my $self = shift;
1784 # If there is an update to the column, we'll let the original accessor
1786 # deal with it.
1787 return $self->_description(@_) if @_;
1788 # Fetch the column value.
1790 my $description = $self->_description;
1791 # If there's something in the description field, then just return that.
1793 return $description if defined $description && length $descripton;
1794 # Otherwise, generate a description.
1796 return $self->generate_description;
1797 }
1798
1800 DBIx::Class objects with Data::Dumper
1801 Data::Dumper can be a very useful tool for debugging, but sometimes it
1802 can be hard to find the pertinent data in all the data it can generate.
1803 Specifically, if one naively tries to use it like so,
1804 use Data::Dumper;
1806 my $cd = $schema->resultset('CD')->find(1);
1808 print Dumper($cd);
1809 several pages worth of data from the CD object's schema and result
1811 source will be dumped to the screen. Since usually one is only
1812 interested in a few column values of the object, this is not very
1813 helpful.
1814 Luckily, it is possible to modify the data before Data::Dumper outputs
1816 it. Simply define a hook that Data::Dumper will call on the object
1817 before dumping it. For example,
1818 package My::DB::CD;
1820 sub _dumper_hook {
1822 $_[0] = bless {
1823 %{ $_[0] },
1824 result_source => undef,
1825 }, ref($_[0]);
1826 }
1827 [...]
1829 use Data::Dumper;
1831 local $Data::Dumper::Freezer = '_dumper_hook';
1833 my $cd = $schema->resultset('CD')->find(1);
1835 print Dumper($cd);
1836 # dumps $cd without its ResultSource
1837 If the structure of your schema is such that there is a common base
1839 class for all your table classes, simply put a method similar to
1840 "_dumper_hook" in the base class and set $Data::Dumper::Freezer to its
1841 name and Data::Dumper will automagically clean up your data before
1842 printing it. See "EXAMPLES" in Data::Dumper for more information.
1843 Profiling
1845 When you enable DBIx::Class::Storage's debugging it prints the SQL
1846 executed as well as notifications of query completion and transaction
1847 begin/commit. If you'd like to profile the SQL you can subclass the
1848 DBIx::Class::Storage::Statistics class and write your own profiling
1849 mechanism:
1850 package My::Profiler;
1852 use strict;
1853 use base 'DBIx::Class::Storage::Statistics';
1855 use Time::HiRes qw(time);
1857 my $start;
1859 sub query_start {
1861 my $self = shift();
1862 my $sql = shift();
1863 my $params = @_;
1864 $self->print("Executing $sql: ".join(', ', @params)."\n");
1866 $start = time();
1867 }
1868 sub query_end {
1870 my $self = shift();
1871 my $sql = shift();
1872 my @params = @_;
1873 my $elapsed = sprintf("%0.4f", time() - $start);
1875 $self->print("Execution took $elapsed seconds.\n");
1876 $start = undef;
1877 }
1878 1;
1880 You can then install that class as the debugging object:
1882 __PACKAGE__->storage->debugobj(new My::Profiler());
1884 __PACKAGE__->storage->debug(1);
1885 A more complicated example might involve storing each execution of SQL
1887 in an array:
1888 sub query_end {
1890 my $self = shift();
1891 my $sql = shift();
1892 my @params = @_;
1893 my $elapsed = time() - $start;
1895 push(@{ $calls{$sql} }, {
1896 params => \@params,
1897 elapsed => $elapsed
1898 });
1899 }
1900 You could then create average, high and low execution times for an SQL
1902 statement and dig down to see if certain parameters cause aberrant
1903 behavior. You might want to check out DBIx::Class::QueryLog as well.
1904
1906 DBIx::Class programs can have a significant startup delay as the ORM
1907 loads all the relevant classes. This section examines techniques for
1908 reducing the startup delay.
1909 These tips are are listed in order of decreasing effectiveness - so the
1911 first tip, if applicable, should have the greatest effect on your
1912 application.
1913 Statically Define Your Schema
1915 If you are using DBIx::Class::Schema::Loader to build the classes
1916 dynamically based on the database schema then there will be a
1917 significant startup delay.
1918 For production use a statically defined schema (which can be generated
1920 using DBIx::Class::Schema::Loader to dump the database schema once -
1921 see make_schema_at and dump_directory for more details on creating
1922 static schemas from a database).
1923 Move Common Startup into a Base Class
1925 Typically DBIx::Class result classes start off with
1926 use base qw/DBIx::Class::Core/;
1928 __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
1929 If this preamble is moved into a common base class:-
1931 package MyDBICbase;
1933 use base qw/DBIx::Class::Core/;
1935 __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
1936 1;
1937 and each result class then uses this as a base:-
1939 use base qw/MyDBICbase/;
1941 then the load_components is only performed once, which can result in a
1943 considerable startup speedup for schemas with many classes.
1944 Explicitly List Schema Result Classes
1946 The schema class will normally contain
1947 __PACKAGE__->load_classes();
1949 to load the result classes. This will use Module::Find to find and load
1951 the appropriate modules. Explicitly defining the classes you wish to
1952 load will remove the overhead of Module::Find and the related directory
1953 operations:
1954 __PACKAGE__->load_classes(qw/ CD Artist Track /);
1956 If you are instead using the load_namespaces syntax to load the
1958 appropriate classes there is not a direct alternative avoiding
1959 Module::Find.
1960
1962 Cached statements
1963 DBIx::Class normally caches all statements with prepare_cached(). This
1964 is normally a good idea, but if too many statements are cached, the
1965 database may use too much memory and may eventually run out and fail
1966 entirely. If you suspect this may be the case, you may want to examine
1967 DBI's CachedKids hash:
1968 # print all currently cached prepared statements
1970 print for keys %{$schema->storage->dbh->{CachedKids}};
1971 # get a count of currently cached prepared statements
1972 my $count = scalar keys %{$schema->storage->dbh->{CachedKids}};
1973 If it's appropriate, you can simply clear these statements,
1975 automatically deallocating them in the database:
1976 my $kids = $schema->storage->dbh->{CachedKids};
1978 delete @{$kids}{keys %$kids} if scalar keys %$kids > 100;
1979 But what you probably want is to expire unused statements and not those
1981 that are used frequently. You can accomplish this with Tie::Cache or
1982 Tie::Cache::LRU:
1983 use Tie::Cache;
1985 use DB::Main;
1986 my $schema = DB::Main->connect($dbi_dsn, $user, $pass, {
1987 on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 },
1988 });
1989perl v5.12.0 2010-05-12 DBIx::Class::Manual::Cookbook(3)