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