AbstractPlatform.php 62.3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
<?php
/*
 * 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
17
 * <http://www.doctrine-project.org>.
18 19
 */

20 21
namespace Doctrine\DBAL\Platforms;

22
use Doctrine\DBAL\DBALException,
23
    Doctrine\DBAL\Connection,
24 25 26
    Doctrine\DBAL\Types,
    Doctrine\DBAL\Schema\Table,
    Doctrine\DBAL\Schema\Index,
27 28
    Doctrine\DBAL\Schema\ForeignKeyConstraint,
    Doctrine\DBAL\Schema\TableDiff;
29 30 31 32 33 34

/**
 * Base class for all DatabasePlatforms. The DatabasePlatforms are the central
 * point of abstraction of platform-specific behaviors, features and SQL dialects.
 * They are a passive source of information.
 *
35 36 37 38 39 40 41 42
 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL
 * @link    www.doctrine-project.org
 * @since   2.0
 * @version $Revision: 3938 $
 * @author  Guilherme Blanco <guilhermeblanco@hotmail.com>
 * @author  Jonathan Wage <jonwage@gmail.com>
 * @author  Roman Borschel <roman@code-factory.org>
 * @author  Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
43
 * @author  Benjamin Eberlei <kontakt@beberlei.de>
44
 * @todo Remove any unnecessary methods.
45
 */
46
abstract class AbstractPlatform
47
{
48 49 50 51 52 53 54 55 56 57
    /**
     * @var int
     */
    const CREATE_INDEXES = 1;

    /**
     * @var int
     */
    const CREATE_FOREIGNKEYS = 2;

58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
    /**
     * @var int
     */
    const TRIM_UNSPECIFIED = 0;

    /**
     * @var int
     */
    const TRIM_LEADING = 1;

    /**
     * @var int
     */
    const TRIM_TRAILING = 2;

    /**
     * @var int
     */
    const TRIM_BOTH = 3;

78 79 80 81 82
    /**
     * @var array
     */
    protected $doctrineTypeMapping = null;

83 84 85 86
    /**
     * Constructor.
     */
    public function __construct() {}
87

88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139
    /**
     * Gets the SQL snippet that declares a boolean column.
     *
     * @param array $columnDef
     * @return string
     */
    abstract public function getBooleanTypeDeclarationSQL(array $columnDef);

    /**
     * Gets the SQL snippet that declares a 4 byte integer column.
     *
     * @param array $columnDef
     * @return string
     */
    abstract public function getIntegerTypeDeclarationSQL(array $columnDef);

    /**
     * Gets the SQL snippet that declares an 8 byte integer column.
     *
     * @param array $columnDef
     * @return string
     */
    abstract public function getBigIntTypeDeclarationSQL(array $columnDef);

    /**
     * Gets the SQL snippet that declares a 2 byte integer column.
     *
     * @param array $columnDef
     * @return string
     */
    abstract public function getSmallIntTypeDeclarationSQL(array $columnDef);

    /**
     * Gets the SQL snippet that declares common properties of an integer column.
     *
     * @param array $columnDef
     * @return string
     */
    abstract protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef);

    /**
     * Lazy load Doctrine Type Mappings
     *
     * @return void
     */
    abstract protected function initializeDoctrineTypeMappings();

    /**
     * Gets the SQL snippet used to declare a VARCHAR column type.
     *
     * @param array $field
     */
140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158
    public function getVarcharTypeDeclarationSQL(array $field)
    {
        if ( !isset($field['length'])) {
            $field['length'] = $this->getVarcharDefaultLength();
        }

        $fixed = (isset($field['fixed'])) ? $field['fixed'] : false;

        if ($field['length'] > $this->getVarcharMaxLength()) {
            return $this->getClobTypeDeclarationSQL($field);
        } else {
            return $this->getVarcharTypeDeclarationSQLSnippet($field['length'], $fixed);
        }
    }

    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
    {
        throw DBALException::notSupported('VARCHARs not supported by Platform.');
    }
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173

    /**
     * Gets the SQL snippet used to declare a CLOB column type.
     *
     * @param array $field
     */
    abstract public function getClobTypeDeclarationSQL(array $field);

    /**
     * Gets the name of the platform.
     *
     * @return string
     */
    abstract public function getName();

174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213
    /**
     * Register a doctrine type to be used in conjunction with a column type of this platform.
     *
     * @param string $dbType
     * @param string $doctrineType
     */
    public function registerDoctrineTypeMapping($dbType, $doctrineType)
    {
        if ($this->doctrineTypeMapping === null) {
            $this->initializeDoctrineTypeMappings();
        }

        if (!Types\Type::hasType($doctrineType)) {
            throw DBALException::typeNotFound($doctrineType);
        }

        $dbType = strtolower($dbType);
        $this->doctrineTypeMapping[$dbType] = $doctrineType;
    }

    /**
     * Get the Doctrine type that is mapped for the given database column type.
     * 
     * @param  string $dbType
     * @return string
     */
    public function getDoctrineTypeMapping($dbType)
    {
        if ($this->doctrineTypeMapping === null) {
            $this->initializeDoctrineTypeMappings();
        }
        
        $dbType = strtolower($dbType);
        if (isset($this->doctrineTypeMapping[$dbType])) {
            return $this->doctrineTypeMapping[$dbType];
        } else {
            throw new \Doctrine\DBAL\DBALException("Unknown database type ".$dbType." requested, " . get_class($this) . " may not support it.");
        }
    }

214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
    /**
     * Check if a database type is currently supported by this platform.
     *
     * @param string $dbType
     * @return bool
     */
    public function hasDoctrineTypeMappingFor($dbType)
    {
        if ($this->doctrineTypeMapping === null) {
            $this->initializeDoctrineTypeMappings();
        }

        $dbType = strtolower($dbType);
        return isset($this->doctrineTypeMapping[$dbType]);
    }

230 231 232 233 234 235 236 237 238
    /**
     * Gets the character used for identifier quoting.
     *
     * @return string
     */
    public function getIdentifierQuoteCharacter()
    {
        return '"';
    }
239

240 241 242 243 244 245 246 247 248
    /**
     * Gets the string portion that starts an SQL comment.
     *
     * @return string
     */
    public function getSqlCommentStartString()
    {
        return "--";
    }
249

250
    /**
251
     * Gets the string portion that ends an SQL comment.
252 253 254 255 256 257 258
     *
     * @return string
     */
    public function getSqlCommentEndString()
    {
        return "\n";
    }
259

260 261 262 263 264 265
    /**
     * Gets the maximum length of a varchar field.
     *
     * @return integer
     */
    public function getVarcharMaxLength()
266 267 268 269 270 271 272 273 274 275
    {
        return 4000;
    }

    /**
     * Gets the default length of a varchar field.
     *
     * @return integer
     */
    public function getVarcharDefaultLength()
276 277 278
    {
        return 255;
    }
279

280 281 282 283 284 285 286 287 288
    /**
     * Gets all SQL wildcard characters of the platform.
     *
     * @return array
     */
    public function getWildcards()
    {
        return array('%', '_');
    }
289

