/// <summary>
/// Get the QueryEngine with the supplied ID
/// </summary>
/// <param name="qeId"></param>
/// <returns></returns>
Qel.QueryEngine GetQueryEngineInstance(int qeId)
{
lock (Qel.QueryEngine.IdToInstanceDict)
{
if (Qel.QueryEngine.IdToInstanceDict.ContainsKey(qeId))
{
Qel.QueryEngine qe = Qel.QueryEngine.IdToInstanceDict[qeId];
return(qe);
}
else
{
throw new ArgumentException("Invalid query engine id: " + qeId);
}
}
}
/// <summary>
/// Read input data from database
/// </summary>
/// <param name="smp">
/// <returns></returns>
List<CompoundStructureActivityData> ReadData(
SasMapParms smp)
{
MetaColumn activityMc = smp.EndpointMc;
QueryColumn keyCriteriaQc = smp.KeyCriteriaQc;
AssertMx.IsNotNull(activityMc, "mc");
AssertMx.IsNotNull(keyCriteriaQc, "keyCriteriaQc");
MetaTable mt, mt2;
MetaColumn mc2 = null;
Query q = new Query();
mt = activityMc.MetaTable;
QueryTable qt = new QueryTable(mt);
if (mt.SummarizedExists && !mt.UseSummarizedData)
{ // retrieve summarized data if exists
mt2 = MetaTableCollection.Get(mt.Name + MetaTable.SummarySuffix);
if (mt2 != null)
{
mc2 = mt2.GetMetaColumnByName(activityMc.Name);
if (mc2 == null) mc2 = mt2.GetMetaColumnByLabel(activityMc.Label);
}
if (mc2 != null) // same column available in summarized?
{
mt = mt2;
activityMc = mc2;
}
}
SMP.KeyCriteriaQc.CopyCriteriaToQueryKeyCritera(q);
q.KeyCriteriaDisplay = SMP.KeyCriteriaQc.CriteriaDisplay;
qt.SelectKeyOnly();
QueryColumn qc = qt.GetQueryColumnByName(activityMc.Name);
qc.Selected = true;
q.AddQueryTable(qt);
QueryEngine qe = new QueryEngine();
List<string> keyList = qe.ExecuteQuery(q); // note that keylist may be empty if single-step query
HashSet<string> keySet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
List<CompoundStructureActivityData> data = new List<CompoundStructureActivityData>();
int rowCount = 0;
while (true)
{
object[] vo = qe.NextRow();
if (vo == null) break;
CompoundStructureActivityData cd = new CompoundStructureActivityData();
string cid = (string)vo[0];
cd.Cid = cid;
keySet.Add(cid); // accumulate keys
object val = vo[2];
if (NullValue.IsNull(val)) continue;
if (val is double)
cd.Activity = (double)val;
else if (val is Int32)
cd.Activity = (Int32)val;
else if (val is NumberMx)
{
NumberMx nex = val as NumberMx;
cd.Activity = nex.Value;
}
else if (val is QualifiedNumber)
{
QualifiedNumber qn = val as QualifiedNumber;
cd.Activity = qn.NumberValue;
//if (qn.Qualifier != null && qn.Qualifier != "" && qn.Qualifier != "=")
// continue; // (don't want to do this since may filter out good data (e.g. IC50 <0.0001))
}
else continue;
if (cd.Activity == NullValue.NullNumber) continue;
data.Add(cd);
rowCount++;
}
// Retrieve structures
keyList = new List<string>(keySet);
Dictionary<string, MoleculeMx> csDict = MoleculeUtil.SelectMoleculesForCidList(keyList, qt.MetaTable); // get the structures in a single step
// Add structures and build/store fingerprints to data
DebugLog.Message("========== Fingerprints ============");
foreach (CompoundStructureActivityData cd in data)
{
if (!csDict.ContainsKey(cd.Cid) || csDict[cd.Cid] == null) continue;
if (cd.Cid == "111" || cd.Cid == "222") csDict = csDict; // debug
MoleculeMx cs = csDict[cd.Cid];
cd.Structure = cs;
FingerprintType fpType = FingerprintType.Circular;
int fpSubtype = -1;
if (SMP.SimilarityType == SimilaritySearchType.ECFP4) // some issue with ECFP4?
{
fpType = FingerprintType.Circular;
fpSubtype = CircularFingerprintType.ECFP4;
}
else if (SMP.SimilarityType == SimilaritySearchType.Normal)
{
fpType = FingerprintType.MACCS;
}
cd.BitsetFingerprint = cs.BuildBitSetFingerprint(fpType, fpSubtype);
if (cd.BitsetFingerprint == null) continue; // couldn't build fingerprint (e.g. no structure)
if (Debug) DebugLog.Message(cd.Cid + ": " + Lex.Join(CdkMolUtil.GetBitSet(cd.BitsetFingerprint), ", "));
}
return data;
}
/// <summary>
/// Build a set of rows from data in the buffer
/// </summary>
public override void BuildDataFromOtherRetrievedData(
QueryEngine qe,
int ti,
List <object[]> results,
int firstResult,
int resultCount)
{
// This routine takes a set of results in the buffer, computes the calculated field value
// and stores the results back in the buffer. For a calc field derived from a single column
// the process is simple; however, for two column calculations there are two cases to consider.
// 1. If no values then nothing to compute.
// 2. If both values are present in the buffer row then just use them.
// 3. If a single value is present then pick up the most recent other value that
// exists for the same key.
// Note that if the query only retrieves one of the two fields the the other field
// will be retrieved into the calc field buffer.
QueryColumn qc;
MetaColumn mc;
CalcFieldColumn cfc, cfc2;
int voiCfKey, voiCf, voi;
object v, v1, v2, vn;
object[] vo, mergedVo;
const int keyOffset = 1;
throw new NotImplementedException();
#if false
Query q = qe.Query;
CalcFieldMetaTable cfMt = GetCalcFieldMetaTable(q.Tables[ti].MetaTable);
CalcField cf = cfMt.CalcField;
// Setup
if (resultCount <= 0)
{
return;
}
if (SelectList.Count <= 1)
{
return; // just return if only key selected
}
voiCfKey = Qt.KeyQueryColumn.VoPosition; // calc field key value buffer index
voiCf = Qt.GetQueryColumnByNameWithException("CALC_FIELD").VoPosition; // calculated value buffer index
mergedVo = new object[results[0].Length]; // merged vo containing latest values for each column
// Process each result row
for (int ri = firstResult; ri < firstResult + resultCount; ri++) // result loop
{
vo = results[ri];
if (vo[0] == null)
{
continue;
}
if (mergedVo[0] == null || Lex.Ne(vo[0].ToString(), mergedVo[0].ToString()))
{ // if new key init voVals
Array.Clear(mergedVo, 0, mergedVo.Length);
mergedVo[0] = vo[0];
}
int nonNullCount = 0;
int nullCount = 0;
QueryColumn[] colMap = _dataMap.InputColMap;
for (int mi = 0; mi < colMap.Length; mi++)
{
if (colMap[mi] == null)
{
continue; //
}
voi = colMap[mi].VoPosition;
v = vo[voi];
if (!NullValue.IsNull(v))
{
mergedVo[voi] = v;
nonNullCount++;
}
else
{
nullCount++;
}
}
if (nonNullCount == 0)
{
continue; // if all inputs are null then all done (assume all outputs are null also)
}
vo[voiCfKey] = vo[0]; // copy key value
QueryTableData qtd = qe.Qtd[ti];
// Pick up each retrieved value in the from other data fields
for (int cfci = 0; cfci < qtd.SelectedColumns.Count; cfci++) // columns to retrieve/calculate loop
{
qc = qtd.SelectedColumns[cfci];
mc = qc.MetaColumn;
voi = qc.VoPosition;
bool calcFieldCol = Lex.Eq(mc.Name, "CALC_FIELD");
bool selectedCol = !calcFieldCol;
// Input value column
if (selectedCol) // retrieved input value
{
QueryColumn cmi = _dataMap.SelectedColMap[cfci];
if (cmi != null && cmi.VoPosition > 0)
{
v = mergedVo[cmi.VoPosition];
if (mc.DataType == MetaColumnType.Image) // convert to proper image reference
{
MetaTable sourceMt = cmi.QueryTable.MetaTable;
GenericMetaBroker gmb = QueryEngine.GetGlobalBroker(cmi.QueryTable.MetaTable);
v = gmb.ConvertValueToImageReference(v);
}
vo[voi] = v;
}
}
// Calculate the CF value
else
{
cfc = cf.CfCols[0];
vn = vo[voiCf]; // get possible single retrieved value
vo[voiCf] = null; // clear calc value (may hold one of two values)
v = mergedVo[colMap[0].VoPosition]; // get first value
if (NullValue.IsNull(v))
{
continue; // if null then result must be null
}
v = ApplyFunction(cfc, v);
for (int mi = 1; mi < colMap.Length; mi++) // combine with
{
v1 = v; // reference previous value
if (colMap[mi] == null)
{
continue;
}
cfc2 = cf.CfCols[mi];
voi = colMap[mi].VoPosition;
v2 = mergedVo[voi];
v2 = ApplyFunction(cfc2, v2);
v = CalculateValue(cf, v1, cfc2, v2);
if (v == null)
{
break;
}
}
v = ApplyClassification(cf, v);
vo[voiCf] = v; // store calculated value
}
} // column loop
vo = vo; // debug to check row loop at end
} // row loop
return;
#endif
}
/// <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;
}
public bool Cancelled; // flag set if cancelled
/// <summary>
/// Basic constructor
/// </summary>
public ExecuteQueryParms(
QueryEngine qe)
{
Qe = qe;
}
object IInvokeServiceOps.InvokeServiceOperation(int opCode, object[] args)
{
MobiusQueryEngineService op = (MobiusQueryEngineService)opCode;
switch (op)
{
case MobiusQueryEngineService.Initialize:
{
Qel.QueryEngine.InitializeForSession();
return(null);
}
case MobiusQueryEngineService.CreateInstance:
{
Qel.QueryEngine instance = new Mobius.QueryEngineLibrary.QueryEngine();
return(instance.Id);
}
case MobiusQueryEngineService.DisposeInstance:
{
int instanceId = (int)args[0];
bool disposed = Qel.QueryEngine.Dispose(instanceId);
return(disposed);
}
case MobiusQueryEngineService.SetParameter:
{
string parm = "", value = "";
if (args.Length == 2)
{
parm = args[0].ToString();
value = args[1].ToString();
}
else // old form (remove when all old clients are updated)
{
parm = "DatabaseSubset";
value = args[0].ToString();
}
Qel.QueryEngine.SetParameter(parm, value);
return(null);
}
case MobiusQueryEngineService.GetSummarizationDetailQuery:
{
int instanceId = (int)args[0];
string metaTableName = (string)args[1];
string metaColumnName = (string)args[2];
int level = (int)args[3];
string resultId = (string)args[4];
//Qel.QueryEngine qe = null;
//lock (Qel.QueryEngine.IdToInstanceDict)
//{
// if (Qel.QueryEngine.IdToInstanceDict.ContainsKey(instanceId))
// {
// qe = Qel.QueryEngine.IdToInstanceDict[instanceId];
// }
//}
//if (qe != null)
//{
string queryXml = "";
Data.Query query = QueryEngine.GetSummarizationDetailQuery(metaTableName, metaColumnName, level, resultId);
if (query != null)
{
queryXml = query.Serialize(false);
}
return(queryXml);
//}
//else
//{
// throw new ArgumentException("Not a valid query engine instance id!");
//}
}
case MobiusQueryEngineService.GetDrilldownDetailQuery:
{
int instanceId = (int)args[0];
string metaTableName = (string)args[1];
string metaColumnName = (string)args[2];
int level = (int)args[3];
string resultId = (string)args[4];
//Qel.QueryEngine qe = null;
//lock (Qel.QueryEngine.IdToInstanceDict)
//{
// if (Qel.QueryEngine.IdToInstanceDict.ContainsKey(instanceId))
// {
// qe = Qel.QueryEngine.IdToInstanceDict[instanceId];
// }
//}
//if (qe != null)
//{
Mobius.Data.MetaTable metaTable = Mobius.Data.MetaTableCollection.GetExisting(metaTableName);
Mobius.Data.MetaColumn metaColumn = metaTable.GetMetaColumnByName(metaColumnName);
Data.Query query = QueryEngine.GetDrilldownDetailQuery(metaTable, metaColumn, level, resultId);
string queryXml = query.Serialize(false);
return(queryXml);
//}
//else
//{
// throw new ArgumentException("Not a valid query engine instance id!");
//}
}
case MobiusQueryEngineService.GetImage:
{
string mtMcName = null;
int instanceId = (int)args[0];
if (args[1] is string) // New "mtName.mcName" format for arg (Post Client 5.0)
{
mtMcName = (string)args[1];
}
else if (args[1] is MetaColumn) // old Mobius.Services.Types.MetaColumn format
{
MetaColumn mc = (MetaColumn)args[1];
mtMcName = mc.MetaTable.Name + "." + mc.Name;
}
else
{
return(null); // error
}
Mobius.Data.MetaColumn mobiusMC = Mobius.Data.MetaColumn.ParseMetaTableMetaColumnName(mtMcName);
string graphicsIdString = (string)args[2];
int desiredWidth = (int)args[3];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
//Mobius.Data.MetaColumn mobiusMC = _transHelper.Convert<MetaColumn, Mobius.Data.MetaColumn>(metaColumn);
System.Drawing.Bitmap bitmap = qe.GetImage(mobiusMC, graphicsIdString, desiredWidth);
Types.Bitmap result = new Types.Bitmap(bitmap);
return(result);
}
case MobiusQueryEngineService.ResolveCidListReference:
{
string tok = (string)args[0];
Mobius.Data.UserObject uo = Qel.QueryEngine.ResolveCidListReference(tok);
UserObjectNode result = _transHelper.Convert <Mobius.Data.UserObject, UserObjectNode>(uo);
return(result);
}
case MobiusQueryEngineService.GetRootTable:
{
string queryString = (string)args[0];
Mobius.Data.Query mobiusQuery = Data.Query.Deserialize(queryString);
Mobius.Data.MetaTable mobiusMT = Qel.QueryEngine.GetRootTable(mobiusQuery);
string mtString = null;
if (mobiusMT != null)
{
mtString = mobiusMT.Serialize();
}
return(mtString);
}
case MobiusQueryEngineService.DoPresearchChecksAndTransforms:
{
string serializedQuery = (string)args[0];
Data.Query q = Data.Query.Deserialize(serializedQuery);
q = Qel.QueryEngine.DoPreSearchTransformations(q);
if (q == null)
{
return(null);
}
string serializedQuery2 = q.Serialize(true);
return(serializedQuery2);
}
case MobiusQueryEngineService.GetKeys:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
List <string> keys = qe.GetKeys();
return(keys);
}
case MobiusQueryEngineService.ExecuteQuery:
{
int instanceId = (int)args[0];
string serializedQuery = (string)args[1];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Data.Query q = Data.Query.Deserialize(serializedQuery);
List <string> result = qe.ExecuteQuery(q, false, false);
return(result);
}
case MobiusQueryEngineService.TransformAndExecuteQuery:
{
int instanceId = (int)args[0];
string serializedQuery = (string)args[1];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Data.Query q2;
Data.Query q = Data.Query.Deserialize(serializedQuery);
List <string> keyList = qe.TransformAndExecuteQuery(q, out q2);
object[] sa = new object[2];
sa[0] = keyList;
if (q2 != null) // any transformed query?
{
sa[1] = q2.Serialize(true, true); // serialize including metatables not previously send to client
}
return(sa);
}
case MobiusQueryEngineService.BuildSqlStatements:
{
int instanceId = (int)args[0];
string serializedQuery = (string)args[1];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Data.Query q = Data.Query.Deserialize(serializedQuery);
string sqlStatements = qe.BuildSqlStatements(q);
return(sqlStatements);
}
case MobiusQueryEngineService.SaveSpotfireSql:
{
string sqlStmtName = (string)args[0];
string serializedQuery = (string)args[1];
Data.Query q = Data.Query.Deserialize(serializedQuery);
int stmtId = Mobius.QueryEngineLibrary.QueryEngine.SaveSpotfireSql(sqlStmtName, q);
return(stmtId);
}
case MobiusQueryEngineService.SaveSpotfireKeyList:
{
string keyColName, listType, keyList;
if (args.Length >= 3)
{
keyColName = (string)args[0];
listType = (string)args[1];
keyList = (string)args[2];
}
else if (args.Length == 2) // old form
{
keyColName = "CORP_ID";
listType = (string)args[0];
keyList = (string)args[1];
}
else // old form for cid list
{
keyColName = "CORP_ID";
listType = "CIDLIST";
keyList = (string)args[0];
}
string keyListName = Mobius.QueryEngineLibrary.QueryEngine.SaveSpotfireKeyList(keyColName, listType, keyList);
return(keyListName);
}
case MobiusQueryEngineService.ReadSpotfireSql:
{
string sqlStmtName = args[0] as string;
int version = (int)args[1];
string sql = Mobius.QueryEngineLibrary.QueryEngine.ReadSpotfireSql(sqlStmtName, version);
return(sql);
}
case MobiusQueryEngineService.RemapTablesForRetrieval: // DoPreRetrievalTransformation
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Mobius.Data.Query q = qe.DoPreRetrievalTableExpansions();
if (q == null)
{
return(null);
}
string serializedQuery = q.Serialize(true); // serialize including metatables not previously included
return(serializedQuery);
}
case MobiusQueryEngineService.NextRowsSerialized:
{
int minRows = 1;
bool returnQeStats = false;
List <Mobius.Data.MetaBrokerStats> mbStats = null;
int instanceId = (int)args[0];
int ai = 1;
if (args.Length >= 4) // newer version with minRows arg
{
minRows = (int)args[ai++];
}
int maxRows = (int)args[ai++];
int maxTime = (int)args[ai++];
if (ai < args.Length) // newer version with returnQeStats arg
{
returnQeStats = (bool)args[ai++];
}
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
if (returnQeStats)
{
mbStats = new List <Mobius.Data.MetaBrokerStats>();
}
byte[] serializedRows = qe.NextRowsSerialized(minRows, maxRows, maxTime, mbStats);
if (!returnQeStats)
{
return(serializedRows);
}
else // return serialized rows and stats in a two-element object array
{
object[] oa = new object[2];
oa[0] = serializedRows;
oa[1] = Mobius.Data.MetaBrokerStats.SerializeList(mbStats);
return(oa);
}
}
case MobiusQueryEngineService.NextRows:
{
int minRows = 1;
int instanceId = (int)args[0];
int ai = 1;
if (args.Length >= 4) // newer version with minRows arg
{
minRows = (int)args[ai++];
}
int maxRows = (int)args[ai++];
int maxTime = (int)args[ai++];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
List <object[]> rows = qe.NextRows(minRows, maxRows, maxTime);
DataRow dataRow = null;
List <DataRow> dataRows = new List <DataRow>();
if (rows == null)
{
return(dataRows);
}
for (int ri = 0; ri < rows.Count; ri++) // convert each row
{
object[] row = rows[ri];
if (row != null)
{
dataRow = new DataRow();
object[] convertedRow = new object[row.Length];
for (int i = 0; i < row.Length; i++)
{
convertedRow[i] = _transHelper.ConvertObject(row[i]);
}
dataRow.Data = convertedRow;
}
dataRows.Add(dataRow);
}
return(dataRows);
}
case MobiusQueryEngineService.NextRow:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
object[] row = qe.NextRow();
DataRow dataRow = null;
if (row != null)
{
dataRow = new DataRow();
object[] convertedRow = new object[row.Length];
for (int i = 0; i < row.Length; i++)
{
convertedRow[i] = _transHelper.ConvertObject(row[i]);
}
dataRow.Data = convertedRow;
}
return(dataRow);
}
case MobiusQueryEngineService.Close:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
qe.Close();
return(true);
}
case MobiusQueryEngineService.Cancel:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
qe.Cancel(false);
return(true);
}
case MobiusQueryEngineService.GetQueryEngineState:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Mobius.Data.QueryEngineState state = qe.State;
QueryEngineState result = _transHelper.Convert <Mobius.Data.QueryEngineState, QueryEngineState>(state);
return(result);
}
case MobiusQueryEngineService.LogExceptionAndSerializedQuery:
{
string exMsg = (string)args[0];
string serializedQuery = (string)args[1];
Data.Query query = Data.Query.Deserialize(serializedQuery);
return(Qel.QueryEngine.LogExceptionAndSerializedQuery(exMsg, query));
}
case MobiusQueryEngineService.LogQueryExecutionStatistics:
{
Qel.QueryEngine.LogQueryExecutionStatistics();
return(null);
}
case MobiusQueryEngineService.ValidateCalculatedFieldExpression:
{
string advExpr = (string)args[0];
return(Qel.QueryEngine.ValidateCalculatedFieldExpression(advExpr));
}
case MobiusQueryEngineService.CreateQueryFromMQL:
{
string mql = (string)args[0];
Mobius.Data.Query mobiusQuery = Mobius.Data.MqlUtil.ConvertMqlToQuery(mql);
Query result = _transHelper.Convert <Mobius.Data.Query, Query>(mobiusQuery); //translate the result
return(result);
}
case MobiusQueryEngineService.ExecuteMQLQuery:
{
Mobius.Data.Query q2;
int instanceId = (int)args[0];
string mql = (string)args[1];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
Mobius.Data.Query q = Mobius.Data.MqlUtil.ConvertMqlToQuery(mql);
List <string> result = qe.TransformAndExecuteQuery(q, out q2);
return(result);
}
case MobiusQueryEngineService.GetSelectAllDataQuery:
{
string mtName = (string)args[0];
string cn = (string)args[1];
string queryXml = "";
Mobius.Data.Query q = QueryEngineLibrary.QueryEngine.GetSelectAllDataQuery(mtName, cn);
if (q != null)
{
queryXml = q.Serialize(false);
}
return(queryXml);
}
case MobiusQueryEngineService.GetStandardMobileQueries:
{
Data.Query[] mobileQueries = QueryEngineLibrary.QueryEngine.GetStandardMobileQueries();
List <Query> queries = new List <Query>();
foreach (Data.Query query in mobileQueries)
{
Query typesQuery = _transHelper.Convert <Mobius.Data.Query, Query>(query);
queries.Add(typesQuery);
}
return(queries.ToArray());
}
case MobiusQueryEngineService.GetMobileQueriesByOwner:
{
string user = (string)args[0];
Data.Query[] mobileQueries = QueryEngineLibrary.QueryEngine.GetMobileQueriesByOwner(user);
List <Query> queries = new List <Query>();
foreach (Data.Query query in mobileQueries)
{
Query typesQuery = _transHelper.Convert <Mobius.Data.Query, Query>(query);
queries.Add(typesQuery);
}
return(queries.ToArray());
}
case MobiusQueryEngineService.GetAdditionalData:
{
int instanceId = (int)args[0];
string command = (string)args[1];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
object o = qe.GetAdditionalData(command);
return(o);
}
case MobiusQueryEngineService.ExportDataToSpotfireFiles:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
ExportParms ep = ExportParms.Deserialize(args[1] as string);
QueryEngineStats qeStats = qe.ExportDataToSpotfireFiles(ep);
return(qeStats != null ? qeStats.Serialize() : null);
}
case MobiusQueryEngineService.CompleteRowRetrieval:
{
int instanceId = (int)args[0];
Qel.QueryEngine qe = GetQueryEngineInstance(instanceId);
QueryEngineStats qeStats = qe.CompleteRowRetrieval();
return(qeStats != null ? qeStats.Serialize() : null);
}
default:
throw new InvalidOperationException("Unrecognized operation: " + (int)op);
}
}
/// <summary>
/// Analyze key criteria in token list.
/// A saved list reference is returned as a set of key values &
/// blanked out in the token list.
/// Other key references are returned as a set of indexes in a list.
/// </summary>
/// <param name="q"></param>
/// <param name="Qtd"></param>
/// <param name="tokens"></param>
/// <param name="keyCriteriaPositions">Token indexes for key criteria column names</param>
/// <param name="keyCriteriaSavedListKeys">Keys in any referenced saved list</param>
/// <param name="keyCriteriaInListKeys">Keys in any literal list </param>
/// <param name="keyCriteriaConstantPositions">Positions of key values for literal lists</param>
public static void AnalyzeKeyCriteria(
Query q,
QueryTableData[] Qtd,
List <MqlToken> tokens,
out CompareOp keyCriteriaOp,
out List <int> keyCriteriaPositions,
out List <string> keyCriteriaSavedListKeys,
out List <string> keyCriteriaInListKeys,
out List <int> keyCriteriaConstantPositions)
{
string tok1, tok2, tok3, tok4, tok5;
keyCriteriaOp = CompareOp.Unknown;
keyCriteriaPositions = new List <int>();
keyCriteriaSavedListKeys = null;
keyCriteriaInListKeys = null;
keyCriteriaConstantPositions = new List <int>();
for (int tki = 0; tki < tokens.Count; tki++)
{
QueryColumn qc = tokens[tki].Qc;
if (qc == null)
{
continue;
}
if (!qc.IsKey)
{
continue;
}
keyCriteriaPositions.Add(tki); // remember position of key col reference
if (tokens.Count < tki + 2)
{
throw new QueryException("Incomplete compound id criteria at end of statement");
}
bool notLogic = false;
tok1 = GetTokenListString(tokens, tki + 1);
if (Lex.Eq(tok1, "Not"))
{
notLogic = true;
tki++;
if (tokens.Count < tki + 2)
{
throw new QueryException("Incomplete compound id criteria at end of statement");
}
tok1 = GetTokenListString(tokens, tki + 1);
}
tok2 = GetTokenListString(tokens, tki + 2);
tok3 = GetTokenListString(tokens, tki + 3);
tok4 = GetTokenListString(tokens, tki + 4);
tok5 = GetTokenListString(tokens, tki + 5);
// Saved List
if (tokens.Count > tki + 3 && Lex.Eq(tok1, "In") &&
Lex.Eq(tok2, "List"))
{ // have a saved list
keyCriteriaOp = CompareOp.InList;
if (keyCriteriaSavedListKeys != null) // already have it?
{
throw new UserQueryException("Only one condition is allowed for the " + qc.ActiveLabel + " field");
}
if (notLogic) // not currently allowed for saved lists
{
throw new UserQueryException("\"Not\" logic is not allowed for " + qc.ActiveLabel + " saved lists");
}
string listName = tokens[tki + 3].Tok.Text;
UserObject uo = ResolveCidListReference(listName);
if (uo == null)
{
throw new UserQueryException("Key list " + listName + " not found");
}
listName = uo.InternalName;
CidList keyCriteriaSavedList = CidListDao.Read(listName, QueryEngine.GetRootTable(q));
if (keyCriteriaSavedList == null)
{
throw new UserQueryException("Key list " + listName + " not found");
}
keyCriteriaSavedListKeys = keyCriteriaSavedList.ToStringList();
tokens[tki].Qc = null;
tokens[tki].Tok.Text = "";
for (int tki2 = tki + 1; tki2 <= tki + 3; tki2++)
{
tokens[tki2].Tok.Text = "";
}
if (!MqlUtil.DisableAdjacentAndLogic(tokens, tki, tki + 3))
{
throw new UserQueryException("Only \"And\" logic is allowed with " +
qc.ActiveLabel + " saved lists");
}
int ti = q.GetQueryTableIndexByAlias(qc.QueryTable.Alias);
Qtd[ti].CriteriaCount--;
keyCriteriaPositions.RemoveAt(keyCriteriaPositions.Count - 1);
}
// Explicit list of allowed keys
else if (tokens.Count > tki + 2 && Lex.Eq(tok1, "In") &&
Lex.Eq(tok2, "("))
{
keyCriteriaOp = CompareOp.In;
int listKeyCount = 0; // count keys in this list
for (int tki2 = tki + 3; tki2 < tokens.Count; tki2++)
{
tok1 = tokens[tki2].Tok.Text;
if (tok1 == ",")
{
continue;
}
else if (tok1 == ")")
{
break;
}
keyCriteriaConstantPositions.Add(tki2);
if (keyCriteriaInListKeys == null)
{
keyCriteriaInListKeys = new List <string>();
}
MetaTable rootTable = QueryEngine.GetRootTable(q);
string normKey = CompoundId.Normalize(tok1, rootTable);
if (normKey != null && normKey != "")
{
keyCriteriaInListKeys.Add(normKey);
listKeyCount++;
}
}
if (listKeyCount == 0)
{
throw new UserQueryException("The query contains an invalid empty list of " + qc.ActiveLabel + "s");
}
}
// Between a range of key values
else if (tokens.Count > tki + 4 && Lex.Eq(tok1, "Between") &&
Lex.Eq(tok3, "And"))
{
keyCriteriaOp = CompareOp.Between;
keyCriteriaConstantPositions.Add(tki + 2);
keyCriteriaConstantPositions.Add(tki + 4);
}
// Single key value, treat like a list with a single value
else if (Lex.Eq(tok1, "="))
{
keyCriteriaOp = CompareOp.Eq;
keyCriteriaConstantPositions.Add(tki + 2);
if (keyCriteriaInListKeys == null)
{
keyCriteriaInListKeys = new List <string>();
}
MetaTable rootTable = QueryEngine.GetRootTable(q);
string normKey = CompoundId.Normalize(tok2, rootTable);
if (Lex.IsDefined(normKey))
{
keyCriteriaInListKeys.Add(normKey);
}
}
// Other binary operator
else if (MqlUtil.IsBasicComparisonOperator(tok1))
{
keyCriteriaOp = CompareOpString.ToCompareOp(tok1);
keyCriteriaConstantPositions.Add(tki + 2);
}
// Unary operator "is null"
else if (Lex.Eq(tok1, "Is") && Lex.Eq(tok2, "Null"))
{
keyCriteriaOp = CompareOp.IsNull;
}
// Unary operator "is not null"
else if (Lex.Eq(tok1, "Is") && Lex.Eq(tok2, "Not") && Lex.Eq(tok3, "Null"))
{
keyCriteriaOp = CompareOp.IsNotNull;
}
else
{
throw new QueryException("Unrecognized compound id condition " + tok1);
}
}
return;
}
/// <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);
}
/// <summary>
/// Prep non-Oracle query
/// </summary>
/// <param name="eqp"></param>
/// <returns></returns>
public override string PrepareQuery(
ExecuteQueryParms eqp)
{
int cti;
Eqp = eqp;
QueryEngine qe = eqp.Qe;
Query q = qe.Query;
QueryTable qt = eqp.QueryTable;
QueryColumn qc;
MetaTable mt = qt.MetaTable;
MetaColumn mc = null;
StructCriteriaQc = KeyCriteriaQc = null;
QueryColumn strQc = qt.FirstStructureQueryColumn;
if (strQc != null && Lex.IsDefined(strQc.Criteria))
{
StructCriteriaQc = strQc;
}
else
{
throw new Exception("Structure criteria not defined");
}
Pssc = ParsedStructureCriteria.Parse(StructCriteriaQc);
QueryColumn keyQc = qt.KeyQueryColumn;
if (keyQc != null && Lex.IsDefined(q.KeyCriteria)) //keyQc.Criteria))
{
KeyCriteriaQc = keyQc;
}
if (StructCriteriaQc == null &&
KeyCriteriaQc == null &&
eqp.SearchKeySubset == null)
{
throw new Exception("NonSqlBroker - No criteria specified");
}
SelectList = new List <MetaColumn>(); // list of selected metacolumns
foreach (QueryColumn qc2 in qt.QueryColumns)
{
if (qc2.MetaColumn == null)
{
continue; // in case metacolumn not defined
}
if (qc2.IsKey)
{
qc2.Selected = true;
}
if (qc2.Selected || qc2.SortOrder != 0)
{
SelectList.Add(qc2.MetaColumn);
}
}
// Setup for ECFP4 similarity search
if (Pssc.SearchType == StructureSearchType.MolSim && Pssc.SimilarityType == SimilaritySearchType.ECFP4)
{
Ecfp4Dao = null; // reset Dao to initiate new search
return("");
}
else if (Pssc.SearchType == StructureSearchType.SmallWorld) // SmallWorld search
{
SwDao = null; // reset Dao to initiate new search
return("");
}
else if (Pssc.SearchType == StructureSearchType.Related) // Related structure search
{
RSS = null; // reset to initiate new search
return("");
}
else if (!MqlUtil.IsCartridgeMetaTable(mt)) // must be non chemical cartridge table (e.g. User structure DB or structure in Annotation table
{
return(""); // everything looks ok, query criteria stored here, no sql returned
}
else
{
throw new Exception("Unsupported NonSqlBroker search for table: " + eqp.QueryTable.MetaTable.Label);
}
}
/// <summary>
/// Convert a multipivot table into a set of tables where data exists for
/// one or more of the compound identifiers in the list.
/// </summary>
/// <param name="qt">Current form of query table</param>
/// <param name="q">Query to add transformed tables to</param>
/// <param name="ResultKeys">Keys data will be retrieved for</param>
public override void ExpandToMultipleTables(
QueryTable qt,
Query q,
List <string> resultKeys)
{
MetaTable mt2;
QueryTable qt2;
QueryColumn qc2;
HashSet <string> geneDict = new HashSet <string>();
string sql;
string geneSymbol;
int t0 = TimeOfDay.Milliseconds();
// Build query & get set of gene symbols
Query q2 = new Query();
q2.SingleStepExecution = true;
q2.KeyCriteria = q2.KeyCriteriaDisplay = // keylist to return (may want to include all target summary option criteria)
" in (" + MqlUtil.FormatValueListString(resultKeys, true) + ")";
qt2 = qt.Clone();
qt2.SelectKeyOnly();
qc2 = qt2.GetQueryColumnByNameWithException("gene_symbol");
qc2.Selected = true;
q2.AddQueryTable(qt2);
QueryEngine qe2 = new QueryEngine();
qe2.ExecuteQuery(q2);
while (true)
{
object[] vo = qe2.NextRow();
if (vo == null)
{
qe2.Close();
break;
}
if (qe2.Cancelled)
{
qe2.Close();
return;
}
geneSymbol = vo[2] as string;
if (Lex.IsNullOrEmpty(geneSymbol))
{
continue;
}
geneDict.Add(geneSymbol.ToUpper());
}
string[] sa = new string[geneDict.Count];
geneDict.CopyTo(sa);
Array.Sort(sa);
foreach (string s0 in sa)
{
geneSymbol = s0;
string mt2Name = MultiDbAssayDataNames.BasePivotTablePrefix + geneSymbol;
//if (QueryEngine.FilterAllDataQueriesByDatabaseContents &&
// !MetaTableCollection.IsMetaTableInContents(mtName)) continue; // metatable must be in contents
mt2 = MetaTableCollection.Get(mt2Name);
if (mt2 == null)
{
continue; // in case can't allocate for some reason
}
qt2 = q.GetQueryTableByName(mt2.Name); // see if already in query
if (qt2 != null)
{
continue; // ignore if already there
}
qt2 = new QueryTable(q, mt2); // allocate new query table & add to query
qt2.DeselectAll();
foreach (QueryColumn qc0 in qt.QueryColumns) // set selected columns to match those in the source qt
{
if (qc0.Selected && qt2.GetQueryColumnByName(qc0.MetaColumn.Name) != null)
{
qt2.GetQueryColumnByName(qc0.MetaColumn.Name).Selected = true;
}
}
if (qt.HeaderBackgroundColor != Color.Empty)
{
qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor;
}
}
t0 = TimeOfDay.Milliseconds() - t0;
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;
}