dml

Namespaces
namespace	core\dml
	Applies the same callback to all recorset records.

Classes
class	core\dml\recordset_walk
	Iterator that walks through a moodle_recordset applying the provided function. More...
class	core\dml\sql_join
	An object that contains sql join fragments. More...
class	core\dml\table
	Helpers and methods relating to DML tables. More...
class	dml_connection_exception
	DML db connection exception - triggered if database not accessible. More...
class	dml_exception
	DML exception class, use instead of throw new moodle_exception() in dml code. More...
class	dml_missing_record_exception
	Caused by missing record that is required for normal operation. More...
class	dml_multiple_records_exception
	Caused by multiple records found in get_record() call. More...
class	dml_read_exception
	DML read exception - triggered by some SQL syntax errors, etc. More...
class	dml_sessionwait_exception
	DML db session wait exception - triggered when session lock request times out. More...
class	dml_transaction_exception
	DML transaction exception - triggered by problems related to DB transactions. More...
class	dml_write_exception
	DML write exception - triggered by some SQL syntax errors, etc. More...

Functions
	can_use_readonly (int $type, string $sql)
	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.
	commit_delegated_transaction (moodle_transaction $transaction)
	Indicates delegated transaction finished successfully.
	connect ($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null)
	Connect to db The connection parameters processor that sets up stage for master write and slave readonly handles.
	get_db_handle ()
	Gets db handle currently used with queries.
	perf_get_reads_slave ()
	Returns the number of reads done by the read only database.
	query_end ($result)
	This should be called immediately after each db query.
	query_start ($sql, ?array $params, $type, $extrainfo=null)
	Called before each db query.
	raw_connect (string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null)
	Connect to db The real connection establisment, called from connect() and set_dbhwrite()
	select_db_handle (int $type, string $sql)
	Select appropriate db handle - readwrite or readonly.
	set_db_handle ($dbh)
	Sets db handle to be used with subsequent queries.
stdClass	setup_DB ()
	Sets up global $DB moodle_database instance.
	start_delegated_transaction ()
	On DBs that support it, switch to transaction mode and begin a transaction.
	table_names (string $sql)
	Parse table names from query.
	want_read_slave ()
	Returns whether we want to connect to slave database for read queries.

Variables
resource	$dbhreadonly
	slave read only database handle
const	IGNORE_MISSING 0
	Return false if record not found, show debug warning if multiple records found.
const	IGNORE_MULTIPLE 1
	Similar to IGNORE_MISSING but does not show debug warning if multiple records found, not recommended to be used.
trait	moodle_read_slave_trait
	Trait to wrap connect() method of database driver classes that gives ability to use read only slave instances for SELECT queries.
const	MUST_EXIST 2
	Indicates exactly one record must exist.

Detailed Description

Function Documentation

can_use_readonly()

can_use_readonly ( int $type, string $sql )

protected

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.

Parameters

int	$type	type of query
string	$sql

Return values

bool

commit_delegated_transaction()

commit_delegated_transaction

(

moodle_transaction

$transaction

)

Indicates delegated transaction finished successfully.

Set written times after outermost transaction finished

Parameters

moodle_transaction

$transaction

The transaction to commit

Return values

void

Exceptions

dml_transaction_exception

Creates and throws transaction related exceptions.

connect()

connect	(		$dbhost,
			$dbuser,
			$dbpass,
			$dbname,
			$prefix,
		array	$dboptions = null )

Connect to db The connection parameters processor that sets up stage for master write and slave readonly handles.

Must be called before other methods.

Parameters

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

Return values

bool

true

Exceptions

dml_connection_exception

if error

get_db_handle()

get_db_handle ( )

abstractprotected

Gets db handle currently used with queries.

Return values

resource

perf_get_reads_slave()

perf_get_reads_slave

(

)

Returns the number of reads done by the read only database.

Return values

int	Number of reads.

query_end()

query_end ( $result )

protected

This should be called immediately after each db query.

It does a clean up of resources.

Parameters

mixed

$result

The db specific result obtained from running a query.

