fdo#51976 make "refetch one row" query easier to optimise

Instead of playing tricks with parameters that when filled in force a part of the WHERE clause to have not influence, actually use several different statements and hardcode in each the kind of test to be done

Change-Id: I93e1978f0420bc627a02291f209c788b9b4f2e96

4 files changed, 134 insertions, 62 deletions

diff --git a/dbaccess/source/core/api/KeySet.cxx b/dbaccess/source/core/api/KeySet.cxx
index 6970db27ca56..6252eec070cc 100644
--- a/dbaccess/source/core/api/KeySet.cxx
+++ b/dbaccess/source/core/api/KeySet.cxx
@@ -64,6 +64,7 @@ using namespace ::com::sun::star::io;
 using namespace ::com::sun::star;
 using namespace ::cppu;
 using namespace ::osl;
+using std::vector;
 
 namespace
 {
@@ -130,8 +131,15 @@ OKeySet::OKeySet(const connectivity::OSQLTable& _xTable,
 
 OKeySet::~OKeySet()
 {
-    tryDispose(m_xStatement);
     tryDispose(m_xSet);
+    // m_xStatement is necessarily one of those
+    const vStatements_t::const_iterator end(m_vStatements.end());
+    for(vStatements_t::iterator i(m_vStatements.begin());
+        i != end;
+        ++i)
+    {
+        tryDispose(i->second);
+    }
 
     m_xComposer = NULL;
 
@@ -221,13 +229,22 @@ SAL_WNODEPRECATED_DECLARATIONS_POP
 
 namespace
 {
-    void appendOneKeyColumnClause( const OUString &tblName, const OUString &colName, OUStringBuffer &o_buf )
+    void appendOneKeyColumnClause( const OUString &tblName, const OUString &colName, const connectivity::ORowSetValue &_rValue, OUStringBuffer &o_buf )
     {
-        static OUString s_sDot(".");
-        static OUString s_sParam0(" ( 1 = ? AND ");
-        static OUString s_sParam1(" = ? OR 1 = ? AND ");
-        static OUString s_sParam2(" IS NULL ) ");
-        o_buf.append(s_sParam0 + tblName + s_sDot + colName + s_sParam1 + tblName + s_sDot + colName + s_sParam2);
+        static const OUString s_sDot(".");
+        OUString fullName;
+        if (tblName.isEmpty())
+            fullName = colName;
+        else
+            fullName = tblName + s_sDot + colName;
+        if ( _rValue.isNull() )
+        {
+            o_buf.append(fullName + " IS NULL ");
+        }
+        else
+        {
+            o_buf.append(fullName + " = ? ");
+        }
     }
 }
 
@@ -235,58 +252,60 @@ void OKeySet::setOneKeyColumnParameter( sal_Int32 &nPos, const Reference< XParam
 {
     if ( _rValue.isNull() )
     {
-        _xParameter->setByte( nPos++, 0 );
-        // We do the full call so that the right sqlType is passed to setNull
-        setParameter( nPos++, _xParameter, _rValue, _nType, _nScale );
-        _xParameter->setByte( nPos++, 1 );
+        // Nothing to do, appendOneKeyColumnClause took care of it,
+        // the "IS NULL" is hardcoded in the query
     }
     else
     {
-        _xParameter->setByte( nPos++, 1 );
         setParameter( nPos++, _xParameter, _rValue, _nType, _nScale );
-        _xParameter->setByte( nPos++, 0 );
     }
 }
 
 OUStringBuffer OKeySet::createKeyFilter()
 {
-    static OUString aAnd(" AND ");
+    connectivity::ORowVector< ORowSetValue >::Vector::const_iterator aIter = m_aKeyIter->second.first->get().begin();
+
+    static const OUString aAnd(" AND ");
     const OUString aQuote    = getIdentifierQuoteString();
     OUStringBuffer aFilter;
     // create the where clause
     Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
-    SelectColumnsMetaData::iterator aPosEnd = m_pKeyColumnNames->end();
-    for(SelectColumnsMetaData::iterator aPosIter = m_pKeyColumnNames->begin();aPosIter != aPosEnd;)
+    SelectColumnsMetaData::const_iterator aPosEnd = m_pKeyColumnNames->end();
+    for(SelectColumnsMetaData::const_iterator aPosIter = m_pKeyColumnNames->begin();aPosIter != aPosEnd; ++aPosIter)
     {
-        appendOneKeyColumnClause(::dbtools::quoteTableName( xMeta,aPosIter->second.sTableName,::dbtools::eInDataManipulation),
-                                 ::dbtools::quoteName( aQuote,aPosIter->second.sRealName),
+        if ( ! aFilter.isEmpty() )
+            aFilter.append(aAnd);
+        appendOneKeyColumnClause(::dbtools::quoteTableName(xMeta, aPosIter->second.sTableName, ::dbtools::eInDataManipulation),
+                                 ::dbtools::quoteName(aQuote, aPosIter->second.sRealName),
+                                 *aIter++,
                                  aFilter);
-        ++aPosIter;
-        if(aPosIter != aPosEnd)
+    }
+    aPosEnd = m_pForeignColumnNames->end();
+    for(SelectColumnsMetaData::const_iterator aPosIter = m_pForeignColumnNames->begin(); aPosIter != aPosEnd; ++aPosIter)
+    {
+        if ( ! aFilter.isEmpty() )
             aFilter.append(aAnd);
+        appendOneKeyColumnClause(::dbtools::quoteTableName(xMeta, aPosIter->second.sTableName, ::dbtools::eInDataManipulation),
+                                 ::dbtools::quoteName(aQuote, aPosIter->second.sRealName),
+                                 *aIter++,
+                                 aFilter);
     }
     return aFilter;
 }
 
-void OKeySet::construct(const Reference< XResultSet>& _xDriverSet,const OUString& i_sRowSetFilter)
+void OKeySet::construct(const Reference< XResultSet>& _xDriverSet, const OUString& i_sRowSetFilter)
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "dbaccess", "Ocke.Janssen@sun.com", "OKeySet::construct" );
+
     OCacheSet::construct(_xDriverSet,i_sRowSetFilter);
+
     initColumns();
+    m_sRowSetFilter = i_sRowSetFilter;
 
-    Reference<XNameAccess> xKeyColumns  = getKeyColumns();
     Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
-    Reference<XColumnsSupplier> xQueryColSup(m_xComposer,UNO_QUERY);
+    Reference<XColumnsSupplier> xQueryColSup(m_xComposer, UNO_QUERY);
     const Reference<XNameAccess> xQueryColumns = xQueryColSup->getColumns();
-    findTableColumnsMatching_throw(makeAny(m_xTable),m_sUpdateTableName,xMeta,xQueryColumns,m_pKeyColumnNames);
-
-    // the first row is empty because it's now easier for us to distinguish when we are beforefirst or first
-    // without extra variable to be set
-    OKeySetValue keySetValue((ORowSetValueVector *)NULL,::std::pair<sal_Int32,Reference<XRow> >(0,(Reference<XRow>)NULL));
-    m_aKeyMap.insert(OKeySetMatrix::value_type(0, keySetValue));
-    m_aKeyIter = m_aKeyMap.begin();
-
-    OUStringBuffer aFilter = createKeyFilter();
+    findTableColumnsMatching_throw( makeAny(m_xTable), m_sUpdateTableName, xMeta, xQueryColumns, m_pKeyColumnNames );
 
     Reference< XSingleSelectQueryComposer> xSourceComposer(m_xComposer,UNO_QUERY);
     Reference< XMultiServiceFactory >  xFactory(m_xConnection, UNO_QUERY_THROW);
@@ -297,8 +316,6 @@ void OKeySet::construct(const Reference< XResultSet>& _xDriverSet,const OUString
     const Sequence< OUString> aSeq = xSelectTables->getElementNames();
     if ( aSeq.getLength() > 1 ) // special handling for join
     {
-        static OUString aAnd(" AND ");
-        const OUString aQuote    = getIdentifierQuoteString();
         const OUString* pIter = aSeq.getConstArray();
         const OUString* const pEnd  = pIter + aSeq.getLength();
         for(;pIter != pEnd;++pIter)
@@ -309,31 +326,63 @@ void OKeySet::construct(const Reference< XResultSet>& _xDriverSet,const OUString
                 Reference<XPropertySet> xProp(xSelColSup,uno::UNO_QUERY);
                 OUString sSelectTableName = ::dbtools::composeTableName( xMeta, xProp, ::dbtools::eInDataManipulation, false, false, false );
 
-                ::dbaccess::getColumnPositions(xQueryColumns,xSelColSup->getColumns()->getElementNames(),sSelectTableName,(*m_pForeignColumnNames));
+                ::dbaccess::getColumnPositions(xQueryColumns, xSelColSup->getColumns()->getElementNames(), sSelectTableName, (*m_pForeignColumnNames), true);
 
-                const SelectColumnsMetaData::iterator aPosEnd = (*m_pForeignColumnNames).end();
-                for(SelectColumnsMetaData::iterator aPosIter = (*m_pForeignColumnNames).begin();aPosIter != aPosEnd;++aPosIter)
-                {
-                    // look for columns not in the source columns to use them as filter as well
-                    if ( aFilter.getLength() )
-                        aFilter.append(aAnd);
-                    appendOneKeyColumnClause(::dbtools::quoteName( aQuote,sSelectTableName),
-                                             ::dbtools::quoteName( aQuote,aPosIter->second.sRealName),
-                                             aFilter);
-                }
-                break;
+                // LEM: there used to be a break here; however, I see no reason to stop
+                //      at first non-updateTable, so I removed it. (think of multiple joins...)
             }
         }
     }
-    executeStatement(aFilter,i_sRowSetFilter,xAnalyzer);
+
+    // the first row is empty because it's now easier for us to distinguish when we are beforefirst or first
+    // without extra variable to be set
+    OKeySetValue keySetValue((ORowSetValueVector *)NULL,::std::pair<sal_Int32,Reference<XRow> >(0,(Reference<XRow>)NULL));
+    m_aKeyMap.insert(OKeySetMatrix::value_type(0, keySetValue));
+    m_aKeyIter = m_aKeyMap.begin();
+}
+
+void OKeySet::ensureStatement( )
+{
+    // do we already have a statement for the current combination of NULLness
+    // of key & foreign columns?
+    FilterColumnsNULL_t FilterColumnsNULL;
+    FilterColumnsNULL.reserve(m_aKeyIter->second.first->get().size());
+    connectivity::ORowVector< ORowSetValue >::Vector::const_iterator aIter = m_aKeyIter->second.first->get().begin();
+    const connectivity::ORowVector< ORowSetValue >::Vector::const_iterator aEnd  = m_aKeyIter->second.first->get().end();
+    for( ; aIter != aEnd; ++aIter )
+        FilterColumnsNULL.push_back(aIter->isNull());
+    vStatements_t::iterator pNewStatement(m_vStatements.find(FilterColumnsNULL));
+    if(pNewStatement == m_vStatements.end())
+    {
+        // no: make a new one
+        makeNewStatement();
+        std::pair< vStatements_t::iterator, bool > insert_result
+            (m_vStatements.insert(vStatements_t::value_type(FilterColumnsNULL, m_xStatement)));
+        assert(insert_result.second);
+    }
+    else
+        // yes: use it
+        m_xStatement = pNewStatement->second;
 }
-void OKeySet::executeStatement(OUStringBuffer& io_aFilter,const OUString& i_sRowSetFilter,Reference<XSingleSelectQueryComposer>& io_xAnalyzer)
+
+void OKeySet::makeNewStatement()
 {
-    bool bFilterSet = !i_sRowSetFilter.isEmpty();
+    Reference< XSingleSelectQueryComposer> xSourceComposer(m_xComposer,UNO_QUERY);
+    Reference< XMultiServiceFactory >  xFactory(m_xConnection, UNO_QUERY_THROW);
+    Reference<XSingleSelectQueryComposer> xAnalyzer(xFactory->createInstance(SERVICE_NAME_SINGLESELECTQUERYCOMPOSER),UNO_QUERY);
+    xAnalyzer->setElementaryQuery(xSourceComposer->getElementaryQuery());
+
+    OUStringBuffer aFilter(createKeyFilter());
+    executeStatement(aFilter, xAnalyzer);
+}
+
+void OKeySet::executeStatement(OUStringBuffer& io_aFilter, Reference<XSingleSelectQueryComposer>& io_xAnalyzer)
+{
+    bool bFilterSet = !m_sRowSetFilter.isEmpty();
     if ( bFilterSet )
     {
         FilterCreator aFilterCreator;
-        aFilterCreator.append( i_sRowSetFilter );
+        aFilterCreator.append( m_sRowSetFilter );
         aFilterCreator.append( io_aFilter.makeStringAndClear() );
         io_aFilter = aFilterCreator.getComposedAndClear();
     }
@@ -1298,6 +1347,7 @@ sal_Bool SAL_CALL OKeySet::previous(  ) throw(SQLException, RuntimeException)
 
 bool OKeySet::doTryRefetch_throw()  throw(SQLException, RuntimeException)
 {
+    ensureStatement( );
     // we just reassign the base members
     Reference< XParameters > xParameter(m_xStatement,UNO_QUERY);
     OSL_ENSURE(xParameter.is(),"No Parameter interface!");
@@ -1346,7 +1396,7 @@ void SAL_CALL OKeySet::refreshRow() throw(SQLException, RuntimeException)
 
     invalidateRow();
 
-    if(isBeforeFirst() || isAfterLast() || !m_xStatement.is())
+    if(isBeforeFirst() || isAfterLast())
         return;
 
     if ( m_aKeyIter->second.second.second.is() )
@@ -1667,16 +1717,19 @@ void getColumnPositions(const Reference<XNameAccess>& _rxQueryColumns,
                     sal_Int32 nNullable = ColumnValue::NULLABLE_UNKNOWN;
                     OSL_VERIFY( xQueryColumnProp->getPropertyValue( PROPERTY_ISNULLABLE ) >>= nNullable );
 
+                    SelectColumnDescription aColDesc( nPos, nType, nScale, nNullable != sdbc::ColumnValue::NO_NULLS, sColumnDefault );
+                    OUString sName;
                     if ( i_bAppendTableName )
                     {
-                        OUString sName = sTableName + "." + sRealName;
-                        SelectColumnDescription aColDesc( nPos, nType,nScale,nNullable != sdbc::ColumnValue::NO_NULLS, sColumnDefault );
+                        sName = sTableName + "." + sRealName;
                         aColDesc.sRealName = sRealName;
                         aColDesc.sTableName = sTableName;
-                        o_rColumnNames[sName] = aColDesc;
                     }
                     else
-                        o_rColumnNames[sRealName] = SelectColumnDescription( nPos, nType,nScale,nNullable != sdbc::ColumnValue::NO_NULLS, sColumnDefault );
+                    {
+                        sName = sRealName;
+                    }
+                    o_rColumnNames[sName] = aColDesc;
 
                     break;
                 }
diff --git a/dbaccess/source/core/api/KeySet.hxx b/dbaccess/source/core/api/KeySet.hxx
index 44edc44fe1ec..118579014c67 100644
--- a/dbaccess/source/core/api/KeySet.hxx
+++ b/dbaccess/source/core/api/KeySet.hxx
@@ -25,6 +25,7 @@
 #include <cppuhelper/implbase1.hxx>
 #include <memory>
 #include <map>
+#include <vector>
 
 #include <com/sun/star/lang/XUnoTunnel.hpp>
 #include <com/sun/star/sdb/XSingleSelectQueryAnalyzer.hpp>
@@ -91,11 +92,21 @@ namespace dbaccess
         SAL_WNODEPRECATED_DECLARATIONS_POP
         connectivity::OSQLTable                                 m_xTable; // reference to our table
         ::com::sun::star::uno::Reference< ::com::sun::star::container::XIndexAccess>    m_xTableKeys;
+        // we need a different SQL (statement) for each different combination
+        // of NULLness of key & foreign columns;
+        // each subclause is either "colName = ?" or "colName IS NULL"
+        // (we avoid the standard "colName IS NOT DISTINCT FROM ?" because it is not widely supported)
+        typedef ::std::vector< bool > FilterColumnsNULL_t;
+        typedef ::std::map< FilterColumnsNULL_t,
+                            ::com::sun::star::uno::Reference< ::com::sun::star::sdbc::XPreparedStatement > >
+                vStatements_t;
+        vStatements_t                                           m_vStatements;
         ::com::sun::star::uno::Reference< ::com::sun::star::sdbc::XPreparedStatement>   m_xStatement;
         ::com::sun::star::uno::Reference< ::com::sun::star::sdbc::XResultSet>           m_xSet;
         ::com::sun::star::uno::Reference< ::com::sun::star::sdbc::XRow>                 m_xRow;
         ::com::sun::star::uno::Reference< ::com::sun::star::sdb::XSingleSelectQueryAnalyzer >   m_xComposer;
-        ::rtl::OUString                                                                 m_sUpdateTableName;
+        const ::rtl::OUString                                   m_sUpdateTableName;
+        ::rtl::OUString                                         m_sRowSetFilter;
         ::std::vector< ::rtl::OUString >                        m_aFilterColumns;
         sal_Int32&                                              m_rRowCount;
 
@@ -124,17 +135,19 @@ namespace dbaccess
                                              const ::com::sun::star::uno::Reference< ::com::sun::star::container::XNameAccess>& i_xQueryColumns,
                                              ::std::auto_ptr<SelectColumnsMetaData>& o_pKeyColumnNames);
         SAL_WNODEPRECATED_DECLARATIONS_POP
+        void ensureStatement( );
+        virtual void makeNewStatement( );
         void setOneKeyColumnParameter( sal_Int32 &nPos,
                                        const ::com::sun::star::uno::Reference<  ::com::sun::star::sdbc::XParameters > &_xParameter,
                                        const connectivity::ORowSetValue &_rValue,
                                        sal_Int32 _nType,
                                        sal_Int32 _nScale ) const;
-        ::rtl::OUStringBuffer createKeyFilter();
+        ::rtl::OUStringBuffer createKeyFilter( );
         bool doTryRefetch_throw() throw(::com::sun::star::sdbc::SQLException, ::com::sun::star::uno::RuntimeException);;
         void tryRefetch(const ORowSetRow& _rInsertRow,bool bRefetch);
         void executeUpdate(const ORowSetRow& _rInsertRow ,const ORowSetRow& _rOrginalRow,const ::rtl::OUString& i_sSQL,const ::rtl::OUString& i_sTableName,const ::std::vector<sal_Int32>& _aIndexColumnPositions = ::std::vector<sal_Int32>());
         void executeInsert( const ORowSetRow& _rInsertRow,const ::rtl::OUString& i_sSQL,const ::rtl::OUString& i_sTableName = ::rtl::OUString(),bool bRefetch = false);
-        void executeStatement(::rtl::OUStringBuffer& io_aFilter,const ::rtl::OUString& i_sRowSetFilter,::com::sun::star::uno::Reference< ::com::sun::star::sdb::XSingleSelectQueryComposer>& io_xAnalyzer);
+        void executeStatement(::rtl::OUStringBuffer& io_aFilter, ::com::sun::star::uno::Reference< ::com::sun::star::sdb::XSingleSelectQueryComposer>& io_xAnalyzer);
 
         virtual ~OKeySet();
     public:
diff --git a/dbaccess/source/core/api/OptimisticSet.cxx b/dbaccess/source/core/api/OptimisticSet.cxx
index 34f545be1057..546a984f56dd 100644
--- a/dbaccess/source/core/api/OptimisticSet.cxx
+++ b/dbaccess/source/core/api/OptimisticSet.cxx
@@ -1,4 +1,3 @@
-
 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
 /*
  * This file is part of the LibreOffice project.
@@ -108,8 +107,11 @@ OptimisticSet::~OptimisticSet()
 void OptimisticSet::construct(const Reference< XResultSet>& _xDriverSet,const ::rtl::OUString& i_sRowSetFilter)
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "dbaccess", "Ocke.Janssen@sun.com", "OptimisticSet::construct" );
+
     OCacheSet::construct(_xDriverSet,i_sRowSetFilter);
+
     initColumns();
+    m_sRowSetFilter = i_sRowSetFilter;
 
     Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
     bool bCase = (xMeta.is() && xMeta->supportsMixedCaseQuotedIdentifiers()) ? true : false;
@@ -134,7 +136,10 @@ void OptimisticSet::construct(const Reference< XResultSet>& _xDriverSet,const ::
     OKeySetValue keySetValue((ORowSetValueVector *)NULL,::std::pair<sal_Int32,Reference<XRow> >(0,(Reference<XRow>)NULL));
     m_aKeyMap.insert(OKeySetMatrix::value_type(0,keySetValue));
     m_aKeyIter = m_aKeyMap.begin();
+}
 
+void OptimisticSet::makeNewStatement( )
+{
     ::rtl::OUStringBuffer aFilter = createKeyFilter();
 
     Reference< XSingleSelectQueryComposer> xSourceComposer(m_xComposer,UNO_QUERY);
@@ -152,12 +157,12 @@ void OptimisticSet::construct(const Reference< XResultSet>& _xDriverSet,const ::
     fillJoinedColumns_throw(m_aSqlIterator.getJoinConditions());
 
     const ::rtl::OUString sComposerFilter = m_xComposer->getFilter();
-    if ( !i_sRowSetFilter.isEmpty() || (!sComposerFilter.isEmpty() && sComposerFilter != i_sRowSetFilter) )
+    if ( !m_sRowSetFilter.isEmpty() || (!sComposerFilter.isEmpty() && sComposerFilter != m_sRowSetFilter) )
     {
         FilterCreator aFilterCreator;
-        if ( !sComposerFilter.isEmpty() && sComposerFilter != i_sRowSetFilter )
+        if ( !sComposerFilter.isEmpty() && sComposerFilter != m_sRowSetFilter )
             aFilterCreator.append( sComposerFilter );
-        aFilterCreator.append( i_sRowSetFilter );
+        aFilterCreator.append( m_sRowSetFilter );
         aFilterCreator.append( aFilter.makeStringAndClear() );
         aFilter = aFilterCreator.getComposedAndClear();
     }
diff --git a/dbaccess/source/core/api/OptimisticSet.hxx b/dbaccess/source/core/api/OptimisticSet.hxx
index a35f18ee41f9..0e9723381d0d 100644
--- a/dbaccess/source/core/api/OptimisticSet.hxx
+++ b/dbaccess/source/core/api/OptimisticSet.hxx
@@ -50,6 +50,7 @@ namespace dbaccess
         void fillJoinedColumns_throw(const ::std::vector< ::connectivity::TNodePair>& i_aJoinColumns);
         void fillJoinedColumns_throw(const ::rtl::OUString& i_sLeftColumn,const ::rtl::OUString& i_sRightColumn);
     protected:
+        virtual void makeNewStatement( );
         virtual ~OptimisticSet();
     public:
         OptimisticSet(const ::comphelper::ComponentContext& _rContext,