Location: PHPKode > projects > QuickerBooks > QuickerBooks/DBBuilder.php
<?php /* Created 30 April 2007 */

/** This file is used to generate the class files used for database work
 * I didn't want to have to write all that stuff over and over so here you go
 * 
 * In order to use it, just get in in a browser:
 *  - If you give it a table param it will only build that table. 
 *  - If your table is `All` then all tables will be built.
 *  - If you give in nothing, it will provide options for you to build.
 */
 
require_once('autoload.php');

// this is made for the workforce and workforcedev databases
// but you could use it on any :)
$params = array_merge($_POST, $_GET);
$db = 'QuickerBooks-dev';
if (@$params['db'] != null) {
	$db = $params['db'];
}

// we need to get in with more power
// so we go in as root
Database::init('localhost', 'root', 'mycon30', $db);

print '<h1>Database Object Builder</h1>';
	

// get all the tables in the database
print 'working dir: '.getcwd();
$result = Database::execute('Show tables');
//printTable($result,array("Tables In $db"));

// get all the tables into an array
$tables = $result->toArray();
$htmloptions = '';
foreach ($tables as $table) {
	$htmloptions .= '<option value="'.$table.'">'.$table.'</option>'."\n";	
}

$dir = '.\classes\data\schema\\';
if (isset($params['location'])) {
	$dir = $params['location'];	
}
	
$form = 
'
<form action="" method="get">
	<div>select a table: 
		<select name="table">
			<option value="all">All Tables</option>
			'.$htmloptions.'
		</select>
	</div>
	<div>select a location: 
		<input type="type" name="location" size="60" value="'.htmlentities($dir).'" />
	</div>
	<div>
		<input type="submit" value="Go" />
	</div>
</form>
<hr />
';

print $form;


// generate?
if (isset($params['table'])) {
	
	
	//$dir = "Source/Generated/";
	if (!is_dir($dir)) {
		mkdir($dir);
		Debug::display("'$dir' directory created");
	}
		
	if ($params['table'] != 'all') {
		$tables = array($params['table']);	
	}
	
	// show all tables description
	foreach($tables as $table) {
		$result = Database::execute('Describe `'.$table.'`');
		printTable($result, array($table));	
		
		$cols = $result->toArray();
		$query = new SelectQuery('information_schema.KEY_COLUMN_USAGE', array('*'), 
			array(	"TABLE_SCHEMA='$db'",
					"(`TABLE_NAME` = '$table' OR `REFERENCED_TABLE_NAME` = '$table')"
				));
		$result = Database::executeQuery($query);
		printTable($result, array($table));
			
		$keys = $result->toArray();
		$classes[$table] = new DBBuilder();
		$classes[$table] = $classes[$table]->build($table, $cols, $keys, $dir);
	}
}

class DBBuilder {
	
	protected $pkeys = array();
	protected $fkeys = array();
	protected $ffkeys = array();
	protected $columns = array();
	protected $defaults = array();
	
	function __construct() {
		
	}
	
	function getTypes($cols) {
		
		// Gather Columns
		foreach($cols as $col) {
			
			// add field
			$type = null;
			$reg = null;
			$info = array();
			ereg("^([a-z_][a-z_0-9]*)\(?([^)]+)?\)?[ ]?(.+)?\$", $col['Type'], $reg);
			switch($reg[1]) {
				case "varchar": 
					$type = "String";
					$info['max-length'] = $reg[2];
				case "text": 
					$type = "String";
					break;
				case "bigint":
				case "int": 
					$type = "Integer"; 
					break;
				case "tinyint":
					if ($reg[2] == 1) {
						$type = "Boolean";	
					}
					else {
						$type = "Integer";	
					}
					break;
				case "double": 
					$type = "Double"; 
					break;
				case "decimal": 
					$type = "Double";
					break;
				case "datetime":
					$type = "Datetime"; 
					break;
				case "time":
					$type = "Time"; 
					break;
				case "date": 
					$type = "Date"; 
					break;
				case "enum":
					$type = "Enumeration";
					$info['Options'] = explode("','", substr($reg[2],1,strlen($reg[2])-2));
					break;
				default: 
					$type = "Object ".$reg[1]." ".$reg[2];
			}
			//Debug::display($col['Field']." $type (".$col['Type'].") Nulls?: ".($col['Null'] == "YES"? "True":"False"));
			
			$info['Field'] = $col['Field'];
			$info['Type'] = $type;
			$info['Null'] = ($col['Null'] == "YES");
			if (! $col['Default'] == null) {
				$info['Default'] = $col['Default'];
				$this->defaults[$col['Field']] = $col['Default'];
			}
			
			//Debug::display($info);
			
			$this->columns [$col['Field']] = $info;
		}
	}
	
