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 2 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 sqlsrv class representing moodle database interface.
19
 *
20
 * @package    core_dml
21
 * @copyright  2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
22
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v2 or later
23
 */
24
 
25
defined('MOODLE_INTERNAL') || die();
26
 
27
require_once(__DIR__.'/moodle_database.php');
28
require_once(__DIR__.'/sqlsrv_native_moodle_recordset.php');
29
require_once(__DIR__.'/sqlsrv_native_moodle_temptables.php');
30
 
31
/**
32
 * Native sqlsrv class representing moodle database interface.
33
 *
34
 * @package    core_dml
35
 * @copyright  2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
36
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v2 or later
37
 */
38
class sqlsrv_native_moodle_database extends moodle_database {
39
 
40
    protected $sqlsrv = null;
41
    protected $last_error_reporting; // To handle SQL*Server-Native driver default verbosity
42
    protected $temptables; // Control existing temptables (sqlsrv_moodle_temptables object)
43
    protected $collation;  // current DB collation cache
44
    /**
45
     * Does the used db version support ANSI way of limiting (2012 and higher)
46
     * @var bool
47
     */
48
    protected $supportsoffsetfetch;
49
 
50
    /** @var array list of open recordsets */
51
    protected $recordsets = array();
52
 
53
    /** @var array list of reserve words in MSSQL / Transact from http://msdn2.microsoft.com/en-us/library/ms189822.aspx */
54
    protected $reservewords = [
55
        "add", "all", "alter", "and", "any", "as", "asc", "authorization", "avg", "backup", "begin", "between", "break",
56
        "browse", "bulk", "by", "cascade", "case", "check", "checkpoint", "close", "clustered", "coalesce", "collate", "column",
57
        "commit", "committed", "compute", "confirm", "constraint", "contains", "containstable", "continue", "controlrow",
58
        "convert", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user",
59
        "cursor", "database", "dbcc", "deallocate", "declare", "default", "delete", "deny", "desc", "disk", "distinct",
60
        "distributed", "double", "drop", "dummy", "dump", "else", "end", "errlvl", "errorexit", "escape", "except", "exec",
61
        "execute", "exists", "exit", "external", "fetch", "file", "fillfactor", "floppy", "for", "foreign", "freetext",
62
        "freetexttable", "from", "full", "function", "goto", "grant", "group", "having", "holdlock", "identity",
63
        "identity_insert", "identitycol", "if", "in", "index", "inner", "insert", "intersect", "into", "is", "isolation",
64
        "join", "key", "kill", "left", "level", "like", "lineno", "load", "max", "merge", "min", "mirrorexit", "national",
65
        "nocheck", "nonclustered", "not", "null", "nullif", "of", "off", "offsets", "on", "once", "only", "open",
66
        "opendatasource", "openquery", "openrowset", "openxml", "option", "or", "order", "outer", "over", "percent", "perm",
67
        "permanent", "pipe", "pivot", "plan", "precision", "prepare", "primary", "print", "privileges", "proc", "procedure",
68
        "processexit", "public", "raiserror", "read", "readtext", "reconfigure", "references", "repeatable", "replication",
69
        "restore", "restrict", "return", "revert", "revoke", "right", "rollback", "rowcount", "rowguidcol", "rule", "save",
70
        "schema", "securityaudit", "select", "semantickeyphrasetable", "semanticsimilaritydetailstable",
71
        "semanticsimilaritytable", "serializable", "session_user", "set", "setuser", "shutdown", "some", "statistics", "sum",
72
        "system_user", "table", "tablesample", "tape", "temp", "temporary", "textsize", "then", "to", "top", "tran",
73
        "transaction", "trigger", "truncate", "try_convert", "tsequal", "uncommitted", "union", "unique", "unpivot", "update",
74
        "updatetext", "use", "user", "values", "varying", "view", "waitfor", "when", "where", "while", "with", "within group",
75
        "work", "writetext"
76
    ];
77
 
78
    /**
79
     * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
80
     *              note this has effect to decide if prefix checks must be performed or no
81
     * @param bool true means external database used
82
     */
83
    public function __construct($external=false) {
84
        parent::__construct($external);
85
    }
86
 
87
    /**
88
     * Detects if all needed PHP stuff installed.
89
     * Note: can be used before connect()
90
     * @return mixed true if ok, string if something
91
     */
92
    public function driver_installed() {
93
        // use 'function_exists()' rather than 'extension_loaded()' because
94
        // the name used by 'extension_loaded()' is case specific! The extension
95
        // therefore *could be* mixed case and hence not found.
96
        if (!function_exists('sqlsrv_num_rows')) {
97
            return get_string('nativesqlsrvnodriver', 'install');
98
        }
99
        return true;
100
    }
101
 
102
    /**
103
     * Returns database family type - describes SQL dialect
104
     * Note: can be used before connect()
105
     * @return string db family name (mysql, postgres, mssql, sqlsrv, oracle, etc.)
106
     */
107
    public function get_dbfamily() {
108
        return 'mssql';
109
    }
110
 
111
    /**
112
     * Returns more specific database driver type
113
     * Note: can be used before connect()
114
     * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
115
     */
116
    protected function get_dbtype() {
117
        return 'sqlsrv';
118
    }
119
 
120
    /**
121
     * Returns general database library name
122
     * Note: can be used before connect()
123
     * @return string db type pdo, native
124
     */
125
    protected function get_dblibrary() {
126
        return 'native';
127
    }
128
 
129
    /**
130
     * Returns localised database type name
131
     * Note: can be used before connect()
132
     * @return string
133
     */
134
    public function get_name() {
135
        return get_string('nativesqlsrv', 'install');
136
    }
137
 
138
    /**
139
     * Returns localised database configuration help.
140
     * Note: can be used before connect()
141
     * @return string
142
     */
143
    public function get_configuration_help() {
144
        return get_string('nativesqlsrvhelp', 'install');
145
    }
146
 
147
    /**
148
     * Diagnose database and tables, this function is used
149
     * to verify database and driver settings, db engine types, etc.
150
     *
151
     * @return string null means everything ok, string means problem found.
152
     */
153
    public function diagnose() {
154
        // Verify the database is running with READ_COMMITTED_SNAPSHOT enabled.
155
        // (that's required to get snapshots/row versioning on READ_COMMITED mode).
156
        $correctrcsmode = false;
157
        $sql = "SELECT is_read_committed_snapshot_on
158
                  FROM sys.databases
159
                 WHERE name = '{$this->dbname}'";
160
        $this->query_start($sql, null, SQL_QUERY_AUX);
161
        $result = sqlsrv_query($this->sqlsrv, $sql);
162
        $this->query_end($result);
163
        if ($result) {
164
            if ($row = sqlsrv_fetch_array($result)) {
165
                $correctrcsmode = (bool)reset($row);
166
            }
167
        }
168
        $this->free_result($result);
169
 
170
        if (!$correctrcsmode) {
171
            return get_string('mssqlrcsmodemissing', 'error');
172
        }
173
 
174
        // Arrived here, all right.
175
        return null;
176
    }
177
 
178
    /**
179
     * Connect to db
180
     * Must be called before most other methods. (you can call methods that return connection configuration parameters)
181
     * @param string $dbhost The database host.
182
     * @param string $dbuser The database username.
183
     * @param string $dbpass The database username's password.
184
     * @param string $dbname The name of the database being connected to.
185
     * @param mixed $prefix string|bool The moodle db table name's prefix. false is used for external databases where prefix not used
186
     * @param array $dboptions driver specific options
187
     * @return bool true
188
     * @throws dml_connection_exception if error
189
     */
190
    public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
191
        if ($prefix == '' and !$this->external) {
192
            // Enforce prefixes for everybody but mysql.
193
            throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
194
        }
195
 
196
        $driverstatus = $this->driver_installed();
197
 
198
        if ($driverstatus !== true) {
199
            throw new dml_exception('dbdriverproblem', $driverstatus);
200
        }
201
 
202
        /*
203
         * Log all Errors.
204
         */
205
        sqlsrv_configure("WarningsReturnAsErrors", FALSE);
206
        sqlsrv_configure("LogSubsystems", SQLSRV_LOG_SYSTEM_OFF);
207
        sqlsrv_configure("LogSeverity", SQLSRV_LOG_SEVERITY_ERROR);
208
 
209
        $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
210
 
211
        $options = [
212
            'UID' => $this->dbuser,
213
            'PWD' => $this->dbpass,
214
            'Database' => $this->dbname,
215
            'CharacterSet' => 'UTF-8',
216
            'MultipleActiveResultSets' => true,
217
            'ConnectionPooling' => !empty($this->dboptions['dbpersist']),
218
            'ReturnDatesAsStrings' => true,
219
        ];
220
 
221
        $dbhost = $this->dbhost;
222
        if (!empty($dboptions['dbport'])) {
223
            $dbhost .= ',' . $dboptions['dbport'];
224
        }
225
 
226
        // The sqlsrv_connect() has a lot of connection options to be used.
227
        // Users can add any supported options with the 'extrainfo' key in the dboptions.
228
        if (isset($this->dboptions['extrainfo'])) {
229
            $options = array_merge($options, $this->dboptions['extrainfo']);
230
        }
231
 
232
        $this->sqlsrv = sqlsrv_connect($dbhost, $options);
233
 
234
        if ($this->sqlsrv === false) {
235
            $this->sqlsrv = null;
236
            $dberr = $this->get_last_error();
237
 
238
            throw new dml_connection_exception($dberr);
239
        }
240
 
241
        // Disable logging until we are fully setup.
242
        $this->query_log_prevent();
243
 
244
        // Allow quoted identifiers
245
        $sql = "SET QUOTED_IDENTIFIER ON";
246
        $this->query_start($sql, null, SQL_QUERY_AUX);
247
        $result = sqlsrv_query($this->sqlsrv, $sql);
248
        $this->query_end($result);
249
 
250
        $this->free_result($result);
251
 
252
        // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
253
        // instead of equal(=) and distinct(<>) symbols
254
        $sql = "SET ANSI_NULLS ON";
255
        $this->query_start($sql, null, SQL_QUERY_AUX);
256
        $result = sqlsrv_query($this->sqlsrv, $sql);
257
        $this->query_end($result);
258
 
259
        $this->free_result($result);
260
 
261
        // Force ANSI warnings so arithmetic/string overflows will be
262
        // returning error instead of transparently truncating data
263
        $sql = "SET ANSI_WARNINGS ON";
264
        $this->query_start($sql, null, SQL_QUERY_AUX);
265
        $result = sqlsrv_query($this->sqlsrv, $sql);
266
        $this->query_end($result);
267
 
268
        // Concatenating null with anything MUST return NULL
269
        $sql = "SET CONCAT_NULL_YIELDS_NULL  ON";
270
        $this->query_start($sql, null, SQL_QUERY_AUX);
271
        $result = sqlsrv_query($this->sqlsrv, $sql);
272
        $this->query_end($result);
273
 
274
        $this->free_result($result);
275
 
276
        // Set transactions isolation level to READ_COMMITTED
277
        // prevents dirty reads when using transactions +
278
        // is the default isolation level of sqlsrv
279
        $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
280
        $this->query_start($sql, NULL, SQL_QUERY_AUX);
281
        $result = sqlsrv_query($this->sqlsrv, $sql);
282
        $this->query_end($result);
283
 
284
        $this->free_result($result);
285
 
286
        $serverinfo = $this->get_server_info();
287
        // Fetch/offset is supported staring from SQL Server 2012.
288
        $this->supportsoffsetfetch = $serverinfo['version'] > '11';
289
 
290
        // We can enable logging now.
291
        $this->query_log_allow();
292
 
293
        // Connection established and configured, going to instantiate the temptables controller
294
        $this->temptables = new sqlsrv_native_moodle_temptables($this);
295
 
296
        return true;
297
    }
