Centrally manage cached grid data for data pilot tables.

This prevents cached data from getting re-generated everytime ScDPObject
gets copied, which happens quite often.  Without this, the performance
of datapilot would really really suffer.

16 files changed, 272 insertions, 150 deletions

diff --git a/sc/inc/dpcachetable.hxx b/sc/inc/dpcachetable.hxx
index d9f607f18..4c83ec991 100644
--- a/sc/inc/dpcachetable.hxx
+++ b/sc/inc/dpcachetable.hxx
@@ -121,7 +121,7 @@ public:
         Criterion();
     };
 
-    ScDPCacheTable(ScDPCache* pCache);
+    ScDPCacheTable(const ScDPCache* pCache);
     ~ScDPCacheTable();
 
     sal_Int32 getRowSize() const;
@@ -169,15 +169,13 @@ public:
     SCROW getOrder(long nDim, SCROW nIndex) const;
     void clear();
     bool empty() const;
-    void setCache(ScDPCache* p);
+    void setCache(const ScDPCache* p);
     bool hasCache() const;
 
 private:
     ScDPCacheTable();
     ScDPCacheTable(const ScDPCacheTable&);
 
-    ScDPCache* getCache();
-
     /** 
      * Check if a given row meets all specified criteria.
      *
@@ -194,7 +192,7 @@ private:
         has the index of 0. */
     ::std::vector<bool> maRowsVisible;
 
-    ScDPCache* mpCache;
+    const ScDPCache* mpCache;
 };
 #endif
 
diff --git a/sc/inc/dpobject.hxx b/sc/inc/dpobject.hxx
index 7b57348b8..b796221cd 100644
--- a/sc/inc/dpobject.hxx
+++ b/sc/inc/dpobject.hxx
@@ -39,6 +39,7 @@
 
 #include <boost/ptr_container/ptr_list.hpp>
 #include <boost/ptr_container/ptr_vector.hpp>
