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