298
 
299
    /**
300
     * Close database connection and release all resources
301
     * and memory (especially circular memory references).
302
     * Do NOT use connect() again, create a new instance if needed.
303
     */
304
    public function dispose() {
305
        parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
306
 
307
        if ($this->sqlsrv) {
308
            sqlsrv_close($this->sqlsrv);
309
            $this->sqlsrv = null;
310
        }
311
    }
312
 
313
    /**
314
     * Called before each db query.
315
     * @param string $sql
316
     * @param array|null $params An array of parameters.
317
     * @param int $type type of query
318
     * @param mixed $extrainfo driver specific extra information
319
     * @return void
320
     */
321
    protected function query_start($sql, ?array $params, $type, $extrainfo = null) {
322
        parent::query_start($sql, $params, $type, $extrainfo);
323
    }
324
 
325
    /**
326
     * Called immediately after each db query.
327
     * @param mixed db specific result
328
     * @return void
329
     */
330
    protected function query_end($result) {
331
        parent::query_end($result);
332
    }
333
 
334
    /**
335
     * Returns database server info array
336
     * @return array Array containing 'description', 'version' and 'database' (current db) info
337
     */
338
    public function get_server_info() {
339
        static $info;
340
 
341
        if (!$info) {
342
            $server_info = sqlsrv_server_info($this->sqlsrv);
343
 
344
            if ($server_info) {
345
                $info['description'] = $server_info['SQLServerName'];
346
                $info['version'] = $server_info['SQLServerVersion'];
347
                $info['database'] = $server_info['CurrentDatabase'];
348
            }
349
        }
350
        return $info;
351
    }
352
 
353
    /**
354
     * Override: Converts short table name {tablename} to real table name
355
     * supporting temp tables (#) if detected
356
     *
357
     * @param string sql
358
     * @return string sql
359
     */
360
    protected function fix_table_names($sql) {
361
        if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/i', $sql, $matches)) {
362
            foreach ($matches[0] as $key => $match) {
363
                $name = $matches[1][$key];
364
 
365
                if ($this->temptables->is_temptable($name)) {
366
                    $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql);
367
                } else {
368
                    $sql = str_replace($match, $this->prefix.$name, $sql);
369
                }
370
            }
371
        }
372
        return $sql;
373
    }
374
 
375
    /**
376
     * Returns supported query parameter types
377
     * @return int bitmask
378
     */
379
    protected function allowed_param_types() {
380
        return SQL_PARAMS_QM;  // sqlsrv 1.1 can bind
381
    }
382
 
383
    /**
384
     * Returns last error reported by database engine.
385
     * @return string error message
386
     */
387
    public function get_last_error() {
388
        $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
389
        $errorMessage = 'No errors found';
390
 
391
        if ($retErrors != null) {
392
            $errorMessage = '';
393
 
394
            foreach ($retErrors as $arrError) {
395
                $errorMessage .= "SQLState: ".$arrError['SQLSTATE']."<br>\n";
396
                $errorMessage .= "Error Code: ".$arrError['code']."<br>\n";
397
                $errorMessage .= "Message: ".$arrError['message']."<br>\n";
398
            }
399
        }
400
 
401
        return $errorMessage;
402
    }
