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