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