PageRenderTime 81ms CodeModel.GetById 32ms app.highlight 38ms RepoModel.GetById 1ms app.codeStats 1ms

/lib/dml/oci_native_moodle_database.php

http://github.com/moodle/moodle
PHP | 1855 lines | 1227 code | 170 blank | 458 comment | 167 complexity | f6025ae2175e303ef60506d04a754d62 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   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
  25defined('MOODLE_INTERNAL') || die();
  26
  27require_once(__DIR__.'/moodle_database.php');
  28require_once(__DIR__.'/oci_native_moodle_recordset.php');
  29require_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 */
  41class 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 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=null, $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     * @throws dml_connection_exception
 349     * @param string $sql
 350     * @return resource
 351     */
 352    protected function parse_query($sql) {
 353        $stmt = oci_parse($this->oci, $sql);
 354        if ($stmt == false) {
 355            throw new dml_connection_exception('Can not parse sql query'); //TODO: maybe add better info
 356        }
 357        return $stmt;
 358    }
 359
 360    /**
 361     * Make sure there are no reserved words in param names...
 362     * @param string $sql
 363     * @param array $params
 364     * @return array ($sql, $params) updated query and parameters
 365     */
 366    protected function tweak_param_names($sql, array $params) {
 367        if (empty($params)) {
 368            return array($sql, $params);
 369        }
 370
 371        $newparams = array();
 372        $searcharr = array(); // search => replace pairs
 373        foreach ($params as $name => $value) {
 374            // Keep the name within the 30 chars limit always (prefixing/replacing)
 375            if (strlen($name) <= 28) {
 376                $newname = 'o_' . $name;
 377            } else {
 378                $newname = 'o_' . substr($name, 2);
 379            }
 380            $newparams[$newname] = $value;
 381            $searcharr[':' . $name] = ':' . $newname;
 382        }
 383        // sort by length desc to avoid potential str_replace() overlap
 384        uksort($searcharr, array('oci_native_moodle_database', 'compare_by_length_desc'));
 385
 386        $sql = str_replace(array_keys($searcharr), $searcharr, $sql);
 387        return array($sql, $newparams);
 388    }
 389
 390    /**
 391     * Return tables in database WITHOUT current prefix
 392     * @param bool $usecache if true, returns list of cached tables.
 393     * @return array of table names in lowercase and without prefix
 394     */
 395    public function get_tables($usecache=true) {
 396        if ($usecache and $this->tables !== null) {
 397            return $this->tables;
 398        }
 399        $this->tables = array();
 400        $prefix = str_replace('_', "\\_", strtoupper($this->prefix));
 401        $sql = "SELECT TABLE_NAME
 402                  FROM CAT
 403                 WHERE TABLE_TYPE='TABLE'
 404                       AND TABLE_NAME NOT LIKE 'BIN\$%'
 405                       AND TABLE_NAME LIKE '$prefix%' ESCAPE '\\'";
 406        $this->query_start($sql, null, SQL_QUERY_AUX);
 407        $stmt = $this->parse_query($sql);
 408        $result = oci_execute($stmt, $this->commit_status);
 409        $this->query_end($result, $stmt);
 410        $records = null;
 411        oci_fetch_all($stmt, $records, 0, -1, OCI_ASSOC);
 412        oci_free_statement($stmt);
 413        $records = array_map('strtolower', $records['TABLE_NAME']);
 414        foreach ($records as $tablename) {
 415            if ($this->prefix !== false && $this->prefix !== '') {
 416                if (strpos($tablename, $this->prefix) !== 0) {
 417                    continue;
 418                }
 419                $tablename = substr($tablename, strlen($this->prefix));
 420            }
 421            $this->tables[$tablename] = $tablename;
 422        }
 423
 424        // Add the currently available temptables
 425        $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
 426
 427        return $this->tables;
 428    }
 429
 430    /**
 431     * Return table indexes - everything lowercased.
 432     * @param string $table The table we want to get indexes from.
 433     * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed
 434     */
 435    public function get_indexes($table) {
 436        $indexes = array();
 437        $tablename = strtoupper($this->prefix.$table);
 438
 439        $sql = "SELECT i.INDEX_NAME, i.UNIQUENESS, c.COLUMN_POSITION, c.COLUMN_NAME, ac.CONSTRAINT_TYPE
 440                  FROM ALL_INDEXES i
 441                  JOIN ALL_IND_COLUMNS c ON c.INDEX_NAME=i.INDEX_NAME
 442             LEFT JOIN ALL_CONSTRAINTS ac ON (ac.TABLE_NAME=i.TABLE_NAME AND ac.CONSTRAINT_NAME=i.INDEX_NAME AND ac.CONSTRAINT_TYPE='P')
 443                 WHERE i.TABLE_NAME = '$tablename'
 444              ORDER BY i.INDEX_NAME, c.COLUMN_POSITION";
 445
 446        $stmt = $this->parse_query($sql);
 447        $result = oci_execute($stmt, $this->commit_status);
 448        $this->query_end($result, $stmt);
 449        $records = null;
 450        oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
 451        oci_free_statement($stmt);
 452
 453        foreach ($records as $record) {
 454            if ($record['CONSTRAINT_TYPE'] === 'P') {
 455                //ignore for now;
 456                continue;
 457            }
 458            $indexname = strtolower($record['INDEX_NAME']);
 459            if (!isset($indexes[$indexname])) {
 460                $indexes[$indexname] = array('primary' => ($record['CONSTRAINT_TYPE'] === 'P'),
 461                                             'unique'  => ($record['UNIQUENESS'] === 'UNIQUE'),
 462                                             'columns' => array());
 463            }
 464            $indexes[$indexname]['columns'][] = strtolower($record['COLUMN_NAME']);
 465        }
 466
 467        return $indexes;
 468    }
 469
 470    /**
 471     * Fetches detailed information about columns in table.
 472     *
 473     * @param string $table name
 474     * @return array array of database_column_info objects indexed with column names
 475     */
 476    protected function fetch_columns(string $table): array {
 477        $structure = array();
 478
 479        // We give precedence to CHAR_LENGTH for VARCHAR2 columns over WIDTH because the former is always
 480        // BYTE based and, for cross-db operations, we want CHAR based results. See MDL-29415
 481        // Instead of guessing sequence based exclusively on name, check tables against user_triggers to
 482        // ensure the table has a 'before each row' trigger to assume 'id' is auto_increment. MDL-32365
 483        $sql = "SELECT CNAME, COLTYPE, nvl(CHAR_LENGTH, WIDTH) AS WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL,
 484                  DECODE(NVL(TRIGGER_NAME, '0'), '0', '0', '1') HASTRIGGER
 485                  FROM COL c
 486             LEFT JOIN USER_TAB_COLUMNS u ON (u.TABLE_NAME = c.TNAME AND u.COLUMN_NAME = c.CNAME AND u.DATA_TYPE = 'VARCHAR2')
 487             LEFT JOIN USER_TRIGGERS t ON (t.TABLE_NAME = c.TNAME AND TRIGGER_TYPE = 'BEFORE EACH ROW' AND c.CNAME = 'ID')
 488                 WHERE TNAME = UPPER('{" . $table . "}')
 489              ORDER BY COLNO";
 490
 491        list($sql, $params, $type) = $this->fix_sql_params($sql, null);
 492
 493        $this->query_start($sql, null, SQL_QUERY_AUX);
 494        $stmt = $this->parse_query($sql);
 495        $result = oci_execute($stmt, $this->commit_status);
 496        $this->query_end($result, $stmt);
 497        $records = null;
 498        oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
 499        oci_free_statement($stmt);
 500
 501        if (!$records) {
 502            return array();
 503        }
 504        foreach ($records as $rawcolumn) {
 505            $rawcolumn = (object)$rawcolumn;
 506
 507            $info = new stdClass();
 508            $info->name = strtolower($rawcolumn->CNAME);
 509            $info->auto_increment = ((int)$rawcolumn->HASTRIGGER) ? true : false;
 510            $matches = null;
 511
 512            if ($rawcolumn->COLTYPE === 'VARCHAR2'
 513             or $rawcolumn->COLTYPE === 'VARCHAR'
 514             or $rawcolumn->COLTYPE === 'NVARCHAR2'
 515             or $rawcolumn->COLTYPE === 'NVARCHAR'
 516             or $rawcolumn->COLTYPE === 'CHAR'
 517             or $rawcolumn->COLTYPE === 'NCHAR') {
 518                $info->type          = $rawcolumn->COLTYPE;
 519                $info->meta_type     = 'C';
 520                $info->max_length    = $rawcolumn->WIDTH;
 521                $info->scale         = null;
 522                $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
 523                $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
 524                if ($info->has_default) {
 525
 526                    // this is hacky :-(
 527                    if ($rawcolumn->DEFAULTVAL === 'NULL') {
 528                        $info->default_value = null;
 529                    } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space
 530                        $info->default_value = "";
 531                    } else if ($rawcolumn->DEFAULTVAL === "' '") { // Sometimes it's stored without trailing space
 532                        $info->default_value = "";
 533                    } else {
 534                        $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
 535                        $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
 536                    }
 537                } else {
 538                    $info->default_value = null;
 539                }
 540                $info->primary_key   = false;
 541                $info->binary        = false;
 542                $info->unsigned      = null;
 543                $info->unique        = null;
 544
 545            } else if ($rawcolumn->COLTYPE === 'NUMBER') {
 546                $info->type       = $rawcolumn->COLTYPE;
 547                $info->max_length = $rawcolumn->PRECISION;
 548                $info->binary     = false;
 549                if (!is_null($rawcolumn->SCALE) && $rawcolumn->SCALE == 0) { // null in oracle scale allows decimals => not integer
 550                    // integer
 551                    if ($info->name === 'id') {
 552                        $info->primary_key   = true;
 553                        $info->meta_type     = 'R';
 554                        $info->unique        = true;
 555                        $info->has_default   = false;
 556                    } else {
 557                        $info->primary_key   = false;
 558                        $info->meta_type     = 'I';
 559                        $info->unique        = null;
 560                    }
 561                    $info->scale = 0;
 562
 563                } else {
 564                    //float
 565                    $info->meta_type     = 'N';
 566                    $info->primary_key   = false;
 567                    $info->unsigned      = null;
 568                    $info->unique        = null;
 569                    $info->scale         = $rawcolumn->SCALE;
 570                }
 571                $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
 572                $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
 573                if ($info->has_default) {
 574                    $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
 575                } else {
 576                    $info->default_value = null;
 577                }
 578
 579            } else if ($rawcolumn->COLTYPE === 'FLOAT') {
 580                $info->type       = $rawcolumn->COLTYPE;
 581                $info->max_length = (int)($rawcolumn->PRECISION * 3.32193);
 582                $info->primary_key   = false;
 583                $info->meta_type     = 'N';
 584                $info->unique        = null;
 585                $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
 586                $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
 587                if ($info->has_default) {
 588                    $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
 589                } else {
 590                    $info->default_value = null;
 591                }
 592
 593            } else if ($rawcolumn->COLTYPE === 'CLOB'
 594                    or $rawcolumn->COLTYPE === 'NCLOB') {
 595                $info->type          = $rawcolumn->COLTYPE;
 596                $info->meta_type     = 'X';
 597                $info->max_length    = -1;
 598                $info->scale         = null;
 599                $info->scale         = null;
 600                $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
 601                $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
 602                if ($info->has_default) {
 603                    // this is hacky :-(
 604                    if ($rawcolumn->DEFAULTVAL === 'NULL') {
 605                        $info->default_value = null;
 606                    } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space
 607                        $info->default_value = "";
 608                    } else if ($rawcolumn->DEFAULTVAL === "' '") { // Other times it's stored without trailing space
 609                        $info->default_value = "";
 610                    } else {
 611                        $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
 612                        $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
 613                    }
 614                } else {
 615                    $info->default_value = null;
 616                }
 617                $info->primary_key   = false;
 618                $info->binary        = false;
 619                $info->unsigned      = null;
 620                $info->unique        = null;
 621
 622            } else if ($rawcolumn->COLTYPE === 'BLOB') {
 623                $info->type          = $rawcolumn->COLTYPE;
 624                $info->meta_type     = 'B';
 625                $info->max_length    = -1;
 626                $info->scale         = null;
 627                $info->scale         = null;
 628                $info->not_null      = ($rawcolumn->NULLS === 'NOT NULL');
 629                $info->has_default   = !is_null($rawcolumn->DEFAULTVAL);
 630                if ($info->has_default) {
 631                    // this is hacky :-(
 632                    if ($rawcolumn->DEFAULTVAL === 'NULL') {
 633                        $info->default_value = null;
 634                    } else if ($rawcolumn->DEFAULTVAL === "' ' ") { // Sometimes it's stored with trailing space
 635                        $info->default_value = "";
 636                    } else if ($rawcolumn->DEFAULTVAL === "' '") { // Sometimes it's stored without trailing space
 637                        $info->default_value = "";
 638                    } else {
 639                        $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
 640                        $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
 641                    }
 642                } else {
 643                    $info->default_value = null;
 644                }
 645                $info->primary_key   = false;
 646                $info->binary        = true;
 647                $info->unsigned      = null;
 648                $info->unique        = null;
 649
 650            } else {
 651                // unknown type - sorry
 652                $info->type          = $rawcolumn->COLTYPE;
 653                $info->meta_type     = '?';
 654            }
 655
 656            $structure[$info->name] = new database_column_info($info);
 657        }
 658
 659        return $structure;
 660    }
 661
 662    /**
 663     * Normalise values based in RDBMS dependencies (booleans, LOBs...)
 664     *
 665     * @param database_column_info $column column metadata corresponding with the value we are going to normalise
 666     * @param mixed $value value we are going to normalise
 667     * @return mixed the normalised value
 668     */
 669    protected function normalise_value($column, $value) {
 670        $this->detect_objects($value);
 671
 672        if (is_bool($value)) { // Always, convert boolean to int
 673            $value = (int)$value;
 674
 675        } else if ($column->meta_type == 'B') { // BLOB detected, we return 'blob' array instead of raw value to allow
 676            if (!is_null($value)) {             // binding/executing code later to know about its nature
 677                $value = array('blob' => $value);
 678            }
 679
 680        } else if ($column->meta_type == 'X' && strlen($value) > 4000) { // CLOB detected (>4000 optimisation), we return 'clob'
 681            if (!is_null($value)) {                                      // array instead of raw value to allow binding/
 682                $value = array('clob' => (string)$value);                // executing code later to know about its nature
 683            }
 684
 685        } else if ($value === '') {
 686            if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
 687                $value = 0; // prevent '' problems in numeric fields
 688            }
 689        }
 690        return $value;
 691    }
 692
 693    /**
 694     * Transforms the sql and params in order to emulate the LIMIT clause available in other DBs
 695     *
 696     * @param string $sql the SQL select query to execute.
 697     * @param array $params array of sql parameters
 698     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 699     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 700     * @return array with the transformed sql and params updated
 701     */
 702    private function get_limit_sql($sql, array $params = null, $limitfrom=0, $limitnum=0) {
 703
 704        list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
 705        // TODO: Add the /*+ FIRST_ROWS */ hint if there isn't another hint
 706
 707        if ($limitfrom and $limitnum) {
 708            $sql = "SELECT oracle_o.*
 709                      FROM (SELECT oracle_i.*, rownum AS oracle_rownum
 710                              FROM ($sql) oracle_i
 711                             WHERE rownum <= :oracle_num_rows
 712                            ) oracle_o
 713                     WHERE oracle_rownum > :oracle_skip_rows";
 714            $params['oracle_num_rows'] = $limitfrom + $limitnum;
 715            $params['oracle_skip_rows'] = $limitfrom;
 716
 717        } else if ($limitfrom and !$limitnum) {
 718            $sql = "SELECT oracle_o.*
 719                      FROM (SELECT oracle_i.*, rownum AS oracle_rownum
 720                              FROM ($sql) oracle_i
 721                            ) oracle_o
 722                     WHERE oracle_rownum > :oracle_skip_rows";
 723            $params['oracle_skip_rows'] = $limitfrom;
 724
 725        } else if (!$limitfrom and $limitnum) {
 726            $sql = "SELECT *
 727                      FROM ($sql)
 728                     WHERE rownum <= :oracle_num_rows";
 729            $params['oracle_num_rows'] = $limitnum;
 730        }
 731
 732        return array($sql, $params);
 733    }
 734
 735    /**
 736     * This function will handle all the column values before being inserted/updated to DB for Oracle
 737     * installations. This is because the "special feature" of Oracle where the empty string is
 738     * equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
 739     * (and with empties handling in general)
 740     *
 741     * Note that this function is 100% private and should be used, exclusively by DML functions
 742     * in this file. Also, this is considered a DIRTY HACK to be removed when possible.
 743     *
 744     * This function is private and must not be used outside this driver at all
 745     *
 746     * @param $table string the table where the record is going to be inserted/updated (without prefix)
 747     * @param $field string the field where the record is going to be inserted/updated
 748     * @param $value mixed the value to be inserted/updated
 749     */
 750    private function oracle_dirty_hack ($table, $field, $value) {
 751
 752        // General bound parameter, just hack the spaces and pray it will work.
 753        if (!$table) {
 754            if ($value === '') {
 755                return ' ';
 756            } else if (is_bool($value)) {
 757                return (int)$value;
 758            } else {
 759                return $value;
 760            }
 761        }
 762
 763        // Get metadata
 764        $columns = $this->get_columns($table);
 765        if (!isset($columns[$field])) {
 766            if ($value === '') {
 767                return ' ';
 768            } else if (is_bool($value)) {
 769                return (int)$value;
 770            } else {
 771                return $value;
 772            }
 773        }
 774        $column = $columns[$field];
 775
 776        // !! This paragraph explains behaviour before Moodle 2.0:
 777        //
 778        // For Oracle DB, empty strings are converted to NULLs in DB
 779        // and this breaks a lot of NOT NULL columns currently Moodle. In the future it's
 780        // planned to move some of them to NULL, if they must accept empty values and this
 781        // piece of code will become less and less used. But, for now, we need it.
 782        // What we are going to do is to examine all the data being inserted and if it's
 783        // an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
 784        // such data in the best form possible ("0" for booleans and numbers and " " for the
 785        // rest of strings. It isn't optimal, but the only way to do so.
 786        // In the opposite, when retrieving records from Oracle, we'll decode " " back to
 787        // empty strings to allow everything to work properly. DIRTY HACK.
 788
 789        // !! These paragraphs explain the rationale about the change for Moodle 2.5:
 790        //
 791        // Before Moodle 2.0, we only used to apply this DIRTY HACK to NOT NULL columns, as
 792        // stated above, but it causes one problem in NULL columns where both empty strings
 793        // and real NULLs are stored as NULLs, being impossible to differentiate them when
 794        // being retrieved from DB.
 795        //
 796        // So, starting with Moodle 2.0, we are going to apply the DIRTY HACK to all the
 797        // CHAR/CLOB columns no matter of their nullability. That way, when retrieving
 798        // NULLABLE fields we'll get proper empties and NULLs differentiated, so we'll be able
 799        // to rely in NULL/empty/content contents without problems, until now that wasn't
 800        // possible at all.
 801        //
 802        // One space DIRTY HACK is now applied automatically for all query parameters
 803        // and results. The only problem is string concatenation where the glue must
 804        // be specified as "' '" sql fragment.
 805        //
 806        // !! Conclusions:
 807        //
 808        // From Moodle 2.5 onwards, ALL empty strings in Oracle DBs will be stored as
 809        // 1-whitespace char, ALL NULLs as NULLs and, obviously, content as content. And
 810        // those 1-whitespace chars will be converted back to empty strings by all the
 811        // get_field/record/set() functions transparently and any SQL needing direct handling
 812        // of empties will have to use placeholders or sql_isempty() helper function.
 813
 814        // If the field isn't VARCHAR or CLOB, skip
 815        if ($column->meta_type != 'C' and $column->meta_type != 'X') {
 816            return $value;
 817        }
 818
 819        // If the value isn't empty, skip
 820        if (!empty($value)) {
 821            return $value;
 822        }
 823
 824        // Now, we have one empty value, going to be inserted to one VARCHAR2 or CLOB field
 825        // Try to get the best value to be inserted
 826
 827        // The '0' string doesn't need any transformation, skip
 828        if ($value === '0') {
 829            return $value;
 830        }
 831
 832        // Transformations start
 833        if (gettype($value) == 'boolean') {
 834            return '0'; // Transform false to '0' that evaluates the same for PHP
 835
 836        } else if (gettype($value) == 'integer') {
 837            return '0'; // Transform 0 to '0' that evaluates the same for PHP
 838
 839        } else if ($value === '') {
 840            return ' '; // Transform '' to ' ' that DON'T EVALUATE THE SAME
 841                        // (we'll transform back again on get_records_XXX functions and others)!!
 842        }
 843
 844        // Fail safe to original value
 845        return $value;
 846    }
 847
 848    /**
 849     * Helper function to order by string length desc
 850     *
 851     * @param $a string first element to compare
 852     * @param $b string second element to compare
 853     * @return int < 0 $a goes first (is less), 0 $b goes first, 0 doesn't matter
 854     */
 855    private function compare_by_length_desc($a, $b) {
 856        return strlen($b) - strlen($a);
 857    }
 858
 859    /**
 860     * Is db in unicode mode?
 861     * @return bool
 862     */
 863    public function setup_is_unicodedb() {
 864        $sql = "SELECT VALUE
 865                  FROM NLS_DATABASE_PARAMETERS
 866                 WHERE PARAMETER = 'NLS_CHARACTERSET'";
 867        $this->query_start($sql, null, SQL_QUERY_AUX);
 868        $stmt = $this->parse_query($sql);
 869        $result = oci_execute($stmt, $this->commit_status);
 870        $this->query_end($result, $stmt);
 871        $records = null;
 872        oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN);
 873        oci_free_statement($stmt);
 874
 875        return (isset($records['VALUE'][0]) and $records['VALUE'][0] === 'AL32UTF8');
 876    }
 877
 878    /**
 879     * Do NOT use in code, to be used by database_manager only!
 880     * @param string|array $sql query
 881     * @param array|null $tablenames an array of xmldb table names affected by this request.
 882     * @return bool true
 883     * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
 884     */
 885    public function change_database_structure($sql, $tablenames = null) {
 886        $this->get_manager(); // Includes DDL exceptions classes ;-)
 887        $sqls = (array)$sql;
 888
 889        try {
 890            foreach ($sqls as $sql) {
 891                $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
 892                $stmt = $this->parse_query($sql);
 893                $result = oci_execute($stmt, $this->commit_status);
 894                $this->query_end($result, $stmt);
 895                oci_free_statement($stmt);
 896            }
 897        } catch (ddl_change_structure_exception $e) {
 898            $this->reset_caches($tablenames);
 899            throw $e;
 900        }
 901
 902        $this->reset_caches($tablenames);
 903        return true;
 904    }
 905
 906    protected function bind_params($stmt, array &$params=null, $tablename=null, array &$descriptors = null) {
 907        if ($params) {
 908            $columns = array();
 909            if ($tablename) {
 910                $columns = $this->get_columns($tablename);
 911            }
 912            foreach($params as $key => $value) {
 913                // Decouple column name and param name as far as sometimes they aren't the same
 914                if ($key == 'o_newfieldtoset') { // found case where column and key diverge, handle that
 915                    $columnname   = key($value);    // columnname is the key of the array
 916                    $params[$key] = $value[$columnname]; // set the proper value in the $params array and
 917                    $value        = $value[$columnname]; // set the proper value in the $value variable
 918                } else {
 919                    $columnname = preg_replace('/^o_/', '', $key); // Default columnname (for DB introspecting is key), but...
 920                }
 921                // Continue processing
 922                // Now, handle already detected LOBs
 923                if (is_array($value)) { // Let's go to bind special cases (lob descriptors)
 924                    if (isset($value['clob'])) {
 925                        $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
 926                        if ($descriptors === null) {
 927                            throw new coding_exception('moodle_database::bind_params() $descriptors not specified for clob');
 928                        }
 929                        $descriptors[] = $lob;
 930                        oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB);
 931                        $lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]['clob']), OCI_TEMP_CLOB);
 932                        continue; // Column binding finished, go to next one
 933                    } else if (isset($value['blob'])) {
 934                        $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
 935                        if ($descriptors === null) {
 936                            throw new coding_exception('moodle_database::bind_params() $descriptors not specified for clob');
 937                        }
 938                        $descriptors[] = $lob;
 939                        oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB);
 940                        $lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB);
 941                        continue; // Column binding finished, go to next one
 942                    }
 943                } else {
 944                    // If, at this point, the param value > 4000 (bytes), let's assume it's a clob
 945                    // passed in an arbitrary sql (not processed by normalise_value() ever,
 946                    // and let's handle it as such. This will provide proper binding of CLOBs in
 947                    // conditions and other raw SQLs not covered by the above function.
 948                    if (strlen($value) > 4000) {
 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_CLOB);
 955                        $lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]), OCI_TEMP_CLOB);
 956                        continue; // Param binding finished, go to next one.
 957                    }
 958                }
 959                // TODO: Put proper types and length is possible (enormous speedup)
 960                // Arrived here, continue with standard processing, using metadata if possible
 961                if (isset($columns[$columnname])) {
 962                    $type = $columns[$columnname]->meta_type;
 963                    $maxlength = $columns[$columnname]->max_length;
 964                } else {
 965                    $type = '?';
 966                    $maxlength = -1;
 967                }
 968                switch ($type) {
 969                    case 'I':
 970                    case 'R':
 971                        // TODO: Optimise
 972                        oci_bind_by_name($stmt, $key, $params[$key]);
 973                        break;
 974
 975                    case 'N':
 976                    case 'F':
 977                        // TODO: Optimise
 978                        oci_bind_by_name($stmt, $key, $params[$key]);
 979                        break;
 980
 981                    case 'B':
 982                        // TODO: Only arrive here if BLOB is null: Bind if so, else exception!
 983                        // don't break here
 984
 985                    case 'X':
 986                        // TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
 987                        // don't break here
 988
 989                    default: // Bind as CHAR (applying dirty hack)
 990                        // TODO: Optimise
 991                        $params[$key] = $this->oracle_dirty_hack($tablename, $columnname, $params[$key]);
 992                        // Because of PHP7 bug (https://bugs.php.net/bug.php?id=72524) it seems that it's
 993                        // impossible to bind NULL values in a reliable way, let's use empty string
 994                        // instead in the mean time.
 995                        if ($params[$key] === null && version_compare(PHP_VERSION, '7.0.0', '>=')) {
 996                            $params[$key] = '';
 997                        }
 998                        oci_bind_by_name($stmt, $key, $params[$key]);
 999                }
