public void AddColumnOperationMigration()
{
var migrationOperations = new List<MigrationOperation>();
if (ProviderManifest == null)
ProviderManifest = new MySqlProviderManifest(Version.ToString());
TypeUsage tu = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
TypeUsage result = ProviderManifest.GetStoreType(tu);
var intColumn = new ColumnModel(PrimitiveTypeKind.Int32, result)
{
Name = "TotalPosts",
IsNullable = false
};
var addColumnMigratioOperation = new AddColumnOperation("Blogs", intColumn);
migrationOperations.Add(addColumnMigratioOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists()) context.Database.Delete();
context.Database.Create();
using (MySqlConnection conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
MySqlCommand query = new MySqlCommand("Select Column_name, Is_Nullable, Data_Type from information_schema.Columns where table_schema ='" + conn.Database + "' and table_name = 'Blogs' and column_name ='TotalPosts'" , conn);
MySqlDataReader reader = query.ExecuteReader();
while (reader.Read())
{
Assert.AreEqual("TotalPosts", reader[0].ToString());
Assert.AreEqual("NO", reader[1].ToString());
Assert.AreEqual("int", reader[2].ToString());
}
reader.Close();
conn.Close();
}
}
}
public void CreateTableOperationMigration()
{
var migrationOperations = new List<MigrationOperation>();
var createTableOperation = CreateTableOperation();
migrationOperations.Add(createTableOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists()) context.Database.Delete();
context.Database.Create();
using (var conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
MySqlCommand query = new MySqlCommand("Select Column_name from information_schema.Columns where table_schema ='" + conn.Database + "' and table_name = 'Posts'", conn);
MySqlDataReader reader = query.ExecuteReader();
while (reader.Read())
{
Assert.AreEqual(1, createTableOperation.Columns.Where(t => t.Name.Equals(reader[0].ToString())).Count());
}
reader.Close();
conn.Close();
}
}
}
/// <summary>
/// Generate and apply sql statemens from the
/// migration operations list
/// return false is case of fail or if database doesn't exist
/// </summary>
private bool GenerateAndExecuteMySQLStatements(List<MigrationOperation> migrationOperations)
{
MySqlProviderServices ProviderServices;
ProviderServices = new MySqlProviderServices();
using (BlogContext context = new BlogContext())
{
if (!context.Database.Exists()) return false;
using (MySqlConnection conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
var migratorGenerator = new MySqlMigrationSqlGenerator();
var Token = ProviderServices.GetProviderManifestToken(conn);
var sqlStmts = migratorGenerator.Generate(migrationOperations, providerManifestToken: Token);
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
foreach (MigrationStatement stmt in sqlStmts)
{
try
{
MySqlCommand cmd = new MySqlCommand(stmt.Sql, conn);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
return false;
}
}
}
}
return true;
}
public void DropPrimaryKeyOperation()
{
var migrationOperations = new List<MigrationOperation>();
// create table where the PK exists
var createTableOperation = CreateTableOperation();
migrationOperations.Add(createTableOperation);
var createDropPKOperation = new DropPrimaryKeyOperation(anonymousArguments: new { DeleteAutoIncrement = true });
createDropPKOperation.Table = "Posts";
createDropPKOperation.Columns.Add("PostId");
migrationOperations.Add(createDropPKOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists()) context.Database.Delete();
context.Database.Create();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
using (var conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
// check for table creation
var query = new MySqlCommand("select Count(*) from information_schema.Tables WHERE `table_name` = 'Posts' and `table_schema` = '" + conn.Database + "' ", conn);
int rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
// check if PK exists
query = new MySqlCommand("select Count(*) from information_schema.table_constraints where `constraint_type` = 'primary key' and `constraint_schema` = '" + conn.Database + "' and table_name= 'Posts'", conn);
rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(0, rows);
//check the definition of the column that was PK
query = new MySqlCommand("Select Column_name, Is_Nullable, Data_Type from information_schema.Columns where table_schema ='" + conn.Database + "' and table_name = 'Posts' and column_name ='PostId'", conn);
MySqlDataReader reader = query.ExecuteReader();
while (reader.Read())
{
Assert.AreEqual("PostId", reader[0].ToString());
Assert.AreEqual("NO", reader[1].ToString());
Assert.AreEqual("int", reader[2].ToString());
}
reader.Close();
conn.Close();
}
}
}
public void CreateForeignKeyOperation()
{
var migrationOperations = new List<MigrationOperation>();
// create dependant table Posts
var createTableOperation = CreateTableOperation();
migrationOperations.Add(createTableOperation);
// Add column BlogId to create the constraints
if (ProviderManifest == null)
ProviderManifest = new MySqlProviderManifest(Version.ToString());
TypeUsage tu = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
TypeUsage result = ProviderManifest.GetStoreType(tu);
var intColumn = new ColumnModel(PrimitiveTypeKind.Int32, result)
{
Name = "BlogId",
IsNullable = false
};
var addColumnMigratioOperation = new AddColumnOperation("Posts", intColumn);
migrationOperations.Add(addColumnMigratioOperation);
// create constrain object
var createForeignkeyOperation = new AddForeignKeyOperation();
createForeignkeyOperation.Name = "FKBlogs";
createForeignkeyOperation.DependentTable = "Posts";
createForeignkeyOperation.DependentColumns.Add("BlogId");
createForeignkeyOperation.CascadeDelete = true;
createForeignkeyOperation.PrincipalTable = "Blogs";
createForeignkeyOperation.PrincipalColumns.Add("BlogId");
//create index to use
migrationOperations.Add(createForeignkeyOperation.CreateCreateIndexOperation());
migrationOperations.Add(createForeignkeyOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists()) context.Database.Delete();
context.Database.Create();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
using (var conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
// check for foreign key creation
MySqlCommand query = new MySqlCommand("select Count(*) from information_schema.table_constraints where constraint_type = 'foreign key' and constraint_schema = '" + conn.Database + "' and constraint_name = 'FKBlogs'", conn);
int rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
// check for table creation
query = new MySqlCommand("select Count(*) from information_schema.Tables WHERE `table_name` = 'Posts' and `table_schema` = '" + conn.Database + "' ", conn);
rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
conn.Close();
}
}
}
public void CreateForeignKeyOperation()
{
var migrationOperations = new List<MigrationOperation>();
// create dependant table Posts
var createTableOperation = CreateTableOperation();
migrationOperations.Add(createTableOperation);
// Add column BlogId to create the constraints
if (ProviderManifest == null)
ProviderManifest = new MySqlProviderManifest(Version.ToString());
TypeUsage tu = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
TypeUsage result = ProviderManifest.GetStoreType(tu);
var intColumn = new ColumnModel(PrimitiveTypeKind.Int32, result)
{
Name = "BlogId",
IsNullable = false
};
var addColumnMigratioOperation = new AddColumnOperation("Posts", intColumn);
migrationOperations.Add(addColumnMigratioOperation);
// create constrain object
var createForeignkeyOperation = new AddForeignKeyOperation();
createForeignkeyOperation.Name = "FKBlogs";
createForeignkeyOperation.DependentTable = "Posts";
createForeignkeyOperation.DependentColumns.Add("BlogId");
createForeignkeyOperation.CascadeDelete = true;
createForeignkeyOperation.PrincipalTable = "Blogs";
createForeignkeyOperation.PrincipalColumns.Add("BlogId");
//create index to use
migrationOperations.Add(createForeignkeyOperation.CreateCreateIndexOperation());
migrationOperations.Add(createForeignkeyOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists()) context.Database.Delete();
context.Database.Create();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
using (var conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed) conn.Open();
// check for foreign key creation
MySqlCommand query = new MySqlCommand("select Count(*) from information_schema.table_constraints where constraint_type = 'foreign key' and constraint_schema = '" + conn.Database + "' and constraint_name = 'FKBlogs'", conn);
int rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
// check for table creation
query = new MySqlCommand("select Count(*) from information_schema.Tables WHERE `table_name` = 'Posts' and `table_schema` = '" + conn.Database + "' ", conn);
rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
conn.Close();
}
// Test fix for
MySqlConnection con = GetConnectionFromContext(context);
con.Open();
try
{
MySqlCommand cmd = new MySqlCommand("show create table `posts`", con);
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
string sql = r.GetString(1);
Assert.IsTrue(sql.IndexOf(
" CONSTRAINT `FKBlogs` FOREIGN KEY (`BlogId`) REFERENCES `blogs` (`BlogId`) ON DELETE CASCADE ON UPDATE CASCADE",
StringComparison.OrdinalIgnoreCase) != -1);
}
}
finally
{
con.Close();
}
}
}
public void CreateForeignKeyOperation()
{
var migrationOperations = new List <MigrationOperation>();
// create dependant table Posts
var createTableOperation = CreateTableOperation();
migrationOperations.Add(createTableOperation);
// Add column BlogId to create the constraints
if (ProviderManifest == null)
{
ProviderManifest = new MySqlProviderManifest(Version.ToString());
}
TypeUsage tu = TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int32));
TypeUsage result = ProviderManifest.GetStoreType(tu);
var intColumn = new ColumnModel(PrimitiveTypeKind.Int32, result)
{
Name = "BlogId",
IsNullable = false
};
var addColumnMigratioOperation = new AddColumnOperation("Posts", intColumn);
migrationOperations.Add(addColumnMigratioOperation);
// create constrain object
var createForeignkeyOperation = new AddForeignKeyOperation();
createForeignkeyOperation.Name = "FKBlogs";
createForeignkeyOperation.DependentTable = "Posts";
createForeignkeyOperation.DependentColumns.Add("BlogId");
createForeignkeyOperation.CascadeDelete = true;
createForeignkeyOperation.PrincipalTable = "Blogs";
createForeignkeyOperation.PrincipalColumns.Add("BlogId");
//create index to use
migrationOperations.Add(createForeignkeyOperation.CreateCreateIndexOperation());
migrationOperations.Add(createForeignkeyOperation);
using (BlogContext context = new BlogContext())
{
if (context.Database.Exists())
{
context.Database.Delete();
}
context.Database.Create();
Assert.AreEqual(true, GenerateAndExecuteMySQLStatements(migrationOperations));
using (var conn = new MySqlConnection(context.Database.Connection.ConnectionString))
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
// check for foreign key creation
MySqlCommand query = new MySqlCommand("select Count(*) from information_schema.table_constraints where constraint_type = 'foreign key' and constraint_schema = '" + conn.Database + "' and constraint_name = 'FKBlogs'", conn);
int rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
// check for table creation
query = new MySqlCommand("select Count(*) from information_schema.Tables WHERE `table_name` = 'Posts' and `table_schema` = '" + conn.Database + "' ", conn);
rows = Convert.ToInt32(query.ExecuteScalar());
Assert.AreEqual(1, rows);
conn.Close();
}
// Test fix for
MySqlConnection con = GetConnectionFromContext(context);
con.Open();
try
{
MySqlCommand cmd = new MySqlCommand("show create table `posts`", con);
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
string sql = r.GetString(1);
Assert.IsTrue(sql.IndexOf(
" CONSTRAINT `FKBlogs` FOREIGN KEY (`BlogId`) REFERENCES `blogs` (`BlogId`) ON DELETE CASCADE ON UPDATE CASCADE",
StringComparison.OrdinalIgnoreCase) != -1);
}
}
finally
{
con.Close();
}
}
}
