Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

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

Native mysqli 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: 2142 lines (81 kb)
Included or required: 3 times
Referenced: 0 times
Includes or requires: 4 files
 lib/dml/mysqli_native_moodle_recordset.php
 lib/dml/moodle_database.php
 lib/dml/moodle_read_slave_trait.php
 lib/dml/mysqli_native_moodle_temptables.php

Defines 1 class

mysqli_native_moodle_database:: (77 methods):
  create_database()
  driver_installed()
  get_dbfamily()
  get_dbtype()
  get_dblibrary()
  get_dbengine()
  get_dbcollation()
  detect_collation()
  is_antelope_file_format_no_more_supported()
  get_row_format()
  is_compressed_row_format_supported()
  is_file_per_table_enabled()
  is_large_prefix_enabled()
  get_row_format_sql()
  get_name()
  get_configuration_help()
  diagnose()
  raw_connect()
  dispose()
  get_db_handle()
  set_db_handle()
  can_use_readonly()
  get_server_info()
  allowed_param_types()
  get_last_error()
  get_tables()
  get_indexes()
  fetch_columns()
  has_breaking_change_quoted_defaults()
  has_breaking_change_sqlmode()
  get_column_info()
  mysqltype2moodletype()
  normalise_value()
  setup_is_unicodedb()
  change_database_structure()
  emulate_bound_params()
  execute()
  get_recordset_sql()
  export_table_recordset()
  create_recordset()
  get_records_sql()
  get_fieldset_sql()
  insert_record_raw()
  insert_record()
  insert_records()
  insert_chunk()
  import_record()
  update_record_raw()
  update_record()
  set_field_select()
  delete_records_select()
  delete_records_subquery()
  sql_cast_char2int()
  sql_cast_char2real()
  sql_equal()
  sql_like()
  sql_concat()
  sql_concat_join()
  sql_group_concat()
  sql_length()
  sql_regex_supported()
  sql_regex()
  sql_regex_get_word_beginning_boundary_marker()
  sql_regex_get_word_end_boundary_marker()
  sql_cast_2signed()
  sql_intersect()
  replace_all_text_supported()
  session_lock_supported()
  get_session_lock()
  release_session_lock()
  transactions_supported()
  begin_transaction()
  commit_transaction()
  rollback_transaction()
  convert_table_row_format()
  is_fulltext_search_supported()
  fix_table_name()


Class: mysqli_native_moodle_database  - X-Ref

Native mysqli class representing moodle database interface.

create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null)   X-Ref
Attempt to create the database

param: string $dbhost
param: string $dbuser
param: string $dbpass
param: string $dbname
return: bool success

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_dbengine()   X-Ref
Returns the current MySQL db engine.

This is an ugly workaround for MySQL default engine problems,
Moodle is designed to work best on ACID compliant databases
with full transaction support. Do not use MyISAM.

return: string or null MySQL engine name

get_dbcollation()   X-Ref
Returns the current MySQL db collation.

This is an ugly workaround for MySQL default collation problems.

return: string or null MySQL collation name

detect_collation()   X-Ref
Set 'dbcollation' option

return: string|null $dbcollation

is_antelope_file_format_no_more_supported()   X-Ref
Tests if the Antelope file format is still supported or it has been removed.
When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine.

return: bool True if the Antelope file format has been removed; otherwise, false.

get_row_format($table = null)   X-Ref
Get the row format from the database schema.

param: string $table
return: string row_format name or null if not known or table does not exist.

is_compressed_row_format_supported($cached = true)   X-Ref
Is this database compatible with compressed row format?
This feature is necessary for support of large number of text
columns in InnoDB/XtraDB database.

param: bool $cached use cached result
return: bool true if table can be created or changed to compressed row format.

is_file_per_table_enabled()   X-Ref
Check the database to see if innodb_file_per_table is on.

return: bool True if on otherwise false.

is_large_prefix_enabled()   X-Ref
Check the database to see if innodb_large_prefix is on.

return: bool True if on otherwise false.

get_row_format_sql($engine = null, $collation = null)   X-Ref
Determine if the row format should be set to compressed, dynamic, or default.

Terrible kludge. If we're using utf8mb4 AND we're using InnoDB, we need to specify row format to
be either dynamic or compressed (default is compact) in order to allow for bigger indexes (MySQL
errors #1709 and #1071).

param: string $engine The database engine being used. Will be looked up if not supplied.
param: string $collation The database collation to use. Will look up the current collation if not supplied.
return: string An sql fragment to add to sql statements.

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.

raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null)   X-Ref
Connect to db

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.e
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 success

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.


get_db_handle()   X-Ref
Gets db handle currently used with queries

return: resource

set_db_handle($dbh)   X-Ref
Sets db handle to be used with subsequent queries

param: resource $dbh
return: void

can_use_readonly(int $type, string $sql)   X-Ref
Check if The query qualifies for readonly connection execution
Logging queries are exempt, those are write operations that circumvent
standard query_start/query_end paths.

param: int $type type of query
param: string $sql
return: bool