+#include <boost/ptr_container/ptr_map.hpp>
 #include <boost/shared_ptr.hpp>
 
 namespace com { namespace sun { namespace star { namespace sheet {
@@ -117,7 +118,6 @@ private:
     SC_DLLPRIVATE void				CreateObjects();
     SC_DLLPRIVATE void				CreateOutput();
     sal_Bool					bRefresh;
-    long						mnCacheId;
 
 public:
     ScDPObject(ScDocument* pD);
@@ -257,13 +257,48 @@ public:
 
 class ScDPCollection
 {
-private:
-    typedef ::boost::ptr_vector<ScDPObject> TablesType;
+public:
 
-    ScDocument*	pDoc;
-    TablesType maTables;
+    /**
+     * Stores and manages all caches from internal sheets.
+     */
+    class SheetCaches
+    {
+        typedef ::boost::ptr_map<ScRange, ScDPCache> CachesType;
+        CachesType maCaches;
+        ScDocument* mpDoc;
+    public:
+        SheetCaches(ScDocument* pDoc);
+        const ScDPCache* getCache(const ScRange& rRange);
+        void removeCache(const ScRange& rRange);
+    };
+
+    /**
+     * Defines connection type to external data source.  Used as a key to look
+     * up database cache.
+     */
+    struct DBType
+    {
+        sal_Int32 mnSdbType;
+        ::rtl::OUString maDBName;
+        ::rtl::OUString maCommand;
+        DBType(sal_Int32 nSdbType, const ::rtl::OUString& rDBName, const ::rtl::OUString& rCommand);
+    };
+
+    /**
+     * Data caches for external database sources.
+     */
+    class DBCaches
+    {
+        typedef ::boost::ptr_map<DBType, ScDPCache> CachesType;
+        CachesType maCaches;
+        ScDocument* mpDoc;
+    public:
+        DBCaches(ScDocument* pDoc);
+        const ScDPCache* getCache(sal_Int32 nSdbType, const ::rtl::OUString& rDBName, const ::rtl::OUString& rCommand);
+        void removeCache(sal_Int32 nSdbType, const ::rtl::OUString& rDBName, const ::rtl::OUString& rCommand);
+    };
 
-public:
     ScDPCollection(ScDocument* pDocument);
     ScDPCollection(const ScDPCollection& r);
     ~ScDPCollection();
@@ -296,8 +331,20 @@ public:
     SC_DLLPUBLIC bool InsertNewTable(ScDPObject* pDPObj);
 
     bool HasDPTable(SCCOL nCol, SCROW nRow, SCTAB nTab) const;
+
+    SheetCaches& GetSheetCaches();
+    DBCaches& GetDBCaches();
+
+private:
+    typedef ::boost::ptr_vector<ScDPObject> TablesType;
+
+    ScDocument*	pDoc;
+    TablesType maTables;
+    SheetCaches maSheetCaches;
+    DBCaches maDBCaches;
 };
 
+bool operator<(const ScDPCollection::DBType& left, const ScDPCollection::DBType& right);
 
 #endif
 
diff --git a/sc/inc/dpsdbtab.hxx b/sc/inc/dpsdbtab.hxx
index 10e3d7e20..3e24e1e02 100644
--- a/sc/inc/dpsdbtab.hxx
+++ b/sc/inc/dpsdbtab.hxx
@@ -41,8 +41,8 @@ class ScDocument;
 
 struct ScImportSourceDesc
 {
-    String	aDBName;
-    String	aObject;
+    ::rtl::OUString aDBName;
+    ::rtl::OUString aObject;
     sal_uInt16	nType;			// enum DataImportMode
     bool    bNative;
     ScDocument* mpDoc;
@@ -56,7 +56,8 @@ struct ScImportSourceDesc
                  bNative == rOther.bNative &&
                 mpDoc == rOther.mpDoc; }
 
-    ScDPCache* CreateCache() const;
+    sal_Int32 GetCommandType() const;
+    const ScDPCache* CreateCache() const;
 };
 
 /**
diff --git a/sc/inc/dpshttab.hxx b/sc/inc/dpshttab.hxx
index 24d1d036e..f165981d2 100644
--- a/sc/inc/dpshttab.hxx
+++ b/sc/inc/dpshttab.hxx
@@ -78,7 +78,7 @@ public:
     const ScQueryParam& GetQueryParam() const;
 
     bool operator== ( const ScSheetSourceDesc& rOther ) const;
-    SC_DLLPUBLIC ScDPCache* CreateCache() const;
+    SC_DLLPUBLIC const ScDPCache* CreateCache() const;
 
     /**
      * Check the sanity of the data source range.
@@ -111,7 +111,7 @@ private:
     ScDPCacheTable  aCacheTable;
 
 public:
-    ScSheetDPData(ScDocument* pD, const ScSheetSourceDesc& rDesc, ScDPCache* pCache);
+    ScSheetDPData(ScDocument* pD, const ScSheetSourceDesc& rDesc, const ScDPCache* pCache);
     virtual	~ScSheetDPData();
 
     virtual long					GetColumnCount();
diff --git a/sc/inc/dptabdat.hxx b/sc/inc/dptabdat.hxx
index cc68fe73b..deeb034fc 100644
--- a/sc/inc/dptabdat.hxx
+++ b/sc/inc/dptabdat.hxx
@@ -39,6 +39,7 @@
 #include <set>
 #include <boost/unordered_set.hpp>
 #include <boost/unordered_map.hpp>
+#include <boost/noncopyable.hpp>
 
 namespace com { namespace sun { namespace star { namespace sheet {
     struct DataPilotFieldFilter;
@@ -91,7 +92,7 @@ class ScDocument;
  * Base class that abstracts different data source types of a datapilot
  * table.
  */
-class SC_DLLPUBLIC ScDPTableData
+class SC_DLLPUBLIC ScDPTableData : public ::boost::noncopyable
 {
     //	cached data for GetDatePart
     long 	nLastDateVal;
diff --git a/sc/inc/dptablecache.hxx b/sc/inc/dptablecache.hxx
index e773d0fdc..e1ad6ab74 100644
--- a/sc/inc/dptablecache.hxx
+++ b/sc/inc/dptablecache.hxx
@@ -93,7 +93,7 @@ public:
     bool IsEmptyMember( SCROW nRow, sal_uInt16 nColumn ) const;
     bool IsRowEmpty( SCROW nRow ) const;
     bool IsValid() const;
-    bool ValidQuery( SCROW nRow, const ScQueryParam& rQueryParam, bool* pSpecial );
+    bool ValidQuery( SCROW nRow, const ScQueryParam& rQueryParam, bool* pSpecial ) const;
 
     ScDocument* GetDoc() const;//ms-cache-core
     long GetColumnCount() const;
diff --git a/sc/source/core/data/dpcachetable.cxx b/sc/source/core/data/dpcachetable.cxx
index c0049662b..e7f06338a 100644
--- a/sc/source/core/data/dpcachetable.cxx
+++ b/sc/source/core/data/dpcachetable.cxx
@@ -158,14 +158,13 @@ ScDPCacheTable::Criterion::Criterion() :
 
 // ----------------------------------------------------------------------------
 
-ScDPCacheTable::ScDPCacheTable(ScDPCache* pCache) :
+ScDPCacheTable::ScDPCacheTable(const ScDPCache* pCache) :
     mpCache(pCache)
 {
 }
 
 ScDPCacheTable::~ScDPCacheTable()
 {
-    delete mpCache;
 }
 
 sal_Int32 ScDPCacheTable::getRowSize() const
@@ -181,9 +180,9 @@ sal_Int32 ScDPCacheTable::getColSize() const
 void ScDPCacheTable::fillTable(
     const ScQueryParam& rQuery, bool* pSpecial, bool bIgnoreEmptyRows, bool bRepeatIfEmpty)
 {
-   const SCROW	nRowCount = getRowSize();
-   const SCCOL  nColCount = (SCCOL) getColSize();
-   if ( nRowCount <= 0 || nColCount <= 0)
+    const SCROW	nRowCount = getRowSize();
+    const SCCOL  nColCount = (SCCOL) getColSize();
+    if ( nRowCount <= 0 || nColCount <= 0)
         return;
 
     maRowsVisible.clear();
@@ -402,7 +401,6 @@ void ScDPCacheTable::clear()
 {
     maFieldEntries.clear();
     maRowsVisible.clear();
-    delete mpCache;
     mpCache = NULL;
 }
 
@@ -411,9 +409,8 @@ bool ScDPCacheTable::empty() const
     return mpCache == NULL || maFieldEntries.empty();
 }
 
-void ScDPCacheTable::setCache(ScDPCache* p)
+void ScDPCacheTable::setCache(const ScDPCache* p)
 {
-    delete mpCache;
     mpCache = p;
 }
 
@@ -448,9 +445,4 @@ const ScDPCache* ScDPCacheTable::getCache() const
     return mpCache;
 }
 
-ScDPCache* ScDPCacheTable::getCache()
-{
-    return mpCache;
-}
-
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index d1d9b5c24..e5c76d465 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -54,6 +54,7 @@
 #include "dpglobal.hxx"
 #include "globstr.hrc"
 #include <com/sun/star/beans/XPropertySet.hpp>
+#include <com/sun/star/sdb/XCompletedExecution.hpp>
 #include <com/sun/star/sheet/GeneralFunction.hpp>
 #include <com/sun/star/sheet/DataPilotFieldFilter.hpp>
 #include <com/sun/star/sheet/DataPilotFieldOrientation.hpp>
@@ -70,13 +71,15 @@
 #include <com/sun/star/sheet/XDrillDownDataSupplier.hpp>
 
 #include <comphelper/processfactory.hxx>
+#include <comphelper/types.hxx>
 #include <sal/macros.h>
 #include <tools/debug.hxx>
 #include <tools/diagnose_ex.h>
 #include <svl/zforlist.hxx>		// IsNumberFormat
+#include <vcl/msgbox.hxx>
 
 #include <vector>
-#include <stdio.h>
+#include <memory>
 
 using namespace com::sun::star;
 using ::std::vector;
@@ -95,6 +98,13 @@ using ::com::sun::star::sheet::XDimensionsSupplier;
 using ::com::sun::star::beans::XPropertySet;
 using ::rtl::OUString;
 
+#define SC_SERVICE_ROWSET			"com.sun.star.sdb.RowSet"
+#define SC_SERVICE_INTHANDLER		"com.sun.star.task.InteractionHandler"
+
+#define SC_DBPROP_DATASOURCENAME	"DataSourceName"
+#define SC_DBPROP_COMMAND			"Command"
+#define SC_DBPROP_COMMANDTYPE		"CommandType"
+
 // -----------------------------------------------------------------------
 
 #define SCDPSOURCE_SERVICE	"com.sun.star.sheet.DataPilotSource"
@@ -417,7 +427,7 @@ ScDPTableData* ScDPObject::GetTableData()
                 OSL_FAIL("no source descriptor");
                 pSheetDesc = new ScSheetSourceDesc(pDoc);     // dummy defaults
             }
-            ScDPCache* pCache = pSheetDesc->CreateCache();
+            const ScDPCache* pCache = pSheetDesc->CreateCache();
             if (pCache)
                 pData.reset(new ScSheetDPData(pDoc, *pSheetDesc, pCache));
         }
@@ -2383,15 +2393,123 @@ uno::Reference<sheet::XDimensionsSupplier> ScDPObject::CreateSource( const ScDPS
     return xRet;
 }
 
-// ----------------------------------------------------------------------------
+ScDPCollection::SheetCaches::SheetCaches(ScDocument* pDoc) : mpDoc(pDoc) {}
+
+const ScDPCache* ScDPCollection::SheetCaches::getCache(const ScRange& rRange)
+{
+    CachesType::const_iterator itr = maCaches.find(rRange);
+    if (itr != maCaches.end())
+        // already cached.
+        return itr->second;
+
+    ::std::auto_ptr<ScDPCache> pCache(new ScDPCache(mpDoc));
+    pCache->InitFromDoc(mpDoc, rRange);
+    const ScDPCache* p = pCache.get();
+    maCaches.insert(rRange, pCache);
+    return p;
+}
+
+void ScDPCollection::SheetCaches::removeCache(const ScRange& rRange)
+{
+    CachesType::iterator itr = maCaches.find(rRange);
+    if (itr != maCaches.end())
+        maCaches.erase(itr);
+}
+
+ScDPCollection::DBType::DBType(sal_Int32 nSdbType, const OUString& rDBName, const OUString& rCommand) :
+    mnSdbType(nSdbType), maDBName(rDBName), maCommand(rCommand) {}
+
+ScDPCollection::DBCaches::DBCaches(ScDocument* pDoc) : mpDoc(pDoc) {}
+
+const ScDPCache* ScDPCollection::DBCaches::getCache(sal_Int32 nSdbType, const OUString& rDBName, const OUString& rCommand)
+{
+    DBType aType(nSdbType, rDBName, rCommand);
+    CachesType::const_iterator itr = maCaches.find(aType);
+    if (itr != maCaches.end())
+        // already cached.
+        return itr->second;
+
+    uno::Reference<sdbc::XRowSet> xRowSet ;
+    try
+    {
+        xRowSet = uno::Reference<sdbc::XRowSet>(
+            comphelper::getProcessServiceFactory()->createInstance(
+            rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( SC_SERVICE_ROWSET )) ),
+            uno::UNO_QUERY);
+        uno::Reference<beans::XPropertySet> xRowProp( xRowSet, uno::UNO_QUERY );
+        DBG_ASSERT( xRowProp.is(), "can't get RowSet" );
+        if ( xRowProp.is() )
+        {
+            //
+            //  set source parameters
+            //
+            uno::Any aAny;
+            aAny <<= rDBName;
+            xRowProp->setPropertyValue(
+                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_DATASOURCENAME)), aAny );
+
+            aAny <<= rCommand;
+            xRowProp->setPropertyValue(
+                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_COMMAND)), aAny );
+
+            aAny <<= nSdbType;
+            xRowProp->setPropertyValue(
+                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_COMMANDTYPE)), aAny );
+
+            uno::Reference<sdb::XCompletedExecution> xExecute( xRowSet, uno::UNO_QUERY );
+            if ( xExecute.is() )
+            {
+                uno::Reference<task::XInteractionHandler> xHandler(
+                    comphelper::getProcessServiceFactory()->createInstance(
+                    rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( SC_SERVICE_INTHANDLER )) ),
+                    uno::UNO_QUERY);
+                xExecute->executeWithCompletion( xHandler );
+            }
+            else
+                xRowSet->execute();
+        }
+    }
+    catch ( sdbc::SQLException& rError )
+    {
+        //! store error message
+        InfoBox aInfoBox( 0, String(rError.Message) );
+        aInfoBox.Execute();
+        return NULL;
+    }
+    catch ( uno::Exception& )
+    {
+        OSL_FAIL("Unexpected exception in database");
+        return NULL;
+    }
+
+    ::std::auto_ptr<ScDPCache> pCache(new ScDPCache(mpDoc));
+    SvNumberFormatter aFormat(mpDoc->GetServiceManager(), ScGlobal::eLnge);
+    pCache->InitFromDataBase(xRowSet, *aFormat.GetNullDate());
+    ::comphelper::disposeComponent(xRowSet);
+    const ScDPCache* p = pCache.get();
+    maCaches.insert(aType, pCache);
+    return p;
+}
+
+void ScDPCollection::DBCaches::removeCache(sal_Int32 nSdbType, const OUString& rDBName, const OUString& rCommand)
+{
+    DBType aType(nSdbType, rDBName, rCommand);
+    CachesType::iterator itr = maCaches.find(aType);
+    if (itr != maCaches.end())
+        maCaches.erase(itr);
+}
 
 ScDPCollection::ScDPCollection(ScDocument* pDocument) :
