/ php-ppcms/includes/classes/database.mysql.link.class.php

<?php

/***************************************************************

*  Copyright notice

*  (c) 2009, jianyuzhu@gmail.com

*  All rights reserved

*  This script is part of the PPEMI project.

***************************************************************/



class ADODBDatabase {

	var $database = '';

	var $dbType = 'mysql';

	var $dbHostName = '';

	var $dbUserName = '';

	var $dbPassword = '';

	var $dbName = '';

	var $lastmysqlrow = -1;

	

	//

	var $enableSQLlog = false;

	var $dieOnError = false;

	

	//query time

	var $queryTime = 0;

	

	//query count from the page start to the end

	//internal counter

	var $queryCount = 0;

	

	//

	var $_link = '_db_link';

	

	//constructor

	function &getInstance() {

		static $instance;

		if( !is_object($instance) ) {

			$instance = & new ADODBDatabase();

		}

		return $instance;

	}

	

	function isMySQL() {

		return ($this->dbType == 'mysql');

	}

	

	function setDbType($dbType) {

		$this->dbType = $dbType;

	}

	

	function getDbType() {

		return $this->dbType;

	}

	

	function setDbHostName($dbHostName) {

		$this->dbHostName = $dbHostName;

	}

	

	function getDbHostName() {

		return $this->dbHostName;

	}

	

	function setDbUserName($dbUserName) {

		$this->dbUserName = $dbUserName;

	}

	

	function getDbUserName() {

		return $this->dbUserName;

	}

	

	function setDbPassword($dbPassword) {

		$this->dbPassword = $dbPassword;

	}

	

	function getDbPassword() {

		return $this->dbPassword;

	}

	

	function setDbName($dbName) {

		$this->dbName = $dbName;

	}

	

	function getDbName() {

		return $this->dbName;

	}

	

	function change_key_case($arr) {

		return is_array($arr)?array_change_key_case($arr):$arr;

	}

	

	//construction

	function ADODBDatabase($dbtype = '', $hostname = '', $dbname = '', $username = '', $password = '') {

		$this->initSettings();

		$this->resetSettings($dbtype, $hostname, $dbname, $username, $password);

	}

	

	function initSettings() {

		$this->db_type = CONFIG_DB_TYPE;

		$this->db_host = CONFIG_DB_HOST;

		$this->db_user = CONFIG_DB_USER;

		$this->db_password = CONFIG_DB_PASSWORD;

		$this->db_name = CONFIG_DB_NAME;

		$this->db_prefix = CONFIG_DB_PREFIX;

		$this->db_pconnect = CONFIG_DB_PCONNECT;

		$this->db_logsql = CONFIG_DB_LOGSQL;

		$this->db_logfile = CONFIG_DB_LOGFILE;

	}

	

	function resetSettings($dbtype = '', $hostname = '', $dbname = '', $username = '', $password = '') {

		if( $hostname == '' ) {

			$this->disconnect();

			$this->setDbType($this->db_type);

			$this->setDbHostName($this->db_host);

			$this->setDbUserName($this->db_user);

			$this->setDbPassword($this->db_password);

			$this->setDbName($this->db_name);

			if( $this->db_logsql ) {

				$this->enableSQLlog = ($this->db_logsql == true);

			}

		} else {

			$this->disconnect();

			$this->setDbType($dbtype);

			$this->setDbHostName($hostname);

			$this->setDbUserName($username);

			$this->setDbPassword($password);

			$this->setDbName($dbname);

		}

	}

	

	function connect($link = '_db_link') {

		if( !isset($this->dbType) ) {

			return false;

		}

		global $$link;

		

		if( $this->db_pconnect == 'true' ) {

			$$link = mysql_pconnect($this->dbHostName, $this->dbUserName, $this->dbPassword) or die('DBECP');

		} else {

			$$link = mysql_connect($this->dbHostName, $this->dbUserName, $this->dbPassword) or die('DBEC');

		}

		

		if( $$link ) {

			mysql_select_db($this->dbName) or die('DBEDB');

			$this->database = $$link;

		}

		

		return $$link;

	}

	

