Proyectos de Subversion Moodle

Rev

| Ultima modificación | Ver Log |

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