1 /***************************************************************************
2 qgshanafeatureiterator.cpp
3 --------------------------------------
4 Date : 31-05-2019
5 Copyright : (C) SAP SE
6 Author : Maxim Rylov
7 ***************************************************************************/
8
9 /***************************************************************************
10 *
11 * This program is free software; you can redistribute it and/or modify
12 * it under the terms of the GNU General Public License as published by
13 * the Free Software Foundation; either version 2 of the License, or
14 * (at your option) any later version.
15 *
16 ***************************************************************************/
17 #include "qgsexception.h"
18 #include "qgsgeometry.h"
19 #include "qgsgeometryfactory.h"
20 #include "qgshanaconnection.h"
21 #include "qgshanaexception.h"
22 #include "qgshanaexpressioncompiler.h"
23 #include "qgshanafeatureiterator.h"
24 #include "qgshanaprimarykeys.h"
25 #include "qgshanaprovider.h"
26 #include "qgshanacrsutils.h"
27 #include "qgshanautils.h"
28 #include "qgslogger.h"
29 #include "qgsmessagelog.h"
30 #include "qgssettings.h"
31 #include "qgsgeometryengine.h"
32
33 namespace
34 {
clampBBOX(const QgsRectangle & bbox,const QgsCoordinateReferenceSystem & crs,double allowedExcessFactor)35 QgsRectangle clampBBOX( const QgsRectangle &bbox, const QgsCoordinateReferenceSystem &crs, double allowedExcessFactor )
36 {
37 // In geographic CRS', HANA will reject any points outside the "normalized"
38 // range, which is (in radian) [-PI;PI] for longitude and [-PI/2;PI/2] for
39 // latitude. As QGIS seems to expect that larger bounding boxes should
40 // be allowed and should not wrap-around, we clamp the bounding boxes for
41 // geographic CRS' here.
42 if ( !crs.isGeographic() )
43 return bbox;
44
45 double factor = QgsHanaCrsUtils::getAngularUnits( crs );
46
47 double minx = -M_PI / factor;
48 double maxx = M_PI / factor;
49 double spanx = maxx - minx;
50 minx -= allowedExcessFactor * spanx;
51 maxx += allowedExcessFactor * spanx;
52
53 double miny = -0.5 * M_PI / factor;
54 double maxy = 0.5 * M_PI / factor;
55 double spany = maxy - miny;
56 miny -= allowedExcessFactor * spany;
57 maxy += allowedExcessFactor * spany;
58
59 return bbox.intersect( QgsRectangle( minx, miny, maxx, maxy ) );
60 }
61
fieldExpression(const QgsField & field)62 QString fieldExpression( const QgsField &field )
63 {
64 QString typeName = field.typeName();
65 QString fieldName = QgsHanaUtils::quotedIdentifier( field.name() );
66 if ( field.type() == QVariant::String &&
67 ( typeName == QLatin1String( "ST_GEOMETRY" ) || typeName == QLatin1String( "ST_POINT" ) ) )
68 return QStringLiteral( "%1.ST_ASWKT()" ).arg( fieldName );
69 return fieldName;
70 }
71 }
72
QgsHanaFeatureIterator(QgsHanaFeatureSource * source,bool ownSource,const QgsFeatureRequest & request)73 QgsHanaFeatureIterator::QgsHanaFeatureIterator(
74 QgsHanaFeatureSource *source,
75 bool ownSource,
76 const QgsFeatureRequest &request )
77 : QgsAbstractFeatureIteratorFromSource<QgsHanaFeatureSource>( source, ownSource, request )
78 , mDatabaseVersion( source->mDatabaseVersion )
79 , mConnection( source->mUri )
80 {
81 if ( mConnection.isNull() )
82 {
83 mClosed = true;
84 iteratorClosed();
85 return;
86 }
87
88 if ( mRequest.destinationCrs().isValid() && mRequest.destinationCrs() != mSource->mCrs )
89 mTransform = QgsCoordinateTransform( mSource->mCrs, mRequest.destinationCrs(), mRequest.transformContext() );
90
91 try
92 {
93 mFilterRect = filterRectToSourceCrs( mTransform );
94 }
95 catch ( QgsCsException & )
96 {
97 close();
98 return;
99 }
100
101 // prepare spatial filter geometries for optimal speed
102 switch ( mRequest.spatialFilterType() )
103 {
104 case Qgis::SpatialFilterType::NoFilter:
105 case Qgis::SpatialFilterType::BoundingBox:
106 break;
107
108 case Qgis::SpatialFilterType::DistanceWithin:
109 if ( !mRequest.referenceGeometry().isEmpty() )
110 {
111 mDistanceWithinGeom = mRequest.referenceGeometry();
112 mDistanceWithinEngine.reset( QgsGeometry::createGeometryEngine( mDistanceWithinGeom.constGet() ) );
113 mDistanceWithinEngine->prepareGeometry();
114 }
115 break;
116 }
117
118 try
119 {
120 mSqlQuery = buildSqlQuery( request );
121 mSqlQueryParams = buildSqlQueryParameters();
122
123 rewind();
124 }
125 catch ( const QgsHanaException & )
126 {
127 close();
128 }
129 }
130
~QgsHanaFeatureIterator()131 QgsHanaFeatureIterator::~QgsHanaFeatureIterator()
132 {
133 close();
134 }
135
rewind()136 bool QgsHanaFeatureIterator::rewind()
137 {
138 if ( mClosed )
139 return false;
140
141 mResultSet.reset();
142 mResultSet = mConnection->executeQuery( mSqlQuery, mSqlQueryParams );
143
144 return true;
145 }
146
close()147 bool QgsHanaFeatureIterator::close()
148 {
149
150 if ( mClosed )
151 return false;
152
153 if ( mResultSet )
154 {
155 mResultSet->close();
156 mResultSet.reset();
157 }
158
159 iteratorClosed();
160 mClosed = true;
161
162 return true;
163 }
164
fetchFeature(QgsFeature & feature)165 bool QgsHanaFeatureIterator::fetchFeature( QgsFeature &feature )
166 {
167 feature.setValid( false );
168
169 if ( mClosed || !mResultSet )
170 return false;
171
172 while ( mResultSet->next() )
173 {
174 feature.initAttributes( mSource->mFields.count() );
175 unsigned short paramIndex = 1;
176
177 // Read feature id
178 QgsFeatureId fid = FID_NULL;
179 bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
180 QgsAttributeList fetchAttributes = mRequest.subsetOfAttributes();
181
182 if ( !mSource->mPrimaryKeyAttrs.isEmpty() )
183 {
184 switch ( mSource->mPrimaryKeyType )
185 {
186 case QgsHanaPrimaryKeyType::PktInt:
187 {
188 int idx = mSource->mPrimaryKeyAttrs.at( 0 );
189 QVariant v = mResultSet->getValue( paramIndex );
190 if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
191 feature.setAttribute( idx, v );
192 fid = QgsHanaPrimaryKeyUtils::intToFid( v.toInt() );
193 ++paramIndex;
194 }
195 break;
196 case QgsHanaPrimaryKeyType::PktInt64:
197 {
198 int idx = mSource->mPrimaryKeyAttrs.at( 0 );
199 QVariant v = mResultSet->getValue( paramIndex );
200 if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
201 feature.setAttribute( idx, v );
202 fid = mSource->mPrimaryKeyCntx->lookupFid( QVariantList( { v} ) );
203 ++paramIndex;
204 }
205 break;
206 case QgsHanaPrimaryKeyType::PktFidMap:
207 {
208 QVariantList pkValues;
209 pkValues.reserve( mSource->mPrimaryKeyAttrs.size() );
210 for ( int idx : std::as_const( mSource->mPrimaryKeyAttrs ) )
211 {
212 QVariant v = mResultSet->getValue( paramIndex );
213 pkValues << v;
214 if ( !subsetOfAttributes || fetchAttributes.contains( idx ) )
215 feature.setAttribute( idx, v );
216 paramIndex++;
217 }
218 fid = mSource->mPrimaryKeyCntx->lookupFid( pkValues );
219 }
220 break;
221 case QgsHanaPrimaryKeyType::PktUnknown:
222 break;
223 }
224 }
225
226 feature.setId( fid );
227
228 // Read attributes
229 if ( mHasAttributes )
230 {
231 for ( int idx : std::as_const( mAttributesToFetch ) )
232 {
233 feature.setAttribute( idx, mResultSet->getValue( paramIndex ) );
234 ++paramIndex;
235 }
236 }
237
238 // Read geometry
239 if ( mHasGeometryColumn )
240 {
241 QgsGeometry geom = mResultSet->getGeometry( paramIndex );
242 if ( !geom.isNull() )
243 feature.setGeometry( geom );
244 else
245 feature.clearGeometry();
246 }
247 else
248 {
249 feature.clearGeometry();
250 }
251
252 geometryToDestinationCrs( feature, mTransform );
253 if ( mDistanceWithinEngine && mDistanceWithinEngine->distance( feature.geometry().constGet() ) > mRequest.distanceWithin() )
254 {
255 continue;
256 }
257
258 feature.setValid( true );
259 feature.setFields( mSource->mFields ); // allow name-based attribute lookups
260 return true;
261 }
262 return false;
263 }
264
nextFeatureFilterExpression(QgsFeature & feature)265 bool QgsHanaFeatureIterator::nextFeatureFilterExpression( QgsFeature &feature )
266 {
267 if ( !mExpressionCompiled )
268 return QgsAbstractFeatureIterator::nextFeatureFilterExpression( feature );
269 else
270 return fetchFeature( feature );
271 }
272
getBBOXFilter() const273 QString QgsHanaFeatureIterator::getBBOXFilter( ) const
274 {
275 if ( mDatabaseVersion.majorVersion() == 1 )
276 return QStringLiteral( "%1.ST_SRID(%2).ST_IntersectsRect(ST_GeomFromText(?, ?), ST_GeomFromText(?, ?)) = 1" )
277 .arg( QgsHanaUtils::quotedIdentifier( mSource->mGeometryColumn ), QString::number( mSource->mSrid ) );
278 else
279 return QStringLiteral( "%1.ST_IntersectsRectPlanar(ST_GeomFromText(?, ?), ST_GeomFromText(?, ?)) = 1" )
280 .arg( QgsHanaUtils::quotedIdentifier( mSource->mGeometryColumn ) );
281 }
282
getFilterRect() const283 QgsRectangle QgsHanaFeatureIterator::getFilterRect() const
284 {
285 const QgsCoordinateReferenceSystem &crs = mSource->mCrs;
286 if ( !crs.isGeographic() )
287 return mFilterRect;
288
289 if ( mDatabaseVersion.majorVersion() > 1 )
290 return clampBBOX( mFilterRect, crs, 0.0 );
291
292 int srid = QgsHanaUtils::toPlanarSRID( mSource->mSrid );
293 if ( srid == mSource->mSrid )
294 return mFilterRect;
295 return clampBBOX( mFilterRect, crs, 0.5 );
296 }
297
prepareOrderBy(const QList<QgsFeatureRequest::OrderByClause> & orderBys)298 bool QgsHanaFeatureIterator::prepareOrderBy( const QList<QgsFeatureRequest::OrderByClause> &orderBys )
299 {
300 Q_UNUSED( orderBys )
301 // Preparation has already been done in the constructor, so we just communicate the result
302 return mOrderByCompiled;
303 }
304
buildSqlQuery(const QgsFeatureRequest & request)305 QString QgsHanaFeatureIterator::buildSqlQuery( const QgsFeatureRequest &request )
306 {
307 const bool geometryRequested = ( request.flags() & QgsFeatureRequest::NoGeometry ) == 0
308 || request.spatialFilterType() == Qgis::SpatialFilterType::DistanceWithin;
309 bool limitAtProvider = ( request.limit() >= 0 ) && mRequest.spatialFilterType() != Qgis::SpatialFilterType::DistanceWithin;
310
311 QgsRectangle filterRect = mFilterRect;
312 if ( !mSource->mSrsExtent.isEmpty() )
313 filterRect = mSource->mSrsExtent.intersect( filterRect );
314
315 if ( !filterRect.isFinite() )
316 QgsMessageLog::logMessage( QObject::tr( "Infinite filter rectangle specified" ), QObject::tr( "SAP HANA" ) );
317
318 QStringList orderByParts;
319 #if 0
320 mOrderByCompiled = true;
321
322 if ( QgsSettings().value( QStringLiteral( "qgis/compileExpressions" ), true ).toBool() )
323 {
324 const auto constOrderBy = request.orderBy();
325 for ( const QgsFeatureRequest::OrderByClause &clause : constOrderBy )
326 {
327 QgsHanaExpressionCompiler compiler = QgsHanaExpressionCompiler( mSource );
328 QgsExpression expression = clause.expression();
329 if ( compiler.compile( &expression ) == QgsSqlExpressionCompiler::Complete )
330 {
331 QString part;
332 part = compiler.result();
333 part += clause.ascending() ? QStringLiteral( " ASC" ) : QStringLiteral( " DESC" );
334 part += clause.nullsFirst() ? QStringLiteral( " NULLS FIRST" ) : QStringLiteral( " NULLS LAST" );
335 orderByParts << part;
336 }
337 else
338 {
339 // Bail out on first non-complete compilation.
340 // Most important clauses at the beginning of the list
341 // will still be sent and used to pre-sort so the local
342 // CPU can use its cycles for fine-tuning.
343 mOrderByCompiled = false;
344 break;
345 }
346 }
347 }
348 else
349 {
350 mOrderByCompiled = false;
351 }
352 #endif
353
354 if ( !mOrderByCompiled )
355 limitAtProvider = false;
356
357 bool subsetOfAttributes = mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes;
358 QgsAttributeIds attrIds = qgis::listToSet( subsetOfAttributes ?
359 request.subsetOfAttributes() : mSource->mFields.allAttributesList() );
360
361 if ( subsetOfAttributes )
362 {
363 if ( mRequest.filterType() == QgsFeatureRequest::FilterExpression )
364 // Ensure that all attributes required for expression filter are fetched
365 attrIds.unite( request.filterExpression()->referencedAttributeIndexes( mSource->mFields ) );
366
367 if ( !mRequest.orderBy().isEmpty() )
368 // Ensure that all attributes required for order by are fetched
369 attrIds.unite( mRequest.orderBy().usedAttributeIndices( mSource->mFields ) );
370 }
371
372 QStringList sqlFields;
373 // Add feature id column
374 for ( int idx : std::as_const( mSource->mPrimaryKeyAttrs ) )
375 {
376 const QgsField &field = mSource->mFields.at( idx );
377 sqlFields.push_back( fieldExpression( field ) );
378 }
379
380 for ( int idx : std::as_const( attrIds ) )
381 {
382 if ( mSource->mPrimaryKeyAttrs.contains( idx ) )
383 continue;
384
385 mAttributesToFetch.append( idx );
386 const QgsField &field = mSource->mFields.at( idx );
387 sqlFields.push_back( fieldExpression( field ) );
388 }
389
390 mHasAttributes = !mAttributesToFetch.isEmpty();
391
392 // Add geometry column
393 if ( mSource->isSpatial() &&
394 ( geometryRequested || ( request.filterType() == QgsFeatureRequest::FilterExpression &&
395 request.filterExpression()->needsGeometry() ) ) )
396 {
397 sqlFields += QgsHanaUtils::quotedIdentifier( mSource->mGeometryColumn );
398 mHasGeometryColumn = true;
399 }
400
401 QStringList sqlFilter;
402 // Set spatial filter
403 if ( mSource->isSpatial() && mHasGeometryColumn && !( filterRect.isNull() || filterRect.isEmpty() ) )
404 sqlFilter.push_back( getBBOXFilter() );
405
406 if ( !mSource->mQueryWhereClause.isEmpty() )
407 sqlFilter.push_back( mSource->mQueryWhereClause );
408
409 // Set fid filter
410 if ( !mSource->mPrimaryKeyAttrs.isEmpty() )
411 {
412 if ( request.filterType() == QgsFeatureRequest::FilterFid )
413 {
414 QString fidWhereClause = QgsHanaPrimaryKeyUtils::buildWhereClause( request.filterFid(), mSource->mFields, mSource->mPrimaryKeyType, mSource->mPrimaryKeyAttrs, *mSource->mPrimaryKeyCntx );
415 if ( fidWhereClause.isEmpty() )
416 throw QgsHanaException( QStringLiteral( "Key values for feature %1 not found." ).arg( request.filterFid() ) );
417 sqlFilter.push_back( fidWhereClause );
418 }
419 else if ( request.filterType() == QgsFeatureRequest::FilterFids && !mRequest.filterFids().isEmpty() )
420 {
421 QString fidsWhereClause = QgsHanaPrimaryKeyUtils::buildWhereClause( request.filterFids(), mSource->mFields, mSource->mPrimaryKeyType, mSource->mPrimaryKeyAttrs, *mSource->mPrimaryKeyCntx );
422 if ( fidsWhereClause.isEmpty() )
423 throw QgsHanaException( QStringLiteral( "Key values for features not found." ) );
424 sqlFilter.push_back( fidsWhereClause );
425 }
426 }
427
428 //IMPORTANT - this MUST be the last clause added
429 mExpressionCompiled = false;
430 mCompileStatus = NoCompilation;
431 if ( request.filterType() == QgsFeatureRequest::FilterExpression )
432 {
433 if ( QgsSettings().value( QStringLiteral( "qgis/compileExpressions" ), true ).toBool() )
434 {
435 QgsHanaExpressionCompiler compiler = QgsHanaExpressionCompiler( mSource, request.flags() & QgsFeatureRequest::IgnoreStaticNodesDuringExpressionCompilation );
436 QgsSqlExpressionCompiler::Result result = compiler.compile( request.filterExpression() );
437 switch ( result )
438 {
439 case QgsSqlExpressionCompiler::Result::Complete:
440 case QgsSqlExpressionCompiler::Result::Partial:
441 {
442 QString filterExpr = compiler.result();
443 if ( !filterExpr.isEmpty() )
444 {
445 sqlFilter.push_back( filterExpr );
446 //if only partial success when compiling expression, we need to double-check results
447 //using QGIS' expressions
448 mExpressionCompiled = ( result == QgsSqlExpressionCompiler::Result::Complete );
449 mCompileStatus = ( mExpressionCompiled ? Compiled : PartiallyCompiled );
450 }
451 }
452 break;
453 case QgsSqlExpressionCompiler::Result::Fail:
454 QgsDebugMsg( QStringLiteral( "Unable to compile filter expression: '%1'" )
455 .arg( request.filterExpression()->expression() ).toStdString().c_str() );
456 break;
457 case QgsSqlExpressionCompiler::Result::None:
458 break;
459 }
460 if ( result != QgsSqlExpressionCompiler::Result::Complete )
461 {
462 //can't apply limit at provider side as we need to check all results using QGIS expressions
463 limitAtProvider = false;
464 }
465 }
466 else
467 {
468 limitAtProvider = false;
469 }
470 }
471
472 QString sql = QStringLiteral( "SELECT %1 FROM %2" ).arg(
473 sqlFields.isEmpty() ? QStringLiteral( "*" ) : sqlFields.join( ',' ),
474 mSource->mQuery );
475
476 if ( !sqlFilter.isEmpty() )
477 sql += QStringLiteral( " WHERE (%1)" ).arg( sqlFilter.join( QLatin1String( ") AND (" ) ) );
478
479 if ( !orderByParts.isEmpty() )
480 sql += QStringLiteral( " ORDER BY %1 " ).arg( orderByParts.join( ',' ) );
481
482 if ( limitAtProvider )
483 sql += QStringLiteral( " LIMIT %1" ).arg( mRequest.limit() );
484
485 QgsDebugMsgLevel( "Query: " + sql, 4 );
486
487 return sql;
488 }
489
buildSqlQueryParameters() const490 QVariantList QgsHanaFeatureIterator::buildSqlQueryParameters( ) const
491 {
492 if ( !( mFilterRect.isNull() || mFilterRect.isEmpty() ) && mSource->isSpatial() && mHasGeometryColumn )
493 {
494 QgsRectangle filterRect = getFilterRect();
495 QString ll = QStringLiteral( "POINT(%1 %2)" ).arg( QString::number( filterRect.xMinimum() ), QString::number( filterRect.yMinimum() ) );
496 QString ur = QStringLiteral( "POINT(%1 %2)" ).arg( QString::number( filterRect.xMaximum() ), QString::number( filterRect.yMaximum() ) );
497 return { ll, mSource->mSrid, ur, mSource->mSrid };
498 }
499 return QVariantList();
500 }
501
QgsHanaFeatureSource(const QgsHanaProvider * p)502 QgsHanaFeatureSource::QgsHanaFeatureSource( const QgsHanaProvider *p )
503 : mDatabaseVersion( p->mDatabaseVersion )
504 , mUri( p->mUri )
505 , mQuery( p->mQuerySource )
506 , mQueryWhereClause( p->mQueryWhereClause )
507 , mPrimaryKeyType( p->mPrimaryKeyType )
508 , mPrimaryKeyAttrs( p->mPrimaryKeyAttrs )
509 , mPrimaryKeyCntx( p->mPrimaryKeyCntx )
510 , mFields( p->mFields )
511 , mGeometryColumn( p->mGeometryColumn )
512 , mGeometryType( p->wkbType() )
513 , mSrid( p->mSrid )
514 , mSrsExtent( p->mSrsExtent )
515 , mCrs( p->crs() )
516 {
517 if ( p->mHasSrsPlanarEquivalent && p->mDatabaseVersion.majorVersion() <= 1 )
518 mSrid = QgsHanaUtils::toPlanarSRID( p->mSrid );
519 }
520
521 QgsHanaFeatureSource::~QgsHanaFeatureSource() = default;
522
getFeatures(const QgsFeatureRequest & request)523 QgsFeatureIterator QgsHanaFeatureSource::getFeatures( const QgsFeatureRequest &request )
524 {
525 return QgsFeatureIterator( new QgsHanaFeatureIterator( this, false, request ) );
526 }
527