/// <summary>
/// Add sql for query to list of total sql for query
/// </summary>
/// <param name="mb"></param>
/// <param name="sql"></param>
internal void AddSqlToSqlStatementList(
GenericMetaBroker mb,
string sql)
{
string sqlWithDbLinks = sql;
DbConnectionMx mxConn = DbConnectionMx.MapSqlToConnection(ref sqlWithDbLinks); // add any dblinks as necessary
if (SqlList == null)
{
SqlList = new List <List <string> >();
}
List <string> sli = new List <string>();
if (Lex.IsDefined(mb.Label))
{
sli.Add(mb.Label);
}
else
{
sli.Add(mb.Qt.ActiveLabel + " (" + mb.Qt.MetaTable.Name + ")");
}
string formattedSql = OracleMx.FormatSql(sqlWithDbLinks);
sli.Add(formattedSql);
SqlList.Add(sli);
return;
}
/// <summary>
/// Establish Oracle connection for the Dao instance
/// </summary>
public void Connect()
{
DbCommandMx acd = new DbCommandMx();
this.DbCmd = acd;
string tName = TableName;
acd.MxConn = DbConnectionMx.MapSqlToConnection(ref tName);
}
/// <summary>
/// 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");
}
/// <summary>
/// Build and save SQL for use in Spotfire information link
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static int SaveSpotfireSql(
string sqlStmtName,
Query query)
{
DbConnectionMx conn = null;
string qtSql = "", sql, expr;
string keys = null;
string keyColName = "";
string t1KeyColExpr = "", keyColExpr;
string rootDataSource = "DEV857";
string rootSchema = "MBS_OWNER";
//if (query.Tables.Count != 1) throw new Exception("Can only save Spotfire Sql for single-table queries");
Query q = query.Clone();
// Clean up query before generating SQL
q.KeyCriteria = q.KeyCriteriaDisplay = "";
List <QueryTable> qtToRemove = new List <QueryTable>();
foreach (QueryTable qt0 in q.Tables)
{
if (qt0.SelectedCount <= 1 ||
!qt0.MetaTable.RetrievesDataFromQueryEngine)
{
qtToRemove.Add(qt0);
continue;
}
foreach (QueryColumn qc0 in qt0.QueryColumns) // clear any criteria
{
if (Lex.IsDefined(qc0.Criteria))
{
qc0.Criteria = qc0.CriteriaDisplay = "";
}
}
}
foreach (QueryTable qt0 in qtToRemove)
{
q.RemoveQueryTable(qt0);
}
string selectList = ""; // top-level list of selected columns
string fromList = ""; // sql for each QueryTable
string joinCriteria = ""; // join criteria between QueryTables
int remapCount = 0;
q.AssignUndefinedAliases();
q.MarkDuplicateNamesAndLabels();
for (int ti = 0; ti < q.Tables.Count; ti++)
{
QueryTable qt = q.Tables[ti];
if (ti == 0)
{
keyColName = qt.MetaTable.KeyMetaColumn.Name;
}
QueryEngine qe = new QueryEngine();
ExecuteQueryParms eqp = new ExecuteQueryParms(qe, qt);
eqp.ReturnQNsInFullDetail = false;
qtSql = qe.BuildSqlForSingleTable(eqp);
qtSql = Lex.Replace(qtSql, "/*+ hint */ ", "");
conn = DbConnectionMx.MapSqlToConnection(ref qtSql, rootDataSource, rootSchema); // convert SQL to use dblinks from root source/schema
if (conn == null)
{
throw new Exception("Connection not found for: " + rootDataSource);
}
// Recast numeric cols that are integers as integers for Spotfire
List <DbColumnMetadata> cmdList = OracleMx.GetColumnMetadataFromSql(qtSql, conn);
string qtSelectList = "";
remapCount = 0; // number of cols remapped
int sci = -1;
foreach (QueryColumn qc in qt.QueryColumns)
{
if (!qc.Selected)
{
continue;
}
sci++; // synch with cmdList
MetaColumn mc = qc.MetaColumn;
string mcName = mc.Name;
if (q.Tables.Count > 1) // if more than one table qualify by table name
{
mcName = qt.Alias + "." + mcName;
}
string colName = qc.UniqueName;
//if (mc.Name == "CORP_SBMSN_ID") mc = mc; // debug
//if (mc.DataType == MetaColumnType.CompoundId) mc = mc; // debug
if (mc.IsNumeric && (mc.DataType == MetaColumnType.Integer || mc.DataType == MetaColumnType.CompoundId))
{
DbColumnMetadata md = cmdList[sci];
expr = "cast (" + mcName + " as integer) " + colName; // integer same as number(22,0)--- " as number(38, 0)) " + expr;
}
else if (mcName != colName)
{
expr = mcName + " " + colName;
remapCount++;
}
else
{
expr = mc.Name;
}
if (qtSelectList != "")
{
qtSelectList += ", ";
}
qtSelectList += expr;
}
if (selectList != "")
{
selectList += ", ";
}
selectList += qtSelectList;
if (fromList != "")
{
fromList += ", ";
}
fromList += "(" + qtSql + ") " + qt.Alias;
keyColExpr = qt.Alias + "." + qt.KeyQueryColumn.MetaColumn.Name;
if (ti == 0)
{
t1KeyColExpr = keyColExpr;
}
else
{
if (joinCriteria != "")
{
joinCriteria += " and ";
}
joinCriteria += keyColExpr + " (+) = " + t1KeyColExpr;
}
}
selectList += " "; // be sure last col name in list is delimited with a space
if (q.Tables.Count == 1 && remapCount == 0)
{
sql = qtSql; // simple single table with no remapping of cols
}
else // combine list of elements
{
sql =
"select " + selectList +
" from " + fromList;
if (joinCriteria != "")
{
sql += " where " + joinCriteria;
}
sql = "select * from (" + sql + ")"; // encapsulate the SQL
}
int v2 = SpotfireDao.InsertSpotfireSql(sqlStmtName, 0, sql, keyColName, null, Security.UserName);
return(v2);
}
