NAME

SYNOPSIS

 use SPOPS::DBI;
 @ISA = qw( SPOPS::DBI );
 ...

DESCRIPTION

• (optional) Methods to sort member objects or perform operations on groups of them at once.
• (optional) Methods to relate an object of this class to objects of other classes -- for instance, to find all users within a group.
• (optional) The initialization method (_class_initialize() ), which should create a config() object stored in the package variable and initialize it with configuration information relevant to the class.
• (optional) Methods to accomplish actions before/after many of the actions implemented here: fetch/save/remove.
• (optional) Methods to accomplish actions before/after saving or removing an object from the cache.

Of course, these methods can also do anything else you like. :-)

As you can see, all the methods are optional. Along with SPOPS::ClassFactory, you can create an entirely virtual class consisting only of configuration information. So you can actually create the implementation for a new object in two steps:

1.

Create the configuration file (or add to the existing one)

2.

Create the database table the class depends on.

Complete!

DATABASE HANDLE

The latter sounds more difficult than it is. And if you have many objects using the same handle it is definitely the way to go. For instance, your database handle class could look like:

 package My::Object::DBI;

 use strict;

 # These next two are optional but enable you to change databases for
 # lots of objects very quickly.

 use SPOPS::DBI;
 use SPOPS::DBI::Pg;
 @My::Object::DBI::ISA = qw( SPOPS::DBI::Pg SPOPS::DBI );

 use constant DBI_DSN  => 'DBI:Pg:dbname=postgres';
 use constant DBI_USER => 'postgres';
 use constant DBI_PASS => 'postgres';

 my ( $DB );

 sub global_datasource_handle {
   unless ( ref $DB ) {
     $DB = DBI->connect( DBI_DSN, DBI_USER, DBI_PASS,
                         { RaiseError => 1, LongReadLen => 65536, LongTruncOk => 0 } )
               || spops_error "Cannot connect! $DBI::errstr";
   }
   return $DB;
 }

 1;

And all your objects can use this method simply by putting the class in their 'isa' configuration key:

 $conf = {
    myobj => {
       isa => [ qw/ My::Object::DBI / ],
    },
 };

Now, your objects will have transparent access to a DBI data source.

CLASS METHODS

Ensures that all fields are not private and are translated through the field map properly. If "\%fields", we return a hashref with no private fields and keys translated to mapped fields. If "\@fields" -- return an arrayref with no private fields and fields translated to mapped fields.

Examples:

 # 'favorite_colour' is mapped to 'favorite_color'
 my @fields = qw( first_name last_name _current_balance favorite_colour );
 my $filtered = $class->filter_fields( \@fields );
 # $filtered = [ 'first_name', 'last_name', 'favorite_color' ]

 my %data = ( first_name       => 'Chris',
              last_name        => 'Winters',
              _current_balance => 100000000000,
              favorite_colour  => 'puce' );
 my $filtered = $class->filter_fields( \%data );
 # $filtered = { first_name       => 'Chris',
 #               last_name        => 'Winters',
 #               favorite_color   => 'puce' }

Returns: arrayref or hashref of filtered fields, depending on the input.

field_update()

This method is documented below in "OBJECT METHODS", but it has a class method aspect to it in addition to the object method.

DATA ACCESS METHODS

base_table (Returns: $)

Just the table name, no owners or db names prepended.

table_name (Returns: $)

Fully-qualified table name

field (Returns: \%)

Hashref of fields/properties (field is key, value is true)

field_list (Returns: \@)

Arrayref of fields/propreties

no_insert (Returns: \%)

Hashref of fields not to insert (field is key, value is true)

no_update (Returns: \%)

Hashref of fields not to update (field is key, value is true)

skip_undef (Returns: \%)

Hashref of fields to skip update/insert if they are undefined (field is key, value is true)

field_alter (Returns: \%)

Hashref of data-formatting instructions that get used with "fetch()" and "fetch_group()" (field is key, instruction is value)

insert_alter (Returns: \%)

Hashref of data-formatting instructions that get used with "save()" on a new object. The field is the key, the value is a sprintf format that should contain one %s sequence into which the actual value of the object will be plugged.

For instance, your database may use a non-standard format for inserting dates. You can specify:

 insert_alter => { last_login =>
                        "to_date('%s','YYYY-MM-DD HH24:MI:SS')" },

So when the object value is set:

 $object->{last_login} = '2002-04-22 14:47:32';

What actually gets put into the database is:

 INSERT INTO table
 ( ... last_login ... )
 VALUES 
 ( ... to_date( '2002-04-22 14:47:32', 'YYYY-MM-DD HH24:MI:SS' ) ... )

