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 
lcl_getSeparatorChar(std::u16string_view _rSeparator,sal_Unicode _nFallback)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 
getSeparatorChars(const Locale & _rLocale,sal_Unicode & _rDecSep,sal_Unicode & _rThdSep) const73     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 
OPredicateInputController(const Reference<XComponentContext> & rxContext,const Reference<XConnection> & _rxConnection,const IParseContext * _pParseContext)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 
implPredicateTree(OUString & _rErrorMessage,const OUString & _rStatement,const Reference<XPropertySet> & _rxField) const129     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 
normalizePredicateString(OUString & _rPredicateValue,const Reference<XPropertySet> & _rxField,OUString * _pErrorMessage) const226     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 
getPredicateValueStr(const OUString & _rPredicateValue,const Reference<XPropertySet> & _rxField) const263     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 
getPredicateValueStr(const OUString & _sField,const OUString & _rPredicateValue) const282     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 
getPredicateValue(const OUString & _rPredicateValue,const Reference<XPropertySet> & _rxField) const327     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 
implParseNode(std::unique_ptr<OSQLParseNode> pParseNode,bool _bForStatementUse) const346     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: */
412