private static void HandleGroups(List <Group> groups, SQLiteConnection conn)
{
foreach (Group g in groups)
{
string sql = string.Format("SELECT GroupID, Name FROM DeviceGroups WHERE GroupID = {0}", g.id);
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader.GetString(1) != g.name)
{
Updater updater = new Updater("DeviceGroups", string.Format("GroupID = {0}", g.id), conn);
updater.Set("Name", g.name.Trim(), true);
updater.Execute();
}
}
else
{
Inserter insert = new Inserter("DeviceGroups", conn);
insert.Set("GroupID", g.id);
insert.Set("Name", g.name.Trim(), true);
insert.Execute();
}
}
}
}
public void Set(string path, string value, SQLiteConnection conn)
{
ILog log = LogManager.GetLogger(typeof(Attribute));
try
{
log.DebugFormat("Setting attribute '{0}' to '{1}'", path, value);
using (SQLiteTransaction t = conn.BeginTransaction())
{
Changer change = new Deleter("Attributes", $"Path = '{path}'", conn);
change.Execute();
change = new Inserter("Attributes", conn);
change.Set("Path", path, true);
change.Set("Value", value, false);
change.Execute();
t.Commit();
}
}
catch (Exception ex)
{
log.Error(ex);
}
}
private void UpdateMostRecentData(long data_id, SQLiteConnection conn)
{
Changer change = new Deleter("MostRecentDataPerCollector", $"CollectorID = {DCContext.ID.ID}", conn);
change.Execute();
change = new Inserter("MostRecentDataPerCollector", conn);
change.Set("CollectorID", DCContext.ID.ID);
change.Set("DataID", data_id);
change.Execute();
}
public long InsertData(SQLiteConnection conn)
{
Inserter insert = new Inserter("Data", conn);
insert.Set("CollectorID", DCContext.ID.ID);
insert.Set("Value", Value, false);
insert.Set("TimeStamp", Timestamp);
insert.Execute();
UpdateMostRecentData(conn.LastInsertRowId, conn);
return(conn.LastInsertRowId);
}
/// <summary>
/// Used when a new device is being added to the system
/// </summary>
/// <param name="di">The device being added</param>
/// <param name="conn">The DB connection to use</param>
private static void AddDevice(DeviceInfo di, DateTimeOffset timestamp, SQLiteConnection conn)
{
Inserter inserter = new Inserter("Devices", conn);
if (di.id >= 0)
{
inserter.Set("DeviceID", di.id);
}
inserter.Set("Name", di.name, true);
inserter.Set("Type", (int)di.type);
inserter.Set("IPAddress", di.ipAddress, true);
inserter.Set("Username", di.username, true);
SimpleEncryptDecrypt sed = new SimpleEncryptDecrypt();
inserter.Set("Password", sed.Encrypt(di.password), true);
inserter.Set("DateActivated", timestamp);
if (di.groupID < 0)
{
inserter.SetNull("GroupID");
}
else
{
inserter.Set("GroupID", di.groupID);
}
inserter.Execute();
long device_id = conn.LastInsertRowId;
Database.AddCollectors(di, device_id, timestamp, conn);
}
private static void Insert(SQLiteConnection conn, int count, Dictionary <int, string> dict)
{
Random r = new Random();
for (int i = 0; i < count; ++i)
{
int a = r.Next();
string b = Guid.NewGuid().ToString();
Inserter inserter = new Inserter("Temp", conn);
inserter.Set("A", a);
inserter.Set("B", b, false);
inserter.Execute();
dict[a] = b;
}
}
/// <summary>
/// Used to make sure the *Type tables are updated
/// </summary>
/// <param name="conn">The connection to the database</param>
/// <param name="values">The key/value pair that needs to exist in the DB. If there's a missing entry, or the description
/// changed, we'll insert or update as appropriate</param>
/// <param name="table">The table we're going to be updating</param>
/// <param name="type_column">The name of the type column.</param>
public static void PopulateTypesTable(this SQLiteConnection conn, Dictionary <int, string> values, string table, string type_column)
{
ILog log = LogManager.GetLogger(typeof(Extensions));
Dictionary <int, string> db_values = new Dictionary <int, string>();
string select_query = string.Format("SELECT {0}, Description FROM {1};", type_column, table);
using (SQLiteCommand command = new SQLiteCommand(select_query, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
db_values[reader.GetInt32(0)] = reader.GetString(1);
}
}
List <int> db_types = new List <int>(db_values.Keys);
List <int> types = new List <int>(values.Keys);
db_types.ForEach(t => types.Remove(t));
// Whatever's left needs to be added
foreach (int type in types)
{
log.Info($"Inserting {type}=>{values[type]} into {table}");
Inserter inserter = new Inserter(table, conn);
inserter.Set(type_column, type);
inserter.Set("Description", values[type], false);
inserter.Execute();
db_values[type] = values[type];
}
// Now compare the existing descriptions with the descriptions in values
foreach (int type in values.Keys)
{
if (values[type] != db_values[type])
{
log.Info($"Changing {type}=>{db_values[type]} to {values[type]} in {table}");
Updater updater = new Updater(table, $"{type_column} = {type}", conn);
updater.Set("Description", values[type], false);
updater.Execute();
}
}
}
public void ProperlyHandleLongs()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B INTEGER NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Dictionary <int, long> dict = new Dictionary <int, long>();
Random r = new Random();
int count = 100;
for (int i = 0; i < count; ++i)
{
int a = r.Next();
long b = r.NextLong();
Inserter ins = new Inserter("Temp", conn);
ins.Set("A", a);
ins.Set("B", b);
ins.Execute();
dict[a] = b;
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
long b = reader.GetInt64(0);
Assert.Equal(dict[a], b);
}
}
}
}
}
}
public void ProperlyHandleDates()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Dictionary <int, DateTimeOffset> dict = new Dictionary <int, DateTimeOffset>();
Random r = new Random();
int count = 100;
for (int i = 0; i < count; ++i)
{
int a = r.Next();
DateTimeOffset b = new DateTimeOffset(2018, r.Next(1, 12), r.Next(1, 28), r.Next(0, 23), r.Next(0, 59), r.Next(0, 59), r.Next(0, 999), TimeSpan.FromHours(r.Next(-12, 12)));
Inserter ins = new Inserter("Temp", conn);
ins.Set("A", a);
ins.Set("B", b);
ins.Execute();
dict[a] = b;
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
DateTimeOffset b = DateTimeOffset.Parse(reader.GetString(0));
Assert.Equal(dict[a], b);
}
}
}
}
}
}
public static void SetValue(string path, string value, bool normalize, DateTimeOffset timestamp, SQLiteConnection conn)
{
if (string.IsNullOrEmpty(path))
{
return;
}
string existing_value = GetValue(path, conn);
if (value != existing_value)
{
Clear(path, conn);
Inserter insert = new Inserter("Configuration", conn);
insert.Set("Path", path, true);
insert.Set("Value", value ?? string.Empty, normalize);
insert.Set("DateAdded", timestamp);
insert.Set("IsValid", 1);
insert.Execute();
}
}
public void NotBeNullable()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery("CREATE TABLE Temp(A INTEGER NOT NULL PRIMARY KEY, B TEXT NOT NULL)");
Assert.True(conn.DoesTableExist("Temp"));
Inserter i = new Inserter("Temp", conn);
i.Set("A", 1);
i.SetNull("B");
Assert.Throws <SQLiteException>(() => i.Execute());
}
}
}
/// <summary>
/// Updates a device's status. If the new status is null, the existing status should be invalidated and no new
/// entry added. If the new status is non-null, see it the existing status matches the new status. If the new
/// and old status are identical, do nothing. If the new and old status are different somehow (the status itself,
/// the is-alarm flag or the message are different), invalidate the old status and insert a new status.
///
/// A single device may have different statuses, so we need to restrict our decisions to a set of types, which
/// is included in the 3rd parameter. For example, the memory statuses are adequate, low, or critically low. Only
/// one of these should be 'active' at a given time.
/// </summary>
/// <param name="device_id">The ID of the device we're checking</param>
/// <param name="type">The new type. Can be null to indicate the status should be cleared</param>
/// <param name="statuses">The full set of statuses we should be considering. Will be a subset of all
/// the statuses a device can have.</param>
/// <param name="message">A message regarding the status</param>
/// <param name="conn">The DB connection to use</param>
protected static void SetDeviceStatus(long device_id, EStatusType?type, List <EStatusType> statuses, string message, SQLiteConnection conn)
{
if (type.HasValue)
{
Debug.Assert(statuses.Contains(type.Value));
}
EAlertLevel alert_level = (type.HasValue ? type.Value.GetAlertLevel() : null) ?? EAlertLevel.Normal;
string in_clause = statuses.Join();
string clause = $"DeviceID = {device_id} AND StatusType IN ({in_clause}) AND IsValid = 1";
string sql = $"SELECT StatusType, IsAlarm, Message FROM DeviceStatus WHERE {clause};";
bool insert = type.HasValue;
bool remove = type.HasValue == false;
if (type.HasValue)
{
// We may be inserting a new type. We need to see if the current value for the device/status is the
// same, or has changed. If it's the same we don't need to do anything. If it's changed, we'll want to
// mark the old value as invalid, and insert the new value.
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
EStatusType existing_type = (EStatusType)reader.GetInt32(0);
EAlertLevel existing_alert_level = (EAlertLevel)reader.GetInt32(1);
string existing_message = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
// An existing record exists, so insert the new one and update the old one if something's changed.
// If nothing changed, we'll leave the old one alone
bool something_changed =
(type.Value != existing_type) ||
(alert_level != existing_alert_level) ||
(string.Compare(message, existing_message) != 0);
// If something has changed, we'll want to remove the old version, and insert the new
// version. If nothing changed, we don't want to do either.
insert = remove = something_changed;
}
// If it wasn't found, just insert, which is the default
}
}
// In this case there's no status value, so that means were clearing out the old one, if it exists.
if (insert || remove)
{
if (remove)
{
Updater updater = new Updater("DeviceStatus", clause, conn);
updater.Set("IsValid", 0);
updater.Execute();
}
if (insert)
{
Inserter inserter = new Inserter("DeviceStatus", conn);
inserter.Set("DeviceID", device_id);
inserter.Set("StatusType", (int)type);
inserter.Set("IsAlarm", (int)alert_level);
inserter.Set("Date", DateTimeOffset.Now);
inserter.Set("Message", message, false);
inserter.Set("IsValid", 1);
inserter.Execute();
}
}
// else, no change
}
public void InsertNullOK()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery(CreateTable);
Assert.True(conn.DoesTableExist("Temp"));
int count = 100;
Random r = new Random();
Dictionary <int, string> dict = new Dictionary <int, string>();
for (int i = 0; i < count; ++i)
{
Inserter inserter = new Inserter("Temp", conn);
int a = r.Next();
inserter.Set("A", a);
// Make every other one null
if (i % 2 == 0)
{
string b = Guid.NewGuid().ToString();
inserter.Set("B", b, false);
dict[a] = b;
}
else
{
inserter.SetNull("B");
dict[a] = null;
}
inserter.Execute();
}
foreach (int a in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {a}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.True(read);
if (read)
{
if (dict[a] != null)
{
Assert.False(reader.IsDBNull(0));
string b = reader.GetString(0);
Assert.Equal(dict[a], b);
}
else
{
Assert.True(reader.IsDBNull(0));
Assert.Throws <InvalidCastException>(() => reader.GetString(0));
}
}
}
}
}
}
}
public void Interpret(Data d, SQLiteConnection conn)
{
if (d.Type != ECollectorType.Ping)
{
return;
}
if (d is ListData <PingResult> )
{
ListData <PingResult> data = d as ListData <PingResult>;
Dictionary <string, string> ip_to_name_map = new Dictionary <string, string>();
string sql = "SELECT IPAddress, Name FROM Devices WHERE DateDisabled IS NULL;";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.IsDBNull(0) == false)
{
ip_to_name_map[reader.GetString(0)] = reader.GetString(1);
}
}
}
foreach (PingResult result in data.Data)
{
try
{
string ip = result.Address.ToString();
string name = ip;
if (ip_to_name_map.ContainsKey(ip))
{
name = ip_to_name_map[ip];
}
sql = $"SELECT IPAddress FROM NetworkStatus WHERE IPAddress = '{ip}';";
Changer changer = null;
bool existing = false;
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
// It already exists--update the status and name in case it's been changed
existing = true;
changer = new Updater("NetworkStatus", $"IPAddress = '{ip}'", conn);
}
else
{
changer = new Inserter("NetworkStatus", conn);
changer.Set("IPAddress", ip, false);
}
if (changer != null)
{
changer.Set("Name", name, false);
changer.Set("SuccessfulPing", result.IsPingable ? 1 : 0);
changer.Set("DatePingAttempted", data.CollectedAt);
if (result.IsPingable)
{
changer.Set("DateSuccessfulPingOccurred", data.CollectedAt);
}
else if (!existing)
{
// It's new, and wasn't pingable, so we need to report that. We'll do that by
// having an empty date/time
changer.Set("DateSuccessfulPingOccurred", "", false, false);
}
// else it exists, but isn't pingable, so leave the DateSuccessfulPingOccurred alone
}
}
if (changer != null)
{
changer.Execute();
}
}
catch (Exception e)
{
ApplicationEventLog log = new ApplicationEventLog();
log.LogError($"PingInterpreter -- {result.Address.ToString()}");
log.Log(e);
}
}
}
else
{
throw new Exception("PingInterpreter: data type is wrong");
}
}