private void GetColumnConstraints(TableMap map)
        {
            // primary key
            string    sql = String.Format(selectPrimaryKeyConstraints, map.TableId);
            SqlResult sr  = broker.Execute(sql);

            for (int i = 0; i < sr.Rows.Count; i++)
            {
                int      columnId = sr.GetInt(i, "column_id");
                FieldMap fm       = map.Fields.FindColumnById(columnId);
                if (fm != null)
                {
                    fm.SetIsPrimaryKey(true);
                }
            }
            // foreign key
            sql = String.Format(selectForeignKeyConstraints, map.TableId);
            sr  = broker.Execute(sql);
            for (int i = 0; i < sr.Rows.Count; i++)
            {
                int      columnId = sr.GetInt(i, "column_id");
                FieldMap fm       = map.Fields.FindColumnById(columnId);
                if (fm != null)
                {
                    fm.SetForeignKeyTableName(sr.GetString(i, "PT"));
                    fm.SetForeignKeyColumnName(sr.GetString(i, "PK"));
                }
            }
        }
Exemple #2
0
 protected void GetIdentityInformation(TableMap map)
 {
     if (map != null)
     {
         // also obtain identity column information
         GentleSqlFactory sf = provider.GetSqlFactory();
         try
         {
             string tableName = sf.GetTableName(map.TableName);
             foreach (FieldMap fm in map.Fields)
             {
                 if (fm.IsPrimaryKey)
                 {
                     if (!map.IsView)
                     {
                         SqlResult sr = broker.Execute(String.Format(selectIdentityInfo, tableName, fm.ColumnName));
                         fm.SetIsAutoGenerated(sr.GetInt(0, "IsIdentity") == 1);
                     }
                 }
             }
         }
         catch (Exception e)
         {
             Check.LogWarning(LogCategories.Metadata, "Unable to determine whether PK column of table {0} is an identity column.",
                              map.TableName);
             Check.LogWarning(LogCategories.Metadata, e.Message);
         }
     }
 }
        private void GetColumnData(TableMap map)
        {
            string    sql = String.Format(selectColumns, map.TableId);
            SqlResult sr  = broker.Execute(sql);

            for (int i = 0; i < sr.Rows.Count; i++)
            {
                string   column = sr.GetString(i, "column_name");
                FieldMap fm     = map.GetFieldMapFromColumn(column);
                if (fm == null)
                {
                    fm = new FieldMap(map, column);
                    map.Fields.Add(fm);
                }

                bool isNullable      = sr.GetString(i, "nulls").Equals("Y");
                bool isAutoGenerated = sr.GetString(i, "default").ToLower() == "autoincrement";
                fm.ColumnId = sr.GetInt(i, "column_id");
                fm.SetDbType(sr.GetString(i, "domain_name"), false);
                fm.SetIsNullable(isNullable);
                fm.SetIsAutoGenerated(isAutoGenerated);
                fm.SetSize(sr.GetInt(i, "width"));
            }
        }
        private void GetColumnData(TableMap map)
        {
            string       sql  = String.Format(selectColumns, map.TableName);
            SqlStatement stmt = broker.GetStatement(sql);

            stmt.StatementType = StatementType.Select;
            SqlResult sr = stmt.Execute();

            for (int i = 0; i < sr.Rows.Count; i++)
            {
                // returns columns: Field, Type, TypeSize, FieldSize, NotNull, HasDefault, Default
                string columnName = sr.GetString(i, "field");
                // get or create FieldMap for column
                FieldMap fm = map.GetFieldMapFromColumn(columnName);
                if (fm == null)
                {
                    fm = new FieldMap(map, columnName);
                    map.Fields.Add(fm);
                }
                bool isNullable = sr.GetString(i, "notnull").Trim().ToLower().StartsWith("f");
                if (fm != null)
                {
                    bool hasDefault = sr.GetBoolean(i, "hasdefault");
                    fm.SetDbType(sr.GetString(i, "type"), false);
                    int size = ExtractSize(sr.GetInt(i, "typesize"), sr.GetInt(i, "fieldsize"));
                    fm.SetSize(size);
                    fm.SetIsNullable(isNullable);
                    //fm.SetIsPrimaryKey( sr.GetString( i, "Key" ).Equals( "PRI" ) );
                    // fm.SetIsForeignKey( sr.GetString( i, "Key" ).Equals( "FOR" ) );
                    //if( fm.IsPrimaryKey )
                    //	fm.SetIsAutoGenerated( sr.GetString( i, "Key" ).Equals( "auto_increment" ) );
                    //if( sr.GetString( i, "HasDefault" ).Equals( "t" ) )
                    //	fm.SetMagicValue( sr.GetObject( i, "Default" ) );
                }
            }
        }
        private void GetColumnData(TableMap map)
        {
            string    sql = String.Format(selectColumns, map.TableId);
            SqlResult sr  = broker.Execute(sql);

            for (int i = 0; i < sr.Rows.Count; i++)
            {
                string   column     = sr.GetString(i, "column_name");
                FieldMap fm         = map.GetFieldMapFromColumn(column);
                bool     isNullable = sr.GetString(i, "nulls").Equals("Y");
                if (fm != null)
                {
                    fm.ColumnId = sr.GetInt(i, "column_id");
                    fm.SetDbType(sr.GetString(i, "domain_name"), false);
                    fm.SetIsNullable(isNullable);
                    fm.SetSize(sr.GetInt(i, "width"));
                }
                else                 // raise an error if we've detected a database/type mismatch
                {
                    // TODO disabled due to code restructuring
                    // Check.Verify( isNullable, Error.NoPropertyForNotNullColumn, column, map.Type );
                }
            }
        }
            "t.table_id = c.table_id and t.creator = u.user_id and t.table_id={0} and pkey in('Y','M')";             // table id

        /// <summary>
        /// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
        /// interface it implements a description of this method.
        /// </summary>
        public override void Analyze(string tableName)
        {
            try
            {
                bool      isSingleRun = tableName != null;
                SqlResult sr          = broker.Execute(selectTables);
                for (int i = 0; i < sr.Rows.Count; i++)
                {
                    try
                    {
                        string dbTableName = sr.GetString(i, "table_name");
                        if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
                        {
                            TableMap map = GetTableMap(dbTableName);
                            if (map == null)
                            {
                                map = new TableMap(provider, dbTableName);
                                maps[dbTableName.ToLower()] = map;
                            }
                            map.TableId = sr.GetInt(i, "table_id");
                            // get column information for this table
                            GetColumnData(map);
                            // get additional column information for this table
                            GetColumnConstraints(map);
                            // abort loop if analyzing single table only
                            if (isSingleRun)
                            {
                                break;
                            }
                        }
                    }
                    catch (GentleException fe)
                    {
                        // ignore errors caused by tables found in db but for which no map exists
                        // TODO this should be a config option
                        if (fe.Error != Error.NoObjectMapForTable)
                        {
                            throw fe;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
            }
        }
Exemple #7
0
        /// <summary>
        /// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
        /// interface it implements a description of this method.
        /// </summary>
        public override void Analyze(string tableName)
        {
            try
            {
                bool   isSingleRun  = tableName != null;
                string selectSingle = isSingleRun ? String.Format(SELECT_SINGLE, tableName) : String.Empty;

                // Check Oracle version and select appropriate SQL-Syntax
                SqlResult sr = broker.Execute(ORA_VERSION_SELECT);
                //				string ver = sr.GetString( 0, "Version" ).Substring( 0, 1 );
                //				int version = Convert.ToInt32( sr.GetString( 0, "Version" ).Substring( 0, 1 ) );

                string ver = sr.GetString(0, "Version");

                int indexOfDot = ver.IndexOf(".");
                if (indexOfDot < 0)
                {
                    throw new GentleException(Error.DeveloperError, "Unable to determine Oracle database version.");
                }

                int version = Convert.ToInt32(ver.Substring(0, indexOfDot));

                string select;
                string selectReferences;
                if (version < 9)
                {
                    // If Oracle version == '8.1.6' use no-views selectReferences
                    if (ver.Substring(0, 5).CompareTo("8.1.6") == 0)
                    {
                        selectReferences = ORA816_SELECT_REFERENCES;
                    }
                    else
                    {
                        selectReferences = ORA8_SELECT_REFERENCES;
                    }
                    select = ORA8_SELECT + selectSingle + ORDER_BY;
                }
                else
                {
                    select           = ORA9_SELECT + selectSingle + ORDER_BY;
                    selectReferences = ORA9_SELECT_REFERENCES;
                }

                sr = broker.Execute(select);
                // process result set using columns:
                // TableName, ColumnName, Type, Size, IsNullable, DefaultValue,
                // ConstraintName, ConstraintReference, ConstraintType, UpdateRule, DeleteRule
                for (int i = 0; i < sr.Rows.Count; i++)
                {
                    try
                    {
                        string dbTableName = sr.GetString(i, "TableName");
                        if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
                        {
                            // get or create TableMap for table
                            TableMap map = GetTableMap(dbTableName);
                            if (map == null)
                            {
                                map = new TableMap(provider, dbTableName);
                                maps[dbTableName.ToLower()] = map;
                            }
                            // get or create FieldMap for column
                            string   columnName = sr.GetString(i, "ColumnName");
                            FieldMap fm         = map.GetFieldMapFromColumn(columnName);
                            if (fm == null)
                            {
                                fm = new FieldMap(map, columnName);
                                map.Fields.Add(fm);
                            }
                            // get basic column information
                            fm.SetDbType(sr.GetString(i, "Type"), false);
                            fm.SetIsNullable(GetBoolean(sr.GetString(i, "IsNullable")));

                            if (sr[i, "Size"] != null)
                            {
                                if (fm.DbType == (long)OracleDbType.Clob)
                                {
                                    //Max 4GB
                                    //Preferred size 4294967296
                                    fm.SetSize(int.MaxValue);
                                }
                                else
                                {
                                    fm.SetSize(sr.GetInt(i, "Size"));
                                }
                            }

                            // get column constraint infomation
                            if (sr[i, "ConstraintName"] != null)
                            {
                                string typ = sr.GetString(i, "ConstraintType");
                                if (typ.ToLower().Equals("p"))
                                {
                                    fm.SetIsPrimaryKey(true);
                                }
                                else if (typ.ToLower().Equals("r"))
                                {
                                    string    conref = sr.GetString(i, "ConstraintReference");
                                    SqlResult res    = broker.Execute(String.Format(selectReferences, conref));
                                    fm.SetForeignKeyTableName(res.GetString(0, "ChildTable"));
                                    fm.SetForeignKeyColumnName(res.GetString(0, "ChildColumn"));
                                }
                            }
                        }
                    }
                    catch (GentleException fe)
                    {
                        // ignore errors caused by tables found in db but for which no map exists
                        // TODO this should be a config option
                        if (fe.Error != Error.NoObjectMapForTable)
                        {
                            throw;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Check.LogInfo(LogCategories.General, "Using provider {0} and connectionString {1}.",
                              provider.Name, provider.ConnectionString);
                Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
            }
        }
        public override void Analyze(string tableName)
        {
            GentleSqlFactory sf = provider.GetSqlFactory();

            try
            {
                bool isSingleRun = tableName != null;
                // Create foreign key statement
                SqlStatement fk = broker.GetStatement(StatementType.Select, selectReferences);
                sf.AddParameter(fk.Command, "TableName", (long)FbDbType.VarChar);
                sf.AddParameter(fk.Command, "ColumnName", (long)FbDbType.VarChar);
                // Get tables information
                SqlResult sr = broker.Execute(select);
                // process result set
                for (int i = 0; i < sr.Rows.Count; i++)
                {
                    try
                    {
                        string dbTableName = sr.GetString(i, "TableName").Trim();

                        if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
                        {
                            TableMap map = GetTableMap(dbTableName);
                            if (map == null)
                            {
                                map = new TableMap(provider, dbTableName);
                                maps[dbTableName.ToLower()] = map;
                            }

                            // get column information for this table
                            string   columnName = sr.GetString(i, "ColumnName").Trim();
                            FieldMap fm         = map.GetFieldMapFromColumn(columnName);
                            if (fm == null)
                            {
                                fm = new FieldMap(map, columnName);
                                map.Fields.Add(fm);
                            }
                            FbDbType type = GetFbDbType(
                                sr.GetInt(i, "ColumnDataType"),
                                sr[i, "ColumnSubType"] != null ? sr.GetInt(i, "ColumnSubType") : 0,
                                sr[i, "ColumnScale"] != null ? sr.GetInt(i, "ColumnScale") : 0);
                            fm.SetDbType((long)type);
                            if (sr[i, "NullFlag"] == null)
                            {
                                fm.SetIsNullable(true);
                            }
                            else
                            {
                                fm.SetIsNullable(false);
                            }
                            if (sr[i, "ColumnSize"] != null)
                            {
                                switch (type)
                                {
                                case FbDbType.Binary:
                                case FbDbType.Text:
                                    fm.SetSize(Int32.MaxValue);
                                    break;

                                default:
                                    fm.SetSize(sr.GetInt(i, "ColumnSize"));
                                    break;
                                }
                            }
                            if (sr.GetInt(i, "PrimaryKey") > 0)
                            {
                                fm.SetIsPrimaryKey(true);
                            }
                            if (sr.GetInt(i, "ForeignKey") > 0)
                            {
                                fk.SetParameter("TableName", map.TableName.ToUpper());
                                fk.SetParameter("ColumnName", columnName.ToUpper());

                                SqlResult res = fk.Execute();
                                fm.SetForeignKeyTableName(res.GetString(0, "FKTableName").Trim());
                                fm.SetForeignKeyColumnName(res.GetString(0, "FKColumnName").Trim());
                            }
                        }
                    }
                    catch (GentleException fe)
                    {
                        // ignore errors caused by tables found in db but for which no map exists
                        // TODO this should be a config option
                        if (fe.Error != Error.NoObjectMapForTable)
                        {
                            throw fe;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
            }
        }
        /// <summary>
        /// Please refer to the <see cref="GentleAnalyzer"/> class and the <see cref="IDatabaseAnalyzer"/>
        /// interface it implements a description of this method.
        /// </summary>
        public override void Analyze(string tableName)
        {
            GentleSqlFactory sf = provider.GetSqlFactory();

            try
            {
                bool isSingleRun = tableName != null;
                // don't quote reserved words here (table name is just a string parameter)
                string    sql = isSingleRun ? select + String.Format(selectSingle, tableName) : select;
                SqlResult sr  = broker.Execute(sql, null, null);
                // process result set using columns:
                // TableName, ColumnName, Type, Size, IsNullable, DefaultValue,
                // ConstraintName, ConstraintReference, ConstraintType,
                // UpdateRule, DeleteRule, TableType
                for (int i = 0; i < sr.Rows.Count; i++)
                {
                    try
                    {
                        string dbTableName = sr.GetString(i, "tablename");
                        if (!isSingleRun || tableName.ToLower().Equals(dbTableName.ToLower()))
                        {
                            TableMap map = GetTableMap(dbTableName);
                            if (map == null)
                            {
                                map = new TableMap(provider, dbTableName);
                                maps[dbTableName.ToLower()] = map;
                            }
                            map.IsView = sr.GetString(i, "TableType") == "VIEW";
                            // get column information for this table
                            string   columnName = sr.GetString(i, "ColumnName");
                            FieldMap fm         = map.GetFieldMapFromColumn(columnName);
                            if (fm == null)
                            {
                                fm = new FieldMap(map, columnName);
                                map.Fields.Add(fm);
                            }
                            // get basic column information
                            fm.SetDbType(sr.GetString(i, "Type"), false);                                 // sql server is always false
                            fm.SetIsNullable(GetBoolean(sr.GetString(i, "IsNullable")));
                            fm.SetIsAutoGenerated(sr.GetString(i, "DefaultValue").Length > 0 ? true : false);
                            if (sr[i, "Size"] != null && fm.DbType != (long)SqlDbType.Text)
                            {
                                fm.SetSize(sr.GetInt(i, "Size"));
                            }
                            // get column constraint infomation
                            if (sr[i, "ConstraintName"] != null)
                            {
                                string type = sr.GetString(i, "ConstraintType");
                                if (type.ToLower().Equals("primary key"))
                                {
                                    fm.SetIsPrimaryKey(true);
                                }
                                else if (type.ToLower().Equals("foreign key"))
                                {
                                    string conref = sr.GetString(i, "ConstraintReference");
                                    if (conref.StartsWith("IDX"))
                                    {
                                        string fkRef = sr.GetString(i, "ConstraintName");
                                        if (fkRef != null && fkRef.StartsWith("FK"))
                                        {
                                            conref = fkRef;
                                        }
                                    }
                                    SqlResult res = broker.Execute(String.Format(selectReferences, conref), null, null);
                                    if (res.ErrorCode == 0 && res.RowsContained == 1)
                                    {
                                        fm.SetForeignKeyTableName(res.GetString(0, "TableName"));
                                        fm.SetForeignKeyColumnName(res.GetString(0, "ColumnName"));
                                    }
                                    else
                                    {
                                        if (res.RowsContained == 0)
                                        {
                                            // see GOPF-155 for additional information
                                            Check.LogWarning(LogCategories.Metadata,
                                                             "Unable to obtain foreign key information for column {0} of table {1}.",
                                                             fm.ColumnName, map.TableName);
                                        }
                                        else
                                        {
                                            Check.LogWarning(LogCategories.Metadata, "Gentle 1.x does not support composite foreign keys.");
                                        }
                                    }
                                }
                            }
                            if (map.IsView)
                            {
                                // TODO
                                // process backing table members and infer PK/identity info
                                // requires that tables be processed before views!
                                //
                                //string sv = String.Format( selectViewDependencies, map.TableName );
                                //SqlResult res = broker.Execute( sv );
                            }
                        }
                    }
                    catch (GentleException fe)
                    {
                        // ignore errors caused by tables found in db but for which no map exists
                        // TODO this should be a config option
                        if (fe.Error != Error.NoObjectMapForTable)
                        {
                            throw;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Check.LogInfo(LogCategories.General, "Using provider {0} and connectionString {1}.",
                              provider.Name, provider.ConnectionString);
                Check.Fail(e, Error.Unspecified, "An error occurred while analyzing the database schema.");
            }
        }