class MySQLDatabase extends Database implements TransactionManager

MySQL connector class.

Supported indexes for {@link requireTable()}

You are advised to backup your tables if changing settings on an existing database connection_charset and charset should be equal, similarly so should connection_collation and collation

Traits

Provides extensions to this object to integrate it with standard config API methods.

Config options

connection_charset String Default connection charset (may be overridden in $databaseConfig)
connection_collation string Default connection collation
charset string Default charset
collation string Default collation

Methods

getConnector()

Get the current connector

from Database
setConnector(DBConnector $connector)

Injector injection point for connector dependency

from Database
getSchemaManager()

Returns the current schema manager

from Database
setSchemaManager(DBSchemaManager $schemaManager)

Injector injection point for schema manager

from Database
getQueryBuilder()

Returns the current query builder

from Database
setQueryBuilder(DBQueryBuilder $queryBuilder)

Injector injection point for schema manager

from Database
query(string $sql, int $errorLevel = E_USER_ERROR)

Execute the given SQL query.

preparedQuery(string $sql, array $parameters, int $errorLevel = E_USER_ERROR)

Execute the given SQL parameterised query with the specified arguments

integer
getGeneratedID(string $table)

Get the autogenerated ID from the previous INSERT query.

from Database
boolean
isActive()

Determines if we are connected to a server AND have a valid database selected.

from Database
string
escapeString(mixed $value)

Returns an escaped string. This string won't be quoted, so would be suitable for appending to other quoted strings.

from Database
string
quoteString(mixed $value)

Wrap a string into DB-specific quotes.

from Database
string
escapeIdentifier(string|array $value, string $separator = '.')

Escapes an identifier (table / database name). Typically the value is simply double quoted. Don't pass in already escaped identifiers in, as this will double escape the value!

from Database
manipulate(array $manipulation)

Execute a complex manipulation on the database.

from Database
quiet()

Enable supression of database messages.

from Database
clearAllData()

Clear all data out of the database

from Database
clearTable(string $table)

Clear all data in a given table

string
nullCheckClause(string $field, bool $isNull)

Generates a WHERE clause for null comparison check

from Database
String
comparisonClause(String $field, String $value, boolean $exact = false, boolean $negate = false, boolean $caseSensitive = null, boolean $parameterised = false)

Generate a WHERE clause for text matching.

string
formattedDatetimeClause(string $date, string $format)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime in a certain format

string
datetimeIntervalClause($date, $interval)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime addition

string
datetimeDifferenceClause($date1, $date2)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime substraction

string
concatOperator()

String operator for concatenation of strings

from Database
boolean
supportsCollations()

Returns true if this database supports collations

Boolean
supportsTimezoneOverride()

Can the database override timezone as a connection setting, or does it use the system timezone exclusively?

string
getVersion()

Query for the version of the currently connected database

from Database
string
getDatabaseServer()

Get the database server type (e.g. mysql, postgresql).

int
affectedRows()

Return the number of rows affected by the previous operation.

from Database
searchEngine(array $classesToSearch, string $keywords, integer $start, integer $pageLength, string $sortBy = "Relevance DESC", string $extraFilter = "", boolean $booleanSearch = false, string $alternativeFileFilter = "", boolean $invertedMatch = false)

The core search engine, used by this class and its subclasses to do fun stuff.

boolean
supportsTransactions()

Determines if this database supports transactions

boolean
supportsSavepoints()

Does this database support savepoints in transactions By default it is assumed that they don't unless they are explicitly enabled.

withTransaction(callable $callback, callable $errorCallback = null, bool|string $transactionMode = false, bool $errorIfTransactionsUnsupported = false)

Invoke $callback within a transaction

from Database
supportsExtensions($extensions)

No description

from Database
transactionStart(string|boolean $transactionMode = false, string|boolean $sessionCharacteristics = false)

Start a prepared transaction See http://developer.postgresql.org/pgdocs/postgres/sql-set-transaction.html for details on transaction isolation options

transactionSavepoint(string $savepoint)

Create a savepoint that you can jump back to if you encounter problems

bool|null
transactionRollback(string|boolean $savepoint = false)

Rollback or revert to a savepoint if your queries encounter problems If you encounter a problem at any point during a transaction, you may need to rollback that particular query, or return to a savepoint

bool|null
transactionEnd(bool $chain = false)

Commit everything inside this transaction so far

int
transactionDepth()

Return depth of current transaction

bool
supportsLocks()

Determines if the used database supports application-level locks, which is different from table- or row-level locking.

bool
canLock(string $name)

Returns if the lock is available.

bool
getLock(string $name, integer $timeout = 5)

