static void AddBody(StringBuilder buffer, QueryResults results, Site site) { buffer.AppendLine(site.Name); buffer.AppendLine("-------------------------------------------------"); buffer.AppendLine(results.Messages); buffer.AppendLine(); buffer.AppendLine(); buffer.AppendLine(); }
public void TestToText() { QueryResults results = new QueryResults(); results.Messages = @"1 2 "; ResultSet first = new ResultSet(); first.MessagePosition = 0; first.Columns.Add(new ResultColumnInfo() {Name = "a"}); first.Rows.Add(new List<object>() { "xxx" }); ResultSet second = new ResultSet(); second.MessagePosition = 4; second.Columns.Add(new ResultColumnInfo() {Name = "hello"}); second.Rows.Add(new List<object>() { "x" }); results.ResultSets.Add(first); results.ResultSets.Add(second); var transformed = results.ToTextResults(); Assert.AreEqual(true, transformed.TextOnly); var expected = @"a --- xxx 1 hello ----- x 2 "; var actual = string.Join("\r\n", transformed.Messages.Split('\n').Select(s => s.Trim())); Assert.AreEqual(expected , actual); }
public void TestGapsInFirstResultSet() { var results1 = new QueryResults(); var results2 = new QueryResults(); results1.ResultSets = new List<ResultSet>() { new ResultSet() }; results2.ResultSets = new List<ResultSet>() { new ResultSet() }; results1.ResultSets[0].Columns = new List<ResultColumnInfo> { new ResultColumnInfo{ Name = "col1", Type = ResultColumnType.Default }, new ResultColumnInfo{ Name = "col2", Type = ResultColumnType.Default }, new ResultColumnInfo{ Name = "Pivot", Type = ResultColumnType.Default } }; results2.ResultSets[0].Columns = new List<ResultColumnInfo> { new ResultColumnInfo{ Name = "col1", Type = ResultColumnType.Default }, new ResultColumnInfo{ Name = "col2", Type = ResultColumnType.Default }, new ResultColumnInfo{ Name = "Pivot", Type = ResultColumnType.Default } }; results1.ResultSets[0].Rows = new List<List<object>> { new List<object>{1,1,1}, new List<object>{2,2,2}, new List<object>{3,3,3}, }; results2.ResultSets[0].Rows = new List<List<object>> { new List<object>{2,2,99}, new List<object>{4,4,666} }; QueryRunner.MergePivot(Current.DB.Sites.First(), results1, results2); Assert.IsNull(results1.ResultSets[0].Rows[0][3]); Assert.AreEqual(99, results1.ResultSets[0].Rows[1][3]); Assert.IsNull(results1.ResultSets[0].Rows[2][3]); Assert.IsNull(results1.ResultSets[0].Rows[3][2]); Assert.AreEqual(666, results1.ResultSets[0].Rows[3][3]); }
public static void MergePivot(Site site, QueryResults current, QueryResults newResults) { int pivotIndex = -1; foreach (var info in newResults.ResultSets.First().Columns) { pivotIndex++; if (info.Name == "Pivot") { break; } } var map = current .ResultSets .First() .Rows .Select(columns => new { key = string.Join("|||", columns.Where((c, i) => i != pivotIndex && i < newResults.ResultSets.First().Columns.Count)), cols = columns }) .ToDictionary(r => r.key, r => r.cols); var newRows = new List<List<object>>(); foreach (var row in newResults.ResultSets.First().Rows) { List<object> foundRow; if (map.TryGetValue(string.Join("|||", row.Where((c, i) => i != pivotIndex)), out foundRow)) { foundRow.Add(row[pivotIndex]); } else { newRows.Add(row); } } current.ResultSets.First().Columns.Add(new ResultColumnInfo { Name = site.Name + " Pivot", Type = newResults.ResultSets.First().Columns[pivotIndex].Type }); var totalColumns = current.ResultSets.First().Columns.Count; foreach (var row in current.ResultSets.First().Rows) { if (row.Count < totalColumns) { row.Add(null); } } foreach (var row in newRows) { for (int i = pivotIndex+1; i < totalColumns; i++) { row.Insert(pivotIndex, null); } current.ResultSets.First().Rows.Add(row); } }
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user, AsyncQueryRunner.AsyncResult result) { var remoteIP = OData.GetRemoteIP(); var key = "total-" + remoteIP; var currentCount = (int?)Current.GetCachedObject(key) ?? 0; currentCount++; Current.SetCachedObjectSliding(key, currentCount, 60 * 60); if (currentCount > 130) { // clearly a robot, auto black list Current.DB.BlackList.Insert(new { CreationDate = DateTime.UtcNow, IPAddress = remoteIP }); } if (currentCount > 100) { throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!"); } if (Current.DB.Query<int>("select count(*) from BlackList where IPAddress = @remoteIP", new { remoteIP }).First() > 0) { System.Threading.Thread.Sleep(2000); throw new Exception("You have been blacklisted due to abuse!"); } var results = new QueryResults(); using (SqlConnection cnn = site.GetOpenConnection()) { // well we do not want to risk blocking, if somebody needs to change this we will need to add a setting cnn.Execute("set transaction isolation level read uncommitted"); var timer = new Stopwatch(); timer.Start(); var messages = new StringBuilder(); var infoHandler = new SqlInfoMessageEventHandler((sender, args) => { // todo handle errors as well messages.AppendLine(args.Message); }); try { cnn.InfoMessage += infoHandler; if (query.IncludeExecutionPlan) { using (var command = new SqlCommand("SET STATISTICS XML ON", cnn)) { command.ExecuteNonQuery(); } } var plan = new QueryPlan(); foreach (string batch in query.ExecutionSqlBatches) { using (var command = new SqlCommand(batch, cnn)) { if (result != null) { result.Command = command; if (result.Cancelled) { continue; } } command.CommandTimeout = AppSettings.QueryTimeout; try { PopulateResults(results, command, result, messages, query.IncludeExecutionPlan); } catch (Exception ex) { // Ugh. So, if we cancel the query in-process, we get an exception... // But we have no good way of knowing that the exception here is actually // *that* exception...so we'll just assume it was if the state is Cancelled if (result == null || result.State != AsyncQueryRunner.AsyncState.Cancelled) { throw ex; } } } if (query.IncludeExecutionPlan) { plan.AppendBatchPlan(results.ExecutionPlan); results.ExecutionPlan = null; } } results.ExecutionPlan = plan.PlanXml; } finally { cnn.InfoMessage -= infoHandler; results.Messages = messages.ToString(); } timer.Stop(); results.ExecutionTime = timer.ElapsedMilliseconds; ProcessMagicColumns(results, cnn); } return results; }
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); }
public QueryResults ToTextResults() { var results = new QueryResults(); results.ExecutionTime = ExecutionTime; results.FirstRun = FirstRun; results.MaxResults = MaxResults; results.QueryId = QueryId; results.SiteId = SiteId; results.SiteName = SiteName; results.TextOnly = true; results.Truncated = Truncated; results.Url = Url; results.Slug = Slug; results.TargetSites = TargetSites; results.ExecutionPlan = this.ExecutionPlan; results.ParentId = ParentId; results.RevisionId = RevisionId; results.Created = Created; results.QuerySetId = QuerySetId; results.Messages = FormatTextResults(Messages, ResultSets); return results; }
/// <summary> /// Adds the results of a running a particular query for a given site to the database cache /// </summary> /// <param name="results">The results of the query</param> /// <param name="query">The query that was executed</param> /// <param name="site">The site that the query was run against</param> /// <param name="planOnly">Whether or not this is just an update to add the cached execution plan</param> private static void AddResultToCache(QueryResults results, ParsedQuery query, Site site, bool planOnly) { // If the cache time is zero, just don't save a cache if (AppSettings.AutoExpireCacheMinutes == 0) { return; } if (!planOnly) { Current.DB.Execute(@" INSERT INTO CachedResults( QueryHash, SiteId, Results, ExecutionPlan, Messages, Truncated, CreationDate ) VALUES( @hash, @site, @results, @plan, @messages, @truncated, @creation )", new { hash = query.ExecutionHash, site = site.Id, results = results.GetJsonResults(), plan = results.ExecutionPlan, messages = results.Messages, truncated = results.Truncated, creation = DateTime.UtcNow } ); } else { // Should we just update everything in this case? Presumably the only // thing that changed was the addition of the execution plan, but... Current.DB.Execute(@" UPDATE CachedResults SET ExecutionPlan = @plan WHERE QueryHash = @hash", new { plan = results.ExecutionPlan, hash = query.ExecutionHash } ); } }
private static QueryResults TranslateResults(ParsedQuery query, bool textResults, QueryResults results) { textResults = textResults || (results.ResultSets.Count != 1); if (textResults) { results = results.ToTextResults(); } if (query.IncludeExecutionPlan) { results = results.TransformQueryPlan(); } return results; }
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"); }
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); }
private static void ProcessMagicColumns(QueryResults results, SqlConnection cnn) { int index = 0; foreach (ResultSet resultSet in results.ResultSets) { foreach (ResultColumnInfo column in resultSet.Columns) { if (magic_columns.ContainsKey(column.Name)) { DecorateColumn(column); IEnumerable<object> values = resultSet.Rows.Select(row => row[index]); List<object> processedValues = magic_columns[column.Name](cnn, values); int rowNumber = 0; foreach (var row in resultSet.Rows) { row[index] = processedValues[rowNumber]; rowNumber++; } } index++; } } }
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user) { var remoteIP = OData.GetRemoteIP(); var key = "total-" + remoteIP; var currentCount = (int?)Current.GetCachedObject(key) ?? 0; currentCount++; Current.SetCachedObjectSliding(key, currentCount, 60 * 60); if (currentCount > 130) { // clearly a robot, auto black list var b = new BlackList { CreationDate = DateTime.UtcNow, IPAddress = remoteIP }; } if (currentCount > 100) { throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!"); } if (Current.DB.ExecuteQuery<int>("select count(*) from BlackList where IPAddress = {0}", remoteIP).First() > 0) { System.Threading.Thread.Sleep(2000); throw new Exception("You have been blacklisted due to abuse!"); } var results = new QueryResults(); using (SqlConnection cnn = site.GetConnection()) { cnn.Open(); // well we do not want to risk blocking, if somebody needs to change this we will need to add a setting cnn.Execute("set transaction isolation level read uncommitted"); var timer = new Stopwatch(); timer.Start(); var messages = new StringBuilder(); var infoHandler = new SqlInfoMessageEventHandler((sender, args) => { // todo handle errors as well messages.AppendLine(args.Message); }); try { cnn.InfoMessage += infoHandler; if (query.IncludeExecutionPlan) { using (var command = new SqlCommand("SET STATISTICS XML ON", cnn)) { command.ExecuteNonQuery(); } } var plan = new QueryPlan(); foreach (string batch in query.ExecutionSqlBatches) { using (var command = new SqlCommand(batch, cnn)) { command.CommandTimeout = QUERY_TIMEOUT; PopulateResults(results, command, messages, query.IncludeExecutionPlan); } if (query.IncludeExecutionPlan) { plan.AppendBatchPlan(results.ExecutionPlan); results.ExecutionPlan = null; } } results.ExecutionPlan = plan.PlanXml; } finally { cnn.InfoMessage -= infoHandler; results.Messages = messages.ToString(); } timer.Stop(); results.ExecutionTime = timer.ElapsedMilliseconds; ProcessMagicColumns(results, cnn); } return results; }
public static QueryResults GetSingleSiteResults(ParsedQuery query, Site site, User user) { QueryResults results = null; var timer = new Stopwatch(); timer.Start(); var cache = QueryUtil.GetCachedResults(query, site.Id); if (cache != null) { if (!query.IncludeExecutionPlan || cache.ExecutionPlan != null) { results = new QueryResults(); results.WithCache(cache); results.Truncated = cache.Truncated; results.Messages = cache.Messages; results.FromCache = true; // If we didn't ask for the execution plan, don't return it if (!query.IncludeExecutionPlan) { results.ExecutionPlan = null; } } } timer.Stop(); if (results == null) { results = ExecuteNonCached(query, site, user); results.FromCache = false; AddResultToCache(results, query, site, cache != null); } else { results.ExecutionTime = timer.ElapsedMilliseconds; } results.Url = site.Url; results.SiteId = site.Id; results.SiteName = site.Name.ToLower(); return results; }
private static QueryResults MockResults() { var rows = new List<List<object>>(); rows.Add(new List<object>()); rows[0].Add("hello"); var resultSet = new ResultSet(); resultSet.Rows = rows; QueryResults results = new QueryResults() { Url = "test", QueryId = 100 }; results.ResultSets.Add(resultSet); return results; }
/// <summary> /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results. /// </summary> /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param> /// <param name="command">SQL command to execute.</param> /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param> /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param> /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained /// in the results sets; otherwise, false.</param> private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan) { QueryPlan plan = new QueryPlan(); using (SqlDataReader reader = command.ExecuteReader()) { if (result != null && reader.HasRows) { result.HasOutput = true; } do { // Check to see if the resultset is an execution plan if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan") { if (reader.Read()) { plan.AppendStatementPlan(reader[0].ToString()); } } else { if (reader.FieldCount == 0) { if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } continue; } var resultSet = new ResultSet(); resultSet.MessagePosition = messages.Length; results.ResultSets.Add(resultSet); for (int i = 0; i < reader.FieldCount; i++) { var columnInfo = new ResultColumnInfo(); columnInfo.Name = reader.GetName(i); ResultColumnType colType; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType)) { columnInfo.Type = colType; } resultSet.Columns.Add(columnInfo); } int currentRow = 0; while (reader.Read()) { if (currentRow++ >= MAX_RESULTS) { results.Truncated = true; results.MaxResults = MAX_RESULTS; break; } var row = new List<object>(); resultSet.Rows.Add(row); for (int i = 0; i < reader.FieldCount; i++) { object col = reader.GetValue(i); if (col is DateTime) { var date = (DateTime)col; col = date.ToJavascriptTime(); } row.Add(col); } } if (results.Truncated) { // next result would force ado.net to fast forward // through the result set, which is way too slow break; } if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count); } } while (reader.NextResult()); command.Cancel(); } results.ExecutionPlan = plan.PlanXml; }
private static QueryResults GetSingleSiteResults(ParsedQuery query, Site site, User user, AsyncQueryRunner.AsyncResult result = null) { QueryResults results = null; var timer = new Stopwatch(); timer.Start(); var cache = QueryUtil.GetCachedResults(query, site.Id); if (cache != null) { if (!query.IncludeExecutionPlan || cache.ExecutionPlan != null) { results = new QueryResults(); results.WithCache(cache); results.Truncated = cache.Truncated; results.Messages = cache.Messages; results.FromCache = true; // If we didn't ask for the execution plan, don't return it if (!query.IncludeExecutionPlan) { results.ExecutionPlan = null; } } } timer.Stop(); if (results == null) { results = ExecuteNonCached(query, site, user, result); results.FromCache = false; // Don't cache cancelled results, since we don't know what state they're in... if (result != null && !result.Cancelled) { AddResultToCache(results, query, site, cache != null); } } else { results.ExecutionTime = timer.ElapsedMilliseconds; } results.Url = site.Url; results.SiteId = site.Id; results.SiteName = site.Name.ToLower(); return results; }
public static QueryResults ExecuteNonCached(ParsedQuery query, Site site, User user, AsyncQueryRunner.AsyncResult result) { var remoteIP = OData.GetRemoteIP(); var key = "total-" + remoteIP; var currentCount = (int?)Current.GetCachedObject(key) ?? 0; currentCount++; Current.SetCachedObjectSliding(key, currentCount, 60 * 60); if (currentCount > 130) { // clearly a robot, auto black list Current.DB.BlackList.Insert(new { CreationDate = DateTime.UtcNow, IPAddress = remoteIP }); } if (currentCount > 100) { throw new Exception("You can not run any new queries for another hour, you have exceeded your limit!"); } if (Current.DB.Query <int>("select count(*) from BlackList where IPAddress = @remoteIP", new { remoteIP }).First() > 0) { System.Threading.Thread.Sleep(2000); throw new Exception("You have been blacklisted due to abuse!"); } var results = new QueryResults(); using (SqlConnection cnn = site.GetOpenConnection()) { // well we do not want to risk blocking, if somebody needs to change this we will need to add a setting cnn.Execute("set transaction isolation level read uncommitted"); var timer = new Stopwatch(); timer.Start(); var messages = new StringBuilder(); var infoHandler = new SqlInfoMessageEventHandler((sender, args) => { // todo handle errors as well messages.AppendLine(args.Message); }); try { cnn.InfoMessage += infoHandler; if (query.IncludeExecutionPlan) { using (var command = new SqlCommand("SET STATISTICS XML ON", cnn)) { command.ExecuteNonQuery(); } } var plan = new QueryPlan(); foreach (string batch in query.ExecutionSqlBatches) { using (var command = new SqlCommand(batch, cnn)) { if (result != null) { result.Command = command; if (result.Cancelled) { continue; } } command.CommandTimeout = AppSettings.QueryTimeout; try { PopulateResults(results, command, result, messages, query.IncludeExecutionPlan); } catch (Exception ex) { // Ugh. So, if we cancel the query in-process, we get an exception... // But we have no good way of knowing that the exception here is actually // *that* exception...so we'll just assume it was if the state is Cancelled if (result == null || result.State != AsyncQueryRunner.AsyncState.Cancelled) { throw ex; } } } if (query.IncludeExecutionPlan) { plan.AppendBatchPlan(results.ExecutionPlan); results.ExecutionPlan = null; } } results.ExecutionPlan = plan.PlanXml; } finally { cnn.InfoMessage -= infoHandler; results.Messages = messages.ToString(); } timer.Stop(); results.ExecutionTime = timer.ElapsedMilliseconds; ProcessMagicColumns(results, cnn); } return(results); }
private static void ProcessMagicColumns(QueryResults results, SqlConnection cnn) { int index = 0; foreach (ResultSet resultSet in results.ResultSets) { foreach (ResultColumnInfo column in resultSet.Columns) { if (magic_columns.ContainsKey(column.Name)) { DecorateColumn(column); // tricky ... multi site has special handling. if (resultSet.Columns.Any(c => c.Type == ResultColumnType.Site)) { int siteNameIndex = 0; foreach (var item in resultSet.Columns) { if (item.Type == ResultColumnType.Site) break; siteNameIndex++; } var sites = Current.DB.Sites.All(); foreach (var group in resultSet.Rows.GroupBy(r => r[siteNameIndex])) { using (var newConnection = sites.First(s => s.Id == ((SiteInfo)group.First()[siteNameIndex]).Id).GetOpenConnection()) { ProcessColumn(newConnection, index, group.ToList(), column); } } } else { ProcessColumn(cnn, index, resultSet.Rows, column); } } index++; } } }
public static void MergePivot(Site site, QueryResults current, QueryResults newResults) { int pivotIndex = -1; foreach (var info in newResults.ResultSets.First().Columns) { pivotIndex++; if (info.Name == "Pivot") { break; } } var map = current .ResultSets .First() .Rows .Select(columns => new { key = string.Join("|||", columns.Where((c, i) => i != pivotIndex && i < newResults.ResultSets.First().Columns.Count)), cols = columns }) .ToDictionary(r => r.key, r => r.cols); var newRows = new List <List <object> >(); foreach (var row in newResults.ResultSets.First().Rows) { List <object> foundRow; if (map.TryGetValue(string.Join("|||", row.Where((c, i) => i != pivotIndex)), out foundRow)) { foundRow.Add(row[pivotIndex]); } else { newRows.Add(row); } } current.ResultSets.First().Columns.Add(new ResultColumnInfo { Name = site.Name + " Pivot", Type = newResults.ResultSets.First().Columns[pivotIndex].Type }); var totalColumns = current.ResultSets.First().Columns.Count; foreach (var row in current.ResultSets.First().Rows) { if (row.Count < totalColumns) { row.Add(null); } } foreach (var row in newRows) { for (int i = pivotIndex + 1; i < totalColumns; i++) { row.Insert(pivotIndex, null); } current.ResultSets.First().Rows.Add(row); } }
public QueryResults TransformQueryPlan() { var returnValue = new QueryResults(); returnValue.ExecutionTime = this.ExecutionTime; returnValue.FirstRun = this.FirstRun; returnValue.MaxResults = this.MaxResults; returnValue.QueryId = this.QueryId; returnValue.SiteId = this.SiteId; returnValue.SiteName = this.SiteName; returnValue.TextOnly = this.TextOnly; returnValue.Truncated = this.Truncated; returnValue.Url = this.Url; returnValue.Slug = this.Slug; returnValue.TargetSites = this.TargetSites; returnValue.ResultSets = this.ResultSets; returnValue.Messages = this.Messages; returnValue.ParentId = ParentId; returnValue.RevisionId = RevisionId; returnValue.Created = Created; returnValue.QuerySetId = QuerySetId; returnValue.ExecutionPlan = TransformPlan(this.ExecutionPlan); return returnValue; }
/// <summary> /// Executes an SQL query and populates a given <see cref="QueryResults" /> instance with the results. /// </summary> /// <param name="results"><see cref="QueryResults" /> instance to populate with results.</param> /// <param name="command">SQL command to execute.</param> /// <param name="result"><see cref="AsyncResult"/> instance to use to mark state changes.</param> /// <param name="messages"><see cref="StringBuilder" /> instance to which to append messages.</param> /// <param name="IncludeExecutionPlan">If true indciates that the query execution plans are expected to be contained /// in the results sets; otherwise, false.</param> private static void PopulateResults(QueryResults results, SqlCommand command, AsyncQueryRunner.AsyncResult result, StringBuilder messages, bool IncludeExecutionPlan) { QueryPlan plan = new QueryPlan(); using (SqlDataReader reader = command.ExecuteReader()) { if (result != null && reader.HasRows) { result.HasOutput = true; } do { // Check to see if the resultset is an execution plan if (IncludeExecutionPlan && reader.FieldCount == 1 && reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan") { if (reader.Read()) { plan.AppendStatementPlan(reader[0].ToString()); } } else { if (reader.FieldCount == 0) { if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } continue; } var resultSet = new ResultSet(); resultSet.MessagePosition = messages.Length; results.ResultSets.Add(resultSet); for (int i = 0; i < reader.FieldCount; i++) { var columnInfo = new ResultColumnInfo(); columnInfo.Name = reader.GetName(i); ResultColumnType colType; if (ResultColumnInfo.ColumnTypeMap.TryGetValue(reader.GetFieldType(i), out colType)) { columnInfo.Type = colType; } resultSet.Columns.Add(columnInfo); } int currentRow = 0; while (reader.Read()) { if (currentRow++ >= MAX_RESULTS) { results.Truncated = true; results.MaxResults = MAX_RESULTS; break; } var row = new List <object>(); resultSet.Rows.Add(row); for (int i = 0; i < reader.FieldCount; i++) { object col = reader.GetValue(i); if (col is DateTime) { var date = (DateTime)col; col = date.ToJavascriptTime(); } row.Add(col); } } if (results.Truncated) { // next result would force ado.net to fast forward // through the result set, which is way too slow break; } if (reader.RecordsAffected >= 0) { messages.AppendFormat("({0} row(s) affected)\n\n", reader.RecordsAffected); } messages.AppendFormat("({0} row(s) affected)\n\n", resultSet.Rows.Count); } } while (reader.NextResult()); command.Cancel(); } results.ExecutionPlan = plan.PlanXml; }