| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[Summary view] [Print] [Text view]
1 <?php 2 // This file is part of Moodle - http://moodle.org/ 3 // 4 // Moodle is free software: you can redistribute it and/or modify 5 // it under the terms of the GNU General Public License as published by 6 // the Free Software Foundation, either version 3 of the License, or 7 // (at your option) any later version. 8 // 9 // Moodle is distributed in the hope that it will be useful, 10 // but WITHOUT ANY WARRANTY; without even the implied warranty of 11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 // GNU General Public License for more details. 13 // 14 // You should have received a copy of the GNU General Public License 15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>. 16 17 /** 18 * Native mysqli class representing moodle database interface. 19 * 20 * @package core_dml 21 * @copyright 2008 Petr Skoda (http://skodak.org) 22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 23 */ 24 25 defined('MOODLE_INTERNAL') || die(); 26 27 require_once (__DIR__.'/moodle_database.php'); 28 require_once (__DIR__.'/mysqli_native_moodle_recordset.php'); 29 require_once (__DIR__.'/mysqli_native_moodle_temptables.php'); 30 31 /** 32 * Native mysqli class representing moodle database interface. 33 * 34 * @package core_dml 35 * @copyright 2008 Petr Skoda (http://skodak.org) 36 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 37 */ 38 class mysqli_native_moodle_database extends moodle_database { 39 40 /** @var mysqli $mysqli */ 41 protected $mysqli = null; 42 /** @var bool is compressed row format supported cache */ 43 protected $compressedrowformatsupported = null; 44 45 private $transactions_supported = null; 46 47 /** 48 * Attempt to create the database 49 * @param string $dbhost 50 * @param string $dbuser 51 * @param string $dbpass 52 * @param string $dbname 53 * @return bool success 54 * @throws dml_exception A DML specific exception is thrown for any errors. 55 */ 56 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) { 57 $driverstatus = $this->driver_installed(); 58 59 if ($driverstatus !== true) { 60 throw new dml_exception('dbdriverproblem', $driverstatus); 61 } 62 63 if (!empty($dboptions['dbsocket']) 64 and (strpos($dboptions['dbsocket'], '/') !== false or strpos($dboptions['dbsocket'], '\\') !== false)) { 65 $dbsocket = $dboptions['dbsocket']; 66 } else { 67 $dbsocket = ini_get('mysqli.default_socket'); 68 } 69 if (empty($dboptions['dbport'])) { 70 $dbport = (int)ini_get('mysqli.default_port'); 71 } else { 72 $dbport = (int)$dboptions['dbport']; 73 } 74 // verify ini.get does not return nonsense 75 if (empty($dbport)) { 76 $dbport = 3306; 77 } 78 ob_start(); 79 $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db 80 $dberr = ob_get_contents(); 81 ob_end_clean(); 82 $errorno = @$conn->connect_errno; 83 84 if ($errorno !== 0) { 85 throw new dml_connection_exception($dberr); 86 } 87 88 if (isset($dboptions['dbcollation']) and strpos($dboptions['dbcollation'], 'utf8_') === 0) { 89 $collation = $dboptions['dbcollation']; 90 } else { 91 $collation = 'utf8_unicode_ci'; 92 } 93 94 $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET utf8 DEFAULT COLLATE ".$collation); 95 96 $conn->close(); 97 98 if (!$result) { 99 throw new dml_exception('cannotcreatedb'); 100 } 101 102 return true; 103 } 104 105 /** 106 * Detects if all needed PHP stuff installed. 107 * Note: can be used before connect() 108 * @return mixed true if ok, string if something 109 */ 110 public function driver_installed() { 111 if (!extension_loaded('mysqli')) { 112 return get_string('mysqliextensionisnotpresentinphp', 'install'); 113 } 114 return true; 115 } 116 117 /** 118 * Returns database family type - describes SQL dialect 119 * Note: can be used before connect() 120 * @return string db family name (mysql, postgres, mssql, oracle, etc.) 121 */ 122 public function get_dbfamily() { 123 return 'mysql'; 124 } 125 126 /** 127 * Returns more specific database driver type 128 * Note: can be used before connect() 129 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv 130 */ 131 protected function get_dbtype() { 132 return 'mysqli'; 133 } 134 135 /** 136 * Returns general database library name 137 * Note: can be used before connect() 138 * @return string db type pdo, native 139 */ 140 protected function get_dblibrary() { 141 return 'native'; 142 } 143 144 /** 145 * Returns the current MySQL db engine. 146 * 147 * This is an ugly workaround for MySQL default engine problems, 148 * Moodle is designed to work best on ACID compliant databases 149 * with full transaction support. Do not use MyISAM. 150 * 151 * @return string or null MySQL engine name 152 */ 153 public function get_dbengine() { 154 if (isset($this->dboptions['dbengine'])) { 155 return $this->dboptions['dbengine']; 156 } 157 158 if ($this->external) { 159 return null; 160 } 161 162 $engine = null; 163 164 // Look for current engine of our config table (the first table that gets created), 165 // so that we create all tables with the same engine. 166 $sql = "SELECT engine 167 FROM INFORMATION_SCHEMA.TABLES 168 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'"; 169 $this->query_start($sql, NULL, SQL_QUERY_AUX); 170 $result = $this->mysqli->query($sql); 171 $this->query_end($result); 172 if ($rec = $result->fetch_assoc()) { 173 $engine = $rec['engine']; 174 } 175 $result->close(); 176 177 if ($engine) { 178 // Cache the result to improve performance. 179 $this->dboptions['dbengine'] = $engine; 180 return $engine; 181 } 182 183 // get the default database engine 184 $sql = "SELECT @@storage_engine"; 185 $this->query_start($sql, NULL, SQL_QUERY_AUX); 186 $result = $this->mysqli->query($sql); 187 $this->query_end($result); 188 if ($rec = $result->fetch_assoc()) { 189 $engine = $rec['@@storage_engine']; 190 } 191 $result->close(); 192 193 if ($engine === 'MyISAM') { 194 // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported 195 $sql = "SHOW STORAGE ENGINES"; 196 $this->query_start($sql, NULL, SQL_QUERY_AUX); 197 $result = $this->mysqli->query($sql); 198 $this->query_end($result); 199 $engines = array(); 200 while ($res = $result->fetch_assoc()) { 201 if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') { 202 $engines[$res['Engine']] = true; 203 } 204 } 205 $result->close(); 206 if (isset($engines['InnoDB'])) { 207 $engine = 'InnoDB'; 208 } 209 if (isset($engines['XtraDB'])) { 210 $engine = 'XtraDB'; 211 } 212 } 213 214 // Cache the result to improve performance. 215 $this->dboptions['dbengine'] = $engine; 216 return $engine; 217 } 218 219 /** 220 * Returns the current MySQL db collation. 221 * 222 * This is an ugly workaround for MySQL default collation problems. 223 * 224 * @return string or null MySQL collation name 225 */ 226 public function get_dbcollation() { 227 if (isset($this->dboptions['dbcollation'])) { 228 return $this->dboptions['dbcollation']; 229 } 230 if ($this->external) { 231 return null; 232 } 233 234 $collation = null; 235 236 // Look for current collation of our config table (the first table that gets created), 237 // so that we create all tables with the same collation. 238 $sql = "SELECT collation_name 239 FROM INFORMATION_SCHEMA.COLUMNS 240 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config' AND column_name = 'value'"; 241 $this->query_start($sql, NULL, SQL_QUERY_AUX); 242 $result = $this->mysqli->query($sql); 243 $this->query_end($result); 244 if ($rec = $result->fetch_assoc()) { 245 $collation = $rec['collation_name']; 246 } 247 $result->close(); 248 249 if (!$collation) { 250 // Get the default database collation, but only if using UTF-8. 251 $sql = "SELECT @@collation_database"; 252 $this->query_start($sql, NULL, SQL_QUERY_AUX); 253 $result = $this->mysqli->query($sql); 254 $this->query_end($result); 255 if ($rec = $result->fetch_assoc()) { 256 if (strpos($rec['@@collation_database'], 'utf8_') === 0) { 257 $collation = $rec['@@collation_database']; 258 } 259 } 260 $result->close(); 261 } 262 263 if (!$collation) { 264 // We want only utf8 compatible collations. 265 $collation = null; 266 $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8\_%' AND Charset = 'utf8'"; 267 $this->query_start($sql, NULL, SQL_QUERY_AUX); 268 $result = $this->mysqli->query($sql); 269 $this->query_end($result); 270 while ($res = $result->fetch_assoc()) { 271 $collation = $res['Collation']; 272 if (strtoupper($res['Default']) === 'YES') { 273 $collation = $res['Collation']; 274 break; 275 } 276 } 277 $result->close(); 278 } 279 280 // Cache the result to improve performance. 281 $this->dboptions['dbcollation'] = $collation; 282 return $collation; 283 } 284 285 /** 286 * Get the row format from the database schema. 287 * 288 * @param string $table 289 * @return string row_format name or null if not known or table does not exist. 290 */ 291 public function get_row_format($table) { 292 $rowformat = null; 293 $table = $this->mysqli->real_escape_string($table); 294 $sql = "SELECT row_format 295 FROM INFORMATION_SCHEMA.TABLES 296 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'"; 297 $this->query_start($sql, NULL, SQL_QUERY_AUX); 298 $result = $this->mysqli->query($sql); 299 $this->query_end($result); 300 if ($rec = $result->fetch_assoc()) { 301 $rowformat = $rec['row_format']; 302 } 303 $result->close(); 304 305 return $rowformat; 306 } 307 308 /** 309 * Is this database compatible with compressed row format? 310 * This feature is necessary for support of large number of text 311 * columns in InnoDB/XtraDB database. 312 * 313 * @param bool $cached use cached result 314 * @return bool true if table can be created or changed to compressed row format. 315 */ 316 public function is_compressed_row_format_supported($cached = true) { 317 if ($cached and isset($this->compressedrowformatsupported)) { 318 return($this->compressedrowformatsupported); 319 } 320 321 $engine = strtolower($this->get_dbengine()); 322 $info = $this->get_server_info(); 323 324 if (version_compare($info['version'], '5.5.0') < 0) { 325 // MySQL 5.1 is not supported here because we cannot read the file format. 326 $this->compressedrowformatsupported = false; 327 328 } else if ($engine !== 'innodb' and $engine !== 'xtradb') { 329 // Other engines are not supported, most probably not compatible. 330 $this->compressedrowformatsupported = false; 331 332 } else if (!$filepertable = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_file_per_table'")) { 333 $this->compressedrowformatsupported = false; 334 335 } else if ($filepertable->value !== 'ON') { 336 $this->compressedrowformatsupported = false; 337 338 } else if (!$fileformat = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_file_format'")) { 339 $this->compressedrowformatsupported = false; 340 341 } else if ($fileformat->value !== 'Barracuda') { 342 $this->compressedrowformatsupported = false; 343 344 } else { 345 // All the tests passed, we can safely use ROW_FORMAT=Compressed in sql statements. 346 $this->compressedrowformatsupported = true; 347 } 348 349 return $this->compressedrowformatsupported; 350 } 351 352 /** 353 * Returns localised database type name 354 * Note: can be used before connect() 355 * @return string 356 */ 357 public function get_name() { 358 return get_string('nativemysqli', 'install'); 359 } 360 361 /** 362 * Returns localised database configuration help. 363 * Note: can be used before connect() 364 * @return string 365 */ 366 public function get_configuration_help() { 367 return get_string('nativemysqlihelp', 'install'); 368 } 369 370 /** 371 * Diagnose database and tables, this function is used 372 * to verify database and driver settings, db engine types, etc. 373 * 374 * @return string null means everything ok, string means problem found. 375 */ 376 public function diagnose() { 377 $sloppymyisamfound = false; 378 $prefix = str_replace('_', '\\_', $this->prefix); 379 $sql = "SELECT COUNT('x') 380 FROM INFORMATION_SCHEMA.TABLES 381 WHERE table_schema = DATABASE() 382 AND table_name LIKE BINARY '$prefix%' 383 AND Engine = 'MyISAM'"; 384 $this->query_start($sql, null, SQL_QUERY_AUX); 385 $result = $this->mysqli->query($sql); 386 $this->query_end($result); 387 if ($result) { 388 if ($arr = $result->fetch_assoc()) { 389 $count = reset($arr); 390 if ($count) { 391 $sloppymyisamfound = true; 392 } 393 } 394 $result->close(); 395 } 396 397 if ($sloppymyisamfound) { 398 return get_string('myisamproblem', 'error'); 399 } else { 400 return null; 401 } 402 } 403 404 /** 405 * Connect to db 406 * Must be called before other methods. 407 * @param string $dbhost The database host. 408 * @param string $dbuser The database username. 409 * @param string $dbpass The database username's password. 410 * @param string $dbname The name of the database being connected to.e 411 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used 412 * @param array $dboptions driver specific options 413 * @return bool success 414 */ 415 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { 416 $driverstatus = $this->driver_installed(); 417 418 if ($driverstatus !== true) { 419 throw new dml_exception('dbdriverproblem', $driverstatus); 420 } 421 422 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); 423 424 // dbsocket is used ONLY if host is NULL or 'localhost', 425 // you can not disable it because it is always tried if dbhost is 'localhost' 426 if (!empty($this->dboptions['dbsocket']) 427 and (strpos($this->dboptions['dbsocket'], '/') !== false or strpos($this->dboptions['dbsocket'], '\\') !== false)) { 428 $dbsocket = $this->dboptions['dbsocket']; 429 } else { 430 $dbsocket = ini_get('mysqli.default_socket'); 431 } 432 if (empty($this->dboptions['dbport'])) { 433 $dbport = (int)ini_get('mysqli.default_port'); 434 } else { 435 $dbport = (int)$this->dboptions['dbport']; 436 } 437 // verify ini.get does not return nonsense 438 if (empty($dbport)) { 439 $dbport = 3306; 440 } 441 if ($dbhost and !empty($this->dboptions['dbpersist'])) { 442 $dbhost = "p:$dbhost"; 443 } 444 $this->mysqli = @new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket); 445 446 if ($this->mysqli->connect_errno !== 0) { 447 $dberr = $this->mysqli->connect_error; 448 $this->mysqli = null; 449 throw new dml_connection_exception($dberr); 450 } 451 452 $this->query_start("--set_charset()", null, SQL_QUERY_AUX); 453 $this->mysqli->set_charset('utf8'); 454 $this->query_end(true); 455 456 // If available, enforce strict mode for the session. That guaranties 457 // standard behaviour under some situations, avoiding some MySQL nasty 458 // habits like truncating data or performing some transparent cast losses. 459 // With strict mode enforced, Moodle DB layer will be consistently throwing 460 // the corresponding exceptions as expected. 461 $si = $this->get_server_info(); 462 if (version_compare($si['version'], '5.0.2', '>=')) { 463 $sql = "SET SESSION sql_mode = 'STRICT_ALL_TABLES'"; 464 $this->query_start($sql, null, SQL_QUERY_AUX); 465 $result = $this->mysqli->query($sql); 466 $this->query_end($result); 467 } 468 469 // Connection stabilised and configured, going to instantiate the temptables controller 470 $this->temptables = new mysqli_native_moodle_temptables($this); 471 472 return true; 473 } 474 475 /** 476 * Close database connection and release all resources 477 * and memory (especially circular memory references). 478 * Do NOT use connect() again, create a new instance if needed. 479 */ 480 public function dispose() { 481 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection 482 if ($this->mysqli) { 483 $this->mysqli->close(); 484 $this->mysqli = null; 485 } 486 } 487 488 /** 489 * Returns database server info array 490 * @return array Array containing 'description' and 'version' info 491 */ 492 public function get_server_info() { 493 return array('description'=>$this->mysqli->server_info, 'version'=>$this->mysqli->server_info); 494 } 495 496 /** 497 * Returns supported query parameter types 498 * @return int bitmask of accepted SQL_PARAMS_* 499 */ 500 protected function allowed_param_types() { 501 return SQL_PARAMS_QM; 502 } 503 504 /** 505 * Returns last error reported by database engine. 506 * @return string error message 507 */ 508 public function get_last_error() { 509 return $this->mysqli->error; 510 } 511 512 /** 513 * Return tables in database WITHOUT current prefix 514 * @param bool $usecache if true, returns list of cached tables. 515 * @return array of table names in lowercase and without prefix 516 */ 517 public function get_tables($usecache=true) { 518 if ($usecache and $this->tables !== null) { 519 return $this->tables; 520 } 521 $this->tables = array(); 522 $prefix = str_replace('_', '\\_', $this->prefix); 523 $sql = "SHOW TABLES LIKE '$prefix%'"; 524 $this->query_start($sql, null, SQL_QUERY_AUX); 525 $result = $this->mysqli->query($sql); 526 $this->query_end($result); 527 $len = strlen($this->prefix); 528 if ($result) { 529 while ($arr = $result->fetch_assoc()) { 530 $tablename = reset($arr); 531 $tablename = substr($tablename, $len); 532 $this->tables[$tablename] = $tablename; 533 } 534 $result->close(); 535 } 536 537 // Add the currently available temptables 538 $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); 539 return $this->tables; 540 } 541 542 /** 543 * Return table indexes - everything lowercased. 544 * @param string $table The table we want to get indexes from. 545 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed 546 */ 547 public function get_indexes($table) { 548 $indexes = array(); 549 $sql = "SHOW INDEXES FROM {$this->prefix}$table"; 550 $this->query_start($sql, null, SQL_QUERY_AUX); 551 $result = $this->mysqli->query($sql); 552 try { 553 $this->query_end($result); 554 } catch (dml_read_exception $e) { 555 return $indexes; // table does not exist - no indexes... 556 } 557 if ($result) { 558 while ($res = $result->fetch_object()) { 559 if ($res->Key_name === 'PRIMARY') { 560 continue; 561 } 562 if (!isset($indexes[$res->Key_name])) { 563 $indexes[$res->Key_name] = array('unique'=>empty($res->Non_unique), 'columns'=>array()); 564 } 565 $indexes[$res->Key_name]['columns'][$res->Seq_in_index-1] = $res->Column_name; 566 } 567 $result->close(); 568 } 569 return $indexes; 570 } 571 572 /** 573 * Returns detailed information about columns in table. This information is cached internally. 574 * @param string $table name 575 * @param bool $usecache 576 * @return database_column_info[] array of database_column_info objects indexed with column names 577 */ 578 public function get_columns($table, $usecache=true) { 579 580 if ($usecache) { 581 $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash()); 582 $cache = cache::make('core', 'databasemeta', $properties); 583 if ($data = $cache->get($table)) { 584 return $data; 585 } 586 } 587 588 $structure = array(); 589 590 $sql = "SELECT column_name, data_type, character_maximum_length, numeric_precision, 591 numeric_scale, is_nullable, column_type, column_default, column_key, extra 592 FROM information_schema.columns 593 WHERE table_name = '" . $this->prefix.$table . "' 594 AND table_schema = '" . $this->dbname . "' 595 ORDER BY ordinal_position"; 596 $this->query_start($sql, null, SQL_QUERY_AUX); 597 $result = $this->mysqli->query($sql); 598 $this->query_end(true); // Don't want to throw anything here ever. MDL-30147 599 600 if ($result === false) { 601 return array(); 602 } 603 604 if ($result->num_rows > 0) { 605 // standard table exists 606 while ($rawcolumn = $result->fetch_assoc()) { 607 $info = (object)$this->get_column_info((object)$rawcolumn); 608 $structure[$info->name] = new database_column_info($info); 609 } 610 $result->close(); 611 612 } else { 613 // temporary tables are not in information schema, let's try it the old way 614 $result->close(); 615 $sql = "SHOW COLUMNS FROM {$this->prefix}$table"; 616 $this->query_start($sql, null, SQL_QUERY_AUX); 617 $result = $this->mysqli->query($sql); 618 $this->query_end(true); 619 if ($result === false) { 620 return array(); 621 } 622 while ($rawcolumn = $result->fetch_assoc()) { 623 $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER); 624 $rawcolumn->column_name = $rawcolumn->field; unset($rawcolumn->field); 625 $rawcolumn->column_type = $rawcolumn->type; unset($rawcolumn->type); 626 $rawcolumn->character_maximum_length = null; 627 $rawcolumn->numeric_precision = null; 628 $rawcolumn->numeric_scale = null; 629 $rawcolumn->is_nullable = $rawcolumn->null; unset($rawcolumn->null); 630 $rawcolumn->column_default = $rawcolumn->default; unset($rawcolumn->default); 631 $rawcolumn->column_key = $rawcolumn->key; unset($rawcolumn->default); 632 633 if (preg_match('/(enum|varchar)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 634 $rawcolumn->data_type = $matches[1]; 635 $rawcolumn->character_maximum_length = $matches[2]; 636 637 } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 638 $rawcolumn->data_type = $matches[1]; 639 $rawcolumn->numeric_precision = $matches[2]; 640 $rawcolumn->max_length = $rawcolumn->numeric_precision; 641 642 $type = strtoupper($matches[1]); 643 if ($type === 'BIGINT') { 644 $maxlength = 18; 645 } else if ($type === 'INT' or $type === 'INTEGER') { 646 $maxlength = 9; 647 } else if ($type === 'MEDIUMINT') { 648 $maxlength = 6; 649 } else if ($type === 'SMALLINT') { 650 $maxlength = 4; 651 } else if ($type === 'TINYINT') { 652 $maxlength = 2; 653 } else { 654 // This should not happen. 655 $maxlength = 0; 656 } 657 if ($maxlength < $rawcolumn->max_length) { 658 $rawcolumn->max_length = $maxlength; 659 } 660 661 } else if (preg_match('/(decimal)\((\d+),(\d+)\)/i', $rawcolumn->column_type, $matches)) { 662 $rawcolumn->data_type = $matches[1]; 663 $rawcolumn->numeric_precision = $matches[2]; 664 $rawcolumn->numeric_scale = $matches[3]; 665 666 } else if (preg_match('/(double|float)(\((\d+),(\d+)\))?/i', $rawcolumn->column_type, $matches)) { 667 $rawcolumn->data_type = $matches[1]; 668 $rawcolumn->numeric_precision = isset($matches[3]) ? $matches[3] : null; 669 $rawcolumn->numeric_scale = isset($matches[4]) ? $matches[4] : null; 670 671 } else if (preg_match('/([a-z]*text)/i', $rawcolumn->column_type, $matches)) { 672 $rawcolumn->data_type = $matches[1]; 673 $rawcolumn->character_maximum_length = -1; // unknown 674 675 } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->column_type, $matches)) { 676 $rawcolumn->data_type = $matches[1]; 677 678 } else { 679 $rawcolumn->data_type = $rawcolumn->column_type; 680 } 681 682 $info = $this->get_column_info($rawcolumn); 683 $structure[$info->name] = new database_column_info($info); 684 } 685 $result->close(); 686 } 687 688 if ($usecache) { 689 $cache->set($table, $structure); 690 } 691 692 return $structure; 693 } 694 695 /** 696 * Returns moodle column info for raw column from information schema. 697 * @param stdClass $rawcolumn 698 * @return stdClass standardised colum info 699 */ 700 private function get_column_info(stdClass $rawcolumn) { 701 $rawcolumn = (object)$rawcolumn; 702 $info = new stdClass(); 703 $info->name = $rawcolumn->column_name; 704 $info->type = $rawcolumn->data_type; 705 $info->meta_type = $this->mysqltype2moodletype($rawcolumn->data_type); 706 $info->default_value = $rawcolumn->column_default; 707 $info->has_default = !is_null($rawcolumn->column_default); 708 $info->not_null = ($rawcolumn->is_nullable === 'NO'); 709 $info->primary_key = ($rawcolumn->column_key === 'PRI'); 710 $info->binary = false; 711 $info->unsigned = null; 712 $info->auto_increment = false; 713 $info->unique = null; 714 $info->scale = null; 715 716 if ($info->meta_type === 'C') { 717 $info->max_length = $rawcolumn->character_maximum_length; 718 719 } else if ($info->meta_type === 'I') { 720 if ($info->primary_key) { 721 $info->meta_type = 'R'; 722 $info->unique = true; 723 } 724 // Return number of decimals, not bytes here. 725 $info->max_length = $rawcolumn->numeric_precision; 726 if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 727 $type = strtoupper($matches[1]); 728 if ($type === 'BIGINT') { 729 $maxlength = 18; 730 } else if ($type === 'INT' or $type === 'INTEGER') { 731 $maxlength = 9; 732 } else if ($type === 'MEDIUMINT') { 733 $maxlength = 6; 734 } else if ($type === 'SMALLINT') { 735 $maxlength = 4; 736 } else if ($type === 'TINYINT') { 737 $maxlength = 2; 738 } else { 739 // This should not happen. 740 $maxlength = 0; 741 } 742 // It is possible that display precision is different from storage type length, 743 // always use the smaller value to make sure our data fits. 744 if ($maxlength < $info->max_length) { 745 $info->max_length = $maxlength; 746 } 747 } 748 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 749 $info->auto_increment= (strpos($rawcolumn->extra, 'auto_increment') !== false); 750 751 } else if ($info->meta_type === 'N') { 752 $info->max_length = $rawcolumn->numeric_precision; 753 $info->scale = $rawcolumn->numeric_scale; 754 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 755 756 } else if ($info->meta_type === 'X') { 757 if ("$rawcolumn->character_maximum_length" === '4294967295') { // watch out for PHP max int limits! 758 // means maximum moodle size for text column, in other drivers it may also mean unknown size 759 $info->max_length = -1; 760 } else { 761 $info->max_length = $rawcolumn->character_maximum_length; 762 } 763 $info->primary_key = false; 764 765 } else if ($info->meta_type === 'B') { 766 $info->max_length = -1; 767 $info->primary_key = false; 768 $info->binary = true; 769 } 770 771 return $info; 772 } 773 774 /** 775 * Normalise column type. 776 * @param string $mysql_type 777 * @return string one character 778 * @throws dml_exception 779 */ 780 private function mysqltype2moodletype($mysql_type) { 781 $type = null; 782 783 switch(strtoupper($mysql_type)) { 784 case 'BIT': 785 $type = 'L'; 786 break; 787 788 case 'TINYINT': 789 case 'SMALLINT': 790 case 'MEDIUMINT': 791 case 'INT': 792 case 'INTEGER': 793 case 'BIGINT': 794 $type = 'I'; 795 break; 796 797 case 'FLOAT': 798 case 'DOUBLE': 799 case 'DECIMAL': 800 $type = 'N'; 801 break; 802 803 case 'CHAR': 804 case 'ENUM': 805 case 'SET': 806 case 'VARCHAR': 807 $type = 'C'; 808 break; 809 810 case 'TINYTEXT': 811 case 'TEXT': 812 case 'MEDIUMTEXT': 813 case 'LONGTEXT': 814 $type = 'X'; 815 break; 816 817 case 'BINARY': 818 case 'VARBINARY': 819 case 'BLOB': 820 case 'TINYBLOB': 821 case 'MEDIUMBLOB': 822 case 'LONGBLOB': 823 $type = 'B'; 824 break; 825 826 case 'DATE': 827 case 'TIME': 828 case 'DATETIME': 829 case 'TIMESTAMP': 830 case 'YEAR': 831 $type = 'D'; 832 break; 833 } 834 835 if (!$type) { 836 throw new dml_exception('invalidmysqlnativetype', $mysql_type); 837 } 838 return $type; 839 } 840 841 /** 842 * Normalise values based in RDBMS dependencies (booleans, LOBs...) 843 * 844 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 845 * @param mixed $value value we are going to normalise 846 * @return mixed the normalised value 847 */ 848 protected function normalise_value($column, $value) { 849 $this->detect_objects($value); 850 851 if (is_bool($value)) { // Always, convert boolean to int 852 $value = (int)$value; 853 854 } else if ($value === '') { 855 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { 856 $value = 0; // prevent '' problems in numeric fields 857 } 858 // Any float value being stored in varchar or text field is converted to string to avoid 859 // any implicit conversion by MySQL 860 } else if (is_float($value) and ($column->meta_type == 'C' or $column->meta_type == 'X')) { 861 $value = "$value"; 862 } 863 return $value; 864 } 865 866 /** 867 * Is this database compatible with utf8? 868 * @return bool 869 */ 870 public function setup_is_unicodedb() { 871 // All new tables are created with this collation, we just have to make sure it is utf8 compatible, 872 // if config table already exists it has this collation too. 873 $collation = $this->get_dbcollation(); 874 875 $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = 'utf8'"; 876 $this->query_start($sql, NULL, SQL_QUERY_AUX); 877 $result = $this->mysqli->query($sql); 878 $this->query_end($result); 879 if ($result->fetch_assoc()) { 880 $return = true; 881 } else { 882 $return = false; 883 } 884 $result->close(); 885 886 return $return; 887 } 888 889 /** 890 * Do NOT use in code, to be used by database_manager only! 891 * @param string|array $sql query 892 * @return bool true 893 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors. 894 */ 895 public function change_database_structure($sql) { 896 $this->get_manager(); // Includes DDL exceptions classes ;-) 897 if (is_array($sql)) { 898 $sql = implode("\n;\n", $sql); 899 } 900 901 try { 902 $this->query_start($sql, null, SQL_QUERY_STRUCTURE); 903 $result = $this->mysqli->multi_query($sql); 904 if ($result === false) { 905 $this->query_end(false); 906 } 907 while ($this->mysqli->more_results()) { 908 $result = $this->mysqli->next_result(); 909 if ($result === false) { 910 $this->query_end(false); 911 } 912 } 913 $this->query_end(true); 914 } catch (ddl_change_structure_exception $e) { 915 while (@$this->mysqli->more_results()) { 916 @$this->mysqli->next_result(); 917 } 918 $this->reset_caches(); 919 throw $e; 920 } 921 922 $this->reset_caches(); 923 return true; 924 } 925 926 /** 927 * Very ugly hack which emulates bound parameters in queries 928 * because prepared statements do not use query cache. 929 */ 930 protected function emulate_bound_params($sql, array $params=null) { 931 if (empty($params)) { 932 return $sql; 933 } 934 // ok, we have verified sql statement with ? and correct number of params 935 $parts = array_reverse(explode('?', $sql)); 936 $return = array_pop($parts); 937 foreach ($params as $param) { 938 if (is_bool($param)) { 939 $return .= (int)$param; 940 } else if (is_null($param)) { 941 $return .= 'NULL'; 942 } else if (is_number($param)) { 943 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting 944 } else if (is_float($param)) { 945 $return .= $param; 946 } else { 947 $param = $this->mysqli->real_escape_string($param); 948 $return .= "'$param'"; 949 } 950 $return .= array_pop($parts); 951 } 952 return $return; 953 } 954 955 /** 956 * Execute general sql query. Should be used only when no other method suitable. 957 * Do NOT use this to make changes in db structure, use database_manager methods instead! 958 * @param string $sql query 959 * @param array $params query parameters 960 * @return bool true 961 * @throws dml_exception A DML specific exception is thrown for any errors. 962 */ 963 public function execute($sql, array $params=null) { 964 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 965 966 if (strpos($sql, ';') !== false) { 967 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); 968 } 969 970 $rawsql = $this->emulate_bound_params($sql, $params); 971 972 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 973 $result = $this->mysqli->query($rawsql); 974 $this->query_end($result); 975 976 if ($result === true) { 977 return true; 978 979 } else { 980 $result->close(); 981 return true; 982 } 983 } 984 985 /** 986 * Get a number of records as a moodle_recordset using a SQL statement. 987 * 988 * Since this method is a little less readable, use of it should be restricted to 989 * code where it's possible there might be large datasets being returned. For known 990 * small datasets use get_records_sql - it leads to simpler code. 991 * 992 * The return type is like: 993 * @see function get_recordset. 994 * 995 * @param string $sql the SQL select query to execute. 996 * @param array $params array of sql parameters 997 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 998 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 999 * @return moodle_recordset instance 1000 * @throws dml_exception A DML specific exception is thrown for any errors. 1001 */ 1002 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1003 1004 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1005 1006 if ($limitfrom or $limitnum) { 1007 if ($limitnum < 1) { 1008 $limitnum = "18446744073709551615"; 1009 } 1010 $sql .= " LIMIT $limitfrom, $limitnum"; 1011 } 1012 1013 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1014 $rawsql = $this->emulate_bound_params($sql, $params); 1015 1016 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1017 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables 1018 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1019 $this->query_end($result); 1020 1021 return $this->create_recordset($result); 1022 } 1023 1024 /** 1025 * Get all records from a table. 1026 * 1027 * This method works around potential memory problems and may improve performance, 1028 * this method may block access to table until the recordset is closed. 1029 * 1030 * @param string $table Name of database table. 1031 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}. 1032 * @throws dml_exception A DML specific exception is thrown for any errors. 1033 */ 1034 public function export_table_recordset($table) { 1035 $sql = $this->fix_table_names("SELECT * FROM {{$table}}"); 1036 1037 $this->query_start($sql, array(), SQL_QUERY_SELECT); 1038 // MYSQLI_STORE_RESULT may eat all memory for large tables, unfortunately MYSQLI_USE_RESULT blocks other queries. 1039 $result = $this->mysqli->query($sql, MYSQLI_USE_RESULT); 1040 $this->query_end($result); 1041 1042 return $this->create_recordset($result); 1043 } 1044 1045 protected function create_recordset($result) { 1046 return new mysqli_native_moodle_recordset($result); 1047 } 1048 1049 /** 1050 * Get a number of records as an array of objects using a SQL statement. 1051 * 1052 * Return value is like: 1053 * @see function get_records. 1054 * 1055 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1056 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1057 * returned array. 1058 * @param array $params array of sql parameters 1059 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1060 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1061 * @return array of objects, or empty array if no records were found 1062 * @throws dml_exception A DML specific exception is thrown for any errors. 1063 */ 1064 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1065 1066 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1067 1068 if ($limitfrom or $limitnum) { 1069 if ($limitnum < 1) { 1070 $limitnum = "18446744073709551615"; 1071 } 1072 $sql .= " LIMIT $limitfrom, $limitnum"; 1073 } 1074 1075 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1076 $rawsql = $this->emulate_bound_params($sql, $params); 1077 1078 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1079 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1080 $this->query_end($result); 1081 1082 $return = array(); 1083 1084 while($row = $result->fetch_assoc()) { 1085 $row = array_change_key_case($row, CASE_LOWER); 1086 $id = reset($row); 1087 if (isset($return[$id])) { 1088 $colname = key($row); 1089 debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER); 1090 } 1091 $return[$id] = (object)$row; 1092 } 1093 $result->close(); 1094 1095 return $return; 1096 } 1097 1098 /** 1099 * Selects records and return values (first field) as an array using a SQL statement. 1100 * 1101 * @param string $sql The SQL query 1102 * @param array $params array of sql parameters 1103 * @return array of values 1104 * @throws dml_exception A DML specific exception is thrown for any errors. 1105 */ 1106 public function get_fieldset_sql($sql, array $params=null) { 1107 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1108 $rawsql = $this->emulate_bound_params($sql, $params); 1109 1110 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1111 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1112 $this->query_end($result); 1113 1114 $return = array(); 1115 1116 while($row = $result->fetch_assoc()) { 1117 $return[] = reset($row); 1118 } 1119 $result->close(); 1120 1121 return $return; 1122 } 1123 1124 /** 1125 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1126 * @param string $table name 1127 * @param mixed $params data record as object or array 1128 * @param bool $returnit return it of inserted record 1129 * @param bool $bulk true means repeated inserts expected 1130 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1131 * @return bool|int true or new id 1132 * @throws dml_exception A DML specific exception is thrown for any errors. 1133 */ 1134 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { 1135 if (!is_array($params)) { 1136 $params = (array)$params; 1137 } 1138 1139 if ($customsequence) { 1140 if (!isset($params['id'])) { 1141 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); 1142 } 1143 $returnid = false; 1144 } else { 1145 unset($params['id']); 1146 } 1147 1148 if (empty($params)) { 1149 throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); 1150 } 1151 1152 $fields = implode(',', array_keys($params)); 1153 $qms = array_fill(0, count($params), '?'); 1154 $qms = implode(',', $qms); 1155 1156 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($qms)"; 1157 1158 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1159 $rawsql = $this->emulate_bound_params($sql, $params); 1160 1161 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1162 $result = $this->mysqli->query($rawsql); 1163 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db 1164 $this->query_end($result); 1165 1166 if (!$customsequence and !$id) { 1167 throw new dml_write_exception('unknown error fetching inserted id'); 1168 } 1169 1170 if (!$returnid) { 1171 return true; 1172 } else { 1173 return (int)$id; 1174 } 1175 } 1176 1177 /** 1178 * Insert a record into a table and return the "id" field if required. 1179 * 1180 * Some conversions and safety checks are carried out. Lobs are supported. 1181 * If the return ID isn't required, then this just reports success as true/false. 1182 * $data is an object containing needed data 1183 * @param string $table The database table to be inserted into 1184 * @param object $data A data object with values for one or more fields in the record 1185 * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned. 1186 * @return bool|int true or new id 1187 * @throws dml_exception A DML specific exception is thrown for any errors. 1188 */ 1189 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { 1190 $dataobject = (array)$dataobject; 1191 1192 $columns = $this->get_columns($table); 1193 if (empty($columns)) { 1194 throw new dml_exception('ddltablenotexist', $table); 1195 } 1196 1197 $cleaned = array(); 1198 1199 foreach ($dataobject as $field=>$value) { 1200 if ($field === 'id') { 1201 continue; 1202 } 1203 if (!isset($columns[$field])) { 1204 continue; 1205 } 1206 $column = $columns[$field]; 1207 $cleaned[$field] = $this->normalise_value($column, $value); 1208 } 1209 1210 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); 1211 } 1212 1213 /** 1214 * Insert multiple records into database as fast as possible. 1215 * 1216 * Order of inserts is maintained, but the operation is not atomic, 1217 * use transactions if necessary. 1218 * 1219 * This method is intended for inserting of large number of small objects, 1220 * do not use for huge objects with text or binary fields. 1221 * 1222 * @since Moodle 2.7 1223 * 1224 * @param string $table The database table to be inserted into 1225 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach 1226 * @return void does not return new record ids 1227 * 1228 * @throws coding_exception if data objects have different structure 1229 * @throws dml_exception A DML specific exception is thrown for any errors. 1230 */ 1231 public function insert_records($table, $dataobjects) { 1232 if (!is_array($dataobjects) and !$dataobjects instanceof Traversable) { 1233 throw new coding_exception('insert_records() passed non-traversable object'); 1234 } 1235 1236 // MySQL has a relatively small query length limit by default, 1237 // make sure 'max_allowed_packet' in my.cnf is high enough 1238 // if you change the following default... 1239 static $chunksize = null; 1240 if ($chunksize === null) { 1241 if (!empty($this->dboptions['bulkinsertsize'])) { 1242 $chunksize = (int)$this->dboptions['bulkinsertsize']; 1243 1244 } else { 1245 if (PHP_INT_SIZE === 4) { 1246 // Bad luck for Windows, we cannot do any maths with large numbers. 1247 $chunksize = 5; 1248 } else { 1249 $sql = "SHOW VARIABLES LIKE 'max_allowed_packet'"; 1250 $this->query_start($sql, null, SQL_QUERY_AUX); 1251 $result = $this->mysqli->query($sql); 1252 $this->query_end($result); 1253 $size = 0; 1254 if ($rec = $result->fetch_assoc()) { 1255 $size = $rec['Value']; 1256 } 1257 $result->close(); 1258 // Hopefully 200kb per object are enough. 1259 $chunksize = (int)($size / 200000); 1260 if ($chunksize > 50) { 1261 $chunksize = 50; 1262 } 1263 } 1264 } 1265 } 1266 1267 $columns = $this->get_columns($table, true); 1268 $fields = null; 1269 $count = 0; 1270 $chunk = array(); 1271 foreach ($dataobjects as $dataobject) { 1272 if (!is_array($dataobject) and !is_object($dataobject)) { 1273 throw new coding_exception('insert_records() passed invalid record object'); 1274 } 1275 $dataobject = (array)$dataobject; 1276 if ($fields === null) { 1277 $fields = array_keys($dataobject); 1278 $columns = array_intersect_key($columns, $dataobject); 1279 unset($columns['id']); 1280 } else if ($fields !== array_keys($dataobject)) { 1281 throw new coding_exception('All dataobjects in insert_records() must have the same structure!'); 1282 } 1283 1284 $count++; 1285 $chunk[] = $dataobject; 1286 1287 if ($count === $chunksize) { 1288 $this->insert_chunk($table, $chunk, $columns); 1289 $chunk = array(); 1290 $count = 0; 1291 } 1292 } 1293 1294 if ($count) { 1295 $this->insert_chunk($table, $chunk, $columns); 1296 } 1297 } 1298 1299 /** 1300 * Insert records in chunks. 1301 * 1302 * Note: can be used only from insert_records(). 1303 * 1304 * @param string $table 1305 * @param array $chunk 1306 * @param database_column_info[] $columns 1307 */ 1308 protected function insert_chunk($table, array $chunk, array $columns) { 1309 $fieldssql = '('.implode(',', array_keys($columns)).')'; 1310 1311 $valuessql = '('.implode(',', array_fill(0, count($columns), '?')).')'; 1312 $valuessql = implode(',', array_fill(0, count($chunk), $valuessql)); 1313 1314 $params = array(); 1315 foreach ($chunk as $dataobject) { 1316 foreach ($columns as $field => $column) { 1317 $params[] = $this->normalise_value($column, $dataobject[$field]); 1318 } 1319 } 1320 1321 $sql = "INSERT INTO {$this->prefix}$table $fieldssql VALUES $valuessql"; 1322 1323 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1324 $rawsql = $this->emulate_bound_params($sql, $params); 1325 1326 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1327 $result = $this->mysqli->query($rawsql); 1328 $this->query_end($result); 1329 } 1330 1331 /** 1332 * Import a record into a table, id field is required. 1333 * Safety checks are NOT carried out. Lobs are supported. 1334 * 1335 * @param string $table name of database table to be inserted into 1336 * @param object $dataobject A data object with values for one or more fields in the record 1337 * @return bool true 1338 * @throws dml_exception A DML specific exception is thrown for any errors. 1339 */ 1340 public function import_record($table, $dataobject) { 1341 $dataobject = (array)$dataobject; 1342 1343 $columns = $this->get_columns($table); 1344 $cleaned = array(); 1345 1346 foreach ($dataobject as $field=>$value) { 1347 if (!isset($columns[$field])) { 1348 continue; 1349 } 1350 $cleaned[$field] = $value; 1351 } 1352 1353 return $this->insert_record_raw($table, $cleaned, false, true, true); 1354 } 1355 1356 /** 1357 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1358 * @param string $table name 1359 * @param mixed $params data record as object or array 1360 * @param bool true means repeated updates expected 1361 * @return bool true 1362 * @throws dml_exception A DML specific exception is thrown for any errors. 1363 */ 1364 public function update_record_raw($table, $params, $bulk=false) { 1365 $params = (array)$params; 1366 1367 if (!isset($params['id'])) { 1368 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); 1369 } 1370 $id = $params['id']; 1371 unset($params['id']); 1372 1373 if (empty($params)) { 1374 throw new coding_exception('moodle_database::update_record_raw() no fields found.'); 1375 } 1376 1377 $sets = array(); 1378 foreach ($params as $field=>$value) { 1379 $sets[] = "$field = ?"; 1380 } 1381 1382 $params[] = $id; // last ? in WHERE condition 1383 1384 $sets = implode(',', $sets); 1385 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=?"; 1386 1387 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1388 $rawsql = $this->emulate_bound_params($sql, $params); 1389 1390 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1391 $result = $this->mysqli->query($rawsql); 1392 $this->query_end($result); 1393 1394 return true; 1395 } 1396 1397 /** 1398 * Update a record in a table 1399 * 1400 * $dataobject is an object containing needed data 1401 * Relies on $dataobject having a variable "id" to 1402 * specify the record to update 1403 * 1404 * @param string $table The database table to be checked against. 1405 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. 1406 * @param bool true means repeated updates expected 1407 * @return bool true 1408 * @throws dml_exception A DML specific exception is thrown for any errors. 1409 */ 1410 public function update_record($table, $dataobject, $bulk=false) { 1411 $dataobject = (array)$dataobject; 1412 1413 $columns = $this->get_columns($table); 1414 $cleaned = array(); 1415 1416 foreach ($dataobject as $field=>$value) { 1417 if (!isset($columns[$field])) { 1418 continue; 1419 } 1420 $column = $columns[$field]; 1421 $cleaned[$field] = $this->normalise_value($column, $value); 1422 } 1423 1424 return $this->update_record_raw($table, $cleaned, $bulk); 1425 } 1426 1427 /** 1428 * Set a single field in every table record which match a particular WHERE clause. 1429 * 1430 * @param string $table The database table to be checked against. 1431 * @param string $newfield the field to set. 1432 * @param string $newvalue the value to set the field to. 1433 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1434 * @param array $params array of sql parameters 1435 * @return bool true 1436 * @throws dml_exception A DML specific exception is thrown for any errors. 1437 */ 1438 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { 1439 if ($select) { 1440 $select = "WHERE $select"; 1441 } 1442 if (is_null($params)) { 1443 $params = array(); 1444 } 1445 list($select, $params, $type) = $this->fix_sql_params($select, $params); 1446 1447 // Get column metadata 1448 $columns = $this->get_columns($table); 1449 $column = $columns[$newfield]; 1450 1451 $normalised_value = $this->normalise_value($column, $newvalue); 1452 1453 if (is_null($normalised_value)) { 1454 $newfield = "$newfield = NULL"; 1455 } else { 1456 $newfield = "$newfield = ?"; 1457 array_unshift($params, $normalised_value); 1458 } 1459 $sql = "UPDATE {$this->prefix}$table SET $newfield $select"; 1460 $rawsql = $this->emulate_bound_params($sql, $params); 1461 1462 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1463 $result = $this->mysqli->query($rawsql); 1464 $this->query_end($result); 1465 1466 return true; 1467 } 1468 1469 /** 1470 * Delete one or more records from a table which match a particular WHERE clause. 1471 * 1472 * @param string $table The database table to be checked against. 1473 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1474 * @param array $params array of sql parameters 1475 * @return bool true 1476 * @throws dml_exception A DML specific exception is thrown for any errors. 1477 */ 1478 public function delete_records_select($table, $select, array $params=null) { 1479 if ($select) { 1480 $select = "WHERE $select"; 1481 } 1482 $sql = "DELETE FROM {$this->prefix}$table $select"; 1483 1484 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1485 $rawsql = $this->emulate_bound_params($sql, $params); 1486 1487 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1488 $result = $this->mysqli->query($rawsql); 1489 $this->query_end($result); 1490 1491 return true; 1492 } 1493 1494 public function sql_cast_char2int($fieldname, $text=false) { 1495 return ' CAST(' . $fieldname . ' AS SIGNED) '; 1496 } 1497 1498 public function sql_cast_char2real($fieldname, $text=false) { 1499 return ' CAST(' . $fieldname . ' AS DECIMAL) '; 1500 } 1501 1502 /** 1503 * Returns 'LIKE' part of a query. 1504 * 1505 * @param string $fieldname usually name of the table column 1506 * @param string $param usually bound query parameter (?, :named) 1507 * @param bool $casesensitive use case sensitive search 1508 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive) 1509 * @param bool $notlike true means "NOT LIKE" 1510 * @param string $escapechar escape char for '%' and '_' 1511 * @return string SQL code fragment 1512 */ 1513 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 1514 if (strpos($param, '%') !== false) { 1515 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 1516 } 1517 $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\' 1518 1519 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 1520 if ($casesensitive) { 1521 return "$fieldname $LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'"; 1522 } else { 1523 if ($accentsensitive) { 1524 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'"; 1525 } else { 1526 return "$fieldname $LIKE $param ESCAPE '$escapechar'"; 1527 } 1528 } 1529 } 1530 1531 /** 1532 * Returns the proper SQL to do CONCAT between the elements passed 1533 * Can take many parameters 1534 * 1535 * @param string $str,... 1 or more fields/strings to concat 1536 * 1537 * @return string The concat sql 1538 */ 1539 public function sql_concat() { 1540 $arr = func_get_args(); 1541 $s = implode(', ', $arr); 1542 if ($s === '') { 1543 return "''"; 1544 } 1545 return "CONCAT($s)"; 1546 } 1547 1548 /** 1549 * Returns the proper SQL to do CONCAT between the elements passed 1550 * with a given separator 1551 * 1552 * @param string $separator The string to use as the separator 1553 * @param array $elements An array of items to concatenate 1554 * @return string The concat SQL 1555 */ 1556 public function sql_concat_join($separator="' '", $elements=array()) { 1557 $s = implode(', ', $elements); 1558 1559 if ($s === '') { 1560 return "''"; 1561 } 1562 return "CONCAT_WS($separator, $s)"; 1563 } 1564 1565 /** 1566 * Returns the SQL text to be used to calculate the length in characters of one expression. 1567 * @param string fieldname or expression to calculate its length in characters. 1568 * @return string the piece of SQL code to be used in the statement. 1569 */ 1570 public function sql_length($fieldname) { 1571 return ' CHAR_LENGTH(' . $fieldname . ')'; 1572 } 1573 1574 /** 1575 * Does this driver support regex syntax when searching 1576 */ 1577 public function sql_regex_supported() { 1578 return true; 1579 } 1580 1581 /** 1582 * Return regex positive or negative match sql 1583 * @param bool $positivematch 1584 * @return string or empty if not supported 1585 */ 1586 public function sql_regex($positivematch=true) { 1587 return $positivematch ? 'REGEXP' : 'NOT REGEXP'; 1588 } 1589 1590 /** 1591 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED. 1592 * 1593 * @deprecated since 2.3 1594 * @param string $fieldname The name of the field to be cast 1595 * @return string The piece of SQL code to be used in your statement. 1596 */ 1597 public function sql_cast_2signed($fieldname) { 1598 return ' CAST(' . $fieldname . ' AS SIGNED) '; 1599 } 1600 1601 /** 1602 * Returns the SQL that allows to find intersection of two or more queries 1603 * 1604 * @since Moodle 2.8 1605 * 1606 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields 1607 * @param string $fields comma-separated list of fields 1608 * @return string SQL query that will return only values that are present in each of selects 1609 */ 1610 public function sql_intersect($selects, $fields) { 1611 if (count($selects) <= 1) { 1612 return parent::sql_intersect($selects, $fields); 1613 } 1614 $fields = preg_replace('/\s/', '', $fields); 1615 static $aliascnt = 0; 1616 $falias = 'intsctal'.($aliascnt++); 1617 $rv = "SELECT $falias.". 1618 preg_replace('/,/', ','.$falias.'.', $fields). 1619 " FROM ($selects[0]) $falias"; 1620 for ($i = 1; $i < count($selects); $i++) { 1621 $alias = 'intsctal'.($aliascnt++); 1622 $rv .= " JOIN (".$selects[$i].") $alias ON ". 1623 join(' AND ', 1624 array_map( 1625 create_function('$a', 'return "'.$falias.'.$a = '.$alias.'.$a";'), 1626 preg_split('/,/', $fields)) 1627 ); 1628 } 1629 return $rv; 1630 } 1631 1632 /** 1633 * Does this driver support tool_replace? 1634 * 1635 * @since Moodle 2.6.1 1636 * @return bool 1637 */ 1638 public function replace_all_text_supported() { 1639 return true; 1640 } 1641 1642 public function session_lock_supported() { 1643 return true; 1644 } 1645 1646 /** 1647 * Obtain session lock 1648 * @param int $rowid id of the row with session record 1649 * @param int $timeout max allowed time to wait for the lock in seconds 1650 * @return void 1651 */ 1652 public function get_session_lock($rowid, $timeout) { 1653 parent::get_session_lock($rowid, $timeout); 1654 1655 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1656 $sql = "SELECT GET_LOCK('$fullname', $timeout)"; 1657 $this->query_start($sql, null, SQL_QUERY_AUX); 1658 $result = $this->mysqli->query($sql); 1659 $this->query_end($result); 1660 1661 if ($result) { 1662 $arr = $result->fetch_assoc(); 1663 $result->close(); 1664 1665 if (reset($arr) == 1) { 1666 return; 1667 } else { 1668 throw new dml_sessionwait_exception(); 1669 } 1670 } 1671 } 1672 1673 public function release_session_lock($rowid) { 1674 if (!$this->used_for_db_sessions) { 1675 return; 1676 } 1677 1678 parent::release_session_lock($rowid); 1679 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1680 $sql = "SELECT RELEASE_LOCK('$fullname')"; 1681 $this->query_start($sql, null, SQL_QUERY_AUX); 1682 $result = $this->mysqli->query($sql); 1683 $this->query_end($result); 1684 1685 if ($result) { 1686 $result->close(); 1687 } 1688 } 1689 1690 /** 1691 * Are transactions supported? 1692 * It is not responsible to run productions servers 1693 * on databases without transaction support ;-) 1694 * 1695 * MyISAM does not support support transactions. 1696 * 1697 * You can override this via the dbtransactions option. 1698 * 1699 * @return bool 1700 */ 1701 protected function transactions_supported() { 1702 if (!is_null($this->transactions_supported)) { 1703 return $this->transactions_supported; 1704 } 1705 1706 // this is all just guessing, might be better to just specify it in config.php 1707 if (isset($this->dboptions['dbtransactions'])) { 1708 $this->transactions_supported = $this->dboptions['dbtransactions']; 1709 return $this->transactions_supported; 1710 } 1711 1712 $this->transactions_supported = false; 1713 1714 $engine = $this->get_dbengine(); 1715 1716 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...) 1717 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) { 1718 $this->transactions_supported = true; 1719 } 1720 1721 return $this->transactions_supported; 1722 } 1723 1724 /** 1725 * Driver specific start of real database transaction, 1726 * this can not be used directly in code. 1727 * @return void 1728 */ 1729 protected function begin_transaction() { 1730 if (!$this->transactions_supported()) { 1731 return; 1732 } 1733 1734 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"; 1735 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1736 $result = $this->mysqli->query($sql); 1737 $this->query_end($result); 1738 1739 $sql = "START TRANSACTION"; 1740 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1741 $result = $this->mysqli->query($sql); 1742 $this->query_end($result); 1743 } 1744 1745 /** 1746 * Driver specific commit of real database transaction, 1747 * this can not be used directly in code. 1748 * @return void 1749 */ 1750 protected function commit_transaction() { 1751 if (!$this->transactions_supported()) { 1752 return; 1753 } 1754 1755 $sql = "COMMIT"; 1756 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1757 $result = $this->mysqli->query($sql); 1758 $this->query_end($result); 1759 } 1760 1761 /** 1762 * Driver specific abort of real database transaction, 1763 * this can not be used directly in code. 1764 * @return void 1765 */ 1766 protected function rollback_transaction() { 1767 if (!$this->transactions_supported()) { 1768 return; 1769 } 1770 1771 $sql = "ROLLBACK"; 1772 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1773 $result = $this->mysqli->query($sql); 1774 $this->query_end($result); 1775 1776 return true; 1777 } 1778 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |