private int OffsetRecords(int sensorId, PosixTimeRange range, int offset)
{
int c = 0;
if (0 == offset)
{
return(0);
}
if (!ForceConnectionOpen())
{
throw new Exception("Could not open database.");
}
const string delLowCommandText = "DELETE FROM Record WHERE stamp >= @minDelRangeInclusive AND stamp < @maxDelRangeExclusive";
const string delHighCommandText = "DELETE FROM Record WHERE stamp > @minDelRangeExclusive AND stamp <= @maxDelRangeInclusive";
const string shiftCommandText = "UPDATE Record SET stamp = stamp + @offset WHERE sensorId = @sensorId AND stamp = @stamp";
using (var command = _connection.CreateTextCommand(String.Empty)) {
command.Transaction = _connection.BeginTransaction();
if (offset < 0)
{
command.CommandText = delLowCommandText;
var minDelRangeInclusive = command.AddParameter("minDelRangeInclusive", DbType.Int32, range.Low.Value + offset);
var maxDelRangeExclusive = command.AddParameter("maxDelRangeExclusive", DbType.Int32, range.Low);
var deletedCount = command.ExecuteNonQuery();
command.CommandText = shiftCommandText;
var sensorIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
var stampParam = command.AddParameter("stamp", DbType.Int32, 0);
var offsetParam = command.AddParameter("offset", DbType.Int32, offset);
for (int i = range.Low; i <= range.High; i++)
{
stampParam.Value = i;
c += command.ExecuteNonQuery();
}
}
else
{
command.CommandText = delHighCommandText;
var minDelRangeExclusive = command.AddParameter("minDelRangeExclusive", DbType.Int32, range.High);
var maxDelRangeInclusive = command.AddParameter("maxDelRangeInclusive", DbType.Int32, range.High.Value + offset);
var deletedCount = command.ExecuteNonQuery();
command.CommandText = shiftCommandText;
var sensorIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
var stampParam = command.AddParameter("stamp", DbType.Int32, 0);
var offsetParam = command.AddParameter("offset", DbType.Int32, offset);
for (int i = range.High; i >= range.Low; i--)
{
stampParam.Value = i;
c += command.ExecuteNonQuery();
}
}
command.Transaction.Commit();
}
return(c);
}
private bool ShrinkRecords(int sensorId, PosixTimeRange range, int delta)
{
if (delta > 0)
{
return(false);
}
if (0 == delta)
{
return(true);
}
int totalDeletions = -delta;
int rangeSpan = range.Span;
if (rangeSpan < totalDeletions)
{
return(false);
}
using (var command = _connection.CreateTextCommand(
"UPDATE OR REPLACE Record SET stamp = stamp - @offset WHERE sensorId = @sensorId AND stamp = @stamp")) {
command.Transaction = _connection.BeginTransaction();
var sensorIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
var stampParam = command.AddParameter("stamp", DbType.Int32, 0);
var offsetParam = command.AddParameter("offset", DbType.Int32, 0);
for (int delNumber = 0; delNumber < totalDeletions; delNumber++)
{
// scoot it down
int delIndex = (rangeSpan * (delNumber + 1)) / ((rangeSpan - totalDeletions) + 1);
int nextIndex = (rangeSpan * (delNumber + 2)) / ((rangeSpan - totalDeletions) + 1);
nextIndex = Math.Min(nextIndex, range.High);
offsetParam.Value = delNumber + 1;
for (int i = delIndex; i < nextIndex; i++)
{
stampParam.Value = i;
command.ExecuteNonQuery();
}
}
command.CommandText = "DELETE FROM Record WHERE sensorId = @sensorId AND stamp > @minStamp AND stamp <= @maxStamp";
command.Parameters.Clear();
command.Parameters.Add(sensorIdParam);
var minStampParam = command.AddParameter("minStamp", DbType.Int32, range.High - totalDeletions);
var maxStampParam = command.AddParameter("maxStamp", DbType.Int32, range.High);
command.ExecuteNonQuery();
command.Transaction.Commit();
}
return(true);
}
private bool UpdateSummaryRecords(PosixTimeRange rangeCovered, string sensorName, int sensorId)
{
return(UpdateSummaryRecords(
new TimeRange(rangeCovered.Low.AsDateTime, rangeCovered.High.AsDateTime),
sensorName, sensorId));
}
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);
}
private bool ExpandRecords(int sensorId, PosixTimeRange range, int delta)
{
if (delta < 0)
{
return(false);
}
if (delta == 0)
{
return(true);
}
int totalInserts = delta;
int rangeSpan = range.Span;
int resultingSpan = rangeSpan + delta;
if (totalInserts >= resultingSpan)
{
return(false);
}
int splitParts = Math.Min(rangeSpan, delta + 1);
const string moveCommandText =
"UPDATE OR REPLACE Record SET stamp = (stamp + @offset) WHERE sensorId = @sensorId AND stamp = @stamp";
const string delOverflowCommand =
"DELETE FROM Record WHERE stamp > @minRange AND stamp < @maxRange";
using (var command = _connection.CreateTextCommand(String.Empty)) {
command.Transaction = _connection.BeginTransaction();
command.CommandText = delOverflowCommand;
command.AddParameter("minRange", DbType.Int32, range.High);
command.AddParameter("maxRange", DbType.Int32, range.High.Value + delta);
command.ExecuteNonQuery();
var sensorIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
var offsetParam = command.AddParameter("offset", DbType.Int32, 0);
var stampParam = command.AddParameter("stamp", DbType.Int32, 0);
command.CommandText = moveCommandText;
for (int partIndex = splitParts - 1; partIndex >= 0; partIndex--)
{
int curPartIndex = ((partIndex * rangeSpan) / splitParts);
int nextPartIndex = Math.Min(((partIndex + 1) * rangeSpan) / splitParts, rangeSpan);
int destIndex = (curPartIndex * (resultingSpan - 1)) / (rangeSpan - 1);
int shift = (destIndex + range.Low) - (curPartIndex + range.Low);
// move all between ["curPartIndex","nextPartIndex") up by "shift"
//minStampParam.Value = curPartIndex;
//maxStampParam.Value = nextPartIndex;
offsetParam.Value = shift;
for (int i = nextPartIndex + range.Low - 1; i >= curPartIndex + range.Low; i--)
{
stampParam.Value = i;
command.ExecuteNonQuery();
}
}
command.Transaction.Commit();
}
return(true);
}
public bool AdjustTimeStamps(
string sensorName,
PosixTimeRange currentRange,
PosixTimeRange correctedRange,
bool canOverwrite
)
{
int delta = correctedRange.Span - currentRange.Span;
int offset = correctedRange.Low - currentRange.Low;
if (delta == 0 && offset == 0)
{
return(false);
}
if (!ForceConnectionOpen())
{
throw new Exception("Could not open database.");
}
int sensorId = -1;
using (var command = _connection.CreateTextCommand(
"SELECT sensorID FROM Sensor WHERE nameKey = @nameKey")) {
command.AddParameter("nameKey", DbType.String, sensorName);
using (var reader = command.ExecuteReader()) {
if (reader.Read())
{
sensorId = reader.GetInt32(0);
}
}
}
if (sensorId < 0)
{
throw new KeyNotFoundException("Sensor not found in datastore.");
}
if (
!canOverwrite &&
(correctedRange.Low < currentRange.Low || correctedRange.High > currentRange.High)
)
{
using (var command = _connection.CreateTextCommand("SELECT COUNT(*) FROM Record WHERE sensorId = @sensorId")) {
//var nameIdParam = command.AddParameter("sensorId", DbType.Int32, sensorId);
var orJoin = new List <string>();
if (correctedRange.Low < currentRange.Low)
{
orJoin.Add("(stamp >= @corLow AND stamp < @curLow)");
}
//var corLowParam = command.AddParameter("corLow", DbType.Int32, correctedRange.Low);
//var curLowParam = command.AddParameter("curLow", DbType.Int32, currentRange.Low);
if (correctedRange.High > currentRange.High)
{
orJoin.Add("(stamp <= @corHigh AND stamp > @curHigh)");
}
//var corHighParam = command.AddParameter("corHigh", DbType.Int32, correctedRange.High);
//var curHighParam = command.AddParameter("curHigh", DbType.Int32, currentRange.High);
command.CommandText += " AND (" +
String.Join(" OR ", orJoin.ToArray()) +
") AND NOT (stamp >= @curLow AND stamp <= @curHigh)";
int count = 0;
using (var reader = command.ExecuteReader()) {
if (reader.Read())
{
count = reader.GetInt32(0);
}
}
if (count > 0)
{
throw new InvalidOperationException("Cannot overwrite existing datastore entries.");
}
}
}
// at this point there is space to shift or grow into
bool ok = false;
if (delta < 0)
{
// shrinking
ok = ShrinkRecords(sensorId, currentRange, delta);
// then shift all if needed
if (offset != 0)
{
ok = (0 != OffsetRecords(sensorId, new PosixTimeRange(currentRange.Low, currentRange.Low + correctedRange.Span), offset))
| ok;
}
}
else
{
// need to shift the start into place
if (offset != 0)
{
ok = 0 != OffsetRecords(sensorId, currentRange, offset);
}
// now we need to stretch the records
if (delta > 0)
{
//ok |= InterpolateInsertRecords(sensorId, new PosixTimeRange(currentRange.High - offset + 1, correctedRange.High), correctedRange);
ok |= ExpandRecords(sensorId, new PosixTimeRange(currentRange.Low + offset, currentRange.High + offset), delta);
}
}
bool okUpdateCurrent, okUpdateCorrected;
if (currentRange.Intersects(correctedRange))
{
var completeRange = new PosixTimeRange(
new PosixTime(Math.Min(currentRange.Low, correctedRange.Low)),
new PosixTime(Math.Max(currentRange.High, correctedRange.High))
);
okUpdateCurrent = okUpdateCorrected =
UpdateSummaryRecords(completeRange, sensorName, sensorId);
}
else
{
okUpdateCurrent = UpdateSummaryRecords(currentRange, sensorName, sensorId);
okUpdateCorrected = UpdateSummaryRecords(correctedRange, sensorName, sensorId);
}
return(ok && okUpdateCurrent && okUpdateCorrected);
}