NAME¶

SYNOPSIS¶

  ## For an informal overview of Rose::DB::Object, please
  ## see the Rose::DB::Object::Tutorial documentation.  The
  ## reference documentation follows.
  ## 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, see the Rose::DB::Tutorial documentation.
  ##
  ## Create classes - two possible approaches:
  ##
  #
  # 1. Automatic configuration
  #
  package Category;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'categories',
    auto  => 1,
  );
  ...
  package Price;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'prices',
    auto  => 1,
  );
  ...
  package Product;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'products',
    auto  => 1,
  );
  #
  # 2. Manual configuration
  #
  package Category;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'categories',
    columns =>
    [
      id          => { type => 'int', primary_key => 1 },
      name        => { type => 'varchar', length => 255 },
      description => { type => 'text' },
    ],
    unique_key => 'name',
  );
  ...
  package Price;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'prices',
    columns =>
    [
      id         => { type => 'int', primary_key => 1 },
      price      => { type => 'decimal' },
      region     => { type => 'char', length => 3 },
      product_id => { type => 'int' }
    ],
    unique_key => [ 'product_id', 'region' ],
  );
  ...
  package Product;
  use base qw(Rose::DB::Object);
  __PACKAGE__->meta->setup
  (
    table => 'products',
    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' },
    ],
    unique_key => 'name',
    foreign_keys =>
    [
      category =>
      {
        class       => 'Category',
        key_columns => { category_id => 'id' },
      },
    ],
    relationships =>
    [
      prices =>
      {
        type       => 'one to many',
        class      => 'Price',
        column_map => { id => 'product_id' },
      },
    ],
  );
  ...
  #
  # 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¶

Restrictions¶

•

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.

Features¶

•

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.

•

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.

Configuration¶

Serial and Auto-Incremented Columns¶

    package My::DB::Object;
    ...
    __PACKAGE__->meta->setup
    (
      columns =>
      [
        id => { type => 'serial', primary_key => 1, not_null => 1 },
        ...
      ],
      ...
    );

    $o = My::DB::Object->new(name => 'bud'); # no id specified
    $o->save; # new id value generated here
    print "Generated new id value: ", $o->id;

•

PostgreSQL

 

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

•

MySQL

 

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

•

SQLite

 

    CREATE TABLE mytable
    (
      id   INTEGER PRIMARY KEY AUTOINCREMENT,
      ...
    );
    

•

Informix

 

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

•

Oracle

 

Since Oracle does not natively support a serial or auto-incremented column data type, an explicit sequence and trigger must be created to simulate the behavior. The sequence should be named according to this convention: "<table>_<column>_seq". For example, if the table is named "mytable" and the column is named "id", then the sequence should be named "mytable_id_seq". Here's an example database setup.

 

    CREATE TABLE mytable
    (
      id   INT NOT NULL PRIMARY KEY,
      ...
    );
    CREATE SEQUENCE mytable_id_seq;
    CREATE TRIGGER mytable_insert BEFORE INSERT ON mytable
    FOR EACH ROW
    BEGIN
        SELECT NVL(:new.id, mytable_id_seq.nextval) 
          INTO :new.id FROM dual;
    END;
    

 

Note the use of "NVL()" in the trigger, which allows the value of the "id" column to be set explicitly. If a non-NULL value for the "id" column is provided, then a new value is not pulled from the sequence.

 

