Proyectos de Subversion Moodle

Rev

| Ultima modificación | Ver Log |

Rev Autor Línea Nro. Línea
1 efrain 1
<?php
2
// This file is part of Moodle - http://moodle.org/
3
//
4
// Moodle is free software: you can redistribute it and/or modify
5
// it under the terms of the GNU General Public License as published by
6
// the Free Software Foundation, either version 3 of the License, or
7
// (at your option) any later version.
8
//
9
// Moodle is distributed in the hope that it will be useful,
10
// but WITHOUT ANY WARRANTY; without even the implied warranty of
11
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
12
// GNU General Public License for more details.
13
//
14
// You should have received a copy of the GNU General Public License
15
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
16
 
17
/**
18
 * Native mysqli class representing moodle database interface.
19
 *
20
 * @package    core_dml
21
 * @copyright  2008 Petr Skoda (http://skodak.org)
22
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23
 */
24
 
25
defined('MOODLE_INTERNAL') || die();
26
 
27
require_once(__DIR__.'/moodle_database.php');
28
require_once(__DIR__.'/moodle_read_slave_trait.php');
29
require_once(__DIR__.'/mysqli_native_moodle_recordset.php');
30
require_once(__DIR__.'/mysqli_native_moodle_temptables.php');
31
 
32
/**
33
 * Native mysqli class representing moodle database interface.
34
 *
35
 * @package    core_dml
36
 * @copyright  2008 Petr Skoda (http://skodak.org)
37
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
38
 */
39
class mysqli_native_moodle_database extends moodle_database {
40
    use moodle_read_slave_trait {
41
        can_use_readonly as read_slave_can_use_readonly;
42
    }
43
 
44
    /** @var array $sslmodes */
45
    private static $sslmodes = [
46
        'require',
47
        'verify-full'
48
    ];
49
 
50
    /** @var mysqli $mysqli */
51
    protected $mysqli = null;
52
    /** @var bool is compressed row format supported cache */
53
    protected $compressedrowformatsupported = null;
54
    /** @var string DB server actual version */
55
    protected $serverversion = null;
56
 
57
    private $transactions_supported = null;
58
 
59
    /**
60
     * Attempt to create the database
61
     * @param string $dbhost
62
     * @param string $dbuser
63
     * @param string $dbpass
64
     * @param string $dbname
65
     * @return bool success
66
     * @throws dml_exception A DML specific exception is thrown for any errors.
67
     */
68
    public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
69
        $driverstatus = $this->driver_installed();
70
 
71
        if ($driverstatus !== true) {
72
            throw new dml_exception('dbdriverproblem', $driverstatus);
73
        }
74
 
75
        if (!empty($dboptions['dbsocket'])
76
                and (strpos($dboptions['dbsocket'], '/') !== false or strpos($dboptions['dbsocket'], '\\') !== false)) {
77
            $dbsocket = $dboptions['dbsocket'];
78
        } else {
79
            $dbsocket = ini_get('mysqli.default_socket');
80
        }
81
        if (empty($dboptions['dbport'])) {
82
            $dbport = (int)ini_get('mysqli.default_port');
83
        } else {
84
            $dbport = (int)$dboptions['dbport'];
85
        }
86
        // verify ini.get does not return nonsense
87
        if (empty($dbport)) {
88
            $dbport = 3306;
89
        }
90
        ob_start();
91
        $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db
92
        $dberr = ob_get_contents();
93
        ob_end_clean();
94
        $errorno = @$conn->connect_errno;
95
 
96
        if ($errorno !== 0) {
97
            throw new dml_connection_exception($dberr);
98
        }
99
 
100
        // Normally a check would be done before setting utf8mb4, but the database can be created
101
        // before the enviroment checks are done. We'll proceed with creating the database and then do checks next.
102
        $charset = 'utf8mb4';
103
        if (isset($dboptions['dbcollation']) and (strpos($dboptions['dbcollation'], 'utf8_') === 0
104
                || strpos($dboptions['dbcollation'], 'utf8mb4_') === 0)) {
105
            $collation = $dboptions['dbcollation'];
106
            $collationinfo = explode('_', $dboptions['dbcollation']);
107
            $charset = reset($collationinfo);
108
        } else {
109
            $collation = 'utf8mb4_unicode_ci';
110
        }
111
 
112
        $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE ".$collation);
113
 
114
        $conn->close();
115
 
116
        if (!$result) {
117
            throw new dml_exception('cannotcreatedb');
118
        }
119
 
120
        return true;
121
    }
122
 
123
    /**
124
     * Detects if all needed PHP stuff installed.
125
     * Note: can be used before connect()
126
     * @return mixed true if ok, string if something
127
     */
128
    public function driver_installed() {
129
        if (!extension_loaded('mysqli')) {
130
            return get_string('mysqliextensionisnotpresentinphp', 'install');
131
        }
132
        return true;
133
    }
134
 
135
    /**
136
     * Returns database family type - describes SQL dialect
137
     * Note: can be used before connect()
138
     * @return string db family name (mysql, postgres, mssql, oracle, etc.)
139
     */
140
    public function get_dbfamily() {
141
        return 'mysql';
142
    }
143
 
144
    /**
145
     * Returns more specific database driver type
146
     * Note: can be used before connect()
147
     * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
148
     */
149
    protected function get_dbtype() {
150
        return 'mysqli';
151
    }
152
 
153
    /**
154
     * Returns general database library name
155
     * Note: can be used before connect()
156
     * @return string db type pdo, native
157
     */
158
    protected function get_dblibrary() {
159
        return 'native';
160
    }
161
 
162
    /**
163
     * Returns the current MySQL db engine.
164
     *
165
     * This is an ugly workaround for MySQL default engine problems,
166
     * Moodle is designed to work best on ACID compliant databases
167
     * with full transaction support. Do not use MyISAM.
168
     *
169
     * @return string or null MySQL engine name
170
     */
171
    public function get_dbengine() {
172
        if (isset($this->dboptions['dbengine'])) {
173
            return $this->dboptions['dbengine'];
174
        }
175
 
176
        if ($this->external) {
177
            return null;
178
        }
179
 
180
        $engine = null;
181
 
182
        // Look for current engine of our config table (the first table that gets created),
183
        // so that we create all tables with the same engine.
184
        $sql = "SELECT engine
185
                  FROM INFORMATION_SCHEMA.TABLES
186
                 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'";
187
        $this->query_start($sql, null, SQL_QUERY_AUX);
188
        $result = $this->mysqli->query($sql);
189
        $this->query_end($result);
190
        if ($rec = $result->fetch_assoc()) {
191
            // MySQL 8 BC: information_schema.* returns the fields in upper case.
192
            $rec = array_change_key_case($rec, CASE_LOWER);
193
            $engine = $rec['engine'];
194
        }
195
        $result->close();
196
 
197
        if ($engine) {
198
            // Cache the result to improve performance.
199
            $this->dboptions['dbengine'] = $engine;
200
            return $engine;
201
        }
202
 
203
        // Get the default database engine.
204
        $sql = "SELECT @@default_storage_engine engine";
205
        $this->query_start($sql, null, SQL_QUERY_AUX);
206
        $result = $this->mysqli->query($sql);
207
        $this->query_end($result);
208
        if ($rec = $result->fetch_assoc()) {
209
            $engine = $rec['engine'];
210
        }
211
        $result->close();
212
 
213
        if ($engine === 'MyISAM') {
214
            // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported
215
            $sql = "SHOW STORAGE ENGINES";
216
            $this->query_start($sql, null, SQL_QUERY_AUX);
217
            $result = $this->mysqli->query($sql);
218
            $this->query_end($result);
219
            $engines = array();
220
            while ($res = $result->fetch_assoc()) {
221
                if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') {
222
                    $engines[$res['Engine']] = true;
223
                }
224
            }
225
            $result->close();
226
            if (isset($engines['InnoDB'])) {
227
                $engine = 'InnoDB';
228
            }
229
            if (isset($engines['XtraDB'])) {
230
                $engine = 'XtraDB';
231
            }
232
        }
233
 
234
        // Cache the result to improve performance.
235
        $this->dboptions['dbengine'] = $engine;
236
        return $engine;
237
    }
238
 
239
    /**
240
     * Returns the current MySQL db collation.
241
     *
242
     * This is an ugly workaround for MySQL default collation problems.
243
     *
244
     * @return string or null MySQL collation name
245
     */
246
    public function get_dbcollation() {
247
        if (isset($this->dboptions['dbcollation'])) {
248
            return $this->dboptions['dbcollation'];
249
        }
250
    }
251
 
252
    /**
253
     * Set 'dbcollation' option
254
     *
255
     * @return string|null $dbcollation
256
     */
257
    private function detect_collation(): ?string {
258
        if ($this->external) {
259
            return null;
260
        }
261
 
262
        $collation = null;
263
 
264
        // Look for current collation of our config table (the first table that gets created),
265
        // so that we create all tables with the same collation.
266
        $sql = "SELECT collation_name
267
                  FROM INFORMATION_SCHEMA.COLUMNS
268
                 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config' AND column_name = 'value'";
269
        $result = $this->mysqli->query($sql);
270
        if ($rec = $result->fetch_assoc()) {
271
            // MySQL 8 BC: information_schema.* returns the fields in upper case.
272
            $rec = array_change_key_case($rec, CASE_LOWER);
273
            $collation = $rec['collation_name'];
274
        }
275
        $result->close();
276
 
277
 
278
        if (!$collation) {
279
            // Get the default database collation, but only if using UTF-8.
280
            $sql = "SELECT @@collation_database";
281
            $result = $this->mysqli->query($sql);
282
            if ($rec = $result->fetch_assoc()) {
283
                if (strpos($rec['@@collation_database'], 'utf8_') === 0 || strpos($rec['@@collation_database'], 'utf8mb4_') === 0) {
284
                    $collation = $rec['@@collation_database'];
285
                }
286
            }
287
            $result->close();
288
        }
289
 
290
        if (!$collation) {
291
            // We want only utf8 compatible collations.
292
            $collation = null;
293
            $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
294
            $result = $this->mysqli->query($sql);
295
            while ($res = $result->fetch_assoc()) {
296
                $collation = $res['Collation'];
297
                if (strtoupper($res['Default']) === 'YES') {
298
                    $collation = $res['Collation'];
299
                    break;
300
                }
301
            }
302
            $result->close();
303
        }
304
 
305
        // Cache the result to improve performance.
306
        $this->dboptions['dbcollation'] = $collation;
307
        return $collation;
308
    }
