/// <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>
/// 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>
/// Read next record
/// </summary>
/// <returns></returns>
public AnnotationVo Read()
{
if (!DbCmd.Read())
{
return(null);
}
AnnotationVo vo = new AnnotationVo();
vo.rslt_id = DbCmd.GetLong(0);
vo.rslt_grp_id = DbCmd.GetLong(1);
vo.ext_cmpnd_id_txt = DbCmd.GetString(2);
vo.ext_cmpnd_id_nbr = DbCmd.GetInt(3);
vo.src_db_id = DbCmd.GetInt(4);
vo.mthd_vrsn_id = DbCmd.GetInt(5);
vo.rslt_typ_id = DbCmd.GetLong(6);
vo.rslt_val_prfx_txt = DbCmd.GetString(7);
vo.rslt_val_nbr = DbCmd.GetDouble(8);
vo.uom_id = DbCmd.GetInt(9);
vo.rslt_val_txt = DbCmd.GetString(10);
vo.rslt_val_dt = DbCmd.GetDateTime(11);
vo.cmnt_txt = DbCmd.GetString(12);
vo.dc_lnk = DbCmd.GetString(13);
vo.chng_op_cd = DbCmd.GetString(14);
vo.chng_usr_id = DbCmd.GetString(15);
return(vo);
}
/// <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);
}
public static long SelectLong(
string sql,
OracleDbType[] parmTypes = null,
object[] parmValues = null)
{
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(sql, parmTypes, parmValues);
if (cmd == null)
{
return(NullValue.NullNumber);
}
long value = cmd.GetLong(0);
cmd.CloseReader();
return(value);
}
/// <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>
/// Select structures for a list of compound ids using a single SQL cursor.
/// </summary>
/// <param name="notInCache"></param>
/// <param name="mt"></param>
/// <param name="strMc"></param>
static Dictionary <string, MoleculeMx> SelectChemicalStructuresForCorpIdList(
List <string> cidList,
MetaTable mt)
{
List <string> notInCacheKeyList;
Dictionary <Int64, string> intKeyToStringKey;
DbType parmType;
MoleculeMx cs;
Int64 intCid;
string cid;
int li;
Stopwatch sw = Stopwatch.StartNew();
Dictionary <string, MoleculeMx> csDict = new Dictionary <string, MoleculeMx>();
List <string> notInCacheList = new List <string>();
// Get structures already in the cache
for (li = 0; li < cidList.Count; li++)
{
cid = cidList[li];
if (RestrictedDatabaseView.KeyIsRetricted(cid))
{
continue;
}
cid = CompoundId.Normalize(cid, mt);
if (!Int64.TryParse(cid, out intCid))
{
continue; // make sure an integer
}
if (MoleculeCache.Contains(cid)) // see if in cache
{
csDict[cid] = MoleculeCache.Get(cid);
continue;
}
else
{
notInCacheList.Add(cid);
}
}
// Retrieve structures from the database for those not in cache
if (notInCacheList.Count == 0)
{
return(csDict);
}
MetaColumn strMc = mt.GetMetaColumnByName("molstructure");
if (strMc == null)
{
return(null);
}
string tableMap = mt.GetTableMapWithAliasAppendedIfNeeded(); // some SQL (e.g. Postgres) requires an alias for subqueries)
string keyColName = mt.KeyMetaColumn.ColumnMap;
if (Lex.IsUndefined(keyColName))
{
keyColName = mt.KeyMetaColumn.Name;
}
string strColExpr = strMc.ColumnMap;
if (strColExpr == "")
{
strColExpr = strMc.Name;
}
if (MqlUtil.IsCartridgeMetaTable(mt)) // selecting from Direct cartridge
{
if (!Lex.Contains(tableMap, "chime(")) // if no chime expression
{
strColExpr = "chime(ctab)"; // then create one (gets clob)
}
strColExpr += ", chime(ctab)"; // add 2nd column that gets clob in case first just gets first 4000 characters
}
string sql =
"select " + keyColName + ", " + strColExpr + " " +
"from " + tableMap + " " +
"where " + keyColName + " in (<list>)";
DbCommandMx drd = new DbCommandMx();
bool isNumericKey = (mt.KeyMetaColumn.IsNumeric);
bool isStringKey = !isNumericKey;
if (isStringKey)
{
parmType = DbType.String;
}
else
{
parmType = DbType.Int64;
}
try
{
drd.PrepareListReader(sql, parmType);
drd.ExecuteListReader(notInCacheList);
while (drd.Read())
{
if (drd.Rdr.IsDBNull(0))
{
continue;
}
if (isNumericKey)
{
intCid = drd.GetLong(0);
cid = intCid.ToString();
}
else // string cid
{
cid = drd.GetString(0);
}
cid = CompoundId.Normalize(cid, mt);
string molString = drd.GetString(1);
cs = new MoleculeMx(MoleculeFormat.Chime, molString);
csDict[cid] = cs;
MoleculeCache.AddMolecule(cid, cs);
}
drd.Dispose();
int msTime = (int)sw.ElapsedMilliseconds;
return(csDict);
}
catch (Exception ex)
{
if (drd != null)
{
drd.Dispose();
}
throw new Exception(
"SelectStructuresForCorpIdList, table: " + mt.Name + "\n" +
"sql: " + OracleMx.FormatSql(sql) + "\n");
}
}
public static void UpdateFingerprintFiles()
{
string msg = "";
long uci, lastUci = 0;
int baseFpWriteCnt = 0, fragFpWriteCnt = 0, nullFpCnt = 0, readCnt = 0;
int fi = 0;
string sql = @"
select
x.uci,
x.src_id,
x.src_compound_id,
x.src_compound_id_nbr,
s.fingerprint,
h.fingerprint
from
DEV_MBS_OWNER.CORP_UC_XREF x,
DEV_MBS_OWNER.CORP_UC_STRUCTURE s,
DEV_MBS_OWNER.CORP_UC_FIKHB_HIERARCHY h
where
s.uci = x.uci
and H.PARENT (+) = s.fikhb
/* and s.uci between 1 and 1000 */ /* debug */
order by s.uci" ;
ShowProgress("Executing select fingerprints query...");
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(sql);
if (cmd == null)
{
throw new Exception("No rows retrieved");
}
bool readOk = true;
BinaryWriter[] bw = FpDao.OpenFingerprintFilesForWriting();
fi = 0;
DateTime lastProgressUpdate = DateTime.Now;
while (true)
{
if (readCnt > 0) // read next row if not first row
{
readOk = cmd.Read();
}
if (readOk)
{
readCnt++;
}
if (DateTime.Now.Subtract(lastProgressUpdate).TotalSeconds > 1 || !readOk) // show progress
{
lastProgressUpdate = DateTime.Now;
int fpWriteCnt = baseFpWriteCnt + fragFpWriteCnt;
msg =
"Update Fingerprint Files\r\n" +
"\r\n" +
"Reads: " + readCnt + "\r\n" +
"Fingerprints written: " + fpWriteCnt + "\r\n" +
"Null FPs: " + nullFpCnt;
ShowProgress(msg);
}
if (!readOk)
{
break;
}
uci = cmd.GetLong(0);
int src = cmd.GetInt(1);
string cidString = cmd.GetString(2);
int cidInt = cmd.GetInt(3);
byte[] fp = cmd.GetBinary(4);
byte[] fp2 = cmd.GetBinary(5);
if (fp == null && fp2 == null)
{
nullFpCnt++;
continue;
}
if (uci != lastUci)
{
fi = (fi + 1) % FpDao.FingerprintFileCount;
lastUci = uci;
}
if (fp != null)
{
FpDao.WriteFingerprintRec(bw[fi], uci, src, cidString, fp);
baseFpWriteCnt++;
}
if (fp2 != null)
{
FpDao.WriteFingerprintRec(bw[fi], uci, src, cidString, fp2);
fragFpWriteCnt++;
}
} // read loop
cmd.CloseReader();
FpDao.CloseFingerprintFilesForWriting();
FpDao.BackupAndReplaceFingerprintFiles();
msg = "*** Update Complete ***\r\n\r\n" + msg;
ShowProgress(msg);
return;
}
/// <summary>
/// Reorder the rows in all annotation tables that haven't been reordered since last call
/// </summary>
public static string ReorderRows(string singleAnnotTableId)
{
long methodId = 0, rowCnt = 0, rowCnt2 = 0, totalRows = 0;
int failCnt = 0, skipCnt = 0;
Lex lex = new Lex();
lex.OpenString(singleAnnotTableId);
string singleMvId = lex.Get(); // item to set
string sql = @"
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
group by mthd_vrsn_id)
order by mthd_vrsn_id
" ;
if (Lex.IsDefined(singleMvId)) // single annot table
{
if (!Lex.IsLong(singleMvId))
{
throw new ArgumentException();
}
sql = Lex.Replace(sql, "> 0", "= " + singleMvId);
}
Mobius.UAL.Progress.Show("Getting list of annotation tables to update...");
DbCommandMx cmd = DbCommandMx.PrepareAndExecuteReader(sql);
List <long> methodIds = new List <long>();
List <long> methodRowCounts = new List <long>();
while (cmd.Read())
{
methodId = cmd.GetLong(0);
methodIds.Add(methodId);
rowCnt = cmd.GetLong(1);
methodRowCounts.Add(rowCnt);
}
cmd.Dispose();
totalRows = 0;
int vmIdCnt = 0;
for (int mi = 0; mi < methodIds.Count; mi++)
{
methodId = methodIds[mi];
//if (methodId <= 442290) continue; // continue where left off
rowCnt = methodRowCounts[mi];
string msg = "Table Id: " + methodId + " (" + (mi + 1) + " / " + methodIds.Count + "), Rows: " + rowCnt + ", Total Rows Reordered: " + totalRows + ", Failed: " + failCnt + ", Skipped: " + skipCnt;
if (rowCnt > 1000000 && Lex.IsUndefined(singleMvId)) // skip if > 1000000 rows and not a specific single annot table
{
skipCnt++;
DebugLog.Message("Skipping " + msg);
continue;
}
Mobius.UAL.Progress.Show(msg);
DebugLog.Message(msg);
try
{
rowCnt2 = ReorderRows(methodId);
}
catch (Exception ex)
{
DebugLog.Message(DebugLog.FormatExceptionMessage(ex));
failCnt++;
continue;
}
vmIdCnt++;
totalRows += rowCnt;
}
return("Annotation tables reordered: " + vmIdCnt + ", Failed: " + failCnt + ", Skipped: " + skipCnt + ", Total rows reordered: " + totalRows);
}