290 291 292 293 294 295 296
    /**
     * Returns the regular expression operator.
     *
     * @return string
     */
    public function getRegexpExpression()
    {
297
        throw DBALException::notSupported(__METHOD__);
298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 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 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409
    }

    /**
     * Returns the average value of a column
     *
     * @param string $column    the column to use
     * @return string           generated sql including an AVG aggregate function
     */
    public function getAvgExpression($column)
    {
        return 'AVG(' .  $column . ')';
    }

    /**
     * Returns the number of rows (without a NULL value) of a column
     *
     * If a '*' is used instead of a column the number of selected rows
     * is returned.
     *
     * @param string|integer $column    the column to use
     * @return string                   generated sql including a COUNT aggregate function
     */
    public function getCountExpression($column)
    {
        return 'COUNT(' . $column . ')';
    }

    /**
     * Returns the highest value of a column
     *
     * @param string $column    the column to use
     * @return string           generated sql including a MAX aggregate function
     */
    public function getMaxExpression($column)
    {
        return 'MAX(' . $column . ')';
    }

    /**
     * Returns the lowest value of a column
     *
     * @param string $column the column to use
     * @return string
     */
    public function getMinExpression($column)
    {
        return 'MIN(' . $column . ')';
    }

    /**
     * Returns the total sum of a column
     *
     * @param string $column the column to use
     * @return string
     */
    public function getSumExpression($column)
    {
        return 'SUM(' . $column . ')';
    }

    // scalar functions

    /**
     * Returns the md5 sum of a field.
     *
     * Note: Not SQL92, but common functionality
     *
     * @return string
     */
    public function getMd5Expression($column)
    {
        return 'MD5(' . $column . ')';
    }

    /**
     * Returns the length of a text field.
     *
     * @param string $expression1
     * @param string $expression2
     * @return string
     */
    public function getLengthExpression($column)
    {
        return 'LENGTH(' . $column . ')';
    }

    /**
     * Rounds a numeric field to the number of decimals specified.
     *
     * @param string $expression1
     * @param string $expression2
     * @return string
     */
    public function getRoundExpression($column, $decimals = 0)
    {
        return 'ROUND(' . $column . ', ' . $decimals . ')';
    }

    /**
     * Returns the remainder of the division operation
     * $expression1 / $expression2.
     *
     * @param string $expression1
     * @param string $expression2
     * @return string
     */
    public function getModExpression($expression1, $expression2)
    {
        return 'MOD(' . $expression1 . ', ' . $expression2 . ')';
    }

    /**
410
     * Trim a string, leading/trailing/both and with a given char which defaults to space.
411
     *
412 413 414
     * @param string $str
     * @param int $pos
     * @param string $char has to be quoted already
415 416
     * @return string
     */
417
    public function getTrimExpression($str, $pos = self::TRIM_UNSPECIFIED, $char = false)
418
    {
419 420 421 422 423 424 425 426 427 428 429 430
        $posStr = '';
        $trimChar = ($char != false) ? $char . ' FROM ' : '';
        
        if ($pos == self::TRIM_LEADING) {
            $posStr = 'LEADING '.$trimChar;
        } else if($pos == self::TRIM_TRAILING) {
            $posStr = 'TRAILING '.$trimChar;
        } else if($pos == self::TRIM_BOTH) {
            $posStr = 'BOTH '.$trimChar;
        }

        return 'TRIM(' . $posStr . $str . ')';
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
    }

    /**
     * rtrim
     * returns the string $str with proceeding space characters removed
     *
     * @param string $str       literal string or column name
     * @return string
     */
    public function getRtrimExpression($str)
    {
        return 'RTRIM(' . $str . ')';
    }

    /**
     * ltrim
     * returns the string $str with leading space characters removed
     *
     * @param string $str       literal string or column name
     * @return string
     */
    public function getLtrimExpression($str)
    {
        return 'LTRIM(' . $str . ')';
    }

    /**
     * upper
     * Returns the string $str with all characters changed to
     * uppercase according to the current character set mapping.
     *
     * @param string $str       literal string or column name
     * @return string
     */
    public function getUpperExpression($str)
    {
        return 'UPPER(' . $str . ')';
    }

    /**
     * lower
     * Returns the string $str with all characters changed to
     * lowercase according to the current character set mapping.
     *
     * @param string $str       literal string or column name
     * @return string
     */
    public function getLowerExpression($str)
    {
        return 'LOWER(' . $str . ')';
    }

    /**
     * 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
488
     * @param int    $pos       position to start at, beginning of string by default
489 490
     * @return integer
     */
491
    public function getLocateExpression($str, $substr, $startPos = false)
492
    {
493
        throw DBALException::notSupported(__METHOD__);
494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512
    }

    /**
     * Returns the current system date.
     *
     * @return string
     */
    public function getNowExpression()
    {
        return 'NOW()';
    }

    /**
     * return string to call a function to get a substring inside an SQL statement
     *
     * Note: Not SQL92, but common functionality.
     *
     * SQLite only supports the 2 parameter variant of this function
     *
513 514 515 516
     * @param  string $value         an sql string literal or column name/alias
     * @param  integer $from     where to start the substring portion
     * @param  integer $len       the substring portion length
     * @return string
517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537
     */
    public function getSubstringExpression($value, $from, $len = null)
    {
        if ($len === null)
            return 'SUBSTRING(' . $value . ' FROM ' . $from . ')';
        else {
            return 'SUBSTRING(' . $value . ' FROM ' . $from . ' FOR ' . $len . ')';
        }
    }

    /**
     * Returns a series of strings concatinated
     *
     * concat() accepts an arbitrary number of parameters. Each parameter
     * must contain an expression
     *
     * @param string $arg1, $arg2 ... $argN     strings that will be concatinated.
     * @return string
     */
    public function getConcatExpression()
    {
538
        return join(' || ' , func_get_args());
539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555
    }

    /**
     * Returns the SQL for a logical not.
     *
     * Example:
     * <code>
     * $q = new Doctrine_Query();
     * $e = $q->expr;
     * $q->select('*')->from('table')
     *   ->where($e->eq('id', $e->not('null'));
     * </code>
     *
     * @return string a logical expression
     */
    public function getNotExpression($expression)
    {
556
        return 'NOT(' . $expression . ')';
557 558 559 560
    }

    /**
     * Returns the SQL to check if a value is one in a set of
561
     * given values.
562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579
     *
     * in() accepts an arbitrary number of parameters. The first parameter
     * must always specify the value that should be matched against. Successive
     * must contain a logical expression or an array with logical expressions.
     * These expressions will be matched against the first parameter.
     *
     * @param string $column        the value that should be matched against
     * @param string|array(string)  values that will be matched against $column
     * @return string logical expression
     */
    public function getInExpression($column, $values)
    {
        if ( ! is_array($values)) {
            $values = array($values);
        }
        $values = $this->getIdentifiers($values);

        if (count($values) == 0) {
580
            throw \InvalidArgumentException('Values must not be empty.');
581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645
        }
        return $column . ' IN (' . implode(', ', $values) . ')';
    }

    /**
     * Returns SQL that checks if a expression is null.
     *
     * @param string $expression the expression that should be compared to null
     * @return string logical expression
     */
    public function getIsNullExpression($expression)
    {
        return $expression . ' IS NULL';
    }

    /**
     * Returns SQL that checks if a expression is not null.
     *
     * @param string $expression the expression that should be compared to null
     * @return string logical expression
     */
    public function getIsNotNullExpression($expression)
    {
        return $expression . ' IS NOT NULL';
    }

    /**
     * Returns SQL that checks if an expression evaluates to a value between
     * two values.
     *
     * The parameter $expression is checked if it is between $value1 and $value2.
     *
     * Note: There is a slight difference in the way BETWEEN works on some databases.
     * http://www.w3schools.com/sql/sql_between.asp. If you want complete database
     * independence you should avoid using between().
     *
     * @param string $expression the value to compare to
     * @param string $value1 the lower value to compare with
     * @param string $value2 the higher value to compare with
     * @return string logical expression
     */
    public function getBetweenExpression($expression, $value1, $value2)
    {
        return $expression . ' BETWEEN ' .$value1 . ' AND ' . $value2;
    }

    public function getAcosExpression($value)
    {
        return 'ACOS(' . $value . ')';
    }

    public function getSinExpression($value)
    {
        return 'SIN(' . $value . ')';
    }

    public function getPiExpression()
    {
        return 'PI()';
    }

    public function getCosExpression($value)
    {
        return 'COS(' . $value . ')';
    }
