Exemplo n.º 1
0
        /// <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);
        }
Exemplo n.º 2
0
        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);
        }
Exemplo n.º 3
0
        /// <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);
        }
Exemplo n.º 4
0
        /// <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
        }
Exemplo n.º 5
0
        /// <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);
            }
        }
Exemplo n.º 6
0
        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);
            }
        }
Exemplo n.º 7
0
        /// <summary>
        /// Convert a multipivot table into a set of tables where data exists for
        /// one or more of the compound identifiers in the list.
        /// </summary>
        /// <param name="qt">Current form of query table</param>
        /// <param name="q">Query to add transformed tables to</param>
        /// <param name="ResultKeys">Keys data will be retrieved for</param>

        public override void ExpandToMultipleTables(
            QueryTable qt,
            Query q,
            List <string> resultKeys)
        {
            MetaTable  mt2;
            QueryTable qt2;
            string     sql;
            int        methodId, i1;

            int t0 = TimeOfDay.Milliseconds();

            List <string> normalizedResultKeys = new List <string>();

            for (i1 = 0; i1 < resultKeys.Count; i1++)             // copy keys to parameter array properly normalized
            {
                string key = CompoundId.NormalizeForDatabase(resultKeys[i1], qt.MetaTable);
                if (key == null)
                {
                    key = NullValue.NullNumber.ToString();                              // if fails supply a "null" numeric value
                }
                normalizedResultKeys.Add(key);
            }

            sql =             // todo: Make to work in general case (PubChem only now)
                  "select mthd_vrsn_id " +
                  "from " + "mbs_owner.mbs_pbchm_rslt" + " " +
                  "where ext_cmpnd_id_nbr in (<list>) " +
                  " and sts_id = 1 " +               // active records only
                  "group by mthd_vrsn_id";

            DbCommandMx drd = new DbCommandMx();

            drd.PrepareListReader(sql, DbType.Int32);
            drd.ExecuteListReader(normalizedResultKeys);
            if (drd.Cancelled)
            {
                // todo qe.Cancelled = true;
                drd.Dispose();
                return;
            }

            Hashtable mtHash        = new Hashtable();
            int       methodIdCount = 0;

            while (true)             // convert list of methods to set of metatable names
            {
                if (!drd.ListRead())
                {
                    break;
                }
                methodId = drd.GetInt(0);
                string mtName = "pubchem_aid_" + methodId.ToString();                 // todo: Make to work in general case (PubChem only now)
                if (QueryEngine.FilterAllDataQueriesByDatabaseContents &&
                    !MetaTableCollection.IsMetaTableInContents(mtName))
                {
                    continue;                                                                         // metatable must be in contents
                }
                if (qt.MetaTable.UseSummarizedData)
                {
                    mtName += MetaTable.SummarySuffix;
                }
                mtHash[mtName] = null;
                methodIdCount++;
            }

            drd.Dispose();
            if (drd.Cancelled)
            {
                // todo qe.Cancelled = true;
                return;
            }

            ArrayList mtList = new ArrayList();

            foreach (string mtName2 in mtHash.Keys)
            {             // put metatable labels & names into a list for sorting
                mt2 = MetaTableCollection.Get(mtName2);
                if (mt2 == null)
                {
                    continue;
                }
                if (mt2.Parent == null)
                {
                    continue;                                     // skip if no parent
                }
                mtList.Add(mt2.Label.ToLower().PadRight(64) + "\t" + mt2.Name);
            }

            mtList.Sort();

            foreach (string mts in mtList)
            {             // add new querytables/metatables to query
                string[] sa = mts.Split('\t');
                mt2 = MetaTableCollection.Get(sa[1]);
                if (mt2 == null)
                {
                    continue;
                }
                qt2 = new QueryTable(q, mt2);
                if (qt.HeaderBackgroundColor != Color.Empty)
                {
                    qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor;
                }
            }

            t0 = TimeOfDay.Milliseconds() - t0;
            return;
        }
Exemplo n.º 8
0
        /// <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;
        }
Exemplo n.º 9
0
        /// Build an unparameterized key list SQL predicate
        /// </summary>
        /// <param name="Qt"></param>
        /// <param name="keyName">Key column name qualified by table name/alias</param>

        /// <summary>
        /// Build an unparameterized key list SQL predicate
        /// </summary>
        /// <param name="eqp"></param>
        /// <param name="baseSql"></param>
        /// <param name="keyName">Key column name qualified by table name/alias</param>
        /// <param name="keyList"></param>
        /// <param name="firstKeyIdx"></param>
        /// <param name="keyCount"></param>
        /// <param name="keyCriteria">Full criteria including col name operator and list</param>
        /// <param name="keyListString">Just the list of keys</param>

        public void BuildUnparameterizedKeyListPredicate(
            ExecuteQueryParms eqp,
            ref string baseSql,
            string keyName,
            List <string> keyList,
            int firstKeyIdx,
            int keyCount,
            out string keyCriteria,
            out string keyListString)
        {
            StringBuilder sb;
            int           i1;

            QueryTable qt         = eqp.QueryTable;
            MetaTable  mt         = qt.MetaTable;
            bool       integerKey = mt.IsIntegerKey();

            List <StringBuilder> sublists = new List <StringBuilder>();

            sb = new StringBuilder();
            sublists.Add(sb);

            int sublistKeyCount = 0;             // current keys in predicate

            keyCriteria = keyListString = null;

            for (i1 = 0; i1 < keyCount; i1++)
            {
                if (sublistKeyCount >= DbCommandMx.MaxOracleInListItemCount)
                {
                    sb = new StringBuilder();
                    sublists.Add(sb);
                    sublistKeyCount = 0;
                }

                if (sb.Length > 0)
                {
                    sb.Append(",");
                }
                string key = CompoundId.NormalizeForDatabase((string)keyList[firstKeyIdx + i1], qt.MetaTable);
                if (key == null)
                {
                    key = NullValue.NullNumber.ToString();              // if fails supply a "null" numeric value
                }
                if (!integerKey || !Lex.IsInteger(key))                 // quote it if not integer column or value
                {
                    key = Lex.AddSingleQuotes(key);                     // (note: quoted integers can cause mismatches for some database systems, e.g. Denodo)
                }
                sb.Append(key);
                sublistKeyCount++;
            }

            sb = new StringBuilder();
            if (sublists.Count >= 2)
            {
                sb.Append("(");                                  // wrap in parens if multiple sublists
            }
            for (int sli = 0; sli < sublists.Count; sli++)
            {
                if (sli > 0)
                {
                    sb.Append(" or ");
                }
                sb.Append(keyName + " in (" + sublists[sli] + ")");
            }
            if (sublists.Count >= 2)
            {
                sb.Append(")");
            }
            keyCriteria = sb.ToString();

            keyListString = sublists[0].ToString();             // return just the first sublist (adjust later for larger lists)
            return;
        }
Exemplo n.º 10
0
/// <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;
        }
Exemplo n.º 11
0
        /// <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);
        }