QGIS API Documentation  3.22.4-Białowieża (ce8e65e95e)
qgssqlexpressioncompiler.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgssqlexpressioncompiler.cpp
3  ----------------------------
4  begin : November 2015
5  copyright : (C) 2015 Nyall Dawson
6  email : nyall dot dawson at gmail dot com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 
17 #include "qgsexpressionnodeimpl.h"
18 #include "qgsexpressionfunction.h"
19 #include "qgsexpression.h"
20 
21 QgsSqlExpressionCompiler::QgsSqlExpressionCompiler( const QgsFields &fields, Flags flags, bool ignoreStaticNodes )
22  : mFields( fields )
23  , mFlags( flags )
24  , mIgnoreStaticNodes( ignoreStaticNodes )
25 {
26 }
27 
29 {
30  if ( exp->rootNode() )
31  return compileNode( exp->rootNode(), mResult );
32  else
33  return Fail;
34 }
35 
37 {
38  return mResult;
39 }
40 
42 {
43  if ( op == QgsExpressionNodeBinaryOperator::BinaryOperator::boILike ||
44  op == QgsExpressionNodeBinaryOperator::BinaryOperator::boLike ||
45  op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotILike ||
46  op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotLike ||
47  op == QgsExpressionNodeBinaryOperator::BinaryOperator::boRegexp )
48  return true;
49  else
50  return false;
51 }
52 
53 QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
54 {
55  QString quoted = identifier;
56  quoted.replace( '"', QLatin1String( "\"\"" ) );
57  quoted = quoted.prepend( '\"' ).append( '\"' );
58  return quoted;
59 }
60 
61 QString QgsSqlExpressionCompiler::quotedValue( const QVariant &value, bool &ok )
62 {
63  ok = true;
64 
65  if ( value.isNull() )
66  return QStringLiteral( "NULL" );
67 
68  switch ( value.type() )
69  {
70  case QVariant::Int:
71  case QVariant::LongLong:
72  case QVariant::Double:
73  return value.toString();
74 
75  case QVariant::Bool:
76  return value.toBool() ? QStringLiteral( "TRUE" ) : QStringLiteral( "FALSE" );
77 
78  default:
79  case QVariant::String:
80  QString v = value.toString();
81  v.replace( '\'', QLatin1String( "''" ) );
82  if ( v.contains( '\\' ) )
83  return v.replace( '\\', QLatin1String( "\\\\" ) ).prepend( "E'" ).append( '\'' );
84  else
85  return v.prepend( '\'' ).append( '\'' );
86  }
87 }
88 
90 {
92  if ( staticRes != Fail )
93  return staticRes;
94 
95  switch ( node->nodeType() )
96  {
98  {
99  const QgsExpressionNodeUnaryOperator *n = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
100  switch ( n->op() )
101  {
103  {
104  QString right;
105  if ( compileNode( n->operand(), right ) == Complete )
106  {
107  result = "( NOT " + right + ')';
108  return Complete;
109  }
110 
111  return Fail;
112  }
113 
115  {
116  if ( mFlags.testFlag( NoUnaryMinus ) )
117  return Fail;
118 
119  QString right;
120  if ( compileNode( n->operand(), right ) == Complete )
121  {
122  result = "( - (" + right + "))";
123  return Complete;
124  }
125 
126  return Fail;
127  }
128  }
129 
130  break;
131  }
132 
134  {
135  const QgsExpressionNodeBinaryOperator *n = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
136 
137  QString op;
138  bool partialCompilation = false;
139  bool failOnPartialNode = false;
140  switch ( n->op() )
141  {
144  {
145  // equality between column refs results in a partial compilation, since provider is performing
146  // case-insensitive matches between strings
147  partialCompilation = true;
148  }
149 
150  op = QStringLiteral( "=" );
151  break;
152 
154  op = QStringLiteral( ">=" );
155  break;
156 
158  op = QStringLiteral( ">" );
159  break;
160 
162  op = QStringLiteral( "<=" );
163  break;
164 
166  op = QStringLiteral( "<" );
167  break;
168 
170  op = QStringLiteral( "IS" );
171  break;
172 
174  op = QStringLiteral( "IS NOT" );
175  failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
176  break;
177 
179  op = QStringLiteral( "LIKE" );
180  partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
181  break;
182 
184  if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
185  op = QStringLiteral( "LIKE" );
186  else
187  op = QStringLiteral( "ILIKE" );
188  break;
189 
191  op = QStringLiteral( "NOT LIKE" );
192  partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
193  failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
194  break;
195 
197  failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
198  if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
199  op = QStringLiteral( "NOT LIKE" );
200  else
201  op = QStringLiteral( "NOT ILIKE" );
202  break;
203 
205  if ( mFlags.testFlag( NoNullInBooleanLogic ) )
206  {
207  if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
208  return Fail;
209  }
210 
211  op = QStringLiteral( "OR" );
212  break;
213 
215  if ( mFlags.testFlag( NoNullInBooleanLogic ) )
216  {
217  if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
218  return Fail;
219  }
220 
221  op = QStringLiteral( "AND" );
222  break;
223 
225  failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
226  op = QStringLiteral( "<>" );
227  break;
228 
230  op = QStringLiteral( "*" );
231  break;
232 
234  op = QStringLiteral( "+" );
235  break;
236 
238  op = QStringLiteral( "-" );
239  break;
240 
242  op = QStringLiteral( "/" );
243  break;
244 
246  op = QStringLiteral( "%" );
247  break;
248 
250  op = QStringLiteral( "||" );
251  break;
252 
254  op = QStringLiteral( "/" );
255  break;
256 
258  op = QStringLiteral( "^" );
259  break;
260 
262  op = QStringLiteral( "~" );
263  break;
264  }
265 
266  if ( op.isNull() )
267  return Fail;
268 
269  QString left;
270  const Result lr( compileNode( n->opLeft(), left ) );
271 
272  if ( opIsStringComparison( n ->op() ) )
273  left = castToText( left );
274 
275  QString right;
276  const Result rr( compileNode( n->opRight(), right ) );
277 
278  if ( failOnPartialNode && ( lr == Partial || rr == Partial ) )
279  return Fail;
280 
282  {
283  right = castToReal( right );
284  if ( right.isEmpty() )
285  {
286  // not supported
287  return Fail;
288  }
289  }
290 
291  result = '(' + left + ' ' + op + ' ' + right + ')';
293  {
294  result = castToInt( result );
295  if ( result.isEmpty() )
296  {
297  // not supported
298  return Fail;
299  }
300  }
301 
302  if ( lr == Complete && rr == Complete )
303  return ( partialCompilation ? Partial : Complete );
304  else if ( ( lr == Partial && rr == Complete ) || ( lr == Complete && rr == Partial ) || ( lr == Partial && rr == Partial ) )
305  return Partial;
306  else
307  return Fail;
308  }
309 
311  {
312  const QgsExpressionNodeLiteral *n = static_cast<const QgsExpressionNodeLiteral *>( node );
313  bool ok = false;
314  if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().type() == QVariant::String )
315  {
316  // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
317  // double check results using QGIS' expression engine
318  result = quotedValue( n->value(), ok );
319  return ok ? Partial : Fail;
320  }
321  else
322  {
323  result = quotedValue( n->value(), ok );
324  return ok ? Complete : Fail;
325  }
326  }
327 
329  {
330  const QgsExpressionNodeColumnRef *n = static_cast<const QgsExpressionNodeColumnRef *>( node );
331 
332  // QGIS expressions don't care about case sensitive field naming, so we match case insensitively here to the
333  // layer's fields and then retrieve the actual case of the field name for use in the compilation
334  const int fieldIndex = mFields.lookupField( n->name() );
335  if ( fieldIndex == -1 )
336  // Not a provider field
337  return Fail;
338 
339  result = quotedIdentifier( mFields.at( fieldIndex ).name() );
340 
341  return Complete;
342  }
343 
345  {
346  const QgsExpressionNodeInOperator *n = static_cast<const QgsExpressionNodeInOperator *>( node );
347  QStringList list;
348 
349  Result inResult = Complete;
350  const auto constList = n->list()->list();
351  for ( const QgsExpressionNode *ln : constList )
352  {
353  QString s;
354  const Result r = compileNode( ln, s );
355  if ( r == Complete || r == Partial )
356  {
357  list << s;
358  if ( r == Partial )
359  inResult = Partial;
360  }
361  else
362  return r;
363  }
364 
365  QString nd;
366  const Result rn = compileNode( n->node(), nd );
367  if ( rn != Complete && rn != Partial )
368  return rn;
369 
370  result = QStringLiteral( "%1 %2IN (%3)" ).arg( nd, n->isNotIn() ? QStringLiteral( "NOT " ) : QString(), list.join( ',' ) );
371  return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
372  }
373 
375  {
376  const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
378 
379  // get sql function to compile node expression
380  const QString nd = sqlFunctionFromFunctionName( fd->name() );
381  // if no sql function the node can't be compiled
382  if ( nd.isNull() )
383  return Fail;
384 
385  // compile arguments
386  QStringList args;
387  Result inResult = Complete;
388  const auto constList = n->args()->list();
389  for ( const QgsExpressionNode *ln : constList )
390  {
391  QString s;
392  const Result r = compileNode( ln, s );
393  if ( r == Complete || r == Partial )
394  {
395  args << s;
396  if ( r == Partial )
397  inResult = Partial;
398  }
399  else
400  return r;
401  }
402 
403  // update arguments to be adapted to SQL function
404  args = sqlArgumentsFromFunctionName( fd->name(), args );
405 
406  // build result
407  result = !nd.isEmpty() ? QStringLiteral( "%1(%2)" ).arg( nd, args.join( ',' ) ) : args.join( ',' );
408  return inResult == Partial ? Partial : Complete;
409  }
410 
412  break;
413 
415  break;
416  }
417 
418  return Fail;
419 }
420 
421 QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
422 {
423  Q_UNUSED( fnName )
424  return QString();
425 }
426 
427 QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
428 {
429  Q_UNUSED( fnName )
430  return QStringList( fnArgs );
431 }
432 
433 QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
434 {
435  Q_UNUSED( value )
436  return QString();
437 }
438 
439 QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
440 {
441  return value;
442 }
443 
444 QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
445 {
446  Q_UNUSED( value )
447  return QString();
448 }
449 
451 {
452  if ( mIgnoreStaticNodes )
453  return Fail;
454 
455  if ( node->hasCachedStaticValue() )
456  {
457  bool ok = false;
458  if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && node->cachedStaticValue().type() == QVariant::String )
459  {
460  // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
461  // double check results using QGIS' expression engine
462  result = quotedValue( node->cachedStaticValue(), ok );
463  return ok ? Partial : Fail;
464  }
465  else
466  {
467  result = quotedValue( node->cachedStaticValue(), ok );
468  return ok ? Complete : Fail;
469  }
470  }
471  return Fail;
472 }
473 
474 bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpressionNode *node ) const
475 {
476  if ( node->nodeType() != QgsExpressionNode::ntLiteral )
477  return false;
478 
479  const QgsExpressionNodeLiteral *nLit = static_cast<const QgsExpressionNodeLiteral *>( node );
480  return nLit->value().isNull();
481 }
A abstract base class for defining QgsExpression functions.
QString name() const
The name of the function.
A binary expression operator, which operates on two values.
QgsExpressionNodeBinaryOperator::BinaryOperator op() const
Returns the binary operator.
QgsExpressionNode * opRight() const
Returns the node to the right of the operator.
QgsExpressionNode * opLeft() const
Returns the node to the left of the operator.
BinaryOperator
list of binary operators
An expression node which takes it value from a feature's field.
QString name() const
The name of the column.
An expression node for expression functions.
int fnIndex() const
Returns the index of the node's function.
QgsExpressionNode::NodeList * args() const
Returns a list of arguments specified for the function.
An expression node for value IN or NOT IN clauses.
QgsExpressionNode * node() const
Returns the expression node.
QgsExpressionNode::NodeList * list() const
Returns the list of nodes to search for matching values within.
bool isNotIn() const
Returns true if this node is a "NOT IN" operator, or false if the node is a normal "IN" operator.
An expression node for literal values.
QVariant value() const
The value of the literal.
A unary node is either negative as in boolean (not) or as in numbers (minus).
QgsExpressionNodeUnaryOperator::UnaryOperator op() const
Returns the unary operator.
QgsExpressionNode * operand() const
Returns the node the operator will operate upon.
QList< QgsExpressionNode * > list()
Gets a list of all the nodes.
Abstract base class for all nodes that can appear in an expression.
bool hasCachedStaticValue() const
Returns true if the node can be replaced by a static cached value.
virtual QgsExpressionNode::NodeType nodeType() const =0
Gets the type of this node.
QVariant cachedStaticValue() const
Returns the node's static cached value.
@ ntIndexOperator
Index operator.
Class for parsing and evaluation of expressions (formerly called "search strings").
static const QList< QgsExpressionFunction * > & Functions()
const QgsExpressionNode * rootNode() const
Returns the root node of the expression.
QString name
Definition: qgsfield.h:60
Container of fields for a vector layer.
Definition: qgsfields.h:45
QgsField at(int i) const
Returns the field at particular index (must be in range 0..N-1).
Definition: qgsfields.cpp:163
int lookupField(const QString &fieldName) const
Looks up field's index from the field name.
Definition: qgsfields.cpp:344
virtual Result compileNode(const QgsExpressionNode *node, QString &str)
Compiles an expression node and returns the result of the compilation.
virtual Result compile(const QgsExpression *exp)
Compiles an expression and returns the result of the compilation.
Result
Possible results from expression compilation.
@ Fail
Provider cannot handle expression.
@ Complete
Expression was successfully compiled and can be completely delegated to provider.
@ Partial
Expression was partially compiled, but provider will return extra records and results must be double-...
virtual QStringList sqlArgumentsFromFunctionName(const QString &fnName, const QStringList &fnArgs) const
Returns the Arguments for SQL function for the expression function.
virtual Result replaceNodeByStaticCachedValueIfPossible(const QgsExpressionNode *node, QString &str)
Tries to replace a node by its static cached value where possible.
virtual QString result()
Returns the compiled expression string for use by the provider.
virtual QString quotedValue(const QVariant &value, bool &ok)
Returns a quoted attribute value, in the format expected by the provider.
virtual QString castToText(const QString &value) const
Casts a value to a text result.
virtual QString castToInt(const QString &value) const
Casts a value to a integer result.
virtual QString quotedIdentifier(const QString &identifier)
Returns a quoted column identifier, in the format expected by the provider.
virtual QString sqlFunctionFromFunctionName(const QString &fnName) const
Returns the SQL function for the expression function.
QgsSqlExpressionCompiler(const QgsFields &fields, QgsSqlExpressionCompiler::Flags flags=Flags(), bool ignoreStaticNodes=false)
Constructor for expression compiler.
virtual QString castToReal(const QString &value) const
Casts a value to a real result.
bool opIsStringComparison(QgsExpressionNodeBinaryOperator::BinaryOperator op)
Returns true if op is one of.
@ LikeIsCaseInsensitive
Provider treats LIKE as case-insensitive.
@ NoUnaryMinus
Provider does not unary minus, e.g., " -( 100 * 2 ) = ...".
@ CaseInsensitiveStringMatch
Provider performs case-insensitive string matching for all strings.
@ NoNullInBooleanLogic
Provider does not support using NULL with boolean logic, e.g., "(...) OR NULL".
@ IntegerDivisionResultsInInteger
Dividing int by int results in int on provider. Subclass must implement the castToReal() function to ...