Developing software in the Real World

Metadata from Zend_Db_Table_Abstract

This post is part of a series about my experiences building a PHP app for Windows Server 2008 and IIS 7 for the European WinPHP Challenge 2009 which is sponsored by iBuildings, Microsoft and Leaseweb.

I finally found some more time to work on SuccesSQL and can now display table structure information:

successql_structure-1.jpg

Zend_Db_Table provides all this information directly which is quite useful, however the intended use-case for Zend_Db_Table is that you extend Zend_Db_Table_Abstract for each table that you want to interact with. Obviously, SuccesSQL doesn’t know in advance the names of the tables, so I created a stub class that allowed me to instantiate a Zend_Db_Table dynamically:

class SSQL_Db_Table extends Zend_Db_Table_Abstract
{
}

Then, for any given table, I can instantiate like this:

$table = new SSQL_Db_Table(array('name'=>$tableName));

The metadata about the table is stored in a protected member variable called _metadata. As this is protected, it’s not accessible, outside of the class, so I created an accessor method to allow me to get at the data:


class SSQL_Db_Table extends Zend_Db_Table_Abstract
{

public function getMetadata()
{
if (empty($this->_metadata)) {
$this->_setupMetadata();
}
return $this->_metadata;
}

}

Interestingly, Zend_Db_Table_Abstract lazily loads the _metadata variable and so I had to check if it had been loaded and if not, set it up. The data returned is an multi-dimensional array, with one sub-array per field. A dump looks like this:

array
'id' =>
array
'SCHEMA_NAME' => null
'TABLE_NAME' => string 'albums' (length=6)
'COLUMN_NAME' => string 'id' (length=2)
'COLUMN_POSITION' => int 1
'DATA_TYPE' => string 'int' (length=3)
'DEFAULT' => null
'NULLABLE' => boolean false
'LENGTH' => int 4
'SCALE' => int 0
'PRECISION' => int 10
'UNSIGNED' => null
'PRIMARY' => boolean true
'PRIMARY_POSITION' => int 1
'IDENTITY' => boolean true
'artist' =>
array
'SCHEMA_NAME' => null
[etc]

From then on, it’s just a case of display formatting.

5 thoughts on “Metadata from Zend_Db_Table_Abstract

  1. By my experience, the Primary attribute is true even if the field is an external reference (DB2 9.5/Linux).

    I have to check, if the Primary position already exists, and in that case it is an external reference (quite stupid if the primary columns are not on the top of the column list).

  2. As Luke R. noted, the info() method will return this information already. If no arguments are provided, it returns the entire array of metadata; alternately, you can provide a specific metadata key and it will return just that information.

Thoughts? Leave a reply

Your email address will not be published. Required fields are marked *