dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk | 51 -
dbaccess/JunitTest_dbaccess_complex.mk | 1
dbaccess/Module_dbaccess.mk | 1
dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java | 355 +++++++
dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx | 448
----------
5 files changed, 356 insertions(+), 500 deletions(-)
New commits:
commit 42190cd153d4061a8401acdb2c2f8edf9154a978
Author: Noel Grandin <noel.gran...@collabora.co.uk>
AuthorDate: Mon Mar 31 12:38:35 2025 +0200
Commit: Noel Grandin <noel.gran...@collabora.co.uk>
CommitDate: Mon Mar 31 18:08:26 2025 +0200
Revert "Move SingleSelectQueryComposer.java tests to C++"
This reverts commit d1ec7e90772421331bef31eb934180c7f992e42f.
To avoid deadlocks on jenkins.
Change-Id: Icbe299ed29dfd02f61b14953344a902e2184c606
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/183535
Tested-by: Jenkins
Reviewed-by: Noel Grandin <noel.gran...@collabora.co.uk>
diff --git a/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
b/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
deleted file mode 100644
index 7be6e2dbbd37..000000000000
--- a/dbaccess/CppunitTest_dbaccess_SingleSelectQueryComposer_test.mk
+++ /dev/null
@@ -1,51 +0,0 @@
-# -*- 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 bed3b5231355..04609c4413f0 100644
--- a/dbaccess/JunitTest_dbaccess_complex.mk
+++ b/dbaccess/JunitTest_dbaccess_complex.mk
@@ -41,6 +41,7 @@ $(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 960989e5443c..f368fcc33e19 100644
--- a/dbaccess/Module_dbaccess.mk
+++ b/dbaccess/Module_dbaccess.mk
@@ -59,7 +59,6 @@ $(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
new file mode 100644
index 000000000000..978f318cafef
--- /dev/null
+++ b/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
@@ -0,0 +1,355 @@
+/*
+ * 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.*;
+
+/**
+ Note that this test CANNOT BE CONVERTED TO C++
+ because the JDBC driver uses a STA/AFFINE UNO compartment thing, which is
extremely deadlock prone
+ when called from inside the same process.
+*/
+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
deleted file mode 100644
index fab287ce7a62..000000000000
--- a/dbaccess/qa/unit/SingleSelectQueryComposer_test.cxx
+++ /dev/null
@@ -1,448 +0,0 @@
-/* -*- 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;
- virtual void tearDown() 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;
-}
-
-void SingleSelectQueryComposerTest::tearDown()
-{
- m_xComposer.clear();
- if (m_xConnection)
- {
- m_xConnection->close();
- m_xConnection.clear();
- }
- DBTestBase::tearDown();
-}
-
-// 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::COMMAND
- 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: */
