diff options
Diffstat (limited to 'h-source/Library/Db')
| -rwxr-xr-x | h-source/Library/Db/Mysql.php | 179 | ||||
| -rw-r--r-- | h-source/Library/Db/Mysqli.php | 265 |
2 files changed, 401 insertions, 43 deletions
diff --git a/h-source/Library/Db/Mysql.php b/h-source/Library/Db/Mysql.php index d19d24a..e0dcfe0 100755 --- a/h-source/Library/Db/Mysql.php +++ b/h-source/Library/Db/Mysql.php @@ -2,7 +2,7 @@ // EasyGiant is a PHP framework for creating and managing dynamic content // -// Copyright (C) 2009 - 2011 Antonio Gallo +// Copyright (C) 2009 - 2014 Antonio Gallo (info@laboratoriolibero.com) // See COPYRIGHT.txt and LICENSE.txt. // // This file is part of EasyGiant @@ -24,16 +24,34 @@ if (!defined('EG')) die('Direct access not allowed!'); //class to manage the database //singleton! -class Db_Mysql { +class Db_Mysql +{ + + private $autocommit = true; + private $transactionBatchSize = 100; + + public $transactionBatch = array(); public $query = null; //the last query executed + public $queries = array(); //array containing all the queries executed + public $charsetError = true; //true: non eccor occurred during the modification of the connection charset, false: one error occurred public $charset = null; //the charset of the client connection private static $instance = null; //instance of this class private $dblink; - private $fieldsType = array('tinyint','smallint','int','mediumint','bigint','float','double'); + + private $charTypes = array('varchar','char'); + private $textTypes = array('tinytext','text','mediumtext','longtext'); + private $integerTypes = array('tinyint','smallint','int','mediumint','bigint'); + private $floatTypes = array('real','float','double'); + private $dateTypes = array('date'); + private $enumTypes = array('enum'); + private $decimalTypes = array('decimal'); + private $uniqueIndexStrings = array('UNI'); + + private $fieldsType = array(); /** *connect to the database @@ -51,7 +69,8 @@ class Db_Mysql { private function __construct($host,$user,$pwd,$db_name) { - + $this->fieldsType = array_merge($this->integerTypes, $this->floatTypes); + $this->dblink = mysql_connect($host,$user,$pwd); if ($this->dblink === FALSE) { @@ -78,7 +97,46 @@ class Db_Mysql { return self::$instance; } - + public function getUniqueIndexStrings() + { + return $this->uniqueIndexStrings; + } + + public function getTextTypes() + { + return $this->textTypes; + } + + public function getDecimalTypes() + { + return $this->decimalTypes; + } + + public function getEnumTypes() + { + return $this->enumTypes; + } + + public function getCharTypes() + { + return $this->charTypes; + } + + public function getIntegerTypes() + { + return $this->integerTypes; + } + + public function getFloatTypes() + { + return $this->floatTypes; + } + + public function getDateTypes() + { + return $this->dateTypes; + } + //close the connection public function disconnect() { @@ -163,16 +221,29 @@ class Db_Mysql { public function get_num_rows($table,$where=null,$group_by=null,$on=array(),$using=array(),$join=array()) { - $query = $this->createSelectQuery($table,'*',$where,$group_by,null,null,$on,$using,$join); + $select = isset($group_by) ? "*" : 'count(*) as number'; + + $query = $this->createSelectQuery($table,$select,$where,$group_by,null,null,$on,$using,$join); $this->query=$query; + $this->queries[] = $query; $ris = mysql_query($query); if ($ris) { - $num_rows = mysql_num_rows($ris); - return $num_rows; + + if (isset($group_by)) + { + $num_rows = mysql_num_rows($ris); + } + else + { + $row = mysql_fetch_array($ris); + $num_rows = $row['number']; + } + + return (int)$num_rows; } else { - return false; + return 0; } } @@ -182,6 +253,8 @@ class Db_Mysql { $query = $this->createSelectQuery($table,"$func($field) AS m",$where,$group_by,null,null,$on,$using,$join); $this->query = $query; + $this->queries[] = $query; + $result = mysql_query($query); if ($result) { @@ -223,6 +296,8 @@ class Db_Mysql { $query = $this->createSelectQuery($table,$fields,$where,$group_by,$order_by,$limit,$on,$using,$join); $this->query = $query; + $this->queries[] = $query; + $result = mysql_query($query); return $this->getData($result); } @@ -259,32 +334,73 @@ class Db_Mysql { } else { - return false; + return array(); } } - //return an array containing all the types of the fields (indicated in $fields) of a table (indicated in $table) - public function getTypes($table, $fields) + + private function getFieldsFeature($feature, $table, $fields, $full = false, $associative = false) { $query = "DESCRIBE $table;"; $result = mysql_query($query); $temp = array(); while ($row = mysql_fetch_assoc($result)) { - $e = explode('(',$row['Type']); - $temp[$row['Field']] = reset($e); + if ($full) + { + $temp[$row['Field']] = $row[$feature]; + } + else + { + $e = explode('(',$row[$feature]); + $temp[$row['Field']] = strcmp($feature,"Type") === 0 ? strtolower(reset($e)) : reset($e); + } } + $this->queries[] = $query; + + //return all fields types + if ($fields === "*") + { + $fields = implode(",",array_keys($temp)); + } + $types = array(); $fields = explode(',',$fields); for ($i = 0; $i < count($fields); $i++) { if (!array_key_exists($fields[$i],$temp)) return false; - $types[] = $temp[$fields[$i]]; + + if ($associative) + { + $types[$fields[$i]] = $temp[$fields[$i]]; + } + else + { + $types[] = $temp[$fields[$i]]; + } } return $types; } + //return an array containing all the keys of the fields (indicated in $fields) of a table (indicated in $table) + public function getKeys($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Key', $table, $fields, $full, $associative); + } + + //return an array containing all the default values of the fields (indicated in $fields) of a table (indicated in $table) + public function getDefaultValues($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Default', $table, $fields, $full, $associative); + } + + //return an array containing all the types of the fields (indicated in $fields) of a table (indicated in $table) + public function getTypes($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Type', $table, $fields, $full, $associative); + } + public function insert($table,$fields,$values) { #$table is a string @@ -312,6 +428,8 @@ class Db_Mysql { $values = implode(',',$values); $query="INSERT INTO $table ($fields) VALUES ($values);"; $this->query = $query; + $this->queries[] = $query; + $ris = mysql_query($query); #check the result @@ -320,12 +438,34 @@ class Db_Mysql { } else { return false; } - + } else { return false; } } + //set the autocommit attribute + public function setAutocommit($value) + { + } + + //set the transactionBatchSize attribute + public function setTransactionBatchSize($size) + { + } + + //commit a batch of queries + //$batch: array of queries + public function commitBatch($batch) + { + } + + //commit the transaction + public function commitTransaction() + { + return false; + } + // Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT). public function lastId() { @@ -367,6 +507,8 @@ class Db_Mysql { $str=implode(',',$str); $query="UPDATE $table SET $str $where;"; $this->query=$query; + $this->queries[] = $query; + $ris = mysql_query($query); #check the result @@ -375,6 +517,7 @@ class Db_Mysql { } else { return false; } + } else { return false; } @@ -390,6 +533,8 @@ class Db_Mysql { // } $query="DELETE FROM $table $where;"; $this->query=$query; + $this->queries[] = $query; + $ris = mysql_query($query); #check the result @@ -423,6 +568,8 @@ class Db_Mysql { public function query($query) { $this->query = $query; + $this->queries[] = $query; + $result = mysql_query($query); if ($result === false) { diff --git a/h-source/Library/Db/Mysqli.php b/h-source/Library/Db/Mysqli.php index b65f470..bd3ce1c 100644 --- a/h-source/Library/Db/Mysqli.php +++ b/h-source/Library/Db/Mysqli.php @@ -2,7 +2,7 @@ // EasyGiant is a PHP framework for creating and managing dynamic content // -// Copyright (C) 2009 - 2011 Antonio Gallo +// Copyright (C) 2009 - 2014 Antonio Gallo (info@laboratoriolibero.com) // See COPYRIGHT.txt and LICENSE.txt. // // This file is part of EasyGiant @@ -27,14 +27,31 @@ if (!defined('EG')) die('Direct access not allowed!'); class Db_Mysqli { + private $autocommit = true; + private $transactionBatchSize = 100; + + public $transactionBatch = array(); + public $query = null; //the last query executed + public $queries = array(); //array containing all the queries executed + public $charsetError = true; //true: non eccor occurred during the modification of the connection charset, false: one error occurred public $charset = null; //the charset of the client connection private static $instance = null; //instance of this class private $db; - private $fieldsType = array('tinyint','smallint','int','mediumint','bigint','float','double'); + + private $charTypes = array('varchar','char'); + private $textTypes = array('tinytext','text','mediumtext','longtext'); + private $integerTypes = array('tinyint','smallint','int','mediumint','bigint'); + private $floatTypes = array('real','float','double'); + private $dateTypes = array('date'); + private $enumTypes = array('enum'); + private $decimalTypes = array('decimal'); + private $uniqueIndexStrings = array('UNI'); + + private $fieldsType = array(); //PHP-Mysql charset translation table private $charsetTranslationTable = array( @@ -53,7 +70,8 @@ class Db_Mysqli private function __construct($host,$user,$pwd,$db_name) { - + $this->fieldsType = array_merge($this->integerTypes, $this->floatTypes); + $this->db = new mysqli($host,$user,$pwd,$db_name); if (mysqli_connect_error()) @@ -85,7 +103,46 @@ class Db_Mysqli return self::$instance; } - + public function getUniqueIndexStrings() + { + return $this->uniqueIndexStrings; + } + + public function getTextTypes() + { + return $this->textTypes; + } + + public function getDecimalTypes() + { + return $this->decimalTypes; + } + + public function getEnumTypes() + { + return $this->enumTypes; + } + + public function getCharTypes() + { + return $this->charTypes; + } + + public function getIntegerTypes() + { + return $this->integerTypes; + } + + public function getFloatTypes() + { + return $this->floatTypes; + } + + public function getDateTypes() + { + return $this->dateTypes; + } + //close the connection public function disconnect() { @@ -170,16 +227,30 @@ class Db_Mysqli public function get_num_rows($table,$where=null,$group_by=null,$on=array(),$using=array(),$join=array()) { - $query = $this->createSelectQuery($table,'*',$where,$group_by,null,null,$on,$using,$join); + $select = isset($group_by) ? "*" : 'count(*) as number'; + + $query = $this->createSelectQuery($table,$select,$where,$group_by,null,null,$on,$using,$join); $this->query = $query; + $this->queries[] = $query; + $ris = $this->db->query($query); if ($ris) { - $num_rows = $ris->num_rows; + + if (isset($group_by)) + { + $num_rows = $ris->num_rows; + } + else + { + $row = $ris->fetch_array(); + $num_rows = $row['number']; + } + $ris->close(); - return $num_rows; + return (int)$num_rows; } else { - return false; + return 0; } } @@ -188,6 +259,8 @@ class Db_Mysqli $query = $this->createSelectQuery($table,"$func($field) AS m",$where,$group_by,null,null,$on,$using,$join); $this->query = $query; + $this->queries[] = $query; + $result = $this->db->query($query); if ($result) { @@ -230,6 +303,8 @@ class Db_Mysqli $query = $this->createSelectQuery($table,$fields,$where,$group_by,$order_by,$limit,$on,$using,$join); $this->query = $query; + $this->queries[] = $query; + $result = $this->db->query($query); return $this->getData($result); } @@ -262,45 +337,85 @@ class Db_Mysqli $result->close(); return $data; } else { - return false; + return array(); } } - //return an array containing all the types of the fields (indicated in $fields) of a table (indicated in $table) - public function getTypes($table, $fields) + private function getFieldsFeature($feature, $table, $fields, $full = false, $associative = false ) { $query = "DESCRIBE $table;"; $result = $this->db->query($query); $temp = array(); while ($row = $result->fetch_assoc()) { - $e = explode('(',$row['Type']); - $temp[$row['Field']] = reset($e); + if ($full) + { + $temp[$row['Field']] = $row[$feature]; + } + else + { + $e = explode('(',$row[$feature]); + $temp[$row['Field']] = strcmp($feature,"Type") === 0 ? strtolower(reset($e)) : reset($e); + } } $result->close(); + $this->queries[] = $query; + + //return all fields types + if ($fields === "*") + { + $fields = implode(",",array_keys($temp)); + } + $types = array(); $fields = explode(',',$fields); for ($i = 0; $i < count($fields); $i++) { if (!array_key_exists($fields[$i],$temp)) return false; - $types[] = $temp[$fields[$i]]; + + if ($associative) + { + $types[$fields[$i]] = $temp[$fields[$i]]; + } + else + { + $types[] = $temp[$fields[$i]]; + } } return $types; } + //return an array containing all the keys of the fields (indicated in $fields) of a table (indicated in $table) + public function getKeys($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Key', $table, $fields, $full, $associative); + } + + //return an array containing all the default values of the fields (indicated in $fields) of a table (indicated in $table) + public function getDefaultValues($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Default', $table, $fields, $full, $associative); + } + + //return an array containing all the types of the fields (indicated in $fields) of a table (indicated in $table) + public function getTypes($table, $fields, $full = false, $associative = false) + { + return $this->getFieldsFeature('Type', $table, $fields, $full, $associative); + } + public function insert($table,$fields,$values) { - #$table is a string #$fields has to be a string with comma as separator: name1,name2,... #$values has to be an array + $values = array_values($values); if (strcmp($fields,'') !== 0) { //get the type of the fields $types = $this->getTypes($table,$fields); if (!$types) return false; - + for($i = 0; $i < count($values); $i++) { if (!in_array($types[$i],$this->fieldsType)) @@ -316,21 +431,103 @@ class Db_Mysqli $values = implode(',',$values); $query="INSERT INTO $table ($fields) VALUES ($values);"; $this->query=$query; + $this->queries[] = $query; - $ris = $this->db->query($query); + if ($this->autocommit) + { + $ris = $this->db->query($query); - #check the result - if ($ris) { + #check the result + if ($ris) { + return true; + } else { + return false; + } + } + else + { + $this->transactionBatch[] = $query; return true; - } else { - return false; } - } else { return false; } } + //set the autocommit attribute + public function setAutocommit($value) + { + if ($value === true or $value === false) + { + $this->autocommit = $value; + $this->db->autocommit($value); + } + else + { + $this->autocommit = true; + $this->db->autocommit(true); + } + } + + //set the transactionBatchSize attribute + public function setTransactionBatchSize($size) + { + $this->transactionBatchSize = abs($size); + } + + //commit a batch of queries + //$batch: array of queries + public function commitBatch($batch) + { + foreach ($batch as $sql) + { + $this->db->query($sql); + } + + if (!$this->autocommit and $this->db->commit()) + { + return true; + } + else + { + return false; + } + } + + //commit the transaction + public function commitTransaction() + { + $returnArray = array(); + + if (!$this->autocommit) + { + if (count($this->transactionBatch) > 0) + { + if ($this->transactionBatchSize === 0) + { + $returnArray[] = $this->commitBatch($this->transactionBatch); + } + else + { + $batchArray = array_chunk($this->transactionBatch, $this->transactionBatchSize); + + foreach ($batchArray as $batch) + { + $returnArray[] = $this->commitBatch($batch); + } + } + } + } + + if (count(array_filter($returnArray)) === count($returnArray)) + { + $this->transactionBatch = array(); + return true; + } + + return false; + } + // Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT). public function lastId() { @@ -372,13 +569,23 @@ class Db_Mysqli $str=implode(',',$str); $query="UPDATE $table SET $str $where;"; $this->query=$query; - $ris = $this->db->query($query); + $this->queries[] = $query; + + if ($this->autocommit) + { + $ris = $this->db->query($query); - #check the result - if ($ris) { + #check the result + if ($ris) { + return true; + } else { + return false; + } + } + else + { + $this->transactionBatch[] = $query; return true; - } else { - return false; } } else { return false; @@ -395,6 +602,8 @@ class Db_Mysqli // } $query="DELETE FROM $table $where;"; $this->query=$query; + $this->queries[] = $query; + $ris = $this->db->query($query); #check the result @@ -429,6 +638,8 @@ class Db_Mysqli public function query($query) { $this->query = $query; + $this->queries[] = $query; + $result = $this->db->query($query); if ($result === true) { |