Web App Generator: database metadata

This is an obsolete article, and left here as a placeholder until a better one can be written.

This is a snapshot of GGDatabase.class.php, a bit of code to construct objects that hold metadata about a MySQL table.

<?php
/**
 * Copyright 2006 Slaptech.net
 */
$base = '../';
include_once('../config/config.inc.php');

/*
 * Database and table metadata section
 */

class GGDatabaseMetaData {
	var $tables = array();
}
class GGTableMetaData {
	var $fields = array();
	function GGTableMetaData( $table )
	{
		$db = new Database();
		$sql = "SELECT * FROM $table";
		$db->query( $sql );
		$numFields = mysql_num_fields($db->result);
		$table  = mysql_field_table($db->result, 0);
		for ( $i=0; $i < $numFields; $i++ ) 
		{
			$name  = mysql_field_name($db->result, $i);
			$type  = mysql_field_type($db->result, $i);
			$len   = mysql_field_len($db->result, $i);
			$flags = mysql_field_flags($db->result, $i);
			$this->fields[$name] = 
				new GGFieldMetaData( $name, $type, $len, $flags );
		}
	}
	function fieldNames( $excluding=array() )
	{
		return array_diff( array_keys( $this->fields ), $excluding );
	}
	/**
	 * @returns a string like `id`,`name`
	 */
	function SQLFieldList( $ex=array() )
	{
		return '`'.join('`,`',$this->fieldNames($ex) ).'`';
	}
	/**
	 * @returns a string like %d,`%s`
	 */
	function SprintfFormatList( $ex=array() )
	{
		$formats = array();
		foreach( $this->fieldNames($ex) as $f )
		{
			switch( $this->fields[$f]->type )
			{
				'number': 'int': 'integer': 'decimal': 'real':
					array_push( $formats, '%d' ); break;
				default:	
					array_push( $formats, "'%s'" ); break;
			}
		}
		return join( ',', $formats );
	}
	/**
	 * @returns a string like addslashes($this->id), addslashes($this->name)
	 */
	function SprintfArgumentList( $ex=array() )
	{
		return 'addslashes($this->'.join( '), addslashes($this->', $this->fieldNames($ex) ) . ')';
	}


}
class GGFieldMetaData {
	var $name;
	var $type;
	var $len;
	var $flags;
	var $not_null;
	var $primary_key;
	var $auto_increment;
	var $multiple_key;
	function GGFieldMetaData( $name, $type, $len, $flags )
	{
		$this->name				= $name;
		$this->type 			= $type;
		$this->len 				= $len;
		$this->flags 			= $flags;
		$this->not_null 		= is_int(strpos( $flags, 'not_null' ));
		$this->primary_key 		= is_int(strpos( $flags, 'primary_key' ));
		$this->auto_increment 	= is_int(strpos( $flags, 'auto_increment' ));
		$this->multiple_key 	= is_int(strpos( $flags, 'multiple_key' ));
	}
}

/*
$md = new GGTableMetaData( 'sapphos_collections' );
echo '<pre>';
print_r( $md->SQLFieldList() );
print '<p>';
print_r( $md->SprintfFormatList() );
print '<p>';
print_r( $md->SprintfArgumentList() );
print '<p>';
print_r( $md );
// */


?>

Comments

This is legacy code, and there are a few things I don't like about it. First is that it gets the field names by analyzing the result of a SELECT statement. Second, the Sprintf* and SQLFieldList methods are just utility functions and need to be more comprehensive (support more data types). Third, obviously, GGDatabaseMetadata isn't done. That's a class that could iterate over all the tables, constructing all the table metadata objects.

GGDatabaseMetadata should be lazy, so it gets metadata on demand.

GGTableMetatada needs a concept of a primary key (an array of fields that are the PK), and a foreign key. This requires augmenting the information from MySQL with external information about FKs.

GGDatabaseMetadata might be replaced with another class, GGDataSet, similar to an ADO.NET DataSet. So, maybe the thing there needs to be some kind of GGDataSet, and the DatabaseMetadata could be a dataset that maps to a database. (An ADO.NET dataset is a set of tables and table-like objects that, together, can be queried.)

Revisions

I did a little code cleanup, because the original was a little verbose.

The code was revised to move the field list exclusion feature into fieldNames(). It eliminated a temporary variable, and around 3 lines of code per affected function.

The logic in SprintFormatList() was altered to be a little shorter. The catenation was altered, and the code is now more obvious. The previous version looked like this:

	function SprintfFormatList( $excluding=Null )
	{
		$fields = $this->fieldNames();
		if ($excluding)
			$fields = array_diff( $fields, $excluding );
		foreach( $fields as $f )
		{
			if (in_array($this->fields[$f]->type, 
					array('number','int','integer','decimal','real')))
				$output .= ', %d';
			else
				$output .= ', \'%s\'';
		}
		return ltrim( $output, ',' );
	}

The changes were simple. The $excluding default was changed to array(). That's usable as a parameter to array_diff(). The first three lines of the body were eliminated, and is now done in $this->fieldNames( $ex ). The If statement became a Switch, and the constants were written just a little differently. The output isn't catenated anymore: it's accumulated into an array, and the output is stringified with a join(). Here's the new code, for comparison:

	function SprintfFormatList( $ex=array() )
	{
		$formats = array();
		foreach( $this->fieldNames($ex) as $f )
		{
			switch( $this->fields[$f]->type )
			{
				'number': 'int': 'integer': 'decimal': 'real':
					array_push( $formats, '%d' ); break;
				default:	
					array_push( $formats, "'%s'" ); break;
			}
		}
		return join( ',', $formats );
	}

15 lines versus 19 lines. More uniform indentation, and clearer code.