-    pDoc( pDocument )
+    pDoc( pDocument ),
+    maSheetCaches(pDocument),
+    maDBCaches(pDocument)
 {
 }
 
 ScDPCollection::ScDPCollection(const ScDPCollection& r) :
-    pDoc(r.pDoc)
+    pDoc(r.pDoc),
+    maSheetCaches(r.pDoc),
+    maDBCaches(r.pDoc)
 {
 }
 
@@ -2584,4 +2702,25 @@ bool ScDPCollection::HasDPTable(SCCOL nCol, SCROW nRow, SCTAB nTab) const
     return pMergeAttr->HasDPTable();
 }
 
+ScDPCollection::SheetCaches& ScDPCollection::GetSheetCaches()
+{
+    return maSheetCaches;
+}
+
+ScDPCollection::DBCaches& ScDPCollection::GetDBCaches()
+{
+    return maDBCaches;
+}
+
+bool operator<(const ScDPCollection::DBType& left, const ScDPCollection::DBType& right)
+{
+    if (left.mnSdbType != right.mnSdbType)
+        return left.mnSdbType < right.mnSdbType;
+
+    if (!left.maDBName.equals(right.maDBName))
+        return left.maDBName < right.maDBName;
+
+    return left.maCommand < right.maCommand;
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/dpsdbtab.cxx b/sc/source/core/data/dpsdbtab.cxx
index 1c8f8d56f..417c14246 100644
--- a/sc/source/core/data/dpsdbtab.cxx
+++ b/sc/source/core/data/dpsdbtab.cxx
@@ -33,23 +33,6 @@
 
 // INCLUDE --------------------------------------------------------------
 
-#include <tools/debug.hxx>
-#include <vcl/msgbox.hxx>
-#include <svl/zforlist.hxx>
-#include <comphelper/processfactory.hxx>
-#include <comphelper/types.hxx>
-
-#include <com/sun/star/sheet/DataImportMode.hpp>
-#include <com/sun/star/beans/XPropertySet.hpp>
-#include <com/sun/star/sdb/CommandType.hpp>
-#include <com/sun/star/sdb/XCompletedExecution.hpp>
-#include <com/sun/star/sdbc/DataType.hpp>
-#include <com/sun/star/sdbc/XRow.hpp>
-#include <com/sun/star/sdbc/XRowSet.hpp>
-#include <com/sun/star/sdbc/XResultSetMetaDataSupplier.hpp>
-#include <com/sun/star/lang/XMultiServiceFactory.hpp>
-#include <com/sun/star/sheet/DataPilotFieldFilter.hpp>
-
 #include "dpsdbtab.hxx"
 #include "collect.hxx"
 #include "global.hxx"
@@ -59,100 +42,41 @@
 #include "document.hxx"
 #include "dpobject.hxx"
 
+#include <com/sun/star/sheet/DataImportMode.hpp>
+#include <com/sun/star/sdb/CommandType.hpp>
+
 using namespace com::sun::star;
 
 using ::std::vector;
 using ::com::sun::star::uno::Sequence;
-using ::com::sun::star::uno::Reference;
 using ::com::sun::star::uno::Any;
-using ::com::sun::star::uno::UNO_QUERY;
 
-#define SC_SERVICE_ROWSET			"com.sun.star.sdb.RowSet"
-#define SC_SERVICE_INTHANDLER		"com.sun.star.task.InteractionHandler"
-
-//!	move to a header file?
-#define SC_DBPROP_DATASOURCENAME	"DataSourceName"
-#define SC_DBPROP_COMMAND			"Command"
-#define SC_DBPROP_COMMANDTYPE		"CommandType"
-
-ScDPCache* ScImportSourceDesc::CreateCache() const
+sal_Int32 ScImportSourceDesc::GetCommandType() const
 {
-    if (!mpDoc)
-        return NULL;
-
     sal_Int32 nSdbType = -1;
-  
+
     switch ( nType )
     {
-    case sheet::DataImportMode_SQL:        nSdbType = sdb::CommandType::COMMAND;  break;
-    case sheet::DataImportMode_TABLE:   nSdbType = sdb::CommandType::TABLE;      break;
-    case sheet::DataImportMode_QUERY:  nSdbType = sdb::CommandType::QUERY;     break;
-    default:
-        return NULL;
+        case sheet::DataImportMode_SQL:   nSdbType = sdb::CommandType::COMMAND; break;
+        case sheet::DataImportMode_TABLE: nSdbType = sdb::CommandType::TABLE;   break;
+        case sheet::DataImportMode_QUERY: nSdbType = sdb::CommandType::QUERY;   break;
+        default:
+            ;
     }
+    return nSdbType;
+}
 
-    ScDPCache* pCache = new ScDPCache(mpDoc);
-    
-    uno::Reference<sdbc::XRowSet> xRowSet ;
-    try
-    {
-        xRowSet = uno::Reference<sdbc::XRowSet>(
-            comphelper::getProcessServiceFactory()->createInstance(
-            rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( SC_SERVICE_ROWSET )) ),
-            uno::UNO_QUERY);
-        uno::Reference<beans::XPropertySet> xRowProp( xRowSet, uno::UNO_QUERY );
-        DBG_ASSERT( xRowProp.is(), "can't get RowSet" );
-        if ( xRowProp.is() )
-        {
-            //
-            //  set source parameters
-            //
-            uno::Any aAny;
-            aAny <<= rtl::OUString( aDBName );
-            xRowProp->setPropertyValue(
-                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_DATASOURCENAME)), aAny );
-
-            aAny <<= rtl::OUString( aObject );
-            xRowProp->setPropertyValue(
-                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_COMMAND)), aAny );
-
-            aAny <<= nSdbType;
-            xRowProp->setPropertyValue(
-                rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(SC_DBPROP_COMMANDTYPE)), aAny );
-
-            uno::Reference<sdb::XCompletedExecution> xExecute( xRowSet, uno::UNO_QUERY );
-            if ( xExecute.is() )
-            {
-                uno::Reference<task::XInteractionHandler> xHandler(
-                    comphelper::getProcessServiceFactory()->createInstance(
-                    rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( SC_SERVICE_INTHANDLER )) ),
-                    uno::UNO_QUERY);
-                xExecute->executeWithCompletion( xHandler );
-            }
-            else
-                xRowSet->execute();
-            SvNumberFormatter aFormat(mpDoc->GetServiceManager(), ScGlobal::eLnge);
-            pCache->InitFromDataBase( xRowSet, *aFormat.GetNullDate() );
-        }
-    }
-    catch ( sdbc::SQLException& rError )
-    {
-        //! store error message
-        delete pCache;
-        pCache = NULL;
-        InfoBox aInfoBox( 0, String(rError.Message) );
-        aInfoBox.Execute();
-    }
-    catch ( uno::Exception& )
-    {
-        delete pCache;
-        pCache = NULL;
-        OSL_FAIL("Unexpected exception in database");
-    }
+const ScDPCache* ScImportSourceDesc::CreateCache() const
+{
+    if (!mpDoc)
+        return NULL;
 
+    sal_Int32 nSdbType = GetCommandType();
+    if (nSdbType < 0)
+        return NULL;
 
-    ::comphelper::disposeComponent( xRowSet );
-     return pCache;
+    ScDPCollection::DBCaches& rCaches = mpDoc->GetDPCollection()->GetDBCaches();
+    return rCaches.getCache(nSdbType, aDBName, aObject);
 }
 
 ScDatabaseDPData::ScDatabaseDPData(ScDocument* pDoc, const ScImportSourceDesc& rImport) :
