QGIS API Documentation  3.25.0-Master (6b426f5f8a)
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 QgsExpressionNodeBetweenOperator *n = static_cast<const QgsExpressionNodeBetweenOperator *>( node );
313  QString res;
314  Result betweenResult = Complete;
315 
316  const Result rn = compileNode( n->node(), res );
317  if ( rn == Complete || rn == Partial )
318  {
319  if ( rn == Partial )
320  {
321  betweenResult = Partial;
322  }
323  }
324  else
325  {
326  return rn;
327  }
328 
329  QString s;
330  const Result rl = compileNode( n->lowerBound(), s );
331  if ( rl == Complete || rl == Partial )
332  {
333  if ( rl == Partial )
334  {
335  betweenResult = Partial;
336  }
337  }
338  else
339  {
340  return rl;
341  }
342 
343  res.append( n->negate() ? QStringLiteral( " NOT BETWEEN %1" ).arg( s ) : QStringLiteral( " BETWEEN %1" ).arg( s ) );
344 
345  const Result rh = compileNode( n->higherBound(), s );
346  if ( rh == Complete || rh == Partial )
347  {
348  if ( rh == Partial )
349  {
350  betweenResult = Partial;
351  }
352  }
353  else
354  {
355  return rh;
356  }
357 
358  res.append( QStringLiteral( " AND %1" ).arg( s ) );
359  result = res;
360  return betweenResult;
361  }
362 
364  {
365  const QgsExpressionNodeLiteral *n = static_cast<const QgsExpressionNodeLiteral *>( node );
366  bool ok = false;
367  if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().type() == QVariant::String )
368  {
369  // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
370  // double check results using QGIS' expression engine
371  result = quotedValue( n->value(), ok );
372  return ok ? Partial : Fail;
373  }
374  else
375  {
376  result = quotedValue( n->value(), ok );
377  return ok ? Complete : Fail;
378  }
379  }
380 
382  {
383  const QgsExpressionNodeColumnRef *n = static_cast<const QgsExpressionNodeColumnRef *>( node );
384 
385  // QGIS expressions don't care about case sensitive field naming, so we match case insensitively here to the
386  // layer's fields and then retrieve the actual case of the field name for use in the compilation
387  const int fieldIndex = mFields.lookupField( n->name() );
388  if ( fieldIndex == -1 )
389  // Not a provider field
390  return Fail;
391 
392  result = quotedIdentifier( mFields.at( fieldIndex ).name() );
393 
394  return Complete;
395  }
396 
398  {
399  const QgsExpressionNodeInOperator *n = static_cast<const QgsExpressionNodeInOperator *>( node );
400  QStringList list;
401 
402  Result inResult = Complete;
403  const auto constList = n->list()->list();
404  for ( const QgsExpressionNode *ln : constList )
405  {
406  QString s;
407  const Result r = compileNode( ln, s );
408  if ( r == Complete || r == Partial )
409  {
410  list << s;
411  if ( r == Partial )
412  inResult = Partial;
413  }
414  else
415  return r;
416  }
417 
418  QString nd;
419  const Result rn = compileNode( n->node(), nd );
420  if ( rn != Complete && rn != Partial )
421  return rn;
422 
423  result = QStringLiteral( "%1 %2IN (%3)" ).arg( nd, n->isNotIn() ? QStringLiteral( "NOT " ) : QString(), list.join( ',' ) );
424  return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
425  }
426 
428  {
429  const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
431 
432  // get sql function to compile node expression
433  const QString nd = sqlFunctionFromFunctionName( fd->name() );
434  // if no sql function the node can't be compiled
435  if ( nd.isNull() )
436  return Fail;
437 
438  // compile arguments
439  QStringList args;
440  Result inResult = Complete;
441  const auto constList = n->args()->list();
442  for ( const QgsExpressionNode *ln : constList )
443  {
444  QString s;
445  const Result r = compileNode( ln, s );
446  if ( r == Complete || r == Partial )
447  {
448  args << s;
449  if ( r == Partial )
450  inResult = Partial;
451  }
452  else
453  return r;
454  }
455 
456  // update arguments to be adapted to SQL function
457  args = sqlArgumentsFromFunctionName( fd->name(), args );
458 
459  // build result
460  result = !nd.isEmpty() ? QStringLiteral( "%1(%2)" ).arg( nd, args.join( ',' ) ) : args.join( ',' );
461  return inResult == Partial ? Partial : Complete;
462  }
463 
465  break;
466 
468  break;
469  }
470 
471  return Fail;
472 }
473 
474 QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
475 {
476  Q_UNUSED( fnName )
477  return QString();
478 }
479 
480 QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
481 {
482  Q_UNUSED( fnName )
483  return QStringList( fnArgs );
484 }
485 
486 QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
487 {
488  Q_UNUSED( value )
489  return QString();
490 }
491 
492 QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
493 {
494  return value;
495 }
496 
497 QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
498 {
499  Q_UNUSED( value )
500  return QString();
501 }
502 
504 {
505  if ( mIgnoreStaticNodes )
506  return Fail;
507 
508  if ( node->hasCachedStaticValue() )
509  {
510  bool ok = false;
511  if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && node->cachedStaticValue().type() == QVariant::String )
512  {
513  // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
514  // double check results using QGIS' expression engine
515  result = quotedValue( node->cachedStaticValue(), ok );
516  return ok ? Partial : Fail;
517  }
518  else
519  {
520  result = quotedValue( node->cachedStaticValue(), ok );
521  return ok ? Complete : Fail;
522  }
523  }
524  return Fail;
525 }
526 
527 bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpressionNode *node ) const
528 {
529  if ( node->nodeType() != QgsExpressionNode::ntLiteral )
530  return false;
531 
532  const QgsExpressionNodeLiteral *nLit = static_cast<const QgsExpressionNodeLiteral *>( node );
533  return nLit->value().isNull();
534 }
A abstract base class for defining QgsExpression functions.
QString name() const
The name of the function.
SQL-like BETWEEN and NOT BETWEEN predicates.
bool negate() const
Returns true if the predicate is an exclusion test (NOT BETWEEN).
QgsExpressionNode * lowerBound() const
Returns the lower bound expression node of the range.
QgsExpressionNode * node() const
Returns the expression node.
QgsExpressionNode * higherBound() const
Returns the higher bound expression node of the range.
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.
@ ntBetweenOperator
Between operator.
@ 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:349
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 ...