Note that the resulting value is passed unquoted to the database.

If you need more complicated processing than this allows, you can override the method "apply_insert_alter( \%params )" in your class.

NOTE: Fieldnames in the 'insert_alter' configuration must be in lower-case, even if the fields in your datasource are mixed- or upper-case.

OBJECT METHODS

Returns a snippet of SQL suitable for identifying this object in the database.

This can be called either from the class or from a particular object. If called from a class, the $id value must be passed in. Examples:

 my $id = 35;
 my $sql = qq/
   DELETE FROM $table
    WHERE @{[ $class->id_clause( $id ) ]}
 /;
 print "SQL: $sql";

 >> SQL:
      DELETE FROM this_table
       WHERE this_table.this_id = 35

 $class->db_select( ... where => $class->id_clause( 15 ), ... )

If the system cannot determine the data type of the id field, it makes a best guess based on the package variable GUESS_ID_FIELD_TYPE. It defaults to SQL_INTEGER (as set by DBI), but you can set it dynamically as well:

 $SPOPS::DBI::GUESS_ID_FIELD_TYPE = SQL_VARCHAR;

Note that the default behavior is to create a fully-qualified ID field. If you do not wish to do this (for instance, if you need to use the ID field for a lookup into a link table), just pass 'noqualify' as the second argument. To use the example from above:

 my $id = 35;
 my $sql = qq/
   DELETE FROM $table
    WHERE @{[ $class->id_clause( $id, 'noqualify' ) ]}
 /;
 print "SQL: $sql";

 >> SQL:
      DELETE FROM this_table
       WHERE this_id = 35

id_field_select( $id, \%params )

Generates a list of fieldnames suitable for passing in the 'select' parameter to SPOPS::SQLInterface.

If you pass a true value for the 'noqualify' parameter, SPOPS will not prefix the fieldnames with the table name.

This method is aware of objects using multiple primary keys.

Returns: list of fields.

fetch( $id, \%params )

Fetches the information for an object of type class from the data store, creates an object with the data and returns the object. Any failures result in either an SPOPS::Exception or an SPOPS::Exception::DBI object being thrown, depending on the source of the error.

If $id is blank or begins with 'tmp', "undef" is returned. Classes using multiple primary key fields need to specify the ID as a string separated by a comma. For instance, if you had a class specified with:

 myclass => {
     class => 'My::Customer',
     id    => [ 'entno', 'custno' ],
     ...
 },

Then you would fetch a customer with:

 my $cust = My::Customer->fetch( "$entno,$custno" );

If you have security turned on for the object class, the system will first check if the currently-configured user is allowed to fetch the object. If the user has less that SEC_LEVEL_READ access, the fetch is denied and a SPOPS::Exception::Security object thrown.

Note that if the fetch is successful we store the access level of this object within the object itself. Check the temporary property {tmp_security_level} of any object and you will find it.

Parameters:

• column_group ($) (optional)

  Name a group of columns you want to fetch. Only the values for these columns will be retrieved, and an arrayref of

• field_alter (\%) (optional)

  fields are keys, values are database-dependent formatting strings. You can accomplish different types of date-formatting or other manipulation tricks.

• DEBUG (bool) (optional)

  You can also pass a DEBUG value to get debugging information for that particular statement dumped into the error log:

   my $obj = eval { $class->fetch( $id, { DEBUG => 1 } ); };
      

fetch_group( \%params )

Returns an arrayref of objects that meet the criteria you specify.

This is actually fairly powerful. Examples:

 # Get all the user objects and put them in a hash
 # indexed by the id
 my %uid = map { $_->id => $_ } @{ $R->user->fetch_group({ order => 'last_name' }) };

 # Get only those user objects for people who have
 # logged in today
 my $users = $R->user->fetch_group( {
               where => 'datediff( dd, last_login, get_date() ) = 0',
               order => 'last_name'
             } );
 foreach my $user ( @{ $users } ) {
   print "User: $user->{login_name} logged in today.\n";
 }

Note that you can also return objects that match the results of a join query:

 my $list = eval { $class->fetch_group({
                               order => 'item.this, item.that',
                               from => [ 'item', 'modifier' ],
                               where => 'modifier.property = ? AND ' .
                                        'item.item_id = modifier.item_id',
                               value => [ 'property value' ] } ) };

And you can use parameters found in "fetch()":

 my $list = eval { $class->fetch_group({ column_group => 'minimal' }) };

Parameters:

• where ($)

  A WHERE clause; leave this blank and you will get all entries

