Location: PHPKode > scripts > MySQL Ajax Database Access Layer - MADAL > DBStructure.php
<?php
// include class file
require_once ("XML/Serializer.php");
require_once 'Constants.php';
require_once 'ClassGenerator.php';
require_once 'libs/JSMin.php';
require_once 'Config.php';
require_once 'Functions.php';

class DBStructure {
	//Parameters:
	private $db_host = DB_HOST;
	private $db_user = DB_USERNAME;
	private $db_pass = DB_PASSWORD;
	public $database = DB_NAME;
	private $connection;

	public $tables = array ();
	/**
	 *
	 */
	function __construct() {
		$this->connection = mysql_connect ( $this->db_host, $this->db_user, $this->db_pass );
		if (! $this->connection) {
			die ( mysql_error () );
		}
		if (! mysql_select_db ( $this->database, $this->connection ))
		die ( mysql_error () );

		$result = mysql_query ( "SHOW TABLES FROM $this->database" );
		while ( $row = mysql_fetch_row ( $result ) ) {
			$table_name = $row [0];
			$this->tables [$table_name] = array ();
			$result_table = mysql_query ( "SHOW FULL COLUMNS FROM $table_name" );
			while ( $row_table = mysql_fetch_row ( $result_table ) ) {
				$field_name = $row_table [0];
				$this->tables [$table_name] [$field_name] = array ();
				foreach ( $row_table as $key => $fieldprop ) {
					$key = GetFieldPropertyType ( $key );
					if ($key != FIELD_NAME_FIELD) {
						$this->tables [$table_name] [$field_name] [$key] = $fieldprop;
					}
				}
			}
		}
	}

	/**
	 * @return the text structure of the tables
	 */
	function printTextStructure() {
		$structure = "";
		foreach ( $this->tables as $table => $fields ) {
			$structure .= "Table: $table \n";
			foreach ( $fields as $field_name => $field_properties ) {
				$structure .= "\tField: $field_name\n";
				foreach ( $field_properties as $field_property => $property_value ) {
					$structure .= "\t\t$field_property: $property_value\n";
				}
			}
		}
		return $structure;
	}

	/**
	 * @return the xml of the tables and changes the header type to text/xml
	 */
	function printXMLStructure() {
		// An array of serializer options
		$serializer_options = array ('indent' => '	', 'typeHints' => TRUE, 'addDoctype' => TRUE, 'addDecl' => TRUE, 'encoding' => 'ISO-8859-1' );
		// create objecth
		$serializer = new XML_Serializer ( $serializer_options );
		$status = $serializer->serialize ( $this );
		if (PEAR::isError ( $status )) {
			throw new Exception ( $status->getMessage () );
		}
		//header ( 'Content-type: text/xml' );
		return $serializer->getSerializedData ();
	}

	/**
	 * Generates a connection file
	 */
	function generateConnectionCode() {
		$conn = "<?php
header('Cache-Control: no-cache, must-revalidate'); // HTTP/1.1
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		
if(!function_exists('session_started')){
	function session_started(){
	    if(isset(\$_SESSION)){ return true; }else{ return false; }
	}
}

if(!function_exists('start_session')){
	function start_session(){
		if(!session_started()){
			session_start();
		}
	}
}

start_session();

//Connection Parameters:
\$db_host = '$this->db_host';
\$db_user = '$this->db_user';
\$db_pass = '$this->db_pass';
\$database = '$this->database';

\$".$this->database."_connection = mysql_connect ( \$db_host, \$db_user, \$db_pass, TRUE );//establish a new connection link
if (! \$".$this->database."_connection) {
	die ( mysql_error () );
}
if (! mysql_select_db ( \$database, \$".$this->database."_connection ))
	die ( mysql_error () );

?>";
		return $conn;
	}

