MySqlPlatform.php 23.7 KB
Newer Older
1
<?php
romanb's avatar
romanb committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
/*
 *  $Id$
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
19
 * <http://www.doctrine-project.org>.
romanb's avatar
romanb committed
20
 */
21

22
namespace Doctrine\DBAL\Platforms;
23

24
use Doctrine\DBAL\DBALException,
25
    Doctrine\DBAL\Schema\TableDiff;
26

27 28
/**
 * The MySqlPlatform provides the behavior, features and SQL dialect of the
29 30
 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that
 * uses the InnoDB storage engine.
31 32 33
 *
 * @since 2.0
 * @author Roman Borschel <roman@code-factory.org>
34
 * @author Benjamin Eberlei <kontakt@beberlei.de>
35
 * @todo Rename: MySQLPlatform
36
 */
37
class MySqlPlatform extends AbstractPlatform
38
{
romanb's avatar
romanb committed
39 40 41 42 43 44 45 46 47
    /**
     * Gets the character used for identifier quoting.
     *
     * @return string
     * @override
     */
    public function getIdentifierQuoteCharacter()
    {
        return '`';
48 49 50
    }
    
    /**
romanb's avatar
romanb committed
51
     * Returns the regular expression operator.
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
     *
     * @return string
     * @override
     */
    public function getRegexpExpression()
    {
        return 'RLIKE';
    }

    /**
     * Returns global unique identifier
     *
     * @return string to get global unique identifier
     * @override
     */
    public function getGuidExpression()
    {
        return 'UUID()';
    }

72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
    /**
     * returns the position of the first occurrence of substring $substr in string $str
     *
     * @param string $substr    literal string to find
     * @param string $str       literal string
     * @param int    $pos       position to start at, beginning of string by default
     * @return integer
     */
    public function getLocateExpression($str, $substr, $startPos = false)
    {
        if ($startPos == false) {
            return 'LOCATE(' . $substr . ', ' . $str . ')';
        } else {
            return 'LOCATE(' . $substr . ', ' . $str . ', '.$startPos.')';
        }
    }

89 90 91 92 93 94 95 96 97 98 99 100 101 102
    /**
     * Returns a series of strings concatinated
     *
     * concat() accepts an arbitrary number of parameters. Each parameter
     * must contain an expression or an array with expressions.
     *
     * @param string|array(string) strings that will be concatinated.
     * @override
     */
    public function getConcatExpression()
    {
        $args = func_get_args();
        return 'CONCAT(' . join(', ', (array) $args) . ')';
    }
103

104
    public function getListDatabasesSQL()
105 106 107 108
    {
        return 'SHOW DATABASES';
    }

109
    public function getListTableConstraintsSQL($table)
110
    {
111
        return 'SHOW INDEX FROM ' . $table;
112 113
    }

114
    public function getListTableIndexesSQL($table)
115
    {
116
        return 'SHOW INDEX FROM ' . $table;
117 118
    }

119
    public function getListViewsSQL($database)
120
    {
121
        return "SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '".$database."'";
122 123
    }

124
    public function getListTableForeignKeysSQL($table, $database = null)
125
    {
126 127 128 129 130
        $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
               "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
               "FROM information_schema.key_column_usage k /*!50116 ".
               "INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND ".
               "  c.constraint_name = k.constraint_name AND ".
131
               "  c.table_name = '$table' */ WHERE k.table_name = '$table'";
132 133

        if ( ! is_null($database)) {
134
            $sql .= " AND table_schema = '$database'";
135 136
        }

137
        $sql .= " AND `REFERENCED_COLUMN_NAME` is not NULL";
138 139 140 141

        return $sql;
    }

142
    public function getCreateViewSQL($name, $sql)
143 144 145 146
    {
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
    }

147
    public function getDropViewSQL($name)
148 149 150 151
    {
        return 'DROP VIEW '. $name;
    }

152
    /**
153 154 155
     * Gets the SQL snippet used to declare a VARCHAR column on the MySql platform.
     *
     * @params array $field
156
     */
157
    public function getVarcharTypeDeclarationSQL(array $field)
158 159 160 161 162 163 164 165 166 167
    {
        if ( ! isset($field['length'])) {
            if (array_key_exists('default', $field)) {
                $field['length'] = $this->getVarcharMaxLength();
            } else {
                $field['length'] = false;
            }
        }

        $length = ($field['length'] <= $this->getVarcharMaxLength()) ? $field['length'] : false;
168
        $fixed = (isset($field['fixed'])) ? $field['fixed'] : false;
169 170

        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
171
                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
172
    }
173

174
    /** @override */
175
    public function getClobTypeDeclarationSQL(array $field)
176 177 178 179 180 181 182 183 184 185 186 187 188
    {
        if ( ! empty($field['length'])) {
            $length = $field['length'];
            if ($length <= 255) {
                return 'TINYTEXT';
            } else if ($length <= 65532) {
                return 'TEXT';
            } else if ($length <= 16777215) {
                return 'MEDIUMTEXT';
            }
        }
        return 'LONGTEXT';
    }
189

190 191 192
    /**
     * @override
     */
193
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
194
    {
195
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {
196 197 198 199
            return 'TIMESTAMP';
        } else {
            return 'DATETIME';
        }
200
    }
201 202 203 204
    
    /**
     * @override
     */
205
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
206 207 208
    {
        return 'DATE';
    }
209

210 211 212
    /**
     * @override
     */
213
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration) 
214 215 216 217
    {
        return 'TIME';
    }	

