1Rose::DB::Object::TutorUisaelr(3C)ontributed Perl DocumeRnotsaet:i:oDnB::Object::Tutorial(3)
2
6 Rose::DB::Object::Tutorial - A guided tour of the basics of
7 Rose::DB::Object
8
10 This document provides a step-by-step introduction to the
11 Rose::DB::Object module distribution. It demonstrates all of the
12 important features using a semi-realistic example database. This
13 tutorial does not replace the actual documentation for each module,
14 however. The "reference" documentation found in each ".pm" file is
15 still essential, and contains some good examples of its own.
16 This tutorial provides a gradual introduction to Rose::DB::Object. It
18 also describes "best practices" for using Rose::DB::Object in the most
19 robust, maintainable manner. If you're just trying to get a feel for
20 what's possible, you can skip to the end and take a look at the
21 completed example database and associated Perl code. But I recommend
22 reading the tutorial from start to finish at least once.
23 The examples will start simple and get progressively more complex.
25 You, the developer, have to decide which level of complexity or
26 abstraction is appropriate for your particular task.
27
29 Some of the examples in this tutorial will use the fictional "My::"
30 namespace prefix. Some will use no prefix at all. Your code should
31 use whatever namespace you deem appropriate. Usually, it will be
32 something like "MyCorp::MyProject::" (i.e., your corporation,
33 organization, and/or project). I've chosen to use "My::" or to omit
34 the prefix entirely simply because this produces shorter class names,
35 which will help this tutorial stay within an 80-column width.
36 For the sake of brevity, the "use strict" directive and associated "my"
38 declarations have also been omitted from the example code. Needless to
39 say, you should always "use strict" in your actual code.
40 Similarly, the traditional "1;" true value used at the end of each
42 ".pm" file has been omitted from the examples. Don't forget to add
43 this to the end of your actual Perl module files.
44 Although most of the examples in this tutorial use the base.pm module
46 to set up inheritance, directly modifying the @ISA package variable
47 usually works just as well. In situations where there are circular
48 relationships between classes, the "use base ..." form may be
49 preferable because it runs at compile-time, whereas @ISA modification
50 happens at run-time. In either case, it's a good idea to set up
51 inheritance as early as possible in each module.
52 package Product;
54 # Set up inheritance first
56 use base qw(Rose::DB::Object);
57 # Then do other stuff...
59 ...
60
62 Preface
63 Before doing anything useful with Rose::DB::Object, it's necessary to
64 create and configure a Rose::DB subclass through which
65 Rose::DB::Object-derived objects will access the database.
66 To get up to speed quickly with Rose::DB, read the Rose::DB::Tutorial
68 documentation. The rest of this tutorial will assume the existence of
69 a "My::DB" class created as described in the Rose::DB tutorial. Here's
70 a possible incarnation of the "My::DB" class.
71 package My::DB;
73 use base qw(Rose::DB);
75 __PACKAGE__->use_private_registry;
77 __PACKAGE__->register_db(
79 driver => 'pg',
80 database => 'mydb',
81 host => 'localhost',
82 username => 'devuser',
83 password => 'mysecret',
84 );
85 Read the Rose::DB tutorial for an explanation of this code.
87 The PostgreSQL database will be used in the examples in this tutorial,
89 but the features demonstrated will not be specific to that database.
90 If you are following along with a different database, you may have to
91 adjust the specific syntax used in the SQL table creation statements,
92 but all of the same features should be present in some form.
93 This tutorial is based on a fictional database schema for a store-like
95 application. Both the database schema the corresponding Perl classes
96 will evolve over the course of this document.
97 Getting started
99 Let's start with a single table in our fictional store database.
100 CREATE TABLE products
102 (
103 id SERIAL NOT NULL PRIMARY KEY,
104 name VARCHAR(255) NOT NULL,
105 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
106 UNIQUE(name)
108 );
109 Here's a basic Rose::DB::Object class to front that table:
111 package Product;
113 use base qw(Rose::DB::Object);
115 __PACKAGE__->meta->setup
117 (
118 table => 'products',
119 columns => [ qw(id name price) ],
120 pk_columns => 'id',
121 unique_key => 'name',
122 );
123 The steps are simple:
125 1. Inherit from Rose::DB::Object.
127 2. Name the table.
128 3. Name the columns.
129 4. Name the primary key column(s).
130 5. Add unique keys (if any).
131 6. Initialize. (Implied at the end of the setup call)
132 Operations 2 through 6 are done through the setup method on the
134 metadata object associated with this class. The table must have a
135 primary key, and may have zero or more unique keys. The primary key
136 and each unique key may contain multiple columns.
137 Of course, earlier it was established that Rose::DB needs to be set up
139 for any Rose::DB::Object class to work properly. To that end, this
140 tutorial assumes the existence of a Rose::DB subclass named My::DB that
141 is set up according to the best practices of Rose::DB. We need to make
142 the "Product" class use My::DB. Here's one way to do it:
143 package Product;
145 use My::DB;
147 use base qw(Rose::DB::Object);
149 __PACKAGE__->meta->setup
151 (
152 table => 'products',
153 columns => [ qw(id name price) ],
154 pk_columns => 'id',
155 unique_key => 'name',
156 );
157 sub init_db { My::DB->new }
159 Now "Product" will create a My::DB object when it needs to connect to
161 the database.
162 Note that the "My::DB->new" call in init_db() means that each "Product"
164 object will have its own, private "My::DB" object. See the section
165 below, "A brief digression: database objects", for an explanation of
166 this setup and some alternatives.
167 Setting up your own base class
169 Looking forward, it's likely that all of our Rose::DB::Object-derived
171 classes will want to use My::DB objects when connecting to the
172 database. It's tedious to repeat this code in all of those classes. A
173 common base class can provide a single, shared location for that code.
174 package My::DB::Object;
176 use My::DB;
178 use base qw(Rose::DB::Object);
180 sub init_db { My::DB->new }
182 (Again, note that all "My::DB::Object"-derived objects will get their
184 own "My::DB" objects given this definition of init_db(). See the
185 "digression" section below for more information.)
186 Now the "Product" class can inherit from "My::DB::Object" instead of
188 inheriting from Rose::DB::Object directly.
189 package Product;
191 use base 'My::DB::Object';
193 __PACKAGE__->meta->setup
195 (
196 table => 'products',
197 columns => [ qw(id name price) ],
198 pk_columns => 'id',
199 unique_key => 'name',
200 );
201 This use of a common base class is strongly recommended. You will see
203 this pattern repeated in the Rose::DB tutorial as well. The creation
204 of seemingly "trivial" subclasses is a cheap and easy way to ensure
205 ease of extensibility later on.
206 For example, imagine we want to add a copy() method to all of our
208 database objects. If they all inherit directly from
209 "Rose::DB::Object", that's not easy to do. But if they all inherit
210 from "My::DB::Object", we can just add the copy() method to that class.
211 The lesson is simple: when in doubt, subclass. A few minutes spent now
213 can save you a lot more time down the road.
214 Rose::DB::Object in action
216 Now that we have our "Product" class all set up, let's see what we can
218 do with it.
219 Get and set column values
221 By default, each column has a combined accessor/mutator method. When
223 passed a value, the column value is set and returned. When called with
224 no arguments, the value is simply returned.
225 $p->name('Bike'); # set name
227 print $p->name; # get name
228 Since Rose::DB::Object inherits from Rose::Object, each object method
230 is also a valid constructor argument.
231 $p = Product->new(name => 'Cane', price => 1.99);
233 print $p->price; # 1.99
234 Load
236 An object can be loaded based on a primary key.
238 $p = Product->new(id => 1); # primary key
240 $p->load; # Load the object from the database
241 An object can also be loaded based on a unique key:
243 $p = Product->new(name => 'Sled'); # unique key
245 $p->load; # Load the object from the database
246 If there is no row in the database table with the specified primary or
248 unique key value, the call to load() will fail. Under the default
249 error mode, an exception will be thrown. To safely check whether or
250 not such a row exists, use the "speculative" parameter.
251 $p = Product->new(id => 1);
253 unless($p->load(speculative => 1))
255 {
256 print "No such product with id = 1";
257 }
258 Regardless of the error mode, load() will simply return true or false
260 when the "speculative" parameter is used.
261 Insert
263 To insert a row, create an object and then save it.
265 $p = Product->new(id => 123, name => 'Widget', price => 4.56);
267 $p->save; # Insert the object into the database
268 The default error mode will throw an exception if anything goes wrong
270 during the save, so we don't have to check the return value.
271 Here's another variation:
273 $p = Product->new(name => 'Widget', price => 1.23);
275 $p->save;
276 print $p->id; # print the auto-generated primary key value
278 Since the primary key of the "products" table, "id", is a SERIAL
280 column, a new primary key value will be automatically generated if one
281 is not specified. After the object is saved, we can retrieve the auto-
282 generated value.
283 Update
285 To update a row, simply save an object that has been previously loaded
287 or saved.
288 $p1 = Product->new(name => 'Sprocket', price => 9.99);
290 $p1->save; # Insert a new object into the database
291 $p1->price(12.00);
293 $p1->save; # Update the object in the database
294 $p2 = Product->new(id => 1);
296 $p2->load; # Load an existing object
297 $p2->name($p2->name . ' Mark II');
299 $p2->save; # Update the object in the database
300 Delete
302 An object can be deleted based on a primary key or a unique key.
304 $p = Product->new(id => 1); # primary key
306 $p->delete; # Delete the object from the database
307 $p = Product->new(name => 'Sled'); # unique key
309 $p->delete; # Delete the object from the database
310 The delete method will return true if the row was deleted or did not
312 exist, false otherwise.
313 It works just as well with objects that have been loaded or saved.
315 $p1 = Product->new(name => 'Sprocket', price => 9.99);
317 $p1->save; # Insert a new object into the database
318 $p1->delete; # Now delete the object
319 $p2 = Product->new(id => 1);
321 $p2->load; # Load an existing object
322 $p2->delete; # Now delete the object
323 Multiple objects
325 The examples above show SELECT, INSERT, UPDATE, and DELETE operations
327 on one row at time based on primary or unique keys. What about
328 manipulating rows based on other criteria? What about manipulating
329 multiple rows simultaneously? Enter Rose::DB::Object::Manager, or just
330 "the manager" for short.
331 But why is there a separate class for dealing with multiple objects?
333 Why not simply add more methods to the object itself? Say, a search()
334 method to go alongside load(), save(), delete() and friends? There are
335 several reasons.
336 First, it's somewhat "semantically impure" for the class that
338 represents a single row to also be the class that's used to fetch
339 multiple rows. It's also important to keep the object method namespace
340 as sparsely populated as possible. Each new object method prevents a
341 column with the same name from using that method name.
342 Rose::DB::Object tries to keep the list of reserved method names as
343 small as possible.
344 Second, inevitably, classes grow. It's important for the object
346 manager class to be separate from the object class itself so each class
347 can grow happily in isolation, with no potential for namespace or
348 functionality clashes.
349 All of that being said, Rose::DB::Object::Manager does include support
351 for adding manager methods to the object class. Obviously, this
352 practice is not recommended, but it exists if you really want it.
353 Anyway, let's see some examples. Making a manager class is simply a
355 matter of inheriting from Rose::DB::Object::Manager, specifying the
356 object class, and then creating a series of appropriately named wrapper
357 methods.
358 package Product::Manager;
360 use base qw(Rose::DB::Object::Manager);
362 sub object_class { 'Product' }
364 __PACKAGE__->make_manager_methods('products');
366 The call to make_manager_methods() creates the following methods:
368 get_products
370 get_products_iterator
371 get_products_count
372 delete_products
373 update_products
374 The names are pretty much self-explanatory. You can read the
376 Rose::DB::Object::Manager documentation for all the gory details. The
377 important thing to note is that the methods were all named based on the
378 "products" argument to make_manager_methods(). You can see how
379 "products" has been incorporated into each of the method names.
380 This naming scheme is just a suggestion. You can name these methods
382 anything you want (using the "methods" parameter to the
383 make_manager_methods() call), or you can even write the methods
384 yourself. Each of these methods is a merely a thin wrapper around the
385 generically-named methods in Rose::DB::Object::Manager. The wrappers
386 pass the specified object class to the generic methods.
387 The Perl code for the "Product::Manager" class shown above can be
389 generated automatically by calling the perl_manager_class method on the
390 Rose::DB::Object::Metadata that's associated with the "Product" class.
391 Similarly, the make_manager_class method called on the "Product"
392 metadata object will both generate the code and evaluate it for you,
393 automating the entire process of creating a manager class from within
394 your Rose::DB::Object-derived class.
395 package Product;
397 use base qw(Rose::DB::Object);
399 ...
400 # This actually creates the Product::Manager class
402 # as shown in the code sample above.
403 __PACKAGE__->meta->make_manager_class('products');
404 As the comment says, the call to make_manager_class will create a
406 standalone "Product::Manager" class in memory. See the documentation
407 for the perl_manager_class and make_manager_class methods for more
408 information.
409 If you decide not to heed my advice, but instead decide to create these
411 methods inside your Rose::DB::Object-derived class directly, you can do
412 so by calling make_manager_methods() from within your object class.
413 package Product;
415 use Rose::DB::Object::Manager;
417 use base 'My::DB::Object';
419 ...
420 Rose::DB::Object::Manager->make_manager_methods('products');
421 This will be the last you see of this technique in this tutorial. All
423 of the examples will assume that the recommended approach is used
424 instead.
425 Fetching objects
427 The most common task for the manager is fetching multiple objects.
429 We'll use the get_products() method to do that. It's based on the
430 get_objects() method, which takes many parameters.
431 One (optional) parameter is the now-familiar db object used to connect
433 to the database. This parameter is valid for all
434 Rose::DB::Object::Manager methods. In the absence of this parameter,
435 the init_db() method of the object class will be called in order to
436 create one.
437 Passing no arguments at all will simply fetch every "Product" object in
439 the database.
440 $products = Product::Manager->get_products();
442 foreach my $product (@$products)
444 {
445 print $product->name, "\n";
446 }
447 The return value is a reference to an array of "Product" objects. Now
449 let's go to the other extreme.
450 $products =
452 Product::Manager->get_products(
453 query =>
454 [
455 name => { like => '%Hat' },
456 id => { ge => 7 },
457 or =>
458 [
459 price => 15.00,
460 price => { lt => 10.00 },
461 ],
462 ],
463 sort_by => 'name',
464 limit => 10,
465 offset => 50);
466 That call produces SQL that looks something like this:
468 SELECT id, name, price FROM products WHERE
470 name LIKE '%Hat' AND
471 id >= 7 AND
472 (price = 15.00 OR price < 10.00)
473 ORDER BY name
474 LIMIT 10 OFFSET 50
475 Manager queries support nested boolean logic and several different
477 kinds of comparison operators. For a full explanation of all the
478 options, see the Rose::DB::Object::Manager documentation.
479 The iterator method takes the same kinds of arguments, but returns an
481 iterator that will fetch the objects from the database one at a time.
482 $iterator = Product::Manager->get_products_iterator(...);
484 while($product = $iterator->next)
486 {
487 print $product->id, ' ', $product->name, "\n";
488 $iterator->finish if(...); # exit early?
490 }
491 print $iterator->total; # total iterated over
493 Note that this is a "real" iterator. Objects not iterated over are not
495 fetched from the database at all.
496 Counting objects
498 Counting objects is straightforward. The get_products_count() method
500 takes the same kinds of arguments as get_products() and
501 get_products_iterator(). It returns the count.
502 $num_cheap_products =
504 Product::Manager->get_products_count(
505 query => [ price => { lt => 1.00 } ]);
506 Deleting objects
508 The delete_products() method accepts the same kinds of "query"
510 arguments as the manager methods described above, only it uses the
511 parameter name "where" instead.
512 $num_rows_deleted =
514 Product::Manager->delete_products(
515 where =>
516 [
517 id => { ne => 123 },
518 name => { like => 'Wax%' },
519 ]);
520 Updating objects
522 The update_products() method accepts the same kinds of arguments as the
524 delete_products() method, plus a "set" parameter to specify the actual
525 update information.
526 $num_rows_updated =
528 Product::Manager->update_products(
529 set =>
530 {
531 price => 5.00,
532 },
533 where =>
534 [
535 price => 4.99,
536 id => { gt => 100 },
537 ]);
538 The end of the beginning
540 This section has covered the bare minimum usage and functionality of
542 the Rose::DB::Object module distribution. Using these features alone,
543 you can automate the basic CRUD operations (Create, Retrieve, Update,
544 and Delete) for single or multiple objects. But it's almost a shame to
545 stop at this point. There's a lot more that Rose::DB::Object can do
546 for you. The "sweet spot" of effort vs. results is much farther along
547 the curve.
548 In the next section, we will expand upon our "Product" class and tap
550 more of Rose::DB::Object's features. But first...
551 A brief digression: database objects
553 The Rose::DB-derived database object used by each
555 Rose::DB::Object-derived object is available via the db object
556 attribute.
557 $p = Product->new(...);
559 $db = $p->db; # My::DB object
560 You can read the Rose::DB documentation to explore the capabilities of
562 these db objects. Most of the time, you won't have to be concerned
563 about them. But it's sometime useful to deal with them directly.
564 The first thing to understand is where the database object comes from.
566 If the db attribute doesn't exist, it is created by calling init_db().
567 The typical init_db() method simply builds a new database object and
568 returns it. (See the Rose::DB tutorial for an explanation of the
569 possible arguments to new(), and why there are none in the call below.)
570 package Product;
572 ...
573 sub init_db { My::DB->new }
574 This means that each "Product" object will have its own "My::DB"
576 object, and therefore (in the absence of modules like Apache::DBI) its
577 own connection to the database.
578 If this not what you want, you can make init_db() return the same
580 "My::DB" object to every "Product" object. This will make it harder to
581 ensure that the database handle will be closed when all "Product"
582 objects go out of scope, but that may not be important for your
583 application. The easiest way to do this is to call new_or_cached
584 instead of new.
585 package Product;
587 ...
588 sub init_db { My::DB->new_or_cached }
589 Since init_db() is only called if a "Product" object does not already
591 have a db object, another way to share a single "My::DB" object with
592 several "Product" objects is to do so explicitly, either by pre-
593 creating the "My::DB" object:
594 $db = My::DB->new; # will share this db with the Products below
596 $p1 = Product->new(db => $db, ...);
598 $p2 = Product->new(db => $db, ...);
599 $p3 = Product->new(db => $db, ...);
600 or by letting one of the "Product" objects provide the db for the rest.
602 $p1 = Product->new(...);
604 $p2 = Product->new(db => $p1->db, ...); # use $p1's db
605 $p3 = Product->new(db => $p1->db, ...); # use $p1's db
606 A note for mod_perl users: when using Apache::DBI, even if each
608 "Product" has its own "My::DB" object, remember that they will all
609 share a single underlying DBI database handle. That is, each
610 Rose::DB-derived object of a given type and domain will eventually call
611 DBI's connect() method with the same arguments, and therefore return
612 the same, cached database handle when running under Apache::DBI. The
613 default cache implementation underlying the new_or_cached method is
614 also mod_perl-aware and will cooperate with Apache::DBI.
615 Here's an example where sharing a database object is important:
617 creating several "Product" objects in a single transaction.
618 $db = My::DB->new;
620 $db->begin_work; # Start transaction
622 # Use this $db with each product object
624 $p1 = Product->new(name => 'Bike', db => $db);
626 $p1->save;
627 $p2 = Product->new(name => 'Sled', db => $db);
629 $p2->save;
630 $p3 = Product->new(name => 'Kite', db => $db);
632 $p3->save;
633 if(...) # Now either commit them all or roll them all back
635 {
636 $db->commit;
637 }
638 else
639 {
640 $db->rollback;
641 }
642 Cross-database migration is another important use for explicitly shared
644 db objects. Here's how to move a product from a production database to
645 an archive database.
646 $production_db = My::DB->new('production');
648 $archive_db = My::DB->new('archive');
649 # Load bike from production database
651 $p = Product->new(name => 'Bike', db => $production_db);
652 $p->load;
653 # Save the bike into the archive database
655 $p->db($archive_db);
656 $p->save(insert => 1); # force an insert instead of an update
657 # Delete the bike from the production database
659 $p->db($production_db);
660 $p->delete;
661 Mainstream usage
663 Let's imagine that the "products" table has expanded. It now looks
664 like this.
665 CREATE TABLE products
667 (
668 id SERIAL NOT NULL PRIMARY KEY,
669 name VARCHAR(255) NOT NULL,
670 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
671 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
673 CHECK(status IN ('inactive', 'active', 'defunct')),
674 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
676 release_date TIMESTAMP,
677 UNIQUE(name)
679 );
680 We could do a straightforward expansion of the "Product" class as
682 designed in the previous section.
683 package Product;
685 use base 'My::DB::Object';
687 __PACKAGE__->meta->setup
689 (
690 table => 'products',
691 columns => [ qw(id name price status date_created release_date) ],
692 pk_columns => 'id',
693 unique_key => 'name',
694 );
695 But now we're faced with a few problems. First, while the "status"
697 column only accepts a few pre-defined values, our "Product" object will
698 gladly accept any status value. But maybe that's okay because the
699 database will reject invalid values, causing an exception will be
700 thrown when the object is saved.
701 The date/time fields are more troubling. What is the format of a valid
703 value for a TIMESTAMP column in PostgreSQL? Consulting the PostgreSQL
704 documentation will yield the answer, I suppose. But now all the code
705 that uses "Product" objects has to be sure to format the "date_created"
706 and "release_date" values accordingly. That's even more difficult if
707 some of those values come from external sources, such as a web form.
708 Worse, what if we decide to change databases in the future? We'd have
710 to hunt down every single place where a "date_created" or
711 "release_date" value is set and then modify the formatting to match
712 whatever format the new database wants. Oh, and we'll have to look
713 that up too. Blah.
714 Finally, what about all those default values? The "price" column
716 already had a default value, but now two more columns also have
717 defaults. True, the database will take care of this when a row is
718 inserted, but now the Perl object is diverging more and more from the
719 database representation.
720 Let's solve all of these problems. If we more accurately describe the
722 columns, Rose::DB::Object will do the rest.
723 package Product;
725 use base 'My::DB::Object';
727 __PACKAGE__->meta->setup
729 (
730 table => 'products',
731 columns =>
733 [
734 id => { type => 'serial', primary_key => 1, not_null => 1 },
735 name => { type => 'varchar', length => 255, not_null => 1 },
736 price =>
738 {
739 type => 'decimal',
740 precision => 10,
741 scale => 2,
742 not_null => 1,
743 default => 0.00
744 },
745 status =>
747 {
748 type => 'varchar',
749 length => 128,
750 not_null => 1,
751 default => 'inactive',
752 check_in => [ 'inactive', 'active', 'defunct' ],
753 },
754 date_created => { type => 'timestamp', not_null => 1,
756 default => 'now()' },
757 release_date => { type => 'timestamp' },
758 ],
759 unique_key => 'name',
761 allow_inline_column_values => 1,
763 );
764 Before examining what new functionality this new class gives us, there
766 are a few things to note about the definition. First, the primary key
767 is no longer specified with the primary_key_columns() method. Instead,
768 the "id" column has its "primary_key" attribute set to a true value in
769 its description.
770 Second, note the default value for the "date_created" column. It's a
772 string containing a call to the PL/SQL function now(), which can
773 actually only be run within the database. But thanks to the
774 allow_inline_column_values attribute being set to a true value,
775 Rose::DB::Object will pass the string "now()" through to the database
776 as-is.
777 In the case of "creation date" columns like this, it's often better to
779 let the database provide the value as close as possible to the very
780 moment the row is created. On the other hand, this will mean that any
781 newly created "Product" object will have a "strange" value for that
782 column (the string "now()") until/unless it is re-loaded from the
783 database. It's a trade-off.
784 Let's see the new "Product" class in action. The defaults work as
786 expected.
787 $p = Product->new;
789 print $p->status; # 'inactive'
791 print $p->price; # 0.00
792 The "status" method now restricts its input, throwing an exception if
794 the input is invalid.
795 $p->status('nonesuch'); # Boom! Invalid status: 'nonesuch'
797 The timestamp columns now accept any value that Rose::DateTime::Util's
799 parse_date() method can understand.
800 $p->release_date('2005-01-22 18:00:57');
802 $p->release_date('12/24/1980 10am');
803 See the Rose::DateTime::Util documentation for a full list of
805 acceptable formats.
806 Inside a "Product" object, date/time information is stored in DateTime
808 objects.
809 $dt = $p->release_date; # DateTime object
811 Since DateTime objects can be modified in-place, doing a formerly
813 thorny task like date math is now trivial.
814 $p->release_date->add(days => 1);
816 The release_date() method also accepts a DateTime object as an input,
818 of course:
819 $p->release_date(DateTime->new(...));
821 There are even a few convenience functions triggered by passing a
823 name/value pair.
824 # Thursday, December 25th 1980 at 10:00:00 AM
826 print $p->release_date(format => '%A, %B %E %Y at %t');
827 # Clone the DateTime object, truncate the clone, and return it
829 $month_start = $p->release_date(truncate => 'month');
830 print $month_start->strftime('%Y-%m-%d'); # 1980-12-01
832 Conveniently, Rose::DB::Object::Manager queries can also use any values
834 that the corresponding column methods will accept. For example, here's
835 a query that filters on the "release_date" column using a DateTime
836 object.
837 $last_week = DateTime->now->subtract(weeks => 1);
839 $products =
841 Product::Manager->get_products(
842 query =>
843 [
844 release_date => { lt => $last_week },
845 ],
846 sort_by => 'release_date');
847 The upshot is that you no longer have to be concerned about the details
849 of the date/time format(s) understood by the underlying database.
850 You're also free to use DateTime objects as a convenient interchange
851 format in your code.
852 This ability isn't just limited to date/time columns. Any data type
854 that requires special formatting in the database, and/or is more
855 conveniently dealt with as a more "rich" value on the Perl side of the
856 fence is fair game for this treatment.
857 Some other examples include the bitfield column type, which is
859 represented by a Bit::Vector object on the Perl side, and the boolean
860 column type which evaluates the "truth" of its arguments and coerces
861 the value accordingly. In all cases, column values are automatically
862 formatted as required by the native column data types in the database.
863 In some circumstances, Rose::DB::Object can even "fake" a data type for
865 use with a database that does not natively support it. For example,
866 the array column type is natively supported by PostgreSQL, but it will
867 also work with MySQL using a VARCHAR column as a stand-in.
868 Finally, if you're concerned about the performance implications of
870 "inflating" column values from strings and numbers into (relatively)
871 large objects, rest assured that such inflation is only done as needed.
872 For example, an object with ten date/time columns can be loaded,
873 modified, and saved without ever creating a single DateTime object,
874 provided that none of the date/time columns were among those whose
875 values were modified.
876 Put another way, the methods that service the columns have an awareness
878 of the producer and consumer of their data. When data is coming from
879 the database, the column methods accept it as-is. When data is being
880 sent to the database, it is formatted appropriately, if necessary. If
881 a column value was not modified since it was loaded from the database,
882 then the value that was loaded is simply returned as-is. In this way,
883 data can make a round-trip without ever being inflated, deflated, or
884 formatted.
885 This behavior is not a requirement of all column methods, but it is a
887 recommended practice--one followed by all the column classes that are
888 part of the Rose::DB::Object distribution.
889 Auto-initialization and the convention manager
891 The "Product" class set up in the previous section is useful, but it
892 also takes significantly more typing to set up. Over the long term,
893 it's still a clear win. On the other hand, a lot of the details in the
894 column descriptions are already known by the database: column types,
895 default values, maximum lengths, etc. It would be handy if we could
896 ask the database for this information instead of looking it up and
897 typing it in manually.
898 This process of interrogating the database in order to extract metadata
900 is called "auto-initialization." There's an entire section of the
901 Rose::DB::Object::Metadata documentation dedicated to the topic. The
902 executive summary is that auto-initialization saves work in the short-
903 run, but with some long-term costs. Read the friendly manual for the
904 details. For the purposes of this tutorial, I will simply demonstrate
905 the features, culminating in the suggested best practice.
906 Let's start by applying auto-initialization to the "Product" class.
908 package Product;
910 use base 'My::DB::Object';
912 __PACKAGE__->meta->table('products');
914 __PACKAGE__->meta->auto_initialize;
915 Believe it or not, that class is equivalent to the previous
917 incarnation, right down to the details of the columns and the unique
918 key. As long as the table is specified, Rose::DB::Object will dig all
919 the rest of the information out of the database. Handy!
920 In fact, that class can be shortened even further with the help of the
922 convention manager.
923 package Product;
925 use base 'My::DB::Object';
927 __PACKAGE__->meta->auto_initialize;
929 Now even the table is left unspecified. How does Rose::DB::Object know
931 what to do in this case? Why, by convention, of course. The default
932 convention manager dictates that class names are singular and
933 TitleCased, and their corresponding table names are lowercase and
934 plural. Thus, the omitted table name in the "Product" class is, by
935 convention, assumed to be named "products".
936 Like auto-initialization, the convention manager is handy, but may also
938 present some maintenance issues. I tend to favor a more explicitly
939 approach, but I can also imagine scenarios where the convention manager
940 is a good fit.
941 Keep in mind that customized convention managers are possible, allowing
943 individual organizations or projects to define their own conventions.
944 You can read all about it in the Rose::DB::Object::ConventionManager
945 documentation.
946 Anyway, back to auto-initialization. Yes, auto_initialize() will dig
948 out all sorts of interesting and important information for you.
949 Unfortunately, it will dig that information out every single time the
950 class is loaded. Worse, this class will fail to load at all if a
951 database connection is not immediately available.
952 Auto-initialization seems like something that is best done only once,
954 with the results being saved in a more conventional form. That's just
955 what Rose::DB::Object::Metadata's code generation functions are
956 designed to do. The "perl_*" family of methods can generate snippets
957 of Perl code, or even entire classes, based on the results of the auto-
958 initialization process. They'll even honor some basic code formatting
959 directives.
960 package Product;
962 use base 'My::DB::Object';
964 __PACKAGE__->meta->table('products');
966 __PACKAGE__->meta->auto_initialize;
967 print __PACKAGE__->meta->perl_class_definition(indent => 2,
969 braces => 'bsd');
970 Here's the output of that print statement. A few long lines were
972 manually wrapped, but it's otherwise unmodified.
973 package Product;
975 use strict;
977 use base 'My::DB::Object';
979 __PACKAGE__->meta->setup
981 (
982 table => 'products',
983 columns =>
985 [
986 id => { type => 'integer', not_null => 1 },
987 name => { type => 'varchar', length => 255, not_null => 1 },
988 price => { type => 'numeric', default => '0.00',
989 not_null => 1, precision => 10, scale => 2 },
990 vendor_id => { type => 'integer' },
991 status => { type => 'varchar', default => 'inactive',
992 length => 128, not_null => 1 },
993 date_created => { type => 'timestamp', default => 'now()',
994 not_null => 1 },
995 release_date => { type => 'timestamp' },
996 ],
997 primary_key_columns => [ 'id' ],
999 unique_keys => [ 'name' ],
1001 allow_inline_column_values => 1,
1003 );
1004 1;
1006 Copy and paste that output back into the "Product.pm" file and you're
1008 in business.
1009 The door is open to further automation through scripts that call the
1011 methods demonstrated above. Although it's my inclination to work
1012 towards a static, explicit type of class definition, the tools are
1013 there for those who prefer a more dynamic approach.
1014 Foreign keys
1016 When a column in one table references a row in another table, the
1017 referring table is said to have a "foreign key." As with primary and
1018 unique keys, Rose::DB::Object supports foreign keys made up of more
1019 than one column.
1020 In the context of Rose::DB::Object, a foreign key is a database-
1022 supported construct that ensures that any non-null value in a foreign
1023 key column actually refers to an existing row in the foreign table.
1024 Databases that enforce this constraint are said to support "referential
1025 integrity." Foreign keys are only applicable to
1026 Rose::DB::Object-derived classes when the underlying database supports
1027 "native" foreign keys and enforces referential integrity.
1028 While it's possible to define foreign keys in a
1030 Rose::DB::Object-derived class even if there is no support for them in
1031 the database, this is considered bad practice. If you're just trying
1032 to express some sort of relationship between two tables, there's a more
1033 appropriate way to do so. (More on that in the next section.)
1034 Let's add a foreign key to the "products" table. First, we'll need to
1036 create the table that the foreign key will reference.
1037 CREATE TABLE vendors
1039 (
1040 id SERIAL NOT NULL PRIMARY KEY,
1041 name VARCHAR(255) NOT NULL,
1042 UNIQUE(name)
1044 );
1045 When dealing with any kind of inter-table relationship,
1047 Rose::DB::Object requires a Rose::DB::Object-derived class fronting
1048 each participating table. So we need a class for the "vendors" table.
1049 package Vendor;
1051 use base 'My::DB::Object';
1053 __PACKAGE__->meta->setup
1055 (
1056 table => 'vendors',
1057 columns =>
1058 [
1059 id => { type => 'serial', primary_key => 1, not_null => 1 },
1060 name => { type => 'varchar', length => 255, not_null => 1 },
1061 ],
1062 unique_key => 'name',
1063 );
1064 Now we'll add the foreign key to our ever-growing "products" table.
1066 CREATE TABLE products
1068 (
1069 id SERIAL NOT NULL PRIMARY KEY,
1070 name VARCHAR(255) NOT NULL,
1071 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
1072 vendor_id INT REFERENCES vendors (id),
1074 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
1076 CHECK(status IN ('inactive', 'active', 'defunct')),
1077 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
1079 release_date TIMESTAMP,
1080 UNIQUE(name)
1082 );
1083 Finally, here's how the foreign key definition looks in the Perl class.
1085 package Product;
1087 use base 'My::DB::Object';
1089 __PACKAGE__->meta->setup
1091 (
1092 table => 'products',
1093 columns =>
1095 [
1096 id => { type => 'integer', not_null => 1 },
1097 name => { type => 'varchar', length => 255, not_null => 1 },
1098 price => { type => 'numeric', default => '0.00',
1099 not_null => 1, precision => 10, scale => 2 },
1100 vendor_id => { type => 'integer' },
1101 status => { type => 'varchar', default => 'inactive',
1102 length => 128, not_null => 1 },
1103 date_created => { type => 'timestamp', default => 'now()',
1104 not_null => 1 },
1105 release_date => { type => 'timestamp' },
1106 ],
1107 primary_key_columns => [ 'id' ],
1109 unique_keys => [ 'name' ],
1111 allow_inline_column_values => 1,
1113 foreign_keys =>
1115 [
1116 vendor =>
1117 {
1118 class => 'Vendor',
1119 key_columns => { vendor_id => 'id' },
1120 },
1121 ],
1122 );
1123 Note that a "vendor_id" column is added to the column list. This needs
1125 to be done independently of any foreign key definition. It's a new
1126 column, so it needs to be in the column list. There's nothing more to
1127 it than that.
1128 There's also the foreign key definition itself. The name/hashref-value
1130 pair passed to the foreign_keys() method is (roughly) shorthand for
1131 this.
1132 Rose::DB::Object::Metadata::ForeignKey->new(
1134 name => 'vendor',
1135 class => 'Vendor',
1136 key_columns => { vendor_id => 'id' });
1137 In other words, "vendor" is the name of the foreign key, and the rest
1139 of the information is used to set attributes on the foreign key object.
1140 You could, in fact, construct your own foreign key objects and pass
1141 them to foreign_keys() (or add_foreign_keys(), etc.) but that would
1142 require even more typing.
1143 Going in the other direction, since our class and column names match up
1145 with what the convention manager expects, we could actually shorten the
1146 foreign key setup code to this.
1147 foreign_keys => [ 'vendor' ],
1149 Given only a foreign key name, the convention manager will derive the
1151 "Vendor" class name and will find the "vendor_id" column in the
1152 "Product" class and match it up to the primary key of the "vendors"
1153 table. As with most things in Rose::DB::Object class setup, you can be
1154 as explicit or as terse as you feel comfortable with, depending on how
1155 closely you conform to the expected conventions.
1156 So, what does this new "vendor" foreign key do for us? Let's add some
1158 data and see. Imagine the following two objects.
1159 $v = Vendor->new(name => 'Acme')->save;
1161 $p = Product->new(name => 'Kite')->save;
1162 Note the use of the idiomatic way to create and then save an object in
1164 "one step." This is possible because both the new and save methods
1165 return the object itself. Anyway, let's link the two objects. One way
1166 to do it is to set the column values directly.
1167 $p->vendor_id($v->id);
1169 $p->save;
1170 To use this technique, we must know which columns link to which other
1172 columns, of course. But it works. We can see this by calling the
1173 method named after the foreign key itself: vendor().
1174 $v = $p->vendor; # Vendor object
1176 print $v->name; # "Acme"
1177 The vendor() method can be used to link the two objects as well. Let's
1179 start over and try it that way:
1180 $v = Vendor->new(name => 'Smith')->save;
1182 $p = Product->new(name => 'Knife')->save;
1183 $p->vendor($v);
1185 $p->save;
1186 print $p->vendor->name; # "Smith"
1188 Remember that there is no column named "vendor" in the "products"
1190 table. There is a "vendor_id" column, which has its own vendor_id()
1191 get/set method that accepts and returns an integer value, but that's
1192 not what we're doing in the example above. Instead, we're calling the
1193 vendor() method, which accepts and returns an entire "Vendor" object.
1194 The vendor() method actually accepts several different kinds of
1196 arguments, all of which it inflates into "Vendor" objects. An already-
1197 formed "Vendor" object was passed above, but other formats are
1198 possible. Imagine a new product also made by Smith.
1199 $p = Product->new(name => 'Rope')->save;
1201 $p->vendor(name => 'Smith');
1202 $p->save;
1203 Here the arguments passed to the vendor() method are name/value pairs
1205 which will be used to construct the appropriate "Vendor" object. Since
1206 "name" is a unique key in the "vendors" table, the "Vendor" class can
1207 look up the existing vendor named "Smith" and assign it to the "Rope"
1208 product.
1209 If no vendor named "Smith" existed, one would have been created when
1211 the product was saved. In this case, the save process would take place
1212 within a transaction (assuming the database supports transactions) to
1213 ensure that both the product and vendor are created successfully, or
1214 neither is.
1215 The name/value pairs can also be provided in a reference to a hash.
1217 $p = Product->new(name => 'Rope')->save;
1219 $p->vendor({ name => 'Smith' });
1220 $p->save;
1221 Here's yet another argument format. Imagine that the "Acme" vendor id
1223 is 1.
1224 $p = Product->new(name => 'Crate')->save;
1226 $p->vendor(1);
1227 $p->save;
1228 print $p->vendor->name; # "Acme"
1230 Like the name/value pair argument format, a primary key value will be
1232 used to construct the appropriate object. (This only works if the
1233 foreign table has a single-column primary key, of course.) And like
1234 before, if such an object doesn't exist, it will be created. But in
1235 this case, if no existing vendor object had an "id" of 1, the attempt
1236 to create one would have failed because the "name" column of the
1237 inserted row would have been null.
1238 To summarize, the foreign key method can take arguments in these forms.
1240 • An object of the appropriate class.
1242 • Name/value pairs used to construct such an object.
1244 • A reference to a hash containing name/value pairs used to construct
1246 such an object.
1247 • A primary key value (but only if the foreign table has a single-
1249 column primary key).
1250 In each case, the foreign object will be added to the database it if
1252 does not already exist there. This all happens when the "parent"
1253 ("Product") object is saved. Until then, nothing is stored in the
1254 database.
1255 There's also another method created in response to the foreign key
1257 definition. This one allows the foreign object to be deleted from the
1258 database.
1259 print $p->vendor->name; # "Acme"
1261 $p->delete_vendor();
1262 $p->save; # The "Acme" vendor is deleted from the vendors table
1263 Again, the actual database modification takes place when the parent
1265 object is saved. Note that this operation will fail if any other rows
1266 in the "products" table still reference the Acme vendor. And again,
1267 since this all takes place within a transaction (where supported), the
1268 entire operation will fail or succeed as a single unit.
1269 Finally, if we want to simply disassociate a product from its vendor,
1271 we can simply set the vendor to undef.
1272 $p->vendor(undef); # This product has no vendor
1274 $p->save;
1275 Setting the "vendor_id" column directly has the same effect, of course.
1277 $p->vendor_id(undef); # set vendor_id = NULL
1279 $p->save;
1280 Before moving on to the next section, here's a brief note about auto-
1282 initialization and foreign keys. Since foreign keys are a construct of
1283 the database itself, the auto-initialization process can actually
1284 discover them and create the appropriate foreign key metadata.
1285 Since all of the column and table names are still in sync with the
1287 expected conventions, the "Product" class can still be defined like
1288 this:
1289 package Product;
1291 use base 'My::DB::Object';
1293 __PACKAGE__->meta->auto_initialize;
1295 while retaining all of the abilities demonstrated above.
1297 The perl_class_definition() method will produce the appropriate foreign
1299 key definitions, as expected.
1300 package Product;
1302 use base 'My::DB::Object';
1304 __PACKAGE__->meta->auto_initialize;
1306 print __PACKAGE__->meta->perl_class_definition(indent => 2,
1308 braces => 'bsd');
1309 Here's the output.
1311 package Product;
1313 use base 'My::DB::Object';
1315 __PACKAGE__->meta->setup
1317 (
1318 table => 'products',
1319 columns =>
1321 [
1322 id => { type => 'integer', not_null => 1 },
1323 name => { type => 'varchar', length => 255, not_null => 1 },
1324 price => { type => 'numeric', default => '0.00',
1325 not_null => 1, precision => 10, scale => 2 },
1326 vendor_id => { type => 'integer' },
1327 status => { type => 'varchar', default => 'inactive',
1328 length => 128, not_null => 1 },
1329 date_created => { type => 'timestamp', default => 'now()',
1330 not_null => 1 },
1331 release_date => { type => 'timestamp' },
1332 ],
1333 primary_key_columns => [ 'id' ],
1335 unique_keys => [ 'name' ],
1337 allow_inline_column_values => 1,
1339 foreign_keys =>
1341 [
1342 vendor =>
1343 {
1344 class => 'Vendor',
1345 key_columns => { vendor_id => 'id' },
1346 },
1347 ],
1348 );
1349 1;
1351 Relationships
1353 One-to-one and many-to-one relationships
1354 Foreign keys are a database-native representation of a specific kind of
1356 inter-table relationship. This concept can be further generalized to
1357 encompass other kinds of relationships as well. But before we delve
1358 into that, let's consider the kind of relationship that a foreign key
1359 represents.
1360 In the product and vendor example in the previous section, each product
1362 has one vendor. (Actually it can have zero or one vendor, since the
1363 "vendor_id" column allows NULL values. But for now, we'll leave that
1364 aside.)
1365 When viewed in terms of the participating tables, things look slightly
1367 different. Earlier, we established that several products can have the
1368 same vendor. So the inter-table relationship is actually this: many
1369 rows from the "products" table may refer to one row from the "vendors"
1370 table.
1371 Rose::DB::Object describes inter-table relationships from the
1373 perspective of a given table by using the cardinality of the "local"
1374 table ("products") followed by the cardinality of the "remote" table
1375 ("vendors"). The foreign key in the "products" table (and "Product"
1376 class) therefore represents a "many to one" relationship.
1377 If the relationship were different and each vendor was only allowed to
1379 have a single product, then the relationship would be "one to one."
1380 Given only the foreign key definition as it exists in the database,
1381 it's not possible to determine whether the relationship is "many to
1382 one" or "one to one." The default is "many to one" because that's the
1383 less restrictive choice.
1384 To override the default, a relationship type string can be included in
1386 the foreign key description.
1387 foreign_keys =>
1389 [
1390 vendor =>
1391 {
1392 class => 'Vendor',
1393 key_columns => { vendor_id => 'id' },
1394 relationship_type => 'one to one',
1395 },
1396 ],
1397 (The "relationship_type" parameter may be shortened to "rel_type", if
1399 desired.)
1400 Rose::DB::Object generalizes all inter-table relationships using a
1402 family of aptly named relationship objects. Each inherits from the
1403 Rose::DB::Object::Metadata::Relationship base class.
1404 Even foreign keys are included under the umbrella of this concept.
1406 When foreign key metadata is added to a Rose::DB::Object-derived class,
1407 a corresponding "many to one" or "one to one" relationship is actually
1408 added as well. This relationship is simply a proxy for the foreign
1409 key. It exists so that the set of relationship objects encompasses all
1410 relationships, even those that correspond to foreign keys in the
1411 database. This makes iterating over all relationships in a class a
1412 simple affair.
1413 foreach my $rel (Product->meta->relationships)
1415 {
1416 print $rel->name, ': ', $rel->type, "\n";
1417 }
1418 For the "Product" class, the output is:
1420 vendor: many to one
1422 Given the two possible cardinalities, "many" and "one", it's easy to
1424 come up with a list of all possible inter-table relationships. Here
1425 they are, listed with their corresponding relationship object classes.
1426 one to one - Rose::DB::Object::Metadata::Relationship::OneToOne
1428 one to many - Rose::DB::Object::Metadata::Relationship::OneToMany
1429 many to one - Rose::DB::Object::Metadata::Relationship::ManyToOne
1430 many to many - Rose::DB::Object::Metadata::Relationship::ManyToMany
1431 We've already seen that "one to one" and "many to one" relationships
1433 can be represented by foreign keys in the database, but that's not a
1434 requirement. It's perfectly possible to have either of those two kinds
1435 of relationships in a database that has no native support for foreign
1436 keys. (MySQL using the MyISAM storage engine is a common example.)
1437 If you find yourself using such a database, there's no reason to lie to
1439 your Perl classes by adding foreign key metadata. Instead, simply add
1440 a relationship.
1441 Here's an example of our "Product" class as it might exist on a
1443 database that does not support foreign keys. (The "Product" class is
1444 getting larger now, so previously established portions may be omitted
1445 from now on.)
1446 package Product;
1448 use base 'My::DB::Object';
1450 __PACKAGE__->meta->setup
1452 (
1453 table => 'products',
1454 columns => [... ],
1455 pk_columns => 'id',
1456 unique_key => 'name',
1457 relationships =>
1459 [
1460 vendor =>
1461 {
1462 type => 'many to one',
1463 class => 'Vendor',
1464 column_map => { vendor_id => 'id' },
1465 },
1466 ],
1467 );
1468 They syntax and semantics are similar to those described for foreign
1470 keys. The only slight differences are the names and types of
1471 parameters accepted by relationship objects.
1472 In the example above, a "many to one" relationship named "vendor" is
1474 set up. As demonstrated before, this definition can be reduced much
1475 further, allowing the convention manager to fill in the details. But
1476 unlike the case with the foreign key definition, where only the name
1477 was supplied, we must provide the relationship type as well.
1478 relationships => [ vendor => { type => 'many to one' } ],
1480 There's an even more convenient shorthand for that:
1482 relationships => [ vendor => 'many to one' ],
1484 (Again, this all depends on naming the tables, classes, and columns in
1486 accordance with the expectations of the convention manager.) The
1487 resulting vendor() and delete_vendor() methods behave exactly the same
1488 as the methods created on behalf of the foreign key definition.
1489 One-to-many relationships
1491 Now let's explore the other two relationship types. We'll start with
1493 "one to many" by adding region-specific pricing to our products.
1494 First, we'll need a "prices" table.
1495 CREATE TABLE prices
1497 (
1498 id SERIAL NOT NULL PRIMARY KEY,
1499 product_id INT NOT NULL REFERENCES products (id),
1500 region CHAR(2) NOT NULL DEFAULT 'US',
1501 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
1502 UNIQUE(product_id, region)
1504 );
1505 This table needs a corresponding Rose::DB::Object-derived class, of
1507 course.
1508 package Price;
1510 use base 'My::DB::Object';
1512 __PACKAGE__->meta->setup
1514 (
1515 table => 'prices',
1516 columns =>
1518 [
1519 id => { type => 'serial', not_null => 1 },
1520 product_id => { type => 'int', not_null => 1 },
1521 region => { type => 'char', length => 2, not_null => 1 },
1522 price =>
1523 {
1524 type => 'decimal',
1525 precision => 10,
1526 scale => 2,
1527 not_null => 1,
1528 default => 0.00
1529 },
1530 ],
1531 primary_key_columns => [ 'id' ],
1533 unique_key => [ 'product_id', 'region' ],
1535 foreign_keys =>
1537 [
1538 product =>
1539 {
1540 class => 'Product',
1541 key_columns => { product_id => 'id' },
1542 },
1543 ],
1544 );
1545 The "price" column can be removed from the "products" table.
1547 ALTER TABLE products DROP COLUMN price;
1549 Finally, the "Product" class needs to be modified to reference the
1551 "prices" table.
1552 package Product;
1554 use base 'My::DB::Object';
1556 use Price;
1558 use Vendor;
1559 __PACKAGE__->meta->setup
1561 (
1562 table => 'products',
1563 columns => [ ... ],
1564 pk_columns => 'id',
1565 unique_key => 'name',
1566 foreign_keys =>
1568 [
1569 vendor =>
1570 {
1571 class => 'Vendor',
1572 key_columns => { vendor_id => 'id' },
1573 },
1574 ],
1575 relationships =>
1577 [
1578 prices =>
1579 {
1580 type => 'one to many',
1581 class => 'Price',
1582 column_map => { id => 'product_id' },
1583 },
1584 ],
1585 );
1586 Note that both the column map for the "one to many" relationship and
1588 the key columns for the foreign key connect "local" columns to
1589 "foreign" columns.
1590 The "vendor_id" column in the local table ("products") is connected to
1592 the "id" column in the foreign table ("vendors"):
1593 vendor =>
1595 {
1596 key_columns => { vendor_id => 'id' },
1597 ...
1598 }
1599 The "id" column in the local table ("products") is connected to the
1601 "product_id" column in the foreign table ("prices"):
1602 prices =>
1604 {
1605 column_map => { id => 'product_id' },
1606 ...
1607 }
1608 This is all from the perspective of the class in which the definitions
1610 appear. Note that things are reversed in the "Price" class.
1611 package Price;
1613 ...
1614 __PACKAGE__->meta->setup
1615 (
1616 ...
1617 foreign_keys =>
1618 [
1619 product =>
1620 {
1621 class => 'Product',
1622 key_columns => { product_id => 'id' },
1623 },
1624 ],
1625 );
1626 Here, the "product_id" column in the local table ("prices") is
1628 connected to the "id" column in the foreign table ("products").
1629 The methods created by "... to many" relationships behave much like
1631 their "... to one" and foreign key counterparts. The main difference
1632 is that lists or references to arrays of the previously described
1633 argument formats are also acceptable, while name/value pairs outside of
1634 a hashref are not.
1635 Here's a list of argument types accepted by "many to one" methods like
1637 "prices".
1638 • A list or reference to an array of objects of the appropriate
1640 class.
1641 • A list or reference to an array of hash references containing
1643 name/value pairs used to construct such objects.
1644 • A list or reference to an array of primary key values (but only if
1646 the foreign table has a single-column primary key).
1647 Setting a new list of prices will delete all the old prices. As with
1649 foreign keys, any actual database modification happens when the parent
1650 object is saved. Here are some examples.
1651 $p = Product->new(name => 'Kite');
1653 $p->prices({ price => 1.23, region => 'US' },
1654 { price => 4.56, region => 'UK' });
1655 $p->save; # database is modified here
1657 # US: 1.23, UK: 4.56
1659 print join(', ', map { $_->region . ': ' . $_->price } $p->prices);
1660 New prices can be added without deleting and resetting the entire list:
1662 # Add two prices to the existing list
1664 $p->add_prices({ price => 7.89, region => 'DE' },
1665 { price => 1.11, region => 'JP' });
1666 $p->save; # database is modified here
1668 Passing a reference to an empty array will cause all the prices to be
1670 deleted:
1671 $p->prices([]); # delete all prices associated with this product
1673 $p->save; # database is modified here
1674 Cascading delete
1676 Deleting a product now becomes slightly more interesting. The naive
1678 approach fails.
1679 $p->delete; # Fatal error!
1681 # DBD::Pg::st execute failed: ERROR: update or delete on "products"
1683 # violates foreign key constraint "prices_product_id_fkey" on
1684 # "prices"
1685 # DETAIL: Key (id)=(12345) is still referenced from table "prices".
1686 Since rows in the "prices" table now link to rows in the "products"
1688 table, a product cannot be deleted until all of the prices that refer
1689 to it are also deleted. There are a few ways to deal with this.
1690 The best solution is to add a trigger to the "products" table itself in
1692 the database that makes sure to delete any associated prices before
1693 deleting a product. This change will allow the naive approach shown
1694 above to work correctly.
1695 A less robust solution is necessary if your database does not support
1697 triggers. One such solution is to manually delete the prices before
1698 deleting the product. This can be done in several ways. The prices
1699 can be deleted directly, like this.
1700 foreach my $price ($p->prices)
1702 {
1703 $price->delete; # Delete all associated prices
1704 }
1705 $p->delete; # Now it's safe to delete the product
1707 The list of prices for the product can also be set to an empty list,
1709 which will have the effect of deleting all associated prices when the
1710 product is saved.
1711 $p->prices([]);
1713 $p->save; # All associated prices deleted here
1714 $p->delete; # Now it's safe to delete the product
1715 Finally, the delete() method can actually automate this process, and do
1717 it all inside a transaction as well.
1718 $p->delete(cascade => 1); # Delete all associated rows too
1720 Again, the recommended approach is to use triggers inside the database
1722 itself. But if necessary, these other approaches will work too.
1723 Many-to-many relationships
1725 The final relationship type is the most complex. In a "many to many"
1727 relationship, a single row in table A may be related to multiple rows
1728 in table B, while a single row in table B may also be related to
1729 multiple rows in table A. (Confused? A concrete example will follow
1730 shortly.)
1731 This kind of relationship involves three tables instead of just two.
1733 The "local" and "foreign" tables, familiar from the other relationship
1734 types described above, still exist, but now there's a third table that
1735 connects rows from those two tables. This third table is called the
1736 "mapping table," and the Rose::DB::Object-derived class that fronts it
1737 is called the "map class."
1738 Let's add such a relationship to our growing family of classes.
1740 Imagine that each product may come in several colors. Right away,
1741 both the "one to one" and "many to one" relationship types are
1742 eliminated since they can only provide a single color for any given
1743 product.
1744 But wait, isn't a "one to many" relationship suitable? After all, one
1746 product may have many colors. Unfortunately, such a relationship is
1747 wasteful in this case. Let's see why. Imagine a "colors" table like
1748 this.
1749 CREATE TABLE colors
1751 (
1752 id SERIAL NOT NULL PRIMARY KEY,
1753 name VARCHAR(255) NOT NULL,
1754 product_id INT NOT NULL REFERENCES products (id)
1755 );
1756 Here's a simple "Color" class to front it.
1758 package Color;
1760 use base 'My::DB::Object';
1762 __PACKAGE__->meta->setup
1764 (
1765 table => 'colors',
1766 columns =>
1767 [
1768 id => { type => 'serial', primary_key => 1, not_null => 1 },
1769 name => { type => 'varchar', length => 255, not_null => 1 },
1770 product_id => { type => 'int', not_null => 1 },
1771 ],
1772 foreign_keys =>
1774 [
1775 product =>
1776 {
1777 class => 'Product',
1778 key_columns => { product_id => 'id' },
1779 },
1780 ],
1781 );
1782 Finally, let's add the "one to many" relationship to the "Product"
1784 class.
1785 package Product;
1787 use base 'My::DB::Object';
1789 __PACKAGE__->meta->setup
1791 (
1792 ...
1793 relationships =>
1794 [
1795 colors =>
1796 {
1797 type => 'one to many',
1798 class => 'Color',
1799 column_map => { id => 'product_id' },
1800 },
1801 ...
1802 ],
1803 );
1804 It works as expected.
1806 $p1 = Product->new(id => 10,
1808 name => 'Sled',
1809 colors =>
1810 [
1811 { name => 'red' },
1812 { name => 'green' },
1813 ]);
1814 $p1->save;
1815 $p2 = Product->new(id => 20,
1817 name => 'Kite',
1818 colors =>
1819 [
1820 { name => 'blue' },
1821 { name => 'green' },
1822 { name => 'red' },
1823 ]);
1824 $p2->save;
1825 But now look at the contents of the "colors" table in the database.
1827 mydb=# select * from colors;
1829 id | name | product_id
1831 ----+-------+------------
1832 1 | red | 10
1833 2 | green | 10
1834 3 | blue | 20
1835 4 | green | 20
1836 5 | red | 20
1837 Notice that the colors "green" and "red" appear twice. Now imagine
1839 that there are 50,000 products. What are the odds that there will be
1840 more than a few colors in common among them?
1841 This is a poor database design. To fix it, we must recognize that
1843 colors will be shared among products, since the set of possible colors
1844 is relatively small compared to the set of possible products. One
1845 product may have many colors, but one color may also belong to many
1846 products. And there you have it: a textbook "many to many"
1847 relationship.
1848 Let's redesign this relationship in "many to many" form, starting with
1850 a new version of the "colors" table.
1851 CREATE TABLE colors
1853 (
1854 id SERIAL NOT NULL PRIMARY KEY,
1855 name VARCHAR(255) NOT NULL,
1856 UNIQUE(name)
1858 );
1859 Since each color will now appear only once in this table, we can make
1861 the "name" column a unique key.
1862 Here's the new "Color" class.
1864 package Color;
1866 use base 'My::DB::Object';
1868 __PACKAGE__->meta->setup
1870 (
1871 table => 'colors',
1872 columns =>
1873 [
1874 id => { type => 'serial', primary_key => 1, not_null => 1 },
1875 name => { type => 'varchar', length => 255, not_null => 1 },
1876 ],
1877 unique_key => 'name',
1879 );
1880 Since the "colors" table no longer has a foreign key that points to the
1882 "products" table, we need some way to connect the two tables: a mapping
1883 table.
1884 CREATE TABLE product_color_map
1886 (
1887 product_id INT NOT NULL REFERENCES products (id),
1888 color_id INT NOT NULL REFERENCES colors (id),
1889 PRIMARY KEY(product_id, color_id)
1891 );
1892 Note that there's no reason for a separate primary key column in this
1894 table. We'll use a two-column primary key instead.
1895 Here's the map class.
1897 package ProductColorMap;
1899 use base 'My::DB::Object';
1901 __PACKAGE__->meta->setup
1903 (
1904 table => 'product_color_map',
1905 columns =>
1906 [
1907 product_id => { type => 'int', not_null => 1 },
1908 color_id => { type => 'int', not_null => 1 },
1909 ],
1910 primary_key_columns => [ 'product_id', 'color_id' ],
1912 foreign_keys =>
1914 [
1915 product =>
1916 {
1917 class => 'Product',
1918 key_columns => { product_id => 'id' },
1919 },
1920 color =>
1922 {
1923 class => 'Color',
1924 key_columns => { color_id => 'id' },
1925 },
1926 ],
1927 );
1928 It's important that the map class have either a foreign key or a "many
1930 to one" relationship pointing to each of the tables that it maps
1931 between. In this case, there are two foreign keys.
1932 Finally, here's the "many to many" relationship definition in the
1934 "Product" class.
1935 package Product;
1937 ...
1938 __PACKAGE__->meta->setup
1939 (
1940 ...
1941 relationships =>
1942 [
1943 colors =>
1944 {
1945 type => 'many to many',
1946 map_class => 'ProductColorMap'
1947 map_from => 'product',
1948 map_to => 'color',
1949 },
1950 ...
1951 ],
1952 );
1953 Note that only the map class needs to be "use"d in the "Product" class.
1955 The relationship definition specifies the name of the map class, and
1956 (optionally) the names of the foreign keys or "many to one"
1957 relationships in the map class that connect the two tables.
1958 In most cases, these two parameters ("map_from" and "map_to") are
1960 unnecessary. Rose::DB::Object will figure out what to do given only
1961 the map class, so long as there's no ambiguity in the mapping table.
1962 In this case, there is no ambiguity, so the relationship definition can
1964 be shortened to this.
1965 use Product;
1967 ...
1968 __PACKAGE__->meta->setup
1969 (
1970 relationships =>
1971 [
1972 colors =>
1973 {
1974 type => 'many to many',
1975 map_class => 'ProductColorMap'
1976 },
1977 ],
1978 ...
1979 );
1980 In fact, since the map table is named according to the default
1982 conventions, it can be shortened even further.
1983 use Product;
1985 ...
1986 __PACKAGE__->meta->setup
1987 (
1988 relationships =>
1989 [
1990 colors => { type => 'many to many' },
1991 ...
1992 ],
1993 ...
1994 );
1995 And further still:
1997 use Product;
1999 ...
2000 __PACKAGE__->meta->setup
2001 (
2002 relationships =>
2003 [
2004 colors => 'many to many',
2005 ...
2006 ],
2007 ...
2008 );
2009 (Classes can be shortened even more absurdly when auto-initialization
2011 is combined with the convention manager. See the convention manager
2012 documentation for an example.)
2013 Now let's revisit the example code.
2015 $p1 = Product->new(id => 10,
2017 name => 'Sled',
2018 colors =>
2019 [
2020 { name => 'red' },
2021 { name => 'green' }
2022 ]);
2023 $p1->save;
2024 $p2 = Product->new(id => 20,
2026 name => 'Kite',
2027 colors =>
2028 [
2029 { name => 'blue' },
2030 { name => 'green' },
2031 { name => 'red' },
2032 ]);
2033 $p2->save;
2034 The code works as expected, but the database now looks much nicer.
2036 mydb=# select * from colors;
2038 id | name
2040 ----+-------
2041 1 | red
2042 2 | green
2043 3 | blue
2044 mydb=# select * from product_color_map;
2047 product_id | color_id
2049 ------------+----------
2050 10 | 1
2051 10 | 2
2052 20 | 3
2053 20 | 2
2054 20 | 1
2055 Each color appears only once, and the mapping table handles all the
2057 connections between the "colors" and "products" tables.
2058 The "many to many" "colors" method works much like the "one to many"
2060 "prices" method described earlier. The valid argument formats are the
2061 same.
2062 • A list or reference to an array of objects of the appropriate
2064 class.
2065 • A list or reference to an array of hash references containing
2067 name/value pairs used to construct such objects.
2068 • A list or reference to an array of primary key values (but only if
2070 the foreign table has a single-column primary key).
2071 The database modification behavior is also the same, with changes
2073 happening when the "parent" object is saved.
2074 $p = Product->new(id => 123)->load;
2076 $p->colors({ name => 'green' },
2078 { name => 'blue' });
2079 $p->save; # database is modified here
2081 Setting the list of colors replaces the old list, but in the case of a
2083 "many to many" relationship, only the map records are deleted.
2084 $p = Product->new(id => 123)->load;
2086 $p->colors({ name => 'pink' },
2088 { name => 'orange' });
2089 # Delete old rows in the mapping table and create new ones
2091 $p->save;
2092 New colors can be added without deleting and resetting the entire list:
2094 # Add two colors to the existing list
2096 $p->add_colors({ name => 'gray' },
2097 { name => 'red' });
2098 $p->save; # database is modified here
2100 Passing a reference to an empty array will remove all colors associated
2102 with a particular product by deleting all the mapping table entries.
2103 $p->colors([]);
2105 $p->save; # all mapping table entries for this product deleted here
2106 Finally, the same caveats described earlier about deleting products
2108 that have associated prices apply to colors as well. Again, I
2109 recommend using a trigger in the database to handle this, but
2110 Rose::DB::Object's cascading delete feature will work in a pinch.
2111 # Delete all associated rows in the prices table, plus any
2113 # rows in the product_color_map table, before deleting the
2114 # row in the products table.
2115 $p->delete(cascade => 1);
2116 Relationship code summary
2118 To summarize this exploration of inter-table relationships, here's a
2120 terse summary of the current state of our Perl classes, and the
2121 associated database tables.
2122 For the sake of brevity, I've chosen to use the shorter versions of the
2124 foreign key and relationship definitions in the Perl classes shown
2125 below. Just remember that this only works when your tables, columns,
2126 and classes are named according to the expected conventions.
2127 First, the database schema.
2129 CREATE TABLE vendors
2131 (
2132 id SERIAL NOT NULL PRIMARY KEY,
2133 name VARCHAR(255) NOT NULL,
2134 UNIQUE(name)
2136 );
2137 CREATE TABLE products
2139 (
2140 id SERIAL NOT NULL PRIMARY KEY,
2141 name VARCHAR(255) NOT NULL,
2142 vendor_id INT REFERENCES vendors (id),
2144 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
2146 CHECK(status IN ('inactive', 'active', 'defunct')),
2147 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
2149 release_date TIMESTAMP,
2150 UNIQUE(name)
2152 );
2153 CREATE TABLE prices
2155 (
2156 id SERIAL NOT NULL PRIMARY KEY,
2157 product_id INT NOT NULL REFERENCES products (id),
2158 region CHAR(2) NOT NULL DEFAULT 'US',
2159 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
2160 UNIQUE(product_id, region)
2162 );
2163 CREATE TABLE colors
2165 (
2166 id SERIAL NOT NULL PRIMARY KEY,
2167 name VARCHAR(255) NOT NULL,
2168 UNIQUE(name)
2170 );
2171 CREATE TABLE product_color_map
2173 (
2174 product_id INT NOT NULL REFERENCES products (id),
2175 color_id INT NOT NULL REFERENCES colors (id),
2176 PRIMARY KEY(product_id, color_id)
2178 );
2179 Now the Perl classes. Remember that these must each be in their own
2181 ".pm" files, despite appearing in one contiguous code snippet below.
2182 package Vendor;
2184 use base 'My::DB::Object';
2186 __PACKAGE__->meta->setup
2188 (
2189 table => 'vendors',
2190 columns =>
2191 [
2192 id => { type => 'serial', primary_key => 1, not_null => 1 },
2193 name => { type => 'varchar', length => 255, not_null => 1 },
2194 ],
2195 unique_key => 'name',
2197 );
2198 1;
2200 package Product;
2203 use base 'My::DB::Object';
2205 __PACKAGE__->meta->setup
2207 (
2208 table => 'products',
2209 columns =>
2210 [
2211 id => { type => 'integer', not_null => 1 },
2212 name => { type => 'varchar', length => 255, not_null => 1 },
2213 vendor_id => { type => 'int' },
2215 status => { type => 'varchar', default => 'inactive',
2216 length => 128, not_null => 1 },
2217 date_created => { type => 'timestamp', not_null => 1,
2218 default => 'now()' },
2219 release_date => { type => 'timestamp' },
2220 ]
2221 primary_key_columns => 'id',
2223 unique_key => 'name',
2225 allow_inline_column_values => 1,
2227 relationships =>
2229 [
2230 prices => 'one to many',
2231 colors => 'many to many',
2232 ]
2233 );
2234 1;
2236 package Price;
2239 use Product;
2241 use base 'My::DB::Object';
2243 __PACKAGE__->meta->setup
2245 (
2246 table => 'prices',
2247 columns =>
2249 [
2250 id => { type => 'serial', primary_key => 1, not_null => 1 },
2251 product_id => { type => 'int', not_null => 1 },
2252 region => { type => 'char', length => 2, not_null => 1 },
2253 price =>
2254 {
2255 type => 'decimal',
2256 precision => 10,
2257 scale => 2,
2258 not_null => 1,
2259 default => 0.00
2260 },
2261 ],
2262 unique_key => [ 'product_id', 'region' ],
2264 foreign_key => [ 'product' ],
2266 );
2267 1;
2269 package Color;
2272 use base 'My::DB::Object';
2274 __PACKAGE__->meta->setup
2276 (
2277 table => 'colors',
2278 columns =>
2279 [
2280 id => { type => 'serial', primary_key => 1, not_null => 1 },
2281 name => { type => 'varchar', length => 255, not_null => 1 },
2282 ],
2283 unique_key => 'name',
2284 );
2285 1;
2287 package ProductColorMap;
2290 use base 'My::DB::Object';
2292 __PACKAGE__->meta->setup
2294 (
2295 table => 'product_color_map',
2296 columns =>
2297 [
2298 product_id => { type => 'int', not_null => 1 },
2299 color_id => { type => 'int', not_null => 1 },
2300 ],
2301 pk_columns => [ 'product_id', 'color_id' ],
2302 foreign_keys => [ 'product', 'color' ],
2303 );
2304 1;
2306 The loader
2308 If the code above still looks like too much work to you, try letting
2309 Rose::DB::Object::Loader do it all for you. Given the database schema
2310 shown above, the suite of associated Perl classes could have been
2311 created automatically with a single method call.
2312 $loader =
2314 Rose::DB::Object::Loader->new(db => Rose::DB->new,
2315 class_prefix => 'My::');
2316 $loader->make_classes;
2318 If you want to see what the loader did for you, catch the return value
2320 of the make_classes method (which will be a list of class names) and
2321 then ask each class to print its perl equivalent.
2322 @classes = $loader->make_classes;
2324 foreach my $class (@classes)
2326 {
2327 if($class->isa('Rose::DB::Object'))
2328 {
2329 print $class->meta->perl_class_definition(braces => 'bsd',
2330 indent => 2), "\n";
2331 }
2332 else # Rose::DB::Object::Manager subclasses
2333 {
2334 print $class->perl_class_definition, "\n";
2335 }
2336 }
2337 You can also ask the loader to make actual Perl modules (that is, a set
2339 of actual *.pm files in the file system) by calling the aptly named
2340 make_modules method.
2341 The code created by the loader is shown below. Compare it to the
2343 manually created Perl code shown above and you'll see that it's nearly
2344 identical. Again, careful table name choices really help here. Do
2345 what the convention manager expects (or write your own convention
2346 manager subclass that does what you expect) and automation like this
2347 can work very well.
2348 package My::Color;
2350 use strict;
2352 use base qw(My::DB::Object::Base1);
2354 __PACKAGE__->meta->setup
2356 (
2357 table => 'colors',
2358 columns =>
2360 [
2361 id => { type => 'integer', not_null => 1 },
2362 name => { type => 'varchar', length => 255, not_null => 1 },
2363 ],
2364 primary_key_columns => [ 'id' ],
2366 unique_keys => [ 'name' ],
2368 relationships =>
2370 [
2371 products =>
2372 {
2373 column_map => { color_id => 'id' },
2374 foreign_class => 'My::Product',
2375 map_class => 'My::ProductColorMap',
2376 map_from => 'color',
2377 map_to => 'product',
2378 type => 'many to many',
2379 },
2380 ],
2381 );
2382 1;
2384 package My::Color::Manager;
2386 use base qw(Rose::DB::Object::Manager);
2388 use My::Color;
2390 sub object_class { 'My::Color' }
2392 __PACKAGE__->make_manager_methods('colors');
2394 1;
2396 package My::Price;
2398 use strict;
2400 use base qw(My::DB::Object::Base1);
2402 __PACKAGE__->meta->setup
2404 (
2405 table => 'prices',
2406 columns =>
2408 [
2409 id => { type => 'integer', not_null => 1 },
2410 product_id => { type => 'integer', not_null => 1 },
2411 region => { type => 'character', default => 'US', length => 2,
2412 not_null => 1 },
2413 price => { type => 'numeric', default => '0.00', not_null => 1,
2414 precision => 10, scale => 2 },
2415 ],
2416 primary_key_columns => [ 'id' ],
2418 unique_key => [ 'product_id', 'region' ],
2420 foreign_keys =>
2422 [
2423 product =>
2424 {
2425 class => 'My::Product',
2426 key_columns =>
2427 {
2428 product_id => 'id',
2429 },
2430 },
2431 ],
2432 );
2433 1;
2435 package My::Price::Manager;
2437 use base qw(Rose::DB::Object::Manager);
2439 use My::Price;
2441 sub object_class { 'My::Price' }
2443 __PACKAGE__->make_manager_methods('prices');
2445 1;
2447 package My::ProductColorMap;
2449 use strict;
2451 use base qw(My::DB::Object::Base1);
2453 __PACKAGE__->meta->setup
2455 (
2456 table => 'product_color_map',
2457 columns =>
2459 [
2460 product_id => { type => 'integer', not_null => 1 },
2461 color_id => { type => 'integer', not_null => 1 },
2462 ],
2463 primary_key_columns => [ 'product_id', 'color_id' ],
2465 foreign_keys =>
2467 [
2468 color =>
2469 {
2470 class => 'My::Color',
2471 key_columns =>
2472 {
2473 color_id => 'id',
2474 },
2475 },
2476 product =>
2478 {
2479 class => 'My::Product',
2480 key_columns =>
2481 {
2482 product_id => 'id',
2483 },
2484 },
2485 ],
2486 );
2487 1;
2489 package My::ProductColorMap::Manager;
2491 use base qw(Rose::DB::Object::Manager);
2493 use My::ProductColorMap;
2495 sub object_class { 'My::ProductColorMap' }
2497 __PACKAGE__->make_manager_methods('product_color_map');
2499 1;
2501 package My::ProductColor;
2503 use strict;
2505 use base qw(My::DB::Object::Base1);
2507 __PACKAGE__->meta->setup
2509 (
2510 table => 'product_colors',
2511 columns =>
2513 [
2514 id => { type => 'integer', not_null => 1 },
2515 product_id => { type => 'integer', not_null => 1 },
2516 color_code => { type => 'character', length => 3, not_null => 1 },
2517 ],
2518 primary_key_columns => [ 'id' ],
2520 );
2521 1;
2523 package My::ProductColor::Manager;
2525 use base qw(Rose::DB::Object::Manager);
2527 use My::ProductColor;
2529 sub object_class { 'My::ProductColor' }
2531 __PACKAGE__->make_manager_methods('product_colors');
2533 1;
2535 package My::Product;
2537 use strict;
2539 use base qw(My::DB::Object::Base1);
2541 __PACKAGE__->meta->setup
2543 (
2544 table => 'products',
2545 columns =>
2547 [
2548 id => { type => 'integer', not_null => 1 },
2549 name => { type => 'varchar', length => 255, not_null => 1 },
2550 price => { type => 'numeric', default => '0.00', not_null => 1,
2551 precision => 10, scale => 2 },
2552 vendor_id => { type => 'integer' },
2553 status => { type => 'varchar', default => 'inactive',
2554 length => 128, not_null => 1 },
2555 date_created => { type => 'timestamp', default => 'now()',
2556 not_null => 1 },
2557 release_date => { type => 'timestamp' },
2558 ],
2559 primary_key_columns => [ 'id' ],
2561 unique_keys => [ 'name' ],
2563 allow_inline_column_values => 1,
2565 foreign_keys =>
2567 [
2568 vendor =>
2569 {
2570 class => 'My::Vendor',
2571 key_columns =>
2572 {
2573 vendor_id => 'id',
2574 },
2575 },
2576 ],
2577 relationships =>
2579 [
2580 colors =>
2581 {
2582 column_map => { product_id => 'id' },
2583 foreign_class => 'My::Color',
2584 map_class => 'My::ProductColorMap',
2585 map_from => 'product',
2586 map_to => 'color',
2587 type => 'many to many',
2588 },
2589 prices =>
2591 {
2592 class => 'My::Price',
2593 key_columns => { id => 'product_id' },
2594 type => 'one to many',
2595 },
2596 ],
2597 );
2598 1;
2600 package My::Product::Manager;
2602 use base qw(Rose::DB::Object::Manager);
2604 use My::Product;
2606 sub object_class { 'My::Product' }
2608 __PACKAGE__->make_manager_methods('products');
2610 1;
2612 package My::Vendor;
2614 use strict;
2616 use base qw(My::DB::Object::Base1);
2618 __PACKAGE__->meta->setup
2620 (
2621 table => 'vendors',
2622 columns =>
2624 [
2625 id => { type => 'integer', not_null => 1 },
2626 name => { type => 'varchar', length => 255, not_null => 1 },
2627 ],
2628 primary_key_columns => [ 'id' ],
2630 unique_keys => [ 'name' ],
2632 relationships =>
2634 [
2635 products =>
2636 {
2637 class => 'My::Product',
2638 key_columns => { id => 'vendor_id' },
2639 type => 'one to many',
2640 },
2641 ],
2642 );
2643 1;
2645 package My::Vendor::Manager;
2647 use base qw(Rose::DB::Object::Manager);
2649 use My::Vendor;
2651 sub object_class { 'My::Vendor' }
2653 __PACKAGE__->make_manager_methods('vendors');
2655 1;
2657 Auto-joins and other Manager features
2659 The "Product::Manager" class we created earlier is deceptively simple.
2660 Setting it up can actually be reduced to a one-liner, but it provides a
2661 rich set of features.
2662 The basics demonstrated earlier cover most kinds of single-table SELECT
2664 statements. But as the "Product" class has become more complex,
2665 linking to other objects via foreign keys and other relationships,
2666 selecting rows from just the "products" table has become a lot less
2667 appealing. What good is it to retrieve hundreds of products in a
2668 single query when you then have to execute hundreds of individual
2669 queries to get the prices of those products?
2670 This is what SQL JOINs were made for: selecting related rows from
2672 multiple tables simultaneously. Rose::DB::Object::Manager supports a
2673 two kinds of joins. The interface to this functionality is presented
2674 in terms of objects via the "require_objects" and "with_objects"
2675 parameters to the get_objects() method.
2676 Both parameters expect a list of foreign key or relationship names.
2678 The "require_objects" parameters will use an "inner join" to fetch
2679 related objects, while the "with_objects" parameter will perform an
2680 "outer join."
2681 If you're unfamiliar with these terms, it's probably a good idea to
2683 learn about them from a good SQL book or web tutorial. But even if
2684 you've never written an SQL JOIN by hand, there's not much you need to
2685 understand in order to use your manager class effectively.
2686 The rule of thumb is simple. When you want each and every object
2688 returned by your query to have a particular related object, then use
2689 the "require_objects" parameter. But if you do not want to exclude
2690 objects even if they do not have a particular related object attached
2691 to them yet, then use the "with_objects" parameter.
2692 Sometimes, this decision is already made for you by the table
2694 structure. For example, let's modify the "products" table in order to
2695 require that every single product has a vendor. To do so, we'll change
2696 the "vendor_id" column definition from this:
2697 vendor_id INT REFERENCES vendors (id)
2699 to this:
2701 vendor_id INT NOT NULL REFERENCES vendors (id)
2703 Now it's impossible for a product to have a NULL "vendor_id". And
2705 since our database enforces referential integrity, it's also impossible
2706 for the "vendor_id" column to have a value that does not refer to the
2707 "id" of an existing row in the "vendors" table.
2708 While the "with_objects" parameter could technically be used to fetch
2710 "Product"s with their associated "Vendor" objects, it would be
2711 wasteful. (Outer joins are often less efficient than inner joins.)
2712 The table structure basically dictates that the "require_objects"
2713 parameter be used when fetching "Product"s with their "Vendor"s.
2714 Here's how such a query could actually look.
2716 $products =
2718 Product::Manager->get_products(
2719 query =>
2720 [
2721 name => { like => 'Kite%' },
2722 id => { gt => 15 },
2723 ]
2724 require_objects => [ 'vendor' ],
2725 sort_by => 'name');
2726 Recall that the name of the foreign key that connects a product to its
2728 vendor is "vendor". Thus, the value of the "require_objects" parameter
2729 is a reference to an array containing this name.
2730 Getting information about each product's vendor now no longer requires
2732 additional database queries.
2733 foreach my $product (@$products)
2735 {
2736 # This does not hit the database at all
2737 print $product->vendor->name, "\n";
2738 }
2739 For the SQL-inclined, the actual query run looks something like this.
2741 SELECT
2743 t1.date_created,
2744 t1.id,
2745 t1.name,
2746 t1.release_date,
2747 t1.status,
2748 t1.vendor_id,
2749 t2.id,
2750 t2.name
2751 FROM
2752 products t1,
2753 vendors t2
2754 WHERE
2755 t1.id >= 16 AND
2756 t1.name LIKE 'Kite%' AND
2757 t1.vendor_id = t2.id
2758 ORDER BY t1.name
2759 As you can see, the query includes "tN" aliases for each table. This
2761 is important because columns in separate tables often have identical
2762 names. For example, both the "products" and the "vendors" tables have
2763 columns named "id" and "name".
2764 In the query, you'll notice that the "name => { like => 'Kite%' }"
2766 argument ended up filtering on the product name rather than the vendor
2767 name. This is intentional. Any unqualified column name that is
2768 ambiguous is considered to belong to the "primary" table ("products",
2769 in this case).
2770 The "tN" numbering is deterministic. The primary table is always "t1",
2772 and secondary tables are assigned ascending numbers starting from
2773 there. You can find a full explanation of the numbering rules in the
2774 Rose::DB::Object::Manager documentation.
2775 In the example above, if we wanted to filter and sort on the vendor
2777 name instead, we could do this.
2778 $products =
2780 Product::Manager->get_products(
2781 query =>
2782 [
2783 't2.name' => { like => 'Acm%' },
2784 id => { gt => 15 },
2785 ]
2786 require_objects => [ 'vendor' ],
2787 sort_by => 't2.name');
2788 But that's not the only option. There are several ways to disambiguate
2790 a query clause. The column name can also be qualified by prefixing it
2791 with a relationship name.
2792 $products =
2794 Product::Manager->get_products(
2795 query =>
2796 [
2797 'vendor.name' => { like => 'Acm%' },
2798 id => { gt => 15 },
2799 ]
2800 require_objects => [ 'vendor' ],
2801 sort_by => 'vendor.name');
2802 The actual table name itself can also be used (although I do not
2804 recommend this practice since you will have to change all such usage
2805 instances if you ever rename the table).
2806 $products =
2808 Product::Manager->get_products(
2809 query =>
2810 [
2811 'vendors.name' => { like => 'Acm%' },
2812 id => { gt => 15 },
2813 ]
2814 require_objects => [ 'vendor' ],
2815 sort_by => 'vendors.name');
2816 Now let's see an example of the "with_objects" parameter in action.
2818 Each "Product" has zero or more "Price"s. Let's fetch products with
2819 all their associated prices. And remember that some of these products
2820 may have no prices at all.
2821 $products =
2823 Product::Manager->get_products(
2824 query =>
2825 [
2826 name => { like => 'Kite%' },
2827 id => { gt => 15 },
2828 ],
2829 with_objects => [ 'prices' ],
2830 sort_by => 'name');
2831 Again, since the name of the "one to many" relationship that connects a
2833 product to its prices is "prices", this is the value use in the
2834 "with_objects" parameter. The SQL looks something like this:
2835 SELECT
2837 t1.date_created,
2838 t1.id,
2839 t1.name,
2840 t1.release_date,
2841 t1.status,
2842 t1.vendor_id,
2843 t2.id,
2844 t2.price,
2845 t2.product_id,
2846 t2.region
2847 FROM
2848 products t1
2849 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id)
2850 WHERE
2851 t1.id > 15 AND
2852 t1.name LIKE 'Kite%'
2853 ORDER BY t1.name
2854 Fetching products with both their vendors and prices (if any) is
2856 straightforward. Just use the "require_objects" parameter for the
2857 vendors and the "with_objects" parameter for the prices.
2858 $products =
2860 Product::Manager->get_products(
2861 query =>
2862 [
2863 name => { like => 'Kite%' },
2864 id => { gt => 15 },
2865 ],
2866 require_objects => [ 'vendor' ],
2867 with_objects => [ 'prices' ],
2868 sort_by => 'name');
2869 The resulting SQL is what you'd expect.
2871 SELECT
2873 t1.date_created,
2874 t1.id,
2875 t1.name,
2876 t1.release_date,
2877 t1.status,
2878 t1.vendor_id,
2879 t2.id,
2880 t2.price,
2881 t2.product_id,
2882 t2.region,
2883 t3.id,
2884 t3.name
2885 FROM
2886 products t1
2887 JOIN vendors t3 ON (t1.vendor_id = t3.id)
2888 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id)
2889 WHERE
2890 t1.id > 15 AND
2891 t1.name LIKE 'Kite%'
2892 ORDER BY t1.name
2893 Each "Product" also has zero or more "Color"s which are related to it
2895 through a mapping table (fronted by the "ProductColorMap" class, but we
2896 don't need to know that). The "with_objects" parameter can handle that
2897 as well.
2898 $products =
2900 Product::Manager->get_products(
2901 query =>
2902 [
2903 name => { like => 'Kite%' },
2904 id => { gt => 15 },
2905 ],
2906 with_objects => [ 'colors' ],
2907 sort_by => 'name');
2908 The resulting SQL is a bit more complex.
2910 SELECT
2912 t1.date_created,
2913 t1.id,
2914 t1.name,
2915 t1.release_date,
2916 t1.status,
2917 t1.vendor_id,
2918 t3.id,
2919 t3.name
2920 FROM
2921 products t1
2922 LEFT OUTER JOIN product_color_map t2 ON(t2.product_id = t1.id)
2923 LEFT OUTER JOIN colors t3 ON(t2.color_id = t3.id)
2924 WHERE
2925 t1.id > 15 AND
2926 t1.name LIKE 'Kite%'
2927 Again, combinations are straightforward. Let's fetch products with
2929 their vendors and colors.
2930 $products =
2932 Product::Manager->get_products(
2933 query =>
2934 [
2935 name => { like => 'Kite%' },
2936 id => { gt => 15 },
2937 ],
2938 require_objects => [ 'vendor' ],
2939 with_objects => [ 'colors' ],
2940 sort_by => 'name');
2941 Now the SQL is starting to get a bit hairy.
2943 SELECT
2945 t1.id,
2946 t1.name,
2947 t1.vendor_id,
2948 t3.code,
2949 t3.name,
2950 t4.id,
2951 t4.name,
2952 t4.region_id
2953 FROM
2954 products t1
2955 JOIN vendors t4 ON (t1.vendor_id = t4.id)
2956 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
2957 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
2958 WHERE
2959 t1.id > 15 AND
2960 t1.name LIKE 'Kite%'
2961 Anyone who knows SQL well will recognize that there is a danger lurking
2963 when combining JOINs. Multiple joins that each fetch multiple rows can
2964 result in a geometric explosion of rows returned by the database. For
2965 example, the number of rows returned when fetching products with their
2966 associated prices and colors would be:
2967 <number of matching products> x
2969 <number of prices for each product> x
2970 <number of colors for each product>
2971 That number can get very large, very fast if products have many prices,
2973 colors, or both. (The last two terms in the multiplication maybe
2974 switched, depending on the order of the actual JOIN clauses, but the
2975 results are similar.) And the problem only gets worse as the number of
2976 objects related by "... to many" relationships increases.
2977 That said, Rose::DB::Object::Manager does allow multiple objects
2979 related by "... to many" relationships to be fetched simultaneously.
2980 But it requires the developer to supply the "multi_many_ok" parameter
2981 with a true value as a form of confirmation. "Yes, I know the risks,
2982 but I want to do it anyway."
2983 As an example, let's try fetching products with their associated
2985 prices, colors, and vendors. To do so, we'll have to include the
2986 "multi_many_ok" parameter.
2987 $products =
2989 Product::Manager->get_products(
2990 query =>
2991 [
2992 name => { like => 'Kite%' },
2993 id => { gt => 15 },
2994 ],
2995 require_objects => [ 'vendor' ],
2996 with_objects => [ 'colors', 'prices' ],
2997 multi_many_ok => 1,
2998 sort_by => 'name');
2999 Here's the SQL.
3001 SELECT
3003 t1.id,
3004 t1.name,
3005 t1.vendor_id,
3006 t3.code,
3007 t3.name,
3008 t4.price_id,
3009 t4.product_id,
3010 t4.region,
3011 t4.price,
3012 t5.id,
3013 t5.name,
3014 t5.region_id
3015 FROM
3016 products t1
3017 JOIN vendors t5 ON (t1.vendor_id = t5.id)
3018 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
3019 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
3020 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id)
3021 WHERE
3022 t1.id > 15 AND
3023 t1.name LIKE 'Kite%'
3024 ORDER BY t1.name
3025 It's questionable whether this five-way join will be faster than doing
3027 a four- or three-way join and then fetching the other information after
3028 the fact, with separate queries. It all depends on the number of rows
3029 expected to match. Only you know your data. You must choose the most
3030 efficient query that suits your needs.
3031 Moving beyond even the example above, it's possible to chain foreign
3033 key or relationship names to an arbitrary depth. For example, imagine
3034 that each "Vendor" has a "Region" related to it by a foreign key named
3035 "region". The following call will get region information for each
3036 product's vendor, filtering on the region name.
3037 $products =
3039 Product::Manager->get_products(
3040 query =>
3041 [
3042 'vendor.region.name' => 'UK',
3043 'name' => { like => 'Kite%' },
3044 'id' => { gt => 15 },
3045 ],
3046 require_objects => [ 'vendor.region' ],
3047 with_objects => [ 'colors', 'prices' ],
3048 multi_many_ok => 1,
3049 sort_by => 'name');
3050 The SQL would now look something like this.
3052 SELECT
3054 t1.id,
3055 t1.name,
3056 t1.vendor_id,
3057 t3.code,
3058 t3.name,
3059 t4.price_id,
3060 t4.product_id,
3061 t4.region,
3062 t4.price,
3063 t5.id,
3064 t5.name,
3065 t5.region_id,
3066 t6.id,
3067 t6.name
3068 FROM
3069 products t1
3070 JOIN (vendors t5 JOIN regions t6 ON (t5.region_id = t6.id))
3071 ON (t1.vendor_id = t5.id)
3072 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
3073 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
3074 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id)
3075 WHERE
3076 t1.id > 15 AND
3077 t1.name LIKE 'Kite%' AND
3078 t6.name = 'UK'
3079 ORDER BY t1.name
3080 The same caveat about performance and the potential explosion of
3082 redundant data when JOINing across multiple "... to many" relationships
3083 also applies to the "chained" selectors demonstrated above--even more
3084 so, in fact, as the depth of the chain increases. That said, it's
3085 usually safe to go a few levels deep into "... to one" relationships
3086 when using the "require_objects" parameter.
3087 Finally, it's also possible to load a single product with all of its
3089 associated foreign objects. The load() method accepts a "with"
3090 parameter that takes a list of foreign key and relationship names.
3091 $product = Product->new(id => 1234);
3093 $product->load(with => [ 'vendor', 'colors', 'prices' ]);
3094 The same "multi many" caveats apply, but the "multi_many_ok" parameter
3096 is not required in this case. The assumption is that a single object
3097 won't have too many related objects. But again, only you know your
3098 data, so be careful.
3099 Wrap-up
3101 I hope you've learned something from this tutorial. Although it is by
3102 no means a complete tour of all of the features of Rose::DB::Object, it
3103 does hit most of the highlights. This tutorial will likely expand in
3104 the future, and a separate document describing the various ways that
3105 Rose::DB::Object can be extended is also planned. For now, there is a
3106 brief overview that was pulled from the Rose::DB::Object mailing list
3107 in the wiki.
3108 https://github.com/siracusa/rose/wiki/Extending-Rose%3A%3ADB%3A%3AObject
3110 See the support section below for more information on the mailing list.
3112
3114 The Rose development policy applies to this, and all "Rose::*" modules.
3115 Please install Rose from CPAN and then run ""perldoc Rose"" for more
3116 information.
3117
3119 Any Rose::DB::Object questions or problems can be posted to the
3120 Rose::DB::Object mailing list. To subscribe to the list or view the
3121 archives, go here:
3122 <http://groups.google.com/group/rose-db-object>
3124 Although the mailing list is the preferred support mechanism, you can
3126 also email the author (see below) or file bugs using the CPAN bug
3127 tracking system:
3128 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object>
3130 There's also a wiki and other resources linked from the Rose project
3132 home page:
3133 <http://rosecode.org>
3135
3137 John C. Siracusa (siracusa@gmail.com)
3138
3140 Copyright (c) 2007 by John C. Siracusa. All rights reserved. This
3141 program is free software; you can redistribute it and/or modify it
3142 under the same terms as Perl itself.
3143perl v5.38.0 2023-07-21 Rose::DB::Object::Tutorial(3)