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
 * Abstract database driver class.
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__.'/database_column_info.php');
28
require_once(__DIR__.'/moodle_recordset.php');
29
require_once(__DIR__.'/moodle_transaction.php');
30
 
31
/** SQL_PARAMS_NAMED - Bitmask, indicates :name type parameters are supported by db backend. */
32
define('SQL_PARAMS_NAMED', 1);
33
 
34
/** SQL_PARAMS_QM - Bitmask, indicates ? type parameters are supported by db backend. */
35
define('SQL_PARAMS_QM', 2);
36
 
37
/** SQL_PARAMS_DOLLAR - Bitmask, indicates $1, $2, ... type parameters are supported by db backend. */
38
define('SQL_PARAMS_DOLLAR', 4);
39
 
40
/** SQL_QUERY_SELECT - Normal select query, reading only. */
41
define('SQL_QUERY_SELECT', 1);
42
 
43
/** SQL_QUERY_INSERT - Insert select query, writing. */
44
define('SQL_QUERY_INSERT', 2);
45
 
46
/** SQL_QUERY_UPDATE - Update select query, writing. */
47
define('SQL_QUERY_UPDATE', 3);
48
 
49
/** SQL_QUERY_STRUCTURE - Query changing db structure, writing. */
50
define('SQL_QUERY_STRUCTURE', 4);
51
 
52
/** SQL_QUERY_AUX - Auxiliary query done by driver, setting connection config, getting table info, etc. */
53
define('SQL_QUERY_AUX', 5);
54
 
55
/** SQL_QUERY_AUX_READONLY - Auxiliary query that can be done using the readonly connection:
56
 * database parameters, table/index/column lists, if not within transaction/ddl. */
57
define('SQL_QUERY_AUX_READONLY', 6);
58
 
59
/**
60
 * Abstract class representing moodle database interface.
61
 * @link https://moodledev.io/docs/apis/core/dml/ddl
62
 *
63
 * @package    core_dml
64
 * @copyright  2008 Petr Skoda (http://skodak.org)
65
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
66
 */
67
abstract class moodle_database {
68
 
69
    /** @var database_manager db manager which allows db structure modifications. */
70
    protected $database_manager;
71
    /** @var moodle_temptables temptables manager to provide cross-db support for temp tables. */
72
    protected $temptables;
73
    /** @var array Cache of table info. */
74
    protected $tables  = null;
75
 
76
    // db connection options
77
    /** @var string db host name. */
78
    protected $dbhost;
79
    /** @var string db host user. */
80
    protected $dbuser;
81
    /** @var string db host password. */
82
    protected $dbpass;
83
    /** @var string db name. */
84
    protected $dbname;
85
    /** @var string Prefix added to table names. */
86
    protected $prefix;
87
 
88
    /** @var array Database or driver specific options, such as sockets or TCP/IP db connections. */
89
    protected $dboptions;
90
 
91
    /** @var bool True means non-moodle external database used.*/
92
    protected $external;
93
 
94
    /** @var int The database reads (performance counter).*/
95
    protected $reads = 0;
96
    /** @var int The database writes (performance counter).*/
97
    protected $writes = 0;
98
    /** @var float Time queries took to finish, seconds with microseconds.*/
99
    protected $queriestime = 0;
100
 
101
    /** @var int Debug level. */
102
    protected $debug  = 0;
103
 
104
    /** @var string Last used query sql. */
105
    protected $last_sql;
106
    /** @var array Last query parameters. */
107
    protected $last_params;
108
    /** @var int Last query type. */
109
    protected $last_type;
110
    /** @var string Last extra info. */
111
    protected $last_extrainfo;
112
    /** @var float Last time in seconds with millisecond precision. */
113
    protected $last_time;
114
    /** @var bool Flag indicating logging of query in progress. This helps prevent infinite loops. */
115
    protected $loggingquery = false;
116
 
117
    /** @var bool True if the db is used for db sessions. */
118
    protected $used_for_db_sessions = false;
119
 
120
    /** @var array Array containing open transactions. */
121
    protected $transactions = array();
122
    /** @var bool Flag used to force rollback of all current transactions. */
123
    private $force_rollback = false;
124
 
125
    /** @var string MD5 of settings used for connection. Used by MUC as an identifier. */
126
    private $settingshash;
127
 
128
    /** @var cache_application for column info */
129
    protected $metacache;
130
 
131
    /** @var cache_application|cache_session|cache_store for column info on temp tables */
132
    protected $metacachetemp;
133
 
134
    /** @var bool flag marking database instance as disposed */
135
    protected $disposed;
136
 
137
    /**
138
     * @var int internal temporary variable used to fix params. Its used by {@link _fix_sql_params_dollar_callback()}.
139
     */
140
    private $fix_sql_params_i;
141
    /**
142
     * @var int internal temporary variable used to guarantee unique parameters in each request. Its used by {@link get_in_or_equal()}.
143
     */
144
    protected $inorequaluniqueindex = 1;
145
 
146
    /**
147
     * @var boolean variable use to temporarily disable logging.
148
     */
149
    protected $skiplogging = false;
150
 
151
    /**
152
     * Constructor - Instantiates the database, specifying if it's external (connect to other systems) or not (Moodle DB).
153
     *              Note that this affects the decision of whether prefix checks must be performed or not.
154
     * @param bool $external True means that an external database is used.
155
     */
156
    public function __construct($external=false) {
157
        $this->external  = $external;
158
    }
159
 
160
    /**
161
     * Destructor - cleans up and flushes everything needed.
162
     */
163
    public function __destruct() {
164
        $this->dispose();
165
    }
166
 
167
    /**
168
     * Detects if all needed PHP stuff are installed for DB connectivity.
169
     * Note: can be used before connect()
170
     * @return mixed True if requirements are met, otherwise a string if something isn't installed.
171
     */
172
    abstract public function driver_installed();
173
 
174
    /**
175
     * Returns database table prefix
176
     * Note: can be used before connect()
177
     * @return string The prefix used in the database.
178
     */
179
    public function get_prefix() {
180
        return $this->prefix;
181
    }
182
 
183
    /**
184
     * Loads and returns a database instance with the specified type and library.
185
     *
186
     * The loaded class is within lib/dml directory and of the form: $type.'_'.$library.'_moodle_database'
187
     *
188
     * @param string $type Database driver's type. (eg: mysqli, pgsql, mssql, sqldrv, oci, etc.)
189
     * @param string $library Database driver's library (native, pdo, etc.)
190
     * @param bool $external True if this is an external database.
191
     * @return ?moodle_database driver object or null if error, for example of driver object see {@see mysqli_native_moodle_database}
192
     */
193
    public static function get_driver_instance($type, $library, $external = false) {
194
        global $CFG;
195
 
196
        $classname = $type.'_'.$library.'_moodle_database';
197
        $libfile   = "$CFG->libdir/dml/$classname.php";
198
 
199
        if (!file_exists($libfile)) {
200
            return null;
201
        }
202
 
203
        require_once($libfile);
204
        return new $classname($external);
205
    }
206
 
207
    /**
208
     * Returns the database vendor.
209
     * Note: can be used before connect()
210
     * @return string The db vendor name, usually the same as db family name.
211
     */
212
    public function get_dbvendor() {
213
        return $this->get_dbfamily();
214
    }
215
 
216
    /**
217
     * Returns the database family type. (This sort of describes the SQL 'dialect')
218
     * Note: can be used before connect()
219
     * @return string The db family name (mysql, postgres, mssql, oracle, etc.)
220
     */
221
    abstract public function get_dbfamily();
222
 
223
    /**
224
     * Returns a more specific database driver type
225
     * Note: can be used before connect()
226
     * @return string The db type mysqli, pgsql, oci, mssql, sqlsrv
227
     */
228
    abstract protected function get_dbtype();
229
 
230
    /**
231
     * Returns the general database library name
232
     * Note: can be used before connect()
233
     * @return string The db library type -  pdo, native etc.
234
     */
235
    abstract protected function get_dblibrary();
236
 
237
    /**
238
     * Returns the localised database type name
239
     * Note: can be used before connect()
240
     * @return string
241
     */
242
    abstract public function get_name();
243
 
244
    /**
245
     * Returns the localised database configuration help.
246
     * Note: can be used before connect()
247
     * @return string
248
     */
249
    abstract public function get_configuration_help();
250
 
251
    /**
252
     * Returns the localised database description
253
     * Note: can be used before connect()
254
     * @deprecated since 2.6
255
     * @return string
256
     */
257
    public function get_configuration_hints() {
258
        debugging('$DB->get_configuration_hints() method is deprecated, use $DB->get_configuration_help() instead');
259
        return $this->get_configuration_help();
260
    }
261
 
262
    /**
263
     * Returns the db related part of config.php
264
     * @return stdClass
265
     */
266
    public function export_dbconfig() {
267
        $cfg = new stdClass();
268
        $cfg->dbtype    = $this->get_dbtype();
269
        $cfg->dblibrary = $this->get_dblibrary();
270
        $cfg->dbhost    = $this->dbhost;
271
        $cfg->dbname    = $this->dbname;
272
        $cfg->dbuser    = $this->dbuser;
273
        $cfg->dbpass    = $this->dbpass;
274
        $cfg->prefix    = $this->prefix;
275
        if ($this->dboptions) {
276
            $cfg->dboptions = $this->dboptions;
277
        }
278
 
279
        return $cfg;
280
    }
281
 
282
    /**
283
     * Diagnose database and tables, this function is used
284
     * to verify database and driver settings, db engine types, etc.
285
     *
286
     * @return string null means everything ok, string means problem found.
287
     */
288
    public function diagnose() {
289
        return null;
290
    }
291
 
292
    /**
293
     * Connects to the database.
294
     * Must be called before other methods.
295
     * @param string $dbhost The database host.
296
     * @param string $dbuser The database user to connect as.
297
     * @param string $dbpass The password to use when connecting to the database.
298
     * @param string $dbname The name of the database being connected to.
299
     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
300
     * @param array $dboptions driver specific options
301
     * @return bool true
302
     * @throws dml_connection_exception if error
303
     */
304
    abstract public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null);
305
 
306
    /**
307
     * Store various database settings
308
     * @param string $dbhost The database host.
309
     * @param string $dbuser The database user to connect as.
310
     * @param string $dbpass The password to use when connecting to the database.
311
     * @param string $dbname The name of the database being connected to.
312
     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
313
     * @param array $dboptions driver specific options
314
     * @return void
315
     */
316
    protected function store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
317
        $this->dbhost    = $dbhost;
318
        $this->dbuser    = $dbuser;
319
        $this->dbpass    = $dbpass;
320
        $this->dbname    = $dbname;
321
        $this->prefix    = $prefix;
322
        $this->dboptions = (array)$dboptions;
323
    }
324
 
325
    /**
326
     * Returns a hash for the settings used during connection.
327
     *
328
     * If not already requested it is generated and stored in a private property.
329
     *
330
     * @return string
331
     */
332
    protected function get_settings_hash() {
333
        if (empty($this->settingshash)) {
334
            $this->settingshash = md5($this->dbhost . $this->dbuser . $this->dbname . $this->prefix);
335
        }
336
        return $this->settingshash;
337
    }
338
 
339
    /**
340
     * Handle the creation and caching of the databasemeta information for all databases.
341
     *
342
     * @return cache_application The databasemeta cachestore to complete operations on.
343
     */
344
    protected function get_metacache() {
345
        if (!isset($this->metacache)) {
346
            $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash());
347
            $this->metacache = cache::make('core', 'databasemeta', $properties);
348
        }
349
        return $this->metacache;
350
    }
351
 
352
    /**
353
     * Handle the creation and caching of the temporary tables.
354
     *
355
     * @return cache_application The temp_tables cachestore to complete operations on.
356
     */
357
    protected function get_temp_tables_cache() {
358
        if (!isset($this->metacachetemp)) {
359
            // Using connection data to prevent collisions when using the same temp table name with different db connections.
360
            $properties = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash());
361
            $this->metacachetemp = cache::make('core', 'temp_tables', $properties);
362
        }
363
        return $this->metacachetemp;
364
    }
365
 
366
    /**
367
     * Attempt to create the database
368
     * @param string $dbhost The database host.
369
     * @param string $dbuser The database user to connect as.
370
     * @param string $dbpass The password to use when connecting to the database.
371
     * @param string $dbname The name of the database being connected to.
372
     * @param array $dboptions An array of optional database options (eg: dbport)
373
     *
374
     * @return bool success True for successful connection. False otherwise.
375
     */
376
    public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
377
        return false;
378
    }
379
 
380
    /**
381
     * Returns transaction trace for debugging purposes.
382
     * @private to be used by core only
383
     * @return ?array or null if not in transaction.
384
     */
385
    public function get_transaction_start_backtrace() {
386
        if (!$this->transactions) {
387
            return null;
388
        }
389
        $lowesttransaction = end($this->transactions);
390
        return $lowesttransaction->get_backtrace();
391
    }
392
 
393
    /**
394
     * Closes the database connection and releases all resources
395
     * and memory (especially circular memory references).
396
     * Do NOT use connect() again, create a new instance if needed.
397
     * @return void
398
     */
399
    public function dispose() {
400
        if ($this->disposed) {
401
            return;
402
        }
403
        $this->disposed = true;
404
        if ($this->transactions) {
405
            $this->force_transaction_rollback();
406
        }
407
 
408
        if ($this->temptables) {
409
            $this->temptables->dispose();
410
            $this->temptables = null;
411
        }
412
        if ($this->database_manager) {
413
            $this->database_manager->dispose();
414
            $this->database_manager = null;
415
        }
416
        $this->tables  = null;
417
    }
