/// <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>
/// 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>
/// 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>
/// 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>
/// 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>
/// 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>
/// /// <summary>
/// Copy PRD Annotation Table data to DEV reording and compressing the data by method
/// </summary>
/// <returns></returns>
public static string CopyPrdAnnotationTableDataToDev()
{
string mvidSql = @"
select mthd_vrsn_id, count from
(
select mthd_vrsn_id, count(mthd_vrsn_id) count
FROM MBS_OWNER.mbs_adw_rslt
WHERE mthd_vrsn_id > 0 /* = 708070 */
group by mthd_vrsn_id)
order by mthd_vrsn_id
";
Mobius.UAL.Progress.Show("Getting list of annotation table mtht_vrsn_ids ...");
DbCommandMx c1 = new DbCommandMx();
c1.Prepare(mvidSql);
c1.ExecuteReader();
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = DbConnectionMx.GetConnection("DEV857");
cmd.BeginTransaction();
long totalIns = 0;
int vmIdCnt = 0;
while (c1.Read())
{
long mvId = c1.GetLong(0);
string sql =
@"insert /*+ APPEND */ into mbs_owner.mbs_adw_rslt
select * from mbs_owner.mbs_adw_rslt
where mthd_vrsn_id = " + mvId + @"
order by ext_cmpnd_id_nbr, rslt_grp_id, rslt_id";
cmd.PrepareUsingDefinedConnection(sql);
int insCnt = cmd.ExecuteNonReader();
cmd.Commit();
totalIns += insCnt;
vmIdCnt++;
string msg = "Mthd_Vrsn_id: " + mvId + ", Vmids: " + vmIdCnt + ", Rows: " + totalIns;
Mobius.UAL.Progress.Show(msg);
DebugLog.Message(msg);
}
c1.Dispose();
cmd.Dispose();
return("Rows copied: " + totalIns);
}
/// <summary>
/// Be sure the table is truncated
/// </summary>
/// <param name="schema"></param>
/// <param name="listName"></param>
public static void TruncateListTable(
string dsName,
string listName)
{
DbCommandMx cmd = null;
string fullTableName;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
string sql = "truncate table " + fullTableName;
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql);
cmd.ExecuteNonReader();
int tDelta = (int)TimeOfDay.Delta(t0);
//DebugLog.Message("Truncate Table time: " + tDelta);
}
catch (Exception ex) // if truncate failed see if need to create
{
if (!ex.Message.Contains("ORA-00942"))
{
throw ex; // if other than already exists then throw exception
}
bool created = CreateListTableIfNeeded(dsName, listName);
}
finally { if (cmd != null)
{
cmd.Dispose();
}
}
return;
}
/// <summary>
/// DeleteSqlStatement
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static bool DeleteSqlStatement(
string name,
int version)
{
string sql = @"
delete from dev_mbs_owner.mbs_spotfire_sql
where name = '" + name + "'";
if (version >= 0) // specific version?
{
sql += " and version = " + version;
}
DbCommandMx cmd = new DbCommandMx();
cmd.Prepare(sql);
int count = cmd.ExecuteNonReader();
cmd.Dispose();
return(count > 0);
}
/// <summary>
/// Update Common Assay Attributes table (mbs_owner.cmn_assy_atrbts)
///
/// Command: Update AssayAttributesTable
///
/// This command builds an entry (or two) in the cmn_assy_atrbts table for each assay
/// referenced in the Mobius contents tree that reports an SP or CRC value as determined
/// by the associated metafactory and available in each metatable.
/// If the gene target associated with an assay can be identified then information on that
/// gene is included as well.
///
/// Additional gene information may come from the metadata for a source such as the results
/// warehouse.
///
/// Note that this function must be run under an account that has access to all restricted data so that it
/// can properly see what's available and build the table.
/// </summary>
/// <param name="lex"></param>
/// <returns></returns>
public static string UpdateAssayAttributesTable(string args)
{
MetaTable mt;
MetaColumn mc;
AssayAttributes aa, aa2;
Dictionary <int, int> geneIdCounts = new Dictionary <int, int>(); // genes and counts keyed by entrez gene id
Dictionary <int, double> targetMapXDict; // dendogram X coord keyed by gene id
Dictionary <int, double> targetMapYDict; // dendogram Y coord keyed by gene id
Dictionary <string, int> targetTypeCounts = new Dictionary <string, int>(); // target types and counts
Dictionary <int, string> assayTypeDict;
Dictionary <int, string> assayModeDict;
//UnpivotedAssayResult rr, rr2;
List <string> toks;
string mtName, tableNamePrefix, key, fileName, msg;
bool crcExists, spExists, isSummary;
int tableId, step, ri, resultTypeId, assayRowCount;
Log = new LogFile(ServicesDirs.LogDir + @"\UpdateCommonAssayAttributes.log");
Log.ResetFile();
// Get list of all of the assays in the tree
LogMessage("Accumulating assays...");
HashSet <string> mtNameHash = new HashSet <string>();
foreach (MetaTreeNode mtn0 in MetaTree.Nodes.Values)
{
if (mtn0.Type != MetaTreeNodeType.MetaTable)
{
continue;
}
mtName = mtn0.Target;
if (AssayMetaData.IsAssayMetaTableName(mtName))
{
mtNameHash.Add(mtName);
}
}
bool debug = false; // set to true to debug with limited list of assays from below
if (debug)
{
mtNameHash.Clear();
//assayHash.Add("ASSAY_1");
//assayHash.Add("ASSAY_2");
//assayHash.Add("ASSAY_3");
}
// Get other informatin needed from AssayMetadata
LogMessage("Reading AssayMetadata ASSAY metadata...");
Dictionary <int, AssayDbMetadata> assayMetadataAssayDict = // get assays and associated target/gene information
AssayMetadataDao.GetAssayTargetGeneData();
LogMessage("Getting AssayMetadata result types...");
Dictionary <int, AssayDbResultType> resultTypeDict = AssayMetadataDao.GetResultTypeDict();
LogMessage("Getting assay types and modes...");
AssayMetadataDao.GetAssayTypesAndModes(out assayTypeDict, out assayModeDict);
LogMessage("Getting gene dendogram coordinates...");
try
{
AssayMetadataDao.GetImageCoords(out targetMapXDict, out targetMapYDict);
}
catch (Exception ex) // may fail if problem with data source
{
LogMessage(DebugLog.FormatExceptionMessage(ex, true));
targetMapXDict = new Dictionary <int, double>();
targetMapYDict = new Dictionary <int, double>();
}
// Process each assay
int metatablesFound = 0, metatablesNotFound = 0;
int assaysProcessed = 0;
int assaysWithGenes = 0;
int assaysWithGeneCoords = 0;
int assaysWithTargets = 0;
int assaysWithSpOnly = 0;
int assaysWithCrcOnly = 0;
int assaysWithNoCrcSP = 0;
int assaysWithOtherTypes = 0;
int assaysWithCrcAndSp = 0;
int assaysWithNoKeyTypes = 0;
int assaysWithProcessingErrors = 0;
Dictionary <string, int> CrcAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> SpAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
Dictionary <string, int> OtherAssayCnt = new Dictionary <string, int>()
{
{ "ASSAY_DB1", 0 }, { "ASSAY_DB2", 0 }, { "ASSAY_DB3", 0 }
};
List <AssayAttributes> resultTypeRows = new List <AssayAttributes>();
List <AssayAttributes> geneRows = new List <AssayAttributes>();
List <AssayAttributes> dbRows = new List <AssayAttributes>();
string copyUpdateMsg = "";
foreach (string mtName0 in mtNameHash)
{
AssayMetaData assayMetaData = null; // metadata for assay
bool isAssay = false;
int assayIdNbr = NullValue.NullNumber;
int assayId = NullValue.NullNumber;
//if (assaysProcessed >= 100) break; // debug
mtName = mtName0;
MetaTable.ParseMetaTableName(mtName, out tableNamePrefix, out tableId, out isSummary);
string resultType = "";
string rtId = "";
string assayName, assayDb;
mt = MetaTableCollection.Get(mtName); // get metatable
if (mt == null)
{
metatablesNotFound++;
LogMessage("MetaTable not found: " + mtName);
continue;
}
metatablesFound++;
if (mt.Code == "")
{
continue; // must be single pivoted assay
}
assayDb = "ASSAY_DB"; // customize
assayIdNbr = -1;
if (UalUtil.IClient != null && UalUtil.IClient.Attended)
{
UAL.Progress.Show((assaysProcessed + 1).ToString() + " / " + mtNameHash.Count + " - " + mt.Name + "\r\n" + mt.Label);
}
aa = new AssayAttributes();
aa.AssayDatabase = assayDb;
aa.AssayIdNbr = assayIdNbr; // data-source-specific assay Id
aa.AssayIdTxt = mt.Name; // store ASSAY_1234 type table name
aa.AssayId2 = assayId; // any associated assay id
if (isAssay)
{
aa.AssayName = assayMetaData.Name; // name from AssayMetadata
}
else
{
aa.AssayName = MetaTable.RemoveSuffixesFromName(mt.Label); // name from metatable
}
if (mt.SummarizedExists)
{
aa.SummarizedAvailable = true;
}
else
{
aa.SummarizedAvailable = false;
}
if (isAssay)
{
if (assayTypeDict.ContainsKey(tableId))
{
aa.AssayType = assayTypeDict[tableId];
}
if (assayModeDict.ContainsKey(tableId))
{
aa.AssayMode = assayModeDict[tableId];
}
aa.AssaySource = AssayMetaData.GetAssaySource(tableId);
aa.AssociationSource = "TODO"; // customize
}
aa.AssayStatus = "Active"; // say all active for now
MetaTableStats mts = MetaTableFactory.GetStats(mtName);
if (mts != null)
{
aa.ResultCount = (int)mts.RowCount;
aa.AssayUpdateDate = mts.UpdateDateTime;
}
else
{
aa.ResultCount = 0; // assume no results if no stats
}
if (mt.DescriptionIsAvailable()) // use description from Mobius
{
aa.AssayDesc = "Y";
}
if (String.IsNullOrEmpty(aa.GeneFamily))
{
aa.GeneFamily = "Unknown"; // set these to "Unknown" rather than null
}
if (String.IsNullOrEmpty(aa.AssayType))
{
aa.AssayType = "UNKNOWN"; // upper case UNKNOWN
}
if (String.IsNullOrEmpty(aa.AssayMode))
{
aa.AssayMode = "UNKNOWN"; // upper case UNKNOWN
}
// Step1: Add a row for primary & any secondary results
resultTypeRows.Clear();
MetaColumn firstResultCol = null, firstKeyResultCol = null, firstOtherKeyResultCol = null;
string resultTypeConcType;
HashSet <string> keyResultTypeCodes = new HashSet <string>();
int spCnt = 0, crcCnt = 0, otherCnt = 0;
for (int mci = 0; mci < mt.MetaColumns.Count; mci++) // pick first col with result code (could also check summarization method)
{
mc = mt.MetaColumns[mci];
if (Lex.IsUndefined(mc.ResultCode))
{
continue; // must have code defined
}
if (keyResultTypeCodes.Contains(mc.ResultCode))
{
continue; // and not included so far
}
if (mc.InitialSelection != ColumnSelectionEnum.Selected)
{
continue; // selected only
}
if (firstResultCol == null)
{
firstResultCol = mc;
}
if (!IsKeyResultType(mc, out resultTypeConcType))
{
continue;
}
if (firstKeyResultCol == null)
{
firstKeyResultCol = mc;
}
keyResultTypeCodes.Add(mc.ResultCode);
aa2 = aa.Clone();
if (resultTypeRows.Count == 0)
{
aa2.TopLevelResult = "Y";
}
else
{
aa2.TopLevelResult = "N";
}
aa2.ResultTypeId2 = GetAssayResultTypeId(mc); // AssayMetadata result type id
aa2.ResultTypeIdNbr = GetInternalResultTypeId(mc); // Internal database result type id
aa2.ResultTypeIdTxt = mc.Name; // Mobius column name
if (isAssay && resultTypeDict.ContainsKey(aa2.ResultTypeId2))
{
aa2.ResultName = resultTypeDict[aa2.ResultTypeId2].Name; // use name from AssayMetadata result type dict
}
else
{
aa2.ResultName = mc.Label; // use label from Mobius
}
aa2.ResultTypeUnits = mc.Units; // result units
if (Lex.Eq(resultTypeConcType, "SP"))
{
aa2.ResultTypeConcType = "SP";
spCnt++;
}
else if (Lex.Eq(resultTypeConcType, "CRC"))
{
aa2.ResultTypeConcType = "CRC";
crcCnt++;
}
else
{
aa2.ResultTypeConcType = "";
otherCnt++;
if (firstOtherKeyResultCol == null)
{
firstOtherKeyResultCol = mc;
}
}
aa2.ResultTypeConcUnits = ""; // todo
resultTypeRows.Add(aa2);
}
if (resultTypeRows.Count >= 1)
{
if (crcCnt > 0)
{
CrcAssayCnt[assayDb]++; // count primary type by db
}
else if (spCnt > 0)
{
SpAssayCnt[assayDb]++;
}
else
{
OtherAssayCnt[assayDb]++;
}
if (crcCnt > 0 && spCnt == 0)
{
assaysWithCrcOnly++; // count overall primary/secondary types
}
else if (crcCnt == 0 && spCnt > 0)
{
assaysWithSpOnly++;
}
else if (crcCnt > 0 && spCnt > 0)
{
assaysWithCrcAndSp++;
}
if (crcCnt == 0 && spCnt == 0) // no SP or CRC result types
{
assaysWithNoCrcSP++;
mc = firstKeyResultCol;
LogMessage("Assay with No SP/CRC key results: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
else if (otherCnt > 0) // no SP or CRC result types
{
assaysWithOtherTypes++;
mc = firstOtherKeyResultCol;
LogMessage("Non SP/CRC key result: " + mt.Name + "." + mc.Name + " (" + mc.ResultCode + "), " + mt.Label + "." + mc.Label);
}
}
else // no key result type
{
aa2 = aa.Clone();
resultTypeRows.Add(aa2); // include row for step1
OtherAssayCnt[assayDb]++;
assaysWithNoKeyTypes++;
LogMessage("No key result type for metatable: " + mt.Name + ", " + mt.Label);
}
// Build a step2 row for each target/gene
geneRows.Clear();
List <AssayTarget> targets = new List <AssayTarget>();
int geneCount = 0;
if (isAssay)
{
targets = assayMetaData.Targets;
}
if (targets.Count > 0)
{
assaysWithTargets++;
}
foreach (AssayTarget target in targets)
{
aa = new AssayAttributes();
aa.GeneFamily = target.TargetTypeShortName; // count target type occurance
if (Lex.IsUndefined(aa.GeneFamily))
{
aa.GeneFamily = "Unknown";
}
if (!targetTypeCounts.ContainsKey(aa.GeneFamily))
{
targetTypeCounts[aa.GeneFamily] = 0;
}
targetTypeCounts[aa.GeneFamily]++;
if (target.Genes == null || target.Genes.Count == 0) // if no genes add a single target row
{
geneRows.Add(aa);
continue;
}
foreach (AssayGene rg in target.Genes)
{
if (!Lex.IsDefined(rg.GeneSymbol))
{
continue;
}
aa2 = aa.Clone();
geneRows.Add(aa2);
aa2.GeneSymbol = rg.GeneSymbol;
int.TryParse(rg.GeneId, out aa2.GeneId);
if (aa2.GeneId > 0 && targetMapXDict.ContainsKey(aa2.GeneId))
{
aa2.TargetMapX = targetMapXDict[aa2.GeneId];
aa2.TargetMapY = targetMapYDict[aa2.GeneId];
if (geneCount == 0)
{
assaysWithGeneCoords++;
}
}
if (!geneIdCounts.ContainsKey(aa2.GeneId)) // count gene occurance
{
geneIdCounts[aa2.GeneId] = 0;
}
geneIdCounts[aa2.GeneId]++;
if (geneCount == 0)
{
assaysWithGenes++;
}
geneCount++;
}
}
if (geneRows.Count == 0) // if no step 2 rows (i.e. no targets), create a single step2 row
{
aa = new AssayAttributes();
geneRows.Add(aa);
}
// Combine key result types with target/genes
for (int i1 = 0; i1 < resultTypeRows.Count; i1++)
{
AssayAttributes s1aa = resultTypeRows[i1];
for (int i2 = 0; i2 < geneRows.Count; i2++)
{
AssayAttributes s2aa = geneRows[i2];
aa = s1aa.Clone();
aa.GeneId = s2aa.GeneId;
aa.GeneSymbol = s2aa.GeneSymbol;
aa.GeneFamily = s2aa.GeneFamily;
aa.TargetMapX = s2aa.TargetMapX;
aa.TargetMapY = s2aa.TargetMapY;
aa.GeneCount = geneCount;
if (i2 > 0)
{
aa.GeneCount = -geneCount; // negative for other than 1st gene
}
dbRows.Add(aa);
}
}
assaysProcessed++;
}
// Update table
bool updateTable = true; // set to false for debug
if (dbRows.Count <= 0)
{
LogMessage("No rows in new dataset, table not updated");
}
else if (updateTable)
{
LogMessage("Deleting existing data...");
DbCommandMx dao = new DbCommandMx();
string sql = "delete from mbs_owner.cmn_assy_atrbts";
sql = AssayAttributesDao.AdjustAssayAttrsTableName(sql);
dao.Prepare(sql);
dao.BeginTransaction();
int delCnt = dao.ExecuteNonReader();
LogMessage("Inserting new data...");
int t0 = TimeOfDay.Milliseconds();
for (ri = 0; ri < dbRows.Count; ri++)
{
aa = dbRows[ri];
aa.Id = ri + 1;
//aa.Id += 10000; // debug
if (aa.GeneSymbol != null)
{
aa.GeneSymbol = aa.GeneSymbol.ToUpper(); // be sure key match cols are upper case
}
if (aa.GeneFamily != null)
{
aa.GeneFamily = aa.GeneFamily.ToUpper();
}
if (aa.GeneFamilyTargetSymbol != null)
{
aa.GeneFamilyTargetSymbol = aa.GeneFamilyTargetSymbol.ToUpper();
}
if (aa.ResultTypeConcType != null)
{
aa.ResultTypeConcType = aa.ResultTypeConcType.ToUpper();
}
if (aa.AssayType != null)
{
aa.AssayType = aa.AssayType.ToUpper();
}
if (aa.AssayMode != null)
{
aa.AssayMode = aa.AssayMode.ToUpper();
}
AssayAttributesDao.InsertCommonAssayAttributes(aa, dao);
if (TimeOfDay.Milliseconds() - t0 > 1000)
{
//Progress.Show("Inserting new data " + (ri + 1) + "/" + rows.Count + "...");
t0 = TimeOfDay.Milliseconds();
}
}
dao.Commit();
dao.Dispose();
copyUpdateMsg = UpdateCmnAssyAtrbtsCopies();
}
string response =
"----------------------------------\r\n" +
"Assays processed: " + assaysProcessed + "\r\n" +
"Assays with processing errors: " + assaysWithProcessingErrors + "\r\n" +
"Rows inserted: " + dbRows.Count + "\r\n" +
copyUpdateMsg +
"----------------------------------\r\n" +
"Assays with CRC only: " + assaysWithCrcOnly + "\r\n" +
"Assays with SP only: " + assaysWithSpOnly + "\r\n" +
"Assays with CRC and SP: " + assaysWithCrcAndSp + "\r\n" +
"Assays with no CRC or SP: " + assaysWithNoCrcSP + "\r\n" +
"Assays with non CRC/SP key types: " + assaysWithOtherTypes + "\r\n" +
"Assays with no key types: " + assaysWithNoKeyTypes + "\r\n" +
"----------------------------------\r\n" +
"Assays with targets defined: " + assaysWithTargets + "\r\n" +
"Assays with genes defined: " + assaysWithGenes + "\r\n" +
"Assays with gene map coordinates: " + assaysWithGeneCoords + "\r\n" +
"----------------------------------\r\n" +
//"CRC Assays: " + CrcAssayCnt["ASSAY"] + "\r\n" +
//"SP Assays: " + SpAssayCnt["ASSAY"] + "\r\n" +
//"??? Assays: " + OtherAssayCnt["ASSAY"] + "\r\n" +
"----------------------------------";
LogMessage("\r\n" + response);
UAL.Progress.Hide();
return(response);
}
/// <summary>
/// Insert row into log table
/// </summary>
/// <param name="uo"></param>
/// <returns></returns>
public static bool Insert(
UserObject uo)
{
if (DbConnectionMx.NoDatabaseAccessIsAvailable)
{
return(true); // don't try to write
}
try
{
int t1 = TimeOfDay.Milliseconds();
string sql = @"
insert into mbs_owner.mbs_log (
obj_id,
obj_typ_id,
ownr_id,
obj_nm,
obj_desc_txt,
fldr_typ_id,
fldr_nm,
acs_lvl_id,
obj_itm_cnt,
obj_cntnt,
chng_op_cd,
chng_usr_id,
crt_dt,
upd_dt)
values (:0, :1, :2, :3, :4, :5, :6, :7, :8, :9, 'I', :2, sysdate(), sysdate())" ;
// Note that chng_usr_id is truncated to 12 chars to avoid exceptions for longer user ids. Fix after column is expanded
DbCommandMx drDao = new DbCommandMx();
OracleDbType[] pa = new OracleDbType[10];
pa[0] = OracleDbType.Int32;
pa[1] = OracleDbType.Int32;
pa[2] = OracleDbType.Varchar2;
pa[3] = OracleDbType.Varchar2;
pa[4] = OracleDbType.Varchar2;
pa[5] = OracleDbType.Int32;
pa[6] = OracleDbType.Varchar2;
pa[7] = OracleDbType.Int32;
pa[8] = OracleDbType.Int32;
pa[9] = OracleDbType.Clob;
drDao.Prepare(sql, pa);
object[] p = new object[10];
uo.Id = SequenceDao.NextVal("mbs_owner.mbs_log_seq");
p[0] = uo.Id;
p[1] = (int)uo.Type;
p[2] = (Lex.IsDefined(uo.Owner) ? uo.Owner.ToUpper() : "UNKNOWN");
p[3] = uo.Name;
p[4] = (Lex.IsUndefined(uo.Description) ? uo.Description : " ");
p[5] = uo.ParentFolderType;
p[6] = uo.ParentFolder;
p[7] = uo.AccessLevel;
p[8] = uo.Count;
p[9] = uo.Content;
// if (uo.Content!="") p[9] = uo.Content;
// else p[9]=null;
int count = drDao.ExecuteNonReader(p); // insert the row
drDao.Dispose();
int t2 = TimeOfDay.Milliseconds();
int time = t2 - t1; // 80ms 5/24/05
return(true);
}
catch (Exception e)
{
return(false);
}
}
/// <summary>
/// Insert an array of rows into Mobius warehouse table
/// </summary>
/// <param name="voList"></param>
/// <returns></returns>
public bool Insert(
List <AnnotationVo> voList)
{
// This insert uses the APPEND_VALUES hint if there are 10 or more rows inserted.
// This means that inserts will go into new blocks and be physically associated with each other
// which will result in significantly faster retrieval for individual annotation tables since fewer
// disk reads will be needed. Otherwise tables that are reloaded multiple times will tend to be spread over
// a larger number of reused blocks resulting in more reads and slower performance.
//
// From a web article:
// Each of the following is a benefit in some cases
// Each of the following is a disaster in other cases
// Append does a direct path load (if it can, it is not a promise, you are requesting and we may or may not do it for you - silently)
// if you direct path load, the transaction that did the direct path load CANNOT query that segment -but other transactions can, they just cannot see the newly loaded data.
// if you direct path load, you never use any existing free space, it always writes above the high water mark.
// if you direct path load, we bypass UNDO on the table -only the table -modifications
// if you direct path load, you'll maintain indexes - we build mini indexes on the newly loaded data and merge them into the 'real' indexes in bulk. A direct path load of large amounts of data will maintain indexes very efficiently.
// if you direct path load you can bypass redo on the TABLE in archivelog mode, if the database is set up to allow nologging and you have the segment set to nologging
// direct path loading bypasses the buffer cache, you write directly to the datafiles.
// direct path loading is only appropriate for the first load of a segment or an increment load of lots of data - or an increment load into a table that never has any deletes(so there is no free space to consider)
//
// transactional systems - you probably won't use it.
// warehouses - a tool you'll use a lot
AnnotationVo vo;
if (voList == null || voList.Count == 0)
{
return(false);
}
//CheckForDuplicateInsert(voList); // debug
try
{
string sql =
"insert /*+ APPEND_VALUES */ into " + TableName + " " +
"(rslt_id, " +
"rslt_grp_id, " +
"ext_cmpnd_id_txt, " +
"ext_cmpnd_id_nbr, " +
"src_db_id, " +
"mthd_vrsn_id, " +
"rslt_typ_id, " +
"rslt_val_prfx_txt, " +
"rslt_val_nbr, " +
"uom_id, " +
"rslt_val_txt, " +
"rslt_val_dt, " +
"cmnt_txt, " +
"dc_lnk, " +
"chng_op_cd, " +
"chng_usr_id, " +
"sts_id, " +
"sts_dt, " +
"crt_dt, " +
"updt_dt) " +
"values (nvl(:0," + SeqName + ".nextval)" + // if rslt_id not null use it otherwise call nextval locally
",:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,1,sysdate(),sysdate(),sysdate())";
if (voList.Count < 10) // require minimum number of rows to use APPEND_VALUESd
{
sql = Lex.Replace(sql, "APPEND_VALUES", "");
}
OracleDbType[] pa = new OracleDbType[16];
pa[0] = OracleDbType.Long; // rslt_id
pa[1] = OracleDbType.Long; // rslt_grp_id
pa[2] = OracleDbType.Varchar2;
pa[3] = OracleDbType.Int32;
pa[4] = OracleDbType.Int32;
pa[5] = OracleDbType.Int32; // mthd_vrsn_id
pa[6] = OracleDbType.Long; // rslt_typ_id
pa[7] = OracleDbType.Varchar2;
pa[8] = OracleDbType.Double;
pa[9] = OracleDbType.Int32;
pa[10] = OracleDbType.Varchar2;
pa[11] = OracleDbType.Date;
pa[12] = OracleDbType.Varchar2;
pa[13] = OracleDbType.Varchar2;
pa[14] = OracleDbType.Varchar2;
pa[15] = OracleDbType.Varchar2;
DbCmd.Prepare(sql, pa);
int cnt = voList.Count;
object[] p = new object[16]; // parameter values
object[] rslt_idA = new object[cnt]; p[0] = rslt_idA; // allocate arrays to hold values
long[] rslt_grp_idA = new long[cnt]; p[1] = rslt_grp_idA;
string[] ext_cmpnd_id_txtA = new string[cnt]; p[2] = ext_cmpnd_id_txtA;
int[] ext_cmpnd_id_nbrA = new int[cnt]; p[3] = ext_cmpnd_id_nbrA;
int[] src_db_idA = new int[cnt]; p[4] = src_db_idA;
int[] mthd_vrsn_idA = new int[cnt]; p[5] = mthd_vrsn_idA;
long[] rslt_typ_idA = new long[cnt]; p[6] = rslt_typ_idA;
string[] rslt_val_prfx_txtA = new string[cnt]; p[7] = rslt_val_prfx_txtA;
OracleDecimal[] rslt_val_nbrA = new OracleDecimal[cnt]; p[8] = rslt_val_nbrA;
int[] uom_idA = new int[cnt]; p[9] = uom_idA;
string[] rslt_val_txtA = new string[cnt]; p[10] = rslt_val_txtA;
OracleDate[] rslt_val_dtA = new OracleDate[cnt]; p[11] = rslt_val_dtA;
string[] cmnt_txtA = new string[cnt]; p[12] = cmnt_txtA;
string[] dc_lnkA = new string[cnt]; p[13] = dc_lnkA;
string[] chng_op_cdA = new string[cnt]; p[14] = chng_op_cdA;
string[] chng_usr_idA = new string[cnt]; p[15] = chng_usr_idA;
for (int li = 0; li < cnt; li++)
{ // copy values to parameter arrays
vo = voList[li];
try { vo.ext_cmpnd_id_nbr = Int32.Parse(vo.ext_cmpnd_id_txt); } // try to store text ext_cmpnd_id_txt value also as integer in ext_cmpnd_id_nbr
catch (Exception ex) { }
if (vo.rslt_id <= 0 && voList.Count == 1) // assign seq no if not already assigned if inserting only 1 row
{
vo.rslt_id = SequenceDao.NextValLong(SeqName);
}
if (vo.rslt_id > 0)
{
rslt_idA[li] = vo.rslt_id; // is result_id defined?
}
else
{
rslt_idA[li] = DBNull.Value; // if not defined send as null value so sequence is used in nvl function in insert
}
rslt_grp_idA[li] = vo.rslt_grp_id;
string txt = vo.ext_cmpnd_id_txt;
if (txt != null && txt.Length > 32) // truncate to 32 chars if needed
{
txt = txt.Substring(0, 32);
}
ext_cmpnd_id_txtA[li] = txt;
ext_cmpnd_id_nbrA[li] = vo.ext_cmpnd_id_nbr;
src_db_idA[li] = vo.src_db_id;
mthd_vrsn_idA[li] = vo.mthd_vrsn_id;
rslt_typ_idA[li] = vo.rslt_typ_id;
rslt_val_prfx_txtA[li] = vo.rslt_val_prfx_txt;
if (vo.rslt_val_nbr != NullValue.NullNumber)
{
rslt_val_nbrA[li] = new OracleDecimal(vo.rslt_val_nbr);
}
uom_idA[li] = vo.uom_id;
rslt_val_txtA[li] = vo.rslt_val_txt;
if (rslt_val_txtA[li] != null && rslt_val_txtA[li].Length > 3900) // avoid overflow error, must leave space for catenating
{
rslt_val_txtA[li] = rslt_val_txtA[li].Substring(0, 3897) + "..."; // link info & keeping total <= 4000
}
if (vo.rslt_val_txt.Contains(","))
{
vo.rslt_val_txt = vo.rslt_val_txt; // debug
}
if (vo.rslt_val_dt != DateTime.MinValue) // non-null date?
{
rslt_val_dtA[li] = new OracleDate(vo.rslt_val_dt);
}
cmnt_txtA[li] = vo.cmnt_txt;
dc_lnkA[li] = vo.dc_lnk;
chng_op_cdA[li] = vo.chng_op_cd;
chng_usr_idA[li] = vo.chng_usr_id;
}
int t0 = TimeOfDay.Milliseconds();
DbCmd.OracleCmd.ArrayBindCount = cnt;
int count = DbCmd.ExecuteNonReader(p); // do insert
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("MobiusDwDao insert rows, count = " + count.ToString() + ", Time(ms) = " + t0.ToString());
return(true);
}
catch (Exception e)
{
DebugLog.Message("MobiusDwDao.Insert - Error inserting into " + TableName + ": " + e.Message);
return(false);
}
}
/// <summary>
/// Get the next value for the sequence (MySQL)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongMySQL(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
int count = (seqDao.CacheSize > 0 ? seqDao.CacheSize : 1);
int t0 = TimeOfDay.Milliseconds();
DbCommandMx seqCmd = new DbCommandMx();
seqCmd.MxConn = DbConnectionMx.GetConnection("MySql_Mobius"); // "MySql_Mobius_Sequences"
sql = String.Format(
@"update mbs_owner.mbs_sequences
set value = last_insert_id(value) + {0}
where name = '{1}'" , count, seqName.ToUpper());
seqCmd.PrepareUsingDefinedConnection(sql);
int updCount = seqCmd.ExecuteNonReader();
if (updCount <= 0)
{
throw new Exception("Error updating sequence (may not exist): " + seqName);
}
sql = "select last_insert_id()"; // gets value before update above
seqCmd.PrepareUsingDefinedConnection(sql, null);
DbDataReader rdr = seqCmd.ExecuteReader();
bool readOk = rdr.Read();
AssertMx.IsTrue(readOk, "readOk");
long value = rdr.GetInt64(0);
rdr.Close();
seqCmd.CloseReader();
nextVal = value + 1; // return this one now
long v2 = value + 2; // next value
long vn = value + count; // last value
for (long vi = v2; vi <= vn; vi++)
{
seqQueue.Enqueue(vi);
}
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// Load data into PubChem database
/// PubChem assay data files are downloaded from the PubChem site:
/// http://pubchem.ncbi.nlm.nih.gov/ using a program like SmartFTP.
/// The files are in GNU Zip (.gz) format and can be unzipped with
/// the following gzip commands:
/// c:\gzip\gzip -d c:\pubchem\bioassay\csv\description\*.gz
/// c:\gzip\gzip -d c:\pubchem\bioassay\csv\data\*.gz
/// After downloading and decompression this method can be called on the files.
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public static string LoadData(
string aid)
{
int recCount = 0;
string mtName = "PubChem_aid_" + aid;
MetaTable mt = MetaTableCollection.Get(mtName);
if (mt == null)
{
return("Failed to get metatable");
}
// if (Math.Sqrt(4) == 2) goto UpdateCids;
string fileName = PubChemAssayDirectory + @"\CSV\Data\" + aid + ".csv";
StreamReader sr;
try { sr = new StreamReader(fileName); }
catch (Exception ex) { return("File not found: " + fileName); }
string header = sr.ReadLine(); // read headers line
List <string> headers = Csv.SplitCsvString(header);
int cidIdx = -1;
for (cidIdx = 0; cidIdx < headers.Count; cidIdx++)
{
if (headers[cidIdx].ToUpper() == "PUBCHEM_CID")
{
break;
}
}
if (cidIdx >= headers.Count)
{
sr.Close();
return("PUBCHEM_CID column not found in data headers");
}
Dictionary <string, MetaColumn> mcd = new Dictionary <string, MetaColumn>();
foreach (MetaColumn mc2 in mt.MetaColumns)
{
mcd[mc2.Name.ToUpper()] = mc2; // build dict for quick metacolumn lookup
}
DbConnectionMx conn = DbConnectionMx.MapSqlToConnection(ref PubChemWarehouseTable);
conn.BeginTransaction(); // do multiple updates per transaction
GenericDwDao dao = new GenericDwDao(
PubChemWarehouseTable, // table for results
PubChemWarehouseSeq); // sequence to use
dao.BufferInserts(true); // buffer inserts for better speed
SequenceDao.SetCacheSize(PubChemWarehouseSeq, 100); // number of ids to cache locally from sequence
//string progressMsg = "Deleting existing data...";
int i1 = dao.DeleteTable(Int32.Parse(mt.TableFilterValues[0]), true);
//if (Progress.CancelRequested())
//{
// dao.Dispose();
// return "Cancelled during data delete";
//}
//Progress.Show("Loading file...");
recCount = 0;
int t1 = 0;
while (true)
{
int t2 = TimeOfDay.Milliseconds();
if (t2 - t1 > 1000)
{
if (Progress.CancelRequested)
{
dao.ExecuteBufferedInserts();
conn.Commit();
conn.Close();
sr.Close();
Progress.Hide();
return(recCount.ToString() + " rows loaded");
}
Progress.Show("Loading file (" + recCount.ToString() + ") ...");
t1 = t2;
}
string rec = sr.ReadLine();
if (rec == null)
{
break;
}
List <string> vals = Csv.SplitCsvString(rec);
int cid;
try { cid = Int32.Parse(vals[cidIdx]); } // get compound id
catch (Exception ex)
{
string txtCid = vals[cidIdx];
if (txtCid == null)
{
txtCid = "";
}
DebugLog.Message("Load PubChem bad CID " + txtCid + ", AID = " + aid);
continue;
}
long rslt_grp_id = dao.GetNextIdLong(); // id to hold row together
for (int vi = 0; vi < vals.Count; vi++)
{
string s = vals[vi];
if (s == "")
{
continue;
}
string[] sa = rec.Split(',');
if (vi >= headers.Count)
{
continue;
}
string mcName = headers[vi].ToUpper();
if (mcName.Length > 26)
{
mcName = mcName.Substring(0, 26); // limit length to 26
}
if (mcName == "PUBCHEM_CID")
{
continue;
}
if (Lex.IsInteger(mcName))
{
mcName = "R_" + mcName; // result number
}
MetaColumn mc = mcd[mcName];
if (mc == null)
{
continue;
}
AnnotationVo vo = new AnnotationVo();
vo.rslt_grp_id = rslt_grp_id;
if (mc.DataType == MetaColumnType.String)
{
vo.rslt_val_txt = s;
}
else if (mc.DataType == MetaColumnType.Number || mc.DataType == MetaColumnType.Integer)
{
try
{
vo.rslt_val_nbr = Convert.ToDouble(s);
}
catch (Exception e) { continue; } // just continue if bad
}
else if (mc.DataType == MetaColumnType.Date)
{
s = DateTimeMx.Normalize(s);
if (s == null)
{
continue;
}
vo.rslt_val_dt = DateTimeMx.NormalizedToDateTime(s);
}
else if (mc.Name == "PUBCHEM_ACTIVITY_OUTCOME") // activity outcome is a dict value stored as an integer
{
try
{
vo.rslt_val_nbr = Convert.ToInt32(s);
}
catch (Exception e) { continue; } // just continue if bad
}
else if (mc.DataType == MetaColumnType.Hyperlink ||
mc.DataType == MetaColumnType.DictionaryId)
{
vo.rslt_val_txt = s;
}
else
{
continue;
}
vo.ext_cmpnd_id_nbr = cid;
vo.ext_cmpnd_id_txt = cid.ToString();
vo.mthd_vrsn_id = Int32.Parse(mt.TableFilterValues[0]);
vo.rslt_typ_id = Int32.Parse(mc.PivotValues[0]);
vo.chng_op_cd = "I";
vo.chng_usr_id = Security.UserInfo.UserName;
dao.Insert(vo);
} // end of field loop
recCount++;
if (recCount % 100 == 0)
{ // commit after group of updates
dao.ExecuteBufferedInserts();
conn.Commit();
conn.BeginTransaction(); // do multiple updates per transaction
}
} // end of record loop
dao.ExecuteBufferedInserts();
conn.Commit();
conn.Close();
dao.Dispose();
sr.Close();
//UpdateCids: // Add any missing CIDs under method 1000000
Progress.Show("Updating CID table...");
string sql =
"INSERT INTO " + PubChemWarehouseTable + "(ext_cmpnd_id_nbr,rslt_id,mthd_vrsn_id,rslt_typ_id,rslt_grp_id) " +
"SELECT ext_cmpnd_id_nbr, " + PubChemWarehouseSeq + ".NEXTVAL,1000000,0,0 " +
"FROM ( " +
"SELECT UNIQUE ext_cmpnd_id_nbr " +
"FROM " + PubChemWarehouseTable + " r1 " +
"WHERE mthd_vrsn_id = " + aid + " " +
"AND NOT EXISTS ( " +
" SELECT * " +
"FROM " + PubChemWarehouseTable + " r2 " +
"WHERE mthd_vrsn_id = 1000000 " +
"AND r2.ext_cmpnd_id_nbr = r1.ext_cmpnd_id_nbr) " +
"and rownum <= 10000)";
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.BeginTransaction();
int newCids = 0;
while (true)
{
int addedCids = drd.ExecuteNonReader();
if (addedCids == 0)
{
break;
}
newCids += addedCids;
drd.Commit();
drd.BeginTransaction(); // do multiple updates per transaction
Progress.Show("Updating CID table (" + newCids.ToString() + ")...");
}
drd.Dispose();
Progress.Hide();
return(recCount.ToString() + " rows loaded for AID " + aid + " plus " + newCids.ToString() + " new CIDs");
}