	function checkConnection() {

		if( !isset($this->database) ) {

			$this->connect();

		} else {

			

		}

	}

	

	function disconnect($link = '_db_link') {

		//global $$link;

		

		//return mysql_close($$link);

	}

	

	function close($link = '_db_link') {

		global $$link;

		

		return mysql_close($$link);

	}

	

	function error($sql, $errno, $error) {

		die($errno . '<br>' . $error . '<br>' . $sql);

	}

	

	//QUERY

	function query($sql, $link = '_db_link') {

		global $$link;

		

		$this->checkConnection();

		

		//if( $this->enableSQLlog == 'true') ) {

		//	error_log('QUERY ' . $query . "\n", 3, $this->db_logfile);

		//}

		//$result = mysql_query($sql, $$link) or $this->error($sql, mysql_errno(), mysql_error());

		$result = mysql_query($sql, $$link);

		if( !$result ) {

			//return false;

			$this->error($sql, mysql_errno(), mysql_error());

		}

		//if( $this->enableSQLlog == 'true') ) {

		//	error_log('RESULT ' . $result . ' ' . mysql_errno() . ' ' . mysql_error() . "\n", 3, $this->db_logfile);

		//}

		

		//counter

		$this->addQueryCount();

		

		//log

		$this->addQueryLog($sql);

		

		return $result;

	}

	

	function perform($table, $data, $action = 'insert', $parameters = '', $link = '_db_link') {

		reset($data);

		

		if( $action == 'insert' || $action == 'replace' ) {

			//$sql = 'insert into ' . $table . ' (';

			$sql = $action . ' into ' . $table . ' (';

			while( list($columns, ) = each($data) ) {

				$sql .= $columns . ', ';

			}

			$sql = substr($sql, 0, -2) . ') values (';

			reset($data);

			while( list($columns, $value) = each($data) ) {

				switch( (string)$value ) {

					case 'now()':

						$sql .= 'now(), ';

						break;

					case 'add_one()':

						$sql .= $columns . ' + 1, ';

						break;

					case 'null':

						$sql .= 'null, ';

						break;

					default:

						$sql .= '\'' . $this->str_input($value) . '\', ';

						break;

				}

			}

			$sql = substr($sql, 0, -2) . ')';

			

		} elseif( $action == 'update' ) {

			$sql = 'update ' . $table . ' set ';

			while( list($columns, $value) = each($data) ) {

				switch( (string)$value ) {

					case 'now()':

						$sql .= $columns . ' = now(), ';

						break;

					case 'add_one()':

						$sql .= $columns . ' = ' . $columns . ' + 1, ';

						break;

					case 'null':

						$sql .= $columns .= ' = null, ';

						break;

					default:

						$sql .= $columns . ' = \'' . $this->str_input($value) . '\', ';

						break;

				}

			}

			$sql = substr($sql, 0, -2) . ' where ' . $parameters;

		}

		

		return $this->query($sql, $link);

	}

	

	function add($sql) {

		$result = $this->query($sql);

		if( !$result ) {

			return false;

		}

		

		return true;

	}

	

	function insert($sql) {

		$result = $this->query($sql);

		if( !$result ) {

			return false;

		}

		$insert_id = $this->insert_id();

		

		return $insert_id;

	}

	

	function update($sql) {

		$result = $this->query($sql);

		if( !$result ) {

			return false;

		}

		

		return true;

	}

	

	function delete($sql) {

		$result = $this->query($sql);

		if( !$result ) {

			return false;

		}

		

		return true;

	}

	

	function updateField($table, $id, $field, $value, $pk = '') {

		if( $pk == '' ) {

			$pk = $table . '_id';

		}

		$sql = "update " . $table . " set " . $field . " = '" . func_db_input($value) . "' where " . $pk . " = '" . $id . "' limit 1";

		return $this->update($sql);

	}

	

	function updateFieldC($table, $id, $field, $value, $pk = '') {

		if( $pk == '' ) {

			$pk = $table . '_id';

		}

		$count = $this->getCountA($table, " " . $pk . " != '" . $id . "' and " . $field . " = '" . func_db_input($value) . "' ");

		if( $count > 0 ) {

			return -1;

		} else {

			return $this->updateField($table, $id, $field, $value, $pk);

		}

	}

	

	function updateRow($table, $id, $field, $value, $pk = '') {

		if( $pk == '' ) {

			$pk = $table . '_id';

		}

		$sql = "update " . $table . " set " . $field . " = '" . func_db_input($value) . "' where " . $pk . " = '" . $id . "' limit 1";

		return $this->update($sql);

	}

	

	function updateRowC($table, $id, $field, $value, $pk = '') {

		if( $pk == '' ) {

			$pk = $table . '_id';

		}

		$count = $this->getCountA($table, " " . $pk . " != '" . $id . "' and " . $field . " = '" . func_db_input($value) . "' ");

		if( $count > 0 ) {

			return -1;

		} else {

			return $this->updateRow($table, $id, $field, $value, $pk);

		}

	}

	

	function deleteRow($table, $id, $pk = '') {

		if( $pk == '' ) {

			$pk = $table . '_id';

		}

		$sql = "delete from " . $table . " where " . $pk . " = '" . (int)$id . "' limit 1";

		return $this->delete($sql);

	}

	

	function addA($table, $data) {

		$result = $this->perform($table, $data);

		if( !$result ) {

			return false;

		}

		

		return true;

	}

	

	function insertA($table, $data) {

		$result = $this->perform($table, $data);

		if( !$result ) {

			return false;

		}

		$insert_id = $this->insert_id();

		

		return $insert_id;

	}

	

	function updateA($table, $data, $parameters) {

		$result = $this->perform($table, $data, 'update', $parameters);

		if( !$result ) {

			return false;

		}

		

		return true;

	}

	

	function deleteA($table, $parameters) {

		$sql = "delete from " . $table . " where " . $parameters;

		return $this->delete($sql);

	}

	

	//RESULT

	function fetch_array($db_query) {

		return mysql_fetch_array($db_query, MYSQL_ASSOC);

	}

	

	function fetch_array_assoc($db_query) {

		return mysql_fetch_array($db_query, MYSQL_ASSOC);

	}

	

	function fetch_array_num($db_query) {

		return mysql_fetch_array($db_query, MYSQL_NUM);

	}

	

	function fetch_array_both($db_query) {

		return mysql_fetch_array($db_query, MYSQL_BOTH);

	}

	

	function fetch_assoc($db_query) {

		return mysql_fetch_assoc($db_query);

	}

	

	function fetch_object($db_query) {

		return mysql_fetch_object($db_query);

	}

	

	function fetch_objects($db_query) {

		$objects = array();

		

		while($row = mysql_fetch_object($db_query)) {

			$objects[] = $row;

		}

		

		return $objects;

	}

	

	function num_rows($db_query) {

		return mysql_num_rows($db_query);

	}

	

	function affected_rows($link = '_db_link') {

		global $$link;

		return mysql_affected_rows($$link);

	}

	

	function data_seek($db_query, $row_number) {

		return mysql_data_seek($db_query, $row_number);

	}

	

	function getUniqueID($table) {

		$id = $table . "_id";

		$query = "select MAX(" . $id . ") as last_id from " . $table . "";

		//$result = mysql_query($query);

		//$row = mysql_fetch_array($result);

		$result = $this->query($query);

		$row = $this->fetch_array($result);

		

		$next_id = $row['last_id'] + 1;

		return $next_id;

	}

	

	function insert_id() {

		return mysql_insert_id();

	}

	