418
 
419
    /**
420
     * This should be called before each db query.
421
     *
422
     * @param string $sql The query string.
423
     * @param array|null $params An array of parameters.
424
     * @param int $type The type of query ( SQL_QUERY_SELECT | SQL_QUERY_AUX_READONLY | SQL_QUERY_AUX |
425
     *                  SQL_QUERY_INSERT | SQL_QUERY_UPDATE | SQL_QUERY_STRUCTURE ).
426
     * @param mixed $extrainfo This is here for any driver specific extra information.
427
     * @return void
428
     */
429
    protected function query_start($sql, ?array $params, $type, $extrainfo=null) {
430
        if ($this->loggingquery) {
431
            return;
432
        }
433
        $this->last_sql       = $sql;
434
        $this->last_params    = $params;
435
        $this->last_type      = $type;
436
        $this->last_extrainfo = $extrainfo;
437
        $this->last_time      = microtime(true);
438
 
439
        switch ($type) {
440
            case SQL_QUERY_SELECT:
441
            case SQL_QUERY_AUX:
442
            case SQL_QUERY_AUX_READONLY:
443
                $this->reads++;
444
                break;
445
            case SQL_QUERY_INSERT:
446
            case SQL_QUERY_UPDATE:
447
            case SQL_QUERY_STRUCTURE:
448
                $this->writes++;
449
            default:
450
                if ((PHPUNIT_TEST) || (defined('BEHAT_TEST') && BEHAT_TEST) ||
451
                    defined('BEHAT_SITE_RUNNING')) {
452
 
453
                    // Set list of tables that are updated.
454
                    require_once(__DIR__.'/../testing/classes/util.php');
455
                    testing_util::set_table_modified_by_sql($sql);
456
                }
457
        }
458
 
459
        $this->print_debug($sql, $params);
460
    }
461
 
462
    /**
463
     * This should be called immediately after each db query. It does a clean up of resources.
464
     * It also throws exceptions if the sql that ran produced errors.
465
     * @param mixed $result The db specific result obtained from running a query.
466
     * @throws dml_read_exception | dml_write_exception | ddl_change_structure_exception
467
     * @return void
468
     */
469
    protected function query_end($result) {
470
        if ($this->loggingquery) {
471
            return;
472
        }
473
        if ($result !== false) {
474
            $this->query_log();
475
            // free memory
476
            $this->last_sql    = null;
477
            $this->last_params = null;
478
            $this->print_debug_time();
479
            return;
480
        }
481
 
482
        // remember current info, log queries may alter it
483
        $type   = $this->last_type;
484
        $sql    = $this->last_sql;
485
        $params = $this->last_params;
486
        $error  = $this->get_last_error();
487
 
488
        $this->query_log($error);
489
 
490
        switch ($type) {
491
            case SQL_QUERY_SELECT:
492
            case SQL_QUERY_AUX:
493
            case SQL_QUERY_AUX_READONLY:
494
                throw new dml_read_exception($error, $sql, $params);
495
            case SQL_QUERY_INSERT:
496
            case SQL_QUERY_UPDATE:
497
                throw new dml_write_exception($error, $sql, $params);
498
            case SQL_QUERY_STRUCTURE:
499
                $this->get_manager(); // includes ddl exceptions classes ;-)
500
                throw new ddl_change_structure_exception($error, $sql);
501
        }
502
    }
503
 
504
    /**
505
     * This logs the last query based on 'logall', 'logslow' and 'logerrors' options configured via $CFG->dboptions .
506
     * @param string|bool $error or false if not error
507
     * @return void
508
     */
509
    public function query_log($error=false) {
510
        // Logging disabled by the driver.
511
        if ($this->skiplogging) {
512
            return;
513
        }
514
 
515
        $logall    = !empty($this->dboptions['logall']);
516
        $logslow   = !empty($this->dboptions['logslow']) ? $this->dboptions['logslow'] : false;
517
        $logerrors = !empty($this->dboptions['logerrors']);
518
        $iserror   = ($error !== false);
519
 
520
        $time = $this->query_time();
521
 
522
        // Will be shown or not depending on MDL_PERF values rather than in dboptions['log*].
523
        $this->queriestime = $this->queriestime + $time;
524
 
525
        if ($logall or ($logslow and ($logslow < ($time+0.00001))) or ($iserror and $logerrors)) {
526
            $this->loggingquery = true;
527
            try {
528
                $backtrace = debug_backtrace();
529
                if ($backtrace) {
530
                    //remove query_log()
531
                    array_shift($backtrace);
532
                }
533
                if ($backtrace) {
534
                    //remove query_end()
535
                    array_shift($backtrace);
536
                }
537
                $log = new stdClass();
538
                $log->qtype      = $this->last_type;
539
                $log->sqltext    = $this->last_sql;
540
                $log->sqlparams  = var_export((array)$this->last_params, true);
541
                $log->error      = (int)$iserror;
542
                $log->info       = $iserror ? $error : null;
543
                $log->backtrace  = format_backtrace($backtrace, true);
544
                $log->exectime   = $time;
545
                $log->timelogged = time();
546
                $this->insert_record('log_queries', $log);
547
            } catch (Exception $ignored) {
548
            }
549
            $this->loggingquery = false;
550
        }
551
    }
552
 
553
    /**
554
     * Disable logging temporarily.
555
     */
556
    protected function query_log_prevent() {
557
        $this->skiplogging = true;
558
    }
559
 
560
    /**
561
     * Restore old logging behavior.
562
     */
563
    protected function query_log_allow() {
564
        $this->skiplogging = false;
565
    }
566
 
567
    /**
568
     * Returns the time elapsed since the query started.
569
     * @return float Seconds with microseconds
570
     */
571
    protected function query_time() {
572
        return microtime(true) - $this->last_time;
573
    }
574
 
575
    /**
576
     * Returns database server info array
577
     * @return array Array containing 'description' and 'version' at least.
578
     */
579
    abstract public function get_server_info();
580
 
581
    /**
582
     * Returns supported query parameter types
583
     * @return int bitmask of accepted SQL_PARAMS_*
584
     */
585
    abstract protected function allowed_param_types();
586
 
587
    /**
588
     * Returns the last error reported by the database engine.
589
     * @return string The error message.
590
     */
591
    abstract public function get_last_error();
592
 
593
    /**
594
     * Prints sql debug info
595
     * @param string $sql The query which is being debugged.
596
     * @param array $params The query parameters. (optional)
597
     * @param mixed $obj The library specific object. (optional)
598
     * @return void
599
     */
600
    protected function print_debug($sql, array $params=null, $obj=null) {
601
        if (!$this->get_debug()) {
602
            return;
603
        }
604
        if (CLI_SCRIPT) {
605
            $separator = "--------------------------------\n";
606
            echo $separator;
607
            echo "{$sql}\n";
608
            if (!is_null($params)) {
609
                echo "[" . var_export($params, true) . "]\n";
610
            }
611
            echo $separator;
612
        } else if (AJAX_SCRIPT) {
613
            $separator = "--------------------------------";
614
            error_log($separator);
615
            error_log($sql);
616
            if (!is_null($params)) {
617
                error_log("[" . var_export($params, true) . "]");
618
            }
619
            error_log($separator);
620
        } else {
621
            $separator = "<hr />\n";
622
            echo $separator;
623
            echo s($sql) . "\n";
624
            if (!is_null($params)) {
625
                echo "[" . s(var_export($params, true)) . "]\n";
626
            }
627
            echo $separator;
628
        }
629
    }
630
 
631
    /**
632
     * Prints the time a query took to run.
633
     * @return void
634
     */
635
    protected function print_debug_time() {
636
        if (!$this->get_debug()) {
637
            return;
638
        }
639
        $time = $this->query_time();
640
        $message = "Query took: {$time} seconds.\n";
641
        if (CLI_SCRIPT) {
642
            echo $message;
643
            echo "--------------------------------\n";
644
        } else if (AJAX_SCRIPT) {
645
            error_log($message);
646
            error_log("--------------------------------");
647
        } else {
648
            echo s($message);
649
            echo "<hr />\n";
650
        }
651
    }
652
 
653
    /**
654
     * Returns the SQL WHERE conditions.
655
     *
656
     * @param string $table The table name that these conditions will be validated against.
657
     * @param array $conditions The conditions to build the where clause. (must not contain numeric indexes)
658
     * @return array An array list containing sql 'where' part and 'params'.
659
     * @throws dml_exception
660
     */
661
    protected function where_clause($table, array $conditions=null) {
662
        // We accept nulls in conditions
663
        $conditions = is_null($conditions) ? array() : $conditions;
664
 
665
        if (empty($conditions)) {
666
            return array('', array());
667
        }
668
 
669
        // Some checks performed under debugging only
670
        if (debugging()) {
671
            $columns = $this->get_columns($table);
672
            if (empty($columns)) {
673
                // no supported columns means most probably table does not exist
674
                throw new dml_exception('ddltablenotexist', $table);
675
            }
676
            foreach ($conditions as $key=>$value) {
677
                if (!isset($columns[$key])) {
678
                    $a = new stdClass();
679
                    $a->fieldname = $key;
680
                    $a->tablename = $table;
681
                    throw new dml_exception('ddlfieldnotexist', $a);
682
                }
683
                $column = $columns[$key];
684
                if ($column->meta_type == 'X') {
685
                    //ok so the column is a text column. sorry no text columns in the where clause conditions
686
                    throw new dml_exception('textconditionsnotallowed', $conditions);
687
                }
688
            }
689
        }
690
 
691
        $allowed_types = $this->allowed_param_types();
692
        $where = array();
693
        $params = array();
694
 
695
        foreach ($conditions as $key=>$value) {
696
            if (is_int($key)) {
697
                throw new dml_exception('invalidnumkey');
698
            }
699
            if (is_null($value)) {
700
                $where[] = "$key IS NULL";
701
            } else {
702
                if ($allowed_types & SQL_PARAMS_NAMED) {
703
                    // Need to verify key names because they can contain, originally,
704
                    // spaces and other forbidden chars when using sql_xxx() functions and friends.
705
                    $normkey = trim(preg_replace('/[^a-zA-Z0-9_-]/', '_', $key), '-_');
706
                    if ($normkey !== $key) {
707
                        debugging('Invalid key found in the conditions array.');
708
                    }
709
                    $where[] = "$key = :$normkey";
710
                    $params[$normkey] = $value;
711
                } else {
712
                    $where[] = "$key = ?";
713
                    $params[] = $value;
714
                }
715
            }
716
        }
717
        $where = implode(" AND ", $where);
718
        return array($where, $params);
719
    }
720
 
721
    /**
722
     * Returns SQL WHERE conditions for the ..._list group of methods.
723
     *
724
     * @param string $field the name of a field.
725
     * @param array $values the values field might take.
726
     * @return array An array containing sql 'where' part and 'params'
727
     */
728
    protected function where_clause_list($field, array $values) {
729
        if (empty($values)) {
730
            return array("1 = 2", array()); // Fake condition, won't return rows ever. MDL-17645
731
        }
732
 
733
        // Note: Do not use get_in_or_equal() because it can not deal with bools and nulls.
734
 
735
        $params = array();
736
        $select = "";
737
        $values = (array)$values;
738
        foreach ($values as $value) {
739
            if (is_bool($value)) {
740
                $value = (int)$value;
741
            }
742
            if (is_null($value)) {
743
                $select = "$field IS NULL";
744
            } else {
745
                $params[] = $value;
746
            }
747
        }
748
        if ($params) {
749
            if ($select !== "") {
750
                $select = "$select OR ";
751
            }
752
            $count = count($params);
753
            if ($count == 1) {
754
                $select = $select."$field = ?";
755
            } else {
756
                $qs = str_repeat(',?', $count);
757
                $qs = ltrim($qs, ',');
758
                $select = $select."$field IN ($qs)";
759
            }
760
        }
761
        return array($select, $params);
762
    }
763
 
764
    /**
765
     * Constructs 'IN()' or '=' sql fragment
766
     * @param mixed $items A single value or array of values for the expression.
767
     * @param int $type Parameter bounding type : SQL_PARAMS_QM or SQL_PARAMS_NAMED.
768
     * @param string $prefix Named parameter placeholder prefix (a unique counter value is appended to each parameter name).
769
     * @param bool $equal True means we want to equate to the constructed expression, false means we don't want to equate to it.
770
     * @param mixed $onemptyitems This defines the behavior when the array of items provided is empty. Defaults to false,
771
     *              meaning throw exceptions. Other values will become part of the returned SQL fragment.
772
     * @throws coding_exception | dml_exception
773
     * @return array A list containing the constructed sql fragment and an array of parameters.
774
     */