309
 
310
    /**
311
     * Tests if the Antelope file format is still supported or it has been removed.
312
     * When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine.
313
     *
314
     * @return bool True if the Antelope file format has been removed; otherwise, false.
315
     */
316
    protected function is_antelope_file_format_no_more_supported() {
317
        // Breaking change: Antelope file format support has been removed from both MySQL and MariaDB.
318
        // The following InnoDB file format configuration parameters were deprecated and then removed:
319
        // - innodb_file_format
320
        // - innodb_file_format_check
321
        // - innodb_file_format_max
322
        // - innodb_large_prefix
323
        // 1. MySQL: deprecated in 5.7.7 and removed 8.0.0+.
324
        $ismysqlge8d0d0 = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql') &&
325
                version_compare($this->get_server_info()['version'], '8.0.0', '>=');
326
        // 2. MariaDB: deprecated in 10.2.0 and removed 10.3.1+.
327
        $ismariadbge10d3d1 = ($this->get_dbtype() == 'mariadb') &&
328
                version_compare($this->get_server_info()['version'], '10.3.1', '>=');
329
 
330
        return $ismysqlge8d0d0 || $ismariadbge10d3d1;
331
    }
332
 
333
    /**
334
     * Get the row format from the database schema.
335
     *
336
     * @param string $table
337
     * @return string row_format name or null if not known or table does not exist.
338
     */
339
    public function get_row_format($table = null) {
340
        $rowformat = null;
341
        if (isset($table)) {
342
            $table = $this->mysqli->real_escape_string($table);
343
            $sql = "SELECT row_format
344
                      FROM INFORMATION_SCHEMA.TABLES
345
                     WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'";
346
        } else {
347
            if ($this->is_antelope_file_format_no_more_supported()) {
348
                // Breaking change: Antelope file format support has been removed, only Barracuda.
349
                $dbengine = $this->get_dbengine();
350
                $supporteddbengines = array('InnoDB', 'XtraDB');
351
                if (in_array($dbengine, $supporteddbengines)) {
352
                    $rowformat = 'Barracuda';
353
                }
354
 
355
                return $rowformat;
356
            }
357
 
358
            $sql = "SHOW VARIABLES LIKE 'innodb_file_format'";
359
        }
360
        $this->query_start($sql, null, SQL_QUERY_AUX);
361
        $result = $this->mysqli->query($sql);
362
        $this->query_end($result);
363
        if ($rec = $result->fetch_assoc()) {
364
            // MySQL 8 BC: information_schema.* returns the fields in upper case.
365
            $rec = array_change_key_case($rec, CASE_LOWER);
366
            if (isset($table)) {
367
                $rowformat = $rec['row_format'];
368
            } else {
369
                $rowformat = $rec['value'];
370
            }
371
        }
372
        $result->close();
373
 
374
        return $rowformat;
375
    }
376
 
377
    /**
378
     * Is this database compatible with compressed row format?
379
     * This feature is necessary for support of large number of text
380
     * columns in InnoDB/XtraDB database.
381
     *
382
     * @param bool $cached use cached result
383
     * @return bool true if table can be created or changed to compressed row format.
384
     */
385
    public function is_compressed_row_format_supported($cached = true) {
386
        if ($cached and isset($this->compressedrowformatsupported)) {
387
            return($this->compressedrowformatsupported);
388
        }
389
 
390
        $engine = strtolower($this->get_dbengine());
391
        $info = $this->get_server_info();
392
 
393
        if (version_compare($info['version'], '5.5.0') < 0) {
394
            // MySQL 5.1 is not supported here because we cannot read the file format.
395
            $this->compressedrowformatsupported = false;
396
 
397
        } else if ($engine !== 'innodb' and $engine !== 'xtradb') {
398
            // Other engines are not supported, most probably not compatible.
399
            $this->compressedrowformatsupported = false;
400
 
401
        } else if (!$this->is_file_per_table_enabled()) {
402
            $this->compressedrowformatsupported = false;
403
 
404
        } else if ($this->get_row_format() !== 'Barracuda') {
405
            $this->compressedrowformatsupported = false;
406
 
407
        } else if ($this->get_dbtype() === 'auroramysql') {
408
            // Aurora MySQL doesn't support COMPRESSED and falls back to COMPACT if you try to use it.
409
            $this->compressedrowformatsupported = false;
410
 
411
        } else {
412
            // All the tests passed, we can safely use ROW_FORMAT=Compressed in sql statements.
413
            $this->compressedrowformatsupported = true;
414
        }
415
 
416
        return $this->compressedrowformatsupported;
417
    }
418
 
419
    /**
420
     * Check the database to see if innodb_file_per_table is on.
421
     *
422
     * @return bool True if on otherwise false.
423
     */
424
    public function is_file_per_table_enabled() {
425
        if ($filepertable = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_file_per_table'")) {
426
            if ($filepertable->value == 'ON') {
427
                return true;
428
            }
429
        }
430
        return false;
431
    }
432
 
433
    /**
434
     * Check the database to see if innodb_large_prefix is on.
435
     *
436
     * @return bool True if on otherwise false.
437
     */
438
    public function is_large_prefix_enabled() {
439
        if ($this->is_antelope_file_format_no_more_supported()) {
440
            // Breaking change: Antelope file format support has been removed, only Barracuda.
441
            return true;
442
        }
443
 
444
        if ($largeprefix = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_large_prefix'")) {
445
            if ($largeprefix->value == 'ON') {
446
                return true;
447
            }
448
        }
449
        return false;
450
    }
451
 
452
    /**
453
     * Determine if the row format should be set to compressed, dynamic, or default.
454
     *
455
     * Terrible kludge. If we're using utf8mb4 AND we're using InnoDB, we need to specify row format to
456
     * be either dynamic or compressed (default is compact) in order to allow for bigger indexes (MySQL
457
     * errors #1709 and #1071).
458
     *
459
     * @param  string $engine The database engine being used. Will be looked up if not supplied.
460
     * @param  string $collation The database collation to use. Will look up the current collation if not supplied.
461
     * @return string An sql fragment to add to sql statements.
462
     */
463
    public function get_row_format_sql($engine = null, $collation = null) {
464
 
465
        if (!isset($engine)) {
466
            $engine = $this->get_dbengine();
467
        }
468
        $engine = strtolower($engine);
469
 
470
        if (!isset($collation)) {
471
            $collation = $this->get_dbcollation();
472
        }
473
 
474
        $rowformat = '';
475
        if (($engine === 'innodb' || $engine === 'xtradb') && strpos($collation, 'utf8mb4_') === 0) {
476
            if ($this->is_compressed_row_format_supported()) {
477
                $rowformat = "ROW_FORMAT=Compressed";
478
            } else {
479
                $rowformat = "ROW_FORMAT=Dynamic";
480
            }
481
        }
482
        return $rowformat;
483
    }
484
 
485
    /**
486
     * Returns localised database type name
487
     * Note: can be used before connect()
488
     * @return string
489
     */
490
    public function get_name() {
491
        return get_string('nativemysqli', 'install');
492
    }
493
 
494
    /**
495
     * Returns localised database configuration help.
496
     * Note: can be used before connect()
497
     * @return string
498
     */
499
    public function get_configuration_help() {
500
        return get_string('nativemysqlihelp', 'install');
501
    }
502
 
503
    /**
504
     * Diagnose database and tables, this function is used
505
     * to verify database and driver settings, db engine types, etc.
506
     *
507
     * @return string null means everything ok, string means problem found.
508
     */
509
    public function diagnose() {
510
        $sloppymyisamfound = false;
511
        $prefix = str_replace('_', '\\_', $this->prefix);
512
        $sql = "SELECT COUNT('x')
513
                  FROM INFORMATION_SCHEMA.TABLES
514
                 WHERE table_schema = DATABASE()
515
                       AND table_name LIKE BINARY '$prefix%'
516
                       AND Engine = 'MyISAM'";
517
        $this->query_start($sql, null, SQL_QUERY_AUX);
518
        $result = $this->mysqli->query($sql);
519
        $this->query_end($result);
520
        if ($result) {
521
            if ($arr = $result->fetch_assoc()) {
522
                $count = reset($arr);
523
                if ($count) {
524
                    $sloppymyisamfound = true;
525
                }
526
            }
527
            $result->close();
528
        }
529
 
530
        if ($sloppymyisamfound) {
531
            return get_string('myisamproblem', 'error');
532
        } else {
533
            return null;
534
        }
535
    }
536
 
537
    /**
538
     * Connect to db
539
     * @param string $dbhost The database host.
540
     * @param string $dbuser The database username.
541
     * @param string $dbpass The database username's password.
542
     * @param string $dbname The name of the database being connected to.e
543
     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
544
     * @param array $dboptions driver specific options
545
     * @return bool success
546
     * @throws moodle_exception
547
     * @throws dml_connection_exception if error
548
     */
549
    public function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null): bool {
550
        $driverstatus = $this->driver_installed();
551
 
552
        if ($driverstatus !== true) {
553
            throw new dml_exception('dbdriverproblem', $driverstatus);
554
        }
555
 
556
        $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
557
 
558
        // The dbsocket option is used ONLY if host is null or 'localhost'.
559
        // You can not disable it because it is always tried if dbhost is 'localhost'.
560
        if (!empty($this->dboptions['dbsocket'])
561
                and (strpos($this->dboptions['dbsocket'], '/') !== false or strpos($this->dboptions['dbsocket'], '\\') !== false)) {
562
            $dbsocket = $this->dboptions['dbsocket'];
563
        } else {
564
            $dbsocket = ini_get('mysqli.default_socket');
565
        }
566
        if (empty($this->dboptions['dbport'])) {
567
            $dbport = (int)ini_get('mysqli.default_port');
568
        } else {
569
            $dbport = (int)$this->dboptions['dbport'];
570
        }
571
        // verify ini.get does not return nonsense
572
        if (empty($dbport)) {
573
            $dbport = 3306;
574
        }
575
        if ($dbhost and !empty($this->dboptions['dbpersist'])) {
576
            $dbhost = "p:$dbhost";
577
        }
578
 
579
        // We want to keep exceptions out from the native driver.
580
        // TODO: See MDL-75761 for future improvements.
581
        mysqli_report(MYSQLI_REPORT_OFF); // Disable reporting (default before PHP 8.1).
582
 
583
        $this->mysqli = mysqli_init();
584
        if (!empty($this->dboptions['connecttimeout'])) {
585
            $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->dboptions['connecttimeout']);
586
        }
