| [ Index ] |
PHP Cross Reference of Unnamed Project |
[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 @@default_storage_engine 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['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 if ($usecache) { 580 if ($this->temptables->is_temptable($table)) { 581 if ($data = $this->get_temp_tables_cache()->get($table)) { 582 return $data; 583 } 584 } else { 585 if ($data = $this->get_metacache()->get($table)) { 586 return $data; 587 } 588 } 589 } 590 591 $structure = array(); 592 593 $sql = "SELECT column_name, data_type, character_maximum_length, numeric_precision, 594 numeric_scale, is_nullable, column_type, column_default, column_key, extra 595 FROM information_schema.columns 596 WHERE table_name = '" . $this->prefix.$table . "' 597 AND table_schema = '" . $this->dbname . "' 598 ORDER BY ordinal_position"; 599 $this->query_start($sql, null, SQL_QUERY_AUX); 600 $result = $this->mysqli->query($sql); 601 $this->query_end(true); // Don't want to throw anything here ever. MDL-30147 602 603 if ($result === false) { 604 return array(); 605 } 606 607 if ($result->num_rows > 0) { 608 // standard table exists 609 while ($rawcolumn = $result->fetch_assoc()) { 610 $info = (object)$this->get_column_info((object)$rawcolumn); 611 $structure[$info->name] = new database_column_info($info); 612 } 613 $result->close(); 614 615 } else { 616 // temporary tables are not in information schema, let's try it the old way 617 $result->close(); 618 $sql = "SHOW COLUMNS FROM {$this->prefix}$table"; 619 $this->query_start($sql, null, SQL_QUERY_AUX); 620 $result = $this->mysqli->query($sql); 621 $this->query_end(true); 622 if ($result === false) { 623 return array(); 624 } 625 while ($rawcolumn = $result->fetch_assoc()) { 626 $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER); 627 $rawcolumn->column_name = $rawcolumn->field; unset($rawcolumn->field); 628 $rawcolumn->column_type = $rawcolumn->type; unset($rawcolumn->type); 629 $rawcolumn->character_maximum_length = null; 630 $rawcolumn->numeric_precision = null; 631 $rawcolumn->numeric_scale = null; 632 $rawcolumn->is_nullable = $rawcolumn->null; unset($rawcolumn->null); 633 $rawcolumn->column_default = $rawcolumn->default; unset($rawcolumn->default); 634 $rawcolumn->column_key = $rawcolumn->key; unset($rawcolumn->default); 635 636 if (preg_match('/(enum|varchar)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 637 $rawcolumn->data_type = $matches[1]; 638 $rawcolumn->character_maximum_length = $matches[2]; 639 640 } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 641 $rawcolumn->data_type = $matches[1]; 642 $rawcolumn->numeric_precision = $matches[2]; 643 $rawcolumn->max_length = $rawcolumn->numeric_precision; 644 645 $type = strtoupper($matches[1]); 646 if ($type === 'BIGINT') { 647 $maxlength = 18; 648 } else if ($type === 'INT' or $type === 'INTEGER') { 649 $maxlength = 9; 650 } else if ($type === 'MEDIUMINT') { 651 $maxlength = 6; 652 } else if ($type === 'SMALLINT') { 653 $maxlength = 4; 654 } else if ($type === 'TINYINT') { 655 $maxlength = 2; 656 } else { 657 // This should not happen. 658 $maxlength = 0; 659 } 660 if ($maxlength < $rawcolumn->max_length) { 661 $rawcolumn->max_length = $maxlength; 662 } 663 664 } else if (preg_match('/(decimal)\((\d+),(\d+)\)/i', $rawcolumn->column_type, $matches)) { 665 $rawcolumn->data_type = $matches[1]; 666 $rawcolumn->numeric_precision = $matches[2]; 667 $rawcolumn->numeric_scale = $matches[3]; 668 669 } else if (preg_match('/(double|float)(\((\d+),(\d+)\))?/i', $rawcolumn->column_type, $matches)) { 670 $rawcolumn->data_type = $matches[1]; 671 $rawcolumn->numeric_precision = isset($matches[3]) ? $matches[3] : null; 672 $rawcolumn->numeric_scale = isset($matches[4]) ? $matches[4] : null; 673 674 } else if (preg_match('/([a-z]*text)/i', $rawcolumn->column_type, $matches)) { 675 $rawcolumn->data_type = $matches[1]; 676 $rawcolumn->character_maximum_length = -1; // unknown 677 678 } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->column_type, $matches)) { 679 $rawcolumn->data_type = $matches[1]; 680 681 } else { 682 $rawcolumn->data_type = $rawcolumn->column_type; 683 } 684 685 $info = $this->get_column_info($rawcolumn); 686 $structure[$info->name] = new database_column_info($info); 687 } 688 $result->close(); 689 } 690 691 if ($usecache) { 692 if ($this->temptables->is_temptable($table)) { 693 $this->get_temp_tables_cache()->set($table, $structure); 694 } else { 695 $this->get_metacache()->set($table, $structure); 696 } 697 } 698 699 return $structure; 700 } 701 702 /** 703 * Returns moodle column info for raw column from information schema. 704 * @param stdClass $rawcolumn 705 * @return stdClass standardised colum info 706 */ 707 private function get_column_info(stdClass $rawcolumn) { 708 $rawcolumn = (object)$rawcolumn; 709 $info = new stdClass(); 710 $info->name = $rawcolumn->column_name; 711 $info->type = $rawcolumn->data_type; 712 $info->meta_type = $this->mysqltype2moodletype($rawcolumn->data_type); 713 $info->default_value = $rawcolumn->column_default; 714 $info->has_default = !is_null($rawcolumn->column_default); 715 $info->not_null = ($rawcolumn->is_nullable === 'NO'); 716 $info->primary_key = ($rawcolumn->column_key === 'PRI'); 717 $info->binary = false; 718 $info->unsigned = null; 719 $info->auto_increment = false; 720 $info->unique = null; 721 $info->scale = null; 722 723 if ($info->meta_type === 'C') { 724 $info->max_length = $rawcolumn->character_maximum_length; 725 726 } else if ($info->meta_type === 'I') { 727 if ($info->primary_key) { 728 $info->meta_type = 'R'; 729 $info->unique = true; 730 } 731 // Return number of decimals, not bytes here. 732 $info->max_length = $rawcolumn->numeric_precision; 733 if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 734 $type = strtoupper($matches[1]); 735 if ($type === 'BIGINT') { 736 $maxlength = 18; 737 } else if ($type === 'INT' or $type === 'INTEGER') { 738 $maxlength = 9; 739 } else if ($type === 'MEDIUMINT') { 740 $maxlength = 6; 741 } else if ($type === 'SMALLINT') { 742 $maxlength = 4; 743 } else if ($type === 'TINYINT') { 744 $maxlength = 2; 745 } else { 746 // This should not happen. 747 $maxlength = 0; 748 } 749 // It is possible that display precision is different from storage type length, 750 // always use the smaller value to make sure our data fits. 751 if ($maxlength < $info->max_length) { 752 $info->max_length = $maxlength; 753 } 754 } 755 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 756 $info->auto_increment= (strpos($rawcolumn->extra, 'auto_increment') !== false); 757 758 } else if ($info->meta_type === 'N') { 759 $info->max_length = $rawcolumn->numeric_precision; 760 $info->scale = $rawcolumn->numeric_scale; 761 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 762 763 } else if ($info->meta_type === 'X') { 764 if ("$rawcolumn->character_maximum_length" === '4294967295') { // watch out for PHP max int limits! 765 // means maximum moodle size for text column, in other drivers it may also mean unknown size 766 $info->max_length = -1; 767 } else { 768 $info->max_length = $rawcolumn->character_maximum_length; 769 } 770 $info->primary_key = false; 771 772 } else if ($info->meta_type === 'B') { 773 $info->max_length = -1; 774 $info->primary_key = false; 775 $info->binary = true; 776 } 777 778 return $info; 779 } 780 781 /** 782 * Normalise column type. 783 * @param string $mysql_type 784 * @return string one character 785 * @throws dml_exception 786 */ 787 private function mysqltype2moodletype($mysql_type) { 788 $type = null; 789 790 switch(strtoupper($mysql_type)) { 791 case 'BIT': 792 $type = 'L'; 793 break; 794 795 case 'TINYINT': 796 case 'SMALLINT': 797 case 'MEDIUMINT': 798 case 'INT': 799 case 'INTEGER': 800 case 'BIGINT': 801 $type = 'I'; 802 break; 803 804 case 'FLOAT': 805 case 'DOUBLE': 806 case 'DECIMAL': 807 $type = 'N'; 808 break; 809 810 case 'CHAR': 811 case 'ENUM': 812 case 'SET': 813 case 'VARCHAR': 814 $type = 'C'; 815 break; 816 817 case 'TINYTEXT': 818 case 'TEXT': 819 case 'MEDIUMTEXT': 820 case 'LONGTEXT': 821 $type = 'X'; 822 break; 823 824 case 'BINARY': 825 case 'VARBINARY': 826 case 'BLOB': 827 case 'TINYBLOB': 828 case 'MEDIUMBLOB': 829 case 'LONGBLOB': 830 $type = 'B'; 831 break; 832 833 case 'DATE': 834 case 'TIME': 835 case 'DATETIME': 836 case 'TIMESTAMP': 837 case 'YEAR': 838 $type = 'D'; 839 break; 840 } 841 842 if (!$type) { 843 throw new dml_exception('invalidmysqlnativetype', $mysql_type); 844 } 845 return $type; 846 } 847 848 /** 849 * Normalise values based in RDBMS dependencies (booleans, LOBs...) 850 * 851 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 852 * @param mixed $value value we are going to normalise 853 * @return mixed the normalised value 854 */ 855 protected function normalise_value($column, $value) { 856 $this->detect_objects($value); 857 858 if (is_bool($value)) { // Always, convert boolean to int 859 $value = (int)$value; 860 861 } else if ($value === '') { 862 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { 863 $value = 0; // prevent '' problems in numeric fields 864 } 865 // Any float value being stored in varchar or text field is converted to string to avoid 866 // any implicit conversion by MySQL 867 } else if (is_float($value) and ($column->meta_type == 'C' or $column->meta_type == 'X')) { 868 $value = "$value"; 869 } 870 return $value; 871 } 872 873 /** 874 * Is this database compatible with utf8? 875 * @return bool 876 */ 877 public function setup_is_unicodedb() { 878 // All new tables are created with this collation, we just have to make sure it is utf8 compatible, 879 // if config table already exists it has this collation too. 880 $collation = $this->get_dbcollation(); 881 882 $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = 'utf8'"; 883 $this->query_start($sql, NULL, SQL_QUERY_AUX); 884 $result = $this->mysqli->query($sql); 885 $this->query_end($result); 886 if ($result->fetch_assoc()) { 887 $return = true; 888 } else { 889 $return = false; 890 } 891 $result->close(); 892 893 return $return; 894 } 895 896 /** 897 * Do NOT use in code, to be used by database_manager only! 898 * @param string|array $sql query 899 * @param array|null $tablenames an array of xmldb table names affected by this request. 900 * @return bool true 901 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors. 902 */ 903 public function change_database_structure($sql, $tablenames = null) { 904 $this->get_manager(); // Includes DDL exceptions classes ;-) 905 if (is_array($sql)) { 906 $sql = implode("\n;\n", $sql); 907 } 908 909 try { 910 $this->query_start($sql, null, SQL_QUERY_STRUCTURE); 911 $result = $this->mysqli->multi_query($sql); 912 if ($result === false) { 913 $this->query_end(false); 914 } 915 while ($this->mysqli->more_results()) { 916 $result = $this->mysqli->next_result(); 917 if ($result === false) { 918 $this->query_end(false); 919 } 920 } 921 $this->query_end(true); 922 } catch (ddl_change_structure_exception $e) { 923 while (@$this->mysqli->more_results()) { 924 @$this->mysqli->next_result(); 925 } 926 $this->reset_caches($tablenames); 927 throw $e; 928 } 929 930 $this->reset_caches($tablenames); 931 return true; 932 } 933 934 /** 935 * Very ugly hack which emulates bound parameters in queries 936 * because prepared statements do not use query cache. 937 */ 938 protected function emulate_bound_params($sql, array $params=null) { 939 if (empty($params)) { 940 return $sql; 941 } 942 // ok, we have verified sql statement with ? and correct number of params 943 $parts = array_reverse(explode('?', $sql)); 944 $return = array_pop($parts); 945 foreach ($params as $param) { 946 if (is_bool($param)) { 947 $return .= (int)$param; 948 } else if (is_null($param)) { 949 $return .= 'NULL'; 950 } else if (is_number($param)) { 951 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting 952 } else if (is_float($param)) { 953 $return .= $param; 954 } else { 955 $param = $this->mysqli->real_escape_string($param); 956 $return .= "'$param'"; 957 } 958 $return .= array_pop($parts); 959 } 960 return $return; 961 } 962 963 /** 964 * Execute general sql query. Should be used only when no other method suitable. 965 * Do NOT use this to make changes in db structure, use database_manager methods instead! 966 * @param string $sql query 967 * @param array $params query parameters 968 * @return bool true 969 * @throws dml_exception A DML specific exception is thrown for any errors. 970 */ 971 public function execute($sql, array $params=null) { 972 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 973 974 if (strpos($sql, ';') !== false) { 975 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); 976 } 977 978 $rawsql = $this->emulate_bound_params($sql, $params); 979 980 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 981 $result = $this->mysqli->query($rawsql); 982 $this->query_end($result); 983 984 if ($result === true) { 985 return true; 986 987 } else { 988 $result->close(); 989 return true; 990 } 991 } 992 993 /** 994 * Get a number of records as a moodle_recordset using a SQL statement. 995 * 996 * Since this method is a little less readable, use of it should be restricted to 997 * code where it's possible there might be large datasets being returned. For known 998 * small datasets use get_records_sql - it leads to simpler code. 999 * 1000 * The return type is like: 1001 * @see function get_recordset. 1002 * 1003 * @param string $sql the SQL select query to execute. 1004 * @param array $params array of sql parameters 1005 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1006 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1007 * @return moodle_recordset instance 1008 * @throws dml_exception A DML specific exception is thrown for any errors. 1009 */ 1010 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1011 1012 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1013 1014 if ($limitfrom or $limitnum) { 1015 if ($limitnum < 1) { 1016 $limitnum = "18446744073709551615"; 1017 } 1018 $sql .= " LIMIT $limitfrom, $limitnum"; 1019 } 1020 1021 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1022 $rawsql = $this->emulate_bound_params($sql, $params); 1023 1024 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1025 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables 1026 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1027 $this->query_end($result); 1028 1029 return $this->create_recordset($result); 1030 } 1031 1032 /** 1033 * Get all records from a table. 1034 * 1035 * This method works around potential memory problems and may improve performance, 1036 * this method may block access to table until the recordset is closed. 1037 * 1038 * @param string $table Name of database table. 1039 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}. 1040 * @throws dml_exception A DML specific exception is thrown for any errors. 1041 */ 1042 public function export_table_recordset($table) { 1043 $sql = $this->fix_table_names("SELECT * FROM {{$table}}"); 1044 1045 $this->query_start($sql, array(), SQL_QUERY_SELECT); 1046 // MYSQLI_STORE_RESULT may eat all memory for large tables, unfortunately MYSQLI_USE_RESULT blocks other queries. 1047 $result = $this->mysqli->query($sql, MYSQLI_USE_RESULT); 1048 $this->query_end($result); 1049 1050 return $this->create_recordset($result); 1051 } 1052 1053 protected function create_recordset($result) { 1054 return new mysqli_native_moodle_recordset($result); 1055 } 1056 1057 /** 1058 * Get a number of records as an array of objects using a SQL statement. 1059 * 1060 * Return value is like: 1061 * @see function get_records. 1062 * 1063 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1064 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1065 * returned array. 1066 * @param array $params array of sql parameters 1067 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1068 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1069 * @return array of objects, or empty array if no records were found 1070 * @throws dml_exception A DML specific exception is thrown for any errors. 1071 */ 1072 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1073 1074 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1075 1076 if ($limitfrom or $limitnum) { 1077 if ($limitnum < 1) { 1078 $limitnum = "18446744073709551615"; 1079 } 1080 $sql .= " LIMIT $limitfrom, $limitnum"; 1081 } 1082 1083 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1084 $rawsql = $this->emulate_bound_params($sql, $params); 1085 1086 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1087 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1088 $this->query_end($result); 1089 1090 $return = array(); 1091 1092 while($row = $result->fetch_assoc()) { 1093 $row = array_change_key_case($row, CASE_LOWER); 1094 $id = reset($row); 1095 if (isset($return[$id])) { 1096 $colname = key($row); 1097 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); 1098 } 1099 $return[$id] = (object)$row; 1100 } 1101 $result->close(); 1102 1103 return $return; 1104 } 1105 1106 /** 1107 * Selects records and return values (first field) as an array using a SQL statement. 1108 * 1109 * @param string $sql The SQL query 1110 * @param array $params array of sql parameters 1111 * @return array of values 1112 * @throws dml_exception A DML specific exception is thrown for any errors. 1113 */ 1114 public function get_fieldset_sql($sql, array $params=null) { 1115 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1116 $rawsql = $this->emulate_bound_params($sql, $params); 1117 1118 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1119 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1120 $this->query_end($result); 1121 1122 $return = array(); 1123 1124 while($row = $result->fetch_assoc()) { 1125 $return[] = reset($row); 1126 } 1127 $result->close(); 1128 1129 return $return; 1130 } 1131 1132 /** 1133 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1134 * @param string $table name 1135 * @param mixed $params data record as object or array 1136 * @param bool $returnit return it of inserted record 1137 * @param bool $bulk true means repeated inserts expected 1138 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1139 * @return bool|int true or new id 1140 * @throws dml_exception A DML specific exception is thrown for any errors. 1141 */ 1142 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { 1143 if (!is_array($params)) { 1144 $params = (array)$params; 1145 } 1146 1147 if ($customsequence) { 1148 if (!isset($params['id'])) { 1149 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); 1150 } 1151 $returnid = false; 1152 } else { 1153 unset($params['id']); 1154 } 1155 1156 if (empty($params)) { 1157 throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); 1158 } 1159 1160 $fields = implode(',', array_keys($params)); 1161 $qms = array_fill(0, count($params), '?'); 1162 $qms = implode(',', $qms); 1163 1164 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($qms)"; 1165 1166 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1167 $rawsql = $this->emulate_bound_params($sql, $params); 1168 1169 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1170 $result = $this->mysqli->query($rawsql); 1171 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db 1172 $this->query_end($result); 1173 1174 if (!$customsequence and !$id) { 1175 throw new dml_write_exception('unknown error fetching inserted id'); 1176 } 1177 1178 if (!$returnid) { 1179 return true; 1180 } else { 1181 return (int)$id; 1182 } 1183 } 1184 1185 /** 1186 * Insert a record into a table and return the "id" field if required. 1187 * 1188 * Some conversions and safety checks are carried out. Lobs are supported. 1189 * If the return ID isn't required, then this just reports success as true/false. 1190 * $data is an object containing needed data 1191 * @param string $table The database table to be inserted into 1192 * @param object $data A data object with values for one or more fields in the record 1193 * @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. 1194 * @return bool|int true or new id 1195 * @throws dml_exception A DML specific exception is thrown for any errors. 1196 */ 1197 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { 1198 $dataobject = (array)$dataobject; 1199 1200 $columns = $this->get_columns($table); 1201 if (empty($columns)) { 1202 throw new dml_exception('ddltablenotexist', $table); 1203 } 1204 1205 $cleaned = array(); 1206 1207 foreach ($dataobject as $field=>$value) { 1208 if ($field === 'id') { 1209 continue; 1210 } 1211 if (!isset($columns[$field])) { 1212 continue; 1213 } 1214 $column = $columns[$field]; 1215 $cleaned[$field] = $this->normalise_value($column, $value); 1216 } 1217 1218 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); 1219 } 1220 1221 /** 1222 * Insert multiple records into database as fast as possible. 1223 * 1224 * Order of inserts is maintained, but the operation is not atomic, 1225 * use transactions if necessary. 1226 * 1227 * This method is intended for inserting of large number of small objects, 1228 * do not use for huge objects with text or binary fields. 1229 * 1230 * @since Moodle 2.7 1231 * 1232 * @param string $table The database table to be inserted into 1233 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach 1234 * @return void does not return new record ids 1235 * 1236 * @throws coding_exception if data objects have different structure 1237 * @throws dml_exception A DML specific exception is thrown for any errors. 1238 */ 1239 public function insert_records($table, $dataobjects) { 1240 if (!is_array($dataobjects) and !$dataobjects instanceof Traversable) { 1241 throw new coding_exception('insert_records() passed non-traversable object'); 1242 } 1243 1244 // MySQL has a relatively small query length limit by default, 1245 // make sure 'max_allowed_packet' in my.cnf is high enough 1246 // if you change the following default... 1247 static $chunksize = null; 1248 if ($chunksize === null) { 1249 if (!empty($this->dboptions['bulkinsertsize'])) { 1250 $chunksize = (int)$this->dboptions['bulkinsertsize']; 1251 1252 } else { 1253 if (PHP_INT_SIZE === 4) { 1254 // Bad luck for Windows, we cannot do any maths with large numbers. 1255 $chunksize = 5; 1256 } else { 1257 $sql = "SHOW VARIABLES LIKE 'max_allowed_packet'"; 1258 $this->query_start($sql, null, SQL_QUERY_AUX); 1259 $result = $this->mysqli->query($sql); 1260 $this->query_end($result); 1261 $size = 0; 1262 if ($rec = $result->fetch_assoc()) { 1263 $size = $rec['Value']; 1264 } 1265 $result->close(); 1266 // Hopefully 200kb per object are enough. 1267 $chunksize = (int)($size / 200000); 1268 if ($chunksize > 50) { 1269 $chunksize = 50; 1270 } 1271 } 1272 } 1273 } 1274 1275 $columns = $this->get_columns($table, true); 1276 $fields = null; 1277 $count = 0; 1278 $chunk = array(); 1279 foreach ($dataobjects as $dataobject) { 1280 if (!is_array($dataobject) and !is_object($dataobject)) { 1281 throw new coding_exception('insert_records() passed invalid record object'); 1282 } 1283 $dataobject = (array)$dataobject; 1284 if ($fields === null) { 1285 $fields = array_keys($dataobject); 1286 $columns = array_intersect_key($columns, $dataobject); 1287 unset($columns['id']); 1288 } else if ($fields !== array_keys($dataobject)) { 1289 throw new coding_exception('All dataobjects in insert_records() must have the same structure!'); 1290 } 1291 1292 $count++; 1293 $chunk[] = $dataobject; 1294 1295 if ($count === $chunksize) { 1296 $this->insert_chunk($table, $chunk, $columns); 1297 $chunk = array(); 1298 $count = 0; 1299 } 1300 } 1301 1302 if ($count) { 1303 $this->insert_chunk($table, $chunk, $columns); 1304 } 1305 } 1306 1307 /** 1308 * Insert records in chunks. 1309 * 1310 * Note: can be used only from insert_records(). 1311 * 1312 * @param string $table 1313 * @param array $chunk 1314 * @param database_column_info[] $columns 1315 */ 1316 protected function insert_chunk($table, array $chunk, array $columns) { 1317 $fieldssql = '('.implode(',', array_keys($columns)).')'; 1318 1319 $valuessql = '('.implode(',', array_fill(0, count($columns), '?')).')'; 1320 $valuessql = implode(',', array_fill(0, count($chunk), $valuessql)); 1321 1322 $params = array(); 1323 foreach ($chunk as $dataobject) { 1324 foreach ($columns as $field => $column) { 1325 $params[] = $this->normalise_value($column, $dataobject[$field]); 1326 } 1327 } 1328 1329 $sql = "INSERT INTO {$this->prefix}$table $fieldssql VALUES $valuessql"; 1330 1331 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1332 $rawsql = $this->emulate_bound_params($sql, $params); 1333 1334 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1335 $result = $this->mysqli->query($rawsql); 1336 $this->query_end($result); 1337 } 1338 1339 /** 1340 * Import a record into a table, id field is required. 1341 * Safety checks are NOT carried out. Lobs are supported. 1342 * 1343 * @param string $table name of database table to be inserted into 1344 * @param object $dataobject A data object with values for one or more fields in the record 1345 * @return bool true 1346 * @throws dml_exception A DML specific exception is thrown for any errors. 1347 */ 1348 public function import_record($table, $dataobject) { 1349 $dataobject = (array)$dataobject; 1350 1351 $columns = $this->get_columns($table); 1352 $cleaned = array(); 1353 1354 foreach ($dataobject as $field=>$value) { 1355 if (!isset($columns[$field])) { 1356 continue; 1357 } 1358 $cleaned[$field] = $value; 1359 } 1360 1361 return $this->insert_record_raw($table, $cleaned, false, true, true); 1362 } 1363 1364 /** 1365 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1366 * @param string $table name 1367 * @param mixed $params data record as object or array 1368 * @param bool true means repeated updates expected 1369 * @return bool true 1370 * @throws dml_exception A DML specific exception is thrown for any errors. 1371 */ 1372 public function update_record_raw($table, $params, $bulk=false) { 1373 $params = (array)$params; 1374 1375 if (!isset($params['id'])) { 1376 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); 1377 } 1378 $id = $params['id']; 1379 unset($params['id']); 1380 1381 if (empty($params)) { 1382 throw new coding_exception('moodle_database::update_record_raw() no fields found.'); 1383 } 1384 1385 $sets = array(); 1386 foreach ($params as $field=>$value) { 1387 $sets[] = "$field = ?"; 1388 } 1389 1390 $params[] = $id; // last ? in WHERE condition 1391 1392 $sets = implode(',', $sets); 1393 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=?"; 1394 1395 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1396 $rawsql = $this->emulate_bound_params($sql, $params); 1397 1398 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1399 $result = $this->mysqli->query($rawsql); 1400 $this->query_end($result); 1401 1402 return true; 1403 } 1404 1405 /** 1406 * Update a record in a table 1407 * 1408 * $dataobject is an object containing needed data 1409 * Relies on $dataobject having a variable "id" to 1410 * specify the record to update 1411 * 1412 * @param string $table The database table to be checked against. 1413 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. 1414 * @param bool true means repeated updates expected 1415 * @return bool true 1416 * @throws dml_exception A DML specific exception is thrown for any errors. 1417 */ 1418 public function update_record($table, $dataobject, $bulk=false) { 1419 $dataobject = (array)$dataobject; 1420 1421 $columns = $this->get_columns($table); 1422 $cleaned = array(); 1423 1424 foreach ($dataobject as $field=>$value) { 1425 if (!isset($columns[$field])) { 1426 continue; 1427 } 1428 $column = $columns[$field]; 1429 $cleaned[$field] = $this->normalise_value($column, $value); 1430 } 1431 1432 return $this->update_record_raw($table, $cleaned, $bulk); 1433 } 1434 1435 /** 1436 * Set a single field in every table record which match a particular WHERE clause. 1437 * 1438 * @param string $table The database table to be checked against. 1439 * @param string $newfield the field to set. 1440 * @param string $newvalue the value to set the field to. 1441 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1442 * @param array $params array of sql parameters 1443 * @return bool true 1444 * @throws dml_exception A DML specific exception is thrown for any errors. 1445 */ 1446 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { 1447 if ($select) { 1448 $select = "WHERE $select"; 1449 } 1450 if (is_null($params)) { 1451 $params = array(); 1452 } 1453 list($select, $params, $type) = $this->fix_sql_params($select, $params); 1454 1455 // Get column metadata 1456 $columns = $this->get_columns($table); 1457 $column = $columns[$newfield]; 1458 1459 $normalised_value = $this->normalise_value($column, $newvalue); 1460 1461 if (is_null($normalised_value)) { 1462 $newfield = "$newfield = NULL"; 1463 } else { 1464 $newfield = "$newfield = ?"; 1465 array_unshift($params, $normalised_value); 1466 } 1467 $sql = "UPDATE {$this->prefix}$table SET $newfield $select"; 1468 $rawsql = $this->emulate_bound_params($sql, $params); 1469 1470 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1471 $result = $this->mysqli->query($rawsql); 1472 $this->query_end($result); 1473 1474 return true; 1475 } 1476 1477 /** 1478 * Delete one or more records from a table which match a particular WHERE clause. 1479 * 1480 * @param string $table The database table to be checked against. 1481 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1482 * @param array $params array of sql parameters 1483 * @return bool true 1484 * @throws dml_exception A DML specific exception is thrown for any errors. 1485 */ 1486 public function delete_records_select($table, $select, array $params=null) { 1487 if ($select) { 1488 $select = "WHERE $select"; 1489 } 1490 $sql = "DELETE FROM {$this->prefix}$table $select"; 1491 1492 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1493 $rawsql = $this->emulate_bound_params($sql, $params); 1494 1495 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1496 $result = $this->mysqli->query($rawsql); 1497 $this->query_end($result); 1498 1499 return true; 1500 } 1501 1502 public function sql_cast_char2int($fieldname, $text=false) { 1503 return ' CAST(' . $fieldname . ' AS SIGNED) '; 1504 } 1505 1506 public function sql_cast_char2real($fieldname, $text=false) { 1507 // Set to 65 (max mysql 5.5 precision) with 7 as scale 1508 // because we must ensure at least 6 decimal positions 1509 // per casting given that postgres is casting to that scale (::real::). 1510 // Can be raised easily but that must be done in all DBs and tests. 1511 return ' CAST(' . $fieldname . ' AS DECIMAL(65,7)) '; 1512 } 1513 1514 /** 1515 * Returns 'LIKE' part of a query. 1516 * 1517 * Note that mysql does not support $casesensitive = true and $accentsensitive = false. 1518 * More information in http://bugs.mysql.com/bug.php?id=19567. 1519 * 1520 * @param string $fieldname usually name of the table column 1521 * @param string $param usually bound query parameter (?, :named) 1522 * @param bool $casesensitive use case sensitive search 1523 * @param bool $accensensitive use accent sensitive search (ignored if $casesensitive is true) 1524 * @param bool $notlike true means "NOT LIKE" 1525 * @param string $escapechar escape char for '%' and '_' 1526 * @return string SQL code fragment 1527 */ 1528 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 1529 if (strpos($param, '%') !== false) { 1530 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 1531 } 1532 $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\' 1533 1534 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 1535 1536 if ($casesensitive) { 1537 // Current MySQL versions do not support case sensitive and accent insensitive. 1538 return "$fieldname $LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'"; 1539 1540 } else if ($accentsensitive) { 1541 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case. 1542 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'"; 1543 1544 } else { 1545 // Case insensitive and accent insensitive. 1546 $collation = ''; 1547 if ($this->get_dbcollation() == 'utf8_bin') { 1548 // Force a case insensitive comparison if using utf8_bin. 1549 $collation = 'COLLATE utf8_unicode_ci'; 1550 } 1551 1552 return "$fieldname $LIKE $param $collation ESCAPE '$escapechar'"; 1553 } 1554 } 1555 1556 /** 1557 * Returns the proper SQL to do CONCAT between the elements passed 1558 * Can take many parameters 1559 * 1560 * @param string $str,... 1 or more fields/strings to concat 1561 * 1562 * @return string The concat sql 1563 */ 1564 public function sql_concat() { 1565 $arr = func_get_args(); 1566 $s = implode(', ', $arr); 1567 if ($s === '') { 1568 return "''"; 1569 } 1570 return "CONCAT($s)"; 1571 } 1572 1573 /** 1574 * Returns the proper SQL to do CONCAT between the elements passed 1575 * with a given separator 1576 * 1577 * @param string $separator The string to use as the separator 1578 * @param array $elements An array of items to concatenate 1579 * @return string The concat SQL 1580 */ 1581 public function sql_concat_join($separator="' '", $elements=array()) { 1582 $s = implode(', ', $elements); 1583 1584 if ($s === '') { 1585 return "''"; 1586 } 1587 return "CONCAT_WS($separator, $s)"; 1588 } 1589 1590 /** 1591 * Returns the SQL text to be used to calculate the length in characters of one expression. 1592 * @param string fieldname or expression to calculate its length in characters. 1593 * @return string the piece of SQL code to be used in the statement. 1594 */ 1595 public function sql_length($fieldname) { 1596 return ' CHAR_LENGTH(' . $fieldname . ')'; 1597 } 1598 1599 /** 1600 * Does this driver support regex syntax when searching 1601 */ 1602 public function sql_regex_supported() { 1603 return true; 1604 } 1605 1606 /** 1607 * Return regex positive or negative match sql 1608 * @param bool $positivematch 1609 * @return string or empty if not supported 1610 */ 1611 public function sql_regex($positivematch=true) { 1612 return $positivematch ? 'REGEXP' : 'NOT REGEXP'; 1613 } 1614 1615 /** 1616 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED. 1617 * 1618 * @deprecated since 2.3 1619 * @param string $fieldname The name of the field to be cast 1620 * @return string The piece of SQL code to be used in your statement. 1621 */ 1622 public function sql_cast_2signed($fieldname) { 1623 return ' CAST(' . $fieldname . ' AS SIGNED) '; 1624 } 1625 1626 /** 1627 * Returns the SQL that allows to find intersection of two or more queries 1628 * 1629 * @since Moodle 2.8 1630 * 1631 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields 1632 * @param string $fields comma-separated list of fields 1633 * @return string SQL query that will return only values that are present in each of selects 1634 */ 1635 public function sql_intersect($selects, $fields) { 1636 if (count($selects) <= 1) { 1637 return parent::sql_intersect($selects, $fields); 1638 } 1639 $fields = preg_replace('/\s/', '', $fields); 1640 static $aliascnt = 0; 1641 $falias = 'intsctal'.($aliascnt++); 1642 $rv = "SELECT $falias.". 1643 preg_replace('/,/', ','.$falias.'.', $fields). 1644 " FROM ($selects[0]) $falias"; 1645 for ($i = 1; $i < count($selects); $i++) { 1646 $alias = 'intsctal'.($aliascnt++); 1647 $rv .= " JOIN (".$selects[$i].") $alias ON ". 1648 join(' AND ', 1649 array_map( 1650 create_function('$a', 'return "'.$falias.'.$a = '.$alias.'.$a";'), 1651 preg_split('/,/', $fields)) 1652 ); 1653 } 1654 return $rv; 1655 } 1656 1657 /** 1658 * Does this driver support tool_replace? 1659 * 1660 * @since Moodle 2.6.1 1661 * @return bool 1662 */ 1663 public function replace_all_text_supported() { 1664 return true; 1665 } 1666 1667 public function session_lock_supported() { 1668 return true; 1669 } 1670 1671 /** 1672 * Obtain session lock 1673 * @param int $rowid id of the row with session record 1674 * @param int $timeout max allowed time to wait for the lock in seconds 1675 * @return void 1676 */ 1677 public function get_session_lock($rowid, $timeout) { 1678 parent::get_session_lock($rowid, $timeout); 1679 1680 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1681 $sql = "SELECT GET_LOCK('$fullname', $timeout)"; 1682 $this->query_start($sql, null, SQL_QUERY_AUX); 1683 $result = $this->mysqli->query($sql); 1684 $this->query_end($result); 1685 1686 if ($result) { 1687 $arr = $result->fetch_assoc(); 1688 $result->close(); 1689 1690 if (reset($arr) == 1) { 1691 return; 1692 } else { 1693 throw new dml_sessionwait_exception(); 1694 } 1695 } 1696 } 1697 1698 public function release_session_lock($rowid) { 1699 if (!$this->used_for_db_sessions) { 1700 return; 1701 } 1702 1703 parent::release_session_lock($rowid); 1704 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1705 $sql = "SELECT RELEASE_LOCK('$fullname')"; 1706 $this->query_start($sql, null, SQL_QUERY_AUX); 1707 $result = $this->mysqli->query($sql); 1708 $this->query_end($result); 1709 1710 if ($result) { 1711 $result->close(); 1712 } 1713 } 1714 1715 /** 1716 * Are transactions supported? 1717 * It is not responsible to run productions servers 1718 * on databases without transaction support ;-) 1719 * 1720 * MyISAM does not support support transactions. 1721 * 1722 * You can override this via the dbtransactions option. 1723 * 1724 * @return bool 1725 */ 1726 protected function transactions_supported() { 1727 if (!is_null($this->transactions_supported)) { 1728 return $this->transactions_supported; 1729 } 1730 1731 // this is all just guessing, might be better to just specify it in config.php 1732 if (isset($this->dboptions['dbtransactions'])) { 1733 $this->transactions_supported = $this->dboptions['dbtransactions']; 1734 return $this->transactions_supported; 1735 } 1736 1737 $this->transactions_supported = false; 1738 1739 $engine = $this->get_dbengine(); 1740 1741 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...) 1742 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) { 1743 $this->transactions_supported = true; 1744 } 1745 1746 return $this->transactions_supported; 1747 } 1748 1749 /** 1750 * Driver specific start of real database transaction, 1751 * this can not be used directly in code. 1752 * @return void 1753 */ 1754 protected function begin_transaction() { 1755 if (!$this->transactions_supported()) { 1756 return; 1757 } 1758 1759 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"; 1760 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1761 $result = $this->mysqli->query($sql); 1762 $this->query_end($result); 1763 1764 $sql = "START TRANSACTION"; 1765 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1766 $result = $this->mysqli->query($sql); 1767 $this->query_end($result); 1768 } 1769 1770 /** 1771 * Driver specific commit of real database transaction, 1772 * this can not be used directly in code. 1773 * @return void 1774 */ 1775 protected function commit_transaction() { 1776 if (!$this->transactions_supported()) { 1777 return; 1778 } 1779 1780 $sql = "COMMIT"; 1781 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1782 $result = $this->mysqli->query($sql); 1783 $this->query_end($result); 1784 } 1785 1786 /** 1787 * Driver specific abort of real database transaction, 1788 * this can not be used directly in code. 1789 * @return void 1790 */ 1791 protected function rollback_transaction() { 1792 if (!$this->transactions_supported()) { 1793 return; 1794 } 1795 1796 $sql = "ROLLBACK"; 1797 $this->query_start($sql, NULL, SQL_QUERY_AUX); 1798 $result = $this->mysqli->query($sql); 1799 $this->query_end($result); 1800 1801 return true; 1802 } 1803 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |