/*
** GetDefaultSchema
**
** History:
** 17-Aug-06 (thoda04)
** Created.
*/
/// <summary>
/// Return the current_user as the default schema name.
/// </summary>
/// <returns></returns>
public string GetDefaultSchema()
{
if (userSearchOrder == null) // build current_user, dba, $ingres
{
userSearchOrder = new UserSearchOrder(AdvanConnect);
}
if (userSearchOrder == null || userSearchOrder.Count == 0)
{
return(null);
}
return((string)(userSearchOrder[0]));
}
} // 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
/*
** FindMissingSchemaName
**
** History:
** 28-Jan-03 (thoda04)
** Created.
*/
/// <summary>
/// Search catalog for schema name (table_owner) for a
/// table name reference that does not contain the schema qualification.
/// </summary>
public string FindMissingSchemaName(string tableName)
{
if (userSearchOrder == null) // build current_user, dba, $ingres
{
userSearchOrder = new UserSearchOrder(AdvanConnect);
}
if (userSearchOrder == null || userSearchOrder.Count == 0)
{
return(null);
}
if (tableName == null || tableName.Length == 0)
{
return(null);
}
if (userSearchOrder.Count == 0) // return if no users
{
return(null); // in list to search on
}
// (should never happen)
// build the query to send to the database catalog
IDbCommand cmd = AdvanConnect.Connection.CreateCommand();
StringBuilder sb = new StringBuilder(
"SELECT DISTINCT table_owner FROM iitables " +
"WHERE table_name = ? AND (", 100);
IDataParameter parm = cmd.CreateParameter();
parm.Value = tableName;
parm.DbType = DbType.AnsiString; // don't send Unicode
cmd.Parameters.Add(parm);
int i = 0;
foreach (string user in userSearchOrder)
{
if (i != 0)
{
sb.Append(" OR ");
}
i++;
sb.Append("table_owner = ?");
parm = cmd.CreateParameter();
parm.Value = user;
parm.DbType = DbType.AnsiString; // don't send Unicode
cmd.Parameters.Add(parm);
}
sb.Append(")");
cmd.CommandText = sb.ToString();
// SELECT table_owner FROM iitables WHERE table_name = <tablename>
// AND (table_owner = <user> OR table_owner = <dbaname> OR
// table_owner = <$ingres>)
// send the query to the database catalog to get possible owners
IDataReader rdr = null;
int ownerIndex;
int ownerIndexBest = 9;
string tableOwner;
try
{
rdr = cmd.ExecuteReader(); // read the owners with same tablename
while (rdr.Read()) // process list of owners
{
if (rdr.IsDBNull(0))
{
continue;
}
tableOwner = rdr.GetString(0).TrimEnd();
ownerIndex = userSearchOrder.IndexOf(tableOwner);
if (ownerIndex != -1 && ownerIndex < ownerIndexBest)
{
ownerIndexBest = ownerIndex; // a better user found
}
}
rdr.Close();
if (ownerIndexBest != 9)
{
return((string)userSearchOrder[ownerIndexBest]);
}
}
catch (SqlEx)
{
throw;
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
return(null); // no user found in userSearchOrder for named table
} // FindMissingSchemaName