get_server_info()   X-Ref
Returns database server info array

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

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

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: database_column_info[] array of database_column_info objects indexed with column names

has_breaking_change_quoted_defaults()   X-Ref
Indicates whether column information retrieved from `information_schema.columns` has default values quoted or not.

return: boolean True when default values are quoted (breaking change); otherwise, false.

has_breaking_change_sqlmode()   X-Ref
Indicates whether SQL_MODE default value has changed in a not backward compatible way.

return: boolean True when SQL_MODE breaks BC; otherwise, false.

get_column_info(stdClass $rawcolumn)   X-Ref
Returns moodle column info for raw column from information schema.

param: stdClass $rawcolumn
return: stdClass standardised colum info

mysqltype2moodletype($mysql_type)   X-Ref
Normalise column type.

param: string $mysql_type
return: string one character

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

setup_is_unicodedb()   X-Ref
Is this database compatible with utf8?

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

emulate_bound_params($sql, array $params=null)   X-Ref
Very ugly hack which emulates bound parameters in queries
because prepared statements do not use query cache.


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_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

export_table_recordset($table)   X-Ref
Get all records from a table.

This method works around potential memory problems and may improve performance,
this method may block access to table until the recordset is closed.

param: string $table Name of database table.
return: moodle_recordset A moodle_recordset instance {@link function get_recordset}.

create_recordset($result)   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

insert_records($table, $dataobjects)   X-Ref
Insert multiple records into database as fast as possible.

Order of inserts is maintained, but the operation is not atomic,
use transactions if necessary.

This method is intended for inserting of large number of small objects,
do not use for huge objects with text or binary fields.

param: string $table  The database table to be inserted into
param: array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
return: void does not return new record ids

insert_chunk($table, array $chunk, array $columns)   X-Ref
Insert records in chunks.

Note: can be used only from insert_records().

param: string $table
param: array $chunk
param: database_column_info[] $columns

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

delete_records_subquery(string $table, string $field, string $alias, string $subquery, array $params = [])   X-Ref
Deletes records using a subquery, which is done with a strange DELETE...JOIN syntax in MySQL
because it performs very badly with normal subqueries.

param: string $table Table to delete from
param: string $field Field in table to match
param: string $alias Name of single column in subquery e.g. 'id'
param: string $subquery Query that will return values of the field to delete
param: array $params Parameters for query

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

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

sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false)   X-Ref
No description

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

Note that mysql does not support $casesensitive = true and $accentsensitive = false.
More information in http://bugs.mysql.com/bug.php?id=19567.

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 (ignored if $casesensitive is true)
param: bool $notlike true means "NOT LIKE"
param: string $escapechar escape char for '%' and '_'
return: string SQL code fragment

sql_concat()   X-Ref
Returns the proper SQL to do CONCAT between the elements passed
Can take many parameters

param: string $str,... 1 or more fields/strings to concat
return: string The concat sql

sql_concat_join($separator="' '", $elements=array()   X-Ref
Returns the proper SQL to do CONCAT between the elements passed
with a given separator

param: string $separator The string to use as the separator
param: array $elements An array of items to concatenate
return: string The concat SQL

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_length($fieldname)   X-Ref
Returns the SQL text to be used to calculate the length in characters of one expression.

param: string fieldname or expression to calculate its length in characters.
return: string the piece of SQL code to be used in the statement.

sql_regex_supported()   X-Ref
Does this driver support regex syntax when searching


sql_regex($positivematch = true, $casesensitive = false)   X-Ref
Return regex positive or negative match sql

param: bool $positivematch
param: bool $casesensitive
return: string or empty if not supported

sql_regex_get_word_beginning_boundary_marker()   X-Ref
Returns the word-beginning boundary marker based on MySQL version.

return: string The word-beginning boundary marker.

sql_regex_get_word_end_boundary_marker()   X-Ref
Returns the word-end boundary marker based on MySQL version.

return: string The word-end boundary marker.

sql_cast_2signed($fieldname)   X-Ref
Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.

param: string $fieldname The name of the field to be cast
return: string The piece of SQL code to be used in your statement.

sql_intersect($selects, $fields)   X-Ref
Returns the SQL that allows to find intersection of two or more queries

param: array $selects array of SQL select queries, each of them only returns fields with the names from $fields
param: string $fields comma-separated list of fields
return: string SQL query that will return only values that are present in each of selects

replace_all_text_supported()   X-Ref
No description

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

transactions_supported()   X-Ref
Are transactions supported?
It is not responsible to run productions servers
on databases without transaction support ;-)

MyISAM does not support support transactions.

You can override this via the dbtransactions option.

return: bool

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

convert_table_row_format($tablename)   X-Ref
Converts a table to either 'Compressed' or 'Dynamic' row format.

param: string $tablename Name of the table to convert to the new row format.

is_fulltext_search_supported()   X-Ref
Does this mysql instance support fulltext indexes?

return: bool

fix_table_name($tablename)   X-Ref
Fixes any table names that clash with reserved words.

param: string $tablename The table name
return: string The fixed table name