QueryBuilderTest.java

package com.vaadin.v7.data.util.sqlcontainer.query;

import java.util.ArrayList;

import org.easymock.EasyMock;
import org.junit.Assert;
import org.junit.Test;

import com.vaadin.v7.data.Container.Filter;
import com.vaadin.v7.data.util.filter.And;
import com.vaadin.v7.data.util.filter.Between;
import com.vaadin.v7.data.util.filter.Compare.Equal;
import com.vaadin.v7.data.util.filter.Compare.Greater;
import com.vaadin.v7.data.util.filter.Compare.GreaterOrEqual;
import com.vaadin.v7.data.util.filter.Compare.Less;
import com.vaadin.v7.data.util.filter.Compare.LessOrEqual;
import com.vaadin.v7.data.util.filter.IsNull;
import com.vaadin.v7.data.util.filter.Like;
import com.vaadin.v7.data.util.filter.Not;
import com.vaadin.v7.data.util.filter.Or;
import com.vaadin.v7.data.util.filter.SimpleStringFilter;
import com.vaadin.v7.data.util.sqlcontainer.query.generator.StatementHelper;
import com.vaadin.v7.data.util.sqlcontainer.query.generator.filter.QueryBuilder;
import com.vaadin.v7.data.util.sqlcontainer.query.generator.filter.StringDecorator;

public class QueryBuilderTest {

    private StatementHelper mockedStatementHelper(Object... values) {
        StatementHelper sh = EasyMock.createMock(StatementHelper.class);
        for (Object val : values) {
            sh.addParameterValue(val);
            EasyMock.expectLastCall();
        }
        EasyMock.replay(sh);
        return sh;
    }

    // escape bad characters and wildcards

