NAME

Rose::DB::Object - Extensible, high performance RDBMS-OO mapper.

SYNOPSIS

  ## First, set up your Rose::DB data sources, otherwise you
  ## won't be able to connect to the database at all.  See 
  ## the Rose::DB documentation for more information.  For
  ## a quick start, run: perldoc Rose::DB::Tutorial

  ##
  ## Create classes - two possible approaches:
  ##

  #
  # 1. Automatic configuration
  #

  package Category;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('categories');

  __PACKAGE__->meta->auto_initialize;

  ...

  package Price;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('prices');

  __PACKAGE__->meta->auto_initialize;

  ...

  package Product;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('products');

  __PACKAGE__->meta->auto_initialize;

  #
  # 2. Manual configuration
  #

  package Category;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('categories');

  __PACKAGE__->meta->columns
  (
    id          => { type => 'int', primary_key => 1 },
    name        => { type => 'varchar', length => 255 },
    description => { type => 'text' },
  );

  __PACKAGE__->meta->add_unique_key('name');

  __PACKAGE__->meta->initialize;

  ...

  package Price;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('prices');

  __PACKAGE__->meta->columns
  (
    id         => { type => 'int', primary_key => 1 },
    price      => { type => 'decimal' },
    region     => { type => 'char', length => 3 },
    product_id => { type => 'int' }
  );

  __PACKAGE__->meta->add_unique_key('product_id', 'region');

  __PACKAGE__->meta->initialize;

  ...

  package Product;

  use Rose::DB::Object;
  our @ISA = qw(Rose::DB::Object);

  __PACKAGE__->meta->table('products');

  __PACKAGE__->meta->columns
  (
    id          => { type => 'int', primary_key => 1 },
    name        => { type => 'varchar', length => 255 },
    description => { type => 'text' },
    category_id => { type => 'int' },

    status => 
    {
      type      => 'varchar', 
      check_in  => [ 'active', 'inactive' ],
      default   => 'inactive',
    },

    start_date  => { type => 'datetime' },
    end_date    => { type => 'datetime' },

    date_created     => { type => 'timestamp', default => 'now' },  
    last_modified    => { type => 'timestamp', default => 'now' },
  );

  __PACKAGE__->meta->add_unique_key('name');

  __PACKAGE__->meta->foreign_keys
  (
    category =>
    {
      class       => 'Category',
      key_columns =>
      {
        category_id => 'id',
      }
    },
  );

  # This part cannot be done automatically.
  # perldoc Rose::DB::Object::Metadata to find out why.
  __PACKAGE__->meta->relationships
  (
    prices =>
    {
      type       => 'one to many',
      class      => 'Price',
      column_map => { id => 'product_id' },
    },
  );

  __PACKAGE__->meta->initialize;

  ...

  #
  # Example usage
  #

  $product = Product->new(id          => 123,
                          name        => 'GameCube',
                          status      => 'active',
                          start_date  => '11/5/2001',
                          end_date    => '12/1/2007',
                          category_id => 5);

  $product->save;

  ...

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

  # Load foreign object via "one to one" relationship
  print $product->category->name;

  $product->end_date->add(days => 45);

  $product->save;

  ...

  $product = Product->new(id => 456);
  $product->load;

  # Load foreign objects via "one to many" relationship
  print join ' ', $product->prices;

  ...

DESCRIPTION

Rose::DB::Object is a base class for objects that encapsulate a single row in a database table. Rose::DB::Object-derived objects are sometimes simply called "Rose::DB::Object objects" in this documentation for the sake of brevity, but be assured that derivation is the only reasonable way to use this class.

Rose::DB::Object inherits from, and follows the conventions of, Rose::Object. See the Rose::Object documentation for more information.

Restrictions

Rose::DB::Object objects can represent rows in almost any database table, subject to the following constraints.

  • The database server must be supported by Rose::DB.

  • The database table must have a primary key.

  • The primary key must not allow null values in any of its columns.

Although the list above contains the only hard and fast rules, there may be other realities that you'll need to work around.

The most common example is the existence of a column name in the database table that conflicts with the name of a method in the Rose::DB::Object API. There are two possible workarounds: either explicitly alias the column, or define a mapping function. See the alias_column and column_name_to_method_name_mapper methods in the Rose::DB::Object::Metadata documentation for more details.

