Location: PHPKode > projects > AeroSQL > engine.php
<?php

/*
    AeroSQL - Web based MySql Manager
    Copyright (C) 2009  Oleg Burlaca <hide@address.com>  (http://www.burlaca.com/)

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

$obj = new AeroSQL();
$obj->init();
$obj->main();


//-------------------------------------------------------------------------------------------------------------------------------------
class AeroSQL {

public function init() {
    require 'servers.php';
}

//-------------------------------------------------------------------------------------------------------------------------------------
public function main() {
	$this->isDebugSQL = $_POST['debugSQL'];  // turn On/Off sql monitor
    $this->debugSQL = array();
    
    $cmd = $_POST['cmd'];
	
	switch ($cmd) {
		case 'getMainTree': 
			$this->getMainTree();
			break;
		case 'getTableMeta': 
			$this->getTableMeta();    // Columns & Indexes for Browse/Edit Grid
			break;
		case 'fetchTableRows':
			$this->fetchTableRows();    // Table rows
			break;
        case 'execQuery':
            $this->execQuery();
            break;
        case 'execMultiQuery':
            $this->execMultiQuery();
            break;
        case 'debugQuery':
            $this->debugQuery();
            break;            
		case 'treeDelNodes':
			$this->treeDelNodes();
			break;
		case 'tblDelRow':
			$this->tblDelRow();
			break;
		case 'tblSaveRows':
			$this->tblSaveRows();
			break;
		default: 
			echo 'Unrecognized Command !!!';
			break;
	}
}
//-------------------------------------------------------------------------------------------------------------------------------------
//  determine the default value of a column
//  http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
public function _getImplicitDefaultValue(&$col) {
	$ct = $col['ct'];
	if (strpos($ct, 'int') || in_array($ct, array('decimal', 'numeric', 'decimal', 'bit', 'float', 'real', 'double'))) {
		return 0;
	} else if (strpos($ct, 'char') || strpos($ct, 'binary') || $ct == 'set' || strpos($ct, 'text') || strpos($ct, 'blob')) {
		return '';
	} else if ($ct == 'date') {
		// return '1970-01-01';
        return '0000-00-00';
	} else if ($ct == 'datetime') {
        // return '1970-01-01 00:00:00';
        return '0000-00-00 00:00:00';
	} else if ($ct == '$year') {
		return '0000';
	} else if ($ct == 'enum') {
		$t = $col['ctl'];   // enum('a','b','c')
		$k1 = strpos($t, "'") + 1;
		$k2 = strpos($t, "'", $k1);
		return substr($t, $k1, $k2 - $k1);
	} else {
		return '';
	}
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function execMultiQuery() {
    $this->_serverConnect($_POST['serverId']);
    $dbh = $this->dbh;

    $r = array();
    $r['msg'] = array(); // warnings & errors
    $r['errors'] = 0;
    $r['warnings'] = 0;
    $r['affectedRows'] = 0;
    $r['debugSQL'] = &$this->debugSQL;
    
    if ($_POST['dbName']) {
        $db = $_POST['dbName'];
        if (! get_magic_quotes_gpc()) { $db = addslashes($db); }
        $dbh->select_db($db);
        $r['dbName'] = $db;  // when user specified the db (SELECTs without dbName)
    }
    
    $st = $_POST['sql'];
    
    // $this->logSql($st, 5);
    
    if ($dbh->multi_query($st)) {
        do {
          $result = $dbh->store_result();
          
          // do we have a result set?
          if ($result) {
              // only the last result set will be sent back to the client
              unset($r['rows'], $r['metaData']);
              
              $this->_fetchExecResult($dbh, $result, $r, '', true);
          } else if ($dbh->affected_rows) {
              $r['isAffected'] = true;
              $r['affectedRows'] += $dbh->affected_rows;
          }
          
          if ($dbh->warning_count) { 
              $r['warnings'] += $dbh->warning_count;
              // we can't execut SHOW WARNINGS in the middle of a multi_query, 
              // ... waiting for the mysqli->get_warnings implementation: http://md.php.net/manual/en/mysqli.get-warnings.php
              // $this->_appendSqlWarnings($dbh, $r); 
          }
        } while ($dbh->next_result()); 
    }
    
    if ($dbh->warning_count) { 
        $this->_appendSqlWarnings($dbh, $r);
    }

    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function execQuery() {
    $this->_serverConnect($_POST['serverId']);
    $dbh = $this->dbh;

    $this->mode = $_POST['mode'];   // [table, query]
    
    $r = array();
    $r['msg'] = array(); // warnings & errors
    $r['errors'] = 0;
    $r['warnings'] = 0;
    $r['debugSQL'] = &$this->debugSQL;

    if ($_POST['dbName']) {
        $db = $_POST['dbName'];
        if (! get_magic_quotes_gpc()) { $db = addslashes($db); }
        $dbh->select_db($db);
        $r['initDbName'] = $db;  // when user didn't specified the DB in SELECT, use the default one
    }
    
    if ($this->mode == 'table') {
        $r['dbName'] = $db;
        $r['tblName'] = $_POST['tbl'];
        
        $this->tblName = $_POST['tbl'];
        $tbl = '`' . $this->tblName . '`';        
        $cols = $_POST['cols'] or '*';
        $limit = $_POST['limit'] or 20;
        
        
        // COUNT(*) FROM tblName
        $st = 'SELECT COUNT(*) AS total FROM ' . $this->tblName;
        $result = $dbh->query($st);
        $row = $result->fetch_assoc();
        $r['rowCount'] = $row['total'];

        $st = "SELECT $cols FROM $tbl LIMIT $limit";
        // if (isset ($_POST['sort'])) { $st .= ' ORDER BY ' . $_POST['sort'] . ' ' . $_POST['dir']; }
    } else {
        $st = $_POST['sql'];
    }
    
    // Returns TRUE on success or FALSE on failure. For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object. 
    $result = $dbh->query($st);
    $this->logSql($st, 4);
    
    // SQL failed
    if ($result === false) {
        $this->_appendSqlWarnings($dbh, $r);
        echo json_encode($r);
        return;
    }

    // append SHOW WARNINGS to the response (SHOW WARNINGS also shows Errors and Notes)
    if ($dbh->warning_count) { 
        $this->_appendSqlWarnings($dbh, $r);
    }
    
    // SQL didn't return a result set
    if ($result === true) {
        $r['affectedRows'] = $dbh->affected_rows;
        $r['isAffected'] = true;
        echo json_encode($r);
        return;
    } 
    
	$this->_fetchExecResult($dbh, $result, $r, $st, false);
    
    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _fetchExecResult($dbh, $result, &$r, $st, $multi) {
    
    $resFields = $result->fetch_fields();
    
    // only simple queries can be LIVE
    if ($multi) {
        $r['live'] = false;
    } else {
        // based on sql query & result fields determine if the query is 'editable'
        // Note: the result should have a set of fields that represent a unique key
        $this->_LiveQueryCheck($dbh, $r, $st, $resFields);
        
    }
    $this->_createQueryMetaData($dbh, $r, $resFields);
    
    // fetch resutls
    if ($this->mode != 'table') {
        $r['rowCount'] = $result->num_rows;
    }
    
    $data = array();
	while ($row = $result->fetch_assoc()) { 
		$data[] = $row;
	}
    $r['rows'] = $data;
    
    // in some cases, for ex: "SHOW COLUMNS FROM viewName"  where viewName is a VIEW
    // the 'orgname' and 'name' properties of $result->fetch_fields() are not correct:
    // the 'orgname' is not as the same as data returned using $result->fetch_assoc()
    $this->_fixStoreFieldNames($r);
    
    $result->free();
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _fixStoreFieldNames(&$r) {
    if (! $r['rowCount']) {return;}
    $rec = $r['rows'][0];
    foreach ($r['metaData']['fields'] as &$field) {
        $t = $field['name'];
        // property_exists
        // if(!array_key_exists($rec, $t)) { $field['name'] = $field['header']; }
        if(!array_key_exists($t, $rec)) { $field['name'] = $field['header']; }
    }
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _createQueryMetaData($dbh, &$r, &$resFields) {
    $meta = array();
    
    $meta['totalProperty'] = 'rowCount';
    $meta['root'] = 'rows';
    $meta['defaultSortable'] = true;
    
    if ($r['live']) {
        $tblCols = $this->_getTableMeta($dbh, $r);
    }
    
    // $r['debugInfo'] = json_encode($resFields);
    
    $meta['fields'] = array();
    foreach ($resFields as $d) {
        $col = array();
        $col['header'] = $d->name;
        
        // is this a real column or a calculated one
        if ($d->orgname) {
            $col['name'] = $d->orgname;
            $col['_calculated'] = false;
        } else {
            $col['name'] = $d->name;
            $col['_calculated'] = true;
        }
        // $col['_visible'] = true;
        
        // add more details about result columns: defaulValue, type, _ct(ColumnType), _ctl(ColumnTypeLong)
        if ($r['live']) {
            $t = $tblCols[ $col['name'] ]; //  $r['columns'][ $col['name'] ];
            
            $this->_setupCol($col, $d);
            
            if ($col['_calculated']) {
                $col['type'] = $this->intSqlType2extType($d->type);
            } else {
                $col['type'] = $this->sqlType2extType($t['ct']);
                if ($t['ct'] == 'timestamp' || $t['ext'] == 'auto_increment') {
                    $col['defaultValue'] = null;
                } else {
                    $col['defaultValue'] = $t['df'];
                }
                $col['_ct'] = $t['ct'];
                $col['_ctl'] = $t['ctl'];
            }
        } else {
            $this->_setupCol($col, $d);
        }
        if ($col['type'] == 'date') { $col['dateFormat'] = 'Y-m-d'; }
        
        $meta['fields'][] = $col;
    }
    
    
    // for Live queries, the Record should have the definition of all table columns
    // to properly set default values when new rows are added and 
    // to find the newly added row in the DB 
    if ($r['live']) {
        $missingCols = array_diff($r['tblCols'], $r['rCols']);
        foreach ($missingCols as $d) {
            $t = $tblCols[ $d ];
            
            $col = array();
            $col['name'] = $d;
            $col['_calculated'] = false;
            $col['type'] = $this->sqlType2extType($t['ct']);
            $col['defaultValue'] = $t['df'];
            $col['_ct'] = $t['ct'];
            $col['_ctl'] = $t['ctl'];
            
            $meta['fields'][] = $col;
        }
        
        // 
    }
    
    
    
    // .... I thought a little: NO, don't do it... 
    // if mode == 'table' the columns from the unique index that 
    // are missing from the user query will be automatically added to the SQL:
    // before: 'SELECT title FROM tbl'   after: 'SELECT id, title FROM tbl'
    // these auto added columns will be HIDDEN in the grid
    // if ($r['live']) {
    // }
    
    // metaData: {
        // totalProperty: 
        // root: 'rows',
        // fields: [
            // {name: 'name', header: 'Name', sortable: true},
            // {name: 'occupation', header: 'Job'},
            // {name: 'hits', header: 'Hits', type: 'int', _ct: 'mediumint', _ctl: 'mediumint unsigned'},
            // {name: 'date', header: 'Date', type: 'date', dateFormat: 'Y-m-d'}
        // ]
    // },
    $r['metaData'] = $meta;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getTableMeta($dbh, &$r) {
	$dbName = $r['dbName'];
	$tblName = $r['tblName'];

	$st = "SELECT COLUMN_NAME AS name, COLUMN_DEFAULT AS df, IS_NULLABLE AS isnl, DATA_TYPE AS ct, CHARACTER_MAXIMUM_LENGTH AS maxl, " .
		  "COLUMN_TYPE AS ctl, COLUMN_KEY AS ck, EXTRA AS ext " .
		  "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";

	$columns = array();  // Table Columns
  	$colDefaults = array(); // Columns Default Values
	
    $result = $dbh->query($st);
    $this->logSql($st, 3);
    
	$tblCols = array();
    while ($row = $result->fetch_assoc()) { 
		if ($row['ext'] == 'auto_increment') {
			$r['autoIncCol'] = $row['name'];  // this table has an auto_increment column
            // !!! we should also check if the auto_inc col is present in query result fields
            // if not ... the Live should be set to false :(
		}
		
		// auto_inc and timestamp columns doesn't have fixed default values
		if ($row['ext'] != 'auto_increment' && $row['ct'] != 'timestamp') {
			
			if (is_null($row['df']) && $row['isnl'] == 'NO') {
				// set implicit default value
				$colDefaults[ $row['name'] ] = $this->_getImplicitDefaultValue($row);
			} else {
				$colDefaults[ $row['name'] ] = $row['df'];
			}
            $row['df'] = $colDefaults[ $row['name'] ];
			
			if ($row['ct'] != 'date') { $row['df'] = $colDefaults[ $row['name'] ]; }
		}
		
		$columns[ $row['name'] ] = $row;
        $tblCols[] = $row['name'];
	}
	$result->free();

    $r['colDefaults'] = $colDefaults;
    $r['tblCols'] = $tblCols; // ALL table columns 
    
    return $columns;
    // $r['columns'] = $columns;
	
}
//-------------------------------------------------------------------------------------------------------------------------------------
//  check if the query is 'editable',
//  if yes, determine the 'dbName' and 'tblName'
private function _LiveQueryCheck($dbh, &$r, $st, &$resFields) {
    // Check Nr1: are all fields from the same Table ?
    
    $tblName = '';
    $r['live'] = false;
    
    // a query can be live if the response columns a from single table only
    $rCols = array();
    foreach ($resFields as $d) { 
        if ($d->table && $tblName && ($d->table != $tblName)) { return; }
        if ($d->table) { $tblName = $d->table; }
        
        // is this a real table column ?
        if ($d->orgname) { $rCols[] = $d->orgname; }
    }
    
    // the select statement didn't use a table at all
    if (!$tblName) {return; }
    
    if ($this->mode == 'query') { 
        $r['dbName'] = $this->_extractDbName($r, $st, $tblName);
        if (! $r['dbName']) { return; }
        $r['tblName'] = $tblName;
    }
    
    // Check Nr2: search results has fields of a unique index?
    $idxCols = $this->_getTblUniqueIdx($dbh, $r['dbName'], $r['tblName']);
    if (!$idxCols) { return; }  // in the future, add ALL columns if st == 'SELECT * FROM'
  
    // correctly handle multiple instances of a column: 'SELECT id, title, id FROM ...'
    $rCols = array_unique($rCols);

    // $r['dbg-1'] = $idxCols;
    // $r['dbg-2'] = $rCols;
    // $r['dbg-3'] = array_intersect($rCols, $idxCols);
    
    // ALL $idxCols should be in the result set
    if (count($idxCols) != count(array_intersect($rCols, $idxCols))) { return; }
    
    $r['uniqueIndex'] = $idxCols;
    $r['rCols'] = $rCols;   // real table columns from result set
    $r['live'] = true;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _extractDbName(&$r, $st, $tblName) {
    // if (preg_match("/\sfrom\s+`?([^`.]+?)`?\.?`?$tblName\s?/i", $st, $matches)) {
    //     if ($matches[1]) { $r['dbName'] = $matches[1]; }
    // }
    
    // FROM `db`.`tbl`     
    // FROM `db`.tbl
    if (preg_match("/\sfrom\s+`([^`.]+?)`\.`?$tblName`?\s?/i", $st, $matches)) { return $matches[1]; }
    
    // FROM db.`tbl`  
    // FROM db.tbl
    if (preg_match("/\sfrom\s+([^\s]+?)\.`?$tblName`?\s?/i", $st, $matches)) { return $matches[1]; }
    
    
    return $r['initDbName'];
}
//-------------------------------------------------------------------------------------------------------------------------------------
//  returns an array of tblColumns
private function _getTblUniqueIdx($dbh, $dbName, $tblName) {

	// fetch Table Indexes and search for a UNIQUE index with a minimum number of columns
	// PRIMARY index will be used even if it has more columns than a simple UNIQUE index
	$st = "SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS idxColumns, " .
		  "MIN(NON_UNIQUE) AS idxNonUnique, COUNT(*) AS colTotal FROM INFORMATION_SCHEMA.STATISTICS " .
		  "WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName' AND NON_UNIQUE=0 " .
		  "GROUP BY INDEX_NAME ORDER BY colTotal DESC";
	$result = $dbh->query($st);
    $this->logSql($st, 3);
    
	$idxCols = '';
	while ($row = $result->fetch_assoc()) { 
		if (! $idxCols) { $idxCols = $row['INDEX_NAME']; }
		if ($row['INDEX_NAME'] == 'PRIMARY') {
			$idxCols = $row['idxColumns'];
			break;
		}
	}
    $result->free();
	
    // if there are no unique indexes, use ALL fields as the index
    if ($idxCols) { 
        $a = explode(',', $idxCols);
    } else {
        $st = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";
        $result = $dbh->query($st);
        $this->logSql($st, 3);
        
        $a = array();
        while ($row = $result->fetch_assoc()) { 
            $a[] = $row['COLUMN_NAME'];
        }
        $result->free();
        // return null; 
    }
    
    return $a;
}
//-------------------------------------------------------------------------------------------------------------------------------------
// http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html
private function _appendSqlWarnings($dbh, &$r) {
//     try {
    $this->logSql('SHOW WARNINGS', 5);
    $result = $dbh->query('SHOW WARNINGS');
    if ($result === false) { 
        error_log($dbh->error);
        error_log(json_encode(debug_backtrace()));
        return; 
    }
/*    } catch (Exception $e) {
        echo json_encode(debug_backtrace);
    } */
    
	while ($row = $result->fetch_assoc()) { 
		$r['msg'][] = $row;
        
        if ($row['Level'] == 'Warning') { 
            $r['warnings']++; 
        } else if ($row['Level'] == 'Error') {
            $r['errors']++; 
        } else {
            // Notes are treated as Warnings
            $r['warnings']++;
        }
	}
	$result->free();
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function getTableMeta() {
	$this->_serverConnect($_POST['serverId']);
	$dbh = $this->dbh;
	$dbName = $_POST['db'];
	$tblName = $_POST['tbl'];
		
	$r = array();
	$cdf = array(); // Columns Defaults Values
	
	$st = "SELECT COLUMN_NAME AS name, COLUMN_DEFAULT AS df, IS_NULLABLE AS isnl, DATA_TYPE AS dt, CHARACTER_MAXIMUM_LENGTH AS maxl, " .
		  "COLUMN_TYPE AS ct, COLUMN_KEY AS ck, EXTRA AS ext " .
		  "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";

	
    if ($this->isDebugSQL) { $this->debugSQL[] = $st; }
    
	// fetch Table Columns
	$colAry = array();
	$result = $this->dbh->query($st);
	while ($row = $result->fetch_assoc()) { 
		if ($row['ext'] == 'auto_increment') {
			$r['aiColumn'] = $row['name'];  // this table has an auto_increment column
		}
		
		// auto_inc and timestamp columns doesn't have fixed default values
		if ($row['ext'] != 'auto_increment' && $row['dt'] != 'timestamp') {
			
			if (is_null($row['df']) && $row['isnl'] == 'NO') {
				// set implicit default value
				$cdf[ $row['name'] ] = $this->_getImplicitDefaultValue($row);
			} else {
				$cdf[ $row['name'] ] = $row['df'];
			}
			
			if ($row['dt'] != 'date') { $row['df'] = $cdf[ $row['name'] ]; }
		}
		
		$colAry[] = $row;
	}
	$result->free();
	
	// fetch Table Indexes and search for a UNIQUE index with a minimum number of columns
	// PRIMARY index will be used even if it has more columns than a simple UNIQUE index
	$st = "SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS idxColumns, " .
		  "MIN(NON_UNIQUE) AS idxNonUnique, COUNT(*) AS colTotal FROM INFORMATION_SCHEMA.STATISTICS " .
		  "WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName' AND NON_UNIQUE=0 " .
		  "GROUP BY INDEX_NAME ORDER BY colTotal DESC";
    if ($this->isDebugSQL) { $this->debugSQL[] = $st; }
    $result = $dbh->query($st);
	$idxCols = '';
	while ($row = $result->fetch_assoc()) { 
		if (! $idxCols) { $idxCols = $row['INDEX_NAME']; }
		if ($row['INDEX_NAME'] == 'PRIMARY') {
			$idxCols = $row['idxColumns'];
			break;
		}
	}
	
	// if there are no unique indexes, use ALL fields as the index
	if (! $idxCols) { 
		foreach ($colAry as $row) { $idxCols .= $row['name'] . ','; }
		$idxCols = rtrim($idxCols, ',');
	}
	
	$r['indexes'] = $idxCols;
	$r['columns'] = $colAry;
	$r['cdf'] = $cdf;
	
	echo json_encode($r);
	// echo "{indexes:'$idxCols', columns:" . json_encode($colAry) . '}';	
}

