Version: current

       1 <?php
       2 /**
       3  * MySQL connector class.
       4  *
       5  * Supported indexes for {@link requireTable()}:
       6  *
       7  * @package sapphire
       8  * @subpackage model
       9  */
      10 class MySQLDatabase extends SS_Database {
      11     /**
      12      * Connection to the DBMS.
      13      * @var resource
      14      */
      15     private $dbConn;
      16
      17     /**
      18      * True if we are connected to a database.
      19      * @var boolean
      20      */
      21     private $active;
      22
      23     /**
      24      * The name of the database.
      25      * @var string
      26      */
      27     private $database;
      28
      29     private static $connection_charset = null;
      30
      31     private $supportsTransactions=false;
      32
      33     /**
      34      * Sets the character set for the MySQL database connection.
      35      *
      36      * The character set connection should be set to 'utf8' for SilverStripe version 2.4.0 and
      37      * later.
      38      *
      39      * However, sites created before version 2.4.0 should leave this unset or data that isn't 7-bit
      40      * safe will be corrupted.  As such, the installer comes with this set in mysite/_config.php by
      41      * default in versions 2.4.0 and later.
      42      */
      43     public static function set_connection_charset($charset = 'utf8') {
      44         self::$connection_charset = $charset;
      45     }
      46
      47     /**
      48      * Connect to a MySQL database.
      49      * @param array $parameters An map of parameters, which should include:
      50      *  - server: The server, eg, localhost
      51      *  - username: The username to log on with
      52      *  - password: The password to log on with
      53      *  - database: The database to connect to
      54      *  - timezone: (optional) the timezone offset, eg: +12:00 for NZ time
      55      */
      56     public function __construct($parameters) {
      57         $this->dbConn = mysql_connect($parameters['server'], $parameters['username'], $parameters['password']);
      58
      59         if(self::$connection_charset) {
      60             $this->query("SET CHARACTER SET '" . self::$connection_charset . "'");
      61             $this->query("SET NAMES '" . self::$connection_charset . "'");
      62         }
      63
      64         $this->active = mysql_select_db($parameters['database'], $this->dbConn);
      65         $this->database = $parameters['database'];
      66         if(isset($parameters['timezone'])) {    //set timezone to custom parameter
      67             mysql_query("SET SESSION time_zone='" . $parameters['timezone'] . "'");
      68         }
      69         if(!$this->dbConn) {
      70             $this->databaseError("Couldn't connect to MySQL database");
      71         }
      72         $this->query('set names utf8'); // !!! already in params
      73         $this->query("SET sql_mode = 'ANSI'");
      74     }
      75
      76     /**
      77      * Not implemented, needed for PDO
      78      */
      79     public function getConnect($parameters) {
      80         return null;
      81     }
      82
      83     /**
      84      * Returns true if this database supports collations
      85      * @return boolean
      86      */
      87     public function supportsCollations() {
      88         return $this->getVersion() >= 4.1;
      89     }
      90
      91     /**
      92      * The version of MySQL.
      93      * @var float
      94      */
      95     private $mysqlVersion;
      96
      97     /**
      98      * Get the version of MySQL.
      99      * @return float
     100      */
     101     public function getVersion() {
     102         if(!$this->mysqlVersion) {
     103             $this->mysqlVersion = (float)substr(trim(ereg_replace("([A-Za-z-])", "", $this->query("SELECT VERSION()")->value())), 0, 3);
     104         }
     105         return $this->mysqlVersion;
     106     }
     107
     108     /**
     109      * Get the database server, namely mysql.
     110      * @return string
     111      */
     112     public function getDatabaseServer() {
     113         return "mysql";
     114     }
     115
     116     public function query($sql, $errorLevel = E_USER_ERROR) {
     117         if(isset($_REQUEST['previewwrite']) && in_array(strtolower(substr($sql,0,strpos($sql,' '))), array('insert','update','delete','replace'))) {
     118             Debug::message("Will execute: $sql");
     119             return;
     120         }
     121
     122         if(isset($_REQUEST['showqueries'])) {
     123             $starttime = microtime(true);
     124         }
     125
     126         $handle = mysql_query($sql, $this->dbConn);
     127
     128         if(isset($_REQUEST['showqueries'])) {
     129             $endtime = round(microtime(true) - $starttime,4);
     130             if (!isset($_REQUEST['ajax'])) Debug::message("\n$sql\n{$endtime}ms\n", false);
     131             else echo "\n$sql\n{$endtime}ms\n";
     132         }
     133
     134         if(!$handle && $errorLevel) $this->databaseError("Couldn't run query: $sql | " . mysql_error($this->dbConn), $errorLevel);
     135         return new MySQLQuery($this, $handle);
     136     }
     137
     138     public function getGeneratedID($table) {
     139         return mysql_insert_id($this->dbConn);
     140     }
     141
     142     public function isActive() {
     143         return $this->active ? true : false;
     144     }
     145
     146     public function createDatabase() {
     147         $this->query("CREATE DATABASE `$this->database`");
     148         $this->query("USE `$this->database`");
     149
     150         $this->tableList = $this->fieldList = $this->indexList = null;
     151
     152         if(mysql_select_db($this->database, $this->dbConn)) {
     153             $this->active = true;
     154             return true;
     155         }
     156     }
     157
     158     /**
     159      * Drop the database that this object is currently connected to.
     160      * Use with caution.
     161      */
     162     public function dropDatabase() {
     163         $this->dropDatabaseByName($this->database);
     164     }
     165
     166     /**
     167      * Drop the database that this object is currently connected to.
     168      * Use with caution.
     169      */
     170     public function dropDatabaseByName($dbName) {
     171         $this->query("DROP DATABASE \"$dbName\"");
     172     }
     173
     174     /**
     175      * Returns the name of the currently selected database
     176      */
     177     public function currentDatabase() {
     178         return $this->database;
     179     }
     180
     181     /**
     182      * Switches to the given database.
     183      * If the database doesn't exist, you should call createDatabase() after calling selectDatabase()
     184      */
     185     public function selectDatabase($dbname) {
     186         $this->database = $dbname;
     187         if($this->databaseExists($this->database)) {
     188             if(mysql_select_db($this->database, $this->dbConn)) $this->active = true;
     189         }
     190         $this->tableList = $this->fieldList = $this->indexList = null;
     191     }
     192
     193     /**
     194      * Returns true if the named database exists.
     195      */
     196     public function databaseExists($name) {
     197         $SQL_name = Convert::raw2sql($name);
     198         return $this->query("SHOW DATABASES LIKE '$SQL_name'")->value() ? true : false;
     199     }
     200
     201     /**
     202      * Returns a column
     203      */
     204     public function allDatabaseNames() {
     205         return $this->query("SHOW DATABASES")->column();
     206     }
     207
     208     /**
     209      * Create a new table.
     210      * @param $tableName The name of the table
     211      * @param $fields A map of field names to field types
     212      * @param $indexes A map of indexes
     213      * @param $options An map of additional options.  The available keys are as follows:
     214      *   - 'MSSQLDatabase'/'MySQLDatabase'/'PostgreSQLDatabase' - database-specific options such as "engine" for MySQL.
     215      *   - 'temporary' - If true, then a temporary table will be created
     216      * @return The table name generated.  This may be different from the table name, for example with temporary tables.
     217      */
     218     public function createTable($table, $fields = null, $indexes = null, $options = null, $advancedOptions = null) {
     219         $fieldSchemas = $indexSchemas = "";
     220
     221         $addOptions = empty($options[get_class($this)]) ? "ENGINE=MyISAM" : $options[get_class($this)];
     222
     223         if(!isset($fields['ID'])) $fields['ID'] = "int(11) not null auto_increment";
     224         if($fields) foreach($fields as $k => $v) $fieldSchemas .= "\"$k\" $v,\n";
     225         if($indexes) foreach($indexes as $k => $v) $indexSchemas .= $this->getIndexSqlDefinition($k, $v) . ",\n";
     226
     227         // Switch to "CREATE TEMPORARY TABLE" for temporary tables
     228         $temporary = empty($options['temporary']) ? "" : "TEMPORARY";
     229
     230         $this->query("CREATE $temporary TABLE \"$table\" (
     231                 $fieldSchemas
     232                 $indexSchemas
     233                 primary key (ID)
     234             ) {$addOptions}");
     235
     236         return $table;
     237     }
     238
     239     /**
     240      * Alter a table's schema.
     241      * @param $table The name of the table to alter
     242      * @param $newFields New fields, a map of field name => field schema
     243      * @param $newIndexes New indexes, a map of index name => index type
     244      * @param $alteredFields Updated fields, a map of field name => field schema
     245      * @param $alteredIndexes Updated indexes, a map of index name => index type
     246      * @param $alteredOptions
     247      */
     248     public function alterTable($tableName, $newFields = null, $newIndexes = null, $alteredFields = null, $alteredIndexes = null, $alteredOptions = null, $advancedOptions = null) {
     249         $fieldSchemas = $indexSchemas = "";
     250         $alterList = array();
     251
     252         if($newFields) foreach($newFields as $k => $v) $alterList[] .= "ADD \"$k\" $v";
     253         if($newIndexes) foreach($newIndexes as $k => $v) $alterList[] .= "ADD " . $this->getIndexSqlDefinition($k, $v);
     254         if($alteredFields) foreach($alteredFields as $k => $v) $alterList[] .= "CHANGE \"$k\" \"$k\" $v";
     255         if($alteredIndexes) foreach($alteredIndexes as $k => $v) {
     256             $alterList[] .= "DROP INDEX \"$k\"";
     257             $alterList[] .= "ADD ". $this->getIndexSqlDefinition($k, $v);
     258         }
     259
     260         $alterations = implode(",\n", $alterList);
     261         $this->query("ALTER TABLE \"$tableName\" $alterations");
     262
     263         if($alteredOptions && isset($alteredOptions[get_class($this)])) {
     264             $this->query(sprintf("ALTER TABLE \"%s\" %s", $tableName, $alteredOptions[get_class($this)]));
     265             DB::alteration_message(
     266                 sprintf("Table %s options changed: %s", $tableName, $alteredOptions[get_class($this)]),
     267                 "changed"
     268             );
     269         }
     270     }
     271
     272     public function renameTable($oldTableName, $newTableName) {
     273         $this->query("ALTER TABLE \"$oldTableName\" RENAME \"$newTableName\"");
     274     }
     275
     276
     277
     278     /**
     279      * Checks a table's integrity and repairs it if necessary.
     280      * @var string $tableName The name of the table.
     281      * @return boolean Return true if the table has integrity after the method is complete.
     282      */
     283     public function checkAndRepairTable($tableName) {
     284         if(!$this->runTableCheckCommand("CHECK TABLE \"$tableName\"")) {
     285             if($this->runTableCheckCommand("CHECK TABLE \"".strtolower($tableName)."\"")){
     286                 DB::alteration_message("Table $tableName: renamed from lowercase","repaired");
     287                 return $this->renameTable(strtolower($tableName),$tableName);
     288             }
     289
     290             DB::alteration_message("Table $tableName: repaired","repaired");
     291             return $this->runTableCheckCommand("REPAIR TABLE \"$tableName\" USE_FRM");
     292         } else {
     293             return true;
     294         }
     295     }
     296
     297     /**
     298      * Helper function used by checkAndRepairTable.
     299      * @param string $sql Query to run.
     300      * @return boolean Returns if the query returns a successful result.
     301      */
     302     protected function runTableCheckCommand($sql) {
     303         $testResults = $this->query($sql);
     304         foreach($testResults as $testRecord) {
     305             if(strtolower($testRecord['Msg_text']) != 'ok') {
     306                 return false;
     307             }
     308         }
     309         return true;
     310     }
     311
     312     public function createField($tableName, $fieldName, $fieldSpec) {
     313         $this->query("ALTER TABLE \"$tableName\" ADD \"$fieldName\" $fieldSpec");
     314     }
     315
     316     /**
     317      * Change the database type of the given field.
     318      * @param string $tableName The name of the tbale the field is in.
     319      * @param string $fieldName The name of the field to change.
     320      * @param string $fieldSpec The new field specification
     321      */
     322     public function alterField($tableName, $fieldName, $fieldSpec) {
     323         $this->query("ALTER TABLE \"$tableName\" CHANGE \"$fieldName\" \"$fieldName\" $fieldSpec");
     324     }
     325
     326     /**
     327      * Change the database column name of the given field.
     328      *
     329      * @param string $tableName The name of the tbale the field is in.
     330      * @param string $oldName The name of the field to change.
     331      * @param string $newName The new name of the field
     332      */
     333     public function renameField($tableName, $oldName, $newName) {
     334         $fieldList = $this->fieldList($tableName);
     335         if(array_key_exists($oldName, $fieldList)) {
     336             $this->query("ALTER TABLE \"$tableName\" CHANGE \"$oldName\" \"$newName\" " . $fieldList[$oldName]);
     337         }
     338     }
     339
     340     private static $_cache_collation_info = array();
     341
     342     public function fieldList($table) {
     343         $fields = DB::query("SHOW FULL FIELDS IN \"$table\"");
     344         foreach($fields as $field) {
     345             $fieldSpec = $field['Type'];
     346             if(!$field['Null'] || $field['Null'] == 'NO') {
     347                 $fieldSpec .= ' not null';
     348             }
     349
     350             if($field['Collation'] && $field['Collation'] != 'NULL') {
     351                 // Cache collation info to cut down on database traffic
     352                 if(!isset(self::$_cache_collation_info[$field['Collation']])) {
     353                     self::$_cache_collation_info[$field['Collation']] = DB::query("SHOW COLLATION LIKE '$field[Collation]'")->record();
     354                 }
     355                 $collInfo = self::$_cache_collation_info[$field['Collation']];
     356                 $fieldSpec .= " character set $collInfo[Charset] collate $field[Collation]";
     357             }
     358
     359             if($field['Default'] || $field['Default'] === "0") {
     360                 if(is_numeric($field['Default']))
     361                     $fieldSpec .= " default " . addslashes($field['Default']);
     362                 else
     363                     $fieldSpec .= " default '" . addslashes($field['Default']) . "'";
     364             }
     365             if($field['Extra']) $fieldSpec .= " $field[Extra]";
     366
     367             $fieldList[$field['Field']] = $fieldSpec;
     368         }
     369         return $fieldList;
     370     }
     371
     372     /**
     373      * Create an index on a table.
     374      *
     375      * @param string $tableName The name of the table.
     376      * @param string $indexName The name of the index.
     377      * @param string $indexSpec The specification of the index, see {@link SS_Database::requireIndex()} for more details.
     378      */
     379     public function createIndex($tableName, $indexName, $indexSpec) {
     380         $this->query("ALTER TABLE \"$tableName\" ADD " . $this->getIndexSqlDefinition($indexName, $indexSpec));
     381     }
     382
     383     /**
     384      * This takes the index spec which has been provided by a class (ie static $indexes = blah blah)
     385      * and turns it into a proper string.
     386      * Some indexes may be arrays, such as fulltext and unique indexes, and this allows database-specific
     387      * arrays to be created. See {@link requireTable()} for details on the index format.
     388      *
     389      * @see http://dev.mysql.com/doc/refman/5.0/en/create-index.html
     390      *
     391      * @param string|array $indexSpec
     392      * @return string MySQL compatible ALTER TABLE syntax
     393      */
     394     public function convertIndexSpec($indexSpec){
     395         if(is_array($indexSpec)){
     396             //Here we create a db-specific version of whatever index we need to create.
     397             switch($indexSpec['type']){
     398                 case 'fulltext':
     399                     $indexSpec='fulltext (' . str_replace(' ', '', $indexSpec['value']) . ')';
     400                     break;
     401                 case 'unique':
     402                     $indexSpec='unique (' . $indexSpec['value'] . ')';
     403                     break;
     404                 case 'btree':
     405                     $indexSpec='using btree (' . $indexSpec['value'] . ')';
     406                     break;
     407                 case 'hash':
     408                     $indexSpec='using hash (' . $indexSpec['value'] . ')';
     409                     break;
     410             }
     411         }
     412
     413         return $indexSpec;
     414     }
     415
     416     /**
     417      * @param string $indexName
     418      * @param string|array $indexSpec See {@link requireTable()} for details
     419      * @return string MySQL compatible ALTER TABLE syntax
     420      */
     421     protected function getIndexSqlDefinition($indexName, $indexSpec=null) {
     422
     423         $indexSpec=$this->convertIndexSpec($indexSpec);
     424
     425         $indexSpec = trim($indexSpec);
     426         if($indexSpec[0] != '(') list($indexType, $indexFields) = explode(' ',$indexSpec,2);
     427         else $indexFields = $indexSpec;
     428
     429         if(!isset($indexType))
     430             $indexType = "index";
     431
     432         if($indexType=='using')
     433             return "index \"$indexName\" using $indexFields";
     434         else {
     435             return "$indexType \"$indexName\" $indexFields";
     436         }
     437
     438     }
     439
     440     /**
     441      * MySQL does not need any transformations done on the index that's created, so we can just return it as-is
     442      */
     443     function getDbSqlDefinition($tableName, $indexName, $indexSpec){
     444         return $indexName;
     445     }
     446
     447     /**
     448      * Alter an index on a table.
     449      * @param string $tableName The name of the table.
     450      * @param string $indexName The name of the index.
     451      * @param string $indexSpec The specification of the index, see {@link SS_Database::requireIndex()} for more details.
     452      */
     453     public function alterIndex($tableName, $indexName, $indexSpec) {
     454
     455         $indexSpec=$this->convertIndexSpec($indexSpec);
     456
     457         $indexSpec = trim($indexSpec);
     458         if($indexSpec[0] != '(') {
     459             list($indexType, $indexFields) = explode(' ',$indexSpec,2);
     460         } else {
     461             $indexFields = $indexSpec;
     462         }
     463
     464         if(!$indexType) {
     465             $indexType = "index";
     466         }
     467
     468         $this->query("ALTER TABLE \"$tableName\" DROP INDEX \"$indexName\"");
     469         $this->query("ALTER TABLE \"$tableName\" ADD $indexType \"$indexName\" $indexFields");
     470     }
     471
     472     /**
     473      * Return the list of indexes in a table.
     474      * @param string $table The table name.
     475      * @return array
     476      */
     477     public function indexList($table) {
     478         $indexes = DB::query("SHOW INDEXES IN \"$table\"");
     479         $groupedIndexes = array();
     480         $indexList = array();
     481
     482         foreach($indexes as $index) {
     483             $groupedIndexes[$index['Key_name']]['fields'][$index['Seq_in_index']] = $index['Column_name'];
     484
     485             if($index['Index_type'] == 'FULLTEXT') {
     486                 $groupedIndexes[$index['Key_name']]['type'] = 'fulltext ';
     487             } else if(!$index['Non_unique']) {
     488                 $groupedIndexes[$index['Key_name']]['type'] = 'unique ';
     489             } else if($index['Index_type'] =='HASH') {
     490                 $groupedIndexes[$index['Key_name']]['type'] = 'hash ';
     491             } else if($index['Index_type'] =='RTREE') {
     492                 $groupedIndexes[$index['Key_name']]['type'] = 'rtree ';
     493             } else {
     494                 $groupedIndexes[$index['Key_name']]['type'] = '';
     495             }
     496         }
     497
     498         if($groupedIndexes) {
     499             foreach($groupedIndexes as $index => $details) {
     500                 ksort($details['fields']);
     501                 $indexList[$index] = $details['type'] . '(' . implode(',',$details['fields']) . ')';
     502             }
     503         }
     504
     505         return $indexList;
     506     }
     507
     508     /**
     509      * Returns a list of all the tables in the database.
     510      * @return array
     511      */
     512     public function tableList() {
     513         $tables = array();
     514         foreach($this->query("SHOW TABLES") as $record) {
     515             $table = reset($record);
     516             $tables[strtolower($table)] = $table;
     517         }
     518         return $tables;
     519     }
     520
     521     /**
     522      * Return the number of rows affected by the previous operation.
     523      * @return int
     524      */
     525     public function affectedRows() {
     526         return mysql_affected_rows($this->dbConn);
     527     }
     528
     529     function databaseError($msg, $errorLevel = E_USER_ERROR) {
     530         // try to extract and format query
     531         if(preg_match('/Couldn\'t run query: ([^\|]*)\|\s*(.*)/', $msg, $matches)) {
     532             $formatter = new SQLFormatter();
     533             $msg = "Couldn't run query: \n" . $formatter->formatPlain($matches[1]) . "\n\n" . $matches[2];
     534         }
     535
     536         user_error($msg, $errorLevel);
     537     }
     538
     539     /**
     540      * Return a boolean type-formatted string
     541      *
     542      * @param array $values Contains a tokenised list of info about this data type
     543      * @return string
     544      */
     545     public function boolean($values){
     546         //For reference, this is what typically gets passed to this function:
     547         //$parts=Array('datatype'=>'tinyint', 'precision'=>1, 'sign'=>'unsigned', 'null'=>'not null', 'default'=>$this->default);
     548         //DB::requireField($this->tableName, $this->name, "tinyint(1) unsigned not null default '{$this->defaultVal}'");
     549
     550         return 'tinyint(1) unsigned not null default ' . (int)$values['default'];
     551     }
     552
     553     /**
     554      * Return a date type-formatted string
     555      * For MySQL, we simply return the word 'date', no other parameters are necessary
     556      *
     557      * @param array $values Contains a tokenised list of info about this data type
     558      * @return string
     559      */
     560     public function date($values){
     561         //For reference, this is what typically gets passed to this function:
     562         //$parts=Array('datatype'=>'date');
     563         //DB::requireField($this->tableName, $this->name, "date");
     564
     565         return 'date';
     566     }
     567
     568     /**
     569      * Return a decimal type-formatted string
     570      *
     571      * @param array $values Contains a tokenised list of info about this data type
     572      * @return string
     573      */
     574     public function decimal($values){
     575         //For reference, this is what typically gets passed to this function:
     576         //$parts=Array('datatype'=>'decimal', 'precision'=>"$this->wholeSize,$this->decimalSize");
     577         //DB::requireField($this->tableName, $this->name, "decimal($this->wholeSize,$this->decimalSize)");
     578
     579         // Avoid empty strings being put in the db
     580         if($values['precision'] == '') {
     581             $precision = 1;
     582         } else {
     583             $precision = $values['precision'];
     584         }
     585
     586         $defaultValue = '';
     587         if(isset($values['default']) && is_numeric($values['default'])) {
     588             list($dummy, $decimal) = explode(',', $precision);
     589             if (!$decimal) $decimal = 0;
     590             $defaultValue = ' default ' . number_format($values['default'], $decimal, '.', '');
     591         }
     592
     593         return 'decimal(' . $precision . ') not null' . $defaultValue;
     594     }
     595
     596     /**
     597      * Return a enum type-formatted string
     598      *
     599      * @param array $values Contains a tokenised list of info about this data type
     600      * @return string
     601      */
     602     public function enum($values){
     603         //For reference, this is what typically gets passed to this function:
     604         //$parts=Array('datatype'=>'enum', 'enums'=>$this->enum, 'character set'=>'utf8', 'collate'=> 'utf8_unicode_ci', 'default'=>$this->default);
     605         //DB::requireField($this->tableName, $this->name, "enum('" . implode("','", $this->enum) . "') character set utf8 collate utf8_unicode_ci default '{$this->default}'");
     606
     607         return 'enum(\'' . implode('\',\'', $values['enums']) . '\') character set utf8 collate utf8_unicode_ci default \'' . $values['default'] . '\'';
     608     }
     609
     610     /**
     611      * Return a set type-formatted string
     612      *
     613      * @param array $values Contains a tokenised list of info about this data type
     614      * @return string
     615      */
     616     public function set($values){
     617         //For reference, this is what typically gets passed to this function:
     618         //$parts=Array('datatype'=>'enum', 'enums'=>$this->enum, 'character set'=>'utf8', 'collate'=> 'utf8_unicode_ci', 'default'=>$this->default);
     619         //DB::requireField($this->tableName, $this->name, "enum('" . implode("','", $this->enum) . "') character set utf8 collate utf8_unicode_ci default '{$this->default}'");
     620         $default = empty($values['default']) ? '' : " default '$values[default]'";
     621         return 'set(\'' . implode('\',\'', $values['enums']) . '\') character set utf8 collate utf8_unicode_ci' . $default;
     622     }
     623
     624     /**
     625      * Return a float type-formatted string
     626      * For MySQL, we simply return the word 'date', no other parameters are necessary
     627      *
     628      * @param array $values Contains a tokenised list of info about this data type
     629      * @return string
     630      */
     631     public function float($values){
     632         //For reference, this is what typically gets passed to this function:
     633         //$parts=Array('datatype'=>'float');
     634         //DB::requireField($this->tableName, $this->name, "float");
     635
     636         return 'float not null default ' . $values['default'];
     637     }
     638
     639     /**
     640      * Return a int type-formatted string
     641      *
     642      * @param array $values Contains a tokenised list of info about this data type
     643      * @return string
     644      */
     645     public function int($values){
     646         //For reference, this is what typically gets passed to this function:
     647         //$parts=Array('datatype'=>'int', 'precision'=>11, 'null'=>'not null', 'default'=>(int)$this->default);
     648         //DB::requireField($this->tableName, $this->name, "int(11) not null default '{$this->defaultVal}'");
     649
     650         return 'int(11) not null default ' . (int)$values['default'];
     651     }
     652
     653     /**
     654      * Return a datetime type-formatted string
     655      * For MySQL, we simply return the word 'datetime', no other parameters are necessary
     656      *
     657      * @param array $values Contains a tokenised list of info about this data type
     658      * @return string
     659      */
     660     public function ss_datetime($values){
     661         //For reference, this is what typically gets passed to this function:
     662         //$parts=Array('datatype'=>'datetime');
     663         //DB::requireField($this->tableName, $this->name, $values);
     664
     665         return 'datetime';
     666     }
     667
     668     /**
     669      * Return a text type-formatted string
     670      *
     671      * @param array $values Contains a tokenised list of info about this data type
     672      * @return string
     673      */
     674     public function text($values){
     675         //For reference, this is what typically gets passed to this function:
     676         //$parts=Array('datatype'=>'mediumtext', 'character set'=>'utf8', 'collate'=>'utf8_unicode_ci');
     677         //DB::requireField($this->tableName, $this->name, "mediumtext character set utf8 collate utf8_unicode_ci");
     678
     679         return 'mediumtext character set utf8 collate utf8_unicode_ci';
     680     }
     681
     682     /**
     683      * Return a time type-formatted string
     684      * For MySQL, we simply return the word 'time', no other parameters are necessary
     685      *
     686      * @param array $values Contains a tokenised list of info about this data type
     687      * @return string
     688      */
     689     public function time($values){
     690         //For reference, this is what typically gets passed to this function:
     691         //$parts=Array('datatype'=>'time');
     692         //DB::requireField($this->tableName, $this->name, "time");
     693
     694         return 'time';
     695     }
     696
     697     /**
     698      * Return a varchar type-formatted string
     699      *
     700      * @param array $values Contains a tokenised list of info about this data type
     701      * @return string
     702      */
     703     public function varchar($values){
     704         //For reference, this is what typically gets passed to this function:
     705         //$parts=Array('datatype'=>'varchar', 'precision'=>$this->size, 'character set'=>'utf8', 'collate'=>'utf8_unicode_ci');
     706         //DB::requireField($this->tableName, $this->name, "varchar($this->size) character set utf8 collate utf8_unicode_ci");
     707
     708         return 'varchar(' . $values['precision'] . ') character set utf8 collate utf8_unicode_ci';
     709     }
     710
     711     /*
     712      * Return the MySQL-proprietary 'Year' datatype
     713      */
     714     public function year($values){
     715         return 'year(4)';
     716     }
     717     /**
     718      * This returns the column which is the primary key for each table
     719      * In Postgres, it is a SERIAL8, which is the equivalent of an auto_increment
     720      *
     721      * @return string
     722      */
     723     function IdColumn(){
     724         return 'int(11) not null auto_increment';
     725     }
     726
     727     /**
     728      * Returns the SQL command to get all the tables in this database
     729      */
     730     function allTablesSQL(){
     731         return "SHOW TABLES;";
     732     }
     733
     734     /**
     735      * Returns true if the given table is exists in the current database
     736      * NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released.
     737      */
     738     public function hasTable($table) {
     739         $SQL_table = Convert::raw2sql($table);
     740         return (bool)($this->query("SHOW TABLES LIKE '$SQL_table'")->value());
     741     }
     742
     743     /**
     744      * Returns the values of the given enum field
     745      * NOTE: Experimental; introduced for db-abstraction and may changed before 2.4 is released.
     746      */
     747     public function enumValuesForField($tableName, $fieldName) {
     748         // Get the enum of all page types from the SiteTree table
     749         $classnameinfo = DB::query("DESCRIBE \"$tableName\" \"$fieldName\"")->first();
     750         preg_match_all("/'[^,]+'/", $classnameinfo["Type"], $matches);
     751
     752         $classes = array();
     753         foreach($matches[0] as $value) {
     754             $classes[] = trim($value, "'");
     755         }
     756         return $classes;
     757     }
     758
     759     /**
     760      * The core search engine, used by this class and its subclasses to do fun stuff.
     761      * Searches both SiteTree and File.
     762      *
     763      * @param string $keywords Keywords as a string.
     764      */
     765     public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false) {
     766         $fileFilter = '';
     767         $keywords = Convert::raw2sql($keywords);
     768         $htmlEntityKeywords = htmlentities($keywords, version_compare(phpversion(), '5.4', '<') ? ENT_COMPAT : (ENT_COMPAT | ENT_HTML401), "UTF-8");
     769
     770         $extraFilters = array('SiteTree' => '', 'File' => '');
     771
     772
     773         $boolean = ($booleanSearch) ? "IN BOOLEAN MODE" : '';
     774
     775         if($extraFilter) {
     776             $extraFilters['SiteTree'] = " AND $extraFilter";
     777
     778             if($alternativeFileFilter) $extraFilters['File'] = " AND $alternativeFileFilter";
     779             else $extraFilters['File'] = $extraFilters['SiteTree'];
     780         }
     781
     782         // Always ensure that only pages with ShowInSearch = 1 can be searched
     783         $extraFilters['SiteTree'] .= " AND ShowInSearch <> 0";
     784
     785         $limit = $start . ", " . (int) $pageLength;
     786
     787         $notMatch = $invertedMatch ? "NOT " : "";
     788         if($keywords) {
     789             $match['SiteTree'] = "
     790                 MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('$keywords' $boolean)
     791                 + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('$htmlEntityKeywords' $boolean)
     792             ";
     793             $match['File'] = "MATCH (Filename, Title, Content) AGAINST ('$keywords' $boolean) AND ClassName = 'File'";
     794
     795             // чтобы релевантность правильно работала в список полей надо поставлять ее точно как в where
     796             $relevance['SiteTree'] = $match['SiteTree'];
     797             $relevance['File'] = $match['File'];
     798         } else {
     799             $relevance['SiteTree'] = $relevance['File'] = 1;
     800             $match['SiteTree'] = $match['File'] = "1 = 1";
     801         }
     802         // Generate initial queries and base table names
     803         $baseClasses = array('SiteTree' => '', 'File' => '');
     804         foreach($classesToSearch as $class) {
     805             $queries[$class] = singleton($class)->extendedSQL($notMatch . $match[$class] . $extraFilters[$class], "");
     806             $baseClasses[$class] = reset($queries[$class]->from);
     807         }
     808
     809         // Make column selection lists
     810         $select = array(
     811             'SiteTree' => array("ClassName","$baseClasses[SiteTree].ID","ParentID","Title","MenuTitle","URLSegment","Content","LastEdited","Created","_utf8'' AS Filename", "_utf8'' AS Name", "$relevance[SiteTree] AS Relevance", "CanViewType"),
     812             'File' => array("ClassName","$baseClasses[File].ID","_utf8'' AS ParentID","Title","_utf8'' AS MenuTitle","_utf8'' AS URLSegment","Content","LastEdited","Created","Filename","Name","$relevance[File] AS Relevance","NULL AS CanViewType"),
     813         );
     814         // Process queries
     815         foreach($classesToSearch as $class) {
     816             // There's no need to do all that joining
     817             $queries[$class]->from = array(str_replace('`','',$baseClasses[$class]) => $baseClasses[$class]);
     818             $queries[$class]->select = $select[$class];
     819             $queries[$class]->orderby = null;
     820         }
     821
     822         // Combine queries
     823         $querySQLs = array();
     824         $totalCount = 0;
     825         foreach($queries as $query) {
     826             $querySQLs[] = $query->sql();
     827             $totalCount += $query->unlimitedRowCount();
     828         }
     829         $fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY $sortBy LIMIT $limit";
     830
     831         // Get records
     832         $records = DB::query($fullQuery);
     833
     834         foreach($records as $record)
     835             $objects[] = new $record['ClassName']($record);
     836
     837         if(isset($objects)) $doSet = new DataObjectSet($objects);
     838         else $doSet = new DataObjectSet();
     839
     840         $doSet->setPageLimits($start, $pageLength, $totalCount);
     841         return $doSet;
     842     }
     843
     844     /**
     845      * MySQL uses NOW() to return the current date/time.
     846      */
     847     function now(){
     848         return 'NOW()';
     849     }
     850
     851     /*
     852      * Returns the database-specific version of the random() function
     853      */
     854     function random(){
     855         return 'RAND()';
     856     }
     857
     858     /*
     859      * This is a lookup table for data types.
     860      * For instance, Postgres uses 'INT', while MySQL uses 'UNSIGNED'
     861      * So this is a DB-specific list of equivilents.
     862      */
     863     function dbDataType($type){
     864         $values=Array(
     865             'unsigned integer'=>'UNSIGNED'
     866         );
     867
     868         if(isset($values[$type]))
     869             return $values[$type];
     870         else return '';
     871     }
     872
     873     /*
     874      * This will return text which has been escaped in a database-friendly manner
     875      * Using PHP's addslashes method won't work in MSSQL
     876      */
     877     function addslashes($value){
     878         return mysql_real_escape_string($value, $this->dbConn);
     879     }
     880
     881     /*
     882      * This changes the index name depending on database requirements.
     883      * MySQL doesn't need any changes.
     884      */
     885     function modifyIndex($index){
     886         return $index;
     887     }
     888
     889     /**
     890      * Returns a SQL fragment for querying a fulltext search index
     891      * @param $fields array The list of field names to search on
     892      * @param $keywords string The search query
     893      * @param $booleanSearch A MySQL-specific flag to switch to boolean search
     894      */
     895     function fullTextSearchSQL($fields, $keywords, $booleanSearch = false) {
     896         $boolean = $booleanSearch ? "IN BOOLEAN MODE" : "";
     897         $fieldNames = '"' . implode('", "', $fields) . '"';
     898
     899         $SQL_keywords = Convert::raw2sql($keywords);
     900         $SQL_htmlEntityKeywords = Convert::raw2sql(htmlentities($keywords));
     901
     902         return "(MATCH ($fieldNames) AGAINST ('$SQL_keywords' $boolean) + MATCH ($fieldNames) AGAINST ('$SQL_htmlEntityKeywords' $boolean))";
     903     }
     904
     905     /*
     906      * Does this database support transactions?
     907      */
     908     public function supportsTransactions(){
     909         return $this->supportsTransactions;
     910     }
     911
     912     /*
     913      * This is a quick lookup to discover if the database supports particular extensions
     914      * Currently, MySQL supports no extensions
     915      */
     916     public function supportsExtensions($extensions=Array('partitions', 'tablespaces', 'clustering')){
     917         if(isset($extensions['partitions']))
     918             return false;
     919         elseif(isset($extensions['tablespaces']))
     920             return false;
     921         elseif(isset($extensions['clustering']))
     922             return false;
     923         else
     924             return false;
     925     }
     926
     927     /*
     928      * Start a prepared transaction
     929      * See http://developer.postgresql.org/pgdocs/postgres/sql-set-transaction.html for details on transaction isolation options
     930      */
     931     public function startTransaction($transaction_mode=false, $session_characteristics=false){
     932         //Transactions not set up for MySQL yet
     933     }
     934
     935     /*
     936      * Create a savepoint that you can jump back to if you encounter problems
     937      */
     938     public function transactionSavepoint($savepoint){
     939         //Transactions not set up for MySQL yet
     940     }
     941
     942     /*
     943      * Rollback or revert to a savepoint if your queries encounter problems
     944      * If you encounter a problem at any point during a transaction, you may
     945      * need to rollback that particular query, or return to a savepoint
     946      */
     947     public function transactionRollback($savepoint=false){
     948         //Transactions not set up for MySQL yet
     949     }
     950
     951     /*
     952      * Commit everything inside this transaction so far
     953      */
     954     public function endTransaction(){
     955         //Transactions not set up for MySQL yet
     956     }
     957
     958     /**
     959      * Function to return an SQL datetime expression that can be used with MySQL
     960      * used for querying a datetime in a certain format
     961      * @param 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"'
     962      * @param string $format to be used, supported specifiers:
     963      * %Y = Year (four digits)
     964      * %m = Month (01..12)
     965      * %d = Day (01..31)
     966      * %H = Hour (00..23)
     967      * %i = Minutes (00..59)
     968      * %s = Seconds (00..59)
     969      * %U = unix timestamp, can only be used on it's own
     970      * @return string SQL datetime expression to query for a formatted datetime
     971      */
     972     function formattedDatetimeClause($date, $format) {
     973
     974         preg_match_all('/%(.)/', $format, $matches);
     975         foreach($matches[1] as $match) if(array_search($match, array('Y','m','d','H','i','s','U')) === false) user_error('formattedDatetimeClause(): unsupported format character %' . $match, E_USER_WARNING);
     976
     977         if(preg_match('/^now$/i', $date)) {
     978             $date = "NOW()";
     979         } else if(preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) {
     980             $date = "'$date'";
     981         }
     982
     983         if($format == '%U') return "UNIX_TIMESTAMP($date)";
     984
     985         return "DATE_FORMAT($date, '$format')";
     986
     987     }
     988
     989     /**
     990      * Function to return an SQL datetime expression that can be used with MySQL
     991      * used for querying a datetime addition
     992      * @param string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
     993      * @param string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR
     994      * supported qualifiers:
     995      * - years
     996      * - months
     997      * - days
     998      * - hours
     999      * - minutes
    1000      * - seconds
    1001      * This includes the singular forms as well
    1002      * @return string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition
    1003      */
    1004     function datetimeIntervalClause($date, $interval) {
    1005
    1006         $interval = preg_replace('/(year|month|day|hour|minute|second)s/i', '$1', $interval);
    1007
    1008         if(preg_match('/^now$/i', $date)) {
    1009             $date = "NOW()";
    1010         } else if(preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) {
    1011             $date = "'$date'";
    1012         }
    1013
    1014         return "$date + INTERVAL $interval";
    1015     }
    1016
    1017     /**
    1018      * Function to return an SQL datetime expression that can be used with MySQL
    1019      * used for querying a datetime substraction
    1020      * @param string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"'
    1021      * @param 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"'
    1022      * @return string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction
    1023      */
    1024     function datetimeDifferenceClause($date1, $date2) {
    1025
    1026         if(preg_match('/^now$/i', $date1)) {
    1027             $date1 = "NOW()";
    1028         } else if(preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date1)) {
    1029             $date1 = "'$date1'";
    1030         }
    1031
    1032         if(preg_match('/^now$/i', $date2)) {
    1033             $date2 = "NOW()";
    1034         } else if(preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date2)) {
    1035             $date2 = "'$date2'";
    1036         }
    1037
    1038         return "UNIX_TIMESTAMP($date1) - UNIX_TIMESTAMP($date2)";
    1039     }
    1040 }
    1041
    1042 /**
    1043  * A result-set from a MySQL database.
    1044  * @package sapphire
    1045  * @subpackage model
    1046  */
    1047 class MySQLQuery extends SS_Query {
    1048     /**
    1049      * The MySQLDatabase object that created this result set.
    1050      * @var MySQLDatabase
    1051      */
    1052     private $database;
    1053
    1054     /**
    1055      * The internal MySQL handle that points to the result set.
    1056      * @var resource
    1057      */
    1058     private $handle;
    1059
    1060     /**
    1061      * Hook the result-set given into a Query class, suitable for use by sapphire.
    1062      * @param database The database object that created this query.
    1063      * @param handle the internal mysql handle that is points to the resultset.
    1064      */
    1065     public function __construct(MySQLDatabase $database, $handle) {
    1066         $this->database = $database;
    1067         $this->handle = $handle;
    1068     }
    1069
    1070     public function __destroy() {
    1071         mysql_free_result($this->handle);
    1072     }
    1073
    1074     public function seek($row) {
    1075         return mysql_data_seek($this->handle, $row);
    1076     }
    1077
    1078     public function numRecords() {
    1079         return mysql_num_rows($this->handle);
    1080     }
    1081
    1082     public function nextRecord() {
    1083         // Coalesce rather than replace common fields.
    1084         if($data = mysql_fetch_row($this->handle)) {
    1085             foreach($data as $columnIdx => $value) {
    1086                 $columnName = mysql_field_name($this->handle, $columnIdx);
    1087                 // $value || !$ouput[$columnName] means that the *last* occurring value is shown
    1088                 // !$ouput[$columnName] means that the *first* occurring value is shown
    1089                 if(isset($value) || !isset($output[$columnName])) {
    1090                     $output[$columnName] = $value;
    1091                 }
    1092             }
    1093             return $output;
    1094         } else {
    1095             return false;
    1096         }
    1097     }
    1098
    1099
    1100 }
    1101
    1102 ?>
    1103

[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. -