There are also varying degrees of support for data types in each database server supported by Rose::DB. If you have a table that uses a data type not supported by an existing Rose::DB::Object::Metadata::Column-derived class, you will have to write your own column class and then map it to a type name using Rose::DB::Object::Metadata's column_type_class method, yada yada. (Or, of course, you can map the new type to an existing column class.)

The entire framework is extensible. This module distribution contains straight-forward implementations of the most common column types, but there's certainly more that can be done. Submissions are welcome.

Features

Rose::DB::Object provides the following functions:

  • Create a row in the database by saving a newly constructed object.

  • Initialize an object by loading a row from the database.

  • Update a row by saving a modified object back to the database.

  • Delete a row from the database.

  • Fetch an object referred to by a foreign key in the current object. (i.e., "one to one" and "many to one" relationships.)

  • Fetch multiple objects that refer to the current object, either directly through foreign keys or indirectly through a mapping table. (i.e., "one to many" and "many to many" relationships.)

  • Load an object along with "foreign objects" that are related through any of the supported relationship types.

Objects can be loaded based on either a primary key or a unique key. Since all tables fronted by Rose::DB::Objects must have non-null primary keys, insert, update, and delete operations are done based on the primary key.

In addition, its sibling class, Rose::DB::Object::Manager, can do the following:

  • Fetch multiple objects from the database using arbitrary query conditions, limits, and offsets.

  • Iterate over a list of objects, fetching from the database in response to each step of the iterator.

  • Fetch objects along with "foreign objects" (related through any of the supported relationship types) in a single query by automatically generating the appropriate SQL join(s).

  • Count the number of objects that match a complex query.

  • Update objects that match a complex query.

  • Delete objects that match a complex query.

Rose::DB::Object::Manager can be subclassed and used separately (the recommended approach), or it can create object manager methods within a Rose::DB::Object subclass. See the Rose::DB::Object::Manager documentation for more information.

Rose::DB::Object can parse, coerce, inflate, and deflate column values on your behalf, providing the most convenient possible data representations on the Perl side of the fence, while allowing the programmer to completely forget about the ugly details of the data formats required by the database. Default implementations are included for most common column types, and the framework is completely extensible.

Configuration

Before Rose::DB::Object can do any useful work, you must register at least one Rose::DB data source. By default, Rose::DB::Object instantiates a Rose::DB object by passing no arguments to its constructor. (See the db method.) If you register a Rose::DB data source using the default type and domain, this will work fine. Otherwise, you must override the meta method in your Rose::DB::Object subclass and have it return the appropriate Rose::DB-derived object.

To define your own Rose::DB::Object-derived class, you must describe the table that your class will act as a front-end for. This is done through the Rose::DB::Object::Metadata object associated with each Rose::DB::Object-dervied class. The metadata object is accessible via Rose::DB::Object's meta method.

Metadata objects can be populated manually or automatically. Both techniques are shown in the synopsis above. The automatic mode works by asking the database itself for the information. There are some caveats to this approach. See the auto-initialization section of the Rose::DB::Object::Metadata documentation for more information.

Error Handling

Error handling for Rose::DB::Object-derived objects is controlled by the error_mode method of the Rose::DB::Object::Metadata object associated with the class (accessible via the meta method). The default setting is "fatal", which means that Rose::DB::Object methods will croak if they encounter an error.

PLEASE NOTE: The error return values described in the object method documentation are only relevant when the error mode is set to something "non-fatal." In other words, if an error occurs, you'll never see any of those return values if the selected error mode dies or croaks or otherwise throws an exception when an error occurs.

CONSTRUCTOR

new PARAMS

Returns a new Rose::DB::Object constructed according to PARAMS, where PARAMS are name/value pairs. Any object method is a valid parameter name.

CLASS METHODS

meta

Returns the Rose::DB::Object::Metadata-derived object associated with this class. This object describes the database table whose rows are fronted by this class: the name of the table, its columns, unique keys, foreign keys, etc.

See the Rose::DB::Object::Metadata documentation for more information.

meta_class

Return the name of the Rose::DB::Object::Metadata-derived class used to store this object's metadata. Subclasses should override this method if they want to use a custom Rose::DB::Object::Metadata subclass. (See the source code for Rose::DB::Object::Std for an example of this.)

OBJECT METHODS

db [DB]

Get or set the Rose::DB object used to access the database that contains the table whose rows are fronted by the Rose::DB::Object-derived class.

If it does not already exist, this object is created with a simple, argument-less call to Rose::DB->new(). To override this default in a subclass, override the init_db method and return the Rose::DB to be used as the new default.

init_db

Returns the Rose::DB-derived object used to access the database in the absence of an explicit db value. The default implementation simply calls Rose::DB->new() with no arguments.

Override this method in your subclass in order to use a different default data source.

dbh

Returns the DBI database handle contained in db.

delete [PARAMS]

Delete the row represented by the current object. The object must have been previously loaded from the database (or must otherwise have a defined primary key value) in order to be deleted. Returns true if the row was deleted or did not exist, false otherwise.

PARAMS are optional name/value pairs. Valid PARAMS are:

cascade TYPE

Also process related rows. TYPE must be "delete", "null", or "1". The value "1" is an alias for "delete". Passing an illegal TYPE value will cause a fatal error.

For each "one to many" relationship, all of the rows in the foreign ("many") table that reference the current object ("one") will be deleted in "delete" mode, or will have the column(s) that reference the current object set to NULL in "null" mode.

For each "many to many" relationship, all of the rows in the "mapping table" that reference the current object will deleted in "delete" mode, or will have the columns that reference the two tables that the mapping table maps between set to NULL in "null" mode.

For each "one to one" relationship or foreign key with a "one to one" relationship type, all of the rows in the foreign table that reference the current object will deleted in "delete" mode, or will have the column(s) that reference the current object set to NULL in "null" mode.

In all modes, if the db is not currently in a transaction (i.e., if AutoCommit is turned off), a new transaction is started. If any part of the cascaded delete fails, the transaction is rolled back.

The cascaded delete feature described above plays it safe by only deleting rows that are not referenced by any other rows (according to the metadata provided by each Rose::DB::Object-derived class). I strongly recommend that you implement "cascaded delete" in the database itself, rather than using this feature. It will undoubtedly be faster and more robust than doing it "client-side." You may also want to cascade only to certain tables, or otherwise deviate from the "safe" plan. If your database supports automatic cascaded delete and/or triggers, please consider using thse features.

error

Returns the text message associated with the last error that occurred.

load [PARAMS]

Load a row from the database table, initializing the object with the values from that row. An object can be loaded based on either a primary key or a unique key.

Returns true if the row was loaded successfully, undef if the row could not be loaded due to an error, or zero (0) if the row does not exist. The true value returned on success will be the object itself. If the object overloads its boolean value such that it is not true, then a true value will be returned instead of the object itself.

PARAMS are optional name/value pairs. Valid PARAMS are:

speculative BOOL

If this parameter is passed with a true value, and if the load failed because the row was not found, then the error_mode setting is ignored and zero (0) is returned.

with OBJECTS

Load the object and the specified "foreign objects" simultaneously. OBJECTS should be a reference to an array of foreign key or relationship names.

SUBCLASS NOTE: If you are going to override the load method in your subclass, you must pass an alias to the actual object as the first argument to the method, rather than passing a copy of the object reference. Example:

    # This is the CORRECT way to override load() while still
    # calling the base class version of the method.
    sub load
    {
      my $self = $_[0]; # Copy, no shift

      ... # Do your stuff

      shift->SUPER::load(@_); # Call superclass
    }

Now here's the wrong way:

    # This is the WRONG way to override load() while still
    # calling the base class version of the method.
    sub load
    {
      my $self = shift; # WRONG!  The alias to original object is now lost!

      ... # Do your stuff

      $self->SUPER::load(@_); # This won't work right!
    }

This requirement exists in order to preserve some sneaky object-replacement optimizations in the base class implementation of load. At some point, those optimizations may change or go away. But if you follow these guidelines, your code will continue to work no matter what.

not_found

Returns true if the previous call to load failed because a row in the database table with the specified primary or unique key did not exist, false otherwise.

meta

