<?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>";
}
?>