Auto generating basic models for a Zend Framework app

Last modified date

Comment: 1

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]< ?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 = < < 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)) : ”)
);
}
[/php]

So assuming I had a database structure like:

[sql]–
— 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;[/sql]

it would output something like:

[php]< ?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’)
)
);
}

Share

1 Response

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.