3 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
4 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
5 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
6 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
7 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
8 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
9 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
10 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
11 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
12 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
13 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
15 * This software consists of voluntary contributions made by many individuals
16 * and is licensed under the MIT license. For more information, see
17 * <http://www.doctrine-project.org>.
20 namespace Doctrine\DBAL\Platforms;
22 use Doctrine\DBAL\DBALException,
23 Doctrine\DBAL\Connection,
25 Doctrine\DBAL\Schema\Constraint,
26 Doctrine\DBAL\Schema\Sequence,
27 Doctrine\DBAL\Schema\Table,
28 Doctrine\DBAL\Schema\Index,
29 Doctrine\DBAL\Schema\ForeignKeyConstraint,
30 Doctrine\DBAL\Schema\TableDiff,
31 Doctrine\DBAL\Schema\Column,
32 Doctrine\DBAL\Schema\ColumnDiff,
33 Doctrine\DBAL\Types\Type,
35 Doctrine\Common\EventManager,
36 Doctrine\DBAL\Event\SchemaCreateTableEventArgs,
37 Doctrine\DBAL\Event\SchemaCreateTableColumnEventArgs,
38 Doctrine\DBAL\Event\SchemaDropTableEventArgs,
39 Doctrine\DBAL\Event\SchemaAlterTableEventArgs,
40 Doctrine\DBAL\Event\SchemaAlterTableAddColumnEventArgs,
41 Doctrine\DBAL\Event\SchemaAlterTableRemoveColumnEventArgs,
42 Doctrine\DBAL\Event\SchemaAlterTableChangeColumnEventArgs,
43 Doctrine\DBAL\Event\SchemaAlterTableRenameColumnEventArgs;
46 * Base class for all DatabasePlatforms. The DatabasePlatforms are the central
47 * point of abstraction of platform-specific behaviors, features and SQL dialects.
48 * They are a passive source of information.
51 * @link www.doctrine-project.org
53 * @author Guilherme Blanco <guilhermeblanco@hotmail.com>
54 * @author Jonathan Wage <jonwage@gmail.com>
55 * @author Roman Borschel <roman@code-factory.org>
56 * @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
57 * @author Benjamin Eberlei <kontakt@beberlei.de>
58 * @todo Remove any unnecessary methods.
60 abstract class AbstractPlatform
65 const CREATE_INDEXES = 1;
70 const CREATE_FOREIGNKEYS = 2;
75 const TRIM_UNSPECIFIED = 0;
80 const TRIM_LEADING = 1;
85 const TRIM_TRAILING = 2;
95 protected $doctrineTypeMapping = null;
98 * Contains a list of all columns that should generate parseable column comments for type-detection
99 * in reverse engineering scenarios.
103 protected $doctrineTypeComments = null;
106 * @var Doctrine\Common\EventManager
108 protected $_eventManager;
111 * Holds the KeywordList instance for the current platform.
113 * @var \Doctrine\DBAL\Platforms\Keywords\KeywordList
115 protected $_keywords;
120 public function __construct() {}
123 * Sets the EventManager used by the Platform.
125 * @param \Doctrine\Common\EventManager
127 public function setEventManager(EventManager $eventManager)
129 $this->_eventManager = $eventManager;
133 * Gets the EventManager used by the Platform.
135 * @return \Doctrine\Common\EventManager
137 public function getEventManager()
139 return $this->_eventManager;
143 * Gets the SQL snippet that declares a boolean column.
145 * @param array $columnDef
149 abstract public function getBooleanTypeDeclarationSQL(array $columnDef);
152 * Gets the SQL snippet that declares a 4 byte integer column.
154 * @param array $columnDef
158 abstract public function getIntegerTypeDeclarationSQL(array $columnDef);
161 * Gets the SQL snippet that declares an 8 byte integer column.
163 * @param array $columnDef
167 abstract public function getBigIntTypeDeclarationSQL(array $columnDef);
170 * Gets the SQL snippet that declares a 2 byte integer column.
172 * @param array $columnDef
176 abstract public function getSmallIntTypeDeclarationSQL(array $columnDef);
179 * Gets the SQL snippet that declares common properties of an integer column.
181 * @param array $columnDef
184 abstract protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef);
187 * Lazy load Doctrine Type Mappings
191 abstract protected function initializeDoctrineTypeMappings();
194 * Initialize Doctrine Type Mappings with the platform defaults
195 * and with all additional type mappings.
197 private function initializeAllDoctrineTypeMappings()
199 $this->initializeDoctrineTypeMappings();
201 foreach (Type::getTypesMap() as $typeName => $className) {
202 foreach (Type::getType($typeName)->getMappedDatabaseTypes($this) as $dbType) {
203 $this->doctrineTypeMapping[$dbType] = $typeName;
209 * Gets the SQL snippet used to declare a VARCHAR column type.
211 * @param array $field
215 public function getVarcharTypeDeclarationSQL(array $field)
217 if ( !isset($field['length'])) {
218 $field['length'] = $this->getVarcharDefaultLength();
221 $fixed = (isset($field['fixed'])) ? $field['fixed'] : false;
223 if ($field['length'] > $this->getVarcharMaxLength()) {
224 return $this->getClobTypeDeclarationSQL($field);
227 return $this->getVarcharTypeDeclarationSQLSnippet($field['length'], $fixed);
231 * Get the SQL Snippet to create a GUID/UUID field.
233 * By default this maps directly to a VARCHAR and only maps to more
234 * special datatypes when the underlying databases support this datatype.
236 * @param array $field
240 public function getGuidTypeDeclarationSQL(array $field)
242 return $this->getVarcharTypeDeclarationSQL($field);
246 * @param integer $length
247 * @param boolean $fixed
251 * @throws \Doctrine\DBAL\DBALException
253 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
255 throw DBALException::notSupported('VARCHARs not supported by Platform.');
259 * Gets the SQL snippet used to declare a CLOB column type.
261 * @param array $field
265 abstract public function getClobTypeDeclarationSQL(array $field);
268 * Gets the SQL Snippet used to declare a BLOB column type.
270 * @param array $field
274 abstract public function getBlobTypeDeclarationSQL(array $field);
277 * Gets the name of the platform.
281 abstract public function getName();
284 * Register a doctrine type to be used in conjunction with a column type of this platform.
286 * @param string $dbType
287 * @param string $doctrineType
289 * @throws \Doctrine\DBAL\DBALException if the type is not found
291 public function registerDoctrineTypeMapping($dbType, $doctrineType)
293 if ($this->doctrineTypeMapping === null) {
294 $this->initializeAllDoctrineTypeMappings();
297 if (!Types\Type::hasType($doctrineType)) {
298 throw DBALException::typeNotFound($doctrineType);
301 $dbType = strtolower($dbType);
302 $this->doctrineTypeMapping[$dbType] = $doctrineType;
306 * Get the Doctrine type that is mapped for the given database column type.
308 * @param string $dbType
312 public function getDoctrineTypeMapping($dbType)
314 if ($this->doctrineTypeMapping === null) {
315 $this->initializeAllDoctrineTypeMappings();
318 $dbType = strtolower($dbType);
320 if (!isset($this->doctrineTypeMapping[$dbType])) {
321 throw new \Doctrine\DBAL\DBALException("Unknown database type ".$dbType." requested, " . get_class($this) . " may not support it.");
324 return $this->doctrineTypeMapping[$dbType];
328 * Check if a database type is currently supported by this platform.
330 * @param string $dbType
334 public function hasDoctrineTypeMappingFor($dbType)
336 if ($this->doctrineTypeMapping === null) {
337 $this->initializeAllDoctrineTypeMappings();
340 $dbType = strtolower($dbType);
341 return isset($this->doctrineTypeMapping[$dbType]);
345 * Initialize the Doctrine Type comments instance variable for in_array() checks.
349 protected function initializeCommentedDoctrineTypes()
351 $this->doctrineTypeComments = array();
353 foreach (Type::getTypesMap() as $typeName => $className) {
354 $type = Type::getType($typeName);
356 if ($type->requiresSQLCommentHint($this)) {
357 $this->doctrineTypeComments[] = $typeName;
363 * Is it necessary for the platform to add a parsable type comment to allow reverse engineering the given type?
365 * @param Type $doctrineType
369 public function isCommentedDoctrineType(Type $doctrineType)
371 if ($this->doctrineTypeComments === null) {
372 $this->initializeCommentedDoctrineTypes();
375 return in_array($doctrineType->getName(), $this->doctrineTypeComments);
379 * Mark this type as to be commented in ALTER TABLE and CREATE TABLE statements.
381 * @param string|Type $doctrineType
385 public function markDoctrineTypeCommented($doctrineType)
387 if ($this->doctrineTypeComments === null) {
388 $this->initializeCommentedDoctrineTypes();
391 $this->doctrineTypeComments[] = $doctrineType instanceof Type ? $doctrineType->getName() : $doctrineType;
395 * Get the comment to append to a column comment that helps parsing this type in reverse engineering.
397 * @param Type $doctrineType
400 public function getDoctrineTypeComment(Type $doctrineType)
402 return '(DC2Type:' . $doctrineType->getName() . ')';
406 * Return the comment of a passed column modified by potential doctrine type comment hints.
408 * @param Column $column
411 protected function getColumnComment(Column $column)
413 $comment = $column->getComment();
415 if ($this->isCommentedDoctrineType($column->getType())) {
416 $comment .= $this->getDoctrineTypeComment($column->getType());
423 * Gets the character used for identifier quoting.
427 public function getIdentifierQuoteCharacter()
433 * Gets the string portion that starts an SQL comment.
437 public function getSqlCommentStartString()
443 * Gets the string portion that ends an SQL comment.
447 public function getSqlCommentEndString()
453 * Gets the maximum length of a varchar field.
457 public function getVarcharMaxLength()
463 * Gets the default length of a varchar field.
467 public function getVarcharDefaultLength()
473 * Gets all SQL wildcard characters of the platform.
477 public function getWildcards()
479 return array('%', '_');
483 * Returns the regular expression operator.
487 public function getRegexpExpression()
489 throw DBALException::notSupported(__METHOD__);
493 * Returns global unique identifier
495 * @return string to get global unique identifier
497 public function getGuidExpression()
499 throw DBALException::notSupported(__METHOD__);
503 * Returns the average value of a column
505 * @param string $column the column to use
507 * @return string generated sql including an AVG aggregate function
509 public function getAvgExpression($column)
511 return 'AVG(' . $column . ')';
515 * Returns the number of rows (without a NULL value) of a column
517 * If a '*' is used instead of a column the number of selected rows
520 * @param string|integer $column the column to use
522 * @return string generated sql including a COUNT aggregate function
524 public function getCountExpression($column)
526 return 'COUNT(' . $column . ')';
530 * Returns the highest value of a column
532 * @param string $column the column to use
533 * @return string generated sql including a MAX aggregate function
535 public function getMaxExpression($column)
537 return 'MAX(' . $column . ')';
541 * Returns the lowest value of a column
543 * @param string $column the column to use
546 public function getMinExpression($column)
548 return 'MIN(' . $column . ')';
552 * Returns the total sum of a column
554 * @param string $column the column to use
557 public function getSumExpression($column)
559 return 'SUM(' . $column . ')';
565 * Returns the md5 sum of a field.
567 * Note: Not SQL92, but common functionality
569 * @param string $column
572 public function getMd5Expression($column)
574 return 'MD5(' . $column . ')';
578 * Returns the length of a text field.
580 * @param string $column
584 public function getLengthExpression($column)
586 return 'LENGTH(' . $column . ')';
590 * Returns the squared value of a column
592 * @param string $column the column to use
594 * @return string generated sql including an SQRT aggregate function
596 public function getSqrtExpression($column)
598 return 'SQRT(' . $column . ')';
602 * Rounds a numeric field to the number of decimals specified.
604 * @param string $column
605 * @param integer $decimals
609 public function getRoundExpression($column, $decimals = 0)
611 return 'ROUND(' . $column . ', ' . $decimals . ')';
615 * Returns the remainder of the division operation
616 * $expression1 / $expression2.
618 * @param string $expression1
619 * @param string $expression2
623 public function getModExpression($expression1, $expression2)
625 return 'MOD(' . $expression1 . ', ' . $expression2 . ')';
629 * Trim a string, leading/trailing/both and with a given char which defaults to space.
632 * @param integer $pos
633 * @param string $char has to be quoted already
637 public function getTrimExpression($str, $pos = self::TRIM_UNSPECIFIED, $char = false)
640 $trimChar = ($char != false) ? $char . ' FROM ' : '';
643 case self::TRIM_LEADING:
644 $posStr = 'LEADING '.$trimChar;
647 case self::TRIM_TRAILING:
648 $posStr = 'TRAILING '.$trimChar;
651 case self::TRIM_BOTH:
652 $posStr = 'BOTH '.$trimChar;
656 return 'TRIM(' . $posStr . $str . ')';
661 * returns the string $str with proceeding space characters removed
663 * @param string $str literal string or column name
667 public function getRtrimExpression($str)
669 return 'RTRIM(' . $str . ')';
674 * returns the string $str with leading space characters removed
676 * @param string $str literal string or column name
680 public function getLtrimExpression($str)
682 return 'LTRIM(' . $str . ')';
687 * Returns the string $str with all characters changed to
688 * uppercase according to the current character set mapping.
690 * @param string $str literal string or column name
694 public function getUpperExpression($str)
696 return 'UPPER(' . $str . ')';
701 * Returns the string $str with all characters changed to
702 * lowercase according to the current character set mapping.
704 * @param string $str literal string or column name
708 public function getLowerExpression($str)
710 return 'LOWER(' . $str . ')';
714 * returns the position of the first occurrence of substring $substr in string $str
716 * @param string $str literal string
717 * @param string $substr literal string to find
718 * @param integer $startPos position to start at, beginning of string by default
722 public function getLocateExpression($str, $substr, $startPos = false)
724 throw DBALException::notSupported(__METHOD__);
728 * Returns the current system date.
732 public function getNowExpression()
738 * return string to call a function to get a substring inside an SQL statement
740 * Note: Not SQL92, but common functionality.
742 * SQLite only supports the 2 parameter variant of this function
744 * @param string $value an sql string literal or column name/alias
745 * @param integer $from where to start the substring portion
746 * @param integer $length the substring portion length
750 public function getSubstringExpression($value, $from, $length = null)
752 if ($length === null) {
753 return 'SUBSTRING(' . $value . ' FROM ' . $from . ')';
756 return 'SUBSTRING(' . $value . ' FROM ' . $from . ' FOR ' . $length . ')';
760 * Returns a series of strings concatinated
762 * concat() accepts an arbitrary number of parameters. Each parameter
763 * must contain an expression
765 * @param string $arg1, $arg2 ... $argN strings that will be concatenated.
769 public function getConcatExpression()
771 return join(' || ' , func_get_args());
775 * Returns the SQL for a logical not.
779 * $q = new Doctrine_Query();
781 * $q->select('*')->from('table')
782 * ->where($e->eq('id', $e->not('null'));
785 * @param string $expression
787 * @return string a logical expression
789 public function getNotExpression($expression)
791 return 'NOT(' . $expression . ')';
795 * Returns the SQL to check if a value is one in a set of
798 * in() accepts an arbitrary number of parameters. The first parameter
799 * must always specify the value that should be matched against. Successive
800 * must contain a logical expression or an array with logical expressions.
801 * These expressions will be matched against the first parameter.
803 * @param string $column the value that should be matched against
804 * @param string|array<string> $values values that will be matched against $column
806 * @return string logical expression
808 public function getInExpression($column, $values)
810 if ( ! is_array($values)) {
811 $values = array($values);
814 // TODO: fix this code: the method does not exist
815 $values = $this->getIdentifiers($values);
817 if (count($values) == 0) {
818 throw new \InvalidArgumentException('Values must not be empty.');
821 return $column . ' IN (' . implode(', ', $values) . ')';
825 * Returns SQL that checks if a expression is null.
827 * @param string $expression the expression that should be compared to null
829 * @return string logical expression
831 public function getIsNullExpression($expression)
833 return $expression . ' IS NULL';
837 * Returns SQL that checks if a expression is not null.
839 * @param string $expression the expression that should be compared to null
841 * @return string logical expression
843 public function getIsNotNullExpression($expression)
845 return $expression . ' IS NOT NULL';
849 * Returns SQL that checks if an expression evaluates to a value between
852 * The parameter $expression is checked if it is between $value1 and $value2.
854 * Note: There is a slight difference in the way BETWEEN works on some databases.
855 * http://www.w3schools.com/sql/sql_between.asp. If you want complete database
856 * independence you should avoid using between().
858 * @param string $expression the value to compare to
859 * @param string $value1 the lower value to compare with
860 * @param string $value2 the higher value to compare with
862 * @return string logical expression
864 public function getBetweenExpression($expression, $value1, $value2)
866 return $expression . ' BETWEEN ' .$value1 . ' AND ' . $value2;
869 public function getAcosExpression($value)
871 return 'ACOS(' . $value . ')';
874 public function getSinExpression($value)
876 return 'SIN(' . $value . ')';
879 public function getPiExpression()
884 public function getCosExpression($value)
886 return 'COS(' . $value . ')';
890 * Calculate the difference in days between the two passed dates.
892 * Computes diff = date1 - date2
894 * @param string $date1
895 * @param string $date2
899 public function getDateDiffExpression($date1, $date2)
901 throw DBALException::notSupported(__METHOD__);
905 * Add the number of given days to a date.
907 * @param string $date
908 * @param integer $days
912 public function getDateAddDaysExpression($date, $days)
914 throw DBALException::notSupported(__METHOD__);
918 * Substract the number of given days to a date.
920 * @param string $date
921 * @param integer $days
925 public function getDateSubDaysExpression($date, $days)
927 throw DBALException::notSupported(__METHOD__);
931 * Add the number of given months to a date.
933 * @param string $date
934 * @param integer $months
938 public function getDateAddMonthExpression($date, $months)
940 throw DBALException::notSupported(__METHOD__);
944 * Substract the number of given months to a date.
946 * @param string $date
947 * @param integer $months
951 public function getDateSubMonthExpression($date, $months)
953 throw DBALException::notSupported(__METHOD__);
957 * Gets SQL bit AND comparison expression
959 * @param string $value1
960 * @param string $value2
964 public function getBitAndComparisonExpression($value1, $value2)
966 return '(' . $value1 . ' & ' . $value2 . ')';
970 * Gets SQL bit OR comparison expression
972 * @param string $value1
973 * @param string $value2
977 public function getBitOrComparisonExpression($value1, $value2)
979 return '(' . $value1 . ' | ' . $value2 . ')';
982 public function getForUpdateSQL()
988 * Honors that some SQL vendors such as MsSql use table hints for locking instead of the ANSI SQL FOR UPDATE specification.
990 * @param string $fromClause
991 * @param integer $lockMode
995 public function appendLockHint($fromClause, $lockMode)
1001 * Get the sql snippet to append to any SELECT statement which locks rows in shared read lock.
1003 * This defaults to the ASNI SQL "FOR UPDATE", which is an exclusive lock (Write). Some database
1004 * vendors allow to lighten this constraint up to be a real read lock.
1008 public function getReadLockSQL()
1010 return $this->getForUpdateSQL();
1014 * Get the SQL snippet to append to any SELECT statement which obtains an exclusive lock on the rows.
1016 * The semantics of this lock mode should equal the SELECT .. FOR UPDATE of the ASNI SQL standard.
1020 public function getWriteLockSQL()
1022 return $this->getForUpdateSQL();
1026 * Get the SQL snippet to drop an existing database
1028 * @param string $database name of the database that should be dropped
1032 public function getDropDatabaseSQL($database)
1034 return 'DROP DATABASE ' . $database;
1040 * @throws \InvalidArgumentException
1042 * @param Table|string $table
1046 public function getDropTableSQL($table)
1050 if ($table instanceof Table) {
1051 $table = $table->getQuotedName($this);
1052 } else if(!is_string($table)) {
1053 throw new \InvalidArgumentException('getDropTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
1056 if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaDropTable)) {
1057 $eventArgs = new SchemaDropTableEventArgs($tableArg, $this);
1058 $this->_eventManager->dispatchEvent(Events::onSchemaDropTable, $eventArgs);
1060 if ($eventArgs->isDefaultPrevented()) {
1061 return $eventArgs->getSql();
1065 return 'DROP TABLE ' . $table;
1069 * Get SQL to safely drop a temporary table WITHOUT implicitly committing an open transaction.
1071 * @param Table|string $table
1075 public function getDropTemporaryTableSQL($table)
1077 return $this->getDropTableSQL($table);
1081 * Drop index from a table
1083 * @param Index|string $name
1084 * @param string|Table $table
1088 public function getDropIndexSQL($index, $table = null)
1090 if ($index instanceof Index) {
1091 $index = $index->getQuotedName($this);
1092 } else if(!is_string($index)) {
1093 throw new \InvalidArgumentException('AbstractPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
1096 return 'DROP INDEX ' . $index;
1100 * Get drop constraint sql
1102 * @param \Doctrine\DBAL\Schema\Constraint $constraint
1103 * @param string|Table $table
1107 public function getDropConstraintSQL($constraint, $table)
1109 if ($constraint instanceof Constraint) {
1110 $constraint = $constraint->getQuotedName($this);
1113 if ($table instanceof Table) {
1114 $table = $table->getQuotedName($this);
1117 return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $constraint;
1121 * @param ForeignKeyConstraint|string $foreignKey
1122 * @param Table|string $table
1126 public function getDropForeignKeySQL($foreignKey, $table)
1128 if ($foreignKey instanceof ForeignKeyConstraint) {
1129 $foreignKey = $foreignKey->getQuotedName($this);
1132 if ($table instanceof Table) {
1133 $table = $table->getQuotedName($this);
1136 return 'ALTER TABLE ' . $table . ' DROP FOREIGN KEY ' . $foreignKey;
1140 * Gets the SQL statement(s) to create a table with the specified name, columns and constraints
1143 * @param string $table The name of the table.
1144 * @param integer $createFlags
1146 * @return array The sequence of SQL statements.
1148 public function getCreateTableSQL(Table $table, $createFlags = self::CREATE_INDEXES)
1150 if ( ! is_int($createFlags)) {
1151 throw new \InvalidArgumentException("Second argument of AbstractPlatform::getCreateTableSQL() has to be integer.");
1154 if (count($table->getColumns()) === 0) {
1155 throw DBALException::noColumnsSpecifiedForTable($table->getName());
1158 $tableName = $table->getQuotedName($this);
1159 $options = $table->getOptions();
1160 $options['uniqueConstraints'] = array();
1161 $options['indexes'] = array();
1162 $options['primary'] = array();
1164 if (($createFlags&self::CREATE_INDEXES) > 0) {
1165 foreach ($table->getIndexes() as $index) {
1166 /* @var $index Index */
1167 if ($index->isPrimary()) {
1168 $options['primary'] = $index->getColumns();
1169 $options['primary_index'] = $index;
1171 $options['indexes'][$index->getName()] = $index;
1176 $columnSql = array();
1179 foreach ($table->getColumns() as $column) {
1180 /* @var \Doctrine\DBAL\Schema\Column $column */
1182 if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTableColumn)) {
1183 $eventArgs = new SchemaCreateTableColumnEventArgs($column, $table, $this);
1184 $this->_eventManager->dispatchEvent(Events::onSchemaCreateTableColumn, $eventArgs);
1186 $columnSql = array_merge($columnSql, $eventArgs->getSql());
1188 if ($eventArgs->isDefaultPrevented()) {
1193 $columnData = array();
1194 $columnData['name'] = $column->getQuotedName($this);
1195 $columnData['type'] = $column->getType();
1196 $columnData['length'] = $column->getLength();
1197 $columnData['notnull'] = $column->getNotNull();
1198 $columnData['fixed'] = $column->getFixed();
1199 $columnData['unique'] = false; // TODO: what do we do about this?
1200 $columnData['version'] = $column->hasPlatformOption("version") ? $column->getPlatformOption('version') : false;
1202 if (strtolower($columnData['type']) == "string" && $columnData['length'] === null) {
1203 $columnData['length'] = 255;
1206 $columnData['unsigned'] = $column->getUnsigned();
1207 $columnData['precision'] = $column->getPrecision();
1208 $columnData['scale'] = $column->getScale();
1209 $columnData['default'] = $column->getDefault();
1210 $columnData['columnDefinition'] = $column->getColumnDefinition();
1211 $columnData['autoincrement'] = $column->getAutoincrement();
1212 $columnData['comment'] = $this->getColumnComment($column);
1214 if (in_array($column->getName(), $options['primary'])) {
1215 $columnData['primary'] = true;
1218 $columns[$columnData['name']] = $columnData;
1221 if (($createFlags&self::CREATE_FOREIGNKEYS) > 0) {
1222 $options['foreignKeys'] = array();
1223 foreach ($table->getForeignKeys() as $fkConstraint) {
1224 $options['foreignKeys'][] = $fkConstraint;
1228 if (null !== $this->_eventManager && $this->_eventManager->hasListeners(Events::onSchemaCreateTable)) {
1229 $eventArgs = new SchemaCreateTableEventArgs($table, $columns, $options, $this);
1230 $this->_eventManager->dispatchEvent(Events::onSchemaCreateTable, $eventArgs);
1232 if ($eventArgs->isDefaultPrevented()) {
1233 return array_merge($eventArgs->getSql(), $columnSql);
1237 $sql = $this->_getCreateTableSQL($tableName, $columns, $options);
1238 if ($this->supportsCommentOnStatement()) {
1239 foreach ($table->getColumns() as $column) {
1240 if ($this->getColumnComment($column)) {
1241 $sql[] = $this->getCommentOnColumnSQL($tableName, $column->getName(), $this->getColumnComment($column));
1246 return array_merge($sql, $columnSql);
1249 public function getCommentOnColumnSQL($tableName, $columnName, $comment)
1251 return "COMMENT ON COLUMN " . $tableName . "." . $columnName . " IS '" . $comment . "'";
1255 * Gets the SQL used to create a table.
1257 * @param string $tableName
1258 * @param array $columns
1259 * @param array $options
1263 protected function _getCreateTableSQL($tableName, array $columns, array $options = array())
1265 $columnListSql = $this->getColumnDeclarationListSQL($columns);
1267 if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
1268 foreach ($options['uniqueConstraints'] as $name => $definition) {
1269 $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
1273 if (isset($options['primary']) && ! empty($options['primary'])) {
1274 $columnListSql .= ', PRIMARY KEY(' . implode(', ', array_unique(array_values($options['primary']))) . ')';
1277 if (isset($options['indexes']) && ! empty($options['indexes'])) {
1278 foreach($options['indexes'] as $index => $definition) {
1279 $columnListSql .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
1283 $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql;
1285 $check = $this->getCheckDeclarationSQL($columns);
1286 if ( ! empty($check)) {
1287 $query .= ', ' . $check;
1293 if (isset($options['foreignKeys'])) {
1294 foreach ((array) $options['foreignKeys'] as $definition) {
1295 $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
1302 public function getCreateTemporaryTableSnippetSQL()
1304 return "CREATE TEMPORARY TABLE";
1308 * Gets the SQL to create a sequence on this platform.
1310 * @param \Doctrine\DBAL\Schema\Sequence $sequence
1314 * @throws DBALException
1316 public function getCreateSequenceSQL(Sequence $sequence)
1318 throw DBALException::notSupported(__METHOD__);
1322 * Gets the SQL statement to change a sequence on this platform.
1324 * @param \Doctrine\DBAL\Schema\Sequence $sequence
1328 public function getAlterSequenceSQL(Sequence $sequence)
1330 throw DBALException::notSupported(__METHOD__);
1334 * Gets the SQL to create a constraint on a table on this platform.
1336 * @param \Doctrine\DBAL\Schema\Constraint $constraint
1337 * @param string|Table $table
1341 public function getCreateConstraintSQL(Constraint $constraint, $table)
1343 if ($table instanceof Table) {
1344 $table = $table->getQuotedName($this);
1347 $query = 'ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $constraint->getQuotedName($this);
1350 foreach ($constraint->getColumns() as $column) {
1351 $columns[] = $column;
1353 $columnList = '('. implode(', ', $columns) . ')';
1355 $referencesClause = '';
1356 if ($constraint instanceof Index) {
1357 if($constraint->isPrimary()) {
1358 $query .= ' PRIMARY KEY';
1359 } elseif ($constraint->isUnique()) {
1360 $query .= ' UNIQUE';
1362 throw new \InvalidArgumentException(
1363 'Can only create primary or unique constraints, no common indexes with getCreateConstraintSQL().'
1366 } else if ($constraint instanceof ForeignKeyConstraint) {
1367 $query .= ' FOREIGN KEY';
1369 $foreignColumns = array();
1370 foreach ($constraint->getForeignColumns() as $column) {
1371 $foreignColumns[] = $column;
1374 $referencesClause = ' REFERENCES '.$constraint->getForeignTableName(). ' ('.implode(', ', $foreignColumns).')';
1376 $query .= ' '.$columnList.$referencesClause;
1382 * Gets the SQL to create an index on a table on this platform.
1384 * @param Index $index
1385 * @param string|Table $table name of the table on which the index is to be created
1389 public function getCreateIndexSQL(Index $index, $table)
1391 if ($table instanceof Table) {
1392 $table = $table->getQuotedName($this);
1394 $name = $index->getQuotedName($this);
1395 $columns = $index->getColumns();
1397 if (count($columns) == 0) {
1398 throw new \InvalidArgumentException("Incomplete definition. 'columns' required.");
1401 if ($index->isPrimary()) {
1402 return $this->getCreatePrimaryKeySQL($index, $table);
1405 $query = 'CREATE ' . $this->getCreateIndexSQLFlags($index) . 'INDEX ' . $name . ' ON ' . $table;
1406 $query .= ' (' . $this->getIndexFieldDeclarationListSQL($columns) . ')';
1412 * Adds additional flags for index generation
1414 * @param Index $index
1418 protected function getCreateIndexSQLFlags(Index $index)
1420 return $index->isUnique() ? 'UNIQUE ' : '';
1424 * Get SQL to create an unnamed primary key constraint.
1426 * @param Index $index
1427 * @param string|Table $table
1431 public function getCreatePrimaryKeySQL(Index $index, $table)
1433 return 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . $this->getIndexFieldDeclarationListSQL($index->getColumns()) . ')';
1437 * Quotes a string so that it can be safely used as a table or column name,
1438 * even if it is a reserved word of the platform. This also detects identifier
1439 * chains separated by dot and quotes them independently.
1441 * NOTE: Just because you CAN use quoted identifiers doesn't mean
1442 * you SHOULD use them. In general, they end up causing way more
1443 * problems than they solve.
1445 * @param string $str identifier name to be quoted
1447 * @return string quoted identifier string
1449 public function quoteIdentifier($str)
1451 if (strpos($str, ".") !== false) {
1452 $parts = array_map(array($this, "quoteIdentifier"), explode(".", $str));
1454 return implode(".", $parts);
1457 return $this->quoteSingleIdentifier($str);
1461 * Quote a single identifier (no dot chain separation)
1463 * @param string $str
1467 public function quoteSingleIdentifier($str)
1469 $c = $this->getIdentifierQuoteCharacter();
1471 return $c . str_replace($c, $c.$c, $str) . $c;
1475 * Create a new foreign key
1477 * @param ForeignKeyConstraint $foreignKey ForeignKey instance
1478 * @param string|Table $table name of the table on which the foreign key is to be created
1482 public function getCreateForeignKeySQL(ForeignKeyConstraint $foreignKey, $table)
1484 if ($table instanceof Table) {
1485 $table = $table->getQuotedName($this);
1488 $query = 'ALTER TABLE ' . $table . ' ADD ' . $this->getForeignKeyDeclarationSQL($foreignKey);
1494 * Gets the sql statements for altering an existing table.
1496 * The method returns an array of sql statements, since some platforms need several statements.
1498 * @param TableDiff $diff
1502 public function getAlterTableSQL(TableDiff $diff)
1504 throw DBALException::notSupported(__METHOD__);
1508 * @param Column $column
1509 * @param TableDiff $diff
1510 * @param array $columnSql
1514 protected function onSchemaAlterTableAddColumn(Column $column, TableDiff $diff, &$columnSql)
1516 if (null === $this->_eventManager) {
1520 if ( ! $this->_eventManager->hasListeners(Events::onSchemaAlterTableAddColumn)) {
1524 $eventArgs = new SchemaAlterTableAddColumnEventArgs($column, $diff, $this);
1525 $this->_eventManager->dispatchEvent(Events::onSchemaAlterTableAddColumn, $eventArgs);
1527 $columnSql = array_merge($columnSql, $eventArgs->getSql());
1529 return $eventArgs->isDefaultPrevented();
1533 * @param Column $column
1534 * @param TableDiff $diff
1535 * @param array $columnSql
1539 protected function onSchemaAlterTableRemoveColumn(Column $column, TableDiff $diff, &$columnSql)
1541 if (null === $this->_eventManager) {
1545 if ( ! $this->_eventManager->hasListeners(Events::onSchemaAlterTableRemoveColumn)) {
1549 $eventArgs = new SchemaAlterTableRemoveColumnEventArgs($column, $diff, $this);
1550 $this->_eventManager->dispatchEvent(Events::onSchemaAlterTableRemoveColumn, $eventArgs);
1552 $columnSql = array_merge($columnSql, $eventArgs->getSql());
1554 return $eventArgs->isDefaultPrevented();
1558 * @param ColumnDiff $columnDiff
1559 * @param TableDiff $diff
1560 * @param array $columnSql
1564 protected function onSchemaAlterTableChangeColumn(ColumnDiff $columnDiff, TableDiff $diff, &$columnSql)
1566 if (null === $this->_eventManager) {
1570 if ( ! $this->_eventManager->hasListeners(Events::onSchemaAlterTableChangeColumn)) {
1574 $eventArgs = new SchemaAlterTableChangeColumnEventArgs($columnDiff, $diff, $this);
1575 $this->_eventManager->dispatchEvent(Events::onSchemaAlterTableChangeColumn, $eventArgs);
1577 $columnSql = array_merge($columnSql, $eventArgs->getSql());
1579 return $eventArgs->isDefaultPrevented();
1583 * @param string $oldColumnName
1584 * @param Column $column
1585 * @param TableDiff $diff
1586 * @param array $columnSql
1590 protected function onSchemaAlterTableRenameColumn($oldColumnName, Column $column, TableDiff $diff, &$columnSql)
1592 if (null === $this->_eventManager) {
1596 if ( ! $this->_eventManager->hasListeners(Events::onSchemaAlterTableRenameColumn)) {
1600 $eventArgs = new SchemaAlterTableRenameColumnEventArgs($oldColumnName, $column, $diff, $this);
1601 $this->_eventManager->dispatchEvent(Events::onSchemaAlterTableRenameColumn, $eventArgs);
1603 $columnSql = array_merge($columnSql, $eventArgs->getSql());
1605 return $eventArgs->isDefaultPrevented();
1609 * @param TableDiff $diff
1614 protected function onSchemaAlterTable(TableDiff $diff, &$sql)
1616 if (null === $this->_eventManager) {
1620 if ( ! $this->_eventManager->hasListeners(Events::onSchemaAlterTable)) {
1624 $eventArgs = new SchemaAlterTableEventArgs($diff, $this);
1625 $this->_eventManager->dispatchEvent(Events::onSchemaAlterTable, $eventArgs);
1627 $sql = array_merge($sql, $eventArgs->getSql());
1629 return $eventArgs->isDefaultPrevented();
1632 protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff)
1634 $tableName = $diff->name;
1637 if ($this->supportsForeignKeyConstraints()) {
1638 foreach ($diff->removedForeignKeys as $foreignKey) {
1639 $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
1641 foreach ($diff->changedForeignKeys as $foreignKey) {
1642 $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
1646 foreach ($diff->removedIndexes as $index) {
1647 $sql[] = $this->getDropIndexSQL($index, $tableName);
1649 foreach ($diff->changedIndexes as $index) {
1650 $sql[] = $this->getDropIndexSQL($index, $tableName);
1656 protected function getPostAlterTableIndexForeignKeySQL(TableDiff $diff)
1658 $tableName = false !== $diff->newName ? $diff->newName : $diff->name;
1661 if ($this->supportsForeignKeyConstraints()) {
1662 foreach ($diff->addedForeignKeys as $foreignKey) {
1663 $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
1665 foreach ($diff->changedForeignKeys as $foreignKey) {
1666 $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
1670 foreach ($diff->addedIndexes as $index) {
1671 $sql[] = $this->getCreateIndexSQL($index, $tableName);
1673 foreach ($diff->changedIndexes as $index) {
1674 $sql[] = $this->getCreateIndexSQL($index, $tableName);
1681 * Common code for alter table statement generation that updates the changed Index and Foreign Key definitions.
1683 * @param TableDiff $diff
1687 protected function _getAlterTableIndexForeignKeySQL(TableDiff $diff)
1689 return array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $this->getPostAlterTableIndexForeignKeySQL($diff));
1693 * Get declaration of a number of fields in bulk
1695 * @param array $fields a multidimensional associative array.
1696 * The first dimension determines the field name, while the second
1697 * dimension is keyed with the name of the properties
1698 * of the field being declared as array indexes. Currently, the types
1699 * of supported field properties are as follows:
1702 * Integer value that determines the maximum length of the text
1703 * field. If this argument is missing the field should be
1704 * declared to have the longest length allowed by the DBMS.
1707 * Text value to be used as default for this field.
1710 * Boolean flag that indicates whether this field is constrained
1711 * to not be set to null.
1713 * Text value with the default CHARACTER SET for this field.
1715 * Text value with the default COLLATION for this field.
1721 public function getColumnDeclarationListSQL(array $fields)
1723 $queryFields = array();
1725 foreach ($fields as $fieldName => $field) {
1726 $queryFields[] = $this->getColumnDeclarationSQL($fieldName, $field);
1729 return implode(', ', $queryFields);
1733 * Obtain DBMS specific SQL code portion needed to declare a generic type
1734 * field to be used in statements like CREATE TABLE.
1736 * @param string $name name the field to be declared.
1737 * @param array $field associative array with the name of the properties
1738 * of the field being declared as array indexes. Currently, the types
1739 * of supported field properties are as follows:
1742 * Integer value that determines the maximum length of the text
1743 * field. If this argument is missing the field should be
1744 * declared to have the longest length allowed by the DBMS.
1747 * Text value to be used as default for this field.
1750 * Boolean flag that indicates whether this field is constrained
1751 * to not be set to null.
1753 * Text value with the default CHARACTER SET for this field.
1755 * Text value with the default COLLATION for this field.
1759 * column check constraint
1761 * a string that defines the complete column
1763 * @return string DBMS specific SQL code portion that should be used to declare the column.
1765 public function getColumnDeclarationSQL($name, array $field)
1767 if (isset($field['columnDefinition'])) {
1768 $columnDef = $this->getCustomTypeDeclarationSQL($field);
1770 $default = $this->getDefaultValueDeclarationSQL($field);
1772 $charset = (isset($field['charset']) && $field['charset']) ?
1773 ' ' . $this->getColumnCharsetDeclarationSQL($field['charset']) : '';
1775 $collation = (isset($field['collation']) && $field['collation']) ?
1776 ' ' . $this->getColumnCollationDeclarationSQL($field['collation']) : '';
1778 $notnull = (isset($field['notnull']) && $field['notnull']) ? ' NOT NULL' : '';
1780 $unique = (isset($field['unique']) && $field['unique']) ?
1781 ' ' . $this->getUniqueFieldDeclarationSQL() : '';
1783 $check = (isset($field['check']) && $field['check']) ?
1784 ' ' . $field['check'] : '';
1786 $typeDecl = $field['type']->getSqlDeclaration($field, $this);
1787 $columnDef = $typeDecl . $charset . $default . $notnull . $unique . $check . $collation;
1790 if ($this->supportsInlineColumnComments() && isset($field['comment']) && $field['comment']) {
1791 $columnDef .= " COMMENT '" . $field['comment'] . "'";
1794 return $name . ' ' . $columnDef;
1798 * Gets the SQL snippet that declares a floating point column of arbitrary precision.
1800 * @param array $columnDef
1804 public function getDecimalTypeDeclarationSQL(array $columnDef)
1806 $columnDef['precision'] = ( ! isset($columnDef['precision']) || empty($columnDef['precision']))
1807 ? 10 : $columnDef['precision'];
1808 $columnDef['scale'] = ( ! isset($columnDef['scale']) || empty($columnDef['scale']))
1809 ? 0 : $columnDef['scale'];
1811 return 'NUMERIC(' . $columnDef['precision'] . ', ' . $columnDef['scale'] . ')';
1815 * Obtain DBMS specific SQL code portion needed to set a default value
1816 * declaration to be used in statements like CREATE TABLE.
1818 * @param array $field field definition array
1820 * @return string DBMS specific SQL code portion needed to set a default value
1822 public function getDefaultValueDeclarationSQL($field)
1824 $default = empty($field['notnull']) ? ' DEFAULT NULL' : '';
1826 if (isset($field['default'])) {
1827 $default = " DEFAULT '".$field['default']."'";
1828 if (isset($field['type'])) {
1829 if (in_array((string)$field['type'], array("Integer", "BigInteger", "SmallInteger"))) {
1830 $default = " DEFAULT ".$field['default'];
1831 } else if ((string)$field['type'] == 'DateTime' && $field['default'] == $this->getCurrentTimestampSQL()) {
1832 $default = " DEFAULT ".$this->getCurrentTimestampSQL();
1833 } else if ((string) $field['type'] == 'Boolean') {
1834 $default = " DEFAULT '" . $this->convertBooleans($field['default']) . "'";
1842 * Obtain DBMS specific SQL code portion needed to set a CHECK constraint
1843 * declaration to be used in statements like CREATE TABLE.
1845 * @param array $definition check definition
1847 * @return string DBMS specific SQL code portion needed to set a CHECK constraint
1849 public function getCheckDeclarationSQL(array $definition)
1851 $constraints = array();
1852 foreach ($definition as $field => $def) {
1853 if (is_string($def)) {
1854 $constraints[] = 'CHECK (' . $def . ')';
1856 if (isset($def['min'])) {
1857 $constraints[] = 'CHECK (' . $field . ' >= ' . $def['min'] . ')';
1860 if (isset($def['max'])) {
1861 $constraints[] = 'CHECK (' . $field . ' <= ' . $def['max'] . ')';
1866 return implode(', ', $constraints);
1870 * Obtain DBMS specific SQL code portion needed to set a unique
1871 * constraint declaration to be used in statements like CREATE TABLE.
1873 * @param string $name name of the unique constraint
1874 * @param Index $index index definition
1876 * @return string DBMS specific SQL code portion needed
1877 * to set a constraint
1879 public function getUniqueConstraintDeclarationSQL($name, Index $index)
1881 if (count($index->getColumns()) === 0) {
1882 throw new \InvalidArgumentException("Incomplete definition. 'columns' required.");
1885 return 'CONSTRAINT ' . $name . ' UNIQUE ('
1886 . $this->getIndexFieldDeclarationListSQL($index->getColumns())
1891 * Obtain DBMS specific SQL code portion needed to set an index
1892 * declaration to be used in statements like CREATE TABLE.
1894 * @param string $name name of the index
1895 * @param Index $index index definition
1897 * @return string DBMS specific SQL code portion needed to set an index
1899 public function getIndexDeclarationSQL($name, Index $index)
1903 if ($index->isUnique()) {
1907 if (count($index->getColumns()) === 0) {
1908 throw new \InvalidArgumentException("Incomplete definition. 'columns' required.");
1911 return $type . 'INDEX ' . $name . ' ('
1912 . $this->getIndexFieldDeclarationListSQL($index->getColumns())
1917 * getCustomTypeDeclarationSql
1918 * Obtail SQL code portion needed to create a custom column,
1919 * e.g. when a field has the "columnDefinition" keyword.
1920 * Only "AUTOINCREMENT" and "PRIMARY KEY" are added if appropriate.
1922 * @param array $columnDef
1926 public function getCustomTypeDeclarationSQL(array $columnDef)
1928 return $columnDef['columnDefinition'];
1932 * getIndexFieldDeclarationList
1933 * Obtain DBMS specific SQL code portion needed to set an index
1934 * declaration to be used in statements like CREATE TABLE.
1936 * @param array $fields
1940 public function getIndexFieldDeclarationListSQL(array $fields)
1944 foreach ($fields as $field => $definition) {
1945 if (is_array($definition)) {
1948 $ret[] = $definition;
1952 return implode(', ', $ret);
1956 * A method to return the required SQL string that fits between CREATE ... TABLE
1957 * to create the table as a temporary table.
1959 * Should be overridden in driver classes to return the correct string for the
1960 * specific database type.
1962 * The default is to return the string "TEMPORARY" - this will result in a
1963 * SQL error for any database that does not support temporary tables, or that
1964 * requires a different SQL command from "CREATE TEMPORARY TABLE".
1966 * @return string The string required to be placed between "CREATE" and "TABLE"
1967 * to generate a temporary table, if possible.
1969 public function getTemporaryTableSQL()
1975 * Some vendors require temporary table names to be qualified specially.
1977 * @param string $tableName
1981 public function getTemporaryTableName($tableName)
1987 * Get sql query to show a list of database.
1991 public function getShowDatabasesSQL()
1993 throw DBALException::notSupported(__METHOD__);
1997 * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
1998 * of a field declaration to be used in statements like CREATE TABLE.
2000 * @param \Doctrine\DBAL\Schema\ForeignKeyConstraint $foreignKey
2002 * @return string DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
2003 * of a field declaration.
2005 public function getForeignKeyDeclarationSQL(ForeignKeyConstraint $foreignKey)
2007 $sql = $this->getForeignKeyBaseDeclarationSQL($foreignKey);
2008 $sql .= $this->getAdvancedForeignKeyOptionsSQL($foreignKey);
2014 * Return the FOREIGN KEY query section dealing with non-standard options
2015 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
2017 * @param ForeignKeyConstraint $foreignKey foreign key definition
2021 public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
2024 if ($this->supportsForeignKeyOnUpdate() && $foreignKey->hasOption('onUpdate')) {
2025 $query .= ' ON UPDATE ' . $this->getForeignKeyReferentialActionSQL($foreignKey->getOption('onUpdate'));
2027 if ($foreignKey->hasOption('onDelete')) {
2028 $query .= ' ON DELETE ' . $this->getForeignKeyReferentialActionSQL($foreignKey->getOption('onDelete'));
2034 * returns given referential action in uppercase if valid, otherwise throws
2037 * @throws \InvalidArgumentException if unknown referential action given
2039 * @param string $action foreign key referential action
2043 public function getForeignKeyReferentialActionSQL($action)
2045 $upper = strtoupper($action);
2054 throw new \InvalidArgumentException('Invalid foreign key action: ' . $upper);
2059 * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint
2060 * of a field declaration to be used in statements like CREATE TABLE.
2062 * @param ForeignKeyConstraint $foreignKey
2066 public function getForeignKeyBaseDeclarationSQL(ForeignKeyConstraint $foreignKey)
2069 if (strlen($foreignKey->getName())) {
2070 $sql .= 'CONSTRAINT ' . $foreignKey->getQuotedName($this) . ' ';
2072 $sql .= 'FOREIGN KEY (';
2074 if (count($foreignKey->getLocalColumns()) === 0) {
2075 throw new \InvalidArgumentException("Incomplete definition. 'local' required.");
2077 if (count($foreignKey->getForeignColumns()) === 0) {
2078 throw new \InvalidArgumentException("Incomplete definition. 'foreign' required.");
2080 if (strlen($foreignKey->getForeignTableName()) === 0) {
2081 throw new \InvalidArgumentException("Incomplete definition. 'foreignTable' required.");
2084 $sql .= implode(', ', $foreignKey->getLocalColumns())
2086 . $foreignKey->getQuotedForeignTableName($this) . ' ('
2087 . implode(', ', $foreignKey->getForeignColumns()) . ')';
2093 * Obtain DBMS specific SQL code portion needed to set the UNIQUE constraint
2094 * of a field declaration to be used in statements like CREATE TABLE.
2096 * @return string DBMS specific SQL code portion needed to set the UNIQUE constraint
2097 * of a field declaration.
2099 public function getUniqueFieldDeclarationSQL()
2105 * Obtain DBMS specific SQL code portion needed to set the CHARACTER SET
2106 * of a field declaration to be used in statements like CREATE TABLE.
2108 * @param string $charset name of the charset
2110 * @return string DBMS specific SQL code portion needed to set the CHARACTER SET
2111 * of a field declaration.
2113 public function getColumnCharsetDeclarationSQL($charset)
2119 * Obtain DBMS specific SQL code portion needed to set the COLLATION
2120 * of a field declaration to be used in statements like CREATE TABLE.
2122 * @param string $collation name of the collation
2124 * @return string DBMS specific SQL code portion needed to set the COLLATION
2125 * of a field declaration.
2127 public function getColumnCollationDeclarationSQL($collation)
2133 * Whether the platform prefers sequences for ID generation.
2134 * Subclasses should override this method to return TRUE if they prefer sequences.
2138 public function prefersSequences()
2144 * Whether the platform prefers identity columns (eg. autoincrement) for ID generation.
2145 * Subclasses should override this method to return TRUE if they prefer identity columns.
2149 public function prefersIdentityColumns()
2155 * Some platforms need the boolean values to be converted.
2157 * The default conversion in this implementation converts to integers (false => 0, true => 1).
2159 * @param mixed $item
2163 public function convertBooleans($item)
2165 if (is_array($item)) {
2166 foreach ($item as $k => $value) {
2167 if (is_bool($value)) {
2168 $item[$k] = (int) $value;
2171 } else if (is_bool($item)) {
2172 $item = (int) $item;
2179 * Gets the SQL specific for the platform to get the current date.
2183 public function getCurrentDateSQL()
2185 return 'CURRENT_DATE';
2189 * Gets the SQL specific for the platform to get the current time.
2193 public function getCurrentTimeSQL()
2195 return 'CURRENT_TIME';
2199 * Gets the SQL specific for the platform to get the current timestamp
2203 public function getCurrentTimestampSQL()
2205 return 'CURRENT_TIMESTAMP';
2209 * Get sql for transaction isolation level Connection constant
2211 * @param integer $level
2215 protected function _getTransactionIsolationLevelSQL($level)
2218 case Connection::TRANSACTION_READ_UNCOMMITTED:
2219 return 'READ UNCOMMITTED';
2220 case Connection::TRANSACTION_READ_COMMITTED:
2221 return 'READ COMMITTED';
2222 case Connection::TRANSACTION_REPEATABLE_READ:
2223 return 'REPEATABLE READ';
2224 case Connection::TRANSACTION_SERIALIZABLE:
2225 return 'SERIALIZABLE';
2227 throw new \InvalidArgumentException('Invalid isolation level:' . $level);
2231 public function getListDatabasesSQL()
2233 throw DBALException::notSupported(__METHOD__);
2236 public function getListSequencesSQL($database)
2238 throw DBALException::notSupported(__METHOD__);
2241 public function getListTableConstraintsSQL($table)
2243 throw DBALException::notSupported(__METHOD__);
2246 public function getListTableColumnsSQL($table, $database = null)
2248 throw DBALException::notSupported(__METHOD__);
2251 public function getListTablesSQL()
2253 throw DBALException::notSupported(__METHOD__);
2256 public function getListUsersSQL()
2258 throw DBALException::notSupported(__METHOD__);
2262 * Get the SQL to list all views of a database or user.
2264 * @param string $database
2268 public function getListViewsSQL($database)
2270 throw DBALException::notSupported(__METHOD__);
2274 * Get the list of indexes for the current database.
2276 * The current database parameter is optional but will always be passed
2277 * when using the SchemaManager API and is the database the given table is in.
2279 * Attention: Some platforms only support currentDatabase when they
2280 * are connected with that database. Cross-database information schema
2281 * requests may be impossible.
2283 * @param string $table
2284 * @param string $currentDatabase
2288 public function getListTableIndexesSQL($table, $currentDatabase = null)
2290 throw DBALException::notSupported(__METHOD__);
2293 public function getListTableForeignKeysSQL($table)
2295 throw DBALException::notSupported(__METHOD__);
2298 public function getCreateViewSQL($name, $sql)
2300 throw DBALException::notSupported(__METHOD__);
2303 public function getDropViewSQL($name)
2305 throw DBALException::notSupported(__METHOD__);
2309 * Get the SQL snippet to drop an existing sequence
2311 * @param \Doctrine\DBAL\Schema\Sequence $sequence
2315 public function getDropSequenceSQL($sequence)
2317 throw DBALException::notSupported(__METHOD__);
2320 public function getSequenceNextValSQL($sequenceName)
2322 throw DBALException::notSupported(__METHOD__);
2326 * create a new database
2328 * @param string $database name of the database that should be created
2332 public function getCreateDatabaseSQL($database)
2334 throw DBALException::notSupported(__METHOD__);
2338 * Get sql to set the transaction isolation level
2340 * @param integer $level
2344 public function getSetTransactionIsolationSQL($level)
2346 throw DBALException::notSupported(__METHOD__);
2350 * Obtain DBMS specific SQL to be used to create datetime fields in
2351 * statements like CREATE TABLE
2353 * @param array $fieldDeclaration
2357 public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
2359 throw DBALException::notSupported(__METHOD__);
2363 * Obtain DBMS specific SQL to be used to create datetime with timezone offset fields.
2365 * @param array $fieldDeclaration
2369 public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration)
2371 return $this->getDateTimeTypeDeclarationSQL($fieldDeclaration);
2376 * Obtain DBMS specific SQL to be used to create date fields in statements
2377 * like CREATE TABLE.
2379 * @param array $fieldDeclaration
2383 public function getDateTypeDeclarationSQL(array $fieldDeclaration)
2385 throw DBALException::notSupported(__METHOD__);
2389 * Obtain DBMS specific SQL to be used to create time fields in statements
2390 * like CREATE TABLE.
2392 * @param array $fieldDeclaration
2396 public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
2398 throw DBALException::notSupported(__METHOD__);
2401 public function getFloatDeclarationSQL(array $fieldDeclaration)
2403 return 'DOUBLE PRECISION';
2407 * Gets the default transaction isolation level of the platform.
2409 * @return integer The default isolation level.
2411 * @see Doctrine\DBAL\Connection\TRANSACTION_* constants.
2413 public function getDefaultTransactionIsolationLevel()
2415 return Connection::TRANSACTION_READ_COMMITTED;
2418 /* supports*() methods */
2421 * Whether the platform supports sequences.
2425 public function supportsSequences()
2431 * Whether the platform supports identity columns.
2432 * Identity columns are columns that recieve an auto-generated value from the
2433 * database on insert of a row.
2437 public function supportsIdentityColumns()
2443 * Whether the platform supports indexes.
2447 public function supportsIndexes()
2453 * Whether the platform supports altering tables.
2457 public function supportsAlterTable()
2463 * Whether the platform supports transactions.
2467 public function supportsTransactions()
2473 * Whether the platform supports savepoints.
2477 public function supportsSavepoints()
2483 * Whether the platform supports releasing savepoints.
2487 public function supportsReleaseSavepoints()
2489 return $this->supportsSavepoints();
2493 * Whether the platform supports primary key constraints.
2497 public function supportsPrimaryConstraints()
2503 * Does the platform supports foreign key constraints?
2507 public function supportsForeignKeyConstraints()
2513 * Does this platform supports onUpdate in foreign key constraints?
2517 public function supportsForeignKeyOnUpdate()
2519 return ($this->supportsForeignKeyConstraints() && true);
2523 * Whether the platform supports database schemas.
2527 public function supportsSchemas()
2533 * Can this platform emulate schemas?
2535 * Platforms that either support or emulate schemas don't automatically
2536 * filter a schema for the namespaced elements in {@link
2537 * AbstractManager#createSchema}.
2541 public function canEmulateSchemas()
2547 * Some databases don't allow to create and drop databases at all or only with certain tools.
2551 public function supportsCreateDropDatabase()
2557 * Whether the platform supports getting the affected rows of a recent
2558 * update/delete type query.
2562 public function supportsGettingAffectedRows()
2568 * Does this plaform support to add inline column comments as postfix.
2572 public function supportsInlineColumnComments()
2578 * Does this platform support the propriortary synatx "COMMENT ON asset"
2582 public function supportsCommentOnStatement()
2587 public function getIdentityColumnNullInsertSQL()
2593 * Does this platform views ?
2597 public function supportsViews()
2603 * Gets the format string, as accepted by the date() function, that describes
2604 * the format of a stored datetime value of this platform.
2606 * @return string The format string.
2608 public function getDateTimeFormatString()
2610 return 'Y-m-d H:i:s';
2614 * Gets the format string, as accepted by the date() function, that describes
2615 * the format of a stored datetime with timezone value of this platform.
2617 * @return string The format string.
2619 public function getDateTimeTzFormatString()
2621 return 'Y-m-d H:i:s';
2625 * Gets the format string, as accepted by the date() function, that describes
2626 * the format of a stored date value of this platform.
2628 * @return string The format string.
2630 public function getDateFormatString()
2636 * Gets the format string, as accepted by the date() function, that describes
2637 * the format of a stored time value of this platform.
2639 * @return string The format string.
2641 public function getTimeFormatString()
2647 * Modify limit query
2649 * @param string $query
2650 * @param integer $limit
2651 * @param integer $offset
2655 final public function modifyLimitQuery($query, $limit, $offset = null)
2657 if ($limit !== null) {
2658 $limit = (int)$limit;
2661 if ($offset !== null) {
2662 $offset = (int)$offset;
2665 throw new DBALException("LIMIT argument offset=$offset is not valid");
2667 if ($offset > 0 && ! $this->supportsLimitOffset()) {
2668 throw new DBALException(sprintf("Platform %s does not support offset values in limit queries.", $this->getName()));
2672 return $this->doModifyLimitQuery($query, $limit, $offset);
2676 * Adds an driver-specific LIMIT clause to the query
2678 * @param string $query
2679 * @param integer $limit
2680 * @param integer $offset
2684 protected function doModifyLimitQuery($query, $limit, $offset)
2686 if ($limit !== null) {
2687 $query .= ' LIMIT ' . $limit;
2690 if ($offset !== null) {
2691 $query .= ' OFFSET ' . $offset;
2698 * Does the database platform support offsets in modify limit clauses?
2702 public function supportsLimitOffset()
2708 * Gets the character casing of a column in an SQL result set of this platform.
2710 * @param string $column The column name for which to get the correct character casing.
2712 * @return string The column name in the character casing used in SQL result sets.
2714 public function getSQLResultCasing($column)
2720 * Makes any fixes to a name of a schema element (table, sequence, ...) that are required
2721 * by restrictions of the platform, like a maximum length.
2723 * @param string $schemaElementName
2727 public function fixSchemaElementName($schemaElementName)
2729 return $schemaElementName;
2733 * Maximum length of any given databse identifier, like tables or column names.
2737 public function getMaxIdentifierLength()
2743 * Get the insert sql for an empty insert statement
2745 * @param string $tableName
2746 * @param string $identifierColumnName
2748 * @return string $sql
2750 public function getEmptyIdentityInsertSQL($tableName, $identifierColumnName)
2752 return 'INSERT INTO ' . $tableName . ' (' . $identifierColumnName . ') VALUES (null)';
2756 * Generate a Truncate Table SQL statement for a given table.
2758 * Cascade is not supported on many platforms but would optionally cascade the truncate by
2759 * following the foreign keys.
2761 * @param string $tableName
2762 * @param boolean $cascade
2766 public function getTruncateTableSQL($tableName, $cascade = false)
2768 return 'TRUNCATE '.$tableName;
2772 * This is for test reasons, many vendors have special requirements for dummy statements.
2776 public function getDummySelectSQL()
2782 * Generate SQL to create a new savepoint
2784 * @param string $savepoint
2788 public function createSavePoint($savepoint)
2790 return 'SAVEPOINT ' . $savepoint;
2794 * Generate SQL to release a savepoint
2796 * @param string $savepoint
2800 public function releaseSavePoint($savepoint)
2802 return 'RELEASE SAVEPOINT ' . $savepoint;
2806 * Generate SQL to rollback a savepoint
2808 * @param string $savepoint
2812 public function rollbackSavePoint($savepoint)
2814 return 'ROLLBACK TO SAVEPOINT ' . $savepoint;
2818 * Return the keyword list instance of this platform.
2820 * Throws exception if no keyword list is specified.
2822 * @throws DBALException
2824 * @return \Doctrine\DBAL\Platforms\Keywords\KeywordList
2826 final public function getReservedKeywordsList()
2828 // Check for an existing instantiation of the keywords class.
2829 if ($this->_keywords) {
2830 return $this->_keywords;
2833 $class = $this->getReservedKeywordsClass();
2834 $keywords = new $class;
2835 if ( ! $keywords instanceof \Doctrine\DBAL\Platforms\Keywords\KeywordList) {
2836 throw DBALException::notSupported(__METHOD__);
2839 // Store the instance so it doesn't need to be generated on every request.
2840 $this->_keywords = $keywords;
2846 * The class name of the reserved keywords list.
2850 protected function getReservedKeywordsClass()
2852 throw DBALException::notSupported(__METHOD__);