775
    public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false) {
776
 
777
        // default behavior, throw exception on empty array
778
        if (is_array($items) and empty($items) and $onemptyitems === false) {
779
            throw new coding_exception('moodle_database::get_in_or_equal() does not accept empty arrays');
780
        }
781
        // handle $onemptyitems on empty array of items
782
        if (is_array($items) and empty($items)) {
783
            if (is_null($onemptyitems)) {             // Special case, NULL value
784
                $sql = $equal ? ' IS NULL' : ' IS NOT NULL';
785
                return (array($sql, array()));
786
            } else {
787
                $items = array($onemptyitems);        // Rest of cases, prepare $items for std processing
788
            }
789
        }
790
 
791
        if ($type == SQL_PARAMS_QM) {
792
            if (!is_array($items) or count($items) == 1) {
793
                $sql = $equal ? '= ?' : '<> ?';
794
                $items = (array)$items;
795
                $params = array_values($items);
796
            } else {
797
                if ($equal) {
798
                    $sql = 'IN ('.implode(',', array_fill(0, count($items), '?')).')';
799
                } else {
800
                    $sql = 'NOT IN ('.implode(',', array_fill(0, count($items), '?')).')';
801
                }
802
                $params = array_values($items);
803
            }
804
 
805
        } else if ($type == SQL_PARAMS_NAMED) {
806
            if (empty($prefix)) {
807
                $prefix = 'param';
808
            }
809
 
810
            if (!is_array($items)){
811
                $param = $prefix.$this->inorequaluniqueindex++;
812
                $sql = $equal ? "= :$param" : "<> :$param";
813
                $params = array($param=>$items);
814
            } else if (count($items) == 1) {
815
                $param = $prefix.$this->inorequaluniqueindex++;
816
                $sql = $equal ? "= :$param" : "<> :$param";
817
                $item = reset($items);
818
                $params = array($param=>$item);
819
            } else {
820
                $params = array();
821
                $sql = array();
822
                foreach ($items as $item) {
823
                    $param = $prefix.$this->inorequaluniqueindex++;
824
                    $params[$param] = $item;
825
                    $sql[] = ':'.$param;
826
                }
827
                if ($equal) {
828
                    $sql = 'IN ('.implode(',', $sql).')';
829
                } else {
830
                    $sql = 'NOT IN ('.implode(',', $sql).')';
831
                }
832
            }
833
 
834
        } else {
835
            throw new dml_exception('typenotimplement');
836
        }
837
        return array($sql, $params);
838
    }
839
 
840
    /**
841
     * Converts short table name {tablename} to the real prefixed table name in given sql.
842
     * @param string $sql The sql to be operated on.
843
     * @return string The sql with tablenames being prefixed with $CFG->prefix
844
     */
845
    protected function fix_table_names($sql) {
846
        return preg_replace_callback(
847
            '/\{([a-z][a-z0-9_]*)\}/',
848
            function($matches) {
849
                return $this->fix_table_name($matches[1]);
850
            },
851
            $sql
852
        );
853
    }
854
 
855
    /**
856
     * Adds the prefix to the table name.
857
     *
858
     * @param string $tablename The table name
859
     * @return string The prefixed table name
860
     */
861
    protected function fix_table_name($tablename) {
862
        return $this->prefix . $tablename;
863
    }
864
 
865
    /**
866
     * Internal private utitlity function used to fix parameters.
867
     * Used with {@link preg_replace_callback()}
868
     * @param array $match Refer to preg_replace_callback usage for description.
869
     * @return string
870
     */
871
    private function _fix_sql_params_dollar_callback($match) {
872
        $this->fix_sql_params_i++;
873
        return "\$".$this->fix_sql_params_i;
874
    }
875
 
876
    /**
877
     * Detects object parameters and throws exception if found
878
     * @param mixed $value
879
     * @return void
880
     * @throws coding_exception if object detected
881
     */
882
    protected function detect_objects($value) {
883
        if (is_object($value)) {
884
            throw new coding_exception('Invalid database query parameter value', 'Objects are are not allowed: '.get_class($value));
885
        }
886
    }
887
 
888
    /**
889
     * Normalizes sql query parameters and verifies parameters.
890
     * @param string $sql The query or part of it.
891
     * @param array $params The query parameters.
892
     * @return array (sql, params, type of params)
893
     */
894
    public function fix_sql_params($sql, array $params=null) {
895
        global $CFG;
896
 
897
        require_once($CFG->libdir . '/ddllib.php');
898
 
899
        $params = (array)$params; // mke null array if needed
900
        $allowed_types = $this->allowed_param_types();
901
 
902
        // convert table names
903
        $sql = $this->fix_table_names($sql);
904
 
905
        // cast booleans to 1/0 int and detect forbidden objects
906
        foreach ($params as $key => $value) {
907
            $this->detect_objects($value);
908
            $params[$key] = is_bool($value) ? (int)$value : $value;
909
        }
910
 
911
        // NICOLAS C: Fixed regexp for negative backwards look-ahead of double colons. Thanks for Sam Marshall's help
912
        $named_count = preg_match_all('/(?<!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts
913
        $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches);
914
        $q_count     = substr_count($sql, '?');
915
 
916
        // Optionally add debug trace to sql as a comment.
917
        $sql = $this->add_sql_debugging($sql);
918
 
919
        $count = 0;
920
 
921
        if ($named_count) {
922
            $type = SQL_PARAMS_NAMED;
923
            $count = $named_count;
924
 
925
        }
926
        if ($dollar_count) {
927
            if ($count) {
928
                throw new dml_exception('mixedtypesqlparam');
929
            }
930
            $type = SQL_PARAMS_DOLLAR;
931
            $count = $dollar_count;
932
 
933
        }
934
        if ($q_count) {
935
            if ($count) {
936
                throw new dml_exception('mixedtypesqlparam');
937
            }
938
            $type = SQL_PARAMS_QM;
939
            $count = $q_count;
940
 
941
        }
942
 
943
        if (!$count) {
944
             // ignore params
945
            if ($allowed_types & SQL_PARAMS_NAMED) {
946
                return array($sql, array(), SQL_PARAMS_NAMED);
947
            } else if ($allowed_types & SQL_PARAMS_QM) {
948
                return array($sql, array(), SQL_PARAMS_QM);
949
            } else {
950
                return array($sql, array(), SQL_PARAMS_DOLLAR);
951
            }
952
        }
953
 
954
        if ($count > count($params)) {
955
            $a = new stdClass;
956
            $a->expected = $count;
957
            $a->actual = count($params);
958
            throw new dml_exception('invalidqueryparam', $a);
959
        }
960
 
961
        $target_type = $allowed_types;
962
 
963
        if ($type & $allowed_types) { // bitwise AND
964
            if ($count == count($params)) {
965
                if ($type == SQL_PARAMS_QM) {
966
                    return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required
967
                } else {
968
                    //better do the validation of names below
969
                }
970
            }
971
            // needs some fixing or validation - there might be more params than needed
972
            $target_type = $type;
973
        }
974
 
975
        if ($type == SQL_PARAMS_NAMED) {
976
            $finalparams = array();
977
            foreach ($named_matches[0] as $key) {
978
                $key = trim($key, ':');
979
                if (!array_key_exists($key, $params)) {
980
                    throw new dml_exception('missingkeyinsql', $key, '');
981
                }
982
                if (strlen($key) > xmldb_field::NAME_MAX_LENGTH) {
983
                    throw new coding_exception(
984
                            "Placeholder names must be " . xmldb_field::NAME_MAX_LENGTH . " characters or shorter. '" .
985
                            $key . "' is too long.", $sql);
986
                }
987
                $finalparams[$key] = $params[$key];
988
            }
989
            if ($count != count($finalparams)) {
990
                throw new dml_exception('duplicateparaminsql');
991
            }
992
 
993
            if ($target_type & SQL_PARAMS_QM) {
994
                $sql = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', '?', $sql);
995
                return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required
996
            } else if ($target_type & SQL_PARAMS_NAMED) {
997
                return array($sql, $finalparams, SQL_PARAMS_NAMED);
998
            } else {  // $type & SQL_PARAMS_DOLLAR
999
                //lambda-style functions eat memory - we use globals instead :-(
1000
                $this->fix_sql_params_i = 0;
1001
                $sql = preg_replace_callback('/(?<!:):[a-z][a-z0-9_]*/', array($this, '_fix_sql_params_dollar_callback'), $sql);
1002
                return array($sql, array_values($finalparams), SQL_PARAMS_DOLLAR); // 0-based required
1003
            }
1004
 
1005
        } else if ($type == SQL_PARAMS_DOLLAR) {
1006
            if ($target_type & SQL_PARAMS_DOLLAR) {
1007
                return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required
1008
            } else if ($target_type & SQL_PARAMS_QM) {
1009
                $sql = preg_replace('/\$[0-9]+/', '?', $sql);
1010
                return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required
1011
            } else { //$target_type & SQL_PARAMS_NAMED
1012
                $sql = preg_replace('/\$([0-9]+)/', ':param\\1', $sql);
1013
                $finalparams = array();
1014
                foreach ($params as $key=>$param) {
1015
                    $key++;
1016
                    $finalparams['param'.$key] = $param;
1017
                }
1018
                return array($sql, $finalparams, SQL_PARAMS_NAMED);
1019
            }
1020
 
1021
        } else { // $type == SQL_PARAMS_QM
1022
            if (count($params) != $count) {
1023
                $params = array_slice($params, 0, $count);
1024
            }
1025
 
1026
            if ($target_type & SQL_PARAMS_QM) {
1027
                return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required
1028
            } else if ($target_type & SQL_PARAMS_NAMED) {
1029
                $finalparams = array();
1030
                $pname = 'param0';
1031
                $parts = explode('?', $sql);
1032
                $sql = array_shift($parts);
1033
                foreach ($parts as $part) {
1034
                    $param = array_shift($params);
1035
                    $pname++;
1036
                    $sql .= ':'.$pname.$part;
1037
                    $finalparams[$pname] = $param;
1038
                }
1039
                return array($sql, $finalparams, SQL_PARAMS_NAMED);
1040
            } else {  // $type & SQL_PARAMS_DOLLAR
1041
                //lambda-style functions eat memory - we use globals instead :-(
1042
                $this->fix_sql_params_i = 0;
1043
                $sql = preg_replace_callback('/\?/', array($this, '_fix_sql_params_dollar_callback'), $sql);
1044
                return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required
1045
            }
1046
        }
1047
    }
1048
 
1049
    /**
1050
     * Add an SQL comment to trace all sql calls back to the calling php code
1051
     * @param string $sql Original sql
1052
     * @return string Instrumented sql
1053
     */
1054
    protected function add_sql_debugging(string $sql): string {
1055
        global $CFG;
1056
 
1057
        if (!property_exists($CFG, 'debugsqltrace')) {
1058
            return $sql;
1059
        }
1060
 
1061
        $level = $CFG->debugsqltrace;
1062
 
1063
        if (empty($level)) {
1064
            return $sql;
1065
        }
1066
 
1067
        $callers = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
1068
 
1069
        // Ignore moodle_database internals.
1070
        $callers = array_filter($callers, function($caller) {
1071
            return empty($caller['class']) || $caller['class'] != 'moodle_database';
1072
        });
1073
 
1074
        $callers = array_slice($callers, 0, $level);
1075
 
1076
        $text = trim(format_backtrace($callers, true));
1077
 
1078
        // Convert all linebreaks to SQL comments, optionally
1079
        // also eating any * formatting.
1080
        $text = preg_replace("/(^|\n)\*?\s*/", "\n-- ", $text);
1081
 
1082
        // Convert all ? to 'unknown' in the sql coment so these don't get
1083
        // caught by fix_sql_params().
1084
        $text = str_replace('?', 'unknown', $text);
1085
 
1086
        // Convert tokens like :test to ::test for the same reason.
1087
        $text = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', ':\0', $text);
1088
 
1089
        return $sql . $text;
1090
    }
1091
 
1092
 
1093
    /**
1094
     * Ensures that limit params are numeric and positive integers, to be passed to the database.
1095
     * We explicitly treat null, '' and -1 as 0 in order to provide compatibility with how limit
1096
     * values have been passed historically.
1097
     *
1098
     * @param int $limitfrom Where to start results from
1099
     * @param int $limitnum How many results to return
1100
     * @return array Normalised limit params in array($limitfrom, $limitnum)
1101
     */
1102
    protected function normalise_limit_from_num($limitfrom, $limitnum) {
1103
        global $CFG;
1104
 
1105
        // We explicilty treat these cases as 0.
1106
        if ($limitfrom === null || $limitfrom === '' || $limitfrom === -1) {
1107
            $limitfrom = 0;
1108
        }
1109
        if ($limitnum === null || $limitnum === '' || $limitnum === -1) {
1110
            $limitnum = 0;
1111
        }
1112
 
1113
        if ($CFG->debugdeveloper) {
1114
            if (!is_numeric($limitfrom)) {
1115
                $strvalue = var_export($limitfrom, true);
1116
                debugging("Non-numeric limitfrom parameter detected: $strvalue, did you pass the correct arguments?",
1117
                    DEBUG_DEVELOPER);
1118
            } else if ($limitfrom < 0) {
1119
                debugging("Negative limitfrom parameter detected: $limitfrom, did you pass the correct arguments?",
1120
                    DEBUG_DEVELOPER);
1121
            }
1122
 
1123
            if (!is_numeric($limitnum)) {
1124
                $strvalue = var_export($limitnum, true);
1125
                debugging("Non-numeric limitnum parameter detected: $strvalue, did you pass the correct arguments?",
1126
                    DEBUG_DEVELOPER);
1127
            } else if ($limitnum < 0) {
1128
                debugging("Negative limitnum parameter detected: $limitnum, did you pass the correct arguments?",
1129
                    DEBUG_DEVELOPER);
1130
            }
1131
        }
1132
 
1133
        $limitfrom = (int)$limitfrom;
1134
        $limitnum  = (int)$limitnum;
1135
        $limitfrom = max(0, $limitfrom);
1136
        $limitnum  = max(0, $limitnum);
1137
 
1138
        return array($limitfrom, $limitnum);
1139
    }
1140
 
1141
    /**
1142
     * Return tables in database WITHOUT current prefix.
1143
     * @param bool $usecache if true, returns list of cached tables.
1144
     * @return array of table names in lowercase and without prefix
1145
     */
1146
    abstract public function get_tables($usecache=true);
1147
 
1148
    /**
1149
     * Return table indexes - everything lowercased.
1150
     * @param string $table The table we want to get indexes from.
1151
     * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed
1152
     */
1153
    abstract public function get_indexes($table);
1154
 
1155
    /**
1156
     * Returns detailed information about columns in table. This information is cached internally.
1157
     *
1158
     * @param string $table The table's name.
1159
     * @param bool $usecache Flag to use internal cacheing. The default is true.
1160
     * @return database_column_info[] of database_column_info objects indexed with column names
1161
     */
1162
    public function get_columns($table, $usecache = true): array {
1163
        if (!$table) { // Table not specified, return empty array directly.
1164
            return [];
1165
        }
1166
 
1167
        if ($usecache) {
1168
            if ($this->temptables->is_temptable($table)) {
1169
                if ($data = $this->get_temp_tables_cache()->get($table)) {
1170
                    return $data;
1171
                }
1172
            } else {
1173
                if ($data = $this->get_metacache()->get($table)) {
1174
                    return $data;
1175
                }
1176
            }
1177
        }
1178
 
1179
        $structure = $this->fetch_columns($table);
1180
 
1181
        if ($usecache) {
1182
            if ($this->temptables->is_temptable($table)) {
1183
                $this->get_temp_tables_cache()->set($table, $structure);
1184
            } else {
1185
                $this->get_metacache()->set($table, $structure);
1186
            }
1187
        }
1188
 
1189
        return $structure;
1190
    }
1191
 
1192
    /**
1193
     * Returns detailed information about columns in table. This information is cached internally.
1194
     *
1195
     * @param string $table The table's name.
1196
     * @return database_column_info[] of database_column_info objects indexed with column names
1197
     */
1198
    abstract protected function fetch_columns(string $table): array;
1199
 
1200
    /**
1201
     * Normalise values based on varying RDBMS's dependencies (booleans, LOBs...)
1202
     *
1203
     * @param database_column_info $column column metadata corresponding with the value we are going to normalise
1204
     * @param mixed $value value we are going to normalise
1205
     * @return mixed the normalised value
1206
     */
1207
    abstract protected function normalise_value($column, $value);
1208
 
1209
    /**
1210
     * Resets the internal column details cache
1211
     *
1212
     * @param array|null $tablenames an array of xmldb table names affected by this request.
1213
     * @return void
1214
     */
1215
    public function reset_caches($tablenames = null) {
1216
        if (!empty($tablenames)) {
1217
            $dbmetapurged = false;
1218
            foreach ($tablenames as $tablename) {
1219
                if ($this->temptables->is_temptable($tablename)) {
1220
                    $this->get_temp_tables_cache()->delete($tablename);
1221
                } else if ($dbmetapurged === false) {
1222
                    $this->tables = null;
1223
                    $this->get_metacache()->purge();
1224
                    $this->metacache = null;
1225
                    $dbmetapurged = true;
1226
                }
1227
            }
1228
        } else {
1229
            $this->get_temp_tables_cache()->purge();
1230
            $this->tables = null;
1231
            // Purge MUC as well.
1232
            $this->get_metacache()->purge();
1233
            $this->metacache = null;
1234
        }
1235
    }
1236
 
1237
    /**
1238
     * Returns the sql generator used for db manipulation.
1239
     * Used mostly in upgrade.php scripts.
1240
     * @return database_manager The instance used to perform ddl operations.
1241
     * @see lib/ddl/database_manager.php
1242
     */
1243
    public function get_manager() {
1244
        global $CFG;
1245
 
1246
        if (!$this->database_manager) {
1247
            require_once($CFG->libdir.'/ddllib.php');
1248
 
1249
            $classname = $this->get_dbfamily().'_sql_generator';
1250
            require_once("$CFG->libdir/ddl/$classname.php");
1251
            $generator = new $classname($this, $this->temptables);
1252
 
1253
            $this->database_manager = new database_manager($this, $generator);
1254
        }
1255
        return $this->database_manager;
1256
    }
1257
 
1258
    /**
1259
     * Attempts to change db encoding to UTF-8 encoding if possible.
1260
     * @return bool True is successful.
1261
     */
1262
    public function change_db_encoding() {
1263
        return false;
1264
    }
1265
 
1266
    /**
1267
     * Checks to see if the database is in unicode mode?
1268
     * @return bool
1269
     */
1270
    public function setup_is_unicodedb() {
1271
        return true;
1272
    }
1273
 
1274
    /**
1275
     * Enable/disable very detailed debugging.
1276
     * @param bool $state
1277
     * @return void
1278
     */
1279
    public function set_debug($state) {
1280
        $this->debug = $state;
1281
    }
1282
 
1283
    /**
1284
     * Returns debug status
1285
     * @return bool $state
1286
     */
1287
    public function get_debug() {
1288
        return $this->debug;
1289
    }
1290
 
1291
    /**
1292
     * Enable/disable detailed sql logging
1293
     *
1294
     * @deprecated since Moodle 2.9
1295
     */
1296
    public function set_logging($state) {
1297
        throw new coding_exception('set_logging() can not be used any more.');
1298
    }
1299
 
1300
    /**
1301
     * Do NOT use in code, this is for use by database_manager only!
1302
     * @param string|array $sql query or array of queries
1303
     * @param array|null $tablenames an array of xmldb table names affected by this request.
1304
     * @return bool true
1305
     * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
1306
     */
1307
    abstract public function change_database_structure($sql, $tablenames = null);
1308
 
1309
    /**
1310
     * Executes a general sql query. Should be used only when no other method suitable.
1311
     * Do NOT use this to make changes in db structure, use database_manager methods instead!
1312
     * @param string $sql query
1313
     * @param array $params query parameters
1314
     * @return bool true
1315
     * @throws dml_exception A DML specific exception is thrown for any errors.
1316
     */
1317
    abstract public function execute($sql, array $params=null);
1318
 
1319
    /**
1320
     * Get a number of records as a moodle_recordset where all the given conditions met.
1321
     *
1322
     * Selects records from the table $table.
1323
     *
1324
     * If specified, only records meeting $conditions.
1325
     *
1326
     * If specified, the results will be sorted as specified by $sort. This
1327
     * is added to the SQL as "ORDER BY $sort". Example values of $sort
1328
     * might be "time ASC" or "time DESC".
1329
     *
1330
     * If $fields is specified, only those fields are returned.
1331
     *
1332
     * Since this method is a little less readable, use of it should be restricted to
1333
     * code where it's possible there might be large datasets being returned.  For known
1334
     * small datasets use get_records - it leads to simpler code.
1335
     *
1336
     * If you only want some of the records, specify $limitfrom and $limitnum.
1337
     * The query will skip the first $limitfrom records (according to the sort
1338
     * order) and then return the next $limitnum records. If either of $limitfrom
1339
     * or $limitnum is specified, both must be present.
1340
     *
1341
     * The return value is a moodle_recordset
1342
     * if the query succeeds. If an error occurs, false is returned.
1343
     *
1344
     * @param string $table the table to query.
1345
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1346
     * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1347
     * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
1348
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1349
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1350
     * @return moodle_recordset A moodle_recordset instance
1351
     * @throws dml_exception A DML specific exception is thrown for any errors.
1352
     */
1353
    public function get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1354
        list($select, $params) = $this->where_clause($table, $conditions);
1355
        return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1356
    }
1357
 
1358
    /**
1359
     * Get a number of records as a moodle_recordset where one field match one list of values.
1360
     *
1361
     * Only records where $field takes one of the values $values are returned.
1362
     * $values must be an array of values.
1363
     *
1364
     * Other arguments and the return type are like {@link function get_recordset}.
1365
     *
1366
     * @param string $table the table to query.
1367
     * @param string $field a field to check (optional).
1368
     * @param array $values array of values the field must have
1369
     * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1370
     * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
1371
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1372
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1373
     * @return moodle_recordset A moodle_recordset instance.
1374
     * @throws dml_exception A DML specific exception is thrown for any errors.
1375
     */
1376
    public function get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1377
        list($select, $params) = $this->where_clause_list($field, $values);
1378
        return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1379
    }
1380
 
1381
    /**
1382
     * Get a number of records as a moodle_recordset which match a particular WHERE clause.
1383
     *
1384
     * If given, $select is used as the SELECT parameter in the SQL query,
1385
     * otherwise all records from the table are returned.
1386
     *
1387
     * Other arguments and the return type are like {@link function get_recordset}.
1388
     *
1389
     * @param string $table the table to query.
1390
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1391
     * @param array $params array of sql parameters
1392
     * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1393
     * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
1394
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1395
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1396
     * @return moodle_recordset A moodle_recordset instance.
1397
     * @throws dml_exception A DML specific exception is thrown for any errors.
1398
     */
1399
    public function get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1400
        $sql = "SELECT $fields FROM {".$table."}";
1401
        if ($select) {
1402
            $sql .= " WHERE $select";
1403
        }
1404
        if ($sort) {
1405
            $sql .= " ORDER BY $sort";
1406
        }
1407
        return $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
1408
    }
1409
 
1410
    /**
1411
     * Get a number of records as a moodle_recordset using a SQL statement.
1412
     *
1413
     * Since this method is a little less readable, use of it should be restricted to
1414
     * code where it's possible there might be large datasets being returned.  For known
1415
     * small datasets use get_records_sql - it leads to simpler code.
1416
     *
1417
     * The return type is like {@link function get_recordset}.
1418
     *
1419
     * @param string $sql the SQL select query to execute.
1420
     * @param array $params array of sql parameters
1421
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1422
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1423
     * @return moodle_recordset A moodle_recordset instance.
1424
     * @throws dml_exception A DML specific exception is thrown for any errors.
1425
     */
1426
    abstract public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
1427
 
1428
    /**
1429
     * Get all records from a table.
1430
     *
1431
     * This method works around potential memory problems and may improve performance,
1432
     * this method may block access to table until the recordset is closed.
1433
     *
1434
     * @param string $table Name of database table.
1435
     * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}.
1436
     * @throws dml_exception A DML specific exception is thrown for any errors.
1437
     */
1438
    public function export_table_recordset($table) {
1439
        return $this->get_recordset($table, array());
1440
    }
1441
 
1442
    /**
1443
     * Get a number of records as an array of objects where all the given conditions met.
1444
     *
1445
     * If the query succeeds and returns at least one record, the
1446
     * return value is an array of objects, one object for each
1447
     * record found. The array key is the value from the first
1448
     * column of the result set. The object associated with that key
1449
     * has a member variable for each column of the results.
1450
     *
1451
     * @param string $table the table to query.
1452
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1453
     * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1454
     * @param string $fields a comma separated list of fields to return (optional, by default
1455
     *   all fields are returned). The first field will be used as key for the
1456
     *   array so must be a unique field such as 'id'.
1457
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1458
     * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set).
1459
     * @return array An array of Objects indexed by first column.
1460
     * @throws dml_exception A DML specific exception is thrown for any errors.
1461
     */
1462
    public function get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1463
        list($select, $params) = $this->where_clause($table, $conditions);
1464
        return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1465
    }
1466
 
1467
    /**
1468
     * Get a number of records as an array of objects where one field match one list of values.
1469
     *
1470
     * Return value is like {@link function get_records}.
1471
     *
1472
     * @param string $table The database table to be checked against.
1473
     * @param string $field The field to search
1474
     * @param array $values An array of values
1475
     * @param string $sort Sort order (as valid SQL sort parameter)
1476
     * @param string $fields A comma separated list of fields to be returned from the chosen table. If specified,
1477
     *   the first field should be a unique one such as 'id' since it will be used as a key in the associative
1478
     *   array.
1479
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1480
     * @param int $limitnum return a subset comprising this many records in total (optional).
1481
     * @return array An array of objects indexed by first column
1482
     * @throws dml_exception A DML specific exception is thrown for any errors.
1483
     */
1484
    public function get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1485
        list($select, $params) = $this->where_clause_list($field, $values);
1486
        return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1487
    }
1488
 
1489
    /**
1490
     * Get a number of records as an array of objects which match a particular WHERE clause.
1491
     *
1492
     * Return value is like {@link function get_records}.
1493
     *
1494
     * @param string $table The table to query.
1495
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1496
     * @param array $params An array of sql parameters
1497
     * @param string $sort An order to sort the results in (optional, a valid SQL ORDER BY parameter).
1498
     * @param string $fields A comma separated list of fields to return
1499
     *   (optional, by default all fields are returned). The first field will be used as key for the
1500
     *   array so must be a unique field such as 'id'.
1501
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1502
     * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set).
1503
     * @return array of objects indexed by first column
1504
     * @throws dml_exception A DML specific exception is thrown for any errors.
1505
     */
1506
    public function get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1507
        if ($select) {
1508
            $select = "WHERE $select";
1509
        }
1510
        if ($sort) {
1511
            $sort = " ORDER BY $sort";
1512
        }
1513
        return $this->get_records_sql("SELECT $fields FROM {" . $table . "} $select $sort", $params, $limitfrom, $limitnum);
1514
    }
1515
 
1516
    /**
1517
     * Get a number of records as an array of objects using a SQL statement.
1518
     *
1519
     * Return value is like {@link function get_records}.
1520
     *
1521
     * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1522
     *   must be a unique value (usually the 'id' field), as it will be used as the key of the
1523
     *   returned array.
1524
     * @param array $params array of sql parameters
1525
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1526
     * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set).
1527
     * @return array of objects indexed by first column
1528
     * @throws dml_exception A DML specific exception is thrown for any errors.
1529
     */
