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