1000            }
1001        }
1002        return $descriptors;
1003    }
1004
1005    protected function free_descriptors($descriptors) {
1006        foreach ($descriptors as $descriptor) {
1007            // Because all descriptors used in the driver come from LOB::writeTemporary() calls
1008            // we can safely close them here unconditionally.
1009            $descriptor->close();
1010            // Free resources.
1011            oci_free_descriptor($descriptor);
1012        }
1013    }
1014
1015    /**
1016     * This function is used to convert all the Oracle 1-space defaults to the empty string
1017     * like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
1018     * fields will be out from Moodle.
1019     * @param string the string to be converted to '' (empty string) if it's ' ' (one space)
1020     * @param mixed the key of the array in case we are using this function from array_walk,
1021     *              defaults to null for other (direct) uses
1022     * @return boolean always true (the converted variable is returned by reference)
1023     */
1024    public static function onespace2empty(&$item, $key=null) {
1025        $item = ($item === ' ') ? '' : $item;
1026        return true;
1027    }
1028
1029    /**
1030     * Execute general sql query. Should be used only when no other method suitable.
1031     * Do NOT use this to make changes in db structure, use database_manager methods instead!
1032     * @param string $sql query
1033     * @param array $params query parameters
1034     * @return bool true
1035     * @throws dml_exception A DML specific exception is thrown for any errors.
1036     */
1037    public function execute($sql, array $params=null) {
1038        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1039
1040        if (strpos($sql, ';') !== false) {
1041            throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
1042        }
1043
1044        list($sql, $params) = $this->tweak_param_names($sql, $params);
1045        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1046        $stmt = $this->parse_query($sql);
1047        $descriptors = array();
1048        $this->bind_params($stmt, $params, null, $descriptors);
1049        $result = oci_execute($stmt, $this->commit_status);
1050        $this->free_descriptors($descriptors);
1051        $this->query_end($result, $stmt);
1052        oci_free_statement($stmt);
1053
1054        return true;
1055    }
1056
1057    /**
1058     * Get a single database record as an object using a SQL statement.
1059     *
1060     * The SQL statement should normally only return one record.
1061     * It is recommended to use get_records_sql() if more matches possible!
1062     *
1063     * @param string $sql The SQL string you wish to be executed, should normally only return one record.
1064     * @param array $params array of sql parameters
1065     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1066     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1067     *                        MUST_EXIST means throw exception if no record or multiple records found
1068     * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
1069     * @throws dml_exception A DML specific exception is thrown for any errors.
1070     */
1071    public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
1072        $strictness = (int)$strictness;
1073        if ($strictness == IGNORE_MULTIPLE) {
1074            // do not limit here - ORA does not like that
1075            $rs = $this->get_recordset_sql($sql, $params);
1076            $result = false;
1077            foreach ($rs as $rec) {
1078                $result = $rec;
1079                break;
1080            }
1081            $rs->close();
1082            return $result;
1083        }
1084        return parent::get_record_sql($sql, $params, $strictness);
1085    }
1086
1087    /**
1088     * Get a number of records as a moodle_recordset using a SQL statement.
1089     *
1090     * Since this method is a little less readable, use of it should be restricted to
1091     * code where it's possible there might be large datasets being returned.  For known
1092     * small datasets use get_records_sql - it leads to simpler code.
1093     *
1094     * The return type is like:
1095     * @see function get_recordset.
1096     *
1097     * @param string $sql the SQL select query to execute.
1098     * @param array $params array of sql parameters
1099     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1100     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1101     * @return moodle_recordset instance
1102     * @throws dml_exception A DML specific exception is thrown for any errors.
1103     */
1104    public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1105
1106        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1107
1108        list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum);
1109
1110        list($rawsql, $params) = $this->tweak_param_names($rawsql, $params);
1111        $this->query_start($rawsql, $params, SQL_QUERY_SELECT);
1112        $stmt = $this->parse_query($rawsql);
1113        $descriptors = array();
1114        $this->bind_params($stmt, $params, null, $descriptors);
1115        $result = oci_execute($stmt, $this->commit_status);
1116        $this->free_descriptors($descriptors);
1117        $this->query_end($result, $stmt);
1118
1119        return $this->create_recordset($stmt);
1120    }
1121
1122    protected function create_recordset($stmt) {
1123        return new oci_native_moodle_recordset($stmt);
1124    }
1125
1126    /**
1127     * Get a number of records as an array of objects using a SQL statement.
1128     *
1129     * Return value is like:
1130     * @see function get_records.
1131     *
1132     * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1133     *   must be a unique value (usually the 'id' field), as it will be used as the key of the
1134     *   returned array.
1135     * @param array $params array of sql parameters
1136     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1137     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1138     * @return array of objects, or empty array if no records were found
1139     * @throws dml_exception A DML specific exception is thrown for any errors.
1140     */
1141    public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1142
1143        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1144
1145        list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum);
1146
1147        list($rawsql, $params) = $this->tweak_param_names($rawsql, $params);
1148        $this->query_start($rawsql, $params, SQL_QUERY_SELECT);
1149        $stmt = $this->parse_query($rawsql);
1150        $descriptors = array();
1151        $this->bind_params($stmt, $params, null, $descriptors);
1152        $result = oci_execute($stmt, $this->commit_status);
1153        $this->

Large files files are truncated, but you can click here to view the full file