cn.lead2success.ddlutils.platform.mssql.MSSqlBuilder Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of ddlutils Show documentation
Show all versions of ddlutils Show documentation
Fork of Apache DdlUtils project without ant support.
package cn.lead2success.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 cn.lead2success.ddlutils.Platform;
import cn.lead2success.ddlutils.alteration.ColumnDefinitionChange;
import cn.lead2success.ddlutils.model.*;
import cn.lead2success.ddlutils.platform.SqlBuilder;
import org.apache.commons.lang.StringUtils;
import java.io.IOException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Map;
/**
* 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
// We need to this only if
// - there is a column in both tables that is auto increment only in the target table, or
// - there is a column in both tables that is auto increment in both tables
Column[] targetIdentityColumns = targetTable.getAutoIncrementColumns();
// Sql Server allows only one identity column, so let's take a shortcut here
boolean needToAllowIdentityInsert = (targetIdentityColumns.length > 0) &&
(sourceTable.findColumn(targetIdentityColumns[0].getName(), getPlatform().isDelimitedIdentifierModeOn()) != null);
if (needToAllowIdentityInsert)
{
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 (needToAllowIdentityInsert)
{
print("SET IDENTITY_INSERT ");
printIdentifier(getTableName(targetTable));
print(" OFF");
printEndOfStatement();
}
}
/**
* {@inheritDoc}
*/
public void addColumn(Database model, 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
* @throws IOException
*/
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
* @throws IOException
*/
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
* @throws IOException
*/
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; null for table-level
* constraints
* @param typeIdentifier The constraint type identifier as is specified for the
* sysobjects 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 &&
(targetColumn.getSize() != null) && (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));
}
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy