1NAME 2 Class::DBI - Simple Database Abstraction 3 4SYNOPSIS 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 60INTRODUCTION 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 152CLASS 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 265CONSTRUCTORS 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 340RETRIEVING 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 396ITERATORS 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 443COPY 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 479TRIGGERS 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 511CONSTRAINTS 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 583DATA 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 599DATA 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 620EXCEPTIONS 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 662WARNINGS 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 667INSTANCE 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 701Changing 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 991TABLE 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 1186MANY 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 1237ADDING 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 1253DEFINING 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 1425LAZY 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 1507TRANSACTIONS 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 1558UNIQUENESS 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 1628SUBCLASSING 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 1645CAVEATS 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 1655SUPPORTED 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 1676CURRENT AUTHOR 1677 Tony Bowden 1678 1679AUTHOR EMERITUS 1680 Michael G Schwern 1681 1682THANKS 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 1693RELEASE 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 bugs-Class-DBI@rt.cpan.org 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 1787SUPPORT 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 1816LICENSE 1817 This library is free software; you can redistribute it and/or modify it 1818 under the same terms as Perl itself. 1819 1820SEE 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 1826