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);
}
private List <SQLQuerry> GetQuerries(string sqlDialect, List <string> dbNames)
{
List <SQLQuerry> ret = new List <SQLQuerry>();
int count = 0;
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);
List <SQLResult> result = new List <SQLResult>();
foreach (var item in sqls)
{
DBExecutor.RunSql(result, item);
}
foreach (var item in result)
{
SQLQuerry querryItem = new SQLQuerry()
{
sourceCode = item.Column0,
name = item.Column1,
groupName = item.Column2,
database = item.Column3,
schema = item.Column4
};
ret.Add(querryItem);
count++;
}
}
catch (Exception)
{
throw;
}
}
this.ProgressInfo.RemoveProgress();
return(ret);
}
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 <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 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);
}
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<SQLDatabaseModelItem> GetTeradataDatabaseModels(string sqlDialect, List<string> dbNames)
{
List<SQLDatabaseModelItem> modelItems = new List<SQLDatabaseModelItem>();
this.ProgressInfo.CreateProgress();
SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem();
modelItem.name = "default";
modelItem.tables = new List<SQLTableModelItem>();
int iiCounter = 0;
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, "tables", replaces);
// 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
SQLQuerry 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 = "" // item.Column6
};
tableModelItem.columns.Add(columnModelItem);
}
}
catch(Exception ex)
{
this.Log("Ignored error for table/view " + tableOrViewName + ", error:" + ex.Message);// ignore
}
}
this.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed.");
}
modelItems.Add(modelItem);
return modelItems;
}
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 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;
}
private List<SQLQuerry> GetQuerries(string sqlDialect, List<string> dbNames)
{
List<SQLQuerry> ret = new List<SQLQuerry>();
int count = 0;
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);
List<SQLResult> result = new List<SQLResult>();
foreach (var item in sqls)
{
DBExecutor.RunSql(result, item);
}
foreach (var item in result)
{
SQLQuerry querryItem = new SQLQuerry()
{
sourceCode = item.Column0,
name = item.Column1,
groupName = item.Column2,
database = item.Column3,
schema = item.Column4
};
ret.Add(querryItem);
count++;
}
}
catch (Exception)
{
throw;
}
}
this.ProgressInfo.RemoveProgress();
return ret;
}
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;
}
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 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);
}