<?php
/**
 * I hold mysql methods
 *
 * @name MySQLService
 * @author  Jonnie Spratley
 * @version 1.0
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 *
 */
class MySQLConnect
{
	public $mysqli;
	
	/**
	 * I hold methods to alter a mysql database
	 *
	 * @param [string] $host
	 * @param [string] $username
	 * @param [string] $password
	 */
	public function __construct( $host, $username, $password )
	{
		$link = new mysqli ( $host, $username, $password );
		
		/* check connection */
		if ( mysqli_connect_errno () )
		{
			trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
			exit ();
		}
		else
		{
			$this->setMysqli ( $link );
		}
	}
	
	/**
	 * I execute a query
	 *
	 * @param [string] $sql
	 * @return [array]
	 */
	public function execute( $sql )
	{
		return $this->queryToARRAY ( $sql );
	}
	
	/**
	 * I get the databases
	 *
	 * @return [array]
	 */
	public function getDatabases()
	{
		return $this->queryToARRAY ( "SHOW DATABASES" );
	}
	
	/**
	 * I execute a raw query
	 *
	 * @param [string] $query
	 * @return [link]
	 */
	public function realQuery( $query )
	{
		return $this->mysqli->query ( $query );
	}
	
	/**
	 * I start the tree
	 *
	 * @return [array]
	 */
	public function tree_getTree()
	{
		$mysqlVersion = mysqli_get_client_info ( $this->mysqli );
		$host = $_SERVER [ 'HTTP_HOST' ] . " (MySQL v. $mysqlVersion )";
		
		$hostArray = array ( 
			'label' => $host, 'type' => 'server', 'children' => $this->tree_getSchemas () 
		);
		$treeArray [] = array ( 
			'label' => 'SQL Databases', 'type' => 'servers', 'children' => $hostArray 
		);
		
		return json_encode ( $treeArray );
	}
	
	/**
	 * I build the tree
	 *
	 * @return [array]
	 */
	private function tree_getSchemas()
	{
		//Database query     
		$databaseSQL = $this->realQuery ( "SHOW DATABASES" );
		
		//New database array
		$databases = array ();
		
		//Loop the query
		while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
		{
			//Create a new array of tables for each database
			$tables = array ();
			$status = array ();
			$size = array ();
			
			foreach ( $database as $key => $value )
			{
				//Set the table array to get the tbles from the database 
				$tables = $this->tree_db_getTables ( $value );
				$status = $this->_db_getStatus ( $value );
				$size = $this->_db_getSize ( $value );
			}
			
			//Add the tables to the database array
			$databases [] = array ( 
				"aDatabase" => $value, "aData" => $key, "aType" => "database", "aIcon" => "database", "aStatus" => $status, "aSize" => $size, "aTables" => $tables 
			);
		}
		
		$databaseFolder [] = array ( 
			'label' => 'Schemas', 'children' => $databases 
		);
		
		return $databaseFolder;
	}
	
	/**
	 * I get the users auth
	 *
	 * @return [array]
	 */
	private function tree_db_getAuthorizations()
	{
		$authorizationsArray = array ( 
			'label' => 'Authorization IDs', 'children' => array ( 
			'label' => 'rfd' 
		) 
		);
		
		return $authorizationsArray;
	}
	
	//TODO:
	private function tree_db_getDependcenies( $database )
	{
		$dependceniesArray = array ( 
			'label' => 'Dependcencies', 'children' => array ( 
			'label' => 'test' 
		) 
		);
		
		return $dependceniesArray;
	}
	
	//TODO:
	private function tree_db_getStoredProcs( $database )
	{
		$storedProcsArray = array ( 
			'label' => 'Stored Procedures', 'children' => array ( 
			'label' => 'test' 
		) 
		);
		
		return $storedProcsArray;
	}
	
