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."); }