diff --git a/sc/source/core/data/dpshttab.cxx b/sc/source/core/data/dpshttab.cxx
index ca68c6cdb..ed22149c2 100644
--- a/sc/source/core/data/dpshttab.cxx
+++ b/sc/source/core/data/dpshttab.cxx
@@ -60,7 +60,7 @@ using ::std::vector;
 
 // -----------------------------------------------------------------------
 
-ScSheetDPData::ScSheetDPData(ScDocument* pD, const ScSheetSourceDesc& rDesc, ScDPCache* pCache) :
+ScSheetDPData::ScSheetDPData(ScDocument* pD, const ScSheetSourceDesc& rDesc, const ScDPCache* pCache) :
     ScDPTableData(pD),
     aQuery ( rDesc.GetQueryParam() ),
     pSpecial(NULL),
@@ -307,7 +307,7 @@ bool ScSheetSourceDesc::operator== (const ScSheetSourceDesc& rOther) const
         maQueryParam  == rOther.maQueryParam;
 }
 
-ScDPCache* ScSheetSourceDesc::CreateCache() const
+const ScDPCache* ScSheetSourceDesc::CreateCache() const
 {
     if (!mpDoc)
         return NULL;
@@ -319,9 +319,10 @@ ScDPCache* ScSheetSourceDesc::CreateCache() const
         return NULL;
     }
 
