|
public RunQuerySql ( List |
||
| result | List |
|
| cmd | string | |
| return | void | |
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 <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);
}