connectivity/source/commontools/predicateinput.cxx

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