public ActionResult Save(string sql, string title, string description, int siteId, int? querySetId, bool? textResults, bool? withExecutionPlan, TargetSites? targetSites)
{
if (CurrentUser.IsAnonymous && !CaptchaController.CaptchaPassed(GetRemoteIP()))
{
return Json(new { captcha = true });
}
ActionResult response = null;
try
{
if (!ValidateTargetSites(targetSites))
{
throw new ApplicationException("Invalid target sites selection");
}
QuerySet querySet = null;
if (querySetId.HasValue)
{
querySet = Current.DB.QuerySets.Get(querySetId.Value);
if (querySet == null)
{
throw new ApplicationException("Invalid query set ID");
}
}
var parsedQuery = new ParsedQuery(
sql,
Request.Params,
withExecutionPlan == true,
targetSites ?? TargetSites.Current
);
QueryResults results = null;
Site site = GetSite(siteId);
ValidateQuery(parsedQuery, site);
if (title.HasValue() && title.Length > 100)
{
throw new ApplicationException("Title must be no more than 100 characters");
}
if (description.HasValue() && description.Length > 1000)
{
throw new ApplicationException("Description must be no more than 1000 characters");
}
var contextData = new QueryContextData
{
Title = title,
Description = description,
IsText = textResults == true,
QuerySet = querySet
};
var asyncResults = AsyncQueryRunner.Execute(parsedQuery, CurrentUser, site, contextData);
if (asyncResults.State == AsyncQueryRunner.AsyncState.Failure)
{
throw asyncResults.Exception;
}
if (asyncResults.State == AsyncQueryRunner.AsyncState.Success || asyncResults.State == AsyncQueryRunner.AsyncState.Cancelled)
{
results = asyncResults.QueryResults;
}
else
{
return Json(new {running = true, job_id = asyncResults.JobId});
}
response = CompleteResponse(results, parsedQuery, contextData, siteId);
}
catch (Exception ex)
{
response = TransformExecutionException(ex);
}
return response;
}
public ActionResult Execute(int querySetId, int revisionId, int siteId, bool? textResults, bool? withExecutionPlan, TargetSites? targetSites)
{
if (CurrentUser.IsAnonymous && !CaptchaController.CaptchaPassed(GetRemoteIP()))
{
return Json(new { captcha = true });
}
ActionResult response = null;
try
{
if (!ValidateTargetSites(targetSites))
{
throw new ApplicationException("Invalid target sites selection");
}
QuerySet querySet = null;
querySet = Current.DB.QuerySets.Get(querySetId);
if (querySet == null)
{
throw new ApplicationException("Invalid query set ID");
}
Revision revision = Current.DB.Revisions.Get(revisionId);
if (revision == null)
{
throw new ApplicationException("Invalid revision ID");
}
Query query = Current.DB.Queries.Get(revision.QueryId);
var parsedQuery = new ParsedQuery(
query.QueryBody,
Request.Params,
withExecutionPlan == true,
targetSites ?? TargetSites.Current
);
QueryResults results = null;
Site site = GetSite(siteId);
ValidateQuery(parsedQuery, site);
var contextData = new QueryContextData
{
IsText = textResults == true,
QuerySet = querySet,
Revision = revision
};
var asyncResults = AsyncQueryRunner.Execute(parsedQuery, CurrentUser, site, contextData);
if (asyncResults.State == AsyncQueryRunner.AsyncState.Failure)
{
throw asyncResults.Exception;
}
if (asyncResults.State == AsyncQueryRunner.AsyncState.Success)
{
results = asyncResults.QueryResults;
}
else
{
return Json(new { running = true, job_id = asyncResults.JobId });
}
response = CompleteResponse(results, parsedQuery, contextData, siteId);
}
catch (Exception ex)
{
response = TransformExecutionException(ex);
}
return response;
}
public static AsyncResult Execute(ParsedQuery query, User user, Site site, QueryContextData context)
{
string userTag = user.IsAnonymous ? user.IPAddress : user.Id.ToString();
List<Task> activeTasks;
running.TryGetValue(userTag, out activeTasks);
if (activeTasks != null)
{
lock(activeTasks)
{
if (activeTasks.Where(t => !t.IsCompleted).Count() >= AppSettings.ConcurrentQueries)
{
throw new ApplicationException("Too many queries are running, you may only run " + AppSettings.ConcurrentQueries + " queries at a time");
}
}
}
else
{
running.TryAdd(userTag, new List<Task>());
activeTasks = running[userTag];
}
AsyncResult result = new AsyncResult
{
JobId = Guid.NewGuid(),
State = AsyncState.Pending,
ParsedQuery = query,
Site = site,
LastPoll = DateTime.UtcNow,
QueryContextData = context
};
Task task = new Task(() =>
{
try
{
result.QueryResults = QueryRunner.GetResults(query, site, user, result);
if (result.State == AsyncState.Pending)
{
result.State = AsyncState.Success;
}
}
catch (Exception e)
{
result.Exception = e;
result.State = AsyncState.Failure;
}
});
task.ContinueWith(ignore => {
result.CompletionDate = DateTime.UtcNow;
lock (activeTasks)
{
activeTasks.RemoveAll(t => t.IsCompleted);
}
});
result.Task = task;
jobs.TryAdd(result.JobId, result);
task.Start();
lock(activeTasks)
{
activeTasks.Add(task);
}
// give it some time to get results ...
System.Threading.Thread.Sleep(50);
return result;
}
private ActionResult CompleteResponse(
QueryResults results,
ParsedQuery parsedQuery,
QueryContextData context,
int siteId
)
{
results = TranslateResults(parsedQuery, context.IsText, results);
var query = Current.DB.Query<Query>(
"SELECT * FROM Queries WHERE QueryHash = @hash",
new
{
hash = parsedQuery.Hash
}
).FirstOrDefault();
int revisionId = 0;
DateTime saveTime;
// We only create revisions if something actually changed.
// We'll log it as an execution anyway if applicable, so the user will
// still get a link in their profile, just not their own revision.
if (context.Revision == null && (context.QuerySet == null || query == null || context.QuerySet.CurrentRevision == null || context.QuerySet.CurrentRevision.QueryId != query.Id))
{
int queryId;
if (query == null)
{
queryId = (int)Current.DB.Queries.Insert(
new
{
QueryHash = parsedQuery.Hash,
QueryBody = parsedQuery.Sql
}
);
}
else
{
queryId = query.Id;
}
revisionId = (int)Current.DB.Revisions.Insert(
new
{
QueryId = queryId,
OwnerId = CurrentUser.IsAnonymous ? null : (int?)CurrentUser.Id,
OwnerIP = GetRemoteIP(),
CreationDate = saveTime = DateTime.UtcNow,
OriginalQuerySetId = context.QuerySet != null ? context.QuerySet.Id : (int?)null
}
);
int querySetId;
// brand new queryset
if (context.QuerySet == null)
{
// insert it
querySetId = (int)Current.DB.QuerySets.Insert(new
{
InitialRevisionId = revisionId,
CurrentRevisionId = revisionId,
context.Title,
context.Description,
LastActivity = DateTime.UtcNow,
Votes = 0,
Views = 0,
Featured = false,
Hidden = false,
CreationDate = DateTime.UtcNow,
OwnerIp = CurrentUser.IPAddress,
OwnerId = CurrentUser.IsAnonymous?(int?)null:CurrentUser.Id
});
Current.DB.Revisions.Update(revisionId, new { OriginalQuerySetId = querySetId });
}
else if (
(CurrentUser.IsAnonymous && context.QuerySet.OwnerIp == CurrentUser.IPAddress) || context.QuerySet.OwnerId != CurrentUser.Id)
{
// fork it
querySetId = (int)Current.DB.QuerySets.Insert(new
{
InitialRevisionId = context.QuerySet.InitialRevisionId,
CurrentRevisionId = revisionId,
context.Title,
context.Description,
LastActivity = DateTime.UtcNow,
Votes = 0,
Views = 0,
Featured = false,
Hidden = false,
CreationDate = DateTime.UtcNow,
OwnerIp = CurrentUser.IPAddress,
OwnerId = CurrentUser.IsAnonymous ? (int?)null : CurrentUser.Id,
ForkedQuerySetId = context.QuerySet.Id
});
Current.DB.Execute(@"insert QuerySetRevisions(QuerySetId, RevisionId)
select @newId, RevisionId from QuerySetRevisions where QuerySetId = @oldId", new
{
newId = querySetId,
oldId = context.QuerySet.Id
});
}
else
{
// update it
querySetId = context.QuerySet.Id;
context.Title = context.Title ?? context.QuerySet.Title;
context.Description = context.Description ?? context.QuerySet.Description;
Current.DB.QuerySets.Update(context.QuerySet.Id, new { context.Title, context.Description, CurrentRevisionId = revisionId, LastActivity = DateTime.UtcNow});
}
Current.DB.QuerySetRevisions.Insert(new { QuerySetId = querySetId, RevisionId = revisionId });
results.RevisionId = revisionId;
results.Created = saveTime;
results.QuerySetId = querySetId;
}
else
{
results.RevisionId = context.Revision != null ? context.Revision.Id : context.QuerySet.CurrentRevisionId;
results.QuerySetId = context.QuerySet.Id;
results.Created = null;
}
if (context.Title != null)
{
results.Slug = context.Title.URLFriendly();
}
QueryRunner.LogRevisionExecution(CurrentUser, siteId, results.RevisionId);
// Consider handling this XSS condition (?) in the ToJson() method instead, if possible
return Content(results.ToJson().Replace("/", "\\/"), "application/json");
}
public static AsyncResult Execute(ParsedQuery query, User user, Site site, QueryContextData context)
{
string userTag = user.IsAnonymous ? user.IPAddress : user.Id.ToString();
List <Task> activeTasks;
running.TryGetValue(userTag, out activeTasks);
if (activeTasks != null)
{
lock (activeTasks)
{
if (activeTasks.Where(t => !t.IsCompleted).Count() >= AppSettings.ConcurrentQueries)
{
throw new ApplicationException("Too many queries are running, you may only run " + AppSettings.ConcurrentQueries + " queries at a time");
}
}
}
else
{
running.TryAdd(userTag, new List <Task>());
activeTasks = running[userTag];
}
AsyncResult result = new AsyncResult
{
JobId = Guid.NewGuid(),
State = AsyncState.Pending,
ParsedQuery = query,
Site = site,
LastPoll = DateTime.UtcNow,
QueryContextData = context
};
Task task = new Task(() =>
{
try
{
result.QueryResults = QueryRunner.GetResults(query, site, user, result);
if (result.State == AsyncState.Pending)
{
result.State = AsyncState.Success;
}
}
catch (Exception e)
{
result.Exception = e;
result.State = AsyncState.Failure;
}
});
task.ContinueWith(ignore => {
result.CompletionDate = DateTime.UtcNow;
lock (activeTasks)
{
activeTasks.RemoveAll(t => t.IsCompleted);
}
});
result.Task = task;
jobs.TryAdd(result.JobId, result);
task.Start();
lock (activeTasks)
{
activeTasks.Add(task);
}
// give it some time to get results ...
System.Threading.Thread.Sleep(50);
return(result);
}
