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 TemplateNodeInfo Instantiate(ConnectionData connection, DataRow row, TemplateNodeQueryInfo replicationSourceQuery, TemplateNodeInfo parent)
{
Debug.Assert(!IsInstance);
Debug.Assert(NeedDataRowToBeInstantiated);
var result = (TemplateNodeInfo)MemberwiseClone();
result.Childs = new List <TemplateNodeInfo>();
result.Template = this;
result.Attributes = new NodeAttirbutes(result, row);
result.Connection = connection;
result.ReplicationSourceQuery = replicationSourceQuery;
result.Queries = Queries.Select(q => q.Clone()).ToList();
result.ConnectionQueries = ConnectionQueries.Select(q => q.Clone()).ToList();
result.GroupQueries = GroupQueries.Select(q => q.Clone()).ToList();
result.Parent = parent;
result.Id = null;
result.Name = Name + result.Id;
result.OnAttributesChanged();
return(result);
}
public IEnumerable <TemplateNodeInfo> GetSubnodesFor(TemplateNodeQueryInfo groupQuery)
{
Debug.Assert(IsInstance);
Debug.Assert(GroupQueries.Contains(groupQuery));
return(Template.Childs.Where(t => t.GroupSelectId == groupQuery.Id));
}
public abstract long? GetQueryId( TemplateNodeInfo node, TemplateNodeQueryInfo templateNodeQuery, InstanceInfo instance, DateTime dateCreated, bool onlyFind );
protected override long? GetParameterId(
ConnectionGroupInfo connectionGroup,
TemplateNodeQueryInfo query,
string parameterName
)
{
return Storage.TemplateNodeQueryGroupParameterDirectory
.GetId(connectionGroup, query, parameterName);
}
/// <summary>
/// Get query info
/// </summary>
/// <param name="templateQueryInfo">Template node query info</param>
/// <returns>Query info</returns>
public List <QueryInfo> GetQueryByTemplateNodeQueryInfo(TemplateNodeQueryInfo templateQueryInfo)
{
log.InfoFormat("templateQueryInfo:QueryFileName:'{0}';QueryName:'{1}'",
templateQueryInfo.QueryFileName,
templateQueryInfo.QueryName
);
string key = templateQueryInfo.QueryFileName + "|" + templateQueryInfo.QueryName;
if (!this._queriesCache.Any(x => x.Item1 == key))
{
log.DebugFormat("QueryFileName:'{0}'",
FilesProvider.GetQueryFileName(
templateQueryInfo.QueryFileName,
templateQueryInfo.TemplateNode.ConnectionGroup.TemplateDir
)
);
List <QueryInfo> queries =
LoadQueries(FilesProvider.GetQueryFileName(
templateQueryInfo.QueryFileName,
templateQueryInfo.TemplateNode.ConnectionGroup.TemplateDir
),
templateQueryInfo.TemplateNode.ConnectionGroup.IsExternal
);
foreach (QueryInfo query in queries)
{
log.InfoFormat("query:name:'{0}';source:'{1}'",
query.Name,
query.Source
);
string curKey = templateQueryInfo.QueryFileName + "|" + query.Name;
if (!this._queriesCache.Any(x => x.Item1 == curKey && x.Item2.Source == query.Source))
{
this._queriesCache.Add(new Tuple <string, QueryInfo>(curKey, query));
}
}
}
if (!this._queriesCache.Any(x => x.Item1 == key))
{
throw new ArgumentException(
string.Format(
LocaleManager.GetLocalizedText(
LocaleManager.Exceptions,
"querynotfound"
),
key
)
);
}
return(this._queriesCache.Where(x => x.Item1 == key).Select(x => x.Item2).ToList());
}
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 DataTable[] GetGroupSelectResultsFromServer(
SqlProcessor sqlProcessor,
InstanceInfo serverInstance,
QueryInfo queryInfo,
TemplateNodeQueryInfo query
)
{
DataTable[] tables = null;
ErrorInfo error = null;
Debug.Assert(IsInstance);
Debug.Assert(GroupQueries.Contains(query));
var db = this._model.GetVaultProcessor(ConnectionGroup).CurrentStorage;
InstanceTemplate settings = GetUserSettings();
//looking for user settings for parameter values
query.ReadParametersFrom(settings);
try
{
tables = sqlProcessor.ExecuteSql(
serverInstance,
queryInfo,
GetDefaultDatabase(),
queryInfo.Parameters,
query.ParameterValues,
fromGroupSelect: true
);
}
catch (Exception ex)
{
error = new ErrorInfo(ex);
tables = null;
}
Int64?queryId = db.QueryGroupDirectory.GetQueryId(
query.TemplateNode,
query,
serverInstance,
DateTime.Now,
false
);
db.SaveMeta(queryId, error, tables);
if (tables != null && tables.Length < 0)
{
throw new InvalidTemplateException(query + " returned no recordsets.");
}
return(tables);
}
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)
);
}
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)
);
}
/// <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)
);
}
/// <summary>
/// Get query info
/// </summary>
/// <param name="templateQueryInfo">Template node query info</param>
/// <returns>Query info</returns>
public List<QueryInfo> GetQueryByTemplateNodeQueryInfo(TemplateNodeQueryInfo templateQueryInfo)
{
log.InfoFormat("templateQueryInfo:QueryFileName:'{0}';QueryName:'{1}'",
templateQueryInfo.QueryFileName,
templateQueryInfo.QueryName
);
string key = templateQueryInfo.QueryFileName + "|" + templateQueryInfo.QueryName;
if (!this._queriesCache.Any(x=>x.Item1 == key))
{
log.DebugFormat("QueryFileName:'{0}'",
FilesProvider.GetQueryFileName(
templateQueryInfo.QueryFileName,
templateQueryInfo.TemplateNode.ConnectionGroup.TemplateDir
)
);
List<QueryInfo> queries =
LoadQueries(FilesProvider.GetQueryFileName(
templateQueryInfo.QueryFileName,
templateQueryInfo.TemplateNode.ConnectionGroup.TemplateDir
),
templateQueryInfo.TemplateNode.ConnectionGroup.IsExternal
);
foreach (QueryInfo query in queries)
{
log.InfoFormat("query:name:'{0}';source:'{1}'",
query.Name,
query.Source
);
string curKey = templateQueryInfo.QueryFileName + "|" + query.Name;
if (!this._queriesCache.Any(x => x.Item1 == curKey && x.Item2.Source == query.Source))
{
this._queriesCache.Add(new Tuple<string, QueryInfo>(curKey, query));
}
}
}
if (!this._queriesCache.Any(x => x.Item1 == key))
{
throw new ArgumentException(
string.Format(
LocaleManager.GetLocalizedText(
LocaleManager.Exceptions,
"querynotfound"
),
key
)
);
}
return this._queriesCache.Where(x => x.Item1 == key).Select(x => x.Item2).ToList();
}
public TemplateNodeInfo Instantiate(ConnectionData connection, DataRow row, TemplateNodeQueryInfo replicationSourceQuery, TemplateNodeInfo parent)
{
Debug.Assert(!IsInstance);
Debug.Assert(NeedDataRowToBeInstantiated);
var result = (TemplateNodeInfo)MemberwiseClone();
result.Childs = new List<TemplateNodeInfo>();
result.Template = this;
result.Attributes = new NodeAttirbutes(result, row);
result.Connection = connection;
result.ReplicationSourceQuery = replicationSourceQuery;
result.Queries = Queries.Select(q => q.Clone()).ToList();
result.ConnectionQueries = ConnectionQueries.Select(q => q.Clone()).ToList();
result.GroupQueries = GroupQueries.Select(q => q.Clone()).ToList();
result.Parent = parent;
result.Id = null;
result.Name = Name + result.Id;
result.OnAttributesChanged();
return result;
}
public QueryDatabaseResult(
TemplateNodeQueryInfo templateNodeQueryInfo,
InstanceInfo instanceInfo,
QueryDatabaseResultInfo databaseResult)
{
this.TemplateNodeQuery = templateNodeQueryInfo;
this.Instance = instanceInfo;
this.Result = databaseResult;
}
protected abstract Int64? GetParameterId( ConnectionGroupInfo connectionGroup, TemplateNodeQueryInfo query, string parameterName );
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;
}
public DataTable[] GetGroupSelectResultsFromServer(
SqlProcessor sqlProcessor,
InstanceInfo serverInstance,
QueryInfo queryInfo,
TemplateNodeQueryInfo query
)
{
DataTable[] tables = null;
ErrorInfo error = null;
Debug.Assert(IsInstance);
Debug.Assert(GroupQueries.Contains(query));
var db = this._model.GetVaultProcessor(ConnectionGroup).CurrentStorage;
InstanceTemplate settings = GetUserSettings();
//looking for user settings for parameter values
query.ReadParametersFrom(settings);
try
{
tables = sqlProcessor.ExecuteSql(
serverInstance,
queryInfo,
GetDefaultDatabase(),
queryInfo.Parameters,
query.ParameterValues,
fromGroupSelect: true
);
}
catch (Exception ex)
{
error = new ErrorInfo(ex);
tables = null;
}
Int64? queryId = db.QueryGroupDirectory.GetQueryId(
query.TemplateNode,
query,
serverInstance,
DateTime.Now,
false
);
db.SaveMeta(queryId, error, tables);
if (tables != null && tables.Length < 0)
{
throw new InvalidTemplateException(query + " returned no recordsets.");
}
return tables;
}
private TemplateNodeResultItem ReadTemplateNodeResult(TemplateNodeQueryInfo templateNodeQueryInfo, MultyQueryResultInfo result)
{
var queryResult = new QueryResultInfo();
List<QueryInfo> queries = MsSqlAuditor.GetQueryByTemplateNodeQueryInfo(templateNodeQueryInfo);
// looking for user settings for parameter values
templateNodeQueryInfo.ReadParametersFrom(Settings);
string connectionsSelectId = templateNodeQueryInfo.ConnectionsSelectId;
if (connectionsSelectId != null)
{
foreach (InstanceInfo instance in this._instances)
{
Int64? queryId = Storage.QueryDirectory.GetQueryId(
templateNodeQueryInfo.TemplateNode,
templateNodeQueryInfo,
instance,
DateTime.Now,
false
);
if (queryId.HasValue)
{
List<DynamicConnection> connections = new List<DynamicConnection>(
Storage.DynamicConnectionDirectory.ReadConnections(queryId.Value)
);
foreach (DynamicConnection connection in connections)
{
if (!connection.QueryId.HasValue)
{
continue;
}
Int64 dynamicQueryId = connection.QueryId.Value;
QuerySource sourceType;
if (!Enum.TryParse(connection.Type, true, out sourceType))
{
_log.ErrorFormat(
@"Unknown ConnectionType:'{0}'",
connection.Type ?? "<Null>"
);
sourceType = QuerySource.MSSQL;
}
InstanceInfo selectConnectionInstance = InstanceInfoResolver.ResolveDynamicInstance(
connection.Name,
sourceType,
connection.IsOdbc
);
selectConnectionInstance.ConnectionGroup = instance.ConnectionGroup;
selectConnectionInstance.LoadServerProperties(Storage);
QueryInstanceResultInfo instanceResult = GetInstanceResult(
result,
selectConnectionInstance,
dynamicQueryId,
templateNodeQueryInfo,
queries
);
if (instanceResult != null)
{
queryResult.AddInstanceResult(instanceResult);
}
}
}
}
}
else
{
foreach (InstanceInfo instance in this._instances)
{
Int64? queryId = Storage.QueryDirectory.GetQueryId(
base.TemplateNode,
templateNodeQueryInfo,
instance,
new DateTime(),
true
);
if (queryId != null)
{
QueryInstanceResultInfo instanceResult = GetInstanceResult(
result,
instance,
queryId.Value,
templateNodeQueryInfo,
queries
);
if (instanceResult != null)
{
queryResult.AddInstanceResult(instanceResult);
}
}
}
}
Tuple<DateTime?, DateTime?> dateTimes =
Storage.NodeInstances.GetTreeNodeLastUpdateAndDuration(base.TemplateNode);
result.NodeLastUpdated = dateTimes.Item1;
result.NodeLastUpdateDuration = dateTimes.Item2;
return new TemplateNodeResultItem(templateNodeQueryInfo, queryResult);
}
private void 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
);
}
}
}
}
public IEnumerable<TemplateNodeInfo> GetSubnodesFor(TemplateNodeQueryInfo groupQuery)
{
Debug.Assert(IsInstance);
Debug.Assert(GroupQueries.Contains(groupQuery));
return Template.Childs.Where(t => t.GroupSelectId == groupQuery.Id);
}
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 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);
}
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);
}
}
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;
}
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;
}
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 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 override long? GetQueryId(
TemplateNodeInfo node,
TemplateNodeQueryInfo templateNodeQuery,
InstanceInfo instance,
DateTime dateCreated,
bool onlyFind
)
{
Int64? queryId = 0L;
Debug.Assert(node.IsInstance);
if (node.TemplateNodeId == null)
{
throw new InvalidOperationException();
}
Int64? templateNodeQueryId = Storage.TemplateNodeQueryDirectory
.GetId(node.ConnectionGroup, node.Template, templateNodeQuery);
Int64? serverId = Storage.ServerInstanceDirectory
.GetId(node.ConnectionGroup, instance);
if (onlyFind)
{
Tuple<long, long, long> key = new Tuple<Int64, Int64, Int64>(
(long) node.TemplateNodeId.Value,
(long) templateNodeQueryId,
(long) serverId
);
return this.Cache.GetOrAdd(
key, () =>
{
ITableRow row = this.NewRow();
row.Values.Add(NodeInstanceIdFn, node.TemplateNodeId.Value);
row.Values.Add(TemplateNodeQueryIdFn, templateNodeQueryId);
row.Values.Add(ServerInstanceIdFn, serverId);
queryId = this.GetRow(row);
Log.InfoFormat("ServerId:'{0}';NodeInstanceId:'{1}';TemplateNodeQueryId:'{2}';PrimaryKey:'{3}'",
serverId,
node.TemplateNodeId.Value,
templateNodeQueryId,
queryId
);
return queryId;
}
);
}
List<Field> customFields = new List<Field>
{
this.CreateField(DefaultDatabaseNameFn, node.GetDefaultDatabase()),
this.CreateField(TableDefinition.DateCreated, dateCreated),
this.CreateField(NodeInstanceIdFn, node.TemplateNodeId.GetValueOrDefault()),
this.CreateField(TemplateNodeQueryIdFn, templateNodeQueryId),
this.CreateField(ServerInstanceIdFn, serverId)
};
queryId = this.GetRecordIdByFields(customFields.ToArray());
Log.InfoFormat("ServerId:'{0}';NodeInstanceId:'{1}';TemplateNodeQueryId:'{2}';id:'{3}'",
serverId,
node.TemplateNodeId.GetValueOrDefault(),
templateNodeQueryId,
queryId
);
return queryId;
}
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 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;
}
/// <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 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;
}
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;
}