public override void CanDropColumnWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE `TestTable1` DROP COLUMN `TestColumn1`");
}
public override void CanDropColumn()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
string sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [TestTable1] DROP COLUMN [TestColumn1]");
}
public override void CanDropMultipleColumnsWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "\"TestColumn1\"", "\"TestColumn2\"" });
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP COLUMN \"TestColumn1\";" + System.Environment.NewLine + "ALTER TABLE \"TestTable1\" DROP COLUMN \"TestColumn2\"");
}
public override void CanDropMultipleColumnsWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new [] { "TestColumn1", "TestColumn2" });
var result = Generator.Generate(expression);
result.ShouldBe(string.Empty);
}
public override void CanDropColumnWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
var result = Generator.Generate(expression);
result.ShouldBe(string.Empty);
}
public override void CanDropMultipleColumnsWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP (\"TestColumn1\"); ALTER TABLE \"TestTable1\" DROP (\"TestColumn2\");");
}
public void CanDropMultipleColumns()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new string[] { "TestColumn1", "TestColumn2" });
string sql = _generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [TestTable1] DROP COLUMN [TestColumn1]; ALTER TABLE [TestTable1] DROP COLUMN [TestColumn2]");
}
public void CanDropColumn()
{
//This does not work if column in used in constraint, index etc.
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
var sql = generator.Generate(expression);
sql.ShouldBe("ALTER TABLE [TestTable1] DROP COLUMN [TestColumn1];");
}
public override void CanDropColumnWithDefaultSchema()
{
//This does not work if column in used in constraint, index etc.
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE [TestTable1] DROP COLUMN [TestColumn1];");
}
public override void CanDropMultipleColumnsWithCustomSchema()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
Assert.Throws <DatabaseOperationNotSupportedException>(() => Generator.Generate(expression));
}
public override void CanDropColumn()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
var sql = generator.Generate(expression);
var expectedSql = "\n\t\t\tDECLARE @default sysname, @sql nvarchar(max);\n\n\t\t\t-- get name of default constraint\n\t\t\tSELECT @default = name\n\t\t\tFROM sys.default_constraints \n\t\t\tWHERE parent_object_id = object_id('[TestTable1]')\n\t\t\tAND type = 'D'\n\t\t\tAND parent_column_id = (\n\t\t\t\tSELECT column_id \n\t\t\t\tFROM sys.columns \n\t\t\t\tWHERE object_id = object_id('[TestTable1]')\n\t\t\t\tAND name = '[TestColumn1]'\n\t\t\t);\n\n\t\t\t-- create alter table command as string and run it\n\t\t\tSET @sql = N'ALTER TABLE [TestTable1] DROP CONSTRAINT ' + @default;\n\t\t\tEXEC sp_executesql @sql;\n\n\t\t\t-- now we can finally drop column\n\t\t\tALTER TABLE [TestTable1] DROP COLUMN [TestColumn1];";
sql.ShouldBe(expectedSql);
}
public override void CanDropColumn()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
string sql = _generator.Generate(expression);
sql.ShouldBe("ALTER TABLE TestTable1 DROP COLUMN TestColumn1");
sql = quotedIdentiferGenerator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"TestTable1\" DROP COLUMN \"TestColumn1\"");
}
public override void CanDropMultipleColumnsWithCustomSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP \"TestColumn1\";" + System.Environment.NewLine + "ALTER TABLE \"TestTable1\" DROP \"TestColumn2\"");
}
public override void CanDropMultipleColumnsWithCustomSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE TestTable1 DROP TestColumn1; ALTER TABLE TestTable1 DROP TestColumn2");
}
public override void CanDropColumnWithCustomSchema()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP \"TestColumn1\"");
}
public override void CanDropMultipleColumnsWithDefaultSchema()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
var expected = "ALTER TABLE [dbo].[TestTable1] DROP [TestColumn1]; ALTER TABLE [dbo].[TestTable1] DROP [TestColumn2]";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public override void CanDropColumnWithCustomSchema()
{
Assert.Ignore("Hana support change default value with type like bellow");
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP (\"TestColumn1\")");
}
public void CanDropMultipleColumns()
{
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new string[] { "TestColumn1", "TestColumn2" });
string sql = _generator.Generate(expression);
sql.ShouldBe("ALTER TABLE TestTable1 DROP COLUMN TestColumn1;" + Environment.NewLine + "ALTER TABLE TestTable1 DROP COLUMN TestColumn2");
expression = GeneratorTestHelper.GetDeleteColumnExpression(new string[] { "\"TestColumn1\"", "\"TestColumn2\"" });
sql = quotedIdentiferGenerator.Generate(expression);
sql.ShouldBe("ALTER TABLE \"TestTable1\" DROP COLUMN \"TestColumn1\";" + Environment.NewLine + "ALTER TABLE \"TestTable1\" DROP COLUMN \"TestColumn2\"");
}
public override void CanDropColumnWithCustomSchema()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression();
expression.SchemaName = "TestSchema";
var expected = "ALTER TABLE [TestSchema].[TestTable1] DROP [TestColumn1]";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public override void CanDropMultipleColumnsWithCustomSchema()
{
Assert.Ignore("Hana support change default value with type like bellow");
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new[] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" DROP (\"TestColumn1\");"
+ System.Environment.NewLine +
"ALTER TABLE \"TestTable1\" DROP (\"TestColumn2\")");
}
public override void CanDropMultipleColumnsWithCustomSchema()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new [] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
var expected = "DECLARE @default sysname, @sql nvarchar(max);" + System.Environment.NewLine + System.Environment.NewLine +
"-- get name of default constraint" + System.Environment.NewLine +
"SELECT @default = name" + System.Environment.NewLine +
"FROM sys.default_constraints" + System.Environment.NewLine +
"WHERE parent_object_id = object_id('[TestSchema].[TestTable1]')" + System.Environment.NewLine +
"AND type = 'D'" + System.Environment.NewLine +
"AND parent_column_id = (" + System.Environment.NewLine +
"SELECT column_id" + System.Environment.NewLine +
"FROM sys.columns" + System.Environment.NewLine +
"WHERE object_id = object_id('[TestSchema].[TestTable1]')" + System.Environment.NewLine +
"AND name = 'TestColumn1'" + System.Environment.NewLine +
");" + System.Environment.NewLine + System.Environment.NewLine +
"-- create alter table command to drop constraint as string and run it" + System.Environment.NewLine +
"SET @sql = N'ALTER TABLE [TestSchema].[TestTable1] DROP CONSTRAINT ' + QUOTENAME(@default);" + System.Environment.NewLine +
"EXEC sp_executesql @sql;" + System.Environment.NewLine + System.Environment.NewLine +
"-- now we can finally drop column" + System.Environment.NewLine +
"ALTER TABLE [TestSchema].[TestTable1] DROP COLUMN [TestColumn1];" + System.Environment.NewLine +
"GO" + System.Environment.NewLine +
"DECLARE @default sysname, @sql nvarchar(max);" + System.Environment.NewLine + System.Environment.NewLine +
"-- get name of default constraint" + System.Environment.NewLine +
"SELECT @default = name" + System.Environment.NewLine +
"FROM sys.default_constraints" + System.Environment.NewLine +
"WHERE parent_object_id = object_id('[TestSchema].[TestTable1]')" + System.Environment.NewLine +
"AND type = 'D'" + System.Environment.NewLine +
"AND parent_column_id = (" + System.Environment.NewLine +
"SELECT column_id" + System.Environment.NewLine +
"FROM sys.columns" + System.Environment.NewLine +
"WHERE object_id = object_id('[TestSchema].[TestTable1]')" + System.Environment.NewLine +
"AND name = 'TestColumn2'" + System.Environment.NewLine +
");" + System.Environment.NewLine + System.Environment.NewLine +
"-- create alter table command to drop constraint as string and run it" + System.Environment.NewLine +
"SET @sql = N'ALTER TABLE [TestSchema].[TestTable1] DROP CONSTRAINT ' + QUOTENAME(@default);" + System.Environment.NewLine +
"EXEC sp_executesql @sql;" + System.Environment.NewLine + System.Environment.NewLine +
"-- now we can finally drop column" + System.Environment.NewLine +
"ALTER TABLE [TestSchema].[TestTable1] DROP COLUMN [TestColumn2];" + System.Environment.NewLine;
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public void CanDropMultipleColumnsWithCustomSchema()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new string[] { "TestColumn1", "TestColumn2" });
expression.SchemaName = "TestSchema";
var sql = generator.Generate(expression);
const string expected = "DECLARE @default sysname, @sql nvarchar(max);\r\n\r\n" +
"-- get name of default constraint\r\n" +
"SELECT @default = name\r\n" +
"FROM sys.default_constraints\r\n" +
"WHERE parent_object_id = object_id('[TestSchema].[TestTable1]')\r\n" + "" +
"AND type = 'D'\r\n" + "" +
"AND parent_column_id = (\r\n" + "" +
"SELECT column_id\r\n" +
"FROM sys.columns\r\n" +
"WHERE object_id = object_id('[TestSchema].[TestTable1]')\r\n" +
"AND name = 'TestColumn1'\r\n" +
");\r\n\r\n" +
"-- create alter table command to drop contraint as string and run it\r\n" +
"SET @sql = N'ALTER TABLE [TestSchema].[TestTable1] DROP CONSTRAINT ' + @default;\r\n" +
"EXEC sp_executesql @sql;\r\n\r\n" +
"-- now we can finally drop column\r\n" +
"ALTER TABLE [TestSchema].[TestTable1] DROP COLUMN [TestColumn1];\r\n" +
"GO\r\n" +
"DECLARE @default sysname, @sql nvarchar(max);\r\n\r\n" +
"-- get name of default constraint\r\n" +
"SELECT @default = name\r\n" +
"FROM sys.default_constraints\r\n" +
"WHERE parent_object_id = object_id('[TestSchema].[TestTable1]')\r\n" + "" +
"AND type = 'D'\r\n" + "" +
"AND parent_column_id = (\r\n" + "" +
"SELECT column_id\r\n" +
"FROM sys.columns\r\n" +
"WHERE object_id = object_id('[TestSchema].[TestTable1]')\r\n" +
"AND name = 'TestColumn2'\r\n" +
");\r\n\r\n" +
"-- create alter table command to drop contraint as string and run it\r\n" +
"SET @sql = N'ALTER TABLE [TestSchema].[TestTable1] DROP CONSTRAINT ' + @default;\r\n" +
"EXEC sp_executesql @sql;\r\n\r\n" +
"-- now we can finally drop column\r\n" +
"ALTER TABLE [TestSchema].[TestTable1] DROP COLUMN [TestColumn2];\r\n";
sql.ShouldBe(expected);
}
public void CanDropMultipleColumns()
{
//This does not work if it is a primary key
var expression = GeneratorTestHelper.GetDeleteColumnExpression(new [] { "TestColumn1", "TestColumn2" });
var sql = _generator.Generate(expression);
string expected = "DECLARE @default sysname, @sql nvarchar(4000);" + Environment.NewLine + Environment.NewLine +
"-- get name of default constraint" + Environment.NewLine +
"SELECT @default = name" + Environment.NewLine +
"FROM sys.default_constraints" + Environment.NewLine +
"WHERE parent_object_id = object_id('[TestTable1]')" + Environment.NewLine + "" +
"AND type = 'D'" + Environment.NewLine +
"AND parent_column_id = (" + Environment.NewLine +
"SELECT column_id" + Environment.NewLine +
"FROM sys.columns" + Environment.NewLine +
"WHERE object_id = object_id('[TestTable1]')" + Environment.NewLine +
"AND name = 'TestColumn1'" + Environment.NewLine +
");" + Environment.NewLine + Environment.NewLine +
"-- create alter table command to drop constraint as string and run it" + Environment.NewLine +
"SET @sql = N'ALTER TABLE [TestTable1] DROP CONSTRAINT ' + @default;" + Environment.NewLine +
"EXEC sp_executesql @sql;" + Environment.NewLine + Environment.NewLine +
"-- now we can finally drop column" + Environment.NewLine +
"ALTER TABLE [TestTable1] DROP COLUMN [TestColumn1];" + Environment.NewLine +
"GO" + Environment.NewLine +
"DECLARE @default sysname, @sql nvarchar(4000);" + Environment.NewLine + Environment.NewLine +
"-- get name of default constraint" + Environment.NewLine +
"SELECT @default = name" + Environment.NewLine +
"FROM sys.default_constraints" + Environment.NewLine +
"WHERE parent_object_id = object_id('[TestTable1]')" + Environment.NewLine +
"AND type = 'D'" + Environment.NewLine +
"AND parent_column_id = (" + Environment.NewLine +
"SELECT column_id" + Environment.NewLine +
"FROM sys.columns" + Environment.NewLine +
"WHERE object_id = object_id('[TestTable1]')" + Environment.NewLine +
"AND name = 'TestColumn2'" + Environment.NewLine +
");" + Environment.NewLine + Environment.NewLine +
"-- create alter table command to drop constraint as string and run it" + Environment.NewLine +
"SET @sql = N'ALTER TABLE [TestTable1] DROP CONSTRAINT ' + @default;" + Environment.NewLine +
"EXEC sp_executesql @sql;" + Environment.NewLine + Environment.NewLine +
"-- now we can finally drop column" + Environment.NewLine +
"ALTER TABLE [TestTable1] DROP COLUMN [TestColumn2];" + Environment.NewLine;
sql.ShouldBe(expected);
}
