Version: current

# Class MySQLDatabase

MySQL connector class.

Supported indexes for `SS_Database::requireTable()`:

  SS_Database
  MySQLDatabase

Package: sapphire\model
Located at sapphire/core/model/MySQLDatabase.php

## Methods summary

`public static` | # `set_connection_charset( mixed $charset = 'utf8' )` Sets the character set for the MySQL database connection. Sets the character set for the MySQL database connection. The character set connection should be set to 'utf8' for SilverStripe version 2.4.0 and later. However, sites created before version 2.4.0 should leave this unset or data that isn't 7-bit safe will be corrupted. As such, the installer comes with this set in mysite/_config.php by default in versions 2.4.0 and later.
`public` | # `__construct( array $parameters )` Connect to a MySQL database. Connect to a MySQL database. #### Parameters $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 - timezone: (optional) the timezone offset, eg: +12:00 for NZ time
`public string` | # `getConnect( array $parameters )` Not implemented, needed for PDO Not implemented, needed for PDO #### Parameters $parameters array $parameters The connection details #### Returns `string` $connect The connection string
`public boolean` | # `supportsCollations( )` Returns true if this database supports collations Returns true if this database supports collations #### Returns `boolean` boolean
`public float` | # `getVersion( )` Get the version of MySQL. Get the version of MySQL. #### Returns `float` float
`public string` | # `getDatabaseServer( )` Get the database server, namely mysql. Get the database server, namely mysql. #### Returns `string` string
`public `SS_Query`` | # `query( string $sql, integer $errorLevel = E_USER_ERROR )` Execute the given SQL query. This abstract function must be defined by subclasses as part of the actual implementation. It should return a subclass of SS_Query as the result. Execute the given SQL query. This abstract function must be defined by subclasses as part of the actual implementation. It should return a subclass of SS_Query as the result. #### Parameters $sql string $sql The SQL query to execute $errorLevel int $errorLevel The level of error reporting to enable for the query #### Returns ``SS_Query`` SS_Query
`public integer` | # `getGeneratedID( mixed $table )` Get the autogenerated ID from the previous INSERT query. Get the autogenerated ID from the previous INSERT query. #### Returns `integer` int
`public boolean` | # `isActive( )` Check if the connection to the database is active. Check if the connection to the database is active. #### Returns `boolean` boolean
`public boolean` | # `createDatabase( )` Create the database and connect to it. This can be called if the initial database connection is not successful because the database does not exist. Create the database and connect to it. This can be called if the initial database connection is not successful because the database does not exist. It takes no parameters, and should create the database from the information specified in the constructor. #### Returns `boolean` Returns true if successful
`public` | # `dropDatabase( )` Drop the database that this object is currently connected to. Use with caution. Drop the database that this object is currently connected to. Use with caution.
`public` | # `dropDatabaseByName( mixed $dbName )` Drop the database that this object is currently connected to. Use with caution. Drop the database that this object is currently connected to. Use with caution.
`public` | # `currentDatabase( )` Returns the name of the currently selected database Returns the name of the currently selected database
`public` | # `selectDatabase( mixed $dbname )` Switches to the given database. If the database doesn't exist, you should call createDatabase() after calling selectDatabase() Switches to the given database. If the database doesn't exist, you should call createDatabase() after calling selectDatabase()
`public` | # `databaseExists( mixed $name )` Returns true if the named database exists. Returns true if the named database exists.
`public` | # `allDatabaseNames( )` Returns a column Returns a column
`public The` | # `createTable( mixed $table, mixed $fields = null, mixed $indexes = null, mixed $options = null, mixed $advancedOptions = null )` Create a new table. Create a new table. #### Parameters $table $tableName The name of the table $fields $fields A map of field names to field types $indexes $indexes A map of indexes $options $options An map of additional options. The available keys are as follows: - 'MSSQLDatabase'/'MySQLDatabase'/'PostgreSQLDatabase' - database-specific options such as "engine" for MySQL. - 'temporary' - If true, then a temporary table will be created $advancedOptions #### Returns `The` table name generated. This may be different from the table name, for example with temporary tables.
`public` | # `alterTable( mixed $tableName, mixed $newFields = null, mixed $newIndexes = null, mixed $alteredFields = null, mixed $alteredIndexes = null, mixed $alteredOptions = null, mixed $advancedOptions = null )` Alter a table's schema. Alter a table's schema. #### Parameters $tableName $table The name of the table to alter $newFields $newFields New fields, a map of field name => field schema $newIndexes $newIndexes New indexes, a map of index name => index type $alteredFields $alteredFields Updated fields, a map of field name => field schema $alteredIndexes $alteredIndexes Updated indexes, a map of index name => index type $alteredOptions $alteredOptions $advancedOptions
`public` | # `renameTable( string $oldTableName, string $newTableName )` Rename a table. Rename a table. #### Parameters $oldTableName string $oldTableName The old table name. $newTableName string $newTableName The new table name.
`public boolean` | # `checkAndRepairTable( mixed $tableName )` Checks a table's integrity and repairs it if necessary. Checks a table's integrity and repairs it if necessary. #### Returns `boolean` Return true if the table has integrity after the method is complete. #### Var string $tableName The name of the table.
`protected boolean` | # `runTableCheckCommand( string $sql )` Helper function used by checkAndRepairTable. Helper function used by checkAndRepairTable. #### Parameters $sql string $sql Query to run. #### Returns `boolean` Returns if the query returns a successful result.
`public` | # `createField( string $tableName, string $fieldName, string $fieldSpec )` Create a new field on a table. Create a new field on a table. #### Parameters $tableName string $table Name of the table. $fieldName string $field Name of the field to add. $fieldSpec string $spec The field specification, eg 'INTEGER NOT NULL'
`public` | # `alterField( string $tableName, string $fieldName, string $fieldSpec )` Change the database type of the given field. Change the database type of the given field. #### Parameters $tableName string $tableName The name of the tbale the field is in. $fieldName string $fieldName The name of the field to change. $fieldSpec string $fieldSpec The new field specification
`public` | # `renameField( string $tableName, string $oldName, string $newName )` Change the database column name of the given field. Change the database column name of the given field. #### Parameters $tableName string $tableName The name of the tbale the field is in. $oldName string $oldName The name of the field to change. $newName string $newName The new name of the field
`public array` | # `fieldList( string $table )` Get a list of all the fields for the given table. Returns a map of field name => field spec. Get a list of all the fields for the given table. Returns a map of field name => field spec. #### Parameters $table string $table The table name. #### Returns `array` array
`public` | # `createIndex( string $tableName, string $indexName, string $indexSpec )` Create an index on a table. Create an index on a table. #### Parameters $tableName string $tableName The name of the table. $indexName string $indexName The name of the index. $indexSpec string $indexSpec The specification of the index, see `SS_Database::requireIndex()` for more details.
`public string` | # `convertIndexSpec( string|array $indexSpec )` This takes the index spec which has been provided by a class (ie static $indexes = blah blah) and turns it into a proper string. Some indexes may be arrays, such as fulltext and unique indexes, and this allows database-specific arrays to be created. See `SS_Database::requireTable()` for details on the index format. This takes the index spec which has been provided by a class (ie static $indexes = blah blah) and turns it into a proper string. Some indexes may be arrays, such as fulltext and unique indexes, and this allows database-specific arrays to be created. See `SS_Database::requireTable()` for details on the index format. #### Parameters $indexSpec string|array $indexSpec #### Returns `string` MySQL compatible ALTER TABLE syntax #### See http://dev.mysql.com/doc/refman/5.0/en/create-index.html
`protected string` | # `getIndexSqlDefinition( string $indexName, string|array $indexSpec = null )` #### Parameters $indexName string $indexName $indexSpec string|array $indexSpec See `SS_Database::requireTable()` for details #### Returns `string` MySQL compatible ALTER TABLE syntax
`public` | # `getDbSqlDefinition( mixed $tableName, mixed $indexName, mixed $indexSpec )` MySQL does not need any transformations done on the index that's created, so we can just return it as-is MySQL does not need any transformations done on the index that's created, so we can just return it as-is
`public` | # `alterIndex( string $tableName, string $indexName, string $indexSpec )` Alter an index on a table. Alter an index on a table. #### Parameters $tableName string $tableName The name of the table. $indexName string $indexName The name of the index. $indexSpec string $indexSpec The specification of the index, see `SS_Database::requireIndex()` for more details.
`public array` | # `indexList( string $table )` Return the list of indexes in a table. Return the list of indexes in a table. #### Parameters $table string $table The table name. #### Returns `array` array
`public array` | # `tableList( )` Returns a list of all the tables in the database. Returns a list of all the tables in the database. #### Returns `array` array
`public integer` | # `affectedRows( )` Return the number of rows affected by the previous operation. Return the number of rows affected by the previous operation. #### Returns `integer` int
`public` | # `databaseError( string $msg, integer $errorLevel = E_USER_ERROR )` Error handler for database errors. All database errors will call this function to report the error. It isn't a static function; it will be called on the object itself and as such can be overridden in a subclass. Error handler for database errors. All database errors will call this function to report the error. It isn't a static function; it will be called on the object itself and as such can be overridden in a subclass. #### Parameters $msg string $msg The error message. $errorLevel int $errorLevel The level of the error to throw. #### Overrides `SS_Database::databaseError`
`public string` | # `boolean( array $values )` Return a boolean type-formatted string Return a boolean type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `date( array $values )` Return a date type-formatted string For MySQL, we simply return the word 'date', no other parameters are necessary Return a date type-formatted string For MySQL, we simply return the word 'date', no other parameters are necessary #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `decimal( array $values )` Return a decimal type-formatted string Return a decimal type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `enum( array $values )` Return a enum type-formatted string Return a enum type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `set( array $values )` Return a set type-formatted string Return a set type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `float( array $values )` Return a float type-formatted string For MySQL, we simply return the word 'date', no other parameters are necessary Return a float type-formatted string For MySQL, we simply return the word 'date', no other parameters are necessary #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `int( array $values )` Return a int type-formatted string Return a int type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `ss_datetime( array $values )` Return a datetime type-formatted string For MySQL, we simply return the word 'datetime', no other parameters are necessary Return a datetime type-formatted string For MySQL, we simply return the word 'datetime', no other parameters are necessary #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `text( array $values )` Return a text type-formatted string Return a text type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `time( array $values )` Return a time type-formatted string For MySQL, we simply return the word 'time', no other parameters are necessary Return a time type-formatted string For MySQL, we simply return the word 'time', no other parameters are necessary #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public string` | # `varchar( array $values )` Return a varchar type-formatted string Return a varchar type-formatted string #### Parameters $values array $values Contains a tokenised list of info about this data type #### Returns `string` string
`public` | # `year( mixed $values )`
`public string` | # `IdColumn( )` This returns the column which is the primary key for each table In Postgres, it is a SERIAL8, which is the equivalent of an auto_increment This returns the column which is the primary key for each table In Postgres, it is a SERIAL8, which is the equivalent of an auto_increment #### Returns `string` string
`public` | # `allTablesSQL( )` Returns the SQL command to get all the tables in this database Returns the SQL command to get all the tables in this database
`public` | # `hasTable( mixed $table )` Returns true if the given table is exists in the current database NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released. Returns true if the given table is exists in the current database NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released.
`public` | # `enumValuesForField( mixed $tableName, mixed $fieldName )` Returns the values of the given enum field NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released. Returns the values of the given enum field NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released.
`public` | # `searchEngine( string $classesToSearch, mixed $keywords, mixed $start, mixed $pageLength, mixed $sortBy = "Relevance DESC", mixed $extraFilter = "", mixed $booleanSearch = false, mixed $alternativeFileFilter = "", mixed $invertedMatch = false )` The core search engine, used by this class and its subclasses to do fun stuff. Searches both SiteTree and File. The core search engine, used by this class and its subclasses to do fun stuff. Searches both SiteTree and File. #### Parameters $classesToSearch string $keywords Keywords as a string. $keywords $start $pageLength $sortBy $extraFilter $booleanSearch $alternativeFileFilter $invertedMatch
`public` | # `now( )` MySQL uses NOW() to return the current date/time. MySQL uses NOW() to return the current date/time.
`public` | # `random( )`
`public` | # `dbDataType( mixed $type )`
`public` | # `addslashes( mixed $value )`
`public` | # `modifyIndex( mixed $index )`
`public` | # `fullTextSearchSQL( mixed $fields, mixed $keywords, mixed $booleanSearch = false )` Returns a SQL fragment for querying a fulltext search index Returns a SQL fragment for querying a fulltext search index #### Parameters $fields $fields array The list of field names to search on $keywords $keywords string The search query $booleanSearch $booleanSearch A MySQL-specific flag to switch to boolean search
`public` | # `supportsTransactions( )`
`public` | # `supportsExtensions( mixed $extensions = Array('partitions', 'tablespaces', 'clustering') )`
`public` | # `startTransaction( mixed $transaction_mode = false, mixed $session_characteristics = false )`
`public` | # `transactionSavepoint( mixed $savepoint )`
`public` | # `transactionRollback( mixed $savepoint = false )`
`public` | # `endTransaction( )`
`public string` | # `formattedDatetimeClause( string $date, string $format )` Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime in a certain format Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime in a certain format #### Parameters $date 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"' $format 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 #### Returns `string` SQL datetime expression to query for a formatted datetime
`public string` | # `datetimeIntervalClause( string $date, string $interval )` Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime addition Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime addition #### Parameters $date string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' $interval string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR supported qualifiers: - years - months - days - hours - minutes - seconds This includes the singular forms as well #### Returns `string` SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition
`public string` | # `datetimeDifferenceClause( string $date1, string $date2 )` Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime substraction Function to return an SQL datetime expression that can be used with MySQL used for querying a datetime substraction #### Parameters $date1 string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' $date2 string $date2 to be substracted of $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' #### Returns `string` SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction

### Methods inherited from SS_Database

`alterationMessage()`, `beginSchemaUpdate()`, `cancelSchemaUpdate()`, `doesSchemaNeedUpdating()`, `dontRequireField()`, `dontRequireTable()`, `endSchemaUpdate()`, `manipulate()`, `quiet()`, `replace_with_null()`, `requireField()`, `requireIndex()`, `requireTable()`, `sqlQueryToString()`, `transAlterField()`, `transAlterIndex()`, `transAlterTable()`, `transCreateField()`, `transCreateIndex()`, `transCreateTable()`, `transInitTable()`

### Magic methods summary

## Properties summary

### Properties inherited from SS_Database

`$fieldList`, `$globalConn`, `$indexList`, `$schemaUpdateTransaction`, `$supressOutput`, `$tableList`

[Raise a SilverStripe Framework issue/bug](https://github.com/silverstripe/silverstripe-framework/issues/new)
- [Raise a SilverStripe CMS issue/bug](https://github.com/silverstripe/silverstripe-cms/issues/new)
- Please use the Silverstripe Forums to ask development related questions. -