PHP Schema Generator

So here’s an interesting project. This is a spaghetti-mess of php code that produces a very simple DAL by reverse engineering a mysql database. All you have to do is pass it the connection information and database name and it will read in all of the tables and their field types producing a simplistic but complete data access layer for every table in that database.

You get a ‘database’ object from which you can pull out any ‘table’ object. The table objects have collections for field names and descriptors which identify the field properties such as datatype, length, key status, etc. Each table object can load an active row by primary key (only single integer auto-increment primary keys are currently supported) and it generates the necessary select. You can update a loaded record or create a new record from scratch. Call ’save’ and it produces the update or insert statement necessary to create or update the data in the database.

Now this isn’t something you’re likely going to want to use in production code as building the schema for the entire database on every page load is quite a bit more work than any given page likely needs to do. You could probably shove the loaded schema into the session but again, with big databases that’s probably not a bright idea. However, for phase 2 (and the original reason I started the project), once a schema is loaded you will have the ability to export a set of schema-specific php classes with all of the load/save functionality and named accessors and mutators instead of the potentially ambiguous string based collections used in the generator. Eventually I’d like to make it smart enough to follow the relations and produce relation-based accessors like the real ORM tools out there but that’s a bit wishful.

Regardless, I’m pretty darn pleased with the amount of functionality I was able to squeeze into 365 lines of php code (minus the comments :))

Here’s a php sample that uses the schema generator. Obviously this won’t work for you as is since it’s my db but you get the idea:

<?php
	//include the generator
	require_once('PHPSchemaGenerator.php');
	//Create the 'database' level object
	$db = new DB_Schema();
	//Load the schema for the entire database
	$db->Load_Full_Schema('vmx-ubuntu', 'MRCClients', 'user', '******');
	//Now were going to spit out all of the table/field properties
	foreach($db->getTables() as $table)
	{
		//Print the table name in bold
		echo "<h3>".$table->getSchemaName()."</h3><br />";
		//Iterate through the field descriptor collection
		foreach($table->getField_Descriptor_Collection() as $field)
		{
			//Print out all available properties
			echo "Name: " . $field->getName();
			echo "<br />DataType: " . $field->getDataType();
			echo "<br />Length: " . $field->getLength();
			echo "<br />CanNull: " . $field->getCanNull();
			echo "<br />KeyType: " . $field->getKeyType();
			echo "<br />DefaultValue: " . $field->getDefaultValue();
			echo "<br />Extra: " . $field->getExtra();
			echo "<br /><br />";
		}
	}
	//Now we'll play with some updates
	//Get the 'Task' table
	$tbl = $db->Table("Task");
	if($tbl == null) echo "No table";
	//Load the first task, this generates a SELECT
	$tbl->Load(1);
	//Print out the current title
	echo "Current first task is " . $tbl->Value("Title");
	//Append ' Supreme' to the title
	$tbl->setValue("Title", $tbl->Value("Title") . " Supreme");
	//This generates and executes an 'UPDATE'
	$tbl->Save();
	//Spit out the new name
	echo "<br /><br />Curent first task is now " . $tbl->Value("Title");
	//Clear the current record
	$tbl->Clear();
	//Set the fields on the table
	$tbl->setValue("Title", "Test Title");
	$tbl->setValue("ProjectID", 1);
	$tbl->setValue("ParentID", 0);
	$tbl->setValue("Description", "Test Description");
	$tbl->setValue("Status", "1");
	$tbl->setValue("Priority", 10);
	//This generates and executes an 'INSERT'
	$tbl->Save();
	//Spit out the current title
	echo "<br /><br />Curent first task is now " . $tbl->Value("Title");
?>

The source is here. Be warned, it will be probably be changing often.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

Back to Blog