/// <summary>
/// Build the set of results rows based on the current key set
/// </summary>
/// <param name="eqp"></param>
void BuildRelatedStructureSearchQueryEngineRows(
ExecuteQueryParms eqp)
{
HashSet <string> keySet = new HashSet <string>();
if (eqp.SearchKeySubset != null) // subsetting
{
foreach (string cid0 in eqp.SearchKeySubset)
{
keySet.Add(cid0);
}
}
List <StructSearchMatch> recs = RSS.AllMatches; // start with full match list
Results = new List <object[]>(); // destination results list (may or may not be subsetted)
for (int ri = 0; ri < recs.Count; ri++)
{
StructSearchMatch m = recs[ri];
if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid))
{
if (!keySet.Contains(m.SrcCid))
{
continue;
}
}
if (RestrictedDatabaseView.KeyIsRetricted(m.SrcCid))
{
continue;
}
object[] voa = new object[2];
voa[0] = m.SrcCid;
voa[1] = m;
Results.Add(voa);
}
CursorPos = -1;
return;
}
/// <summary>
/// Get list of compounds whose fragments match those of the compounds in the list.
/// </summary>
/// <param name="cnList"></param>
/// <returns></returns>
public static Dictionary <string, List <string> > GetAllSaltForms(
List <string> cnList)
{
int t0, t1;
t0 = TimeOfDay.Milliseconds();
Dictionary <string, List <string> > cidDict = new Dictionary <string, List <string> >();
List <string> cnList2 = new List <string>();
foreach (string s in cnList)
{ // get just the list entries that are integers (e.g. remove MFCD numbers)
if (Lex.IsInteger(s))
{
cnList2.Add(s);
}
}
t1 = TimeOfDay.Milliseconds() - t0;
if (cnList2.Count == 0)
{
return(cidDict);
}
//MetaTable mt = MetaTableCollection.Get("frag_occurrence");
MetaTable mt = MetaTableCollection.Get("CorpId_salt_isomer_info");
if (mt == null)
{
return(cidDict);
}
string sql = mt.TableMap; // get sql to use from metatable
if (sql.StartsWith("("))
{
sql = sql.Substring(1, sql.Length - 2); // remove surround parens if necessary
}
sql = Lex.Replace(sql, "where", "where CorpId in (<list>) and "); // add criteria needed to do list search
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareListReader(sql, DbType.Int32);
drd.ExecuteListReader(cnList2);
if (drd.Cancelled)
{
drd.Dispose();
return(null);
}
while (true)
{
if (!drd.ListRead())
{
break;
}
string cn = CompoundId.Normalize(drd.GetInt(0).ToString());
string cn2 = CompoundId.Normalize(drd.GetInt(1).ToString());
if (RestrictedDatabaseView.KeyIsRetricted(cn))
{
continue;
}
if (RestrictedDatabaseView.KeyIsRetricted(cn2))
{
continue;
}
if (!cidDict.ContainsKey(cn))
{
cidDict[cn] = new List <string>();
}
List <string> al = cidDict[cn];
if (al.Count == 0 || al[al.Count - 1] != cn2) // add if not dup
{
al.Add(cn2);
}
}
drd.Dispose();
}
catch (Exception ex)
{ // catch case non-numeric item in list, single-row subquery returns more than one row, etc.
drd.Dispose();
return(new Dictionary <string, List <string> >());
}
t1 = TimeOfDay.Milliseconds() - t0;
return(cidDict);
}
/// <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>
/// See if a compound id exists
/// </summary>
/// <param name="cid"></param>
/// <param name="mt">MetaTable to check in or if null check based on prefix</param>
/// <returns></returns>
public static bool Exists(
string cid,
MetaTable mt)
{
DbDataReader dr = null;
MetaTable keyMt;
object obj;
bool result;
string table, prefix, suffix, cid2;
int number;
if (cid == null || cid == "")
{
return(false);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(false);
}
if (mt != null && mt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key
{
keyMt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, keyMt);
}
else // from non-user database, get key table based on prefix
{
cid = CompoundId.Normalize(cid, mt);
keyMt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (keyMt != null && Lex.Eq(keyMt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
keyMt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (keyMt == null)
{
return(false);
}
if (cid.StartsWith("pbchm", StringComparison.OrdinalIgnoreCase))
{
return(true); // hack for pubchem until full set of compound ids available in Oracle
}
cid = CompoundId.NormalizeForDatabase(cid, keyMt); // get form that will match database
if (cid == null)
{
return(false);
}
string keyCol = keyMt.KeyMetaColumn.ColumnMap;
if (keyCol == "")
{
keyCol = keyMt.KeyMetaColumn.Name;
}
string sql =
"select " + keyCol +
" from " + keyMt.GetTableMapWithAliasAppendedIfNeeded() +
" where " + keyCol + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareMultipleParameter(sql, 1);
dr = drd.ExecuteReader(cid);
result = drd.Read();
}
catch (Exception ex)
{
// Don't log error since usually because of invalid format number
// DebugLog.Message("CompoundIdUtil.Exists - SQL Error:\n" + sql + "\n" + ex.Message);
drd.Dispose();
return(false);
}
if (result == true)
{
obj = dr.GetValue(0);
cid2 = CompoundId.Normalize(obj.ToString()); // need to normalize if number
// if (cid2 != CompoundId.Normalize(cid)) result=false;
}
dr.Close(); // need to close cursor
drd.Dispose();
return(result);
}