connectivity/source/commontools/predicateinput.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: predicateinput.cxx,v $
  10  * $Revision: 1.10 $
  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_connectivity.hxx"
  33 #include <connectivity/predicateinput.hxx>
  34 #include <comphelper/types.hxx>
  35 #include <connectivity/dbtools.hxx>
  36 #include <com/sun/star/sdbc/DataType.hpp>
  37 #include <osl/diagnose.h>
  38 #include <connectivity/sqlnode.hxx>
  39 #include <comphelper/numbers.hxx>
  40
  41 //.........................................................................
  42 namespace dbtools
  43 {
  44 //.........................................................................
  45
  46     using ::com::sun::star::sdbc::XConnection;
  47     using ::com::sun::star::lang::XMultiServiceFactory;
  48     using ::com::sun::star::util::XNumberFormatsSupplier;
  49     using ::com::sun::star::util::XNumberFormatter;
  50     using ::com::sun::star::uno::UNO_QUERY;
  51     using ::com::sun::star::beans::XPropertySet;
  52     using ::com::sun::star::beans::XPropertySetInfo;
  53     using ::com::sun::star::lang::Locale;
  54     using ::com::sun::star::uno::Exception;
  55     using ::com::sun::star::i18n::XLocaleData;
  56     using ::com::sun::star::i18n::LocaleDataItem;
  57
  58     using namespace ::com::sun::star::sdbc;
  59     using namespace ::connectivity;
  60
  61     using ::connectivity::OSQLParseNode;
  62
  63     #define Reference ::com::sun::star::uno::Reference
  64
  65     //=====================================================================
  66     //---------------------------------------------------------------------
  67     static sal_Unicode lcl_getSeparatorChar( const ::rtl::OUString& _rSeparator, sal_Unicode _nFallback )
  68     {
  69         OSL_ENSURE( 0 < _rSeparator.getLength(), "::lcl_getSeparatorChar: invalid separator string!" );
  70
  71         sal_Unicode nReturn( _nFallback );
  72         if ( _rSeparator.getLength() )
  73             nReturn = static_cast< sal_Char >( _rSeparator.getStr()[0] );
  74         return nReturn;
  75     }
  76
  77     //=====================================================================
  78     //= OPredicateInputController
  79     //=====================================================================
  80     //---------------------------------------------------------------------
  81     sal_Bool OPredicateInputController::getSeparatorChars( const Locale& _rLocale, sal_Unicode& _rDecSep, sal_Unicode& _rThdSep ) const
  82     {
  83         _rDecSep = '.';
  84         _rThdSep = ',';
  85         try
  86         {
  87             LocaleDataItem aLocaleData;
  88             if ( m_xLocaleData.is() )
  89             {
  90                 aLocaleData = m_xLocaleData->getLocaleItem( _rLocale );
  91                 _rDecSep = lcl_getSeparatorChar( aLocaleData.decimalSeparator, _rDecSep );
  92                 _rThdSep = lcl_getSeparatorChar( aLocaleData.decimalSeparator, _rThdSep );
  93                 return sal_True;
  94             }
  95         }
  96         catch( const Exception& )
  97         {
  98             OSL_ENSURE( sal_False, "OPredicateInputController::getSeparatorChars: caught an exception!" );
  99         }
 100         return sal_False;
 101     }
 102
 103     //---------------------------------------------------------------------
 104     OPredicateInputController::OPredicateInputController(
 105         const Reference< XMultiServiceFactory >& _rxORB, const Reference< XConnection >& _rxConnection, const IParseContext* _pParseContext )
 106         :m_xORB( _rxORB )
 107         ,m_xConnection( _rxConnection )
 108         ,m_aParser( m_xORB, _pParseContext )
 109     {
 110         try
 111         {
 112             // create a number formatter / number formats supplier pair
 113             OSL_ENSURE( m_xORB.is(), "OPredicateInputController::OPredicateInputController: need a service factory!" );
 114             if ( m_xORB.is() )
 115             {
 116                 m_xFormatter = Reference< XNumberFormatter >( m_xORB->createInstance(
 117                     ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "com.sun.star.util.NumberFormatter" ) ) ),
 118                     UNO_QUERY
 119                 );
 120             }
 121
 122             Reference< XNumberFormatsSupplier >  xNumberFormats = ::dbtools::getNumberFormats( m_xConnection, sal_True );
 123             if ( !xNumberFormats.is() )
 124                 ::comphelper::disposeComponent( m_xFormatter );
 125             else if ( m_xFormatter.is() )
 126                 m_xFormatter->attachNumberFormatsSupplier( xNumberFormats );
 127
 128             // create the locale data
 129             if ( m_xORB.is() )
 130             {
 131                 m_xLocaleData = m_xLocaleData.query( m_xORB->createInstance(
 132                     ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "com.sun.star.i18n.LocaleData" ) ) )
 133                 );
 134             }
 135         }
 136         catch( const Exception& )
 137         {
 138             OSL_ENSURE( sal_False, "OPredicateInputController::OPredicateInputController: caught an exception!" );
 139         }
 140     }
 141
 142     //---------------------------------------------------------------------
 143     OSQLParseNode* OPredicateInputController::implPredicateTree(::rtl::OUString& _rErrorMessage, const ::rtl::OUString& _rStatement, const Reference< XPropertySet > & _rxField) const
 144     {
 145         OSQLParseNode* pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, _rStatement, m_xFormatter, _rxField );
 146         if ( !pReturn )
 147         {       // is it a text field ?
 148             sal_Int32 nType = DataType::OTHER;
 149             _rxField->getPropertyValue( ::rtl::OUString::createFromAscii( "Type" ) ) >>= nType;
 150
 151             if  (       ( DataType::CHAR == nType )
 152                 ||      ( DataType::VARCHAR == nType )
 153                 ||      ( DataType::LONGVARCHAR == nType )
 154                 )
 155             {   // yes -> force a quoted text and try again
 156                 ::rtl::OUString sQuoted( _rStatement );
 157                 if      (       sQuoted.getLength()
 158                     &&  (       (sQuoted.getStr()[0] != '\'')
 159                         ||      (sQuoted.getStr()[ sQuoted.getLength() - 1 ] != '\'' )
 160                         )
 161                     )
 162                 {
 163                     static const ::rtl::OUString sSingleQuote( RTL_CONSTASCII_USTRINGPARAM( "'" ) );
 164                     static const ::rtl::OUString sDoubleQuote( RTL_CONSTASCII_USTRINGPARAM( "''" ) );
 165
 166                     sal_Int32 nIndex = -1;
 167                     sal_Int32 nTemp = 0;
 168                     while ( -1 != ( nIndex = sQuoted.indexOf( '\'',nTemp ) ) )
 169                     {
 170                         sQuoted = sQuoted.replaceAt( nIndex, 1, sDoubleQuote );
 171                         nTemp = nIndex+2;
 172                     }
 173
 174                     ::rtl::OUString sTemp( sSingleQuote );
 175                     ( sTemp += sQuoted ) += sSingleQuote;
 176                     sQuoted = sTemp;
 177                 }
 178                 pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sQuoted, m_xFormatter, _rxField );
 179             }
 180
 181             // one more fallback: for numeric fields, and value strings containing a decimal/thousands separator
 182             // problem which is to be solved with this:
 183             // * a system locale "german"
 184             // * a column formatted with an english number format
 185             // => the output is german (as we use the system locale for this), i.e. "3,4"
 186             // => the input does not recognize the german text, as predicateTree uses the number format
 187             //    of the column to determine the main locale - the locale on the context is only a fallback
 188             if  (       ( DataType::FLOAT == nType )
 189                 ||      ( DataType::REAL == nType )
 190                 ||      ( DataType::DOUBLE == nType )
 191                 ||      ( DataType::NUMERIC == nType )
 192                 ||      ( DataType::DECIMAL == nType )
 193                 )
 194             {
 195                 const IParseContext& rParseContext = m_aParser.getContext();
 196                 // get the separators for the locale of our parse context
 197                 sal_Unicode nCtxDecSep;
 198                 sal_Unicode nCtxThdSep;
 199                 getSeparatorChars( rParseContext.getPreferredLocale(), nCtxDecSep, nCtxThdSep );
 200
 201                 // determine the locale of the column we're building a predicate string for
 202                 sal_Unicode nFmtDecSep( nCtxDecSep );
 203                 sal_Unicode nFmtThdSep( nCtxThdSep );
 204                 try
 205                 {
 206                     Reference< XPropertySetInfo > xPSI( _rxField->getPropertySetInfo() );
 207                     if ( xPSI.is() && xPSI->hasPropertyByName( ::rtl::OUString::createFromAscii( "FormatKey" ) ) )
 208                     {
 209                         sal_Int32 nFormatKey = 0;
 210                         _rxField->getPropertyValue( ::rtl::OUString::createFromAscii( "FormatKey" ) ) >>= nFormatKey;
 211                         if ( nFormatKey && m_xFormatter.is() )
 212                         {
 213                             Locale aFormatLocale;
 214                             ::comphelper::getNumberFormatProperty(
 215                                 m_xFormatter,
 216                                 nFormatKey,
 217                                 ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "Locale" ) )
 218                             ) >>= aFormatLocale;
 219
 220                             // valid locale
 221                             if ( aFormatLocale.Language.getLength() )
 222                             {
 223                                 getSeparatorChars( aFormatLocale, nFmtDecSep, nCtxThdSep );
 224                             }
 225                         }
 226                     }
 227                 }
 228                 catch( const Exception& )
 229                 {
 230                     OSL_ENSURE( sal_False, "OPredicateInputController::implPredicateTree: caught an exception while dealing with the formats!" );
 231                 }
 232
 233                 sal_Bool bDecDiffers = ( nCtxDecSep != nFmtDecSep );
 234                 sal_Bool bFmtDiffers = ( nCtxThdSep != nFmtThdSep );
 235                 if ( bDecDiffers || bFmtDiffers )
 236                 {       // okay, at least one differs
 237                     // "translate" the value into the "format locale"
 238                     ::rtl::OUString sTranslated( _rStatement );
 239                     const sal_Unicode nIntermediate( '_' );
 240                     sTranslated = sTranslated.replace( nCtxDecSep,      nIntermediate );
 241                     sTranslated = sTranslated.replace( nCtxThdSep,      nFmtThdSep );
 242                     sTranslated = sTranslated.replace( nIntermediate, nFmtDecSep );
 243
 244                     pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sTranslated, m_xFormatter, _rxField );
 245                 }
 246             }
 247         }
 248         return pReturn;
 249     }
 250
 251     //---------------------------------------------------------------------
 252     sal_Bool OPredicateInputController::normalizePredicateString(
 253         ::rtl::OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField, ::rtl::OUString* _pErrorMessage ) const
 254     {
 255         OSL_ENSURE( m_xConnection.is() && m_xFormatter.is() && _rxField.is(),
 256             "OPredicateInputController::normalizePredicateString: invalid state or params!" );
 257
 258         sal_Bool bSuccess = sal_False;
 259         if ( m_xConnection.is() && m_xFormatter.is() && _rxField.is() )
 260         {
 261             // parse the string
 262             ::rtl::OUString sError;
 263             ::rtl::OUString sTransformedText( _rPredicateValue );
 264             OSQLParseNode* pParseNode = implPredicateTree( sError, sTransformedText, _rxField );
 265             if ( _pErrorMessage ) *_pErrorMessage = sError;
 266
 267             if ( pParseNode )
 268             {
 269                 const IParseContext& rParseContext = m_aParser.getContext();
 270                 sal_Unicode nDecSeparator, nThousandSeparator;
 271                 getSeparatorChars( rParseContext.getPreferredLocale(), nDecSeparator, nThousandSeparator );
 272
 273                 // translate it back into a string
 274                 sTransformedText = ::rtl::OUString();
 275                 pParseNode->parseNodeToPredicateStr(
 276                     sTransformedText, m_xConnection, m_xFormatter, _rxField,
 277                     rParseContext.getPreferredLocale(), (sal_Char)nDecSeparator, &rParseContext
 278                 );
 279                 _rPredicateValue = sTransformedText;
 280                 delete pParseNode;
 281
 282                 bSuccess = sal_True;
 283             }
 284         }
 285
 286         return bSuccess;
 287     }
 288
 289     //---------------------------------------------------------------------
 290     ::rtl::OUString OPredicateInputController::getPredicateValue(
 291         const ::rtl::OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField,
 292         sal_Bool _bForStatementUse, ::rtl::OUString* _pErrorMessage ) const
 293     {
 294         OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
 295         ::rtl::OUString sReturn;
 296         if ( _rxField.is() )
 297         {
 298             ::rtl::OUString sValue( _rPredicateValue );
 299
 300             // a little problem : if the field is a text field, the normalizePredicateString added two
 301             // '-characters to the text. If we would give this to predicateTree this would add
 302             // two  additional '-characters which we don't want. So check the field format.
 303             // FS - 06.01.00 - 71532
 304             sal_Bool bValidQuotedText = ( sValue.getLength() >= 2 )
 305                                     &&  ( sValue.getStr()[0] == '\'' )
 306                                     &&  ( sValue.getStr()[ sValue.getLength() - 1 ] == '\'' );
 307                 // again : as normalizePredicateString always did a conversion on the value text,
 308                 // bValidQuotedText == sal_True implies that we have a text field, as no other field
 309                 // values will be formatted with the quote characters
 310             if ( bValidQuotedText )
 311             {
 312                 sValue = sValue.copy( 1, sValue.getLength() - 2 );
 313                 static const ::rtl::OUString sSingleQuote( RTL_CONSTASCII_USTRINGPARAM( "'" ) );
 314                 static const ::rtl::OUString sDoubleQuote( RTL_CONSTASCII_USTRINGPARAM( "''" ) );
 315
 316                 sal_Int32 nIndex = -1;
 317                 sal_Int32 nTemp = 0;
 318                 while ( -1 != ( nIndex = sValue.indexOf( sDoubleQuote,nTemp ) ) )
 319                 {
 320                     sValue = sValue.replaceAt( nIndex, 2, sSingleQuote );
 321                     nTemp = nIndex+2;
 322                 }
 323             }
 324
 325             // The following is mostly stolen from the former implementation in the parameter dialog
 326             // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
 327
 328             ::rtl::OUString sError;
 329             OSQLParseNode* pParseNode = implPredicateTree( sError, sValue, _rxField );
 330             if ( _pErrorMessage ) *_pErrorMessage = sError;
 331
 332             if ( pParseNode )
 333             {
 334                 OSQLParseNode* pOdbcSpec = pParseNode->getByRule( OSQLParseNode::odbc_fct_spec );
 335                 if ( pOdbcSpec )
 336                 {
 337                     if ( !_bForStatementUse )
 338                     {
 339                         if      (       ( pOdbcSpec->count() >= 2 )
 340                             &&  ( SQL_NODE_STRING == pOdbcSpec->getChild(1)->getNodeType() )
 341                             )
 342                         {
 343
 344                             sReturn = pOdbcSpec->getChild(1)->getTokenValue();
 345                         }
 346                         else
 347                             OSL_ENSURE( sal_False, "OPredicateInputController::getPredicateValue: unknown/invalid structure (odbc + param use)!" );
 348                     }
 349                     else
 350                     {
 351                         OSQLParseNode* pFuncSpecParent = pOdbcSpec->getParent();
 352                         OSL_ENSURE( pFuncSpecParent, "OPredicateInputController::getPredicateValue: an ODBC func spec node without parent?" );
 353                         if ( pFuncSpecParent )
 354                             pFuncSpecParent->parseNodeToStr(
 355                                 sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True
 356                             );
 357                     }
 358                 }
 359                 else
 360                 {
 361                     if  ( pParseNode->count() >= 3 )
 362                     {
 363                         OSQLParseNode* pValueNode = pParseNode->getChild(2);
 364                         OSL_ENSURE( pValueNode, "OPredicateInputController::getPredicateValue: invalid node child!" );
 365                         if ( !_bForStatementUse )
 366                         {
 367                             if ( SQL_NODE_STRING == pValueNode->getNodeType() )
 368                                 sReturn = pValueNode->getTokenValue();
 369                             else
 370                                 pValueNode->parseNodeToStr(
 371                                     sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True
 372                                 );
 373                         }
 374                         else
 375                             pValueNode->parseNodeToStr(
 376                                 sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True
 377                             );
 378                     }
 379                     else
 380                         OSL_ENSURE( sal_False, "OPredicateInputController::getPredicateValue: unknown/invalid structure (noodbc)!" );
 381                 }
 382
 383                 delete pParseNode;
 384             }
 385         }
 386
 387         return sReturn;
 388     }
 389 //.........................................................................
 390 }       // namespace dbtools
 391 //.........................................................................
 392
 393