/// <summary>
/// Default constructor
/// </summary>
/// <param name="date">DateTime of error</param>
/// <param name="instance">Server instance of error</param>
/// <param name="queryItem">Query</param>
/// <param name="error">Thrown exception</param>
public ErrorLogItem(DateTime date, InstanceInfo instance, QueryItemInfo queryItem, Exception error)
{
this._date = date;
this._instance = instance;
this._queryItem = queryItem;
this._error = error;
}
/// <summary>
/// Default constructor
/// </summary>
/// <param name="date">DateTime of error</param>
/// <param name="instance">Server instance of error</param>
/// <param name="queryItem">Query</param>
/// <param name="error">Thrown exception</param>
public ErrorLogItem(DateTime date, InstanceInfo instance, QueryItemInfo queryItem, Exception error)
{
this._date = date;
this._instance = instance;
this._queryItem = queryItem;
this._error = error;
}
/// <summary>
/// The prepare.
/// </summary>
/// <param name="queryItem">
/// The query item.
/// </param>
public void PrepareIfNeeds(QueryItemInfo queryItem)
{
if (!this.PreparationNeeded(queryItem))
{
return;
}
this.logger.WriteToLog("Вставка дополнительного SQL");
var additionalSql = this.settings.AdditionalSql.Replace("\r\n", string.Empty);
if (!additionalSql.EndsWith(";"))
{
additionalSql += ";";
}
queryItem.Text = additionalSql + queryItem.Text;
this.logger.WriteToLog("SQL вставлен");
}
/// <summary>
/// The sign query item.
/// </summary>
/// <param name="cryptoProcessor">The crypto processor.</param>
/// <param name="itemInfo">The item info.</param>
/// <param name="query">The query.</param>
private void SignQueryItem(CryptoProcessor cryptoProcessor, QueryItemInfo itemInfo, QueryInfo query)
{
this._logger.WriteToLog("prepare to sign the query....");
itemInfo.ParentQuery = query;
new QueryPreparator(this.Settings, this._logger).PrepareIfNeeds(itemInfo);
this._logger.WriteToLog("Sign query....");
itemInfo.Signature = cryptoProcessor.Sign(itemInfo.Text);
}
/// <summary>
/// The preparation nedeed.
/// </summary>
/// <returns>
/// The <see cref="bool" />.
/// </returns>
private bool PreparationNeeded(QueryItemInfo queryItem)
{
return (queryItem.ParentQuery.Source == QuerySource.MSSQL || queryItem.ParentQuery.Source == QuerySource.TDSQL) // #187
&& !string.IsNullOrEmpty(this.settings.AdditionalSql);
}
internal DataTable[] ExecuteSql(
InstanceInfo instance,
QueryItemInfo sql,
IEnumerable<QueryParameterInfo> parameters = null,
IEnumerable<ParameterValue> parameterValues = null
)
{
bool shouldExecute = true;
if (sql == null)
{
throw new Exception("There is no sql statement to execute (QueryItemInfo == null).");
}
// Log.InfoFormat("instance:'{0}';sql.Text:'{1}'",
// instance,
// sql.Text
// );
if (sql.ExecuteIfSqlText != null)
{
var clone = sql.Clone();
clone.ExecuteIfSqlText = null;
clone.Text = sql.ExecuteIfSqlText;
DataTable[] tt = ExecuteSql(
instance,
clone,
parameters,
parameterValues
);
if (tt.Length > 0 && tt[0].Rows.Count > 0)
{
shouldExecute = (int)(tt[0].Rows[0][0]) == 1;
}
}
if (shouldExecute)
{
return this.CurrentStorage.ExecuteSql(
sql.Text,
parameters,
parameterValues,
instance
);
}
return new DataTable[0];
}
private void SaveHistoryData(
QueryInfo query,
InstanceInfo instance,
long queryId,
TemplateNodeQueryInfo templateNodeQuery
)
{
QueryInfo queryInfo = new QueryInfo { Source = QuerySource.SQLite };
Regex regex = new Regex(@"\[\$\{(?<QueryName>[\w]+)\}\$_\$\{(?<RecordSetNumber>[\w]+)\}\$\]");
string strQueryName = String.Empty;
Int64 intRecordSetNumber = 0L;
string strReplacedTableName = String.Empty;
if (query.FillStatementList == null)
{
return;
}
List<HistoryFillStatement> list = query.FillStatementList.GetSortedStatements();
List<QueryParameterInfo> parameters = new List<QueryParameterInfo>
{
new QueryParameterInfo
{
Name = _QueryIdParameterName,
Type = SqlDbType.BigInt
}
};
List<ParameterValue> paramterValues = new List<ParameterValue>
{
new ParameterValue
{
Name = _QueryIdParameterName,
StringValue = queryId.ToString()
}
};
// string newTableName = String.Format("[{0}]",
// this.GetTableName(templateNodeQuery, recordSet) ?? "_unknown_table_"
// );
// string oldTableName = "[${" + query.Name + "}$_${" + recordSet + "}$]";
// string oldTableName = String.Format("[${{{0}}}$_${{{1}}}$]",
// query.Name ?? "_unknown_table_",
// recordSet
//);
// Log.DebugFormat("oldTableName:'{0}',newTableName:'{1}'",
// oldTableName,
// newTableName
//);
foreach (HistoryFillStatement statement in list)
{
QueryItemInfo queryItem = new QueryItemInfo();
queryItem.ParentQuery = queryInfo;
queryItem.Text = statement.Text;
// queryItem.Text = statement.Text.Replace(oldTableName, newTableName);
// Regex regex = new Regex("[\$\{([\w]+)\}\$_\$\{([\w]+)\}\$]");
// Log.InfoFormat("regex:'{0}'",
// regex
// );
var results = regex.Matches(statement.Text);
foreach (Match match in results)
{
// Log.InfoFormat("match:'{0}';match.Value:'{1}'",
// match,
// match.Value
// );
strQueryName = match.Groups["QueryName"].Value;
intRecordSetNumber = Int64.Parse(match.Groups["RecordSetNumber"].Value);
// Log.InfoFormat("strQueryName:'{0}';intRecordSetNumber:'{1}'",
// strQueryName,
// intRecordSetNumber
// );
if (String.Equals(strQueryName, query.Name, StringComparison.OrdinalIgnoreCase))
{
// Log.InfoFormat("matches:strQueryName:'{0}';query.Name:'{1}'",
// strQueryName,
// query.Name
// );
strReplacedTableName = string.Format(
"[{0}]",
this.GetTableName(
templateNodeQuery,
intRecordSetNumber
)
);
// Log.InfoFormat("strReplacedTableName:'{0}'",
// strReplacedTableName
// );
// Log.InfoFormat("queryItem.Text:'{0}'",
// queryItem.Text
// );
queryItem.Text = queryItem.Text.Replace(
match.Value,
strReplacedTableName
);
// Log.InfoFormat("queryItem.Text:'{0}'",
// queryItem.Text
// );
}
}
this.ExecuteSql(
instance,
queryItem,
parameters,
paramterValues
);
}
}
/// <summary>
/// Load queries with signature check
/// </summary>
/// <param name="filename">Xml-file name</param>
/// <param name="isExternal">Is opened from user file template</param>
/// <returns>Queries list</returns>
public List <QueryInfo> LoadQueries(string filename, bool isExternal)
{
log.InfoFormat("filename:'{0}',isExternal:'{1}'",
filename ?? "<null>",
isExternal
);
List <string> wrongQueries = new List <string>();
CryptoProcessor cryptoProcessor = null;
try
{
if (AppVersionHelper.IsNotDebug())
{
cryptoProcessor = new CryptoProcessor(
Settings.SystemSettings.PublicKeyXmlSign,
Settings.SystemSettings.PrivateKeyXmlDecrypt
);
}
}
catch (System.Security.XmlSyntaxException ex)
{
log.Error(ex);
}
catch (Exception ex)
{
log.Error(ex);
log.Error(ex.GetBaseException());
}
List <QueryInfo> queries = QueriesLoader.LoadFromXml(filename, cryptoProcessor, isExternal);
for (int i = queries.Count - 1; i >= 0; i--)
{
QueryInfo query = queries[i];
log.InfoFormat("query:'{0}'", query);
if (AppVersionHelper.IsNotDebug() && !isExternal)
{
for (int j = query.DatabaseSelect.Count - 1; j >= 0; j--)
{
QueryItemInfo queryItem = query.DatabaseSelect[j];
if (cryptoProcessor != null && !cryptoProcessor.Verify(queryItem.Text, queryItem.Signature))
{
if (!wrongQueries.Contains(query.Name))
{
wrongQueries.Add(query.Name);
}
query.DatabaseSelect.RemoveAt(j);
}
}
for (int j = query.Items.Count - 1; j >= 0; j--)
{
QueryItemInfo queryItem = query.Items[j];
log.InfoFormat("queryItem.Text:'{0}'", queryItem.Text);
if (cryptoProcessor != null && !cryptoProcessor.Verify(queryItem.Text, queryItem.Signature))
{
if (!wrongQueries.Contains(query.Name))
{
wrongQueries.Add(query.Name);
}
query.Items.RemoveAt(j);
}
}
}
if (query.Items.Count == 0)
{
queries.RemoveAt(i);
}
}
if ((Settings.WarnAboutUnsignedQuery) && (wrongQueries.Count > 0))
{
StringBuilder sb = new StringBuilder();
sb.Append(filename + Environment.NewLine + Environment.NewLine);
foreach (string wrongQuery in wrongQueries)
{
sb.Append(wrongQuery + Environment.NewLine);
}
MessageBox.Show(sb.ToString(), LocaleManager.GetLocalizedText(LocaleManager.Exceptions, "wrongQueriesSignatures"));
}
return(queries);
}
internal DataTable[] ExecuteSql(
InstanceInfo instance,
QueryItemInfo sql,
string database = null,
IEnumerable<QueryParameterInfo> parameters = null,
IEnumerable<ParameterValue> parameterValues = null,
ProgressItem progress = null,
bool fromGroupSelect = false
)
{
Exception gotException = null;
List<DataTable> tables = new List<DataTable>();
this._cancellationToken.ThrowIfCancellationRequested();
try
{
if (sql == null)
{
throw new Exception("There is no sql statement to execute (QueryItemInfo == null).");
}
List<Tuple<int, string>> parametersQueueForODBC;
using (IQueryConnection connection = _queryConnectionFactory.CreateQueryConnection(sql.ParentQuery.Source, instance))
{
using (IQueryCommand sqlCommand = connection.GetCommand(sql.Text, this._model.Settings.SqlTimeout, parameters, out parametersQueueForODBC))
{
using (new TryFinally(connection.Open, connection.Close))
{
connection.ChangeDatabase(database);
var shouldExecute = true;
if (sql.ExecuteIfSqlText != null)
{
var clone = sql.Clone();
clone.ExecuteIfSqlText = null;
clone.Text = sql.ExecuteIfSqlText;
DataTable[] tt = ExecuteSql(
instance,
clone,
database,
parameters,
parameterValues,
progress,
fromGroupSelect
);
if (tt.Length > 0 && tt[0].Rows.Count > 0)
{
shouldExecute = (int)(tt[0].Rows[0][0]) == 1;
}
}
if (shouldExecute)
{
var executionFinishedEvent = new AutoResetEvent(false);
IQueryCommand command = null;
Action<IAsyncResult> handleCallback = result =>
{
command = (IQueryCommand)result.AsyncState;
try
{
using (var reader = command.EndExecuteReader(result))
{
while (!reader.IsClosed)
{
DataTable table = new DataTable();
table.Load(reader, LoadOption.OverwriteChanges, ExecuteSqlFillErrorHandler);
tables.Add(table);
}
}
}
catch (Exception ex)
{
log.Error(ex);
gotException = ex;
if (fromGroupSelect)
{
log.ErrorFormat(
"Instance:'{0}';Authentication:'{1}';SQL:'{2}';Exception:'{3}'",
instance.Instance,
instance.Authentication,
sql,
ex
);
}
}
finally
{
if (command != null)
{
command.Cancel();
}
executionFinishedEvent.Set();
}
};
sqlCommand.AssignParameters(parameters, parameterValues, parametersQueueForODBC);
var callback = new AsyncCallback(handleCallback);
var asyncResult = sqlCommand.BeginExecuteReader(callback);
if (WaitHandle.WaitAny(new[] { asyncResult.AsyncWaitHandle, this._cancellationToken.WaitHandle }) == 1)
{
if (command != null)
{
command.Cancel();
}
this._cancellationToken.ThrowIfCancellationRequested();
}
executionFinishedEvent.WaitOne();
}
}
}
}
}
catch (Exception ex)
{
log.Error(ex);
if (gotException == null)
{
gotException = ex;
if (fromGroupSelect)
{
log.ErrorFormat(
"Instance:'{0}';Authentication:'{1}';Exception:'{2}'",
instance.Instance,
instance.Authentication,
ex
);
}
}
}
finally
{
if (progress != null)
{
progress.SetProgressDone();
}
}
if (gotException != null)
{
if (!fromGroupSelect)
{
gotException.Data.Add("IgnoreLog", true);
}
throw gotException;
}
return tables.ToArray();
}
private QueryDatabaseResultInfo ExecuteQueryItem(
InstanceInfo connection,
QueryItemInfo queryItem,
string database,
string databaseId,
IEnumerable<QueryParameterInfo> parameters,
IEnumerable<ParameterValue> parameterValues,
ProgressItem progress = null
)
{
try
{
var table = ExecuteSql(
connection,
queryItem,
database,
parameters,
parameterValues,
progress
);
return new QueryDatabaseResultInfo(table, queryItem, database, databaseId);
}
catch (OperationCanceledException ex)
{
return new QueryDatabaseResultInfo(new ErrorInfo(ex), queryItem, database, databaseId);
}
catch (Exception ex)
{
log.Error(ex);
if (!ex.Data.Contains("IgnoreLog"))
{
log.Error(queryItem.ToString() + Environment.NewLine
+ "connection.Instance=" + connection.Instance +
Environment.NewLine
+ " database=" + database + Environment.NewLine
+ " connection.Authentication.Username=" +
connection.Authentication.Username, ex);
}
if (progress != null)
{
progress.SetProgress(100);
}
return new QueryDatabaseResultInfo(new ErrorInfo(ex), queryItem, database, databaseId);
}
}
public QueryInstanceResultInfo GetConnectionSelectResults(
InstanceInfo selectConnectionInstance,
QueryInfo queryInfo,
QueryItemInfo queryItem,
TemplateNodeQueryInfo query
)
{
QueryDatabaseResultInfo dbResultInfo;
try
{
DataTable[] tables = ExecuteSql(
selectConnectionInstance,
queryItem,
query.TemplateNode.GetDefaultDatabase(),
queryInfo.Parameters,
query.ParameterValues
);
if (tables != null && tables.Length < 0)
{
throw new InvalidTemplateException(query + " returned no recordsets.");
}
dbResultInfo = new QueryDatabaseResultInfo(tables, queryItem);
}
catch (Exception exc)
{
ErrorInfo error = new ErrorInfo(exc);
dbResultInfo = new QueryDatabaseResultInfo(error, queryItem);
}
QueryInstanceResultInfo instanceResultInfo = new QueryInstanceResultInfo(selectConnectionInstance);
instanceResultInfo.AddDatabaseResult(dbResultInfo);
return instanceResultInfo;
}
/// <summary>
/// Constructor for thrown exception
/// </summary>
/// <param name="errorInfo">Error info</param>
/// <param name="queryItem">Query item</param>
/// <param name="databaseId">Database name (null for instance scope)</param>
public QueryDatabaseResultInfo(ErrorInfo errorInfo, QueryItemInfo queryItem, string database = "", string databaseId = "")
{
this.QueryItem = queryItem;
this._errorInfo = errorInfo;
this._database = database;
this._databaseId = databaseId;
}
/// <summary>
/// Default constructor
/// </summary>
/// <param name="dataTable">Result datatable</param>
/// <param name="queryItem">Query item</param>
/// <param name="database">Database name (null for instance scope)</param>
/// <param name="databaseId">Database Id</param>
public QueryDatabaseResultInfo(DataTable[] dataTable, QueryItemInfo queryItem, string database = "", string databaseId = "")
{
this.QueryItem = queryItem;
this._dataTables = dataTable;
this._database = database;
this._databaseId = databaseId;
}
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);
}
