view release on metacpan or  search on metacpan

lib/Rose/DB/Object/Tutorial.pod  view on Meta::CPAN

=item * A list or reference to an array of primary key values (but only if the foreign table has a single-column primary key).

=back

Setting a new list of prices will delete all the old prices.  As with foreign keys, any actual database modification happens when the parent object is saved.  Here are some examples.

    $p = Product->new(name => 'Kite');
    $p->prices({ price => 1.23, region => 'US' },
               { price => 4.56, region => 'UK' });

    $p->save; # database is modified here

    # US: 1.23, UK: 4.56
    print join(', ', map { $_->region . ': ' . $_->price } $p->prices);

New prices can be added without deleting and resetting the entire list:

    # Add two prices to the existing list
    $p->add_prices({ price => 7.89, region => 'DE' },
                   { price => 1.11, region => 'JP' });

    $p->save; # database is modified here

Passing a reference to an empty array will cause all the prices to be deleted:

    $p->prices([]); # delete all prices associated with this product
    $p->save;       # database is modified here

=head3 Cascading delete

Deleting a product now becomes slightly more interesting.  The naive approach fails.

    $p->delete; # Fatal error!

    # DBD::Pg::st execute failed: ERROR:  update or delete on "products"
    # violates foreign key constraint "prices_product_id_fkey" on
    # "prices"
    # DETAIL:  Key (id)=(12345) is still referenced from table "prices".

Since rows in the C<prices> table now link to rows in the C<products> table, a product cannot be deleted until all of the prices that refer to it are also deleted.  There are a few ways to deal with this.

The best solution is to add a trigger to the C<products> table itself in the database that makes sure to delete any associated prices before deleting a product.  This change will allow the naive approach shown above to work correctly.

A less robust solution is necessary if your database does not support triggers.  One such solution is to manually delete the prices before deleting the product.  This can be done in several ways.  The prices can be deleted directly, like this.

    foreach my $price ($p->prices)
    {
      $price->delete; # Delete all associated prices
    }

    $p->delete; # Now it's safe to delete the product

The list of prices for the product can also be set to an empty list, which will have the effect of deleting all associated prices when the product is saved.

    $p->prices([]);
    $p->save;   # All associated prices deleted here
    $p->delete; # Now it's safe to delete the product

Finally, the L<delete()|Rose::DB::Object/delete> method can actually automate this process, and do it all inside a transaction as well.

    $p->delete(cascade => 1); # Delete all associated rows too

Again, the recommended approach is to use triggers inside the database itself.  But if necessary, these other approaches will work too.

=head3 Many-to-many relationships