218 219 220
    /**
     * @override
     */
221
    public function getBooleanTypeDeclarationSQL(array $field)
222 223 224 225
    {
        return 'TINYINT(1)';
    }

226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250
    /**
     * Obtain DBMS specific SQL code portion needed to set the COLLATION
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @param string $collation   name of the collation
     * @return string  DBMS specific SQL code portion needed to set the COLLATION
     *                 of a field declaration.
     */
    public function getCollationFieldDeclaration($collation)
    {
        return 'COLLATE ' . $collation;
    }
    
    /**
     * Whether the platform prefers identity columns for ID generation.
     * MySql prefers "autoincrement" identity columns since sequences can only
     * be emulated with a table.
     *
     * @return boolean
     * @override
     */
    public function prefersIdentityColumns()
    {
        return true;
    }
romanb's avatar
romanb committed
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
    
    /**
     * Whether the platform supports identity columns.
     * MySql supports this through AUTO_INCREMENT columns.
     *
     * @return boolean
     * @override
     */
    public function supportsIdentityColumns()
    {
        return true;
    }
    
    /**
     * Whether the platform supports savepoints. MySql does not.
     *
     * @return boolean
     * @override
     */
    public function supportsSavepoints()
    {
        return false;
    }
274

275
    public function getShowDatabasesSQL()
276 277 278 279
    {
        return 'SHOW DATABASES';
    }
    
280
    public function getListTablesSQL()
281
    {
282
        return 'SHOW FULL TABLES WHERE Table_type = "BASE TABLE"';
283
    }
284

285
    public function getListTableColumnsSQL($table)
286
    {
287
        return 'DESCRIBE ' . $table;
288 289
    }

290 291 292 293 294 295 296
    /**
     * create a new database
     *
     * @param string $name name of the database that should be created
     * @return string
     * @override
     */
297
    public function getCreateDatabaseSQL($name)
298
    {
299
        return 'CREATE DATABASE ' . $name;
300 301 302 303 304 305 306 307 308
    }
    
    /**
     * drop an existing database
     *
     * @param string $name name of the database that should be dropped
     * @return string
     * @override
     */
309
    public function getDropDatabaseSQL($name)
310
    {
311
        return 'DROP DATABASE ' . $name;
312 313 314 315 316
    }
    
    /**
     * create a new table
     *
317 318
     * @param string $tableName   Name of the database that should be created
     * @param array $columns  Associative array that contains the definition of each field of the new table
319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348
     *                       The indexes of the array entries are the names of the fields of the table an
     *                       the array entry values are associative arrays like those that are meant to be
     *                       passed with the field definitions to get[Type]Declaration() functions.
     *                          array(
     *                              'id' => array(
     *                                  'type' => 'integer',
     *                                  'unsigned' => 1
     *                                  'notnull' => 1
     *                                  'default' => 0
     *                              ),
     *                              'name' => array(
     *                                  'type' => 'text',
     *                                  'length' => 12
     *                              ),
     *                              'password' => array(
     *                                  'type' => 'text',
     *                                  'length' => 12
     *                              )
     *                          );
     * @param array $options  An associative array of table options:
     *                          array(
     *                              'comment' => 'Foo',
     *                              'charset' => 'utf8',
     *                              'collate' => 'utf8_unicode_ci',
     *                              'type'    => 'innodb',
     *                          );
     *
     * @return void
     * @override
     */
349
    protected function _getCreateTableSQL($tableName, array $columns, array $options = array())
350
    {
351
        $queryFields = $this->getColumnDeclarationListSQL($columns);
352

353
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
354
            foreach ($options['uniqueConstraints'] as $index => $definition) {
355
                $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition);
356
            }
357 358 359 360 361
        }

        // add all indexes
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
            foreach($options['indexes'] as $index => $definition) {
362
                $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
363 364 365 366 367
            }
        }

        // attach all primary keys
        if (isset($options['primary']) && ! empty($options['primary'])) {
368
            $keyColumns = array_unique(array_values($options['primary']));
369 370 371 372 373 374 375
            $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';
        }

        $query = 'CREATE ';
        if (!empty($options['temporary'])) {
            $query .= 'TEMPORARY ';
        }