646

647
    public function getForUpdateSQL()
648 649 650
    {
        return 'FOR UPDATE';
    }
651

652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688
    /**
     * Honors that some SQL vendors such as MsSql use table hints for locking instead of the ANSI SQL FOR UPDATE specification.
     *
     * @param  string $fromClause
     * @param  int $lockMode
     * @return string
     */
    public function appendLockHint($fromClause, $lockMode)
    {
        return $fromClause;
    }

    /**
     * Get the sql snippet to append to any SELECT statement which locks rows in shared read lock.
     *
     * This defaults to the ASNI SQL "FOR UPDATE", which is an exclusive lock (Write). Some database
     * vendors allow to lighten this constraint up to be a real read lock.
     *
     * @return string
     */
    public function getReadLockSQL()
    {
        return $this->getForUpdateSQL();
    }

    /**
     * Get the SQL snippet to append to any SELECT statement which obtains an exclusive lock on the rows.
     *
     * The semantics of this lock mode should equal the SELECT .. FOR UPDATE of the ASNI SQL standard.
     *
     * @return string
     */
    public function getWriteLockSQL()
    {
        return $this->getForUpdateSQL();
    }

689
    public function getDropDatabaseSQL($database)
690 691 692
    {
        return 'DROP DATABASE ' . $database;
    }
693

694 695 696 697 698 699
    /**
     * Drop a Table
     * 
     * @param  Table|string $table
     * @return string
     */
700
    public function getDropTableSQL($table)
701
    {
702
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
703
            $table = $table->getQuotedName($this);
704 705
        }

706 707
        return 'DROP TABLE ' . $table;
    }
708

709 710 711 712 713 714 715
    /**
     * Drop index from a table
     *
     * @param Index|string $name
     * @param string|Table $table
     * @return string
     */
716
    public function getDropIndexSQL($index, $table=null)
717
    {
718
        if($index instanceof \Doctrine\DBAL\Schema\Index) {
719
            $index = $index->getQuotedName($this);
720
        } else if(!is_string($index)) {
721
            throw new \InvalidArgumentException('AbstractPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
722 723 724
        }

        return 'DROP INDEX ' . $index;
725
    }
726

727 728 729 730 731 732 733
    /**
     * Get drop constraint sql
     * 
     * @param  \Doctrine\DBAL\Schema\Constraint $constraint
     * @param  string|Table $table
     * @return string
     */
734
    public function getDropConstraintSQL($constraint, $table)
735
    {
736
        if ($constraint instanceof \Doctrine\DBAL\Schema\Constraint) {
737
            $constraint = $constraint->getQuotedName($this);
738 739
        }

740
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
741
            $table = $table->getQuotedName($this);
742 743 744
        }

        return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $constraint;
745
    }
746

747 748 749 750 751
    /**
     * @param  ForeignKeyConstraint|string $foreignKey
     * @param  Table|string $table
     * @return string
     */
752
    public function getDropForeignKeySQL($foreignKey, $table)
753
    {
754
        if ($foreignKey instanceof \Doctrine\DBAL\Schema\ForeignKeyConstraint) {
755
            $foreignKey = $foreignKey->getQuotedName($this);
756 757 758
        }

        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
759
            $table = $table->getQuotedName($this);
760 761 762
        }

        return 'ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $foreignKey;
763
    }
764

765
    /**
766
     * Gets the SQL statement(s) to create a table with the specified name, columns and constraints
767
     * on this platform.
768
     *
769
     * @param string $table The name of the table.
770
     * @param int $createFlags
771
     * @return array The sequence of SQL statements.
772
     */
773
    public function getCreateTableSQL(Table $table, $createFlags=self::CREATE_INDEXES)
774
    {
775
        if ( ! is_int($createFlags)) {
776
            throw new \InvalidArgumentException("Second argument of AbstractPlatform::getCreateTableSQL() has to be integer.");
777 778
        }

779 780 781 782
        if (count($table->getColumns()) == 0) {
            throw DBALException::noColumnsSpecifiedForTable($table->getName());
        }

783
        $tableName = $table->getQuotedName($this);
784 785 786 787 788
        $options = $table->getOptions();
        $options['uniqueConstraints'] = array();
        $options['indexes'] = array();
        $options['primary'] = array();

789 790 791 792 793 794 795 796
        if (($createFlags&self::CREATE_INDEXES) > 0) {
            foreach ($table->getIndexes() AS $index) {
                /* @var $index Index */
                if ($index->isPrimary()) {
                    $options['primary'] = $index->getColumns();
                } else {
                    $options['indexes'][$index->getName()] = $index;
                }
797 798
            }
        }
799

800
        $columns = array();
801
        foreach ($table->getColumns() AS $column) {
802 803
            /* @var \Doctrine\DBAL\Schema\Column $column */
            $columnData = array();
804
            $columnData['name'] = $column->getQuotedName($this);
805 806 807
            $columnData['type'] = $column->getType();
            $columnData['length'] = $column->getLength();
            $columnData['notnull'] = $column->getNotNull();
808
            $columnData['unique'] = false; // TODO: what do we do about this?
809 810 811 812 813 814 815
            $columnData['version'] = ($column->hasPlatformOption("version"))?$column->getPlatformOption('version'):false;
            if(strtolower($columnData['type']) == "string" && $columnData['length'] === null) {
                $columnData['length'] = 255;
            }
            $columnData['precision'] = $column->getPrecision();
            $columnData['scale'] = $column->getScale();
            $columnData['default'] = $column->getDefault();
816
            $columnData['columnDefinition'] = $column->getColumnDefinition();
817
            $columnData['autoincrement'] = $column->getAutoincrement();
818 819 820 821 822 823 824 825

            if(in_array($column->getName(), $options['primary'])) {
                $columnData['primary'] = true;
            }

            $columns[$columnData['name']] = $columnData;
        }

826 827 828 829 830 831 832
        if (($createFlags&self::CREATE_FOREIGNKEYS) > 0) {
            $options['foreignKeys'] = array();
            foreach ($table->getForeignKeys() AS $fkConstraint) {
                $options['foreignKeys'][] = $fkConstraint;
            }
        }

833
        return $this->_getCreateTableSQL($tableName, $columns, $options);
834 835 836
    }

    /**
837
     * @param string $tableName
838 839 840 841
     * @param array $columns
     * @param array $options
     * @return array
     */
842
    protected function _getCreateTableSQL($tableName, array $columns, array $options = array())
843
    {
844
        $columnListSql = $this->getColumnDeclarationListSQL($columns);
845
        
846
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
847
            foreach ($options['uniqueConstraints'] as $name => $definition) {
848
                $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
849 850 851
            }
        }
        
852
        if (isset($options['primary']) && ! empty($options['primary'])) {
853
            $columnListSql .= ', PRIMARY KEY(' . implode(', ', array_unique(array_values($options['primary']))) . ')';
854 855 856 857
        }

        if (isset($options['indexes']) && ! empty($options['indexes'])) {
            foreach($options['indexes'] as $index => $definition) {
858
                $columnListSql .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
859 860 861
            }
        }

862
        $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql;
863

864
        $check = $this->getCheckDeclarationSQL($columns);
865 866
        if ( ! empty($check)) {
            $query .= ', ' . $check;
867
        }
868 869 870 871 872
        $query .= ')';

        $sql[] = $query;

        if (isset($options['foreignKeys'])) {
873
            foreach ((array) $options['foreignKeys'] AS $definition) {
874
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
875 876
            }
        }
877

878 879
        return $sql;
    }
880
    
881
    public function getCreateTemporaryTableSnippetSQL()
882 883 884
    {
        return "CREATE TEMPORARY TABLE";
    }
885

886
    /**
887
     * Gets the SQL to create a sequence on this platform.
888
     *
889
     * @param \Doctrine\DBAL\Schema\Sequence $sequence
890
     * @throws DBALException
891
     */
