summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorOcke Janssen [oj] <Ocke.Janssen@oracle.com>2011-01-10 13:41:20 +0100
committerOcke Janssen [oj] <Ocke.Janssen@oracle.com>2011-01-10 13:41:20 +0100
commit897ade45672a36ffd278b1cb9ba21694c0ee45c7 (patch)
treefeaf8ecce2966f6e82c92e72bf05b2f45471f300
parentb06a3287f38af15af2172e20f2ad29a0d12cd8ff (diff)
dba34c: #i108415# handle structed filter handling
-rwxr-xr-xdbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java84
-rw-r--r--dbaccess/source/core/api/SingleSelectQueryComposer.cxx28
-rw-r--r--dbaccess/source/core/inc/SingleSelectQueryComposer.hxx30
3 files changed, 65 insertions, 77 deletions
diff --git a/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java b/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
index 89d107ed5bb1..4823f5d50e4c 100755
--- a/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
+++ b/dbaccess/qa/complex/dbaccess/SingleSelectQueryComposer.java
@@ -55,11 +55,12 @@ 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' )";
+ 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)";
// --------------------------------------------------------------------------------------------------------
@@ -135,15 +136,15 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
try
{
final String table = "SELECT * FROM \"customers\"";
- m_composer.setCommand("customers",CommandType.TABLE);
+ 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);
+ 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);
+ m_composer.setCommand(sql, CommandType.COMMAND);
assertTrue("setCommand/getQuery COMMAND inconsistent", m_composer.getQuery().equals(sql));
}
catch (Exception e)
@@ -151,6 +152,7 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
fail("Exception caught: " + e);
}
}
+
/** tests accessing attributes of the composer (order, filter, group by, having)
*/
@Test
@@ -161,6 +163,7 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
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));
@@ -177,16 +180,16 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
final XIndexAccess orderColumns = m_composer.getOrderColumns();
assertTrue("Order columns doesn't exist: \"Address\"",
- orderColumns != null && orderColumns.getCount() == 1 && orderColumns.getByIndex(0) != null);
+ 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);
+ 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);
+ xSelectColumns != null && xSelectColumns.getColumns() != null && xSelectColumns.getColumns().getElementNames().length == 6);
// structured filter
m_composer.setQuery("SELECT \"ID\", \"Postal\", \"Address\" FROM \"customers\"");
@@ -194,6 +197,11 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
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
@@ -244,6 +252,7 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
final XIndexAccess parameters = suppParams.getParameters();
final String expectedParamNames[] =
+
{
"cname",
"Product Name"
@@ -276,6 +285,7 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
m_composer.setQuery("SELECT * FROM \"customers\"");
final Object initArgs[] =
+
{
new NamedValue("AutomaticAddition", Boolean.valueOf(true))
};
@@ -288,8 +298,8 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
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());
+ 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)
@@ -333,16 +343,16 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
{
// 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\" " +
- ") ";
+ "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[][]
{
@@ -356,20 +366,20 @@ public class SingleSelectQueryComposer extends CRMBasedTestCase
// 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 " +
- ")";
+ "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[][]
{
diff --git a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
index fa67a3d8cfdb..f3b40ffe968e 100644
--- a/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
+++ b/dbaccess/source/core/api/SingleSelectQueryComposer.cxx
@@ -58,6 +58,7 @@
#include <comphelper/sequence.hxx>
#include <comphelper/types.hxx>
#include <cppuhelper/typeprovider.hxx>
+#include <connectivity/predicateinput.hxx>
#include <rtl/logfile.hxx>
#include <unotools/syslocale.hxx>
#include <tools/debug.hxx>
@@ -1243,16 +1244,12 @@ sal_Bool OSingleSelectQueryComposer::setComparsionPredicate(OSQLParseNode * pCon
::rtl::OUString aValue;
::rtl::OUString aColumnName;
- pCondition->parseNodeToPredicateStr(aValue, m_xConnection, xFormatter, m_aLocale, static_cast<sal_Char>( m_sDecimalSep.toChar() ) );
+ pCondition->getChild(2)->parseNodeToPredicateStr(aValue, m_xConnection, xFormatter, m_aLocale, static_cast<sal_Char>( m_sDecimalSep.toChar() ) );
pCondition->getChild(0)->parseNodeToPredicateStr( aColumnName, m_xConnection, xFormatter, m_aLocale, static_cast<sal_Char>( m_sDecimalSep .toChar() ) );
- // don't display the column name
- aValue = aValue.copy(aColumnName.getLength());
- aValue.trim();
-
aItem.Name = getColumnName(pCondition->getChild(0),_rIterator);
aItem.Value <<= aValue;
- aItem.Handle = pCondition->getNodeType();
+ aItem.Handle = getPredicateType(pCondition->getChild(1));
rFilter.push_back(aItem);
}
else // kann sich nur um einen Expr. Ausdruck handeln
@@ -1269,7 +1266,7 @@ sal_Bool OSingleSelectQueryComposer::setComparsionPredicate(OSQLParseNode * pCon
pLhs->getChild(i)->parseNodeToPredicateStr( aName, m_xConnection, xFormatter, m_aLocale, static_cast<sal_Char>( m_sDecimalSep.toChar() ) );
// Kriterium
- aItem.Handle = pCondition->getChild(1)->getNodeType();
+ aItem.Handle = getPredicateType(pCondition->getChild(1));
aValue = pCondition->getChild(1)->getTokenValue();
for(i=0;i< pRhs->count();i++)
pRhs->getChild(i)->parseNodeToPredicateStr(aValue, m_xConnection, xFormatter, m_aLocale, static_cast<sal_Char>( m_sDecimalSep.toChar() ) );
@@ -1514,7 +1511,7 @@ Reference< XIndexAccess > SAL_CALL OSingleSelectQueryComposer::getOrderColumns(
// -----------------------------------------------------------------------------
namespace
{
- ::rtl::OUString lcl_getCondition(const Sequence< Sequence< PropertyValue > >& filter )
+ ::rtl::OUString lcl_getCondition(const Sequence< Sequence< PropertyValue > >& filter,const OPredicateInputController& i_aPredicateInputController,const Reference< XNameAccess >& i_xSelectColumns)
{
::rtl::OUStringBuffer sRet;
const Sequence< PropertyValue >* pOrIter = filter.getConstArray();
@@ -1531,6 +1528,15 @@ namespace
sRet.append(pAndIter->Name);
::rtl::OUString sValue;
pAndIter->Value >>= sValue;
+ if ( i_xSelectColumns.is() && i_xSelectColumns->hasByName(pAndIter->Name) )
+ {
+ Reference<XPropertySet> xColumn(i_xSelectColumns->getByName(pAndIter->Name),UNO_QUERY);
+ sValue = i_aPredicateInputController.getPredicateValue(sValue,xColumn,sal_True);
+ }
+ else
+ {
+ sValue = i_aPredicateInputController.getPredicateValue(pAndIter->Name,sValue,sal_True);
+ }
lcl_addFilterCriteria_throw(pAndIter->Handle,sValue,sRet);
++pAndIter;
if ( pAndIter != pAndEnd )
@@ -1549,13 +1555,15 @@ namespace
void SAL_CALL OSingleSelectQueryComposer::setStructuredFilter( const Sequence< Sequence< PropertyValue > >& filter ) throw (SQLException, ::com::sun::star::lang::IllegalArgumentException, RuntimeException)
{
RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "dbaccess", "Ocke.Janssen@sun.com", "OSingleSelectQueryComposer::setStructuredFilter" );
- setFilter(lcl_getCondition(filter));
+ OPredicateInputController aPredicateInput(m_aContext.getLegacyServiceFactory(),m_xConnection);
+ setFilter(lcl_getCondition(filter,aPredicateInput,getColumns()));
}
// -----------------------------------------------------------------------------
void SAL_CALL OSingleSelectQueryComposer::setStructuredHavingClause( const Sequence< Sequence< PropertyValue > >& filter ) throw (SQLException, RuntimeException)
{
RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "dbaccess", "Ocke.Janssen@sun.com", "OSingleSelectQueryComposer::setStructuredHavingClause" );
- setHavingClause(lcl_getCondition(filter));
+ OPredicateInputController aPredicateInput(m_aContext.getLegacyServiceFactory(),m_xConnection);
+ setHavingClause(lcl_getCondition(filter,aPredicateInput,getColumns()));
}
// -----------------------------------------------------------------------------
void OSingleSelectQueryComposer::setConditionByColumn( const Reference< XPropertySet >& column, sal_Bool andCriteria ,::std::mem_fun1_t<bool,OSingleSelectQueryComposer,::rtl::OUString>& _aSetFunctor,sal_Int32 filterOperator)
diff --git a/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx b/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
index 7c0d2ba4cea3..412fbb1c5231 100644
--- a/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
+++ b/dbaccess/source/core/inc/SingleSelectQueryComposer.hxx
@@ -27,51 +27,21 @@
#ifndef DBACCESS_CORE_API_SINGLESELECTQUERYCOMPOSER_HXX
#define DBACCESS_CORE_API_SINGLESELECTQUERYCOMPOSER_HXX
-#ifndef _COM_SUN_STAR_SDB_XPARAMETERSSUPPLIER_HPP_
#include <com/sun/star/sdb/XParametersSupplier.hpp>
-#endif
-#ifndef _COM_SUN_STAR_SDBCX_XTABLESSUPPLIER_HPP_
#include <com/sun/star/sdbcx/XTablesSupplier.hpp>
-#endif
-#ifndef _COM_SUN_STAR_SDBCX_XCOLUMNSSUPPLIER_HPP_
#include <com/sun/star/sdbcx/XColumnsSupplier.hpp>
-#endif
-#ifndef _COM_SUN_STAR_SDB_XSINGLESELECTQUERYCOMPOSER_HPP_
#include <com/sun/star/sdb/XSingleSelectQueryComposer.hpp>
-#endif
-#ifndef _COM_SUN_STAR_LANG_XSERVICEINFO_HPP_
#include <com/sun/star/lang/XServiceInfo.hpp>
-#endif
-#ifndef _COM_SUN_STAR_SCRIPT_XTYPECONVERTER_HPP_
#include <com/sun/star/script/XTypeConverter.hpp>
-#endif
-#ifndef _CPPUHELPER_IMPLBASE5_HXX_
#include <cppuhelper/implbase5.hxx>
-#endif
-#ifndef _CONNECTIVITY_PARSE_SQLITERATOR_HXX_
#include <connectivity/sqliterator.hxx>
-#endif
-#ifndef _CONNECTIVITY_SQLPARSE_HXX
#include <connectivity/sqlparse.hxx>
-#endif
-#ifndef _DBASHARED_APITOOLS_HXX_
#include "apitools.hxx"
-#endif
-#ifndef _COMPHELPER_BROADCASTHELPER_HXX_
#include <comphelper/broadcasthelper.hxx>
-#endif
-#ifndef _COMPHELPER_UNO3_HXX_
#include <comphelper/uno3.hxx>
-#endif
-#ifndef _COMPHELPER_PROPERTY_ARRAY_HELPER_HXX_
#include <comphelper/proparrhlp.hxx>
-#endif
-#ifndef _COMPHELPER_PROPERTYCONTAINER_HXX_
#include <comphelper/propertycontainer.hxx>
-#endif
-#ifndef COMPHELPER_COMPONENTCONTEXT_HXX
#include <comphelper/componentcontext.hxx>
-#endif
#include <memory>