1Class::DBI(3)         User Contributed Perl Documentation        Class::DBI(3)
2
3
4

NAME

6       Class::DBI - Simple Database Abstraction
7

SYNOPSIS

9         package Music::DBI;
10         use base 'Class::DBI';
11         Music::DBI->connection('dbi:mysql:dbname', 'username', 'password');
12
13         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
19         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
30         Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);
31
32         package Music::Track;
33         use base 'Music::DBI';
34         Music::Track->table('track');
35         Music::Track->columns(All => qw/trackid cd position title/);
36
37         #-- Meanwhile, in a nearby piece of code! --#
38
39         my $artist = Music::Artist->insert({ artistid => 1, name => 'U2' });
40
41         my $cd = $artist->add_to_cds({
42           cdid   => 1,
43           title  => 'October',
44           year   => 1980,
45         });
46
47         # Oops, got it wrong.
48         $cd->year(1981);
49         $cd->update;
50
51         # etc.
52
53         foreach my $track ($cd->tracks) {
54           print $track->position, $track->title
55         }
56
57         $cd->delete; # also deletes the tracks
58
59         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

INTRODUCTION

65       Class::DBI provides a convenient abstraction layer to a database.
66
67       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
72       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
77       In short, Class::DBI aims to make it simple to introduce 'best
78       practice' when dealing with data stored in a relational database.
79
80   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
87       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
92           Using our CD example, you might declare a table something like
93           this:
94
95             CREATE TABLE cd (
96               cdid   INTEGER   PRIMARY KEY,
97               artist INTEGER, # references 'artist'
98               title  VARCHAR(255),
99               year   CHAR(4),
100             );
101
102       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
109             package Music::DBI;
110             use base 'Class::DBI';
111
112       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
117             Music::DBI->connection('dbi:mysql:dbname', 'user', 'password');
118
119           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
122       Set up each Class
123             package Music::CD;
124             use base 'Music::DBI';
125
126           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
130       Declare the name of your table
131           Inform Class::DBI what table you are using for this class:
132
133             Music::CD->table('cd');
134
135       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
140             Music::CD->columns(All => qw/cdid artist title year/);
141
142           If the primary key of your table spans multiple columns then
143           declare them using a separate call to columns() like this:
144
145             Music::CD->columns(Primary => qw/pk1 pk2/);
146             Music::CD->columns(Others => qw/foo bar baz/);
147
148           For more information about how you can more efficiently use subsets
149           of your columns, see "LAZY POPULATION"
150
151       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
157       Let's look at all that in more detail:
158

CLASS METHODS

