/// <summary>
/// Get list of CIDS that are in the oracle database but not the Mobius fingerprint files
/// </summary>
/// <returns></returns>
static List <string> GetMissingCidList()
{
string sql = "", cid;
int readCnt = 0;
HashSet <string> knownCidsSet = FpDao.GetExistingCidSet(); // get list of cids in DB
ExistingUndefinedStructureCids = FpDao.ReadUndefinedStructuresCids(); // cids that have undefined structures and aren't in DB
knownCidsSet.UnionWith(ExistingUndefinedStructureCids);
List <string> cidList = new List <string>();
if (CorpDatabase)
{
sql = SelectAllCorpIds;
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr = 3431641"); // debug
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr between 1000000 and 1100000"); // debug
}
else // chembl
{
sql = SelectAllChemblIds;
}
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
if (CorpDatabase)
{
int corpId = rdr.GetInt(0); // corp_nbr
cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
}
else
{
cid = rdr.GetString(0); // chembl
}
if (!knownCidsSet.Contains(cid))
{
cidList.Add(cid);
}
}
rdr.CloseReader();
return(cidList);
}
StructSearchMatch ReadFingerprintRec_To_StructSearchMatch(
ReadFingerprintRecArgs a)
{
StructSearchMatch sm = new StructSearchMatch();
sm.SearchType = StructureSearchType.MolSim;
sm.SrcDbId = a.src;
sm.SrcCid = AsciiEncodingInstance.GetString(a.cidBytes, 0, a.cidLength);
if (sm.SrcDbId == 0)
{
sm.SrcCid = CompoundId.NormalizeForDatabase(sm.SrcCid); // be sure CorpIds are proper length
}
return(sm);
}
/// <summary>
/// Get a list of CorpIds that have been added or modified since the checkpoint date
/// </summary>
/// <returns></returns>
static List <string> GetNewAndModifiedCorpIdList(
out Dictionary <string, DateTime> cidUpdateDateDict)
{
int readCnt = 0;
List <string> cidList = new List <string>();
cidUpdateDateDict = new Dictionary <string, DateTime>();
CheckpointDateTime = FpDao.ReadFingerPrintSimMxDataUpdateCheckpointDate(); // , "20-mar-2016 000000");
if (Lex.IsUndefined(CheckpointDateTime))
{
throw new Exception("Mobius UpdateSimMxCorpIdDataCheckpointDate is not defined");
}
//CheckpointDateTime = "20-mar-2016 000000"; // debug
string sql = Lex.Replace(SelectCorpIdsByDateRange, "1-jan-1900 000000", CheckpointDateTime);
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
int corpId = rdr.GetInt(0); // corp_nbr
string cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
cidList.Add(cid);
DateTime dt = rdr.GetDateTime(1);
cidUpdateDateDict[cid] = dt;
}
rdr.CloseReader();
return(cidList);
}
/// <summary>
/// Create an annotation table from a DataTable
/// </summary>
/// <param name="fullyQualifiedName">Fully qualified name to assign to table</param>
/// <param name="dataTable">DataTable containing table definition & data</param>
/// <param name="showProgress">Display dialog box showing progress of creation</param>
/// <returns>Internal name assigned to annotation table (ANNOTATION_12345)</returns>
public static MetaTable CreateAnnotationTable(
string fullyQualifiedName,
DataTable dataTable,
bool showProgress)
{
List <AnnotationVo> voList = new List <AnnotationVo>();
AnnotationVo avo;
if (dataTable == null)
{
DebugMx.ArgException("DataTable is null");
}
if (dataTable.Columns.Count == 0)
{
DebugMx.ArgException("No DataColumns are defined");
}
if (dataTable.Columns[0].DataType != typeof(CompoundId))
{
DebugMx.ArgException("The first column must be of type CompoundId");
}
if (showProgress)
{
Progress.Show("Creating annotation table...");
}
AnnotationDao aDao = new AnnotationDao();
UserObject auo = UserObjectUtil.ParseInternalUserObjectName(fullyQualifiedName);
auo.Type = UserObjectType.Annotation;
UserObjectTree.GetValidUserObjectTypeFolder(auo);
UserObject auo2 = UserObjectDao.Read(auo); // see if there already
MetaTable oldMt = null;
if (auo2 == null) // get new identifier
{
auo.Id = UserObjectDao.GetNextId(); // id to store table under
}
else // reuse identifier
{
auo.Id = auo2.Id; // copy it over
aDao.DeleteTable(auo.Id); // delete any existing data
string oldMtName = "ANNOTATION_" + auo2.Id;
oldMt = MetaTableCollection.Get(oldMtName);
}
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Annotation;
mt.Name = "ANNOTATION_" + auo.Id; // name table by uo
mt.Label = auo.Name;
mt.Code = auo.Id.ToString(); // code for the metatable
int mtCode = auo.Id;
if (dataTable.ExtendedProperties.ContainsKey("ParentTableName"))
{
mt.Parent = MetaTableCollection.Get((string)dataTable.ExtendedProperties["ParentTableName"]);
}
foreach (DataColumn dc in dataTable.Columns)
{
MetaColumn mc = new MetaColumn();
mc.MetaTable = mt;
mc.Name = dc.ColumnName;
MetaColumn oldMc = null;
if (oldMt != null)
{
oldMc = oldMt.GetMetaColumnByName(mc.Name); // see if column name exists
}
if (oldMc != null && oldMc.ResultCode != "") // use any existing code
{
mc.ResultCode = oldMc.ResultCode;
}
else
{
mc.ResultCode = aDao.GetNextIdLong().ToString();
}
if (dc.Caption != null)
{
mc.Label = dc.Caption;
}
else
{
mc.Label = mc.Name;
}
if (dc.DataType == typeof(CompoundId))
{
mc.DataType = MetaColumnType.CompoundId;
if (dc.ExtendedProperties.ContainsKey("StorageType") && dc.ExtendedProperties["StorageType"] is MetaColumnType &&
((MetaColumnType)dc.ExtendedProperties["StorageType"]) == MetaColumnType.String)
{
mc.ColumnMap = "EXT_CMPND_ID_TXT"; // text column
}
else
{
mc.ColumnMap = "EXT_CMPND_ID_NBR"; // numeric column otherwise
}
}
else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32))
{
mc.DataType = MetaColumnType.Integer;
}
else if (dc.DataType == typeof(float) || dc.DataType == typeof(double))
{
mc.DataType = MetaColumnType.Number;
}
else if (dc.DataType == typeof(QualifiedNumber))
{
mc.DataType = MetaColumnType.QualifiedNo;
}
else if (dc.DataType == typeof(string))
{
mc.DataType = MetaColumnType.String;
}
else if (dc.DataType == typeof(DateTime))
{
mc.DataType = MetaColumnType.Date;
}
else if (dc.DataType == typeof(MoleculeMx))
{
mc.DataType = MetaColumnType.Structure;
}
else
{
throw new Exception("Invalid data type " + dc.DataType.ToString());
}
if (dc.ExtendedProperties.ContainsKey("DisplayLevel"))
{
mc.InitialSelection = (ColumnSelectionEnum)dc.ExtendedProperties["DisplayLevel"];
}
if (dc.ExtendedProperties.ContainsKey("DisplayWidth"))
{
mc.Width = (float)dc.ExtendedProperties["DisplayWidth"];
}
if (dc.ExtendedProperties.ContainsKey("DisplayFormat"))
{
mc.Format = (ColumnFormatEnum)dc.ExtendedProperties["DisplayFormat"];
}
if (dc.ExtendedProperties.ContainsKey("Decimals"))
{
mc.Decimals = (int)dc.ExtendedProperties["Decimals"];
}
mt.MetaColumns.Add(mc);
}
ToolHelper.CreateAnnotationTable(mt, auo);
aDao.BeginTransaction(); // insert all data in single transaction
if (showProgress)
{
Progress.Show("Writing data to annotation table...");
}
int t1 = TimeOfDay.Milliseconds();
int writeCount = 0;
foreach (DataRow dr in dataTable.Rows)
{
if (dr.IsNull(0))
{
continue; // shouldn't happen
}
string key = dr[0].ToString();
key = CompoundId.NormalizeForDatabase(key, mt.Root);
long rslt_grp_id = aDao.GetNextIdLong();
for (int ci = 1; ci < dataTable.Columns.Count; ci++) // do columns after key
{
if (dr.IsNull(ci))
{
continue;
}
DataColumn dc = dataTable.Columns[ci];
MetaColumn mc = mt.MetaColumns[ci];
int mcCode = Int32.Parse(mc.ResultCode);
avo = new AnnotationVo();
avo.rslt_grp_id = rslt_grp_id; // keep row together
if (dc.DataType == typeof(CompoundId)) // shouldn't happen since key processed already
{
avo.rslt_val_txt = dr[ci].ToString();
}
else if (dc.DataType == typeof(int) || dc.DataType == typeof(Int16) || dc.DataType == typeof(Int32) ||
dc.DataType == typeof(float) || dc.DataType == typeof(double))
{
avo.rslt_val_nbr = (double)dr[ci];
}
else if (dc.DataType == typeof(QualifiedNumber))
{
QualifiedNumber qn = (QualifiedNumber)dr[ci];
avo.rslt_val_nbr = qn.NumberValue;
avo.rslt_val_prfx_txt = qn.Qualifier;
avo.rslt_val_txt = qn.TextValue;
avo.dc_lnk = qn.Hyperlink;
}
else if (dc.DataType == typeof(string))
{
avo.rslt_val_txt = dr[ci].ToString();
}
else if (dc.DataType == typeof(DateTime))
{
avo.rslt_val_dt = (DateTime)dr[ci];
}
else if (dc.DataType == typeof(MoleculeMx))
{
avo.rslt_val_txt = dr[ci].ToString();
}
AddAnnotationVoToList(avo, key, mtCode, mcCode, voList);
}
writeCount++;
if (Progress.CancelRequested) // check for cancel
{
aDao.Commit();
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
aDao.Dispose();
if (showProgress)
{
Progress.Hide();
}
MessageBoxMx.ShowError("Writing of annotation table cancelled.");
return(null);
}
int t2 = TimeOfDay.Milliseconds();
if (showProgress && t2 - t1 >= 1000)
{
t1 = t2;
Progress.Show("Writing data to annotation table " + writeCount.ToString() +
" of " + dataTable.Rows.Count.ToString() + " ...");
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
}
}
aDao.Insert(voList);
voList.Clear();
aDao.Commit();
aDao.Dispose();
if (showProgress)
{
Progress.Hide();
}
return(mt); // return metatable name
}
/// <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);
}
}
static ICdkMol CdkMolUtil => StaticCdkMol.I; // static molecule shortcut for utility methods
/// <summary>
/// UpdateCorpFingerprintDatabaseMx
/// ///////////////////////////////////////////////////////
/// Syntax: Update FingerprintDatabaseMx [Corp | ChEMBL] [MACCS | ECFP4] [Load | ByCidRange | SinceLastCheckpoint | LoadMissing | <SingleCorpId>]
///
/// Corp Examples:
/// Update FingerprintDatabaseMx Corp MACCS Load
/// Update FingerprintDatabaseMx Corp MACCS LoadMissing
/// Update FingerprintDatabaseMx Corp MACCS SinceLastCheckpoint
///
/// Update FingerprintDatabaseMx Corp ECFP4 Load
/// Update FingerprintDatabaseMx Corp ECFP4 LoadMissing
/// Update FingerprintDatabaseMx Corp ECFP4 SinceLastCheckpoint
///
/// ChEMBL Examples:
/// Update FingerprintDatabaseMx Chembl MACCS Load
/// Update FingerprintDatabaseMx Chembl MACCS LoadMissing
///
/// Update FingerprintDatabaseMx Chembl ECFP4 Load
/// Update FingerprintDatabaseMx Chembl ECFP4 LoadMissing
/// ///////////////////////////////////////////////////////
/// </summary>
/// <param name="argString"></param>
/// <returns></returns>
static public string Update(
string argString)
{
MoleculeMx mol;
double mw;
string chime, smiles, molString, molFile = "";
string msg = "", sql = "", chemblId, cid = "", maxCorpIdSql, maxIdSql2, mf, missingFixCriteria = "", CorpIdList = "";
int storeChunkCount = 0, CorpId, molregno, molId, lowId = 0, highId = 0, maxDestId = 0, maxSrcId = 0;
int readCount = 0, storeCount = 0;
ByCheckpoint = ByCidRange = ByCidList = LoadIfMissing = false;
ReadChunkSize = DefaultReadChunkSize;
WriteChunkSize = DefaultWriteChunkSize;
Failures = new Dictionary <string, string>();
NewUndefinedStructureCids = new List <string>();
LastFailure = "";
FailureCount = 0;
// global try loop
try
{
////////////////////////
/// Parse Parameters ///
////////////////////////
// See which database
argString = argString.Trim();
if (Lex.StartsWith(argString, "Corp"))
{
Database = "Corp";
argString = argString.Substring(5).Trim();
}
else if (Lex.StartsWith(argString, "Chembl"))
{
Database = "ChEMBL";
argString = argString.Substring(6).Trim();
}
else
{
return(SyntaxMsg);
}
// See which fingerprint type
FingerprintType = FingerprintType.MACCS; // default to MACCS if type not defined
if (Lex.TryReplace(ref argString, "MACCS", ""))
{
FingerprintType = FingerprintType.MACCS;
argString = argString.Trim();
}
else if (Lex.TryReplace(ref argString, "ECFP4", ""))
{
FingerprintType = FingerprintType.Circular; // (i.e. ECFP4)
argString = argString.Trim();
}
FpDao = new FingerprintDao(Database, FingerprintType);
List <FingerprintRec> fpRecList = new List <FingerprintRec>();
string args = argString.Trim();
string initialMsg = "Update FingerprintDatabase started: " + args;
CidList = new List <string>(); // init empty list
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
if (Lex.Eq(args, "Load"))
{
ByCidRange = true;
ShowProgress("Getting range of CorpIds to insert...");
maxCorpIdSql = SelectMaxCorpId; // get highest id in source db
maxSrcId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (maxSrcId < 0)
{
maxSrcId = 0;
}
maxDestId = GetMaxDestMolId();
//maxIdSql2 = "select max(src_compound_id_nbr) from dev_mbs_owner.corp_uc_xref where src_id = 0"; // get highest id in UniChemDb db
//highCorpId = SelectSingleValueDao.SelectInt(maxIdSql2);
if (maxDestId < 0)
{
maxDestId = 0;
}
}
else if (Lex.Eq(args, "SinceLastCheckpoint"))
{
ByCheckpoint = true;
ShowProgress("Getting list of CorpIds updated since last checkpoint...");
CidList = GetNewAndModifiedCorpIdList(out CidUpdateDateDict);
//CidUpdateList = new List<string>(); // debug with single cmpd
//CidUpdateList.Add("03435269");
if (CidList.Count == 0)
{
return("There have been no updates since the last checkpoint");
}
initialMsg += ", CorpIds to add/update: " + CidList.Count;
}
else if (Lex.StartsWith(args, "ByCorpIdList"))
{
ByCidList = true;
CorpIdList = args.Substring("ByCorpIdList".Length).Trim();
if (Lex.IsUndefined(CorpIdList))
{
throw new Exception("Undefined CorpId list");
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
LoadIfMissing = true;
if (args.Contains(" "))
{
missingFixCriteria = args.Substring("LoadMissing".Length).Trim();
}
ShowProgress("Getting list of missing CorpIds...");
CidList = GetMissingCidList();
if (CidList.Count == 0)
{
return("There are no missing CorpIds");
}
initialMsg += ", Missing CorpIds: " + CidList.Count;
}
else if (int.TryParse(args, out maxSrcId)) // single CorpId
{
ByCidRange = true;
maxDestId = maxSrcId - 1; // say 1 less is the max we have
}
else
{
return(SyntaxMsg);
}
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else if (ChemblDatabase)
{
if (Lex.Eq(args, "Load"))
{
ByCidRange = true;
ShowProgress("Getting range of MolRegNos to insert...");
sql = "select max(molregno) from chembl_owner.compound_struct_xxxxxx";
maxSrcId = SelectSingleValueDao.SelectInt(sql);
if (maxSrcId < 0)
{
maxSrcId = 0;
}
maxDestId = GetMaxDestMolId();
if (maxDestId < 0)
{
maxDestId = 0;
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
LoadIfMissing = true;
ShowProgress("Getting list of missing ChEMBL Ids...");
CidList = GetMissingCidList();
if (CidList.Count == 0)
{
return("There are no missing Ids");
}
initialMsg += ", Missing Chembl Ids: " + CidList.Count;
}
else
{
return(SyntaxMsg);
}
}
else
{
return(SyntaxMsg);
}
CidListOriginalCount = CidList.Count;
Log(initialMsg);
/////////////////////////////
// Loop over chunks of data
/////////////////////////////
for (int chunk = 1; ; chunk++)
{
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
if (ByCheckpoint) // single chunk
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break;
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
string matchString = "order by m.corp_nbr";
if (!Lex.Contains(sql, matchString))
{
throw new Exception(matchString + " not found");
}
sql = Lex.Replace(sql, matchString, "order by m.molecule_date");
msg = "Processing " + CidListOriginalCount + " updates since " + CheckpointDateTime;
// + " (" + Mobius.Data.CidList.FormatCidListForDisplay(null, chunkCidList) + ")";
}
else if (ByCidRange) // by CorpId range
{
if (maxDestId >= maxSrcId)
{
break; // done
}
lowId = maxDestId + 1; // start of next chunk
highId = lowId + ReadChunkSize;
maxDestId = highId;
//lowCorpId = highCorpId = 12345; // debug
if (highId >= maxSrcId)
{
highId = maxSrcId;
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "between " + lowId + " and " + highId);
msg = "Processing CorpId range: " + lowId + " to " + highId;
}
else if (ByCidList) // by single user-supplied CorpId list
{
if (chunk > 1)
{
break; // break 2nd time through
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + CorpIdList + ")");
msg = "Processing CorpId list: " + CorpIdList;
}
else if (LoadIfMissing)
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break; // all done
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
msg = "Processing missing CorpId Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) +
", Total Ids: " + CidListOriginalCount;
Log(msg);
}
else
{
return(SyntaxMsg);
}
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else if (ChemblDatabase)
{
if (ByCidRange) // by CID range
{
if (maxDestId >= maxSrcId)
{
break; // done
}
lowId = maxDestId + 1; // start of next chunk
highId = lowId + ReadChunkSize;
maxDestId = highId;
//lowId = highId = 12345; // debug
if (maxDestId >= maxSrcId)
{
maxDestId = maxSrcId;
}
sql = SelectChemblSql;
sql = Lex.Replace(sql, "<molregnoCriteria>", "between " + lowId + " and " + highId);
msg = "Processing ChEMBL MolRegNo range: " + lowId + " to " + highId;
}
else if (LoadIfMissing)
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break; // all done
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
msg = "Processing missing ChEMBL Id Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) +
", Total Ids: " + CidListOriginalCount;
}
else
{
return(SyntaxMsg);
}
}
else
{
return(SyntaxMsg);
}
ShowProgress(msg);
// Execute the SQL to get the rows for the chunk
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
DateTime lastShowProgressTime = DateTime.MinValue;
///////////////////////////////////////////
/// Loop over rows in the current chunk ///
///////////////////////////////////////////
while (true)
{
// Update progress display
if (DateTime.Now.Subtract(lastShowProgressTime).TotalSeconds > 1) // show progress
{
int storeTotalCount = storeCount + storeChunkCount;
string msg2 = msg + "\r\n" +
"Reads: " + readCount + "\r\n" +
"Undefined: " + NewUndefinedStructureCids.Count + "\r\n" +
"Insert/Updates: " + storeTotalCount + "\r\n" +
"Failures: " + FailureCount + "\r\n" +
"Failure Types: " + Failures.Count + "\r\n" +
"Last Failure: " + LastFailure;
ShowProgress(msg2);
lastShowProgressTime = DateTime.Now;
}
// Read and process next compound
bool readOk = rdr.Read();
if (readOk)
{
readCount++;
try
{
double t1 = 0, t2 = 0, t3 = 0, t4 = 0;
DateTime t0 = DateTime.Now;
mol = null;
//t2 = TimeOfDay.Delta(ref t0);
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
CorpId = rdr.GetInt(0); // corp_nbr
//Log("CorpId: " + CorpId); // debug
molId = CorpId;
cid = CorpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
if (!rdr.IsNull(1)) // be sure chime field isn't null
{
chime = rdr.GetClob(1);
if (Lex.IsDefined(chime))
{
molFile = MoleculeMx.ChimeStringToMolfileString(chime); // convert Chime to MolFile
mol = new MoleculeMx(MoleculeFormat.Molfile, molFile);
}
}
MoleculeDateTime = rdr.GetDateTimeByName("Molecule_Date"); // Date molecule was updated in the CorpDB cartridge DB
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else // chembl
{
molId = molregno = rdr.GetInt(0);
cid = chemblId = rdr.GetString(1);
smiles = rdr.GetString(2);
if (Lex.IsDefined(smiles))
{
mol = new MoleculeMx(MoleculeFormat.Smiles, smiles);
}
}
if (MoleculeMx.IsUndefined(mol) || mol.AtomCount <= 1)
{
NewUndefinedStructureCids.Add(cid);
continue;
//mol = new AtomContainer(); // write empty structure
}
bool includeOverallFingerprint = true;
List <BitSetFingerprint> fps = CdkMol.BuildBitSetFingerprints(mol.MolfileString, includeOverallFingerprint, FingerprintType);
//t3 = TimeOfDay.Delta(ref t0);
foreach (BitSetFingerprint fp in fps)
{
FingerprintRec fpr = new FingerprintRec();
fpr.molId = molId;
fpr.SrcId = SrcDbId;
fpr.Cid = cid;
fpr.Cardinality = fp.cardinality();
fpr.Fingerprint = fp.asBitSet().toLongArray();
fpRecList.Add(fpr);
}
//t4 = TimeOfDay.Delta(ref t0);
t4 = t4;
}
catch (Exception ex)
{
if (!Failures.ContainsKey(ex.Message))
{
Failures.Add(ex.Message, cid);
}
else
{
Failures[ex.Message] += ", " + cid;
}
LastFailure = "Cid: " + cid + " - " + ex.Message;
Log(LastFailure);
//ShowProgress(ex.Message + "\r\n" + ex.StackTrace.ToString()); // debug
FailureCount++;
continue;
}
storeChunkCount++;
}
bool commitTransaction = (storeChunkCount >= WriteChunkSize || (!readOk && storeChunkCount > 0));
if (commitTransaction) // end of chunk of data to store?
{
// if updating by CheckPoint date range then merge existing data with new/updated data
if (ByCheckpoint)
{
if (readCount > 0 && (storeCount > 0 || FailureCount == 0)) // make sure not everything has failed)
{
MergeRecordsIntoFiles(fpRecList);
}
}
// Simple append of records to files
else
{
FpDao.OpenWriters("bin", FileMode.Append); // open bin files for append
foreach (FingerprintRec fpr in fpRecList) // write out buffered recs
{
FpDao.WriteFingerprintRec(fpr);
}
FpDao.CloseWriters();
int cnt = fpRecList.Count;
if (cnt > 0)
{
string cid1 = fpRecList[0].Cid;
string cid2 = fpRecList[cnt - 1].Cid;
Log("Records Appended: " + cnt + ", CIDS: " + cid1 + " - " + cid2);
}
else
{
Log("Records Appended: 0");
}
}
fpRecList.Clear();
storeCount += storeChunkCount;
storeChunkCount = 0;
}
if (!readOk)
{
break;
}
} // end of read loop for rows in a chunk
rdr.Dispose();
} // end for loop of chunks
DeleteTempFiles();
if (LoadIfMissing) // update list of cids with missing structures
{
ExistingUndefinedStructureCids.UnionWith(NewUndefinedStructureCids);
FpDao.WriteUndefinedStructuresCids(ExistingUndefinedStructureCids);
}
msg = "*** Update Complete ***\r\n\r\n" + msg;
ShowProgress(msg);
System.Threading.Thread.Sleep(100);
string logMsg = "UpdateFingerprintDb - CIDs stored: " + storeCount + ", Undefined structures: " + NewUndefinedStructureCids.Count + ", failures: " + FailureCount + "\r\n";
foreach (string key in Failures.Keys)
{
logMsg += key + " - CIDs: " + Failures[key] + "\r\n";
}
Log(logMsg);
return(logMsg);
} // end of main try loop
catch (Exception ex)
{
Log(DebugLog.FormatExceptionMessage(ex));
throw new Exception(ex.Message, ex);
}
}
/// <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;
}
/// Build an unparameterized key list SQL predicate
/// </summary>
/// <param name="Qt"></param>
/// <param name="keyName">Key column name qualified by table name/alias</param>
/// <summary>
/// Build an unparameterized key list SQL predicate
/// </summary>
/// <param name="eqp"></param>
/// <param name="baseSql"></param>
/// <param name="keyName">Key column name qualified by table name/alias</param>
/// <param name="keyList"></param>
/// <param name="firstKeyIdx"></param>
/// <param name="keyCount"></param>
/// <param name="keyCriteria">Full criteria including col name operator and list</param>
/// <param name="keyListString">Just the list of keys</param>
public void BuildUnparameterizedKeyListPredicate(
ExecuteQueryParms eqp,
ref string baseSql,
string keyName,
List <string> keyList,
int firstKeyIdx,
int keyCount,
out string keyCriteria,
out string keyListString)
{
StringBuilder sb;
int i1;
QueryTable qt = eqp.QueryTable;
MetaTable mt = qt.MetaTable;
bool integerKey = mt.IsIntegerKey();
List <StringBuilder> sublists = new List <StringBuilder>();
sb = new StringBuilder();
sublists.Add(sb);
int sublistKeyCount = 0; // current keys in predicate
keyCriteria = keyListString = null;
for (i1 = 0; i1 < keyCount; i1++)
{
if (sublistKeyCount >= DbCommandMx.MaxOracleInListItemCount)
{
sb = new StringBuilder();
sublists.Add(sb);
sublistKeyCount = 0;
}
if (sb.Length > 0)
{
sb.Append(",");
}
string key = CompoundId.NormalizeForDatabase((string)keyList[firstKeyIdx + i1], qt.MetaTable);
if (key == null)
{
key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value
}
if (!integerKey || !Lex.IsInteger(key)) // quote it if not integer column or value
{
key = Lex.AddSingleQuotes(key); // (note: quoted integers can cause mismatches for some database systems, e.g. Denodo)
}
sb.Append(key);
sublistKeyCount++;
}
sb = new StringBuilder();
if (sublists.Count >= 2)
{
sb.Append("("); // wrap in parens if multiple sublists
}
for (int sli = 0; sli < sublists.Count; sli++)
{
if (sli > 0)
{
sb.Append(" or ");
}
sb.Append(keyName + " in (" + sublists[sli] + ")");
}
if (sublists.Count >= 2)
{
sb.Append(")");
}
keyCriteria = sb.ToString();
keyListString = sublists[0].ToString(); // return just the first sublist (adjust later for larger lists)
return;
}
/// <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;
}
/// <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);
}