TextReader GetAssayXml(
string aid)
{
TextReader rdr = null;
DbCommandMx dao = null;
string xml;
// Try to get from file first
if (PubChemAssayDirectory == null)
{
PubChemAssayDirectory = ServicesIniFile.Read("PubChemAssayDirectory");
}
if (PubChemAssayDirectory != "")
{
try
{
string fileName = PubChemAssayDirectory + @"\CSV\Description\" + aid + ".descr.xml";
StreamReader sr = new StreamReader(fileName);
xml = sr.ReadToEnd();
sr.Close();
if (String.IsNullOrEmpty(xml))
{
return(null);
}
return(new StringReader(xml));
}
catch (Exception ex) { }
}
// Try to get from table (new method)
string sql =
"select xml " +
"from mbs_owner.mbs_pbchm_assy_xml " +
"where aid = :0";
try
{
dao = new DbCommandMx();
dao.PrepareParameterized(sql, DbType.String);
dao.ExecuteReader(aid);
if (!dao.Read())
{
throw new Exception("Not found");
}
xml = dao.GetString(0);
dao.Dispose();
}
catch (Exception ex)
{
dao.Dispose();
return(null);
}
if (String.IsNullOrEmpty(xml))
{
return(null);
}
return(new StringReader(xml));
}
/// <summary>
/// Lookup a query by matching the supplied substring against the query name
/// </summary>
/// <param name="querySubString"></param>
/// <param name="msg"></param>
/// <returns></returns>
public static int FindQueryByNameSubstring(string querySubString, out string msg)
{
string objNm, ownrId, fldrNm;
int objId = -1, exactThisUser = -1, exactOtherUser = -1;
string sql =
"select obj_id, obj_nm, ownr_id, fldr_nm " +
"from mbs_owner.mbs_usr_obj " +
"where obj_typ_id = " + (int)UserObjectType.Query + " and lower (obj_nm) like lower('%" + querySubString + "%')";
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
msg = "";
System.Text.StringBuilder sb = new System.Text.StringBuilder();
int rowCnt = 0;
while (dao.Read())
{
objId = dao.GetInt(0);
objNm = dao.GetString(1);
ownrId = dao.GetString(2);
fldrNm = dao.GetString(3);
sb.AppendLine(objId + ": " + objNm + "." + ownrId + "." + fldrNm);
if (Lex.Eq(objNm, querySubString)) // exact match?
{
if (Lex.Eq(ownrId, Security.UserName))
{
if (exactThisUser == -1)
{
exactThisUser = objId;
}
else
{
exactThisUser = -2;
}
}
else
{
if (exactOtherUser == -1)
{
exactOtherUser = objId;
}
else
{
exactOtherUser = -2;
}
}
}
rowCnt++;
}
dao.Dispose();
if (rowCnt == 1)
{
return(objId); // if just one hit return its id
}
else if (exactThisUser > 0)
{
return(exactThisUser);
}
else if (exactThisUser == -1 && exactOtherUser > 0)
{
return(exactOtherUser);
}
else
{
if (rowCnt == 0)
{
msg = "No queries found";
}
else if (rowCnt > 1)
{
msg = "Multiple matches:\n\n" + sb.ToString();
}
return(-1);
}
}
/// <summary>
/// Get Assay data including biological target(s) & gene(s) for a single Assay or all Assays
/// </summary>
/// <param name="assayId">Assay to get data for or -1 to get for all assays</param>
/// <returns></returns>
public static Dictionary <int, AssayDbMetadata> GetAssayDbDict
(int assayId)
{
if (ForceAssayId)
{
assayId = ForcedAssayId;
}
else if (AssayTargetGeneDataDict != null)
{
return(AssayTargetGeneDataDict);
}
string sql = @"
SELECT <columns>
FROM <tables>
WHERE 1 = 1
and <conditions>" ;
DbCommandMx drd = new DbCommandMx();
if (assayId > 0) // get for single assay
{
sql = sql.Replace("1=1", "<keyColExpr> = :0");
drd.PrepareParameterized(sql, DbType.Int32);
drd.ExecuteReader(assayId);
}
else // get for all assays
{
drd.Prepare(sql);
drd.ExecuteReader();
}
Dictionary <int, AssayDbMetadata> dict = new Dictionary <int, AssayDbMetadata>();
if (ForceAssayId)
{
AssayTargetGeneDataDict = dict;
}
AssayDbMetadata assay = null;
AssayDbTarget target = null;
int currentAssayId = -1;
int currentTrgtId = -1;
while (true)
{
if (!drd.Read())
{
break;
}
assayId = drd.GetInt(0);
string assayName = drd.GetString(1);
string MthdVrsnMdTxt = drd.GetString(2);
if (currentAssayId < 0 || currentAssayId != assayId)
{
assay = new AssayDbMetadata();
assay.AssayId = assayId;
assay.Name = assayName;
if (Lex.Eq(MthdVrsnMdTxt, "Single Point")) // translate values
{
assay.SP = true;
}
else if (Lex.Eq(MthdVrsnMdTxt, "Conc./Dose Response Curve"))
{
assay.CRC = true;
}
dict[assayId] = assay;
currentAssayId = assayId;
currentTrgtId = -1;
}
int trgtId = drd.GetInt(3); // target id
if (currentTrgtId < 0 || currentTrgtId != trgtId)
{ // new target
target = new AssayDbTarget();
target.TargetId = trgtId;
target.TargetName = drd.GetString(4); // target name
target.TargetDesc = drd.GetString(5); // target desc
target.TargetTypeName = drd.GetString(6); // e.g. G protein coupled receptor
target.TargetTypeShortName = drd.GetString(7); // e.g. GPCR
assay.Targets.Add(target);
currentTrgtId = trgtId;
}
string geneId = drd.GetString(8);
if (String.IsNullOrEmpty(geneId))
{
continue;
}
AssayDbGene gene = new AssayDbGene();
gene.GeneId = geneId;
gene.GeneSymbol = drd.GetString(9);
target.Genes.Add(gene);
}
drd.Dispose();
return(dict);
}
/// <summary>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}
/// <summary>
/// Return description for table.
/// May be html or simple text
/// </summary>
/// <param name="mt"></param>
/// <returns></returns>
public static TableDescription GetTableDescription(
MetaTable mt)
{
DateTime dt;
bool firstRow;
double d1;
int assayId, i1;
string geneSymbol, geneId, geneLinkUrl = "", html = "", url, txt;
TableDescription td = new TableDescription();
try
{
string templateFile = CommonConfigInfo.MiscConfigDir + @"<templateName>";
StreamReader sr = new StreamReader(templateFile);
html = sr.ReadToEnd();
sr.Close();
}
catch (Exception ex)
{
td.TextDescription = "Error: " + ex.Message;
return(td);
}
try
{ geneLinkUrl = ServicesIniFile.Read("LsgEntrezGeneUrlTemplate"); }
catch (Exception ex) { }
try { assayId = Convert.ToInt32(mt.Code); }
catch (Exception ex) { return(null); }
AssayDbMetadata assay = AssayMetadataDao.GetAssayTargetGeneData(assayId); // get biological target(s) & gene(s) for assay
string btStr = "";
string gsStr = "";
List <AssayDbTarget> targets = assay.Targets;
foreach (AssayDbTarget target in assay.Targets)
{
string bt = target.TargetName;
if (bt == "")
{
continue;
}
bt += "<Target_Link>";
if (target.TargetDesc != "")
{
bt += " - " + target.TargetDesc;
}
if (btStr.IndexOf(bt) < 0) // new bio target
{
if (gsStr != "")
{
gsStr = " (" + gsStr + ")";
}
btStr = btStr.Replace("<Target_Link>", gsStr);
gsStr = "";
btStr += "<br> " +
bt;
}
if (target.Genes != null && target.Genes.Count > 0)
{ // add link to gene if appropriate
AssayDbGene gene = target.Genes[0]; // just first gene for now
geneSymbol = gene.GeneSymbol;
geneId = gene.GeneId;
geneLinkUrl =
"http:////Mobius/command?ShowContextMenu:TargetContextMenu(<Gene_Link>)";
if (!String.IsNullOrEmpty(geneLinkUrl))
{ // include link to gene description
if (Lex.IsDefined(geneId))
{
url = geneLinkUrl.Replace("<Gene_Link>", geneId); // link on gene id
}
else
{
url = geneLinkUrl.Replace("<Gene_Link>", gene.GeneSymbol); // link on symbol
}
geneSymbol = // define tag to open in new window
"<a href=\"" + url + "\">" + geneSymbol + "</a>";
}
if (gsStr != "")
{
gsStr += ", "; // same bt additional gene
}
gsStr += geneSymbol;
}
}
if (gsStr != "")
{
gsStr = " (" + gsStr + ")";
}
btStr = btStr.Replace("<Target_Link>", gsStr); // plug in last gene symbol
html = html.Replace("blgcl_trgt_text", btStr);
// Method type & descriptors
string methodDescrSql = @"
SELECT <columns>
FROM <tables>
WHERE <criteria>
ORDER BY LOWER(<orderCols>)";
DbCommandMx drd = new DbCommandMx();
drd.PrepareParameterized(methodDescrSql, DbType.Int32);
assayId = Convert.ToInt32(mt.Code);
drd.ExecuteReader(assayId);
string mdStr = "";
firstRow = true;
while (true)
{
if (!drd.Read())
{
break;
}
if (firstRow) // get single assay parameters
{
firstRow = false;
}
string mdtn = "<methodTypeName>";
if (mdtn == "")
{
continue;
}
string bmdn = drd.GetStringByName("<methodDescriptionColumn>");
string bmddt = drd.GetStringByName("<methodDescriptionColumn2>");
mdtn = "<br> " +
mdtn + ": " + bmdn;
if (bmddt != "" && bmdn.ToLower().IndexOf(bmddt.ToLower()) < 0) // include any description if don't already have
{
mdtn += " - " + bmddt;
}
mdStr += mdtn;
}
html = html.Replace("<methodDescriptionPlaceHolder>", mdStr);
// Get Minimum Significant Results information
string msrSql = @"
SELECT <columns>
FROM <tables>
WHERE <conditions>" ;
// RDM MSR Sql (obsolete)
string msrSqlRdm = @"<todo>";
drd.PrepareParameterized(msrSql, DbType.Int32);
assayId = Convert.ToInt32(mt.Code);
drd.ExecuteReader(assayId);
while (true)
{
if (!drd.Read())
{
break; // should always get at least one row even if no MSR data
}
}
// Pharmacological action
// <todo>
// Research effort & therapeutic target
// <todo>
// Biological Conditions
// <todo>
// All done
drd.Dispose();
td.TextDescription = html;
return(td);
}
/// <summary>
/// Get approximation of assay type (binding/functional) and mode (agonist, antagonist, potentiator)
/// </summary>
/// <param name="assayTypeDict"></param>
/// <param name="assayModeDict"></param>
public static void GetAssayTypesAndModes(
out Dictionary <int, string> assayTypeDict,
out Dictionary <int, string> assayModeDict)
{
assayTypeDict = new Dictionary <int, string>();
assayModeDict = new Dictionary <int, string>();
string sql = @"
select <columns>
from <tables>
where 1=1
and <conditions>";
if (ForceAssayId)
{
sql = sql.Replace("1=1", "<keyCriteria> = " + ForcedAssayId);
}
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
bool readOk = true;
int assayId = -1, currentAssayId = -1;
string assayType = "", assayMode = "";
while (true)
{
if (drd.Read())
{
assayId = drd.GetInt(0);
}
else
{
readOk = false;
}
if (assayId != currentAssayId || !readOk)
{
if (currentAssayId > 0)
{
assayTypeDict[currentAssayId] = assayType;
assayModeDict[currentAssayId] = assayMode;
assayType = assayMode = "";
}
if (!readOk)
{
break;
}
currentAssayId = assayId;
}
}
drd.CloseReader();
drd.Dispose();
return;
}
/// <summary>
/// Execute structure search for structures stored in an Oracle text column
/// </summary>
/// <param name="sql">Basic sql to select key & structure</param>
public void ExecuteInternalOracleStructureColumnSearch(
string sql,
MoleculeFormat structureFormat)
{
string cid, molString;
object[] vo;
MoleculeMx cs;
bool match = false;
QueryColumn molsimQc = Eqp.QueryTable.GetSelectedMolsimQueryColumn(); // get any column to return similarity score in
DbCommandMx drd = new DbCommandMx();
if (Eqp.SearchKeySubset == null)
{
drd.Prepare(sql);
drd.ExecuteReader();
}
else // limit to list
{
sql += " where " + Eqp.QueryTable.MetaTable.KeyMetaColumn.Name + " in (<list>)";
drd.PrepareListReader(sql, DbType.String);
List <string> dbKeySubset = new List <string>();
foreach (string key in Eqp.SearchKeySubset)
{
string dbKey = CompoundId.NormalizeForDatabase(key, Eqp.QueryTable.MetaTable);
dbKeySubset.Add(dbKey);
}
drd.ExecuteListReader(dbKeySubset);
}
drd.CheckForCancel = Eqp.CheckForCancel; // set cancel check flag
StructureMatcher matcher = new StructureMatcher(); // allocate structure matcher
List <object[]> results = new List <object[]>();
int matchCount = 0;
while (drd.Read())
{
cid = drd.GetObject(0).ToString();
cid = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix as needed
molString = drd.GetString(1);
if (String.IsNullOrEmpty(molString))
{
continue;
}
cs = new MoleculeMx(structureFormat, molString);
if (Pssc.SearchType == StructureSearchType.Substructure)
{
if (matcher.IsSSSMatch(Pssc.Molecule, cs))
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
results.Add(vo);
}
}
else if (Pssc.SearchType == StructureSearchType.MolSim)
{
double score = matcher.Molsim(Pssc.Molecule, cs, Pssc.SimilarityType);
if (score >= Pssc.MinimumSimilarity)
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
if (vo.Length >= 2)
{
vo[1] = (float)score;
}
results.Add(vo);
}
}
else if (Pssc.SearchType == StructureSearchType.FullStructure)
{
if (matcher.FullStructureMatch(Pssc.Molecule, cs, Pssc.Options))
{
vo = new object[SelectList.Count];
vo[0] = CompoundId.Normalize(cid, Eqp.QueryTable.MetaTable); // normalize adding prefix
results.Add(vo);
}
}
matchCount++;
// if (matchCount >100) break; // debug
if (drd.Cancelled)
{
Eqp.Qe.Cancelled = true;
drd.Dispose();
Results = null;
return;
}
}
drd.Dispose();
Results = results;
return;
}
/// <summary>
/// Build platform / target / assay metatree
/// </summary>
public static void BuildPlatformMetaTree()
{
string assayId, assayDb;
string sql = @"
select gene_fmly, gene_symbl, assy_nm, assy_db, assy_id_txt
from mbs_owner.cmn_assy_atrbts
where gene_fmly is not null and gene_symbl is not null and assy_nm is not null
order by lower(gene_fmly), lower(gene_symbl), lower(assy_nm)" ;
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
try
{
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
string family = "";
string targetSymbol = "";
string assayName = "";
MetaTreeNode familyMtn = null, targetMtn = null, tsMtn = null, assayMtn = null;
while (drd.Read())
{
string nextFamily = drd.GetString(0);
if (Lex.Ne(family, nextFamily)) // get current family node
{
family = nextFamily;
string familyNodeName = "GFTA_" + family.Replace(" ", "_"); // build name to match node
familyMtn = MetaTreeFactory.GetNode(familyNodeName);
}
string nextTargetSymbol = drd.GetString(1);
// if (Lex.Eq(nextTargetSymbol, "Adr1a")) nextTargetSymbol = nextTargetSymbol; // debug
if (Lex.Ne(targetSymbol, nextTargetSymbol)) // add new target node if going to new target
{
targetSymbol = nextTargetSymbol;
targetMtn = new MetaTreeNode();
targetMtn.Type = MetaTreeNodeType.Target;
targetMtn.Name = "TGT_ASSYS_" + targetSymbol.ToUpper();
targetMtn.Label = targetSymbol;
targetMtn.Target = targetMtn.Name;
MetaTreeFactory.AddNode(targetMtn);
if (familyMtn != null)
{
familyMtn.Nodes.Add(targetMtn);
}
tsMtn = new MetaTreeNode(); // add node for summary view by gene symbol
tsMtn.Type = MetaTreeNodeType.MetaTable;
tsMtn.Name = MultiDbAssayDataNames.BasePivotTablePrefix + targetSymbol.ToUpper();
tsMtn.Label = targetSymbol + " Assay Results Summary";
tsMtn.Target = tsMtn.Name;
MetaTreeNode mtn2 = MetaTreeFactory.GetNode(tsMtn.Name); // node with this name already exist?
if (mtn2 == null)
{
MetaTreeFactory.AddNode(tsMtn); // add to tree if doesn't exist
}
else
{
tsMtn = mtn2; // use existing node otherwise since this label doesn't have priority
}
if (targetMtn != null)
{
targetMtn.Nodes.Add(tsMtn);
}
}
string nextAssayName = drd.GetString(2);
if (Lex.Ne(assayName, nextAssayName))
{
assayName = nextAssayName;
assayDb = drd.GetString(3);
assayId = drd.GetString(4);
assayMtn = new MetaTreeNode();
assayMtn.Type = MetaTreeNodeType.MetaTable;
assayMtn.Name = assayId;
assayMtn.Target = assayMtn.Name;
assayMtn.Label = assayName;
MetaTreeNode mtn2 = MetaTreeFactory.GetNode(assayMtn.Name); // node with this name already exist?
if (mtn2 == null)
{
MetaTreeFactory.AddNode(assayMtn); // add to tree if doesn't exist
}
else
{
assayMtn = mtn2; // use existing node otherwise since this label doesn't have priority
}
if (targetMtn != null)
{
targetMtn.Nodes.Add(assayMtn);
}
}
}
drd.CloseReader();
drd.Dispose();
}
catch (Exception ex)
{
DebugLog.Message("TargetAssayMetafactory.Build Error:\r\n" + DebugLog.FormatExceptionMessage(ex));
return;
}
}
/// <summary>
/// See if a compound id exists
/// </summary>
/// <param name="cid"></param>
/// <param name="mt">MetaTable to check in or if null check based on prefix</param>
/// <returns></returns>
public static bool Exists(
string cid,
MetaTable mt)
{
DbDataReader dr = null;
MetaTable keyMt;
object obj;
bool result;
string table, prefix, suffix, cid2;
int number;
if (cid == null || cid == "")
{
return(false);
}
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
return(false);
}
if (mt != null && mt.Root.IsUserDatabaseStructureTable) // if root metatable is user database then normalize based on key
{
keyMt = mt.Root; // be sure we have root
cid = CompoundId.Normalize(cid, keyMt);
}
else // from non-user database, get key table based on prefix
{
cid = CompoundId.Normalize(cid, mt);
keyMt = CompoundId.GetRootMetaTableFromCid(cid, mt);
if (keyMt != null && Lex.Eq(keyMt.Name, "corp_structure") && MetaTableCollection.Get("corp_structure2") != null)
{
keyMt = MetaTableCollection.Get("corp_structure2"); // hack to search both small & large mols for Corp database
}
}
if (keyMt == null)
{
return(false);
}
if (cid.StartsWith("pbchm", StringComparison.OrdinalIgnoreCase))
{
return(true); // hack for pubchem until full set of compound ids available in Oracle
}
cid = CompoundId.NormalizeForDatabase(cid, keyMt); // get form that will match database
if (cid == null)
{
return(false);
}
string keyCol = keyMt.KeyMetaColumn.ColumnMap;
if (keyCol == "")
{
keyCol = keyMt.KeyMetaColumn.Name;
}
string sql =
"select " + keyCol +
" from " + keyMt.GetTableMapWithAliasAppendedIfNeeded() +
" where " + keyCol + " = :0";
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareMultipleParameter(sql, 1);
dr = drd.ExecuteReader(cid);
result = drd.Read();
}
catch (Exception ex)
{
// Don't log error since usually because of invalid format number
// DebugLog.Message("CompoundIdUtil.Exists - SQL Error:\n" + sql + "\n" + ex.Message);
drd.Dispose();
return(false);
}
if (result == true)
{
obj = dr.GetValue(0);
cid2 = CompoundId.Normalize(obj.ToString()); // need to normalize if number
// if (cid2 != CompoundId.Normalize(cid)) result=false;
}
dr.Close(); // need to close cursor
drd.Dispose();
return(result);
}