1<?php 2 3/** 4 * Abstract DB clas. 5 * 6 * @abstract 7 */ 8abstract class DB { 9 10 11 /** 12 * Default db username 13 * 14 * (default value: null) 15 * 16 * @var mixed 17 * @access protected 18 */ 19 protected $username = null; 20 21 /** 22 * Default db password 23 * 24 * (default value: null) 25 * 26 * @var mixed 27 * @access protected 28 */ 29 protected $password = null; 30 31 /** 32 * charset 33 * 34 * (default value: 'utf8') 35 * 36 * @var string 37 * @access protected 38 */ 39 protected $charset = 'utf8'; 40 41 /** 42 * pdo 43 * 44 * (default value: null) 45 * 46 * @var mixed 47 * @access protected 48 */ 49 protected $pdo = null; 50 51 /** 52 * Database name - needed for check 53 * 54 * (default value: '') 55 * 56 * @var string 57 * @access public 58 */ 59 public $dbname = ''; // needed for DB check 60 61 /** 62 * hosnamr 63 * 64 * (default value: 'localhost') 65 * 66 * @var string 67 * @access protected 68 */ 69 protected $host = 'localhost'; 70 71 /** 72 * Default port number 73 * 74 * (default value: '3306') 75 * 76 * @var string 77 * @access protected 78 */ 79 protected $port = '3306'; 80 81 /** 82 * Cache file to store all results from queries to 83 * 84 * structure: 85 * 86 * [table][index] = (object) $content 87 * 88 * 89 * (default value: array()) 90 * 91 * @var array 92 * @access public 93 */ 94 public $cache = array(); 95 96 97 98 99 /** 100 * __construct function. 101 * 102 * @access public 103 * @param mixed $username (default: null) 104 * @param mixed $password (default: null) 105 * @param mixed $charset (default: null) 106 * @param mixed $ssl (default: null) 107 * @return void 108 */ 109 public function __construct($username = null, $password = null, $charset = null, $ssl = null) { 110 if (isset($username)) $this->username = $username; 111 if (isset($password)) $this->password = $password; 112 if (isset($charset)) $this->charset = $charset; 113 # ssl 114 if ($ssl) { 115 $this->ssl = $ssl; 116 } 117 } 118 119 /** 120 * convert a date object/string ready for use in sql 121 * 122 * @access public 123 * @static 124 * @param mixed $date (default: null) 125 * @return void 126 */ 127 public static function toDate($date = null) { 128 if (is_int($date)) { 129 return date('Y-m-d H:i:s', $date); 130 } else if (is_string($date)) { 131 return date('Y-m-d H:i:s', strtotime($date)); 132 } else { 133 return date('Y-m-d H:i:s'); 134 } 135 } 136 137 /** 138 * Connect to the database 139 * Call whenever a connection is needed to be made 140 * 141 * @access public 142 * @return void 143 */ 144 public function connect() { 145 $dsn = $this->makeDsn(); 146 147 try { 148 # ssl? 149 if ($this->ssl) { 150 $this->pdo = new \PDO($dsn, $this->username, $this->password, $this->ssl); 151 } 152 else { 153 $this->pdo = new \PDO($dsn, $this->username, $this->password); 154 } 155 156 $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); 157 158 } catch (\PDOException $e) { 159 throw new Exception ("Could not connect to database! ".$e->getMessage()); 160 } 161 162 @$this->pdo->query('SET NAMES \'' . $this->charset . '\';'); 163 } 164 165 /** 166 * makeDsn function. 167 * 168 * @access protected 169 * @return void 170 */ 171 protected function makeDsn() { 172 return ':charset=' . $this->charset; 173 } 174 175 /** 176 * resets conection. 177 * 178 * @access public 179 * @return void 180 */ 181 public function resetConn() { 182 unset($this->pdo); 183 $this->install = false; 184 } 185 186 /** 187 * logs queries to file 188 * 189 * @access private 190 * @param mixed $query 191 * @param array $values 192 * @return void 193 */ 194 private function log_query ($query, $values = false) { 195 if($this->debug) { 196 197 $myFile = "/tmp/queries.txt"; 198 $fh = fopen($myFile, 'a') or die("can't open file"); 199 // query 200 fwrite($fh, $query->queryString); 201 // values 202 if(is_array($values)) { 203 fwrite($fh, " Params: ".implode(", ", $values)); 204 } 205 // break 206 fwrite($fh, "\n"); 207 fclose($fh); 208 } 209 } 210 211 /** 212 * Remove outer quotes from a string 213 * 214 * @access public 215 * @static 216 * @param mixed $str 217 * @return void 218 */ 219 public static function unquote_outer($str) { 220 $len = strlen($str); 221 222 if ($len>1) { 223 if ($str[0] == "'" && $str[$len-1] == "'") { 224 return substr($str, 1, -1); 225 } else if ($str[0] == "'") { 226 return substr($str, 1); 227 } else if ($str[$len-1] == "'") { 228 return substr($str, 0, -1); 229 } 230 } else if ($len>0) { 231 if ($str[0] == "'") { 232 return ''; 233 } 234 } 235 236 return $str; 237 } 238 239 /** 240 * Are we currently connected to the database 241 * 242 * @access public 243 * @return void 244 */ 245 public function isConnected() { 246 return ($this->pdo !== null); 247 } 248 249 /** 250 * Returns last insert ID 251 * 252 * @access public 253 * @return void 254 */ 255 public function lastInsertId() { 256 return $this->pdo->lastInsertId(); 257 } 258 259 /** 260 * Run a statement on the database 261 * Note: no objects are fetched 262 * 263 * @access public 264 * @param mixed $query 265 * @param array $values (default: array()) 266 * @param integer|null &$rowCount (default: null) 267 * @return void 268 */ 269 public function runQuery($query, $values = array(), &$rowCount = null) { 270 if (!$this->isConnected()) $this->connect(); 271 272 $result = null; 273 274 $statement = $this->pdo->prepare($query); 275 276 //debuq 277 $this->log_query($statement, $values); 278 279 if (is_object($statement)) { 280 $result = $statement->execute((array)$values); //this array cast allows single values to be used as the parameter 281 $rowCount = $statement->rowCount(); 282 } 283 return $result; 284 } 285 286 /** 287 * Allow a value to be escaped, ready for insertion as a mysql parameter 288 * Note: for usage as a value (rather than prepared statements), you MUST manually quote around. 289 * 290 * @access public 291 * @param mixed $str 292 * @return void 293 */ 294 public function escape($str) { 295 $str = (string) $str; 296 if (strlen($str) == 0) return ""; 297 298 if (!$this->isConnected()) $this->connect(); 299 300 // SQL Injection - strip backquote character 301 $str = str_replace('`', '', $str); 302 return $this->unquote_outer($this->pdo->quote($str)); 303 } 304 305 /** 306 * Get a quick number of objects in a table 307 * 308 * @access public 309 * @param mixed $tableName 310 * @return void 311 */ 312 public function numObjects($tableName) { 313 if (!$this->isConnected()) $this->connect(); 314 315 $tableName = $this->escape($tableName); 316 $statement = $this->pdo->prepare('SELECT COUNT(*) as `num` FROM `'.$tableName.'`;'); 317 318 //debuq 319 $this->log_query ($statement); 320 $statement->execute(); 321 322 return $statement->fetchColumn(); 323 } 324 325 /** 326 * Get a quick number of objects in a table for filtered field 327 * 328 * @access public 329 * @param mixed $tableName 330 * @param mixed $method 331 * @param boolean $like (default: false) 332 * @param mixed $value 333 * @return void 334 */ 335 public function numObjectsFilter($tableName, $method, $value, $like = false) { 336 if (!$this->isConnected()) $this->connect(); 337 338 $like === true ? $operator = "LIKE" : $operator = "="; 339 340 $tableName = $this->escape($tableName); 341 $statement = $this->pdo->prepare('SELECT COUNT(*) as `num` FROM `'.$tableName.'` where `'.$method.'` '.$operator.' ?;'); 342 343 //debuq 344 $this->log_query ($statement, (array) $value); 345 $statement->execute(array($value)); 346 347 return $statement->fetchColumn(); 348 } 349 350 /** 351 * Update an object in a table with values given 352 * 353 * Note: the id of the object is assumed to be in. 354 * 355 * @access public 356 * @param mixed $tableName 357 * @param mixed $obj 358 * @param string $primarykey (default: 'id') 359 * @param mixed $primarykey2 (default: null) 360 * @return void 361 */ 362 public function updateObject($tableName, $obj, $primarykey = 'id', $primarykey2 = null) { 363 if (!$this->isConnected()) $this->connect(); 364 365 $obj = (array)$obj; 366 367 //we cannot update an object without an id specified so quit 368 if (!isset($obj[$primarykey])) { 369 throw new Exception('Missing primary key'); 370 return false; 371 } 372 373 $tableName = $this->escape($tableName); 374 375 //get the objects id from the provided object and knock it off from the object so we dont try to update it 376 $objId[] = $obj[$primarykey]; 377 unset($obj[$primarykey]); 378 379 //secondary primary key? 380 if(!is_null($primarykey2)) { 381 $objId[] = $obj[$primarykey2]; 382 unset($obj[$primarykey2]); 383 } 384 385 //TODO: validate given object parameters with that of the table (this validates parameters names) 386 387 //formulate an update statement based on the object parameters 388 $objParams = array_keys($obj); 389 390 $preparedParamArr = array(); 391 foreach ($objParams as $objParam) { 392 $preparedParamArr[] = '`' . $this->escape($objParam) . '`=?'; 393 } 394 395 // exit on no parameters 396 if(sizeof($preparedParamArr)==0) { 397 throw new Exception('No values to update'); 398 return false; 399 } 400 401 $preparedParamStr = implode(',', $preparedParamArr); 402 403 //primary key 2? 404 if(!is_null($primarykey2)) 405 $statement = $this->pdo->prepare('UPDATE `' . $tableName . '` SET ' . $preparedParamStr . ' WHERE `' . $primarykey . '`=? AND `' . $primarykey2 . '`=?;'); 406 else 407 $statement = $this->pdo->prepare('UPDATE `' . $tableName . '` SET ' . $preparedParamStr . ' WHERE `' . $primarykey . '`=?;'); 408 409 //merge the parameters and values 410 $paramValues = array_merge(array_values($obj), $objId); 411 412 //debuq 413 $this->log_query ($statement, $paramValues); 414 //run the update on the object 415 return $statement->execute($paramValues); 416 } 417 418 /** 419 * Update multiple objects at once. 420 * 421 * @access public 422 * @param string $tableName 423 * @param array $ids 424 * @param array $values 425 * @return void 426 */ 427 public function updateMultipleObjects($tableName, $ids, $values) { 428 $tableName = $this->escape($tableName); 429 //set ids 430 $num = count($ids); 431 $idParts = array_fill(0, $num, '`id`=?'); 432 //set values 433 $objParams = array_keys($values); 434 $preparedParamArr = array(); 435 foreach ($objParams as $objParam) { 436 $preparedParamArr[] = '`' . $this->escape($objParam) . '`=?'; 437 } 438 //set values 439 $all_values = array_merge(array_values($values),$ids); 440 //execute 441 return $this->runQuery('UPDATE `'.$tableName.'` SET '.implode(',', $preparedParamArr).' WHERE '.implode(' OR ', $idParts), $all_values); 442 } 443 444 /** 445 * Insert an object into a table 446 * Note: an id field is ignored if specified. 447 * 448 * @access public 449 * @param string $tableName 450 * @param object|array $obj 451 * @param bool $raw (default: false) 452 * @param bool $replace (default: false) 453 * @param bool $ignoreId (default: true) 454 * @return void 455 */ 456 public function insertObject($tableName, $obj, $raw = false, $replace = false, $ignoreId = true) { 457 if (!$this->isConnected()) $this->connect(); 458 459 $obj = (array)$obj; 460 461 $tableName = $this->escape($tableName); 462 463 if (!$raw && array_key_exists('id', $obj) && $ignoreId) { 464 unset($obj['id']); 465 } 466 467 if (count($obj)<1) { 468 return true; 469 } 470 471 //formulate an update statement based on the object parameters 472 $objValues = array_values($obj); 473 474 $preparedParamsArr = array(); 475 foreach ($obj as $key => $value) { 476 $preparedParamsArr[] = '`' . $this->escape($key) . '`'; 477 } 478 479 $preparedParamsStr = implode(', ', $preparedParamsArr); 480 $preparedValuesStr = implode(', ', array_fill(0, count($objValues), '?')); 481 482 if ($replace) { 483 $statement = $this->pdo->prepare('REPLACE INTO `' . $tableName . '` (' . $preparedParamsStr . ') VALUES (' . $preparedValuesStr . ');'); 484 } else { 485 $statement = $this->pdo->prepare('INSERT INTO `' . $tableName . '` (' . $preparedParamsStr . ') VALUES (' . $preparedValuesStr . ');'); 486 } 487 488 //run the update on the object 489 if (!$statement->execute($objValues)) { 490 $errObj = $statement->errorInfo(); 491 492 //return false; 493 throw new Exception($errObj[2]); 494 } 495 496 return $this->pdo->lastInsertId(); 497 } 498 499 500 /** 501 * Check if an object exists. 502 * 503 * @access public 504 * @param string $tableName 505 * @param string $query (default: null) 506 * @param array $values (default: array()) 507 * @param mixed $id (default: null) 508 * @return void 509 */ 510 public function objectExists($tableName, $query = null, $values = array(), $id = null) { 511 return is_object($this->getObject($tableName, $id)); 512 } 513 514 /** 515 * Get a filtered list of objects from the database. 516 * 517 * @access public 518 * @param string $tableName 519 * @param string $sortField (default: 'id') 520 * @param bool $sortAsc (default: true) 521 * @param mixed $numRecords (default: null) 522 * @param int $offset (default: 0) 523 * @param string $class (default: 'stdClass') 524 * @return void 525 */ 526 public function getObjects($tableName, $sortField = 'id', $sortAsc = true, $numRecords = null, $offset = 0, $class = 'stdClass') { 527 if (!$this->isConnected()) $this->connect(); 528 529 $sortStr = ''; 530 if (!$sortAsc) { 531 $sortStr = 'DESC'; 532 } 533 534 // change sort fields for vlans and vrfs. ugly :/ 535 if ($tableName=='vlans' && $sortField=='id') { $sortField = "vlanId"; } 536 if ($tableName=='vrf' && $sortField=='id') { $sortField = "vrfId"; } 537 538 //we should escape all of the params that we need to 539 $tableName = $this->escape($tableName); 540 $sortField = $this->escape($sortField); 541 542 if ($numRecords === null) { 543 //get all (no limit) 544 $statement = $this->pdo->query('SELECT * FROM `'.$tableName.'` ORDER BY `'.$sortField.'` '.$sortStr.';'); 545 } else { 546 //get a limited range of objects 547 $statement = $this->pdo->query('SELECT * FROM `'.$tableName.'` ORDER BY `'.$sortField.'` '.$sortStr.' LIMIT '.$numRecords.' OFFSET '.$offset.';'); 548 } 549 550 $results = array(); 551 552 if (is_object($statement)) { 553 $results = $statement->fetchAll($class == 'stdClass' ? PDO::FETCH_CLASS : PDO::FETCH_NUM); 554 } 555 556 return $results; 557 } 558 559 560 /** 561 * use this function to conserve memory and read rows one by one rather than reading all of them 562 * 563 * @access public 564 * @param mixed $query (default: null) 565 * @param array $values (default: array()) 566 * @param mixed $callback (default: null) 567 * @return void 568 */ 569 public function getObjectsQueryIncremental($query = null, $values = array(), $callback = null) { 570 if (!$this->isConnected()) $this->connect(); 571 572 $statement = $this->pdo->prepare($query); 573 574 //debuq 575 $this->log_query ($statement, $values); 576 $statement->execute((array)$values); 577 578 if (is_object($statement)) { 579 if ($callback) { 580 while ($newObj = $statement->fetchObject('stdClass')) { 581 if ($callback($newObj)===false) { 582 return false; 583 } 584 } 585 } 586 } 587 588 return true; 589 } 590 591 592 /** 593 * Get all objects matching values 594 * 595 * @access public 596 * @param mixed $query (default: null) 597 * @param array $values (default: array()) 598 * @param string $class (default: 'stdClass') 599 * @return void 600 */ 601 public function getObjectsQuery($query = null, $values = array(), $class = 'stdClass') { 602 if (!$this->isConnected()) $this->connect(); 603 604 $statement = $this->pdo->prepare($query); 605 606 //debug 607 $this->log_query ($statement, $values); 608 $statement->execute((array)$values); 609 610 $results = array(); 611 612 if (is_object($statement)) { 613 $results = $statement->fetchAll($class == 'stdClass' ? PDO::FETCH_CLASS : PDO::FETCH_NUM); 614 } 615 616 return $results; 617 } 618 619 /** 620 * Get all objects groped by $groupField, array of (id,count(*)) pairs 621 * 622 * @param string $tableName 623 * @param string $groupField 624 * @return array 625 */ 626 public function getGroupBy($tableName, $groupField = 'id') { 627 if (!$this->isConnected()) $this->connect(); 628 629 $statement = $this->pdo->prepare("SELECT `$groupField`,COUNT(*) FROM `$tableName` GROUP BY `$groupField`"); 630 631 //debug 632 $this->log_query ($statement, array()); 633 $statement->execute(); 634 635 $results = array(); 636 637 if (is_object($statement)) { 638 $results = $statement->fetchAll(PDO::FETCH_KEY_PAIR); 639 } 640 641 return $results; 642 } 643 644 /** 645 * Get a single object from the database 646 * 647 * @access public 648 * @param mixed $tableName 649 * @param mixed $id (default: null) 650 * @param string $class (default: 'stdClass') 651 * @return void 652 */ 653 public function getObject($tableName, $id = null, $class = 'stdClass') { 654 if (!$this->isConnected()) $this->connect(); 655 $id = intval($id); 656 657 //has a custom query been provided? 658 $tableName = $this->escape($tableName); 659 660 //prepare a statement to get a single object from the database 661 if ($id !== null) { 662 $statement = $this->pdo->prepare('SELECT * FROM `'.$tableName.'` WHERE `id`=? LIMIT 1;'); 663 $statement->bindParam(1, $id, \PDO::PARAM_INT); 664 } else { 665 $statement = $this->pdo->prepare('SELECT * FROM `'.$tableName.'` LIMIT 1;'); 666 } 667 668 //debuq 669 $this->log_query ($statement, array($id)); 670 $statement->execute(); 671 672 //we can then extract the single object (if we have a result) 673 $resultObj = $statement->fetchObject($class); 674 675 if ($resultObj === false) { 676 return null; 677 } else { 678 return $resultObj; 679 } 680 } 681 682 /** 683 * Fetches single object from provided query 684 * 685 * @access public 686 * @param mixed $query (default: null) 687 * @param array $values (default: array()) 688 * @param string $class (default: 'stdClass') 689 * @return void 690 */ 691 public function getObjectQuery($query = null, $values = array(), $class = 'stdClass') { 692 if (!$this->isConnected()) $this->connect(); 693 694 $statement = $this->pdo->prepare($query); 695 //debuq 696 $this->log_query ($statement, $values); 697 $statement->execute((array)$values); 698 699 $resultObj = $statement->fetchObject($class); 700 701 if ($resultObj === false) { 702 return null; 703 } else { 704 return $resultObj; 705 } 706 } 707 708 /** 709 * Get single value 710 * 711 * @access public 712 * @param mixed $query (default: null) 713 * @param array $values (default: array()) 714 * @param string $class (default: 'stdClass') 715 * @return void 716 */ 717 public function getValueQuery($query = null, $values = array(), $class = 'stdClass') { 718 $obj = $this->getObjectQuery($query, $values, $class); 719 720 if (is_object($obj)) { 721 $obj = (array)$obj; 722 return reset($obj); 723 } else { 724 return null; 725 } 726 } 727 728 /** 729 * Escape $result_fields parameter 730 * 731 * @access public 732 * @param string|array $result_fields 733 * @return string 734 */ 735 public function escape_result_fields($result_fields) { 736 if (empty($result_fields)) return "*"; 737 738 if (is_array($result_fields)) { 739 foreach ($result_fields as $i => $f) $result_fields[$i] = "`$f`"; 740 $result_fields = implode(',', $result_fields); 741 } 742 return $result_fields; 743 } 744 745 /** 746 * Searches for object in database 747 * 748 * @access public 749 * @param mixed $table 750 * @param mixed $field 751 * @param mixed $value 752 * @param string $sortField (default: 'id') 753 * @param bool $sortAsc (default: true) 754 * @param bool $like (default: false) 755 * @param bool $negate (default: false) 756 * @param string|array $result_fields (default: "*") 757 * @return void 758 */ 759 public function findObjects($table, $field, $value, $sortField = 'id', $sortAsc = true, $like = false, $negate = false, $result_fields = "*") { 760 $table = $this->escape($table); 761 $field = $this->escape($field); 762 $sortField = $this->escape($sortField); 763 $like === true ? $operator = "LIKE" : $operator = "="; 764 $negate === true ? $negate_operator = "NOT " : $negate_operator = ""; 765 766 $result_fields = $this->escape_result_fields($result_fields); 767 768 // change sort fields for vlans and vrfs. ugly :/ 769 if ($table=='vlans' && $sortField=='id') { $sortField = "vlanId"; } 770 if ($table=='vrf' && $sortField=='id') { $sortField = "vrfId"; } 771 772 // subnets 773 if ($table=='subnets' && $sortField=='subnet') { 774 return $this->getObjectsQuery('SELECT '.$result_fields.' FROM `' . $table . '` WHERE `'. $field .'`'.$negate_operator. $operator .'? ORDER BY LPAD(`subnet`,39,0) ' . ($sortAsc ? '' : 'DESC') . ';', array($value)); 775 } else { 776 return $this->getObjectsQuery('SELECT '.$result_fields.' FROM `' . $table . '` WHERE `'. $field .'`'.$negate_operator. $operator .'? ORDER BY `'.$sortField.'` ' . ($sortAsc ? '' : 'DESC') . ';', array($value)); 777 } 778 } 779 780 /** 781 * Searches for single object. 782 * 783 * @access public 784 * @param mixed $table 785 * @param mixed $field 786 * @param mixed $value 787 * @return void 788 */ 789 public function findObject($table, $field, $value) { 790 $table = $this->escape($table); 791 $field = $this->escape($field); 792 793 return $this->getObjectQuery('SELECT * FROM `' . $table . '` WHERE `' . $field . '` = ? LIMIT 1;', array($value)); 794 } 795 796 /** 797 * Get list of items. 798 * 799 * @access public 800 * @param mixed $query (default: null) 801 * @param array $values (default: array()) 802 * @param string $class (default: 'stdClass') 803 * @return void 804 */ 805 public function getList($query = null, $values = array(), $class = 'stdClass') { 806 $objs = $this->getObjectsQuery($query, $values, $class); 807 808 $list = array(); 809 810 if (!is_array($objs)) 811 return $list; 812 813 foreach ($objs as $obj) { 814 $columns = array_values((array)$obj); 815 $list[] = $columns[0]; 816 } 817 818 return $list; 819 } 820 821 /** 822 * Delete an object from the database 823 * 824 * @param {string} table name 825 * @param {int} object id 826 * @return {boolean} success 827 */ 828 public function deleteObject($tableName, $id) { 829 $tableName = $this->escape($tableName); 830 831 return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `id`=?;', array($id)); 832 } 833 834 /** 835 * Delete a list of objects from the database 836 * 837 * @param {string} table name 838 * @param {array} list of ids 839 * @return {boolean} success 840 */ 841 public function deleteObjects($tableName, $ids) { 842 $tableName = $this->escape($tableName); 843 $num = count($ids); 844 $idParts = array_fill(0, $num, '`id`=?'); 845 846 return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE ' . implode(' OR ', $idParts), $ids); 847 } 848 849 /** 850 * Delete a list of objects from the database based on identifier 851 * 852 * @method deleteObjects 853 * @param string $tableName 854 * @param string $identifier 855 * @param mixed $ids 856 * @return bool 857 */ 858 public function deleteObjectsByIdentifier($tableName, $identifier = "id", $id = 0) { 859 $tableName = $this->escape($tableName); 860 $identifier = $this->escape($identifier); 861 862 return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$identifier.'` = ?', $id); 863 } 864 865 /** 866 * Delete specified row 867 * 868 * @access public 869 * @param {string} $tableName 870 * @param {string $field 871 * @param {string $value 872 * @return void 873 */ 874 public function deleteRow($tableName, $field, $value, $field2=null, $value2 = null) { 875 $tableName = $this->escape($tableName); 876 $field = $this->escape($field); 877 $field2 = $this->escape($field2); 878 879 //multiple 880 if(!empty($field2)) 881 return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$field.'`=? and `'.$field2.'`=?;', array($value, $value2)); 882 else 883 return $this->runQuery('DELETE FROM `'.$tableName.'` WHERE `'.$field.'`=?;', array($value)); 884 } 885 886 /** 887 * truncate specified table 888 * 889 * @access public 890 * @param {string} $tableName 891 * @return void 892 */ 893 public function emptyTable($tableName) { 894 //escape talbe name 895 $tableName = $this->escape($tableName); 896 //execute 897 return $this->runQuery('TRUNCATE TABLE `'.$tableName.'`;'); 898 } 899 900 /** 901 * Begin SQL Transaction 902 * 903 * @access public 904 * @return bool 905 */ 906 public function beginTransaction() { 907 return $this->pdo->beginTransaction(); 908 } 909 910 /** 911 * Commit SQL Transaction 912 * 913 * @access public 914 * @return bool 915 */ 916 public function commit() { 917 return $this->pdo->commit(); 918 } 919 920 /** 921 * Commit SQL Transaction 922 * 923 * @access public 924 * @return bool 925 */ 926 public function rollBack() { 927 return $this->pdo->rollBack(); 928 } 929} 930 931 932/** 933* 934* PDO class wrapper 935* Database class 936* 937*/ 938class Database_PDO extends DB { 939 940 941 /** 942 * SSL options for db connection 943 * 944 * (default value: array ()) 945 * 946 * @var array 947 * @access protected 948 */ 949 protected $pdo_ssl_opts = array (); 950 951 /** 952 * flag if installation is happenig! 953 * 954 * (default value: false) 955 * 956 * @var bool 957 * @access public 958 */ 959 public $install = false; 960 961 /** 962 * Debugging flag 963 * 964 * (default value: false) 965 * 966 * @var bool 967 * @access protected 968 */ 969 protected $debug = false; 970 971 972 973 974 975 976 /** 977 * __construct function. 978 * 979 * @access public 980 * @param mixed $host (default: null) 981 * @param mixed $port (default: null) 982 * @param mixed $dbname (default: null) 983 * @param mixed $username (default: null) 984 * @param mixed $password (default: null) 985 * @param mixed $charset (default: null) 986 */ 987 public function __construct($username=null, $password=null, $host=null, $port=null, $dbname=null, $charset=null) { 988 # set parameters 989 $this->set_db_params (); 990 # rewrite user/pass if requested - for installation 991 $username==null ? : $this->username = $username; 992 $password==null ? : $this->password = $password; 993 $host==null ? : $this->host = $host; 994 $port==null ? : $this->port = $port; 995 $dbname==null ? : $this->dbname = $dbname; 996 997 # construct 998 parent::__construct($this->username, $this->password, $this->charset, $this->ssl); 999 } 1000 1001 1002 /** 1003 * get database parameters from config.php 1004 * 1005 * @access private 1006 * @return void 1007 */ 1008 private function set_db_params () { 1009 # use config file 1010 $db = Config::get('db'); 1011 1012 # set 1013 $this->host = $db['host']; 1014 $this->port = $db['port']; 1015 $this->username = $db['user']; 1016 $this->password = $db['pass']; 1017 $this->dbname = $db['name']; 1018 1019 $this->ssl = false; 1020 if (@$db['ssl']===true) { 1021 1022 $this->pdo_ssl_opts = array ( 1023 'ssl_key' => PDO::MYSQL_ATTR_SSL_KEY, 1024 'ssl_cert' => PDO::MYSQL_ATTR_SSL_CERT, 1025 'ssl_ca' => PDO::MYSQL_ATTR_SSL_CA, 1026 'ssl_cipher' => PDO::MYSQL_ATTR_SSL_CIPHER, 1027 'ssl_capath' => PDO::MYSQL_ATTR_SSL_CAPATH 1028 ); 1029 1030 $this->ssl = array(); 1031 1032 if ($db['ssl_verify']===false) { 1033 $this->ssl[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false; 1034 } 1035 1036 foreach ($this->pdo_ssl_opts as $key => $pdoopt) { 1037 if ($db[$key]) { 1038 $this->ssl[$pdoopt] = $db[$key]; 1039 } 1040 } 1041 1042 } 1043 1044 } 1045 1046 /** 1047 * connect function. 1048 * 1049 * @access public 1050 * @return void 1051 */ 1052 public function connect() { 1053 parent::connect(); 1054 //@$this->pdo->query('SET NAMES \'' . $this->charset . '\';'); 1055 } 1056 1057 /** 1058 * makeDsn function 1059 * 1060 * @access protected 1061 * @return void 1062 */ 1063 protected function makeDsn() { 1064 # for installation 1065 if($this->install) { return 'mysql:host=' . $this->host . ';port=' . $this->port . ';charset=' . $this->charset; } 1066 else { return 'mysql:host=' . $this->host . ';port=' . $this->port . ';dbname=' . $this->dbname . ';charset=' . $this->charset; } 1067 } 1068 1069 /** 1070 * more generic static useful methods 1071 * 1072 * @access public 1073 * @return void 1074 */ 1075 public function getColumnInfo() { 1076 $columns = $this->getObjectsQuery(" 1077 SELECT `table_name`, `column_name`, `column_default`, `is_nullable`, `data_type`,`column_key`, `extra` 1078 FROM `columns` 1079 WHERE `table_schema`='" . $this->dbname . "'; 1080 "); 1081 1082 $columnsByTable = array(); 1083 1084 if (!is_array($columns)) 1085 return $columnsByTable; 1086 1087 foreach ($columns as $column) { 1088 if (!isset($columnsByTable[$column->table_name])) { 1089 $columnsByTable[$column->table_name] = array(); 1090 } 1091 1092 $columnsByTable[$column->table_name][$column->column_name] = $column; 1093 } 1094 1095 return $columnsByTable; 1096 } 1097 1098 /** 1099 * Returns field info. 1100 * 1101 * @access public 1102 * @param bool $tableName (default: false) 1103 * @param bool $field (default: false) 1104 * @return void|object 1105 */ 1106 public function getFieldInfo ($tableName = false, $field = false) { 1107 //escape 1108 $tableName = $this->escape($tableName); 1109 $field = $this->escape($field); 1110 // fetch and return 1111 return $this->getObjectQuery("SHOW FIELDS FROM `$tableName` where Field = ?", array($field)); 1112 1113 } 1114 1115 /** 1116 * getForeignKeyInfo function. 1117 * 1118 * @access public 1119 * @return void 1120 */ 1121 public function getForeignKeyInfo() { 1122 $foreignLinks = $this->getObjectsQuery(" 1123 SELECT i.`table_name`, k.`column_name`, i.`constraint_type`, i.`constraint_name`, k.`referenced_table_name`, k.`referenced_column_name` 1124 FROM `table_constraints` i 1125 LEFT JOIN `key_column_usage` k ON i.`constraint_name` = k.`constraint_name` 1126 WHERE i.`constraint_type` = 'FOREIGN KEY' AND i.`table_schema`='" . $this->dbname . "'; 1127 "); 1128 1129 $foreignLinksByTable = array(); 1130 $foreignLinksByRefTable = array(); 1131 1132 if (!is_array($foreignLinks)) 1133 return array($foreignLinksByTable, $foreignLinksByRefTable); 1134 1135 foreach ($foreignLinks as $foreignLink) { 1136 if (!isset($foreignLinksByTable[$foreignLink->table_name])) { 1137 $foreignLinksByTable[$foreignLink->table_name] = array(); 1138 } 1139 1140 if (!isset($foreignLinksByRefTable[$foreignLink->referenced_table_name])) { 1141 $foreignLinksByRefTable[$foreignLink->referenced_table_name] = array(); 1142 } 1143 1144 $foreignLinksByTable[$foreignLink->table_name][$foreignLink->column_name] = $foreignLink; 1145 $foreignLinksByRefTable[$foreignLink->referenced_table_name][$foreignLink->table_name] = $foreignLink; 1146 } 1147 1148 return array($foreignLinksByTable, $foreignLinksByRefTable); 1149 } 1150} 1151