Exemplo n.º 1
0
        /// <summary>
        /// Build the sql for the query
        /// </summary>
        /// <param name="qt"></param>
        /// <returns></returns>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            string sql = base.BuildSql(eqp);

            return(sql);
        }
Exemplo n.º 2
0
        /// <summary>
        /// PrepareQuery
        /// </summary>
        /// <param name="eqp"></param>
        /// <returns></returns>
        public override string PrepareQuery(
            ExecuteQueryParms eqp)
        {
            Sql = BuildSql(eqp);

            return(Sql);
        }
Exemplo n.º 3
0
        /// <summary>
        /// Build the sql for the query
        /// </summary>
        /// <param name="eqp"></param>
        /// <returns></returns>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;             // save for possible later reference

            return("NoSql");       // NonSqlMetaBroker placeholder
        }
Exemplo n.º 4
0
/// <summary>
/// Execute the query
/// </summary>
/// <param name="eqp"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            base.ExecuteQuery(eqp);
            ActivityClass = new ActivityClass();
            return;
        }
Exemplo n.º 5
0
        /// <summary>
        /// Execute query for new set of keys
        /// </summary>
        /// <param name="eqp"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            CidToStructureDict = null;
            KeyListPos         = -1;
            MapPos             = -1;
            return;
        }
Exemplo n.º 6
0
        /// <summary>
        /// Build the sql for a query
        /// </summary>
        /// <param name="eqp">ExecuteQueryParms</param>
        /// <returns>Sql for the query</returns>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            string     sql = "";
            MetaColumn mc;

            List <MetaBrokerType> MbTypes =            // metabrokers that can provide unpivoted assay data
                                            new List <MetaBrokerType>();

            MbTypes.Add(MetaBrokerType.Assay);

            Eqp   = eqp;
            Qt    = eqp.QueryTable;
            Exprs = FromClause = OrderBy = "";             // outer sql elements
            KeyMc = Mt.KeyMetaColumn;

            SelectList = new List <MetaColumn>();            // build list of selected metacolumns
            foreach (QueryColumn qc in Qt.QueryColumns)
            {
                mc = qc.MetaColumn;
                if (qc.Selected)
                {
                    SelectList.Add(mc);
                }
            }

            foreach (MetaBrokerType mbt in MbTypes)             // get Sql for each broker and union together
            {
                IMetaBroker mb = MetaBrokerUtil.Create(mbt);
                if (mb == null)
                {
                    throw new Exception("Unrecognized Metabroker: " + mbt);
                }
                string sql2 = mb.BuildUnpivotedAssayResultsSql(eqp);

                //if (actBinAQc != null)
                //  sql2 = Lex.Replace(sql2, "null activity_bin", ActivityBinSqlExpression + " activity_bin");

                if (Lex.IsDefined(sql))
                {
                    sql += " union all ";
                }
                sql += "/*** MetaBrokerType." + mbt.ToString() + " ***/ " + sql2;
            }

            sql = " select * from ( " + sql + " ) ";
            if (Qt.Alias != "")
            {
                sql += " " + Qt.Alias;
            }
            if (eqp.CallerSuppliedCriteria != "")
            {
                sql += " where " + eqp.CallerSuppliedCriteria;
            }

            return(sql);
        }
Exemplo n.º 7
0
        /// <summary>
        /// Execute query for new set of keys
        /// </summary>
        /// <param name="eqp"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;             // save for possible later reference

            MetaTable mt = eqp?.QueryTable?.MetaTable;

            GSP(mt).ExecuteQuery(eqp);
            return;
        }
Exemplo n.º 8
0
        /// <summary>
        /// Prepare for execution
        /// </summary>
        /// <param name="eqp"></param>
        /// <returns></returns>

        public override string PrepareQuery(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;             // save for possible later reference
            Qt  = eqp.QueryTable;

            MetaTable mt = eqp?.QueryTable?.MetaTable;

            return(GSP(mt).PrepareQuery(eqp));
        }
Exemplo n.º 9
0
/// <summary>
/// Build sql and prepare query
/// </summary>
/// <param name="eqp">ExecuteQueryParms</param>
/// <returns>Sql for the query</returns>

        public override string PrepareQuery(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;
            Qt  = eqp.QueryTable;

            Sql = BuildSql(eqp);

            OrderBy = "";

            return(Sql);
        }
Exemplo n.º 10
0
        /// <summary>
        /// Execute query
        /// </summary>
        /// <param name="eqp"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;
            Qt  = eqp.QueryTable;
            MetaTable mt = Qt.MetaTable;

            if (string.IsNullOrEmpty(Sql))
            {
                return;                                        // nothing to do here
            }
            base.ExecuteQuery(eqp);
        }
Exemplo n.º 11
0
        /// <summary>
        /// Build the sql for the query
        /// </summary>
        /// <param name="eqp"></param>
        /// <returns></returns>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            string sql = "";

            Eqp = eqp;
            if (Eqp == null)
            {
                DebugMx.DataException("Eqp parameter is null");
            }

            Qt = eqp.QueryTable;
            if (Qt == null)
            {
                DebugMx.DataException("Eqp.QueryTable is null");
            }

            MetaTable mt = Qt.MetaTable;

            if (mt == null)
            {
                DebugMx.DataException("Metatable not defined for CalcField Query Table");
            }

            Query q = eqp.Qe.Query;

            QueryTableData[] qtd = eqp.Qe.Qtd;             // query table data
            QueryColumn      qc;
            MetaColumn       mc;

            KeyMc = mt.KeyMetaColumn;
            if (KeyMc == null)
            {
                throw new Exception("Key (compound number) column not found for MetaTable " + mt.Name);
            }
            KeyQci         = Qt.GetQueryColumnIndexByName(KeyMc.Name);
            KeyQc          = Qt.QueryColumns[KeyQci];
            KeyQc.Selected = true;                     // be sure key is selected

            Qt.MetaTable.KeyMetaColumn.ColumnMap = ""; // reset key column mapping

            Query q2 = InitializeSubQuery(Qt);

            foreach (QueryTable qtx in q2.Tables)
            {
                // todo...
            }

            return(sql);
        }
