ADOdb's Active Record Implementation and Data Dictionary in PHP

ADOdb’s Active Record Implementation and Data Dictionary in PHP

Posted May 13th, 2009 in ADOdb, PHP and tagged ActiveRecord, ADOdb, database schema, Databases, functionality, PHP, Programming, xml schema by Aaron

The Active Record Pattern is a method developed to help access and store data in relational databases. This small post will cover some of the basic Active Record methods that you can use with the ADOdb Library in PHP. When used properly, the Active Record Pattern can help you develop and visualize your data faster than just throwing database queries into your applications.

Here is a quote from Wikipedia detailing exactly what the Active Record Pattern is:

From: Wikipedia on the Active Record Pattern
Active record is an approach to access data in a database. A database table or view is wrapped into a class, thus an object instance is tied to a single row in the table. After creation of an object, a new row is added to the table upon save. Any object loaded gets its information from the database; when an object is updated, the corresponding row in the table is also updated. The wrapper class implements accessor methods or properties for each column in the table or view.

Essentially what we are doing is building a class for our tables in our database, then using the objects created to control what is coming in and out of the database. Also, for this post, I am going to be referencing the same contact table that was created and covered in our first ADOdb post, Getting started with ADOdb for PHP. Now, not to worry if you do not have the database already created, because in this article we also are going to cover dynamically generating table schema with XML using the ADOdb Data Dictionary.

To get started, lets first write the class that will control our contacts table.

An ADOdb_Active_Record Class

Each one of our tables that we want to access via the Active Records Method needs a class wrapped around it. The class we are going to create is going to be called contact and will extend the ADOdb_Active_Record class. The extending of the ADOdb class will give us all the basic functionality we will need in our class. You also will need to include one more file into your header, as well as change the way your database adapter is called.

require_once("adodb/adodb.inc.php");
require_once('adodb/adodb-active-record.inc.php');
$db = NewADOConnection($config['dbtype']);
$db->debug = true;
$db->Connect($config['server'], $config['username'], $config['password'], $config['database']);
ADOdb_Active_Record::SetDatabaseAdapter($db); // This line of code establishes DB connection to work with ActiveRecords.

Now lets make an extremely simple table wrapper class.

class contact extends ADOdb_Active_Record{
	var $_table = 'contacts'; // We need to specify what the name of the table is for this class.
}

It’s pretty simple, nothing here is required of the class, except for the $_table variable, and that is only required if your table name differs from the name of the class. Since the name of our class is contact and the table is named contacts, we must specify what the table is named.

Organizing how you wish to place your classes and files is up to you, but if you are looking for ideas, here is the generic directory structure that I use to setup my project.

   project/
      db/
         wrappers/
            contact.php
         schemas/
            contact.xml
      index.php

The wrappers folder contains all of the classes for the tables, while the schemas folder contains all of the XML files used to build/maintain the databases. However, for this example, I am just going to leave them all in the same file or same folder.

Building a method to Maintain our Schema

This is a small method I normally include in all of my wrappers to build the databases. You could easily build a class of your own that the wrappers extend, then have your new class extend the ADOdb class, to prevent yourself from repeating code. However, in this example, we are going for simplicity and keeping everything in one place and making it work. First off you are going to need to include the proper files from ADOdb, including adodb/adodb-xmlschema.inc.php. This file is the file that will do the parsing and execution of the XML files you will create.

So, now lets make an XML Schema file that will control the format of our table.

<?xml version="1.0"?>
<schema version="0.2">
  <table name="contacts">
    <field name="id" type="I">
      	<descr>A unique ID assigned to each contact.</descr>
      	<KEY/>
      	<AUTOINCREMENT/>
    </field>
    <field name="FirstName" type="C" size="50">
				<NOTNULL/>
		</field>
    <field name="LastName" type="C" size="50">
    		<NOTNULL/>
    </field>
    <field name="Phone" type="C" size="15">
    		<NOTNULL/>
    </field>
    <field name="Email" type="C" size="100">
    		<NOTNULL/>
    </field>
  </table>
</schema>

The layout of the XML is pretty easy to understand if you just glance over it. Essentially it is the schema of our database written out in a simple to use XML format. Each table and each field receives its own nodes and parts of the XML. More documentation on exactly what you can include into this XML can be found in the Data Dictionary Documentation, about 1/2 way down the page. You are going to need to save this file in the same area that you are working in, I saved it as contacts.xml.

Building an Initialization Method for the Schema

Now that we have our XML written for the table, now we need to parse it and include it into our table. For this, I normally would make a method called “init” on my wrapper that calls the proper XML file, parses it and executes any updates needed to the table all while maintaining the data within the table. Here is what our table wrapper would look like with this class embedded in it.

class contact extends ADOdb_Active_Record{
	var $_table = 'contacts'; // We need to specify what the name of the table is for this class.

	// This is a function I created, to work with the XML DB schema
	function init() {
		global $db; // We need to bring in the $db adapter
		$schemaFile = "contacts.xml"; // This is the file of the schema
		$schema = new adoSchema($db); // Establish a new Schema Object, and pass in the DB parameters
		$schema->ParseSchema($schemaFile); // Parse through the schema and read it into the object
		$schema->ExecuteSchema(); // Execute the Schema, and update the table if needed
	}
}

The code above is pretty well commented, essentially we are telling the object which file it should be using as its schema, creating a new schema object, parsing the schema then executing any actions required to update the table. If the script you were running had to build tables on the fly, this might be an excellent way to accomplish it. The main reason I use these XML schema is for rapid deployment across multiple servers. No more porting data from one database server to another, instead have an init call that will build your tables for you.

Alright, now that the wrapper object is created, we can move into the actual application portion of the code. So far, if you are using the code from the last example, your code should look something like this.

