public override void ReadTo(MultyQueryResultInfo result)
{
foreach (var guardQueryInfo in base.TemplateNode.SqlCodeGuardQueries)
{
this.ProcessCodeGuardQuery(result, guardQueryInfo);
}
}
public void SaveRequestedData(
TemplateNodeInfo templateNodeInfo,
MultyQueryResultInfo results
)
{
this._storageManager.SaveRequestedData(templateNodeInfo, results);
}
public QueryResultDataSource(
MsSqlAuditorModel model,
ConcreteTemplateNodeDefinition nodeDefinition
)
{
this._model = model;
this._nodeDefinition = nodeDefinition;
this._queriesResult = ReadQueriesResult();
}
public override void ReadTo(MultyQueryResultInfo result)
{
foreach (TemplateNodeResultItem templateNodeResultItem in base.TemplateNode.Queries.Select(
templateNodeQueryInfo => ReadTemplateNodeResult(templateNodeQueryInfo, result))
)
{
result.Add(templateNodeResultItem);
}
}
public MultyQueryResultInfo Read(
ConnectionGroupInfo connectionGroup,
ConcreteTemplateNodeDefinition concreteTemplateNode
)
{
var result = new MultyQueryResultInfo();
this.ReadFromMeta(connectionGroup, concreteTemplateNode, result);
this.ReadFromCodeGuard(concreteTemplateNode, result);
return result;
}
private void ReadFromCodeGuard(
ConcreteTemplateNodeDefinition concreteTemplateNode,
MultyQueryResultInfo result
)
{
NodeResultReader nodeResultReader = new CodeGuardResultReader(
this._msSqlAuditor,
this._storageManager,
concreteTemplateNode
);
nodeResultReader.ReadTo(result);
}
private void ReadFromMeta(
ConnectionGroupInfo connectionGroup,
ConcreteTemplateNodeDefinition concreteTemplateNode,
MultyQueryResultInfo result
)
{
NodeResultReader nodeResultReader = new MetaResultReader(
this._msSqlAuditor,
this._storageManager,
connectionGroup,
concreteTemplateNode
);
nodeResultReader.ReadTo(result);
}
private List<Tuple<TemplateNodeQueryInfo, Int64, DataTable>> GetResultTables(
ConcreteTemplateNodeDefinition nodeDefinition,
MultyQueryResultInfo queriesResult
)
{
List<Tuple<TemplateNodeQueryInfo, Int64, DataTable>> resultTables =
new List<Tuple<TemplateNodeQueryInfo, Int64, DataTable>>();
try
{
GroupDefinition database = nodeDefinition.Group;
if (database != null)
{
foreach (QueryDatabaseResult queryDatabaseResult in queriesResult.GetDatabaseResults())
{
QueryDatabaseResultInfo dbResult = queryDatabaseResult.Result;
if (dbResult == null || dbResult.DataTables == null)
{
continue;
}
if (database.Name == dbResult.Database)
{
Int64 recordSet = 1L;
foreach (DataTable curTable in dbResult.DataTables)
{
resultTables.Add(
new Tuple<TemplateNodeQueryInfo, Int64, DataTable>(
queryDatabaseResult.TemplateNodeQuery,
recordSet,
curTable
)
);
recordSet++;
}
}
}
}
}
catch (Exception ex)
{
log.Error("Error in 'Extracts data from queries and saves it to table for RDLC.'", ex);
throw;
}
return resultTables;
}
private TemplateNodeResultItem ReadTemplateNodeResult(TemplateNodeQueryInfo templateNodeQueryInfo, MultyQueryResultInfo result)
{
var queryResult = new QueryResultInfo();
List<QueryInfo> queries = MsSqlAuditor.GetQueryByTemplateNodeQueryInfo(templateNodeQueryInfo);
// looking for user settings for parameter values
templateNodeQueryInfo.ReadParametersFrom(Settings);
string connectionsSelectId = templateNodeQueryInfo.ConnectionsSelectId;
if (connectionsSelectId != null)
{
foreach (InstanceInfo instance in this._instances)
{
Int64? queryId = Storage.QueryDirectory.GetQueryId(
templateNodeQueryInfo.TemplateNode,
templateNodeQueryInfo,
instance,
DateTime.Now,
false
);
if (queryId.HasValue)
{
List<DynamicConnection> connections = new List<DynamicConnection>(
Storage.DynamicConnectionDirectory.ReadConnections(queryId.Value)
);
foreach (DynamicConnection connection in connections)
{
if (!connection.QueryId.HasValue)
{
continue;
}
Int64 dynamicQueryId = connection.QueryId.Value;
QuerySource sourceType;
if (!Enum.TryParse(connection.Type, true, out sourceType))
{
_log.ErrorFormat(
@"Unknown ConnectionType:'{0}'",
connection.Type ?? "<Null>"
);
sourceType = QuerySource.MSSQL;
}
InstanceInfo selectConnectionInstance = InstanceInfoResolver.ResolveDynamicInstance(
connection.Name,
sourceType,
connection.IsOdbc
);
selectConnectionInstance.ConnectionGroup = instance.ConnectionGroup;
selectConnectionInstance.LoadServerProperties(Storage);
QueryInstanceResultInfo instanceResult = GetInstanceResult(
result,
selectConnectionInstance,
dynamicQueryId,
templateNodeQueryInfo,
queries
);
if (instanceResult != null)
{
queryResult.AddInstanceResult(instanceResult);
}
}
}
}
}
else
{
foreach (InstanceInfo instance in this._instances)
{
Int64? queryId = Storage.QueryDirectory.GetQueryId(
base.TemplateNode,
templateNodeQueryInfo,
instance,
new DateTime(),
true
);
if (queryId != null)
{
QueryInstanceResultInfo instanceResult = GetInstanceResult(
result,
instance,
queryId.Value,
templateNodeQueryInfo,
queries
);
if (instanceResult != null)
{
queryResult.AddInstanceResult(instanceResult);
}
}
}
}
Tuple<DateTime?, DateTime?> dateTimes =
Storage.NodeInstances.GetTreeNodeLastUpdateAndDuration(base.TemplateNode);
result.NodeLastUpdated = dateTimes.Item1;
result.NodeLastUpdateDuration = dateTimes.Item2;
return new TemplateNodeResultItem(templateNodeQueryInfo, queryResult);
}
private void ProcessCodeGuardQuery(
MultyQueryResultInfo result,
TemplateNodeSqlGuardQueryInfo guardQueryInfo)
{
var queryResultInfo = result.List.First(item => item.TemplateNodeQuery.Id == guardQueryInfo.SqlQueryId);
var templateNodeQueryInfo = queryResultInfo.TemplateNodeQuery;
var userParams = new List<ParameterValue>();
if (base.Settings != null)
{
var querySettings = base.Settings.Connection.Activity.Parameters
.Where(i => i.Key == guardQueryInfo.IdsHierarchy && i.Value != null);
foreach (var info in querySettings)
{
switch (info.Type)
{
case ParameterInfoType.Attribute:
guardQueryInfo.GetType().GetProperty("User" + info.Parameter)
.SetValue(guardQueryInfo, info.Value, null);
break;
case ParameterInfoType.Parameter:
var parameter =
templateNodeQueryInfo.ParameterValues.FirstOrDefault(p => p.Name == info.Parameter);
if (parameter != null)
{
parameter.UserValue = info.Value;
}
break;
case ParameterInfoType.EditableParameter:
var editparameter = new ParameterValue
{
Name = info.Parameter,
StringValue = info.Default,
UserValue = info.Value
};
userParams.Add(editparameter);
break;
}
}
}
var guardQueryResult = new QueryResultInfo();
foreach (var instanceResult in queryResultInfo.QueryResult.InstancesResult)
{
var instance = instanceResult.Key;
var queryTable = instanceResult.Value.DatabasesResult.First().Value.DataTables.First();
if (!queryTable.Columns.Contains(guardQueryInfo.QueryCodeColumn))
{
continue;
}
//var meta = ReadMeta(connectionGroup, templateNode, instance, database, templateNodeQueryInfo).FirstOrDefault();
var meta = Storage.ReadLastMeta(
base.TemplateNode,
instance,
templateNodeQueryInfo
);
if (meta == null)
{
continue;
}
QueryInstanceResultInfo guardInstanceResult;
var timestamp = (DateTime)meta.Values[TableDefinition.DateCreated];
result.RefreshTimestamp(timestamp);
if (!string.IsNullOrEmpty(meta.Values[MetaResultTable.ErrorMessageFieldName].ToString()))
{
guardInstanceResult = new QueryInstanceResultInfo(
new ErrorInfo(
meta.Values[MetaResultTable.ErrorIdFieldName].ToString(),
meta.Values[MetaResultTable.ErrorCodeFieldName].ToString(),
meta.Values[MetaResultTable.ErrorMessageFieldName].ToString(),
timestamp
),
instance
);
}
else
{
guardInstanceResult = new QueryInstanceResultInfo(instance);
}
var dataTables = Storage.ReadSqlCodeGuardResult(guardQueryInfo, queryTable, userParams);
QueryItemInfo queryItemInfo = new QueryItemInfo
{
ParentQuery = new QueryInfo { Name = guardQueryInfo.QueryName }
};
QueryDatabaseResultInfo databaseResult = new QueryDatabaseResultInfo(
dataTables,
queryItemInfo,
base.GroupDefinition.Name,
base.GroupDefinition.Id
);
guardInstanceResult.AddDatabaseResult(databaseResult);
guardQueryResult.AddInstanceResult(guardInstanceResult);
}
var templateNodeResultItem = new TemplateNodeResultItem(guardQueryInfo, guardQueryResult);
result.Add(templateNodeResultItem);
}
public abstract void ReadTo(MultyQueryResultInfo result);
internal void SaveMeta(
TemplateNodeInfo templateNodeInfo,
MultyQueryResultInfo results,
long requestId,
DateTime timestamp
)
{
Debug.Assert(templateNodeInfo.IsInstance);
Int64 sessionId = 1L;
List<ITableRow> metaRows = new List<ITableRow>();
foreach (TemplateNodeResultItem tuple in results.List)
{
TemplateNodeQueryInfo templateNodeQuery = tuple.TemplateNodeQuery;
QueryResultInfo queryResult = tuple.QueryResult;
foreach (QueryInstanceResultInfo queryInstanceResult in queryResult.InstancesResult.Values)
{
InstanceInfo instance = queryInstanceResult.Instance;
Int64? queryId = this.QueryDirectory.GetQueryId(
templateNodeInfo,
templateNodeQuery,
instance,
timestamp,
false);
Log.InfoFormat("Instance:'{0}';QueryId:'{1}'",
instance,
queryId
);
ITableRow row = this.MetaResultTable.GetMetaRow(
requestId,
sessionId,
timestamp,
queryInstanceResult,
queryId,
null
);
metaRows.Add(row);
}
}
this.MetaResultTable.ReplaceRows(metaRows);
this.ResetRowCountCache(templateNodeInfo);
}
private void SaveDynamicConnections(
TemplateNodeInfo templateNode,
MultyQueryResultInfo queriesResult
)
{
foreach (TemplateNodeResultItem templateNodeResultItem in queriesResult.List)
{
TemplateNodeQueryInfo queryInfo = templateNodeResultItem.TemplateNodeQuery;
TemplateNodeInfo templateNodeClone = templateNode;
if (queryInfo.ConnectionsSelectId != null)
{
QueryResultInfo queryResult = templateNodeResultItem.QueryResult;
foreach (InstanceInfo instanceInfo in templateNodeClone.ConnectionGroup.Connections)
{
Int64? destParentQueryId = this.CurrentStorage.QueryDirectory.GetQueryId(
templateNode,
queryInfo,
instanceInfo,
DateTime.Now,
false
);
foreach (KeyValuePair<InstanceInfo, QueryInstanceResultInfo> queryInstanceResultInfo in queryResult.InstancesResult)
{
InstanceInfo selectConnectionInstance = queryInstanceResultInfo.Key;
Int64? dynamicQueryId = this.CurrentStorage.QueryDirectory.GetQueryId(
templateNodeClone,
queryInfo,
selectConnectionInstance,
DateTime.Now,
false
);
if (destParentQueryId == null)
{
continue;
}
DynamicConnection dynamicConnection = new DynamicConnection(
selectConnectionInstance.Name,
selectConnectionInstance.Type.ToString(),
selectConnectionInstance.IsODBC,
dynamicQueryId
);
this.CurrentStorage.DynamicConnectionDirectory.UpdateConnection(
destParentQueryId.Value,
dynamicConnection
);
}
}
}
}
}
public void SerializeData(
TemplateNodeInfo templateNodeInfo,
MultyQueryResultInfo queriesResult
)
{
SaveDynamicConnections(templateNodeInfo, queriesResult);
SaveRequestedData(templateNodeInfo, queriesResult);
this.CurrentStorage.UpdateTreeNodeTimings(
templateNodeInfo,
queriesResult
);
}
public void SaveRequestedData(
TemplateNodeInfo templateNodeInfo,
MultyQueryResultInfo results
)
{
Debug.Assert(templateNodeInfo.IsInstance);
long requestId = this.CurrentStorage.MetaResultTable.GetMaxRequestId() + 1L;
DateTime timestamp = DateTime.Now;
const long sessionId = 1L;
List<ITableRow> metaRows = new List<ITableRow>();
foreach (TemplateNodeResultItem nodeResult in results.List)
{
TemplateNodeQueryInfo templateNodeQuery = nodeResult.TemplateNodeQuery;
QueryResultInfo queryResult = nodeResult.QueryResult;
foreach (KeyValuePair<InstanceInfo, QueryInstanceResultInfo> instancePair in queryResult.InstancesResult)
{
long totalRowsSaved = 0L;
InstanceInfo instance = instancePair.Key;
QueryInstanceResultInfo queryInstanceResult = instancePair.Value;
Int64? queryId = CurrentStorage.QueryDirectory.GetQueryId(
templateNodeInfo,
templateNodeQuery,
instance,
timestamp,
false
);
Log.InfoFormat("Instance:'{0}';QueryId:'{1}'",
instance,
queryId
);
if (queryInstanceResult.ErrorInfo == null)
{
IEnumerable<QueryDatabaseResultInfo> notEmptyResults = queryInstanceResult.DatabasesResult.Values
.Where(d => d != null && d.DataTables != null);
foreach (QueryDatabaseResultInfo databaseResultInfo in notEmptyResults)
{
totalRowsSaved += SaveDatabaseResult(
templateNodeQuery,
instance,
databaseResultInfo,
queryId
);
}
}
ITableRow metaRow = this.CurrentStorage.MetaResultTable.GetMetaRow(
requestId,
sessionId,
timestamp,
queryInstanceResult,
queryId,
totalRowsSaved
);
metaRows.Add(metaRow);
}
}
this.CurrentStorage.MetaResultTable.ReplaceRows(metaRows);
this.CurrentStorage.ResetRowCountCache(templateNodeInfo);
}
/// <summary>
/// Executes multyquery
/// </summary>
/// <param name="connectionGroup">Connection group</param>
/// <param name="templateNodeQueryInfos">Template nodes</param>
/// <param name="progress">Progress item</param>
/// <param name="maxthreadCount">Maximum thread</param>
/// <returns>Multyquery result</returns>
public MultyQueryResultInfo ExecuteMultyQuery(
ConnectionGroupInfo connectionGroup,
List<TemplateNodeQueryInfo> templateNodeQueryInfos,
ProgressItem progress,
int maxthreadCount,
bool checkHist = false
)
{
Settings.InstanceTemplate settings = null;
////////////////////////////////////////////////////////////////////////////////////////
// string strLogMessage = "DEBUG:MSSQLServerAuditor.Model.ExecuteMultyQuery(1)";
// strLogMessage = strLogMessage + ";GD:" + connectionGroup.ToString();
// log.Debug(strLogMessage);
////////////////////////////////////////////////////////////////////////////////////////
var result = new MultyQueryResultInfo();
if (templateNodeQueryInfos.Count > 0)
{
progress.SetPromisedChildCount(templateNodeQueryInfos.Count);
foreach (var qi in templateNodeQueryInfos)
{
TemplateNodeQueryInfo queryInfo = qi;
QueryResultInfo queryResult = null;
try
{
var queries = this._model.GetQueryByTemplateNodeQueryInfo(queryInfo);
if (this._skipMsSqlQueries)
{
queries.RemoveAll(
x => (
x.Source == QuerySource.MSSQL
|| x.Source == QuerySource.TDSQL
)
);
}
if (checkHist)
{
queries.RemoveAll(x => (x.Source != QuerySource.SQLite));
}
else
{
queries.RemoveAll(x => (x.Source == QuerySource.SQLite));
}
if (queryInfo.ConnectionsSelectId == null)
{
if (!queries.Any(
x => connectionGroup.Connections.Select(y => y.Type).Contains(x.Source)
|| x.Source == QuerySource.SQLite
))
{
continue;
}
}
// var settings = Program.Model.TemplateSettings.UserSettings.FirstOrDefault(i =>
// i.TemplateName == connectionGroup.TemplateFileName
// && i.Connection.ParentKey == queryInfo.TemplateNode.IdsHierarchy
// );
if (Program.Model != null)
{
settings = Program.Model.TemplateSettings.UserSettings.FirstOrDefault(i =>
i.TemplateName == connectionGroup.TemplateFileName
&& i.Connection.ParentKey == queryInfo.TemplateNode.IdsHierarchy
);
}
else
{
settings = this._model.TemplateSettings.UserSettings.FirstOrDefault(i =>
i.TemplateName == connectionGroup.TemplateFileName
&& i.Connection.ParentKey == queryInfo.TemplateNode.IdsHierarchy
);
}
queryInfo.ReadParametersFrom(settings);
string connectionsSelectId = queryInfo.ConnectionsSelectId;
if (connectionsSelectId != null)
{
queryResult = ExecuteConnectionsSelectQuery(
queryInfo,
connectionGroup,
queries,
maxthreadCount
);
}
else
{
queryResult = ExecuteQuery(
connectionGroup,
queries,
QueryExecutionParams.CreateFrom(queryInfo),
maxthreadCount, progress.GetChild()
);
}
}
catch (OperationCanceledException ex)
{
log.Error(queryInfo.ToString(), ex);
throw;
}
catch (AggregateException ex)
{
if (ex.InnerExceptions.All(e => e is OperationCanceledException))
{
throw;
}
queryResult = new QueryResultInfo(
new ErrorInfo(
ex.InnerExceptions.FirstOrDefault(
e => !(e is OperationCanceledException)
)
)
);
progress.GetChild().SetProgress(100);
log.Error(queryInfo.ToString(), ex);
}
catch (Exception ex)
{
queryResult = new QueryResultInfo(new ErrorInfo(ex));
progress.GetChild().SetProgress(100);
log.Error(queryInfo.ToString(), ex);
}
result.Add(new TemplateNodeResultItem(queryInfo, queryResult));
}
}
else
{
progress.SetProgress(100);
}
return result;
}
/// <summary>
/// Executes multyquery for database
/// </summary>
/// <param name="group">Database definition</param>
/// <param name="templateNodeQueryInfos">Template nodes</param>
/// <param name="progress">Progress item</param>
/// <returns>Multyquery result</returns>
public MultyQueryResultInfo ExecuteMultyQuery(
GroupDefinition @group,
List<TemplateNodeQueryInfo> templateNodeQueryInfos,
ProgressItem progress
)
{
////////////////////////////////////////////////////////////////////////////////////////
// string strLogMessage = "DEBUG:MSSQLServerAuditor.Model.ExecuteMultyQuery(2)";
// strLogMessage = strLogMessage + ";GD:" + @group.ToString();
// log.Debug(strLogMessage);
////////////////////////////////////////////////////////////////////////////////////////
MultyQueryResultInfo result = new MultyQueryResultInfo();
progress.SetPromisedChildCount(templateNodeQueryInfos.Count);
foreach (TemplateNodeQueryInfo queryInfo in templateNodeQueryInfos)
{
List<QueryInfo> queries = this._model.GetQueryByTemplateNodeQueryInfo(queryInfo);
QueryInfo query = queries.FirstOrDefault(x =>
x.Source == (@group.Instance.Type) || x.Source == QuerySource.SQLite);
if ((query.Source == QuerySource.MSSQL || query.Source == QuerySource.TDSQL) && this._skipMsSqlQueries)
{
continue;
}
QueryExecutionParams parameters = QueryExecutionParams.CreateFrom(queryInfo);
QueryResultInfo queryResult = ExecuteQuery(
@group,
query,
parameters,
progress.GetChild()
);
result.Add(new TemplateNodeResultItem(queryInfo, queryResult));
if (this._cancellationToken.IsCancellationRequested)
{
break;
}
}
return result;
}
/// <summary>
/// Append errors to log
/// </summary>
/// <param name="multyQueryResultInfo">from multyquery result</param>
public void AppendErrorLog(MultyQueryResultInfo multyQueryResultInfo)
{
foreach (TemplateNodeResultItem item in multyQueryResultInfo.List)
{
if (item.QueryResult.ErrorInfo == null)
{
AppendErrorLog(item.QueryResult);
}
else
{
this._errorItems.Add(new ErrorLogItem(item.QueryResult.ErrorInfo.DateTime, null,
new QueryItemInfo() {Text = item.TemplateNodeQuery.QueryName},
item.QueryResult.ErrorInfo.Exception)
);
}
}
}
private QueryInstanceResultInfo GetInstanceResult(
MultyQueryResultInfo result,
InstanceInfo instance,
Int64 queryId,
TemplateNodeQueryInfo templateNodeQueryInfo,
List<QueryInfo> queries
)
{
QueryInstanceResultInfo instanceResult = null;
Int64 recordSetCount = 0L;
ITableRow meta = Storage.ReadLastMeta(queryId);
if (meta != null)
{
DateTime timestamp = (DateTime)meta.Values[TableDefinition.DateCreated];
result.RefreshTimestamp(timestamp);
if (!string.IsNullOrEmpty(meta.Values[MetaResultTable.ErrorMessageFieldName].ToString()))
{
instanceResult = new QueryInstanceResultInfo(
new ErrorInfo(
meta.Values[MetaResultTable.ErrorIdFieldName].ToString(),
meta.Values[MetaResultTable.ErrorCodeFieldName].ToString(),
meta.Values[MetaResultTable.ErrorMessageFieldName].ToString(),
(DateTime)meta.Values[TableDefinition.DateCreated]
),
instance
);
}
else
{
instanceResult = new QueryInstanceResultInfo(instance);
}
recordSetCount = (Int64) meta.Values[MetaResultTable.RecordSetCountFieldName];
DataTable[] dataTables = GetDataTables(
recordSetCount,
queryId,
instance,
templateNodeQueryInfo
);
QueryInfo query = queries.FirstOrDefault(x => x.Source == instance.Type || x.Source == QuerySource.SQLite);
if (query != null)
{
QueryDatabaseResultInfo databaseResult = new QueryDatabaseResultInfo(
dataTables,
query.Items.GetQueryItemForVersion(instance.GetServerPropertiesSafe().Version),
base.GroupDefinition.Name,
base.GroupDefinition.Id
);
instanceResult.AddDatabaseResult(databaseResult);
}
}
return instanceResult;
}
public void UpdateTreeNodeTimings(
TemplateNodeInfo node,
MultyQueryResultInfo results
)
{
UpdateTreeNodeTimings(
node,
results.NodeLastUpdated,
results.NodeLastUpdateDuration
);
}
private Dictionary<TemplateNodeQueryInfo, DataTable> PrepareHistoryData(
MultyQueryResultInfo results
)
{
Dictionary<TemplateNodeQueryInfo, DataTable> result =
new Dictionary<TemplateNodeQueryInfo, DataTable>();
foreach (QueryDatabaseResult queryDatabaseResult in results.GetDatabaseResults())
{
QueryDatabaseResultInfo dbResult = queryDatabaseResult.Result;
if (dbResult == null || dbResult.DataTables == null)
{
continue;
}
TemplateNodeQueryInfo templateNodeQuery = queryDatabaseResult.TemplateNodeQuery;
foreach (DataTable table in dbResult.DataTables)
{
Debug.Assert(table != null);
if (!result.ContainsKey(templateNodeQuery))
{
result.Add(templateNodeQuery, table);
}
}
}
return result;
}