892
    public function getCreateSequenceSQL(\Doctrine\DBAL\Schema\Sequence $sequence)
893
    {
894
        throw DBALException::notSupported(__METHOD__);
895
    }
896

897
    /**
898
     * Gets the SQL to create a constraint on a table on this platform.
899
     *
900 901
     * @param Constraint $constraint
     * @param string|Table $table
902
     * @return string
903
     */
904
    public function getCreateConstraintSQL(\Doctrine\DBAL\Schema\Constraint $constraint, $table)
905
    {
906
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
907
            $table = $table->getQuotedName($this);
908 909
        }

910
        $query = 'ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraint->getQuotedName($this);
911

912
        $columns = array();
913
        foreach ($constraint->getColumns() as $column) {
914
            $columns[] = $column;
915
        }
916 917 918 919 920 921 922 923 924 925
        $columnList = '('. implode(', ', $columns) . ')';

        $referencesClause = '';
        if ($constraint instanceof \Doctrine\DBAL\Schema\Index) {
            if($constraint->isPrimary()) {
                $query .= ' PRIMARY KEY';
            } elseif ($constraint->isUnique()) {
                $query .= ' UNIQUE';
            } else {
                throw new \InvalidArgumentException(
926
                    'Can only create primary or unique constraints, no common indexes with getCreateConstraintSQL().'
927 928 929 930 931 932 933 934 935 936 937 938 939
                );
            }
        } else if ($constraint instanceof \Doctrine\DBAL\Schema\ForeignKeyConstraint) {
            $query .= ' FOREIGN KEY';

            $foreignColumns = array();
            foreach ($constraint->getForeignColumns() AS $column) {
                $foreignColumns[] = $column;
            }
            
            $referencesClause = ' REFERENCES '.$constraint->getForeignTableName(). ' ('.implode(', ', $foreignColumns).')';
        }
        $query .= ' '.$columnList.$referencesClause;
940 941 942

        return $query;
    }
943

944
    /**
945
     * Gets the SQL to create an index on a table on this platform.
946
     *
947 948
     * @param Index $index
     * @param string|Table $table name of the table on which the index is to be created
949 950
     * @return string
     */
951
    public function getCreateIndexSQL(Index $index, $table)
952
    {
953
        if ($table instanceof Table) {
954
            $table = $table->getQuotedName($this);
955
        }
956
        $name = $index->getQuotedName($this);
957 958 959 960
        $columns = $index->getColumns();

        if (count($columns) == 0) {
            throw new \InvalidArgumentException("Incomplete definition. 'columns' required.");
961
        }
962 963 964 965 966 967 968 969
        
        if ($index->isPrimary()) {
            return $this->getCreatePrimaryKeySQL($index, $table);
        } else {
            $type = '';
            if ($index->isUnique()) {
                $type = 'UNIQUE ';
            }
970

971 972
            $query = 'CREATE ' . $type . 'INDEX ' . $name . ' ON ' . $table;
            $query .= ' (' . $this->getIndexFieldDeclarationListSQL($columns) . ')';
973 974 975 976
        }

        return $query;
    }
977 978 979 980 981 982 983 984 985 986 987 988
    
    /**
     * Get SQL to create an unnamed primary key constraint.
     * 
     * @param Index $index
     * @param string|Table $table
     * @return string
     */
    public function getCreatePrimaryKeySQL(Index $index, $table)
    {
        return 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . $this->getIndexFieldDeclarationListSQL($index->getColumns()) . ')';
    }
989

990
    /**
991 992
     * Quotes a string so that it can be safely used as a table or column name,
     * even if it is a reserved word of the platform.
993
     *
994
     * NOTE: Just because you CAN use quoted identifiers doesn't mean
995 996 997 998 999 1000 1001 1002 1003 1004
     * you SHOULD use them.  In general, they end up causing way more
     * problems than they solve.
     *
     * @param string $str           identifier name to be quoted
     * @return string               quoted identifier string
     */
    public function quoteIdentifier($str)
    {
        $c = $this->getIdentifierQuoteCharacter();

1005
        return $c . str_replace($c, $c.$c, $str) . $c;
1006
    }
1007

1008
    /**
1009
     * Create a new foreign key
1010
     *
1011 1012
     * @param ForeignKeyConstraint  $foreignKey    ForeignKey instance
     * @param string|Table          $table         name of the table on which the foreign key is to be created
1013 1014
     * @return string
     */
1015
    public function getCreateForeignKeySQL(ForeignKeyConstraint $foreignKey, $table)
1016
    {
1017
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
1018
            $table = $table->getQuotedName($this);
1019 1020
        }

1021
        $query = 'ALTER TABLE ' . $table . ' ADD ' . $this->getForeignKeyDeclarationSQL($foreignKey);
1022 1023 1024

        return $query;
    }
1025

1026
    /**
1027 1028 1029
     * Gets the sql statements for altering an existing table.
     *
     * The method returns an array of sql statements, since some platforms need several statements.
1030
     *
1031
     * @param TableDiff $diff
1032
     * @return array
1033
     */
1034
    public function getAlterTableSQL(TableDiff $diff)
1035
    {
1036
        throw DBALException::notSupported(__METHOD__);
1037
    }
1038

1039 1040 1041 1042 1043 1044
    /**
     * Common code for alter table statement generation that updates the changed Index and Foreign Key definitions.
     *
     * @param TableDiff $diff
     * @return array
     */
1045
    protected function _getAlterTableIndexForeignKeySQL(TableDiff $diff)
1046
    {
1047 1048 1049 1050 1051 1052
        if ($diff->newName !== false) {
            $tableName = $diff->newName;
        } else {
            $tableName = $diff->name;
        }

1053 1054 1055
        $sql = array();
        if ($this->supportsForeignKeyConstraints()) {
            foreach ($diff->removedForeignKeys AS $foreignKey) {
1056
                $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
1057
            }
1058
            foreach ($diff->addedForeignKeys AS $foreignKey) {
1059
                $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
1060
            }
1061
            foreach ($diff->changedForeignKeys AS $foreignKey) {
1062 1063
                $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
                $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
1064 1065 1066 1067
            }
        }

        foreach ($diff->addedIndexes AS $index) {
1068
            $sql[] = $this->getCreateIndexSQL($index, $tableName);
1069 1070
        }
        foreach ($diff->removedIndexes AS $index) {
1071
            $sql[] = $this->getDropIndexSQL($index, $tableName);
1072 1073
        }
        foreach ($diff->changedIndexes AS $index) {
1074 1075
            $sql[] = $this->getDropIndexSQL($index, $tableName);
            $sql[] = $this->getCreateIndexSQL($index, $tableName);
1076 1077 1078 1079 1080
        }

        return $sql;
    }

1081
    /**
1082
     * Get declaration of a number of fields in bulk
1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109
     *
     * @param array $fields  a multidimensional associative array.
     *      The first dimension determines the field name, while the second
     *      dimension is keyed with the name of the properties
     *      of the field being declared as array indexes. Currently, the types
     *      of supported field properties are as follows:
     *
     *      length
     *          Integer value that determines the maximum length of the text
     *          field. If this argument is missing the field should be
     *          declared to have the longest length allowed by the DBMS.
     *
     *      default
     *          Text 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.
     *      charset
     *          Text value with the default CHARACTER SET for this field.
     *      collation
     *          Text value with the default COLLATION for this field.
     *      unique
     *          unique constraint
     *
     * @return string
     */
1110
    public function getColumnDeclarationListSQL(array $fields)
