Commit | Line | Data |
---|---|---|
520b730c | 1 | <?php |
2 | ||
3 | // This file is part of Moodle - http://moodle.org/ | |
4 | // | |
5 | // Moodle is free software: you can redistribute it and/or modify | |
6 | // it under the terms of the GNU General Public License as published by | |
7 | // the Free Software Foundation, either version 3 of the License, or | |
8 | // (at your option) any later version. | |
9 | // | |
10 | // Moodle is distributed in the hope that it will be useful, | |
11 | // but WITHOUT ANY WARRANTY; without even the implied warranty of | |
12 | // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the | |
13 | // GNU General Public License for more details. | |
14 | // | |
15 | // You should have received a copy of the GNU General Public License | |
16 | // along with Moodle. If not, see <http://www.gnu.org/licenses/>. | |
17 | ||
18 | ||
19 | /** | |
20 | * Native mssql class representing moodle database interface. | |
21 | * | |
66c0ee78 | 22 | * @package core |
f2ed3f05 | 23 | * @subpackage dml |
520b730c | 24 | * @copyright 2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com} |
25 | * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later | |
26 | */ | |
27 | ||
f2ed3f05 PS |
28 | defined('MOODLE_INTERNAL') || die(); |
29 | ||
520b730c | 30 | require_once($CFG->libdir.'/dml/moodle_database.php'); |
31 | require_once($CFG->libdir.'/dml/mssql_native_moodle_recordset.php'); | |
32 | require_once($CFG->libdir.'/dml/mssql_native_moodle_temptables.php'); | |
33 | ||
34 | /** | |
35 | * Native mssql class representing moodle database interface. | |
36 | */ | |
37 | class mssql_native_moodle_database extends moodle_database { | |
38 | ||
39 | protected $mssql = null; | |
5c08b24b | 40 | protected $last_error_reporting; // To handle mssql driver default verbosity |
6055f89d | 41 | protected $collation; // current DB collation cache |
5c08b24b | 42 | |
520b730c | 43 | /** |
44 | * Detects if all needed PHP stuff installed. | |
45 | * Note: can be used before connect() | |
46 | * @return mixed true if ok, string if something | |
47 | */ | |
48 | public function driver_installed() { | |
49 | if (!function_exists('mssql_connect')) { | |
50 | return get_string('mssqlextensionisnotpresentinphp', 'install'); | |
51 | } | |
52 | return true; | |
53 | } | |
54 | ||
55 | /** | |
56 | * Returns database family type - describes SQL dialect | |
57 | * Note: can be used before connect() | |
58 | * @return string db family name (mysql, postgres, mssql, oracle, etc.) | |
59 | */ | |
60 | public function get_dbfamily() { | |
61 | return 'mssql'; | |
62 | } | |
63 | ||
64 | /** | |
65 | * Returns more specific database driver type | |
66 | * Note: can be used before connect() | |
4a6c2ab8 | 67 | * @return string db type mysqli, pgsql, oci, mssql, sqlsrv |
520b730c | 68 | */ |
69 | protected function get_dbtype() { | |
70 | return 'mssql'; | |
71 | } | |
72 | ||
73 | /** | |
74 | * Returns general database library name | |
75 | * Note: can be used before connect() | |
eb123deb | 76 | * @return string db type pdo, native |
520b730c | 77 | */ |
78 | protected function get_dblibrary() { | |
79 | return 'native'; | |
80 | } | |
81 | ||
82 | /** | |
83 | * Returns localised database type name | |
84 | * Note: can be used before connect() | |
85 | * @return string | |
86 | */ | |
87 | public function get_name() { | |
88 | return get_string('nativemssql', 'install'); | |
89 | } | |
90 | ||
520b730c | 91 | /** |
92 | * Returns localised database configuration help. | |
93 | * Note: can be used before connect() | |
94 | * @return string | |
95 | */ | |
96 | public function get_configuration_help() { | |
97 | return get_string('nativemssqlhelp', 'install'); | |
98 | } | |
99 | ||
100 | /** | |
101 | * Returns localised database description | |
102 | * Note: can be used before connect() | |
103 | * @return string | |
104 | */ | |
105 | public function get_configuration_hints() { | |
106 | $str = get_string('databasesettingssub_mssql', 'install'); | |
107 | $str .= "<p style='text-align:right'><a href=\"javascript:void(0)\" "; | |
108 | $str .= "onclick=\"return window.open('http://docs.moodle.org/en/Installing_MSSQL_for_PHP')\""; | |
109 | $str .= ">"; | |
110 | $str .= '<img src="pix/docs.gif' . '" alt="Docs" class="iconhelp" />'; | |
111 | $str .= get_string('moodledocslink', 'install') . '</a></p>'; | |
112 | return $str; | |
113 | } | |
114 | ||
115 | /** | |
116 | * Connect to db | |
117 | * Must be called before other methods. | |
118 | * @param string $dbhost | |
119 | * @param string $dbuser | |
120 | * @param string $dbpass | |
121 | * @param string $dbname | |
122 | * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used | |
123 | * @param array $dboptions driver specific options | |
124 | * @return bool true | |
125 | * @throws dml_connection_exception if error | |
126 | */ | |
127 | public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { | |
128 | if ($prefix == '' and !$this->external) { | |
129 | //Enforce prefixes for everybody but mysql | |
130 | throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily()); | |
131 | } | |
132 | ||
133 | $driverstatus = $this->driver_installed(); | |
134 | ||
135 | if ($driverstatus !== true) { | |
136 | throw new dml_exception('dbdriverproblem', $driverstatus); | |
137 | } | |
138 | ||
139 | $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); | |
140 | ||
141 | ob_start(); | |
a0b7200d | 142 | if (!empty($this->dboptions['dbpersist'])) { // persistent connection |
520b730c | 143 | $this->mssql = mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpass, true); |
144 | } else { | |
145 | $this->mssql = mssql_connect($this->dbhost, $this->dbuser, $this->dbpass, true); | |
146 | } | |
147 | $dberr = ob_get_contents(); | |
148 | ob_end_clean(); | |
149 | ||
150 | if ($this->mssql === false) { | |
151 | $this->mssql = null; | |
152 | throw new dml_connection_exception($dberr); | |
153 | } | |
154 | ||
155 | // already connected, select database and set some env. variables | |
156 | $this->query_start("--mssql_select_db", null, SQL_QUERY_AUX); | |
157 | $result = mssql_select_db($this->dbname, $this->mssql); | |
158 | $this->query_end($result); | |
159 | ||
160 | // No need to set charset. It's UTF8, with transparent conversions | |
161 | // back and forth performed both by FreeTDS or ODBTP | |
162 | ||
163 | // Allow quoted identifiers | |
164 | $sql = "SET QUOTED_IDENTIFIER ON"; | |
165 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
166 | $result = mssql_query($sql, $this->mssql); | |
167 | $this->query_end($result); | |
168 | ||
169 | $this->free_result($result); | |
170 | ||
171 | // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL | |
4d276e34 | 172 | // instead of equal(=) and distinct(<>) symbols |
520b730c | 173 | $sql = "SET ANSI_NULLS ON"; |
174 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
175 | $result = mssql_query($sql, $this->mssql); | |
176 | $this->query_end($result); | |
177 | ||
178 | $this->free_result($result); | |
179 | ||
180 | // Force ANSI warnings so arithmetic/string overflows will be | |
181 | // returning error instead of transparently truncating data | |
182 | $sql = "SET ANSI_WARNINGS ON"; | |
183 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
184 | $result = mssql_query($sql, $this->mssql); | |
185 | $this->query_end($result); | |
186 | ||
187 | // Concatenating null with anything MUST return NULL | |
188 | $sql = "SET CONCAT_NULL_YIELDS_NULL ON"; | |
189 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
190 | $result = mssql_query($sql, $this->mssql); | |
191 | $this->query_end($result); | |
192 | ||
193 | $this->free_result($result); | |
194 | ||
195 | // Set transactions isolation level to READ_COMMITTED | |
196 | // prevents dirty reads when using transactions + | |
197 | // is the default isolation level of MSSQL | |
12ffe203 | 198 | // Requires database to run with READ_COMMITTED_SNAPSHOT ON |
520b730c | 199 | $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"; |
200 | $this->query_start($sql, NULL, SQL_QUERY_AUX); | |
201 | $result = mssql_query($sql, $this->mssql); | |
202 | $this->query_end($result); | |
203 | ||
204 | $this->free_result($result); | |
205 | ||
4d276e34 | 206 | // Connection stabilised and configured, going to instantiate the temptables controller |
520b730c | 207 | $this->temptables = new mssql_native_moodle_temptables($this); |
208 | ||
209 | return true; | |
210 | } | |
211 | ||
212 | /** | |
213 | * Close database connection and release all resources | |
214 | * and memory (especially circular memory references). | |
215 | * Do NOT use connect() again, create a new instance if needed. | |
216 | */ | |
217 | public function dispose() { | |
4d276e34 | 218 | parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection |
520b730c | 219 | if ($this->mssql) { |
220 | mssql_close($this->mssql); | |
221 | $this->mssql = null; | |
222 | } | |
223 | } | |
224 | ||
225 | /** | |
226 | * Called before each db query. | |
227 | * @param string $sql | |
228 | * @param array array of parameters | |
229 | * @param int $type type of query | |
230 | * @param mixed $extrainfo driver specific extra information | |
231 | * @return void | |
232 | */ | |
233 | protected function query_start($sql, array $params=null, $type, $extrainfo=null) { | |
234 | parent::query_start($sql, $params, $type, $extrainfo); | |
5c08b24b | 235 | // mssql driver tends to send debug to output, we do not need that ;-) |
236 | $this->last_error_reporting = error_reporting(0); | |
520b730c | 237 | } |
238 | ||
239 | /** | |
240 | * Called immediately after each db query. | |
241 | * @param mixed db specific result | |
242 | * @return void | |
243 | */ | |
244 | protected function query_end($result) { | |
5c08b24b | 245 | // reset original debug level |
246 | error_reporting($this->last_error_reporting); | |
520b730c | 247 | parent::query_end($result); |
248 | } | |
249 | ||
250 | /** | |
251 | * Returns database server info array | |
252 | * @return array | |
253 | */ | |
254 | public function get_server_info() { | |
255 | static $info; | |
256 | if (!$info) { | |
257 | $info = array(); | |
258 | $sql = 'sp_server_info 2'; | |
259 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
260 | $result = mssql_query($sql, $this->mssql); | |
261 | $this->query_end($result); | |
262 | $row = mssql_fetch_row($result); | |
263 | $info['server'] = $row[2]; | |
264 | $this->free_result($result); | |
265 | ||
266 | $sql = 'sp_server_info 500'; | |
267 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
268 | $result = mssql_query($sql, $this->mssql); | |
269 | $this->query_end($result); | |
270 | $row = mssql_fetch_row($result); | |
271 | $info['version'] = $row[2]; | |
272 | $this->free_result($result); | |
273 | } | |
274 | return $info; | |
275 | } | |
276 | ||
277 | protected function is_min_version($version) { | |
278 | $server = $this->get_server_info(); | |
279 | $server = $server['version']; | |
280 | return version_compare($server, $version, '>='); | |
281 | } | |
282 | ||
283 | /** | |
284 | * Converts short table name {tablename} to real table name | |
285 | * supporting temp tables (#) if detected | |
286 | * | |
287 | * @param string sql | |
288 | * @return string sql | |
289 | */ | |
290 | protected function fix_table_names($sql) { | |
291 | if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/', $sql, $matches)) { | |
292 | foreach($matches[0] as $key=>$match) { | |
293 | $name = $matches[1][$key]; | |
4b1ee2b3 | 294 | if ($this->temptables->is_temptable($name)) { |
295 | $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql); | |
296 | } else { | |
297 | $sql = str_replace($match, $this->prefix.$name, $sql); | |
298 | } | |
520b730c | 299 | } |
300 | } | |
301 | return $sql; | |
302 | } | |
303 | ||
304 | /** | |
305 | * Returns supported query parameter types | |
9331d879 | 306 | * @return int bitmask |
520b730c | 307 | */ |
308 | protected function allowed_param_types() { | |
309 | return SQL_PARAMS_QM; // Not really, but emulated, see emulate_bound_params() | |
310 | } | |
311 | ||
312 | /** | |
313 | * Returns last error reported by database engine. | |
9331d879 | 314 | * @return string error message |
520b730c | 315 | */ |
316 | public function get_last_error() { | |
317 | return mssql_get_last_message(); | |
318 | } | |
319 | ||
320 | /** | |
321 | * Return tables in database WITHOUT current prefix | |
322 | * @return array of table names in lowercase and without prefix | |
323 | */ | |
324 | public function get_tables($usecache=true) { | |
325 | if ($usecache and $this->tables !== null) { | |
326 | return $this->tables; | |
327 | } | |
328 | $this->tables = array(); | |
329 | $sql = "SELECT table_name | |
330 | FROM information_schema.tables | |
331 | WHERE table_name LIKE '$this->prefix%' | |
332 | AND table_type = 'BASE TABLE'"; | |
333 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
334 | $result = mssql_query($sql, $this->mssql); | |
335 | $this->query_end($result); | |
336 | ||
337 | if ($result) { | |
338 | while ($row = mssql_fetch_row($result)) { | |
339 | $tablename = reset($row); | |
340 | if (strpos($tablename, $this->prefix) !== 0) { | |
341 | continue; | |
342 | } | |
343 | $tablename = substr($tablename, strlen($this->prefix)); | |
344 | $this->tables[$tablename] = $tablename; | |
345 | } | |
346 | $this->free_result($result); | |
347 | } | |
348 | ||
349 | // Add the currently available temptables | |
350 | $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); | |
351 | return $this->tables; | |
352 | } | |
353 | ||
354 | /** | |
355 | * Return table indexes - everything lowercased | |
356 | * @return array of arrays | |
357 | */ | |
358 | public function get_indexes($table) { | |
359 | $indexes = array(); | |
360 | $tablename = $this->prefix.$table; | |
361 | ||
362 | // Indexes aren't covered by information_schema metatables, so we need to | |
363 | // go to sys ones. Skipping primary key indexes on purpose. | |
364 | $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name | |
365 | FROM sys.indexes i | |
366 | JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id | |
367 | JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id | |
368 | JOIN sys.tables t ON i.object_id = t.object_id | |
369 | WHERE t.name = '$tablename' | |
370 | AND i.is_primary_key = 0 | |
371 | ORDER BY i.name, i.index_id, ic.index_column_id"; | |
372 | ||
373 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
374 | $result = mssql_query($sql, $this->mssql); | |
375 | $this->query_end($result); | |
376 | ||
377 | if ($result) { | |
378 | $lastindex = ''; | |
379 | $unique = false; | |
380 | $columns = array(); | |
381 | while ($row = mssql_fetch_assoc($result)) { | |
382 | if ($lastindex and $lastindex != $row['index_name']) { // Save lastindex to $indexes and reset info | |
383 | $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns); | |
384 | $unique = false; | |
385 | $columns = array(); | |
386 | } | |
387 | $lastindex = $row['index_name']; | |
388 | $unique = empty($row['is_unique']) ? false : true; | |
389 | $columns[] = $row['column_name']; | |
390 | } | |
391 | if ($lastindex ) { // Add the last one if exists | |
392 | $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns); | |
393 | } | |
394 | $this->free_result($result); | |
395 | } | |
396 | return $indexes; | |
397 | } | |
398 | ||
399 | /** | |
400 | * Returns datailed information about columns in table. This information is cached internally. | |
401 | * @param string $table name | |
402 | * @param bool $usecache | |
403 | * @return array array of database_column_info objects indexed with column names | |
404 | */ | |
405 | public function get_columns($table, $usecache=true) { | |
406 | if ($usecache and isset($this->columns[$table])) { | |
407 | return $this->columns[$table]; | |
408 | } | |
409 | ||
410 | $this->columns[$table] = array(); | |
411 | ||
520b730c | 412 | if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema |
413 | $sql = "SELECT column_name AS name, | |
414 | data_type AS type, | |
415 | numeric_precision AS max_length, | |
416 | character_maximum_length AS char_max_length, | |
417 | numeric_scale AS scale, | |
418 | is_nullable AS is_nullable, | |
419 | columnproperty(object_id(quotename(table_schema) + '.' + | |
420 | quotename(table_name)), column_name, 'IsIdentity') AS auto_increment, | |
421 | column_default AS default_value | |
422 | FROM information_schema.columns | |
896a2a91 | 423 | WHERE table_name = '{" . $table . "}' |
520b730c | 424 | ORDER BY ordinal_position"; |
425 | } else { // temp table, get metadata from tempdb schema | |
426 | $sql = "SELECT column_name AS name, | |
427 | data_type AS type, | |
428 | numeric_precision AS max_length, | |
429 | character_maximum_length AS char_max_length, | |
430 | numeric_scale AS scale, | |
431 | is_nullable AS is_nullable, | |
432 | columnproperty(object_id(quotename(table_schema) + '.' + | |
433 | quotename(table_name)), column_name, 'IsIdentity') AS auto_increment, | |
434 | column_default AS default_value | |
435 | FROM tempdb.information_schema.columns | |
3c41bba0 | 436 | JOIN tempdb..sysobjects ON name = table_name |
437 | WHERE id = object_id('tempdb..{" . $table . "}') | |
520b730c | 438 | ORDER BY ordinal_position"; |
439 | } | |
440 | ||
896a2a91 | 441 | list($sql, $params, $type) = $this->fix_sql_params($sql, null); |
442 | ||
520b730c | 443 | $this->query_start($sql, null, SQL_QUERY_AUX); |
444 | $result = mssql_query($sql, $this->mssql); | |
445 | $this->query_end($result); | |
446 | ||
447 | if (!$result) { | |
448 | return array(); | |
449 | } | |
450 | ||
451 | while ($rawcolumn = mssql_fetch_assoc($result)) { | |
452 | ||
453 | $rawcolumn = (object)$rawcolumn; | |
454 | ||
455 | $info = new object(); | |
456 | $info->name = $rawcolumn->name; | |
457 | $info->type = $rawcolumn->type; | |
458 | $info->meta_type = $this->mssqltype2moodletype($info->type); | |
459 | ||
460 | // Prepare auto_increment info | |
461 | $info->auto_increment = $rawcolumn->auto_increment ? true : false; | |
462 | ||
463 | // Define type for auto_increment columns | |
464 | $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type; | |
465 | ||
466 | // id columns being auto_incremnt are PK by definition | |
467 | $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment); | |
468 | ||
469 | // Put correct length for character and LOB types | |
470 | $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length; | |
471 | $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length; | |
472 | ||
473 | // Scale | |
474 | $info->scale = $rawcolumn->scale ? $rawcolumn->scale : false; | |
475 | ||
476 | // Prepare not_null info | |
477 | $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false; | |
478 | ||
479 | // Process defaults | |
480 | $info->has_default = !empty($rawcolumn->default_value); | |
481 | $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value); | |
482 | ||
483 | // Process binary | |
484 | $info->binary = $info->meta_type == 'B' ? true : false; | |
485 | ||
486 | $this->columns[$table][$info->name] = new database_column_info($info); | |
487 | } | |
488 | $this->free_result($result); | |
489 | ||
490 | return $this->columns[$table]; | |
491 | } | |
492 | ||
493 | /** | |
494 | * Normalise values based in RDBMS dependencies (booleans, LOBs...) | |
495 | * | |
496 | * @param database_column_info $column column metadata corresponding with the value we are going to normalise | |
497 | * @param mixed $value value we are going to normalise | |
498 | * @return mixed the normalised value | |
499 | */ | |
e3acc8af | 500 | protected function normalise_value($column, $value) { |
520b730c | 501 | if (is_bool($value)) { /// Always, convert boolean to int |
502 | $value = (int)$value; | |
8a89be3c | 503 | } // And continue processing because text columns with numeric info need special handling below |
520b730c | 504 | |
8a89be3c | 505 | if ($column->meta_type == 'B') { // BLOBs need to be properly "packed", but can be inserted directly if so. |
520b730c | 506 | if (!is_null($value)) { // If value not null, unpack it to unquoted hexadecimal byte-string format |
507 | $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it | |
508 | } // easily and "bind" the param ok. | |
509 | ||
510 | } else if ($column->meta_type == 'X') { // MSSQL doesn't cast from int to text, so if text column | |
511 | if (is_numeric($value)) { // and is numeric value then cast to string | |
512 | $value = array('numstr' => (string)$value); // and put into array, so emulate_bound_params() will know how | |
513 | } // to "bind" the param ok, avoiding reverse conversion to number | |
514 | ||
515 | } else if ($value === '') { | |
516 | if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { | |
517 | $value = 0; // prevent '' problems in numeric fields | |
518 | } | |
519 | } | |
520 | return $value; | |
521 | } | |
522 | ||
523 | /** | |
524 | * Selectively call mssql_free_result(), avoiding some warnings without using the horrible @ | |
525 | * | |
526 | * @param mssql_resource $resource resource to be freed if possible | |
527 | */ | |
528 | private function free_result($resource) { | |
529 | if (!is_bool($resource)) { // true/false resources cannot be freed | |
530 | mssql_free_result($resource); | |
531 | } | |
532 | } | |
533 | ||
534 | /** | |
535 | * Provides mapping between mssql native data types and moodle_database - database_column_info - ones) | |
536 | * | |
537 | * @param string $mssql_type native mssql data type | |
538 | * @return string 1-char database_column_info data type | |
539 | */ | |
540 | private function mssqltype2moodletype($mssql_type) { | |
541 | $type = null; | |
542 | switch (strtoupper($mssql_type)) { | |
543 | case 'BIT': | |
544 | $type = 'L'; | |
545 | break; | |
546 | case 'INT': | |
547 | case 'SMALLINT': | |
548 | case 'INTEGER': | |
549 | case 'BIGINT': | |
550 | $type = 'I'; | |
551 | break; | |
552 | case 'DECIMAL': | |
553 | case 'REAL': | |
554 | case 'FLOAT': | |
555 | $type = 'N'; | |
556 | break; | |
557 | case 'VARCHAR': | |
558 | case 'NVARCHAR': | |
559 | $type = 'C'; | |
560 | break; | |
561 | case 'TEXT': | |
562 | case 'NTEXT': | |
563 | case 'VARCHAR(MAX)': | |
564 | case 'NVARCHAR(MAX)': | |
565 | $type = 'X'; | |
566 | break; | |
567 | case 'IMAGE': | |
568 | case 'VARBINARY(MAX)': | |
569 | $type = 'B'; | |
570 | break; | |
571 | case 'DATETIME': | |
572 | $type = 'D'; | |
573 | break; | |
574 | } | |
575 | if (!$type) { | |
576 | throw new dml_exception('invalidmssqlnativetype', $mssql_type); | |
577 | } | |
578 | return $type; | |
579 | } | |
580 | ||
581 | /** | |
582 | * Do NOT use in code, to be used by database_manager only! | |
583 | * @param string $sql query | |
584 | * @return bool true | |
585 | * @throws dml_exception if error | |
586 | */ | |
587 | public function change_database_structure($sql) { | |
588 | $this->reset_caches(); | |
589 | ||
590 | $this->query_start($sql, null, SQL_QUERY_STRUCTURE); | |
5c08b24b | 591 | $result = mssql_query($sql, $this->mssql); |
520b730c | 592 | $this->query_end($result); |
593 | ||
594 | return true; | |
595 | } | |
596 | ||
597 | /** | |
598 | * Very ugly hack which emulates bound parameters in queries | |
599 | * because the mssql driver doesn't support placeholders natively at all | |
600 | */ | |
601 | protected function emulate_bound_params($sql, array $params=null) { | |
602 | if (empty($params)) { | |
603 | return $sql; | |
604 | } | |
605 | /// ok, we have verified sql statement with ? and correct number of params | |
606 | $return = strtok($sql, '?'); | |
607 | foreach ($params as $param) { | |
608 | if (is_bool($param)) { | |
609 | $return .= (int)$param; | |
610 | ||
611 | } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially | |
612 | $return .= '0x' . $param['hex']; | |
613 | ||
614 | } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not* | |
615 | $return .= "N'{$param['numstr']}'"; // be converted back to number params, but bound as strings | |
616 | ||
617 | } else if (is_null($param)) { | |
618 | $return .= 'NULL'; | |
619 | ||
620 | } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646 | |
621 | $return .= $param; | |
622 | ||
623 | } else if (is_float($param)) { | |
624 | $return .= $param; | |
625 | ||
626 | } else { | |
627 | $param = str_replace("'", "''", $param); | |
628 | $return .= "N'$param'"; | |
629 | } | |
630 | ||
631 | $return .= strtok('?'); | |
632 | } | |
633 | return $return; | |
634 | } | |
635 | ||
636 | /** | |
637 | * Execute general sql query. Should be used only when no other method suitable. | |
638 | * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead! | |
639 | * @param string $sql query | |
640 | * @param array $params query parameters | |
641 | * @return bool true | |
642 | * @throws dml_exception if error | |
643 | */ | |
644 | public function execute($sql, array $params=null) { | |
645 | ||
646 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
647 | $rawsql = $this->emulate_bound_params($sql, $params); | |
648 | ||
649 | if (strpos($sql, ';') !== false) { | |
650 | throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); | |
651 | } | |
652 | ||
653 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); | |
5c08b24b | 654 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 655 | $this->query_end($result); |
656 | $this->free_result($result); | |
657 | ||
658 | return true; | |
659 | } | |
660 | ||
661 | /** | |
662 | * Get a number of records as a moodle_recordset using a SQL statement. | |
663 | * | |
664 | * Since this method is a little less readable, use of it should be restricted to | |
665 | * code where it's possible there might be large datasets being returned. For known | |
666 | * small datasets use get_records_sql - it leads to simpler code. | |
667 | * | |
668 | * The return type is as for @see function get_recordset. | |
669 | * | |
670 | * @param string $sql the SQL select query to execute. | |
671 | * @param array $params array of sql parameters | |
672 | * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). | |
673 | * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). | |
5212b07f | 674 | * @return moodle_recordset instance |
520b730c | 675 | * @throws dml_exception if error |
676 | */ | |
677 | public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { | |
678 | $limitfrom = (int)$limitfrom; | |
679 | $limitnum = (int)$limitnum; | |
680 | $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom; | |
681 | $limitnum = ($limitnum < 0) ? 0 : $limitnum; | |
682 | if ($limitfrom or $limitnum) { | |
4d276e34 | 683 | if ($limitnum >= 1) { // Only apply TOP clause if we have any limitnum (limitfrom offset is handled later) |
72a3902f | 684 | $fetch = $limitfrom + $limitnum; |
685 | $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i', | |
686 | "\\1SELECT\\2 TOP $fetch", $sql); | |
520b730c | 687 | } |
520b730c | 688 | } |
689 | ||
690 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
691 | $rawsql = $this->emulate_bound_params($sql, $params); | |
692 | ||
693 | $this->query_start($sql, $params, SQL_QUERY_SELECT); | |
5c08b24b | 694 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 695 | $this->query_end($result); |
696 | ||
72a3902f | 697 | if ($limitfrom) { // Skip $limitfrom records |
5c08b24b | 698 | mssql_data_seek($result, $limitfrom); |
520b730c | 699 | } |
700 | ||
701 | return $this->create_recordset($result); | |
702 | } | |
703 | ||
704 | protected function create_recordset($result) { | |
705 | return new mssql_native_moodle_recordset($result); | |
706 | } | |
707 | ||
708 | /** | |
709 | * Get a number of records as an array of objects using a SQL statement. | |
710 | * | |
711 | * Return value as for @see function get_records. | |
712 | * | |
713 | * @param string $sql the SQL select query to execute. The first column of this SELECT statement | |
714 | * must be a unique value (usually the 'id' field), as it will be used as the key of the | |
715 | * returned array. | |
716 | * @param array $params array of sql parameters | |
717 | * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). | |
718 | * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). | |
3503dcad | 719 | * @return array of objects, or empty array if no records were found |
520b730c | 720 | * @throws dml_exception if error |
721 | */ | |
722 | public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { | |
723 | ||
724 | $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum); | |
725 | ||
726 | $results = array(); | |
727 | ||
728 | foreach ($rs as $row) { | |
df5f9165 | 729 | $id = reset($row); |
730 | if (isset($results[$id])) { | |
520b730c | 731 | $colname = key($row); |
732 | 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); | |
733 | } | |
df5f9165 | 734 | $results[$id] = $row; |
520b730c | 735 | } |
736 | $rs->close(); | |
737 | ||
738 | return $results; | |
739 | } | |
740 | ||
741 | /** | |
742 | * Selects records and return values (first field) as an array using a SQL statement. | |
743 | * | |
744 | * @param string $sql The SQL query | |
745 | * @param array $params array of sql parameters | |
3503dcad | 746 | * @return array of values |
520b730c | 747 | * @throws dml_exception if error |
748 | */ | |
749 | public function get_fieldset_sql($sql, array $params=null) { | |
750 | ||
751 | $rs = $this->get_recordset_sql($sql, $params); | |
752 | ||
753 | $results = array(); | |
754 | ||
755 | foreach ($rs as $row) { | |
756 | $results[] = reset($row); | |
757 | } | |
758 | $rs->close(); | |
759 | ||
760 | return $results; | |
761 | } | |
762 | ||
763 | /** | |
764 | * Insert new record into database, as fast as possible, no safety checks, lobs not supported. | |
765 | * @param string $table name | |
766 | * @param mixed $params data record as object or array | |
767 | * @param bool $returnit return it of inserted record | |
768 | * @param bool $bulk true means repeated inserts expected | |
769 | * @param bool $customsequence true if 'id' included in $params, disables $returnid | |
3503dcad | 770 | * @return bool|int true or new id |
520b730c | 771 | * @throws dml_exception if error |
772 | */ | |
773 | public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { | |
774 | if (!is_array($params)) { | |
775 | $params = (array)$params; | |
776 | } | |
777 | ||
778 | $returning = ""; | |
779 | ||
780 | if ($customsequence) { | |
781 | if (!isset($params['id'])) { | |
782 | throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); | |
783 | } | |
784 | $returnid = false; | |
785 | } else { | |
786 | unset($params['id']); | |
787 | if ($returnid) { | |
788 | $returning = "; SELECT SCOPE_IDENTITY()"; | |
789 | } | |
790 | } | |
791 | ||
792 | if (empty($params)) { | |
793 | throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); | |
794 | } | |
795 | ||
796 | $fields = implode(',', array_keys($params)); | |
797 | $qms = array_fill(0, count($params), '?'); | |
798 | $qms = implode(',', $qms); | |
799 | ||
800 | $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms) $returning"; | |
801 | ||
802 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
803 | $rawsql = $this->emulate_bound_params($sql, $params); | |
804 | ||
805 | $this->query_start($sql, $params, SQL_QUERY_INSERT); | |
5c08b24b | 806 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 807 | $this->query_end($result); |
808 | ||
809 | if ($returning !== "") { | |
810 | $row = mssql_fetch_assoc($result); | |
811 | $params['id'] = reset($row); | |
812 | } | |
813 | $this->free_result($result); | |
814 | ||
815 | if (!$returnid) { | |
816 | return true; | |
817 | } | |
818 | ||
819 | return (int)$params['id']; | |
820 | } | |
821 | ||
822 | /** | |
823 | * Insert a record into a table and return the "id" field if required. | |
824 | * | |
825 | * Some conversions and safety checks are carried out. Lobs are supported. | |
826 | * If the return ID isn't required, then this just reports success as true/false. | |
827 | * $data is an object containing needed data | |
828 | * @param string $table The database table to be inserted into | |
829 | * @param object $data A data object with values for one or more fields in the record | |
830 | * @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. | |
3503dcad | 831 | * @return bool|int true or new id |
520b730c | 832 | * @throws dml_exception if error |
833 | */ | |
834 | public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { | |
d8fa8e40 | 835 | $dataobject = (array)$dataobject; |
520b730c | 836 | |
837 | $columns = $this->get_columns($table); | |
838 | $cleaned = array(); | |
839 | ||
840 | foreach ($dataobject as $field => $value) { | |
d8fa8e40 PS |
841 | if ($field === 'id') { |
842 | continue; | |
843 | } | |
520b730c | 844 | if (!isset($columns[$field])) { |
845 | continue; | |
846 | } | |
847 | $column = $columns[$field]; | |
848 | $cleaned[$field] = $this->normalise_value($column, $value); | |
849 | } | |
850 | ||
851 | return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); | |
852 | } | |
853 | ||
854 | /** | |
855 | * Import a record into a table, id field is required. | |
856 | * Safety checks are NOT carried out. Lobs are supported. | |
857 | * | |
858 | * @param string $table name of database table to be inserted into | |
859 | * @param object $dataobject A data object with values for one or more fields in the record | |
860 | * @return bool true | |
861 | * @throws dml_exception if error | |
862 | */ | |
863 | public function import_record($table, $dataobject) { | |
d8fa8e40 | 864 | $dataobject = (array)$dataobject; |
520b730c | 865 | |
866 | $columns = $this->get_columns($table); | |
867 | $cleaned = array(); | |
868 | ||
896a2a91 | 869 | foreach ($dataobject as $field => $value) { |
520b730c | 870 | if (!isset($columns[$field])) { |
871 | continue; | |
872 | } | |
896a2a91 | 873 | $column = $columns[$field]; |
874 | $cleaned[$field] = $this->normalise_value($column, $value); | |
520b730c | 875 | } |
876 | ||
896a2a91 | 877 | // Disable IDENTITY column before inserting record with id |
878 | $sql = 'SET IDENTITY_INSERT {' . $table . '} ON'; // Yes, it' ON!! | |
879 | ||
880 | list($sql, $params, $type) = $this->fix_sql_params($sql, null); | |
881 | ||
882 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
883 | $result = mssql_query($sql, $this->mssql); | |
884 | $this->query_end($result); | |
885 | ||
886 | $this->free_result($result); | |
887 | ||
888 | $insertresult = $this->insert_record_raw($table, $cleaned, false, false, true); | |
889 | ||
890 | // Enable IDENTITY column after inserting record with id | |
891 | $sql = 'SET IDENTITY_INSERT {' . $table . '} OFF'; // Yes, it' OFF!! | |
892 | ||
893 | list($sql, $params, $type) = $this->fix_sql_params($sql, null); | |
894 | ||
895 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
896 | $result = mssql_query($sql, $this->mssql); | |
897 | $this->query_end($result); | |
898 | ||
899 | $this->free_result($result); | |
900 | ||
901 | return $insertresult; | |
520b730c | 902 | } |
903 | ||
904 | /** | |
905 | * Update record in database, as fast as possible, no safety checks, lobs not supported. | |
906 | * @param string $table name | |
907 | * @param mixed $params data record as object or array | |
908 | * @param bool true means repeated updates expected | |
909 | * @return bool true | |
910 | * @throws dml_exception if error | |
911 | */ | |
912 | public function update_record_raw($table, $params, $bulk=false) { | |
d8fa8e40 PS |
913 | $params = (array)$params; |
914 | ||
520b730c | 915 | if (!isset($params['id'])) { |
916 | throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); | |
917 | } | |
918 | $id = $params['id']; | |
919 | unset($params['id']); | |
920 | ||
921 | if (empty($params)) { | |
922 | throw new coding_exception('moodle_database::update_record_raw() no fields found.'); | |
923 | } | |
924 | ||
925 | $sets = array(); | |
926 | foreach ($params as $field=>$value) { | |
927 | $sets[] = "$field = ?"; | |
928 | } | |
929 | ||
930 | $params[] = $id; // last ? in WHERE condition | |
931 | ||
932 | $sets = implode(',', $sets); | |
933 | $sql = "UPDATE {" . $table . "} SET $sets WHERE id = ?"; | |
934 | ||
935 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
936 | $rawsql = $this->emulate_bound_params($sql, $params); | |
937 | ||
938 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); | |
5c08b24b | 939 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 940 | $this->query_end($result); |
941 | ||
942 | $this->free_result($result); | |
943 | return true; | |
944 | } | |
945 | ||
946 | /** | |
947 | * Update a record in a table | |
948 | * | |
949 | * $dataobject is an object containing needed data | |
950 | * Relies on $dataobject having a variable "id" to | |
951 | * specify the record to update | |
952 | * | |
953 | * @param string $table The database table to be checked against. | |
954 | * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. | |
955 | * @param bool true means repeated updates expected | |
956 | * @return bool true | |
957 | * @throws dml_exception if error | |
958 | */ | |
959 | public function update_record($table, $dataobject, $bulk=false) { | |
d8fa8e40 | 960 | $dataobject = (array)$dataobject; |
520b730c | 961 | |
962 | $columns = $this->get_columns($table); | |
963 | $cleaned = array(); | |
964 | ||
965 | foreach ($dataobject as $field => $value) { | |
966 | if (!isset($columns[$field])) { | |
967 | continue; | |
968 | } | |
969 | $column = $columns[$field]; | |
970 | $cleaned[$field] = $this->normalise_value($column, $value); | |
971 | } | |
972 | ||
973 | return $this->update_record_raw($table, $cleaned, $bulk); | |
974 | } | |
975 | ||
976 | /** | |
977 | * Set a single field in every table record which match a particular WHERE clause. | |
978 | * | |
979 | * @param string $table The database table to be checked against. | |
980 | * @param string $newfield the field to set. | |
981 | * @param string $newvalue the value to set the field to. | |
982 | * @param string $select A fragment of SQL to be used in a where clause in the SQL call. | |
983 | * @param array $params array of sql parameters | |
984 | * @return bool true | |
985 | * @throws dml_exception if error | |
986 | */ | |
987 | public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { | |
988 | ||
989 | if ($select) { | |
990 | $select = "WHERE $select"; | |
991 | } | |
992 | if (is_null($params)) { | |
993 | $params = array(); | |
994 | } | |
995 | ||
996 | /// Get column metadata | |
997 | $columns = $this->get_columns($table); | |
998 | $column = $columns[$newfield]; | |
999 | ||
1000 | $newvalue = $this->normalise_value($column, $newvalue); | |
1001 | ||
1002 | if (is_null($newvalue)) { | |
1003 | $newfield = "$newfield = NULL"; | |
1004 | } else { | |
1005 | $newfield = "$newfield = ?"; | |
1006 | array_unshift($params, $newvalue); | |
1007 | } | |
1008 | $sql = "UPDATE {" . $table . "} SET $newfield $select"; | |
1009 | ||
1010 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
1011 | $rawsql = $this->emulate_bound_params($sql, $params); | |
1012 | ||
1013 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); | |
5c08b24b | 1014 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 1015 | $this->query_end($result); |
1016 | ||
1017 | $this->free_result($result); | |
1018 | ||
1019 | return true; | |
1020 | } | |
1021 | ||
1022 | /** | |
1023 | * Delete one or more records from a table which match a particular WHERE clause. | |
1024 | * | |
1025 | * @param string $table The database table to be checked against. | |
1026 | * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). | |
1027 | * @param array $params array of sql parameters | |
1028 | * @return bool true | |
1029 | * @throws dml_exception if error | |
1030 | */ | |
1031 | public function delete_records_select($table, $select, array $params=null) { | |
1032 | ||
1033 | if ($select) { | |
1034 | $select = "WHERE $select"; | |
1035 | } | |
1036 | ||
1037 | $sql = "DELETE FROM {" . $table . "} $select"; | |
1038 | ||
1039 | list($sql, $params, $type) = $this->fix_sql_params($sql, $params); | |
1040 | $rawsql = $this->emulate_bound_params($sql, $params); | |
1041 | ||
1042 | $this->query_start($sql, $params, SQL_QUERY_UPDATE); | |
5c08b24b | 1043 | $result = mssql_query($rawsql, $this->mssql); |
520b730c | 1044 | $this->query_end($result); |
1045 | ||
1046 | $this->free_result($result); | |
1047 | ||
1048 | return true; | |
1049 | } | |
1050 | ||
1051 | /// SQL helper functions | |
1052 | ||
1053 | public function sql_bitxor($int1, $int2) { | |
1054 | return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')'; | |
1055 | } | |
1056 | ||
1057 | public function sql_cast_char2int($fieldname, $text=false) { | |
1058 | return ' CAST(' . $fieldname . ' AS INT) '; | |
1059 | } | |
1060 | ||
1061 | public function sql_ceil($fieldname) { | |
1062 | return ' CEILING(' . $fieldname . ')'; | |
1063 | } | |
1064 | ||
6055f89d PS |
1065 | |
1066 | protected function get_collation() { | |
1067 | if (isset($this->collation)) { | |
1068 | return $this->collation; | |
1069 | } | |
1070 | if (!empty($this->dboptions['dbcollation'])) { | |
1071 | // perf speedup | |
1072 | $this->collation = $this->dboptions['dbcollation']; | |
1073 | return $this->collation; | |
1074 | } | |
1075 | ||
1076 | // make some default | |
1077 | $this->collation = 'Latin1_General_CI_AI'; | |
1078 | ||
1079 | $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation"; | |
1080 | $this->query_start($sql, null, SQL_QUERY_AUX); | |
e016d126 | 1081 | $result = mssql_query($sql, $this->mssql); |
6055f89d PS |
1082 | $this->query_end($result); |
1083 | ||
1084 | if ($result) { | |
e016d126 | 1085 | if ($rawcolumn = mssql_fetch_assoc($result)) { |
6055f89d PS |
1086 | $this->collation = reset($rawcolumn); |
1087 | } | |
1088 | $this->free_result($result); | |
1089 | } | |
1090 | ||
1091 | return $this->collation; | |
1092 | } | |
1093 | ||
6055f89d PS |
1094 | /** |
1095 | * Returns 'LIKE' part of a query. | |
1096 | * | |
1097 | * @param string $fieldname usually name of the table column | |
1098 | * @param string $param usually bound query parameter (?, :named) | |
1099 | * @param bool $casesensitive use case sensitive search | |
1100 | * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive) | |
1101 | * @param string $escapechar escape char for '%' and '_' | |
1102 | * @return string SQL code fragment | |
1103 | */ | |
1104 | public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') { | |
1105 | if (strpos($param, '%') !== false) { | |
1106 | debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)'); | |
1107 | } | |
1108 | ||
1109 | $collation = $this->get_collation(); | |
1110 | ||
1111 | if ($casesensitive) { | |
1112 | $collation = str_replace('_CI', '_CS', $collation); | |
1113 | } else { | |
1114 | $collation = str_replace('_CS', '_CI', $collation); | |
1115 | } | |
1116 | if ($accentsensitive) { | |
1117 | $collation = str_replace('_AI', '_AS', $collation); | |
1118 | } else { | |
1119 | $collation = str_replace('_AS', '_AI', $collation); | |
1120 | } | |
1121 | ||
1122 | return "$fieldname COLLATE $collation LIKE $param ESCAPE '$escapechar'"; | |
1123 | } | |
1124 | ||
520b730c | 1125 | public function sql_concat() { |
1126 | $arr = func_get_args(); | |
1127 | foreach ($arr as $key => $ele) { | |
1128 | $arr[$key] = ' CAST(' . $ele . ' AS VARCHAR(255)) '; | |
1129 | } | |
1130 | $s = implode(' + ', $arr); | |
1131 | if ($s === '') { | |
1132 | return " '' "; | |
1133 | } | |
1134 | return " $s "; | |
1135 | } | |
1136 | ||
1137 | public function sql_concat_join($separator="' '", $elements=array()) { | |
1138 | for ($n=count($elements)-1; $n > 0 ; $n--) { | |
1139 | array_splice($elements, $n, 0, $separator); | |
1140 | } | |
1141 | $s = implode(' + ', $elements); | |
1142 | if ($s === '') { | |
1143 | return " '' "; | |
1144 | } | |
1145 | return " $s "; | |
1146 | } | |
1147 | ||
1148 | public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { | |
1149 | if ($textfield) { | |
63b3d8ab | 1150 | return ' (' . $this->sql_compare_text($fieldname) . " = '') "; |
520b730c | 1151 | } else { |
63b3d8ab | 1152 | return " ($fieldname = '') "; |
520b730c | 1153 | } |
1154 | } | |
1155 | ||
1156 | /** | |
1157 | * Returns the SQL text to be used to calculate the length in characters of one expression. | |
1158 | * @param string fieldname or expression to calculate its length in characters. | |
1159 | * @return string the piece of SQL code to be used in the statement. | |
1160 | */ | |
1161 | public function sql_length($fieldname) { | |
1162 | return ' LEN(' . $fieldname . ')'; | |
1163 | } | |
1164 | ||
1165 | public function sql_order_by_text($fieldname, $numchars=32) { | |
1166 | return ' CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')'; | |
1167 | } | |
1168 | ||
1169 | /** | |
1170 | * Returns the SQL for returning searching one string for the location of another. | |
1171 | */ | |
1172 | public function sql_position($needle, $haystack) { | |
1173 | return "CHARINDEX(($needle), ($haystack))"; | |
1174 | } | |
1175 | ||
1176 | /** | |
ab62bd23 | 1177 | * Returns the proper substr() SQL text used to extract substrings from DB |
520b730c | 1178 | * NOTE: this was originally returning only function name |
1179 | * | |
1180 | * @param string $expr some string field, no aggregates | |
4d276e34 PS |
1181 | * @param mixed $start integer or expression evaluating to int |
1182 | * @param mixed $length optional integer or expression evaluating to int | |
520b730c | 1183 | * @return string sql fragment |
1184 | */ | |
1185 | public function sql_substr($expr, $start, $length=false) { | |
1186 | if (count(func_get_args()) < 2) { | |
1187 | throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originaly this function wa | |
1188 | s only returning name of SQL substring function, it now requires all parameters.'); | |
1189 | } | |
1190 | if ($length === false) { | |
1191 | return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))"; | |
1192 | } else { | |
1193 | return "SUBSTRING($expr, $start, $length)"; | |
1194 | } | |
1195 | } | |
1196 | ||
1197 | /// session locking | |
1198 | ||
1199 | public function session_lock_supported() { | |
6d682bae | 1200 | return true; |
520b730c | 1201 | } |
1202 | ||
1203 | public function get_session_lock($rowid) { | |
520b730c | 1204 | if (!$this->session_lock_supported()) { |
1205 | return; | |
1206 | } | |
520b730c | 1207 | parent::get_session_lock($rowid); |
6d682bae | 1208 | |
1209 | $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; | |
1210 | $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session', 120000"; | |
520b730c | 1211 | $this->query_start($sql, null, SQL_QUERY_AUX); |
6d682bae | 1212 | $result = mssql_query($sql, $this->mssql); |
520b730c | 1213 | $this->query_end($result); |
1214 | ||
6d682bae | 1215 | $this->free_result($result); |
520b730c | 1216 | } |
1217 | ||
1218 | public function release_session_lock($rowid) { | |
1219 | if (!$this->session_lock_supported()) { | |
1220 | return; | |
1221 | } | |
1222 | parent::release_session_lock($rowid); | |
1223 | ||
6d682bae | 1224 | $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; |
982fd65f | 1225 | $sql = "sp_releaseapplock '$fullname', 'Session'"; |
520b730c | 1226 | $this->query_start($sql, null, SQL_QUERY_AUX); |
6d682bae | 1227 | $result = mssql_query($sql, $this->mssql); |
520b730c | 1228 | $this->query_end($result); |
1229 | ||
6d682bae | 1230 | $this->free_result($result); |
520b730c | 1231 | } |
1232 | ||
1233 | /// transactions | |
1234 | ||
1235 | /** | |
d5a8d9aa PS |
1236 | * Driver specific start of real database transaction, |
1237 | * this can not be used directly in code. | |
1238 | * @return void | |
520b730c | 1239 | */ |
12ffe203 EL |
1240 | protected function begin_transaction() { |
1241 | // requires database to run with READ_COMMITTED_SNAPSHOT ON | |
520b730c | 1242 | $sql = "BEGIN TRANSACTION"; // Will be using READ COMMITTED isolation |
1243 | $this->query_start($sql, NULL, SQL_QUERY_AUX); | |
1244 | $result = mssql_query($sql, $this->mssql); | |
1245 | $this->query_end($result); | |
1246 | ||
1247 | $this->free_result($result); | |
520b730c | 1248 | } |
1249 | ||
1250 | /** | |
d5a8d9aa PS |
1251 | * Driver specific commit of real database transaction, |
1252 | * this can not be used directly in code. | |
1253 | * @return void | |
520b730c | 1254 | */ |
12ffe203 | 1255 | protected function commit_transaction() { |
520b730c | 1256 | $sql = "COMMIT TRANSACTION"; |
1257 | $this->query_start($sql, NULL, SQL_QUERY_AUX); | |
1258 | $result = mssql_query($sql, $this->mssql); | |
1259 | $this->query_end($result); | |
1260 | ||
1261 | $this->free_result($result); | |
520b730c | 1262 | } |
1263 | ||
1264 | /** | |
d5a8d9aa PS |
1265 | * Driver specific abort of real database transaction, |
1266 | * this can not be used directly in code. | |
1267 | * @return void | |
520b730c | 1268 | */ |
12ffe203 | 1269 | protected function rollback_transaction() { |
520b730c | 1270 | $sql = "ROLLBACK TRANSACTION"; |
1271 | $this->query_start($sql, NULL, SQL_QUERY_AUX); | |
1272 | $result = mssql_query($sql, $this->mssql); | |
1273 | $this->query_end($result); | |
1274 | ||
1275 | $this->free_result($result); | |
520b730c | 1276 | } |
1277 | } |