160   connection
161         __PACKAGE__->connection($data_source, $user, $password, \%attr);
162
163       This sets up a database connection with the given information.
164
165       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
169         package Music::DBI;
170         use base 'Class::DBI';
171
172         Music::DBI->connection('dbi:foo:dbname', 'user', 'password');
173
174         package My::Other::Table;
175         use base 'Music::DBI';
176
177       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
182         FetchHashKeyName   => 'NAME_lc',
183         ShowErrorStatement => 1,
184         ChopBlanks         => 1,
185         AutoCommit         => 1,
186
187       (Except for Oracle and Pg, where AutoCommit defaults 0, placing the
188       database in transactional mode).
189
190       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
193         Music::DBI->connection(dbi:foo:dbname','user','pass',{ChopBlanks=>0});
194
195       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
199       Dynamic Database Connections / db_Main
200
201       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
205       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
213       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
217   table
218         __PACKAGE__->table($table);
219
220         $table = Class->table;
221         $table = $obj->table;
222
223       An accessor to get/set the name of the database table in which this
224       class is stored.  It -must- be set.
225
226       Table information is inherited by subclasses, but can be overridden.
227
228   table_alias
229         package Shop::Order;
230         __PACKAGE__->table('orders');
231         __PACKAGE__->table_alias('orders');
232
233       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
239       This can also be passed as a second argument to 'table':
240
241         __PACKAGE__->table('orders', 'orders');
242
243       As with table, this is inherited but can be overridden.
244
245   sequence / auto_increment
246         __PACKAGE__->sequence($sequence_name);
247
248         $sequence_name = Class->sequence;
249         $sequence_name = $obj->sequence;
250
251       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
255         __PACKAGE__->columns(Primary => 'id');
256         __PACKAGE__->sequence('class_id_seq');
257
258       Class::DBI will use the sequence to generate a primary key value when
259       objects are inserted without one.
260
261       *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
265       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
269       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

CONSTRUCTORS and DESTRUCTORS

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
278   insert
279         my $obj = Class->insert(\%data);
280
281       This is a constructor to insert new data into the database and create
282       an object representing the newly inserted row.
283
284       %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
288         my $cd = Music::CD->insert({
289           cdid   => 1,
290           artist => $artist,
291           title  => 'October',
292           year   => 1980,
293         });
294
295       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
301       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
307       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
311       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
315       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
321       The "after_create" trigger is invoked after the database insert has
322       executed.
323
324   find_or_create
325         my $cd = Music::CD->find_or_create({ artist => 'U2', title => 'Boy' });
326
327       This checks if a CD can be found to match the information passed, and
328       if not inserts it.
329
330   delete
331         $obj->delete;
332         Music::CD->search(year => 1980, title => 'Greatest %')->delete_all;
333
334       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
339       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
343       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

RETRIEVING OBJECTS

349       Class::DBI provides a few very simple search methods.
350
351       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
354       Class::DBI::AbstractSearch, available on CPAN, provides a much more
355       complex search interface than Class::DBI provides itself.
356
357   retrieve
358         $obj = Class->retrieve( $id );
359         $obj = Class->retrieve( %key_values );
360
361       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
366         my $cd = Music::CD->retrieve(1) or die "No such cd";
367
368   retrieve_all
369         my @objs = Class->retrieve_all;
370         my $iterator = Class->retrieve_all;
371
372       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
375   search
376         @objs = Class->search(column1 => $value, column2 => $value ...);
377
378       This is a simple search for all objects where the columns specified are
379       equal to the values specified e.g.:
380
381         @cds = Music::CD->search(year => 1990);
382         @cds = Music::CD->search(title => "Greatest Hits", year => 1990);
383
384       You may also specify the sort order of the results by adding a final
385       hash of arguments with the key 'order_by':
386
387         @cds = Music::CD->search(year => 1990, { order_by=>'artist' });
388
389       This is passed through 'as is', enabling order_by clauses such as 'year
390       DESC, title'.
391
392   search_like
393         @objs = Class->search_like(column1 => $like_pattern, ....);
394
395       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
400         @cds = Music::CD->search_like(title => 'October%');
401         @cds = Music::CD->search_like(title => 'Hits%', artist => 'Various%');
402
403       You can also use 'order_by' with these, as with search().
404

ITERATORS

406         my $it = Music::CD->search_like(title => 'October%');
407         while (my $cd = $it->next) {
408           print $cd->title;
409         }
410
411       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
416       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
422       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
427   Subclassing the Iterator
428         Music::CD->iterator_class('Music::CD::Iterator');
429
430       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
434   QUICK RETRIEVAL
435         my $obj = Class->construct(\%data);
436
437       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
442       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
447          return map $class->construct($_), $sth->fetchall_hash;
448
449       The construct() method creates a new empty object, loads in the column
450       values, and then invokes the "select" trigger.
451

COPY AND MOVE

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
458       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
462       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
466       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
471         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
477   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
482       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

TRIGGERS

490         __PACKAGE__->add_trigger(trigger_point_name => \&code_to_execute);
491
492         # e.g.
493
494         __PACKAGE__->add_trigger(after_create  => \&call_after_create);
495
496       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
499         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
509       You can create any number of triggers for each point, but you cannot
510       specify the order in which they will be run.
511
512       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
517       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

CONSTRAINTS

522         __PACKAGE__->add_constraint('name', column => \&check_sub);
523
524         # e.g.
525
526         __PACKAGE__->add_constraint('over18', age => \&check_age);
527
528         # Simple version
529         sub check_age {
530           my ($value) = @_;
531           return $value >= 18;
532         }
533
534         # 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
544       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
548       The constraint code is called with four parameters:
549
550         - 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
558       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
563       The exception thrown has its data set to a hashref of the column being
564       changed and the value being changed to.
565
566       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
571       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
575   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
580       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
586       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

DATA NORMALIZATION

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
600       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
605       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

DATA VALIDATION

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
615       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
619       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
625       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

EXCEPTIONS

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
635       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
639       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
643         use Error;
644
645         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
654       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
660       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
666       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

WARNINGS

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

INSTANCE METHODS

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
687   the fundamental set() and get() methods
688         $value = $obj->get($column_name);
689         @values = $obj->get(@column_names);
690
691         $obj->set($column_name => $value);
692         $obj->set($col1 => $value1, $col2 => $value2 ... );
693
694       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
699       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
704       The "after_set_$column" trigger is invoked after the new value has been
705       stored.
706
707       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

Changing Your Column Accessor Method Names

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
717       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
722               my $meta_col = Class::DBI::Column->new(meta_info => {
723                       accessor => 'metadata',
724               });
725
726         __PACKAGE__->columns(All => qw/id name/, $meta_col);
727
728       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
732       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
738         sub accessor_name_for {
739           my ($class, $column) = @_;
740           $column =~ s/^customer//;
741           return $column;
742         }
743
744       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
748         sub mutator_name_for {
749           my ($class, $column) = @_;
750           return "set_" . $column->accessor;
751         }
752
753       If you override the mutator name, then the accessor method will be
754       enforced as read-only, and the mutator as write-only.
755
756   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
763       This is an example of manual updating:
764
765         # 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
772       And of autoupdating:
773
774         # Turn autoupdating on for this object.
775         $gone->autoupdate(1);
776
777         # Each accessor call causes the new value to immediately be written.
778         $gone->NumExplodingSheep(5);
779         $gone->Rating('NC-17');
780
781       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
786       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
790   autoupdate
791         __PACKAGE__->autoupdate($on_or_off);
792         $update_style = Class->autoupdate;
793
794         $obj->autoupdate($on_or_off);
795         $update_style = $obj->autoupdate;
796
797       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
802       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
807         __PACKAGE__->autoupdate(1);     # Autoupdate is now on for the class.
808
809         $obj = Class->retrieve('Aliens Cut My Hair');
810         $obj->autoupdate(0);      # Shut off autoupdating for this object.
811
812       The update setting for an object is not stored in the database.
813
814   update
815         $obj->update;
816
817       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
822       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
826       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
832       When update() is called the "before_update"($self) trigger is always
833       invoked immediately.
834
835       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
839       The trigger code can modify the discard_columns array to affect which
840       columns are discarded.
841
842       For example:
843
844         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
853       Take care to not delete a primary key column unless you know what
854       you're doing.
855
856       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
860       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
864   discard_changes
865         $obj->discard_changes;
866
867       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
870       If you're using autoupdate this method will throw an exception.
871
872   is_changed
873         my $changed = $obj->is_changed;
874         my @changed_keys = $obj->is_changed;
875
876       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
881   id
882         $id = $obj->id;
883         @id = $obj->id;
884
885       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
889       A warning will be generated if this method is used in scalar context on
890       a table with a multi-column primary key.
891
892   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
898       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
904       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
909       _attrs
910             @values = $object->_attrs(@cols);
911
912           Returns the values for one or more keys.
913
914       _attribute_store
915             $object->_attribute_store( { $col0 => $val0, $col1 => $val1 } );
916             $object->_attribute_store($col0, $val0, $col1, $val1);
917
918           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
923       _attribute_set
924             $object->_attribute_set( { $col0 => $val0, $col1 => $val1 } );
925             $object->_attribute_set($col0, $val0, $col1, $val1);
926
927           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
932       _attribute_delete
933             @values = $object->_attribute_delete(@cols);
934
935           Deletes values from the object, and returns the deleted values.
936
937       _attribute_exists
938             $bool = $object->_attribute_exists($col);
939
940           Returns a true value if the object contains a value for the
941           specified column, and a false value otherwise.
942
943       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
947   OVERLOADED OPERATORS
948       Class::DBI and its subclasses overload the perl builtin stringify and
949       bool operators. This is a significant convenience.
950
951       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
956       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
960       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
965         Widget->columns(Stringify => qw/name/);
966
967       If you need to do anything more complex, you can provide an
968       stringify_self() method which stringification will call:
969
970         sub stringify_self {
971           my $self = shift;
972           return join ":", $self->id, $self->name;
973         }
974
975       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
979         package Widget;
980         use base 'My::Class::DBI';
981         Widget->table('widget');
982         Widget->columns(All => qw/widgetid name price currency_code/);
983
984         $obj = Widget->retrieve($id);
985         print $obj->price . " " . $obj->currency_code;
986
987       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
994         print $obj->price . " " . $obj->currency_code->id;
995
996       However, with overloaded stringification, the original code continues
997       to work as before, with no code changes needed.
998
999       This makes it much simpler and safer to add relationships to existing
1000       applications, or remove them later.
1001

TABLE RELATIONSHIPS

1003       Databases are all about relationships. Thus Class::DBI provides a way
1004       for you to set up descriptions of your relationhips.
1005
1006       Class::DBI provides three such relationships: 'has_a', 'has_many', and
1007       'might_have'. Others are available from CPAN.
1008
1009   has_a
1010         Music::CD->has_a(column => 'Foreign::Class');
1011
1012         Music::CD->has_a(artist => 'Music::Artist');
1013         print $cd->artist->name;
1014
1015       '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
1020       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
1024         Music::CD->has_a(reldate => 'Date::Simple');
1025         print $cd->reldate->format("%d %b, %Y");
1026
1027         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
1033       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
1039       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
1044       *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
1048   has_many
1049         Class->has_many(method_to_create => "Foreign::Class");
1050
1051         Music::CD->has_many(tracks => 'Music::Track');
1052
1053         my @tracks = $cd->tracks;
1054
1055         my $track6 = $cd->add_to_tracks({
1056           position => 6,
1057           title    => 'Tomorrow',
1058         });
1059
1060       This method declares that another table is referencing us (i.e. storing
1061       our primary key in its table).
1062
1063       It creates a named accessor method in our class which returns a list of
1064       all the matching Foreign::Class objects.
1065
1066       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
1071       The add_to_tracks example above is exactly equivalent to:
1072
1073         my $track6 = Music::Track->insert({
1074           cd       => $cd,
1075           position => 6,
1076           title    => 'Tomorrow',
1077         });
1078
1079       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
1088       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
1092       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
1096       Limiting
1097
1098         Music::Artist->has_many(cds => 'Music::CD');
1099         my @cds = $artist->cds(year => 1980);
1100
1101       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
1105       Ordering
1106
1107         Music::CD->has_many(tracks => 'Music::Track', { order_by => 'playorder' });
1108
1109       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
1114       Mapping
1115
1116         Music::CD->has_many(styles => [ 'Music::StyleRef' => 'style' ]);
1117
1118       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
1122       The above is exactly equivalent to:
1123
1124         Music::CD->has_many(_style_refs => 'Music::StyleRef');
1125
1126         sub styles {
1127           my $self = shift;
1128           return map $_->style, $self->_style_refs;
1129         }
1130
1131       For an example of where this is useful see "MANY TO MANY RELATIONSHIPS"
1132       below.
1133
1134       Cascading Delete
1135
1136         Music::Artist->has_many(cds => 'Music::CD', { cascade => 'Fail' });
1137
1138       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
1146       You can also write your own Cascade strategies by supplying a Class
1147       Name here.
1148
1149       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
1153         Music::Artist->has_many(cds => 'Music::CD', {
1154           cascade => 'Class::DBI::Cascade::Plugin::Nullify'
1155         });
1156
1157   might_have
1158         Music::CD->might_have(method_name => Class => (@fields_to_import));
1159
1160         Music::CD->might_have(liner_notes => LinerNotes => qw/notes/);
1161
1162         my $liner_notes_object = $cd->liner_notes;
1163         my $notes = $cd->notes; # equivalent to $cd->liner_notes->notes;
1164
1165       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
1173       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
1182       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
1186   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
1195       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

MANY TO MANY RELATIONSHIPS

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
1207       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
1210         Role->table('role');
1211         Role->columns(Primary => qw/film actor/);
1212         Role->has_a(film => 'Film');
1213         Role->has_a(actor => 'Actor');
1214
1215       Then, set up the Film and Actor classes to use this linking table:
1216
1217         Film->table('film');
1218         Film->columns(All => qw/id title rating/);
1219         Film->has_many(stars => [ Role => 'actor' ]);
1220
1221         Actor->table('actor');
1222         Actor->columns(All => qw/id name/);
1223         Actor->has_many(films => [ Role => 'film' ]);
1224
1225       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
1230       In the case of Film, this is equivalent to the more long-winded:
1231
1232         Film->has_many(roles => "Role");
1233
1234         sub actors {
1235           my $self = shift;
1236           return map $_->actor, $self->roles
1237         }
1238
1239       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
1243         $film->add_to_actors({ actor => $actor });
1244
1245       Similarly a cascading delete will also do the right thing as it will
1246       only delete the relationship from the linking table.
1247
1248       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

ADDING NEW RELATIONSHIP TYPES

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
1259         __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
1265       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

DEFINING SQL STATEMENTS

1270       There are several main approaches to setting up your own SQL queries:
1271
1272       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
1276       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
1281   add_constructor
1282         __PACKAGE__->add_constructor(method_name => 'SQL_where_clause');
1283
1284       The SQL can be of arbitrary complexity and will be turned into:
1285
1286         SELECT (essential columns)
1287           FROM (table name)
1288          WHERE <your SQL>
1289
1290       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
1293       For example:
1294
1295         Music::CD->add_constructor(new_music => 'year > 2000');
1296         my @recent = Music::CD->new_music;
1297
1298       You can also supply placeholders in your SQL, which must then be
1299       specified at query time:
1300
1301         Music::CD->add_constructor(new_music => 'year > ?');
1302         my @recent = Music::CD->new_music(2000);
1303
1304   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
1309         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
1317   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
1323       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
1327       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
1333       For example, the SQL for the internal 'update' method is implemented
1334       as:
1335
1336         __PACKAGE__->set_sql('update', <<"");
1337           UPDATE __TABLE__
1338           SET    %s
1339           WHERE  __IDENTIFIER__
1340
1341       The 'longhand' version of the new_music constructor shown above would
1342       similarly be:
1343
1344         Music::CD->set_sql(new_music => qq{
1345           SELECT __ESSENTIAL__
1346             FROM __TABLE__
1347            WHERE year > ?
1348         });
1349
1350       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
1358       This does the equivalent of:
1359
1360         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
1367       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
1372       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
1376         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
1385         my @artists = Music::Artist->search_most_cds();
1386
1387       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
1391   Class::DBI::AbstractSearch
1392         my @music = Music::CD->search_where(
1393           artist => [ 'Ozzy', 'Kelly' ],
1394           status => { '!=', 'outdated' },
1395         );
1396
1397       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
1401   Single Value SELECTs
1402       select_val
1403
1404       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
1408         __PACKAGE__->set_sql(count_all => "SELECT COUNT(*) FROM __TABLE__");
1409         # .. then ..
1410         my $count = $class->sql_count_all->select_val;
1411
1412       This can also take placeholders and/or do column interpolation if
1413       required:
1414
1415         __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
1421       sql_single
1422
1423       Internally Class::DBI defines a very simple SQL fragment called
1424       'single':
1425
1426         "SELECT %s FROM __TABLE__".
1427
1428       This is used to implement the above Class->count_all():
1429
1430         $class->sql_single("COUNT(*)")->select_val;
1431
1432       This interpolates the COUNT(*) into the %s of the SQL, and then
1433       executes the query, returning a single value.
1434
1435       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
1438       Internally several helper methods are defined using this approach:
1439
1440       - count_all
1441       - maximum_value_of($column)
1442       - minimum_value_of($column)
1443

LAZY POPULATION

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
1451       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
1455       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
1458         Music::CD->columns(Primary   => qw/cdid/);
1459         Music::CD->columns(Essential => qw/artist title/);
1460         Music::CD->columns(Others    => qw/year runlength/);
1461
1462       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
1467       This can potentially increase performance.
1468
1469       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
1474         Music::CD->columns(Essential => qw/cdid artist title year runlength/);
1475
1476   columns
1477         my @all_columns  = $class->columns;
1478         my @columns      = $class->columns($group);
1479
1480         my @primary      = $class->primary_columns;
1481         my $primary      = $class->primary_column;
1482         my @essential    = $class->_essential;
1483
1484       There are four 'reserved' groups: 'All', 'Essential', 'Primary' and
1485       'TEMP'.
1486
1487       'All' are all columns used by the class. If not set it will be created
1488       from all the other groups.
1489
1490       'Primary' is the primary key columns for this class. It must be set
1491       before objects can be used.
1492
1493       If 'All' is given but not 'Primary' it will assume the first column in
1494       'All' is the primary key.
1495
1496       '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
1503       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
1507   Non-Persistent Fields
1508         Music::CD->columns(TEMP => qw/nonpersistent/);
1509
1510       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
1514   find_column
1515         Class->find_column($column);
1516         $obj->find_column($column);
1517
1518       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
1524       The interface of the Column object itself is still under development,
1525       so you shouldn't really rely on anything internal to it.
1526

TRANSACTIONS

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
1533       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
1537   dbi_commit / dbi_rollback
1538         $obj->dbi_commit();
1539         $obj->dbi_rollback();
1540
1541       These are thin aliases through to the DBI's commit() and rollback()
1542       commands to commit or rollback all changes to this object.
1543
1544   Localised Transactions
1545       A nice idiom for turning on a transaction locally (with AutoCommit
1546       turned on globally) (courtesy of Dominic Mitchell) is:
1547
1548         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
1556           # 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
1565         And then you just call:
1566
1567         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
1575       Now either both will get added, or the entire transaction will be
1576       rolled back.
1577

UNIQUENESS OF OBJECTS IN MEMORY

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
1584       Here's an example to illustrate:
1585
1586         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
1590       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
1594       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
1599       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
1605         perl -e 'use Scalar::Util qw(weaken)'
1606
1607       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
1611       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
1615   remove_from_object_index
1616         $artist->remove_from_object_index();
1617
1618       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
1622   clear_object_index
1623         Music::DBI->clear_object_index();
1624
1625       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
1628   purge_object_index_every
1629         Music::Artist->purge_object_index_every(2000);
1630
1631       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
1639       (Eventually this may handled in the DESTROY method instead.)
1640
1641       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

SUBCLASSING

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
1654       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
1658       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

CAVEATS

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
1672   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

SUPPORTED DATABASES

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
1683         L<Class::DBI::mysql>, L<Class::DBI::Pg>, L<Class::DBI::Oracle>,
1684         L<Class::DBI::SQLite>
1685
1686       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
1691       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

CURRENT AUTHOR

1698       Tony Bowden
1699

AUTHOR EMERITUS

1701       Michael G Schwern
1702

THANKS TO

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

RELEASE PHILOSOPHY

1715       Class::DBI now uses a three-level versioning system. This release, for
1716       example, is version 3.0.17
1717
1718       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
1723       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
1732   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
1738       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
1742       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
1746       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
1752   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
1763       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
1768   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
1777       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
1784       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
1790   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
1795       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
1799       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

SUPPORT

1809       Support for Class::DBI is mostly via the mailing list.
1810
1811       To join the list, or read the archives, visit
1812         http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi
1813
1814       There is also a Class::DBI wiki at
1815         http://www.class-dbi.com/
1816
1817       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
1820       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
1824       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
1828       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
1832       The wiki has numerous references to other articles, presentations etc.
1833
1834       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

LICENSE

1839       This library is free software; you can redistribute it and/or modify it
1840       under the same terms as Perl itself.
1841

SEE ALSO

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.
1847
1848
1849
1850perl v5.30.1                      2020-01-29                     Class::DBI(3)
Impressum