• value (\@)

  If you use placeholders in your WHERE clause, put the values in order in this parameter and they will be properly quoted.

• order ($)

  An ORDER BY clause; leave this blank and the order is arbitrary (whatever is normally returned by your database). Note that you can have ORDER BY clauses that use more than one field, such as:

   order => 'active_date, updated_date DESC'
      

• limit ($)

  You can limit the number of objects returned from this method. For example, you might run a search but allow the user to specify a maximum of 50 results per page. For each successive page displayed you can retrieve only those specific results.

  For instance, the following will return the first 10 records of a result set:

   my $records = eval { $object_class->fetch_group({ where => "field = ?",
                                                     value => [ 'foo' ],
                                                     limit => '10' }) };
      

  You can also return a particular number of records offset from the beginning. The following will return results 21-30 of the result set.

   my $records = eval { $object_class->fetch_group({ where => "field = ?",
                                                     value => [ 'foo' ],
                                                     limit => '20,10' }) };
      

  Other parameters get passed onto the fetch() statement when the records are being retrieved.

fetch_iterator \%params )

Uses the same parameters as "fetch_group()" but instead of returning an arrayref with all the objects, it returns an SPOPS::Iterator::DBI object. You can use this object to step through the objects one at a time, which can be an enormous resource savings if you are retrieving large groups of objects.

Example:

  my $iter = My::SPOPS->fetch_iterator({
                             where         => 'package = ?',
                             value         => [ 'base_theme' ],
                             order         => 'name' });
  while ( my $template = $iter->get_next ) {
      print "Item ", $iter->position, ": $template->{package} / $template->{name}";
      print " (", $iter->is_first, ") (", $iter->is_last, ")\n";
  }

All security restrictions are still upheld -- if a user cannot retrieve an object with "fetch()" or "fetch_group()", the user cannot retrieve it with "fetch_iterator()" either.

Parameters: see "fetch_group()".

fetch_count( \%params )

Returns the number of objects that would have been returned from a query. Note that this INCLUDES SECURITY CHECKS. So running:

 my $query = { where => 'field = ?',
               value => 'broom' };
 my $just_count = $class->fetch_count( $query );
 $query->{order} = 'other_thingy';
 my $rows = $class->fetch_group( $query );
 print ( $just_count == scalar @{ $rows } )
       ? "Equal!"
       : "Not equal -- something's wrong!";

Should print "Equal!"

This method takes mostly the same parameters as "fetch_group()", although ones like 'order' will not any functionality to the query but will add time.

Parameters not used: 'limit'

refetch( [ $fields | \@fields ], \%params )

Refetches the value of the specified field(s) from the data store and updates the object in memory. Returns the new value(s). A single field name can be specified as a simple scalar value and and multiple fields can be specified as an arrayref. Returns a scalar value for single fields and a list for multiple fields.

If the first parameter is empty, does a refetch for all fields in the object.

E.g.

  $new_val = $obj->refetch( 'field1' );
  ($new_val1, $new_val2) = $obj->refetch( [ qw/ field1 field2 / ] );

Parameters:

•

DEBUG (bool) (optional)

save( [ \%params ] )

Object method that saves this object to the data store. Returns the object if the save was successful. As with other methods, any failures trigger an exception

Example:

 my $obj = $class->new;
 $obj->{param1} = $value1;
 $obj->{param2} = $value2;
 eval { $obj->save };
 if ( $@ ) {
   print "Error inserting object: $@\n";
 }
 else {
   print "New object created with ID: $new_id\n";
 }

The object can generally tell whether it should be created in the data store or whether it should update the data store values. Currently it determines this by the presence of an ID value. If an ID value exists, this is probably an update; if it does not exist, it is probably a save.

You can give SPOPS hints otherwise. If you are controlling ID values yourself and an ID value exists in your object, you can do:

 $obj->save({ is_add => 1 });

to tell SPOPS to treat the request as a creation rather than an update.

One other thing to note if you are using SPOPS::Secure for security: SPOPS assumes that your application determines whether a user can create an object. That is, all requests to create an object are automatically approved. Once the object is created, the initial security logic kicks in and any further actions (fetch/save/remove) are controlled by "SPOPS::Secure".

Note that if your database schema includes something like:

 CREATE TABLE my_table (
  my_id      int,
  created_on datetime default today(),
  table_legs tinyint default 4
 )

and your object had the following values:

 my_id      => 10,
 created_on => undef,
 table_legs => undef

The only thing your object would reflect after inserting is the ID, since the other values are filled in by the database. The save() method tries to take this into account and syncs the object up with what is in the database once the record has been successfully inserted. If you want to skip this step, either pass a positive value for the 'no_sync' key or set 'no_save_sync' to a positive value in the CONFIG of the implementing class.

