1<?php 2namespace DALMP; 3 4/** 5 * Database - Abstraction Layer for MySQL 6 * 7 * @author Nicolas Embriz <nbari@dalmp.com> 8 * @package DALMP 9 * @license BSD License 10 * @version 3.0.3 11 */ 12class Database 13{ 14 /** 15 * Contains database object 16 * 17 * @access protected 18 * @var object 19 */ 20 protected $DB; 21 22 /** 23 * Contains the database parameters DSN. 24 * 25 * @access protected 26 * @var array 27 */ 28 protected $dsn = array(); 29 30 /** 31 * query result 32 * 33 * @access protected 34 * @var mixed 35 */ 36 protected $_rs = null; 37 38 /** 39 * prepared statement object or false if an error occurred. 40 * 41 * @access protected 42 * @var mixed 43 */ 44 protected $_stmt = null; 45 46 /** 47 * cache DALMP\Cache instance 48 * 49 * @access private 50 * @var mixed 51 */ 52 public $cache = null; 53 54 /** 55 * If enabled, logs all queries and executions. 56 * 57 * @access private 58 * @var boolean 59 */ 60 private $debug = false; 61 62 /** 63 * Holds the fetchMode. 64 * 65 * @access private 66 * @var mixed 67 */ 68 private $fetchMode = MYSQLI_BOTH; 69 70 /** 71 * Holds the num of rows returned. 72 * 73 * @access private 74 * @var int 75 */ 76 private $numOfRows; 77 78 /** 79 * Holds the num of rows affected by INSERT, UPDATE, or DELETE query. 80 * 81 * @access private 82 * @var int 83 */ 84 private $numOfRowsAffected; 85 86 /** 87 * Holds the num of fields returned. 88 * 89 * @access private 90 * @var int 91 */ 92 private $numOfFields; 93 94 /** 95 * Contains the prepared statments parameters 96 * 97 * @access private 98 * @var array 99 */ 100 private $stmtParams = array(); 101 102 /** 103 * transaction status 104 * 105 * @access private 106 * @var array 107 */ 108 private $trans = array(); 109 110 /** 111 * Constructor 112 * 113 * @param DSN $dsn 114 * @param array $ssl 115 */ 116 public function __construct($dsn = null, $ssl = null) 117 { 118 if ($dsn) { 119 $dsn = parse_url($dsn); 120 $this->dsn['charset'] = isset($dsn['scheme']) ? (($dsn['scheme'] == 'mysql') ? null : $dsn['scheme']) : 'utf8'; 121 if (isset($dsn['host'])) { 122 $host = explode('=', $dsn['host']); 123 if ($host[0] == 'unix_socket') { 124 $this->dsn['host'] = null; 125 $this->dsn['socket'] = str_replace('\\', '/', $host[1]); 126 } else { 127 $this->dsn['host'] = rawurldecode($dsn['host']); 128 $this->dsn['socket'] = null; 129 } 130 } else { 131 $this->dsn['host'] = '127.0.0.1'; 132 } 133 $this->dsn['port'] = isset($dsn['port']) ? $dsn['port'] : 3306; 134 $this->dsn['user'] = isset($dsn['user']) ? rawurldecode($dsn['user']) : null; 135 $this->dsn['pass'] = isset($dsn['pass']) ? rawurldecode($dsn['pass']) : null; 136 $this->dsn['dbName'] = isset($dsn['path']) ? rawurldecode(substr($dsn['path'], 1)) : null; 137 $this->dsn['cache'] = isset($dsn['query']) ? $dsn['query'] : null; 138 $this->dsn['ssl'] = $ssl; 139 } else { 140 throw new \InvalidArgumentException('DSN missing: charset://username:password@host:port/database'); 141 } 142 } 143 144 /** 145 * Opens a connection to a mysql server 146 */ 147 public function connect() 148 { 149 if ($this->isConnected()) { 150 if ($this->debug) $this->debug->log(__METHOD__, 'still connected'); 151 return; 152 } 153 154 if (!extension_loaded('mysqli')) { 155 die('The Mysqli extension is required'); 156 } 157 158 $mysqli = mysqli_init(); 159 160 if (!$mysqli) { 161 die('mysqli_init failed'); 162 } 163 164 if (!$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, defined('DALMP_CONNECT_TIMEOUT') ? DALMP_CONNECT_TIMEOUT : 5)) { 165 throw new \Exception('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed'); 166 } 167 168 if (defined('DALMP_MYSQLI_INIT_COMMAND')) { 169 if (!$mysqli->options(MYSQLI_INIT_COMMAND, DALMP_MYSQLI_INIT_COMMAND)) { 170 throw new \Exception('Setting MYSQLI_INIT_COMMAND failed'); 171 } 172 } 173 174 if (is_array($this->dsn['ssl'])) { 175 if ($this->debug) $this->debug->log('DSN', 'SSL', $this->dsn['ssl']); 176 $mysqli->ssl_set($this->dsn['ssl']['key'], $this->dsn['ssl']['cert'], $this->dsn['ssl']['ca'], $this->dsn['ssl']['capath'], $this->dsn['ssl']['cipher']); 177 } 178 179 if ($this->debug) $this->debug->log(__METHOD__, 'connecting'); 180 181 $rs = @$mysqli->real_connect($this->dsn['host'], $this->dsn['user'], $this->dsn['pass'], $this->dsn['dbName'], $this->dsn['port'], $this->dsn['socket']); 182 if ($mysqli->connect_errno || $rs === false) { 183 if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', 'mysqli connection error'); 184 throw new \Exception(mysqli_connect_error(), mysqli_connect_errno()); 185 } 186 187 if ($this->dsn['charset']) { 188 if (!$mysqli->set_charset($this->dsn['charset'])) { 189 trigger_error("Error loading character set {$this->dsn['charset']}", E_USER_NOTICE); 190 } 191 } 192 193 $this->DB = $mysqli; 194 } 195 196 /** 197 * debuger 198 * 199 * @param boolean $log2file 200 * @param mixed $debugFile 201 */ 202 public function debug($log2file = false, $debugFile = false) 203 { 204 if ($log2file == 'off') { 205 if (is_object($this->debug)) { 206 $this->debug->getLog(); 207 $this->debug = false; 208 } 209 } else { 210 $debugFile = $debugFile ?: (defined('DALMP_DEBUG_FILE') ? DALMP_DEBUG_FILE : '/tmp/dalmp.log'); 211 $this->debug = new Logger($log2file, $debugFile); 212 $this->debug->log('DSN', $this->dsn); 213 if ($this->isConnected()) { 214 $this->debug->log('DALMP', mysqli_get_host_info($this->DB), 'protocol version: ' . mysqli_get_proto_info($this->DB), 'character set: ' . mysqli_character_set_name($this->DB)); 215 } 216 } 217 218 return; 219 } 220 221 /** 222 * isConnected 223 * 224 * @return boolean 225 */ 226 public function isConnected() 227 { 228 return $this->DB instanceof \mysqli; 229 } 230 231 /** 232 * Closes a previously opened database connection 233 */ 234 public function closeConnection() 235 { 236 if ($this->debug) $this->debug->log(__METHOD__); 237 $this->isConnected() && $this->DB->close(); 238 $this->DB = null; 239 } 240 241 /** 242 * Frees the memory associated with a result 243 */ 244 public function Close() 245 { 246 if ($this->debug) $this->debug->log(__METHOD__); 247 return ($this->isConnected()) && $this->_rs->close(); 248 } 249 250 /** 251 * Frees stored result memory for the given statement handle & 252 * Closes a prepared statement 253 */ 254 public function PClose() 255 { 256 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__); 257 if ($this->isConnected()) { 258 $this->_stmt->free_result(); 259 260 return $this->_stmt->close(); 261 } else { 262 return false; 263 } 264 } 265 266 /** 267 * getNumOfRows 268 * 269 * @return int num of rows 270 */ 271 public function getNumOfRows() 272 { 273 return $this->numOfRows; 274 } 275 276 /** 277 * getNumOfRowsAffected 278 * 279 * @return int num of rows affected 280 */ 281 public function getNumOfRowsAffected() 282 { 283 return $this->numOfRowsAffected; 284 } 285 286 /** 287 * getNumOfFields 288 * 289 * @return int num of fields 290 */ 291 public function getNumOfFields() 292 { 293 return $this->numOfFields; 294 } 295 296 /** 297 * Get the column names 298 * 299 * @param $table; 300 * @return array or false if no table set 301 */ 302 public function getColumnNames($table = null) 303 { 304 return ($table) ? $this->getCol("DESCRIBE $table") : false; 305 } 306 307 /** 308 * Sets the Fetch Mode 309 * 310 * @chainable 311 * @param ASSOC = MYSQLI_ASSOC, NUM = MYSQLI_NUM, null = MYSQLI_BOTH. 312 */ 313 public function FetchMode($mode = null) 314 { 315 switch (strtoupper($mode)) { 316 case 'NUM': 317 $this->fetchMode = MYSQLI_NUM; 318 break; 319 320 case 'ASSOC': 321 $this->fetchMode = MYSQLI_ASSOC; 322 break; 323 324 default : 325 $this->fetchMode = MYSQLI_BOTH; 326 } 327 328 if ($this->debug) $this->debug->log(__METHOD__, $mode, $this->fetchMode); 329 return $this; 330 } 331 332 /** 333 * Prepare arguments 334 * 335 * @param string $args 336 * @return array with arguments; 337 */ 338 public function Prepare() 339 { 340 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, func_get_args()); 341 342 switch (func_num_args()) { 343 case 1: 344 $param = func_get_arg(0); 345 $clean = true; 346 break; 347 348 case 2: 349 $key = func_get_arg(0); 350 $param = func_get_arg(1); 351 if (in_array($key, array('i', 'd', 's', 'b'), true)) { 352 $this->stmtParams[] = array($key => $param); 353 } else { 354 $clean = true; 355 } 356 break; 357 358 default : 359 return $this->stmtParams; 360 } 361 362 if (isset($clean)) { 363 if (is_numeric($param)) { 364 $param = !strcmp(intval($param), $param) ? (int) $param : (!strcmp(floatval($param), $param) ? (float) $param : $param); 365 } 366 $key = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ? 's' : 'b')); 367 368 return $this->stmtParams[] = array($key => $param); 369 } 370 } 371 372 /** 373 * Prepared Statements 374 * 375 * example: PGetAll('SELECT * FROM users WHERE name=? AND id=?', 'name', 1, 'db1') 376 * user also can define the corresponding type of the bind variables (i, d, s, b): http://www.php.net/manual/en/mysqli-stmt.bind-param.php 377 * example: PGetAll('SELECT * FROM table WHERE name=? AND id=?', array('s'=>'99.3', 7)); or use the Prepare() method 378 * 379 * @param SQL $sql 380 * @param string $params 381 */ 382 public function PExecute() 383 { 384 $args = func_get_args(); 385 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, $args); 386 387 !$this->isConnected() && $this->connect(); 388 389 $sql = array_shift($args); 390 $this->_stmt = $this->DB->prepare($sql); 391 392 if (!$this->_stmt) { 393 $this->closeConnection(); 394 trigger_error('ERROR -> ' . __METHOD__ . ": Please check your sql statement, unable to prepare: $sql with args: " . json_encode($args), E_USER_ERROR); 395 } 396 397 $params = array(); 398 $types = null; 399 400 $args = is_array(current($args)) ? current($args) : $args; 401 402 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, 'args:',$args); 403 404 if (!empty($args)) { 405 406 foreach ($args as $key => $param) { 407 $params[] = &$args[$key]; 408 409 if (!in_array($key, array('i', 'd', 's', 'b'), true)) { 410 411 if (is_numeric($param)) { 412 $param = !strcmp(intval($param), $param) ? (int) $param : (!strcmp(floatval($param), $param) ? (float) $param : $param); 413 } 414 415 if (is_null($param)) { 416 $key = 's'; 417 } else { 418 $key = is_int($param) ? 'i' : (is_float($param) ? 'd' : (is_string($param) ? 's' : 'b')); 419 } 420 } 421 422 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, "key: $key param: $param"); 423 $types .= $key; 424 } 425 426 array_unshift($params, $types); 427 428 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, "sql: $sql params:", $params); 429 430 call_user_func_array(array($this->_stmt, 'bind_param'), $params); 431 } 432 433 /** 434 * if you get erros like 'Illegal mix of collations 435 * (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)' 436 * try to set your table fiels to: "character set: UTF8" 437 * and "collation: utf8_unicode_ci" 438 */ 439 if ($this->_stmt->execute()) { 440 $this->_stmt->store_result(); 441 if (is_object($this->_stmt->result_metadata())) { 442 $this->numOfRows = $this->_stmt->num_rows; 443 $this->numOfFields = $this->_stmt->field_count; 444 if (!$this->_stmt->num_rows) { 445 return false; 446 } 447 } 448 449 $this->numOfRowsAffected = $this->_stmt->affected_rows; 450 451 /** 452 * An integer greater than zero indicates the number of rows affected 453 * or retrieved. Zero indicates that no records where updated for an 454 * UPDATE/DELETE statement, no rows matched the WHERE clause in the query 455 * or that no query has yet been executed. -1 indicates that the query has 456 * returned an error. NULL indicates an invalid argument was supplied to the 457 * function. 458 */ 459 if ($this->_stmt->affected_rows > 0) { 460 return true; 461 } elseif ($this->_stmt->affected_rows == -1) { 462 return false; 463 } else { 464 return $this->_stmt->affected_rows; 465 } 466 } else { 467 if (array_key_exists('error', $this->trans)) { 468 $this->trans['error']++; 469 } 470 471 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, 'ERROR', "sql: $sql params: ", $params, " Errorcode:" . $this->DB->errno); 472 473 throw new \ErrorException(__METHOD__ . 'ERROR -> ' . $this->DB->error . " - sql: $sql with params: " . json_encode($params)); 474 } 475 } 476 477 /** 478 * Prepared Statements query 479 * 480 * @param array $row 481 */ 482 public function Pquery(&$row) 483 { 484 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__); 485 $meta = $this->_stmt->result_metadata(); 486 $columns = array(); 487 488 while ($column = $meta->fetch_field()) { 489 $columns[] = &$row[$column->name]; 490 } 491 call_user_func_array(array($this->_stmt, 'bind_result'), $columns); 492 493 return $this->_stmt->fetch(); 494 } 495 496 /** 497 * _pFetch 498 * 499 * @access protected 500 * @return array 501 */ 502 protected function _pFetch($get = null) 503 { 504 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__, $get); 505 506 if (!$this->_stmt->num_rows) { 507 $this->PClose(); 508 509 return false; 510 } 511 512 $meta = $this->_stmt->result_metadata(); 513 $columns = array(); 514 $results = array(); 515 516 while (($column = $meta->fetch_field()) !== false) { 517 $columns[$column->name] = &$results[$column->name]; 518 } 519 520 call_user_func_array(array($this->_stmt, 'bind_result'), $columns); 521 522 $rs = array(); 523 524 switch ($get) { 525 case 'one': 526 while ($this->_stmt->fetch()) { 527 $rs = array_shift($columns); 528 break; 529 } 530 $rs = is_array($rs) ? reset($rs) : $rs; 531 break; 532 533 case 'col': 534 while ($this->_stmt->fetch()) { 535 $rs[] = reset($columns); 536 } 537 break; 538 539 case 'assoc': 540 if ($this->numOfFields < 2) { 541 if ($this->debug) { $this->debug->log('PreparedStatements', __METHOD__, 'ERROR', $get, 'num of columns < 2'); } 542 543 return false; 544 } 545 if ($this->numOfFields == 2) { 546 while ($this->_stmt->fetch()) { 547 $rs[reset($columns)] = next($columns); 548 } 549 } else { 550 while ($this->_stmt->fetch()) { 551 $rs[reset($columns)] = array_slice($columns, 1); 552 } 553 } 554 break; 555 556 default : 557 while ($this->_stmt->fetch()) { 558 $tmp = array(); 559 foreach ($columns as $key => $val) { 560 switch ($this->fetchMode) { 561 case MYSQLI_NUM: 562 $tmp[] = $val; 563 break; 564 565 case MYSQLI_ASSOC: 566 $tmp[$key] = $val; 567 break; 568 569 default : 570 $tmp[] = $val; 571 $tmp[$key] = $val; 572 break; 573 } 574 } 575 array_push($rs, $tmp); 576 if ($get == 'row') { 577 $rs = array_shift($rs); 578 break; 579 } 580 } 581 } 582 583 $this->PClose(); 584 585 return empty($rs) ? false : $rs; 586 } 587 588 /** 589 * _fetch 590 * 591 * @access protected 592 * @return array of strings that corresponds to the fetched row or false 593 */ 594 protected function _fetch() 595 { 596 if ($this->debug) $this->debug->log(__METHOD__); 597 return $this->_rs->fetch_array($this->fetchMode) ?: false; 598 } 599 600 /** 601 * Auto Execute 602 * 603 * @param string $table 604 * @param array $fields 605 * @param string $mode 606 * @param string $where 607 * @return true or false on error 608 */ 609 public function AutoExecute($table, array $fields, $mode = 'INSERT', $where = null) 610 { 611 if ($this->debug) $this->debug->log(__METHOD__, 'args:', $table, $fields, $mode, $where); 612 613 $mode = (strtoupper($mode) == 'INSERT') ? 'INSERT' : 'UPDATE'; 614 615 if ($mode == 'UPDATE' && !$where) { 616 if ($this->debug) $this->debug->log( __METHOD__, 'ERROR', 'WHERE clause missing'); 617 throw new \InvalidArgumentException(__METHOD__ . ' WHERE clause missing'); 618 } 619 620 if ($columnNames = $this->getColumnNames($table)) { 621 $data = array(); 622 $placeholder = ''; 623 foreach ($columnNames as $col) { 624 if (isset($fields[$col])) { 625 $data["`$col`"] = $fields[$col]; 626 $placeholder.= '?,'; 627 } 628 } 629 if (empty($data)) { 630 if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', "no matching fields on table: $table with fields:", $fields); 631 return false; 632 } 633 } else { 634 return false; 635 } 636 637 if ($mode == 'INSERT') { 638 $fields = implode(', ', array_keys($data)); 639 $placeholder = rtrim($placeholder, ','); 640 $query = array_values($data); 641 $sql = "INSERT INTO $table ($fields) VALUES($placeholder)"; 642 } else { 643 $fields = implode('=?, ', array_keys($data)); 644 $fields.= '=?'; 645 $query = array_values($data); 646 $sql = "UPDATE $table SET $fields WHERE $where"; 647 } 648 649 return call_user_func_array(array($this, 'PExecute'), array($sql, $query)); 650 } 651 652 /** 653 * multiple insert 654 * 655 * @param string $table 656 * @param array $col_name example array('col1', 'col2') 657 * @param array $multiple_values example array(array('val1', 'val2')) 658 * @return boolean 659 */ 660 public function multipleInsert($table, array $col_name, array $multiple_values) 661 { 662 $num_of_fields = count($col_name); 663 if ($num_of_fields != count(end($multiple_values))) { 664 throw new \InvalidArgumentException('number of values do not match number of columns'); 665 } 666 667 $pvalues = ''; 668 $values = array(); 669 670 foreach ($multiple_values as $value) { 671 $placeholder ='('; 672 for ($i = 0; $i < $num_of_fields; $i++) { 673 $values[] = isset($value[$i]) ? $value[$i] : null; 674 $placeholder .= '?,'; 675 } 676 $pvalues .= rtrim($placeholder, ',') . '),'; 677 $placeholder = null; 678 } 679 680 $pvalues = rtrim($pvalues,','); 681 682 $columns = array_map(create_function('$n', 'return "`$n`";'), $col_name); 683 684 $sql = "INSERT INTO $table (" . implode(',', $columns) . ") VALUES $pvalues"; 685 686 if ($this->debug) $this->debug->log(__METHOD__, $sql, $values); 687 return call_user_func_array(array($this, 'PExecute'), array($sql, $values)); 688 } 689 690 /** 691 * Execute SQL statement 692 * 693 * @param strign $sql 694 * @return true or false if there was an error in executing the sql. 695 */ 696 public function Execute($sql) 697 { 698 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 699 700 !$this->isConnected() && $this->connect(); 701 702 if ($rs = $this->DB->query($sql)) { 703 if (is_object($rs)) { 704 $this->_rs = $rs; 705 $this->numOfRows = $this->_rs->num_rows; 706 $this->numOfFields = $this->_rs->field_count; 707 if ($this->debug) $this->debug->log(__METHOD__, 'returned object', "#rows: $this->numOfRows #fields: $this->numOfFields"); 708 if (!$this->numOfRows) { 709 $this->Close(); 710 711 return false; 712 } 713 } 714 715 $this->numOfRowsAffected = $this->DB->affected_rows; 716 717 /** 718 * An integer greater than zero indicates the number of rows affected or 719 * retrieved. Zero indicates that no records were updated for an UPDATE 720 * statement, no rows matched the WHERE clause in the query or that no query 721 * has yet been executed. -1 indicates that the query returned an error. 722 */ 723 if ($this->DB->affected_rows > 0) { 724 return true; 725 } elseif ($this->DB->affected_rows == -1) { 726 return false; 727 } else { 728 return $this->DB->affected_rows; 729 } 730 } else { 731 if (array_key_exists('error', $this->trans)) { 732 $this->trans['error']++; 733 } 734 if ($this->debug) $this->debug->log(__METHOD__, 'ERROR', "sql: $sql Errorcode: " . $this->DB->errno); 735 throw new \ErrorException(__METHOD__ . ' ERROR -> ' . $this->DB->error . " - sql: $sql"); 736 } 737 } 738 739 /** 740 * Query 741 * 742 * @see _fetch 743 * @return array or null 744 */ 745 public function query() 746 { 747 if ($this->debug) $this->debug->log(__METHOD__); 748 return $this->_fetch(); 749 } 750 751 /** 752 * Export to CSV 753 * 754 * @param string $sql 755 * @return csv 756 */ 757 public function csv() 758 { 759 $args = func_get_args(); 760 if ($this->debug) $this->debug->log(__METHOD__, $args); 761 762 switch (func_num_args()) { 763 case 1: 764 if (call_user_func_array(array($this, 'Execute'), $args)) { 765 $row = $this->_rs->fetch_array(MYSQLI_ASSOC); 766 $fp = fopen('php://output', 'w'); 767 fputcsv($fp, array_keys($row)); 768 $this->_rs->data_seek(0); 769 while ($row = $this->_rs->fetch_array(MYSQLI_NUM)) { 770 fputcsv($fp, $row); 771 } 772 $this->Close(); 773 fclose($fp); 774 } 775 break; 776 777 default: 778 if (call_user_func_array(array($this, 'PExecute'), $args)) { 779 $meta = $this->_stmt->result_metadata(); 780 $columns = array(); 781 $results = array(); 782 while (($column = $meta->fetch_field()) !== false) { 783 $columns[$column->name] = &$results[$column->name]; 784 } 785 $fp = fopen('php://output', 'w'); 786 fputcsv($fp, array_keys($columns)); 787 call_user_func_array(array($this->_stmt, 'bind_result'), $columns); 788 while ($this->_stmt->fetch()) { 789 fputcsv($fp, $columns); 790 } 791 $this->PClose(); 792 fclose($fp); 793 } 794 } 795 } 796 797 /** 798 * maps the result to an object 799 * 800 * @param sting sql the query string 801 * @param string class_name of the class to instantiate 802 * @param array optional array of parameters to pass to the constructor for class_name objects. 803 * @see mysqli_result::fetch_object 804 * @return object or false 805 */ 806 public function map($sql, $class_name=null, $params=array()) 807 { 808 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 809 if ($this->Execute($sql)) { 810 return ($class_name) ? $this->_rs->fetch_object($class_name, $params) : $this->_rs->fetch_object(); 811 } else { 812 return false; 813 } 814 } 815 816 /** 817 * Fetch a result row as an associative, a numeric array, or both 818 * 819 * @param SQL $sql 820 * @return array or false 821 */ 822 public function getAll($sql) 823 { 824 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 825 if ($this->Execute($sql)) { 826 $rows = array(); 827 while ($row = $this->_fetch()) { 828 $rows[] = $row; 829 } 830 $this->Close(); 831 832 return $rows; 833 } else { 834 return false; 835 } 836 } 837 838 /** 839 * Get a Row 840 * 841 * @param SQL $sql 842 * @return the first row as an array or false. 843 */ 844 public function getRow($sql) 845 { 846 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 847 if ($this->Execute($sql)) { 848 $row = $this->_fetch(); 849 $this->Close(); 850 851 return $row; 852 } else { 853 return false; 854 } 855 } 856 857 /** 858 * Get a Column 859 * 860 * @param SQL $sql 861 * @return the first column as an array, or false. 862 */ 863 public function getCol($sql) 864 { 865 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 866 if ($this->Execute($sql)) { 867 $col = array(); 868 while ($row = $this->_rs->fetch_row()) { 869 $col[] = reset($row); 870 } 871 $this->Close(); 872 873 return $col; 874 } else { 875 return false; 876 } 877 } 878 879 /** 880 * Get One 881 * 882 * @param SQL $sql 883 * @return the first field of the first row, or false. 884 */ 885 public function getOne($sql) 886 { 887 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 888 if ($this->Execute($sql)) { 889 $row = $this->_rs->fetch_row(); 890 $this->Close(); 891 892 return reset($row); 893 } else { 894 return false; 895 } 896 } 897 898 /** 899 * Get an associative array using the first column as keys 900 * 901 * @param SQL $sql 902 * @return associative array, false if columns < 2, or no records found. 903 */ 904 public function getASSOC($sql) 905 { 906 if ($this->debug) $this->debug->log(__METHOD__, "sql: $sql"); 907 if ($this->Execute($sql)) { 908 $cols = $this->numOfFields; 909 if ($cols < 2) { 910 return false; 911 } 912 $this->fetchMode = MYSQLI_ASSOC; 913 $assoc = array(); 914 if ($cols == 2) { 915 while ($row = $this->_fetch()) { 916 $assoc[reset($row)] = next($row); 917 } 918 } else { 919 while ($row = $this->_fetch()) { 920 $assoc[reset($row)] = array_slice($row, 1); 921 } 922 } 923 $this->Close(); 924 925 return $assoc; 926 } else { 927 return false; 928 } 929 } 930 931 /** 932 * Start the transaction 933 */ 934 public function StartTrans() 935 { 936 if (empty($this->trans)) { 937 if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans); 938 $this->trans = array('level' => 0, 'error' => 0); 939 940 return $this->Execute('BEGIN'); 941 } else { 942 $this->trans['level']++; 943 if ($this->debug) $this->debug->log('transactions', __METHOD__, array('transaction level' => $this->trans['level'])); 944 return $this->Execute(sprintf('SAVEPOINT level%d', $this->trans['level'])); 945 } 946 } 947 948 /** 949 * Complete the transaction 950 * 951 * @return boolean 952 */ 953 public function CompleteTrans() 954 { 955 if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans); 956 if (empty($this->trans)) { 957 return false; 958 } else { 959 if ($this->trans['error'] > 0) { 960 if ($this->debug) $this->debug->log('transactions', __METHOD__, 'ERROR', array('error in level' => $this->trans['level'])); 961 if ($this->trans['level'] > 0) { 962 $this->Execute(sprintf('ROLLBACK TO SAVEPOINT level%d', $this->trans['level'])); 963 $this->trans['level']--; 964 } else { 965 $this->Execute('ROLLBACK'); 966 } 967 968 return false; 969 } 970 if ($this->trans['level'] == 0) { 971 $this->trans = array(); 972 973 return ($this->Execute('COMMIT') === false) ? false : true; 974 } else { 975 $rs = $this->Execute(sprintf('RELEASE SAVEPOINT level%d', $this->trans['level'])); 976 $this->trans['level']--; 977 978 return ($rs === false) ? false : true; 979 } 980 } 981 } 982 983 /** 984 * Rollback the transaction 985 * 986 * @return false if there was an error executing the ROLLBACK. 987 */ 988 public function RollBackTrans() 989 { 990 if ($this->debug) $this->debug->log('transactions', __METHOD__, $this->trans); 991 if (isset($this->trans['level']) && $this->trans['level'] > 0) { 992 $rs = $this->Execute(sprintf('ROLLBACK TO SAVEPOINT level%d', $this->trans['level'])); 993 $this->trans['level']--; 994 995 return $rs; 996 } else { 997 return $this->Execute('ROLLBACK'); 998 } 999 } 1000 1001 /** 1002 * Insert_Id 1003 * 1004 * @return int the auto generated id used in the last query 1005 */ 1006 public function Insert_Id() 1007 { 1008 if ($this->debug) $this->debug->log(__METHOD__); 1009 return $this->DB->insert_id; 1010 } 1011 1012 /** 1013 * ErrorMsg 1014 * 1015 * @return string description of the last error 1016 */ 1017 public function ErrorMsg() 1018 { 1019 return $this->DB->error; 1020 } 1021 1022 /** 1023 * ErrorNum 1024 * 1025 * @return int error code 1026 */ 1027 public function ErrorNum() 1028 { 1029 return $this->DB->errno; 1030 } 1031 1032 /** 1033 * Quotes a string 1034 * 1035 * @param string $value 1036 */ 1037 public function qstr($value) 1038 { 1039 if ($this->debug) $this->debug->log(__METHOD__, func_get_args()); 1040 if (is_int($value) || is_float($value)) { 1041 $rs = $value; 1042 } else { 1043 !$this->isConnected() && $this->connect(); 1044 $rs = $this->DB->real_escape_string($value); 1045 } 1046 if ($this->debug) $this->debug->log(__METHOD__, "returned: $rs"); 1047 return $rs; 1048 } 1049 1050 /** 1051 * renumber 1052 * 1053 * @param string $table 1054 * @param int $col 1055 * @return boolean 1056 */ 1057 public function renumber($table, $col = 'id') 1058 { 1059 if (isset($table)) { 1060 return $this->Execute('SET @var_dalmp=0') ? ($this->Execute("UPDATE $table SET $col = (@var_dalmp := @var_dalmp +1)") ? $this->Execute("ALTER TABLE $table AUTO_INCREMENT = 1") : false) : false; 1061 } else { 1062 return false; 1063 } 1064 } 1065 1066 /** 1067 * forceTruncate - force truncate of a table 1068 * 1069 * @param string $table 1070 * @return boolean 1071 */ 1072 public function forceTruncate($table) 1073 { 1074 return (false !== $this->Execute('SET FOREIGN_KEY_CHECKS = 0') && false !== $this->Execute(sprintf('TRUNCATE %s', $table))); 1075 } 1076 1077 /** 1078 * useCache 1079 * 1080 * @param DALMP\Cache $cache 1081 */ 1082 public function useCache(Cache $cache) 1083 { 1084 if ($this->debug) $this->debug->log(__METHOD__, $cache); 1085 $this->cache = $cache; 1086 } 1087 1088 /** 1089 * DALMP\Cache fabric method 1090 * 1091 * @return DAMLP\Cache instance 1092 */ 1093 public function Cache() 1094 { 1095 if ($this->debug) $this->debug->log(__METHOD__, 'Creating cache using DSN'); 1096 if ($this->cache instanceof Cache) { 1097 return $this->cache; 1098 } else { 1099 list($type, $host, $port, $compress) = @explode(':', $this->dsn['cache']) + array(null, null, null, null); 1100 $type = strtolower($type); 1101 switch ($type) { 1102 case 'memcache': 1103 $cache = new Cache\Memcache($host, $port, 1, $compress); 1104 break; 1105 1106 case 'redis': 1107 $cache = new Cache\Redis($host, $port, $compress); 1108 break; 1109 1110 case 'disk': 1111 default: 1112 $cache = new Cache\Disk($host); 1113 break; 1114 } 1115 $this->cache = new Cache($cache); 1116 } 1117 1118 return $this->cache; 1119 } 1120 1121 /** 1122 * general method for caching 1123 * 1124 * @param string $fetch_method 1125 * @param int $expire 1126 * @param string $sql 1127 * @param string $key 1128 * @param string $group 1129 * @return boolean; 1130 */ 1131 protected function _Cache() 1132 { 1133 $args = func_get_args(); 1134 1135 if ($this->debug) $this->debug->log(__METHOD__, 'Args', $args); 1136 1137 $fetch = array_shift($args); 1138 $expire = (int) (reset($args)) ? array_shift($args) : 3600; 1139 $sql = array_shift($args); 1140 $key = isset($args[0]) ? $args[0] : $fetch; 1141 1142 if (strncmp($key, 'group:', 6) == 0) { 1143 $group = $key; 1144 $key = $fetch; 1145 } else { 1146 $group = (isset($args[1]) and (strncmp($args[1], 'group:', 6) == 0)) ? $args[1] : null; 1147 } 1148 1149 $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP'; 1150 $hkey = sha1($skey . $sql . $key); 1151 1152 if ($this->debug) $this->debug->log(__METHOD__, 'Parsed Args', array('fetch method' => $fetch, 'expire' => $expire, 'sql' => $sql, 'key' => $key, 'group' => $group), array('Cache key' => $hkey)); 1153 1154 is_null($this->cache) && $this->Cache(); 1155 1156 if ($this->cache instanceof Cache && $cache = $this->cache->get($hkey)) { 1157 if ($this->debug) $this->debug->log(__METHOD__, 'serving from cache'); 1158 return $cache; 1159 } else { 1160 switch ($fetch) { 1161 case 'all': 1162 $cache = $this->getAll($sql); 1163 break; 1164 case 'row': 1165 $cache = $this->getRow($sql); 1166 break; 1167 case 'col': 1168 $cache = $this->getCol($sql); 1169 break; 1170 case 'one': 1171 $cache = $this->getOne($sql); 1172 break; 1173 case 'assoc': 1174 $cache = $this->getASSOC($sql); 1175 break; 1176 } 1177 1178 if ($this->cache instanceof Cache) { 1179 $this->_setCache($hkey, $cache, $expire, $group); 1180 } else { 1181 trigger_error('Cache instance not defined, use the method useCache($cache) to set a cache engine.', E_USER_WARNING); 1182 } 1183 1184 if ($this->debug) $this->debug->log(__METHOD__, 'Set', array('key' => $hkey, 'expire' => $expire, 'group' => $group)); 1185 return $cache; 1186 } 1187 } 1188 1189 /** 1190 * method for caching prepared statements 1191 * 1192 * @param string $fetch_method 1193 * @param int $expire 1194 * @param string $sql 1195 * @param string $key 1196 * @param string $group 1197 * @return boolean; 1198 */ 1199 protected function _CacheP() 1200 { 1201 $args = func_get_args(); 1202 1203 if ($this->debug) $this->debug->log(__METHOD__, 'Args', $args); 1204 1205 $fetch = array_shift($args); 1206 $expire = (int) (reset($args)) ? array_shift($args) : 3600; 1207 $sql = array_shift($args); 1208 1209 // expected params 1210 $eparams = count(explode('?', $sql, -1)); 1211 $targs = count($args); 1212 $args = is_array(current($args)) ? current($args) : $args; 1213 if ($targs > $eparams) { 1214 if (($targs - $eparams) == 1) { 1215 $key = array_pop($args); 1216 $params = $args; 1217 if (strncmp($key, 'group:', 6) == 0) { 1218 $group = $key; 1219 $key = $fetch . implode('|', array_merge(array_keys($args), $args)); 1220 } else { 1221 $group = null; // only key no group 1222 } 1223 } else { 1224 $group = array_pop($args); 1225 $group = (strncmp($group, 'group:', 6) == 0) ? $group : null; 1226 $key = array_pop($args); 1227 $params = $args; 1228 } 1229 } else { 1230 $key = $fetch . implode('|', array_merge(array_keys($args), $args)); 1231 $params = $args; 1232 $group = null; 1233 } 1234 1235 array_unshift($args, $sql); 1236 1237 $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP'; 1238 $hkey = sha1($skey . $sql . $key); 1239 1240 if ($this->debug) $this->debug->log(__METHOD__, 'Parsed Args', array('fetch method' => $fetch, 'expire' => $expire, 'sql' => $sql, 'key' => $key, 'group' => $group), array('Cache key' => $hkey)); 1241 1242 is_null($this->cache) && $this->Cache(); 1243 1244 if ($this->cache instanceof Cache && $cache = $this->cache->Get($hkey)) { 1245 if ($this->debug) $this->debug->log(__METHOD__, 'serving from cache'); 1246 return $cache; 1247 } else { 1248 $nargs = array(); 1249 foreach (array_keys($args) as $akey) { 1250 if (!is_int($akey)) { 1251 $nargs['dalmp'][$akey] = $args[$akey]; 1252 } else { 1253 $nargs[] = $args[$akey]; 1254 } 1255 } 1256 call_user_func_array(array($this, 'PExecute'), $nargs); 1257 $cache = $this->_pFetch($fetch); 1258 1259 if ($this->cache instanceof Cache) { 1260 $this->_setCache($hkey, $cache, $expire, $group); 1261 } else { 1262 trigger_error('Cache instance not defined, use the method useCache($cache) to set a cache engine.', E_USER_WARNING); 1263 } 1264 1265 if ($this->debug) $this->debug->log(__METHOD__, 'Set', array('key' => $hkey, 'expire' => $expire, 'group' => $group)); 1266 return $cache; 1267 } 1268 } 1269 1270 /** 1271 * _setCache - store data in cache 1272 * 1273 * @access protected 1274 * @param string $hkey The key that will be associated with the item. 1275 * @param data $cache The variable to store 1276 * @param int $expire Expiration time of the item 1277 * @param string $group group:name (to group cache keys) usefull when flushing the cache 1278 * @return boolean 1279 */ 1280 protected function _setCache($hkey, $cache, $expire = 3600, $group = null) 1281 { 1282 is_null($this->cache) && $this->Cache(); 1283 1284 if ($group) { 1285 $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP'; 1286 $gkey = sha1($skey . $group); 1287 1288 if ($gCache = $this->cache->Get($gkey)) { 1289 foreach ($gCache as $key => $exp) { 1290 if ($exp < time()) { 1291 unset($gCache[$key]); 1292 } 1293 } 1294 } else { 1295 $gCache = array(); 1296 } 1297 1298 $gCache[$hkey] = time() + $expire; 1299 1300 if (!($this->cache->Set($hkey, $cache, $expire) && $this->cache->Set($gkey, $gCache, 0))) { 1301 throw new \UnexpectedValueException('Can not store data on cache'); 1302 } 1303 } else { 1304 if (!$this->cache->Set($hkey, $cache, $expire)) { 1305 throw new \UnexpectedValueException('Can not store data on cache'); 1306 } 1307 } 1308 1309 return true; 1310 } 1311 1312 /** 1313 * Cache flush 1314 * 1315 * @param string $sql, SQL, cache group or null 1316 * @param string $key 1317 * @return boolean 1318 */ 1319 public function CacheFlush($sql = null, $key = null) 1320 { 1321 is_null($this->cache) && $this->Cache(); 1322 1323 if (is_null($sql)) { 1324 if ($this->debug) $this->debug->log(__METHOD__, 'Flushing all cache'); 1325 return $this->cache->Flush(); 1326 } 1327 1328 $skey = defined('DALMP_SITE_KEY') ? DALMP_SITE_KEY : 'DALMP'; 1329 $hkey = sha1($skey . $sql . $key); 1330 1331 if (strncmp($sql, 'group:', 6) == 0) { 1332 $gkey = sha1($skey . $sql); 1333 if ($group = $this->cache->get($gkey)) { 1334 if ($this->debug) $this->debug->log(__METHOD__, 'group', array('group' => $sql, 'Cache group key' => $gkey)); 1335 foreach ($group as $key => $timeout) { 1336 $this->cache->Delete($key); 1337 } 1338 } 1339 } 1340 1341 if ($this->debug) $this->debug->log(__METHOD__, 'Delete', array('sql' => $sql, 'key' => $hkey)); 1342 return $this->cache->Delete($hkey); 1343 } 1344 1345 /** 1346 * @return string server Version 1347 */ 1348 public function getServerVersion() 1349 { 1350 $version = $this->DB->server_version; 1351 $major = (int) ($version / 10000); 1352 $minor = (int) ($version % 10000 / 100); 1353 $revision = (int) ($version % 100); 1354 1355 return $major . '.' . $minor . '.' . $revision; 1356 } 1357 1358 /** 1359 * @return string Client Version 1360 */ 1361 public function getClientVersion() 1362 { 1363 $version = $this->DB->client_version; 1364 $major = (int) ($version / 10000); 1365 $minor = (int) ($version % 10000 / 100); 1366 $revision = (int) ($version % 100); 1367 1368 return $major . '.' . $minor . '.' . $revision; 1369 } 1370 1371 /** 1372 * Universally Unique Identifier v4 1373 * 1374 * @param int $b 1375 * @return UUID, if $b returns binary(16) 1376 */ 1377 public function UUID($b=null) 1378 { 1379 if ($this->debug) $this->debug->log(__METHOD__); 1380 if (function_exists('uuid_create')) { 1381 $uuid = uuid_create(); 1382 } else { 1383 $uuid = sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x', 1384 mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff), 1385 mt_rand(0, 0x0fff) | 0x4000, 1386 mt_rand(0, 0x3fff) | 0x8000, 1387 mt_rand(0, 0xffff), mt_rand(0, 0xffff), mt_rand(0, 0xffff) 1388 ); 1389 } 1390 1391 return $b ? pack('H*', str_replace('-', '', $uuid)) : $uuid; 1392 } 1393 1394 /** 1395 * X execute/call custom methods 1396 * 1397 * @return mysqli object 1398 */ 1399 public function X() 1400 { 1401 if ($this->debug) $this->debug->log(__METHOD__); 1402 !$this->isConnected() && $this->connect(); 1403 1404 return $this->DB; 1405 } 1406 1407 /** 1408 * magic method for Pget, Cacheget, and CachePge(all, row, col, one, assoc) 1409 */ 1410 public function __call($name, $args) 1411 { 1412 $n = strtolower($name); 1413 1414 $method = function ($subject) { 1415 ($m = preg_match('/^(pget|cacheget|cachepget)/i', $subject, $matches)) && $m = $matches[0]; 1416 1417 return $m; 1418 }; 1419 1420 $get = function ($m) use ($n) { 1421 $method = explode($m, $n) + array(null, null); 1422 1423 return in_array($method[1], array('all', 'row', 'col', 'one', 'assoc')) ? $method[1] : false; 1424 }; 1425 1426 switch ($method($n)) { 1427 case 'pget': 1428 if ($func = $get('pget')) { 1429 if ($this->debug) $this->debug->log('PreparedStatements', __METHOD__); 1430 return call_user_func_array(array($this, 'PExecute'), $args) ? $this->_pFetch($func) : false; 1431 } 1432 break; 1433 1434 case 'cacheget': 1435 if ($func = $get('cacheget')) { 1436 if ($this->debug) $this->debug->log('Cache', __METHOD__); 1437 array_unshift($args, $func); 1438 1439 return call_user_func_array(array($this, '_Cache'), $args); 1440 } 1441 break; 1442 1443 case 'cachepget': 1444 if ($func = $get('cachepget')) { 1445 if ($this->debug) $this->debug->log('CacheP', __METHOD__); 1446 array_unshift($args, $func); 1447 1448 return call_user_func_array(array($this, '_CacheP'), $args); 1449 } 1450 break; 1451 } 1452 throw new \Exception("DALMP DB method ({$name}) does not exist", 0); 1453 } 1454 1455 /** 1456 * usage: echo $db; 1457 * 1458 * @return database stats 1459 */ 1460 public function __toString() 1461 { 1462 if ($this->isConnected()) { 1463 $status = 'DALMP :: '; 1464 $status .= 'Character set: ' . $this->DB->character_set_name(); 1465 $status .= ', ' . $this->DB->host_info; 1466 $status .= ', Server version: ' . $this->getServerVersion(); 1467 $status .= ', Client version: ' . $this->getClientVersion(); 1468 $status .= ', System status: ' . $this->DB->stat(); 1469 } else { 1470 $status = 'no connections available'; 1471 } 1472 1473 return $status; 1474 } 1475 1476 /** 1477 * destructor 1478 */ 1479 public function __destruct() 1480 { 1481 if ($this->debug) $this->debug->getLog(); 1482 return $this->closeConnection(); 1483 } 1484 1485} 1486