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.

24 thoughts on “Automate Db Model Creation with Zend_CodeGenerator_Php_Class”

  1. Hello!

    Nice thing you have posted.

    But i got a problem with it:

    My Apache or PHP get broken when i execute this code at this line:
    // get all fields
    $fields = $db->describeTable($table);

    Is this method to much for localhost or something else?

    For testing i only used a database with 1 table.

  2. Okay….
    I found the exact line:
    public function describeTable(…){
    …..
    echo $sql;
    $stmt = $this->query($sql);
    echo ‘done?’;

    }

    after “echo $sql” he breakes. And in SQL variable where is:
    DESCRIBE `testtable1`

    This works in MySQL Query Browser with the same user/privileges….

    Any idea?

  3. If i understood your problem right an abstract factory would solve it too. The point here is that you need a nearly similar model for each student. So why write/generate a model file for each student if you can have a student factory which does that for you on the fly?

  4. This is very cool. Thanks for sharing.

    I’d be interested in knowing a bit more about your Mapper class and how it interacts with your generated classes. Perhaps a follow-up post?

    Cheers.

  5. David, I’d be glad to give you some resources on that topic.

    Martin Fowler has a great book covering various useful design patterns including the data mapper pattern. Here is a brief description and you can get more information and examples from his book:
    http://martinfowler.com/eaaCatalog/dataMapper.html

    Padraic Brady has an online book he has been working on which uses the data mapper and how to implement it:
    http://www.survivethedeepend.com/zendframeworkbook/en/1.0/implementing.the.domain.model.entries.and.authors

    My implementation of the data mapper is very similar to Padraic Brady’s example. He has lots of useful information in his book as well as his blog: http://blog.astrumfutura.com/

  6. Hello
    your solution for automatic model generation seems very usefull i was just wondering if it works with an application that will use zend_db but not the entire framework?

  7. Lexter, you shouldn’t need the entire framework for Zend_CodeGenerator to work, just make sure you have all the files needed by it. Try to copy the CodeGenerator folder from the zend framework ‘Zend’ folder and include the files inside your project such as: lib\CodeGenerator\Php\Class.php. I haven’t tried this though but give it a shot.

  8. Hey Leonard, I do it like so:
    http://martinfowler.com/eaaCatalog/dataMapper.html

    I call the mapper directly to do basic CRUD operations which uses the DbTable. In the case of fetch/fetchAll on the mapper, the mapper returns an instance or an array of object instances specific to that mapper.

    So, if we are talking about Users, I’ll have a UserModel, UserMapper, UserDbTable (I like to store each in a different folder). If I want to grab all users I would do something like:

    $mapper = new UserMapper();
    $users = $mapper->fetchAll();

    The fetchAll method in the mapper would call the UserDbTable instance fetchAll. For example:

    $table = new UserDbTable();
    return $table->fetchAll();

  9. Hi Joey,

    What I am looking for is a good way to translate the database relations into the model representation in php.

    Lets use a simple blog entry example.

    class Blog_Model_Entry{
    protected $_id;
    protected $_title;
    protected $_content;
    // @var Blog_Model_Category
    protected $_category;
    // @var array of Blog_Model_Tag
    protected $_tags;
    //…
    }

    Aboveyou can see the relation with parent table Category.

    What I am currently doing is: (In the mapper class of the blog entry) I instantiate the category mapper to map the parent row to a category model class. An example:

    // from the blog entry mapper class
    public function map(Zend_Db_Table_Row $row, Blog_Model_Entry $model)
    {
    $model->setId($row->id);
    $model->setTitle($row->title);
    $model->setContent($row->content);

    // find parent row, possible with defined table relation
    $cat = $row->findParentRow(‘Blog_Model_DbTable_Category’);

    // instantiate category, mapper and then map
    $category = new Blog_Model_Category();
    $mapper = new Blog_Model_CategoryMapper();
    $mapper->map($cat, $category);

    // set Blog_Entry_Model category
    $model->setCategory($category);

    // something similar for tags and comments
    //…
    //…
    }

    So to go back to my question; for generating code, how would you look at this or have you found a good solution to generate code in this way (for scenarios where you have 1:1, 1:n, n:m relations)

  10. Leonard,

    I understand your question now, thanks for clearing that up. To best honest, I haven’t taken this code past where it is now. I see this more as a starting point for a project – or a way of quickly recreating files when database tables change. For me, there seems to be little benefits in spending time adding more functionality when there are so many scenarios to be handled.

    I don’t believe describeTable gives you any foreign key information to know if there is a relationship in the table. I guess you would have to start by looking at the ‘table_constraints’ table in ‘information_schema’ to see what relationships exists for you schema (assuming we are talking about MySQL). From there, using the table_name and constraint_type fields, you would be able to know what objects need relationships to each other and where. Hope this helps.

    *Edit: Actually the `information_schema`.`COLUMNS` table has probably everything you need. Shows the table, field, what table and fields are being referenced.

  11. Hi

    I am kinda new to Zend. And this seriously is some smart work done by you here. I didn’t know about CodeGen before so thanks on that part too.

    However, I am a little confused as to how is this code executed to generate the requisite php files.

    Hope you are still reachable through this link.

    Thanks.

    1. Hey Xuhaib, for this example I was using the Zend Framework so if you aren’t currently using it this example would be confusing. You don’t have to use the framework though, if you only use the code generator class by including it into your project and some db connection you should still be able to create a similar working project as this one. I unfortunately don’t have an example handy to show.

  12. Pingback: links | warpgeek

Leave a Reply

Your email address will not be published. Required fields are marked *