The final relationship type is the most complex.  In a "many to many" relationship, a single row in table A may be related to multiple rows in table B, while a single row in table B may also be related to multiple rows in table A.  (Confused?  A conc...

This kind of relationship involves three tables instead of just two.  The "local" and "foreign" tables, familiar from the other relationship types described above, still exist, but now there's a third table that connects rows from those two tables.  ...

Let's add such a relationship to our growing family of classes.  Imagine that each product may come in several colors.    Right away, both the "one to one" and "many to one" relationship types are eliminated since they can only provide a single color...

But wait, isn't a "one to many" relationship suitable?  After all, one product may have many colors.  Unfortunately, such a relationship is wasteful in this case.  Let's see why.  Imagine a C<colors> table like this.

    CREATE TABLE colors
    (
      id            SERIAL NOT NULL PRIMARY KEY,
      name          VARCHAR(255) NOT NULL,
      product_id    INT NOT NULL REFERENCES products (id)
    );

Here's a simple C<Color> class to front it.

    package Color;  

    use base 'My::DB::Object';

    __PACKAGE__->meta->setup
    (
      table => 'colors',
      columns =>
      [
        id   => { type => 'serial', primary_key => 1, not_null => 1 },
        name => { type => 'varchar', length => 255, not_null => 1 },
        product_id => { type => 'int', not_null => 1 },
      ],

      foreign_keys =>
      [
        product =>
        {
          class       => 'Product',
          key_columns => { product_id => 'id' },
        },
      ],
    );

Finally, let's add the "one to many" relationship to the C<Product> class.

    package Product;

    use base 'My::DB::Object';

    __PACKAGE__->meta->setup
    (
      ...
      relationships =>
      [
        colors =>
        {
          type       => 'one to many',

lib/Rose/DB/Object/Tutorial.pod  view on Meta::CPAN

     product_id | color_id 
    ------------+----------
             10 |        1
             10 |        2
             20 |        3
             20 |        2
             20 |        1

Each color appears only once, and the mapping table handles all the connections between the C<colors> and C<products> tables.

The "many to many" C<colors> method works much like the "one to many" C<prices> method described earlier.  The valid argument formats are the same.

=over 4

=item * A list or reference to an array of objects of the appropriate class.

=item * A list or reference to an array of hash references containing name/value pairs used to construct such objects.

=item * A list or reference to an array of primary key values (but only if the foreign table has a single-column primary key).

=back

The database modification behavior is also the same, with changes happening when the "parent" object is saved.

    $p = Product->new(id => 123)->load;

    $p->colors({ name => 'green' },
               { name => 'blue'  });

    $p->save; # database is modified here

Setting the list of colors replaces the old list, but in the case of a "many to many" relationship, only the map records are deleted.

    $p = Product->new(id => 123)->load;

    $p->colors({ name => 'pink'   },
               { name => 'orange' });

    # Delete old rows in the mapping table and create new ones
    $p->save; 

New colors can be added without deleting and resetting the entire list:

    # Add two colors to the existing list
    $p->add_colors({ name => 'gray' },
                   { name => 'red'  });

    $p->save; # database is modified here

Passing a reference to an empty array will remove all colors associated with a particular product by deleting all the mapping table entries.

    $p->colors([]);
    $p->save; # all mapping table entries for this product deleted here

Finally, the same caveats L<described earlier|/"Cascading delete"> about deleting products that have associated prices apply to colors as well.  Again, I recommend using a trigger in the database to handle this, but L<Rose::DB::Object>'s cascading de...

    # Delete all associated rows in the prices table, plus any
    # rows in the product_color_map table, before deleting the
    # row in the products table.
    $p->delete(cascade => 1);

=head3 Relationship code summary

To summarize this exploration of inter-table relationships, here's a terse summary of the current state of our Perl classes, and the associated database tables.

For the sake of brevity, I've chosen to use the shorter versions of the foreign key and relationship definitions in the Perl classes shown below.  Just remember that this only works when your tables, columns, and classes are named according to the ex...

First, the database schema.

    CREATE TABLE vendors
    (
      id    SERIAL NOT NULL PRIMARY KEY,
      name  VARCHAR(255) NOT NULL,

      UNIQUE(name)
    );

    CREATE TABLE products
    (
      id      SERIAL NOT NULL PRIMARY KEY,
      name    VARCHAR(255) NOT NULL,

      vendor_id  INT REFERENCES vendors (id),

      status  VARCHAR(128) NOT NULL DEFAULT 'inactive' 
                CHECK(status IN ('inactive', 'active', 'defunct')),

      date_created  TIMESTAMP NOT NULL DEFAULT NOW(),
      release_date  TIMESTAMP,

      UNIQUE(name)
    );

    CREATE TABLE prices
    (
      id          SERIAL NOT NULL PRIMARY KEY,
      product_id  INT NOT NULL REFERENCES products (id),
      region      CHAR(2) NOT NULL DEFAULT 'US',
      price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,

      UNIQUE(product_id, region)
    );

    CREATE TABLE colors
    (
      id    SERIAL NOT NULL PRIMARY KEY,
      name  VARCHAR(255) NOT NULL,

      UNIQUE(name)
    );

    CREATE TABLE product_color_map
    (
      product_id  INT NOT NULL REFERENCES products (id),
      color_id    INT NOT NULL REFERENCES colors (id),

      PRIMARY KEY(product_id, color_id)
    );

Now the Perl classes.  Remember that these must each be in their own ".pm" files, despite appearing in one contiguous code snippet below.