📄 mssqlbuilder.java
字号:
printEndOfStatement();
}
super.writeCopyDataStatement(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}
*/
protected void processChanges(Database currentModel, Database desiredModel, List changes, CreationParameters params) throws IOException
{
if (!changes.isEmpty())
{
writeQuotationOnStatement();
}
// For column data type and size changes, we need to drop and then re-create indexes
// and foreign keys using the column, as well as any primary keys containg
// these columns
// However, if the index/foreign key/primary key is already slated for removal or
// change, then we don't want to generate change duplication
HashSet removedIndexes = new HashSet();
HashSet removedForeignKeys = new HashSet();
HashSet removedPKs = new HashSet();
for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
{
Object change = changeIt.next();
if (change instanceof RemoveIndexChange)
{
removedIndexes.add(((RemoveIndexChange)change).getIndex());
}
else if (change instanceof RemoveForeignKeyChange)
{
removedForeignKeys.add(((RemoveForeignKeyChange)change).getForeignKey());
}
else if (change instanceof RemovePrimaryKeyChange)
{
removedPKs.add(((RemovePrimaryKeyChange)change).getChangedTable());
}
}
ArrayList additionalChanges = new ArrayList();
for (Iterator changeIt = changes.iterator(); changeIt.hasNext();)
{
Object change = changeIt.next();
if ((change instanceof ColumnDataTypeChange) ||
(change instanceof ColumnSizeChange))
{
Column column = ((ColumnChange)change).getChangedColumn();
Table table = ((ColumnChange)change).getChangedTable();
if (column.isPrimaryKey() && !removedPKs.contains(table))
{
Column[] pk = table.getPrimaryKeyColumns();
additionalChanges.add(new RemovePrimaryKeyChange(table, pk));
additionalChanges.add(new AddPrimaryKeyChange(table, pk));
removedPKs.add(table);
}
for (int idx = 0; idx < table.getIndexCount(); idx++)
{
Index index = table.getIndex(idx);
if (index.hasColumn(column) && !removedIndexes.contains(index))
{
additionalChanges.add(new RemoveIndexChange(table, index));
additionalChanges.add(new AddIndexChange(table, index));
removedIndexes.add(index);
}
}
for (int tableIdx = 0; tableIdx < currentModel.getTableCount(); tableIdx++)
{
Table curTable = currentModel.getTable(tableIdx);
for (int fkIdx = 0; fkIdx < curTable.getForeignKeyCount(); fkIdx++)
{
ForeignKey curFk = curTable.getForeignKey(fkIdx);
if ((curFk.hasLocalColumn(column) || curFk.hasForeignColumn(column)) &&
!removedForeignKeys.contains(curFk))
{
additionalChanges.add(new RemoveForeignKeyChange(curTable, curFk));
additionalChanges.add(new AddForeignKeyChange(curTable, curFk));
removedForeignKeys.add(curFk);
}
}
}
}
}
changes.addAll(additionalChanges);
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);
}
}
ArrayList columnChanges = new ArrayList();
// 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;
// Sql Server 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)
{
// Sql Server 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
columnChanges.add(change);
}
}
if (columnChanges != null)
{
HashSet processedColumns = new HashSet();
for (Iterator changeIt = columnChanges.iterator(); changeIt.hasNext();)
{
ColumnChange change = (ColumnChange)changeIt.next();
Column sourceColumn = change.getChangedColumn();
Column targetColumn = targetTable.findColumn(sourceColumn.getName(), getPlatform().isDelimitedIdentifierModeOn());
if (!processedColumns.contains(targetColumn))
{
processColumnChange(sourceTable,
targetTable,
sourceColumn,
targetColumn,
(change instanceof ColumnDataTypeChange) || (change instanceof ColumnSizeChange));
processedColumns.add(targetColumn);
}
changes.remove(change);
change.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 COLUMN ");
printIdentifier(getColumnName(change.getColumn()));
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(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("END");
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
* @param typeChange Whether this is a type change
*/
protected void processColumnChange(Table sourceTable,
Table targetTable,
Column sourceColumn,
Column targetColumn,
boolean typeChange) throws IOException
{
boolean hasDefault = sourceColumn.getParsedDefaultValue() != null;
boolean shallHaveDefault = targetColumn.getParsedDefaultValue() != null;
String newDefault = targetColumn.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)
{
targetColumn.setDefaultValue(null);
}
if (hasDefault)
{
// we're dropping the old default
String tableName = getTableName(sourceTable);
String columnName = getColumnName(sourceColumn);
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");
println(" WHERE objs.xtype = 'D' AND");
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();
}
print("ALTER TABLE ");
printlnIdentifier(getTableName(sourceTable));
printIndent();
print("ALTER COLUMN ");
writeColumn(sourceTable, targetColumn);
printEndOfStatement();
if (shallHaveDefault)
{
targetColumn.setDefaultValue(newDefault);
// if the column shall have a default, then we have to add it as a constraint
print("ALTER TABLE ");
printlnIdentifier(getTableName(sourceTable));
printIndent();
print("ADD CONSTRAINT ");
printIdentifier(getConstraintName("DF", sourceTable, sourceColumn.getName(), null));
writeColumnDefaultValueStmt(sourceTable, targetColumn);
print(" FOR ");
printIdentifier(getColumnName(sourceColumn));
printEndOfStatement();
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -