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 override void Initialise()
{
var dbFile = ExtractFilename(SmartConnectionString.For(myConfig.ConnectionString));
try
{
if (!File.Exists(dbFile))
{
Logger.Debug("\tCreating Wolfpack 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);
// Geo - point
AddColumnIfMissing("Latitude", "TEXT", true);
AddColumnIfMissing("Longitude", "TEXT", 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);
}
