/// <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 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); }