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:

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.