587
 
588
        $flags = 0;
589
        if ($this->dboptions['clientcompress'] ?? false) {
590
            $flags |= MYSQLI_CLIENT_COMPRESS;
591
        }
592
        if (isset($this->dboptions['ssl'])) {
593
            $sslmode = $this->dboptions['ssl'];
594
            if (!in_array($sslmode, self::$sslmodes, true)) {
595
                throw new moodle_exception("Invalid 'dboptions''ssl' value '$sslmode'");
596
            }
597
            $flags |= MYSQLI_CLIENT_SSL;
598
            if ($sslmode === 'verify-full') {
599
                $flags |= MYSQLI_CLIENT_SSL_VERIFY_SERVER_CERT;
600
            }
601
        }
602
 
603
        $conn = null;
604
        $dberr = null;
605
        try {
606
            // real_connect() is doing things we don't expext.
607
            $conn = @$this->mysqli->real_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket, $flags);
608
        } catch (\Exception $e) {
609
            $dberr = "$e";
610
        }
611
        if (!$conn) {
612
            $dberr = $dberr ?: "{$this->mysqli->connect_error} ({$this->mysqli->connect_errno})";
613
            $this->mysqli = null;
614
            throw new dml_connection_exception($dberr);
615
        }
616
 
617
        // Disable logging until we are fully setup.
618
        $this->query_log_prevent();
619
 
620
        if (isset($dboptions['dbcollation'])) {
621
            $collation = $this->dboptions['dbcollation'] = $dboptions['dbcollation'];
622
        } else {
623
            $collation = $this->detect_collation();
624
        }
625
        $collationinfo = explode('_', $collation);
626
        $charset = reset($collationinfo);
627
 
628
        $this->mysqli->set_charset($charset);
629
 
630
        // If available, enforce strict mode for the session. That guaranties
631
        // standard behaviour under some situations, avoiding some MySQL nasty
632
        // habits like truncating data or performing some transparent cast losses.
633
        // With strict mode enforced, Moodle DB layer will be consistently throwing
634
        // the corresponding exceptions as expected.
635
        $si = $this->get_server_info();
636
        if (version_compare($si['version'], '5.0.2', '>=')) {
637
            $sql = "SET SESSION sql_mode = 'STRICT_ALL_TABLES'";
638
            $result = $this->mysqli->query($sql);
639
        }
640
 
641
        // We can enable logging now.
642
        $this->query_log_allow();
643
 
644
        // Connection stabilised and configured, going to instantiate the temptables controller
645
        $this->temptables = new mysqli_native_moodle_temptables($this);
646
 
647
        return true;
648
    }
649
 
650
    /**
651
     * Close database connection and release all resources
652
     * and memory (especially circular memory references).
653
     * Do NOT use connect() again, create a new instance if needed.
654
     */
655
    public function dispose() {
656
        parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
657
        if ($this->mysqli) {
658
            $this->mysqli->close();
659
            $this->mysqli = null;
660
        }
661
    }
662
 
663
    /**
664
     * Gets db handle currently used with queries
665
     * @return resource
666
     */
667
    protected function get_db_handle() {
668
        return $this->mysqli;
669
    }
670
 
671
    /**
672
     * Sets db handle to be used with subsequent queries
673
     * @param resource $dbh
674
     * @return void
675
     */
676
    protected function set_db_handle($dbh): void {
677
        $this->mysqli = $dbh;
678
    }
679
 
680
    /**
681
     * Check if The query qualifies for readonly connection execution
682
     * Logging queries are exempt, those are write operations that circumvent
683
     * standard query_start/query_end paths.
684
     * @param int $type type of query
685
     * @param string $sql
686
     * @return bool
687
     */
688
    protected function can_use_readonly(int $type, string $sql): bool {
689
        // ... *_LOCK queries always go to master.
690
        if (preg_match('/\b(GET|RELEASE)_LOCK/i', $sql)) {
691
            return false;
692
        }
693
 
694
        return $this->read_slave_can_use_readonly($type, $sql);
695
    }
696
 
697
    /**
698
     * Returns the version of the MySQL server, as reported by the PHP client connection.
699
     *
700
     * Wrap $this->mysqli->server_info to improve testing strategy.
701
     *
702
     * @return string A string representing the version of the MySQL server that the MySQLi extension is connected to.
703
     */
704
    protected function get_mysqli_server_info(): string {
705
        return $this->mysqli->server_info;
706
    }
707
 
708
    /**
709
     * Returns the version of the MySQL server, as reported by 'SELECT VERSION()' query.
710
     *
711
     * @return string A string that indicates the MySQL server version.
712
     * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail.
713
     */
714
    protected function get_version_from_db(): string {
715
        $version = null;
716
        // Query the DB server for the server version.
717
        $sql = "SELECT VERSION() version;";
718
        try {
719
            $result = $this->mysqli->query($sql);
720
            if ($result) {
721
                if ($row = $result->fetch_assoc()) {
722
                    $version = $row['version'];
723
                }
724
                $result->close();
725
                unset($row);
726
            }
727
        } catch (\Throwable $e) { // Exceptions in case of MYSQLI_REPORT_STRICT.
728
            // It looks like we've an issue out of the expected boolean 'false' result above.
729
            throw new dml_read_exception($e->getMessage(), $sql);
730
        }
731
        if (empty($version)) {
732
            // Exception dml_read_exception usually reports raw mysqli errors i.e. not localised by Moodle.
733
            throw new dml_read_exception("Unable to read the DB server version.", $sql);
734
        }
735
 
736
        return $version;
737
    }
738
 
739
    /**
740
     * Returns whether $CFG->dboptions['versionfromdb'] has been set to boolean `true`.
741
     *
742
     * @return bool True if $CFG->dboptions['versionfromdb'] has been set to boolean `true`. Otherwise, `false`.
743
     */
744
    protected function should_db_version_be_read_from_db(): bool {
745
        if (!empty($this->dboptions['versionfromdb'])) {
746
            return true;
747
        }
748
 
749
        return false;
750
    }
751
 
752
    /**
753
     * Returns database server info array.
754
     * @return array Array containing 'description' and 'version' info.
755
     * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail.
756
     */
757
    public function get_server_info() {
758
        $version = $this->serverversion;
759
        if (empty($version)) {
760
            $version = $this->get_mysqli_server_info();
761
            // The version returned by the PHP client could not be the actual DB server version.
762
            // For example in MariaDB, it was prefixed by the RPL_VERSION_HACK, "5.5.5-" (MDEV-4088), starting from 10.x,
763
            // when not using an authentication plug-in.
764
            // Strip the RPL_VERSION_HACK prefix off - it will be "always" there in MariaDB until MDEV-28910 will be implemented.
765
            $version = str_replace('5.5.5-', '', $version);
766
 
767
            // Should we use the VERSION function to get the actual DB version instead of the PHP client version above?
768
            if ($this->should_db_version_be_read_from_db()) {
769
                // Try to query the actual version of the target database server: indeed some cloud providers, e.g. Azure,
770
                // put a gateway in front of the actual instance which reports its own version to the PHP client
771
                // and it doesn't represent the actual version of the DB server the PHP client is connected to.
772
                // Refs:
773
                // - https://learn.microsoft.com/en-us/azure/mariadb/concepts-supported-versions
774
                // - https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-connect-to-a-gateway-node .
775
                // Reset the version returned by the PHP client with the actual DB version reported by 'VERSION' function.
776
                $version = $this->get_version_from_db();
777
            }
778
 
779
            // The version here starts with the following naming scheme: 'X.Y.Z[-<suffix>]'.
780
            // Example: in MariaDB at least one suffix is "always" there, hardcoded in 'mysql_version.h.in':
781
            // #define MYSQL_SERVER_VERSION       "@VERSION@-MariaDB"
782
            // MariaDB and MySQL server version could have extra suffixes too, set by the compilation environment,
783
            // e.g. '-debug', '-embedded', '-log' or any other vendor specific suffix (e.g. build information).
784
            // Strip out any suffix.
785
            $parts = explode('-', $version, 2);
786
            // Finally, keep just major, minor and patch versions (X.Y.Z) from the reported DB server version.
787
            $this->serverversion = $parts[0];
788
        }
789
 
790
        return [
791
            'description' => $this->get_mysqli_server_info(),
792
            'version' => $this->serverversion
793
        ];
794
    }
795
 
796
    /**
797
     * Returns supported query parameter types
798
     * @return int bitmask of accepted SQL_PARAMS_*
799
     */
800
    protected function allowed_param_types() {
801
        return SQL_PARAMS_QM;
802
    }
803
 
804
    /**
805
     * Returns last error reported by database engine.
806
     * @return string error message
807
     */
808
    public function get_last_error() {
809
        return $this->mysqli->error;
810
    }
811
 
812
    /**
813
     * Return tables in database WITHOUT current prefix
814
     * @param bool $usecache if true, returns list of cached tables.
815
     * @return array of table names in lowercase and without prefix
816
     */
817
    public function get_tables($usecache=true) {
818
        if ($usecache and $this->tables !== null) {
819
            return $this->tables;
820
        }
821
        $this->tables = array();
822
        $prefix = str_replace('_', '\\_', $this->prefix);
823
        $sql = "SHOW TABLES LIKE '$prefix%'";
824
        $this->query_start($sql, null, $usecache ? SQL_QUERY_AUX_READONLY : SQL_QUERY_AUX);
825
        $result = $this->mysqli->query($sql);
826
        $this->query_end($result);
827
        $len = strlen($this->prefix);
828
        if ($result) {
829
            while ($arr = $result->fetch_assoc()) {
830
                $tablename = reset($arr);
831
                $tablename = substr($tablename, $len);
832
                $this->tables[$tablename] = $tablename;
833
            }
834
            $result->close();
835
        }
836
 
837
        // Add the currently available temptables
838
        $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
839
        return $this->tables;
840
    }
841
 
842
    /**
843
     * Return table indexes - everything lowercased.
844
     * @param string $table The table we want to get indexes from.
845
     * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed
846
     */
