public void ShouldMergeAllColumnOperationsAcrossUpdateTableOperations()
{
var op = new UpdateTableOperation("schema", "name",
new[] { Column.Int("column1", new Identity(), null), Column.Bit("column2", Nullable.Null, null, null, null) },
new[] { Column.Int("column3", new Identity(), null), Column.Bit("column4", Nullable.Null, null, null, null) },
new[] { "column6", "column7" });
var otherUpdateOp = new UpdateTableOperation("SCHEMA", "NAME",
new [] { Column.Int("COLUMN7", Nullable.Null, null, null, null), Column.Bit("column8", Nullable.NotNull, null, null, null) },
new [] { Column.BigInt("COLUMN1", new Identity(), null), Column.Bit("column9", Nullable.Null, null, null, null) },
new [] { "COLUMN2", "COLUMN4", "column10" });
op.Merge(otherUpdateOp);
Assert.That(op.Disabled, Is.False);
Assert.That(otherUpdateOp.Disabled, Is.True);
Assert.That(op.AddColumns.Count, Is.EqualTo(3));
Assert.That(op.AddColumns[0].Name, Is.EqualTo("COLUMN1"));
Assert.That(op.AddColumns[0].DataType, Is.EqualTo(DataType.BigInt));
Assert.That(op.AddColumns[1].Name, Is.EqualTo("COLUMN7"));
Assert.That(op.AddColumns[2].Name, Is.EqualTo("column8"));
Assert.That(op.UpdateColumns.Count, Is.EqualTo(2));
Assert.That(op.UpdateColumns[0].Name, Is.EqualTo("column3"));
Assert.That(op.UpdateColumns[1].Name, Is.EqualTo("column9"));
Assert.That(op.RemoveColumns.Count, Is.EqualTo(2));
Assert.That(op.RemoveColumns[0], Is.EqualTo("column6"));
Assert.That(op.RemoveColumns[1], Is.EqualTo("column10"));
}
public void ShouldRenameColumnDefaultConstraintIfMergedWithRenameOperation()
{
var op = new UpdateTableOperation("schema", "table",
new[]
{
Column.Bit("no default", Nullable.NotNull, null, null, null),
Column.Int("name2", Nullable.NotNull, "1", "name", null)
},
new[]
{
Column.Bit("no default2", Nullable.NotNull, null, null, null),
Column.Int("name", Nullable.NotNull, "2", "name2", null)
},
null);
var renameOp = new RenameObjectOperation("SCHEMA", "NAME", "NEW NAME");
var rename2Op = new RenameObjectOperation("SCHEMA", "NAME2", "NEW NAME2");
op.Merge(renameOp);
op.Merge(rename2Op);
Assert.That(op.Disabled, Is.False);
Assert.That(op.Name, Is.EqualTo("table"));
Assert.That(op.AddColumns[1].Name, Is.EqualTo("name2"));
Assert.That(op.AddColumns[1].DefaultConstraintName, Is.EqualTo("NEW NAME"));
Assert.That(op.UpdateColumns[1].Name, Is.EqualTo("name"));
Assert.That(op.UpdateColumns[1].DefaultConstraintName, Is.EqualTo("NEW NAME2"));
Assert.That(renameOp.Disabled, Is.True);
Assert.That(rename2Op.Disabled, Is.True);
}
public void ShouldMergeWithUpdateTableOperationOnce()
{
var columns = new[]
{
Column.Int("ID", Nullable.NotNull, null, null, null),
Column.VarChar("Name", new CharacterLength(500), null, Nullable.NotNull, null, null, null),
};
var addTableOp = new AddTableOperation("skma", "Farmers", columns, false, null, null, null, new string[0]);
var updateNameColumnOp = new UpdateTableOperation("skma", "Farmers",
new[] { Column.VarChar("NAME", new CharacterLength(50), null, Nullable.NotNull, null, null, null) }, null,
null);
var updateZipColumnOp = new UpdateTableOperation("skma", "Farmers",
new[] { Column.VarChar("Zip", new CharacterLength(10), null, Nullable.Null, null, null, null) }, null, null);
var renameZipColumnOp = new RenameColumnOperation("skma", "Farmers", "ZIP", "ZipCode");
updateZipColumnOp.Merge(renameZipColumnOp);
updateNameColumnOp.Merge(renameZipColumnOp);
addTableOp.Merge(renameZipColumnOp);
updateNameColumnOp.Merge(updateZipColumnOp);
addTableOp.Merge(updateZipColumnOp);
addTableOp.Merge(updateNameColumnOp);
Assert.That(addTableOp.Disabled, Is.False);
Assert.That(updateNameColumnOp.Disabled, Is.True);
Assert.That(updateZipColumnOp.Disabled, Is.True);
Assert.That(renameZipColumnOp.Disabled, Is.True);
Assert.That(addTableOp.Columns.Count, Is.EqualTo(3));
Assert.That(addTableOp.Columns[0].Name, Is.EqualTo("ID"));
Assert.That(addTableOp.Columns[1].Name, Is.EqualTo("NAME"));
Assert.That(addTableOp.Columns[2].Name, Is.EqualTo("ZipCode"));
}
public void ShouldReplaceColumnsIfUpdatedByMerge()
{
var columns = new Column[]
{
Column.Int("id", null, "description"),
Column.VarChar("name", new CharacterLength(50), "collation", Nullable.Null, null, null, "description"),
Column.Char("flag", new CharacterLength(1), "collation", Nullable.NotNull, null, null, "description")
};
var op = new AddTableOperation("schema", "table", columns, false, "filegroup", "textimagefilegroup", "filetreamfilegroup", new string[0]);
var updatedColumns = new Column[]
{
Column.BigInt("ID", new Identity(), "new_description"),
Column.NVarChar("NAME", new CharacterLength(25), "new collation", Nullable.NotNull, "default", "default constraint name", "new description"),
Column.NVarChar("donotexist", new CharacterLength(25), "new collation", Nullable.NotNull, "default", "default constraint name", "new description")
};
var updateOp = new UpdateTableOperation("schema", "table", null, updatedColumns, null);
op.Merge(updateOp);
Assert.That(op.Disabled, Is.False);
Assert.That(updateOp.Disabled, Is.True);
Assert.That(op.Columns[0], Is.SameAs(updatedColumns[0]));
Assert.That(op.Columns[1], Is.SameAs(updatedColumns[1]));
Assert.That(op.Columns[2], Is.SameAs(columns[2]));
}
public void ShouldNotRemoveUpdateOperationIfItIsADifferentTable()
{
var op = new UpdateTableOperation("schema", "tableName", new Column[0], new Column[0], new string[0]);
var removeTableOp = new RemoveTableOperation("SCHEMA", "tableName2");
op.Merge(removeTableOp);
Assert.That(op.Disabled, Is.False);
Assert.That(removeTableOp.Disabled, Is.False);
}
public void ShouldRemoveUpdateOperationIfTableRemoved()
{
var op = new UpdateTableOperation("schema", "table Name", new Column[0], new Column[0], new string[0]);
var removeTableOp = new RemoveTableOperation("SCHEMA", "TABLE NAME");
op.Merge(removeTableOp);
Assert.That(op.Disabled, Is.True);
Assert.That(removeTableOp.Disabled, Is.True);
}
public void ShouldWriteQueryForUpdateTableWithRemoveOnly()
{
var op = new UpdateTableOperation(SchemaName, Name, null, null, removeColumnList);
var expectedQuery =
string.Format("alter table [schemaName].[name] drop column [column 3]{0}alter table [schemaName].[name] drop column [column 4]", Environment.NewLine);
Assert.AreEqual(expectedQuery, op.ToQuery());
}
public void ShouldDisableOperationWhenItsColumnIsRemoved()
{
var op = new AddExtendedPropertyOperation("schema", "table", "column", "name", "value", false);
var updateTableOp = new UpdateTableOperation("SCHEMA", "TABLE", new Column[0], new Column[0], new[] { "COLUMN" });
op.Merge(updateTableOp);
Assert.That(op.Disabled, Is.True);
Assert.That(updateTableOp.Disabled, Is.False);
}
public void ShouldWriteQueryForUpdateTableWithUpdateOnly()
{
var op = new UpdateTableOperation(SchemaName, Name, null, updateColumnList, null);
var expectedQuery =
$"alter table [schemaName].[name] alter column [int column] int identity not null{Environment.NewLine}" +
$"alter table [schemaName].[name] alter column [name] varchar(50) not null constraint [{DefaultConstraintName}] default ''";
Assert.AreEqual(expectedQuery, op.ToQuery());
}
public void ShouldSetPropertiesForUpdateTable()
{
var op = new UpdateTableOperation(SchemaName, Name, addColumnList, updateColumnList, removeColumnList);
Assert.AreEqual(SchemaName, op.SchemaName);
Assert.AreEqual(Name, op.Name);
Assert.AreEqual(addColumnList, op.AddColumns);
Assert.AreEqual(updateColumnList, op.UpdateColumns);
Assert.That(op.RemoveColumns, Is.EqualTo(removeColumnList));
Assert.That(op.ObjectName, Is.EqualTo($"{SchemaName}.{Name}"));
}
public void ShouldMergeRemovedColumnsAcrossUpdateTableOperations()
{
var op = new UpdateTableOperation("schema", "name", null, null, new [] { "column1" });
var removeColumnOp = new UpdateTableOperation("SCHEMA", "NAME", null, null, new [] { "column2" });
op.Merge(removeColumnOp);
Assert.That(op.Disabled, Is.False);
Assert.That(removeColumnOp.Disabled, Is.True);
Assert.That(op.RemoveColumns.Count, Is.EqualTo(2));
Assert.That(op.RemoveColumns[0], Is.EqualTo("column1"));
Assert.That(op.RemoveColumns[1], Is.EqualTo("column2"));
}
public void ShouldWriteQueryForUpdateTableWithAddUpdateRemove()
{
var op = new UpdateTableOperation(SchemaName, Name, addColumnList, updateColumnList, removeColumnList);
var expectedQuery =
$"alter table [schemaName].[name] add [name] varchar(50) not null constraint [{DefaultConstraintName}] default ''{Environment.NewLine}" +
$"alter table [schemaName].[name] add [int column] int identity not null{Environment.NewLine}" +
$"alter table [schemaName].[name] alter column [int column] int identity not null{Environment.NewLine}" +
$"alter table [schemaName].[name] alter column [name] varchar(50) not null constraint [{DefaultConstraintName}] default ''{Environment.NewLine}" +
$"alter table [schemaName].[name] drop column [column 3]{Environment.NewLine}alter table [schemaName].[name] drop column [column 4]";
Assert.AreEqual(expectedQuery, op.ToQuery());
}
public void ShouldMergeUpdateColumnsAcrossUpdateTableOperations()
{
var op = new UpdateTableOperation("schema", "name", null,
new[] { Column.Int("column1", Nullable.NotNull, null, null, null) }, null);
var updateColumnOp = new UpdateTableOperation("SCHEMA", "NAME", null,
new[] { Column.Bit("column2", Nullable.NotNull, null, null, null) }, null);
op.Merge(updateColumnOp);
Assert.That(op.Disabled, Is.False);
Assert.That(updateColumnOp.Disabled, Is.True);
Assert.That(op.UpdateColumns.Count, Is.EqualTo(2));
Assert.That(op.UpdateColumns[0].Name, Is.EqualTo("column1"));
Assert.That(op.UpdateColumns[1].Name, Is.EqualTo(updateColumnOp.UpdateColumns[0].Name));
}
public void ShouldRemoveDefaultExpressionFromAddedColumnsAndDisableRemovedDefaultConstraint()
{
var columns = new Column[]
{
Column.Int("column1", Nullable.NotNull, null, null, null),
Column.Int("column2", Nullable.NotNull, null, null, null),
};
var op = new UpdateTableOperation("schema", "tableName", columns, new Column[0], new string[0]);
var removeDefaultConstraintOp = new RemoveDefaultConstraintOperation("SCHEMA", "TABLENAME", "COLUMN2", "name");
op.Merge(removeDefaultConstraintOp);
Assert.That(op.Disabled, Is.False);
Assert.That(removeDefaultConstraintOp.Disabled, Is.True);
Assert.That(op.AddColumns[1].DefaultExpression, Is.Null);
}
public void ShouldDisableItselfIfAllColumnsAreRemoved()
{
var op = new UpdateTableOperation("schema", "name",
new[] { Column.Int("column1", new Identity(), null) },
new[] { Column.Int("column2", new Identity(), null) },
null);
var updateOp = new UpdateTableOperation("SCHEMA", "NAME", null, null, new[] { "COLUMN1", "COLUMN2" });
op.Merge(updateOp);
Assert.That(op.Disabled, Is.True);
Assert.That(updateOp.Disabled, Is.True);
Assert.That(op.AddColumns.Count, Is.Zero);
Assert.That(op.UpdateColumns.Count, Is.Zero);
Assert.That(op.RemoveColumns.Count, Is.Zero);
}
public void ShouldRenameUpdatedColumnsIfRenamed()
{
var columns = new Column[]
{
Column.Int("id", null, "description"),
Column.VarChar("name", new CharacterLength(50), "collation", Nullable.Null, null, null, "description")
};
var op = new UpdateTableOperation("schema", "table", new Column[0], columns, new string[0]);
var renameColumnOp = new RenameColumnOperation("SCHEMA", "TABLE", "NAME", "new name");
op.Merge(renameColumnOp);
Assert.That(op.Disabled, Is.False);
Assert.That(renameColumnOp.Disabled, Is.True);
Assert.That(op.UpdateColumns[0].Name, Is.EqualTo("id"));
Assert.That(op.UpdateColumns[1].Name, Is.EqualTo("new name"));
}
public void ShouldDeleteColumnsIfDeletedByMerge()
{
var columns = new []
{
Column.Int("id", null, "description"),
Column.VarChar("name", new CharacterLength(50), "collation", Nullable.Null, null, null, "description"),
Column.Char("flag", new CharacterLength(1), "collation", Nullable.NotNull, null, null, "description")
};
var op = new AddTableOperation("schema", "table", columns, false, "filegroup", "textimagefilegroup", "filetreamfilegroup", new string[0]);
var updateOp = new UpdateTableOperation("schema", "table", null, null, new string[] { "ID", "NAME", "IDONOTEXIST" });
op.Merge(updateOp);
Assert.That(op.Disabled, Is.False);
Assert.That(updateOp.Disabled, Is.True);
Assert.That(op.Columns.Count, Is.EqualTo(1));
Assert.That(op.Columns[0], Is.SameAs(columns[2]));
}
public void ShouldNotAddRowGuidColForAnotherTablesColumn()
{
var columns = new Column[]
{
Column.UniqueIdentifier("uuid", false, Nullable.Null, null, null, "description"),
Column.UniqueIdentifier("uuid2", false, Nullable.Null, null, null, "description"),
Column.Int("uuid", Nullable.Null, null, null, "description"),
};
var op = new UpdateTableOperation("schema", "table", new Column[0], columns, new string[0]);
var addRowGuidColOp = new AddRowGuidColOperation("SCHEMA", "table2", "UUID");
op.Merge(addRowGuidColOp);
Assert.That(op.Disabled, Is.False);
Assert.That(addRowGuidColOp.Disabled, Is.False);
Assert.That(op.UpdateColumns[0].RowGuidCol, Is.False);
Assert.That(op.UpdateColumns[1].RowGuidCol, Is.False);
Assert.That(op.UpdateColumns[2].RowGuidCol, Is.False);
}
public void ShouldAddDefaultExpressionAndConstraintNameToAddedColumnsAndDisableAddDefaultConstraint()
{
var columns = new[]
{
Column.Int("column1", Nullable.NotNull, null, null, "description"),
Column.Int("column2", Nullable.NotNull, null, null, "description"),
};
var op = new UpdateTableOperation("schema", "table", columns, new Column[0], new string[0]);
var addDefaultConstraintOp =
new AddDefaultConstraintOperation("SCHEMA", "TABLE", "NAME", "COLUMN2", "expression", false);
op.Merge(addDefaultConstraintOp);
Assert.That(op.Disabled, Is.False);
Assert.That(addDefaultConstraintOp.Disabled, Is.True);
Assert.That(op.AddColumns[0].DefaultExpression, Is.Null);
Assert.That(op.AddColumns[0].DefaultConstraintName, Is.Null);
Assert.That(op.AddColumns[1].DefaultExpression, Is.EqualTo("expression"));
Assert.That(op.AddColumns[1].DefaultConstraintName, Is.EqualTo("NAME"));
}
public void ShouldMergeAllUpdatedColumns()
{
var op = new UpdateTableOperation("schema", "name", null, new [] { Column.Bit("bit", Nullable.NotNull, null, null, null) }, null);
var op2 = new UpdateTableOperation("schema", "name", null, null, null);
var op3 = new UpdateTableOperation("schema", "name", null, new[] { Column.Bit("BIT", Nullable.Null, null, null, null) }, null);
op2.Merge(op3);
op.Merge(op3);
op.Merge(op2);
Assert.That(op.Disabled, Is.False);
Assert.That(op2.Disabled, Is.True);
Assert.That(op3.Disabled, Is.True);
Assert.That(op.UpdateColumns.Count, Is.EqualTo(1));
Assert.That(op.UpdateColumns[0].Name, Is.EqualTo("BIT"));
Assert.That(op.UpdateColumns[0].Nullable, Is.EqualTo(Nullable.Null));
}
public void ShouldNotAddDefaultExpressionToUpdatedColumnsWhenDefaultConstraintWithValues()
{
var columns = new Column[]
{
Column.Int("column1", Nullable.NotNull, null, null, "description"),
Column.Int("column2", Nullable.NotNull, null, null, "description"),
};
var op = new UpdateTableOperation("schema", "table", new Column[0], columns, new string[0]);
var addDefaultConstraintOp =
new AddDefaultConstraintOperation("SCHEMA", "TABLE", "NAME", "COLUMN2", "expression", true);
op.Merge(addDefaultConstraintOp);
Assert.That(op.Disabled, Is.False);
Assert.That(addDefaultConstraintOp.Disabled, Is.False);
Assert.That(op.UpdateColumns[0].DefaultExpression, Is.Null);
Assert.That(op.UpdateColumns[0].DefaultConstraintName, Is.Null);
Assert.That(op.UpdateColumns[1].DefaultExpression, Is.Null);
Assert.That(op.UpdateColumns[1].DefaultConstraintName, Is.Null);
}
public void ShouldRemoveRowGuidColToUpdatedColumn()
{
var columns = new Column[]
{
Column.UniqueIdentifier("uuid", true, Nullable.Null, null, null, "description"),
Column.UniqueIdentifier("uuid2", true, Nullable.Null, null, null, "description"),
Column.Int("uuid", Nullable.Null, null, null, "description"),
};
columns[2].RowGuidCol = true;
var op = new UpdateTableOperation("schema", "table", new Column[0], columns, new string[0]);
var removeRowGuidColOp = new RemoveRowGuidColOperation("SCHEMA", "TABLE", "UUID");
op.Merge(removeRowGuidColOp);
Assert.That(op.Disabled, Is.False);
Assert.That(removeRowGuidColOp.Disabled, Is.True);
Assert.That(op.UpdateColumns[0].RowGuidCol, Is.False);
Assert.That(op.UpdateColumns[1].RowGuidCol, Is.True);
Assert.That(op.UpdateColumns[2].RowGuidCol, Is.True);
}
public void ShouldOnlyMergeOnceSoMultipleUpdateOperationsDoNotDuplicateOperations()
{
var op = new UpdateTableOperation("schema", "name", null, null, new[] { "column1" });
var op2 = new UpdateTableOperation("schema", "name", null, null, new[] { "column2" });
var op3 = new UpdateTableOperation("schema", "name", null, null, new[] { "column3" });
var op4 = new UpdateTableOperation("schema", "name", null, null, new [] { "column4" });
op3.Merge(op4);
op2.Merge(op4);
op.Merge(op4);
op2.Merge(op3);
op.Merge(op3);
op.Merge(op2);
Assert.That(op.Disabled, Is.False);
Assert.That(op2.Disabled, Is.True);
Assert.That(op3.Disabled, Is.True);
Assert.That(op4.Disabled, Is.True);
Assert.That(op.RemoveColumns.Count, Is.EqualTo(4));
Assert.That(op.RemoveColumns[0], Is.EqualTo("column1"));
Assert.That(op.RemoveColumns[1], Is.EqualTo("column2"));
Assert.That(op.RemoveColumns[2], Is.EqualTo("column3"));
Assert.That(op.RemoveColumns[3], Is.EqualTo("column4"));
}
public void ShouldAddNewColumnsToARenamedTable()
{
var addTableOp = new AddTableOperation("schema", "name", null, false, "file group", "text image file group",
"file stream file group", null);
var renameTableOp = new RenameObjectOperation("schema", "name", "new table");
var newColumns = new[]
{
Column.TinyInt("errorId", Nullable.Null, null, null, "some description")
};
var updateTableOp = new UpdateTableOperation("schema", "new table", newColumns, null, null);
addTableOp.Merge(renameTableOp);
renameTableOp.Merge(updateTableOp);
addTableOp.Merge(updateTableOp);
Assert.That(renameTableOp.Disabled, Is.True);
Assert.That(updateTableOp.Disabled, Is.True);
Assert.That(addTableOp.Disabled, Is.False);
Assert.That(addTableOp.Columns, Is.Not.Empty);
Assert.That(addTableOp.Columns, Has.Count.EqualTo(1));
Assert.That(addTableOp.Columns[0], Is.SameAs(newColumns[0]));
Assert.That(addTableOp.Name, Is.EqualTo("new table"));
}