Exemplo n.º 12
0
/// <summary>
/// Generate SQL
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            Eqp = eqp;
            Qt  = eqp.QueryTable;
            MetaTable mt = Qt.MetaTable;

            KeyMc = mt.KeyMetaColumn;

            string sql = base.BuildSql(eqp);

            sql = FormatActivityBinExpression(sql);

            return(sql);
        }
Exemplo n.º 13
0
		/// <summary>
		/// Prepare for execution
		/// </summary>
		/// <param name="eqp"></param>
		/// <returns></returns>

		public override string PrepareQuery(
		ExecuteQueryParms eqp)
		{
			Eqp = eqp;
			Qt = eqp.QueryTable;

			//PrepareForDecompose(Qt, eqp.Qe.Query); // setup again for this broker instance

			SelectList = new List<MetaColumn>();
			foreach (QueryColumn qc in Qt.QueryColumns)
			{
				if (qc.Selected || qc.IsKey)
					SelectList.Add(qc.MetaColumn);
			}
			return ""; // No SQL returned
		}
Exemplo n.º 14
0
        /// <summary>
        /// Execute Related Structure Search
        /// </summary>
        /// <param name="eqp"></param>

        void ExecuteRelatedStructureSearch(
            ExecuteQueryParms eqp)
        {
            if (RSS == null)
            {
                RSS = new RelatedStructureSearch();

                Query       q  = StructCriteriaQc.QueryTable.Query;
                QueryTable  qt = StructCriteriaQc.QueryTable;
                MetaTable   mt = qt.MetaTable;
                QueryColumn qc = StructCriteriaQc;
                MetaColumn  mc = qc.MetaColumn;

                RSS.QueryMtName = mt.Name;

                RSS.QueryChimeString = Pssc.Molecule.GetChimeString();                 // if no cid in options use passed structure

                RSS.IncludeQueryStructure = true;

                RSS.SearchCorp   = (Lex.Contains(mt.Name, "Corp"));
                RSS.SearchChembl = (Lex.Contains(mt.Name, "ChEMBL"));

                RSS.SearchFSS        = SST.IsFull(Pssc.SearchTypeUnion);
                RSS.SearchMmp        = SST.IsMmp(Pssc.SearchTypeUnion);
                RSS.SearchSmallWorld = SST.IsSw(Pssc.SearchTypeUnion);
                RSS.SearchSim        = SST.IsSim(Pssc.SearchTypeUnion);
                RSS.SearchSSS        = SST.IsSSS(Pssc.SearchTypeUnion);

                if (RSS.SearchMmp && Lex.IsUndefined(RSS.QueryCid))                 // try to get a cid from the structure if we are searching MMP and don't already have one
                {
                    int corpId = Pssc.Molecule.GetCompoundId();
                    if (corpId > 0)
                    {
                        RSS.QueryCid = corpId.ToString();
                    }
                }

                RSS.KeysToExclude = q.KeysToExclude;

                RSS.ExecuteSearch();
            }

            BuildRelatedStructureSearchQueryEngineRows(eqp);

            return;
        }
Exemplo n.º 15
0
/// <summary>
/// Return true if associated query is a valid non-Sql search
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>

        public static bool IsNonSqlStructureSearchQueryTable(
            ExecuteQueryParms eqp)
        {
            if (Lex.IsDefined(eqp.CallerSuppliedCriteria))
            {
                return(false);                                                       // return false if retrieval step of query
            }
            QueryColumn strQc = eqp.QueryTable.FirstStructureQueryColumn;

            if (strQc == null || strQc.Criteria == "")
            {
                return(false);
            }

            bool result = CanExecuteInternalStructureSearch(strQc);

            return(result);
        }
Exemplo n.º 16
0
/// <summary>
/// Build the set of results rows based on the current key set
/// </summary>
/// <param name="eqp"></param>

        void BuildRelatedStructureSearchQueryEngineRows(
            ExecuteQueryParms eqp)
        {
            HashSet <string> keySet = new HashSet <string>();

            if (eqp.SearchKeySubset != null)             // subsetting
            {
                foreach (string cid0 in eqp.SearchKeySubset)
                {
                    keySet.Add(cid0);
                }
            }

            List <StructSearchMatch> recs = RSS.AllMatches; // start with full match list

            Results = new List <object[]>();                // destination results list (may or may not be subsetted)

            for (int ri = 0; ri < recs.Count; ri++)
            {
                StructSearchMatch m = recs[ri];

                if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid))
                {
                    if (!keySet.Contains(m.SrcCid))
                    {
                        continue;
                    }
                }

                if (RestrictedDatabaseView.KeyIsRetricted(m.SrcCid))
                {
                    continue;
                }

                object[] voa = new object[2];
                voa[0] = m.SrcCid;
                voa[1] = m;
                Results.Add(voa);
            }

            CursorPos = -1;
            return;
        }
Exemplo n.º 17
0
        /// <summary>
        /// Build a single-parameter in-list by converting a key list string into a table using string operations and the Dual table
        /// (From Tom Kyte: Varying in lists..., 2-June-2006)
        /// (Note that this only works for strings up to 4000 chars and not currently used.)
        /// </summary>
        /// <param name="eqp"></param>
        /// <param name="keyName"></param>
        /// <param name="keyList"></param>
        /// <param name="firstKeyIdx"></param>
        /// <param name="keyCount"></param>
        /// <returns></returns>

        public string BuildSingleParameterOracleKeyListPredicate(
            ExecuteQueryParms eqp,
            string keyName,
            List <string> keyList,
            int firstKeyIdx,
            int keyCount,
            out string keyListString)
        {
            string sql =
                keyName + @" in (
					select
					trim( substr (txt,
								instr (txt, ',', 1, level  ) + 1,
								instr (txt, ',', 1, level+1)
										- instr (txt, ',', 1, level) -1 ) )
						as token
					from (select ','||:0||',' txt
									from dual)
					connect by level <=
						length(:0)-length(replace(:0,',','')) + 1
					)"                    ;

            StringBuilder sb = new StringBuilder();

            for (int i1 = 0; i1 < keyCount; i1++)
            {
                string sKey = keyList[firstKeyIdx + i1];
                if (Lex.IsNullOrEmpty(sKey))
                {
                    continue;
                }
                if (sb.Length > 0)
                {
                    sb.Append(",");
                }
                sb.Append(sKey);
            }

            keyListString = sb.ToString();

            return(sql);
        }
Exemplo n.º 18
0
        /// <summary>
        /// Build the set of results rows based on the current key set
        /// </summary>
        /// <returns></returns>

        void BuildECFP4QueryEngineRows(
            ExecuteQueryParms eqp)
        {
            HashSet <string> keySet = new HashSet <string>();

            if (eqp.SearchKeySubset != null)             // subsetting
            {
                foreach (string cid0 in eqp.SearchKeySubset)
                {
                    keySet.Add(cid0);
                }
            }

            List <StructSearchMatch> recs = Ecfp4Dao.MatchList; // start with full match list

            Results = new List <object[]>();                    // destination results list (may or may not be subsetted)

            for (int ri = 0; ri < recs.Count; ri++)
            {
                StructSearchMatch m = recs[ri];

                if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.SrcCid))
                {
                    if (!keySet.Contains(m.SrcCid))
                    {
                        continue;
                    }
                }

                object[] voa = new object[VoLength];
                voa[0] = m.SrcCid;
                if (VoLength > 1)
                {
                    voa[1] = m.MatchScore;
                }

                Results.Add(voa);
            }

            CursorPos = -1;
            return;
        }
Exemplo n.º 19
0
        /// <summary>
        /// Execute SmallWorld search
        /// </summary>
        /// <param name="eqp"></param>

        void ExecuteSmallWorldSearch(
            ExecuteQueryParms eqp)
        {
            if (!SmallWorldDao.IsSmallWorldAvailable())
            {
                throw new UserQueryException("SmallWorld searching is not currently available.");
            }

            Query      q  = eqp.QueryTable.Query;
            QueryTable qt = eqp.QueryTable;

            SmallWorldPredefinedParameters swp = Pssc.SmallWorldParameters;
            string smiles = Pssc.Molecule.GetSmilesString();

            swp.Smiles = smiles;
            //swp.Database = "Corp";

            LastSwNonSqlBroker = null;     // debug - force new broker

            if (SwDao == null)             // if Dao not allocated then allocate & execute the search
            {
                if (LastSwNonSqlBroker != null && LastSwNonSqlBroker.Pssc != null && LastSwNonSqlBroker.Pssc.SmallWorldParameters != null &&
                    LastSwNonSqlBroker.Pssc.SmallWorldParameters.Serialize() == swp.Serialize())
                {
                    SwDao = LastSwNonSqlBroker.SwDao;                     // get rows from previous search
                    SwDao.BuildSwToQeColumnMap(SelectList);               // reset the column map for this broker instance
                }

                else
                {
                    SwDao = new SmallWorldDao();
                    SwDao.KeysToExclude = q.KeysToExclude;
                    SwDao.ExecuteSearch(swp);
                    SwDao.BuildSwToQeColumnMap(SelectList);        //
                    LastSwNonSqlBroker = this;                     // remember for future use
                }
            }

            BuildSmallWorldQueryEngineRows(eqp);             // build the QE row set (filtered) from the SW rowset

            return;
        }
Exemplo n.º 20
0
        /// <summary>
        /// Build in list predicate using temporary database table
        /// </summary>
        /// <param name="eqp"></param>
        /// <param name="baseSql"></param>
        /// <param name="keyName"></param>
        /// <param name="keyList"></param>
        /// <param name="firstKeyIdx"></param>
        /// <param name="keyCount"></param>
        /// <returns></returns>

        public string BuildTempDbTableKeyListPredicate(
            ExecuteQueryParms eqp,
            ref string baseSql,
            string keyName,
            List <string> keyList,
            int firstKeyIdx,
            int keyCount)
        {
            bool intKey = true;

            if (QueryEngine.AllowNetezzaUse && eqp.AllowNetezzaUse)             // && keyCount > MaxNetzzaInListItemCount)
            {
                return(DbCommandMx.BuildNetezzaTempDbTableKeyListPredicate(keyName, keyList, firstKeyIdx, keyCount));
            }

            else             // if (keyCount > MaxOracleInListItemCount)
            {
                return(DbCommandMx.BuildOracleTempDbTableKeyListPredicate(ref baseSql, keyName, intKey, keyList, firstKeyIdx, keyCount));
            }
        }
Exemplo n.º 21
0
        /// <summary>
        /// Execute CDK ECFP4 search
        /// </summary>
        /// <param name="eqp"></param>

        void ExecuteECFP4Search(
            ExecuteQueryParms eqp)
        {
            CdkSimSearchMx dao = Ecfp4Dao;

            if (!CdkSimSearchMx.IsSearchingAvailable())
            {
                throw new UserQueryException("ECFP4 searching is not currently available.");
            }

            Query      q  = eqp.QueryTable.Query;
            QueryTable qt = eqp.QueryTable;
            MetaTable  mt = qt.MetaTable;

            if (dao == null)             // if Dao not allocated then allocate Dao & execute the search
            {
                dao = Ecfp4Dao = new CdkSimSearchMx();
                dao.KeysToExclude = q.KeysToExclude;

                if (eqp.SearchKeySubset != null)
                {
                    dao.SearchKeySubset = new HashSet <string>(eqp.SearchKeySubset);
                }

                string databases = mt.Name + "," + mt.Label;                  // quick/dirty DB Id

                List <StructSearchMatch> hits = dao.ExecuteSearch(Pssc.Molecule.GetMolfileString(), databases, FingerprintType.Circular, Pssc.MinimumSimilarity, Pssc.MaxSimHits);
                VoLength = 1;
                if (qt.GetSelectedMolsimQueryColumn() != null)
                {
                    VoLength = 2;
                }
            }

            BuildECFP4QueryEngineRows(eqp);             // build the QE row set (filtered)

            return;
        }