847
    public function get_indexes($table) {
848
        $indexes = array();
849
        $fixedtable = $this->fix_table_name($table);
850
        $sql = "SHOW INDEXES FROM $fixedtable";
851
        $this->query_start($sql, null, SQL_QUERY_AUX_READONLY);
852
        $result = $this->mysqli->query($sql);
853
        try {
854
            $this->query_end($result);
855
        } catch (dml_read_exception $e) {
856
            return $indexes; // table does not exist - no indexes...
857
        }
858
        if ($result) {
859
            while ($res = $result->fetch_object()) {
860
                if ($res->Key_name === 'PRIMARY') {
861
                    continue;
862
                }
863
                if (!isset($indexes[$res->Key_name])) {
864
                    $indexes[$res->Key_name] = array('unique'=>empty($res->Non_unique), 'columns'=>array());
865
                }
866
                $indexes[$res->Key_name]['columns'][$res->Seq_in_index-1] = $res->Column_name;
867
            }
868
            $result->close();
869
        }
870
        return $indexes;
871
    }
872
 
873
    /**
874
     * Fetches detailed information about columns in table.
875
     *
876
     * @param string $table name
877
     * @return database_column_info[] array of database_column_info objects indexed with column names
878
     */
879
    protected function fetch_columns(string $table): array {
880
        $structure = array();
881
 
882
        $sql = "SELECT column_name, data_type, character_maximum_length, numeric_precision,
883
                       numeric_scale, is_nullable, column_type, column_default, column_key, extra
884
                  FROM information_schema.columns
885
                 WHERE table_name = '" . $this->prefix.$table . "'
886
                       AND table_schema = '" . $this->dbname . "'
887
              ORDER BY ordinal_position";
888
        $this->query_start($sql, null, SQL_QUERY_AUX_READONLY);
889
        $result = $this->mysqli->query($sql);
890
        $this->query_end(true); // Don't want to throw anything here ever. MDL-30147
891
 
892
        if ($result === false) {
893
            return array();
894
        }
895
 
896
        if ($result->num_rows > 0) {
897
            // standard table exists
898
            while ($rawcolumn = $result->fetch_assoc()) {
899
                // MySQL 8 BC: information_schema.* returns the fields in upper case.
900
                $rawcolumn = array_change_key_case($rawcolumn, CASE_LOWER);
901
                $info = (object)$this->get_column_info((object)$rawcolumn);
902
                $structure[$info->name] = new database_column_info($info);
903
            }
904
            $result->close();
905
 
906
        } else {
907
            // temporary tables are not in information schema, let's try it the old way
908
            $result->close();
909
            $fixedtable = $this->fix_table_name($table);
910
            $sql = "SHOW COLUMNS FROM $fixedtable";
911
            $this->query_start($sql, null, SQL_QUERY_AUX_READONLY);
912
            $result = $this->mysqli->query($sql);
913
            $this->query_end(true);
914
            if ($result === false) {
915
                return array();
916
            }
917
            while ($rawcolumn = $result->fetch_assoc()) {
918
                $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER);
919
                $rawcolumn->column_name              = $rawcolumn->field; unset($rawcolumn->field);
920
                $rawcolumn->column_type              = $rawcolumn->type; unset($rawcolumn->type);
921
                $rawcolumn->character_maximum_length = null;
922
                $rawcolumn->numeric_precision        = null;
923
                $rawcolumn->numeric_scale            = null;
924
                $rawcolumn->is_nullable              = $rawcolumn->null; unset($rawcolumn->null);
925
                $rawcolumn->column_default           = $rawcolumn->default; unset($rawcolumn->default);
926
                $rawcolumn->column_key               = $rawcolumn->key; unset($rawcolumn->key);
927
 
928
                if (preg_match('/(enum|varchar)\((\d+)\)/i', $rawcolumn->column_type, $matches)) {
929
                    $rawcolumn->data_type = $matches[1];
930
                    $rawcolumn->character_maximum_length = $matches[2];
931
 
932
                } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) {
933
                    $rawcolumn->data_type = $matches[1];
934
                    $rawcolumn->numeric_precision = $matches[2];
935
                    $rawcolumn->max_length = $rawcolumn->numeric_precision;
936
 
937
                    $type = strtoupper($matches[1]);
938
                    if ($type === 'BIGINT') {
939
                        $maxlength = 18;
940
                    } else if ($type === 'INT' or $type === 'INTEGER') {
941
                        $maxlength = 9;
942
                    } else if ($type === 'MEDIUMINT') {
943
                        $maxlength = 6;
944
                    } else if ($type === 'SMALLINT') {
945
                        $maxlength = 4;
946
                    } else if ($type === 'TINYINT') {
947
                        $maxlength = 2;
948
                    } else {
949
                        // This should not happen.
950
                        $maxlength = 0;
951
                    }
952
                    if ($maxlength < $rawcolumn->max_length) {
953
                        $rawcolumn->max_length = $maxlength;
954
                    }
955
 
956
                } else if (preg_match('/(decimal)\((\d+),(\d+)\)/i', $rawcolumn->column_type, $matches)) {
957
                    $rawcolumn->data_type = $matches[1];
958
                    $rawcolumn->numeric_precision = $matches[2];
959
                    $rawcolumn->numeric_scale = $matches[3];
960
 
961
                } else if (preg_match('/(double|float)(\((\d+),(\d+)\))?/i', $rawcolumn->column_type, $matches)) {
962
                    $rawcolumn->data_type = $matches[1];
963
                    $rawcolumn->numeric_precision = isset($matches[3]) ? $matches[3] : null;
964
                    $rawcolumn->numeric_scale = isset($matches[4]) ? $matches[4] : null;
965
 
966
                } else if (preg_match('/([a-z]*text)/i', $rawcolumn->column_type, $matches)) {
967
                    $rawcolumn->data_type = $matches[1];
968
                    $rawcolumn->character_maximum_length = -1; // unknown
969
 
970
                } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->column_type, $matches)) {
971
                    $rawcolumn->data_type = $matches[1];
972
 
973
                } else {
974
                    $rawcolumn->data_type = $rawcolumn->column_type;
975
                }
976
 
977
                $info = $this->get_column_info($rawcolumn);
978
                $structure[$info->name] = new database_column_info($info);
979
            }
980
            $result->close();
981
        }
982
 
983
        return $structure;
984
    }
985
 
986
    /**
987
     * Indicates whether column information retrieved from `information_schema.columns` has default values quoted or not.
988
     * @return boolean True when default values are quoted (breaking change); otherwise, false.
989
     */
990
    protected function has_breaking_change_quoted_defaults() {
991
        return false;
992
    }
993
 
994
    /**
995
     * Indicates whether SQL_MODE default value has changed in a not backward compatible way.
996
     * @return boolean True when SQL_MODE breaks BC; otherwise, false.
997
     */
998
    public function has_breaking_change_sqlmode() {
999
        return false;
1000
    }
1001
 
1002
    /**
1003
     * Returns moodle column info for raw column from information schema.
1004
     * @param stdClass $rawcolumn
1005
     * @return stdClass standardised colum info
1006
     */
1007
    private function get_column_info(stdClass $rawcolumn) {
1008
        $rawcolumn = (object)$rawcolumn;
1009
        $info = new stdClass();
1010
        $info->name           = $rawcolumn->column_name;
1011
        $info->type           = $rawcolumn->data_type;
1012
        $info->meta_type      = $this->mysqltype2moodletype($rawcolumn->data_type);
1013
        if ($this->has_breaking_change_quoted_defaults()) {
1014
            $info->default_value = is_null($rawcolumn->column_default) ? null : trim($rawcolumn->column_default, "'");
1015
            if ($info->default_value === 'NULL') {
1016
                $info->default_value = null;
1017
            }
1018
        } else {
1019
            $info->default_value = $rawcolumn->column_default;
1020
        }
1021
        $info->has_default    = !is_null($info->default_value);
1022
        $info->not_null       = ($rawcolumn->is_nullable === 'NO');
1023
        $info->primary_key    = ($rawcolumn->column_key === 'PRI');
1024
        $info->binary         = false;
1025
        $info->unsigned       = null;
1026
        $info->auto_increment = false;
1027
        $info->unique         = null;
1028
        $info->scale          = null;
1029
 
1030
        if ($info->meta_type === 'C') {
1031
            $info->max_length = $rawcolumn->character_maximum_length;
1032
 
1033
        } else if ($info->meta_type === 'I') {
1034
            if ($info->primary_key) {
1035
                $info->meta_type = 'R';
1036
                $info->unique    = true;
1037
            }
1038
            // Return number of decimals, not bytes here.
1039
            $info->max_length    = $rawcolumn->numeric_precision;
1040
            if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) {
1041
                $type = strtoupper($matches[1]);
1042
                if ($type === 'BIGINT') {
1043
                    $maxlength = 18;
1044
                } else if ($type === 'INT' or $type === 'INTEGER') {
1045
                    $maxlength = 9;
1046
                } else if ($type === 'MEDIUMINT') {
1047
                    $maxlength = 6;
1048
                } else if ($type === 'SMALLINT') {
1049
                    $maxlength = 4;
1050
                } else if ($type === 'TINYINT') {
1051
                    $maxlength = 2;
1052
                } else {
1053
                    // This should not happen.
1054
                    $maxlength = 0;
1055
                }
1056
                // It is possible that display precision is different from storage type length,
1057
                // always use the smaller value to make sure our data fits.
1058
                if ($maxlength < $info->max_length) {
1059
                    $info->max_length = $maxlength;
1060
                }
1061
            }
1062
            $info->unsigned      = (stripos($rawcolumn->column_type, 'unsigned') !== false);
1063
            $info->auto_increment= (strpos($rawcolumn->extra, 'auto_increment') !== false);
1064
 
1065
        } else if ($info->meta_type === 'N') {
1066
            $info->max_length    = $rawcolumn->numeric_precision;
1067
            $info->scale         = $rawcolumn->numeric_scale;
1068
            $info->unsigned      = (stripos($rawcolumn->column_type, 'unsigned') !== false);
1069
 
1070
        } else if ($info->meta_type === 'X') {
1071
            if ("$rawcolumn->character_maximum_length" === '4294967295') { // watch out for PHP max int limits!
1072
                // means maximum moodle size for text column, in other drivers it may also mean unknown size
1073
                $info->max_length = -1;
1074
            } else {
1075
                $info->max_length = $rawcolumn->character_maximum_length;
1076
            }
1077
            $info->primary_key   = false;
1078
 
1079
        } else if ($info->meta_type === 'B') {
1080
            $info->max_length    = -1;
1081
            $info->primary_key   = false;
1082
            $info->binary        = true;
1083
        }
1084
 
1085
        return $info;
1086
    }
1087
 
1088
    /**
1089
     * Normalise column type.
1090
     * @param string $mysql_type
1091
     * @return string one character
1092
     * @throws dml_exception
1093
     */
