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