public void TestSimpleReductionParsing()
{
string sql = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" TOP 10 *")
.AppendLine("FROM")
.AppendLine(" Posts")
.ToString();
var query = new ParsedQuery(sql, null);
Assert.AreEqual("SELECT\nTOP 10 *\nFROM\nPosts", query.ExecutionSql);
}
public void TestBatchSplittingIgnoresEmptyBatches()
{
string sql = new StringBuilder()
.AppendLine("SELECT 1")
.AppendLine("GO")
.ToString();
var query = new ParsedQuery(sql, null);
var batches = query.ExecutionSqlBatches.ToArray();
Assert.AreEqual(1, batches.Length);
Assert.AreEqual("SELECT 1", batches[0]);
}
public void TestStringEncoding()
{
string sql = "SELECT * FROM Users WHERE Login = ##UserName:string##";
var parameters = new NameValueCollection
{
{ "UserName", "I'm a User's Name" }
};
var query = new ParsedQuery(sql, parameters);
Assert.AreEqual("SELECT * FROM Users WHERE Login = '******''m a User''s Name'", query.ExecutionSql);
Assert.IsTrue(query.IsExecutionReady);
}
private static void ValidateQuery(ParsedQuery query, Site site)
{
if (!query.IsExecutionReady)
{
throw new ApplicationException(
query.ErrorMessage.IsNullOrEmptyReturn("All parameters must be set!")
);
}
if (site == null)
{
throw new ApplicationException("Invalid site ID");
}
}
public void TestWeDetectMissingParameterValues()
{
string sql = "##a## ##b##";
var parameters = new NameValueCollection
{
{ "a", "1" },
{ "b", "" }
};
var query = new ParsedQuery(sql, parameters);
Assert.IsFalse(query.IsExecutionReady);
Assert.AreEqual("Missing value for b!", query.Errors[0]);
}
public void TestInvalidIntParameters()
{
string sql = "##a:int## ##b##";
var parameters = new NameValueCollection
{
{ "a", "hello" },
{ "b", "3" }
};
var query = new ParsedQuery(sql, parameters);
Assert.AreEqual("Expected value of a to be a int!", query.ErrorMessage);
Assert.IsFalse(query.IsExecutionReady);
}
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);
}
public void TestInvalidParameterType()
{
string sql = "##a:frog##";
var parameters = new NameValueCollection
{
{ "a", "thingadongdong" }
};
var query = new ParsedQuery(sql, parameters);
Assert.IsTrue(query.Parameters.ContainsKey("a"));
Assert.AreEqual("a has unknown parameter type frog!", query.ErrorMessage);
Assert.IsFalse(query.IsExecutionReady);
}
public void TestIntParameters()
{
string sql = "##a:int## ##b##";
var parameters = new NameValueCollection
{
{ "a", "1" },
{ "b", "3" }
};
var query = new ParsedQuery(sql, parameters);
Assert.AreEqual("int", query.Parameters["a"].Type);
Assert.AreEqual("1 3", query.ExecutionSql);
Assert.IsTrue(query.IsExecutionReady);
}
public void TestWeDetectAllParameters()
{
string sql = "##a## ##b##";
var parameters = new NameValueCollection
{
{ "a", "1" },
{ "b", "3" }
};
var query = new ParsedQuery(sql, parameters);
Assert.IsTrue(query.Parameters.ContainsKey("a"));
Assert.IsTrue(query.Parameters.ContainsKey("b"));
Assert.IsTrue(query.IsExecutionReady);
}
public QueryPlan BuildExecutionPlan(ParsedQuery parsedQuery, bool freeCache)
{
QueryPlan characterPlan = _charactersIndex.BuildExecutionPlan(new ParsedQuery(words: parsedQuery.Words), freeCache);
if (string.IsNullOrEmpty(parsedQuery.Numbers))
{
return(characterPlan);
}
QueryPlan numberPlan = _numberIndex.BuildExecutionPlan(new ParsedQuery(numbers: parsedQuery.Numbers), freeCache);
if (string.IsNullOrEmpty(parsedQuery.Words))
{
return(numberPlan);
}
return(QueryPlan.And(characterPlan, numberPlan));
}
public void TestValidFloats()
{
string sql = "##a:float## ##b:float##";
var parameters = new NameValueCollection
{
{ "a", "1" },
{ "b", "1.2" }
};
var query = new ParsedQuery(sql, parameters);
Assert.AreEqual("float", query.Parameters["a"].Type);
Assert.AreEqual(query.ExecutionSql, "1 1.2");
Assert.IsTrue(query.IsExecutionReady);
}
public void TestFailingQuery()
{
string sql = new StringBuilder()
.AppendLine("Select 'Select ''' + ''' As [DatabaseName], Case WHEN TagName IS NULL Then" +
" ''' + 'Null' + ''' WHEN TagName = '''' Then ''Empty'' ELSE ''Unexpected'' End As " +
"[Type], Count(*) As [Count] from ' ")
.AppendLine(" + '[' + ']' + '..Tags Where IsNull(TagName,'''')='''' Group By TagName UNION'")
.ToString();
var query = new ParsedQuery(sql, null);
Assert.AreEqual("Select 'Select ''' + ''' As [DatabaseName], Case WHEN TagName IS NULL Then '''" +
" + 'Null' + ''' WHEN TagName = '''' Then ''Empty'' ELSE ''Unexpected'' End As [Type]," +
" Count(*) As [Count] from '\n+ '[' + ']' + '..Tags Where IsNull(TagName,'''')='''' Group" +
" By TagName UNION'", query.ExecutionSql);
}
public ActionResult ShowSingleSiteCsv(string sitename, int revisionId, string slug)
{
Query query = QueryUtil.GetQueryForRevision(revisionId);
if (query == null)
{
return(PageNotFound());
}
Site site;
if (!TryGetSite(sitename, out site))
{
return(site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/csv/{1}{2}{3}",
site.TinyName.ToLower(), revisionId, slug.HasValue() ? "/" + slug : "", Request.Url.Query
)));
}
var parsedQuery = new ParsedQuery(query.QueryBody, Request.Params);
if (!parsedQuery.IsExecutionReady)
{
return(PageBadRequest());
}
CachedResult cachedResults = QueryUtil.GetCachedResults(
parsedQuery,
Site.Id
);
List <ResultSet> resultSets;
if (cachedResults != null)
{
resultSets = JsonConvert.DeserializeObject <List <ResultSet> >(cachedResults.Results, QueryResults.GetSettings());
}
else
{
resultSets = QueryRunner.GetResults(
parsedQuery,
site,
CurrentUser
).ResultSets;
}
return(new CsvResult(resultSets));
}
public static VisitedQuery VisitStroke(this ParsedQuery q, Func <Type, bool> isEntity)
{
List <SqlQueryExpression> positioned = new List <SqlQueryExpression>();
var visitor = new StrokeVisitor(q.InitialTableReferences, isEntity);
foreach (var pe in q.Arguments)
{
visitor.Visit(pe.Expression);
var sql = visitor.Retrieve();
sql.IsTop = true;
sql.Position = pe.Position;
sql.Index = pe.Index;
positioned.Add(sql);
}
return(new VisitedQuery(q.QueryStructure, positioned.ToArray(), visitor.UsedTypes));
}
private void SetParameters(ParsedQuery query)
{
if (query.Named)
{
foreach (var name in query.ParameterNames.Distinct())
{
_command.AddParameter(name, ParameterSource[name]);
}
}
else
{
for (var i = 0; i < query.ParameterNames.Count; i++)
{
var name = query.ParameterNames[i];
_command.AddParameter(i.ToString(), ParameterSource[name]);
}
}
}
public void TestWeParseParametersInMultiLineStrings()
{
string sql = new StringBuilder()
.AppendLine("SELECT * FROM Posts WHERE Body LIKE '%")
.AppendLine("##SearchTerm##'")
.ToString();
var parameters = new NameValueCollection
{
{ "SearchTerm", "foobar" }
};
var query = new ParsedQuery(sql, parameters);
Assert.IsTrue(query.Parameters.ContainsKey("SearchTerm"));
Assert.AreEqual("SELECT * FROM Posts WHERE Body LIKE '%\nfoobar'", query.ExecutionSql);
Assert.IsTrue(query.IsExecutionReady);
}
public void TestCommentReductionParsing()
{
string sql = new StringBuilder()
.AppendLine("-- A single line comment")
.AppendLine("SELECT")
.AppendLine(" TOP 10 * -- We only want the top 10")
.AppendLine("FROM")
.AppendLine("/* Posts */")
.AppendLine("/*")
.AppendLine(" Comments")
.AppendLine(" */")
.AppendLine(" Users")
.ToString();
var query = new ParsedQuery(sql, null);
Assert.AreEqual("SELECT\nTOP 10 *\nFROM\nUsers", query.ExecutionSql);
}
public ActionResult ShowSingleSiteCsv(string sitename, int revisionId)
{
Query query = QueryUtil.GetQueryForRevision(revisionId);
if (query == null)
{
return(PageNotFound());
}
var site = GetSite(sitename);
if (sitename == null)
{
return(PageNotFound());
}
var parsedQuery = new ParsedQuery(query.QueryBody, Request.Params);
if (!parsedQuery.IsExecutionReady)
{
return(PageBadRequest());
}
CachedResult cachedResults = QueryUtil.GetCachedResults(
parsedQuery,
Site.Id
);
List <ResultSet> resultSets;
if (cachedResults != null)
{
resultSets = JsonConvert.DeserializeObject <List <ResultSet> >(cachedResults.Results);
}
else
{
resultSets = QueryRunner.GetResults(
parsedQuery,
site,
CurrentUser
).ResultSets;
}
return(new CsvResult(resultSets));
}
public static void Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("Usage: IndexStats index-dir action [args]");
Environment.Exit(1);
}
var index = Index.Load(args[0]);
var action = args[1];
if (action == "termDocs")
{
var term = args[2];
var query = new ParsedQuery {
TermsWithSynonyms = new[] { new[] { term } }
};
Console.WriteLine(string.Join(" ", index.GetPostingsByQuery(query).Select(x => x.PostingId.ToString()).ToArray()));
}
else if (action == "playGame")
{
var urls = File.ReadAllLines("urls.txt");
using (var output = new StreamWriter("ranking.txt"))
{
var reqNum = 0;
foreach (var req in File.ReadAllLines("requests.txt"))
{
var parsedQuery = RawQueryParser.Parse(req, null, null, null, new MorphologyAnalyzer("127.0.0.1"));
var results = index
.GetPostingsByQuery(parsedQuery)
.Rank()
.Take(10);
var pos = 0;
foreach (var res in results)
{
output.WriteLine("{0} {1} {2}", reqNum, pos, urls[res.PostingId - 1]);
pos++;
}
reqNum++;
}
}
}
}
private Hit ConvertDocumentIdToHit(WeightedPosting posting, ParsedQuery query)
{
string formattedDocumentId = string.Format("{0:D6}", posting.PostingId);
var document = new Document(formattedDocumentId);
var snippet = new SnippetGenerator(query, document).GenerateSnippet();
return
(new Hit
{
DocumentId = formattedDocumentId,
Description = GetDescription(posting.PostingId, string.Format("№{0}", formattedDocumentId)),
DebugInfo =
new[]
{
string.Format("tf-idf = {0:F5} = {1}", Ranker.GetTfIdf(posting), FormatTfIdf(posting)),
string.Format("ExactWordMatchCount = {0}", posting.ExactWordMatchCount)
},
Snippet = string.Join(" … ", snippet.Skip(1).ToArray()),
Title = snippet.First(),
});
}
public void TestInvalidFloats()
{
string sql = "##a:float## ##b:float## ##c:float## ##d:float##";
var parameters = new NameValueCollection
{
{ "a", "1." },
{ "b", "1.2." },
{ "c", ".2" },
{ "d", "frog" }
};
var query = new ParsedQuery(sql, parameters);
Assert.AreEqual(4, query.Errors.Count);
Assert.AreEqual("Expected value of a to be a float!", query.Errors[0]);
Assert.AreEqual("Expected value of b to be a float!", query.Errors[1]);
Assert.AreEqual("Expected value of c to be a float!", query.Errors[2]);
Assert.AreEqual("Expected value of d to be a float!", query.Errors[3]);
Assert.IsFalse(query.IsExecutionReady);
}
private void RunQuery()
{
lock (_queryLock)
{
if (Database == null)
{
return;
}
if (String.IsNullOrEmpty(Query) && _lastResult.Count == 0)
{
return;
}
Stopwatch w = Stopwatch.StartNew();
ParsedQuery.SymbolName = Query;
ParsedQuery.IgnoreCase = Options.IgnoreCase;
ParsedQuery.TryFindMembers(Database, ref _lastResult);
w.Stop();
QueryStatus = String.Format("{0:n0} matches in {1} for \"{2}\"", _lastResult.Count, w.Elapsed.ToFriendlyString(), Query);
}
}
private IEnumerable <Hit> Search(ParsedQuery query, int page, bool rankResults)
{
var results = Global.Index.GetPostingsByQuery(query);
if (rankResults)
{
results = results.Rank();
}
int resultCount = results.Count();
int pageCount = resultCount / PageSize + 1;
if (resultCount % PageSize == 0)
{
pageCount--;
}
if (page >= pageCount)
{
page = 0;
}
IEnumerable <Hit> pagedResults = results.Skip(PageSize * page).Take(PageSize).Select(x => ConvertDocumentIdToHit(x, query));
if (pagedResults.Count() > 0)
{
lblPage.Visible = hrefPrev.Visible = hrefNext.Visible = true;
int firstResult = page * PageSize + 1;
int lastResult = firstResult + pagedResults.Count() - 1;
listResults.Attributes["start"] = firstResult.ToString();
lblPage.Text = string.Format("Показываем результаты <b>{0}</b>—<b>{1}</b> из <b>{2}</b>", firstResult, lastResult, resultCount);
if (page > 0)
{
BuildUrlForPage(page - 1, hrefPrev);
}
if (page + 1 < pageCount)
{
BuildUrlForPage(page + 1, hrefNext);
}
}
return(pagedResults);
}
public void TestWeIgnoreCommentedParameters()
{
string sql = new StringBuilder()
.AppendLine("SELECT")
.AppendLine(" TOP ##TopCount##")
.AppendLine("FROM")
.AppendLine(" Posts")
.AppendLine("--WHERE UserId == ##UserId##")
.ToString();
var parameters = new NameValueCollection
{
{ "TopCount", "10" },
{ "UserId", "1" }
};
var query = new ParsedQuery(sql, parameters);
Assert.IsTrue(query.Parameters.ContainsKey("TopCount"));
Assert.IsFalse(query.Parameters.ContainsKey("UserId"));
Assert.IsTrue(query.IsExecutionReady);
}
public ActionResult ShowPlan(string sitename, int revisionId, string slug)
{
Query query = QueryUtil.GetQueryForRevision(revisionId);
if (query == null)
{
return(PageNotFound());
}
Site site;
if (!TryGetSite(sitename, out site))
{
return(site == null ? (ActionResult)PageNotFound() : RedirectPermanent(string.Format("/{0}/plan/{1}{2}{3}",
site.TinyName.ToLower(), revisionId, slug.HasValue() ? "/" + slug : "", Request.Url.Query
)));
}
var parsedQuery = new ParsedQuery(query.QueryBody, Request.Params);
if (!parsedQuery.IsExecutionReady)
{
return(PageBadRequest());
}
CachedResult cache = QueryUtil.GetCachedResults(
parsedQuery,
site.Id
);
if (cache == null || cache.ExecutionPlan == null)
{
return(PageNotFound());
}
return(new QueryPlanResult(cache.ExecutionPlan));
}
public void TestCreateProcWithGoReduction()
{
string sql = new StringBuilder()
.AppendLine("CREATE TABLE #Test (id int IDENTITY(1,1) NOT NULL, v VARCHAR(MAX) NOT NULL);")
.AppendLine("go")
.AppendLine("CREATE INDEX #IX_T on #Test(id)")
.AppendLine("go")
.AppendLine("--BEGIN")
.AppendLine("-- RETURN GETDATE()")
.AppendLine("--END")
.AppendLine("CREATE PROCEDURE #T_P2 @v VARCHAR(MAX) AS")
.AppendLine(" PRINT @v")
.AppendLine("RETURN")
.AppendLine("GO")
.AppendLine("#T_P2 'test'")
.ToString();
var query = new ParsedQuery(sql, null);
Assert.IsTrue(query.ExecutionSqlBatches.Any());
Assert.AreEqual("CREATE TABLE #Test (id int IDENTITY(1,1) NOT NULL, v VARCHAR(MAX) NOT NULL);\n" +
"go\nCREATE INDEX #IX_T on #Test(id)\ngo\nCREATE PROCEDURE #T_P2 @v VARCHAR(MAX) AS\nPRINT @v\n" +
"RETURN\nGO\n#T_P2 'test'", query.ExecutionSql);
}
public SnippetGenerator(ParsedQuery query, Document document)
{
this.query = query;
this.document = document;
}
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);
}
