public static Executor CreateExecutor(DBExecutor dbExecutor, string sqlDialect)
{
if (sqlDialect == "teradata")
{
return new TeradataExecutor(dbExecutor);
}
else
{
return new Executor(dbExecutor);
}
}
public void Run(string customSqlSetName, Guid myKey, string sqlDialect, string exportFileName)
{
this.ProgressInfo.CreateProgress();
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
this.LogFileName = "SQLdepLog.txt";
// pripoj se do databaze
this.Log("Before database open.");
DBExecutor.Connect();
this.Log("Database open.");
this.ProgressInfo.SetProgressRatio(0.95, string.Empty);
SQLCompleteStructure dbStructure = this.Run(sqlDialect);
this.ProgressInfo.SetProgressRatio(0.05, string.Empty);
int totalTablesCount = 0;
foreach (var item in dbStructure.databaseModel.databases)
{
totalTablesCount += item.tables.Count;
}
if (dbStructure.queries.Count == 0 && totalTablesCount == 0)
{
throw new Exception("None data collected. Missinq any querry or table.");
}
dbStructure.dialect = sqlDialect;
dbStructure.userAccountId = myKey.ToString();
dbStructure.customSqlSetName = customSqlSetName;
sw.Stop();
dbStructure.exportTime = sw.ElapsedMilliseconds.ToString();
myJson = this.SaveStructureToFile(dbStructure, exportFileName);
DBExecutor.Close();
}
catch (Exception ex)
{
this.Log("Error " + ex.Message + "\n" + ex.StackTrace);
throw;
}
finally
{
this.ProgressInfo.RemoveProgress();
}
}
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);
}
private List <SQLDBLink> GetDBLinks(string sqlDialect)
{
List <SQLDBLink> ret = new List <SQLDBLink>();
this.ProgressInfo.CreateProgress();
int count = 0;
try
{
// sql commands
List <string> sqls = this.GetSQLCommands(sqlDialect, "dblinks", null);
List <SQLResult> result = new List <SQLResult>();
int iiItem = 0;
foreach (var item in sqls)
{
this.ProgressInfo.SetProgressDone((double)100 * ++iiItem / sqls.Count, item);
DBExecutor.RunSql(result, item);
}
foreach (var item in result)
{
SQLDBLink dblinkItem = new SQLDBLink()
{
owner = item.Column0,
name = item.Column1,
userName = item.Column2,
host = item.Column3,
};
ret.Add(dblinkItem);
count++;
}
}
catch (Exception)
{
throw;
}
this.ProgressInfo.RemoveProgress();
return(ret);
}
private List <SQLDBLink> GetDBLinks(string sqlDialect)
{
List <SQLDBLink> ret = new List <SQLDBLink>();
int count = 0;
try
{
// sql commands
List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DBLINKS, true, null);
List <SQLResult> result = new List <SQLResult>();
foreach (var item in sqls)
{
//this.ProgressInfo.SetProgressDone((double)100 * ++iiItem / sqls.Count, item);
DBExecutor.RunSql(result, item);
}
foreach (var item in result)
{
this.ProgressInfo.SetProgressPercent(62 + 20 * (count / result.Count), String.Format("Colleting DB links({0}/{1}).", count, result.Count));
SQLDBLink dblinkItem = new SQLDBLink()
{
owner = item.Column0,
name = item.Column1,
userName = item.Column2,
host = item.Column3,
};
ret.Add(dblinkItem);
count++;
}
}
catch (Exception)
{
throw;
}
return(ret);
}
private List <string> GetTeradataDbNames(string sqlDialect)
{
List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DATABASES, true, null);
List <SQLResult> result = new List <SQLResult>();
foreach (var item in sqls)
{
DBExecutor.RunSql(result, item);
}
List <string> ret = new List <string>();
foreach (var item in result)
{
// trimm outer spaces
string dbName = item.Column0.Trim();
ret.Add(dbName);
}
return(ret);
}
private List <string> GetDbNames(string sqlDialect)
{
List <string> sqls = this.GetSQLCommands(sqlDialect, "databases", null);
List <SQLResult> result = new List <SQLResult>();
foreach (var item in sqls)
{
DBExecutor.RunSql(result, item);
}
List <string> ret = new List <string>();
foreach (var item in result)
{
ret.Add(item.Column0);
}
#if DEBUG
ret = ret.Where(x => !x.StartsWith("Fle") && !x.StartsWith("Mu") && !x.StartsWith("Od") && !x.StartsWith("Poj") && !x.StartsWith("Rel") && !x.StartsWith("Te")).ToList();
#endif
return(ret);
}
private List <string> GetDbNames(string sqlDialect)
{
List <string> sqls = this.GetSQLCommands(sqlDialect, Purpose.DATABASES, true, null);
List <SQLResult> result = new List <SQLResult>();
foreach (var item in sqls)
{
Logger.Log(string.Format("GetDbNames processing cmd {0}", item));
DBExecutor.RunSql(result, item);
}
List <string> ret = new List <string>();
foreach (var item in result)
{
ret.Add(item.Column0);
}
Logger.Log(string.Format("GetDBNames found {0} dbs: {1}.", ret.Count, string.Join(", ", ret)));
return(ret);
}
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 string BuildConnectionString(DBExecutor dbExecutor)
{
DBExecutor.UseDriver useDriverType;
string driverName = this.GetDriverName(out useDriverType);
ComboBoxDSNItem dsnItem = this.GetSelectedDSNName();
string dsn = dsnItem.IsDSN ? dsnItem.Text : string.Empty;
return dbExecutor.BuildConnectionString(this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex),
dsn,
this.GetAuthTypeName(this.comboBoxAuthType.SelectedIndex),
this.textBoxServerName.Text,
this.textBoxPort.Text,
this.textBoxDatabaseName.Text,
this.textBoxLoginName.Text,
this.textBoxLoginPassword.Text,
driverName, useDriverType);
}
private string GetDriverName(out DBExecutor.UseDriver value)
{
int idx = this.comboBoxDriverName.SelectedIndex;
if (idx >= 0 && idx < this.comboBoxDriverName.Items.Count)
{
SQLDep.ComboBoxDriverItem item = (SQLDep.ComboBoxDriverItem) this.comboBoxDriverName.Items[idx];
value = item.UseDriverType;
return item.Text;
}
else
{
value = DBExecutor.UseDriver.DEFAULT;
return string.Empty;
}
}
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);
}
public Executor(DBExecutor dbExecutor)
{
this.DBExecutor = dbExecutor;
this.runId = Guid.NewGuid().ToString();
this.ProgressInfo = new ProgressInfo();
}
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);
}
public TeradataExecutor(DBExecutor dbExecutor)
: base(dbExecutor)
{
}
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);
}
private void buttonTestConnection_Click(object sender, EventArgs e)
{
DBExecutor dbExecutor = new DBExecutor();
string connection = this.BuildConnectionString(dbExecutor);
try
{
dbExecutor.Connect();
dbExecutor.Close();
this.buttonRun.Enabled = true;
this.SaveDialogSettings();
MessageBox.Show("Database connected!");
}
catch (Exception ex)
{
MessageBox.Show("Database not connected! \n\nConnection string:\n" + connection + "\n\nError: " + ex.ToString());
}
}
private void button1_Click(object sender, EventArgs e)
{
// only one execution at time is allowed, do nothing now
if (this.AsyncExecutor != null)
{
return;
}
FolderBrowserDialog fbd = new FolderBrowserDialog();
DialogResult result = fbd.ShowDialog();
try
{
this.SaveDialogSettings();
DBExecutor dbExecutor = new DBExecutor();
this.BuildConnectionString(dbExecutor);
string myName = this.textBoxUserName.Text.ToString();
Guid myKey;
if (!Guid.TryParse(this.textBoxKey.Text.ToString(), out myKey))
{
throw new Exception("Invalid or missing API key! Get one at https://www.sqldep.com/browser/upload/api");
}
string sqlDialect = this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex);
List<string> failedDbs = new List<string>();
Executor executor = ExecutorFactory.CreateExecutor(dbExecutor, sqlDialect);
string exportFileName = fbd.SelectedPath + "\\DBexport_" + executor.runId + "_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".json";
this.AsyncExecutor = new AsyncExecutor(myName, myKey, sqlDialect, exportFileName, executor);
this.AsyncExecutorThread = new Thread(AsyncExecutor.Run);
this.AsyncExecutorThread.Start();
new Thread(this.ShowProgress).Start();
}
catch (Exception ex)
{
string msg = ex.Message;
MessageBox.Show(msg);
}
}
private string BuildConnectionString(DBExecutor dbExecutor)
{
return dbExecutor.BuildConnectionString(this.GetDatabaseTypeName(this.comboBoxDatabase.SelectedIndex),
this.GetAuthTypeName(this.comboBoxAuthType.SelectedIndex),
this.textBoxServerName.Text,
this.textBoxPort.Text,
this.textBoxDatabaseName.Text,
this.textBoxLoginName.Text,
this.textBoxLoginPassword.Text);
}
public TeradataExecutor(DBExecutor dbExecutor) : base(dbExecutor)
{
}
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);
}
public Executor(DBExecutor dbExecutor)
{
this.DBExecutor = dbExecutor;
this.runId = Guid.NewGuid().ToString();
this.ProgressInfo = new ProgressInfo();
}
private List <SQLQuerry> GetTeradataQuerries(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);
{
// let us run this script two times, first run returns list of procedures, the second loads its definition
List <string> procedures = new List <string>();
// sql commands with replace
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, "tables", replaces);
// first command is list of procedures and views
List <SQLResult> result = new List <SQLResult>();
DBExecutor.RunSql(result, sqls.ElementAt(2));
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
{
SQLQuerry querryItem = this.GetQuerry(dbName, procedureOrViewName, itemType);
ret.Add(querryItem);
}
catch (Exception ex)
{
this.Log("Ignored error " + ex.Message);// ignore
}
}
}
}
this.ProgressInfo.RemoveProgress();
return(ret);
}
