Class::DBI - Simple Object Persistance
package Film; use base qw(Class::DBI); # Tell Class::DBI a little about yourself. Film->table('Movies'); Film->columns(All => qw( Title Director Rating NumExplodingSheep )); Film->columns(Primary => qw( Title )); Film->set_db('Main', 'dbi:mysql', 'me', 'noneofyourgoddamnedbusiness', {AutoCommit => 1}); #-- 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 %');
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 uses a scheme to automatically set up accessors for each data field in your class. These accessors control access to the underlying database.
Here's a fairly quick set of steps on how to make your class persistant. More details about individual methods will follow.
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.
DBD::CSV works in a pinch.
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 (unless somebody wants to convince me otherwise).
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 )
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);
This can be done using columns(). 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.
Film->columns(All => qw( Title Director Rating NumExplodingSheep ));
For more information about how you can more efficiently declare your columns, "Lazy Population of Columns"
Inform Class::DBI what table you will be storing your objects in. This is the table you set up eariler.
Film->table('Movies');
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');
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', {AutoCommit => 1});
set_db() is inherited from Ima::DBI. See that module's man page for details.
XXX I should probably make this even simpler. set_db_main() or something.
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.
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.
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.
$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 columns 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 });
If the primary column is not in %data, new() will assume it is to be generated. If a sequence() has been specified for this Class, it will use that. Otherwise, it will assume the primary key has an AUTO_INCREMENT constraint on it and attempt to use that.
If the class has declared relationships with foreign classes via hasa(), it can pass an object to new() for the value of that key. Class::DBI will Do The Right Thing.
$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');
$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");
$obj->delete;
Deletes this object from the database and from memory. $obj is no longer usable after this call.
Class::DBI inherits from Class::Accessor and thus provides accessor methods for every column 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.
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 = Class->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.
$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.
$obj->rollback;
Removes any changes you've made to this object since the last commit. Currently this simply reloads the values from the database. This can have concurrency issues.
If you're using autocommit this method will throw an exception.
@changed_keys = $obj->is_changed;
Indicates if the given $obj has uncommitted changes. Returns a list of keys which have changed.
$id = $obj->id;
Returns a unique identifier for this object. Its the equivalent of $obj->get($self->columns('Primary'));
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.
Table information is inherited by subclasses, but can be overridden.
Class->sequence($sequence_name); $sequence_name = Class->sequence; $sequence_name = $obj->sequence;
An accessor to get/set the name of a sequence for the primary key.
Class->columns(Primary => 'id'); Class->sequence('class_id_seq');
Class::DBI will use the sequence to generate primary keys when objects are created yet the primary key is not specified.
NOTE: Class::DBI also supports AUTO_INCREMENT and similar semantics.
@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. For more information about this, "Lazy Population of Columns".
There are three 'reserved' groups. 'All', 'Essential' and 'Primary'.
'All' are all columns used by the class. If not set it will be created from all the other groups.
'Primary' is the single primary key column for this class. It must be set before objects can be used. (Multiple primary keys will be supported eventually)
Class->columns('Primary', 'Title');
'Essential' are the minimal set of columns needed to load and use the object. Only the columns in this group will be loaded when an object is retreive()'d. 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-
If no arguments are given it will assume you want a list of All columns.
NOTE I haven't decided on this method's behavior in scalar context.
Class->is_column($column); $obj->is_column($column);
This will return true if the given $column is a column of the class or object.
Often you'll want one object to contain other objects in your database, in the same way one table references another with foreign keys. For example, say we decided we wanted to store more information about directors of our films. You might set up a table...
CREATE TABLE Directors ( Name VARCHAR(80), Birthday INTEGER, IsInsane BOOLEAN )
And put a Class::DBI subclass around it.
package Film::Directors; use base qw(Class::DBI); Film::Directors->table('Directors'); Film::Directors->columns(All => qw( Name Birthday IsInsane )); Film::Directors->columns(Prmary => qw( Name )); Film::Directors->set_db(Main => 'dbi:mysql', 'me', 'heywoodjablowme', {AutoCommit => 1});
Now Film can use its Director column as a way of getting at Film::Directors objects, instead of just the director's name. Its a simple matter of adding one line to Film.
# Director() is now an accessor to Film::Directors objects. Film->hasa('Film::Directors', 'Director');
Now the Film->Director() accessor gets and sets Film::Director objects instead of just their name.
Class->hasa($foreign_class, @foreign_key_columns);
Declares that the given Class has a relationship with the $foreign_class and is storing $foreign_class's primary key information in the @foreign_key_columns.
An accessor will be generated with the name of the first element in @foreign_key_columns. It gets/sets objects of $foreign_class. Using our Film::Director example...
# Set the director of Bad Taste to the Film::Director object # representing Peter Jackson. $pj = Film::Directory->retreive('Peter Jackson'); $btaste = Film->retreive('Bad Taste'); $btaste->Director($pj);
hasa() will try to require the foreign class for you. If the require fails, it will assume its not a simple require (ie. Foreign::Class isn't in Foreign/Class.pm) and that you've already taken care of it and ignore the warning.
It is not necessary to call columns() to set up the @foreign_key_columns. hasa() will do this for you if you haven't already.
XXX I don't know if I like the way this works. It may change a bit in the future. I'm not sure about the way the accessor is named.
NOTE The two classes do not have to be in the same database!
In the tradition of Perl, Class::DBI is lazy about how it loads your objects. Often, you find yourself using only a small number of the available columns and it would be a waste of memory to load all of them just to get at two, especially if you're dealing with large numbers of objects simultaneously.
Class::DBI will load a group of columns together. You access one column in the group, and it will load them all on the assumption that if you use one you're probably going to use the rest. So for example, say we wanted to add NetProfit and GrossProfit to our Film class. You're probably going to use them together, so...
Film->columns('Profit', qw(NetProfit GrossProfit));
Now when you say:
$net = $film->NetProfit;
Class::DBI will load both NetProfit and GrossProfit from the database. If you then call GrossProfit() on that same object it will not have to hit the database. This can potentially increase performance (YMMV).
If you don't like this behavior, just create a group called 'All' and stick all your columns into it. Then Class::DBI will load everything at once.
SQL is largely case insensitive. Perl is largely not. This can lead to problems when reading information out of a database. Class::DBI does some data normalization.
$obj->normalize(\@columns);
There is no guarantee how a database will muck with the case of columns, so to protect against things like DBI->fetchrow_hashref() returning strangely cased column names (along with table names appended to the front) we normalize all column names before using them as data keys.
$obj->normalize_hash(\%hash);
Given a %hash, it will normalize all its keys using normalize(). This is for convenience.
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 set_sql() to generate cached statement handles.
Generally, a call to set_sql() looks something like this:
# Define sql_GetFooBar() Class->set_sql('GetFooBar', <<'SQL'); SELECT %s FROM %s WHERE Foo = ? AND Bar = ?
This generates a method called sql_GetFooBar(). Any arguments given are used fill in your SQL statement via sprintf().
my $sth = Class->sql_GetFooBar(join(', ', Class->columns('Essential')), Class->table);
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 $db_name is omitted it will assume you are using the 'Main' connection.
We provide a few simple search methods, more to show the potential of the class than to be serious search methods.
@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');
@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".
XXX Should I offer glob-style * and ? instead of % and _?
# Search for movies directed by guys named Bob. @films = Film->search_like('Director', 'Bob %');
Ummm... well, there's the SYNOPSIS.
XXX Need more examples. They'll come.
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 (although I plan to prove ways to handle this soon). If you want to store a hash you should probably consider making a new table and a new class.
Someone might be able to convince me to build accessors which automagically serialize data.
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.
Eventually I'll ease this restriction for link tables and tables representing lists of data.
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.
There's no graceful way to handle relationships between two tables/objects. I plan to eventually support these relationships in a fairly simple manner.
There's no graceful way to handle lists of things as object data. This is also something I plan to implement eventually.
If you need this feature let me know and I'll get it working.
$obj->commit should DBI->commit???
Need an easy way to do class-wide commit and rollback.
Michael G Schwern <schwern@pobox.com> with much late-night help from Uri Gutman, Damian Conway, Mike Lambert and the POOP group.
Ima::DBI, Class::Accessor, base, Class::Data::Inheritable http://www.pobox.com/~schwern/papers/Class-DBI/, Perl Object-Oriented Persistence <poop-group@lists.sourceforge.net>, Alzabo and Tangram
1 POD Error
The following errors were encountered while parsing the POD:
You forgot a '=back' before '=head1'
To install Class::DBI, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Class::DBI
CPAN shell
perl -MCPAN -e shell install Class::DBI
For more information on module installation, please visit the detailed CPAN module installation guide.