/// <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> /// 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);