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’)
)
);
}