If the sequence is not named according to the "<table>_<column>_seq" convention, you can specify the sequence name explicitly in the column metadata. Example:

 

    columns =>
    [
      id => { type => 'serial', primary_key => 1, not_null => 1,
              sequence => 'some_other_seq' },
      ...
    

    package MyDBObject;
    use base qw(Rose::DB::Object);
    __PACKAGE__->meta->setup
    (
      table => 'mytable',
      columns =>
      [
        k1   => { type => 'int', not_null => 1 },
        k2   => { type => 'int', not_null => 1 },
        name => { type => 'varchar', length => 255 },
        ...
      ],
      primary_key_columns => [ 'k1', 'k2' ],
      primary_key_generator => sub
      {
        my($meta, $db) = @_;
        # Generate primary key values somehow
        my $k1 = ...;
        my $k2 = ...;
        return $k1, $k2;
      },
    );

Inheritance¶

  package BaseClass;
  use base 'Rose::DB::Object';
  __PACKAGE__->meta->setup
  (
    table => 'objects',
    columns =>
    [
      id    => { type => 'int', primary_key => 1 },
      start => { type => 'scalar' },
    ],
  );
  ...
  package SubClass;
  use base 'BaseClass';
  # Set a default value for this column.
  __PACKAGE__->meta->column('id')->default(123);
  # Change the "start" column into a datetime column.
  __PACKAGE__->meta->replace_column(start => { type => 'datetime' });
  # Initialize, replacing any inherited methods with newly created ones
  __PACKAGE__->meta->initialize(replace_existing => 1);
  ...
  $b = BaseClass->new;
  $id = $b->id; # undef
  $b->start('1/2/2003');
  print $b->start; # '1/2/2003' (plain string)
  $s = SubClass->new;
  $id = $s->id; # 123
  $b->start('1/2/2003'); # Value is converted to a DateTime object
  print $b->start->strftime('%B'); # 'January'

  package SubClass;
  use base 'BaseClass';
  __PACKAGE__->meta->initialize(preserve_existing => 1);

Error Handling¶

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¶

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. Note: This method must be callable as both an object method and a class method.

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. Note: This method must be callable as both an object method and a class method.

dbh [DBH]

Get or set 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:

error

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

insert [PARAMS]

Insert the current object to the database table. This method should only be used when you're absolutely sure that you want to force the current object to be inserted, rather than updated. It is recommended that you use the save method instead of this one in most circumstances. The save method will "do the right thing," executing an insert or update as appropriate for the current situation.

 

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

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.

 

When loading based on a unique key, unique keys are considered in the order in which they were defined in the metadata for this class. If the object has defined values for every column in a unique key, then that key is used. If no such key is found, then the first key for which the object has at least one defined value is used.

 

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

    # This is the CORRECT way to override load() while still
    # calling the base class version of the method.
    sub load
    {
      my $self = $_[0]; # Copy, not shift
      ... # Do your stuff
      shift->SUPER::load(@_); # Call superclass
    }

    # 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 the object is now lost!
      ... # Do your stuff
      $self->SUPER::load(@_); # This won't work right!
    }

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 inserted. PARAMS are name/value pairs. Valid PARAMS are listed below.

 

Actions associated with sub-objects that were added or deleted using one of the "*_on_save" relationship or foreign key method types are also performed when this method is called. If there are any such actions to perform, a new transaction is started if the db is not already in one, and rollback() is called if any of the actions fail during the save(). Example:

 

    $product = Product->new(name => 'Sled');
    $vendor  = Vendor->new(name => 'Acme');  
    $product->vendor($vendor);
    # Product and vendor records created and linked together,
    # all within a single transaction.
    $product->save;
    

 

See the "making methods" sections of the Rose::DB::Object::Metadata::Relationship and Rose::DB::Object::Metadata::ForeignKey documentation for a description of the "method map" associated with each relationship and foreign key. Only the actions initiated through one of the "*_on_save" method types are handled when save() is called. See the documentation for each individual "*_on_save" method type for more specific information.

 

Valid parameters to save() are:

update [PARAMS]

Update the current object in the database table. This method should only be used when you're absolutely sure that you want to force the current object to be updated, rather than inserted. It is recommended that you use the save method instead of this one in most circumstances. The save method will "do the right thing," executing an insert or update as appropriate for the current situation.

 

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

RESERVED METHODS¶

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

DEVELOPMENT POLICY¶

SUPPORT¶

    perldoc Rose::DB::Object::Tutorial

CONTRIBUTORS¶

AUTHOR¶

LICENSE¶