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; }
/// <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> /// 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> /// 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); }
// # 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> /// 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> /// 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> /// 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> /// Select a Molecule object for a compound id /// </summary> /// <param name="cid"></param> /// <param name="mt"></param> /// <returns></returns> public static MoleculeMx SelectMoleculeForCid( string cid, MetaTable mt = null) { MetaColumn strMc = null; string mtName = null, keyColName, strColExpr, chimeString; MoleculeMx cs; if (cid == null || cid == "") { return(null); } if (RestrictedDatabaseView.KeyIsRetricted(cid)) { return(null); } //if (mt != null) mtName = mt.Name; // debug bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database if (isUcdb) // if root metatable is user database then normalize based on key { mt = mt.Root; // be sure we have root cid = CompoundId.Normalize(cid, mt); } else { cid = CompoundId.Normalize(cid, mt); cs = MoleculeCache.Get(cid); // see if in cache if (cs != null) { return(cs); } mt = CompoundId.GetRootMetaTableFromCid(cid, mt); if (mt != null && Lex.Eq(mt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null) { mt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database } } if (mt == null) { return(null); } strMc = mt.FirstStructureMetaColumn; //.getmt.GetMetaColumnByName("molstructure"); if (strMc == null) { return(null); } cid = CompoundId.NormalizeForDatabase(cid, mt); if (String.IsNullOrEmpty(cid)) { return(null); } // Call external method to select structure if (strMc.ColumnMap.StartsWith("InternalMethod", StringComparison.OrdinalIgnoreCase) || strMc.ColumnMap.StartsWith("PluginMethod", StringComparison.OrdinalIgnoreCase)) { // call external method to get structure List <MetaColumn> selectList = new List <MetaColumn>(); selectList.Add(mt.KeyMetaColumn); selectList.Add(strMc); object[] vo = new object[2]; vo[0] = cid; try { GenericMetaBroker.CallLateBindMethodToFillVo(vo, 1, mt, selectList); } catch (Exception ex) { return(null); } if (vo[1] is MoleculeMx) { cs = (MoleculeMx)vo[1]; } else if (vo[1] is string) { cs = MoleculeMx.MolStringToMoleculeMx((string)vo[1]); } else { cs = null; } if (!isUcdb) { MoleculeCache.AddMolecule(cid, cs); } return(cs); } // Normal case //if (HelmEnabled) // temp till server back //{ // cs = new MoleculeMx(); // MoleculeMx.SetMoleculeToTestHelmString(cid, cs); // return cs; //} string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries) strColExpr = strMc.ColumnMap; if (strColExpr == "") { strColExpr = strMc.Name; } if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge { if (!Lex.Contains(tableMap, "chime(")) // if no chime expression { strColExpr = "chime(ctab)"; // then create one (gets clob) } strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters } keyColName = mt.KeyMetaColumn.ColumnMap; if (keyColName == "") { keyColName = mt.KeyMetaColumn.Name; } DbType parmType = DbType.String; object cidObj = cid; if (mt.KeyMetaColumn.IsNumeric) { if (!Lex.IsInteger(cid)) { return(null); // if numeric col be sure cid is numeric also } parmType = DbType.Int64; cidObj = Int64.Parse(cid); } string sql = "select " + strColExpr + " " + "from " + tableMap + " " + "where " + keyColName + " = :0"; DbCommandMx drd = new DbCommandMx(); try { drd.PrepareParameterized(sql, parmType); drd.ExecuteReader(cidObj); if (!drd.Read() || drd.Rdr.IsDBNull(0)) { drd.Dispose(); return(null); } string molString = drd.GetString(0); drd.Dispose(); MoleculeMx.TrySetStructureFormatPrefix(ref molString, strMc.DataTransform); // add molString type if indicated by data transform cs = MoleculeMx.MolStringToMoleculeMx(molString); cs.StoreKeyValueInMolComments(strMc, cid); if (!isUcdb) { MoleculeCache.AddMolecule(cid, cs); } //if (MoleculeMx.HelmEnabled == true && Lex.IsInteger(cid)) // MoleculeMx.SetMoleculeToTestHelmString(cid, cs); return(cs); } catch (Exception ex) { // just log message & return; DebugLog.Message("SelectMoleculeForCid Exception, Cid: " + cid + ", table: " + mt.Name + "\n" + "sql: " + OracleMx.FormatSql(sql) + "\n" + DebugLog.FormatExceptionMessage(ex)); if (drd != null) { drd.Dispose(); } return(null); } }
/// <summary> /// Display any structure and related compounds for input string or /// Matching database contents /// </summary> /// <param name="inputString"></param> /// <param name="showRelatedCompounds"></param> public void ShowQuickSearchPopup( string inputString, bool updateRelatedCompounds) { string extCid = "", tok, tok2; MRUEdit clc = CommandLineControl; // be sure popup is properly positioned if (clc.FindForm() == SessionManager.Instance.ShellForm) // slightly different position for Shell form that other forms { Left = clc.Left; Top = clc.Bottom; } else { Point p = clc.PointToScreen(new Point(0, clc.Bottom)); // get screen coord for upper left corner of popup Point p2 = this.Parent.PointToClient(p); // convert screen coord back to relative Left = p2.X; // position below input control Top = p2.Y; } // See if input matches structure Molecule = null; if (StructurePopupEnabled && !String.IsNullOrEmpty(inputString)) { Cid = CompoundId.Normalize(inputString); extCid = CompoundId.Format(Cid); CidMt = CompoundId.GetRootMetaTableFromCid(Cid); Molecule = MoleculeUtil.SelectMoleculeForCid(Cid, CidMt); RelatedDataButton.Enabled = (CidMt != null && Lex.Eq(CidMt.Root.Name, MetaTable.PrimaryRootTable)); AllDataButtonStb.Enabled = QbUtil.IsMdbAssayDataViewAvailable(CidMt); } if (Molecule != null) { string txt = extCid; if (CidMt != null) { txt = CidMt.KeyMetaColumn.Label + " " + txt; } if (Lex.IsUndefined(Molecule.Id)) { Molecule.Id = txt; } if (SS.I.FindRelatedCpdsInQuickSearch) { txt += " and Related Structures"; } StructurePanel.Text = txt; RelatedStructuresControl.MoleculeControl.Molecule = Molecule; if (SS.I.FindRelatedCpdsInQuickSearch) { Width = InitialStructurePanelWidth; // assure correct width } else { Width = InitialStructurePanelWidthWithoutRelatedStructureOptions; RSM.ClearSearchStatus(); } if (!StructurePanel.Visible) // if structure not showing then set height to initial height { Height = InitialStructurePanelHeight; } StructurePanel.Location = new Point(0, 0); StructurePanel.Visible = true; //Size = StructurePanel.Size; ListControl.Visible = false; Visible = true; RenderId++; if (updateRelatedCompounds) { UpdateRelatedCidsDisplay(CidMt, Cid, Molecule, RenderId); } return; } // See if input matches database contents tree else if (ContentsPopupEnabled) { DoQuickSearchAndDisplayOfContentsTree(inputString); return; } HideQuickSearchPopup(); return; }
/// <summary> /// Handle UI operations during tick of timer /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Timer_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; try { string txt = CompoundIdControl.Text; if (txt == PreviousInput) { return; } if (DateTime.Now.Subtract(LastCompoundIdControlKeyDownTime).TotalSeconds < .5) { // wait for a pause in typing before doing retrieve //SystemUtil.Beep(); // debug return; } PreviousInput = txt; MetaTable mt = CompoundId.GetRootMetaTableFromCid(txt); string cid = CompoundId.Normalize(txt, mt); string mtName = mt.Name; MoleculeMx queryStruct = MoleculeUtil.SelectMoleculeForCid(cid, mt); RSM.StartSearchAndRetrievalOfRelatedStructures(mtName, cid, queryStruct, StructureSearchTypes); //Text = "Structures related to " + txt; // set form header InSetup = true; // set structure without triggering another search RelatedStructuresControl.MoleculeControl.Molecule = queryStruct; InSetup = false; } catch (Exception ex) { ex = ex; } finally { InTimer_Tick = false; } #if false // code to handle either CID or structure input bool userDefinedStructure = Lex.Eq(CurrentInput, "User Defined"); string mtName = ""; if (!userDefinedStructure) { CidMt = CompoundId.GetRootTableFromCid(CurrentInput); mtName = CidMt.Name; Cid = CompoundId.Normalize(CurrentInput, CidMt); QueryStruct = MoleculeUtil.SelectChemicalStructureFromCid(Cid, CidMt); InSetup = true; Text = "Structures related to " + CurrentInput; ChemicalStructure.SetRendererStructure(SQuery, QueryStruct); InSetup = false; } else // structure directly entered { if (!StructureChanged) { InTimer_Tick = false; return; } Cid = SearchId.ToString(); CidMt = null; mtName = "UserDefined"; QueryStruct = new ChemicalStructure(StructureFormat.MolFile, SQuery.MolfileString); StructureChanged = false; } #endif }
/// <summary> /// See if a ClickFunction command & process if so /// </summary> /// <param name="command"></param> /// <param name="qm"></param> /// <param name="cInf"></param> public static void Process( string command, QueryManager qm, CellInfo cInf = null) { QueryTable rootQt, qt; QueryColumn qc; MetaTable mt; MetaColumn mc; Query q2; string dbName = "", mtName = "", mcName = ""; List <string> args0, args; string funcName, arg1, arg2, arg3, arg4, arg5; string value = "", keyValue = ""; int ai; try { // Parse click function arguments stripping all single quotes. // Arguments may be defined in the clickfunction definition including col values // indicated by field.Value in the metacolumn clickfunction definition. // If no args are defined in the clickfunction definition then a field value // argument will be added by default or the keyValue if [keyvalue] appears in the // ClickFunction definition CurrentClickQueryManager = qm; args0 = Lex.ParseAllExcludingDelimiters(command, "( , )", false); args = new List <string>(); for (ai = 0; ai < args0.Count; ai++) // strip all single quotes { string arg = args0[ai]; if (arg.StartsWith("'")) { arg = Lex.RemoveSingleQuotes(arg); } //if (Lex.Eq(arg, "[rowcol]") && cInf!= null) //{ // pass grid row & col // args.Add(cInf.GridRowHandle.ToString()); // args.Add(cInf.GridColAbsoluteIndex.ToString()); //} //else args.Add(arg); } funcName = args[0]; arg1 = (args.Count >= 2 ? args[1] : ""); // get other args arg2 = (args.Count >= 3 ? args[2] : ""); arg3 = (args.Count >= 4 ? args[3] : ""); arg4 = (args.Count >= 5 ? args[4] : ""); arg5 = (args.Count >= 6 ? args[5] : ""); if (Lex.Eq(funcName, "DisplayAllData")) { // do all data display for supplied root table and key, i.e. DisplayAllData(TableName, KeyColName, KeyValue) ParseMetaTableMetaColumn(arg1, out mt, arg2, out mc); string extKey = arg3; string intKey = CompoundId.Normalize(extKey, mt); Progress.Show("Building Query..."); _query = QueryEngine.GetSelectAllDataQuery(mt.Name, intKey); Progress.Show("Retrieving data..."); // put up progress dialog since this may take a while QbUtil.RunPopupQuery(_query, mt.KeyMetaColumn.Name + " " + extKey); Progress.Hide(); return; } else if (Lex.Eq(funcName, "DisplayAllDataUsingDbName")) { // display all data for supplied database synonym & key value, i.e. DisplayAllData2(DataBaseSynonym, KeyValue) mtName = null; dbName = arg1; RootTable rti = RootTable.GetFromTableLabel(dbName); if (rti != null) { mtName = rti.MetaTableName; } else // try synonyms { DictionaryMx dict = DictionaryMx.Get("Database_Synonyms"); if (dict != null) { mtName = dict.LookupDefinition(dbName); } } if (String.IsNullOrEmpty(mtName)) { MessageBoxMx.ShowError("Unrecognized database: " + dbName); return; } mt = MetaTableCollection.Get(mtName); if (mt == null) { MessageBoxMx.ShowError("Can't find key metatable " + mtName + " for database " + dbName); return; } string extKey = arg2; string intKey = CompoundId.Normalize(extKey, mt); Progress.Show("Building Query..."); _query = QueryEngine.GetSelectAllDataQuery(mt.Name, intKey); Progress.Show("Retrieving data..."); // put up progress dialog since this may take a while QbUtil.RunPopupQuery(_query, mt.KeyMetaColumn.Name + " " + extKey); return; } // Run a query displaying results to a grid or web page and substituting a parameter value else if (Lex.Eq(funcName, "RunHtmlQuery") || Lex.Eq(funcName, "RunGridQuery")) { // command to display to grid or html if (arg1.StartsWith("MetaTreeNode=", StringComparison.OrdinalIgnoreCase)) { // query based on metatables under a tree node string nodeName = arg1.Substring("MetaTreeNode=".Length).Trim(); _cid = arg2; MetaTreeNode mtn = MetaTree.GetNode(nodeName); if (mtn == null) { MessageBoxMx.ShowError("Can't find tree node referenced in ClickFunction: " + nodeName); return; } _query = new Query(); MetaTable rootMt = null; foreach (MetaTreeNode mtn_ in mtn.Nodes) { if (!mtn_.IsDataTableType) { continue; } mt = MetaTableCollection.Get(mtn_.Target); if (mt == null) { continue; } if (rootMt == null) { rootMt = mt.Root; rootQt = new QueryTable(_query, rootMt); } if (mt == rootMt) { continue; } qt = new QueryTable(_query, mt); } if (_query.Tables.Count == 0) { MessageBoxMx.ShowError("No valid data tables found: " + nodeName); return; } _query.KeyCriteria = "= " + _cid; _title = mtn.Label + " for " + _query.Tables[0].MetaTable.MetaColumns[0].Label + " " + CompoundId.Format(_cid); } else if (arg1.StartsWith("Query=", StringComparison.OrdinalIgnoreCase)) { // query based on saved query string qIdString = arg1.Substring("Query=".Length).Trim(); if (qIdString.StartsWith("Query_", StringComparison.OrdinalIgnoreCase)) { qIdString = qIdString.Substring("Query_".Length).Trim(); } int qId = int.Parse(qIdString); _query = QbUtil.ReadQuery(qId); _cid = arg2; _query.KeyCriteria = "= " + _cid; _title = _query.UserObject.Name + " for " + _query.Tables[0].MetaTable.MetaColumns[0].Label + " " + CompoundId.Format(_cid); } else // explicit mql string to execute { _mql = arg1; // mql to execute if (Lex.IsUndefined(_mql)) { throw new Exception("Expected MQL query not found: " + command); } mt = null; mc = null; if (Lex.IsDefined(arg2) && Lex.IsDefined(arg3)) { mtName = arg2; mcName = arg3; value = arg4; // value to plug in to mql keyValue = value; ParseMetaTableMetaColumn(arg2, out mt, arg3, out mc); } else if (cInf != null) { mt = cInf.Mt; mc = cInf.Mc; value = cInf?.DataValue?.ToString(); keyValue = qm?.DataTableManager?.GetRowKey(cInf.DataRowIndex); } if (mt == null || mc == null) { throw new Exception("Invalid MetaTable or MetaColumn name(s): " + command); } if (!mc.IsNumeric) { value = Lex.AddSingleQuotes(value); // quote if not numeric } int i1 = _mql.ToLower().IndexOf("[value]"); // see if a value parameter if (i1 >= 0) { string value2 = value; _mql = _mql.Replace(_mql.Substring(i1, 7), value); _title = mc.Label + " " + value; } i1 = _mql.ToLower().IndexOf("[keyvalue]"); // see if a key value parameter if (i1 >= 0) { _mql = _mql.Replace(_mql.Substring(i1, 10), keyValue); _title = mt.KeyMetaColumn.Label + " " + keyValue; } try { _query = MqlUtil.ConvertMqlToQuery(_mql); } catch (Exception ex) { MessageBoxMx.ShowError("Error converting Mql to query: " + ex.Message); return; } } if (Lex.Eq(funcName, "RunHtmlQuery")) { QbUtil.RunPopupQuery(_query, _title, OutputDest.Html); } else // output to grid { QbUtil.RunPopupQuery(_query, _title, OutputDest.WinForms); } //else // create new grid query & run (will lose results for current query) //{ // QbUtil.NewQuery(_title); // show in query builder // QbUtil.SetCurrentQueryInstance(_query); // QbUtil.RenderQuery(); // string nextCommand = QueryExec.RunQuery(_query, OutputDest.Grid); //} return; } // Open a URL, normally substituting parameter value else if (Lex.Eq(funcName, "OpenUrl")) { string url = arg1; // url to execute value = arg2; // value to plug in to url int i1 = Lex.IndexOf(url, "[value]"); // fill in one of the value place holders if (i1 >= 0) { string value2 = value; url = url.Replace(url.Substring(i1, 7), value); } else // check to see if we are matching on key { i1 = Lex.IndexOf(url, "[keyvalue]"); if (i1 >= 0) { url = url.Replace(url.Substring(i1, 10), value); } } SystemUtil.StartProcess(url); return; } else if (Lex.Eq(funcName, "OpenUrlFromSmallWorldCid")) { SmallWorldDepictions.OpenUrlFromSmallWorldCid(arg1); return; } else if (Lex.Eq(funcName, "ShowProjectDescription")) { string projName = arg1; QbUtil.ShowProjectDescription(projName); return; } else if (Lex.Eq(funcName, "ShowTableDescription")) { mtName = arg1; QbUtil.ShowTableDescription(mtName); return; } else if (Lex.Eq(funcName, "DisplayDrilldownDetail")) { // drill down into a result value mtName = arg1; // table mcName = arg2; // column int level = Int32.Parse(arg3); string resultId = arg4; // quoted resultId to get q2 = QueryEngine.GetSummarizationDetailQuery(mtName, mcName, level, resultId); if (q2 == null) { throw new Exception("Unable to build drill-down query for: " + mtName + "." + mcName); } bool success = QbUtil.RunPopupQuery(q2, "Result Detail", OutputDest.WinForms); return; } //else if (Lex.Eq(funcName, "PopupSmilesStructure")) // display structure for a Smiles string (still needs some work...) //{ // string molString = arg1.ToString(); // ChemicalStructure cs = new ChemicalStructure(StructureFormat.Smiles, molString); // ToolHelper.DisplayStructureInPopupGrid("Title...", "Smiles", "Structure", cs); //} //else if (Lex.Eq(funcName, "PopupChimeStructure")) // display structure for a Chime string //{ // string molString = arg1.ToString(); // ChemicalStructure cs = new ChemicalStructure(StructureFormat.Smiles, molString); // ToolHelper.DisplayStructureInPopupGrid("Title...", "Smiles", "Structure", cs); //} else if (Lex.Eq(funcName, "DisplayWebPage")) { // substitute a field value into a url & display associated web page string url = arg1; ParseMetaTableMetaColumn(arg2, out mt, arg3, out mc); value = arg4; // value to plug in to mql // value = "{6E9C28EF-407E-44A0-9007-5FFB735A5C6C}"; // debug // value = "{0AC17903-E551-445E-BFAA-860023D2884F}"; // debug // value = "{63EE71F9-15BA-42FB-AFDC-C399103707B1}"; // debug // value = "{80591183-B7BA-4669-8C5F-7E7F53D981CE}"; //lex.OpenString(mc.ClickFunction); // reparse url to get proper case //funcName = lex.GetNonDelimiter(); //url = Lex.RemoveAllQuotes(lex.GetNonDelimiter()); _title = mc.Label + " " + value; int i1 = url.ToLower().IndexOf("[value]"); // fill in one of the value place holders if (i1 >= 0) { url = url.Replace(url.Substring(i1, 7), value); } else // check to see if we are matching on key { i1 = url.ToLower().IndexOf("[keyvalue]"); if (i1 >= 0) { url = url.Replace(url.Substring(i1, 10), value); _title = mt.KeyMetaColumn.Label + " " + value; } } UIMisc.ShowHtmlPopupFormDocument(url, _title); return; } else if (Lex.Eq(funcName, "DisplayOracleBlobDocument")) // display a document contained in an Oracle blob column { // Syntax: DisplayOracleBlobDocument(<table-to-lookup>, <value_match_column>, <file-name-or-type-col>, <content-column>) string table = arg1; string matchCol = arg2; string typeCol = arg3; string contentCol = arg4; string matchVal = arg5; // value to match try { string typeName; byte[] ba; UalUtil.SelectOracleBlob(table, matchCol, typeCol, contentCol, matchVal, out typeName, out ba); if (ba == null || ba.Length == 0) { return; } UIMisc.SaveAndOpenBinaryDocument(typeName, ba); } catch (Exception ex) { MessageBoxMx.ShowError("Error retrieving document: " + ex.Message); return; } return; } else if (Lex.Eq(funcName, "DisplayOracleClobDocument")) // display a document contained in an Oracle clob column { // Syntax: DisplayOracleBlobDocument(<table-to-lookup>, <value_match_column>, <file-name-or-type-col>, <content-column>) string table = arg1; string matchCol = arg2; string typeCol = arg3; string contentCol = arg4; string matchVal = arg5; // value to match try { string typeName, clobString; UalUtil.SelectOracleClob(table, matchCol, typeCol, contentCol, matchVal, out typeName, out clobString); if (Lex.IsUndefined(clobString)) { return; } UIMisc.SaveAndOpenBase64BinaryStringDocument(typeName, clobString); // assume clob string is a Base64Binary string } catch (Exception ex) { MessageBoxMx.ShowError("Error retrieving document: " + ex.Message); return; } return; } else if (Plugins.IsMethodExtensionPoint(funcName)) { List <object> objArgs = new List <object>(); for (ai = 1; ai < args.Count; ai++) // build list of object arguments { objArgs.Add(args[ai]); } Plugins.CallStringExtensionPointMethod(funcName, objArgs); } else if (Lex.Eq(funcName, "None")) // dummy click function { return; } else { MessageBoxMx.ShowError("Unrecogized click function: " + funcName); return; } } catch (Exception ex) { Exception ex2 = ex; if (ex.InnerException != null) { ex2 = ex.InnerException; } string msg = "Error executing ClickFunction: " + command + "\r\n" + DebugLog.FormatExceptionMessage(ex); MessageBoxMx.ShowError(msg); ServicesLog.Message(msg); } }
/// <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> /// 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> /// Retrieve any existing SVG for the list of supplied molecules /// The Id column should contain the CorpId /// </summary> /// <param name="molList"></param> public static int SelectMoleculeListSvg( List <MoleculeMx> molList) { MoleculeMx mol; int corpId, molSvgsFetchedCount = 0; string corpIdString, molString, svg; const string sql = @" SELECT corp_nbr, molstructure svgString FROM mbs_owner.corp_moltable_mx WHERE corp_nbr in (<list>) and molstructure is not null " ; if (!Security.UserInfo.Privileges.CanRetrieveStructures) // structures allowed? { return(0); } //if (DebugMx.True) return 0; // debug, don't use existing values List <string> lsnList = new List <string>(); Dictionary <string, MoleculeMx> molDict = new Dictionary <string, MoleculeMx>(); foreach (MoleculeMx mol0 in molList) // set up a dict keyed by cid with mol values { if (mol0.PrimaryFormat != MoleculeFormat.Helm || Lex.IsUndefined(mol0.PrimaryValue)) { continue; } if (int.TryParse(mol0.Id, out corpId)) { molDict[mol0.Id] = mol0; } } if (molDict.Count == 0) { return(0); } DbCommandMx cmd = new DbCommandMx(); cmd.PrepareListReader(sql, DbType.String); cmd.ExecuteListReader(new List <string>(molDict.Keys)); while (cmd.Read()) { corpId = cmd.GetInt(0); if (!cmd.IsNull(1)) // molstructure { molString = cmd.GetClob(1); if (!SvgUtil.IsSvgString(molString)) { continue; // skip if not SVG } svg = molString; // should be compressed format SVG corpIdString = CompoundId.Normalize(corpId.ToString()); if (Lex.IsDefined(svg) && molDict.ContainsKey(corpIdString)) { mol = molDict[corpIdString]; mol.SvgString = svg; molSvgsFetchedCount++; } } } cmd.CloseReader(); return(molSvgsFetchedCount); }
/// <summary> /// Analyze key criteria in token list. /// A saved list reference is returned as a set of key values & /// blanked out in the token list. /// Other key references are returned as a set of indexes in a list. /// </summary> /// <param name="q"></param> /// <param name="Qtd"></param> /// <param name="tokens"></param> /// <param name="keyCriteriaPositions">Token indexes for key criteria column names</param> /// <param name="keyCriteriaSavedListKeys">Keys in any referenced saved list</param> /// <param name="keyCriteriaInListKeys">Keys in any literal list </param> /// <param name="keyCriteriaConstantPositions">Positions of key values for literal lists</param> public static void AnalyzeKeyCriteria( Query q, QueryTableData[] Qtd, List <MqlToken> tokens, out CompareOp keyCriteriaOp, out List <int> keyCriteriaPositions, out List <string> keyCriteriaSavedListKeys, out List <string> keyCriteriaInListKeys, out List <int> keyCriteriaConstantPositions) { string tok1, tok2, tok3, tok4, tok5; keyCriteriaOp = CompareOp.Unknown; keyCriteriaPositions = new List <int>(); keyCriteriaSavedListKeys = null; keyCriteriaInListKeys = null; keyCriteriaConstantPositions = new List <int>(); for (int tki = 0; tki < tokens.Count; tki++) { QueryColumn qc = tokens[tki].Qc; if (qc == null) { continue; } if (!qc.IsKey) { continue; } keyCriteriaPositions.Add(tki); // remember position of key col reference if (tokens.Count < tki + 2) { throw new QueryException("Incomplete compound id criteria at end of statement"); } bool notLogic = false; tok1 = GetTokenListString(tokens, tki + 1); if (Lex.Eq(tok1, "Not")) { notLogic = true; tki++; if (tokens.Count < tki + 2) { throw new QueryException("Incomplete compound id criteria at end of statement"); } tok1 = GetTokenListString(tokens, tki + 1); } tok2 = GetTokenListString(tokens, tki + 2); tok3 = GetTokenListString(tokens, tki + 3); tok4 = GetTokenListString(tokens, tki + 4); tok5 = GetTokenListString(tokens, tki + 5); // Saved List if (tokens.Count > tki + 3 && Lex.Eq(tok1, "In") && Lex.Eq(tok2, "List")) { // have a saved list keyCriteriaOp = CompareOp.InList; if (keyCriteriaSavedListKeys != null) // already have it? { throw new UserQueryException("Only one condition is allowed for the " + qc.ActiveLabel + " field"); } if (notLogic) // not currently allowed for saved lists { throw new UserQueryException("\"Not\" logic is not allowed for " + qc.ActiveLabel + " saved lists"); } string listName = tokens[tki + 3].Tok.Text; UserObject uo = ResolveCidListReference(listName); if (uo == null) { throw new UserQueryException("Key list " + listName + " not found"); } listName = uo.InternalName; CidList keyCriteriaSavedList = CidListDao.Read(listName, QueryEngine.GetRootTable(q)); if (keyCriteriaSavedList == null) { throw new UserQueryException("Key list " + listName + " not found"); } keyCriteriaSavedListKeys = keyCriteriaSavedList.ToStringList(); tokens[tki].Qc = null; tokens[tki].Tok.Text = ""; for (int tki2 = tki + 1; tki2 <= tki + 3; tki2++) { tokens[tki2].Tok.Text = ""; } if (!MqlUtil.DisableAdjacentAndLogic(tokens, tki, tki + 3)) { throw new UserQueryException("Only \"And\" logic is allowed with " + qc.ActiveLabel + " saved lists"); } int ti = q.GetQueryTableIndexByAlias(qc.QueryTable.Alias); Qtd[ti].CriteriaCount--; keyCriteriaPositions.RemoveAt(keyCriteriaPositions.Count - 1); } // Explicit list of allowed keys else if (tokens.Count > tki + 2 && Lex.Eq(tok1, "In") && Lex.Eq(tok2, "(")) { keyCriteriaOp = CompareOp.In; int listKeyCount = 0; // count keys in this list for (int tki2 = tki + 3; tki2 < tokens.Count; tki2++) { tok1 = tokens[tki2].Tok.Text; if (tok1 == ",") { continue; } else if (tok1 == ")") { break; } keyCriteriaConstantPositions.Add(tki2); if (keyCriteriaInListKeys == null) { keyCriteriaInListKeys = new List <string>(); } MetaTable rootTable = QueryEngine.GetRootTable(q); string normKey = CompoundId.Normalize(tok1, rootTable); if (normKey != null && normKey != "") { keyCriteriaInListKeys.Add(normKey); listKeyCount++; } } if (listKeyCount == 0) { throw new UserQueryException("The query contains an invalid empty list of " + qc.ActiveLabel + "s"); } } // Between a range of key values else if (tokens.Count > tki + 4 && Lex.Eq(tok1, "Between") && Lex.Eq(tok3, "And")) { keyCriteriaOp = CompareOp.Between; keyCriteriaConstantPositions.Add(tki + 2); keyCriteriaConstantPositions.Add(tki + 4); } // Single key value, treat like a list with a single value else if (Lex.Eq(tok1, "=")) { keyCriteriaOp = CompareOp.Eq; keyCriteriaConstantPositions.Add(tki + 2); if (keyCriteriaInListKeys == null) { keyCriteriaInListKeys = new List <string>(); } MetaTable rootTable = QueryEngine.GetRootTable(q); string normKey = CompoundId.Normalize(tok2, rootTable); if (Lex.IsDefined(normKey)) { keyCriteriaInListKeys.Add(normKey); } } // Other binary operator else if (MqlUtil.IsBasicComparisonOperator(tok1)) { keyCriteriaOp = CompareOpString.ToCompareOp(tok1); keyCriteriaConstantPositions.Add(tki + 2); } // Unary operator "is null" else if (Lex.Eq(tok1, "Is") && Lex.Eq(tok2, "Null")) { keyCriteriaOp = CompareOp.IsNull; } // Unary operator "is not null" else if (Lex.Eq(tok1, "Is") && Lex.Eq(tok2, "Not") && Lex.Eq(tok3, "Null")) { keyCriteriaOp = CompareOp.IsNotNull; } else { throw new QueryException("Unrecognized compound id condition " + tok1); } } return; }
/// <summary> /// Build the query /// </summary> private Query BuildQuery() { Query q, q2; QueryTable qt; QueryColumn keyQc; MetaTreeNode mtn; string title = ""; q = null; foreach (ImageListBoxItem item in MruListCtl.SelectedItems) { mtn = item.Tag as MetaTreeNode; if (mtn == null || // use base query (mtn.Type == MetaTreeNodeType.Query && Lex.Eq(mtn.Target, "RelatedCompoundsSourceQuery"))) { if (q == null) { q = SourceQuery.Clone(); // base new query on query we originated from } else { q.MergeQuery(SourceQuery); } } else if (mtn.Type == MetaTreeNodeType.Query) // selected query item { UserObjectType objType; int objId = -1; UserObject.ParseObjectTypeAndIdFromInternalName(mtn.Target, out objType, out objId); q2 = QbUtil.QueriesControl.GetQueryByUserObjectId(objId); // if this query is open, get the current, possibly edited, version if (q2 == null) { q2 = QbUtil.ReadQuery(mtn.Target); // read in existing query } if (q2 != null) { if (q == null) { q = q2.Clone(); } else { q.MergeQuery(q2); } } } else if (mtn.IsDataTableType) // single data table { MetaTable mt = MetaTableCollection.Get(mtn.Target); if (mt == null) { throw new Exception("Can't get metatable " + mtn.Target); } if (q == null) { q = new Query(); } if (Lex.IsUndefined(q.Name)) { q.Name = mt.Label; } if (q.GetQueryTableByName(mt.Name) == null) // add table if not already included { qt = new QueryTable(q, mt); } } else { throw new Exception("Invalid node type: " + mtn.Type); } } List <string> cidList = new List <string>(); bool cidBasedQuery = true; // assume query is based on a list of cids string cid = CidCtl.Text; cid = CompoundId.Normalize(cid); // Retrieve data for a single Cid if (CurrentCidCheckEdit.Checked) { cidList.Add(cid); title = q.Name + " for " + RootTable.KeyMetaColumn.Label + " " + CidCtl.Text; } // Retrieve data for related structures else if (RelatedStrsCheckEdit.Checked) { // AltForms, MatchedPairs, SmallWorld, SimilarSearch, Substructure string queryStrName = QueryMolCtl.GetTemporaryStructureTag(); if (Lex.IsUndefined(queryStrName)) { queryStrName = "query structure"; } title = q.Name + " compounds related to " + queryStrName; cidBasedQuery = false; // need to do a related structure search } // Retrieve for marked Cids else if (MarkedCidsCheckEdit.Checked) { cidList = MarkedCidsList; title = q.Name + " for checked " + RootTable.KeyMetaColumn.Label + "s"; } // Retrieve for all Cids in originating query else if (AllCidsCheckEdit.Checked) { cidList = CurrentBaseQueryCidHitList; title = q.Name + " for current hitlist " + RootTable.KeyMetaColumn.Label + "s " + CidCtl.Text; } q.Name = title; // name query same as title ( + " (Q" + (++QueryCount) + ")"; // with query sequence id appended) // Remove existing criteria if requested if (!ApplyExistingCriteria.Checked) { q.ClearAllQueryColumnCriteria(); } // Modify the derived query to do either a Cid or Related-structure search q.IncludeRootTableAsNeeded(); if (IncludeStructures.Checked) { AddStructureTableToQuery(q); } if (cidBasedQuery) { ModifyQueryForCidSearch(q, cidList); } else { ModifyQueryForRelatedStructureSearch(q, QueryMolCtl); } q.SingleStepExecution = false; // Single step fails for RelatedSS search (Done in QE now) return(q); }
/// <summary> /// Validate the form and build and validate the query /// </summary> /// <returns></returns> bool ValidateFormAndBuildQuery() { try { // Retrieve data for a single Cid if (CurrentCidCheckEdit.Checked) { string cid = CidCtl.Text; cid = CompoundId.Normalize(cid); if (Lex.IsUndefined(cid) || !CompoundIdUtil.Exists(cid)) { throw new Exception("Invalid or non-existant :" + RootTable.KeyMetaColumn.Label + ": " + CidCtl.Text); } } // Retrieve data for related structures else if (RelatedStrsCheckEdit.Checked) { if (AltForms.Checked || MatchedPairs.Checked || SmallWorld.Checked || SimilarSearch.Checked || Substructure.Checked) { ; } else { throw new Exception("At least one type of structure search must be specified to find structurally-related compounds"); } } // Retrieve for marked Cids else if (MarkedCidsCheckEdit.Checked) { // OK } // Retrieve for all Cids in originating query else if (AllCidsCheckEdit.Checked) { // OK } else { throw new Exception("A compound retrieval type must be specified"); } int mi = MruListCtl.SelectedIndex; if (mi < 0 || mi >= MruListCtl.ItemCount) { throw new Exception("You must select a query or data table to use to retrieve related data"); } UpdateSelectedNodesSet(); Query q = BuildQuery(); MqlUtil.CheckTableCompatibility(q); RelatedCompoundsQuery = q; return(true); } catch (Exception ex) { MessageBoxMx.ShowError(ex.Message); CidCtl.Focus(); return(false); } }
/// <summary> /// Transform basic query to select all data for a compound number /// </summary> /// <param name="keyMt">Key metatable. If null then try to determine from key value</param> /// <param name="cn"></param> /// <returns></returns> public static Query TransformSelectAllDataQuery( Query originalQuery, QueryTable qt0, Query newQuery) { Query q2 = null; MetaTable mt; MetaColumn mc; QueryTable qt; QueryColumn qc; MetaTreeNode mtn, tn; qc = qt0.GetQueryColumnByNameWithException("root_table"); ParsedSingleCriteria psc = MqlUtil.ParseQueryColumnCriteria(qc); if (psc == null || Lex.IsUndefined(psc.Value)) { throw new UserQueryException("Root table not defined"); } string keyMtName = psc.Value; psc = MqlUtil.ParseSingleCriteria("cid " + originalQuery.KeyCriteria); if (psc == null || Lex.IsUndefined(psc.Value)) { throw new UserQueryException("Compound Id not defined"); } string cn = psc.Value; 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"); } string allTableName = keyMt.Name + "_AllData"; // see if specific all-data tree node mtn = MetaTree.GetNode(allTableName); if (mtn == null) // no special "_AllData" node, lookup in menu { foreach (MetaTreeNode parent in MetaTree.Nodes.Values) { foreach (MetaTreeNode child in parent.Nodes) { if (Lex.Eq(child.Target, keyMt.Name)) { mtn = parent; break; } } } IUserObjectTree iuot = InterfaceRefs.IUserObjectTree; if (mtn == null && keyMt.IsUserDatabaseStructureTable && iuot != null) // see if user structure table & db { int userObjId = UserObject.ParseObjectIdFromInternalName(keyMt.Name); string nodeItemId = "ANNOTATION_" + userObjId; MetaTreeNode childMtn = iuot.GetUserObjectNodeBytarget(nodeItemId); if (childMtn != null && childMtn.Parent.Type == MetaTreeNodeType.Database) { mtn = childMtn.Parent; } } } if (mtn == null) { return(null); } Query q = newQuery; for (int i1 = 0; i1 < mtn.Nodes.Count; i1++) { tn = (MetaTreeNode)mtn.Nodes[i1]; if (!tn.IsDataTableType) { continue; } mt = MetaTableCollection.Get(tn.Target); if (mt == null) { continue; } if (mt.Root.Name != keyMt.Name) { continue; // must have same root } if (mt.MultiPivot && !mt.UseSummarizedData && mt.SummarizedExists) { MetaTable mt2 = MetaTableCollection.Get(mt.Name + MetaTable.SummarySuffix); if (mt2 != null) { mt = mt2; } } //if (mt.RemapForRetrieval && mt.SummarizedExists) mt.UseSummarizedData = true; // get summarized multipivot data (not good, permanently changes the metatable) qt = new QueryTable(mt); // if (Lex.Eq(mt.Name, "all_star_pivoted") || Lex.Eq(mt.Name, "all_annotation_pivoted")) mt = mt // debug; if (qt.SelectedCount > 0) // be sure something is selected { q.AddQueryTable(qt); } } // See if a model query exists & use it or append to what we have already string fileName = ServicesDirs.ModelQueriesDir + @"\" + allTableName + ".qry"; if (!ServerFile.GetLastWriteTime(fileName).Equals(DateTime.MinValue)) // model query file exist? { try { string query2String = FileUtil.ReadFile(fileName); q2 = Query.Deserialize(query2String); q.MergeSubqueries(q2); // just use subquery } catch (Exception ex) { ex = ex; } } q.SetupQueryPagesAndViews(ResultsViewType.HtmlTable); // be sure we have a default page & HTML view // Set key criteria q.KeyCriteria = " = " + cn; // Review tables (debug) //int tCnt = q.Tables.Count; //string tls = q.TableListString; //q.Tables.RemoveRange(23, 1); //q.Tables.RemoveRange(27, q.Tables.Count - 27); //q.Tables.RemoveRange(1, 25); // Get list of any inaccessible tables & remove from query q.InaccessableData = CheckDataSourceAccessibility(q); if (q.InaccessableData != null) { foreach (string schema in q.InaccessableData.Keys) { foreach (string tName in q.InaccessableData[schema]) { qt = q.GetQueryTableByName(tName); if (qt != null && !qt.MetaTable.IsRootTable && q.Tables.Contains(qt)) { q.RemoveQueryTable(qt); } } } //ShowUnavailableDataMessage(q); q.InaccessableData = null; } UsageDao.LogEvent("QueryAllData", ""); //string mql = MqlUtil.ConvertQueryToMql(q); // debug return(q); }
/// <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> /// 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; }
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> /// 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); }