protected virtual TableSchema GetSchema(string table)
{
var columns = new List <TableSchema.ColumnDefinition>();
using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("PRAGMA table_info('{0}')", table)))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var def = new TableSchema.ColumnDefinition
{
Name = reader["name"].ToString(),
Type = reader["type"].ToString(),
IsNullable = Convert.ToBoolean(reader["notnull"])
};
columns.Add(def);
}
}
}
return(new TableSchema
{
Columns = columns.AsReadOnly()
});
}
public void Publish(HealthCheckResult message)
{
var data = SerialisationHelper <HealthCheckResult> .DataContractSerialize(message);
using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("INSERT INTO AgentData (")
.Append("TypeId,EventType,SiteId,AgentId,CheckId,Result,ResultCount,GeneratedOnUtc,ReceivedOnUtc,Data,Tags,Version,MinuteBucket,HourBucket,DayBucket")
.Append(") VALUES (")
.InsertParameter("@pTypeId", message.Check.Identity.TypeId).Append(",")
.InsertParameter("@pEventType", message.EventType).Append(",")
.InsertParameter("@pSiteId", message.Agent.SiteId).Append(",")
.InsertParameter("@pAgentId", message.Agent.AgentId).Append(",")
.InsertParameter("@pCheckId", message.Check.Identity.Name).Append(",")
.InsertParameter("@pResult", message.Check.Result).Append(",")
.InsertParameter("@pResultCount", message.Check.ResultCount).Append(",")
.InsertParameter("@pGeneratedOnUtc", message.Check.GeneratedOnUtc).Append(",")
.InsertParameter("@pReceivedOnUtc", DateTime.UtcNow).Append(",")
.InsertParameter("@pData", data).Append(",")
.InsertParameter("@pTags", message.Check.Tags).Append(",")
.InsertParameter("@pVersion", message.Id).Append(",")
.InsertParameter("@pMinuteBucket", message.MinuteBucket).Append(",")
.InsertParameter("@pHourBucket", message.HourBucket).Append(",")
.InsertParameter("@pDayBucket", message.DayBucket)
.Append(")")))
{
cmd.ExecuteNonQuery();
}
}
public override void Initialise()
{
var dbFile = ExtractFilename(SmartConnectionString.For(myConfig.ConnectionString));
try
{
if (!File.Exists(dbFile))
{
Logger.Debug("\tCreating MonitorWang SQLite datafile at {0}...", dbFile);
SQLiteConnection.CreateFile(dbFile);
}
var schema = GetSchema("AgentData");
if (schema.Columns.Count == 0)
{
Logger.Debug("\tCreating AgentData table...");
using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("CREATE TABLE AgentData (")
.Append("[TypeId] UNIQUEIDENTIFIER NOT NULL,")
.Append("[EventType] TEXT NOT NULL,")
.Append("[SiteId] TEXT NOT NULL,")
.Append("[AgentId] TEXT NOT NULL,")
.Append("[CheckId] TEXT NULL,")
.Append("[Result] BOOL NULL,")
.Append("[GeneratedOnUtc] DATETIME NOT NULL,")
.Append("[ReceivedOnUtc] DATETIME NOT NULL,")
.Append("[Data] TEXT NOT NULL,")
.Append("[Tags] TEXT NULL,")
.Append("[Version] UNIQUEIDENTIFIER NOT NULL,")
.Append("[ResultCount] REAL NULL,")
.Append("[MinuteBucket] INTEGER NULL,")
.Append("[HourBucket] INTEGER NULL,")
.Append("[DayBucket] INTEGER NULL)")))
{
cmd.ExecuteNonQuery();
}
}
AddColumnIfMissing("MinuteBucket", "INTEGER", true);
AddColumnIfMissing("HourBucket", "INTEGER", true);
AddColumnIfMissing("DayBucket", "INTEGER", true);
Logger.Debug("\tSuccess, AgentData table established");
}
catch (Exception)
{
Logger.Debug("\tError during SQLite datafile/database creation...");
throw;
}
}
protected virtual bool AddColumnIfMissing(string column, string datatype, bool nullable)
{
if (GetSchema("AgentData").HasColumn(column))
{
return(false);
}
using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("ALTER TABLE AgentData ADD {0} {1} {2} NULL",
column,
datatype,
nullable ? string.Empty : "NOT")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tAdded column {0} [{1}]", column, datatype);
}
return(true);
}
public void Publish(HealthCheckAgentStart message)
{
var data = SerialisationHelper <HealthCheckAgentStart> .DataContractSerialize(message);
using (var cmd = SQLiteAdhocCommand.UsingSmartConnection(myConfig.ConnectionString)
.WithSql(SQLiteStatement.Create("INSERT INTO AgentData (")
.Append("TypeId,EventType,SiteId,AgentId,GeneratedOnUtc,ReceivedOnUtc,Data,Version")
.Append(") VALUES (")
.InsertParameter("@pTypeId", message.Id).Append(",")
.InsertParameter("@pEventType", "SessionStart").Append(",")
.InsertParameter("@pSiteId", message.Agent.SiteId).Append(",")
.InsertParameter("@pAgentId", message.Agent.AgentId).Append(",")
.InsertParameter("@pGeneratedOnUtc", message.DiscoveryStarted).Append(",")
.InsertParameter("@pReceivedOnUtc", DateTime.UtcNow).Append(",")
.InsertParameter("@pData", data).Append(",")
.InsertParameter("@pVersion", message.Id)
.Append(")")))
{
cmd.ExecuteNonQuery();
}
}