1=head1 NAME 2 3Rose::DB::Object::Tutorial - A guided tour of the basics of Rose::DB::Object 4 5=head1 INTRODUCTION 6 7This document provides a step-by-step introduction to the L<Rose::DB::Object> module distribution. It demonstrates all of the important features using a semi-realistic example database. This tutorial does not replace the actual documentation for each module, however. The "reference" documentation found in each ".pm" file is still essential, and contains some good examples of its own. 8 9This tutorial provides a gradual introduction to L<Rose::DB::Object>. It also describes "best practices" for using L<Rose::DB::Object> in the most robust, maintainable manner. If you're just trying to get a feel for what's possible, you can L<skip to the end|/"Relationship code summary"> and take a look at the completed example database and associated Perl code. But I recommend reading the tutorial from start to finish at least once. 10 11The examples will start simple and get progressively more complex. You, the developer, have to decide which level of complexity or abstraction is appropriate for your particular task. 12 13=head1 CONVENTIONS 14 15Some of the examples in this tutorial will use the fictional C<My::> namespace prefix. Some will use no prefix at all. Your code should use whatever namespace you deem appropriate. Usually, it will be something like C<MyCorp::MyProject::> (i.e., your corporation, organization, and/or project). I've chosen to use C<My::> or to omit the prefix entirely simply because this produces shorter class names, which will help this tutorial stay within an 80-column width. 16 17For the sake of brevity, the C<use strict> directive and associated "my" declarations have also been omitted from the example code. Needless to say, you should always C<use strict> in your actual code. 18 19Similarly, the traditional "1;" true value used at the end of each ".pm" file has been omitted from the examples. Don't forget to add this to the end of your actual Perl module files. 20 21Although most of the examples in this tutorial use the L<base.pm|base> module to set up inheritance, directly modifying the C<@ISA> package variable usually works just as well. In situations where there are circular relationships between classes, the C<use base ...> form may be preferable because it runs at compile-time, whereas C<@ISA> modification happens at run-time. In either case, it's a good idea to set up inheritance as early as possible in each module. 22 23 package Product; 24 25 # Set up inheritance first 26 use base qw(Rose::DB::Object); 27 28 # Then do other stuff... 29 ... 30 31=head1 TUTORIAL 32 33=head2 Preface 34 35Before doing anything useful with L<Rose::DB::Object>, it's necessary to create and configure a L<Rose::DB> subclass through which L<Rose::DB::Object>-derived objects will access the database. 36 37To get up to speed quickly with L<Rose::DB>, read the L<Rose::DB::Tutorial> documentation. The rest of this tutorial will assume the existence of a C<My::DB> class created as L<described|Rose::DB::Tutorial/"Multiple data sources using namespaces"> in the L<Rose::DB tutorial|Rose::DB::Tutorial>. Here's a possible incarnation of the C<My::DB> class. 38 39 package My::DB; 40 41 use base qw(Rose::DB); 42 43 __PACKAGE__->use_private_registry; 44 45 __PACKAGE__->register_db( 46 driver => 'pg', 47 database => 'mydb', 48 host => 'localhost', 49 username => 'devuser', 50 password => 'mysecret', 51 ); 52 53Read the L<Rose::DB tutorial|Rose::DB::Tutorial> for an explanation of this code. 54 55The PostgreSQL database will be used in the examples in this tutorial, but the features demonstrated will not be specific to that database. If you are following along with a different database, you may have to adjust the specific syntax used in the SQL table creation statements, but all of the same features should be present in some form. 56 57This tutorial is based on a fictional database schema for a store-like application. Both the database schema the corresponding Perl classes will evolve over the course of this document. 58 59=head2 Getting started 60 61Let's start with a single table in our fictional store database. 62 63 CREATE TABLE products 64 ( 65 id SERIAL NOT NULL PRIMARY KEY, 66 name VARCHAR(255) NOT NULL, 67 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 68 69 UNIQUE(name) 70 ); 71 72Here's a basic L<Rose::DB::Object> class to front that table: 73 74 package Product; 75 76 use base qw(Rose::DB::Object); 77 78 __PACKAGE__->meta->setup 79 ( 80 table => 'products', 81 columns => [ qw(id name price) ], 82 pk_columns => 'id', 83 unique_key => 'name', 84 ); 85 86The steps are simple: 87 88=over 4 89 90=item 1. Inherit from L<Rose::DB::Object>. 91 92=item 2. Name the table. 93 94=item 3. Name the columns. 95 96=item 4. Name the primary key column(s). 97 98=item 5. Add unique keys (if any). 99 100=item 6. Initialize. (Implied at the end of the L<setup|Rose::DB::Object::Metadata/setup> call) 101 102=back 103 104Operations 2 through 6 are done through the L<setup|Rose::DB::Object::Metadata/setup> method on the L<metadata object|Rose::DB::Object::Metadata> associated with this class. The table must have a primary key, and may have zero or more unique keys. The primary key and each unique key may contain multiple columns. 105 106Of course, L<earlier|/Preface> it was established that L<Rose::DB> needs to be set up for any L<Rose::DB::Object> class to work properly. To that end, this tutorial assumes the existence of a L<Rose::DB> subclass named L<My::DB> that is set up according to the L<best practices|Rose::DB::Tutorial> of L<Rose::DB>. We need to make the C<Product> class use L<My::DB>. Here's one way to do it: 107 108 package Product; 109 110 use My::DB; 111 112 use base qw(Rose::DB::Object); 113 114 __PACKAGE__->meta->setup 115 ( 116 table => 'products', 117 columns => [ qw(id name price) ], 118 pk_columns => 'id', 119 unique_key => 'name', 120 ); 121 122 sub init_db { My::DB->new } 123 124Now C<Product> will create a L<My::DB> object when it needs to connect to the database. 125 126Note that the C<My::DB-E<gt>new> call in C<init_db()> means that each C<Product> object will have its own, private C<My::DB> object. See the section below, L<"A brief digression: database objects">, for an explanation of this setup and some alternatives. 127 128=head3 Setting up your own base class 129 130Looking forward, it's likely that all of our L<Rose::DB::Object>-derived classes will want to use L<My::DB> objects when connecting to the database. It's tedious to repeat this code in all of those classes. A common base class can provide a single, shared location for that code. 131 132 package My::DB::Object; 133 134 use My::DB; 135 136 use base qw(Rose::DB::Object); 137 138 sub init_db { My::DB->new } 139 140(Again, note that all C<My::DB::Object>-derived objects will get their own C<My::DB> objects given this definition of C<init_db()>. See the L<"digression"|/"A brief digression: database objects"> section below for more information.) 141 142Now the C<Product> class can inherit from C<My::DB::Object> instead of inheriting from L<Rose::DB::Object> directly. 143 144 package Product; 145 146 use base 'My::DB::Object'; 147 148 __PACKAGE__->meta->setup 149 ( 150 table => 'products', 151 columns => [ qw(id name price) ], 152 pk_columns => 'id', 153 unique_key => 'name', 154 ); 155 156This use of a common base class is strongly recommended. You will see this pattern repeated in the L<Rose::DB tutorial|Rose::DB::Tutorial> as well. The creation of seemingly "trivial" subclasses is a cheap and easy way to ensure ease of extensibility later on. 157 158For example, imagine we want to add a C<copy()> method to all of our database objects. If they all inherit directly from C<Rose::DB::Object>, that's not easy to do. But if they all inherit from C<My::DB::Object>, we can just add the C<copy()> method to that class. 159 160The lesson is simple: when in doubt, subclass. A few minutes spent now can save you a lot more time down the road. 161 162=head3 Rose::DB::Object in action 163 164Now that we have our C<Product> class all set up, let's see what we can do with it. 165 166=head4 Get and set column values 167 168By default, each column has a combined accessor/mutator method. When passed a value, the column value is set and returned. When called with no arguments, the value is simply returned. 169 170 $p->name('Bike'); # set name 171 print $p->name; # get name 172 173Since L<Rose::DB::Object> inherits from L<Rose::Object>, each object method is also a valid constructor argument. 174 175 $p = Product->new(name => 'Cane', price => 1.99); 176 print $p->price; # 1.99 177 178=head4 Load 179 180An object can be loaded based on a primary key. 181 182 $p = Product->new(id => 1); # primary key 183 $p->load; # Load the object from the database 184 185An object can also be loaded based on a unique key: 186 187 $p = Product->new(name => 'Sled'); # unique key 188 $p->load; # Load the object from the database 189 190If there is no row in the database table with the specified primary or unique key value, the call to L<load()|Rose::DB::Object/load> will fail. Under the default L<error mode|Rose::DB::Object::Metadata/error_mode>, an exception will be thrown. To safely check whether or not such a row exists, use the C<speculative> parameter. 191 192 $p = Product->new(id => 1); 193 194 unless($p->load(speculative => 1)) 195 { 196 print "No such product with id = 1"; 197 } 198 199Regardless of the L<error mode|Rose::DB::Object::Metadata/error_mode>, L<load()|Rose::DB::Object/load> will simply return true or false when the C<speculative> parameter is used. 200 201=head4 Insert 202 203To insert a row, create an object and then L<save|Rose::DB::Object/save> it. 204 205 $p = Product->new(id => 123, name => 'Widget', price => 4.56); 206 $p->save; # Insert the object into the database 207 208The default L<error mode|Rose::DB::Object::Metadata/error_mode> will throw an exception if anything goes wrong during the save, so we don't have to check the return value. 209 210Here's another variation: 211 212 $p = Product->new(name => 'Widget', price => 1.23); 213 $p->save; 214 215 print $p->id; # print the auto-generated primary key value 216 217Since the primary key of the C<products> table, C<id>, is a SERIAL column, a new primary key value will be automatically generated if one is not specified. After the object is saved, we can retrieve the auto-generated value. 218 219=head4 Update 220 221To update a row, simply L<save|Rose::DB::Object/save> an object that has been previously L<load|Rose::DB::Object/load>ed or L<save|Rose::DB::Object/save>d. 222 223 $p1 = Product->new(name => 'Sprocket', price => 9.99); 224 $p1->save; # Insert a new object into the database 225 226 $p1->price(12.00); 227 $p1->save; # Update the object in the database 228 229 $p2 = Product->new(id => 1); 230 $p2->load; # Load an existing object 231 232 $p2->name($p2->name . ' Mark II'); 233 $p2->save; # Update the object in the database 234 235=head4 Delete 236 237An object can be deleted based on a primary key or a unique key. 238 239 $p = Product->new(id => 1); # primary key 240 $p->delete; # Delete the object from the database 241 242 $p = Product->new(name => 'Sled'); # unique key 243 $p->delete; # Delete the object from the database 244 245The L<delete|Rose::DB::Object/delete> method will return true if the row was deleted or did not exist, false otherwise. 246 247It works just as well with objects that have been loaded or saved. 248 249 $p1 = Product->new(name => 'Sprocket', price => 9.99); 250 $p1->save; # Insert a new object into the database 251 $p1->delete; # Now delete the object 252 253 $p2 = Product->new(id => 1); 254 $p2->load; # Load an existing object 255 $p2->delete; # Now delete the object 256 257=head3 Multiple objects 258 259The examples above show SELECT, INSERT, UPDATE, and DELETE operations on one row at time based on primary or unique keys. What about manipulating rows based on other criteria? What about manipulating multiple rows simultaneously? Enter L<Rose::DB::Object::Manager>, or just "the manager" for short. 260 261But why is there a separate class for dealing with multiple objects? Why not simply add more methods to the object itself? Say, a C<search()> method to go alongside L<load()|Rose::DB::Object/load>, L<save()|Rose::DB::Object/save>, L<delete()|Rose::DB::Object/delete> and friends? There are several reasons. 262 263First, it's somewhat "semantically impure" for the class that represents a single row to also be the class that's used to fetch multiple rows. It's also important to keep the object method namespace as sparsely populated as possible. Each new object method prevents a column with the same name from using that method name. L<Rose::DB::Object> tries to keep the list of L<reserved method names|Rose::DB::Object/"RESERVED METHODS"> as small as possible. 264 265Second, inevitably, classes grow. It's important for the object manager class to be separate from the object class itself so each class can grow happily in isolation, with no potential for namespace or functionality clashes. 266 267All of that being said, L<Rose::DB::Object::Manager> does include support for adding manager methods to the object class. Obviously, this practice is not recommended, but it exists if you really want it. 268 269Anyway, let's see some examples. Making a manager class is simply a matter of inheriting from L<Rose::DB::Object::Manager>, specifying the object class, and then creating a series of appropriately named wrapper methods. 270 271 package Product::Manager; 272 273 use base qw(Rose::DB::Object::Manager); 274 275 sub object_class { 'Product' } 276 277 __PACKAGE__->make_manager_methods('products'); 278 279The call to L<make_manager_methods()|Rose::DB::Object::Manager/make_manager_methods> creates the following methods: 280 281 get_products 282 get_products_iterator 283 get_products_count 284 delete_products 285 update_products 286 287The names are pretty much self-explanatory. You can read the L<Rose::DB::Object::Manager> documentation for all the gory details. The important thing to note is that the methods were all named based on the "products" argument to L<make_manager_methods()|Rose::DB::Object::Manager/make_manager_methods>. You can see how "products" has been incorporated into each of the method names. 288 289This naming scheme is just a suggestion. You can name these methods anything you want (using the C<methods> parameter to the L<make_manager_methods()|Rose::DB::Object::Manager/make_manager_methods> call), or you can even write the methods yourself. Each of these methods is a merely a thin wrapper around the generically-named methods in L<Rose::DB::Object::Manager>. The wrappers pass the specified object class to the generic methods. 290 291The Perl code for the C<Product::Manager> class shown above can be generated automatically by calling the L<perl_manager_class|Rose::DB::Object::Metadata/perl_manager_class> method on the L<Rose::DB::Object::Metadata> that's associated with the C<Product> class. Similarly, the L<make_manager_class|Rose::DB::Object::Metadata/make_manager_class> method called on the C<Product> metadata object will both generate the code and L<eval|perlfunc/eval>uate it for you, automating the entire process of creating a manager class from within your L<Rose::DB::Object>-derived class. 292 293 package Product; 294 295 use base qw(Rose::DB::Object); 296 ... 297 298 # This actually creates the Product::Manager class 299 # as shown in the code sample above. 300 __PACKAGE__->meta->make_manager_class('products'); 301 302As the comment says, the call to L<make_manager_class|Rose::DB::Object::Metadata/make_manager_class> will create a standalone C<Product::Manager> class in memory. See the documentation for the L<perl_manager_class|Rose::DB::Object::Metadata/perl_manager_class> and L<make_manager_class|Rose::DB::Object::Metadata/make_manager_class> methods for more information. 303 304If you decide not to heed my advice, but instead decide to create these methods inside your L<Rose::DB::Object>-derived class directly, you can do so by calling L<make_manager_methods()|Rose::DB::Object::Manager/make_manager_methods> from within your object class. 305 306 package Product; 307 308 use Rose::DB::Object::Manager; 309 310 use base 'My::DB::Object'; 311 ... 312 Rose::DB::Object::Manager->make_manager_methods('products'); 313 314This will be the last you see of this technique in this tutorial. All of the examples will assume that the recommended approach is used instead. 315 316=head4 Fetching objects 317 318The most common task for the manager is fetching multiple objects. We'll use the C<get_products()> method to do that. It's based on the L<get_objects()|Rose::DB::Object::Manager/get_objects> method, which takes many parameters. 319 320One (optional) parameter is the now-familiar L<db|Rose::DB> object used to connect to the database. This parameter is valid for all L<Rose::DB::Object::Manager> methods. In the absence of this parameter, the L<init_db()|Rose::DB/init_db> method of the object class will be called in order to create one. 321 322Passing no arguments at all will simply fetch every C<Product> object in the database. 323 324 $products = Product::Manager->get_products(); 325 326 foreach my $product (@$products) 327 { 328 print $product->name, "\n"; 329 } 330 331The return value is a reference to an array of C<Product> objects. Now let's go to the other extreme. 332 333 $products = 334 Product::Manager->get_products( 335 query => 336 [ 337 name => { like => '%Hat' }, 338 id => { ge => 7 }, 339 or => 340 [ 341 price => 15.00, 342 price => { lt => 10.00 }, 343 ], 344 ], 345 sort_by => 'name', 346 limit => 10, 347 offset => 50); 348 349That call produces SQL that looks something like this: 350 351 SELECT id, name, price FROM products WHERE 352 name LIKE '%Hat' AND 353 id >= 7 AND 354 (price = 15.00 OR price < 10.00) 355 ORDER BY name 356 LIMIT 10 OFFSET 50 357 358Manager queries support nested boolean logic and several different kinds of comparison operators. For a full explanation of all the options, see the L<Rose::DB::Object::Manager> documentation. 359 360The iterator method takes the same kinds of arguments, but returns an iterator that will fetch the objects from the database one at a time. 361 362 $iterator = Product::Manager->get_products_iterator(...); 363 364 while($product = $iterator->next) 365 { 366 print $product->id, ' ', $product->name, "\n"; 367 368 $iterator->finish if(...); # exit early? 369 } 370 371 print $iterator->total; # total iterated over 372 373Note that this is a "real" iterator. Objects not iterated over are not fetched from the database at all. 374 375=head4 Counting objects 376 377Counting objects is straightforward. The C<get_products_count()> method takes the same kinds of arguments as C<get_products()> and C<get_products_iterator()>. It returns the count. 378 379 $num_cheap_products = 380 Product::Manager->get_products_count( 381 query => [ price => { lt => 1.00 } ]); 382 383=head4 Deleting objects 384 385The C<delete_products()> method accepts the same kinds of C<query> arguments as the manager methods described above, only it uses the parameter name C<where> instead. 386 387 $num_rows_deleted = 388 Product::Manager->delete_products( 389 where => 390 [ 391 id => { ne => 123 }, 392 name => { like => 'Wax%' }, 393 ]); 394 395=head4 Updating objects 396 397The C<update_products()> method accepts the same kinds of arguments as the C<delete_products()> method, plus a C<set> parameter to specify the actual update information. 398 399 $num_rows_updated = 400 Product::Manager->update_products( 401 set => 402 { 403 price => 5.00, 404 }, 405 where => 406 [ 407 price => 4.99, 408 id => { gt => 100 }, 409 ]); 410 411=head3 The end of the beginning 412 413This section has covered the I<bare minimum> usage and functionality of the L<Rose::DB::Object> module distribution. Using these features alone, you can automate the basic CRUD operations (Create, Retrieve, Update, and Delete) for single or multiple objects. But it's almost a shame to stop at this point. There's a lot more that L<Rose::DB::Object> can do for you. The "sweet spot" of effort vs. results is much farther along the curve. 414 415In the next section, we will expand upon our C<Product> class and tap more of L<Rose::DB::Object>'s features. But first... 416 417=head3 A brief digression: database objects 418 419The L<Rose::DB>-derived database object used by each L<Rose::DB::Object>-derived object is available via the L<db|Rose::DB::Object/db> object attribute. 420 421 $p = Product->new(...); 422 $db = $p->db; # My::DB object 423 424You can read the L<Rose::DB> documentation to explore the capabilities of these db objects. Most of the time, you won't have to be concerned about them. But it's sometime useful to deal with them directly. 425 426The first thing to understand is where the database object comes from. If the L<db|Rose::DB::Object/db> attribute doesn't exist, it is created by calling L<init_db()|Rose::DB::Object/init_db>. The typical C<init_db()> method simply builds a new database object and returns it. (See the L<Rose::DB tutorial|Rose::DB::Tutorial> for an explanation of the possible arguments to L<new()|Rose::DB/new>, and why there are none in the call below.) 427 428 package Product; 429 ... 430 sub init_db { My::DB->new } 431 432This means that each C<Product> object will have its own C<My::DB> object, and therefore (in the absence of modules like L<Apache::DBI>) its own connection to the database. 433 434If this not what you want, you can make C<init_db()> return the same C<My::DB> object to every C<Product> object. This will make it harder to ensure that the database handle will be closed when all C<Product> objects go out of scope, but that may not be important for your application. The easiest way to do this is to call L<new_or_cached|Rose::DB/new_or_cached> instead of L<new|Rose::DB/new>. 435 436 package Product; 437 ... 438 sub init_db { My::DB->new_or_cached } 439 440Since C<init_db()> is only called if a C<Product> object does not already have a L<db|Rose::DB::Object/db> object, another way to share a single C<My::DB> object with several C<Product> objects is to do so explicitly, either by pre-creating the C<My::DB> object: 441 442 $db = My::DB->new; # will share this db with the Products below 443 444 $p1 = Product->new(db => $db, ...); 445 $p2 = Product->new(db => $db, ...); 446 $p3 = Product->new(db => $db, ...); 447 448or by letting one of the C<Product> objects provide the L<db|Rose::DB::Object/db> for the rest. 449 450 $p1 = Product->new(...); 451 $p2 = Product->new(db => $p1->db, ...); # use $p1's db 452 $p3 = Product->new(db => $p1->db, ...); # use $p1's db 453 454A note for L<mod_perl> users: when using L<Apache::DBI>, even if each C<Product> has its own C<My::DB> object, remember that they will all share a single underlying L<DBI> database handle. That is, each L<Rose::DB>-derived object of a given L<type|Rose::DB/type> and L<domain|Rose::DB/domain> will eventually call L<DBI>'s L<connect()|DBI/connect> method with the same arguments, and therefore return the same, cached database handle when running under L<Apache::DBI>. The L<default cache implementation|Rose::DB::Cache> underlying the L<new_or_cached|Rose::DB/new_or_cached> method is also L<mod_perl-aware|Rose::DB::Cache/prepare_db> and will cooperate with L<Apache::DBI>. 455 456Here's an example where sharing a database object is important: creating several C<Product> objects in a single transaction. 457 458 $db = My::DB->new; 459 460 $db->begin_work; # Start transaction 461 462 # Use this $db with each product object 463 464 $p1 = Product->new(name => 'Bike', db => $db); 465 $p1->save; 466 467 $p2 = Product->new(name => 'Sled', db => $db); 468 $p2->save; 469 470 $p3 = Product->new(name => 'Kite', db => $db); 471 $p3->save; 472 473 if(...) # Now either commit them all or roll them all back 474 { 475 $db->commit; 476 } 477 else 478 { 479 $db->rollback; 480 } 481 482Cross-database migration is another important use for explicitly shared L<db|Rose::DB::Object/db> objects. Here's how to move a product from a production database to an archive database. 483 484 $production_db = My::DB->new('production'); 485 $archive_db = My::DB->new('archive'); 486 487 # Load bike from production database 488 $p = Product->new(name => 'Bike', db => $production_db); 489 $p->load; 490 491 # Save the bike into the archive database 492 $p->db($archive_db); 493 $p->save(insert => 1); # force an insert instead of an update 494 495 # Delete the bike from the production database 496 $p->db($production_db); 497 $p->delete; 498 499=head2 Mainstream usage 500 501Let's imagine that the C<products> table has expanded. It now looks like this. 502 503 CREATE TABLE products 504 ( 505 id SERIAL NOT NULL PRIMARY KEY, 506 name VARCHAR(255) NOT NULL, 507 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 508 509 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 510 CHECK(status IN ('inactive', 'active', 'defunct')), 511 512 date_created TIMESTAMP NOT NULL DEFAULT NOW(), 513 release_date TIMESTAMP, 514 515 UNIQUE(name) 516 ); 517 518We could do a straightforward expansion of the C<Product> class as designed in the L<previous section|/"Getting started">. 519 520 package Product; 521 522 use base 'My::DB::Object'; 523 524 __PACKAGE__->meta->setup 525 ( 526 table => 'products', 527 columns => [ qw(id name price status date_created release_date) ], 528 pk_columns => 'id', 529 unique_key => 'name', 530 ); 531 532But now we're faced with a few problems. First, while the C<status> column only accepts a few pre-defined values, our C<Product> object will gladly accept any status value. But maybe that's okay because the database will reject invalid values, causing an exception will be thrown when the object is saved. 533 534The date/time fields are more troubling. What is the format of a valid value for a TIMESTAMP column in PostgreSQL? Consulting the PostgreSQL documentation will yield the answer, I suppose. But now all the code that uses C<Product> objects has to be sure to format the C<date_created> and C<release_date> values accordingly. That's even more difficult if some of those values come from external sources, such as a web form. 535 536Worse, what if we decide to change databases in the future? We'd have to hunt down every single place where a C<date_created> or C<release_date> value is set and then modify the formatting to match whatever format the new database wants. Oh, and we'll have to look that up too. Blah. 537 538Finally, what about all those default values? The C<price> column already had a default value, but now two more columns also have defaults. True, the database will take care of this when a row is inserted, but now the Perl object is diverging more and more from the database representation. 539 540Let's solve all of these problems. If we more accurately describe the columns, L<Rose::DB::Object> will do the rest. 541 542 package Product; 543 544 use base 'My::DB::Object'; 545 546 __PACKAGE__->meta->setup 547 ( 548 table => 'products', 549 550 columns => 551 [ 552 id => { type => 'serial', primary_key => 1, not_null => 1 }, 553 name => { type => 'varchar', length => 255, not_null => 1 }, 554 555 price => 556 { 557 type => 'decimal', 558 precision => 10, 559 scale => 2, 560 not_null => 1, 561 default => 0.00 562 }, 563 564 status => 565 { 566 type => 'varchar', 567 length => 128, 568 not_null => 1, 569 default => 'inactive', 570 check_in => [ 'inactive', 'active', 'defunct' ], 571 }, 572 573 date_created => { type => 'timestamp', not_null => 1, 574 default => 'now()' }, 575 release_date => { type => 'timestamp' }, 576 ], 577 578 unique_key => 'name', 579 580 allow_inline_column_values => 1, 581 ); 582 583Before examining what new functionality this new class gives us, there are a few things to note about the definition. First, the primary key is no longer specified with the L<primary_key_columns()|Rose::DB::Object::Metadata/primary_key_columns> method. Instead, the C<id> column has its C<primary_key> attribute set to a true value in its description. 584 585Second, note the default value for the C<date_created> column. It's a string containing a call to the PL/SQL function C<now()>, which can actually only be run within the database. But thanks to the L<allow_inline_column_values|Rose::DB::Object::Metadata/allow_inline_column_values> attribute being set to a true value, L<Rose::DB::Object> will pass the string "now()" through to the database as-is. 586 587In the case of "creation date" columns like this, it's often better to let the database provide the value as close as possible to the very moment the row is created. On the other hand, this will mean that any newly created C<Product> object will have a "strange" value for that column (the string "now()") until/unless it is re-L<load|Rose::DB::Object/load>ed from the database. It's a trade-off. 588 589Let's see the new C<Product> class in action. The defaults work as expected. 590 591 $p = Product->new; 592 593 print $p->status; # 'inactive' 594 print $p->price; # 0.00 595 596The C<status> method now restricts its input, throwing an exception if the input is invalid. 597 598 $p->status('nonesuch'); # Boom! Invalid status: 'nonesuch' 599 600The timestamp columns now accept any value that L<Rose::DateTime::Util>'s L<parse_date()|Rose::DateTime::Util/parse_date> method can understand. 601 602 $p->release_date('2005-01-22 18:00:57'); 603 $p->release_date('12/24/1980 10am'); 604 605See the L<Rose::DateTime::Util|Rose::DateTime::Util/parse_date> documentation for a full list of acceptable formats. 606 607Inside a C<Product> object, date/time information is stored in L<DateTime> objects. 608 609 $dt = $p->release_date; # DateTime object 610 611Since L<DateTime> objects can be modified in-place, doing a formerly thorny task like date math is now trivial. 612 613 $p->release_date->add(days => 1); 614 615The C<release_date()> method also accepts a L<DateTime> object as an input, of course: 616 617 $p->release_date(DateTime->new(...)); 618 619There are even a few convenience functions triggered by passing a name/value pair. 620 621 # Thursday, December 25th 1980 at 10:00:00 AM 622 print $p->release_date(format => '%A, %B %E %Y at %t'); 623 624 # Clone the DateTime object, truncate the clone, and return it 625 $month_start = $p->release_date(truncate => 'month'); 626 627 print $month_start->strftime('%Y-%m-%d'); # 1980-12-01 628 629Conveniently, L<Rose::DB::Object::Manager> queries can also use any values that the corresponding column methods will accept. For example, here's a query that filters on the C<release_date> column using a L<DateTime> object. 630 631 $last_week = DateTime->now->subtract(weeks => 1); 632 633 $products = 634 Product::Manager->get_products( 635 query => 636 [ 637 release_date => { lt => $last_week }, 638 ], 639 sort_by => 'release_date'); 640 641The upshot is that you no longer have to be concerned about the details of the date/time format(s) understood by the underlying database. You're also free to use L<DateTime> objects as a convenient interchange format in your code. 642 643This ability isn't just limited to date/time columns. Any data type that requires special formatting in the database, and/or is more conveniently dealt with as a more "rich" value on the Perl side of the fence is fair game for this treatment. 644 645Some other examples include the L<bitfield|Rose::DB::Object::Metadata::Column::Bitfield> column type, which is represented by a L<Bit::Vector> object on the Perl side, and the L<boolean|Rose::DB::Object::Metadata::Column::Boolean> column type which evaluates the "truth" of its arguments and coerces the value accordingly. In all cases, column values are automatically formatted as required by the native column data types in the database. 646 647In some circumstances, L<Rose::DB::Object> can even "fake" a data type for use with a database that does not natively support it. For example, the L<array|Rose::DB::Object::Metadata::Column::Array> column type is natively supported by PostgreSQL, but it will also work with MySQL using a VARCHAR column as a stand-in. 648 649Finally, if you're concerned about the performance implications of "inflating" column values from strings and numbers into (relatively) large objects, rest assured that such inflation is only done as needed. For example, an object with ten date/time columns can be loaded, modified, and saved without ever creating a single L<DateTime> object, provided that none of the date/time columns were among those whose values were modified. 650 651Put another way, the methods that service the columns have an awareness of the producer and consumer of their data. When data is coming from the database, the column methods accept it as-is. When data is being sent to the database, it is formatted appropriately, if necessary. If a column value was not modified since it was loaded from the database, then the value that was loaded is simply returned as-is. In this way, data can make a round-trip without ever being inflated, deflated, or formatted. 652 653This behavior is not a requirement of all column methods, but it is a recommended practice--one followed by all the column classes that are part of the L<Rose::DB::Object> distribution. 654 655=head2 Auto-initialization and the convention manager 656 657The C<Product> class set up in the previous section is useful, but it also takes significantly more typing to set up. Over the long term, it's still a clear win. On the other hand, a lot of the details in the column descriptions are already known by the database: column types, default values, maximum lengths, etc. It would be handy if we could ask the database for this information instead of looking it up and typing it in manually. 658 659This process of interrogating the database in order to extract metadata is called "auto-initialization." There's an L<entire section|Rose::DB::Object::Metadata/"AUTO-INITIALIZATION"> of the L<Rose::DB::Object::Metadata> documentation dedicated to the topic. The executive summary is that auto-initialization saves work in the short-run, but with some long-term costs. Read the L<friendly manual|Rose::DB::Object::Metadata/"AUTO-INITIALIZATION"> for the details. For the purposes of this tutorial, I will simply demonstrate the features, culminating in the suggested best practice. 660 661Let's start by applying auto-initialization to the C<Product> class. 662 663 package Product; 664 665 use base 'My::DB::Object'; 666 667 __PACKAGE__->meta->table('products'); 668 __PACKAGE__->meta->auto_initialize; 669 670Believe it or not, that class is equivalent to the previous incarnation, right down to the details of the columns and the unique key. As long as the table is specified, L<Rose::DB::Object> will dig all the rest of the information out of the database. Handy! 671 672In fact, that class can be shortened even further with the help of the L<convention manager|Rose::DB::Object::ConventionManager>. 673 674 package Product; 675 676 use base 'My::DB::Object'; 677 678 __PACKAGE__->meta->auto_initialize; 679 680Now even the table is left unspecified. How does L<Rose::DB::Object> know what to do in this case? Why, by convention, of course. The default convention manager dictates that class names are singular and TitleCased, and their corresponding table names are lowercase and plural. Thus, the omitted table name in the C<Product> class is, by convention, assumed to be named "products". 681 682Like auto-initialization, the convention manager is handy, but may also present some maintenance issues. I tend to favor a more explicitly approach, but I can also imagine scenarios where the convention manager is a good fit. 683 684Keep in mind that customized convention managers are possible, allowing individual organizations or projects to define their own conventions. You can read all about it in the L<Rose::DB::Object::ConventionManager> documentation. 685 686Anyway, back to auto-initialization. Yes, L<auto_initialize()|Rose::DB::Object::Metadata/auto_initialize> will dig out all sorts of interesting and important information for you. Unfortunately, it will dig that information out I<every single time the class is loaded>. Worse, this class will fail to load at all if a database connection is not immediately available. 687 688Auto-initialization seems like something that is best done only once, with the results being saved in a more conventional form. That's just what L<Rose::DB::Object::Metadata>'s L<code generation|Rose::DB::Object::Metadata/"Code Generation"> functions are designed to do. The C<perl_*> family of methods can generate snippets of Perl code, or even entire classes, based on the results of the auto-initialization process. They'll even honor some basic code formatting directives. 689 690 package Product; 691 692 use base 'My::DB::Object'; 693 694 __PACKAGE__->meta->table('products'); 695 __PACKAGE__->meta->auto_initialize; 696 697 print __PACKAGE__->meta->perl_class_definition(indent => 2, 698 braces => 'bsd'); 699 700Here's the output of that print statement. A few long lines were manually wrapped, but it's otherwise unmodified. 701 702 package Product; 703 704 use strict; 705 706 use base 'My::DB::Object'; 707 708 __PACKAGE__->meta->setup 709 ( 710 table => 'products', 711 712 columns => 713 [ 714 id => { type => 'integer', not_null => 1 }, 715 name => { type => 'varchar', length => 255, not_null => 1 }, 716 price => { type => 'numeric', default => '0.00', 717 not_null => 1, precision => 10, scale => 2 }, 718 vendor_id => { type => 'integer' }, 719 status => { type => 'varchar', default => 'inactive', 720 length => 128, not_null => 1 }, 721 date_created => { type => 'timestamp', default => 'now()', 722 not_null => 1 }, 723 release_date => { type => 'timestamp' }, 724 ], 725 726 primary_key_columns => [ 'id' ], 727 728 unique_keys => [ 'name' ], 729 730 allow_inline_column_values => 1, 731 ); 732 733 1; 734 735Copy and paste that output back into the "Product.pm" file and you're in business. 736 737The door is open to further automation through scripts that call the methods demonstrated above. Although it's my inclination to work towards a static, explicit type of class definition, the tools are there for those who prefer a more dynamic approach. 738 739=head2 Foreign keys 740 741When a column in one table references a row in another table, the referring table is said to have a "foreign key." As with primary and unique keys, L<Rose::DB::Object> supports foreign keys made up of more than one column. 742 743In the context of L<Rose::DB::Object>, a foreign key is a database-supported construct that ensures that any non-null value in a foreign key column actually refers to an existing row in the foreign table. Databases that enforce this constraint are said to support "referential integrity." Foreign keys are only applicable to L<Rose::DB::Object>-derived classes when the underlying database supports "native" foreign keys and enforces referential integrity. 744 745While it's possible to define foreign keys in a L<Rose::DB::Object>-derived class even if there is no support for them in the database, this is considered bad practice. If you're just trying to express some sort of relationship between two tables, there's a more appropriate way to do so. (More on that in the L<next section|/Relationships>.) 746 747Let's add a foreign key to the C<products> table. First, we'll need to create the table that the foreign key will reference. 748 749 CREATE TABLE vendors 750 ( 751 id SERIAL NOT NULL PRIMARY KEY, 752 name VARCHAR(255) NOT NULL, 753 754 UNIQUE(name) 755 ); 756 757When dealing with any kind of inter-table relationship, L<Rose::DB::Object> requires a L<Rose::DB::Object>-derived class fronting each participating table. So we need a class for the C<vendors> table. 758 759 package Vendor; 760 761 use base 'My::DB::Object'; 762 763 __PACKAGE__->meta->setup 764 ( 765 table => 'vendors', 766 columns => 767 [ 768 id => { type => 'serial', primary_key => 1, not_null => 1 }, 769 name => { type => 'varchar', length => 255, not_null => 1 }, 770 ], 771 unique_key => 'name', 772 ); 773 774Now we'll add the foreign key to our ever-growing C<products> table. 775 776 CREATE TABLE products 777 ( 778 id SERIAL NOT NULL PRIMARY KEY, 779 name VARCHAR(255) NOT NULL, 780 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 781 782 vendor_id INT REFERENCES vendors (id), 783 784 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 785 CHECK(status IN ('inactive', 'active', 'defunct')), 786 787 date_created TIMESTAMP NOT NULL DEFAULT NOW(), 788 release_date TIMESTAMP, 789 790 UNIQUE(name) 791 ); 792 793Finally, here's how the foreign key definition looks in the Perl class. 794 795 package Product; 796 797 use base 'My::DB::Object'; 798 799 __PACKAGE__->meta->setup 800 ( 801 table => 'products', 802 803 columns => 804 [ 805 id => { type => 'integer', not_null => 1 }, 806 name => { type => 'varchar', length => 255, not_null => 1 }, 807 price => { type => 'numeric', default => '0.00', 808 not_null => 1, precision => 10, scale => 2 }, 809 vendor_id => { type => 'integer' }, 810 status => { type => 'varchar', default => 'inactive', 811 length => 128, not_null => 1 }, 812 date_created => { type => 'timestamp', default => 'now()', 813 not_null => 1 }, 814 release_date => { type => 'timestamp' }, 815 ], 816 817 primary_key_columns => [ 'id' ], 818 819 unique_keys => [ 'name' ], 820 821 allow_inline_column_values => 1, 822 823 foreign_keys => 824 [ 825 vendor => 826 { 827 class => 'Vendor', 828 key_columns => { vendor_id => 'id' }, 829 }, 830 ], 831 ); 832 833Note that a C<vendor_id> column is added to the column list. This needs to be done independently of any foreign key definition. It's a new column, so it needs to be in the column list. There's nothing more to it than that. 834 835There's also the foreign key definition itself. The name/hashref-value pair passed to the L<foreign_keys()|Rose::DB::Object::Metadata/foreign_keys> method is (roughly) shorthand for this. 836 837 Rose::DB::Object::Metadata::ForeignKey->new( 838 name => 'vendor', 839 class => 'Vendor', 840 key_columns => { vendor_id => 'id' }); 841 842In other words, C<vendor> is the name of the foreign key, and the rest of the information is used to set attributes on the L<foreign key object|Rose::DB::Object::Metadata::ForeignKey>. You could, in fact, construct your own foreign key objects and pass them to L<foreign_keys()|Rose::DB::Object::Metadata/foreign_keys> (or L<add_foreign_keys()|Rose::DB::Object::Metadata/add_foreign_keys>, etc.) but that would require even more typing. 843 844Going in the other direction, since our class and column names match up with what the convention manager expects, we could actually shorten the foreign key setup code to this. 845 846 foreign_keys => [ 'vendor' ], 847 848Given only a foreign key name, the convention manager will derive the C<Vendor> class name and will find the C<vendor_id> column in the C<Product> class and match it up to the primary key of the C<vendors> table. As with most things in L<Rose::DB::Object> class setup, you can be as explicit or as terse as you feel comfortable with, depending on how closely you conform to the expected conventions. 849 850So, what does this new C<vendor> foreign key do for us? Let's add some data and see. Imagine the following two objects. 851 852 $v = Vendor->new(name => 'Acme')->save; 853 $p = Product->new(name => 'Kite')->save; 854 855Note the use of the idiomatic way to create and then save an object in "one step." This is possible because both the L<new|Rose::DB::Object/new> and L<save|Rose::DB::Object/save> methods return the object itself. Anyway, let's link the two objects. One way to do it is to set the column values directly. 856 857 $p->vendor_id($v->id); 858 $p->save; 859 860To use this technique, we must know which columns link to which other columns, of course. But it works. We can see this by calling the method named after the foreign key itself: C<vendor()>. 861 862 $v = $p->vendor; # Vendor object 863 print $v->name; # "Acme" 864 865The C<vendor()> method can be used to link the two objects as well. Let's start over and try it that way: 866 867 $v = Vendor->new(name => 'Smith')->save; 868 $p = Product->new(name => 'Knife')->save; 869 870 $p->vendor($v); 871 $p->save; 872 873 print $p->vendor->name; # "Smith" 874 875Remember that there is no column named "vendor" in the "products" table. There is a "vendor_id" column, which has its own C<vendor_id()> get/set method that accepts and returns an integer value, but that's not what we're doing in the example above. Instead, we're calling the C<vendor()> method, which accepts and returns an entire C<Vendor> object. 876 877The C<vendor()> method actually accepts several different kinds of arguments, all of which it inflates into C<Vendor> objects. An already-formed C<Vendor> object was passed above, but other formats are possible. Imagine a new product also made by Smith. 878 879 $p = Product->new(name => 'Rope')->save; 880 $p->vendor(name => 'Smith'); 881 $p->save; 882 883Here the arguments passed to the C<vendor()> method are name/value pairs which will be used to construct the appropriate C<Vendor> object. Since C<name> is a unique key in the C<vendors> table, the C<Vendor> class can look up the existing vendor named "Smith" and assign it to the "Rope" product. 884 885If no vendor named "Smith" existed, one would have been created when the product was saved. In this case, the save process would take place within a transaction (assuming the database supports transactions) to ensure that both the product and vendor are created successfully, or neither is. 886 887The name/value pairs can also be provided in a reference to a hash. 888 889 $p = Product->new(name => 'Rope')->save; 890 $p->vendor({ name => 'Smith' }); 891 $p->save; 892 893Here's yet another argument format. Imagine that the "Acme" vendor id is 1. 894 895 $p = Product->new(name => 'Crate')->save; 896 $p->vendor(1); 897 $p->save; 898 899 print $p->vendor->name; # "Acme" 900 901Like the name/value pair argument format, a primary key value will be used to construct the appropriate object. (This only works if the foreign table has a single-column primary key, of course.) And like before, if such an object doesn't exist, it will be created. But in this case, if no existing vendor object had an C<id> of 1, the attempt to create one would have failed because the C<name> column of the inserted row would have been null. 902 903To summarize, the foreign key method can take arguments in these forms. 904 905=over 4 906 907=item * An object of the appropriate class. 908 909=item * Name/value pairs used to construct such an object. 910 911=item * A reference to a hash containing name/value pairs used to construct such an object. 912 913=item * A primary key value (but only if the foreign table has a single-column primary key). 914 915=back 916 917In each case, the foreign object will be added to the database it if does not already exist there. This all happens when the "parent" (C<Product>) object is saved. Until then, nothing is stored in the database. 918 919There's also another method created in response to the foreign key definition. This one allows the foreign object to be deleted from the database. 920 921 print $p->vendor->name; # "Acme" 922 $p->delete_vendor(); 923 $p->save; # The "Acme" vendor is deleted from the vendors table 924 925Again, the actual database modification takes place when the parent object is saved. Note that this operation will fail if any other rows in the C<products> table still reference the Acme vendor. And again, since this all takes place within a transaction (where supported), the entire operation will fail or succeed as a single unit. 926 927Finally, if we want to simply disassociate a product from its vendor, we can simply set the vendor to undef. 928 929 $p->vendor(undef); # This product has no vendor 930 $p->save; 931 932Setting the C<vendor_id> column directly has the same effect, of course. 933 934 $p->vendor_id(undef); # set vendor_id = NULL 935 $p->save; 936 937Before moving on to the next section, here's a brief note about auto-initialization and foreign keys. Since foreign keys are a construct of the database itself, the auto-initialization process can actually discover them and create the appropriate foreign key metadata. 938 939Since all of the column and table names are still in sync with the expected conventions, the C<Product> class can still be defined like this: 940 941 package Product; 942 943 use base 'My::DB::Object'; 944 945 __PACKAGE__->meta->auto_initialize; 946 947while retaining all of the abilities demonstrated above. 948 949The L<perl_class_definition()|Rose::DB::Object::Metadata/perl_class_definition> method will produce the appropriate foreign key definitions, as expected. 950 951 package Product; 952 953 use base 'My::DB::Object'; 954 955 __PACKAGE__->meta->auto_initialize; 956 957 print __PACKAGE__->meta->perl_class_definition(indent => 2, 958 braces => 'bsd'); 959 960Here's the output. 961 962 package Product; 963 964 use base 'My::DB::Object'; 965 966 __PACKAGE__->meta->setup 967 ( 968 table => 'products', 969 970 columns => 971 [ 972 id => { type => 'integer', not_null => 1 }, 973 name => { type => 'varchar', length => 255, not_null => 1 }, 974 price => { type => 'numeric', default => '0.00', 975 not_null => 1, precision => 10, scale => 2 }, 976 vendor_id => { type => 'integer' }, 977 status => { type => 'varchar', default => 'inactive', 978 length => 128, not_null => 1 }, 979 date_created => { type => 'timestamp', default => 'now()', 980 not_null => 1 }, 981 release_date => { type => 'timestamp' }, 982 ], 983 984 primary_key_columns => [ 'id' ], 985 986 unique_keys => [ 'name' ], 987 988 allow_inline_column_values => 1, 989 990 foreign_keys => 991 [ 992 vendor => 993 { 994 class => 'Vendor', 995 key_columns => { vendor_id => 'id' }, 996 }, 997 ], 998 ); 999 1000 1; 1001 1002=head2 Relationships 1003 1004=head3 One-to-one and many-to-one relationships 1005 1006Foreign keys are a database-native representation of a specific kind of inter-table relationship. This concept can be further generalized to encompass other kinds of relationships as well. But before we delve into that, let's consider the kind of relationship that a foreign key represents. 1007 1008In the product and vendor example in the L<previous section|/"Foreign keys">, each product has one vendor. (Actually it can have zero or one vendor, since the C<vendor_id> column allows NULL values. But for now, we'll leave that aside.) 1009 1010When viewed in terms of the participating tables, things look slightly different. Earlier, we established that several products can have the same vendor. So the inter-table relationship is actually this: many rows from the C<products> table may refer to one row from the C<vendors> table. 1011 1012L<Rose::DB::Object> describes inter-table relationships from the perspective of a given table by using the cardinality of the "local" table (C<products>) followed by the cardinality of the "remote" table (C<vendors>). The foreign key in the C<products> table (and C<Product> class) therefore represents a "B<many to one>" relationship. 1013 1014If the relationship were different and each vendor was only allowed to have a single product, then the relationship would be "one to one." Given only the foreign key definition as it exists in the database, it's not possible to determine whether the relationship is "many to one" or "one to one." The default is "many to one" because that's the less restrictive choice. 1015 1016To override the default, a relationship type string can be included in the foreign key description. 1017 1018 foreign_keys => 1019 [ 1020 vendor => 1021 { 1022 class => 'Vendor', 1023 key_columns => { vendor_id => 'id' }, 1024 relationship_type => 'one to one', 1025 }, 1026 ], 1027 1028(The C<relationship_type> parameter may be shortened to C<rel_type>, if desired.) 1029 1030L<Rose::DB::Object> generalizes all inter-table relationships using a family of aptly named relationship objects. Each inherits from the L<Rose::DB::Object::Metadata::Relationship> base class. 1031 1032Even foreign keys are included under the umbrella of this concept. When foreign key metadata is added to a L<Rose::DB::Object>-derived class, a corresponding "many to one" or "one to one" relationship is actually added as well. This relationship is simply a proxy for the foreign key. It exists so that the set of relationship objects encompasses all relationships, even those that correspond to foreign keys in the database. This makes iterating over all relationships in a class a simple affair. 1033 1034 foreach my $rel (Product->meta->relationships) 1035 { 1036 print $rel->name, ': ', $rel->type, "\n"; 1037 } 1038 1039For the C<Product> class, the output is: 1040 1041 vendor: many to one 1042 1043Given the two possible cardinalities, "many" and "one", it's easy to come up with a list of all possible inter-table relationships. Here they are, listed with their corresponding relationship object classes. 1044 1045 one to one - Rose::DB::Object::Metadata::Relationship::OneToOne 1046 one to many - Rose::DB::Object::Metadata::Relationship::OneToMany 1047 many to one - Rose::DB::Object::Metadata::Relationship::ManyToOne 1048 many to many - Rose::DB::Object::Metadata::Relationship::ManyToMany 1049 1050We've already seen that "one to one" and "many to one" relationships can be represented by foreign keys in the database, but that's not a requirement. It's perfectly possible to have either of those two kinds of relationships in a database that has no native support for foreign keys. (MySQL using the MyISAM storage engine is a common example.) 1051 1052If you find yourself using such a database, there's no reason to lie to your Perl classes by adding foreign key metadata. Instead, simply add a relationship. 1053 1054Here's an example of our C<Product> class as it might exist on a database that does not support foreign keys. (The C<Product> class is getting larger now, so previously established portions may be omitted from now on.) 1055 1056 package Product; 1057 1058 use base 'My::DB::Object'; 1059 1060 __PACKAGE__->meta->setup 1061 ( 1062 table => 'products', 1063 columns => [... ], 1064 pk_columns => 'id', 1065 unique_key => 'name', 1066 1067 relationships => 1068 [ 1069 vendor => 1070 { 1071 type => 'many to one', 1072 class => 'Vendor', 1073 column_map => { vendor_id => 'id' }, 1074 }, 1075 ], 1076 ); 1077 1078They syntax and semantics are similar to those L<described|/"Foreign keys"> for foreign keys. The only slight differences are the names and types of parameters accepted by relationship objects. 1079 1080In the example above, a "many to one" relationship named "vendor" is set up. As demonstrated before, this definition can be reduced much further, allowing the convention manager to fill in the details. But unlike the case with the foreign key definition, where only the name was supplied, we must provide the relationship type as well. 1081 1082 relationships => [ vendor => { type => 'many to one' } ], 1083 1084There's an even more convenient shorthand for that: 1085 1086 relationships => [ vendor => 'many to one' ], 1087 1088(Again, this all depends on naming the tables, classes, and columns in accordance with the expectations of the L<convention manager|Rose::DB::Object::Metadata/convention_manager>.) The resulting C<vendor()> and C<delete_vendor()> methods behave exactly the same as the methods created on behalf of the foreign key definition. 1089 1090=head3 One-to-many relationships 1091 1092Now let's explore the other two relationship types. We'll start with "one to many" by adding region-specific pricing to our products. First, we'll need a C<prices> table. 1093 1094 CREATE TABLE prices 1095 ( 1096 id SERIAL NOT NULL PRIMARY KEY, 1097 product_id INT NOT NULL REFERENCES products (id), 1098 region CHAR(2) NOT NULL DEFAULT 'US', 1099 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1100 1101 UNIQUE(product_id, region) 1102 ); 1103 1104This table needs a corresponding L<Rose::DB::Object>-derived class, of course. 1105 1106 package Price; 1107 1108 use base 'My::DB::Object'; 1109 1110 __PACKAGE__->meta->setup 1111 ( 1112 table => 'prices', 1113 1114 columns => 1115 [ 1116 id => { type => 'serial', not_null => 1 }, 1117 product_id => { type => 'int', not_null => 1 }, 1118 region => { type => 'char', length => 2, not_null => 1 }, 1119 price => 1120 { 1121 type => 'decimal', 1122 precision => 10, 1123 scale => 2, 1124 not_null => 1, 1125 default => 0.00 1126 }, 1127 ], 1128 1129 primary_key_columns => [ 'id' ], 1130 1131 unique_key => [ 'product_id', 'region' ], 1132 1133 foreign_keys => 1134 [ 1135 product => 1136 { 1137 class => 'Product', 1138 key_columns => { product_id => 'id' }, 1139 }, 1140 ], 1141 ); 1142 1143The C<price> column can be removed from the C<products> table. 1144 1145 ALTER TABLE products DROP COLUMN price; 1146 1147Finally, the C<Product> class needs to be modified to reference the C<prices> table. 1148 1149 package Product; 1150 1151 use base 'My::DB::Object'; 1152 1153 use Price; 1154 use Vendor; 1155 1156 __PACKAGE__->meta->setup 1157 ( 1158 table => 'products', 1159 columns => [ ... ], 1160 pk_columns => 'id', 1161 unique_key => 'name', 1162 1163 foreign_keys => 1164 [ 1165 vendor => 1166 { 1167 class => 'Vendor', 1168 key_columns => { vendor_id => 'id' }, 1169 }, 1170 ], 1171 1172 relationships => 1173 [ 1174 prices => 1175 { 1176 type => 'one to many', 1177 class => 'Price', 1178 column_map => { id => 'product_id' }, 1179 }, 1180 ], 1181 ); 1182 1183Note that both the L<column map|Rose::DB::Object::Metadata::Relationship::OneToMany/column_map> for the "one to many" relationship and the L<key columns|Rose::DB::Object::Metadata::ForeignKey/key_columns> for the foreign key connect "local" columns to "foreign" columns. 1184 1185The C<vendor_id> column in the local table (C<products>) is connected to the C<id> column in the foreign table (C<vendors>): 1186 1187 vendor => 1188 { 1189 key_columns => { vendor_id => 'id' }, 1190 ... 1191 } 1192 1193The C<id> column in the local table (C<products>) is connected to the C<product_id> column in the foreign table (C<prices>): 1194 1195 prices => 1196 { 1197 column_map => { id => 'product_id' }, 1198 ... 1199 } 1200 1201This is all from the perspective of the class in which the definitions appear. Note that things are reversed in the C<Price> class. 1202 1203 package Price; 1204 ... 1205 __PACKAGE__->meta->setup 1206 ( 1207 ... 1208 foreign_keys => 1209 [ 1210 product => 1211 { 1212 class => 'Product', 1213 key_columns => { product_id => 'id' }, 1214 }, 1215 ], 1216 ); 1217 1218Here, the C<product_id> column in the local table (C<prices>) is connected to the C<id> column in the foreign table (C<products>). 1219 1220The methods created by "... to many" relationships behave much like their "... to one" and foreign key counterparts. The main difference is that lists or references to arrays of the L<previously described|/"Foreign keys"> argument formats are also acceptable, while name/value pairs outside of a hashref are not. 1221 1222Here's a list of argument types accepted by "many to one" methods like C<prices>. 1223 1224=over 4 1225 1226=item * A list or reference to an array of objects of the appropriate class. 1227 1228=item * A list or reference to an array of hash references containing name/value pairs used to construct such objects. 1229 1230=item * A list or reference to an array of primary key values (but only if the foreign table has a single-column primary key). 1231 1232=back 1233 1234Setting a new list of prices will delete all the old prices. As with foreign keys, any actual database modification happens when the parent object is saved. Here are some examples. 1235 1236 $p = Product->new(name => 'Kite'); 1237 $p->prices({ price => 1.23, region => 'US' }, 1238 { price => 4.56, region => 'UK' }); 1239 1240 $p->save; # database is modified here 1241 1242 # US: 1.23, UK: 4.56 1243 print join(', ', map { $_->region . ': ' . $_->price } $p->prices); 1244 1245New prices can be added without deleting and resetting the entire list: 1246 1247 # Add two prices to the existing list 1248 $p->add_prices({ price => 7.89, region => 'DE' }, 1249 { price => 1.11, region => 'JP' }); 1250 1251 $p->save; # database is modified here 1252 1253Passing a reference to an empty array will cause all the prices to be deleted: 1254 1255 $p->prices([]); # delete all prices associated with this product 1256 $p->save; # database is modified here 1257 1258=head3 Cascading delete 1259 1260Deleting a product now becomes slightly more interesting. The naive approach fails. 1261 1262 $p->delete; # Fatal error! 1263 1264 # DBD::Pg::st execute failed: ERROR: update or delete on "products" 1265 # violates foreign key constraint "prices_product_id_fkey" on 1266 # "prices" 1267 # DETAIL: Key (id)=(12345) is still referenced from table "prices". 1268 1269Since rows in the C<prices> table now link to rows in the C<products> table, a product cannot be deleted until all of the prices that refer to it are also deleted. There are a few ways to deal with this. 1270 1271The best solution is to add a trigger to the C<products> table itself in the database that makes sure to delete any associated prices before deleting a product. This change will allow the naive approach shown above to work correctly. 1272 1273A less robust solution is necessary if your database does not support triggers. One such solution is to manually delete the prices before deleting the product. This can be done in several ways. The prices can be deleted directly, like this. 1274 1275 foreach my $price ($p->prices) 1276 { 1277 $price->delete; # Delete all associated prices 1278 } 1279 1280 $p->delete; # Now it's safe to delete the product 1281 1282The list of prices for the product can also be set to an empty list, which will have the effect of deleting all associated prices when the product is saved. 1283 1284 $p->prices([]); 1285 $p->save; # All associated prices deleted here 1286 $p->delete; # Now it's safe to delete the product 1287 1288Finally, the L<delete()|Rose::DB::Object/delete> method can actually automate this process, and do it all inside a transaction as well. 1289 1290 $p->delete(cascade => 1); # Delete all associated rows too 1291 1292Again, the recommended approach is to use triggers inside the database itself. But if necessary, these other approaches will work too. 1293 1294=head3 Many-to-many relationships 1295 1296The final relationship type is the most complex. In a "many to many" relationship, a single row in table A may be related to multiple rows in table B, while a single row in table B may also be related to multiple rows in table A. (Confused? A concrete example will follow shortly.) 1297 1298This kind of relationship involves three tables instead of just two. The "local" and "foreign" tables, familiar from the other relationship types described above, still exist, but now there's a third table that connects rows from those two tables. This third table is called the "mapping table," and the L<Rose::DB::Object>-derived class that fronts it is called the "map class." 1299 1300Let's add such a relationship to our growing family of classes. Imagine that each product may come in several colors. Right away, both the "one to one" and "many to one" relationship types are eliminated since they can only provide a single color for any given product. 1301 1302But wait, isn't a "one to many" relationship suitable? After all, one product may have many colors. Unfortunately, such a relationship is wasteful in this case. Let's see why. Imagine a C<colors> table like this. 1303 1304 CREATE TABLE colors 1305 ( 1306 id SERIAL NOT NULL PRIMARY KEY, 1307 name VARCHAR(255) NOT NULL, 1308 product_id INT NOT NULL REFERENCES products (id) 1309 ); 1310 1311Here's a simple C<Color> class to front it. 1312 1313 package Color; 1314 1315 use base 'My::DB::Object'; 1316 1317 __PACKAGE__->meta->setup 1318 ( 1319 table => 'colors', 1320 columns => 1321 [ 1322 id => { type => 'serial', primary_key => 1, not_null => 1 }, 1323 name => { type => 'varchar', length => 255, not_null => 1 }, 1324 product_id => { type => 'int', not_null => 1 }, 1325 ], 1326 1327 foreign_keys => 1328 [ 1329 product => 1330 { 1331 class => 'Product', 1332 key_columns => { product_id => 'id' }, 1333 }, 1334 ], 1335 ); 1336 1337Finally, let's add the "one to many" relationship to the C<Product> class. 1338 1339 package Product; 1340 1341 use base 'My::DB::Object'; 1342 1343 __PACKAGE__->meta->setup 1344 ( 1345 ... 1346 relationships => 1347 [ 1348 colors => 1349 { 1350 type => 'one to many', 1351 class => 'Color', 1352 column_map => { id => 'product_id' }, 1353 }, 1354 ... 1355 ], 1356 ); 1357 1358It works as expected. 1359 1360 $p1 = Product->new(id => 10, 1361 name => 'Sled', 1362 colors => 1363 [ 1364 { name => 'red' }, 1365 { name => 'green' }, 1366 ]); 1367 $p1->save; 1368 1369 $p2 = Product->new(id => 20, 1370 name => 'Kite', 1371 colors => 1372 [ 1373 { name => 'blue' }, 1374 { name => 'green' }, 1375 { name => 'red' }, 1376 ]); 1377 $p2->save; 1378 1379But now look at the contents of the C<colors> table in the database. 1380 1381 mydb=# select * from colors; 1382 1383 id | name | product_id 1384 ----+-------+------------ 1385 1 | red | 10 1386 2 | green | 10 1387 3 | blue | 20 1388 4 | green | 20 1389 5 | red | 20 1390 1391Notice that the colors "green" and "red" appear twice. Now imagine that there are 50,000 products. What are the odds that there will be more than a few colors in common among them? 1392 1393This is a poor database design. To fix it, we must recognize that colors will be shared among products, since the set of possible colors is relatively small compared to the set of possible products. One product may have many colors, but one color may also belong to many products. And there you have it: a textbook "many to many" relationship. 1394 1395Let's redesign this relationship in "many to many" form, starting with a new version of the C<colors> table. 1396 1397 CREATE TABLE colors 1398 ( 1399 id SERIAL NOT NULL PRIMARY KEY, 1400 name VARCHAR(255) NOT NULL, 1401 1402 UNIQUE(name) 1403 ); 1404 1405Since each color will now appear only once in this table, we can make the C<name> column a unique key. 1406 1407Here's the new C<Color> class. 1408 1409 package Color; 1410 1411 use base 'My::DB::Object'; 1412 1413 __PACKAGE__->meta->setup 1414 ( 1415 table => 'colors', 1416 columns => 1417 [ 1418 id => { type => 'serial', primary_key => 1, not_null => 1 }, 1419 name => { type => 'varchar', length => 255, not_null => 1 }, 1420 ], 1421 1422 unique_key => 'name', 1423 ); 1424 1425Since the C<colors> table no longer has a foreign key that points to the C<products> table, we need some way to connect the two tables: a mapping table. 1426 1427 CREATE TABLE product_color_map 1428 ( 1429 product_id INT NOT NULL REFERENCES products (id), 1430 color_id INT NOT NULL REFERENCES colors (id), 1431 1432 PRIMARY KEY(product_id, color_id) 1433 ); 1434 1435Note that there's no reason for a separate primary key column in this table. We'll use a two-column primary key instead. 1436 1437Here's the map class. 1438 1439 package ProductColorMap; 1440 1441 use base 'My::DB::Object'; 1442 1443 __PACKAGE__->meta->setup 1444 ( 1445 table => 'product_color_map', 1446 columns => 1447 [ 1448 product_id => { type => 'int', not_null => 1 }, 1449 color_id => { type => 'int', not_null => 1 }, 1450 ], 1451 1452 primary_key_columns => [ 'product_id', 'color_id' ], 1453 1454 foreign_keys => 1455 [ 1456 product => 1457 { 1458 class => 'Product', 1459 key_columns => { product_id => 'id' }, 1460 }, 1461 1462 color => 1463 { 1464 class => 'Color', 1465 key_columns => { color_id => 'id' }, 1466 }, 1467 ], 1468 ); 1469 1470It's important that the map class have either a foreign key or a "many to one" relationship pointing to each of the tables that it maps between. In this case, there are two foreign keys. 1471 1472Finally, here's the "many to many" relationship definition in the C<Product> class. 1473 1474 package Product; 1475 ... 1476 __PACKAGE__->meta->setup 1477 ( 1478 ... 1479 relationships => 1480 [ 1481 colors => 1482 { 1483 type => 'many to many', 1484 map_class => 'ProductColorMap' 1485 map_from => 'product', 1486 map_to => 'color', 1487 }, 1488 ... 1489 ], 1490 ); 1491 1492Note that only the map class needs to be C<use>d in the C<Product> class. The relationship definition specifies the name of the map class, and (optionally) the names of the foreign keys or "many to one" relationships in the map class that connect the two tables. 1493 1494In most cases, these two parameters (C<map_from> and C<map_to>) are unnecessary. L<Rose::DB::Object> will figure out what to do given only the map class, so long as there's no ambiguity in the mapping table. 1495 1496In this case, there is no ambiguity, so the relationship definition can be shortened to this. 1497 1498 1499 use Product; 1500 ... 1501 __PACKAGE__->meta->setup 1502 ( 1503 relationships => 1504 [ 1505 colors => 1506 { 1507 type => 'many to many', 1508 map_class => 'ProductColorMap' 1509 }, 1510 ], 1511 ... 1512 ); 1513 1514In fact, since the map table is named according to the default L<conventions|Rose::DB::Object::ConventionManager>, it can be shortened even further. 1515 1516 use Product; 1517 ... 1518 __PACKAGE__->meta->setup 1519 ( 1520 relationships => 1521 [ 1522 colors => { type => 'many to many' }, 1523 ... 1524 ], 1525 ... 1526 ); 1527 1528And further still: 1529 1530 use Product; 1531 ... 1532 __PACKAGE__->meta->setup 1533 ( 1534 relationships => 1535 [ 1536 colors => 'many to many', 1537 ... 1538 ], 1539 ... 1540 ); 1541 1542(Classes can be shortened even more absurdly when auto-initialization is combined with the convention manager. See the L<convention manager documentation|Rose::DB::Object::ConventionManager/"AUTO-INIT EXAMPLE"> for an example.) 1543 1544Now let's revisit the example code. 1545 1546 $p1 = Product->new(id => 10, 1547 name => 'Sled', 1548 colors => 1549 [ 1550 { name => 'red' }, 1551 { name => 'green' } 1552 ]); 1553 $p1->save; 1554 1555 $p2 = Product->new(id => 20, 1556 name => 'Kite', 1557 colors => 1558 [ 1559 { name => 'blue' }, 1560 { name => 'green' }, 1561 { name => 'red' }, 1562 ]); 1563 $p2->save; 1564 1565The code works as expected, but the database now looks much nicer. 1566 1567 mydb=# select * from colors; 1568 1569 id | name 1570 ----+------- 1571 1 | red 1572 2 | green 1573 3 | blue 1574 1575 1576 mydb=# select * from product_color_map; 1577 1578 product_id | color_id 1579 ------------+---------- 1580 10 | 1 1581 10 | 2 1582 20 | 3 1583 20 | 2 1584 20 | 1 1585 1586Each color appears only once, and the mapping table handles all the connections between the C<colors> and C<products> tables. 1587 1588The "many to many" C<colors> method works much like the "one to many" C<prices> method described earlier. The valid argument formats are the same. 1589 1590=over 4 1591 1592=item * A list or reference to an array of objects of the appropriate class. 1593 1594=item * A list or reference to an array of hash references containing name/value pairs used to construct such objects. 1595 1596=item * A list or reference to an array of primary key values (but only if the foreign table has a single-column primary key). 1597 1598=back 1599 1600The database modification behavior is also the same, with changes happening when the "parent" object is saved. 1601 1602 $p = Product->new(id => 123)->load; 1603 1604 $p->colors({ name => 'green' }, 1605 { name => 'blue' }); 1606 1607 $p->save; # database is modified here 1608 1609Setting the list of colors replaces the old list, but in the case of a "many to many" relationship, only the map records are deleted. 1610 1611 $p = Product->new(id => 123)->load; 1612 1613 $p->colors({ name => 'pink' }, 1614 { name => 'orange' }); 1615 1616 # Delete old rows in the mapping table and create new ones 1617 $p->save; 1618 1619New colors can be added without deleting and resetting the entire list: 1620 1621 # Add two colors to the existing list 1622 $p->add_colors({ name => 'gray' }, 1623 { name => 'red' }); 1624 1625 $p->save; # database is modified here 1626 1627Passing a reference to an empty array will remove all colors associated with a particular product by deleting all the mapping table entries. 1628 1629 $p->colors([]); 1630 $p->save; # all mapping table entries for this product deleted here 1631 1632Finally, the same caveats L<described earlier|/"Cascading delete"> about deleting products that have associated prices apply to colors as well. Again, I recommend using a trigger in the database to handle this, but L<Rose::DB::Object>'s cascading delete feature will work in a pinch. 1633 1634 # Delete all associated rows in the prices table, plus any 1635 # rows in the product_color_map table, before deleting the 1636 # row in the products table. 1637 $p->delete(cascade => 1); 1638 1639=head3 Relationship code summary 1640 1641To summarize this exploration of inter-table relationships, here's a terse summary of the current state of our Perl classes, and the associated database tables. 1642 1643For the sake of brevity, I've chosen to use the shorter versions of the foreign key and relationship definitions in the Perl classes shown below. Just remember that this only works when your tables, columns, and classes are named according to the expected L<conventions|Rose::DB::Object::ConventionManager>. 1644 1645First, the database schema. 1646 1647 CREATE TABLE vendors 1648 ( 1649 id SERIAL NOT NULL PRIMARY KEY, 1650 name VARCHAR(255) NOT NULL, 1651 1652 UNIQUE(name) 1653 ); 1654 1655 CREATE TABLE products 1656 ( 1657 id SERIAL NOT NULL PRIMARY KEY, 1658 name VARCHAR(255) NOT NULL, 1659 1660 vendor_id INT REFERENCES vendors (id), 1661 1662 status VARCHAR(128) NOT NULL DEFAULT 'inactive' 1663 CHECK(status IN ('inactive', 'active', 'defunct')), 1664 1665 date_created TIMESTAMP NOT NULL DEFAULT NOW(), 1666 release_date TIMESTAMP, 1667 1668 UNIQUE(name) 1669 ); 1670 1671 CREATE TABLE prices 1672 ( 1673 id SERIAL NOT NULL PRIMARY KEY, 1674 product_id INT NOT NULL REFERENCES products (id), 1675 region CHAR(2) NOT NULL DEFAULT 'US', 1676 price DECIMAL(10,2) NOT NULL DEFAULT 0.00, 1677 1678 UNIQUE(product_id, region) 1679 ); 1680 1681 CREATE TABLE colors 1682 ( 1683 id SERIAL NOT NULL PRIMARY KEY, 1684 name VARCHAR(255) NOT NULL, 1685 1686 UNIQUE(name) 1687 ); 1688 1689 CREATE TABLE product_color_map 1690 ( 1691 product_id INT NOT NULL REFERENCES products (id), 1692 color_id INT NOT NULL REFERENCES colors (id), 1693 1694 PRIMARY KEY(product_id, color_id) 1695 ); 1696 1697Now the Perl classes. Remember that these must each be in their own ".pm" files, despite appearing in one contiguous code snippet below. 1698 1699 package Vendor; 1700 1701 use base 'My::DB::Object'; 1702 1703 __PACKAGE__->meta->setup 1704 ( 1705 table => 'vendors', 1706 columns => 1707 [ 1708 id => { type => 'serial', primary_key => 1, not_null => 1 }, 1709 name => { type => 'varchar', length => 255, not_null => 1 }, 1710 ], 1711 1712 unique_key => 'name', 1713 ); 1714 1715 1; 1716 1717 1718 package Product; 1719 1720 use base 'My::DB::Object'; 1721 1722 __PACKAGE__->meta->setup 1723 ( 1724 table => 'products', 1725 columns => 1726 [ 1727 id => { type => 'integer', not_null => 1 }, 1728 name => { type => 'varchar', length => 255, not_null => 1 }, 1729 1730 vendor_id => { type => 'int' }, 1731 status => { type => 'varchar', default => 'inactive', 1732 length => 128, not_null => 1 }, 1733 date_created => { type => 'timestamp', not_null => 1, 1734 default => 'now()' }, 1735 release_date => { type => 'timestamp' }, 1736 ] 1737 1738 primary_key_columns => 'id', 1739 1740 unique_key => 'name', 1741 1742 allow_inline_column_values => 1, 1743 1744 relationships => 1745 [ 1746 prices => 'one to many', 1747 colors => 'many to many', 1748 ] 1749 ); 1750 1751 1; 1752 1753 1754 package Price; 1755 1756 use Product; 1757 1758 use base 'My::DB::Object'; 1759 1760 __PACKAGE__->meta->setup 1761 ( 1762 table => 'prices', 1763 1764 columns => 1765 [ 1766 id => { type => 'serial', primary_key => 1, not_null => 1 }, 1767 product_id => { type => 'int', not_null => 1 }, 1768 region => { type => 'char', length => 2, not_null => 1 }, 1769 price => 1770 { 1771 type => 'decimal', 1772 precision => 10, 1773 scale => 2, 1774 not_null => 1, 1775 default => 0.00 1776 }, 1777 ], 1778 1779 unique_key => [ 'product_id', 'region' ], 1780 1781 foreign_key => [ 'product' ], 1782 ); 1783 1784 1; 1785 1786 1787 package Color; 1788 1789 use base 'My::DB::Object'; 1790 1791 __PACKAGE__->meta->setup 1792 ( 1793 table => 'colors', 1794 columns => 1795 [ 1796 id => { type => 'serial', primary_key => 1, not_null => 1 }, 1797 name => { type => 'varchar', length => 255, not_null => 1 }, 1798 ], 1799 unique_key => 'name', 1800 ); 1801 1802 1; 1803 1804 1805 package ProductColorMap; 1806 1807 use base 'My::DB::Object'; 1808 1809 __PACKAGE__->meta->setup 1810 ( 1811 table => 'product_color_map', 1812 columns => 1813 [ 1814 product_id => { type => 'int', not_null => 1 }, 1815 color_id => { type => 'int', not_null => 1 }, 1816 ], 1817 pk_columns => [ 'product_id', 'color_id' ], 1818 foreign_keys => [ 'product', 'color' ], 1819 ); 1820 1821 1; 1822 1823=head2 The loader 1824 1825If the code above still looks like too much work to you, try letting L<Rose::DB::Object::Loader> do it all for you. Given the database schema L<shown above|/"Relationship code summary">, the suite of associated Perl classes could have been created automatically with a single method call. 1826 1827 $loader = 1828 Rose::DB::Object::Loader->new(db => Rose::DB->new, 1829 class_prefix => 'My::'); 1830 1831 $loader->make_classes; 1832 1833If you want to see what the loader did for you, catch the return value of the L<make_classes|Rose::DB::Object::Loader/make_classes> method (which will be a list of class names) and then ask each class to print its perl equivalent. 1834 1835 @classes = $loader->make_classes; 1836 1837 foreach my $class (@classes) 1838 { 1839 if($class->isa('Rose::DB::Object')) 1840 { 1841 print $class->meta->perl_class_definition(braces => 'bsd', 1842 indent => 2), "\n"; 1843 } 1844 else # Rose::DB::Object::Manager subclasses 1845 { 1846 print $class->perl_class_definition, "\n"; 1847 } 1848 } 1849 1850You can also ask the loader to make actual Perl modules (that is, a set of actual *.pm files in the file system) by calling the aptly named L<make_modules|Rose::DB::Object::Loader/make_modules> method. 1851 1852The code created by the loader is shown below. Compare it to the manually created Perl code L<shown above|/"Relationship code summary"> and you'll see that it's nearly identical. Again, careful table name choices really help here. Do what the L<convention manager|Rose::DB::Object::ConventionManager> expects (or write your own convention manager subclass that does what I<you> expect) and automation like this can work very well. 1853 1854 package My::Color; 1855 1856 use strict; 1857 1858 use base qw(My::DB::Object::Base1); 1859 1860 __PACKAGE__->meta->setup 1861 ( 1862 table => 'colors', 1863 1864 columns => 1865 [ 1866 id => { type => 'integer', not_null => 1 }, 1867 name => { type => 'varchar', length => 255, not_null => 1 }, 1868 ], 1869 1870 primary_key_columns => [ 'id' ], 1871 1872 unique_keys => [ 'name' ], 1873 1874 relationships => 1875 [ 1876 products => 1877 { 1878 column_map => { color_id => 'id' }, 1879 foreign_class => 'My::Product', 1880 map_class => 'My::ProductColorMap', 1881 map_from => 'color', 1882 map_to => 'product', 1883 type => 'many to many', 1884 }, 1885 ], 1886 ); 1887 1888 1; 1889 1890 package My::Color::Manager; 1891 1892 use base qw(Rose::DB::Object::Manager); 1893 1894 use My::Color; 1895 1896 sub object_class { 'My::Color' } 1897 1898 __PACKAGE__->make_manager_methods('colors'); 1899 1900 1; 1901 1902 package My::Price; 1903 1904 use strict; 1905 1906 use base qw(My::DB::Object::Base1); 1907 1908 __PACKAGE__->meta->setup 1909 ( 1910 table => 'prices', 1911 1912 columns => 1913 [ 1914 id => { type => 'integer', not_null => 1 }, 1915 product_id => { type => 'integer', not_null => 1 }, 1916 region => { type => 'character', default => 'US', length => 2, 1917 not_null => 1 }, 1918 price => { type => 'numeric', default => '0.00', not_null => 1, 1919 precision => 10, scale => 2 }, 1920 ], 1921 1922 primary_key_columns => [ 'id' ], 1923 1924 unique_key => [ 'product_id', 'region' ], 1925 1926 foreign_keys => 1927 [ 1928 product => 1929 { 1930 class => 'My::Product', 1931 key_columns => 1932 { 1933 product_id => 'id', 1934 }, 1935 }, 1936 ], 1937 ); 1938 1939 1; 1940 1941 package My::Price::Manager; 1942 1943 use base qw(Rose::DB::Object::Manager); 1944 1945 use My::Price; 1946 1947 sub object_class { 'My::Price' } 1948 1949 __PACKAGE__->make_manager_methods('prices'); 1950 1951 1; 1952 1953 package My::ProductColorMap; 1954 1955 use strict; 1956 1957 use base qw(My::DB::Object::Base1); 1958 1959 __PACKAGE__->meta->setup 1960 ( 1961 table => 'product_color_map', 1962 1963 columns => 1964 [ 1965 product_id => { type => 'integer', not_null => 1 }, 1966 color_id => { type => 'integer', not_null => 1 }, 1967 ], 1968 1969 primary_key_columns => [ 'product_id', 'color_id' ], 1970 1971 foreign_keys => 1972 [ 1973 color => 1974 { 1975 class => 'My::Color', 1976 key_columns => 1977 { 1978 color_id => 'id', 1979 }, 1980 }, 1981 1982 product => 1983 { 1984 class => 'My::Product', 1985 key_columns => 1986 { 1987 product_id => 'id', 1988 }, 1989 }, 1990 ], 1991 ); 1992 1993 1; 1994 1995 package My::ProductColorMap::Manager; 1996 1997 use base qw(Rose::DB::Object::Manager); 1998 1999 use My::ProductColorMap; 2000 2001 sub object_class { 'My::ProductColorMap' } 2002 2003 __PACKAGE__->make_manager_methods('product_color_map'); 2004 2005 1; 2006 2007 package My::ProductColor; 2008 2009 use strict; 2010 2011 use base qw(My::DB::Object::Base1); 2012 2013 __PACKAGE__->meta->setup 2014 ( 2015 table => 'product_colors', 2016 2017 columns => 2018 [ 2019 id => { type => 'integer', not_null => 1 }, 2020 product_id => { type => 'integer', not_null => 1 }, 2021 color_code => { type => 'character', length => 3, not_null => 1 }, 2022 ], 2023 2024 primary_key_columns => [ 'id' ], 2025 ); 2026 2027 1; 2028 2029 package My::ProductColor::Manager; 2030 2031 use base qw(Rose::DB::Object::Manager); 2032 2033 use My::ProductColor; 2034 2035 sub object_class { 'My::ProductColor' } 2036 2037 __PACKAGE__->make_manager_methods('product_colors'); 2038 2039 1; 2040 2041 package My::Product; 2042 2043 use strict; 2044 2045 use base qw(My::DB::Object::Base1); 2046 2047 __PACKAGE__->meta->setup 2048 ( 2049 table => 'products', 2050 2051 columns => 2052 [ 2053 id => { type => 'integer', not_null => 1 }, 2054 name => { type => 'varchar', length => 255, not_null => 1 }, 2055 price => { type => 'numeric', default => '0.00', not_null => 1, 2056 precision => 10, scale => 2 }, 2057 vendor_id => { type => 'integer' }, 2058 status => { type => 'varchar', default => 'inactive', 2059 length => 128, not_null => 1 }, 2060 date_created => { type => 'timestamp', default => 'now()', 2061 not_null => 1 }, 2062 release_date => { type => 'timestamp' }, 2063 ], 2064 2065 primary_key_columns => [ 'id' ], 2066 2067 unique_keys => [ 'name' ], 2068 2069 allow_inline_column_values => 1, 2070 2071 foreign_keys => 2072 [ 2073 vendor => 2074 { 2075 class => 'My::Vendor', 2076 key_columns => 2077 { 2078 vendor_id => 'id', 2079 }, 2080 }, 2081 ], 2082 2083 relationships => 2084 [ 2085 colors => 2086 { 2087 column_map => { product_id => 'id' }, 2088 foreign_class => 'My::Color', 2089 map_class => 'My::ProductColorMap', 2090 map_from => 'product', 2091 map_to => 'color', 2092 type => 'many to many', 2093 }, 2094 2095 prices => 2096 { 2097 class => 'My::Price', 2098 key_columns => { id => 'product_id' }, 2099 type => 'one to many', 2100 }, 2101 ], 2102 ); 2103 2104 1; 2105 2106 package My::Product::Manager; 2107 2108 use base qw(Rose::DB::Object::Manager); 2109 2110 use My::Product; 2111 2112 sub object_class { 'My::Product' } 2113 2114 __PACKAGE__->make_manager_methods('products'); 2115 2116 1; 2117 2118 package My::Vendor; 2119 2120 use strict; 2121 2122 use base qw(My::DB::Object::Base1); 2123 2124 __PACKAGE__->meta->setup 2125 ( 2126 table => 'vendors', 2127 2128 columns => 2129 [ 2130 id => { type => 'integer', not_null => 1 }, 2131 name => { type => 'varchar', length => 255, not_null => 1 }, 2132 ], 2133 2134 primary_key_columns => [ 'id' ], 2135 2136 unique_keys => [ 'name' ], 2137 2138 relationships => 2139 [ 2140 products => 2141 { 2142 class => 'My::Product', 2143 key_columns => { id => 'vendor_id' }, 2144 type => 'one to many', 2145 }, 2146 ], 2147 ); 2148 2149 1; 2150 2151 package My::Vendor::Manager; 2152 2153 use base qw(Rose::DB::Object::Manager); 2154 2155 use My::Vendor; 2156 2157 sub object_class { 'My::Vendor' } 2158 2159 __PACKAGE__->make_manager_methods('vendors'); 2160 2161 1; 2162 2163=head2 Auto-joins and other Manager features 2164 2165The C<Product::Manager> class we created L<earlier|/"Multiple objects"> is deceptively simple. Setting it up can actually be reduced to a one-liner, but it provides a rich set of features. 2166 2167The basics demonstrated earlier cover most kinds of single-table SELECT statements. But as the C<Product> class has become more complex, linking to other objects via L<foreign keys|/"Foreign keys"> and other L<relationships|/"Relationships">, selecting rows from just the C<products> table has become a lot less appealing. What good is it to retrieve hundreds of products in a single query when you then have to execute hundreds of individual queries to get the prices of those products? 2168 2169This is what SQL JOINs were made for: selecting related rows from multiple tables simultaneously. L<Rose::DB::Object::Manager> supports a two kinds of joins. The interface to this functionality is presented in terms of objects via the C<require_objects> and C<with_objects> parameters to the L<get_objects()|Rose::DB::Object::Manager/get_objects> method. 2170 2171Both parameters expect a list of foreign key or relationship names. The C<require_objects> parameters will use an "inner join" to fetch related objects, while the C<with_objects> parameter will perform an "outer join." 2172 2173If you're unfamiliar with these terms, it's probably a good idea to learn about them from a good SQL book or web tutorial. But even if you've never written an SQL JOIN by hand, there's not much you need to understand in order to use your manager class effectively. 2174 2175The rule of thumb is simple. When you want each and every object returned by your query to have a particular related object, then use the C<require_objects> parameter. But if you do not want to exclude objects even if they do not have a particular related object attached to them yet, then use the C<with_objects> parameter. 2176 2177Sometimes, this decision is already made for you by the table structure. For example, let's modify the C<products> table in order to require that every single product has a vendor. To do so, we'll change the C<vendor_id> column definition from this: 2178 2179 vendor_id INT REFERENCES vendors (id) 2180 2181to this: 2182 2183 vendor_id INT NOT NULL REFERENCES vendors (id) 2184 2185Now it's impossible for a product to have a NULL C<vendor_id>. And since our database enforces referential integrity, it's also impossible for the C<vendor_id> column to have a value that does not refer to the C<id> of an existing row in the C<vendors> table. 2186 2187While the C<with_objects> parameter could technically be used to fetch C<Product>s with their associated C<Vendor> objects, it would be wasteful. (Outer joins are often less efficient than inner joins.) The table structure basically dictates that the C<require_objects> parameter be used when fetching C<Product>s with their C<Vendor>s. 2188 2189Here's how such a query could actually look. 2190 2191 $products = 2192 Product::Manager->get_products( 2193 query => 2194 [ 2195 name => { like => 'Kite%' }, 2196 id => { gt => 15 }, 2197 ] 2198 require_objects => [ 'vendor' ], 2199 sort_by => 'name'); 2200 2201Recall that the name of the foreign key that connects a product to its vendor is "vendor". Thus, the value of the C<require_objects> parameter is a reference to an array containing this name. 2202 2203Getting information about each product's vendor now no longer requires additional database queries. 2204 2205 foreach my $product (@$products) 2206 { 2207 # This does not hit the database at all 2208 print $product->vendor->name, "\n"; 2209 } 2210 2211For the SQL-inclined, the actual query run looks something like this. 2212 2213 SELECT 2214 t1.date_created, 2215 t1.id, 2216 t1.name, 2217 t1.release_date, 2218 t1.status, 2219 t1.vendor_id, 2220 t2.id, 2221 t2.name 2222 FROM 2223 products t1, 2224 vendors t2 2225 WHERE 2226 t1.id >= 16 AND 2227 t1.name LIKE 'Kite%' AND 2228 t1.vendor_id = t2.id 2229 ORDER BY t1.name 2230 2231As you can see, the query includes "tN" aliases for each table. This is important because columns in separate tables often have identical names. For example, both the C<products> and the C<vendors> tables have columns named C<id> and C<name>. 2232 2233In the query, you'll notice that the C<name =E<gt> { like =E<gt> 'Kite%' }> argument ended up filtering on the product name rather than the vendor name. This is intentional. Any unqualified column name that is ambiguous is considered to belong to the "primary" table (C<products>, in this case). 2234 2235The "tN" numbering is deterministic. The primary table is always "t1", and secondary tables are assigned ascending numbers starting from there. You can find a L<full explanation|Rose::DB::Object::Manager/get_objects> of the numbering rules in the L<Rose::DB::Object::Manager> documentation. 2236 2237In the example above, if we wanted to filter and sort on the vendor name instead, we could do this. 2238 2239 $products = 2240 Product::Manager->get_products( 2241 query => 2242 [ 2243 't2.name' => { like => 'Acm%' }, 2244 id => { gt => 15 }, 2245 ] 2246 require_objects => [ 'vendor' ], 2247 sort_by => 't2.name'); 2248 2249But that's not the only option. There are several ways to disambiguate a query clause. The column name can also be qualified by prefixing it with a relationship name. 2250 2251 $products = 2252 Product::Manager->get_products( 2253 query => 2254 [ 2255 'vendor.name' => { like => 'Acm%' }, 2256 id => { gt => 15 }, 2257 ] 2258 require_objects => [ 'vendor' ], 2259 sort_by => 'vendor.name'); 2260 2261The actual table name itself can also be used (although I do not recommend this practice since you will have to change all such usage instances if you ever rename the table). 2262 2263 $products = 2264 Product::Manager->get_products( 2265 query => 2266 [ 2267 'vendors.name' => { like => 'Acm%' }, 2268 id => { gt => 15 }, 2269 ] 2270 require_objects => [ 'vendor' ], 2271 sort_by => 'vendors.name'); 2272 2273Now let's see an example of the C<with_objects> parameter in action. Each C<Product> has zero or more C<Price>s. Let's fetch products with all their associated prices. And remember that some of these products may have no prices at all. 2274 2275 $products = 2276 Product::Manager->get_products( 2277 query => 2278 [ 2279 name => { like => 'Kite%' }, 2280 id => { gt => 15 }, 2281 ], 2282 with_objects => [ 'prices' ], 2283 sort_by => 'name'); 2284 2285Again, since the name of the "one to many" relationship that connects a product to its prices is "prices", this is the value use in the C<with_objects> parameter. The SQL looks something like this: 2286 2287 SELECT 2288 t1.date_created, 2289 t1.id, 2290 t1.name, 2291 t1.release_date, 2292 t1.status, 2293 t1.vendor_id, 2294 t2.id, 2295 t2.price, 2296 t2.product_id, 2297 t2.region 2298 FROM 2299 products t1 2300 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id) 2301 WHERE 2302 t1.id > 15 AND 2303 t1.name LIKE 'Kite%' 2304 ORDER BY t1.name 2305 2306Fetching products with both their vendors and prices (if any) is straightforward. Just use the C<require_objects> parameter for the vendors and the C<with_objects> parameter for the prices. 2307 2308 $products = 2309 Product::Manager->get_products( 2310 query => 2311 [ 2312 name => { like => 'Kite%' }, 2313 id => { gt => 15 }, 2314 ], 2315 require_objects => [ 'vendor' ], 2316 with_objects => [ 'prices' ], 2317 sort_by => 'name'); 2318 2319The resulting SQL is what you'd expect. 2320 2321 SELECT 2322 t1.date_created, 2323 t1.id, 2324 t1.name, 2325 t1.release_date, 2326 t1.status, 2327 t1.vendor_id, 2328 t2.id, 2329 t2.price, 2330 t2.product_id, 2331 t2.region, 2332 t3.id, 2333 t3.name 2334 FROM 2335 products t1 2336 JOIN vendors t3 ON (t1.vendor_id = t3.id) 2337 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id) 2338 WHERE 2339 t1.id > 15 AND 2340 t1.name LIKE 'Kite%' 2341 ORDER BY t1.name 2342 2343Each C<Product> also has zero or more C<Color>s which are related to it through a mapping table (fronted by the C<ProductColorMap> class, but we don't need to know that). The C<with_objects> parameter can handle that as well. 2344 2345 $products = 2346 Product::Manager->get_products( 2347 query => 2348 [ 2349 name => { like => 'Kite%' }, 2350 id => { gt => 15 }, 2351 ], 2352 with_objects => [ 'colors' ], 2353 sort_by => 'name'); 2354 2355The resulting SQL is a bit more complex. 2356 2357 SELECT 2358 t1.date_created, 2359 t1.id, 2360 t1.name, 2361 t1.release_date, 2362 t1.status, 2363 t1.vendor_id, 2364 t3.id, 2365 t3.name 2366 FROM 2367 products t1 2368 LEFT OUTER JOIN product_color_map t2 ON(t2.product_id = t1.id) 2369 LEFT OUTER JOIN colors t3 ON(t2.color_id = t3.id) 2370 WHERE 2371 t1.id > 15 AND 2372 t1.name LIKE 'Kite%' 2373 2374Again, combinations are straightforward. Let's fetch products with their vendors and colors. 2375 2376 $products = 2377 Product::Manager->get_products( 2378 query => 2379 [ 2380 name => { like => 'Kite%' }, 2381 id => { gt => 15 }, 2382 ], 2383 require_objects => [ 'vendor' ], 2384 with_objects => [ 'colors' ], 2385 sort_by => 'name'); 2386 2387Now the SQL is starting to get a bit hairy. 2388 2389 SELECT 2390 t1.id, 2391 t1.name, 2392 t1.vendor_id, 2393 t3.code, 2394 t3.name, 2395 t4.id, 2396 t4.name, 2397 t4.region_id 2398 FROM 2399 products t1 2400 JOIN vendors t4 ON (t1.vendor_id = t4.id) 2401 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id) 2402 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code) 2403 WHERE 2404 t1.id > 15 AND 2405 t1.name LIKE 'Kite%' 2406 2407Anyone who knows SQL well will recognize that there is a danger lurking when combining JOINs. Multiple joins that each fetch multiple rows can result in a geometric explosion of rows returned by the database. For example, the number of rows returned when fetching products with their associated prices and colors would be: 2408 2409 <number of matching products> x 2410 <number of prices for each product> x 2411 <number of colors for each product> 2412 2413That number can get very large, very fast if products have many prices, colors, or both. (The last two terms in the multiplication maybe switched, depending on the order of the actual JOIN clauses, but the results are similar.) And the problem only gets worse as the number of objects related by "... to many" relationships increases. 2414 2415That said, L<Rose::DB::Object::Manager> does allow multiple objects related by "... to many" relationships to be fetched simultaneously. But it requires the developer to supply the C<multi_many_ok> parameter with a true value as a form of confirmation. "Yes, I know the risks, but I want to do it anyway." 2416 2417As an example, let's try fetching products with their associated prices, colors, and vendors. To do so, we'll have to include the C<multi_many_ok> parameter. 2418 2419 $products = 2420 Product::Manager->get_products( 2421 query => 2422 [ 2423 name => { like => 'Kite%' }, 2424 id => { gt => 15 }, 2425 ], 2426 require_objects => [ 'vendor' ], 2427 with_objects => [ 'colors', 'prices' ], 2428 multi_many_ok => 1, 2429 sort_by => 'name'); 2430 2431Here's the SQL. 2432 2433 SELECT 2434 t1.id, 2435 t1.name, 2436 t1.vendor_id, 2437 t3.code, 2438 t3.name, 2439 t4.price_id, 2440 t4.product_id, 2441 t4.region, 2442 t4.price, 2443 t5.id, 2444 t5.name, 2445 t5.region_id 2446 FROM 2447 products t1 2448 JOIN vendors t5 ON (t1.vendor_id = t5.id) 2449 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id) 2450 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code) 2451 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id) 2452 WHERE 2453 t1.id > 15 AND 2454 t1.name LIKE 'Kite%' 2455 ORDER BY t1.name 2456 2457It's questionable whether this five-way join will be faster than doing a four- or three-way join and then fetching the other information after the fact, with separate queries. It all depends on the number of rows expected to match. Only you know your data. You must choose the most efficient query that suits your needs. 2458 2459Moving beyond even the example above, it's possible to chain foreign key or relationship names to an arbitrary depth. For example, imagine that each C<Vendor> has a C<Region> related to it by a foreign key named "region". The following call will get region information for each product's vendor, filtering on the region name. 2460 2461 $products = 2462 Product::Manager->get_products( 2463 query => 2464 [ 2465 'vendor.region.name' => 'UK', 2466 'name' => { like => 'Kite%' }, 2467 'id' => { gt => 15 }, 2468 ], 2469 require_objects => [ 'vendor.region' ], 2470 with_objects => [ 'colors', 'prices' ], 2471 multi_many_ok => 1, 2472 sort_by => 'name'); 2473 2474The SQL would now look something like this. 2475 2476 SELECT 2477 t1.id, 2478 t1.name, 2479 t1.vendor_id, 2480 t3.code, 2481 t3.name, 2482 t4.price_id, 2483 t4.product_id, 2484 t4.region, 2485 t4.price, 2486 t5.id, 2487 t5.name, 2488 t5.region_id, 2489 t6.id, 2490 t6.name 2491 FROM 2492 products t1 2493 JOIN (vendors t5 JOIN regions t6 ON (t5.region_id = t6.id)) 2494 ON (t1.vendor_id = t5.id) 2495 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id) 2496 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code) 2497 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id) 2498 WHERE 2499 t1.id > 15 AND 2500 t1.name LIKE 'Kite%' AND 2501 t6.name = 'UK' 2502 ORDER BY t1.name 2503 2504The same caveat about performance and the potential explosion of redundant data when JOINing across multiple "... to many" relationships also applies to the "chained" selectors demonstrated above--even more so, in fact, as the depth of the chain increases. That said, it's usually safe to go a few levels deep into "... to one" relationships when using the C<require_objects> parameter. 2505 2506Finally, it's also possible to load a single product with all of its associated foreign objects. The L<load()|Rose::DB::Object/load> method accepts a C<with> parameter that takes a list of foreign key and relationship names. 2507 2508 $product = Product->new(id => 1234); 2509 $product->load(with => [ 'vendor', 'colors', 'prices' ]); 2510 2511The same "multi many" caveats apply, but the C<multi_many_ok> parameter is not required in this case. The assumption is that a single object won't have too many related objects. But again, only you know your data, so be careful. 2512 2513=head2 Wrap-up 2514 2515I hope you've learned something from this tutorial. Although it is by no means a complete tour of all of the features of L<Rose::DB::Object>, it does hit most of the highlights. This tutorial will likely expand in the future, and a separate document describing the various ways that L<Rose::DB::Object> can be extended is also planned. For now, there is a brief overview that was pulled from the L<Rose::DB::Object> mailing list in the wiki. 2516 2517http://code.google.com/p/rose/wiki/RDBOExtending 2518 2519See the L<support|/SUPPORT> section below for more information on the mailing list. 2520 2521=head1 DEVELOPMENT POLICY 2522 2523The L<Rose development policy|Rose/"DEVELOPMENT POLICY"> applies to this, and all C<Rose::*> modules. Please install L<Rose> from CPAN and then run "C<perldoc Rose>" for more information. 2524 2525=head1 SUPPORT 2526 2527Any L<Rose::DB::Object> questions or problems can be posted to the L<Rose::DB::Object> mailing list. To subscribe to the list or view the archives, go here: 2528 2529L<http://groups.google.com/group/rose-db-object> 2530 2531Although the mailing list is the preferred support mechanism, you can also email the author (see below) or file bugs using the CPAN bug tracking system: 2532 2533L<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object> 2534 2535There's also a wiki and other resources linked from the Rose project home page: 2536 2537L<http://rose.googlecode.com> 2538 2539=head1 AUTHOR 2540 2541John C. Siracusa (siracusa@gmail.com) 2542 2543=head1 COPYRIGHT 2544 2545Copyright (c) 2007 by John C. Siracusa. All rights reserved. This program is 2546free software; you can redistribute it and/or modify it under the same terms 2547as Perl itself. 2548