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
75 different ways.
76 In short, Class::DBI aims to make it simple to introduce 'best
78 practice' when dealing with data stored in a relational database.
79 How to set it up
81 Set up a database.
82 You must have an existing database set up, have DBI.pm installed
83 and the necessary DBD:: driver module for that database. See DBI
84 and the documentation of your particular database and driver for
85 details.
86 Set up a table for your objects to be stored in.
88 Class::DBI works on a simple one class/one table model. It is your
89 responsibility to have your database tables already set up.
90 Automating that process is outside the scope of Class::DBI.
91 Using our CD example, you might declare a table something like
93 this:
94 CREATE TABLE cd (
96 cdid INTEGER PRIMARY KEY,
97 artist INTEGER, # references 'artist'
98 title VARCHAR(255),
99 year CHAR(4),
100 );
101 Set up an application base class
103 It's usually wise to set up a "top level" class for your entire
104 application to inherit from, rather than have each class inherit
105 directly from Class::DBI. This gives you a convenient point to
106 place system-wide overrides and enhancements to Class::DBI's
107 behavior.
108 package Music::DBI;
110 use base 'Class::DBI';
111 Give it a database connection
113 Class::DBI needs to know how to access the database. It does this
114 through a DBI connection which you set up by calling the
115 connection() method.
116 Music::DBI->connection('dbi:mysql:dbname', 'user', 'password');
118 By setting the connection up in your application base class all the
120 table classes that inherit from it will share the same connection.
121 Set up each Class
123 package Music::CD;
124 use base 'Music::DBI';
125 Each class will inherit from your application base class, so you
127 don't need to repeat the information on how to connect to the
128 database.
129 Declare the name of your table
131 Inform Class::DBI what table you are using for this class:
132 Music::CD->table('cd');
134 Declare your columns.
136 This is done using the columns() method. In the simplest form, you
137 tell it the name of all your columns (with the single primary key
138 first):
139 Music::CD->columns(All => qw/cdid artist title year/);
141 If the primary key of your table spans multiple columns then
143 declare them using a separate call to columns() like this:
144 Music::CD->columns(Primary => qw/pk1 pk2/);
146 Music::CD->columns(Others => qw/foo bar baz/);
147 For more information about how you can more efficiently use subsets
149 of your columns, see "LAZY POPULATION"
150 Done.
152 That's it! You now have a class with methods to "insert",
153 "retrieve", "search" for, "update" and "delete" objects from your
154 table, as well as accessors and mutators for each of the columns in
155 that object (row).
156 Let's look at all that in more detail:
158
160 connection
161 __PACKAGE__->connection($data_source, $user, $password, \%attr);
162 This sets up a database connection with the given information.
164 This uses Ima::DBI to set up an inheritable connection (named Main). It
166 is therefore usual to only set up a connection() in your application
167 base class and let the 'table' classes inherit from it.
168 package Music::DBI;
170 use base 'Class::DBI';
171 Music::DBI->connection('dbi:foo:dbname', 'user', 'password');
173 package My::Other::Table;
175 use base 'Music::DBI';
176 Class::DBI helps you along a bit to set up the database connection.
178 connection() provides its own default attributes depending on the
179 driver name in the data_source parameter. The connection() method
180 provides defaults for these attributes:
181 FetchHashKeyName => 'NAME_lc',
183 ShowErrorStatement => 1,
184 ChopBlanks => 1,
185 AutoCommit => 1,
186 (Except for Oracle and Pg, where AutoCommit defaults 0, placing the
188 database in transactional mode).
189 The defaults can always be extended (or overridden if you know what
191 you're doing) by supplying your own \%attr parameter. For example:
192 Music::DBI->connection(dbi:foo:dbname','user','pass',{ChopBlanks=>0});
194 The RootClass of DBIx::ContextualFetch in also inherited from Ima::DBI,
196 and you should be very careful not to change this unless you know what
197 you're doing!
198 Dynamic Database Connections / db_Main
200 It is sometimes desirable to generate your database connection
202 information dynamically, for example, to allow multiple databases with
203 the same schema to not have to duplicate an entire class hierarchy.
204 The preferred method for doing this is to supply your own db_Main()
206 method rather than calling "connection". This method should return a
207 valid database handle, and should ensure it sets the standard
208 attributes described above, preferably by combining
209 $class->_default_attributes() with your own. Note, this handle *must*
210 have its RootClass set to DBIx::ContextualFetch, so it is usually not
211 possible to just supply a $dbh obtained elsewhere.
212 Note that connection information is class data, and that changing it at
214 run time may have unexpected behaviour for instances of the class
215 already in existence.
216 table
218 __PACKAGE__->table($table);
219 $table = Class->table;
221 $table = $obj->table;
222 An accessor to get/set the name of the database table in which this
224 class is stored. It -must- be set.
225 Table information is inherited by subclasses, but can be overridden.
227 table_alias
229 package Shop::Order;
230 __PACKAGE__->table('orders');
231 __PACKAGE__->table_alias('orders');
232 When Class::DBI constructs SQL, it aliases your table name to a name
234 representing your class. However, if your class's name is an SQL
235 reserved word (such as 'Order') this will cause SQL errors. In such
236 cases you should supply your own alias for your table name (which can,
237 of course, be the same as the actual table name).
238 This can also be passed as a second argument to 'table':
240 __PACKAGE__->table('orders', 'orders');
242 As with table, this is inherited but can be overridden.
244 sequence / auto_increment
246 __PACKAGE__->sequence($sequence_name);
247 $sequence_name = Class->sequence;
249 $sequence_name = $obj->sequence;
250 If you are using a database which supports sequences and you want to
252 use a sequence to automatically supply values for the primary key of a
253 table, then you should declare this using the sequence() method:
254 __PACKAGE__->columns(Primary => 'id');
256 __PACKAGE__->sequence('class_id_seq');
257 Class::DBI will use the sequence to generate a primary key value when
259 objects are inserted without one.
260 *NOTE* This method does not work for Oracle. However,
262 Class::DBI::Oracle (which can be downloaded separately from CPAN)
263 provides a suitable replacement sequence() method.
264 If you are using a database with AUTO_INCREMENT (e.g. MySQL) then you
266 do not need this, and any call to insert() without a primary key
267 specified will fill this in automagically.
268 Sequence and auto-increment mechanisms only apply to tables that have a
270 single column primary key. For tables with multi-column primary keys
271 you need to supply the key values manually.
272
274 The following are methods provided for convenience to insert, retrieve
275 and delete stored objects. It's not entirely one-size fits all and you
276 might find it necessary to override them.
277 insert
279 my $obj = Class->insert(\%data);
280 This is a constructor to insert new data into the database and create
282 an object representing the newly inserted row.
283 %data consists of the initial information to place in your object and
285 the database. The keys of %data match up with the columns of your
286 objects and the values are the initial settings of those fields.
287 my $cd = Music::CD->insert({
289 cdid => 1,
290 artist => $artist,
291 title => 'October',
292 year => 1980,
293 });
294 If the table has a single primary key column and that column value is
296 not defined in %data, insert() will assume it is to be generated. If a
297 sequence() has been specified for this Class, it will use that.
298 Otherwise, it will assume the primary key can be generated by
299 AUTO_INCREMENT and attempt to use that.
300 The "before_create" trigger is invoked directly after storing the
302 supplied values into the new object and before inserting the record
303 into the database. The object stored in $self may not have all the
304 functionality of the final object after_creation, particularly if the
305 database is going to be providing the primary key value.
306 For tables with multi-column primary keys you need to supply all the
308 key values, either in the arguments to the insert() method, or by
309 setting the values in a "before_create" trigger.
310 If the class has declared relationships with foreign classes via
312 has_a(), you can pass an object to insert() for the value of that key.
313 Class::DBI will Do The Right Thing.
314 After the new record has been inserted into the database the data for
316 non-primary key columns is discarded from the object. If those columns
317 are accessed again they'll simply be fetched as needed. This ensures
318 that the data in the application is consistent with what the database
319 actually stored.
320 The "after_create" trigger is invoked after the database insert has
322 executed.
323 find_or_create
325 my $cd = Music::CD->find_or_create({ artist => 'U2', title => 'Boy' });
326 This checks if a CD can be found to match the information passed, and
328 if not inserts it.
329 delete
331 $obj->delete;
332 Music::CD->search(year => 1980, title => 'Greatest %')->delete_all;
333 Deletes this object from the database and from memory. If you have set
335 up any relationships using "has_many" or "might_have", this will delete
336 the foreign elements also, recursively (cascading delete). $obj is no
337 longer usable after this call.
338 Multiple objects can be deleted by calling delete_all on the Iterator
340 returned from a search. Each object found will be deleted in turn, so
341 cascading delete and other triggers will be honoured.
342 The "before_delete" trigger is when an object instance is about to be
344 deleted. It is invoked before any cascaded deletes. The "after_delete"
345 trigger is invoked after the record has been deleted from the database
346 and just before the contents in memory are discarded.
347
349 Class::DBI provides a few very simple search methods.
350 It is not the goal of Class::DBI to replace the need for using SQL.
352 Users are expected to write their own searches for more complex cases.
353 Class::DBI::AbstractSearch, available on CPAN, provides a much more
355 complex search interface than Class::DBI provides itself.
356 retrieve
358 $obj = Class->retrieve( $id );
359 $obj = Class->retrieve( %key_values );
360 Given key values it will retrieve the object with that key from the
362 database. For tables with a single column primary key a single
363 parameter can be used, otherwise a hash of key-name key-value pairs
364 must be given.
365 my $cd = Music::CD->retrieve(1) or die "No such cd";
367 retrieve_all
369 my @objs = Class->retrieve_all;
370 my $iterator = Class->retrieve_all;
371 Retrieves objects for all rows in the database. This is probably a bad
373 idea if your table is big, unless you use the iterator version.
374 search
376 @objs = Class->search(column1 => $value, column2 => $value ...);
377 This is a simple search for all objects where the columns specified are
379 equal to the values specified e.g.:
380 @cds = Music::CD->search(year => 1990);
382 @cds = Music::CD->search(title => "Greatest Hits", year => 1990);
383 You may also specify the sort order of the results by adding a final
385 hash of arguments with the key 'order_by':
386 @cds = Music::CD->search(year => 1990, { order_by=>'artist' });
388 This is passed through 'as is', enabling order_by clauses such as 'year
390 DESC, title'.
391 search_like
393 @objs = Class->search_like(column1 => $like_pattern, ....);
394 This is a simple search for all objects where the columns specified are
396 like the values specified. $like_pattern is a pattern given in SQL
397 LIKE predicate syntax. '%' means "any zero or more characters", '_'
398 means "any single character".
399 @cds = Music::CD->search_like(title => 'October%');
401 @cds = Music::CD->search_like(title => 'Hits%', artist => 'Various%');
402 You can also use 'order_by' with these, as with search().
404
406 my $it = Music::CD->search_like(title => 'October%');
407 while (my $cd = $it->next) {
408 print $cd->title;
409 }
410 Any of the above searches (as well as those defined by has_many) can
412 also be used as an iterator. Rather than creating a list of objects
413 matching your criteria, this will return a Class::DBI::Iterator
414 instance, which can return the objects required one at a time.
415 Currently the iterator initially fetches all the matching row data into
417 memory, and defers only the creation of the objects from that data
418 until the iterator is asked for the next object. So using an iterator
419 will only save significant memory if your objects will inflate
420 substantially when used.
421 In the case of has_many relationships with a mapping method, the
423 mapping method is not called until each time you call 'next'. This
424 means that if your mapping is not a one-to-one, the results will
425 probably not be what you expect.
426 Subclassing the Iterator
428 Music::CD->iterator_class('Music::CD::Iterator');
429 You can also subclass the default iterator class to override its
431 functionality. This is done via class data, and so is inherited into
432 your subclasses.
433 QUICK RETRIEVAL
435 my $obj = Class->construct(\%data);
436 This is used to turn data from the database into objects, and should
438 thus only be used when writing constructors. It is very handy for
439 cheaply setting up lots of objects from data for without going back to
440 the database.
441 For example, instead of doing one SELECT to get a bunch of IDs and then
443 feeding those individually to retrieve() (and thus doing more SELECT
444 calls), you can do one SELECT to get the essential data of many objects
445 and feed that data to construct():
446 return map $class->construct($_), $sth->fetchall_hash;
448 The construct() method creates a new empty object, loads in the column
450 values, and then invokes the "select" trigger.
451
453 copy
454 $new_obj = $obj->copy;
455 $new_obj = $obj->copy($new_id);
456 $new_obj = $obj->copy({ title => 'new_title', rating => 18 });
457 This creates a copy of the given $obj, removes the primary key, sets
459 any supplied column values and calls insert() to make a new record in
460 the database.
461 For tables with a single column primary key, copy() can be called with
463 no parameters and the new object will be assigned a key automatically.
464 Or a single parameter can be supplied and will be used as the new key.
465 For tables with a multi-column primary key, copy() must be called with
467 parameters which supply new values for all primary key columns, unless
468 a "before_create" trigger will supply them. The insert() method will
469 fail if any primary key columns are not defined.
470 my $blrunner_dc = $blrunner->copy("Bladerunner: Director's Cut");
472 my $blrunner_unrated = $blrunner->copy({
473 Title => "Bladerunner: Director's Cut",
474 Rating => 'Unrated',
475 });
476 move
478 my $new_obj = Sub::Class->move($old_obj);
479 my $new_obj = Sub::Class->move($old_obj, $new_id);
480 my $new_obj = Sub::Class->move($old_obj, \%changes);
481 For transferring objects from one class to another. Similar to copy(),
483 an instance of Sub::Class is inserted using the data in $old_obj
484 (Sub::Class is a subclass of $old_obj's subclass). Like copy(), you can
485 supply $new_id as the primary key of $new_obj (otherwise the usual
486 sequence or autoincrement is used), or a hashref of multiple new
487 values.
488
490 __PACKAGE__->add_trigger(trigger_point_name => \&code_to_execute);
491 # e.g.
493 __PACKAGE__->add_trigger(after_create => \&call_after_create);
495 It is possible to set up triggers that will be called at various points
497 in the life of an object. Valid trigger points are:
498 before_create (also used for deflation)
500 after_create
501 before_set_$column (also used by add_constraint)
502 after_set_$column (also used for inflation and by has_a)
503 before_update (also used for deflation and by might_have)
504 after_update
505 before_delete
506 after_delete
507 select (also used for inflation and by construct and _flesh)
508 You can create any number of triggers for each point, but you cannot
510 specify the order in which they will be run.
511 All triggers are passed the object they are being fired for, except
513 when "before_set_$column" is fired during "insert", in which case the
514 class is passed in place of the object, which does not yet exist. You
515 may change object values if required.
516 Some triggers are also passed extra parameters as name-value pairs. The
518 individual triggers are further documented with the methods that
519 trigger them.
520
522 __PACKAGE__->add_constraint('name', column => \&check_sub);
523 # e.g.
525 __PACKAGE__->add_constraint('over18', age => \&check_age);
527 # Simple version
529 sub check_age {
530 my ($value) = @_;
531 return $value >= 18;
532 }
533 # Cross-field checking - must have SSN if age < 18
535 sub check_age {
536 my ($value, $self, $column_name, $changing) = @_;
537 return 1 if $value >= 18; # We're old enough.
538 return 1 if $changing->{SSN}; # We're also being given an SSN
539 return 0 if !ref($self); # This is an insert, so we can't have an SSN
540 return 1 if $self->ssn; # We already have one in the database
541 return 0; # We can't find an SSN anywhere
542 }
543 It is also possible to set up constraints on the values that can be set
545 on a column. The constraint on a column is triggered whenever an object
546 is created and whenever the value in that column is being changed.
547 The constraint code is called with four parameters:
549 - The new value to be assigned
551 - The object it will be assigned to
552 (or class name when initially creating an object)
553 - The name of the column
554 (useful if many constraints share the same code)
555 - A hash ref of all new column values being assigned
556 (useful for cross-field validation)
557 The constraints are applied to all the columns being set before the
559 object data is changed. Attempting to create or modify an object where
560 one or more constraint fail results in an exception and the object
561 remains unchanged.
562 The exception thrown has its data set to a hashref of the column being
564 changed and the value being changed to.
565 Note 1: Constraints are implemented using before_set_$column triggers.
567 This will only prevent you from setting these values through a the
568 provided insert() or set() methods. It will always be possible to
569 bypass this if you try hard enough.
570 Note 2: When an object is created constraints are currently only
572 checked for column names included in the parameters to insert(). This
573 is probably a bug and is likely to change in future.
574 constrain_column
576 Film->constrain_column(year => qr/^\d{4}$/);
577 Film->constrain_column(rating => [qw/U Uc PG 12 15 18/]);
578 Film->constrain_column(title => sub { length() <= 20 });
579 Simple anonymous constraints can also be added to a column using the
581 constrain_column() method. By default this takes either a regex which
582 must match, a reference to a list of possible values, or a subref which
583 will have $_ aliased to the value being set, and should return a true
584 or false value.
585 However, this behaviour can be extended (or replaced) by providing a
587 constraint handler for the type of argument passed to constrain_column.
588 This behavior should be provided in a method named
589 "_constrain_by_$type", where $type is the moniker of the argument. For
590 example, the year example above could be provided by
591 _constrain_by_array().
592
594 Before an object is assigned data from the application (via insert or a
595 set accessor) the normalize_column_values() method is called with a
596 reference to a hash containing the column names and the new values
597 which are to be assigned (after any validation and constraint checking,
598 as described below).
599 Currently Class::DBI does not offer any per-column mechanism here. The
601 default method is empty. You can override it in your own classes to
602 normalize (edit) the data in any way you need. For example the values
603 in the hash for certain columns could be made lowercase.
604 The method is called as an instance method when the values of an
606 existing object are being changed, and as a class method when a new
607 object is being created.
608
610 Before an object is assigned data from the application (via insert or a
611 set accessor) the validate_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.
614 The method is called as an instance method when the values of an
616 existing object are being changed, and as a class method when a new
617 object is being inserted.
618 The default method calls the before_set_$column trigger for each column
620 name in the hash. Each trigger is called inside an eval. Any failures
621 result in an exception after all have been checked. The exception data
622 is a reference to a hash which holds the column name and error text for
623 each trigger error.
624 When using this mechanism for form data validation, for example, this
626 exception data can be stored in an exception object, via a custom
627 _croak() method, and then caught and used to redisplay the form with
628 error messages next to each field which failed validation.
629
631 All errors that are generated, or caught and propagated, by Class::DBI
632 are handled by calling the _croak() method (as an instance method if
633 possible, or else as a class method).
634 The _croak() method is passed an error message and in some cases some
636 extra information as described below. The default behaviour is simply
637 to call Carp::croak($message).
638 Applications that require custom behaviour should override the _croak()
640 method in their application base class (or table classes for table-
641 specific behaviour). For example:
642 use Error;
644 sub _croak {
646 my ($self, $message, %info) = @_;
647 # convert errors into exception objects
648 # except for duplicate insert errors which we'll ignore
649 Error->throw(-text => $message, %info)
650 unless $message =~ /^Can't insert .* duplicate/;
651 return;
652 }
653 The _croak() method is expected to trigger an exception and not return.
655 If it does return then it should use "return;" so that an undef or
656 empty list is returned as required depending on the calling context.
657 You should only return other values if you are prepared to deal with
658 the (unsupported) consequences.
659 For exceptions that are caught and propagated by Class::DBI, $message
661 includes the text of $@ and the original $@ value is available in
662 $info{err}. That allows you to correctly propagate exception objects
663 that may have been thrown 'below' Class::DBI (using
664 Exception::Class::DBI for example).
665 Exceptions generated by some methods may provide additional data in
667 $info{data} and, if so, also store the method name in $info{method}.
668 For example, the validate_column_values() method stores details of
669 failed validations in $info{data}. See individual method documentation
670 for what additional data they may store, if any.
671
673 All warnings are handled by calling the _carp() method (as an instance
674 method if possible, or else as a class method). The default behaviour
675 is simply to call Carp::carp().
676
678 accessors
679 Class::DBI inherits from Class::Accessor and thus provides individual
680 accessor methods for every column in your subclass. It also overrides
681 the get() and set() methods provided by Accessor to automagically
682 handle database reading and writing. (Note that as it doesn't make
683 sense to store a list of values in a column, set() takes a hash of
684 column => value pairs, rather than the single key => values of
685 Class::Accessor).
686 the fundamental set() and get() methods
688 $value = $obj->get($column_name);
689 @values = $obj->get(@column_names);
690 $obj->set($column_name => $value);
692 $obj->set($col1 => $value1, $col2 => $value2 ... );
693 These methods are the fundamental entry points for getting and setting
695 column values. The extra accessor methods automatically generated for
696 each column of your table are simple wrappers that call these get() and
697 set() methods.
698 The set() method calls normalize_column_values() then
700 validate_column_values() before storing the values. The
701 "before_set_$column" trigger is invoked by validate_column_values(),
702 checking any constraints that may have been set up.
703 The "after_set_$column" trigger is invoked after the new value has been
705 stored.
706 It is possible for an object to not have all its column data in memory
708 (due to lazy inflation). If the get() method is called for such a
709 column then it will select the corresponding group of columns and then
710 invoke the "select" trigger.
711
713 accessor_name_for / mutator_name_for
714 It is possible to change the name of the accessor method created for a
715 column either declaratively or programmatically.
716 If, for example, you have a column with a name that clashes with a
718 method otherwise created by Class::DBI, such as 'meta_info', you could
719 create that Column explicitly with a different accessor (and/or
720 mutator) when setting up your columns:
721 my $meta_col = Class::DBI::Column->new(meta_info => {
723 accessor => 'metadata',
724 });
725 __PACKAGE__->columns(All => qw/id name/, $meta_col);
727 If you want to change the name of all your accessors, or all that match
729 a certain pattern, you need to provide an accessor_name_for($col)
730 method, which will convert a column name to a method name.
731 e.g: if your local database naming convention was to prepend the word
733 'customer' to each column in the 'customer' table, so that you had the
734 columns 'customerid', 'customername' and 'customerage', but you wanted
735 your methods to just be $customer->name and $customer->age rather than
736 $customer->customername etc., you could create a
737 sub accessor_name_for {
739 my ($class, $column) = @_;
740 $column =~ s/^customer//;
741 return $column;
742 }
743 Similarly, if you wanted to have distinct accessor and mutator methods,
745 you could provide a mutator_name_for($col) method which would return
746 the name of the method to change the value:
747 sub mutator_name_for {
749 my ($class, $column) = @_;
750 return "set_" . $column->accessor;
751 }
752 If you override the mutator name, then the accessor method will be
754 enforced as read-only, and the mutator as write-only.
755 update vs auto update
757 There are two modes for the accessors to work in: manual update and
758 autoupdate. When in autoupdate mode, every time one calls an accessor
759 to make a change an UPDATE will immediately be sent to the database.
760 Otherwise, if autoupdate is off, no changes will be written until
761 update() is explicitly called.
762 This is an example of manual updating:
764 # The calls to NumExplodingSheep() and Rating() will only make the
766 # changes in memory, not in the database. Once update() is called
767 # it writes to the database in one swell foop.
768 $gone->NumExplodingSheep(5);
769 $gone->Rating('NC-17');
770 $gone->update;
771 And of autoupdating:
773 # Turn autoupdating on for this object.
775 $gone->autoupdate(1);
776 # Each accessor call causes the new value to immediately be written.
778 $gone->NumExplodingSheep(5);
779 $gone->Rating('NC-17');
780 Manual updating is probably more efficient than autoupdating and it
782 provides the extra safety of a discard_changes() option to clear out
783 all unsaved changes. Autoupdating can be more convenient for the
784 programmer. Autoupdating is off by default.
785 If changes are neither updated nor rolled back when the object is
787 destroyed (falls out of scope or the program ends) then Class::DBI's
788 DESTROY method will print a warning about unsaved changes.
789 autoupdate
791 __PACKAGE__->autoupdate($on_or_off);
792 $update_style = Class->autoupdate;
793 $obj->autoupdate($on_or_off);
795 $update_style = $obj->autoupdate;
796 This is an accessor to the current style of auto-updating. When called
798 with no arguments it returns the current auto-updating state, true for
799 on, false for off. When given an argument it turns auto-updating on
800 and off: a true value turns it on, a false one off.
801 When called as a class method it will control the updating style for
803 every instance of the class. When called on an individual object it
804 will control updating for just that object, overriding the choice for
805 the class.
806 __PACKAGE__->autoupdate(1); # Autoupdate is now on for the class.
808 $obj = Class->retrieve('Aliens Cut My Hair');
810 $obj->autoupdate(0); # Shut off autoupdating for this object.
811 The update setting for an object is not stored in the database.
813 update
815 $obj->update;
816 If "autoupdate" is not enabled then changes you make to your object are
818 not reflected in the database until you call update(). It is harmless
819 to call update() if there are no changes to be saved. (If autoupdate
820 is on there'll never be anything to save.)
821 Note: If you have transactions turned on for your database (but see
823 "TRANSACTIONS" below) you will also need to call dbi_commit(), as
824 update() merely issues the UPDATE to the database).
825 After the database update has been executed, the data for columns that
827 have been updated are deleted from the object. If those columns are
828 accessed again they'll simply be fetched as needed. This ensures that
829 the data in the application is consistent with what the database
830 actually stored.
831 When update() is called the "before_update"($self) trigger is always
833 invoked immediately.
834 If any columns have been updated then the "after_update" trigger is
836 invoked after the database update has executed and is passed:
837 ($self, discard_columns => \@discard_columns)
838 The trigger code can modify the discard_columns array to affect which
840 columns are discarded.
841 For example:
843 Class->add_trigger(after_update => sub {
845 my ($self, %args) = @_;
846 my $discard_columns = $args{discard_columns};
847 # discard the md5_hash column if any field starting with 'foo'
848 # has been updated - because the md5_hash will have been changed
849 # by a trigger.
850 push @$discard_columns, 'md5_hash' if grep { /^foo/ } @$discard_columns;
851 });
852 Take care to not delete a primary key column unless you know what
854 you're doing.
855 The update() method returns the number of rows updated. If the object
857 had not changed and thus did not need to issue an UPDATE statement, the
858 update() call will have a return value of -1.
859 If the record in the database has been deleted, or its primary key
861 value changed, then the update will not affect any records and so the
862 update() method will return 0.
863 discard_changes
865 $obj->discard_changes;
866 Removes any changes you've made to this object since the last update.
868 Currently this simply discards the column values from the object.
869 If you're using autoupdate this method will throw an exception.
871 is_changed
873 my $changed = $obj->is_changed;
874 my @changed_keys = $obj->is_changed;
875 Indicates if the given $obj has changes since the last update. Returns
877 a list of keys which have changed. (If autoupdate is on, this method
878 will return an empty list, unless called inside a before_update or
879 after_set_$column trigger)
880 id
882 $id = $obj->id;
883 @id = $obj->id;
884 Returns a unique identifier for this object based on the values in the
886 database. It's the equivalent of $obj->get($self->columns('Primary')),
887 with inflated values reduced to their ids.
888 A warning will be generated if this method is used in scalar context on
890 a table with a multi-column primary key.
891 LOW-LEVEL DATA ACCESS
893 On some occasions, such as when you're writing triggers or constraint
894 routines, you'll want to manipulate data in a Class::DBI object without
895 using the usual get() and set() accessors, which may themselves call
896 triggers, fetch information from the database, etc.
897 Rather than interacting directly with the data hash stored in a
899 Class::DBI object (the exact implementation of which may change in
900 future releases) you could use Class::DBI's low-level accessors. These
901 appear 'private' to make you think carefully about using them - they
902 should not be a common means of dealing with the object.
903 The data within the object is modelled as a set of key-value pairs,
905 where the keys are normalized column names (returned by find_column()),
906 and the values are the data from the database row represented by the
907 object. Access is via these functions:
908 _attrs
910 @values = $object->_attrs(@cols);
911 Returns the values for one or more keys.
913 _attribute_store
915 $object->_attribute_store( { $col0 => $val0, $col1 => $val1 } );
916 $object->_attribute_store($col0, $val0, $col1, $val1);
917 Stores values in the object. They key-value pairs may be passed in
919 either as a simple list or as a hash reference. This only updates
920 values in the object itself; changes will not be propagated to the
921 database.
922 _attribute_set
924 $object->_attribute_set( { $col0 => $val0, $col1 => $val1 } );
925 $object->_attribute_set($col0, $val0, $col1, $val1);
926 Updates values in the object via _attribute_store(), but also logs
928 the changes so that they are propagated to the database with the
929 next update. (Unlike set(), however, _attribute_set() will not
930 trigger an update if autoupdate is turned on.)
931 _attribute_delete
933 @values = $object->_attribute_delete(@cols);
934 Deletes values from the object, and returns the deleted values.
936 _attribute_exists
938 $bool = $object->_attribute_exists($col);
939 Returns a true value if the object contains a value for the
941 specified column, and a false value otherwise.
942 By default, Class::DBI uses simple hash references to store object
944 data, but all access is via these routines, so if you want to implement
945 a different data model, just override these functions.
946 OVERLOADED OPERATORS
948 Class::DBI and its subclasses overload the perl builtin stringify and
949 bool operators. This is a significant convenience.
950 The perl builtin bool operator is overloaded so that a Class::DBI
952 object reference is true so long as all its key columns have defined
953 values. (This means an object with an id() of zero is not considered
954 false.)
955 When a Class::DBI object reference is used in a string context it will,
957 by default, return the value of the primary key. (Composite primary key
958 values will be separated by a slash).
959 You can also specify the column(s) to be used for stringification via
961 the special 'Stringify' column group. So, for example, if you're using
962 an auto-incremented primary key, you could use this to provide a more
963 meaningful display string:
964 Widget->columns(Stringify => qw/name/);
966 If you need to do anything more complex, you can provide an
968 stringify_self() method which stringification will call:
969 sub stringify_self {
971 my $self = shift;
972 return join ":", $self->id, $self->name;
973 }
974 This overloading behaviour can be useful for columns that have has_a()
976 relationships. For example, consider a table that has price and
977 currency fields:
978 package Widget;
980 use base 'My::Class::DBI';
981 Widget->table('widget');
982 Widget->columns(All => qw/widgetid name price currency_code/);
983 $obj = Widget->retrieve($id);
985 print $obj->price . " " . $obj->currency_code;
986 The would print something like ""42.07 USD"". If the currency_code
988 field is later changed to be a foreign key to a new currency table then
989 $obj->currency_code will return an object reference instead of a plain
990 string. Without overloading the stringify operator the example would
991 now print something like ""42.07 Widget=HASH(0x1275}"" and the fix
992 would be to change the code to add a call to id():
993 print $obj->price . " " . $obj->currency_code->id;
995 However, with overloaded stringification, the original code continues
997 to work as before, with no code changes needed.
998 This makes it much simpler and safer to add relationships to existing
1000 applications, or remove them later.
1001
1003 Databases are all about relationships. Thus Class::DBI provides a way
1004 for you to set up descriptions of your relationhips.
1005 Class::DBI provides three such relationships: 'has_a', 'has_many', and
1007 'might_have'. Others are available from CPAN.
1008 has_a
1010 Music::CD->has_a(column => 'Foreign::Class');
1011 Music::CD->has_a(artist => 'Music::Artist');
1013 print $cd->artist->name;
1014 'has_a' is most commonly used to supply lookup information for a
1016 foreign key. If a column is declared as storing the primary key of
1017 another table, then calling the method for that column does not return
1018 the id, but instead the relevant object from that foreign class.
1019 It is also possible to use has_a to inflate the column value to a non
1021 Class::DBI based. A common usage would be to inflate a date field to a
1022 date/time object:
1023 Music::CD->has_a(reldate => 'Date::Simple');
1025 print $cd->reldate->format("%d %b, %Y");
1026 Music::CD->has_a(reldate => 'Time::Piece',
1028 inflate => sub { Time::Piece->strptime(shift, "%Y-%m-%d") },
1029 deflate => 'ymd',
1030 );
1031 print $cd->reldate->strftime("%d %b, %Y");
1032 If the foreign class is another Class::DBI representation retrieve is
1034 called on that class with the column value. Any other object will be
1035 instantiated either by calling new($value) or using the given 'inflate'
1036 method. If the inflate method name is a subref, it will be executed,
1037 and will be passed the value and the Class::DBI object as arguments.
1038 When the object is being written to the database the object will be
1040 deflated either by calling the 'deflate' method (if given), or by
1041 attempting to stringify the object. If the deflate method is a subref,
1042 it will be passed the Class::DBI object as an argument.
1043 *NOTE* You should not attempt to make your primary key column inflate
1045 using has_a() as bad things will happen. If you have two tables which
1046 share a primary key, consider using might_have() instead.
1047 has_many
1049 Class->has_many(method_to_create => "Foreign::Class");
1050 Music::CD->has_many(tracks => 'Music::Track');
1052 my @tracks = $cd->tracks;
1054 my $track6 = $cd->add_to_tracks({
1056 position => 6,
1057 title => 'Tomorrow',
1058 });
1059 This method declares that another table is referencing us (i.e. storing
1061 our primary key in its table).
1062 It creates a named accessor method in our class which returns a list of
1064 all the matching Foreign::Class objects.
1065 In addition it creates another method which allows a new associated
1067 object to be constructed, taking care of the linking automatically.
1068 This method is the same as the accessor method with "add_to_"
1069 prepended.
1070 The add_to_tracks example above is exactly equivalent to:
1072 my $track6 = Music::Track->insert({
1074 cd => $cd,
1075 position => 6,
1076 title => 'Tomorrow',
1077 });
1078 When setting up the relationship the foreign class's has_a()
1080 declarations are examined to discover which of its columns reference
1081 our class. (Note that because this happens at compile time, if the
1082 foreign class is defined in the same file, the class with the has_a()
1083 must be defined earlier than the class with the has_many(). If the
1084 classes are in different files, Class::DBI should usually be able to do
1085 the right things, as long as all classes inherit Class::DBI before
1086 'use'ing any other classes.)
1087 If the foreign class has no has_a() declarations linking to this class,
1089 it is assumed that the foreign key in that class is named after the
1090 moniker() of this class.
1091 If this is not true you can pass an additional third argument to the
1093 has_many() declaration stating which column of the foreign class is the
1094 foreign key to this class.
1095 Limiting
1097 Music::Artist->has_many(cds => 'Music::CD');
1099 my @cds = $artist->cds(year => 1980);
1100 When calling the method created by has_many, you can also supply any
1102 additional key/value pairs for restricting the search. The above
1103 example will only return the CDs with a year of 1980.
1104 Ordering
1106 Music::CD->has_many(tracks => 'Music::Track', { order_by => 'playorder' });
1108 has_many takes an optional final hashref of options. If an 'order_by'
1110 option is set, its value will be set in an ORDER BY clause in the SQL
1111 issued. This is passed through 'as is', enabling order_by clauses such
1112 as 'length DESC, position'.
1113 Mapping
1115 Music::CD->has_many(styles => [ 'Music::StyleRef' => 'style' ]);
1117 If the second argument to has_many is turned into a listref of the
1119 Classname and an additional method, then that method will be called in
1120 turn on each of the objects being returned.
1121 The above is exactly equivalent to:
1123 Music::CD->has_many(_style_refs => 'Music::StyleRef');
1125 sub styles {
1127 my $self = shift;
1128 return map $_->style, $self->_style_refs;
1129 }
1130 For an example of where this is useful see "MANY TO MANY RELATIONSHIPS"
1132 below.
1133 Cascading Delete
1135 Music::Artist->has_many(cds => 'Music::CD', { cascade => 'Fail' });
1137 It is also possible to control what happens to the 'child' objects when
1139 the 'parent' object is deleted. By default this is set to 'Delete' -
1140 so, for example, when you delete an artist, you also delete all their
1141 CDs, leaving no orphaned records. However you could also set this to
1142 'None', which would leave all those orphaned records (although this
1143 generally isn't a good idea), or 'Fail', which will throw an exception
1144 when you try to delete an artist that still has any CDs.
1145 You can also write your own Cascade strategies by supplying a Class
1147 Name here.
1148 For example you could write a Class::DBI::Cascade::Plugin::Nullify
1150 which would set all related foreign keys to be NULL, and plug it into
1151 your relationship:
1152 Music::Artist->has_many(cds => 'Music::CD', {
1154 cascade => 'Class::DBI::Cascade::Plugin::Nullify'
1155 });
1156 might_have
1158 Music::CD->might_have(method_name => Class => (@fields_to_import));
1159 Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);
1161 my $liner_notes_object = $cd->liner_notes;
1163 my $notes = $cd->notes; # equivalent to $cd->liner_notes->notes;
1164 might_have() is similar to has_many() for relationships that can have
1166 at most one associated objects. For example, if you have a CD database
1167 to which you want to add liner notes information, you might not want to
1168 add a 'liner_notes' column to your main CD table even though there is
1169 no multiplicity of relationship involved (each CD has at most one
1170 'liner notes' field). So, you create another table with the same
1171 primary key as this one, with which you can cross-reference.
1172 But you don't want to have to keep writing methods to turn the the
1174 'list' of liner_notes objects you'd get back from has_many into the
1175 single object you'd need. So, might_have() does this work for you. It
1176 creates an accessor to fetch the single object back if it exists, and
1177 it also allows you import any of its methods into your namespace. So,
1178 in the example above, the LinerNotes class can be mostly invisible -
1179 you can just call $cd->notes and it will call the notes method on the
1180 correct LinerNotes object transparently for you.
1181 Making sure you don't have namespace clashes is up to you, as is
1183 correctly creating the objects, but this may be made simpler in later
1184 versions. (Particularly if someone asks for this!)
1185 Notes
1187 has_a(), might_have() and has_many() check that the relevant class has
1188 already been loaded. If it hasn't then they try to load the module of
1189 the same name using require. If the require fails because it can't
1190 find the module then it will assume it's not a simple require (i.e.,
1191 Foreign::Class isn't in Foreign/Class.pm) and that you will take care
1192 of it and ignore the warning. Any other error, such as a syntax error,
1193 triggers an exception.
1194 NOTE: The two classes in a relationship do not have to be in the same
1196 database, on the same machine, or even in the same type of database! It
1197 is quite acceptable for a table in a MySQL database to be connected to
1198 a different table in an Oracle database, and for cascading delete etc
1199 to work across these. This should assist greatly if you need to migrate
1200 a database gradually.
1201
1203 Class::DBI does not currently support Many to Many relationships, per
1204 se. However, by combining the relationships that already exist it is
1205 possible to set these up.
1206 Consider the case of Films and Actors, with a linking Role table with a
1208 multi-column Primary Key. First of all set up the Role class:
1209 Role->table('role');
1211 Role->columns(Primary => qw/film actor/);
1212 Role->has_a(film => 'Film');
1213 Role->has_a(actor => 'Actor');
1214 Then, set up the Film and Actor classes to use this linking table:
1216 Film->table('film');
1218 Film->columns(All => qw/id title rating/);
1219 Film->has_many(stars => [ Role => 'actor' ]);
1220 Actor->table('actor');
1222 Actor->columns(All => qw/id name/);
1223 Actor->has_many(films => [ Role => 'film' ]);
1224 In each case the 'mapping method' variation of has_many() is used to
1226 call the lookup method on the Role object returned. As these methods
1227 are the 'has_a' relationships on the Role, these will return the actual
1228 Actor and Film objects, providing a cheap many-to-many relationship.
1229 In the case of Film, this is equivalent to the more long-winded:
1231 Film->has_many(roles => "Role");
1233 sub actors {
1235 my $self = shift;
1236 return map $_->actor, $self->roles
1237 }
1238 As this is almost exactly what is created internally, add_to_stars and
1240 add_to_films will generally do the right thing as they are actually
1241 doing the equivalent of add_to_roles:
1242 $film->add_to_actors({ actor => $actor });
1244 Similarly a cascading delete will also do the right thing as it will
1246 only delete the relationship from the linking table.
1247 If the Role table were to contain extra information, such as the name
1249 of the character played, then you would usually need to skip these
1250 short-cuts and set up each of the relationships, and associated helper
1251 methods, manually.
1252
1254 add_relationship_type
1255 The relationships described above are implemented through
1256 Class::DBI::Relationship subclasses. These are then plugged into
1257 Class::DBI through an add_relationship_type() call:
1258 __PACKAGE__->add_relationship_type(
1260 has_a => "Class::DBI::Relationship::HasA",
1261 has_many => "Class::DBI::Relationship::HasMany",
1262 might_have => "Class::DBI::Relationship::MightHave",
1263 );
1264 If is thus possible to add new relationship types, or modify the
1266 behaviour of the existing types. See Class::DBI::Relationship for more
1267 information on what is required.
1268
1270 There are several main approaches to setting up your own SQL queries:
1271 For queries which could be used to create a list of matching objects
1273 you can create a constructor method associated with this SQL and let
1274 Class::DBI do the work for you, or just inline the entire query.
1275 For more complex queries you need to fall back on the underlying
1277 Ima::DBI query mechanism. (Caveat: since Ima::DBI uses sprintf-style
1278 interpolation, you need to be careful to double any "wildcard" % signs
1279 in your queries).
1280 add_constructor
1282 __PACKAGE__->add_constructor(method_name => 'SQL_where_clause');
1283 The SQL can be of arbitrary complexity and will be turned into:
1285 SELECT (essential columns)
1287 FROM (table name)
1288 WHERE <your SQL>
1289 This will then create a method of the name you specify, which returns a
1291 list of objects as with any built in query.
1292 For example:
1294 Music::CD->add_constructor(new_music => 'year > 2000');
1296 my @recent = Music::CD->new_music;
1297 You can also supply placeholders in your SQL, which must then be
1299 specified at query time:
1300 Music::CD->add_constructor(new_music => 'year > ?');
1302 my @recent = Music::CD->new_music(2000);
1303 retrieve_from_sql
1305 On occasions where you want to execute arbitrary SQL, but don't want to
1306 go to the trouble of setting up a constructor method, you can inline
1307 the entire WHERE clause, and just get the objects back directly:
1308 my @cds = Music::CD->retrieve_from_sql(qq{
1310 artist = 'Ozzy Osbourne' AND
1311 title like "%Crazy" AND
1312 year <= 1986
1313 ORDER BY year
1314 LIMIT 2,3
1315 });
1316 Ima::DBI queries
1318 When you can't use 'add_constructor', e.g. when using aggregate
1319 functions, you can fall back on the fact that Class::DBI inherits from
1320 Ima::DBI and prefers to use its style of dealing with statements, via
1321 set_sql().
1322 The Class::DBI set_sql() method defaults to using prepare_cached()
1324 unless the $cache parameter is defined and false (see Ima::DBI docs for
1325 more information).
1326 To assist with writing SQL that is inheritable into subclasses, several
1328 additional substitutions are available here: __TABLE__, __ESSENTIAL__
1329 and __IDENTIFIER__. These represent the table name associated with the
1330 class, its essential columns, and the primary key of the current
1331 object, in the case of an instance method on it.
1332 For example, the SQL for the internal 'update' method is implemented
1334 as:
1335 __PACKAGE__->set_sql('update', <<"");
1337 UPDATE __TABLE__
1338 SET %s
1339 WHERE __IDENTIFIER__
1340 The 'longhand' version of the new_music constructor shown above would
1342 similarly be:
1343 Music::CD->set_sql(new_music => qq{
1345 SELECT __ESSENTIAL__
1346 FROM __TABLE__
1347 WHERE year > ?
1348 });
1349 For such 'SELECT' queries Ima::DBI's set_sql() method is extended to
1351 create a helper shortcut method, named by prefixing the name of the SQL
1352 fragment with 'search_'. Thus, the above call to set_sql() will
1353 automatically set up the method Music::CD->search_new_music(), which
1354 will execute this search and return the relevant objects or Iterator.
1355 (If there are placeholders in the query, you must pass the relevant
1356 arguments when calling your search method.)
1357 This does the equivalent of:
1359 sub search_new_music {
1361 my ($class, @args) = @_;
1362 my $sth = $class->sql_new_music;
1363 $sth->execute(@args);
1364 return $class->sth_to_objects($sth);
1365 }
1366 The $sth which is used to return the objects here is a normal DBI-style
1368 statement handle, so if the results can't be turned into objects
1369 easily, it is still possible to call $sth->fetchrow_array etc and
1370 return whatever data you choose.
1371 Of course, any query can be added via set_sql, including joins. So, to
1373 add a query that returns the 10 Artists with the most CDs, you could
1374 write (with MySQL):
1375 Music::Artist->set_sql(most_cds => qq{
1377 SELECT artist.id, COUNT(cd.id) AS cds
1378 FROM artist, cd
1379 WHERE artist.id = cd.artist
1380 GROUP BY artist.id
1381 ORDER BY cds DESC
1382 LIMIT 10
1383 });
1384 my @artists = Music::Artist->search_most_cds();
1386 If you also need to access the 'cds' value returned from this query,
1388 the best approach is to declare 'cds' to be a TEMP column. (See "Non-
1389 Persistent Fields" below).
1390 Class::DBI::AbstractSearch
1392 my @music = Music::CD->search_where(
1393 artist => [ 'Ozzy', 'Kelly' ],
1394 status => { '!=', 'outdated' },
1395 );
1396 The Class::DBI::AbstractSearch module, available from CPAN, is a plugin
1398 for Class::DBI that allows you to write arbitrarily complex searches
1399 using perl data structures, rather than SQL.
1400 Single Value SELECTs
1402 select_val
1403 Selects which only return a single value can couple Class::DBI's
1405 sql_single() SQL, with the $sth->select_val() call which we get from
1406 DBIx::ContextualFetch.
1407 __PACKAGE__->set_sql(count_all => "SELECT COUNT(*) FROM __TABLE__");
1409 # .. then ..
1410 my $count = $class->sql_count_all->select_val;
1411 This can also take placeholders and/or do column interpolation if
1413 required:
1414 __PACKAGE__->set_sql(count_above => q{
1416 SELECT COUNT(*) FROM __TABLE__ WHERE %s > ?
1417 });
1418 # .. then ..
1419 my $count = $class->sql_count_above('year')->select_val(2001);
1420 sql_single
1422 Internally Class::DBI defines a very simple SQL fragment called
1424 'single':
1425 "SELECT %s FROM __TABLE__".
1427 This is used to implement the above Class->count_all():
1429 $class->sql_single("COUNT(*)")->select_val;
1431 This interpolates the COUNT(*) into the %s of the SQL, and then
1433 executes the query, returning a single value.
1434 Any SQL set up via set_sql() can of course be supplied here, and
1436 select_val can take arguments for any placeholders there.
1437 Internally several helper methods are defined using this approach:
1439 - count_all
1441 - maximum_value_of($column)
1442 - minimum_value_of($column)
1443
1445 In the tradition of Perl, Class::DBI is lazy about how it loads your
1446 objects. Often, you find yourself using only a small number of the
1447 available columns and it would be a waste of memory to load all of them
1448 just to get at two, especially if you're dealing with large numbers of
1449 objects simultaneously.
1450 You should therefore group together your columns by typical usage, as
1452 fetching one value from a group can also pre-fetch all the others in
1453 that group for you, for more efficient access.
1454 So for example, if we usually fetch the artist and title, but don't use
1456 the 'year' so much, then we could say the following:
1457 Music::CD->columns(Primary => qw/cdid/);
1459 Music::CD->columns(Essential => qw/artist title/);
1460 Music::CD->columns(Others => qw/year runlength/);
1461 Now when you fetch back a CD it will come pre-loaded with the 'cdid',
1463 'artist' and 'title' fields. Fetching the 'year' will mean another
1464 visit to the database, but will bring back the 'runlength' whilst it's
1465 there.
1466 This can potentially increase performance.
1468 If you don't like this behavior, then just add all your columns to the
1470 Essential group, and Class::DBI will load everything at once. If you
1471 have a single column primary key you can do this all in one shot with
1472 one single column declaration:
1473 Music::CD->columns(Essential => qw/cdid artist title year runlength/);
1475 columns
1477 my @all_columns = $class->columns;
1478 my @columns = $class->columns($group);
1479 my @primary = $class->primary_columns;
1481 my $primary = $class->primary_column;
1482 my @essential = $class->_essential;
1483 There are four 'reserved' groups: 'All', 'Essential', 'Primary' and
1485 'TEMP'.
1486 'All' are all columns used by the class. If not set it will be created
1488 from all the other groups.
1489 'Primary' is the primary key columns for this class. It must be set
1491 before objects can be used.
1492 If 'All' is given but not 'Primary' it will assume the first column in
1494 'All' is the primary key.
1495 'Essential' are the minimal set of columns needed to load and use the
1497 object. Only the columns in this group will be loaded when an object is
1498 retrieve()'d. It is typically used to save memory on a class that has a
1499 lot of columns but where only use a few of them are commonly used. It
1500 will automatically be set to 'Primary' if not explicitly set. The
1501 'Primary' column is always part of the 'Essential' group.
1502 For simplicity primary_columns(), primary_column(), and _essential()
1504 methods are provided to return these. The primary_column() method
1505 should only be used for tables that have a single primary key column.
1506 Non-Persistent Fields
1508 Music::CD->columns(TEMP => qw/nonpersistent/);
1509 If you wish to have fields that act like columns in every other way,
1511 but that don't actually exist in the database (and thus will not
1512 persist), you can declare them as part of a column group of 'TEMP'.
1513 find_column
1515 Class->find_column($column);
1516 $obj->find_column($column);
1517 The columns of a class are stored as Class::DBI::Column objects. This
1519 method will return you the object for the given column, if it exists.
1520 This is most useful either in a boolean context to discover if the
1521 column exists, or to 'normalize' a user-entered column name to an
1522 actual Column.
1523 The interface of the Column object itself is still under development,
1525 so you shouldn't really rely on anything internal to it.
1526
1528 Class::DBI suffers from the usual problems when dealing with
1529 transactions. In particular, you should be very wary when committing
1530 your changes that you may actually be in a wider scope than expected
1531 and that your caller may not be expecting you to commit.
1532 However, as long as you are aware of this, and try to keep the scope of
1534 your transactions small, ideally always within the scope of a single
1535 method, you should be able to work with transactions with few problems.
1536 dbi_commit / dbi_rollback
1538 $obj->dbi_commit();
1539 $obj->dbi_rollback();
1540 These are thin aliases through to the DBI's commit() and rollback()
1542 commands to commit or rollback all changes to this object.
1543 Localised Transactions
1545 A nice idiom for turning on a transaction locally (with AutoCommit
1546 turned on globally) (courtesy of Dominic Mitchell) is:
1547 sub do_transaction {
1549 my $class = shift;
1550 my ( $code ) = @_;
1551 # Turn off AutoCommit for this scope.
1552 # A commit will occur at the exit of this block automatically,
1553 # when the local AutoCommit goes out of scope.
1554 local $class->db_Main->{ AutoCommit };
1555 # Execute the required code inside the transaction.
1557 eval { $code->() };
1558 if ( $@ ) {
1559 my $commit_error = $@;
1560 eval { $class->dbi_rollback }; # might also die!
1561 die $commit_error;
1562 }
1563 }
1564 And then you just call:
1566 Music::DBI->do_transaction( sub {
1568 my $artist = Music::Artist->insert({ name => 'Pink Floyd' });
1569 my $cd = $artist->add_to_cds({
1570 title => 'Dark Side Of The Moon',
1571 year => 1974,
1572 });
1573 });
1574 Now either both will get added, or the entire transaction will be
1576 rolled back.
1577
1579 Class::DBI supports uniqueness of objects in memory. In a given perl
1580 interpreter there will only be one instance of any given object at one
1581 time. Many variables may reference that object, but there can be only
1582 one.
1583 Here's an example to illustrate:
1585 my $artist1 = Music::Artist->insert({ artistid => 7, name => 'Polysics' });
1587 my $artist2 = Music::Artist->retrieve(7);
1588 my $artist3 = Music::Artist->search( name => 'Polysics' )->first;
1589 Now $artist1, $artist2, and $artist3 all point to the same object. If
1591 you update a property on one of them, all of them will reflect the
1592 update.
1593 This is implemented using a simple object lookup index for all live
1595 objects in memory. It is not a traditional cache - when your objects go
1596 out of scope, they will be destroyed normally, and a future retrieve
1597 will instantiate an entirely new object.
1598 The ability to perform this magic for you replies on your perl having
1600 access to the Scalar::Util::weaken function. Although this is part of
1601 the core perl distribution, some vendors do not compile support for it.
1602 To find out if your perl has support for it, you can run this on the
1603 command line:
1604 perl -e 'use Scalar::Util qw(weaken)'
1606 If you get an error message about weak references not being
1608 implemented, Class::DBI will not maintain this lookup index, but give
1609 you a separate instances for each retrieve.
1610 A few new tools are offered for adjusting the behavior of the object
1612 index. These are still somewhat experimental and may change in a future
1613 release.
1614 remove_from_object_index
1616 $artist->remove_from_object_index();
1617 This is an object method for removing a single object from the live
1619 objects index. You can use this if you want to have multiple distinct
1620 copies of the same object in memory.
1621 clear_object_index
1623 Music::DBI->clear_object_index();
1624 You can call this method on any class or instance of Class::DBI, but
1626 the effect is universal: it removes all objects from the index.
1627 purge_object_index_every
1629 Music::Artist->purge_object_index_every(2000);
1630 Weak references are not removed from the index when an object goes out
1632 of scope. This means that over time the index will grow in memory.
1633 This is really only an issue for long-running environments like
1634 mod_perl, but every so often dead references are cleaned out to prevent
1635 this. By default, this happens every 1000 object loads, but you can
1636 change that default for your class by setting the
1637 'purge_object_index_every' value.
1638 (Eventually this may handled in the DESTROY method instead.)
1640 As a final note, keep in mind that you can still have multiple distinct
1642 copies of an object in memory if you have multiple perl interpreters
1643 running. CGI, mod_perl, and many other common usage situations run
1644 multiple interpreters, meaning that each one of them may have an
1645 instance of an object representing the same data. However, this is no
1646 worse than it was before, and is entirely normal for database
1647 applications in multi-process environments.
1648
1650 The preferred method of interacting with Class::DBI is for you to write
1651 a subclass for your database connection, with each table-class
1652 inheriting in turn from it.
1653 As well as encapsulating the connection information in one place, this
1655 also allows you to override default behaviour or add additional
1656 functionality across all of your classes.
1657 As the innards of Class::DBI are still in flux, you must exercise
1659 extreme caution in overriding private methods of Class::DBI (those
1660 starting with an underscore), unless they are explicitly mentioned in
1661 this documentation as being safe to override. If you find yourself
1662 needing to do this, then I would suggest that you ask on the mailing
1663 list about it, and we'll see if we can either come up with a better
1664 approach, or provide a new means to do whatever you need to do.
1665
1667 Multi-Column Foreign Keys are not supported
1668 You can't currently add a relationship keyed on multiple columns. You
1669 could, however, write a Relationship plugin to do this, and the world
1670 would be eternally grateful...
1671 Don't change or inflate the value of your primary columns
1673 Altering your primary key column currently causes Bad Things to happen.
1674 I should really protect against this.
1675
1677 Theoretically Class::DBI should work with almost any standard RDBMS. Of
1678 course, in the real world, we know that that's not true. It is known to
1679 work with MySQL, PostgreSQL, Oracle and SQLite, each of which have
1680 their own additional subclass on CPAN that you should explore if you're
1681 using them:
1682 L<Class::DBI::mysql>, L<Class::DBI::Pg>, L<Class::DBI::Oracle>,
1684 L<Class::DBI::SQLite>
1685 For the most part it's been reported to work with Sybase, although
1687 there are some issues with multi-case column/table names. Beyond that
1688 lies The Great Unknown(tm). If you have access to other databases,
1689 please give this a test run, and let me know the results.
1690 Ima::DBI (and hence Class::DBI) requires a database that supports table
1692 aliasing and a DBI driver that supports placeholders. This means it
1693 won't work with older releases of DBD::AnyData (and any releases of its
1694 predecessor DBD::RAM), and DBD::Sybase + FreeTDS may or may not work
1695 depending on your FreeTDS version.
1696
1698 Tony Bowden
1699
1701 Michael G Schwern
1702
1704 Tim Bunce, Tatsuhiko Miyagawa, Perrin Harkins, Alexander Karelas, Barry
1705 Hoggard, Bart Lateur, Boris Mouzykantskii, Brad Bowman, Brian Parker,
1706 Casey West, Charles Bailey, Christopher L. Everett Damian Conway, Dan
1707 Thill, Dave Cash, David Jack Olrik, Dominic Mitchell, Drew Taylor, Drew
1708 Wilson, Jay Strauss, Jesse Sheidlower, Jonathan Swartz, Marty Pauley,
1709 Michael Styer, Mike Lambert, Paul Makepeace, Phil Crow, Richard
1710 Piacentini, Simon Cozens, Simon Wilcox, Thomas Klausner, Tom Renfro,
1711 Uri Gutman, William McKee, the Class::DBI mailing list, the POOP group,
1712 and all the others who've helped, but that I've forgetten to mention.
1713
1715 Class::DBI now uses a three-level versioning system. This release, for
1716 example, is version 3.0.17
1717 The general approach to releases will be that users who like a degree
1719 of stability can hold off on upgrades until the major sub-version
1720 increases (e.g. 3.1.0). Those who like living more on the cutting edge
1721 can keep up to date with minor sub-version releases.
1722 Functionality which was introduced during a minor sub-version release
1724 may disappear without warning in a later minor sub-version release.
1725 I'll try to avoid doing this, and will aim to have a deprecation cycle
1726 of at least a few minor sub-versions, but you should keep a close eye
1727 on the CHANGES file, and have good tests in place. (This is good advice
1728 generally, of course.) Anything that is in a major sub-version release
1729 will go through a deprecation cycle of at least one further major sub-
1730 version before it is removed (and usually longer).
1731 Getting changes accepted
1733 There is an active Class::DBI community, however I am not part of it.
1734 I am not on the mailing list, and I don't follow the wiki. I also do
1735 not follow Perl Monks or CPAN reviews or annoCPAN or whatever the tool
1736 du jour happens to be.
1737 If you find a problem with Class::DBI, by all means discuss it in any
1739 of these places, but don't expect anything to happen unless you
1740 actually tell me about it.
1741 The preferred method for doing this is via the CPAN RT interface, which
1743 you can access at http://rt.cpan.org/ or by emailing
1744 bugs-Class-DBI@rt.cpan.org
1745 If you email me personally about Class::DBI issues, then I will
1747 probably bounce them on to there, unless you specifically ask me not
1748 to. Otherwise I can't keep track of what all needs fixed. (This of
1749 course means that if you ask me not to send your mail to RT, there's a
1750 much higher chance that nothing will every happen about your problem).
1751 Bug Reports
1753 If you're reporting a bug then it has a much higher chance of getting
1754 fixed quicker if you can include a failing test case. This should be a
1755 completely stand-alone test that could be added to the Class::DBI
1756 distribution. That is, it should use Test::Simple or Test::More, fail
1757 with the current code, but pass when I fix the problem. If it needs to
1758 have a working database to show the problem, then this should
1759 preferably use SQLite, and come with all the code to set this up. The
1760 nice people on the mailing list will probably help you out if you need
1761 assistance putting this together.
1762 You don't need to include code for actually fixing the problem, but of
1764 course it's often nice if you can. I may choose to fix it in a
1765 different way, however, so it's often better to ask first whether I'd
1766 like a patch, particularly before spending a lot of time hacking.
1767 Patches
1769 If you are sending patches, then please send either the entire code
1770 that is being changed or the output of 'diff -Bub'. Please also note
1771 what version the patch is against. I tend to apply all patches
1772 manually, so I'm more interested in being able to see what you're doing
1773 than in being able to apply the patch cleanly. Code formatting isn't an
1774 issue, as I automagically run perltidy against the source after any
1775 changes, so please format for clarity.
1776 Patches have a much better chance of being applied if they are small.
1778 People often think that it's better for me to get one patch with a
1779 bunch of fixes. It's not. I'd much rather get 100 small patches that
1780 can be applied one by one. A change that I can make and release in five
1781 minutes is always better than one that needs a couple of hours to
1782 ponder and work through.
1783 I often reject patches that I don't like. Please don't take it
1785 personally. I also like time to think about the wider implications of
1786 changes. Often a lot of time. Feel free to remind me about things that
1787 I may have forgotten about, but as long as they're on rt.cpan.org I
1788 will get around to them eventually.
1789 Feature Requests
1791 Wish-list requests are fine, although you should probably discuss them
1792 on the mailing list (or equivalent) with others first. There's quite
1793 often a plugin somewhere that already does what you want.
1794 In general I am much more open to discussion on how best to provide the
1796 flexibility for you to make your Cool New Feature(tm) a plugin rather
1797 than adding it to Class::DBI itself.
1798 For the most part the core of Class::DBI already has most of the
1800 functionality that I believe it will ever need (and some more besides,
1801 that will probably be split off at some point). Most other things are
1802 much better off as plugins, with a separate life on CPAN or elsewhere
1803 (and with me nowhere near the critical path). Most of the ongoing work
1804 on Class::DBI is about making life easier for people to write
1805 extensions - whether they're local to your own codebase or released for
1806 wider consumption.
1807
1809 Support for Class::DBI is mostly via the mailing list.
1810 To join the list, or read the archives, visit
1812 http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
1813 There is also a Class::DBI wiki at
1815 http://www.class-dbi.com/
1816 The wiki contains much information that should probably be in these
1818 docs but isn't yet. (See above if you want to help to rectify this.)
1819 As mentioned above, I don't follow the list or the wiki, so if you want
1821 to contact me individually, then you'll have to track me down
1822 personally.
1823 There are lots of 3rd party subclasses and plugins available. For a
1825 list of the ones on CPAN see:
1826 http://search.cpan.org/search?query=Class%3A%3ADBI&mode=module
1827 An article on Class::DBI was published on Perl.com a while ago. It's
1829 slightly out of date , but it's a good introduction:
1830 http://www.perl.com/pub/a/2002/11/27/classdbi.html
1831 The wiki has numerous references to other articles, presentations etc.
1833 http://poop.sourceforge.net/ provides a document comparing a variety of
1835 different approaches to database persistence, such as Class::DBI,
1836 Alazabo, Tangram, SPOPS etc.
1837
1839 This library is free software; you can redistribute it and/or modify it
1840 under the same terms as Perl itself.
1841
1843 Class::DBI is built on top of Ima::DBI, DBIx::ContextualFetch,
1844 Class::Accessor and Class::Data::Inheritable. The innards and much of
1845 the interface are easier to understand if you have an idea of how they
1846 all work as well.
1847perl v5.32.0 2020-07-28 Class::DBI(3)