	function free_result($db_query) {

		return mysql_free_result($db_query);

	}

	

	function fetch_fields($db_query) {

		$fields_array = array();

		$i = 0;

		while($i < mysql_num_fields($db_query)) {

			$meta = mysql_fetch_field($db_query, $i);

			if( !$meta ) {

				$this->error("fetch_fields", "mysql_fetch_field", "");

			}

			

			$fields_array[] = array(

					'blob' => $meta->blob,

					'max_length' => $meta->max_length,

					'multiple_key' => $meta->multiple_key,

					'name' => $meta->name,

					'not_null' => $meta->not_null,

					'numeric' => $meta->numeric,

					'primary_key' => $meta->primary_key,

					'table' => $meta->table,

					'type' => $meta->type,

					'unique_key' => $meta->unique_key,

					'unsigned' => $meta->unsigned,

					'zerofill' => $meta->zerofill,

						);

			$i++;

		}

		

		return $fields_array;

	}

	

	//

	function getOne($sql) {

		$result = $this->query($sql);

		$row = $this->fetch_array($result);

		

		return $row;

	}

	

	function getRow($sql, $field = '') {

		$result = $this->query($sql);

		$row = $this->fetch_array($result);

		

		if( $field == '' ) {

			return $row;

		} elseif( is_array($field) ) {

			$rowb = array();

			foreach($field as $k => $v) {

				if( isset($row[$v]) ) {

					$rowb[] = $row[$v];

				}

			}

			return $rowb;

		} else {

			return $row[$field];

		}

	}

	

	function getRows($sql, $limit = false) {

		$i = 0;

		$result = $this->query($sql);

		while($row = $this->fetch_array($result)) {

			$rows[] = $row;

			if( $limit > 0 && $i > $limit ) {

				break;

			}

			$i++;

		}

		

		return $rows;

	}

	

	function getRowLight($table, $key, $value) {

		$sql = "select * from " . $table . " where " . $key . " = '" . $value . "'";

		return $this->getRow($sql);

	}

	

	function getRowParam($table, $param) {

		$query = "select * from " . $table . " ";

		if( is_array($param) ) {

			foreach($param as $k => $v) {

				if( $i == 0 ) {

					$query .= " where ";

					$query .= " " . $k . " = '" . $v . "' ";

				} else {

					$query .= " and ";

					$query .= " " . $k . " = '" . $v . "' ";

				}

			}

		}

		

		return $this->getRow($sql);

	}

	

	function getCount($sql) {

		$result = $this->query($sql);

		$row = $this->fetch_array($result);

		

		if( is_array($row) ) {

			if( isset($row['count']) ) {

				return $row['count'];

			} elseif( strpos($sql, 'count(') !== false ) {

				return $row['0'];

			} else {

				return $this->num_rows($result);

			}

		}

		

		return 0;

	}

	

	function getField($sql, $field) {

		$row = $this->getRow($sql);

		if( is_array($row) && isset($row[$field]) ) {

			return $row[$field];

		}

		

		return '';

	}

	

	function checkCount($sql, $unique = false) {

		$count = $this->getCount($sql);

		

		if( $unique == true ) {

			return ($count == 1) ? true : false;

		} else {

			return ($count > 0) ? true : false;

		}

	}

	

	//

	function getOneA($table, $parameters = '') {

		if( $parameters == '' ) {

			$parameters = '1';

		}

		$sql = "select * from " . $table . " where " . $parameters;

		

		return $this->getRow($sql);

	}

	

	function getRowA($table, $parameters = '') {

		if( $parameters == '' ) {

			$parameters = '1';

		}

		$sql = "select * from " . $table . " where " . $parameters;

		

		return $this->getRow($sql);

	}

	

	function getRowsA($table, $parameters = '', $limit = false) {

		if( $parameters == '' ) {

			$parameters = '1';

		}

		$sql = "select * from " . $table . " where " . $parameters;

		

		return $this->getRows($sql, $limit);

	}

	

