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));
        }
Example #3
0
        /// <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);
                }
            }
        }
Example #4
0
        /// <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);
        }
Example #5
0
        /// <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;
        }
Example #6
0
        // 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);
        }
Example #8
0
 public static void Enqueue(Target target, MetricGroup metricGroup, ProbeResultingData newData)
 {
     var msg = new ProbeResultsDataMessage(target, metricGroup, newData);
     _dataQueue.Enqueue(msg);
 }
Example #9
0
        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);
            }
        }
Example #15
0
        /// <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;
        }
Example #17
0
        // 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;
        }
Example #18
0
        /// <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;
        }
Example #19
0
        // 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;
        }
Example #20
0
        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");
     }
 }