376
        $query.= 'TABLE ' . $tableName . ' (' . $queryFields . ')';
377 378 379 380 381 382 383 384 385 386 387 388 389 390

        $optionStrings = array();

        if (isset($options['comment'])) {
            $optionStrings['comment'] = 'COMMENT = ' . $this->quote($options['comment'], 'text');
        }
        if (isset($options['charset'])) {
            $optionStrings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
            if (isset($options['collate'])) {
                $optionStrings['charset'] .= ' COLLATE ' . $options['collate'];
            }
        }

        // get the type of the table
391 392 393 394 395
        if (isset($options['engine'])) {
            $optionStrings[] = 'ENGINE = ' . $engine;
        } else {
            // default to innodb
            $optionStrings[] = 'ENGINE = InnoDB';
396
        }
397
        
398 399 400 401 402 403
        if ( ! empty($optionStrings)) {
            $query.= ' '.implode(' ', $optionStrings);
        }
        $sql[] = $query;

        if (isset($options['foreignKeys'])) {
404
            foreach ((array) $options['foreignKeys'] as $definition) {
405
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
406 407 408 409 410 411 412
            }
        }
        
        return $sql;
    }
    
    /**
413
     * Gets the SQL to alter an existing table.
414
     *
415 416
     * @param string $name The name of the table that is intended to be changed.
     * @param array $changes Associative array that contains the details of each type
417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500
     *                             of change that is intended to be performed. The types of
     *                             changes that are currently supported are defined as follows:
     *
     *                             name
     *
     *                                New name for the table.
     *
     *                            add
     *
     *                                Associative array with the names of fields to be added as
     *                                 indexes of the array. The value of each entry of the array
     *                                 should be set to another associative array with the properties
     *                                 of the fields to be added. The properties of the fields should
     *                                 be the same as defined by the Metabase parser.
     *
     *
     *                            remove
     *
     *                                Associative array with the names of fields to be removed as indexes
     *                                 of the array. Currently the values assigned to each entry are ignored.
     *                                 An empty array should be used for future compatibility.
     *
     *                            rename
     *
     *                                Associative array with the names of fields to be renamed as indexes
     *                                 of the array. The value of each entry of the array should be set to
     *                                 another associative array with the entry named name with the new
     *                                 field name and the entry named Declaration that is expected to contain
     *                                 the portion of the field declaration already in DBMS specific SQL code
     *                                 as it is used in the CREATE TABLE statement.
     *
     *                            change
     *
     *                                Associative array with the names of the fields to be changed as indexes
     *                                 of the array. Keep in mind that if it is intended to change either the
     *                                 name of a field and any other properties, the change array entries
     *                                 should have the new names of the fields as array indexes.
     *
     *                                The value of each entry of the array should be set to another associative
     *                                 array with the properties of the fields to that are meant to be changed as
     *                                 array entries. These entries should be assigned to the new values of the
     *                                 respective properties. The properties of the fields should be the same
     *                                 as defined by the Metabase parser.
     *
     *                            Example
     *                                array(
     *                                    'name' => 'userlist',
     *                                    'add' => array(
     *                                        'quota' => array(
     *                                            'type' => 'integer',
     *                                            'unsigned' => 1
     *                                        )
     *                                    ),
     *                                    'remove' => array(
     *                                        'file_limit' => array(),
     *                                        'time_limit' => array()
     *                                    ),
     *                                    'change' => array(
     *                                        'name' => array(
     *                                            'length' => '20',
     *                                            'definition' => array(
     *                                                'type' => 'text',
     *                                                'length' => 20,
     *                                            ),
     *                                        )
     *                                    ),
     *                                    'rename' => array(
     *                                        'sex' => array(
     *                                            'name' => 'gender',
     *                                            'definition' => array(
     *                                                'type' => 'text',
     *                                                'length' => 1,
     *                                                'default' => 'M',
     *                                            ),
     *                                        )
     *                                    )
     *                                )
     *
     * @param boolean $check     indicates whether the function should just check if the DBMS driver
     *                           can perform the requested table alterations if the value is true or
     *                           actually perform them otherwise.
     * @return boolean
     * @override
     */
501
    public function getAlterTableSQL(TableDiff $diff)
502
    {
503 504 505
        $queryParts = array();
        if ($diff->newName !== false) {
            $queryParts[] =  'RENAME TO ' . $diff->newName;
506 507
        }

508
        foreach ($diff->addedColumns AS $fieldName => $column) {
509
            $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getName(), $column->toArray());
510 511
        }

512 513
        foreach ($diff->removedColumns AS $column) {
            $queryParts[] =  'DROP ' . $column->getName();
514 515
        }

