private string GetMaxValueMultiRowCumulative(int targetId, MetricGroup metricGroup, string metric, string[] keysToReturn)
{
object[] keys;
string maxValue = string.Empty;
if (!InMemoryCache.ContainsKey(InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Data)))
InMemoryCache.LoadDataIntoCache(targetId, metricGroup, false);
CacheTable dataCache = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Data)];
int id = dataCache.GetIdOfMaxValue(metric, metricGroup.metrics[metricGroup.GetMetricIdByName(metric)].type);
if (id == -1)
return maxValue;
if (!InMemoryCache.ContainsKey(InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Dictionary)))
InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, false);
CacheTable dictCache = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Dictionary)];
int keyId;
foreach (string keyName in keysToReturn)
{
keyId = metricGroup.GetKeyIdByName(keyName);
if (keyId != -1)
{
keys = dictCache[id];
if (keys == null || keys[keyId] == null)
maxValue += " / ";
else
maxValue += String.Format("{0} / ", keys[keyId]);
}
else
{
keyId = metricGroup.GetKeyAttributeIdByName(keyName);
keys = dictCache[id];
if (keys == null || keys[metricGroup.NumberOfMultiRowKeys + keyId] == null)
maxValue += " / ";
else
maxValue += String.Format("{0} / ", keys[metricGroup.NumberOfMultiRowKeys + keyId]);
}
}
maxValue = maxValue.Remove(maxValue.Length - 3);
return maxValue;
}
/// <summary> Creates in-memory cache table for storing latest data. Single row metrics only.
/// Such tables will be used in reports/monitors representing current activity across all targets. </summary>
/// <param name="metricGroup">metric group</param>
/// <param name="CacheType">data/dictionary</param>
public static void CreateCacheTableSingleRow(MetricGroup metricGroup, CacheType cacheType)
{
if (metricGroup.isMultiRow)
throw new Exception("Only single-row metrics are supported");
Dictionary<int, Column> keyColumns = new Dictionary<int, Column>();
Dictionary<int, Column> valueColumns = new Dictionary<int, Column>();
// targetId is the key because we have only one current records per target
keyColumns.Add(0, new Column("targetId", DataType.SmallInt));
// value columns
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
valueColumns.Add(i, metricGroup.metrics[i]);
}
// create new dictionary in dictionaryCache
if (!ContainsKey(GetCacheKey(-1, metricGroup, cacheType)))
TryAdd(GetCacheKey(-1, metricGroup, cacheType), new CacheTable(keyColumns, valueColumns, metricGroup.isCumulative));
}
/// <summary>Saves data into dictionary and data table for multi-value metrics</summary>
private void WriteMultipleRowsToRepository(int targetId, MetricGroup metricGroup, ProbeResultingData data)
{
int id;
CacheTable dictCache, dataCache;
List<int> newDictRows;
List<Tuple<int, int>> oldDictRows;
object[] key, attributes;
string dataTableName, dictTableName;
byte tryCount = 0;
bool canExit = false;
SqlTransaction tran = null;
string dictSqlStmt = string.Empty;
string dataSqlStmt = string.Empty;
newDictRows = new List<int>(); // ids of records that should be added to the dictionary (new rows or rows with updated attributes)
oldDictRows = new List<Tuple<int, int>>(); // ids and dictionary ids of records that changed since last probe and need to be closed
dataTableName = SqlServerProbe.DataTableName(targetId, metricGroup);
_logger.Debug("Name of data table: " + dataTableName);
dictTableName = SqlServerProbe.DictTableName(targetId, metricGroup);
_logger.Debug("Name of dictionary: " + dictTableName);
// load the dictionary cache table if it doesn't exist
if (!InMemoryCache.ContainsKey(dictTableName))
InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, false);
dictCache = Configuration.inMemoryCache[dictTableName];
// load the dictionary cache table if it doesn't exist
if (!InMemoryCache.ContainsKey(dataTableName))
InMemoryCache.LoadDataIntoCache(targetId, metricGroup, false);
/*
* Checks for changed or new records in dictionary and if needed prepares SQL statement to update dictionary table
*/
switch (metricGroup.multiRowKeyAttributesChangeSpeed)
{
case ChangeSpeed.Static:
// check whether all records are in the dictionary or some need to be added to it
for (int i = 0; i < data.NumberOfRows; i++)
{
key = new object[metricGroup.NumberOfMultiRowKeys];
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
key[j] = data.values[i, j];
if (dictCache.GetIdByKey(key) == -1)
newDictRows.Add(i);
}
// generate SQL statements if there are any new dictionary records
if (newDictRows.Count > 0)
dictSqlStmt = GenerateSqlStaticDict(targetId, metricGroup, data, newDictRows);
break;
case ChangeSpeed.Slow:
// check whether all records are in the dictionary or some need to be added to it
for (int i = 0; i < data.NumberOfRows; i++)
{
key = new object[metricGroup.NumberOfMultiRowKeys];
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
key[j] = data.values[i, j];
id = dictCache.GetIdByKey(key);
if (id == -1)
newDictRows.Add(i);
else // check that attributes match
{
attributes = new object[metricGroup.NumberOfMultiRowKeyAttributes];
for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++)
attributes[j] = data.values[i, metricGroup.NumberOfMultiRowKeys + j];
if (!dictCache.CompareAttributesForKey(id, attributes))
oldDictRows.Add(new Tuple<int, int>(i, id)); // this is to close the old record - UPDATE
}
}
// generate SQL statements if there are any changes or new records in dictionary
if (oldDictRows.Count > 0 || newDictRows.Count > 0)
dictSqlStmt = GenerateSqlSlowDict(targetId, metricGroup, data, oldDictRows, newDictRows);
break;
default:
throw new Exception("Unknown dictionary change speed");
}
/*
* Write new data into dictionary but don't close transaction yet
*/
if (dictSqlStmt.CompareTo(string.Empty) != 0)
{
_logger.Trace(dictSqlStmt);
// If tables don't exist, will try to create them and rerun SQL statements
while (!canExit && tryCount < 2)
{
try
{
// we will write to the dictionary first and then to the data table so we need to begin a transaction
tran = this.reposConn.BeginTransaction();
if (dictSqlStmt.CompareTo(string.Empty) != 0)
{
// save dictionary changes
using (SqlCommand cmd = this.reposConn.CreateCommand())
{
cmd.Transaction = tran;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = dictSqlStmt;
int rowCount = cmd.ExecuteNonQuery();
_logger.Debug("Rows affected: " + rowCount.ToString());
}
}
InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true, this.reposConn, tran);
canExit = true;
}
catch (SqlException e)
{
if (tran != null)
{
tran.Rollback();
tran.Dispose();
tran = null;
}
switch (e.Number)
{
case 208: // Invalid object
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return;
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
break;
default:
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
break;
}
}
tryCount++;
}
}
/*
* Prepare SQL statement to save data with right references to the dictionary records
*/
switch (metricGroup.changeSpeed)
{
case ChangeSpeed.Fast:
dataSqlStmt = "INSERT INTO " + dataTableName + " (dt,dictId,";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
dataSqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ",";
}
dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma
dataSqlStmt += ")" + Environment.NewLine + "VALUES";
for (int i = 0; i < data.NumberOfRows; i++)
{
dataSqlStmt += Environment.NewLine + "('" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',";
// retrieve corresponding id from dictionary
key = new object[metricGroup.NumberOfMultiRowKeys];
for (int k = 0; k < metricGroup.NumberOfMultiRowKeys; k++)
{
key[k] = data.values[i, k];
}
id = dictCache.GetIdByKey(key);
dataSqlStmt += id.ToString() + ",";
// add metric values
for (int j = 0; j < metricGroup.NumberOfMetrics; j++)
{
dataSqlStmt += SqlServerProbe.DataValueToString(metricGroup.metrics[j].type, data.values[i, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + j]) + ",";
}
dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma
dataSqlStmt += "),";
}
dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma
_logger.Trace(dataSqlStmt);
break;
default:
throw new Exception("Unsupported data change speed");
}
/*
* Executes SQL statements
* If tables don't exist, will try to create them and rerun SQL statements
*/
try
{
// save data
using (SqlCommand cmd = this.reposConn.CreateCommand())
{
if (tran != null) // use same transaction as for the dictionary
cmd.Transaction = tran;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = dataSqlStmt;
int rowCount = cmd.ExecuteNonQuery();
_logger.Debug("Rows affected: " + rowCount.ToString());
}
if (tran != null)
tran.Commit();
InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true);
dictCache = Configuration.inMemoryCache[dictTableName];
// Update in-memory data cache
object[] newValues;
dataCache = Configuration.inMemoryCache[dataTableName];
for (int i = 0; i < data.NumberOfRows; i++)
{
key = new object[metricGroup.NumberOfMultiRowKeys];
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
{
key[j] = data.values[i, j];
}
id = dictCache.GetIdByKey(key);
newValues = new object[metricGroup.NumberOfMetrics];
for (int j = 0; j < metricGroup.NumberOfMetrics; j++)
{
newValues[j] = data.values[i, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + j];
}
dataCache.AddOrUpdateRowValues(id, new object[0], newValues);
}
canExit = true;
}
catch (SqlException e)
{
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
if (tran != null)
{
tran.Rollback();
InMemoryCache.LoadDictionaryIntoCache(targetId, metricGroup, true);
}
}
}
/// <summary>Saves single row & fast changing metric</summary>
private void WriteFastSingleRowToRepository(int targetId, MetricGroup metricGroup, ProbeResultingData data)
{
// generate SQL statement
string dataSqlStmt = GenerateSqlSingleRowFast(targetId, metricGroup, data);
_logger.Trace(dataSqlStmt);
SqlServerProbe.ExecuteSql(dataSqlStmt, targetId, metricGroup);
// update in-memory data
object[] newValues = new object[data.NumberOfColumns];
for (int i = 0; i < data.NumberOfColumns; i++)
newValues[i] = data.values[0, i];
// create in-memory cache table if it doesn't exist
string cacheKey = InMemoryCache.GetCacheKey(-1, metricGroup);
if (!InMemoryCache.ContainsKey(cacheKey))
{
// Do no create new cache table if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return;
InMemoryCache.CreateCacheTableSingleRow(metricGroup, CacheType.Data);
}
Configuration.inMemoryCache[cacheKey].AddOrUpdateRowValues(-1, new object[] { targetId }, newValues);
}
/// <summary>Returns true if new data matches in-memory copy or no history is found - single row - slow changing</summary>
private int CompareSlowSingleRowWithInMemoryData(int targetId, MetricGroup metricGroup, ProbeResultingData data, SqlConnection connection)
{
bool noHistory = false;
CacheTable dataCache;
// create in-memory cache table if it doesn't exist
if (!InMemoryCache.ContainsKey(InMemoryCache.GetCacheKey(-1, metricGroup)))
{
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return -1;
InMemoryCache.CreateCacheTableSingleRow(metricGroup, CacheType.Data);
}
dataCache = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(-1, metricGroup, CacheType.Data)];
// load latest row from the repository if it is not in-memory yet
int id = dataCache.GetIdByKey(new object[] { targetId });
if (id == -1)
{
string sqlStmt = "SELECT ";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
sqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ",";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
sqlStmt += " FROM " + SqlServerProbe.DataTableName(targetId, metricGroup);
sqlStmt += " WHERE startDate = (SELECT MAX(startDate) FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + ")";
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sqlStmt;
try
{
SqlDataReader dataReader = cmd.ExecuteReader();
if (dataReader.Read())
{
object[] oldValues = new object[metricGroup.NumberOfMetrics];
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
// check data type before casting
switch (metricGroup.metrics[i].type)
{
case DataType.Ansi:
if (!DataTypeMappingSqlServer.DoesBelong(dataReader.GetDataTypeName(i), DataType.Ansi))
throw new Exception("Data type of column #" + (i + 1).ToString() + " of '" + metricGroup.name + "' metric does not match any allowed data type for internal data type Ansi");
oldValues[i] = (object)dataReader.GetString(i);
break;
case DataType.Unicode:
if (!DataTypeMappingSqlServer.DoesBelong(dataReader.GetDataTypeName(i), DataType.Unicode))
throw new Exception("Data type of column #" + (i + 1).ToString() + " of '" + metricGroup.name + "' metric does not match any allowed data type for internal data type Unicode");
oldValues[i] = (object)dataReader.GetString(i);
break;
case DataType.Double:
if (!DataTypeMappingSqlServer.DoesBelong(dataReader.GetDataTypeName(i), DataType.Double))
throw new Exception("Data type of column #" + (i + 1).ToString() + " of '" + metricGroup.name + "' metric does not match any allowed data type for internal data type Double");
oldValues[i] = (object)dataReader.GetDouble(i);
break;
case DataType.SmallInt:
if (!DataTypeMappingSqlServer.DoesBelong(dataReader.GetDataTypeName(i), DataType.SmallInt))
throw new Exception("Data type of column #" + (i + 1).ToString() + " of '" + metricGroup.name + "' metric does not match any allowed data type for internal data type Int16");
oldValues[i] = (object)dataReader.GetInt16(i);
break;
case DataType.Datetime:
if (!DataTypeMappingSqlServer.DoesBelong(dataReader.GetDataTypeName(i), DataType.Datetime))
throw new Exception("Data type of column #" + (i + 1).ToString() + " of '" + metricGroup.name + "' metric does not match any allowed data type for internal data type Datetime");
oldValues[i] = (object)dataReader.GetDateTime(i);
break;
default:
throw new Exception("Unknown data type");
} // end of switch
i++;
}
id = dataCache.Add(-1, new object[] { targetId }, oldValues);
}
else
noHistory = true;
dataReader.Close();
dataReader.Dispose();
}
catch (SqlException e)
{
if (e.Number == 208) // Invalid object
{
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return -1;
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
noHistory = true;
}
else
throw;
}
}
if (noHistory)
return -1;
}
// compare old and new values
object[] newValues = new object[metricGroup.NumberOfMetrics];
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
newValues[i] = data.values[0, i];
if (dataCache.CompareAttributesForKey(id, newValues))
return 0;
else
return 1;
}
// Generates INSERT statement for a single data row - static dictionary
private static string GenerateSqlStaticDict(int targetId, MetricGroup metricGroup, ProbeResultingData data, List<int> rowsNotInDict)
{
string sqlStmt = "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ",";
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
sqlStmt += ")" + Environment.NewLine;
sqlStmt += "VALUES " + Environment.NewLine;
foreach (int i in rowsNotInDict)
{
sqlStmt += "(";
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
{
sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[i, j]) + ",";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
sqlStmt += "),";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
return sqlStmt;
}
public static bool GetCurrentValues(int targetId, MetricGroup metricGroup, string[] metrics, out DataRow data)
{
if (metricGroup.isMultiRow)
throw new Exception("Only single-row metric group has been implemented so far");
return GetCurrentValuesSingleRow(targetId, metricGroup, metrics, out data);
}
public static void Enqueue(Target target, MetricGroup metricGroup, ProbeResultingData newData)
{
var msg = new ProbeResultsDataMessage(target, metricGroup, newData);
_dataQueue.Enqueue(msg);
}
private void ReloadMetricGroups()
{
MetricGroup metricGroup;
MetricGroupCollection tmp;
tmp = new MetricGroupCollection();
using (SqlConnection reposConn = new SqlConnection(GetReposConnectionString("Manager")))
{
reposConn.Open();
// Read MetricGroups into temporary collection. This is to allow running threads to work with old data.
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Id, Name, ProbeCode, ChangeSpeed, IsMultiRow, IsCumulative, MultiRowKeyAttributesChangeSpeed, Script FROM dbo.MetricGroups ORDER BY Id";
reposCmd.CommandType = CommandType.Text;
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
metricGroup = new MetricGroup(
(int)reposReader["Id"],
reposReader["Name"].ToString(),
probes.GetProbeCodeByName(reposReader["ProbeCode"].ToString()),
Configuration.ChangeSpeedFromString(reposReader["ChangeSpeed"].ToString()),
(bool)reposReader["IsMultiRow"],
(bool)reposReader["IsCumulative"],
Configuration.ChangeSpeedFromString(reposReader["MultiRowKeyAttributesChangeSpeed"].ToString()));
metricGroup.scriptText = reposReader["Script"].ToString();
tmp.Add(metricGroup);
}
reposReader.Close();
}
}
foreach (MetricGroup mg in tmp)
{
if (mg.isMultiRow)
{
// Read MultiRow Keys
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType from [dbo].[MetricMultiRowKeys] WHERE MetricGroupId = @metricGroupId AND IsKeyAttribute = 'FALSE' ORDER BY Id";
reposCmd.CommandType = CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMultiRowKey(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
// Read MultiRow Key Attributes
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType from [dbo].[MetricMultiRowKeys] WHERE MetricGroupId = @metricGroupId AND IsKeyAttribute = 'TRUE' ORDER BY Id";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMultiRowKeyAttribute(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
}
// Read from Metrics
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType FROM [dbo].[Metrics] WHERE MetricGroupId = @metricGroupId ORDER BY Id";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMetric(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
// Read default schedule
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT OffsetInSecondsFromMidnight, IntervalInSeconds, RetentionPeriodInHours FROM [dbo].[DefaultSchedule] WHERE MetricGroupId = @metricGroupId";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.defaultSchedule = new Schedule(
(int)reposReader["OffsetInSecondsFromMidnight"],
(int)reposReader["IntervalInSeconds"],
(int)reposReader["RetentionPeriodInHours"]
);
}
reposReader.Close();
}
}
}
reposConn.Close();
}
metricGroups = tmp;
}
// Returns query that select the latest data record for single-row realtime metric
private static string QueryToLoadSingleRowRealtime(int targetId, MetricGroup metricGroup)
{
string query = "SELECT TOP 1 ";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
query += metricGroup.metrics[i].name.Replace(' ', '_') + ", ";
}
query = query.Remove(query.Length - 2); // remove last comma
query += " FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + " ORDER BY ";
switch (metricGroup.changeSpeed)
{
case ChangeSpeed.Slow:
query += "startDate";
break;
case ChangeSpeed.Fast:
query += "dt";
break;
default:
throw new Exception("Only Slow and Fast change speeds have been implemented so far");
}
query += " DESC";
return query;
}
private static void LoadCacheTableFromDatabaseSingleRowRealtime(MetricGroup metricGroup)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader dataReader = null;
// check whether dictionary is loaded
if (!ContainsKey(GetCacheKey(-1, metricGroup)))
CreateCacheTableSingleRow(metricGroup, CacheType.Data);
CacheTable cache = _cache[GetCacheKey(-1, metricGroup)];
if (cache.loadedFromDatabase)
return;
try
{
conn = new SqlConnection(Configuration.GetReposConnectionString("Cache"));
conn.Open();
int targetId;
string query;
object[] values = new object[metricGroup.NumberOfMetrics];
// load data for each target with the specified metric group active
foreach (InstanceSchedule schedule in Configuration.timeTable.Values)
{
targetId = schedule._targetId;
if (schedule._metricGroupId == metricGroup.id)
{
query = QueryToLoadSingleRowRealtime(targetId, metricGroup);
cmd = null;
dataReader = null;
int attempt = 1;
bool canTry = true;
while (attempt < 3 && canTry)
{
try
{
cmd = new SqlCommand(query);
cmd.Connection = conn;
dataReader = cmd.ExecuteReader();
// should return only one row
if (dataReader.Read())
{
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
values[i] = dataReader[metricGroup.metrics[i].name.Replace(' ', '_')];
}
cache.Add(-1, new object[] { targetId }, values);
}
dataReader.Close();
}
catch (SqlException e)
{
switch (e.Number)
{
case 208: // Invalid object
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return;
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
break;
default:
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
_logger.Error(e.StackTrace);
canTry = false;
break;
}
}
finally
{
if (dataReader != null)
((IDisposable)dataReader).Dispose();
if (cmd != null)
((IDisposable)cmd).Dispose();
}
attempt++;
}
}
}
conn.Close();
}
catch (SqlException e)
{
_logger.Error(e.Message);
_logger.Error(e.StackTrace);
}
finally
{
if (conn != null)
((IDisposable)conn).Dispose();
}
cache.loadedFromDatabase = true;
}
private static bool GetCurrentValuesSingleRow(int targetId, MetricGroup metricGroup, string[] metrics, out DataRow data)
{
data = null;
object[] values = null;
CacheTable cache = null;
// Do not load data from repository if it has not been done yet. This means no current stats are available yet.
if (metricGroup.isCumulative)
{
if (ContainsKey(GetCacheKey(-1, metricGroup)))
cache = _cache[GetCacheKey(-1, metricGroup)];
}
else
{
if (ContainsKey(GetCacheKey(targetId, metricGroup)))
cache = _cache[GetCacheKey(targetId, metricGroup)];
}
data = new DataRow();
// get values by key (targetId)
if (cache != null)
{
int id = cache.GetIdByKey(new object[] { (object)targetId });
if (id != -1)
values = cache[id];
}
if (values == null)
{
foreach (string metricName in metrics)
data.Add(metricName, new DataValue(metricGroup[metricName].type, null));
return false;
}
foreach (string metricName in metrics)
{
data.Add(metricName,
new DataValue(cache.GetColumnMetadataByName(metricName).type,
values[cache.GetValueColumnIdByName(metricName) + 1]
)
);
}
return true;
}
// Loads dictionary from repository into in-memory cache. Creates a new record in dictionaryCache
public static void LoadDictionaryIntoCache(int targetId, MetricGroup metricGroup, bool allowReload, SqlConnection connection = null, SqlTransaction transaction = null)
{
string cacheKey;
SqlConnection conn;
int tryCount = 0;
bool canExit = false;
// create new in-memory cache for dictionary
if (!ContainsKey(GetCacheKey(targetId, metricGroup, CacheType.Dictionary)))
{
Dictionary<int, Column> keyColumns = new Dictionary<int, Column>();
Dictionary<int, Column> attrColumns = new Dictionary<int, Column>();
// key columns
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
keyColumns.Add(i, metricGroup.multiRowKeys[i]);
// attribute columns
for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++)
attrColumns.Add(i, metricGroup.multiRowKeyAttributes[i]);
TryAdd(GetCacheKey(targetId, metricGroup, CacheType.Dictionary), new CacheTable(keyColumns, attrColumns, false));
}
cacheKey = GetCacheKey(targetId, metricGroup, CacheType.Dictionary);
CacheTable tmpCache = _cache[cacheKey].CloneAndClear();
// don't reload cache unless allowReload is specified
if (allowReload == false && tmpCache.loadedFromDatabase)
return;
string sqlStmt = "SELECT id, ";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
sqlStmt += metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ", ";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++)
sqlStmt += metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ", ";
if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Static)
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma
else
sqlStmt += "startDate ";
sqlStmt += Environment.NewLine + "FROM " + SqlServerProbe.DictTableName(targetId, metricGroup);
if (metricGroup.multiRowKeyAttributesChangeSpeed == ChangeSpeed.Slow)
sqlStmt += Environment.NewLine + "WHERE endDate IS NULL";
_logger.Trace(sqlStmt);
while (!canExit && tryCount < 2)
{
try
{
if (connection == null)
{
conn = new SqlConnection(Configuration.GetReposConnectionString("Cache"));
conn.Open();
}
else
conn = connection;
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sqlStmt;
cmd.CommandType = System.Data.CommandType.Text;
if (transaction != null)
cmd.Transaction = transaction;
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
int id;
object[] keys = new object[metricGroup.NumberOfMultiRowKeys];
object[] values = new object[metricGroup.NumberOfMultiRowKeyAttributes];
while (dataReader.Read())
{
id = (int)dataReader["id"];
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
keys[i] = dataReader[1 + i];
for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++)
values[i] = dataReader[1 + metricGroup.NumberOfMultiRowKeys + i];
// add record to dictionary
switch (metricGroup.multiRowKeyAttributesChangeSpeed)
{
case ChangeSpeed.Static:
tmpCache.Add(id, keys, values);
break;
case ChangeSpeed.Slow:
tmpCache.Add(id, keys, values, (DateTime)dataReader[1 + metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]);
break;
default:
throw new Exception("Only Static and Slow changing dictionaries are supported");
}
}
tmpCache.loadedFromDatabase = true;
Replace(cacheKey, tmpCache);
dataReader.Close();
}
}
if (connection == null)
conn.Close();
}
catch (SqlException e)
{
if (e.Number == 208) // Invalid object
{
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return;
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
}
else
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
} // end of catch
tryCount++;
} // end of while
}
// Loads data from repository into in-memory cache. Creates a new record in dictionaryCache
public static void LoadDataIntoCache(int targetId, MetricGroup metricGroup, bool allowReload, SqlConnection connection = null, SqlTransaction transaction = null)
{
string cacheKey;
SqlConnection conn;
if (metricGroup.changeSpeed != ChangeSpeed.Fast)
throw new Exception("Only fast changing metric is allowed");
SqlCommand cmd = null;
SqlDataReader dataReader = null;
// create new in-memory cache for dictionary
if (!ContainsKey(GetCacheKey(targetId, metricGroup, CacheType.Data)))
{
Dictionary<int, Column> valueColumns = new Dictionary<int, Column>();
// value/metric columns
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
valueColumns.Add(i, metricGroup.metrics[i]);
}
TryAdd(GetCacheKey(targetId, metricGroup, CacheType.Data), new CacheTable(new Dictionary<int, Column>(), valueColumns, metricGroup.isCumulative));
}
cacheKey = GetCacheKey(targetId, metricGroup, CacheType.Data);
CacheTable tmpCache = _cache[cacheKey].CloneAndClear();
// don't reload cache unless allowReload is specified
if (allowReload == false && tmpCache.loadedFromDatabase)
return;
try
{
string sqlStmt = "SELECT dictId, ";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
sqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ", ";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma
sqlStmt += Environment.NewLine + "FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) +
Environment.NewLine + "WHERE dt = (SELECT MAX(dt) FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + ")";
_logger.Trace(sqlStmt);
if (connection == null)
{
conn = new SqlConnection(Configuration.GetReposConnectionString("Cache"));
conn.Open();
}
else
conn = connection;
int attempt = 1;
bool canTry = true;
while (attempt < 3 && canTry)
{
cmd = conn.CreateCommand();
cmd.CommandText = sqlStmt;
cmd.CommandType = System.Data.CommandType.Text;
if (transaction != null)
cmd.Transaction = transaction;
try
{
dataReader = cmd.ExecuteReader();
int id;
object[] values = new object[metricGroup.NumberOfMetrics];
while (dataReader.Read())
{
id = (int)dataReader["dictId"];
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
values[i] = dataReader[metricGroup.metrics[i].name.Replace(' ', '_')];
}
tmpCache.Add(id, new object[0], values);
}
dataReader.Close();
tmpCache.loadedFromDatabase = true;
Replace(cacheKey, tmpCache);
}
catch (SqlException e)
{
if (transaction != null)
{
transaction.Rollback();
}
switch (e.Number)
{
case 208: // Invalid object
// Do not create tables if target has been deleted
if (!Configuration.targets.ContainsKey(targetId))
return;
SqlServerProbe.CreateTablesForMetricGroup(targetId, metricGroup);
break;
default:
_logger.Error("SqlException: " + e.Message + " ErrorCode: " + e.Number.ToString());
canTry = false;
break;
}
}
finally
{
if (dataReader != null)
((IDisposable)dataReader).Dispose();
if (cmd != null)
((IDisposable)cmd).Dispose();
}
attempt++;
}
if (connection == null)
conn.Close();
}
catch (Exception e)
{
_logger.Error("SqlException: " + e.Message);
}
}
/// <summary>Saves single row & slow changing metric</summary>
private void WriteSlowSingleRowToRepository(int targetId, MetricGroup metricGroup, ProbeResultingData data)
{
int dataMatches;
string dataSqlStmt;
object[] newValues;
// compare with in-memory data
dataMatches = this.CompareSlowSingleRowWithInMemoryData(targetId, metricGroup, data, this.reposConn);
// generate SQL statement
dataSqlStmt = GenerateSqlSingleRowSlow(targetId, metricGroup, dataMatches, data);
_logger.Trace(dataSqlStmt);
SqlServerProbe.ExecuteSql(dataSqlStmt, targetId, metricGroup);
// update in-memory data
newValues = new object[data.NumberOfColumns];
for (int i = 0; i < data.NumberOfColumns; i++)
newValues[i] = data.values[0, i];
if (dataMatches == -1)
InMemoryCache.Add(InMemoryCache.GetCacheKey(targetId, metricGroup), -1, new object[] { targetId }, newValues);
else
Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup)].UpdateRowValues(new object[] { targetId }, newValues);
}
private void ReloadMetricGroups()
{
MetricGroup metricGroup;
MetricGroupCollection tmp;
tmp = new MetricGroupCollection();
using (SqlConnection reposConn = new SqlConnection(GetReposConnectionString("Manager")))
{
reposConn.Open();
// Read MetricGroups into temporary collection. This is to allow running threads to work with old data.
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Id, Name, ProbeCode, ChangeSpeed, IsMultiRow, IsCumulative, MultiRowKeyAttributesChangeSpeed, Script FROM dbo.MetricGroups ORDER BY Id";
reposCmd.CommandType = CommandType.Text;
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
metricGroup = new MetricGroup(
(int)reposReader["Id"],
reposReader["Name"].ToString(),
probes.GetProbeCodeByName(reposReader["ProbeCode"].ToString()),
Configuration.ChangeSpeedFromString(reposReader["ChangeSpeed"].ToString()),
(bool)reposReader["IsMultiRow"],
(bool)reposReader["IsCumulative"],
Configuration.ChangeSpeedFromString(reposReader["MultiRowKeyAttributesChangeSpeed"].ToString()));
metricGroup.scriptText = reposReader["Script"].ToString();
tmp.Add(metricGroup);
}
reposReader.Close();
}
}
foreach (MetricGroup mg in tmp)
{
if (mg.isMultiRow)
{
// Read MultiRow Keys
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType from [dbo].[MetricMultiRowKeys] WHERE MetricGroupId = @metricGroupId AND IsKeyAttribute = 'FALSE' ORDER BY Id";
reposCmd.CommandType = CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMultiRowKey(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
// Read MultiRow Key Attributes
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType from [dbo].[MetricMultiRowKeys] WHERE MetricGroupId = @metricGroupId AND IsKeyAttribute = 'TRUE' ORDER BY Id";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMultiRowKeyAttribute(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
}
// Read from Metrics
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT Name, DataType FROM [dbo].[Metrics] WHERE MetricGroupId = @metricGroupId ORDER BY Id";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.AddMetric(
reposReader["Name"].ToString(),
Configuration.DataTypeFromString(reposReader["DataType"].ToString())
);
}
reposReader.Close();
}
}
// Read default schedule
using (SqlCommand reposCmd = reposConn.CreateCommand())
{
reposCmd.CommandText = "SELECT OffsetInSecondsFromMidnight, IntervalInSeconds, RetentionPeriodInHours FROM [dbo].[DefaultSchedule] WHERE MetricGroupId = @metricGroupId";
reposCmd.CommandType = System.Data.CommandType.Text;
reposCmd.Parameters.Add("@metricGroupId", SqlDbType.Int);
reposCmd.Parameters["@metricGroupId"].Value = mg.id;
reposCmd.Prepare();
using (SqlDataReader reposReader = reposCmd.ExecuteReader())
{
while (reposReader.Read())
{
mg.defaultSchedule = new Schedule(
(int)reposReader["OffsetInSecondsFromMidnight"],
(int)reposReader["IntervalInSeconds"],
(int)reposReader["RetentionPeriodInHours"]
);
}
reposReader.Close();
}
}
}
reposConn.Close();
}
metricGroups = tmp;
}
// Generates INSERT statement for a single data row - fast changing metric
private static string GenerateSqlSingleRowFast(int targetId, MetricGroup metricGroup, ProbeResultingData data)
{
string dataSqlStmt = "INSERT INTO " + SqlServerProbe.DataTableName(targetId, metricGroup) + " (dt,";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
dataSqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ",";
}
dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma
dataSqlStmt += ")" + Environment.NewLine + "VALUES ('" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',";
if (metricGroup.NumberOfMetrics != data.NumberOfColumns)
throw new Exception("Number of metrics don't match number of columns in probe results");
// add metric values
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
dataSqlStmt += SqlServerProbe.DataValueToString(metricGroup.metrics[i].type, data.values[0, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + i]) + ",";
}
dataSqlStmt = dataSqlStmt.Remove(dataSqlStmt.Length - 1); // remove last comma
dataSqlStmt += ")";
return dataSqlStmt;
}
/// <summary>Generates UPDATE or INSERT statement for a single data row - slow changing metric</summary>
private static string GenerateSqlSingleRowSlow(int targetId, MetricGroup metricGroup, int dataMatches, ProbeResultingData data)
{
string dataSqlStmt;
if (dataMatches == 0) // just update endDate when current data matches one stored in repository
dataSqlStmt = "UPDATE " + SqlServerProbe.DataTableName(targetId, metricGroup)
+ " SET endDate = '" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "'"
+ " WHERE startDate = (SELECT MAX(startDate) FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + ")";
else
{
if (metricGroup.NumberOfMetrics != data.NumberOfColumns)
throw new Exception("Number of metrics doesn't match number of columns in probe results");
dataSqlStmt = "INSERT INTO " + SqlServerProbe.DataTableName(targetId, metricGroup) + " (";
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
dataSqlStmt += metricGroup.metrics[i].name.Replace(' ', '_') + ",";
}
dataSqlStmt += "startDate,endDate)"
+ Environment.NewLine + "VALUES (";
// add metric values
for (int i = 0; i < metricGroup.NumberOfMetrics; i++)
{
dataSqlStmt += SqlServerProbe.DataValueToString(metricGroup.metrics[i].type, data.values[0, metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes + i]) + ",";
}
// startDate,endDate
dataSqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "','" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "')";
}
return dataSqlStmt;
}
// Generates UPDATE statement for closing records and INSERT statement for openning records
private static string GenerateSqlSlowDict(int targetId, MetricGroup metricGroup, ProbeResultingData data, List<Tuple<int, int>> rowsChanged, List<int> rowsNotInDict)
{
CacheTable dict = Configuration.inMemoryCache[InMemoryCache.GetCacheKey(targetId, metricGroup, CacheType.Dictionary)];
string sqlStmt = string.Empty;
// old rows where endDate need to be updated
if (rowsChanged.Count > 0)
{
sqlStmt = "UPDATE " + SqlServerProbe.DictTableName(targetId, metricGroup) + Environment.NewLine;
sqlStmt += "SET endDate = '" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "'" + Environment.NewLine;
sqlStmt += "WHERE";
foreach (Tuple<int, int> ids in rowsChanged)
{
sqlStmt += Environment.NewLine + "(id = " + ids.Item2.ToString() + " AND ";
sqlStmt += "startDate = '" + SqlServerProbe.DateTimeToString((DateTime)dict[ids.Item2][metricGroup.NumberOfMultiRowKeys + metricGroup.NumberOfMultiRowKeyAttributes]) + "')";
sqlStmt += " OR ";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 4); // remove last ' OR '
sqlStmt += ";" + Environment.NewLine;
// new records for changed rows
sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ",";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++)
sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ",";
sqlStmt += "startDate,endDate)" + Environment.NewLine;
sqlStmt += "VALUES " + Environment.NewLine;
foreach (Tuple<int, int> ids in rowsChanged)
{
sqlStmt += "(" + ids.Item2.ToString() + ",";
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
{
sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[ids.Item1, j]) + ",";
}
for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++)
{
sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[ids.Item1, metricGroup.NumberOfMultiRowKeys + j]) + ",";
}
// add startDate and endDate
sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
}
// new rows
if (rowsNotInDict.Count > 0)
{
sqlStmt += "INSERT INTO " + SqlServerProbe.DictTableName(targetId, metricGroup) + " (id,";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeys; i++)
sqlStmt += Environment.NewLine + metricGroup.multiRowKeys[i].name.Replace(' ', '_') + ",";
for (int i = 0; i < metricGroup.NumberOfMultiRowKeyAttributes; i++)
sqlStmt += Environment.NewLine + metricGroup.multiRowKeyAttributes[i].name.Replace(' ', '_') + ",";
sqlStmt += "startDate,endDate)" + Environment.NewLine;
sqlStmt += "VALUES " + Environment.NewLine;
foreach (int i in rowsNotInDict)
{
sqlStmt += "(NEXT VALUE FOR " + SqlServerProbe.SchemaName(targetId) + ".seq_" + metricGroup.dictTableName + ",";
for (int j = 0; j < metricGroup.NumberOfMultiRowKeys; j++)
{
sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeys[j].type, data.values[i, j]) + ",";
}
for (int j = 0; j < metricGroup.NumberOfMultiRowKeyAttributes; j++)
{
sqlStmt += SqlServerProbe.DataValueToString(metricGroup.multiRowKeyAttributes[j].type, data.values[i, metricGroup.NumberOfMultiRowKeys + j]) + ",";
}
// add startDate and endDate
sqlStmt += "'" + SqlServerProbe.DateTimeToString(data.probeDateTime) + "',NULL),";
}
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 1); // remove last comma
}
return sqlStmt;
}
private bool Archive(int targetId, ArchiveOffset archiveOffset, MetricGroup metricGroup,
DateTime archiveTo, DateTime archiveFrom)
{
// Do not archive static and slow changing metrics
if (metricGroup.changeSpeed != ChangeSpeed.Fast)
return false;
// Compose SQL statement
// Save aggregated data in a temp table
string sqlStmt = "SELECT " + RoundDate("dt", archiveOffset.IntervalInSeconds) + " as dt, " + Environment.NewLine;
// add dictId if the metric group has multiple rows
if (metricGroup.isMultiRow)
sqlStmt += "dictId, ";
// Add AVG(column names)
foreach (var item in metricGroup.metrics)
sqlStmt += "AVG(" + item.Value.name.Replace(' ', '_') + ") as " + item.Value.name.Replace(' ', '_') + ", ";
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma
sqlStmt += "INTO #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine;
sqlStmt += "FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + Environment.NewLine;
sqlStmt += "WHERE dt BETWEEN @dateFrom AND @dateTo" + Environment.NewLine;
sqlStmt += "GROUP BY " + RoundDate("dt", archiveOffset.IntervalInSeconds) + ", ";
// add dictId if the metric group has multiple rows
if (metricGroup.isMultiRow)
sqlStmt += "dictId, ";
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + ";" + Environment.NewLine + Environment.NewLine; // remove last comma
// Delete aggregated records
sqlStmt += "DELETE FROM " + SqlServerProbe.DataTableName(targetId, metricGroup) + " WHERE dt BETWEEN @dateFrom AND @dateTo;" + Environment.NewLine + Environment.NewLine;
// Copy records from the temp table
sqlStmt += "INSERT INTO " + SqlServerProbe.DataTableName(targetId, metricGroup) + " (dt, ";
// add dictId if the metric group has multiple rows
if (metricGroup.isMultiRow)
sqlStmt += "dictId, ";
// Add column names
foreach (var item in metricGroup.metrics)
sqlStmt += item.Value.name.Replace(' ', '_') + ", ";
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2); // remove last comma
sqlStmt += ")" + Environment.NewLine;
sqlStmt += "SELECT dt, ";
// add dictId if the metric group has multiple rows
if (metricGroup.isMultiRow)
sqlStmt += "dictId, ";
// Add column names
foreach (var item in metricGroup.metrics)
sqlStmt += item.Value.name.Replace(' ', '_') + ", ";
sqlStmt = sqlStmt.Remove(sqlStmt.Length - 2) + Environment.NewLine; // remove last comma
sqlStmt += "FROM #AVG_TMP_" + metricGroup.dataTableName + Environment.NewLine + Environment.NewLine;
// Update ArchivedToDate value
sqlStmt += "UPDATE dbo.ArchiveWatermarks SET ArchivedToDate = @dateTo WHERE ArchiveOffsetId = @archiveOffsetId and TargetId = @targetId;";
_logger.Trace(sqlStmt);
// Execute SQL statement
SqlTransaction reposTran = null;
SqlCommand reposCmd = null;
try
{
if (_reposConn.State != ConnectionState.Open)
_reposConn.Open();
reposTran = _reposConn.BeginTransaction();
reposCmd = _reposConn.CreateCommand();
reposCmd.Transaction = reposTran;
reposCmd.CommandType = CommandType.Text;
reposCmd.CommandText = sqlStmt;
reposCmd.CommandTimeout = 300;
reposCmd.Parameters.Add("@targetId", SqlDbType.Int);
reposCmd.Parameters["@targetId"].Value = targetId;
reposCmd.Parameters.Add("@archiveOffsetId", SqlDbType.Int);
reposCmd.Parameters["@archiveOffsetId"].Value = archiveOffset.Id;
reposCmd.Parameters.Add("@dateFrom", SqlDbType.DateTime2, 6);
reposCmd.Parameters["@dateFrom"].Value = RoundDate(archiveFrom, archiveOffset.IntervalInSeconds);
reposCmd.Parameters.Add("@dateTo", SqlDbType.DateTime2, 6);
reposCmd.Parameters["@dateTo"].Value = archiveTo;
reposCmd.Prepare();
reposCmd.ExecuteNonQuery();
reposTran.Commit();
}
catch (SqlException e)
{
if (_reposConn.State != ConnectionState.Open)
{
Manager.SetRepositoryAccessibility(false);
return false;
}
switch (e.Number)
{
case 208: // Ignore missing tables. Target might be recently initialized
break;
default:
_logger.Error("SqlException: {0} ErrorCode: {1}", e.Message, e.Number);
break;
}
if (reposTran != null)
{
// Transaction might be rolled back if commit fails. In this case second rollback will fail
try
{
reposTran.Rollback();
}
catch (Exception)
{
_logger.Debug("Transaction has been rolled back already");
}
}
return false;
}
catch (Exception e)
{
if (_reposConn.State == ConnectionState.Open)
{
_logger.Error(e.Message);
_logger.Error(e.StackTrace);
}
else
{
Manager.SetRepositoryAccessibility(false);
}
return false;
}
finally
{
if (reposCmd != null)
((IDisposable)reposCmd).Dispose();
if (reposTran != null)
((IDisposable)reposTran).Dispose();
}
return true;
}
/// <summary> Returns cache key name
/// For single-row metric cache is common for all targets (table name only, no schema)
/// Multi-row metrics each have its own cache </summary>
/// <param name="targetId">target id or -1 for single row metrics</param>
/// <param name="metricGroup">metric group</param>
/// <param name="CacheType">data/dictionary</param>
public static string GetCacheKey(int targetId, MetricGroup metricGroup, CacheType cacheType = CacheType.Data)
{
switch (cacheType)
{
case CacheType.Data:
if (metricGroup.isMultiRow)
return SqlServerProbe.DataTableName(targetId, metricGroup);
else
return metricGroup.dataTableName;
case CacheType.Dictionary:
return SqlServerProbe.DictTableName(targetId, metricGroup);
default:
throw new Exception("Unsupported cache type");
}
}