public bool Push <T>(string sensor, IEnumerable <T> readings, bool replace) where T : IReading
{
if (String.IsNullOrEmpty(sensor))
{
throw new ArgumentOutOfRangeException("sensor");
}
if (!ForceConnectionOpen())
{
throw new Exception("Could not open database.");
}
int?sensorRecordId = null;
using (var getSensorCmd = _connection.CreateTextCommand("SELECT sensorId FROM Sensor WHERE nameKey = '" + sensor + "'")) {
using (var getSensorReader = getSensorCmd.ExecuteReader()) {
if (getSensorReader.Read())
{
sensorRecordId = getSensorReader.GetInt32(getSensorReader.GetOrdinal("sensorId"));
}
}
}
if (!sensorRecordId.HasValue)
{
return(false);
}
TimeRange insertTimeRange = default(TimeRange);
// insert the initial values
using (var pushRecordCommand = _connection.CreateTextCommand(
"INSERT OR " + (replace ? "REPLACE" : "IGNORE") +
" INTO Record (sensorId,stamp,[values])" +
" VALUES (@sensorId,@stamp,@values)"
)) {
pushRecordCommand.AddParameter("sensorId", DbType.Int32, sensorRecordId.Value);
DbParameter stampParam = pushRecordCommand.AddParameter("stamp", DbType.Int32, null);
DbParameter valuesParam = pushRecordCommand.AddParameter("values", DbType.Binary, null);
insertTimeRange = typeof(T) == typeof(PackedReading)
? PushReadings(
pushRecordCommand, readings.Cast <PackedReading>(),
r => PackedReadingValues.ConvertToPackedBytes(r.Values),
stampParam, valuesParam
)
: PushReadings(
pushRecordCommand, readings.Cast <PackedReading>(),
r => PackedReadingValues.ConvertToPackedBytes(r),
stampParam, valuesParam
);
}
return(UpdateSummaryRecords(insertTimeRange, sensor, sensorRecordId.Value));
}
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);
}
