@Test
  public void testLikeOperatorNonCaseSensitive() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(likeTo(COLUMN_CITY, "Bar%"))
                .column(COLUMN_ID)
                .sort(COLUMN_ID, SortOrder.ASCENDING)
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(6);
    assertDataSetValue(result, 0, 0, "1.00");
    assertDataSetValue(result, 5, 0, "6.00");

    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(likeTo(COLUMN_CITY, "%L%", false /* Case un-sensitive */))
                .buildLookup());

    assertThat(result.getRowCount()).isEqualTo(26);
  }
  @Test
  public void testFilterMultiple() throws Exception {
    Calendar c = Calendar.getInstance();
    c.set(2015, 0, 0, 0, 0);
    Timestamp date = new Timestamp(c.getTime().getTime());

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("date", greaterThan(date))
                .filter("amount", lowerOrEqualsTo(120.35))
                .filter("city", notEqualsTo("Barcelona"))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(2);
    assertDataSetValue(result, 0, 0, "9.00");
    assertDataSetValue(result, 1, 0, "10.00");

    // The order of the filter criteria does not alter the result.
    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("city", notEqualsTo("Barcelona"))
                .filter("amount", lowerOrEqualsTo(120.35))
                .filter("date", greaterThan(date))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(2);
    assertDataSetValue(result, 0, 0, "9.00");
    assertDataSetValue(result, 1, 0, "10.00");
  }
  @Test
  public void testLogicalExprNonEmpty() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(AND(COLUMN_EMPLOYEE, new ArrayList<ColumnFilter>()))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(50);

    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(OR(COLUMN_EMPLOYEE, new ArrayList<ColumnFilter>()))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(50);

    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(NOT(COLUMN_EMPLOYEE, new ArrayList<ColumnFilter>()))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(50);
  }
  @Test
  public void testFilterByStringWithPreProcessor() throws Exception {
    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS + "2")
                .filter(COLUMN_CITY, equalsTo("Barcelona"))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(0);
  }
  @Test
  public void testFilterByString() throws Exception {
    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("city", equalsTo("Barcelona"))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(6);
    assertDataSetValue(result, 0, 0, "1.00");
    assertDataSetValue(result, 5, 0, "6.00");
  }
  @Test
  public void testANDExpression() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("amount", AND(greaterThan(100), lowerThan(150)))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(1);
    assertDataSetValue(result, 0, 0, "1.00");
  }
  @Test
  public void testLikeOperator() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(likeTo("city", "Bar%"))
                .column("id")
                .sort("id", SortOrder.ASCENDING)
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(6);
    assertDataSetValue(result, 0, 0, "1.00");
    assertDataSetValue(result, 5, 0, "6.00");

    // Case sensitive
    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(likeTo("city", "%L%", true))
                .buildLookup());

    assertThat(result.getRowCount()).isEqualTo(7);

    // Case un-sensitive
    result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(likeTo("city", "%L%", false))
                .buildLookup());

    assertThat(result.getRowCount()).isEqualTo(26);
  }
  @Test
  public void testNOTExpression() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(COLUMN_AMOUNT, NOT(greaterThan(100)))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(5);
    assertDataSetValue(result, 0, 0, "9.00");
    assertDataSetValue(result, 4, 0, "30.00");
  }
  @Test
  public void testFilterByDate() throws Exception {
    Calendar c = Calendar.getInstance();
    c.set(2015, 0, 0, 0, 0);
    Timestamp date = new Timestamp(c.getTime().getTime());

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("date", greaterThan(new Timestamp(date.getTime())))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(15);
  }
  @Test
  public void testFilterByNumber() throws Exception {
    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter("amount", between(100, 200))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(5);
    assertDataSetValue(result, 0, 0, "1.00");
    assertDataSetValue(result, 1, 0, "6.00");
    assertDataSetValue(result, 2, 0, "10.00");
    assertDataSetValue(result, 3, 0, "17.00");
    assertDataSetValue(result, 4, 0, "33.00");
  }
  @Test
  public void testORExpressionMultilple() throws Exception {

    List<Comparable> cities = new ArrayList<Comparable>();
    for (String city : new String[] {"Barcelona", "London", "Madrid"}) {
      cities.add(city);
    }

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(equalsTo(COLUMN_CITY, cities))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(19);
  }
  @Test
  public void testCombinedExpression() throws Exception {

    DataSet result =
        dataSetManager.lookupDataSet(
            DataSetFactory.newDataSetLookupBuilder()
                .dataset(EXPENSE_REPORTS)
                .filter(
                    "amount",
                    AND(
                        equalsTo("department", "Sales"),
                        OR(NOT(lowerThan(300)), equalsTo("city", "Madrid"))))
                .buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(7);
    assertDataSetValue(result, 0, 0, "9.00");
    assertDataSetValue(result, 6, 0, "28.00");
  }
  @Test
  public void testCombinedExpression2() throws Exception {

    List<Comparable> cities = new ArrayList<Comparable>();
    for (String city : new String[] {"Barcelona", "London", "Madrid"}) {
      cities.add(city);
    }

    List<ColumnFilter> condList = new ArrayList<ColumnFilter>();
    for (String employee : new String[] {"Roxie Foraker", "Patricia J. Behr"}) {
      condList.add(equalsTo(employee));
    }

    ColumnFilter filter1 = equalsTo(COLUMN_CITY, cities);
    ColumnFilter filter2 =
        AND(OR(COLUMN_EMPLOYEE, condList), equalsTo(COLUMN_DEPARTMENT, "Engineering"));
    ColumnFilter filter3 = equalsTo(COLUMN_DEPARTMENT, "Services");

    DataSetLookupBuilder builder = DataSetFactory.newDataSetLookupBuilder();
    builder.dataset(EXPENSE_REPORTS);
    builder.filter(AND(filter1, OR(filter2, filter3)));
    builder.column(COLUMN_ID);
    builder.column(COLUMN_CITY);
    builder.column(COLUMN_DEPARTMENT);
    builder.column(COLUMN_EMPLOYEE);
    builder.column(COLUMN_AMOUNT);
    builder.column(COLUMN_DATE);

    //  (CITY = Barcelona, London, Madrid
    //     AND (((EMPLOYEE = Roxie Foraker OR EMPLOYEE = Patricia J. Behr) AND DEPARTMENT =
    // Engineering)
    //            OR DEPARTMENT = Services))
    DataSet result = dataSetManager.lookupDataSet(builder.buildLookup());

    // printDataSet(result);
    assertThat(result.getRowCount()).isEqualTo(8);
    assertDataSetValue(result, 0, 0, "1.00");
    assertDataSetValue(result, 7, 0, "8.00");
  }