protected virtual TableSchema GetSchema(string table)
{
var columns = new List <TableSchema.ColumnDefinition>();
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", table)))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var def = new TableSchema.ColumnDefinition
{
Name = reader["COLUMN_NAME"].ToString(),
Type = reader["DATA_TYPE"].ToString(),
IsNullable =
(string.Compare(reader["IS_NULLABLE"].ToString(), "yes", true) == 0)
};
columns.Add(def);
}
}
}
return(new TableSchema
{
Columns = columns.AsReadOnly()
});
}
protected virtual bool AddColumnIfMissing(string column, string datatype, bool nullable)
{
if (GetSchema("AgentData").HasColumn(column))
{
return(false);
}
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("ALTER TABLE [{3}].[AgentData] ADD {0} {1} {2} NULL",
column,
datatype,
nullable ? string.Empty : "NOT",
_config.SchemaName)))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tAdded column {0} [{1}]", column, datatype);
}
return(true);
}
public override void Initialise()
{
try
{
Logger.Debug("\tCreating {0} schema...", _config.SchemaName);
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{0}') SELECT CAST(1 as bit) ELSE Select CAST(0 as bit)", _config.SchemaName)))
{
bool exists = (bool)cmd.ExecuteScalar();
if (exists)
{
using (var createCmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("CREATE SCHEMA {0}", _config.SchemaName)))
{
createCmd.ExecuteNonQuery();
Logger.Debug("\tDone");
}
}
}
Logger.Debug("\tCreating AgentData table...");
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[AgentData]') AND type in (N'U')) BEGIN", _config.SchemaName)
.Append("CREATE TABLE [{0}].[AgentData](", _config.SchemaName)
.Append("[TypeId] [uniqueidentifier] NOT NULL,")
.Append("[EventType] [varchar](20) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[SiteId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[AgentId] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,")
.Append("[CheckId] [varchar](50) COLLATE Latin1_General_CI_AS NULL,")
.Append("[Result] [bit] NULL,")
.Append("[GeneratedOnUtc] [datetime] NOT NULL,")
.Append("[ReceivedOnUtc] [datetime] NOT NULL,")
.Append("[Data] [xml] NOT NULL,")
.Append("[Version] [uniqueidentifier] NOT NULL) END")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tDone");
}
Logger.Debug("\tApplying schema updates (ResultCount) to AgentData table...");
using (var cmd = SqlServerAdhocCommand.UsingSmartConnection(_config.ConnectionString)
.WithSql(SqlServerStatement.Create("IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount') BEGIN")
.Append("ALTER TABLE {0}.AgentData ADD ResultCount DECIMAL(20,4) NULL", _config.SchemaName)
.Append("END ELSE BEGIN")
.Append("IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AgentData' AND COLUMN_NAME = 'ResultCount' AND DATA_TYPE = 'bigint') BEGIN")
.Append("ALTER TABLE {0}.AgentData ALTER COLUMN ResultCount DECIMAL(20,4)", _config.SchemaName)
.Append("END")
.Append("END")))
{
cmd.ExecuteNonQuery();
Logger.Debug("\tDone");
}
Logger.Debug("\tApplying schema updates to AgentData table...");
AddColumnIfMissing("Tags", "VARCHAR(200)", true);
AddColumnIfMissing("MinuteBucket", "INT", true);
AddColumnIfMissing("HourBucket", "INT", true);
AddColumnIfMissing("DayBucket", "INT", true);
// Geo - point
AddColumnIfMissing("Latitude", "VARCHAR(12)", true);
AddColumnIfMissing("Longitude", "VARCHAR(12)", true);
Logger.Debug("\tSuccess, AgentData table established");
}
catch (Exception)
{
Logger.Debug("\tFailed to create AgentData table");
throw;
}
}