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