} // GetCatalogTableIndexes /* ** GetCatalogTablePrimaryKey ** ** Description: ** Find the fields that make up the table's primary key. ** ** History: ** 10-Feb-03 (thoda04) ** Created. */ /// <summary> /// Find the fields that make up the table's primary key. /// </summary> public Catalog.Table GetCatalogTablePrimaryKey(Catalog.Table catTable) { string sql_lvl = AdvanConnect.conn.dbCaps.getDbCap("COMMON/SQL_LEVEL"); int cs_lvl = 0; int key_id; int key_id_prior = -1; Object obj; if (sql_lvl != null) // get the Ingres or Gateway support level { try { cs_lvl = Int32.Parse(sql_lvl); } catch (Exception /*ignore*/) {} } ; IDbCommand cmd = AdvanConnect.Connection.CreateCommand(); IDataParameter parm; 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); IDataReader rdr = null; bool PrimaryKeyFound = false; // if Ingres server and iikeys is supported if (AdvanConnect.conn.is_ingres && cs_lvl > 601) { cmd.CommandText = "SELECT DISTINCT k.column_name, k.key_position " + "FROM iikeys k, iiconstraints c " + "WHERE k.schema_name = ? AND k.table_name = ? AND " + "c.constraint_type = 'P' AND " + "k.constraint_name = c.constraint_name "; // send the query to the database catalog to get keys rdr = null; try { // read the primary key columns of table from iikeys rdr = cmd.ExecuteReader(); while (rdr.Read()) // process list of columns { if (rdr.IsDBNull(0)) // skip if somehow null { continue; } String columnName = rdr.GetString(0).TrimEnd(); // find the primary key column in the table's columns foreach (Catalog.Column col in catTable.Columns) { if (col.ColumnName.Equals(columnName)) { if (rdr.IsDBNull(1)) { col.PrimaryKeySequence = 0; } else { obj = rdr.GetValue(1); // int32 or Oracle NUMERIC float col.PrimaryKeySequence = Convert.ToInt32(obj); PrimaryKeyFound = true; } break; } } // end loop through columns } // end while loop reading through columns in catalog } catch (SqlEx) // ex) { //Console.WriteLine(ex); throw; } finally { if (rdr != null) { rdr.Close(); } } if (PrimaryKeyFound) { return(catTable); // no need to look further } } // end if Ingres server // Primary key not found in iikeys; try iialt_columns. // Use the primary key or first unique key. cmd.CommandText = "SELECT DISTINCT k.column_name, k.key_sequence, k.key_id " + "FROM iialt_columns k " + "WHERE table_owner = ? AND table_name = ? AND " + "key_sequence <> 0 " + "ORDER BY 3"; // send the query to the database catalog to get keys rdr = null; try { // read the first unique key columns of table from iialt_columns rdr = cmd.ExecuteReader(); while (rdr.Read()) // process list of columns { if (rdr.IsDBNull(0)) // skip if somehow null { continue; } if (rdr.IsDBNull(2)) { key_id = 0; } else { obj = rdr.GetValue(2); // int32 or Oracle NUMERIC float key_id = Convert.ToInt32(obj); } if (PrimaryKeyFound && key_id != key_id_prior) { break; // break out if found new index key } String columnName = rdr.GetString(0).TrimEnd(); // find the primary key column in the table's columns foreach (Catalog.Column col in catTable.Columns) { if (col.ColumnName.Equals(columnName)) { if (rdr.IsDBNull(1)) { col.PrimaryKeySequence = 0; } else { obj = rdr.GetValue(1); // int32 or Oracle NUMERIC float col.PrimaryKeySequence = Convert.ToInt32(obj); PrimaryKeyFound = true; key_id_prior = key_id; // save key_id of first key } break; // break out of table's columns search } } // end loop through columns } // end while loop reading through columns in catalog } catch (SqlEx) // ex) { //Console.WriteLine(ex); throw; } finally { if (rdr != null) { cmd.Cancel(); // cancel the remainder to avoid spurious msg rdr.Close(); } } if (PrimaryKeyFound) { return(catTable); } // Primary key not found in iikeys nor iialt_columns // Fall back to trying to use the physical underlying key foreach (Catalog.Column col in catTable.Columns) { col.PrimaryKeySequence = col.KeySequence; } return(catTable); } // GetCatalogTablePrimaryKey
} // 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
} // 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
} // FindMissingSchemaName /* ** GetAllCatalogTablesAndViews ** ** History: ** 23-Jun-03 (thoda04) ** Created. */ /// <summary> /// Get a list of all table or view names, qualified by /// "User Tables", "User Views", "All Tables", "All Views". /// </summary> /// <param name="qualification"></param> /// <returns>list of tables or views requested</returns> public ArrayList GetAllCatalogTablesAndViews(string qualification) { if (qualification == null) // if qualificatin is unknown then { return(new ArrayList()); // return empty list as safety check } ArrayList tablesList; // pointer to tables list we are building ArrayList viewsList; // pointer to views list we are building string schemaName = null; string readSchemaName; string readTableName; string readType; qualification = qualification.ToUpper( System.Globalization.CultureInfo.InvariantCulture); switch (qualification) { case "USER TABLES": { if (this.userTablesList != null) // return if already built { return(this.userTablesList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder != null || userSearchOrder.Count > 0) { schemaName = (string)this.userSearchOrder[0]; } break; } case "USER VIEWS": { if (this.userViewsList != null) // return if already built { return(this.userViewsList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); if (userSearchOrder == null) // build current_user, dba, $ingres { userSearchOrder = new UserSearchOrder(AdvanConnect); } if (userSearchOrder != null || userSearchOrder.Count > 0) { schemaName = (string)this.userSearchOrder[0]; } break; } case "ALL TABLES": { if (this.allTablesList != null) // return if already built { return(this.allTablesList); } tablesList = this.allTablesList = new ArrayList(); viewsList = this.allViewsList = new ArrayList(); break; } case "ALL VIEWS": { if (this.allViewsList != null) // return if already built { return(this.allViewsList); } tablesList = this.userTablesList = new ArrayList(); viewsList = this.userViewsList = new ArrayList(); break; } default: return(new ArrayList()); // return empty list as safety check } // end switch // We need to build the tables list and views list. // We distinguish between user and all for performance reasons // since all tables/views could be several thousand entries vs. // a couple dozen user tables/views. // We do batch table list and view list building together since // if the user is interested in a table list, we assume they are // interested in a view list on the next mouse click. This // eliminates a second query. // 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). IDbCommand cmd = AdvanConnect.Connection.CreateCommand(); IDataParameter parm; StringBuilder sb = new StringBuilder( "SELECT DISTINCT table_owner, table_name, table_type " + "FROM iitables " + "WHERE " + "system_use <> 'S' AND " + "table_type in ('T','V') AND " + "table_name NOT LIKE 'ii%' AND " + "table_name NOT LIKE 'II%' " , 200); if (schemaName != null) // "User Tables" or "User Views" { sb.Append("AND table_owner = ? "); // qualify by user parm = cmd.CreateParameter(); parm.Value = schemaName; parm.DbType = DbType.AnsiString; // don't send Unicode cmd.Parameters.Add(parm); } // end if schemaName != null sb.Append("ORDER BY 1, 2"); cmd.CommandText = sb.ToString(); // send the query to the database catalog to get columns IDataReader rdr = null; try { // read the table/view names from the catalog rdr = cmd.ExecuteReader(); while (rdr.Read()) // process list of owners { if (rdr.IsDBNull(0) || rdr.IsDBNull(1) || rdr.IsDBNull(2)) // skip table/view if somehow null { continue; } readSchemaName = rdr.GetString(0).TrimEnd(); readTableName = rdr.GetString(1).TrimEnd(); readType = rdr.GetString(2).TrimEnd(); Catalog.Table catTable = new Catalog.Table(readSchemaName, readTableName); if (readType == "T") { tablesList.Add(catTable); // add table to list } else { viewsList.Add(catTable); // add view to list } } // end while loop through tables/views in catalog } catch (SqlEx /*ex*/) { //Console.WriteLine(ex); throw; } finally { if (rdr != null) { rdr.Close(); } } // The right lists are built now. // Call ourselves recursively to return the desired list. return(GetAllCatalogTablesAndViews(qualification)); } // GetAllCatalogTablesAndViews