//-------------------------------------------------------------------------------------------------------------------------------------
public function tblSaveRows() {
	$this->_serverConnect($_POST['serverId']);
	$dbh = $this->dbh;

    $dbName = $_POST['db'];
    if (! get_magic_quotes_gpc()) { $dbName = addslashes($dbName); }
    $dbh->select_db($dbName);  // we should USE DB because triggers complains (notes): NO database selected

	$data = json_decode($_POST['data']);
	$idxAry = $data->uniqueIndex;
	
	$tbl = '`' . $dbName . '`.`' . $_POST['tbl'] . '`';
	
	$r = array();
	$r['success'] = true;
	$r['errors'] = 0;
    $r['warnings'] = 0;
	$r['msg'] = array();
	
	$r['update'] = array();
    $r['debugSQL'] = &$this->debugSQL; // for SQL Monitor
		
	$r['insert'] = array();
	
	
	// UPDATE rows
    $st = "UPDATE $tbl SET ";
	foreach ($data->update as $d) {
		$t = $st . $this->_rec2mysql($d[1]) . ' WHERE ' . $this->_makeSqlKey($idxAry, $d[0]);
		
        $this->logSql($t, 1);
        if ($dbh->query($t)) {
			$r['update'][$d[1]->_gridId] = 1;
			// $t = "SELECT * FROM $tbl WHERE " . implode(' AND ', $a);
            if ($dbh->warning_count) { 
                $this->_appendSqlWarnings($dbh, $r);
            }
		} else {
			$this->_appendSqlWarnings($dbh, $r);
		}
	}
	
	// INSERT rows
	$st = "INSERT INTO $tbl SET ";
	foreach ($data->insert as $d) {

        $a = array();  // INSERT: the new record: column=value
        $a2 = array(); // SELECT: the same as $a but with "column IS NULL" 
        
		foreach($d as $k => $v) {
            if ($k == '_gridId') { continue; }
		    if (! is_null($v) and ! get_magic_quotes_gpc()) { $v = addslashes($v); }
		    if (is_null($v)) { 
                $a[] = "$k=NULL";
                $a2[] = "$k IS NULL";
            } else { 
                $a[] = "$k='$v'";
                $a2[] = "$k='$v'";
            }
		}
		$t = $st . implode(', ', $a);
		
        // if user specified values for a unique index, 
        // find the newly added record by that index
        $findByIdx = 1;
        $rowIdx = array();
        foreach ($data->uniqueIndex as $idxField) {
            // ALL fields of the unique index should be specified
            if (! property_exists($d, $idxField)) { $findByIdx = 0; break; }
            
            $v = $d->$idxField;
            
            // ... I'm not sure MySql allows NULL in indexed fields
            if (is_null($v)) { 
                $rowIdx[] = "$idxField IS NULL";
            } else {
                $rowIdx[] = "$idxField='$v'";
            }
        }
        
        $this->logSql($t, 1);
        //  if INSERT == OK, fetch the new row
        if ($dbh->query($t)) {
			if ($dbh->warning_count) { 
                $this->_appendSqlWarnings($dbh, $r);
            }
            
            // the table has an AUTO_INCREMENT column ?
			if ($data->autoIncCol) {
			    $lastId = $dbh->insert_id;
				$aiColumn = $data->autoIncCol;
				$t = "SELECT * FROM $tbl WHERE $aiColumn=$lastId";
			} else {
				$t = "SELECT * FROM $tbl WHERE ";
                $t .= $findByIdx ? implode(' AND ', $rowIdx) 
                                 : implode(' AND ', $a2);
			}
			$this->logSql($t, 1);
            $result = $dbh->query($t);
			$row = $result->fetch_assoc();
			
			$r['insert'][$d->_gridId] = $row;
			$result->free();
		} else {
            // return the SQL error message
            $this->_appendSqlWarnings($dbh, $r);
		}
	}
	
	echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function _makeSqlKey(&$idxAry, &$recAry) {
	$st = '';
	$a = array();
	for ($i=0; $i<count($idxAry); $i++) {
		
        $a[] = $recAry[$i] === null ? "$idxAry[$i] IS NULL"
                                    : "$idxAry[$i]='$recAry[$i]'";
	}

	$t = implode(' AND ', $a);
	return $t;
}

//-------------------------------------------------------------------------------------------------------------------------------------
public function _rec2mysql(&$r) {
	$a = array();
	while (list($k, $v) = each($r)) {
		if ($k == '_gridId') { continue; }
		if ($v === null) {
            $a[] = "$k=NULL";
        } else {
            if (! get_magic_quotes_gpc()) { $v = addslashes($v); }
            $a[] = "$k='$v'";
        }
	}
	$t = implode(', ', $a);
	
	return $t;
}

//-------------------------------------------------------------------------------------------------------------------------------------
//  deletes a row 
public function tblDelRow() {
	$this->_serverConnect($_POST['serverId']);
	$data = json_decode($_POST['rec']);
   
	$st = "DELETE FROM `" . $_POST['db'] . '`.`' . $_POST['tbl'] . "` WHERE ";

	$uniqueIdx = $data->uniqueIndex;
    $row = $data->row;
    
    $a = array();
    for($i=0; $i<count($uniqueIdx); $i++) {
        $k = $uniqueIdx[$i];
        $v = $row[$i];
        if ($v === null) { 
            $v = 'NULL'; 
        } else {
            if (! get_magic_quotes_gpc()) { $v = addslashes($v); }
            $v = "'$v'";
        }
        $a[] = "$k=$v";
    }
    
    /* $a = array();
	while (list($k, $v) = each($rec)) {
		if (! get_magic_quotes_gpc()) { $v = addslashes($v); }
		array_push($a, "$k='$v'");
	} */
    
	$st .= implode(' AND ', $a);
	
    //echo $st; return; 
	$r = array();
	$r['success'] = true;
	$r['errors'] = 0;
    $r['warnings'] = 0;
	$r['msg'] = array();
    $r['debugSQL'] = &$this->debugSQL;
   
	$dbh = $this->dbh;
    
    $this->logSql($st, 1);
	$result = $dbh->query($st);
    if ($result === true) {
        $r['isAffected'] = true;
        $r['affectedRows'] = $dbh->affected_rows;
        if ($dbh->warning_count) { 
            $this->_appendSqlWarnings($dbh, $r);
        }
    } else {
        $this->_appendSqlWarnings($dbh, $r);
        // can't delete row
    }
    
    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
//  fetches [$start .. $start+$limit] rows from a DB table
public function fetchTableRows() {
	$this->_serverConnect($_POST['serverId']);
	$dbh = $this->dbh;
	
	$tbl = '`' . $_POST['db'] . '`.`' . $_POST['tbl'] . '`';
	$start = $_POST['start'] or 0;
	$limit = $_POST['limit'] or 20;
	// $start = 0;	
	// $limit = 50;
	$cols = $_POST['cols'] or '*';
    
	$st = "SELECT $cols FROM $tbl ";
	if (isset ($_POST['sort'])) {
		$st .= ' ORDER BY ' . $_POST['sort'] . ' ' . $_POST['dir'];
	}
	$st .= " LIMIT $start, $limit";

	if (!$result = $dbh->query($st)) {
		echo "{success: false, st: '" . addslashes($st) . "'}";  // cols: '" . addslashes($cols) . "'
		return;
	}
	
	// $data = $result->fetch_all();  // implemented in mysqlnd
	$data = array();
	while ($row = $result->fetch_assoc()) { 
		$data[] = $row;
	}
	$result->free();

	$st = "SELECT COUNT(*) AS total FROM $tbl";
	$result = $dbh->query($st);
	$row = $result->fetch_assoc();
	$total = $row['total'];
	
	echo '{success: true, rowCount:'.$total.',rows:'.json_encode($data).'}';
}
//-------------------------------------------------------------------------------------------------------------------------------------
// populates the main Left Tree
public function getMainTree() {
	// $pids = explode('/', $_POST['crumb']);
	$pids = explode('/', $_POST['crumb']);
	
	array_shift($pids);  // remove emtpy item
	array_shift($pids);  // remove 'root' item
	
	switch (count($pids)) {
		case 0:   
			$this->_getMainTree_servers();     // root node: get server list
			break;
		case 1: 
			$this->_getMainTree_db($pids[0]);   // server node: connect & get DB list
			break;
		case 2: 
			$this->_getMainTree_dbFolders($pids);  // common DB folders: tables, views, triggers, functions
			break;
		case 3: 
			$this->_getMainTree_dbContents($pids); // depending on dbFolder: a list of tables, functionc etc
			break;
		case 4: 
			$this->_getMainTree_tblFolders($pids);
			break;
		case 5: 
			$this->_getMainTree_tblContents($pids);
			break;
	}
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_servers() {
	$r = array ();
	reset($this->servers);
	while (list($key, $d) = each($this->servers)) {
		$a = array('pid' => $key, 'text' => $d['text'], 'iconCls' => 'icon-server');
		array_push($r, $a);
	}
	echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_db($serverId) {
	$this->_serverConnect($serverId);
	
	$dbh = $this->dbh;
	$st = 'SELECT * FROM INFORMATION_SCHEMA.SCHEMATA';
    $this->logSql($st, 3);
    $result = $dbh->query($st);    // SHOW DATABASES
	
	$r = array();
	while ($row = $result->fetch_assoc()) { 
		$info = array();
        $info[] = array('k' => 'charset', 'v' => $row['DEFAULT_CHARACTER_SET_NAME']);
        $info[] = array('k' => 'collation', 'v' => $row['DEFAULT_COLLATION_NAME']);
        
        $a = array('pid' => $row['SCHEMA_NAME'], 'text' => $row['SCHEMA_NAME'], 'iconCls' => 'icon-db', '_info' => $info);
		array_push($r, $a);
	}
	$result->free();
	
    // $r['debugSQL'] = &$this->debugSQL;
	echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_dbFolders($pids) {
	$this->_serverConnect($pids[0]);
	$dbName = $pids[1];
	
    $dbh = $this->dbh;
    
	// count TABLEs
    $st = "SELECT COUNT(*) AS tblCount FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$dbName' AND TABLE_TYPE<>'VIEW'"; // BASE TABLE
	$this->logSql($st, 3);
    $result = $dbh->query($st);	$row = $result->fetch_row(); 
    $tblCount = $row[0];
    $result->free();
    
    // count VIEW's
    $st = "SELECT COUNT(*) AS viewCount FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema = '$dbName'";
	$this->logSql($st, 3);
    $result = $dbh->query($st);	$row = $result->fetch_row(); 
    $viewCount = $row[0];
    $result->free();

    // count FUNCTION's
    $st = "SELECT COUNT(*) AS funcCount FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '$dbName'";
	
    $t = "$st AND ROUTINE_TYPE='FUNCTION'";
    $this->logSql($t, 3);
    $result = $dbh->query($t);
    $row = $result->fetch_row(); 
    $funcCount = $row[0];
    $result->free();
    
    // count PROCEDURE's
    $t = "$st AND ROUTINE_TYPE='PROCEDURE'";
    $result = $dbh->query($t);
    $this->logSql($t, 3);
    $row = $result->fetch_row(); 
    $procCount = $row[0];
    $result->free();

    // count TRIGGERS's
    $st = "SELECT COUNT(*) AS trigCount FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = '$dbName'";
    $this->logSql($st, 3);
    $result = $dbh->query($st);
    $row = $result->fetch_row(); 
    $trigCount = $row[0];
    $result->free();
	
	/* 
    $r = array(array('pid' => 'tables', 'text' => $this->_formatNodeText('Tables', $tblCount)),
			   array('pid' => 'views', 'text' => $this->_formatNodeText('Views', $viewCount)),
			   array('pid' => 'procs', 'text' => $this->_formatNodeText('Stored Procs', $procCount)),
			   array('pid' => 'funcs', 'text' => $this->_formatNodeText('Functions', $funcCount)),
			   array('pid' => 'trigs', 'text' => $this->_formatNodeText('Triggers', $trigCount), 
                     'leaf' => true, 'iconCls' => 'icon-folder')    // 'draggable' => false, 
	); */
    
    $r = array($this->_createDbChildNode('tables', 'Tables', $tblCount),
               $this->_createDbChildNode('views', 'Views', $viewCount),
               $this->_createDbChildNode('procs', 'Stored Procs', $procCount),
               $this->_createDbChildNode('funcs', 'Functions', $funcCount),
               $this->_createDbChildNode('trigs', 'Triggers', $trigCount)
               );
	
    // $r['debugSQL'] = &$this->debugSQL;
    echo json_encode($r);
    
    
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _createDbChildNode($pid, $text, $total) {
    $a = array('pid' => $pid);
    
    if ($total) {
        $a['text'] = "$text <span class=\"gr\">[$total]</span>";
    } else {
        $a['text'] = $text;
        $a['leaf'] = true;
        $a['iconCls'] = 'icon-folder';
    }
    
    return $a;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_dbContents($pids) {
	$this->_serverConnect($pids[0]);
	
	$type = $pids[2];   // type=[tables,views,procs...]
	$dbName = $pids[1];
	
	$r = array();
	switch ($type) {
		case 'tables':
			$st = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '$dbName' AND TABLE_TYPE <> 'VIEW'";

			$result = $this->dbh->query($st);
			while ($row = $result->fetch_assoc()) { 
				$t = $row['TABLE_NAME']; // . ' <span class="gr">' . bytesConvert($row['DATA_LENGTH'] + $row['INDEX_LENGTH']) . '</span>';
				// $qTip = $this->_getTableQTip($row);
				$a = array('pid' => $row['TABLE_NAME'], 'text' => $t, 'iconCls' => 'icon-tbl2', '_info' => $this->getTableInfo($row));  // 'qtip' => $qTip,
				$r[] = $a;
			}
			break;
        case 'views':
			$st = "SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema = '$dbName'";

			$result = $this->dbh->query($st);
			while ($row = $result->fetch_assoc()) { 
				$t = $row['TABLE_NAME']; 
				$iconCls = $row['IS_UPDATABLE'] == 'YES' ? 'icon-tblview2' : 'icon-tblview_off2';
                
                $a = array('pid' => $row['TABLE_NAME'], 'text' => $t, 'allowChildren' => false, '_info' => $this->getViewInfo($row),
                           'iconCls' => $iconCls, 'leaf' => true);  // 'qtip' => $qTip,
				$r[] = $a;
			}
            
            break;
        case 'procs':
            $st = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '$dbName' AND ROUTINE_TYPE='PROCEDURE'";
            $result = $this->dbh->query($st);
   			while ($row = $result->fetch_assoc()) { 
				$t = $row['ROUTINE_NAME']; // . ' <span class="gr">' . bytesConvert($row['DATA_LENGTH'] + $row['INDEX_LENGTH']) . '</span>';
				// $qTip = $this->_getTableQTip($row);
				// $iconCls = $row['IS_UPDATABLE'] == 'YES' ? 'icon-tblview' : 'icon-tblview_off';
                $iconCls = 'icon-proc';
                
                $a = array('pid' => $row['ROUTINE_NAME'], 'text' => $t, 'allowChildren' => false, '_info' => $this->getProcInfo($row),
                           'iconCls' => $iconCls, 'leaf' => true);  // 'qtip' => $qTip,
				$r[] = $a;
			}
            break;
        case 'funcs':
            $st = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '$dbName' AND ROUTINE_TYPE='FUNCTION'";
            $result = $this->dbh->query($st);
   			while ($row = $result->fetch_assoc()) { 
				$t = $row['ROUTINE_NAME']; 
                $iconCls = 'icon-func';
                
                $a = array('pid' => $row['ROUTINE_NAME'], 'text' => $t, 'allowChildren' => false, '_info' => $this->getFuncInfo($row),
                           'iconCls' => $iconCls, 'leaf' => true);  // 'qtip' => $qTip,
				$r[] = $a;
			}
            break;
        case 'trigs':
            $st = "SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = '$dbName'";
            $result = $this->dbh->query($st);
   			while ($row = $result->fetch_assoc()) { 
				$t = $row['TRIGGER_NAME']; 
                $iconCls = $row['ACTION_TIMING'] == 'BEFORE' ? 'icon-flagg' : 'icon-flagr';
                
                $a = array('pid' => $row['TRIGGER_NAME'], 'text' => $t, 'allowChildren' => false, '_info' => $this->getTrigInfo($row),
                           'iconCls' => $iconCls, 'leaf' => true);
				$r[] = $a;
			}
            break;
		default:
			echo 'Unknown type!';
			break;
	}
	if ($result) { $result->free(); }
    
    // $r['debugSQL'] = &$this->debugSQL;
    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function getTableInfo(&$row) {
    $a = array();
    $a[] = array('k' => 'rows', 'v' => number_format($row['TABLE_ROWS']));
    $a[] = array('k' => 'data', 'v' => bytesConvert($row['DATA_LENGTH']));
    $a[] = array('k' => 'index', 'v' => bytesConvert($row['INDEX_LENGTH']));
    $a[] = array('k' => 'engine', 'v' => $row['ENGINE']);
    $a[] = array('k' => 'rowFormat', 'v' => $row['ROW_FORMAT']);
    $a[] = array('k' => 'created', 'v' => $row['CREATE_TIME']);
    $a[] = array('k' => 'updated', 'v' => $row['UPDATE_TIME']);
    $a[] = array('k' => 'collation', 'v' => $row['TABLE_COLLATION']);
	return $a;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function getViewInfo(&$row) {
    $a = array();
    $a[] = array('k' => 'check', 'v' => strtolower($row['CHECK_OPTION']));
    $a[] = array('k' => 'updatable', 'v' => strtolower($row['IS_UPDATABLE']));
    $a[] = array('k' => 'security', 'v' => strtolower($row['SECURITY_TYPE']));    
    $a[] = array('k' => 'definer', 'v' => strtolower($row['DEFINER']));
	return $a;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function getProcInfo(&$row) {
    $a = array();
    $a[] = array('k' => 'deterministic', 'v' => strtolower($row['IS_DETERMINISTIC']));
    $a[] = array('k' => 'dataAccess', 'v' => strtolower($row['SQL_DATA_ACCESS']));    
    $a[] = array('k' => 'created', 'v' => strtolower($row['CREATED']));    
    $a[] = array('k' => 'altered', 'v' => strtolower($row['LAST_ALTERED']));    
    $a[] = array('k' => 'security', 'v' => strtolower($row['SECURITY_TYPE']));        
    $a[] = array('k' => 'definer', 'v' => strtolower($row['DEFINER']));
	return $a;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function getFuncInfo(&$row) {
    $a = array();
    $a[] = array('k' => 'deterministic', 'v' => strtolower($row['IS_DETERMINISTIC']));
    $a[] = array('k' => 'dataAccess', 'v' => strtolower($row['SQL_DATA_ACCESS']));
    $a[] = array('k' => 'created', 'v' => strtolower($row['CREATED']));    
    $a[] = array('k' => 'altered', 'v' => strtolower($row['LAST_ALTERED']));    
    $a[] = array('k' => 'security', 'v' => strtolower($row['SECURITY_TYPE']));        
    $a[] = array('k' => 'definer', 'v' => strtolower($row['DEFINER']));
	return $a;    
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function getTrigInfo(&$row) {
    $a = array();
    $a[] = array('k' => 'event', 'v' => $row['EVENT_MANIPULATION']);
    $a[] = array('k' => 'table', 'v' => strtolower($row['EVENT_OBJECT_TABLE']));
    $a[] = array('k' => 'timing', 'v' => $row['ACTION_TIMING']);
    $a[] = array('k' => 'definer', 'v' => strtolower($row['DEFINER']));
	return $a;    
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_tblFolders($pids) {
	$this->_serverConnect($pids[0]);
	
	$dbName = $pids[1];
	$tblName = $pids[3];
	
	$st = "SELECT COUNT(*) AS colTotal FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";
	$result = $this->dbh->query($st);
	$row = $result->fetch_assoc();
	$colTotal = $row['colTotal'];
	
	$st = "SELECT COUNT(DISTINCT(INDEX_NAME)) AS idxTotal FROM INFORMATION_SCHEMA.STATISTICS " .
		  "WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";
	$result = $this->dbh->query($st);
	$row = $result->fetch_assoc();
	$idxTotal = $row['idxTotal'];

	$r = array(array('pid' => 'col', 'text' => 'Columns' . $this->_appendNodeTotal($colTotal)),
			   array('pid' => 'idx', 'text' => 'Indexes' . $this->_appendNodeTotal($idxTotal))
	);
	
    // $r['debugSQL'] = &$this->debugSQL;
    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _appendNodeTotal(&$total) {
	if (! $total) { 
		$t = '';
	} else {
		$t = ' <span class="gr">[' . $total . ']</span>';
	}
	return $t;
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _getMainTree_tblContents($pids) {
	$this->_serverConnect($pids[0]);
	
	$dbName = $pids[1];
	$tblName = $pids[3];
	$type = $pids[4];   // type=[col, idx]	
	
	$r = array();
	switch ($type) {
		case 'col':
			$st = "SELECT COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, EXTRA " .
			      "FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName'";

			$result = $this->dbh->query($st);
			while ($row = $result->fetch_assoc()) { 
				$t = $row['COLUMN_NAME'] . ' <span class="gr">' . $row['COLUMN_TYPE'];
				if ($row['IS_NULLABLE'] == 'YES') { $t .= ', NULL'; }
				if ($row['EXTRA']) { $t .= ', ' . $row['EXTRA']; }
				$t .= '</span>';
				
				$iconCls = 'icon-col';
				if ($row['COLUMN_KEY']) {
					$iconCls = $row['COLUMN_KEY'] == 'PRI' ? 'icon-idx_pri' : 'icon-idx'; //icon-col_key';
				}
				
				$a = array('pid' => $row['COLUMN_NAME'], 'text' => $t, 'iconCls' => $iconCls, 'leaf' => true);
				array_push($r, $a);
			}
			break;
		case 'idx':
			$st = "SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS idxColumns, " .
				  "MIN(NON_UNIQUE) AS idxNonUnique, COUNT(*) AS colTotal FROM INFORMATION_SCHEMA.STATISTICS " .
				  "WHERE TABLE_SCHEMA='$dbName' AND TABLE_NAME='$tblName' " .
				  "GROUP BY INDEX_NAME ORDER BY INDEX_NAME";
			$result = $this->dbh->query($st);
			while ($row = $result->fetch_assoc()) { 
				$t = $row['INDEX_NAME'];
				$iconCls = 'icon-idx';
				
				if ($row['INDEX_NAME'] == 'PRIMARY') {
					$iconCls = 'icon-idx_pri';
					$t = $row['idxColumns'];
				} else {
					$t = $row['INDEX_NAME'];
					if ($row['idxColumns'] != $row['INDEX_NAME']) { $t .= ' <span class="gr">' . $row['idxColumns'] . '</span>'; }
					if (! $row['idxNonUnique']) { $iconCls = 'icon-idx_uni'; }
				}
				
				$a = array('pid' => $row['INDEX_NAME'], 'text' => $t, 'iconCls' => $iconCls, 'leaf' => true);
				if ($row['INDEX_NAME'] == 'PRIMARY') {
					// the PRIMARY key is the first on the list
					array_unshift($r, $a);
				} else {
					array_push($r, $a);
				}
				
			}
			break;
	}

    // $r['debugSQL'] = &$this->debugSQL;
	echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function treeDelNodes() {
    $ary = explode(',', $_POST['crumbs']);
    
    $r = array();
    $r['success'] = true;
    $r['msg'] = array(); // warnings & errors
    $r['debugSQL'] = &$this->debugSQL;
    $r['errors'] = 0;
    $r['warnings'] = 0;
    $r['affectedRows'] = 0;
    $r['deletedNodes'] = array();   // an array of nodeCrumb to be removed from TreePanel at clientside

    for ($i=0; $i<count($ary); $i++) {
        $crumb = $ary[$i];
        $this->_treeDelNode($crumb, $r);
    }
    
    echo json_encode($r);
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _treeDelNode($crumb, &$r) {
	$pids = explode('/', $crumb);
	array_shift($pids);  // remove emtpy item
	array_shift($pids);  // remove 'root' item

	$this->_serverConnect($pids[0]);
	$dbh = $this->dbh;
    
    $dbName = $pids[1];
	
	$st = '';
    switch (count($pids)) {
		case 2: 
            $st = "DROP DATABASE `$dbName`";
            break;
        case 4:
            $type2mysql = array('tables' => 'TABLE', 'views' => 'VIEW', 'procs' => 'PROCEDURE', 'funcs' => 'FUNCTION', 'trigs' => 'TRIGGER');
            $type = $type2mysql[$pids[2]];   // type=[tables,views,procs,funcs,trigs]
            
            if ($type) {
                $st = "DROP $type `$dbName`.`$pids[3]`";
            }
            
            break;
        case 6:   
			$type = $pids[4];   // type=[col,idx]	
			if ($type == 'col') {
				$st = "ALTER TABLE `$dbName`.`$pids[3]` DROP COLUMN $pids[5]";
			} elseif ($type == 'idx') {
				$st = "ALTER TABLE `$dbName`.`$pids[3]` DROP INDEX $pids[5]";
			}
			break;
	}
    
    // no SQL statement provided, we don't know how to delete this node
    if (!$st) { return; }
    $this->logSql($st, 1);
    
    
    if ($dbh->query($st)) {
        if ($dbh->affected_rows) {
            $r['affectedRows'] += $dbh->affected_rows;
        }
        $r['deletedNodes'][] = $crumb;
        // echo "{success: true, st: '" . addslashes($st) . "'}";
    } else {
        $r['success'] = false;  // notify the client that there were errors
        // echo "{success: false, error: '" . addslashes($this->dbh->error) . "'}";
    }
    
    if ($dbh->warning_count) { 
        $this->_appendSqlWarnings($dbh, $r);
    }
}
//-------------------------------------------------------------------------------------------------------------------------------------
private function _serverConnect($serverId) {
	// do not connect to the same server twice, check if we are already connected to it
    if (isset($this->_connectedServer) and $this->_connectedServer == $serverId && isset($this->dbh)) return true;
	
	$opt = $this->servers[$serverId];
	if (! isset($opt['port'])) { $opt['port'] = 3306; }
	
	@ $this->dbh = new mysqli($opt['host'], $opt['user'], $opt['pass'], '', $opt['port']);

	if (mysqli_connect_errno()) {
		// $this->_errMsg = //return false;
		// echo 'Error: Could not connect to database. Please try again later.';
		
		echo mysqli_connect_error();
		exit;
	} else {
		if (!empty($opt['init'])) { $this->dbh->query($opt['init']); }
        
        $this->_connectedServer = $serverId; // remember to which server we are connected
        return true;
	}
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function __construct() {
//    $this->SQL_TYPES = array('t0': 'decimal', 't1': 'tiny', 't2': 'short', 't3': 'long', 't4': 'float',
//                             't5': 'double',  't6': 'null', 't7': 'timestamp', 't8': 'longlong', 't9': 'int24',
//                             't10': 'date', 't11': 'time', 't12': 'datetime', 't13': 'year', 't14': 'newdate', 
//                             't247': 'enum', 't248': 'set', 't249': 'tiny_blob', 't250': 'medium_blob', 't251': 'long_blob',
//                             't253': 'var_string', 't254': 'string', 't255': 'geometry');

}
//-------------------------------------------------------------------------------------------------------------------------------------
public function __destruct() {
	if (isset($this->dbh)) { $this->dbh->close(); }
}
//-------------------------------------------------------------------------------------------------------------------------------------
// http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html
private function _setupCol(&$col, &$field) {
    $col['type'] = $this->intSqlType2extType($field->type);
    $col['_ct'] = 'varchar';
    $col['_maxLen'] = $field->length;
    
    $ft = $field->type;
    $ff = $field->flags;

    if ($ft >= 249 && $ft <= 252) {
        $col['_blob'] = true;
    }
    if ($ff && 32) {
        $col['_unsigned'] = true;
    }
    
    // check if column is part of an index
    if ($ff & 2) {
        $col['_key'] = 'pri';
        // $col['id'] = 'idx_pri1';
    } else if ($ff & 4) {
        $col['_key'] = 'uni';
        // $col['id'] = 'idx1';
    } else if ($ff & 8) {
        $col['_key'] = 'mul';
        // $col['id'] = 'idx1';
    }

    if ($col['type'] == 'string' && isset($col['_blob'])) {
        $col['_ct'] = 'text';
    }
}
//------------------- MYSQL FLAGS --------------------------------------------------------------
// http://dev.mysql.com/sources/doxygen/mysql-5.1/mysql__com_8h-source.html
/*
NOT_NULL_FLAG   1               Field can't be NULL
PRI_KEY_FLAG    2               Field is part of a primary key 
UNIQUE_KEY_FLAG 4                Field is part of a unique key 
MULTIPLE_KEY_FLAG 8              Field is part of a key 
BLOB_FLAG       16               Field is a blob (deprecated: use field->type to check if BLOB)
UNSIGNED_FLAG   32               Field is unsigned 
ZEROFILL_FLAG   64               Field is zerofill 
BINARY_FLAG     128              Field is binary   
 
The following are only sent to new clients 
ENUM_FLAG       256             field is an enum 
AUTO_INCREMENT_FLAG 512         field is a autoincrement field 
TIMESTAMP_FLAG  1024            Field is a timestamp 
SET_FLAG        2048            field is a set 
NO_DEFAULT_VALUE_FLAG 4096      Field doesn't have default value 
NUM_FLAG        32768            Field is num (for clients) 
*/
//-------------------------------------------------------------------------------------------------------------------------------------
public function sqlType2extType($t) {
    // ExtJS data types
    // auto: is the default, but 'string' will be used
    // string
    // int
    // float
    // boolean
    // date
    
    if ((strpos($t, 'int') > -1) || ($t == 'bit')) {
        return 'int';
    } else if ($t == 'float' || $t == 'double' || $t == 'decimal' || $t == 'numeric') {
        return 'float';
    } else if ($t == 'date') {
        return 'date';
    } else {
        return 'string';
    }
    // for ENUM('N','Y') the 'boolean' type will be used in the future
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function logSql($st, $logLevel) {
    $k = $this->isDebugSQL;
    if (! $k) { return; }
    
    if ($logLevel <= $k ) { $this->debugSQL[] = $st; }
}
//-------------------------------------------------------------------------------------------------------------------------------------
public function intSqlType2extType($i) {
    
    if ($i <= 3 || $i == 8 || $i == 9 || $i == 13) {
        return 'int';
    } else if ($i == 4 || $i == 5) {
        return 'float';
    } else if ($i == '10' || $i == 14) {
        return 'date';
    } else {
        return 'string';
    }
/*
    ------ INT -----------------
    tiny 	= 	1
    short 	= 	2
    long 	= 	3
    longlong 	= 	8
    int24 	= 	9
    year 	= 	13

    ------ FLOAT ---------------
    float 	= 	4
    double 	= 	5    

    ------ DATE ----------------
    date 	= 	10
    newdate 	= 	14
    
    ------ STRING --------------    
    var_string 	= 	253
    string 	= 	254
    timestamp 	= 	7
    datetime 	= 	12   
    time 	= 	11    
    enum 	= 	247
    set 	= 	248
    null 	= 	6
    geometry 	= 	255    
    

    ------ BLOB ----------------
    tiny_blob 	= 	249
    medium_blob 	= 	250
    long_blob 	= 	251
    blob 	= 	252

    -----------------------------
    char 	= 	tiny
    interval 	= 	enum

*/
}

}
//---  END of AeroSQL -------------------------------------------------------------------------------------------------------------------------------


//-------------------------------------------------------------------------------------------------------------------------------------
function bytesConvert($bytes)
{
    $ext = array('B', 'KB', 'MB', 'GB', 'TB');
    $unitCount = 0;
    for(; $bytes >= 1024; $unitCount++) $bytes /= 1024;
    return number_format($bytes, 0) . ' ' . $ext[$unitCount];
}
//-------------------------------------------------------------------------------------------------------------------------------------
?>
Return current item: AeroSQL