	/**
	 * Writes out all of the database table files
	 * @param string $directory_path The directory path without a trailing /
	 */
	function writeTablesToPHPAndJavascriptObjects() {
		$feedback = "";

		//create folder structure if it doesn't exist
		$base_dir = DOC_ROOT . "/" . DB_NAME . "_mvc";
		$model_dir = $base_dir . "/Model";
		$view_dir = $base_dir . "/View";
		$controller_dir = $base_dir . "/Controller";
		$custom_stubs_dir = DOC_ROOT . "/" . DB_NAME . "_custom";
		$model_includes_dir = MYSQL_DB_CODE_GENERATOR_DIR . "/ModelIncludes";
		$view_includes_dir = MYSQL_DB_CODE_GENERATOR_DIR . "/ViewIncludes";
		$controller_includes_dir = MYSQL_DB_CODE_GENERATOR_DIR . "/ControllerIncludes";

		//Create directory structure
		if (conditionalCreateDirectory ( $base_dir ))
		$feedback .= "Created " . $base_dir . "<br>";
		if (conditionalCreateDirectory ( $model_dir ))
		$feedback .= "Created " . $model_dir . "<br>";
		if (conditionalCreateDirectory ( $view_dir ))
		$feedback .= "Created " . $view_dir . "<br>";
		if (conditionalCreateDirectory ( $controller_dir ))
		$feedback .= "Created " . $controller_dir . "<br>";
		if (conditionalCreateDirectory ( $custom_stubs_dir ))
		$feedback .= "Created " . $custom_stubs_dir . "<br>";
			
		//WRITE STRUCTURE FILES
		WriteToFile ( "$base_dir/" . $this->database . "_Structure.txt", $this->printTextStructure () );
		$feedback .= "Wrote $base_dir/" . $this->database . "_Structure.txt<br>";

		WriteToFile ( "$base_dir/" . $this->database . "_Structure.xml", $this->printXMLStructure () );
		$feedback .= "Wrote $base_dir/" . $this->database . "_Structure.xml<br>";

		//Write CONNECTION file
		WriteToFile ( "$base_dir/Connection.php", $this->generateConnectionCode () );
		$feedback .= "Wrote $base_dir/Connection.php<br>";

		WriteToFile ( $base_dir . "/Constants.php", "<?php
		//constants
		define(\"MYSQL_DB_CODE_GENERATOR_DIR\", \"".MYSQL_DB_CODE_GENERATOR_DIR."\"); //this should point to MySQL DB Code Generator's base directory
		define(\"DOC_ROOT\",\"".DOC_ROOT."\"); //this should point to the directory above MySQL DB Code Generator's base directory
		define ( \"DB_NAME\", \"".DB_NAME."\" ); //the database from which to generate the code
		define ( \"DB_HOST\", \"".DB_HOST."\" ); //the host of the db
		define ( \"DB_USERNAME\", \"".DB_USERNAME."\" ); //the username for the db
		define ( \"DB_PASSWORD\", \"".DB_PASSWORD."\" ); //the password for the db
		?>" );
		$feedback .= "Copied " . "Config.php to $base_dir/Constants.php" . "<br>";

		//COPY STATIC MODEL FILES
		/* DBElement, XML_Serializer*/
		//if (! is_dir ( $model_dir . "/Includes" )) {
		recursiveCopy ( $model_includes_dir, $model_dir . "/Includes" );
		$feedback .= "Copied $model_includes_dir to " . $model_dir . "/Includes<br>";
		//}

		//COPY STATIC VIEW INCLUDE FILES
		/* JQUERY */
		//if (! is_dir ( $view_dir . "/Includes" )) {
		recursiveCopy ( $view_includes_dir, $view_dir . "/Includes" );
		$feedback .= "Copied " . $view_includes_dir . " to $view_dir/Includes" . "<br>";
		//}

		copy ( "web_service.php", $view_dir . "/web_service.php" );
		$feedback .= "Copied " . "web_service.php" . " to $view_dir/web_service.php" . "<br>";

		copy ( "tabbed_class_viewer.php", $view_dir . "/tabbed_class_viewer.php" );
		$feedback .= "Copied " . "tabbed_class_viewer.php" . " to $view_dir/tabbed_class_viewer.php" . "<br>";

		copy ( "foreign_key_objects.php", $view_dir . "/foreign_key_objects.php" );
		$feedback .= "Copied " . "foreign_key_objects.php" . " to $view_dir/foreign_key_objects.php" . "<br>";

		//ONLY COPY ONCE
		if (conditionalCopyFile ( "web_service_security.php", $custom_stubs_dir . "/web_service_security.php" )) {
			$feedback .= "Copied " . "web_service_security.php" . " to $custom_stubs_dir/web_service_security.php" . "<br>";
		}

		//COPY STATIC CONTROLLER FILES
		//if (! is_dir ( $controller_dir . "/Includes" )) {
		recursiveCopy ( $controller_includes_dir, $controller_dir . "/Includes" );
		$feedback .= "Copied " . $controller_includes_dir . " to $controller_dir/Includes" . "<br>";
		//}

		copy ( "Functions.php", $controller_dir . "/Functions.php" );
		$feedback .= "Copied " . "Functions.php" . " to $controller_dir/Functions.php" . "<br>";
		copy ( "Constants.php", $controller_dir . "/Constants.php" );
		$feedback .= "Copied " . "Constants.php" . " to $controller_dir/Constants.php" . "<br>";

		$phpRequirements = "<?php /* Include this file to get all the model classes as require_once */
"; //will contain all the php require_once's for each class


		$javascript_classes = "/* Include this file to get all the model classes as Javascript Classes */
"; //will contain all of the javascript classes	

		//extract all primary and foreign keys so that the ClassGenerator can generate the List getters
		$foreign_keys = $this->getPrimaryAndForeignKeys ();

		//get the php version
		$version = explode('.', PHP_VERSION);
		echo "PHP Version: " . PHP_VERSION . "<br/>";
		if($version[0] < 5){
			die("MADAL currently requires version 5.2 or higher due to needing the json_encode function");
		}
		else if($version[0] == 5 && $version[1] < 2){
			die("MADAL currently requires version 5.2 or higher due to needing the json_encode function");
		}

		foreach ( $this->tables as $table => $fields ) {
			//since views have no primary key, they won't show up in the list of foreign keys for a table, they can't be the table a foreign key is pointing to
			if(!isset($foreign_keys [$table])){
				$foreign_keys [$table] = NULL;
			}
			$generator = new ClassGenerator ( $this->database, $table, $fields, $foreign_keys [$table] );
			$classCode = $generator->generateClassCode ();
			
			//Note: some code for generating classes for earlier than version 5, unfortunately, json_encode is missing from earlier versions, so it's still no go
			if($version[0] < 5){
				$classCode =  str_ireplace("public $", "var $", $classCode);
				$classCode =  str_ireplace("private $", "var $", $classCode);
				$classCode =  str_ireplace("protected $", "var $", $classCode);
				$classCode =  str_ireplace("public function", "function", $classCode);
				$classCode =  str_ireplace("protected function", "function", $classCode);
				$classCode =  str_ireplace("public static function", "function", $classCode);
				//throw new Exception
				$classCode =  str_ireplace("throw new Exception", "print", $classCode);
				//switch to DBElement4.php
				$classCode =  str_ireplace("DBElement.php", "DBElement4.php", $classCode);
				$classCode =  str_ireplace("FieldProperties.php", "FieldProperties4.php", $classCode);
				//__construct
				$classCode =  str_ireplace("__construct", $generator->class_name, $classCode);
			}

			WriteToFile ( "$model_dir/$generator->class_name.php",  $classCode);
			require_once "$model_dir/$generator->class_name.php";
			$feedback .= "Wrote $model_dir/$generator->class_name.php<br>";
			$javascript_classes .= $generator->generateJavascriptClass ();
			WriteToFile ( "$model_dir/$generator->class_name.js", $generator->generateJavascriptClass () );
			$feedback .= "Wrote $model_dir/$generator->class_name.js<br>";
			//add to php includes file
			$phpRequirements .= "require_once dirname(__FILE__).'/$generator->class_name.php';
";
		}

		//write php file that includes all the model php files
		$phpRequirements .= " ?>";
		WriteToFile ( "$model_dir/model_require_once.php", $phpRequirements );

		// write js file that has an unminified version of all the model javascript files
		WriteToFile ( "$model_dir/" . $this->database . "_javascript_classes.js", $javascript_classes );

		// write js file that has a minified version of all the model javascript files
		WriteToFile ( "$model_dir/" . $this->database . "_javascript_classes.min.js", JSMin::minify ( $javascript_classes ) );

		$feedback .= " Files Written Successfully to '$base_dir' directory";
		return $feedback;
	}

	/**
	 * Gets all the primary and foreign keys and puts them into arrays
	 */
	private function getPrimaryAndForeignKeys() {
		$foreign_keys = array (); //[$table]['pointToMe'] = the array of tables that point to this table's primary key
		//[$table][$field] = the table that a field of this table points to
		$primary_keys = array (); //the list of all the primary keys in the db
		$mul_keys = array (); //each entry is a set of tables that have a field which is a MUL key (meaning it points to a particular field in another table)

		$ptr_keys = array(); //the set of fields for a table that are MUL keys (points to a primary key in another table)
		$foreign_ptr_keys = array();

		foreach ( $this->tables as $table => $fields ) {
			foreach ( $fields as $field_name => $field_properties ) {
				if ($field_properties [INDEX_FIELD] == PRIMARY_FIELD) {
					//add to the (primary keys => table name array)
					$primary_keys [$table] = $field_name;

					//if it doesn't exist, construct the mul_keys array for this field
					if (! isset ( $mul_keys [$field_name] )) {
						$mul_keys [$field_name] = array ();
					}
				} else if ($field_properties [INDEX_FIELD] == MULTIPLE_VALUE_INDEX_FIELD) {
					//if it doesn't exist, construct the mul_keys array for this field
					if (! isset ( $mul_keys [$field_name] )) {
						$mul_keys [$field_name] = array ();
					}

					//add to the (multiple index keys => table names array) array
					//$table points has a mul key that points to another table
					array_push ( $mul_keys [$field_name], $table );

					//if it doesn't exist, construct the ptr_keys array for this table
					if (! isset ( $ptr_keys [$table] )) {
						$ptr_keys [$table] = array ();
					}

					//find the set of table
					//field that points to another table
					array_push ( $ptr_keys [$table], $field_name );
				}
			}
		}

		//find the foreign keys by the relationship between $primary_keys and $mul_keys
		foreach ( $primary_keys as $table => $field_name ) {
			//I have a foreign key relationship to the mul_keys tables array
			//this means that all the tables with $field_name as one of their indexed fields, points to me
			$foreign_keys [$table]['pointToMe'] = $mul_keys [$field_name];
		}

		//traverse the array of tables with pointer keys
		foreach($ptr_keys as $table => $my_ptr_keys){
			$foreign_ptr_keys[$table] = array();
			//traverse the set of pointer keys for each table
			foreach($my_ptr_keys as $field){
				$foreign_keys [$table][$field] = array_search($field, $primary_keys);
			}
		}


		return $foreign_keys;
	}

}

//TODO: generate a database_name class that can insert new ones of the database elements
//TODO: create an insert for each type of item

?>
Return current item: MySQL Ajax Database Access Layer - MADAL