Whenever a programmer is going to use a database seriously - beyond a few simple stores and queries - they must spend considerable time producing a thunking layer: A layer that sits between the database and the computing language so that queries from the database can be made sense of and so that every bit of the program doesn't have to know how to deal with the database itself.
To be honest, this is a boring and monotonous task that a programmer can easily make a lot of mistakes doing if they're not extremely careful.
What would be nice is if someone else was to do ninety percent of this work for us. Apart from the odd case, this is mostly just writing the same old code over and over again. Most of the time the code ends up being some kind of object that represents a line in the database which has the ability to alter itself and retrieve associated other lines as objects from the database in turn.
Class::DBI, a module that is a base class for these lines-as-objects, makes use of the fact that most thunking layers are pretty much identical and only requires you to enter but a few details on each table before a usable subclass can be created.
Using it can greatly speed up your development time, and has the added bonus that you're much less likely to make mistakes than coding this kind of thing by hand. Full of hooks and customisable options it's powerful enough that you should be able to thunk to sensible objects that you can use to represent the most convoluted and complicated databases, while being simple enough that it can be used with minimum fuss in the situations that don't require it.
So, Christmas is coming and the proverbial goose is getting fat, so it's time to start planning the Christmas meal. And what better way than creating a database of items that we need for the meal?
This year I'm going to let my database have two tables, one that represents the various items in the meal (the turkey, the roast potatoes, the Christmas pudding, etc) and another table that represents the ingredients in each of these items (the bird itself, the stuffing, the potatoes, the lard, etc...)
#!/usr/bin/perl
# turn on Perl's safety features use strict; use warnings;
# connect to the database
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=meal.db","","");
# run the SQL to create the items table
$dbh->do(q{
CREATE TABLE item (
itemid INTEGER PRIMARY KEY,
description STRING,
qty INTEGER
)});
# run the sql to create the ingredients table
$dbh->do(q{
CREATE TABLE ingredient (
ingredientsid INTEGER PRIMARY KEY,
item INTEGER,
name STRING,
qty INTEGER
)});
So what we want to do is create a class for each database table so that each instance of that class we create represents one line in the table. While this sounds quite tricky, Class::DBI will do most of the work for us - all we really have to do is write a thin layer of mapping code.
Before we can actually write these classes we first create an abstract
base class that inherits from Class::DBI (or Class::DBI::SQLite
in this case when we're using an SQLite database) and knows how to
connect to the database. By having each of our classes inherit from
this once class they can share a connection to the database (which
will put less stress on the database than by each class or each instance
opening a new connection each time.) This also has the advantage that
all of our password data is one file - so if this ever changes we only
ever need to edit this one base class.
package XmasMeal::DBI;
# inherit from Class::DBI::SQLite not Class::DBI # directly when using DBD::SQLite use base qw(Class::DBI::SQLite);
# turn on Perl's safety features use strict; use warnings;
# tell it how to connect
Xmas::DBI->set_db('Main', # always 'Main'
'dbi:SQLite:meal.db', # database handle info
'', # username
''); # password
# return true to keep perl happy 1;
Thankfully, that's the hardest bit out the way. In comparison the mapping code is simple; Here's the example for the items table:
package XmasMeal::Item; use base qw(XmasMeal::DBI);
# turn on the safety features use strict; use warnings;
# set it to use the correct table
XmasMeal::Item->table('item');
# and the columns we have
XmasMeal::Item->columns(All => qw(itemid
description
qty));
# return true to keep perl happy 1;
And that's all the mapping code we need. We simply needed to tell the class what table it was dealing with and what columns are in that table. The first column we handed it is always assumed to be the primary key.
We can now perform a whole plethora of operations really simply. For example we can create new rows in the table like so:
use XmasMeal::Item;
my $item = XmasMeal::Item->create({ description => "Turkey",
qty => 1 });
Note that we don't have to specify the primary key (the itemid) as
the database is treating that field as auto-updating it'll be
automatically filled in for us. The primary key (if you happen to
know it) can be used to retrieve the created rows again:
# get the item my $item = Xmas::Item->retrieve(1);
When we have an object we can access any of the object's fields by simply calling the accessor method that's the same name as the column in the database.
# say how much of this item we need
print "We need to prepare ". $item->qty .
" " . $item->description . "\n";
And if we want to change the values then we can simply call the same accessors with the new value, and then commit the changes to 'save' them back to the database.
# I prefer chicken to turkey
$item->description("Chicken")
# they're small though. Twice as much? $item->qty( $item->qty * 2 );
# save those changes $item->commit()
Until you commit the changes are stored 'in memory' only. This is
useful as it enables you to rollback easily (undo the changes you made
easily) and is much more efficient than writing to the database after
each and every call. However, if you want the auto-committing behaviour
it can be turned on on a per class or per object basis with the
autocommit command.
# turn on auto-commiting XmasMeal::Item->autocommit(1);
Now, all of this wouldn't be that useful if all we could possibly do was look up objects by their primary key and store and retrieve them. What we want to be able to do is search the relational database and have the results returned to us as a list of objects.
# lookup the item
my ($turkey)
= XmasMeal::Item->search( description => "Turkey" );
# look up all items whose description contains the
# word 'Roast'
my @gonna_take_up_oven_space
= XmasMeal::Item->search_like( description => "%Roast%" );
Sometimes one of these searches will return too many results (especially if a certain someone attempts to cook every known vegetable to man with the Christmas meal.) If you call the search method in scalar context (i.e. you assign it to a scalar not to an array or list or scalars) then you'll get an 'iterator' back. An iterator is simple an object that you can continually ask for the next result from.
# do the search, store the iterator in '$roasting'
my $roasting
= XmasMeal::Item->search_like( description => "%Roast%" );
# process each item in turn. 'next' will return undef when
# there are no more items, and then the loop will end.
while (my $item = $roasting->next)
{
print $item->description . "\n";
}
Class::DBI can create relationships between tables. In our example we have ingredients that are parts of the items that make up our Christmas dinner. Each of the ingredients 'has a' item that they're part of, and each item 'has many' ingredients. For example the raisins and brandy are part of the Christmas pudding so they each 'has a' Christmas pudding and the pudding 'has many' of them. Let's write the Ingredients class to demonstrate how that works.
package XmasMeal::Ingredient; use base qw(XmasMeal::DBI);
# turn on the safety features use strict; use warnings;
# set it to use the correct table
XmasMeal::Ingredient->table('ingredient');
# and the columns we have
XmasMeal::Ingredient->columns(All => qw(ingredientid
item
name
qty
units));
# now tell that rather than returning the item's primary # key stored in item it should retrieve the Item for that # primary key and return that instead XmasMeal::Ingredient->has_a(item => "XmasMeal::Item" );
# return true to keep perl happy 1;
And now we can populate the database just as you might expect.
Instead of assigning the primary key of the pudding row to the item
field, we simply assign the object and let Class::DBI sort it out
for us.
# create the pudding
my $pudding = XmasMeal::Item->create({
description => "Christmas Pudding",
qty => 1
});
# create the raisins
XmasMeal::Ingredient->create({
name => "Raisins",
qty => 0.150,
units => "kilogrammes",
item => $pudding,
});
# create the brandy
XmasMeal::Ingredient->create({
name => "Brandy",
qty => 0.025,
units => "litres",
item => $pudding,
});
So now we can find out the description of the item that an ingredient is by getting it's associated item like so:
use XmasMeal::Ingredient;
# get the brandy my ($brandy) = XmasMeal::Ingredient->search( name => "Brandy" );
# get the item it's part of $item = $brandy->item;
# print the description print $item->description;
We would also love to go the other way round so we can find out what ingredients are in an item. To do this we need to declare the following 'has_many' relationship in XmasMeal::Item
# the ingredients accessor should return a list of all ingredients
# that have our primary key in their 'item' field.
XmasMeal::Item->has_many('ingredients',
'XmasMeal::Ingredient' => 'item');
Which enables to find out for example, what's in our pudding like so:
print join ", ", map { $_->name } $pudding->ingredients;
print "\n";
Class::DBI also offers a shortcut for creating new objects and associating them with existing classes.
$pudding->add_to_ingredients({
name => "Cherries",
units => "kilogrammes",
qty => "0.02"
});