403
 
404
    /**
405
     * Prepare the query binding and do the actual query.
406
     *
407
     * @param string $sql The sql statement
408
     * @param array $params array of params for binding. If NULL, they are ignored.
409
     * @param int $sql_query_type - Type of operation
410
     * @param bool $free_result - Default true, transaction query will be freed.
411
     * @param bool $scrollable - Default false, to use for quickly seeking to target records
412
     * @return resource|bool result
413
     */
414
    private function do_query($sql, $params, $sql_query_type, $free_result = true, $scrollable = false) {
415
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
416
 
417
        /*
418
         * Bound variables *are* supported. Until I can get it to work, emulate the bindings
419
         * The challenge/problem/bug is that although they work, doing a SELECT SCOPE_IDENTITY()
420
         * doesn't return a value (no result set)
421
         *
422
         * -- somebody from MS
423
         */
424
 
425
        $sql = $this->emulate_bound_params($sql, $params);
426
        $this->query_start($sql, $params, $sql_query_type);
427
        if (!$scrollable) { // Only supporting next row
428
            $result = sqlsrv_query($this->sqlsrv, $sql);
429
        } else { // Supporting absolute/relative rows
430
            $result = sqlsrv_query($this->sqlsrv, $sql, array(), array('Scrollable' => SQLSRV_CURSOR_STATIC));
431
        }
432
 
433
        if ($result === false) {
434
            // TODO do something with error or just use if DEV or DEBUG?
435
            $dberr = $this->get_last_error();
436
        }
437
 
438
        $this->query_end($result);
439
 
440
        if ($free_result) {
441
            $this->free_result($result);
442
            return true;
443
        }
444
        return $result;
445
    }
446
 
447
    /**
448
     * Return tables in database WITHOUT current prefix.
449
     * @param bool $usecache if true, returns list of cached tables.
450
     * @return array of table names in lowercase and without prefix
451
     */
452
    public function get_tables($usecache = true) {
453
        if ($usecache and $this->tables !== null) {
454
            return $this->tables;
455
        }
456
        $this->tables = array ();
457
        $prefix = str_replace('_', '\\_', $this->prefix);
458
        $sql = "SELECT table_name
459
                  FROM INFORMATION_SCHEMA.TABLES
460
                 WHERE table_name LIKE '$prefix%' ESCAPE '\\' AND table_type = 'BASE TABLE'";
461
 
462
        $this->query_start($sql, null, SQL_QUERY_AUX);
463
        $result = sqlsrv_query($this->sqlsrv, $sql);
464
        $this->query_end($result);
465
 
466
        if ($result) {
467
            while ($row = sqlsrv_fetch_array($result)) {
468
                $tablename = reset($row);
469
                if ($this->prefix !== false && $this->prefix !== '') {
470
                    if (strpos($tablename, $this->prefix) !== 0) {
471
                        continue;
472
                    }
473
                    $tablename = substr($tablename, strlen($this->prefix));
474
                }
475
                $this->tables[$tablename] = $tablename;
476
            }
477
            $this->free_result($result);
478
        }
479
 
480
        // Add the currently available temptables
481
        $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
482
        return $this->tables;
483
    }
484
 
485
    /**
486
     * Return table indexes - everything lowercased.
487
     * @param string $table The table we want to get indexes from.
488
     * @return array of arrays
489
     */
490
    public function get_indexes($table) {
491
        $indexes = array ();
492
        $tablename = $this->prefix.$table;
493
 
494
        // Indexes aren't covered by information_schema metatables, so we need to
495
        // go to sys ones. Skipping primary key indexes on purpose.
496
        $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name
497
                  FROM sys.indexes i
498
                  JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
499
                  JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
500
                  JOIN sys.tables t ON i.object_id = t.object_id
501
                 WHERE t.name = '$tablename' AND i.is_primary_key = 0
502
              ORDER BY i.name, i.index_id, ic.index_column_id";
503
 
504
        $this->query_start($sql, null, SQL_QUERY_AUX);
505
        $result = sqlsrv_query($this->sqlsrv, $sql);
506
        $this->query_end($result);
507
 
508
        if ($result) {
509
            $lastindex = '';
510
            $unique = false;
511
            $columns = array ();
512
 
513
            while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
514
                if ($lastindex and $lastindex != $row['index_name'])
515
                    { // Save lastindex to $indexes and reset info
516
                    $indexes[$lastindex] = array
517
                     (
518
                      'unique' => $unique,
519
                      'columns' => $columns
520
                     );
521
 
522
                    $unique = false;
523
                    $columns = array ();
524
                }
525
                $lastindex = $row['index_name'];
526
                $unique = empty($row['is_unique']) ? false : true;
527
                $columns[] = $row['column_name'];
528
            }
529
 
530
            if ($lastindex) { // Add the last one if exists
531
                $indexes[$lastindex] = array
532
                 (
533
                  'unique' => $unique,
534
                  'columns' => $columns
535
                 );
536
            }
537
 
538
            $this->free_result($result);
539
        }
540
        return $indexes;
541
    }
542
 
543
    /**
544
     * Returns detailed information about columns in table.
545
     *
546
     * @param string $table name
547
     * @return array array of database_column_info objects indexed with column names
548
     */
549
    protected function fetch_columns(string $table): array {
550
        $structure = array();
551
 
552
        if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema
553
            $sql = "SELECT column_name AS name,
554
                           data_type AS type,
555
                           numeric_precision AS max_length,
556
                           character_maximum_length AS char_max_length,
557
                           numeric_scale AS scale,
558
                           is_nullable AS is_nullable,
559
                           columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
560
                           column_default AS default_value
561
                      FROM INFORMATION_SCHEMA.COLUMNS
562
                     WHERE table_name = '{".$table."}'
563
                  ORDER BY ordinal_position";
564
        } else { // temp table, get metadata from tempdb schema
565
            $sql = "SELECT column_name AS name,
566
                           data_type AS type,
567
                           numeric_precision AS max_length,
568
                           character_maximum_length AS char_max_length,
569
                           numeric_scale AS scale,
570
                           is_nullable AS is_nullable,
571
                           columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
572
                           column_default AS default_value
573
                      FROM tempdb.INFORMATION_SCHEMA.COLUMNS ".
574
            // check this statement
575
            // JOIN tempdb..sysobjects ON name = table_name
576
            // WHERE id = object_id('tempdb..{".$table."}')
577
                    "WHERE table_name LIKE '{".$table."}__________%'
578
                  ORDER BY ordinal_position";
579
        }
580
 
581
        list($sql, $params, $type) = $this->fix_sql_params($sql, null);
582
 
583
        $this->query_start($sql, null, SQL_QUERY_AUX);
584
        $result = sqlsrv_query($this->sqlsrv, $sql);
585
        $this->query_end($result);
586
 
587
        if (!$result) {
588
            return array ();
589
        }
590
 
591
        while ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
592
 
593
            $rawcolumn = (object)$rawcolumn;
594
 
595
            $info = new stdClass();
596
            $info->name = $rawcolumn->name;
597
            $info->type = $rawcolumn->type;
598
            $info->meta_type = $this->sqlsrvtype2moodletype($info->type);
599
 
600
            // Prepare auto_increment info
601
            $info->auto_increment = $rawcolumn->auto_increment ? true : false;
602
 
603
            // Define type for auto_increment columns
604
            $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type;
605
 
606
            // id columns being auto_incremnt are PK by definition
607
            $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment);
608
 
