QGIS API Documentation 3.30.0-'s-Hertogenbosch (f186b8efe0)
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
19#include "qgsexpression.h"
20#include "qgsvariantutils.h"
21
22QgsSqlExpressionCompiler::QgsSqlExpressionCompiler( const QgsFields &fields, Flags flags, bool ignoreStaticNodes )
23 : mFields( fields )
24 , mFlags( flags )
25 , mIgnoreStaticNodes( ignoreStaticNodes )
26{
27}
28
30{
31 if ( exp->rootNode() )
32 return compileNode( exp->rootNode(), mResult );
33 else
34 return Fail;
35}
36
38{
39 return mResult;
40}
41
43{
44 if ( op == QgsExpressionNodeBinaryOperator::BinaryOperator::boILike ||
45 op == QgsExpressionNodeBinaryOperator::BinaryOperator::boLike ||
46 op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotILike ||
47 op == QgsExpressionNodeBinaryOperator::BinaryOperator::boNotLike ||
48 op == QgsExpressionNodeBinaryOperator::BinaryOperator::boRegexp )
49 return true;
50 else
51 return false;
52}
53
54QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
55{
56 QString quoted = identifier;
57 quoted.replace( '"', QLatin1String( "\"\"" ) );
58 quoted = quoted.prepend( '\"' ).append( '\"' );
59 return quoted;
60}
61
62QString QgsSqlExpressionCompiler::quotedValue( const QVariant &value, bool &ok )
63{
64 ok = true;
65
66 if ( QgsVariantUtils::isNull( value ) )
67 return QStringLiteral( "NULL" );
68
69 switch ( value.type() )
70 {
71 case QVariant::Int:
72 case QVariant::LongLong:
73 case QVariant::Double:
74 return value.toString();
75
76 case QVariant::Bool:
77 return value.toBool() ? QStringLiteral( "TRUE" ) : QStringLiteral( "FALSE" );
78
79 default:
80 case QVariant::String:
81 QString v = value.toString();
82 v.replace( '\'', QLatin1String( "''" ) );
83 if ( v.contains( '\\' ) )
84 return v.replace( '\\', QLatin1String( "\\\\" ) ).prepend( "E'" ).append( '\'' );
85 else
86 return v.prepend( '\'' ).append( '\'' );
87 }
88}
89
91{
93 if ( staticRes != Fail )
94 return staticRes;
95
96 switch ( node->nodeType() )
97 {
99 {
100 const QgsExpressionNodeUnaryOperator *n = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
101 switch ( n->op() )
102 {
104 {
105 QString right;
106 if ( compileNode( n->operand(), right ) == Complete )
107 {
108 result = "( NOT " + right + ')';
109 return Complete;
110 }
111
112 return Fail;
113 }
114
116 {
117 if ( mFlags.testFlag( NoUnaryMinus ) )
118 return Fail;
119
120 QString right;
121 if ( compileNode( n->operand(), right ) == Complete )
122 {
123 result = "( - (" + right + "))";
124 return Complete;
125 }
126
127 return Fail;
128 }
129 }
130
131 break;
132 }
133
135 {
136 const QgsExpressionNodeBinaryOperator *n = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
137
138 QString op;
139 bool partialCompilation = false;
140 bool failOnPartialNode = false;
141 switch ( n->op() )
142 {
145 {
146 // equality between column refs results in a partial compilation, since provider is performing
147 // case-insensitive matches between strings
148 partialCompilation = true;
149 }
150
151 op = QStringLiteral( "=" );
152 break;
153
155 op = QStringLiteral( ">=" );
156 break;
157
159 op = QStringLiteral( ">" );
160 break;
161
163 op = QStringLiteral( "<=" );
164 break;
165
167 op = QStringLiteral( "<" );
168 break;
169
171 op = QStringLiteral( "IS" );
172 break;
173
175 op = QStringLiteral( "IS NOT" );
176 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
177 break;
178
180 op = QStringLiteral( "LIKE" );
181 partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
182 break;
183
185 if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
186 op = QStringLiteral( "LIKE" );
187 else
188 op = QStringLiteral( "ILIKE" );
189 break;
190
192 op = QStringLiteral( "NOT LIKE" );
193 partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
194 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
195 break;
196
198 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
199 if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
200 op = QStringLiteral( "NOT LIKE" );
201 else
202 op = QStringLiteral( "NOT ILIKE" );
203 break;
204
206 if ( mFlags.testFlag( NoNullInBooleanLogic ) )
207 {
208 if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
209 return Fail;
210 }
211
212 op = QStringLiteral( "OR" );
213 break;
214
216 if ( mFlags.testFlag( NoNullInBooleanLogic ) )
217 {
218 if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
219 return Fail;
220 }
221
222 op = QStringLiteral( "AND" );
223 break;
224
226 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
227 op = QStringLiteral( "<>" );
228 break;
229
231 op = QStringLiteral( "*" );
232 break;
233
235 op = QStringLiteral( "+" );
236 break;
237
239 op = QStringLiteral( "-" );
240 break;
241
243 op = QStringLiteral( "/" );
244 break;
245
247 op = QStringLiteral( "%" );
248 break;
249
251 op = QStringLiteral( "||" );
252 break;
253
255 op = QStringLiteral( "/" );
256 break;
257
259 op = QStringLiteral( "^" );
260 break;
261
263 op = QStringLiteral( "~" );
264 break;
265 }
266
267 if ( op.isNull() )
268 return Fail;
269
270 QString left;
271 const Result lr( compileNode( n->opLeft(), left ) );
272
273 if ( opIsStringComparison( n ->op() ) )
274 left = castToText( left );
275
276 QString right;
277 const Result rr( compileNode( n->opRight(), right ) );
278
279 if ( failOnPartialNode && ( lr == Partial || rr == Partial ) )
280 return Fail;
281
283 {
284 right = castToReal( right );
285 if ( right.isEmpty() )
286 {
287 // not supported
288 return Fail;
289 }
290 }
291
292 result = '(' + left + ' ' + op + ' ' + right + ')';
294 {
296 if ( result.isEmpty() )
297 {
298 // not supported
299 return Fail;
300 }
301 }
302
303 if ( lr == Complete && rr == Complete )
304 return ( partialCompilation ? Partial : Complete );
305 else if ( ( lr == Partial && rr == Complete ) || ( lr == Complete && rr == Partial ) || ( lr == Partial && rr == Partial ) )
306 return Partial;
307 else
308 return Fail;
309 }
310
312 {
313 const QgsExpressionNodeBetweenOperator *n = static_cast<const QgsExpressionNodeBetweenOperator *>( node );
314 QString res;
315 Result betweenResult = Complete;
316
317 const Result rn = compileNode( n->node(), res );
318 if ( rn == Complete || rn == Partial )
319 {
320 if ( rn == Partial )
321 {
322 betweenResult = Partial;
323 }
324 }
325 else
326 {
327 return rn;
328 }
329
330 QString s;
331 const Result rl = compileNode( n->lowerBound(), s );
332 if ( rl == Complete || rl == Partial )
333 {
334 if ( rl == Partial )
335 {
336 betweenResult = Partial;
337 }
338 }
339 else
340 {
341 return rl;
342 }
343
344 res.append( n->negate() ? QStringLiteral( " NOT BETWEEN %1" ).arg( s ) : QStringLiteral( " BETWEEN %1" ).arg( s ) );
345
346 const Result rh = compileNode( n->higherBound(), s );
347 if ( rh == Complete || rh == Partial )
348 {
349 if ( rh == Partial )
350 {
351 betweenResult = Partial;
352 }
353 }
354 else
355 {
356 return rh;
357 }
358
359 res.append( QStringLiteral( " AND %1" ).arg( s ) );
360 result = res;
361 return betweenResult;
362 }
363
365 {
366 const QgsExpressionNodeLiteral *n = static_cast<const QgsExpressionNodeLiteral *>( node );
367 bool ok = false;
368 if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().type() == QVariant::String )
369 {
370 // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
371 // double check results using QGIS' expression engine
372 result = quotedValue( n->value(), ok );
373 return ok ? Partial : Fail;
374 }
375 else
376 {
377 result = quotedValue( n->value(), ok );
378 return ok ? Complete : Fail;
379 }
380 }
381
383 {
384 const QgsExpressionNodeColumnRef *n = static_cast<const QgsExpressionNodeColumnRef *>( node );
385
386 // QGIS expressions don't care about case sensitive field naming, so we match case insensitively here to the
387 // layer's fields and then retrieve the actual case of the field name for use in the compilation
388 const int fieldIndex = mFields.lookupField( n->name() );
389 if ( fieldIndex == -1 )
390 // Not a provider field
391 return Fail;
392
393 result = quotedIdentifier( mFields.at( fieldIndex ).name() );
394
395 return Complete;
396 }
397
399 {
400 const QgsExpressionNodeInOperator *n = static_cast<const QgsExpressionNodeInOperator *>( node );
401 QStringList list;
402
403 Result inResult = Complete;
404 const auto constList = n->list()->list();
405 for ( const QgsExpressionNode *ln : constList )
406 {
407 QString s;
408 const Result r = compileNode( ln, s );
409 if ( r == Complete || r == Partial )
410 {
411 list << s;
412 if ( r == Partial )
413 inResult = Partial;
414 }
415 else
416 return r;
417 }
418
419 QString nd;
420 const Result rn = compileNode( n->node(), nd );
421 if ( rn != Complete && rn != Partial )
422 return rn;
423
424 result = QStringLiteral( "%1 %2IN (%3)" ).arg( nd, n->isNotIn() ? QStringLiteral( "NOT " ) : QString(), list.join( ',' ) );
425 return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
426 }
427
429 {
430 const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
432
433 // get sql function to compile node expression
434 const QString nd = sqlFunctionFromFunctionName( fd->name() );
435 // if no sql function the node can't be compiled
436 if ( nd.isNull() )
437 return Fail;
438
439 // compile arguments
440 QStringList args;
441 Result inResult = Complete;
442 const auto constList = n->args()->list();
443 for ( const QgsExpressionNode *ln : constList )
444 {
445 QString s;
446 const Result r = compileNode( ln, s );
447 if ( r == Complete || r == Partial )
448 {
449 args << s;
450 if ( r == Partial )
451 inResult = Partial;
452 }
453 else
454 return r;
455 }
456
457 // update arguments to be adapted to SQL function
458 args = sqlArgumentsFromFunctionName( fd->name(), args );
459
460 // build result
461 result = !nd.isEmpty() ? QStringLiteral( "%1(%2)" ).arg( nd, args.join( ',' ) ) : args.join( ',' );
462 return inResult == Partial ? Partial : Complete;
463 }
464
466 break;
467
469 break;
470 }
471
472 return Fail;
473}
474
475QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
476{
477 Q_UNUSED( fnName )
478 return QString();
479}
480
481QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
482{
483 Q_UNUSED( fnName )
484 return QStringList( fnArgs );
485}
486
487QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
488{
489 Q_UNUSED( value )
490 return QString();
491}
492
493QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
494{
495 return value;
496}
497
498QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
499{
500 Q_UNUSED( value )
501 return QString();
502}
503
505{
506 if ( mIgnoreStaticNodes )
507 return Fail;
508
509 if ( node->hasCachedStaticValue() )
510 {
511 bool ok = false;
512 if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && node->cachedStaticValue().type() == QVariant::String )
513 {
514 // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
515 // double check results using QGIS' expression engine
516 result = quotedValue( node->cachedStaticValue(), ok );
517 return ok ? Partial : Fail;
518 }
519 else
520 {
521 result = quotedValue( node->cachedStaticValue(), ok );
522 return ok ? Complete : Fail;
523 }
524 }
525 return Fail;
526}
527
528bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpressionNode *node ) const
529{
530 if ( node->nodeType() != QgsExpressionNode::ntLiteral )
531 return false;
532
533 const QgsExpressionNodeLiteral *nLit = static_cast<const QgsExpressionNodeLiteral *>( node );
534 return QgsVariantUtils::isNull( nLit->value() );
535}
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 * higherBound() const
Returns the higher bound expression node of the range.
QgsExpressionNode * node() const
Returns the expression node.
A binary expression operator, which operates on two values.
QgsExpressionNode * opLeft() const
Returns the node to the left of the operator.
QgsExpressionNode * opRight() const
Returns the node to the right of the operator.
QgsExpressionNodeBinaryOperator::BinaryOperator op() const
Returns the binary 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:61
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:359
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 ...
static bool isNull(const QVariant &variant)
Returns true if the specified variant should be considered a NULL value.