| [ 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 oci 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__.'/oci_native_moodle_recordset.php'); 29 require_once (__DIR__.'/oci_native_moodle_temptables.php'); 30 31 /** 32 * Native oci class representing moodle database interface. 33 * 34 * One complete reference for PHP + OCI: 35 * http://www.oracle.com/technology/tech/php/underground-php-oracle-manual.html 36 * 37 * @package core_dml 38 * @copyright 2008 Petr Skoda (http://skodak.org) 39 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 40 */ 41 class oci_native_moodle_database extends moodle_database { 42 43 protected $oci = null; 44 45 /** @var To store stmt errors and enable get_last_error() to detect them.*/ 46 private $last_stmt_error = null; 47 /** @var Default value initialised in connect method, we need the driver to be present.*/ 48 private $commit_status = null; 49 50 /** @var To handle oci driver default verbosity.*/ 51 private $last_error_reporting; 52 /** @var To store unique_session_id. Needed for temp tables unique naming.*/ 53 private $unique_session_id; 54 55 /** 56 * Detects if all needed PHP stuff installed. 57 * Note: can be used before connect() 58 * @return mixed true if ok, string if something 59 */ 60 public function driver_installed() { 61 if (!extension_loaded('oci8')) { 62 return get_string('ociextensionisnotpresentinphp', 'install'); 63 } 64 return true; 65 } 66 67 /** 68 * Returns database family type - describes SQL dialect 69 * Note: can be used before connect() 70 * @return string db family name (mysql, postgres, mssql, oracle, etc.) 71 */ 72 public function get_dbfamily() { 73 return 'oracle'; 74 } 75 76 /** 77 * Returns more specific database driver type 78 * Note: can be used before connect() 79 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv 80 */ 81 protected function get_dbtype() { 82 return 'oci'; 83 } 84 85 /** 86 * Returns general database library name 87 * Note: can be used before connect() 88 * @return string db type pdo, native 89 */ 90 protected function get_dblibrary() { 91 return 'native'; 92 } 93 94 /** 95 * Returns localised database type name 96 * Note: can be used before connect() 97 * @return string 98 */ 99 public function get_name() { 100 return get_string('nativeoci', 'install'); 101 } 102 103 /** 104 * Returns localised database configuration help. 105 * Note: can be used before connect() 106 * @return string 107 */ 108 public function get_configuration_help() { 109 return get_string('nativeocihelp', 'install'); 110 } 111 112 /** 113 * Diagnose database and tables, this function is used 114 * to verify database and driver settings, db engine types, etc. 115 * 116 * @return string null means everything ok, string means problem found. 117 */ 118 public function diagnose() { 119 return null; 120 } 121 122 /** 123 * Connect to db 124 * Must be called before other methods. 125 * @param string $dbhost The database host. 126 * @param string $dbuser The database username. 127 * @param string $dbpass The database username's password. 128 * @param string $dbname The name of the database being connected to. 129 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used 130 * @param array $dboptions driver specific options 131 * @return bool true 132 * @throws dml_connection_exception if error 133 */ 134 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { 135 if ($prefix == '' and !$this->external) { 136 //Enforce prefixes for everybody but mysql 137 throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily()); 138 } 139 if (!$this->external and strlen($prefix) > 2) { 140 //Max prefix length for Oracle is 2cc 141 $a = (object)array('dbfamily'=>'oracle', 'maxlength'=>2); 142 throw new dml_exception('prefixtoolong', $a); 143 } 144 145 $driverstatus = $this->driver_installed(); 146 147 if ($driverstatus !== true) { 148 throw new dml_exception('dbdriverproblem', $driverstatus); 149 } 150 151 // Autocommit ON by default. 152 // Switching to OFF (OCI_DEFAULT), when playing with transactions 153 // please note this thing is not defined if oracle driver not present in PHP 154 // which means it can not be used as default value of object property! 155 $this->commit_status = OCI_COMMIT_ON_SUCCESS; 156 157 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); 158 unset($this->dboptions['dbsocket']); 159 160 // NOTE: use of ', ", / and \ is very problematic, even native oracle tools seem to have 161 // problems with these, so just forget them and do not report problems into tracker... 162 163 if (empty($this->dbhost)) { 164 // old style full address (TNS) 165 $dbstring = $this->dbname; 166 } else { 167 if (empty($this->dboptions['dbport'])) { 168 $this->dboptions['dbport'] = 1521; 169 } 170 $dbstring = '//'.$this->dbhost.':'.$this->dboptions['dbport'].'/'.$this->dbname; 171 } 172 173 ob_start(); 174 if (empty($this->dboptions['dbpersist'])) { 175 $this->oci = oci_new_connect($this->dbuser, $this->dbpass, $dbstring, 'AL32UTF8'); 176 } else { 177 $this->oci = oci_pconnect($this->dbuser, $this->dbpass, $dbstring, 'AL32UTF8'); 178 } 179 $dberr = ob_get_contents(); 180 ob_end_clean(); 181 182 183 if ($this->oci === false) { 184 $this->oci = null; 185 $e = oci_error(); 186 if (isset($e['message'])) { 187 $dberr = $e['message']; 188 } 189 throw new dml_connection_exception($dberr); 190 } 191 192 // Make sure moodle package is installed - now required. 193 if (!$this->oci_package_installed()) { 194 try { 195 $this->attempt_oci_package_install(); 196 } catch (Exception $e) { 197 // Ignore problems, only the result counts, 198 // admins have to fix it manually if necessary. 199 } 200 if (!$this->oci_package_installed()) { 201 throw new dml_exception('dbdriverproblem', 'Oracle PL/SQL Moodle support package MOODLELIB is not installed! Database administrator has to execute /lib/dml/oci_native_moodle_package.sql script.'); 202 } 203 } 204 205 // get unique session id, to be used later for temp tables stuff 206 $sql = 'SELECT DBMS_SESSION.UNIQUE_SESSION_ID() FROM DUAL'; 207 $this->query_start($sql, null, SQL_QUERY_AUX); 208 $stmt = $this->parse_query($sql); 209 $result = oci_execute($stmt, $this->commit_status); 210 $this->query_end($result, $stmt); 211 $records = null; 212 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); 213 oci_free_statement($stmt); 214 $this->unique_session_id = reset($records[0]); 215 216 //note: do not send "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'" ! 217 // instead fix our PHP code to convert "," to "." properly! 218 219 // Connection stabilised and configured, going to instantiate the temptables controller 220 $this->temptables = new oci_native_moodle_temptables($this, $this->unique_session_id); 221 222 return true; 223 } 224 225 /** 226 * Close database connection and release all resources 227 * and memory (especially circular memory references). 228 * Do NOT use connect() again, create a new instance if needed. 229 */ 230 public function dispose() { 231 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection 232 if ($this->oci) { 233 oci_close($this->oci); 234 $this->oci = null; 235 } 236 } 237 238 239 /** 240 * Called before each db query. 241 * @param string $sql 242 * @param array array of parameters 243 * @param int $type type of query 244 * @param mixed $extrainfo driver specific extra information 245 * @return void 246 */ 247 protected function query_start($sql, array $params=null, $type, $extrainfo=null) { 248 parent::query_start($sql, $params, $type, $extrainfo); 249 // oci driver tents to send debug to output, we do not need that ;-) 250 $this->last_error_reporting = error_reporting(0); 251 } 252 253 /** 254 * Called immediately after each db query. 255 * @param mixed db specific result 256 * @return void 257 */ 258 protected function query_end($result, $stmt=null) { 259 // reset original debug level 260 error_reporting($this->last_error_reporting); 261 if ($stmt and $result === false) { 262 // Look for stmt error and store it 263 if (is_resource($stmt)) { 264 $e = oci_error($stmt); 265 if ($e !== false) { 266 $this->last_stmt_error = $e['message']; 267 } 268 } 269 oci_free_statement($stmt); 270 } 271 parent::query_end($result); 272 } 273 274 /** 275 * Returns database server info array 276 * @return array Array containing 'description' and 'version' info 277 */ 278 public function get_server_info() { 279 static $info = null; // TODO: move to real object property 280 281 if (is_null($info)) { 282 $this->query_start("--oci_server_version()", null, SQL_QUERY_AUX); 283 $description = oci_server_version($this->oci); 284 $this->query_end(true); 285 preg_match('/(\d+\.)+\d+/', $description, $matches); 286 $info = array('description'=>$description, 'version'=>$matches[0]); 287 } 288 289 return $info; 290 } 291 292 /** 293 * Converts short table name {tablename} to real table name 294 * supporting temp tables ($this->unique_session_id based) if detected 295 * 296 * @param string sql 297 * @return string sql 298 */ 299 protected function fix_table_names($sql) { 300 if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/', $sql, $matches)) { 301 foreach($matches[0] as $key=>$match) { 302 $name = $matches[1][$key]; 303 if ($this->temptables && $this->temptables->is_temptable($name)) { 304 $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql); 305 } else { 306 $sql = str_replace($match, $this->prefix.$name, $sql); 307 } 308 } 309 } 310 return $sql; 311 } 312 313 /** 314 * Returns supported query parameter types 315 * @return int bitmask of accepted SQL_PARAMS_* 316 */ 317 protected function allowed_param_types() { 318 return SQL_PARAMS_NAMED; 319 } 320 321 /** 322 * Returns last error reported by database engine. 323 * @return string error message 324 */ 325 public function get_last_error() { 326 $error = false; 327 // First look for any previously saved stmt error 328 if (!empty($this->last_stmt_error)) { 329 $error = $this->last_stmt_error; 330 $this->last_stmt_error = null; 331 } else { // Now try connection error 332 $e = oci_error($this->oci); 333 if ($e !== false) { 334 $error = $e['message']; 335 } 336 } 337 return $error; 338 } 339 340 /** 341 * Prepare the statement for execution 342 * @throws dml_connection_exception 343 * @param string $sql 344 * @return resource 345 */ 346 protected function parse_query($sql) { 347 $stmt = oci_parse($this->oci, $sql); 348 if ($stmt == false) { 349 throw new dml_connection_exception('Can not parse sql query'); //TODO: maybe add better info 350 } 351 return $stmt; 352 } 353 354 /** 355 * Make sure there are no reserved words in param names... 356 * @param string $sql 357 * @param array $params 358 * @return array ($sql, $params) updated query and parameters 359 */ 360 protected function tweak_param_names($sql, array $params) { 361 if (empty($params)) { 362 return array($sql, $params); 363 } 364 365 $newparams = array(); 366 $searcharr = array(); // search => replace pairs 367 foreach ($params as $name => $value) { 368 // Keep the name within the 30 chars limit always (prefixing/replacing) 369 if (strlen($name) <= 28) { 370 $newname = 'o_' . $name; 371 } else { 372 $newname = 'o_' . substr($name, 2); 373 } 374 $newparams[$newname] = $value; 375 $searcharr[':' . $name] = ':' . $newname; 376 } 377 // sort by length desc to avoid potential str_replace() overlap 378 uksort($searcharr, array('oci_native_moodle_database', 'compare_by_length_desc')); 379 380 $sql = str_replace(array_keys($searcharr), $searcharr, $sql); 381 return array($sql, $newparams); 382 } 383 384 /** 385 * Return tables in database WITHOUT current prefix 386 * @param bool $usecache if true, returns list of cached tables. 387 * @return array of table names in lowercase and without prefix 388 */ 389 public function get_tables($usecache=true) { 390 if ($usecache and $this->tables !== null) { 391 return $this->tables; 392 } 393 $this->tables = array(); 394 $prefix = str_replace('_', "\\_", strtoupper($this->prefix)); 395 $sql = "SELECT TABLE_NAME 396 FROM CAT 397 WHERE TABLE_TYPE='TABLE' 398 AND TABLE_NAME NOT LIKE 'BIN\$%' 399 AND TABLE_NAME LIKE '$prefix%' ESCAPE '\\'"; 400 $this->query_start($sql, null, SQL_QUERY_AUX); 401 $stmt = $this->parse_query($sql); 402 $result = oci_execute($stmt, $this->commit_status); 403 $this->query_end($result, $stmt); 404 $records = null; 405 oci_fetch_all($stmt, $records, 0, -1, OCI_ASSOC); 406 oci_free_statement($stmt); 407 $records = array_map('strtolower', $records['TABLE_NAME']); 408 foreach ($records as $tablename) { 409 if ($this->prefix !== false && $this->prefix !== '') { 410 if (strpos($tablename, $this->prefix) !== 0) { 411 continue; 412 } 413 $tablename = substr($tablename, strlen($this->prefix)); 414 } 415 $this->tables[$tablename] = $tablename; 416 } 417 418 // Add the currently available temptables 419 $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); 420 421 return $this->tables; 422 } 423 424 /** 425 * Return table indexes - everything lowercased. 426 * @param string $table The table we want to get indexes from. 427 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed 428 */ 429 public function get_indexes($table) { 430 $indexes = array(); 431 $tablename = strtoupper($this->prefix.$table); 432 433 $sql = "SELECT i.INDEX_NAME, i.UNIQUENESS, c.COLUMN_POSITION, c.COLUMN_NAME, ac.CONSTRAINT_TYPE 434 FROM ALL_INDEXES i 435 JOIN ALL_IND_COLUMNS c ON c.INDEX_NAME=i.INDEX_NAME 436 LEFT JOIN ALL_CONSTRAINTS ac ON (ac.TABLE_NAME=i.TABLE_NAME AND ac.CONSTRAINT_NAME=i.INDEX_NAME AND ac.CONSTRAINT_TYPE='P') 437 WHERE i.TABLE_NAME = '$tablename' 438 ORDER BY i.INDEX_NAME, c.COLUMN_POSITION"; 439 440 $stmt = $this->parse_query($sql); 441 $result = oci_execute($stmt, $this->commit_status); 442 $this->query_end($result, $stmt); 443 $records = null; 444 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); 445 oci_free_statement($stmt); 446 447 foreach ($records as $record) { 448 if ($record['CONSTRAINT_TYPE'] === 'P') { 449 //ignore for now; 450 continue; 451 } 452 $indexname = strtolower($record['INDEX_NAME']); 453 if (!isset($indexes[$indexname])) { 454 $indexes[$indexname] = array('primary' => ($record['CONSTRAINT_TYPE'] === 'P'), 455 'unique' => ($record['UNIQUENESS'] === 'UNIQUE'), 456 'columns' => array()); 457 } 458 $indexes[$indexname]['columns'][] = strtolower($record['COLUMN_NAME']); 459 } 460 461 return $indexes; 462 } 463 464 /** 465 * Returns detailed information about columns in table. This information is cached internally. 466 * @param string $table name 467 * @param bool $usecache 468 * @return array array of database_column_info objects indexed with column names 469 */ 470 public function get_columns($table, $usecache=true) { 471 472 if ($usecache) { 473 $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash()); 474 $cache = cache::make('core', 'databasemeta', $properties); 475 if ($data = $cache->get($table)) { 476 return $data; 477 } 478 } 479 480 if (!$table) { // table not specified, return empty array directly 481 return array(); 482 } 483 484 $structure = array(); 485 486 // We give precedence to CHAR_LENGTH for VARCHAR2 columns over WIDTH because the former is always 487 // BYTE based and, for cross-db operations, we want CHAR based results. See MDL-29415 488 // Instead of guessing sequence based exclusively on name, check tables against user_triggers to 489 // ensure the table has a 'before each row' trigger to assume 'id' is auto_increment. MDL-32365 490 $sql = "SELECT CNAME, COLTYPE, nvl(CHAR_LENGTH, WIDTH) AS WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL, 491 DECODE(NVL(TRIGGER_NAME, '0'), '0', '0', '1') HASTRIGGER 492 FROM COL c 493 LEFT JOIN USER_TAB_COLUMNS u ON (u.TABLE_NAME = c.TNAME AND u.COLUMN_NAME = c.CNAME AND u.DATA_TYPE = 'VARCHAR2') 494 LEFT JOIN USER_TRIGGERS t ON (t.TABLE_NAME = c.TNAME AND TRIGGER_TYPE = 'BEFORE EACH ROW' AND c.CNAME = 'ID') 495 WHERE TNAME = UPPER('{" . $table . "}') 496 ORDER BY COLNO"; 497 498 list($sql, $params, $type) = $this->fix_sql_params($sql, null); 499 500 $this->query_start($sql, null, SQL_QUERY_AUX); 501 $stmt = $this->parse_query($sql); 502 $result = oci_execute($stmt, $this->commit_status); 503 $this->query_end($result, $stmt); 504 $records = null; 505 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); 506 oci_free_statement($stmt); 507 508 if (!$records) { 509 return array(); 510 } 511 foreach ($records as $rawcolumn) { 512 $rawcolumn = (object)$rawcolumn; 513 514 $info = new stdClass(); 515 $info->name = strtolower($rawcolumn->CNAME); 516 $info->auto_increment = ((int)$rawcolumn->HASTRIGGER) ? true : false; 517 $matches = null; 518 519 if ($rawcolumn->COLTYPE === 'VARCHAR2' 520 or $rawcolumn->COLTYPE === 'VARCHAR' 521 or $rawcolumn->COLTYPE === 'NVARCHAR2' 522 or $rawcolumn->COLTYPE === 'NVARCHAR' 523 or $rawcolumn->COLTYPE === 'CHAR' 524 or $rawcolumn->COLTYPE === 'NCHAR') { 525 $info->type = $rawcolumn->COLTYPE; 526 $info->meta_type = 'C'; 527 $info->max_length = $rawcolumn->WIDTH; 528 $info->scale = null; 529 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL'); 530 $info->has_default = !is_null($rawcolumn->DEFAULTVAL); 531 if ($info->has_default) { 532 533 // this is hacky :-( 534 if ($rawcolumn->DEFAULTVAL === 'NULL') { 535 $info->default_value = null; 536 } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space 537 $info->default_value = ""; 538 } else if ($rawcolumn->DEFAULTVAL === "' '") { // Sometimes it's stored without trailing space 539 $info->default_value = ""; 540 } else { 541 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space 542 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim '' 543 } 544 } else { 545 $info->default_value = null; 546 } 547 $info->primary_key = false; 548 $info->binary = false; 549 $info->unsigned = null; 550 $info->unique = null; 551 552 } else if ($rawcolumn->COLTYPE === 'NUMBER') { 553 $info->type = $rawcolumn->COLTYPE; 554 $info->max_length = $rawcolumn->PRECISION; 555 $info->binary = false; 556 if (!is_null($rawcolumn->SCALE) && $rawcolumn->SCALE == 0) { // null in oracle scale allows decimals => not integer 557 // integer 558 if ($info->name === 'id') { 559 $info->primary_key = true; 560 $info->meta_type = 'R'; 561 $info->unique = true; 562 $info->has_default = false; 563 } else { 564 $info->primary_key = false; 565 $info->meta_type = 'I'; 566 $info->unique = null; 567 } 568 $info->scale = 0; 569 570 } else { 571 //float 572 $info->meta_type = 'N'; 573 $info->primary_key = false; 574 $info->unsigned = null; 575 $info->unique = null; 576 $info->scale = $rawcolumn->SCALE; 577 } 578 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL'); 579 $info->has_default = !is_null($rawcolumn->DEFAULTVAL); 580 if ($info->has_default) { 581 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space 582 } else { 583 $info->default_value = null; 584 } 585 586 } else if ($rawcolumn->COLTYPE === 'FLOAT') { 587 $info->type = $rawcolumn->COLTYPE; 588 $info->max_length = (int)($rawcolumn->PRECISION * 3.32193); 589 $info->primary_key = false; 590 $info->meta_type = 'N'; 591 $info->unique = null; 592 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL'); 593 $info->has_default = !is_null($rawcolumn->DEFAULTVAL); 594 if ($info->has_default) { 595 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space 596 } else { 597 $info->default_value = null; 598 } 599 600 } else if ($rawcolumn->COLTYPE === 'CLOB' 601 or $rawcolumn->COLTYPE === 'NCLOB') { 602 $info->type = $rawcolumn->COLTYPE; 603 $info->meta_type = 'X'; 604 $info->max_length = -1; 605 $info->scale = null; 606 $info->scale = null; 607 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL'); 608 $info->has_default = !is_null($rawcolumn->DEFAULTVAL); 609 if ($info->has_default) { 610 // this is hacky :-( 611 if ($rawcolumn->DEFAULTVAL === 'NULL') { 612 $info->default_value = null; 613 } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space 614 $info->default_value = ""; 615 } else if ($rawcolumn->DEFAULTVAL === "' '") { // Other times it's stored without trailing space 616 $info->default_value = ""; 617 } else { 618 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space 619 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim '' 620 } 621 } else { 622 $info->default_value = null; 623 } 624 $info->primary_key = false; 625 $info->binary = false; 626 $info->unsigned = null; 627 $info->unique = null; 628 629 } else if ($rawcolumn->COLTYPE === 'BLOB') { 630 $info->type = $rawcolumn->COLTYPE; 631 $info->meta_type = 'B'; 632 $info->max_length = -1; 633 $info->scale = null; 634 $info->scale = null; 635 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL'); 636 $info->has_default = !is_null($rawcolumn->DEFAULTVAL); 637 if ($info->has_default) { 638 // this is hacky :-( 639 if ($rawcolumn->DEFAULTVAL === 'NULL') { 640 $info->default_value = null; 641 } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space 642 $info->default_value = ""; 643 } else if ($rawcolumn->DEFAULTVAL === "' '") { // Sometimes it's stored without trailing space 644 $info->default_value = ""; 645 } else { 646 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space 647 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim '' 648 } 649 } else { 650 $info->default_value = null; 651 } 652 $info->primary_key = false; 653 $info->binary = true; 654 $info->unsigned = null; 655 $info->unique = null; 656 657 } else { 658 // unknown type - sorry 659 $info->type = $rawcolumn->COLTYPE; 660 $info->meta_type = '?'; 661 } 662 663 $structure[$info->name] = new database_column_info($info); 664 } 665 666 if ($usecache) { 667 $cache->set($table, $structure); 668 } 669 670 return $structure; 671 } 672 673 /** 674 * Normalise values based in RDBMS dependencies (booleans, LOBs...) 675 * 676 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 677 * @param mixed $value value we are going to normalise 678 * @return mixed the normalised value 679 */ 680 protected function normalise_value($column, $value) { 681 $this->detect_objects($value); 682 683 if (is_bool($value)) { // Always, convert boolean to int 684 $value = (int)$value; 685 686 } else if ($column->meta_type == 'B') { // CLOB detected, we return 'blob' array instead of raw value to allow 687 if (!is_null($value)) { // binding/executing code later to know about its nature 688 $value = array('blob' => $value); 689 } 690 691 } else if ($column->meta_type == 'X' && strlen($value) > 4000) { // CLOB detected (>4000 optimisation), we return 'clob' 692 if (!is_null($value)) { // array instead of raw value to allow binding/ 693 $value = array('clob' => (string)$value); // executing code later to know about its nature 694 } 695 696 } else if ($value === '') { 697 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { 698 $value = 0; // prevent '' problems in numeric fields 699 } 700 } 701 return $value; 702 } 703 704 /** 705 * Transforms the sql and params in order to emulate the LIMIT clause available in other DBs 706 * 707 * @param string $sql the SQL select query to execute. 708 * @param array $params array of sql parameters 709 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 710 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 711 * @return array with the transformed sql and params updated 712 */ 713 private function get_limit_sql($sql, array $params = null, $limitfrom=0, $limitnum=0) { 714 715 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 716 // TODO: Add the /*+ FIRST_ROWS */ hint if there isn't another hint 717 718 if ($limitfrom and $limitnum) { 719 $sql = "SELECT oracle_o.* 720 FROM (SELECT oracle_i.*, rownum AS oracle_rownum 721 FROM ($sql) oracle_i 722 WHERE rownum <= :oracle_num_rows 723 ) oracle_o 724 WHERE oracle_rownum > :oracle_skip_rows"; 725 $params['oracle_num_rows'] = $limitfrom + $limitnum; 726 $params['oracle_skip_rows'] = $limitfrom; 727 728 } else if ($limitfrom and !$limitnum) { 729 $sql = "SELECT oracle_o.* 730 FROM (SELECT oracle_i.*, rownum AS oracle_rownum 731 FROM ($sql) oracle_i 732 ) oracle_o 733 WHERE oracle_rownum > :oracle_skip_rows"; 734 $params['oracle_skip_rows'] = $limitfrom; 735 736 } else if (!$limitfrom and $limitnum) { 737 $sql = "SELECT * 738 FROM ($sql) 739 WHERE rownum <= :oracle_num_rows"; 740 $params['oracle_num_rows'] = $limitnum; 741 } 742 743 return array($sql, $params); 744 } 745 746 /** 747 * This function will handle all the column values before being inserted/updated to DB for Oracle 748 * installations. This is because the "special feature" of Oracle where the empty string is 749 * equal to NULL and this presents a problem with all our currently NOT NULL default '' fields. 750 * (and with empties handling in general) 751 * 752 * Note that this function is 100% private and should be used, exclusively by DML functions 753 * in this file. Also, this is considered a DIRTY HACK to be removed when possible. 754 * 755 * This function is private and must not be used outside this driver at all 756 * 757 * @param $table string the table where the record is going to be inserted/updated (without prefix) 758 * @param $field string the field where the record is going to be inserted/updated 759 * @param $value mixed the value to be inserted/updated 760 */ 761 private function oracle_dirty_hack ($table, $field, $value) { 762 763 // General bound parameter, just hack the spaces and pray it will work. 764 if (!$table) { 765 if ($value === '') { 766 return ' '; 767 } else if (is_bool($value)) { 768 return (int)$value; 769 } else { 770 return $value; 771 } 772 } 773 774 // Get metadata 775 $columns = $this->get_columns($table); 776 if (!isset($columns[$field])) { 777 if ($value === '') { 778 return ' '; 779 } else if (is_bool($value)) { 780 return (int)$value; 781 } else { 782 return $value; 783 } 784 } 785 $column = $columns[$field]; 786 787 // !! This paragraph explains behaviour before Moodle 2.0: 788 // 789 // For Oracle DB, empty strings are converted to NULLs in DB 790 // and this breaks a lot of NOT NULL columns currently Moodle. In the future it's 791 // planned to move some of them to NULL, if they must accept empty values and this 792 // piece of code will become less and less used. But, for now, we need it. 793 // What we are going to do is to examine all the data being inserted and if it's 794 // an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify 795 // such data in the best form possible ("0" for booleans and numbers and " " for the 796 // rest of strings. It isn't optimal, but the only way to do so. 797 // In the opposite, when retrieving records from Oracle, we'll decode " " back to 798 // empty strings to allow everything to work properly. DIRTY HACK. 799 800 // !! These paragraphs explain the rationale about the change for Moodle 2.5: 801 // 802 // Before Moodle 2.0, we only used to apply this DIRTY HACK to NOT NULL columns, as 803 // stated above, but it causes one problem in NULL columns where both empty strings 804 // and real NULLs are stored as NULLs, being impossible to differentiate them when 805 // being retrieved from DB. 806 // 807 // So, starting with Moodle 2.0, we are going to apply the DIRTY HACK to all the 808 // CHAR/CLOB columns no matter of their nullability. That way, when retrieving 809 // NULLABLE fields we'll get proper empties and NULLs differentiated, so we'll be able 810 // to rely in NULL/empty/content contents without problems, until now that wasn't 811 // possible at all. 812 // 813 // One space DIRTY HACK is now applied automatically for all query parameters 814 // and results. The only problem is string concatenation where the glue must 815 // be specified as "' '" sql fragment. 816 // 817 // !! Conclusions: 818 // 819 // From Moodle 2.5 onwards, ALL empty strings in Oracle DBs will be stored as 820 // 1-whitespace char, ALL NULLs as NULLs and, obviously, content as content. And 821 // those 1-whitespace chars will be converted back to empty strings by all the 822 // get_field/record/set() functions transparently and any SQL needing direct handling 823 // of empties will have to use placeholders or sql_isempty() helper function. 824 825 // If the field isn't VARCHAR or CLOB, skip 826 if ($column->meta_type != 'C' and $column->meta_type != 'X') { 827 return $value; 828 } 829 830 // If the value isn't empty, skip 831 if (!empty($value)) { 832 return $value; 833 } 834 835 // Now, we have one empty value, going to be inserted to one VARCHAR2 or CLOB field 836 // Try to get the best value to be inserted 837 838 // The '0' string doesn't need any transformation, skip 839 if ($value === '0') { 840 return $value; 841 } 842 843 // Transformations start 844 if (gettype($value) == 'boolean') { 845 return '0'; // Transform false to '0' that evaluates the same for PHP 846 847 } else if (gettype($value) == 'integer') { 848 return '0'; // Transform 0 to '0' that evaluates the same for PHP 849 850 } else if ($value === '') { 851 return ' '; // Transform '' to ' ' that DON'T EVALUATE THE SAME 852 // (we'll transform back again on get_records_XXX functions and others)!! 853 } 854 855 // Fail safe to original value 856 return $value; 857 } 858 859 /** 860 * Helper function to order by string length desc 861 * 862 * @param $a string first element to compare 863 * @param $b string second element to compare 864 * @return int < 0 $a goes first (is less), 0 $b goes first, 0 doesn't matter 865 */ 866 private function compare_by_length_desc($a, $b) { 867 return strlen($b) - strlen($a); 868 } 869 870 /** 871 * Is db in unicode mode? 872 * @return bool 873 */ 874 public function setup_is_unicodedb() { 875 $sql = "SELECT VALUE 876 FROM NLS_DATABASE_PARAMETERS 877 WHERE PARAMETER = 'NLS_CHARACTERSET'"; 878 $this->query_start($sql, null, SQL_QUERY_AUX); 879 $stmt = $this->parse_query($sql); 880 $result = oci_execute($stmt, $this->commit_status); 881 $this->query_end($result, $stmt); 882 $records = null; 883 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN); 884 oci_free_statement($stmt); 885 886 return (isset($records['VALUE'][0]) and $records['VALUE'][0] === 'AL32UTF8'); 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 $sqls = (array)$sql; 898 899 try { 900 foreach ($sqls as $sql) { 901 $this->query_start($sql, null, SQL_QUERY_STRUCTURE); 902 $stmt = $this->parse_query($sql); 903 $result = oci_execute($stmt, $this->commit_status); 904 $this->query_end($result, $stmt); 905 oci_free_statement($stmt); 906 } 907 } catch (ddl_change_structure_exception $e) { 908 $this->reset_caches(); 909 throw $e; 910 } 911 912 $this->reset_caches(); 913 return true; 914 } 915 916 protected function bind_params($stmt, array $params=null, $tablename=null) { 917 $descriptors = array(); 918 if ($params) { 919 $columns = array(); 920 if ($tablename) { 921 $columns = $this->get_columns($tablename); 922 } 923 foreach($params as $key => $value) { 924 // Decouple column name and param name as far as sometimes they aren't the same 925 if ($key == 'o_newfieldtoset') { // found case where column and key diverge, handle that 926 $columnname = key($value); // columnname is the key of the array 927 $params[$key] = $value[$columnname]; // set the proper value in the $params array and 928 $value = $value[$columnname]; // set the proper value in the $value variable 929 } else { 930 $columnname = preg_replace('/^o_/', '', $key); // Default columnname (for DB introspecting is key), but... 931 } 932 // Continue processing 933 // Now, handle already detected LOBs 934 if (is_array($value)) { // Let's go to bind special cases (lob descriptors) 935 if (isset($value['clob'])) { 936 $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB); 937 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB); 938 $lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]['clob']), OCI_TEMP_CLOB); 939 $descriptors[] = $lob; 940 continue; // Column binding finished, go to next one 941 } else if (isset($value['blob'])) { 942 $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB); 943 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB); 944 $lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB); 945 $descriptors[] = $lob; 946 continue; // Column binding finished, go to next one 947 } 948 } 949 // TODO: Put proper types and length is possible (enormous speedup) 950 // Arrived here, continue with standard processing, using metadata if possible 951 if (isset($columns[$columnname])) { 952 $type = $columns[$columnname]->meta_type; 953 $maxlength = $columns[$columnname]->max_length; 954 } else { 955 $type = '?'; 956 $maxlength = -1; 957 } 958 switch ($type) { 959 case 'I': 960 case 'R': 961 // TODO: Optimise 962 oci_bind_by_name($stmt, $key, $params[$key]); 963 break; 964 965 case 'N': 966 case 'F': 967 // TODO: Optimise 968 oci_bind_by_name($stmt, $key, $params[$key]); 969 break; 970 971 case 'B': 972 // TODO: Only arrive here if BLOB is null: Bind if so, else exception! 973 // don't break here 974 975 case 'X': 976 // TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception 977 // don't break here 978 979 default: // Bind as CHAR (applying dirty hack) 980 // TODO: Optimise 981 oci_bind_by_name($stmt, $key, $this->oracle_dirty_hack($tablename, $columnname, $params[$key])); 982 } 983 } 984 } 985 return $descriptors; 986 } 987 988 protected function free_descriptors($descriptors) { 989 foreach ($descriptors as $descriptor) { 990 oci_free_descriptor($descriptor); 991 } 992 } 993 994 /** 995 * This function is used to convert all the Oracle 1-space defaults to the empty string 996 * like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT '' 997 * fields will be out from Moodle. 998 * @param string the string to be converted to '' (empty string) if it's ' ' (one space) 999 * @param mixed the key of the array in case we are using this function from array_walk, 1000 * defaults to null for other (direct) uses 1001 * @return boolean always true (the converted variable is returned by reference) 1002 */ 1003 public static function onespace2empty(&$item, $key=null) { 1004 $item = ($item === ' ') ? '' : $item; 1005 return true; 1006 } 1007 1008 /** 1009 * Execute general sql query. Should be used only when no other method suitable. 1010 * Do NOT use this to make changes in db structure, use database_manager methods instead! 1011 * @param string $sql query 1012 * @param array $params query parameters 1013 * @return bool true 1014 * @throws dml_exception A DML specific exception is thrown for any errors. 1015 */ 1016 public function execute($sql, array $params=null) { 1017 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1018 1019 if (strpos($sql, ';') !== false) { 1020 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); 1021 } 1022 1023 list($sql, $params) = $this->tweak_param_names($sql, $params); 1024 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1025 $stmt = $this->parse_query($sql); 1026 $this->bind_params($stmt, $params); 1027 $result = oci_execute($stmt, $this->commit_status); 1028 $this->query_end($result, $stmt); 1029 oci_free_statement($stmt); 1030 1031 return true; 1032 } 1033 1034 /** 1035 * Get a single database record as an object using a SQL statement. 1036 * 1037 * The SQL statement should normally only return one record. 1038 * It is recommended to use get_records_sql() if more matches possible! 1039 * 1040 * @param string $sql The SQL string you wish to be executed, should normally only return one record. 1041 * @param array $params array of sql parameters 1042 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1043 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1044 * MUST_EXIST means throw exception if no record or multiple records found 1045 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode 1046 * @throws dml_exception A DML specific exception is thrown for any errors. 1047 */ 1048 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) { 1049 $strictness = (int)$strictness; 1050 if ($strictness == IGNORE_MULTIPLE) { 1051 // do not limit here - ORA does not like that 1052 $rs = $this->get_recordset_sql($sql, $params); 1053 $result = false; 1054 foreach ($rs as $rec) { 1055 $result = $rec; 1056 break; 1057 } 1058 $rs->close(); 1059 return $result; 1060 } 1061 return parent::get_record_sql($sql, $params, $strictness); 1062 } 1063 1064 /** 1065 * Get a number of records as a moodle_recordset using a SQL statement. 1066 * 1067 * Since this method is a little less readable, use of it should be restricted to 1068 * code where it's possible there might be large datasets being returned. For known 1069 * small datasets use get_records_sql - it leads to simpler code. 1070 * 1071 * The return type is like: 1072 * @see function get_recordset. 1073 * 1074 * @param string $sql the SQL select query to execute. 1075 * @param array $params array of sql parameters 1076 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1077 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1078 * @return moodle_recordset instance 1079 * @throws dml_exception A DML specific exception is thrown for any errors. 1080 */ 1081 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1082 1083 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1084 1085 list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum); 1086 1087 list($rawsql, $params) = $this->tweak_param_names($rawsql, $params); 1088 $this->query_start($rawsql, $params, SQL_QUERY_SELECT); 1089 $stmt = $this->parse_query($rawsql); 1090 $this->bind_params($stmt, $params); 1091 $result = oci_execute($stmt, $this->commit_status); 1092 $this->query_end($result, $stmt); 1093 1094 return $this->create_recordset($stmt); 1095 } 1096 1097 protected function create_recordset($stmt) { 1098 return new oci_native_moodle_recordset($stmt); 1099 } 1100 1101 /** 1102 * Get a number of records as an array of objects using a SQL statement. 1103 * 1104 * Return value is like: 1105 * @see function get_records. 1106 * 1107 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1108 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1109 * returned array. 1110 * @param array $params array of sql parameters 1111 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1112 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1113 * @return array of objects, or empty array if no records were found 1114 * @throws dml_exception A DML specific exception is thrown for any errors. 1115 */ 1116 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1117 1118 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1119 1120 list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum); 1121 1122 list($rawsql, $params) = $this->tweak_param_names($rawsql, $params); 1123 $this->query_start($rawsql, $params, SQL_QUERY_SELECT); 1124 $stmt = $this->parse_query($rawsql); 1125 $this->bind_params($stmt, $params); 1126 $result = oci_execute($stmt, $this->commit_status); 1127 $this->query_end($result, $stmt); 1128 1129 $records = null; 1130 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); 1131 oci_free_statement($stmt); 1132 1133 $return = array(); 1134 1135 foreach ($records as $row) { 1136 $row = array_change_key_case($row, CASE_LOWER); 1137 unset($row['oracle_rownum']); 1138 array_walk($row, array('oci_native_moodle_database', 'onespace2empty')); 1139 $id = reset($row); 1140 if (isset($return[$id])) { 1141 $colname = key($row); 1142 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); 1143 } 1144 $return[$id] = (object)$row; 1145 } 1146 1147 return $return; 1148 } 1149 1150 /** 1151 * Selects records and return values (first field) as an array using a SQL statement. 1152 * 1153 * @param string $sql The SQL query 1154 * @param array $params array of sql parameters 1155 * @return array of values 1156 * @throws dml_exception A DML specific exception is thrown for any errors. 1157 */ 1158 public function get_fieldset_sql($sql, array $params=null) { 1159 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1160 1161 list($sql, $params) = $this->tweak_param_names($sql, $params); 1162 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1163 $stmt = $this->parse_query($sql); 1164 $this->bind_params($stmt, $params); 1165 $result = oci_execute($stmt, $this->commit_status); 1166 $this->query_end($result, $stmt); 1167 1168 $records = null; 1169 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN); 1170 oci_free_statement($stmt); 1171 1172 $return = reset($records); 1173 array_walk($return, array('oci_native_moodle_database', 'onespace2empty')); 1174 1175 return $return; 1176 } 1177 1178 /** 1179 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1180 * @param string $table name 1181 * @param mixed $params data record as object or array 1182 * @param bool $returnit return it of inserted record 1183 * @param bool $bulk true means repeated inserts expected 1184 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1185 * @return bool|int true or new id 1186 * @throws dml_exception A DML specific exception is thrown for any errors. 1187 */ 1188 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { 1189 if (!is_array($params)) { 1190 $params = (array)$params; 1191 } 1192 1193 $returning = ""; 1194 1195 if ($customsequence) { 1196 if (!isset($params['id'])) { 1197 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); 1198 } 1199 $returnid = false; 1200 } else { 1201 unset($params['id']); 1202 if ($returnid) { 1203 $returning = " RETURNING id INTO :oracle_id"; // crazy name nobody is ever going to use or parameter ;-) 1204 } 1205 } 1206 1207 if (empty($params)) { 1208 throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); 1209 } 1210 1211 $fields = implode(',', array_keys($params)); 1212 $values = array(); 1213 foreach ($params as $pname => $value) { 1214 $values[] = ":$pname"; 1215 } 1216 $values = implode(',', $values); 1217 1218 $sql = "INSERT INTO {" . $table . "} ($fields) VALUES ($values)"; 1219 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1220 $sql .= $returning; 1221 1222 $id = null; 1223 1224 // note we don't need tweak_param_names() here. Placeholders are safe column names. MDL-28080 1225 // list($sql, $params) = $this->tweak_param_names($sql, $params); 1226 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1227 $stmt = $this->parse_query($sql); 1228 $descriptors = $this->bind_params($stmt, $params, $table); 1229 if ($returning) { 1230 oci_bind_by_name($stmt, ":oracle_id", $id, 10, SQLT_INT); 1231 } 1232 $result = oci_execute($stmt, $this->commit_status); 1233 $this->free_descriptors($descriptors); 1234 $this->query_end($result, $stmt); 1235 oci_free_statement($stmt); 1236 1237 if (!$returnid) { 1238 return true; 1239 } 1240 1241 if (!$returning) { 1242 die('TODO - implement oracle 9.2 insert support'); //TODO 1243 } 1244 1245 return (int)$id; 1246 } 1247 1248 /** 1249 * Insert a record into a table and return the "id" field if required. 1250 * 1251 * Some conversions and safety checks are carried out. Lobs are supported. 1252 * If the return ID isn't required, then this just reports success as true/false. 1253 * $data is an object containing needed data 1254 * @param string $table The database table to be inserted into 1255 * @param object $data A data object with values for one or more fields in the record 1256 * @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. 1257 * @return bool|int true or new id 1258 * @throws dml_exception A DML specific exception is thrown for any errors. 1259 */ 1260 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { 1261 $dataobject = (array)$dataobject; 1262 1263 $columns = $this->get_columns($table); 1264 if (empty($columns)) { 1265 throw new dml_exception('ddltablenotexist', $table); 1266 } 1267 1268 $cleaned = array(); 1269 1270 foreach ($dataobject as $field=>$value) { 1271 if ($field === 'id') { 1272 continue; 1273 } 1274 if (!isset($columns[$field])) { // Non-existing table field, skip it 1275 continue; 1276 } 1277 $column = $columns[$field]; 1278 $cleaned[$field] = $this->normalise_value($column, $value); 1279 } 1280 1281 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); 1282 } 1283 1284 /** 1285 * Import a record into a table, id field is required. 1286 * Safety checks are NOT carried out. Lobs are supported. 1287 * 1288 * @param string $table name of database table to be inserted into 1289 * @param object $dataobject A data object with values for one or more fields in the record 1290 * @return bool true 1291 * @throws dml_exception A DML specific exception is thrown for any errors. 1292 */ 1293 public function import_record($table, $dataobject) { 1294 $dataobject = (array)$dataobject; 1295 1296 $columns = $this->get_columns($table); 1297 $cleaned = array(); 1298 1299 foreach ($dataobject as $field=>$value) { 1300 if (!isset($columns[$field])) { 1301 continue; 1302 } 1303 $column = $columns[$field]; 1304 $cleaned[$field] = $this->normalise_value($column, $value); 1305 } 1306 1307 return $this->insert_record_raw($table, $cleaned, false, true, true); 1308 } 1309 1310 /** 1311 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1312 * @param string $table name 1313 * @param mixed $params data record as object or array 1314 * @param bool true means repeated updates expected 1315 * @return bool true 1316 * @throws dml_exception A DML specific exception is thrown for any errors. 1317 */ 1318 public function update_record_raw($table, $params, $bulk=false) { 1319 $params = (array)$params; 1320 1321 if (!isset($params['id'])) { 1322 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); 1323 } 1324 1325 if (empty($params)) { 1326 throw new coding_exception('moodle_database::update_record_raw() no fields found.'); 1327 } 1328 1329 $sets = array(); 1330 foreach ($params as $field=>$value) { 1331 if ($field == 'id') { 1332 continue; 1333 } 1334 $sets[] = "$field = :$field"; 1335 } 1336 1337 $sets = implode(',', $sets); 1338 $sql = "UPDATE {" . $table . "} SET $sets WHERE id=:id"; 1339 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1340 1341 // note we don't need tweak_param_names() here. Placeholders are safe column names. MDL-28080 1342 // list($sql, $params) = $this->tweak_param_names($sql, $params); 1343 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1344 $stmt = $this->parse_query($sql); 1345 $descriptors = $this->bind_params($stmt, $params, $table); 1346 $result = oci_execute($stmt, $this->commit_status); 1347 $this->free_descriptors($descriptors); 1348 $this->query_end($result, $stmt); 1349 oci_free_statement($stmt); 1350 1351 return true; 1352 } 1353 1354 /** 1355 * Update a record in a table 1356 * 1357 * $dataobject is an object containing needed data 1358 * Relies on $dataobject having a variable "id" to 1359 * specify the record to update 1360 * 1361 * @param string $table The database table to be checked against. 1362 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. 1363 * @param bool true means repeated updates expected 1364 * @return bool true 1365 * @throws dml_exception A DML specific exception is thrown for any errors. 1366 */ 1367 public function update_record($table, $dataobject, $bulk=false) { 1368 $dataobject = (array)$dataobject; 1369 1370 $columns = $this->get_columns($table); 1371 $cleaned = array(); 1372 1373 foreach ($dataobject as $field=>$value) { 1374 if (!isset($columns[$field])) { 1375 continue; 1376 } 1377 $column = $columns[$field]; 1378 $cleaned[$field] = $this->normalise_value($column, $value); 1379 } 1380 1381 $this->update_record_raw($table, $cleaned, $bulk); 1382 1383 return true; 1384 } 1385 1386 /** 1387 * Set a single field in every table record which match a particular WHERE clause. 1388 * 1389 * @param string $table The database table to be checked against. 1390 * @param string $newfield the field to set. 1391 * @param string $newvalue the value to set the field to. 1392 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1393 * @param array $params array of sql parameters 1394 * @return bool true 1395 * @throws dml_exception A DML specific exception is thrown for any errors. 1396 */ 1397 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { 1398 1399 if ($select) { 1400 $select = "WHERE $select"; 1401 } 1402 if (is_null($params)) { 1403 $params = array(); 1404 } 1405 1406 // Get column metadata 1407 $columns = $this->get_columns($table); 1408 $column = $columns[$newfield]; 1409 1410 $newvalue = $this->normalise_value($column, $newvalue); 1411 1412 list($select, $params, $type) = $this->fix_sql_params($select, $params); 1413 1414 if (is_bool($newvalue)) { 1415 $newvalue = (int)$newvalue; // prevent "false" problems 1416 } 1417 if (is_null($newvalue)) { 1418 $newsql = "$newfield = NULL"; 1419 } else { 1420 // Set the param to array ($newfield => $newvalue) and key to 'newfieldtoset' 1421 // name in the build sql. Later, bind_params() will detect the value array and 1422 // perform the needed modifications to allow the query to work. Note that 1423 // 'newfieldtoset' is one arbitrary name that hopefully won't be used ever 1424 // in order to avoid problems where the same field is used both in the set clause and in 1425 // the conditions. This was breaking badly in drivers using NAMED params like oci. 1426 $params['newfieldtoset'] = array($newfield => $newvalue); 1427 $newsql = "$newfield = :newfieldtoset"; 1428 } 1429 $sql = "UPDATE {" . $table . "} SET $newsql $select"; 1430 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1431 1432 list($sql, $params) = $this->tweak_param_names($sql, $params); 1433 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1434 $stmt = $this->parse_query($sql); 1435 $descriptors = $this->bind_params($stmt, $params, $table); 1436 $result = oci_execute($stmt, $this->commit_status); 1437 $this->free_descriptors($descriptors); 1438 $this->query_end($result, $stmt); 1439 oci_free_statement($stmt); 1440 1441 return true; 1442 } 1443 1444 /** 1445 * Delete one or more records from a table which match a particular WHERE clause. 1446 * 1447 * @param string $table The database table to be checked against. 1448 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1449 * @param array $params array of sql parameters 1450 * @return bool true 1451 * @throws dml_exception A DML specific exception is thrown for any errors. 1452 */ 1453 public function delete_records_select($table, $select, array $params=null) { 1454 1455 if ($select) { 1456 $select = "WHERE $select"; 1457 } 1458 1459 $sql = "DELETE FROM {" . $table . "} $select"; 1460 1461 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1462 1463 list($sql, $params) = $this->tweak_param_names($sql, $params); 1464 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1465 $stmt = $this->parse_query($sql); 1466 $this->bind_params($stmt, $params); 1467 $result = oci_execute($stmt, $this->commit_status); 1468 $this->query_end($result, $stmt); 1469 oci_free_statement($stmt); 1470 1471 return true; 1472 } 1473 1474 function sql_null_from_clause() { 1475 return ' FROM dual'; 1476 } 1477 1478 public function sql_bitand($int1, $int2) { 1479 return 'bitand((' . $int1 . '), (' . $int2 . '))'; 1480 } 1481 1482 public function sql_bitnot($int1) { 1483 return '((0 - (' . $int1 . ')) - 1)'; 1484 } 1485 1486 public function sql_bitor($int1, $int2) { 1487 return 'MOODLELIB.BITOR(' . $int1 . ', ' . $int2 . ')'; 1488 } 1489 1490 public function sql_bitxor($int1, $int2) { 1491 return 'MOODLELIB.BITXOR(' . $int1 . ', ' . $int2 . ')'; 1492 } 1493 1494 /** 1495 * Returns the SQL text to be used in order to perform module '%' 1496 * operation - remainder after division 1497 * 1498 * @param integer int1 first integer in the operation 1499 * @param integer int2 second integer in the operation 1500 * @return string the piece of SQL code to be used in your statement. 1501 */ 1502 public function sql_modulo($int1, $int2) { 1503 return 'MOD(' . $int1 . ', ' . $int2 . ')'; 1504 } 1505 1506 public function sql_cast_char2int($fieldname, $text=false) { 1507 if (!$text) { 1508 return ' CAST(' . $fieldname . ' AS INT) '; 1509 } else { 1510 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) '; 1511 } 1512 } 1513 1514 public function sql_cast_char2real($fieldname, $text=false) { 1515 if (!$text) { 1516 return ' CAST(' . $fieldname . ' AS FLOAT) '; 1517 } else { 1518 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS FLOAT) '; 1519 } 1520 } 1521 1522 /** 1523 * Returns 'LIKE' part of a query. 1524 * 1525 * @param string $fieldname usually name of the table column 1526 * @param string $param usually bound query parameter (?, :named) 1527 * @param bool $casesensitive use case sensitive search 1528 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive) 1529 * @param bool $notlike true means "NOT LIKE" 1530 * @param string $escapechar escape char for '%' and '_' 1531 * @return string SQL code fragment 1532 */ 1533 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 1534 if (strpos($param, '%') !== false) { 1535 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 1536 } 1537 1538 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 1539 1540 // no accent sensitiveness here for now, sorry 1541 1542 if ($casesensitive) { 1543 return "$fieldname $LIKE $param ESCAPE '$escapechar'"; 1544 } else { 1545 return "LOWER($fieldname) $LIKE LOWER($param) ESCAPE '$escapechar'"; 1546 } 1547 } 1548 1549 public function sql_concat() { 1550 $arr = func_get_args(); 1551 if (empty($arr)) { 1552 return " ' ' "; 1553 } 1554 foreach ($arr as $k => $v) { 1555 if ($v === "' '") { 1556 $arr[$k] = "'*OCISP*'"; // New mega hack. 1557 } 1558 } 1559 $s = $this->recursive_concat($arr); 1560 return " MOODLELIB.UNDO_MEGA_HACK($s) "; 1561 } 1562 1563 public function sql_concat_join($separator="' '", $elements = array()) { 1564 if ($separator === "' '") { 1565 $separator = "'*OCISP*'"; // New mega hack. 1566 } 1567 foreach ($elements as $k => $v) { 1568 if ($v === "' '") { 1569 $elements[$k] = "'*OCISP*'"; // New mega hack. 1570 } 1571 } 1572 for ($n = count($elements)-1; $n > 0 ; $n--) { 1573 array_splice($elements, $n, 0, $separator); 1574 } 1575 if (empty($elements)) { 1576 return " ' ' "; 1577 } 1578 $s = $this->recursive_concat($elements); 1579 return " MOODLELIB.UNDO_MEGA_HACK($s) "; 1580 } 1581 1582 /** 1583 * Constructs 'IN()' or '=' sql fragment 1584 * 1585 * Method overriding {@link moodle_database::get_in_or_equal} to be able to get 1586 * more than 1000 elements working, to avoid ORA-01795. We use a pivoting technique 1587 * to be able to transform the params into virtual rows, so the original IN() 1588 * expression gets transformed into a subquery. Once more, be noted that we shouldn't 1589 * be using ever get_in_or_equal() with such number of parameters (proper subquery and/or 1590 * chunking should be used instead). 1591 * 1592 * @param mixed $items A single value or array of values for the expression. 1593 * @param int $type Parameter bounding type : SQL_PARAMS_QM or SQL_PARAMS_NAMED. 1594 * @param string $prefix Named parameter placeholder prefix (a unique counter value is appended to each parameter name). 1595 * @param bool $equal True means we want to equate to the constructed expression, false means we don't want to equate to it. 1596 * @param mixed $onemptyitems This defines the behavior when the array of items provided is empty. Defaults to false, 1597 * meaning throw exceptions. Other values will become part of the returned SQL fragment. 1598 * @throws coding_exception | dml_exception 1599 * @return array A list containing the constructed sql fragment and an array of parameters. 1600 */ 1601 public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false) { 1602 list($sql, $params) = parent::get_in_or_equal($items, $type, $prefix, $equal, $onemptyitems); 1603 1604 // Less than 1000 elements, nothing to do. 1605 if (count($params) < 1000) { 1606 return array($sql, $params); // Return unmodified. 1607 } 1608 1609 // Extract the interesting parts of the sql to rewrite. 1610 if (preg_match('!(^.*IN \()([^\)]*)(.*)$!', $sql, $matches) === false) { 1611 return array($sql, $params); // Return unmodified. 1612 } 1613 1614 $instart = $matches[1]; 1615 $insql = $matches[2]; 1616 $inend = $matches[3]; 1617 $newsql = ''; 1618 1619 // Some basic verification about the matching going ok. 1620 $insqlarr = explode(',', $insql); 1621 if (count($insqlarr) !== count($params)) { 1622 return array($sql, $params); // Return unmodified. 1623 } 1624 1625 // Arrived here, we need to chunk and pivot the params, building a new sql (params remain the same). 1626 $addunionclause = false; 1627 while ($chunk = array_splice($insqlarr, 0, 125)) { // Each chunk will handle up to 125 (+125 +1) elements (DECODE max is 255). 1628 $chunksize = count($chunk); 1629 if ($addunionclause) { 1630 $newsql .= "\n UNION ALL"; 1631 } 1632 $newsql .= "\n SELECT DECODE(pivot"; 1633 $counter = 1; 1634 foreach ($chunk as $element) { 1635 $newsql .= ",\n {$counter}, " . trim($element); 1636 $counter++; 1637 } 1638 $newsql .= ")"; 1639 $newsql .= "\n FROM dual"; 1640 $newsql .= "\n CROSS JOIN (SELECT LEVEL AS pivot FROM dual CONNECT BY LEVEL <= {$chunksize})"; 1641 $addunionclause = true; 1642 } 1643 1644 // Rebuild the complete IN() clause and return it. 1645 return array($instart . $newsql . $inend, $params); 1646 } 1647 1648 /** 1649 * Mega hacky magic to work around crazy Oracle NULL concats. 1650 * @param array $args 1651 * @return string 1652 */ 1653 protected function recursive_concat(array $args) { 1654 $count = count($args); 1655 if ($count == 1) { 1656 $arg = reset($args); 1657 return $arg; 1658 } 1659 if ($count == 2) { 1660 $args[] = "' '"; 1661 // No return here intentionally. 1662 } 1663 $first = array_shift($args); 1664 $second = array_shift($args); 1665 $third = $this->recursive_concat($args); 1666 return "MOODLELIB.TRICONCAT($first, $second, $third)"; 1667 } 1668 1669 /** 1670 * Returns the SQL for returning searching one string for the location of another. 1671 */ 1672 public function sql_position($needle, $haystack) { 1673 return "INSTR(($haystack), ($needle))"; 1674 } 1675 1676 /** 1677 * Returns the SQL to know if one field is empty. 1678 * 1679 * @param string $tablename Name of the table (without prefix). Not used for now but can be 1680 * necessary in the future if we want to use some introspection using 1681 * meta information against the DB. 1682 * @param string $fieldname Name of the field we are going to check 1683 * @param bool $nullablefield For specifying if the field is nullable (true) or no (false) in the DB. 1684 * @param bool $textfield For specifying if it is a text (also called clob) field (true) or a varchar one (false) 1685 * @return string the sql code to be added to check for empty values 1686 */ 1687 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { 1688 if ($textfield) { 1689 return " (".$this->sql_compare_text($fieldname)." = ' ') "; 1690 } else { 1691 return " ($fieldname = ' ') "; 1692 } 1693 } 1694 1695 public function sql_order_by_text($fieldname, $numchars=32) { 1696 return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)'; 1697 } 1698 1699 /** 1700 * Is the required OCI server package installed? 1701 * @return bool 1702 */ 1703 protected function oci_package_installed() { 1704 $sql = "SELECT 1 1705 FROM user_objects 1706 WHERE object_type = 'PACKAGE BODY' 1707 AND object_name = 'MOODLELIB' 1708 AND status = 'VALID'"; 1709 $this->query_start($sql, null, SQL_QUERY_AUX); 1710 $stmt = $this->parse_query($sql); 1711 $result = oci_execute($stmt, $this->commit_status); 1712 $this->query_end($result, $stmt); 1713 $records = null; 1714 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW); 1715 oci_free_statement($stmt); 1716 return isset($records[0]) && reset($records[0]) ? true : false; 1717 } 1718 1719 /** 1720 * Try to add required moodle package into oracle server. 1721 */ 1722 protected function attempt_oci_package_install() { 1723 $sqls = file_get_contents(__DIR__.'/oci_native_moodle_package.sql'); 1724 $sqls = preg_split('/^\/$/sm', $sqls); 1725 foreach ($sqls as $sql) { 1726 $sql = trim($sql); 1727 if ($sql === '' or $sql === 'SHOW ERRORS') { 1728 continue; 1729 } 1730 $this->change_database_structure($sql); 1731 } 1732 } 1733 1734 /** 1735 * Does this driver support tool_replace? 1736 * 1737 * @since Moodle 2.8 1738 * @return bool 1739 */ 1740 public function replace_all_text_supported() { 1741 return true; 1742 } 1743 1744 public function session_lock_supported() { 1745 return true; 1746 } 1747 1748 /** 1749 * Obtain session lock 1750 * @param int $rowid id of the row with session record 1751 * @param int $timeout max allowed time to wait for the lock in seconds 1752 * @return void 1753 */ 1754 public function get_session_lock($rowid, $timeout) { 1755 parent::get_session_lock($rowid, $timeout); 1756 1757 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1758 $sql = 'SELECT MOODLELIB.GET_LOCK(:lockname, :locktimeout) FROM DUAL'; 1759 $params = array('lockname' => $fullname , 'locktimeout' => $timeout); 1760 $this->query_start($sql, $params, SQL_QUERY_AUX); 1761 $stmt = $this->parse_query($sql); 1762 $this->bind_params($stmt, $params); 1763 $result = oci_execute($stmt, $this->commit_status); 1764 if ($result === false) { // Any failure in get_lock() raises error, causing return of bool false 1765 throw new dml_sessionwait_exception(); 1766 } 1767 $this->query_end($result, $stmt); 1768 oci_free_statement($stmt); 1769 } 1770 1771 public function release_session_lock($rowid) { 1772 if (!$this->used_for_db_sessions) { 1773 return; 1774 } 1775 1776 parent::release_session_lock($rowid); 1777 1778 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1779 $params = array('lockname' => $fullname); 1780 $sql = 'SELECT MOODLELIB.RELEASE_LOCK(:lockname) FROM DUAL'; 1781 $this->query_start($sql, $params, SQL_QUERY_AUX); 1782 $stmt = $this->parse_query($sql); 1783 $this->bind_params($stmt, $params); 1784 $result = oci_execute($stmt, $this->commit_status); 1785 $this->query_end($result, $stmt); 1786 oci_free_statement($stmt); 1787 } 1788 1789 /** 1790 * Driver specific start of real database transaction, 1791 * this can not be used directly in code. 1792 * @return void 1793 */ 1794 protected function begin_transaction() { 1795 $this->commit_status = OCI_DEFAULT; //Done! ;-) 1796 } 1797 1798 /** 1799 * Driver specific commit of real database transaction, 1800 * this can not be used directly in code. 1801 * @return void 1802 */ 1803 protected function commit_transaction() { 1804 $this->query_start('--oracle_commit', NULL, SQL_QUERY_AUX); 1805 $result = oci_commit($this->oci); 1806 $this->commit_status = OCI_COMMIT_ON_SUCCESS; 1807 $this->query_end($result); 1808 } 1809 1810 /** 1811 * Driver specific abort of real database transaction, 1812 * this can not be used directly in code. 1813 * @return void 1814 */ 1815 protected function rollback_transaction() { 1816 $this->query_start('--oracle_rollback', NULL, SQL_QUERY_AUX); 1817 $result = oci_rollback($this->oci); 1818 $this->commit_status = OCI_COMMIT_ON_SUCCESS; 1819 $this->query_end($result); 1820 } 1821 }
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 |