aboutsummaryrefslogtreecommitdiff
path: root/h-source/Library/Db
diff options
context:
space:
mode:
Diffstat (limited to 'h-source/Library/Db')
-rwxr-xr-xh-source/Library/Db/Mysql.php179
-rw-r--r--h-source/Library/Db/Mysqli.php265
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)
{