/// <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; }