From 2b1d6f0d3b0b025148c81986ba7f109659d838af Mon Sep 17 00:00:00 2001 From: Lionel Elie Mamane Date: Sun, 30 Jul 2017 17:57:14 +0200 Subject: tdf#96370 rework filtering to be aware of WHERE vs HAVING clause Several bugs (AFAIK not filed into tdf bugzilla) fixed. Remaining problems: When some filter clauses go into WHERE and others in HAVING, they are always logically ANDed (it cannot be any other way), but that is not communicated to the user in the UI. Some things left undone: * DatabaseDataProvider (and its users?) needs to be updated to be HAVING-aware, too. * Form-based filter (.uno:FormFilter) not HAVING-aware it reads the current filter in function svxform::FormController::setFilter in svx/source/form/formcontrollers.cxx That's one place that needs to be updated. The other place is the one that applies the filter. Change-Id: I0e9d30a1927b6739a16ae7627e8d0dae8823b376 --- forms/source/component/DatabaseForm.cxx | 42 +++++++++++++++++++++++++-- forms/source/inc/frm_strings.hxx | 1 + forms/source/inc/property.hxx | 2 +- forms/source/runtime/formoperations.cxx | 51 +++++++++++++++++++++++++-------- forms/source/runtime/formoperations.hxx | 14 +++++++-- 5 files changed, 93 insertions(+), 17 deletions(-) (limited to 'forms') diff --git a/forms/source/component/DatabaseForm.cxx b/forms/source/component/DatabaseForm.cxx index 925d8a8fa3f2..1aae5d88aa39 100644 --- a/forms/source/component/DatabaseForm.cxx +++ b/forms/source/component/DatabaseForm.cxx @@ -1288,7 +1288,7 @@ void ODatabaseForm::describeFixedAndAggregateProperties( Sequence< Property >& _rProps, Sequence< Property >& _rAggregateProps ) const { - _rProps.realloc( 22 ); + _rProps.realloc( 23 ); css::beans::Property* pProperties = _rProps.getArray(); if (m_xAggregateSet.is()) @@ -1312,6 +1312,7 @@ void ODatabaseForm::describeFixedAndAggregateProperties( // (e.g. the ones which result from linking master fields to detail fields // via column names instead of parameters) RemoveProperty( _rAggregateProps, PROPERTY_FILTER ); + RemoveProperty( _rAggregateProps, PROPERTY_HAVINGCLAUSE ); RemoveProperty( _rAggregateProps, PROPERTY_APPLYFILTER ); DECL_IFACE_PROP4( ACTIVE_CONNECTION,XConnection, BOUND, TRANSIENT, MAYBEVOID, CONSTRAINED); @@ -1322,6 +1323,7 @@ void ODatabaseForm::describeFixedAndAggregateProperties( DECL_PROP2 ( DATASOURCE, OUString, BOUND, CONSTRAINED ); DECL_PROP3 ( CYCLE, TabulatorCycle, BOUND, MAYBEVOID, MAYBEDEFAULT ); DECL_PROP2 ( FILTER, OUString, BOUND, MAYBEDEFAULT ); + DECL_PROP2 ( HAVINGCLAUSE, OUString, BOUND, MAYBEDEFAULT ); DECL_BOOL_PROP2 ( INSERTONLY, BOUND, MAYBEDEFAULT ); DECL_PROP1 ( NAVIGATION, NavigationBarMode, BOUND ); DECL_BOOL_PROP1 ( ALLOWADDITIONS, BOUND ); @@ -1464,6 +1466,10 @@ void ODatabaseForm::getFastPropertyValue( Any& rValue, sal_Int32 nHandle ) const rValue <<= m_aFilterManager.getFilterComponent( FilterManager::FilterComponent::PublicFilter ); break; + case PROPERTY_ID_HAVINGCLAUSE: + rValue <<= m_aFilterManager.getFilterComponent( FilterManager::FilterComponent::PublicHaving ); + break; + case PROPERTY_ID_APPLYFILTER: rValue <<= m_aFilterManager.isApplyPublicFilter(); break; @@ -1546,6 +1552,10 @@ sal_Bool ODatabaseForm::convertFastPropertyValue( Any& rConvertedValue, Any& rOl bModified = tryPropertyValue( rConvertedValue, rOldValue, rValue, m_aFilterManager.getFilterComponent( FilterManager::FilterComponent::PublicFilter ) ); break; + case PROPERTY_ID_HAVINGCLAUSE: + bModified = tryPropertyValue( rConvertedValue, rOldValue, rValue, m_aFilterManager.getFilterComponent( FilterManager::FilterComponent::PublicHaving ) ); + break; + case PROPERTY_ID_APPLYFILTER: bModified = tryPropertyValue( rConvertedValue, rOldValue, rValue, m_aFilterManager.isApplyPublicFilter() ); break; @@ -1635,6 +1645,14 @@ void ODatabaseForm::setFastPropertyValue_NoBroadcast( sal_Int32 nHandle, const A } break; + case PROPERTY_ID_HAVINGCLAUSE: + { + OUString sNewFilter; + rValue >>= sNewFilter; + m_aFilterManager.setFilterComponent( FilterManager::FilterComponent::PublicHaving, sNewFilter ); + } + break; + case PROPERTY_ID_APPLYFILTER: { bool bApply = true; @@ -1780,6 +1798,13 @@ PropertyState ODatabaseForm::getPropertyStateByHandle(sal_Int32 nHandle) eState = PropertyState_DIRECT_VALUE; break; + case PROPERTY_ID_HAVINGCLAUSE: + if ( m_aFilterManager.getFilterComponent( FilterManager::FilterComponent::PublicHaving ).isEmpty() ) + eState = PropertyState_DEFAULT_VALUE; + else + eState = PropertyState_DIRECT_VALUE; + break; + case PROPERTY_ID_APPLYFILTER: eState = m_aFilterManager.isApplyPublicFilter() ? PropertyState_DEFAULT_VALUE : PropertyState_DIRECT_VALUE; break; @@ -1813,6 +1838,7 @@ void ODatabaseForm::setPropertyToDefaultByHandle(sal_Int32 nHandle) { case PROPERTY_ID_INSERTONLY: case PROPERTY_ID_FILTER: + case PROPERTY_ID_HAVINGCLAUSE: case PROPERTY_ID_APPLYFILTER: case PROPERTY_ID_NAVIGATION: case PROPERTY_ID_CYCLE: @@ -1843,6 +1869,10 @@ Any ODatabaseForm::getPropertyDefaultByHandle( sal_Int32 nHandle ) const aReturn <<= OUString(); break; + case PROPERTY_ID_HAVINGCLAUSE: + aReturn <<= OUString(); + break; + case PROPERTY_ID_APPLYFILTER: aReturn <<= true; break; @@ -3741,7 +3771,7 @@ void SAL_CALL ODatabaseForm::write(const Reference& _rxOutS OFormComponents::write(_rxOutStream); // version - _rxOutStream->writeShort(0x0003); + _rxOutStream->writeShort(0x0004); // Name _rxOutStream << m_sName; @@ -3819,10 +3849,13 @@ void SAL_CALL ODatabaseForm::write(const Reference& _rxOutS _rxOutStream->writeShort((sal_Int16)m_eNavigation); OUString sFilter; + OUString sHaving; OUString sOrder; if (m_xAggregateSet.is()) { m_xAggregateSet->getPropertyValue(PROPERTY_FILTER) >>= sFilter; + // version 4 + m_xAggregateSet->getPropertyValue(PROPERTY_HAVINGCLAUSE) >>= sHaving; m_xAggregateSet->getPropertyValue(PROPERTY_SORT) >>= sOrder; } _rxOutStream << sFilter; @@ -3922,6 +3955,11 @@ void SAL_CALL ODatabaseForm::read(const Reference& _rxInStre _rxInStream >> sAggregateProp; setPropertyValue(PROPERTY_FILTER, makeAny(sAggregateProp)); + if(nVersion > 3) + { + _rxInStream >> sAggregateProp; + setPropertyValue(PROPERTY_HAVINGCLAUSE, makeAny(sAggregateProp)); + } _rxInStream >> sAggregateProp; if (m_xAggregateSet.is()) diff --git a/forms/source/inc/frm_strings.hxx b/forms/source/inc/frm_strings.hxx index 868772abc744..790f279b1206 100644 --- a/forms/source/inc/frm_strings.hxx +++ b/forms/source/inc/frm_strings.hxx @@ -53,6 +53,7 @@ namespace frm #define PROPERTY_RELEVANT "Relevant" #define PROPERTY_ISREADONLY "IsReadOnly" #define PROPERTY_FILTER "Filter" + #define PROPERTY_HAVINGCLAUSE "HavingClause" #define PROPERTY_WIDTH "Width" #define PROPERTY_SEARCHABLE "IsSearchable" #define PROPERTY_MULTILINE "MultiLine" diff --git a/forms/source/inc/property.hxx b/forms/source/inc/property.hxx index b5d879a9e7a6..ea2ffca31e5d 100644 --- a/forms/source/inc/property.hxx +++ b/forms/source/inc/property.hxx @@ -173,7 +173,7 @@ namespace frm #define PROPERTY_ID_AUTOINCREMENT (PROPERTY_ID_START +133) // UINT16 // free #define PROPERTY_ID_FILTER (PROPERTY_ID_START +135) // ::rtl::OUString - // free +#define PROPERTY_ID_HAVINGCLAUSE (PROPERTY_ID_START +136) // ::rtl::OUString #define PROPERTY_ID_QUERY (PROPERTY_ID_START +137) // ::rtl::OUString #define PROPERTY_ID_DEFAULT_LONG_VALUE (PROPERTY_ID_START +138) // Double #define PROPERTY_ID_DEFAULT_DATE (PROPERTY_ID_START +139) // UINT32 diff --git a/forms/source/runtime/formoperations.cxx b/forms/source/runtime/formoperations.cxx index 340b4518f7d9..e5f9df54af20 100644 --- a/forms/source/runtime/formoperations.cxx +++ b/forms/source/runtime/formoperations.cxx @@ -307,8 +307,10 @@ namespace frm case FormFeature::ToggleApplyFilter: { OUString sFilter; - m_xCursorProperties->getPropertyValue( PROPERTY_FILTER ) >>= sFilter; - if ( !sFilter.isEmpty() ) + OUString sHaving; + m_xCursorProperties->getPropertyValue( PROPERTY_FILTER ) >>= sFilter; + m_xCursorProperties->getPropertyValue( PROPERTY_HAVINGCLAUSE ) >>= sHaving; + if ( ! (sFilter.isEmpty() && sHaving.isEmpty()) ) { aState.State = m_xCursorProperties->getPropertyValue( PROPERTY_APPLYFILTER ); aState.Enabled = !impl_isInsertOnlyForm_throw(); @@ -718,13 +720,15 @@ namespace frm OSL_ENSURE( xProperties.is(), "FormOperations::execute: no multi property access!" ); if ( xProperties.is() ) { - Sequence< OUString > aNames( 2 ); + Sequence< OUString > aNames( 3 ); aNames[0] = PROPERTY_FILTER; - aNames[1] = PROPERTY_SORT; + aNames[1] = PROPERTY_HAVINGCLAUSE; + aNames[2] = PROPERTY_SORT; - Sequence< Any> aValues( 2 ); + Sequence< Any> aValues( 3 ); aValues[0] <<= OUString(); aValues[1] <<= OUString(); + aValues[2] <<= OUString(); WaitObject aWO( nullptr ); xProperties->setPropertyValues( aNames, aValues ); @@ -1034,6 +1038,11 @@ namespace frm if ( m_xParser->getFilter() != sNewValue ) m_xParser->setFilter( sNewValue ); } + else if ( _rEvent.PropertyName == PROPERTY_HAVINGCLAUSE ) + { + if ( m_xParser->getHavingClause() != sNewValue ) + m_xParser->setHavingClause( sNewValue ); + } else if ( _rEvent.PropertyName == PROPERTY_SORT ) { _rEvent.NewValue >>= sNewValue; @@ -1221,14 +1230,17 @@ namespace frm { OUString sStatement; OUString sFilter; + OUString sHaving; OUString sSort; m_xCursorProperties->getPropertyValue( PROPERTY_ACTIVECOMMAND ) >>= sStatement; m_xCursorProperties->getPropertyValue( PROPERTY_FILTER ) >>= sFilter; + m_xCursorProperties->getPropertyValue( PROPERTY_HAVINGCLAUSE ) >>= sHaving; m_xCursorProperties->getPropertyValue( PROPERTY_SORT ) >>= sSort; m_xParser->setElementaryQuery( sStatement ); m_xParser->setFilter ( sFilter ); + m_xParser->setHavingClause ( sHaving ); m_xParser->setOrder ( sSort ); } @@ -1236,6 +1248,7 @@ namespace frm // we can keep our parser in sync m_xCursorProperties->addPropertyChangeListener( PROPERTY_ACTIVECOMMAND, this ); m_xCursorProperties->addPropertyChangeListener( PROPERTY_FILTER, this ); + m_xCursorProperties->addPropertyChangeListener( PROPERTY_HAVINGCLAUSE, this ); m_xCursorProperties->addPropertyChangeListener( PROPERTY_SORT, this ); } } @@ -1257,6 +1270,7 @@ namespace frm if ( m_xParser.is() && m_xCursorProperties.is() ) { m_xCursorProperties->removePropertyChangeListener( PROPERTY_FILTER, this ); + m_xCursorProperties->removePropertyChangeListener( PROPERTY_HAVINGCLAUSE, this ); m_xCursorProperties->removePropertyChangeListener( PROPERTY_ACTIVECOMMAND, this ); m_xCursorProperties->removePropertyChangeListener( PROPERTY_SORT, this ); } @@ -1337,7 +1351,9 @@ namespace frm bool FormOperations::impl_hasFilterOrOrder_throw() const { - return impl_isParseable_throw() && ( !m_xParser->getFilter().isEmpty() || !m_xParser->getOrder().isEmpty() ); + return impl_isParseable_throw() && ( !m_xParser->getFilter().isEmpty() || + !m_xParser->getHavingClause().isEmpty() || + !m_xParser->getOrder().isEmpty() ); } @@ -1600,21 +1616,27 @@ namespace frm return; OUString sOriginalFilter; - m_xCursorProperties->getPropertyValue( PROPERTY_FILTER ) >>= sOriginalFilter; + OUString sOriginalHaving; + m_xCursorProperties->getPropertyValue( PROPERTY_FILTER ) >>= sOriginalFilter; + m_xCursorProperties->getPropertyValue( PROPERTY_HAVINGCLAUSE ) >>= sOriginalHaving; bool bApplied = true; m_xCursorProperties->getPropertyValue( PROPERTY_APPLYFILTER ) >>= bApplied; // if we have a filter, but it's not applied, then we have to overwrite it, else append one if ( !bApplied ) + { m_xParser->setFilter( OUString() ); + m_xParser->setHavingClause( OUString() ); + } - impl_appendFilterByColumn_throw aAction(this, xBoundField); + impl_appendFilterByColumn_throw aAction(this, m_xParser, xBoundField); impl_doActionInSQLContext_throw( aAction, RID_STR_COULD_NOT_SET_FILTER ); WaitObject aWO( nullptr ); try { - m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + m_xCursorProperties->setPropertyValue( PROPERTY_HAVINGCLAUSE, makeAny( m_xParser->getHavingClause() ) ); m_xCursorProperties->setPropertyValue( PROPERTY_APPLYFILTER, makeAny( true ) ); m_xLoadableForm->reload(); @@ -1629,9 +1651,11 @@ namespace frm { // something went wrong -> restore the original state try { - m_xParser->setOrder( sOriginalFilter ); + m_xParser->setFilter ( sOriginalFilter ); + m_xParser->setHavingClause( sOriginalHaving ); m_xCursorProperties->setPropertyValue( PROPERTY_APPLYFILTER, makeAny( bApplied ) ); - m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + m_xCursorProperties->setPropertyValue( PROPERTY_HAVINGCLAUSE, makeAny( m_xParser->getHavingClause() ) ); m_xLoadableForm->reload(); } catch( const Exception& ) @@ -1678,7 +1702,10 @@ namespace frm { WaitObject aWO( nullptr ); if ( _bFilter ) - m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + { + m_xCursorProperties->setPropertyValue( PROPERTY_FILTER, makeAny( m_xParser->getFilter() ) ); + m_xCursorProperties->setPropertyValue( PROPERTY_HAVINGCLAUSE, makeAny( m_xParser->getHavingClause() ) ); + } else m_xCursorProperties->setPropertyValue( PROPERTY_SORT, makeAny( m_xParser->getOrder() ) ); m_xLoadableForm->reload(); diff --git a/forms/source/runtime/formoperations.hxx b/forms/source/runtime/formoperations.hxx index 425e789f8419..ad1494fecf64 100644 --- a/forms/source/runtime/formoperations.hxx +++ b/forms/source/runtime/formoperations.hxx @@ -25,8 +25,10 @@ #include #include #include +#include #include #include +#include #include #include #include @@ -35,7 +37,7 @@ #include #include - +#include namespace frm { @@ -320,14 +322,22 @@ namespace frm { public: impl_appendFilterByColumn_throw(const FormOperations *pFO, + css::uno::Reference< css::sdb::XSingleSelectQueryComposer > const & xParser, css::uno::Reference< css::beans::XPropertySet > const & xField) : m_pFO(pFO) + , m_xParser(xParser) , m_xField(xField) {}; - void operator()() { m_pFO->m_xParser->appendFilterByColumn( m_xField, true, css::sdb::SQLFilterOperator::EQUAL ); } + void operator()() { + if (dbtools::isAggregateColumn( m_xParser, m_xField )) + m_pFO->m_xParser->appendHavingClauseByColumn( m_xField, true, css::sdb::SQLFilterOperator::EQUAL ); + else + m_pFO->m_xParser->appendFilterByColumn( m_xField, true, css::sdb::SQLFilterOperator::EQUAL ); + } private: const FormOperations *m_pFO; + css::uno::Reference< css::sdb::XSingleSelectQueryComposer > m_xParser; css::uno::Reference< css::beans::XPropertySet > m_xField; }; -- cgit