Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        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();
            }
        }
Esempio n. 5
0
        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();
            }
        }
Esempio n. 6
0
 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);
             }
         }
     }
 }
Esempio n. 7
0
        /// <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);
            }
        }
Esempio n. 8
0
        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;
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        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;
        }
Esempio n. 11
0
        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;
        }