public static bool Execute(DbQuery query, out QueryOutput result)
{
try
{
result = QueryOutput.PermissionDenied;
using (var con = Repo.DB.SKtimeManagement)
{
var param = new DynamicParameters();
param.Add("loginID", query.LoginID, DbType.Int32);
param.Add("employeeID", query.EmployeeID, DbType.Int32);
param.Add("action", query.Action, DbType.String);
param.Add("query", query.Query, DbType.String);
param.Add("log", query.Log, DbType.Boolean);
param.Add("result", dbType: DbType.Int32, direction: ParameterDirection.Output);
con.Execute("sp_ExecuteQuery", param, null, 300, System.Data.CommandType.StoredProcedure);
result = param.Get <QueryOutput>("result");
}
return(result == QueryOutput.Success);
}
catch (Exception e)
{
result = QueryOutput.Error;
return(false);
}
}
public static List <T> Query <T>(DbQuery query, out QueryOutput result)
{
var data = new List <T>();
try
{
result = QueryOutput.PermissionDenied;
using (var con = Repo.DB.SKtimeManagement)
{
if (query.Log)
{
var param = new DynamicParameters();
param.Add("loginID", query.LoginID, DbType.Int32);
param.Add("employeeID", query.EmployeeID, DbType.Int32);
param.Add("action", query.Action, DbType.String);
param.Add("query", query.Query, DbType.String);
param.Add("log", query.Log, DbType.Boolean);
param.Add("result", dbType: DbType.Int32, direction: ParameterDirection.Output);
data = con.Query <T>("sp_ExecuteQuery", param, null, true, 300, System.Data.CommandType.StoredProcedure).ToList();
result = param.Get <QueryOutput>("result");
}
else
{
data = con.Query <T>(query.Query).ToList();
result = QueryOutput.Success;
}
}
return(data);
}
catch (Exception e)
{
result = QueryOutput.Error;
return(data);
}
}
/// <summary>
/// Raises the callbacks provided with an output.
/// </summary>
/// <param name="callbacks">The callbacks to be raised.</param>
/// <param name="output">The output to be passed to all the callbacks.</param>
private static void RaiseCallbacks(IEnumerable <QueryCallback> callbacks, QueryOutput output)
{
foreach (var callback in callbacks)
{
callback?.Invoke(output);
}
}
/// <summary>
/// Runs the selected query in the context of the specified Command asynchronously.
/// </summary>
/// <param name="query">The query to be ran.</param>
/// <param name="command">The command context to be used.</param>
/// <returns>The output after the query gets executed.</returns>
private async Task <QueryOutput> RunCommandAsync(Query query, MySqlCommand command)
{
var queryOutput = new QueryOutput(query, null);
command.CommandText = query.QueryString;
command.Parameters.Clear();
command.Parameters.AddRange(query.Parameters.ToArray());
switch (query.Type)
{
case EQueryType.NonQuery:
queryOutput.Output = await command.ExecuteNonQueryAsync();
break;
case EQueryType.Scalar:
queryOutput.Output = await command.ExecuteScalarAsync();
break;
case EQueryType.Reader:
var readerResult = new List <Row>();
using (var reader = await command.ExecuteReaderAsync())
{
try
{
while (await reader.ReadAsync())
{
var columns = new List <Column>();
for (var i = 0; i < reader.FieldCount; i++)
{
columns.Add(new Column(reader.GetName(i), reader.GetValue(i)));
}
readerResult.Add(new Row(columns));
}
}
finally
{
reader.Close();
}
}
queryOutput.Output = readerResult;
break;
}
RaiseCallbacks(query.Callbacks, queryOutput);
if (Configuration.UseCache && query.ShouldCache)
{
m_CacheManager.StoreUpdateItem(queryOutput);
}
return(queryOutput);
}
/// <summary>
/// Asynchronously queries the sqlCode to output
/// </summary>
/// <param name="sqlCode">use this format: UPDATE table.row SET var=?var WHERE var2=?var2</param>
/// <param name="output"></param>
/// <param name="p"></param>
/// <returns></returns>
public static async Task QueryAsync(string sqlCode, QueryOutput output, params object[] p)
{
if (instance.info == null)
{
return;
}
MySqlConnection connection = new MySqlConnection(instance.info.GetConnectionString());
List <MySqlParameter> parameters = new List <MySqlParameter>();
string curCode = sqlCode;
string prevCode = "";
// Get code ready to extract
while (curCode != prevCode)
{
prevCode = curCode;
curCode = curCode.Replace(" = ", "=");
curCode = curCode.Replace(" =", "=");
curCode = curCode.Replace("= ", "=");
}
List <string> splitSql = new List <string>();
splitSql.AddRange(curCode.Split(' '));
for (int i = 0; i < splitSql.Count; i++)
{
if (splitSql[i].Contains("?"))
{
string s = splitSql[i].Split('?')[1].Trim('?', ';', '(', ')', '=', ',');
if (parameters.Find(x => { return(x.ParameterName == s); }) == null)
{
parameters.Add(new MySqlParameter(
s,
p[parameters.Count]
));
}
}
}
await Task.Run(async() => await PollQuery(connection, curCode, parameters, output));
}
/// <summary>
/// Create a new query object.
/// </summary>
/// <param name="output">A QueryOutput object containing information necessary to perform output operations.</param>
/// <param name="interval">The interval at which to query</param>
/// <param name="name">The name of the query in the database</param>
/// <param name="database">The filepath of the database that contains this query</param>
/// <param name="viewName">The data view (query or table) to reference to point to in the database.</param>
/// <param name="transpose">Whether or not to transpose the contents of the query into the file. Default: false</param>
/// <param name="time">The time in the Interval to write this query. For Hourly, Time represents the minute. For Daily, Time represents the hour. Default: -1</param>
/// <param name="delimiter">The character(s) used to delimit values in this Query. Default: ","</param>
/// <param name="printHeaders">Whether or not the headers of this query should be printed at the top of the file.</param>
public Query(QueryOutput output, QueryInterval interval, string name, string database, string viewName = "", bool transpose = false, int time = -1, string delimiter = ",", bool printHeaders = false)
{
Output = output;
Interval = interval;
Name = name;
if (viewName == "")
{
DataView = name;
}
else
{
DataView = viewName;
}
Database = database;
Transpose = transpose;
Time = time;
Delimiter = delimiter;
PrintHeaders = printHeaders;
Paused = false;
}
public void BuildWithListResult()
{
_output = QueryOutput.List;
Build();
}
private void StartLoad(BackgroundWorker worker)
{
var useParams = false;
var badParams = new List <string>();
foreach (var theKey in _paramMappings.Keys)
{
if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0))
{
badParams.Add(theKey);
}
}
foreach (var theKey in badParams)
{
_paramMappings.Remove(theKey);
}
//Need some parameters?
if (_paramMappings.Count > 0)
{
ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings);
useParams = true;
}
//Initialize the connection pool
var conn = new SqlConnection(_connectionString);
//TODO: use this or not??
SqlConnection.ClearPool(conn);
conn.Open();
conn.Dispose();
//Spin up the load threads
for (var i = 0; i < _threads; i++)
{
conn = new SqlConnection(_connectionString);
//TODO: Figure out how to make this option work (maybe)
//conn.FireInfoMessageEventOnUserErrors = true;
SqlCommand statsComm = null;
var queryComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query
};
if (useParams)
{
queryComm.Parameters.AddRange(ParamServer.GetParams());
}
var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : string.Empty) + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : string.Empty);
if (setStatistics.Length > 0)
{
statsComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics
};
}
//Queue<queryOutput> queryOutInfo = new Queue<queryOutput>();
using var input = new QueryInput(statsComm, queryComm,
// this.queryOutInfo,
_iterations, _forceDataRetrieval, _queryDelay, worker, _killQueriesOnCancel, _threads);
var theThread = new Thread(input.StartLoadThread)
{
Priority = ThreadPriority.BelowNormal, IsBackground = true
};
theThread.Name = "thread: " + i;
_threadPool.Add(theThread);
_commandPool.Add(queryComm);
//queryOutInfoPool.Add(queryOutInfo);
}
// create a token source for the workers to be able to listen to a cancel event
using var workerCTS = new CancellationTokenSource();
_finishedThreads = 0;
for (var i = 0; i < _threads; i++)
{
_threadPool[i].Start(workerCTS.Token);
}
//Start reading the queue...
var cancelled = false;
while (!cancelled)
{
QueryOutput theOut = null;
try
{
// wait for OutInfo items in the queue or a user cancel event
theOut = QueryOutInfo.Take(_backgroundWorkerCTS.Token);
}
catch (Exception ex)
{
// The exception is InvalidOperationException if the threads are done
// and OperationCanceledException if the user clicked cancel.
// If it's OperationCanceledException, we need to cancel
// the worker threads and wait for them to exit
if (ex is OperationCanceledException)
{
workerCTS.Cancel();
foreach (var theThread in _threadPool)
{
// give the thread max 5 seconds to cancel nicely
if (!theThread.Join(5000))
{
theThread.Interrupt();
}
}
}
SqlConnection.ClearAllPools();
cancelled = true;
}
if (theOut != null)
{
//Report output to the UI
int finishedThreads = Interlocked.CompareExchange(ref _finishedThreads, 0, 0);
theOut.ActiveThreads = _threads - finishedThreads;
worker.ReportProgress((int)(_finishedThreads / (decimal)_threads * 100), theOut);
}
GC.Collect();
}
}
public void StartLoadThread(Object token)
{
bool runCancelled = false;
CancellationToken ctsToken = (CancellationToken)token;
try
{
ctsToken.Register(() =>
{
// Cancellation on the token will interrupt and cancel the thread
runCancelled = true;
_statsComm.Cancel();
_queryComm.Cancel();
});
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations && !runCancelled; i++)
{
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
if (conn != null)
{
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (!runCancelled && reader.Read())
{
//grab the first column to force the row down the pipe
// ReSharper disable once UnusedVariable
var x = reader[0];
Thread.Sleep(0);
}
} while (!runCancelled && reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception ex)
{
if (!runCancelled)
{
outException = ex;
}
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (conn != null)
{
if (_statsComm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
QueryOutInfo.Add(_outInfo);
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
if (!runCancelled)
{
try
{
if (_queryDelay > 0)
{
Task.Delay(_queryDelay, ctsToken).Wait();
}
}
catch (AggregateException ae)
{
ae.Handle((x) =>
{
if (x is TaskCanceledException)
{
runCancelled = true;
return(true);
}
// if we get here, the exception wasn't a cancel
// so don't swallow it
return(false);
});
}
}
}
}
}
catch (Exception)
{
if (runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
QueryOutInfo.Add(_outInfo);
}
}
Interlocked.Increment(ref _finishedThreads);
if (_finishedThreads == _numWorkerThreads)
{
// once all of the threads have exited, tell the other side that we're done adding items to the collection
QueryOutInfo.CompleteAdding();
}
}
public void StartLoadThread()
{
try
{
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations; i++)
{
if (_runCancelled)
throw new Exception();
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
var x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception e)
{
if (_runCancelled)
throw;
else
outException = e;
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (_statsComm != null)
conn.InfoMessage -= handler;
conn.Close();
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
queryOutput theout = new queryOutput(
outException,
sw.Elapsed,
finished,
(infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
Monitor.Pulse(QueryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
}
}
}
catch
{
if (_runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
}
}
else
throw;
}
}
/// <summary>
/// Queries the sqlCode to output
/// </summary>
/// <param name="sqlCode">use this format: UPDATE table.row SET var=?var WHERE var2=?var2</param>
/// <param name="output"></param>
public static void Query(string sqlCode, QueryOutput output, params object[] p)
{
if (instance.info == null)
{
return;
}
MySqlConnection connection = new MySqlConnection(instance.info.GetConnectionString());
List <MySqlParameter> parameters = new List <MySqlParameter>();
string curCode = sqlCode;
string prevCode = "";
// Get code ready to extract
while (curCode != prevCode)
{
prevCode = curCode;
curCode = curCode.Replace(" = ", "=");
curCode = curCode.Replace(" =", "=");
curCode = curCode.Replace("= ", "=");
}
List <string> splitSql = new List <string>();
splitSql.AddRange(curCode.Split(' '));
for (int i = 0; i < splitSql.Count; i++)
{
List <string> splitString = new List <string>();
splitString.AddRange(splitSql[i].Split('='));
if (splitString.Count > 1)
{
if (splitString[1].StartsWith("?"))
{
if (parameters.Find(x => { return(x.ParameterName == splitString[0]); }) == null)
{
parameters.Add(new MySqlParameter(splitString[0], p[parameters.Count]));
}
}
}
else
{
if (splitSql[i].Contains("?"))
{
splitString = new List <string>();
splitString.AddRange(splitSql[i].Split('?'));
string parameterName = splitString[1].Split(';')[0].TrimEnd(',', ')', ';');
if (parameters.Find(x => { return(x.ParameterName == parameterName); }) == null)
{
parameters.Add(new MySqlParameter(parameterName, p[parameters.Count]));
}
}
}
}
MySqlCommand command = connection.CreateCommand();
command.CommandText = curCode;
command.Parameters.AddRange(parameters.ToArray());
connection.Open();
bool hasRead = false;
if (output != null)
{
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Dictionary <string, object> outputdict = new Dictionary <string, object>();
for (int i = 0; i < r.VisibleFieldCount; i++)
{
outputdict.Add(r.GetName(i), r.GetValue(i));
}
output?.Invoke(new SqlQueryResponse(outputdict));
hasRead = true;
}
if (!hasRead)
{
output?.Invoke(null);
}
}
else
{
command.ExecuteNonQuery();
}
connection.Close();
}
internal static async Task PollQuery(MySqlConnection connection, string CommandText, List <MySqlParameter> parameters, QueryOutput output)
{
MySqlCommand command = connection.CreateCommand();
command.CommandText = CommandText;
command.Parameters.AddRange(parameters.ToArray());
connection.Open();
bool hasRead = false;
if (output != null)
{
MySqlDataReader r = await command.ExecuteReaderAsync() as MySqlDataReader;
Dictionary <string, object> outputdict = new Dictionary <string, object>();
while (await r.ReadAsync())
{
outputdict = new Dictionary <string, object>();
for (int i = 0; i < r.VisibleFieldCount; i++)
{
outputdict.Add(r.GetName(i), r.GetValue(i));
}
output?.Invoke(new SqlQueryResponse(outputdict));
hasRead = true;
}
if (!hasRead)
{
output?.Invoke(new SqlQueryResponse(outputdict));
}
}
else
{
await command.ExecuteNonQueryAsync();
}
await connection.CloseAsync();
}
/// <inheritdoc cref="ISearchStore{TDocument}.AdvancedQuery" />
public QueryOutput <TDocument> AdvancedQuery(AdvancedQueryInput input)
{
if (input == null)
{
throw new ArgumentNullException("input");
}
var apiInput = input.ApiInput;
/* Tri */
var sortDef = GetSortDefinition(input);
/* Requête de filtrage. */
var textSubQuery = GetTextSubQuery(input);
var securitySubQuery = GetSecuritySubQuery(input);
var facetSubQuery = GetFacetSelectionSubQuery(input);
var filterSubQuery = GetFilterSubQuery(input);
var conditionSubQuery = GetConditionSubQuery(input);
var customSubQuery = GetCustomSubQuery(input);
var filterQuery = _builder.BuildAndQuery(textSubQuery, securitySubQuery, facetSubQuery, filterSubQuery, conditionSubQuery, customSubQuery);
var hasFilter = !string.IsNullOrEmpty(filterQuery);
/* Facettage. */
var facetDefList = GetFacetDefinitionList(input);
var hasFacet = facetDefList.Any();
/* Group */
var groupFieldName = GetGroupFieldName(input);
var hasGroup = !string.IsNullOrEmpty(apiInput.Group);
/* Pagination. */
var skip = apiInput.Skip ?? 0;
var size = hasGroup ? 0 : apiInput.Top ?? 100000; // TODO Paramétrable ?
var res = this.GetClient()
.Search <TDocument>(s => {
s
/* Index / type document. */
.Index(_indexName)
.Type(_documentTypeName)
/* Pagination */
.From(skip)
.Size(size);
/* Tri */
if (sortDef.HasSort)
{
s.Sort(x => x
.OnField(sortDef.FieldName)
.Order(sortDef.Order));
}
/* Critère de filtrage. */
if (hasFilter)
{
s.Query(q =>
q.QueryString(qs => qs
.Query(filterQuery)));
}
/* Aggrégations. */
if (hasFacet || hasGroup)
{
s.Aggregations(a => {
if (hasFacet)
{
/* Facettage. */
foreach (var facetDef in facetDefList)
{
_handler.DefineAggregation(a, facetDef);
}
}
if (hasGroup)
{
/* Groupement. */
a.Terms(groupFieldName, st => st
.Field(groupFieldName)
.Size(10)
.Aggregations(g => g
.TopHits(_topHitName,
x => {
x.Size(10);
if (sortDef.HasSort)
{
x.Sort(t => t
.OnField(sortDef.FieldName)
.Order(sortDef.Order));
}
return(x);
})));
/* Groupement pour les valeurs nulles */
a.Missing(groupFieldName + MissingGroupPrefix, st => st
.Field(groupFieldName)
.Aggregations(g => g
.TopHits(_topHitName, x => x.Size(10))));
}
return(a);
});
}
return(s);
});
res.CheckStatus("AdvancedQuery");
/* Extraction des facettes. */
var facetListOutput = new List <FacetOutput>();
if (hasFacet)
{
var aggs = res.Aggs;
foreach (var facetDef in facetDefList)
{
FacetOutput facetOutput = _handler.ExtractFacetOutput(aggs, facetDef);
facetOutput.Code = facetDef.Name;
facetOutput.Label = facetDef.Label;
facetListOutput.Add(facetOutput);
}
}
/* Ajout des facettes manquantes */
if (input.ApiInput.Facets != null)
{
foreach (var facet in input.ApiInput.Facets)
{
FacetOutput facetOutput = facetListOutput.Where(f => f.Code == facet.Key).First();
if (!facetOutput.Values.Any(f => f.Code == facet.Value))
{
facetOutput.Values.Add(new Kinetix.ComponentModel.SearchV3.FacetItem {
Code = facet.Value,
Label = facetDefList.FirstOrDefault(fct => fct.Name == facet.Key)?.ResolveLabel(facet.Value),
Count = 0
});
}
}
}
/* Extraction des résultats. */
var resultList = new List <TDocument>();
var groupResultList = new List <GroupResult <TDocument> >();
if (hasGroup)
{
/* Groupement. */
var bucket = (Bucket)res.Aggregations[groupFieldName];
foreach (KeyItem group in bucket.Items)
{
var groupName = group.Key;
var topHitAgg = (TopHitsMetric)group.Aggregations[_topHitName];
var docs = topHitAgg.Documents <TDocument>().ToList();
groupResultList.Add(new GroupResult <TDocument>()
{
Code = groupName,
Label = facetDefList.FirstOrDefault(fct => fct.Name == apiInput.Group)?.ResolveLabel(groupName),
List = docs
});
}
/* Groupe pour les valeurs null. */
var nullBucket = (SingleBucket)res.Aggregations[groupFieldName + MissingGroupPrefix];
var nullTopHitAgg = (TopHitsMetric)nullBucket.Aggregations[_topHitName];
var nullDocs = nullTopHitAgg.Documents <TDocument>().ToList();
if (nullDocs.Any())
{
groupResultList.Add(new GroupResult <TDocument>()
{
Code = FacetConst.NullValue, List = nullDocs
});
}
resultList = null;
}
else
{
/* Liste unique. */
resultList = res.Documents.ToList();
groupResultList = null;
}
/* Construction de la sortie. */
var output = new QueryOutput <TDocument> {
List = resultList,
Facets = facetListOutput,
Groups = groupResultList,
Query = apiInput,
TotalCount = res.Total
};
return(output);
}
public void StartLoad(BackgroundWorker worker)
{
var useParams = false;
var badParams = new List <string>();
foreach (var theKey in _paramMappings.Keys)
{
if ((_paramMappings[theKey] == null) || (_paramMappings[theKey].Length == 0))
{
badParams.Add(theKey);
}
}
foreach (var theKey in badParams)
{
_paramMappings.Remove(theKey);
}
//Need some parameters?
if (_paramMappings.Count > 0)
{
ParamServer.Initialize(_paramQuery, _paramConnectionString, _paramMappings);
useParams = true;
}
//Initialize the connection pool
var conn = new SqlConnection(_connectionString);
//TODO: use this or not??
SqlConnection.ClearPool(conn);
conn.Open();
conn.Dispose();
//make sure the run cancelled flag is not set
QueryInput.RunCancelled = false;
//Spin up the load threads
for (var i = 0; i < _threads; i++)
{
conn = new SqlConnection(_connectionString);
//TODO: Figure out how to make this option work (maybe)
//conn.FireInfoMessageEventOnUserErrors = true;
SqlCommand statsComm = null;
var queryComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = _query
};
if (useParams)
{
queryComm.Parameters.AddRange(ParamServer.GetParams());
}
var setStatistics = (_collectIoStats ? @"SET STATISTICS IO ON;" : "") + (_collectTimeStats ? @"SET STATISTICS TIME ON;" : "");
if (setStatistics.Length > 0)
{
statsComm = new SqlCommand {
CommandTimeout = _commandTimeout, Connection = conn, CommandText = setStatistics
};
}
//Queue<queryOutput> queryOutInfo = new Queue<queryOutput>();
var input = new QueryInput(statsComm, queryComm,
// this.queryOutInfo,
_iterations, _forceDataRetrieval);
var theThread = new Thread(input.StartLoadThread)
{
Priority = ThreadPriority.BelowNormal
};
_threadPool.Add(theThread);
_commandPool.Add(queryComm);
//queryOutInfoPool.Add(queryOutInfo);
}
//Start the load threads
for (var i = 0; i < _threads; i++)
{
_threadPool[i].Start();
}
//Start reading the queue...
var finishedThreads = 0;
var cancelled = false;
while (finishedThreads < _threads)
{
// for (int i = 0; i < threads; i++)
// {
// try
// {
QueryOutput theOut = null;
//lock (queryOutInfoPool[i])
lock (QueryOutInfo)
{
//if (queryOutInfoPool[i].Count > 0)
//theOut = (queryOutput)queryOutInfoPool[i].Dequeue();
if (QueryOutInfo.Count > 0)
{
theOut = QueryOutInfo.Dequeue();
}
else
{
Monitor.Wait(QueryOutInfo);
}
}
if (theOut != null)
{
//Report output to the UI
worker.ReportProgress((int)(finishedThreads / (decimal)_threads * 100), theOut);
//TODO: Make this actually remove the queue from the pool so that it's not checked again -- maintain this with a bitmap, perhaps?
if (theOut.Finished)
{
finishedThreads++;
}
}
/* }
* catch (InvalidOperationException e)
* {
* }
*/
/*
* if (theOut != null)
* Thread.Sleep(200);
* else
* Thread.Sleep(10);
*/
// }
//TODO: Remove this ?
GC.Collect();
if (worker.CancellationPending && !cancelled)
{
QueryInput.RunCancelled = true;
//First, kill connections as fast as possible
SqlConnection.ClearAllPools();
//for each 20 threads, create a new thread dedicated
//to killing them
var threadNum = _threadPool.Count;
var killerThreads = new List <Thread>();
while (threadNum > 0)
{
var i = threadNum <= 20 ? 0 : threadNum - 20;
var killThreads = new Thread[threadNum - i < 1 ? threadNum : threadNum - i];
var killCommands = new SqlCommand[threadNum - i < 1 ? threadNum : threadNum - i];
_threadPool.CopyTo(i, killThreads, 0, killThreads.Length);
_commandPool.CopyTo(i, killCommands, 0, killCommands.Length);
for (var j = threadNum - 1; j >= i; j--)
{
_threadPool.RemoveAt(j);
_commandPool.RemoveAt(j);
}
var kill = new ThreadKiller(killThreads, killCommands);
var killer = new Thread(kill.KillEm);
killer.Start();
Thread.Sleep(0);
killerThreads.Add(killer);
threadNum = i;
}
//wait for the kill threads to return
//before exiting...
foreach (var theThread in killerThreads)
{
theThread.Join();
}
cancelled = true;
}
}
//clear any remaining messages -- these are almost certainly
//execeptions due to thread cancellation
//queryOutInfo.Clear();
}
public void BuildWithSingleResult()
{
_output = QueryOutput.Single;
Build();
}
/// <inheritdoc cref="ISearchStore{TDocument}.AdvancedQuery" />
public QueryOutput <TDocument> AdvancedQuery(AdvancedQueryInput input)
{
if (input == null)
{
throw new ArgumentNullException("input");
}
var apiInput = input.ApiInput;
/* Tri */
var sortDef = GetSortDefinition(input);
/* Requêtes de filtrage. */
var filterQuery = GetFilterQuery(input);
var hasFilter = !string.IsNullOrEmpty(filterQuery);
var postFilterQuery = GetPostFilterSubQuery(input);
var hasPostFilter = !string.IsNullOrEmpty(postFilterQuery);
/* Facettage. */
var facetDefList = GetFacetDefinitionList(input);
var hasFacet = facetDefList.Any();
var portfolio = input.Portfolio;
/* Group */
var groupFieldName = GetGroupFieldName(input);
var hasGroup = !string.IsNullOrEmpty(apiInput.Group);
/* Pagination. */
var skip = apiInput.Skip ?? 0;
var size = hasGroup ? 0 : apiInput.Top ?? 1000; // TODO Paramétrable ?
var res = this.GetClient()
.Search <TDocument>(s => {
s
/* Index / type document. */
.Index(_indexName)
.Type(_documentTypeName)
/* Pagination */
.From(skip)
.Size(size);
/* Tri */
if (sortDef.HasSort)
{
s.Sort(x => x
.Field(sortDef.FieldName, sortDef.Order));
}
/* Critère de filtrage. */
if (hasFilter)
{
s.Query(q => q.QueryString(qs => qs.Query(filterQuery)));
}
/* Critère de post-filtrage. */
if (hasPostFilter)
{
s.PostFilter(q => q.QueryString(qs => qs.Query(postFilterQuery)));
}
/* Aggrégations. */
if (hasFacet || hasGroup)
{
s.Aggregations(a => {
if (hasFacet)
{
/* Facettage. */
foreach (var facetDef in facetDefList)
{
GetHandler(facetDef).DefineAggregation(a, facetDef, facetDefList, input.ApiInput.Facets, portfolio);
}
}
if (hasGroup)
{
/* Groupement. */
a.Filter(GroupAggs, f => {
/* Critère de post-filtrage répété sur les groupes, puisque ce sont des agrégations qui par définition ne sont pas affectées par le post-filtrage. */
if (hasPostFilter)
{
f.Filter(q => q.QueryString(qs => qs.Query(postFilterQuery)));
}
return(f.Aggregations(aa => aa
/* Groupement. */
.Terms(groupFieldName, st => st
.Field(groupFieldName)
.Aggregations(g => g.TopHits(_topHitName, x => x.Size(input.GroupSize))))
/* Groupement pour les valeurs nulles */
.Missing(groupFieldName + MissingGroupPrefix, st => st
.Field(groupFieldName)
.Aggregations(g => g.TopHits(_topHitName, x => x.Size(input.GroupSize))))));
});
}
return(a);
});
}
return(s);
});
res.CheckStatus("AdvancedQuery");
/* Extraction des facettes. */
var facetListOutput = new List <FacetOutput>();
if (hasFacet)
{
var aggs = res.Aggs;
foreach (var facetDef in facetDefList)
{
facetListOutput.Add(new FacetOutput {
Code = facetDef.Code,
Label = facetDef.Label,
IsMultiSelectable = facetDef.IsMultiSelectable,
Values = GetHandler(facetDef).ExtractFacetItemList(aggs, facetDef, res.Total)
});
}
}
/* Ajout des valeurs de facettes manquantes (cas d'une valeur demandée par le client non trouvée par la recherche.) */
if (input.ApiInput.Facets != null)
{
foreach (var facet in input.ApiInput.Facets)
{
var facetItems = facetListOutput.Single(f => f.Code == facet.Key).Values;
/* On ajoute un FacetItem par valeur non trouvée, avec un compte de 0. */
foreach (var value in facet.Value)
{
if (!facetItems.Any(f => f.Code == value))
{
facetItems.Add(new FacetItem {
Code = value,
Label = facetDefList.FirstOrDefault(fct => fct.Code == facet.Key)?.ResolveLabel(value),
Count = 0
});
}
}
}
}
/* Extraction des résultats. */
var resultList = new List <TDocument>();
var groupResultList = new List <GroupResult <TDocument> >();
if (hasGroup)
{
/* Groupement. */
var bucket = (BucketAggregate)res.Aggs.Filter(GroupAggs).Aggregations[groupFieldName];
foreach (KeyedBucket <object> group in bucket.Items)
{
var list = ((TopHitsAggregate)group.Aggregations[_topHitName]).Documents <TDocument>().ToList();
groupResultList.Add(new GroupResult <TDocument> {
Code = group.Key.ToString(),
Label = facetDefList.First(f => f.Code == apiInput.Group).ResolveLabel(group.Key),
List = list,
TotalCount = (int)group.DocCount
});
}
/* Groupe pour les valeurs null. */
var nullBucket = (SingleBucketAggregate)res.Aggs.Filter(GroupAggs).Aggregations[groupFieldName + MissingGroupPrefix];
var nullTopHitAgg = (TopHitsAggregate)nullBucket.Aggregations[_topHitName];
var nullDocs = nullTopHitAgg.Documents <TDocument>().ToList();
if (nullDocs.Any())
{
groupResultList.Add(new GroupResult <TDocument> {
Code = FacetConst.NullValue,
Label = input.FacetQueryDefinition.FacetNullValueLabel ?? "focus.search.results.missing",
List = nullDocs,
TotalCount = (int)nullBucket.DocCount
});
}
resultList = null;
}
else
{
/* Liste unique. */
resultList = res.Documents.ToList();
groupResultList = null;
}
/* Construction de la sortie. */
var output = new QueryOutput <TDocument> {
List = resultList,
Facets = facetListOutput,
Groups = groupResultList,
Query = apiInput,
TotalCount = res.Total
};
return(output);
}
/// <summary>
/// Load a QueryList from a Queries.xml file.
/// </summary>
/// <param name="path">The path of the XML file for this list.</param>
/// <returns>A QueryList containing all queries in the file.</returns>
public static QueryList FromXML(string path = "Queries.xml")
{
QueryList queries = new QueryList(path);
XMLFile qFile = new XMLFile(path);
foreach (XMLSection section in qFile.GetSections()[0].GetSections())
{
if (!section.HasSections("output"))
{
continue;
}
XMLSection outputSection = section.GetSections("output")[0];
QueryOutput output = null;
switch (outputSection.Get("type").ToLower())
{
case "file":
string outputLocation = outputSection.Get("location").Replace('/', '\\');
outputLocation = Path.GetFullPath(outputLocation);
if (outputSection.HasValue("dateformat"))
{
DateTime date = DateTime.Now;
//if (interval == "daily") date = date.AddDays(-1);
string[] dateformat = outputSection.Get("dateformat").ToLower().Split(',');
StringBuilder formattedDate = new StringBuilder();
foreach (string f in dateformat)
{
switch (f)
{
case "dayofyear":
formattedDate.Append(date.DayOfYear.ToString("000"));
break;
default:
try {
formattedDate.Append(date.ToString(f));
} catch (FormatException) { continue; }
break;
}
}
outputLocation = outputLocation.Replace("*", formattedDate.ToString());
}
output = new QueryOutputFile(outputLocation);
break;
case "email":
output = new QueryOutputEmail(
outputSection.Get("from"),
outputSection.Get("to").Split(','),
outputSection.Get("subject"),
outputSection.Get("body"),
outputSection.Get("openers", false),
outputSection.Get("attachmentFilepath")
);
break;
case "ftp":
string filename = outputSection.Get("filename");
if (outputSection.HasValue("dateformat"))
{
DateTime date = DateTime.Now.AddDays(outputSection.Get("dateOffset", 0));
//if (interval == "daily") date = date.AddDays(-1);
string[] dateformat = outputSection.Get("dateformat").ToLower().Split(',');
StringBuilder formattedDate = new StringBuilder();
foreach (string f in dateformat)
{
switch (f)
{
case "dayofyear":
formattedDate.Append(date.DayOfYear.ToString("000"));
break;
default:
try {
formattedDate.Append(date.ToString(f));
} catch (FormatException) { continue; }
break;
}
}
filename = filename.Replace("*", formattedDate.ToString());
}
output = new QueryOutputFTP(
outputSection.Get("address"),
filename,
outputSection.Get("remotepath"),
outputSection.Get("username"),
outputSection.Get("password")
);
break;
}
if (output == null)
{
continue;
}
Query q = new Query(output, section.GetEnum <QueryInterval>("interval"), section.Get("name"), section.Get("database"), section.Get("dataview", ""), section.Get("transpose", false), section.Get("time", -1), section.Get("delimiter", ","), section.Get("printHeaders", false));
if (section.HasValue("queryStatement") && section.Get("queryStatement").Length > 0)
{
q.QueryStatement = section.Get("queryStatement");
}
if (section.HasValue("paused"))
{
q.Paused = section.Get("paused", true);
}
queries.Add(q);
}
return(queries);
}
public void StartLoadThread()
{
try
{
//do the work
using (var conn = _queryComm.Connection)
{
SqlInfoMessageEventHandler handler = GetInfoMessages;
for (var i = 0; i < _iterations; i++)
{
if (_runCancelled)
{
throw new Exception();
}
Exception outException = null;
try
{
//initialize the outInfo structure
_outInfo = new QueryOutput();
conn.Open();
//set up the statistics gathering
if (_statsComm != null)
{
_statsComm.ExecuteNonQuery();
Thread.Sleep(0);
conn.InfoMessage += handler;
}
//Params are assigned only once -- after that, their values are dynamically retrieved
if (_queryComm.Parameters.Count > 0)
{
ParamServer.GetNextRow_Values(_queryComm.Parameters);
}
_sw.Start();
//TODO: This could be made better
if (_forceDataRetrieval)
{
var reader = _queryComm.ExecuteReader();
Thread.Sleep(0);
do
{
Thread.Sleep(0);
while (reader.Read())
{
//grab the first column to force the row down the pipe
var x = reader[0];
Thread.Sleep(0);
}
} while (reader.NextResult());
}
else
{
_queryComm.ExecuteNonQuery();
Thread.Sleep(0);
}
_sw.Stop();
}
catch (Exception e)
{
if (_runCancelled)
{
throw;
}
else
{
outException = e;
}
if (_sw.IsRunning)
{
_sw.Stop();
}
}
finally
{
//Clean up the connection
if (_statsComm != null)
{
conn.InfoMessage -= handler;
}
conn.Close();
}
var finished = i == _iterations - 1;
//List<string> infoMessages = null;
//infoMessages = (stats_comm != null) ? theInfoMessages[connectionHashCode] : null;
/*
* queryOutput theout = new queryOutput(
* outException,
* sw.Elapsed,
* finished,
* (infoMessages == null || infoMessages.Count == 0) ? null : infoMessages.ToArray());
*/
_outInfo.E = outException;
_outInfo.Time = _sw.Elapsed;
_outInfo.Finished = finished;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
Monitor.Pulse(QueryOutInfo);
}
//Prep the collection for the next round
//if (infoMessages != null && infoMessages.Count > 0)
// infoMessages.Clear();
_sw.Reset();
}
}
}
catch
{
if (_runCancelled)
{
//queryOutput theout = new queryOutput(null, new TimeSpan(0), true, null);
_outInfo.Time = new TimeSpan(0);
_outInfo.Finished = true;
lock (QueryOutInfo)
{
QueryOutInfo.Enqueue(_outInfo);
}
}
else
{
throw;
}
}
}
/// <summary>
/// Deals with the event raised by the cache to update the requested element. A single access to the identifiable is needed.
/// </summary>
/// <param name="item">The element in cache to update.</param>
/// <param name="identifiable">The inner identifiable of the previous item.</param>
private void CacheItemUpdateRequested(CachedItem <QueryOutput> item, ref QueryOutput identifiable)
{
RequestCacheUpdate(identifiable.Query);
}
public virtual async Task QueryAsync(string text, QueryOutput output, params object[] parameters) => throw new NotImplementedException();
public async Task QueryAsync(string text, QueryOutput output, params object[] parameters)
{
await Sql.QueryAsync(text, output, parameters);
}