1<?php 2// +----------------------------------------------------------------------+ 3// | PHP versions 4 and 5 | 4// +----------------------------------------------------------------------+ 5// | Copyright (c) 1998-2006 Manuel Lemos, Tomas V.V.Cox, | 6// | Stig. S. Bakken, Lukas Smith | 7// | All rights reserved. | 8// +----------------------------------------------------------------------+ 9// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB | 10// | API as well as database abstraction for PHP applications. | 11// | This LICENSE is in the BSD license style. | 12// | | 13// | Redistribution and use in source and binary forms, with or without | 14// | modification, are permitted provided that the following conditions | 15// | are met: | 16// | | 17// | Redistributions of source code must retain the above copyright | 18// | notice, this list of conditions and the following disclaimer. | 19// | | 20// | Redistributions in binary form must reproduce the above copyright | 21// | notice, this list of conditions and the following disclaimer in the | 22// | documentation and/or other materials provided with the distribution. | 23// | | 24// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, | 25// | Lukas Smith nor the names of his contributors may be used to endorse | 26// | or promote products derived from this software without specific prior| 27// | written permission. | 28// | | 29// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS | 30// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT | 31// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS | 32// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE | 33// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, | 34// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, | 35// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS| 36// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED | 37// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT | 38// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY| 39// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE | 40// | POSSIBILITY OF SUCH DAMAGE. | 41// +----------------------------------------------------------------------+ 42// | Author: Lukas Smith <smith@pooteeweet.org> | 43// +----------------------------------------------------------------------+ 44// 45// $Id: Extended.php 327310 2012-08-27 15:16:18Z danielc $ 46 47/** 48 * @package MDB2 49 * @category Database 50 * @author Lukas Smith <smith@pooteeweet.org> 51 */ 52 53/** 54 * Used by autoPrepare() 55 */ 56define('MDB2_AUTOQUERY_INSERT', 1); 57define('MDB2_AUTOQUERY_UPDATE', 2); 58define('MDB2_AUTOQUERY_DELETE', 3); 59define('MDB2_AUTOQUERY_SELECT', 4); 60 61/** 62 * MDB2_Extended: class which adds several high level methods to MDB2 63 * 64 * @package MDB2 65 * @category Database 66 * @author Lukas Smith <smith@pooteeweet.org> 67 */ 68class MDB2_Extended extends MDB2_Module_Common 69{ 70 // {{{ autoPrepare() 71 72 /** 73 * Generate an insert, update or delete query and call prepare() on it 74 * 75 * @param string table 76 * @param array the fields names 77 * @param int type of query to build 78 * MDB2_AUTOQUERY_INSERT 79 * MDB2_AUTOQUERY_UPDATE 80 * MDB2_AUTOQUERY_DELETE 81 * MDB2_AUTOQUERY_SELECT 82 * @param string (in case of update and delete queries, this string will be put after the sql WHERE statement) 83 * @param array that contains the types of the placeholders 84 * @param mixed array that contains the types of the columns in 85 * the result set or MDB2_PREPARE_RESULT, if set to 86 * MDB2_PREPARE_MANIP the query is handled as a manipulation query 87 * 88 * @return resource handle for the query 89 * @see buildManipSQL 90 * @access public 91 */ 92 function autoPrepare($table, $table_fields, $mode = MDB2_AUTOQUERY_INSERT, 93 $where = false, $types = null, $result_types = MDB2_PREPARE_MANIP) 94 { 95 $query = $this->buildManipSQL($table, $table_fields, $mode, $where); 96 if (MDB2::isError($query)) { 97 return $query; 98 } 99 $db = $this->getDBInstance(); 100 if (MDB2::isError($db)) { 101 return $db; 102 } 103 $lobs = array(); 104 foreach ((array)$types as $param => $type) { 105 if (($type == 'clob') || ($type == 'blob')) { 106 $lobs[$param] = $table_fields[$param]; 107 } 108 } 109 return $db->prepare($query, $types, $result_types, $lobs); 110 } 111 112 // }}} 113 // {{{ autoExecute() 114 115 /** 116 * Generate an insert, update or delete query and call prepare() and execute() on it 117 * 118 * @param string name of the table 119 * @param array assoc ($key=>$value) where $key is a field name and $value its value 120 * @param int type of query to build 121 * MDB2_AUTOQUERY_INSERT 122 * MDB2_AUTOQUERY_UPDATE 123 * MDB2_AUTOQUERY_DELETE 124 * MDB2_AUTOQUERY_SELECT 125 * @param string (in case of update and delete queries, this string will be put after the sql WHERE statement) 126 * @param array that contains the types of the placeholders 127 * @param string which specifies which result class to use 128 * @param mixed array that contains the types of the columns in 129 * the result set or MDB2_PREPARE_RESULT, if set to 130 * MDB2_PREPARE_MANIP the query is handled as a manipulation query 131 * 132 * @return bool|MDB2_Error true on success, a MDB2 error on failure 133 * @see buildManipSQL 134 * @see autoPrepare 135 * @access public 136 */ 137 function autoExecute($table, $fields_values, $mode = MDB2_AUTOQUERY_INSERT, 138 $where = false, $types = null, $result_class = true, $result_types = MDB2_PREPARE_MANIP) 139 { 140 $fields_values = (array)$fields_values; 141 if ($mode == MDB2_AUTOQUERY_SELECT) { 142 if (is_array($result_types)) { 143 $keys = array_keys($result_types); 144 } elseif (!empty($fields_values)) { 145 $keys = $fields_values; 146 } else { 147 $keys = array(); 148 } 149 } else { 150 $keys = array_keys($fields_values); 151 } 152 $params = array_values($fields_values); 153 if (empty($params)) { 154 $query = $this->buildManipSQL($table, $keys, $mode, $where); 155 156 $db = $this->getDBInstance(); 157 if (MDB2::isError($db)) { 158 return $db; 159 } 160 if ($mode == MDB2_AUTOQUERY_SELECT) { 161 $result = $db->query($query, $result_types, $result_class); 162 } else { 163 $result = $db->exec($query); 164 } 165 } else { 166 $stmt = $this->autoPrepare($table, $keys, $mode, $where, $types, $result_types); 167 if (MDB2::isError($stmt)) { 168 return $stmt; 169 } 170 $result = $stmt->execute($params, $result_class); 171 $stmt->free(); 172 } 173 return $result; 174 } 175 176 // }}} 177 // {{{ buildManipSQL() 178 179 /** 180 * Make automaticaly an sql query for prepare() 181 * 182 * Example : buildManipSQL('table_sql', array('field1', 'field2', 'field3'), MDB2_AUTOQUERY_INSERT) 183 * will return the string : INSERT INTO table_sql (field1,field2,field3) VALUES (?,?,?) 184 * NB : - This belongs more to a SQL Builder class, but this is a simple facility 185 * - Be carefull ! If you don't give a $where param with an UPDATE/DELETE query, all 186 * the records of the table will be updated/deleted ! 187 * 188 * @param string name of the table 189 * @param ordered array containing the fields names 190 * @param int type of query to build 191 * MDB2_AUTOQUERY_INSERT 192 * MDB2_AUTOQUERY_UPDATE 193 * MDB2_AUTOQUERY_DELETE 194 * MDB2_AUTOQUERY_SELECT 195 * @param string (in case of update and delete queries, this string will be put after the sql WHERE statement) 196 * 197 * @return string sql query for prepare() 198 * @access public 199 */ 200 function buildManipSQL($table, $table_fields, $mode, $where = false) 201 { 202 $db = $this->getDBInstance(); 203 if (MDB2::isError($db)) { 204 return $db; 205 } 206 207 if ($db->options['quote_identifier']) { 208 $table = $db->quoteIdentifier($table); 209 } 210 211 if (!empty($table_fields) && $db->options['quote_identifier']) { 212 foreach ($table_fields as $key => $field) { 213 $table_fields[$key] = $db->quoteIdentifier($field); 214 } 215 } 216 217 if ((false !== $where) && (null !== $where)) { 218 if (is_array($where)) { 219 $where = implode(' AND ', $where); 220 } 221 $where = ' WHERE '.$where; 222 } 223 224 switch ($mode) { 225 case MDB2_AUTOQUERY_INSERT: 226 if (empty($table_fields)) { 227 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null, 228 'Insert requires table fields', __FUNCTION__); 229 } 230 $cols = implode(', ', $table_fields); 231 $values = '?'.str_repeat(', ?', (count($table_fields) - 1)); 232 return 'INSERT INTO '.$table.' ('.$cols.') VALUES ('.$values.')'; 233 break; 234 case MDB2_AUTOQUERY_UPDATE: 235 if (empty($table_fields)) { 236 return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null, 237 'Update requires table fields', __FUNCTION__); 238 } 239 $set = implode(' = ?, ', $table_fields).' = ?'; 240 $sql = 'UPDATE '.$table.' SET '.$set.$where; 241 return $sql; 242 break; 243 case MDB2_AUTOQUERY_DELETE: 244 $sql = 'DELETE FROM '.$table.$where; 245 return $sql; 246 break; 247 case MDB2_AUTOQUERY_SELECT: 248 $cols = !empty($table_fields) ? implode(', ', $table_fields) : '*'; 249 $sql = 'SELECT '.$cols.' FROM '.$table.$where; 250 return $sql; 251 break; 252 } 253 return $db->raiseError(MDB2_ERROR_SYNTAX, null, null, 254 'Non existant mode', __FUNCTION__); 255 } 256 257 // }}} 258 // {{{ limitQuery() 259 260 /** 261 * Generates a limited query 262 * 263 * @param string query 264 * @param array that contains the types of the columns in the result set 265 * @param integer the numbers of rows to fetch 266 * @param integer the row to start to fetching 267 * @param string which specifies which result class to use 268 * @param mixed string which specifies which class to wrap results in 269 * 270 * @return MDB2_Result|MDB2_Error result set on success, a MDB2 error on failure 271 * @access public 272 */ 273 function limitQuery($query, $types, $limit, $offset = 0, $result_class = true, 274 $result_wrap_class = false) 275 { 276 $db = $this->getDBInstance(); 277 if (MDB2::isError($db)) { 278 return $db; 279 } 280 281 $result = $db->setLimit($limit, $offset); 282 if (MDB2::isError($result)) { 283 return $result; 284 } 285 return $db->query($query, $types, $result_class, $result_wrap_class); 286 } 287 288 // }}} 289 // {{{ execParam() 290 291 /** 292 * Execute a parameterized DML statement. 293 * 294 * @param string the SQL query 295 * @param array if supplied, prepare/execute will be used 296 * with this array as execute parameters 297 * @param array that contains the types of the values defined in $params 298 * 299 * @return int|MDB2_Error affected rows on success, a MDB2 error on failure 300 * @access public 301 */ 302 function execParam($query, $params = array(), $param_types = null) 303 { 304 $db = $this->getDBInstance(); 305 if (MDB2::isError($db)) { 306 return $db; 307 } 308 309 settype($params, 'array'); 310 if (empty($params)) { 311 return $db->exec($query); 312 } 313 314 $stmt = $db->prepare($query, $param_types, MDB2_PREPARE_MANIP); 315 if (MDB2::isError($stmt)) { 316 return $stmt; 317 } 318 319 $result = $stmt->execute($params); 320 if (MDB2::isError($result)) { 321 return $result; 322 } 323 324 $stmt->free(); 325 return $result; 326 } 327 328 // }}} 329 // {{{ getOne() 330 331 /** 332 * Fetch the first column of the first row of data returned from a query. 333 * Takes care of doing the query and freeing the results when finished. 334 * 335 * @param string the SQL query 336 * @param string that contains the type of the column in the result set 337 * @param array if supplied, prepare/execute will be used 338 * with this array as execute parameters 339 * @param array that contains the types of the values defined in $params 340 * @param int|string which column to return 341 * 342 * @return scalar|MDB2_Error data on success, a MDB2 error on failure 343 * @access public 344 */ 345 function getOne($query, $type = null, $params = array(), 346 $param_types = null, $colnum = 0) 347 { 348 $db = $this->getDBInstance(); 349 if (MDB2::isError($db)) { 350 return $db; 351 } 352 353 settype($params, 'array'); 354 settype($type, 'array'); 355 if (empty($params)) { 356 return $db->queryOne($query, $type, $colnum); 357 } 358 359 $stmt = $db->prepare($query, $param_types, $type); 360 if (MDB2::isError($stmt)) { 361 return $stmt; 362 } 363 364 $result = $stmt->execute($params); 365 if (!MDB2::isResultCommon($result)) { 366 return $result; 367 } 368 369 $one = $result->fetchOne($colnum); 370 $stmt->free(); 371 $result->free(); 372 return $one; 373 } 374 375 // }}} 376 // {{{ getRow() 377 378 /** 379 * Fetch the first row of data returned from a query. Takes care 380 * of doing the query and freeing the results when finished. 381 * 382 * @param string the SQL query 383 * @param array that contains the types of the columns in the result set 384 * @param array if supplied, prepare/execute will be used 385 * with this array as execute parameters 386 * @param array that contains the types of the values defined in $params 387 * @param int the fetch mode to use 388 * 389 * @return array|MDB2_Error data on success, a MDB2 error on failure 390 * @access public 391 */ 392 function getRow($query, $types = null, $params = array(), 393 $param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT) 394 { 395 $db = $this->getDBInstance(); 396 if (MDB2::isError($db)) { 397 return $db; 398 } 399 400 settype($params, 'array'); 401 if (empty($params)) { 402 return $db->queryRow($query, $types, $fetchmode); 403 } 404 405 $stmt = $db->prepare($query, $param_types, $types); 406 if (MDB2::isError($stmt)) { 407 return $stmt; 408 } 409 410 $result = $stmt->execute($params); 411 if (!MDB2::isResultCommon($result)) { 412 return $result; 413 } 414 415 $row = $result->fetchRow($fetchmode); 416 $stmt->free(); 417 $result->free(); 418 return $row; 419 } 420 421 // }}} 422 // {{{ getCol() 423 424 /** 425 * Fetch a single column from a result set and return it as an 426 * indexed array. 427 * 428 * @param string the SQL query 429 * @param string that contains the type of the column in the result set 430 * @param array if supplied, prepare/execute will be used 431 * with this array as execute parameters 432 * @param array that contains the types of the values defined in $params 433 * @param int|string which column to return 434 * 435 * @return array|MDB2_Error data on success, a MDB2 error on failure 436 * @access public 437 */ 438 function getCol($query, $type = null, $params = array(), 439 $param_types = null, $colnum = 0) 440 { 441 $db = $this->getDBInstance(); 442 if (MDB2::isError($db)) { 443 return $db; 444 } 445 446 settype($params, 'array'); 447 settype($type, 'array'); 448 if (empty($params)) { 449 return $db->queryCol($query, $type, $colnum); 450 } 451 452 $stmt = $db->prepare($query, $param_types, $type); 453 if (MDB2::isError($stmt)) { 454 return $stmt; 455 } 456 457 $result = $stmt->execute($params); 458 if (!MDB2::isResultCommon($result)) { 459 return $result; 460 } 461 462 $col = $result->fetchCol($colnum); 463 $stmt->free(); 464 $result->free(); 465 return $col; 466 } 467 468 // }}} 469 // {{{ getAll() 470 471 /** 472 * Fetch all the rows returned from a query. 473 * 474 * @param string the SQL query 475 * @param array that contains the types of the columns in the result set 476 * @param array if supplied, prepare/execute will be used 477 * with this array as execute parameters 478 * @param array that contains the types of the values defined in $params 479 * @param int the fetch mode to use 480 * @param bool if set to true, the $all will have the first 481 * column as its first dimension 482 * @param bool $force_array used only when the query returns exactly 483 * two columns. If true, the values of the returned array will be 484 * one-element arrays instead of scalars. 485 * @param bool $group if true, the values of the returned array is 486 * wrapped in another array. If the same key value (in the first 487 * column) repeats itself, the values will be appended to this array 488 * instead of overwriting the existing values. 489 * 490 * @return array|MDB2_Error data on success, a MDB2 error on failure 491 * @access public 492 */ 493 function getAll($query, $types = null, $params = array(), 494 $param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT, 495 $rekey = false, $force_array = false, $group = false) 496 { 497 $db = $this->getDBInstance(); 498 if (MDB2::isError($db)) { 499 return $db; 500 } 501 502 settype($params, 'array'); 503 if (empty($params)) { 504 return $db->queryAll($query, $types, $fetchmode, $rekey, $force_array, $group); 505 } 506 507 $stmt = $db->prepare($query, $param_types, $types); 508 if (MDB2::isError($stmt)) { 509 return $stmt; 510 } 511 512 $result = $stmt->execute($params); 513 if (!MDB2::isResultCommon($result)) { 514 return $result; 515 } 516 517 $all = $result->fetchAll($fetchmode, $rekey, $force_array, $group); 518 $stmt->free(); 519 $result->free(); 520 return $all; 521 } 522 523 // }}} 524 // {{{ getAssoc() 525 526 /** 527 * Fetch the entire result set of a query and return it as an 528 * associative array using the first column as the key. 529 * 530 * If the result set contains more than two columns, the value 531 * will be an array of the values from column 2-n. If the result 532 * set contains only two columns, the returned value will be a 533 * scalar with the value of the second column (unless forced to an 534 * array with the $force_array parameter). A MDB2 error code is 535 * returned on errors. If the result set contains fewer than two 536 * columns, a MDB2_ERROR_TRUNCATED error is returned. 537 * 538 * For example, if the table 'mytable' contains: 539 * <pre> 540 * ID TEXT DATE 541 * -------------------------------- 542 * 1 'one' 944679408 543 * 2 'two' 944679408 544 * 3 'three' 944679408 545 * </pre> 546 * Then the call getAssoc('SELECT id,text FROM mytable') returns: 547 * <pre> 548 * array( 549 * '1' => 'one', 550 * '2' => 'two', 551 * '3' => 'three', 552 * ) 553 * </pre> 554 * ...while the call getAssoc('SELECT id,text,date FROM mytable') returns: 555 * <pre> 556 * array( 557 * '1' => array('one', '944679408'), 558 * '2' => array('two', '944679408'), 559 * '3' => array('three', '944679408') 560 * ) 561 * </pre> 562 * 563 * If the more than one row occurs with the same value in the 564 * first column, the last row overwrites all previous ones by 565 * default. Use the $group parameter if you don't want to 566 * overwrite like this. Example: 567 * <pre> 568 * getAssoc('SELECT category,id,name FROM mytable', null, null 569 * MDB2_FETCHMODE_ASSOC, false, true) returns: 570 * array( 571 * '1' => array(array('id' => '4', 'name' => 'number four'), 572 * array('id' => '6', 'name' => 'number six') 573 * ), 574 * '9' => array(array('id' => '4', 'name' => 'number four'), 575 * array('id' => '6', 'name' => 'number six') 576 * ) 577 * ) 578 * </pre> 579 * 580 * Keep in mind that database functions in PHP usually return string 581 * values for results regardless of the database's internal type. 582 * 583 * @param string the SQL query 584 * @param array that contains the types of the columns in the result set 585 * @param array if supplied, prepare/execute will be used 586 * with this array as execute parameters 587 * @param array that contains the types of the values defined in $params 588 * @param bool $force_array used only when the query returns 589 * exactly two columns. If TRUE, the values of the returned array 590 * will be one-element arrays instead of scalars. 591 * @param bool $group if TRUE, the values of the returned array 592 * is wrapped in another array. If the same key value (in the first 593 * column) repeats itself, the values will be appended to this array 594 * instead of overwriting the existing values. 595 * 596 * @return array|MDB2_Error data on success, a MDB2 error on failure 597 * @access public 598 */ 599 function getAssoc($query, $types = null, $params = array(), $param_types = null, 600 $fetchmode = MDB2_FETCHMODE_DEFAULT, $force_array = false, $group = false) 601 { 602 $db = $this->getDBInstance(); 603 if (MDB2::isError($db)) { 604 return $db; 605 } 606 607 settype($params, 'array'); 608 if (empty($params)) { 609 return $db->queryAll($query, $types, $fetchmode, true, $force_array, $group); 610 } 611 612 $stmt = $db->prepare($query, $param_types, $types); 613 if (MDB2::isError($stmt)) { 614 return $stmt; 615 } 616 617 $result = $stmt->execute($params); 618 if (!MDB2::isResultCommon($result)) { 619 return $result; 620 } 621 622 $all = $result->fetchAll($fetchmode, true, $force_array, $group); 623 $stmt->free(); 624 $result->free(); 625 return $all; 626 } 627 628 // }}} 629 // {{{ executeMultiple() 630 631 /** 632 * This function does several execute() calls on the same statement handle. 633 * $params must be an array indexed numerically from 0, one execute call is 634 * done for every 'row' in the array. 635 * 636 * If an error occurs during execute(), executeMultiple() does not execute 637 * the unfinished rows, but rather returns that error. 638 * 639 * @param resource query handle from prepare() 640 * @param array numeric array containing the data to insert into the query 641 * 642 * @return bool|MDB2_Error true on success, a MDB2 error on failure 643 * @access public 644 * @see prepare(), execute() 645 */ 646 function executeMultiple($stmt, $params = null) 647 { 648 if (MDB2::isError($stmt)) { 649 return $stmt; 650 } 651 for ($i = 0, $j = count($params); $i < $j; $i++) { 652 $result = $stmt->execute($params[$i]); 653 if (MDB2::isError($result)) { 654 return $result; 655 } 656 } 657 return MDB2_OK; 658 } 659 660 // }}} 661 // {{{ getBeforeID() 662 663 /** 664 * Returns the next free id of a sequence if the RDBMS 665 * does not support auto increment 666 * 667 * @param string name of the table into which a new row was inserted 668 * @param string name of the field into which a new row was inserted 669 * @param bool when true the sequence is automatic created, if it not exists 670 * @param bool if the returned value should be quoted 671 * 672 * @return int|MDB2_Error id on success, a MDB2 error on failure 673 * @access public 674 */ 675 function getBeforeID($table, $field = null, $ondemand = true, $quote = true) 676 { 677 $db = $this->getDBInstance(); 678 if (MDB2::isError($db)) { 679 return $db; 680 } 681 682 if ($db->supports('auto_increment') !== true) { 683 $seq = $table.(empty($field) ? '' : '_'.$field); 684 $id = $db->nextID($seq, $ondemand); 685 if (!$quote || MDB2::isError($id)) { 686 return $id; 687 } 688 return $db->quote($id, 'integer'); 689 } elseif (!$quote) { 690 return null; 691 } 692 return 'NULL'; 693 } 694 695 // }}} 696 // {{{ getAfterID() 697 698 /** 699 * Returns the autoincrement ID if supported or $id 700 * 701 * @param mixed value as returned by getBeforeId() 702 * @param string name of the table into which a new row was inserted 703 * @param string name of the field into which a new row was inserted 704 * 705 * @return int|MDB2_Error id on success, a MDB2 error on failure 706 * @access public 707 */ 708 function getAfterID($id, $table, $field = null) 709 { 710 $db = $this->getDBInstance(); 711 if (MDB2::isError($db)) { 712 return $db; 713 } 714 715 if ($db->supports('auto_increment') !== true) { 716 return $id; 717 } 718 return $db->lastInsertID($table, $field); 719 } 720 721 // }}} 722} 723?> 724