609
            if ($info->meta_type === 'C' and $rawcolumn->char_max_length == -1) {
610
                // This is NVARCHAR(MAX), not a normal NVARCHAR.
611
                $info->max_length = -1;
612
                $info->meta_type = 'X';
613
            } else {
614
                // Put correct length for character and LOB types
615
                $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length;
616
                $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length;
617
            }
618
 
619
            // Scale
620
            $info->scale = $rawcolumn->scale;
621
 
622
            // Prepare not_null info
623
            $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false;
624
 
625
            // Process defaults
626
            $info->has_default = !empty($rawcolumn->default_value);
627
            if ($rawcolumn->default_value === NULL) {
628
                $info->default_value = NULL;
629
            } else {
630
                $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value);
631
            }
632
 
633
            // Process binary
634
            $info->binary = $info->meta_type == 'B' ? true : false;
635
 
636
            $structure[$info->name] = new database_column_info($info);
637
        }
638
        $this->free_result($result);
639
 
640
        return $structure;
641
    }
642
 
643
    /**
644
     * Normalise values based in RDBMS dependencies (booleans, LOBs...)
645
     *
646
     * @param database_column_info $column column metadata corresponding with the value we are going to normalise
647
     * @param mixed $value value we are going to normalise
648
     * @return mixed the normalised value
649
     */
650
    protected function normalise_value($column, $value) {
651
        $this->detect_objects($value);
652
 
653
        if (is_bool($value)) {                               // Always, convert boolean to int
654
            $value = (int)$value;
655
        }                                                    // And continue processing because text columns with numeric info need special handling below
656
 
657
        if ($column->meta_type == 'B')
658
            { // BLOBs need to be properly "packed", but can be inserted directly if so.
659
            if (!is_null($value)) {               // If value not null, unpack it to unquoted hexadecimal byte-string format
660
                $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it
661
            }                                                // easily and "bind" the param ok.
662
 
663
        } else if ($column->meta_type == 'X') {              // sqlsrv doesn't cast from int to text, so if text column
664
            if (is_numeric($value)) { // and is numeric value then cast to string
665
                $value = array('numstr' => (string)$value);  // and put into array, so emulate_bound_params() will know how
666
            }                                                // to "bind" the param ok, avoiding reverse conversion to number
667
        } else if ($value === '') {
668
 
669
            if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
670
                $value = 0; // prevent '' problems in numeric fields
671
            }
672
        }
673
        return $value;
674
    }
675
 
676
    /**
677
     * Selectively call sqlsrv_free_stmt(), avoiding some warnings without using the horrible @
678
     *
679
     * @param sqlsrv_resource $resource resource to be freed if possible
680
     * @return bool
681
     */
682
    private function free_result($resource) {
683
        if (!is_bool($resource) && is_resource($resource)) {
684
            // We need to make sure that the statement resource is in the correct type before freeing it.
685
            return sqlsrv_free_stmt($resource);
686
        }
687
        return false;
688
    }
689
 
690
    /**
691
     * Provides mapping between sqlsrv native data types and moodle_database - database_column_info - ones)
692
     *
693
     * @param string $sqlsrv_type native sqlsrv data type
694
     * @return string 1-char database_column_info data type
695
     */
696
    private function sqlsrvtype2moodletype($sqlsrv_type) {
697
        $type = null;
698
 
699
        switch (strtoupper($sqlsrv_type)) {
700
          case 'BIT':
701
           $type = 'L';
702
           break;
703
 
704
          case 'INT':
705
          case 'SMALLINT':
706
          case 'INTEGER':
707
          case 'BIGINT':
708
           $type = 'I';
709
           break;
710
 
711
          case 'DECIMAL':
712
          case 'REAL':
713
          case 'FLOAT':
714
           $type = 'N';
715
           break;
716
 
717
          case 'VARCHAR':
718
          case 'NVARCHAR':
719
           $type = 'C';
720
           break;
721
 
722
          case 'TEXT':
723
          case 'NTEXT':
724
          case 'VARCHAR(MAX)':
725
          case 'NVARCHAR(MAX)':
726
           $type = 'X';
727
           break;
728
 
729
          case 'IMAGE':
730
          case 'VARBINARY':
731
          case 'VARBINARY(MAX)':
732
           $type = 'B';
733
           break;
734
 
735
          case 'DATETIME':
736
           $type = 'D';
737
           break;
738
         }
739
 
740
        if (!$type) {
741
            throw new dml_exception('invalidsqlsrvnativetype', $sqlsrv_type);
742
        }
743
        return $type;
744
    }
745
 
746
    /**
747
     * Do NOT use in code, to be used by database_manager only!
748
     * @param string|array $sql query
749
     * @param array|null $tablenames an array of xmldb table names affected by this request.
750
     * @return bool true
751
     * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
752
     */
753
    public function change_database_structure($sql, $tablenames = null) {
754
        $this->get_manager(); // Includes DDL exceptions classes ;-)
755
        $sqls = (array)$sql;
756
 
757
        try {
758
            foreach ($sqls as $sql) {
759
                $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
760
                $result = sqlsrv_query($this->sqlsrv, $sql);
761
                $this->query_end($result);
762
            }
763
        } catch (ddl_change_structure_exception $e) {
764
            $this->reset_caches($tablenames);
765
            throw $e;
766
        }
767
 
768
        $this->reset_caches($tablenames);
769
        return true;
770
    }
771
 
772
    /**
773
     * Prepare the array of params for native binding
774
     */
775
    protected function build_native_bound_params(array $params = null) {
776
 
777
        return null;
778
    }
779
 
780
    /**
781
     * Workaround for SQL*Server Native driver similar to MSSQL driver for
782
     * consistent behavior.
783
     */
784
    protected function emulate_bound_params($sql, array $params = null) {
785
 
786
        if (empty($params)) {
787
            return $sql;
788
        }
789
        // ok, we have verified sql statement with ? and correct number of params
790
        $parts = array_reverse(explode('?', $sql));
791
        $return = array_pop($parts);
792
        foreach ($params as $param) {
793
            if (is_bool($param)) {
794
                $return .= (int)$param;
795
            } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially
796
                $return .= '0x'.$param['hex'];
797
            } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not*
798
                $return .= "N'{$param['numstr']}'";                   // be converted back to number params, but bound as strings
799
            } else if (is_null($param)) {
800
                $return .= 'NULL';
801
 
802
            } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
803
                $return .= "'$param'"; // this is a hack for MDL-23997, we intentionally use string because it is compatible with both nvarchar and int types
804
            } else if (is_float($param)) {
805
                $return .= $param;
806
            } else {
807
                $param = str_replace("'", "''", $param);
808
                $param = str_replace("\0", "", $param);
809
                $return .= "N'$param'";
810
            }
811
 
812
            $return .= array_pop($parts);
813
        }
814
        return $return;
815
    }
816
 
817
    /**
818
     * Execute general sql query. Should be used only when no other method suitable.
819
     * Do NOT use this to make changes in db structure, use database_manager methods instead!
820
     * @param string $sql query
821
     * @param array $params query parameters
822
     * @return bool true
823
     * @throws dml_exception A DML specific exception is thrown for any errors.
824
     */
825
    public function execute($sql, array $params = null) {
826
        if (strpos($sql, ';') !== false) {
827
            throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
828
        }
829
        $this->do_query($sql, $params, SQL_QUERY_UPDATE);
830
        return true;
831
    }
832
 
833
    /**
834
     * Whether the given SQL statement has the ORDER BY clause in the main query.
835
     *
836
     * @param string $sql the SQL statement
837
     * @return bool true if the main query has the ORDER BY clause; otherwise, false.
838
     */
