public List <MetricMeasurement> UpdateMetrics(Dictionary <string, string> deviceVars)
{
SQLiteConnection connection;
List <MetricMeasurement> metricMeasurements = new List <MetricMeasurement>();
using (connection = this.CreateConnection())
{
foreach (KeyValuePair <string, string> deviceVar in deviceVars)
{
if (!TryGetVariableMetric(deviceVar.Key, out VariableMetric metric))
{
continue;
}
if (!metric.Initialized)
{
InitializeMetric(connection, metric);
}
MetricMeasurement metricMeasurement = new MetricMeasurement()
{
VariableName = metric.VariableName,
Timestamp = DateTime.UtcNow,
Value = double.Parse(deviceVar.Value)
};
InsertMetricRaw(connection, metric, metricMeasurement);
InsertMetric1Minute(connection, metric, metricMeasurement);
metricMeasurements.Add(metricMeasurement);
}
connection.Close();
}
return(metricMeasurements);
}
private static void InsertMetricRaw(
SQLiteConnection connection,
VariableMetric metric,
MetricMeasurement metricMeasurement)
{
if (!metric.Initialized)
{
throw new Exception("Metric is not initialized!");
}
SQLiteCommand cmd;
using (cmd = new SQLiteCommand(connection))
{
cmd.CommandText = $"INSERT INTO [{metric.TableName}_raw] (timestamp, value) VALUES (@timestamp, @value)";
cmd.Parameters.AddWithValue("@timestamp", metricMeasurement.Timestamp.ToEpochSeconds());
cmd.Parameters.AddWithValue("@value", metricMeasurement.Value);
cmd.ExecuteNonQuery();
}
}
private static void InsertMetric1Minute(
SQLiteConnection connection,
VariableMetric metric,
MetricMeasurement metricMeasurement)
{
if (!metric.Initialized)
{
throw new Exception("Metric is not initialized!");
}
var timestamp = new DateTime(
metricMeasurement.Timestamp.Year,
metricMeasurement.Timestamp.Month,
metricMeasurement.Timestamp.Day,
metricMeasurement.Timestamp.Hour,
metricMeasurement.Timestamp.Minute,
0);
double?newValue = null;
int valueCount = 0;
using (var cmd = new SQLiteCommand(connection))
{
cmd.CommandText = $"SELECT * FROM [{metric.TableName}_1m] WHERE [timestamp] = @ts";
cmd.Parameters.AddWithValue("@ts", timestamp.ToEpochSeconds());
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (string.IsNullOrWhiteSpace(metric.TableName))
{
throw new Exception("TableName read from metric row is empty");
}
double value = (double)reader["value"];
valueCount = (int)reader["count"];
newValue = ((value * valueCount) + metricMeasurement.Value) / (valueCount + 1);
break;
}
}
}
if (newValue != null)
{
using (var cmd = new SQLiteCommand(connection))
{
cmd.CommandText = $"UPDATE [{metric.TableName}_1m] SET [value] = @value, [count] = @count WHERE [timestamp] = @ts";
cmd.Parameters.AddWithValue("@value", newValue.Value);
cmd.Parameters.AddWithValue("@count", valueCount + 1);
cmd.Parameters.AddWithValue("@ts", timestamp.ToEpochSeconds());
cmd.ExecuteNonQuery();
}
return;
}
using (var cmd = new SQLiteCommand(connection))
{
cmd.CommandText = $"INSERT INTO [{metric.TableName}_1m] (timestamp, value, count) VALUES (@ts, @value, @count)";
cmd.Parameters.AddWithValue("@ts", timestamp.ToEpochSeconds());
cmd.Parameters.AddWithValue("@value", metricMeasurement.Value);
cmd.Parameters.AddWithValue("@count", 1);
cmd.ExecuteNonQuery();
}
}
