/// <summary>
/// Add sql for query to list of total sql for query
/// </summary>
/// <param name="mb"></param>
/// <param name="sql"></param>
internal void AddSqlToSqlStatementList(
GenericMetaBroker mb,
string sql)
{
string sqlWithDbLinks = sql;
DbConnectionMx mxConn = DbConnectionMx.MapSqlToConnection(ref sqlWithDbLinks); // add any dblinks as necessary
if (SqlList == null)
{
SqlList = new List <List <string> >();
}
List <string> sli = new List <string>();
if (Lex.IsDefined(mb.Label))
{
sli.Add(mb.Label);
}
else
{
sli.Add(mb.Qt.ActiveLabel + " (" + mb.Qt.MetaTable.Name + ")");
}
string formattedSql = OracleMx.FormatSql(sqlWithDbLinks);
sli.Add(formattedSql);
SqlList.Add(sli);
return;
}
/// <summary>
/// Get a metatable from a database catalog
/// </summary>
/// <param name="schemaDotTableName"></param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
string schemaDotTableName)
{
string[] sa = schemaDotTableName.Split('.');
if (sa.Length != 2)
{
return(null);
}
string schemaName = sa[0];
string tableName = sa[1];
DbSchemaMx schema = DbSchemaMx.GetSchemaInfoFromName(schemaName);
if (schema == null)
{
return(null); // unknown schema
}
if (Lex.IsDefined(schema.AliasFor))
{
schemaName = schema.AliasFor;
}
string dsName = schema.DataSourceName;
DataSourceMx dataSource = DbConnectionMx.GetRootDataSource(schemaDotTableName);
if (dataSource == null)
{
return(null);
}
DbConnectionMx conn = DbConnectionMx.GetConnection(dataSource.DataSourceName);
if (dataSource.DbType == DatabaseType.MySql)
{
return(MySqlMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
else if (dataSource.DbType == DatabaseType.Oracle)
{
return(OracleMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
else if (dataSource.DbType == DatabaseType.ODBC)
{
return(OdbcMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
throw new Exception("Database dictionary not supported for: " + schemaDotTableName);
}
/// <summary>
/// Select structures for a list of compound ids using a single SQL cursor.
/// </summary>
/// <param name="notInCache"></param>
/// <param name="mt"></param>
/// <param name="strMc"></param>
static Dictionary <string, MoleculeMx> SelectChemicalStructuresForCorpIdList(
List <string> cidList,
MetaTable mt)
{
List <string> notInCacheKeyList;
Dictionary <Int64, string> intKeyToStringKey;
DbType parmType;
MoleculeMx cs;
Int64 intCid;
string cid;
int li;
Stopwatch sw = Stopwatch.StartNew();
Dictionary <string, MoleculeMx> csDict = new Dictionary <string, MoleculeMx>();
List <string> notInCacheList = new List <string>();
// Get structures already in the cache
for (li = 0; li < cidList.Count; li++)
{
cid = cidList[li];
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
continue;
}
cid = CompoundId.Normalize(cid, mt);
if (!Int64.TryParse(cid, out intCid))
{
continue; // make sure an integer
}
if (MoleculeCache.Contains(cid)) // see if in cache
{
csDict[cid] = MoleculeCache.Get(cid);
continue;
}
else
{
notInCacheList.Add(cid);
}
}
// Retrieve structures from the database for those not in cache
if (notInCacheList.Count == 0)
{
return(csDict);
}
MetaColumn strMc = mt.GetMetaColumnByName("molstructure");
if (strMc == null)
{
return(null);
}
string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries)
string keyColName = mt.KeyMetaColumn.ColumnMap;
if (Lex.IsUndefined(keyColName))
{
keyColName = mt.KeyMetaColumn.Name;
}
string strColExpr = strMc.ColumnMap;
if (strColExpr == "")
{
strColExpr = strMc.Name;
}
if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge
{
if (!Lex.Contains(tableMap, "chime(")) // if no chime expression
{
strColExpr = "chime(ctab)"; // then create one (gets clob)
}
strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters
}
string sql =
"select " + keyColName + ", " + strColExpr + " " +
"from " + tableMap + " " +
"where " + keyColName + " in (<list>)";
DbCommandMx drd = new DbCommandMx();
bool isNumericKey = (mt.KeyMetaColumn.IsNumeric);
bool isStringKey = !isNumericKey;
if (isStringKey)
{
parmType = DbType.String;
}
else
{
parmType = DbType.Int64;
}
try
{
drd.PrepareListReader(sql, parmType);
drd.ExecuteListReader(notInCacheList);
while (drd.Read())
{
if (drd.Rdr.IsDBNull(0))
{
continue;
}
if (isNumericKey)
{
intCid = drd.GetLong(0);
cid = intCid.ToString();
}
else // string cid
{
cid = drd.GetString(0);
}
cid = CompoundId.Normalize(cid, mt);
string molString = drd.GetString(1);
cs = new MoleculeMx(MoleculeFormat.Chime, molString);
csDict[cid] = cs;
MoleculeCache.AddMolecule(cid, cs);
}
drd.Dispose();
int msTime = (int)sw.ElapsedMilliseconds;
return(csDict);
}
catch (Exception ex)
{
if (drd != null)
{
drd.Dispose();
}
throw new Exception(
"SelectStructuresForCorpIdList, table: " + mt.Name + "\n" +
"sql: " + OracleMx.FormatSql(sql) + "\n");
}
}
/// <summary>
/// Select a Molecule object for a compound id
/// </summary>
/// <param name="cid"></param>
/// <param name="mt"></param>
/// <returns></returns>
public static MoleculeMx SelectMoleculeForCid(
string cid,
MetaTable mt = null)
{
MetaColumn strMc = null;
string mtName = null, keyColName, strColExpr, chimeString;
MoleculeMx cs;
if (cid == null || cid == "")
{
return(null);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(null);
}
//if (mt != null) mtName = mt.Name; // debug
bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database
if (isUcdb) // if root metatable is user database then normalize based on key
{
mt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, mt);
}
else
{
cid = CompoundId.Normalize(cid, mt);
cs = MoleculeCache.Get(cid); // see if in cache
if (cs != null)
{
return(cs);
}
mt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (mt != null && Lex.Eq(mt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
mt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (mt == null)
{
return(null);
}
strMc = mt.FirstStructureMetaColumn; //.getmt.GetMetaColumnByName("molstructure");
if (strMc == null)
{
return(null);
}
cid = CompoundId.NormalizeForDatabase(cid, mt);
if (String.IsNullOrEmpty(cid))
{
return(null);
}
// Call external method to select structure
if (strMc.ColumnMap.StartsWith("InternalMethod", StringComparison.OrdinalIgnoreCase) ||
strMc.ColumnMap.StartsWith("PluginMethod", StringComparison.OrdinalIgnoreCase))
{ // call external method to get structure
List <MetaColumn> selectList = new List <MetaColumn>();
selectList.Add(mt.KeyMetaColumn);
selectList.Add(strMc);
object[] vo = new object[2];
vo[0] = cid;
try { GenericMetaBroker.CallLateBindMethodToFillVo(vo, 1, mt, selectList); }
catch (Exception ex)
{ return(null); }
if (vo[1] is MoleculeMx)
{
cs = (MoleculeMx)vo[1];
}
else if (vo[1] is string)
{
cs = MoleculeMx.MolStringToMoleculeMx((string)vo[1]);
}
else
{
cs = null;
}
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
return(cs);
}
// Normal case
//if (HelmEnabled) // temp till server back
//{
// cs = new MoleculeMx();
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
// return cs;
//}
string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries)
strColExpr = strMc.ColumnMap;
if (strColExpr == "")
{
strColExpr = strMc.Name;
}
if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge
{
if (!Lex.Contains(tableMap, "chime(")) // if no chime expression
{
strColExpr = "chime(ctab)"; // then create one (gets clob)
}
strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters
}
keyColName = mt.KeyMetaColumn.ColumnMap;
if (keyColName == "")
{
keyColName = mt.KeyMetaColumn.Name;
}
DbType parmType = DbType.String;
object cidObj = cid;
if (mt.KeyMetaColumn.IsNumeric)
{
if (!Lex.IsInteger(cid))
{
return(null); // if numeric col be sure cid is numeric also
}
parmType = DbType.Int64;
cidObj = Int64.Parse(cid);
}
string sql =
"select " + strColExpr + " " +
"from " + tableMap + " " +
"where " + keyColName + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareParameterized(sql, parmType);
drd.ExecuteReader(cidObj);
if (!drd.Read() || drd.Rdr.IsDBNull(0))
{
drd.Dispose();
return(null);
}
string molString = drd.GetString(0);
drd.Dispose();
MoleculeMx.TrySetStructureFormatPrefix(ref molString, strMc.DataTransform); // add molString type if indicated by data transform
cs = MoleculeMx.MolStringToMoleculeMx(molString);
cs.StoreKeyValueInMolComments(strMc, cid);
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
//if (MoleculeMx.HelmEnabled == true && Lex.IsInteger(cid))
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
return(cs);
}
catch (Exception ex)
{ // just log message & return;
DebugLog.Message("SelectMoleculeForCid Exception, Cid: " + cid + ", table: " + mt.Name + "\n" +
"sql: " + OracleMx.FormatSql(sql) + "\n" + DebugLog.FormatExceptionMessage(ex));
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
/// <summary>
/// Build and save SQL for use in Spotfire information link
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static int SaveSpotfireSql(
string sqlStmtName,
Query query)
{
DbConnectionMx conn = null;
string qtSql = "", sql, expr;
string keys = null;
string keyColName = "";
string t1KeyColExpr = "", keyColExpr;
string rootDataSource = "DEV857";
string rootSchema = "MBS_OWNER";
//if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries");
Query q = query.Clone();
// Clean up query before generating SQL
q.KeyCriteria = q.KeyCriteriaDisplay = "";
List <QueryTable> qtToRemove = new List <QueryTable>();
foreach (QueryTable qt0 in q.Tables)
{
if (qt0.SelectedCount <= 1 ||
!qt0.MetaTable.RetrievesDataFromQueryEngine)
{
qtToRemove.Add(qt0);
continue;
}
foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria
{
if (Lex.IsDefined(qc0.Criteria))
{
qc0.Criteria = qc0.CriteriaDisplay = "";
}
}
}
foreach (QueryTable qt0 in qtToRemove)
{
q.RemoveQueryTable(qt0);
}
string selectList = ""; // top-level list of selected columns
string fromList = ""; // sql for each QueryTable
string joinCriteria = ""; // join criteria between QueryTables
int remapCount = 0;
q.AssignUndefinedAliases();
q.MarkDuplicateNamesAndLabels();
for (int ti = 0; ti < q.Tables.Count; ti++)
{
QueryTable qt = q.Tables[ti];
if (ti == 0)
{
keyColName = qt.MetaTable.KeyMetaColumn.Name;
}
QueryEngine qe = new QueryEngine();
ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt);
eqp.ReturnQNsInFullDetail = false;
qtSql = qe.BuildSqlForSingleTable(eqp);
qtSql = Lex.Replace(qtSql, "/*+ hint */ ", "");
conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema
if (conn == null)
{
throw new Exception("Connection not found for: " + rootDataSource);
}
// Recast numeric cols that are integers as integers for Spotfire
List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn);
string qtSelectList = "";
remapCount = 0; // number of cols remapped
int sci = -1;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Selected)
{
continue;
}
sci++; // synch with cmdList
MetaColumn mc = qc.MetaColumn;
string mcName = mc.Name;
if (q.Tables.Count > 1) // if more than one table qualify by table name
{
mcName = qt.Alias + "." + mcName;
}
string colName = qc.UniqueName;
//if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug
//if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug
if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId))
{
DbColumnMetadata md = cmdList[sci];
expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr;
}
else if (mcName != colName)
{
expr = mcName + " " + colName;
remapCount++;
}
else
{
expr = mc.Name;
}
if (qtSelectList != "")
{
qtSelectList += ", ";
}
qtSelectList += expr;
}
if (selectList != "")
{
selectList += ", ";
}
selectList += qtSelectList;
if (fromList != "")
{
fromList += ", ";
}
fromList += "(" + qtSql + ") " + qt.Alias;
keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name;
if (ti == 0)
{
t1KeyColExpr = keyColExpr;
}
else
{
if (joinCriteria != "")
{
joinCriteria += " and ";
}
joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr;
}
}
selectList += " "; // be sure last col name in list is delimited with a space
if (q.Tables.Count == 1 && remapCount == 0)
{
sql = qtSql; // simple single table with no remapping of cols
}
else // combine list of elements
{
sql =
"select " + selectList +
" from " + fromList;
if (joinCriteria != "")
{
sql += " where " + joinCriteria;
}
sql = "select * from (" + sql + ")"; // encapsulate the SQL
}
int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName);
return(v2);
}