1111
    {
1112
        $queryFields = array();
1113
        foreach ($fields as $fieldName => $field) {
1114
            $query = $this->getColumnDeclarationSQL($fieldName, $field);
1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147
            $queryFields[] = $query;
        }
        return implode(', ', $queryFields);
    }

    /**
     * Obtain DBMS specific SQL code portion needed to declare a generic type
     * field to be used in statements like CREATE TABLE.
     *
     * @param string $name   name the field to be declared.
     * @param array  $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:
     *
     *      length
     *          Integer value that determines the maximum length of the text
     *          field. If this argument is missing the field should be
     *          declared to have the longest length allowed by the DBMS.
     *
     *      default
     *          Text 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.
     *      charset
     *          Text value with the default CHARACTER SET for this field.
     *      collation
     *          Text value with the default COLLATION for this field.
     *      unique
     *          unique constraint
     *      check
     *          column check constraint
1148 1149
     *      columnDefinition
     *          a string that defines the complete column
1150
     *
1151
     * @return string  DBMS specific SQL code portion that should be used to declare the column.
1152
     */
1153
    public function getColumnDeclarationSQL($name, array $field)
1154
    {
1155
        if (isset($field['columnDefinition'])) {
1156
            $columnDef = $this->getCustomTypeDeclarationSQL($field);
1157
        } else {
1158
            $default = $this->getDefaultValueDeclarationSQL($field);
1159

1160
            $charset = (isset($field['charset']) && $field['charset']) ?
1161
                    ' ' . $this->getColumnCharsetDeclarationSQL($field['charset']) : '';
1162

1163
            $collation = (isset($field['collation']) && $field['collation']) ?
1164
                    ' ' . $this->getColumnCollationDeclarationSQL($field['collation']) : '';
1165

1166
            $notnull = (isset($field['notnull']) && $field['notnull']) ? ' NOT NULL' : '';
1167

1168
            $unique = (isset($field['unique']) && $field['unique']) ?
1169
                    ' ' . $this->getUniqueFieldDeclarationSQL() : '';
1170

1171 1172
            $check = (isset($field['check']) && $field['check']) ?
                    ' ' . $field['check'] : '';
1173

1174 1175 1176
            $typeDecl = $field['type']->getSqlDeclaration($field, $this);
            $columnDef = $typeDecl . $charset . $default . $notnull . $unique . $check . $collation;
        }
1177

1178
        return $name . ' ' . $columnDef;
1179
    }
1180 1181 1182 1183 1184 1185 1186
    
    /**
     * Gets the SQL snippet that declares a floating point column of arbitrary precision.
     *
     * @param array $columnDef
     * @return string
     */
1187
    public function getDecimalTypeDeclarationSQL(array $columnDef) 
1188 1189
    {
        $columnDef['precision'] = ( ! isset($columnDef['precision']) || empty($columnDef['precision']))
1190
            ? 10 : $columnDef['precision'];
1191 1192 1193 1194 1195
        $columnDef['scale'] = ( ! isset($columnDef['scale']) || empty($columnDef['scale']))
            ? 0 : $columnDef['scale'];
        
        return 'NUMERIC(' . $columnDef['precision'] . ', ' . $columnDef['scale'] . ')';
    }
1196 1197 1198 1199 1200 1201 1202 1203

    /**
     * Obtain DBMS specific SQL code portion needed to set a default value
     * declaration to be used in statements like CREATE TABLE.
     *
     * @param array $field      field definition array
     * @return string           DBMS specific SQL code portion needed to set a default value
     */
1204
    public function getDefaultValueDeclarationSQL($field)
1205
    {
1206
        $default = empty($field['notnull']) ? ' DEFAULT NULL' : '';
1207

1208
        if (isset($field['default'])) {
1209 1210 1211 1212
            $default = " DEFAULT '".$field['default']."'";
            if (isset($field['type'])) {
                if (in_array((string)$field['type'], array("Integer", "BigInteger", "SmallInteger"))) {
                    $default = " DEFAULT ".$field['default'];
1213 1214
                } else if ((string)$field['type'] == 'DateTime' && $field['default'] == $this->getCurrentTimestampSQL()) {
                    $default = " DEFAULT ".$this->getCurrentTimestampSQL();
1215 1216
                }
            }
1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227
        }
        return $default;
    }

    /**
     * Obtain DBMS specific SQL code portion needed to set a CHECK constraint
     * declaration to be used in statements like CREATE TABLE.
     *
     * @param array $definition     check definition
     * @return string               DBMS specific SQL code portion needed to set a CHECK constraint
     */
1228
    public function getCheckDeclarationSQL(array $definition)
1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246
    {
        $constraints = array();
        foreach ($definition as $field => $def) {
            if (is_string($def)) {
                $constraints[] = 'CHECK (' . $def . ')';
            } else {
                if (isset($def['min'])) {
                    $constraints[] = 'CHECK (' . $field . ' >= ' . $def['min'] . ')';
                }

                if (isset($def['max'])) {
                    $constraints[] = 'CHECK (' . $field . ' <= ' . $def['max'] . ')';
                }
            }
        }

        return implode(', ', $constraints);
    }
1247 1248 1249 1250 1251 1252 1253 1254 1255 1256
    
    /**
     * Obtain DBMS specific SQL code portion needed to set a unique
     * constraint declaration to be used in statements like CREATE TABLE.
     *
     * @param string $name          name of the unique constraint
     * @param Index $index          index definition
     * @return string               DBMS specific SQL code portion needed 
     *                              to set a constraint
     */
1257
    public function getUniqueConstraintDeclarationSQL($name, Index $index)
1258 1259 1260 1261 1262
    {
        if (count($index->getColumns()) == 0) {
            throw \InvalidArgumentException("Incomplete definition. 'columns' required.");
        }
        
1263
        return 'CONSTRAINT ' . $name . ' UNIQUE ('
1264
             . $this->getIndexFieldDeclarationListSQL($index->getColumns()) 
1265 1266
             . ')';
    }
1267 1268 1269 1270 1271 1272

    /**
     * Obtain DBMS specific SQL code portion needed to set an index
     * declaration to be used in statements like CREATE TABLE.
     *
     * @param string $name          name of the index
1273
     * @param Index $index          index definition
1274 1275
     * @return string               DBMS specific SQL code portion needed to set an index
     */
1276
    public function getIndexDeclarationSQL($name, Index $index)
1277
    {
1278
        $type = '';
1279

1280
        if($index->isUnique()) {
1281
            $type = 'UNIQUE ';
1282 1283
        }

1284
        if (count($index->getColumns()) == 0) {
1285
            throw \InvalidArgumentException("Incomplete definition. 'columns' required.");
1286 1287
        }

1288
        return $type . 'INDEX ' . $name . ' ('
1289
             . $this->getIndexFieldDeclarationListSQL($index->getColumns()) 
1290
             . ')';
1291 1292
    }

1293 1294 1295 1296 1297 1298 1299 1300
    /**
     * getCustomTypeDeclarationSql
     * Obtail SQL code portion needed to create a custom column,
     * e.g. when a field has the "columnDefinition" keyword.
     * Only "AUTOINCREMENT" and "PRIMARY KEY" are added if appropriate.
     *
     * @return string
     */
1301
    public function getCustomTypeDeclarationSQL(array $columnDef)
1302 1303 1304 1305
    {
        return $columnDef['columnDefinition'];
    }

1306 1307 1308 1309 1310 1311 1312
    /**
     * getIndexFieldDeclarationList
     * Obtain DBMS specific SQL code portion needed to set an index
     * declaration to be used in statements like CREATE TABLE.
     *
     * @return string
     */
1313
    public function getIndexFieldDeclarationListSQL(array $fields)
