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/types.hxx>
  23 #include <connectivity/dbtools.hxx>
  24 #include <com/sun/star/i18n/LocaleData.hpp>
  25 #include <com/sun/star/sdbc/DataType.hpp>
  26 #include <com/sun/star/sdbc/ColumnValue.hpp>
  27 #include <com/sun/star/sdbc/XConnection.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 <memory>
  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::uno::UNO_QUERY_THROW;
  45     using ::com::sun::star::uno::XComponentContext;
  46     using ::com::sun::star::beans::XPropertySet;
  47     using ::com::sun::star::beans::XPropertySetInfo;
  48     using ::com::sun::star::lang::Locale;
  49     using ::com::sun::star::uno::Exception;
  50     using ::com::sun::star::uno::Reference;
  51     using ::com::sun::star::i18n::LocaleData;
  52     using ::com::sun::star::i18n::LocaleDataItem;
  53     using ::com::sun::star::uno::Any;
  54
  55     using namespace ::com::sun::star::sdbc;
  56     using namespace ::connectivity;
  57
  58     using ::connectivity::OSQLParseNode;
  59
  60
  61     static sal_Unicode lcl_getSeparatorChar( const OUString& _rSeparator, sal_Unicode _nFallback )
  62     {
  63         OSL_ENSURE( !_rSeparator.isEmpty(), "::lcl_getSeparatorChar: invalid separator string!" );
  64
  65         sal_Unicode nReturn( _nFallback );
  66         if ( !_rSeparator.isEmpty() )
  67             nReturn = _rSeparator[0];
  68         return nReturn;
  69     }
  70
  71     bool OPredicateInputController::getSeparatorChars( const Locale& _rLocale, sal_Unicode& _rDecSep, sal_Unicode& _rThdSep ) const
  72     {
  73         _rDecSep = '.';
  74         _rThdSep = ',';
  75         try
  76         {
  77             LocaleDataItem aLocaleData;
  78             if ( m_xLocaleData.is() )
  79             {
  80                 aLocaleData = m_xLocaleData->getLocaleItem( _rLocale );
  81                 _rDecSep = lcl_getSeparatorChar( aLocaleData.decimalSeparator, _rDecSep );
  82                 _rThdSep = lcl_getSeparatorChar( aLocaleData.thousandSeparator, _rThdSep );
  83                 return true;
  84             }
  85         }
  86         catch( const Exception& )
  87         {
  88             OSL_FAIL( "OPredicateInputController::getSeparatorChars: caught an exception!" );
  89         }
  90         return false;
  91     }
  92
  93
  94     OPredicateInputController::OPredicateInputController(
  95         const Reference< XComponentContext >& rxContext, const Reference< XConnection >& _rxConnection, const IParseContext* _pParseContext )
  96         : m_xConnection( _rxConnection )
  97         ,m_aParser( rxContext, _pParseContext )
  98     {
  99         try
 100         {
 101             // create a number formatter / number formats supplier pair
 102             OSL_ENSURE( rxContext.is(), "OPredicateInputController::OPredicateInputController: need a service factory!" );
 103             if ( rxContext.is() )
 104             {
 105                 m_xFormatter.set( NumberFormatter::create(rxContext), UNO_QUERY_THROW );
 106             }
 107
 108             Reference< XNumberFormatsSupplier >  xNumberFormats = ::dbtools::getNumberFormats( m_xConnection, true );
 109             if ( !xNumberFormats.is() )
 110                 ::comphelper::disposeComponent( m_xFormatter );
 111             else
 112                 m_xFormatter->attachNumberFormatsSupplier( xNumberFormats );
 113
 114             // create the locale data
 115             if ( rxContext.is() )
 116             {
 117                 m_xLocaleData = LocaleData::create( rxContext );
 118             }
 119         }
 120         catch( const Exception& )
 121         {
 122             OSL_FAIL( "OPredicateInputController::OPredicateInputController: caught an exception!" );
 123         }
 124     }
 125
 126
 127     std::unique_ptr<OSQLParseNode> OPredicateInputController::implPredicateTree(OUString& _rErrorMessage, const OUString& _rStatement, const Reference< XPropertySet > & _rxField) const
 128     {
 129         std::unique_ptr<OSQLParseNode> pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, _rStatement, m_xFormatter, _rxField );
 130         if ( !pReturn )
 131         {   // is it a text field ?
 132             sal_Int32 nType = DataType::OTHER;
 133             _rxField->getPropertyValue("Type") >>= nType;
 134
 135             if  (   ( DataType::CHAR        == nType )
 136                 ||  ( DataType::VARCHAR     == nType )
 137                 ||  ( DataType::LONGVARCHAR == nType )
 138                 ||  ( DataType::CLOB        == nType )
 139                 )
 140             {   // yes -> force a quoted text and try again
 141                 OUString sQuoted( _rStatement );
 142                 if  (   !sQuoted.isEmpty()
 143                     &&  (   !sQuoted.startsWith("'")
 144                         ||  !sQuoted.endsWith("'")
 145                         )
 146                     )
 147                 {
 148                     static const char sSingleQuote[] = "'";
 149
 150                     sal_Int32 nIndex = -1;
 151                     sal_Int32 nTemp = 0;
 152                     while ( -1 != ( nIndex = sQuoted.indexOf( '\'',nTemp ) ) )
 153                     {
 154                         sQuoted = sQuoted.replaceAt( nIndex, 1, "''" );
 155                         nTemp = nIndex+2;
 156                     }
 157
 158                     sQuoted = sSingleQuote + sQuoted + sSingleQuote;
 159                 }
 160                 pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sQuoted, m_xFormatter, _rxField );
 161             }
 162
 163             // one more fallback: for numeric fields, and value strings containing a decimal/thousands separator
 164             // problem which is to be solved with this:
 165             // * a system locale "german"
 166             // * a column formatted with an english number format
 167             // => the output is german (as we use the system locale for this), i.e. "3,4"
 168             // => the input does not recognize the german text, as predicateTree uses the number format
 169             //    of the column to determine the main locale - the locale on the context is only a fallback
 170             if  (   ( DataType::FLOAT == nType )
 171                 ||  ( DataType::REAL == nType )
 172                 ||  ( DataType::DOUBLE == nType )
 173                 ||  ( DataType::NUMERIC == nType )
 174                 ||  ( DataType::DECIMAL == nType )
 175                 )
 176             {
 177                 const IParseContext& rParseContext = m_aParser.getContext();
 178                 // get the separators for the locale of our parse context
 179                 sal_Unicode nCtxDecSep;
 180                 sal_Unicode nCtxThdSep;
 181                 getSeparatorChars( rParseContext.getPreferredLocale(), nCtxDecSep, nCtxThdSep );
 182
 183                 // determine the locale of the column we're building a predicate string for
 184                 sal_Unicode nFmtDecSep( nCtxDecSep );
 185                 sal_Unicode nFmtThdSep( nCtxThdSep );
 186                 try
 187                 {
 188                     Reference< XPropertySetInfo > xPSI( _rxField->getPropertySetInfo() );
 189                     if ( xPSI.is() && xPSI->hasPropertyByName("FormatKey") )
 190                     {
 191                         sal_Int32 nFormatKey = 0;
 192                         _rxField->getPropertyValue("FormatKey") >>= nFormatKey;
 193                         if ( nFormatKey && m_xFormatter.is() )
 194                         {
 195                             Locale aFormatLocale;
 196                             ::comphelper::getNumberFormatProperty(
 197                                 m_xFormatter,
 198                                 nFormatKey,
 199                                 "Locale"
 200                             ) >>= aFormatLocale;
 201
 202                             // valid locale
 203                             if ( !aFormatLocale.Language.isEmpty() )
 204                             {
 205                                 getSeparatorChars( aFormatLocale, nFmtDecSep, nCtxThdSep );
 206                             }
 207                         }
 208                     }
 209                 }
 210                 catch( const Exception& )
 211                 {
 212                     OSL_FAIL( "OPredicateInputController::implPredicateTree: caught an exception while dealing with the formats!" );
 213                 }
 214
 215                 bool bDecDiffers = ( nCtxDecSep != nFmtDecSep );
 216                 bool bFmtDiffers = ( nCtxThdSep != nFmtThdSep );
 217                 if ( bDecDiffers || bFmtDiffers )
 218                 {   // okay, at least one differs
 219                     // "translate" the value into the "format locale"
 220                     OUString sTranslated( _rStatement );
 221                     const sal_Unicode nIntermediate( '_' );
 222                     sTranslated = sTranslated.replace( nCtxDecSep,  nIntermediate );
 223                     sTranslated = sTranslated.replace( nCtxThdSep,  nFmtThdSep );
 224                     sTranslated = sTranslated.replace( nIntermediate, nFmtDecSep );
 225
 226                     pReturn = const_cast< OSQLParser& >( m_aParser ).predicateTree( _rErrorMessage, sTranslated, m_xFormatter, _rxField );
 227                 }
 228             }
 229         }
 230         return pReturn;
 231     }
 232
 233
 234     bool OPredicateInputController::normalizePredicateString(
 235         OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField, OUString* _pErrorMessage ) const
 236     {
 237         OSL_ENSURE( m_xConnection.is() && m_xFormatter.is() && _rxField.is(),
 238             "OPredicateInputController::normalizePredicateString: invalid state or params!" );
 239
 240         bool bSuccess = false;
 241         if ( m_xConnection.is() && m_xFormatter.is() && _rxField.is() )
 242         {
 243             // parse the string
 244             OUString sError;
 245             OUString sTransformedText( _rPredicateValue );
 246             std::unique_ptr<OSQLParseNode> pParseNode = implPredicateTree( sError, sTransformedText, _rxField );
 247             if ( _pErrorMessage ) *_pErrorMessage = sError;
 248
 249             if ( pParseNode )
 250             {
 251                 const IParseContext& rParseContext = m_aParser.getContext();
 252                 sal_Unicode nDecSeparator, nThousandSeparator;
 253                 getSeparatorChars( rParseContext.getPreferredLocale(), nDecSeparator, nThousandSeparator );
 254
 255                 // translate it back into a string
 256                 sTransformedText.clear();
 257                 pParseNode->parseNodeToPredicateStr(
 258                     sTransformedText, m_xConnection, m_xFormatter, _rxField, OUString(),
 259                     rParseContext.getPreferredLocale(), static_cast<sal_Char>(nDecSeparator), &rParseContext
 260                 );
 261                 _rPredicateValue = sTransformedText;
 262
 263                 bSuccess = true;
 264             }
 265         }
 266
 267         return bSuccess;
 268     }
 269
 270
 271     OUString OPredicateInputController::getPredicateValueStr(
 272         const OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField ) const
 273     {
 274         OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
 275         OUString sReturn;
 276         if ( _rxField.is() )
 277         {
 278             // The following is mostly stolen from the former implementation in the parameter dialog
 279             // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
 280
 281             OUString sError;
 282             std::unique_ptr<OSQLParseNode> pParseNode = implPredicateTree( sError, _rPredicateValue, _rxField );
 283
 284             implParseNode(std::move(pParseNode), true) >>= sReturn;
 285         }
 286
 287         return sReturn;
 288     }
 289
 290     OUString OPredicateInputController::getPredicateValueStr(
 291         const OUString& _sField, const OUString& _rPredicateValue ) const
 292     {
 293         OUString sReturn = _rPredicateValue;
 294         OUString sError;
 295         sal_Int32 nIndex = 0;
 296         OUString sField = _sField.getToken(0, '(', nIndex);
 297         if(nIndex == -1)
 298             sField = _sField;
 299         sal_Int32 nType = ::connectivity::OSQLParser::getFunctionReturnType(sField,&m_aParser.getContext());
 300         if ( nType == DataType::OTHER || sField.isEmpty() )
 301         {
 302             // first try the international version
 303             OUString sSql = "SELECT * FROM x WHERE " + sField + _rPredicateValue;
 304             std::unique_ptr<OSQLParseNode> pParseNode( const_cast< OSQLParser& >( m_aParser ).parseTree( sError, sSql, true ) );
 305             nType = DataType::DOUBLE;
 306         }
 307
 308         Reference<XDatabaseMetaData> xMeta = m_xConnection->getMetaData();
 309         parse::OParseColumn* pColumn = new parse::OParseColumn( sField,
 310                                                                 OUString(),
 311                                                                 OUString(),
 312                                                                 OUString(),
 313                                                                 ColumnValue::NULLABLE_UNKNOWN,
 314                                                                 0,
 315                                                                 0,
 316                                                                 nType,
 317                                                                 false,
 318                                                                 false,
 319                                                                 xMeta.is() && xMeta->supportsMixedCaseQuotedIdentifiers(),
 320                                                                 OUString(),
 321                                                                 OUString(),
 322                                                                 OUString());
 323         Reference<XPropertySet> xColumn = pColumn;
 324         pColumn->setFunction(true);
 325         pColumn->setRealName(sField);
 326
 327         std::unique_ptr<OSQLParseNode> pParseNode = implPredicateTree( sError, _rPredicateValue, xColumn );
 328         if(pParseNode)
 329         {
 330             implParseNode(std::move(pParseNode), true) >>= sReturn;
 331         }
 332         return sReturn;
 333     }
 334
 335     Any OPredicateInputController::getPredicateValue(
 336         const OUString& _rPredicateValue, const Reference< XPropertySet > & _rxField ) const
 337     {
 338         OSL_ENSURE( _rxField.is(), "OPredicateInputController::getPredicateValue: invalid params!" );
 339
 340         if ( _rxField.is() )
 341         {
 342             // The following is mostly stolen from the former implementation in the parameter dialog
 343             // (dbaccess/source/ui/dlg/paramdialog.cxx). I do not fully understand this .....
 344
 345             OUString sError;
 346             std::unique_ptr<OSQLParseNode> pParseNode = implPredicateTree( sError, _rPredicateValue, _rxField );
 347
 348             return implParseNode(std::move(pParseNode), false);
 349         }
 350
 351         return Any();
 352     }
 353
 354     Any OPredicateInputController::implParseNode(std::unique_ptr<OSQLParseNode> pParseNode, bool _bForStatementUse) const
 355     {
 356         if ( ! pParseNode )
 357             return Any();
 358         else
 359         {
 360             OUString sReturn;
 361             OSQLParseNode* pOdbcSpec = pParseNode->getByRule( OSQLParseNode::odbc_fct_spec );
 362             if ( pOdbcSpec )
 363             {
 364                 if ( _bForStatementUse )
 365                 {
 366                     OSQLParseNode* pFuncSpecParent = pOdbcSpec->getParent();
 367                     OSL_ENSURE( pFuncSpecParent, "OPredicateInputController::getPredicateValue: an ODBC func spec node without parent?" );
 368                     if ( pFuncSpecParent )
 369                         pFuncSpecParent->parseNodeToStr(sReturn, m_xConnection, &m_aParser.getContext());
 370                 }
 371                 else
 372                 {
 373                     OSQLParseNode* pValueNode = pOdbcSpec->getChild(1);
 374                     if ( SQLNodeType::String == pValueNode->getNodeType() )
 375                         sReturn = pValueNode->getTokenValue();
 376                     else
 377                         pValueNode->parseNodeToStr(sReturn, m_xConnection, &m_aParser.getContext());
 378                 }
 379             }
 380             else
 381             {
 382                 if (pParseNode->getKnownRuleID() == OSQLParseNode::test_for_null )
 383                 {
 384                     assert(pParseNode->count() == 2);
 385                     return Any();
 386                 }
 387                 // LEM this seems overly permissive as test...
 388                 else if (pParseNode->count() >= 3)
 389                 {
 390                     OSQLParseNode* pValueNode = pParseNode->getChild(2);
 391                     assert(pValueNode && "OPredicateInputController::getPredicateValue: invalid node child!");
 392                     if ( !_bForStatementUse )
 393                     {
 394                         if ( SQLNodeType::String == pValueNode->getNodeType() )
 395                             sReturn = pValueNode->getTokenValue();
 396                         else
 397                             pValueNode->parseNodeToStr(
 398                                 sReturn, m_xConnection, &m_aParser.getContext()
 399                             );
 400                     }
 401                     else
 402                         pValueNode->parseNodeToStr(
 403                             sReturn, m_xConnection, &m_aParser.getContext()
 404                         );
 405                 }
 406                 else
 407                 {
 408                     OSL_FAIL( "OPredicateInputController::getPredicateValue: unknown/invalid structure (noodbc)!" );
 409                     return Any();
 410                 }
 411             }
 412             return Any(sReturn);
 413         }
 414     }
 415
 416 }   // namespace dbtools
 417
 418
 419 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */