Auto generating basic models for a Zend Framework app
Do you have a database with foreign keys and just wish you could have something automatically create your ZF models from it? Well, today that was me. So as a little proof of concept, this is the code I came up with to do it for me…
But before we get to that, a few caveats:
- It’s just a proof of concept
- The output needs updating for proper reference names, etc.
- Outputs everything to screen in one go and doesn’t save the files.
However, it might be handy to someone, so I post it up for your comments.
< ?php require_once 'Zend/Loader.php'; Zend_Loader::registerAutoload(); $config = array( 'host' => 'localhost', 'username' => 'myusername', 'password' => 'mypassword', 'dbname' => 'mydbname' ); try { $db = Zend_Db::factory('pdo_mysql', $config); } catch (Zend_Db_Exception $e) { echo $e->getMessage(); die; } $model = < <<EOT <?php class %s extends Zend_Db_Table_Abstract { protected \$_name = '%s'; %s } EOT; $refmap_outer = <<<EOT protected \$_referenceMap = array( %s ); EOT; $refmap_inner = <<<EOT '%s' => array( 'columns' => array('%s'), 'refTableClass' => '%s', 'refColumns' => array('%s') ) EOT; $toTable = new Zend_Filter_Inflector( ':tbl', array(':tbl' => array('Word_CamelCaseToUnderscore', 'StringToLower')) ); $toClass = new Zend_Filter_Inflector( ':tbl', array(':tbl' => array('StringToLower', 'Word_UnderscoreToCamelCase')) ); foreach ($db->listTables() as $table) { $sql = " select tc.constraint_name, kcu.table_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name from information_schema.table_constraints tc, information_schema.key_column_usage kcu where tc.table_name = " . $db->quote($table) . " and tc.constraint_type = 'FOREIGN KEY' and kcu.constraint_name = tc.constraint_name "; $keys = $db->fetchAll($sql); $refs = array(); if (!empty($keys)) { $r = 0; foreach ($keys as $key) { $refs[] = sprintf($refmap_inner, 'ref' . ++$r, $key['column_name'], $toClass->filter(array('tbl' => $key['referenced_table_name'])), $key['referenced_column_name'] ); } } printf($model, $toClass->filter(array('tbl' => $table)), $toTable->filter(array('tbl' => $table)), (!empty($refs) ? sprintf($refmap_outer, join(",\n", $refs)) : '') ); }
So assuming I had a database structure like:
-- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `added` datetime NOT NULL, `username` varchar(32) NOT NULL, `password` varchar(64) NOT NULL, `name_first` varchar(32) NOT NULL, `name_last` varchar(32) NOT NULL, `name_nick` varchar(32) NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Table structure for table `user_tags` -- CREATE TABLE `user_tags` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` int(10) UNSIGNED NOT NULL, `tag_user_id` int(10) UNSIGNED NOT NULL, `tag` varchar(24) NOT NULL, PRIMARY KEY (`id`), KEY `tag` (`tag`), KEY `fk_user_tags_user_id` (`user_id`), KEY `fk_user_tags_tag_user_id` (`tag_user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Constraints for table `user_tags` -- ALTER TABLE `user_tags` ADD CONSTRAINT `fk_user_tags_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_user_tags_tag_user_id` FOREIGN KEY (`tag_user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
it would output something like:
< ?php
class UserTags extends Zend_Db_Table_Abstract
{
protected $_name = 'user_tags';
protected $_referenceMap = array(
'ref1' => array(
‘columns’ => array(‘user_id’),
‘refTableClass’ => ‘Users’,
‘refColumns’ => array(‘id’)
),
‘ref2’ => array(
‘columns’ => array(‘tag_user_id’),
‘refTableClass’ => ‘Users’,
‘refColumns’ => array(‘id’)
)
);
}
1 Response