1314 1315 1316 1317
    {
        $ret = array();
        foreach ($fields as $field => $definition) {
            if (is_array($definition)) {
1318
                $ret[] = $field;
1319
            } else {
1320
                $ret[] = $definition;
1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339
            }
        }
        return implode(', ', $ret);
    }

    /**
     * A method to return the required SQL string that fits between CREATE ... TABLE
     * to create the table as a temporary table.
     *
     * Should be overridden in driver classes to return the correct string for the
     * specific database type.
     *
     * The default is to return the string "TEMPORARY" - this will result in a
     * SQL error for any database that does not support temporary tables, or that
     * requires a different SQL command from "CREATE TEMPORARY TABLE".
     *
     * @return string The string required to be placed between "CREATE" and "TABLE"
     *                to generate a temporary table, if possible.
     */
1340
    public function getTemporaryTableSQL()
1341 1342 1343
    {
        return 'TEMPORARY';
    }
1344

1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355
    /**
     * Some vendors require temporary table names to be qualified specially.
     *
     * @param  string $tableName
     * @return string
     */
    public function getTemporaryTableName($tableName)
    {
        return $tableName;
    }

1356
    /**
1357
     * Get sql query to show a list of database.
1358
     *
1359
     * @return string
1360
     */
1361
    public function getShowDatabasesSQL()
1362
    {
1363
        throw DBALException::notSupported(__METHOD__);
1364
    }
1365

1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406
    /**
     * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @param array $definition         an associative array with the following structure:
     *          name                    optional constraint name
     *
     *          local                   the local field(s)
     *
     *          foreign                 the foreign reference field(s)
     *
     *          foreignTable            the name of the foreign table
     *
     *          onDelete                referential delete action
     *
     *          onUpdate                referential update action
     *
     *          deferred                deferred constraint checking
     *
     * The onDelete and onUpdate keys accept the following values:
     *
     * CASCADE: Delete or update the row from the parent table and automatically delete or
     *          update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.
     *          Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column
     *          in the parent table or in the child table.
     *
     * SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the
     *          child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier
     *          specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.
     *
     * NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary
     *           key value is not allowed to proceed if there is a related foreign key value in the referenced table.
     *
     * RESTRICT: Rejects the delete or update operation for the parent table. NO ACTION and RESTRICT are the same as
     *           omitting the ON DELETE or ON UPDATE clause.
     *
     * SET DEFAULT
     *
     * @return string  DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
     *                 of a field declaration.
     */
1407
    public function getForeignKeyDeclarationSQL(ForeignKeyConstraint $foreignKey)
1408
    {
1409 1410
        $sql  = $this->getForeignKeyBaseDeclarationSQL($foreignKey);
        $sql .= $this->getAdvancedForeignKeyOptionsSQL($foreignKey);
1411 1412 1413 1414 1415 1416 1417 1418

        return $sql;
    }

    /**
     * Return the FOREIGN KEY query section dealing with non-standard options
     * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
     *
1419
     * @param ForeignKeyConstraint $foreignKey     foreign key definition
1420 1421
     * @return string
     */
1422
    public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
1423 1424
    {
        $query = '';
1425
        if ($this->supportsForeignKeyOnUpdate() && $foreignKey->hasOption('onUpdate')) {
1426
            $query .= ' ON UPDATE ' . $this->getForeignKeyReferentialActionSQL($foreignKey->getOption('onUpdate'));
1427
        }
1428
        if ($foreignKey->hasOption('onDelete')) {
1429
            $query .= ' ON DELETE ' . $this->getForeignKeyReferentialActionSQL($foreignKey->getOption('onDelete'));
1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441
        }
        return $query;
    }

    /**
     * returns given referential action in uppercase if valid, otherwise throws
     * an exception
     *
     * @throws Doctrine_Exception_Exception     if unknown referential action given
     * @param string $action    foreign key referential action
     * @param string            foreign key referential action in uppercase
     */
1442
    public function getForeignKeyReferentialActionSQL($action)
1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453
    {
        $upper = strtoupper($action);
        switch ($upper) {
            case 'CASCADE':
            case 'SET NULL':
            case 'NO ACTION':
            case 'RESTRICT':
            case 'SET DEFAULT':
                return $upper;
            break;
            default:
1454
                throw new \InvalidArgumentException('Invalid foreign key action: ' . $upper);
1455 1456 1457 1458 1459 1460 1461
        }
    }

    /**
     * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
     * of a field declaration to be used in statements like CREATE TABLE.
     *
1462
     * @param ForeignKeyConstraint $foreignKey
1463 1464
     * @return string
     */
1465
    public function getForeignKeyBaseDeclarationSQL(ForeignKeyConstraint $foreignKey)
1466 1467
    {
        $sql = '';
1468
        if (strlen($foreignKey->getName())) {
1469
            $sql .= 'CONSTRAINT ' . $foreignKey->getQuotedName($this) . ' ';
1470 1471 1472
        }
        $sql .= 'FOREIGN KEY (';

1473
        if (count($foreignKey->getLocalColumns()) == 0) {
1474
            throw new \InvalidArgumentException("Incomplete definition. 'local' required.");
1475
        }
1476
        if (count($foreignKey->getForeignColumns()) == 0) {
1477
            throw new \InvalidArgumentException("Incomplete definition. 'foreign' required.");
1478
        }
1479
        if (strlen($foreignKey->getForeignTableName()) == 0) {
1480
            throw new \InvalidArgumentException("Incomplete definition. 'foreignTable' required.");
1481 1482
        }

1483
        $sql .= implode(', ', $foreignKey->getLocalColumns())
1484
              . ') REFERENCES '
1485 1486
              . $foreignKey->getForeignTableName() . '('
              . implode(', ', $foreignKey->getForeignColumns()) . ')';
1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497

        return $sql;
    }

    /**
     * Obtain DBMS specific SQL code portion needed to set the UNIQUE constraint
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @return string  DBMS specific SQL code portion needed to set the UNIQUE constraint
     *                 of a field declaration.
     */
1498
    public function getUniqueFieldDeclarationSQL()
1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510
    {
        return 'UNIQUE';
    }

    /**
     * Obtain DBMS specific SQL code portion needed to set the CHARACTER SET
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @param string $charset   name of the charset
     * @return string  DBMS specific SQL code portion needed to set the CHARACTER SET
     *                 of a field declaration.
     */
1511
    public function getColumnCharsetDeclarationSQL($charset)
1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523
    {
        return '';
    }

    /**
     * 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.
     */
1524
    public function getColumnCollationDeclarationSQL($collation)
1525 1526 1527
    {
        return '';
    }
1528

1529 1530 1531 1532 1533 1534 1535 1536 1537 1538
    /**
     * Whether the platform prefers sequences for ID generation.
     * Subclasses should override this method to return TRUE if they prefer sequences.
     *
     * @return boolean
     */
    public function prefersSequences()
    {
        return false;
    }
1539

1540 1541 1542 1543 1544 1545 1546 1547 1548 1549
    /**
     * Whether the platform prefers identity columns (eg. autoincrement) for ID generation.
     * Subclasses should override this method to return TRUE if they prefer identity columns.
     *
     * @return boolean
     */
    public function prefersIdentityColumns()
    {
        return false;
    }
1550

1551 1552
    /**
     * Some platforms need the boolean values to be converted.
romanb's avatar
romanb committed
1553 1554
     * 
     * The default conversion in this implementation converts to integers (false => 0, true => 1).
1555
     *
romanb's avatar
romanb committed
1556
     * @param mixed $item
1557 1558 1559 1560 1561 1562 1563 1564 1565
     */
    public function convertBooleans($item)
    {
        if (is_array($item)) {
            foreach ($item as $k => $value) {
                if (is_bool($value)) {
                    $item[$k] = (int) $value;
                }
            }
romanb's avatar
romanb committed
1566 1567
        } else if (is_bool($item)) {
            $item = (int) $item;
1568 1569 1570 1571 1572
        }
        return $item;
    }

    /**
1573
     * Gets the SQL statement specific for the platform to set the charset.
1574
     *
1575 1576 1577
     * This function is MySQL specific and required by
     * {@see \Doctrine\DBAL\Connection::setCharset($charset)}
     *
1578
     * @param string $charset
1579 1580
     * @return string
     */