-    ScDPCache* pCache = new ScDPCache(mpDoc);
-    pCache->InitFromDoc(mpDoc, GetSourceRange());
-    return pCache;
+    // All cache instances are managed centrally by ScDPCollection.
+    ScDPCollection* pDPs = mpDoc->GetDPCollection();
+    ScDPCollection::SheetCaches& rCaches = pDPs->GetSheetCaches();
+    return rCaches.getCache(GetSourceRange());
 }
 
 long ScSheetSourceDesc::GetCacheId() const
diff --git a/sc/source/core/data/dptablecache.cxx b/sc/source/core/data/dptablecache.cxx
index de7480c9d..3aff11870 100644
--- a/sc/source/core/data/dptablecache.cxx
+++ b/sc/source/core/data/dptablecache.cxx
@@ -565,7 +565,7 @@ sal_uLong ScDPCache::GetDimNumType( SCCOL nDim) const
         return GetNumType(maTableDataValues[nDim][0].nNumFormat);
 }
 
-bool ScDPCache::ValidQuery( SCROW nRow, const ScQueryParam &rParam, bool *pSpecial)
+bool ScDPCache::ValidQuery( SCROW nRow, const ScQueryParam &rParam, bool *pSpecial) const
 {
     if (!rParam.GetEntry(0).bDoQuery)
         return true;
diff --git a/sc/source/filter/excel/xepivot.cxx b/sc/source/filter/excel/xepivot.cxx
index a3f312579..6d80be011 100644
--- a/sc/source/filter/excel/xepivot.cxx
+++ b/sc/source/filter/excel/xepivot.cxx
@@ -538,7 +538,7 @@ void XclExpPCField::InsertNumDateGroupItems( const ScDPObject& rDPObj, const ScD
     if( const ScSheetSourceDesc* pSrcDesc = rDPObj.GetSheetDesc() )
     {
         // get the string collection with original source elements
-        ScDPCache* pCache = pSrcDesc->CreateCache();
+        const ScDPCache* pCache = pSrcDesc->CreateCache();
         if (!pCache)
             return;
 
diff --git a/sc/source/filter/xml/XMLExportDataPilot.cxx b/sc/source/filter/xml/XMLExportDataPilot.cxx
index c39efaa8d..146e7bd28 100644
--- a/sc/source/filter/xml/XMLExportDataPilot.cxx
+++ b/sc/source/filter/xml/XMLExportDataPilot.cxx
@@ -858,24 +858,24 @@ void ScXMLExportDataPilot::WriteDataPilots(const uno::Reference <sheet::XSpreads
                 case sheet::DataImportMode_NONE : break;
                 case sheet::DataImportMode_QUERY :
                 {
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, rtl::OUString(pImpSource->aDBName));
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_QUERY_NAME, rtl::OUString(pImpSource->aObject));
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, pImpSource->aDBName);
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_QUERY_NAME, pImpSource->aObject);
                     SvXMLElementExport aElemID(rExport, XML_NAMESPACE_TABLE, XML_DATABASE_SOURCE_QUERY, sal_True, sal_True);
                     rExport.CheckAttrList();
                 }
                 break;
                 case sheet::DataImportMode_TABLE :
                 {
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, rtl::OUString(pImpSource->aDBName));
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_TABLE_NAME, rtl::OUString(pImpSource->aObject));
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, pImpSource->aDBName);
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_TABLE_NAME, pImpSource->aObject);
                     SvXMLElementExport aElemID(rExport, XML_NAMESPACE_TABLE, XML_DATABASE_SOURCE_TABLE, sal_True, sal_True);
                     rExport.CheckAttrList();
                 }
                 break;
                 case sheet::DataImportMode_SQL :
                 {
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, rtl::OUString(pImpSource->aDBName));
-                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_SQL_STATEMENT, rtl::OUString(pImpSource->aObject));
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_DATABASE_NAME, pImpSource->aDBName);
+                    rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_SQL_STATEMENT, pImpSource->aObject);
                     if (!pImpSource->bNative)
                         rExport.AddAttribute(XML_NAMESPACE_TABLE, XML_PARSE_SQL_STATEMENT, XML_TRUE);
                     SvXMLElementExport aElemID(rExport, XML_NAMESPACE_TABLE, XML_DATABASE_SOURCE_SQL, sal_True, sal_True);
