TW Blog :: Archives for February 2004

MySQL Schema Via PEAR::DB_DataObject

Relates to PHP and MySQL, PEAR

I have been updating some of my PEAR classes and stumbled upon the DB_DataObject package. I have actually been trying to implement a data modelling layer within my PHP CMS project for some time, and this package is a comprehensive collection of methods to achieve just that abstraction. Configuration was relatively painless and with a set of data objects ready to go I have been testing their application in the CMS API.

In the original interface, I was parsing initialisation files (parse_ini_file) to load the table schema from which the appropriate form or table model could be constructed utilising a mix of PEAR's HTML packages and custom classes. The main problem with this was that changes in the MySQL table schema had to be manually updated in the ini file.

When a set of data objects are created with DB_DataObject::createTables(), an ini file is also created that records the database schema. This file is parsed internally when a new data object is instantiated for a particular entity. The table schema is instantly available by calling DB_DataObject::table().

$obj =& DB_DataObject::factory('data_object');
$dbo_schema = $obj->table();
echo "<pre>";
foreach($dbo_schema as $fld_name => $fld_type)
{
  printf("%-20s", $fld_name);
  if ($fld_type & DB_DATAOBJECT_INT) echo "int ";
  if ($fld_type & DB_DATAOBJECT_STR) echo "str ";  
  if ($fld_type & DB_DATAOBJECT_DATE) echo "date ";  
  if ($fld_type & DB_DATAOBJECT_TIME) echo "time ";  
  if ($fld_type & DB_DATAOBJECT_BOOL) echo "bool ";  
  if ($fld_type & DB_DATAOBJECT_TXT) echo "txt ";  
  if ($fld_type & DB_DATAOBJECT_BLOB) echo "blob ";  
  if ($fld_type & DB_DATAOBJECT_NOTNULL) echo "not null ";  
  if ($fld_type & DB_DATAOBJECT_MYSQLTIMESTAMP)
     echo "timestamp ";  
  echo "\n";
}
echo "</pre>";

The returned schema contains key/value pairs where the key is the field (attribute) name, and the value is the datatype(s) as defined when the ini file is created. The datatype value is conditionally tested against the named constants with the bitwise & operator to determine the actual types defined. This works fine for a table model, where the schema fields and a generalised data type is all I require [the table model uses DOM scripting to allow column reordering based on general data types]. However, for the form model, I need to know the maximum allowed size for fields, default values and other metadata to compose accessible and usable form controls. For this the obvious solution is to fall back to the MySQL layer itself, and run a "SHOW COLUMNS" SQL query.

A PEAR::DB object is already a persistant property of the instantiated data object, so if this can be retrieved a standard DB::getAll() query can be run.

$db =& $obj->getDatabaseConnection();
$mysql_schema = $db->getAll("SHOW COLUMNS FROM "
                              . $obj->tableName());
echo "<pre>";
foreach($mysql_schema as $field)
{
  printf("%-8s %-25s %-10s %-6s %-20s\n",
         $field[0], $field[1],
         (empty($field[2]) ? "[ NOT NULL ]" : ""),
         $field[3], $field[4]);  
}
echo "</pre>";

DB_DataObject::getDatabaseConnection() returns a reference to the PEAR::DB object. To keep the code snippet general DB_DataObject::tableName() is called to retrieve the table name within the SQL query. Example output of this code is:

id      smallint(5)   unsigned  [ NOT NULL ]   PRI
name    varchar(80)             [ NOT NULL ]
url     varchar(100)                           MUL
version float(3,1)              [ NOT NULL ]          1.0
amended timestamp(14)
added   timestamp(14)

This provides all the data I need to create a corresponding set of form controls. If the CMS model is amending an entity, loading the data is just a case of calling $obj->get(id), and the data can be updated with $obj->update() once it has been passed through a data validator, and the new values assigned to the object's properties.

Next is to put the SQL builder aspect of DB_DataObject to test with some of the more complex triple join queries currently in the CMS model…

Posted on Saturday, Feb 28, 2004 at 00:52:09.