Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 and 403]

Native oci class representing moodle database interface.

Copyright: 2008 Petr Skoda (http://skodak.org)
License: http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
File Size: 1906 lines (79 kb)
Included or required:0 times
Referenced: 0 times
Includes or requires: 3 files
 lib/dml/oci_native_moodle_recordset.php
 lib/dml/oci_native_moodle_temptables.php
 lib/dml/moodle_database.php

Defines 1 class

oci_native_moodle_database:: (70 methods):
  driver_installed()
  get_dbfamily()
  get_dbtype()
  get_dblibrary()
  get_name()
  get_configuration_help()
  diagnose()
  connect()
  dispose()
  query_start()
  query_end()
  get_server_info()
  fix_table_names()
  allowed_param_types()
  get_last_error()
  parse_query()
  tweak_param_names()
  get_tables()
  get_indexes()
  fetch_columns()
  normalise_value()
  get_limit_sql()
  oracle_dirty_hack()
  compare_by_length_desc()
  setup_is_unicodedb()
  change_database_structure()
  bind_params()
  free_descriptors()
  onespace2empty()
  execute()
  get_record_sql()
  get_recordset_sql()
  create_recordset()
  get_records_sql()
  get_fieldset_sql()
  insert_record_raw()
  insert_record()
  import_record()
  update_record_raw()
  update_record()
  set_field_select()
  delete_records_select()
  sql_null_from_clause()
  sql_bitand()
  sql_bitnot()
  sql_bitor()
  sql_bitxor()
  sql_modulo()
  sql_cast_to_char()
  sql_cast_char2int()
  sql_cast_char2real()
  sql_like()
  sql_concat()
  sql_concat_join()
  sql_group_concat()
  sql_order_by_null()
  get_in_or_equal()
  recursive_concat()
  sql_position()
  sql_isempty()
  sql_order_by_text()
  oci_package_installed()
  attempt_oci_package_install()
  replace_all_text_supported()
  session_lock_supported()
  get_session_lock()
  release_session_lock()
  begin_transaction()
  commit_transaction()
  rollback_transaction()


Class: oci_native_moodle_database  - X-Ref

Native oci class representing moodle database interface.

One complete reference for PHP + OCI:
http://www.oracle.com/technology/tech/php/underground-php-oracle-manual.html

driver_installed()   X-Ref
Detects if all needed PHP stuff installed.
Note: can be used before connect()

return: mixed true if ok, string if something

get_dbfamily()   X-Ref
Returns database family type - describes SQL dialect
Note: can be used before connect()

return: string db family name (mysql, postgres, mssql, oracle, etc.)

get_dbtype()   X-Ref
Returns more specific database driver type
Note: can be used before connect()

return: string db type mysqli, pgsql, oci, mssql, sqlsrv

get_dblibrary()   X-Ref
Returns general database library name
Note: can be used before connect()

return: string db type pdo, native

get_name()   X-Ref
Returns localised database type name
Note: can be used before connect()

return: string

get_configuration_help()   X-Ref
Returns localised database configuration help.
Note: can be used before connect()

return: string

diagnose()   X-Ref
Diagnose database and tables, this function is used
to verify database and driver settings, db engine types, etc.

return: string null means everything ok, string means problem found.

connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null)   X-Ref
Connect to db
Must be called before other methods.

param: string $dbhost The database host.
param: string $dbuser The database username.
param: string $dbpass The database username's password.
param: string $dbname The name of the database being connected to.
param: mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
param: array $dboptions driver specific options
return: bool true

dispose()   X-Ref
Close database connection and release all resources
and memory (especially circular memory references).
Do NOT use connect() again, create a new instance if needed.


query_start($sql, ?array $params, $type, $extrainfo=null)   X-Ref
Called before each db query.

param: string $sql
param: array|null $params An array of parameters.
param: int $type type of query
param: mixed $extrainfo driver specific extra information
return: void

