/************************************************************** * * 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.PropertyState; import com.sun.star.sdb.SQLFilterOperator; import com.sun.star.beans.PropertyAttribute; import com.sun.star.beans.XPropertySet; import com.sun.star.beans.XPropertyContainer; import com.sun.star.beans.NamedValue; import com.sun.star.container.XNameAccess; import com.sun.star.sdbcx.XTablesSupplier; import com.sun.star.sdb.XParametersSupplier; import com.sun.star.beans.PropertyValue; import com.sun.star.sdbcx.XColumnsSupplier; import com.sun.star.container.XIndexAccess; import com.sun.star.sdb.CommandType; import com.sun.star.sdb.XSingleSelectQueryComposer; import com.sun.star.uno.UnoRuntime; import com.sun.star.sdbc.DataType; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; // ---------- junit imports ----------------- import org.junit.Test; import static org.junit.Assert.*; // ------------------------------------------ public class SingleSelectQueryComposer extends CRMBasedTestCase { private XSingleSelectQueryComposer m_composer = null; private final static String COMPLEXFILTER = "( \"ID\" = 1 AND \"Postal\" = '4' )" + " OR ( \"ID\" = 2 AND \"Postal\" = '5' )" + " OR ( \"ID\" = 3 AND \"Postal\" = '6' AND \"Address\" = '7' )" + " OR ( \"Address\" = '8' )" + " OR ( \"Postal\" = '9' )" + " OR ( NOW( ) = {D '2010-01-01' } )"; private final static String INNERPRODUCTSQUERY = "products (inner)"; // -------------------------------------------------------------------------------------------------------- private void createQueries() throws Exception { m_database.getDatabase().getDataSource().createQuery(INNERPRODUCTSQUERY, "SELECT * FROM \"products\""); } // -------------------------------------------------------------------------------------------------------- @Override protected void createTestCase() { try { super.createTestCase(); createQueries(); m_composer = createQueryComposer(); } catch (Exception e) { fail("caught an exception (" + e.getMessage() + ") while creating the test case"); } } // -------------------------------------------------------------------------------------------------------- private void checkAttributeAccess(String _attributeName, String _attributeValue) { System.out.println("setting " + _attributeName + " to " + _attributeValue); String realValue = null; try { final Class composerClass = m_composer.getClass(); final Method attributeGetter = composerClass.getMethod("get" + _attributeName, new Class[] { }); final Method attributeSetter = composerClass.getMethod("set" + _attributeName, new Class[] { String.class }); attributeSetter.invoke(m_composer, new Object[] { _attributeValue }); realValue = (String) attributeGetter.invoke(m_composer, new Object[] { }); } catch (NoSuchMethodException e) { } catch (IllegalAccessException e) { } catch (InvocationTargetException e) { } assertTrue("set/get" + _attributeName + " not working as expected (set: " + _attributeValue + ", get: " + (realValue != null ? realValue : "null") + ")", realValue.equals(_attributeValue)); System.out.println(" (results in " + m_composer.getQuery() + ")"); } /** tests setCommand of the composer */ @Test public void testSetCommand() { System.out.println("testing SingleSelectQueryComposer's setCommand"); try { final String table = "SELECT * FROM \"customers\""; m_composer.setCommand("customers", CommandType.TABLE); assertTrue("setCommand/getQuery TABLE inconsistent", m_composer.getQuery().equals(table)); m_database.getDatabase().getDataSource().createQuery("set command test", "SELECT * FROM \"orders for customer\" \"a\", \"customers\" \"b\" WHERE \"a\".\"Product Name\" = \"b\".\"Name\""); m_composer.setCommand("set command test", CommandType.QUERY); assertTrue("setCommand/getQuery QUERY inconsistent", m_composer.getQuery().equals(m_database.getDatabase().getDataSource().getQueryDefinition("set command test").getCommand())); final String sql = "SELECT * FROM \"orders for customer\" WHERE \"Product Name\" = 'test'"; m_composer.setCommand(sql, CommandType.COMMAND); assertTrue("setCommand/getQuery COMMAND inconsistent", m_composer.getQuery().equals(sql)); } catch (Exception e) { fail("Exception caught: " + e); } } /** tests accessing attributes of the composer (order, filter, group by, having) */ @Test public void testAttributes() { System.out.println("testing SingleSelectQueryComposer's attributes (order, filter, group by, having)"); try { System.out.println("check setElementaryQuery"); final String simpleQuery2 = "SELECT * FROM \"customers\" WHERE \"Name\" = 'oranges'"; m_composer.setElementaryQuery(simpleQuery2); assertTrue("setElementaryQuery/getQuery inconsistent", m_composer.getQuery().equals(simpleQuery2)); System.out.println("check setQuery"); final String simpleQuery = "SELECT * FROM \"customers\""; m_composer.setQuery(simpleQuery); assertTrue("set/getQuery inconsistent", m_composer.getQuery().equals(simpleQuery)); checkAttributeAccess("Filter", "\"Name\" = 'oranges'"); checkAttributeAccess("Group", "\"City\""); checkAttributeAccess("Order", "\"Address\""); checkAttributeAccess("HavingClause", "\"ID\" <> 4"); final XIndexAccess orderColumns = m_composer.getOrderColumns(); assertTrue("Order columns doesn't exist: \"Address\"", orderColumns != null && orderColumns.getCount() == 1 && orderColumns.getByIndex(0) != null); final XIndexAccess groupColumns = m_composer.getGroupColumns(); assertTrue("Group columns doesn't exist: \"City\"", groupColumns != null && groupColumns.getCount() == 1 && groupColumns.getByIndex(0) != null); // XColumnsSupplier final XColumnsSupplier xSelectColumns = UnoRuntime.queryInterface(XColumnsSupplier.class, m_composer); assertTrue("no select columns, or wrong number of select columns", xSelectColumns != null && xSelectColumns.getColumns() != null && xSelectColumns.getColumns().getElementNames().length == 6); // structured filter m_composer.setQuery("SELECT \"ID\", \"Postal\", \"Address\" FROM \"customers\""); m_composer.setFilter(COMPLEXFILTER); final PropertyValue[][] aStructuredFilter = m_composer.getStructuredFilter(); m_composer.setFilter(""); m_composer.setStructuredFilter(aStructuredFilter); if (!m_composer.getFilter().equals(COMPLEXFILTER)) { System.out.println(COMPLEXFILTER); System.out.println(m_composer.getFilter()); } assertTrue("Structured Filter not identical", m_composer.getFilter().equals(COMPLEXFILTER)); // structured having clause m_composer.setHavingClause(COMPLEXFILTER); final PropertyValue[][] aStructuredHaving = m_composer.getStructuredHavingClause(); m_composer.setHavingClause(""); m_composer.setStructuredHavingClause(aStructuredHaving); assertTrue("Structured Having Clause not identical", m_composer.getHavingClause().equals(COMPLEXFILTER)); } catch (Exception e) { fail("Exception caught: " + e); } } /** test various sub query related features ("queries in queries") */ @Test public void testSubQueries() throws Exception { m_composer.setQuery("SELECT * from \"" + INNERPRODUCTSQUERY + "\""); final XTablesSupplier suppTables = UnoRuntime.queryInterface(XTablesSupplier.class, m_composer); final XNameAccess tables = suppTables.getTables(); assertTrue("a simple SELECT * FROM could not be parsed", tables != null && tables.hasByName(INNERPRODUCTSQUERY)); final String sInnerCommand = m_database.getDatabase().getDataSource().getQueryDefinition(INNERPRODUCTSQUERY).getCommand(); final String sExecutableQuery = m_composer.getQueryWithSubstitution(); assertTrue("simple query containing a sub query improperly parsed to SDBC level statement: \n1. " + sExecutableQuery + "\n2. " + "SELECT * FROM ( " + sInnerCommand + " ) AS \"" + INNERPRODUCTSQUERY + "\"", sExecutableQuery.equals("SELECT * FROM ( " + sInnerCommand + " ) AS \"" + INNERPRODUCTSQUERY + "\"")); } /** tests the XParametersSupplier functionality */ @Test public void testParameters() { try { // "orders for customers" is a query with a named parameter (based on another query) m_database.getDatabase().getDataSource().createQuery("orders for customer", "SELECT * FROM \"all orders\" WHERE \"Customer Name\" LIKE :cname"); // "orders for customer and product" is query based on "orders for customers", adding an additional, // anonymous parameter m_database.getDatabase().getDataSource().createQuery("orders for customer and product", "SELECT * FROM \"orders for customer\" WHERE \"Product Name\" LIKE ?"); m_composer.setQuery(m_database.getDatabase().getDataSource().getQueryDefinition("orders for customer and product").getCommand()); final XParametersSupplier suppParams = UnoRuntime.queryInterface(XParametersSupplier.class, m_composer); final XIndexAccess parameters = suppParams.getParameters(); final String expectedParamNames[] = { "cname", "Product Name" }; final int paramCount = parameters.getCount(); assertTrue("composer did find wrong number of parameters in the nested queries.", paramCount == expectedParamNames.length); for (int i = 0; i < paramCount; ++i) { final XPropertySet parameter = UnoRuntime.queryInterface(XPropertySet.class, parameters.getByIndex(i)); final String paramName = (String) parameter.getPropertyValue("Name"); assertTrue("wrong parameter name at position " + (i + 1) + " (expected: " + expectedParamNames[i] + ", found: " + paramName + ")", paramName.equals(expectedParamNames[i])); } } catch (Exception e) { fail("caught an exception: " + e); } } @Test public void testConditionByColumn() { try { m_composer.setQuery("SELECT * FROM \"customers\""); final Object initArgs[] = { new NamedValue("AutomaticAddition", Boolean.valueOf(true)) }; final String serviceName = "com.sun.star.beans.PropertyBag"; final XPropertyContainer filter = UnoRuntime.queryInterface(XPropertyContainer.class, getMSF().createInstanceWithArguments(serviceName, initArgs)); filter.addProperty("Name", PropertyAttribute.MAYBEVOID, "Comment"); filter.addProperty("RealName", PropertyAttribute.MAYBEVOID, "Comment"); filter.addProperty("TableName", PropertyAttribute.MAYBEVOID, "customers"); filter.addProperty("Value", PropertyAttribute.MAYBEVOID, "Good one."); filter.addProperty("Type", PropertyAttribute.MAYBEVOID, Integer.valueOf(DataType.LONGVARCHAR)); final XPropertySet column = UnoRuntime.queryInterface(XPropertySet.class, filter); m_composer.appendFilterByColumn(column, true, SQLFilterOperator.LIKE); assertTrue("At least one row should exist", m_database.getConnection().createStatement().executeQuery(m_composer.getQuery()).next()); } catch (Exception e) { // this is an error: the query is expected to be parseable fail("caught an exception: " + e); } } private void impl_testDisjunctiveNormalForm(String _query, PropertyValue[][] _expectedDNF) { try { m_composer.setQuery(_query); } catch (Exception e) { // this is an error: the query is expected to be parseable fail("caught an exception: " + e); } final PropertyValue[][] disjunctiveNormalForm = m_composer.getStructuredFilter(); assertEquals("DNF: wrong number of rows", _expectedDNF.length, disjunctiveNormalForm.length); for (int i = 0; i < _expectedDNF.length; ++i) { assertEquals("DNF: wrong number of columns in row " + i, _expectedDNF[i].length, disjunctiveNormalForm[i].length); for (int j = 0; j < _expectedDNF[i].length; ++j) { assertEquals("DNF: wrong content in column " + j + ", row " + i, _expectedDNF[i][j].Name, disjunctiveNormalForm[i][j].Name); } } } /** tests the disjunctive normal form functionality, aka the structured filter, * of the composer */ @Test public void testDisjunctiveNormalForm() { // a simple case: WHERE clause simply is a combination of predicates knitted with AND String query = "SELECT \"customers\".\"Name\", " + "\"customers\".\"Address\", " + "\"customers\".\"City\", " + "\"customers\".\"Postal\", " + "\"products\".\"Name\" " + "FROM \"orders\", \"customers\", \"orders_details\", \"products\" " + "WHERE ( \"orders\".\"CustomerID\" = \"customers\".\"ID\" " + "AND \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " + "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " + ") "; impl_testDisjunctiveNormalForm(query, new PropertyValue[][] { new PropertyValue[] { new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE) } }); // somewhat more challenging: One of the conjunction terms is a disjunction itself query = "SELECT \"customers\".\"Name\", " + "\"customers\".\"Address\", " + "\"customers\".\"City\", " + "\"customers\".\"Postal\", " + "\"products\".\"Name\" " + "FROM \"orders\", \"customers\", \"orders_details\", \"products\" " + "WHERE ( \"orders\".\"CustomerID\" = \"customers\".\"ID\" " + "AND \"orders_details\".\"OrderID\" = \"orders\".\"ID\" " + "AND \"orders_details\".\"ProductID\" = \"products\".\"ID\" " + ") " + "AND " + "( \"products\".\"Name\" = 'Apples' " + "OR \"products\".\"ID\" = 2 " + ")"; impl_testDisjunctiveNormalForm(query, new PropertyValue[][] { new PropertyValue[] { new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("Name", SQLFilterOperator.EQUAL, "Apples", PropertyState.DIRECT_VALUE) }, new PropertyValue[] { new PropertyValue("CustomerID", SQLFilterOperator.EQUAL, "\"customers\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("OrderID", SQLFilterOperator.EQUAL, "\"orders\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("ProductID", SQLFilterOperator.EQUAL, "\"products\".\"ID\"", PropertyState.DIRECT_VALUE), new PropertyValue("ID", SQLFilterOperator.EQUAL, Integer.valueOf(2), PropertyState.DIRECT_VALUE) } }); } }