public QueryPage()
{
this.InitializeComponent();
this.viewModel = new AdminQueryViewModel();
this.DataContext = this.viewModel;
this.setDateText();
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageIndexes))
{
return(Unauthorized());
}
if (!_luceneIndexProvider.Exists(model.IndexName))
{
return(NotFound());
}
if (String.IsNullOrWhiteSpace(model.Query))
{
return(View(model));
}
var luceneSettings = await _luceneIndexingService.GetLuceneSettingsAsync();
var queryParser = new QueryParser(LuceneSettings.DefaultVersion, "", new StandardAnalyzer(LuceneSettings.DefaultVersion));
var query = queryParser.Parse(model.Query);
_luceneIndexProvider.Search(model.IndexName, searcher =>
{
var docs = searcher.Search(query, 10);
model.Documents = docs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)).ToList();
});
return(View(model));
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageSqlQueries))
{
return(Forbid());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
var dialect = SqlDialectFactory.For(connection);
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var templateContext = _liquidTemplateManager.Context;
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, NullEncoder.Default);
model.FactoryName = _store.Configuration.ConnectionFactory.GetType().FullName;
if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages))
{
model.RawSql = rawQuery;
model.Parameters = JsonConvert.SerializeObject(parameters, Formatting.Indented);
try
{
using (connection)
{
await connection.OpenAsync();
model.Documents = await connection.QueryAsync(rawQuery, parameters);
}
}
catch (Exception e)
{
ModelState.AddModelError("", S["An error occurred while executing the SQL query: {0}", e.Message]);
}
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageGraphQLQueries))
{
return(Forbid());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
model.Parameters = JsonConvert.SerializeObject(parameters, Formatting.Indented);
try
{
var gqlQueryResult = await _queryService.ExecuteQuery(model.DecodedQuery, parameters);
model.RawGraphQL = gqlQueryResult.TokenizedQuery;
if (gqlQueryResult.Result.Errors?.Count > 0)
{
foreach (var error in gqlQueryResult.Result.Errors)
{
ModelState.AddModelError("", error.Message);
}
}
model.Documents = new List <JObject>
{
JObject.FromObject(gqlQueryResult.Result)
};
model.Elapsed = stopwatch.Elapsed;
}
catch (Exception ex)
{
ModelState.AddModelError("", S["An error occurred while executing the GraphQL query: {0}", ex.Message]);
}
return(View(model));
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageSqlQueries))
{
return(Unauthorized());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
var dialect = SqlDialectFactory.For(connection);
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var tokenizedQuery = _tokenizer.Tokenize(model.DecodedQuery, parameters);
if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, out var rawQuery, out var rawParameters, out var messages))
{
model.RawSql = rawQuery;
model.RawParameters = JsonConvert.SerializeObject(rawParameters);
try
{
using (connection)
{
connection.Open();
model.Documents = await connection.QueryAsync(rawQuery, rawParameters);
}
}
catch (Exception e)
{
ModelState.AddModelError("", _stringLocalizer["An error occured while executing the SQL query: {0}", e.Message]);
}
}
public async Task <IActionResult> Query(AdminQueryViewModel model, [FromServices] ITokenizer tokenizer)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageIndexes))
{
return(Unauthorized());
}
model.Indices = _luceneIndexManager.List().ToArray();
// Can't query if there are no indices
if (model.Indices.Length == 0)
{
return(RedirectToAction("Index"));
}
if (String.IsNullOrEmpty(model.IndexName))
{
model.IndexName = model.Indices[0];
}
if (!_luceneIndexManager.Exists(model.IndexName))
{
return(NotFound());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var luceneSettings = await _luceneIndexingService.GetLuceneSettingsAsync();
var stopwatch = new Stopwatch();
stopwatch.Start();
await _luceneIndexManager.SearchAsync(model.IndexName, async searcher =>
{
var analyzer = _luceneAnalyzerManager.CreateAnalyzer("standardanalyzer");
var context = new LuceneQueryContext(searcher, LuceneSettings.DefaultVersion, analyzer);
var tokenizedContent = tokenizer.Tokenize(model.DecodedQuery, JObject.Parse(model.Parameters));
try
{
var parameterizedQuery = JObject.Parse(tokenizedContent);
var docs = await _queryService.SearchAsync(context, parameterizedQuery);
model.Documents = docs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)).ToList();
}
catch (Exception e)
{
Logger.LogError("Error while executing query: {0}", e.Message);
ModelState.AddModelError(nameof(model.DecodedQuery), "Invalid query");
}
stopwatch.Stop();
model.Elapsed = stopwatch.Elapsed;
});
return(View(model));
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageIndexes))
{
return(Forbid());
}
model.Indices = (await _luceneIndexSettingsService.GetSettingsAsync()).Select(x => x.IndexName).ToArray();
// Can't query if there are no indices
if (model.Indices.Length == 0)
{
return(RedirectToAction("Index"));
}
if (String.IsNullOrEmpty(model.IndexName))
{
model.IndexName = model.Indices[0];
}
if (!_luceneIndexManager.Exists(model.IndexName))
{
return(NotFound());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
await _luceneIndexManager.SearchAsync(model.IndexName, async searcher =>
{
var analyzer = _luceneAnalyzerManager.CreateAnalyzer(await _luceneIndexSettingsService.GetIndexAnalyzerAsync(model.IndexName));
var context = new LuceneQueryContext(searcher, LuceneSettings.DefaultVersion, analyzer);
var templateContext = _liquidTemplateManager.Context;
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedContent = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, _javaScriptEncoder);
try
{
var parameterizedQuery = JObject.Parse(tokenizedContent);
var luceneTopDocs = await _queryService.SearchAsync(context, parameterizedQuery);
if (luceneTopDocs != null)
{
model.Documents = luceneTopDocs.TopDocs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)).ToList();
model.Count = luceneTopDocs.Count;
}
}
catch (Exception e)
{
_logger.LogError(e, "Error while executing query");
ModelState.AddModelError(nameof(model.DecodedQuery), S["Invalid query : {0}", e.Message]);
}
stopwatch.Stop();
model.Elapsed = stopwatch.Elapsed;
});
return(View(model));
}
public async Task <IActionResult> SearchIndex()
{
var select = Request.Query["select"];
var content = Request.Query["content"];
var model = new AdminQueryViewModel
{
DecodedQuery = "SELECT * FROM ContentItemIndex",
Parameters = "{}"
};
if (select == "all")
{
model.DecodedQuery = "SELECT * FROM Document where " +
"Type=\'OrchardCore.ContentManagement.ContentItem, OrchardCore.ContentManagement.Abstractions\'" +
"and Content LIKE \'%Published\":true%\'" +
"and Content LIKE \'%ContentType_%\' " +
"and Content LIKE \'%Title%\' " +
"and Content LIKE \'%Body%\'" +
"and Content LIKE \'%" +
content +
"%\'";
}
if (select == "zhengwen")
{
}
if (select == "biaoti")
{
}
var stopwatch = new Stopwatch();
stopwatch.Start();
var connection = _store.Configuration.ConnectionFactory.CreateConnection();
var dialect = SqlDialectFactory.For(connection);
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var templateContext = new TemplateContext();
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, templateContext);
if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, out var rawQuery, out var rawParameters, out var messages))
{
model.RawSql = rawQuery;
model.RawParameters = JsonConvert.SerializeObject(rawParameters);
try
{
using (connection)
{
connection.Open();
model.Documents = await connection.QueryAsync(rawQuery, rawParameters);
}
}
catch (Exception e)
{
ModelState.AddModelError("", _stringLocalizer["An error occured while executing the SQL query: {0}", e.Message]);
}
}
public async Task <LuceneQueryResults> Query(AdminQueryViewModel model, Boolean returnContentItems)
{
//if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageIndexes))
//{
// return Unauthorized();
//}
// model.Indices = _luceneIndexManager.List().ToArray();
model.Indices = (await _luceneIndexSettingsService.GetSettingsAsync()).Select(x => x.IndexName).ToArray();
// Can't query if there are no indices
//if (model.Indices.Length == 0)
//{
// return RedirectToAction("Index");
//}
if (String.IsNullOrEmpty(model.IndexName))
{
model.IndexName = model.Indices[0];
}
//if (!_luceneIndexManager.Exists(model.IndexName))
//{
// return NotFound();
//}
//if (String.IsNullOrWhiteSpace(model.DecodedQuery))
//{
// return View(model);
//}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
// var luceneSettings = await _luceneIndexingService.GetLuceneSettingsAsync();
// var stopwatch = new Stopwatch();
//stopwatch.Start();
var luceneQueryResults = new LuceneQueryResults();
await _luceneIndexManager.SearchAsync(model.IndexName, async searcher =>
{
var analyzer = _luceneAnalyzerManager.CreateAnalyzer("standardanalyzer");
var context = new LuceneQueryContext(searcher, LuceneSettings.DefaultVersion, analyzer);
var templateContext = _liquidTemplateManager.Context;// new TemplateContext();
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedContent = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, NullEncoder.Default, templateContext);
try
{
var parameterizedQuery = JObject.Parse(tokenizedContent);
var docs = await _queryService.SearchAsync(context, parameterizedQuery);
// model.Documents = docs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)).ToList();
if (returnContentItems)
{
// Load corresponding content item versions
var contentItemVersionIds = docs.TopDocs.ScoreDocs.Select(x => searcher.Doc(x.Doc).Get("Content.ContentItem.ContentItemVersionId")).ToArray();
var contentItems = await _session.Query <ContentItem, ContentItemIndex>(x => x.ContentItemVersionId.IsIn(contentItemVersionIds)).ListAsync();
// Reorder the result to preserve the one from the lucene query
var indexed = contentItems.ToDictionary(x => x.ContentItemVersionId, x => x);
luceneQueryResults.Items = contentItemVersionIds.Select(x => indexed[x]).ToArray();
}
else
{
var results = new List <JObject>();
foreach (var document in docs.TopDocs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)))
{
results.Add(new JObject(document.Select(x => new JProperty(x.Name, x.GetStringValue()))));
}
luceneQueryResults.Items = results;
}
}
catch (Exception e)
{
Logger.LogError(e, "Error while executing query");
// ModelState.AddModelError(nameof(model.DecodedQuery), "Invalid query");
}
// stopwatch.Stop();
// model.Elapsed = stopwatch.Elapsed;
});
return(luceneQueryResults);
}
public async Task <IActionResult> ContentItemQuery(AdminQueryViewModel model)
{
// if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageSqlQueries))
// {
// return Unauthorized();
// }
// if (String.IsNullOrWhiteSpace(model.Query))
// {
// return View(model);
// }
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
// var connection = _store.Configuration.ConnectionFactory.CreateConnection();
// var dialect = SqlDialectFactory.For(connection);
//load original json doc
var querySource = ((Queries.Services.ContentItemQuery)(await _queryManager.GetQueryAsync(model.DecodedQuery)));
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var templateContext = _liquidTemplateManager.Context;
// await templateContext.ContextualizeAsync( _serviceProvider);
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, NullEncoder.Default, templateContext);
model.FactoryName = _store.Configuration.ConnectionFactory.GetType().FullName;
// if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages))
// {
// model.RawSql = rawQuery;
model.Parameters = JsonConvert.SerializeObject(parameters, Formatting.Indented);
try
{
// using (connection)
// {
// connection.Open();
// model.Documents = await connection.QueryAsync(rawQuery, parameters);
// }
model.ContentItems = await _contentItemService.GetResults(querySource);
}
catch (Exception e)
{
ModelState.AddModelError("", _stringLocalizer["An error occured while executing the Content Items query: {0}", e.Message]);
}
model.Elapsed = stopwatch.Elapsed;
return(View(model));
}
public async Task <IActionResult> Query(AdminQueryViewModel model)
{
if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageIndexes))
{
return(Unauthorized());
}
//model.Indices = _elasticIndexManager.List().ToArray();
var client = await _elasticIndexManager.Client();
IEnumerable <string> elasticIndices = client.Indices.Stats(new IndicesStatsRequest( )).Indices.Select(x => x.Key).Distinct();
model.Indices = elasticIndices.ToArray();
// Can't query if there are no indices
if (model.Indices.Length == 0)
{
return(RedirectToAction("Index"));
}
if (String.IsNullOrEmpty(model.IndexName))
{
model.IndexName = model.Indices[0];
}
if (!client.Indices.Exists(model.IndexName).Exists)
{
return(NotFound());
}
if (String.IsNullOrWhiteSpace(model.DecodedQuery))
{
return(View(model));
}
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var elasticSettings = await _elasticIndexingService.GetElasticSettingsAsync();
var stopwatch = new Stopwatch();
stopwatch.Start();
/*await _elasticIndexManager.SearchAsync(model.IndexName, async searcher =>
* {
* var analyzer = _luceneAnalyzerManager.CreateAnalyzer(LuceneSettings.StandardAnalyzer);
* var context = new LuceneQueryContext(searcher, LuceneSettings.DefaultVersion, analyzer);
*
* var templateContext = new TemplateContext();
* await templateContext.ContextualizeAsync(_serviceProvider);
* var parameters = JsonConvert.DeserializeObject<Dictionary<string, object>>(model.Parameters);
*
* foreach (var parameter in parameters)
* {
* templateContext.SetValue(parameter.Key, parameter.Value);
* }
*
* var tokenizedContent = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, System.Text.Encodings.Web.JavaScriptEncoder.Default, templateContext);
*
* try
* {
* var parameterizedQuery = JObject.Parse(tokenizedContent);
* var docs = await _queryService.SearchAsync(context, parameterizedQuery);
* model.Documents = docs.ScoreDocs.Select(hit => searcher.Doc(hit.Doc)).ToList();
* }
* catch(Exception e)
* {
* Logger.LogError(e, "Error while executing query");
* ModelState.AddModelError(nameof(model.DecodedQuery), "Invalid query");
* }
*
* stopwatch.Stop();
* model.Elapsed = stopwatch.Elapsed;
* });*/
return(View(model));
}
public async Task <IActionResult> JSONQuery(AdminQueryViewModel model)
{
// if (!await _authorizationService.AuthorizeAsync(User, Permissions.ManageSqlQueries))
// {
// return Unauthorized();
// }
// if (String.IsNullOrWhiteSpace(model.Query))
// {
// return View(model);
// }
// if (String.IsNullOrWhiteSpace(model.DecodedQuery))
// {
// return View(model);
// }
if (String.IsNullOrEmpty(model.Parameters))
{
model.Parameters = "{ }";
}
var stopwatch = new Stopwatch();
stopwatch.Start();
// var connection = _store.Configuration.ConnectionFactory.CreateConnection();
// var dialect = SqlDialectFactory.For(connection);
//load original json doc
var querySource = ((JsonDocumentQuery)(await _queryManager.GetQueryAsync(model.Query)));
var parameters = JsonConvert.DeserializeObject <Dictionary <string, object> >(model.Parameters);
var templateContext = _liquidTemplateManager.Context;
// templateContext.SetValue("Model",sqlQuery);
// await templateContext.ContextualizeAsync(_serviceProvider);
foreach (var parameter in parameters)
{
templateContext.SetValue(parameter.Key, parameter.Value);
}
var tokenizedQuery = await _liquidTemplateManager.RenderAsync(model.DecodedQuery, NullEncoder.Default, templateContext);
// model.FactoryName = _store.Configuration.ConnectionFactory.GetType().FullName;
// if (SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages))
// {
// model.Document = rawQuery;
model.Parameters = JsonConvert.SerializeObject(parameters, Formatting.Indented);
if (String.IsNullOrEmpty(model.DecodedQuery))
{
model.Results = querySource.Document;
}
else
{
//perform a jsonpath query using template
// dynamic queryResults;
// results = JObject.FromObject<jsonDocQuery.Document>;
var jArrObject = JArray.Parse(querySource.Document);
var items = jArrObject.SelectTokens(model.DecodedQuery);
model.Results = JsonConvert.SerializeObject(items, Formatting.Indented);
}
model.Elapsed = stopwatch.Elapsed;
return(View(model));
}
