AOO410/main/dbaccess/qa/complex/dbaccess/Parser.java

/**************************************************************
 * 
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 * 
 *************************************************************/


package complex.dbaccess;

import com.sun.star.beans.XPropertySet;
import com.sun.star.container.XIndexAccess;
import com.sun.star.sdb.XParametersSupplier;
import com.sun.star.sdb.XSingleSelectQueryComposer;
import com.sun.star.sdbc.DataType;
import com.sun.star.sdbc.SQLException;
import com.sun.star.uno.Exception;
import com.sun.star.uno.UnoRuntime;


// ---------- junit imports -----------------
import org.junit.Test;
import static org.junit.Assert.*;
// ------------------------------------------

public class Parser extends CRMBasedTestCase
{
    // --------------------------------------------------------------------------------------------------------
    @Override
    protected void createTestCase()
    {
        try
        {
            super.createTestCase();
            m_database.getDatabase().getDataSource().createQuery( "query products", "SELECT * FROM \"products\"" );
        }
        catch ( Exception e )
        {
            e.printStackTrace( System.err );
            fail( "caught an exception (" + e.getMessage() + ") while creating the test case");
        }
    }

    // --------------------------------------------------------------------------------------------------------
    @Test
    public void checkWhere() throws Exception
    {
        final XSingleSelectQueryComposer composer = createQueryComposer();
        final String SELECT = "SELECT \"products\".\"Name\" FROM \"products\" WHERE ";
        final String[] queries = new String[]
        {
             "\"ID\" in ( 1,2,3,4)"
            ,"not ( \"ID\" in ( 1,2,3,4))"
            ,"(1 = 1) is true"
            ,"(1 = 1) is not false"
            ,"(1 = 1) is not null"
            ,"not ( (1 = 1) is not null)"
            ,"'a' like 'a%'"
            ,"not ( 'a' like 'a%')"
            ,"'a' not like 'a%'"
            ,"1 between 0 and 2"
            ,"not ( 1 between 0 and 2 )"
            ,"1 not between 3 and 4"
            ,"1 not between ( select \"ID\" from \"categories\") and ( select \"ID\" from \"categories\")"
            ,"1 = 1"
            ,"0 < 1"
            ,"not(0 < 1)"
            ,"1 > 0"
            ,"not(1 > 0)"
            ,"1 <> 0"
            ,"(1 <> 0 and 'a' = 'a' and 'c' = 'd') or (1 = 1 and 2 = 2 and 3 = 4)"
            ,"not ( 1 <> 0 )"
            ,"\"CategoryID\" in ( select \"ID\" from \"categories\")"
            ,"not (\"CategoryID\" in ( select \"ID\" from \"categories\"))"
            ,"\"CategoryID\" not in ( select \"ID\" from \"categories\")"
        };
        for (int i = 0; i < queries.length; i++)
        {
            composer.setQuery( SELECT + queries[i]);
        }
    }
    // --------------------------------------------------------------------------------------------------------
    /** verifies that aliases for inner queries work as expected
     */
    @Test
    public void checkJoinSyntax() throws Exception
    {
        final XSingleSelectQueryComposer composer = createQueryComposer();

        // feed the composer with some statements. If any of those cannot be parsed, the composer
        // will throw an exception - which is a regression then
        composer.setQuery(
            "SELECT \"categories\".\"Name\", " +
                    "\"products\".\"Name\" " +
            "FROM \"products\" RIGHT OUTER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" );

        composer.setQuery(
            "SELECT \"categories\".\"Name\", " +
                    "\"products\".\"Name\" " +
            "FROM \"products\" LEFT OUTER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" );

        composer.setQuery(
            "SELECT \"categories\".\"Name\", " +
                    "\"products\".\"Name\" " +
            "FROM \"products\" CROSS JOIN \"categories\" AS \"categories\"" );

        composer.setQuery(
            "SELECT \"categories\".\"Name\", " +
                    "\"products\".\"Name\" " +
            "FROM \"products\" INNER JOIN \"categories\" AS \"categories\" ON \"products\".\"CategoryID\" = \"categories\".\"ID\"" );

        // just to be sure the composer *really* parses upon setting the query: feed it with
        // an unparseable statement
        boolean caughtExpected = false;
        try
        {
            composer.setQuery( "NONSENSE" );
        }
        catch( SQLException e )
        {
            caughtExpected = true;
        }
        assertTrue( "pre-condition not met: parser should except on unparseable statements, else the complete" +
            "test is bogus!", caughtExpected );
    }

    // --------------------------------------------------------------------------------------------------------
    private void impl_checkParameters( final String _statement, final String[] _expectedParameterNames, final int[] _expectedParameterTypes,final String _context ) throws Exception
    {
        final XSingleSelectQueryComposer composer = createQueryComposer();
        composer.setQuery( _statement );

        assertEquals( "checkParameterTypes: internal error", _expectedParameterNames.length, _expectedParameterTypes.length );

        final XParametersSupplier paramSupp = UnoRuntime.queryInterface(XParametersSupplier.class, composer);
        final XIndexAccess parameters = paramSupp.getParameters();

        assertEquals( "(ctx: " + _context + ") unexpected parameter count", _expectedParameterNames.length, parameters.getCount() );
        for ( int i=0; i<parameters.getCount(); ++i )
        {
            final XPropertySet parameter = UnoRuntime.queryInterface(XPropertySet.class, parameters.getByIndex(i));

            final String name = (String)parameter.getPropertyValue( "Name" );
            assertEquals( "(ctx: " + _context + ") unexpected parameter name for parameter number " + ( i + 1 ), _expectedParameterNames[i], name );

            final int type = ((Integer)parameter.getPropertyValue( "Type" )).intValue();
            assertEquals( "(ctx: " + _context + ") unexpected data type for parameter number " + ( i + 1 ), _expectedParameterTypes[i], type );
        }
    }

    // --------------------------------------------------------------------------------------------------------
    /** verifies that the parser properly recognizes the types of parameters
     */
    @Test
    public void checkParameterTypes() throws Exception
    {
        impl_checkParameters(
            "SELECT * FROM \"all orders\" " +
            "WHERE ( \"Order Date\" >= :order_date ) " +
            "  AND (    ( \"Customer Name\" LIKE :customer ) " +
            "      OR   ( \"Product Name\" LIKE ? ) " +
            "      )",
            new String[] { "order_date", "customer", "Product Name" },
            new int[] { DataType.DATE, DataType.VARCHAR, DataType.VARCHAR },
            ">= && LIKE"
        );

        impl_checkParameters(
            "SELECT * FROM \"categories\" " +
            "WHERE \"ID\" BETWEEN :id_lo AND :id_hi",
            new String[] { "id_lo", "id_hi" },
            new int[] { DataType.INTEGER, DataType.INTEGER },
            "BETWEEN"
        );

        impl_checkParameters(
            "SELECT CONCAT( :prefix, CONCAT( \"Name\", :suffix ) ) FROM \"customers\"",
            new String[] { "prefix", "suffix" },
            new int[] { DataType.VARCHAR, DataType.VARCHAR },
            "CONCAT"
        );
    }
}