query_end($result, $stmt=null)   X-Ref
Called immediately after each db query.

param: mixed db specific result
return: void

get_server_info()   X-Ref
Returns database server info array

return: array Array containing 'description' and 'version' info

fix_table_names($sql)   X-Ref
Converts short table name {tablename} to real table name
supporting temp tables ($this->unique_session_id based) if detected

param: string sql
return: string sql

allowed_param_types()   X-Ref
Returns supported query parameter types

return: int bitmask of accepted SQL_PARAMS_*

get_last_error()   X-Ref
Returns last error reported by database engine.

return: string error message

parse_query($sql)   X-Ref
Prepare the statement for execution

param: string $sql
return: resource

tweak_param_names($sql, array $params)   X-Ref
Make sure there are no reserved words in param names...

param: string $sql
param: array $params
return: array ($sql, $params) updated query and parameters

get_tables($usecache=true)   X-Ref
Return tables in database WITHOUT current prefix

param: bool $usecache if true, returns list of cached tables.
return: array of table names in lowercase and without prefix

get_indexes($table)   X-Ref
Return table indexes - everything lowercased.

param: string $table The table we want to get indexes from.
return: array An associative array of indexes containing 'unique' flag and 'columns' being indexed

fetch_columns(string $table)   X-Ref
Fetches detailed information about columns in table.

param: string $table name
return: array array of database_column_info objects indexed with column names

normalise_value($column, $value)   X-Ref
Normalise values based in RDBMS dependencies (booleans, LOBs...)

param: database_column_info $column column metadata corresponding with the value we are going to normalise
param: mixed $value value we are going to normalise
return: mixed the normalised value

get_limit_sql($sql, array $params = null, $limitfrom=0, $limitnum=0)   X-Ref
Transforms the sql and params in order to emulate the LIMIT clause available in other DBs

param: string $sql the SQL select query to execute.
param: array $params array of sql parameters
param: int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
param: int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
return: array with the transformed sql and params updated

oracle_dirty_hack($table, $field, $value)   X-Ref
This function will handle all the column values before being inserted/updated to DB for Oracle
installations. This is because the "special feature" of Oracle where the empty string is
equal to NULL and this presents a problem with all our currently NOT NULL default '' fields.
(and with empties handling in general)

Note that this function is 100% private and should be used, exclusively by DML functions
in this file. Also, this is considered a DIRTY HACK to be removed when possible.

This function is private and must not be used outside this driver at all

param: $table string the table where the record is going to be inserted/updated (without prefix)
param: $field string the field where the record is going to be inserted/updated
param: $value mixed the value to be inserted/updated

compare_by_length_desc($a, $b)   X-Ref
Helper function to order by string length desc

param: $a string first element to compare
param: $b string second element to compare
return: int < 0 $a goes first (is less), 0 $b goes first, 0 doesn't matter

setup_is_unicodedb()   X-Ref
Is db in unicode mode?

return: bool

change_database_structure($sql, $tablenames = null)   X-Ref
Do NOT use in code, to be used by database_manager only!

param: string|array $sql query
param: array|null $tablenames an array of xmldb table names affected by this request.
return: bool true

bind_params($stmt, array &$params=null, $tablename=null, array &$descriptors = null)   X-Ref
No description

free_descriptors($descriptors)   X-Ref
No description

onespace2empty(&$item, $key=null)   X-Ref
This function is used to convert all the Oracle 1-space defaults to the empty string
like a really DIRTY HACK to allow it to work better until all those NOT NULL DEFAULT ''
fields will be out from Moodle.

param: string the string to be converted to '' (empty string) if it's ' ' (one space)
param: mixed the key of the array in case we are using this function from array_walk,
return: boolean always true (the converted variable is returned by reference)

execute($sql, array $params=null)   X-Ref
Execute general sql query. Should be used only when no other method suitable.
Do NOT use this to make changes in db structure, use database_manager methods instead!

param: string $sql query
param: array $params query parameters
return: bool true

get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING)   X-Ref
Get a single database record as an object using a SQL statement.

The SQL statement should normally only return one record.
It is recommended to use get_records_sql() if more matches possible!

param: string $sql The SQL string you wish to be executed, should normally only return one record.
param: array $params array of sql parameters
param: int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
return: mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode

get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)   X-Ref
Get a number of records as a moodle_recordset using a SQL statement.

Since this method is a little less readable, use of it should be restricted to
code where it's possible there might be large datasets being returned.  For known
small datasets use get_records_sql - it leads to simpler code.

The return type is like:
param: string $sql the SQL select query to execute.
param: array $params array of sql parameters
param: int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
param: int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
return: moodle_recordset instance

create_recordset($stmt)   X-Ref
No description

get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0)   X-Ref
Get a number of records as an array of objects using a SQL statement.

Return value is like:
param: string $sql the SQL select query to execute. The first column of this SELECT statement
param: array $params array of sql parameters
param: int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
param: int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
return: array of objects, or empty array if no records were found

get_fieldset_sql($sql, array $params=null)   X-Ref
Selects records and return values (first field) as an array using a SQL statement.

param: string $sql The SQL query
param: array $params array of sql parameters
return: array of values

insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false)   X-Ref
Insert new record into database, as fast as possible, no safety checks, lobs not supported.

param: string $table name
param: mixed $params data record as object or array
param: bool $returnit return it of inserted record
param: bool $bulk true means repeated inserts expected
param: bool $customsequence true if 'id' included in $params, disables $returnid
return: bool|int true or new id

insert_record($table, $dataobject, $returnid=true, $bulk=false)   X-Ref
Insert a record into a table and return the "id" field if required.

Some conversions and safety checks are carried out. Lobs are supported.
If the return ID isn't required, then this just reports success as true/false.
$data is an object containing needed data
param: string $table The database table to be inserted into
param: object|array $dataobject A data object with values for one or more fields in the record
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.
return: bool|int true or new id

import_record($table, $dataobject)   X-Ref
Import a record into a table, id field is required.
Safety checks are NOT carried out. Lobs are supported.

param: string $table name of database table to be inserted into
param: object $dataobject A data object with values for one or more fields in the record
return: bool true

update_record_raw($table, $params, $bulk=false)   X-Ref
Update record in database, as fast as possible, no safety checks, lobs not supported.

param: string $table name
param: mixed $params data record as object or array
param: bool true means repeated updates expected
return: bool true

update_record($table, $dataobject, $bulk=false)   X-Ref
Update a record in a table

$dataobject is an object containing needed data
Relies on $dataobject having a variable "id" to
specify the record to update

param: string $table The database table to be checked against.
param: object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
param: bool true means repeated updates expected
return: bool true

set_field_select($table, $newfield, $newvalue, $select, array $params=null)   X-Ref
Set a single field in every table record which match a particular WHERE clause.

param: string $table The database table to be checked against.
param: string $newfield the field to set.
param: string $newvalue the value to set the field to.
param: string $select A fragment of SQL to be used in a where clause in the SQL call.
param: array $params array of sql parameters
return: bool true

delete_records_select($table, $select, array $params=null)   X-Ref
Delete one or more records from a table which match a particular WHERE clause.

param: string $table The database table to be checked against.
param: string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
param: array $params array of sql parameters
return: bool true

sql_null_from_clause()   X-Ref
No description

sql_bitand($int1, $int2)   X-Ref
No description

sql_bitnot($int1)   X-Ref
No description

sql_bitor($int1, $int2)   X-Ref
No description

sql_bitxor($int1, $int2)   X-Ref
No description

sql_modulo($int1, $int2)   X-Ref
Returns the SQL text to be used in order to perform module '%'
operation - remainder after division

param: integer int1 first integer in the operation
param: integer int2 second integer in the operation
return: string the piece of SQL code to be used in your statement.

sql_cast_to_char(string $field)   X-Ref
Return SQL for casting to char of given field/expression

param: string $field Table field or SQL expression to be cast
return: string

sql_cast_char2int($fieldname, $text=false)   X-Ref
No description

sql_cast_char2real($fieldname, $text=false)   X-Ref
No description

sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\')   X-Ref
Returns 'LIKE' part of a query.

param: string $fieldname usually name of the table column
param: string $param usually bound query parameter (?, :named)
param: bool $casesensitive use case sensitive search
param: bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
param: bool $notlike true means "NOT LIKE"
param: string $escapechar escape char for '%' and '_'
return: string SQL code fragment

sql_concat()   X-Ref
No description

sql_concat_join($separator="' '", $elements = array()   X-Ref
No description

sql_group_concat(string $field, string $separator = ', ', string $sort = '')   X-Ref
Return SQL for performing group concatenation on given field/expression

param: string $field
param: string $separator
param: string $sort
return: string

sql_order_by_null(string $fieldname, int $sort = SORT_ASC)   X-Ref
Returns the SQL text to be used to order by columns, standardising the return
pattern of null values across database types to sort nulls first when ascending
and last when descending.

param: string $fieldname The name of the field we need to sort by.
param: int $sort An order to sort the results in.
return: string The piece of SQL code to be used in your statement.

get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false)   X-Ref
Constructs 'IN()' or '=' sql fragment

Method overriding {@link moodle_database::get_in_or_equal} to be able to get
more than 1000 elements working, to avoid ORA-01795. We use a pivoting technique
to be able to transform the params into virtual rows, so the original IN()
expression gets transformed into a subquery. Once more, be noted that we shouldn't
be using ever get_in_or_equal() with such number of parameters (proper subquery and/or
chunking should be used instead).

param: mixed $items A single value or array of values for the expression.
param: int $type Parameter bounding type : SQL_PARAMS_QM or SQL_PARAMS_NAMED.
param: string $prefix Named parameter placeholder prefix (a unique counter value is appended to each parameter name).
param: bool $equal True means we want to equate to the constructed expression, false means we don't want to equate to it.
param: mixed $onemptyitems This defines the behavior when the array of items provided is empty. Defaults to false,
return: array A list containing the constructed sql fragment and an array of parameters.

recursive_concat(array $args)   X-Ref
Mega hacky magic to work around crazy Oracle NULL concats.

param: array $args
return: string

sql_position($needle, $haystack)   X-Ref
Returns the SQL for returning searching one string for the location of another.


sql_isempty($tablename, $fieldname, $nullablefield, $textfield)   X-Ref
Returns the SQL to know if one field is empty.

param: string $tablename Name of the table (without prefix). Not used for now but can be
param: string $fieldname Name of the field we are going to check
param: bool $nullablefield For specifying if the field is nullable (true) or no (false) in the DB.
param: bool $textfield For specifying if it is a text (also called clob) field (true) or a varchar one (false)
return: string the sql code to be added to check for empty values

sql_order_by_text($fieldname, $numchars=32)   X-Ref
No description

oci_package_installed()   X-Ref
Is the required OCI server package installed?

return: bool

attempt_oci_package_install()   X-Ref
Try to add required moodle package into oracle server.


replace_all_text_supported()   X-Ref
Does this driver support tool_replace?

return: bool

session_lock_supported()   X-Ref
No description

get_session_lock($rowid, $timeout)   X-Ref
Obtain session lock

param: int $rowid id of the row with session record
param: int $timeout max allowed time to wait for the lock in seconds
return: void

release_session_lock($rowid)   X-Ref
No description

begin_transaction()   X-Ref
Driver specific start of real database transaction,
this can not be used directly in code.

return: void

commit_transaction()   X-Ref
Driver specific commit of real database transaction,
this can not be used directly in code.

return: void

rollback_transaction()   X-Ref
Driver specific abort of real database transaction,
this can not be used directly in code.

return: void