private void addJoinItem(SelectBuilder builder, Join join)
{
JoinStart start = join as JoinStart;
if (start == null)
{
builder.AddJoin(join);
return;
}
AliasedSource source = start.Source;
Table table = source.Source as Table;
if (table != null)
{
builder.AddTable(table, source.Alias);
return;
}
ISelectBuilder select = source.Source as SelectBuilder;
if (select != null)
{
builder.AddSelect(select, source.Alias);
return;
}
Function functionCall = source.Source as Function;
if (functionCall != null)
{
builder.AddFunction(functionCall, source.Alias);
return;
}
throw new InvalidOperationException();
}
public void TestFilterGroup_Optimize_SimplifiesConditions()
{
FilterGroup topFilter = new FilterGroup(Conjunction.Or,
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("FirstName"), new StringLiteral("Albert")),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("LastName"), new StringLiteral("Einstein")))),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("FirstName"), new StringLiteral("Max")),
new FilterGroup(Conjunction.And,
new EqualToFilter(new Column("LastName"), new StringLiteral("Planck")))));
wrapInParentheses(topFilter, true);
SelectBuilder selectBuilder = new SelectBuilder();
selectBuilder.AddTable(new Table("Person"));
selectBuilder.AddProjection(new Column("FirstName"));
selectBuilder.AddProjection(new Column("LastName"));
selectBuilder.AddWhere(topFilter);
Formatter formatter = new Formatter();
string beforeActual = formatter.GetCommandText(selectBuilder);
const string beforeExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND ((LastName = 'Einstein'))) OR ((FirstName = 'Max') AND ((LastName = 'Planck'))))";
Assert.AreEqual(beforeExpected, beforeActual, "The initial query had an unexpected string representation.");
wrapInParentheses(topFilter, false);
topFilter.Optimize();
wrapInParentheses(topFilter, true);
string afterActual = formatter.GetCommandText(selectBuilder, new CommandOptions() { WrapFiltersInParentheses = true });
const string afterExpected = "SELECT FirstName, LastName FROM Person WHERE (((FirstName = 'Albert') AND (LastName = 'Einstein')) OR ((FirstName = 'Max') AND (LastName = 'Planck')))";
Assert.AreEqual(afterExpected, afterActual, "The optimized query had an unexpected string representation.");
}
public void TestSelect_ExistsFilter()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
SelectBuilder inner = new SelectBuilder();
inner.AddProjection(new NumericLiteral(1));
ExistsFilter filter = new ExistsFilter(inner);
builder.AddWhere(filter);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE EXISTS(SELECT 1)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_Modulus()
{
SelectBuilder builder = new SelectBuilder();
builder.AddProjection(new Modulus(new NumericLiteral(1), new NumericLiteral(1)));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT (1 % 1)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
private void buildGroupByList(MatchResult result, SelectBuilder builder)
{
MatchResult multiple = result.Matches[SqlGrammar.GroupByList.Multiple.Name];
if (multiple.IsMatch)
{
MatchResult firstResult = multiple.Matches[SqlGrammar.GroupByList.Multiple.First];
IGroupByItem first = (IGroupByItem)buildArithmeticItem(firstResult);
builder.AddGroupBy(first);
MatchResult remainingResult = multiple.Matches[SqlGrammar.GroupByList.Multiple.Remaining];
buildGroupByList(remainingResult, builder);
return;
}
MatchResult single = result.Matches[SqlGrammar.GroupByList.Single];
if (single.IsMatch)
{
IGroupByItem item = (IGroupByItem)buildArithmeticItem(single);
builder.AddGroupBy(item);
return;
}
throw new InvalidOperationException();
}
public void TestSelect_FromSelect()
{
SelectBuilder builder = new SelectBuilder();
SelectBuilder inner = new SelectBuilder();
inner.AddProjection(new NumericLiteral(1), "Column");
builder.AddSelect(inner);
builder.AddProjection(new Column("Column"));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Column FROM (SELECT 1 AS Column)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
private void buildProjectionList(MatchResult result, SelectBuilder builder)
{
MatchResult multiple = result.Matches[SqlGrammar.ProjectionList.Multiple.Name];
if (multiple.IsMatch)
{
MatchResult first = multiple.Matches[SqlGrammar.ProjectionList.Multiple.First];
buildProjectionItem(first, builder);
MatchResult remaining = multiple.Matches[SqlGrammar.ProjectionList.Multiple.Remaining];
buildProjectionList(remaining, builder);
return;
}
MatchResult single = result.Matches[SqlGrammar.ProjectionList.Single];
if (single.IsMatch)
{
buildProjectionItem(single, builder);
return;
}
throw new InvalidOperationException();
}
private Top buildTop(MatchResult result, SelectBuilder builder)
{
MatchResult expressionResult = result.Matches[SqlGrammar.SelectSpecification.Top.Expression];
IProjectionItem expression = (IProjectionItem)buildArithmeticItem(expressionResult);
Top top = new Top(expression);
MatchResult percentResult = result.Matches[SqlGrammar.SelectSpecification.Top.PercentKeyword];
top.IsPercent = percentResult.IsMatch;
MatchResult withTiesResult = result.Matches[SqlGrammar.SelectSpecification.Top.WithTiesKeyword];
top.WithTies = withTiesResult.IsMatch;
return top;
}
public void TestSelect_InFilter_Negated()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
builder.AddWhere(new InFilter(table.Column("Column"), new ValueList(new NumericLiteral(1), new NumericLiteral(2), new NumericLiteral(3))) { Not = true });
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column NOT IN (1, 2, 3)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_MultipleProjections()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(new NumericLiteral(1));
builder.AddProjection(new StringLiteral("Hello"));
builder.AddProjection(new NullLiteral());
builder.AddProjection(new Function("IsNull", table.Column("Column"), new NumericLiteral(123)));
builder.AddProjection(table.Column("Column"));
SelectBuilder inner = new SelectBuilder();
inner.AddProjection(new NumericLiteral(1234));
builder.AddProjection(inner);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT 1, 'Hello', NULL, IsNull(Table.Column, 123), Table.Column, (SELECT 1234) FROM Table";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_GroupBy()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(new Function("COUNT", new NumericLiteral(1)));
builder.AddGroupBy(table.Column("Column"));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT COUNT(1) FROM Table GROUP BY Table.Column";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_InFilter_FunctionSource()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
builder.AddWhere(new InFilter(table.Column("Column"), new Function("GetData")));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column IN GetData()";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
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.");
}
public void TestSelect_MultipleJoinItems()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table1 = builder.AddTable(new Table("Table1"));
AliasedSource table2 = builder.AddTable(new Table("Table2"));
builder.AddProjection(table1.Column("Column"));
builder.AddProjection(table2.Column("Column"));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table1.Column, Table2.Column FROM Table1, Table2";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_LeftOuterJoin()
{
SelectBuilder builder = new SelectBuilder();
Join join = Join.From(new Table("Table1"))
.LeftOuterJoin(new Table("Table2"))
.On(j => new EqualToFilter(j.Sources["Table1"].Column("Column"), j.Sources["Table2"].Column("Column")));
builder.AddJoin(join);
builder.AddProjection(new AllColumns());
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Column = Table2.Column";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_MultipleOrderByItems()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column1"));
builder.AddProjection(table.Column("Column2"));
builder.AddOrderBy(new OrderBy(table.Column("Column1")));
builder.AddOrderBy(new OrderBy(table.Column("Column2")));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column1, Table.Column2 FROM Table ORDER BY Table.Column1, Table.Column2";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_LessThanEqualTo()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
builder.AddWhere(new LessThanEqualToFilter(table.Column("Column"), new NumericLiteral(1)));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column <= 1";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
private void buildProjectionItem(MatchResult result, SelectBuilder builder)
{
MatchResult expression = result.Matches[SqlGrammar.ProjectionItem.Expression.Name];
if (expression.IsMatch)
{
MatchResult itemResult = expression.Matches[SqlGrammar.ProjectionItem.Expression.Item];
IProjectionItem item = (IProjectionItem)buildArithmeticItem(itemResult);
string alias = null;
MatchResult aliasExpression = expression.Matches[SqlGrammar.ProjectionItem.Expression.AliasExpression.Name];
if (aliasExpression.IsMatch)
{
MatchResult aliasResult = aliasExpression.Matches[SqlGrammar.ProjectionItem.Expression.AliasExpression.Alias];
alias = getToken(aliasResult);
}
builder.AddProjection(item, alias);
return;
}
MatchResult star = result.Matches[SqlGrammar.ProjectionItem.Star.Name];
if (star.IsMatch)
{
AliasedSource source = null;
MatchResult qualifier = star.Matches[SqlGrammar.ProjectionItem.Star.Qualifier.Name];
if (qualifier.IsMatch)
{
MatchResult columnSource = qualifier.Matches[SqlGrammar.ProjectionItem.Star.Qualifier.ColumnSource];
List<string> parts = new List<string>();
buildMultipartIdentifier(columnSource, parts);
string sourceName = parts[parts.Count - 1];
source = scope.GetSource(sourceName);
}
AllColumns all = new AllColumns(source);
builder.AddProjection(all);
return;
}
throw new InvalidOperationException();
}
public void TestSelect_LikeFilter_Negated()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
builder.AddWhere(new LikeFilter(table.Column("Column"), new StringLiteral("%Bob%")) { Not = true });
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column NOT LIKE '%Bob%'";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
private ISelectBuilder buildSelectSpecification(MatchResult result)
{
SelectBuilder builder = new SelectBuilder();
MatchResult distinctQualifier = result.Matches[SqlGrammar.SelectSpecification.DistinctQualifier];
if (distinctQualifier.IsMatch)
{
builder.Distinct = buildDistinctQualifier(distinctQualifier);
}
MatchResult top = result.Matches[SqlGrammar.SelectSpecification.Top.Name];
if (top.IsMatch)
{
builder.Top = buildTop(top, builder);
}
MatchResult from = result.Matches[SqlGrammar.SelectSpecification.From.Name];
if (from.IsMatch)
{
MatchResult fromList = from.Matches[SqlGrammar.SelectSpecification.From.FromList];
buildFromList(fromList, builder);
}
scope.Push(builder.Sources);
MatchResult projectionList = result.Matches[SqlGrammar.SelectSpecification.ProjectionList];
buildProjectionList(projectionList, builder);
MatchResult where = result.Matches[SqlGrammar.SelectSpecification.Where.Name];
if (where.IsMatch)
{
MatchResult filterList = where.Matches[SqlGrammar.SelectSpecification.Where.FilterList];
IFilter innerFilter = buildOrFilter(filterList);
builder.WhereFilterGroup.AddFilter(innerFilter);
builder.WhereFilterGroup.Optimize();
}
MatchResult groupBy = result.Matches[SqlGrammar.SelectSpecification.GroupBy.Name];
if (groupBy.IsMatch)
{
MatchResult groupByList = groupBy.Matches[SqlGrammar.SelectSpecification.GroupBy.GroupByList];
buildGroupByList(groupByList, builder);
}
MatchResult having = result.Matches[SqlGrammar.SelectSpecification.Having.Name];
if (having.IsMatch)
{
MatchResult filterList = having.Matches[SqlGrammar.SelectSpecification.Having.FilterList];
IFilter innerFilter = buildOrFilter(filterList);
builder.HavingFilterGroup.AddFilter(innerFilter);
builder.HavingFilterGroup.Optimize();
}
scope.Pop();
return builder;
}
/// <summary>
/// Visits a SelectBuilder builder.
/// </summary>
/// <param name="item">The item to visit.</param>
protected internal virtual void VisitSelect(SelectBuilder item)
{
}
private void buildFromList(MatchResult result, SelectBuilder builder)
{
MatchResult multiple = result.Matches[SqlGrammar.FromList.Multiple.Name];
if (multiple.IsMatch)
{
MatchResult first = multiple.Matches[SqlGrammar.FromList.Multiple.First];
Join join = buildJoin(first, false);
addJoinItem(builder, join);
MatchResult remaining = multiple.Matches[SqlGrammar.FromList.Multiple.Remaining];
buildFromList(remaining, builder);
return;
}
MatchResult single = result.Matches[SqlGrammar.FromList.Single];
if (single.IsMatch)
{
Join join = buildJoin(single, false);
addJoinItem(builder, join);
return;
}
throw new InvalidOperationException();
}
private void visitSelect(SelectBuilder item)
{
bool needsParentheses = level > 0;
if (needsParentheses)
{
writer.Write("(");
}
writer.Write("SELECT ");
if (item.Distinct != DistinctQualifier.Default)
{
DistinctQualifierConverter converter = new DistinctQualifierConverter();
writer.Write(converter.ToString(item.Distinct));
writer.Write(" ");
}
if (item.Top != null)
{
IVisitableBuilder top = item.Top;
top.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference));
writer.Write(" ");
}
forValueContext(ValueReferenceType.Declaration).join(", ", item.Projection);
if (item.From.Any())
{
writer.Write(" FROM ");
forSourceContext(SourceReferenceType.Declaration).join(", ", item.From);
}
if (item.WhereFilterGroup.HasFilters)
{
writer.Write(" WHERE ");
IFilter where = item.WhereFilterGroup;
where.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference));
}
if (item.GroupBy.Any())
{
writer.Write(" GROUP BY ");
forValueContext(ValueReferenceType.Reference).join(", ", item.GroupBy);
}
if (item.HavingFilterGroup.HasFilters)
{
writer.Write(" HAVING ");
IFilter having = item.HavingFilterGroup;
having.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference));
}
if (item.OrderBy.Any())
{
writer.Write(" ORDER BY ");
forValueContext(ValueReferenceType.Alias).join(", ", item.OrderBy);
}
if (needsParentheses)
{
writer.Write(")");
}
}
public void TestSelect_MatchCase_Else()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
MatchCase options = new MatchCase(column);
options.AddBranch(new StringLiteral("Admin"), new StringLiteral("Administrator"));
options.Default = new StringLiteral("User");
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE Table.Column WHEN 'Admin' THEN 'Administrator' ELSE 'User' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
public void TestSelect_FromFunction()
{
SelectBuilder builder = new SelectBuilder();
builder.AddFunction(new Function("GetData"), "F");
builder.AddProjection(builder.Sources["F"].Column("Column"));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT F.Column FROM GetData() F";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_MatchCase_MultipleCases()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
MatchCase options = new MatchCase(column);
options.AddBranch(new NumericLiteral(0), new StringLiteral("Sunday"));
options.AddBranch(new NumericLiteral(1), new StringLiteral("Monday"));
options.AddBranch(new NumericLiteral(2), new StringLiteral("Tuesday"));
options.AddBranch(new NumericLiteral(3), new StringLiteral("Wednesday"));
options.AddBranch(new NumericLiteral(4), new StringLiteral("Thursday"));
options.AddBranch(new NumericLiteral(5), new StringLiteral("Friday"));
options.AddBranch(new NumericLiteral(6), new StringLiteral("Saturday"));
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE Table.Column WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' WHEN 2 THEN 'Tuesday' WHEN 3 THEN 'Wednesday' WHEN 4 THEN 'Thursday' WHEN 5 THEN 'Friday' WHEN 6 THEN 'Saturday' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
public void TestSelect_Minus()
{
SelectBuilder builder = new SelectBuilder();
builder.AddProjection(new NumericLiteral(1));
Minus union = new Minus(builder, builder);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(union);
string expected = "SELECT 1 MINUS SELECT 1";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
/// <summary>
/// Generates the text for a Select builder.
/// </summary>
/// <param name="item">The Select builder to generate the text for.</param>
protected internal override void VisitSelect(SelectBuilder item)
{
forCommandType(CommandType.Select).visitSelect(item);
}
public void TestSelect_Distinct()
{
SelectBuilder builder = new SelectBuilder();
builder.Distinct = DistinctQualifier.Distinct;
builder.AddProjection(new NumericLiteral(1));
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT DISTINCT 1";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}