Sets an application-level lock so that no two processes can run at the same time, also called a "cooperative advisory lock".

bool
releaseLock(string $name)

Remove an application-level lock file to allow another process to run (if the execution aborts (e.g. due to an error) all locks are automatically released).

connect(array $parameters)

Instruct the database to generate a live connection

bool
databaseExists(string $name)

Determine if the database with the specified name exists

from Database
array
databaseList()

Retrieves the list of all databases the user has access to

from Database
bool
selectDatabase(string $name, bool $create = false, int|bool $errorLevel = E_USER_ERROR)

Change the connection to the specified database, optionally creating the database if it doesn't exist in the current schema.

from Database
dropSelectedDatabase()

Drop the database that this object is currently connected to.

from Database
string|null
getSelectedDatabase()

Returns the name of the currently selected database

from Database
string
now()

Return SQL expression used to represent the current date/time

string
random()

Returns the database-specific version of the random() function

static Config_ForClass
config()

Get a configuration accessor for this class. Short hand for Config::inst()->get($this->class, .

mixed
stat(string $name) deprecated

Get inherited config value

mixed
uninherited(string $name)

Gets the uninherited value for the given config option

$this
set_stat(string $name, mixed $value) deprecated

Update the config value for a given property

setSQLMode(string $mode)

Sets the SQL mode

selectTimezone(string $timezone)

Sets the system timezone for the database connection

Details

in Database at line 51
DBConnector getConnector()

Get the current connector

Return Value

DBConnector

in Database at line 61
setConnector(DBConnector $connector)

Injector injection point for connector dependency

Parameters

DBConnector $connector

in Database at line 78
DBSchemaManager getSchemaManager()

Returns the current schema manager

Return Value

DBSchemaManager

in Database at line 88
setSchemaManager(DBSchemaManager $schemaManager)

Injector injection point for schema manager

Parameters

DBSchemaManager $schemaManager

in Database at line 109
DBQueryBuilder getQueryBuilder()

Returns the current query builder

Return Value

DBQueryBuilder

in Database at line 119
setQueryBuilder(DBQueryBuilder $queryBuilder)

Injector injection point for schema manager

Parameters

DBQueryBuilder $queryBuilder

at line 377
Query query(string $sql, int $errorLevel = E_USER_ERROR)

Execute the given SQL query.

Parameters

string $sql The SQL query to execute
int $errorLevel The level of error reporting to enable for the query

Return Value

Query

at line 383
Query preparedQuery(string $sql, array $parameters, int $errorLevel = E_USER_ERROR)

Execute the given SQL parameterised query with the specified arguments

Parameters

string $sql The SQL query to execute. The ? character will denote parameters.
array $parameters An ordered list of arguments.
int $errorLevel The level of error reporting to enable for the query

Return Value

Query

in Database at line 238
integer getGeneratedID(string $table)

Get the autogenerated ID from the previous INSERT query.

Parameters

string $table The name of the table to get the generated ID for

Return Value

integer the most recently generated ID for the specified table

in Database at line 249
boolean isActive()

Determines if we are connected to a server AND have a valid database selected.

Return Value

boolean Flag indicating that a valid database is connected

in Database at line 261
string escapeString(mixed $value)

Returns an escaped string. This string won't be quoted, so would be suitable for appending to other quoted strings.

Parameters

mixed $value Value to be prepared for database query

Return Value

string Prepared string

in Database at line 272
string quoteString(mixed $value)

Wrap a string into DB-specific quotes.

Parameters

mixed $value Value to be prepared for database query

Return Value

string Prepared string

in Database at line 286
string escapeIdentifier(string|array $value, string $separator = '.')

Escapes an identifier (table / database name). Typically the value is simply double quoted. Don't pass in already escaped identifiers in, as this will double escape the value!

Parameters

string|array $value The identifier to escape or list of split components
string $separator Splitter for each component

Return Value

string

in Database at line 326
manipulate(array $manipulation)

Execute a complex manipulation on the database.

A manipulation is an array of insert / or update sequences. The keys of the array are table names, and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update', and fields should be a map of field names to field values, NOT including quotes.

The field values could also be in paramaterised format, such as array('MAX(?,?)' => array(42, 69)), allowing the use of raw SQL values such as array('NOW()' => array()).

Parameters

array $manipulation

See also

SQLWriteExpression::addAssignments for syntax examples

in Database at line 383
quiet()

Enable supression of database messages.

in Database at line 391
clearAllData()

Clear all data out of the database

at line 532
clearTable(string $table)

Clear all data in a given table

Parameters

string $table Name of table

in Database at line 416
string nullCheckClause(string $field, bool $isNull)

Generates a WHERE clause for null comparison check

Parameters

string $field Quoted field name
bool $isNull Whether to check for NULL or NOT NULL

Return Value

string Non-parameterised null comparison clause

at line 405
String comparisonClause(String $field, String $value, boolean $exact = false, boolean $negate = false, boolean $caseSensitive = null, boolean $parameterised = false)

Generate a WHERE clause for text matching.

Parameters

String $field Quoted field name
String $value Escaped search. Can include percentage wildcards. Ignored if $parameterised is true.
boolean $exact Exact matches or wildcard support.
boolean $negate Negate the clause.
boolean $caseSensitive Enforce case sensitivity if TRUE or FALSE. Fallback to default collation if set to NULL.
boolean $parameterised Insert the ? placeholder rather than the given value. If this is true then $value is ignored.

Return Value

String SQL

at line 429
string formattedDatetimeClause(string $date, string $format)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime in a certain format

Parameters

string $date to be formated, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
string $format to be used, supported specifiers: %Y = Year (four digits) %m = Month (01..12) %d = Day (01..31) %H = Hour (00..23) %i = Minutes (00..59) %s = Seconds (00..59) %U = unix timestamp, can only be used on it's own

Return Value

string SQL datetime expression to query for a formatted datetime

at line 451
string datetimeIntervalClause($date, $interval)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime addition

Parameters

$date
$interval

Return Value

string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition

at line 464
string datetimeDifferenceClause($date1, $date2)

function to return an SQL datetime expression that can be used with the adapter in use used for querying a datetime substraction

Parameters

$date1
$date2

Return Value

string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction

in Database at line 504
string concatOperator()

String operator for concatenation of strings

Return Value

string

at line 128
boolean supportsCollations()

Returns true if this database supports collations

Return Value

boolean

at line 133
Boolean supportsTimezoneOverride()

Can the database override timezone as a connection setting, or does it use the system timezone exclusively?

Return Value

Boolean

in Database at line 529
string getVersion()

Query for the version of the currently connected database

Return Value

string Version of this database

at line 138
string getDatabaseServer()

Get the database server type (e.g. mysql, postgresql).

This value is passed to the connector as the 'driver' argument when initiating a database connection

Return Value

string

in Database at line 547
int affectedRows()

Return the number of rows affected by the previous operation.

Return Value

int

at line 159
PaginatedList searchEngine(array $classesToSearch, string $keywords, integer $start, integer $pageLength, string $sortBy = "Relevance DESC", string $extraFilter = "", boolean $booleanSearch = false, string $alternativeFileFilter = "", boolean $invertedMatch = false)

The core search engine, used by this class and its subclasses to do fun stuff.

Searches both SiteTree and File.

Parameters

array $classesToSearch List of classes to search
string $keywords Keywords as a string.
integer $start Item to start returning results from
integer $pageLength Number of items per page
string $sortBy Sort order expression
string $extraFilter Additional filter
boolean $booleanSearch Flag for boolean search mode
string $alternativeFileFilter
boolean $invertedMatch

Return Value

PaginatedList Search results

Exceptions

Exception

at line 322
boolean supportsTransactions()

Determines if this database supports transactions

Return Value

boolean Flag indicating support for transactions

at line 326
boolean supportsSavepoints()

Does this database support savepoints in transactions By default it is assumed that they don't unless they are explicitly enabled.

Return Value

boolean Flag indicating support for savepoints in transactions

in Database at line 607
withTransaction(callable $callback, callable $errorCallback = null, bool|string $transactionMode = false, bool $errorIfTransactionsUnsupported = false)

Invoke $callback within a transaction

Parameters

callable $callback Callback to run
callable $errorCallback Optional callback to run after rolling back transaction.
bool|string $transactionMode Optional transaction mode to use
bool $errorIfTransactionsUnsupported If true, this method will fail if transactions are unsupported. Otherwise, the $callback will potentially be invoked outside of a transaction.

Exceptions

Exception

in Database at line 645
supportsExtensions($extensions)

Parameters

$extensions

at line 331
transactionStart(string|boolean $transactionMode = false, string|boolean $sessionCharacteristics = false)

Start a prepared transaction See http://developer.postgresql.org/pgdocs/postgres/sql-set-transaction.html for details on transaction isolation options

Parameters

string|boolean $transactionMode Transaction mode, or false to ignore
string|boolean $sessionCharacteristics Session characteristics, or false to ignore

at line 336
transactionSavepoint(string $savepoint)

Create a savepoint that you can jump back to if you encounter problems

Parameters

string $savepoint Name of savepoint

at line 341
bool|null transactionRollback(string|boolean $savepoint = false)

Rollback or revert to a savepoint if your queries encounter problems If you encounter a problem at any point during a transaction, you may need to rollback that particular query, or return to a savepoint

Parameters

string|boolean $savepoint Name of savepoint, or leave empty to rollback to last savepoint

Return Value

bool|null Boolean is returned if success state is known, or null if unknown. Note: For error checking purposes null should not be treated as error.

at line 351
bool|null transactionEnd(bool $chain = false)

Commit everything inside this transaction so far

Parameters

bool $chain

Return Value

bool|null Boolean is returned if success state is known, or null if unknown. Note: For error checking purposes null should not be treated as error.

at line 346
int transactionDepth()

Return depth of current transaction

Return Value

int Nesting level, or 0 if not in a transaction

at line 482
bool supportsLocks()

Determines if the used database supports application-level locks, which is different from table- or row-level locking.

See {@link getLock()} for details.

Return Value

bool Flag indicating that locking is available

at line 487
bool canLock(string $name)

Returns if the lock is available.

See {@link supportsLocks()} to check if locking is generally supported.

Parameters

string $name Name of the lock

Return Value

bool

at line 493
bool getLock(string $name, integer $timeout = 5)

Sets an application-level lock so that no two processes can run at the same time, also called a "cooperative advisory lock".

Return FALSE if acquiring the lock fails; otherwise return TRUE, if lock was acquired successfully. Lock is automatically released if connection to the database is broken (either normally or abnormally), making it less prone to deadlocks than session- or file-based locks. Should be accompanied by a {@link releaseLock()} call after the logic requiring the lock has completed. Can be called multiple times, in which case locks "stack" (PostgreSQL, SQL Server), or auto-releases the previous lock (MySQL).

Note that this might trigger the database to wait for the lock to be released, delaying further execution.

Parameters

string $name Name of lock
integer $timeout Timeout in seconds

Return Value

bool

at line 503
bool releaseLock(string $name)

Remove an application-level lock file to allow another process to run (if the execution aborts (e.g. due to an error) all locks are automatically released).

Parameters

string $name Name of the lock

Return Value

bool Flag indicating whether the lock was successfully released

at line 67
connect(array $parameters)

Instruct the database to generate a live connection

Parameters

array $parameters An map of parameters, which should include: - server: The server, eg, localhost - username: The username to log on with - password: The password to log on with - database: The database to connect to - charset: The character set to use. Defaults to utf8 - timezone: (optional) The timezone offset. For example: +12:00, "Pacific/Auckland", or "SYSTEM" - driver: (optional) Driver name

in Database at line 796
bool databaseExists(string $name)

Determine if the database with the specified name exists

Parameters

string $name Name of the database to check for

Return Value

bool Flag indicating whether this database exists

in Database at line 806
array databaseList()

Retrieves the list of all databases the user has access to

Return Value

array List of database names

in Database at line 823
bool selectDatabase(string $name, bool $create = false, int|bool $errorLevel = E_USER_ERROR)

Change the connection to the specified database, optionally creating the database if it doesn't exist in the current schema.

Parameters

string $name Name of the database
bool $create Flag indicating whether the database should be created if it doesn't exist. If $create is false and the database doesn't exist then an error will be raised
int|bool $errorLevel The level of error reporting to enable for the query, or false if no error should be raised

Return Value

bool Flag indicating success

in Database at line 849
dropSelectedDatabase()

Drop the database that this object is currently connected to.

Use with caution.

in Database at line 863
string|null getSelectedDatabase()

Returns the name of the currently selected database

Return Value

string|null Name of the selected database, or null if none selected

at line 516
string now()

Return SQL expression used to represent the current date/time

Return Value

string Expression for the current date/time

at line 522
string random()

Returns the database-specific version of the random() function

Return Value

string Expression for a random value

in Configurable at line 20
static Config_ForClass config()

Get a configuration accessor for this class. Short hand for Config::inst()->get($this->class, .

....).

Return Value

Config_ForClass

in Configurable at line 32
mixed stat(string $name) deprecated

deprecated 5.0 Use ->config()->get() instead

Get inherited config value

Parameters

string $name

Return Value

mixed

in Configurable at line 44
mixed uninherited(string $name)

Gets the uninherited value for the given config option

Parameters

string $name

Return Value

mixed

in Configurable at line 57
$this set_stat(string $name, mixed $value) deprecated

deprecated 5.0 Use ->config()->set() instead

Update the config value for a given property

Parameters

string $name
mixed $value

Return Value

$this

at line 107
setSQLMode(string $mode)

Sets the SQL mode

Parameters

string $mode Connection mode

at line 120
selectTimezone(string $timezone)

Sets the system timezone for the database connection

Parameters

string $timezone