1530
    abstract public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
1531
 
1532
    /**
1533
     * Get the first two columns from a number of records as an associative array where all the given conditions met.
1534
     *
1535
     * Arguments are like {@link function get_recordset}.
1536
     *
1537
     * If no errors occur the return value
1538
     * is an associative whose keys come from the first field of each record,
1539
     * and whose values are the corresponding second fields.
1540
     * False is returned if an error occurs.
1541
     *
1542
     * @param string $table the table to query.
1543
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1544
     * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1545
     * @param string $fields a comma separated list of fields to return - the number of fields should be 2!
1546
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1547
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1548
     * @return array an associative array
1549
     * @throws dml_exception A DML specific exception is thrown for any errors.
1550
     */
1551
    public function get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1552
        $menu = array();
1553
        if ($records = $this->get_records($table, $conditions, $sort, $fields, $limitfrom, $limitnum)) {
1554
            foreach ($records as $record) {
1555
                $record = (array)$record;
1556
                $key   = array_shift($record);
1557
                $value = array_shift($record);
1558
                $menu[$key] = $value;
1559
            }
1560
        }
1561
        return $menu;
1562
    }
1563
 
1564
    /**
1565
     * Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
1566
     *
1567
     * Arguments are like {@link function get_recordset_select}.
1568
     * Return value is like {@link function get_records_menu}.
1569
     *
1570
     * @param string $table The database table to be checked against.
1571
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1572
     * @param array $params array of sql parameters
1573
     * @param string $sort Sort order (optional) - a valid SQL order parameter
1574
     * @param string $fields A comma separated list of fields to be returned from the chosen table - the number of fields should be 2!
1575
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1576
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1577
     * @return array an associative array
1578
     * @throws dml_exception A DML specific exception is thrown for any errors.
1579
     */
1580
    public function get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1581
        $menu = array();
1582
        if ($records = $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum)) {
1583
            foreach ($records as $record) {
1584
                $record = (array)$record;
1585
                $key   = array_shift($record);
1586
                $value = array_shift($record);
1587
                $menu[$key] = $value;
1588
            }
1589
        }
1590
        return $menu;
1591
    }
1592
 
1593
    /**
1594
     * Get the first two columns from a number of records as an associative array using a SQL statement.
1595
     *
1596
     * Arguments are like {@link function get_recordset_sql}.
1597
     * Return value is like {@link function get_records_menu}.
1598
     *
1599
     * @param string $sql The SQL string you wish to be executed.
1600
     * @param array $params array of sql parameters
1601
     * @param int $limitfrom return a subset of records, starting at this point (optional).
1602
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1603
     * @return array an associative array
1604
     * @throws dml_exception A DML specific exception is thrown for any errors.
1605
     */
1606
    public function get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1607
        $menu = array();
1608
        if ($records = $this->get_records_sql($sql, $params, $limitfrom, $limitnum)) {
1609
            foreach ($records as $record) {
1610
                $record = (array)$record;
1611
                $key   = array_shift($record);
1612
                $value = array_shift($record);
1613
                $menu[$key] = $value;
1614
            }
1615
        }
1616
        return $menu;
1617
    }
1618
 
1619
    /**
1620
     * Get a single database record as an object where all the given conditions met.
1621
     *
1622
     * @param string $table The table to select from.
1623
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1624
     * @param string $fields A comma separated list of fields to be returned from the chosen table.
1625
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1626
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1627
     *                        MUST_EXIST means we will throw an exception if no record or multiple records found.
1628
     *
1629
     * @todo MDL-30407 MUST_EXIST option should not throw a dml_exception, it should throw a different exception as it's a requested check.
1630
     * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
1631
     * @throws dml_exception A DML specific exception is thrown for any errors.
1632
     */
1633
    public function get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING) {
1634
        list($select, $params) = $this->where_clause($table, $conditions);
1635
        return $this->get_record_select($table, $select, $params, $fields, $strictness);
1636
    }
1637
 
1638
    /**
1639
     * Get a single database record as an object which match a particular WHERE clause.
1640
     *
1641
     * @param string $table The database table to be checked against.
1642
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1643
     * @param array $params array of sql parameters
1644
     * @param string $fields A comma separated list of fields to be returned from the chosen table.
1645
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1646
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1647
     *                        MUST_EXIST means throw exception if no record or multiple records found
1648
     * @return stdClass|false a fieldset object containing the first matching record, false or exception if error not found depending on mode
1649
     * @throws dml_exception A DML specific exception is thrown for any errors.
1650
     */
1651
    public function get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING) {
1652
        if ($select) {
1653
            $select = "WHERE $select";
1654
        }
1655
        try {
1656
            return $this->get_record_sql("SELECT $fields FROM {" . $table . "} $select", $params, $strictness);
1657
        } catch (dml_missing_record_exception $e) {
1658
            // create new exception which will contain correct table name
1659
            throw new dml_missing_record_exception($table, $e->sql, $e->params);
1660
        }
1661
    }
1662
 
1663
    /**
1664
     * Get a single database record as an object using a SQL statement.
1665
     *
1666
     * The SQL statement should normally only return one record.
1667
     * It is recommended to use get_records_sql() if more matches possible!
1668
     *
1669
     * @param string $sql The SQL string you wish to be executed, should normally only return one record.
1670
     * @param array $params array of sql parameters
1671
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1672
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1673
     *                        MUST_EXIST means throw exception if no record or multiple records found
1674
     * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
1675
     * @throws dml_exception A DML specific exception is thrown for any errors.
1676
     */
1677
    public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
1678
        $strictness = (int)$strictness; // we support true/false for BC reasons too
1679
        if ($strictness == IGNORE_MULTIPLE) {
1680
            $count = 1;
1681
        } else {
1682
            $count = 0;
1683
        }
1684
        if (!$records = $this->get_records_sql($sql, $params, 0, $count)) {
1685
            // not found
1686
            if ($strictness == MUST_EXIST) {
1687
                throw new dml_missing_record_exception('', $sql, $params);
1688
            }
1689
            return false;
1690
        }
1691
 
1692
        if (count($records) > 1) {
1693
            if ($strictness == MUST_EXIST) {
1694
                throw new dml_multiple_records_exception($sql, $params);
1695
            }
1696
            debugging('Error: mdb->get_record() found more than one record!');
1697
        }
1698
 
1699
        $return = reset($records);
1700
        return $return;
1701
    }
1702
 
1703
    /**
1704
     * Get a single field value from a table record where all the given conditions met.
1705
     *
1706
     * @param string $table the table to query.
1707
     * @param string $return the field to return the value of.
1708
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1709
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1710
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1711
     *                        MUST_EXIST means throw exception if no record or multiple records found
1712
     * @return mixed the specified value false if not found
1713
     * @throws dml_exception A DML specific exception is thrown for any errors.
1714
     */
1715
    public function get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING) {
1716
        list($select, $params) = $this->where_clause($table, $conditions);
1717
        return $this->get_field_select($table, $return, $select, $params, $strictness);
1718
    }
1719
 
1720
    /**
1721
     * Get a single field value from a table record which match a particular WHERE clause.
1722
     *
1723
     * @param string $table the table to query.
1724
     * @param string $return the field to return the value of.
1725
     * @param string $select A fragment of SQL to be used in a where clause returning one row with one column
1726
     * @param array $params array of sql parameters
1727
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1728
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1729
     *                        MUST_EXIST means throw exception if no record or multiple records found
1730
     * @return mixed the specified value false if not found
1731
     * @throws dml_exception A DML specific exception is thrown for any errors.
1732
     */
1733
    public function get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING) {
1734
        if ($select) {
1735
            $select = "WHERE $select";
1736
        }
1737
        try {
1738
            return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params, $strictness);
1739
        } catch (dml_missing_record_exception $e) {
1740
            // create new exception which will contain correct table name
1741
            throw new dml_missing_record_exception($table, $e->sql, $e->params);
1742
        }
1743
    }
1744
 
1745
    /**
1746
     * Get a single field value (first field) using a SQL statement.
1747
     *
1748
     * @param string $sql The SQL query returning one row with one column
1749
     * @param array $params array of sql parameters
1750
     * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1751
     *                        IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1752
     *                        MUST_EXIST means throw exception if no record or multiple records found
1753
     * @return mixed the specified value false if not found
1754
     * @throws dml_exception A DML specific exception is thrown for any errors.
1755
     */
1756
    public function get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
1757
        if (!$record = $this->get_record_sql($sql, $params, $strictness)) {
1758
            return false;
1759
        }
1760
 
1761
        $record = (array)$record;
1762
        return reset($record); // first column
1763
    }
1764
 
1765
    /**
1766
     * Selects records and return values of chosen field as an array where all the given conditions met.
1767
     *
1768
     * @param string $table the table to query.
1769
     * @param string $return the field we are intered in
1770
     * @param array|null $conditions optional array $fieldname=>requestedvalue with AND in between
1771
     * @return array of values
1772
     * @throws dml_exception A DML specific exception is thrown for any errors.
1773
     */
1774
    public function get_fieldset(string $table, string $return, ?array $conditions = null): array {
1775
        [$select, $params] = $this->where_clause($table, $conditions);
1776
        return $this->get_fieldset_select($table, $return, $select, $params);
1777
    }
1778
 
1779
    /**
1780
     * Selects records and return values of chosen field as an array which match a particular WHERE clause.
1781
     *
1782
     * @param string $table the table to query.
1783
     * @param string $return the field we are intered in
1784
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1785
     * @param array $params array of sql parameters
1786
     * @return array of values
1787
     * @throws dml_exception A DML specific exception is thrown for any errors.
1788
     */
1789
    public function get_fieldset_select($table, $return, $select, array $params=null) {
1790
        if ($select) {
1791
            $select = "WHERE $select";
1792
        }
1793
        return $this->get_fieldset_sql("SELECT $return FROM {" . $table . "} $select", $params);
1794
    }
1795
 
1796
    /**
1797
     * Selects records and return values (first field) as an array using a SQL statement.
1798
     *
1799
     * @param string $sql The SQL query
1800
     * @param array $params array of sql parameters
1801
     * @return array of values
1802
     * @throws dml_exception A DML specific exception is thrown for any errors.
1803
     */
1804
    abstract public function get_fieldset_sql($sql, array $params=null);
1805
 
1806
    /**
1807
     * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1808
     * @param string $table name
1809
     * @param stdClass|array $params data record as object or array
1810
     * @param bool $returnid Returns id of inserted record.
1811
     * @param bool $bulk true means repeated inserts expected
1812
     * @param bool $customsequence true if 'id' included in $params, disables $returnid
1813
     * @return bool|int true or new id
1814
     * @throws dml_exception A DML specific exception is thrown for any errors.
1815
     */
1816
    abstract public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false);
1817
 
1818
    /**
1819
     * Insert a record into a table and return the "id" field if required.
1820
     *
1821
     * Some conversions and safety checks are carried out. Lobs are supported.
1822
     * If the return ID isn't required, then this just reports success as true/false.
1823
     * $data is an object containing needed data
1824
     * @param string $table The database table to be inserted into
1825
     * @param object|array $dataobject A data object with values for one or more fields in the record
1826
     * @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.
1827
     * @param bool $bulk Set to true is multiple inserts are expected
1828
     * @return bool|int true or new id
1829
     * @throws dml_exception A DML specific exception is thrown for any errors.
1830
     */
1831
    abstract public function insert_record($table, $dataobject, $returnid=true, $bulk=false);
1832
 
1833
    /**
1834
     * Insert multiple records into database as fast as possible.
1835
     *
1836
     * Order of inserts is maintained, but the operation is not atomic,
1837
     * use transactions if necessary.
1838
     *
1839
     * This method is intended for inserting of large number of small objects,
1840
     * do not use for huge objects with text or binary fields.
1841
     *
1842
     * @since Moodle 2.7
1843
     *
1844
     * @param string $table  The database table to be inserted into
1845
     * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
1846
     * @return void does not return new record ids
1847
     *
1848
     * @throws coding_exception if data objects have different structure
1849
     * @throws dml_exception A DML specific exception is thrown for any errors.
1850
     */
1851
    public function insert_records($table, $dataobjects) {
1852
        if (!is_array($dataobjects) and !($dataobjects instanceof Traversable)) {
1853
            throw new coding_exception('insert_records() passed non-traversable object');
1854
        }
1855
 
1856
        $fields = null;
1857
        // Note: override in driver if there is a faster way.
1858
        foreach ($dataobjects as $dataobject) {
1859
            if (!is_array($dataobject) and !is_object($dataobject)) {
1860
                throw new coding_exception('insert_records() passed invalid record object');
1861
            }
1862
            $dataobject = (array)$dataobject;
1863
            if ($fields === null) {
1864
                $fields = array_keys($dataobject);
1865
            } else if ($fields !== array_keys($dataobject)) {
1866
                throw new coding_exception('All dataobjects in insert_records() must have the same structure!');
1867
            }
1868
            $this->insert_record($table, $dataobject, false);
1869
        }
1870
    }
1871
 
1872
    /**
1873
     * Import a record into a table, id field is required.
1874
     * Safety checks are NOT carried out. Lobs are supported.
1875
     *
1876
     * @param string $table name of database table to be inserted into
1877
     * @param object $dataobject A data object with values for one or more fields in the record
1878
     * @return bool true
1879
     * @throws dml_exception A DML specific exception is thrown for any errors.
1880
     */