	/**
	 * I get the tables
	 *
	 * @param [string] $database the database
	 * @return [array]
	 */
	private function tree_db_getTables( $database )
	{
		//table query
		$tableSQL = $this->realQuery ( "SHOW TABLES FROM $database" );
		
		//create a new array of tables
		$tables = array ();
		
		//loop all the results
		while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
		{
			$columns = array ();
			$statuss = array ();
			$indexes = array ();
			$constraints = array ();
			$dependicies = array ();
			$triggers = array ();
			
			//for each table in the result make an array
			foreach ( $table as $t_key => $t_value )
			{
				//get the tables fields for each table
				$columns = $this->tree_tbl_getColumns ( $database, $t_value );
				
				//now get the primary key for each table
				$constraints = $this->tree_tbl_getConstraints ( $database, $t_value );
				
				//now get the indexes for each table
				$indexes = $this->tree_tbl_getIndexes ( $database, $t_value );
				
				//now get the dependencys for each table
				$dependicies = $this->tree_tbl_getDependcenies ( $database, $t_value );
				
				//now get the triggers for each table
				$triggers = $this->tree_tbl_getTriggers ( $database, $t_value );
				
				//now get the status for each table
				$statuss = $this->_tbl_getStatus ( $database, $t_value );
			
			}
			
			$columnArr = $columns;
			$constraintArr = $constraints;
			$indexArr = $indexes;
			$dependencyArr = $dependicies;
			$triggerArr = $triggers;
			$statusArr = $statuss;
			$tables [] = array ( 
				"label" => $t_value, "type" => "table", "icon" => "table", "children" => array ( 
				$columnArr, $constraintArr, $indexArr, $dependencyArr, $triggerArr, $statusArr 
			) 
			);
		}
		
		$tableFolder [] = array ( 
			'label' => 'Tables', 'children' => $tables 
		);
		
		return $tableFolder;
	}
	
	//TODO:
	private function tree_db_getUserFunctions( $database )
	{
	
	}
	
	//TODO:
	private function tree_db_getViews( $database )
	{
	
	}
	
	/**
	 * I get the columns
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function tree_tbl_getColumns( $database, $table )
	{
		$sql = "SHOW FIELDS FROM $database.$table";
		$query = $this->realQuery ( $sql );
		
		$columnsArray = array ();
		
		while ( $row = mysqli_fetch_row ( $query ) )
		{
			$type = strtoupper ( $row [ 1 ] );
			$null = '';
			
			//Check if the column can be null
			if ( $row [ 2 ] == 'YES' )
			{
				$null = 'Nullable';
			}
			$type = '[' . $type . ' ' . $null . ']';
			
			$columnsArray [] = array ( 
				'label' => $row [ 0 ] . ' ' . $type 
			);
		}
		//Create the folder
		$columnsFolder = array ( 
			'label' => 'Columns', 'children' => $columnsArray 
		);
		
		return $columnsFolder;
	}
	
	/**
	 * I get the primary keys
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function tree_tbl_getConstraints( $database, $table )
	{
		$sql = "SHOW INDEX FROM $database.$table";
		$result = $this->realQuery ( $sql );
		$constraintArray = array ();
		
		while ( $constraint = mysqli_fetch_assoc ( $result ) )
		{
			//check if the key is the primary key
			if ( $constraint [ 'Key_name' ] == 'PRIMARY' )
			{
				$constraintArray = array ( 
					'label' => $constraint [ 'Key_name' ] 
				);
			}
		}
		$constraintFolder = array ( 
			'label' => 'Constraints', 'children' => array ( 
			$constraintArray 
		) 
		);
		
		return $constraintFolder;
	}
	
	//TODO:
	/**
	 * I get the dependcencies
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function tree_tbl_getDependcenies( $database, $table )
	{
		$dependArray = array ( 
			'label' => 'admin table' 
		);
		
		$dependFolder = array ( 
			'label' => 'Dependencies', 'children' => array ( 
			$dependArray 
		) 
		);
		
		return $dependFolder;
	}
	
	/**
	 * I get the indexes
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function tree_tbl_getIndexes( $database, $table )
	{
		$sql = "SHOW INDEX FROM $database.$table";
		$query = mysqli_query ( $this->mysqli, $sql );
		
		$indexArray = array ();
		
		while ( $row = mysqli_fetch_row ( $query ) )
		{
			if ( $row [ 2 ] !== 'PRIMARY' )
			{
				$indexArray [] = array ( 
					'label' => $row [ 4 ] . "($row[2])" 
				);
			}
		}
		
		$indexFolder = array ( 
			'label' => 'Indexes', 'children' => $indexArray 
		);
		
		return $indexFolder;
	}
	
	//TODO:
	/**
	 * I get the triggers
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function tree_tbl_getTriggers( $database, $table )
	{
		$triggerArray = $this->queryToARRAY ( "SHOW INDEX FROM $database.$table" );
		
		$triggerFolder = array ( 
			'label' => 'Triggers', 'children' => array ( 
			$triggerArray 
		) 
		);
		
		return $triggerFolder;
	}
	
	/**
	 * I get the table status
	 *
	 * @param [string] $database
	 * @param [string] $table
	 * @return [array]
	 */
	private function _tbl_getStatus( $database, $table )
	{
		return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database LIKE '$table'" );
	}
	
