private string SaveStructureToFile(SQLCompleteStructure querries, string logJSONName)
{
querries.createdBy = "SQLdep v1.5.2";
querries.exportId = this.runId;
querries.physicalInstance = this.DBExecutor.Server;
var jsonSerialiser = new JavaScriptSerializer();
jsonSerialiser.MaxJsonLength = Int32.MaxValue;
var json = jsonSerialiser.Serialize(querries);
// post data
//string URI = " https://dev-jessie.sqldep.com/api/rest/sqlset/create/";
//string myParameters = json;
//using (WebClient wc = new WebClient())
//{
// wc.Headers[HttpRequestHeader.ContentType] = "application/json";
// string HtmlResult = wc.UploadString(URI, myParameters);
//}
StreamWriter wr = File.CreateText(logJSONName);
wr.Write(json);
wr.Close();
this.Log("Result data saved in " + logJSONName);
return(json);
}
public override SQLCompleteStructure Run(string sqlDialect, bool useFS)
{
// The following SELECTS map to JSON (see example.json)
SQLCompleteStructure ret = new SQLCompleteStructure();
Logger.Log("Getting list of databases");
List <string> dbNames = this.GetTeradataDbNames(sqlDialect);
Logger.Log("List of databases has " + dbNames.Count + " items.");
Logger.Log("Getting list of querries");
if (!useFS)
{
ret.queries = this.GetTeradataQuerries(sqlDialect, dbNames);
Logger.Log("List of querries has " + ret.queries.Count + " items.");
}
else
{
ret.queries = new List <SQLQuery>();
}
ret.databaseModel = new SQLDatabaseModel();
ret.databaseModel.databases = this.GetTeradataDatabaseModels(sqlDialect, dbNames);
return(ret);
}
public override SQLCompleteStructure Run(string sqlDialect)
{
this.ProgressInfo.CreateProgress();
// The following SELECTS map to JSON (see example.json)
SQLCompleteStructure ret = new SQLCompleteStructure();
this.Log("Getting list of databases");
List <string> dbNames = this.GetTeradataDbNames(sqlDialect);
this.Log("List of databases has " + dbNames.Count + " items.");
this.Log("Getting list of querries");
this.ProgressInfo.SetProgressRatio(0.45, "querries");
ret.queries = this.GetTeradataQuerries(sqlDialect, dbNames);
this.Log("List of querries has " + ret.queries.Count + " items.");
this.ProgressInfo.SetProgressRatio(0.55, "DB model");
ret.databaseModel = new SQLDatabaseModel();
ret.databaseModel.databases = this.GetTeradataDatabaseModels(sqlDialect, dbNames);
this.ProgressInfo.RemoveProgress();
return(ret);
}
public void Run(string customSqlSetName, Guid myKey, string sqlDialect, string exportFileName, bool useFs)
{
try
{
Stopwatch sw = new Stopwatch();
sw.Start();
// could be useful
Logger.Log(Environment.Is64BitOperatingSystem ? "64bit system" : "32bit system");
DBExecutor.Connect();
this.ProgressInfo.SetProgressPercent(10, "Collecting data from DB.");
// this will fill some dbStructure fields, such as queries and tables...
SQLCompleteStructure dbStructure = this.Run(sqlDialect, useFs);
// append queries from FS
if (useFs)
{
this.ProgressInfo.SetProgressPercent(85, "Collecting data from FileSystem.");
this.GetQueriesFromFS(dbStructure);
}
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. Missing any querry or table.");
}
dbStructure.dialect = sqlDialect;
dbStructure.userAccountId = myKey.ToString();
dbStructure.customSqlSetName = customSqlSetName;
sw.Stop();
dbStructure.exportTime = sw.ElapsedMilliseconds.ToString();
this.ProgressInfo.SetProgressPercent(95, "Saving collected data to file.");
if (sqlDialect == "snowflake")
{
makeDbModelCaseSensitive(dbStructure);
}
myJson = this.SaveStructureToFile(dbStructure, exportFileName);
DBExecutor.Close();
}
finally
{
this.ProgressInfo.RemoveProgress();
}
}
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();
}
}
private void makeDbModelCaseSensitive(SQLCompleteStructure dbStructure)
{
foreach (var database in dbStructure.databaseModel.databases)
{
database.name = String.Format("\"{0}\"", database.name);
foreach (var table in database.tables)
{
table.name = String.Format("\"{0}\"", table.name);
table.schema = String.Format("\"{0}\"", table.schema);
foreach (var column in table.columns)
{
column.name = String.Format("\"{0}\"", column.name);
}
}
}
}
/// <summary>
/// Gets all files that are matched with fileMask and appends them to dbStructure as queries.
/// </summary>
/// <param name="dbStructure"></param>
private void GetQueriesFromFS(SQLCompleteStructure dbStructure)
{
Logger.Log("Getting data from Filesystem.");
FileSystemData fsData = new FileSystemData();
fsData.Load();
string[] allFiles = Directory.GetFiles(fsData.ConfFile.InputDir, fsData.ConfFile.FileMask, SearchOption.AllDirectories);
foreach (var path in allFiles)
{
string fileString = File.ReadAllText(path);
SQLQuery newQuery = new SQLQuery();
newQuery.name = Path.GetFileNameWithoutExtension(path);
newQuery.sourceCode = fileString;
newQuery.schema = fsData.ConfFile.DefaultSchema;
newQuery.database = fsData.ConfFile.DefaultDatabase;
dbStructure.queries.Add(newQuery);
}
}
public override SQLCompleteStructure Run(string sqlDialect)
{
this.ProgressInfo.CreateProgress();
// The following SELECTS map to JSON (see example.json)
SQLCompleteStructure ret = new SQLCompleteStructure();
this.Log("Getting list of databases");
List<string> dbNames = this.GetTeradataDbNames(sqlDialect);
this.Log("List of databases has " + dbNames.Count + " items.");
this.Log("Getting list of querries");
this.ProgressInfo.SetProgressRatio(0.45, "querries");
ret.queries = this.GetTeradataQuerries(sqlDialect, dbNames);
this.Log("List of querries has " + ret.queries.Count + " items.");
this.ProgressInfo.SetProgressRatio(0.55, "DB model");
ret.databaseModel = new SQLDatabaseModel();
ret.databaseModel.databases = this.GetTeradataDatabaseModels(sqlDialect, dbNames);
this.ProgressInfo.RemoveProgress();
return ret;
}
public virtual SQLCompleteStructure Run(string sqlDialect)
{
this.ProgressInfo.CreateProgress();
// The following SELECTS map to JSON (see example.json)
SQLCompleteStructure ret = new SQLCompleteStructure();
this.Log("Getting list of databases");
List <string> dbNames = this.GetDbNames(sqlDialect);
this.Log("List of databases has " + dbNames.Count + " items.");
// 2. SELECT
// DDL ("queries" in JSON): procedures and views
// - sourceCode: `select * from table1` (string, required)
// - name (string, optional) - for visualization purposes - use your internal name if available
// - groupName (string, optional) - for visualization purposes - use your internal name if available
// - database (string, optional)
// - schema (string, optional)
//
// Expect columns in this order: SourceCode, Name, GroupName, Database, Schema
// 3. SELECT
// DbDef: ("databaseModel" in JSON) details on table and view columns
// ## Table (object)
//
// - schema: `ETL_SCHEMA` (string, required) - name of the schema for the table/view
// - name: ACCOUNT (string, required) - name of the table/view, avoid using duplicate names (only the first occurrence may be processed)
// - isView: false (boolean, optional, default) - true => view, otherwise => table
// - columns (array[Column]) - columns are processed sequentially, provide them in the same order as in your table/view
//
// ## Column (object)
//
// - name: `ACC_ID` (string, required) - name of the column, avoid using duplicate names (only the first occurrence may be processed)
// - dataType: `NUMBER(10)` (string, optional) - column data type
// - comment: `Unique account identifier` (string, optional) - column comment if available
//
// Expect columns in this order: Database, Schema, TableName, IsView, ColumnName, DataType, Comment, ColOrder
// 4. SELECT
// synonyms
// ## Synonym (object)
//
// - schema: `DW_SCHEMA` (string, required) - name of the synonym schema
// - name: ACCOUNTS (string, required) - synonym name, avoid using duplicate names (only the first occurrence may be processed)
// - sourceName: ACCOUNTS (string, required) - table/view name
// - sourceSchema: `ETL_SCHEMA` (string, optional) - name of the schema for source table/view
// - sourceDbLinkName (string, optional) - database link for source table/view
//
// Expect columns in this order: Database, Schema, Name, SoourceName, SourceSchema, SourceDbLinkName
// 5. SELECT
// ## DBLink (object)
//
// - owner : (string, required)
// - name : (string, required)
// - userName : (string, required)
// - host : (string, required)
//
// Expect columns in this order: Owner, Name, UserName, Host
this.Log("Getting list of querries");
this.ProgressInfo.SetProgressRatio(0.45, "querries");
if (sqlDialect == "oracle")
{
this.Log("Using Oracle dialect");
ret.queries = this.GetOracleQuerries(sqlDialect, dbNames);
}
else
{
ret.queries = this.GetQuerries(sqlDialect, dbNames);
}
this.Log("List of querries has " + ret.queries.Count + " items.");
this.ProgressInfo.SetProgressRatio(0.35, "DB model");
ret.databaseModel = new SQLDatabaseModel();
ret.databaseModel.databases = this.GetDatabaseModels(sqlDialect, dbNames);
this.Log("Getting list of dblinks");
this.ProgressInfo.SetProgressRatio(0.2, "dblinks");
ret.dblinks = this.GetDBLinks(sqlDialect);
this.Log("List of dblinks has " + ret.dblinks.Count + " items.");
this.ProgressInfo.RemoveProgress();
return(ret);
}
private string SaveStructureToFile(SQLCompleteStructure querries, string logJSONName)
{
querries.createdBy = "SQLdep v1.5.5";
querries.exportId = this.runId;
querries.physicalInstance = this.DBExecutor.Server;
var jsonSerialiser = new JavaScriptSerializer();
jsonSerialiser.MaxJsonLength = Int32.MaxValue;
var json = jsonSerialiser.Serialize(querries);
// post data
//string URI = " https://dev-jessie.sqldep.com/api/rest/sqlset/create/";
//string myParameters = json;
//using (WebClient wc = new WebClient())
//{
// wc.Headers[HttpRequestHeader.ContentType] = "application/json";
// string HtmlResult = wc.UploadString(URI, myParameters);
//}
StreamWriter wr = File.CreateText(logJSONName);
wr.Write(json);
wr.Close();
this.Log("Result data saved in " + logJSONName);
return json;
}
public virtual SQLCompleteStructure Run(string sqlDialect)
{
this.ProgressInfo.CreateProgress();
// The following SELECTS map to JSON (see example.json)
SQLCompleteStructure ret = new SQLCompleteStructure();
this.Log("Getting list of databases");
List<string> dbNames = this.GetDbNames(sqlDialect);
this.Log("List of databases has " + dbNames.Count + " items.");
// 2. SELECT
// DDL ("queries" in JSON): procedures and views
// - sourceCode: `select * from table1` (string, required)
// - name (string, optional) - for visualization purposes - use your internal name if available
// - groupName (string, optional) - for visualization purposes - use your internal name if available
// - database (string, optional)
// - schema (string, optional)
//
// Expect columns in this order: SourceCode, Name, GroupName, Database, Schema
// 3. SELECT
// DbDef: ("databaseModel" in JSON) details on table and view columns
// ## Table (object)
//
// - schema: `ETL_SCHEMA` (string, required) - name of the schema for the table/view
// - name: ACCOUNT (string, required) - name of the table/view, avoid using duplicate names (only the first occurrence may be processed)
// - isView: false (boolean, optional, default) - true => view, otherwise => table
// - columns (array[Column]) - columns are processed sequentially, provide them in the same order as in your table/view
//
// ## Column (object)
//
// - name: `ACC_ID` (string, required) - name of the column, avoid using duplicate names (only the first occurrence may be processed)
// - dataType: `NUMBER(10)` (string, optional) - column data type
// - comment: `Unique account identifier` (string, optional) - column comment if available
//
// Expect columns in this order: Database, Schema, TableName, IsView, ColumnName, DataType, Comment, ColOrder
// 4. SELECT
// synonyms
// ## Synonym (object)
//
// - schema: `DW_SCHEMA` (string, required) - name of the synonym schema
// - name: ACCOUNTS (string, required) - synonym name, avoid using duplicate names (only the first occurrence may be processed)
// - sourceName: ACCOUNTS (string, required) - table/view name
// - sourceSchema: `ETL_SCHEMA` (string, optional) - name of the schema for source table/view
// - sourceDbLinkName (string, optional) - database link for source table/view
//
// Expect columns in this order: Database, Schema, Name, SoourceName, SourceSchema, SourceDbLinkName
// 5. SELECT
// ## DBLink (object)
//
// - owner : (string, required)
// - name : (string, required)
// - userName : (string, required)
// - host : (string, required)
//
// Expect columns in this order: Owner, Name, UserName, Host
this.Log("Getting list of querries");
this.ProgressInfo.SetProgressRatio(0.45, "querries");
if (sqlDialect == "oracle")
{
this.Log("Using Oracle dialect");
ret.queries = this.GetOracleQuerries(sqlDialect, dbNames);
}
else
{
ret.queries = this.GetQuerries(sqlDialect, dbNames);
}
this.Log("List of querries has " + ret.queries.Count + " items.");
this.ProgressInfo.SetProgressRatio(0.35, "DB model");
ret.databaseModel = new SQLDatabaseModel();
ret.databaseModel.databases = this.GetDatabaseModels(sqlDialect, dbNames);
this.Log("Getting list of dblinks");
this.ProgressInfo.SetProgressRatio(0.2, "dblinks");
ret.dblinks = this.GetDBLinks(sqlDialect);
this.Log("List of dblinks has " + ret.dblinks.Count + " items.");
this.ProgressInfo.RemoveProgress();
return ret;
}
