/// <summary>
/// Read dictionary from Oracle table
/// </summary>
/// <param name="dict"></param>
/// <returns></returns>
static DictionaryMx ReadDictionaryFromOracle(
DictionaryMx dict)
{
DbCommandMx drd = null;
int t0, t1, i1;
try
{
//if (Lex.Eq(dict.Name, "DIT_PERSON")) dict = dict; // debug
DbDataReader dr = null;
drd = new DbCommandMx();
drd.Prepare(dict.Sql);
dr = drd.ExecuteReader();
t0 = TimeOfDay.Milliseconds();
while (drd.Read())
{
if (!dr.IsDBNull(0))
{
string word = dr.GetValue(0).ToString();
if (Lex.IsNullOrEmpty(word))
{
continue;
}
string definition = null;
if (!dr.IsDBNull(1))
{
definition = dr.GetValue(1).ToString();
}
dict.Add(word, definition);
t1 = TimeOfDay.Milliseconds();
// if (t1-t0 > 2000) break; // limit time for development
}
}
drd.CloseReader();
drd.Dispose();
t1 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("ReadDictionaryFromOracle " + dict.Name + " Time: " + t1.ToString());
return(dict);
}
catch (Exception ex)
{
if (drd != null)
{
drd.Dispose();
}
return(null);
}
}
/// <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>
/// Get list of ACL roles
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static string[] GetACLRoles(string username)
{
string[] roles = null;
try
{
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter("SELECT acl_api_pkg.acl_get_user_roles_fnc(:0) FROM DUAL", 1);
DbDataReader odr = drd.ExecuteReader(username.ToUpper());
string response = null;
if (odr.Read())
{
response = odr.GetString(0);
}
drd.CloseReader();
drd.Dispose();
if (response != null && response.StartsWith("OK") && response.IndexOf("|") > 0)
{
roles = response.Substring(response.IndexOf("|") + 1).Split(new char[] { ' ', ';' });
}
}
catch (Exception ex)
{
//do nothing
}
return(roles);
}
/// <summary>
/// Debug check for disallowed duplicate insert for a given method, cid, result group and result type
/// </summary>
/// <param name="voList"></param>
/// <returns></returns>
bool CheckForDuplicateInsert(List <AnnotationVo> voList)
{
AnnotationVo vo = voList[0];
string sql0 = // see if this result value already exists
"SELECT * " +
" FROM mbs_owner.MBS_ADW_RSLT " +
" WHERE mthd_vrsn_id = " + vo.mthd_vrsn_id +
" and ext_cmpnd_id_nbr = " + vo.ext_cmpnd_id_nbr +
" and rslt_grp_id = " + vo.rslt_grp_id +
" and rslt_typ_id = " + vo.rslt_typ_id +
" and sts_id = 1";
DbCommandMx d0 = new DbCommandMx();
d0.Prepare(sql0);
d0.ExecuteReader();
bool exists = d0.Read();
d0.Dispose();
if (exists)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Select an Oracle Blob value
/// </summary>
/// <param name="table"></param>
/// <param name="matchCol"></param>
/// <param name="typeCol"></param>
/// <param name="contentCol"></param>
/// <param name="matchVal"></param>
public static void SelectOracleBlob(
string table,
string matchCol,
string typeCol,
string contentCol,
string matchVal,
out string typeVal,
out byte[] ba)
{
typeVal = null;
ba = null;
string sql =
"select " + typeCol + ", " + contentCol + " " +
"from " + table + " " +
"where " + matchCol + " = :0";
DbCommandMx drd = new DbCommandMx();
drd.PrepareMultipleParameter(sql, 1);
for (int step = 1; step <= 2; step++) // try two different forms of matchVal
{
if (step == 2) // try alternate form of spaces
{
if (matchVal.Contains("%20"))
{
matchVal = matchVal.Replace("%20", " "); // convert html spaces to regular spaces
}
else
{
matchVal = matchVal.Replace(" ", "%20"); // convert regular spaces to html spaces
}
}
drd.ExecuteReader(matchVal);
if (!drd.Read())
{
continue;
}
typeVal = drd.GetString(0);
if (drd.Rdr.IsDBNull(1))
{
break;
}
OracleBlob ob = drd.OracleRdr.GetOracleBlob(1);
if (ob != null && ob.Length >= 0)
{
ba = new byte[ob.Length];
ob.Read(ba, 0, (int)ob.Length);
}
break; // have value
}
drd.Dispose();
return;
}
/// <summary>
/// Get image coordinates
/// </summary>
/// <param name="targetMapXDict"></param>
/// <param name="targetMapYDict"></param>
public static void GetImageCoords(
out Dictionary <int, double> targetMapXDict,
out Dictionary <int, double> targetMapYDict)
{
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
string sql = @"
select entrezgene_id, x, y
from mdbassay_owner.image_coord" ;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
int geneId = drd.GetInt(0);
double x = drd.GetDouble(1);
targetMapXDict[geneId] = x;
double y = drd.GetDouble(2);
targetMapYDict[geneId] = y;
}
drd.CloseReader();
drd.Dispose();
return;
}
public static string SelectString(
DbConnectionMx mxConn,
string sql)
{
string value;
DbDataReader dr = null;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = mxConn; // set connection to use
drd.Prepare(sql);
dr = drd.ExecuteReader();
if (!dr.Read())
{
throw (new Exception("SelectString Read failed"));
}
if (dr.IsDBNull(0))
{
value = "";
}
else
{
value = dr.GetString(0);
}
dr.Close();
drd.Dispose();
return(value);
}
/// <summary>
/// Copy PRD Annotation Table data to DEV by groups of result ids
/// </summary>
/// <returns></returns>
public static string CopyPrdAnnotationTableDataToDevBy_Rslt_Id()
{
long minRi = 1;
//long minRi = 1310251601; // appro half of rows, allows index on all cols to be built
long maxRi = 4331096190;
long chunkSize = 100000;
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = DbConnectionMx.GetConnection("DEV857");
cmd.BeginTransaction();
long totalIns = 0;
for (long ri = minRi; ri <= maxRi; ri += chunkSize)
{
string range = (ri + 1).ToString() + " and " + (ri + chunkSize);
string sql =
@"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt
(select * from mbs_owner.mbs_adw_rslt
where rslt_id between " + range + ")";
cmd.PrepareUsingDefinedConnection(sql);
int insCnt = cmd.ExecuteNonReader();
cmd.Commit();
totalIns += insCnt;
string msg = "Rows copied: " + totalIns + " (" + range + ")";
Mobius.UAL.Progress.Show(msg);
DebugLog.Message(msg);
}
cmd.Dispose();
return("Rows copied: " + totalIns);
}
/// <summary>
/// Create the temp table for the list if needed
/// </summary>
/// <param name="schema"></param>
/// <param name="name"></param>
public static bool CreateListTableIfNeeded(
string dsName,
string listName)
{
DbCommandMx cmd = null;
bool created = true;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
if (Lists.ContainsKey(fullTableName))
{
return(false);
}
string sql = @"
create global temporary table <schema.name> (
rowPos integer,
intKey integer,
stringKey varchar2(256))
on commit preserve rows" ;
sql = sql.Replace("<schema.name>", fullTableName);
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
created = true;
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Create Table time: " + tDelta);
}
catch (Exception ex)
{
if (!ex.Message.Contains("ORA-xxx"))
{
throw ex; // if other than already exists then throw exception
}
created = false; // already exists
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
TempDbList list = new TempDbList();
list.DataSource = ds;
list.Name = listName;
Lists[fullTableName] = list;
return(created);
}
/// <summary>
/// Update copies of CMN_ASSY_ATRBTS in other Oracle instances
/// </summary>
/// <returns></returns>
static string UpdateCmnAssyAtrbtsCopies()
{
DbCommandMx dao;
string sql, msg = "", errMsg = "";
int cnt;
string[] dbList = new string[]
{
//"AssayDB1",
//"AssayDB2",
//"...",
};
foreach (string conName in dbList)
{
try
{
dao = new DbCommandMx();
dao.MxConn = DbConnectionMx.GetConnection(conName);
sql = "delete from MBS_OWNER.CMN_ASSY_ATRBTS";
dao.PrepareUsingDefinedConnection(sql);
cnt = dao.ExecuteNonReader();
sql = @"
insert into MBS_OWNER.CMN_ASSY_ATRBTS
select * from MBS_OWNER.CMN_ASSY_ATRBTS" ;
dao.PrepareUsingDefinedConnection(sql);
cnt = dao.ExecuteNonReader();
dao.Commit();
dao.Dispose();
if (msg != "")
{
msg += ", ";
}
msg += conName;
}
catch (Exception ex)
{
errMsg += "Error updating " + conName + ": " + ex.Message + "\r\n";
}
}
if (msg != "")
{
msg = "Updated copies of CMN_ASSY_ATRBTS in: " + msg;
}
if (errMsg != "")
{
msg += "\r\n" + errMsg;
}
return(msg);
}
/// <summary>
/// Calculate & persist library stats
/// </summary>
/// <returns></returns>
public static string UpdateLibraryStatistics()
{
Dictionary <string, MetaTableStats> stats = new Dictionary <string, MetaTableStats>();
int libId;
long cnt;
DateTime dt;
string libName, txt;
try
{
DbCommandMx dao = new DbCommandMx();
string sql = // get count and date (must use crt_timestamp) for each library
@"
select
l.lib_id,
count(*),
l.crt_timestamp
from
corp_owner.corp_library l,
corp_owner.corp_library_substance ls
where ls.lib_id (+) = l.lib_id
group by l.lib_id, l.crt_timestamp
order by l.lib_id" ;
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
libId = dao.GetInt(0);
libName = "LIBRARY_" + libId;
if (!stats.ContainsKey(libName))
{
stats[libName] = new MetaTableStats();
}
cnt = dao.GetLong(1);
stats[libName].RowCount = cnt;
dt = dao.GetDateTime(2);
stats[libName].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats";
MetaTableFactory.WriteMetaTableStats(stats, fileName);
}
catch (Exception ex)
{
throw ex;
}
return("Updated statistics for " + stats.Count + " libraries");
}
/// <summary>
/// Store Spotfire SQL and associated fields returning the sequence number of the row
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int InsertSpotfireSql(
string name,
int version,
string sqlStmt,
string keyColName,
string keys,
string owner)
{
int v0 = version;
if (version < 0)
{
version = SequenceDao.NextVal("dev_mbs_owner.mbs_spotfire_sql_seq");
if (v0 == -2) // append version to name
{
name += version;
}
}
else
{
DeleteSqlStatement(name, version); // delete if matching version
}
string sql = @"
insert into dev_mbs_owner.mbs_spotfire_sql
(name, version, sql, key_col_name, keys, ownr_id)
values (:0,:1,:2,:3,:4,:5)" ;
DbCommandMx drDao = new DbCommandMx();
OracleDbType[] pa = new OracleDbType[6];
pa[0] = OracleDbType.Varchar2;
pa[1] = OracleDbType.Int32;
pa[2] = OracleDbType.Clob;
pa[3] = OracleDbType.Varchar2;
pa[4] = OracleDbType.Varchar2;
pa[5] = OracleDbType.Varchar2;
drDao.Prepare(sql, pa);
object[] p = new object[6];
p[0] = name;
p[1] = version;
p[2] = sqlStmt;
p[3] = keyColName;
p[4] = keys;
p[5] = owner;
int count = drDao.ExecuteNonReader(p); // insert the row
drDao.Dispose();
return(version);
}
/// <summary>
/// Retrieve target Entrez gene id from symbol
/// </summary>
/// <param name="targetSymbol"></param>
/// <returns></returns>
public static int TargetSymbolToId(
string targetSymbol)
{
string sql =
@"select entrezgene_id
from <table>
where upper(gene_symbol) = :0" ;
DbCommandMx d = new DbCommandMx();
d.PrepareParameterized(sql, DbType.String);
d.ExecuteReader(targetSymbol.ToUpper());
if (!d.Read())
{
d.Dispose();
throw new Exception("Target Symbol not found: " + targetSymbol);
}
int targetId = d.GetInt(0);
d.Dispose();
return(targetId);
}
/// <summary>
/// /// <summary>
/// Copy PRD Annotation Table data to DEV reording and compressing the data by method
/// </summary>
/// <returns></returns>
public static string CopyPrdAnnotationTableDataToDev()
{
string mvidSql = @"
select mthd_vrsn_id, count from
(
select mthd_vrsn_id, count(mthd_vrsn_id) count
FROM MBS_OWNER.mbs_adw_rslt
WHERE mthd_vrsn_id > 0 /* = 708070 */
group by mthd_vrsn_id)
order by mthd_vrsn_id
";
Mobius.UAL.Progress.Show("Getting list of annotation table mtht_vrsn_ids ...");
DbCommandMx c1 = new DbCommandMx();
c1.Prepare(mvidSql);
c1.ExecuteReader();
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = DbConnectionMx.GetConnection("DEV857");
cmd.BeginTransaction();
long totalIns = 0;
int vmIdCnt = 0;
while (c1.Read())
{
long mvId = c1.GetLong(0);
string sql =
@"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt
select * from mbs_owner.mbs_adw_rslt
where mthd_vrsn_id = " + mvId + @"
order by ext_cmpnd_id_nbr, rslt_grp_id, rslt_id";
cmd.PrepareUsingDefinedConnection(sql);
int insCnt = cmd.ExecuteNonReader();
cmd.Commit();
totalIns += insCnt;
vmIdCnt++;
string msg = "Mthd_Vrsn_id: " + mvId + ", Vmids: " + vmIdCnt + ", Rows: " + totalIns;
Mobius.UAL.Progress.Show(msg);
DebugLog.Message(msg);
}
c1.Dispose();
cmd.Dispose();
return("Rows copied: " + totalIns);
}
/// <summary>
/// Retrieve theoretical elemental composition value from Cassper database
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public object GetElementalAnalysisPct(
string cassperId,
string aSymbol)
{
DbCommandMx drd = new DbCommandMx();
string sql =
"select comp_pct " +
"from cas_owner.cas_structure " +
"where cassper_id = :0";
drd.PrepareMultipleParameter(sql, 1);
drd.ExecuteReader(cassperId);
if (!drd.Read() || drd.IsNull(0))
{
drd.Dispose();
return(null);
}
string s = drd.GetString(0);
drd.Dispose();
int i1 = s.ToLower().IndexOf(aSymbol.ToLower()); // find atom symbol
if (i1 < 0)
{
return(null);
}
s = s.Substring(i1 + 2); // get following number
i1 = s.IndexOf(" "); // and any following space
if (i1 >= 0)
{
s = s.Substring(0, i1);
}
return(Double.Parse(s));
}
/// <summary>
/// Get the next value for the sequence (Oracle)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongOracle(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
if (seqDao.CacheSize <= 0)
{
sql = "select " + seqName + ".nextval from dual";
}
else
{
sql = "select /*+ first_rows */ " + seqName + ".nextval from sys.all_catalog where rownum <= " +
(seqDao.CacheSize + 1).ToString();
}
int t0 = TimeOfDay.Milliseconds();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
if (!drd.Read())
{
throw (new Exception("SequenceDao.NextVal Read failed"));
}
nextVal = drd.GetLong(0); // return this one
while (drd.Read()) // enqueue the rest
{
seqQueue.Enqueue(drd.GetLong(0));
}
drd.CloseReader();
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// Read the list of compounds for a library
/// </summary>
/// <param name="libId"></param>
/// <returns></returns>
public static CidList ReadLibrary(
int libId)
{
CidList l = new CidList();
DbCommandMx dao = new DbCommandMx();
string sql = @"
SELECT l.library_name, l.library_desc_text, s.corp_nbr
FROM corp_owner.corp_substance s,
corp_owner.corp_library_substance ls,
corp_owner.corp_library l
WHERE
l.lib_id = <libId> and
s.cpd_id = ls.cpd_id and
l.lib_id = ls.lib_id" ;
sql = Lex.Replace(sql, "<libId>", libId.ToString());
dao.Prepare(sql);
dao.ExecuteReader();
MetaTable rootMt = MetaTableCollection.Get(MetaTable.PrimaryRootTable);
while (dao.Read())
{
if (Lex.IsNullOrEmpty(l.UserObject.Name))
{
string name = dao.GetString(0);
if (Lex.IsNullOrEmpty(name) || Lex.IsInteger(name))
{
name = dao.GetString(1); // use desc if no name or just a number
}
if (Lex.IsNullOrEmpty(name))
{
name = "Library " + libId;
}
l.UserObject.Name = name;
}
int intCorpId = dao.GetInt(2);
string corpId = CompoundId.Normalize(intCorpId, rootMt);
l.Add(corpId);
}
dao.CloseReader();
dao.Dispose();
return(l);
}
/// <summary>
/// Read log object given the object id
/// </summary>
/// <param name="objectId"></param>
/// <returns></returns>
public static UserObject Read( // read object
int objectId) // id of item to read
{
string sql =
"select obj_id, obj_typ_id, ownr_id, obj_nm, obj_desc_txt, " +
"fldr_typ_id, fldr_nm, acs_lvl_id, obj_itm_cnt, obj_cntnt, crt_dt, upd_dt " +
"from mbs_owner.mbs_log where obj_id=:0";
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql, OracleDbType.Int32);
drd.ExecuteReader(objectId);
UserObject uo = UserObjectDao.FetchUserObject(drd);
drd.Dispose();
return(uo);
}
/// <summary>
/// Get dict of assay result types
/// </summary>
/// <param name="assayId"></param>
/// <returns></returns>
public static Dictionary <int, int> GetExpectedAssayResultTypes(int assayId)
{
Dictionary <int, int> dict = new Dictionary <int, int>();
DbCommandMx drd = new DbCommandMx();
string sql = AssayResultType.SelectExpectedResultTypesSqlTemplate;
drd.PrepareMultipleParameter(sql, 1); // (takes ~700 ms first time)
drd.ExecuteReader(assayId);
while (drd.Read())
{
int resultType = drd.GetIntByName("<resultTypeId>");
int secondaryResultType = drd.GetIntByName("<secondaryResultTypeId");
dict[secondaryResultType] = resultType;
}
drd.Dispose();
return(dict);
}
/// <summary>
/// Check that the specified calculated field expression is valid
/// </summary>
/// <param name="advExpr"></param>
/// <returns></returns>
public static string ValidateCalculatedFieldExpression(
string advExpr)
{
try
{
CalcFieldMetaBroker mb = new CalcFieldMetaBroker();
string sql = mb.BuildAdvancedExpressionSql(advExpr);
sql = "select * from (" + sql + ") where 1=2"; // wrap in some sql for rapid evaluation
DbCommandMx dao = new DbCommandMx();
DateTime t0 = DateTime.Now;
dao.Prepare(sql);
dao.ExecuteReader();
dao.Dispose();
double ms = TimeOfDay.Delta(t0);
return("");
}
catch (Exception ex)
{
return(ex.Message);
}
}
/// <summary>
/// Be sure the table is truncated
/// </summary>
/// <param name="schema"></param>
/// <param name="listName"></param>
public static void TruncateListTable(
string dsName,
string listName)
{
DbCommandMx cmd = null;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
string sql = "truncate table " + fullTableName;
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Truncate Table time: " + tDelta);
}
catch (Exception ex) // if truncate failed see if need to create
{
if (!ex.Message.Contains("ORA-00942"))
{
throw ex; // if other than already exists then throw exception
}
bool created = CreateListTableIfNeeded(dsName, listName);
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
return;
}
/// <summary>
/// DeleteSqlStatement
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static bool DeleteSqlStatement(
string name,
int version)
{
string sql = @"
delete from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "'";
if (version >= 0) // specific version?
{
sql += " and version = " + version;
}
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
int count = cmd.ExecuteNonReader();
cmd.Dispose();
return(count > 0);
}
public static int SelectInt(
DbConnectionMx mxConn,
string sql)
{
int value;
DbDataReader dr = null;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = mxConn; // set connection to use
drd.Prepare(sql);
dr = drd.ExecuteReader() as OracleDataReader;
if (!dr.Read())
{
throw (new Exception("SelectInt Read failed"));
}
value = drd.GetInt(0);
dr.Close();
drd.Dispose();
return(value);
}
/// <summary>
/// ReadSqlStatement
/// </summary>
/// <param name="name"></param>
/// <param name="id"></param>
/// <param name="sqlStmt"></param>
/// <param name="owner"></param>
/// <returns></returns>
public static bool ReadSqlStatement(
string name,
int version,
out string sqlStmt,
out string keyColName,
out string owner)
{
string sql = @"
select
sql,
key_col_name,
ownr_id
from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "' and " +
"version = " + version;
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
cmd.ExecuteReader();
sqlStmt = keyColName = owner = null;
bool exists = cmd.Read();
if (exists)
{
sqlStmt = cmd.GetClob(0);
keyColName = cmd.GetString(1);
owner = cmd.GetString(2);
}
cmd.CloseReader();
cmd.Dispose();
return(exists);
}
/// <summary>
/// Get dict relating types between different assay databases
/// </summary>
static void BuildResultTypeDicts()
{
string sql = @"
select <columns>
from <tables>
order by lower(<orderColumns>)";
resultTypeDict1 = new Dictionary <int, AssayResultType>();
resultTypeDict2 = new Dictionary <int, AssayResultType>();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (drd.Read())
{
AssayResultType r = new AssayResultType();
r.Name = drd.GetStringByName("rslt_typ_nm");
r.ResultTypeId1 = drd.GetIntByName("rslt_typ_id");
r.ResultTypeId2 = drd.GetIntByName("SECONDARY_RSLT_TYP_ID");
r.SummarizedMeanTypeCode = drd.GetStringByName("smrzd_mean_typ_cd");
r.DimensionTypeId = drd.GetIntByName("dmnsn_typ_id");
if (r.DimensionTypeId != 70) // dmnsn_typ_id: 70 = 'Result', 61 = 'Stat'
{
r.ResultTypeId2 = -r.ResultTypeId2;
}
resultTypeDict1[r.ResultTypeId1] = r;
resultTypeDict2[r.ResultTypeId2] = r;
}
drd.Dispose();
return;
}
/// <summary>
/// Update Common Assay Attributes table (mbs_owner.cmn_assy_atrbts)
///
/// Command: Update AssayAttributesTable
///
/// This command builds an entry (or two) in the cmn_assy_atrbts table for each assay
/// referenced in the Mobius contents tree that reports an SP or CRC value as determined
/// by the associated metafactory and available in each metatable.
/// If the gene target associated with an assay can be identified then information on that
/// gene is included as well.
///
/// Additional gene information may come from the metadata for a source such as the results
/// warehouse.
///
/// Note that this function must be run under an account that has access to all restricted data so that it
/// can properly see what's available and build the table.
/// </summary>
/// <param name="lex"></param>
/// <returns></returns>
public static string UpdateAssayAttributesTable(string args)
{
MetaTable mt;
MetaColumn mc;
AssayAttributes aa, aa2;
Dictionary <int, int> geneIdCounts = new Dictionary <int, int>(); // genes and counts keyed by entrez gene id
Dictionary <int, double> targetMapXDict; // dendogram X coord keyed by gene id
Dictionary <int, double> targetMapYDict; // dendogram Y coord keyed by gene id
Dictionary <string, int> targetTypeCounts = new Dictionary <string, int>(); // target types and counts
Dictionary <int, string> assayTypeDict;
Dictionary <int, string> assayModeDict;
//UnpivotedAssayResult rr, rr2;
List <string> toks;
string mtName, tableNamePrefix, key, fileName, msg;
bool crcExists, spExists, isSummary;
int tableId, step, ri, resultTypeId, assayRowCount;
Log = new LogFile(ServicesDirs.LogDir + @"\UpdateCommonAssayAttributes.log");
Log.ResetFile();
// Get list of all of the assays in the tree
LogMessage("Accumulating assays...");
HashSet <string> mtNameHash = new HashSet <string>();
foreach (MetaTreeNode mtn0 in MetaTree.Nodes.Values)
{
if (mtn0.Type != MetaTreeNodeType.MetaTable)
{
continue;
}
mtName = mtn0.Target;
if (AssayMetaData.IsAssayMetaTableName(mtName))
{
mtNameHash.Add(mtName);
}
}
bool debug = false; // set to true to debug with limited list of assays from below
if (debug)
{
mtNameHash.Clear();
//assayHash.Add("ASSAY_1");
//assayHash.Add("ASSAY_2");
//assayHash.Add("ASSAY_3");
}
// Get other informatin needed from AssayMetadata
LogMessage("Reading AssayMetadata ASSAY metadata...");
Dictionary <int, AssayDbMetadata> assayMetadataAssayDict = // get assays and associated target/gene information
AssayMetadataDao.GetAssayTargetGeneData();
LogMessage("Getting AssayMetadata result types...");
Dictionary <int, AssayDbResultType> resultTypeDict = AssayMetadataDao.GetResultTypeDict();
LogMessage("Getting assay types and modes...");
AssayMetadataDao.GetAssayTypesAndModes(out assayTypeDict, out assayModeDict);
LogMessage("Getting gene dendogram coordinates...");
try
{
AssayMetadataDao.GetImageCoords(out targetMapXDict, out targetMapYDict);
}
catch (Exception ex) // may fail if problem with data source
{
LogMessage(DebugLog.FormatExceptionMessage(ex, true));
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
}
// Process each assay
int metatablesFound = 0, metatablesNotFound = 0;
int assaysProcessed = 0;
int assaysWithGenes = 0;
int assaysWithGeneCoords = 0;
int assaysWithTargets = 0;
int assaysWithSpOnly = 0;
int assaysWithCrcOnly = 0;
int assaysWithNoCrcSP = 0;
int assaysWithOtherTypes = 0;
int assaysWithCrcAndSp = 0;
int assaysWithNoKeyTypes = 0;
int assaysWithProcessingErrors = 0;
Dictionary <string, int> CrcAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> SpAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> OtherAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
List <AssayAttributes> resultTypeRows = new List <AssayAttributes>();
List <AssayAttributes> geneRows = new List <AssayAttributes>();
List <AssayAttributes> dbRows = new List <AssayAttributes>();
string copyUpdateMsg = "";
foreach (string mtName0 in mtNameHash)
{
AssayMetaData assayMetaData = null; // metadata for assay
bool isAssay = false;
int assayIdNbr = NullValue.NullNumber;
int assayId = NullValue.NullNumber;
//if (assaysProcessed >= 100) break; // debug
mtName = mtName0;
MetaTable.ParseMetaTableName(mtName, out tableNamePrefix, out tableId, out isSummary);
string resultType = "";
string rtId = "";
string assayName, assayDb;
mt = MetaTableCollection.Get(mtName); // get metatable
if (mt == null)
{
metatablesNotFound++;
LogMessage("MetaTable not found: " + mtName);
continue;
}
metatablesFound++;
if (mt.Code == "")
{
continue; // must be single pivoted assay
}
assayDb = "ASSAY_DB"; // customize
assayIdNbr = -1;
if (UalUtil.IClient != null && UalUtil.IClient.Attended)
{
UAL.Progress.Show((assaysProcessed + 1).ToString() + " / " + mtNameHash.Count + " - " + mt.Name + "\r\n" + mt.Label);
}
aa = new AssayAttributes();
aa.AssayDatabase = assayDb;
aa.AssayIdNbr = assayIdNbr; // data-source-specific assay Id
aa.AssayIdTxt = mt.Name; // store ASSAY_1234 type table name
aa.AssayId2 = assayId; // any associated assay id
if (isAssay)
{
aa.AssayName = assayMetaData.Name; // name from AssayMetadata
}
else
{
aa.AssayName = MetaTable.RemoveSuffixesFromName(mt.Label); // name from metatable
}
if (mt.SummarizedExists)
{
aa.SummarizedAvailable = true;
}
else
{
aa.SummarizedAvailable = false;
}
if (isAssay)
{
if (assayTypeDict.ContainsKey(tableId))
{
aa.AssayType = assayTypeDict[tableId];
}
if (assayModeDict.ContainsKey(tableId))
{
aa.AssayMode = assayModeDict[tableId];
}
aa.AssaySource = AssayMetaData.GetAssaySource(tableId);
aa.AssociationSource = "TODO"; // customize
}
aa.AssayStatus = "Active"; // say all active for now
MetaTableStats mts = MetaTableFactory.GetStats(mtName);
if (mts != null)
{
aa.ResultCount = (int)mts.RowCount;
aa.AssayUpdateDate = mts.UpdateDateTime;
}
else
{
aa.ResultCount = 0; // assume no results if no stats
}
if (mt.DescriptionIsAvailable()) // use description from Mobius
{
aa.AssayDesc = "Y";
}
if (String.IsNullOrEmpty(aa.GeneFamily))
{
aa.GeneFamily = "Unknown"; // set these to "Unknown" rather than null
}
if (String.IsNullOrEmpty(aa.AssayType))
{
aa.AssayType = "UNKNOWN"; // upper case UNKNOWN
}
if (String.IsNullOrEmpty(aa.AssayMode))
{
aa.AssayMode = "UNKNOWN"; // upper case UNKNOWN
}
// Step1: Add a row for primary & any secondary results
resultTypeRows.Clear();
MetaColumn firstResultCol = null, firstKeyResultCol = null, firstOtherKeyResultCol = null;
string resultTypeConcType;
HashSet <string> keyResultTypeCodes = new HashSet <string>();
int spCnt = 0, crcCnt = 0, otherCnt = 0;
for (int mci = 0; mci < mt.MetaColumns.Count; mci++) // pick first col with result code (could also check summarization method)
{
mc = mt.MetaColumns[mci];
if (Lex.IsUndefined(mc.ResultCode))
{
continue; // must have code defined
}
if (keyResultTypeCodes.Contains(mc.ResultCode))
{
continue; // and not included so far
}
if (mc.InitialSelection != ColumnSelectionEnum.Selected)
{
continue; // selected only
}
if (firstResultCol == null)
{
firstResultCol = mc;
}
if (!IsKeyResultType(mc, out resultTypeConcType))
{
continue;
}
if (firstKeyResultCol == null)
{
firstKeyResultCol = mc;
}
keyResultTypeCodes.Add(mc.ResultCode);
aa2 = aa.Clone();
if (resultTypeRows.Count == 0)
{
aa2.TopLevelResult = "Y";
}
else
{
aa2.TopLevelResult = "N";
}
aa2.ResultTypeId2 = GetAssayResultTypeId(mc); // AssayMetadata result type id
aa2.ResultTypeIdNbr = GetInternalResultTypeId(mc); // Internal database result type id
aa2.ResultTypeIdTxt = mc.Name; // Mobius column name
if (isAssay && resultTypeDict.ContainsKey(aa2.ResultTypeId2))
{
aa2.ResultName = resultTypeDict[aa2.ResultTypeId2].Name; // use name from AssayMetadata result type dict
}
else
{
aa2.ResultName = mc.Label; // use label from Mobius
}
aa2.ResultTypeUnits = mc.Units; // result units
if (Lex.Eq(resultTypeConcType, "SP"))
{
aa2.ResultTypeConcType = "SP";
spCnt++;
}
else if (Lex.Eq(resultTypeConcType, "CRC"))
{
aa2.ResultTypeConcType = "CRC";
crcCnt++;
}
else
{
aa2.ResultTypeConcType = "";
otherCnt++;
if (firstOtherKeyResultCol == null)
{
firstOtherKeyResultCol = mc;
}
}
aa2.ResultTypeConcUnits = ""; // todo
resultTypeRows.Add(aa2);
}
if (resultTypeRows.Count >= 1)
{
if (crcCnt > 0)
{
CrcAssayCnt[assayDb]++; // count primary type by db
}
else if (spCnt > 0)
{
SpAssayCnt[assayDb]++;
}
else
{
OtherAssayCnt[assayDb]++;
}
if (crcCnt > 0 && spCnt == 0)
{
assaysWithCrcOnly++; // count overall primary/secondary types
}
else if (crcCnt == 0 && spCnt > 0)
{
assaysWithSpOnly++;
}
else if (crcCnt > 0 && spCnt > 0)
{
assaysWithCrcAndSp++;
}
if (crcCnt == 0 && spCnt == 0) // no SP or CRC result types
{
assaysWithNoCrcSP++;
mc = firstKeyResultCol;
LogMessage("Assay with No SP/CRC key results: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
else if (otherCnt > 0) // no SP or CRC result types
{
assaysWithOtherTypes++;
mc = firstOtherKeyResultCol;
LogMessage("Non SP/CRC key result: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
}
else // no key result type
{
aa2 = aa.Clone();
resultTypeRows.Add(aa2); // include row for step1
OtherAssayCnt[assayDb]++;
assaysWithNoKeyTypes++;
LogMessage("No key result type for metatable: " + mt.Name + ", " + mt.Label);
}
// Build a step2 row for each target/gene
geneRows.Clear();
List <AssayTarget> targets = new List <AssayTarget>();
int geneCount = 0;
if (isAssay)
{
targets = assayMetaData.Targets;
}
if (targets.Count > 0)
{
assaysWithTargets++;
}
foreach (AssayTarget target in targets)
{
aa = new AssayAttributes();
aa.GeneFamily = target.TargetTypeShortName; // count target type occurance
if (Lex.IsUndefined(aa.GeneFamily))
{
aa.GeneFamily = "Unknown";
}
if (!targetTypeCounts.ContainsKey(aa.GeneFamily))
{
targetTypeCounts[aa.GeneFamily] = 0;
}
targetTypeCounts[aa.GeneFamily]++;
if (target.Genes == null || target.Genes.Count == 0) // if no genes add a single target row
{
geneRows.Add(aa);
continue;
}
foreach (AssayGene rg in target.Genes)
{
if (!Lex.IsDefined(rg.GeneSymbol))
{
continue;
}
aa2 = aa.Clone();
geneRows.Add(aa2);
aa2.GeneSymbol = rg.GeneSymbol;
int.TryParse(rg.GeneId, out aa2.GeneId);
if (aa2.GeneId > 0 && targetMapXDict.ContainsKey(aa2.GeneId))
{
aa2.TargetMapX = targetMapXDict[aa2.GeneId];
aa2.TargetMapY = targetMapYDict[aa2.GeneId];
if (geneCount == 0)
{
assaysWithGeneCoords++;
}
}
if (!geneIdCounts.ContainsKey(aa2.GeneId)) // count gene occurance
{
geneIdCounts[aa2.GeneId] = 0;
}
geneIdCounts[aa2.GeneId]++;
if (geneCount == 0)
{
assaysWithGenes++;
}
geneCount++;
}
}
if (geneRows.Count == 0) // if no step 2 rows (i.e. no targets), create a single step2 row
{
aa = new AssayAttributes();
geneRows.Add(aa);
}
// Combine key result types with target/genes
for (int i1 = 0; i1 < resultTypeRows.Count; i1++)
{
AssayAttributes s1aa = resultTypeRows[i1];
for (int i2 = 0; i2 < geneRows.Count; i2++)
{
AssayAttributes s2aa = geneRows[i2];
aa = s1aa.Clone();
aa.GeneId = s2aa.GeneId;
aa.GeneSymbol = s2aa.GeneSymbol;
aa.GeneFamily = s2aa.GeneFamily;
aa.TargetMapX = s2aa.TargetMapX;
aa.TargetMapY = s2aa.TargetMapY;
aa.GeneCount = geneCount;
if (i2 > 0)
{
aa.GeneCount = -geneCount; // negative for other than 1st gene
}
dbRows.Add(aa);
}
}
assaysProcessed++;
}
// Update table
bool updateTable = true; // set to false for debug
if (dbRows.Count <= 0)
{
LogMessage("No rows in new dataset, table not updated");
}
else if (updateTable)
{
LogMessage("Deleting existing data...");
DbCommandMx dao = new DbCommandMx();
string sql = "delete from mbs_owner.cmn_assy_atrbts";
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.BeginTransaction();
int delCnt = dao.ExecuteNonReader();
LogMessage("Inserting new data...");
int t0 = TimeOfDay.Milliseconds();
for (ri = 0; ri < dbRows.Count; ri++)
{
aa = dbRows[ri];
aa.Id = ri + 1;
//aa.Id += 10000; // debug
if (aa.GeneSymbol != null)
{
aa.GeneSymbol = aa.GeneSymbol.ToUpper(); // be sure key match cols are upper case
}
if (aa.GeneFamily != null)
{
aa.GeneFamily = aa.GeneFamily.ToUpper();
}
if (aa.GeneFamilyTargetSymbol != null)
{
aa.GeneFamilyTargetSymbol = aa.GeneFamilyTargetSymbol.ToUpper();
}
if (aa.ResultTypeConcType != null)
{
aa.ResultTypeConcType = aa.ResultTypeConcType.ToUpper();
}
if (aa.AssayType != null)
{
aa.AssayType = aa.AssayType.ToUpper();
}
if (aa.AssayMode != null)
{
aa.AssayMode = aa.AssayMode.ToUpper();
}
AssayAttributesDao.InsertCommonAssayAttributes(aa, dao);
if (TimeOfDay.Milliseconds() - t0 > 1000)
{
//Progress.Show("Inserting new data " + (ri + 1) + "/" + rows.Count + "...");
t0 = TimeOfDay.Milliseconds();
}
}
dao.Commit();
dao.Dispose();
copyUpdateMsg = UpdateCmnAssyAtrbtsCopies();
}
string response =
"----------------------------------\r\n" +
"Assays processed: " + assaysProcessed + "\r\n" +
"Assays with processing errors: " + assaysWithProcessingErrors + "\r\n" +
"Rows inserted: " + dbRows.Count + "\r\n" +
copyUpdateMsg +
"----------------------------------\r\n" +
"Assays with CRC only: " + assaysWithCrcOnly + "\r\n" +
"Assays with SP only: " + assaysWithSpOnly + "\r\n" +
"Assays with CRC and SP: " + assaysWithCrcAndSp + "\r\n" +
"Assays with no CRC or SP: " + assaysWithNoCrcSP + "\r\n" +
"Assays with non CRC/SP key types: " + assaysWithOtherTypes + "\r\n" +
"Assays with no key types: " + assaysWithNoKeyTypes + "\r\n" +
"----------------------------------\r\n" +
"Assays with targets defined: " + assaysWithTargets + "\r\n" +
"Assays with genes defined: " + assaysWithGenes + "\r\n" +
"Assays with gene map coordinates: " + assaysWithGeneCoords + "\r\n" +
"----------------------------------\r\n" +
//"CRC Assays: " + CrcAssayCnt["ASSAY"] + "\r\n" +
//"SP Assays: " + SpAssayCnt["ASSAY"] + "\r\n" +
//"??? Assays: " + OtherAssayCnt["ASSAY"] + "\r\n" +
"----------------------------------";
LogMessage("\r\n" + response);
UAL.Progress.Hide();
return(response);
}
/// <summary>
/// Insert row into log table
/// </summary>
/// <param name="uo"></param>
/// <returns></returns>
public static bool Insert(
UserObject uo)
{
if (DbConnectionMx.NoDatabaseAccessIsAvailable)
{
return(true); // don't try to write
}
try
{
int t1 = TimeOfDay.Milliseconds();
string sql = @"
insert into mbs_owner.mbs_log (
obj_id,
obj_typ_id,
ownr_id,
obj_nm,
obj_desc_txt,
fldr_typ_id,
fldr_nm,
acs_lvl_id,
obj_itm_cnt,
obj_cntnt,
chng_op_cd,
chng_usr_id,
crt_dt,
upd_dt)
values (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, 'I', :2, sysdate(), sysdate())" ;
// Note that chng_usr_id is truncated to 12 chars to avoid exceptions for longer user ids. Fix after column is expanded
DbCommandMx drDao = new DbCommandMx();
OracleDbType[] pa = new OracleDbType[10];
pa[0] = OracleDbType.Int32;
pa[1] = OracleDbType.Int32;
pa[2] = OracleDbType.Varchar2;
pa[3] = OracleDbType.Varchar2;
pa[4] = OracleDbType.Varchar2;
pa[5] = OracleDbType.Int32;
pa[6] = OracleDbType.Varchar2;
pa[7] = OracleDbType.Int32;
pa[8] = OracleDbType.Int32;
pa[9] = OracleDbType.Clob;
drDao.Prepare(sql, pa);
object[] p = new object[10];
uo.Id = SequenceDao.NextVal("mbs_owner.mbs_log_seq");
p[0] = uo.Id;
p[1] = (int)uo.Type;
p[2] = (Lex.IsDefined(uo.Owner) ? uo.Owner.ToUpper() : "UNKNOWN");
p[3] = uo.Name;
p[4] = (Lex.IsUndefined(uo.Description) ? uo.Description : " ");
p[5] = uo.ParentFolderType;
p[6] = uo.ParentFolder;
p[7] = uo.AccessLevel;
p[8] = uo.Count;
p[9] = uo.Content;
// if (uo.Content!="") p[9] = uo.Content;
// else p[9]=null;
int count = drDao.ExecuteNonReader(p); // insert the row
drDao.Dispose();
int t2 = TimeOfDay.Milliseconds();
int time = t2 - t1; // 80ms 5/24/05
return(true);
}
catch (Exception e)
{
return(false);
}
}
/// <summary>
/// Dump out usage data for analysis (e.g. Usage 19-apr-2013 19-apr-2013 DumpUsageData)
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static string DumpUsageData(string where)
{
string rec;
int readCount = 0, writeCount = 0;
//where += " and obj_desc_txt like 'Query%'"; // limit to query data
string orderBy = "order by ownr_id, crt_dt";
DbCommandMx drd = UsageDao.Select(where, orderBy);
string fileName = ServicesDirs.LogDir + @"\QueryStatsDump.csv";
StreamWriter sw = new StreamWriter(fileName);
sw.WriteLine("Owner, DateTime, Event, EventData");
Progress.Show("Executing query...");
while (true)
{
UserObject uo = UsageDao.Read(drd);
if (uo == null)
{
drd.Dispose();
sw.Close();
Progress.Hide();
if (readCount == 0)
{
throw new Exception("No data found for specified for time period");
}
return("Rows written to file " + fileName + ": " + writeCount);
}
if (uo.Owner == null || uo.Owner.Trim().Length == 0)
{
continue; // skip if owner not specified
}
readCount++;
if (readCount % 1000 == 0)
{
Progress.Show("Rows read: " + readCount + "...");
}
string eventName = uo.Description;
string eventData = uo.Content;
if (Lex.Eq(eventName, "QueryEngineStats")) // multiple lines for QE stats
{
string[] sa = eventData.Split('\n');
foreach (string s in sa)
{
if (s == "")
{
continue;
}
rec = uo.Owner + "," + uo.UpdateDateTime.ToString() + "," + eventName + "," + s;
sw.WriteLine(rec);
writeCount++;
}
}
else if (Lex.Eq(eventName, "CommandStats"))
{
continue; // ignore these
}
else
{
rec = uo.Owner + "," + uo.UpdateDateTime.ToString() + "," + eventName + "," + eventData;
sw.WriteLine(rec);
writeCount++;
}
}
}
/// <summary>
/// Calculate & persist metatable stats for tables belonging to broker
/// </summary>
/// <returns></returns>
public int UpdateMetaTableStatistics()
{
MetaTableStats mts;
DateTime dt;
string sql;
int mthdId;
long rowCount;
Dictionary <int, MetaTableStats> stats = new Dictionary <int, MetaTableStats>();
DbCommandMx dao = new DbCommandMx();
// Get row count for each table
sql =
"select " +
" mthd_vrsn_id, count(*) " +
"from ( " +
" select " +
" mthd_vrsn_id, rslt_grp_id" +
" from mbs_owner.mbs_adw_rslt " +
" where " +
" sts_id = 1 " +
// " and mthd_vrsn_id = 148411 " + // debug
" group by mthd_vrsn_id, rslt_grp_id) " +
"group by mthd_vrsn_id";
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
mthdId = dao.GetInt(0);
rowCount = dao.GetLong(1);
if (!stats.ContainsKey(mthdId))
{
stats[mthdId] = new MetaTableStats();
}
stats[mthdId].RowCount = rowCount;
}
dao.CloseReader();
// Get latest update date for each table
sql =
"select " +
" mthd_vrsn_id, max(updt_dt) " +
"from mbs_owner.mbs_adw_rslt " +
"where " +
" updt_dt <= sysdate and " +
" sts_id = 1" +
// " and mthd_vrsn_id = 148411 " + // debug
"group by mthd_vrsn_id";
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
mthdId = dao.GetInt(0);
dt = dao.GetDateTime(1);
if (!stats.ContainsKey(mthdId))
{
stats[mthdId] = new MetaTableStats();
}
stats[mthdId].UpdateDateTime = dt;
}
dao.CloseReader();
dao.Dispose();
int updCnt = 0;
foreach (int mthdId_ in stats.Keys)
{
mts = stats[mthdId_];
try { UserObjectDao.UpdateUpdateDateAndCount(mthdId_, mts.UpdateDateTime, (int)mts.RowCount); }
catch (Exception ex) { continue; }
updCnt++;
}
return(stats.Count);
}
/// <summary>
/// (OLD VERSION)
/// Get list of compounds whose fragments match those of the compounds in the list.
/// </summary>
/// <param name="cnList"></param>
/// <returns></returns>
public static Dictionary <string, List <string> > GetAllSaltFormsNew(
List <string> cnList)
{
int t0, t1;
t0 = TimeOfDay.Milliseconds();
Dictionary <string, List <string> > cidDict = new Dictionary <string, List <string> >();
List <string> cnList2 = new List <string>();
foreach (string s in cnList)
{ // get just the list entries that are integers (e.g. remove MFCD numbers)
if (Lex.IsInteger(s))
{
cnList2.Add(s);
}
}
t1 = TimeOfDay.Milliseconds() - t0;
if (cnList2.Count == 0)
{
return(cidDict);
}
//MetaTable mt = MetaTableCollection.Get("frag_occurrence");
MetaTable mt = MetaTableCollection.Get("CorpId_salt_isomer_info");
if (mt == null)
{
return(cidDict);
}
string sql = mt.TableMap; // get sql to use from metatable
if (sql.StartsWith("("))
{
sql = sql.Substring(1, sql.Length - 2); // remove surround parens if necessary
}
sql = Lex.Replace(sql, "where", "where CorpId in (<list>) and "); // add criteria needed to do list search
DbCommandMx drd = new DbCommandMx();
try
{
drd.PrepareListReader(sql, DbType.Int32);
drd.ExecuteListReader(cnList2);
if (drd.Cancelled)
{
drd.Dispose();
return(null);
}
while (true)
{
if (!drd.ListRead())
{
break;
}
string cn = CompoundId.Normalize(drd.GetInt(0).ToString());
string cn2 = CompoundId.Normalize(drd.GetInt(1).ToString());
if (!cidDict.ContainsKey(cn))
{
cidDict[cn] = new List <string>();
}
List <string> al = cidDict[cn];
if (al.Count == 0 || al[al.Count - 1] != cn2) // add if not dup
{
al.Add(cn2);
}
}
drd.Dispose();
}
catch (Exception ex)
{ // catch case non-numeric item in list, single-row subquery returns more than one row, etc.
drd.Dispose();
return(new Dictionary <string, List <string> >());
}
t1 = TimeOfDay.Milliseconds() - t0;
return(cidDict);
}