Hack 39. Export Database Schema as XML

Hack 39. Export Database Schema as XML

Use PHP to read the schema from your database and export it as XML for documentation or code generation.

It can be handy to have a dump of the current database schema for several reasons. First, you can use it to generate PHP for database access [Hack #37]. You can also use it to compare two versions of a schema to build a migration script for software upgrades.

5.7.1. The Code

schema.php is shown in Example 5-23.

Example 5-23. Script that extracts XML for a database schema representation

<?php
$dbuser = "root";
$dbpassword = "password";
$dbserver = "localhost";
$dbname = "wordpress";

$db = mysql_connect( $dbserver, $dbuser, $dbpassword );
	
mysql_select_db( $dbname );
	
$tables_res = mysql_query( "SHOW TABLES FROM ".$dbname, $db );
$tables = array();
while( $tableinfo = mysql_fetch_row($tables_res) ) {
	$tables[] = $tableinfo[ 0 ];
}
mysql_free_result( $tables_res );

header( "content-type: text/xml" );
?>
<schema>
<?php foreach( $tables as $table ) { ?>
<table name="<?php echo( $table ); ?>">
<?php
$fields_res = mysql_query( "SHOW FIELDS FROM ".$table, $db );
while( $fieldinfo = mysql_fetch_row($fields_res) ) {
?>
<field
	name="<?php echo( $fieldinfo[0]); ?>"
	type="<?php echo( $fieldinfo[1]); ?>"
	/>
<?php }
mysql_free_result( $fields_res );
?>
</table>
<?php } ?>
</schema>

This small script reads the schema from a MySQL database and outputs XML that describes the schema to the console (of course, you can pipe this output to a file). The script starts by defining the connection to the database through a set of constants. Then the script connects to the database and finds out what tables are available using SHOW TABLES. Next, the script iterates over each table and uses SHOW FIELDS to find the fields for each table. All of the returned information is dropped into XML, formatted on the fly by the script.

5.7.2. Running the Hack

Use the command-line version of PHP to run this script, like so:

	% php schema.php
	<schema>
	<table name="wp_categories">
	<field
	  name="cat_ID"
	  type="bigint(20)"
	  />
	<field
	  name="cat_name"
	  type="varchar(55)"
	  />
	<field
	  name="category_nicename"
	  type="varchar(200)"
	  />
	<field
	  name="category_description"
	  type="longtext"
	  />
	<field
	  name="category_parent"
	  type="int(4)"
	  />
	…

In this example, I pointed the script at my WordPress database, which is fairly complex. The XML result has a base schema tag that contains a table tag for each table. Within each table tag, the fields are listed with individual field tags that specify a name and a type.

This script is specific to MySQL. Reflection queries such as SHOW TABLES and SHOW FIELDS are available for other databases but are specified slightly differently; you should be able to make a few changes to get this running on your database of choice.

5.7.3. See Also

• "Generate Database SQL" [Hack #41]

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

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