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);
}
public SQLQuerry 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;
}
SQLQuerry querryItem = new SQLQuerry()
{
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');
querryItem.sourceCode += sourceCodeLine;
}
return(querryItem);
}
public SQLQuerry 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;
}
SQLQuerry querryItem = new SQLQuerry()
{
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');
querryItem.sourceCode += sourceCodeLine;
}
return querryItem;
}
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<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 <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);
}
