QGIS API Documentation 3.40.0-Bratislava (b56115d8743)
Loading...
Searching...
No Matches
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
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.userType() )
70 {
71 case QMetaType::Type::Int:
72 case QMetaType::Type::LongLong:
73 case QMetaType::Type::Double:
74 return value.toString();
75
76 case QMetaType::Type::Bool:
77 return value.toBool() ? QStringLiteral( "TRUE" ) : QStringLiteral( "FALSE" );
78
79 default:
80 case QMetaType::Type::QString:
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 // This is just to identify the most simple cases where nodes are numeric
97 std::function<bool( const QgsExpressionNode * )> nodeIsNumeric;
98 nodeIsNumeric = [this, &nodeIsNumeric]( const QgsExpressionNode * node )
99 {
100 const QgsExpressionNode::NodeType nodeType { node->nodeType() };
101
102 switch ( nodeType )
103 {
105 {
106 const QgsExpressionNodeColumnRef *col = static_cast<const QgsExpressionNodeColumnRef *>( node );
107 const int idx = mFields.indexFromName( col->name() );
108 return idx >= 0 && QgsVariantUtils::isNumericType( mFields[idx].type() );
109 }
111 {
112 const QgsExpressionNodeLiteral *lit = static_cast<const QgsExpressionNodeLiteral *>( node );
113 return QgsVariantUtils::isNumericType( static_cast< QMetaType::Type >( lit->value().userType() ) );
114 }
116 {
117 const QgsExpressionNodeBinaryOperator *op = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
118 return nodeIsNumeric( op->opLeft() ) && nodeIsNumeric( op->opRight() );
119 }
121 {
122 const QgsExpressionNodeUnaryOperator *op = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
123 return nodeIsNumeric( op->operand() );
124 }
125
126 default:
127 return false;
128 }
129
130 };
131
132 switch ( node->nodeType() )
133 {
135 {
136 const QgsExpressionNodeUnaryOperator *n = static_cast<const QgsExpressionNodeUnaryOperator *>( node );
137 switch ( n->op() )
138 {
140 {
141 QString right;
142 if ( compileNode( n->operand(), right ) == Complete )
143 {
144 result = "( NOT " + right + ')';
145 return Complete;
146 }
147
148 return Fail;
149 }
150
152 {
153 if ( mFlags.testFlag( NoUnaryMinus ) )
154 return Fail;
155
156 QString right;
157 if ( compileNode( n->operand(), right ) == Complete )
158 {
159 result = "( - (" + right + "))";
160 return Complete;
161 }
162
163 return Fail;
164 }
165 }
166
167 break;
168 }
169
171 {
172 const QgsExpressionNodeBinaryOperator *n = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
173
174 QString op;
175 bool partialCompilation = false;
176 bool failOnPartialNode = false;
177 switch ( n->op() )
178 {
181 {
182 // equality between column refs results in a partial compilation, since provider is performing
183 // case-insensitive matches between strings
184 partialCompilation = true;
185 }
186
187 op = QStringLiteral( "=" );
188 break;
189
191 op = QStringLiteral( ">=" );
192 break;
193
195 op = QStringLiteral( ">" );
196 break;
197
199 op = QStringLiteral( "<=" );
200 break;
201
203 op = QStringLiteral( "<" );
204 break;
205
207 op = QStringLiteral( "IS" );
208 break;
209
211 op = QStringLiteral( "IS NOT" );
212 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
213 break;
214
216 op = QStringLiteral( "LIKE" );
217 partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
218 break;
219
221 if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
222 op = QStringLiteral( "LIKE" );
223 else
224 op = QStringLiteral( "ILIKE" );
225 break;
226
228 op = QStringLiteral( "NOT LIKE" );
229 partialCompilation = mFlags.testFlag( LikeIsCaseInsensitive );
230 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
231 break;
232
234 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
235 if ( mFlags.testFlag( LikeIsCaseInsensitive ) )
236 op = QStringLiteral( "NOT LIKE" );
237 else
238 op = QStringLiteral( "NOT ILIKE" );
239 break;
240
242 if ( mFlags.testFlag( NoNullInBooleanLogic ) )
243 {
244 if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
245 return Fail;
246 }
247
248 op = QStringLiteral( "OR" );
249 break;
250
252 if ( mFlags.testFlag( NoNullInBooleanLogic ) )
253 {
254 if ( nodeIsNullLiteral( n->opLeft() ) || nodeIsNullLiteral( n->opRight() ) )
255 return Fail;
256 }
257
258 op = QStringLiteral( "AND" );
259 break;
260
262 failOnPartialNode = mFlags.testFlag( CaseInsensitiveStringMatch );
263 op = QStringLiteral( "<>" );
264 break;
265
267 op = QStringLiteral( "*" );
268 break;
269
271 {
272 const QgsExpressionNodeBinaryOperator *nodeOp = static_cast<const QgsExpressionNodeBinaryOperator *>( node );
273 if ( nodeIsNumeric( nodeOp->opLeft() ) && nodeIsNumeric( nodeOp->opRight() ) )
274 {
275 op = QStringLiteral( "+" );
276 }
277 break;
278 }
279
281 op = QStringLiteral( "-" );
282 break;
283
285 op = QStringLiteral( "/" );
286 break;
287
289 op = QStringLiteral( "%" );
290 break;
291
293 op = QStringLiteral( "||" );
294 break;
295
297 op = QStringLiteral( "/" );
298 break;
299
301 op = QStringLiteral( "^" );
302 break;
303
305 op = QStringLiteral( "~" );
306 break;
307 }
308
309 if ( op.isNull() )
310 return Fail;
311
312 QString left;
313 const Result lr( compileNode( n->opLeft(), left ) );
314
315 if ( opIsStringComparison( n ->op() ) )
316 left = castToText( left );
317
318 QString right;
319 const Result rr( compileNode( n->opRight(), right ) );
320
321 if ( failOnPartialNode && ( lr == Partial || rr == Partial ) )
322 return Fail;
323
325 {
326 right = castToReal( right );
327 if ( right.isEmpty() )
328 {
329 // not supported
330 return Fail;
331 }
332 }
333
334 result = '(' + left + ' ' + op + ' ' + right + ')';
336 {
338 if ( result.isEmpty() )
339 {
340 // not supported
341 return Fail;
342 }
343 }
344
345 if ( lr == Complete && rr == Complete )
346 return ( partialCompilation ? Partial : Complete );
347 else if ( ( lr == Partial && rr == Complete ) || ( lr == Complete && rr == Partial ) || ( lr == Partial && rr == Partial ) )
348 return Partial;
349 else
350 return Fail;
351 }
352
354 {
355 const QgsExpressionNodeBetweenOperator *n = static_cast<const QgsExpressionNodeBetweenOperator *>( node );
356 QString res;
357 Result betweenResult = Complete;
358
359 const Result rn = compileNode( n->node(), res );
360 if ( rn == Complete || rn == Partial )
361 {
362 if ( rn == Partial )
363 {
364 betweenResult = Partial;
365 }
366 }
367 else
368 {
369 return rn;
370 }
371
372 QString s;
373 const Result rl = compileNode( n->lowerBound(), s );
374 if ( rl == Complete || rl == Partial )
375 {
376 if ( rl == Partial )
377 {
378 betweenResult = Partial;
379 }
380 }
381 else
382 {
383 return rl;
384 }
385
386 res.append( n->negate() ? QStringLiteral( " NOT BETWEEN %1" ).arg( s ) : QStringLiteral( " BETWEEN %1" ).arg( s ) );
387
388 const Result rh = compileNode( n->higherBound(), s );
389 if ( rh == Complete || rh == Partial )
390 {
391 if ( rh == Partial )
392 {
393 betweenResult = Partial;
394 }
395 }
396 else
397 {
398 return rh;
399 }
400
401 res.append( QStringLiteral( " AND %1" ).arg( s ) );
402 result = res;
403 return betweenResult;
404 }
405
407 {
408 const QgsExpressionNodeLiteral *n = static_cast<const QgsExpressionNodeLiteral *>( node );
409 bool ok = false;
410 if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && n->value().userType() == QMetaType::Type::QString )
411 {
412 // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
413 // double check results using QGIS' expression engine
414 result = quotedValue( n->value(), ok );
415 return ok ? Partial : Fail;
416 }
417 else
418 {
419 result = quotedValue( n->value(), ok );
420 return ok ? Complete : Fail;
421 }
422 }
423
425 {
426 const QgsExpressionNodeColumnRef *n = static_cast<const QgsExpressionNodeColumnRef *>( node );
427
428 // QGIS expressions don't care about case sensitive field naming, so we match case insensitively here to the
429 // layer's fields and then retrieve the actual case of the field name for use in the compilation
430 const int fieldIndex = mFields.lookupField( n->name() );
431 if ( fieldIndex == -1 )
432 // Not a provider field
433 return Fail;
434
435 result = quotedIdentifier( mFields.at( fieldIndex ).name() );
436
437 return Complete;
438 }
439
441 {
442 const QgsExpressionNodeInOperator *n = static_cast<const QgsExpressionNodeInOperator *>( node );
443 QStringList list;
444
445 Result inResult = Complete;
446 const auto constList = n->list()->list();
447 for ( const QgsExpressionNode *ln : constList )
448 {
449 QString s;
450 const Result r = compileNode( ln, s );
451 if ( r == Complete || r == Partial )
452 {
453 list << s;
454 if ( r == Partial )
455 inResult = Partial;
456 }
457 else
458 return r;
459 }
460
461 QString nd;
462 const Result rn = compileNode( n->node(), nd );
463 if ( rn != Complete && rn != Partial )
464 return rn;
465
466 result = QStringLiteral( "%1 %2IN (%3)" ).arg( nd, n->isNotIn() ? QStringLiteral( "NOT " ) : QString(), list.join( ',' ) );
467 return ( inResult == Partial || rn == Partial ) ? Partial : Complete;
468 }
469
471 {
472 const QgsExpressionNodeFunction *n = static_cast<const QgsExpressionNodeFunction *>( node );
474
475 // get sql function to compile node expression
476 const QString nd = sqlFunctionFromFunctionName( fd->name() );
477 // if no sql function the node can't be compiled
478 if ( nd.isNull() )
479 return Fail;
480
481 // compile arguments
482 QStringList args;
483 Result inResult = Complete;
484 const auto constList = n->args()->list();
485 for ( const QgsExpressionNode *ln : constList )
486 {
487 QString s;
488 const Result r = compileNode( ln, s );
489 if ( r == Complete || r == Partial )
490 {
491 args << s;
492 if ( r == Partial )
493 inResult = Partial;
494 }
495 else
496 return r;
497 }
498
499 // update arguments to be adapted to SQL function
500 args = sqlArgumentsFromFunctionName( fd->name(), args );
501
502 // build result
503 result = !nd.isEmpty() ? QStringLiteral( "%1(%2)" ).arg( nd, args.join( ',' ) ) : args.join( ',' );
504 return inResult == Partial ? Partial : Complete;
505 }
506
508 break;
509
511 break;
512 }
513
514 return Fail;
515}
516
517QString QgsSqlExpressionCompiler::sqlFunctionFromFunctionName( const QString &fnName ) const
518{
519 Q_UNUSED( fnName )
520 return QString();
521}
522
523QStringList QgsSqlExpressionCompiler::sqlArgumentsFromFunctionName( const QString &fnName, const QStringList &fnArgs ) const
524{
525 Q_UNUSED( fnName )
526 return QStringList( fnArgs );
527}
528
529QString QgsSqlExpressionCompiler::castToReal( const QString &value ) const
530{
531 Q_UNUSED( value )
532 return QString();
533}
534
535QString QgsSqlExpressionCompiler::castToText( const QString &value ) const
536{
537 return value;
538}
539
540QString QgsSqlExpressionCompiler::castToInt( const QString &value ) const
541{
542 Q_UNUSED( value )
543 return QString();
544}
545
547{
548 if ( mIgnoreStaticNodes )
549 return Fail;
550
551 if ( node->hasCachedStaticValue() )
552 {
553 bool ok = false;
554 if ( mFlags.testFlag( CaseInsensitiveStringMatch ) && node->cachedStaticValue().userType() == QMetaType::Type::QString )
555 {
556 // provider uses case insensitive matching, so if literal was a string then we only have a Partial compilation and need to
557 // double check results using QGIS' expression engine
558 result = quotedValue( node->cachedStaticValue(), ok );
559 return ok ? Partial : Fail;
560 }
561 else
562 {
563 result = quotedValue( node->cachedStaticValue(), ok );
564 return ok ? Complete : Fail;
565 }
566 }
567 return Fail;
568}
569
570bool QgsSqlExpressionCompiler::nodeIsNullLiteral( const QgsExpressionNode *node ) const
571{
572 if ( node->nodeType() != QgsExpressionNode::ntLiteral )
573 return false;
574
575 const QgsExpressionNodeLiteral *nLit = static_cast<const QgsExpressionNodeLiteral *>( node );
576 return QgsVariantUtils::isNull( nLit->value() );
577}
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.
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.
NodeType
Known node types.
@ 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:62
Container of fields for a vector layer.
Definition qgsfields.h:46
Q_INVOKABLE int indexFromName(const QString &fieldName) const
Gets the field index from the field name.
QgsField at(int i) const
Returns the field at particular index (must be in range 0..N-1).
Q_INVOKABLE int lookupField(const QString &fieldName) const
Looks up field's index from the field name.
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, bool silenceNullWarnings=false)
Returns true if the specified variant should be considered a NULL value.
static bool isNumericType(QMetaType::Type metaType)
Returns true if the specified metaType is a numeric type.