MDL-17491 dml tests4oracle empties/null/hack added.
[moodle.git] / lib / dml / oci_native_moodle_database.php
CommitLineData
49926145 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 oci class representing moodle database interface.
21 *
22 * @package moodlecore
23 * @subpackage DML
24 * @copyright 2008 Petr Skoda (http://skodak.org)
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
66e75f8d 27
28require_once($CFG->libdir.'/dml/moodle_database.php');
29require_once($CFG->libdir.'/dml/oci_native_moodle_recordset.php');
30
31/**
32 * Native oci class representing moodle database interface.
8089f8f6 33 *
34 * One complete reference for PHP + OCI:
35 * http://www.oracle.com/technology/tech/php/underground-php-oracle-manual.html
66e75f8d 36 */
37class oci_native_moodle_database extends moodle_database {
38
39 protected $oci = null;
66e75f8d 40
7cfaef13 41 private $last_stmt_error = null; // To store stmt errors and enable get_last_error() to detect them
8089f8f6 42 private $commit_status = OCI_COMMIT_ON_SUCCESS; // Autocommit ON by default. Switching to OFF (OCI_DEFAULT)
43 // when playing with transactions
66e75f8d 44
45 /**
46 * Detects if all needed PHP stuff installed.
47 * Note: can be used before connect()
48 * @return mixed true if ok, string if something
49 */
50 public function driver_installed() {
51 if (!extension_loaded('oci8')) {
52 return get_string('ociextensionisnotpresentinphp', 'install');
53 }
54 return true;
55 }
56
57 /**
58 * Returns database family type - describes SQL dialect
59 * Note: can be used before connect()
60 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
61 */
62 public function get_dbfamily() {
63 return 'oracle';
64 }
65
66 /**
67 * Returns more specific database driver type
68 * Note: can be used before connect()
69 * @return string db type mysql, oci, postgres7
70 */
71 protected function get_dbtype() {
72 return 'oci';
73 }
74
75 /**
76 * Returns general database library name
77 * Note: can be used before connect()
78 * @return string db type adodb, pdo, native
79 */
80 protected function get_dblibrary() {
81 return 'native';
82 }
83
84 /**
85 * Returns localised database type name
86 * Note: can be used before connect()
87 * @return string
88 */
89 public function get_name() {
90 return get_string('nativeoci', 'install'); // TODO: localise
91 }
92
3b093310 93 /**
94 * Returns localised database configuration help.
95 * Note: can be used before connect()
96 * @return string
97 */
98 public function get_configuration_help() {
99 return get_string('nativeocihelp', 'install');
100 }
101
66e75f8d 102 /**
103 * Returns localised database description
104 * Note: can be used before connect()
105 * @return string
106 */
107 public function get_configuration_hints() {
108 return get_string('databasesettingssub_oci', 'install'); // TODO: l
109 }
110
111 /**
112 * Connect to db
113 * Must be called before other methods.
114 * @param string $dbhost
115 * @param string $dbuser
116 * @param string $dbpass
117 * @param string $dbname
118 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
119 * @param array $dboptions driver specific options
120 * @return bool true
121 * @throws dml_connection_exception if error
122 */
123 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
124 if ($prefix == '' and !$this->external) {
125 //Enforce prefixes for everybody but mysql
126 throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
127 }
128 if (!$this->external and strlen($prefix) > 2) {
129 //Max prefix length for Oracle is 2cc
130 $a = (object)array('dbfamily'=>'oracle', 'maxlength'=>2);
131 throw new dml_exception('prefixtoolong', $a);
132 }
133
134 $driverstatus = $this->driver_installed();
135
136 if ($driverstatus !== true) {
137 throw new dml_exception('dbdriverproblem', $driverstatus);
138 }
139
140 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
3b093310 141 unset($this->dboptions['dbsocket']);
66e75f8d 142
143 $pass = addcslashes($this->dbpass, "'\\");
144
145 if (empty($this->dbhost)) {
146 // old style full address
147 } else {
148 if (empty($this->dboptions['dbport'])) {
149 $this->dboptions['dbport'] = 1521;
150 }
151 $this->dbname = '//'.$this->dbhost.':'.$this->dboptions['dbport'].'/'.$this->dbname;
152 }
153
154 ob_start();
155 if (empty($this->dboptions['dbpersit'])) {
8089f8f6 156 $this->oci = oci_connect($this->dbuser, $this->dbpass, $this->dbname, 'AL32UTF8');
66e75f8d 157 } else {
8089f8f6 158 $this->oci = oci_pconnect($this->dbuser, $this->dbpass, $this->dbname, 'AL32UTF8');
66e75f8d 159 }
160 $dberr = ob_get_contents();
161 ob_end_clean();
162
163
164 if ($this->oci === false) {
165 $this->oci = null;
166 $e = oci_error();
167 if (isset($e['message'])) {
168 $dberr = $e['message'];
169 }
170 throw new dml_connection_exception($dberr);
171 }
172
173 //note: do not send "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'" !
174 // instead fix our PHP code to convert "," to "." properly!
175
176 return true;
177 }
178
179 /**
180 * Close database connection and release all resources
181 * and memory (especially circular memory references).
182 * Do NOT use connect() again, create a new instance if needed.
183 */
184 public function dispose() {
8fdb24f0 185 parent::dispose(); // Call parent dispose to write/close session and other common stuff before clossing conn
66e75f8d 186 if ($this->oci) {
187 oci_close($this->oci);
188 $this->oci = null;
189 }
66e75f8d 190 }
191
192
193 /**
194 * Called before each db query.
195 * @param string $sql
196 * @param array array of parameters
197 * @param int $type type of query
198 * @param mixed $extrainfo driver specific extra information
199 * @return void
200 */
201 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
202 parent::query_start($sql, $params, $type, $extrainfo);
66e75f8d 203 }
204
205 /**
206 * Called immediately after each db query.
207 * @param mixed db specific result
208 * @return void
209 */
210 protected function query_end($result, $stmt=null) {
66e75f8d 211 if ($stmt and $result === false) {
7cfaef13 212 // Look for stmt error and store it
213 if (is_resource($stmt)) {
214 $e = oci_error($stmt);
215 if ($e !== false) {
216 $this->last_stmt_error = $e['message'];
217 }
218 }
66e75f8d 219 oci_free_statement($stmt);
220 }
221 parent::query_end($result);
222 }
223
224 /**
225 * Returns database server info array
226 * @return array
227 */
228 public function get_server_info() {
229 static $info = null; // TODO: move to real object property
230
231 if (is_null($info)) {
232 $this->query_start("--oci_server_version()", null, SQL_QUERY_AUX);
233 $description = oci_server_version($this->oci);
234 $this->query_end(true);
235 preg_match('/(\d+\.)+\d+/', $description, $matches);
236 $info = array('description'=>$description, 'version'=>$matches[0]);
237 }
238
239 return $info;
240 }
241
242 protected function is_min_version($version) {
243 $server = $this->get_server_info();
244 $server = $server['version'];
245 return version_compare($server, $version, '>=');
246 }
247
248 /**
249 * Returns supported query parameter types
250 * @return bitmask
251 */
252 protected function allowed_param_types() {
253 return SQL_PARAMS_NAMED;
254 }
255
256 /**
257 * Returns last error reported by database engine.
258 */
259 public function get_last_error() {
7cfaef13 260 $error = false;
261 // First look for any previously saved stmt error
262 if (!empty($this->last_stmt_error)) {
263 $error = $this->last_stmt_error;
264 $this->last_stmt_error = null;
265 } else { // Now try connection error
266 $e = oci_error($this->oci);
267 if ($e !== false) {
268 $error = $e['message'];
269 }
66e75f8d 270 }
7cfaef13 271 return $error;
66e75f8d 272 }
273
274 protected function parse_query($sql) {
275 $stmt = oci_parse($this->oci, $sql);
8089f8f6 276 if ($stmt == false) {
66e75f8d 277 throw new dml_connection_exception('Can not parse sql query'); //TODO: maybe add better info
278 }
279 return $stmt;
280 }
281
282 /**
283 * Return tables in database WITHOUT current prefix
284 * @return array of table names in lowercase and without prefix
285 */
117679db 286 public function get_tables($usecache=true) {
66e75f8d 287 $tables = array();
288 $prefix = str_replace('_', "\\_", strtoupper($this->prefix));
289 $sql = "SELECT TABLE_NAME
290 FROM CAT
291 WHERE TABLE_TYPE='TABLE'
292 AND TABLE_NAME NOT LIKE 'BIN\$%'
293 AND TABLE_NAME LIKE '$prefix%' ESCAPE '\\'";
294 $this->query_start($sql, null, SQL_QUERY_AUX);
295 $stmt = $this->parse_query($sql);
8089f8f6 296 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 297 $this->query_end($result, $stmt);
298 $records = null;
299 oci_fetch_all($stmt, $records, 0, -1, OCI_ASSOC);
300 oci_free_statement($stmt);
301 $records = array_map('strtolower', $records['TABLE_NAME']);
302 foreach ($records as $tablename) {
303 if (strpos($tablename, $this->prefix) !== 0) {
304 continue;
305 }
306 $tablename = substr($tablename, strlen($this->prefix));
307 $tables[$tablename] = $tablename;
308 }
309
310 return $tables;
311 }
312
313 /**
314 * Return table indexes - everything lowercased
315 * @return array of arrays
316 */
317 public function get_indexes($table) {
318 $indexes = array();
319 $tablename = strtoupper($this->prefix.$table);
320
321 $sql = "SELECT i.INDEX_NAME, i.UNIQUENESS, c.COLUMN_POSITION, c.COLUMN_NAME, ac.CONSTRAINT_TYPE
322 FROM ALL_INDEXES i
323 JOIN ALL_IND_COLUMNS c ON c.INDEX_NAME=i.INDEX_NAME
324 LEFT JOIN ALL_CONSTRAINTS ac ON (ac.TABLE_NAME=i.TABLE_NAME AND ac.CONSTRAINT_NAME=i.INDEX_NAME AND ac.CONSTRAINT_TYPE='P')
325 WHERE i.TABLE_NAME = '$tablename'
326 ORDER BY i.INDEX_NAME, c.COLUMN_POSITION";
327
328 $stmt = $this->parse_query($sql);
8089f8f6 329 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 330 $this->query_end($result, $stmt);
331 $records = null;
332 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
333 oci_free_statement($stmt);
334
335 foreach ($records as $record) {
336 if ($record['CONSTRAINT_TYPE'] === 'P') {
337 //ignore for now;
338 continue;
339 }
340 $indexname = strtolower($record['INDEX_NAME']);
341 if (!isset($indexes[$indexname])) {
342 $indexes[$indexname] = array('primary' => ($record['CONSTRAINT_TYPE'] === 'P'),
343 'unique' => ($record['UNIQUENESS'] === 'UNIQUE'),
344 'columns' => array());
345 }
346 $indexes[$indexname]['columns'][] = strtolower($record['COLUMN_NAME']);
347 }
348
349 return $indexes;
350 }
351
352 /**
353 * Returns datailed information about columns in table. This information is cached internally.
354 * @param string $table name
355 * @param bool $usecache
356 * @return array array of database_column_info objects indexed with column names
357 */
358 public function get_columns($table, $usecache=true) {
359 if ($usecache and isset($this->columns[$table])) {
360 return $this->columns[$table];
361 }
362
363 $this->columns[$table] = array();
364
365 $tablename = strtoupper($this->prefix.$table);
366
367 $sql = "SELECT CNAME, COLTYPE, WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL
368 FROM COL
369 WHERE TNAME='$tablename'
370 ORDER BY COLNO";
371
372 $this->query_start($sql, null, SQL_QUERY_AUX);
373 $stmt = $this->parse_query($sql);
8089f8f6 374 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 375 $this->query_end($result, $stmt);
376 $records = null;
377 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
378 oci_free_statement($stmt);
379
380 if (!$records) {
381 return array();
382 }
383 foreach ($records as $rawcolumn) {
384 $rawcolumn = (object)$rawcolumn;
385
386 $info = new object();
387 $info->name = strtolower($rawcolumn->CNAME);
388 $matches = null;
389
390 if ($rawcolumn->COLTYPE === 'VARCHAR2'
391 or $rawcolumn->COLTYPE === 'VARCHAR'
392 or $rawcolumn->COLTYPE === 'NVARCHAR2'
393 or $rawcolumn->COLTYPE === 'NVARCHAR'
394 or $rawcolumn->COLTYPE === 'CHAR'
395 or $rawcolumn->COLTYPE === 'NCHAR') {
396 //TODO add some basic enum support here
397 $info->type = $rawcolumn->COLTYPE;
398 $info->meta_type = 'C';
399 $info->max_length = $rawcolumn->WIDTH;
400 $info->scale = null;
401 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL');
402 $info->has_default = !is_null($rawcolumn->DEFAULTVAL);
403 if ($info->has_default) {
404 // this is hacky :-(
405 if ($rawcolumn->DEFAULTVAL === 'NULL') {
406 $info->default_value = null;
407 } else if ($rawcolumn->DEFAULTVAL === "' ' ") {
408 $info->default_value = "";
409 } else {
410 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
411 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
412 }
413 } else {
414 $info->default_value = null;
415 }
416 $info->primary_key = false;
417 $info->binary = false;
418 $info->unsigned = null;
419 $info->auto_increment= false;
420 $info->unique = null;
421
422 } else if ($rawcolumn->COLTYPE === 'NUMBER') {
423 $info->type = $rawcolumn->COLTYPE;
424 $info->max_length = $rawcolumn->PRECISION;
425 $info->binary = false;
426 if ($rawcolumn->SCALE == 0) {
427 // integer
428 if ($info->name === 'id') {
429 $info->primary_key = true;
430 $info->meta_type = 'R';
431 $info->unique = true;
432 $info->auto_increment= true;
433 $info->has_default = false;
434 } else {
435 $info->primary_key = false;
436 $info->meta_type = 'I';
437 $info->unique = null;
438 $info->auto_increment= false;
439 }
440 $info->scale = null;
441
442 } else {
443 //float
444 $info->meta_type = 'N';
445 $info->primary_key = false;
446 $info->unsigned = null;
447 $info->auto_increment= false;
448 $info->unique = null;
449 $info->scale = $rawcolumn->SCALE;
450 }
451 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL');
452 $info->has_default = !is_null($rawcolumn->DEFAULTVAL);
453 if ($info->has_default) {
454 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
455 } else {
456 $info->default_value = null;
457 }
458
459 } else if ($rawcolumn->COLTYPE === 'FLOAT') {
460 $info->type = $rawcolumn->COLTYPE;
461 $info->max_length = (int)($rawcolumn->PRECISION * 3.32193);
462 $info->primary_key = false;
463 $info->meta_type = 'N';
464 $info->unique = null;
465 $info->auto_increment= false;
466 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL');
467 $info->has_default = !is_null($rawcolumn->DEFAULTVAL);
468 if ($info->has_default) {
469 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
470 } else {
471 $info->default_value = null;
472 }
473
474 } else if ($rawcolumn->COLTYPE === 'CLOB'
475 or $rawcolumn->COLTYPE === 'NCLOB') {
476 $info->type = $rawcolumn->COLTYPE;
477 $info->meta_type = 'X';
478 $info->max_length = $rawcolumn->WIDTH;
479 $info->scale = null;
480 $info->scale = null;
481 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL');
482 $info->has_default = !is_null($rawcolumn->DEFAULTVAL);
483 if ($info->has_default) {
484 // this is hacky :-(
485 if ($rawcolumn->DEFAULTVAL === 'NULL') {
486 $info->default_value = null;
487 } else if ($rawcolumn->DEFAULTVAL === "' ' ") {
488 $info->default_value = "";
489 } else {
490 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
491 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
492 }
493 } else {
494 $info->default_value = null;
495 }
496 $info->primary_key = false;
497 $info->binary = false;
498 $info->unsigned = null;
499 $info->auto_increment= false;
500 $info->unique = null;
501
502 } else if ($rawcolumn->COLTYPE === 'BLOB') {
503 $info->type = $rawcolumn->COLTYPE;
504 $info->meta_type = 'B';
505 $info->max_length = $rawcolumn->WIDTH;
506 $info->scale = null;
507 $info->scale = null;
508 $info->not_null = ($rawcolumn->NULLS === 'NOT NULL');
509 $info->has_default = !is_null($rawcolumn->DEFAULTVAL);
510 if ($info->has_default) {
511 // this is hacky :-(
512 if ($rawcolumn->DEFAULTVAL === 'NULL') {
513 $info->default_value = null;
514 } else if ($rawcolumn->DEFAULTVAL === "' ' ") {
515 $info->default_value = "";
516 } else {
517 $info->default_value = trim($rawcolumn->DEFAULTVAL); // remove trailing space
518 $info->default_value = substr($info->default_value, 1, strlen($info->default_value)-2); //trim ''
519 }
520 } else {
521 $info->default_value = null;
522 }
523 $info->primary_key = false;
524 $info->binary = true;
525 $info->unsigned = null;
526 $info->auto_increment= false;
527 $info->unique = null;
528
529 } else {
530 // unknown type - sorry
531 $info->type = $rawcolumn->COLTYPE;
532 $info->meta_type = '?';
533 }
534
535 $this->columns[$table][$info->name] = new database_column_info($info);
536 }
537
538 return $this->columns[$table];
539 }
540
52a01626 541 /**
542 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
543 *
544 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
545 * @param mixed $value value we are going to normalise
546 * @return mixed the normalised value
547 */
548 private function normalise_value($column, $value) {
549 if (is_bool($value)) { /// Always, convert boolean to int
550 $value = (int)$value;
551
552 } else if ($column->meta_type == 'B') { // CLOB detected, we return 'blob' array instead of raw value to allow
553 if (!is_null($value)) { // binding/executing code later to know about its nature
554 $value = array('blob' => $value);
555 }
556
557 } else if ($column->meta_type == 'X' && strlen($value) > 4000) { // CLOB detected (>4000 optimisation), we return 'clob'
558 if (!is_null($value)) { // array instead of raw value to allow binding/
559 $value = array('clob' => (string)$value); // executing code later to know about its nature
560 }
561
562 } else if ($value === '') {
563 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
564 $value = 0; // prevent '' problems in numeric fields
565 }
566 }
567 return $value;
568 }
569
8089f8f6 570 /**
571 * This function will handle all the records before being inserted/updated to DB for Oracle
572 * installations. This is because the "special feature" of Oracle where the empty string is
573 * equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
574 *
575 * Once Moodle DB will be free of this sort of false NOT NULLS, this hack could be removed safely
576 *
577 * Note that this function is 100% private and should be used, exclusively by DML functions
578 * in this file. Also, this is considered a DIRTY HACK to be removed when possible. (stronk7)
579 *
580 * This function is private and must not be used outside this driver at all
581 *
582 * @param $table string the table where the record is going to be inserted/updated (without prefix)
583 * @param $field string the field where the record is going to be inserted/updated
584 * @param $value mixed the value to be inserted/updated
585 */
586 private function oracle_dirty_hack ($table, $field, $value) {
587
588 /// Get metadata
589 $columns = $this->get_columns($table);
590 if (!isset($columns[$field])) {
591 return $value;
592 }
593 $column = $columns[$field];
594
595 /// For Oracle DB, empty strings are converted to NULLs in DB
596 /// and this breaks a lot of NOT NULL columns currenty Moodle. In the future it's
597 /// planned to move some of them to NULL, if they must accept empty values and this
598 /// piece of code will become less and less used. But, for now, we need it.
599 /// What we are going to do is to examine all the data being inserted and if it's
600 /// an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
601 /// such data in the best form possible ("0" for booleans and numbers and " " for the
602 /// rest of strings. It isn't optimal, but the only way to do so.
603 /// In the oppsite, when retrieving records from Oracle, we'll decode " " back to
604 /// empty strings to allow everything to work properly. DIRTY HACK.
605
606 /// If the field ins't VARCHAR or CLOB, skip
607 if ($column->meta_type != 'C' and $column->meta_type != 'X') {
608 return $value;
609 }
610
611 /// If the field isn't NOT NULL, skip (it's nullable, so accept empty-null values)
612 if (!$column->not_null) {
613 return $value;
614 }
615
616 /// If the value isn't empty, skip
617 if (!empty($value)) {
618 return $value;
619 }
620
621 /// Now, we have one empty value, going to be inserted to one NOT NULL, VARCHAR2 or CLOB field
622 /// Try to get the best value to be inserted
623
624 /// The '0' string doesn't need any transformation, skip
625 if ($value === '0') {
626 return $value;
627 }
628
629 /// Transformations start
630 if (gettype($value) == 'boolean') {
631 return '0'; /// Transform false to '0' that evaluates the same for PHP
632
633 } else if (gettype($value) == 'integer') {
634 return '0'; /// Transform 0 to '0' that evaluates the same for PHP
635
636 } else if (gettype($value) == 'NULL') {
637 return '0'; /// Transform NULL to '0' that evaluates the same for PHP
638
639 } else if ($value === '') {
640 return ' '; /// Transform '' to ' ' that DONT'T EVALUATE THE SAME
641 /// (we'll transform back again on get_records_XXX functions and others)!!
642 }
643
644 /// Fail safe to original value
645 return $value;
646 }
647
66e75f8d 648 /**
649 * Is db in unicode mode?
650 * @return bool
651 */
652 public function setup_is_unicodedb() {
653 $sql = "SELECT VALUE
654 FROM NLS_DATABASE_PARAMETERS
655 WHERE PARAMETER = 'NLS_CHARACTERSET'";
656 $this->query_start($sql, null, SQL_QUERY_AUX);
657 $stmt = $this->parse_query($sql);
8089f8f6 658 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 659 $this->query_end($result, $stmt);
660 $records = null;
661 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN);
662 oci_free_statement($stmt);
663
664 return (isset($records['VALUE'][0]) and $records['VALUE'][0] === 'AL32UTF8');
665 }
666
667 /**
668 * Do NOT use in code, to be used by database_manager only!
669 * @param string $sql query
670 * @return bool true
671 * @throws dml_exception if error
672 */
673 public function change_database_structure($sql) {
117679db 674 $this->reset_caches();
66e75f8d 675
676 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
677 $stmt = $this->parse_query($sql);
8089f8f6 678 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 679 $this->query_end($result, $stmt);
680 oci_free_statement($stmt);
681
682 return true;
683 }
684
d3f9913a 685 protected function bind_params($stmt, array $params=null, $tablename=null) {
66e75f8d 686 $descriptors = array();
687 if ($params) {
688 $columns = array();
689 if ($tablename) {
690 $columns = $this->get_columns($tablename);
691 }
52a01626 692 foreach($params as $key => $value) {
693 // First of all, handle already detected LOBs
694 if (is_array($value)) { // Let's go to bind special cases (lob descriptors)
695 if (isset($value['clob'])) {
696 $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
697 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB);
698 $lob->writeTemporary($params[$key]['clob'], OCI_TEMP_CLOB);
699 $descriptors[] = $lob;
700 continue; // Column binding finished, go to next one
701 } else if (isset($value['blob'])) {
702 $lob = oci_new_descriptor($this->oci, OCI_DTYPE_LOB);
703 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB);
704 $lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB);
705 $descriptors[] = $lob;
706 continue; // Column binding finished, go to next one
707 }
708 }
709 // TODO: Put propper types and length is possible (enormous speedup)
710 // Arrived here, continue with standard processing, using metadata if possible
66e75f8d 711 if (isset($columns[$key])) {
712 $type = $columns[$key]->meta_type;
713 $maxlength = $columns[$key]->max_length;
714 } else {
715 $type = '?';
716 $maxlength = -1;
717 }
718 switch ($type) {
719 case 'I':
720 case 'R':
721 case 'N':
722 $params[$key] = (int)$value;
52a01626 723 oci_bind_by_name($stmt, $key, $params[$key]);
66e75f8d 724 break;
725 case 'F':
726 $params[$key] = (float)$value;
52a01626 727 oci_bind_by_name($stmt, $key, $params[$key]);
66e75f8d 728 break;
729
730 case 'B':
52a01626 731 // TODO: Shouldn't arrive here ever! Blobs already bound above. Exception!
732 break;
66e75f8d 733
734 case 'X':
52a01626 735 if (strlen($value) > 4000) {
736 // TODO: Shouldn't arrive here ever! BIG Clobs already bound above. Exception!
66e75f8d 737 }
52a01626 738 default: // Bind as CHAR
739 oci_bind_by_name($stmt, $key, $params[$key]);
66e75f8d 740 }
741 }
742 }
743 return $descriptors;
744 }
745
746 protected function free_descriptors($descriptors) {
747 foreach ($descriptors as $descriptor) {
748 oci_free_descriptor($descriptor);
749 }
750 }
751
752 /**
753 * This function is used to convert all the Oracle 1-space defaults to the empty string
754 * like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
755 * fields will be out from Moodle.
756 * @param string the string to be converted to '' (empty string) if it's ' ' (one space)
757 * @param mixed the key of the array in case we are using this function from array_walk,
758 * defaults to null for other (direct) uses
759 * @return boolean always true (the converted variable is returned by reference)
760 */
761 public static function onespace2empty(&$item, $key=null) {
762 $item = ($item === ' ') ? '' : $item;
763 return true;
764 }
765
766 /**
767 * Execute general sql query. Should be used only when no other method suitable.
768 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
769 * @param string $sql query
770 * @param array $params query parameters
771 * @return bool true
772 * @throws dml_exception if error
773 */
774 public function execute($sql, array $params=null) {
775 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
776
777 if (strpos($sql, ';') !== false) {
778 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
779 }
780
781 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
782 $stmt = $this->parse_query($sql);
783 $this->bind_params($stmt, $params);
8089f8f6 784 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 785 $this->query_end($result, $stmt);
786 oci_free_statement($stmt);
787
788 return true;
789 }
790
791 /**
792 * Get a single database record as an object using a SQL statement.
793 *
af12ea93 794 * The SQL statement should normally only return one record.
795 * It is recommended to use get_records_sql() if more matches possible!
66e75f8d 796 *
797 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
798 * @param array $params array of sql parameters
e6c6531c 799 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
800 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
801 * MUST_EXIST means throw exception if no record or multiple records found
af12ea93 802 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
66e75f8d 803 * @throws dml_exception if error
804 */
2bad34a3 805 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
806 $strictness = (int)$strictness;
807 if ($strictness == IGNORE_MULTIPLE) {
af12ea93 808 // do not limit here - ORA does not like that
809 if (!$rs = $this->get_recordset_sql($sql, $params)) {
810 return false;
811 }
812 foreach ($rs as $result) {
813 $rs->close();
814 return $result;
815 }
816 $rs->close();
66e75f8d 817 return false;
818 }
2bad34a3 819 return parent::get_record_sql($sql, $params, $strictness);
66e75f8d 820 }
821
822 /**
823 * Get a number of records as a moodle_recordset using a SQL statement.
824 *
825 * Since this method is a little less readable, use of it should be restricted to
826 * code where it's possible there might be large datasets being returned. For known
827 * small datasets use get_records_sql - it leads to simpler code.
828 *
829 * The return type is as for @see function get_recordset.
830 *
831 * @param string $sql the SQL select query to execute.
832 * @param array $params array of sql parameters
833 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
834 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
835 * @return mixed an moodle_recordset object
836 * @throws dml_exception if error
837 */
838 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
839 $limitfrom = (int)$limitfrom;
840 $limitnum = (int)$limitnum;
841 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
842 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
843
844 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
845
846 if ($limitfrom and $limitnum) {
8089f8f6 847 $rawsql = "SELECT oracle_o.*
848 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
849 FROM ($sql) oracle_i
850 ) oracle_o
851 WHERE rownum <= :oracle_max AND oracle_rownum > :oracle_min";
66e75f8d 852 $params['oracle_max'] = $limitfrom + $limitnum;
853 $params['oracle_min'] = $limitfrom;
854
855 } else if ($limitfrom and !$limitnum) {
8089f8f6 856 $rawsql = "SELECT oracle_o.*
857 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
858 FROM ($sql) oracle_i
859 ) oracle_o
860 WHERE oracle_rownum > :oracle_min";
66e75f8d 861 $params['oracle_min'] = $limitfrom;
862
863 } else if (!$limitfrom and $limitnum) {
8089f8f6 864 $rawsql = "SELECT *
865 FROM ($sql)
866 WHERE rownum <= :oracle_max";
66e75f8d 867 $params['oracle_max'] = $limitnum;
8089f8f6 868
869 } else { // No limitfrom nor limitnum
870 $rawsql = $sql;
66e75f8d 871 }
872
873 $this->query_start($sql, $params, SQL_QUERY_SELECT);
8089f8f6 874 $stmt = $this->parse_query($rawsql);
66e75f8d 875 $this->bind_params($stmt, $params);
8089f8f6 876 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 877 $this->query_end($result, $stmt);
878
879 return $this->create_recordset($stmt);
880 }
881
882 protected function create_recordset($stmt) {
883 return new oci_native_moodle_recordset($stmt);
884 }
885
886 /**
887 * Get a number of records as an array of objects using a SQL statement.
888 *
889 * Return value as for @see function get_records.
890 *
891 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
892 * must be a unique value (usually the 'id' field), as it will be used as the key of the
893 * returned array.
894 * @param array $params array of sql parameters
895 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
896 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
897 * @return mixed an array of objects, or empty array if no records were found
898 * @throws dml_exception if error
899 */
900 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
901 $limitfrom = (int)$limitfrom;
902 $limitnum = (int)$limitnum;
903 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
904 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
905
906 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
907
908 if ($limitfrom and $limitnum) {
909 $sql = "SELECT oracle_o.*
910 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
911 FROM ($sql) oracle_i
912 ) oracle_o
913 WHERE rownum <= :oracle_max AND oracle_rownum > :oracle_min";
914 $params['oracle_max'] = $limitfrom + $limitnum;
915 $params['oracle_min'] = $limitfrom;
916
917 } else if ($limitfrom and !$limitnum) {
918 $sql = "SELECT oracle_o.*
919 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
920 FROM ($sql) oracle_i
921 ) oracle_o
922 WHERE oracle_rownum > :oracle_min";
923 $params['oracle_min'] = $limitfrom;
924
925 } else if (!$limitfrom and $limitnum) {
926 $sql = "SELECT *
927 FROM ($sql)
928 WHERE rownum <= :oracle_max";
929 $params['oracle_max'] = $limitnum;
930 }
931
932 $this->query_start($sql, $params, SQL_QUERY_SELECT);
933 $stmt = $this->parse_query($sql);
934 $this->bind_params($stmt, $params);
8089f8f6 935 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 936 $this->query_end($result, $stmt);
937
938 $records = null;
939 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW);
940 oci_free_statement($stmt);
941
942 $return = array();
943
944 foreach ($records as $row) {
945 $row = array_change_key_case($row, CASE_LOWER);
946 unset($row['oracle_rownum']);
947 array_walk($row, array('oci_native_moodle_database', 'onespace2empty'));
948 $id = reset($row);
949 if (isset($return[$id])) {
950 $colname = key($row);
951 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);
952 }
953 $return[$id] = (object)$row;
954 }
955
956 return $return;
957 }
958
959 /**
960 * Selects records and return values (first field) as an array using a SQL statement.
961 *
962 * @param string $sql The SQL query
963 * @param array $params array of sql parameters
964 * @return array of values
965 * @throws dml_exception if error
966 */
967 public function get_fieldset_sql($sql, array $params=null) {
968 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
969
970 $this->query_start($sql, $params, SQL_QUERY_SELECT);
971 $stmt = $this->parse_query($sql);
972 $this->bind_params($stmt, $params);
8089f8f6 973 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 974 $this->query_end($result, $stmt);
975
976 $records = null;
977 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN);
978 oci_free_statement($stmt);
979
980 $return = reset($records);
981 array_walk($return, array('oci_native_moodle_database', 'onespace2empty'));
982
983 return $return;
984 }
985
986 /**
987 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
988 * @param string $table name
989 * @param mixed $params data record as object or array
990 * @param bool $returnit return it of inserted record
991 * @param bool $bulk true means repeated inserts expected
992 * @param bool $customsequence true if 'id' included in $params, disables $returnid
993 * @return true or new id
994 * @throws dml_exception if error
995 */
996 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
997 if (!is_array($params)) {
998 $params = (array)$params;
999 }
1000
1001 $returning = "";
1002
1003 if ($customsequence) {
1004 if (!isset($params['id'])) {
1005 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1006 }
1007 $returnid = false;
1008 } else {
52a01626 1009 unset($params['id']);
66e75f8d 1010 if ($returnid) {
52a01626 1011 $returning = "RETURNING id INTO :oracle_id"; // crazy name nobody is ever going to use or parameter ;-)
66e75f8d 1012 }
1013 }
1014
1015 if (empty($params)) {
1016 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1017 }
1018
1019 $fields = implode(',', array_keys($params));
1020 $values = array();
52a01626 1021 foreach ($params as $pname => $value) {
66e75f8d 1022 $values[] = ":$pname";
1023 }
1024 $values = implode(',', $values);
1025
52a01626 1026 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES ($values) $returning";
66e75f8d 1027 $id = null;
1028
1029 $this->query_start($sql, $params, SQL_QUERY_INSERT);
1030 $stmt = $this->parse_query($sql);
1031 $descriptors = $this->bind_params($stmt, $params, $table);
1032 if ($returning) {
1033 oci_bind_by_name($stmt, ":oracle_id", $id, -1, SQLT_LNG);
1034 }
8089f8f6 1035 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1036 $this->free_descriptors($descriptors);
1037 $this->query_end($result, $stmt);
1038 oci_free_statement($stmt);
1039
1040 if (!$returnid) {
1041 return true;
1042 }
1043
1044 if (!$returning) {
1045 die('TODO - implement oracle 9.2 insert support'); //TODO
1046 }
1047
1048 return (int)$id;
1049 }
1050
1051 /**
1052 * Insert a record into a table and return the "id" field if required.
1053 *
1054 * Some conversions and safety checks are carried out. Lobs are supported.
1055 * If the return ID isn't required, then this just reports success as true/false.
1056 * $data is an object containing needed data
1057 * @param string $table The database table to be inserted into
1058 * @param object $data A data object with values for one or more fields in the record
1059 * @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.
1060 * @return true or new id
1061 * @throws dml_exception if error
1062 */
1063 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
1064 if (!is_object($dataobject)) {
1065 $dataobject = (object)$dataobject;
1066 }
1067
66e75f8d 1068 unset($dataobject->id);
8089f8f6 1069
1070 $columns = $this->get_columns($table);
66e75f8d 1071 $cleaned = array();
1072
1073 foreach ($dataobject as $field=>$value) {
52a01626 1074 if (!isset($columns[$field])) { // Non-existing table field, skip it
66e75f8d 1075 continue;
1076 }
52a01626 1077 $column = $columns[$field];
1078 $cleaned[$field] = $this->normalise_value($column, $value);
66e75f8d 1079 }
1080
52a01626 1081 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
66e75f8d 1082 }
1083
1084 /**
1085 * Import a record into a table, id field is required.
1086 * Safety checks are NOT carried out. Lobs are supported.
1087 *
1088 * @param string $table name of database table to be inserted into
1089 * @param object $dataobject A data object with values for one or more fields in the record
1090 * @return bool true
1091 * @throws dml_exception if error
1092 */
1093 public function import_record($table, $dataobject) {
52a01626 1094 if (!is_object($dataobject)) {
1095 $dataobject = (object)$dataobject;
1096 }
66e75f8d 1097
1098 $columns = $this->get_columns($table);
1099 $cleaned = array();
1100
1101 foreach ($dataobject as $field=>$value) {
1102 if (!isset($columns[$field])) {
1103 continue;
1104 }
52a01626 1105 $column = $columns[$field];
1106 $cleaned[$field] = $this->normalise_value($column, $value);
66e75f8d 1107 }
1108
1109 return $this->insert_record_raw($table, $cleaned, false, true, true);
1110 }
1111
1112 /**
1113 * Update record in database, as fast as possible, no safety checks, lobs not supported.
1114 * @param string $table name
1115 * @param mixed $params data record as object or array
1116 * @param bool true means repeated updates expected
1117 * @return bool true
1118 * @throws dml_exception if error
1119 */
1120 public function update_record_raw($table, $params, $bulk=false) {
1121 if (!is_array($params)) {
1122 $params = (array)$params;
1123 }
1124 if (!isset($params['id'])) {
1125 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1126 }
1127
1128 if (empty($params)) {
1129 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1130 }
1131
1132 $sets = array();
1133 foreach ($params as $field=>$value) {
1134 if ($field == 'id') {
1135 continue;
1136 }
1137 $sets[] = "$field = :$field";
1138 }
1139
1140 $sets = implode(',', $sets);
1141 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=:id";
1142
1143 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1144 $stmt = $this->parse_query($sql);
1145 $descriptors = $this->bind_params($stmt, $params, $table);
8089f8f6 1146 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1147 $this->free_descriptors($descriptors);
52a01626 1148 $this->query_end($result, $stmt);
66e75f8d 1149 oci_free_statement($stmt);
1150
1151 return true;
1152 }
1153
1154 /**
1155 * Update a record in a table
1156 *
1157 * $dataobject is an object containing needed data
1158 * Relies on $dataobject having a variable "id" to
1159 * specify the record to update
1160 *
1161 * @param string $table The database table to be checked against.
1162 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1163 * @param bool true means repeated updates expected
1164 * @return bool true
1165 * @throws dml_exception if error
1166 */
1167 public function update_record($table, $dataobject, $bulk=false) {
1168 if (!is_object($dataobject)) {
1169 $dataobject = (object)$dataobject;
1170 }
1171
1172 $columns = $this->get_columns($table);
1173 $cleaned = array();
1174
1175 foreach ($dataobject as $field=>$value) {
1176 if (!isset($columns[$field])) {
1177 continue;
1178 }
52a01626 1179 $column = $columns[$field];
1180 $cleaned[$field] = $this->normalise_value($column, $value);
66e75f8d 1181 }
1182
1183 $this->update_record_raw($table, $cleaned, $bulk);
1184
1185 return true;
1186 }
1187
1188 /**
1189 * Set a single field in every table record which match a particular WHERE clause.
1190 *
1191 * @param string $table The database table to be checked against.
1192 * @param string $newfield the field to set.
1193 * @param string $newvalue the value to set the field to.
1194 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1195 * @param array $params array of sql parameters
1196 * @return bool true
1197 * @throws dml_exception if error
1198 */
1199 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
52a01626 1200
66e75f8d 1201 if ($select) {
1202 $select = "WHERE $select";
1203 }
52a01626 1204 if (is_null($params)) {
1205 $params = array();
1206 }
1207
1208 // Get column metadata
1209 $columns = $this->get_columns($table);
1210 $column = $columns[$newfield];
1211
1212 $newvalue = $this->normalise_value($column, $newvalue);
66e75f8d 1213
1214 list($select, $params, $type) = $this->fix_sql_params($select, $params);
1215
1216 if (is_bool($newvalue)) {
1217 $newvalue = (int)$newvalue; // prevent "false" problems
1218 }
1219 if (is_null($newvalue)) {
1220 $newsql = "$newfield = NULL";
1221 } else {
1222 $params[$newfield] = $newvalue;
1223 $newsql = "$newfield = :$newfield";
1224 }
1225 $sql = "UPDATE {$this->prefix}$table SET $newsql $select";
1226
1227 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1228 $stmt = $this->parse_query($sql);
1229 $descriptors = $this->bind_params($stmt, $params, $table);
8089f8f6 1230 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1231 $this->free_descriptors($descriptors);
52a01626 1232 $this->query_end($result, $stmt);
66e75f8d 1233 oci_free_statement($stmt);
1234
1235 return true;
1236 }
1237
1238 /**
1239 * Delete one or more records from a table which match a particular WHERE clause.
1240 *
1241 * @param string $table The database table to be checked against.
1242 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1243 * @param array $params array of sql parameters
1244 * @return bool true
1245 * @throws dml_exception if error
1246 */
1247 public function delete_records_select($table, $select, array $params=null) {
52a01626 1248
66e75f8d 1249 if ($select) {
1250 $select = "WHERE $select";
1251 }
52a01626 1252
1253 $sql = "DELETE FROM {" . $table . "} $select";
66e75f8d 1254
1255 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1256
1257 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1258 $stmt = $this->parse_query($sql);
1259 $this->bind_params($stmt, $params);
8089f8f6 1260 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1261 $this->query_end($result, $stmt);
1262 oci_free_statement($stmt);
1263
1264 return true;
1265 }
1266
1267 function sql_null_from_clause() {
1268 return ' FROM dual';
1269 }
1270
1271 public function sql_bitand($int1, $int2) {
1272 return 'bitand((' . $int1 . '), (' . $int2 . '))';
1273 }
1274
1275 public function sql_bitnot($int1) {
1276 return '((0 - (' . $int1 . ')) - 1)';
1277 }
1278
1279 public function sql_bitor($int1, $int2) {
1280 return '((' . $int1 . ') + (' . $int2 . ') - ' . $this->sql_bitand($int1, $int2) . ')';
1281 }
1282
1283 public function sql_bitxor($int1, $int2) {
2c4eb006 1284 return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')';
66e75f8d 1285 }
1286
1287 /**
1288 * Returns the SQL text to be used in order to perform module '%'
1289 * opration - remainder after division
1290 *
1291 * @param integer int1 first integer in the operation
1292 * @param integer int2 second integer in the operation
1293 * @return string the piece of SQL code to be used in your statement.
1294 */
1295 public function sql_modulo($int1, $int2) {
1296 return 'MOD(' . $int1 . ', ' . $int2 . ')';
1297 }
1298
1299 public function sql_cast_char2int($fieldname, $text=false) {
1300 if (!$text) {
1301 return ' CAST(' . $fieldname . ' AS INT) ';
1302 } else {
1303 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1304 }
1305 }
1306
52a01626 1307 // TODO: Change this function and uses to support 2 parameters: fieldname and value
1308 // that way we can use REGEXP_LIKE(x, y, 'i') to provide case-insensitive like searches
1309 // to lower() comparison or whatever
66e75f8d 1310 public function sql_ilike() {
1311 // TODO: add some ilike workaround
1312 return 'LIKE';
1313 }
1314
52a01626 1315 // NOTE: Oracle concat implementation isn't ANSI compliant when using NULLs (the result of
1316 // any concatenation with NULL must return NULL) because of his inability to diferentiate
1317 // NULLs and empty strings. So this function will cause some tests to fail. Hopefully
1318 // it's only a side case and it won't affect normal concatenation operations in Moodle.
66e75f8d 1319 public function sql_concat() {
1320 $arr = func_get_args();
1321 $s = implode(' || ', $arr);
1322 if ($s === '') {
1323 return " '' ";
1324 }
1325 return " $s ";
1326 }
1327
1328 public function sql_concat_join($separator="' '", $elements=array()) {
1329 for ($n=count($elements)-1; $n > 0 ; $n--) {
1330 array_splice($elements, $n, 0, $separator);
1331 }
1332 $s = implode(' || ', $elements);
1333 if ($s === '') {
1334 return " '' ";
1335 }
1336 return " $s ";
1337 }
1338
1339 /**
1340 * Returns the SQL for returning searching one string for the location of another.
1341 */
1342 public function sql_position($needle, $haystack) {
1343 return "INSTR(($haystack), ($needle))";
1344 }
1345
1346 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1347 if ($nullablefield) {
1348 return " $fieldname IS NULL "; /// empties in nullable fields are stored as
1349 } else { /// NULLs
1350 if ($textfield) {
1351 return " ".$this->sql_compare_text($fieldname)." = ' ' "; /// oracle_dirty_hack inserts 1-whitespace
1352 } else { /// in NOT NULL varchar and text columns so
1353 return " $fieldname = ' ' "; /// we need to look for that in any situation
1354 }
1355 }
1356 }
1357
8089f8f6 1358 public function sql_empty() {
66e75f8d 1359 return ' ';
1360 }
1361
d7d86462 1362/// session locking
fc12e1f7 1363 // http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2.htm#999576
1364
66e75f8d 1365/// transactions
1366 /**
1367 * on DBs that support it, switch to transaction mode and begin a transaction
1368 * you'll need to ensure you call commit_sql() or your changes *will* be lost.
1369 *
1370 * this is _very_ useful for massive updates
1371 */
1372 public function begin_sql() {
a1dda107 1373 if (!parent::begin_sql()) {
1374 return false;
1375 }
1376 return true;
66e75f8d 1377
1378 $sql = "BEGIN";
1379 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1380 $stmt = $this->parse_query($sql);
8089f8f6 1381 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1382 $this->query_end($result, $stmt);
1383 oci_free_statement($stmt);
1384 return true;
1385 }
1386
1387 /**
1388 * on DBs that support it, commit the transaction
1389 */
1390 public function commit_sql() {
a1dda107 1391 if (!parent::commit_sql()) {
1392 return false;
1393 }
1394 return true;
66e75f8d 1395
1396 $sql = "COMMIT";
1397 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1398 $stmt = $this->parse_query($sql);
8089f8f6 1399 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1400 $this->query_end($result, $stmt);
1401 oci_free_statement($stmt);
1402 return true;
1403 }
1404
1405 /**
1406 * on DBs that support it, rollback the transaction
1407 */
1408 public function rollback_sql() {
a1dda107 1409 if (!parent::rollback_sql()) {
1410 return false;
1411 }
1412 return true;
66e75f8d 1413
1414 $sql = "ROLLBACK";
1415 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1416 $stmt = $this->parse_query($sql);
8089f8f6 1417 $result = oci_execute($stmt, $this->commit_status);
66e75f8d 1418 $this->query_end($result, $stmt);
1419 oci_free_statement($stmt);
1420 return true;
1421 }
1422}