Example #1
0
        private List <SQLQuerry> GetQuerries(string sqlDialect, List <string> dbNames)
        {
            List <SQLQuerry> ret = new List <SQLQuerry>();

            int count = 0;

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

                    List <SQLResult> result = new List <SQLResult>();
                    foreach (var item in sqls)
                    {
                        DBExecutor.RunSql(result, item);
                    }

                    foreach (var item in result)
                    {
                        SQLQuerry querryItem = new SQLQuerry()
                        {
                            sourceCode = item.Column0,
                            name       = item.Column1,
                            groupName  = item.Column2,
                            database   = item.Column3,
                            schema     = item.Column4
                        };


                        ret.Add(querryItem);
                        count++;
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            this.ProgressInfo.RemoveProgress();
            return(ret);
        }
Example #2
0
        public SQLQuerry 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;
            }

            SQLQuerry querryItem = new SQLQuerry()
            {
                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');
                querryItem.sourceCode += sourceCodeLine;
            }

            return(querryItem);
        }
Example #3
0
        public SQLQuerry 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;
            }

            SQLQuerry querryItem = new SQLQuerry()
            {
                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');
                querryItem.sourceCode += sourceCodeLine;
            }

            return querryItem;
        }
Example #4
0
        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);
        }
Example #5
0
        private List<SQLQuerry> GetQuerries(string sqlDialect, List<string> dbNames)
        {
            List<SQLQuerry> ret = new List<SQLQuerry>();

            int count = 0;

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

                    List<SQLResult> result = new List<SQLResult>();
                    foreach (var item in sqls)
                    {
                        DBExecutor.RunSql(result, item);
                    }

                    foreach (var item in result)
                    {
                        SQLQuerry querryItem = new SQLQuerry()
                        {
                            sourceCode = item.Column0,
                            name = item.Column1,
                            groupName = item.Column2,
                            database = item.Column3,
                            schema = item.Column4
                        };

                        ret.Add(querryItem);
                        count++;
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }
            this.ProgressInfo.RemoveProgress();
            return ret;
        }
Example #6
0
        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;
        }
Example #7
0
        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);
        }