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_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.");
}
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.");
}
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_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_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_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_DisjunctionFilter()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
FilterGroup orGroup = new FilterGroup(Conjunction.Or);
orGroup.AddFilter(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1)));
orGroup.AddFilter(new NullFilter(table.Column("Column")));
builder.AddWhere(orGroup);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE 1 = 1 OR Table.Column IS NULL";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_Any_ValueList()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
ValueList values = new ValueList(new NumericLiteral(1), new NumericLiteral(2), new NumericLiteral(3));
NotEqualToQuantifierFilter filter = new NotEqualToQuantifierFilter(table.Column("Column"), Quantifier.Any, values);
builder.AddWhere(filter);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE Table.Column <> ANY (1, 2, 3)";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
public void TestSelect_WrappedFilters()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource table = builder.AddTable(new Table("Table"));
builder.AddProjection(table.Column("Column"));
FilterGroup group = new FilterGroup() { WrapInParentheses = true };
group.AddFilter(new EqualToFilter(new NumericLiteral(1), new NumericLiteral(1)));
group.AddFilter(new LikeFilter(table.Column("Column"), new StringLiteral("%ABC")));
builder.AddWhere(group);
Formatter formatter = new Formatter();
string commandText = formatter.GetCommandText(builder);
string expected = "SELECT Table.Column FROM Table WHERE (1 = 1 AND Table.Column LIKE '%ABC')";
Assert.AreEqual(expected, commandText, "The wrong SQL was generated.");
}