1094
    private function mysqltype2moodletype($mysql_type) {
1095
        $type = null;
1096
 
1097
        switch(strtoupper($mysql_type)) {
1098
            case 'BIT':
1099
                $type = 'L';
1100
                break;
1101
 
1102
            case 'TINYINT':
1103
            case 'SMALLINT':
1104
            case 'MEDIUMINT':
1105
            case 'INT':
1106
            case 'INTEGER':
1107
            case 'BIGINT':
1108
                $type = 'I';
1109
                break;
1110
 
1111
            case 'FLOAT':
1112
            case 'DOUBLE':
1113
            case 'DECIMAL':
1114
                $type = 'N';
1115
                break;
1116
 
1117
            case 'CHAR':
1118
            case 'ENUM':
1119
            case 'SET':
1120
            case 'VARCHAR':
1121
                $type = 'C';
1122
                break;
1123
 
1124
            case 'TINYTEXT':
1125
            case 'TEXT':
1126
            case 'MEDIUMTEXT':
1127
            case 'LONGTEXT':
1128
                $type = 'X';
1129
                break;
1130
 
1131
            case 'BINARY':
1132
            case 'VARBINARY':
1133
            case 'BLOB':
1134
            case 'TINYBLOB':
1135
            case 'MEDIUMBLOB':
1136
            case 'LONGBLOB':
1137
                $type = 'B';
1138
                break;
1139
 
1140
            case 'DATE':
1141
            case 'TIME':
1142
            case 'DATETIME':
1143
            case 'TIMESTAMP':
1144
            case 'YEAR':
1145
                $type = 'D';
1146
                break;
1147
        }
1148
 
1149
        if (!$type) {
1150
            throw new dml_exception('invalidmysqlnativetype', $mysql_type);
1151
        }
1152
        return $type;
1153
    }
1154
 
1155
    /**
1156
     * Normalise values based in RDBMS dependencies (booleans, LOBs...)
1157
     *
1158
     * @param database_column_info $column column metadata corresponding with the value we are going to normalise
1159
     * @param mixed $value value we are going to normalise
1160
     * @return mixed the normalised value
1161
     */
1162
    protected function normalise_value($column, $value) {
1163
        $this->detect_objects($value);
1164
 
1165
        if (is_bool($value)) { // Always, convert boolean to int
1166
            $value = (int)$value;
1167
 
1168
        } else if ($value === '') {
1169
            if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
1170
                $value = 0; // prevent '' problems in numeric fields
1171
            }
1172
        // Any float value being stored in varchar or text field is converted to string to avoid
1173
        // any implicit conversion by MySQL
1174
        } else if (is_float($value) and ($column->meta_type == 'C' or $column->meta_type == 'X')) {
1175
            $value = "$value";
1176
        }
1177
        return $value;
1178
    }
1179
 
1180
    /**
1181
     * Is this database compatible with utf8?
1182
     * @return bool
1183
     */
1184
    public function setup_is_unicodedb() {
1185
        // All new tables are created with this collation, we just have to make sure it is utf8 compatible,
1186
        // if config table already exists it has this collation too.
1187
        $collation = $this->get_dbcollation();
1188
 
1189
        $collationinfo = explode('_', $collation);
1190
        $charset = reset($collationinfo);
1191
 
1192
        $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = '$charset'";
1193
        $this->query_start($sql, null, SQL_QUERY_AUX_READONLY);
1194
        $result = $this->mysqli->query($sql);
1195
        $this->query_end($result);
1196
        if ($result->fetch_assoc()) {
1197
            $return = true;
1198
        } else {
1199
            $return = false;
1200
        }
1201
        $result->close();
1202
 
1203
        return $return;
1204
    }
1205
 
1206
    /**
1207
     * Do NOT use in code, to be used by database_manager only!
1208
     * @param string|array $sql query
1209
     * @param array|null $tablenames an array of xmldb table names affected by this request.
1210
     * @return bool true
1211
     * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
1212
     */
1213
    public function change_database_structure($sql, $tablenames = null) {
1214
        $this->get_manager(); // Includes DDL exceptions classes ;-)
1215
        if (is_array($sql)) {
1216
            $sql = implode("\n;\n", $sql);
1217
        }
1218
 
1219
        try {
1220
            $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
1221
            $result = $this->mysqli->multi_query($sql);
1222
            if ($result === false) {
1223
                $this->query_end(false);
1224
            }
1225
            while ($this->mysqli->more_results()) {
1226
                $result = $this->mysqli->next_result();
1227
                if ($result === false) {
1228
                    $this->query_end(false);
1229
                }
1230
            }
1231
            $this->query_end(true);
1232
        } catch (ddl_change_structure_exception $e) {
1233
            while (@$this->mysqli->more_results()) {
1234
                @$this->mysqli->next_result();
1235
            }
1236
            $this->reset_caches($tablenames);
1237
            throw $e;
1238
        }
1239
 
1240
        $this->reset_caches($tablenames);
1241
        return true;
1242
    }
1243
 
1244
    /**
1245
     * Very ugly hack which emulates bound parameters in queries
1246
     * because prepared statements do not use query cache.
1247
     */
1248
    protected function emulate_bound_params($sql, array $params=null) {
1249
        if (empty($params)) {
1250
            return $sql;
1251
        }
1252
        // ok, we have verified sql statement with ? and correct number of params
1253
        $parts = array_reverse(explode('?', $sql));
1254
        $return = array_pop($parts);
1255
        foreach ($params as $param) {
1256
            if (is_bool($param)) {
1257
                $return .= (int)$param;
1258
            } else if (is_null($param)) {
1259
                $return .= 'NULL';
1260
            } else if (is_number($param)) {
1261
                $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting
1262
            } else if (is_float($param)) {
1263
                $return .= $param;
1264
            } else {
1265
                $param = $this->mysqli->real_escape_string($param);
1266
                $return .= "'$param'";
1267
            }
1268
            $return .= array_pop($parts);
1269
        }
1270
        return $return;
1271
    }
1272
 
1273
    /**
1274
     * Execute general sql query. Should be used only when no other method suitable.
1275
     * Do NOT use this to make changes in db structure, use database_manager methods instead!
1276
     * @param string $sql query
1277
     * @param array $params query parameters
1278
     * @return bool true
1279
     * @throws dml_exception A DML specific exception is thrown for any errors.
1280
     */
1281
    public function execute($sql, array $params=null) {
1282
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1283
 
1284
        if (strpos($sql, ';') !== false) {
1285
            throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
1286
        }
1287
 
1288
        $rawsql = $this->emulate_bound_params($sql, $params);
1289
 
1290
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1291
        $result = $this->mysqli->query($rawsql);
1292
        $this->query_end($result);
1293
 
1294
        if ($result === true) {
1295
            return true;
1296
 
1297
        } else {
1298
            $result->close();
1299
            return true;
1300
        }
1301
    }
1302
 
1303
    /**
1304
     * Get a number of records as a moodle_recordset using a SQL statement.
1305
     *
1306
     * Since this method is a little less readable, use of it should be restricted to
1307
     * code where it's possible there might be large datasets being returned.  For known
1308
     * small datasets use get_records_sql - it leads to simpler code.
1309
     *
1310
     * The return type is like:
1311
     * @see function get_recordset.
1312
     *
1313
     * @param string $sql the SQL select query to execute.
1314
     * @param array $params array of sql parameters
1315
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1316
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1317
     * @return moodle_recordset instance
1318
     * @throws dml_exception A DML specific exception is thrown for any errors.
1319
     */
1320
    public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1321
 
1322
        list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
1323
 
1324
        if ($limitfrom or $limitnum) {
1325
            if ($limitnum < 1) {
1326
                $limitnum = "18446744073709551615";
1327
            }
1328
            $sql .= " LIMIT $limitfrom, $limitnum";
1329
        }
1330
 
1331
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1332
        $rawsql = $this->emulate_bound_params($sql, $params);
1333
 
1334
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
1335
        // no MYSQLI_USE_RESULT here, it would block write ops on affected tables
1336
        $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
1337
        $this->query_end($result);
1338
 
1339
        return $this->create_recordset($result);
1340
    }
1341
 
1342
    /**
1343
     * Get all records from a table.
1344
     *
1345
     * This method works around potential memory problems and may improve performance,
1346
     * this method may block access to table until the recordset is closed.
1347
     *
1348
     * @param string $table Name of database table.
1349
     * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}.
1350
     * @throws dml_exception A DML specific exception is thrown for any errors.
1351
     */
1352
    public function export_table_recordset($table) {
1353
        $sql = $this->fix_table_names("SELECT * FROM {{$table}}");
1354
 
1355
        $this->query_start($sql, array(), SQL_QUERY_SELECT);
1356
        // MYSQLI_STORE_RESULT may eat all memory for large tables, unfortunately MYSQLI_USE_RESULT blocks other queries.
1357
        $result = $this->mysqli->query($sql, MYSQLI_USE_RESULT);
1358
        $this->query_end($result);
1359
 
1360
        return $this->create_recordset($result);
1361
    }
1362
 
1363
    protected function create_recordset($result) {
1364
        return new mysqli_native_moodle_recordset($result);
1365
    }
1366
 
1367
    /**
1368
     * Get a number of records as an array of objects using a SQL statement.
1369
     *
1370
     * Return value is like:
1371
     * @see function get_records.
1372
     *
1373
     * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1374
     *   must be a unique value (usually the 'id' field), as it will be used as the key of the
1375
     *   returned array.
1376
     * @param array $params array of sql parameters
1377
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1378
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1379
     * @return array of objects, or empty array if no records were found
1380
     * @throws dml_exception A DML specific exception is thrown for any errors.
1381
     */
1382
    public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1383
 
1384
        list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
1385
 
1386
        if ($limitfrom or $limitnum) {
1387
            if ($limitnum < 1) {
1388
                $limitnum = "18446744073709551615";
1389
            }
1390
            $sql .= " LIMIT $limitfrom, $limitnum";
1391
        }
1392
 
1393
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1394
        $rawsql = $this->emulate_bound_params($sql, $params);
1395
 
1396
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
1397
        $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
1398
        $this->query_end($result);
1399
 
1400
        $return = array();
1401
 
1402
        while($row = $result->fetch_assoc()) {
1403
            $row = array_change_key_case($row, CASE_LOWER);
1404
            $id  = reset($row);
1405
            if (isset($return[$id])) {
1406
                $colname = key($row);
1407
                debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER);
1408
            }