diff --git a/sc/source/ui/cctrl/dpcontrol.cxx b/sc/source/ui/cctrl/dpcontrol.cxx
index 2a5d41541..380cdf9cf 100644
--- a/sc/source/ui/cctrl/dpcontrol.cxx
+++ b/sc/source/ui/cctrl/dpcontrol.cxx
@@ -1032,6 +1032,7 @@ ScDPFieldPopupWindow::ScDPFieldPopupWindow(Window* pParent, ScDocument* pDoc) :
     maChkToggleAll.SetPosSizePixel(aPos, aSize);
     maChkToggleAll.SetFont(getLabelFont());
     maChkToggleAll.SetText(ScRscStrLoader(RID_POPUP_FILTER, STR_BTN_TOGGLE_ALL).GetString());
+    maChkToggleAll.SetTextColor(rStyle.GetMenuTextColor());
     maChkToggleAll.SetControlBackground(rStyle.GetMenuColor());
     maChkToggleAll.SetClickHdl( LINK(this, ScDPFieldPopupWindow, TriStateHdl) );
     maChkToggleAll.Show();
diff --git a/sc/source/ui/dbgui/dapidata.cxx b/sc/source/ui/dbgui/dapidata.cxx
index fda584a62..be5a67cb6 100644
--- a/sc/source/ui/dbgui/dapidata.cxx
+++ b/sc/source/ui/dbgui/dapidata.cxx
@@ -130,7 +130,7 @@ void ScDataPilotDatabaseDlg::GetValues( ScImportSourceDesc& rDesc )
     rDesc.aDBName = aLbDatabase.GetSelectEntry();
     rDesc.aObject = aCbObject.GetText();
 
