/// <summary>
/// Prepare a query that takes a list as part of criteria
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterType"></param>
public void PrepareListReader(
DbCommandMx drd,
string sql,
DbType parameterType)
{
drd.PrepareListReader(sql, parameterType);
return;
}
/// <summary>
/// (OLD VERSION)
/// 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> > GetAllSaltFormsNew(
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 (!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>
/// Convert a multipivot table into a set of tables where data exists for
/// one or more of the compound identifiers in the list.
/// </summary>
/// <param name="qt">Current form of query table</param>
/// <param name="q">Query to add transformed tables to</param>
/// <param name="ResultKeys">Keys data will be retrieved for</param>
public override void ExpandToMultipleTables(
QueryTable qt,
Query q,
List <string> resultKeys)
{
MetaTable mt2;
QueryTable qt2;
string sql;
int methodId, i1;
int t0 = TimeOfDay.Milliseconds();
List <string> normalizedResultKeys = new List <string>();
for (i1 = 0; i1 < resultKeys.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase(resultKeys[i1], qt.MetaTable);
if (key == null)
{
key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value
}
normalizedResultKeys.Add(key);
}
sql = // todo: Make to work in general case (PubChem only now)
"select mthd_vrsn_id " +
"from " + "mbs_owner.mbs_pbchm_rslt" + " " +
"where ext_cmpnd_id_nbr in (<list>) " +
" and sts_id = 1 " + // active records only
"group by mthd_vrsn_id";
DbCommandMx drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.Int32);
drd.ExecuteListReader(normalizedResultKeys);
if (drd.Cancelled)
{
// todo qe.Cancelled = true;
drd.Dispose();
return;
}
Hashtable mtHash = new Hashtable();
int methodIdCount = 0;
while (true) // convert list of methods to set of metatable names
{
if (!drd.ListRead())
{
break;
}
methodId = drd.GetInt(0);
string mtName = "pubchem_aid_" + methodId.ToString(); // todo: Make to work in general case (PubChem only now)
if (QueryEngine.FilterAllDataQueriesByDatabaseContents &&
!MetaTableCollection.IsMetaTableInContents(mtName))
{
continue; // metatable must be in contents
}
if (qt.MetaTable.UseSummarizedData)
{
mtName += MetaTable.SummarySuffix;
}
mtHash[mtName] = null;
methodIdCount++;
}
drd.Dispose();
if (drd.Cancelled)
{
// todo qe.Cancelled = true;
return;
}
ArrayList mtList = new ArrayList();
foreach (string mtName2 in mtHash.Keys)
{ // put metatable labels & names into a list for sorting
mt2 = MetaTableCollection.Get(mtName2);
if (mt2 == null)
{
continue;
}
if (mt2.Parent == null)
{
continue; // skip if no parent
}
mtList.Add(mt2.Label.ToLower().PadRight(64) + "\t" + mt2.Name);
}
mtList.Sort();
foreach (string mts in mtList)
{ // add new querytables/metatables to query
string[] sa = mts.Split('\t');
mt2 = MetaTableCollection.Get(sa[1]);
if (mt2 == null)
{
continue;
}
qt2 = new QueryTable(q, mt2);
if (qt.HeaderBackgroundColor != Color.Empty)
{
qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor;
}
}
t0 = TimeOfDay.Milliseconds() - t0;
return;
}
/// <summary>
/// Read in data, pivot & buffer for supplied set of rows.
/// This is called for retrieval only, not for search
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
DbCommandMx drd;
int rowsFetched = 0, vosCreated = 0;
MetaTable mt;
MetaColumn mc = null;
DateTime dt;
PivotMetaBroker mb;
List <GenericMetaBroker> mbList;
string cid, pivotKey, tableFilter, s, txt, tok;
int fci, mci, pvi, pci, si, i1;
object[] vo = null;
object o;
if (!PivotInCode) // let Oracle do the pivoting?
{
base.ExecuteQuery(eqp);
return;
}
// Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source
int t0 = TimeOfDay.Milliseconds();
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
mt = eqp.QueryTable.MetaTable;
string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql
MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey];
if (mpd.FirstTableName != mt.Name)
{
return; // retrieve data for all tables when we see first table
}
mpd.ClearBuffers();
// Build sql
StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable
tableFilter = "";
if (mt.TableFilterColumns.Count == 1)
{ // build single in list if single filter column
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(mt.TableFilterValues[0]);
tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")";
}
}
else // multiple table filter columns, build and/or expressions
{
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(" or ");
}
tableFilter = "(" + GetTableFilterCriteria(mt) + ")";
sb.Append(tableFilter);
}
tableFilter = "(" + sb.ToString() + ")";
}
string sql = "select * from " + mt.TableMap + " where ";
if (tableFilter != "")
{
sql += tableFilter + " and ";
}
sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) ";
// Read unpivoted data, merge/pivot & buffer pivoted rows
List <string> keySubset = eqp.SearchKeySubset;
if (keySubset == null)
{
keySubset = GetPreviewSubset(); // assume previewing of single table if no subset
}
List <string> parmList = new List <string>();
for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable);
if (key != null)
{
parmList.Add(key);
}
}
drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.String);
drd.ExecuteListReader(parmList);
while (drd.Read())
{
rowsFetched++;
string tableFilterKey = ""; // get column values to identify table
for (fci = 0; fci < mt.TableFilterColumns.Count; fci++)
{
o = drd.GetObjectByName(mt.TableFilterColumns[fci]);
if (o == null)
{
s = "";
}
else
{
s = o.ToString();
}
if (tableFilterKey != "")
{
tableFilterKey += ", ";
}
tableFilterKey += s;
}
mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey];
if (mt == null)
{
continue; // continue if don't know about this table
}
if (!mpd.MbInstances.ContainsKey(mt.Name))
{
continue; // have row hash for broker?
}
int mbIdx = 0;
mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList);
while (true) // copy out for each metabroker
{
mt = mb.Qt.MetaTable;
if (mt == null)
{
continue;
}
if (mb.MultipivotRowDict == null)
{
mb.MultipivotRowDict = new Dictionary <string, object[]>();
}
string rowKey = "";
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString();
}
rowKey += "<" + s + ">";
}
if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row?
{
vo = (object[])mb.MultipivotRowDict[rowKey];
}
else // new row, create vo for it & fill in merged column values
{
vo = new Object[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values
{
mc = mb.SelectList[si];
if (mc.PivotValues != null)
{
continue; // skip pivoted cols for now
}
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci]))
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (mc.IsKey) // normalize cid adding prefix as needed
{
o = CompoundId.Normalize(o.ToString(), mt);
}
vo[si] = o;
break;
}
}
}
mb.MultipivotRowDict[rowKey] = vo;
vosCreated++;
}
// Pivot out data based on pivot column values
if (mb.PivotKeys == null)
{ // build set of pivot keys for the pivoted columns in the table if not done yet
mb.PivotKeys = new string[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++)
{
mc = mb.SelectList[si];
if (mc.PivotValues == null)
{
continue; // skip non-pivoted cols
}
pivotKey = "";
for (pvi = 0; pvi < mc.PivotValues.Count; pvi++)
{
pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">";
}
mb.PivotKeys[si] = pivotKey;
}
}
pivotKey = "";
for (pci = 0; pci < mt.PivotColumns.Count; pci++)
{ // build pivot key for this unpivoted row
o = drd.GetObjectByName(mt.PivotColumns[pci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString().ToLower();
}
pivotKey += "<" + s + ">";
}
for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values
{
if (mb.PivotKeys[si] == null || // skip non-pivoted cols
pivotKey != mb.PivotKeys[si])
{
continue; // and non-matches
}
mc = mb.SelectList[si];
int ci = drd.Rdr.GetOrdinal(mc.ColumnMap);
if (mc.DataType == MetaColumnType.Integer)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetInt(ci);
}
else // value with possible resultId, linking information
{
txt = drd.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Number)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDouble(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.QualifiedNo)
{
// todo
}
else if (mc.DataType == MetaColumnType.String)
{
if (!mc.DetailsAvailable)
{
vo[si] = drd.GetString(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Date)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDateTime(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Structure)
{ // structures come in as compound identifiers (todo: fix for annotation)
tok = Dr.GetValue(si).ToString();
cid = CompoundId.Normalize(tok, Qt.MetaTable);
vo[si] = cid;
}
else if (mc.DataType == MetaColumnType.MolFormula)
{
vo[si] = drd.GetString(ci);
}
else if (mc.DataType == MetaColumnType.DictionaryId)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Image)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Hyperlink)
{
txt = drd.GetString(ci);
Hyperlink hlink = new Hyperlink();
vo[si] = hlink;
hlink.Text = txt;
}
}
if (mbList == null)
{
break; // single broker
}
mbIdx++; // go to next broker
if (mbIdx >= mbList.Count)
{
break; // at end of brokers?
}
mb = (PivotMetaBroker)mbList[mbIdx];
} // end of broker loop
} // end of read loop
drd.Dispose();
return;
}
/// <summary>
/// Retrieve any existing SVG for the list of supplied molecules
/// The Id column should contain the CorpId
/// </summary>
/// <param name="molList"></param>
public static int SelectMoleculeListSvg(
List <MoleculeMx> molList)
{
MoleculeMx mol;
int corpId, molSvgsFetchedCount = 0;
string corpIdString, molString, svg;
const string sql = @"
SELECT
corp_nbr,
molstructure svgString
FROM
mbs_owner.corp_moltable_mx
WHERE
corp_nbr in (<list>)
and molstructure is not null
" ;
if (!Security.UserInfo.Privileges.CanRetrieveStructures) // structures allowed?
{
return(0);
}
//if (DebugMx.True) return 0; // debug, don't use existing values
List <string> lsnList = new List <string>();
Dictionary <string, MoleculeMx> molDict = new Dictionary <string, MoleculeMx>();
foreach (MoleculeMx mol0 in molList) // set up a dict keyed by cid with mol values
{
if (mol0.PrimaryFormat != MoleculeFormat.Helm || Lex.IsUndefined(mol0.PrimaryValue))
{
continue;
}
if (int.TryParse(mol0.Id, out corpId))
{
molDict[mol0.Id] = mol0;
}
}
if (molDict.Count == 0)
{
return(0);
}
DbCommandMx cmd = new DbCommandMx();
cmd.PrepareListReader(sql, DbType.String);
cmd.ExecuteListReader(new List <string>(molDict.Keys));
while (cmd.Read())
{
corpId = cmd.GetInt(0);
if (!cmd.IsNull(1)) // molstructure
{
molString = cmd.GetClob(1);
if (!SvgUtil.IsSvgString(molString))
{
continue; // skip if not SVG
}
svg = molString; // should be compressed format SVG
corpIdString = CompoundId.Normalize(corpId.ToString());
if (Lex.IsDefined(svg) && molDict.ContainsKey(corpIdString))
{
mol = molDict[corpIdString];
mol.SvgString = svg;
molSvgsFetchedCount++;
}
}
}
cmd.CloseReader();
return(molSvgsFetchedCount);
}
/// <summary>
/// Execute structure search for structures stored in an Oracle text column
/// </summary>
/// <param name="sql">Basic sql to select key & structure</param>
public void ExecuteInternalOracleStructureColumnSearch(
string sql,
MoleculeFormat structureFormat)
{
string cid, molString;
object[] vo;
MoleculeMx cs;
bool match = false;
QueryColumn molsimQc = Eqp.QueryTable.GetSelectedMolsimQueryColumn(); // get any column to return similarity score in
DbCommandMx drd = new DbCommandMx();
if (Eqp.SearchKeySubset == null)
{
drd.Prepare(sql);
drd.ExecuteReader();
}
else // limit to list
{
sql += " where " + Eqp.QueryTable.MetaTable.KeyMetaColumn.Name + " in (<list>)";
drd.PrepareListReader(sql, DbType.String);
List <string> dbKeySubset = new List <string>();
foreach (string key in Eqp.SearchKeySubset)
{
string dbKey = CompoundId.NormalizeForDatabase(key, Eqp.QueryTable.MetaTable);
dbKeySubset.Add(dbKey);
}
drd.ExecuteListReader(dbKeySubset);
}
drd.CheckForCancel = Eqp.CheckForCancel; // set cancel check flag
StructureMatcher matcher = new StructureMatcher(); // allocate structure matcher
List <object[]> results = new List <object[]>();
int matchCount = 0;
while (drd.Read())
{
cid = drd.GetObject(0).ToString();
cid = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix as needed
molString = drd.GetString(1);
if (String.IsNullOrEmpty(molString))
{
continue;
}
cs = new MoleculeMx(structureFormat, molString);
if (Pssc.SearchType == StructureSearchType.Substructure)
{
if (matcher.IsSSSMatch(Pssc.Molecule, cs))
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
results.Add(vo);
}
}
else if (Pssc.SearchType == StructureSearchType.MolSim)
{
double score = matcher.Molsim(Pssc.Molecule, cs, Pssc.SimilarityType);
if (score >= Pssc.MinimumSimilarity)
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
if (vo.Length >= 2)
{
vo[1] = (float)score;
}
results.Add(vo);
}
}
else if (Pssc.SearchType == StructureSearchType.FullStructure)
{
if (matcher.FullStructureMatch(Pssc.Molecule, cs, Pssc.Options))
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
results.Add(vo);
}
}
matchCount++;
// if (matchCount >100) break; // debug
if (drd.Cancelled)
{
Eqp.Qe.Cancelled = true;
drd.Dispose();
Results = null;
return;
}
}
drd.Dispose();
Results = results;
return;
}