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