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.
