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