public void CanAlterDefaultConstraint()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
var sql = _generator.Generate(expression);
const string expected = "DECLARE @default sysname, @sql nvarchar(4000);\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('[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('[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 [TestTable1] DROP CONSTRAINT ' + @default;\r\n" +
"EXEC sp_executesql @sql;\r\n\r\n" +
"-- create alter table command to create new default constraint as string and run it\r\n" +
"ALTER TABLE [TestTable1] WITH NOCHECK ADD CONSTRAINT DF_TestTable1_TestColumn1 DEFAULT(1) FOR [TestColumn1];";
sql.ShouldBe(expected);
}
public void CanAlterDefaultConstraintWithDefaultSchema()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
string expected = "DECLARE @default sysname, @sql nvarchar(max);" + 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('[dbo].[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('[dbo].[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 [dbo].[TestTable1] DROP CONSTRAINT ' + QUOTENAME(@default);" + Environment.NewLine +
"EXEC sp_executesql @sql;" + Environment.NewLine + Environment.NewLine +
"-- create alter table command to create new default constraint as string and run it" + Environment.NewLine +
"ALTER TABLE [dbo].[TestTable1] WITH NOCHECK ADD CONSTRAINT [DF_TestTable1_TestColumn1] DEFAULT(1) FOR [TestColumn1];";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public void CanAlterDefaultConstraintWithCurrentUtcDateAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentUTCDateTime;
var sql = generator.Generate(expression);
string expected = "DECLARE @default sysname, @sql nvarchar(max);" + 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('[dbo].[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('[dbo].[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 [dbo].[TestTable1] DROP CONSTRAINT ' + @default;" + Environment.NewLine +
"EXEC sp_executesql @sql;" + Environment.NewLine + Environment.NewLine +
"-- create alter table command to create new default constraint as string and run it" + Environment.NewLine +
"ALTER TABLE [dbo].[TestTable1] WITH NOCHECK ADD CONSTRAINT [DF_TestTable1_TestColumn1] DEFAULT(GETUTCDATE()) FOR [TestColumn1];";
sql.ShouldBe(expected);
}
public void CanAlterDefaultConstraintWithValueAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" MODIFY \"TestColumn1\" DEFAULT 1");
}
public void CanAlterDefaultConstraintForCustomSchemaWithValueAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.SchemaName = "USER";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"USER\".\"TestTable1\" MODIFY \"TestColumn1\" DEFAULT 1");
}
public void CanAlterDefaultConstraintWithDefaultSystemMethodCurrentUser()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentUser;
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" MODIFY \"TestColumn1\" DEFAULT USER");
}
public void CanAlterDefaultConstraintWithDefaultSystemMethodCurrentDateTime()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTime;
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE TestTable1 MODIFY TestColumn1 DEFAULT LOCALTIMESTAMP");
}
public void CanAlterDefaultConstraintWithDefaultSystemMethodNewGuid()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.NewGuid;
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE TestTable1 MODIFY TestColumn1 DEFAULT sys_guid()");
}
public void CanAlterDefaultConstraintWithStringValueAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = "1";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE TestTable1 MODIFY TestColumn1 DEFAULT '1'");
}
public void CanAlterDefaultConstraintToCurrentTimestamp()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTime;
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE `TestTable1` ALTER `TestColumn1` SET DEFAULT CURRENT_TIMESTAMP");
}
public void CanAlterDefaultConstraintForCustomSchemaWithDefaultSystemMethodCurrentDateTime()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTime;
expression.SchemaName = "USER";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"USER\".\"TestTable1\" MODIFY \"TestColumn1\" DEFAULT LOCALTIMESTAMP");
}
public void CanAlterDefaultConstraintForCustomSchemaWithDefaultSystemMethodCurrentUser()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.SchemaName = "USER";
expression.DefaultValue = SystemMethods.CurrentUser;
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE USER.TestTable1 MODIFY TestColumn1 DEFAULT USER");
}
public void CanAlterDefaultConstraintForCustomSchemaWithDefaultSystemMethodNewGuid()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.NewGuid;
expression.SchemaName = "USER";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"USER\".\"TestTable1\" MODIFY \"TestColumn1\" DEFAULT sys_guid()");
}
public void CanAlterDefaultConstraintToCurrentDate()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTime;
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE TestSchema.TestTable1 ALTER COLUMN TestColumn1 SET DEFAULT CURRENT_TIMESTAMP");
}
public void CanAlterDefaultConstraintToCurrentUtcDateTime()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentUTCDateTime;
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestSchema\".\"TestTable1\" ALTER \"TestColumn1\" DROP DEFAULT, ALTER \"TestColumn1\" SET DEFAULT (now() at time zone 'UTC');");
}
public void CanAlterDefaultConstraintToNewGuid()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.NewGuid;
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestSchema\".\"TestTable1\" ALTER \"TestColumn1\" DROP DEFAULT, ALTER \"TestColumn1\" SET DEFAULT uuid_generate_v4();");
}
public void CanAlterDefaultConstraintToCurrentDate()
{
Assert.Ignore("Hana support change default value with type like bellow");
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTime;
expression.SchemaName = "TestSchema";
var result = Generator.Generate(expression);
result.ShouldBe("ALTER TABLE \"TestTable1\" ALTER (\"TestColumn1\" NVARCHAR(255) DEFAULT CURRENT_TIMESTAMP)");
}
public void CanAlterDefaultConstraintWithNewGuidAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.NewGuid;
var expected = "ALTER TABLE [dbo].[TestTable1] ALTER [TestColumn1] DROP DEFAULT;" + Environment.NewLine +
"-- create alter table command to create new default constraint as string and run it" + Environment.NewLine +
"ALTER TABLE [dbo].[TestTable1] ALTER [TestColumn1] DEFAULT NEWID();";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public void AlterDefaultConstraintThrowsNotSupportedException()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
Assert.Throws <DatabaseOperationNotSupportedException>(() => Generator.Generate(expression));
}
public void AlterDefaultConstraintThrowsNotSupportedException()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
generator.Generate(expression);
}