3 // This file is part of Moodle - http://moodle.org/
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.
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.
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/>.
20 * Native oci class representing moodle database interface.
24 * @copyright 2008 Petr Skoda (http://skodak.org)
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
28 defined('MOODLE_INTERNAL') ||
die();
30 require_once($CFG->libdir
.'/dml/moodle_database.php');
31 require_once($CFG->libdir
.'/dml/oci_native_moodle_recordset.php');
32 require_once($CFG->libdir
.'/dml/oci_native_moodle_temptables.php');
35 * Native oci class representing moodle database interface.
37 * One complete reference for PHP + OCI:
38 * http://www.oracle.com/technology/tech/php/underground-php-oracle-manual.html
40 class oci_native_moodle_database
extends moodle_database
{
42 protected $oci = null;
44 private $last_stmt_error = null; // To store stmt errors and enable get_last_error() to detect them
45 private $commit_status = null; // default value initialised in connect method, we need the driver to be present
47 private $last_error_reporting; // To handle oci driver default verbosity
48 private $unique_session_id; // To store unique_session_id. Needed for temp tables unique naming
50 private $dblocks_supported = null; // To cache locks support along the connection life
51 private $bitwise_supported = null; // To cache bitwise operations support along the connection life
54 * Detects if all needed PHP stuff installed.
55 * Note: can be used before connect()
56 * @return mixed true if ok, string if something
58 public function driver_installed() {
59 if (!extension_loaded('oci8')) {
60 return get_string('ociextensionisnotpresentinphp', 'install');
66 * Returns database family type - describes SQL dialect
67 * Note: can be used before connect()
68 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
70 public function get_dbfamily() {
75 * Returns more specific database driver type
76 * Note: can be used before connect()
77 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
79 protected function get_dbtype() {
84 * Returns general database library name
85 * Note: can be used before connect()
86 * @return string db type pdo, native
88 protected function get_dblibrary() {
93 * Returns localised database type name
94 * Note: can be used before connect()
97 public function get_name() {
98 return get_string('nativeoci', 'install');
102 * Returns localised database configuration help.
103 * Note: can be used before connect()
106 public function get_configuration_help() {
107 return get_string('nativeocihelp', 'install');
111 * Returns localised database description
112 * Note: can be used before connect()
115 public function get_configuration_hints() {
116 return get_string('databasesettingssub_oci', 'install');
120 * Diagnose database and tables, this function is used
121 * to verify database and driver settings, db engine types, etc.
123 * @return string null means everything ok, string means problem found.
125 public function diagnose() {
126 if (!$this->bitwise_supported() or !$this->session_lock_supported()) {
127 return 'Oracle PL/SQL Moodle support packages are not installed! Database administrator has to execute /lib/dml/oci_native_moodle_package.sql script.';
134 * Must be called before other methods.
135 * @param string $dbhost
136 * @param string $dbuser
137 * @param string $dbpass
138 * @param string $dbname
139 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
140 * @param array $dboptions driver specific options
142 * @throws dml_connection_exception if error
144 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
145 if ($prefix == '' and !$this->external
) {
146 //Enforce prefixes for everybody but mysql
147 throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
149 if (!$this->external
and strlen($prefix) > 2) {
150 //Max prefix length for Oracle is 2cc
151 $a = (object)array('dbfamily'=>'oracle', 'maxlength'=>2);
152 throw new dml_exception('prefixtoolong', $a);
155 $driverstatus = $this->driver_installed();
157 if ($driverstatus !== true) {
158 throw new dml_exception('dbdriverproblem', $driverstatus);
161 // Autocommit ON by default.
162 // Switching to OFF (OCI_DEFAULT), when playing with transactions
163 // please note this thing is not defined if oracle driver not present in PHP
164 // which means it can not be used as default value of object property!
165 $this->commit_status
= OCI_COMMIT_ON_SUCCESS
;
167 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
168 unset($this->dboptions
['dbsocket']);
170 // NOTE: use of ', ", / and \ is very problematic, even native oracle tools seem to have
171 // problems with these, so just forget them and do not report problems into tracker...
173 if (empty($this->dbhost
)) {
174 // old style full address (TNS)
175 $dbstring = $this->dbname
;
177 if (empty($this->dboptions
['dbport'])) {
178 $this->dboptions
['dbport'] = 1521;
180 $dbstring = '//'.$this->dbhost
.':'.$this->dboptions
['dbport'].'/'.$this->dbname
;
184 if (empty($this->dboptions
['dbpersist'])) {
185 $this->oci
= oci_new_connect($this->dbuser
, $this->dbpass
, $dbstring, 'AL32UTF8');
187 $this->oci
= oci_pconnect($this->dbuser
, $this->dbpass
, $dbstring, 'AL32UTF8');
189 $dberr = ob_get_contents();
193 if ($this->oci
=== false) {
196 if (isset($e['message'])) {
197 $dberr = $e['message'];
199 throw new dml_connection_exception($dberr);
202 // get unique session id, to be used later for temp tables stuff
203 $sql = 'SELECT DBMS_SESSION.UNIQUE_SESSION_ID() FROM DUAL';
204 $this->query_start($sql, null, SQL_QUERY_AUX
);
205 $stmt = $this->parse_query($sql);
206 $result = oci_execute($stmt, $this->commit_status
);
207 $this->query_end($result, $stmt);
209 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
210 oci_free_statement($stmt);
211 $this->unique_session_id
= reset($records[0]);
213 //note: do not send "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'" !
214 // instead fix our PHP code to convert "," to "." properly!
216 // Connection stabilised and configured, going to instantiate the temptables controller
217 $this->temptables
= new oci_native_moodle_temptables($this, $this->unique_session_id
);
223 * Close database connection and release all resources
224 * and memory (especially circular memory references).
225 * Do NOT use connect() again, create a new instance if needed.
227 public function dispose() {
228 parent
::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
230 oci_close($this->oci
);
237 * Called before each db query.
239 * @param array array of parameters
240 * @param int $type type of query
241 * @param mixed $extrainfo driver specific extra information
244 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
245 parent
::query_start($sql, $params, $type, $extrainfo);
246 // oci driver tents to send debug to output, we do not need that ;-)
247 $this->last_error_reporting
= error_reporting(0);
251 * Called immediately after each db query.
252 * @param mixed db specific result
255 protected function query_end($result, $stmt=null) {
256 // reset original debug level
257 error_reporting($this->last_error_reporting
);
258 if ($stmt and $result === false) {
259 // Look for stmt error and store it
260 if (is_resource($stmt)) {
261 $e = oci_error($stmt);
263 $this->last_stmt_error
= $e['message'];
266 oci_free_statement($stmt);
268 parent
::query_end($result);
272 * Returns database server info array
275 public function get_server_info() {
276 static $info = null; // TODO: move to real object property
278 if (is_null($info)) {
279 $this->query_start("--oci_server_version()", null, SQL_QUERY_AUX
);
280 $description = oci_server_version($this->oci
);
281 $this->query_end(true);
282 preg_match('/(\d+\.)+\d+/', $description, $matches);
283 $info = array('description'=>$description, 'version'=>$matches[0]);
289 protected function is_min_version($version) {
290 $server = $this->get_server_info();
291 $server = $server['version'];
292 return version_compare($server, $version, '>=');
296 * Converts short table name {tablename} to real table name
297 * supporting temp tables ($this->unique_session_id based) if detected
302 protected function fix_table_names($sql) {
303 if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/', $sql, $matches)) {
304 foreach($matches[0] as $key=>$match) {
305 $name = $matches[1][$key];
306 if ($this->temptables
->is_temptable($name)) {
307 $sql = str_replace($match, $this->temptables
->get_correct_name($name), $sql);
309 $sql = str_replace($match, $this->prefix
.$name, $sql);
317 * Returns supported query parameter types
318 * @return int bitmask
320 protected function allowed_param_types() {
321 return SQL_PARAMS_NAMED
;
325 * Returns last error reported by database engine.
326 * @return string error message
328 public function get_last_error() {
330 // First look for any previously saved stmt error
331 if (!empty($this->last_stmt_error
)) {
332 $error = $this->last_stmt_error
;
333 $this->last_stmt_error
= null;
334 } else { // Now try connection error
335 $e = oci_error($this->oci
);
337 $error = $e['message'];
344 * Prepare the statement for execution
345 * @throws dml_connection_exception
349 protected function parse_query($sql) {
350 $stmt = oci_parse($this->oci
, $sql);
351 if ($stmt == false) {
352 throw new dml_connection_exception('Can not parse sql query'); //TODO: maybe add better info
358 * Make sure there are no reserved words in param names...
360 * @param array $params
361 * @return array ($sql, $params) updated query and parameters
363 protected function tweak_param_names($sql, array $params) {
364 if (empty($params)) {
365 return array($sql, $params);
368 $newparams = array();
369 $searcharr = array(); // search => replace pairs
370 foreach ($params as $name => $value) {
371 // Keep the name within the 30 chars limit always (prefixing/replacing)
372 if (strlen($name) <= 28) {
373 $newname = 'o_' . $name;
375 $newname = 'o_' . substr($name, 2);
377 $newparams[$newname] = $value;
378 $searcharr[':' . $name] = ':' . $newname;
380 // sort by length desc to avoid potential str_replace() overlap
381 uksort($searcharr, array('oci_native_moodle_database', 'compare_by_length_desc'));
383 $sql = str_replace(array_keys($searcharr), $searcharr, $sql);
384 return array($sql, $newparams);
388 * Return tables in database WITHOUT current prefix
389 * @return array of table names in lowercase and without prefix
391 public function get_tables($usecache=true) {
392 if ($usecache and $this->tables
!== null) {
393 return $this->tables
;
395 $this->tables
= array();
396 $prefix = str_replace('_', "\\_", strtoupper($this->prefix
));
397 $sql = "SELECT TABLE_NAME
399 WHERE TABLE_TYPE='TABLE'
400 AND TABLE_NAME NOT LIKE 'BIN\$%'
401 AND TABLE_NAME LIKE '$prefix%' ESCAPE '\\'";
402 $this->query_start($sql, null, SQL_QUERY_AUX
);
403 $stmt = $this->parse_query($sql);
404 $result = oci_execute($stmt, $this->commit_status
);
405 $this->query_end($result, $stmt);
407 oci_fetch_all($stmt, $records, 0, -1, OCI_ASSOC
);
408 oci_free_statement($stmt);
409 $records = array_map('strtolower', $records['TABLE_NAME']);
410 foreach ($records as $tablename) {
411 if (strpos($tablename, $this->prefix
) !== 0) {
414 $tablename = substr($tablename, strlen($this->prefix
));
415 $this->tables
[$tablename] = $tablename;
418 // Add the currently available temptables
419 $this->tables
= array_merge($this->tables
, $this->temptables
->get_temptables());
421 return $this->tables
;
425 * Return table indexes - everything lowercased
426 * @return array of arrays
428 public function get_indexes($table) {
430 $tablename = strtoupper($this->prefix
.$table);
432 $sql = "SELECT i.INDEX_NAME, i.UNIQUENESS, c.COLUMN_POSITION, c.COLUMN_NAME, ac.CONSTRAINT_TYPE
434 JOIN ALL_IND_COLUMNS c ON c.INDEX_NAME=i.INDEX_NAME
435 LEFT JOIN ALL_CONSTRAINTS ac ON (ac.TABLE_NAME=i.TABLE_NAME AND ac.CONSTRAINT_NAME=i.INDEX_NAME AND ac.CONSTRAINT_TYPE='P')
436 WHERE i.TABLE_NAME = '$tablename'
437 ORDER BY i.INDEX_NAME, c.COLUMN_POSITION";
439 $stmt = $this->parse_query($sql);
440 $result = oci_execute($stmt, $this->commit_status
);
441 $this->query_end($result, $stmt);
443 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
444 oci_free_statement($stmt);
446 foreach ($records as $record) {
447 if ($record['CONSTRAINT_TYPE'] === 'P') {
451 $indexname = strtolower($record['INDEX_NAME']);
452 if (!isset($indexes[$indexname])) {
453 $indexes[$indexname] = array('primary' => ($record['CONSTRAINT_TYPE'] === 'P'),
454 'unique' => ($record['UNIQUENESS'] === 'UNIQUE'),
455 'columns' => array());
457 $indexes[$indexname]['columns'][] = strtolower($record['COLUMN_NAME']);
464 * Returns detailed information about columns in table. This information is cached internally.
465 * @param string $table name
466 * @param bool $usecache
467 * @return array array of database_column_info objects indexed with column names
469 public function get_columns($table, $usecache=true) {
470 if ($usecache and isset($this->columns
[$table])) {
471 return $this->columns
[$table];
474 if (!$table) { // table not specified, return empty array directly
478 $this->columns
[$table] = array();
480 $sql = "SELECT CNAME, COLTYPE, WIDTH, SCALE, PRECISION, NULLS, DEFAULTVAL
482 WHERE TNAME = UPPER('{" . $table . "}')
485 list($sql, $params, $type) = $this->fix_sql_params($sql, null);
487 $this->query_start($sql, null, SQL_QUERY_AUX
);
488 $stmt = $this->parse_query($sql);
489 $result = oci_execute($stmt, $this->commit_status
);
490 $this->query_end($result, $stmt);
492 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
493 oci_free_statement($stmt);
498 foreach ($records as $rawcolumn) {
499 $rawcolumn = (object)$rawcolumn;
501 $info = new stdClass();
502 $info->name
= strtolower($rawcolumn->CNAME
);
505 if ($rawcolumn->COLTYPE
=== 'VARCHAR2'
506 or $rawcolumn->COLTYPE
=== 'VARCHAR'
507 or $rawcolumn->COLTYPE
=== 'NVARCHAR2'
508 or $rawcolumn->COLTYPE
=== 'NVARCHAR'
509 or $rawcolumn->COLTYPE
=== 'CHAR'
510 or $rawcolumn->COLTYPE
=== 'NCHAR') {
511 //TODO add some basic enum support here
512 $info->type
= $rawcolumn->COLTYPE
;
513 $info->meta_type
= 'C';
514 $info->max_length
= $rawcolumn->WIDTH
;
516 $info->not_null
= ($rawcolumn->NULLS
=== 'NOT NULL');
517 $info->has_default
= !is_null($rawcolumn->DEFAULTVAL
);
518 if ($info->has_default
) {
521 if ($rawcolumn->DEFAULTVAL
=== 'NULL') {
522 $info->default_value
= null;
523 } else if ($rawcolumn->DEFAULTVAL
=== "' ' ") { // Sometimes it's stored with trailing space
524 $info->default_value
= "";
525 } else if ($rawcolumn->DEFAULTVAL
=== "' '") { // Sometimes it's stored without trailing space
526 $info->default_value
= "";
528 $info->default_value
= trim($rawcolumn->DEFAULTVAL
); // remove trailing space
529 $info->default_value
= substr($info->default_value
, 1, strlen($info->default_value
)-2); //trim ''
532 $info->default_value
= null;
534 $info->primary_key
= false;
535 $info->binary
= false;
536 $info->unsigned
= null;
537 $info->auto_increment
= false;
538 $info->unique
= null;
540 } else if ($rawcolumn->COLTYPE
=== 'NUMBER') {
541 $info->type
= $rawcolumn->COLTYPE
;
542 $info->max_length
= $rawcolumn->PRECISION
;
543 $info->binary
= false;
544 if (!is_null($rawcolumn->SCALE
) && $rawcolumn->SCALE
== 0) { // null in oracle scale allows decimals => not integer
546 if ($info->name
=== 'id') {
547 $info->primary_key
= true;
548 $info->meta_type
= 'R';
549 $info->unique
= true;
550 $info->auto_increment
= true;
551 $info->has_default
= false;
553 $info->primary_key
= false;
554 $info->meta_type
= 'I';
555 $info->unique
= null;
556 $info->auto_increment
= false;
562 $info->meta_type
= 'N';
563 $info->primary_key
= false;
564 $info->unsigned
= null;
565 $info->auto_increment
= false;
566 $info->unique
= null;
567 $info->scale
= $rawcolumn->SCALE
;
569 $info->not_null
= ($rawcolumn->NULLS
=== 'NOT NULL');
570 $info->has_default
= !is_null($rawcolumn->DEFAULTVAL
);
571 if ($info->has_default
) {
572 $info->default_value
= trim($rawcolumn->DEFAULTVAL
); // remove trailing space
574 $info->default_value
= null;
577 } else if ($rawcolumn->COLTYPE
=== 'FLOAT') {
578 $info->type
= $rawcolumn->COLTYPE
;
579 $info->max_length
= (int)($rawcolumn->PRECISION
* 3.32193);
580 $info->primary_key
= false;
581 $info->meta_type
= 'N';
582 $info->unique
= null;
583 $info->auto_increment
= false;
584 $info->not_null
= ($rawcolumn->NULLS
=== 'NOT NULL');
585 $info->has_default
= !is_null($rawcolumn->DEFAULTVAL
);
586 if ($info->has_default
) {
587 $info->default_value
= trim($rawcolumn->DEFAULTVAL
); // remove trailing space
589 $info->default_value
= null;
592 } else if ($rawcolumn->COLTYPE
=== 'CLOB'
593 or $rawcolumn->COLTYPE
=== 'NCLOB') {
594 $info->type
= $rawcolumn->COLTYPE
;
595 $info->meta_type
= 'X';
596 $info->max_length
= -1;
599 $info->not_null
= ($rawcolumn->NULLS
=== 'NOT NULL');
600 $info->has_default
= !is_null($rawcolumn->DEFAULTVAL
);
601 if ($info->has_default
) {
603 if ($rawcolumn->DEFAULTVAL
=== 'NULL') {
604 $info->default_value
= null;
605 } else if ($rawcolumn->DEFAULTVAL
=== "' ' ") { // Sometimes it's stored with trailing space
606 $info->default_value
= "";
607 } else if ($rawcolumn->DEFAULTVAL
=== "' '") { // Other times it's stored without trailing space
608 $info->default_value
= "";
610 $info->default_value
= trim($rawcolumn->DEFAULTVAL
); // remove trailing space
611 $info->default_value
= substr($info->default_value
, 1, strlen($info->default_value
)-2); //trim ''
614 $info->default_value
= null;
616 $info->primary_key
= false;
617 $info->binary
= false;
618 $info->unsigned
= null;
619 $info->auto_increment
= false;
620 $info->unique
= null;
622 } else if ($rawcolumn->COLTYPE
=== 'BLOB') {
623 $info->type
= $rawcolumn->COLTYPE
;
624 $info->meta_type
= 'B';
625 $info->max_length
= -1;
628 $info->not_null
= ($rawcolumn->NULLS
=== 'NOT NULL');
629 $info->has_default
= !is_null($rawcolumn->DEFAULTVAL
);
630 if ($info->has_default
) {
632 if ($rawcolumn->DEFAULTVAL
=== 'NULL') {
633 $info->default_value
= null;
634 } else if ($rawcolumn->DEFAULTVAL
=== "' ' ") { // Sometimes it's stored with trailing space
635 $info->default_value
= "";
636 } else if ($rawcolumn->DEFAULTVAL
=== "' '") { // Sometimes it's stored without trailing space
637 $info->default_value
= "";
639 $info->default_value
= trim($rawcolumn->DEFAULTVAL
); // remove trailing space
640 $info->default_value
= substr($info->default_value
, 1, strlen($info->default_value
)-2); //trim ''
643 $info->default_value
= null;
645 $info->primary_key
= false;
646 $info->binary
= true;
647 $info->unsigned
= null;
648 $info->auto_increment
= false;
649 $info->unique
= null;
652 // unknown type - sorry
653 $info->type
= $rawcolumn->COLTYPE
;
654 $info->meta_type
= '?';
657 $this->columns
[$table][$info->name
] = new database_column_info($info);
660 return $this->columns
[$table];
664 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
666 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
667 * @param mixed $value value we are going to normalise
668 * @return mixed the normalised value
670 protected function normalise_value($column, $value) {
671 if (is_bool($value)) { // Always, convert boolean to int
672 $value = (int)$value;
674 } else if ($column->meta_type
== 'B') { // CLOB detected, we return 'blob' array instead of raw value to allow
675 if (!is_null($value)) { // binding/executing code later to know about its nature
676 $value = array('blob' => $value);
679 } else if ($column->meta_type
== 'X' && strlen($value) > 4000) { // CLOB detected (>4000 optimisation), we return 'clob'
680 if (!is_null($value)) { // array instead of raw value to allow binding/
681 $value = array('clob' => (string)$value); // executing code later to know about its nature
684 } else if ($value === '') {
685 if ($column->meta_type
== 'I' or $column->meta_type
== 'F' or $column->meta_type
== 'N') {
686 $value = 0; // prevent '' problems in numeric fields
693 * Transforms the sql and params in order to emulate the LIMIT clause available in other DBs
695 * @param string $sql the SQL select query to execute.
696 * @param array $params array of sql parameters
697 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
698 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
699 * @return array with the transformed sql and params updated
701 private function get_limit_sql($sql, array $params = null, $limitfrom=0, $limitnum=0) {
703 $limitfrom = (int)$limitfrom;
704 $limitnum = (int)$limitnum;
705 $limitfrom = ($limitfrom < 0) ?
0 : $limitfrom;
706 $limitnum = ($limitnum < 0) ?
0 : $limitnum;
708 // TODO: Add the /*+ FIRST_ROWS */ hint if there isn't another hint
710 if ($limitfrom and $limitnum) {
711 $sql = "SELECT oracle_o.*
712 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
714 WHERE rownum <= :oracle_num_rows
716 WHERE oracle_rownum > :oracle_skip_rows";
717 $params['oracle_num_rows'] = $limitfrom +
$limitnum;
718 $params['oracle_skip_rows'] = $limitfrom;
720 } else if ($limitfrom and !$limitnum) {
721 $sql = "SELECT oracle_o.*
722 FROM (SELECT oracle_i.*, rownum AS oracle_rownum
725 WHERE oracle_rownum > :oracle_skip_rows";
726 $params['oracle_skip_rows'] = $limitfrom;
728 } else if (!$limitfrom and $limitnum) {
731 WHERE rownum <= :oracle_num_rows";
732 $params['oracle_num_rows'] = $limitnum;
735 return array($sql, $params);
739 * This function will handle all the column values before being inserted/updated to DB for Oracle
740 * installations. This is because the "special feature" of Oracle where the empty string is
741 * equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
742 * (and with empties handling in general)
744 * Note that this function is 100% private and should be used, exclusively by DML functions
745 * in this file. Also, this is considered a DIRTY HACK to be removed when possible.
747 * This function is private and must not be used outside this driver at all
749 * @param $table string the table where the record is going to be inserted/updated (without prefix)
750 * @param $field string the field where the record is going to be inserted/updated
751 * @param $value mixed the value to be inserted/updated
753 private function oracle_dirty_hack ($table, $field, $value) {
756 $columns = $this->get_columns($table);
757 if (!isset($columns[$field])) {
760 $column = $columns[$field];
762 // !! This paragraph explains behaviour before Moodle 2.0:
764 // For Oracle DB, empty strings are converted to NULLs in DB
765 // and this breaks a lot of NOT NULL columns currently Moodle. In the future it's
766 // planned to move some of them to NULL, if they must accept empty values and this
767 // piece of code will become less and less used. But, for now, we need it.
768 // What we are going to do is to examine all the data being inserted and if it's
769 // an empty string (NULL for Oracle) and the field is defined as NOT NULL, we'll modify
770 // such data in the best form possible ("0" for booleans and numbers and " " for the
771 // rest of strings. It isn't optimal, but the only way to do so.
772 // In the opposite, when retrieving records from Oracle, we'll decode " " back to
773 // empty strings to allow everything to work properly. DIRTY HACK.
775 // !! These paragraphs explain the rationale about the change for Moodle 2.0:
777 // Before Moodle 2.0, we only used to apply this DIRTY HACK to NOT NULL columns, as
778 // stated above, but it causes one problem in NULL columns where both empty strings
779 // and real NULLs are stored as NULLs, being impossible to differentiate them when
780 // being retrieved from DB.
782 // So, starting with Moodle 2.0, we are going to apply the DIRTY HACK to all the
783 // CHAR/CLOB columns no matter of their nullability. That way, when retrieving
784 // NULLABLE fields we'll get proper empties and NULLs differentiated, so we'll be able
785 // to rely in NULL/empty/content contents without problems, until now that wasn't
788 // No breakage with old data is expected as long as at the time of writing this
789 // (20090922) all the current uses of both sql_empty() and sql_isempty() has been
790 // revised in 2.0 and all them were being performed against NOT NULL columns,
791 // where nothing has changed (the DIRTY HACK was already being applied).
795 // From Moodle 2.0 onwards, ALL empty strings in Oracle DBs will be stored as
796 // 1-whitespace char, ALL NULLs as NULLs and, obviously, content as content. And
797 // those 1-whitespace chars will be converted back to empty strings by all the
798 // get_field/record/set() functions transparently and any SQL needing direct handling
799 // of empties will need to use the sql_empty() and sql_isempty() helper functions.
802 // If the field ins't VARCHAR or CLOB, skip
803 if ($column->meta_type
!= 'C' and $column->meta_type
!= 'X') {
807 // If the value isn't empty, skip
808 if (!empty($value)) {
812 // Now, we have one empty value, going to be inserted to one VARCHAR2 or CLOB field
813 // Try to get the best value to be inserted
815 // The '0' string doesn't need any transformation, skip
816 if ($value === '0') {
820 // Transformations start
821 if (gettype($value) == 'boolean') {
822 return '0'; // Transform false to '0' that evaluates the same for PHP
824 } else if (gettype($value) == 'integer') {
825 return '0'; // Transform 0 to '0' that evaluates the same for PHP
827 } else if ($value === '') {
828 return ' '; // Transform '' to ' ' that DONT'T EVALUATE THE SAME
829 // (we'll transform back again on get_records_XXX functions and others)!!
832 // Fail safe to original value
837 * Helper function to order by string length desc
839 * @param $a string first element to compare
840 * @param $b string second element to compare
841 * @return int < 0 $a goes first (is less), 0 $b goes first, 0 doesn't matter
843 private function compare_by_length_desc($a, $b) {
844 return strlen($b) - strlen($a);
848 * Is db in unicode mode?
851 public function setup_is_unicodedb() {
853 FROM NLS_DATABASE_PARAMETERS
854 WHERE PARAMETER = 'NLS_CHARACTERSET'";
855 $this->query_start($sql, null, SQL_QUERY_AUX
);
856 $stmt = $this->parse_query($sql);
857 $result = oci_execute($stmt, $this->commit_status
);
858 $this->query_end($result, $stmt);
860 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN
);
861 oci_free_statement($stmt);
863 return (isset($records['VALUE'][0]) and $records['VALUE'][0] === 'AL32UTF8');
867 * Do NOT use in code, to be used by database_manager only!
868 * @param string $sql query
870 * @throws dml_exception if error
872 public function change_database_structure($sql) {
873 $this->reset_caches();
875 $this->query_start($sql, null, SQL_QUERY_STRUCTURE
);
876 $stmt = $this->parse_query($sql);
877 $result = oci_execute($stmt, $this->commit_status
);
878 $this->query_end($result, $stmt);
879 oci_free_statement($stmt);
884 protected function bind_params($stmt, array $params=null, $tablename=null) {
885 $descriptors = array();
889 $columns = $this->get_columns($tablename);
891 foreach($params as $key => $value) {
892 // Decouple column name and param name as far as sometimes they aren't the same
893 if ($key == 'o_newfieldtoset') { // found case where column and key diverge, handle that
894 $columnname = key($value); // columnname is the key of the array
895 $params[$key] = $value[$columnname]; // set the proper value in the $params array and
896 $value = $value[$columnname]; // set the proper value in the $value variable
898 $columnname = preg_replace('/^o_/', '', $key); // Default columnname (for DB introspecting is key), but...
900 // Continue processing
901 // Now, handle already detected LOBs
902 if (is_array($value)) { // Let's go to bind special cases (lob descriptors)
903 if (isset($value['clob'])) {
904 $lob = oci_new_descriptor($this->oci
, OCI_DTYPE_LOB
);
905 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_CLOB
);
906 $lob->writeTemporary($this->oracle_dirty_hack($tablename, $columnname, $params[$key]['clob']), OCI_TEMP_CLOB
);
907 $descriptors[] = $lob;
908 continue; // Column binding finished, go to next one
909 } else if (isset($value['blob'])) {
910 $lob = oci_new_descriptor($this->oci
, OCI_DTYPE_LOB
);
911 oci_bind_by_name($stmt, $key, $lob, -1, SQLT_BLOB
);
912 $lob->writeTemporary($params[$key]['blob'], OCI_TEMP_BLOB
);
913 $descriptors[] = $lob;
914 continue; // Column binding finished, go to next one
917 // TODO: Put proper types and length is possible (enormous speedup)
918 // Arrived here, continue with standard processing, using metadata if possible
919 if (isset($columns[$columnname])) {
920 $type = $columns[$columnname]->meta_type
;
921 $maxlength = $columns[$columnname]->max_length
;
930 oci_bind_by_name($stmt, $key, $params[$key]);
936 oci_bind_by_name($stmt, $key, $params[$key]);
940 // TODO: Only arrive here if BLOB is null: Bind if so, else exception!
944 // TODO: Only arrive here if CLOB is null or <= 4000 cc, else exception
947 default: // Bind as CHAR (applying dirty hack)
949 oci_bind_by_name($stmt, $key, $this->oracle_dirty_hack($tablename, $columnname, $params[$key]));
956 protected function free_descriptors($descriptors) {
957 foreach ($descriptors as $descriptor) {
958 oci_free_descriptor($descriptor);
963 * This function is used to convert all the Oracle 1-space defaults to the empty string
964 * like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
965 * fields will be out from Moodle.
966 * @param string the string to be converted to '' (empty string) if it's ' ' (one space)
967 * @param mixed the key of the array in case we are using this function from array_walk,
968 * defaults to null for other (direct) uses
969 * @return boolean always true (the converted variable is returned by reference)
971 public static function onespace2empty(&$item, $key=null) {
972 $item = ($item === ' ') ?
'' : $item;
977 * Execute general sql query. Should be used only when no other method suitable.
978 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
979 * @param string $sql query
980 * @param array $params query parameters
982 * @throws dml_exception if error
984 public function execute($sql, array $params=null) {
985 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
987 if (strpos($sql, ';') !== false) {
988 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
991 list($sql, $params) = $this->tweak_param_names($sql, $params);
992 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
993 $stmt = $this->parse_query($sql);
994 $this->bind_params($stmt, $params);
995 $result = oci_execute($stmt, $this->commit_status
);
996 $this->query_end($result, $stmt);
997 oci_free_statement($stmt);
1003 * Get a single database record as an object using a SQL statement.
1005 * The SQL statement should normally only return one record.
1006 * It is recommended to use get_records_sql() if more matches possible!
1008 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
1009 * @param array $params array of sql parameters
1010 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1011 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1012 * MUST_EXIST means throw exception if no record or multiple records found
1013 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
1014 * @throws dml_exception if error
1016 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING
) {
1017 $strictness = (int)$strictness;
1018 if ($strictness == IGNORE_MULTIPLE
) {
1019 // do not limit here - ORA does not like that
1020 $rs = $this->get_recordset_sql($sql, $params);
1022 foreach ($rs as $rec) {
1029 return parent
::get_record_sql($sql, $params, $strictness);
1033 * Get a number of records as a moodle_recordset using a SQL statement.
1035 * Since this method is a little less readable, use of it should be restricted to
1036 * code where it's possible there might be large datasets being returned. For known
1037 * small datasets use get_records_sql - it leads to simpler code.
1039 * The return type is as for @see function get_recordset.
1041 * @param string $sql the SQL select query to execute.
1042 * @param array $params array of sql parameters
1043 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1044 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1045 * @return moodle_recordset instance
1046 * @throws dml_exception if error
1048 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1050 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1052 list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum);
1054 list($rawsql, $params) = $this->tweak_param_names($rawsql, $params);
1055 $this->query_start($rawsql, $params, SQL_QUERY_SELECT
);
1056 $stmt = $this->parse_query($rawsql);
1057 $this->bind_params($stmt, $params);
1058 $result = oci_execute($stmt, $this->commit_status
);
1059 $this->query_end($result, $stmt);
1061 return $this->create_recordset($stmt);
1064 protected function create_recordset($stmt) {
1065 return new oci_native_moodle_recordset($stmt);
1069 * Get a number of records as an array of objects using a SQL statement.
1071 * Return value as for @see function get_records.
1073 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1074 * must be a unique value (usually the 'id' field), as it will be used as the key of the
1076 * @param array $params array of sql parameters
1077 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1078 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1079 * @return array of objects, or empty array if no records were found
1080 * @throws dml_exception if error
1082 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1084 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1086 list($rawsql, $params) = $this->get_limit_sql($sql, $params, $limitfrom, $limitnum);
1088 list($rawsql, $params) = $this->tweak_param_names($rawsql, $params);
1089 $this->query_start($rawsql, $params, SQL_QUERY_SELECT
);
1090 $stmt = $this->parse_query($rawsql);
1091 $this->bind_params($stmt, $params);
1092 $result = oci_execute($stmt, $this->commit_status
);
1093 $this->query_end($result, $stmt);
1096 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
1097 oci_free_statement($stmt);
1101 foreach ($records as $row) {
1102 $row = array_change_key_case($row, CASE_LOWER
);
1103 unset($row['oracle_rownum']);
1104 array_walk($row, array('oci_native_moodle_database', 'onespace2empty'));
1106 if (isset($return[$id])) {
1107 $colname = key($row);
1108 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
);
1110 $return[$id] = (object)$row;
1117 * Selects records and return values (first field) as an array using a SQL statement.
1119 * @param string $sql The SQL query
1120 * @param array $params array of sql parameters
1121 * @return array of values
1122 * @throws dml_exception if error
1124 public function get_fieldset_sql($sql, array $params=null) {
1125 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1127 list($sql, $params) = $this->tweak_param_names($sql, $params);
1128 $this->query_start($sql, $params, SQL_QUERY_SELECT
);
1129 $stmt = $this->parse_query($sql);
1130 $this->bind_params($stmt, $params);
1131 $result = oci_execute($stmt, $this->commit_status
);
1132 $this->query_end($result, $stmt);
1135 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_COLUMN
);
1136 oci_free_statement($stmt);
1138 $return = reset($records);
1139 array_walk($return, array('oci_native_moodle_database', 'onespace2empty'));
1145 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1146 * @param string $table name
1147 * @param mixed $params data record as object or array
1148 * @param bool $returnit return it of inserted record
1149 * @param bool $bulk true means repeated inserts expected
1150 * @param bool $customsequence true if 'id' included in $params, disables $returnid
1151 * @return bool|int true or new id
1152 * @throws dml_exception if error
1154 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1155 if (!is_array($params)) {
1156 $params = (array)$params;
1161 if ($customsequence) {
1162 if (!isset($params['id'])) {
1163 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1167 unset($params['id']);
1169 $returning = " RETURNING id INTO :oracle_id"; // crazy name nobody is ever going to use or parameter ;-)
1173 if (empty($params)) {
1174 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1177 $fields = implode(',', array_keys($params));
1179 foreach ($params as $pname => $value) {
1180 $values[] = ":$pname";
1182 $values = implode(',', $values);
1184 $sql = "INSERT INTO {" . $table . "} ($fields) VALUES ($values)";
1185 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1190 // note we don't need tweak_param_names() here. Placeholders are safe column names. MDL-28080
1191 // list($sql, $params) = $this->tweak_param_names($sql, $params);
1192 $this->query_start($sql, $params, SQL_QUERY_INSERT
);
1193 $stmt = $this->parse_query($sql);
1194 $descriptors = $this->bind_params($stmt, $params, $table);
1196 oci_bind_by_name($stmt, ":oracle_id", $id, 10, SQLT_INT
);
1198 $result = oci_execute($stmt, $this->commit_status
);
1199 $this->free_descriptors($descriptors);
1200 $this->query_end($result, $stmt);
1201 oci_free_statement($stmt);
1208 die('TODO - implement oracle 9.2 insert support'); //TODO
1215 * Insert a record into a table and return the "id" field if required.
1217 * Some conversions and safety checks are carried out. Lobs are supported.
1218 * If the return ID isn't required, then this just reports success as true/false.
1219 * $data is an object containing needed data
1220 * @param string $table The database table to be inserted into
1221 * @param object $data A data object with values for one or more fields in the record
1222 * @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.
1223 * @return bool|int true or new id
1224 * @throws dml_exception if error
1226 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
1227 $dataobject = (array)$dataobject;
1229 $columns = $this->get_columns($table);
1232 foreach ($dataobject as $field=>$value) {
1233 if ($field === 'id') {
1236 if (!isset($columns[$field])) { // Non-existing table field, skip it
1239 $column = $columns[$field];
1240 $cleaned[$field] = $this->normalise_value($column, $value);
1243 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1247 * Import a record into a table, id field is required.
1248 * Safety checks are NOT carried out. Lobs are supported.
1250 * @param string $table name of database table to be inserted into
1251 * @param object $dataobject A data object with values for one or more fields in the record
1253 * @throws dml_exception if error
1255 public function import_record($table, $dataobject) {
1256 $dataobject = (array)$dataobject;
1258 $columns = $this->get_columns($table);
1261 foreach ($dataobject as $field=>$value) {
1262 if (!isset($columns[$field])) {
1265 $column = $columns[$field];
1266 $cleaned[$field] = $this->normalise_value($column, $value);
1269 return $this->insert_record_raw($table, $cleaned, false, true, true);
1273 * Update record in database, as fast as possible, no safety checks, lobs not supported.
1274 * @param string $table name
1275 * @param mixed $params data record as object or array
1276 * @param bool true means repeated updates expected
1278 * @throws dml_exception if error
1280 public function update_record_raw($table, $params, $bulk=false) {
1281 $params = (array)$params;
1283 if (!isset($params['id'])) {
1284 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1287 if (empty($params)) {
1288 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1292 foreach ($params as $field=>$value) {
1293 if ($field == 'id') {
1296 $sets[] = "$field = :$field";
1299 $sets = implode(',', $sets);
1300 $sql = "UPDATE {" . $table . "} SET $sets WHERE id=:id";
1301 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1303 // note we don't need tweak_param_names() here. Placeholders are safe column names. MDL-28080
1304 // list($sql, $params) = $this->tweak_param_names($sql, $params);
1305 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1306 $stmt = $this->parse_query($sql);
1307 $descriptors = $this->bind_params($stmt, $params, $table);
1308 $result = oci_execute($stmt, $this->commit_status
);
1309 $this->free_descriptors($descriptors);
1310 $this->query_end($result, $stmt);
1311 oci_free_statement($stmt);
1317 * Update a record in a table
1319 * $dataobject is an object containing needed data
1320 * Relies on $dataobject having a variable "id" to
1321 * specify the record to update
1323 * @param string $table The database table to be checked against.
1324 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1325 * @param bool true means repeated updates expected
1327 * @throws dml_exception if error
1329 public function update_record($table, $dataobject, $bulk=false) {
1330 $dataobject = (array)$dataobject;
1332 $columns = $this->get_columns($table);
1335 foreach ($dataobject as $field=>$value) {
1336 if (!isset($columns[$field])) {
1339 $column = $columns[$field];
1340 $cleaned[$field] = $this->normalise_value($column, $value);
1343 $this->update_record_raw($table, $cleaned, $bulk);
1349 * Set a single field in every table record which match a particular WHERE clause.
1351 * @param string $table The database table to be checked against.
1352 * @param string $newfield the field to set.
1353 * @param string $newvalue the value to set the field to.
1354 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1355 * @param array $params array of sql parameters
1357 * @throws dml_exception if error
1359 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
1362 $select = "WHERE $select";
1364 if (is_null($params)) {
1368 // Get column metadata
1369 $columns = $this->get_columns($table);
1370 $column = $columns[$newfield];
1372 $newvalue = $this->normalise_value($column, $newvalue);
1374 list($select, $params, $type) = $this->fix_sql_params($select, $params);
1376 if (is_bool($newvalue)) {
1377 $newvalue = (int)$newvalue; // prevent "false" problems
1379 if (is_null($newvalue)) {
1380 $newsql = "$newfield = NULL";
1382 // Set the param to array ($newfield => $newvalue) and key to 'newfieldtoset'
1383 // name in the build sql. Later, bind_params() will detect the value array and
1384 // perform the needed modifications to allow the query to work. Note that
1385 // 'newfieldtoset' is one arbitrary name that hopefully won't be used ever
1386 // in order to avoid problems where the same field is used both in the set clause and in
1387 // the conditions. This was breaking badly in drivers using NAMED params like oci.
1388 $params['newfieldtoset'] = array($newfield => $newvalue);
1389 $newsql = "$newfield = :newfieldtoset";
1391 $sql = "UPDATE {" . $table . "} SET $newsql $select";
1392 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1394 list($sql, $params) = $this->tweak_param_names($sql, $params);
1395 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1396 $stmt = $this->parse_query($sql);
1397 $descriptors = $this->bind_params($stmt, $params, $table);
1398 $result = oci_execute($stmt, $this->commit_status
);
1399 $this->free_descriptors($descriptors);
1400 $this->query_end($result, $stmt);
1401 oci_free_statement($stmt);
1407 * Delete one or more records from a table which match a particular WHERE clause.
1409 * @param string $table The database table to be checked against.
1410 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1411 * @param array $params array of sql parameters
1413 * @throws dml_exception if error
1415 public function delete_records_select($table, $select, array $params=null) {
1418 $select = "WHERE $select";
1421 $sql = "DELETE FROM {" . $table . "} $select";
1423 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1425 list($sql, $params) = $this->tweak_param_names($sql, $params);
1426 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1427 $stmt = $this->parse_query($sql);
1428 $this->bind_params($stmt, $params);
1429 $result = oci_execute($stmt, $this->commit_status
);
1430 $this->query_end($result, $stmt);
1431 oci_free_statement($stmt);
1436 function sql_null_from_clause() {
1437 return ' FROM dual';
1440 // Bitwise operations
1441 protected function bitwise_supported() {
1442 if (isset($this->bitwise_supported
)) { // Use cached value if available
1443 return $this->bitwise_supported
;
1447 WHERE object_type = 'PACKAGE BODY'
1448 AND object_name = 'MOODLE_BITS'
1449 AND status = 'VALID'";
1450 $this->query_start($sql, null, SQL_QUERY_AUX
);
1451 $stmt = $this->parse_query($sql);
1452 $result = oci_execute($stmt, $this->commit_status
);
1453 $this->query_end($result, $stmt);
1455 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
1456 oci_free_statement($stmt);
1457 $this->bitwise_supported
= isset($records[0]) && reset($records[0]) ?
true : false;
1458 return $this->bitwise_supported
;
1461 public function sql_bitand($int1, $int2) {
1462 return 'bitand((' . $int1 . '), (' . $int2 . '))';
1465 public function sql_bitnot($int1) {
1466 return '((0 - (' . $int1 . ')) - 1)';
1469 public function sql_bitor($int1, $int2) {
1470 // Use the MOODLE_BITS package if available
1471 if ($this->bitwise_supported()) {
1472 return 'MOODLE_BITS.BITOR(' . $int1 . ', ' . $int2 . ')';
1474 // fallback to PHP bool operations, can break if using placeholders
1475 return '((' . $int1 . ') + (' . $int2 . ') - ' . $this->sql_bitand($int1, $int2) . ')';
1478 public function sql_bitxor($int1, $int2) {
1479 // Use the MOODLE_BITS package if available
1480 if ($this->bitwise_supported()) {
1481 return 'MOODLE_BITS.BITXOR(' . $int1 . ', ' . $int2 . ')';
1483 // fallback to PHP bool operations, can break if using placeholders
1484 return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')';
1488 * Returns the SQL text to be used in order to perform module '%'
1489 * operation - remainder after division
1491 * @param integer int1 first integer in the operation
1492 * @param integer int2 second integer in the operation
1493 * @return string the piece of SQL code to be used in your statement.
1495 public function sql_modulo($int1, $int2) {
1496 return 'MOD(' . $int1 . ', ' . $int2 . ')';
1499 public function sql_cast_char2int($fieldname, $text=false) {
1501 return ' CAST(' . $fieldname . ' AS INT) ';
1503 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1507 public function sql_cast_char2real($fieldname, $text=false) {
1509 return ' CAST(' . $fieldname . ' AS FLOAT) ';
1511 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS FLOAT) ';
1516 * Returns 'LIKE' part of a query.
1518 * @param string $fieldname usually name of the table column
1519 * @param string $param usually bound query parameter (?, :named)
1520 * @param bool $casesensitive use case sensitive search
1521 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1522 * @param bool $notlike true means "NOT LIKE"
1523 * @param string $escapechar escape char for '%' and '_'
1524 * @return string SQL code fragment
1526 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1527 if (strpos($param, '%') !== false) {
1528 debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
1531 $LIKE = $notlike ?
'NOT LIKE' : 'LIKE';
1533 // no accent sensitiveness here for now, sorry
1535 if ($casesensitive) {
1536 return "$fieldname $LIKE $param ESCAPE '$escapechar'";
1538 return "LOWER($fieldname) $LIKE LOWER($param) ESCAPE '$escapechar'";
1542 // NOTE: Oracle concat implementation isn't ANSI compliant when using NULLs (the result of
1543 // any concatenation with NULL must return NULL) because of his inability to differentiate
1544 // NULLs and empty strings. So this function will cause some tests to fail. Hopefully
1545 // it's only a side case and it won't affect normal concatenation operations in Moodle.
1546 public function sql_concat() {
1547 $arr = func_get_args();
1548 $s = implode(' || ', $arr);
1555 public function sql_concat_join($separator="' '", $elements=array()) {
1556 for ($n=count($elements)-1; $n > 0 ; $n--) {
1557 array_splice($elements, $n, 0, $separator);
1559 $s = implode(' || ', $elements);
1567 * Returns the SQL for returning searching one string for the location of another.
1569 public function sql_position($needle, $haystack) {
1570 return "INSTR(($haystack), ($needle))";
1573 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1575 return " (".$this->sql_compare_text($fieldname)." = '".$this->sql_empty()."') ";
1577 return " ($fieldname = '".$this->sql_empty()."') ";
1581 public function sql_empty() {
1585 public function sql_order_by_text($fieldname, $numchars=32) {
1586 return 'dbms_lob.substr(' . $fieldname . ', ' . $numchars . ',1)';
1590 public function session_lock_supported() {
1591 if (isset($this->dblocks_supported
)) { // Use cached value if available
1592 return $this->dblocks_supported
;
1596 WHERE object_type = 'PACKAGE BODY'
1597 AND object_name = 'MOODLE_LOCKS'
1598 AND status = 'VALID'";
1599 $this->query_start($sql, null, SQL_QUERY_AUX
);
1600 $stmt = $this->parse_query($sql);
1601 $result = oci_execute($stmt, $this->commit_status
);
1602 $this->query_end($result, $stmt);
1604 oci_fetch_all($stmt, $records, 0, -1, OCI_FETCHSTATEMENT_BY_ROW
);
1605 oci_free_statement($stmt);
1606 $this->dblocks_supported
= isset($records[0]) && reset($records[0]) ?
true : false;
1607 return $this->dblocks_supported
;
1610 public function get_session_lock($rowid) {
1611 if (!$this->session_lock_supported()) {
1614 parent
::get_session_lock($rowid);
1616 $fullname = $this->dbname
.'-'.$this->prefix
.'-session-'.$rowid;
1617 $sql = 'SELECT MOODLE_LOCKS.GET_LOCK(:lockname, :locktimeout) FROM DUAL';
1618 $params = array('lockname' => $fullname , 'locktimeout' => 120);
1619 $this->query_start($sql, $params, SQL_QUERY_AUX
);
1620 $stmt = $this->parse_query($sql);
1621 $this->bind_params($stmt, $params);
1622 $result = oci_execute($stmt, $this->commit_status
);
1623 $this->query_end($result, $stmt);
1624 oci_free_statement($stmt);
1627 public function release_session_lock($rowid) {
1628 if (!$this->session_lock_supported()) {
1631 parent
::release_session_lock($rowid);
1633 $fullname = $this->dbname
.'-'.$this->prefix
.'-session-'.$rowid;
1634 $params = array('lockname' => $fullname);
1635 $sql = 'SELECT MOODLE_LOCKS.RELEASE_LOCK(:lockname) FROM DUAL';
1636 $this->query_start($sql, $params, SQL_QUERY_AUX
);
1637 $stmt = $this->parse_query($sql);
1638 $this->bind_params($stmt, $params);
1639 $result = oci_execute($stmt, $this->commit_status
);
1640 $this->query_end($result, $stmt);
1641 oci_free_statement($stmt);
1646 * Driver specific start of real database transaction,
1647 * this can not be used directly in code.
1650 protected function begin_transaction() {
1651 $this->commit_status
= OCI_DEFAULT
; //Done! ;-)
1655 * Driver specific commit of real database transaction,
1656 * this can not be used directly in code.
1659 protected function commit_transaction() {
1660 $this->query_start('--oracle_commit', NULL, SQL_QUERY_AUX
);
1661 $result = oci_commit($this->oci
);
1662 $this->commit_status
= OCI_COMMIT_ON_SUCCESS
;
1663 $this->query_end($result);
1667 * Driver specific abort of real database transaction,
1668 * this can not be used directly in code.
1671 protected function rollback_transaction() {
1672 $this->query_start('--oracle_rollback', NULL, SQL_QUERY_AUX
);
1673 $result = oci_rollback($this->oci
);
1674 $this->commit_status
= OCI_COMMIT_ON_SUCCESS
;
1675 $this->query_end($result);