/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return result;
}
/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return(result);
}
/// <summary>
/// Recreates a database schema using Metadata information
/// </summary>
/// <param name="schema">Metadata about the tables to create in the schema</param>
public override void CloneSchema(SchemaTablesMetaData schema)
{
try
{
using (SQLiteConnection connector = new SQLiteConnection(connectionString))
{
connector.Open();
foreach (TableMetaData table in schema.tables)
{
using (SQLiteCommand Cmd = new SQLiteCommand())
{
Cmd.CommandText = CreateTableDML(table);
if (CommandLineParametersHelper.verbose)
{
Console.Out.WriteLine("{0}", Cmd.CommandText);
}
Cmd.Connection = connector;
Cmd.ExecuteNonQuery();
}
}
AddViews(schema.views);
connector.Close();
}
}
catch (SQLiteException ex)
{
Console.Out.WriteLine("Exception cloning schema: {0}", ex.Message);
}
}
/// <summary>
/// Recreates a database schema using Metadata information
/// </summary>
/// <param name="schema">Metadata about the tables to create in the schema</param>
public override void CloneSchema(SchemaTablesMetaData schema)
{
try
{
using (SQLiteConnection connector = new SQLiteConnection(connectionString))
{
connector.Open();
foreach (TableMetaData table in schema.tables)
{
using (SQLiteCommand Cmd = new SQLiteCommand())
{
Cmd.CommandText = CreateTableDML(table);
if (CommandLineParametersHelper.verbose)
{
Console.Out.WriteLine("{0}", Cmd.CommandText);
}
Cmd.Connection = connector;
Cmd.ExecuteNonQuery();
}
}
connector.Close();
}
}
catch (SQLiteException ex)
{
Console.Out.WriteLine("Exception cloning schema: {0}", ex.Message);
}
}
/// <summary>
/// Populates a SQLiteCommand parameter list with parameter names based on the column names of a table
/// </summary>
/// <param name="table">Table metadata to obtain column names</param>
/// <param name="cmd">SQLite command whose parameter List will be filled</param>
private void CreateSqlCommandParameters(TableMetaData table, SQLiteCommand cmd)
{
cmd.Parameters.Clear();
foreach (ColumnMetaData column in table.columns)
{
cmd.Parameters.Add(new SQLiteParameter(String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName))));
}
}
/// <summary>
/// Creates the text of the SQL command to create a table in SQLite
/// </summary>
/// <param name="table">Table Metadata to create</param>
/// <returns>The DML sentence (SQL) to create the given table in a SQLite schema</returns>
private string CreateTableDML(TableMetaData table)
{
StringBuilder stmtBuilder = new StringBuilder();
string primary_keys_string = string.Empty;
stmtBuilder.Append("CREATE TABLE " + SchemaTablesMetaData.EscapeIdentifier(table.tableName) + " (");
for (int i = 0; i < table.columns.Count; i++)
{
ColumnMetaData column = table.columns[i];
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.columnName));
stmtBuilder.Append(" ");
stmtBuilder.Append(ConvertDbTypeToSQLiteType(column));
if (!column.isNullable)
{
stmtBuilder.Append(" NOT NULL");
}
if (column.hasDefault)
{
stmtBuilder.Append(" DEFAULT ");
if (IsLiteralType(column))
{
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.defaultValue));
}
else
{
stmtBuilder.Append(column.defaultValue);
}
}
if (column.hasForeignKey)
{
stmtBuilder.Append(String.Format(" REFERENCES {0} ({1})", SchemaTablesMetaData.EscapeIdentifier(column.fkTable), SchemaTablesMetaData.EscapeIdentifier(column.fkColumn)));
}
if (column.isPrimaryKey)
{
if (string.IsNullOrEmpty(primary_keys_string))
{
primary_keys_string = SchemaTablesMetaData.EscapeIdentifier(column.columnName);
}
else
{
primary_keys_string = String.Format("{0},{1}", primary_keys_string, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
}
}
if (i + 1 < table.columns.Count || !string.IsNullOrEmpty(primary_keys_string))
{
stmtBuilder.Append(", ");
}
}
if (!string.IsNullOrEmpty(primary_keys_string))
{
stmtBuilder.Append(String.Format("PRIMARY KEY({0})", primary_keys_string));
}
stmtBuilder.Append(")");
return(stmtBuilder.ToString());
}
/// <summary>
/// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
/// </summary>
/// <param name="table">Table metadata from the table to copy</param>
/// <returns>SQL Sentence of the insertion in the target database</returns>
private string CreateInsertQuery(TableMetaData table)
{
string csvColumList = string.Empty;
string csvParamsList = string.Empty;
foreach (ColumnMetaData column in table.columns)
{
csvColumList = String.Format("{0}{1},", csvColumList, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
}
string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));
return(query);
}
/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
// get all views fo ms access db
DataTable dt2 = connector.GetSchema("Views");
foreach (DataRow row in dt2.Rows)
{
string queryText = (string)row["VIEW_DEFINITION"];
queryText = queryText.Replace(System.Environment.NewLine, " ");
string queryName = (string)row["TABLE_NAME"];
Console.WriteLine(queryName);
ViewMetaData vm = new ViewMetaData();
vm.ViewName = queryName;
vm.ViewQuery = queryText;
result.views.Add(vm);
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="schema"></param>
public override void CloneSchema(SchemaTablesMetaData schema)
{
throw new NotImplementedException();
}
static int Main(string[] args)
{
/// TESTs
///
bool enableTest = false;
if (enableTest)
{
UtilsTests.RunTableFindTest();
UtilsTests.RunSwapRightJoinTest();
return(0);
}
if (!CommandLineParametersHelper.ParseArguments(args))
{
return(1);
}
MSAccessBackend msbackend = new MSAccessBackend(CommandLineParametersHelper.databaseSource);
string TargetFile = CommandLineParametersHelper.databaseTarget;
if (null == TargetFile)
{
TargetFile = System.IO.Path.ChangeExtension(CommandLineParametersHelper.databaseSource, "db");
}
if (System.IO.File.Exists(TargetFile) && CommandLineParametersHelper.erase)
{
System.IO.File.Delete(TargetFile);
}
SQLiteBackend backend = new SQLiteBackend(TargetFile);
// Explore the source database to get metadata.
SchemaTablesMetaData schemaInfo = msbackend.QuerySchemaDefinition(null);
foreach (TableMetaData t in schemaInfo.tables)
{
msbackend.QueryTableDefinition(t);
}
if (CommandLineParametersHelper.verbose)
{
Console.Out.WriteLine(schemaInfo.ToString());
Console.Out.WriteLine("There are {0} tables to convert.", schemaInfo.tables.Count);
}
schemaInfo.SortTablesByDependencies();
if (CommandLineParametersHelper.verbose)
{
Console.Out.WriteLine("Tables will be created in this order:");
foreach (TableMetaData t in schemaInfo.tables)
{
Console.Out.Write(t.tableName + " , ");
}
Console.Out.WriteLine();
}
// Clone source db schema into target db schema (no data)
backend.CloneSchema(schemaInfo);
// Copy data from source db to target db
DateTime dt1 = DateTime.Now;
foreach (TableMetaData table in schemaInfo.tables)
{
Console.Out.Write("Dumping table {0}", table.tableName);
msbackend.DumpTableContents(table, backend);
}
TimeSpan sp = DateTime.Now - dt1;
Console.Out.WriteLine("Complete dump performed in {0} ", sp);
return(0);
}
/// <summary>
///
/// </summary>
/// <param name="schema"></param>
public override void CloneSchema(SchemaTablesMetaData schema)
{
throw new NotImplementedException();
}
/// <summary>
/// Get Metadata information about the tables in a schema in the current database
/// </summary>
/// <param name="schema">Name of the schema in the database.</param>
/// <returns></returns>
public override SchemaTablesMetaData QuerySchemaDefinition(string schema)
{
SchemaTablesMetaData result = new SchemaTablesMetaData();
result.schemaName = schema;
try
{
using (OleDbConnection connector = new OleDbConnection(connectionString))
{
connector.Open();
using (DataTable dt = connector.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, schema, null, "TABLE" }))
{
foreach (DataRow row in dt.Rows)
{
TableMetaData table = new TableMetaData();
table.tableName = row[2].ToString();
result.AddTable(table);
}
}
// get all views fo ms access db
DataTable dt2 = connector.GetSchema("Views");
foreach (DataRow row in dt2.Rows)
{
string queryText = (string)row["VIEW_DEFINITION"];
queryText = queryText.Replace(System.Environment.NewLine," ");
string queryName = (string)row["TABLE_NAME"];
Console.WriteLine(queryName);
ViewMetaData vm = new ViewMetaData();
vm.ViewName = queryName;
vm.ViewQuery = queryText;
result.views.Add(vm);
}
connector.Close();
}
}
catch (OleDbException ex)
{
Console.Out.WriteLine("Exception fetching schema metadata: {0}", ex.Message);
}
return result;
}
/// <summary>
/// Sets the parameter value for a SQLite query
/// </summary>
/// <param name="column">Column metadata info. The parameter will be named after the column name</param>
/// <param name="cmd">SQLite command where to set the parameter</param>
/// <param name="value">Value to set the parameter will be obtained from a row in this OleDbDataReader whose column has the column name</param>
private void SetSqlCommandParameterValueForColumn(ColumnMetaData column, SQLiteCommand cmd, DbDataReader value)
{
string parameterName = String.Format("@{0}", SchemaTablesMetaData.HexString(column.columnName));
cmd.Parameters[parameterName].Value = value[column.columnName];
}
/// <summary> /// Recreates a database schema using Metadata information /// </summary> /// <param name="schema">Metadata about the tables to create in the schema</param> public abstract void CloneSchema(SchemaTablesMetaData schema);
/// <summary> /// Recreates a database schema using Metadata information /// </summary> /// <param name="schema">Metadata about the tables to create in the schema</param> public abstract void CloneSchema(SchemaTablesMetaData schema);