Hack42.Generate Database Select Code

Hack 42. Generate Database Select Code

Use PHP to build code for database access directly from an XML description of the schema.

Building database access classes for SQL tables can require a lot of annoying, error-prone grunt work. In this hack, I use an XML file that describes a database schema and a code generator written in PHP to create the PHP classes automatically.

I used the same schema.xml file that I use in this hack, to generate the corresponding SQL [Hack #41].

Figure 5-6 illustrates how the abstract schema XML is taken as input by the generator. The generator in turn creates the PHP classes in the mydb.php file.

This output file is temporary and you should never edit it directly.

Figure 5-6. The flow of the PHP SQL Select generator

5.10.1. The Code

schema.xml, representing the database, is shown in Example 5-27.

Example 5-27. XML representing a database schema

 <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> 

As when generating CRUD code [Hack #37], you'll need a database wrapper; save Example 5-28 as dbwrap.php.

Example 5-28. A wrapper providing database-specific access information

 <?php require_once( "DB.php" ); $dsn = 'mysql://root:password@localhost/books'; $db =& DB::Connect( $dsn, array() ); if (PEAR::isError($db)) { die($db->getMessage()); } function selectOne( $sql, $args ) {   global $db;   $res = $db->query( $sql, $args );   $res->fetchInto($row, DB_FETCHMODE_ASSOC);   return $row; } function selectBlock( $sql, $args ) {   global $db;   $res = $db->query( $sql, $args );   $rows = array();   while( $res->fetchInto($row, DB_FETCHMODE_ASSOC) ) { $rows []= $row; }   return $rows; } ?> 

Next, save the code in Example 5-29 as gen.php. Here is where the actual code generation takes place.

Example 5-29. The script that does the actual 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(); echo( "<?php\n" ); ?> require_once( "dbwrap.php" ); <?php foreach( $tables as $table ) {   $pk = null;   foreach( $table['fields'] as $field ) {     if ( $field['pk'] ) $pk = $field['name']; } ?> class <?php echo( ucfirst( $table['name'] ) ) ?> {   function getOne( $id )   {     return selectOne( "SELECT * FROM <?php echo( $table['name'] ) ?> WHERE <?php echo( $pk ); ?> = ?", array( $id ) );   }   function getAll()   {     return selectBlock( "SELECT * FROM <?php echo( $table['name'] ) ?>", array() );   } } <?php } echo( "?>" ); $php = ob_get_clean(); $fh = fopen( "mydb.php", "w" ); fwrite( $fh, $php ); fclose( $fh ); ?> 

This generator code starts by reading the XML into an in-memory data structure. Then it starts buffering the output into a string and creates a class for each table using standard PHP templating techniques. Once all of the class code for the tables is created, the output buffering is turned off and the buffered output is stored into a string, which is then saved into a file using fopen( ), fwrite(), and fclose().

5.10.2. Running the Hack

Use the command-line version of PHP to run the generator against the XML schema file:

 php gen.php < schema.xml 

This will create a file called mydb.php in the same directory, much like this:

 <?php require_once( "dbwrap.php" ); class Book {   function getOne( $id )   {     return selectOne( "SELECT * FROM book WHERE id = ?", array( $id ) );   }   function getAll()   {     return selectBlock( "SELECT * FROM book", array() );   } } class Publisher {   function getOne( $id )   {   return selectOne( "SELECT * FROM publisher WHERE id = ?", array( $id ) );   }   function getAll()   {   return selectBlock( "SELECT * FROM publisher", array() );   } } class Author {   function getOne( $id )   {     return selectOne( "SELECT * FROM author WHERE id = ?", array( $id ) );   }   function getAll()   {   return selectBlock( "SELECT * FROM author", array() );   } } ?> 

How cool is that! PHP that creates PHP!

The script starts by parsing through the schema file and then uses simple PHP to create the code that uses the functions in dbwrap.php to query the data from the database. Each class has two functions: one gets all of the items in the table, and the other gets just a single record.

To test this, create index.php (shown in Example 5-30) and use it to create a Publisher object to query the publisher table.

Example 5-30. A script that tests the database code

 <?php require_once( "mydb.php" ); $pub = new Publisher(); ?> <html> <body> <table> <?php $rows = $pub->getAll(); foreach( $rows as $row ) { ?> <tr><td><?php echo( $row['id'] ); ?></td> <td><?php echo( $row['name'] ); ?></td></tr> <?php } ?> </table> </body> </html> 

Use your browser to navigate to the page to see the contents of the publisher table. Figure 5-7 shows the publisher table in the browser.

5.10.3. See Also

• "Generate Database SQL" [Hack #41]

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