	function getCountA($table, $parameters = '') {

		if( $parameters == '' ) {

			$parameters = '1';

		}

		$sql = "select count(*) as count from " . $table . " where " . $parameters;

		

		return $this->getCount($sql);

	}

	

	function checkCountA($table, $parameters, $unique = false) {

		$count = $this->getCountA($table, $parameters);

		

		if( $unique == true ) {

			return ($count == 1) ? true : false;

		} else {

			return ($count > 0) ? true : false;

		}

	}

	

	//STRING

	function str_output($string) {

		return htmlspecialchars($string);

	}

	

	function str_input($string) {

		return addslashes($string);

	}

	

	function str_prepare_input($string) {

		if( is_string($string)) {

			return trim(util_string_sanitize(stripslashes($string)));

		} elseif( is_array($string)) {

			reset($string);

			while( list($key, $value) = each($string) ) {

				$string[$key] = $this->str_input($value);

			}

			return $string;

		} else {

			return $string;

		}

	}

	

	function escape_string($string) {

		return mysql_escape_string($string);

	}

	

	//META

	function info($link = '_db_link') {

		global $$link;

		

		return mysql_info($$link);

	}

	

	function getDbs($link = '_db_link') {

		global $$link;

		

		$result = mysql_list_dbs($$link);

		if( !$result ) {

			$this->error("mysql_list_dbs", "mysql_list_dbs", "");

		}

		$dbs = array();

		while($row = mysql_fetch_object($result)) {

    		$dbs[] = $row->Database;

		}

		/*

		//

		$i = 0;

		$num_rows = mysql_num_rows($result);

		while($i < $num_rows) {

			$dbs[] = mysql_db_name($result, $i);

			$i++;

		}

		*/

		

		return $dbs;

	}

	

	function getTables($link = '_db_link') {

		global $$link;

		

		$result = mysql_list_tables($this->dbName, $$link);

		if( !$result ) {

			$this->error("mysql_list_tables", "mysql_list_tables", "");

		}

		$tables = array();

		/*

		while($row = mysql_fetch_row($result)) {

			$tables[] = $row['0'];

		}

		*/

		$num_rows = mysql_num_rows($result);

		for($i=0; $i<$num_rows; $i++) {

			$tables[] = mysql_tablename($result, $i);

		}

		mysql_free_result($result);

		

		return $tables;

	}

	

	function getMetadata($table, $link = '_db_link') {

		global $$link;

		

		$count = 0;

		$id = 0;

		$metadata_array = array();

		

		$result = mysql_list_fields($this->dbName, $table, $$link);

		if( $result < 0 ) {

			$this->error("mysql_list_fields", "mysql_list_fields", "");

		}

		$count = mysql_num_fields($result);

		for($i=0; $i<$count; $i++) {

			$field_table = mysql_field_table($result, $i);

			$field_name = mysql_field_name($result, $i);

			$field_type = mysql_field_type($result, $i);

			$field_len = mysql_field_len($result, $i);

			$field_flags = mysql_field_flags($result, $i);

			

			$metadata_array[] = array(

						'table' => $field_table,

						'name' => $field_name,

						'type' => $field_type,

						'len' => $field_len,

						'flags' => $field_flags,

						//'meta' => $i,

								);

		}

		mysql_free_result($result);

		

		return $metadata_array;

	}

	

	function getFields($table, $link = '_db_link') {

		global $$link;

		

		$sql = "select * from " . $table . " where 1 limit 1";

		$result = mysql_query($sql, $$link);

		if( !$result ) {

			$this->error("getFields", "getFields", "");

		}

		

		$fields_array = array();

		$i = 0;

		while($i < mysql_num_fields($result)) {

			$meta = mysql_fetch_field($result, $i);

			if( !$meta ) {

				$this->error("getFields", "mysql_fetch_field", "");

			}

			

			$fields_array[] = array(

					'blob' => $meta->blob,

					'max_length' => $meta->max_length,

					'multiple_key' => $meta->multiple_key,

					'name' => $meta->name,

					'not_null' => $meta->not_null,

					'numeric' => $meta->numeric,

					'primary_key' => $meta->primary_key,

					'table' => $meta->table,

					'type' => $meta->type,

					'unique_key' => $meta->unique_key,

					'unsigned' => $meta->unsigned,

					'zerofill' => $meta->zerofill,

						);

			$i++;

		}

		

		return $fields_array;

	}

	

