public void InitialTest()
{
//var paths = new List<Path>()
//{
// new Path()
// {
// id = 199,
// path = "$.Audit.CreatedOn",
// },
// new Path()
// {
// id = 150,
// path ="$.Name",
// }
// // ...etc.
//};
var paths = new Dictionary <string, int>
{
{ "$.Audit.CreatedOn", 199 },
{ "$.Name", 150 },
{ "$.Id", 155 },
// ...etc.
};
var docType = new DocumentType(typeof(Employee));
var queryLines = new List <SearchQuery.Line>()
{
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Name)}")
, SearchQuery.Op.Equals, ContentFactory.Default.CreateFrom("John Smith")),
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Id)}")
, SearchQuery.Op.Equals, ContentFactory.Default.CreateFrom(1)),
};
var query = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), false, 0, 20);
var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM
( SELECT f1.DocumentId FROM
(
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny]='John Smith' AND [PathId]=150)
UNION ALL
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny]='011.000000' AND [PathId]=155)
) as f1
group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}
) as filtered
LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
WHERE [PathId] = {paths["$.Id"]}) as Sorted
ON Sorted.DocumentId = filtered.DocumentId) AS [A]
INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
WHERE [SourceType] = '{query.DocumentType.Name}') AS [B] ON [A].DocumentId = [B].Id
ORDER BY [A].ValueAsAny
OFFSET {query.Offset} ROWS
FETCH NEXT {query.Limit} ROWS ONLY";
Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
}
internal override SqlExpression VisitColumnRef(SqlColumnRef cref)
{
SqlColumnRef ref2 = this.BubbleUp(cref);
if (ref2 == null)
{
throw Error.ColumnReferencedIsNotInScope(SqlResolver.GetColumnName(cref.Column));
}
return(ref2);
}
public void NotNull()
{
var paths = new Dictionary <string, int>
{
{ "$.Audit.CreatedOn", 199 },
{ "$.Name", 150 },
{ "$.Id", 155 },
{ "$.StartDate", 156 }
// ...etc.
};
var docType = new DocumentType(typeof(Employee));
var queryLines = new List <SearchQuery.Line>()
{
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.StartDate)}")
, SearchQuery.Op.NotEquals, ContentFactory.Default.CreateFrom(null)),
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Name)}")
, SearchQuery.Op.GreaterThanOrEquals, ContentFactory.Default.CreateFrom(new DateTime(2019, 08, 01))),
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Name)}")
, SearchQuery.Op.LessThanOrEquals, ContentFactory.Default.CreateFrom(new DateTime(2019, 11, 27)))
};
var query = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);
var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM
(
SELECT f1.DocumentId FROM
(
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny] IS NOT NULL AND [PathId]=156)
UNION ALL
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny]>='2019-08-01T00:00:00.0000000' AND [PathId]=150)
UNION ALL
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny]<='2019-11-27T00:00:00.0000000' AND [PathId]=150)
) as f1
group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}
) as filtered
LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
WHERE [PathId] = {paths["$.Id"]}) as Sorted
ON Sorted.DocumentId = filtered.DocumentId
) AS [A]
INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
WHERE [SourceType] = '{query.DocumentType}') AS [B] ON [A].DocumentId = [B].Id
ORDER BY [A].ValueAsAny DESC
OFFSET {query.Offset} ROWS
FETCH NEXT {query.Limit} ROWS ONLY";
Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
}
public void AnyOf()
{
var paths = new Dictionary <string, int>
{
{ "$.Audit.CreatedOn", 199 },
{ "$.Name", 150 },
{ "$.Id", 155 },
{ "$.Phones", 156 },
// ...etc.
};
var docType = new DocumentType(typeof(Employee));
var queryLines = new List <SearchQuery.Line>()
{
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Phones)}")
, SearchQuery.Op.AnyOf, ContentFactory.Default.CreateFrom(new string[] { "0001ddd", "0002ddd", "0003ddd" })),
new SearchQuery.Line(
ContentPath.Parse($"$.{nameof(Employee.Name)}")
, SearchQuery.Op.AnyOf, ContentFactory.Default.CreateFrom(new int[] { 1, 2, 3 }))
};
var query = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);
var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM
(
SELECT f1.DocumentId FROM
(
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny] IN ('0001ddd','0002ddd','0003ddd') AND [PathId]=156)
UNION ALL
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK)
WHERE ([ValueAsAny] IN ('011.000000','012.000000','013.000000') AND [PathId]=150)
) as f1
group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}
) as filtered
LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
WHERE [PathId] = 155) as Sorted
ON Sorted.DocumentId = filtered.DocumentId
) AS [A]
INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
WHERE [SourceType] = '{query.DocumentType.Name}') AS [B] ON [A].DocumentId = [B].Id
ORDER BY [A].ValueAsAny DESC
OFFSET {query.Offset} ROWS
FETCH NEXT {query.Limit} ROWS ONLY";
Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
}
internal override SqlExpression VisitTableValuedFunctionCall(SqlTableValuedFunctionCall fc)
{
foreach (SqlColumn column in fc.Columns)
{
if (column == this.match)
{
if (this.found != null)
{
throw Error.ColumnIsDefinedInMultiplePlaces(SqlResolver.GetColumnName(this.match));
}
this.found = column;
return(fc);
}
}
return(fc);
}
internal override SqlTable VisitTable(SqlTable tab)
{
foreach (SqlColumn column in tab.Columns)
{
if (column == this.match)
{
if (this.found != null)
{
throw Error.ColumnIsDefinedInMultiplePlaces(SqlResolver.GetColumnName(this.match));
}
this.found = column;
return(tab);
}
}
return(tab);
}
internal override SqlRow VisitRow(SqlRow row)
{
foreach (SqlColumn column in row.Columns)
{
if (RefersToColumn(column, this.match))
{
if (this.found != null)
{
throw Error.ColumnIsDefinedInMultiplePlaces(SqlResolver.GetColumnName(this.match));
}
this.found = column;
return(row);
}
}
return(row);
}
internal override SqlSelect VisitSelect(SqlSelect select)
{
this.Visit(select.Row);
if (this.found == null)
{
this.Visit(select.From);
if (this.found == null)
{
return(select);
}
if (select.IsDistinct && !this.match.IsConstantColumn)
{
throw Error.ColumnIsNotAccessibleThroughDistinct(SqlResolver.GetColumnName(this.match));
}
if ((select.GroupBy.Count != 0) && !this.IsFoundInGroup(select))
{
throw Error.ColumnIsNotAccessibleThroughGroupBy(SqlResolver.GetColumnName(this.match));
}
this.ForceLocal(select.Row, this.found.Name);
}
return(select);
}
public void EmptyQuery()
{
var paths = new Dictionary <string, int>
{
{ "$.Audit.CreatedOn", 199 },
{ "$.Id", 155 },
// ...etc.
};
var docType = new DocumentType(typeof(Employee));
var queryLines = new List <SearchQuery.Line>();
var query = new SearchQuery(docType, queryLines, ContentPath.Parse($"$.{nameof(Employee.Id)}"), true, 0, 20);
var actual = SqlResolver.ResolveSqlText(query, (s) => paths[s], "[search].[Docs]", "[search].[DocTokens]");
var expectedWithSpaces = $@"SELECT [B].* FROM (SELECT DISTINCT filtered.DocumentId, Sorted.ValueAsAny FROM
(
SELECT f1.DocumentId FROM
(
SELECT DocumentId FROM [search].[DocTokens] WITH (NOLOCK) Group by [DocumentId]
) as f1
group by f1.DocumentId HAVING count(*) >= {query.QueryLines.Length}
) as filtered
LEFT JOIN (SELECT DocumentId,ValueAsAny FROM [search].[DocTokens] WITH (NOLOCK)
WHERE [PathId] = {paths["$.Id"]}) as Sorted
ON Sorted.DocumentId = filtered.DocumentId
) AS [A]
INNER JOIN (SELECT * FROM [search].[Docs] WITH (NOLOCK)
WHERE [SourceType] = '{query.DocumentType}') AS [B] ON [A].DocumentId = [B].Id
ORDER BY [A].ValueAsAny DESC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY";
Assert.AreEqual(Regex.Replace(expectedWithSpaces, @"\s+", string.Empty), Regex.Replace(actual, @"\s+", string.Empty));
}