/// <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 }
/// <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; }
/// <summary> /// Execute query in preparation for retrieving rows /// </summary> /// <param name="parms"></param> public override void ExecuteQuery( ExecuteQueryParms eqp) { BufferedRows = new List <UnpivotedAssayResult>(); MultiTablePivotBrokerTypeData mpd = null; MetaTable mt; List <MultiDbAssayMetaBroker> mbList = null; MultiDbAssayMetaBroker mb, mb2 = null; UnpivotedAssayResult rr, rr2; List <UnpivotedAssayResult> rrList = new List <UnpivotedAssayResult>(); // list of results summarized by target & result type bool unpivotedTableIsFirst; object[] vo; string mtName = null; int t0 = TimeOfDay.Milliseconds(); mt = eqp.QueryTable.MetaTable; Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo; if (PivotInCode) { mpd = mbsi[MpGroupKey]; if (PivotInCode && mpd.MbInstances.Count == 1 && UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name)) { PivotInCode = false; // don't multipivot if single unpivoted summary table } } if (!PivotInCode) // if not multipivot then call generic broker { base.ExecuteQuery(eqp); return; } if (mpd.FirstTableName != Qt.MetaTable.Name) { return; // retrieve data for all tables when we see first table } SetupSecondaryMetaBroker(eqp, mpd, out unpivotedTableIsFirst); mb2 = SecondaryMetaBroker; mpd.ClearBuffers(); // Retrieve data & store for associated metabrokers if (mb2 == this) { base.ExecuteQuery(eqp); // execute with the base generic broker } else { mb2.ExecuteQuery(Eqp2); // use the secondary broker that was created } AssayDict dict = new AssayDict(); rr = new UnpivotedAssayResult(); int readCnt = 0; bool includeResultDetail = MdbAssayVoMap.ResultDetailId.Voi >= 0; while (true) { if (mb2 == this) { vo = base.NextRow(); // get data via generic broker } else { vo = mb2.NextRow(); // get data with secondary broker } if (vo == null) { break; } rr.FromValueObject(vo, mb2.MdbAssayVoMap); // parse values into a UnpivotedAssayResult int rowsFetched = 0, vosCreated = 0; for (int pup = 0; pup < 2; pup++) // first pass for unpivoted table, 2nd for pivoted by gene { //try //{ if (pup == 0) { if (!unpivotedTableIsFirst) { continue; // if no unpivoted first table skip this } mtName = mpd.FirstTableName; // unpivoted table should be first } else // pivoted table { if (Lex.IsNullOrEmpty(rr.GeneSymbol)) { continue; // skip if no target symbol } if (Lex.Contains(mpd.FirstTableName, "CORP")) // mapped to pivoted corp only table { mtName = MultiDbAssayDataNames.BasePivotTablePrefix + rr.GeneSymbol.ToUpper(); // name of table mapped to } else // combined tables { mtName = MultiDbAssayDataNames.CombinedPivotTablePrefix + rr.GeneSymbol.ToUpper(); } } //} //catch (Exception ex) { ex = ex; } mt = MetaTableCollection.Get(mtName); if (mt == null) { continue; } if (!mpd.MbInstances.ContainsKey(mt.Name)) { continue; // have row hash for broker? } int mbIdx = 0; if (mpd.MbInstances[mtName] is MultiDbAssayMetaBroker) { mb = (MultiDbAssayMetaBroker)mpd.MbInstances[mtName]; // broker assoc w/table } else { mbList = (List <MultiDbAssayMetaBroker>)mpd.MbInstances[mtName]; mb = (MultiDbAssayMetaBroker)mbList[0]; } while (true) // copy out for each metabroker for metatable { UnpivotedAssayResultFieldPositionMap voMap = mb.MdbAssayVoMap; vo = rr.ToValueObject(mb.Qt.SelectedCount, voMap); if (mb.MultipivotRowList == null) { mb.MultipivotRowList = new List <object[]>(); } mb.MultipivotRowList.Add(vo); if (mbList == null) { break; // single broker } mbIdx++; // go to next broker if (mbIdx >= mbList.Count) { break; // at end of brokers? } mb = (MultiDbAssayMetaBroker)mbList[mbIdx]; } } // tables to copy data to loop } // row fetch loop return; }
/// <summary> /// Setup/configure any needed secondary metabroker /// </summary> /// <param name="eqp"></param> /// <param name="mpd"></param> /// <param name="unpivotedTableIsFirst"></param> void SetupSecondaryMetaBroker( ExecuteQueryParms eqp, MultiTablePivotBrokerTypeData mpd, out bool unpivotedTableIsFirst) { QueryEngine qe2; Query q2; QueryTable qt2; QueryColumn qc2; MetaTable mt2; string firstMtName = mpd.FirstTableName; MetaTable firstMt = MetaTableCollection.GetWithException(firstMtName); unpivotedTableIsFirst = UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(firstMtName); if (unpivotedTableIsFirst) // just use unpivoted table as is { SecondaryMetaBroker = this; Eqp2 = eqp; } else // all pivoted tables, create secondary query on the summarized unpivoted table { if (SecondaryMetaBroker == null) { MultiDbAssayMetaBroker mb2 = SecondaryMetaBroker = new MultiDbAssayMetaBroker(); if (UnpivotedAssayResult.IsCombinedMdbAssayTable(firstMtName)) { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.CombinedTableName); } else { mt2 = MetaTableCollection.GetWithException(MultiDbAssayDataNames.BaseTableName); } q2 = new Query(); qt2 = new QueryTable(mt2); qc2 = qt2.GetQueryColumnByNameWithException(MultiDbAssayDataNames.GeneSymbol); if (mpd.TableCodeCsvList.Length != 0) // limit by codes { qc2.Criteria = qc2.CriteriaDisplay = MultiDbAssayDataNames.GeneSymbol + " in (" + mpd.TableCodeCsvList + ")"; } q2.AddQueryTable(qt2); qe2 = new QueryEngine(); qe2.Query = q2; Eqp2 = new ExecuteQueryParms(qe2, qt2); mb2.PrepareQuery(Eqp2); mb2.Sql += " where " + qc2.Criteria; } Eqp2.SearchKeySubset = eqp.SearchKeySubset; } return; }
/// <summary> /// Prepare query /// </summary> /// <param name="parms"></param> public override string PrepareQuery( ExecuteQueryParms eqp) { List <MultiDbAssayMetaBroker> mbList; MultiTablePivotBrokerTypeData mpd; // multipivot data for this broker type Dictionary <string, MultiTablePivotBrokerTypeData> mbsi; QueryColumn qc; MetaTable mt; Eqp = eqp; Qt = eqp.QueryTable; mt = eqp.QueryTable.MetaTable; BuildActivityBinCondFormat(); MdbAssayVoMap = UnpivotedAssayResultFieldPositionMap.NewMdbAssayMap(Qt); MdbAssayVoMap.InitializeForQueryTable(Qt); LoadTargetMap(); if ( // check for basic conditions that disallow multipivoting !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 !UnpivotedAssayResult.IsSummarizedMdbAssayTable(mt.Name)) // summarized tables only { return(base.PrepareQuery(eqp)); } int pivotedColCount = 0; foreach (QueryColumn qc1 in Qt.QueryColumns) { // if any non-key criteria then pivot individually rather than via multipivot if (qc1.Criteria != "" && !qc1.IsKey) { if (UnpivotedAssayResult.IsUnpivotedSummarizedMdbAssayTable(mt.Name) && Qt.Query.SingleStepExecution) { } // special case: allow criteria on unpivoted summary table which also apply to associated pivoted tables else { return(base.PrepareQuery(eqp)); } } if (IsPivotedColumn(qc1.MetaColumn)) { pivotedColCount++; } } if (pivotedColCount == 0) { return(base.PrepareQuery(eqp)); // must have at least one column to pivot } // Store pivot info for queryTable PivotInCode = true; Sql = BuildSql(eqp); if (eqp.Qe.MetaBrokerStateInfo == null) { eqp.Qe.MetaBrokerStateInfo = new Dictionary <string, MultiTablePivotBrokerTypeData>(); } mbsi = eqp.Qe.MetaBrokerStateInfo; MpGroupKey = MetaBrokerType.TargetAssay.ToString(); // key for broker for query if (!QueryEngine.AllowMultiTablePivot) { MpGroupKey += "_" + Qt.MetaTable.Name; } mpd = MultiTablePivotBrokerTypeData.GetMultiPivotData(eqp.Qe.MetaBrokerStateInfo, MpGroupKey, mt.Name); string geneSymbol = mt.Code; if (!Lex.IsNullOrEmpty(geneSymbol) && !mpd.TableCodeDict.ContainsKey(geneSymbol)) { mpd.TableCodeDict[geneSymbol] = new MpdResultTypeData(); // add key to hash list if (mpd.TableCodeCsvList.Length > 0) { mpd.TableCodeCsvList.Append(","); } mpd.TableCodeCsvList.Append(Lex.AddSingleQuotes(geneSymbol)); } mpd.AddMetaBroker(mt.Name, this); return(Sql); }