1881
    abstract public function import_record($table, $dataobject);
1882
 
1883
    /**
1884
     * Update record in database, as fast as possible, no safety checks, lobs not supported.
1885
     * @param string $table name
1886
     * @param stdClass|array $params data record as object or array
1887
     * @param bool $bulk True means repeated updates expected.
1888
     * @return bool true
1889
     * @throws dml_exception A DML specific exception is thrown for any errors.
1890
     */
1891
    abstract public function update_record_raw($table, $params, $bulk=false);
1892
 
1893
    /**
1894
     * Update a record in a table
1895
     *
1896
     * $dataobject is an object containing needed data
1897
     * Relies on $dataobject having a variable "id" to
1898
     * specify the record to update
1899
     *
1900
     * @param string $table The database table to be checked against.
1901
     * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue.
1902
     *        Must have an entry for 'id' to map to the table specified.
1903
     * @param bool $bulk True means repeated updates expected.
1904
     * @return bool true
1905
     * @throws dml_exception A DML specific exception is thrown for any errors.
1906
     */
1907
    abstract public function update_record($table, $dataobject, $bulk=false);
1908
 
1909
    /**
1910
     * Set a single field in every table record where all the given conditions met.
1911
     *
1912
     * @param string $table The database table to be checked against.
1913
     * @param string $newfield the field to set.
1914
     * @param mixed $newvalue the value to set the field to.
1915
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1916
     * @return bool true
1917
     * @throws dml_exception A DML specific exception is thrown for any errors.
1918
     */
1919
    public function set_field($table, $newfield, $newvalue, array $conditions=null) {
1920
        list($select, $params) = $this->where_clause($table, $conditions);
1921
        return $this->set_field_select($table, $newfield, $newvalue, $select, $params);
1922
    }
1923
 
1924
    /**
1925
     * Set a single field in every table record which match a particular WHERE clause.
1926
     *
1927
     * @param string $table The database table to be checked against.
1928
     * @param string $newfield the field to set.
1929
     * @param mixed $newvalue the value to set the field to.
1930
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1931
     * @param array $params array of sql parameters
1932
     * @return bool true
1933
     * @throws dml_exception A DML specific exception is thrown for any errors.
1934
     */
1935
    abstract public function set_field_select($table, $newfield, $newvalue, $select, array $params=null);
1936
 
1937
 
1938
    /**
1939
     * Count the records in a table where all the given conditions met.
1940
     *
1941
     * @param string $table The table to query.
1942
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1943
     * @return int The count of records returned from the specified criteria.
1944
     * @throws dml_exception A DML specific exception is thrown for any errors.
1945
     */
1946
    public function count_records($table, array $conditions=null) {
1947
        list($select, $params) = $this->where_clause($table, $conditions);
1948
        return $this->count_records_select($table, $select, $params);
1949
    }
1950
 
1951
    /**
1952
     * Count the records in a table which match a particular WHERE clause.
1953
     *
1954
     * @param string $table The database table to be checked against.
1955
     * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
1956
     * @param array $params array of sql parameters
1957
     * @param string $countitem The count string to be used in the SQL call. Default is COUNT('x').
1958
     * @return int The count of records returned from the specified criteria.
1959
     * @throws dml_exception A DML specific exception is thrown for any errors.
1960
     */
1961
    public function count_records_select($table, $select, array $params=null, $countitem="COUNT('x')") {
1962
        if ($select) {
1963
            $select = "WHERE $select";
1964
        }
1965
        return $this->count_records_sql("SELECT $countitem FROM {" . $table . "} $select", $params);
1966
    }
1967
 
1968
    /**
1969
     * Get the result of a SQL SELECT COUNT(...) query.
1970
     *
1971
     * Given a query that counts rows, return that count. (In fact,
1972
     * given any query, return the first field of the first record
1973
     * returned. However, this method should only be used for the
1974
     * intended purpose.) If an error occurs, 0 is returned.
1975
     *
1976
     * @param string $sql The SQL string you wish to be executed.
1977
     * @param array $params array of sql parameters
1978
     * @return int the count
1979
     * @throws dml_exception A DML specific exception is thrown for any errors.
1980
     */
1981
    public function count_records_sql($sql, array $params=null) {
1982
        $count = $this->get_field_sql($sql, $params);
1983
        if ($count === false or !is_number($count) or $count < 0) {
1984
            throw new coding_exception("count_records_sql() expects the first field to contain non-negative number from COUNT(), '$count' found instead.");
1985
        }
1986
        return (int)$count;
1987
    }
1988
 
1989
    /**
1990
     * Test whether a record exists in a table where all the given conditions met.
1991
     *
1992
     * @param string $table The table to check.
1993
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1994
     * @return bool true if a matching record exists, else false.
1995
     * @throws dml_exception A DML specific exception is thrown for any errors.
1996
     */
1997
    public function record_exists($table, array $conditions) {
1998
        list($select, $params) = $this->where_clause($table, $conditions);
1999
        return $this->record_exists_select($table, $select, $params);
2000
    }
2001
 
2002
    /**
2003
     * Test whether any records exists in a table which match a particular WHERE clause.
2004
     *
2005
     * @param string $table The database table to be checked against.
2006
     * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
2007
     * @param array $params array of sql parameters
2008
     * @return bool true if a matching record exists, else false.
2009
     * @throws dml_exception A DML specific exception is thrown for any errors.
2010
     */
2011
    public function record_exists_select($table, $select, array $params=null) {
2012
        if ($select) {
2013
            $select = "WHERE $select";
2014
        }
2015
        return $this->record_exists_sql("SELECT 'x' FROM {" . $table . "} $select", $params);
2016
    }
2017
 
2018
    /**
2019
     * Test whether a SQL SELECT statement returns any records.
2020
     *
2021
     * This function returns true if the SQL statement executes
2022
     * without any errors and returns at least one record.
2023
     *
2024
     * @param string $sql The SQL statement to execute.
2025
     * @param array $params array of sql parameters
2026
     * @return bool true if the SQL executes without errors and returns at least one record.
2027
     * @throws dml_exception A DML specific exception is thrown for any errors.
2028
     */
2029
    public function record_exists_sql($sql, array $params=null) {
2030
        $mrs = $this->get_recordset_sql($sql, $params, 0, 1);
2031
        $return = $mrs->valid();
2032
        $mrs->close();
2033
        return $return;
2034
    }
2035
 
2036
    /**
2037
     * Delete the records from a table where all the given conditions met.
2038
     * If conditions not specified, table is truncated.
2039
     *
2040
     * @param string $table the table to delete from.
2041
     * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
2042
     * @return bool true.
2043
     * @throws dml_exception A DML specific exception is thrown for any errors.
2044
     */
2045
    public function delete_records($table, array $conditions=null) {
2046
        // truncate is drop/create (DDL), not transactional safe,
2047
        // so we don't use the shortcut within them. MDL-29198
2048
        if (is_null($conditions) && empty($this->transactions)) {
2049
            return $this->execute("TRUNCATE TABLE {".$table."}");
2050
        }
2051
        list($select, $params) = $this->where_clause($table, $conditions);
2052
        return $this->delete_records_select($table, $select, $params);
2053
    }
2054
 
2055
    /**
2056
     * Delete the records from a table where one field match one list of values.
2057
     *
2058
     * @param string $table the table to delete from.
2059
     * @param string $field The field to search
2060
     * @param array $values array of values
2061
     * @return bool true.
2062
     * @throws dml_exception A DML specific exception is thrown for any errors.
2063
     */
2064
    public function delete_records_list($table, $field, array $values) {
2065
        list($select, $params) = $this->where_clause_list($field, $values);
2066
        return $this->delete_records_select($table, $select, $params);
2067
    }
2068
 
2069
    /**
2070
     * Deletes records from a table using a subquery. The subquery should return a list of values
2071
     * in a single column, which match one field from the table being deleted.
2072
     *
2073
     * The $alias parameter must be set to the name of the single column in your subquery result
2074
     * (e.g. if the subquery is 'SELECT id FROM whatever', then it should be 'id'). This is not
2075
     * needed on most databases, but MySQL requires it.
2076
     *
2077
     * (On database where the subquery is inefficient, it is implemented differently.)
2078
     *
2079
     * @param string $table Table to delete from
2080
     * @param string $field Field in table to match
2081
     * @param string $alias Name of single column in subquery e.g. 'id'
2082
     * @param string $subquery Subquery that will return values of the field to delete
2083
     * @param array $params Parameters for subquery
2084
     * @throws dml_exception If there is any error
2085
     * @since Moodle 3.10
2086
     */
2087
    public function delete_records_subquery(string $table, string $field, string $alias,
2088
            string $subquery, array $params = []): void {
2089
        $this->delete_records_select($table, $field . ' IN (' . $subquery . ')', $params);
2090
    }
2091
 
2092
    /**
2093
     * Delete one or more records from a table which match a particular WHERE clause.
2094
     *
2095
     * @param string $table The database table to be checked against.
2096
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
2097
     * @param array $params array of sql parameters
2098
     * @return bool true.
2099
     * @throws dml_exception A DML specific exception is thrown for any errors.
2100
     */
2101
    abstract public function delete_records_select($table, $select, array $params=null);
2102
 
2103
    /**
2104
     * Returns the FROM clause required by some DBs in all SELECT statements.
2105
     *
2106
     * To be used in queries not having FROM clause to provide cross_db
2107
     * Most DBs don't need it, hence the default is ''
2108
     * @return string
2109
     */
2110
    public function sql_null_from_clause() {
2111
        return '';
2112
    }
2113
 
2114
    /**
2115
     * Returns the SQL text to be used in order to perform one bitwise AND operation
2116
     * between 2 integers.
2117
     *
2118
     * NOTE: The SQL result is a number and can not be used directly in
2119
     *       SQL condition, please compare it to some number to get a bool!!
2120
     *
2121
     * @param string $int1 SQL for the first integer in the operation.
2122
     * @param string $int2 SQL for the second integer in the operation.
2123
     * @return string The piece of SQL code to be used in your statement.
2124
     */
2125
    public function sql_bitand($int1, $int2) {
2126
        return '((' . $int1 . ') & (' . $int2 . '))';
2127
    }
2128
 
2129
    /**
2130
     * Returns the SQL text to be used in order to perform one bitwise NOT operation
2131
     * with 1 integer.
2132
     *
2133
     * @param int $int1 The operand integer in the operation.
2134
     * @return string The piece of SQL code to be used in your statement.
2135
     */
2136
    public function sql_bitnot($int1) {
2137
        return '(~(' . $int1 . '))';
2138
    }
2139
 
2140
    /**
2141
     * Returns the SQL text to be used in order to perform one bitwise OR operation
2142
     * between 2 integers.
2143
     *
2144
     * NOTE: The SQL result is a number and can not be used directly in
2145
     *       SQL condition, please compare it to some number to get a bool!!
2146
     *
2147
     * @param int $int1 The first operand integer in the operation.
2148
     * @param int $int2 The second operand integer in the operation.
2149
     * @return string The piece of SQL code to be used in your statement.
2150
     */
2151
    public function sql_bitor($int1, $int2) {
2152
        return '((' . $int1 . ') | (' . $int2 . '))';
2153
    }
2154
 
2155
    /**
2156
     * Returns the SQL text to be used in order to perform one bitwise XOR operation
2157
     * between 2 integers.
2158
     *
2159
     * NOTE: The SQL result is a number and can not be used directly in
2160
     *       SQL condition, please compare it to some number to get a bool!!
2161
     *
2162
     * @param int $int1 The first operand integer in the operation.
2163
     * @param int $int2 The second operand integer in the operation.
2164
     * @return string The piece of SQL code to be used in your statement.
2165
     */
2166
    public function sql_bitxor($int1, $int2) {
2167
        return '((' . $int1 . ') ^ (' . $int2 . '))';
2168
    }
2169
 
2170
    /**
2171
     * Returns the SQL text to be used in order to perform module '%'
2172
     * operation - remainder after division
2173
     *
2174
     * @param int $int1 The first operand integer in the operation.
2175
     * @param int $int2 The second operand integer in the operation.
2176
     * @return string The piece of SQL code to be used in your statement.
2177
     */
2178
    public function sql_modulo($int1, $int2) {
2179
        return '((' . $int1 . ') % (' . $int2 . '))';
2180
    }
2181
 
2182
    /**
2183
     * Returns the cross db correct CEIL (ceiling) expression applied to fieldname.
2184
     * note: Most DBs use CEIL(), hence it's the default here.
2185
     *
2186
     * @param string $fieldname The field (or expression) we are going to ceil.
2187
     * @return string The piece of SQL code to be used in your ceiling statement.
2188
     */
2189
    public function sql_ceil($fieldname) {
2190
        return ' CEIL(' . $fieldname . ')';
2191
    }
2192
 
2193
    /**
2194
     * Return SQL for casting to char of given field/expression. Default implementation performs implicit cast using
2195
     * concatenation with an empty string
2196
     *
2197
     * @param string $field Table field or SQL expression to be cast
2198
     * @return string
2199
     */
2200
    public function sql_cast_to_char(string $field): string {
2201
        return $this->sql_concat("''", $field);
2202
    }
2203
 
2204
    /**
2205
     * Returns the SQL to be used in order to CAST one CHAR column to INTEGER.
2206
     *
2207
     * Be aware that the CHAR column you're trying to cast contains really
2208
     * int values or the RDBMS will throw an error!
2209
     *
2210
     * @param string $fieldname The name of the field to be casted.
2211
     * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false.
2212
     * @return string The piece of SQL code to be used in your statement.
2213
     */