    @Test
    public void getWhereStringForFilter_equals() {
        StatementHelper sh = mockedStatementHelper("Fido");
        Equal f = new Equal("NAME", "Fido");
        Assert.assertEquals("\"NAME\" = ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_greater() {
        StatementHelper sh = mockedStatementHelper(18);
        Greater f = new Greater("AGE", 18);
        Assert.assertEquals("\"AGE\" > ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_less() {
        StatementHelper sh = mockedStatementHelper(65);
        Less f = new Less("AGE", 65);
        Assert.assertEquals("\"AGE\" < ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_greaterOrEqual() {
        StatementHelper sh = mockedStatementHelper(18);
        GreaterOrEqual f = new GreaterOrEqual("AGE", 18);
        Assert.assertEquals("\"AGE\" >= ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_lessOrEqual() {
        StatementHelper sh = mockedStatementHelper(65);
        LessOrEqual f = new LessOrEqual("AGE", 65);
        Assert.assertEquals("\"AGE\" <= ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_simpleStringFilter() {
        StatementHelper sh = mockedStatementHelper("Vi%");
        SimpleStringFilter f = new SimpleStringFilter("NAME", "Vi", false,
                true);
        Assert.assertEquals("\"NAME\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_simpleStringFilterMatchAnywhere() {
        StatementHelper sh = mockedStatementHelper("%Vi%");
        SimpleStringFilter f = new SimpleStringFilter("NAME", "Vi", false,
                false);
        Assert.assertEquals("\"NAME\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_simpleStringFilterMatchAnywhereIgnoreCase() {
        StatementHelper sh = mockedStatementHelper("%VI%");
        SimpleStringFilter f = new SimpleStringFilter("NAME", "Vi", true,
                false);
        Assert.assertEquals("UPPER(\"NAME\") LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_startsWith() {
        StatementHelper sh = mockedStatementHelper("Vi%");
        Like f = new Like("NAME", "Vi%");
        Assert.assertEquals("\"NAME\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_startsWithNumber() {
        StatementHelper sh = mockedStatementHelper("1%");
        Like f = new Like("AGE", "1%");
        Assert.assertEquals("\"AGE\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_endsWith() {
        StatementHelper sh = mockedStatementHelper("%lle");
        Like f = new Like("NAME", "%lle");
        Assert.assertEquals("\"NAME\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_contains() {
        StatementHelper sh = mockedStatementHelper("%ill%");
        Like f = new Like("NAME", "%ill%");
        Assert.assertEquals("\"NAME\" LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_between() {
        StatementHelper sh = mockedStatementHelper(18, 65);
        Between f = new Between("AGE", 18, 65);
        Assert.assertEquals("\"AGE\" BETWEEN ? AND ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_caseInsensitive_equals() {
        StatementHelper sh = mockedStatementHelper("FIDO");
        Like f = new Like("NAME", "Fido");
        f.setCaseSensitive(false);
        Assert.assertEquals("UPPER(\"NAME\") LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_caseInsensitive_startsWith() {
        StatementHelper sh = mockedStatementHelper("VI%");
        Like f = new Like("NAME", "Vi%");
        f.setCaseSensitive(false);
        Assert.assertEquals("UPPER(\"NAME\") LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_caseInsensitive_endsWith() {
        StatementHelper sh = mockedStatementHelper("%LLE");
        Like f = new Like("NAME", "%lle");
        f.setCaseSensitive(false);
        Assert.assertEquals("UPPER(\"NAME\") LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilter_caseInsensitive_contains() {
        StatementHelper sh = mockedStatementHelper("%ILL%");
        Like f = new Like("NAME", "%ill%");
        f.setCaseSensitive(false);
        Assert.assertEquals("UPPER(\"NAME\") LIKE ?",
                QueryBuilder.getWhereStringForFilter(f, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_listOfFilters() {
        StatementHelper sh = mockedStatementHelper("%lle", 18);
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Like("NAME", "%lle"));
        filters.add(new Greater("AGE", 18));
        Assert.assertEquals(" WHERE \"NAME\" LIKE ? AND \"AGE\" > ?",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_oneAndFilter() {
        StatementHelper sh = mockedStatementHelper("%lle", 18);
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new And(new Like("NAME", "%lle"), new Greater("AGE", 18)));
        Assert.assertEquals(" WHERE (\"NAME\" LIKE ? AND \"AGE\" > ?)",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_oneOrFilter() {
        StatementHelper sh = mockedStatementHelper("%lle", 18);
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Or(new Like("NAME", "%lle"), new Greater("AGE", 18)));
        Assert.assertEquals(" WHERE (\"NAME\" LIKE ? OR \"AGE\" > ?)",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_complexCompoundFilters() {
        StatementHelper sh = mockedStatementHelper("%lle", 18, 65, "Pelle");
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Or(
                new And(new Like("NAME", "%lle"),
                        new Or(new Less("AGE", 18), new Greater("AGE", 65))),
                new Equal("NAME", "Pelle")));
        Assert.assertEquals(
                " WHERE ((\"NAME\" LIKE ? AND (\"AGE\" < ? OR \"AGE\" > ?)) OR \"NAME\" = ?)",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_complexCompoundFiltersAndSingleFilter() {
        StatementHelper sh = mockedStatementHelper("%lle", 18, 65, "Pelle",
                "Virtanen");
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Or(
                new And(new Like("NAME", "%lle"),
                        new Or(new Less("AGE", 18), new Greater("AGE", 65))),
                new Equal("NAME", "Pelle")));
        filters.add(new Equal("LASTNAME", "Virtanen"));
        Assert.assertEquals(
                " WHERE ((\"NAME\" LIKE ? AND (\"AGE\" < ? OR \"AGE\" > ?)) OR \"NAME\" = ?) AND \"LASTNAME\" = ?",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_emptyList_shouldReturnEmptyString() {
        ArrayList<Filter> filters = new ArrayList<Filter>();
        Assert.assertEquals("", QueryBuilder.getWhereStringForFilters(filters,
                new StatementHelper()));
    }

    @Test
    public void getWhereStringForFilters_NotFilter() {
        StatementHelper sh = mockedStatementHelper(18);
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Not(new Equal("AGE", 18)));
        Assert.assertEquals(" WHERE NOT \"AGE\" = ?",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_complexNegatedFilter() {
        StatementHelper sh = mockedStatementHelper(65, 18);
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(
                new Not(new Or(new Equal("AGE", 65), new Equal("AGE", 18))));
        Assert.assertEquals(" WHERE NOT (\"AGE\" = ? OR \"AGE\" = ?)",
                QueryBuilder.getWhereStringForFilters(filters, sh));
        EasyMock.verify(sh);
    }

    @Test
    public void getWhereStringForFilters_isNull() {
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new IsNull("NAME"));
        Assert.assertEquals(" WHERE \"NAME\" IS NULL", QueryBuilder
                .getWhereStringForFilters(filters, new StatementHelper()));
    }

    @Test
    public void getWhereStringForFilters_isNotNull() {
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Not(new IsNull("NAME")));
        Assert.assertEquals(" WHERE \"NAME\" IS NOT NULL", QueryBuilder
                .getWhereStringForFilters(filters, new StatementHelper()));
    }

    @Test
    public void getWhereStringForFilters_customStringDecorator() {
        QueryBuilder.setStringDecorator(new StringDecorator("[", "]"));
        ArrayList<Filter> filters = new ArrayList<Filter>();
        filters.add(new Not(new IsNull("NAME")));
        Assert.assertEquals(" WHERE [NAME] IS NOT NULL", QueryBuilder
                .getWhereStringForFilters(filters, new StatementHelper()));
        // Reset the default string decorator
        QueryBuilder.setStringDecorator(new StringDecorator("\"", "\""));
    }
}