516 517 518 519
        foreach ($diff->changedColumns AS $columnDiff) {
            /* @var $columnDiff Doctrine\DBAL\Schema\ColumnDiff */
            $column = $columnDiff->column;
            $queryParts[] =  'CHANGE ' . ($columnDiff->oldColumnName) . ' '
520
                    . $this->getColumnDeclarationSQL($column->getName(), $column->toArray());
521 522
        }

523 524
        foreach ($diff->renamedColumns AS $oldColumnName => $column) {
            $queryParts[] =  'CHANGE ' . $oldColumnName . ' '
525
                    . $this->getColumnDeclarationSQL($column->getName(), $column->toArray());
526 527
        }

528
        $sql = array();
529 530
        if (count($queryParts) > 0) {
            $sql[] = 'ALTER TABLE ' . $diff->name . ' ' . implode(", ", $queryParts);
531
        }
532
        $sql = array_merge($sql, $this->_getAlterTableIndexForeignKeySQL($diff));
533
        return $sql;
534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561
    }
    
    /**
     * Obtain DBMS specific SQL code portion needed to declare an integer type
     * field to be used in statements like CREATE TABLE.
     *
     * @param string  $name   name the field to be declared.
     * @param string  $field  associative array with the name of the properties
     *                        of the field being declared as array indexes.
     *                        Currently, the types of supported field
     *                        properties are as follows:
     *
     *                       unsigned
     *                        Boolean flag that indicates whether the field
     *                        should be declared as unsigned integer if
     *                        possible.
     *
     *                       default
     *                        Integer value to be used as default for this
     *                        field.
     *
     *                       notnull
     *                        Boolean flag that indicates whether this field is
     *                        constrained to not be set to null.
     * @return string  DBMS specific SQL code portion that should be used to
     *                 declare the specified field.
     * @override
     */
562
    public function getIntegerTypeDeclarationSQL(array $field)
563
    {
564
        return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
565 566 567
    }

    /** @override */
568
    public function getBigIntTypeDeclarationSQL(array $field)
569
    {
570
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
571 572 573
    }

    /** @override */
574
    public function getSmallIntTypeDeclarationSQL(array $field)
575
    {
576
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
577 578 579
    }

    /** @override */
580
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
581
    {
582
        $autoinc = '';
583
        if ( ! empty($columnDef['autoincrement'])) {
584 585
            $autoinc = ' AUTO_INCREMENT';
        }
586
        $unsigned = (isset($columnDef['unsigned']) && $columnDef['unsigned']) ? ' UNSIGNED' : '';
587

588
        return $unsigned . $autoinc;
589 590 591 592 593 594
    }
    
    /**
     * Return the FOREIGN KEY query section dealing with non-standard options
     * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
     *
595
     * @param ForeignKeyConstraint $foreignKey
596 597 598
     * @return string
     * @override
     */
599
    public function getAdvancedForeignKeyOptionsSQL(\Doctrine\DBAL\Schema\ForeignKeyConstraint $foreignKey)
600 601
    {
        $query = '';
602 603
        if ($foreignKey->hasOption('match')) {
            $query .= ' MATCH ' . $foreignKey->getOption('match');
604
        }
605
        $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
606 607 608 609
        return $query;
    }
    
    /**
610
     * Gets the SQL to drop an index of a table.
611
     *
612 613
     * @param Index $index           name of the index to be dropped
     * @param string|Table $table          name of table that should be used in method
614 615
     * @override
     */
616
    public function getDropIndexSQL($index, $table=null)
617
    {
618 619 620
        if($index instanceof \Doctrine\DBAL\Schema\Index) {
            $index = $index->getName();
        } else if(!is_string($index)) {
621
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
622 623 624 625 626
        }
        
        if($table instanceof \Doctrine\DBAL\Schema\Table) {
            $table = $table->getName();
        } else if(!is_string($table)) {
627
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
628 629 630
        }

        return 'DROP INDEX ' . $index . ' ON ' . $table;
631 632 633
    }
    
    /**
634
     * Gets the SQL to drop a table.
635
     *
636
     * @param string $table The name of table to drop.
637 638
     * @override
     */
639
    public function getDropTableSQL($table)
640
    {
641 642 643
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
            $table = $table->getName();
        } else if(!is_string($table)) {
644
            throw new \InvalidArgumentException('MysqlPlatform::getDropTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
645 646
        }

647
        return 'DROP TABLE ' . $table;
648
    }
649

650
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
651
    {
652
        return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
romanb's avatar
romanb committed
653
    }
654 655

    /**
656
     * Get the platform name for this instance.
657 658 659 660 661 662 663
     *
     * @return string
     */
    public function getName()
    {
        return 'mysql';
    }
664 665 666 667 668

    public function createsExplicitIndexForForeignKeys()
    {
        return true;
    }
669
}