TW Blog :: Entries for MySQL

MySQL Schema Via PEAR::DB_DataObject

Also relates to PHP and 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 Feb 28, 2004 at 00:52:09. [Comments for MySQL Schema Via PEAR::DB_DataObject- 1]

Selecting MySQL Next And Previous Revisited

Also relates to Databases

The other day I discussed a simple technique for selecting the next and previous records from a MySQL database using variables. Here is a little tip for extending this to limit the results to a defined number of most recent entries.

Having selected the unique ID for the next and previous records, as discussed in the previous blog entry, another variable can be defined which counts the number of records posted more recently than the previous record. Then the previous entry is only returned if it is within a predefined boundary for recent entries. Here is the full SQL:

# set the boundary to the most recent five results
SET @lower_bound := 5;

# set the id of the previous entry
SELECT @prev := uid 
       FROM table 
       WHERE uid < {x} 
       ORDER by uid DESC 
       LIMIT 0, 1;

# set the id of the next entry
SELECT @next := uid 
       FROM table
       WHERE uid > {x}
       ORDER by uid ASC
       LIMIT 0, 1;
       
# get the number of records posted more 
# recently than the previous record
SELECT @records := COUNT(uid)
       FROM table
       WHERE uid > @prev
       ORDER by uid DESC;

# create a return result array, but only include
# the previous record if it falls within the boundary
# of most recent records
SELECT IF(uid = @prev,'p','n') as pos, uid, title 
       FROM table
       WHERE (uid = @prev AND @records < @lower_bound) 
              OR uid = @next

A typical application of this solution is distinguishing latest news items from the news archive in a small scale News Desk (i.e. where new items may only be added occasionally so the date is not a reliable factor for determining the latest posts). By integrating a couple of simple mod-rewrite rules in the configuration or .htaccess file, and a cache module (eg PEAR::Cache_DB) to compensate for the additional database overhead a complete News Desk with latest items and archive items can be readily simulated through a single source.

Posted on Feb 08, 2004 at 04:30:40. [Comments for Selecting MySQL Next And Previous Revisited- 1]