/ php-ppcms/includes/classes/database.mysql.link.class.php
PHP | 887 lines | 671 code | 153 blank | 63 comment | 97 complexity | 8f8848be24aa5c49ad4f5fd6ff3fd83d MD5 | raw file
Possible License(s): LGPL-2.1, GPL-2.0
- <?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);
- }
- }
- //
- ?>