/// <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>
/// <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>
/// 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>
/// 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>
/// 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>
/// 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>
/// 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);
}
public static int SelectInt(
string sql,
OracleDbType[] parmTypes,
object[] parmValues)
{
DbCommandMx cmd = DbCommandMx.PrepareExecuteAndRead(sql, parmTypes, parmValues);
if (cmd == null)
{
return(NullValue.NullNumber);
}
int value = cmd.GetInt(0);
cmd.CloseReader();
return(value);
}
/// <summary>
/// Get the number of non-null text, numeric or date values for a given annotation table.
/// </summary>
/// <returns></returns>
public int GetNonNullRsltCnt(int method_vrsn_id)
{
string sql = @"select
count(rslt_id) as rslt_cnt
from MBS_OWNER.mbs_adw_rslt
where
mthd_vrsn_id = :0
and sts_id = 1
and (rslt_val_nbr is not null
or rslt_val_txt is not null
or rslt_val_dt is not null)";
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql, Oracle.DataAccess.Client.OracleDbType.Int32);
cmd.ExecuteReader(method_vrsn_id);
cmd.Read();
return(cmd.GetInt(0));
}
/// <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);
}
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>
/// Get a list of CorpIds that have been added or modified since the checkpoint date
/// </summary>
/// <returns></returns>
static List <string> GetNewAndModifiedCorpIdList(
out Dictionary <string, DateTime> cidUpdateDateDict)
{
int readCnt = 0;
List <string> cidList = new List <string>();
cidUpdateDateDict = new Dictionary <string, DateTime>();
CheckpointDateTime = FpDao.ReadFingerPrintSimMxDataUpdateCheckpointDate(); // , "20-mar-2016 000000");
if (Lex.IsUndefined(CheckpointDateTime))
{
throw new Exception("Mobius UpdateSimMxCorpIdDataCheckpointDate is not defined");
}
//CheckpointDateTime = "20-mar-2016 000000"; // debug
string sql = Lex.Replace(SelectCorpIdsByDateRange, "1-jan-1900 000000", CheckpointDateTime);
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
while (rdr.Read())
{
readCnt++;
int corpId = rdr.GetInt(0); // corp_nbr
string cid = corpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
cidList.Add(cid);
DateTime dt = rdr.GetDateTime(1);
cidUpdateDateDict[cid] = dt;
}
rdr.CloseReader();
return(cidList);
}
/// <summary>
/// Get the AFS project id, if any, associated with a project node name
/// </summary>
/// <param name="projNodeName"></param>
/// <returns></returns>
public static int GetAfsProjectId(string projNodeName)
{
string sql = @"
select proj_id
from <mbs_owner>.afs_project
where afs_current = 1 and MBS_PROJECT_CODE = '" + projNodeName.ToUpper() + "'";
sql = Lex.Replace(sql, "<mbs_owner>", AfsTableSchema);
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
if (!dao.Read())
{
dao.CloseReader();
return(-1);
}
int projId = dao.GetInt(0);
dao.CloseReader();
return(projId);
}
/// <summary>
/// Sync the Mobius CorpMoltable replicate used to retrieve Smiles
/// Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]
/// </summary>
/// <returns></returns>
public static string UpdateCorpDbMoltableMx(
string args)
{
DateTime moleculeDateTime = DateTime.MinValue;
double mw;
string msg = "", sql = "", maxCorpIdSql, mf = "", chime = "", smiles = "", checkPointDate = "", helm = "", sequence = "", svg = "";
object[][] pva = null;
int pvaCount = 0, CorpId, lowCorpId = 0, highCorpId = 0, srcMaxCorpId = 0;
int SelectChunkSize = 20; // small chunks
int InsertBufferSize = 10;
//int SelectChunkSize = 100000; // big chunks
//int InsertBufferSize = 1000;
// Select data from corp_moltable by CorpId range
const string SelectByCorpIdRange = @"
SELECT
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date
FROM
corp_owner.corp_moltable m,
corp_owner.corp_substance s
where
m.corp_nbr > 0
and s.corp_nbr = m.corp_nbr
and (s.status_code is null or s.status_code = 'A')
ORDER BY corp_nbr";
// Select data from corp_moltable by date range comparing to corp_moltable_mx
const string SelectByDateRange = @"
select
m.corp_nbr,
chime(m.ctab),
m.molformula,
m.molweight,
null molsmiles,
s.helm_txt,
s.sequence_txt,
m.molecule_date,
m2.molecule_date
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx m2
where
m.molecule_date > to_date('1-jan-1900 000000','DD-MON-YYYY HH24MISS')
and s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and m2.corp_nbr (+) = m.corp_nbr
and m2.molecule_date (+) != m.molecule_date
order by m.molecule_date" ;
// Select for missing smiles strings, ex: Update CorpDbMoltableMx LoadMissing mx.molecule_date > '1-jan-2014'
const string SelectMissingSmilesFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where molsmiles_mx is null /* extra condition */
order by corp_nbr" ;
// Insert missing helm info
const string SelectMissingHelmFix = @"
select /* check for CorpIds in corp_moltable not in corp_moltable_mx */
corp_nbr,
chime(ctab),
molformula,
molweight,
null molsmiles,
helm_txt,
sequence_txt,
molecule_date
from
(
select
m.*,
s.helm_txt,
s.sequence_txt,
mx.molsmiles molsmiles_mx
from
corp_owner.corp_moltable m,
corp_owner.corp_substance s,
corp_moltable_mx mx
where
s.corp_nbr = M.CORP_NBR
and (s.status_code is null or s.status_code = 'A')
and mx.corp_nbr (+) = m.corp_nbr
and 1=1 /* condition to substitute */
) m
where length(helm_txt) > 0 /* extra condition */
order by corp_nbr" ;
// Secondary "large" structure table (~5k mols)
const string SelectLargeMols = @"
select
corp_nbr,
to_clob(molstructure),
to_clob(molformula),
molweight,
molsmiles,
null helm_txt,
null sequence_txt,
molecule_date
from
(select
corp_srl_nbr corp_nbr,
'CompoundId=' || corp_srl_nbr molstructure,
null ctab,
mlclr_frml_txt molformula,
mlclr_wgt molweight,
null molsmiles,
null molecule_date
from rdm_owner.rdm_sbstnc
where rdw_src_cd = 'LRG'" ;
// Insert statement
const string InsertSql = @"
insert into mbs_owner.corp_moltable_mx (
corp_nbr,
molstructure,
molformula,
molweight,
molsmiles,
molecule_date)
values (:0, :1, :2, :3, :4, :5)" ;
// Build select sql
bool byDateRange = false, byCorpIdRange = false, missingFix = true, deleteExisting = true;
string missingFixCriteria = "";
if (Lex.IsUndefined(args) || Lex.Eq(args, "ByDateRange"))
{
byDateRange = true;
}
else if (Lex.Eq(args, "ByCorpIdRange"))
{
byCorpIdRange = true;
Progress.Show("Getting range of CorpIds to insert...");
maxCorpIdSql = "select max(corp_nbr) from corp_owner.corp_moltable"; // get highest CorpId in source db
srcMaxCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (srcMaxCorpId < 0)
{
srcMaxCorpId = 0;
}
maxCorpIdSql = "select max(corp_nbr) from mbs_owner.corp_moltable_mx"; // get highest CorpId in dest db
highCorpId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (highCorpId < 0)
{
highCorpId = 0;
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
missingFix = true;
if (args.Contains(" "))
{
missingFixCriteria = args.Substring(10).Trim();
}
}
else if (int.TryParse(args, out srcMaxCorpId)) // single CorpId
{
byCorpIdRange = true;
highCorpId = srcMaxCorpId - 1; // say 1 less is the max we have
}
else
{
return("Syntax: UpdateCorpDbMoltableMx [ ByDateRange | ByCorpIdRange | LoadMissing | <singleCorpId>]");
}
Log("UpdateCorpDbMoltableMx started: " + args);
int readCount = 0, insCount = 0, insertCount = 0, updateCount = 0, undefinedStructures = 0, smilesSuccess = 0, smilesFails = 0, helmStructures = 0;
List <string> CorpIdList = new List <string>();
for (int chunk = 1; ; chunk++) // loop over chunks
{
if (byDateRange) // single chunk
{
if (chunk > 1)
{
break; // break 2nd time through
}
checkPointDate = UserObjectDao.GetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", "01-sep-2013 000000");
//UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate);
sql = Lex.Replace(SelectByDateRange, "1-jan-1900 000000", checkPointDate);
msg = "Reading where date >= " + checkPointDate;
}
else if (byCorpIdRange) // by CorpId range
{
if (highCorpId >= srcMaxCorpId)
{
break; // done
}
lowCorpId = highCorpId + 1; // start of next chunk
highCorpId = lowCorpId + SelectChunkSize;
if (highCorpId >= srcMaxCorpId)
{
highCorpId = srcMaxCorpId;
}
sql = Lex.Replace(SelectByCorpIdRange, "corp_nbr > 0", "corp_nbr between " + lowCorpId + " and " + highCorpId);
msg = "Reading: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
if (chunk > 1)
{
break; // break 2nd time through
}
sql = SelectMissingHelmFix;
if (Lex.IsDefined(missingFixCriteria)) // substitute any criteria
{
sql = Lex.Replace(sql, "1=1", missingFixCriteria);
}
msg = "Fixing missing data";
}
Progress.Show(msg);
DbCommandMx readCmd = new DbCommandMx();
readCmd.MxConn = DbConnectionMx.GetConnection("prd123");
readCmd.PrepareUsingDefinedConnection(sql, null);
DbDataReader rdr = readCmd.ExecuteReader();
DbCommandMx insertCmd = new DbCommandMx();
OracleDbType[] pta = new OracleDbType[6];
pta[0] = OracleDbType.Int32; // corp_nbr
pta[1] = OracleDbType.Clob; // molstructure
pta[2] = OracleDbType.Clob; // molformula
pta[3] = OracleDbType.Double; // molweight
pta[4] = OracleDbType.Clob; // smiles
pta[5] = OracleDbType.Date; // molecule_date
insertCmd.Prepare(InsertSql, pta);
insertCmd.BeginTransaction(); // be sure we have a transaction going
pva = DbCommandMx.NewObjectArrayArray(6, InsertBufferSize); // alloc insert row array
object[] vo = new object[6];
while (true)
{
bool readOk = rdr.Read();
if (readOk)
{
rdr.GetValues(vo);
CorpId = readCmd.GetInt(0); // corp_nbr
vo[0] = CorpId;
CorpIdList.Add(CorpId.ToString());
if (!readCmd.IsNull(1)) // molstructure
{
chime = readCmd.GetClob(1);
chime = OracleMx.ClearStringIfExceedsMaxStringSize(chime);
vo[1] = chime;
}
else
{
chime = "";
}
if (!readCmd.IsNull(2)) // molformula
{
mf = readCmd.GetClob(2);
mf = OracleMx.ClearStringIfExceedsMaxStringSize(mf);
vo[2] = mf;
}
if (!readCmd.IsNull(3)) // molweight
{
mw = readCmd.GetDouble(3);
vo[3] = mw;
}
if (Lex.IsDefined(chime)) // molsmiles - calculate from chime string
{
MoleculeMx cs = new MoleculeMx(MoleculeFormat.Chime, chime);
if (cs.AtomCount > 1) // need more than one atom
{
MoleculeMx cs2 = cs.ConvertTo(MoleculeFormat.Smiles);
smiles = cs2.GetSmilesString();
if (Lex.IsDefined(smiles))
{
smilesSuccess++;
}
else
{
Log("Smiles conversion failure for CorpId: " + CorpId);
smilesFails++;
}
smiles = OracleMx.ClearStringIfExceedsMaxStringSize(smiles);
vo[4] = smiles;
}
else
{
undefinedStructures++;
}
}
else
{
undefinedStructures++;
}
if (!readCmd.IsNull(5))
{
helm = readCmd.GetClob(5);
if (Lex.IsDefined(helm))
{
svg = HelmControl.GetSvg(helm);
vo[1] = SvgUtil.CompressSvgString(svg); // store compressed svg in molstructure column for now
helmStructures++;
}
}
if (!readCmd.IsNull(6))
{
sequence = readCmd.GetClob(6);
if (Lex.IsDefined(sequence))
{
// nothing yet
}
}
moleculeDateTime = DateTime.MinValue;
if (!readCmd.IsNull(7)) // molecule_date
{
moleculeDateTime = readCmd.GetDateTime(7);
vo[5] = moleculeDateTime;
}
for (int pi = 0; pi < 6; pi++) // invert for insert
{
pva[pi][pvaCount] = vo[pi];
}
if (Debug)
{
msg = String.Format("CorpId: {0}, mf: {1}, chime: {2}, smiles: {3}", CorpId.ToString(), mf.Length, chime.Length, smiles.Length);
Log(msg);
}
pvaCount++;
}
if (pvaCount >= InsertBufferSize || (!readOk && pvaCount > 0)) // write if buffer full or at end
{
try
{
if (deleteExisting)
{
int delCount = DoDeletes(CorpIdList);
updateCount += delCount; // count deletes as updates
insertCount -= delCount; // subtract from inserts
}
CorpIdList.Clear();
insCount = insertCmd.ExecuteArrayNonReader(pva, ref pvaCount);
insertCmd.Commit();
insertCmd.BeginTransaction();
insertCount += insCount;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
if (byDateRange)
{
string checkPointDate2 = String.Format("{0:dd-MMM-yyyy HHmmss}", moleculeDateTime); // format date time that will work with oracle
UserObjectDao.SetUserParameter("MOBIUS", "UpdateCorpDbMoltableMxCheckpointDate", checkPointDate2);
msg = "Processing where date >= " + checkPointDate + ", Reads: " + readCount + ", Inserts: " + insertCount + ", Updates: " + updateCount;
}
else if (byCorpIdRange) // CorpId range
{
msg = "Processing: " + lowCorpId + " to " + highCorpId + ", Reads: " + readCount + ", Inserts: " + insertCount;
}
else if (missingFix)
{
msg = "Fixing missing smiles, Updates: " + updateCount;
}
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Progress.Show(msg);
}
if (!readOk)
{
break;
}
readCount++;
}
readCmd.Dispose();
insertCmd.Dispose();
} // end for select chunk
msg = "UpdateCorpDbMoltableMx - Inserts: " + insertCount + ", Updates: " + updateCount;
msg += String.Format(", Undefined structures: {0} , Smiles failures: {1}, Helms: {2}", undefinedStructures, smilesFails, helmStructures);
Log(msg);
return(msg);
}
static ICdkMol CdkMolUtil => StaticCdkMol.I; // static molecule shortcut for utility methods
/// <summary>
/// UpdateCorpFingerprintDatabaseMx
/// ///////////////////////////////////////////////////////
/// Syntax: Update FingerprintDatabaseMx [Corp | ChEMBL] [MACCS | ECFP4] [Load | ByCidRange | SinceLastCheckpoint | LoadMissing | <SingleCorpId>]
///
/// Corp Examples:
/// Update FingerprintDatabaseMx Corp MACCS Load
/// Update FingerprintDatabaseMx Corp MACCS LoadMissing
/// Update FingerprintDatabaseMx Corp MACCS SinceLastCheckpoint
///
/// Update FingerprintDatabaseMx Corp ECFP4 Load
/// Update FingerprintDatabaseMx Corp ECFP4 LoadMissing
/// Update FingerprintDatabaseMx Corp ECFP4 SinceLastCheckpoint
///
/// ChEMBL Examples:
/// Update FingerprintDatabaseMx Chembl MACCS Load
/// Update FingerprintDatabaseMx Chembl MACCS LoadMissing
///
/// Update FingerprintDatabaseMx Chembl ECFP4 Load
/// Update FingerprintDatabaseMx Chembl ECFP4 LoadMissing
/// ///////////////////////////////////////////////////////
/// </summary>
/// <param name="argString"></param>
/// <returns></returns>
static public string Update(
string argString)
{
MoleculeMx mol;
double mw;
string chime, smiles, molString, molFile = "";
string msg = "", sql = "", chemblId, cid = "", maxCorpIdSql, maxIdSql2, mf, missingFixCriteria = "", CorpIdList = "";
int storeChunkCount = 0, CorpId, molregno, molId, lowId = 0, highId = 0, maxDestId = 0, maxSrcId = 0;
int readCount = 0, storeCount = 0;
ByCheckpoint = ByCidRange = ByCidList = LoadIfMissing = false;
ReadChunkSize = DefaultReadChunkSize;
WriteChunkSize = DefaultWriteChunkSize;
Failures = new Dictionary <string, string>();
NewUndefinedStructureCids = new List <string>();
LastFailure = "";
FailureCount = 0;
// global try loop
try
{
////////////////////////
/// Parse Parameters ///
////////////////////////
// See which database
argString = argString.Trim();
if (Lex.StartsWith(argString, "Corp"))
{
Database = "Corp";
argString = argString.Substring(5).Trim();
}
else if (Lex.StartsWith(argString, "Chembl"))
{
Database = "ChEMBL";
argString = argString.Substring(6).Trim();
}
else
{
return(SyntaxMsg);
}
// See which fingerprint type
FingerprintType = FingerprintType.MACCS; // default to MACCS if type not defined
if (Lex.TryReplace(ref argString, "MACCS", ""))
{
FingerprintType = FingerprintType.MACCS;
argString = argString.Trim();
}
else if (Lex.TryReplace(ref argString, "ECFP4", ""))
{
FingerprintType = FingerprintType.Circular; // (i.e. ECFP4)
argString = argString.Trim();
}
FpDao = new FingerprintDao(Database, FingerprintType);
List <FingerprintRec> fpRecList = new List <FingerprintRec>();
string args = argString.Trim();
string initialMsg = "Update FingerprintDatabase started: " + args;
CidList = new List <string>(); // init empty list
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
if (Lex.Eq(args, "Load"))
{
ByCidRange = true;
ShowProgress("Getting range of CorpIds to insert...");
maxCorpIdSql = SelectMaxCorpId; // get highest id in source db
maxSrcId = SelectSingleValueDao.SelectInt(maxCorpIdSql);
if (maxSrcId < 0)
{
maxSrcId = 0;
}
maxDestId = GetMaxDestMolId();
//maxIdSql2 = "select max(src_compound_id_nbr) from dev_mbs_owner.corp_uc_xref where src_id = 0"; // get highest id in UniChemDb db
//highCorpId = SelectSingleValueDao.SelectInt(maxIdSql2);
if (maxDestId < 0)
{
maxDestId = 0;
}
}
else if (Lex.Eq(args, "SinceLastCheckpoint"))
{
ByCheckpoint = true;
ShowProgress("Getting list of CorpIds updated since last checkpoint...");
CidList = GetNewAndModifiedCorpIdList(out CidUpdateDateDict);
//CidUpdateList = new List<string>(); // debug with single cmpd
//CidUpdateList.Add("03435269");
if (CidList.Count == 0)
{
return("There have been no updates since the last checkpoint");
}
initialMsg += ", CorpIds to add/update: " + CidList.Count;
}
else if (Lex.StartsWith(args, "ByCorpIdList"))
{
ByCidList = true;
CorpIdList = args.Substring("ByCorpIdList".Length).Trim();
if (Lex.IsUndefined(CorpIdList))
{
throw new Exception("Undefined CorpId list");
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
LoadIfMissing = true;
if (args.Contains(" "))
{
missingFixCriteria = args.Substring("LoadMissing".Length).Trim();
}
ShowProgress("Getting list of missing CorpIds...");
CidList = GetMissingCidList();
if (CidList.Count == 0)
{
return("There are no missing CorpIds");
}
initialMsg += ", Missing CorpIds: " + CidList.Count;
}
else if (int.TryParse(args, out maxSrcId)) // single CorpId
{
ByCidRange = true;
maxDestId = maxSrcId - 1; // say 1 less is the max we have
}
else
{
return(SyntaxMsg);
}
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else if (ChemblDatabase)
{
if (Lex.Eq(args, "Load"))
{
ByCidRange = true;
ShowProgress("Getting range of MolRegNos to insert...");
sql = "select max(molregno) from chembl_owner.compound_struct_xxxxxx";
maxSrcId = SelectSingleValueDao.SelectInt(sql);
if (maxSrcId < 0)
{
maxSrcId = 0;
}
maxDestId = GetMaxDestMolId();
if (maxDestId < 0)
{
maxDestId = 0;
}
}
else if (Lex.StartsWith(args, "LoadMissing"))
{
LoadIfMissing = true;
ShowProgress("Getting list of missing ChEMBL Ids...");
CidList = GetMissingCidList();
if (CidList.Count == 0)
{
return("There are no missing Ids");
}
initialMsg += ", Missing Chembl Ids: " + CidList.Count;
}
else
{
return(SyntaxMsg);
}
}
else
{
return(SyntaxMsg);
}
CidListOriginalCount = CidList.Count;
Log(initialMsg);
/////////////////////////////
// Loop over chunks of data
/////////////////////////////
for (int chunk = 1; ; chunk++)
{
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
if (ByCheckpoint) // single chunk
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break;
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
string matchString = "order by m.corp_nbr";
if (!Lex.Contains(sql, matchString))
{
throw new Exception(matchString + " not found");
}
sql = Lex.Replace(sql, matchString, "order by m.molecule_date");
msg = "Processing " + CidListOriginalCount + " updates since " + CheckpointDateTime;
// + " (" + Mobius.Data.CidList.FormatCidListForDisplay(null, chunkCidList) + ")";
}
else if (ByCidRange) // by CorpId range
{
if (maxDestId >= maxSrcId)
{
break; // done
}
lowId = maxDestId + 1; // start of next chunk
highId = lowId + ReadChunkSize;
maxDestId = highId;
//lowCorpId = highCorpId = 12345; // debug
if (highId >= maxSrcId)
{
highId = maxSrcId;
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "between " + lowId + " and " + highId);
msg = "Processing CorpId range: " + lowId + " to " + highId;
}
else if (ByCidList) // by single user-supplied CorpId list
{
if (chunk > 1)
{
break; // break 2nd time through
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + CorpIdList + ")");
msg = "Processing CorpId list: " + CorpIdList;
}
else if (LoadIfMissing)
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break; // all done
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
msg = "Processing missing CorpId Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) +
", Total Ids: " + CidListOriginalCount;
Log(msg);
}
else
{
return(SyntaxMsg);
}
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else if (ChemblDatabase)
{
if (ByCidRange) // by CID range
{
if (maxDestId >= maxSrcId)
{
break; // done
}
lowId = maxDestId + 1; // start of next chunk
highId = lowId + ReadChunkSize;
maxDestId = highId;
//lowId = highId = 12345; // debug
if (maxDestId >= maxSrcId)
{
maxDestId = maxSrcId;
}
sql = SelectChemblSql;
sql = Lex.Replace(sql, "<molregnoCriteria>", "between " + lowId + " and " + highId);
msg = "Processing ChEMBL MolRegNo range: " + lowId + " to " + highId;
}
else if (LoadIfMissing)
{
string cidList = GetNextListChunk();
if (Lex.IsUndefined(cidList))
{
break; // all done
}
sql = SelectByCorpIdCriteria;
sql = Lex.Replace(sql, "<CorpIdCriteria>", "in (" + cidList + ")");
msg = "Processing missing ChEMBL Id Chunk: " + Mobius.Data.CidList.FormatAbbreviatedCidListForDisplay(null, cidList) +
", Total Ids: " + CidListOriginalCount;
}
else
{
return(SyntaxMsg);
}
}
else
{
return(SyntaxMsg);
}
ShowProgress(msg);
// Execute the SQL to get the rows for the chunk
DbCommandMx rdr = DbCommandMx.PrepareAndExecuteReader(sql);
DateTime lastShowProgressTime = DateTime.MinValue;
///////////////////////////////////////////
/// Loop over rows in the current chunk ///
///////////////////////////////////////////
while (true)
{
// Update progress display
if (DateTime.Now.Subtract(lastShowProgressTime).TotalSeconds > 1) // show progress
{
int storeTotalCount = storeCount + storeChunkCount;
string msg2 = msg + "\r\n" +
"Reads: " + readCount + "\r\n" +
"Undefined: " + NewUndefinedStructureCids.Count + "\r\n" +
"Insert/Updates: " + storeTotalCount + "\r\n" +
"Failures: " + FailureCount + "\r\n" +
"Failure Types: " + Failures.Count + "\r\n" +
"Last Failure: " + LastFailure;
ShowProgress(msg2);
lastShowProgressTime = DateTime.Now;
}
// Read and process next compound
bool readOk = rdr.Read();
if (readOk)
{
readCount++;
try
{
double t1 = 0, t2 = 0, t3 = 0, t4 = 0;
DateTime t0 = DateTime.Now;
mol = null;
//t2 = TimeOfDay.Delta(ref t0);
//////////////////////
/// Corp Database ///
//////////////////////
if (CorpDatabase)
{
CorpId = rdr.GetInt(0); // corp_nbr
//Log("CorpId: " + CorpId); // debug
molId = CorpId;
cid = CorpId.ToString();
cid = CompoundId.NormalizeForDatabase(cid);
if (!rdr.IsNull(1)) // be sure chime field isn't null
{
chime = rdr.GetClob(1);
if (Lex.IsDefined(chime))
{
molFile = MoleculeMx.ChimeStringToMolfileString(chime); // convert Chime to MolFile
mol = new MoleculeMx(MoleculeFormat.Molfile, molFile);
}
}
MoleculeDateTime = rdr.GetDateTimeByName("Molecule_Date"); // Date molecule was updated in the CorpDB cartridge DB
}
///////////////////////
/// ChEMBL Database ///
///////////////////////
else // chembl
{
molId = molregno = rdr.GetInt(0);
cid = chemblId = rdr.GetString(1);
smiles = rdr.GetString(2);
if (Lex.IsDefined(smiles))
{
mol = new MoleculeMx(MoleculeFormat.Smiles, smiles);
}
}
if (MoleculeMx.IsUndefined(mol) || mol.AtomCount <= 1)
{
NewUndefinedStructureCids.Add(cid);
continue;
//mol = new AtomContainer(); // write empty structure
}
bool includeOverallFingerprint = true;
List <BitSetFingerprint> fps = CdkMol.BuildBitSetFingerprints(mol.MolfileString, includeOverallFingerprint, FingerprintType);
//t3 = TimeOfDay.Delta(ref t0);
foreach (BitSetFingerprint fp in fps)
{
FingerprintRec fpr = new FingerprintRec();
fpr.molId = molId;
fpr.SrcId = SrcDbId;
fpr.Cid = cid;
fpr.Cardinality = fp.cardinality();
fpr.Fingerprint = fp.asBitSet().toLongArray();
fpRecList.Add(fpr);
}
//t4 = TimeOfDay.Delta(ref t0);
t4 = t4;
}
catch (Exception ex)
{
if (!Failures.ContainsKey(ex.Message))
{
Failures.Add(ex.Message, cid);
}
else
{
Failures[ex.Message] += ", " + cid;
}
LastFailure = "Cid: " + cid + " - " + ex.Message;
Log(LastFailure);
//ShowProgress(ex.Message + "\r\n" + ex.StackTrace.ToString()); // debug
FailureCount++;
continue;
}
storeChunkCount++;
}
bool commitTransaction = (storeChunkCount >= WriteChunkSize || (!readOk && storeChunkCount > 0));
if (commitTransaction) // end of chunk of data to store?
{
// if updating by CheckPoint date range then merge existing data with new/updated data
if (ByCheckpoint)
{
if (readCount > 0 && (storeCount > 0 || FailureCount == 0)) // make sure not everything has failed)
{
MergeRecordsIntoFiles(fpRecList);
}
}
// Simple append of records to files
else
{
FpDao.OpenWriters("bin", FileMode.Append); // open bin files for append
foreach (FingerprintRec fpr in fpRecList) // write out buffered recs
{
FpDao.WriteFingerprintRec(fpr);
}
FpDao.CloseWriters();
int cnt = fpRecList.Count;
if (cnt > 0)
{
string cid1 = fpRecList[0].Cid;
string cid2 = fpRecList[cnt - 1].Cid;
Log("Records Appended: " + cnt + ", CIDS: " + cid1 + " - " + cid2);
}
else
{
Log("Records Appended: 0");
}
}
fpRecList.Clear();
storeCount += storeChunkCount;
storeChunkCount = 0;
}
if (!readOk)
{
break;
}
} // end of read loop for rows in a chunk
rdr.Dispose();
} // end for loop of chunks
DeleteTempFiles();
if (LoadIfMissing) // update list of cids with missing structures
{
ExistingUndefinedStructureCids.UnionWith(NewUndefinedStructureCids);
FpDao.WriteUndefinedStructuresCids(ExistingUndefinedStructureCids);
}
msg = "*** Update Complete ***\r\n\r\n" + msg;
ShowProgress(msg);
System.Threading.Thread.Sleep(100);
string logMsg = "UpdateFingerprintDb - CIDs stored: " + storeCount + ", Undefined structures: " + NewUndefinedStructureCids.Count + ", failures: " + FailureCount + "\r\n";
foreach (string key in Failures.Keys)
{
logMsg += key + " - CIDs: " + Failures[key] + "\r\n";
}
Log(logMsg);
return(logMsg);
} // end of main try loop
catch (Exception ex)
{
Log(DebugLog.FormatExceptionMessage(ex));
throw new Exception(ex.Message, ex);
}
}
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>
/// 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>
/// Convert a multipivot table into a set of tables where data exists for
/// one or more of the compound identifiers in the list.
/// </summary>
/// <param name="qt">Current form of query table</param>
/// <param name="q">Query to add transformed tables to</param>
/// <param name="ResultKeys">Keys data will be retrieved for</param>
public override void ExpandToMultipleTables(
QueryTable qt,
Query q,
List <string> resultKeys)
{
MetaTable mt2;
QueryTable qt2;
string sql;
int methodId, i1;
int t0 = TimeOfDay.Milliseconds();
List <string> normalizedResultKeys = new List <string>();
for (i1 = 0; i1 < resultKeys.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase(resultKeys[i1], qt.MetaTable);
if (key == null)
{
key = NullValue.NullNumber.ToString(); // if fails supply a "null" numeric value
}
normalizedResultKeys.Add(key);
}
sql = // todo: Make to work in general case (PubChem only now)
"select mthd_vrsn_id " +
"from " + "mbs_owner.mbs_pbchm_rslt" + " " +
"where ext_cmpnd_id_nbr in (<list>) " +
" and sts_id = 1 " + // active records only
"group by mthd_vrsn_id";
DbCommandMx drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.Int32);
drd.ExecuteListReader(normalizedResultKeys);
if (drd.Cancelled)
{
// todo qe.Cancelled = true;
drd.Dispose();
return;
}
Hashtable mtHash = new Hashtable();
int methodIdCount = 0;
while (true) // convert list of methods to set of metatable names
{
if (!drd.ListRead())
{
break;
}
methodId = drd.GetInt(0);
string mtName = "pubchem_aid_" + methodId.ToString(); // todo: Make to work in general case (PubChem only now)
if (QueryEngine.FilterAllDataQueriesByDatabaseContents &&
!MetaTableCollection.IsMetaTableInContents(mtName))
{
continue; // metatable must be in contents
}
if (qt.MetaTable.UseSummarizedData)
{
mtName += MetaTable.SummarySuffix;
}
mtHash[mtName] = null;
methodIdCount++;
}
drd.Dispose();
if (drd.Cancelled)
{
// todo qe.Cancelled = true;
return;
}
ArrayList mtList = new ArrayList();
foreach (string mtName2 in mtHash.Keys)
{ // put metatable labels & names into a list for sorting
mt2 = MetaTableCollection.Get(mtName2);
if (mt2 == null)
{
continue;
}
if (mt2.Parent == null)
{
continue; // skip if no parent
}
mtList.Add(mt2.Label.ToLower().PadRight(64) + "\t" + mt2.Name);
}
mtList.Sort();
foreach (string mts in mtList)
{ // add new querytables/metatables to query
string[] sa = mts.Split('\t');
mt2 = MetaTableCollection.Get(sa[1]);
if (mt2 == null)
{
continue;
}
qt2 = new QueryTable(q, mt2);
if (qt.HeaderBackgroundColor != Color.Empty)
{
qt2.HeaderBackgroundColor = qt.HeaderBackgroundColor;
}
}
t0 = TimeOfDay.Milliseconds() - t0;
return;
}
/// <summary>
/// Read in data, pivot & buffer for supplied set of rows.
/// This is called for retrieval only, not for search
/// </summary>
/// <param name="eqp"></param>
public override void ExecuteQuery(
ExecuteQueryParms eqp)
{
DbCommandMx drd;
int rowsFetched = 0, vosCreated = 0;
MetaTable mt;
MetaColumn mc = null;
DateTime dt;
PivotMetaBroker mb;
List <GenericMetaBroker> mbList;
string cid, pivotKey, tableFilter, s, txt, tok;
int fci, mci, pvi, pci, si, i1;
object[] vo = null;
object o;
if (!PivotInCode) // let Oracle do the pivoting?
{
base.ExecuteQuery(eqp);
return;
}
// Self-pivot. Read & buffer data for all query tables from same Source/TableFilterColumns for key set if we are the first table for Source
int t0 = TimeOfDay.Milliseconds();
Dictionary <string, MultiTablePivotBrokerTypeData> mbsi = eqp.Qe.MetaBrokerStateInfo;
mt = eqp.QueryTable.MetaTable;
string sourceKey = mt.TableMap + "," + Csv.JoinCsvString(mt.TableFilterColumns); // grouping based on source sql
MultiTablePivotBrokerTypeData mpd = (MultiTablePivotBrokerTypeData)mbsi[sourceKey];
if (mpd.FirstTableName != mt.Name)
{
return; // retrieve data for all tables when we see first table
}
mpd.ClearBuffers();
// Build sql
StringBuilder sb = new StringBuilder(); // build filter to select for desired metatable
tableFilter = "";
if (mt.TableFilterColumns.Count == 1)
{ // build single in list if single filter column
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(mt.TableFilterValues[0]);
tableFilter = mt.TableFilterColumns[0] + " in (" + sb.ToString() + ")";
}
}
else // multiple table filter columns, build and/or expressions
{
foreach (string mtName in mpd.MbInstances.Keys)
{
mt = MetaTableCollection.Get(mtName);
if (sb.Length > 0)
{
sb.Append(" or ");
}
tableFilter = "(" + GetTableFilterCriteria(mt) + ")";
sb.Append(tableFilter);
}
tableFilter = "(" + sb.ToString() + ")";
}
string sql = "select * from " + mt.TableMap + " where ";
if (tableFilter != "")
{
sql += tableFilter + " and ";
}
sql += mt.KeyMetaColumn.ColumnMap + " in (<list>) ";
// Read unpivoted data, merge/pivot & buffer pivoted rows
List <string> keySubset = eqp.SearchKeySubset;
if (keySubset == null)
{
keySubset = GetPreviewSubset(); // assume previewing of single table if no subset
}
List <string> parmList = new List <string>();
for (i1 = 0; i1 < keySubset.Count; i1++) // copy keys to parameter array properly normalized
{
string key = CompoundId.NormalizeForDatabase((string)keySubset[i1], Qt.MetaTable);
if (key != null)
{
parmList.Add(key);
}
}
drd = new DbCommandMx();
drd.PrepareListReader(sql, DbType.String);
drd.ExecuteListReader(parmList);
while (drd.Read())
{
rowsFetched++;
string tableFilterKey = ""; // get column values to identify table
for (fci = 0; fci < mt.TableFilterColumns.Count; fci++)
{
o = drd.GetObjectByName(mt.TableFilterColumns[fci]);
if (o == null)
{
s = "";
}
else
{
s = o.ToString();
}
if (tableFilterKey != "")
{
tableFilterKey += ", ";
}
tableFilterKey += s;
}
mt = mpd.TableFilterValuesToMetaTableDict[tableFilterKey];
if (mt == null)
{
continue; // continue if don't know about this table
}
if (!mpd.MbInstances.ContainsKey(mt.Name))
{
continue; // have row hash for broker?
}
int mbIdx = 0;
mb = (PivotMetaBroker)mpd.GetFirstBroker(mt.Name, out mbList);
while (true) // copy out for each metabroker
{
mt = mb.Qt.MetaTable;
if (mt == null)
{
continue;
}
if (mb.MultipivotRowDict == null)
{
mb.MultipivotRowDict = new Dictionary <string, object[]>();
}
string rowKey = "";
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString();
}
rowKey += "<" + s + ">";
}
if (mb.MultipivotRowDict.ContainsKey(rowKey)) // have entry for row?
{
vo = (object[])mb.MultipivotRowDict[rowKey];
}
else // new row, create vo for it & fill in merged column values
{
vo = new Object[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++) // transfer non-pivoted values
{
mc = mb.SelectList[si];
if (mc.PivotValues != null)
{
continue; // skip pivoted cols for now
}
for (mci = 0; mci < mt.PivotMergeColumns.Count; mci++)
{
if (Lex.Eq(mc.ColumnMap, mt.PivotMergeColumns[mci]))
{
o = drd.GetObjectByName(mt.PivotMergeColumns[mci]);
if (mc.IsKey) // normalize cid adding prefix as needed
{
o = CompoundId.Normalize(o.ToString(), mt);
}
vo[si] = o;
break;
}
}
}
mb.MultipivotRowDict[rowKey] = vo;
vosCreated++;
}
// Pivot out data based on pivot column values
if (mb.PivotKeys == null)
{ // build set of pivot keys for the pivoted columns in the table if not done yet
mb.PivotKeys = new string[mb.SelectList.Count];
for (si = 0; si < mb.SelectList.Count; si++)
{
mc = mb.SelectList[si];
if (mc.PivotValues == null)
{
continue; // skip non-pivoted cols
}
pivotKey = "";
for (pvi = 0; pvi < mc.PivotValues.Count; pvi++)
{
pivotKey += "<" + mc.PivotValues[pvi].ToLower() + ">";
}
mb.PivotKeys[si] = pivotKey;
}
}
pivotKey = "";
for (pci = 0; pci < mt.PivotColumns.Count; pci++)
{ // build pivot key for this unpivoted row
o = drd.GetObjectByName(mt.PivotColumns[pci]);
if (o == null)
{
s = "<null>";
}
else
{
s = o.ToString().ToLower();
}
pivotKey += "<" + s + ">";
}
for (si = 0; si < mb.SelectList.Count; si++) // transfer pivoted values
{
if (mb.PivotKeys[si] == null || // skip non-pivoted cols
pivotKey != mb.PivotKeys[si])
{
continue; // and non-matches
}
mc = mb.SelectList[si];
int ci = drd.Rdr.GetOrdinal(mc.ColumnMap);
if (mc.DataType == MetaColumnType.Integer)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetInt(ci);
}
else // value with possible resultId, linking information
{
txt = drd.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Number)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDouble(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.QualifiedNo)
{
// todo
}
else if (mc.DataType == MetaColumnType.String)
{
if (!mc.DetailsAvailable)
{
vo[si] = drd.GetString(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Date)
{
if (!mc.DetailsAvailable) // simple scalar value
{
vo[si] = drd.GetDateTime(ci);
}
else // value with possible resultId, linking information
{
txt = Dr.GetString(ci); // todo: fix for annotation
vo[si] = QueryEngine.ParseScalarValue(txt, Qt, mc);
}
}
else if (mc.DataType == MetaColumnType.Structure)
{ // structures come in as compound identifiers (todo: fix for annotation)
tok = Dr.GetValue(si).ToString();
cid = CompoundId.Normalize(tok, Qt.MetaTable);
vo[si] = cid;
}
else if (mc.DataType == MetaColumnType.MolFormula)
{
vo[si] = drd.GetString(ci);
}
else if (mc.DataType == MetaColumnType.DictionaryId)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Image)
{
try // Id may be string or integer value
{ vo[si] = drd.GetString(ci); }
catch (Exception ex)
{ vo[si] = drd.GetInt(ci); }
}
else if (mc.DataType == MetaColumnType.Hyperlink)
{
txt = drd.GetString(ci);
Hyperlink hlink = new Hyperlink();
vo[si] = hlink;
hlink.Text = txt;
}
}
if (mbList == null)
{
break; // single broker
}
mbIdx++; // go to next broker
if (mbIdx >= mbList.Count)
{
break; // at end of brokers?
}
mb = (PivotMetaBroker)mbList[mbIdx];
} // end of broker loop
} // end of read loop
drd.Dispose();
return;
}
/// <summary>
/// Retrieve any existing SVG for the list of supplied molecules
/// The Id column should contain the CorpId
/// </summary>
/// <param name="molList"></param>
public static int SelectMoleculeListSvg(
List <MoleculeMx> molList)
{
MoleculeMx mol;
int corpId, molSvgsFetchedCount = 0;
string corpIdString, molString, svg;
const string sql = @"
SELECT
corp_nbr,
molstructure svgString
FROM
mbs_owner.corp_moltable_mx
WHERE
corp_nbr in (<list>)
and molstructure is not null
" ;
if (!Security.UserInfo.Privileges.CanRetrieveStructures) // structures allowed?
{
return(0);
}
//if (DebugMx.True) return 0; // debug, don't use existing values
List <string> lsnList = new List <string>();
Dictionary <string, MoleculeMx> molDict = new Dictionary <string, MoleculeMx>();
foreach (MoleculeMx mol0 in molList) // set up a dict keyed by cid with mol values
{
if (mol0.PrimaryFormat != MoleculeFormat.Helm || Lex.IsUndefined(mol0.PrimaryValue))
{
continue;
}
if (int.TryParse(mol0.Id, out corpId))
{
molDict[mol0.Id] = mol0;
}
}
if (molDict.Count == 0)
{
return(0);
}
DbCommandMx cmd = new DbCommandMx();
cmd.PrepareListReader(sql, DbType.String);
cmd.ExecuteListReader(new List <string>(molDict.Keys));
while (cmd.Read())
{
corpId = cmd.GetInt(0);
if (!cmd.IsNull(1)) // molstructure
{
molString = cmd.GetClob(1);
if (!SvgUtil.IsSvgString(molString))
{
continue; // skip if not SVG
}
svg = molString; // should be compressed format SVG
corpIdString = CompoundId.Normalize(corpId.ToString());
if (Lex.IsDefined(svg) && molDict.ContainsKey(corpIdString))
{
mol = molDict[corpIdString];
mol.SvgString = svg;
molSvgsFetchedCount++;
}
}
}
cmd.CloseReader();
return(molSvgsFetchedCount);
}
/// <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>
/// 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>
/// (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);
}
/// <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>
/// SelectWithCriteria
/// </summary>
/// <param name="criteria"></param>
/// <returns></returns>
public static List <AfsLibrary> SelectWithCriteria(string criteria)
{
MetaTreeNode tempMtn = new MetaTreeNode(MetaTreeNodeType.Library);
StringBuilder sb = new StringBuilder(64);
string sql = @"
select
p.proj_id,
p.mbs_project_code,
proj_name,
p.mbs_dht_folder_code,
dht_folder_name,
platform_name,
lib_id,
lib_name,
lib_use
from
<mbs_owner>.afs_project p,
<mbs_owner>.afs_lib l
where
p.afs_current = 1
and p.mbs_project_code is not null
and l.afs_current = 1
and l.proj_id = p.proj_id
and <criteria>
order by upper(dht_folder_name), upper(proj_name), upper(lib_name)
" ;
sql = Lex.Replace(sql, "<mbs_owner>", AfsProject.AfsTableSchema);
sql = Lex.Replace(sql, "<criteria>", criteria);
List <AfsLibrary> libs = new List <AfsLibrary>();
DbCommandMx dao = new DbCommandMx();
dao.Prepare(sql);
dao.ExecuteReader();
string fileName = MetaTableFactory.MetaTableXmlFolder + @"\LibraryStats.txt";
Dictionary <string, MetaTableStats> libStats = new Dictionary <string, MetaTableStats>();
int libCount = MetaTableFactory.LoadMetaTableStats(fileName, libStats);
while (dao.Read())
{
AfsLibrary l = new AfsLibrary();
l.ProjId = dao.GetInt(0);
l.MbsProjectName = dao.GetString(1).ToUpper();
l.ProjectLabel = dao.GetString(2);
l.MbsDhtFolderName = dao.GetString(3).ToUpper();
l.DhtFolderLabel = dao.GetString(4).ToUpper();
l.Platform = dao.GetString(5).ToUpper();
l.LibId = dao.GetInt(6);
l.LibName = dao.GetString(7);
l.LibUse = dao.GetString(8).ToUpper();
string nodeName = "LIBRARY_" + l.LibId;
sb.Length = 0;
sb.Append(l.LibName);
sb.Append(" (");
if (!Lex.IsNullOrEmpty(l.LibUse))
{
sb.Append(l.LibUse);
sb.Append(", ");
}
sb.Append("Id: ");
sb.Append(l.LibId);
if (libStats.ContainsKey(nodeName))
{
tempMtn.Size = (int)libStats[nodeName].RowCount;
tempMtn.UpdateDateTime = libStats[nodeName].UpdateDateTime; // really create date
sb.Append(", ");
sb.Append(MetaTreeNode.FormatStatistics(tempMtn));
}
sb.Append(")");
l.LibLabel = sb.ToString();
libs.Add(l);
}
dao.CloseReader();
return(libs);
}