public void WriteTraceIrregular()
{
// All database changes should be directed into these temp tables, since the tested methods
// use SqlBulkCopy, which is not subject to transaction scope.
CreateTempTables();
DateTime startDate = DateTime.Parse("2/10/2000");
DateTime endDate = DateTime.Parse("2/10/2002");
int timeStepCount = 40;
int id, traceNumber = 27;
String extraParamNames = "TimeSeriesType, Unit_Id, RunGUID, VariableType, VariableName, RunElementGUID";
String extraParamValues = "0, 1, 'A0101010-AAAA-BBBB-2222-3E3E3E3E3E3E', 0, 'eraseme', '00000000-0000-0000-0000-000000000000'";
id = _lib.WriteParametersIrregular(_connxNumber, GetSbyte(_TestParamTableName), GetSbyte(_TestTraceTableName),
timeStepCount, startDate, endDate, GetSbyte(extraParamNames), GetSbyte(extraParamValues));
TSDateValueStruct[] dateValArray = new TSDateValueStruct[timeStepCount],
testDateValArray = new TSDateValueStruct[timeStepCount];
double x = 10.0;
double y = 1.0;
DateTime curDate = startDate;
for (int i = 0; i < timeStepCount; i++)
{
dateValArray[i].Value = x;
dateValArray[i].Date = curDate;
x *= 1.2;
y += 0.5;
curDate = curDate.AddDays(y);
}
// The method being tested
_lib.WriteTraceIrregular(_connxNumber, GetSbyte(_TestParamTableName), GetSbyte(_TestTraceTableName),
id, traceNumber, dateValArray);
_lib.CommitTraceWrites(_connxNumber, GetSbyte(_TestParamTableName), GetSbyte(_TestTraceTableName));
String comm = String.Format("select * from {0} where TimeSeries_Id={1} and TraceNumber={2}",
_TestTraceTableName, id, traceNumber);
// SqlDataAdapter object will use the query to fill the DataTable
using (SqlDataAdapter adp = new SqlDataAdapter(comm, _connx))
{
DataTable dTable = new DataTable();
// Execute the query to fill the DataTable object
adp.Fill(dTable);
DataRow row = dTable.Rows[0];
byte[] blob = row.Field <byte[]>("ValueBlob");
TSBlobCoder.ConvertBlobToArrayIrregular(timeStepCount,
false, 0, startDate, startDate, blob, testDateValArray, TSBlobCoder.currentCompressionCode);
//
for (int i = 0; i < timeStepCount; i++)
{
Assert.AreEqual(dateValArray[i], testDateValArray[i]);
}
}
}
public void Test(Boolean inBulk, Boolean doParam)
{
// Create dummy time series that we can write to the database
var valList = new List <double[]>();
for (int i = 0; i < nIter; i++)
{
var iterList = new List <double>();
for (int t = 0; t < nVals; t++)
{
iterList.Add(1.5 * t + i + 0.33);
}
valList.Add(iterList.ToArray());
}
if (inBulk)
{
var traceObjects = new List <ITimeSeriesTrace>();
for (int i = 0; i < nIter; i++)
{
ITimeSeriesTrace traceObject = new TSTrace {
TraceNumber = 1
};
// Convert the array of double values into a byte array...a BLOB
traceObject.ValueBlob = TSBlobCoder.ConvertArrayToBlobRegular
(valList[i], TSBlobCoder.currentCompressionCode, traceObject);
traceObjects.Add(traceObject);
}
WriteBulkTraces(traceObjects);
}
else if (doParam)
{
// reference: http://stackoverflow.com/questions/2449827/pros-and-cons-of-using-sqlcommand-prepare-in-c
// this does appear to be faster!!
SqlCommand cmd = new SqlCommand("INSERT INTO " + TableName
+ "(TimeSeries_Id, TraceNumber, ValueBlob, Checksum) "
+ "VALUES (@TimeSeries_Id, @TraceNumber, @ValueBlob, @Checksum)", Connx);
cmd.Parameters.Add("@TimeSeries_Id", SqlDbType.Int);
cmd.Parameters.Add("@TraceNumber", SqlDbType.Int);
cmd.Parameters.Add("@ValueBlob", SqlDbType.VarBinary, -1);
cmd.Parameters.Add("@Checksum", SqlDbType.Binary, 16);
cmd.Prepare();
SqlCommand selectCmd = new SqlCommand("SELECT TraceNumber, Checksum from "
+ TableName + " where TimeSeries_Id=@TimeSeries_Id order by TraceNumber", Connx);
selectCmd.Parameters.Add("@TimeSeries_Id", SqlDbType.Int);
selectCmd.Prepare();
SqlCommand updateCmd = new SqlCommand("UPDATE " + "OutputTimeSeries"
+ " SET Checksum=@Checksum WHERE Id=@Id", Connx);
updateCmd.Parameters.Add("@Checksum", SqlDbType.Binary, 16);
updateCmd.Parameters.Add("@Id", SqlDbType.Int);
updateCmd.Prepare();
for (int i = 0; i < nIter; i++)
{
ITimeSeriesTrace traceObject = new TSTrace {
TraceNumber = 1
};
// Convert the array of double values into a byte array...a BLOB
traceObject.ValueBlob = TSBlobCoder.ConvertArrayToBlobRegular
(valList[i], TSBlobCoder.currentCompressionCode, traceObject);
WriteOneTraceParam(i + 1, traceObject, cmd);
// update the checksum in the parameters table
UpdateParametersChecksum(i + 1, selectCmd, updateCmd);
}
}
else
{
for (int i = 0; i < nIter; i++)
{
ITimeSeriesTrace traceObject = new TSTrace {
TraceNumber = 1
};
// Convert the array of double values into a byte array...a BLOB
traceObject.ValueBlob = TSBlobCoder.ConvertArrayToBlobRegular
(valList[i], TSBlobCoder.currentCompressionCode, traceObject);
WriteOneTrace(traceObject);
}
}
}
// Note--this test class was created primarily b/c we wanted to individually test the different
// compression methods. Hence, we have created the test methods below. The class TSBlobCoder
// certainly deserves full test coverage, but that can be developed later. In the meantime,
// it is generally expected that TSBlobCoder has adequate test coverage via TSLibraryTest.
#region Test Methods for ConvertBlobToListAll() and ConvertListToBlob()
// The series of tests below is for ConvertBlobToListAll and ConvertListToBlob.
// The tests take advantage of the fact that the methods are designed so that
// the series that is put into the BLOB must be identical to the series that
// comes out of the BLOB.
// This method is re-used by the actual test methods that follow.
public void ConvertBlobAll(List <TimeSeriesValue> inList,
TSDateCalculator.TimeStepUnitCode timeStepUnit, short timeStepQuantity,
DateTime blobStartDate, int compressionCode)
{
TSLibrary tsLib = new TSLibrary();
var traceObject = new TSTrace
{
TraceNumber = 1,
TimeStepCount = inList.Count,
EndDate = inList.Last().Date
};
if (timeStepUnit == TSDateCalculator.TimeStepUnitCode.Irregular)
{
var inArray = inList.Select(v => (TSDateValueStruct)v).ToArray();
var outArray = new TSDateValueStruct[inList.Count];
byte[] blobData = TSBlobCoder.ConvertArrayToBlobIrregular(inArray,
compressionCode, traceObject);
int ret = TSBlobCoder.ConvertBlobToArrayIrregular(inList.Count,
false, 0, blobStartDate, blobStartDate,
blobData, outArray, compressionCode);
// The return value of the function must match the number of items in the original list
Assert.AreEqual(ret, inList.Count);
// the count in both lists must match
Assert.AreEqual(inArray.Length, outArray.Length);
// now check each item in the two lists
Boolean AreEqual = true;
for (int i = 0; i < ret; i++)
{
if (outArray[i].Date != inArray[i].Date || outArray[i].Value != inArray[i].Value)
{
AreEqual = false;
}
}
Assert.IsTrue(AreEqual);
}
else
{
var inArray = inList.Select(v => v.Value).ToArray();
var outArray = new Double[inList.Count];
byte[] blobData = TSBlobCoder.ConvertArrayToBlobRegular(inArray,
compressionCode, traceObject);
int ret = TSBlobCoder.ConvertBlobToArrayRegular(timeStepUnit, timeStepQuantity,
inList.Count, blobStartDate,
false, 0, blobStartDate, blobStartDate,
blobData, outArray, compressionCode);
// The return value of the function must match the number of items in the original list
Assert.AreEqual(ret, inList.Count);
// the count in both lists must match
Assert.AreEqual(inArray.Length, outArray.Length);
// now check each item in the two lists
Boolean AreEqual = true;
for (int i = 0; i < ret; i++)
{
if (outArray[i] != inArray[i])
{
AreEqual = false;
}
}
Assert.IsTrue(AreEqual);
}
}
public void WriteMultiTraceRegular()
{
int id = 0, traceNumber = 0, traceCount = 0;
// Find an existing time series that has at least one trace and where the
// time series type is not irregular
String comm = "SELECT TOP(1) x.Id FROM " + _traceTableName + " t\n"
+ "JOIN " + _paramTableName + " x ON t.TimeSeries_Id=x.Id\n"
+ "WHERE x.TimeSeriesType != 0";
using (var queryCommand = new SqlCommand(comm, _connx)
{
CommandTimeout = 0
})
using (var reader = queryCommand.ExecuteReader())
{
// Loop through every record in the result set
while (reader.Read())
{
// Populate an array of Objects with the query results for this record
Object[] valueArray = new Object[1];
reader.GetValues(valueArray);
id = (int)valueArray[0];
}
}
// Get the highest existing trace number of the existing time series
comm = "SELECT TOP(1) t.[TraceNumber] FROM " + _traceTableName + " t\n"
+ "where t.[TimeSeries_Id]=" + id + "\norder by t.[TraceNumber] desc";
using (var queryCommand = new SqlCommand(comm, _connx)
{
CommandTimeout = 0
})
using (var reader = queryCommand.ExecuteReader())
{
// Loop through every record in the result set
while (reader.Read())
{
// Populate an array of Objects with the query results for this record
Object[] valueArray = new Object[1];
reader.GetValues(valueArray);
traceNumber = (int)valueArray[0];
}
}
// Get the highest existing trace number of the existing time series
comm = "SELECT COUNT(*) FROM " + _traceTableName + " t\n"
+ "where t.[TimeSeries_Id]=" + id;
using (var queryCommand = new SqlCommand(comm, _connx)
{
CommandTimeout = 0
})
using (var reader = queryCommand.ExecuteReader())
{
// Loop through every record in the result set
while (reader.Read())
{
// Populate an array of Objects with the query results for this record
Object[] valueArray = new Object[1];
reader.GetValues(valueArray);
traceCount = (int)valueArray[0];
}
}
// Read the values of the existing trace that has the highest trace number
int ArrayDim = 5000;
var valArray = new Double[ArrayDim];
int nVals = _lib.ReadValuesRegular(_connxNumber,
GetSbyte(_paramTableName), GetSbyte(_traceTableName),
id, traceNumber, ArrayDim, valArray, DateTime.MinValue, DateTime.MaxValue);
var newValArray = valArray.Take(nVals).ToArray();
// Create temporary tables that will absorb all data changes. We do this because
// the tested methods use SqlBulkCopy, which is not subject to TransactionScope.
CreateTempTablesFromExisting(id);
// The method being tested--we add another trace that is identical to the one we just
// read, but give it a new trace number.
_lib.WriteTraceRegular(_connxNumber, GetSbyte(_TestParamTableName), GetSbyte(_TestTraceTableName),
id, traceNumber + 1, newValArray);
_lib.CommitTraceWrites(_connxNumber, GetSbyte(_TestParamTableName), GetSbyte(_TestTraceTableName));
// Now verify that the new trace is written to the DB,
// and that the checksum of the time series includes all traces including the new one
comm = "SELECT c.[Checksum], c.[TimeStepUnit], c.[TimeStepQuantity], "
+ "c.[StartDate],\n t.[TraceNumber], t.[Checksum]\n"
+ "FROM [" + _TestParamTableName + "] c\n"
+ "LEFT JOIN [" + _TestTraceTableName + "] t ON c.[Id]=t.[TimeSeries_Id]\n"
+ "WHERE c.[Id]=" + id;
using (var queryCommand = new SqlCommand(comm, _connx)
{
CommandTimeout = 0
})
using (var reader = queryCommand.ExecuteReader())
{
Boolean initialized = false;
var traceList = new List <ITimeSeriesTrace>();
Byte[] checksum = new Byte[0];
var timeStepUnit = TSDateCalculator.TimeStepUnitCode.Day;
short timeStepQuantity = 1;
DateTime startDate = DateTime.MinValue;
// Loop through every record in the result set
while (reader.Read())
{
// Populate an array of Objects with the query results for this record
Object[] valueArray = new Object[6];
reader.GetValues(valueArray);
if (!initialized)
{
checksum = (Byte[])valueArray[0];
timeStepUnit = (TSDateCalculator.TimeStepUnitCode)(int) valueArray[1];
timeStepQuantity = (short)(int)valueArray[2];
startDate = (DateTime)valueArray[3];
initialized = true;
}
// Add to the collection a container object for this trace's properties
traceList.Add(new TSTrace
{
TraceNumber = (int)valueArray[4],
Checksum = (Byte[])valueArray[5]
});
}
// Is the new trace in the DB?
Assert.IsTrue(traceList.Any(o => o.TraceNumber == traceNumber + 1));
// Is the actual number of traces incremented by one?
Assert.AreEqual(traceCount + 1, traceList.Count);
// Do an independent computation of the checksum
var correctChecksum = TSBlobCoder.ComputeChecksum(timeStepUnit, timeStepQuantity,
startDate, traceList);
// was the correct checksum stored in the DB?
Assert.IsTrue(NumericExtensions.ByteArraysAreEqual(correctChecksum, checksum));
}
}
