/// <summary>
/// Read a sample of a table for previewing
/// </summary>
/// <returns></returns>
List <string> GetPreviewSubset()
{
Dictionary <string, object> PreviewSubset = new Dictionary <string, object>();
MetaTable mt = Eqp.QueryTable.MetaTable;
string sql = "select /*+ first_rows */" + mt.KeyMetaColumn.ColumnMap + " from " +
GetSourceWithTableFilterCriteria(mt);
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
if (drd.IsNull(0))
{
continue;
}
string id = drd.GetObject(0).ToString();
string cid = CompoundId.Normalize(id); // normalize cid adding prefix as needed
PreviewSubset[id] = null;
if (PreviewSubset.Count >= 100)
{
break;
}
}
drd.Dispose();
return(new List <string>(PreviewSubset.Keys));
}
/// <summary>
/// Read next row for DbCommand
/// </summary>
/// <returns></returns>
public bool Read(DbCommandMx drd)
{
object o;
string key;
DateTime t0 = DateTime.Now;
while (true)
{
bool rowWasRead = drd.Read();
double dt = TimeOfDay.Delta(t0);
ReadRowTime += dt;
if (rowWasRead)
{
ReadRowCount++;
}
else
{
return(false); // read failed
}
HashSet <string> keysToExclude = (Query != null ? Query.KeysToExclude : null);
if (keysToExclude != null && keysToExclude.Count > 0 && drd.Cmd != null) // remove any keys to be excluded from keySubset
{
try
{
o = drd.GetObject(0); // assume key is in first position
if (TryGetKeyValueString(o, out key) && Lex.IsDefined(key) &&
keysToExclude.Contains(key))
{
ReadRowsFilteredByKeyExclusionList++;
continue;
}
}
catch (Exception ex) { return(rowWasRead); } // accept record as is
}
return(rowWasRead);
}
}
/// <summary>
/// Read in assay attribute information
/// </summary>
/// <returns></returns>
public static AssayDict ReadAssayAttributesTable()
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = MetaTableCollection.Get(AssayAttrsTableName);
if (mt == null)
{
throw new Exception("Can't get table " + AssayAttrsTableName);
}
UnpivotedAssayResultFieldPositionMap voMap = new UnpivotedAssayResultFieldPositionMap(); // used for fast indexing of value by col name
string fList = "";
for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
{
string colMap = mt.MetaColumns[mci].ColumnMap;
voMap.TrySetVoi(colMap, mci);
if (fList != "")
{
fList += ", ";
}
fList += colMap;
}
string sql =
"select " + fList + " " +
"from mbs_owner.cmn_assy_atrbts " +
"order by lower(gene_fmly), lower(gene_symbl), lower(assy_nm)";
// sql = AdjustAssayAttrsTableName(sql);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
AssayDict dict = new AssayDict();
int readCnt = 0;
while (true)
{
if (!dao.Read())
{
break;
}
//if (readCnt > 100) break; // debug !!!
readCnt++;
object[] vo = new object[mt.MetaColumns.Count];
for (int mci = 0; mci < mt.MetaColumns.Count; mci++)
{
vo[mci] = dao.GetObject(mci);
}
AssayAttributes row = AssayAttributes.FromValueObjectNew(vo, voMap);
dict.AssayList.Add(row);
dict.SetMaps(row);
}
dao.CloseReader();
dao.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(dict);
}
/// <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;
}