private static void WriteDeleteView(SchemaMigrationContext context, ViewDefinition view, StreamWriter output)
{
if ( context.FromDatabaseType == DatabaseType.SqlServer)
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.SqlServer).Sql(\"DROP VIEW [{0}].[{1}]\");", view.SchemaName, view.Name);
else
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Sql(\"DROP VIEW {1}\");", context.FromDatabaseType, view.Name);
foreach (var databaseType in context.GenerateAlternateMigrationsFor)
{
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Sql(\"DROP VIEW {1}\");", databaseType, view.Name);
}
}
public virtual IList<ViewDefinition> ReadViews()
{
string query = @"select OBJECT_SCHEMA_NAME(v.[object_id],DB_ID()) AS [Schema], v.name AS [View], sc.text As [Sql]
FROM sys.views v
INNER JOIN syscomments sc ON v.[object_id] = sc.[id]
ORDER BY v.name, sc.colid
";
var ds = Read(query);
var dt = ds.Tables[0];
IList<ViewDefinition> views = new List<ViewDefinition>();
foreach (DataRow dr in dt.Rows)
{
var matches = (from t in views
where t.Name == dr["View"].ToString()
&& t.SchemaName == dr["Schema"].ToString()
select t).ToList();
ViewDefinition viewDefinition = null;
if (matches.Count > 0) viewDefinition = matches[0];
// create the view if not found
if (viewDefinition == null)
{
viewDefinition = new ViewDefinition()
{
Name = dr["View"].ToString(),
SchemaName = dr["Schema"].ToString()
};
viewDefinition.CreateViewSql = dr["Sql"].ToString();
views.Add(viewDefinition);
}
else
{
// Append the sql as the create sql statement may span more than one row
viewDefinition.CreateViewSql += dr["Sql"];
}
}
return views;
}
public void CanMigrateTableAndView()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32 }
}
};
var view = new ViewDefinition {Name = "FooView", CreateViewSql = "CREATE VIEW FooView AS SELECT * FROM Foo"};
// Act
MigrateToOracle(create, view, 2);
// Assert
}
public void CanMigrateTableAndExcludeView()
{
// Arrange
var create = new CreateTableExpression
{
TableName = "Foo",
Columns = new[]{
new ColumnDefinition {Name = "Id", Type = DbType.Int32 }
}
};
var view = new ViewDefinition {Name = "FooView", CreateViewSql = "CREATE VIEW FooView AS SELECT * FROM Foo"};
var context = GetDefaultContext();
context.GenerateAlternateMigrationsFor.Add(DatabaseType.Oracle);
context.ExcludeViews.Add("FooView");
// Act
MigrateToOracle(create, view, 1, c => c.ExcludeViews.Add("FooView"));
// Assert
}
/// <summary>
/// Migrates a table from SQL Server to Oracle
/// </summary>
/// <param name="create">The table to be created in SQL Server</param>
/// <param name="view">The view to be created in SQL Server</param>
/// <param name="expectedMigrations">The expected number of migrations</param>
/// <param name="contextAction">Delegates that alter the context before the migration is executed</param>
private void MigrateToOracle(CreateTableExpression create, ViewDefinition view, int expectedMigrations, params Action<SchemaMigrationContext>[] contextAction)
{
var context = GetDefaultContext();
context.GenerateAlternateMigrationsFor.Add(DatabaseType.Oracle);
if (contextAction != null)
{
foreach (var action in contextAction)
{
action(context);
}
}
CreateTables(create);
if (view != null)
CreateViews(view);
MigrateTable(context);
if ( context.ExcludeTables.Contains(create.TableName))
AssertOracleTablesDoNotExist(create);
else
{
AssertOracleTablesExist(create);
}
context.MigrationIndex.ShouldBe(expectedMigrations);
}
/// <summary>
/// Basic implementation of SQL Server to Oracle view using string replace
/// </summary>
/// <param name="view">The view to be converted</param>
/// <returns>The altered CREATE VIEW statement</returns>
private static string DefaultSqlServerToOracleViewConvertor(ViewDefinition view)
{
return view.CreateViewSql
.Replace("dbo.", "")
.Replace("[dbo].", "")
.Replace("[", "\"")
.Replace("]", "\"")
.Replace("GETDATE()", "sysdate")
.Replace("ISNULL", "NVL");
}
private void WriteView(SchemaMigrationContext context, ViewDefinition view, StreamWriter output)
{
var scriptsDirectory = Path.Combine(context.WorkingDirectory, context.ScriptsDirectory);
var scriptFile = Path.Combine(scriptsDirectory, string.Format("CreateView{0}_{1}.sql", view.Name, context.FromDatabaseType));
if ( !File.Exists(scriptFile))
{
if (!Directory.Exists(scriptsDirectory))
Directory.CreateDirectory(scriptsDirectory);
File.WriteAllText(scriptFile, view.CreateViewSql);
}
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Script(@\"{1}\");", context.FromDatabaseType, Path.Combine(context.ScriptsDirectory, Path.GetFileName(scriptFile)));
foreach (var databaseType in context.GenerateAlternateMigrationsFor)
{
if (!context.ViewConvertor.ContainsKey(databaseType)) continue;
var alterternateScriptFile = Path.Combine(scriptsDirectory, string.Format("CreateView{0}_{1}.sql", view.Name, databaseType));
if (!File.Exists(alterternateScriptFile))
{
File.WriteAllText(alterternateScriptFile, context.ViewConvertor[databaseType](view));
}
output.WriteLine("\t\t\tExecute.WithDatabaseType(DatabaseType.{0}).Script(@\"{1}\");", databaseType, Path.Combine(context.ScriptsDirectory, Path.GetFileName(alterternateScriptFile)));
}
}
/// <summary>
/// Writes the Migration Up() and Down()
/// </summary>
/// <param name="context">The context that controls how the column should be generated</param>
/// <param name="view">the view to generate the migration for</param>
/// <param name="migration">The migration index to apply</param>
/// <param name="output">The output stream to append the C# code to</param>
private void WriteToStream(SchemaMigrationContext context, ViewDefinition view, int migration, StreamWriter output)
{
WriteMigration(output, context, migration
, () => context.MigrationViewClassNamer(migration, view)
, () => WriteView(context, view, output)
, () => WriteDeleteView(context, view, output));
}