	function getFieldsA($table) {

		$fields_a = array();

		

		$fields = $this->getFields($table);

		if( is_array($fields) ) {

			for($i=0, $n=sizeof($fields); $i<$n; $i++) {

				$fields_a[] = $fields[$i]['name'];

			}

		}

		

		return $fields_a;

	}

	

	//DB

	function createDatabase($db_name, $link = '_db_link') {

		/*

		//

		global $$link;

		$result = mysql_create_db($db_name, $$link);

		if( $result ) {

			return true;

		}

		*/

		$sql = "CREATE DATABASE '" . $db_name . "'";

		$result = $this->query($sql, $link);

		if( $result ) {

			return true;

		}

		

		return false;

	}

	

	function createTable($table_name, $sql, $link = '_db_link') {

		$tables = $this->getTables($link);

		if( in_array($db_name, $tables) ) {

			return false;

		}

		$charset = '';

		$sql = $this->createTableSQL($sql, $charset);

		

		return $this->query($sql);

	}

	

	function createTableSQL($sql, $charset = '') {

		$type = strtoupper(preg_replace("/^\s*CREATE TABLE\s+.+\s+\(.+?\).*(ENGINE|TYPE)\s*=\s*([a-z]+?).*$/isU", "\\2", $sql));

		$type = in_array($type, array('MYISAM', 'HEAP')) ? $type : 'MYISAM';

		$sql = preg_replace("/^\s*(CREATE TABLE\s+.+\s+\(.+?\)).*$/isU", "\\1", $sql) . (mysql_get_server_info() > '4.1' ? " ENGINE=$type DEFAULT CHARSET=$dbcharset" : " TYPE=$type");

		

		return $sql;

	}

	

	function getIncrementID($table) {

		$row = $this->getRow("SHOW TABLE STATUS LIKE '" . $table . "'");

		if( $row['Auto_increment'] ) {

			return $row['Auto_increment'] - 1;

		}

		

		return 0;

	}

	

	function getMaxID() {

		$row = $this->getRow("SHOW TABLE STATUS LIKE '" . $table . "'");

		if( $row['Auto_increment'] ) {

			return $row['Auto_increment'] - 1;

		}

		

		return 0;

	}

	

	//query Count Operation

	function addQueryCount() {

		global $system_db_query_count;

		if( !isset($system_db_query_count) ) {

			$system_db_query_count = 0;

		}

		

		$system_db_query_count++;

		$this->queryCount++;

	}

	

	function getQueryCount() {

		global $system_db_query_count;

		

		if( isset($system_db_query_count) ) {

			return $system_db_query_count;

		}

		return $this->queryCount;

	}

	

	//

	function addQueryLog($sql) {

		global $system_db_query_logs;

		if( isset($system_db_query_logs) && $system_db_query_logs == -1 ) {

			return false;

		}

		global $system_db_query_log;

		if( !isset($system_db_query_log) ) {

			$system_db_query_log = array();

		}

		

		$system_db_query_log[] = $sql;

	}

	

	function getQueryLog() {

		global $system_db_query_log;

		

		if( isset($system_db_query_log) ) {

			return $system_db_query_log;

		}

		return $this->queryLog;

	}

	

	//

	function getCachedRow($sql) {

		

	}

	

	//

	function _get_hash_key($key) {

		return abs(crc32($key)) . '_' . md5($key);

	}

}

//

?>