dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk | 51 +
dbaccess/JunitTest_dbaccess_complex.mk | 1
dbaccess/Module_dbaccess.mk | 1
dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java | 351 --------
dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx | 436
++++++++++
5 files changed, 488 insertions(+), 352 deletions(-)
New commits:
commit d1ec7e90772421331bef31eb934180c7f992e42f
Author: Adam Seskunas <adamsesku...@gmail.com>
AuthorDate: Mon Mar 10 14:10:42 2025 -0700
Commit: Xisco Fauli <xiscofa...@libreoffice.org>
CommitDate: Thu Mar 13 21:21:59 2025 +0100
Move SingleSelectQueryComposer.java tests to C++
Move the tests to SingleSelectQueryComposer_test.cxx
Remove the old SingleSelectQueryComposer JUnit tests/file
Add makefile for SingleSelectQueryComposer_test
Remove the java test from JunitTest_dbaccess_complex.mk
Add the CppUnit test to the Module_dbaccess.mk file to be
run only when Java is enabled on the test machine
Change-Id: I832c80e294be6cd4dced4d358bcee383ea04b0fc
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/182759
Reviewed-by: Xisco Fauli <xiscofa...@libreoffice.org>
Tested-by: Jenkins
diff --git a/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
b/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
new file mode 100644
index 000000000000..7be6e2dbbd37
--- /dev/null
+++ b/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
@@ -0,0 +1,51 @@
+# -*- Mode: makefile-gmake; tab-width: 4; indent-tabs-mode: t -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+
+$(eval $(call
gb_CppunitTest_CppunitTest,dbaccess_SingleSelectQueryComposer_test))
+
+$(eval $(call
gb_CppunitTest_use_external,dbaccess_SingleSelectQueryComposer_test,boost_headers))
+
+$(eval $(call
gb_CppunitTest_add_exception_objects,dbaccess_SingleSelectQueryComposer_test, \
+ dbaccess/qa/unit/SingleSelectQueryComposer_test \
+))
+
+$(eval $(call
gb_CppunitTest_use_libraries,dbaccess_SingleSelectQueryComposer_test, \
+ comphelper \
+ cppu \
+ cppuhelper \
+ dbaxml \
+ dbtools \
+ sal \
+ subsequenttest \
+ utl \
+ test \
+ tk \
+ tl \
+ unotest \
+ xo \
+))
+
+$(eval $(call gb_CppunitTest_use_api,dbaccess_SingleSelectQueryComposer_test,\
+ offapi \
+ oovbaapi \
+ udkapi \
+))
+
+$(eval $(call gb_CppunitTest_use_ure,dbaccess_SingleSelectQueryComposer_test))
+$(eval $(call gb_CppunitTest_use_vcl,dbaccess_SingleSelectQueryComposer_test))
+
+$(eval $(call
gb_CppunitTest_use_rdb,dbaccess_SingleSelectQueryComposer_test,services))
+
+$(eval $(call
gb_CppunitTest_use_configuration,dbaccess_SingleSelectQueryComposer_test))
+
+$(eval $(call
gb_CppunitTest_use_uiconfigs,dbaccess_SingleSelectQueryComposer_test, \
+ dbaccess \
+))
+
+# vim: set noet sw=4 ts=4:
diff --git a/dbaccess/JunitTest_dbaccess_complex.mk
b/dbaccess/JunitTest_dbaccess_complex.mk
index 04609c4413f0..bed3b5231355 100644
--- a/dbaccess/JunitTest_dbaccess_complex.mk
+++ b/dbaccess/JunitTest_dbaccess_complex.mk
@@ -41,7 +41,6 @@ $(eval $(call gb_JunitTest_add_sourcefiles,dbaccess_complex,\
dbaccess/qa/complex/dbaccess/QueryInQuery \
dbaccess/qa/complex/dbaccess/RowSet \
dbaccess/qa/complex/dbaccess/RowSetEventListener \
- dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer \
dbaccess/qa/complex/dbaccess/TestCase \
dbaccess/qa/complex/dbaccess/UISettings \
))
diff --git a/dbaccess/Module_dbaccess.mk b/dbaccess/Module_dbaccess.mk
index f368fcc33e19..960989e5443c 100644
--- a/dbaccess/Module_dbaccess.mk
+++ b/dbaccess/Module_dbaccess.mk
@@ -59,6 +59,7 @@ $(eval $(call gb_Module_add_check_targets,dbaccess,\
CppunitTest_dbaccess_hsqldb_test \
CppunitTest_dbaccess_RowSetClones \
CppunitTest_dbaccess_CRMDatabase_test \
+ CppunitTest_dbaccess_SingleSelectQueryComposer_test \
))
endif
diff --git a/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
b/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
deleted file mode 100644
index 656b44d00c04..000000000000
--- a/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
+++ /dev/null
@@ -1,351 +0,0 @@
-/*
- * This file is part of the LibreOffice project.
- *
- * This Source Code Form is subject to the terms of the Mozilla Public
- * License, v. 2.0. If a copy of the MPL was not distributed with this
- * file, You can obtain one at http://mozilla.org/MPL/2.0/.
- *
- * This file incorporates work covered by the following license notice:
- *
- * 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 .
- */
-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 com.sun.star.sdbc.SQLException;
-
-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 static final 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 static final String INNERPRODUCTSQUERY = "products (inner)";
-
-
- private void createQueries() throws Exception
- {
-
m_database.getDatabase().getDataSource().createQuery(INNERPRODUCTSQUERY,
"SELECT * FROM \"products\"");
- }
-
-
- @Override
- protected void createTestCase() throws Exception
- {
- super.createTestCase();
-
- createQueries();
-
- m_composer = createQueryComposer();
- }
-
-
- 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() throws Exception
- {
- System.out.println("testing SingleSelectQueryComposer's setCommand");
-
- 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));
- }
-
- /** tests accessing attributes of the composer (order, filter, group by,
having)
- */
- @Test
- public void testAttributes() throws Exception
- {
- System.out.println("testing SingleSelectQueryComposer's attributes
(order, filter, group by, having)");
-
- 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));
- }
-
- /** 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:
1. " + sExecutableQuery + "
2. " + "SELECT * FROM ( " + sInnerCommand + " ) AS \"" + INNERPRODUCTSQUERY +
"\"",
- sExecutableQuery.equals("SELECT * FROM ( " + sInnerCommand + "
) AS \"" + INNERPRODUCTSQUERY + "\""));
- }
-
- /** tests the XParametersSupplier functionality
- */
- @Test
- public void testParameters() throws Exception
- {
- // "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]));
-
- }
- }
-
- @Test
- public void testConditionByColumn() throws Exception
- {
- m_composer.setQuery("SELECT * FROM \"customers\"");
-
- final Object initArgs[] =
-
- {
- new NamedValue("AutomaticAddition", Boolean.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());
- }
-
- private void impl_testDisjunctiveNormalForm(String _query,
PropertyValue[][] _expectedDNF) throws SQLException
- {
- m_composer.setQuery(_query);
-
- 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() throws Exception
- {
- // 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)
- }
- });
-
- }
-}
diff --git a/dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx
b/dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx
new file mode 100644
index 000000000000..ac9465f22a8f
--- /dev/null
+++ b/dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx
@@ -0,0 +1,436 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ */
+
+#include "dbtest_base.cxx"
+
+#include <com/sun/star/beans/NamedValue.hpp>
+#include <com/sun/star/beans/PropertyAttribute.hpp>
+#include <com/sun/star/beans/XPropertyContainer.hpp>
+#include <com/sun/star/container/XNameAccess.hpp>
+#include <com/sun/star/lang/XMultiServiceFactory.hpp>
+
+#include <com/sun/star/sdb/CommandType.hpp>
+#include <com/sun/star/sdb/SQLFilterOperator.hpp>
+#include <com/sun/star/sdb/XOfficeDatabaseDocument.hpp>
+#include <com/sun/star/sdb/XQueriesSupplier.hpp>
+#include <com/sun/star/sdb/XQueryDefinition.hpp>
+#include <com/sun/star/sdb/XQueryDefinitionsSupplier.hpp>
+#include <com/sun/star/sdb/XSingleSelectQueryComposer.hpp>
+#include <com/sun/star/sdb/XParametersSupplier.hpp>
+#include <com/sun/star/sdbc/DataType.hpp>
+#include <com/sun/star/sdbc/XDataSource.hpp>
+#include <com/sun/star/sdbc/XRow.hpp>
+#include <com/sun/star/sdbc/XResultSet.hpp>
+#include <com/sun/star/sdbcx/XColumnsSupplier.hpp>
+#include <com/sun/star/sdbcx/XTablesSupplier.hpp>
+
+using namespace ::com::sun::star;
+using namespace ::com::sun::star::uno;
+
+class SingleSelectQueryComposerTest : public DBTestBase
+{
+ Reference<XDataSource> m_xDataSource;
+ Reference<XConnection> m_xConnection;
+ Reference<XSingleSelectQueryComposer> m_xComposer;
+
+public:
+ virtual void setUp() override;
+ void testSetCommand();
+ void testAttributes();
+ void testSubQueries();
+ void testParameters();
+ void testConditionByColumn();
+ void testDisjunctiveNormalForm();
+
+ CPPUNIT_TEST_SUITE(SingleSelectQueryComposerTest);
+ CPPUNIT_TEST(testSetCommand);
+ CPPUNIT_TEST(testAttributes);
+ CPPUNIT_TEST(testSubQueries);
+ CPPUNIT_TEST(testParameters);
+ CPPUNIT_TEST(testConditionByColumn);
+ CPPUNIT_TEST(testDisjunctiveNormalForm);
+ CPPUNIT_TEST_SUITE_END();
+};
+
+void SingleSelectQueryComposerTest::setUp()
+{
+ DBTestBase::setUp();
+ createDBDocument(u"sdbc:embedded:hsqldb"_ustr);
+ Reference<sdb::XOfficeDatabaseDocument> xDocument(mxComponent,
UNO_QUERY_THROW);
+ m_xDataSource = xDocument->getDataSource();
+
+ // Create some common queries
+ DBTestBase::createQueries(m_xDataSource);
+
+ m_xConnection = m_xDataSource->getConnection(u""_ustr, u""_ustr);
+ DBTestBase::createTables(m_xConnection);
+
+ // For some reason we must close, then reopen the connection in
+ // order for the tables to be visible to the connection
+ m_xConnection->close();
+ m_xConnection = m_xDataSource->getConnection(u""_ustr, u""_ustr);
+
+ Reference<lang::XMultiServiceFactory> xFactory(m_xConnection, UNO_QUERY);
+ Reference<XSingleSelectQueryComposer> xComposer(
+
xFactory->createInstance(u"com.sun.star.sdb.SingleSelectQueryComposer"_ustr),
+ UNO_QUERY_THROW);
+ m_xComposer = xComposer;
+}
+
+// tests setCommand of the composer
+
+void SingleSelectQueryComposerTest::testSetCommand()
+{
+ // CommandType::TABLE
+ m_xComposer->setCommand(u"CUSTOMERS"_ustr, CommandType::TABLE);
+ CPPUNIT_ASSERT_EQUAL(u"SELECT * FROM \"CUSTOMERS\""_ustr,
m_xComposer->getQuery());
+
+ // CommandType::QUERY
+ DBTestBase::createQuery(u"SELECT * FROM \"orders for customer\" \"a\",
\"customers\" "
+ "\"b\" WHERE \"a\".\"Product Name\" =
\"b\".\"Name\""_ustr,
+ true, u"set command test"_ustr, m_xDataSource);
+
+ Reference<XQueriesSupplier> xQuerySupplier(m_xConnection, UNO_QUERY_THROW);
+ Reference<container::XNameAccess> xQueryAccess =
xQuerySupplier->getQueries();
+ CPPUNIT_ASSERT(xQueryAccess->hasElements());
+ Reference<XPropertySet> xQuery(xQueryAccess->getByName(u"set command
test"_ustr), UNO_QUERY);
+ CPPUNIT_ASSERT(xQuery.is());
+
+ m_xComposer->setCommand(u"set command test"_ustr, CommandType::QUERY);
+ OUString sQuery;
+ xQuery->getPropertyValue(u"Command"_ustr) >>= sQuery;
+
+ CPPUNIT_ASSERT_EQUAL(sQuery, m_xComposer->getQuery());
+
+ // CommandType::COMMMAND
+ const OUString sCommand
+ = u"SELECT * FROM \"orders for customer\" WHERE \"Product Name\" =
'test'"_ustr;
+ m_xComposer->setCommand(sCommand, CommandType::COMMAND);
+ CPPUNIT_ASSERT_EQUAL(sCommand, m_xComposer->getQuery());
+}
+
+// tests accessing attributes of the composer (order, filter, group by, having)
+
+void SingleSelectQueryComposerTest::testAttributes()
+{
+ const OUString sSimpleQuery2 = u"SELECT * FROM \"CUSTOMERS\" WHERE
\"Name\" = 'oranges'"_ustr;
+ m_xComposer->setElementaryQuery(sSimpleQuery2);
+ CPPUNIT_ASSERT_EQUAL(sSimpleQuery2, m_xComposer->getQuery());
+
+ const OUString sSimpleQuery = u"SELECT * FROM \"CUSTOMERS\""_ustr;
+ m_xComposer->setQuery(sSimpleQuery);
+ CPPUNIT_ASSERT_EQUAL(sSimpleQuery, m_xComposer->getQuery());
+
+ // checkAttributeAccess "Filter"
+ OUString sFilter = u"\"NAME\" = 'oranges'"_ustr;
+ m_xComposer->setFilter(sFilter);
+ CPPUNIT_ASSERT_EQUAL(sFilter, m_xComposer->getFilter());
+
+ // checkAttributeAccess "Group"
+ sFilter = u"\"CITY\""_ustr;
+ m_xComposer->setGroup(sFilter);
+ CPPUNIT_ASSERT_EQUAL(sFilter, m_xComposer->getGroup());
+
+ // checkAttributeAccess "Order"
+ sFilter = u"\"ADDRESS\""_ustr;
+ m_xComposer->setOrder(sFilter);
+ CPPUNIT_ASSERT_EQUAL(sFilter, m_xComposer->getOrder());
+
+ // checkAttributeAccess "HavingClause"
+ sFilter = u"\"ID\" <> 4"_ustr;
+ m_xComposer->setHavingClause(sFilter);
+ CPPUNIT_ASSERT_EQUAL(sFilter, m_xComposer->getHavingClause());
+
+ // check getOrderColumns
+ Reference<container::XIndexAccess>
orderColumns(m_xComposer->getOrderColumns(), UNO_SET_THROW);
+ CPPUNIT_ASSERT(orderColumns->hasElements());
+ CPPUNIT_ASSERT_EQUAL(sal_Int32(1), orderColumns->getCount());
+
+ const Reference<XPropertySet> xOrderColumns(orderColumns->getByIndex(0),
UNO_QUERY_THROW);
+ OUString sColumnName;
+ xOrderColumns->getPropertyValue(u"Name"_ustr) >>= sColumnName;
+ CPPUNIT_ASSERT_EQUAL(u"ADDRESS"_ustr, sColumnName);
+
+ // check getGroupColumns
+ Reference<container::XIndexAccess>
groupColumns(m_xComposer->getGroupColumns(), UNO_SET_THROW);
+ CPPUNIT_ASSERT(groupColumns->hasElements());
+ CPPUNIT_ASSERT_EQUAL(sal_Int32(1), groupColumns->getCount());
+
+ const Reference<XPropertySet> xGroupColumns(groupColumns->getByIndex(0),
UNO_QUERY_THROW);
+ OUString sGroupColumnName;
+ xGroupColumns->getPropertyValue(u"Name"_ustr) >>= sGroupColumnName;
+ CPPUNIT_ASSERT_EQUAL(u"CITY"_ustr, sGroupColumnName);
+
+ // XColumnSupplier
+ Reference<sdbcx::XColumnsSupplier> xSelectColumns(m_xComposer,
UNO_QUERY_THROW);
+ Reference<container::XNameAccess> xColumnAccess =
xSelectColumns->getColumns();
+ CPPUNIT_ASSERT(xColumnAccess->hasElements());
+ CPPUNIT_ASSERT_EQUAL(sal_Int32(6),
xColumnAccess->getElementNames().getLength());
+
+ const OUString COMPLEXFILTER = u"( \"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' } )"_ustr;
+
+ // structured filter
+ m_xComposer->setQuery(u"SELECT \"ID\", \"POSTAL\", \"ADDRESS\" FROM
\"CUSTOMERS\""_ustr);
+ m_xComposer->setFilter(COMPLEXFILTER);
+ Sequence<Sequence<PropertyValue>> aStructuredFilter =
m_xComposer->getStructuredFilter();
+ m_xComposer->setFilter(u""_ustr);
+ m_xComposer->setStructuredFilter(aStructuredFilter);
+ CPPUNIT_ASSERT_EQUAL(COMPLEXFILTER, m_xComposer->getFilter());
+
+ // structured having clause
+ m_xComposer->setHavingClause(COMPLEXFILTER);
+ Sequence<Sequence<PropertyValue>> aStructuredHaving =
m_xComposer->getStructuredHavingClause();
+ m_xComposer->setHavingClause("");
+ m_xComposer->setStructuredHavingClause(aStructuredHaving);
+ CPPUNIT_ASSERT_EQUAL(COMPLEXFILTER, m_xComposer->getHavingClause());
+}
+
+// test various sub query related features ("queries in queries")
+
+void SingleSelectQueryComposerTest::testSubQueries()
+{
+ const OUString INNERPRODUCTSQUERY = u"products (inner)"_ustr;
+ DBTestBase::createQuery(u"SELECT * FROM \"PRODUCTS\""_ustr, true,
INNERPRODUCTSQUERY,
+ m_xDataSource);
+
+ m_xComposer->setQuery(u"SELECT * FROM \""_ustr + INNERPRODUCTSQUERY +
u"\""_ustr);
+
+ Reference<sdbcx::XTablesSupplier> suppTables(m_xComposer, UNO_QUERY_THROW);
+ Reference<container::XNameAccess> tables = suppTables->getTables();
+ CPPUNIT_ASSERT(tables->hasElements());
+ CPPUNIT_ASSERT(tables->hasByName(INNERPRODUCTSQUERY));
+
+ Reference<XQueriesSupplier> xQuerySupplier(m_xConnection, UNO_QUERY_THROW);
+ Reference<container::XNameAccess> xQueryAccess =
xQuerySupplier->getQueries();
+ CPPUNIT_ASSERT(xQueryAccess->hasElements());
+
+ Reference<XPropertySet>
xQuery(xQueryAccess->getByName(INNERPRODUCTSQUERY), UNO_QUERY);
+ OUString sInnerProductsQuery;
+ xQuery->getPropertyValue(u"Command"_ustr) >>= sInnerProductsQuery;
+
+ const OUString sExpectedQuery = u"SELECT * FROM ( "_ustr +
sInnerProductsQuery
+ + u" )"
+ " AS \""_ustr
+ + INNERPRODUCTSQUERY + u"\""_ustr;
+ const OUString sExecutableQuery = m_xComposer->getQueryWithSubstitution();
+
+ CPPUNIT_ASSERT_EQUAL(sExpectedQuery, sExecutableQuery);
+}
+
+// test the XParametersSupplier functionality
+
+void SingleSelectQueryComposerTest::testParameters()
+{
+ // "orders for customers" is a query with a named parameter (based on
another query)
+ DBTestBase::createQuery(
+ u"SELECT * FROM \"orders for customer\" WHERE \"Product Name\" LIKE
?"_ustr, true,
+ u"orders for customer and product"_ustr, m_xDataSource);
+
+ // "orders for customer and product" is query based on "orders for
customers", adding an additional,
+ DBTestBase::createQuery(
+ u"SELECT * FROM \"all orders\" WHERE \"Customer Name\" LIKE
:cname"_ustr, true,
+ u"orders for customer"_ustr, m_xDataSource);
+
+ Reference<XQueryDefinitionsSupplier> xQuerySupplier(m_xDataSource,
UNO_QUERY);
+ Reference<container::XNameAccess> xQueryAccess =
xQuerySupplier->getQueryDefinitions();
+ Reference<sdb::XQueryDefinition> xQueryDefinition(
+ xQueryAccess->getByName(u"orders for customer and product"_ustr),
UNO_QUERY);
+
+ OUString sCustomersAndProduct;
+ xQueryDefinition->getPropertyValue(u"Command"_ustr) >>=
sCustomersAndProduct;
+
+ m_xComposer->setQuery(sCustomersAndProduct);
+
+ Reference<XParametersSupplier> xSuppParams(m_xComposer, UNO_QUERY_THROW);
+ Reference<container::XIndexAccess> xParameters =
xSuppParams->getParameters();
+
+ Sequence<OUString> const expectedParameters{ u"cname"_ustr, u"Product
Name"_ustr };
+ CPPUNIT_ASSERT_EQUAL(expectedParameters.getLength(),
xParameters->getCount());
+
+ for (auto i = 0; i < expectedParameters.getLength(); ++i)
+ {
+ Reference<XPropertySet> xParam(xParameters->getByIndex(i), UNO_QUERY);
+ OUString sParamName;
+ xParam->getPropertyValue(u"Name"_ustr) >>= sParamName;
+ CPPUNIT_ASSERT_EQUAL(expectedParameters[i], sParamName);
+ }
+}
+
+void SingleSelectQueryComposerTest::testConditionByColumn()
+{
+ m_xComposer->setQuery("SELECT * FROM \"CUSTOMERS\"");
+ Sequence<Any> aArgs{ Any(NamedValue(u"AutomaticAddition"_ustr, Any(true)))
};
+ Reference<beans::XPropertyContainer> filter(
+
m_xSFactory->createInstanceWithArguments(u"com.sun.star.beans.PropertyBag"_ustr,
aArgs),
+ UNO_QUERY);
+
+ filter->addProperty(u"Name"_ustr, PropertyAttribute::MAYBEVOID,
Any(u"COMMENT"_ustr));
+ filter->addProperty(u"RealName"_ustr, PropertyAttribute::MAYBEVOID,
Any(u"COMMENT"_ustr));
+ filter->addProperty(u"TableName"_ustr, PropertyAttribute::MAYBEVOID,
Any(u"CUSTOMERS"_ustr));
+ filter->addProperty(u"Value"_ustr, PropertyAttribute::MAYBEVOID,
Any(u"Good one."_ustr));
+ filter->addProperty(u"Type"_ustr, PropertyAttribute::MAYBEVOID,
+ Any(sal_Int32(sdbc::DataType::LONGVARCHAR)));
+
+ Reference<XPropertySet> xColumn(filter, UNO_QUERY_THROW);
+ m_xComposer->appendFilterByColumn(xColumn, true, SQLFilterOperator::LIKE);
+
+ Reference<XStatement> xStatement = m_xConnection->createStatement();
+ Reference<XResultSet> xResults =
xStatement->executeQuery(m_xComposer->getQuery());
+ CPPUNIT_ASSERT(xResults.is());
+
+ // At least one row should exist
+ Reference<XRow> xRow(xResults, UNO_QUERY_THROW);
+ CPPUNIT_ASSERT(xResults->next());
+ // The row returned should be the 4th, i.e. the one that
+ // contains the Value "Good One"
+ CPPUNIT_ASSERT_EQUAL(u"4"_ustr, xRow->getString(1));
+}
+
+// tests the disjunctive normal form functionality, aka the structured filter,
+// of the composer
+
+void SingleSelectQueryComposerTest::testDisjunctiveNormalForm()
+{
+ // a simple case: WHERE clause simply is a combination of predicates
knitted with AND
+ OUString sQuery = u"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\" "
+ ") "_ustr;
+
+ m_xComposer->setQuery(sQuery);
+
+ {
+ Sequence<Sequence<PropertyValue>> disjunctiveNormalForm
+ = m_xComposer->getStructuredFilter();
+
+ Sequence<PropertyValue> expectedDNF(3);
+ PropertyValue* pExpectedDNF = expectedDNF.getArray();
+
+ pExpectedDNF[0].Name = u"CUSTOMERID"_ustr;
+ pExpectedDNF[0].Handle = SQLFilterOperator::EQUAL;
+ pExpectedDNF[0].Value <<= u"\"CUSTOMERS\".\"ID\""_ustr;
+ pExpectedDNF[0].State = PropertyState_DIRECT_VALUE;
+
+ pExpectedDNF[1].Name = u"ORDERID"_ustr;
+ pExpectedDNF[1].Handle = SQLFilterOperator::EQUAL;
+ pExpectedDNF[1].Value <<= u"\"ORDER\".\"ID\""_ustr;
+ pExpectedDNF[1].State = PropertyState_DIRECT_VALUE;
+
+ pExpectedDNF[2].Name = u"PRODUCTID"_ustr;
+ pExpectedDNF[2].Handle = SQLFilterOperator::EQUAL;
+ pExpectedDNF[2].Value <<= u"\"PRODUCT\".\"ID\""_ustr;
+ pExpectedDNF[2].State = PropertyState_DIRECT_VALUE;
+
+ CPPUNIT_ASSERT_EQUAL(sal_Int32(1), disjunctiveNormalForm.getLength());
+
+ for (auto i = 0; i < expectedDNF.getLength(); ++i)
+ {
+ CPPUNIT_ASSERT_EQUAL(expectedDNF[i].Name,
disjunctiveNormalForm[0][i].Name);
+ }
+ }
+
+ // somewhat more challenging: One of the conjunction terms is a
disjunction itself
+ sQuery = u"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 "
+ ")"_ustr;
+
+ m_xComposer->setQuery(sQuery);
+ Sequence<Sequence<PropertyValue>> disjunctiveNormalForm =
m_xComposer->getStructuredFilter();
+
+ {
+ Sequence<PropertyValue> firstExpectedDNF(4);
+ PropertyValue* pFirstExpectedDNF = firstExpectedDNF.getArray();
+
+ pFirstExpectedDNF[0].Name = u"CUSTOMERID"_ustr;
+ pFirstExpectedDNF[0].Handle = SQLFilterOperator::EQUAL;
+ pFirstExpectedDNF[0].Value <<= u"\"CUSTOMERS\".\"ID\""_ustr;
+ pFirstExpectedDNF[0].State = PropertyState_DIRECT_VALUE;
+
+ pFirstExpectedDNF[1].Name = u"ORDERID"_ustr;
+ pFirstExpectedDNF[1].Handle = SQLFilterOperator::EQUAL;
+ pFirstExpectedDNF[1].Value <<= u"\"ORDER\".\"ID\""_ustr;
+ pFirstExpectedDNF[1].State = PropertyState_DIRECT_VALUE;
+
+ pFirstExpectedDNF[2].Name = u"PRODUCTID"_ustr;
+ pFirstExpectedDNF[2].Handle = SQLFilterOperator::EQUAL;
+ pFirstExpectedDNF[2].Value <<= u"\"PRODUCT\".\"ID\""_ustr;
+ pFirstExpectedDNF[2].State = PropertyState_DIRECT_VALUE;
+
+ pFirstExpectedDNF[3].Name = u"Name"_ustr;
+ pFirstExpectedDNF[3].Handle = SQLFilterOperator::EQUAL;
+ pFirstExpectedDNF[3].Value <<= u"\"Apples\""_ustr;
+ pFirstExpectedDNF[3].State = PropertyState_DIRECT_VALUE;
+
+ Sequence<PropertyValue> secondExpectedDNF(4);
+ PropertyValue* pSecondExpectedDNF = secondExpectedDNF.getArray();
+
+ pSecondExpectedDNF[0].Name = u"CUSTOMERID"_ustr;
+ pSecondExpectedDNF[0].Handle = SQLFilterOperator::EQUAL;
+ pSecondExpectedDNF[0].Value <<= u"\"CUSTOMERS\".\"ID\""_ustr;
+ pSecondExpectedDNF[0].State = PropertyState_DIRECT_VALUE;
+
+ pSecondExpectedDNF[1].Name = u"ORDERID"_ustr;
+ pSecondExpectedDNF[1].Handle = SQLFilterOperator::EQUAL;
+ pSecondExpectedDNF[1].Value <<= u"\"ORDER\".\"ID\""_ustr;
+ pSecondExpectedDNF[1].State = PropertyState_DIRECT_VALUE;
+
+ pSecondExpectedDNF[2].Name = u"PRODUCTID"_ustr;
+ pSecondExpectedDNF[2].Handle = SQLFilterOperator::EQUAL;
+ pSecondExpectedDNF[2].Value <<= u"\"PRODUCT\".\"ID\""_ustr;
+ pSecondExpectedDNF[2].State = PropertyState_DIRECT_VALUE;
+
+ pSecondExpectedDNF[3].Name = u"ID"_ustr;
+ pSecondExpectedDNF[3].Handle = SQLFilterOperator::EQUAL;
+ pSecondExpectedDNF[3].Value <<= sal_Int32(2);
+ pSecondExpectedDNF[3].State = PropertyState_DIRECT_VALUE;
+
+ Sequence<Sequence<PropertyValue>> expectedDNF(2);
+ Sequence<PropertyValue>* pExpectedDNF = expectedDNF.getArray();
+ pExpectedDNF[0] = firstExpectedDNF;
+ pExpectedDNF[1] = secondExpectedDNF;
+
+ CPPUNIT_ASSERT_EQUAL(expectedDNF.getLength(),
disjunctiveNormalForm.getLength());
+
+ for (auto i = 0; i < expectedDNF.getLength(); ++i)
+ {
+ for (auto j = 0; j < expectedDNF[0].getLength(); ++j)
+ {
+ CPPUNIT_ASSERT_EQUAL(expectedDNF[i][j].Name,
disjunctiveNormalForm[i][j].Name);
+ }
+ }
+ }
+}
+
+CPPUNIT_TEST_SUITE_REGISTRATION(SingleSelectQueryComposerTest);
+
+CPPUNIT_PLUGIN_IMPLEMENT();
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
