public function getSql(Column $column, $table) { if (!$table instanceof Table) { $table = new Identifier($table); } $sql = array(); $normalized = $column->getType()->getNormalizedPostGISColumnOptions($column->getCustomSchemaOptions()); $srid = $normalized['srid']; // PostGIS 1.5 uses -1 for undefined SRID's if ($srid <= 0) { $srid = -1; } $type = strtoupper($normalized['geometry_type']); if ('ZM' === substr($type, -2)) { $dimension = 4; $type = substr($type, 0, -2); } elseif ('M' === substr($type, -1)) { $dimension = 3; } elseif ('Z' === substr($type, -1)) { $dimension = 3; $type = substr($type, 0, -1); } else { $dimension = 2; } // Geometry columns are created by the AddGeometryColumn stored procedure $sql[] = sprintf("SELECT AddGeometryColumn('%s', '%s', %d, '%s', %d)", $table->getName(), $column->getName(), $srid, $type, $dimension); if ($column->getNotnull()) { // Add a NOT NULL constraint to the field $sql[] = sprintf('ALTER TABLE %s ALTER %s SET NOT NULL', $table->getQuotedName($this->platform), $column->getQuotedName($this->platform)); } return $sql; }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $sql = array(); $columnSql = array(); $commentsSQL = array(); $queryParts = array(); foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $columnDef = $column->toArray(); $queryPart = 'ADD COLUMN ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); // Adding non-nullable columns to a table requires a default value to be specified. if (!empty($columnDef['notnull']) && !isset($columnDef['default']) && empty($columnDef['autoincrement'])) { $queryPart .= ' WITH DEFAULT'; } $queryParts[] = $queryPart; $comment = $this->getColumnComment($column); if (null !== $comment && '' !== $comment) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->getName($this)->getQuotedName($this), $column->getQuotedName($this), $comment); } } foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this); } foreach ($diff->changedColumns as $columnDiff) { if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } if ($columnDiff->hasChanged('comment')) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->getName($this)->getQuotedName($this), $columnDiff->column->getQuotedName($this), $this->getColumnComment($columnDiff->column)); if (count($columnDiff->changedProperties) === 1) { continue; } } $this->gatherAlterColumnSQL($diff->fromTable, $columnDiff, $sql, $queryParts); } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $queryParts[] = 'RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); } $tableSql = array(); if (!$this->onSchemaAlterTable($diff, $tableSql)) { if (count($queryParts) > 0) { $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . implode(" ", $queryParts); } // Some table alteration operations require a table reorganization. if (!empty($diff->removedColumns) || !empty($diff->changedColumns)) { $sql[] = "CALL SYSPROC.ADMIN_CMD ('REORG TABLE " . $diff->getName($this)->getQuotedName($this) . "')"; } $sql = array_merge($sql, $commentsSQL); if ($diff->newName !== false) { $sql[] = 'RENAME TABLE ' . $diff->getName($this)->getQuotedName($this) . ' TO ' . $diff->getNewName()->getQuotedName($this); } $sql = array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $sql, $this->getPostAlterTableIndexForeignKeySQL($diff)); } return array_merge($sql, $tableSql, $columnSql); }
/** * @param \Doctrine\DBAL\Schema\TableDiff $diff * * @return array|bool */ private function getSimpleAlterTableSQL(TableDiff $diff) { // Suppress changes on integer type autoincrement columns. foreach ($diff->changedColumns as $oldColumnName => $columnDiff) { if (!$columnDiff->fromColumn instanceof Column || !$columnDiff->column instanceof Column || !$columnDiff->column->getAutoincrement() || !(string) $columnDiff->column->getType() === 'Integer') { continue; } if (!$columnDiff->hasChanged('type') && $columnDiff->hasChanged('unsigned')) { unset($diff->changedColumns[$oldColumnName]); continue; } $fromColumnType = (string) $columnDiff->fromColumn->getType(); if ($fromColumnType === 'SmallInt' || $fromColumnType === 'BigInt') { unset($diff->changedColumns[$oldColumnName]); } } if (!empty($diff->renamedColumns) || !empty($diff->addedForeignKeys) || !empty($diff->addedIndexes) || !empty($diff->changedColumns) || !empty($diff->changedForeignKeys) || !empty($diff->changedIndexes) || !empty($diff->removedColumns) || !empty($diff->removedForeignKeys) || !empty($diff->removedIndexes) || !empty($diff->renamedIndexes)) { return false; } $table = new Table($diff->name); $sql = array(); $tableSql = array(); $columnSql = array(); foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $field = array_merge(array('unique' => null, 'autoincrement' => null, 'default' => null), $column->toArray()); $type = (string) $field['type']; switch (true) { case isset($field['columnDefinition']) || $field['autoincrement'] || $field['unique']: case $type == 'DateTime' && $field['default'] == $this->getCurrentTimestampSQL(): case $type == 'Date' && $field['default'] == $this->getCurrentDateSQL(): case $type == 'Time' && $field['default'] == $this->getCurrentTimeSQL(): return false; } $field['name'] = $column->getQuotedName($this); if (strtolower($field['type']) == 'string' && $field['length'] === null) { $field['length'] = 255; } $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ADD COLUMN ' . $this->getColumnDeclarationSQL($field['name'], $field); } if (!$this->onSchemaAlterTable($diff, $tableSql)) { if ($diff->newName !== false) { $newTable = new Identifier($diff->newName); $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' RENAME TO ' . $newTable->getQuotedName($this); } } return array_merge($sql, $tableSql, $columnSql); }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $sql = array(); $commentsSQL = array(); $columnSql = array(); foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $query = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; if ($comment = $this->getColumnComment($column)) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->name, $column->getName(), $comment); } } foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $query = 'DROP ' . $column->getQuotedName($this); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } foreach ($diff->changedColumns as $columnDiff) { /** @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } if ($this->isUnchangedBinaryColumn($columnDiff)) { continue; } $oldColumnName = $columnDiff->getOldColumnName()->getQuotedName($this); $column = $columnDiff->column; if ($columnDiff->hasChanged('type') || $columnDiff->hasChanged('precision') || $columnDiff->hasChanged('scale')) { $type = $column->getType(); // here was a server version check before, but DBAL API does not support this anymore. $query = 'ALTER ' . $oldColumnName . ' TYPE ' . $type->getSqlDeclaration($column->toArray(), $this); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } if ($columnDiff->hasChanged('default') || $columnDiff->hasChanged('type')) { $defaultClause = null === $column->getDefault() ? ' DROP DEFAULT' : ' SET' . $this->getDefaultValueDeclarationSQL($column->toArray()); $query = 'ALTER ' . $oldColumnName . $defaultClause; $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } if ($columnDiff->hasChanged('notnull')) { $query = 'ALTER ' . $oldColumnName . ' ' . ($column->getNotNull() ? 'SET' : 'DROP') . ' NOT NULL'; $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } if ($columnDiff->hasChanged('autoincrement')) { if ($column->getAutoincrement()) { // add autoincrement $seqName = $this->getIdentitySequenceName($diff->name, $oldColumnName); $sql[] = "CREATE SEQUENCE " . $seqName; $sql[] = "SELECT setval('" . $seqName . "', (SELECT MAX(" . $oldColumnName . ") FROM " . $diff->getName()->getQuotedName($this) . "))"; $query = "ALTER " . $oldColumnName . " SET DEFAULT nextval('" . $seqName . "')"; $sql[] = "ALTER TABLE " . $diff->getName()->getQuotedName($this) . " " . $query; } else { // Drop autoincrement, but do NOT drop the sequence. It might be re-used by other tables or have $query = "ALTER " . $oldColumnName . " " . "DROP DEFAULT"; $sql[] = "ALTER TABLE " . $diff->getName()->getQuotedName($this) . " " . $query; } } if ($columnDiff->hasChanged('comment')) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->name, $column->getName(), $this->getColumnComment($column)); } if ($columnDiff->hasChanged('length')) { $query = 'ALTER ' . $column->getName() . ' TYPE ' . $column->getType()->getSqlDeclaration($column->toArray(), $this); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); } $tableSql = array(); if (!$this->onSchemaAlterTable($diff, $tableSql)) { if ($diff->newName !== false) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' RENAME TO ' . $diff->getNewName()->getQuotedName($this); } $sql = array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $sql, $this->getPostAlterTableIndexForeignKeySQL($diff), $commentsSQL); } return array_merge($sql, $tableSql, $columnSql); }
/** * @param \Doctrine\DBAL\Schema\TableDiff $diff * * @return array */ protected function getPostAlterTableIndexForeignKeySQL(TableDiff $diff) { $tableName = false !== $diff->newName ? $diff->getNewName()->getQuotedName($this) : $diff->getName()->getQuotedName($this); $sql = array(); if ($this->supportsForeignKeyConstraints()) { foreach ($diff->addedForeignKeys as $foreignKey) { $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName); } foreach ($diff->changedForeignKeys as $foreignKey) { $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName); } } foreach ($diff->addedIndexes as $index) { $sql[] = $this->getCreateIndexSQL($index, $tableName); } foreach ($diff->changedIndexes as $index) { $sql[] = $this->getCreateIndexSQL($index, $tableName); } foreach ($diff->renamedIndexes as $oldIndexName => $index) { $oldIndexName = new Identifier($oldIndexName); $sql = array_merge($sql, $this->getRenameIndexSQL($oldIndexName->getQuotedName($this), $index, $tableName)); } return $sql; }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $sql = array(); $commentsSQL = array(); $columnSql = array(); $fields = array(); foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $fields[] = $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); if ($comment = $this->getColumnComment($column)) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->name, $column->getName(), $comment); } } if (count($fields)) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ADD (' . implode(', ', $fields) . ')'; } $fields = array(); foreach ($diff->changedColumns as $columnDiff) { if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ $column = $columnDiff->column; // Do not generate column alteration clause if type is binary and only fixed property has changed. // Oracle only supports binary type columns with variable length. // Avoids unnecessary table alteration statements. if ($column->getType() instanceof BinaryType && $columnDiff->hasChanged('fixed') && count($columnDiff->changedProperties) === 1) { continue; } $columnHasChangedComment = $columnDiff->hasChanged('comment'); /** * Do not add query part if only comment has changed */ if (!($columnHasChangedComment && count($columnDiff->changedProperties) === 1)) { $columnInfo = $column->toArray(); if (!$columnDiff->hasChanged('notnull')) { $columnInfo['notnull'] = false; } $fields[] = $column->getQuotedName($this) . $this->getColumnDeclarationSQL('', $columnInfo); } if ($columnHasChangedComment) { $commentsSQL[] = $this->getCommentOnColumnSQL($diff->name, $column->getName(), $this->getColumnComment($column)); } } if (count($fields)) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' MODIFY (' . implode(', ', $fields) . ')'; } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); } $fields = array(); foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $fields[] = $column->getQuotedName($this); } if (count($fields)) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' DROP (' . implode(', ', $fields) . ')'; } $tableSql = array(); if (!$this->onSchemaAlterTable($diff, $tableSql)) { if ($diff->newName !== false) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' RENAME TO ' . $diff->getNewName()->getQuotedName($this); } $sql = array_merge($sql, $this->_getAlterTableIndexForeignKeySQL($diff), $commentsSQL); } return array_merge($sql, $tableSql, $columnSql); }
public function onSchemaAlterTableRemoveColumn(SchemaAlterTableRemoveColumnEventArgs $args) { $column = $args->getColumn(); if (!$this->isSpatialColumnType($column)) { return; } if ('geometry' !== $column->getType()->getName() || $this->schemaManager->isPostGis2()) { return; } $platform = $args->getPlatform(); $diff = $args->getTableDiff(); $table = new Identifier(false !== $diff->newName ? $diff->newName : $diff->name); if ($column->getNotnull()) { // Remove NOT NULL constraint from the field $args->addSql(sprintf('ALTER TABLE %s ALTER %s SET DEFAULT NULL', $table->getQuotedName($platform), $column->getQuotedName($platform))); } // We use DropGeometryColumn() to also drop entries from the geometry_columns table $args->addSql(sprintf("SELECT DropGeometryColumn('%s', '%s')", $table->getName(), $column->getName())); $args->preventDefault(); }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $columnSql = array(); $queryParts = array(); if ($diff->newName !== false) { $queryParts[] = 'RENAME TO ' . $diff->getNewName()->getQuotedName($this); } foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $columnArray = $column->toArray(); $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $queryParts[] = 'DROP ' . $column->getQuotedName($this); } foreach ($diff->changedColumns as $columnDiff) { if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ $column = $columnDiff->column; $columnArray = $column->toArray(); // Don't propagate default value changes for unsupported column types. if ($columnDiff->hasChanged('default') && count($columnDiff->changedProperties) === 1 && ($columnArray['type'] instanceof TextType || $columnArray['type'] instanceof BlobType)) { continue; } $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'CHANGE ' . $columnDiff->getOldColumnName()->getQuotedName($this) . ' ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $columnArray = $column->toArray(); $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'CHANGE ' . $oldColumnName->getQuotedName($this) . ' ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } if (isset($diff->addedIndexes['primary'])) { $keyColumns = array_unique(array_values($diff->addedIndexes['primary']->getColumns())); $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')'; unset($diff->addedIndexes['primary']); } $sql = array(); $tableSql = array(); if (!$this->onSchemaAlterTable($diff, $tableSql)) { if (count($queryParts) > 0) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . implode(", ", $queryParts); } $sql = array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $sql, $this->getPostAlterTableIndexForeignKeySQL($diff)); } return array_merge($sql, $tableSql, $columnSql); }
/** * Returns the SQL snippet for declaring a default constraint. * * @param string $table Name of the table to return the default constraint declaration for. * @param array $column Column definition. * * @return string * * @throws \InvalidArgumentException */ public function getDefaultConstraintDeclarationSQL($table, array $column) { if (!isset($column['default'])) { throw new \InvalidArgumentException("Incomplete column definition. 'default' required."); } $columnName = new Identifier($column['name']); return ' CONSTRAINT ' . $this->generateDefaultConstraintName($table, $column['name']) . $this->getDefaultValueDeclarationSQL($column) . ' FOR ' . $columnName->getQuotedName($this); }
/** * {@inheritDoc} */ public function getTruncateTableSQL($tableName, $cascade = false) { $tableIdentifier = new Identifier($tableName); $sql = 'TRUNCATE ' . $tableIdentifier->getQuotedName($this); if ($cascade) { $sql .= ' CASCADE'; } return $sql; }
/** * Returns the quoted representation of the referenced table name * the foreign key constraint is associated with. * * But only if it was defined with one or the referenced table name * is a keyword reserved by the platform. * Otherwise the plain unquoted value as inserted is returned. * * @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform The platform to use for quotation. * * @return string */ public function getQuotedForeignTableName(AbstractPlatform $platform) { return $this->_foreignTableName->getQuotedName($platform); }
/** * Returns the SQL clause for renaming a table in a table alteration. * * @param Identifier $newTableName The quoted name of the table to rename to. * * @return string */ protected function getAlterTableRenameTableClause(Identifier $newTableName) { return 'RENAME ' . $newTableName->getQuotedName($this); }
/** * Generate table index column declaration * @codeCoverageIgnore */ public function getIndexDeclarationSQL($name, Index $index) { $columns = $index->getQuotedColumns($this); $name = new Identifier($name); if (count($columns) == 0) { throw new \InvalidArgumentException("Incomplete definition. 'columns' required."); } return 'INDEX ' . $name->getQuotedName($this) . ' USING FULLTEXT (' . $this->getIndexFieldDeclarationListSQL($columns) . ')'; }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $queryParts = array(); $sql = array(); $columnSql = array(); $commentsSql = array(); /** @var \Doctrine\DBAL\Schema\Column $column */ foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $columnDef = $column->toArray(); $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); if (isset($columnDef['default'])) { $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); } $comment = $this->getColumnComment($column); if (!empty($comment) || is_numeric($comment)) { $commentsSql[] = $this->getCreateColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment); } } foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this); } /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ foreach ($diff->changedColumns as $columnDiff) { if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } $column = $columnDiff->column; $comment = $this->getColumnComment($column); $hasComment = !empty($comment) || is_numeric($comment); if ($columnDiff->fromColumn instanceof Column) { $fromComment = $this->getColumnComment($columnDiff->fromColumn); $hasFromComment = !empty($fromComment) || is_numeric($fromComment); if ($hasFromComment && $hasComment && $fromComment != $comment) { $commentsSql[] = $this->getAlterColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment); } elseif ($hasFromComment && !$hasComment) { $commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this)); } elseif ($hasComment) { $commentsSql[] = $this->getCreateColumnCommentSQL($diff->name, $column->getQuotedName($this), $comment); } } else { // todo: Original comment cannot be determined. What to do? Add, update, drop or skip? } // Do not add query part if only comment has changed. if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) { continue; } $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff); if ($requireDropDefaultConstraint) { $queryParts[] = $this->getAlterTableDropDefaultConstraintClause($diff->name, $columnDiff->oldColumnName); } $columnDef = $column->toArray(); $queryParts[] = 'ALTER COLUMN ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); if (isset($columnDef['default']) && ($requireDropDefaultConstraint || $columnDiff->hasChanged('default'))) { $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); } } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $sql[] = "sp_RENAME '" . $diff->name . "." . $oldColumnName->getQuotedName($this) . "', '" . $column->getQuotedName($this) . "', 'COLUMN'"; // Recreate default constraint with new column name if necessary (for future reference). if ($column->getDefault() !== null) { $queryParts[] = $this->getAlterTableDropDefaultConstraintClause($diff->name, $oldColumnName->getQuotedName($this)); $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); } } $tableSql = array(); if ($this->onSchemaAlterTable($diff, $tableSql)) { return array_merge($tableSql, $columnSql); } foreach ($queryParts as $query) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . $query; } $sql = array_merge($sql, $this->_getAlterTableIndexForeignKeySQL($diff), $commentsSql); if ($diff->newName !== false) { $sql[] = "sp_RENAME '" . $diff->getName()->getQuotedName($this) . "', '" . $diff->getNewName()->getQuotedName($this) . "'"; /** * Rename table's default constraints names * to match the new table name. * This is necessary to ensure that the default * constraints can be referenced in future table * alterations as the table name is encoded in * default constraints' names. */ $sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " . "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " . "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " . "'" . $this->generateIdentifierName($diff->newName) . "') + ''', ''OBJECT'';' " . "FROM sys.default_constraints dc " . "JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id " . "WHERE tbl.name = '" . $diff->getNewName()->getQuotedName($this) . "';" . "EXEC sp_executesql @sql"; } return array_merge($sql, $tableSql, $columnSql); }
/** * {@inheritDoc} */ public function getAlterTableSQL(TableDiff $diff) { $columnSql = array(); $queryParts = array(); if ($diff->newName !== false) { $queryParts[] = 'RENAME TO ' . $diff->getNewName()->getQuotedName($this); } foreach ($diff->addedColumns as $column) { if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { continue; } $columnArray = $column->toArray(); $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } foreach ($diff->removedColumns as $column) { if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { continue; } $queryParts[] = 'DROP ' . $column->getQuotedName($this); } foreach ($diff->changedColumns as $columnDiff) { if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { continue; } /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ $column = $columnDiff->column; $columnArray = $column->toArray(); // Do not generate column alteration clause if type is binary and only fixed property has changed. // Drizzle only supports binary type columns with variable length. // Avoids unnecessary table alteration statements. if ($columnArray['type'] instanceof BinaryType && $columnDiff->hasChanged('fixed') && count($columnDiff->changedProperties) === 1) { continue; } $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'CHANGE ' . $columnDiff->getOldColumnName()->getQuotedName($this) . ' ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } foreach ($diff->renamedColumns as $oldColumnName => $column) { if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { continue; } $oldColumnName = new Identifier($oldColumnName); $columnArray = $column->toArray(); $columnArray['comment'] = $this->getColumnComment($column); $queryParts[] = 'CHANGE ' . $oldColumnName->getQuotedName($this) . ' ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); } $sql = array(); $tableSql = array(); if (!$this->onSchemaAlterTable($diff, $tableSql)) { if (count($queryParts) > 0) { $sql[] = 'ALTER TABLE ' . $diff->getName()->getQuotedName($this) . ' ' . implode(", ", $queryParts); } $sql = array_merge($this->getPreAlterTableIndexForeignKeySQL($diff), $sql, $this->getPostAlterTableIndexForeignKeySQL($diff)); } return array_merge($sql, $tableSql, $columnSql); }
/** * {@inheritdoc} */ public function getCommentOnColumnSQL($tableName, $columnName, $comment) { $tableName = new Identifier($tableName); $columnName = new Identifier($columnName); $comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment); return "COMMENT ON COLUMN " . $tableName->getQuotedName($this) . "." . $columnName->getQuotedName($this) . " IS {$comment}"; }
/** * Generates 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 boolean $cascade * * @return string */ public function getTruncateTableSQL($tableName, $cascade = false) { $tableIdentifier = new Identifier($tableName); return 'TRUNCATE ' . $tableIdentifier->getQuotedName($this); }
/** * Returns the SQL snippet for creating a table constraint. * * @param Constraint $constraint The table constraint to create the SQL snippet for. * @param string|null $name The table constraint name to use if any. * * @return string * * @throws \InvalidArgumentException if the given table constraint type is not supported by this method. */ protected function getTableConstraintDeclarationSQL(Constraint $constraint, $name = null) { if ($constraint instanceof ForeignKeyConstraint) { return $this->getForeignKeyDeclarationSQL($constraint); } if (!$constraint instanceof Index) { throw new \InvalidArgumentException('Unsupported constraint type: ' . get_class($constraint)); } if (!$constraint->isPrimary() && !$constraint->isUnique()) { throw new \InvalidArgumentException('Can only create primary, unique or foreign key constraint declarations, no common index declarations ' . 'with getTableConstraintDeclarationSQL().'); } $constraintColumns = $constraint->getQuotedColumns($this); if (empty($constraintColumns)) { throw new \InvalidArgumentException("Incomplete definition. 'columns' required."); } $sql = ''; $flags = ''; if (!empty($name)) { $name = new Identifier($name); $sql .= 'CONSTRAINT ' . $name->getQuotedName($this) . ' '; } if ($constraint->hasFlag('clustered')) { $flags = 'CLUSTERED '; } if ($constraint->isPrimary()) { return $sql . 'PRIMARY KEY ' . $flags . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')'; } return $sql . 'UNIQUE ' . $flags . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')'; }
/** * {@inheritDoc} */ public function getUniqueConstraintDeclarationSQL($name, Index $index) { $columns = $index->getQuotedColumns($this); $name = new Identifier($name); if (count($columns) === 0) { throw new \InvalidArgumentException("Incomplete definition. 'columns' required."); } return 'UNIQUE (' . $this->getIndexFieldDeclarationListSQL($columns) . ') CONSTRAINT ' . $name->getQuotedName($this); }