839
    protected static function has_query_order_by(string $sql) {
840
        $sqltoupper = strtoupper($sql);
841
        // Fail fast if there is no ORDER BY clause in the original query.
842
        if (strpos($sqltoupper, 'ORDER BY') === false) {
843
            return false;
844
        }
845
 
846
        // Search for an ORDER BY clause in the main query, not in any subquery (not always allowed in MSSQL)
847
        // or in clauses like OVER with a window function e.g. ROW_NUMBER() OVER (ORDER BY ...) or RANK() OVER (ORDER BY ...):
848
        // use PHP PCRE recursive patterns to remove everything found within round brackets.
849
        $mainquery = preg_replace('/\(((?>[^()]+)|(?R))*\)/', '()', $sqltoupper);
850
        if (strpos($mainquery, 'ORDER BY') !== false) {
851
            return true;
852
        }
853
 
854
        return false;
855
    }
856
 
857
    /**
858
     * Get a number of records as a moodle_recordset using a SQL statement.
859
     *
860
     * Since this method is a little less readable, use of it should be restricted to
861
     * code where it's possible there might be large datasets being returned.  For known
862
     * small datasets use get_records_sql - it leads to simpler code.
863
     *
864
     * The return type is like:
865
     * @see function get_recordset.
866
     *
867
     * @param string $sql the SQL select query to execute.
868
     * @param array $params array of sql parameters
869
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
870
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
871
     * @return moodle_recordset instance
872
     * @throws dml_exception A DML specific exception is thrown for any errors.
873
     */
874
    public function get_recordset_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
875
 
876
        list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
877
        $needscrollable = (bool)$limitfrom; // To determine if we'll need to perform scroll to $limitfrom.
878
 
879
        if ($limitfrom or $limitnum) {
880
            if (!$this->supportsoffsetfetch) {
881
                if ($limitnum >= 1) { // Only apply TOP clause if we have any limitnum (limitfrom offset is handled later).
882
                    $fetch = $limitfrom + $limitnum;
883
                    if (PHP_INT_MAX - $limitnum < $limitfrom) { // Check PHP_INT_MAX overflow.
884
                        $fetch = PHP_INT_MAX;
885
                    }
886
                    $sql = preg_replace('/^([\s(])*SELECT([\s]+(DISTINCT|ALL))?(?!\s*TOP\s*\()/i',
887
                                        "\\1SELECT\\2 TOP $fetch", $sql);
888
                }
889
            } else {
890
                $needscrollable = false; // Using supported fetch/offset, no need to scroll anymore.
891
                $sql = (substr($sql, -1) === ';') ? substr($sql, 0, -1) : $sql;
892
                // We need ORDER BY to use FETCH/OFFSET.
893
                // Ordering by first column shouldn't break anything if there was no order in the first place.
894
                if (!self::has_query_order_by($sql)) {
895
                    $sql .= " ORDER BY 1";
896
                }
897
 
898
                $sql .= " OFFSET ".$limitfrom." ROWS ";
899
 
900
                if ($limitnum > 0) {
901
                    $sql .= " FETCH NEXT ".$limitnum." ROWS ONLY";
902
                }
903
            }
904
        }
905
 
906
        // Add WITH (NOLOCK) to any temp tables.
907
        $sql = $this->add_no_lock_to_temp_tables($sql);
908
 
909
        $result = $this->do_query($sql, $params, SQL_QUERY_SELECT, false, $needscrollable);
910
 
911
        if ($needscrollable) { // Skip $limitfrom records.
912
            sqlsrv_fetch($result, SQLSRV_SCROLL_ABSOLUTE, $limitfrom - 1);
913
        }
914
        return $this->create_recordset($result);
915
    }
916
 
917
    /**
918
     * Use NOLOCK on any temp tables. Since it's a temp table and uncommitted reads are low risk anyway.
919
     *
920
     * @param string $sql the SQL select query to execute.
921
     * @return string The SQL, with WITH (NOLOCK) added to all temp tables
922
     */
923
    protected function add_no_lock_to_temp_tables($sql) {
924
        return preg_replace_callback('/(\{([a-z][a-z0-9_]*)\})(\s+(\w+))?/', function($matches) {
925
            $table = $matches[1]; // With the braces, so we can put it back in the query.
926
            $name = $matches[2]; // Without the braces, so we can check if it's a temptable.
927
            $tail = isset($matches[3]) ? $matches[3] : ''; // Catch the next word afterwards so that we can check if it's an alias.
928
            $replacement = $matches[0]; // The table and the word following it, so we can replace it back if no changes are needed.
929
 
930
            if ($this->temptables && $this->temptables->is_temptable($name)) {
931
                if (!empty($tail)) {
932
                    if (in_array(strtolower(trim($tail)), $this->reservewords)) {
933
                        // If the table is followed by a reserve word, it's not an alias so put the WITH (NOLOCK) in between.
934
                        return $table . ' WITH (NOLOCK)' . $tail;
935
                    }
936
                }
937
                // If the table is not followed by a reserve word, put the WITH (NOLOCK) after the whole match.
938
                return $replacement . ' WITH (NOLOCK)';
939
            } else {
940
                return $replacement;
941
            }
942
        }, $sql);
943
    }
944
 
945
    /**
946
     * Create a record set and initialize with first row
947
     *
948
     * @param mixed $result
949
     * @return sqlsrv_native_moodle_recordset
950
     */
951
    protected function create_recordset($result) {
952
        $rs = new sqlsrv_native_moodle_recordset($result, $this);
953
        $this->recordsets[] = $rs;
954
        return $rs;
955
    }
956
 
957
    /**
958
     * Do not use outside of recordset class.
959
     * @internal
960
     * @param sqlsrv_native_moodle_recordset $rs
961
     */
962
    public function recordset_closed(sqlsrv_native_moodle_recordset $rs) {
963
        if ($key = array_search($rs, $this->recordsets, true)) {
964
            unset($this->recordsets[$key]);
965
        }
966
    }
967
 
968
    /**
969
     * Get a number of records as an array of objects using a SQL statement.
970
     *
971
     * Return value is like:
972
     * @see function get_records.
973
     *
974
     * @param string $sql the SQL select query to execute. The first column of this SELECT statement
975
     *   must be a unique value (usually the 'id' field), as it will be used as the key of the
976
     *   returned array.
977
     * @param array $params array of sql parameters
978
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
979
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
980
     * @return array of objects, or empty array if no records were found
981
     * @throws dml_exception A DML specific exception is thrown for any errors.
982
     */
983
    public function get_records_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
984
 
985
        $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
986
 
987
        $results = array();
988
 
989
        foreach ($rs as $row) {
990
            $rowarray = (array)$row;
991
            $id = reset($rowarray);
992
 
993
            if (isset($results[$id])) {
994
                $colname = key($rowarray);
995
                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);
996
            }
997
            $results[$id] = (object)$row;
998
        }
999
        $rs->close();
1000
 
1001
        return $results;
1002
    }
1003
 
1004
    /**
1005
     * Selects records and return values (first field) as an array using a SQL statement.
1006
     *
1007
     * @param string $sql The SQL query
1008
     * @param array $params array of sql parameters
1009
     * @return array of values
1010
     * @throws dml_exception A DML specific exception is thrown for any errors.
1011
     */