SPOPS is generally smart about dealing with auto-generated field values on object creation as well. This is done for you in one of the "SPOPS::DBI::" subclasses, or in one of the "SPOPS::Key::" classes, but it is useful to mention it here.

You can also tell SPOPS to skip certain fields from inserting or updating. The configuration keys 'no_insert' and 'no_update' provided class-wide definitions for this -- frequently you will want to put your primary key field(s) in 'no_update' so they will not be accidentally changed. You can also provide a definition for this on a per-object basis by passing an arrayref with the relevant parameter:

 my $obj = $class->fetch( $id );
 $object->{foo} = 'bar';
 $object->{bar} = 'foo';
 $object->save({ no_update => [ 'foo' ] });

 my $new_obj = $class->fetch( $id );
 print $new_obj->{foo}; # Prints 'bar'

You can also pass empty array references for either 'no_insert' or 'no_update' to override information from the class configuration. (This changed with SPOPS 0.80, previous behavior was to add the values passed in with the method call to the class configuration instead of replacing them.)

You can also tell SPOPS not to insert or update fields when they are undefined using the 'skip_undef' configuration key. (Configuration is an arrayref which we transform for internal purposes to a hashref at class initialization.) This is very useful on inserts when you have defaults defined in your database -- since no value is inserted for the field, the database will fill in the default and SPOPS will re-sync after the insert so that your object is in the proper state. (Note that the value must be "undef", not merely false.)

There are two phases where you can step in and generate or retrieve a generated value: "pre_fetch_id()" and "post_fetch_id()". The first is called before the object is saved, the second is called after the object is saved.

Use "pre_fetch_id()" when you need to ask the database (or another resource) to create a value or command to use for the generated value. For instance the SPOPS::Key::Random or SPOPS::Key::UUID key generators have a "pre_fetch_id()" method which creates a (hopefully unique) key. The SPOPS::DBI::Oracle subclass creates a command which fetches the next value from a named sequence.

The method should return a value that gets put directly into the SQL INSERT statement. Most of the time you will not want SPOPS to quote the value for you so you do not need any other arguments. If you want the value to get quoted in the normal fashion, just pass along a second argument with a true value.

Use "post_fetch_id()" when the database generates a value for you to retrieve after the INSERT. SPOPS::DBI::Sybase fetches the value of @@IDENTITY, and SPOPS::DBI::MySQL gets the value of the auto-incremented field from the database handle.

apply_insert_alter( \%params )

This method is called when "save()" is applied to an unsaved object. It uses configuration information to alter how the value for a particular field gets passed to the database. (See entry under "DATA ACCESS METHODS" for "insert_alter" above.)

If you override this, your method should modify the parameters passed in. (Peek-a-boo logic, sorry.)

Parameters:

• field (\@)

  Column names for the insert.

• value (\@)

  Values for the insert, matched in order to the "field" parameter.

• no_quote (\%)

  If you make a modification for a particular field you should set the value in this hash for the field you changed to a true value. Otherwise SPOPS::SQLInterface will quote the value using the normal DBI rules and the insert will likely fail.

• insert_alter (\%) (optional)

  In addition to defining alterations via the configuration, the user can also pass in alterations directly to the "save()" method via its parameters.

• DEBUG (int) (optional)

  Debugging value as passed into "save()"

Here is a simple example where we modify the value for the field 'tochange' to use some 'CustomSQLFunction'. Note that we explicitly do not check whether other fields need to be altered since in the first step we call the method in the parent class.

 sub apply_insert_alter {
     my ( $self, $params ) = @_;
     $self->SUPER::apply_insert_alter( $params );
     for ( my $i = 0; $i < @{ $params->{value} }; $i++ ) {
          next unless ( $params->{field}[ $i ] eq 'tochange' );
          my ( $type, $insert_value ) = split /=/, $params->{value}[ $i ], 2;
          $params->{value}[ $i ] = qq/CustomSQLFunction( $type, "$insert_value" )/;
          $params->{no_quote}{ 'tochange' };
     }
 }

field_update( $fields, \%params )

There are two versions of this method: an object method and a class method.

Object method

Conditionally updates the value(s) of the specified field(s) in the data store and in the object if successful. If the $fields argument is a scalar or arrayref, the values used to update the fields in the data store are taken from the object. If the $fields argument is a hashref, then it specifies the fields and the new values. In this case, the values in the object are also updated if and only if the update to the data store was successful.

Examples:

  $obj->{field1} = $new_value1;
  $obj->field_update( 'field1' );

  $obj->{field1} = $new_value1;
  $obj->{field2} = $new_value2;
  $obj->field_update( [ qw/ field1 field2 / ] );

  $obj->field_update( { field1 => $new_value1,
                        field2 => $new_value2 } );

  $obj->field_update( { field1 => $new_value1,
                        field2 => $new_value2 },
                      { if_match => 1 } );

  $obj->field_update( { field1 => $new_value1,
                        field2 => $new_value2 },
                      { if_match => { field3 => $val3 } );

  $obj->field_update( { field1 => $new_value1,
                        field2 => $new_value2 },
                      { where => 'field3 > $val3' } );

Parameters:

• if_match (scalar, hashref) (optional)

  If the 'if_match' parameter contains a hashref, then it specifies field names and corresponding values to be included in the WHERE clause of the SQL UPDATE. If it is present and true, but not a hashref then the WHERE clause will include the field(s) being updated and the current value(s) from the object.

  If the update does not modify any rows in the database, then the method returns false and does not update the corresponding field value(s) in the object.

  Note that this rubs against the grain of one object == one update. But it is quite useful, and useful things tend to stick around.

• where (scalar) (optional)

  If the 'where' parameter is present, it supercedes 'if_match' and included in the WHERE clause of the update.

• DEBUG (bool) (optional)

Class method

You can also use this method to update multiple objects at once. This goes against the grain of SPOPS since you generally only make changes to one object at a time, but it can be very useful.

This differs from the object method in a few ways. First, the $fields argument must be a hashref. The method will throw an exception otherwise.

Second, you must specify a 'where' clause in the parameters. The method will throw an exception otherwise.

Note that this does not update any objects in the cache. (Since there is no cache implementation right now, this point is moot but still might be worth mentioning for enterprising souls who wish to code a cache.)

Returns: If the update successfully modifies rows in the database then the method returns the number of rows updated. Otherwise it returns false.

Example:

 # Update all objects updated on '2002-08-02'

 my $num_rows = $class->field_update( { status => 'Active' },
                                      { where => 'created_on = "2002-08-02"' } );
 print "Updated $num_rows rows";

Parameters:

• where (scalar)

  Clause to specify the objects you wish to update.

• DEBUG (bool) (optional)

remove( [ \%params ] )

Note that you can only remove a saved object (duh). Also tries to remove the object from the cache. The object will not actually be destroyed until it goes out of scope, so do not count on its DESTROY method being called exactly when this happens.

Returns 1 on success, throws exception on failure. Example:

 eval { $obj->remove };
 if ( $@ ) {
   print "Object not removed. Error: $@";
 }
 else {
   print "Object removed properly.";
 }

log_action( $action, $id )

Implemented by subclass.

This method is passed the action performed upon an object ('create', 'update', 'remove') and the ID. SPOPS::DBI comes with an empty method, but you can subclass it and do what you wish

LAZY LOADING

If you are interested: the method "perform_lazy_load()" does the actual fetch of the field value.

Important Note #1: If you define one or more 'column_group' entries in your object configuration, then lazy loading is active for this class. If you store additional information in your object then SPOPS will try to lazy-load it even if it is not a valid field. If you want to store information like this, prefix the key with a '_' and SPOPS will ignore it as a lazy loaded field.

For instance, say you are producing a list of objects and want to display the user name and email. Instead of looking up the information every time, you can create a temporary local cache. (Assume that the $object class uses lazy-loading.)

 my %user_cache = ();
 foreach my $object ( @{ $object_list } ) {
     unless ( $user_cache{ $object->{user_id} } ) {
         $user_cache{ $object->{user_id} } = $object->user;
     }
     $object->{_user_name}  = $user_cache->{ $object->{user_id} }->full_name();
     $object->{_user_email} = $user_cache->{ $object->{user_id} }->email();
 }

Note how we use the keys '_user_name' and '_user_email' here. If we used 'user_name' and 'user_email' then $object would try to lazy-load these value and throw an error when the column was not found in the table.

Important Note #2: If you use lazy loading, you must define a method "global_datasource_handle()" (see "DATABASE HANDLE" above) for your object -- otherwise the "perform_lazy_load()" method will not be able to get it.

TO DO

Since we use the {field_alter} directive to modify what is actually selected from the database and use the _fetch_assign_row() to map fieldnames to field positions, we need to have a generic way to map these two things to each other. (It is not that difficult, just making a note to deal with it later.)

BUGS

COPYRIGHT

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

AUTHORS

See the SPOPS module for the full author list.