	/**
	 * I get the size of all the databases
	 *
	 * @param [string] $database the database
	 * @return [array]
	 */
	private function _db_getSize( $database )
	{
		$statusSQL = $this->realQuery ( "SHOW TABLE STATUS FROM $database" );
		$sizeArray = array ();
		
		$totalSize = 0;
		$dataSize = 0;
		$indexSize = 0;
		
		//loop all the results
		while ( $size = mysqli_fetch_assoc ( $statusSQL ) )
		{
			$dataSize += $size [ 'Data_length' ];
			$indexSize += $size [ 'Index_length' ];
		}
		$totalSize = $dataSize + $indexSize;
		$sizeArray [] = array ( 
			'totalSize' => $totalSize, 'dataSize' => $dataSize, 'indexSize' => $indexSize 
		);
		
		return $sizeArray;
	}
	
	/**
	 * I get the status of the all the tables for a database.
	 *
	 * @param [string] $database the database
	 * @return [array]
	 */
	private function _db_getStatus( $database )
	{
		return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database" );
	}
	
	/**
	 * I execute a query and return the results as json.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [json] the result in json
	 */
	private function queryToJSON( $sql )
	{
		$result = $this->realQuery ( $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			$array [] = $row;
		}
		return json_encode ( $array );
	}
	
	/**
	 * I execute a query and return the result as an array.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [array] the result array
	 */
	public function queryToARRAY( $sql )
	{
		$query = $this->realQuery ( $sql );
		$array = array ();
		
		while ( $row = mysqli_fetch_assoc ( $query ) )
		{
			$array [] = $row;
		}
		
		return $array;
	}
	
	/**
	 * I get the query status
	 *
	 * @param [string] $sql
	 * @return [json] mysql status with the ('_') striped out
	 */
	public function queryStatusToJSON( $sql )
	{
		$result = $this->realQuery ( $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			//replace some of the names
			$row = str_replace ( 'Com_', '', $row );
			//take out the _ of the rows
			$row = str_replace ( '_', ' ', $row );
			
			$array [] = $row;
		}
		sort ( $array );
		
		return json_encode ( $array );
	}
	
	/**
	 * I dump vars
	 *
	 * @param [string] $title the title of the dump
	 * @param [var] $var the var
	 */
	public function dump( $title, $var )
	{
		print "<h4>$title</h4>";
		print "<pre>";
		print_r ( $var );
		print "</pre>";
	}
	
	/**
	 * @return [link]
	 */
	public function getMysqli()
	{
		return $this->mysqli;
	}
	
	/**
	 * @param [link] $mysqli
	 */
	public function setMysqli( $mysqli )
	{
		$this->mysqli = $mysqli;
	}

}
?>

<?php

class MySQLConnect
{
	public $mysqli;
	
	public function __construct( $host, $username, $password )
	{
		$link = new mysqli ( $host, $username, $password );
		
		/* check connection */
		if ( mysqli_connect_errno () )
		{
			trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
			exit ();
		}
		else
		{
			$this->setMysqli ( $link );
		}
	}
	
	public function execute( $sql )
	{
		return $this->queryToARRAY ( $sql );
	}
		
	public function getDatabases()
	{
		return $this->queryToARRAY( "SHOW DATABASES" );
	}
	
	public function realQuery( $query )
	{
		return $this->mysqli->query ( $query );
	}
	
	public function tree_getTree()
	{
		$mysqlVersion = mysqli_get_client_info ( $this->mysqli );
		$host = $_SERVER [ 'HTTP_HOST' ] . " (MySQL v. $mysqlVersion )";
		
		$hostArray = array ( 'label' => $host, 'type' => 'server', 'children' => $this->tree_getSchemas () );
		$treeArray [] = array ( 'label' => 'SQL Databases', 'type' => 'servers', 'children' => $hostArray );
		
		return json_encode( $treeArray );
	}
	
	private function tree_getSchemas()
	{
		//Database query     
		$databaseSQL = $this->realQuery ( "SHOW DATABASES" );
		
		//New database array
		$databases = array ();
		
		//Loop the query
		while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
		{
			//Create a new array of tables for each database
			$tables = array ();
			$status = array ();
			$size = array ();
			foreach ( $database as $key => $value )
			{
				//Set the table array to get the tbles from the database 
				$tables = $this->tree_db_getTables ( $value );
				$status = $this->_db_getStatus( $value );
				$size = $this->_db_getSize( $value );
			}
			
			//Add the tables to the database array
			$databases [] = array ( "aDatabase" => $value, 
						"aData" => $key, 
						"aType" => "database", 
						"aIcon" => "database",
						"aStatus" => $status,
						"aSize" => $size,
						"aTables" => $tables );
		}
		
		$databaseFolder [] = array ( 'label' => 'Schemas', 'children' => $databases );
		
		return $databaseFolder;
	}
	
	private function tree_db_getAuthorizations()
	{
		$authorizationsArray = array ( 'label' => 'Authorization IDs', 'children' => array ( 'label' => 'rfd' ) );
		
		return $authorizationsArray;
	}
		//TODO:
	private function tree_db_getDependcenies( $database )
	{
		$dependceniesArray = array ( 'label' => 'Dependcencies', 'children' => array ( 'label' => 'test' ) );
		
		return $dependceniesArray;
	}
		//TODO:
	private function tree_db_getStoredProcs( $database )
	{
		$storedProcsArray = array ( 'label' => 'Stored Procedures', 'children' => array ( 'label' => 'test' ) );
		
		return $storedProcsArray;
	}
	
	private function tree_db_getTables( $database )
	{
		//table query
		$tableSQL = $this->realQuery( "SHOW TABLES FROM $database" );
		
		//create a new array of tables
		$tables = array ();
		
		//loop all the results
		while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
		{
			$columns = array ();
			$statuss = array ();
			$indexes = array ();
			$constraints = array();
			$dependicies= array();
			$triggers = array();
			
			//for each table in the result make an array
			foreach ( $table as $t_key => $t_value )
			{
				//get the tables fields for each table
				$columns = $this->tree_tbl_getColumns ( $database, $t_value );
				
				//now get the primary key for each table
				$constraints = $this->tree_tbl_getConstraints ( $database, $t_value );
				
				//now get the indexes for each table
				$indexes = $this->tree_tbl_getIndexes ( $database, $t_value );
				
				//now get the dependencys for each table
				$dependicies = $this->tree_tbl_getDependcenies ( $database, $t_value );
				
				//now get the triggers for each table
				$triggers = $this->tree_tbl_getTriggers ( $database, $t_value );
				
				//now get the status for each table
				$statuss = $this->_tbl_getStatus( $database, $t_value );
			
			}
			
			$columnArr = $columns;
			$constraintArr = $constraints;
			$indexArr = $indexes;
			$dependencyArr = $dependicies;
			$triggerArr = $triggers;
			$statusArr = $statuss;
			$tables [] = array ( 
				"label" => $t_value, 
				"type" => "table", 
				"icon" => "table", 
				"children" => array ( $columnArr, $constraintArr, $indexArr, $dependencyArr, $triggerArr, $statusArr ) );
		}
		
		$tableFolder [] = array ( 'label' => 'Tables', 'children' => $tables );
		
		return $tableFolder;
	}
	
	
		//TODO:
	private function tree_db_getUserFunctions( $database )
	{
	
	}
		//TODO:
	private function tree_db_getViews( $database )
	{
	
	}
	
	private function tree_tbl_getColumns( $database, $table )
	{
		$sql = "SHOW FIELDS FROM $database.$table";
		$query = $this->realQuery( $sql );
		
		$columnsArray = array ();
		
		while ( $row = mysqli_fetch_row ( $query ) )
		{
			$type = strtoupper ( $row [ 1 ] );
			$null = '';
			
			//Check if the column can be null
			if ( $row [ 2 ] == 'YES' )
			{
				$null = 'Nullable';
			}
			$type = '[' . $type . ' ' . $null . ']';
			
			
			$columnsArray [] = array ( 
				'label' => $row [ 0 ] . ' ' . $type );
		}
		//Create the folder
		$columnsFolder = array ( 'label' => 'Columns', 'children' => $columnsArray );
		
		return $columnsFolder;
	}
	
