public void CanAlterDefaultConstraintWithCurrentDateTimeOffsetAsDefault()
{
var expression = GeneratorTestHelper.GetAlterDefaultConstraintExpression();
expression.DefaultValue = SystemMethods.CurrentDateTimeOffset;
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(SYSDATETIMEOFFSET()) FOR [TestColumn1];";
var result = Generator.Generate(expression);
result.ShouldBe(expected);
}
public void CanCreateTableWithDateTimeOffsetColumn()
{
var expression = new CreateTableExpression {
TableName = "TestTable1"
};
expression.Columns.Add(new ColumnDefinition {
TableName = "TestTable1", Name = "TestColumn1", Type = DbType.DateTimeOffset
});
expression.Columns.Add(new ColumnDefinition {
TableName = "TestTable1", Name = "TestColumn2", Type = DbType.DateTime2
});
expression.Columns.Add(new ColumnDefinition {
TableName = "TestTable1", Name = "TestColumn3", Type = DbType.Date
});
expression.Columns.Add(new ColumnDefinition {
TableName = "TestTable1", Name = "TestColumn4", Type = DbType.Time
});
var result = Generator.Generate(expression);
result.ShouldBe("CREATE TABLE [dbo].[TestTable1] ([TestColumn1] DATETIMEOFFSET NOT NULL, [TestColumn2] DATETIME2 NOT NULL, [TestColumn3] DATE NOT NULL, [TestColumn4] TIME NOT NULL)");
}
public override void CanCreateIndexWithCustomSchema()
{
var expression = GeneratorTestHelper.GetCreateIndexExpression();
expression.Index.SchemaName = "TestSchema";
var result = _generator.Generate(expression);
result.ShouldBe("CREATE INDEX [TestIndex] ON [TestSchema].[TestTable1] ([TestColumn1] ASC)");
}
public void CanCreateTableWithSparseCol()
{
var expression = new CreateTableExpression()
{
TableName = "TestTable1",
};
var serviceProvider = new ServiceCollection().BuildServiceProvider();
var querySchema = new Mock <IQuerySchema>();
new CreateTableExpressionBuilder(expression, new MigrationContext(querySchema.Object, serviceProvider, null, null))
.WithColumn("Id").AsGuid().PrimaryKey()
.WithColumn("TestSparse").AsString(255).Nullable().Sparse();
var result = Generator.Generate(expression);
result.ShouldBe("CREATE TABLE [dbo].[TestTable1] ([Id] UNIQUEIDENTIFIER NOT NULL, [TestSparse] NVARCHAR(255) SPARSE, PRIMARY KEY ([Id]))");
}