Return values

void

query_start()

query_start ( $sql, ?array $params, $type, $extrainfo = null )

protected

Called before each db query.

Parameters

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

Return values

void

raw_connect()

raw_connect ( string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions = null )

abstractprotected

Connect to db The real connection establisment, called from connect() and set_dbhwrite()

Parameters

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

Return values

bool

true

Exceptions

dml_connection_exception

if error

select_db_handle()

select_db_handle ( int $type, string $sql )

protected

Select appropriate db handle - readwrite or readonly.

Parameters

int	$type	type of query
string	$sql

Return values

void

set_db_handle()

set_db_handle ( $dbh )

abstractprotected

Sets db handle to be used with subsequent queries.

Parameters

resource

$dbh

Return values

void

setup_DB()

stdClass setup_DB

(

)

Sets up global $DB moodle_database instance.

$CFG The global configuration instance.

See also

config.php

config-dist.php @global stdClass $DB The global moodle_database instance.

Return values

void|bool

Returns true when finished setting up $DB. Returns void when $DB has already been set.

start_delegated_transaction()

start_delegated_transaction

(

)

On DBs that support it, switch to transaction mode and begin a transaction.

Return values

moodle_transaction

table_names()

table_names ( string $sql )

protected

Parse table names from query.

Parameters

string

$sql

Return values

array

want_read_slave()

want_read_slave

(

)

Returns whether we want to connect to slave database for read queries.

Return values

bool	Want read only connection

Variable Documentation

moodle_read_slave_trait

trait moodle_read_slave_trait

Initial value:

{
 
    
    protected resource $dbhwrite

Trait to wrap connect() method of database driver classes that gives ability to use read only slave instances for SELECT queries.

For the databases that support replication and read only connections to the slave. If the slave connection is configured there will be two database handles created, one for the master and another one for the slave. If there's no slave specified everything uses master handle.

Classes that use this trait need to rename existing connect() method to raw_connect(). In addition, they need to provide get_db_handle() and set_db_handle() methods, due to dbhandle attributes not being named consistently across the database driver classes.

Read only slave connection is configured in the $CFG->dboptions['readonly'] array.

• It supports multiple 'instance' entries, in case one is not accessible, but only one (first connectable) instance is used.
• 'latency' option: master -> slave sync latency in seconds (will probably be a fraction of a second). A table being written to is deemed fully synced after that period and suitable for slave read. Defaults to 1 sec.
• 'exclude_tables' option: a list of tables that never go to the slave for querying. The feature is meant to be used in emergency only, so the readonly feature can still be used in case there is a rogue query that does not go through the standard dml interface or some other unaccounted situation. It should not be used under normal circumstances, and its use indicates a problem in the system that needs addressig.

Choice of the database handle is based on following:

• SQL_QUERY_INSERT, UPDATE and STRUCTURE record table from the query in the $written array and microtime() the event. For those queries master write handle is used.
• SQL_QUERY_AUX queries will always use the master write handle because they are used for transaction start/end, locking etc. In that respect, query_start() and query_end() must not be used during the connection phase.
• SQL_QUERY_AUX_READONLY queries will use the master write handle if in a transaction.
• SELECT queries will use the master write handle if: – any of the tables involved is a temp table – any of the tables involved is listed in the 'exclude_tables' option – any of the tables involved is in the $written array:
  • current microtime() is compared to the write microrime, and if more than latency time has passed the slave handle is used
  • otherwise (not enough time passed) we choose the master write handle If none of the above conditions are met the slave instance is used.

A 'latency' example:

• we have set $CFG->dboptions['readonly']['latency'] to 0.2.
• a SQL_QUERY_UPDATE to table tbl_x happens, and it is recorded in the $written array
• 0.15 seconds later SQL_QUERY_SELECT with tbl_x is requested - the master connection is used
• 0.10 seconds later (0.25 seconds after SQL_QUERY_UPDATE) another SQL_QUERY_SELECT with tbl_x is requested - this time more than 0.2 secs has gone and master -> slave sync is assumed, so the slave connection is used again