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