	private function tree_tbl_getConstraints( $database, $table )
	{
		$sql = "SHOW INDEX FROM $database.$table";
		$result = $this->realQuery( $sql );
		$constraintArray = array ();
		
		while ( $constraint = mysqli_fetch_assoc ( $result ) )
		{
			//check if the key is the primary key
			if ( $constraint [ 'Key_name' ] == 'PRIMARY' )
			{
				$constraintArray = array ( 'label' => $constraint [ 'Key_name' ] );
			}
		}
		$constraintFolder = array ( 'label' => 'Constraints', 'children' => array ( $constraintArray ) );
		
		return $constraintFolder;
	}
	
	//TODO:
	private function tree_tbl_getDependcenies( $database, $table )
	{
		$dependArray = array ( 'label' => 'admin table' );
		
		$dependFolder = array ( 'label' => 'Dependencies', 'children' => array ( $dependArray ) );
		
		return $dependFolder;
	}
	
	private function tree_tbl_getIndexes( $database, $table )
	{
		$sql = "SHOW INDEX FROM $database.$table";
		$query = mysqli_query ( $this->mysqli, $sql );
		
		$indexArray = array ();
		
		while ( $row = mysqli_fetch_row ( $query ) )
		{
			if ( $row [ 2 ] !== 'PRIMARY' )
			{
				$indexArray [] = array ( 'label' => $row [ 4 ] . "($row[2])" );
			}
		}
		
		$indexFolder = array ( 'label' => 'Indexes', 'children' => $indexArray );
		
		return $indexFolder;
	}
	
	//TODO:
	private function tree_tbl_getTriggers( $database, $table )
	{
		$triggerArray = $this->queryToARRAY ( "SHOW INDEX FROM $database.$table" );
		
		$triggerFolder = array ( 'label' => 'Triggers', 'children' => array ( $triggerArray ) );
		
		return $triggerFolder;
	}
	
	
	private function _tbl_getStatus( $database, $table )
	{
		return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database LIKE '$table'" );
	}
	
	
	private function _db_getSize( $database )
	{
		$statusSQL = $this->realQuery( "SHOW TABLE STATUS FROM $database" );
		$sizeArray = array ();
		
		$totalSize = 0;
		$dataSize = 0;
		$indexSize = 0;
		
		//loop all the results
		while ( $size = mysqli_fetch_assoc ( $statusSQL ) )
		{
			$dataSize += $size [ 'Data_length' ];
			$indexSize += $size [ 'Index_length' ];
		}
		$totalSize = $dataSize + $indexSize;
		$sizeArray [] = array ( 
			'totalSize' => $totalSize, 'dataSize' => $dataSize, 'indexSize' => $indexSize 
		);
		
		return $sizeArray;
	}
	
	
	private function _db_getStatus( $database )
	{
		return $this->queryToARRAY ( "SHOW TABLE STATUS FROM $database" );
	}
	
	/**
	 * I execute a query and return the results as json.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [json] the result in json
	 */
	private function queryToJSON( $sql )
	{
		$result = $this->realQuery( $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			$array [] = $row;
		}
		return json_encode ( $array );
	}
	
	/**
	 * I execute a query and return the result as an array.
	 *
	 * @param [string] $sql the query to be executed
	 * @return [array] the result array
	 */
	public function queryToARRAY( $sql )
	{
		$query = $this->realQuery( $sql );
		$array = array ();
		
		while ( $row = mysqli_fetch_assoc ( $query ) )
		{
			$array [] = $row;
		}
		
		return $array;
	}
	
	/**
	 * I get the query status
	 *
	 * @param [string] $sql
	 * @return [json] mysql status with the ('_') striped out
	 */
	public function queryStatusToJSON( $sql )
	{
		$result = $this->realQuery( $sql );
		
		while ( $row = mysqli_fetch_assoc ( $result ) )
		{
			//replace some of the names
			$row = str_replace ( 'Com_', '', $row );
			//take out the _ of the rows
			$row = str_replace ( '_', ' ', $row );
			
			$array [] = $row;
		}
		sort ( $array );
		
		return json_encode ( $array );
	}
	
	public function dump( $title, $var )
	{
		print "<h4>$title</h4>";
		print "<pre>";
		print_r ( $var );
		print "</pre>";
	}
	
	/**
	 * @return unknown
	 */
	public function getMysqli()
	{
		return $this->mysqli;
	}
	
	/**
	 * @param unknown_type $mysqli
	 */
	public function setMysqli( $mysqli )
	{
		$this->mysqli = $mysqli;
	}

}
?>

MySQL JSON Schema Tree

sql, php, json, Flex

PHP