private static string BuildWhereClause(this QueryHistoryQuery query)
{
string sql = "";
if (query.Sql != null)
{
sql += $" {WhereOrAnd()} sql LIKE '%{query.Sql}%' ";
}
if (!query.ShowErrors)
{
sql += $" {WhereOrAnd()} status = {(int)QueryResponseStatus.Succeeded} ";
}
if (query.ShowFavorites)
{
sql += $" {WhereOrAnd()} star = 1 ";
}
return(sql);
string WhereOrAnd() => string.IsNullOrEmpty(sql) ? " WHERE " : " AND ";
}
public static async Task <IEnumerable <QueryHistory> > Search(QueryHistoryQuery query, List <QueryHistoryTerm> terms)
{
Check.NotNull(query, nameof(query));
Check.NotNull(terms, nameof(terms));
string where = "";
if (query.ShowErrors)
{ // status
where += $" {WhereOrAnd()} q.status = {(int)QueryResponseStatus.Failed} ";
}
else
{
where += $" {WhereOrAnd()} q.status IN ({(int)QueryResponseStatus.Succeeded}, {(int)QueryResponseStatus.Canceled}) ";
}
if (query.ShowNamedQueries)
{ // name
where += $" {WhereOrAnd()} name IS NOT NULL ";
}
if (query.Dbms is not null)
{ // type
where += $" {WhereOrAnd()} type = {(int)query.Dbms} ";
}
foreach (var queryTerm in query.Terms?.Split(",") ?? Enumerable.Empty <string>())
{
where += $" {WhereOrAnd()} (";
bool found = false;
foreach (var term in terms.Where(x => x.Name == queryTerm))
{
if (term.Kind == QueryHistoryTermKind.Environment)
{ // last_environment
where += $"{(found ? "OR" : "")} last_environment = '{queryTerm}' ";
found = true;
}
if (term.Kind == QueryHistoryTermKind.Database)
{ // last_database
where += $"{(found ? "OR" : "")} last_database = '{queryTerm}' ";
found = true;
}
if (term.Kind == QueryHistoryTermKind.QueryName)
{ // name
where += $"{(found ? "OR" : "")} name = '{queryTerm}' ";
found = true;
}
if (term.Kind == QueryHistoryTermKind.Topic)
{ // topics
where += $"{(found ? "OR" : "")} topics LIKE '%{TopicSeparator}{queryTerm}{TopicSeparator}%' ";
found = true;
}
}
if (!found)
{ // sql
where += $" sql LIKE '%{queryTerm}%' ";
}
where += $") ";
}
if (query.ShowFavorites)
{ // star
where += $" {WhereOrAnd()} star = 1 ";
}
return(await QueryList(where));
string WhereOrAnd() => string.IsNullOrEmpty(where) ? " WHERE " : " AND ";
}
public static async Task <IEnumerable <QueryHistory> > Load(QueryHistoryQuery query) => await ServerConnection.QueryList(SelectAllClause + query.BuildWhereClause() + OrderByClause, Map);
public async Task <IActionResult> UpdateHistoryFavorite(int id, QueryHistoryQuery query)
{
await QueryManager.History.UpdateFavorite(id, query.Star);
return(Ok());
}
public async Task <ActionResult <bool> > UpdateHistoryTopics(string code, QueryHistoryQuery query)
{
return(Ok(await QueryHistoryManager.UpdateTopics(code, query.Topics)));
}
public async Task <IActionResult> UpdateHistoryName(string code, QueryHistoryQuery query)
{
await QueryHistoryManager.UpdateName(code, query.Name);
return(Ok());
}
public async Task <IActionResult> UpdateHistoryFavorite(string code, QueryHistoryQuery query)
{
await QueryHistoryManager.UpdateFavorite(code, query.Star);
return(Ok());
}
