/framework/core/db/DbConnection.php
PHP | 811 lines | 481 code | 119 blank | 211 comment | 52 complexity | 5b2d81ee6299163458a058c2b218c98d MD5 | raw file
1<?php 2abstract class DbConnection 3{ 4 protected $params; 5 private $queryParams; 6 private $types; 7 /** 8 * Actual connection to the database 9 * 10 * @var unknown_type 11 */ 12 private $conn; 13 private $echo; 14 15 function __construct($params, $connectionName) 16 { 17 $this->params = $params; 18 $this->validateParams($connectionName); 19 $echo = false; 20 $this->init(); 21 } 22 23 // 24 // Begin configuration funtions 25 // 26 27 private function validateParams($connectionName) 28 { 29 // handle the required fields 30 $missing = array(); 31 foreach($this->getRequireds() as $thisRequired) 32 { 33 if(!isset($this->params[$thisRequired])) 34 $missing[] = $thisRequired; 35 } 36 37 if(!empty($missing)) 38 throw new ConfigException('db', $missing, "for connection $connectionName"); 39 40 // handle the defaults 41 foreach($this->getDefaults() as $name => $value) 42 { 43 if(!isset($this->params[$name])) 44 $this->params[$name] = $value; 45 } 46 } 47 48 protected function init() 49 { 50 } 51 52 protected function getRequireds() 53 { 54 return array(); 55 } 56 57 protected function getDefaults() 58 { 59 return array(); 60 } 61 62 // 63 // End configuration funtions 64 // 65 66 // 67 // Begin misc funtions 68 // 69 70 public function echoOn() 71 { 72 $this->echo = true; 73 } 74 75 public function echoOff() 76 { 77 $this->echo = false; 78 } 79 80 function escapeString($string) 81 { 82 trigger_error("escapeString must be defined in each individual database driver"); 83 } 84 85 function escapeIdentifier($string) 86 { 87 return '"' . $string . '"'; 88 } 89 90 // 91 // End misc funtions 92 // 93 94 // 95 // Begin Schema functions 96 // 97 98 public function alterSchema($sql) 99 { 100 return $this->_query($sql); 101 } 102 103 public function getSchema() 104 { 105 return new DbSchema($this); 106 } 107 108 abstract public function tableExists($name); 109 abstract public function getTableNames(); 110 abstract public function getTableFieldInfo($tableName); 111 112 /** 113 * Executes the passed in SQL statement on the database and returns a result set 114 * 115 * @param string $sql SQL statement to execute 116 * @return DbResultSet object 117 */ 118 abstract public function _query($sql); 119 120 // 121 // End Schema functions 122 // 123 124 // 125 // Begin transaction funtions 126 // 127 128 public function beginTransaction() 129 { 130 $this->_query('begin'); 131 } 132 133 public function commitTransaction() 134 { 135 $this->_query('commit'); 136 } 137 138 public function rollbackTransaction() 139 { 140 $this->_query('rollback'); 141 } 142 143 // 144 // End transaction funtions 145 // 146 147 // 148 // Begin query funtions 149 // 150 151 /** 152 * Escapes and inserts parameters into the the SQL statement and executes the statement. 153 * 154 * Syntax for parameters: 155 * - :<varname>:string -- the variable is escaped as a string and replaces this entry 156 * - :<varname>:int -- the variable is escaped as a number and replaces this entry 157 * - :<varname>:keyword -- the variable replaces this entry with no change 158 * - :<varname>:identifier -- the variable is escaped as a SQL identifier (table, field name, etc) and replaces this entry 159 * - :<varname> -- if no type is specified, variable is treated as a string. 160 * 161 * @param string $sql SQL statement to execute 162 * @param assoc_array $params $variable => $value array of parameters to substitute into the SQL statement 163 * @return DbResultSet Resultset object 164 */ 165 public function query($sql, $params) 166 { 167 // do all of the variable replacements 168 $this->queryParams = array(); 169 foreach($params as $key => $value) 170 { 171 $parts = explode(':', $key); 172 $this->queryParams[$parts[0]] = $value; 173 if(isset($parts[1])) 174 $this->types[$parts[0]] = $parts[1]; 175 } 176 // TODO: indication here how to escape options in here 177 // $sql = preg_replace_callback("/:([[:alpha:]_\d]+):([[:alpha:]_]+)|[^:]:([[:alpha:]_\d]+)/", array($this, 'queryCallback'), $sql); 178 $sql = preg_replace_callback("/:([[:alpha:]_\d]+):([[:alpha:]_]+)|:([[:alpha:]_\d]+)/", array($this, 'queryCallback'), $sql); 179 180 if($this->echo) 181 { 182 if(php_sapi_name() == "cli") 183 echo $sql . "\n"; 184 else 185 echo $sql . '<br>'; 186 } 187 188 189 // actually do the query 190 return $this->_query($sql); 191 } 192 193 /** 194 * Private callback function passed to preg_replace_callback for doing the variable substitutions 195 * This method handles all escaping of strings and identifiers based on the matched list. 196 * Syntax: 197 * - :<varname>:string -- the variable is escaped as a string and replaces this entry 198 * - :<varname>:int -- the variable is escaped as a number and replaces this entry 199 * - :<varname>:keyword -- the variable replaces this entry with no change 200 * - :<varname>:identifier -- the variable is escaped as a SQL identifier (table, field name, etc) and replaces this entry 201 * - :<varname> -- if no type is specified, variable is treated as a string. 202 * 203 * @param array $matches array of matches provided by preg_replace_callback 204 * @return string String to replace the matched string with 205 */ 206 private function queryCallback($matches) 207 { 208 if(isset($matches[3])) 209 $matches[1] = $matches[3]; 210 211 $name = $matches[1]; 212 if(is_null($this->queryParams[$name])) 213 return 'NULL'; 214 if($matches[2]) 215 $type = $matches[2]; 216 217 if(isset($type) && isset($this->types[$name])) 218 assert($type == $this->types[$name]); 219 220 $type = isset($type) ? $type : (isset($this->types[$name]) ? $this->types[$name] : 'string'); 221 222 switch($type) 223 { 224 case 'string': 225 $replaceString = $this->escapeString($this->queryParams[$name]); 226 break; 227 case 'int': 228 $replaceString = (int)$this->queryParams[$name]; 229 break; 230 case 'keyword': 231 $replaceString = $this->queryParams[$name]; 232 break; 233 case 'identifier': 234 $replaceString = $this->escapeIdentifier($this->queryParams[$name]); 235 break; 236 case 'inInts': 237 assert(is_array($this->queryParams[$name])); 238 foreach($this->queryParams[$name] as $key => $int) 239 { 240 $this->queryParams[$name][$key] = (int)$int; 241 } 242 $replaceString = implode(', ', $this->queryParams[$name]); 243 break; 244 case 'inStrings': 245 assert(is_array($this->queryParams[$name])); 246 foreach($this->queryParams[$name] as $key => $string) 247 { 248 $this->queryParams[$name][$key] = $this->escapeString($string); 249 } 250 $replaceString = implode(', ', $this->queryParams[$name]); 251 break; 252 default: 253 trigger_error("unknown param type: " . $type); 254 break; 255 } 256 257 return $replaceString; 258 } 259 260 // 261 // end query funtions 262 // 263 264 265 // 266 // Begin fetch funtions 267 // 268 269 public function fetchCell($sql, $params) 270 { 271 $row = $this->fetchRow($sql, $params); 272 if(!$row) 273 return NULL; 274 return current($row); 275 } 276 277 public function fetchRow($sql, $params) 278 { 279 $res = $this->query($sql, $params); 280 $num = $res->numRows(); 281 if($num > 1) 282 trigger_error("1 row expected: $num returned"); 283 284 if($num == 0) 285 return false; 286 287 return $res->current(); 288 } 289 290 public function fetchRows($sql, $params) 291 { 292 $rows = array(); 293 $res = $this->query($sql, $params); 294 295 if(!$res->valid()) 296 return array(); 297 298 for($row = $res->current(); $res->valid(); $row = $res->next()) 299 { 300 $rows[] = $row; 301 } 302 303 return $rows; 304 } 305 306 public function fetchColumn($sql, $params) 307 { 308 $rows = array(); 309 $res = $this->query($sql, $params); 310 311 if(!$res->valid()) 312 return array(); 313 314 for($row = $res->current(); $res->valid(); $row = $res->next()) 315 { 316 $rows[] = current($row); 317 } 318 319 return $rows; 320 } 321 322 323 /** 324 * Creates a simple nested array structure grouping the values of the $valueField column by the values of the columns specified in the $keyFields array. 325 * 326 * For example, if your query returns a list of books and you'd like to group the titles by subject and isbn number, let $keyFields = array("subject", "isbn") and $valueField = "title". 327 * The format thus created will be $var[$subject][$isbn] = $title; 328 * 329 * @param string $sql SQL query with parameters in the format ":variablename" or ":variablename:datatype" 330 * @param array $keyFields array of fields to group the results by 331 * @param array $valueField name of the field containing the value to be grouped 332 * @param array($key=>$value) $params ($key => value) array of parameters to substitute into the SQL query. If you are not passing parameters in, params should be an empty array() 333 * @return associative array structure grouped by the values in $mapFields 334 */ 335 public function fetchSimpleMap($sql, $keyFields, $valueField, $params) 336 { 337 $map = array(); 338 $res = $this->query($sql, $params); 339 for($row = $res->current(); $res->valid(); $row = $res->next()) 340 { 341 $cur = &$map; 342 if(is_array($keyFields)) 343 { 344 foreach($keyFields as $key) 345 { 346 $cur = &$cur[$row[$key]]; 347 $lastKey = $row[$key]; 348 } 349 } 350 else 351 { 352 $cur = &$cur[$row[$keyFields]]; 353 $lastKey = $row[$keyFields]; 354 } 355 356 if(isset($cur) && !empty($lastKey)) 357 trigger_error("db::fetchSimpleMap : duplicate key in query: \n $sql \n"); 358 359 $cur = $row[ $valueField ]; 360 } 361 362 return $map; 363 } 364 365 /** 366 * Returns a nested array, grouped by the fields (or field) listed in $mapFields 367 * 368 * For example, if mapFields = array("person_id", "book_id"), and the resultset returns 369 * a list of all the chapters of all the books of all the people, this will group the 370 * records by person and by book, keeping each row in an array under 371 * $var[$person_id][$book_id] 372 * 373 * @param string $sql SQL query with parameters in the format ":variablename" or ":variablename:datatype" 374 * @param array $mapFields array of fields to group the results by 375 * @param array($key=>$value) $params ($key => value) array of parameters to substitute into the SQL query. If you are not passing parameters in, params should be an empty array() 376 * @return associative array structure grouped by the values in $mapFields 377 */ 378 public function fetchMap($sql, $mapFields, $params) 379 { 380 $map = array(); 381 $res = $this->query($sql, $params); 382 for($row = $res->current(); $res->valid(); $row = $res->next()) 383 { 384 if(is_array($mapFields)) 385 { 386 $cur = &$map; 387 388 foreach($mapFields as $val) 389 { 390 $curKey = $row[$val]; 391 392 if(!isset($cur[$curKey])) 393 $cur[$curKey] = array(); 394 395 $cur = &$cur[$curKey]; 396 } 397 398 if(count($cur)) 399 trigger_error("db::fetchSimpleMap : duplicate key $curKey (would silently destroy data) in query: \n $inQuery \n"); 400 401 $cur = $row; 402 } 403 else 404 { 405 $mapKey = $row[$mapFields]; 406 $map[$mapKey] = $row; 407 } 408 } 409 410 return $map; 411 } 412 413 public function fetchComplexMap($sql, $mapFields, $params) 414 { 415 $map = array(); 416 $res = $this->query($sql, $params); 417 for($row = $res->current(); $res->valid(); $row = $res->next()) 418 { 419 if(is_array($mapFields)) 420 { 421 $cur = &$map; 422 423 foreach($mapFields as $val) 424 { 425 $curKey = $row[$val]; 426 427 if(!isset($cur[$curKey])) 428 $cur[$curKey] = array(); 429 430 $cur = &$cur[$curKey]; 431 } 432 433 $cur[] = $row; 434 } 435 else 436 { 437 $mapKey = $row[$mapFields]; 438 $map[$mapKey][] = $row; 439 } 440 } 441 442 return $map; 443 } 444 445 // 446 // End fetch functions 447 // 448 449 // 450 // Begin insert functions 451 // 452 453 public function insertRow($sql, $params, $serial = true) 454 { 455 $this->query($sql, $params); 456 if($serial) 457 return $this->getLastInsertId(); 458 else 459 return false; 460 } 461 462 public function insertRows($sql, $params, $serial = false) 463 { 464 $ids = array(); 465 foreach($params as $thisRow) 466 { 467 $id = $this->insertRow($sql, $thisRow, $serial); 468 if($serial) 469 $ids[] = $id; 470 } 471 } 472 473 /** 474 * Automatically insert the values of an ($key=>$value) array into a database using the $key as the field name 475 * 476 * @param string $tableName Table into which the insert should be performed 477 * @param array $values ($key => $value) array in the format ($fieldName => $fieldValue) 478 * @param boolean $serial True if the last inserted ID should be returned 479 * @return mixed ID of the inserted row or false if $serial == false 480 */ 481 public function insertArray($tableName, $values, $serial = true) 482 { 483 $insertInfo = DbConnection::generateInsertInfo($tableName, $values); 484 return $this->insertRow($insertInfo['sql'], $insertInfo['params'], $serial); 485 } 486 487 // 488 // End insert functions 489 // 490 491 // 492 // Begin update functions 493 // 494 495 /** 496 * Executes the given update SQL statement. This method throws an error if multiple rows are updated. 497 * 498 * @param string $sql SQL UPDATE statement 499 * @param assoc_array $params $param => $value array of parameters to escape and substitute into the query 500 */ 501 public function updateRow($sql, $params) 502 { 503 $affected = $this->updateRows($sql, $params); 504 if($affected == 0 || $affected > 1) 505 trigger_error("attempting to update one row, $affected altered"); 506 } 507 508 /** 509 * Executes the given update SQL statement 510 * 511 * @param string $sql SQL UPDATE statement 512 * @param assoc_array $params $param => $value array of parameters to escape and substitute into the query 513 */ 514 public function updateRows($sql, $params) 515 { 516 $res = $this->query($sql, $params); 517 return $res->affectedRows(); 518 } 519 520 // 521 // End update functions 522 // 523 524 // 525 // Begin delete functions 526 // 527 528 public function deleteRow($sql, $params) 529 { 530 // it just so happens that they actually need to do the exact same thing 531 $this->updateRow($sql, $params); 532 } 533 534 public function deleteRows($sql, $params) 535 { 536 // it just so happens that they actually need to do the exact same thing 537 return $this->updateRows($sql, $params); 538 } 539 540 // 541 // End delete functions 542 // 543 544 // 545 // Begin combo functions 546 // 547 548 /** 549 * Automatically generates a select statement using the given information 550 * 551 * @param string $tableName name of the table to select from 552 * @param array $fieldsNames array of fields to retreive 553 * @param assoc_array $conditions $field => $value array of what to check (at this time only the "=" operator is supported) 554 * @param assoc_array $params $var => $value array of optional special parameters. Currently "lock" and "orderby" (set to an array of field names) are supported. 555 * @return string SQL select statement 556 */ 557 static function generateSelectInfo($tableName, $fieldsNames, $conditions = NULL, $params = NULL) 558 { 559 // echo_r($params); 560 $selectParams = array(); 561 562 // create the field clause 563 // This first option will allow a "*" or a comma separated list of fields in case an array was not passed in 564 if(is_string($fieldsNames)) 565 $fieldClause = $fieldsNames; 566 else 567 { 568 $fieldList = array(); 569 foreach ($fieldsNames as $fieldName) 570 { 571 $fieldList[] = ":fld_$fieldName:identifier"; 572 $selectParams["fld_$fieldName"] = $fieldName; 573 } 574 $fieldClause = implode(', ', $fieldList); 575 } 576 577 // create the condition clause 578 if($conditions) 579 { 580 $conditionParts = array(); 581 foreach($conditions as $fieldName => $value) 582 { 583 if(is_array($value)) 584 { 585 $valueParts = array(); 586 foreach($value as $thisKey => $thisValue) 587 { 588 $partName = "{$fieldName}_{$thisKey}"; 589 $valueParts[] = ":$partName"; 590 $selectParams[$partName] = $thisValue; 591 } 592 593 $valueString = implode(', ', $valueParts); 594 $conditionParts[] = ":fld_$fieldName:identifier in ($valueString)"; 595 } 596 else 597 $conditionParts[] = ":fld_$fieldName:identifier = :$fieldName"; 598 $selectParams[$fieldName] = $value; 599 $selectParams["fld_$fieldName"] = $fieldName; 600 } 601 $conditionClause = 'WHERE ' . implode(' AND ', $conditionParts); 602 } 603 else 604 $conditionClause = ''; 605 606 // create the lock clause 607 if(isset($params['lock']) && $params['lock']) 608 $lockClause = 'for update'; 609 else 610 $lockClause = ''; 611 612 // create the order by clause 613 if(isset($params['orderby']) && $params['orderby']) 614 { 615 $orderByClause = 'order by '; 616 if(is_array($params['orderby'])) 617 $orderByClause .= implode(', ', $params['orderby']); 618 else 619 $orderByClause .= $params['orderby']; 620 } 621 else 622 $orderByClause = ''; 623 624 // create the limit 625 if(isset($params['limit']) && $params['limit']) 626 { 627 $limitClause = 'limit ' . $params['limit']; 628 } 629 else 630 $limitClause = ''; 631 632 // now put it all together 633 $selectSql = "SELECT $fieldClause FROM :tableName:identifier $conditionClause $orderByClause $limitClause $lockClause"; 634 $selectParams['tableName'] = $tableName; 635 636 return array('sql' => $selectSql, 'params' => $selectParams); 637 } 638 639 /** 640 * Automatically generate an UPDATE SQL statement for the given table based on the passed in conditions and values. Currently only supports direct equalities for conditions. 641 * 642 * @param string $tableName Name of the table to update 643 * @param assoc_array $conditions $field => $value array of conditions. Currently only supports the "=" operator. 644 * @param assoc_array $values $field => $value array of fields to update. The update statement will be generated to update the fields supplied to the supplied values. 645 * @return string SQL statement to update the table 646 */ 647 static function generateUpdateInfo($tableName, $conditions, $values) 648 { 649 $updateParams = array(); 650 651 // create the condition part 652 $conditionParts = array(); 653 foreach($conditions as $fieldName => $value) 654 { 655 $conditionParts[] = ":fld_$fieldName:identifier = :$fieldName"; 656 $updateParams[$fieldName] = $value; 657 $updateParams["fld_$fieldName"] = $fieldName; 658 } 659 $conditionClause = implode(' AND ', $conditionParts); 660 661 // create the set part 662 $setParts = array(); 663 foreach($values as $fieldName => $value) 664 { 665 $fieldNameParts = explode(':', $fieldName); 666 $realFieldName = $fieldNameParts[0]; 667 668 $setParts[] = ":fld_$realFieldName:identifier = :$fieldName"; 669 $updateParams[$realFieldName] = $value; 670 $updateParams["fld_$realFieldName"] = $realFieldName; 671 } 672 673 $setClause = implode(', ', $setParts); 674 675 // now put it all together 676 $updateSql = "UPDATE :tableName:keyword SET $setClause WHERE $conditionClause"; 677 $updateParams['tableName'] = $tableName; 678 679 return array('sql' => $updateSql, 'params' => $updateParams); 680 } 681 682 /** 683 * Automatically generate a statement to delete records from a table based on certain conditions. Currently only the "=" operator is supported for conditions. 684 * 685 * @param string $tableName Name of the table to delete from 686 * @param assoc_array $conditions $field => $value array of conditions to match for the delete statement 687 * @return string SQL statement to delete records from the table 688 */ 689 static function generateDeleteInfo($tableName, $conditions) 690 { 691 $deleteParams = array(); 692 693 // create the condition part 694 $conditionParts = array(); 695 foreach($conditions as $fieldName => $value) 696 { 697 $conditionParts[] = ":fld_$fieldName:identifier = :$fieldName"; 698 $deleteParams[$fieldName] = $value; 699 $deleteParams["fld_$fieldName"] = $fieldName; 700 } 701 $conditionClause = implode(' AND ', $conditionParts); 702 703 $updateSql = "DELETE FROM :tableName:keyword WHERE $conditionClause"; 704 $deleteParams['tableName'] = $tableName; 705 706 return array('sql' => $updateSql, 'params' => $deleteParams); 707 } 708 709 /** 710 * Generate an insert statement from an associative ($key => $value) array 711 * 712 * @param string $tableName Table into which the insert should be performed 713 * @param array $values ($key => $value) array in the format ($fieldName => $fieldValue) 714 * @return string SQL statement to perform the insert 715 */ 716 static function generateInsertInfo($tableName, $values) 717 { 718 $insertParams = array(); 719 720 if($values) 721 { 722 // create the set part 723 $fieldParts = array(); 724 $valuesParts = array(); 725 foreach($values as $fieldName => $value) 726 { 727 $fieldNameParts = explode(':', $fieldName); 728 $realFieldName = $fieldNameParts[0]; 729 $fieldParts[] = ':fld_' . $realFieldName . ":identifier" ; 730 $valuesParts[] = ':' . $fieldName; 731 $insertParams[$realFieldName] = $value; 732 $insertParams["fld_" . $realFieldName] = $realFieldName; 733 } 734 $fieldClause = implode(', ', $fieldParts); 735 $valuesClause = implode(', ', $valuesParts); 736 737 // now put it all together 738 $insertSql = "INSERT INTO :tableName:identifier ($fieldClause) VALUES ($valuesClause)"; 739 $insertParams['tableName'] = $tableName; 740 } 741 else 742 { 743 $insertSql = "INSERT INTO :tableName:identifier DEFAULT VALUES"; 744 $insertParams['tableName'] = $tableName; 745 } 746 747 return array('sql' => $insertSql, 'params' => $insertParams); 748 } 749 750 public function upsertRow($tableName, $conditions, $values) 751 { 752 // generate the update query info 753 $updateInfo = self::generateUpdateInfo($tableName, $conditions, $values); 754 755 // update the row if it's there 756 $num = $this->updateRows($updateInfo['sql'], $updateInfo['params']); 757 758 if($num > 1) 759 trigger_error("one row expected, $num rows updated"); 760 761 if(!$num) 762 { 763 // generate the insert query info 764 $insertInfo = self::generateInsertInfo($tableName, array_merge($conditions, $values)); 765 766 // if it wasn't there insert it 767 $this->insertRow($insertInfo['sql'], $insertInfo['params'], false); 768 769 // 770 // I think the only thing that can go wrong here is we get a race condition where another process inserts the row 771 // after we do the update but before we do the insert. In that case an error will be thrown here that needs to be handled. 772 // Also after catching the error we should still try to execute the update statement. Of course if the thread causing the insert 773 // error then rolls back we might need to do an insert. Hmmm... this could get tricky. 774 // 775 } 776 } 777 778 public function selsertRow($tableName, $fieldNames, $conditions, $defaults = NULL, $lock = 0) 779 { 780 // generate the sqlect query info 781 $selectInfo = self::generateSelectInfo($tableName, $fieldNames, $conditions, $lock); 782 783 // select the row if it's there 784 $row = $this->fetchRow($selectInfo['sql'], $selectInfo['params']); 785 if(!$row) 786 { 787 // generate the insert query info 788 $allInsertFields = $defaults ? array_merge($conditions, $defaults) : $conditions; 789 $insertInfo = self::generateInsertInfo($tableName, array_merge($conditions, $allInsertFields)); 790 791 // if it wasn't there insert it 792 $this->insertRow($insertInfo['sql'], $insertInfo['params'], false); 793 // you may have a race condition here 794 // if another thread inserted it first then we need to supress the error in PHP4 795 // we should probably try to use exceptions in PHP5 796 797 $row = $this->fetchRow($selectInfo['sql'], $selectInfo['params']); 798 if(!$row) 799 { 800 // I'm pretty sure that this should actually never happen 801 trigger_error("error upsert: this shouldn't ever happen. If it does figure out why and fix this function"); 802 } 803 } 804 805 return $row; 806 } 807 808 // 809 // End combo functions 810 // 811}