Hack41.Generate Database SQL

Hack 41. Generate Database SQL

Use PHP to create SQL scripts automatically from a database schema represented as XML.

One of the most common problems with writing database code is the PHP code getting out of sync with the database's structure, or vice versa. Generally we use a .sql script to preload the database with the tables and data required to run the application. But this SQL script can be a pain to maintain, especially when you need to update the PHP that references the SQL tables at the same time.

This hack presents some simple scripts to build SQL and PHP automatically from an XML description of the database. This will ensure that the SQL and the PHP are kept in sync. It also means that if you change database servers or versions of PHP, you can still use the same schema.xml file. All you need to do is change the generator to emit code for a different type of server or PHP version.

Figure 5-5 shows the program flow with the schema.xml file being used as input to the generator codewritten in PHPthat creates the MySQL file, which will in turn create the database.

Figure 5-5. The generator creating SQL from a schema XML file

5.9.1. The Code

Save the sample XML document representing a database schema in Example 5-25 as schema.xml.

Example 5-25. XML representing the database structure

 <schema> <table name="book">   <field name="id" type="int" primary-key="true" />   <field name="title" type="text" />   <field name="publisher_id" type="int" />   <field name="author_id" type="int" /> </table> <table name="publisher">   <field name="id" type="int" primary-key="true" />   <field name="name" type="text" /> </table> <table name="author">   <field name="id" type="int" primary-key="true" />   <field name="name" type="text" /> </table> </schema> 

Save the generation code shown in Example 5-26 as gen.php.

Example 5-26. A script that handles database and code generation

 <?php $tables = array(); function start_element( $parser, $name, $attribs ) {   global $tables;   if ( $name == "TABLE" )   {     $table = array(); $fields = array(); $table['name'] = $attribs['NAME']; $table['fields'] = array(); $tables []= $table;   }   if ( $name == "FIELD" )   {     $field = array(); $field['name'] = $attribs['NAME']; $field['type'] = $attribs['TYPE']; $field['pk'] = ( $attribs['PRIMARY-KEY'] == "true" ) ? 1 : 0; $tables[count($tables)-1]['fields'] []= $field;   } } function end_element( $parser, $name ) { } $parser = xml_parser_create(); xml_set_element_handler($parser, "start_element", "end_element" ); while( !feof( STDIN ) ) {   $text = fgets( STDIN );   xml_parse( $parser, $text ); } xml_parser_free( $parser ); ob_start(); foreach( $tables as $table ) { $pk = null; ?> DROP TABLE IF EXISTS <?php echo( $table['name'] ) ?>; CREATE TABLE <?php echo( $table['name'] ) ?> ( <?php $first = 1; foreach( $table['fields'] as $field ) { ?> <?php echo( $first ? "" : "," ) ?> <?php echo( $field['name'] ) ?> <?php echo( $field['type'] ) ?> <?php if ( $field['pk'] ) { $pk = $field['name']; ?> NOT NULL AUTO_INCREMENT<?php } ?> <?php $first = 0; } ?> <?php if ( $pk ) { ?>  ,primary key( <?php echo( $pk ) ?> ) <?php } ?> ); <?php } $sql = ob_get_clean(); $fh = fopen( "db.sql", "w" ); fwrite( $fh, $sql ); fclose( $fh ); ?> 

5.9.2. Running the Hack

Use the command-line PHP interpreter to run the code:

 php gen.php < schema.xml 

This will create a db.sql file that contains code like this (obviously, your results will vary with different databases and tables):

 DROP TABLE IF EXISTS book; CREATE TABLE book ( id int NOT NULL AUTO_INCREMENT ,title text ,publisher_id int ,author_id int ,primary key( id ) ); DROP TABLE IF EXISTS publisher; CREATE TABLE publisher ( id int NOT NULL AUTO_INCREMENT ,name text ,primary key( id ) ); DROP TABLE IF EXISTS author; CREATE TABLE author ( id int NOT NULL AUTO_INCREMENT ,name text ,primary key( id ) ); 

This SQL code creates the table to match the schema described in the schema.xml file. The XML contains all of the tables and their fields in an XML format and can be used to generate both SQL [Hack #42] and the PHP that uses it [Hack #37]. That way, the SQL and PHP never go out of sync.

Never update the db.sql file manually. Always make revisions to the schema XML file and then rerun the generator to create the new SQL.

5.9.3. See Also

• "Generate Database Select Code" [Hack #42]

• "Generate CRUD Database Code" [Hack #37]