public void Up(IDatabase db)
{
MySqlHelper.ActivateStrictMode(db);
const string tableName = "Mig8";
ExpectedTables.Clear();
// create a table that contains columns for all supported data types
ICreatedTable table = db.CreateTable(tableName);
Dictionary <string, DbType> columns = new Dictionary <string, DbType>();
int i = 1;
foreach (SupportsAttribute support in IntegrationTestContext.SupportsAttributes
.Where(s => !IntegrationTestContext.IsScripting || s.IsScriptable)
.OrderByDescending(s => s.MaximumSize)) // make sure the first column is not a LOB column as Teradata automatically adds an index to the first column and then would crash with: 'Cannot create index on LOB columns.'
{
if (support.DbType == DbType.AnsiStringFixedLength || // skip fixed length character types as the table would grow too large
support.DbType == DbType.StringFixedLength || // skip fixed length character types as the table would grow too large
support.DbType == DbType.Int64 || // skip Int64 as the ODBC driver does not support DbParameters for this data type --> note that Int64 is implicitly tested as MigSharp uses this data type for its timestamp column
(support.DbType == DbType.Decimal && support.MaximumScale == 0)) // this is test thoroughly in Migration11
{
continue;
}
string columnName = "Column" + i++;
ICreatedTableWithAddedColumn column = table.WithNullableColumn(columnName, support.DbType);
if (support.MaximumSize > 0)
{
column.OfSize(support.MaximumSize, support.MaximumScale > 0 ? support.MaximumScale : (int?)null);
}
if (db.Context.ProviderMetadata.Platform == Platform.MySql)
{
// having two maxlength strings exceeds the maximum table length in mysql (65535) - reduce the size of these columns
if (support.DbType == DbType.AnsiString ||
support.DbType == DbType.String)
{
column.OfSize(5000);
}
}
columns.Add(columnName, support.DbType);
}
db.Execute(context =>
{
IDbCommand command = context.CreateCommand();
ExpectedTables[0].Clear();
var values = new List <object>();
foreach (var column in columns)
{
DbType dbType;
object value = GetTestValue(column, db, out dbType);
// MySQL only retains 8 digits for float values
if (db.Context.ProviderMetadata.Platform == Platform.MySql)
{
if (dbType == DbType.Single)
{
value = (float)Math.Round((float)value, 5);
}
}
command.AddParameter("@" + column.Key, dbType, value);
values.Add(value);
}
ExpectedTables[0].Add(values.ToArray());
command.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO ""{0}"" ({1}) VALUES ({2})",
Tables[0].Name,
string.Join(", ", columns.Keys.Select(c => "\"" + c + "\"").ToArray()),
string.Join(", ", command.Parameters.Cast <IDbDataParameter>().Select(p => context.ProviderMetadata.GetParameterSpecifier(p)).ToArray()));
context.CommandExecutor.ExecuteNonQuery(command);
});
ExpectedTables.Add(new ExpectedTable(tableName, columns.Keys));
// create a table for each supported primary key data type
// (as combining them all into one table would be too much)
foreach (SupportsAttribute support in IntegrationTestContext.SupportsAttributes
.Where(s => (!IntegrationTestContext.IsScripting || s.IsScriptable) && s.CanBeUsedAsPrimaryKey))
{
string pkTableName = Tables[0].Name + "WithPkOf" + support.DbType + support.MaximumScale;
ICreatedTable pkTable = db.CreateTable(pkTableName);
int maximumSize = support.MaximumSize;
if (db.Context.ProviderMetadata.Platform == Platform.SqlServer)
{
// SQL Server only allow PKs with a maximum length of 900 bytes
if (support.DbType == DbType.AnsiStringFixedLength)
{
maximumSize = 900; // FEATURE: this information should be part of the SupportAttribute
}
if (support.DbType == DbType.StringFixedLength)
{
maximumSize = 450; // FEATURE: this information should be part of the SupportAttribute
}
}
if (db.Context.ProviderMetadata.Platform == Platform.MySql)
{
// MySQL only allow PKs with a maximum length of 767 bytes
if (support.DbType == DbType.AnsiString ||
support.DbType == DbType.String)
{
maximumSize = 767 / 3; // utf8 chars can take up to three bytes per char (see: https://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html)
}
}
ICreatedTableWithAddedColumn column = pkTable.WithPrimaryKeyColumn("Id", support.DbType);
if (maximumSize > 0)
{
column.OfSize(maximumSize, support.MaximumScale > 0 ? support.MaximumScale : (int?)null);
}
ExpectedTables.Add(new ExpectedTable(pkTableName, "Id"));
}
}
