Various content frameworks, such as Django or Rails, allow a developer to define the data for the objects they use within the code. From there they then handle creating the database structure on the fly. This frees the developer from having to deal with database structure or most SQL. For myself, I have a lot of trouble thinking in those terms. My thought process involves putting the data structure together on the database, then wrapping my code around that.
Building on my previous article about abstracting your database inserts and my little introduction to Object Oriented design, I would now like to put these two together for you. While I’m at it, let’s see what we can do about pushing many of your data requests to your database into the background by having the table structure define your objects.
Fair warning, not much of what I’m about to get into makes sense without referencing my other two articles. If you’re already comfortable with Object Oriented development you can certainly skip that article (though your comments would be appreciated there).
Refresher
In my Object Oriented post I mentioned how doing things in this manner would save you some serious coding time. It does this by getting you into thinking about how to best group similar bits of data and actions then share them through the rest of your code through inheritance. What does this isn’t so much the structure of the code itself, but how the developer (that’d be you) approaches the problems they face.
My next post got into the notion of utilizing the structure of your data in the database to do something intelligent with it. In that case I showed how you could get a better handle on putting stuff into the database in a way that was both easier and provided for better integrity of that data.
Building on both of these concepts I intend to show you how we can take the data structure of your database to help define a wide variety of objects using just a wee bit of inheritance.
Building The DatabaseObject Class
I really need to learn how to come up with cooler names. Even though it’s got a boring name it should provide us with something descriptive as we move along. So let’s get together the beginnings of this.
class DatabaseObject
{
private $objectData = array();
private $fieldList = array();
private $primaryIndex = "";
private $indexValue = 0;
protected $dbPtr = FALSE;
protected $databaseTable = "";
public function __construct($id=0)
{
$this->dbPtr = fancyDatabaseConnector();
$this->indexValue = intval($id);
}
}
Not much to it at this point. We’ve got a couple of arrays, some strings, and an integer for our data members. A simple constructor that passes an integer value along to the $indexValue member. So, let’s get into what all this is supposed to do.
The $objectData array will keep all the data for an object stored in an associative array. You know, like the one you get from running a mysql_fetch_assoc() function. The field names will act as the key, with what is either in, or needs to go into, the database as the value. Needs to go into? Oh yeah, we can also use this to update the database. We’ll get there.
If you recall from my database abstraction post, I used $fieldList as a place to store information about the field in question with a simplified subset of types. In that earlier post that information was forgotten, but now that we’re in Objectville we can not only remeber this information, but use it.
The $primaryIndex will be the name of the field that stores… wait for it…. the primary index! We’ll assign that when we go to populate the $fieldList array. On a very related note, the $indexValue will be the, lacking a better way to say this, the value of the primary index. Pretty simple stuff huh?
The constructor starts by calling out to a database connection function. I didn’t want to get bogged down in connection details or the error handling that you “should” have in there. For what I’m talking about here, just know you’ve got a database resource sitting in the $dbPtr member.
Last in our data member parade is the $databaseTable. Kind of weird that it’s blank, and it has a different permission set. I’m going to let the child class deal with that one, but I need it at least defined here for methods that will be running here.
Getting The Data
At this point we can’t get any data out of the database no matter how many fancy methods we toss in there. We don’t know which table to get that data from! Sounds like before we go any further we’ll at least need the skeleton of a child class.
class Product extends DatabaseObject
{
protected $databaseTable = "Products";
public function __construct($id=0)
{
parent::__construct( intval($id) );
}
}
Yes, our good friend the Product class has returned to us from the OO tutorial post. This time he’s not the top of the tree, just a child of DatabaseObject. To make sure the parent class gets passed the index value an explicit call is made to the parent’s constructor.
Getting back to the DatabaseObject class, let’s get a little pulling action going on from the database.
class DatabaseObject
{
private $objectData = array();
private $fieldList = array();
private $primaryIndex = "";
private $indexValue = 0;
protected $dbPtr = FALSE;
protected $databaseTable = "";
public function __construct($id=0)
{
$this->dbPtr = fancyDatabaseConnector();
$this->indexValue = intval($id);
$this->pullFromDatabase()
}
public function getValue($field) {
if ( array_key_exists($field, $this->objectData) ) {
return $this->objectData[$field];
} else {
return FALSE;
}
}
private function pullFromDatabase()
{
if ( !$this->indexValue
OR !$this->primaryIndex
OR !$this->databaseTable) {
return FALSE;
}
$id = $this->indexValue;
$idFld = $this->primaryIndex;
$table = $this->table;
$query = "SELECT * FROM ".$table." WHERE ".$idFld." = ".$id;
$queryResource = mysql_query($query, $dbPtr);
$this->objectData = mysql_fetch_assoc($queryResource);
}
}
This still isn’t quite ready to run. It seemed like a good stopping point to explain what has happened, and to illustrate one of the nifty little aspects of dealing with an object.
Once the object is constructed it then runs the pullFromDatabase() method. Within that method if anything we’re going to need is missing it doesn’t run. Sure, we’ve got the $indexValue and the $databaseTable from the child class, but we still haven’t hunted down the $primaryIndex yet. I’ll be getting to that bit shortly.
I would like you to notice at this point something about the structure of pullFromDatabase(). There are no arguments being passed in. Everything that method needs to know to run is sitting in those data members. It also doesn’t return a value, since it directly assigns what it has pulled back from the database to this objects $objectData. For you folks still working through getting out of procedural land, this is a pretty good example of how sharing data within an object class works out. Due to this, methods aren’t limited to just arguments in and a single value out like a function out there in global namespace land.
Since we’re putting all of this data into a private data member, $objectData, not even an inherited class can get to this information directly. This is where the accessor method getValue() comes into a play. The additional advantage to this, outside of controlling how the data is passed around, is that now you can also handle what happens if a child class asks for a field that doesn’t exist.
As I said, this isn’t ready to roll yet. The code to figure out the primary index hasn’t been put into place yet. If you look at the line that assigns a value to the string $query it should be readily apparent that this is fairly important. Now this class starts to get a bit longer, as it’s time to convert that old nasty dbFields() function into a slick method worked into this class.
class DatabaseObject
{
private $objectData = array();
private $fieldList = array();
private $primaryIndex = "";
private $indexValue = 0;
protected $dbPtr = FALSE;
protected $databaseTable = "";
public function __construct($id=0)
{
$this->dbPtr = fancyDatabaseConnector();
$this->indexValue = intval($id);
$this->dbFields_mysql();
$this->pullFromDatabase()
}
public function getValue($field)
{
if ( array_key_exists($field, $this->objectData) ) {
return $this->objectData[$field];
} else {
return FALSE;
}
}
public function setValue($field, $value)
{
if ( !array_key_exists($field, $this->fieldList) ) {
return FALSE;
}
$this->objectData[$field] = $value;
}
private function pullFromDatabase()
{
if ( !$this->indexValue
OR !$this->primaryIndex
OR !$this->databaseTable) {
return FALSE;
}
$id = $this->indexValue;
$idFld = $this->primaryIndex;
$table = $this->table;
$query = "SELECT * FROM ".$table." WHERE ".$idFld." = ".$id;
$queryResource = mysql_query($query, $dbPtr);
$this->objectData = mysql_fetch_assoc($queryResource);
}
private function dbFields_mysql()
{
// A translation array used to convert a specific MySQL data type to
// a generic datatype used within this class.
$dataTypes = array("tinyint" => "integer", "smallint" => "integer",
"mediumint" => "integer", "int" => "integer",
"bigint" => "integer",
"float" => "float", "decimal" => "float",
"double" => "float",
"varchar" => "string", "text" => "string",
"char" => "string",
"datetime" => "date", "date" => "date",
"time" => "date");
// Go out and hunt down all that field information
$query = "SHOW COLUMNS FROM ".$this->databaseTable;
$queryResource = mysql_query($query, $this->dbPtr);
// Run through it all and fill in the fieldList and fieldIndex vars
while ( $row = mysql_fetch_assoc($queryResource) ) {
$field = $row["Field"];
$prop = $row["Type"];
$key = $row["Key"];
// Look for a value stored in parenthesis
$sizeStart = strpos($prop, "(") + 1;
$sizeLength = strpos($prop, ")") - $sizeStart;
if ($sizeStart > 1) { // We have a parenthesis
$type = substr($prop, 0, $sizeStart - 1);
$size = substr($prop, $sizeStart, $sizeLength);
} else { // No parenthesis, so set the size to zero
$type = $prop;
$size = 0;
}
// Look for a space in the Type, as that is used to specify unsigned
// or some other type of attribute for this field.
if ( strpos($prop, " ") ) {
$attrib = substr($prop, strpos($prop, " ") + 1);
} else {
$attrib = FALSE;
}
// Assign the generic data type as the type we'll be storing in the
// returned array.
if ( array_key_exists($type, $dataTypes) ) {
$type = $dataTypes[$type]; // Assign a generic data type
}
// Assign what we've discoverd to the fieldList array
if ($key == "PRI") {
$this->primaryIndex = $field;
} else {
$this->fieldList[$field]["type"] = $type;
$this->fieldList[$field]["size"] = $size;
$this->fieldList[$field]["attrib"] = $attrib;
}
}
}
}
The first thing to notice here is that there is now a new call in the constructor to the dbFields_mysql() method. All that was needed to convert this from a procedural style function was a to add a few “$this->” references to the data members, and get rid of having to declare those variables at the top of the method. There is also a new assignment for the $primaryIndex data member, which is what we’re really needing at the moment. The pullFromDatabase() method now has all the information it will need to run.
I’ve also snuck in another little method you can use to change the data within that private $objectData. The setValue() method allows an inherited class a controlled means for altering the data.
As setValue() presently exists it will work, but this method is hardly complete. At this point it at least verifies that a field exists before allowing a value to be assigned. Thing is, thanks to the dbFields_mysql method we also know what are the allowable values for each and every field. At this point I’ve excluded that error checking for the sake of keeping this sort of brief. Essentially you could work up a simple switch/case handler like in the dbUpdate() function from my earlier post.
In a language like Java where type safety is integral the JVM handles making sure that only a string can be assigned to a string, or a number to an integer. Here the setValue() method can be used to implement some type safety into your inherited classes, with the additional ability of insuring that string lengths and numeric values are all within bounds before storing them.
Putting It To Use
Let’s bring back the child class for just a moment here.
class Product extends DatabaseObject
{
protected $databaseTable = "Products";
public function __construct($id=0)
{
parent::__construct( intval($id) );
}
}
Thanks to inheritance, this is now a fully database enabled object. When you pass in an index all the fields are filled in from the database and are ready to use. So let’s see it in action:
$product = new Product(12);
print $product->getValue("model");
print $product->getValue("description");
$product->setValue("price", 12.32);
That’s pretty much it. No SQL to write. It all just happens since the DatabaseObject class handles all of that. Just creating the object makes all of the data readily available to us. We can even change the data in a controlled way. Well, we’re still missing one part of this over all puzzle, and that’s writing changes back to the database. Again going back to my previous post about database abstraction, we have all the data sitting in an array that just happens to be in the exact format the dbUpdate() function is expecting to get in.
The correct way to do this would be to also convert that dbUpdate() function into a method for this class. At this point I would hope that I have provided enough information on doing this so I’m not going to put that code in here for you. Let’s just call it a little bit of homework.
What I will mention here is a little publicly available wrapper method to call your dbUpdate() method.
private function dbUpdate_mysql()
{
// Converted copy of that global function mentioned earlier
}
public function commitChanges()
{
$id = this->dbUpdate_mysql();
return $id;
}
Now when you’re actually using that Product object you can send those updates to the database with just a simple:
$product->commitChanges();
You may be asking yourself, or at least I hope you would, why not just make dbUpdate() public and call it directly? Isn’t it sort of a wast to call one method that doesn’t do anything but call another one? Normally it would be wasteful, but in this case the cost is worth it. By running your update requests through commitChanges() you can point to any dbUpdate routine. In this case we’re using MySQL. You may need to convert your application to use some other database backend at some point due to any one of a variety of reasons. Don’t let yourself get locked in to just one kind of database call.
Perfect database abstraction is a myth. It’s the holy grail of developers to have this one perfect suite of code that is able to talk to any database with the flip of a switch. Like the grail, it is often seen just around the corner, but never actually acquired. The truth of the matter is that often enough you’ll need a feature one engine provides but another doesn’t. The only way to correct this is to be able to code directly to that engine. By abstracting which set of methods are used to do the database transactions you’ve given yourself a way to update which functions are being talked to, without having to go out and alter every line of code that needs something from the database.
The commitChanges() method could just as readily called dbupdate_postgre(), or perhaps worked in some logic to select which method to call based on the type of database used.
What’s Left For You To Do
I’ve left a lot of gaps in that object to get to this point in the post. Like my other posts, and what seems to be a recurring theme here, I’m not providing you copy and paste kind of stuff for your code. That’s what PEAR is for. Here my point is to show you what is hopefully a new and useful concept that you can take with you into your own development. With that being said, here’s a list of items you should complete in order to make this class into something that you would use in production.
- Proper error handling for your database connection.
- Data validation in the setValue() method.
- Properly convert dbUpdate() into a method using data members rather than arguments.
- Fully abstract the calls to the database to avoid engine specific calls.
It doesn’t stop there. You could work in little utility functions like date conversion to and from a database format and a unix date integer for use in the date() function. Whatever else you can dream up for moving as much of the database handling into the background so you can focus on the logic of how you use that data in the foreground.
Different Kinds Of Objects
Let’s just say that you’ve put together a reasonably complete and production ready version of this DatabaseObject. So far I’ve shown how you can use that to create a Product class that immediately has database reading and writing with all the data members in place ready to use. Certainly there are many more kinds of objects out there that a database is going to hold. Stuff like accessories, options, shopping cart data, and everything else that goes into completing a site. If you’ve created the tables in the database with all the appropriate fields, you just keep extending from that DatabaseObject class.
class Accessory extends DatabaseObject
{
protected $databaseTable = "Accessory";
public function __construct($id=0)
{
parent::__construct( intval($id) );
}
}
class Option extends DatabaseObject
{
protected $databaseTable = "Options";
public function __construct($id=0)
{
parent::__construct( intval($id) );
}
}
Every new class your create from DatabaseObject gets all the basic features that your Product object got.
Many Objects
So far I’ve shown what I believe to be a pretty reasonable way to deal with individual database records. This class is built around the notion of one record at a time as an object. You can pull that information in and update it one at a time. So what happens when you need to deal with 100 records at once? Like a list of those products, or showing all the accessories available for a product.
The obvious answer to this is to just create a bunch of those Product objects. It may be obvious, but not ideal.
$indexList = array(12, 32, 14, 1, 87, 23);
foreach ($indexList as $index) {
$prodObj[] = new Product($index);
}
A quick example of creating 6 Product objects each loaded up with all the data associated with each index value. The reason this isn’t an ideal solution is that you had to make 6 seperate database calls. Imagine you needed to get a list of 100 products for 50 people all asking for them at the same time. This approach just doesn’t scale well, especially considering how many different tables are already being needed to get together something presentable. Usually the biggest bottleneck of a dynamic site is getting information out of the database. We want to be as efficient there as possible for the biggest performance gains.
This is heading us down the road to something called an “aggregate class”. Yet another big fancy phrase that means something pretty simple. It’s just a class that has objects as data members. In other words, and object that holds other objects. For what I personally do to deal with this issue is create what I call a list class. This probably has a fancier name than I’m aware of, so you can leave a comment if you know of such a thing.
In a list class I create empty DatabaseObjects then pull in a multi-row result directly from the database engine to fill them in. Notice how in the DatabaseObject class the $index argument isn’t required? In the pullFromDatabase() method I don’t try to get anything from the database unless I have an $index value. Also note that in the setValue() method I check in the $fieldList array rather than the array holding results to determine if the field is valid. These are all little hooks to allow a list class to do all the data filling.
Creating a list class is something I’ll leave for a follow up post. Before getting into reading that, consider how you would approach putting together an inheritable list class for all of these various DatabaseObjects created here. What sort of shared methods could this list employ?
When you start to think in those terms, you are developing in an Object Oriented style.
