Automate Db Model Creation with Zend_CodeGenerator_Php_Class

I’m working on a new tool at work that will automate several processes for a few employees so they don’t have to spend too much time doing very repetitive tasks. This tool has to do a good bit of database manipulation so I’ve decided I’ll build it in PHP using Zend Framework.

I’ll be using Zend_Db_Table_Abstract to communicate with the db tables from my project and I’ll be creating a model for each table as well to store and manipulate data. I’ll be working with lots of tables in the database and many have lots of fields.

I start by opening up Zend Studio on one monitor and SQL Query Analyzer on the other and get to work. The first table I want to work with is the ‘Student’ table. I create a new file in my project called Student.php. Place it on my models/DbTable folder and inside I simply have to declare ‘_name’ as a protected property with the value ‘Student’ and extend ‘Zend_Db_Table_Abstract’. Easy enough but now I want to create the model I will be using to convert the database data into workable objects through my mapper class.

Problem

I create a new file called ‘Student.php’ and save it to my models folder. I open the file up and now I have to create a property (it’s actually an array _data with all properties defined as keys inside) for each field in the Student table… all 50 of them! I have to be careful to name each correctly as well as to not accidentally miss some field. It ends up being a time consuming process and inefficient so I start looking for a better way to accomplish this.

Solution

This is where Zend_CodeGenerator_Php_Class comes in. This component of Zend allows you to create php code on the fly. My theory was, instead of manually creating all these files and typing all the field names (very time consuming with lots of room for error), I could use Zend_CodeGenerator_Php to create all the content of my classes and echo it on the screen so I could copy/paste. Once I had this working, I took it to the next step and included the process of saving that content to files on my project folder. After working on this last night, everything worked like a charm and my classes are easily and quickly created, mapped directly to my database structure, by simply calling one method. No room for error either and I can run this at any time!

Code

I’ll explain the code below. You can download the file if you like.

<?php
$db = Zend_Registry::get('db');
$paths = Zend_Registry::get('paths');
$temp_path = $paths['temp'];

// get all tables in db
$tables = $db->listTables();

foreach($tables as $table)
{
    // need to remove underline first, ucwords, and then remove space
    $name = str_replace(' ', '', ucwords(str_replace('_', ' ', $table)));

    // create new class generator
    $class = new Zend_CodeGenerator_Php_Class();

    // configure docblock
    $docblock = new Zend_CodeGenerator_Php_Docblock(array(
	    'shortDescription' => $name . ' model',
	    'tags' => array(
	        array(
	        	'name' => 'author',
	            'description' => 'Joey Rivera',
	        )
		)
	));

	// set name and docblock
    $class->setName('Ct_Model_' . $name);
    $class->setDocblock($docblock);

    // get all fields
    $fields = $db->describeTable($table);

    // want to track primary ids for table
    $primary = array();

    // add to columns each field with a default value
    $columns = array();
    foreach($fields as $field)
    {
    	// if int field default to 0
    	$columns[$field['COLUMN_NAME']] =
    		strpos($field['DATA_TYPE'], 'int') !== false ? 0 : '';

    	// track primary field(s) for table
    	if($field['PRIMARY'])
    	{
    		$primary[] = $field['COLUMN_NAME'];
    	}
    }

    // add data array property to class
    $class->setProperty(array(
		'name' => '_data',
		'visibility' => 'protected',
		'defaultValue' => $columns,
    	'docblock' => array(
    		'tags' => array(
    			new Zend_CodeGenerator_Php_Docblock_Tag(array(
    				'name' => 'var',
    				'description' => 'array Maps to all fields in table'
    				)
    			)
    		)
    	)
	));

	echo $class->generate() . PHP_EOL;
	file_put_contents($temp_path . $name . '.php', '<?php' . PHP_EOL . $class->generate());

	// create zend_db_table_abstract
	$db_class = new Zend_CodeGenerator_Php_Class();
	$db_class->setName('Ct_Model_DbTable_' . $name);
	$db_class->setDocblock(new Zend_CodeGenerator_Php_Docblock(array(
	    'shortDescription' => $name . ' db table abstract',
	    'tags' => array(
	        array(
	        	'name' => 'author',
	            'description' => 'Joey Rivera',
	        )
		)
	)));

	$db_class->setExtendedClass('Zend_Db_Table_Abstract');

	$db_class->setProperty(array(
		'name' => '_name',
		'visibility' => 'protected',
		'defaultValue' => $table,
		'docblock' => array(
			'tags' => array(
				new Zend_CodeGenerator_Php_Docblock_Tag(array(
					'name' => 'var',
					'description' => 'string Name of db table'
				))
			)
		)
	));

	if(count($primary))
	{
		$db_class->setProperty(array(
			'name' => '_primary',
			'visibility' => 'protected',
			'defaultValue' => count($primary) > 1 ? $primary : $primary[0],
			'docblock' => array(
				'tags' => array(
					new Zend_CodeGenerator_Php_Docblock_Tag(array(
						'name' => 'var',
						'description' => 'string or array of fields in table'
					))
				)
			)
		));
	}

	echo $db_class->generate() . PHP_EOL;
	file_put_contents($temp_path . 'DbTable/' . $name . '.php', '<?php' . PHP_EOL . $db_class->generate());
}