1012
    public function get_fieldset_sql($sql, array $params = null) {
1013
 
1014
        $rs = $this->get_recordset_sql($sql, $params);
1015
 
1016
        $results = array ();
1017
 
1018
        foreach ($rs as $row) {
1019
            $rowarray = (array)$row;
1020
            $results[] = reset($rowarray);
1021
        }
1022
        $rs->close();
1023
 
1024
        return $results;
1025
    }
1026
 
1027
    /**
1028
     * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1029
     * @param string $table name
1030
     * @param mixed $params data record as object or array
1031
     * @param bool $returnit return it of inserted record
1032
     * @param bool $bulk true means repeated inserts expected
1033
     * @param bool $customsequence true if 'id' included in $params, disables $returnid
1034
     * @return bool|int true or new id
1035
     * @throws dml_exception A DML specific exception is thrown for any errors.
1036
     */
1037
    public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1038
        if (!is_array($params)) {
1039
            $params = (array)$params;
1040
        }
1041
 
1042
        $isidentity = false;
1043
 
1044
        if ($customsequence) {
1045
            if (!isset($params['id'])) {
1046
                throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1047
            }
1048
 
1049
            $returnid = false;
1050
            $columns = $this->get_columns($table);
1051
            if (isset($columns['id']) and $columns['id']->auto_increment) {
1052
                $isidentity = true;
1053
            }
1054
 
1055
            // Disable IDENTITY column before inserting record with id, only if the
1056
            // column is identity, from meta information.
1057
            if ($isidentity) {
1058
                $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
1059
                $this->do_query($sql, null, SQL_QUERY_AUX);
1060
            }
1061
 
1062
        } else {
1063
            unset($params['id']);
1064
        }
1065
 
1066
        if (empty($params)) {
1067
            throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1068
        }
1069
        $fields = implode(',', array_keys($params));
1070
        $qms = array_fill(0, count($params), '?');
1071
        $qms = implode(',', $qms);
1072
        $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms)";
1073
        $query_id = $this->do_query($sql, $params, SQL_QUERY_INSERT);
1074
 
1075
        if ($customsequence) {
1076
            // Enable IDENTITY column after inserting record with id, only if the
1077
            // column is identity, from meta information.
1078
            if ($isidentity) {
1079
                $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
1080
                $this->do_query($sql, null, SQL_QUERY_AUX);
1081
            }
1082
        }
1083
 
1084
        if ($returnid) {
1085
            $id = $this->sqlsrv_fetch_id();
1086
            return $id;
1087
        } else {
1088
            return true;
1089
        }
1090
    }
1091
 
1092
    /**
1093
     * Get the ID of the current action
1094
     *
1095
     * @return mixed ID
1096
     */
1097
    private function sqlsrv_fetch_id() {
1098
        $query_id = sqlsrv_query($this->sqlsrv, 'SELECT SCOPE_IDENTITY()');
1099
        if ($query_id === false) {
1100
            $dberr = $this->get_last_error();
1101
            return false;
1102
        }
1103
        $row = $this->sqlsrv_fetchrow($query_id);
1104
        return (int)$row[0];
1105
    }
1106
 
1107
    /**
1108
     * Fetch a single row into an numbered array
1109
     *
1110
     * @param mixed $query_id
1111
     */
1112
    private function sqlsrv_fetchrow($query_id) {
1113
        $row = sqlsrv_fetch_array($query_id, SQLSRV_FETCH_NUMERIC);
1114
        if ($row === false) {
1115
            $dberr = $this->get_last_error();
1116
            return false;
1117
        }
1118
 
1119
        foreach ($row as $key => $value) {
1120
            $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
1121
        }
1122
        return $row;
1123
    }
1124
 
1125
    /**
1126
     * Insert a record into a table and return the "id" field if required.
1127
     *
1128
     * Some conversions and safety checks are carried out. Lobs are supported.
1129
     * If the return ID isn't required, then this just reports success as true/false.
1130
     * $data is an object containing needed data
1131
     * @param string $table The database table to be inserted into
1132
     * @param object|array $dataobject A data object with values for one or more fields in the record
1133
     * @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.
1134
     * @return bool|int true or new id
1135
     * @throws dml_exception A DML specific exception is thrown for any errors.
1136
     */
1137
    public function insert_record($table, $dataobject, $returnid = true, $bulk = false) {
1138
        $dataobject = (array)$dataobject;
1139
 
1140
        $columns = $this->get_columns($table);
1141
        if (empty($columns)) {
1142
            throw new dml_exception('ddltablenotexist', $table);
1143
        }
1144
 
1145
        $cleaned = array ();
1146
 
1147
        foreach ($dataobject as $field => $value) {
1148
            if ($field === 'id') {
1149
                continue;
1150
            }
1151
            if (!isset($columns[$field])) {
1152
                continue;
1153
            }
1154
            $column = $columns[$field];
1155
            $cleaned[$field] = $this->normalise_value($column, $value);
1156
        }
1157
 
1158
        return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1159
    }
1160
 
1161
    /**
1162
     * Import a record into a table, id field is required.
1163
     * Safety checks are NOT carried out. Lobs are supported.
1164
     *
1165
     * @param string $table name of database table to be inserted into
1166
     * @param object $dataobject A data object with values for one or more fields in the record
1167
     * @return bool true
1168
     * @throws dml_exception A DML specific exception is thrown for any errors.
1169
     */
1170
    public function import_record($table, $dataobject) {
1171
        if (!is_object($dataobject)) {
1172
            $dataobject = (object)$dataobject;
1173
        }
1174
 
1175
        $columns = $this->get_columns($table);
1176
        $cleaned = array ();
1177
 
1178
        foreach ($dataobject as $field => $value) {
1179
            if (!isset($columns[$field])) {
1180
                continue;
1181
            }
1182
            $column = $columns[$field];
1183
            $cleaned[$field] = $this->normalise_value($column, $value);
1184
        }
1185
 
1186
        $this->insert_record_raw($table, $cleaned, false, false, true);
1187
 
1188
        return true;
1189
    }
1190
 
1191
    /**
1192
     * Update record in database, as fast as possible, no safety checks, lobs not supported.
1193
     * @param string $table name
1194
     * @param stdClass|array $params data record as object or array
1195
     * @param bool true means repeated updates expected
1196
     * @return bool true
1197
     * @throws dml_exception A DML specific exception is thrown for any errors.
1198
     */
1199
    public function update_record_raw($table, $params, $bulk = false) {
1200
        $params = (array)$params;
1201
 
1202
        if (!isset($params['id'])) {
1203
            throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1204
        }
1205
        $id = $params['id'];
1206
        unset($params['id']);
1207
 
1208
        if (empty($params)) {
1209
            throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1210
        }
1211
 
1212
        $sets = array ();
1213
 
1214
        foreach ($params as $field => $value) {
1215
            $sets[] = "$field = ?";
1216
        }
1217
 
1218
        $params[] = $id; // last ? in WHERE condition
1219
 
1220
        $sets = implode(',', $sets);
1221
        $sql = "UPDATE {".$table."} SET $sets WHERE id = ?";
1222
 
1223
        $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1224
 
1225
        return true;
1226
    }
1227
 
1228
    /**
1229
     * Update a record in a table
1230
     *
1231
     * $dataobject is an object containing needed data
1232
     * Relies on $dataobject having a variable "id" to
1233
     * specify the record to update
1234
     *
1235
     * @param string $table The database table to be checked against.
1236
     * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue.
1237
     *        Must have an entry for 'id' to map to the table specified.
1238
     * @param bool true means repeated updates expected
1239
     * @return bool true
1240
     * @throws dml_exception A DML specific exception is thrown for any errors.
1241
     */
1242
    public function update_record($table, $dataobject, $bulk = false) {
1243
        $dataobject = (array)$dataobject;
1244
 
1245
        $columns = $this->get_columns($table);
1246
        $cleaned = array ();
1247
 
1248
        foreach ($dataobject as $field => $value) {
1249
            if (!isset($columns[$field])) {
1250
                continue;
1251
            }
1252
            $column = $columns[$field];
1253
            $cleaned[$field] = $this->normalise_value($column, $value);
1254
        }
1255
 
1256
        return $this->update_record_raw($table, $cleaned, $bulk);
1257
    }
1258
 
1259
    /**
1260
     * Set a single field in every table record which match a particular WHERE clause.
1261
     *
1262
     * @param string $table The database table to be checked against.
1263
     * @param string $newfield the field to set.
1264
     * @param string $newvalue the value to set the field to.
1265
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1266
     * @param array $params array of sql parameters
1267
     * @return bool true
1268
     * @throws dml_exception A DML specific exception is thrown for any errors.
1269
     */
1270
    public function set_field_select($table, $newfield, $newvalue, $select, array $params = null) {
1271
        if ($select) {
1272
            $select = "WHERE $select";
1273
        }
1274
 
1275
        if (is_null($params)) {
1276
            $params = array ();
1277
        }
1278
 
1279
        // convert params to ? types
1280
        list($select, $params, $type) = $this->fix_sql_params($select, $params);
1281
 
1282
        // Get column metadata
1283
        $columns = $this->get_columns($table);
1284
        $column = $columns[$newfield];
1285
 
1286
        $newvalue = $this->normalise_value($column, $newvalue);
1287
 
1288
        if (is_null($newvalue)) {
1289
            $newfield = "$newfield = NULL";
1290
        } else {
1291
            $newfield = "$newfield = ?";
1292
            array_unshift($params, $newvalue);
1293
        }
1294
        $sql = "UPDATE {".$table."} SET $newfield $select";
1295
 
1296
        $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1297
 
1298
        return true;
1299
    }
1300
 
1301
    /**
1302
     * Delete one or more records from a table which match a particular WHERE clause.
1303
     *
1304
     * @param string $table The database table to be checked against.
1305
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1306
     * @param array $params array of sql parameters
1307
     * @return bool true
1308
     * @throws dml_exception A DML specific exception is thrown for any errors.
1309
     */
1310
    public function delete_records_select($table, $select, array $params = null) {
1311
        if ($select) {
1312
            $select = "WHERE $select";
1313
        }
1314
 
1315
        $sql = "DELETE FROM {".$table."} $select";
1316
 
1317
        // we use SQL_QUERY_UPDATE because we do not know what is in general SQL, delete constant would not be accurate
1318
        $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1319
 
1320
        return true;
1321
    }
1322
 
1323
    /**
1324
     * Return SQL for casting to char of given field/expression
1325
     *
1326
     * @param string $field Table field or SQL expression to be cast
1327
     * @return string
1328
     */
1329
    public function sql_cast_to_char(string $field): string {
1330
        return "CAST({$field} AS NVARCHAR(MAX))";
1331
    }
1332
 
1333
 
1334
    public function sql_cast_char2int($fieldname, $text = false) {
1335
        if (!$text) {
1336
            return ' CAST(' . $fieldname . ' AS INT) ';
1337
        } else {
1338
            return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1339
        }
1340
    }
1341
 
1342
    public function sql_cast_char2real($fieldname, $text=false) {
1343
        if (!$text) {
1344
            return ' CAST(' . $fieldname . ' AS REAL) ';
1345
        } else {
1346
            return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS REAL) ';
1347
        }
1348
    }
1349
 
1350
    public function sql_ceil($fieldname) {
1351
        return ' CEILING('.$fieldname.')';
1352
    }
1353
 
1354
    protected function get_collation() {
1355
        if (isset($this->collation)) {
1356
            return $this->collation;
1357
        }
1358
        if (!empty($this->dboptions['dbcollation'])) {
1359
            // perf speedup
1360
            $this->collation = $this->dboptions['dbcollation'];
1361
            return $this->collation;
1362
        }
1363
 
1364
        // make some default
1365
        $this->collation = 'Latin1_General_CI_AI';
1366
 
1367
        $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
1368
        $this->query_start($sql, null, SQL_QUERY_AUX);
1369
        $result = sqlsrv_query($this->sqlsrv, $sql);
1370
        $this->query_end($result);
1371
 
1372
        if ($result) {
1373
            if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
1374
                $this->collation = reset($rawcolumn);
1375
            }
1376
            $this->free_result($result);
1377
        }
1378
 
1379
        return $this->collation;
1380
    }
1381
 
1382
    public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1383
        $equalop = $notequal ? '<>' : '=';
1384
        $collation = $this->get_collation();
1385
 
1386
        if ($casesensitive) {
1387
            $collation = str_replace('_CI', '_CS', $collation);
1388
        } else {
1389
            $collation = str_replace('_CS', '_CI', $collation);
1390
        }
1391
        if ($accentsensitive) {
1392
            $collation = str_replace('_AI', '_AS', $collation);
1393
        } else {
1394
            $collation = str_replace('_AS', '_AI', $collation);
1395
        }
1396
 
1397
        return "$fieldname COLLATE $collation $equalop $param";
1398
    }
1399
 
1400
    /**
1401
     * Returns 'LIKE' part of a query.
1402
     *
1403
     * @param string $fieldname usually name of the table column
1404
     * @param string $param usually bound query parameter (?, :named)
1405
     * @param bool $casesensitive use case sensitive search
1406
     * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1407
     * @param bool $notlike true means "NOT LIKE"
1408
     * @param string $escapechar escape char for '%' and '_'
1409
     * @return string SQL code fragment
1410
     */
1411
    public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1412
        if (strpos($param, '%') !== false) {
1413
            debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1414
        }
1415
 
1416
        $collation = $this->get_collation();
1417
        $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1418
 
1419
        if ($casesensitive) {
1420
            $collation = str_replace('_CI', '_CS', $collation);
1421
        } else {
1422
            $collation = str_replace('_CS', '_CI', $collation);
1423
        }
1424
        if ($accentsensitive) {
1425
            $collation = str_replace('_AI', '_AS', $collation);
1426
        } else {
1427
            $collation = str_replace('_AS', '_AI', $collation);
1428
        }
1429
 
1430
        return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
1431
    }
1432
 
1433
    /**
1434
     * Escape common SQL LIKE special characters like '_' or '%', plus '[' & ']' which are also supported in SQL Server
1435
     *
1436
     * Note that '^' and '-' also have meaning within a LIKE, but only when enclosed within square brackets. As this syntax
1437
     * is not supported on all databases and the brackets are always escaped, we don't need special handling of them
1438
     *
1439
     * @param string $text
1440
     * @param string $escapechar
1441
     * @return string
1442
     */
1443
    public function sql_like_escape($text, $escapechar = '\\') {
1444
        $text = parent::sql_like_escape($text, $escapechar);
1445
 
1446
        $text = str_replace('[', $escapechar . '[', $text);
1447
        $text = str_replace(']', $escapechar . ']', $text);
1448
 
1449
        return $text;
1450
    }
1451
 
1452
    public function sql_concat(...$arr) {
1453
        foreach ($arr as $key => $ele) {
1454
            $arr[$key] = $this->sql_cast_to_char($ele);
1455
        }
1456
        $s = implode(' + ', $arr);
1457
 
1458
        if ($s === '') {
1459
            return " '' ";
1460
        }
1461
        return " $s ";
1462
    }
