Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        private List<SQLDatabaseModelItem> GetTeradataDatabaseModels(string sqlDialect, List<string> dbNames)
        {
            List<SQLDatabaseModelItem> modelItems = new List<SQLDatabaseModelItem>();
            this.ProgressInfo.CreateProgress();

            SQLDatabaseModelItem modelItem = new SQLDatabaseModelItem();
            modelItem.name = "default";
            modelItem.tables = new List<SQLTableModelItem>();

            int iiCounter = 0;
            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, "tables", replaces);

                // 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

                        SQLQuerry 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 = "" // item.Column6
                            };
                            tableModelItem.columns.Add(columnModelItem);
                        }
                    }
                    catch(Exception ex)
                    {
                        this.Log("Ignored error for table/view " + tableOrViewName + ", error:" + ex.Message);// ignore
                    }
                }

                this.Log("Tables #[" + modelItem.tables.Count + "] in database" + dbName + " processed.");
            }
            modelItems.Add(modelItem);

            return modelItems;
        }
Example #4
0
        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);
        }
Example #5
0
        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;
        }