Auto generating basic models for a Zend Framework app

Published
by
Andy
on April 11, 2008
in Code snippets, Experiments, PHP and Zend Framework
. Tags: , , , .

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.

PLAIN TEXT
PHP:
  1. <?php
  2.  
  3. require_once 'Zend/Loader.php';
  4. Zend_Loader::registerAutoload();
  5.  
  6. $config = array(
  7.     'host'   => 'localhost',
  8.     'username' => 'myusername',
  9.     'password' => 'mypassword',
  10.     'dbname'   => 'mydbname'
  11. );
  12. try {
  13.     $db = Zend_Db::factory('pdo_mysql', $config);
  14. } catch (Zend_Db_Exception $e) {
  15.     echo $e->getMessage();
  16.     die;
  17. }
  18.  
  19. $model = <<<EOT
  20. <?php
  21.  
  22. class %s extends Zend_Db_Table_Abstract
  23. {
  24.     protected \$_name = '%s';
  25. %s
  26. }
  27. EOT;
  28.  
  29. $refmap_outer = <<<EOT
  30.     protected \$_referenceMap   = array(
  31. %s
  32.     );
  33. EOT;
  34.  
  35. $refmap_inner = <<<EOT
  36.         '%s' => array(
  37.             'columns'         => array('%s'),
  38.             'refTableClass'  => '%s',
  39.             'refColumns'        => array('%s')
  40.         )
  41. EOT;
  42.  
  43. $toTable = new Zend_Filter_Inflector(
  44.     ':tbl',
  45.     array(':tbl' => array('Word_CamelCaseToUnderscore', 'StringToLower'))
  46. );
  47.  
  48. $toClass = new Zend_Filter_Inflector(
  49.     ':tbl',
  50.     array(':tbl' => array('StringToLower', 'Word_UnderscoreToCamelCase'))
  51. );
  52.  
  53. foreach ($db->listTables() as $table) {
  54.     $sql = "
  55.         select
  56.             tc.constraint_name,
  57.             kcu.table_name,
  58.             kcu.column_name,
  59.             kcu.referenced_table_name,
  60.             kcu.referenced_column_name
  61.         from
  62.             information_schema.table_constraints tc,
  63.             information_schema.key_column_usage kcu
  64.         where
  65.             tc.table_name = " . $db->quote($table) . "
  66.             and tc.constraint_type = 'FOREIGN KEY'
  67.             and kcu.constraint_name = tc.constraint_name
  68.         ";
  69.     $keys = $db->fetchAll($sql);
  70.  
  71.     $refs = array();
  72.     if (!empty($keys)) {
  73.         $r = 0;
  74.         foreach ($keys as $key) {
  75.             $refs[] = sprintf($refmap_inner,
  76.                     'ref' . ++$r,
  77.                     $key['column_name'],
  78.                     $toClass->filter(array('tbl' => $key['referenced_table_name'])),
  79.                     $key['referenced_column_name']
  80.                 );
  81.         }
  82.     }
  83.     printf($model,
  84.         $toClass->filter(array('tbl' => $table)),
  85.         $toTable->filter(array('tbl' => $table)),
  86.         (!empty($refs) ? sprintf($refmap_outer, join(",\n", $refs)) : '')
  87.     );
  88. }

So assuming I had a database structure like:

PLAIN TEXT
SQL:
  1. --
  2. -- Table structure for table `users`
  3. --
  4. CREATE TABLE `users` (
  5.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  6.   `added` datetime NOT NULL,
  7.   `username` varchar(32) NOT NULL,
  8.   `password` varchar(64) NOT NULL,
  9.   `name_first` varchar(32) NOT NULL,
  10.   `name_last` varchar(32) NOT NULL,
  11.   `name_nick` varchar(32) NOT NULL,
  12.   PRIMARY KEY  (`id`),
  13.   KEY `username` (`username`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  15. -- --------------------------------------------------------
  16. --
  17. -- Table structure for table `user_tags`
  18. --
  19. CREATE TABLE `user_tags` (
  20.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  21.   `user_id` int(10) UNSIGNED NOT NULL,
  22.   `tag_user_id` int(10) UNSIGNED NOT NULL,
  23.   `tag` varchar(24) NOT NULL,
  24.   PRIMARY KEY  (`id`),
  25.   KEY `tag` (`tag`),
  26.   KEY `fk_user_tags_user_id` (`user_id`),
  27.   KEY `fk_user_tags_tag_user_id` (`tag_user_id`)
  28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  29. --
  30. -- Constraints for table `user_tags`
  31. --
  32. ALTER TABLE `user_tags`
  33.   ADD CONSTRAINT `fk_user_tags_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  34.   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:

PLAIN TEXT
PHP:
  1. <?php
  2.  
  3. class UserTags extends Zend_Db_Table_Abstract
  4. {
  5.     protected $_name = 'user_tags';
  6.     protected $_referenceMap    = array(
  7.         'ref1' => array(
  8.             'columns'         => array('user_id'),
  9.             'refTableClass'  => 'Users',
  10.             'refColumns'        => array('id')
  11.         ),
  12.         'ref2' => array(
  13.             'columns'         => array('tag_user_id'),
  14.             'refTableClass'  => 'Users',
  15.             'refColumns'        => array('id')
  16.         )
  17.     );
  18. }
  19.  
  20. <?php
  21.  
  22. class Users extends Zend_Db_Table_Abstract
  23. {
  24.     protected $_name = 'users';
  25. }

0 Responses to “Auto generating basic models for a Zend Framework app”

Feed for this Entry Trackback Address
  1. No Comments

Leave a Reply

You must login to post a comment.