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;
}
} // end of Work method
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);
} // end of Archive method
