public IEnumerable <PackedReading> GetReadings(string sensor, DateTime from, TimeSpan span)
{
DateTime to = from.Add(span);
if (to < from)
{
DateTime s = to;
to = from;
from = s;
}
if (!ForceConnectionOpen())
{
throw new Exception("Could not open database.");
}
using (var command = _connection.CreateTextCommand(
"SELECT stamp,[values] FROM Record"
+ " INNER JOIN Sensor ON (Sensor.sensorId = Record.sensorId)"
+ " WHERE Sensor.nameKey = @sensorNameKey"
+ " AND Record.stamp >= @minPosixStamp"
+ " AND Record.stamp <= @maxPosixStamp"
+ " ORDER BY stamp " + ((span < TimeSpan.Zero) ? "DESC" : "ASC")
)) {
command.AddParameter("minPosixStamp", DbType.Int32, UnitUtility.ConvertToPosixTime(from));
command.AddParameter("maxPosixStamp", DbType.Int32, UnitUtility.ConvertToPosixTime(to));
command.AddParameter("sensorNameKey", DbType.String, sensor);
using (var reader = command.ExecuteReader()) {
int ordStamp = reader.GetOrdinal("stamp");
int ordValues = reader.GetOrdinal("values");
byte[] values = new byte[8];
const int chunkSize = 256;
bool isReading = true;
var cache = new List <PackedReading>(chunkSize);
while (isReading)
{
cache.Clear();
for (int i = 0; i < chunkSize; i++)
{
if (!reader.Read())
{
isReading = false;
break;
}
reader.GetBytes(ordValues, 0, values, 0, values.Length);
cache.Add(new PackedReading(
UnitUtility.ConvertFromPosixTime(reader.GetInt32(ordStamp)),
PackedReadingValues.ConvertFromPackedBytes(values, 0)
));
}
foreach (var item in cache)
{
yield return(item);
}
}
}
}
}
private TimeRange PushReadings <T>(
IDbCommand command, IEnumerable <T> readings, Func <T, byte[]> conversion,
DbParameter stampParam, DbParameter valuesParam
)
where T : IReading
{
DateTime minStamp = new DateTime(9999, 1, 1);
DateTime maxStamp = new DateTime(0);
int counter = 0;
try {
command.Transaction = _connection.BeginTransaction();
foreach (var reading in readings)
{
stampParam.Value = UnitUtility.ConvertToPosixTime(reading.TimeStamp);
valuesParam.Value = conversion(reading);
command.ExecuteNonQuery();
counter++;
if (counter >= RecordBatchQuantity)
{
counter = 0;
command.Transaction.Commit();
command.Transaction = _connection.BeginTransaction();
}
if (reading.TimeStamp < minStamp)
{
minStamp = reading.TimeStamp;
}
if (reading.TimeStamp > maxStamp)
{
maxStamp = reading.TimeStamp;
}
}
if (command.Transaction != null)
{
command.Transaction.Commit();
}
}
catch (Exception ex) {
if (null != command.Transaction)
{
command.Transaction.Rollback();
}
}
return(new TimeRange(minStamp, maxStamp));
}
private IEnumerable <T> GetPackedSummaries <T>(string sensor, DateTime from, TimeSpan span, string tableName)
where T : PackedReadingsSummary, new()
{
DateTime to = from.Add(span);
if (to < from)
{
DateTime s = to;
to = from;
from = s;
}
if (!ForceConnectionOpen())
{
throw new Exception("Could not open database.");
}
using (var command = _connection.CreateTextCommand(String.Format(
"SELECT stamp,minValues,maxValues,meanValues,stddevValues,recordCount"
+ ",tempCount,pressCount,humCount,speedCount,dirCount"
+ " FROM [{0}]"
+ " INNER JOIN Sensor ON (Sensor.sensorId = [{0}].sensorId)"
+ " WHERE Sensor.nameKey = @sensorNameKey"
+ " AND [{0}].stamp >= @minPosixStamp"
+ " AND [{0}].stamp < @maxPosixStamp"
+ " ORDER BY stamp " + ((span < TimeSpan.Zero) ? "DESC" : "ASC"),
tableName
))) {
command.AddParameter("minPosixStamp", DbType.Int32, UnitUtility.ConvertToPosixTime(from));
command.AddParameter("maxPosixStamp", DbType.Int32, UnitUtility.ConvertToPosixTime(to));
command.AddParameter("sensorNameKey", DbType.String, sensor);
using (IDataReader reader = command.ExecuteReader()) {
int ordStamp = reader.GetOrdinal("stamp");
int ordMinValues = reader.GetOrdinal("minValues");
int ordMaxValues = reader.GetOrdinal("maxValues");
int ordMeanValues = reader.GetOrdinal("meanValues");
int ordStddevValues = reader.GetOrdinal("stddevValues");
int ordRecordCount = reader.GetOrdinal("recordCount");
int ordTempCount = reader.GetOrdinal("tempCount");
int ordPressCount = reader.GetOrdinal("pressCount");
int ordHumCount = reader.GetOrdinal("humCount");
int ordSpeedCount = reader.GetOrdinal("speedCount");
int ordDirCount = reader.GetOrdinal("dirCount");
byte[] values = new byte[8];
//return ReadAsSensorReadings(reader);
while (reader.Read())
{
reader.GetBytes(ordMinValues, 0, values, 0, values.Length);
PackedReadingValues minValues = PackedReadingValues.ConvertFromPackedBytes(values);
reader.GetBytes(ordMaxValues, 0, values, 0, values.Length);
PackedReadingValues maxValues = PackedReadingValues.ConvertFromPackedBytes(values);
reader.GetBytes(ordMeanValues, 0, values, 0, values.Length);
PackedReadingValues meanValues = PackedReadingValues.ConvertFromPackedBytes(values);
reader.GetBytes(ordStddevValues, 0, values, 0, values.Length);
PackedReadingValues stddevValues = PackedReadingValues.ConvertFromPackedBytes(values);
var summary = new T {
BeginStamp = UnitUtility.ConvertFromPosixTime(reader.GetInt32(ordStamp)),
Min = minValues,
Max = maxValues,
Mean = meanValues,
SampleStandardDeviation = stddevValues,
Count = reader.GetInt32(ordRecordCount),
TemperatureCounts =
PackedReadingValues.PackedCountsToHashUnsigned16(reader.GetValue(ordTempCount) as byte[]),
PressureCounts =
PackedReadingValues.PackedCountsToHashUnsigned16(reader.GetValue(ordPressCount) as byte[]),
HumidityCounts =
PackedReadingValues.PackedCountsToHashUnsigned16(reader.GetValue(ordHumCount) as byte[]),
WindSpeedCounts =
PackedReadingValues.PackedCountsToHashUnsigned16(reader.GetValue(ordSpeedCount) as byte[]),
WindDirectionCounts =
PackedReadingValues.PackedCountsToHashUnsigned16(reader.GetValue(ordDirCount) as byte[])
};
yield return(summary);
}
}
}
}
private bool PushSummaries(int sensorId, string tableName, IEnumerable <ReadingsSummary> summaries, PosixTimeRange totalRange)
{
string insertCommandText = String.Format(
"INSERT INTO {0} (sensorId,stamp,minValues,maxValues,meanValues,stddevValues,recordCount,tempCount,pressCount,humCount,speedCount,dirCount)"
+ " VALUES (@sensorId,@stamp,@minValues,@maxValues,@meanValues,@stddevValues,@recordCount,@tempCount,@pressCount,@humCount,@speedCount,@dirCount)",
tableName
);
string deleteCommandText = String.Format(
"DELETE FROM {0} WHERE stamp >= @minStamp AND stamp <= @maxStamp AND sensorId = @sensorId",
tableName
);
using (var command = _connection.CreateTextCommand(deleteCommandText)) {
command.Transaction = _connection.BeginTransaction();
var sensorIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
if (totalRange.Span != 0)
{
command.AddParameter("minStamp", DbType.Int32, totalRange.Low);
command.AddParameter("maxStamp", DbType.Int32, totalRange.High);
command.ExecuteNonQuery();
}
command.CommandText = insertCommandText;
var stampParam = command.AddParameter("stamp", DbType.Int32, null);
var minValuesParam = command.AddParameter("minValues", DbType.Binary, null);
minValuesParam.Size = 8;
var maxValuesParam = command.AddParameter("maxValues", DbType.Binary, null);
maxValuesParam.Size = 8;
var meanValuesParam = command.AddParameter("meanValues", DbType.Binary, null);
meanValuesParam.Size = 8;
var stddevValuesParam = command.AddParameter("stddevValues", DbType.Binary, null);
stddevValuesParam.Size = 8;
var recordCountParam = command.AddParameter("recordCount", DbType.Int32, null);
recordCountParam.ParameterName = "recordCount";
var tempCountParam = command.AddParameter("tempCount", DbType.Binary, null);
tempCountParam.Size = 0;
var pressCountParam = command.AddParameter("pressCount", DbType.Binary, null);
pressCountParam.Size = 0;
var humCountParam = command.AddParameter("humCount", DbType.Binary, null);
humCountParam.Size = 0;
var speedCountParam = command.AddParameter("speedCount", DbType.Binary, null);
speedCountParam.Size = 0;
var dirCountParam = command.AddParameter("dirCount", DbType.Binary, null);
dirCountParam.Size = 0;
int counter = 0;
foreach (var summary in summaries)
{
stampParam.Value = UnitUtility.ConvertToPosixTime(summary.BeginStamp);
minValuesParam.Value = PackedReadingValues.ConvertToPackedBytes(summary.Min);
maxValuesParam.Value = PackedReadingValues.ConvertToPackedBytes(summary.Max);
meanValuesParam.Value = PackedReadingValues.ConvertToPackedBytes(summary.Mean);
stddevValuesParam.Value = PackedReadingValues.ConvertToPackedBytes(summary.SampleStandardDeviation);
recordCountParam.Value = summary.Count;
byte[] data;
tempCountParam.Value =
data = PackedReadingValues.ConvertTemperatureCountsToPackedBytes(summary.TemperatureCounts);
tempCountParam.Size = data.Length;
pressCountParam.Value = data = PackedReadingValues.ConvertPressureCountsToPackedBytes(summary.PressureCounts);
pressCountParam.Size = data.Length;
humCountParam.Value = data = PackedReadingValues.ConvertHumidityCountsToPackedBytes(summary.HumidityCounts);
humCountParam.Size = data.Length;
speedCountParam.Value = data = PackedReadingValues.ConvertWindSpeedCountsToPackedBytes(summary.WindSpeedCounts);
speedCountParam.Size = data.Length;
dirCountParam.Value =
data = PackedReadingValues.ConvertWindDirectionCountsToPackedBytes(summary.WindDirectionCounts);
dirCountParam.Size = data.Length;
command.ExecuteNonQuery();
counter++;
if (counter >= RecordBatchQuantity)
{
counter = 0;
command.Transaction.Commit();
command.Transaction = _connection.BeginTransaction();
}
}
command.Transaction.Commit();
}
return(true);
}
public bool PushSyncStamp(DateTime stamp)
{
return(PushSyncStamp(UnitUtility.ConvertToPosixTime(stamp)));
}