/// <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;
}
/// <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>
/// 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);
}
static int DoDeletes(List <string> CorpIdList)
{
StringBuilder sb = new StringBuilder();
int delCount = 0;
int li = -1;
while (li + 1 < CorpIdList.Count)
{
sb.Length = 0;
int sbCorpIdCnt = 0;
for (li = li + 1; li < CorpIdList.Count; li++)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(CorpIdList[li]);
sbCorpIdCnt++;
if (sbCorpIdCnt >= 1000)
{
break;
}
}
string sql =
"delete from mbs_owner.corp_moltable_mx " +
"where corp_nbr in (" + sb + ")";
int delCount2 = DbCommandMx.PrepareAndExecuteNonReaderSql(sql);
delCount += delCount2;
}
return(delCount);
}
/// <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>
/// Insert the rows for a metatree node including its children into the metatree_nodes table
/// Called via command: Update MetatreeNodeTable
/// </summary>
/// <param name="toks"></param>
/// <param name="dao"></param>
public static int InsertMetatreeNodeRows(
MetaTreeNode mtn,
DbCommandMx dao)
{
int insCnt = 0;
foreach (MetaTreeNode cn in mtn.Nodes)
{
string names = "";
string values = "";
AddInsertColumn("parent_name", ref names, ref values, mtn.Name);
AddInsertColumn("parent_label", ref names, ref values, mtn.Label);
AddInsertColumn("parent_type", ref names, ref values, mtn.Type);
AddInsertColumn("child_name", ref names, ref values, cn.Name);
AddInsertColumn("child_label", ref names, ref values, cn.Label);
AddInsertColumn("child_type", ref names, ref values, cn.Type);
AddInsertColumn("child_size", ref names, ref values, cn.Size);
AddInsertColumn("child_update_dt", ref names, ref values, cn.UpdateDateTime);
string sql = "insert into " + MetatreeNodesTableName + " (" + names + ") " +
"values (" + values + ")";
dao.Prepare(sql);
dao.ExecuteNonReader();
insCnt++;
}
return(insCnt);
}
/// <summary>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsTarget> Select(int projId)
{
List <AfsTarget> l = new List <AfsTarget>();
string sql = @"
select *
from <mbs_owner>.afs_target
where afs_current = 1 and proj_id = " + projId +
" order by upper(target_name)";
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsTarget t = new AfsTarget();
t.ProjId = dao.GetIntByName("PROJ_ID");
t.TargetName = dao.GetStringByName("TARGET_NAME");
t.TargetType = dao.GetStringByName("TARGET_TYPE");
l.Add(t);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// Fetch next log object for reader opened by Select method
/// </summary>
/// <param name="drd"></param>
/// <returns></returns>
public static UserObject Read(
DbCommandMx drd)
{
OracleDataReader dr = drd.OracleRdr;
if (!dr.Read())
{
return(null);
}
UserObject uo = new UserObject();
if (!dr.IsDBNull(0))
{
uo.Id = (int)dr.GetOracleDecimal(0);
}
if (!dr.IsDBNull(1))
{
uo.Type = (UserObjectType)(int)dr.GetOracleDecimal(1);
}
if (!dr.IsDBNull(2))
{
uo.Owner = dr.GetString(2);
}
if (!dr.IsDBNull(3))
{
uo.Name = dr.GetString(3);
}
if (!dr.IsDBNull(4))
{
uo.Description = dr.GetString(4);
}
if (!dr.IsDBNull(5))
{
uo.ParentFolderType = (FolderTypeEnum)(int)dr.GetOracleDecimal(5);
}
if (!dr.IsDBNull(6))
{
uo.ParentFolder = dr.GetString(6);
}
if (!dr.IsDBNull(7))
{
uo.AccessLevel = (UserObjectAccess)(int)dr.GetOracleDecimal(7);
}
if (!dr.IsDBNull(8))
{
uo.Count = (int)dr.GetOracleDecimal(8);
}
if (!dr.IsDBNull(9))
{
OracleClob ol = dr.GetOracleClob(9);
uo.Content = ol.Value.ToString();
}
if (!dr.IsDBNull(10))
{
uo.UpdateDateTime = dr.GetDateTime(10);
}
return(uo);
}
/// <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>
/// Select
/// </summary>
/// <param name="projId"></param>
/// <returns></returns>
public static List <AfsProjMeta> Select(int projId)
{
List <AfsProjMeta> l = new List <AfsProjMeta>();
string sql = @"
select *
from <mbs_owner>.afs_proj_meta
where afs_current = 1 and proj_id = " + projId +
" order by upper(category_name)";;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProjMeta m = new AfsProjMeta();
m.ProjId = dao.GetIntByName("PROJ_ID");
m.CategoryName = dao.GetStringByName("CATEGORY_NAME");
m.CategoryValue = dao.GetStringByName("CATEGORY_VALUE");
l.Add(m);
}
dao.CloseReader();
return(l);
}
/// <summary>
/// Open select statement
/// </summary>
/// <param name="where"></param>
/// <param name="orderBy"></param>
/// <returns></returns>
public static DbCommandMx Select(
string where,
string orderBy)
{
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 " + "\r\n" +
where + " \r\n" +
//" where obj_id between 9300232 and 9301232 " + // debug
orderBy;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
return(drd);
}
/// <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;
}
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>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="sourceName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetColumnMetadataFromSql(
string sql,
DbConnectionMx conn)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata md;
List <DbColumnMetadata> mdList = new List <DbColumnMetadata>();
string sql2 = sql + " where 1=2"; // make execution fast
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = conn;
cmd.PrepareUsingDefinedConnection(sql2);
OracleDataReader rdr = cmd.ExecuteReader() as OracleDataReader;
for (int fi = 0; fi < rdr.FieldCount; fi++)
{
md = new DbColumnMetadata();
md.Name = rdr.GetName(fi);
md.Type = rdr.GetDataTypeName(fi); //
mdList.Add(md);
}
rdr.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(mdList);
}
/// <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>
///
/// </summary>
/// <param name="tableName"></param>
public GenericDwDao(
string tableName,
string seqName)
{
DbCmd = new DbCommandMx();
TableName = tableName;
SeqName = seqName;
}
/// <summary>
/// Prepare a query that takes a list as part of criteria
/// </summary>
/// <param name="sql"></param>
/// <param name="parameterType"></param>
public void PrepareListReader(
DbCommandMx drd,
string sql,
DbType parameterType)
{
drd.PrepareListReader(sql, parameterType);
return;
}
/// <summary>
/// Select basic project info with criteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsProject> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
proj_id,
mbs_project_code,
proj_name,
mbs_dht_folder_code,
dht_folder_name,
platform_name
from
<mbs_owner>.afs_project p
where
afs_current = 1
and mbs_project_code is not null
and <criteria>
order by upper(dht_folder_name), upper(proj_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsProject> projects = new List <AfsProject>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsProject p = new AfsProject();
p.ProjId = dao.GetInt(0);
p.MbsProjectName = dao.GetString(1).ToUpper();
p.ProjectLabel = dao.GetString(2);
if (Lex.IsNullOrEmpty(p.ProjectLabel))
{
p.ProjectLabel = p.MbsProjectName;
}
p.MbsDhtFolderName = dao.GetString(3).ToUpper();
p.DhtFolderLabel = dao.GetString(4).ToUpper();
if (Lex.IsNullOrEmpty(p.DhtFolderLabel))
{
p.DhtFolderLabel = p.MbsDhtFolderName;
}
p.PlatformName = dao.GetString(5).ToUpper();
projects.Add(p);
}
dao.CloseReader();
return(projects);
}
/// <summary>
///
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsAssay> SelectWithCriteria(string criteria)
{
MetaTreeNode mtn;
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
assay_id,
assay_name,
assay_db,
assay_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_assay a
where
p.afs_current = 1
and p.mbs_project_code is not null
and a.afs_current = 1
and a.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(assay_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsAssay> assays = new List <AfsAssay>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
while (dao.Read())
{
AfsAssay a = new AfsAssay();
a.ProjId = dao.GetInt(0);
a.MbsProjectName = dao.GetString(1).ToUpper();
a.ProjectLabel = dao.GetString(2);
a.MbsDhtFolderName = dao.GetString(3).ToUpper();
a.DhtFolderLabel = dao.GetString(4).ToUpper();
a.Platform = dao.GetString(5).ToUpper();
a.AssayId = dao.GetInt(6);
a.AssayLabel = dao.GetString(7);
a.AssayDb = dao.GetString(8).ToUpper();
a.AssayUse = dao.GetString(9).ToUpper();
assays.Add(a);
}
dao.CloseReader();
return(assays);
}
/// <summary>
/// Insert a row into the target-assay association table
/// </summary>
/// <param name="toks"></param>
/// <param name="dao"></param>
public static void InsertCommonAssayAttributes(
AssayAttributes taa,
DbCommandMx dao)
{
string names = "";
string values = "";
AddInsertColumn("id", ref names, ref values, taa.Id);
AddInsertColumn("assy_db", ref names, ref values, taa.AssayDatabase);
AddInsertColumn("assy_id2", ref names, ref values, taa.AssayId2);
AddInsertColumn("assy_id_nbr", ref names, ref values, taa.AssayIdNbr);
AddInsertColumn("assy_id_txt", ref names, ref values, taa.AssayIdTxt);
AddInsertColumn("assy_nm", ref names, ref values, taa.AssayName);
AddInsertColumn("assy_desc", ref names, ref values, taa.AssayDesc);
AddInsertColumn("assy_src", ref names, ref values, taa.AssaySource);
AddInsertColumn("assy_typ", ref names, ref values, taa.AssayType);
AddInsertColumn("assy_mode", ref names, ref values, taa.AssayMode);
AddInsertColumn("assy_sts", ref names, ref values, taa.AssayStatus);
AddInsertColumn("assy_sum_lvl", ref names, ref values, taa.SummarizedAvailable ? 1 : 0);
AddInsertColumn("assy_gene_cnt", ref names, ref values, taa.GeneCount);
AddInsertColumn("rslt_typ", ref names, ref values, taa.ResultTypeConcType);
AddInsertColumn("rslt_typ_nm", ref names, ref values, taa.ResultName);
AddInsertColumn("rslt_typ_id2", ref names, ref values, taa.ResultTypeId2);
AddInsertColumn("rslt_typ_id_nbr", ref names, ref values, taa.ResultTypeIdNbr);
AddInsertColumn("rslt_typ_id_txt", ref names, ref values, taa.ResultTypeIdTxt);
AddInsertColumn("rslt_uom", ref names, ref values, taa.ResultTypeUnits);
AddInsertColumn("conc_uom", ref names, ref values, taa.ResultTypeConcUnits);
AddInsertColumn("top_lvl_rslt", ref names, ref values, taa.TopLevelResult);
AddInsertColumn("remppd", ref names, ref values, taa.Remapped);
AddInsertColumn("mltplxd", ref names, ref values, taa.Multiplexed);
AddInsertColumn("rvwd", ref names, ref values, taa.Reviewed);
AddInsertColumn("cmpds_assyd", ref names, ref values, taa.CompoundsAssayed);
AddInsertColumn("prflng_assy", ref names, ref values, taa.ProfilingAssay);
AddInsertColumn("gene_id", ref names, ref values, taa.GeneId);
AddInsertColumn("gene_symbl", ref names, ref values, taa.GeneSymbol);
AddInsertColumn("gene_desc", ref names, ref values, taa.GeneDescription);
AddInsertColumn("gene_fmly", ref names, ref values, taa.GeneFamily);
AddInsertColumn("rslt_cnt", ref names, ref values, taa.ResultCount);
AddInsertColumn("assy_updt_dt", ref names, ref values, taa.AssayUpdateDate);
AddInsertColumn("assn_src", ref names, ref values, taa.AssociationSource);
AddInsertColumn("assn_cnflct", ref names, ref values, taa.AssociationConflict);
AddInsertColumn("x", ref names, ref values, taa.TargetMapX);
AddInsertColumn("y", ref names, ref values, taa.TargetMapY);
AddInsertColumn("z", ref names, ref values, taa.TargetMapZ);
string sql = "insert into mbs_owner.cmn_assy_atrbts (" + names + ") " +
"values (" + values + ")";
// sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.ExecuteNonReader();
return;
}
/// <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>
/// Establish Oracle connection for the Dao instance
/// </summary>
public void Connect()
{
DbCommandMx acd = new DbCommandMx();
this.DbCmd = acd;
string tName = TableName;
acd.MxConn = DbConnectionMx.MapSqlToConnection(ref tName);
}
/// <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>
/// Dispose of any associated DataReaderDao
/// </summary>
public void Dispose()
{
if (DbCmd == null)
{
return;
}
DbCmd.Dispose();
DbCmd = null;
}
public DbDataReader ExecuteReader(
DbCommandMx drd,
object[] parmValues)
{
DateTime t0 = DateTime.Now;
DbDataReader dr = drd.ExecuteReader(parmValues);
double dt = TimeOfDay.Delta(t0);
ExecuteReaderTime += dt;
ExecuteReaderCount++;
return(dr);
}
/// <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>
/// Get list of CIDS that are in the oracle database but not the Mobius fingerprint files
/// </summary>
/// <returns></returns>
static List <string> GetMissingCidList()
{
string sql = "", cid;
int readCnt = 0;
HashSet <string> knownCidsSet = FpDao.GetExistingCidSet(); // get list of cids in DB
ExistingUndefinedStructureCids = FpDao.ReadUndefinedStructuresCids(); // cids that have undefined structures and aren't in DB
knownCidsSet.UnionWith(ExistingUndefinedStructureCids);
List <string> cidList = new List <string>();
if (CorpDatabase)
{
sql = SelectAllCorpIds;
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr = 3431641"); // debug
//sql = Lex.Replace(sql, "s.corp_nbr = m.corp_nbr", "s.corp_nbr = m.corp_nbr and s.corp_nbr between 1000000 and 1100000"); // debug
}
else // chembl
{
sql = SelectAllChemblIds;
}
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
if (CorpDatabase)
{
int corpId = rdr.GetInt(0); // corp_nbr
cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
}
else
{
cid = rdr.GetString(0); // chembl
}
if (!knownCidsSet.Contains(cid))
{
cidList.Add(cid);
}
}
rdr.CloseReader();
return(cidList);
}
/// <summary>
/// Get dictionary of result types
/// </summary>
/// <returns></returns>
public static Dictionary <int, AssayDbResultType> GetResultTypeDict()
{
if (ResultTypeDict != null)
{
return(ResultTypeDict);
}
string sql = @"
select
rt.assay_rslt_typ_id,
rt.blgcl_rslt_typ_shrt_nm,
rt.sum_md_txt,
l.rslt_lvl_nm
from
metadata_owner.rslt_typ rt,
metadata_owner.rslt_lvl l
where
l.rslt_lvl_id = rt.rslt_lvl_id
and rt.sts_id = 1
and l.sts_id = 1";
Dictionary <int, AssayDbResultType> dict = new Dictionary <int, AssayDbResultType>();
ResultTypeDict = dict;
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
while (true)
{
if (!drd.Read())
{
break;
}
AssayDbResultType rt = new AssayDbResultType();
rt.RsltTypeId = drd.GetInt(0);
rt.Name = drd.GetString(1);
rt.SumMdTxt = drd.GetString(2);
rt.RsltLvl = drd.GetString(3);
dict[rt.RsltTypeId] = rt;
}
drd.CloseReader();
return(dict);
}
/// <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);
}