1409
            $return[$id] = (object)$row;
1410
        }
1411
        $result->close();
1412
 
1413
        return $return;
1414
    }
1415
 
1416
    /**
1417
     * Selects records and return values (first field) as an array using a SQL statement.
1418
     *
1419
     * @param string $sql The SQL query
1420
     * @param array $params array of sql parameters
1421
     * @return array of values
1422
     * @throws dml_exception A DML specific exception is thrown for any errors.
1423
     */
1424
    public function get_fieldset_sql($sql, array $params=null) {
1425
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1426
        $rawsql = $this->emulate_bound_params($sql, $params);
1427
 
1428
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
1429
        $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
1430
        $this->query_end($result);
1431
 
1432
        $return = array();
1433
 
1434
        while($row = $result->fetch_assoc()) {
1435
            $return[] = reset($row);
1436
        }
1437
        $result->close();
1438
 
1439
        return $return;
1440
    }
1441
 
1442
    /**
1443
     * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1444
     * @param string $table name
1445
     * @param mixed $params data record as object or array
1446
     * @param bool $returnit return it of inserted record
1447
     * @param bool $bulk true means repeated inserts expected
1448
     * @param bool $customsequence true if 'id' included in $params, disables $returnid
1449
     * @return bool|int true or new id
1450
     * @throws dml_exception A DML specific exception is thrown for any errors.
1451
     */
1452
    public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1453
        if (!is_array($params)) {
1454
            $params = (array)$params;
1455
        }
1456
 
1457
        if ($customsequence) {
1458
            if (!isset($params['id'])) {
1459
                throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1460
            }
1461
            $returnid = false;
1462
        } else {
1463
            unset($params['id']);
1464
        }
1465
 
1466
        if (empty($params)) {
1467
            throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1468
        }
1469
 
1470
        $fields = implode(',', array_keys($params));
1471
        $qms    = array_fill(0, count($params), '?');
1472
        $qms    = implode(',', $qms);
1473
        $fixedtable = $this->fix_table_name($table);
1474
        $sql = "INSERT INTO $fixedtable ($fields) VALUES($qms)";
1475
 
1476
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1477
        $rawsql = $this->emulate_bound_params($sql, $params);
1478
 
1479
        $this->query_start($sql, $params, SQL_QUERY_INSERT);
1480
        $result = $this->mysqli->query($rawsql);
1481
        $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db
1482
        $this->query_end($result);
1483
 
1484
        if (!$customsequence and !$id) {
1485
            throw new dml_write_exception('unknown error fetching inserted id');
1486
        }
1487
 
1488
        if (!$returnid) {
1489
            return true;
1490
        } else {
1491
            return (int)$id;
1492
        }
1493
    }
1494
 
1495
    /**
1496
     * Insert a record into a table and return the "id" field if required.
1497
     *
1498
     * Some conversions and safety checks are carried out. Lobs are supported.
1499
     * If the return ID isn't required, then this just reports success as true/false.
1500
     * $data is an object containing needed data
1501
     * @param string $table The database table to be inserted into
1502
     * @param object|array $dataobject A data object with values for one or more fields in the record
1503
     * @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.
1504
     * @return bool|int true or new id
1505
     * @throws dml_exception A DML specific exception is thrown for any errors.
1506
     */
1507
    public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
1508
        $dataobject = (array)$dataobject;
1509
 
1510
        $columns = $this->get_columns($table);
1511
        if (empty($columns)) {
1512
            throw new dml_exception('ddltablenotexist', $table);
1513
        }
1514
 
1515
        $cleaned = array();
1516
 
1517
        foreach ($dataobject as $field=>$value) {
1518
            if ($field === 'id') {
1519
                continue;
1520
            }
1521
            if (!isset($columns[$field])) {
1522
                continue;
1523
            }
1524
            $column = $columns[$field];
1525
            $cleaned[$field] = $this->normalise_value($column, $value);
1526
        }
1527
 
1528
        return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1529
    }
1530
 
1531
    /**
1532
     * Get chunk size for multiple records insert
1533
     * @return int
1534
     */
1535
    private function insert_chunk_size(): int {
1536
        // MySQL has a relatively small query length limit by default,
1537
        // make sure 'max_allowed_packet' in my.cnf is high enough
1538
        // if you change the following default...
1539
        static $chunksize = null;
1540
        if ($chunksize === null) {
1541
            if (!empty($this->dboptions['bulkinsertsize'])) {
1542
                $chunksize = (int)$this->dboptions['bulkinsertsize'];
1543
 
1544
            } else {
1545
                if (PHP_INT_SIZE === 4) {
1546
                    // Bad luck for Windows, we cannot do any maths with large numbers.
1547
                    $chunksize = 5;
1548
                } else {
1549
                    $sql = "SHOW VARIABLES LIKE 'max_allowed_packet'";
1550
                    $this->query_start($sql, null, SQL_QUERY_AUX);
1551
                    $result = $this->mysqli->query($sql);
1552
                    $this->query_end($result);
1553
                    $size = 0;
1554
                    if ($rec = $result->fetch_assoc()) {
1555
                        $size = $rec['Value'];
1556
                    }
1557
                    $result->close();
1558
                    // Hopefully 200kb per object are enough.
1559
                    $chunksize = (int)($size / 200000);
1560
                    if ($chunksize > 50) {
1561
                        $chunksize = 50;
1562
                    }
1563
                }
1564
            }
1565
        }
1566
        return $chunksize;
1567
    }
1568
 
1569
    /**
1570
     * Insert multiple records into database as fast as possible.
1571
     *
1572
     * Order of inserts is maintained, but the operation is not atomic,
1573
     * use transactions if necessary.
1574
     *
1575
     * This method is intended for inserting of large number of small objects,
1576
     * do not use for huge objects with text or binary fields.
1577
     *
1578
     * @since Moodle 2.7
1579
     *
1580
     * @param string $table  The database table to be inserted into
1581
     * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
1582
     * @return void does not return new record ids
1583
     *
1584
     * @throws coding_exception if data objects have different structure
1585
     * @throws dml_exception A DML specific exception is thrown for any errors.
1586
     */
1587
    public function insert_records($table, $dataobjects) {
1588
        if (!is_array($dataobjects) && !$dataobjects instanceof Traversable) {
1589
            throw new coding_exception('insert_records() passed non-traversable object');
1590
        }
1591
 
1592
        $chunksize = $this->insert_chunk_size();
1593
        $columns = $this->get_columns($table, true);
1594
        $fields = null;
1595
        $count = 0;
1596
        $chunk = array();
1597
        foreach ($dataobjects as $dataobject) {
1598
            if (!is_array($dataobject) and !is_object($dataobject)) {
1599
                throw new coding_exception('insert_records() passed invalid record object');
1600
            }
1601
            $dataobject = (array)$dataobject;
1602
            if ($fields === null) {
1603
                $fields = array_keys($dataobject);
1604
                $columns = array_intersect_key($columns, $dataobject);
1605
                unset($columns['id']);
1606
            } else if ($fields !== array_keys($dataobject)) {
1607
                throw new coding_exception('All dataobjects in insert_records() must have the same structure!');
1608
            }
1609
 
1610
            $count++;
1611
            $chunk[] = $dataobject;
1612
 
1613
            if ($count === $chunksize) {
1614
                $this->insert_chunk($table, $chunk, $columns);
1615
                $chunk = array();
1616
                $count = 0;
1617
            }
1618
        }
1619
 
1620
        if ($count) {
1621
            $this->insert_chunk($table, $chunk, $columns);
1622
        }
1623
    }
1624
 
1625
    /**
1626
     * Insert records in chunks.
1627
     *
1628
     * Note: can be used only from insert_records().
1629
     *
1630
     * @param string $table
1631
     * @param array $chunk
1632
     * @param database_column_info[] $columns
1633
     */
1634
    protected function insert_chunk($table, array $chunk, array $columns) {
1635
        $fieldssql = '('.implode(',', array_keys($columns)).')';
1636
 
1637
        $valuessql = '('.implode(',', array_fill(0, count($columns), '?')).')';
1638
        $valuessql = implode(',', array_fill(0, count($chunk), $valuessql));
1639
 
1640
        $params = array();
1641
        foreach ($chunk as $dataobject) {
1642
            foreach ($columns as $field => $column) {
1643
                $params[] = $this->normalise_value($column, $dataobject[$field]);
1644
            }
1645
        }
1646
 
1647
        $fixedtable = $this->fix_table_name($table);
1648
        $sql = "INSERT INTO $fixedtable $fieldssql VALUES $valuessql";
1649
 
1650
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1651
        $rawsql = $this->emulate_bound_params($sql, $params);
1652
 
1653
        $this->query_start($sql, $params, SQL_QUERY_INSERT);
1654
        $result = $this->mysqli->query($rawsql);
1655
        $this->query_end($result);
1656
    }
1657
 
1658
    /**
1659
     * Import a record into a table, id field is required.
1660
     * Safety checks are NOT carried out. Lobs are supported.
1661
     *
1662
     * @param string $table name of database table to be inserted into
1663
     * @param object $dataobject A data object with values for one or more fields in the record
1664
     * @return bool true
1665
     * @throws dml_exception A DML specific exception is thrown for any errors.
1666
     */
1667
    public function import_record($table, $dataobject) {
1668
        $dataobject = (array)$dataobject;
1669
 
1670
        $columns = $this->get_columns($table);
1671
        $cleaned = array();
1672
 
1673
        foreach ($dataobject as $field=>$value) {
1674
            if (!isset($columns[$field])) {
1675
                continue;
1676
            }
1677
            $cleaned[$field] = $value;
1678
        }
1679
 
1680
        return $this->insert_record_raw($table, $cleaned, false, true, true);
1681
    }
1682
 
1683
    /**
1684
     * Update record in database, as fast as possible, no safety checks, lobs not supported.
1685
     * @param string $table name
1686
     * @param stdClass|array $params data record as object or array
1687
     * @param bool true means repeated updates expected
1688
     * @return bool true
1689
     * @throws dml_exception A DML specific exception is thrown for any errors.
1690
     */
