private List <SQLQuery> GetQuerries(string sqlDialect, List <string> dbNames) { List <SQLQuery> ret = new List <SQLQuery>(); int count = 0; bool firstSqlCommands = true; foreach (var dbName in dbNames) { Logger.Log(String.Format("1. Processing queries in db [{0}].", 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; List <SQLResult> result = new List <SQLResult>(); foreach (var item in sqls) { DBExecutor.RunSql(result, item); } Logger.Log(String.Format("3. Received {0} queries, saving them...", result.Count)); int savingQueryCnt = 0; foreach (var item in result) { if (result.Count != 0) { this.ProgressInfo.SetProgressPercent(15 + 40 * (count / result.Count), "Collecting queries."); } SQLQuery queryItem = new SQLQuery() { sourceCode = item.Column0, name = item.Column1, groupName = item.Column2, database = item.Column3, schema = item.Column4 }; savingQueryCnt++; ret.Add(queryItem); count++; } } return(ret); }
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); }
/// <summary> /// Gets all files that are matched with fileMask and appends them to dbStructure as queries. /// </summary> /// <param name="dbStructure"></param> private void GetQueriesFromFS(SQLCompleteStructure dbStructure) { Logger.Log("Getting data from Filesystem."); FileSystemData fsData = new FileSystemData(); fsData.Load(); string[] allFiles = Directory.GetFiles(fsData.ConfFile.InputDir, fsData.ConfFile.FileMask, SearchOption.AllDirectories); foreach (var path in allFiles) { string fileString = File.ReadAllText(path); SQLQuery newQuery = new SQLQuery(); newQuery.name = Path.GetFileNameWithoutExtension(path); newQuery.sourceCode = fileString; newQuery.schema = fsData.ConfFile.DefaultSchema; newQuery.database = fsData.ConfFile.DefaultDatabase; dbStructure.queries.Add(newQuery); } }
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); }
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); }