2214
    public function sql_cast_char2int($fieldname, $text=false) {
2215
        return ' ' . $fieldname . ' ';
2216
    }
2217
 
2218
    /**
2219
     * Returns the SQL to be used in order to CAST one CHAR column to REAL number.
2220
     *
2221
     * Be aware that the CHAR column you're trying to cast contains really
2222
     * numbers or the RDBMS will throw an error!
2223
     *
2224
     * @param string $fieldname The name of the field to be casted.
2225
     * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false.
2226
     * @return string The piece of SQL code to be used in your statement.
2227
     */
2228
    public function sql_cast_char2real($fieldname, $text=false) {
2229
        return ' ' . $fieldname . ' ';
2230
    }
2231
 
2232
    /**
2233
     * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
2234
     *
2235
     * (Only MySQL needs this. MySQL things that 1 * -1 = 18446744073709551615
2236
     * if the 1 comes from an unsigned column).
2237
     *
2238
     * @deprecated since 2.3
2239
     * @param string $fieldname The name of the field to be cast
2240
     * @return string The piece of SQL code to be used in your statement.
2241
     */
2242
    public function sql_cast_2signed($fieldname) {
2243
        return ' ' . $fieldname . ' ';
2244
    }
2245
 
2246
    /**
2247
     * Returns the SQL text to be used to compare one TEXT (clob) column with
2248
     * one varchar column, because some RDBMS doesn't support such direct
2249
     * comparisons.
2250
     *
2251
     * @param string $fieldname The name of the TEXT field we need to order by
2252
     * @param int $numchars Number of chars to use for the ordering (defaults to 32).
2253
     * @return string The piece of SQL code to be used in your statement.
2254
     */
2255
    public function sql_compare_text($fieldname, $numchars=32) {
2256
        return $this->sql_order_by_text($fieldname, $numchars);
2257
    }
2258
 
2259
    /**
2260
     * Returns an equal (=) or not equal (<>) part of a query.
2261
     *
2262
     * Note the use of this method may lead to slower queries (full scans) so
2263
     * use it only when needed and against already reduced data sets.
2264
     *
2265
     * @since Moodle 3.2
2266
     *
2267
     * @param string $fieldname Usually the name of the table column.
2268
     * @param string $param Usually the bound query parameter (?, :named).
2269
     * @param bool $casesensitive Use case sensitive search when set to true (default).
2270
     * @param bool $accentsensitive Use accent sensitive search when set to true (default). (not all databases support accent insensitive)
2271
     * @param bool $notequal True means not equal (<>)
2272
     * @return string The SQL code fragment.
2273
     */
2274
    public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
2275
        // Note that, by default, it's assumed that the correct sql equal operations are
2276
        // case sensitive. Only databases not observing this behavior must override the method.
2277
        // Also, accent sensitiveness only will be handled by databases supporting it.
2278
        $equalop = $notequal ? '<>' : '=';
2279
        if ($casesensitive) {
2280
            return "$fieldname $equalop $param";
2281
        } else {
2282
            return "LOWER($fieldname) $equalop LOWER($param)";
2283
        }
2284
    }
2285
 
2286
    /**
2287
     * Returns 'LIKE' part of a query.
2288
     *
2289
     * @param string $fieldname Usually the name of the table column.
2290
     * @param string $param Usually the bound query parameter (?, :named).
2291
     * @param bool $casesensitive Use case sensitive search when set to true (default).
2292
     * @param bool $accentsensitive Use accent sensitive search when set to true (default). (not all databases support accent insensitive)
2293
     * @param bool $notlike True means "NOT LIKE".
2294
     * @param string $escapechar The escape char for '%' and '_'.
2295
     * @return string The SQL code fragment.
2296
     */
2297
    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
2298
        if (strpos($param, '%') !== false) {
2299
            debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
2300
        }
2301
        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
2302
        // by default ignore any sensitiveness - each database does it in a different way
2303
        return "$fieldname $LIKE $param ESCAPE '$escapechar'";
2304
    }
2305
 
2306
    /**
2307
     * Escape sql LIKE special characters like '_' or '%'.
2308
     * @param string $text The string containing characters needing escaping.
2309
     * @param string $escapechar The desired escape character, defaults to '\\'.
2310
     * @return string The escaped sql LIKE string.
2311
     */
2312
    public function sql_like_escape($text, $escapechar = '\\') {
2313
        $text = str_replace('_', $escapechar.'_', $text);
2314
        $text = str_replace('%', $escapechar.'%', $text);
2315
        return $text;
2316
    }
2317
 
2318
    /**
2319
     * Returns the proper SQL to do CONCAT between the elements(fieldnames) passed.
2320
     *
2321
     * This function accepts variable number of string parameters.
2322
     * All strings/fieldnames will used in the SQL concatenate statement generated.
2323
     *
2324
     * @param string $arr,... expressions to be concatenated.
2325
     * @return string The SQL to concatenate strings passed in.
2326
     */
2327
    abstract public function sql_concat(...$arr);
2328
 
2329
    /**
2330
     * Returns the proper SQL to do CONCAT between the elements passed
2331
     * with a given separator
2332
     *
2333
     * @param string $separator The separator desired for the SQL concatenating $elements.
2334
     * @param array  $elements The array of strings to be concatenated.
2335
     * @return string The SQL to concatenate the strings.
2336
     */
2337
    abstract public function sql_concat_join($separator="' '", $elements=array());
2338
 
2339
    /**
2340
     * Return SQL for performing group concatenation on given field/expression
2341
     *
2342
     * @param string $field Table field or SQL expression to be concatenated
2343
     * @param string $separator The separator desired between each concatetated field
2344
     * @param string $sort Ordering of the concatenated field
2345
     * @return string
2346
     */
2347
    abstract public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string;
2348
 
2349
    /**
2350
     * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname
2351
     *
2352
     * @todo MDL-31233 This may not be needed here.
2353
     *
2354
     * @param string $first User's first name (default:'firstname').
2355
     * @param string $last User's last name (default:'lastname').
2356
     * @return string The SQL to concatenate strings.
2357
     */
2358
    function sql_fullname($first='firstname', $last='lastname') {
2359
        return $this->sql_concat($first, "' '", $last);
2360
    }
2361
 
2362
    /**
2363
     * Returns the SQL text to be used to order by one TEXT (clob) column, because
2364
     * some RDBMS doesn't support direct ordering of such fields.
2365
     *
2366
     * Note that the use or queries being ordered by TEXT columns must be minimised,
2367
     * because it's really slooooooow.
2368
     *
2369
     * @param string $fieldname The name of the TEXT field we need to order by.
2370
     * @param int $numchars The number of chars to use for the ordering (defaults to 32).
2371
     * @return string The piece of SQL code to be used in your statement.
2372
     */
2373
    public function sql_order_by_text($fieldname, $numchars=32) {
2374
        return $fieldname;
2375
    }
2376
 
2377
    /**
2378
     * Returns the SQL text to be used to order by columns, standardising the return
2379
     * pattern of null values across database types to sort nulls first when ascending
2380
     * and last when descending.
2381
     *
2382
     * @param string $fieldname The name of the field we need to sort by.
2383
     * @param int $sort An order to sort the results in.
2384
     * @return string The piece of SQL code to be used in your statement.
2385
     */
2386
    public function sql_order_by_null(string $fieldname, int $sort = SORT_ASC): string {
2387
        return $fieldname . ' ' . ($sort == SORT_ASC ? 'ASC' : 'DESC');
2388
    }
2389
 
2390
    /**
2391
     * Returns the SQL text to be used to calculate the length in characters of one expression.
2392
     * @param string $fieldname The fieldname/expression to calculate its length in characters.
2393
     * @return string the piece of SQL code to be used in the statement.
2394
     */
2395
    public function sql_length($fieldname) {
2396
        return ' LENGTH(' . $fieldname . ')';
2397
    }
2398
 
2399
    /**
2400
     * Returns the proper substr() SQL text used to extract substrings from DB
2401
     * NOTE: this was originally returning only function name
2402
     *
2403
     * @param string $expr Some string field, no aggregates.
2404
     * @param mixed $start Integer or expression evaluating to integer (1 based value; first char has index 1)
2405
     * @param mixed $length Optional integer or expression evaluating to integer.
2406
     * @return string The sql substring extraction fragment.
2407
     */
2408
    public function sql_substr($expr, $start, $length=false) {
2409
        if (count(func_get_args()) < 2) {
2410
            throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
2411
        }
2412
        if ($length === false) {
2413
            return "SUBSTR($expr, $start)";
2414
        } else {
2415
            return "SUBSTR($expr, $start, $length)";
2416
        }
2417
    }
2418
 
2419
    /**
2420
     * Returns the SQL for returning searching one string for the location of another.
2421
     *
2422
     * Note, there is no guarantee which order $needle, $haystack will be in
2423
     * the resulting SQL so when using this method, and both arguments contain
2424
     * placeholders, you should use named placeholders.
2425
     *
2426
     * @param string $needle the SQL expression that will be searched for.
2427
     * @param string $haystack the SQL expression that will be searched in.
2428
     * @return string The required searching SQL part.
2429
     */
2430
    public function sql_position($needle, $haystack) {
2431
        // Implementation using standard SQL.
2432
        return "POSITION(($needle) IN ($haystack))";
2433
    }
2434
 
2435
    /**
2436
     * This used to return empty string replacement character.
2437
     *
2438
     * @deprecated use bound parameter with empty string instead
2439
     *
2440
     * @return string An empty string.
2441
     */
2442
    function sql_empty() {
2443
        debugging("sql_empty() is deprecated, please use empty string '' as sql parameter value instead", DEBUG_DEVELOPER);
2444
        return '';
2445
    }
2446
 
2447
    /**
2448
     * Returns the proper SQL to know if one field is empty.
2449
     *
2450
     * Note that the function behavior strongly relies on the
2451
     * parameters passed describing the field so, please,  be accurate
2452
     * when specifying them.
2453
     *
2454
     * Also, note that this function is not suitable to look for
2455
     * fields having NULL contents at all. It's all for empty values!
2456
     *
2457
     * This function should be applied in all the places where conditions of
2458
     * the type:
2459
     *
2460
     *     ... AND fieldname = '';
2461
     *
2462
     * are being used. Final result for text fields should be:
2463
     *
2464
     *     ... AND ' . sql_isempty('tablename', 'fieldname', true/false, true);
2465
     *
2466
     * and for varchar fields result should be:
2467
     *
2468
     *    ... AND fieldname = :empty; "; $params['empty'] = '';
2469
     *
2470
     * (see parameters description below)
2471
     *
2472
     * @param string $tablename Name of the table (without prefix). Not used for now but can be
2473
     *                          necessary in the future if we want to use some introspection using
2474
     *                          meta information against the DB. /// TODO ///
2475
     * @param string $fieldname Name of the field we are going to check
2476
     * @param bool $nullablefield For specifying if the field is nullable (true) or no (false) in the DB.
2477
     * @param bool $textfield For specifying if it is a text (also called clob) field (true) or a varchar one (false)
2478
     * @return string the sql code to be added to check for empty values
2479
     */
2480
    public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
2481
        return " ($fieldname = '') ";
2482
    }
2483
 
2484
    /**
2485
     * Returns the proper SQL to know if one field is not empty.
2486
     *
2487
     * Note that the function behavior strongly relies on the
2488
     * parameters passed describing the field so, please,  be accurate
2489
     * when specifying them.
2490
     *
2491
     * This function should be applied in all the places where conditions of
2492
     * the type:
2493
     *
2494
     *     ... AND fieldname != '';
2495
     *
2496
     * are being used. Final result for text fields should be:
2497
     *
2498
     *     ... AND ' . sql_isnotempty('tablename', 'fieldname', true/false, true/false);
2499
     *
2500
     * and for varchar fields result should be:
2501
     *
2502
     *    ... AND fieldname != :empty; "; $params['empty'] = '';
2503
     *
2504
     * (see parameters description below)
2505
     *
2506
     * @param string $tablename Name of the table (without prefix). This is not used for now but can be
2507
     *                          necessary in the future if we want to use some introspection using
2508
     *                          meta information against the DB.
2509
     * @param string $fieldname The name of the field we are going to check.
2510
     * @param bool $nullablefield Specifies if the field is nullable (true) or not (false) in the DB.
2511
     * @param bool $textfield Specifies if it is a text (also called clob) field (true) or a varchar one (false).
2512
     * @return string The sql code to be added to check for non empty values.
2513
     */
2514
    public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield) {
2515
        return ' ( NOT ' . $this->sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') ';
2516
    }
2517
 
2518
    /**
2519
     * Returns true if this database driver supports regex syntax when searching.
2520
     * @return bool True if supported.
2521
     */
2522
    public function sql_regex_supported() {
2523
        return false;
2524
    }
2525
 
2526
    /**
2527
     * Returns the driver specific syntax (SQL part) for matching regex positively or negatively (inverted matching).
2528
     * Eg: 'REGEXP':'NOT REGEXP' or '~*' : '!~*'
2529
     *
2530
     * @param bool $positivematch
2531
     * @param bool $casesensitive
2532
     * @return string or empty if not supported
2533
     */
2534
    public function sql_regex($positivematch = true, $casesensitive = false) {
2535
        return '';
2536
    }
2537
 
