connectivity/source/commontools/predicateinput.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*************************************************************************
   3  *
   4  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   5  *
   6  * Copyright 2000, 2010 Oracle and/or its affiliates.
   7  *
   8  * OpenOffice.org - a multi-platform office productivity suite
   9  *
  10  * This file is part of OpenOffice.org.
  11  *
  12  * OpenOffice.org is free software: you can redistribute it and/or modify
  13  * it under the terms of the GNU Lesser General Public License version 3
  14  * only, as published by the Free Software Foundation.
  15  *
  16  * OpenOffice.org is distributed in the hope that it will be useful,
  17  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  18  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  19  * GNU Lesser General Public License version 3 for more details
  20  * (a copy is included in the LICENSE file that accompanied this code).
  21  *
  22  * You should have received a copy of the GNU Lesser General Public License
  23  * version 3 along with OpenOffice.org.  If not, see
  24  * <http://www.openoffice.org/license.html>
  25  * for a copy of the LGPLv3 License.
  26  *
  27  ************************************************************************/
  28
  29
  30 #include <connectivity/predicateinput.hxx>
  31 #include <comphelper/types.hxx>
  32 #include <connectivity/dbtools.hxx>
  33 #include <com/sun/star/sdbc/DataType.hpp>
  34 #include <com/sun/star/sdbc/ColumnValue.hpp>
  35 #include <osl/diagnose.h>
  36 #include <connectivity/sqlnode.hxx>
  37 #include <connectivity/PColumn.hxx>
  38 #include <comphelper/numbers.hxx>
  39
  40 #include <boost/shared_ptr.hpp>
  41
  42 //.........................................................................
  43 namespace dbtools
  44 {
  45 //.........................................................................
  46
  47     using ::com::sun::star::sdbc::XConnection;
  48     using ::com::sun::star::lang::XMultiServiceFactory;
  49     using ::com::sun::star::util::XNumberFormatsSupplier;
  50     using ::com::sun::star::util::XNumberFormatter;
  51     using ::com::sun::star::uno::UNO_QUERY;
  52     using ::com::sun::star::beans::XPropertySet;
  53     using ::com::sun::star::beans::XPropertySetInfo;
  54     using ::com::sun::star::lang::Locale;
  55     using ::com::sun::star::uno::Exception;
  56     using ::com::sun::star::i18n::XLocaleData;
  57     using ::com::sun::star::i18n::LocaleDataItem;
  58
  59     using namespace ::com::sun::star::sdbc;
  60     using namespace ::connectivity;
  61
  62     using ::connectivity::OSQLParseNode;
  63
  64     #define Reference ::com::sun::star::uno::Reference
  65
  66     //=====================================================================
  67     //---------------------------------------------------------------------
  68     static sal_Unicode lcl_getSeparatorChar( const ::rtl::OUString& _rSeparator, sal_Unicode _nFallback )
  69     {
  70         OSL_ENSURE( !_rSeparator.isEmpty(), "::lcl_getSeparatorChar: invalid separator string!" );
  71
  72         sal_Unicode nReturn( _nFallback );
  73         if ( !_rSeparator.isEmpty() )
  74             nReturn = static_cast< sal_Char >( _rSeparator.getStr()[0] );
  75         return nReturn;
  76     }
  77
  78     //=====================================================================
  79     //= OPredicateInputController
  80     //=====================================================================
  81     //---------------------------------------------------------------------
  82     sal_Bool OPredicateInputController::getSeparatorChars( const Locale& _rLocale, sal_Unicode& _rDecSep, sal_Unicode& _rThdSep ) const
  83     {
  84         _rDecSep = '.';
  85         _rThdSep = ',';
  86         try
  87         {
  88             LocaleDataItem aLocaleData;
  89             if ( m_xLocaleData.is() )
  90             {
  91                 aLocaleData = m_xLocaleData->getLocaleItem( _rLocale );
  92                 _rDecSep = lcl_getSeparatorChar( aLocaleData.decimalSeparator, _rDecSep );
  93                 _rThdSep = lcl_getSeparatorChar( aLocaleData.decimalSeparator, _rThdSep );
  94                 return sal_True;
  95             }
  96         }
  97         catch( const Exception& )
  98         {
  99             OSL_FAIL( "OPredicateInputController::getSeparatorChars: caught an exception!" );
 100         }
 101         return sal_False;
 102     }
 103
 104     //---------------------------------------------------------------------
 105     OPredicateInputController::OPredicateInputController(
 106         const Reference< XMultiServiceFactory >& _rxORB, const Reference< XConnection >& _rxConnection, const IParseContext* _pParseContext )
 107         :m_xORB( _rxORB )
 108         ,m_xConnection( _rxConnection )
 109         ,m_aParser( m_xORB, _pParseContext )
 110     {
 111         try
 112         {
 113             // create a number formatter / number formats supplier pair
 114             OSL_ENSURE( m_xORB.is(), "OPredicateInputController::OPredicateInputController: need a service factory!" );
 115             if ( m_xORB.is() )
 116             {
 117                 m_xFormatter = Reference< XNumberFormatter >( m_xORB->createInstance(
 118                     ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "com.sun.star.util.NumberFormatter" ) ) ),
 119                     UNO_QUERY
 120                 );
 121             }
 122
 123             Reference< XNumberFormatsSupplier >  xNumberFormats = ::dbtools::getNumberFormats( m_xConnection, sal_True );
 124             if ( !xNumberFormats.is() )
 125                 ::comphelper::disposeComponent( m_xFormatter );
 126             else if ( m_xFormatter.is() )
 127                 m_xFormatter->attachNumberFormatsSupplier( xNumberFormats );
 128
 129             // create the locale data
 130             if ( m_xORB.is() )
 131             {
 132                 m_xLocaleData = m_xLocaleData.query( m_xORB->createInstance(
 133                     ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "com.sun.star.i18n.LocaleData" ) ) )
 134                 );
 135             }
 136         }
 137         catch( const Exception& )
 138         {
 139             OSL_FAIL( "OPredicateInputController::OPredicateInputController: caught an exception!" );
 140         }
 141     }
 142
 143     //---------------------------------------------------------------------
 144     OSQLParseNode* OPredicateInputController::implPredicateTree(::rtl::OUString& _rErrorMessage, const ::rtl::OUString& _rStatement, const Reference< XPropertySet > & _rxField) const
 145     {
 146         OSQLParseNode* pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, _rStatement, m_xFormatter, _rxField );
 147         if ( !pReturn )
 148         {   // is it a text field ?
 149             sal_Int32 nType = DataType::OTHER;
 150             _rxField->getPropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( "Type" )) ) >>= nType;
 151
 152             if  (   ( DataType::CHAR        == nType )
 153                 ||  ( DataType::VARCHAR     == nType )
 154                 ||  ( DataType::LONGVARCHAR == nType )
 155                 ||  ( DataType::CLOB        == nType )
 156                 )
 157             {   // yes -> force a quoted text and try again
 158                 ::rtl::OUString sQuoted( _rStatement );
 159                 if  (   !sQuoted.isEmpty()
 160                     &&  (   (sQuoted.getStr()[0] != '\'')
 161                         ||  (sQuoted.getStr()[ sQuoted.getLength() - 1 ] != '\'' )
 162                         )
 163                     )
 164                 {
 165                     static const ::rtl::OUString sSingleQuote( RTL_CONSTASCII_USTRINGPARAM( "'" ) );
 166                     static const ::rtl::OUString sDoubleQuote( RTL_CONSTASCII_USTRINGPARAM( "''" ) );
 167
 168                     sal_Int32 nIndex = -1;
 169                     sal_Int32 nTemp = 0;
 170                     while ( -1 != ( nIndex = sQuoted.indexOf( '\'',nTemp ) ) )
 171                     {
 172                         sQuoted = sQuoted.replaceAt( nIndex, 1, sDoubleQuote );
 173                         nTemp = nIndex+2;
 174                     }
 175
 176                     ::rtl::OUString sTemp( sSingleQuote );
 177                     ( sTemp += sQuoted ) += sSingleQuote;
 178                     sQuoted = sTemp;
 179                 }
 180                 pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sQuoted, m_xFormatter, _rxField );
 181             }
 182
 183             // one more fallback: for numeric fields, and value strings containing a decimal/thousands separator
 184             // problem which is to be solved with this:
 185             // * a system locale "german"
 186             // * a column formatted with an english number format
 187             // => the output is german (as we use the system locale for this), i.e. "3,4"
 188             // => the input does not recognize the german text, as predicateTree uses the number format
 189             //    of the column to determine the main locale - the locale on the context is only a fallback
 190             if  (   ( DataType::FLOAT == nType )
 191                 ||  ( DataType::REAL == nType )
 192                 ||  ( DataType::DOUBLE == nType )
 193                 ||  ( DataType::NUMERIC == nType )
 194                 ||  ( DataType::DECIMAL == nType )
 195                 )
 196             {
 197                 const IParseContext& rParseContext = m_aParser.getContext();
 198                 // get the separators for the locale of our parse context
 199                 sal_Unicode nCtxDecSep;
 200                 sal_Unicode nCtxThdSep;
 201                 getSeparatorChars( rParseContext.getPreferredLocale(), nCtxDecSep, nCtxThdSep );
 202
 203                 // determine the locale of the column we're building a predicate string for
 204                 sal_Unicode nFmtDecSep( nCtxDecSep );
 205                 sal_Unicode nFmtThdSep( nCtxThdSep );
 206                 try
 207                 {
 208                     Reference< XPropertySetInfo > xPSI( _rxField->getPropertySetInfo() );
 209                     if ( xPSI.is() && xPSI->hasPropertyByName( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( "FormatKey" )) ) )
 210                     {
 211                         sal_Int32 nFormatKey = 0;
 212                         _rxField->getPropertyValue( ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM( "FormatKey" )) ) >>= nFormatKey;
 213                         if ( nFormatKey && m_xFormatter.is() )
 214                         {
 215                             Locale aFormatLocale;
 216                             ::comphelper::getNumberFormatProperty(
 217                                 m_xFormatter,
 218                                 nFormatKey,
 219                                 ::rtl::OUString( RTL_CONSTASCII_USTRINGPARAM( "Locale" ) )
 220                             ) >>= aFormatLocale;
 221
 222                             // valid locale
 223                             if ( !aFormatLocale.Language.isEmpty() )
 224                             {
 225                                 getSeparatorChars( aFormatLocale, nFmtDecSep, nCtxThdSep );
 226                             }
 227                         }
 228                     }
 229                 }
 230                 catch( const Exception& )
 231                 {
 232                     OSL_FAIL( "OPredicateInputController::implPredicateTree: caught an exception while dealing with the formats!" );
 233                 }
 234
 235                 sal_Bool bDecDiffers = ( nCtxDecSep != nFmtDecSep );
 236                 sal_Bool bFmtDiffers = ( nCtxThdSep != nFmtThdSep );
 237                 if ( bDecDiffers || bFmtDiffers )
 238                 {   // okay, at least one differs
 239                     // "translate" the value into the "format locale"
 240                     ::rtl::OUString sTranslated( _rStatement );
 241                     const sal_Unicode nIntermediate( '_' );
 242                     sTranslated = sTranslated.replace( nCtxDecSep,  nIntermediate );
 243                     sTranslated = sTranslated.replace( nCtxThdSep,  nFmtThdSep );
 244                     sTranslated = sTranslated.replace( nIntermediate, nFmtDecSep );
 245
 246                     pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sTranslated, m_xFormatter, _rxField );
 247                 }
 248             }
 249         }
 250         return pReturn;
 251     }
 252
 253     //---------------------------------------------------------------------
 254     sal_Bool OPredicateInputController::normalizePredicateString(
 255         ::rtl::OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField, ::rtl::OUString* _pErrorMessage ) const
 256     {
 257         OSL_ENSURE( m_xConnection.is() && m_xFormatter.is() && _rxField.is(),
 258             "OPredicateInputController::normalizePredicateString: invalid state or params!" );
 259
 260         sal_Bool bSuccess = sal_False;
 261         if ( m_xConnection.is() && m_xFormatter.is() && _rxField.is() )
 262         {
 263             // parse the string
 264             ::rtl::OUString sError;
 265             ::rtl::OUString sTransformedText( _rPredicateValue );
 266             OSQLParseNode* pParseNode = implPredicateTree( sError, sTransformedText, _rxField );
 267             if ( _pErrorMessage ) *_pErrorMessage = sError;
 268
 269             if ( pParseNode )
 270             {
 271                 const IParseContext& rParseContext = m_aParser.getContext();
 272                 sal_Unicode nDecSeparator, nThousandSeparator;
 273                 getSeparatorChars( rParseContext.getPreferredLocale(), nDecSeparator, nThousandSeparator );
 274
 275                 // translate it back into a string
 276                 sTransformedText = ::rtl::OUString();
 277                 pParseNode->parseNodeToPredicateStr(
 278                     sTransformedText, m_xConnection, m_xFormatter, _rxField,
 279                     rParseContext.getPreferredLocale(), (sal_Char)nDecSeparator, &rParseContext
 280                 );
 281                 _rPredicateValue = sTransformedText;
 282                 delete pParseNode;
 283
 284                 bSuccess = sal_True;
 285             }
 286         }
 287
 288         return bSuccess;
 289     }
 290
 291     //---------------------------------------------------------------------
 292     ::rtl::OUString OPredicateInputController::getPredicateValue(
 293         const ::rtl::OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField,
 294         sal_Bool _bForStatementUse, ::rtl::OUString* _pErrorMessage ) const
 295     {
 296         OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
 297         ::rtl::OUString sReturn;
 298         if ( _rxField.is() )
 299         {
 300             ::rtl::OUString sValue( _rPredicateValue );
 301
 302             // a little problem : if the field is a text field, the normalizePredicateString added two
 303             // '-characters to the text. If we would give this to predicateTree this would add
 304             // two  additional '-characters which we don't want. So check the field format.
 305             // FS - 06.01.00 - 71532
 306             sal_Bool bValidQuotedText = ( sValue.getLength() >= 2 )
 307                                     &&  ( sValue.getStr()[0] == '\'' )
 308                                     &&  ( sValue.getStr()[ sValue.getLength() - 1 ] == '\'' );
 309                 // again : as normalizePredicateString always did a conversion on the value text,
 310                 // bValidQuotedText == sal_True implies that we have a text field, as no other field
 311                 // values will be formatted with the quote characters
 312             if ( bValidQuotedText )
 313             {
 314                 sValue = sValue.copy( 1, sValue.getLength() - 2 );
 315                 static const ::rtl::OUString sSingleQuote( RTL_CONSTASCII_USTRINGPARAM( "'" ) );
 316                 static const ::rtl::OUString sDoubleQuote( RTL_CONSTASCII_USTRINGPARAM( "''" ) );
 317
 318                 sal_Int32 nIndex = -1;
 319                 sal_Int32 nTemp = 0;
 320                 while ( -1 != ( nIndex = sValue.indexOf( sDoubleQuote,nTemp ) ) )
 321                 {
 322                     sValue = sValue.replaceAt( nIndex, 2, sSingleQuote );
 323                     nTemp = nIndex+2;
 324                 }
 325             }
 326
 327             // The following is mostly stolen from the former implementation in the parameter dialog
 328             // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
 329
 330             ::rtl::OUString sError;
 331             OSQLParseNode* pParseNode = implPredicateTree( sError, sValue, _rxField );
 332             if ( _pErrorMessage )
 333                 *_pErrorMessage = sError;
 334
 335             sReturn = implParseNode(pParseNode,_bForStatementUse);
 336         }
 337
 338         return sReturn;
 339     }
 340
 341     ::rtl::OUString OPredicateInputController::getPredicateValue(
 342         const ::rtl::OUString& _sField, const ::rtl::OUString& _rPredicateValue, sal_Bool _bForStatementUse, ::rtl::OUString* _pErrorMessage ) const
 343     {
 344         ::rtl::OUString sReturn = _rPredicateValue;
 345         ::rtl::OUString sError;
 346         ::rtl::OUString sField = _sField;
 347         sal_Int32 nIndex = 0;
 348         sField = sField.getToken(0,'(',nIndex);
 349         if(nIndex == -1)
 350             sField = _sField;
 351         sal_Int32 nType = ::connectivity::OSQLParser::getFunctionReturnType(sField,&m_aParser.getContext());
 352         if ( nType == DataType::OTHER || sField.isEmpty() )
 353         {
 354             // first try the international version
 355             ::rtl::OUString sSql;
 356             sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM("SELECT * "));
 357             sSql += ::rtl::OUString(RTL_CONSTASCII_USTRINGPARAM(" FROM x WHERE "));
 358             sSql += sField;
 359             sSql += _rPredicateValue;
 360             ::std::auto_ptr<OSQLParseNode> pParseNode( const_cast< OSQLParser& >( m_aParser ).parseTree( sError, sSql, sal_True ) );
 361             nType = DataType::DOUBLE;
 362             if ( pParseNode.get() )
 363             {
 364                 OSQLParseNode* pColumnRef = pParseNode->getByRule(OSQLParseNode::column_ref);
 365                 if ( pColumnRef )
 366                 {
 367                 }
 368             }
 369         }
 370
 371         Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
 372         parse::OParseColumn* pColumn = new parse::OParseColumn( sField,
 373                                                                 ::rtl::OUString(),
 374                                                                 ::rtl::OUString(),
 375                                                                 ::rtl::OUString(),
 376                                                                 ColumnValue::NULLABLE_UNKNOWN,
 377                                                                 0,
 378                                                                 0,
 379                                                                 nType,
 380                                                                 sal_False,
 381                                                                 sal_False,
 382                                                                 xMeta.is() && xMeta->supportsMixedCaseQuotedIdentifiers());
 383         Reference<XPropertySet> xColumn = pColumn;
 384         pColumn->setFunction(sal_True);
 385         pColumn->setRealName(sField);
 386
 387         OSQLParseNode* pParseNode = implPredicateTree( sError, _rPredicateValue, xColumn );
 388         if ( _pErrorMessage )
 389             *_pErrorMessage = sError;
 390         return pParseNode ? implParseNode(pParseNode,_bForStatementUse) : sReturn;
 391     }
 392
 393     ::rtl::OUString OPredicateInputController::implParseNode(OSQLParseNode* pParseNode,sal_Bool _bForStatementUse) const
 394     {
 395         ::rtl::OUString sReturn;
 396         if ( pParseNode )
 397         {
 398             boost::shared_ptr<OSQLParseNode> xTakeOwnership(pParseNode);
 399             OSQLParseNode* pOdbcSpec = pParseNode->getByRule( OSQLParseNode::odbc_fct_spec );
 400             if ( pOdbcSpec )
 401             {
 402                 if ( _bForStatementUse )
 403                 {
 404                     OSQLParseNode* pFuncSpecParent = pOdbcSpec->getParent();
 405                     OSL_ENSURE( pFuncSpecParent, "OPredicateInputController::getPredicateValue: an ODBC func spec node without parent?" );
 406                     if ( pFuncSpecParent )
 407                         pFuncSpecParent->parseNodeToStr(sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True);
 408                 }
 409                 else
 410                 {
 411                     OSQLParseNode* pValueNode = pOdbcSpec->getChild(1);
 412                     if ( SQL_NODE_STRING == pValueNode->getNodeType() )
 413                         sReturn = pValueNode->getTokenValue();
 414                     else
 415                         pValueNode->parseNodeToStr(sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True);
 416                 }
 417             }
 418             else
 419             {
 420                 if  ( pParseNode->count() >= 3 )
 421                 {
 422                     OSQLParseNode* pValueNode = pParseNode->getChild(2);
 423                     OSL_ENSURE( pValueNode, "OPredicateInputController::getPredicateValue: invalid node child!" );
 424                     if ( !_bForStatementUse )
 425                     {
 426                         if ( SQL_NODE_STRING == pValueNode->getNodeType() )
 427                             sReturn = pValueNode->getTokenValue();
 428                         else
 429                             pValueNode->parseNodeToStr(
 430                                 sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True
 431                             );
 432                     }
 433                     else
 434                         pValueNode->parseNodeToStr(
 435                             sReturn, m_xConnection, &m_aParser.getContext(), sal_False, sal_True
 436                         );
 437                 }
 438                 else
 439                     OSL_FAIL( "OPredicateInputController::getPredicateValue: unknown/invalid structure (noodbc)!" );
 440             }
 441         }
 442         return sReturn;
 443     }
 444 //.........................................................................
 445 }   // namespace dbtools
 446 //.........................................................................
 447
 448
 449 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */