public static Executor CreateExecutor(DBExecutor dbExecutor, string sqlDialect) { if (sqlDialect == "teradata") { return new TeradataExecutor(dbExecutor); } else { return new Executor(dbExecutor); } }
public void Run(string customSqlSetName, Guid myKey, string sqlDialect, string exportFileName) { this.ProgressInfo.CreateProgress(); try { Stopwatch sw = new Stopwatch(); sw.Start(); this.LogFileName = "SQLdepLog.txt"; // pripoj se do databaze this.Log("Before database open."); DBExecutor.Connect(); this.Log("Database open."); this.ProgressInfo.SetProgressRatio(0.95, string.Empty); SQLCompleteStructure dbStructure = this.Run(sqlDialect); this.ProgressInfo.SetProgressRatio(0.05, string.Empty); int totalTablesCount = 0; foreach (var item in dbStructure.databaseModel.databases) { totalTablesCount += item.tables.Count; } if (dbStructure.queries.Count == 0 && totalTablesCount == 0) { throw new Exception("None data collected. Missinq any querry or table."); } dbStructure.dialect = sqlDialect; dbStructure.userAccountId = myKey.ToString(); dbStructure.customSqlSetName = customSqlSetName; sw.Stop(); dbStructure.exportTime = sw.ElapsedMilliseconds.ToString(); myJson = this.SaveStructureToFile(dbStructure, exportFileName); DBExecutor.Close(); } catch (Exception ex) { this.Log("Error " + ex.Message + "\n" + ex.StackTrace); throw; } finally { this.ProgressInfo.RemoveProgress(); } }
public SQLQuery GetQuerry(string dbName, string name, ItemType type) { string sql = string.Empty; name = name.Trim(); switch (type) { case ItemType.PROCEDURE: sql = string.Format("SHOW PROCEDURE {0}.{1}", dbName, name); break; case ItemType.TABLE: sql = string.Format("SHOW TABLE {0}.{1}", dbName, name); break; case ItemType.VIEW: sql = string.Format("SHOW VIEW {0}.{1}", dbName, name); break; case ItemType.MACRO: sql = string.Format("SHOW MACRO {0}.{1}", dbName, name); break; } SQLQuery queryItem = new SQLQuery() { sourceCode = string.Empty, name = name, groupName = string.Empty, database = "default", schema = dbName // workaround for Teradata parser }; List <SQLResult> result = new List <SQLResult>(); DBExecutor.RunSql(result, sql); string sourceCodeLine = string.Empty; foreach (var item in result) { // teradata ma zvlastni oddelovac radku sourceCodeLine = item.Column0.Replace('\r', '\n'); queryItem.sourceCode += sourceCodeLine; } return(queryItem); }
private List <SQLDBLink> GetDBLinks(string sqlDialect) { List <SQLDBLink> ret = new List <SQLDBLink>(); this.ProgressInfo.CreateProgress(); int count = 0; try { // sql commands List <string> sqls = this.GetSQLCommands(sqlDialect, "dblinks", null); List <SQLResult> result = new List <SQLResult>(); int iiItem = 0; foreach (var item in sqls) { this.ProgressInfo.SetProgressDone((double)100 * ++iiItem / sqls.Count, item); DBExecutor.RunSql(result, item); } foreach (var item in result) { SQLDBLink dblinkItem = new SQLDBLink() { owner = item.Column0, name = item.Column1, userName = item.Column2, host = item.Column3, }; ret.Add(dblinkItem); count++; } } catch (Exception) { throw; } this.ProgressInfo.RemoveProgress(); return(ret); }
private List <SQLDBLink> GetDBLinks(string sqlDialect) { List <SQLDBLink> ret = new List <SQLDBLink>(); int count = 0; try { // sql commands List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DBLINKS, true, null); List <SQLResult> result = new List <SQLResult>(); foreach (var item in sqls) { //this.ProgressInfo.SetProgressDone((double)100 * ++iiItem / sqls.Count, item); DBExecutor.RunSql(result, item); } foreach (var item in result) { this.ProgressInfo.SetProgressPercent(62 + 20 * (count / result.Count), String.Format("Colleting DB links({0}/{1}).", count, result.Count)); SQLDBLink dblinkItem = new SQLDBLink() { owner = item.Column0, name = item.Column1, userName = item.Column2, host = item.Column3, }; ret.Add(dblinkItem); count++; } } catch (Exception) { throw; } return(ret); }
private List <string> GetTeradataDbNames(string sqlDialect) { List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DATABASES, true, null); List <SQLResult> result = new List <SQLResult>(); foreach (var item in sqls) { DBExecutor.RunSql(result, item); } List <string> ret = new List <string>(); foreach (var item in result) { // trimm outer spaces string dbName = item.Column0.Trim(); ret.Add(dbName); } return(ret); }
private List <string> GetDbNames(string sqlDialect) { List <string> sqls = this.GetSQLCommands(sqlDialect, "databases", null); List <SQLResult> result = new List <SQLResult>(); foreach (var item in sqls) { DBExecutor.RunSql(result, item); } List <string> ret = new List <string>(); foreach (var item in result) { ret.Add(item.Column0); } #if DEBUG ret = ret.Where(x => !x.StartsWith("Fle") && !x.StartsWith("Mu") && !x.StartsWith("Od") && !x.StartsWith("Poj") && !x.StartsWith("Rel") && !x.StartsWith("Te")).ToList(); #endif return(ret); }
private List <string> GetDbNames(string sqlDialect) { List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DATABASES, true, null); List <SQLResult> result = new List <SQLResult>(); foreach (var item in sqls) { Logger.Log(string.Format("GetDbNames processing cmd {0}", item)); DBExecutor.RunSql(result, item); } List <string> ret = new List <string>(); foreach (var item in result) { ret.Add(item.Column0); } Logger.Log(string.Format("GetDBNames found {0} dbs: {1}.", ret.Count, string.Join(", ", ret))); return(ret); }
private List <SQLDatabaseModelItem> GetTeradataDatabaseModels(string sqlDialect, List <string> dbNames) { List <SQLDatabaseModelItem> modelItems = new List <SQLDatabaseModelItem>(); SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem(); modelItem.name = "default"; modelItem.tables = new List <SQLTableModelItem>(); bool firstSqlCommands = true; foreach (var dbName in dbNames) { // this.ProgressInfo.SetProgressDone((double)100 * ++iiCounter / dbNames.Count, dbName); List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.TABLES, firstSqlCommands, replaces); firstSqlCommands = false; // tabulky a viecka List <SQLResult> tablesAndViews = new List <SQLResult>(); DBExecutor.RunSql(tablesAndViews, sqls.ElementAt(0)); // sloupecky List <SQLResult> allColumns = new List <SQLResult>(); DBExecutor.RunSql(allColumns, sqls.ElementAt(1)); foreach (var tableOrView in tablesAndViews) { string tableOrViewName = tableOrView.Column3.Trim(); List <SQLResult> columns = allColumns.Where(x => x.Column1.Trim() == tableOrViewName).ToList(); try { bool isView = false; /* * * zatim neukladame create tabulek * * SQLQuery structure = null; * if (tableOrView.Column1 == "T") * { * // table * structure = this.GetQuerry(dbName, tableOrViewName, ItemType.TABLE); * } * else if (tableOrView.Column1 == "V") * { * structure = this.GetQuerry(dbName, tableOrViewName, ItemType.VIEW); * isView = true; * } * else * { * throw new Exception("NYI"); * } */ SQLTableModelItem tableModelItem = new SQLTableModelItem() { database = string.Empty, schema = dbName, // because Teradata parser treats DB as schema name = tableOrViewName, isView = (isView) ? "true" : "false", columns = new List <SQLColumnModelItem>() }; modelItem.tables.Add(tableModelItem); // columns foreach (var column in columns) { SQLColumnModelItem columnModelItem = new SQLColumnModelItem() { name = column.Column2, dataType = column.Column3, comment = String.IsNullOrEmpty(column.Column4) ? "" : column.Column4, }; tableModelItem.columns.Add(columnModelItem); } } catch (Exception ex) { Logger.Log("Ignored error for table/view " + tableOrViewName + ", error:" + ex.Message);// ignore } } Logger.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed."); } modelItems.Add(modelItem); return(modelItems); }
private List <SQLDatabaseModelItem> GetDatabaseModels(string sqlDialect, List <string> dbNames) { List <SQLDatabaseModelItem> ret = new List <SQLDatabaseModelItem>(); this.ProgressInfo.CreateProgress(); int tableCount = 0; int synonymsCount = 0; int iiCounter = 0; foreach (var dbName in dbNames) { this.ProgressInfo.SetProgressDone((double)100 * ++iiCounter / dbNames.Count, dbName); try { SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem(); modelItem.name = dbName; this.Log("Getting tables in database" + dbName + "."); // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); // tabulky a viecka se sloupci dohromady modelItem.tables = new List <SQLTableModelItem>(); List <string> sqlsTablesWithColumns = this.GetSQLCommands(sqlDialect, "tables", replaces); List <SQLResult> tablesWithColumns = new List <SQLResult>(); foreach (var item in sqlsTablesWithColumns) { DBExecutor.RunSql(tablesWithColumns, item); } foreach (var item in tablesWithColumns) { string tableName = item.Column2; SQLTableModelItem tableModelItem = modelItem.tables.Find(x => x.name == tableName); if (tableModelItem == null) { tableModelItem = new SQLTableModelItem() { database = item.Column0, schema = item.Column1, name = item.Column2, isView = item.Column3, columns = new List <SQLColumnModelItem>() }; modelItem.tables.Add(tableModelItem); tableCount++; } SQLColumnModelItem columnModelItem = new SQLColumnModelItem() { name = item.Column4, dataType = item.Column5, comment = "" // item.Column6 }; tableModelItem.columns.Add(columnModelItem); } this.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed."); // synonyms this.Log("Getting synonyms in database" + dbName + "."); modelItem.synonyms = new List <SQLSynonymModelItem>(); List <string> sqlsSynonyms = this.GetSQLCommands(sqlDialect, "synonyms", replaces); List <SQLResult> synonyms = new List <SQLResult>(); foreach (var item in sqlsSynonyms) { DBExecutor.RunSql(synonyms, item); } foreach (var item in synonyms) { SQLSynonymModelItem synonymModelItem = new SQLSynonymModelItem() { database = item.Column0, schema = item.Column1, name = item.Column2, sourceName = item.Column4, sourceSchema = item.Column3, sourceDbLinkName = item.Column5 }; modelItem.synonyms.Add(synonymModelItem); synonymsCount++; } ret.Add(modelItem); this.Log("Synonyms #[" + sqlsSynonyms.Count + "] in database" + dbName + "processed."); } catch (Exception ex) { if (ex.Message.IndexOf("offline") >= 0) { ;//knonw error - databse is offline, ignore } else { throw; } } } return(ret); }
private string BuildConnectionString(DBExecutor dbExecutor) { DBExecutor.UseDriver useDriverType; string driverName = this.GetDriverName(out useDriverType); ComboBoxDSNItem dsnItem = this.GetSelectedDSNName(); string dsn = dsnItem.IsDSN ? dsnItem.Text : string.Empty; return dbExecutor.BuildConnectionString(this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex), dsn, this.GetAuthTypeName(this.comboBoxAuthType.SelectedIndex), this.textBoxServerName.Text, this.textBoxPort.Text, this.textBoxDatabaseName.Text, this.textBoxLoginName.Text, this.textBoxLoginPassword.Text, driverName, useDriverType); }
private string GetDriverName(out DBExecutor.UseDriver value) { int idx = this.comboBoxDriverName.SelectedIndex; if (idx >= 0 && idx < this.comboBoxDriverName.Items.Count) { SQLDep.ComboBoxDriverItem item = (SQLDep.ComboBoxDriverItem) this.comboBoxDriverName.Items[idx]; value = item.UseDriverType; return item.Text; } else { value = DBExecutor.UseDriver.DEFAULT; return string.Empty; } }
private List <SQLQuerry> GetOracleQuerries(string sqlDialect, List <string> dbNames) { List <SQLQuerry> ret = new List <SQLQuerry>(); this.ProgressInfo.CreateProgress(); int iiDbCounter = 0; foreach (var dbName in dbNames) { this.ProgressInfo.SetProgressDone((double)100 * ++iiDbCounter / dbNames.Count, dbName); try { // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); List <string> sqls = this.GetSQLCommands(sqlDialect, "queries", replaces); // vem prvni select, procedury spojime dle cisla radku List <SQLResult> firstBlock = new List <SQLResult>(); DBExecutor.RunSql(firstBlock, sqls.FirstOrDefault()); StringBuilder wholeCode = new StringBuilder(512 * 1024); string queryName = string.Empty; string querySchema = string.Empty; string queryDatabase = string.Empty; string queryGroup = string.Empty; int counter = 0; int query_counter = 0; foreach (var item in firstBlock) { if (item.Column5.Equals("1")) // dump previous wholeCode { if (counter > 0) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = wholeCode.ToString(), name = queryName, groupName = queryGroup, database = queryDatabase, schema = querySchema }; ret.Add(querryItem); this.Log("Query done " + query_counter); query_counter++; } wholeCode.Length = 0; wholeCode.Append(item.Column0); queryName = item.Column1; queryGroup = item.Column2; querySchema = item.Column4; queryDatabase = item.Column3; } else { wholeCode.Append(item.Column0); queryName = item.Column1; querySchema = item.Column4; queryDatabase = item.Column3; queryGroup = item.Column2; } counter++; } sqls.RemoveAt(0); List <SQLResult> secondBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(secondBlock, sqls.FirstOrDefault()); foreach (var item in secondBlock) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = "CREATE OR REPLACE FORCE VIEW " + item.Column2 + " AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(querryItem); } sqls.RemoveAt(0); List <SQLResult> thirdBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(thirdBlock, sqls.FirstOrDefault()); foreach (var item in thirdBlock) { SQLQuerry querryItem = new SQLQuerry() { sourceCode = "CREATE MATERIALIZED VIEW " + item.Column2 + " AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(querryItem); } } catch (Exception) { throw; } } this.ProgressInfo.RemoveProgress(); return(ret); }
public Executor(DBExecutor dbExecutor) { this.DBExecutor = dbExecutor; this.runId = Guid.NewGuid().ToString(); this.ProgressInfo = new ProgressInfo(); }
private List <SQLQuery> GetTeradataQuerries(string sqlDialect, List <string> dbNames) { List <SQLQuery> ret = new List <SQLQuery>(); bool firstSqlCommands = true; foreach (var dbName in dbNames) { { List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); // load script with replaces for the given database/procedure List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.QUERIES, firstSqlCommands, replaces); firstSqlCommands = false; // first command is list of procedures and views List <SQLResult> result = new List <SQLResult>(); DBExecutor.RunSql(result, sqls.ElementAt(0)); foreach (var item in result) { string procedureOrViewName = item.Column2; ItemType itemType = ItemType.PROCEDURE; switch (item.Column3.Trim()) { case "T": itemType = ItemType.TABLE; break; case "V": itemType = ItemType.VIEW; break; case "P": itemType = ItemType.PROCEDURE; break; case "M": itemType = ItemType.MACRO; break; default: throw new Exception("NYI"); } try { SQLQuery queryItem = this.GetQuerry(dbName, procedureOrViewName, itemType); ret.Add(queryItem); } catch (Exception ex) { Logger.Log("Ignored error " + ex.Message); } } } } return(ret); }
public TeradataExecutor(DBExecutor dbExecutor) : base(dbExecutor) { }
private List <SQLQuery> GetOracleQuerries(string sqlDialect, List <string> dbNames, SQLDatabaseModel databaseModel) { List <SQLQuery> ret = new List <SQLQuery>(); bool firstSqlCommands = true; foreach (var dbName in dbNames) { //this.ProgressInfo.SetProgressDone((double)100* ++iiDbCounter / dbNames.Count, dbName); // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.QUERIES, firstSqlCommands, replaces); firstSqlCommands = false; try { // vem prvni select, procedury spojime dle cisla radku List <SQLResult> firstBlock = new List <SQLResult>(); DBExecutor.RunSql(firstBlock, sqls.FirstOrDefault()); StringBuilder wholeCode = new StringBuilder(512 * 1024); string queryName = string.Empty; string querySchema = string.Empty; string queryDatabase = string.Empty; string queryGroup = string.Empty; int counter = 0; int query_counter = 0; foreach (var item in firstBlock) { if (firstBlock.Count != 0) { this.ProgressInfo.SetProgressPercent(15 + 30 * (counter / firstBlock.Count), "Collecting queries."); } if (item.Column5.Equals("1")) // dump previous wholeCode { if (counter > 0) { SQLQuery queryItem = new SQLQuery() { sourceCode = wholeCode.ToString(), name = queryName, groupName = queryGroup, database = queryDatabase, schema = querySchema }; ret.Add(queryItem); Logger.Log("Query done " + query_counter); query_counter++; } wholeCode.Length = 0; wholeCode.Append(item.Column0); queryName = item.Column1; queryGroup = item.Column2; querySchema = item.Column4; queryDatabase = item.Column3; } else { wholeCode.Append(item.Column0); queryName = item.Column1; querySchema = item.Column4; queryDatabase = item.Column3; queryGroup = item.Column2; } counter++; } sqls.RemoveAt(0); List <SQLResult> secondBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(secondBlock, sqls.FirstOrDefault()); Logger.Log("Processing second block."); foreach (var item in secondBlock) { List <String> colArr = GetColumnsFromDbModel(databaseModel, item.Column1, item.Column4, item.Column3); if (colArr == null) { Logger.Log(String.Format("Skipping table {0}, columns not found in database model.", item.Column2)); continue; } for (int i = 0; i < colArr.Count; i++) { colArr[i] = String.Format("\"{0}\"", colArr[i]); } string columns = String.Join(",", colArr); SQLQuery queryItem = new SQLQuery() { sourceCode = "CREATE OR REPLACE FORCE VIEW " + item.Column2 + " (" + columns + ") AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); List <SQLResult> thirdBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(thirdBlock, sqls.FirstOrDefault()); Logger.Log("Processing third block."); foreach (var item in thirdBlock) { List <String> colArr = GetColumnsFromDbModel(databaseModel, item.Column1, item.Column4, item.Column3); if (colArr == null) { Logger.Log(String.Format("Skipping table {0}, columns not found in database model.", item.Column2)); continue; } for (int i = 0; i < colArr.Count; i++) { colArr[i] = String.Format("\"{0}\"", colArr[i]); } string columns = String.Join(",", colArr); SQLQuery queryItem = new SQLQuery() { sourceCode = "CREATE MATERIALIZED VIEW " + item.Column2 + " (" + columns + ") AS " + item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); // There could be custom code left while (sqls.Count != 0) { String sql = sqls.FirstOrDefault(); List <SQLResult> customBlock = new List <SQLResult>(); DBExecutor.RunQuerySql(customBlock, sql); foreach (var item in customBlock) { SQLQuery queryItem = new SQLQuery() { sourceCode = item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; ret.Add(queryItem); } sqls.RemoveAt(0); } } catch (Oracle.ManagedDataAccess.Client.OracleException oe) { Logger.Log("Last executed SQL dump:\n" + sqls.FirstOrDefault()); throw oe; } catch (Exception) { throw; } } return(ret); }
private void buttonTestConnection_Click(object sender, EventArgs e) { DBExecutor dbExecutor = new DBExecutor(); string connection = this.BuildConnectionString(dbExecutor); try { dbExecutor.Connect(); dbExecutor.Close(); this.buttonRun.Enabled = true; this.SaveDialogSettings(); MessageBox.Show("Database connected!"); } catch (Exception ex) { MessageBox.Show("Database not connected! \n\nConnection string:\n" + connection + "\n\nError: " + ex.ToString()); } }
private void button1_Click(object sender, EventArgs e) { // only one execution at time is allowed, do nothing now if (this.AsyncExecutor != null) { return; } FolderBrowserDialog fbd = new FolderBrowserDialog(); DialogResult result = fbd.ShowDialog(); try { this.SaveDialogSettings(); DBExecutor dbExecutor = new DBExecutor(); this.BuildConnectionString(dbExecutor); string myName = this.textBoxUserName.Text.ToString(); Guid myKey; if (!Guid.TryParse(this.textBoxKey.Text.ToString(), out myKey)) { throw new Exception("Invalid or missing API key! Get one at https://www.sqldep.com/browser/upload/api"); } string sqlDialect = this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex); List<string> failedDbs = new List<string>(); Executor executor = ExecutorFactory.CreateExecutor(dbExecutor, sqlDialect); string exportFileName = fbd.SelectedPath + "\\DBexport_" + executor.runId + "_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".json"; this.AsyncExecutor = new AsyncExecutor(myName, myKey, sqlDialect, exportFileName, executor); this.AsyncExecutorThread = new Thread(AsyncExecutor.Run); this.AsyncExecutorThread.Start(); new Thread(this.ShowProgress).Start(); } catch (Exception ex) { string msg = ex.Message; MessageBox.Show(msg); } }
private string BuildConnectionString(DBExecutor dbExecutor) { return dbExecutor.BuildConnectionString(this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex), this.GetAuthTypeName(this.comboBoxAuthType.SelectedIndex), this.textBoxServerName.Text, this.textBoxPort.Text, this.textBoxDatabaseName.Text, this.textBoxLoginName.Text, this.textBoxLoginPassword.Text); }
private List <SQLDatabaseModelItem> GetDatabaseModels(string sqlDialect, List <string> dbNames) { List <SQLDatabaseModelItem> ret = new List <SQLDatabaseModelItem>(); int tableCount = 0; int synonymsCount = 0; bool firstSqlCommands = true; bool firstSqlCommands2 = true; foreach (var dbName in dbNames) { try { SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem(); modelItem.name = dbName; Logger.Log("Getting tables in database '" + dbName + "'."); // sql commands List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); // tabulky a viecka se sloupci dohromady modelItem.tables = new List <SQLTableModelItem>(); List <string> sqlsTablesWithColumns = this.GetSQLCommands(sqlDialect, Purpose.TABLES, firstSqlCommands, replaces); firstSqlCommands = false; List <SQLResult> tablesWithColumns = new List <SQLResult>(); foreach (var item in sqlsTablesWithColumns) { DBExecutor.RunSql(tablesWithColumns, item); } foreach (var item in tablesWithColumns) { string tableName = item.Column2; string schemaName = item.Column1; SQLTableModelItem tableModelItem = modelItem.tables.Find(x => x.name == tableName && x.schema == schemaName); if (tableModelItem == null) { tableModelItem = new SQLTableModelItem() { database = item.Column0, schema = item.Column1, name = item.Column2, isView = item.Column3, columns = new List <SQLColumnModelItem>() }; modelItem.tables.Add(tableModelItem); tableCount++; } SQLColumnModelItem columnModelItem = new SQLColumnModelItem() { name = item.Column4, dataType = item.Column5, comment = String.IsNullOrEmpty(item.Column6) ? "" : item.Column6, }; tableModelItem.columns.Add(columnModelItem); } Logger.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed."); // synonyms if (sqlDialect != "greenplum" && sqlDialect != "redshift" && sqlDialect != "postgres" && sqlDialect != "snowflake") { Logger.Log("Getting synonyms in database " + dbName + "."); modelItem.synonyms = new List <SQLSynonymModelItem>(); List <string> sqlsSynonyms = this.GetSQLCommands(sqlDialect, Purpose.SYNONYMS, firstSqlCommands2, replaces); firstSqlCommands2 = false; List <SQLResult> synonyms = new List <SQLResult>(); foreach (var item in sqlsSynonyms) { DBExecutor.RunSql(synonyms, item); } foreach (var item in synonyms) { SQLSynonymModelItem synonymModelItem = new SQLSynonymModelItem() { database = item.Column0, schema = item.Column1, name = item.Column2, sourceName = item.Column4, sourceSchema = item.Column3, sourceDbLinkName = item.Column5 }; modelItem.synonyms.Add(synonymModelItem); synonymsCount++; } Logger.Log("Synonyms #[" + sqlsSynonyms.Count + "] in database" + dbName + "processed."); } ret.Add(modelItem); } catch (Exception ex) { if (ex.Message.IndexOf("offline") >= 0) { ;//knonw error - databse is offline, ignore } else { throw; } } } return(ret); }
private List <SQLQuerry> GetTeradataQuerries(string sqlDialect, List <string> dbNames) { List <SQLQuerry> ret = new List <SQLQuerry>(); this.ProgressInfo.CreateProgress(); int iiDbCounter = 0; foreach (var dbName in dbNames) { this.ProgressInfo.SetProgressDone((double)100 * ++iiDbCounter / dbNames.Count, dbName); { // let us run this script two times, first run returns list of procedures, the second loads its definition List <string> procedures = new List <string>(); // sql commands with replace List <StrReplace> replaces = new List <StrReplace>(); StrReplace itemForReplace = new StrReplace() { SearchText = "##DBNAME##", ReplaceText = dbName }; replaces.Add(itemForReplace); // load script with replaces for the given database/procedure List <string> sqls = this.GetSQLCommands(sqlDialect, "tables", replaces); // first command is list of procedures and views List <SQLResult> result = new List <SQLResult>(); DBExecutor.RunSql(result, sqls.ElementAt(2)); foreach (var item in result) { string procedureOrViewName = item.Column2; ItemType itemType = ItemType.PROCEDURE; switch (item.Column3.Trim()) { case "T": itemType = ItemType.TABLE; break; case "V": itemType = ItemType.VIEW; break; case "P": itemType = ItemType.PROCEDURE; break; case "M": itemType = ItemType.MACRO; break; default: throw new Exception("NYI"); } try { SQLQuerry querryItem = this.GetQuerry(dbName, procedureOrViewName, itemType); ret.Add(querryItem); } catch (Exception ex) { this.Log("Ignored error " + ex.Message);// ignore } } } } this.ProgressInfo.RemoveProgress(); return(ret); }