/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="sourceName"></param>
/// <param name="schemaName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetColumnMetadataFromSql(
string sql,
DbConnectionMx conn)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata md;
List <DbColumnMetadata> mdList = new List <DbColumnMetadata>();
string sql2 = sql + " where 1=2"; // make execution fast
DbCommandMx cmd = new DbCommandMx();
cmd.MxConn = conn;
cmd.PrepareUsingDefinedConnection(sql2);
OracleDataReader rdr = cmd.ExecuteReader() as OracleDataReader;
for (int fi = 0; fi < rdr.FieldCount; fi++)
{
md = new DbColumnMetadata();
md.Name = rdr.GetName(fi);
md.Type = rdr.GetDataTypeName(fi); //
mdList.Add(md);
}
rdr.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(mdList);
}
/// <summary>
/// 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);
}
public static string SelectString(
DbConnectionMx mxConn,
string sql)
{
string value;
DbDataReader dr = null;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = mxConn; // set connection to use
drd.Prepare(sql);
dr = drd.ExecuteReader();
if (!dr.Read())
{
throw (new Exception("SelectString Read failed"));
}
if (dr.IsDBNull(0))
{
value = "";
}
else
{
value = dr.GetString(0);
}
dr.Close();
drd.Dispose();
return(value);
}
/// <summary>
/// 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>
/// 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>
/// 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>
/// 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>
/// Get a metatable from a database catalog
/// </summary>
/// <param name="schemaDotTableName"></param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
string schemaDotTableName)
{
string[] sa = schemaDotTableName.Split('.');
if (sa.Length != 2)
{
return(null);
}
string schemaName = sa[0];
string tableName = sa[1];
DbSchemaMx schema = DbSchemaMx.GetSchemaInfoFromName(schemaName);
if (schema == null)
{
return(null); // unknown schema
}
if (Lex.IsDefined(schema.AliasFor))
{
schemaName = schema.AliasFor;
}
string dsName = schema.DataSourceName;
DataSourceMx dataSource = DbConnectionMx.GetRootDataSource(schemaDotTableName);
if (dataSource == null)
{
return(null);
}
DbConnectionMx conn = DbConnectionMx.GetConnection(dataSource.DataSourceName);
if (dataSource.DbType == DatabaseType.MySql)
{
return(MySqlMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
else if (dataSource.DbType == DatabaseType.Oracle)
{
return(OracleMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
else if (dataSource.DbType == DatabaseType.ODBC)
{
return(OdbcMx.GetMetaTableFromDatabaseDictionary(conn, schemaName, tableName));
}
throw new Exception("Database dictionary not supported for: " + schemaDotTableName);
}
/// <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>
/// Return true if this metatable maps to an ODBC source
/// </summary>
/// <param name="mt"></param>
/// <returns></returns>
public static bool IsOdbcMetatable(MetaTable mt)
{
if (DbConnectionMx.IsSqlFromOdbcSource(mt.TableMap))
{
return(true);
}
else
{
return(false);
}
//if (Lex.Contains(mt.TableMap, "dcsgtr") || Lex.Contains(mt.TableMap, "knimeivdr")) return true; // hack for now
// else return false;
}
/// <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;
}
public static List <DbColumnMetadata> GetTableMetadataFromMySqlDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
string sql = String.Format(
@"SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = '{0}'
AND table_name = '{1}'
ORDER BY ORDINAL_POSITION" ,
schema, tableName);
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
drd.PrepareUsingDefinedConnection(sql);
DbDataReader rdr = drd.ExecuteReader();
List <DbColumnMetadata> md = new List <DbColumnMetadata>();
while (rdr.Read())
{
DbColumnMetadata cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetLongByName("character_maximum_length");
cmd.Precision = drd.GetIntByName("numeric_precision");
cmd.Scale = drd.GetIntByName("numeric_scale");
cmd.Nullable = drd.GetStringByName("is_nullable");
cmd.Comment = drd.GetStringByName("column_comment");
md.Add(cmd);
}
rdr.Close();
return(md);
}
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>
/// 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>
/// Get metatable if name belongs to broker
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public MetaTable GetMetaTable(
string name)
{
UserObject uo = null;
int objectId, i1;
string tok;
Stopwatch sw = Stopwatch.StartNew();
if (!UserObject.IsAnnotationTableName(name) &&
!UserObject.IsUserDatabaseStructureTableName(name))
{
return(null);
}
try { objectId = UserObject.ParseObjectIdFromInternalName(name); }
catch (Exception ex) { return(null); }
try { uo = UserObjectDao.Read(objectId); }
catch (MobiusConnectionOpenException ex)
{ DbConnectionMx.ThrowSpecificConnectionOpenException(ex, "mbs_owner"); }
if (uo == null)
{
return(null);
}
if (!Permissions.UserHasReadAccess(Security.UserName, uo))
{
return(null);
}
MetaTable mt = MetaTable.Deserialize(uo.Content);
if (mt == null)
{
return(null); // something wrong with the annotation table content
}
MetaColumn keyMc = mt.KeyMetaColumn;
MetaColumn rootMc = mt.Root.KeyMetaColumn;
if (rootMc.IsNumeric) // adjust key col mapping
{
keyMc.ColumnMap = "EXT_CMPND_ID_NBR";
}
else
{
keyMc.ColumnMap = "EXT_CMPND_ID_TXT";
}
while (mt.MetaColumns.Count > MetaTable.MaxColumns) // fixup for table with too many columns
{
mt.MetaColumns.RemoveAt(mt.MetaColumns.Count - 1);
}
foreach (MetaColumn mc in mt.MetaColumns)
{ // set DetailsAvailable for existing annotation tables */
if (mc.ResultCode != "")
{
mc.DetailsAvailable = true;
}
}
mt.Label = uo.Name; // get latest name (may have changed if renamed after saved)
if (MetaTableFactory.ShowDataSource) // add source to label if requested
{
mt.Label = MetaTableFactory.AddSourceToLabel(mt.Name, mt.Label);
}
long ms = sw.ElapsedMilliseconds;
return(mt);
}
/// <summary>
/// Write temporary list
/// </summary>
/// <param name="dsName">Name of datasource</param>
/// <param name="listName">Name of list</param>
/// <param name="listItems">The list of items to write</param>
/// <param name="numeric">Store list items in numeric column if true, text otherwise</param>
public static string Write(
string dsName,
string listName,
List <string> listItems,
int firstKeyIdx,
int keyCount,
bool numeric)
{
DbCommandMx cmd = null;
Oracle.DataAccess.Client.OracleDbType parmType;
string fullTableName, sKey;
int iKey, i1;
object o;
DataSourceMx ds = GetDataSourceInfo(ref dsName, ref listName, out fullTableName);
TruncateListTable(dsName, listName);
if (keyCount == 0)
{
return(null); // nothing to write
}
string sql = "insert into <schema.name> (<cols>) values(:0)";
sql = sql.Replace("<schema.name>", fullTableName);
if (numeric)
{
sql = sql.Replace("<cols>", "intKey");
}
else
{
sql = sql.Replace("<cols>", "stringKey");
}
object[][] pva = DbCommandMx.NewObjectArrayArray(1, keyCount); // alloc insert row array
if (numeric)
{
for (i1 = 0; i1 < keyCount; i1++)
{
if (int.TryParse(listItems[firstKeyIdx + i1], out iKey))
{
pva[0][i1] = iKey;
}
else
{
pva[0][i1] = DBNull.Value;
}
}
parmType = Oracle.DataAccess.Client.OracleDbType.Int32;
}
else // string keys
{
for (i1 = 0; i1 < keyCount; i1++)
{
sKey = listItems[firstKeyIdx + i1];
if (!Lex.IsNullOrEmpty(sKey))
{
pva[0][i1] = sKey;
}
else
{
pva[0][i1] = DBNull.Value;
}
}
parmType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
}
try
{
DateTime t0 = DateTime.Now;
cmd = new DbCommandMx();
DbConnectionMx dbc = DbConnectionMx.GetConnection(dsName);
cmd.MxConn = dbc;
cmd.Prepare(sql, parmType);
int insCount = cmd.ExecuteArrayNonReader(pva, ref keyCount);
int tDelta = (int)TimeOfDay.Delta(t0);
if (Debug)
{
DebugLog.Message("Write TempDbList for KeyCount: " + insCount + ", Time: " + tDelta);
}
}
catch (Exception ex)
{
throw ex;
}
finally { cmd.Dispose(); }
return(fullTableName);
}
/// <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);
}
/// <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);
}
/// <summary>
/// Get a metatable from MySql catalog
/// </summary>
/// <param name="tableName">schema.table</param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
DbConnectionMx conn,
string schema,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Generic;
mt.Name = tableName;
mt.Label = MetaTable.IdToLabel(tableName);
mt.TableMap = schema + "." + tableName;
List <DbColumnMetadata> cmdList = GetTableMetadataFromMySqlDictionary(conn, schema, tableName);
for (int ci = 0; ci < cmdList.Count; ci++)
{
DbColumnMetadata cmd = cmdList[ci];
MetaColumn mc = new MetaColumn();
mc.Name = cmd.Name;
mc.ColumnMap = mc.Name;
mc.Label = MetaTable.IdToLabel(cmd.Name);
if (Lex.Contains(cmd.Type, "CHAR") ||
Lex.Contains(cmd.Type, "TEXT"))
{
mc.DataType = MetaColumnType.String;
}
else if (Lex.Contains(cmd.Type, "INT") ||
Lex.Contains(cmd.Type, "ENUM"))
{
mc.DataType = MetaColumnType.Integer;
}
else if (cmd.Type == "FLOAT" ||
cmd.Type == "REAL" ||
cmd.Type == "DOUBLE" ||
cmd.Type == "DECIMAL" ||
cmd.Type == "NUMERIC")
{
mc.DataType = MetaColumnType.Number;
mc.Format = ColumnFormatEnum.Decimal;
mc.Decimals = cmd.Scale;
}
else if (cmd.Type == "DATE" ||
cmd.Type == "DATETIME" ||
cmd.Type == "TIMESTAMP")
{
mc.DataType = MetaColumnType.Date;
}
else
{
continue; // unrecognized
}
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Width = 12;
mc.MetaTable = mt;
mc.Description = cmd.Comment;
mt.AddMetaColumn(mc);
}
t0 = TimeOfDay.Milliseconds() - t0;
return(mt);
}
/// GetMetaTableFromOdbcDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Generic;
mt.Name = tableName;
mt.Label = MetaTable.IdToLabel(tableName);
mt.TableMap = tableName;
string sql = "select * from " + tableName;
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
drd.PrepareUsingDefinedConnection(sql);
drd.ExecuteReader();
DataTable st = drd.Rdr.GetSchemaTable();
for (int ri = 0; ri < st.Rows.Count; ri++)
{
DataRow dr = st.Rows[ri];
string column_name = dr["ColumnName"] as string;
Type type = dr["DataType"] as Type;
int data_length = (int)dr["ColumnSize"];
int data_precision = (short)dr["NumericPrecision"];
int data_scale = (short)dr["NumericScale"];
bool nullable = (bool)dr["AllowDBNull"];
MetaColumn mc = new MetaColumn();
mc.Name = column_name;
mc.ColumnMap = mc.Name;
mc.Label = MetaTable.IdToLabel(column_name);
if (Lex.Eq(type.Name, "String"))
{
mc.DataType = MetaColumnType.String;
}
else if (Lex.StartsWith(type.Name, "Int"))
{
mc.DataType = MetaColumnType.Integer;
}
else if (Lex.Eq(type.Name, "Single") ||
Lex.Eq(type.Name, "Double"))
{
mc.DataType = MetaColumnType.Number;
mc.Format = ColumnFormatEnum.SigDigits; // display with 3 sig figures by default
mc.Decimals = 3;
}
else if (Lex.Eq(type.Name, "DateTime"))
{
mc.DataType = MetaColumnType.Date;
}
else
{
continue; // unrecognized
}
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Width = 12;
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(mt);
}
/// <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>
/// GetTableMetadataFromOracleDictionary
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <DbColumnMetadata> GetTableMetadataFromOracleDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
DbColumnMetadata cmd;
List <DbColumnMetadata> cmdList = new List <DbColumnMetadata>();
string sql = "select column_name,data_type,data_length,data_precision,data_scale,nullable " +
"from sys.all_tab_columns where owner=:0 " +
"and table_name=:1 order by column_id";
if (conn == null)
{
throw new Exception("Connection not found for tableName: " + tableName);
}
DbCommandMx drd = new DbCommandMx();
drd.MxConn = conn;
int parmCount = 2;
drd.PrepareMultipleParameter(sql, parmCount);
string[] sa = tableName.Split('.');
if (sa.Length != 2)
{
throw new Exception("TableName not in owner.tableName form: " + tableName);
}
string creator = sa[0];
string tname = sa[1];
if (Lex.Eq(creator, "mbs_user")) // workaround to allow tables owned by dev mbs_owner
{
creator = "mbs_owner"; // to be accessed via synonyms defined on dev mbs_user
}
object[] p = new object[2];
p[0] = creator.ToUpper();
p[1] = tname.ToUpper();
drd.ExecuteReader(p);
while (drd.Read())
{
cmd = new DbColumnMetadata();
cmd.Name = drd.GetStringByName("column_name");
cmd.Type = drd.GetStringByName("data_type");
cmd.Length = drd.GetIntByName("data_length");
cmd.Precision = drd.GetIntByName("data_precision");
cmd.Scale = drd.GetIntByName("data_scale");
cmd.Nullable = drd.GetStringByName("nullable");
cmdList.Add(cmd);
}
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
return(cmdList);
}
/// <summary>
/// Get a metatable from Oracle catalog
/// </summary>
/// <param name="tableName">schema.table</param>
/// <returns></returns>
public static MetaTable GetMetaTableFromDatabaseDictionary(
DbConnectionMx conn,
string schemaName,
string tableName)
{
int t0 = TimeOfDay.Milliseconds();
MetaTable mt = new MetaTable();
mt.MetaBrokerType = MetaBrokerType.Generic;
mt.Name = tableName;
mt.Label = MetaTable.IdToLabel(tableName);
mt.TableMap = schemaName + "." + tableName;
List <DbColumnMetadata> cmdList = GetTableMetadataFromOracleDictionary(conn, schemaName, tableName);
for (int ci = 0; ci < cmdList.Count; ci++)
{
DbColumnMetadata cmd = cmdList[ci];
MetaColumn mc = new MetaColumn();
mc.Name = cmd.Name;
mc.ColumnMap = mc.Name;
mc.Label = MetaTable.IdToLabel(cmd.Name);
if (cmd.Type == "VARCHAR" ||
cmd.Type == "VARCHAR2" ||
cmd.Type == "NVARCHAR2" ||
cmd.Type == "CHAR" ||
cmd.Type == "CHARACTER" ||
cmd.Type == "LONG")
{
mc.DataType = MetaColumnType.String;
}
else if (cmd.Type == "INTEGER")
{
mc.DataType = MetaColumnType.Integer;
}
else if (cmd.Type == "NUMBER" ||
cmd.Type == "FLOAT")
{
mc.DataType = MetaColumnType.Number;
mc.Format = ColumnFormatEnum.SigDigits; // display with 3 sig figures by default
mc.Decimals = 3;
}
else if (cmd.Type == "DATE" || cmd.Type.StartsWith("TIMESTAMP"))
{
mc.DataType = MetaColumnType.Date;
}
else
{
continue; // unrecognized
}
mc.InitialSelection = ColumnSelectionEnum.Selected;
mc.Width = 12;
mc.MetaTable = mt;
mt.AddMetaColumn(mc);
}
t0 = TimeOfDay.Milliseconds() - t0;
return(mt);
}