1691
    public function update_record_raw($table, $params, $bulk=false) {
1692
        $params = (array)$params;
1693
 
1694
        if (!isset($params['id'])) {
1695
            throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1696
        }
1697
        $id = $params['id'];
1698
        unset($params['id']);
1699
 
1700
        if (empty($params)) {
1701
            throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1702
        }
1703
 
1704
        $sets = array();
1705
        foreach ($params as $field=>$value) {
1706
            $sets[] = "$field = ?";
1707
        }
1708
 
1709
        $params[] = $id; // last ? in WHERE condition
1710
 
1711
        $sets = implode(',', $sets);
1712
        $fixedtable = $this->fix_table_name($table);
1713
        $sql = "UPDATE $fixedtable SET $sets WHERE id=?";
1714
 
1715
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1716
        $rawsql = $this->emulate_bound_params($sql, $params);
1717
 
1718
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1719
        $result = $this->mysqli->query($rawsql);
1720
        $this->query_end($result);
1721
 
1722
        return true;
1723
    }
1724
 
1725
    /**
1726
     * Update a record in a table
1727
     *
1728
     * $dataobject is an object containing needed data
1729
     * Relies on $dataobject having a variable "id" to
1730
     * specify the record to update
1731
     *
1732
     * @param string $table The database table to be checked against.
1733
     * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue.
1734
     *        Must have an entry for 'id' to map to the table specified.
1735
     * @param bool true means repeated updates expected
1736
     * @return bool true
1737
     * @throws dml_exception A DML specific exception is thrown for any errors.
1738
     */
1739
    public function update_record($table, $dataobject, $bulk=false) {
1740
        $dataobject = (array)$dataobject;
1741
 
1742
        $columns = $this->get_columns($table);
1743
        $cleaned = array();
1744
 
1745
        foreach ($dataobject as $field=>$value) {
1746
            if (!isset($columns[$field])) {
1747
                continue;
1748
            }
1749
            $column = $columns[$field];
1750
            $cleaned[$field] = $this->normalise_value($column, $value);
1751
        }
1752
 
1753
        return $this->update_record_raw($table, $cleaned, $bulk);
1754
    }
1755
 
1756
    /**
1757
     * Set a single field in every table record which match a particular WHERE clause.
1758
     *
1759
     * @param string $table The database table to be checked against.
1760
     * @param string $newfield the field to set.
1761
     * @param string $newvalue the value to set the field to.
1762
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1763
     * @param array $params array of sql parameters
1764
     * @return bool true
1765
     * @throws dml_exception A DML specific exception is thrown for any errors.
1766
     */
1767
    public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
1768
        if ($select) {
1769
            $select = "WHERE $select";
1770
        }
1771
        if (is_null($params)) {
1772
            $params = array();
1773
        }
1774
        list($select, $params, $type) = $this->fix_sql_params($select, $params);
1775
 
1776
        // Get column metadata
1777
        $columns = $this->get_columns($table);
1778
        $column = $columns[$newfield];
1779
 
1780
        $normalised_value = $this->normalise_value($column, $newvalue);
1781
 
1782
        if (is_null($normalised_value)) {
1783
            $newfield = "$newfield = NULL";
1784
        } else {
1785
            $newfield = "$newfield = ?";
1786
            array_unshift($params, $normalised_value);
1787
        }
1788
        $fixedtable = $this->fix_table_name($table);
1789
        $sql = "UPDATE $fixedtable SET $newfield $select";
1790
        $rawsql = $this->emulate_bound_params($sql, $params);
1791
 
1792
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1793
        $result = $this->mysqli->query($rawsql);
1794
        $this->query_end($result);
1795
 
1796
        return true;
1797
    }
1798
 
1799
    /**
1800
     * Delete one or more records from a table which match a particular WHERE clause.
1801
     *
1802
     * @param string $table The database table to be checked against.
1803
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1804
     * @param array $params array of sql parameters
1805
     * @return bool true
1806
     * @throws dml_exception A DML specific exception is thrown for any errors.
1807
     */
1808
    public function delete_records_select($table, $select, array $params=null) {
1809
        if ($select) {
1810
            $select = "WHERE $select";
1811
        }
1812
        $fixedtable = $this->fix_table_name($table);
1813
        $sql = "DELETE FROM $fixedtable $select";
1814
 
1815
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1816
        $rawsql = $this->emulate_bound_params($sql, $params);
1817
 
1818
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1819
        $result = $this->mysqli->query($rawsql);
1820
        $this->query_end($result);
1821
 
1822
        return true;
1823
    }
1824
 
1825
    /**
1826
     * Deletes records using a subquery, which is done with a strange DELETE...JOIN syntax in MySQL
1827
     * because it performs very badly with normal subqueries.
1828
     *
1829
     * @param string $table Table to delete from
1830
     * @param string $field Field in table to match
1831
     * @param string $alias Name of single column in subquery e.g. 'id'
1832
     * @param string $subquery Query that will return values of the field to delete
1833
     * @param array $params Parameters for query
1834
     * @throws dml_exception If there is any error
1835
     */
1836
    public function delete_records_subquery(string $table, string $field, string $alias, string $subquery, array $params = []): void {
1837
        // Aliases mysql_deltable and mysql_subquery are chosen to be unlikely to conflict.
1838
        $this->execute("DELETE mysql_deltable FROM {" . $table . "} mysql_deltable JOIN " .
1839
                "($subquery) mysql_subquery ON mysql_subquery.$alias = mysql_deltable.$field", $params);
1840
    }
1841
 
1842
    public function sql_cast_char2int($fieldname, $text=false) {
1843
        return ' CAST(' . $fieldname . ' AS SIGNED) ';
1844
    }
1845
 
1846
    public function sql_cast_char2real($fieldname, $text=false) {
1847
        // Set to 65 (max mysql 5.5 precision) with 7 as scale
1848
        // because we must ensure at least 6 decimal positions
1849
        // per casting given that postgres is casting to that scale (::real::).
1850
        // Can be raised easily but that must be done in all DBs and tests.
1851
        return ' CAST(' . $fieldname . ' AS DECIMAL(65,7)) ';
1852
    }
1853
 
1854
    public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1855
        $equalop = $notequal ? '<>' : '=';
1856
 
1857
        $collationinfo = explode('_', $this->get_dbcollation());
1858
        $bincollate = reset($collationinfo) . '_bin';
1859
 
1860
        if ($casesensitive) {
1861
            // Current MySQL versions do not support case sensitive and accent insensitive.
1862
            return "$fieldname COLLATE $bincollate $equalop $param";
1863
        } else if ($accentsensitive) {
1864
            // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case.
1865
            return "LOWER($fieldname) COLLATE $bincollate $equalop LOWER($param)";
1866
        } else {
1867
            // Case insensitive and accent insensitive. All collations are that way, but utf8_bin.
1868
            $collation = '';
1869
            if ($this->get_dbcollation() == 'utf8_bin') {
1870
                $collation = 'COLLATE utf8_unicode_ci';
1871
            } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1872
                $collation = 'COLLATE utf8mb4_unicode_ci';
1873
            }
1874
            return "$fieldname $collation $equalop $param";
1875
        }
1876
    }
1877
 
1878
    /**
1879
     * Returns 'LIKE' part of a query.
1880
     *
1881
     * Note that mysql does not support $casesensitive = true and $accentsensitive = false.
1882
     * More information in http://bugs.mysql.com/bug.php?id=19567.
1883
     *
1884
     * @param string $fieldname usually name of the table column
1885
     * @param string $param usually bound query parameter (?, :named)
1886
     * @param bool $casesensitive use case sensitive search
1887
     * @param bool $accensensitive use accent sensitive search (ignored if $casesensitive is true)
1888
     * @param bool $notlike true means "NOT LIKE"
1889
     * @param string $escapechar escape char for '%' and '_'
1890
     * @return string SQL code fragment
1891
     */
1892
    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1893
        if (strpos($param, '%') !== false) {
1894
            debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1895
        }
1896
        $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
1897
 
1898
        $collationinfo = explode('_', $this->get_dbcollation());
1899
        $bincollate = reset($collationinfo) . '_bin';
1900
 
1901
        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1902
 
1903
        if ($casesensitive) {
1904
            // Current MySQL versions do not support case sensitive and accent insensitive.
1905
            return "$fieldname $LIKE $param COLLATE $bincollate ESCAPE '$escapechar'";
1906
 
1907
        } else if ($accentsensitive) {
1908
            // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case.
1909
            return "LOWER($fieldname) $LIKE LOWER($param) COLLATE $bincollate ESCAPE '$escapechar'";
1910
 
1911
        } else {
1912
            // Case insensitive and accent insensitive.
1913
            $collation = '';
1914
            if ($this->get_dbcollation() == 'utf8_bin') {
1915
                // Force a case insensitive comparison if using utf8_bin.
1916
                $collation = 'COLLATE utf8_unicode_ci';
1917
            } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1918
                // Force a case insensitive comparison if using utf8mb4_bin.
1919
                $collation = 'COLLATE utf8mb4_unicode_ci';
1920
            }
1921
 
1922
            return "$fieldname $LIKE $param $collation ESCAPE '$escapechar'";
1923
        }
1924
    }
1925
 
1926
    /**
1927
     * Returns the proper SQL to do CONCAT between the elements passed
1928
     * Can take many parameters
1929
     *
1930
     * @param string $arr,... 1 or more fields/strings to concat
1931
     *
1932
     * @return string The concat sql
1933
     */
1934
    public function sql_concat(...$arr) {
1935
        $s = implode(', ', $arr);
1936
        if ($s === '') {
1937
            return "''";
1938
        }
1939
        return "CONCAT($s)";
1940
    }
1941
 
1942
    /**
1943
     * Returns the proper SQL to do CONCAT between the elements passed
1944
     * with a given separator
1945
     *
1946
     * @param string $separator The string to use as the separator
1947
     * @param array $elements An array of items to concatenate
1948
     * @return string The concat SQL
1949
     */
1950
    public function sql_concat_join($separator="' '", $elements=array()) {
1951
        $s = implode(', ', $elements);
1952
 
1953
        if ($s === '') {
1954
            return "''";
1955
        }
1956
        return "CONCAT_WS($separator, $s)";
1957
    }
1958
 
1959
    /**
1960
     * Return SQL for performing group concatenation on given field/expression
1961
     *
1962
     * @param string $field
1963
     * @param string $separator
1964
     * @param string $sort
1965
     * @return string
1966
     */
1967
    public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
1968
        $fieldsort = $sort ? "ORDER BY {$sort}" : '';
1969
        return "GROUP_CONCAT({$field} {$fieldsort} SEPARATOR '{$separator}')";
1970
    }
1971
 
1972
    /**
1973
     * Returns the SQL text to be used to calculate the length in characters of one expression.
1974
     * @param string fieldname or expression to calculate its length in characters.
1975
     * @return string the piece of SQL code to be used in the statement.
1976
     */
