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