QGIS API Documentation 3.27.0-Master (75dc696944)
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
21QgsSqlExpressionCompiler::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
53QString QgsSqlExpressionCompiler::quotedIdentifier( const QString &identifier )
54{
55 QString quoted = identifier;
56 quoted.replace( '"', QLatin1String( "\"\"" ) );
57 quoted = quoted.prepend( '\"' ).append( '\"' );
58 return quoted;
59}
60
61QString 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 {
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
474QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
475{
476 Q_UNUSED( fnName )
477 return QString();
478}
479
480QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
481{
482 Q_UNUSED( fnName )
483 return QStringList( fnArgs );
484}
485
486QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
487{
488 Q_UNUSED( value )
489 return QString();
490}
491
492QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
493{
494 return value;
495}
496
497QString 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
527bool 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 * 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: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 ...