1581
    public function getSetCharsetSQL($charset)
1582
    {
1583
        return "SET NAMES '".$charset."'";
1584
    }
1585

1586 1587 1588 1589 1590
    /**
     * Gets the SQL specific for the platform to get the current date.
     *
     * @return string
     */
1591
    public function getCurrentDateSQL()
1592 1593 1594 1595 1596 1597 1598 1599 1600
    {
        return 'CURRENT_DATE';
    }

    /**
     * Gets the SQL specific for the platform to get the current time.
     *
     * @return string
     */
1601
    public function getCurrentTimeSQL()
1602 1603 1604 1605
    {
        return 'CURRENT_TIME';
    }

1606 1607 1608 1609 1610
    /**
     * Gets the SQL specific for the platform to get the current timestamp
     *
     * @return string
     */
1611
    public function getCurrentTimestampSQL()
1612 1613 1614
    {
        return 'CURRENT_TIMESTAMP';
    }
1615

romanb's avatar
romanb committed
1616
    /**
1617
     * Get sql for transaction isolation level Connection constant
romanb's avatar
romanb committed
1618
     *
1619
     * @param integer $level
romanb's avatar
romanb committed
1620
     */
1621
    protected function _getTransactionIsolationLevelSQL($level)
romanb's avatar
romanb committed
1622 1623
    {
        switch ($level) {
1624
            case Connection::TRANSACTION_READ_UNCOMMITTED:
romanb's avatar
romanb committed
1625
                return 'READ UNCOMMITTED';
1626
            case Connection::TRANSACTION_READ_COMMITTED:
romanb's avatar
romanb committed
1627
                return 'READ COMMITTED';
1628
            case Connection::TRANSACTION_REPEATABLE_READ:
romanb's avatar
romanb committed
1629
                return 'REPEATABLE READ';
1630
            case Connection::TRANSACTION_SERIALIZABLE:
romanb's avatar
romanb committed
1631 1632
                return 'SERIALIZABLE';
            default:
1633
                throw new \InvalidArgumentException('Invalid isolation level:' . $level);
1634 1635 1636
        }
    }

1637
    public function getListDatabasesSQL()
1638
    {
1639
        throw DBALException::notSupported(__METHOD__);
1640 1641
    }

1642
    public function getListSequencesSQL($database)
1643
    {
1644
        throw DBALException::notSupported(__METHOD__);
1645 1646
    }

1647
    public function getListTableConstraintsSQL($table)
1648
    {
1649
        throw DBALException::notSupported(__METHOD__);
1650 1651
    }

1652
    public function getListTableColumnsSQL($table)
1653
    {
1654
        throw DBALException::notSupported(__METHOD__);
1655 1656
    }

1657
    public function getListTablesSQL()
1658
    {
1659
        throw DBALException::notSupported(__METHOD__);
1660 1661
    }

1662
    public function getListUsersSQL()
1663
    {
1664
        throw DBALException::notSupported(__METHOD__);
1665 1666
    }

1667 1668 1669 1670 1671 1672
    /**
     * Get the SQL to list all views of a database or user.
     *
     * @param string $database
     * @return string
     */
1673
    public function getListViewsSQL($database)
1674
    {
1675
        throw DBALException::notSupported(__METHOD__);
1676 1677
    }

1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691
    /**
     * Get the list of indexes for the current database.
     * 
     * The current database parameter is optional but will always be passed
     * when using the SchemaManager API and is the database the given table is in.
     * 
     * Attention: Some platforms only support currentDatabase when they
     * are connected with that database. Cross-database information schema
     * requests may be impossible.
     * 
     * @param string $table
     * @param string $currentDatabase 
     */
    public function getListTableIndexesSQL($table, $currentDatabase = null)
1692
    {
1693
        throw DBALException::notSupported(__METHOD__);
1694 1695
    }

1696
    public function getListTableForeignKeysSQL($table)
1697
    {
1698
        throw DBALException::notSupported(__METHOD__);
1699 1700
    }

1701
    public function getCreateViewSQL($name, $sql)
1702
    {
1703
        throw DBALException::notSupported(__METHOD__);
1704 1705
    }

1706
    public function getDropViewSQL($name)
1707
    {
1708
        throw DBALException::notSupported(__METHOD__);
1709 1710
    }

1711
    public function getDropSequenceSQL($sequence)
1712
    {
1713
        throw DBALException::notSupported(__METHOD__);
1714 1715
    }

1716
    public function getSequenceNextValSQL($sequenceName)
1717
    {
1718
        throw DBALException::notSupported(__METHOD__);
romanb's avatar
romanb committed
1719
    }
1720

1721
    public function getCreateDatabaseSQL($database)
1722
    {
1723
        throw DBALException::notSupported(__METHOD__);
1724 1725
    }

romanb's avatar
romanb committed
1726
    /**
1727
     * Get sql to set the transaction isolation level
romanb's avatar
romanb committed
1728
     *
1729
     * @param integer $level
romanb's avatar
romanb committed
1730
     */
1731
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
1732
    {
1733
        throw DBALException::notSupported(__METHOD__);
romanb's avatar
romanb committed
1734
    }
1735

1736 1737 1738 1739 1740 1741 1742
    /**
     * Obtain DBMS specific SQL to be used to create datetime fields in 
     * statements like CREATE TABLE
     *
     * @param array $fieldDeclaration 
     * @return string
     */
1743
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
1744
    {
1745
        throw DBALException::notSupported(__METHOD__);
1746
    }
1747 1748 1749 1750 1751 1752 1753 1754

    /**
     * Obtain DBMS specific SQL to be used to create datetime with timezone offset fields.
     * 
     * @param array $fieldDeclaration
     */
    public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration)
    {
1755
        return $this->getDateTimeTypeDeclarationSQL($fieldDeclaration);
1756 1757
    }
    
1758 1759 1760 1761 1762 1763 1764 1765
    
    /**
     * Obtain DBMS specific SQL to be used to create date fields in statements
     * like CREATE TABLE.
     * 
     * @param array $fieldDeclaration
     * @return string
     */
1766
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
1767
    {
1768
        throw DBALException::notSupported(__METHOD__);
1769
    }
1770

1771 1772 1773 1774 1775 1776 1777
    /**
     * Obtain DBMS specific SQL to be used to create time fields in statements
     * like CREATE TABLE.
     *
     * @param array $fieldDeclaration
     * @return string
     */
1778
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
1779
    {
1780
        throw DBALException::notSupported(__METHOD__);
1781 1782
    }

1783 1784 1785 1786 1787
    public function getFloatDeclarationSQL(array $fieldDeclaration)
    {
        return 'DOUBLE PRECISION';
    }

romanb's avatar
romanb committed
1788 1789 1790 1791
    /**
     * Gets the default transaction isolation level of the platform.
     *
     * @return integer The default isolation level.
1792
     * @see Doctrine\DBAL\Connection\TRANSACTION_* constants.
romanb's avatar
romanb committed
1793 1794 1795
     */
    public function getDefaultTransactionIsolationLevel()
    {
1796
        return Connection::TRANSACTION_READ_COMMITTED;
romanb's avatar
romanb committed
1797
    }
1798

1799
    /* supports*() metods */
1800 1801 1802 1803 1804 1805

    /**
     * Whether the platform supports sequences.
     *
     * @return boolean
     */
1806 1807 1808 1809
    public function supportsSequences()
    {
        return false;
    }
1810 1811 1812 1813 1814 1815 1816 1817

    /**
     * Whether the platform supports identity columns.
     * Identity columns are columns that recieve an auto-generated value from the
     * database on insert of a row.
     *
     * @return boolean
     */
