1Class::DBI(3) User Contributed Perl Documentation Class::DBI(3)
2
6 Class::DBI - Simple Database Abstraction
7
9 package Music::DBI;
10 use base 'Class::DBI';
11 Music::DBI->connection('dbi:mysql:dbname', 'username', 'password');
12 package Music::Artist;
14 use base 'Music::DBI';
15 Music::Artist->table('artist');
16 Music::Artist->columns(All => qw/artistid name/);
17 Music::Artist->has_many(cds => 'Music::CD');
18 package Music::CD;
20 use base 'Music::DBI';
21 Music::CD->table('cd');
22 Music::CD->columns(All => qw/cdid artist title year reldate/);
23 Music::CD->has_many(tracks => 'Music::Track');
24 Music::CD->has_a(artist => 'Music::Artist');
25 Music::CD->has_a(reldate => 'Time::Piece',
26 inflate => sub { Time::Piece->strptime(shift, "%Y-%m-%d") },
27 deflate => 'ymd',
28 );
29 Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);
31 package Music::Track;
33 use base 'Music::DBI';
34 Music::Track->table('track');
35 Music::Track->columns(All => qw/trackid cd position title/);
36 #-- Meanwhile, in a nearby piece of code! --#
38 my $artist = Music::Artist->insert({ artistid => 1, name => 'U2' });
40 my $cd = $artist->add_to_cds({
42 cdid => 1,
43 title => 'October',
44 year => 1980,
45 });
46 # Oops, got it wrong.
48 $cd->year(1981);
49 $cd->update;
50 # etc.
52 foreach my $track ($cd->tracks) {
54 print $track->position, $track->title
55 }
56 $cd->delete; # also deletes the tracks
58 my $cd = Music::CD->retrieve(1);
60 my @cds = Music::CD->retrieve_all;
61 my @cds = Music::CD->search(year => 1980);
62 my @cds = Music::CD->search_like(title => 'October%');
63
65 Class::DBI provides a convenient abstraction layer to a database.
66 It not only provides a simple database to object mapping layer, but can
68 be used to implement several higher order database functions (triggers,
69 referential integrity, cascading delete etc.), at the application
70 level, rather than at the database.
71 This is particularly useful when using a database which doesn't support
73 these (such as MySQL), or when you would like your code to be portable
74 across multiple databases which might implement these things in differ‐
75 ent ways.
76 In short, Class::DBI aims to make it simple to introduce 'best prac‐
78 tice' when dealing with data stored in a relational database.
79 How to set it up
81 Set up a database.
83 You must have an existing database set up, have DBI.pm installed
84 and the necessary DBD:: driver module for that database. See DBI
85 and the documentation of your particular database and driver for
86 details.
87 Set up a table for your objects to be stored in.
89 Class::DBI works on a simple one class/one table model. It is your
90 responsibility to have your database tables already set up.
91 Automating that process is outside the scope of Class::DBI.
92 Using our CD example, you might declare a table something like
94 this:
95 CREATE TABLE cd (
97 cdid INTEGER PRIMARY KEY,
98 artist INTEGER, # references 'artist'
99 title VARCHAR(255),
100 year CHAR(4),
101 );
102 Set up an application base class
104 It's usually wise to set up a "top level" class for your entire
105 application to inherit from, rather than have each class inherit
106 directly from Class::DBI. This gives you a convenient point to
107 place system-wide overrides and enhancements to Class::DBI's behav‐
108 ior.
109 package Music::DBI;
111 use base 'Class::DBI';
112 Give it a database connection
114 Class::DBI needs to know how to access the database. It does this
115 through a DBI connection which you set up by calling the connec‐
116 tion() method.
117 Music::DBI->connection('dbi:mysql:dbname', 'user', 'password');
119 By setting the connection up in your application base class all the
121 table classes that inherit from it will share the same connection.
122 Set up each Class
124 package Music::CD;
125 use base 'Music::DBI';
126 Each class will inherit from your application base class, so you
128 don't need to repeat the information on how to connect to the data‐
129 base.
130 Declare the name of your table
132 Inform Class::DBI what table you are using for this class:
133 Music::CD->table('cd');
135 Declare your columns.
137 This is done using the columns() method. In the simplest form, you
138 tell it the name of all your columns (with the single primary key
139 first):
140 Music::CD->columns(All => qw/cdid artist title year/);
142 If the primary key of your table spans multiple columns then
144 declare them using a separate call to columns() like this:
145 Music::CD->columns(Primary => qw/pk1 pk2/);
147 Music::CD->columns(Others => qw/foo bar baz/);
148 For more information about how you can more efficiently use subsets
150 of your columns, see "LAZY POPULATION"
151 Done.
153 That's it! You now have a class with methods to "insert",
154 "retrieve", "search" for, "update" and "delete" objects from your
155 table, as well as accessors and mutators for each of the columns in
156 that object (row).
157 Let's look at all that in more detail:
159
161 connection
162 __PACKAGE__->connection($data_source, $user, $password, \%attr);
164 This sets up a database connection with the given information.
166 This uses Ima::DBI to set up an inheritable connection (named Main). It
168 is therefore usual to only set up a connection() in your application
169 base class and let the 'table' classes inherit from it.
170 package Music::DBI;
172 use base 'Class::DBI';
173 Music::DBI->connection('dbi:foo:dbname', 'user', 'password');
175 package My::Other::Table;
177 use base 'Music::DBI';
178 Class::DBI helps you along a bit to set up the database connection.
180 connection() provides its own default attributes depending on the
181 driver name in the data_source parameter. The connection() method pro‐
182 vides defaults for these attributes:
183 FetchHashKeyName => 'NAME_lc',
185 ShowErrorStatement => 1,
186 ChopBlanks => 1,
187 AutoCommit => 1,
188 (Except for Oracle and Pg, where AutoCommit defaults 0, placing the
190 database in transactional mode).
191 The defaults can always be extended (or overridden if you know what
193 you're doing) by supplying your own \%attr parameter. For example:
194 Music::DBI->connection(dbi:foo:dbname','user','pass',{ChopBlanks=>0});
196 The RootClass of DBIx::ContextualFetch in also inherited from Ima::DBI,
198 and you should be very careful not to change this unless you know what
199 you're doing!
200 Dynamic Database Connections / db_Main
202 It is sometimes desirable to generate your database connection informa‐
204 tion dynamically, for example, to allow multiple databases with the
205 same schema to not have to duplicate an entire class hierarchy.
206 The preferred method for doing this is to supply your own db_Main()
208 method rather than calling "connection". This method should return a
209 valid database handle, and should ensure it sets the standard
210 attributes described above, preferably by combining
211 $class->_default_attributes() with your own. Note, this handle *must*
212 have its RootClass set to DBIx::ContextualFetch, so it is usually not
213 possible to just supply a $dbh obtained elsewhere.
214 Note that connection information is class data, and that changing it at
216 run time may have unexpected behaviour for instances of the class
217 already in existence.
218 table
220 __PACKAGE__->table($table);
222 $table = Class->table;
224 $table = $obj->table;
225 An accessor to get/set the name of the database table in which this
227 class is stored. It -must- be set.
228 Table information is inherited by subclasses, but can be overridden.
230 table_alias
232 package Shop::Order;
234 __PACKAGE__->table('orders');
235 __PACKAGE__->table_alias('orders');
236 When Class::DBI constructs SQL, it aliases your table name to a name
238 representing your class. However, if your class's name is an SQL
239 reserved word (such as 'Order') this will cause SQL errors. In such
240 cases you should supply your own alias for your table name (which can,
241 of course, be the same as the actual table name).
242 This can also be passed as a second argument to 'table':
244 __PACKAGE__->table('orders', 'orders');
246 As with table, this is inherited but can be overridden.
248 sequence / auto_increment
250 __PACKAGE__->sequence($sequence_name);
252 $sequence_name = Class->sequence;
254 $sequence_name = $obj->sequence;
255 If you are using a database which supports sequences and you want to
257 use a sequence to automatically supply values for the primary key of a
258 table, then you should declare this using the sequence() method:
259 __PACKAGE__->columns(Primary => 'id');
261 __PACKAGE__->sequence('class_id_seq');
262 Class::DBI will use the sequence to generate a primary key value when
264 objects are inserted without one.
265 *NOTE* This method does not work for Oracle. However, Class::DBI::Ora‐
267 cle (which can be downloaded separately from CPAN) provides a suitable
268 replacement sequence() method.
269 If you are using a database with AUTO_INCREMENT (e.g. MySQL) then you
271 do not need this, and any call to insert() without a primary key speci‐
272 fied will fill this in automagically.
273 Sequence and auto-increment mechanisms only apply to tables that have a
275 single column primary key. For tables with multi-column primary keys
276 you need to supply the key values manually.
277
279 The following are methods provided for convenience to insert, retrieve
280 and delete stored objects. It's not entirely one-size fits all and you
281 might find it necessary to override them.
282 insert
284 my $obj = Class->insert(\%data);
286 This is a constructor to insert new data into the database and create
288 an object representing the newly inserted row.
289 %data consists of the initial information to place in your object and
291 the database. The keys of %data match up with the columns of your
292 objects and the values are the initial settings of those fields.
293 my $cd = Music::CD->insert({
295 cdid => 1,
296 artist => $artist,
297 title => 'October',
298 year => 1980,
299 });
300 If the table has a single primary key column and that column value is
302 not defined in %data, insert() will assume it is to be generated. If a
303 sequence() has been specified for this Class, it will use that. Other‐
304 wise, it will assume the primary key can be generated by AUTO_INCREMENT
305 and attempt to use that.
306 The "before_create" trigger is invoked directly after storing the sup‐
308 plied values into the new object and before inserting the record into
309 the database. The object stored in $self may not have all the function‐
310 ality of the final object after_creation, particularly if the database
311 is going to be providing the primary key value.
312 For tables with multi-column primary keys you need to supply all the
314 key values, either in the arguments to the insert() method, or by set‐
315 ting the values in a "before_create" trigger.
316 If the class has declared relationships with foreign classes via
318 has_a(), you can pass an object to insert() for the value of that key.
319 Class::DBI will Do The Right Thing.
320 After the new record has been inserted into the database the data for
322 non-primary key columns is discarded from the object. If those columns
323 are accessed again they'll simply be fetched as needed. This ensures
324 that the data in the application is consistent with what the database
325 actually stored.
326 The "after_create" trigger is invoked after the database insert has
328 executed.
329 find_or_create
331 my $cd = Music::CD->find_or_create({ artist => 'U2', title => 'Boy' });
333 This checks if a CD can be found to match the information passed, and
335 if not inserts it.
336 delete
338 $obj->delete;
340 Music::CD->search(year => 1980, title => 'Greatest %')->delete_all;
341 Deletes this object from the database and from memory. If you have set
343 up any relationships using "has_many" or "might_have", this will delete
344 the foreign elements also, recursively (cascading delete). $obj is no
345 longer usable after this call.
346 Multiple objects can be deleted by calling delete_all on the Iterator
348 returned from a search. Each object found will be deleted in turn, so
349 cascading delete and other triggers will be honoured.
350 The "before_delete" trigger is when an object instance is about to be
352 deleted. It is invoked before any cascaded deletes. The "after_delete"
353 trigger is invoked after the record has been deleted from the database
354 and just before the contents in memory are discarded.
355
357 Class::DBI provides a few very simple search methods.
358 It is not the goal of Class::DBI to replace the need for using SQL.
360 Users are expected to write their own searches for more complex cases.
361 Class::DBI::AbstractSearch, available on CPAN, provides a much more
363 complex search interface than Class::DBI provides itself.
364 retrieve
366 $obj = Class->retrieve( $id );
368 $obj = Class->retrieve( %key_values );
369 Given key values it will retrieve the object with that key from the
371 database. For tables with a single column primary key a single parame‐
372 ter can be used, otherwise a hash of key-name key-value pairs must be
373 given.
374 my $cd = Music::CD->retrieve(1) or die "No such cd";
376 retrieve_all
378 my @objs = Class->retrieve_all;
380 my $iterator = Class->retrieve_all;
381 Retrieves objects for all rows in the database. This is probably a bad
383 idea if your table is big, unless you use the iterator version.
384 search
386 @objs = Class->search(column1 => $value, column2 => $value ...);
388 This is a simple search for all objects where the columns specified are
390 equal to the values specified e.g.:
391 @cds = Music::CD->search(year => 1990);
393 @cds = Music::CD->search(title => "Greatest Hits", year => 1990);
394 You may also specify the sort order of the results by adding a final
396 hash of arguments with the key 'order_by':
397 @cds = Music::CD->search(year => 1990, { order_by=>'artist' });
399 This is passed through 'as is', enabling order_by clauses such as 'year
401 DESC, title'.
402 search_like
404 @objs = Class->search_like(column1 => $like_pattern, ....);
406 This is a simple search for all objects where the columns specified are
408 like the values specified. $like_pattern is a pattern given in SQL
409 LIKE predicate syntax. '%' means "any zero or more characters", '_'
410 means "any single character".
411 @cds = Music::CD->search_like(title => 'October%');
413 @cds = Music::CD->search_like(title => 'Hits%', artist => 'Various%');
414 You can also use 'order_by' with these, as with search().
416
418 my $it = Music::CD->search_like(title => 'October%');
419 while (my $cd = $it->next) {
420 print $cd->title;
421 }
422 Any of the above searches (as well as those defined by has_many) can
424 also be used as an iterator. Rather than creating a list of objects
425 matching your criteria, this will return a Class::DBI::Iterator
426 instance, which can return the objects required one at a time.
427 Currently the iterator initially fetches all the matching row data into
429 memory, and defers only the creation of the objects from that data
430 until the iterator is asked for the next object. So using an iterator
431 will only save significant memory if your objects will inflate substan‐
432 tially when used.
433 In the case of has_many relationships with a mapping method, the map‐
435 ping method is not called until each time you call 'next'. This means
436 that if your mapping is not a one-to-one, the results will probably not
437 be what you expect.
438 Subclassing the Iterator
440 Music::CD->iterator_class('Music::CD::Iterator');
442 You can also subclass the default iterator class to override its func‐
444 tionality. This is done via class data, and so is inherited into your
445 subclasses.
446 QUICK RETRIEVAL
448 my $obj = Class->construct(\%data);
450 This is used to turn data from the database into objects, and should
452 thus only be used when writing constructors. It is very handy for
453 cheaply setting up lots of objects from data for without going back to
454 the database.
455 For example, instead of doing one SELECT to get a bunch of IDs and then
457 feeding those individually to retrieve() (and thus doing more SELECT
458 calls), you can do one SELECT to get the essential data of many objects
459 and feed that data to construct():
460 return map $class->construct($_), $sth->fetchall_hash;
462 The construct() method creates a new empty object, loads in the column
464 values, and then invokes the "select" trigger.
465
467 copy
468 $new_obj = $obj->copy;
470 $new_obj = $obj->copy($new_id);
471 $new_obj = $obj->copy({ title => 'new_title', rating => 18 });
472 This creates a copy of the given $obj, removes the primary key, sets
474 any supplied column values and calls insert() to make a new record in
475 the database.
476 For tables with a single column primary key, copy() can be called with
478 no parameters and the new object will be assigned a key automatically.
479 Or a single parameter can be supplied and will be used as the new key.
480 For tables with a multi-column primary key, copy() must be called with
482 parameters which supply new values for all primary key columns, unless
483 a "before_create" trigger will supply them. The insert() method will
484 fail if any primary key columns are not defined.
485 my $blrunner_dc = $blrunner->copy("Bladerunner: Director's Cut");
487 my $blrunner_unrated = $blrunner->copy({
488 Title => "Bladerunner: Director's Cut",
489 Rating => 'Unrated',
490 });
491 move
493 my $new_obj = Sub::Class->move($old_obj);
495 my $new_obj = Sub::Class->move($old_obj, $new_id);
496 my $new_obj = Sub::Class->move($old_obj, \%changes);
497 For transferring objects from one class to another. Similar to copy(),
499 an instance of Sub::Class is inserted using the data in $old_obj
500 (Sub::Class is a subclass of $old_obj's subclass). Like copy(), you can
501 supply $new_id as the primary key of $new_obj (otherwise the usual
502 sequence or autoincrement is used), or a hashref of multiple new val‐
503 ues.
504
506 __PACKAGE__->add_trigger(trigger_point_name => \&code_to_execute);
507 # e.g.
509 __PACKAGE__->add_trigger(after_create => \&call_after_create);
511 It is possible to set up triggers that will be called at various points
513 in the life of an object. Valid trigger points are:
514 before_create (also used for deflation)
516 after_create
517 before_set_$column (also used by add_constraint)
518 after_set_$column (also used for inflation and by has_a)
519 before_update (also used for deflation and by might_have)
520 after_update
521 before_delete
522 after_delete
523 select (also used for inflation and by construct and _flesh)
524 You can create any number of triggers for each point, but you cannot
526 specify the order in which they will be run.
527 All triggers are passed the object they are being fired for, except
529 when "before_set_$column" is fired during "insert", in which case the
530 class is passed in place of the object, which does not yet exist. You
531 may change object values if required.
532 Some triggers are also passed extra parameters as name-value pairs. The
534 individual triggers are further documented with the methods that trig‐
535 ger them.
536
538 __PACKAGE__->add_constraint('name', column => \&check_sub);
539 # e.g.
541 __PACKAGE__->add_constraint('over18', age => \&check_age);
543 # Simple version
545 sub check_age {
546 my ($value) = @_;
547 return $value >= 18;
548 }
549 # Cross-field checking - must have SSN if age < 18
551 sub check_age {
552 my ($value, $self, $column_name, $changing) = @_;
553 return 1 if $value >= 18; # We're old enough.
554 return 1 if $changing->{SSN}; # We're also being given an SSN
555 return 0 if !ref($self); # This is an insert, so we can't have an SSN
556 return 1 if $self->ssn; # We already have one in the database
557 return 0; # We can't find an SSN anywhere
558 }
559 It is also possible to set up constraints on the values that can be set
561 on a column. The constraint on a column is triggered whenever an object
562 is created and whenever the value in that column is being changed.
563 The constraint code is called with four parameters:
565 - The new value to be assigned
567 - The object it will be assigned to
568 (or class name when initially creating an object)
569 - The name of the column
570 (useful if many constraints share the same code)
571 - A hash ref of all new column values being assigned
572 (useful for cross-field validation)
573 The constraints are applied to all the columns being set before the
575 object data is changed. Attempting to create or modify an object where
576 one or more constraint fail results in an exception and the object
577 remains unchanged.
578 The exception thrown has its data set to a hashref of the column being
580 changed and the value being changed to.
581 Note 1: Constraints are implemented using before_set_$column triggers.
583 This will only prevent you from setting these values through a the pro‐
584 vided insert() or set() methods. It will always be possible to bypass
585 this if you try hard enough.
586 Note 2: When an object is created constraints are currently only
588 checked for column names included in the parameters to insert(). This
589 is probably a bug and is likely to change in future.
590 constrain_column
592 Film->constrain_column(year => qr/^\d{4}$/);
594 Film->constrain_column(rating => [qw/U Uc PG 12 15 18/]);
595 Film->constrain_column(title => sub { length() <= 20 });
596 Simple anonymous constraints can also be added to a column using the
598 constrain_column() method. By default this takes either a regex which
599 must match, a reference to a list of possible values, or a subref which
600 will have $_ aliased to the value being set, and should return a true
601 or false value.
602 However, this behaviour can be extended (or replaced) by providing a
604 constraint handler for the type of argument passed to constrain_column.
605 This behavior should be provided in a method named "_con‐
606 strain_by_$type", where $type is the moniker of the argument. For exam‐
607 ple, the year example above could be provided by _constrain_by_array().
608
610 Before an object is assigned data from the application (via insert or a
611 set accessor) the normalize_column_values() method is called with a
612 reference to a hash containing the column names and the new values
613 which are to be assigned (after any validation and constraint checking,
614 as described below).
615 Currently Class::DBI does not offer any per-column mechanism here. The
617 default method is empty. You can override it in your own classes to
618 normalize (edit) the data in any way you need. For example the values
619 in the hash for certain columns could be made lowercase.
620 The method is called as an instance method when the values of an exist‐
622 ing object are being changed, and as a class method when a new object
623 is being created.
624
626 Before an object is assigned data from the application (via insert or a
627 set accessor) the validate_column_values() method is called with a ref‐
628 erence to a hash containing the column names and the new values which
629 are to be assigned.
630 The method is called as an instance method when the values of an exist‐
632 ing object are being changed, and as a class method when a new object
633 is being inserted.
634 The default method calls the before_set_$column trigger for each column
636 name in the hash. Each trigger is called inside an eval. Any failures
637 result in an exception after all have been checked. The exception data
638 is a reference to a hash which holds the column name and error text for
639 each trigger error.
640 When using this mechanism for form data validation, for example, this
642 exception data can be stored in an exception object, via a custom
643 _croak() method, and then caught and used to redisplay the form with
644 error messages next to each field which failed validation.
645
647 All errors that are generated, or caught and propagated, by Class::DBI
648 are handled by calling the _croak() method (as an instance method if
649 possible, or else as a class method).
650 The _croak() method is passed an error message and in some cases some
652 extra information as described below. The default behaviour is simply
653 to call Carp::croak($message).
654 Applications that require custom behaviour should override the _croak()
656 method in their application base class (or table classes for table-spe‐
657 cific behaviour). For example:
658 use Error;
660 sub _croak {
662 my ($self, $message, %info) = @_;
663 # convert errors into exception objects
664 # except for duplicate insert errors which we'll ignore
665 Error->throw(-text => $message, %info)
666 unless $message =~ /^Can't insert .* duplicate/;
667 return;
668 }
669 The _croak() method is expected to trigger an exception and not return.
671 If it does return then it should use "return;" so that an undef or
672 empty list is returned as required depending on the calling context.
673 You should only return other values if you are prepared to deal with
674 the (unsupported) consequences.
675 For exceptions that are caught and propagated by Class::DBI, $message
677 includes the text of $@ and the original $@ value is available in
678 $info{err}. That allows you to correctly propagate exception objects
679 that may have been thrown 'below' Class::DBI (using Excep‐
680 tion::Class::DBI for example).
681 Exceptions generated by some methods may provide additional data in
683 $info{data} and, if so, also store the method name in $info{method}.
684 For example, the validate_column_values() method stores details of
685 failed validations in $info{data}. See individual method documentation
686 for what additional data they may store, if any.
687
689 All warnings are handled by calling the _carp() method (as an instance
690 method if possible, or else as a class method). The default behaviour
691 is simply to call Carp::carp().
692
694 accessors
695 Class::DBI inherits from Class::Accessor and thus provides individual
697 accessor methods for every column in your subclass. It also overrides
698 the get() and set() methods provided by Accessor to automagically han‐
699 dle database reading and writing. (Note that as it doesn't make sense
700 to store a list of values in a column, set() takes a hash of column =>
701 value pairs, rather than the single key => values of Class::Accessor).
702 the fundamental set() and get() methods
704 $value = $obj->get($column_name);
706 @values = $obj->get(@column_names);
707 $obj->set($column_name => $value);
709 $obj->set($col1 => $value1, $col2 => $value2 ... );
710 These methods are the fundamental entry points for getting and setting
712 column values. The extra accessor methods automatically generated for
713 each column of your table are simple wrappers that call these get() and
714 set() methods.
715 The set() method calls normalize_column_values() then validate_col‐
717 umn_values() before storing the values. The "before_set_$column" trig‐
718 ger is invoked by validate_column_values(), checking any constraints
719 that may have been set up.
720 The "after_set_$column" trigger is invoked after the new value has been
722 stored.
723 It is possible for an object to not have all its column data in memory
725 (due to lazy inflation). If the get() method is called for such a col‐
726 umn then it will select the corresponding group of columns and then
727 invoke the "select" trigger.
728
730 accessor_name_for / mutator_name_for
731 It is possible to change the name of the accessor method created for a
733 column either declaratively or programmatically.
734 If, for example, you have a column with a name that clashes with a
736 method otherwise created by Class::DBI, such as 'meta_info', you could
737 create that Column explicitly with a different accessor (and/or muta‐
738 tor) when setting up your columns:
739 my $meta_col = Class::DBI::Column->new(meta_info => {
741 accessor => 'metadata',
742 });
743 __PACKAGE__->columns(All => qw/id name/, $meta_col);
745 If you want to change the name of all your accessors, or all that match
747 a certain pattern, you need to provide an accessor_name_for($col)
748 method, which will convert a column name to a method name.
749 e.g: if your local database naming convention was to prepend the word
751 'customer' to each column in the 'customer' table, so that you had the
752 columns 'customerid', 'customername' and 'customerage', but you wanted
753 your methods to just be $customer->name and $customer->age rather than
754 $customer->customername etc., you could create a
755 sub accessor_name_for {
757 my ($class, $column) = @_;
758 $column =~ s/^customer//;
759 return $column;
760 }
761 Similarly, if you wanted to have distinct accessor and mutator methods,
763 you could provide a mutator_name_for($col) method which would return
764 the name of the method to change the value:
765 sub mutator_name_for {
767 my ($class, $column) = @_;
768 return "set_" . $column->accessor;
769 }
770 If you override the mutator name, then the accessor method will be
772 enforced as read-only, and the mutator as write-only.
773 update vs auto update
775 There are two modes for the accessors to work in: manual update and
777 autoupdate. When in autoupdate mode, every time one calls an accessor
778 to make a change an UPDATE will immediately be sent to the database.
779 Otherwise, if autoupdate is off, no changes will be written until
780 update() is explicitly called.
781 This is an example of manual updating:
783 # The calls to NumExplodingSheep() and Rating() will only make the
785 # changes in memory, not in the database. Once update() is called
786 # it writes to the database in one swell foop.
787 $gone->NumExplodingSheep(5);
788 $gone->Rating('NC-17');
789 $gone->update;
790 And of autoupdating:
792 # Turn autoupdating on for this object.
794 $gone->autoupdate(1);
795 # Each accessor call causes the new value to immediately be written.
797 $gone->NumExplodingSheep(5);
798 $gone->Rating('NC-17');
799 Manual updating is probably more efficient than autoupdating and it
801 provides the extra safety of a discard_changes() option to clear out
802 all unsaved changes. Autoupdating can be more convenient for the pro‐
803 grammer. Autoupdating is off by default.
804 If changes are neither updated nor rolled back when the object is
806 destroyed (falls out of scope or the program ends) then Class::DBI's
807 DESTROY method will print a warning about unsaved changes.
808 autoupdate
810 __PACKAGE__->autoupdate($on_or_off);
812 $update_style = Class->autoupdate;
813 $obj->autoupdate($on_or_off);
815 $update_style = $obj->autoupdate;
816 This is an accessor to the current style of auto-updating. When called
818 with no arguments it returns the current auto-updating state, true for
819 on, false for off. When given an argument it turns auto-updating on
820 and off: a true value turns it on, a false one off.
821 When called as a class method it will control the updating style for
823 every instance of the class. When called on an individual object it
824 will control updating for just that object, overriding the choice for
825 the class.
826 __PACKAGE__->autoupdate(1); # Autoupdate is now on for the class.
828 $obj = Class->retrieve('Aliens Cut My Hair');
830 $obj->autoupdate(0); # Shut off autoupdating for this object.
831 The update setting for an object is not stored in the database.
833 update
835 $obj->update;
837 If "autoupdate" is not enabled then changes you make to your object are
839 not reflected in the database until you call update(). It is harmless
840 to call update() if there are no changes to be saved. (If autoupdate
841 is on there'll never be anything to save.)
842 Note: If you have transactions turned on for your database (but see
844 "TRANSACTIONS" below) you will also need to call dbi_commit(), as
845 update() merely issues the UPDATE to the database).
846 After the database update has been executed, the data for columns that
848 have been updated are deleted from the object. If those columns are
849 accessed again they'll simply be fetched as needed. This ensures that
850 the data in the application is consistent with what the database actu‐
851 ally stored.
852 When update() is called the "before_update"($self) trigger is always
854 invoked immediately.
855 If any columns have been updated then the "after_update" trigger is
857 invoked after the database update has executed and is passed:
858 ($self, discard_columns => \@discard_columns)
859 The trigger code can modify the discard_columns array to affect which
861 columns are discarded.
862 For example:
864 Class->add_trigger(after_update => sub {
866 my ($self, %args) = @_;
867 my $discard_columns = $args{discard_columns};
868 # discard the md5_hash column if any field starting with 'foo'
869 # has been updated - because the md5_hash will have been changed
870 # by a trigger.
871 push @$discard_columns, 'md5_hash' if grep { /^foo/ } @$discard_columns;
872 });
873 Take care to not delete a primary key column unless you know what
875 you're doing.
876 The update() method returns the number of rows updated. If the object
878 had not changed and thus did not need to issue an UPDATE statement, the
879 update() call will have a return value of -1.
880 If the record in the database has been deleted, or its primary key
882 value changed, then the update will not affect any records and so the
883 update() method will return 0.
884 discard_changes
886 $obj->discard_changes;
888 Removes any changes you've made to this object since the last update.
890 Currently this simply discards the column values from the object.
891 If you're using autoupdate this method will throw an exception.
893 is_changed
895 my $changed = $obj->is_changed;
897 my @changed_keys = $obj->is_changed;
898 Indicates if the given $obj has changes since the last update. Returns
900 a list of keys which have changed. (If autoupdate is on, this method
901 will return an empty list, unless called inside a before_update or
902 after_set_$column trigger)
903 id
905 $id = $obj->id;
907 @id = $obj->id;
908 Returns a unique identifier for this object based on the values in the
910 database. It's the equivalent of $obj->get($self->columns('Primary')),
911 with inflated values reduced to their ids.
912 A warning will be generated if this method is used in scalar context on
914 a table with a multi-column primary key.
915 LOW-LEVEL DATA ACCESS
917 On some occasions, such as when you're writing triggers or constraint
919 routines, you'll want to manipulate data in a Class::DBI object without
920 using the usual get() and set() accessors, which may themselves call
921 triggers, fetch information from the database, etc.
922 Rather than interacting directly with the data hash stored in a
924 Class::DBI object (the exact implementation of which may change in
925 future releases) you could use Class::DBI's low-level accessors. These
926 appear 'private' to make you think carefully about using them - they
927 should not be a common means of dealing with the object.
928 The data within the object is modelled as a set of key-value pairs,
930 where the keys are normalized column names (returned by find_column()),
931 and the values are the data from the database row represented by the
932 object. Access is via these functions:
933 _attrs
935 @values = $object->_attrs(@cols);
936 Returns the values for one or more keys.
938 _attribute_store
940 $object->_attribute_store( { $col0 => $val0, $col1 => $val1 } );
941 $object->_attribute_store($col0, $val0, $col1, $val1);
942 Stores values in the object. They key-value pairs may be passed in
944 either as a simple list or as a hash reference. This only updates
945 values in the object itself; changes will not be propagated to the
946 database.
947 _attribute_set
949 $object->_attribute_set( { $col0 => $val0, $col1 => $val1 } );
950 $object->_attribute_set($col0, $val0, $col1, $val1);
951 Updates values in the object via _attribute_store(), but also logs
953 the changes so that they are propagated to the database with the
954 next update. (Unlike set(), however, _attribute_set() will not
955 trigger an update if autoupdate is turned on.)
956 _attribute_delete
958 @values = $object->_attribute_delete(@cols);
959 Deletes values from the object, and returns the deleted values.
961 _attribute_exists
963 $bool = $object->_attribute_exists($col);
964 Returns a true value if the object contains a value for the speci‐
966 fied column, and a false value otherwise.
967 By default, Class::DBI uses simple hash references to store object
969 data, but all access is via these routines, so if you want to implement
970 a different data model, just override these functions.
971 OVERLOADED OPERATORS
973 Class::DBI and its subclasses overload the perl builtin stringify and
975 bool operators. This is a significant convenience.
976 The perl builtin bool operator is overloaded so that a Class::DBI
978 object reference is true so long as all its key columns have defined
979 values. (This means an object with an id() of zero is not considered
980 false.)
981 When a Class::DBI object reference is used in a string context it will,
983 by default, return the value of the primary key. (Composite primary key
984 values will be separated by a slash).
985 You can also specify the column(s) to be used for stringification via
987 the special 'Stringify' column group. So, for example, if you're using
988 an auto-incremented primary key, you could use this to provide a more
989 meaningful display string:
990 Widget->columns(Stringify => qw/name/);
992 If you need to do anything more complex, you can provide an
994 stringify_self() method which stringification will call:
995 sub stringify_self {
997 my $self = shift;
998 return join ":", $self->id, $self->name;
999 }
1000 This overloading behaviour can be useful for columns that have has_a()
1002 relationships. For example, consider a table that has price and cur‐
1003 rency fields:
1004 package Widget;
1006 use base 'My::Class::DBI';
1007 Widget->table('widget');
1008 Widget->columns(All => qw/widgetid name price currency_code/);
1009 $obj = Widget->retrieve($id);
1011 print $obj->price . " " . $obj->currency_code;
1012 The would print something like ""42.07 USD"". If the currency_code
1014 field is later changed to be a foreign key to a new currency table then
1015 $obj->currency_code will return an object reference instead of a plain
1016 string. Without overloading the stringify operator the example would
1017 now print something like ""42.07 Widget=HASH(0x1275}"" and the fix
1018 would be to change the code to add a call to id():
1019 print $obj->price . " " . $obj->currency_code->id;
1021 However, with overloaded stringification, the original code continues
1023 to work as before, with no code changes needed.
1024 This makes it much simpler and safer to add relationships to existing
1026 applications, or remove them later.
1027
1029 Databases are all about relationships. Thus Class::DBI provides a way
1030 for you to set up descriptions of your relationhips.
1031 Class::DBI provides three such relationships: 'has_a', 'has_many', and
1033 'might_have'. Others are available from CPAN.
1034 has_a
1036 Music::CD->has_a(column => 'Foreign::Class');
1038 Music::CD->has_a(artist => 'Music::Artist');
1040 print $cd->artist->name;
1041 'has_a' is most commonly used to supply lookup information for a for‐
1043 eign key. If a column is declared as storing the primary key of another
1044 table, then calling the method for that column does not return the id,
1045 but instead the relevant object from that foreign class.
1046 It is also possible to use has_a to inflate the column value to a non
1048 Class::DBI based. A common usage would be to inflate a date field to a
1049 date/time object:
1050 Music::CD->has_a(reldate => 'Date::Simple');
1052 print $cd->reldate->format("%d %b, %Y");
1053 Music::CD->has_a(reldate => 'Time::Piece',
1055 inflate => sub { Time::Piece->strptime(shift, "%Y-%m-%d") },
1056 deflate => 'ymd',
1057 );
1058 print $cd->reldate->strftime("%d %b, %Y");
1059 If the foreign class is another Class::DBI representation retrieve is
1061 called on that class with the column value. Any other object will be
1062 instantiated either by calling new($value) or using the given 'inflate'
1063 method. If the inflate method name is a subref, it will be executed,
1064 and will be passed the value and the Class::DBI object as arguments.
1065 When the object is being written to the database the object will be
1067 deflated either by calling the 'deflate' method (if given), or by
1068 attempting to stringify the object. If the deflate method is a subref,
1069 it will be passed the Class::DBI object as an argument.
1070 *NOTE* You should not attempt to make your primary key column inflate
1072 using has_a() as bad things will happen. If you have two tables which
1073 share a primary key, consider using might_have() instead.
1074 has_many
1076 Class->has_many(method_to_create => "Foreign::Class");
1078 Music::CD->has_many(tracks => 'Music::Track');
1080 my @tracks = $cd->tracks;
1082 my $track6 = $cd->add_to_tracks({
1084 position => 6,
1085 title => 'Tomorrow',
1086 });
1087 This method declares that another table is referencing us (i.e. storing
1089 our primary key in its table).
1090 It creates a named accessor method in our class which returns a list of
1092 all the matching Foreign::Class objects.
1093 In addition it creates another method which allows a new associated
1095 object to be constructed, taking care of the linking automatically.
1096 This method is the same as the accessor method with "add_to_"
1097 prepended.
1098 The add_to_tracks example above is exactly equivalent to:
1100 my $track6 = Music::Track->insert({
1102 cd => $cd,
1103 position => 6,
1104 title => 'Tomorrow',
1105 });
1106 When setting up the relationship the foreign class's has_a() declara‐
1108 tions are examined to discover which of its columns reference our
1109 class. (Note that because this happens at compile time, if the foreign
1110 class is defined in the same file, the class with the has_a() must be
1111 defined earlier than the class with the has_many(). If the classes are
1112 in different files, Class::DBI should usually be able to do the right
1113 things, as long as all classes inherit Class::DBI before 'use'ing any
1114 other classes.)
1115 If the foreign class has no has_a() declarations linking to this class,
1117 it is assumed that the foreign key in that class is named after the
1118 moniker() of this class.
1119 If this is not true you can pass an additional third argument to the
1121 has_many() declaration stating which column of the foreign class is the
1122 foreign key to this class.
1123 Limiting
1125 Music::Artist->has_many(cds => 'Music::CD');
1127 my @cds = $artist->cds(year => 1980);
1128 When calling the method created by has_many, you can also supply any
1130 additional key/value pairs for restricting the search. The above exam‐
1131 ple will only return the CDs with a year of 1980.
1132 Ordering
1134 Music::CD->has_many(tracks => 'Music::Track', { order_by => 'playorder' });
1136 has_many takes an optional final hashref of options. If an 'order_by'
1138 option is set, its value will be set in an ORDER BY clause in the SQL
1139 issued. This is passed through 'as is', enabling order_by clauses such
1140 as 'length DESC, position'.
1141 Mapping
1143 Music::CD->has_many(styles => [ 'Music::StyleRef' => 'style' ]);
1145 If the second argument to has_many is turned into a listref of the
1147 Classname and an additional method, then that method will be called in
1148 turn on each of the objects being returned.
1149 The above is exactly equivalent to:
1151 Music::CD->has_many(_style_refs => 'Music::StyleRef');
1153 sub styles {
1155 my $self = shift;
1156 return map $_->style, $self->_style_refs;
1157 }
1158 For an example of where this is useful see "MANY TO MANY RELATIONSHIPS"
1160 below.
1161 Cascading Delete
1163 Music::Artist->has_many(cds => 'Music::CD', { cascade => 'Fail' });
1165 It is also possible to control what happens to the 'child' objects when
1167 the 'parent' object is deleted. By default this is set to 'Delete' -
1168 so, for example, when you delete an artist, you also delete all their
1169 CDs, leaving no orphaned records. However you could also set this to
1170 'None', which would leave all those orphaned records (although this
1171 generally isn't a good idea), or 'Fail', which will throw an exception
1172 when you try to delete an artist that still has any CDs.
1173 You can also write your own Cascade strategies by supplying a Class
1175 Name here.
1176 For example you could write a Class::DBI::Cascade::Plugin::Nullify
1178 which would set all related foreign keys to be NULL, and plug it into
1179 your relationship:
1180 Music::Artist->has_many(cds => 'Music::CD', {
1182 cascade => 'Class::DBI::Cascade::Plugin::Nullify'
1183 });
1184 might_have
1186 Music::CD->might_have(method_name => Class => (@fields_to_import));
1188 Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);
1190 my $liner_notes_object = $cd->liner_notes;
1192 my $notes = $cd->notes; # equivalent to $cd->liner_notes->notes;
1193 might_have() is similar to has_many() for relationships that can have
1195 at most one associated objects. For example, if you have a CD database
1196 to which you want to add liner notes information, you might not want to
1197 add a 'liner_notes' column to your main CD table even though there is
1198 no multiplicity of relationship involved (each CD has at most one
1199 'liner notes' field). So, you create another table with the same pri‐
1200 mary key as this one, with which you can cross-reference.
1201 But you don't want to have to keep writing methods to turn the the
1203 'list' of liner_notes objects you'd get back from has_many into the
1204 single object you'd need. So, might_have() does this work for you. It
1205 creates an accessor to fetch the single object back if it exists, and
1206 it also allows you import any of its methods into your namespace. So,
1207 in the example above, the LinerNotes class can be mostly invisible -
1208 you can just call $cd->notes and it will call the notes method on the
1209 correct LinerNotes object transparently for you.
1210 Making sure you don't have namespace clashes is up to you, as is cor‐
1212 rectly creating the objects, but this may be made simpler in later ver‐
1213 sions. (Particularly if someone asks for this!)
1214 Notes
1216 has_a(), might_have() and has_many() check that the relevant class has
1218 already been loaded. If it hasn't then they try to load the module of
1219 the same name using require. If the require fails because it can't
1220 find the module then it will assume it's not a simple require (i.e.,
1221 Foreign::Class isn't in Foreign/Class.pm) and that you will take care
1222 of it and ignore the warning. Any other error, such as a syntax error,
1223 triggers an exception.
1224 NOTE: The two classes in a relationship do not have to be in the same
1226 database, on the same machine, or even in the same type of database! It
1227 is quite acceptable for a table in a MySQL database to be connected to
1228 a different table in an Oracle database, and for cascading delete etc
1229 to work across these. This should assist greatly if you need to migrate
1230 a database gradually.
1231
1233 Class::DBI does not currently support Many to Many relationships, per
1234 se. However, by combining the relationships that already exist it is
1235 possible to set these up.
1236 Consider the case of Films and Actors, with a linking Role table with a
1238 multi-column Primary Key. First of all set up the Role class:
1239 Role->table('role');
1241 Role->columns(Primary => qw/film actor/);
1242 Role->has_a(film => 'Film');
1243 Role->has_a(actor => 'Actor');
1244 Then, set up the Film and Actor classes to use this linking table:
1246 Film->table('film');
1248 Film->columns(All => qw/id title rating/);
1249 Film->has_many(stars => [ Role => 'actor' ]);
1250 Actor->table('actor');
1252 Actor->columns(All => qw/id name/);
1253 Actor->has_many(films => [ Role => 'film' ]);
1254 In each case the 'mapping method' variation of has_many() is used to
1256 call the lookup method on the Role object returned. As these methods
1257 are the 'has_a' relationships on the Role, these will return the actual
1258 Actor and Film objects, providing a cheap many-to-many relationship.
1259 In the case of Film, this is equivalent to the more long-winded:
1261 Film->has_many(roles => "Role");
1263 sub actors {
1265 my $self = shift;
1266 return map $_->actor, $self->roles
1267 }
1268 As this is almost exactly what is created internally, add_to_stars and
1270 add_to_films will generally do the right thing as they are actually
1271 doing the equivalent of add_to_roles:
1272 $film->add_to_actors({ actor => $actor });
1274 Similarly a cascading delete will also do the right thing as it will
1276 only delete the relationship from the linking table.
1277 If the Role table were to contain extra information, such as the name
1279 of the character played, then you would usually need to skip these
1280 short-cuts and set up each of the relationships, and associated helper
1281 methods, manually.
1282
1284 add_relationship_type
1285 The relationships described above are implemented through
1287 Class::DBI::Relationship subclasses. These are then plugged into
1288 Class::DBI through an add_relationship_type() call:
1289 __PACKAGE__->add_relationship_type(
1291 has_a => "Class::DBI::Relationship::HasA",
1292 has_many => "Class::DBI::Relationship::HasMany",
1293 might_have => "Class::DBI::Relationship::MightHave",
1294 );
1295 If is thus possible to add new relationship types, or modify the behav‐
1297 iour of the existing types. See Class::DBI::Relationship for more
1298 information on what is required.
1299
1301 There are several main approaches to setting up your own SQL queries:
1302 For queries which could be used to create a list of matching objects
1304 you can create a constructor method associated with this SQL and let
1305 Class::DBI do the work for you, or just inline the entire query.
1306 For more complex queries you need to fall back on the underlying
1308 Ima::DBI query mechanism. (Caveat: since Ima::DBI uses sprintf-style
1309 interpolation, you need to be careful to double any "wildcard" % signs
1310 in your queries).
1311 add_constructor
1313 __PACKAGE__->add_constructor(method_name => 'SQL_where_clause');
1315 The SQL can be of arbitrary complexity and will be turned into:
1317 SELECT (essential columns)
1319 FROM (table name)
1320 WHERE <your SQL>
1321 This will then create a method of the name you specify, which returns a
1323 list of objects as with any built in query.
1324 For example:
1326 Music::CD->add_constructor(new_music => 'year > 2000');
1328 my @recent = Music::CD->new_music;
1329 You can also supply placeholders in your SQL, which must then be speci‐
1331 fied at query time:
1332 Music::CD->add_constructor(new_music => 'year > ?');
1334 my @recent = Music::CD->new_music(2000);
1335 retrieve_from_sql
1337 On occasions where you want to execute arbitrary SQL, but don't want to
1339 go to the trouble of setting up a constructor method, you can inline
1340 the entire WHERE clause, and just get the objects back directly:
1341 my @cds = Music::CD->retrieve_from_sql(qq{
1343 artist = 'Ozzy Osbourne' AND
1344 title like "%Crazy" AND
1345 year <= 1986
1346 ORDER BY year
1347 LIMIT 2,3
1348 });
1349 Ima::DBI queries
1351 When you can't use 'add_constructor', e.g. when using aggregate func‐
1353 tions, you can fall back on the fact that Class::DBI inherits from
1354 Ima::DBI and prefers to use its style of dealing with statements, via
1355 set_sql().
1356 The Class::DBI set_sql() method defaults to using prepare_cached()
1358 unless the $cache parameter is defined and false (see Ima::DBI docs for
1359 more information).
1360 To assist with writing SQL that is inheritable into subclasses, several
1362 additional substitutions are available here: __TABLE__, __ESSENTIAL__
1363 and __IDENTIFIER__. These represent the table name associated with the
1364 class, its essential columns, and the primary key of the current
1365 object, in the case of an instance method on it.
1366 For example, the SQL for the internal 'update' method is implemented
1368 as:
1369 __PACKAGE__->set_sql('update', <<"");
1371 UPDATE __TABLE__
1372 SET %s
1373 WHERE __IDENTIFIER__
1374 The 'longhand' version of the new_music constructor shown above would
1376 similarly be:
1377 Music::CD->set_sql(new_music => qq{
1379 SELECT __ESSENTIAL__
1380 FROM __TABLE__
1381 WHERE year > ?
1382 });
1383 For such 'SELECT' queries Ima::DBI's set_sql() method is extended to
1385 create a helper shortcut method, named by prefixing the name of the SQL
1386 fragment with 'search_'. Thus, the above call to set_sql() will auto‐
1387 matically set up the method Music::CD->search_new_music(), which will
1388 execute this search and return the relevant objects or Iterator. (If
1389 there are placeholders in the query, you must pass the relevant argu‐
1390 ments when calling your search method.)
1391 This does the equivalent of:
1393 sub search_new_music {
1395 my ($class, @args) = @_;
1396 my $sth = $class->sql_new_music;
1397 $sth->execute(@args);
1398 return $class->sth_to_objects($sth);
1399 }
1400 The $sth which is used to return the objects here is a normal DBI-style
1402 statement handle, so if the results can't be turned into objects eas‐
1403 ily, it is still possible to call $sth->fetchrow_array etc and return
1404 whatever data you choose.
1405 Of course, any query can be added via set_sql, including joins. So, to
1407 add a query that returns the 10 Artists with the most CDs, you could
1408 write (with MySQL):
1409 Music::Artist->set_sql(most_cds => qq{
1411 SELECT artist.id, COUNT(cd.id) AS cds
1412 FROM artist, cd
1413 WHERE artist.id = cd.artist
1414 GROUP BY artist.id
1415 ORDER BY cds DESC
1416 LIMIT 10
1417 });
1418 my @artists = Music::Artist->search_most_cds();
1420 If you also need to access the 'cds' value returned from this query,
1422 the best approach is to declare 'cds' to be a TEMP column. (See
1423 "Non-Persistent Fields" below).
1424 Class::DBI::AbstractSearch
1426 my @music = Music::CD->search_where(
1428 artist => [ 'Ozzy', 'Kelly' ],
1429 status => { '!=', 'outdated' },
1430 );
1431 The Class::DBI::AbstractSearch module, available from CPAN, is a plugin
1433 for Class::DBI that allows you to write arbitrarily complex searches
1434 using perl data structures, rather than SQL.
1435 Single Value SELECTs
1437 select_val
1439 Selects which only return a single value can couple Class::DBI's
1441 sql_single() SQL, with the $sth->select_val() call which we get from
1442 DBIx::ContextualFetch.
1443 __PACKAGE__->set_sql(count_all => "SELECT COUNT(*) FROM __TABLE__");
1445 # .. then ..
1446 my $count = $class->sql_count_all->select_val;
1447 This can also take placeholders and/or do column interpolation if
1449 required:
1450 __PACKAGE__->set_sql(count_above => q{
1452 SELECT COUNT(*) FROM __TABLE__ WHERE %s > ?
1453 });
1454 # .. then ..
1455 my $count = $class->sql_count_above('year')->select_val(2001);
1456 sql_single
1458 Internally Class::DBI defines a very simple SQL fragment called 'sin‐
1460 gle':
1461 "SELECT %s FROM __TABLE__".
1463 This is used to implement the above Class->count_all():
1465 $class->sql_single("COUNT(*)")->select_val;
1467 This interpolates the COUNT(*) into the %s of the SQL, and then exe‐
1469 cutes the query, returning a single value.
1470 Any SQL set up via set_sql() can of course be supplied here, and
1472 select_val can take arguments for any placeholders there.
1473 Internally several helper methods are defined using this approach:
1475 - count_all
1477 - maximum_value_of($column)
1478 - minimum_value_of($column)
1479
1481 In the tradition of Perl, Class::DBI is lazy about how it loads your
1482 objects. Often, you find yourself using only a small number of the
1483 available columns and it would be a waste of memory to load all of them
1484 just to get at two, especially if you're dealing with large numbers of
1485 objects simultaneously.
1486 You should therefore group together your columns by typical usage, as
1488 fetching one value from a group can also pre-fetch all the others in
1489 that group for you, for more efficient access.
1490 So for example, if we usually fetch the artist and title, but don't use
1492 the 'year' so much, then we could say the following:
1493 Music::CD->columns(Primary => qw/cdid/);
1495 Music::CD->columns(Essential => qw/artist title/);
1496 Music::CD->columns(Others => qw/year runlength/);
1497 Now when you fetch back a CD it will come pre-loaded with the 'cdid',
1499 'artist' and 'title' fields. Fetching the 'year' will mean another
1500 visit to the database, but will bring back the 'runlength' whilst it's
1501 there.
1502 This can potentially increase performance.
1504 If you don't like this behavior, then just add all your columns to the
1506 Essential group, and Class::DBI will load everything at once. If you
1507 have a single column primary key you can do this all in one shot with
1508 one single column declaration:
1509 Music::CD->columns(Essential => qw/cdid artist title year runlength/);
1511 columns
1513 my @all_columns = $class->columns;
1515 my @columns = $class->columns($group);
1516 my @primary = $class->primary_columns;
1518 my $primary = $class->primary_column;
1519 my @essential = $class->_essential;
1520 There are four 'reserved' groups: 'All', 'Essential', 'Primary' and
1522 'TEMP'.
1523 'All' are all columns used by the class. If not set it will be created
1525 from all the other groups.
1526 'Primary' is the primary key columns for this class. It must be set
1528 before objects can be used.
1529 If 'All' is given but not 'Primary' it will assume the first column in
1531 'All' is the primary key.
1532 'Essential' are the minimal set of columns needed to load and use the
1534 object. Only the columns in this group will be loaded when an object is
1535 retrieve()'d. It is typically used to save memory on a class that has a
1536 lot of columns but where only use a few of them are commonly used. It
1537 will automatically be set to 'Primary' if not explicitly set. The
1538 'Primary' column is always part of the 'Essential' group.
1539 For simplicity primary_columns(), primary_column(), and _essential()
1541 methods are provided to return these. The primary_column() method
1542 should only be used for tables that have a single primary key column.
1543 Non-Persistent Fields
1545 Music::CD->columns(TEMP => qw/nonpersistent/);
1547 If you wish to have fields that act like columns in every other way,
1549 but that don't actually exist in the database (and thus will not per‐
1550 sist), you can declare them as part of a column group of 'TEMP'.
1551 find_column
1553 Class->find_column($column);
1555 $obj->find_column($column);
1556 The columns of a class are stored as Class::DBI::Column objects. This
1558 method will return you the object for the given column, if it exists.
1559 This is most useful either in a boolean context to discover if the col‐
1560 umn exists, or to 'normalize' a user-entered column name to an actual
1561 Column.
1562 The interface of the Column object itself is still under development,
1564 so you shouldn't really rely on anything internal to it.
1565
1567 Class::DBI suffers from the usual problems when dealing with transac‐
1568 tions. In particular, you should be very wary when committing your
1569 changes that you may actually be in a wider scope than expected and
1570 that your caller may not be expecting you to commit.
1571 However, as long as you are aware of this, and try to keep the scope of
1573 your transactions small, ideally always within the scope of a single
1574 method, you should be able to work with transactions with few problems.
1575 dbi_commit / dbi_rollback
1577 $obj->dbi_commit();
1579 $obj->dbi_rollback();
1580 These are thin aliases through to the DBI's commit() and rollback()
1582 commands to commit or rollback all changes to this object.
1583 Localised Transactions
1585 A nice idiom for turning on a transaction locally (with AutoCommit
1587 turned on globally) (courtesy of Dominic Mitchell) is:
1588 sub do_transaction {
1590 my $class = shift;
1591 my ( $code ) = @_;
1592 # Turn off AutoCommit for this scope.
1593 # A commit will occur at the exit of this block automatically,
1594 # when the local AutoCommit goes out of scope.
1595 local $class->db_Main->{ AutoCommit };
1596 # Execute the required code inside the transaction.
1598 eval { $code->() };
1599 if ( $@ ) {
1600 my $commit_error = $@;
1601 eval { $class->dbi_rollback }; # might also die!
1602 die $commit_error;
1603 }
1604 }
1605 And then you just call:
1607 Music::DBI->do_transaction( sub {
1609 my $artist = Music::Artist->insert({ name => 'Pink Floyd' });
1610 my $cd = $artist->add_to_cds({
1611 title => 'Dark Side Of The Moon',
1612 year => 1974,
1613 });
1614 });
1615 Now either both will get added, or the entire transaction will be
1617 rolled back.
1618
1620 Class::DBI supports uniqueness of objects in memory. In a given perl
1621 interpreter there will only be one instance of any given object at one
1622 time. Many variables may reference that object, but there can be only
1623 one.
1624 Here's an example to illustrate:
1626 my $artist1 = Music::Artist->insert({ artistid => 7, name => 'Polysics' });
1628 my $artist2 = Music::Artist->retrieve(7);
1629 my $artist3 = Music::Artist->search( name => 'Polysics' )->first;
1630 Now $artist1, $artist2, and $artist3 all point to the same object. If
1632 you update a property on one of them, all of them will reflect the
1633 update.
1634 This is implemented using a simple object lookup index for all live
1636 objects in memory. It is not a traditional cache - when your objects go
1637 out of scope, they will be destroyed normally, and a future retrieve
1638 will instantiate an entirely new object.
1639 The ability to perform this magic for you replies on your perl having
1641 access to the Scalar::Util::weaken function. Although this is part of
1642 the core perl distribution, some vendors do not compile support for it.
1643 To find out if your perl has support for it, you can run this on the
1644 command line:
1645 perl -e 'use Scalar::Util qw(weaken)'
1647 If you get an error message about weak references not being imple‐
1649 mented, Class::DBI will not maintain this lookup index, but give you a
1650 separate instances for each retrieve.
1651 A few new tools are offered for adjusting the behavior of the object
1653 index. These are still somewhat experimental and may change in a future
1654 release.
1655 remove_from_object_index
1657 $artist->remove_from_object_index();
1659 This is an object method for removing a single object from the live
1661 objects index. You can use this if you want to have multiple distinct
1662 copies of the same object in memory.
1663 clear_object_index
1665 Music::DBI->clear_object_index();
1667 You can call this method on any class or instance of Class::DBI, but
1669 the effect is universal: it removes all objects from the index.
1670 purge_object_index_every
1672 Music::Artist->purge_object_index_every(2000);
1674 Weak references are not removed from the index when an object goes out
1676 of scope. This means that over time the index will grow in memory.
1677 This is really only an issue for long-running environments like
1678 mod_perl, but every so often dead references are cleaned out to prevent
1679 this. By default, this happens every 1000 object loads, but you can
1680 change that default for your class by setting the
1681 'purge_object_index_every' value.
1682 (Eventually this may handled in the DESTROY method instead.)
1684 As a final note, keep in mind that you can still have multiple distinct
1686 copies of an object in memory if you have multiple perl interpreters
1687 running. CGI, mod_perl, and many other common usage situations run mul‐
1688 tiple interpreters, meaning that each one of them may have an instance
1689 of an object representing the same data. However, this is no worse than
1690 it was before, and is entirely normal for database applications in
1691 multi-process environments.
1692
1694 The preferred method of interacting with Class::DBI is for you to write
1695 a subclass for your database connection, with each table-class inherit‐
1696 ing in turn from it.
1697 As well as encapsulating the connection information in one place, this
1699 also allows you to override default behaviour or add additional func‐
1700 tionality across all of your classes.
1701 As the innards of Class::DBI are still in flux, you must exercise
1703 extreme caution in overriding private methods of Class::DBI (those
1704 starting with an underscore), unless they are explicitly mentioned in
1705 this documentation as being safe to override. If you find yourself
1706 needing to do this, then I would suggest that you ask on the mailing
1707 list about it, and we'll see if we can either come up with a better
1708 approach, or provide a new means to do whatever you need to do.
1709
1711 Multi-Column Foreign Keys are not supported
1712 You can't currently add a relationship keyed on multiple columns. You
1714 could, however, write a Relationship plugin to do this, and the world
1715 would be eternally grateful...
1716 Don't change or inflate the value of your primary columns
1718 Altering your primary key column currently causes Bad Things to happen.
1720 I should really protect against this.
1721
1723 Theoretically Class::DBI should work with almost any standard RDBMS. Of
1724 course, in the real world, we know that that's not true. It is known to
1725 work with MySQL, PostgreSQL, Oracle and SQLite, each of which have
1726 their own additional subclass on CPAN that you should explore if you're
1727 using them:
1728 L<Class::DBI::mysql>, L<Class::DBI::Pg>, L<Class::DBI::Oracle>,
1730 L<Class::DBI::SQLite>
1731 For the most part it's been reported to work with Sybase, although
1733 there are some issues with multi-case column/table names. Beyond that
1734 lies The Great Unknown(tm). If you have access to other databases,
1735 please give this a test run, and let me know the results.
1736 Ima::DBI (and hence Class::DBI) requires a database that supports table
1738 aliasing and a DBI driver that supports placeholders. This means it
1739 won't work with older releases of DBD::AnyData (and any releases of its
1740 predecessor DBD::RAM), and DBD::Sybase + FreeTDS may or may not work
1741 depending on your FreeTDS version.
1742
1744 Tony Bowden
1745
1747 Michael G Schwern
1748
1750 Tim Bunce, Tatsuhiko Miyagawa, Perrin Harkins, Alexander Karelas, Barry
1751 Hoggard, Bart Lateur, Boris Mouzykantskii, Brad Bowman, Brian Parker,
1752 Casey West, Charles Bailey, Christopher L. Everett Damian Conway, Dan
1753 Thill, Dave Cash, David Jack Olrik, Dominic Mitchell, Drew Taylor, Drew
1754 Wilson, Jay Strauss, Jesse Sheidlower, Jonathan Swartz, Marty Pauley,
1755 Michael Styer, Mike Lambert, Paul Makepeace, Phil Crow, Richard Piacen‐
1756 tini, Simon Cozens, Simon Wilcox, Thomas Klausner, Tom Renfro, Uri Gut‐
1757 man, William McKee, the Class::DBI mailing list, the POOP group, and
1758 all the others who've helped, but that I've forgetten to mention.
1759
1761 Class::DBI now uses a three-level versioning system. This release, for
1762 example, is version 3.0.15
1763 The general approach to releases will be that users who like a degree
1765 of stability can hold off on upgrades until the major sub-version
1766 increases (e.g. 3.1.0). Those who like living more on the cutting edge
1767 can keep up to date with minor sub-version releases.
1768 Functionality which was introduced during a minor sub-version release
1770 may disappear without warning in a later minor sub-version release.
1771 I'll try to avoid doing this, and will aim to have a deprecation cycle
1772 of at least a few minor sub-versions, but you should keep a close eye
1773 on the CHANGES file, and have good tests in place. (This is good advice
1774 generally, of course.) Anything that is in a major sub-version release
1775 will go through a deprecation cycle of at least one further major sub-
1776 version before it is removed (and usually longer).
1777 Getting changes accepted
1779 There is an active Class::DBI community, however I am not part of it.
1781 I am not on the mailing list, and I don't follow the wiki. I also do
1782 not follow Perl Monks or CPAN reviews or annoCPAN or whatever the tool
1783 du jour happens to be.
1784 If you find a problem with Class::DBI, by all means discuss it in any
1786 of these places, but don't expect anything to happen unless you actu‐
1787 ally tell me about it.
1788 The preferred method for doing this is via the CPAN RT interface, which
1790 you can access at http://rt.cpan.org/ or by emailing
1791 bugs-Class-DBI@rt.cpan.org
1792 If you email me personally about Class::DBI issues, then I will proba‐
1794 bly bounce them on to there, unless you specifically ask me not to.
1795 Otherwise I can't keep track of what all needs fixed. (This of course
1796 means that if you ask me not to send your mail to RT, there's a much
1797 higher chance that nothing will every happen about your problem).
1798 Bug Reports
1800 If you're reporting a bug then it has a much higher chance of getting
1802 fixed quicker if you can include a failing test case. This should be a
1803 completely stand-alone test that could be added to the Class::DBI dis‐
1804 tribution. That is, it should use Test::Simple or Test::More, fail with
1805 the current code, but pass when I fix the problem. If it needs to have
1806 a working database to show the problem, then this should preferably use
1807 SQLite, and come with all the code to set this up. The nice people on
1808 the mailing list will probably help you out if you need assistance
1809 putting this together.
1810 You don't need to include code for actually fixing the problem, but of
1812 course it's often nice if you can. I may choose to fix it in a differ‐
1813 ent way, however, so it's often better to ask first whether I'd like a
1814 patch, particularly before spending a lot of time hacking.
1815 Patches
1817 If you are sending patches, then please send either the entire code
1819 that is being changed or the output of 'diff -Bub'. Please also note
1820 what version the patch is against. I tend to apply all patches manu‐
1821 ally, so I'm more interested in being able to see what you're doing
1822 than in being able to apply the patch cleanly. Code formatting isn't an
1823 issue, as I automagically run perltidy against the source after any
1824 changes, so please format for clarity.
1825 Patches have a much better chance of being applied if they are small.
1827 People often think that it's better for me to get one patch with a
1828 bunch of fixes. It's not. I'd much rather get 100 small patches that
1829 can be applied one by one. A change that I can make and release in five
1830 minutes is always better than one that needs a couple of hours to pon‐
1831 der and work through.
1832 I often reject patches that I don't like. Please don't take it person‐
1834 ally. I also like time to think about the wider implications of
1835 changes. Often a lot of time. Feel free to remind me about things that
1836 I may have forgotten about, but as long as they're on rt.cpan.org I
1837 will get around to them eventually.
1838 Feature Requests
1840 Wish-list requests are fine, although you should probably discuss them
1842 on the mailing list (or equivalent) with others first. There's quite
1843 often a plugin somewhere that already does what you want.
1844 In general I am much more open to discussion on how best to provide the
1846 flexibility for you to make your Cool New Feature(tm) a plugin rather
1847 than adding it to Class::DBI itself.
1848 For the most part the core of Class::DBI already has most of the func‐
1850 tionality that I believe it will ever need (and some more besides, that
1851 will probably be split off at some point). Most other things are much
1852 better off as plugins, with a separate life on CPAN or elsewhere (and
1853 with me nowhere near the critical path). Most of the ongoing work on
1854 Class::DBI is about making life easier for people to write extensions -
1855 whether they're local to your own codebase or released for wider con‐
1856 sumption.
1857
1859 Support for Class::DBI is mostly via the mailing list.
1860 To join the list, or read the archives, visit
1862 http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
1863 There is also a Class::DBI wiki at
1865 http://www.class-dbi.com/
1866 The wiki contains much information that should probably be in these
1868 docs but isn't yet. (See above if you want to help to rectify this.)
1869 As mentioned above, I don't follow the list or the wiki, so if you want
1871 to contact me individually, then you'll have to track me down person‐
1872 ally.
1873 There are lots of 3rd party subclasses and plugins available. For a
1875 list of the ones on CPAN see:
1876 http://search.cpan.org/search?query=Class%3A%3ADBI&mode=module
1877 An article on Class::DBI was published on Perl.com a while ago. It's
1879 slightly out of date , but it's a good introduction:
1880 http://www.perl.com/pub/a/2002/11/27/classdbi.html
1881 The wiki has numerous references to other articles, presentations etc.
1883 http://poop.sourceforge.net/ provides a document comparing a variety of
1885 different approaches to database persistence, such as Class::DBI, Alaz‐
1886 abo, Tangram, SPOPS etc.
1887
1889 This library is free software; you can redistribute it and/or modify it
1890 under the same terms as Perl itself.
1891
1893 Class::DBI is built on top of Ima::DBI, DBIx::ContextualFetch,
1894 Class::Accessor and Class::Data::Inheritable. The innards and much of
1895 the interface are easier to understand if you have an idea of how they
1896 all work as well.
1897perl v5.8.8 2006-11-05 Class::DBI(3)