require_once("adodb/adodb.inc.php");
require_once('adodb/adodb-active-record.inc.php');
require_once("adodb/adodb-xmlschema.inc.php");

$config['server'] = "localhost";
$config['dbtype'] = "mysql";
$config['username'] = "root";
$config['password'] = "";
$config['database'] = "adodb_test";  

$db = NewADOConnection($config['dbtype']);
$db->debug = true;
$db->Connect($config['server'], $config['username'], $config['password'], $config['database']);

// This line of code establishes DB connection to work with ActiveRecords.
ADOdb_Active_Record::SetDatabaseAdapter($db);

// This is a special class for your table, extending the ADOdb_Active_Record class.
class contact extends ADOdb_Active_Record{
	var $_table = 'contacts'; // We need to specify what the name of the table is for this class.

	// This is a function I created, to work with the XML DB schema
	function init() {
		global $db; // We need to bring in the $db adapter
		$schemaFile = "contacts.xml"; // This is the file of the schema
		$schema = new adoSchema($db); // Establish a new Schema Object, and pass in the DB parameters
		$schema->ParseSchema($schemaFile); // Parse through the schema and read it into the object
		$schema->ExecuteSchema(); // Execute the Schema, and update the table if needed
	}
}

This is all of the code you should need to get started working with the contacts table, besides the contacts.xml file which we covered in the last section. Now, anytime you need to recreate the table or update the schema of the contacts table, you need to call 2 lines of code.

// First off, we create a new instance of the contacts table wrapper object
$contact = new Contact();
// Then, if needed, we run the init() command to create/update the table as the schema sees fit.
$contact->init();

If you have debugging on, you will see the SQL commands the Active Records method is going to generate automatically for you. You could add or remove columns, modify data types, or even rename columns from the XML, and once these lines of code execute, it will correct your table to match the schema. This is incredibly useful when you are developing an application across multiple servers and need to ensure that the database schemas across all development platforms are the same structure.

Inserting a new record into the Contacts Table

Well, running the above code should give you the table at least, you may want to insert some dummy records into the table, so lets show you how to create a new record in the table.

// Lets insert a new record from scratch
$newPerson = new Contact();
// Set all of their values for the database
$newPerson->firstname = "Aaron";
$newPerson->lastname = "Cox";
$newPerson->phone = "555-012-3456";
$newPerson->email = "aaroncox@emailaddress.com";
// And finally save the person in a new record
$newPerson->save();
// Wondering what their Database ID was? Well it autopopulates it into the field upon saving.
echo $newPerson->id;
// Echoing the $newPerson->id will display their new ID on the page.

All we have to do to create a new table record is create a new instance of the Active Record class, and start populating the properties of the object. Once complete, call the ->save() method on the object and poof, your record is properly saved into the database. The object is also updated with the proper ID that was auto generated by the table, you can see this on the last 2 lines of the example above, as I am echoing the content out to the screen.

When I started with Active Records, using this method helped me a tremendous amount when trying to figure out what variables I was setting to what values. Reading code like this is so extremely simple and effective.

Populating an Active Record by ID

Another incredibly useful method you can use with Active Records is to load records, then update them. To load a record, you simply use 1 line of code.

$contact->load("id = 1");
$contact->firstname = "ChangedName";
$contact->save();

The first line will run the SQL query and load ID = 1 into the object, making it look something like this:

contact Object
(
    [_table] => contacts
    [_dbat] => 0
    [_tableat] => contacts
    [_where] => id = 1
    [_saved] => 1
    [_lasterr] =>
    [_original] => Array
        (
            [0] => 1
            [1] => Joe
            [2] => User
            [3] => 545-454-5454
            [4] => joe@joeuser.com
        )

    [id] => 1
    [firstname] => Joe
    [lastname] => User
    [phone] => 545-454-5454
    [email] => joe@joeuser.com
)

You can do this via any search method you can use in SQL as well, here are a few examples. Keep in mind however that the “LOAD” method will only return one record. After the record was loaded, I am changing the firstname value to “ChangedName” and then proceeding to save the record. Once again, with debugging on, you will see the library writing the SQL for you to update that record to the value you chose to set it to.

Here are a few more examples of ways you can load data using the load method.

$contact->load("firstname = 'John'"); // Finds all with firstname IS John
$contact->load("firstname like 'John%'"); // Finds all with firstname LIKE John%
$contact->load("firstname like 'John%'"); // Finds all with firstname LIKE John%

Returning an Array of Objects using FIND

If you want to return multiple results into an array of objects, you can use the find method to do so. Find functions the same way as load, except it returns the values back into the array specified. We are going to use the same examples above, except as arrays with multiple objects inside them.

$contacts = $contact->find("firstname = 'John'"); // Finds all with firstname IS John
print_r($contacts); // Dump out the results on screen

$contacts = $contact->find("firstname like 'John%'"); // Finds all with firstname LIKE John%
print_r($contacts); // Dump out the results on screen

$contacts = $contact->find("firstname like 'John%'"); // Finds all with firstname LIKE John%
print_r($contacts); // Dump out the results on screen

Now once you have these record sets, you can easily loop through them and treat them individually like their own objects, and even update them. In my opinion, is much easier than writing a query, executing it, loading the results into a recordset and then accessing them.

foreach($contacts as $singlecontact) {
	echo $singlecontact->firstname." ".$singlecontact->lastname."
";
	$singlecontact->email = "email@address.com";
	$singlecontact->save();
}

These are all of the basic principals of the Active Record Implementation and the XML Data Dictionary Schema for ADOdb. There are a lot more things you can do with the Active Records, which I will hopefully dive into at a later date. The documentation on the ADOdb Website provides a LOT of examples that are practical to use, and they are what I used to learn the library myself and implement them in my day to day work.