| package org.apache.ddlutils.platform.sybase; |
| |
| /* |
| * 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.util.ArrayList; |
| import java.util.HashMap; |
| import java.util.Iterator; |
| import java.util.List; |
| import java.util.Map; |
| |
| import org.apache.ddlutils.Platform; |
| import org.apache.ddlutils.alteration.AddColumnChange; |
| import org.apache.ddlutils.alteration.AddPrimaryKeyChange; |
| import org.apache.ddlutils.alteration.ColumnAutoIncrementChange; |
| import org.apache.ddlutils.alteration.ColumnChange; |
| import org.apache.ddlutils.alteration.ColumnDefaultValueChange; |
| import org.apache.ddlutils.alteration.PrimaryKeyChange; |
| import org.apache.ddlutils.alteration.RemoveColumnChange; |
| import org.apache.ddlutils.alteration.RemovePrimaryKeyChange; |
| import org.apache.ddlutils.alteration.TableChange; |
| 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.platform.CreationParameters; |
| import org.apache.ddlutils.platform.SqlBuilder; |
| import org.apache.ddlutils.util.Jdbc3Utils; |
| |
| /** |
| * The SQL Builder for Sybase. |
| * |
| * @version $Revision$ |
| */ |
| public class SybaseBuilder extends SqlBuilder |
| { |
| /** |
| * Creates a new builder instance. |
| * |
| * @param platform The plaftform this builder belongs to |
| */ |
| public SybaseBuilder(Platform platform) |
| { |
| super(platform); |
| addEscapedCharSequence("'", "''"); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void createTable(Database database, Table table, Map parameters) throws IOException |
| { |
| writeQuotationOnStatement(); |
| super.createTable(database, table, parameters); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeTableCreationStmtEnding(Table table, Map parameters) throws IOException |
| { |
| if (parameters != null) |
| { |
| // We support |
| // - 'lock' |
| // - 'at' |
| // - 'external table at' |
| // - 'on' |
| // - with parameters as name value pairs |
| |
| String lockValue = (String)parameters.get("lock"); |
| String atValue = (String)parameters.get("at"); |
| String externalTableAtValue = (String)parameters.get("external table at"); |
| String onValue = (String)parameters.get("on"); |
| |
| if (lockValue != null) |
| { |
| print(" lock "); |
| print(lockValue); |
| } |
| |
| boolean writtenWithParameters = false; |
| |
| for (Iterator it = parameters.entrySet().iterator(); it.hasNext();) |
| { |
| Map.Entry entry = (Map.Entry)it.next(); |
| String name = entry.getKey().toString(); |
| |
| if (!"lock".equals(name) && !"at".equals(name) && !"external table at".equals(name) && !"on".equals(name)) |
| { |
| if (!writtenWithParameters) |
| { |
| print(" with "); |
| writtenWithParameters = true; |
| } |
| else |
| { |
| print(", "); |
| } |
| print(name); |
| if (entry.getValue() != null) |
| { |
| print("="); |
| print(entry.getValue().toString()); |
| } |
| } |
| } |
| if (onValue != null) |
| { |
| print(" on "); |
| print(onValue); |
| } |
| if (externalTableAtValue != null) |
| { |
| print(" external table at \""); |
| print(externalTableAtValue); |
| print("\""); |
| } |
| else if (atValue != null) |
| { |
| print(" at \""); |
| print(atValue); |
| print("\""); |
| } |
| } |
| super.writeTableCreationStmtEnding(table, parameters); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeColumn(Table table, Column column) throws IOException |
| { |
| printIdentifier(getColumnName(column)); |
| print(" "); |
| print(getSqlType(column)); |
| writeColumnDefaultValueStmt(table, column); |
| // Sybase does not like NULL/NOT NULL and IDENTITY together |
| if (column.isAutoIncrement()) |
| { |
| print(" "); |
| writeColumnAutoIncrementStmt(table, column); |
| } |
| else |
| { |
| print(" "); |
| if (column.isRequired()) |
| { |
| writeColumnNotNullableStmt(); |
| } |
| else |
| { |
| // we'll write a NULL for all columns that are not required |
| writeColumnNullableStmt(); |
| } |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected String getNativeDefaultValue(Column column) |
| { |
| if ((column.getTypeCode() == Types.BIT) || |
| (Jdbc3Utils.supportsJava14JdbcTypes() && (column.getTypeCode() == Jdbc3Utils.determineBooleanTypeCode()))) |
| { |
| return getDefaultValueHelper().convert(column.getDefaultValue(), column.getTypeCode(), Types.SMALLINT).toString(); |
| } |
| else |
| { |
| return super.getNativeDefaultValue(column); |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropTable(Table table) throws IOException |
| { |
| writeQuotationOnStatement(); |
| print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = "); |
| printAlwaysSingleQuotedIdentifier(getTableName(table)); |
| println(")"); |
| println("BEGIN"); |
| printIndent(); |
| print("DROP TABLE "); |
| printlnIdentifier(getTableName(table)); |
| print("END"); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeExternalForeignKeyDropStmt(Table table, ForeignKey foreignKey) throws IOException |
| { |
| String constraintName = getForeignKeyName(table, foreignKey); |
| |
| print("IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'RI' AND name = "); |
| printAlwaysSingleQuotedIdentifier(constraintName); |
| println(")"); |
| printIndent(); |
| print("ALTER TABLE "); |
| printIdentifier(getTableName(table)); |
| print(" DROP CONSTRAINT "); |
| printIdentifier(constraintName); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void writeExternalIndexDropStmt(Table table, Index index) throws IOException |
| { |
| print("DROP INDEX "); |
| printIdentifier(getTableName(table)); |
| print("."); |
| printIdentifier(getIndexName(index)); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| public void dropExternalForeignKeys(Table table) throws IOException |
| { |
| writeQuotationOnStatement(); |
| super.dropExternalForeignKeys(table); |
| } |
| |
| /** |
| * {@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("SET IDENTITY_INSERT "); |
| result.append(getDelimitedIdentifier(getTableName(table))); |
| result.append(" ON"); |
| |
| 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("SET IDENTITY_INSERT "); |
| result.append(getDelimitedIdentifier(getTableName(table))); |
| result.append(" OFF"); |
| |
| return result.toString(); |
| } |
| |
| /** |
| * Returns the statement that turns on the ability to write delimited identifiers. |
| * |
| * @return The quotation-on statement |
| */ |
| protected String getQuotationOnStatement() |
| { |
| if (getPlatform().isDelimitedIdentifierModeOn()) |
| { |
| return "SET quoted_identifier on"; |
| } |
| else |
| { |
| return ""; |
| } |
| } |
| |
| /** |
| * Writes the statement that turns on the ability to write delimited identifiers. |
| */ |
| private void writeQuotationOnStatement() throws IOException |
| { |
| print(getQuotationOnStatement()); |
| printEndOfStatement(); |
| } |
| |
| /** |
| * 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 writeCopyDataStatement(Table sourceTable, Table targetTable) throws IOException |
| { |
| boolean hasIdentity = targetTable.getAutoIncrementColumns().length > 0; |
| |
| if (hasIdentity) |
| { |
| print("SET IDENTITY_INSERT "); |
| printIdentifier(getTableName(targetTable)); |
| print(" ON"); |
| printEndOfStatement(); |
| } |
| super.writeCopyDataStatement(sourceTable, targetTable); |
| if (hasIdentity) |
| { |
| print("SET IDENTITY_INSERT "); |
| printIdentifier(getTableName(targetTable)); |
| print(" OFF"); |
| printEndOfStatement(); |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void writeCastExpression(Column sourceColumn, Column targetColumn) throws IOException |
| { |
| String sourceNativeType = getBareNativeType(sourceColumn); |
| String targetNativeType = getBareNativeType(targetColumn); |
| |
| if (sourceNativeType.equals(targetNativeType)) |
| { |
| printIdentifier(getColumnName(sourceColumn)); |
| } |
| else |
| { |
| print("CONVERT("); |
| print(getNativeType(targetColumn)); |
| print(","); |
| printIdentifier(getColumnName(sourceColumn)); |
| print(")"); |
| } |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void processChanges(Database currentModel, Database desiredModel, List changes, CreationParameters params) throws IOException |
| { |
| if (!changes.isEmpty()) |
| { |
| writeQuotationOnStatement(); |
| } |
| super.processChanges(currentModel, desiredModel, changes, params); |
| } |
| |
| /** |
| * {@inheritDoc} |
| */ |
| protected void processTableStructureChanges(Database currentModel, |
| Database desiredModel, |
| Table sourceTable, |
| Table targetTable, |
| Map parameters, |
| List changes) throws IOException |
| { |
| // First we drop primary keys as necessary |
| for (Iterator changeIt = changes.iterator(); changeIt.hasNext();) |
| { |
| TableChange change = (TableChange)changeIt.next(); |
| |
| if (change instanceof RemovePrimaryKeyChange) |
| { |
| processChange(currentModel, desiredModel, (RemovePrimaryKeyChange)change); |
| changeIt.remove(); |
| } |
| else if (change instanceof PrimaryKeyChange) |
| { |
| PrimaryKeyChange pkChange = (PrimaryKeyChange)change; |
| RemovePrimaryKeyChange removePkChange = new RemovePrimaryKeyChange(pkChange.getChangedTable(), |
| pkChange.getOldPrimaryKeyColumns()); |
| |
| processChange(currentModel, desiredModel, removePkChange); |
| } |
| } |
| |
| |
| HashMap columnChanges = new HashMap(); |
| |
| // Next we add/remove columns |
| for (Iterator changeIt = changes.iterator(); changeIt.hasNext();) |
| { |
| TableChange change = (TableChange)changeIt.next(); |
| |
| if (change instanceof AddColumnChange) |
| { |
| AddColumnChange addColumnChange = (AddColumnChange)change; |
| |
| // Sybase can only add not insert columns |
| if (addColumnChange.isAtEnd()) |
| { |
| processChange(currentModel, desiredModel, addColumnChange); |
| changeIt.remove(); |
| } |
| } |
| else if (change instanceof RemoveColumnChange) |
| { |
| processChange(currentModel, desiredModel, (RemoveColumnChange)change); |
| changeIt.remove(); |
| } |
| else if (change instanceof ColumnAutoIncrementChange) |
| { |
| // Sybase has no way of adding or removing an IDENTITY constraint |
| // Thus we have to rebuild the table anyway and can ignore all the other |
| // column changes |
| columnChanges = null; |
| } |
| else if ((change instanceof ColumnChange) && (columnChanges != null)) |
| { |
| // we gather all changed columns because we can use the ALTER TABLE ALTER COLUMN |
| // statement for them |
| ColumnChange columnChange = (ColumnChange)change; |
| ArrayList changesPerColumn = (ArrayList)columnChanges.get(columnChange.getChangedColumn()); |
| |
| if (changesPerColumn == null) |
| { |
| changesPerColumn = new ArrayList(); |
| columnChanges.put(columnChange.getChangedColumn(), changesPerColumn); |
| } |
| changesPerColumn.add(change); |
| } |
| } |
| if (columnChanges != null) |
| { |
| for (Iterator changesPerColumnIt = columnChanges.entrySet().iterator(); changesPerColumnIt.hasNext();) |
| { |
| Map.Entry entry = (Map.Entry)changesPerColumnIt.next(); |
| Column sourceColumn = (Column)entry.getKey(); |
| ArrayList changesPerColumn = (ArrayList)entry.getValue(); |
| |
| // Sybase does not like us to use the ALTER TABLE ALTER statement if we don't actually |
| // change the datatype or the required constraint but only the default value |
| // Thus, if we only have to change the default, we use a different handler |
| if ((changesPerColumn.size() == 1) && (changesPerColumn.get(0) instanceof ColumnDefaultValueChange)) |
| { |
| processChange(currentModel, |
| desiredModel, |
| (ColumnDefaultValueChange)changesPerColumn.get(0)); |
| } |
| else |
| { |
| Column targetColumn = targetTable.findColumn(sourceColumn.getName(), |
| getPlatform().isDelimitedIdentifierModeOn()); |
| |
| processColumnChange(sourceTable, targetTable, sourceColumn, targetColumn); |
| } |
| for (Iterator changeIt = changesPerColumn.iterator(); changeIt.hasNext();) |
| { |
| ((ColumnChange)changeIt.next()).apply(currentModel, getPlatform().isDelimitedIdentifierModeOn()); |
| } |
| } |
| } |
| // Finally we add primary keys |
| for (Iterator changeIt = changes.iterator(); changeIt.hasNext();) |
| { |
| TableChange change = (TableChange)changeIt.next(); |
| |
| if (change instanceof AddPrimaryKeyChange) |
| { |
| processChange(currentModel, desiredModel, (AddPrimaryKeyChange)change); |
| changeIt.remove(); |
| } |
| else if (change instanceof PrimaryKeyChange) |
| { |
| PrimaryKeyChange pkChange = (PrimaryKeyChange)change; |
| AddPrimaryKeyChange addPkChange = new AddPrimaryKeyChange(pkChange.getChangedTable(), |
| pkChange.getNewPrimaryKeyColumns()); |
| |
| processChange(currentModel, desiredModel, addPkChange); |
| changeIt.remove(); |
| } |
| } |
| } |
| |
| |
| /** |
| * Processes the addition of a column to a table. |
| * |
| * @param currentModel The current database schema |
| * @param desiredModel The desired database schema |
| * @param change The change object |
| */ |
| protected void processChange(Database currentModel, |
| Database desiredModel, |
| AddColumnChange change) throws IOException |
| { |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(change.getChangedTable())); |
| printIndent(); |
| print("ADD "); |
| writeColumn(change.getChangedTable(), change.getNewColumn()); |
| printEndOfStatement(); |
| change.apply(currentModel, getPlatform().isDelimitedIdentifierModeOn()); |
| } |
| |
| /** |
| * Processes the removal of a column from a table. |
| * |
| * @param currentModel The current database schema |
| * @param desiredModel The desired database schema |
| * @param change The change object |
| */ |
| protected void processChange(Database currentModel, |
| Database desiredModel, |
| RemoveColumnChange change) throws IOException |
| { |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(change.getChangedTable())); |
| printIndent(); |
| print("DROP "); |
| printIdentifier(getColumnName(change.getChangedColumn())); |
| printEndOfStatement(); |
| change.apply(currentModel, getPlatform().isDelimitedIdentifierModeOn()); |
| } |
| |
| /** |
| * Processes the removal of a primary key from a table. |
| * |
| * @param currentModel The current database schema |
| * @param desiredModel The desired database schema |
| * @param change The change object |
| */ |
| protected void processChange(Database currentModel, |
| Database desiredModel, |
| RemovePrimaryKeyChange change) throws IOException |
| { |
| // TODO: this would be easier when named primary keys are supported |
| // because then we can use ALTER TABLE DROP |
| String tableName = getTableName(change.getChangedTable()); |
| String tableNameVar = "tn" + createUniqueIdentifier(); |
| String constraintNameVar = "cn" + createUniqueIdentifier(); |
| |
| println("BEGIN"); |
| println(" DECLARE @" + tableNameVar + " nvarchar(60), @" + constraintNameVar + " nvarchar(60)"); |
| println(" WHILE EXISTS(SELECT sysindexes.name"); |
| println(" FROM sysindexes, sysobjects"); |
| print(" WHERE sysobjects.name = "); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| println(" AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048) > 0)"); |
| println(" BEGIN"); |
| println(" SELECT @" + tableNameVar + " = sysobjects.name, @" + constraintNameVar + " = sysindexes.name"); |
| println(" FROM sysindexes, sysobjects"); |
| print(" WHERE sysobjects.name = "); |
| printAlwaysSingleQuotedIdentifier(tableName); |
| print(" AND sysobjects.id = sysindexes.id AND (sysindexes.status & 2048) > 0"); |
| println(" EXEC ('ALTER TABLE '+@" + tableNameVar + "+' DROP CONSTRAINT '+@" + constraintNameVar + ")"); |
| println(" END"); |
| print("END"); |
| printEndOfStatement(); |
| change.apply(currentModel, getPlatform().isDelimitedIdentifierModeOn()); |
| } |
| |
| /** |
| * Processes the change of the default value of a column. Note that this method is only |
| * used if it is the only change to that column. |
| * |
| * @param currentModel The current database schema |
| * @param desiredModel The desired database schema |
| * @param change The change object |
| */ |
| protected void processChange(Database currentModel, |
| Database desiredModel, |
| ColumnDefaultValueChange change) throws IOException |
| { |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(change.getChangedTable())); |
| printIndent(); |
| print("REPLACE "); |
| printIdentifier(getColumnName(change.getChangedColumn())); |
| |
| Table curTable = currentModel.findTable(change.getChangedTable().getName(), getPlatform().isDelimitedIdentifierModeOn()); |
| Column curColumn = curTable.findColumn(change.getChangedColumn().getName(), getPlatform().isDelimitedIdentifierModeOn()); |
| |
| print(" DEFAULT "); |
| if (isValidDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode())) |
| { |
| printDefaultValue(change.getNewDefaultValue(), curColumn.getTypeCode()); |
| } |
| else |
| { |
| print("NULL"); |
| } |
| printEndOfStatement(); |
| change.apply(currentModel, getPlatform().isDelimitedIdentifierModeOn()); |
| } |
| |
| /** |
| * Processes a change to a column. |
| * |
| * @param sourceTable The current table |
| * @param targetTable The desired table |
| * @param sourceColumn The current column |
| * @param targetColumn The desired column |
| */ |
| protected void processColumnChange(Table sourceTable, |
| Table targetTable, |
| Column sourceColumn, |
| Column targetColumn) throws IOException |
| { |
| Object oldParsedDefault = sourceColumn.getParsedDefaultValue(); |
| Object newParsedDefault = targetColumn.getParsedDefaultValue(); |
| String newDefault = targetColumn.getDefaultValue(); |
| boolean defaultChanges = ((oldParsedDefault == null) && (newParsedDefault != null)) || |
| ((oldParsedDefault != null) && !oldParsedDefault.equals(newParsedDefault)); |
| |
| // Sybase does not like it if there is a default spec in the ALTER TABLE ALTER |
| // statement; thus we have to change the default afterwards |
| if (newDefault != null) |
| { |
| targetColumn.setDefaultValue(null); |
| } |
| if (defaultChanges) |
| { |
| // we're first removing the default as it might make problems when the |
| // datatype changes |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(sourceTable)); |
| printIndent(); |
| print("REPLACE "); |
| printIdentifier(getColumnName(sourceColumn)); |
| print(" DEFAULT NULL"); |
| printEndOfStatement(); |
| } |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(sourceTable)); |
| printIndent(); |
| print("MODIFY "); |
| writeColumn(sourceTable, targetColumn); |
| printEndOfStatement(); |
| if (defaultChanges) |
| { |
| print("ALTER TABLE "); |
| printlnIdentifier(getTableName(sourceTable)); |
| printIndent(); |
| print("REPLACE "); |
| printIdentifier(getColumnName(sourceColumn)); |
| if (newDefault != null) |
| { |
| writeColumnDefaultValueStmt(sourceTable, targetColumn); |
| } |
| else |
| { |
| print(" DEFAULT NULL"); |
| } |
| printEndOfStatement(); |
| } |
| } |
| } |