Exemple #1
0
        }                     // GetCatalogTable

        /*
        ** GetCatalogTableIndexes
        **
        ** Description:
        **	Find each column that is unique, is the only column in this index,
        **	and is not-null.
        **
        ** History:
        **	07-Feb-03 (thoda04)
        **	    Created.
        */

        /// <summary>
        /// Fill in the unique index information for the table's columns.
        /// </summary>
        public Catalog.Table GetCatalogTableIndexes(Catalog.Table catTable)
        {
            ArrayList indexes = new ArrayList();              // list of unique indexes

            Catalog.Table  index;
            Catalog.Column indexColumn;

            if (catTable == null ||                 // safety check
                catTable.TableName == null ||       // if table name is missing or
                catTable.SchemaName == null)        // if schema is unknown then
            {
                return(catTable);                   //   just return return catTable as-is
            }
            IDbCommand     cmd = AdvanConnect.Connection.CreateCommand();
            IDataParameter parm;

            cmd.CommandText =
                "SELECT DISTINCT ic.column_name, ic.index_name " +
                "FROM iicolumns c, iiindex_columns ic, iiindexes i " +
                "WHERE " +
                "i.base_owner = ? AND i.base_name = ? AND " +
                "c.table_name = i.base_name AND " +
                "c.table_owner = i.base_owner AND " +
                "ic.index_name = i.index_name AND " +
                "ic.index_owner = i.index_owner AND " +
                "ic.column_name = c.column_name AND " +
                "i.unique_rule  = 'U' AND " +
                "c.column_nulls  <> 'Y' ";
            //----------------------

            parm        = cmd.CreateParameter();
            parm.Value  = catTable.SchemaName;
            parm.DbType = DbType.AnsiString;              // don't send Unicode
            cmd.Parameters.Add(parm);

            parm        = cmd.CreateParameter();
            parm.Value  = catTable.TableName;
            parm.DbType = DbType.AnsiString;              // don't send Unicode
            cmd.Parameters.Add(parm);

            // send the query to the database catalog to get columns
            IDataReader rdr = null;

            try
            {
                // read the columns of table from the catalog
                rdr = cmd.ExecuteReader();
                while (rdr.Read())                  // process list of columns
                {
                    if (rdr.IsDBNull(0) ||          // skip if somehow null
                        rdr.IsDBNull(1))
                    {
                        continue;
                    }
                    String indexName = rdr.GetString(1).TrimEnd();
                    index = null;
                    foreach (Catalog.Table indexSearch in indexes)
                    {
                        if (indexSearch.TableName.Equals(indexName))
                        {
                            index = indexSearch;
                            break;
                        }
                    }                                         // end loop through existing indexes

                    if (index == null)                        // index is not in our local list yet
                    {
                        index = new Catalog.Table(indexName); // add to list
                        indexes.Add(index);
                    }

                    indexColumn = new Catalog.Column();
                    //	indexColumn.SchemaName = index.SchemaName;  // not needed
                    //	indexColumn.TableName  = index.TableName;   // not needed
                    indexColumn.ColumnName = rdr.GetString(0).TrimEnd();


                    if (index.Columns == null)                      // if 1st time, build col list
                    {
                        index.Columns = new ArrayList();
                    }
                    index.Columns.Add(indexColumn); // add column to list
                }                                   // end while loop reading through columns in catalog
            }
            catch (SqlEx)                           // ex)
            {
                // Console.WriteLine(ex);
                throw;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
            }


            // At this point: indexes->index(Table)->indexColumn(Column)
            foreach (Catalog.Table indexSearch in indexes)
            {
                if (indexSearch.Columns == null ||                     // skip indexes with
                    indexSearch.Columns.Count != 1)                    // multiple columns
                {
                    continue;
                }

                indexColumn = (Catalog.Column)(indexSearch.Columns[0]);
                string indexColumnName = indexColumn.ColumnName;
                // match up the index column to the caller's table column
                foreach (Catalog.Column col in catTable.Columns)
                {
                    if (indexColumnName.Equals(col.ColumnName))
                    {
                        col.IsUnique = true;                          // mark Catalog.Column as unique
                        break;
                    }
                }             // end loop thru table's columns
            }                 // end loop through indexes

            return(catTable); // return a MetaData.Table with the Columns
        }                     // GetCatalogTableIndexes
Exemple #2
0
        }          // GetAllCatalogTablesAndViews

        /*
        ** GetCatalogTable
        **
        ** History:
        **	28-Jan-03 (thoda04)
        **	    Created.
        */

        /// <summary>
        /// Find the schemaname.tablename in the in-core catalog.
        /// Add if not found and build its list of columns.
        /// </summary>
        public Catalog.Table GetCatalogTable(MetaData.Table table)
        {
            if (table == null ||                // safety check
                table.TableName == null ||      // if table name is missing or
                table.SchemaName == null)       // if schema is unknown then
            {
                return(null);                   //   return "table doesn't exist'
            }
            // get a list of catalog columns for the specified schema and table
            // (use tilde to avoid ambiguity with "." in delimited identifiers
            // when building the key).
            string key = table.SchemaName + "^" + table.TableName;

            Catalog.Table  catTable = tables[key] as Catalog.Table;
            Catalog.Column catColumn;

            if (catTable != null)
            {
                return(catTable);
            }

            catTable = new Catalog.Table(table.SchemaName, table.TableName);

            IDbCommand     cmd = AdvanConnect.Connection.CreateCommand();
            IDataParameter parm;

            cmd.CommandText =
                "SELECT DISTINCT column_name, key_sequence, column_sequence " +
                "FROM iicolumns " +
                "WHERE table_owner = ? AND table_name = ? " +
                "ORDER BY column_sequence";

            parm        = cmd.CreateParameter();
            parm.Value  = table.SchemaName;
            parm.DbType = DbType.AnsiString;              // don't send Unicode
            cmd.Parameters.Add(parm);

            parm        = cmd.CreateParameter();
            parm.Value  = table.TableName;
            parm.DbType = DbType.AnsiString;              // don't send Unicode
            cmd.Parameters.Add(parm);

            // send the query to the database catalog to get columns
            IDataReader rdr = null;

            try
            {
                // read the columns of table from the catalog
                rdr = cmd.ExecuteReader();
                while (rdr.Read())                  // process list of owners
                {
                    if (rdr.IsDBNull(0))            // skip column if somehow null
                    {
                        continue;
                    }
                    catColumn            = new Catalog.Column();
                    catColumn.SchemaName = table.SchemaName;
                    catColumn.TableName  = table.TableName;
                    catColumn.ColumnName = rdr.GetString(0).TrimEnd();
                    // KeySequence may be used later for PrimaryKeySequence
                    if (rdr.IsDBNull(1))
                    {
                        catColumn.KeySequence = 0;
                    }
                    else
                    {
                        Object obj = rdr.GetValue(1);                                 // int32 or Oracle NUMERIC float
                        catColumn.KeySequence = Convert.ToInt32(obj);
                    }


                    if (catTable.Columns == null)
                    {
                        catTable.Columns = new ArrayList();
                    }
                    catTable.Columns.Add(catColumn); // add column to list
                }                                    // end while loop through columns in catalog
            }
            catch (SqlEx /*ex*/)
            {
                //Console.WriteLine(ex);
                throw;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
            }

            if (catTable.Columns == null)              // if no columns found
            {
                return(null);                          //   return "table doesn't exist'
            }
            GetCatalogTableIndexes(catTable);          // mark the unique columns
            GetCatalogTablePrimaryKey(catTable);       // mark primary key columns
            tables[key] = catTable;

            return(catTable); // return a MetaData.Table with the Columns
        }                     // GetCatalogTable