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