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 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);
}
}
}
}