Returns the Rose::DB::Object::Metadata object associated with this class. This object describes the database table whose rows are fronted by this class: the name of the table, its columns, unique keys, foreign keys, etc.

See the Rose::DB::Object::Metadata documentation for more information.

save [PARAMS]

Save the current object to the database table. In the absence of PARAMS, if the object was previously loaded from the database, the row will be updated. Otherwise, a new row will be created.

PARAMS are name/value pairs. Valid parameters are:

  • insert

    If set to a true value, then an insert is attempted, regardless of whether or not the object was previously loaded from the database.

  • update

    If set to a true value, then an update is attempted, regardless of whether or not the object was previously loaded from the database.

It is an error to pass both the insert and update parameters in a single call.

Returns true if the row was inserted or updated successfully, false otherwise. The true value returned on success will be the object itself. If the object overloads its boolean value such that it is not true, then a true value will be returned instead of the object itself.

If an insert was performed and the primary key is a single column that supports auto-generated values, then the object accessor for the primary key column will contain the auto-generated value.

Here are examples of primary key column definitions that provide auto-generated values, one for each of the databases supported by Rose::DB.

  • PostgreSQL

        CREATE TABLE mytable
        (
          id   SERIAL PRIMARY KEY,
          ...
        );
  • MySQL

        CREATE TABLE mytable
        (
          id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
          ...
        );
  • Informix

        CREATE TABLE mytable
        (
          id   SERIAL NOT NULL PRIMARY KEY,
          ...
        );

Other data definitions are possible, of course, but the three definitions above are used in the Rose::DB::Object test suite and are therefore guaranteed to work. If you have success with alternative approaches, patches and/or new tests are welcome.

If your table has a multi-column primary key or does not use a column type that supports auto-generated values, you can define a custom primary key generator function using the primary_key_generator method of the Rose::DB::Object::Metadata-derived object that contains the metadata for this class. Example:

    package MyDBObject;

    use Rose::DB::Object;
    our @ISA = qw(Rose::DB::Object);

    __PACKAGE__->meta->table('mytable');

    __PACKAGE__->meta->columns
    (
      k1   => { type => 'int', not_null => 1 },
      k2   => { type => 'int', not_null => 1 },
      name => { type => 'varchar', length => 255 },
      ...
    );

    __PACKAGE__->meta->primary_key_columns('k1', 'k2');

    __PACKAGE__->meta->initialize;

    __PACKAGE__->meta->primary_key_generator(sub
    {
      my($meta, $db) = @_;

      # Generate primary key values somehow
      my $k1 = ...;
      my $k2 = ...;

      return $k1, $k2;
    });

See the Rose::DB::Object::Metadata documentation for more information on custom primary key generators.

RESERVED METHODS

As described in the Rose::DB::Object::Metadata documentation, each column in the database table has an associated get/set accessor method in the Rose::DB::Object. Since the Rose::DB::Object API already defines many methods (load, save, meta, etc.), accessor methods for columns that share the name of an existing method pose a problem. The solution is to alias such columns using Rose::DB::Object::Metadata's alias_column method.

Here is a list of method names reserved by the Rose::DB::Object API. If you have a column with one of these names, you must alias it.

    db
    dbh
    delete
    DESTROY
    error
    init_db
    _init_db
    insert
    load
    meta
    meta_class
    not_found
    save
    update

Note that not all of these methods are public. These methods do not suddenly become public just because you now know their names! Remember the stated policy of the Rose web application framework: if a method is not documented, it does not exist. (And no, the list of method names above does not constitute "documentation")

DEVELOPMENT POLICY

The Rose development policy applies to this, and all Rose::* modules. Please install Rose from CPAN and then run "perldoc Rose" for more information.

SUPPORT

Any Rose::DB::Object questions or problems can be posted to the Rose::DB::Object mailing list. To subscribe to the list or view the archives, go here:

http://lists.sourceforge.net/lists/listinfo/rose-db-object

Although the mailing list is the preferred support mechanism, you can also email the author (see below) or file bugs using the CPAN bug tracking system:

http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object

AUTHOR

John C. Siracusa (siracusa@mindspring.com)

COPYRIGHT

Copyright (c) 2005 by John C. Siracusa. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.