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
164 "Product" object will have its own, private "My::DB" object. See the
165 section below, "A brief digression: database objects", for an
166 explanation of 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
211 class.
212 The lesson is simple: when in doubt, subclass. A few minutes spent now
214 can save you a lot more time down the road.
215 Rose::DB::Object in action
217 Now that we have our "Product" class all set up, let's see what we can
219 do with it.
220 Get and set column values
222 By default, each column has a combined accessor/mutator method. When
224 passed a value, the column value is set and returned. When called with
225 no arguments, the value is simply returned.
226 $p->name('Bike'); # set name
228 print $p->name; # get name
229 Since Rose::DB::Object inherits from Rose::Object, each object method
231 is also a valid constructor argument.
232 $p = Product->new(name => 'Cane', price => 1.99);
234 print $p->price; # 1.99
235 Load
237 An object can be loaded based on a primary key.
239 $p = Product->new(id => 1); # primary key
241 $p->load; # Load the object from the database
242 An object can also be loaded based on a unique key:
244 $p = Product->new(name => 'Sled'); # unique key
246 $p->load; # Load the object from the database
247 If there is no row in the database table with the specified primary or
249 unique key value, the call to load() will fail. Under the default
250 error mode, an exception will be thrown. To safely check whether or
251 not such a row exists, use the "speculative" parameter.
252 $p = Product->new(id => 1);
254 unless($p->load(speculative => 1))
256 {
257 print "No such product with id = 1";
258 }
259 Regardless of the error mode, load() will simply return true or false
261 when the "speculative" parameter is used.
262 Insert
264 To insert a row, create an object and then save it.
266 $p = Product->new(id => 123, name => 'Widget', price => 4.56);
268 $p->save; # Insert the object into the database
269 The default error mode will throw an exception if anything goes wrong
271 during the save, so we don't have to check the return value.
272 Here's another variation:
274 $p = Product->new(name => 'Widget', price => 1.23);
276 $p->save;
277 print $p->id; # print the auto-generated primary key value
279 Since the primary key of the "products" table, "id", is a SERIAL
281 column, a new primary key value will be automatically generated if one
282 is not specified. After the object is saved, we can retrieve the auto-
283 generated value.
284 Update
286 To update a row, simply save an object that has been previously loaded
288 or saved.
289 $p1 = Product->new(name => 'Sprocket', price => 9.99);
291 $p1->save; # Insert a new object into the database
292 $p1->price(12.00);
294 $p1->save; # Update the object in the database
295 $p2 = Product->new(id => 1);
297 $p2->load; # Load an existing object
298 $p2->name($p2->name . ' Mark II');
300 $p2->save; # Update the object in the database
301 Delete
303 An object can be deleted based on a primary key or a unique key.
305 $p = Product->new(id => 1); # primary key
307 $p->delete; # Delete the object from the database
308 $p = Product->new(name => 'Sled'); # unique key
310 $p->delete; # Delete the object from the database
311 The delete method will return true if the row was deleted or did not
313 exist, false otherwise.
314 It works just as well with objects that have been loaded or saved.
316 $p1 = Product->new(name => 'Sprocket', price => 9.99);
318 $p1->save; # Insert a new object into the database
319 $p1->delete; # Now delete the object
320 $p2 = Product->new(id => 1);
322 $p2->load; # Load an existing object
323 $p2->delete; # Now delete the object
324 Multiple objects
326 The examples above show SELECT, INSERT, UPDATE, and DELETE operations
328 on one row at time based on primary or unique keys. What about
329 manipulating rows based on other criteria? What about manipulating
330 multiple rows simultaneously? Enter Rose::DB::Object::Manager, or just
331 "the manager" for short.
332 But why is there a separate class for dealing with multiple objects?
334 Why not simply add more methods to the object itself? Say, a
335 "search()" method to go alongside load(), save(), delete() and friends?
336 There are several reasons.
337 First, it's somewhat "semantically impure" for the class that
339 represents a single row to also be the class that's used to fetch
340 multiple rows. It's also important to keep the object method namespace
341 as sparsely populated as possible. Each new object method prevents a
342 column with the same name from using that method name.
343 Rose::DB::Object tries to keep the list of reserved method names as
344 small as possible.
345 Second, inevitably, classes grow. It's important for the object
347 manager class to be separate from the object class itself so each class
348 can grow happily in isolation, with no potential for namespace or
349 functionality clashes.
350 All of that being said, Rose::DB::Object::Manager does include support
352 for adding manager methods to the object class. Obviously, this
353 practice is not recommended, but it exists if you really want it.
354 Anyway, let's see some examples. Making a manager class is simply a
356 matter of inheriting from Rose::DB::Object::Manager, specifying the
357 object class, and then creating a series of appropriately named wrapper
358 methods.
359 package Product::Manager;
361 use base qw(Rose::DB::Object::Manager);
363 sub object_class { 'Product' }
365 __PACKAGE__->make_manager_methods('products');
367 The call to make_manager_methods() creates the following methods:
369 get_products
371 get_products_iterator
372 get_products_count
373 delete_products
374 update_products
375 The names are pretty much self-explanatory. You can read the
377 Rose::DB::Object::Manager documentation for all the gory details. The
378 important thing to note is that the methods were all named based on the
379 "products" argument to make_manager_methods(). You can see how
380 "products" has been incorporated into each of the method names.
381 This naming scheme is just a suggestion. You can name these methods
383 anything you want (using the "methods" parameter to the
384 make_manager_methods() call), or you can even write the methods
385 yourself. Each of these methods is a merely a thin wrapper around the
386 generically-named methods in Rose::DB::Object::Manager. The wrappers
387 pass the specified object class to the generic methods.
388 The Perl code for the "Product::Manager" class shown above can be
390 generated automatically by calling the perl_manager_class method on the
391 Rose::DB::Object::Metadata that's associated with the "Product" class.
392 Similarly, the make_manager_class method called on the "Product"
393 metadata object will both generate the code and evaluate it for you,
394 automating the entire process of creating a manager class from within
395 your Rose::DB::Object-derived class.
396 package Product;
398 use base qw(Rose::DB::Object);
400 ...
401 # This actually creates the Product::Manager class
403 # as shown in the code sample above.
404 __PACKAGE__->meta->make_manager_class('products');
405 As the comment says, the call to make_manager_class will create a
407 standalone "Product::Manager" class in memory. See the documentation
408 for the perl_manager_class and make_manager_class methods for more
409 information.
410 If you decide not to heed my advice, but instead decide to create these
412 methods inside your Rose::DB::Object-derived class directly, you can do
413 so by calling make_manager_methods() from within your object class.
414 package Product;
416 use Rose::DB::Object::Manager;
418 use base 'My::DB::Object';
420 ...
421 Rose::DB::Object::Manager->make_manager_methods('products');
422 This will be the last you see of this technique in this tutorial. All
424 of the examples will assume that the recommended approach is used
425 instead.
426 Fetching objects
428 The most common task for the manager is fetching multiple objects.
430 We'll use the "get_products()" method to do that. It's based on the
431 get_objects() method, which takes many parameters.
432 One (optional) parameter is the now-familiar db object used to connect
434 to the database. This parameter is valid for all
435 Rose::DB::Object::Manager methods. In the absence of this parameter,
436 the init_db() method of the object class will be called in order to
437 create one.
438 Passing no arguments at all will simply fetch every "Product" object in
440 the database.
441 $products = Product::Manager->get_products();
443 foreach my $product (@$products)
445 {
446 print $product->name, "\n";
447 }
448 The return value is a reference to an array of "Product" objects. Now
450 let's go to the other extreme.
451 $products =
453 Product::Manager->get_products(
454 query =>
455 [
456 name => { like => '%Hat' },
457 id => { ge => 7 },
458 or =>
459 [
460 price => 15.00,
461 price => { lt => 10.00 },
462 ],
463 ],
464 sort_by => 'name',
465 limit => 10,
466 offset => 50);
467 That call produces SQL that looks something like this:
469 SELECT id, name, price FROM products WHERE
471 name LIKE '%Hat' AND
472 id >= 7 AND
473 (price = 15.00 OR price < 10.00)
474 ORDER BY name
475 LIMIT 10 OFFSET 50
476 Manager queries support nested boolean logic and several different
478 kinds of comparison operators. For a full explanation of all the
479 options, see the Rose::DB::Object::Manager documentation.
480 The iterator method takes the same kinds of arguments, but returns an
482 iterator that will fetch the objects from the database one at a time.
483 $iterator = Product::Manager->get_products_iterator(...);
485 while($product = $iterator->next)
487 {
488 print $product->id, ' ', $product->name, "\n";
489 $iterator->finish if(...); # exit early?
491 }
492 print $iterator->total; # total iterated over
494 Note that this is a "real" iterator. Objects not iterated over are not
496 fetched from the database at all.
497 Counting objects
499 Counting objects is straightforward. The "get_products_count()" method
501 takes the same kinds of arguments as "get_products()" and
502 "get_products_iterator()". It returns the count.
503 $num_cheap_products =
505 Product::Manager->get_products_count(
506 query => [ price => { lt => 1.00 } ]);
507 Deleting objects
509 The "delete_products()" method accepts the same kinds of "query"
511 arguments as the manager methods described above, only it uses the
512 parameter name "where" instead.
513 $num_rows_deleted =
515 Product::Manager->delete_products(
516 where =>
517 [
518 id => { ne => 123 },
519 name => { like => 'Wax%' },
520 ]);
521 Updating objects
523 The "update_products()" method accepts the same kinds of arguments as
525 the "delete_products()" method, plus a "set" parameter to specify the
526 actual update information.
527 $num_rows_updated =
529 Product::Manager->update_products(
530 set =>
531 {
532 price => 5.00,
533 },
534 where =>
535 [
536 price => 4.99,
537 id => { gt => 100 },
538 ]);
539 The end of the beginning
541 This section has covered the bare minimum usage and functionality of
543 the Rose::DB::Object module distribution. Using these features alone,
544 you can automate the basic CRUD operations (Create, Retrieve, Update,
545 and Delete) for single or multiple objects. But it's almost a shame to
546 stop at this point. There's a lot more that Rose::DB::Object can do
547 for you. The "sweet spot" of effort vs. results is much farther along
548 the curve.
549 In the next section, we will expand upon our "Product" class and tap
551 more of Rose::DB::Object's features. But first...
552 A brief digression: database objects
554 The Rose::DB-derived database object used by each
556 Rose::DB::Object-derived object is available via the db object
557 attribute.
558 $p = Product->new(...);
560 $db = $p->db; # My::DB object
561 You can read the Rose::DB documentation to explore the capabilities of
563 these db objects. Most of the time, you won't have to be concerned
564 about them. But it's sometime useful to deal with them directly.
565 The first thing to understand is where the database object comes from.
567 If the db attribute doesn't exist, it is created by calling init_db().
568 The typical "init_db()" method simply builds a new database object and
569 returns it. (See the Rose::DB tutorial for an explanation of the
570 possible arguments to new(), and why there are none in the call below.)
571 package Product;
573 ...
574 sub init_db { My::DB->new }
575 This means that each "Product" object will have its own "My::DB"
577 object, and therefore (in the absence of modules like Apache::DBI) its
578 own connection to the database.
579 If this not what you want, you can make "init_db()" return the same
581 "My::DB" object to every "Product" object. This will make it harder to
582 ensure that the database handle will be closed when all "Product"
583 objects go out of scope, but that may not be important for your
584 application. The easiest way to do this is to call new_or_cached
585 instead of new.
586 package Product;
588 ...
589 sub init_db { My::DB->new_or_cached }
590 Since "init_db()" is only called if a "Product" object does not already
592 have a db object, another way to share a single "My::DB" object with
593 several "Product" objects is to do so explicitly, either by pre-
594 creating the "My::DB" object:
595 $db = My::DB->new; # will share this db with the Products below
597 $p1 = Product->new(db => $db, ...);
599 $p2 = Product->new(db => $db, ...);
600 $p3 = Product->new(db => $db, ...);
601 or by letting one of the "Product" objects provide the db for the rest.
603 $p1 = Product->new(...);
605 $p2 = Product->new(db => $p1->db, ...); # use $p1's db
606 $p3 = Product->new(db => $p1->db, ...); # use $p1's db
607 A note for mod_perl users: when using Apache::DBI, even if each
609 "Product" has its own "My::DB" object, remember that they will all
610 share a single underlying DBI database handle. That is, each
611 Rose::DB-derived object of a given type and domain will eventually call
612 DBI's connect() method with the same arguments, and therefore return
613 the same, cached database handle when running under Apache::DBI. The
614 default cache implementation underlying the new_or_cached method is
615 also mod_perl-aware and will cooperate with Apache::DBI.
616 Here's an example where sharing a database object is important:
618 creating several "Product" objects in a single transaction.
619 $db = My::DB->new;
621 $db->begin_work; # Start transaction
623 # Use this $db with each product object
625 $p1 = Product->new(name => 'Bike', db => $db);
627 $p1->save;
628 $p2 = Product->new(name => 'Sled', db => $db);
630 $p2->save;
631 $p3 = Product->new(name => 'Kite', db => $db);
633 $p3->save;
634 if(...) # Now either commit them all or roll them all back
636 {
637 $db->commit;
638 }
639 else
640 {
641 $db->rollback;
642 }
643 Cross-database migration is another important use for explicitly shared
645 db objects. Here's how to move a product from a production database to
646 an archive database.
647 $production_db = My::DB->new('production');
649 $archive_db = My::DB->new('archive');
650 # Load bike from production database
652 $p = Product->new(name => 'Bike', db => $production_db);
653 $p->load;
654 # Save the bike into the archive database
656 $p->db($archive_db);
657 $p->save(insert => 1); # force an insert instead of an update
658 # Delete the bike from the production database
660 $p->db($production_db);
661 $p->delete;
662 Mainstream usage
664 Let's imagine that the "products" table has expanded. It now looks
665 like this.
666 CREATE TABLE products
668 (
669 id SERIAL NOT NULL PRIMARY KEY,
670 name VARCHAR(255) NOT NULL,
671 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
672 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
674 CHECK(status IN ('inactive', 'active', 'defunct')),
675 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
677 release_date TIMESTAMP,
678 UNIQUE(name)
680 );
681 We could do a straightforward expansion of the "Product" class as
683 designed in the previous section.
684 package Product;
686 use base 'My::DB::Object';
688 __PACKAGE__->meta->setup
690 (
691 table => 'products',
692 columns => [ qw(id name price status date_created release_date) ],
693 pk_columns => 'id',
694 unique_key => 'name',
695 );
696 But now we're faced with a few problems. First, while the "status"
698 column only accepts a few pre-defined values, our "Product" object will
699 gladly accept any status value. But maybe that's okay because the
700 database will reject invalid values, causing an exception will be
701 thrown when the object is saved.
702 The date/time fields are more troubling. What is the format of a valid
704 value for a TIMESTAMP column in PostgreSQL? Consulting the PostgreSQL
705 documentation will yield the answer, I suppose. But now all the code
706 that uses "Product" objects has to be sure to format the "date_created"
707 and "release_date" values accordingly. That's even more difficult if
708 some of those values come from external sources, such as a web form.
709 Worse, what if we decide to change databases in the future? We'd have
711 to hunt down every single place where a "date_created" or
712 "release_date" value is set and then modify the formatting to match
713 whatever format the new database wants. Oh, and we'll have to look
714 that up too. Blah.
715 Finally, what about all those default values? The "price" column
717 already had a default value, but now two more columns also have
718 defaults. True, the database will take care of this when a row is
719 inserted, but now the Perl object is diverging more and more from the
720 database representation.
721 Let's solve all of these problems. If we more accurately describe the
723 columns, Rose::DB::Object will do the rest.
724 package Product;
726 use base 'My::DB::Object';
728 __PACKAGE__->meta->setup
730 (
731 table => 'products',
732 columns =>
734 [
735 id => { type => 'serial', primary_key => 1, not_null => 1 },
736 name => { type => 'varchar', length => 255, not_null => 1 },
737 price =>
739 {
740 type => 'decimal',
741 precision => 10,
742 scale => 2,
743 not_null => 1,
744 default => 0.00
745 },
746 status =>
748 {
749 type => 'varchar',
750 length => 128,
751 not_null => 1,
752 default => 'inactive',
753 check_in => [ 'inactive', 'active', 'defunct' ],
754 },
755 date_created => { type => 'timestamp', not_null => 1,
757 default => 'now()' },
758 release_date => { type => 'timestamp' },
759 ],
760 unique_key => 'name',
762 allow_inline_column_values => 1,
764 );
765 Before examining what new functionality this new class gives us, there
767 are a few things to note about the definition. First, the primary key
768 is no longer specified with the primary_key_columns() method. Instead,
769 the "id" column has its "primary_key" attribute set to a true value in
770 its description.
771 Second, note the default value for the "date_created" column. It's a
773 string containing a call to the PL/SQL function "now()", which can
774 actually only be run within the database. But thanks to the
775 allow_inline_column_values attribute being set to a true value,
776 Rose::DB::Object will pass the string "now()" through to the database
777 as-is.
778 In the case of "creation date" columns like this, it's often better to
780 let the database provide the value as close as possible to the very
781 moment the row is created. On the other hand, this will mean that any
782 newly created "Product" object will have a "strange" value for that
783 column (the string "now()") until/unless it is re-loaded from the
784 database. It's a trade-off.
785 Let's see the new "Product" class in action. The defaults work as
787 expected.
788 $p = Product->new;
790 print $p->status; # 'inactive'
792 print $p->price; # 0.00
793 The "status" method now restricts its input, throwing an exception if
795 the input is invalid.
796 $p->status('nonesuch'); # Boom! Invalid status: 'nonesuch'
798 The timestamp columns now accept any value that Rose::DateTime::Util's
800 parse_date() method can understand.
801 $p->release_date('2005-01-22 18:00:57');
803 $p->release_date('12/24/1980 10am');
804 See the Rose::DateTime::Util documentation for a full list of
806 acceptable formats.
807 Inside a "Product" object, date/time information is stored in DateTime
809 objects.
810 $dt = $p->release_date; # DateTime object
812 Since DateTime objects can be modified in-place, doing a formerly
814 thorny task like date math is now trivial.
815 $p->release_date->add(days => 1);
817 The "release_date()" method also accepts a DateTime object as an input,
819 of course:
820 $p->release_date(DateTime->new(...));
822 There are even a few convenience functions triggered by passing a
824 name/value pair.
825 # Thursday, December 25th 1980 at 10:00:00 AM
827 print $p->release_date(format => '%A, %B %E %Y at %t');
828 # Clone the DateTime object, truncate the clone, and return it
830 $month_start = $p->release_date(truncate => 'month');
831 print $month_start->strftime('%Y-%m-%d'); # 1980-12-01
833 Conveniently, Rose::DB::Object::Manager queries can also use any values
835 that the corresponding column methods will accept. For example, here's
836 a query that filters on the "release_date" column using a DateTime
837 object.
838 $last_week = DateTime->now->subtract(weeks => 1);
840 $products =
842 Product::Manager->get_products(
843 query =>
844 [
845 release_date => { lt => $last_week },
846 ],
847 sort_by => 'release_date');
848 The upshot is that you no longer have to be concerned about the details
850 of the date/time format(s) understood by the underlying database.
851 You're also free to use DateTime objects as a convenient interchange
852 format in your code.
853 This ability isn't just limited to date/time columns. Any data type
855 that requires special formatting in the database, and/or is more
856 conveniently dealt with as a more "rich" value on the Perl side of the
857 fence is fair game for this treatment.
858 Some other examples include the bitfield column type, which is
860 represented by a Bit::Vector object on the Perl side, and the boolean
861 column type which evaluates the "truth" of its arguments and coerces
862 the value accordingly. In all cases, column values are automatically
863 formatted as required by the native column data types in the database.
864 In some circumstances, Rose::DB::Object can even "fake" a data type for
866 use with a database that does not natively support it. For example,
867 the array column type is natively supported by PostgreSQL, but it will
868 also work with MySQL using a VARCHAR column as a stand-in.
869 Finally, if you're concerned about the performance implications of
871 "inflating" column values from strings and numbers into (relatively)
872 large objects, rest assured that such inflation is only done as needed.
873 For example, an object with ten date/time columns can be loaded,
874 modified, and saved without ever creating a single DateTime object,
875 provided that none of the date/time columns were among those whose
876 values were modified.
877 Put another way, the methods that service the columns have an awareness
879 of the producer and consumer of their data. When data is coming from
880 the database, the column methods accept it as-is. When data is being
881 sent to the database, it is formatted appropriately, if necessary. If
882 a column value was not modified since it was loaded from the database,
883 then the value that was loaded is simply returned as-is. In this way,
884 data can make a round-trip without ever being inflated, deflated, or
885 formatted.
886 This behavior is not a requirement of all column methods, but it is a
888 recommended practice--one followed by all the column classes that are
889 part of the Rose::DB::Object distribution.
890 Auto-initialization and the convention manager
892 The "Product" class set up in the previous section is useful, but it
893 also takes significantly more typing to set up. Over the long term,
894 it's still a clear win. On the other hand, a lot of the details in the
895 column descriptions are already known by the database: column types,
896 default values, maximum lengths, etc. It would be handy if we could
897 ask the database for this information instead of looking it up and
898 typing it in manually.
899 This process of interrogating the database in order to extract metadata
901 is called "auto-initialization." There's an entire section of the
902 Rose::DB::Object::Metadata documentation dedicated to the topic. The
903 executive summary is that auto-initialization saves work in the short-
904 run, but with some long-term costs. Read the friendly manual for the
905 details. For the purposes of this tutorial, I will simply demonstrate
906 the features, culminating in the suggested best practice.
907 Let's start by applying auto-initialization to the "Product" class.
909 package Product;
911 use base 'My::DB::Object';
913 __PACKAGE__->meta->table('products');
915 __PACKAGE__->meta->auto_initialize;
916 Believe it or not, that class is equivalent to the previous
918 incarnation, right down to the details of the columns and the unique
919 key. As long as the table is specified, Rose::DB::Object will dig all
920 the rest of the information out of the database. Handy!
921 In fact, that class can be shortened even further with the help of the
923 convention manager.
924 package Product;
926 use base 'My::DB::Object';
928 __PACKAGE__->meta->auto_initialize;
930 Now even the table is left unspecified. How does Rose::DB::Object know
932 what to do in this case? Why, by convention, of course. The default
933 convention manager dictates that class names are singular and
934 TitleCased, and their corresponding table names are lowercase and
935 plural. Thus, the omitted table name in the "Product" class is, by
936 convention, assumed to be named "products".
937 Like auto-initialization, the convention manager is handy, but may also
939 present some maintenance issues. I tend to favor a more explicitly
940 approach, but I can also imagine scenarios where the convention manager
941 is a good fit.
942 Keep in mind that customized convention managers are possible, allowing
944 individual organizations or projects to define their own conventions.
945 You can read all about it in the Rose::DB::Object::ConventionManager
946 documentation.
947 Anyway, back to auto-initialization. Yes, auto_initialize() will dig
949 out all sorts of interesting and important information for you.
950 Unfortunately, it will dig that information out every single time the
951 class is loaded. Worse, this class will fail to load at all if a
952 database connection is not immediately available.
953 Auto-initialization seems like something that is best done only once,
955 with the results being saved in a more conventional form. That's just
956 what Rose::DB::Object::Metadata's code generation functions are
957 designed to do. The "perl_*" family of methods can generate snippets
958 of Perl code, or even entire classes, based on the results of the auto-
959 initialization process. They'll even honor some basic code formatting
960 directives.
961 package Product;
963 use base 'My::DB::Object';
965 __PACKAGE__->meta->table('products');
967 __PACKAGE__->meta->auto_initialize;
968 print __PACKAGE__->meta->perl_class_definition(indent => 2,
970 braces => 'bsd');
971 Here's the output of that print statement. A few long lines were
973 manually wrapped, but it's otherwise unmodified.
974 package Product;
976 use strict;
978 use base 'My::DB::Object';
980 __PACKAGE__->meta->setup
982 (
983 table => 'products',
984 columns =>
986 [
987 id => { type => 'integer', not_null => 1 },
988 name => { type => 'varchar', length => 255, not_null => 1 },
989 price => { type => 'numeric', default => '0.00',
990 not_null => 1, precision => 10, scale => 2 },
991 vendor_id => { type => 'integer' },
992 status => { type => 'varchar', default => 'inactive',
993 length => 128, not_null => 1 },
994 date_created => { type => 'timestamp', default => 'now()',
995 not_null => 1 },
996 release_date => { type => 'timestamp' },
997 ],
998 primary_key_columns => [ 'id' ],
1000 unique_keys => [ 'name' ],
1002 allow_inline_column_values => 1,
1004 );
1005 1;
1007 Copy and paste that output back into the "Product.pm" file and you're
1009 in business.
1010 The door is open to further automation through scripts that call the
1012 methods demonstrated above. Although it's my inclination to work
1013 towards a static, explicit type of class definition, the tools are
1014 there for those who prefer a more dynamic approach.
1015 Foreign keys
1017 When a column in one table references a row in another table, the
1018 referring table is said to have a "foreign key." As with primary and
1019 unique keys, Rose::DB::Object supports foreign keys made up of more
1020 than one column.
1021 In the context of Rose::DB::Object, a foreign key is a database-
1023 supported construct that ensures that any non-null value in a foreign
1024 key column actually refers to an existing row in the foreign table.
1025 Databases that enforce this constraint are said to support "referential
1026 integrity." Foreign keys are only applicable to
1027 Rose::DB::Object-derived classes when the underlying database supports
1028 "native" foreign keys and enforces referential integrity.
1029 While it's possible to define foreign keys in a
1031 Rose::DB::Object-derived class even if there is no support for them in
1032 the database, this is considered bad practice. If you're just trying
1033 to express some sort of relationship between two tables, there's a more
1034 appropriate way to do so. (More on that in the next section.)
1035 Let's add a foreign key to the "products" table. First, we'll need to
1037 create the table that the foreign key will reference.
1038 CREATE TABLE vendors
1040 (
1041 id SERIAL NOT NULL PRIMARY KEY,
1042 name VARCHAR(255) NOT NULL,
1043 UNIQUE(name)
1045 );
1046 When dealing with any kind of inter-table relationship,
1048 Rose::DB::Object requires a Rose::DB::Object-derived class fronting
1049 each participating table. So we need a class for the "vendors" table.
1050 package Vendor;
1052 use base 'My::DB::Object';
1054 __PACKAGE__->meta->setup
1056 (
1057 table => 'vendors',
1058 columns =>
1059 [
1060 id => { type => 'serial', primary_key => 1, not_null => 1 },
1061 name => { type => 'varchar', length => 255, not_null => 1 },
1062 ],
1063 unique_key => 'name',
1064 );
1065 Now we'll add the foreign key to our ever-growing "products" table.
1067 CREATE TABLE products
1069 (
1070 id SERIAL NOT NULL PRIMARY KEY,
1071 name VARCHAR(255) NOT NULL,
1072 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
1073 vendor_id INT REFERENCES vendors (id),
1075 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
1077 CHECK(status IN ('inactive', 'active', 'defunct')),
1078 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
1080 release_date TIMESTAMP,
1081 UNIQUE(name)
1083 );
1084 Finally, here's how the foreign key definition looks in the Perl class.
1086 package Product;
1088 use base 'My::DB::Object';
1090 __PACKAGE__->meta->setup
1092 (
1093 table => 'products',
1094 columns =>
1096 [
1097 id => { type => 'integer', not_null => 1 },
1098 name => { type => 'varchar', length => 255, not_null => 1 },
1099 price => { type => 'numeric', default => '0.00',
1100 not_null => 1, precision => 10, scale => 2 },
1101 vendor_id => { type => 'integer' },
1102 status => { type => 'varchar', default => 'inactive',
1103 length => 128, not_null => 1 },
1104 date_created => { type => 'timestamp', default => 'now()',
1105 not_null => 1 },
1106 release_date => { type => 'timestamp' },
1107 ],
1108 primary_key_columns => [ 'id' ],
1110 unique_keys => [ 'name' ],
1112 allow_inline_column_values => 1,
1114 foreign_keys =>
1116 [
1117 vendor =>
1118 {
1119 class => 'Vendor',
1120 key_columns => { vendor_id => 'id' },
1121 },
1122 ],
1123 );
1124 Note that a "vendor_id" column is added to the column list. This needs
1126 to be done independently of any foreign key definition. It's a new
1127 column, so it needs to be in the column list. There's nothing more to
1128 it than that.
1129 There's also the foreign key definition itself. The name/hashref-value
1131 pair passed to the foreign_keys() method is (roughly) shorthand for
1132 this.
1133 Rose::DB::Object::Metadata::ForeignKey->new(
1135 name => 'vendor',
1136 class => 'Vendor',
1137 key_columns => { vendor_id => 'id' });
1138 In other words, "vendor" is the name of the foreign key, and the rest
1140 of the information is used to set attributes on the foreign key object.
1141 You could, in fact, construct your own foreign key objects and pass
1142 them to foreign_keys() (or add_foreign_keys(), etc.) but that would
1143 require even more typing.
1144 Going in the other direction, since our class and column names match up
1146 with what the convention manager expects, we could actually shorten the
1147 foreign key setup code to this.
1148 foreign_keys => [ 'vendor' ],
1150 Given only a foreign key name, the convention manager will derive the
1152 "Vendor" class name and will find the "vendor_id" column in the
1153 "Product" class and match it up to the primary key of the "vendors"
1154 table. As with most things in Rose::DB::Object class setup, you can be
1155 as explicit or as terse as you feel comfortable with, depending on how
1156 closely you conform to the expected conventions.
1157 So, what does this new "vendor" foreign key do for us? Let's add some
1159 data and see. Imagine the following two objects.
1160 $v = Vendor->new(name => 'Acme')->save;
1162 $p = Product->new(name => 'Kite')->save;
1163 Note the use of the idiomatic way to create and then save an object in
1165 "one step." This is possible because both the new and save methods
1166 return the object itself. Anyway, let's link the two objects. One way
1167 to do it is to set the column values directly.
1168 $p->vendor_id($v->id);
1170 $p->save;
1171 To use this technique, we must know which columns link to which other
1173 columns, of course. But it works. We can see this by calling the
1174 method named after the foreign key itself: "vendor()".
1175 $v = $p->vendor; # Vendor object
1177 print $v->name; # "Acme"
1178 The "vendor()" method can be used to link the two objects as well.
1180 Let's start over and try it that way:
1181 $v = Vendor->new(name => 'Smith')->save;
1183 $p = Product->new(name => 'Knife')->save;
1184 $p->vendor($v);
1186 $p->save;
1187 print $p->vendor->name; # "Smith"
1189 Remember that there is no column named "vendor" in the "products"
1191 table. There is a "vendor_id" column, which has its own "vendor_id()"
1192 get/set method that accepts and returns an integer value, but that's
1193 not what we're doing in the example above. Instead, we're calling the
1194 "vendor()" method, which accepts and returns an entire "Vendor" object.
1195 The "vendor()" method actually accepts several different kinds of
1197 arguments, all of which it inflates into "Vendor" objects. An already-
1198 formed "Vendor" object was passed above, but other formats are
1199 possible. Imagine a new product also made by Smith.
1200 $p = Product->new(name => 'Rope')->save;
1202 $p->vendor(name => 'Smith');
1203 $p->save;
1204 Here the arguments passed to the "vendor()" method are name/value pairs
1206 which will be used to construct the appropriate "Vendor" object. Since
1207 "name" is a unique key in the "vendors" table, the "Vendor" class can
1208 look up the existing vendor named "Smith" and assign it to the "Rope"
1209 product.
1210 If no vendor named "Smith" existed, one would have been created when
1212 the product was saved. In this case, the save process would take place
1213 within a transaction (assuming the database supports transactions) to
1214 ensure that both the product and vendor are created successfully, or
1215 neither is.
1216 The name/value pairs can also be provided in a reference to a hash.
1218 $p = Product->new(name => 'Rope')->save;
1220 $p->vendor({ name => 'Smith' });
1221 $p->save;
1222 Here's yet another argument format. Imagine that the "Acme" vendor id
1224 is 1.
1225 $p = Product->new(name => 'Crate')->save;
1227 $p->vendor(1);
1228 $p->save;
1229 print $p->vendor->name; # "Acme"
1231 Like the name/value pair argument format, a primary key value will be
1233 used to construct the appropriate object. (This only works if the
1234 foreign table has a single-column primary key, of course.) And like
1235 before, if such an object doesn't exist, it will be created. But in
1236 this case, if no existing vendor object had an "id" of 1, the attempt
1237 to create one would have failed because the "name" column of the
1238 inserted row would have been null.
1239 To summarize, the foreign key method can take arguments in these forms.
1241 · An object of the appropriate class.
1243 · Name/value pairs used to construct such an object.
1245 · A reference to a hash containing name/value pairs used to construct
1247 such an object.
1248 · A primary key value (but only if the foreign table has a single-
1250 column primary key).
1251 In each case, the foreign object will be added to the database it if
1253 does not already exist there. This all happens when the "parent"
1254 ("Product") object is saved. Until then, nothing is stored in the
1255 database.
1256 There's also another method created in response to the foreign key
1258 definition. This one allows the foreign object to be deleted from the
1259 database.
1260 print $p->vendor->name; # "Acme"
1262 $p->delete_vendor();
1263 $p->save; # The "Acme" vendor is deleted from the vendors table
1264 Again, the actual database modification takes place when the parent
1266 object is saved. Note that this operation will fail if any other rows
1267 in the "products" table still reference the Acme vendor. And again,
1268 since this all takes place within a transaction (where supported), the
1269 entire operation will fail or succeed as a single unit.
1270 Finally, if we want to simply disassociate a product from its vendor,
1272 we can simply set the vendor to undef.
1273 $p->vendor(undef); # This product has no vendor
1275 $p->save;
1276 Setting the "vendor_id" column directly has the same effect, of course.
1278 $p->vendor_id(undef); # set vendor_id = NULL
1280 $p->save;
1281 Before moving on to the next section, here's a brief note about auto-
1283 initialization and foreign keys. Since foreign keys are a construct of
1284 the database itself, the auto-initialization process can actually
1285 discover them and create the appropriate foreign key metadata.
1286 Since all of the column and table names are still in sync with the
1288 expected conventions, the "Product" class can still be defined like
1289 this:
1290 package Product;
1292 use base 'My::DB::Object';
1294 __PACKAGE__->meta->auto_initialize;
1296 while retaining all of the abilities demonstrated above.
1298 The perl_class_definition() method will produce the appropriate foreign
1300 key definitions, as expected.
1301 package Product;
1303 use base 'My::DB::Object';
1305 __PACKAGE__->meta->auto_initialize;
1307 print __PACKAGE__->meta->perl_class_definition(indent => 2,
1309 braces => 'bsd');
1310 Here's the output.
1312 package Product;
1314 use base 'My::DB::Object';
1316 __PACKAGE__->meta->setup
1318 (
1319 table => 'products',
1320 columns =>
1322 [
1323 id => { type => 'integer', not_null => 1 },
1324 name => { type => 'varchar', length => 255, not_null => 1 },
1325 price => { type => 'numeric', default => '0.00',
1326 not_null => 1, precision => 10, scale => 2 },
1327 vendor_id => { type => 'integer' },
1328 status => { type => 'varchar', default => 'inactive',
1329 length => 128, not_null => 1 },
1330 date_created => { type => 'timestamp', default => 'now()',
1331 not_null => 1 },
1332 release_date => { type => 'timestamp' },
1333 ],
1334 primary_key_columns => [ 'id' ],
1336 unique_keys => [ 'name' ],
1338 allow_inline_column_values => 1,
1340 foreign_keys =>
1342 [
1343 vendor =>
1344 {
1345 class => 'Vendor',
1346 key_columns => { vendor_id => 'id' },
1347 },
1348 ],
1349 );
1350 1;
1352 Relationships
1354 One-to-one and many-to-one relationships
1355 Foreign keys are a database-native representation of a specific kind of
1357 inter-table relationship. This concept can be further generalized to
1358 encompass other kinds of relationships as well. But before we delve
1359 into that, let's consider the kind of relationship that a foreign key
1360 represents.
1361 In the product and vendor example in the previous section, each product
1363 has one vendor. (Actually it can have zero or one vendor, since the
1364 "vendor_id" column allows NULL values. But for now, we'll leave that
1365 aside.)
1366 When viewed in terms of the participating tables, things look slightly
1368 different. Earlier, we established that several products can have the
1369 same vendor. So the inter-table relationship is actually this: many
1370 rows from the "products" table may refer to one row from the "vendors"
1371 table.
1372 Rose::DB::Object describes inter-table relationships from the
1374 perspective of a given table by using the cardinality of the "local"
1375 table ("products") followed by the cardinality of the "remote" table
1376 ("vendors"). The foreign key in the "products" table (and "Product"
1377 class) therefore represents a "many to one" relationship.
1378 If the relationship were different and each vendor was only allowed to
1380 have a single product, then the relationship would be "one to one."
1381 Given only the foreign key definition as it exists in the database,
1382 it's not possible to determine whether the relationship is "many to
1383 one" or "one to one." The default is "many to one" because that's the
1384 less restrictive choice.
1385 To override the default, a relationship type string can be included in
1387 the foreign key description.
1388 foreign_keys =>
1390 [
1391 vendor =>
1392 {
1393 class => 'Vendor',
1394 key_columns => { vendor_id => 'id' },
1395 relationship_type => 'one to one',
1396 },
1397 ],
1398 (The "relationship_type" parameter may be shortened to "rel_type", if
1400 desired.)
1401 Rose::DB::Object generalizes all inter-table relationships using a
1403 family of aptly named relationship objects. Each inherits from the
1404 Rose::DB::Object::Metadata::Relationship base class.
1405 Even foreign keys are included under the umbrella of this concept.
1407 When foreign key metadata is added to a Rose::DB::Object-derived class,
1408 a corresponding "many to one" or "one to one" relationship is actually
1409 added as well. This relationship is simply a proxy for the foreign
1410 key. It exists so that the set of relationship objects encompasses all
1411 relationships, even those that correspond to foreign keys in the
1412 database. This makes iterating over all relationships in a class a
1413 simple affair.
1414 foreach my $rel (Product->meta->relationships)
1416 {
1417 print $rel->name, ': ', $rel->type, "\n";
1418 }
1419 For the "Product" class, the output is:
1421 vendor: many to one
1423 Given the two possible cardinalities, "many" and "one", it's easy to
1425 come up with a list of all possible inter-table relationships. Here
1426 they are, listed with their corresponding relationship object classes.
1427 one to one - Rose::DB::Object::Metadata::Relationship::OneToOne
1429 one to many - Rose::DB::Object::Metadata::Relationship::OneToMany
1430 many to one - Rose::DB::Object::Metadata::Relationship::ManyToOne
1431 many to many - Rose::DB::Object::Metadata::Relationship::ManyToMany
1432 We've already seen that "one to one" and "many to one" relationships
1434 can be represented by foreign keys in the database, but that's not a
1435 requirement. It's perfectly possible to have either of those two kinds
1436 of relationships in a database that has no native support for foreign
1437 keys. (MySQL using the MyISAM storage engine is a common example.)
1438 If you find yourself using such a database, there's no reason to lie to
1440 your Perl classes by adding foreign key metadata. Instead, simply add
1441 a relationship.
1442 Here's an example of our "Product" class as it might exist on a
1444 database that does not support foreign keys. (The "Product" class is
1445 getting larger now, so previously established portions may be omitted
1446 from now on.)
1447 package Product;
1449 use base 'My::DB::Object';
1451 __PACKAGE__->meta->setup
1453 (
1454 table => 'products',
1455 columns => [... ],
1456 pk_columns => 'id',
1457 unique_key => 'name',
1458 relationships =>
1460 [
1461 vendor =>
1462 {
1463 type => 'many to one',
1464 class => 'Vendor',
1465 column_map => { vendor_id => 'id' },
1466 },
1467 ],
1468 );
1469 They syntax and semantics are similar to those described for foreign
1471 keys. The only slight differences are the names and types of
1472 parameters accepted by relationship objects.
1473 In the example above, a "many to one" relationship named "vendor" is
1475 set up. As demonstrated before, this definition can be reduced much
1476 further, allowing the convention manager to fill in the details. But
1477 unlike the case with the foreign key definition, where only the name
1478 was supplied, we must provide the relationship type as well.
1479 relationships => [ vendor => { type => 'many to one' } ],
1481 There's an even more convenient shorthand for that:
1483 relationships => [ vendor => 'many to one' ],
1485 (Again, this all depends on naming the tables, classes, and columns in
1487 accordance with the expectations of the convention manager.) The
1488 resulting "vendor()" and "delete_vendor()" methods behave exactly the
1489 same as the methods created on behalf of the foreign key definition.
1490 One-to-many relationships
1492 Now let's explore the other two relationship types. We'll start with
1494 "one to many" by adding region-specific pricing to our products.
1495 First, we'll need a "prices" table.
1496 CREATE TABLE prices
1498 (
1499 id SERIAL NOT NULL PRIMARY KEY,
1500 product_id INT NOT NULL REFERENCES products (id),
1501 region CHAR(2) NOT NULL DEFAULT 'US',
1502 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
1503 UNIQUE(product_id, region)
1505 );
1506 This table needs a corresponding Rose::DB::Object-derived class, of
1508 course.
1509 package Price;
1511 use base 'My::DB::Object';
1513 __PACKAGE__->meta->setup
1515 (
1516 table => 'prices',
1517 columns =>
1519 [
1520 id => { type => 'serial', not_null => 1 },
1521 product_id => { type => 'int', not_null => 1 },
1522 region => { type => 'char', length => 2, not_null => 1 },
1523 price =>
1524 {
1525 type => 'decimal',
1526 precision => 10,
1527 scale => 2,
1528 not_null => 1,
1529 default => 0.00
1530 },
1531 ],
1532 primary_key_columns => [ 'id' ],
1534 unique_key => [ 'product_id', 'region' ],
1536 foreign_keys =>
1538 [
1539 product =>
1540 {
1541 class => 'Product',
1542 key_columns => { product_id => 'id' },
1543 },
1544 ],
1545 );
1546 The "price" column can be removed from the "products" table.
1548 ALTER TABLE products DROP COLUMN price;
1550 Finally, the "Product" class needs to be modified to reference the
1552 "prices" table.
1553 package Product;
1555 use base 'My::DB::Object';
1557 use Price;
1559 use Vendor;
1560 __PACKAGE__->meta->setup
1562 (
1563 table => 'products',
1564 columns => [ ... ],
1565 pk_columns => 'id',
1566 unique_key => 'name',
1567 foreign_keys =>
1569 [
1570 vendor =>
1571 {
1572 class => 'Vendor',
1573 key_columns => { vendor_id => 'id' },
1574 },
1575 ],
1576 relationships =>
1578 [
1579 prices =>
1580 {
1581 type => 'one to many',
1582 class => 'Price',
1583 column_map => { id => 'product_id' },
1584 },
1585 ],
1586 );
1587 Note that both the column map for the "one to many" relationship and
1589 the key columns for the foreign key connect "local" columns to
1590 "foreign" columns.
1591 The "vendor_id" column in the local table ("products") is connected to
1593 the "id" column in the foreign table ("vendors"):
1594 vendor =>
1596 {
1597 key_columns => { vendor_id => 'id' },
1598 ...
1599 }
1600 The "id" column in the local table ("products") is connected to the
1602 "product_id" column in the foreign table ("prices"):
1603 prices =>
1605 {
1606 column_map => { id => 'product_id' },
1607 ...
1608 }
1609 This is all from the perspective of the class in which the definitions
1611 appear. Note that things are reversed in the "Price" class.
1612 package Price;
1614 ...
1615 __PACKAGE__->meta->setup
1616 (
1617 ...
1618 foreign_keys =>
1619 [
1620 product =>
1621 {
1622 class => 'Product',
1623 key_columns => { product_id => 'id' },
1624 },
1625 ],
1626 );
1627 Here, the "product_id" column in the local table ("prices") is
1629 connected to the "id" column in the foreign table ("products").
1630 The methods created by "... to many" relationships behave much like
1632 their "... to one" and foreign key counterparts. The main difference
1633 is that lists or references to arrays of the previously described
1634 argument formats are also acceptable, while name/value pairs outside of
1635 a hashref are not.
1636 Here's a list of argument types accepted by "many to one" methods like
1638 "prices".
1639 · A list or reference to an array of objects of the appropriate
1641 class.
1642 · A list or reference to an array of hash references containing
1644 name/value pairs used to construct such objects.
1645 · A list or reference to an array of primary key values (but only if
1647 the foreign table has a single-column primary key).
1648 Setting a new list of prices will delete all the old prices. As with
1650 foreign keys, any actual database modification happens when the parent
1651 object is saved. Here are some examples.
1652 $p = Product->new(name => 'Kite');
1654 $p->prices({ price => 1.23, region => 'US' },
1655 { price => 4.56, region => 'UK' });
1656 $p->save; # database is modified here
1658 # US: 1.23, UK: 4.56
1660 print join(', ', map { $_->region . ': ' . $_->price } $p->prices);
1661 New prices can be added without deleting and resetting the entire list:
1663 # Add two prices to the existing list
1665 $p->add_prices({ price => 7.89, region => 'DE' },
1666 { price => 1.11, region => 'JP' });
1667 $p->save; # database is modified here
1669 Passing a reference to an empty array will cause all the prices to be
1671 deleted:
1672 $p->prices([]); # delete all prices associated with this product
1674 $p->save; # database is modified here
1675 Cascading delete
1677 Deleting a product now becomes slightly more interesting. The naive
1679 approach fails.
1680 $p->delete; # Fatal error!
1682 # DBD::Pg::st execute failed: ERROR: update or delete on "products"
1684 # violates foreign key constraint "prices_product_id_fkey" on
1685 # "prices"
1686 # DETAIL: Key (id)=(12345) is still referenced from table "prices".
1687 Since rows in the "prices" table now link to rows in the "products"
1689 table, a product cannot be deleted until all of the prices that refer
1690 to it are also deleted. There are a few ways to deal with this.
1691 The best solution is to add a trigger to the "products" table itself in
1693 the database that makes sure to delete any associated prices before
1694 deleting a product. This change will allow the naive approach shown
1695 above to work correctly.
1696 A less robust solution is necessary if your database does not support
1698 triggers. One such solution is to manually delete the prices before
1699 deleting the product. This can be done in several ways. The prices
1700 can be deleted directly, like this.
1701 foreach my $price ($p->prices)
1703 {
1704 $price->delete; # Delete all associated prices
1705 }
1706 $p->delete; # Now it's safe to delete the product
1708 The list of prices for the product can also be set to an empty list,
1710 which will have the effect of deleting all associated prices when the
1711 product is saved.
1712 $p->prices([]);
1714 $p->save; # All associated prices deleted here
1715 $p->delete; # Now it's safe to delete the product
1716 Finally, the delete() method can actually automate this process, and do
1718 it all inside a transaction as well.
1719 $p->delete(cascade => 1); # Delete all associated rows too
1721 Again, the recommended approach is to use triggers inside the database
1723 itself. But if necessary, these other approaches will work too.
1724 Many-to-many relationships
1726 The final relationship type is the most complex. In a "many to many"
1728 relationship, a single row in table A may be related to multiple rows
1729 in table B, while a single row in table B may also be related to
1730 multiple rows in table A. (Confused? A concrete example will follow
1731 shortly.)
1732 This kind of relationship involves three tables instead of just two.
1734 The "local" and "foreign" tables, familiar from the other relationship
1735 types described above, still exist, but now there's a third table that
1736 connects rows from those two tables. This third table is called the
1737 "mapping table," and the Rose::DB::Object-derived class that fronts it
1738 is called the "map class."
1739 Let's add such a relationship to our growing family of classes.
1741 Imagine that each product may come in several colors. Right away,
1742 both the "one to one" and "many to one" relationship types are
1743 eliminated since they can only provide a single color for any given
1744 product.
1745 But wait, isn't a "one to many" relationship suitable? After all, one
1747 product may have many colors. Unfortunately, such a relationship is
1748 wasteful in this case. Let's see why. Imagine a "colors" table like
1749 this.
1750 CREATE TABLE colors
1752 (
1753 id SERIAL NOT NULL PRIMARY KEY,
1754 name VARCHAR(255) NOT NULL,
1755 product_id INT NOT NULL REFERENCES products (id)
1756 );
1757 Here's a simple "Color" class to front it.
1759 package Color;
1761 use base 'My::DB::Object';
1763 __PACKAGE__->meta->setup
1765 (
1766 table => 'colors',
1767 columns =>
1768 [
1769 id => { type => 'serial', primary_key => 1, not_null => 1 },
1770 name => { type => 'varchar', length => 255, not_null => 1 },
1771 product_id => { type => 'int', not_null => 1 },
1772 ],
1773 foreign_keys =>
1775 [
1776 product =>
1777 {
1778 class => 'Product',
1779 key_columns => { product_id => 'id' },
1780 },
1781 ],
1782 );
1783 Finally, let's add the "one to many" relationship to the "Product"
1785 class.
1786 package Product;
1788 use base 'My::DB::Object';
1790 __PACKAGE__->meta->setup
1792 (
1793 ...
1794 relationships =>
1795 [
1796 colors =>
1797 {
1798 type => 'one to many',
1799 class => 'Color',
1800 column_map => { id => 'product_id' },
1801 },
1802 ...
1803 ],
1804 );
1805 It works as expected.
1807 $p1 = Product->new(id => 10,
1809 name => 'Sled',
1810 colors =>
1811 [
1812 { name => 'red' },
1813 { name => 'green' },
1814 ]);
1815 $p1->save;
1816 $p2 = Product->new(id => 20,
1818 name => 'Kite',
1819 colors =>
1820 [
1821 { name => 'blue' },
1822 { name => 'green' },
1823 { name => 'red' },
1824 ]);
1825 $p2->save;
1826 But now look at the contents of the "colors" table in the database.
1828 mydb=# select * from colors;
1830 id | name | product_id
1832 ----+-------+------------
1833 1 | red | 10
1834 2 | green | 10
1835 3 | blue | 20
1836 4 | green | 20
1837 5 | red | 20
1838 Notice that the colors "green" and "red" appear twice. Now imagine
1840 that there are 50,000 products. What are the odds that there will be
1841 more than a few colors in common among them?
1842 This is a poor database design. To fix it, we must recognize that
1844 colors will be shared among products, since the set of possible colors
1845 is relatively small compared to the set of possible products. One
1846 product may have many colors, but one color may also belong to many
1847 products. And there you have it: a textbook "many to many"
1848 relationship.
1849 Let's redesign this relationship in "many to many" form, starting with
1851 a new version of the "colors" table.
1852 CREATE TABLE colors
1854 (
1855 id SERIAL NOT NULL PRIMARY KEY,
1856 name VARCHAR(255) NOT NULL,
1857 UNIQUE(name)
1859 );
1860 Since each color will now appear only once in this table, we can make
1862 the "name" column a unique key.
1863 Here's the new "Color" class.
1865 package Color;
1867 use base 'My::DB::Object';
1869 __PACKAGE__->meta->setup
1871 (
1872 table => 'colors',
1873 columns =>
1874 [
1875 id => { type => 'serial', primary_key => 1, not_null => 1 },
1876 name => { type => 'varchar', length => 255, not_null => 1 },
1877 ],
1878 unique_key => 'name',
1880 );
1881 Since the "colors" table no longer has a foreign key that points to the
1883 "products" table, we need some way to connect the two tables: a mapping
1884 table.
1885 CREATE TABLE product_color_map
1887 (
1888 product_id INT NOT NULL REFERENCES products (id),
1889 color_id INT NOT NULL REFERENCES colors (id),
1890 PRIMARY KEY(product_id, color_id)
1892 );
1893 Note that there's no reason for a separate primary key column in this
1895 table. We'll use a two-column primary key instead.
1896 Here's the map class.
1898 package ProductColorMap;
1900 use base 'My::DB::Object';
1902 __PACKAGE__->meta->setup
1904 (
1905 table => 'product_color_map',
1906 columns =>
1907 [
1908 product_id => { type => 'int', not_null => 1 },
1909 color_id => { type => 'int', not_null => 1 },
1910 ],
1911 primary_key_columns => [ 'product_id', 'color_id' ],
1913 foreign_keys =>
1915 [
1916 product =>
1917 {
1918 class => 'Product',
1919 key_columns => { product_id => 'id' },
1920 },
1921 color =>
1923 {
1924 class => 'Color',
1925 key_columns => { color_id => 'id' },
1926 },
1927 ],
1928 );
1929 It's important that the map class have either a foreign key or a "many
1931 to one" relationship pointing to each of the tables that it maps
1932 between. In this case, there are two foreign keys.
1933 Finally, here's the "many to many" relationship definition in the
1935 "Product" class.
1936 package Product;
1938 ...
1939 __PACKAGE__->meta->setup
1940 (
1941 ...
1942 relationships =>
1943 [
1944 colors =>
1945 {
1946 type => 'many to many',
1947 map_class => 'ProductColorMap'
1948 map_from => 'product',
1949 map_to => 'color',
1950 },
1951 ...
1952 ],
1953 );
1954 Note that only the map class needs to be "use"d in the "Product" class.
1956 The relationship definition specifies the name of the map class, and
1957 (optionally) the names of the foreign keys or "many to one"
1958 relationships in the map class that connect the two tables.
1959 In most cases, these two parameters ("map_from" and "map_to") are
1961 unnecessary. Rose::DB::Object will figure out what to do given only
1962 the map class, so long as there's no ambiguity in the mapping table.
1963 In this case, there is no ambiguity, so the relationship definition can
1965 be shortened to this.
1966 use Product;
1968 ...
1969 __PACKAGE__->meta->setup
1970 (
1971 relationships =>
1972 [
1973 colors =>
1974 {
1975 type => 'many to many',
1976 map_class => 'ProductColorMap'
1977 },
1978 ],
1979 ...
1980 );
1981 In fact, since the map table is named according to the default
1983 conventions, it can be shortened even further.
1984 use Product;
1986 ...
1987 __PACKAGE__->meta->setup
1988 (
1989 relationships =>
1990 [
1991 colors => { type => 'many to many' },
1992 ...
1993 ],
1994 ...
1995 );
1996 And further still:
1998 use Product;
2000 ...
2001 __PACKAGE__->meta->setup
2002 (
2003 relationships =>
2004 [
2005 colors => 'many to many',
2006 ...
2007 ],
2008 ...
2009 );
2010 (Classes can be shortened even more absurdly when auto-initialization
2012 is combined with the convention manager. See the convention manager
2013 documentation for an example.)
2014 Now let's revisit the example code.
2016 $p1 = Product->new(id => 10,
2018 name => 'Sled',
2019 colors =>
2020 [
2021 { name => 'red' },
2022 { name => 'green' }
2023 ]);
2024 $p1->save;
2025 $p2 = Product->new(id => 20,
2027 name => 'Kite',
2028 colors =>
2029 [
2030 { name => 'blue' },
2031 { name => 'green' },
2032 { name => 'red' },
2033 ]);
2034 $p2->save;
2035 The code works as expected, but the database now looks much nicer.
2037 mydb=# select * from colors;
2039 id | name
2041 ----+-------
2042 1 | red
2043 2 | green
2044 3 | blue
2045 mydb=# select * from product_color_map;
2048 product_id | color_id
2050 ------------+----------
2051 10 | 1
2052 10 | 2
2053 20 | 3
2054 20 | 2
2055 20 | 1
2056 Each color appears only once, and the mapping table handles all the
2058 connections between the "colors" and "products" tables.
2059 The "many to many" "colors" method works much like the "one to many"
2061 "prices" method described earlier. The valid argument formats are the
2062 same.
2063 · A list or reference to an array of objects of the appropriate
2065 class.
2066 · A list or reference to an array of hash references containing
2068 name/value pairs used to construct such objects.
2069 · A list or reference to an array of primary key values (but only if
2071 the foreign table has a single-column primary key).
2072 The database modification behavior is also the same, with changes
2074 happening when the "parent" object is saved.
2075 $p = Product->new(id => 123)->load;
2077 $p->colors({ name => 'green' },
2079 { name => 'blue' });
2080 $p->save; # database is modified here
2082 Setting the list of colors replaces the old list, but in the case of a
2084 "many to many" relationship, only the map records are deleted.
2085 $p = Product->new(id => 123)->load;
2087 $p->colors({ name => 'pink' },
2089 { name => 'orange' });
2090 # Delete old rows in the mapping table and create new ones
2092 $p->save;
2093 New colors can be added without deleting and resetting the entire list:
2095 # Add two colors to the existing list
2097 $p->add_colors({ name => 'gray' },
2098 { name => 'red' });
2099 $p->save; # database is modified here
2101 Passing a reference to an empty array will remove all colors associated
2103 with a particular product by deleting all the mapping table entries.
2104 $p->colors([]);
2106 $p->save; # all mapping table entries for this product deleted here
2107 Finally, the same caveats described earlier about deleting products
2109 that have associated prices apply to colors as well. Again, I
2110 recommend using a trigger in the database to handle this, but
2111 Rose::DB::Object's cascading delete feature will work in a pinch.
2112 # Delete all associated rows in the prices table, plus any
2114 # rows in the product_color_map table, before deleting the
2115 # row in the products table.
2116 $p->delete(cascade => 1);
2117 Relationship code summary
2119 To summarize this exploration of inter-table relationships, here's a
2121 terse summary of the current state of our Perl classes, and the
2122 associated database tables.
2123 For the sake of brevity, I've chosen to use the shorter versions of the
2125 foreign key and relationship definitions in the Perl classes shown
2126 below. Just remember that this only works when your tables, columns,
2127 and classes are named according to the expected conventions.
2128 First, the database schema.
2130 CREATE TABLE vendors
2132 (
2133 id SERIAL NOT NULL PRIMARY KEY,
2134 name VARCHAR(255) NOT NULL,
2135 UNIQUE(name)
2137 );
2138 CREATE TABLE products
2140 (
2141 id SERIAL NOT NULL PRIMARY KEY,
2142 name VARCHAR(255) NOT NULL,
2143 vendor_id INT REFERENCES vendors (id),
2145 status VARCHAR(128) NOT NULL DEFAULT 'inactive'
2147 CHECK(status IN ('inactive', 'active', 'defunct')),
2148 date_created TIMESTAMP NOT NULL DEFAULT NOW(),
2150 release_date TIMESTAMP,
2151 UNIQUE(name)
2153 );
2154 CREATE TABLE prices
2156 (
2157 id SERIAL NOT NULL PRIMARY KEY,
2158 product_id INT NOT NULL REFERENCES products (id),
2159 region CHAR(2) NOT NULL DEFAULT 'US',
2160 price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
2161 UNIQUE(product_id, region)
2163 );
2164 CREATE TABLE colors
2166 (
2167 id SERIAL NOT NULL PRIMARY KEY,
2168 name VARCHAR(255) NOT NULL,
2169 UNIQUE(name)
2171 );
2172 CREATE TABLE product_color_map
2174 (
2175 product_id INT NOT NULL REFERENCES products (id),
2176 color_id INT NOT NULL REFERENCES colors (id),
2177 PRIMARY KEY(product_id, color_id)
2179 );
2180 Now the Perl classes. Remember that these must each be in their own
2182 ".pm" files, despite appearing in one contiguous code snippet below.
2183 package Vendor;
2185 use base 'My::DB::Object';
2187 __PACKAGE__->meta->setup
2189 (
2190 table => 'vendors',
2191 columns =>
2192 [
2193 id => { type => 'serial', primary_key => 1, not_null => 1 },
2194 name => { type => 'varchar', length => 255, not_null => 1 },
2195 ],
2196 unique_key => 'name',
2198 );
2199 1;
2201 package Product;
2204 use base 'My::DB::Object';
2206 __PACKAGE__->meta->setup
2208 (
2209 table => 'products',
2210 columns =>
2211 [
2212 id => { type => 'integer', not_null => 1 },
2213 name => { type => 'varchar', length => 255, not_null => 1 },
2214 vendor_id => { type => 'int' },
2216 status => { type => 'varchar', default => 'inactive',
2217 length => 128, not_null => 1 },
2218 date_created => { type => 'timestamp', not_null => 1,
2219 default => 'now()' },
2220 release_date => { type => 'timestamp' },
2221 ]
2222 primary_key_columns => 'id',
2224 unique_key => 'name',
2226 allow_inline_column_values => 1,
2228 relationships =>
2230 [
2231 prices => 'one to many',
2232 colors => 'many to many',
2233 ]
2234 );
2235 1;
2237 package Price;
2240 use Product;
2242 use base 'My::DB::Object';
2244 __PACKAGE__->meta->setup
2246 (
2247 table => 'prices',
2248 columns =>
2250 [
2251 id => { type => 'serial', primary_key => 1, not_null => 1 },
2252 product_id => { type => 'int', not_null => 1 },
2253 region => { type => 'char', length => 2, not_null => 1 },
2254 price =>
2255 {
2256 type => 'decimal',
2257 precision => 10,
2258 scale => 2,
2259 not_null => 1,
2260 default => 0.00
2261 },
2262 ],
2263 unique_key => [ 'product_id', 'region' ],
2265 foreign_key => [ 'product' ],
2267 );
2268 1;
2270 package Color;
2273 use base 'My::DB::Object';
2275 __PACKAGE__->meta->setup
2277 (
2278 table => 'colors',
2279 columns =>
2280 [
2281 id => { type => 'serial', primary_key => 1, not_null => 1 },
2282 name => { type => 'varchar', length => 255, not_null => 1 },
2283 ],
2284 unique_key => 'name',
2285 );
2286 1;
2288 package ProductColorMap;
2291 use base 'My::DB::Object';
2293 __PACKAGE__->meta->setup
2295 (
2296 table => 'product_color_map',
2297 columns =>
2298 [
2299 product_id => { type => 'int', not_null => 1 },
2300 color_id => { type => 'int', not_null => 1 },
2301 ],
2302 pk_columns => [ 'product_id', 'color_id' ],
2303 foreign_keys => [ 'product', 'color' ],
2304 );
2305 1;
2307 The loader
2309 If the code above still looks like too much work to you, try letting
2310 Rose::DB::Object::Loader do it all for you. Given the database schema
2311 shown above, the suite of associated Perl classes could have been
2312 created automatically with a single method call.
2313 $loader =
2315 Rose::DB::Object::Loader->new(db => Rose::DB->new,
2316 class_prefix => 'My::');
2317 $loader->make_classes;
2319 If you want to see what the loader did for you, catch the return value
2321 of the make_classes method (which will be a list of class names) and
2322 then ask each class to print its perl equivalent.
2323 @classes = $loader->make_classes;
2325 foreach my $class (@classes)
2327 {
2328 if($class->isa('Rose::DB::Object'))
2329 {
2330 print $class->meta->perl_class_definition(braces => 'bsd',
2331 indent => 2), "\n";
2332 }
2333 else # Rose::DB::Object::Manager subclasses
2334 {
2335 print $class->perl_class_definition, "\n";
2336 }
2337 }
2338 You can also ask the loader to make actual Perl modules (that is, a set
2340 of actual *.pm files in the file system) by calling the aptly named
2341 make_modules method.
2342 The code created by the loader is shown below. Compare it to the
2344 manually created Perl code shown above and you'll see that it's nearly
2345 identical. Again, careful table name choices really help here. Do
2346 what the convention manager expects (or write your own convention
2347 manager subclass that does what you expect) and automation like this
2348 can work very well.
2349 package My::Color;
2351 use strict;
2353 use base qw(My::DB::Object::Base1);
2355 __PACKAGE__->meta->setup
2357 (
2358 table => 'colors',
2359 columns =>
2361 [
2362 id => { type => 'integer', not_null => 1 },
2363 name => { type => 'varchar', length => 255, not_null => 1 },
2364 ],
2365 primary_key_columns => [ 'id' ],
2367 unique_keys => [ 'name' ],
2369 relationships =>
2371 [
2372 products =>
2373 {
2374 column_map => { color_id => 'id' },
2375 foreign_class => 'My::Product',
2376 map_class => 'My::ProductColorMap',
2377 map_from => 'color',
2378 map_to => 'product',
2379 type => 'many to many',
2380 },
2381 ],
2382 );
2383 1;
2385 package My::Color::Manager;
2387 use base qw(Rose::DB::Object::Manager);
2389 use My::Color;
2391 sub object_class { 'My::Color' }
2393 __PACKAGE__->make_manager_methods('colors');
2395 1;
2397 package My::Price;
2399 use strict;
2401 use base qw(My::DB::Object::Base1);
2403 __PACKAGE__->meta->setup
2405 (
2406 table => 'prices',
2407 columns =>
2409 [
2410 id => { type => 'integer', not_null => 1 },
2411 product_id => { type => 'integer', not_null => 1 },
2412 region => { type => 'character', default => 'US', length => 2,
2413 not_null => 1 },
2414 price => { type => 'numeric', default => '0.00', not_null => 1,
2415 precision => 10, scale => 2 },
2416 ],
2417 primary_key_columns => [ 'id' ],
2419 unique_key => [ 'product_id', 'region' ],
2421 foreign_keys =>
2423 [
2424 product =>
2425 {
2426 class => 'My::Product',
2427 key_columns =>
2428 {
2429 product_id => 'id',
2430 },
2431 },
2432 ],
2433 );
2434 1;
2436 package My::Price::Manager;
2438 use base qw(Rose::DB::Object::Manager);
2440 use My::Price;
2442 sub object_class { 'My::Price' }
2444 __PACKAGE__->make_manager_methods('prices');
2446 1;
2448 package My::ProductColorMap;
2450 use strict;
2452 use base qw(My::DB::Object::Base1);
2454 __PACKAGE__->meta->setup
2456 (
2457 table => 'product_color_map',
2458 columns =>
2460 [
2461 product_id => { type => 'integer', not_null => 1 },
2462 color_id => { type => 'integer', not_null => 1 },
2463 ],
2464 primary_key_columns => [ 'product_id', 'color_id' ],
2466 foreign_keys =>
2468 [
2469 color =>
2470 {
2471 class => 'My::Color',
2472 key_columns =>
2473 {
2474 color_id => 'id',
2475 },
2476 },
2477 product =>
2479 {
2480 class => 'My::Product',
2481 key_columns =>
2482 {
2483 product_id => 'id',
2484 },
2485 },
2486 ],
2487 );
2488 1;
2490 package My::ProductColorMap::Manager;
2492 use base qw(Rose::DB::Object::Manager);
2494 use My::ProductColorMap;
2496 sub object_class { 'My::ProductColorMap' }
2498 __PACKAGE__->make_manager_methods('product_color_map');
2500 1;
2502 package My::ProductColor;
2504 use strict;
2506 use base qw(My::DB::Object::Base1);
2508 __PACKAGE__->meta->setup
2510 (
2511 table => 'product_colors',
2512 columns =>
2514 [
2515 id => { type => 'integer', not_null => 1 },
2516 product_id => { type => 'integer', not_null => 1 },
2517 color_code => { type => 'character', length => 3, not_null => 1 },
2518 ],
2519 primary_key_columns => [ 'id' ],
2521 );
2522 1;
2524 package My::ProductColor::Manager;
2526 use base qw(Rose::DB::Object::Manager);
2528 use My::ProductColor;
2530 sub object_class { 'My::ProductColor' }
2532 __PACKAGE__->make_manager_methods('product_colors');
2534 1;
2536 package My::Product;
2538 use strict;
2540 use base qw(My::DB::Object::Base1);
2542 __PACKAGE__->meta->setup
2544 (
2545 table => 'products',
2546 columns =>
2548 [
2549 id => { type => 'integer', not_null => 1 },
2550 name => { type => 'varchar', length => 255, not_null => 1 },
2551 price => { type => 'numeric', default => '0.00', not_null => 1,
2552 precision => 10, scale => 2 },
2553 vendor_id => { type => 'integer' },
2554 status => { type => 'varchar', default => 'inactive',
2555 length => 128, not_null => 1 },
2556 date_created => { type => 'timestamp', default => 'now()',
2557 not_null => 1 },
2558 release_date => { type => 'timestamp' },
2559 ],
2560 primary_key_columns => [ 'id' ],
2562 unique_keys => [ 'name' ],
2564 allow_inline_column_values => 1,
2566 foreign_keys =>
2568 [
2569 vendor =>
2570 {
2571 class => 'My::Vendor',
2572 key_columns =>
2573 {
2574 vendor_id => 'id',
2575 },
2576 },
2577 ],
2578 relationships =>
2580 [
2581 colors =>
2582 {
2583 column_map => { product_id => 'id' },
2584 foreign_class => 'My::Color',
2585 map_class => 'My::ProductColorMap',
2586 map_from => 'product',
2587 map_to => 'color',
2588 type => 'many to many',
2589 },
2590 prices =>
2592 {
2593 class => 'My::Price',
2594 key_columns => { id => 'product_id' },
2595 type => 'one to many',
2596 },
2597 ],
2598 );
2599 1;
2601 package My::Product::Manager;
2603 use base qw(Rose::DB::Object::Manager);
2605 use My::Product;
2607 sub object_class { 'My::Product' }
2609 __PACKAGE__->make_manager_methods('products');
2611 1;
2613 package My::Vendor;
2615 use strict;
2617 use base qw(My::DB::Object::Base1);
2619 __PACKAGE__->meta->setup
2621 (
2622 table => 'vendors',
2623 columns =>
2625 [
2626 id => { type => 'integer', not_null => 1 },
2627 name => { type => 'varchar', length => 255, not_null => 1 },
2628 ],
2629 primary_key_columns => [ 'id' ],
2631 unique_keys => [ 'name' ],
2633 relationships =>
2635 [
2636 products =>
2637 {
2638 class => 'My::Product',
2639 key_columns => { id => 'vendor_id' },
2640 type => 'one to many',
2641 },
2642 ],
2643 );
2644 1;
2646 package My::Vendor::Manager;
2648 use base qw(Rose::DB::Object::Manager);
2650 use My::Vendor;
2652 sub object_class { 'My::Vendor' }
2654 __PACKAGE__->make_manager_methods('vendors');
2656 1;
2658 Auto-joins and other Manager features
2660 The "Product::Manager" class we created earlier is deceptively simple.
2661 Setting it up can actually be reduced to a one-liner, but it provides a
2662 rich set of features.
2663 The basics demonstrated earlier cover most kinds of single-table SELECT
2665 statements. But as the "Product" class has become more complex,
2666 linking to other objects via foreign keys and other relationships,
2667 selecting rows from just the "products" table has become a lot less
2668 appealing. What good is it to retrieve hundreds of products in a
2669 single query when you then have to execute hundreds of individual
2670 queries to get the prices of those products?
2671 This is what SQL JOINs were made for: selecting related rows from
2673 multiple tables simultaneously. Rose::DB::Object::Manager supports a
2674 two kinds of joins. The interface to this functionality is presented
2675 in terms of objects via the "require_objects" and "with_objects"
2676 parameters to the get_objects() method.
2677 Both parameters expect a list of foreign key or relationship names.
2679 The "require_objects" parameters will use an "inner join" to fetch
2680 related objects, while the "with_objects" parameter will perform an
2681 "outer join."
2682 If you're unfamiliar with these terms, it's probably a good idea to
2684 learn about them from a good SQL book or web tutorial. But even if
2685 you've never written an SQL JOIN by hand, there's not much you need to
2686 understand in order to use your manager class effectively.
2687 The rule of thumb is simple. When you want each and every object
2689 returned by your query to have a particular related object, then use
2690 the "require_objects" parameter. But if you do not want to exclude
2691 objects even if they do not have a particular related object attached
2692 to them yet, then use the "with_objects" parameter.
2693 Sometimes, this decision is already made for you by the table
2695 structure. For example, let's modify the "products" table in order to
2696 require that every single product has a vendor. To do so, we'll change
2697 the "vendor_id" column definition from this:
2698 vendor_id INT REFERENCES vendors (id)
2700 to this:
2702 vendor_id INT NOT NULL REFERENCES vendors (id)
2704 Now it's impossible for a product to have a NULL "vendor_id". And
2706 since our database enforces referential integrity, it's also impossible
2707 for the "vendor_id" column to have a value that does not refer to the
2708 "id" of an existing row in the "vendors" table.
2709 While the "with_objects" parameter could technically be used to fetch
2711 "Product"s with their associated "Vendor" objects, it would be
2712 wasteful. (Outer joins are often less efficient than inner joins.)
2713 The table structure basically dictates that the "require_objects"
2714 parameter be used when fetching "Product"s with their "Vendor"s.
2715 Here's how such a query could actually look.
2717 $products =
2719 Product::Manager->get_products(
2720 query =>
2721 [
2722 name => { like => 'Kite%' },
2723 id => { gt => 15 },
2724 ]
2725 require_objects => [ 'vendor' ],
2726 sort_by => 'name');
2727 Recall that the name of the foreign key that connects a product to its
2729 vendor is "vendor". Thus, the value of the "require_objects" parameter
2730 is a reference to an array containing this name.
2731 Getting information about each product's vendor now no longer requires
2733 additional database queries.
2734 foreach my $product (@$products)
2736 {
2737 # This does not hit the database at all
2738 print $product->vendor->name, "\n";
2739 }
2740 For the SQL-inclined, the actual query run looks something like this.
2742 SELECT
2744 t1.date_created,
2745 t1.id,
2746 t1.name,
2747 t1.release_date,
2748 t1.status,
2749 t1.vendor_id,
2750 t2.id,
2751 t2.name
2752 FROM
2753 products t1,
2754 vendors t2
2755 WHERE
2756 t1.id >= 16 AND
2757 t1.name LIKE 'Kite%' AND
2758 t1.vendor_id = t2.id
2759 ORDER BY t1.name
2760 As you can see, the query includes "tN" aliases for each table. This
2762 is important because columns in separate tables often have identical
2763 names. For example, both the "products" and the "vendors" tables have
2764 columns named "id" and "name".
2765 In the query, you'll notice that the "name => { like => 'Kite%' }"
2767 argument ended up filtering on the product name rather than the vendor
2768 name. This is intentional. Any unqualified column name that is
2769 ambiguous is considered to belong to the "primary" table ("products",
2770 in this case).
2771 The "tN" numbering is deterministic. The primary table is always "t1",
2773 and secondary tables are assigned ascending numbers starting from
2774 there. You can find a full explanation of the numbering rules in the
2775 Rose::DB::Object::Manager documentation.
2776 In the example above, if we wanted to filter and sort on the vendor
2778 name instead, we could do this.
2779 $products =
2781 Product::Manager->get_products(
2782 query =>
2783 [
2784 't2.name' => { like => 'Acm%' },
2785 id => { gt => 15 },
2786 ]
2787 require_objects => [ 'vendor' ],
2788 sort_by => 't2.name');
2789 But that's not the only option. There are several ways to disambiguate
2791 a query clause. The column name can also be qualified by prefixing it
2792 with a relationship name.
2793 $products =
2795 Product::Manager->get_products(
2796 query =>
2797 [
2798 'vendor.name' => { like => 'Acm%' },
2799 id => { gt => 15 },
2800 ]
2801 require_objects => [ 'vendor' ],
2802 sort_by => 'vendor.name');
2803 The actual table name itself can also be used (although I do not
2805 recommend this practice since you will have to change all such usage
2806 instances if you ever rename the table).
2807 $products =
2809 Product::Manager->get_products(
2810 query =>
2811 [
2812 'vendors.name' => { like => 'Acm%' },
2813 id => { gt => 15 },
2814 ]
2815 require_objects => [ 'vendor' ],
2816 sort_by => 'vendors.name');
2817 Now let's see an example of the "with_objects" parameter in action.
2819 Each "Product" has zero or more "Price"s. Let's fetch products with
2820 all their associated prices. And remember that some of these products
2821 may have no prices at all.
2822 $products =
2824 Product::Manager->get_products(
2825 query =>
2826 [
2827 name => { like => 'Kite%' },
2828 id => { gt => 15 },
2829 ],
2830 with_objects => [ 'prices' ],
2831 sort_by => 'name');
2832 Again, since the name of the "one to many" relationship that connects a
2834 product to its prices is "prices", this is the value use in the
2835 "with_objects" parameter. The SQL looks something like this:
2836 SELECT
2838 t1.date_created,
2839 t1.id,
2840 t1.name,
2841 t1.release_date,
2842 t1.status,
2843 t1.vendor_id,
2844 t2.id,
2845 t2.price,
2846 t2.product_id,
2847 t2.region
2848 FROM
2849 products t1
2850 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id)
2851 WHERE
2852 t1.id > 15 AND
2853 t1.name LIKE 'Kite%'
2854 ORDER BY t1.name
2855 Fetching products with both their vendors and prices (if any) is
2857 straightforward. Just use the "require_objects" parameter for the
2858 vendors and the "with_objects" parameter for the prices.
2859 $products =
2861 Product::Manager->get_products(
2862 query =>
2863 [
2864 name => { like => 'Kite%' },
2865 id => { gt => 15 },
2866 ],
2867 require_objects => [ 'vendor' ],
2868 with_objects => [ 'prices' ],
2869 sort_by => 'name');
2870 The resulting SQL is what you'd expect.
2872 SELECT
2874 t1.date_created,
2875 t1.id,
2876 t1.name,
2877 t1.release_date,
2878 t1.status,
2879 t1.vendor_id,
2880 t2.id,
2881 t2.price,
2882 t2.product_id,
2883 t2.region,
2884 t3.id,
2885 t3.name
2886 FROM
2887 products t1
2888 JOIN vendors t3 ON (t1.vendor_id = t3.id)
2889 LEFT OUTER JOIN prices t2 ON(t1.id = t2.product_id)
2890 WHERE
2891 t1.id > 15 AND
2892 t1.name LIKE 'Kite%'
2893 ORDER BY t1.name
2894 Each "Product" also has zero or more "Color"s which are related to it
2896 through a mapping table (fronted by the "ProductColorMap" class, but we
2897 don't need to know that). The "with_objects" parameter can handle that
2898 as well.
2899 $products =
2901 Product::Manager->get_products(
2902 query =>
2903 [
2904 name => { like => 'Kite%' },
2905 id => { gt => 15 },
2906 ],
2907 with_objects => [ 'colors' ],
2908 sort_by => 'name');
2909 The resulting SQL is a bit more complex.
2911 SELECT
2913 t1.date_created,
2914 t1.id,
2915 t1.name,
2916 t1.release_date,
2917 t1.status,
2918 t1.vendor_id,
2919 t3.id,
2920 t3.name
2921 FROM
2922 products t1
2923 LEFT OUTER JOIN product_color_map t2 ON(t2.product_id = t1.id)
2924 LEFT OUTER JOIN colors t3 ON(t2.color_id = t3.id)
2925 WHERE
2926 t1.id > 15 AND
2927 t1.name LIKE 'Kite%'
2928 Again, combinations are straightforward. Let's fetch products with
2930 their vendors and colors.
2931 $products =
2933 Product::Manager->get_products(
2934 query =>
2935 [
2936 name => { like => 'Kite%' },
2937 id => { gt => 15 },
2938 ],
2939 require_objects => [ 'vendor' ],
2940 with_objects => [ 'colors' ],
2941 sort_by => 'name');
2942 Now the SQL is starting to get a bit hairy.
2944 SELECT
2946 t1.id,
2947 t1.name,
2948 t1.vendor_id,
2949 t3.code,
2950 t3.name,
2951 t4.id,
2952 t4.name,
2953 t4.region_id
2954 FROM
2955 products t1
2956 JOIN vendors t4 ON (t1.vendor_id = t4.id)
2957 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
2958 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
2959 WHERE
2960 t1.id > 15 AND
2961 t1.name LIKE 'Kite%'
2962 Anyone who knows SQL well will recognize that there is a danger lurking
2964 when combining JOINs. Multiple joins that each fetch multiple rows can
2965 result in a geometric explosion of rows returned by the database. For
2966 example, the number of rows returned when fetching products with their
2967 associated prices and colors would be:
2968 <number of matching products> x
2970 <number of prices for each product> x
2971 <number of colors for each product>
2972 That number can get very large, very fast if products have many prices,
2974 colors, or both. (The last two terms in the multiplication maybe
2975 switched, depending on the order of the actual JOIN clauses, but the
2976 results are similar.) And the problem only gets worse as the number of
2977 objects related by "... to many" relationships increases.
2978 That said, Rose::DB::Object::Manager does allow multiple objects
2980 related by "... to many" relationships to be fetched simultaneously.
2981 But it requires the developer to supply the "multi_many_ok" parameter
2982 with a true value as a form of confirmation. "Yes, I know the risks,
2983 but I want to do it anyway."
2984 As an example, let's try fetching products with their associated
2986 prices, colors, and vendors. To do so, we'll have to include the
2987 "multi_many_ok" parameter.
2988 $products =
2990 Product::Manager->get_products(
2991 query =>
2992 [
2993 name => { like => 'Kite%' },
2994 id => { gt => 15 },
2995 ],
2996 require_objects => [ 'vendor' ],
2997 with_objects => [ 'colors', 'prices' ],
2998 multi_many_ok => 1,
2999 sort_by => 'name');
3000 Here's the SQL.
3002 SELECT
3004 t1.id,
3005 t1.name,
3006 t1.vendor_id,
3007 t3.code,
3008 t3.name,
3009 t4.price_id,
3010 t4.product_id,
3011 t4.region,
3012 t4.price,
3013 t5.id,
3014 t5.name,
3015 t5.region_id
3016 FROM
3017 products t1
3018 JOIN vendors t5 ON (t1.vendor_id = t5.id)
3019 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
3020 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
3021 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id)
3022 WHERE
3023 t1.id > 15 AND
3024 t1.name LIKE 'Kite%'
3025 ORDER BY t1.name
3026 It's questionable whether this five-way join will be faster than doing
3028 a four- or three-way join and then fetching the other information after
3029 the fact, with separate queries. It all depends on the number of rows
3030 expected to match. Only you know your data. You must choose the most
3031 efficient query that suits your needs.
3032 Moving beyond even the example above, it's possible to chain foreign
3034 key or relationship names to an arbitrary depth. For example, imagine
3035 that each "Vendor" has a "Region" related to it by a foreign key named
3036 "region". The following call will get region information for each
3037 product's vendor, filtering on the region name.
3038 $products =
3040 Product::Manager->get_products(
3041 query =>
3042 [
3043 'vendor.region.name' => 'UK',
3044 'name' => { like => 'Kite%' },
3045 'id' => { gt => 15 },
3046 ],
3047 require_objects => [ 'vendor.region' ],
3048 with_objects => [ 'colors', 'prices' ],
3049 multi_many_ok => 1,
3050 sort_by => 'name');
3051 The SQL would now look something like this.
3053 SELECT
3055 t1.id,
3056 t1.name,
3057 t1.vendor_id,
3058 t3.code,
3059 t3.name,
3060 t4.price_id,
3061 t4.product_id,
3062 t4.region,
3063 t4.price,
3064 t5.id,
3065 t5.name,
3066 t5.region_id,
3067 t6.id,
3068 t6.name
3069 FROM
3070 products t1
3071 JOIN (vendors t5 JOIN regions t6 ON (t5.region_id = t6.id))
3072 ON (t1.vendor_id = t5.id)
3073 LEFT OUTER JOIN product_colors t2 ON (t2.product_id = t1.id)
3074 LEFT OUTER JOIN colors t3 ON (t2.color_code = t3.code)
3075 LEFT OUTER JOIN prices t4 ON (t1.id = t4.product_id)
3076 WHERE
3077 t1.id > 15 AND
3078 t1.name LIKE 'Kite%' AND
3079 t6.name = 'UK'
3080 ORDER BY t1.name
3081 The same caveat about performance and the potential explosion of
3083 redundant data when JOINing across multiple "... to many" relationships
3084 also applies to the "chained" selectors demonstrated above--even more
3085 so, in fact, as the depth of the chain increases. That said, it's
3086 usually safe to go a few levels deep into "... to one" relationships
3087 when using the "require_objects" parameter.
3088 Finally, it's also possible to load a single product with all of its
3090 associated foreign objects. The load() method accepts a "with"
3091 parameter that takes a list of foreign key and relationship names.
3092 $product = Product->new(id => 1234);
3094 $product->load(with => [ 'vendor', 'colors', 'prices' ]);
3095 The same "multi many" caveats apply, but the "multi_many_ok" parameter
3097 is not required in this case. The assumption is that a single object
3098 won't have too many related objects. But again, only you know your
3099 data, so be careful.
3100 Wrap-up
3102 I hope you've learned something from this tutorial. Although it is by
3103 no means a complete tour of all of the features of Rose::DB::Object, it
3104 does hit most of the highlights. This tutorial will likely expand in
3105 the future, and a separate document describing the various ways that
3106 Rose::DB::Object can be extended is also planned. For now, there is a
3107 brief overview that was pulled from the Rose::DB::Object mailing list
3108 in the wiki.
3109 https://github.com/siracusa/rose/wiki/Extending-Rose%3A%3ADB%3A%3AObject
3111 See the support section below for more information on the mailing list.
3113
3115 The Rose development policy applies to this, and all "Rose::*" modules.
3116 Please install Rose from CPAN and then run ""perldoc Rose"" for more
3117 information.
3118
3120 Any Rose::DB::Object questions or problems can be posted to the
3121 Rose::DB::Object mailing list. To subscribe to the list or view the
3122 archives, go here:
3123 <http://groups.google.com/group/rose-db-object>
3125 Although the mailing list is the preferred support mechanism, you can
3127 also email the author (see below) or file bugs using the CPAN bug
3128 tracking system:
3129 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object>
3131 There's also a wiki and other resources linked from the Rose project
3133 home page:
3134 <http://rosecode.org>
3136
3138 John C. Siracusa (siracusa@gmail.com)
3139
3141 Copyright (c) 2007 by John C. Siracusa. All rights reserved. This
3142 program is free software; you can redistribute it and/or modify it
3143 under the same terms as Perl itself.
3144perl v5.32.0 2020-07-28 Rose::DB::Object::Tutorial(3)