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