Exemplo n.º 22
0
/// <summary>
/// Execute non-Oracle query
/// </summary>
/// <param name="eqp"></param>

        public override void ExecuteQuery(
            ExecuteQueryParms eqp)
        {
            QueryTable qt = Eqp.QueryTable;
            MetaTable  mt = qt.MetaTable;

            if (Pssc.SearchType == StructureSearchType.MolSim && Pssc.SimilarityType == SimilaritySearchType.ECFP4)
            {
                ExecuteECFP4Search(eqp);
            }

            else if (Pssc.SearchType == StructureSearchType.SmallWorld)
            {
                ExecuteSmallWorldSearch(eqp);
            }

            else if (Pssc.SearchType == StructureSearchType.Related)
            {
                ExecuteRelatedStructureSearch(eqp);
            }

            else if (mt.MetaBrokerType == MetaBrokerType.Annotation)
            {
                ExecuteAnnotationStructureSearch();
            }

            else if (Lex.StartsWith(mt.Name, "USERDATABASE_STRUCTURE_"))
            {
                ExecuteUserDatabaseStructureSearch();
            }

            else
            {
                ExecuteInternalOracleStructureColumnSearch();              // internal search of structures stored in general oracle table column
            }
            return;
        }
Exemplo n.º 23
0
        /// <summary>
        /// Build the set of results rows based on the current key set
        /// </summary>
        /// <returns></returns>

        void BuildSmallWorldQueryEngineRows(
            ExecuteQueryParms eqp)
        {
            HashSet <string> keySet = new HashSet <string>();

            if (eqp.SearchKeySubset != null)             // subsetting
            {
                foreach (string cid0 in eqp.SearchKeySubset)
                {
                    keySet.Add(cid0);
                }
            }

            List <SmallWorldMatch> recs = SwDao.MatchList; // start with full match list

            Results = new List <object[]>();               // destination results list (may or may not be subsetted)

            for (int ri = 0; ri < recs.Count; ri++)
            {
                SmallWorldMatch m = recs[ri];

                if (keySet != null && keySet.Count > 0 && Lex.IsDefined(m.Cid))
                {
                    if (!keySet.Contains(m.Cid))
                    {
                        continue;
                    }
                }

                object[] voa = m.BuildVoa(SwDao, ri);
                Results.Add(voa);
            }

            CursorPos = -1;
            return;
        }
Exemplo n.º 24
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.º 25
0
        /// <summary>
        /// Build and save SQL for use in Spotfire information link
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>

        public static int SaveSpotfireSql(
            string sqlStmtName,
            Query query)
        {
            DbConnectionMx conn = null;

            string qtSql = "", sql, expr;
            string keys = null;
            string keyColName = "";
            string t1KeyColExpr = "", keyColExpr;

            string rootDataSource = "DEV857";
            string rootSchema     = "MBS_OWNER";

            //if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries");

            Query q = query.Clone();

            // Clean up query before generating SQL

            q.KeyCriteria = q.KeyCriteriaDisplay = "";

            List <QueryTable> qtToRemove = new List <QueryTable>();

            foreach (QueryTable qt0 in q.Tables)
            {
                if (qt0.SelectedCount <= 1 ||
                    !qt0.MetaTable.RetrievesDataFromQueryEngine)
                {
                    qtToRemove.Add(qt0);
                    continue;
                }

                foreach (QueryColumn qc0 in qt0.QueryColumns)                 // clear any criteria
                {
                    if (Lex.IsDefined(qc0.Criteria))
                    {
                        qc0.Criteria = qc0.CriteriaDisplay = "";
                    }
                }
            }

            foreach (QueryTable qt0 in qtToRemove)
            {
                q.RemoveQueryTable(qt0);
            }

            string selectList   = "";         // top-level list of selected columns
            string fromList     = "";         // sql for each QueryTable
            string joinCriteria = "";         // join criteria between QueryTables
            int    remapCount   = 0;

            q.AssignUndefinedAliases();

            q.MarkDuplicateNamesAndLabels();

            for (int ti = 0; ti < q.Tables.Count; ti++)
            {
                QueryTable qt = q.Tables[ti];
                if (ti == 0)
                {
                    keyColName = qt.MetaTable.KeyMetaColumn.Name;
                }

                QueryEngine       qe  = new QueryEngine();
                ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt);
                eqp.ReturnQNsInFullDetail = false;

                qtSql = qe.BuildSqlForSingleTable(eqp);
                qtSql = Lex.Replace(qtSql, "/*+ hint */ ", "");

                conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema);                 // convert SQL to use dblinks from root source/schema
                if (conn == null)
                {
                    throw new Exception("Connection not found for: " + rootDataSource);
                }

                // Recast numeric cols that are integers as integers for Spotfire

                List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn);

                string qtSelectList = "";
                remapCount = 0;                 // number of cols remapped
                int sci = -1;

                foreach (QueryColumn qc in qt.QueryColumns)
                {
                    if (!qc.Selected)
                    {
                        continue;
                    }
                    sci++;                     // synch with cmdList

                    MetaColumn mc = qc.MetaColumn;

                    string mcName = mc.Name;
                    if (q.Tables.Count > 1)                     // if more than one table qualify by table name
                    {
                        mcName = qt.Alias + "." + mcName;
                    }

                    string colName = qc.UniqueName;

                    //if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug
                    //if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug

                    if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId))
                    {
                        DbColumnMetadata md = cmdList[sci];
                        expr = "cast (" + mcName + " as integer) " + colName;                          //  integer same as number(22,0)--- " as number(38, 0)) " + expr;
                    }

                    else if (mcName != colName)
                    {
                        expr = mcName + " " + colName;
                        remapCount++;
                    }

                    else
                    {
                        expr = mc.Name;
                    }

                    if (qtSelectList != "")
                    {
                        qtSelectList += ", ";
                    }
                    qtSelectList += expr;
                }

                if (selectList != "")
                {
                    selectList += ", ";
                }
                selectList += qtSelectList;

                if (fromList != "")
                {
                    fromList += ", ";
                }
                fromList += "(" + qtSql + ") " + qt.Alias;

                keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name;

                if (ti == 0)
                {
                    t1KeyColExpr = keyColExpr;
                }

                else
                {
                    if (joinCriteria != "")
                    {
                        joinCriteria += " and ";
                    }
                    joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr;
                }
            }

            selectList += " ";             // be sure last col name in list is delimited with a space

            if (q.Tables.Count == 1 && remapCount == 0)
            {
                sql = qtSql; // simple single table with no remapping of cols
            }
            else             // combine list of elements
            {
                sql =
                    "select " + selectList +
                    " from " + fromList;

                if (joinCriteria != "")
                {
                    sql += " where " + joinCriteria;
                }

                sql = "select * from (" + sql + ")";                  // encapsulate the SQL
            }

            int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName);

            return(v2);
        }
Exemplo n.º 26
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.º 27
0
		/// <summary>
		/// Execute query
		/// </summary>
		/// <param name="eqp"></param>

		public override void ExecuteQuery(
				ExecuteQueryParms eqp)
		{
			MetaTable mt;
			MetaColumn mc;
			Query q;
			QueryTable qt;
			QueryColumn qc;
			ResultsPage rp;
			ResultsViewProps view;
			CompoundStructureActivityData cd1, cd2;

			bool smallerIsbetter;
			double r1, r2, r3, r4;
			int di, di2, pdi, pdi2, i3;
			string tok;

			qt = eqp.QueryTable;
			qc = qt.GetQueryColumnByNameWithException(SasMapParms.ParametersMetaColumnName);
			AssertMx.IsDefined(qc.Criteria, qc.Label + " criteria not defined");

			if (Lex.Eq(qc.Criteria, LastCriteriaString)) // if same criteria as last time then use existing data
			{
				VoListPos = -1; // init list position
				return;
			}

			VoList = new List<object[]>();
			VoListPos = -1; // init list position

			LastCriteriaString = qc.Criteria;
			ParsedSingleCriteria psc = ParsedSingleCriteria.Parse(qc);
			SMP = SasMapParms.Deserialize(psc.Value);

			mc = SMP.EndpointMc;
			smallerIsbetter = mc.MultiPoint;

			List<CompoundStructureActivityData> ds1 = ReadData(SMP); // read in the data to analyze
			if (ds1 == null || ds1.Count == 0) return; //  throw new QueryException("No data retrieved");

			List<CompoundStructureActivityData> ds2 = ds1; // just one set for now

			// Calculate difference or ratio coefficents for each pair

			List<PairData> pd = new List<PairData>();
			int minCoef = -1; // index of minimum coefficent selected so far
			double molFactor = AssayAttributes.GetMolarConcFactor(SMP.EndpointMc);

			for (di = 0; di < ds1.Count; di++)
			{ // process all compounds in 1st set
				//		if (ds1[di].Nearest == 0) continue; // any data?
				if (ds2 == ds1) di2 = di + 1; // only do lower rt diagonal if one dataset
				else di2 = 0; // must do all compares, check for dups later

				for ( /* start at di2 */; di2 < ds2.Count; di2++)
				{
					//			if (ds2[di2].Nearest == 0) continue; // any data?
					if (ds1[di].Cid == ds2[di2].Cid) continue; // avoid self compare

					double sim = // similarity
					 CalculateSimilarity(ds1[di], ds2[di2]);
					//if (sim==1.0 && !stereo) // eliminate stereo pairs if requested
					// continue; // a more careful check may be needed

					if (sim < SMP.MinimumSimilarity) continue; // below cutoff value?

					double denom = 1 - sim; // denominator is 1 - sim
					if (denom == 0) denom = .00000000001f; // avoid divide by zero

					double actChange = 0;

					if (smallerIsbetter && ds1[di].Activity < ds2[di2].Activity)
					{
						cd1 = ds1[di];
						cd2 = ds2[di2];
					}

					else
					{
						cd1 = ds2[di2];
						cd2 = ds1[di];
					}

					double a1 = cd1.Activity;
					double a2 = cd2.Activity;

					if (a1 == NullValue.NullNumber || a2 == NullValue.NullNumber)
						actChange = 0;

					else switch (SMP.ActDiffCalcType)
						{
							case ActDiffCalcType.SimpleDifference: // activity difference
								{
									actChange = a1 - a2;
									break;
								}

							case ActDiffCalcType.NegativeLog:
								{
									actChange = -Math.Log10(a1) - -Math.Log10(a2);
									break;
								}

							case ActDiffCalcType.MolarNegativeLog:
								{
									actChange = (-Math.Log10(a1 * molFactor)) - (-Math.Log10(a2 * molFactor));
									break;
								}

							case ActDiffCalcType.Ratio: // activity ratio
								{
									r1 = a1;
									if (r1 == 0) r1 = .00000000001f;
									r2 = a2;
									if (r2 == 0) r2 = .00000000001f;
									r3 = r1 / r2;
									r4 = r2 / r1;

									actChange = r3;
									if (SMP.UseAbsoluteValue && r4 > r3) // take the max value
										actChange = r4;

									break;
								}

							case ActDiffCalcType.Advanced:
								{
									throw new InvalidOperationException("SarMapCalcType.Advanced");
								}

							default:
								throw new InvalidOperationException("SarMapCalcType: " + (int)SMP.ActDiffCalcType);
						}

					if (SMP.UseAbsoluteValue && SMP.ActDiffCalcType != ActDiffCalcType.Ratio)
						actChange = Math.Abs(actChange);

					double coef = actChange / denom;

					if (pd.Count < SMP.MaxPairCount)  // just add this pair to end
					{
						pdi = pd.Count;
						pd.Add(new PairData());
					}

					else
					{ // see if this value is greater than anything in list
						if (minCoef < 0)
						{ // find element with minimum coef
							minCoef = 0;
							for (i3 = 1; i3 < pd.Count; i3++)
							{
								if (pd[i3].Coef < pd[minCoef].Coef)
									minCoef = i3;
							}
						}
						if (coef <= pd[minCoef].Coef) continue; // if this one better?

						//if (ds1 != ds2)
						//{ // be sure not a duplicate of what we have in list
						//	for (i3 = 0; i3 < pd.Count; i3++)
						//	{ // check for pair in list already
						//		if ((di == pd[i3].CD1 && di2 == pd[i3].CD2) ||
						//				(di == pd[i3].CD2 && di2 == pd[i3].CD1)) break;
						//	}
						//	if (i3 < pd.Count) continue; // continue to next pair if found
						//}

						pdi = minCoef; // replace this item
						minCoef = -1; // reset to get new minimum next time
					}

					// Save data for the pair

					pd[pdi].CD1 = cd1;
					pd[pdi].CD2 = cd2;
					pd[pdi].Sim = sim;
					pd[pdi].ActChange = actChange;
					pd[pdi].Coef = coef;
				}
			}

			// Build the list of pair Vos

			int voLen = qt.SetSimpleVoPositions();

			PairData pdItem;
			for (pdi = 1; pdi < pd.Count; pdi++) // sort from max to min coef value
			{
				pdItem = pd[pdi];
				for (pdi2 = pdi - 1; pdi2 >= 0; pdi2--)
				{
					if (pdItem.Coef < pd[pdi2].Coef) break;
					pd[pdi2 + 1] = pd[pdi2];
				}
				pd[pdi2 + 1] = pdItem;
			}

			for (pdi = 0; pdi < pd.Count; pdi++)
			{
				pdItem = pd[pdi];
				cd1 = pdItem.CD1;
				cd2 = pdItem.CD2;

				object[] vo = new object[voLen];

				VoArray.SetVo(qt, "PAIR_ID", vo, new NumberMx(pdi + 1));
				VoArray.SetVo(qt, "COMPOUND1", vo, new StringMx(cd1.Cid));
				VoArray.SetVo(qt, "STRUCTURE1", vo, cd1.Structure);
				VoArray.SetVo(qt, "ACTIVITY1", vo, new NumberMx(cd1.Activity));

				VoArray.SetVo(qt, "COMPOUND2", vo, new StringMx(cd2.Cid));
				VoArray.SetVo(qt, "STRUCTURE2", vo, cd2.Structure);
				VoArray.SetVo(qt, "ACTIVITY2", vo, new NumberMx(cd2.Activity));

				VoArray.SetVo(qt, "SIMILARITY", vo, new NumberMx(pdItem.Sim));
				VoArray.SetVo(qt, "ACTIVITY_DIFF", vo, new NumberMx(pdItem.ActChange));
				VoArray.SetVo(qt, "ACT_SIM_COEF", vo, new NumberMx(pdItem.Coef));

				VoList.Add(vo);
			}

			VoListPos = -1; // init list position
			return;
		}
Exemplo n.º 28
0
        /// <summary>
        /// Clone ExecuteQueryParms
        /// </summary>
        /// <returns></returns>

        public ExecuteQueryParms Clone()
        {
            ExecuteQueryParms eqp = (ExecuteQueryParms)this.MemberwiseClone();

            return(eqp);
        }
Exemplo n.º 29
0
        /// <summary>
        /// Build the sql for a query doing preparation for separate fetch of data.
        /// </summary>
        /// <param name="parms"></param>

        public override string PrepareQuery(
            ExecuteQueryParms eqp)
        {
            MultiTablePivotBrokerTypeData mpd;             // multipivot data for this broker type
            List <GenericMetaBroker>      mbList;
            string tableFilterKey;

            Eqp = eqp;
            Qt  = eqp.QueryTable;
            MetaTable mt = Qt.MetaTable;

            if (!eqp.ReturnQNsInFullDetail || // no multipivot if part of calc field
                eqp.Qe == null)               // need to be able to access queryengine info
            //!QueryEngine.AllowMultiTablePivot) // is multipivot even allowed
            {
                return(base.PrepareQuery(eqp));
            }

            PivotedColCount = 0;
            foreach (QueryColumn qc1 in Qt.QueryColumns)
            {             // if any criteria then pivot individually rather than via multipivot
                if (qc1.Criteria != "" && !qc1.IsKey)
                {
                    return(base.PrepareQuery(eqp));
                }
                if (qc1.MetaColumn.PivotValues != null)
                {
                    PivotedColCount++;
                }
            }

            if (PivotedColCount == 0)
            {
                return(base.PrepareQuery(eqp));                                  // must have at least one column to pivot
            }
// Store pivot info for queryTable

            PivotInCode = true;
            BuildSql(eqp);                                                             // setup SelectList (don't really need sql)

            MpGroupKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql

            if (!QueryEngine.AllowMultiTablePivot)
            {
                MpGroupKey += "_" + Qt.MetaTable.Name;
            }

            if (eqp.Qe.MetaBrokerStateInfo == null)
            {
                eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>();
            }

            Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;

            if (!mbsi.ContainsKey(MpGroupKey))
            {             // create entry for data source
                mpd = new MultiTablePivotBrokerTypeData();
                mbsi[MpGroupKey]   = mpd;
                mpd.FirstTableName = mt.Name;
                mpd.TableFilterValuesToMetaTableDict = new Dictionary <string, MetaTable>();
                mpd.TableCodeCsvList = new StringBuilder();
                mpd.TableCodeDict    = new Dictionary <string, MpdResultTypeData>();
                mpd.MbInstances      = new Dictionary <string, object>();
            }
            else
            {
                mpd = (MultiTablePivotBrokerTypeData)mbsi[MpGroupKey];        // get existing entry
            }
            string pms = Csv.JoinCsvString(mt.TableFilterValues);             // values to identify this metatable

            mpd.AddMetaBroker(Qt.MetaTable.Name, this);

            // Store TableFilter values associated with metatable

            if (mt.TableFilterValues.Count == 1)
            {             // handle case with list of values allowed for single filter column (e.g. pivot values)
                List <string> sl = Csv.SplitCsvString(mt.TableFilterValues[0]);
                foreach (string s in sl)
                {
                    mpd.TableFilterValuesToMetaTableDict[s.ToLower()] = mt;
                }
            }

            else
            {
                tableFilterKey = "";
                foreach (string s in mt.TableFilterValues)
                {
                    if (tableFilterKey != "")
                    {
                        tableFilterKey += ", ";
                    }
                    tableFilterKey += s;
                }
                mpd.TableFilterValuesToMetaTableDict[tableFilterKey.ToLower()] = mt;
            }

#if false                                                     // todo: store codes for quick row identification
            if (!mpd.CodeHash.ContainsKey(Qt.MetaTable.Code)) // include assay code
            {
                mpd.CodeHash[pms] = null;                     // add key to hash list
                if (mpd.Codes.Length > 0)
                {
                    mpd.Codes.Append(",");
                }
                mpd.Codes.Append(pms);
            }
#endif

            return(null);            // no sql generated here
        }
Exemplo n.º 30
0
        /// <summary>
        /// Generate the sql for retrieval from a querytable
        /// If this query returns qualified numbers (i.e. is not search part of query or part of calc field)
        /// then don't retrieve any data other than the compound id & group id.
        /// </summary>
        /// <param name="qt"></param>

        public override string BuildSql(
            ExecuteQueryParms eqp)
        {
            List <MetaColumn>  mcl;
            List <QueryColumn> qcl;
            QueryColumn        qc;
            MetaTable          mt;
            MetaColumn         mc;
            Hashtable          resultKeys;
            string             tableAlias, txt, tok;
            string             pivotExprs, pivotTables = "", pivotCriteria;
            int ci, qci, i1;

            Eqp = eqp;
            Qt  = eqp.QueryTable;

            Exprs = FromClause = OrderBy = "";             // outer sql elements

            qcl        = Qt.QueryColumns;
            mt         = Qt.MetaTable;
            mcl        = mt.MetaColumns;
            KeyMc      = mt.KeyMetaColumn;
            resultKeys = new Hashtable();
            SelectList = new List <MetaColumn>();               // list of selected metacolumns

            int selectCnt   = 0;                                // counting key
            int criteriaCnt = 0;                                // not counting key

            QueryColumn firstCriteriaPivotedCol = null;         // first pivoted column with criteria
            QueryColumn firstSelectedPivotedCol = null;         // first pivoted column that's selected

            StringBuilder pivotValueList = new StringBuilder(); // build list of selected result codes

            for (qci = 0; qci < qcl.Count; qci++)
            {
                qc = qcl[qci];
                mc = qc.MetaColumn;

                if (mc.PivotValues != null && mc.PivotValues.Count == 1 && (qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted))
                {
                    if (pivotValueList.Length > 0)
                    {
                        pivotValueList.Append(",");
                    }
                    pivotValueList.Append(mc.PivotValues[0]);
                }

                if (qc.Is_Selected_or_GroupBy_or_Sorted)
                {
                    selectCnt++;
                    if (mc.PivotValues != null && firstSelectedPivotedCol == null &&
                        !eqp.ReturnQNsInFullDetail)                         // don't count if getting qualified numbers since handled differently
                    {
                        firstSelectedPivotedCol = qc;
                    }
                }

                if (qc.Criteria != "")
                {
                    criteriaCnt++;
                    if (mc.PivotValues != null && firstCriteriaPivotedCol == null)
                    {
                        firstCriteriaPivotedCol = qc;
                    }
                }
            }

            if ((eqp.Qe.MqlLogicType != QueryLogicType.And && criteriaCnt > 1))
            {
                firstSelectedPivotedCol = firstCriteriaPivotedCol = null;                 // don't use this optimization unless "and" logic
            }
            // Add key column to selected list if not already selected

            qc = Qt.KeyQueryColumn;
            if (qc == null)
            {
                throw new Exception("Key column not found for MetaTable " + mt.Name);
            }

            if (!qc.Selected)             // put key at start of list if not already selected
            {
                SelectList.Add(mt.KeyMetaColumn);
                selectCnt++;
            }

            ////////////////////////////////////////////////////////////////////////////////
            // Build sql
            ////////////////////////////////////////////////////////////////////////////////
            //
            // An inner query in the from clause pivots out the columns that are
            // selected or have criteria
            //
            ////////////////////////////////////////////////////////////////////////////////

            pivotExprs =                                                                   // key field with native col name mapped to mc name
                         "t0." + mt.KeyMetaColumn.ColumnMap + " " + mt.KeyMetaColumn.Name; // key field with native col name mapped to mc name

            pivotCriteria = "";
            int pivotCount = 0;

            QueryColumn t0Col = null;             // column assigned to t0

            if (firstCriteriaPivotedCol != null)
            {
                t0Col = firstCriteriaPivotedCol;                 // if there is a pivoted column with criteria assign to t0
            }
            else if (selectCnt == 2 && firstSelectedPivotedCol != null)
            {
                t0Col = firstSelectedPivotedCol;                 // single selected pivoted col assigned to t0
            }
            else
            {             // multiple (or zero) selected pivoted cols, build "select unique" to join all pivoted values to
                pivotTables = "(select unique ";
                for (int mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
                {
                    if (mci > 0)
                    {
                        pivotTables += ", ";
                    }
                    pivotTables += mt.PivotMergeColumns[mci];
                }

                pivotTables += " from " + mt.TableMap + " ";

                string tableSelectionExpr = GetTableFilterCriteria(mt);

                if (pivotValueList.Length > 0)                 // if we have any pivot value then limit to those
                {
                    if (tableSelectionExpr != "")
                    {
                        tableSelectionExpr += " and ";
                    }
                    tableSelectionExpr += mt.PivotColumns[0] + " in (" + pivotValueList + ")";
                }
                pivotTables += " where " + tableSelectionExpr + ") t0 ";
            }

            // Process query column objects

            for (qci = 0; qci < qcl.Count; qci++)
            {
                qc = qcl[qci];
                mc = qc.MetaColumn;

                // Pivot out the result

                if (!qc.Is_Selected_or_Criteria_or_GroupBy_or_Sorted)
                {
                    continue;
                }

                if (mt.KeyMetaColumn == mc)
                {
                    ;                                         // already have key column
                }
                else if (mc.PivotValues != null)              // pivot out of results table
                {
                    if (qc == t0Col)
                    {
                        tableAlias = "t0";                                  // col to be used as t0
                    }
                    else
                    {
                        pivotCount++;
                        tableAlias = "t" + (pivotCount).ToString();
                    }

                    pivotExprs += ", ";                               // add one or more expressions for this column
                    string colName = tableAlias + "." + mc.ColumnMap; // name for this col qualified by table alias
                    if (Eqp.ReturnQNsInFullDetail &&                  // return in qn format?
                        (mc.DataType == MetaColumnType.QualifiedNo || mc.DetailsAvailable))
                    {
                        if (qc.Is_Selected_or_GroupBy_or_Sorted)
                        {                                           // build expression to retrieve catenated col value & linking info
                            string colExpr = colName;
                            if (mc.DataType == MetaColumnType.Date) // return normalized, sortable date string (annotation tables)
                            {
                                colExpr = "to_char(" + colName +
                                          ",'YYYYMMDD HH24MISS')";
                            }

                            string qnExpr =
                                AppendQnElement(mt.QnQualifier) + " chr(11) || " +
                                AppendQnElement(mt.QnNumberValue) + " chr(11) || " +
                                AppendQnElement(mt.QnTextValue) + " chr(11) || " +
                                AppendQnElement(mt.QnNValue) + " chr(11) || " +
                                AppendQnElement(mt.QnNValueTested) + " chr(11) || " +
                                AppendQnElement(mt.QnStandardDeviation) + " chr(11) || " +
                                AppendQnElement(mt.QnStandardError) + " chr(11) || " +
                                AppendQnElement(mt.QnLinkValue);

                            qnExpr = qnExpr.Replace("<TA>", tableAlias);

                            pivotExprs += qnExpr + " " + mc.Name + ", ";
                        }

                        pivotExprs +=                         // native column value for comparison
                                      colName + " " + mc.Name + "_val ";
                    }

                    else                     // not a qualified number or not returning qualified numbers
                    {
                        //if (qc.Criteria.ToLower().EndsWith(" is not null")) // if is-not-null criteria then any value will do not just number
                        //pivotExprs += tableAlias + ".assay_rslt_typ_id " +    mc.Name + "_val "; // todo: fix to work with complex criteria
                        //else

                        pivotExprs +=                         // just return native value
                                      colName + " " + mc.Name + ", ";
                        pivotExprs +=                         // native column value for comparison
                                      colName + " " + mc.Name + "_val ";
                    }

                    if (pivotTables != "")
                    {
                        pivotTables += ", ";
                    }
                    pivotTables += GetSourceWithTableFilterCriteria(mt) + " " + tableAlias + " ";

                    txt = "";
                    for (int pci = 0; pci < mt.PivotColumns.Count; pci++)
                    {                     // filter unpivoted source for just this column
                        if (txt != "")
                        {
                            txt += " and ";
                        }
                        txt += "<TA>." + mt.PivotColumns[pci] + " (+) = " + mc.PivotValues[pci] + " ";
                    }

                    if (qc != t0Col)                      // joint to t0 on merge columns unless we are t0
                    {
                        foreach (string mergeCol in mt.PivotMergeColumns)
                        {
                            txt += "and <TA>." + mergeCol + " (+) = t0." + mergeCol + " ";
                        }
                    }

                    txt = txt.Replace("<TA>", tableAlias);                     // plug in proper table alias
                    if (qc.Criteria != "" || selectCnt <= 2)
                    {
                        txt = txt.Replace("(+)", "");                                                          // remove outer join if criteria on this column
                    }
                    if (pivotCriteria != "")
                    {
                        pivotCriteria += " and ";
                    }
                    pivotCriteria += txt + " ";
                }

                else                 // nulls for other non-result fields
                {
                    pivotExprs += ", null " + mc.Name + " ";
                }

                // Add to select list if selected

                if (qc.Is_Selected_or_GroupBy_or_Sorted)            // if selected add to expression list
                {
                    if (mt.KeyMetaColumn == mc)                     // save index of compound id value object
                    {
                        KeyVoi = SelectList.Count;
                    }

                    if (Exprs.Length > 0)
                    {
                        Exprs += ",";
                    }

                    Exprs += mc.Name;

                    SelectList.Add(mc);
                }
            }             // end of column loop

            FromClause = "(select " + pivotExprs + " " +
                         "from " + pivotTables;
            if (pivotCriteria != "")
            {
                FromClause += " where " + pivotCriteria;
            }
            FromClause += ")";

            Sql = "select /*+ first_rows */ " + Exprs +
                  " from " + FromClause;

            if (Qt.Alias != "")
            {
                Sql += " " + Qt.Alias;
            }
            if (eqp.CallerSuppliedCriteria != "")
            {
                Sql += " where " + eqp.CallerSuppliedCriteria;
            }

            return(Sql);
        }