I start out by getting some needed variables such as my $db adapter and paths that I will use to store my files in. For this example I am using MySQL (5.1.x) database and Zend Framework 1.9.5. If you are not familiar on how to setup your database adapter here is the documentation.

The next step is to call listTables on the db adapter to get an array of all the tables in the database. We want to loop through the array and create two files for each table: an object model and a db table abstract. Each file has a corresponding instance of Zend_CodeGenerator_Php_Class. The first one we create in the code is for the object model. The docblock is the optional comments for the class if you want them. setName is the name you want for the class. In this case I’m using ‘Ct_Model_$name’ where name is the name of the table without any underscores and with the first letter of each word in uppercase. For example, if the table name is: ‘user_info’, name is ‘UserInfo’.

The next information I need is all the fields for that table so I can add them to my data array for each model. Calling describeTable(‘tableName’) returns that. Now that I have my array with all the fields information for this first table, so I loop through to:

• assign names and default value(s) of 0 for an int field type or null for anything else into the columns array.
• track the primary key(s) for each table. Some tables can have more than one.

setProperty is where we create the protected data property array for this class. The name of the property is ‘_data’ and the default value is the array we just created ‘columns’. You can also add a docblock to the property which I did here. Since there is no Zend_CodeGenerator_Php_Docblock_Tag_Property I just used the default Tag class passing it name ‘var’. Had I used Zend_CodeGenerator_Php_Docblock_Tag_Param, my comments would have shown ‘@param’ when it is really ‘@var’. Try it and you’ll see what I mean.

Our first class for the model is now complete! I’m echoing it to the screen so I can see it when I view-source or just use Zend_Debug::dump() instead to see it formatted. The next line saves the newly generated code to a file at a temp_path location. It’s important to add ‘<?php’ before class->generate() so your file have the php tag in them.

The next part of the code creates the second class that extends Zend_Db_Table_Abstract. I won’t go into detail with what is happening there since it’s very similar to the above. If you have any questions do feel free to ask. That’s pretty much it. Run this code, make sure you have your directories created and after executing you should now have two files for each table in your database.

Here is an example of what you should see after running the page:

/**
 * Bio model
 *
 * @author Joey Rivera
 */
class Ct_Model_Bio
{
    /**
     * @var array Maps to all fields in table
     */
    protected $_data = array(
        'bio_id' => 0,
        'user_id' => 0,
        'bio_type_id' => 0,
        'title' => null,
        'content' => null,
        'last_update' => null,
        'enabled' => 0
        );
}
/**
 * Bio db table abstract
 *
 * @author Joey Rivera
 */
class Ct_Model_DbTable_Bio extends Zend_Db_Table_Abstract
{
    /**
     * @var string Name of db table
     */
    protected $_name = 'bio';
    /**
     * @var string or array of fields in table
     */
    protected $_primary = 'bio_id';
}

This is a work in progress since I’m still trying different things. After I finished writing this code I went back to the zend docs and noticed there is Zend_CodeGenerator_Php_File. Seems to do the same thing I did above with file_put_contents. Feel free to try that instead.

Conclusion

I really enjoy the flexibility of using this method instead of typing everything out myself. It’s quicker with a lot less room for error. I’m sure there are already other programs/apps out there that do this but hey, it’s more fun to reinvent the wheel! Plus I learned about another Zend Framework module that I had not used before.

EDIT:

I had to make to small edits to this post to work more efficiently. The first was to change the line where we set the default value of each field for the columns array to ” instead of null. The second was to set the primary property only if there was a primary field found on that table since not every table will always have one.