private List <String> GetColumnsFromDbModel(SQLDatabaseModel dbModel, string tableName, string schemaName, string DbName)
{
try
{
return(dbModel.databases
.Find(x => x.name == DbName).tables
.Find(x => x.name == tableName && x.schema == schemaName).columns
.Select(x => x.name).ToList());
}
catch (Exception ex)
{
Logger.Exception(String.Format("Exception during getting columns: {0}", ex.Message));
return(null);
}
}
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);
}
