/// <summary>
/// Creates the SQLite database from the schema read from the SQL Server.
/// </summary>
/// <param name="sqlitePath">The path to the generated DB file.</param>
/// <param name="schema">The schema of the SQL server database.</param>
/// <param name="password">The password to use for encrypting the DB or null if non is needed.</param>
/// <param name="handler">A handle for progress notifications.</param>
private static void CreateSQLiteDatabase(string sqlitePath, DatabaseSchema schema, string password,
SqlConversionHandler handler,
FailedViewDefinitionHandler viewFailureHandler)
{
_log.Debug("Creating SQLite database...");
// Create the SQLite database file
SQLiteConnection.CreateFile(sqlitePath);
_log.Debug("SQLite file was created successfully at [" + sqlitePath + "]");
// Connect to the newly created database
string sqliteConnString = CreateSQLiteConnectionString(sqlitePath, password);
using (SQLiteConnection conn = new SQLiteConnection(sqliteConnString))
{
conn.Open();
// Create all tables in the new database
int count = 0;
foreach (TableSchema dt in schema.Tables)
{
try
{
AddSQLiteTable(conn, dt);
}
catch (Exception ex)
{
_log.Error("AddSQLiteTable failed", ex);
throw;
}
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / schema.Tables.Count), "Added table " + dt.TableName + " to the SQLite database");
_log.Debug("added schema for SQLite table [" + dt.TableName + "]");
} // foreach
// Create all views in the new database
count = 0;
foreach (ViewSchema vs in schema.Views)
{
try
{
AddSQLiteView(conn, vs, viewFailureHandler);
}
catch (Exception ex)
{
_log.Error("AddSQLiteView failed", ex);
throw;
} // catch
count++;
CheckCancelled();
handler(false, true, 50+(int)(count * 50.0 / schema.Views.Count), "Added view " + vs.ViewName + " to the SQLite database");
_log.Debug("added schema for SQLite view [" + vs.ViewName + "]");
} // foreach
} // using
_log.Debug("finished adding all table/view schemas for SQLite database");
}
/// <summary>
/// Reads the entire SQL Server DB schema using the specified connection string.
/// </summary>
/// <param name="connString">The connection string used for reading SQL Server schema.</param>
/// <param name="handler">A handler for progress notifications.</param>
/// <param name="selectionHandler">The selection handler which allows the user to select
/// which tables to convert.</param>
/// <returns>database schema objects for every table/view in the SQL Server database.</returns>
private static DatabaseSchema ReadSqlServerSchema(string connString, SqlConversionHandler handler,
SqlTableSelectionHandler selectionHandler)
{
// First step is to read the names of all tables in the database
List<TableSchema> tables = new List<TableSchema>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
List<string> tableNames = new List<string>();
List<string> tblschema = new List<string>();
// This command will read the names of all tables in the database
SqlCommand cmd = new SqlCommand(@"select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableNames.Add((string)reader["TABLE_NAME"]);
tblschema.Add((string)reader["TABLE_SCHEMA"]);
} // while
} // using
// Next step is to use ADO APIs to query the schema of each table.
int count = 0;
for (int i=0; i<tableNames.Count; i++)
{
string tname = tableNames[i];
string tschma = tblschema[i];
TableSchema ts = CreateTableSchema(conn, tname, tschma);
CreateForeignKeySchema(conn, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / tableNames.Count), "Parsed table " + tname);
_log.Debug("parsed table schema for [" + tname + "]");
} // foreach
} // using
_log.Debug("finished parsing all tables in SQL Server schema");
// Allow the user a chance to select which tables to convert
if (selectionHandler != null)
{
List<TableSchema> updated = selectionHandler(tables);
if (updated != null)
tables = updated;
} // if
Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
// Continue and read all of the views in the database
List<ViewSchema> views = new List<ViewSchema>();
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (SqlDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
ViewSchema vs = new ViewSchema();
vs.ViewName = (string)reader["TABLE_NAME"];
vs.ViewSQL = (string)reader["VIEW_DEFINITION"];
// Remove all ".dbo" strings from the view definition
vs.ViewSQL = removedbo.Replace(vs.ViewSQL, string.Empty);
views.Add(vs);
count++;
CheckCancelled();
handler(false, true, 50+(int)(count * 50.0 / views.Count), "Parsed view " + vs.ViewName);
_log.Debug("parsed view schema for [" + vs.ViewName + "]");
} // while
} // using
} // using
DatabaseSchema ds = new DatabaseSchema();
ds.Tables = tables;
ds.Views = views;
return ds;
}
/// <summary>
/// Reads the entire SQL Server DB schema using the specified connection string.
/// </summary>
/// <param name="connString">The connection string used for reading SQL Server schema.</param>
/// <param name="handler">A handler for progress notifications.</param>
/// <param name="selectionHandler">The selection handler which allows the user to select
/// which tables to convert.</param>
/// <returns>database schema objects for every table/view in the SQL Server database.</returns>
private static DatabaseSchema ReadOracleSchema(string connString, SqlConversionHandler handler,List<String> includedTables, List<String> existedTables)
{
// First step is to read the names of all tables in the database
List<TableSchema> tables = new List<TableSchema>();
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
// Next step is to use ADO APIs to query the schema of each table.
int count = 0;
for (int i = 0; i < includedTables.Count; i++)
{
string tname = includedTables[i];
TableSchema ts = CreateTableSchema(conn, tname, ""); //TODO Schema
ts.Existed = existedTables.Contains(tname);
//CreateForeignKeySchema(conn, ts);
tables.Add(ts);
count++;
CheckCancelled();
handler(false, true, (int)(count * 50.0 / includedTables.Count), "Parsed table " + tname);
_log.Debug("parsed table schema for [" + tname + "]");
} // foreach
} // using
_log.Debug("finished parsing all tables in SQL Server schema");
// Regex removedbo = new Regex(@"dbo\.", RegexOptions.Compiled | RegexOptions.IgnoreCase);
// Continue and read all of the views in the database
/*
List<ViewSchema> views = new List<ViewSchema>();
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
OracleCommand cmd = new OracleCommand(@"SELECT TABLE_NAME, VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS", conn);
using (OracleDataReader reader = cmd.ExecuteReader())
{
int count = 0;
while (reader.Read())
{
ViewSchema vs = new ViewSchema();
if (reader["TABLE_NAME"] == DBNull.Value)
continue;
if (reader["VIEW_DEFINITION"] == DBNull.Value)
continue;
vs.ViewName = (string)reader["TABLE_NAME"];
vs.ViewSQL = (string)reader["VIEW_DEFINITION"];
// Remove all ".dbo" strings from the view definition
vs.ViewSQL = removedbo.Replace(vs.ViewSQL, string.Empty);
views.Add(vs);
count++;
CheckCancelled();
handler(false, true, 50 + (int)(count * 50.0 / views.Count), "Parsed view " + vs.ViewName);
_log.Debug("parsed view schema for [" + vs.ViewName + "]");
} // while
} // using
} // using
*/
DatabaseSchema ds = new DatabaseSchema();
ds.Tables = tables;
// ds.Views = views;
return ds;
}
