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