1<?php 2/* 3** Zabbix 4** Copyright (C) 2001-2021 Zabbix SIA 5** 6** This program is free software; you can redistribute it and/or modify 7** it under the terms of the GNU General Public License as published by 8** the Free Software Foundation; either version 2 of the License, or 9** (at your option) any later version. 10** 11** This program is distributed in the hope that it will be useful, 12** but WITHOUT ANY WARRANTY; without even the implied warranty of 13** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 14** GNU General Public License for more details. 15** 16** You should have received a copy of the GNU General Public License 17** along with this program; if not, write to the Free Software 18** Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. 19**/ 20 21 22class DB { 23 24 const SCHEMA_FILE = 'schema.inc.php'; 25 26 const DBEXECUTE_ERROR = 1; 27 const RESERVEIDS_ERROR = 2; 28 const SCHEMA_ERROR = 3; 29 const INPUT_ERROR = 4; 30 31 const TABLE_TYPE_CONFIG = 1; 32 const TABLE_TYPE_HISTORY = 2; 33 34 const FIELD_TYPE_INT = 'int'; 35 const FIELD_TYPE_CHAR = 'char'; 36 const FIELD_TYPE_ID = 'id'; 37 const FIELD_TYPE_FLOAT = 'float'; 38 const FIELD_TYPE_UINT = 'uint'; 39 const FIELD_TYPE_BLOB = 'blob'; 40 const FIELD_TYPE_TEXT = 'text'; 41 42 private static $schema = null; 43 44 /** 45 * @var DbBackend 46 */ 47 private static $dbBackend; 48 49 /** 50 * Get necessary DB class. 51 * 52 * @return DbBackend 53 */ 54 public static function getDbBackend() { 55 global $DB; 56 57 if (!self::$dbBackend) { 58 switch ($DB['TYPE']) { 59 case ZBX_DB_MYSQL: 60 self::$dbBackend = new MysqlDbBackend(); 61 break; 62 case ZBX_DB_POSTGRESQL: 63 self::$dbBackend = new PostgresqlDbBackend(); 64 break; 65 case ZBX_DB_ORACLE: 66 self::$dbBackend = new OracleDbBackend(); 67 break; 68 case ZBX_DB_DB2: 69 self::$dbBackend = new Db2DbBackend(); 70 break; 71 } 72 } 73 74 return self::$dbBackend; 75 } 76 77 private static function exception($code, $error) { 78 throw new DBException($error, $code); 79 } 80 81 /** 82 * Reserve ids for primary key of passed table. 83 * If record for table does not exist or value is out of range, ids record is created 84 * using maximum id from table or minimum allowed value. 85 * 86 * @throw APIException 87 * 88 * @static 89 * 90 * @param string $table table name 91 * @param int $count number of ids to reserve 92 * 93 * @return string 94 */ 95 public static function reserveIds($table, $count) { 96 global $DB; 97 98 $tableSchema = self::getSchema($table); 99 $id_name = $tableSchema['key']; 100 101 $sql = 'SELECT nextid'. 102 ' FROM ids'. 103 ' WHERE table_name='.zbx_dbstr($table). 104 ' AND field_name='.zbx_dbstr($id_name). 105 ' FOR UPDATE'; 106 107 $res = DBfetch(DBselect($sql)); 108 109 if ($res) { 110 $maxNextId = bcadd($res['nextid'], $count, 0); 111 112 if (bccomp($maxNextId, ZBX_DB_MAX_ID) == 1) { 113 $nextid = self::refreshIds($table, $count); 114 } 115 else { 116 $sql = 'UPDATE ids'. 117 ' SET nextid='.$maxNextId. 118 ' WHERE table_name='.zbx_dbstr($table). 119 ' AND field_name='.zbx_dbstr($id_name); 120 121 if (!DBexecute($sql)) { 122 self::exception(self::DBEXECUTE_ERROR, 'DBEXECUTE_ERROR'); 123 } 124 125 $nextid = bcadd($res['nextid'], 1, 0); 126 } 127 } 128 129 /* 130 * Detect either the query is executable at all? If query is valid and schema is correct but query still cannot 131 * be executed, then there is a good chance that previous transaction has left row level lock unreleased or it 132 * is still running. In such a case execution must be stopped, otherwise it will call self::refreshIds method. 133 */ 134 elseif (!DBexecute($sql)) { 135 self::exception(self::DBEXECUTE_ERROR, 136 _('Your database is not working properly. Please wait a few minutes and try to repeat this action. If the problem still persists, please contact system administrator. The problem might be caused by long running transaction or row level lock accomplished by your database management system.') 137 ); 138 } 139 // If query is executable, but still returns false, only then call refreshIds. 140 else { 141 $nextid = self::refreshIds($table, $count); 142 } 143 144 return $nextid; 145 } 146 147 /** 148 * Refresh id record for given table. 149 * Record is deleted and then created again with value of maximum id from table or minimum allowed. 150 * 151 * @throw APIException 152 * 153 * @static 154 * 155 * @param string $table table name 156 * @param int $count number of ids to reserve 157 * 158 * @return string 159 */ 160 private static function refreshIds($table, $count) { 161 $tableSchema = self::getSchema($table); 162 $id_name = $tableSchema['key']; 163 164 // when we reach the maximum ID, we try to refresh them to check if any IDs have been freed 165 $sql = 'DELETE FROM ids WHERE table_name='.zbx_dbstr($table).' AND field_name='.zbx_dbstr($id_name); 166 167 if (!DBexecute($sql)) { 168 self::exception(self::DBEXECUTE_ERROR, 'DBEXECUTE_ERROR'); 169 } 170 171 $row = DBfetch(DBselect('SELECT MAX('.$id_name.') AS id FROM '.$table)); 172 173 $nextid = ($row && $row['id']) ? $row['id'] : 0; 174 175 $maxNextId = bcadd($nextid, $count, 0); 176 177 if (bccomp($maxNextId, ZBX_DB_MAX_ID) == 1) { 178 self::exception( 179 self::RESERVEIDS_ERROR, __METHOD__.' ID greater than maximum allowed for table "'.$table.'"' 180 ); 181 } 182 183 $sql = 'INSERT INTO ids (table_name,field_name,nextid)'. 184 ' VALUES ('.zbx_dbstr($table).','.zbx_dbstr($id_name).','.$maxNextId.')'; 185 186 if (!DBexecute($sql)) { 187 self::exception(self::DBEXECUTE_ERROR, 'DBEXECUTE_ERROR'); 188 } 189 190 $nextid = bcadd($nextid, 1, 0); 191 192 return $nextid; 193 } 194 195 /** 196 * Returns the array describing the database schema. 197 * 198 * If the $table parameter is passed, the method will return the schema for the given table, 199 * otherwise - for the whole database. 200 * 201 * @static 202 * 203 * @throws APIException if the given table does not exist 204 * 205 * @param string $table 206 * 207 * @return array 208 */ 209 public static function getSchema($table = null) { 210 if (is_null(self::$schema)) { 211 self::$schema = include(dirname(__FILE__).'/../../'.self::SCHEMA_FILE); 212 } 213 214 if (is_null($table)) { 215 return self::$schema; 216 } 217 elseif (isset(self::$schema[$table])) { 218 return self::$schema[$table]; 219 } 220 else { 221 self::exception(self::SCHEMA_ERROR, _s('Table "%1$s" does not exist.', $table)); 222 } 223 } 224 225 /** 226 * Returns the names of the fields that are used as the primary key of the table. 227 * 228 * @static 229 * 230 * @param string $tableName 231 * 232 * @return string|array 233 */ 234 protected static function getPk($tableName) { 235 $schema = self::getSchema($tableName); 236 237 return $schema['key']; 238 } 239 240 /** 241 * Returns true if the table $tableName has the $fieldName field. 242 * 243 * @static 244 * 245 * @param string $tableName 246 * @param string $fieldName 247 * 248 * @return bool 249 */ 250 public static function hasField($tableName, $fieldName) { 251 $schema = self::getSchema($tableName); 252 253 return isset($schema['fields'][$fieldName]); 254 } 255 256 /** 257 * Returns length of the field. 258 * 259 * @static 260 * 261 * @param string $table_name 262 * @param string $field_name 263 * 264 * @return int 265 */ 266 public static function getFieldLength($table_name, $field_name) { 267 global $DB; 268 269 $schema = self::getSchema($table_name); 270 271 if ($schema['fields'][$field_name]['type'] == self::FIELD_TYPE_TEXT) { 272 return ($DB['TYPE'] == ZBX_DB_DB2 || $DB['TYPE'] == ZBX_DB_ORACLE) ? 2048 : 65536; 273 } 274 275 return $schema['fields'][$field_name]['length']; 276 } 277 278 public static function getDefaults($table) { 279 $table = self::getSchema($table); 280 281 $defaults = []; 282 foreach ($table['fields'] as $name => $field) { 283 if (isset($field['default'])) { 284 $defaults[$name] = $field['default']; 285 } 286 } 287 return $defaults; 288 } 289 290 /** 291 * Returns the default value of the given field. 292 * 293 * @param string $table name of the table 294 * @param string $field name of the field 295 * 296 * @return string|null 297 */ 298 public static function getDefault($table, $field) { 299 $table = self::getSchema($table); 300 $field = $table['fields'][$field]; 301 302 return isset($field['default']) ? $field['default'] : null; 303 } 304 305 public static function checkValueTypes($tableSchema, &$values) { 306 global $DB; 307 308 foreach ($values as $field => $value) { 309 if (!isset($tableSchema['fields'][$field])) { 310 unset($values[$field]); 311 continue; 312 } 313 314 if (isset($tableSchema['fields'][$field]['ref_table'])) { 315 if ($tableSchema['fields'][$field]['null']) { 316 $values[$field] = ($values[$field] == '0') ? NULL : $values[$field]; 317 } 318 } 319 320 if (is_null($values[$field])) { 321 if ($tableSchema['fields'][$field]['null']) { 322 $values[$field] = 'NULL'; 323 } 324 elseif (isset($tableSchema['fields'][$field]['default'])) { 325 $values[$field] = zbx_dbstr($tableSchema['fields'][$field]['default']); 326 } 327 else { 328 self::exception(self::DBEXECUTE_ERROR, 329 _s('Field "%1$s" cannot be set to NULL.', $field) 330 ); 331 } 332 } 333 else { 334 switch ($tableSchema['fields'][$field]['type']) { 335 case self::FIELD_TYPE_CHAR: 336 $length = mb_strlen($values[$field]); 337 338 if ($length > $tableSchema['fields'][$field]['length']) { 339 self::exception(self::SCHEMA_ERROR, _s('Value "%1$s" is too long for field "%2$s" - %3$d characters. Allowed length is %4$d characters.', 340 $values[$field], $field, $length, $tableSchema['fields'][$field]['length'])); 341 } 342 $values[$field] = zbx_dbstr($values[$field]); 343 break; 344 case self::FIELD_TYPE_ID: 345 case self::FIELD_TYPE_UINT: 346 if (!zbx_ctype_digit($values[$field])) { 347 self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for unsigned int field "%2$s".', $values[$field], $field)); 348 } 349 $values[$field] = zbx_dbstr($values[$field]); 350 break; 351 case self::FIELD_TYPE_INT: 352 if (!zbx_is_int($values[$field])) { 353 self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for int field "%2$s".', $values[$field], $field)); 354 } 355 $values[$field] = zbx_dbstr($values[$field]); 356 break; 357 case self::FIELD_TYPE_FLOAT: 358 if (!is_numeric($values[$field])) { 359 self::exception(self::DBEXECUTE_ERROR, _s('Incorrect value "%1$s" for float field "%2$s".', $values[$field], $field)); 360 } 361 $values[$field] = zbx_dbstr($values[$field]); 362 break; 363 case self::FIELD_TYPE_TEXT: 364 if ($DB['TYPE'] == ZBX_DB_DB2 || $DB['TYPE'] == ZBX_DB_ORACLE) { 365 $length = mb_strlen($values[$field]); 366 367 if ($length > 2048) { 368 self::exception(self::SCHEMA_ERROR, _s('Value "%1$s" is too long for field "%2$s" - %3$d characters. Allowed length is %4$d characters.', 369 $values[$field], $field, $length, 2048)); 370 } 371 } 372 $values[$field] = zbx_dbstr($values[$field]); 373 break; 374 } 375 } 376 } 377 } 378 379 /** 380 * Returns the records that match the given criteria. 381 * 382 * @static 383 * 384 * @param string $tableName 385 * @param array $criteria An associative array of field-value pairs, where value can be either a single value 386 * or an array (IN) 387 * 388 * @return array 389 */ 390 public static function find($tableName, array $criteria = []) { 391 // build the WHERE part 392 $sqlWhere = []; 393 foreach ($criteria as $field => $value) { 394 // check if the table has this field 395 if (!self::hasField($tableName, $field)) { 396 self::exception(self::DBEXECUTE_ERROR, _s('Table "%1$s" doesn\'t have a field named "%2$s".', $tableName, $field)); 397 } 398 399 $sqlWhere[] = dbConditionString($field, zbx_toArray($value)); 400 } 401 402 // build query 403 $sql = 'SELECT * FROM '.$tableName; 404 if ($sqlWhere) { 405 $sql .= ' WHERE '.implode(' AND ', $sqlWhere); 406 } 407 408 return DBfetchArray(DBSelect($sql)); 409 } 410 411 /** 412 * Insert data into DB. 413 * 414 * @param string $table 415 * @param array $values pair of fieldname => fieldvalue 416 * @param bool $getids 417 * 418 * @return array an array of ids with the keys preserved 419 */ 420 public static function insert($table, $values, $getids = true) { 421 $table_schema = self::getSchema($table); 422 $fields = []; 423 424 foreach ($values as $key => $row) { 425 $fields += array_diff_key($row, $fields); 426 } 427 428 $fields = array_intersect_key($fields, $table_schema['fields']); 429 430 foreach ($fields as $field => &$value) { 431 $value = array_key_exists('default', $table_schema['fields'][$field]) 432 ? $table_schema['fields'][$field]['default'] 433 : null; 434 } 435 unset($value); 436 437 foreach ($values as $key => &$row) { 438 $row += $fields; 439 440 $ordered_row = []; 441 foreach ($fields as $field => $foo) { 442 $ordered_row[$field] = $row[$field]; 443 } 444 445 $row = $ordered_row; 446 } 447 unset($row); 448 449 return self::insertBatch($table, $values, $getids); 450 } 451 452 /** 453 * Returns the list of mandatory fields with default values for INSERT statements. 454 * 455 * @static 456 * 457 * @param array $table_schema 458 * 459 * @return array 460 */ 461 private static function getMandatoryFields(array $table_schema) { 462 global $DB; 463 464 $mandatory_fields = []; 465 466 if ($DB['TYPE'] == ZBX_DB_MYSQL) { 467 foreach ($table_schema['fields'] as $name => $field) { 468 if ($field['type'] == self::FIELD_TYPE_TEXT) { 469 $mandatory_fields += [$name => $field['default']]; 470 } 471 } 472 } 473 474 return $mandatory_fields; 475 } 476 477 /** 478 * Insert batch data into DB. 479 * 480 * @param string $table 481 * @param array $values pair of fieldname => fieldvalue 482 * @param bool $getids 483 * 484 * @return array an array of ids with the keys preserved 485 */ 486 public static function insertBatch($table, $values, $getids = true) { 487 if (empty($values)) { 488 return true; 489 } 490 491 $resultIds = []; 492 493 $table_schema = self::getSchema($table); 494 495 if ($getids) { 496 $id = self::reserveIds($table, count($values)); 497 } 498 499 $mandatory_fields = self::getMandatoryFields($table_schema); 500 501 foreach ($values as $key => &$row) { 502 if ($getids) { 503 $resultIds[$key] = $id; 504 $row[$table_schema['key']] = $id; 505 $id = bcadd($id, 1, 0); 506 } 507 508 $row += $mandatory_fields; 509 510 self::checkValueTypes($table_schema, $row); 511 } 512 unset($row); 513 514 $sql = self::getDbBackend()->createInsertQuery($table, array_keys(reset($values)), $values); 515 516 if (!DBexecute($sql)) { 517 self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s".', $sql)); 518 } 519 520 return $resultIds; 521 } 522 523 /** 524 * Update data in DB. 525 * 526 * @param string $table 527 * @param array $data 528 * @param array $data[...]['values'] pair of fieldname => fieldvalue for SET clause 529 * @param array $data[...]['where'] pair of fieldname => fieldvalue for WHERE clause 530 * 531 * @return array of ids 532 */ 533 public static function update($table, $data) { 534 if (empty($data)) { 535 return true; 536 } 537 538 $tableSchema = self::getSchema($table); 539 540 $data = zbx_toArray($data); 541 foreach ($data as $row) { 542 // check 543 self::checkValueTypes($tableSchema, $row['values']); 544 if (empty($row['values'])) { 545 self::exception(self::DBEXECUTE_ERROR, _s('Cannot perform update statement on table "%1$s" without values.', $table)); 546 } 547 548 // set creation 549 $sqlSet = ''; 550 foreach ($row['values'] as $field => $value) { 551 if ($sqlSet !== '') { 552 $sqlSet .= ','; 553 } 554 $sqlSet .= $field.'='.$value; 555 } 556 557 if (!isset($row['where']) || empty($row['where']) || !is_array($row['where'])) { 558 self::exception(self::DBEXECUTE_ERROR, _s('Cannot perform update statement on table "%1$s" without where condition.', $table)); 559 } 560 561 // where condition processing 562 $sqlWhere = []; 563 foreach ($row['where'] as $field => $values) { 564 if (!isset($tableSchema['fields'][$field]) || is_null($values)) { 565 self::exception(self::DBEXECUTE_ERROR, _s('Incorrect field "%1$s" name or value in where statement for table "%2$s".', $field, $table)); 566 } 567 $values = zbx_toArray($values); 568 sort($values); // sorting ids to prevent deadlocks when two transactions depend on each other 569 570 $sqlWhere[] = dbConditionString($field, $values); 571 } 572 573 // sql execution 574 $sql = 'UPDATE '.$table.' SET '.$sqlSet.' WHERE '.implode(' AND ', $sqlWhere); 575 if (!DBexecute($sql)) { 576 self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s".', $sql)); 577 } 578 } 579 580 return true; 581 } 582 583 /** 584 * Updates the values by the given PK. 585 * 586 * @static 587 * 588 * @param string $tableName 589 * @param string $pk 590 * @param array $values 591 * 592 * @return bool 593 */ 594 public static function updateByPk($tableName, $pk, array $values) { 595 return self::update($tableName, [ 596 'where' => [self::getPk($tableName) => $pk], 597 'values' => $values 598 ]); 599 } 600 601 /** 602 * Saves the given records to the database. If the record has the primary key set, it is updated, otherwise - a new 603 * record is inserted. For new records the newly generated PK is added to the result. 604 * 605 * @static 606 * 607 * @param $tableName 608 * @param $data 609 * 610 * @return array the same records, that have been passed with the primary keys set for new records 611 */ 612 public static function save($tableName, array $data) { 613 $pk = self::getPk($tableName); 614 615 $newRecords = []; 616 foreach ($data as $key => $record) { 617 // if the pk is set - update the record 618 if (isset($record[$pk])) { 619 self::updateByPk($tableName, $record[$pk], $record); 620 } 621 // if no pk is set, create the record later 622 else { 623 $newRecords[$key] = $data[$key]; 624 } 625 } 626 627 // insert the new records 628 if ($newRecords) { 629 $newIds = self::insert($tableName, $newRecords); 630 foreach ($newIds as $key => $id) { 631 $data[$key][$pk] = $id; 632 } 633 } 634 635 return $data; 636 } 637 638 /** 639 * Replaces the records given in $oldRecords with the ones in $newRecords. 640 * 641 * If a record with the same primary key as a new one already exists in the old records, the record is updated 642 * only if they are different. For new records the newly generated PK is added to the result. Old records that are 643 * not present in the new records are deleted. 644 * 645 * All of the records must have the primary key defined. 646 * 647 * @static 648 * 649 * @param $tableName 650 * @param array $oldRecords 651 * @param array $newRecords 652 * 653 * @return array the new records, that have been passed with the primary keys set for newly inserted records 654 */ 655 public static function replace($tableName, array $oldRecords, array $newRecords) { 656 $pk = self::getPk($tableName); 657 $oldRecords = zbx_toHash($oldRecords, $pk); 658 659 $modifiedRecords = []; 660 foreach ($newRecords as $key => $record) { 661 // if it's a new or modified record - save it later 662 if (!isset($record[$pk]) || self::recordModified($tableName, $oldRecords[$record[$pk]], $record)) { 663 $modifiedRecords[$key] = $record; 664 } 665 666 // remove the existing records from the collection, the remaining ones will be deleted 667 if(isset($record[$pk])) { 668 unset($oldRecords[$record[$pk]]); 669 } 670 } 671 672 // save modified records 673 if ($modifiedRecords) { 674 $modifiedRecords = self::save($tableName, $modifiedRecords); 675 676 // add the new IDs to the new records 677 foreach ($modifiedRecords as $key => $record) { 678 $newRecords[$key][$pk] = $record[$pk]; 679 } 680 } 681 682 // delete remaining records 683 if ($oldRecords) { 684 DB::delete($tableName, [ 685 $pk => array_keys($oldRecords) 686 ]); 687 } 688 689 return $newRecords; 690 } 691 692 /** 693 * Replaces the records given in $groupedOldRecords with the ones given in $groupedNewRecords. 694 * 695 * This method can be used to replace related objects in one-to-many relations. Both old and new records 696 * must be grouped by the ID of the record they belong to. The records will be matched by position, instead of 697 * the primary key as in DB::replace(). That is, the first new record will update the first old one, second new 698 * record - the second old one, etc. Since the records are matched by position, the new records should not contain 699 * primary keys. 700 * 701 * Example 1: 702 * $old = array(2 => array( array('gitemid' => 1, 'color' => 'FF0000') )); 703 * $new = array(2 => array( array('color' => '00FF00') )); 704 * var_dump(DB::replaceByPosition('items', $old, $new)); 705 * // array(array('gitemid' => 1, 'color' => '00FF00')) 706 * 707 * The new record updated the old one. 708 * 709 * Example 2: 710 * $old = array(2 => array( array('gitemid' => 1, 'color' => 'FF0000') )); 711 * $new = array( 712 * 2 => array( 713 * array('color' => '00FF00'), 714 * array('color' => '0000FF') 715 * ) 716 * ); 717 * var_dump(DB::replaceByPosition('items', $old, $new)); 718 * // array(array('gitemid' => 1, 'color' => '00FF00'), array('gitemid' => 2, 'color' => '0000FF')) 719 * 720 * The first record was updated, the second one - created. 721 * 722 * Example 3: 723 * $old = array( 724 * 2 => array( 725 * array('gitemid' => 1, 'color' => 'FF0000'), 726 * array('gitemid' => 2, 'color' => '0000FF') 727 * ) 728 * ); 729 * $new = array(2 => array( array('color' => '00FF00') )); 730 * var_dump(DB::replaceByPosition('items', $old, $new)); 731 * // array(array('gitemid' => 1, 'color' => '00FF00')) 732 * 733 * The first record was updated, the second one - deleted. 734 * 735 * @param string $tableName table to update 736 * @param array $groupedOldRecords grouped old records 737 * @param array $groupedNewRecords grouped new records 738 * 739 * @return array array of new records not grouped (!). 740 */ 741 public static function replaceByPosition($tableName, array $groupedOldRecords, array $groupedNewRecords) { 742 $pk = self::getPk($tableName); 743 744 $allOldRecords = []; 745 $allNewRecords = []; 746 foreach ($groupedNewRecords as $key => $newRecords) { 747 // if records exist for the parent object - replace them, otherwise create new records 748 if (isset($groupedOldRecords[$key])) { 749 $oldRecords = $groupedOldRecords[$key]; 750 751 // updated the records by position 752 $newRecords = self::mergeRecords($oldRecords, $newRecords, $pk); 753 754 foreach ($oldRecords as $record) { 755 $allOldRecords[] = $record; 756 } 757 } 758 759 foreach ($newRecords as $record) { 760 $allNewRecords[] = $record; 761 } 762 } 763 764 // replace the old records with the new ones 765 return self::replace($tableName, $allOldRecords, $allNewRecords); 766 } 767 768 /** 769 * Compares the fields, that are present in both records, and returns true if any of the values differ. 770 * 771 * @static 772 * @param $tableName 773 * @param array $oldRecord 774 * @param array $newRecord 775 * 776 * @return bool 777 */ 778 public static function recordModified($tableName, array $oldRecord, array $newRecord) { 779 foreach ($oldRecord as $field => $value) { 780 if (self::hasField($tableName, $field) 781 && isset($newRecord[$field]) 782 && (string) $value !== (string) $newRecord[$field]) { 783 return true; 784 } 785 } 786 787 return false; 788 } 789 790 /** 791 * Replace each record in $oldRecords with a corresponding record in $newRecords, but keep the old record IDs. 792 * The records are match by position, that is, the first new record, replaces the first old record and etc. 793 * If there are less $newRecords than $oldRecords, the remaining old records will be discarded. 794 * 795 * @param array $oldRecords array of old records 796 * @param array $newRecords array of new records 797 * @param string $pk name of the private key column 798 * 799 * @return array array of new records with the primary keys from the old ones 800 */ 801 protected static function mergeRecords(array $oldRecords, array $newRecords, $pk) { 802 $result = []; 803 foreach ($newRecords as $i => $record) { 804 if (isset($oldRecords[$i])) { 805 $record[$pk] = $oldRecords[$i][$pk]; 806 } 807 808 $result[] = $record; 809 } 810 811 return $result; 812 } 813 814 /** 815 * Delete data from DB. 816 * 817 * Example: 818 * DB::delete('applications', array('applicationid'=>array(1, 8, 6))); 819 * DELETE FROM applications WHERE applicationid IN (1, 8, 6) 820 * 821 * DB::delete('applications', array('applicationid'=>array(1), 'templateid'=array(10))); 822 * DELETE FROM applications WHERE applicationid IN (1) AND templateid IN (10) 823 * 824 * @param string $table 825 * @param array $wheres pair of fieldname => fieldvalues 826 * @param bool $use_or 827 * 828 * @return bool 829 */ 830 public static function delete($table, $wheres, $use_or = false) { 831 if (empty($wheres) || !is_array($wheres)) { 832 self::exception(self::DBEXECUTE_ERROR, _s('Cannot perform delete statement on table "%1$s" without where condition.', $table)); 833 } 834 $table_schema = self::getSchema($table); 835 836 $sqlWhere = []; 837 foreach ($wheres as $field => $values) { 838 if (!isset($table_schema['fields'][$field]) || is_null($values)) { 839 self::exception(self::DBEXECUTE_ERROR, _s('Incorrect field "%1$s" name or value in where statement for table "%2$s".', $field, $table)); 840 } 841 $values = zbx_toArray($values); 842 sort($values); // sorting ids to prevent deadlocks when two transactions depends from each other 843 844 $sqlWhere[] = dbConditionString($field, $values); 845 } 846 847 $sql = 'DELETE FROM '.$table.' WHERE '.implode(($use_or ? ' OR ' : ' AND '), $sqlWhere); 848 if (!DBexecute($sql)) { 849 self::exception(self::DBEXECUTE_ERROR, _s('SQL statement execution has failed "%1$s"', $sql)); 850 } 851 852 return true; 853 } 854 855 /** 856 * @param string $table_name 857 * @param array $options 858 * @param string $table_alias 859 * 860 * @return string 861 */ 862 public static function makeSql($table_name, array &$options, $table_alias = null) { 863 $defaults = [ 864 'output' => [], 865 'countOutput' => false, 866 'filter' => [], 867 'sortfield' => [], 868 'sortorder' => [], 869 'limit' => null, 870 'preservekeys' => false 871 ]; 872 873 if ($array_diff = array_diff_key($options, $defaults)) { 874 unset($array_diff[self::getPk($table_name).'s']); 875 if ($array_diff) { 876 self::exception(self::SCHEMA_ERROR, 877 vsprintf('%s: unsupported option "%s".', [__FUNCTION__, key($array_diff)]) 878 ); 879 } 880 } 881 882 $options = zbx_array_merge($defaults, $options); 883 884 $sql_parts = self::createSelectQueryParts($table_name, $options, $table_alias); 885 886 return 'SELECT '.implode(',', $sql_parts['select']). 887 ' FROM '.implode(',', $sql_parts['from']). 888 ($sql_parts['where'] ? ' WHERE '.implode(' AND ', $sql_parts['where']) : ''). 889 ($sql_parts['order'] ? ' ORDER BY '.implode(',', $sql_parts['order']) : ''); 890 } 891 892 /** 893 * @param string $table_name 894 * @param array $options 895 * @param string $table_alias 896 * 897 * @return array 898 */ 899 public static function select($table_name, array $options, $table_alias = null) { 900 $result = []; 901 $field_names = array_flip($options['output']); 902 $db_result = DBSelect(self::makeSql($table_name, $options, $table_alias), $options['limit']); 903 904 if ($options['preservekeys']) { 905 $pk = self::getPk($table_name); 906 907 while ($db_row = DBfetch($db_result)) { 908 $result[$db_row[$pk]] = $options['countOutput'] ? $db_row : array_intersect_key($db_row, $field_names); 909 } 910 } 911 else { 912 while ($db_row = DBfetch($db_result)) { 913 $result[] = $options['countOutput'] ? $db_row : array_intersect_key($db_row, $field_names); 914 } 915 } 916 917 return $result; 918 } 919 920 /** 921 * Returns the table name with the table alias. 922 * 923 * @param string $table_name 924 * @param string $table_alias 925 * 926 * @return string 927 */ 928 private static function tableId($table_name, $table_alias = null) { 929 return($table_alias !== null) ? $table_name.' '.$table_alias : $table_name; 930 } 931 932 /** 933 * Prepends the table alias to the given field name. 934 * 935 * @param string $field_name 936 * @param string $table_alias 937 * 938 * @return string 939 */ 940 private static function fieldId($field_name, $table_alias = null) { 941 return ($table_alias !== null) ? $table_alias.'.'.$field_name : $field_name; 942 } 943 944 /** 945 * Builds an SQL parts array from the given options. 946 * 947 * @param string $table_name 948 * @param array $options 949 * @param string $table_alias 950 * 951 * @return array The resulting SQL parts array 952 */ 953 private static function createSelectQueryParts($table_name, array $options, $table_alias = null) { 954 $sql_parts = [ 955 'select' => [], 956 'from' => [self::tableId($table_name, $table_alias)], 957 'where' => [], 958 'order' => [] 959 ]; 960 961 // add output options 962 $sql_parts = self::applyQueryOutputOptions($table_name, $options, $table_alias, $sql_parts); 963 964 // add filter options 965 $sql_parts = self::applyQueryFilterOptions($table_name, $options, $table_alias, $sql_parts); 966 967 // add sort options 968 $sql_parts = self::applyQuerySortOptions($table_name, $options, $table_alias, $sql_parts); 969 970 return $sql_parts; 971 } 972 973 /** 974 * Modifies the SQL parts to implement all of the output related options. 975 * 976 * @param string $table_name 977 * @param array $options 978 * @param string $table_alias 979 * @param array $sql_parts 980 * 981 * @return array 982 */ 983 private static function applyQueryOutputOptions($table_name, array $options, $table_alias = null, 984 array $sql_parts) { 985 if ($options['countOutput']) { 986 $sql_parts['select'][] = 'COUNT('.self::fieldId('*', $table_alias).') AS rowscount'; 987 } 988 else { 989 $table_schema = self::getSchema($table_name); 990 $select = []; 991 $select[self::fieldId(self::getPk($table_name), $table_alias)] = true; 992 993 foreach ($options['output'] as $field_name) { 994 if (!array_key_exists($field_name, $table_schema['fields'])) { 995 self::exception(self::SCHEMA_ERROR, 996 vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name]) 997 ); 998 } 999 1000 $select[self::fieldId($field_name, $table_alias)] = true; 1001 } 1002 1003 $sql_parts['select'] = array_keys($select); 1004 } 1005 1006 return $sql_parts; 1007 } 1008 1009 /** 1010 * Modifies the SQL parts to implement all of the filter related options. 1011 * 1012 * @param string $table_name 1013 * @param array $options 1014 * @param string $table_alias 1015 * @param array $sql_parts 1016 * 1017 * @return array 1018 */ 1019 private static function applyQueryFilterOptions($table_name, array $options, $table_alias = null, 1020 array $sql_parts) { 1021 $table_schema = self::getSchema($table_name); 1022 $pk = self::getPk($table_name); 1023 $pk_option = $pk.'s'; 1024 1025 // pks 1026 if (array_key_exists($pk_option, $options)) { 1027 if (!is_array($options[$pk_option])) { 1028 $options[$pk_option] = [$options[$pk_option]]; 1029 } 1030 1031 $field_schema = $table_schema['fields'][$pk]; 1032 $field_name = self::fieldId($pk, $table_alias); 1033 1034 switch ($field_schema['type']) { 1035 case self::FIELD_TYPE_ID: 1036 $sql_parts['where'][] = dbConditionId($field_name, $options[$pk_option]); 1037 break; 1038 1039 case self::FIELD_TYPE_INT: 1040 case self::FIELD_TYPE_UINT: 1041 $sql_parts['where'][] = dbConditionInt($field_name, $options[$pk_option]); 1042 break; 1043 1044 default: 1045 $sql_parts['where'][] = dbConditionString($field_name, $options[$pk_option]); 1046 } 1047 } 1048 1049 // filters 1050 if (is_array($options['filter'])) { 1051 $sql_parts = self::dbFilter($table_name, $options, $table_alias, $sql_parts); 1052 } 1053 1054 return $sql_parts; 1055 } 1056 1057 /** 1058 * Apply filter conditions to sql built query. 1059 * 1060 * @param string $table_name 1061 * @param array $options 1062 * @param string $table_alias 1063 * @param array $sql_parts 1064 * 1065 * @return bool 1066 */ 1067 private static function dbFilter($table_name, $options, $table_alias = null, $sql_parts) { 1068 $table_schema = self::getSchema($table_name); 1069 $filter = []; 1070 1071 foreach ($options['filter'] as $field_name => $value) { 1072 if (!array_key_exists($field_name, $table_schema['fields'])) { 1073 self::exception(self::SCHEMA_ERROR, 1074 vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name]) 1075 ); 1076 } 1077 1078 $field_schema = $table_schema['fields'][$field_name]; 1079 1080 if ($field_schema['type'] == self::FIELD_TYPE_TEXT) { 1081 self::exception(self::SCHEMA_ERROR, 1082 vsprintf('%s: field "%s.%s" has an unsupported type.', [__FUNCTION__, $table_name, $field_name]) 1083 ); 1084 } 1085 1086 if ($value === null) { 1087 continue; 1088 } 1089 1090 if (!is_array($value)) { 1091 $value = [$value]; 1092 } 1093 1094 switch ($field_schema['type']) { 1095 case self::FIELD_TYPE_ID: 1096 $filter[] = dbConditionId(self::fieldId($field_name, $table_alias), $value); 1097 break; 1098 1099 case self::FIELD_TYPE_INT: 1100 case self::FIELD_TYPE_UINT: 1101 $filter[] = dbConditionInt(self::fieldId($field_name, $table_alias), $value); 1102 break; 1103 1104 default: 1105 $filter[] = dbConditionString(self::fieldId($field_name, $table_alias), $value); 1106 } 1107 } 1108 1109 if ($filter) { 1110 $sql_parts['where'][] = implode(' AND ', $filter); 1111 } 1112 1113 return $sql_parts; 1114 } 1115 1116 /** 1117 * Modifies the SQL parts to implement all of the sorting related options. 1118 * 1119 * @param string $table_name 1120 * @param array $options 1121 * @param string $table_alias 1122 * @param array $sql_parts 1123 * 1124 * @return array 1125 */ 1126 private static function applyQuerySortOptions($table_name, array $options, $table_alias = null, array $sql_parts) { 1127 $table_schema = self::getSchema($table_name); 1128 1129 foreach ($options['sortfield'] as $index => $field_name) { 1130 if (!array_key_exists($field_name, $table_schema['fields'])) { 1131 self::exception(self::SCHEMA_ERROR, 1132 vsprintf('%s: field "%s.%s" does not exist.', [__FUNCTION__, $table_name, $field_name]) 1133 ); 1134 } 1135 1136 $sortorder = ''; 1137 if (array_key_exists($index, $options['sortorder']) && $options['sortorder'][$index] == ZBX_SORT_DOWN) { 1138 $sortorder = ' '.ZBX_SORT_DOWN; 1139 } 1140 1141 $sql_parts['order'][] = self::fieldId($field_name, $table_alias).$sortorder; 1142 } 1143 1144 return $sql_parts; 1145 } 1146} 1147