/// <summary>
/// Read a sample of a table for previewing
/// </summary>
/// <returns></returns>
List <string> GetPreviewSubset()
{
Dictionary <string, object> PreviewSubset = new Dictionary <string, object>();
MetaTable mt = Eqp.QueryTable.MetaTable;
string sql = "select /*+ first_rows */" + mt.KeyMetaColumn.ColumnMap + " from " +
GetSourceWithTableFilterCriteria(mt);
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
if (drd.IsNull(0))
{
continue;
}
string id = drd.GetObject(0).ToString();
string cid = CompoundId.Normalize(id); // normalize cid adding prefix as needed
PreviewSubset[id] = null;
if (PreviewSubset.Count >= 100)
{
break;
}
}
drd.Dispose();
return(new List <string>(PreviewSubset.Keys));
}
/// <summary>
/// Update the list of related compounds
/// </summary>
/// <param name="mt"></param>
/// <param name="cid"></param>
/// <param name="str"></param>
private void UpdateRelatedCidsDisplayOld(
MetaTable mt,
string cid,
MoleculeMx str)
{
if (SS.I.AllowGroupingBySalts) // build list of salts & send
{
List <string> salts = MoleculeUtil.GetAllSaltForms(Cid);
if (salts == null || salts.Count == 0) // nothing related
{
OtherCidsList.Visible = false; // hide list
}
else
{
OtherCidsList.Properties.Items.Clear();
foreach (string s in salts)
{
string extCid = CompoundId.Format(s);
OtherCidsList.Properties.Items.Add(extCid);
}
string listHeader = salts.Count.ToString() + " Other Match";
if (salts.Count > 1)
{
listHeader += "es"; // use proper grammar
}
OtherCidsList.Text = listHeader; // select first item
OtherCidsList.Visible = true;
}
}
else
{
OtherCidsList.Visible = false; // hide list
}
}
/// <summary>
/// Format structure for output to SDFile
/// </summary>
/// <param name="molid"></param>
void FormatSdfileStructure(
MoleculeMx cs)
{
string molFile, cid;
if (SS.I.RemoveLeadingZerosFromCids) // normal formatting
{
cid = CompoundId.Format(CurrentKey);
}
else
{
cid = CurrentKey; // just use internal formatting
}
cs = cs.Convert(Rf.StructureFlags | MoleculeTransformationFlags.RemoveStructureCaption, cid);
molFile = cs.GetMolfileString();
if (molFile.IndexOf("\r") < 0) // add carriage returns to molfile if it doesn't contain them
{
molFile = molFile.Replace("\n", "\r\n");
}
SdfLine = molFile;
return;
}
private void InsertSaltsForCurrentCompound_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
string cid = GetCurrentCid(false);
if (cid.Trim().Length == 0)
{
return;
}
List <string> al = MoleculeUtil.GetAllSaltForms(cid);
if (al == null || al.Count == 0)
{
DisplayStatusMsg("No salts for " + CompoundId.Format(cid, RootTable));
SystemUtil.Beep();
return;
}
StringBuilder listBuild = new StringBuilder();
foreach (string s in al)
{ // build formatted list with added items marked
if (s == cid)
{
continue; // don't add self
}
listBuild.Append(CompoundId.Format(s, RootTable));
listBuild.Append(" (+)");
listBuild.Append("\r\n");
}
InsertCidListText(listBuild.ToString());
return;
}
private void InsertSaltsForAllCompounds_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
DisplayStatusMsg("Inserting Salts, wait please...");
GetNormalizedListFromControl(CidList);
List <string> al = MoleculeUtil.InsertSalts(CidList.ToStringList());
CidList newList = new CidList(al);
newList.UserObject = CidList.UserObject;
StringBuilder listBuild = new StringBuilder(newList.Count * 12 + 32); // build formatted list here
foreach (CidListElement cle in newList.List)
{ // build formatted list with added items marked
listBuild.Append(CompoundId.Format(cle.Cid, RootTable));
if (!CidList.Contains(cle.Cid))
{
listBuild.Append(" (+)");
}
listBuild.Append("\r\n");
}
CidList = newList;
CidListCtl.Text = listBuild.ToString();
DisplayStatusMsg("");
return;
}
/// <summary>
/// Click function to open an external web site when clicking on a CID in a SmallWorld database display
/// </summary>
/// <param name="args"></param>
public static void OpenUrlFromSmallWorldCid(
string cid)
{
string[] px = { "PBCHM", "PDB" };
RootTable rt = CompoundId.GetRootTableFromCid(cid);
if (rt == null || Lex.IsUndefined(rt.CidUrl))
{
if (rt != null && Lex.Contains(rt.MetaTableName, MetaTable.PrimaryRootTable))
{
RelatedCompoundsDialog.Show(cid);
return;
}
MessageBoxMx.ShowError("Unable to determine url link for compound Id: " + cid);
return;
}
string url = rt.CidUrl;
foreach (string p in px) // remove unwanted URLs
{
if (Lex.StartsWith(cid, p))
{
cid = cid.Substring(p.Length);
}
}
url = Lex.Replace(url, "[CID]", cid);
SystemUtil.StartProcess(url);
return;
}
/// <summary>
/// Display the structure and other basic data for the supplied cid
/// </summary>
/// <param name="cidArg"></param>
void DisplayCidData(string cidArg)
{
MoleculeMx str = null;
string mwTxt = "", mfTxt = "", haTxt = "", tok;
string intCid = CompoundId.Normalize(cidArg, RootTable);
string extCid = CompoundId.Format(intCid, RootTable);
CidCtl.Text = extCid;
if (RootTable == null || RootTable.KeyMetaColumn.DataType == MetaColumnType.CompoundId)
{
str = MoleculeUtil.SelectMoleculeForCid(intCid, RootTable);
}
if (Lex.IsDefined(str?.PrimaryValue)) // have a structure with at least one atom
{
if (str.PrimaryDisplayFormat == MoleculeRendererType.Chemistry)
{
int ha = str.HeavyAtomCount;
if (ha > 0)
{
haTxt = ha.ToString();
}
double mw = str.MolWeight;
if (mw >= 0)
{
mwTxt = String.Format("{0:f3}", mw);
}
HeavyAtoms.Text = "";
Weight.Text = mwTxt;
string mf = str.MolFormula;
HeavyAtoms.Text = "";
Formula.Text = mf;
}
else
{
} // biopolymer, don't calc structure props (too slow for now)
HeavyAtoms.Text = haTxt;
Weight.Text = mwTxt;
Formula.Text = mfTxt;
MolCtl.Molecule = str;
return;
}
else // no structure
{
HeavyAtoms.Text = "";
Weight.Text = "";
Formula.Text = "";
MolCtl.ClearMolecule();
return;
}
}
/// <summary>
/// Format list for display
/// </summary>
/// <param name="rootTable"></param>
/// <returns></returns>
string FormatList()
{
string formattedCid;
StringBuilder listBuild = new StringBuilder(CidList.Count * 12 + 32); // allocate expected length
for (int li = 0; li < CidList.Count; li++)
{
if (listBuild.Length > 0)
{
listBuild.Append("\r\n");
}
string cid = CidList[li].Cid;
if (SS.I.RemoveLeadingZerosFromCids) // normal formatting
{
formattedCid = CompoundId.Format(cid, RootTable);
}
else
{
formattedCid = cid; // just use internal formatting
}
listBuild.Append(formattedCid);
}
return(listBuild.ToString());
}
private void Timer1_Tick(object sender, EventArgs e)
{
if (ServiceFacade.ServiceFacade.InServiceCall)
{
return; // avoid multiplexing service calls that could cause problems
}
if (InTimer_Tick)
{
return;
}
InTimer_Tick = true;
string currentCid = CidCtl.Text;
if (currentCid == PreviousCid)
{
InTimer_Tick = false;
return;
}
PreviousCid = currentCid;
string cid = CompoundId.Normalize(currentCid, MetaTable);
MetaTable mt = CompoundId.GetRootMetaTableFromCid(cid, MetaTable);
MoleculeMx mol = MoleculeUtil.SelectMoleculeForCid(cid, mt);
QuickStructure.SetupAndRenderMolecule(mol);
InTimer_Tick = false;
return;
}
internal Statistics RecomputeStatistics()
{
var totalWordsIndexed = 0;
var totalUniqueWordsIndexed = 0;
var longestWordIndexed = string.Empty;
var shortestWordIndexed = string.Empty;
var mostFrequentWordIndexed = string.Empty;
var leastFrequentWordIndexed = string.Empty;
var mostFrequentWordCount = 0;
var leastFrequentWordCount = int.MaxValue;
var currentWordId = -1;
var currentWord = string.Empty;
var currentWordFrequency = 0;
foreach (var compoundKey in wordCompoundIdToOccurrenceMap.Keys)
{
var wordId = CompoundId.Split(compoundKey).Item1;
if (currentWordId != wordId)
{
if (currentWordId != -1)
{
if (currentWordFrequency >= mostFrequentWordCount)
{
mostFrequentWordCount = currentWordFrequency;
mostFrequentWordIndexed = currentWord;
}
if (currentWordFrequency <= leastFrequentWordCount)
{
leastFrequentWordCount = currentWordFrequency;
leastFrequentWordIndexed = currentWord;
}
}
currentWordId = wordId;
currentWordFrequency = 0;
currentWord = idToWordMap[wordId];
totalUniqueWordsIndexed++;
if (currentWord.Length >= longestWordIndexed.Length)
{
longestWordIndexed = currentWord;
}
if (currentWord.Length <= shortestWordIndexed.Length || string.IsNullOrEmpty(shortestWordIndexed))
{
shortestWordIndexed = currentWord;
}
}
totalWordsIndexed++;
currentWordFrequency++;
}
return(new Statistics(totalWordsIndexed, totalUniqueWordsIndexed, longestWordIndexed, shortestWordIndexed, mostFrequentWordIndexed, leastFrequentWordIndexed));
}
/// <summary>
/// Select a list of chime strings ChemicalStructure object for a compound id
/// </summary>
/// <param name="cid"></param>
/// <param name="mt">MetaTable used to get root table to select structures from</param>
/// <returns></returns>
public static Dictionary <string, MoleculeMx> SelectMoleculesForCidList(
List <string> cidList,
MetaTable mt = null)
{
MetaColumn strMc = null;
MoleculeMx cs;
KeyValuePair <string, string> kvp;
string mtName = null, keyColName, strColExpr, chimeString, cid;
int li;
if (cidList == null || cidList.Count == 0)
{
return(null);
}
cid = cidList[0];
mt = mt?.Root;
bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database
if (!isUcdb)
{
cid = CompoundId.Normalize(cid, mt);
mt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (mt == null)
{
return(null);
}
if (MqlUtil.IsCartridgeMetaTable(mt))
{
return(SelectChemicalStructuresForCorpIdList(cidList, mt));
}
}
// Do one at a time
Dictionary <string, MoleculeMx> csDict = new Dictionary <string, MoleculeMx>();
foreach (string cid0 in cidList)
{
cs = SelectMoleculeForCid(cid0, mt);
if (cs != null)
{
csDict[cid0] = cs;
}
}
return(csDict);
}
/// <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);
}
/// <summary>
/// ShowImmediate
/// </summary>
/// <param name="singleCorpId"></param>
void ShowPopup(string singleCorpId)
{
KeyQc = QueryTable.GetDefaultRootQueryTable()?.KeyQueryColumn;
KeyQc.Criteria = KeyQc.MetaColumnName + " = " + singleCorpId;
//Tso = new TargetSummaryOptions();
//Tso.CidCriteria = "= " + singleCorpId;
string title = "Spotfire View of Data for Compound Id " + CompoundId.Format(singleCorpId);
BuildQuery(title);
QbUtil.RunPopupQuery(Query, title, OutputDest.WinForms);
return;
}
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);
}
// # Get list of bits where at least one must be in result fp. Use least popular bits if possible.
// if db.mfp_counts:
// reqbits = [count['_id'] for count in db.mfp_counts.find({'_id': {'$in': qfp
// }
//}).sort('count', 1).limit(ncommon)]
// else:
// reqbits = qfp[:ncommon]
// results = []
// for fp in db.molecules.find({'mfp.bits': {'$in': reqbits}, 'mfp.count': {'$gte': qmin, '$lte': qmax}}):
// intersection = len(set(qfp) & set(fp['mfp']['bits']))
// pn = fp['mfp']['count']
// tanimoto = float(intersection) / (pn + qn - intersection)
// if tanimoto >= threshold:
// results.append((tanimoto, fp['chembl_id'], fp['smiles']))
// return results
/// <summary>
/// Test to calculate similarity between pairs of structures (Alt syntax: Call SimSearchMx.Test [cid1] [cid2]
/// Tautomers:
/// Isotopes:
/// Neg Counterion, (Cl-) with 2 quatternary N+ with H attached in main frag:
/// Neg Counterion, (I-) with quatternary N+ (no attached H) in main frag:
/// Pos Counterion (Li+) with O- in main frag:
/// Benzene, cyclohexane:
/// StereoIsomers:
/// StereoIsomers:
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public static string Test(string args)
{
string[] sa = args.Split(' ');
string cid1 = CompoundId.Normalize(sa[0]);
string cid2 = CompoundId.Normalize(sa[1]);
int smiLen = 40;
MoleculeMx s1 = MoleculeMx.SelectMoleculeForCid(cid1);
IAtomContainer mol = CdkMol.MolfileToAtomContainer(s1.GetMolfileString());
UniChemData ucd1 = UniChemUtil.BuildUniChemData(mol);
List <FingerprintMx> fps1 = UniChemDataToFingerprintMxList(ucd1);
MoleculeMx s2 = MoleculeMx.SelectMoleculeForCid(cid2);
IAtomContainer mol2 = CdkMol.MolfileToAtomContainer(s2.GetMolfileString());
UniChemData ucd2 = UniChemUtil.BuildUniChemData(mol2);
List <FingerprintMx> fps2 = UniChemDataToFingerprintMxList(ucd2);
string fps2Smiles = (sa[0] + " / " + sa[1]).PadRight(smiLen);
string scores = "";
for (int i1 = 0; i1 < fps1.Count; i1++)
{
FingerprintMx fp1 = fps1[i1];
for (int i2 = 0; i2 < fps2.Count; i2++)
{
FingerprintMx fp2 = fps2[i2];
if (i1 == 0) // build smiles headers of cid2 frags if first cid1 frag
{
fps2Smiles += "\t" + fp2.CanonSmiles.PadRight(smiLen);
}
if (i2 == 0)
{
scores += "\r\n" + fp1.CanonSmiles.PadRight(smiLen); // include smiles at start of each line
}
float simScore = CalculateFingerprintPairSimilarityScore(fp1, fp2);
scores += "\t" + string.Format("{0:0.00}", simScore).PadRight(smiLen);
}
}
scores = fps2Smiles + scores;
FileUtil.WriteAndOpenTextDocument("SimilarityScores", scores);
return("");
}
/// <summary>
/// Read the list of compounds for a library
/// </summary>
/// <param name="libId"></param>
/// <returns></returns>
public static CidList ReadLibrary(
int libId)
{
CidList l = new CidList();
DbCommandMx dao = new DbCommandMx();
string sql = @"
SELECT l.library_name, l.library_desc_text, s.corp_nbr
FROM corp_owner.corp_substance s,
corp_owner.corp_library_substance ls,
corp_owner.corp_library l
WHERE
l.lib_id = <libId> and
s.cpd_id = ls.cpd_id and
l.lib_id = ls.lib_id" ;
sql = Lex.Replace(sql, "<libId>", libId.ToString());
dao.Prepare(sql);
dao.ExecuteReader();
MetaTable rootMt = MetaTableCollection.Get(MetaTable.PrimaryRootTable);
while (dao.Read())
{
if (Lex.IsNullOrEmpty(l.UserObject.Name))
{
string name = dao.GetString(0);
if (Lex.IsNullOrEmpty(name) || Lex.IsInteger(name))
{
name = dao.GetString(1); // use desc if no name or just a number
}
if (Lex.IsNullOrEmpty(name))
{
name = "Library " + libId;
}
l.UserObject.Name = name;
}
int intCorpId = dao.GetInt(2);
string corpId = CompoundId.Normalize(intCorpId, rootMt);
l.Add(corpId);
}
dao.CloseReader();
dao.Dispose();
return(l);
}
/// <summary>
/// Check that each of the numbers in the list exist in the database
/// </summary>
/// <param name="list">String form of list as entered by user</param>
/// <param name="rootTableName">Root table to check against</param>
/// <returns></returns>
public static string ValidateList(
string listText,
string rootTableName)
{
string cn, extCn, errorMsg;
int i1, i2;
if (String.IsNullOrEmpty(rootTableName))
{
return(null);
}
MetaTable rootTable = MetaTableCollection.GetWithException(rootTableName);
string[] cna = listText.Split(new Char[] { '\n', '\r', ' ' });
int t0 = TimeOfDay.Milliseconds();
for (i1 = 0; i1 < cna.Length; i1++)
{
if (i1 > 1000 || TimeOfDay.Milliseconds() - t0 > 3000)
{
return(null); // limit size & time of check
}
extCn = cna[i1].Trim();
if (extCn == "" || extCn.StartsWith("("))
{
continue;
}
i2 = extCn.IndexOf("("); // strip trailing text in parens
if (i2 > 0)
{
extCn = extCn.Substring(0, i2);
}
cn = CompoundId.BestMatch(extCn, rootTable);
if (cn == "")
{
cn = extCn; // nothing found, use original text
}
if (!CompoundIdUtil.Exists(cn, rootTable))
{
return(extCn);
}
}
return(null);
}
/// <summary>
/// Get query to select all data.
/// It consists of a single QueryTable with the CID and key table name
/// This keeps the initial query sent to the client small which helps
/// response time for users not on the same local network as the server.
/// </summary>
/// <param name="keyMtName"></param>
/// <param name="cn"></param>
/// <returns></returns>
public static Query GetSelectAllDataQuery(
string keyMtName,
string cn)
{
MetaTable mt = MetaTableCollection.GetWithException(MetaTable.AllDataQueryTable);
Query q = new Query();
QueryTable qt = new QueryTable(mt);
q.AddQueryTable(qt);
MetaTable keyMt = null;
if (Lex.IsDefined(keyMtName))
{
keyMt = MetaTableCollection.Get(keyMtName);
}
if (keyMt != null && keyMt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key
{
keyMt = keyMt.Root; // be sure we have root
cn = CompoundId.Normalize(cn, keyMt);
}
else
{
cn = CompoundId.Normalize(cn);
keyMt = CompoundId.GetRootMetaTableFromCid(cn, keyMt);
keyMt = keyMt.Root; // be sure we have root (may not be structure table)
}
if (keyMt == null)
{
throw new Exception("Failed to identify key MetaTable");
}
q.KeyCriteria = q.KeyCriteriaDisplay = " = " + cn;
QueryColumn qc = qt.GetQueryColumnByNameWithException("root_table");
qc.Criteria = qc.CriteriaDisplay = qc.MetaColumn.Name + " = " + keyMt.Name;
return(q);
}
/// <summary>
/// Show related structures for supplied CID
/// </summary>
/// <param name="initialCid"></param>
/// <param name="queryType"></param>
/// <param name="excludeCurrentCompounds"></param>
public void ShowInstance(
string initialCid,
string queryType,
bool excludeCurrentCompounds)
{
try
{
InSetup = true;
MetaTable mt = CompoundId.GetRootMetaTableFromCid(initialCid); // set title
string extCid = CompoundId.Format(initialCid, mt);
string txt = extCid + " and Related Structures";
if (mt != null)
{
txt = mt.KeyMetaColumn.Label + " " + txt;
}
Text = txt;
RelatedStructuresControl.MoleculeControl.ClearMolecule();
RelatedStructuresControl.SetupCheckmarks();
CompoundIdControl.Text = initialCid; // set cid to trigger start of search and UI update on next tick
Left = PopupPos; // position
Top = PopupPos;
PopupPos += 125;
if (PopupPos > InitialPopupPos + (5 * 125))
{
PopupPos = InitialPopupPos;
}
Show(); // show (not modal)
BringToFront();
return;
}
catch (Exception ex) { ex = ex; }
finally
{
InSetup = false;
}
}
/// <summary>
/// Lookup all of the salt forms for a compound
/// </summary>
/// <param name="cid></param>
/// <returns></returns>
public static List <string> GetAllSaltForms(
string cid)
{
if (cid == null || cid == "")
{
return(null);
}
if (ServiceFacade.UseRemoteServices)
{
Mobius.Services.Native.INativeSession nativeClient = ServiceFacade.CreateNativeSessionProxy();
Services.Native.NativeMethodTransportObject resultObject =
ServiceFacade.InvokeNativeMethod(nativeClient,
(int)Services.Native.ServiceCodes.MobiusCompoundUtilService,
(int)Services.Native.ServiceOpCodes.MobiusCompoundUtilService.GetAllSaltForms,
new Services.Native.NativeMethodTransportObject(new object[] { cid }));
((System.ServiceModel.IClientChannel)nativeClient).Close();
List <string> result = (resultObject != null) ? (List <string>)resultObject.Value : null;
return(result);
}
else
{
cid = CompoundId.Normalize(cid);
List <string> list = new List <string>();
list.Add(cid);
Dictionary <string, List <string> > cidDict = GetAllSaltForms(list);
if (cidDict.ContainsKey(cid))
{
return((List <string>)cidDict[cid]);
}
else
{
return(null);
}
}
}
/// <summary>
/// Get list from control removing blank lines & normalizing the ids
/// </summary>
/// <param name="CidList"></param>
void GetNormalizedListFromControl(CidList CidList)
{
CidList.Clear(); // clear contents, keep name
string[] cna = CidListCtl.Lines;
bool allowDuplicates = !RemoveDuplicates.Checked;
for (int i1 = 0; i1 < cna.Length; i1++)
{
string extCn = cna[i1];
if (extCn.Trim() == "" || extCn.StartsWith("("))
{
continue;
}
string intCid = CompoundId.Normalize(extCn, RootTable);
if (String.IsNullOrEmpty(intCid))
{
intCid = extCn;
}
CidList.Add(intCid, allowDuplicates);
}
return;
}
/// <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>
/// Setup the form with existing criteria
/// </summary>
/// <param name="qc"></param>
void Setup(
QueryColumn qc)
{
MetaTable mt = null;
string listName, mcName, tok, tok1, tok2, tok3, txt;
int i1;
if (qc != null && qc.QueryTable != null)
{
mt = qc.QueryTable.MetaTable;
}
Text = qc.ActiveLabel + " Criteria";
Prompt.Text =
"Choose one of the following " + qc.ActiveLabel + " criteria options.";
Cid.Text = "";
CidListDisplay.Text = "";
ListName.Text = "";
SavedListUo = null;
CidLo.Text = "";
CidHi.Text = "";
TempListName.Properties.Items.Clear(); // build dropdown of available temp list names
foreach (TempCidList tl0 in SS.I.TempCidLists)
{
TempListName.Properties.Items.Add(tl0.Name);
}
TempListName.Text = "Current";
Lex lex = new Lex();
lex.OpenString(qc.Criteria);
mcName = lex.GetUpper(); // metacolumn name
if (Lex.Ne(mcName, qc.MetaColumnName)) // check if col name is first token
{ // if not assume it's simply missing and that the operator is the first token
lex.Backup();
mcName = qc.MetaColumnName;
}
tok = lex.GetUpper(); // operator
tok1 = lex.GetUpper(); // following tokens */
tok2 = lex.GetUpper();
tok3 = lex.GetUpper();
if (tok == "=" || tok == "")
{
tok1 = CompoundId.Format(tok1, mt);
Cid.Text = tok1;
EQ.Checked = true;
}
else if (tok == "IN" && tok1.StartsWith("(")) // list saved with query only
{
InList.Checked = true;
CidListString = qc.Criteria;
i1 = CidListString.IndexOf("("); // just get the list itself if parenthesized
if (i1 >= 0)
{
CidListString = CidListString.Substring(i1 + 1);
}
if (CidListString.EndsWith(")"))
{
CidListString = CidListString.Substring(0, CidListString.Length - 1);
}
SetCidListDisplay();
}
else if (tok == "IN" && (Lex.Eq(Lex.RemoveAllQuotes(tok2), "CURRENT") ||
Lex.StartsWith(tok2, UserObject.TempFolderNameQualified)))
{ // temp list
if (Lex.StartsWith(tok2, UserObject.TempFolderNameQualified))
{
tok2 = tok2.Substring(UserObject.TempFolderNameQualified.Length);
}
TempListName.Text = tok2;
TempList.Checked = true;
}
else if (tok == "IN") // saved list
{
SavedListUo = QueryEngine.ResolveCnListReference(tok2);
if (SavedListUo != null)
{
listName = SavedListUo.Name;
}
else
{
listName = "Nonexistant list"; // list deleted
}
ListName.Text = listName;
SavedList.Checked = true;
}
else if (tok == "BETWEEN")
{
tok1 = CompoundId.Format(tok1);
CidLo.Text = tok1;
tok3 = CompoundId.Format(tok3);
CidHi.Text = tok3;
Between.Checked = true;
}
}
private void OK_Click(object sender, EventArgs e)
{
int CnCount;
string listName, listText, errMsg = "", tok, cidLo, cidHi, cid = null, cid2 = null;
QueryColumn qc = Qc;
MetaColumn mc = qc.MetaColumn;
MetaTable mt = mc.MetaTable;
// Perform validity check on form
// Equality
if (EQ.Checked)
{
tok = Cid.Text.ToUpper();
if (tok == "")
{
XtraMessageBox.Show("Single compound value is missing", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
Cid.Focus();
return;
}
if (SS.I.ValidateCompoundIds)
{
if (tok == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else if (RootTable.IsStructureDatabaseRootTable(mt.Root))
{
if (!CompoundId.IsValidForDatabase(tok, mt))
{
errMsg = tok + " is not a valid " + qc.ActiveLabel;
}
else
{
cid = CompoundId.Normalize(tok, mt);
tok = CompoundId.Format(cid, mt);
errMsg = "";
}
}
else
{
cid = tok; // use as is
errMsg = ""; // no error
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
qc.CriteriaDisplay = "= " + tok;
qc.Criteria = mc.Name + " = " + Lex.AddSingleQuotes(cid);
CnCount = 1;
}
// List
else if (InList.Checked)
{
listText = CidListString.Trim();
if (listText == "")
{
XtraMessageBox.Show("The list must contain one or more items", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
if (CidListDisplayIsEditable())
{
CidListDisplay.Focus();
}
return;
}
qc.CriteriaDisplay = CidList.FormatAbbreviatedCidListForDisplay(qc, listText);
qc.Criteria = mc.Name + " IN (" + listText + ")";
}
// Current list
else if (TempList.Checked)
{
qc.CriteriaDisplay = "In temporary list: " + TempListName.Text;
qc.Criteria = mc.Name + " IN LIST " + UserObject.TempFolderNameQualified + TempListName.Text;
CnCount = SessionManager.CurrentResultKeysCount;
}
// Saved list
else if (SavedList.Checked)
{
if (ListName.Text.Trim() == "")
{
XtraMessageBox.Show("A list name must be supplied", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
ListName.Focus();
return;
}
string internalName = ResolveSavedListUo();
if (internalName == null)
{
return;
}
qc.CriteriaDisplay = "In list: " + SavedListUo.Name;
qc.Criteria = mc.Name + " IN LIST " + Lex.AddSingleQuotes("CNLIST_" + SavedListUo.Id.ToString()); // quote list name
CnCount = 1; // may be larger
}
// Between
else if (Between.Checked)
{
cidLo = CidLo.Text.Trim();
cidHi = CidHi.Text.Trim();
if (cidLo == "" || cidHi == "")
{
XtraMessageBox.Show("Between value is missing", UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.None);
if (CidLo.Text.Trim() == "")
{
CidLo.Focus();
}
else if (CidHi.Text.Trim() == "")
{
CidHi.Focus();
}
return;
}
if (SS.I.ValidateCompoundIds)
{
errMsg = "";
if (cidLo == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else
{
if (!CompoundId.IsValidForDatabase(cidLo, mt))
{
errMsg = cidLo + " is not a valid " + qc.ActiveLabel;
}
else
{
cid = CompoundId.Normalize(cidLo, mt);
cidLo = CompoundId.Format(cid);
}
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
else
{
cid = cidLo; // use as is
}
if (SS.I.ValidateCompoundIds)
{
errMsg = "";
if (cidHi == "")
{
errMsg = "You must supply a " + qc.ActiveLabel;
}
else
{
if (!CompoundId.IsValidForDatabase(cidHi, mt))
{
errMsg = cidHi + " is not a valid " + qc.ActiveLabel;
}
else
{
cid2 = CompoundId.Normalize(cidHi, mt);
cidHi = CompoundId.Format(cid2);
}
}
if (errMsg != "")
{
MessageBoxMx.Show(errMsg, UmlautMobius.String, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
return;
}
}
else
{
cid2 = cidHi; // use as is
}
qc.CriteriaDisplay = "Between " + cidLo + " and " + cidHi;
qc.Criteria = mc.Name + " BETWEEN " + Lex.AddSingleQuotes(cid) + " AND " + Lex.AddSingleQuotes(cid2);
}
else if (None.Checked)
{
qc.CriteriaDisplay = "";
qc.Criteria = "";
CnCount = 0;
}
DialogResult = DialogResult.OK;
// this.Hide();
}
/// <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
}
public void Setup(ColumnInfo colInfo)
{
bool check;
string s;
InSetup = true;
ColInfo = colInfo; // save ref to colInfo
QueryColumn qc = colInfo.Qc;
ParsedSingleCriteria psc = MqlUtil.ParseSingleCriteria(ColInfo.Qc.SecondaryCriteria); // parse criteria
Dictionary <string, object> listDict = new Dictionary <string, object>();
if (psc != null && psc.OpEnum != CompareOp.In)
{
psc = null; // switching from other criteria type
}
if (psc != null && Lex.Contains(qc.SecondaryCriteria, "(All)"))
{
psc = null; // rebuild if "all" items
}
if (psc != null) // list of previously selected items
{
foreach (string vs in psc.ValueList)
{
if (qc.MetaColumn.DataType == MetaColumnType.CompoundId)
{
s = CompoundId.Format(vs);
}
else if (qc.MetaColumn.DataType == MetaColumnType.Date)
{
s = DateTimeMx.Format(vs);
}
else
{
s = vs;
}
listDict[s.ToUpper()] = null;
}
}
else // setup default criteria
{
qc.SecondaryCriteria = qc.MetaColumn.Name + " in ('(All)')";
qc.SecondaryCriteriaDisplay = qc.ActiveLabel + " in list (All)";
}
ColumnStatistics stats = colInfo.Rfld.GetStats();
CheckedListBoxItemCollection items = ItemList.Items;
items.Clear();
check = (psc == null || listDict.ContainsKey("(All)".ToUpper()) || listDict.ContainsKey("All".ToUpper()));
AddItem("(All)", check);
if (stats.NullsExist || Math.Abs(1) == 1)
{ // always include blank/nonblank since there may be null rows because other tables have more rows for key
check = (psc == null || listDict.ContainsKey("(Blanks)".ToUpper()) || listDict.ContainsKey("Blanks".ToUpper()));
BlanksPos = items.Count;
AddItem("(Blanks)", check);
check = (psc == null || listDict.ContainsKey("(Non blanks)".ToUpper()) || listDict.ContainsKey("Nonblanks".ToUpper()));
NonBlanksPos = items.Count;
AddItem("(Non blanks)", check);
}
else
{
BlanksPos = NonBlanksPos = -1;
}
foreach (MobiusDataType mdt in stats.DistinctValueList)
{
s = mdt.FormattedText;
check = (psc == null || listDict.ContainsKey(s.ToUpper()));
AddItem(s, check);
if (items.Count >= 100)
{
AddItem("...", check);
break;
}
}
int itemHeight = 18; // height of single item (really 17 but add a bit extra)
int fullHeight = ItemList.Top + 6 + ItemList.Items.Count * itemHeight; // full height of control
if (fullHeight < this.Height)
{
this.Height = fullHeight;
}
InSetup = false;
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>
/// 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);
}
}
