NAME

SPOPS::DBI -- Implement SPOPS class, serializing into a DBI database

SYNOPSIS

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

DESCRIPTION

This SPOPS class is not meant to be used directly. Instead, you inherit certain methods from it while implementing your own. Your module should implement:

(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:

Create the configuration file (or add to the existing one)
Create the database table the class depends on.

Complete!

DATABASE HANDLE

This package requires access to a database handle. We do not keep a reference of the database handle with the object for complexity reasons (but you can actually implement this if you would like). Instead you either need to pass a database handle to a method using the db parameter or define a method in your object global_datasource_handle() which returns an appropriate database handle. (Note: the old global_db_handle() method is deprecated and will be removed eventually.)

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::Exception->throw( "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.

DATA ACCESS METHODS

The following methods access configuration information about the class but are specific to the DBI subclass. You can call all of them from either the class (or subclass) or an instantiated object.

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

id_clause( [ $id, [ $opt, \%params ] )

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 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'

save( [ \%params ] )

Object method that saves this object to the data store. Returns the new ID of the object if it is an add; returns the object ID if it is an update. As with other methods, any failures trigger an exception

Example:

my $obj = $class->new;
$obj->{param1} = $value1;
$obj->{param2} = $value2;
my $new_id = 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.

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' };
    }
}

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

This class supports lazy loading, available in SPOPS 0.40 and later. All you need to do is define one or more 'column_group' entries in the configuration of your object and SPOPS will do the rest.

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

Consistent Field Handling

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

None known.

COPYRIGHT

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

AUTHORS

Chris Winters <chris@cwinters.com>

See the SPOPS module for the full author list.

To install SPOPS, copy and paste the appropriate command in to your terminal.

cpanm

cpanm SPOPS

CPAN shell

perl -MCPAN -e shell
install SPOPS

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SYNOPSIS

DESCRIPTION

DATABASE HANDLE

DATA ACCESS METHODS

OBJECT METHODS

LAZY LOADING

TO DO

BUGS

COPYRIGHT

AUTHORS

Module Install Instructions