	function getKeys($table, $keys) {
		
		$unique = array();
		
		// Gather Keys
		foreach($keys as $key) {
			//**TODO: tables that reference themselves...?
			if ($key['REFERENCED_TABLE_NAME'] == $table) {
				//Debug::display("Foreign Foreign Key found: ".$key['REFERENCED_COLUMN_NAME']." -> " . $key['TABLE_NAME'] .".".$key['COLUMN_NAME']);
				$this->ffkeys[$key['REFERENCED_COLUMN_NAME']] = array($key['TABLE_NAME'] => $key['COLUMN_NAME'] );
				$this->columns[$key['REFERENCED_COLUMN_NAME']]['Referenced'][$key['TABLE_NAME']] = $key['COLUMN_NAME'];
			}
			elseif ($key['CONSTRAINT_NAME'] == "PRIMARY") {
				//Debug::display("Primary Key found: $table -> ".$key['COLUMN_NAME']);
				$this->pkeys[] =  $key['COLUMN_NAME'];
				$this->columns[$key['COLUMN_NAME']]['Primary'] = 1;
			}
			elseif ($key['TABLE_NAME'] == $table) {
				if ($key['COLUMN_NAME'] != '' && $key['REFERENCED_TABLE_NAME'] != '') {
					//Debug::display("Foreign Key found: ".$key['COLUMN_NAME']." -> " . $key['REFERENCED_TABLE_NAME'] .".".$key['REFERENCED_COLUMN_NAME']);
					$this->fkeys[$key['COLUMN_NAME']] = array($key['REFERENCED_TABLE_NAME'] => $key['REFERENCED_COLUMN_NAME']);
					$this->columns[$key['COLUMN_NAME']]['Foreign'] = array($key['REFERENCED_TABLE_NAME'], $key['REFERENCED_COLUMN_NAME']);
				}
				// check for a unique constraint
				else if ($key['POSITION_IN_UNIQUE_CONSTRAINT'] != 1) {
					//Debug::display($key);
					if (isset($unique[$key['CONSTRAINT_NAME']])) {
						$unique[$key['CONSTRAINT_NAME']][$key['ORDINAL_POSITION']] = $key['COLUMN_NAME'];
					}
					else {
						$unique[$key['CONSTRAINT_NAME']] = 
							array($key['ORDINAL_POSITION'] => $key['COLUMN_NAME']);
					}
				}
			}
		}
		
		// process unique keys
		foreach ($unique as $u) {
			Debug::display($u);
			foreach ($u as $col) {
				Debug::display($col);
				$this->columns[$col]['Unique'] = $u;
			}
		}
	}
	
	function output($location, $table) {
				
		$class = 
'<?php
class db_'.$table.' extends DBObject {
	
	function __construct($id = null) {
		$this->table = \''.$table.'\';
		$this->pkeys = array(\''.implode("','",$this->pkeys).'\');
		$this->fkeys = '.arrayToText($this->fkeys, 3).';
		$this->ffkeys = '.arrayToText($this->ffkeys, 3).';
		$this->columns = array(\''.implode("','",array_keys($this->columns)).'\');
		$this->info = '.arrayToText($this->columns, 3).';
		$this->nvalues = '.arrayToText($this->defaults, 3).';

		if($id != null) {
			$this->load($id);
		}
	}

}
?>';
		$file = fopen($location."db_".$table.".php", 'w');
		if (! fwrite($file, $class)) {
			Debug::message('Couldnt write class: '.$table);
		}
		fclose($file);
	}
	
	function build($table, $cols, $keys, $location = '') {	
		//Debug::display($cols);
					
		$this->getTypes($cols);
		$this->getKeys($table, $keys);
		$this->output($location, $table);

	}

	/** 
	 * This function is able to determine generally what type of table a given
	 * table is.
	 */
	function getTableType() {
		// check for foreign keys
		$linker = false;
		if (count($this->fkeys) > 1) {
			$linker = true;	
		}
		
		// check for referencing tables
		
		// check for attributes
	}

}
function arrayToText($array, $level = 0) {
	
	if (count($array) > 0) {
		$str = "array(\n";
		
		foreach ($array as $name=>$val) {
			if (is_array($val)) { 
				$val = arrayToText($val, $level + 1); 
			}
			else {
				if (stripos ($val, 'new ') !== false) {
					//Debug::message($val);
					
				} else {
					$val = "'$val'";
				}
			}
			
			if (is_numeric($name)) {
				$str .= str_repeat("\t",$level) . "$val,\n";
			}
			else {
				$str .= str_repeat("\t",$level) . "'$name' => $val,\n";
			}
		}
		$str .= str_repeat("\t",$level) .")";
	}
	else {
		$str = "array()";	
	}
	return $str;
}

function printTable(DBResult $result, $headers=null) {
	print "<table border='1'>";
	if ($headers != null) {
		print "<tr>";
		foreach($headers as $name=>$val) {
			print "<th>$val</th>";
		}
		print "</tr>";
	}
	while($row = $result->fetchRow()) {
		print "<tr>";
		foreach($row as $name=>$val) {
			print "<td>$val</td>";
		}
		print "</tr>";
	}
	print "</table>";
}

?>
Return current item: QuickerBooks