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