1818 1819 1820 1821
    public function supportsIdentityColumns()
    {
        return false;
    }
1822 1823 1824 1825 1826 1827

    /**
     * Whether the platform supports indexes.
     *
     * @return boolean
     */
1828 1829 1830 1831
    public function supportsIndexes()
    {
        return true;
    }
1832

1833 1834 1835 1836 1837
    public function supportsAlterTable()
    {
        return true;
    }

1838 1839 1840 1841 1842
    /**
     * Whether the platform supports transactions.
     *
     * @return boolean
     */
1843 1844 1845 1846
    public function supportsTransactions()
    {
        return true;
    }
1847 1848 1849 1850 1851 1852

    /**
     * Whether the platform supports savepoints.
     *
     * @return boolean
     */
1853 1854 1855 1856
    public function supportsSavepoints()
    {
        return true;
    }
1857

1858 1859 1860 1861 1862 1863 1864 1865 1866 1867
    /**
     * Whether the platform supports releasing savepoints.
     *
     * @return boolean
     */
    public function supportsReleaseSavepoints()
    {
        return $this->supportsSavepoints();
    }

1868 1869 1870 1871 1872
    /**
     * Whether the platform supports primary key constraints.
     *
     * @return boolean
     */
1873 1874 1875 1876
    public function supportsPrimaryConstraints()
    {
        return true;
    }
1877 1878

    /**
1879
     * Does the platform supports foreign key constraints?
1880 1881 1882
     *
     * @return boolean
     */
1883 1884 1885 1886
    public function supportsForeignKeyConstraints()
    {
        return true;
    }
1887 1888 1889 1890 1891 1892 1893 1894 1895 1896

    /**
     * Does this platform supports onUpdate in foreign key constraints?
     * 
     * @return bool
     */
    public function supportsForeignKeyOnUpdate()
    {
        return ($this->supportsForeignKeyConstraints() && true);
    }
1897 1898 1899 1900 1901 1902 1903 1904 1905 1906
    
    /**
     * Whether the platform supports database schemas.
     * 
     * @return boolean
     */
    public function supportsSchemas()
    {
        return false;
    }
1907

1908 1909 1910 1911 1912 1913 1914 1915 1916 1917
    /**
     * Some databases don't allow to create and drop databases at all or only with certain tools.
     *
     * @return bool
     */
    public function supportsCreateDropDatabase()
    {
        return true;
    }

1918
    /**
1919
     * Whether the platform supports getting the affected rows of a recent
1920 1921 1922 1923
     * update/delete type query.
     *
     * @return boolean
     */
1924 1925 1926 1927
    public function supportsGettingAffectedRows()
    {
        return true;
    }
1928

1929
    public function getIdentityColumnNullInsertSQL()
1930 1931 1932 1933
    {
        return "";
    }

1934
    /**
1935 1936 1937 1938
     * Gets the format string, as accepted by the date() function, that describes
     * the format of a stored datetime value of this platform.
     * 
     * @return string The format string.
1939 1940 1941 1942 1943 1944
     */
    public function getDateTimeFormatString()
    {
        return 'Y-m-d H:i:s';
    }

1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955
    /**
     * Gets the format string, as accepted by the date() function, that describes
     * the format of a stored datetime with timezone value of this platform.
     *
     * @return string The format string.
     */
    public function getDateTimeTzFormatString()
    {
        return 'Y-m-d H:i:s';
    }

1956 1957 1958 1959 1960 1961
    /**
     * Gets the format string, as accepted by the date() function, that describes
     * the format of a stored date value of this platform.
     * 
     * @return string The format string.
     */
1962 1963
    public function getDateFormatString()
    {
1964
        return 'Y-m-d';
1965
    }
1966 1967 1968 1969 1970 1971 1972
    
    /**
     * Gets the format string, as accepted by the date() function, that describes
     * the format of a stored time value of this platform.
     * 
     * @return string The format string.
     */
1973 1974 1975 1976 1977
    public function getTimeFormatString()
    {
        return 'H:i:s';
    }

1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005
    /**
     * Modify limit query
     * 
     * @param string $query
     * @param int $limit
     * @param int $offset
     * @return string
     */
    final public function modifyLimitQuery($query, $limit, $offset = null)
    {
        if ( $limit !== null) {
            $limit = (int)$limit;
        }

        if ( $offset !== null) {
            $offset = (int)$offset;
        }

        return $this->doModifyLimitQuery($query, $limit, $offset);
    }

    /**
     * @param string $query
     * @param int $limit
     * @param int $offset
     * @return string
     */
    protected function doModifyLimitQuery($query, $limit, $offset)
2006
    {
2007
        if ( $limit !== null) {
2008
            $query .= ' LIMIT ' . $limit;
2009 2010
        }

2011
        if ( $offset !== null) {
2012 2013 2014
            $query .= ' OFFSET ' . $offset;
        }

2015 2016
        return $query;
    }
2017
    
2018 2019 2020 2021 2022 2023
    /**
     * Gets the character casing of a column in an SQL result set of this platform.
     * 
     * @param string $column The column name for which to get the correct character casing.
     * @return string The column name in the character casing used in SQL result sets.
     */
2024
    public function getSQLResultCasing($column)
2025 2026 2027
    {
        return $column;
    }
2028 2029 2030 2031 2032 2033 2034 2035 2036 2037 2038 2039
    
    /**
     * Makes any fixes to a name of a schema element (table, sequence, ...) that are required
     * by restrictions of the platform, like a maximum length.
     * 
     * @param string $schemaName
     * @return string
     */
    public function fixSchemaElementName($schemaElementName)
    {
        return $schemaElementName;
    }
2040

2041 2042 2043 2044 2045 2046 2047 2048 2049 2050
    /**
     * Maximum length of any given databse identifier, like tables or column names.
     * 
     * @return int
     */
    public function getMaxIdentifierLength()
    {
        return 63;
    }

2051 2052 2053 2054 2055 2056 2057
    /**
     * Get the insert sql for an empty insert statement
     *
     * @param string $tableName 
     * @param string $identifierColumnName 
     * @return string $sql
     */
2058
    public function getEmptyIdentityInsertSQL($tableName, $identifierColumnName)
2059 2060 2061
    {
        return 'INSERT INTO ' . $tableName . ' (' . $identifierColumnName . ') VALUES (null)';
    }
2062 2063 2064 2065 2066 2067 2068 2069 2070 2071 2072

    /**
     * Generate a Truncate Table SQL statement for a given table.
     *
     * Cascade is not supported on many platforms but would optionally cascade the truncate by
     * following the foreign keys.
     *
     * @param  string $tableName
     * @param  bool $cascade
     * @return string
     */
2073
    public function getTruncateTableSQL($tableName, $cascade = false)
2074 2075 2076
    {
        return 'TRUNCATE '.$tableName;
    }
2077 2078 2079 2080 2081 2082 2083 2084 2085 2086

    /**
     * This is for test reasons, many vendors have special requirements for dummy statements.
     * 
     * @return string
     */
    public function getDummySelectSQL()
    {
        return 'SELECT 1';
    }
2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119

    /**
     * Generate SQL to create a new savepoint
     *
     * @param string $savepoint
     * @return string
     */
    public function createSavePoint($savepoint)
    {
        return 'SAVEPOINT ' . $savepoint;
    }

    /**
     * Generate SQL to release a savepoint
     *
     * @param string $savepoint
     * @return string
     */
    public function releaseSavePoint($savepoint)
    {
        return 'RELEASE SAVEPOINT ' . $savepoint;
    }

    /**
     * Generate SQL to rollback a savepoint
     *
     * @param string $savepoint
     * @return string
     */
    public function rollbackSavePoint($savepoint)
    {
        return 'ROLLBACK TO SAVEPOINT ' . $savepoint;
    }
2120
}