/// <summary>
/// Visits a FunctionWindow builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitFunctionWindow(FunctionWindow item)
{
}
/// <summary>
/// Generates the text for a FunctionWindow builder.
/// </summary>
/// <param name="item">The FunctionWindow builder to generate the text for.</param>
protected internal override void VisitFunctionWindow(FunctionWindow item)
{
writer.Write("OVER (");
bool needsSpace = false;
if (item.Partition.Any())
{
writer.Write("PARTITION BY ");
forValueContext(ValueReferenceType.Reference).join(", ", item.Partition);
needsSpace = true;
}
if (item.OrderBy.Any())
{
if (needsSpace)
{
writer.Write(" ");
}
writer.Write("ORDER BY ");
forValueContext(ValueReferenceType.Reference).join(", ", item.OrderBy);
needsSpace = true;
}
if (item.Frame != null)
{
if (needsSpace)
{
writer.Write(" ");
}
IVisitableBuilder frame = item.Frame;
frame.Accept(forSubCommand());
}
writer.Write(")");
}
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_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.");
}
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.");
}
public void TestSelect_FunctionWithOrderingWindow()
{
SelectBuilder innerBuilder = new SelectBuilder();
AliasedSource table = innerBuilder.AddTable(new Table("Table"));
Column column1 = table.Column("Column1");
Column column2 = table.Column("Column2");
Column column3 = table.Column("Column3");
innerBuilder.AddProjection(column1, "c1");
Function function = new Function("ROW_NUMBER");
FunctionWindow window = new FunctionWindow();
window.AddOrderBy(new OrderBy(column2));
window.AddOrderBy(new OrderBy(column3));
function.FunctionWindow = window;
innerBuilder.AddProjection(function, "rn");
SelectBuilder builder = new SelectBuilder();
AliasedSource inner = builder.AddSelect(innerBuilder, "inner");
builder.AddProjection(inner.Column("c1"));
builder.AddWhere(new BetweenFilter(inner.Column("rn"), new NumericLiteral(11), new NumericLiteral(20)));
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT inner.c1 FROM (SELECT Table.Column1 AS c1, ROW_NUMBER() OVER (ORDER BY Table.Column2, Table.Column3) AS rn FROM Table) inner WHERE inner.rn BETWEEN 11 AND 20";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
/// <summary>
/// Visits a FunctionWindow builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitFunctionWindow(FunctionWindow item)
{
}
