/// <summary>
/// Get Id for data
/// </summary>
/// <param name="connectionGroup">Connection group</param>
/// <param name="templateNode">Template node</param>
/// <returns></returns>
public Int64?GetId(ConnectionGroupInfo connectionGroup, TemplateNodeInfo templateNode)
{
Debug.Assert(!templateNode.IsInstance);
Int64?parentId = null;
if (templateNode.Parent != null)
{
parentId = this.GetId(connectionGroup, templateNode.Parent);
}
long?templateId = Storage.TemplateDirectory.GetId(connectionGroup);
TemplateNodeQueryInfo pq = templateNode.GetParentQuery();
object parentQueryId = pq != null
? (object)Storage.TemplateNodeQueryGroupDirectory.GetId(connectionGroup, pq)
: null;
return(this.GetRecordIdByFields(
this.CreateField(TemplateIdFn, templateId),
this.CreateField(ParentIdFn, parentId),
this.CreateField(UserIdFieldName, templateNode.Id),
this.CreateField(NameFn, templateNode.Name),
this.CreateField(IconFieldName, templateNode.IconImageReferenceName),
this.CreateField(ShowIfEmptyFieldName, !templateNode.HideEmptyResultDatabases),
this.CreateField(ShowNumberOfRecordsFieldName, templateNode.ShowNumberOfRecords),
this.CreateField(ParentQueryGroupIdFn, parentQueryId)
));
}
public static QueryExecutionParams CreateFrom(TemplateNodeQueryInfo templateNodeInfo)
{
return new QueryExecutionParams(
templateNodeInfo.ParameterValues,
templateNodeInfo.TemplateNode.GetDefaultDatabase()
);
}
public static QueryExecutionParams CreateFrom(TemplateNodeQueryInfo templateNodeInfo)
{
return(new QueryExecutionParams(
templateNodeInfo.ParameterValues,
templateNodeInfo.TemplateNode.GetDefaultDatabase()
));
}
public void ReadParameter(
ConnectionGroupInfo connectionGroup,
Int64 queryId,
TemplateNodeQueryInfo query,
ParameterValue paramValue
)
{
Int64?parameterId = GetParameterId(connectionGroup, query, paramValue.Name);
if (parameterId != null)
{
List <ITableRow> rows = this.GetRows(
QueryIdFn + " = " + queryId + " AND " + ParameterIdFn + " = " + parameterId
);
if (rows != null)
{
if (rows.Count != 0)
{
ITableRow paramRow = rows.ElementAt(0);
paramValue.UserValue = paramRow.GetValue <string>(ValueFn);
}
}
}
}
public string GetTableName(
TemplateNodeQueryInfo templateNodeQueryInfo,
Int64 recordSet
)
{
TemplateNodeInfo templateNode = templateNodeQueryInfo.TemplateNode;
Debug.Assert(templateNodeQueryInfo.TemplateNode.IsInstance);
long?id = this.CurrentStorage.TemplateNodeDirectory.GetId(
templateNode.ConnectionGroup,
templateNode.Template
);
string tableName = String.Format(
"t_{0}_{1}_{2}_q{3}_r{4}",
templateNode.TemplateId ?? String.Empty,
id,
templateNodeQueryInfo.QueryName,
templateNodeQueryInfo.Id ?? "0",
recordSet
);
// Log.InfoFormat("tableName:'{0}'",
// tableName
// );
return(tableName);
}
public abstract long?GetQueryId(
TemplateNodeInfo node,
TemplateNodeQueryInfo templateNodeQuery,
InstanceInfo instance,
DateTime dateCreated,
bool onlyFind
);
private static void GenerateResultDefinition(
XmlElement rootNode,
TemplateNodeQueryInfo templateNodeQueryInfo,
ErrorInfo errorInfo,
InstanceInfo instance,
long rowCount,
Int64 recordSets
)
{
string errorCode = string.Empty;
string errorNumber = "0";
XmlElement node = rootNode.OwnerDocument.CreateElement(QueryResultNodeName);
node.SetAttribute("instance", instance.Name);
node.SetAttribute("name", templateNodeQueryInfo.QueryName);
node.SetAttribute("RecordSets", recordSets.ToString());
node.SetAttribute("RowCount", rowCount.ToString());
if (errorInfo != null)
{
XmlElement errorNode = rootNode.OwnerDocument.CreateElement("SqlErrorMessage");
errorNode.InnerText = errorInfo.Message;
node.AppendChild(errorNode);
errorCode = errorInfo.Code;
errorNumber = errorInfo.Number;
}
node.SetAttribute("SqlErrorCode", errorCode);
node.SetAttribute("SqlErrorNumber", errorNumber);
node.SetAttribute("hierarchy", templateNodeQueryInfo.ResultHierarchy);
rootNode.AppendChild(node);
}
private DataTable[] GetDataTables(
Int64 recordSetCount,
Int64 queryId,
InstanceInfo instance,
TemplateNodeQueryInfo templateNodeQueryInfo
)
{
var dataTables = new DataTable[recordSetCount];
for (Int64 recordSet = 1L; recordSet <= recordSetCount; recordSet++)
{
NormalizeInfo db = StorageManager.GetDbStucture(templateNodeQueryInfo, recordSet, instance);
if (db != null)
{
if (this._histTable.ContainsKey(templateNodeQueryInfo))
{
dataTables[recordSet - 1L] = this._histTable[templateNodeQueryInfo];
}
else
{
dataTables[recordSet - 1L] = base.ReportStorage.ReadResult(db, queryId);
}
}
}
return(dataTables);
}
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);
}
protected override long?GetParameterId(
ConnectionGroupInfo connectionGroup,
TemplateNodeQueryInfo query,
string parameterName
)
{
return(Storage.TemplateNodeQueryParameterDirectory
.GetId(connectionGroup, query, parameterName));
}
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 QueryKey(TemplateNodeQueryInfo queryInfo) : this()
{
if (queryInfo == null)
{
throw new ArgumentNullException("queryInfo");
}
this._queryInfo = queryInfo;
}
public QueryKey(TemplateNodeQueryInfo queryInfo) : this()
{
if (queryInfo == null)
{
throw new ArgumentNullException("queryInfo");
}
this._queryInfo = queryInfo;
}
public Int64?GetId(ConnectionGroupInfo connectionGroup, TemplateNodeQueryInfo groupQuery, string parameterName)
{
long?queryId = Storage.TemplateNodeQueryGroupDirectory
.GetId(connectionGroup, groupQuery);
return(this.GetRecordIdByFields(
this.CreateField(TemplateNodeQueryGroupDirectory.TableName.AsFk(), queryId),
this.CreateField(ParameterNameFieldName, parameterName)
));
}
public Int64?GetId(ConnectionGroupInfo connectionGroup, TemplateNodeQueryInfo query, string parameterName)
{
TemplateNodeInfo templateNode = query.TemplateNode.IsInstance
? query.TemplateNode.Template
: query.TemplateNode;
long?queryId = Storage.TemplateNodeQueryDirectory
.GetId(connectionGroup, templateNode, query);
return(this.GetRecordIdByFields(
this.CreateField(TemplateNodeQueryDirectory.TableName.AsFk(), queryId),
this.CreateField(ParameterNameFieldName, parameterName)
));
}
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);
}
public Int64?GetId(ConnectionGroupInfo connectionGroup, TemplateNodeQueryInfo query)
{
Debug.Assert(query.TemplateNode.GroupQueries.Contains(query));
TemplateNodeInfo templateNode = query.TemplateNode.IsInstance
? query.TemplateNode.Template
: query.TemplateNode;
long?templateNodeId = Storage.TemplateNodeDirectory.GetId(connectionGroup, templateNode);
return(this.GetRecordIdByFields(
this.CreateField(TemplateNodeDirectory.TableName.AsFk(), templateNodeId),
this.CreateField(TemplateNodeQueryGroupIdFieldName, query.Id),
this.CreateField(TemplateNodeQueryGroupNameFieldName, query.QueryName),
this.CreateField(DefaultDatabaseFieldFn, query.DatabaseForChildrenFieldName)
));
}
public ITableRow ReadLastMeta(
TemplateNodeInfo node,
InstanceInfo instance,
TemplateNodeQueryInfo templateNodeQuery
)
{
Int64?queryId = this.QueryDirectory.GetQueryId(
node,
templateNodeQuery,
instance,
new DateTime(),
true
);
return(queryId != null
? this.ReadLastMeta(queryId.Value)
: null);
}
private void UpdateHistory(
InstanceInfo instance,
TemplateNodeQueryInfo templateNodeQuery,
long queryId
)
{
if (this.HistoryStorage != null && this._updateHistory)
{
List <QueryInfo> queries = Model.GetQueryByTemplateNodeQueryInfo(templateNodeQuery);
QueryInfo query = queries.FirstOrDefault(
x =>
x.Source == instance.Type
||
x.Source == QuerySource.SQLite
);
try
{
this.SaveHistoryData(
query,
instance,
queryId,
templateNodeQuery
);
}
catch (Exception ex)
{
Log.ErrorFormat("Update History exception:{0};query:{1}",
ex,
query
);
if (!ex.Data.Contains("IgnoreLog"))
{
Log.ErrorFormat("Update History exception:{0};query:{1}",
ex,
query
);
}
}
}
}
private Int64?SaveResults(
InstanceInfo instance,
TemplateNodeQueryInfo templateNodeQuery,
Int64 recordSet,
DataTable dataTable,
Int64?queryId
)
{
Int64?lSavedRows = 0L;
NormalizeInfo dbStructure = this.GetDbStucture(
templateNodeQuery,
recordSet,
instance,
dataTable
);
// Log.InfoFormat("Instance:'{0}';templateNode:'{1}';templateNodeQuery:'{2}';Table:'{3}';PrimaryKey:'{4}'",
// instance.Name,
// templateNode,
// templateNodeQuery,
// dbStructure.TableName,
// queryId
// );
if (queryId != null)
{
this.CurrentStorage.QueryResultsDirectory.GetId(
queryId.Value,
recordSet,
dbStructure.TableName
);
lSavedRows = this.ReportStorage.SaveResults(
queryId.Value,
dbStructure,
dataTable
);
}
return(lSavedRows);
}
/// <summary>
/// Get ID for data
/// </summary>
/// <param name="connectionGroup">Connection group</param>
/// <param name="templateNode">Template node</param>
/// <param name="templateNodeQuery">Template node query</param>
/// <returns></returns>
public Int64?GetId(
ConnectionGroupInfo connectionGroup,
TemplateNodeInfo templateNode,
TemplateNodeQueryInfo templateNodeQuery
)
{
long?templateNodeId = Storage.TemplateNodeDirectory
.GetId(connectionGroup, templateNode);
string id = templateNodeQuery.Id;
string name = templateNodeQuery.QueryName;
string hierarchy = templateNodeQuery.ResultHierarchy;
return(this.GetRecordIdByFields(
this.CreateField(TemplateNodeIdFieldName, templateNodeId),
this.CreateField(UserIdFieldName, id),
this.CreateField(NameFieldName, name),
this.CreateField(HierarchyFieldName, hierarchy)
));
}
private long SaveDatabaseResult(
TemplateNodeQueryInfo templateNodeQuery,
InstanceInfo instance,
QueryDatabaseResultInfo dbResult,
Int64?queryId
)
{
long totalRows = 0L;
Int64 recordSet = 1L;
foreach (DataTable table in dbResult.DataTables)
{
Debug.Assert(table != null);
long?savedRows = this.SaveResults(
instance,
templateNodeQuery,
recordSet,
table,
queryId
);
if (savedRows.HasValue)
{
totalRows += savedRows.Value;
}
recordSet++;
}
this.UpdateHistory(
instance,
templateNodeQuery,
queryId.Value
);
return(totalRows);
}
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));
}
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);
}
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 XmlDocument Transform(QueryResultDataSource dataSource)
{
GroupDefinition database = dataSource.NodeDefinition.Group;
MultyQueryResultInfo queriesResult = dataSource.QueriesResult;
MsSqlAuditorModel model = dataSource.Model;
if (queriesResult == null)
{
return(null);
}
try
{
DateTime timestamp = queriesResult.Timestamp;
XmlDocument xml = new XmlDocument();
xml.AppendChild(xml.CreateXmlDeclaration("1.0", "UTF-8", String.Empty));
XmlElement rootNode = xml.CreateElement(Consts.ResultsTag);
rootNode.SetAttribute(TimestampAttributeName, timestamp.ToInternetString());
xml.AppendChild(rootNode);
foreach (TemplateNodeResultItem tuple in queriesResult.List)
{
TemplateNodeQueryInfo templateNodeQueryInfo = tuple.TemplateNodeQuery;
QueryResultInfo queryResult = tuple.QueryResult;
foreach (
KeyValuePair <InstanceInfo, QueryInstanceResultInfo> instancePair in queryResult.InstancesResult)
{
InstanceInfo instance = instancePair.Key;
QueryInstanceResultInfo queryInstanceResult = instancePair.Value;
if (queryInstanceResult.ErrorInfo == null)
{
try
{
QueryDatabaseResultInfo databaseResult = queryInstanceResult.DatabasesResult[database.Name];
int rowCount = 0;
if (databaseResult != null)
{
rowCount = databaseResult.DataTables != null
? databaseResult.DataTables.Where(x => x != null).Select(x => x.Rows).Sum(x => x.Count)
: 0;
}
GenerateResultDefinition(
rootNode,
templateNodeQueryInfo,
databaseResult.ErrorInfo,
instance,
rowCount,
(databaseResult.DataTables != null ? databaseResult.DataTables.Length : 0)
);
}
catch (Exception ex)
{
log.Error("Error in 'Extracts data from queries and saves it to Xml-files'", ex);
}
}
else
{
GenerateResultDefinition(
rootNode,
templateNodeQueryInfo,
queryInstanceResult.ErrorInfo,
instance,
0,
0
);
}
}
}
foreach (TemplateNodeResultItem tuple in queriesResult.List)
{
TemplateNodeQueryInfo templateNodeQueryInfo = tuple.TemplateNodeQuery;
if (templateNodeQueryInfo.GetType() != typeof(TemplateNodeSqlGuardQueryInfo))
{
model.GetQueryByTemplateNodeQueryInfo(templateNodeQueryInfo);
}
QueryResultInfo queryResult = tuple.QueryResult;
foreach (
KeyValuePair <InstanceInfo, QueryInstanceResultInfo> instancePair in queryResult.InstancesResult)
{
InstanceInfo instance = instancePair.Key;
QueryInstanceResultInfo queryInstanceResult = instancePair.Value;
if (queryInstanceResult.ErrorInfo == null)
{
foreach (KeyValuePair <string, QueryDatabaseResultInfo> namedResult in queryInstanceResult.DatabasesResult)
{
if (namedResult.Key == database.Name)
{
XmlNode parent = rootNode.ChildNodes.OfType <XmlNode>().FirstOrDefault(
x =>
(
x.Attributes["instance"] != null &&
x.Attributes["instance"].Value == instance.Name
)
&&
(
x.Attributes["name"] != null &&
x.Attributes["name"].Value == templateNodeQueryInfo.QueryName
)
&&
(
x.Attributes["hierarchy"] != null &&
x.Attributes["hierarchy"].Value == (templateNodeQueryInfo.ResultHierarchy ?? string.Empty)
)
);
QueryDatabaseResultInfo databaseResult = namedResult.Value;
if (databaseResult.DataTables != null)
{
Int64 recordSet = 1L;
foreach (DataTable curTable in databaseResult.DataTables)
{
if (parent != null)
{
parent.InnerXml = parent.InnerXml +
ProcessDataTableAsStringXml(curTable, recordSet);
}
recordSet++;
}
}
if (databaseResult.QueryItem.ParentQuery.Scope == QueryScope.Database)
{
if (!string.IsNullOrEmpty(databaseResult.Database))
{
XmlAttribute attr = xml.CreateAttribute("database");
attr.Value = databaseResult.Database;
parent.Attributes.Append(attr);
}
if (!string.IsNullOrEmpty(databaseResult.DatabaseId))
{
XmlAttribute attr = xml.CreateAttribute("databaseId");
attr.Value = databaseResult.DatabaseId;
parent.Attributes.Append(attr);
}
}
else if (databaseResult.QueryItem.ParentQuery.Scope == QueryScope.InstanceGroup)
{
if (!string.IsNullOrEmpty(databaseResult.Database))
{
XmlAttribute attr = xml.CreateAttribute("InstanceGroupName");
attr.Value = databaseResult.Database;
parent.Attributes.Append(attr);
}
}
}
}
}
}
}
return(xml);
}
catch (Exception ex)
{
log.Error("Error in 'Extracts data from queries and saves it to Xml-files'", ex);
}
return(null);
}
public void Update(
ConnectionGroupInfo connectionGroup,
Int64 queryId,
TemplateNodeQueryInfo query,
List <ParameterValue> newValues
)
{
Dictionary <long, ITableRow> existingRows = new Dictionary <Int64, ITableRow>();
foreach (ITableRow row in this.GetRows(QueryIdFn + " = " + queryId))
{
existingRows[(Int64)row.Values[ParameterIdFn]] = row;
}
foreach (var value in newValues)
{
Int64?parameterId = GetParameterId(connectionGroup, query, value.Name);
if (parameterId != null)
{
existingRows.Remove((Int64)parameterId);
}
if (!this.ReadOnly)
{
List <Field> fields = new List <Field> {
this.CreateField(ValueFn, value.UserValue),
this.CreateField(IsEnabledFn, true),
this.CreateField(QueryIdFn, queryId),
this.CreateField(ParameterIdFn, parameterId)
};
ITableRow row = this.NewRow();
foreach (Field field in fields)
{
if (field != null)
{
row.Values.Add(field.Item1, field.Item2);
}
}
this.InsertOrUpdateRow(row, this.BeforeRowUpdate, this.BeforeRowAdd);
}
}
foreach (KeyValuePair <long, ITableRow> absent in existingRows)
{
string sqlQuery = string.Format(
"UPDATE {0} SET {1} = {2} WHERE [{4}] = {3} AND {1} != {2};",
GetTableName(),
IsEnabledFn,
SqLiteBool.ToBit(false),
absent.Key,
IdentityField
);
new SqlCustomCommand(this.Connection, sqlQuery)
.Execute(100);
}
}
protected abstract Int64?GetParameterId(
ConnectionGroupInfo connectionGroup,
TemplateNodeQueryInfo query,
string parameterName
);
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
);
}
}
public NormalizeInfo GetDbStucture(
TemplateNodeQueryInfo templateNodeQueryInfo,
Int64 recordSet,
InstanceInfo instance,
DataTable dataTable = null
)
{
List <QueryInfo> queries = Model.GetQueryByTemplateNodeQueryInfo(templateNodeQueryInfo);
QueryInfo query = queries.FirstOrDefault(x => x.Source == instance.Type || x.Source == QuerySource.SQLite);
string tableName = this.GetTableName(templateNodeQueryInfo, recordSet);
string tableIndexes = string.Empty;
if (query != null)
{
tableIndexes = query.QueryIndexFileds;
}
lock (this._dbStructures)
{
if (!this._dbStructures.ContainsKey(tableName))
{
NormalizeInfo definedRecordSet = query.GetDbStructureForRecordSet(recordSet);
if (definedRecordSet != null)
{
var clone = definedRecordSet.Clone();
clone.TableName = tableName;
this._dbStructures.Add(tableName, clone);
}
else
{
if (dataTable == null)
{
NormalizeInfo result = new NormalizeInfo();
NormalizeFieldInfo field = new NormalizeFieldInfo();
result.TableName = tableName;
result.Fields = new List <NormalizeFieldInfo>();
field.Name = "*";
field.Type = SqlDbType.NVarChar;
result.Fields.Add(field);
result.TableIndexFileds = tableIndexes;
return(result);
}
NormalizeInfo dbStructure = new NormalizeInfo
{
TableName = tableName,
IsAutoGenerated = true,
Fields = new List <NormalizeFieldInfo>(),
ChildDirectories = new List <NormalizeInfo>(),
RecordSet = recordSet,
QueryName = query.Name,
TableIndexFileds = tableIndexes
};
foreach (DataColumn column in dataTable.Columns)
{
NormalizeFieldInfo field = new NormalizeFieldInfo();
field.Name = column.ColumnName;
field.Type = column.DataType.ToSqlDbType();
field.IsUnique = true;
dbStructure.Fields.Add(field);
}
this._dbStructures.Add(tableName, dbStructure);
}
}
}
NormalizeInfo structure = this._dbStructures[tableName];
return(structure);
}
public override long?GetQueryId(
TemplateNodeInfo node,
TemplateNodeQueryInfo templateNodeQuery,
InstanceInfo instance,
DateTime dateCreated,
bool onlyFind
)
{
Int64?queryGroupId = 0L;
Debug.Assert(node.IsInstance);
if (node.TemplateNodeId == null)
{
throw new InvalidOperationException();
}
Int64?templateNodeQueryGroupId = Storage.TemplateNodeQueryGroupDirectory
.GetId(node.ConnectionGroup, templateNodeQuery);
Int64?loginId = Storage.LoginDirectory.GetId(instance);
Int64?serverId = Storage.ServerInstanceDirectory.GetId(
node.ConnectionGroup,
instance
);
if (onlyFind)
{
var key = new Tuple <Int64, Int64, Int64, Int64>(
(Int64)node.TemplateNodeId.Value,
(Int64)templateNodeQueryGroupId,
(Int64)loginId,
(Int64)serverId
);
return(base.Cache.GetOrAdd(
key,
() =>
{
ITableRow row = this.NewRow();
row.Values.Add(NodeInstanceIdFn, node.TemplateNodeId.Value);
row.Values.Add(TemplateNodeQueryGroupIdFn, templateNodeQueryGroupId);
row.Values.Add(ServerInstanceIdFn, serverId);
row.Values.Add(LoginIdFn, loginId);
queryGroupId = this.GetRow(row);
Log.InfoFormat("ServerId:'{0}';NodeInstanceId:'{1}';TemplateNodeQueryGroupId:'{2}';PrimaryKey:'{3}'",
serverId,
node.TemplateNodeId.Value,
templateNodeQueryGroupId,
queryGroupId
);
return queryGroupId;
}
));
}
List <Field> customFields = new List <Field>
{
this.CreateField(DefaultDatabaseNameFn, node.GetDefaultDatabase()),
this.CreateField(TableDefinition.DateCreated, dateCreated),
this.CreateField(NodeInstanceIdFn, node.TemplateNodeId.GetValueOrDefault()),
this.CreateField(TemplateNodeQueryGroupIdFn, templateNodeQueryGroupId),
this.CreateField(LoginIdFn, loginId),
this.CreateField(ServerInstanceIdFn, serverId)
};
queryGroupId = this.GetRecordIdByFields(customFields.ToArray());
Log.InfoFormat("ServerId:'{0}';NodeInstanceId:'{1}';TemplateNodeQueryGroupId:'{2}';PrimaryKey:'{3}'",
serverId,
node.TemplateNodeId.GetValueOrDefault(),
templateNodeQueryGroupId,
queryGroupId
);
return(queryGroupId);
}
public QueryKey()
{
this._queryInfo = null;
}
private KeyValuePair<QueryKey, IList<ParameterInfoLocalizable>> ParseQuery(TemplateNodeQueryInfo queryInfo)
{
var queryParameters = new List<ParameterInfoLocalizable>();
if (queryInfo is TemplateNodeSqlGuardQueryInfo)
{
var guardQueryInfo = queryInfo as TemplateNodeSqlGuardQueryInfo;
queryParameters.Add(new ParameterInfoLocalizable(queryInfo.IdsHierarchy,
ParameterInfoType.Attribute, "QueryCodeColumn", guardQueryInfo.QueryCodeColumn, null));
queryParameters.Add(new ParameterInfoLocalizable(queryInfo.IdsHierarchy,
ParameterInfoType.Attribute, "QueryObjectColumn", guardQueryInfo.QueryObjectColumn, null));
queryParameters.Add(new ParameterInfoLocalizable(queryInfo.IdsHierarchy,
ParameterInfoType.Attribute, "IncludedIssue", guardQueryInfo.IncludedIssue, null));
queryParameters.Add(new ParameterInfoLocalizable(queryInfo.IdsHierarchy,
ParameterInfoType.Attribute, "ExcludedIssue", guardQueryInfo.ExcludedIssue, null));
queryParameters.Add(new ParameterInfoLocalizable(queryInfo.IdsHierarchy,
ParameterInfoType.Attribute, "AddSummary", guardQueryInfo.AddSummary.ToString(), null));
}
foreach (ParameterValue paramValue in queryInfo.ParameterValues)
{
ParameterInfoLocalizable paramInfo = new ParameterInfoLocalizable(
queryInfo.IdsHierarchy,
ParameterInfoType.Parameter,
paramValue.Name,
paramValue.StringValue,
paramValue.UserValue,
paramValue.Locales
);
queryParameters.Add(paramInfo);
}
return new KeyValuePair<QueryKey, IList<ParameterInfoLocalizable>>(
new QueryKey(queryInfo),queryParameters);
}
public QueryKey()
{
this._queryInfo = null;
}