} // end of GenerateSqlSlowDict method
#endregion private static methods declarations
#region private methods declarations
/// <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);
}
} // end of WriteSlowSingleRowToRepository function
} // end of WriteSlowSingleRowToRepository function
/// <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);
} // end of WriteFastSingleRowToRepository function
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>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);
}
}
} // end of GenerateSqlStaticDict method
// 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);
} // end of GenerateSqlSlowDict method
