/// <summary>
/// Append errors to log
/// </summary>
/// <param name="queryResult">from query result</param>
public void AppendErrorLog(QueryResultInfo queryResult)
{
foreach (KeyValuePair<InstanceInfo, QueryInstanceResultInfo> instancePair in queryResult.InstancesResult)
{
var instance = instancePair.Value;
if (instance.ErrorInfo != null)
{
this._errorItems.Add(new ErrorLogItem(instance.ErrorInfo.DateTime, instance.Instance, null, instance.ErrorInfo.Exception));
}
else
foreach (KeyValuePair<string, QueryDatabaseResultInfo> databasePair in instance.DatabasesResult)
{
var database = databasePair.Value;
if (database.ErrorInfo != null)
{
this._errorItems.Add(new ErrorLogItem(database.ErrorInfo.DateTime, instance.Instance, database.QueryItem, database.ErrorInfo.Exception));
}
}
}
}
/// <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;
}
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 QueryResultInfo ExecuteQuery(
ConnectionGroupInfo connectionGroupInfo,
List<QueryInfo> queries,
QueryExecutionParams parameters,
int maxthreadCount,
ProgressItem progress = null
)
{
var result = new QueryResultInfo();
List<InstanceInfo> connections = connectionGroupInfo.Connections;
if (progress != null)
{
progress.SetPromisedChildCount(connections.Count);
}
ParallelOptions op = new ParallelOptions { MaxDegreeOfParallelism = (maxthreadCount == 0 ? Int32.MaxValue : maxthreadCount) };
Parallel.ForEach(connections, op, (connection) =>
{
var query = queries.FirstOrDefault(x => x.Source == (connection.Type) || x.Source == QuerySource.SQLite);
if (query != null)
{
ProgressItem subProgress = null;
if (progress != null)
{
subProgress = progress.GetChild();
}
result.AddInstanceResult(ExecuteQuery(connection, query, parameters, subProgress));
}
});
return result;
}
private QueryResultInfo ExecuteQuery(
GroupDefinition groupDefinition,
QueryInfo query,
QueryExecutionParams parameters,
ProgressItem progress = null
)
{
QueryResultInfo queryResult = new QueryResultInfo();
InstanceInfo instance = groupDefinition.Instance;
QueryExecutorFactory factory = new QueryExecutorFactory(
instance,
this.ExecuteQueryItem,
this.ExecuteSql
);
BaseQueryExecutor executor = factory.GetExecutor(
query.Scope
);
CurrentStorage storage = this._model.GetVaultProcessor(
instance.ConnectionGroup).CurrentStorage;
ServerProperties props = instance.InitServerProperties(storage);
QueryInstanceResultInfo instanceResult = executor.ExecuteQuerySimple(
query,
parameters,
props.Version,
progress,
groupDefinition
);
queryResult.AddInstanceResult(instanceResult);
return queryResult;
}
public QueryResultInfo ExecuteConnectionsSelectQuery(
TemplateNodeQueryInfo queryInfo,
ConnectionGroupInfo connectionGroup,
List<QueryInfo> queries,
int maxthreadCount
)
{
const string colConnectionName = "ConnectionName";
const string colConnectionType = "ConnectionType";
const string colConnectionIsOdbc = "IsOdbcConnection";
IStorageManager storageManager = this._model.GetVaultProcessor(connectionGroup);
CurrentStorage storage = storageManager.CurrentStorage;
TemplateNodeQueryInfo connectionsQueryInfo = queryInfo.GetParentConnectionSelectQuery();
List<QueryInfo> connectionsQueries = this._model.GetQueryByTemplateNodeQueryInfo(connectionsQueryInfo);
QueryResultInfo connectionsQueryResult = ExecuteQuery(
connectionGroup,
connectionsQueries,
QueryExecutionParams.CreateFrom(connectionsQueryInfo),
maxthreadCount
);
QueryResultInfo queryResult = new QueryResultInfo();
foreach (KeyValuePair<InstanceInfo, QueryInstanceResultInfo> resultInfo in connectionsQueryResult.InstancesResult)
{
QueryInstanceResultInfo instanceResult = resultInfo.Value;
if (instanceResult.ErrorInfo == null)
{
InstanceInfo mainConnection = resultInfo.Key;
Int64? parentQueryId = storage.QueryDirectory.GetQueryId(
queryInfo.TemplateNode,
queryInfo,
mainConnection,
DateTime.Now,
false
);
List<DynamicConnection> dynamicConnections = new List<DynamicConnection>();
foreach (KeyValuePair<string, QueryDatabaseResultInfo> databaseResultInfo in instanceResult.DatabasesResult)
{
QueryDatabaseResultInfo queryDatabaseResultInfo = databaseResultInfo.Value;
DataTable[] dataTables = queryDatabaseResultInfo.DataTables;
if (dataTables != null)
{
foreach (DataTable dataTable in dataTables)
{
bool colConnectionNameExists = dataTable.Columns.Contains(colConnectionName);
bool colConnectionTypeExists = dataTable.Columns.Contains(colConnectionType);
bool colConnectionOdbcExists = dataTable.Columns.Contains(colConnectionIsOdbc);
foreach (DataRow row in dataTable.Rows)
{
string connectionName = colConnectionNameExists ? row[colConnectionName].ToString() : string.Empty;
string connectionType = colConnectionTypeExists ? row[colConnectionType].ToString() : QuerySource.MSSQL.ToString();
bool isOdbcConnection = false;
if (colConnectionOdbcExists)
{
bool.TryParse(row[colConnectionIsOdbc].ToString(), out isOdbcConnection);
}
QuerySource sourceType;
if (!Enum.TryParse(connectionType, true, out sourceType))
{
sourceType = QuerySource.MSSQL;
}
InstanceInfo selectConnectionInstance = InstanceInfoResolver.ResolveDynamicInstance(
connectionName,
sourceType,
isOdbcConnection
);
selectConnectionInstance.ConnectionGroup = connectionGroup;
if (selectConnectionInstance.IsEnabled)
{
var query = queries.FirstOrDefault(
x => x.Source == (selectConnectionInstance.Type) || x.Source == QuerySource.SQLite
);
if (query == null)
{
continue;
}
InstanceVersion ver = selectConnectionInstance.InitServerProperties(
storage,
this._model.Settings.SqlTimeout
).Version;
QueryItemInfo queryItem = query.Items.GetQueryItemForVersion(ver);
QueryInstanceResultInfo instanceResultInfo = GetConnectionSelectResults(
selectConnectionInstance,
query,
queryItem,
queryInfo
);
queryResult.AddInstanceResult(instanceResultInfo);
Int64? dynamicQueryId = storage.QueryDirectory.GetQueryId(
queryInfo.TemplateNode,
queryInfo,
selectConnectionInstance,
DateTime.Now,
false
);
if (parentQueryId.HasValue && dynamicQueryId.HasValue)
{
DynamicConnection dynamicConnection = new DynamicConnection(
connectionName,
connectionType,
isOdbcConnection,
dynamicQueryId
);
dynamicConnections.Add(dynamicConnection);
}
}
}
}
}
}
if (dynamicConnections.Count > 0)
{
Int64 pqId = parentQueryId.Value;
DynamicConnectionDirectory dynamicConnectionDirectory = storage.DynamicConnectionDirectory;
dynamicConnectionDirectory.RemoveConnections(pqId);
dynamicConnectionDirectory.UpdateConnections(pqId, dynamicConnections);
}
}
}
return queryResult;
}
/// <summary>
/// Default constructor
/// </summary>
/// <param name="templateNodeInfo">Template node info</param>
/// <param name="queryResult">Query result</param>
public TemplateNodeResultItem(TemplateNodeQueryInfo templateNodeInfo, QueryResultInfo queryResult)
{
this._templateNodeInfo = templateNodeInfo;
this._queryResult = 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);
}