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