1463
 
1464
    public function sql_concat_join($separator = "' '", $elements = array ()) {
1465
        for ($n = count($elements) - 1; $n > 0; $n--) {
1466
            array_splice($elements, $n, 0, $separator);
1467
        }
1468
        return call_user_func_array(array($this, 'sql_concat'), array_values($elements));
1469
    }
1470
 
1471
    /**
1472
     * Return SQL for performing group concatenation on given field/expression
1473
     *
1474
     * @param string $field
1475
     * @param string $separator
1476
     * @param string $sort
1477
     * @return string
1478
     */
1479
    public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
1480
        $fieldsort = $sort ? "WITHIN GROUP (ORDER BY {$sort})" : '';
1481
        return "STRING_AGG({$field}, '{$separator}') {$fieldsort}";
1482
    }
1483
 
1484
    public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1485
        if ($textfield) {
1486
            return ' ('.$this->sql_compare_text($fieldname)." = '') ";
1487
        } else {
1488
            return " ($fieldname = '') ";
1489
        }
1490
    }
1491
 
1492
    /**
1493
     * Returns the SQL text to be used to calculate the length in characters of one expression.
1494
     * @param string fieldname or expression to calculate its length in characters.
1495
     * @return string the piece of SQL code to be used in the statement.
1496
     */
1497
    public function sql_length($fieldname) {
1498
        return ' LEN('.$fieldname.')';
1499
    }
1500
 
1501
    public function sql_order_by_text($fieldname, $numchars = 32) {
1502
        return " CONVERT(varchar({$numchars}), {$fieldname})";
1503
    }
1504
 
1505
    /**
1506
     * Returns the SQL for returning searching one string for the location of another.
1507
     */
1508
    public function sql_position($needle, $haystack) {
1509
        return "CHARINDEX(($needle), ($haystack))";
1510
    }
1511
 
1512
    /**
1513
     * Returns the proper substr() SQL text used to extract substrings from DB
1514
     * NOTE: this was originally returning only function name
1515
     *
1516
     * @param string $expr some string field, no aggregates
1517
     * @param mixed $start integer or expression evaluating to int
1518
     * @param mixed $length optional integer or expression evaluating to int
1519
     * @return string sql fragment
1520
     */
1521
    public function sql_substr($expr, $start, $length = false) {
1522
        if (count(func_get_args()) < 2) {
1523
            throw new coding_exception('moodle_database::sql_substr() requires at least two parameters',
1524
                'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
1525
        }
1526
 
1527
        if ($length === false) {
1528
            return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", 2^31-1)";
1529
        } else {
1530
            return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", " . $this->sql_cast_char2int($length) . ")";
1531
        }
1532
    }
1533
 
1534
    /**
1535
     * Does this driver support tool_replace?
1536
     *
1537
     * @since Moodle 2.6.1
1538
     * @return bool
1539
     */
1540
    public function replace_all_text_supported() {
1541
        return true;
1542
    }
1543
 
1544
    public function session_lock_supported() {
1545
        return true;
1546
    }
1547
 
1548
    /**
1549
     * Obtain session lock
1550
     * @param int $rowid id of the row with session record
1551
     * @param int $timeout max allowed time to wait for the lock in seconds
1552
     * @return void
1553
     */
1554
    public function get_session_lock($rowid, $timeout) {
1555
        if (!$this->session_lock_supported()) {
1556
            return;
1557
        }
1558
        parent::get_session_lock($rowid, $timeout);
1559
 
1560
        $timeoutmilli = $timeout * 1000;
1561
 
1562
        $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1563
        // While this may work using proper {call sp_...} calls + binding +
1564
        // executing + consuming recordsets, the solution used for the mssql
1565
        // driver is working perfectly, so 100% mimic-ing that code.
1566
        // $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session',  $timeoutmilli";
1567
        $sql = "BEGIN
1568
                    DECLARE @result INT
1569
                    EXECUTE @result = sp_getapplock @Resource='$fullname',
1570
                                                    @LockMode='Exclusive',
1571
                                                    @LockOwner='Session',
1572
                                                    @LockTimeout='$timeoutmilli'
1573
                    SELECT @result
1574
                END";
1575
        $this->query_start($sql, null, SQL_QUERY_AUX);
1576
        $result = sqlsrv_query($this->sqlsrv, $sql);
1577
        $this->query_end($result);
1578
 
1579
        if ($result) {
1580
            $row = sqlsrv_fetch_array($result);
1581
            if ($row[0] < 0) {
1582
                throw new dml_sessionwait_exception();
1583
            }
1584
        }
1585
 
1586
        $this->free_result($result);
1587
    }
1588
 
1589
    public function release_session_lock($rowid) {
1590
        if (!$this->session_lock_supported()) {
1591
            return;
1592
        }
1593
        if (!$this->used_for_db_sessions) {
1594
            return;
1595
        }
1596
 
1597
        parent::release_session_lock($rowid);
1598
 
1599
        $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1600
        $sql = "sp_releaseapplock '$fullname', 'Session'";
1601
        $this->query_start($sql, null, SQL_QUERY_AUX);
1602
        $result = sqlsrv_query($this->sqlsrv, $sql);
1603
        $this->query_end($result);
1604
        $this->free_result($result);
1605
    }
1606
 
1607
    /**
1608
     * Driver specific start of real database transaction,
1609
     * this can not be used directly in code.
1610
     * @return void
1611
     */
1612
    protected function begin_transaction() {
1613
        // Recordsets do not work well with transactions in SQL Server,
1614
        // let's prefetch the recordsets to memory to work around these problems.
1615
        foreach ($this->recordsets as $rs) {
1616
            $rs->transaction_starts();
1617
        }
1618
 
1619
        $this->query_start('native sqlsrv_begin_transaction', NULL, SQL_QUERY_AUX);
1620
        $result = sqlsrv_begin_transaction($this->sqlsrv);
1621
        $this->query_end($result);
1622
    }
1623
 
1624
    /**
1625
     * Driver specific commit of real database transaction,
1626
     * this can not be used directly in code.
1627
     * @return void
1628
     */
1629
    protected function commit_transaction() {
1630
        $this->query_start('native sqlsrv_commit', NULL, SQL_QUERY_AUX);
1631
        $result = sqlsrv_commit($this->sqlsrv);
1632
        $this->query_end($result);
1633
    }
1634
 
1635
    /**
1636
     * Driver specific abort of real database transaction,
1637
     * this can not be used directly in code.
1638
     * @return void
1639
     */
1640
    protected function rollback_transaction() {
1641
        $this->query_start('native sqlsrv_rollback', NULL, SQL_QUERY_AUX);
1642
        $result = sqlsrv_rollback($this->sqlsrv);
1643
        $this->query_end($result);
1644
    }
1645
 
1646
    /**
1647
     * Is fulltext search enabled?.
1648
     *
1649
     * @return bool
1650
     */
1651
    public function is_fulltext_search_supported() {
1652
        global $CFG;
1653
 
1654
        $sql = "SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')";
1655
        $this->query_start($sql, null, SQL_QUERY_AUX);
1656
        $result = sqlsrv_query($this->sqlsrv, $sql);
1657
        $this->query_end($result);
1658
        if ($result) {
1659
            if ($row = sqlsrv_fetch_array($result)) {
1660
                $property = (bool)reset($row);
1661
            }
1662
        }
1663
        $this->free_result($result);
1664
 
1665
        return !empty($property);
1666
    }
1667
}