Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 2 |
|
57.14% |
24 / 42 |
CRAP | |
70.48% |
234 / 332 |
| IaMysqli | |
0.00% |
0 / 1 |
|
60.00% |
24 / 40 |
665.64 | |
73.35% |
234 / 319 |
| __construct | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| __destruct | |
0.00% |
0 / 1 |
3.04 | |
83.33% |
5 / 6 |
|||
| connectionInfo_set | |
100.00% |
1 / 1 |
6 | |
100.00% |
8 / 8 |
|||
| connect_toString | |
0.00% |
0 / 1 |
3.58 | |
60.00% |
3 / 5 |
|||
| connect | |
0.00% |
0 / 1 |
7.29 | |
81.82% |
9 / 11 |
|||
| connect_real | |
100.00% |
1 / 1 |
6 | |
100.00% |
15 / 15 |
|||
| reconnect | |
0.00% |
0 / 1 |
3.14 | |
75.00% |
3 / 4 |
|||
| close | |
100.00% |
1 / 1 |
5 | |
100.00% |
8 / 8 |
|||
| begin | |
0.00% |
0 / 1 |
2.02 | |
83.33% |
5 / 6 |
|||
| commit | |
0.00% |
0 / 1 |
4.01 | |
92.86% |
13 / 14 |
|||
| rollback | |
0.00% |
0 / 1 |
4.01 | |
92.86% |
13 / 14 |
|||
| autocommit | |
0.00% |
0 / 1 |
9.02 | |
93.33% |
14 / 15 |
|||
| autocommitToDefault | |
0.00% |
0 / 1 |
4.05 | |
85.71% |
6 / 7 |
|||
| transaction | |
0.00% |
0 / 1 |
8.63 | |
78.57% |
11 / 14 |
|||
| queryArray | |
100.00% |
1 / 1 |
3 | |
100.00% |
4 / 4 |
|||
| query | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| insertAndGetId | |
100.00% |
1 / 1 |
2 | |
100.00% |
3 / 3 |
|||
| single_read | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| singleton | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| singleton_full | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| selectVector | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| selectKeyValue | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| selectArrayKey | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| selectArrayIndex | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| valid_unique_key | |
0.00% |
0 / 1 |
30.00 | |
0.00% |
0 / 8 |
|||
| last_insert_id | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| found_rows | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| metaData_get | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| metaData_clear | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| throwSqlException_set | |
100.00% |
1 / 1 |
3 | |
100.00% |
7 / 7 |
|||
| throwSqlException_get | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| trace_get | |
100.00% |
1 / 1 |
3 | |
100.00% |
3 / 3 |
|||
| log_trace | |
100.00% |
1 / 1 |
3 | |
100.00% |
6 / 6 |
|||
| errorLog_get | |
0.00% |
0 / 1 |
6.00 | |
0.00% |
0 / 3 |
|||
| begins_get | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| log_sql_error | |
0.00% |
0 / 1 |
3.07 | |
80.00% |
4 / 5 |
|||
| runSql | |
0.00% |
0 / 1 |
36 | |
96.10% |
74 / 77 |
|||
| selectArrayMultiKey | |
0.00% |
0 / 1 |
42.00 | |
0.00% |
0 / 17 |
|||
| selectArrayMultiKeyDo | |
0.00% |
0 / 1 |
306.00 | |
0.00% |
0 / 39 |
|||
| prepare | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| IacSqlException | |
0.00% |
0 / 1 |
|
0.00% |
0 / 2 |
30.00 | |
0.00% |
0 / 13 |
| __construct | |
0.00% |
0 / 1 |
20.00 | |
0.00% |
0 / 12 |
|||
| getSqlStatement | |
0.00% |
0 / 1 |
2.00 | |
0.00% |
0 / 1 |
|||
| <?php | |
| /** | |
| * mysqli db interface and convience methods. | |
| * | |
| * @author Informatica Asociada | |
| * @copyright 2015 | |
| * @version 1.0.2 | |
| * | |
| */ | |
| namespace ia\Sql\Mysql; | |
| use Exception; | |
| use ia\Util\Str; | |
| use mysqli_driver; | |
| use mysqli_result; | |
| use mysqli_sql_exception; | |
| use mysqli_init; | |
| //Completar example or {@example /path/to/example.php startline number of lines } | |
| //DUDA begin/commit/rollback/autcommit/autocommitDefault try/catch | |
| // https://github.com/swoole/mysql-async/blob/master/Swoole/Async/MySQL.php | |
| //PRBLM: el cahce prblm | |
| // "/*" . MYSQLND_QC_DISABLE_SWITCH . "*/S... http://php.net/manual/en/mysqlnd-qc.configuration.php#ini.mysqlnd-qc.cache-no-table | |
| //PRBLM: el same server problem en transaction_exexcute!, array_execute!, insert for id? y en otros esperando resultado? | |
| /** | |
| * mysqli db interface and convience methods. | |
| * | |
| * @version 1.0.2 | |
| * 2017-05-20 rjsb add selectMultiKey functions | |
| * 2018-05-23 rjsb close mysqli on destruct, on some php errors connection not closing | |
| */ | |
| class IaMysqli { | |
| /** | |
| * $mysqli_options | |
| * | |
| * @var array $mysqli_options, change before connecting, default: [[MYSQLI_OPT_CONNECT_TIMEOUT, 5], [MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT=1'] ), | |
| * | |
| * @link http://php.net/manual/en/mysqli.options.php | |
| * @link http://php.net/manual/en/mysqli.constants.php | |
| */ | |
| public $mysqli_options = [ | |
| [\MYSQLI_INIT_COMMAND, 'SET AUTOCOMMIT=1'], | |
| [\MYSQLI_OPT_CONNECT_TIMEOUT, 5], | |
| ]; | |
| /** | |
| * Character set for connection, usually set to the same as database's characterset. | |
| * | |
| * @var string $charset connection character set, defaults to ut8, change before connecting, default utf8 | |
| */ | |
| public $charset = 'utf8'; | |
| /** | |
| * $coalition | |
| * Coalition for conection, usually set to the same coallition as database's. | |
| * | |
| * @var string $coalition connection coalition defaults to ut8_general_ci, change before connecting, default: utf8_general_ci | |
| */ | |
| public $coalition = 'utf8_general_ci'; | |
| /** | |
| * $metDataOn | |
| * On true stores field_info of last select statement | |
| * | |
| * @var bool $metaDataOn on true store field_info for each query, default false | |
| * @see IaMysqli::metaData_get() IaMysqli::metaData_get() | |
| */ | |
| public $metaDataOn = false; | |
| /** | |
| * $traceOn | |
| * True stores Sql commands. | |
| * | |
| * @var bool $traceOn on true keep log of Sql commands issued, default false | |
| */ | |
| public $traceOn = false; | |
| /** | |
| * $register_warnings | |
| * On true stores last 5 warnings. | |
| * | |
| * @var bool $register_warnings on true keep small log of Sql warnings | |
| * | |
| */ | |
| public $register_warnings = false; | |
| /** | |
| * $mysqli | |
| * php class mysqli for connection | |
| * | |
| * @var object $mysqli mysqli instance used | |
| */ | |
| public $mysqli = /*.(mysqli).*/ null; | |
| /** | |
| * $affected_rows | |
| * Affected rows of last SQL statement (insert, update, delete, ...) | |
| * | |
| * @var int $affected_rows number of affected rows by last command | |
| */ | |
| public $affected_rows=0; | |
| /** | |
| * $num_rows | |
| * Rows retreived in last select stament | |
| * | |
| * @var int $num_rows number of retreived rows by last command | |
| */ | |
| public $num_rows; | |
| /** | |
| * $autocommitDefault | |
| * After a commit or rollback resets autocommit mode to this value | |
| * | |
| * @var bool $autocommitDefault default true. autocommit mode to set on connect, reconnect, commit & rollback | |
| */ | |
| public $autocommitDefault = /*.(bool).*/ true; | |
| /** | |
| * $retries | |
| * Number of times to resend transaction (or single query outside a transaction) when instructed by server | |
| * | |
| * @var int $retries number of retries to do, default 3 | |
| */ | |
| public $retries = 3; | |
| /** | |
| * $connInfo | |
| * Connection info and credentials | |
| * | |
| * @var array $connInfo | |
| * @see IaMysqli::connectionInfo_set IaMysqli::connectionInfo_set | |
| */ | |
| public $connInfo=array(); | |
| /** | |
| * $connected_to | |
| * Last connected to, connection info and credentials. | |
| * | |
| * @var array $connected_to | |
| * @see IaMysqli::real_connect IaMysqli::real_connect | |
| */ | |
| public $connected_to = array(); | |
| /** | |
| * $retry_usleep | |
| * milli-seconds to wait before re-issuing statements or trying to reconnect to db | |
| * | |
| * @var int $retry_sleep number milli-seconds to wait before re-issuing statements | |
| */ | |
| public $retry_usleep = 300; | |
| /** | |
| * $preparedLast | |
| * Last prepared statement's Sql command | |
| * | |
| * @var string $preparedLast last prepared Sql command | |
| * | |
| * @see IaMysqli::prepare() IaMysqli::prepare() | |
| */ | |
| public $preparedLast=''; | |
| /** | |
| * EXEC_ARRAY_INDEX | |
| * Return recordset as zero based numerical array of arrays (either asociative by field name or numerically) | |
| * | |
| * @const int EXEC_ARRAY_INDEX return resultset as numeric array | |
| * @see IaMysqli::selectArrayIndex() IaMysqli::selectArrayIndex() | |
| * | |
| */ | |
| protected $EXEC_ARRAY_INDEX = 1; | |
| /** | |
| * EXEC_SINGLE_READ | |
| * Return first column of first row, from recordset | |
| * | |
| * @const int EXEC_SINGLE_READ return first column of first row | |
| * @see IaMysqli::single_read() IaMysqli::single_read() | |
| * | |
| */ | |
| protected $EXEC_SINGLE_READ = 2; | |
| /** | |
| * EXEC_SIGLETON | |
| * Return an array from the first row only, (either asociative by field name or numerically) | |
| * | |
| * @const int EXEC_SIGLETON return resultset first row as array | |
| * @see IaMysqli::singleton() IaMysqli::singleton() | |
| * | |
| */ | |
| protected $EXEC_SINGLETON = 3; | |
| /** | |
| * EXEC_SIGLETON_FULL | |
| * Return an asociative array from the first row only, if not found return an associative array with all the fields set to '' | |
| * | |
| * @const int EXEC_SIGLETON_FULL return resultset first row as array, if not found array with fields | |
| * @see IaMysqli::singleton_full() IaMysqli::singleton_full() | |
| * | |
| */ | |
| protected $EXEC_SINGLETON_FULL = 4; | |
| /** | |
| * EXEC_VECTOR | |
| * Return a zero based numerical array with the first column of each row. | |
| * | |
| * @const int EXEC_VECTOR return resultset's first column as numeric array | |
| * @see IaMysqli::selectVector() IaMysqli::selectVector() | |
| * | |
| */ | |
| protected $EXEC_VECTOR = 5; | |
| /** | |
| * EXEC_KEY_VALUE | |
| * Return an associative array: array( col1=>col2, col1=>col2, ... ). | |
| * | |
| * @const int EXEC_KEY_VALUE return resultset's second column as associative array indexed by first column | |
| * @see IaMysqli::selectKeyValue() IaMysqli::selectKeyValue() | |
| * | |
| */ | |
| protected $EXEC_KEY_VALUE = 6; | |
| /** | |
| * EXEC_ARRAY_KEY | |
| * | |
| * @const int EXEC_ARRAY_KEY return resultset's as associative array indexed by column $key | |
| * @see IaMysqli::select_key_array() IaMysqli::select_key_array() | |
| * | |
| */ | |
| protected $EXEC_ARRAY_KEY = 7; | |
| /** | |
| * EXEC_QUERY | |
| * Return an associative array indexed by the specified field. | |
| * | |
| * @const int EXEC_QUERY execute a DML query | |
| * @see IaMysqli::query() IaMysqli::query() | |
| * | |
| */ | |
| protected $EXEC_QUERY = 8; | |
| /** | |
| * $trace | |
| * Array with al Sql statements issued. | |
| * | |
| * @var array $trace log of Sql commands issued | |
| * @see IaMysqli::trace_get() IaMysqli::trace_get() | |
| * @see IaMysqli::log_trace() IaMysqli::log_trace() | |
| * | |
| */ | |
| protected $trace = array(); | |
| /** | |
| * $metaData | |
| * Stores field_info of last select statement. | |
| * | |
| * @var array $metaData field info of last select query | |
| * @see IaMysqli::metaData_get() IaMysqli::metaData_get() | |
| * | |
| */ | |
| protected $metaData = array(); | |
| /** | |
| * $errorLog | |
| * Stores Sql error messages. | |
| * | |
| * @var array $errorLog log of Sql errors | |
| * @see IaMysqli::log_sql_error() IaMysqli::log_sql_error() | |
| * @see IaMysqli::errorLog_get() IaMysqli::errorLog_get() | |
| * | |
| * | |
| */ | |
| protected $errorLog = array(); | |
| /** | |
| * $retryOnErrors | |
| * Mysqli errors on which to retry the transaction or query if not inside a transaction. | |
| * | |
| * @var array $retryOnErrors mysqli errors on wichi to retry if not inside transaction | |
| * * Error: 1015 SQLSTATE: HY000 (ER_CANT_LOCK) Message: Can't lock file (errno: %d) | |
| * * Error?: 1027 SQLSTATE: HY000 (ER_FILE_USED) Message: '%s' is locked against change | |
| * * Error: 1689 SQLSTATE: HY000 (ER_LOCK_ABORTED) Message: Wait on a lock was aborted due to a pending exclusive lock | |
| * * Error: 1205 SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT) Message: Lock wait timeout exceeded; try restarting transaction | |
| * * Error: 1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) Message: The total number of locks exceeds the lock table size | |
| * * Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK) Message: Deadlock found when trying to get lock; try restarting transaction | |
| * * Error: 1622 SQLSTATE: HY000 (ER_WARN_ENGINE_TRANSACTION_ROLLBACK) Message: Storage engine %s does not support rollback for this statement. | |
| * Transaction rolled back and must be restarted | |
| * * Error: 1614 SQLSTATE: XA102 (ER_XA_RBDEADLOCK) Message: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected | |
| * * Error: 2006 (CR_SERVER_GONE_ERROR) Message: MySQL server has gone away | |
| * * Error: 2013 (CR_SERVER_LOST) Message: Lost connection to MySQL server during query | |
| * | |
| * @see IaMysqli::runSql() IaMysqli::runSql() | |
| * | |
| */ | |
| protected $retryOnErrors = array(1015=>1, 1689=>1, 1205=>1, 1206=>1, 1213=>1, 1622=>1, 1614=>1, 2006=>1, 2013=>1 ); | |
| /** | |
| * $reconnectOnErrors | |
| * Mysqli errors on which to try to reconnect. | |
| * | |
| * @var array $reconnectOnErrors mysql errorcodes on which to reconnect | |
| * | |
| * Error: 2006 (CR_SERVER_GONE_ERROR) Message: MySQL server has gone away | |
| * Error: 2013 (CR_SERVER_LOST) Message: Lost connection to MySQL server during query | |
| * Error: 2024 (CR_PROBE_SLAVE_CONNECT) Message: Error connecting to slave: | |
| * Error: 2025 (CR_PROBE_MASTER_CONNECT) Message: Error connecting to master: | |
| * Error: 2026 (CR_SSL_CONNECTION_ERROR) Message: SSL connection error: %s | |
| * -- | |
| * Error: 2020 (CR_NET_PACKET_TOO_LARGE) Message: Got packet bigger than 'max_allowed_packet' bytes | |
| * On queries larger than max_ a lost connection error may be returned | |
| * | |
| * | |
| */ | |
| protected $reconnectOnErrors = array(2006=>1, 2013=>1); | |
| /** | |
| * $begins | |
| * Counts the number of begins pending commit or rollback. | |
| * | |
| * @var int $begins number of unclosed begins | |
| * | |
| * | |
| */ | |
| protected $begins = 0; | |
| protected $SqlInfo; | |
| ////////////////////////////////////////////////// | |
| // CONSTRUCTOR & DESTRUCTOR | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::__construct() | |
| * constructor. | |
| * | |
| * @param string $host host to connect, defaults to php.ini setting | |
| * @param string $username login, to connect, defaults to php.ini setting | |
| * @param string $passwd password, to connect, defaults to php.ini setting | |
| * @param string $dbname databasename | |
| * @param string $port to connect, defaults to php.ini setting | |
| * @param string $socket socket to use for connection, defaults to php.ini setting | |
| * @return void | |
| */ | |
| public function __construct($host=null, $username=null, $passwd=null, $dbname='', $port=null, $socket=null) { | |
| $this->connectionInfo_set($host, $username, $passwd, $dbname, $port, $socket); | |
| } | |
| /** | |
| * IaMysqli::__destruct() | |
| * Resests mysqli_report_mode to MYSQLI_REPORT_OFF | |
| * | |
| * @return void | |
| */ | |
| public function __destruct() { | |
| $driver = new mysqli_driver(); | |
| $driver->report_mode = MYSQLI_REPORT_OFF; | |
| try { | |
| if(!empty($this->mysqli)) { | |
| @$this->mysqli->close(); | |
| } | |
| } catch (Exception $ignore) { } | |
| } | |
| ////////////////////////////////////////////////// | |
| // CONNECTION CREDENTIALS | |
| ///////////////////////////////////////////////// | |
| /** | |
| * Adds connection information & credentials for database connection to the connection array. | |
| * | |
| * @param string $host host to connect, defaults to php.ini setting | |
| * @param string $username login, to connect, defaults to php.ini setting | |
| * @param string $passwd password, to connect, defaults to php.ini setting | |
| * @param string $dbname databasename | |
| * @param string $port to connect, defaults to php.ini setting | |
| * @param string $socket socket to use for connection, defaults to php.ini setting | |
| * @return void | |
| * | |
| */ | |
| public function connectionInfo_set($host=null, $username=null, $passwd=null, $dbname='', $port=null, $socket=null) { | |
| $this->connInfo[] = | |
| array( | |
| 'host' => $host === null ? ini_get("mysqli.default_host") : $host, | |
| 'username' => $username === null ? ini_get("mysqli.default_user") : $username, | |
| 'passwd' => $passwd === null ? ini_get("mysqli.default_pw") : $passwd, | |
| 'dbname' => $dbname, | |
| 'port' => $port === null ? ini_get("mysqli.default_port") : $port, | |
| 'socket' => $socket === null ? ini_get("mysqli.default_socket") : $socket | |
| ); | |
| } | |
| /** | |
| * A description of the connection. | |
| * | |
| * @param array $con connection info | |
| * @return string text display for connection info | |
| */ | |
| public function connect_toString($con=null) { | |
| if($con === null) | |
| $con = $this->connected_to; | |
| if(empty($con)) | |
| return "Not connected"; | |
| return "$con[host]:$con[port]/$con[socket] $con[dbname] as $con[username]"; | |
| } | |
| ////////////////////////////////////////////////// | |
| // CONNECT, RE-CONNECT & CLOSE | |
| ///////////////////////////////////////////////// | |
| /** | |
| * connect or reconnect to the db. | |
| * | |
| * @return bool true connected, false connection failed | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function connect() { | |
| //$this->log_trace("-- mysqli_init()"); | |
| $this->mysqli = \mysqli_init(); | |
| // @codeCoverageIgnoreStart | |
| if(!$this->mysqli) { | |
| $this->log_sql_error('-- mysqli init Error', 0, false); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException("mysqli_init() error",0,null,"-- error initializing mysqli"); | |
| } | |
| return false; | |
| } | |
| // @codeCoverageIgnoreEnd | |
| try { | |
| foreach($this->mysqli_options as $option) { | |
| //$this->log_trace("mysqli->option($option[0], $option[1])"); | |
| if(!$this->mysqli->options($option[0], $option[1]) ) { | |
| $this->log_sql_error("-- setting option: $option[0] = $option[1]", 0, false); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException("-- setting option: $option[0] = $option[1]",0,null,"-- setting option"); | |
| } | |
| return false; | |
| } | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| $this->log_sql_error("-- setting option: $option[0] = $option[1]", 0, false); | |
| throw new IacSqlException($mysqliException,0,null,"-- setting option"); | |
| } | |
| return $this->connect_real('Connect to DB'); | |
| //DUDA: set quality of service, ie consistency. http://php.net/manual/en/function.mysqlnd-ms-set-qos.php | |
| } | |
| /** | |
| * | |
| * @param string $msg | |
| * @return bool | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| * | |
| */ | |
| protected function connect_real($msg) { | |
| foreach($this->connInfo as $con) { | |
| $retries = 0; | |
| do { | |
| //$this->log_trace("$msg to ".$this->connect_toString($con), $retries); | |
| try { | |
| if($this->mysqli->real_connect($con['host'], $con['username'], $con['passwd'], $con['dbname'], $con['port'], $con['socket'])) { | |
| $this->connected_to = $con; | |
| $this->mysqli->set_charset($this->charset); | |
| $this->runSql("SET NAMES '$this->charset' COLLATE '$this->coalition'", $this->EXEC_QUERY); | |
| return true; | |
| } | |
| } catch(Exception $mysqliException) { | |
| // Do Nothing $throwMysqliException = new IacSqlException($mysqliException,0,null,"-- $msg"); | |
| } | |
| $this->log_sql_error("-- ".mysqli_connect_errno().': '.mysqli_connect_error()." $msg to ".$this->connect_toString($con), $retries, false); | |
| usleep($this->retry_usleep); // wait to reissue | |
| } while($retries++ < $this->retries); | |
| } | |
| $this->connected_to = array(); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException($this->mysqli->error, $this->mysqli->errno,null,"-- $msg"); | |
| } | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::reconnect() | |
| * | |
| * @return bool true on success, false on failure | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function reconnect() { | |
| // check if reconnection needed, ie max_allowed_packet could cause errno 2006 | |
| try { | |
| if($this->mysqli->ping()) | |
| return true; | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| // Do Nothing | |
| } | |
| return $this->connect_real('Reconnect'); | |
| } | |
| /** | |
| * IaMysqli::close() | |
| * close db connection, silently. | |
| * | |
| * @return bool true on success, false on failure | |
| */ | |
| public function close() { | |
| if(get_class($this->mysqli) === 'mysqli' && method_exists($this->mysqli, 'close')) { | |
| $this->log_trace("-- close db connection"); | |
| try { | |
| if($this->mysqli->close()) { | |
| $this->connected_to = array(); | |
| return true; | |
| } | |
| } | |
| catch(Exception $mysqliException) { | |
| // Do Nothing error is reported below | |
| } | |
| } | |
| $this->log_sql_error('-- mysqli->close()', 0, false); | |
| return false; | |
| } | |
| ////////////////////////////////////////////////// | |
| // TRANSACTIONS | |
| ///////////////////////////////////////////////// | |
| /** | |
| * start or begin a transaction. | |
| * | |
| * | |
| * @param int $flags MYSQLI_TRANS_START_READ_WRITE, MYSQLI_TRANS_START_READ_ONLY, MYSQLI_TRANS_START_WITH_CONSISTENT_SNAPSHOT | |
| * @return bool true ok, false error encountered | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function begin($flags = 0) { | |
| $started = $this->autocommit(false, " -- START TRANSACTION"); | |
| // php < 5.5 mysqli_query($db, "START TRANSACTION"); | |
| // mariadb $flags must be 0? | |
| $this->mysqli->begin_transaction($flags); | |
| if($started) { | |
| $this->begins++; | |
| return true; | |
| } | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::commit() | |
| * commit a transaction. | |
| * | |
| * @return bool true ok, false error encountered | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| * | |
| */ | |
| public function commit() { | |
| $this->log_trace("commit"); | |
| try { | |
| if($this->mysqli->commit()) { | |
| $this->begins--; | |
| $this->autocommitToDefault(); | |
| return true; | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| // Do Nothing | |
| } | |
| $this->begins--; | |
| $this->log_sql_error('-- commit'); | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| $this->autocommitToDefault(); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException($errmsg, $errno,null,"commit -- commit()"); | |
| } | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::rollback() | |
| * rollback a transaction. | |
| * | |
| * @return bool true ok, false error encountered | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function rollback() { | |
| $this->log_trace("rollback"); | |
| try { | |
| if($this->mysqli->rollback() ) { | |
| // rollback ok, reset counters and return autocommit | |
| $this->begins--; | |
| $this->autocommitToDefault(); | |
| return true; | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| // Do nothing $throwMysqliException = new IacSqlException($mysqliException,$this->mysqli->errno,null,"rollback"); | |
| } | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| $this->log_sql_error('-- rollback'); | |
| $this->autocommitToDefault(); | |
| $this->begins--; | |
| //Checar con error por disconnect? | |
| //Duda: log: CHECAR ER_WARNING_NOT_COMPLETE_ROLLBACK updating a nontransactional table within a transaction | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException($errmsg, $errno,null,"rollback -- rollback()"); | |
| } | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::autocommit() | |
| * set autocommit. | |
| * | |
| * @param bool $mode true set autocommit, false no autocommit | |
| * @param string $comment | |
| * @return int sucess | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function autocommit($mode, $comment = '') { | |
| $retries = 0; | |
| do { | |
| $this->log_trace("SET autocommit = ".($mode ? "1" : "0" )."$comment"); | |
| try { | |
| if($this->mysqli->autocommit($mode)) { | |
| return true; | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| // Do nothing | |
| } | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| $this->log_sql_error("SET autocommit = $mode$comment"); | |
| if( $this->begins === 0 && isset($this->reconnectOnErrors[$errno]) ) { | |
| $this->reconnect(); | |
| } else { | |
| usleep($this->retry_usleep); // wait to reissue | |
| } | |
| } while( $this->begins === 0 && $retries++ < $this->retries); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException($errmsg, $errno,null,"SET AUTOCOMMIT=1 -- autocommit()"); | |
| } | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::autocommitToDefault() | |
| * Sets autocommit to default value. | |
| * | |
| * @return bool true on success false on error | |
| * @throws IacSqlException | |
| */ | |
| protected function autocommitToDefault() { | |
| $retries = 0; | |
| do { | |
| try { | |
| if($this->mysqli->autocommit($this->autocommitDefault)) { | |
| return true; | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| ; // Do Nothing | |
| } | |
| $this->reconnect(); | |
| } while($retries++ < $this->retries); | |
| return false; | |
| } | |
| /** | |
| * IaMysqli::transaction() | |
| * Runs commands in an array as a transaction running first begin() and after the last command commit or rollback on Sql error | |
| * If servers sends a "rollbacked, resend transaction error" the entire transaction will be resent upto $this->retries times. | |
| * | |
| * @param array $sqlArray an array of Sql commands | |
| * if the index is a string from then on {{key}} will be replaced by that commands lastInsertId | |
| * @return bool true transaction succeeded, false failed | |
| * | |
| * | |
| * | |
| * @example | |
| * <code> | |
| * $Sql->transaction( array( | |
| * "UPDATE person SET title = 'Mr' WHERE id = 2", | |
| * "INSERT INTO person(name, relationship) VALUES('Susan', 'My wife')" | |
| * "UPDATE person SET spouse_id = 'ella' WHERE id=1", | |
| * )); | |
| * Will run within a transaction issuing a commit at the end or rollback on Sql error | |
| * If servers sends a "rollbacked, resend transaction error" the entire transaction will be resent upto $this->retries times | |
| * </code> | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function transaction($sqlArray) { | |
| // era params , $doSubstitute =false, $replacePrefixQuoted='{{', $replaceSuffixQuoted='}}', $replacePrefixUnQuoted='[[', $replaceSuffixUnQuoted=']]' | |
| $retries = 0; | |
| do { | |
| if(!$this->begin()) | |
| return false; | |
| try { | |
| if($this->queryArray($sqlArray)) { | |
| return $this->commit(); | |
| } | |
| } catch(mysqli_sql_exception $mysqliException) { | |
| // Do Nothing $throwMysqliException = new IacSqlException($mysqliException,0,null,'-- transaction'); | |
| } | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| if(!$this->rollback()) | |
| return false; | |
| } while($retries++ < $this->retries && isset($this->reconnectOnErrors[$errno]) ); | |
| if($this->throwSqlException_get()) { | |
| throw new IacSqlException($errmsg, $errno,null,'-- transaction'); | |
| } | |
| return false; | |
| } | |
| ////////////////////////////////////////////////// | |
| // Data modification & DDL Queries | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::queryArray() | |
| * Execues all queries in $sqlArray, substituing string keys for result if $doSubstitute=true. | |
| * | |
| * @param array $sqlArray an array of Sql commands | |
| * if the index is a string from then on {{key}} will be replaced by that commands lastInsertId, when $doSubstitute=true | |
| * for begin/commit use instead transaction() | |
| * @return bool true success, false failed | |
| * | |
| * @example $Sql->queryArray( array( | |
| * "UPDATE person SET title = 'Mr' WHERE id = 2", | |
| * 'SPOUSE' => "INSERT INTO person(name, relationship) VALUES('Susan', 'My wife')" | |
| * "UPDATE person SET spouse_id = 'gato' WHERE id=1", | |
| * )); | |
| * Will run all commands, stopping if an Sql error is encountered | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function queryArray($sqlArray) { | |
| foreach($sqlArray as $key=>$sql) { | |
| if(!($ret = $this->runSql($sql, $this->EXEC_QUERY)) ) { | |
| return false; | |
| } | |
| } | |
| return true; | |
| } | |
| /** | |
| * IaMysqli::query() | |
| * Executes a query like update/insert/delete. | |
| * | |
| * @param string $sql string: Sql command, mysqli_stmt binded statement | |
| * @param int $resultmode MYSQLI_STORE_RESULT [default], MYSQLI_USE_RESULT may use less memory | |
| * @return mixed bool|array, true success, false on error, associative array if Sql returns result object | |
| * | |
| * @example query("UPDATE table SET col1=1 WHERE table_id=1"); | |
| * returns true on success, false on Sql error | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function query($sql, $resultmode = MYSQLI_STORE_RESULT) { | |
| return $this->runSql($sql, $this->EXEC_QUERY, MYSQLI_ASSOC, array(), null, $resultmode); | |
| } | |
| /** | |
| * IaMysqli::insertAndGetId() | |
| * Executes a query (normally insert) if succesfull returns last inserted id, false on error. | |
| * | |
| * @param string $sql | |
| * @return int last inserted id or false on error | |
| * @throws IacSqlException | |
| */ | |
| public function insertAndGetId($sql) { | |
| if($this->runSql($sql, $this->EXEC_QUERY) ) | |
| return $this->mysqli->insert_id; | |
| return false; | |
| } | |
| ////////////////////////////////////////////////// | |
| // Retreive info, selects | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::single_read() | |
| * read first column of first returned row. | |
| * | |
| * @param string $sql Sql command | |
| * @param string $onNotFound on no rows found return $onNotFound defaults to '' | |
| * @return string first column of first row in select written in $Sql, on empty returns $onNotFound | |
| * | |
| * @exmpale single_read('SELECT name, last_name from person ORDER BY id') | |
| * returns 'Dana' | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function single_read($sql, $onNotFound='') { | |
| return $this->runSql($sql, $this->EXEC_SINGLE_READ, MYSQLI_NUM, $onNotFound); | |
| } | |
| /** | |
| * IaMysqli::singleton() | |
| * reads first row. | |
| * | |
| * @param string $sql Sql command | |
| * @param array $onNotFound on no rows found return $onNotFound defaults to array() | |
| * @param int $resultType MYSQLI_ASSOC [default], MYSQLI_NUM, or MYSQLI_BOTH | |
| * @return mixed first row of select command in $Sql, on empty returns $onNotFound, false on error | |
| * | |
| * @exmpale singleton('SELECT id, name, last_name from person ORDER BY id') | |
| * returns array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith') | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| * | |
| */ | |
| public function singleton($sql, $onNotFound = array(), $resultType = MYSQLI_ASSOC) { | |
| return $this->runSql($sql, $this->EXEC_SINGLETON, $resultType, $onNotFound); | |
| } | |
| /** | |
| * IaMysqli::singleton_full() | |
| * reads first row, on not found returns fields with '' as value. | |
| * | |
| * @param string $sql Sql command | |
| * @param mixed $onNull on Null fill value with | |
| * @return mixed first row of select command in $Sql, on empty returns $onNotFound, false on error | |
| * | |
| * @exmpale singleton_full('SELECT id, name, last_name from person ORDER BY id') | |
| * returns array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith') | |
| * | |
| * | |
| * @exmpale singleton_full('SELECT id, name, last_name from person WHERE id = -99.9 ORDER BY id -- non existant id') | |
| * returns array('id'=>'', 'name'=>'', 'last_name'=>'') | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function singleton_full($sql, $onNull='') { | |
| return $this->runSql($sql, $this->EXEC_SINGLETON_FULL, MYSQLI_ASSOC,array(),$onNull); | |
| } | |
| /** | |
| * IaMysqli::selectVector() | |
| * returns select as a vector. | |
| * | |
| * @param string $sql Sql command | |
| * @param mixed $onNotFound on no rows returned return $onNotFound | |
| * @return mixed array(col_1_row_1, col_1_row_2, ...) or false on error | |
| * | |
| * @exmpale selectVector('SELECT id, name, last_name from person ORDER BY id') | |
| * returns array( 1, 3 ) | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function selectVector($sql, $onNotFound=array()) { | |
| return $this->runSql($sql, $this->EXEC_VECTOR, MYSQLI_NUM, $onNotFound); | |
| } | |
| /** | |
| * IaMysqli::selectKeyValue() | |
| * returns select as key value array. | |
| * | |
| * @param string $sql Sql command | |
| * @param mixed $onNotFound on no rows returned return $onNotFound | |
| * @return array|bool array or false on error | |
| * | |
| * @exmpale selectArrayIndex('SELECT id, name, last_name from person ORDER BY id') | |
| * returns array( 1=>'Dana', 3=>'Paul' ) | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function selectKeyValue($sql, $onNotFound=array()) { | |
| return $this->runSql($sql, $this->EXEC_KEY_VALUE, MYSQLI_NUM, $onNotFound); | |
| } | |
| /** | |
| * IaMysqli::selectArrayKey() | |
| * returns select indexed by key. | |
| * | |
| * @param string $sql Sql command | |
| * @param string $key for associate array | |
| * @param mixed $onNotFound on no rows returned return $onNotFound | |
| * @param int $resultType | |
| * @return array associative array indexed by $key, each entry contains a selected row | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| * @exmpale selectArrayKey('SELECT id, name, last_name from person ORDER BY id', 'id') | |
| * returns array(1=>array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith'), | |
| * 3=>array('id'=>3, 'name'=>'Paul', 'last_name'=>'Jones') | |
| * ) | |
| * | |
| */ | |
| public function selectArrayKey($sql, $key = 'id', $onNotFound=array(), $resultType = MYSQLI_ASSOC) { | |
| return $this->runSql($sql, $this->EXEC_ARRAY_KEY, $resultType, $onNotFound, $key); | |
| } | |
| /** | |
| * IaMysqli::selectArrayIndex() | |
| * returns selecet as a numeric array. | |
| * | |
| * @param string $sql Sql command | |
| * @param mixed $onNotFound on no rows returned return $onNotFound | |
| * @param int $resultType MYSQLI_ASSOC [default], MYSQLI_NUM, or MYSQLI_BOTH | |
| * @return mixed false on error | |
| * | |
| * @exmpale selectArrayIndex('SELECT id, name, last_name from person ORDER BY id') | |
| * returns array(0=>array('id'=>1, 'name'=>'Dana', 'last_name'=>'Smith'), | |
| * 1=>array('id'=>3, 'name'=>'Paul'', 'last_name'=>'Jones') | |
| * ) | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function selectArrayIndex($sql, $onNotFound=array(), $resultType = MYSQLI_ASSOC) { | |
| return $this->runSql($sql, $this->EXEC_ARRAY_INDEX, $resultType, $onNotFound); | |
| } | |
| /** | |
| * @param $table | |
| * @param $UniquekeyValue | |
| * @param $primaryKeyValue | |
| * @param string $QueryComment | |
| * @return string | |
| * @throws IacSqlException | |
| */ | |
| public function valid_unique_key($table, $UniquekeyValue, $primaryKeyValue, $QueryComment = '/*valid_unique_key*/') { | |
| $where = array(); | |
| if(!empty($UniquekeyValue)) { | |
| foreach($UniquekeyValue as $IndexFieldName => $value) { | |
| $where[] = Str::fieldit($IndexFieldName).'='.Str::strit($value); | |
| } | |
| } | |
| if(!empty($primaryKeyValue)) { | |
| foreach($primaryKeyValue as $PrimaryKeyFieldName => $value) { | |
| $where[] = Str::fieldit($PrimaryKeyFieldName).'<>'.Str::strit($value); | |
| } | |
| } | |
| return $this->single_read('SELECT '.$QueryComment.' COUNT(*) FROM '.Str::fieldit($table).' WHERE '.implode(' AND ', $where)); | |
| } | |
| ////////////////////////////////////////////////// | |
| // INFORMATION ON QUERIES | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::last_insert_id() | |
| * returns last inserted id (auto increment value). | |
| * | |
| * @return int last inserted id | |
| */ | |
| public function last_insert_id() { | |
| return $this->mysqli->insert_id; | |
| } | |
| /** | |
| * IaMysqli::found_rows() | |
| * returns found rows for last select. | |
| * | |
| * @return int Last select found rows, if issueed with SQL_CALC_FOUND_ROWS | |
| * | |
| * @example | |
| * $db->singleton("SELECT SQL_CALC_FOUND_ROWS col1,col2 FROM table WHERE col1=1 LIMIT 1"); | |
| * $db->found_rows() => returns number of rows matching query, not considering limit clause | |
| * | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| public function found_rows() { | |
| return $this->runSql("SELECT FOUND_ROWS()", $this->EXEC_SINGLE_READ,MYSQLI_NUM,null); | |
| } | |
| /** | |
| * IaMysqli::metaData_get() | |
| * Get metadata on last select query | |
| * | |
| * @return array field info, metadata, of last select query | |
| * | |
| * | |
| */ | |
| public function metaData_get() { | |
| return $this->metaData; | |
| } | |
| /** | |
| * IaMysqli::metaData_clear() | |
| * Clears last metadata retreived | |
| * | |
| * @return void | |
| * | |
| */ | |
| public function metaData_clear() { | |
| $this->metaData = array(); | |
| } | |
| ////////////////////////////////////////////////// | |
| // SET EXCEPTIONS | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::throwSqlException_set() | |
| * | |
| * | |
| * @param bool|int $mysqli_report_mode true throws excpetions setting: MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT, , functions with errors trhow | |
| * false sets MYSQLI_REPORT_OFF, functions with errors return false | |
| * int sets $mysqli_report_mode | |
| * options: MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ALL | MYSQLI_REPORT_OFF | |
| * @return void | |
| */ | |
| public function throwSqlException_set($mysqli_report_mode) { | |
| $driver = new mysqli_driver(); | |
| if($mysqli_report_mode === false) | |
| $driver->report_mode = MYSQLI_REPORT_OFF; | |
| elseif($mysqli_report_mode === true) | |
| $driver->report_mode = MYSQLI_REPORT_STRICT | MYSQLI_REPORT_ERROR; | |
| else | |
| $driver->report_mode = $mysqli_report_mode; | |
| } | |
| /** | |
| * IaMysqli::throwSqlException_get() | |
| * Does mysqli throw exceptions for Sql errors | |
| * | |
| * @return bool true mysqli_driver sends exceptions for Sql errors, false no exceptions thrown | |
| */ | |
| public function throwSqlException_get() { | |
| $driver = new mysqli_driver(); | |
| return ($driver->report_mode & MYSQLI_REPORT_STRICT) === MYSQLI_REPORT_STRICT; | |
| } | |
| ////////////////////////////////////////////////// | |
| // LOG TRACE & ERRORS | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::trace_get() | |
| * Returns an array with Sql commands issued | |
| * | |
| * @return array log of Sql commands issued | |
| */ | |
| public function trace_get() { | |
| if($this->traceOn === false && empty($this->trace)) | |
| return array("Sql trace is off, turn it on with \$Sql->traceOn=true"); | |
| return $this->trace; | |
| } | |
| /** | |
| * IaMysqli::log_trace() | |
| * Stores issued Sql commands in | |
| * | |
| * @param string $sql command or message to store in trace | |
| * @param int $retries number of times this command has been re-sent to the server | |
| * @return void | |
| * @see IaMysqli::$traceOn IaMysqli::$traceOn | |
| */ | |
| public function log_trace($sql, $retries=0) { | |
| if(!$this->traceOn) | |
| return; | |
| if($retries === 0) | |
| $this->trace[] = $sql; | |
| else | |
| $this->trace[] = $sql." -- retry $retries"; | |
| } | |
| /** | |
| * IaMysqli::errorLog_get() | |
| * Returns an array with the Sql errors | |
| * | |
| * @return array Sql errors | |
| */ | |
| public function errorLog_get() { | |
| if($this->begins!==0) | |
| $this->log_sql_error("-- IaMysqli: ".$this->begins." transactions pending commit/rollback", 0, false); | |
| return $this->errorLog; | |
| } | |
| /** | |
| * IaMysqli::begins_get() | |
| * Return number of begin transactions waiting a commit or rollback statement | |
| * | |
| * @return int number of begin transactions waiting a commit or rollback statement | |
| */ | |
| public function begins_get() { | |
| return $this->begins; | |
| } | |
| /** | |
| * IaMysqli::log_sql_error() | |
| * Stores an error for later processing | |
| * | |
| * @param string $sql Sql command that caused the error | |
| * @param int $retries number of times this command has been re-sent to the server | |
| * @param bool $putMysqliError true include the error from mysqli, false the error is included in $Sql | |
| * @return void | |
| * @see IaMysqli::$errorLog IaMysqli::$errorLog | |
| */ | |
| public function log_sql_error($sql, $retries=0, $putMysqliError=true) { | |
| $retry = $retries === 0 ? '' : " -- retries: $retries"; | |
| if($putMysqliError === false) | |
| $this->errorLog[] = array('errno'=>0, 'error'=>'', 'Sql'=>$sql, 'retries'=>$retry); | |
| else | |
| $this->errorLog[] = array('errno'=>$this->mysqli->errno, 'error'=>$this->mysqli->error, 'Sql'=>$sql, 'retries'=>$retry); | |
| } | |
| ////////////////////////////////////////////////// | |
| // SEND SQL TO SERVER | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::runSql() | |
| * Executes an Sql statement or binded mysqlistmt | |
| * | |
| * @param mixed $sql string or prepared statment | |
| * @param int $exec constant $this->EXEC_ | |
| * @param int $resultType defaults to MYSQLI_ASSOC | MYSQLI_NUM | |
| * @param mixed $onNotFound on no rows returned return $onNotFound | |
| * @param string $key key to use in asociative arrows | |
| * @param int $resultmode defaults to MYSQLI_STORE_RESULT | |
| * @return mixed false on error, true ok no results, array/string database result | |
| * @throws IacSqlException if mysqli_report(MYSQLI_REPORT_STRICT) was set, else returns false on error | |
| */ | |
| protected function runSql($sql, $exec, $resultType = MYSQLI_ASSOC, $onNotFound = array(), $key = null, $resultmode = MYSQLI_STORE_RESULT) { | |
| // store statement for reporting it | |
| if(is_string($sql)) { | |
| $sqlStatement = $sql; | |
| } else { | |
| $sqlStatement = $this->preparedLast; | |
| } | |
| $retries = 0; | |
| do { | |
| try { | |
| $this->log_trace($sqlStatement, $retries); | |
| if(is_string($sql)) { | |
| /** @var mysqli_result|bool $result */ | |
| $result = $this->mysqli->query($sql, $resultmode); | |
| } else { | |
| if( ($result = $sql->execute() )) { // execute binded Sql statement | |
| /** @var mysqli_result|bool $result */ | |
| $result = $sql->get_result(); | |
| $sql->store_result(); | |
| } | |
| } | |
| if($result === null) { | |
| break; | |
| } | |
| if($result === true ) { // Data modification successfull | |
| $this->affected_rows = $this->mysqli->affected_rows; | |
| return true; | |
| } elseif($result !== false ) { // Data read successfull, return it in the desired format | |
| if($this->metaDataOn) { // save fieldinfo on metadata doit | |
| if(!isset($this->SqlInfo)) { | |
| $this->SqlInfo = new SqlInfo($this); | |
| } | |
| $this->metaData = $this->SqlInfo->result2fields($result); | |
| } | |
| switch ($exec) { | |
| case $this->EXEC_ARRAY_KEY: | |
| for($ret = array(); $tmp = $result->fetch_array($resultType);) { | |
| if(empty($key)) // $key not defined, use first item in tuple | |
| $key = key($tmp); | |
| $ret[$tmp[$key]] = $tmp; | |
| } | |
| break; | |
| case $this->EXEC_SINGLE_READ: | |
| $tmp = $result->fetch_row(); | |
| // no results return default value | |
| if($tmp === null) | |
| $ret = $onNotFound; | |
| else | |
| $ret = $tmp[0]; | |
| break; | |
| case $this->EXEC_SINGLETON: | |
| $ret = $result->fetch_array($resultType); | |
| break; | |
| case $this->EXEC_SINGLETON_FULL: | |
| $ret = $result->fetch_assoc(); | |
| if($ret === null) { | |
| $ret = array(); | |
| // no results return empty asociative array with selected fileds | |
| while ($finfo = $result->fetch_field()) { | |
| $ret[ $finfo->name ] = $key; | |
| } | |
| } | |
| break; | |
| case $this->EXEC_VECTOR: | |
| for($ret = array(); $tmp = $result->fetch_row();) | |
| $ret[] = $tmp[0]; | |
| break; | |
| case $this->EXEC_KEY_VALUE: | |
| $isKeyValue = null; | |
| for($ret = array(); $tmp = $result->fetch_row();) { | |
| if($isKeyValue === null) { | |
| $isKeyValue = count($tmp) > 1; | |
| } | |
| if($isKeyValue) { | |
| $ret[$tmp[0]] = $tmp[1]; | |
| } else { | |
| $ret[$tmp[0]] = $tmp[0]; | |
| } | |
| } | |
| break; | |
| default: // return array with received each row | |
| if (method_exists('/mysqli_result', 'fetch_all')) # Compatibility layer with PHP < 5.3 | |
| $ret = $result->fetch_all($resultType); | |
| else | |
| for ($ret = array(); $tmp = $result->fetch_array($resultType);) | |
| $ret[] = $tmp; | |
| } | |
| $this->num_rows = $result->num_rows; | |
| if(is_string($sql)) | |
| $result->free(); | |
| else | |
| $sql->free_result(); | |
| if($exec !== $this->EXEC_SINGLE_READ && empty($ret)) { | |
| $ret = $onNotFound; | |
| } | |
| return $ret; // returns the result in the requested format | |
| } | |
| } | |
| catch(mysqli_sql_exception $mysqliException) { | |
| // Do Nothing $throwMysqliException = new IacSqlException($mysqliException,0,null,$sqlStatement); | |
| } | |
| // log error and save error number | |
| $this->log_sql_error($sqlStatement, $retries); | |
| $errno = $this->mysqli->errno; | |
| // try to reconnect on connection lost and we are not inside a transaction | |
| if($this->begins === 0 && isset($this->reconnectOnErrors[$errno]) ) { | |
| $this->reconnect(); | |
| } else { | |
| usleep($this->retry_usleep); // wait to reissue | |
| } | |
| } while($this->begins === 0 && $retries++ < $this->retries && isset($this->retryOnErrors[$errno]) ); | |
| $this->num_rows = 0; | |
| if(!$this->throwSqlException_get()) | |
| return false; | |
| throw new IacSqlException($this->mysqli->error, $errno,null,$sqlStatement); | |
| } | |
| ////////////////////////////////////////////////// | |
| // Return multikeyed array | |
| ///////////////////////////////////////////////// | |
| /** | |
| * $arr[key_1][...]['key_'.$numKeys]=$col[$numKeys+1] or =[colName=>v,...] if total columns > $numKeys+1 | |
| * | |
| * @param string|array $sql | |
| * @param int $numKeys | |
| * @param int $resultType MYSQLI_ASSOC || MYSQLI_NUM | |
| * @return array |bool $arr[key_1][...]['key_'.$numKeys]=$col[$numKeys+1] or =[colName=>v,...] if total columns > $numKeys+1 | |
| * @throws IacSqlException | |
| */ | |
| public function selectArrayMultiKey($sql, $numKeys, $resultType = MYSQLI_ASSOC) { | |
| $ret = array(); | |
| if(!is_array($sql)) { | |
| $this->selectArrayMultiKeyDo($sql, $ret, $numKeys, $resultType); | |
| return $ret; | |
| } | |
| $ok = true; | |
| foreach($sql as $s) // on ok false it is an error! | |
| try { | |
| $ok &= $this->selectArrayMultiKeyDo($s, $ret, $numKeys, $resultType); | |
| } catch(ia\Sql\Mysql\IacSqlException $iacSqlException) { | |
| $ok = false; | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| $sqlStatement = $iacSqlException->getSqlStatement(); | |
| } | |
| if($ok) | |
| return $ret; | |
| if(!$this->throwSqlException_get()) | |
| return false; | |
| throw new IacSqlException($errmsg, $errno,null,$sqlStatement); | |
| } | |
| /** | |
| * Execute each query and merge or set multi key array $ret | |
| * | |
| * @param string|mysqli_statement $sql | |
| * @param array $ret | |
| * @param int $numKeys | |
| * @param int $resultType MYSQLI_ASSOC || MYSQLI_NUM | |
| * @return boolean true Ok, false Error | |
| * @throws IacSqlException | |
| */ | |
| protected function selectArrayMultiKeyDo($sql, &$ret, $numKeys, $resultType) { | |
| // store statement for reporting it | |
| if(is_string($sql)) { | |
| $sqlStatement = $sql; | |
| } else { | |
| $sqlStatement = $this->preparedLast; | |
| } | |
| $retries = 0; | |
| do { | |
| try { | |
| $this->log_trace($sqlStatement, $retries); | |
| if(is_string($sql)) { | |
| $result = $this->mysqli->query($sql, MYSQLI_STORE_RESULT); | |
| } else { | |
| if( ($result = $sql->execute() )) { | |
| $result = $sql->get_result(); | |
| $sql->store_result(); | |
| } | |
| } | |
| if($result !== false ) { // Data read successfull, | |
| for(; $tmp = $result->fetch_array($resultType);) { | |
| if(!isset($countFields)) { | |
| $countFields = count($tmp); | |
| } | |
| $arrRef = &$ret; | |
| $theKey = reset($tmp); | |
| for($k=0; $k<$numKeys; $k++) { | |
| if(!isset($arrRef[$theKey])) { | |
| $arrRef[$theKey] = array(); | |
| } | |
| $arrRef = &$arrRef[$theKey]; | |
| $theKey = next($tmp); | |
| } | |
| $arrRef = array_merge($arrRef, array_slice($tmp,$numKeys)); | |
| } | |
| $this->num_rows = $result->num_rows; | |
| if(is_string($sql)) { | |
| $result->free(); | |
| } else { | |
| $sql->free_result(); | |
| } | |
| return true; // returns the result in the requested format | |
| } | |
| } | |
| catch(mysqli_sql_exception $mysqliException) { | |
| // Do Nothing $throwMysqliException = new IacSqlException($mysqliException,0,null,$sqlStatement); | |
| } | |
| // log error and save error number | |
| $this->log_sql_error($sqlStatement, $retries); | |
| $errno = $this->mysqli->errno; | |
| $errmsg = $this->mysqli->error; | |
| // try to recconect on connection lost and we are not inside a transaction | |
| if($this->begins === 0 && isset($this->reconnectOnErrors[$errno]) ) { | |
| $this->reconnect(); | |
| } else { | |
| usleep($this->retry_usleep); // wait to reissue | |
| } | |
| } while($this->begins === 0 && $retries++ < $this->retries && isset($this->retryOnErrors[$errno]) ); | |
| $this->num_rows = 0; | |
| if(!$this->throwSqlException_get()) | |
| return false; | |
| throw new IacSqlException($errmsg, $errno,null,$sqlStatement); | |
| } | |
| ////////////////////////////////////////////////// | |
| // PREPARED STATEMENTS | |
| ///////////////////////////////////////////////// | |
| /** | |
| * IaMysqli::prepare() | |
| * | |
| * @param string $sql | |
| * @return bool false on failure, prepared mysqli_stmt on success | |
| */ | |
| public function prepare($sql) { | |
| $this->preparedLast = $sql; | |
| return $this->mysqli->prepare($sql); | |
| // $stmt = $this->mysqli->stmt_init(); | |
| // if($stmt->prepare($Sql)) | |
| // return $stmt; | |
| // return false; | |
| } | |
| } | |
| /** | |
| * IacSqlException | |
| * Exceptions thrown by IaMysqli | |
| * | |
| * | |
| */ | |
| class IacSqlException extends Exception | |
| { | |
| protected $sqlStatement; | |
| /** | |
| * IacSqlException::__construct() | |
| * | |
| * @param string $message error message or caught exception | |
| * @param integer $code error number | |
| * @param object $previous previous exceptin thrown | |
| * @param string $sqlStatement Sql statement that caused the error | |
| * @return void | |
| */ | |
| public function __construct($message = null, $code = 0, $previous = null, $sqlStatement='') { | |
| if(is_subclass_of($message, '\Exception')) { | |
| $msg = $message->getMessage(); | |
| $code = $message->GetCode(); | |
| } elseif(is_string($message)) | |
| $msg = $message; | |
| else | |
| $msg = 'Unknown '. get_class($this); | |
| if(!is_numeric($code)) { | |
| $msg .= " $code"; | |
| $code = 0; | |
| } | |
| parent::__construct($msg, $code, $previous); | |
| $this->sqlStatement = $sqlStatement; | |
| } | |
| /** | |
| * IacSqlException::getSqlStatement() | |
| * | |
| * @return string | |
| */ | |
| public function getSqlStatement() { | |
| return $this->sqlStatement; | |
| } | |
| } |