2538
    /**
2539
     * Returns the word-beginning boundary marker if this database driver supports regex syntax when searching.
2540
     * @return string The word-beginning boundary marker. Otherwise, an empty string.
2541
     */
2542
    public function sql_regex_get_word_beginning_boundary_marker() {
2543
        if ($this->sql_regex_supported()) {
2544
            return '[[:<:]]';
2545
        }
2546
 
2547
        return '';
2548
    }
2549
 
2550
    /**
2551
     * Returns the word-end boundary marker if this database driver supports regex syntax when searching.
2552
     * @return string The word-end boundary marker. Otherwise, an empty string.
2553
     */
2554
    public function sql_regex_get_word_end_boundary_marker() {
2555
        if ($this->sql_regex_supported()) {
2556
            return '[[:>:]]';
2557
        }
2558
 
2559
        return '';
2560
    }
2561
 
2562
    /**
2563
     * Returns the SQL that allows to find intersection of two or more queries
2564
     *
2565
     * @since Moodle 2.8
2566
     *
2567
     * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields
2568
     * @param string $fields comma-separated list of fields (used only by some DB engines)
2569
     * @return string SQL query that will return only values that are present in each of selects
2570
     */
2571
    public function sql_intersect($selects, $fields) {
2572
        if (!count($selects)) {
2573
            throw new coding_exception('sql_intersect() requires at least one element in $selects');
2574
        } else if (count($selects) == 1) {
2575
            return $selects[0];
2576
        }
2577
        static $aliascnt = 0;
2578
        $rv = '('.$selects[0].')';
2579
        for ($i = 1; $i < count($selects); $i++) {
2580
            $rv .= " INTERSECT (".$selects[$i].')';
2581
        }
2582
        return $rv;
2583
    }
2584
 
2585
    /**
2586
     * Does this driver support tool_replace?
2587
     *
2588
     * @since Moodle 2.6.1
2589
     * @return bool
2590
     */
2591
    public function replace_all_text_supported() {
2592
        return false;
2593
    }
2594
 
2595
    /**
2596
     * Replace given text in all rows of column.
2597
     *
2598
     * @since Moodle 2.6.1
2599
     * @param string $table name of the table
2600
     * @param database_column_info $column
2601
     * @param string $search
2602
     * @param string $replace
2603
     */
2604
    public function replace_all_text($table, database_column_info $column, $search, $replace) {
2605
        if (!$this->replace_all_text_supported()) {
2606
            return;
2607
        }
2608
 
2609
        // NOTE: override this methods if following standard compliant SQL
2610
        //       does not work for your driver.
2611
 
2612
        // Enclose the column name by the proper quotes if it's a reserved word.
2613
        $columnname = $this->get_manager()->generator->getEncQuoted($column->name);
2614
 
2615
        $searchsql = $this->sql_like($columnname, '?');
2616
        $searchparam = '%'.$this->sql_like_escape($search).'%';
2617
 
2618
        $sql = "UPDATE {".$table."}
2619
                       SET $columnname = REPLACE($columnname, ?, ?)
2620
                     WHERE $searchsql";
2621
 
2622
        if ($column->meta_type === 'X') {
2623
            $this->execute($sql, array($search, $replace, $searchparam));
2624
 
2625
        } else if ($column->meta_type === 'C') {
2626
            if (core_text::strlen($search) < core_text::strlen($replace)) {
2627
                $colsize = $column->max_length;
2628
                $sql = "UPDATE {".$table."}
2629
                       SET $columnname = " . $this->sql_substr("REPLACE(" . $columnname . ", ?, ?)", 1, $colsize) . "
2630
                     WHERE $searchsql";
2631
            }
2632
            $this->execute($sql, array($search, $replace, $searchparam));
2633
        }
2634
    }
2635
 
2636
    /**
2637
     * Analyze the data in temporary tables to force statistics collection after bulk data loads.
2638
     *
2639
     * @return void
2640
     */
2641
    public function update_temp_table_stats() {
2642
        $this->temptables->update_stats();
2643
    }
2644
 
2645
    /**
2646
     * Checks and returns true if transactions are supported.
2647
     *
2648
     * It is not responsible to run productions servers
2649
     * on databases without transaction support ;-)
2650
     *
2651
     * Override in driver if needed.
2652
     *
2653
     * @return bool
2654
     */
2655
    protected function transactions_supported() {
2656
        // protected for now, this might be changed to public if really necessary
2657
        return true;
2658
    }
2659
 
2660
    /**
2661
     * Returns true if a transaction is in progress.
2662
     * @return bool
2663
     */
2664
    public function is_transaction_started() {
2665
        return !empty($this->transactions);
2666
    }
2667
 
2668
    /**
2669
     * This is a test that throws an exception if transaction in progress.
2670
     * This test does not force rollback of active transactions.
2671
     * @return void
2672
     * @throws dml_transaction_exception if stansaction active
2673
     */
2674
    public function transactions_forbidden() {
2675
        if ($this->is_transaction_started()) {
2676
            throw new dml_transaction_exception('This code can not be excecuted in transaction');
2677
        }
2678
    }
2679
 
2680
    /**
2681
     * On DBs that support it, switch to transaction mode and begin a transaction
2682
     * you'll need to ensure you call allow_commit() on the returned object
2683
     * or your changes *will* be lost.
2684
     *
2685
     * this is _very_ useful for massive updates
2686
     *
2687
     * Delegated database transactions can be nested, but only one actual database
2688
     * transaction is used for the outer-most delegated transaction. This method
2689
     * returns a transaction object which you should keep until the end of the
2690
     * delegated transaction. The actual database transaction will
2691
     * only be committed if all the nested delegated transactions commit
2692
     * successfully. If any part of the transaction rolls back then the whole
2693
     * thing is rolled back.
2694
     *
2695
     * @return moodle_transaction
2696
     */
2697
    public function start_delegated_transaction() {
2698
        $transaction = new moodle_transaction($this);
2699
        $this->transactions[] = $transaction;
2700
        if (count($this->transactions) == 1) {
2701
            $this->begin_transaction();
2702
        }
2703
        return $transaction;
2704
    }
2705
 
2706
    /**
2707
     * Driver specific start of real database transaction,
2708
     * this can not be used directly in code.
2709
     * @return void
2710
     */
2711
    abstract protected function begin_transaction();
2712
 
2713
    /**
2714
     * Indicates delegated transaction finished successfully.
2715
     * The real database transaction is committed only if
2716
     * all delegated transactions committed.
2717
     * @param moodle_transaction $transaction The transaction to commit
2718
     * @return void
2719
     * @throws dml_transaction_exception Creates and throws transaction related exceptions.
2720
     */
2721
    public function commit_delegated_transaction(moodle_transaction $transaction) {
2722
        if ($transaction->is_disposed()) {
2723
            throw new dml_transaction_exception('Transactions already disposed', $transaction);
2724
        }
2725
        // mark as disposed so that it can not be used again
2726
        $transaction->dispose();
2727
 
2728
        if (empty($this->transactions)) {
2729
            throw new dml_transaction_exception('Transaction not started', $transaction);
2730
        }
2731
 
2732
        if ($this->force_rollback) {
2733
            throw new dml_transaction_exception('Tried to commit transaction after lower level rollback', $transaction);
2734
        }
2735
 
2736
        if ($transaction !== $this->transactions[count($this->transactions) - 1]) {
2737
            // one incorrect commit at any level rollbacks everything
2738
            $this->force_rollback = true;
2739
            throw new dml_transaction_exception('Invalid transaction commit attempt', $transaction);
2740
        }
2741
 
2742
        if (count($this->transactions) == 1) {
2743
            // only commit the top most level
2744
            $this->commit_transaction();
2745
        }
2746
        array_pop($this->transactions);
2747
 
2748
        if (empty($this->transactions)) {
2749
            \core\event\manager::database_transaction_commited();
2750
            \core\message\manager::database_transaction_commited();
2751
        }
2752
    }
2753
 
2754
    /**
2755
     * Driver specific commit of real database transaction,
2756
     * this can not be used directly in code.
2757
     * @return void
2758
     */
2759
    abstract protected function commit_transaction();
2760
 
2761
    /**
2762
     * Call when delegated transaction failed, this rolls back
2763
     * all delegated transactions up to the top most level.
2764
     *
2765
     * In many cases you do not need to call this method manually,
2766
     * because all open delegated transactions are rolled back
2767
     * automatically if exceptions not caught.
2768
     *
2769
     * @param moodle_transaction $transaction An instance of a moodle_transaction.
2770
     * @param Exception|Throwable $e The related exception/throwable to this transaction rollback.
2771
     * @return void This does not return, instead the exception passed in will be rethrown.
2772
     */
2773
    public function rollback_delegated_transaction(moodle_transaction $transaction, $e) {
2774
        if (!($e instanceof Exception) && !($e instanceof Throwable)) {
2775
            // PHP7 - we catch Throwables in phpunit but can't use that as the type hint in PHP5.
2776
            $e = new \coding_exception("Must be given an Exception or Throwable object!");
2777
        }
2778
        if ($transaction->is_disposed()) {
2779
            throw new dml_transaction_exception('Transactions already disposed', $transaction);
2780
        }
2781
        // mark as disposed so that it can not be used again
2782
        $transaction->dispose();
2783
 
2784
        // one rollback at any level rollbacks everything
2785
        $this->force_rollback = true;
2786
 
2787
        if (empty($this->transactions) or $transaction !== $this->transactions[count($this->transactions) - 1]) {
2788
            // this may or may not be a coding problem, better just rethrow the exception,
2789
            // because we do not want to loose the original $e
2790
            throw $e;
2791
        }
2792
 
2793
        if (count($this->transactions) == 1) {
2794
            // only rollback the top most level
2795
            $this->rollback_transaction();
2796
        }
2797
        array_pop($this->transactions);
2798
        if (empty($this->transactions)) {
2799
            // finally top most level rolled back
2800
            $this->force_rollback = false;
2801
            \core\event\manager::database_transaction_rolledback();
2802
            \core\message\manager::database_transaction_rolledback();
2803
        }
2804
        throw $e;
2805
    }
2806
 
2807
    /**
2808
     * Driver specific abort of real database transaction,
2809
     * this can not be used directly in code.
2810
     * @return void
2811
     */
2812
    abstract protected function rollback_transaction();
2813
 
2814
    /**
2815
     * Force rollback of all delegated transaction.
2816
     * Does not throw any exceptions and does not log anything.
2817
     *
2818
     * This method should be used only from default exception handlers and other
2819
     * core code.
2820
     *
2821
     * @return void
2822
     */
2823
    public function force_transaction_rollback() {
2824
        if ($this->transactions) {
2825
            try {
2826
                $this->rollback_transaction();
2827
            } catch (dml_exception $e) {
2828
                // ignore any sql errors here, the connection might be broken
2829
            }
2830
        }
2831
 
2832
        // now enable transactions again
2833
        $this->transactions = array();
2834
        $this->force_rollback = false;
2835
 
2836
        \core\event\manager::database_transaction_rolledback();
2837
        \core\message\manager::database_transaction_rolledback();
2838
    }
2839
 
2840
    /**
2841
     * Is session lock supported in this driver?
2842
     * @return bool
2843
     */
2844
    public function session_lock_supported() {
2845
        return false;
2846
    }
2847
 
2848
    /**
2849
     * Obtains the session lock.
2850
     * @param int $rowid The id of the row with session record.
2851
     * @param int $timeout The maximum allowed time to wait for the lock in seconds.
2852
     * @return void
2853
     * @throws dml_exception A DML specific exception is thrown for any errors.
2854
     */
2855
    public function get_session_lock($rowid, $timeout) {
2856
        $this->used_for_db_sessions = true;
2857
    }
2858
 
2859
    /**
2860
     * Releases the session lock.
2861
     * @param int $rowid The id of the row with session record.
2862
     * @return void
2863
     * @throws dml_exception A DML specific exception is thrown for any errors.
2864
     */
2865
    public function release_session_lock($rowid) {
2866
    }
2867
 
2868
    /**
2869
     * Returns the number of reads done by this database.
2870
     * @return int Number of reads.
2871
     */
2872
    public function perf_get_reads() {
2873
        return $this->reads;
2874
    }
2875
 
2876
    /**
2877
     * Returns whether we want to connect to slave database for read queries.
2878
     * @return bool Want read only connection
2879
     */
2880
    public function want_read_slave(): bool {
2881
        return false;
2882
    }
2883
 
2884
    /**
2885
     * Returns the number of reads before first write done by this database.
2886
     * @return int Number of reads.
2887
     */
2888
    public function perf_get_reads_slave(): int {
2889
        return 0;
2890
    }
2891
 
2892
    /**
2893
     * Returns the number of writes done by this database.
2894
     * @return int Number of writes.
2895
     */
2896
    public function perf_get_writes() {
2897
        return $this->writes;
2898
    }
2899
 
2900
    /**
2901
     * Returns the number of queries done by this database.
2902
     * @return int Number of queries.
2903
     */
2904
    public function perf_get_queries() {
2905
        return $this->writes + $this->reads;
2906
    }
2907
 
2908
    /**
2909
     * Time waiting for the database engine to finish running all queries.
2910
     * @return float Number of seconds with microseconds
2911
     */
2912
    public function perf_get_queries_time() {
2913
        return $this->queriestime;
2914
    }
2915
 
2916
    /**
2917
     * Whether the database is able to support full-text search or not.
2918
     *
2919
     * @return bool
2920
     */
2921
    public function is_fulltext_search_supported() {
2922
        // No support unless specified.
2923
        return false;
2924
    }
2925
}