/// <summary>
/// Add sql for query to list of total sql for query
/// </summary>
/// <param name="mb"></param>
/// <param name="sql"></param>
internal void AddSqlToSqlStatementList(
GenericMetaBroker mb,
string sql)
{
string sqlWithDbLinks = sql;
DbConnectionMx mxConn = DbConnectionMx.MapSqlToConnection(ref sqlWithDbLinks); // add any dblinks as necessary
if (SqlList == null)
{
SqlList = new List <List <string> >();
}
List <string> sli = new List <string>();
if (Lex.IsDefined(mb.Label))
{
sli.Add(mb.Label);
}
else
{
sli.Add(mb.Qt.ActiveLabel + " (" + mb.Qt.MetaTable.Name + ")");
}
string formattedSql = OracleMx.FormatSql(sqlWithDbLinks);
sli.Add(formattedSql);
SqlList.Add(sli);
return;
}
/// <summary>
/// Get the specific broker, allocating as needed
/// </summary>
/// <param name="mt"></param>
GenericMetaBroker GSP(
MetaTable mt)
{
if (SpecificBroker == null) // need to allocate?
{
string sbType = mt.TableMap; // metatable map contains is of specific broker type
if (Lex.Eq(sbType, "Mobius.Tools.SarLandscape"))
{
SpecificBroker = new SasMapMetaBroker();
}
else if (Lex.StartsWith(sbType, "SmallWorld"))
{
SpecificBroker = new GenericMetaBroker();
}
else
{
throw new QueryException("Unrecognized NoSql data source type: " + sbType);
}
}
return(SpecificBroker); // already allocated?
}
string MetaDataUnits = ""; // units if available from metadata
/// <summary>
/// CalculateAndStoreClass
/// </summary>
/// <param name="vo"></param>
/// <returns></returns>
public StringMx CalculateAndStoreClass(
GenericMetaBroker mb,
object[] vo)
{
Mb = mb;
Qt = mb.Qt;
Vo = vo;
if (!VoPositionsInitialized)
{
GetVoPositions();
}
if (ActivityClassVoi < 0)
{
return(null);
}
ExtractValues();
StringMx sx = AssignClass();
return(sx);
}
/// <summary>
/// Select a Molecule object for a compound id
/// </summary>
/// <param name="cid"></param>
/// <param name="mt"></param>
/// <returns></returns>
public static MoleculeMx SelectMoleculeForCid(
string cid,
MetaTable mt = null)
{
MetaColumn strMc = null;
string mtName = null, keyColName, strColExpr, chimeString;
MoleculeMx cs;
if (cid == null || cid == "")
{
return(null);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(null);
}
//if (mt != null) mtName = mt.Name; // debug
bool isUcdb = (mt != null && mt.Root.IsUserDatabaseStructureTable); // user compound database
if (isUcdb) // if root metatable is user database then normalize based on key
{
mt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, mt);
}
else
{
cid = CompoundId.Normalize(cid, mt);
cs = MoleculeCache.Get(cid); // see if in cache
if (cs != null)
{
return(cs);
}
mt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (mt != null && Lex.Eq(mt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
mt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (mt == null)
{
return(null);
}
strMc = mt.FirstStructureMetaColumn; //.getmt.GetMetaColumnByName("molstructure");
if (strMc == null)
{
return(null);
}
cid = CompoundId.NormalizeForDatabase(cid, mt);
if (String.IsNullOrEmpty(cid))
{
return(null);
}
// Call external method to select structure
if (strMc.ColumnMap.StartsWith("InternalMethod", StringComparison.OrdinalIgnoreCase) ||
strMc.ColumnMap.StartsWith("PluginMethod", StringComparison.OrdinalIgnoreCase))
{ // call external method to get structure
List <MetaColumn> selectList = new List <MetaColumn>();
selectList.Add(mt.KeyMetaColumn);
selectList.Add(strMc);
object[] vo = new object[2];
vo[0] = cid;
try { GenericMetaBroker.CallLateBindMethodToFillVo(vo, 1, mt, selectList); }
catch (Exception ex)
{ return(null); }
if (vo[1] is MoleculeMx)
{
cs = (MoleculeMx)vo[1];
}
else if (vo[1] is string)
{
cs = MoleculeMx.MolStringToMoleculeMx((string)vo[1]);
}
else
{
cs = null;
}
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
return(cs);
}
// Normal case
//if (HelmEnabled) // temp till server back
//{
// cs = new MoleculeMx();
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
// return cs;
//}
string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries)
strColExpr = strMc.ColumnMap;
if (strColExpr == "")
{
strColExpr = strMc.Name;
}
if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge
{
if (!Lex.Contains(tableMap, "chime(")) // if no chime expression
{
strColExpr = "chime(ctab)"; // then create one (gets clob)
}
strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters
}
keyColName = mt.KeyMetaColumn.ColumnMap;
if (keyColName == "")
{
keyColName = mt.KeyMetaColumn.Name;
}
DbType parmType = DbType.String;
object cidObj = cid;
if (mt.KeyMetaColumn.IsNumeric)
{
if (!Lex.IsInteger(cid))
{
return(null); // if numeric col be sure cid is numeric also
}
parmType = DbType.Int64;
cidObj = Int64.Parse(cid);
}
string sql =
"select " + strColExpr + " " +
"from " + tableMap + " " +
"where " + keyColName + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareParameterized(sql, parmType);
drd.ExecuteReader(cidObj);
if (!drd.Read() || drd.Rdr.IsDBNull(0))
{
drd.Dispose();
return(null);
}
string molString = drd.GetString(0);
drd.Dispose();
MoleculeMx.TrySetStructureFormatPrefix(ref molString, strMc.DataTransform); // add molString type if indicated by data transform
cs = MoleculeMx.MolStringToMoleculeMx(molString);
cs.StoreKeyValueInMolComments(strMc, cid);
if (!isUcdb)
{
MoleculeCache.AddMolecule(cid, cs);
}
//if (MoleculeMx.HelmEnabled == true && Lex.IsInteger(cid))
// MoleculeMx.SetMoleculeToTestHelmString(cid, cs);
return(cs);
}
catch (Exception ex)
{ // just log message & return;
DebugLog.Message("SelectMoleculeForCid Exception, Cid: " + cid + ", table: " + mt.Name + "\n" +
"sql: " + OracleMx.FormatSql(sql) + "\n" + DebugLog.FormatExceptionMessage(ex));
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
/// <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>
/// Create broker of specified type
/// </summary>
/// <param name="mt"></param>
/// <returns></returns>
public static GenericMetaBroker Create(
MetaBrokerType type)
{
GenericMetaBroker mb = null;
if (type == MetaBrokerType.Generic)
{
return(new GenericMetaBroker());
}
if (type == MetaBrokerType.Pivot)
{
PivotMetaBroker pivotMb = new PivotMetaBroker();
mb = (PivotMetaBroker)pivotMb;
return(mb);
}
if (type == MetaBrokerType.Annotation)
{
AnnotationMetaBroker aMb = new AnnotationMetaBroker();
mb = (GenericMetaBroker)aMb;
return(mb);
}
if (type == MetaBrokerType.CalcField)
{
CalcFieldMetaBroker cfMb = new CalcFieldMetaBroker();
mb = (GenericMetaBroker)cfMb;
return(mb);
}
if (type == MetaBrokerType.CalcTable)
{
CalcTableMetaBroker ctMb = new CalcTableMetaBroker();
mb = (GenericMetaBroker)ctMb;
return(mb);
}
if (type == MetaBrokerType.MultiTable)
{
MultiTableMetaBroker mtMb = new MultiTableMetaBroker();
mb = (GenericMetaBroker)mtMb;
return(mb);
}
if (type == MetaBrokerType.TargetAssay)
{
QueryEngineLibrary.MultiDbAssayMetaBroker taMb = new MultiDbAssayMetaBroker();
mb = (GenericMetaBroker)taMb;
return(mb);
}
if (type == MetaBrokerType.Assay)
{
AssayMetaBroker assayMb = new AssayMetaBroker();
mb = (GenericMetaBroker)assayMb;
return(mb);
}
if (type == MetaBrokerType.UnpivotedAssay)
{
UnpivotedAssayMetaBroker urMb = new UnpivotedAssayMetaBroker();
mb = (GenericMetaBroker)urMb;
return(mb);
}
if (type == MetaBrokerType.SpotfireLink)
{
SpotfireLinkMetaBroker slMb = new SpotfireLinkMetaBroker();
mb = (GenericMetaBroker)slMb;
return(mb);
}
if (type == MetaBrokerType.NoSql)
{
NoSqlMetaBroker nsMb = new NoSqlMetaBroker();
mb = (GenericMetaBroker)nsMb;
return(mb);
}
if (type == MetaBrokerType.RgroupDecomp)
{
RgroupMetaBroker rgMb = new RgroupMetaBroker();
mb = (GenericMetaBroker)rgMb;
return(mb);
}
throw new QueryException("Unknown metabroker type " + type.ToString());
}
/// <summary>
/// Build the SQL for a query without executing it
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public List <List <string> > BuildSqlStatementList(
Query query)
{
MetaTable rootTable = null;
SqlList = new List <List <string> >();
ExecuteQuery(query, true, false); // build SQL for search step without actual execution
if (query.SingleStepExecution && DrivingTable != null && DrivingTable.MetaTable != null)
{
rootTable = DrivingTable.MetaTable;
}
else
{
rootTable = RootTables[0]; // just a single root for now
}
bool result = PrepareForRetrieval(rootTable, out KeysForRoot); // get sql for other tables to be retrieved from
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = MetaBrokerStateInfo;
for (int ti = 0; ti < Qtd.Length; ti++)
{
GenericMetaBroker mb = Qtd[ti].Broker as GenericMetaBroker;
QueryTable qt = mb.Qt;
MetaTable mt = qt.MetaTable;
// Create broker label to appear in SqlList
if (mb.PivotInCode)
{
if (!mb.IsKeyMultipivotBrokerInGroup)
{
continue;
}
string tableList = "";
for (int ti2 = 0; ti2 < Qtd.Length; ti2++) // get list of tables
{
GenericMetaBroker mb2 = Qtd[ti2].Broker as GenericMetaBroker;
if (!mb2.PivotInCode || mb2.MpGroupKey != mb.MpGroupKey)
{
continue;
}
if (tableList != "")
{
tableList += ", ";
}
tableList += mb2.Qt.MetaTable.Name;
}
mb.Label = "Retrieval SQL (Mobius-pivot-in-code) for metatable";
if (tableList.Contains(","))
{
mb.Label += "s";
}
mb.Label += ": " + tableList;
}
else
{
if (mt.MetaBrokerType == MetaBrokerType.Generic)
{
mb.Label = "Retrieval SQL for metatable: " + mt.Name;
}
else
{
mb.Label = "Retrieval SQL for metatable: " + mt.Name;
}
}
// Create SQL for broker and store in SqlList via call to AddSqlToSqlStatementList
mb.ExecuteQuery(Qtd[ti].Eqp);
}
return(SqlList);
}