/// <summary>
/// Set the size of the local cache for the sequence
/// </summary>
/// <param name="seqName"></param>
/// <param name="size"></param>
public static void SetCacheSize(
string seqName,
int size)
{
SequenceDao seqDao = Lookup(seqName);
seqDao.CacheSize = size;
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>
/// Get a block of new row ids
/// </summary>
/// <param name="idCount"></param>
/// <returns></returns>
public long[] GetNextIdsLong(int idCount)
{
SequenceDao.SetCacheSize(SeqName, idCount);
long[] ids = new long[idCount];
for (int i1 = 0; i1 < idCount; i1++)
{
ids[i1] = GetNextIdLong();
}
SequenceDao.SetCacheSize(SeqName, 1); // reset
return(ids);
}
/// <summary>
/// Get the next value for the sequence (Oracle)
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
public static long NextValLongOracle(
string seqName)
{
string sql;
long nextVal;
SequenceDao seqDao = Lookup(seqName);
Queue <long> seqQueue = seqDao.Queue;
if (seqQueue.Count > 0)
{
nextVal = seqQueue.Dequeue();
return(nextVal);
}
if (seqDao.CacheSize <= 0)
{
sql = "select " + seqName + ".nextval from dual";
}
else
{
sql = "select /*+ first_rows */ " + seqName + ".nextval from sys.all_catalog where rownum <= " +
(seqDao.CacheSize + 1).ToString();
}
int t0 = TimeOfDay.Milliseconds();
DbCommandMx drd = new DbCommandMx();
drd.Prepare(sql);
drd.ExecuteReader();
if (!drd.Read())
{
throw (new Exception("SequenceDao.NextVal Read failed"));
}
nextVal = drd.GetLong(0); // return this one
while (drd.Read()) // enqueue the rest
{
seqQueue.Enqueue(drd.GetLong(0));
}
drd.CloseReader();
drd.Dispose();
t0 = TimeOfDay.Milliseconds() - t0;
// DebugLog.Message("Read sequence, set size = " + seqQueue.Count.ToString() + ", Time(ms) = " + t0.ToString());
return(nextVal);
}
/// <summary>
/// Lookup a sequence by name & create if doesn't already exist
/// </summary>
/// <param name="seqName"></param>
/// <returns></returns>
static SequenceDao Lookup(
string seqName)
{
if (Sequences == null)
{
Sequences = new Dictionary <string, SequenceDao>();
}
if (Sequences.ContainsKey(seqName))
{
return(Sequences[seqName]);
}
else
{
SequenceDao seqDao = new SequenceDao();
Sequences.Add(seqName, seqDao);
return(seqDao);
}
}
/// <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 next identifier from sequence
/// </summary>
/// <returns></returns>
public long GetNextIdLong()
{
long id = SequenceDao.NextValLong(SeqName);
return(id);
}
/// <summary>
/// Set size for cache
/// </summary>
/// <param name="cacheSize"></param>
public void SetSequenceCacheSize(
int cacheSize)
{
SequenceDao.SetCacheSize(SeqName, cacheSize);
}
/// <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);
}
