public static CreateTableExpression GetCreateTableWithAutoIncrementExpression()
{
var expression = new CreateTableExpression { TableName = TestTableName1 };
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsIdentity = true, Type = DbType.Int32 });
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
return expression;
}
public static CreateTableExpression GetCreateTableExpression()
{
CreateTableExpression expression = new CreateTableExpression() { TableName = TestTableName1, };
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, Type = DbType.String });
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
return expression;
}
public void ErrorIsReturnedWhenTableNameIsEmptyString()
{
var expression = new CreateTableExpression { TableName = String.Empty };
var errors = ValidationHelper.CollectErrors(expression);
errors.ShouldContain(ErrorMessages.TableNameCannotBeNullOrEmpty);
}
public void CanMigrateTableWithIdentityData()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo"
,
Columns = new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32, IsIdentity = true} }
};
var context = GetDefaultContext();
var insert = new InsertDataExpression()
{
TableName = "Foo"
};
insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 1)});
insert.Rows.Add(new InsertionDataDefinition { new KeyValuePair<string, object>("Id", 100) });
insert.WithIdentity = true;
// Act
MigrateTable(context, create, insert);
// Assert
context.MigrationIndex.ShouldBe(1);
}
public void ErrorIsNotReturnedWhenTableNameIsSet()
{
var expression = new CreateTableExpression { TableName = "table1" };
var errors = ValidationHelper.CollectErrors(expression);
Assert.That(errors.Count, Is.EqualTo(0));
}
public static CreateTableExpression GetCreateTableWithNullableColumn()
{
var expression = new CreateTableExpression { TableName = TestTableName1 };
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsNullable = true, Type = DbType.String });
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32 });
return expression;
}
public string Generate(CreateTableExpression expression)
{
IList<ColumnDefinition> columns = expression.Columns;
string result = "";
int total = columns.Count - 1;
//if more than one column is a primary key or the primary key is given a name, then it needs to be added separately
IList<ColumnDefinition> primaryKeyColumns = GetPrimaryKeyColumns(columns);
bool addPrimaryKeySeparately = false;
if (primaryKeyColumns.Count > 1 || (primaryKeyColumns.Count == 1 && !string.IsNullOrEmpty(primaryKeyColumns[0].PrimaryKeyName)))
{
if(CanSeperatePrimaryKeyAndIdentity || primaryKeyColumns.All(x => !x.IsIdentity))
{
addPrimaryKeySeparately = true;
foreach (ColumnDefinition column in primaryKeyColumns)
{
column.IsPrimaryKey = false;
}
}
}
for (int i = 0; i < columns.Count; i++)
{
result += Generate(columns[i]);
if (i != total)
result += ", ";
}
if (addPrimaryKeySeparately)
result = AddPrimaryKeyConstraint(expression.TableName, primaryKeyColumns, result);
return result;
}
public static CreateTableExpression GetCreateTableWithNamedMultiColumnPrimaryKeyExpression()
{
var expression = new CreateTableExpression { TableName = TestTableName1 };
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, IsPrimaryKey = true, PrimaryKeyName = "TestKey", Type = DbType.String });
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32, IsPrimaryKey = true });
return expression;
}
public static CreateTableExpression GetCreateTableWithDefaultValue()
{
CreateTableExpression expression = new CreateTableExpression() { TableName = TestTableName1, };
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName1, Type = DbType.String, DefaultValue = "Default", TableName = TestTableName1 });
expression.Columns.Add(new ColumnDefinition { Name = TestColumnName2, Type = DbType.Int32, DefaultValue = 0, TableName = TestTableName1 });
return expression;
}
private CreateTableExpression GetCreateTableExpression(string tableName)
{
var columnName1 = "ColumnName1";
var column1 = new ColumnDefinition { Name = columnName1, Type = DbType.String };
var expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(column1);
return expression;
}
public dynamic CreateTableMigration(CreateTableMigration createTableMigration)
{
var create_table_expr = new CreateTableExpression
{
TableName = createTableMigration.TableName,
SchemaName = createTableMigration.SchemaName,
};
create_table_expr.Columns = createTableMigration.Colunns.Select(build_column_definition).ToList();
return create_table_expr;
}
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 string Generate(CreateTableExpression expression)
{
string FederatedOn = "";
if (!(string.IsNullOrEmpty(expression.Federation.DistributionName) || string.IsNullOrEmpty(expression.FederationColumn)))
{
FederatedOn = string.Format(FederateTable, expression.Federation.DistributionName,
expression.FederationColumn);
}
return Wrap(string.Format("{0} {1}", base.Generate(expression), FederatedOn));
}
public override string Generate(CreateTableExpression expression)
{
var descriptionStatements = DescriptionGenerator.GenerateDescriptionStatements(expression);
var createTableStatement = string.Format("CREATE TABLE {0}.{1}", Quoter.QuoteSchemaName(expression.SchemaName), base.Generate(expression));
var descriptionStatementsArray = descriptionStatements as string[] ?? descriptionStatements.ToArray();
if (!descriptionStatementsArray.Any())
return createTableStatement;
return ComposeStatements(createTableStatement, descriptionStatementsArray);
}
public void CanCreateTableExpression()
{
CreateTableExpression expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(column.Object);
using (command)
{
processor.Process(expression);
command.CommandText = string.Format("SELECT name FROM sqlite_master WHERE type='table' and name='{0}'", tableName);
command.ExecuteReader().Read().ShouldBeTrue();
}
}
/// <summary>
/// Outputs a create table string
/// </summary>
/// <param name="expression"></param>
/// <returns></returns>
public override string Generate(CreateTableExpression expression)
{
if (string.IsNullOrEmpty(expression.TableName)) throw new ArgumentNullException("expression", "expression.TableName cannot be empty");
if (expression.Columns.Count == 0) throw new ArgumentException("You must specifiy at least one column");
string quotedTableName = Quoter.QuoteTableName(expression.TableName);
string errors = ValidateAdditionalFeatureCompatibility(expression.Columns.SelectMany(x => x.AdditionalFeatures));
if (!string.IsNullOrEmpty(errors)) return errors;
return string.Format(CreateTable, quotedTableName, Column.Generate(expression.Columns, quotedTableName));
}
public void CanCreateTableWithDateTimeOffsetColumn()
{
var tableName = "TestTable1";
var expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn1", Type = DbType.DateTimeOffset });
expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn2", Type = DbType.DateTime2 });
expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn3", Type = DbType.Date });
expression.Columns.Add(new ColumnDefinition { TableName = tableName, Name = "TestColumn4", Type = DbType.Time });
var result = Generator.Generate(expression);
result.ShouldBe(string.Format("CREATE TABLE \"public\".\"{0}\" (\"TestColumn1\" timestamptz NOT NULL, \"TestColumn2\" timestamp NOT NULL, \"TestColumn3\" date NOT NULL, \"TestColumn4\" time NOT NULL);", tableName));
}
public override string Generate(CreateTableExpression expression)
{
var createStatement = new StringBuilder();
var tableName = Quoter.QuoteTableName(expression.TableName);
createStatement.Append(string.Format("CREATE TABLE {0}.{1} ({2})", Quoter.QuoteSchemaName(expression.SchemaName), tableName, Column.Generate(expression.Columns, tableName)));
var descriptionStatement = DescriptionGenerator.GenerateDescriptionStatements(expression);
if (descriptionStatement != null && descriptionStatement.Any())
{
createStatement.Append(";");
createStatement.Append(string.Join(";", descriptionStatement.ToArray()));
}
return createStatement.ToString();
}
public void CanMigrateTable()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo"
,Columns = new List<ColumnDefinition> {new ColumnDefinition {Name = "Id", Type = DbType.Int32}}
};
var context = GetDefaultContext();
// Act
MigrateTable(context, create);
// Assert
context.MigrationIndex.ShouldBe(1);
}
public void CanReadLongViewDefinition()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns =
new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32 } }
};
IList<ViewDefinition> views;
var createSql = new StringBuilder();
createSql.Append("CREATE VIEW FooView As SELECT Id,");
createSql.Append("'");
createSql.Append(new string('A', 3000));
createSql.Append("'");
createSql.Append(" As LongText1,");
createSql.Append("'");
createSql.Append(new string('B', 3000));
createSql.Append("'");
createSql.Append(" As LongText2");
createSql.Append(" FROM Foo");
// Act
using (var connection = new SqlConnection(ConnectionString))
{
var processor = new SqlServerProcessor(connection, new SqlServer2005Generator(), new DebugAnnouncer(), new ProcessorOptions());
processor.Process(create);
Assert.IsTrue(processor.TableExists(string.Empty, create.TableName), "SqlServer");
processor.Execute(createSql.ToString());
var dumper = new SqlServerSchemaDumper(processor, new DebugAnnouncer());
views = dumper.ReadViewSchema();
processor.CommitTransaction();
}
// Assert
Assert.AreEqual(1, views.Count);
}
public void CanDefaultAutoIncrementColumnTypeToInteger()
{
ColumnDefinition column = new ColumnDefinition();
column.Name = "Id";
column.IsIdentity = true;
column.IsPrimaryKey = true;
column.Type = DbType.Int64;
column.IsNullable = false;
CreateTableExpression expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(column);
using (command)
{
processor.Process(expression);
command.CommandText = string.Format("SELECT name FROM sqlite_master WHERE type='table' and name='{0}'", tableName);
command.ExecuteReader().Read().ShouldBeTrue();
}
}
public void CanMigrateView()
{
// Act
var create = new CreateTableExpression
{
TableName = "Foo"
,Columns = new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32 } }
};
ExecuteMigrations(create);
CreateViews(new ViewDefinition { Name = "FooView", CreateViewSql = "CREATE VIEW FooView AS SELECT * FROM Foo"});
var context = GetDefaultContext();
// Act
MigrateTable(context);
// Assert
context.MigrationIndex.ShouldBe(2);
}
public void Dispose_WasCommited_ShouldNotRollback()
{
var createTable = new CreateTableExpression { TableName = "silly" };
createTable.Columns.Add(new Model.ColumnDefinition { Name = "one", Type = DbType.Int32 });
_processor.Process(createTable);
// this will close the connection
_processor.CommitTransaction();
// and this will reopen it again causing Dispose->RollbackTransaction not to throw
var tableExists = _processor.TableExists("", createTable.TableName);
tableExists.ShouldBeTrue();
// Now dispose (->RollbackTransaction)
_processor.Dispose();
_processor = MakeProcessor();
// Check that the table still exists after dispose
_processor.TableExists("", createTable.TableName).ShouldBeTrue();
}
public void CanExcludeTable()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32 }
}
};
var context = GetDefaultContext();
context.GenerateAlternateMigrationsFor.Add(DatabaseType.Oracle);
context.ExcludeTables.Add("Foo");
// Act
MigrateToOracle(create, null, 0, c => c.ExcludeTables.Add("Foo"));
// Assert
}
public override string Generate(CreateTableExpression expression)
{
var descriptionStatements = DescriptionGenerator.GenerateDescriptionStatements(expression);
var statements = descriptionStatements as string[] ?? descriptionStatements.ToArray();
if (!statements.Any())
return base.Generate(expression);
var wrappedCreateTableStatement = WrapStatementInExecuteImmediateBlock(base.Generate(expression));
var createTableWithDescriptionsBuilder = new StringBuilder(wrappedCreateTableStatement);
foreach (var descriptionStatement in statements)
{
if (!string.IsNullOrEmpty(descriptionStatement))
{
var wrappedStatement = WrapStatementInExecuteImmediateBlock(descriptionStatement);
createTableWithDescriptionsBuilder.Append(wrappedStatement);
}
}
return WrapInBlock(createTableWithDescriptionsBuilder.ToString());
}
public void CanReadMultipleViews()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns =
new List<ColumnDefinition> { new ColumnDefinition { Name = "Id", Type = DbType.Int32 } }
};
IList<ViewDefinition> views;
// Act
using (var connection = new SqlConnection(ConnectionString))
{
var processor = new SqlServerProcessor(connection, new SqlServer2005Generator(), new DebugAnnouncer(), new ProcessorOptions());
processor.Process(create);
Assert.IsTrue(processor.TableExists(string.Empty, create.TableName), "SqlServer");
processor.Execute("CREATE VIEW FooViewC AS SELECT Id FROM Foo");
processor.Execute("CREATE VIEW FooViewB AS SELECT Id FROM Foo");
processor.Execute("CREATE VIEW FooViewA AS SELECT Id FROM Foo");
var dumper = new SqlServerSchemaDumper(processor, new DebugAnnouncer());
views = dumper.ReadViewSchema();
processor.CommitTransaction();
}
// Assert
Assert.AreEqual(3, views.Count);
Assert.AreEqual("FooViewA", views[0].Name);
Assert.AreEqual("FooViewB", views[1].Name);
Assert.AreEqual("FooViewC", views[2].Name);
}
protected CreateTableExpression GetCreateTableExpression(string tableName)
{
var columnName1 = "ColumnName1";
var columnName2 = "ColumnName2";
var column1 = new ColumnDefinition { Name = columnName1, Type = DbType.String };
var column2 = new ColumnDefinition { Name = columnName2, Type = DbType.Int32 };
var expression = new CreateTableExpression { TableName = tableName };
expression.Columns.Add(column1);
expression.Columns.Add(column2);
return expression;
}
public override string Generate(CreateTableExpression expression)
{
truncator.Truncate(expression);
return base.Generate(expression);
}
public override string Generate(CreateTableExpression expression)
{
return String.Format("CREATE TABLE {0} ({1})", expression.TableName, Column.Generate(expression));
}
public void CanCreateTableWithGetDateDefault()
{
ColumnDefinition column = new ColumnDefinition
{
Name = "TestColumn1",
Type = DbType.String,
Size = 5,
DefaultValue = "GetDate()"
};
var expression = new CreateTableExpression { TableName = "TestTable1" };
expression.Columns.Add(column);
var sql = _generator.Generate(expression);
sql.ShouldBe("CREATE TABLE [TestTable1] ([TestColumn1] NVARCHAR(5) NOT NULL DEFAULT GetDate())");
}
