NAME

Class::DBI - Simple SQL-based object persistance

SYNOPSIS

package Film;
use base qw(Class::DBI);
use public qw( Title Director Rating NumExplodingSheep );

# Tell Class::DBI a little about yourself.
Film->table('Movies');
Film->columns('Primary', 'Title');
Film->set_db('Main', 'dbi:mysql', 'me', 'noneofyourgoddamnedbusiness');


#-- Meanwhile, in a nearby piece of code! --#
use Film;

# Create a new film entry for Bad Taste.
$btaste = Film->new({ Title       => 'Bad Taste',
                      Director    => 'Peter Jackson',
                      Rating      => 'R',
                      NumExplodingSheep   => 1
                    });

# Retrieve the 'Gone With The Wind' entry from the database.
my $gone = Film->retrieve('Gone With The Wind');

# Shocking new footage found reveals bizarre Scarlet/sheep scene!
$gone->NumExplodingSheep(5);
$gone->Rating('NC-17');
$gone->commit;

# Grab the 'Bladerunner' entry.
my $blrunner = Film->retrieve('Bladerunner');

# Make a copy of 'Bladerunner' and create an entry of the director's
# cut from it.
my $blrunner_dc = $blrunner->copy("Bladerunner: Director's Cut");

# Ishtar doesn't deserve an entry anymore.
Film->retrieve('Ishtar')->delete;

# Find all films which have a rating of PG.
@films = Film->search('Rating', 'PG');

# Find all films which were directed by Bob
@films = Film->search_like('Director', 'Bob %');

DESCRIPTION

I hate SQL. You hate SQL. We all hate SQL. Alas, we often find the need to make our objects persistant and like it or not an SQL database is usually the most flexible solution.

This module is for setting up a reasonably efficient, reasonably simple, reasonably extendable persistant object with as little SQL and DBI knowledge as possible.

Its a subclass of Class::Accessor and uses that scheme to automatically set up accessors for each public data field in your class. These accessors control access to the underlying database.

How to set it up

Here's a fairly quick set of steps on how to make your class persistant. More details about individual methods will follow.

Set up a database.

You must have an existing database set up, have DBI.pm installed and the necessary DBD:: driver module for that database. See DBI and the documentation of your particular database for details.

Set up a table for your objects to be stored in.

Class::DBI works on a simple one class/one table model. It is your responsibility to set up that table, automating the process would introduce too many complications.

Using our Film example, you might declare a table something like this:

CREATE TABLE Movies (
       Title      VARCHAR(255)    PRIMARY KEY,
       Director   VARCHAR(80),
       Rating     CHAR(5),    /* to fit at least 'NC-17' */
       NumExplodingSheep      INTEGER
)

Inherit from Class::DBI.

It is prefered that you use base.pm to do this rather than appending directly to @ISA as your class may have to inherit some protected data fields from Class::DBI and this is important if you're using pseudohashes.

package Film;
use base qw(Class::DBI);

Declare your public data members.

This can be done using fields.pm or public.pm. The names of your fields should match the columns in your database, one to one. Class::DBI (via Class::Accessor) will use this information to determine how to create accessors.

use public qw( Title Director Rating NumExplodingSheep );

Declare the name of your table

Inform Class::DBI what table you will be storing your objects in. This is the table you set up eariler.

Film->table('Movies');

Declare which field is your primary key

One of your fields must be a unique identifier for each object. This will be the primary key in your database. Class::DBI needs this piece of information in order to construct the proper SQL statements to access your stored objects.

Film->columns('Primary', 'Title');

Declare a database connection

Class::DBI needs to know how to access the database. It does this through a DBI connection which you set up. Set up is by calling the set_db() method and declaring a database connection named 'Main'.

Film->set_db('Main', 'dbi:mysql', 'user', 'password');

set_db() is inherited from Ima::DBI. See that module's man page for details.

Done.

All set! You can now use the constructors (new(), copy() and retrieve()) destructors (delete()) and all the accessors and other garbage provided by Class::DBI. Make some new objects and muck around a bit. Watch the table in your database as your object does its thing and see things being stored, changed and deleted.

Is it not nifty? Worship the module.

METHODS

The following provided methods make the assumption that you're using either a hash or a pseudohash as your underlying data structure for your object.

Life and Death - Constructors and Destructors

