/// <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 a metatable from a database catalog /// </summary> /// <param name="schemaDotTableName"></param> /// <returns></returns> public static MetaTable GetMetaTableFromDatabaseDictionary( string schemaDotTableName) { string[] sa = schemaDotTableName.Split('.'); if (sa.Length != 2) { return(null); } string schemaName = sa[0]; string tableName = sa[1]; DbSchemaMx schema = DbSchemaMx.GetSchemaInfoFromName(schemaName); if (schema == null) { return(null); // unknown schema } if (Lex.IsDefined(schema.AliasFor)) { schemaName = schema.AliasFor; } string dsName = schema.DataSourceName; DataSourceMx dataSource = DbConnectionMx.GetRootDataSource(schemaDotTableName); if (dataSource == null) { return(null); } DbConnectionMx conn = DbConnectionMx.GetConnection(dataSource.DataSourceName); if (dataSource.DbType == DatabaseType.MySql) { return(MySqlMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } else if (dataSource.DbType == DatabaseType.Oracle) { return(OracleMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } else if (dataSource.DbType == DatabaseType.ODBC) { return(OdbcMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName)); } throw new Exception("Database dictionary not supported for: " + schemaDotTableName); }
/// <summary> /// Select structures for a list of compound ids using a single SQL cursor. /// </summary> /// <param name="notInCache"></param> /// <param name="mt"></param> /// <param name="strMc"></param> static Dictionary <string, MoleculeMx> SelectChemicalStructuresForCorpIdList( List <string> cidList, MetaTable mt) { List <string> notInCacheKeyList; Dictionary <Int64, string> intKeyToStringKey; DbType parmType; MoleculeMx cs; Int64 intCid; string cid; int li; Stopwatch sw = Stopwatch.StartNew(); Dictionary <string, MoleculeMx> csDict = new Dictionary <string, MoleculeMx>(); List <string> notInCacheList = new List <string>(); // Get structures already in the cache for (li = 0; li < cidList.Count; li++) { cid = cidList[li]; if (RestrictedDatabaseView.KeyIsRetricted(cid)) { continue; } cid = CompoundId.Normalize(cid, mt); if (!Int64.TryParse(cid, out intCid)) { continue; // make sure an integer } if (MoleculeCache.Contains(cid)) // see if in cache { csDict[cid] = MoleculeCache.Get(cid); continue; } else { notInCacheList.Add(cid); } } // Retrieve structures from the database for those not in cache if (notInCacheList.Count == 0) { return(csDict); } MetaColumn strMc = mt.GetMetaColumnByName("molstructure"); if (strMc == null) { return(null); } string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries) string keyColName = mt.KeyMetaColumn.ColumnMap; if (Lex.IsUndefined(keyColName)) { keyColName = mt.KeyMetaColumn.Name; } string 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 } string sql = "select " + keyColName + ", " + strColExpr + " " + "from " + tableMap + " " + "where " + keyColName + " in (<list>)"; DbCommandMx drd = new DbCommandMx(); bool isNumericKey = (mt.KeyMetaColumn.IsNumeric); bool isStringKey = !isNumericKey; if (isStringKey) { parmType = DbType.String; } else { parmType = DbType.Int64; } try { drd.PrepareListReader(sql, parmType); drd.ExecuteListReader(notInCacheList); while (drd.Read()) { if (drd.Rdr.IsDBNull(0)) { continue; } if (isNumericKey) { intCid = drd.GetLong(0); cid = intCid.ToString(); } else // string cid { cid = drd.GetString(0); } cid = CompoundId.Normalize(cid, mt); string molString = drd.GetString(1); cs = new MoleculeMx(MoleculeFormat.Chime, molString); csDict[cid] = cs; MoleculeCache.AddMolecule(cid, cs); } drd.Dispose(); int msTime = (int)sw.ElapsedMilliseconds; return(csDict); } catch (Exception ex) { if (drd != null) { drd.Dispose(); } throw new Exception( "SelectStructuresForCorpIdList, table: " + mt.Name + "\n" + "sql: " + OracleMx.FormatSql(sql) + "\n"); } }
/// <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 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); }