public void TestSelect_FunctionWithStartFraming()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource sale = builder.AddTable(new Table("sale"));
AliasedProjection productId = builder.AddProjection(sale.Column("prod_id"));
AliasedProjection monthNumber = builder.AddProjection(sale.Column("month_num"));
AliasedProjection sales = builder.AddProjection(sale.Column("sales"));
Function function = new Function("SUM", sales.ProjectionItem);
FunctionWindow window = new FunctionWindow();
window.AddPartition(productId);
window.AddOrderBy(new OrderBy(monthNumber));
window.Frame = new PrecedingOnlyWindowFrame(new PrecedingBoundFrame(12));
function.FunctionWindow = window;
builder.AddProjection(function);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT sale.prod_id, sale.month_num, sale.sales, SUM(sale.sales) OVER (PARTITION BY sale.prod_id ORDER BY sale.month_num ROWS 12 PRECEDING) FROM sale";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
private Function buildFunctionCall(MatchResult result)
{
MatchResult functionNameResult = result.Matches[SqlGrammar.FunctionCall.FunctionName];
List<string> parts = new List<string>();
buildMultipartIdentifier(functionNameResult, parts);
Namespace qualifier = getNamespace(parts.Take(parts.Count - 1));
string functionName = parts[parts.Count - 1];
Function function = new Function(qualifier, functionName);
MatchResult argumentsResult = result.Matches[SqlGrammar.FunctionCall.Arguments];
if (argumentsResult.IsMatch)
{
ValueList arguments = new ValueList();
buildValueList(argumentsResult, arguments);
foreach (IProjectionItem value in arguments.Values)
{
function.AddArgument(value);
}
}
MatchResult windowResult = result.Matches[SqlGrammar.FunctionCall.Window.Name];
if (windowResult.IsMatch)
{
FunctionWindow window = new FunctionWindow();
MatchResult partitioning = windowResult.Matches[SqlGrammar.FunctionCall.Window.Partitioning.Name];
if (partitioning.IsMatch)
{
MatchResult valueListResult = partitioning.Matches[SqlGrammar.FunctionCall.Window.Partitioning.ValueList];
ValueList valueList = new ValueList();
buildValueList(valueListResult, valueList);
foreach (IProjectionItem value in valueList.Values)
{
window.AddPartition(value);
}
}
MatchResult ordering = windowResult.Matches[SqlGrammar.FunctionCall.Window.Ordering.Name];
if (ordering.IsMatch)
{
MatchResult orderByListResult = ordering.Matches[SqlGrammar.FunctionCall.Window.Ordering.OrderByList];
buildOrderByList(orderByListResult, window.OrderByList);
}
MatchResult framing = windowResult.Matches[SqlGrammar.FunctionCall.Window.Framing.Name];
if (framing.IsMatch)
{
MatchResult precedingOnlyFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.PrecedingFrame];
if (precedingOnlyFrameResult.IsMatch)
{
IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingOnlyFrameResult);
window.Frame = new PrecedingOnlyWindowFrame(precedingFrame);
}
MatchResult betweenFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.Name];
if (betweenFrameResult.IsMatch)
{
MatchResult precedingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.PrecedingFrame];
IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingFrameResult);
MatchResult followingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.FollowingFrame];
IFollowingFrame followingFrame = buildFollowingFrame(followingFrameResult);
window.Frame = new BetweenWindowFrame(precedingFrame, followingFrame);
}
MatchResult frameTypeResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.FrameType];
window.Frame.FrameType = buildFrameType(frameTypeResult);
}
function.FunctionWindow = window;
}
return function;
}
public void TestSelect_FunctionWithPartitioning()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Employee"));
AliasedProjection employeeType = builder.AddProjection(table.Column("Type"), "Type");
Function function = new Function("COUNT", new NumericLiteral(1));
FunctionWindow window = new FunctionWindow();
window.AddPartition(employeeType);
function.FunctionWindow = window;
builder.AddProjection(function, "Count");
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT Employee.Type AS Type, COUNT(1) OVER (PARTITION BY Employee.Type) AS Count FROM Employee";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}