private ActionResult ShowCommon(Revision revision, string slug, bool latest)
{
if (revision == null)
{
return PageNotFound();
}
var title = revision.Metadata.Title;
int rootId = revision.OwnerId != null ? revision.RootId.Value : revision.Id;
int ownerId = revision.OwnerId ?? 0;
title = title.URLFriendly();
// if this user has a display name, and the title is missing or does not match, permanently redirect to it
if (title.HasValue() && (string.IsNullOrEmpty(slug) || slug != title))
{
string url = "/{0}/query/{2}/{3}";
if (latest)
{
url = "/{0}/query/{1}/{2}/{3}";
}
return PageMovedPermanentlyTo(string.Format(url, new object[] {
Site.Name.ToLower(),
ownerId,
latest ? rootId : revision.Id,
title
}) + Request.Url.Query);
}
title = revision.Metadata.Title;
if (title.IsNullOrEmpty())
{
title = revision.Query.AsTitle();
}
SetHeader(title);
SelectMenuItem("Queries");
ViewData["GuessedUserId"] = Site.GuessUserId(CurrentUser);
// Need to revamp voting process
int totalVotes = Current.DB.Query<int>(@"
SELECT
COUNT(*)
FROM
Votes
WHERE
RootId = @root AND
VoteTypeId = @voteType AND
OwnerId " + (ownerId > 0 ? "= @owner" : "IS NULL"),
new
{
root = rootId,
owner = ownerId,
voteType = (int)VoteType.Favorite
}
).FirstOrDefault();
var voting = new QueryVoting
{
TotalVotes = totalVotes,
RevisionId = revision.Id,
ReadOnly = CurrentUser.IsAnonymous
};
if (!Current.User.IsAnonymous)
{
voting.HasVoted = Current.DB.Query<Vote>(@"
SELECT
*
FROM
Votes
WHERE
RootId = @root AND
VoteTypeId = @voteType AND
UserId = @user AND
OwnerId " + (ownerId > 0 ? "= @owner" : "IS NULL"),
new
{
root = rootId,
owner = ownerId,
voteType = (int)VoteType.Favorite,
user = Current.User.Id
}
).FirstOrDefault() != null;
}
CachedResult cachedResults = QueryUtil.GetCachedResults(
new ParsedQuery(revision.Query.QueryBody, Request.Params),
Site.Id
);
ViewData["QueryVoting"] = voting;
ViewData["query_action"] = "run/" + Site.Id + "/" + revision.Id;
if (cachedResults != null)
{
ViewData["cached_results"] = new QueryResults
{
RevisionId = revision.Id,
SiteId = Site.Id,
SiteName = Site.Name,
Slug = revision.Metadata.Title.URLFriendly()
}.WithCache(cachedResults);
}
if (!IsSearchEngine())
{
QueryViewTracker.TrackQueryView(GetRemoteIP(), rootId, ownerId);
}
var viewmodel = new QueryExecutionViewData
{
QueryVoting = voting,
Id = revision.Id,
Name = revision.Metadata.Title,
Description = revision.Metadata.Description,
FavoriteCount = revision.Metadata.Votes,
Views = revision.Metadata.Views,
LastRun = revision.Metadata.LastActivity,
CreatorId = revision.Owner != null ? revision.Owner.Id : (int?)null,
CreatorLogin = revision.Owner != null ? revision.Owner.Login : null,
SiteName = Site.Name.ToLower(),
SQL = revision.Query.QueryBody,
UseLatestLink = false
};
return View("Viewer", viewmodel);
}
private void SaveMetadata(Revision revision, string title, string description, bool updateWithoutChange)
{
QuerySet querySet = null;
if (title.IsNullOrEmpty())
{
title = null;
}
if (description.IsNullOrEmpty())
{
description = null;
}
if (!CurrentUser.IsAnonymous)
{
querySet = Current.DB.Query<QuerySet>(@"
select * from QuerySets
*
FROM
QuerySets
WHERE
InitialRevisionId = @revision AND
OwnerId = @owner",
new
{
owner = CurrentUser.Id
}
).FirstOrDefault();
}
// We always save a querys set for anonymous users since they don't have an
// actual revision history that we're associating the query set with
if (CurrentUser.IsAnonymous || querySet == null)
{
Current.DB.QuerySets.Insert(
new
{
InitialRevisionId = revision.Id,
CurrentRevisionId = revision.Id,
OwnerId = CurrentUser.IsAnonymous ? (int?)null : CurrentUser.Id,
Title = title,
Description = description,
LastActivity = DateTime.UtcNow,
Votes = 0,
Views = 0
}
);
}
else if (querySet.Title != title || querySet.Description != description)
{
Current.DB.QuerySets.Update(querySet.Id,
new
{
Title = title,
Description = description,
CurrentRevisionId = revision.Id,
LastActivity = DateTime.UtcNow
}
);
}
}
private void SaveMetadata(Revision revision, string title, string description, bool updateWithoutChange)
{
Metadata metadata = null;
if (title.IsNullOrEmpty())
{
title = null;
}
if (description.IsNullOrEmpty())
{
description = null;
}
if (!CurrentUser.IsAnonymous)
{
metadata = Current.DB.Query<Metadata>(@"
SELECT
*
FROM
Metadata
WHERE
RevisionId = @revision AND
OwnerId = @owner",
new
{
revision = revision.RootId,
owner = CurrentUser.Id
}
).FirstOrDefault();
}
// We always save a metadata for anonymous users since they don't have an
// actual revision history that we're associating the metadata with
if (CurrentUser.IsAnonymous || metadata == null)
{
Current.DB.Execute(@"
INSERT INTO Metadata(
RevisionId, OwnerId, Title, Description,
LastQueryId, LastActivity, Votes, Views
) VALUES(
@revision, @owner, @title, @description,
@query, @activity, 0, 0
)",
new
{
revision = CurrentUser.IsAnonymous ? revision.Id : revision.RootId,
owner = CurrentUser.IsAnonymous ? (int?)null : CurrentUser.Id,
title = title,
description = description,
query = revision.QueryId,
activity = DateTime.UtcNow
}
);
}
else if (updateWithoutChange || metadata.Title != title || metadata.Description != description)
{
Current.DB.Execute(@"
UPDATE
Metadata
SET
Title = @title, Description = @description,
LastQueryId = @query, LastActivity = @activity
WHERE
Id = @id",
new
{
id = metadata.Id,
title = title,
description = description,
query = revision.QueryId,
activity = DateTime.UtcNow
}
);
}
}
private ActionResult ShowCommon(Revision revision, string slug, bool latest)
{
if (revision == null)
{
return PageNotFound();
}
var title = revision.QuerySet.Title;
int ownerId = revision.OwnerId ?? 0;
title = title.URLFriendly();
// if this query has a title, and the title is missing or does not match, permanently redirect to it
if (title.HasValue() && (string.IsNullOrEmpty(slug) || slug != title))
{
string url = latest ? "/{0}/query/{1}/{3}" : "/{0}/revision/{1}/{2}/{3}";
return PageMovedPermanentlyTo(string.Format(url,
Site.TinyName.ToLower(),
revision.QuerySet.Id,
revision.Id,
title
) + Request.Url.Query);
}
title = revision.QuerySet.Title;
if (title.IsNullOrEmpty())
{
title = revision.Query.AsTitle();
}
SetHeader(title);
SelectMenuItem("Queries");
ViewData["GuessedUserId"] = Site.GuessUserId(CurrentUser);
// Need to revamp voting process
int totalVotes = Current.DB.Query<int>(@"
SELECT
COUNT(*)
FROM
Votes
WHERE
QuerySetId = @querySetId AND
VoteTypeId = @voteType",
new
{
querySetId = revision.QuerySet.Id,
voteType = (int)VoteType.Favorite
}
).FirstOrDefault();
var voting = new QuerySetVoting
{
TotalVotes = totalVotes,
QuerySetId = revision.QuerySet.Id,
ReadOnly = CurrentUser.IsAnonymous
};
if (!Current.User.IsAnonymous)
{
voting.HasVoted = Current.DB.Query<Vote>(@"
SELECT
*
FROM
Votes
WHERE
QuerySetId = @querySetId AND
VoteTypeId = @voteType AND
UserId = @user",
new
{
querySetId = revision.QuerySet.Id,
voteType = (int)VoteType.Favorite,
user = Current.User.Id
}
).FirstOrDefault() != null;
}
CachedResult cachedResults = QueryUtil.GetCachedResults(
new ParsedQuery(revision.Query.QueryBody, Request.Params),
Site.Id
);
ViewData["QueryVoting"] = voting;
ViewData["query_action"] = "run/" + Site.Id + "/" + revision.QuerySet.Id + "/" + revision.Id;
if (cachedResults != null)
{
// Don't show cached execution plan, since the user didn't ask for it...
cachedResults.ExecutionPlan = null;
ViewData["cached_results"] = new QueryResults
{
RevisionId = revision.Id,
QuerySetId = revision.QuerySet.Id,
SiteId = Site.Id,
SiteName = Site.TinyName.ToLower(),
Slug = revision.QuerySet.Title.URLFriendly(),
Url = Site.Url
}.WithCache(cachedResults);
}
if (!IsSearchEngine())
{
QueryViewTracker.TrackQueryView(GetRemoteIP(), revision.QuerySet.Id);
}
var initialRevision = revision.QuerySet.InitialRevision;
var viewmodel = new QueryViewerData
{
QuerySetVoting = voting,
Revision = revision
};
return View("Viewer", viewmodel);
}
public ActionResult Create(string sql, string title, string description, int siteId, int? parentId, bool? textResults, bool? withExecutionPlan, bool? crossSite, bool? excludeMetas)
{
ActionResult response = null;
try
{
Revision parent = null;
if (parentId.HasValue)
{
parent = QueryUtil.GetBasicRevision(parentId.Value);
if (parent == null)
{
throw new ApplicationException("Invalid revision ID");
}
}
var parsedQuery = new ParsedQuery(
sql,
Request.Params,
withExecutionPlan == true,
crossSite == true,
excludeMetas == true
);
var results = ExecuteWithResults(parsedQuery, siteId, textResults == true);
var query = Current.DB.Query<Query>(
"SELECT * FROM Queries WHERE QueryHash = @hash",
new
{
hash = parsedQuery.Hash
}
).FirstOrDefault();
int revisionId = 0, queryId;
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 (!(parent != null && query != null && query.Id == parent.QueryId))
{
if (query == null)
{
queryId = (int)Current.DB.Query<decimal>(@"
INSERT INTO Queries(
QueryHash, QueryBody
) VALUES(
@hash, @body
)
SELECT SCOPE_IDENTITY()",
new
{
hash = parsedQuery.Hash,
body = parsedQuery.Sql
}
).First();
}
else
{
queryId = query.Id;
}
revisionId = (int)Current.DB.Query<decimal>(@"
INSERT INTO Revisions(
QueryId, RootId, ParentId, OwnerId, OwnerIP, CreationDate
) VALUES(
@query, @root, @parent, @owner, @ip, @creation
)
SELECT SCOPE_IDENTITY()",
new
{
query = queryId,
root = parent != null ? (int?)parent.RootId : null,
parent = parentId,
owner = CurrentUser.IsAnonymous ? null : (int?)CurrentUser.Id,
ip = GetRemoteIP(),
creation = saveTime = DateTime.UtcNow
}
).First();
var revision = new Revision
{
Id = revisionId,
RootId = parent != null ? (int?)parent.RootId : null,
QueryId = queryId
};
SaveMetadata(revision, title, description, true);
results.RevisionId = revisionId;
results.Created = saveTime;
}
else
{
queryId = query.Id;
results.RevisionId = parentId.Value;
}
if (parent != null)
{
results.ParentId = parent.Id;
}
if (title != null)
{
results.Slug = title.URLFriendly();
}
QueryRunner.LogQueryExecution(CurrentUser, siteId, results.RevisionId, queryId);
// Consider handling this XSS condition (?) in the ToJson() method instead, if possible
response = Content(results.ToJson().Replace("/", "\\/"), "application/json");
}
catch (Exception ex)
{
response = TransformExecutionException(ex);
}
return response;
}