-    if ( !rDesc.aDBName.Len() || !rDesc.aObject.Len() )
+    if (rDesc.aDBName.isEmpty() || rDesc.aObject.isEmpty())
         rDesc.nType = sheet::DataImportMode_NONE;
     else if ( nSelect == DP_TYPELIST_TABLE )
         rDesc.nType = sheet::DataImportMode_TABLE;
diff --git a/sc/source/ui/view/dbfunc3.cxx b/sc/source/ui/view/dbfunc3.cxx
index 1fa24c1e8..902947cde 100644
--- a/sc/source/ui/view/dbfunc3.cxx
+++ b/sc/source/ui/view/dbfunc3.cxx
@@ -72,6 +72,8 @@
 #include "dbdocfun.hxx"
 #include "dpoutput.hxx"
 #include "dptabsrc.hxx"
+#include "dpshttab.hxx"
+#include "dpsdbtab.hxx"
 #include "editable.hxx"
 #include "docpool.hxx"
 #include "patattr.hxx"
@@ -697,13 +699,29 @@ void ScDBFunc::RecalcPivotTable()
     ScDocShell* pDocSh  = GetViewData()->GetDocShell();
     ScDocument* pDoc	= GetViewData()->GetDocument();
 
-    //	old pivot not used any more
-
+    ScDPCollection* pDPs = pDoc->GetDPCollection();
     ScDPObject*	pDPObj	= pDoc->GetDPAtCursor( GetViewData()->GetCurX(),
                                                   GetViewData()->GetCurY(),
                                                   GetViewData()->GetTabNo() );
-    if ( pDPObj )
+    if (pDPs && pDPObj)
     {
+        // Remove existing data cache for the data that this datapilot uses,
+        // to force re-build data cache.
+        if (pDPObj->IsSheetData())
+        {
+            // data source is internal sheet.
+            ScDPCollection::SheetCaches& rCaches = pDPs->GetSheetCaches();
+            const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
+            rCaches.removeCache(pDesc->GetSourceRange());
+        }
+        else if (pDPObj->IsImportData())
+        {
+            // data source is external database.
+            ScDPCollection::DBCaches& rCaches = pDPs->GetDBCaches();
+            const ScImportSourceDesc* pDesc = pDPObj->GetImportSourceDesc();
+            rCaches.removeCache(pDesc->GetCommandType(), pDesc->aDBName, pDesc->aObject);
+        }
+
         ScDBDocFunc aFunc( *pDocSh );
         aFunc.DataPilotUpdate( pDPObj, pDPObj, true, false );
         CursorPosChanged();		// shells may be switched