sc/source/core/data/dpsdbtab.cxx

   1 /*************************************************************************
   2  *
   3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   4  *
   5  * Copyright 2008 by Sun Microsystems, Inc.
   6  *
   7  * OpenOffice.org - a multi-platform office productivity suite
   8  *
   9  * $RCSfile: dpsdbtab.cxx,v $
  10  * $Revision: 1.15 $
  11  *
  12  * This file is part of OpenOffice.org.
  13  *
  14  * OpenOffice.org is free software: you can redistribute it and/or modify
  15  * it under the terms of the GNU Lesser General Public License version 3
  16  * only, as published by the Free Software Foundation.
  17  *
  18  * OpenOffice.org is distributed in the hope that it will be useful,
  19  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  20  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  21  * GNU Lesser General Public License version 3 for more details
  22  * (a copy is included in the LICENSE file that accompanied this code).
  23  *
  24  * You should have received a copy of the GNU Lesser General Public License
  25  * version 3 along with OpenOffice.org.  If not, see
  26  * <http://www.openoffice.org/license.html>
  27  * for a copy of the LGPLv3 License.
  28  *
  29  ************************************************************************/
  30
  31 // MARKER(update_precomp.py): autogen include statement, do not remove
  32 #include "precompiled_sc.hxx"
  33
  34
  35
  36 // INCLUDE --------------------------------------------------------------
  37
  38 #include <tools/debug.hxx>
  39 #include <vcl/msgbox.hxx>
  40 #include <svtools/zforlist.hxx>
  41 #include <comphelper/processfactory.hxx>
  42 #include <comphelper/types.hxx>
  43
  44 #include <com/sun/star/sheet/DataImportMode.hpp>
  45 #include <com/sun/star/beans/XPropertySet.hpp>
  46 #include <com/sun/star/sdb/CommandType.hpp>
  47 #include <com/sun/star/sdb/XCompletedExecution.hpp>
  48 #include <com/sun/star/sdbc/DataType.hpp>
  49 #include <com/sun/star/sdbc/XRow.hpp>
  50 #include <com/sun/star/sdbc/XRowSet.hpp>
  51 #include <com/sun/star/sdbc/XResultSetMetaDataSupplier.hpp>
  52 #include <com/sun/star/lang/XMultiServiceFactory.hpp>
  53 #include <com/sun/star/sheet/DataPilotFieldFilter.hpp>
  54
  55 #include "dpsdbtab.hxx"
  56 #include "collect.hxx"
  57 #include "global.hxx"
  58 #include "globstr.hrc"
  59 #include "dpcachetable.hxx"
  60 #include "dptabres.hxx"
  61 #include "document.hxx"
  62 #include "dpobject.hxx"
  63
  64 using namespace com::sun::star;
  65
  66 using ::std::vector;
  67 using ::std::hash_map;
  68 using ::std::hash_set;
  69 using ::com::sun::star::uno::Sequence;
  70 using ::com::sun::star::uno::Reference;
  71 using ::com::sun::star::uno::Any;
  72 using ::com::sun::star::uno::UNO_QUERY;
  73
  74 #define SC_SERVICE_ROWSET                       "com.sun.star.sdb.RowSet"
  75 #define SC_SERVICE_INTHANDLER           "com.sun.star.sdb.InteractionHandler"
  76
  77 //!     move to a header file?
  78 #define SC_DBPROP_DATASOURCENAME        "DataSourceName"
  79 #define SC_DBPROP_COMMAND                       "Command"
  80 #define SC_DBPROP_COMMANDTYPE           "CommandType"
  81
  82 // -----------------------------------------------------------------------
  83
  84 class ScDatabaseDPData_Impl
  85 {
  86 public:
  87     ::com::sun::star::uno::Reference< ::com::sun::star::lang::XMultiServiceFactory > xServiceManager;
  88     ScImportSourceDesc  aDesc;
  89     long                                nColCount;
  90     uno::Reference<sdbc::XRowSet> xRowSet;
  91     sal_Int32*                  pTypes;
  92     SvNumberFormatter*  pFormatter;
  93
  94     ScDPCacheTable      aCacheTable;
  95
  96     ScDatabaseDPData_Impl(ScDPCollection* p) :
  97         aCacheTable(p)
  98     {
  99     }
 100 };
 101
 102 // -----------------------------------------------------------------------
 103
 104 ScDatabaseDPData::ScDatabaseDPData(
 105     ScDocument* pDoc,
 106     const ScImportSourceDesc& rImport ) :
 107     ScDPTableData(pDoc)
 108 {
 109     pImpl = new ScDatabaseDPData_Impl(pDoc->GetDPCollection());
 110     pImpl->xServiceManager = pDoc->GetServiceManager();
 111     pImpl->aDesc = rImport;
 112     pImpl->nColCount = 0;
 113     pImpl->pTypes = NULL;
 114     pImpl->pFormatter = NULL;           // created on demand
 115
 116     OpenDatabase();
 117     CreateCacheTable();
 118 }
 119
 120 ScDatabaseDPData::~ScDatabaseDPData()
 121 {
 122     ::comphelper::disposeComponent( pImpl->xRowSet );
 123
 124     delete[] pImpl->pTypes;
 125     delete pImpl->pFormatter;           // NumberFormatter is local for this object
 126     delete pImpl;
 127 }
 128
 129 void ScDatabaseDPData::DisposeData()
 130 {
 131     //! use OpenDatabase here?
 132     pImpl->aCacheTable.clear();
 133 }
 134
 135 BOOL ScDatabaseDPData::OpenDatabase()
 136 {
 137     sal_Int32 nSdbType = -1;
 138     switch ( pImpl->aDesc.nType )
 139     {
 140         case sheet::DataImportMode_SQL:         nSdbType = sdb::CommandType::COMMAND;   break;
 141         case sheet::DataImportMode_TABLE:       nSdbType = sdb::CommandType::TABLE;             break;
 142         case sheet::DataImportMode_QUERY:       nSdbType = sdb::CommandType::QUERY;             break;
 143         default:
 144             return FALSE;
 145     }
 146
 147     BOOL bSuccess = FALSE;
 148     try
 149     {
 150         pImpl->xRowSet = uno::Reference<sdbc::XRowSet>(
 151                 comphelper::getProcessServiceFactory()->createInstance(
 152                     rtl::OUString::createFromAscii( SC_SERVICE_ROWSET ) ),
 153                 uno::UNO_QUERY);
 154         uno::Reference<beans::XPropertySet> xRowProp( pImpl->xRowSet, uno::UNO_QUERY );
 155         DBG_ASSERT( xRowProp.is(), "can't get RowSet" );
 156         if ( xRowProp.is() )
 157         {
 158             //
 159             //  set source parameters
 160             //
 161
 162             uno::Any aAny;
 163
 164             aAny <<= rtl::OUString( pImpl->aDesc.aDBName );
 165             xRowProp->setPropertyValue(
 166                         rtl::OUString::createFromAscii(SC_DBPROP_DATASOURCENAME), aAny );
 167
 168             aAny <<= rtl::OUString( pImpl->aDesc.aObject );
 169             xRowProp->setPropertyValue(
 170                         rtl::OUString::createFromAscii(SC_DBPROP_COMMAND), aAny );
 171
 172             aAny <<= nSdbType;
 173             xRowProp->setPropertyValue(
 174                         rtl::OUString::createFromAscii(SC_DBPROP_COMMANDTYPE), aAny );
 175
 176             uno::Reference<sdb::XCompletedExecution> xExecute( pImpl->xRowSet, uno::UNO_QUERY );
 177             if ( xExecute.is() )
 178             {
 179                 uno::Reference<task::XInteractionHandler> xHandler(
 180                         comphelper::getProcessServiceFactory()->createInstance(
 181                             rtl::OUString::createFromAscii( SC_SERVICE_INTHANDLER ) ),
 182                         uno::UNO_QUERY);
 183                 xExecute->executeWithCompletion( xHandler );
 184             }
 185             else
 186                 pImpl->xRowSet->execute();
 187
 188             //
 189             //  get column descriptions
 190             //
 191
 192             pImpl->nColCount = 0;
 193             uno::Reference<sdbc::XResultSetMetaData> xMeta;
 194             uno::Reference<sdbc::XResultSetMetaDataSupplier> xMetaSupp( pImpl->xRowSet, uno::UNO_QUERY );
 195             if ( xMetaSupp.is() )
 196                 xMeta = xMetaSupp->getMetaData();
 197             if ( xMeta.is() )
 198                 pImpl->nColCount = xMeta->getColumnCount();     // this is the number of real columns
 199
 200             uno::Reference<sdbc::XResultSet> xResSet( pImpl->xRowSet, uno::UNO_QUERY );
 201             if ( pImpl->nColCount > 0 && xResSet.is() )
 202             {
 203                 pImpl->pTypes = new sal_Int32[pImpl->nColCount];
 204                 for (long nCol=0; nCol<pImpl->nColCount; nCol++)
 205                     pImpl->pTypes[nCol]  = xMeta->getColumnType( nCol+1 );
 206
 207                 bSuccess = TRUE;
 208             }
 209         }
 210     }
 211     catch ( sdbc::SQLException& rError )
 212     {
 213         //!     store error message
 214         InfoBox aInfoBox( 0, String(rError.Message) );
 215         aInfoBox.Execute();
 216     }
 217     catch ( uno::Exception& )
 218     {
 219         DBG_ERROR("Unexpected exception in database");
 220     }
 221
 222
 223     if (!bSuccess)
 224         ::comphelper::disposeComponent( pImpl->xRowSet );
 225
 226     return bSuccess;
 227 }
 228
 229 long ScDatabaseDPData::GetColumnCount()
 230 {
 231     return pImpl->nColCount;
 232 }
 233
 234 const TypedScStrCollection& ScDatabaseDPData::GetColumnEntries(long nColumn)
 235 {
 236     CreateCacheTable();
 237     return pImpl->aCacheTable.getFieldEntries(nColumn);
 238 }
 239
 240 String ScDatabaseDPData::getDimensionName(long nColumn)
 241 {
 242     if (getIsDataLayoutDimension(nColumn))
 243     {
 244         //!     different internal and display names?
 245         //return "Data";
 246         return ScGlobal::GetRscString(STR_PIVOT_DATA);
 247     }
 248
 249     CreateCacheTable();
 250     const String* pStr = pImpl->aCacheTable.getFieldName(nColumn);
 251     if (pStr)
 252         return *pStr;
 253
 254     DBG_ERROR("getDimensionName: invalid dimension");
 255     return String();
 256 }
 257
 258 BOOL ScDatabaseDPData::getIsDataLayoutDimension(long nColumn)
 259 {
 260     return ( nColumn == pImpl->nColCount );
 261 }
 262
 263 BOOL ScDatabaseDPData::IsDateDimension(long /* nDim */)
 264 {
 265     //! later...
 266     return FALSE;
 267 }
 268
 269 void ScDatabaseDPData::SetEmptyFlags( BOOL /* bIgnoreEmptyRows */, BOOL /* bRepeatIfEmpty */ )
 270 {
 271     //  not used for database data
 272     //! disable flags
 273 }
 274
 275 void ScDatabaseDPData::CreateCacheTable()
 276 {
 277     if (!pImpl->aCacheTable.empty())
 278         return;
 279
 280     // Get null date.
 281     if (!pImpl->pFormatter)
 282         pImpl->pFormatter = new SvNumberFormatter(pImpl->xServiceManager, ScGlobal::eLnge);
 283
 284     pImpl->aCacheTable.fillTable(pImpl->xRowSet, *pImpl->pFormatter->GetNullDate());
 285 }
 286
 287 void ScDatabaseDPData::FilterCacheTable(const vector<ScDPCacheTable::Criterion>& rCriteria, const hash_set<sal_Int32>& rCatDims)
 288 {
 289     CreateCacheTable();
 290     pImpl->aCacheTable.filterByPageDimension(
 291         rCriteria, (IsRepeatIfEmpty() ? rCatDims : hash_set<sal_Int32>()));
 292 }
 293
 294 void ScDatabaseDPData::GetDrillDownData(const vector<ScDPCacheTable::Criterion>& rCriteria, const hash_set<sal_Int32>& rCatDims, Sequence< Sequence<Any> >& rData)
 295 {
 296     CreateCacheTable();
 297     sal_Int32 nRowSize = pImpl->aCacheTable.getRowSize();
 298     if (!nRowSize)
 299         return;
 300
 301     pImpl->aCacheTable.filterTable(
 302         rCriteria, rData, IsRepeatIfEmpty() ? rCatDims : hash_set<sal_Int32>());
 303 }
 304
 305 void ScDatabaseDPData::CalcResults(CalcInfo& rInfo, bool bAutoShow)
 306 {
 307     CreateCacheTable();
 308     CalcResultsFromCacheTable(pImpl->aCacheTable, rInfo, bAutoShow);
 309 }
 310
 311 const ScDPCacheTable& ScDatabaseDPData::GetCacheTable() const
 312 {
 313     return pImpl->aCacheTable;
 314 }
 315
 316 // -----------------------------------------------------------------------
 317
 318
 319
 320
 321