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); }
// 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"); } }