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);
}
}
public void CleanOldData(int days_to_keep, SQLiteConnection conn)
{
if (GlobalIsRunning.IsRunning == false)
{
return;
}
try
{
DateTimeOffset dt = DateTimeOffset.Now - TimeSpan.FromDays(days_to_keep);
string dt_as_8601 = dt.DayBeginAs8601();
//log.LogInformation("Deleting old data before " + dt_as_8601));
Stopwatch watch = Stopwatch.StartNew();
Deleter deleter = new Deleter("NetworkStatus", $"DatePingAttempted < '{dt_as_8601}'", conn);
deleter.Execute();
// To keep a single delete from taking too long, let's just do 100 at a time.
int count = 100;
string clause = $"DataID IN (SELECT DataID FROM Data WHERE TimeStamp < '{dt_as_8601}' ORDER BY TimeStamp ASC LIMIT {count})";
if (GlobalIsRunning.IsRunning == true)
{
deleter = new Deleter("MostRecentDataPerCollector", clause, conn);
deleter.Execute();
}
if (GlobalIsRunning.IsRunning == true)
{
deleter = new Deleter("Data", clause, conn);
deleter.Execute();
}
if (GlobalIsRunning.IsRunning == true)
{
// Don't delete from the DeviceStatus table if the status is still valid. Only ones
// that have been superceded should be deleted.
clause = $"Date < '{dt_as_8601}' AND IsValid = 0";
deleter = new Deleter("DeviceStatus", clause, conn);
deleter.Execute();
}
watch.Stop();
long elapsed = watch.ElapsedMilliseconds;
if (elapsed > 1000)
{
logging.EventLog elog = new ApplicationEventLog();
elog.LogInformation($"Finished deleting old data prior to {dt_as_8601}. It took {elapsed} ms");
}
}
catch (Exception ex)
{
ILog log = LogManager.GetLogger(typeof(Database));
log.Error("CleanOldData:");
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 void Clear(string path, SQLiteConnection conn)
{
ILog log = LogManager.GetLogger(typeof(Attribute));
try
{
log.DebugFormat("Clearing attribute '{0}'", path);
Changer change = new Deleter("Attributes", $"Path = '{path}'", conn);
change.Execute();
}
catch (Exception ex)
{
log.Error(ex);
}
}
/// <summary>
/// Used when a device is being removed from a syatem. Makes sure everything related to the device
/// is removed or marked as disabled.
/// </summary>
/// <param name="info">The device being removed</param>
/// <param name="conn">The DB connection to use</param>
private static void RemoveDevice(DeviceInfo info, SQLiteConnection conn)
{
// Don't allow the system or servers to be removed
if (info == null || info.type == EDeviceType.System || info.type == EDeviceType.Server)
{
return;
}
// And we're keying off of the device's DB-assigned ID, so if we don't have that we need to stop
if (info.id < 0)
{
return;
}
string where = string.Format("DeviceID = {0}", info.id);
Updater invalidate = new Updater("DeviceStatus", where, conn);
invalidate.Set("IsValid", 0);
invalidate.Execute();
Deleter deleter = new Deleter("NetworkStatus", string.Format("IPAddress = '{0}'", info.ipAddress), conn);
deleter.Execute();
Updater update = new Updater("Devices", where, conn);
update.Set("DateDisabled", DateTimeOffset.Now);
update.Execute();
// Make sure a device's database configuration information is also disabled
DatabaseType dt = new DatabaseType(info.name);
dt.Disable(conn);
DatabaseConnectionString dcs = new DatabaseConnectionString(info.name);
dcs.Disable(conn);
}
public static void Main()
{
var userName = ConfigurationManager.AppSettings.Get("userName");
var password = ConfigurationManager.AppSettings.Get("password");
var soapOrgServiceUri = ConfigurationManager.AppSettings.Get("soapOrgServiceUri");
var sqlConnectionString = ConfigurationManager.AppSettings.Get("sqlConnectionString");
using (var crmConnector = new CRMConnector(userName, password, soapOrgServiceUri))
{
crmConnector.Execute();
var crmConnectionState = (CRMConnectionState)crmConnector.GetConnectState();
if (crmConnectionState.IsConnect)
{
using (var sqlConnector = new SQLConnector(sqlConnectionString))
{
sqlConnector.Execute();
var sqlConnectionState = (SQLConnectionState)sqlConnector.GetConnectState();
if (sqlConnectionState.IsConnect)
{
var orgService = (IOrganizationService)crmConnectionState.Proxy;
var sqlConnection = sqlConnectionState.SqlConnection;
// Извлечение
var retriever = new Retriever(sqlConnection);
var allRetrieved = retriever.Execute();
// Обновление
var updater = new Updater(orgService, sqlConnection);
var allUpdated = updater.Execute(allRetrieved);
// Удаление
var deleter = new Deleter(orgService, sqlConnection);
deleter.Execute(allRetrieved);
}
}
}
}
}
public void DeleteOK()
{
using (FileDeleter fd = new FileDeleter(Extensions.GetTempDBFile()))
{
Dictionary <int, string> dict = new Dictionary <int, string>();
Database db = new Database(new Context(fd.Fi));
using (SQLiteConnection conn = db.Connection)
{
conn.Open();
conn.ExecuteNonQuery(CreateTable);
Assert.True(conn.DoesTableExist("Temp"));
Insert(conn, 100, dict);
Verify(conn, dict);
foreach (int i in dict.Keys)
{
Deleter d = new Deleter("Temp", $"A = {i}", conn);
d.Execute();
}
foreach (int i in dict.Keys)
{
string sql = $"SELECT B FROM Temp WHERE A = {i}";
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
bool read = reader.Read();
Assert.False(read);
}
}
}
}
}
private static void UpdateDevice(DeviceInfo device, DateTimeOffset timestamp, SQLiteConnection conn)
{
if (device.deleted)
{
RemoveDevice(device, conn);
}
else
{
// Grab the original device name
string original_device_name = string.Empty;
string original_ip_address = string.Empty;
bool do_insert = false;
string sql = string.Format("SELECT Name, IPAddress FROM Devices WHERE DeviceID = {0}", device.id);
using (SQLiteCommand command = new SQLiteCommand(sql, conn))
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader.IsDBNull(0) == false)
{
original_device_name = reader.GetString(0);
}
if (reader.IsDBNull(1) == false)
{
original_ip_address = reader.GetString(1);
// If the IP address hasn't changed, no need to remove it from the NetworkStatus
if (original_ip_address == device.ipAddress)
{
original_ip_address = string.Empty;
}
}
}
else
{
do_insert = true;
}
}
if (do_insert == false)
{
// Just update everything
SimpleEncryptDecrypt sed = new SimpleEncryptDecrypt();
Updater update = new Updater("Devices", string.Format("DeviceID = {0}", device.id), conn);
update.Set("Name", device.name, true);
update.Set("IPAddress", device.ipAddress, true);
update.Set("Username", device.username, true);
update.Set("Password", sed.Encrypt(device.password), false);
if (device.groupID < 0)
{
update.SetNull("GroupID");
}
else
{
update.Set("GroupID", device.groupID);
}
update.Execute();
Database.UpdateCollectors(device.id, device.name, original_device_name, device.collectors, timestamp, conn);
}
else
{
// The device didn't exist, so just add it.
AddDevice(device, timestamp, conn);
}
if (string.IsNullOrEmpty(original_device_name) == false)
{
// Make sure if the name changed that the NetworkStatus table is updated properly
Updater network_updater = new Updater("NetworkStatus", string.Format("Name = '{0}'", original_device_name), conn);
network_updater.Set("Name", device.name, false);
network_updater.Execute();
}
if (string.IsNullOrEmpty(original_ip_address) == false)
{
// In case the IP address was changed, delete the original IP address and let the new one fill it in.
Deleter deleter = new Deleter("NetworkStatus", string.Format("IPAddress = '{0}'", original_ip_address), conn);
deleter.Execute();
}
}
}
