public void CanCreateSequence()
{
var expression = new CreateSequenceExpression
{
Sequence =
{
Cache = 10,
Cycle = true,
Increment = 2,
MaxValue = 100,
MinValue = 0,
Name = "Sequence",
SchemaName = "TestSchema",
StartWith = 2
}
};
var sql = generator.Generate(expression);
sql.ShouldBe("CREATE SEQUENCE [TestSchema].[Sequence] INCREMENT BY 2 MINVALUE 0 MAXVALUE 100 START WITH 2 CACHE 10 CYCLE");
}
public void CanGenerateNecessaryStatementsForADeleteDefaultExpression()
{
var expression = new DeleteDefaultConstraintExpression {
ColumnName = "Name", SchemaName = "Personalia", TableName = "Person"
};
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('[Personalia].[Person]')\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('[Personalia].[Person]')\r\n" +
"AND name = 'Name'\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 [Personalia].[Person] DROP CONSTRAINT ' + @default;\r\n" +
"EXEC sp_executesql @sql;";
generator.Generate(expression).ShouldBe(expected);
}
public void CanAlterDefaultConstraintWithCustomSchema()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.SchemaName = "TestSchema";
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('[TestSchema].[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('[TestSchema].[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 [TestSchema].[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 [TestSchema].[TestTable1] WITH NOCHECK ADD CONSTRAINT [DF_TestTable1_TestColumn1] DEFAULT(1) FOR [TestColumn1];";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public virtual void Process(CreateSchemaExpression expression)
{
Process(Generator.Generate(expression));
}