The following are methods provided for convenience to create, retrieve and delete stored objects. Its not entirely one-size fits all and you might find it necessary to override them.

new

$obj = Class->new(\%data);

This is a constructor to create a new object and store it in the database. %data consists of the initial information to place in your object and the database. The keys of %data match up with the public fields of your objects and the values are the initial settings of those fields.

$obj is an instance of Class built out of a hash reference.

# Create a new film entry for Bad Taste.
$btaste = Film->new({ Title       => 'Bad Taste',
                      Director    => 'Peter Jackson',
                      Rating      => 'R',
                      NumExplodingSheep   => 1
                    });

retrieve

$obj = Class->retrieve($id);

Given an ID it will retrieve an object with that ID from the database.

my $gone = Film->retrieve('Gone With The Wind');

copy

$new_obj = $obj->copy($new_id);

This creates a copy of the given $obj both in memory and in the database. The only difference is that the $new_obj will have a new primary identifier of $new_id.

my $blrunner_dc = $blrunner->copy("Bladerunner: Director's Cut");

delete

$obj->delete;

Deletes this object from the database and from memory. $obj is no longer usable after this call.

Accessors

Class::DBI inherits from Class::Accessor and thus provides accessor methods for every public field in your subclass. It overrides the get() and set() methods provided by Accessor to automagically handle database transactions.

There are two modes for the accessors to work in. Manual commit and autocommit. This is sort of analagous to the manual vs autocommit in DBI, but is not implemented in terms of this. What it simply means is this... when in autocommit mode every time one calls an accessor to make a change the change will immediately be written to the database. Otherwise, if autocommit is off, no changes will be written until commit() is explicitly called.

This is an example of manual committing:

# The calls to NumExplodingSheep() and Rating() will only make the
# changes in memory, not in the database.  Once commit() is called
# it writes to the database in one swell foop.
$gone->NumExplodingSheep(5);
$gone->Rating('NC-17');
$gone->commit;

And of autocommitting:

# Turn autocommitting on for this object.
$gone->autocommit(1);

# Each accessor call causes the new value to immediately be written.
$gone->NumExplodingSheep(5);
$gone->Rating('NC-17');

Manual committing is probably more efficient than autocommiting and it provides the extra safety of a rollback() option to clear out all unsaved changes. Autocommitting is more convient for the programmer.

If changes are left uncommitted or not rolledback when the object is destroyed (falls out of scope or the program ends) then Class::DBI's DESTROY method will print a warning about unsaved changes.

autocommit

Class->autocommit($on_or_off);
$commit_style = Class->autocommit;

$obj->autocommit($on_or_off);
$commit_style = $obj->autocommit;

This is an accessor to the current style of autocommitting. When called with no arguments it returns the current autocommitting state, true for on, false for off. When given an argument it turns autocommiting on and off. A true value turns it on, a false one off. When called as a class method it will control the committing style for every instance of the class. When called on an individual object it will control committing for just that object, overriding the choice for the class.

Class->autocommit(1);     # Autocommit is now on for the class.

$obj->retrieve('Aliens Cut My Hair');
$obj->autocommit(0);      # Shut off autocommitting for this object.

The commit setting for an object is not stored in the database.

Autocommitting is off by default.

commit

$obj->commit;

Writes any changes you've made via accessors to disk. There's nothing wrong with using commit() when autocommit is on, it'll just silently do nothing.

rollback

$obj->rollback;

Removes any changes you've made to this object since the last commit.

If you're using autocommit this method will throw an exception.

is_changed()

$obj->is_changed;

Indicates if the given $obj has uncommitted changes.

Database information

id

$id = $obj->id;

Returns a unique identifier for this object. Its the equivalent of $obj->get($self->columns('Primary'));

table

Class->table($table);
$table = Class->table;
$table = $obj->table;

An accessor to get/set the name of the database table in which this class is stored. It -must- be set.

columns

@all_columns  = $obj->columns;
@columns      = $obj->columns($group);
Class->columns($group, @columns);

This is an accessor to the names of the database columns of a class. Its used to construct SQL statements to act on the class.

Columns are grouped together by typical usage, this can allow more efficient access by loading all columns in a group at once. This basic version of the module does not take advantage of this but more complex subclasses do.

There are three 'reserved' groups. 'All', 'Essential' and 'Primary'.

'All' are all columns used by the class. It will automatically be generated from your public data fields if you don't set it yourself.

'Primary' is the single primary key column for this class. It must be set before objects can be used.

'Essential' are the minimal set of columns needed to load and use the object. Its typically used so save memory on a class that has alot of columns but most only uses a few of them. It will automatically be generated from Class-columns('All')> if you don't set it yourself.

Class->columns('Primary', 'Title');

If the $group is not given it will assume you want 'All'.

is_column

Class->is_column($column);
$obj->is_column($column);

This will return true if the given $column is a column of the class or object.

Defining SQL statements

Class::DBI inherits from Ima::DBI and prefers to use that class's style of dealing with databases and DBI. (Now is a good time to skim Ima::DBI's man page).

In order to write new methods which are inheritable by your subclasses you must be careful not to hardcode any information about your class's table name or primary key. However, it is more efficient to use Ima::DBI::set_sql() to generate cached statement handles.

This clash between inheritability and efficiency is solved by make_sql(). Through the magic of autoloading and closures make_sql() lets you write cached SQL statement handles while still allowing them to be inherited.

make_sql

$obj->make_sql($sql_name, \&sql_generator);
$obj->make_sql($sql_name, \&sql_generator, $db_name);

make_sql() works almost like Ima::DBI::set_sql() with two important differences.

Instead of simply giving it an SQL statement you must instead feed it a subroutine which generates the necessary SQL statement. This routine is called as a method and takes no arguments.

Generally, a call to make_sql() looks something like this:

    # Define sql_GetFooBar()
    Class->make_sql('GetFooBar',
                    sub {
                        my($class) = shift;

						my $sql = '';
			$sql .= 'SELECT '. join(', ', $class->columns('Essential')  ."\n";
			$sql .= 'FROM   '. $class->table                ."\n";
			$sql .= 'WHERE  Foo = ? AND Bar = ?'

						return $sql;
					}
				   );

You must be careful not to hardcode information about your class's table name or primary key column in your statement and instead use the table() and columns() methods instead.

If you're creating an SQL statement that has no information about your class in it (usually because its operating on a different table than your class is stored in) then you may create your statement normally using the set_sql() method inherited from Ima::DBI.

If $db_name is omitted it will assume you are using the 'Main' connection.

Searching

We provide a few simple search methods, more to show the potential of the class than to be serious search methods.

search

@objs = Class->search($key, $value);
@objs = $obj->search($key, $value);

This is a simple search through the stored objects for all objects whose $key has the given $value.

@films = Film->search('Rating', 'PG');

search_like

@objs = Class->search_like($key, $like_pattern);
@objs = $obj->search_like($key, $like_pattern);

A simple search for objects whose $key matches the $like_pattern given. $like_pattern is a pattern given in SQL LIKE predicate syntax. '%' means "any one or more characters", '_' means "any single character".

# Search for movies directed by guys named Bob.
@films = Film->search_like('Director', 'Bob %');

CAVEATS

Only simple scalar values can be stored

SQL sucks in that lists are really complicated to store and hashes practically require a whole new table. Don't even start about anything more complicated. If you want to store a list you're going to have to write the accessors for it yourself. If you want to store a hash you should probably consider making a new table and a new class.

One table, one class

For every class you define one table. Classes cannot be spread over more than one table, this is too much of a headache to deal with.

Single column primary keys only

Having more than one column as your primary key in the SQL table is currently not supported. Why? Its more complicated. A later version will support multi-column keys.

Careful with the autoloaders!

Class::DBI employs an autoloader (inherited from Class::Accessor) so your subclass must be careful if you're defining your own autoloader. You must be sure to call Class::DBI's autoloader should your own not find a valid method. For example:

sub AUTOLOAD {
    my($self) = $_[0];

    my($func) = $AUTOLOAD =~ m/::([^:]+)$/;

    ### Try to autoload $func ###

    # If all else fails, pass the buck to Class::DBI.
    *Class::DBI::AUTOLOAD = \$AUTOLOAD;
    goto &Class::DBI::AUTOLOAD
}

You must, of course, be careful not to modify @_ or $AUTOLOAD.

Define new SQL statements through make_sql()

AUTHOR

Michael G Schwern <schwern@pobox.com> with much late-night help from Uri Gutman

SEE ALSO

Ima::DBI, Class::Accessor, public, base

cpanm Class::DBI

perl -MCPAN -e shell
install Class::DBI