/**************************************************************
*
* 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 <query> 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)
}
});
}
}