private List <SQLDatabaseModelItem> GetTeradataDatabaseModels(string sqlDialect, List <string> dbNames)
{
List <SQLDatabaseModelItem> modelItems = new List <SQLDatabaseModelItem>();
SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem();
modelItem.name = "default";
modelItem.tables = new List <SQLTableModelItem>();
bool firstSqlCommands = true;
foreach (var dbName in dbNames)
{
// this.ProgressInfo.SetProgressDone((double)100 * ++iiCounter / dbNames.Count, dbName);
List <StrReplace> replaces = new List <StrReplace>();
StrReplace itemForReplace = new StrReplace()
{
SearchText = "##DBNAME##",
ReplaceText = dbName
};
replaces.Add(itemForReplace);
List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.TABLES, firstSqlCommands, replaces);
firstSqlCommands = false;
// tabulky a viecka
List <SQLResult> tablesAndViews = new List <SQLResult>();
DBExecutor.RunSql(tablesAndViews, sqls.ElementAt(0));
// sloupecky
List <SQLResult> allColumns = new List <SQLResult>();
DBExecutor.RunSql(allColumns, sqls.ElementAt(1));
foreach (var tableOrView in tablesAndViews)
{
string tableOrViewName = tableOrView.Column3.Trim();
List <SQLResult> columns = allColumns.Where(x => x.Column1.Trim() == tableOrViewName).ToList();
try
{
bool isView = false;
/*
*
* zatim neukladame create tabulek
*
* SQLQuery structure = null;
* if (tableOrView.Column1 == "T")
* {
* // table
* structure = this.GetQuerry(dbName, tableOrViewName, ItemType.TABLE);
* }
* else if (tableOrView.Column1 == "V")
* {
* structure = this.GetQuerry(dbName, tableOrViewName, ItemType.VIEW);
* isView = true;
* }
* else
* {
* throw new Exception("NYI");
* }
*/
SQLTableModelItem tableModelItem = new SQLTableModelItem()
{
database = string.Empty,
schema = dbName, // because Teradata parser treats DB as schema
name = tableOrViewName,
isView = (isView) ? "true" : "false",
columns = new List <SQLColumnModelItem>()
};
modelItem.tables.Add(tableModelItem);
// columns
foreach (var column in columns)
{
SQLColumnModelItem columnModelItem = new SQLColumnModelItem()
{
name = column.Column2,
dataType = column.Column3,
comment = String.IsNullOrEmpty(column.Column4) ? "" : column.Column4,
};
tableModelItem.columns.Add(columnModelItem);
}
}
catch (Exception ex)
{
Logger.Log("Ignored error for table/view " + tableOrViewName + ", error:" + ex.Message);// ignore
}
}
Logger.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed.");
}
modelItems.Add(modelItem);
return(modelItems);
}
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> GetTeradataDatabaseModels(string sqlDialect, List<string> dbNames)
{
List<SQLDatabaseModelItem> modelItems = new List<SQLDatabaseModelItem>();
this.ProgressInfo.CreateProgress();
SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem();
modelItem.name = "default";
modelItem.tables = new List<SQLTableModelItem>();
int iiCounter = 0;
foreach (var dbName in dbNames)
{
// this.ProgressInfo.SetProgressDone((double)100 * ++iiCounter / dbNames.Count, dbName);
List<StrReplace> replaces = new List<StrReplace>();
StrReplace itemForReplace = new StrReplace()
{
SearchText = "##DBNAME##",
ReplaceText = dbName
};
replaces.Add(itemForReplace);
List<string> sqls = this.GetSQLCommands(sqlDialect, "tables", replaces);
// tabulky a viecka
List<SQLResult> tablesAndViews = new List<SQLResult>();
DBExecutor.RunSql(tablesAndViews, sqls.ElementAt(0));
// sloupecky
List<SQLResult> allColumns = new List<SQLResult>();
DBExecutor.RunSql(allColumns, sqls.ElementAt(1));
foreach (var tableOrView in tablesAndViews)
{
string tableOrViewName = tableOrView.Column3.Trim();
List<SQLResult> columns = allColumns.Where(x => x.Column1.Trim() == tableOrViewName).ToList();
try
{
bool isView = false;
/*
zatim neukladame create tabulek
SQLQuerry structure = null;
if (tableOrView.Column1 == "T")
{
// table
structure = this.GetQuerry(dbName, tableOrViewName, ItemType.TABLE);
}
else if (tableOrView.Column1 == "V")
{
structure = this.GetQuerry(dbName, tableOrViewName, ItemType.VIEW);
isView = true;
}
else
{
throw new Exception("NYI");
}
*/
SQLTableModelItem tableModelItem = new SQLTableModelItem()
{
database = string.Empty,
schema = dbName, // because Teradata parser treats DB as schema
name = tableOrViewName,
isView = (isView) ? "true" : "false",
columns = new List<SQLColumnModelItem>()
};
modelItem.tables.Add(tableModelItem);
// columns
foreach (var column in columns)
{
SQLColumnModelItem columnModelItem = new SQLColumnModelItem()
{
name = column.Column2,
dataType = column.Column3,
comment = "" // item.Column6
};
tableModelItem.columns.Add(columnModelItem);
}
}
catch(Exception ex)
{
this.Log("Ignored error for table/view " + tableOrViewName + ", error:" + ex.Message);// ignore
}
}
this.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed.");
}
modelItems.Add(modelItem);
return modelItems;
}
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;
}