1977
    public function sql_length($fieldname) {
1978
        return ' CHAR_LENGTH(' . $fieldname . ')';
1979
    }
1980
 
1981
    /**
1982
     * Does this driver support regex syntax when searching
1983
     */
1984
    public function sql_regex_supported() {
1985
        return true;
1986
    }
1987
 
1988
    /**
1989
     * Return regex positive or negative match sql
1990
     * @param bool $positivematch
1991
     * @param bool $casesensitive
1992
     * @return string or empty if not supported
1993
     */
1994
    public function sql_regex($positivematch = true, $casesensitive = false) {
1995
        $collation = '';
1996
        if ($casesensitive) {
1997
            if (substr($this->get_dbcollation(), -4) !== '_bin') {
1998
                $collationinfo = explode('_', $this->get_dbcollation());
1999
                $collation = 'COLLATE ' . $collationinfo[0] . '_bin ';
2000
            }
2001
        } else {
2002
            if ($this->get_dbcollation() == 'utf8_bin') {
2003
                $collation = 'COLLATE utf8_unicode_ci ';
2004
            } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
2005
                $collation = 'COLLATE utf8mb4_unicode_ci ';
2006
            }
2007
        }
2008
 
2009
        return $collation . ($positivematch ? 'REGEXP' : 'NOT REGEXP');
2010
    }
2011
 
2012
    /**
2013
     * Returns the word-beginning boundary marker based on MySQL version.
2014
     * @return string The word-beginning boundary marker.
2015
     */
2016
    public function sql_regex_get_word_beginning_boundary_marker() {
2017
        $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql');
2018
        $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
2019
        if ($ismysqlge8d0d4) {
2020
            return '\\b';
2021
        }
2022
        // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
2023
        // rather than International Components for Unicode (ICU).
2024
        // MariaDB still supports the "old marker" (MDEV-5357).
2025
        return '[[:<:]]';
2026
    }
2027
 
2028
    /**
2029
     * Returns the word-end boundary marker based on MySQL version.
2030
     * @return string The word-end boundary marker.
2031
     */
2032
    public function sql_regex_get_word_end_boundary_marker() {
2033
        $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql');
2034
        $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
2035
        if ($ismysqlge8d0d4) {
2036
            return '\\b';
2037
        }
2038
        // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
2039
        // rather than International Components for Unicode (ICU).
2040
        // MariaDB still supports the "old marker" (MDEV-5357).
2041
        return '[[:>:]]';
2042
    }
2043
 
2044
    /**
2045
     * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
2046
     *
2047
     * @deprecated since 2.3
2048
     * @param string $fieldname The name of the field to be cast
2049
     * @return string The piece of SQL code to be used in your statement.
2050
     */
2051
    public function sql_cast_2signed($fieldname) {
2052
        return ' CAST(' . $fieldname . ' AS SIGNED) ';
2053
    }
2054
 
2055
    /**
2056
     * Returns the SQL that allows to find intersection of two or more queries
2057
     *
2058
     * @since Moodle 2.8
2059
     *
2060
     * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields
2061
     * @param string $fields comma-separated list of fields
2062
     * @return string SQL query that will return only values that are present in each of selects
2063
     */
2064
    public function sql_intersect($selects, $fields) {
2065
        if (count($selects) <= 1) {
2066
            return parent::sql_intersect($selects, $fields);
2067
        }
2068
        $fields = preg_replace('/\s/', '', $fields);
2069
        static $aliascnt = 0;
2070
        $falias = 'intsctal'.($aliascnt++);
2071
        $rv = "SELECT $falias.".
2072
            preg_replace('/,/', ','.$falias.'.', $fields).
2073
            " FROM ($selects[0]) $falias";
2074
        for ($i = 1; $i < count($selects); $i++) {
2075
            $alias = 'intsctal'.($aliascnt++);
2076
            $rv .= " JOIN (".$selects[$i].") $alias ON ".
2077
                join(' AND ',
2078
                    array_map(
2079
                        function($a) use ($alias, $falias) {
2080
                            return $falias . '.' . $a .' = ' . $alias . '.' . $a;
2081
                        },
2082
                        preg_split('/,/', $fields))
2083
                );
2084
        }
2085
        return $rv;
2086
    }
2087
 
2088
    /**
2089
     * Does this driver support tool_replace?
2090
     *
2091
     * @since Moodle 2.6.1
2092
     * @return bool
2093
     */
2094
    public function replace_all_text_supported() {
2095
        return true;
2096
    }
2097
 
2098
    public function session_lock_supported() {
2099
        return true;
2100
    }
2101
 
2102
    /**
2103
     * Obtain session lock
2104
     * @param int $rowid id of the row with session record
2105
     * @param int $timeout max allowed time to wait for the lock in seconds
2106
     * @return void
2107
     */
2108
    public function get_session_lock($rowid, $timeout) {
2109
        parent::get_session_lock($rowid, $timeout);
2110
 
2111
        $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
2112
        $sql = "SELECT GET_LOCK('$fullname', $timeout)";
2113
        $this->query_start($sql, null, SQL_QUERY_AUX);
2114
        $result = $this->mysqli->query($sql);
2115
        $this->query_end($result);
2116
 
2117
        if ($result) {
2118
            $arr = $result->fetch_assoc();
2119
            $result->close();
2120
 
2121
            if (reset($arr) == 1) {
2122
                return;
2123
            } else {
2124
                throw new dml_sessionwait_exception();
2125
            }
2126
        }
2127
    }
2128
 
2129
    public function release_session_lock($rowid) {
2130
        if (!$this->used_for_db_sessions) {
2131
            return;
2132
        }
2133
 
2134
        parent::release_session_lock($rowid);
2135
        $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
2136
        $sql = "SELECT RELEASE_LOCK('$fullname')";
2137
        $this->query_start($sql, null, SQL_QUERY_AUX);
2138
        $result = $this->mysqli->query($sql);
2139
        $this->query_end($result);
2140
 
2141
        if ($result) {
2142
            $result->close();
2143
        }
2144
    }
2145
 
2146
    /**
2147
     * Are transactions supported?
2148
     * It is not responsible to run productions servers
2149
     * on databases without transaction support ;-)
2150
     *
2151
     * MyISAM does not support support transactions.
2152
     *
2153
     * You can override this via the dbtransactions option.
2154
     *
2155
     * @return bool
2156
     */
2157
    protected function transactions_supported() {
2158
        if (!is_null($this->transactions_supported)) {
2159
            return $this->transactions_supported;
2160
        }
2161
 
2162
        // this is all just guessing, might be better to just specify it in config.php
2163
        if (isset($this->dboptions['dbtransactions'])) {
2164
            $this->transactions_supported = $this->dboptions['dbtransactions'];
2165
            return $this->transactions_supported;
2166
        }
2167
 
2168
        $this->transactions_supported = false;
2169
 
2170
        $engine = $this->get_dbengine();
2171
 
2172
        // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...)
2173
        if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) {
2174
            $this->transactions_supported = true;
2175
        }
2176
 
2177
        return $this->transactions_supported;
2178
    }
2179
 
2180
    /**
2181
     * Driver specific start of real database transaction,
2182
     * this can not be used directly in code.
2183
     * @return void
2184
     */
2185
    protected function begin_transaction() {
2186
        if (!$this->transactions_supported()) {
2187
            return;
2188
        }
2189
 
2190
        $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
2191
        $this->query_start($sql, null, SQL_QUERY_AUX);
2192
        $result = $this->mysqli->query($sql);
2193
        $this->query_end($result);
2194
 
2195
        $sql = "START TRANSACTION";
2196
        $this->query_start($sql, null, SQL_QUERY_AUX);
2197
        $result = $this->mysqli->query($sql);
2198
        $this->query_end($result);
2199
    }
2200
 
2201
    /**
2202
     * Driver specific commit of real database transaction,
2203
     * this can not be used directly in code.
2204
     * @return void
2205
     */
2206
    protected function commit_transaction() {
2207
        if (!$this->transactions_supported()) {
2208
            return;
2209
        }
2210
 
2211
        $sql = "COMMIT";
2212
        $this->query_start($sql, null, SQL_QUERY_AUX);
2213
        $result = $this->mysqli->query($sql);
2214
        $this->query_end($result);
2215
    }
2216
 
2217
    /**
2218
     * Driver specific abort of real database transaction,
2219
     * this can not be used directly in code.
2220
     * @return void
2221
     */
2222
    protected function rollback_transaction() {
2223
        if (!$this->transactions_supported()) {
2224
            return;
2225
        }
2226
 
2227
        $sql = "ROLLBACK";
2228
        $this->query_start($sql, null, SQL_QUERY_AUX);
2229
        $result = $this->mysqli->query($sql);
2230
        $this->query_end($result);
2231
 
2232
        return true;
2233
    }
2234
 
2235
    /**
2236
     * Converts a table to either 'Compressed' or 'Dynamic' row format.
2237
     *
2238
     * @param string $tablename Name of the table to convert to the new row format.
2239
     */
2240
    public function convert_table_row_format($tablename) {
2241
        $currentrowformat = $this->get_row_format($tablename);
2242
        if ($currentrowformat == 'Compact' || $currentrowformat == 'Redundant') {
2243
            $rowformat = ($this->is_compressed_row_format_supported(false)) ? "ROW_FORMAT=Compressed" : "ROW_FORMAT=Dynamic";
2244
            $prefix = $this->get_prefix();
2245
            $this->change_database_structure("ALTER TABLE {$prefix}$tablename $rowformat");
2246
        }
2247
    }
2248
 
2249
    /**
2250
     * Does this mysql instance support fulltext indexes?
2251
     *
2252
     * @return bool
2253
     */
2254
    public function is_fulltext_search_supported() {
2255
        $info = $this->get_server_info();
2256
 
2257
        if (version_compare($info['version'], '5.6.4', '>=')) {
2258
            return true;
2259
        }
2260
        return false;
2261
    }
2262
 
2263
    /**
2264
     * Fixes any table names that clash with reserved words.
2265
     *
2266
     * @param string $tablename The table name
2267
     * @return string The fixed table name
2268
     */
2269
    protected function fix_table_name($tablename) {
2270
        $prefixedtablename = parent::fix_table_name($tablename);
2271
        // This function quotes the table name if it matches one of the MySQL reserved
2272
        // words, e.g. groups.
2273
        return $this->get_manager()->generator->getEncQuoted($prefixedtablename);
2274
    }
2275
}