| package org.apache.ddlutils.platform.mssql; |
| |
| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| */ |
| |
| import java.io.IOException; |
| import java.sql.Types; |
| import java.text.DateFormat; |
| import java.text.SimpleDateFormat; |
| import java.util.Map; |
| |
| import org.apache.commons.lang.StringUtils; |
| import org.apache.ddlutils.Platform; |
| import org.apache.ddlutils.alteration.ColumnDefinitionChange; |
| import org.apache.ddlutils.model.Column; |
| import org.apache.ddlutils.model.Database; |
| import org.apache.ddlutils.model.ForeignKey; |
| import org.apache.ddlutils.model.Index; |
| import org.apache.ddlutils.model.Table; |
| import org.apache.ddlutils.model.TypeMap; |
| import org.apache.ddlutils.platform.SqlBuilder; |
| |
| /** |
| * The SQL Builder for the Microsoft SQL Server. |
| * |
| * @version $Revision$ |
| */ |
| public class MSSqlBuilder extends SqlBuilder |
| { |
| /** We use a generic date format. */ |
| private DateFormat _genericDateFormat = new SimpleDateFormat("yyyy-MM-dd"); |
| /** We use a generic date format. */ |
| private DateFormat _genericTimeFormat = new SimpleDateFormat("HH:mm:ss"); |
| |
| /** |
| * Creates a new builder instance. |
| * |
| * @param platform The plaftform this builder belongs to |
| */ |
| public MSSqlBuilder(Platform platform) |
| { |
| super(platform); |
| addEscapedCharSequence("'", "''"); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void createTable(Database database, Table table, Map parameters) throws IOException |
| { |
| turnOnQuotation(); |
| super.createTable(database, table, parameters); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropTable(Table table) throws IOException |
| { |
| String tableName = getTableName(table); |
| String tableNameVar = "tn" + createUniqueIdentifier(); |
| String constraintNameVar = "cn" + createUniqueIdentifier(); |
| |
| turnOnQuotation(); |
| print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = "); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| println(")"); |
| println("BEGIN"); |
| println(" DECLARE @" + tableNameVar + " nvarchar(256), @" + constraintNameVar + " nvarchar(256)"); |
| println(" DECLARE refcursor CURSOR FOR"); |
| println(" SELECT object_name(objs.parent_obj) tablename, objs.name constraintname"); |
| println(" FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid"); |
| print(" WHERE objs.xtype != 'PK' AND object_name(objs.parent_obj) = "); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| println(" OPEN refcursor"); |
| println(" FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar); |
| println(" WHILE @@FETCH_STATUS = 0"); |
| println(" BEGIN"); |
| println(" EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar + ")"); |
| println(" FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar); |
| println(" END"); |
| println(" CLOSE refcursor"); |
| println(" DEALLOCATE refcursor"); |
| print(" DROP TABLE "); |
| printlnIdentifier(tableName); |
| print("END"); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropForeignKeys(Table table) throws IOException |
| { |
| turnOnQuotation(); |
| super.dropForeignKeys(table); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected DateFormat getValueDateFormat() |
| { |
| return _genericDateFormat; |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected DateFormat getValueTimeFormat() |
| { |
| return _genericTimeFormat; |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected String getValueAsString(Column column, Object value) |
| { |
| if (value == null) |
| { |
| return "NULL"; |
| } |
| |
| StringBuffer result = new StringBuffer(); |
| |
| switch (column.getTypeCode()) |
| { |
| case Types.REAL: |
| case Types.NUMERIC: |
| case Types.FLOAT: |
| case Types.DOUBLE: |
| case Types.DECIMAL: |
| // SQL Server does not want quotes around the value |
| if (!(value instanceof String) && (getValueNumberFormat() != null)) |
| { |
| result.append(getValueNumberFormat().format(value)); |
| } |
| else |
| { |
| result.append(value.toString()); |
| } |
| break; |
| case Types.DATE: |
| result.append("CAST("); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(value instanceof String ? (String)value : getValueDateFormat().format(value)); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(" AS datetime)"); |
| break; |
| case Types.TIME: |
| result.append("CAST("); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(value instanceof String ? (String)value : getValueTimeFormat().format(value)); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(" AS datetime)"); |
| break; |
| case Types.TIMESTAMP: |
| result.append("CAST("); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(value.toString()); |
| result.append(getPlatformInfo().getValueQuoteToken()); |
| result.append(" AS datetime)"); |
| break; |
| } |
| return super.getValueAsString(column, value); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected String getNativeDefaultValue(Column column) |
| { |
| // Sql Server wants BIT default values as 0 or 1 |
| if ((column.getTypeCode() == Types.BIT) || (column.getTypeCode() == Types.BOOLEAN)) |
| { |
| return getDefaultValueHelper().convert(column.getDefaultValue(), column.getTypeCode(), Types.SMALLINT); |
| } |
| else |
| { |
| return super.getNativeDefaultValue(column); |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException |
| { |
| print("IDENTITY (1,1) "); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropIndex(Table table, Index index) throws IOException |
| { |
| print("DROP INDEX "); |
| printIdentifier(getTableName(table)); |
| print("."); |
| printIdentifier(getIndexName(index)); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropForeignKey(Table table, ForeignKey foreignKey) throws IOException |
| { |
| String constraintName = getForeignKeyName(table, foreignKey); |
| |
| print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'F' AND name = "); |
| printAlwaysSingleQuotedIdentifier(constraintName); |
| println(")"); |
| printIndent(); |
| print("ALTER TABLE "); |
| printIdentifier(getTableName(table)); |
| print(" DROP CONSTRAINT "); |
| printIdentifier(constraintName); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * Returns the statement that turns on the ability to write delimited identifiers. |
| * |
| * @return The quotation-on statement |
| */ |
| private String getQuotationOnStatement() |
| { |
| if (getPlatform().isDelimitedIdentifierModeOn()) |
| { |
| return "SET quoted_identifier on" + getPlatformInfo().getSqlCommandDelimiter() + "\n"; |
| } |
| else |
| { |
| return ""; |
| } |
| } |
| |
| /** |
| * If quotation mode is on, then this writes the statement that turns on the ability to write delimited identifiers. |
| */ |
| protected void turnOnQuotation() throws IOException |
| { |
| print(getQuotationOnStatement()); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public String getSelectLastIdentityValues(Table table) |
| { |
| return "SELECT @@IDENTITY"; |
| } |
| |
| /** |
| * Returns the SQL to enable identity override mode. |
| * |
| * @param table The table to enable the mode for |
| * @return The SQL |
| */ |
| protected String getEnableIdentityOverrideSql(Table table) |
| { |
| StringBuffer result = new StringBuffer(); |
| |
| result.append(getQuotationOnStatement()); |
| result.append("SET IDENTITY_INSERT "); |
| result.append(getDelimitedIdentifier(getTableName(table))); |
| result.append(" ON"); |
| result.append(getPlatformInfo().getSqlCommandDelimiter()); |
| |
| return result.toString(); |
| } |
| |
| /** |
| * Returns the SQL to disable identity override mode. |
| * |
| * @param table The table to disable the mode for |
| * @return The SQL |
| */ |
| protected String getDisableIdentityOverrideSql(Table table) |
| { |
| StringBuffer result = new StringBuffer(); |
| |
| result.append(getQuotationOnStatement()); |
| result.append("SET IDENTITY_INSERT "); |
| result.append(getDelimitedIdentifier(getTableName(table))); |
| result.append(" OFF"); |
| result.append(getPlatformInfo().getSqlCommandDelimiter()); |
| |
| return result.toString(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public String getDeleteSql(Table table, Map pkValues, boolean genPlaceholders) |
| { |
| return getQuotationOnStatement() + super.getDeleteSql(table, pkValues, genPlaceholders); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public String getInsertSql(Table table, Map columnValues, boolean genPlaceholders) |
| { |
| return getQuotationOnStatement() + super.getInsertSql(table, columnValues, genPlaceholders); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public String getUpdateSql(Table table, Map columnValues, boolean genPlaceholders) |
| { |
| return getQuotationOnStatement() + super.getUpdateSql(table, columnValues, genPlaceholders); |
| } |
| |
| /** |
| * Prints the given identifier with enforced single quotes around it regardless of whether |
| * delimited identifiers are turned on or not. |
| * |
| * @param identifier The identifier |
| */ |
| private void printAlwaysSingleQuotedIdentifier(String identifier) throws IOException |
| { |
| print("'"); |
| print(identifier); |
| print("'"); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void copyData(Table sourceTable, Table targetTable) throws IOException |
| { |
| // Sql Server per default does not allow us to insert values explicitly into |
| // identity columns. However, we can change this behavior |
| boolean hasIdentityColumns = targetTable.getAutoIncrementColumns().length > 0; |
| |
| if (hasIdentityColumns) |
| { |
| print("SET IDENTITY_INSERT "); |
| printIdentifier(getTableName(targetTable)); |
| print(" ON"); |
| printEndOfStatement(); |
| } |
| super.copyData(sourceTable, targetTable); |
| // We have to turn it off ASAP because it can be on only for one table per session |
| if (hasIdentityColumns) |
| { |
| print("SET IDENTITY_INSERT "); |
| printIdentifier(getTableName(targetTable)); |
| print(" OFF"); |
| printEndOfStatement(); |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void addColumn(Table table, Column newColumn) throws IOException |
| { |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(table)); |
| printIndent(); |
| print("ADD "); |
| writeColumn(table, newColumn); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * Generates the SQL to drop a column from a table. |
| * |
| * @param table The table where to drop the column from |
| * @param column The column to drop |
| */ |
| public void dropColumn(Table table, Column column) throws IOException |
| { |
| if (!StringUtils.isEmpty(column.getDefaultValue())) |
| { |
| writeDropConstraintStatement(table, column, "D"); |
| } |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(table)); |
| printIndent(); |
| print("DROP COLUMN "); |
| printIdentifier(getColumnName(column)); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * Writes the SQL for dropping the primary key of the given table. |
| * |
| * @param table The table |
| */ |
| public void dropPrimaryKey(Table table) throws IOException |
| { |
| // this would be easier if named primary keys are supported |
| // because for named pks we could use ALTER TABLE DROP |
| writeDropConstraintStatement(table, null, "PK"); |
| } |
| |
| /** |
| * Writes the SQL to recreate a column, e.g. using a different type or similar. |
| * |
| * @param table The table |
| * @param curColumn The current column definition |
| * @param newColumn The new column definition |
| */ |
| public void recreateColumn(Table table, Column curColumn, Column newColumn) throws IOException |
| { |
| boolean hasDefault = curColumn.getParsedDefaultValue() != null; |
| boolean shallHaveDefault = newColumn.getParsedDefaultValue() != null; |
| String newDefault = newColumn.getDefaultValue(); |
| |
| // Sql Server does not like it if there is a default spec in the ALTER TABLE ALTER COLUMN |
| // statement; thus we have to change the default manually |
| if (newDefault != null) |
| { |
| newColumn.setDefaultValue(null); |
| } |
| if (hasDefault) |
| { |
| // we're dropping the old default |
| writeDropConstraintStatement(table, curColumn, "D"); |
| } |
| |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(table)); |
| printIndent(); |
| print("ALTER COLUMN "); |
| writeColumn(table, newColumn); |
| printEndOfStatement(); |
| |
| if (shallHaveDefault) |
| { |
| newColumn.setDefaultValue(newDefault); |
| |
| // if the column shall have a default, then we have to add it as a constraint |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(table)); |
| printIndent(); |
| print("ADD CONSTRAINT "); |
| printIdentifier(getConstraintName("DF", table, curColumn.getName(), null)); |
| writeColumnDefaultValueStmt(table, newColumn); |
| print(" FOR "); |
| printIdentifier(getColumnName(curColumn)); |
| printEndOfStatement(); |
| } |
| } |
| |
| /** |
| * Writes the SQL to drop a constraint, e.g. a primary key or default value constraint. |
| * |
| * @param table The table that the constraint is on |
| * @param column The column that the constraint is on; <code>null</code> for table-level |
| * constraints |
| * @param typeIdentifier The constraint type identifier as is specified for the |
| * <code>sysobjects</code> system table |
| */ |
| protected void writeDropConstraintStatement(Table table, Column column, String typeIdentifier) throws IOException |
| { |
| String tableName = getTableName(table); |
| String columnName = column == null ? null : getColumnName(column); |
| String tableNameVar = "tn" + createUniqueIdentifier(); |
| String constraintNameVar = "cn" + createUniqueIdentifier(); |
| |
| println("BEGIN"); |
| println(" DECLARE @" + tableNameVar + " nvarchar(256), @" + constraintNameVar + " nvarchar(256)"); |
| println(" DECLARE refcursor CURSOR FOR"); |
| println(" SELECT object_name(objs.parent_obj) tablename, objs.name constraintname"); |
| println(" FROM sysobjects objs JOIN sysconstraints cons ON objs.id = cons.constid"); |
| print(" WHERE objs.xtype = '"); |
| print(typeIdentifier); |
| println("' AND"); |
| if (columnName != null) |
| { |
| print(" cons.colid = (SELECT colid FROM syscolumns WHERE id = object_id("); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| print(") AND name = "); |
| printAlwaysSingleQuotedIdentifier(columnName); |
| println(") AND"); |
| } |
| print(" object_name(objs.parent_obj) = "); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| println(" OPEN refcursor"); |
| println(" FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar); |
| println(" WHILE @@FETCH_STATUS = 0"); |
| println(" BEGIN"); |
| println(" EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar + ")"); |
| println(" FETCH NEXT FROM refcursor INTO @" + tableNameVar + ", @" + constraintNameVar); |
| println(" END"); |
| println(" CLOSE refcursor"); |
| println(" DEALLOCATE refcursor"); |
| print("END"); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeCastExpression(Column sourceColumn, Column targetColumn) throws IOException |
| { |
| boolean sizeChanged = ColumnDefinitionChange.isSizeChanged(getPlatformInfo(), sourceColumn, targetColumn); |
| boolean typeChanged = ColumnDefinitionChange.isTypeChanged(getPlatformInfo(), sourceColumn, targetColumn); |
| |
| if (sizeChanged || typeChanged) |
| { |
| if (TypeMap.isTextType(targetColumn.getTypeCode()) && |
| sizeChanged && (sourceColumn.getSizeAsInt() > targetColumn.getSizeAsInt())) |
| { |
| print("SUBSTRING(CAST("); |
| printIdentifier(getColumnName(sourceColumn)); |
| print(" AS "); |
| print(getNativeType(targetColumn)); |
| print("),1,"); |
| print(getSizeSpec(targetColumn)); |
| print(")"); |
| } |
| else |
| { |
| print("CAST("); |
| printIdentifier(getColumnName(sourceColumn)); |
| print(" AS "); |
| print(getSqlType(targetColumn)); |
| print(")"); |
| } |
| } |
| else |
| { |
| printIdentifier(getColumnName(sourceColumn)); |
| } |
| } |
| } |