Skip to content

Commit 2c123dc

Browse files
committedJul 6, 2021
GPKG/Spatialite: return fid and geom from selects
1 parent 7b202fe commit 2c123dc

File tree

5 files changed

+162
-18
lines changed

5 files changed

+162
-18
lines changed
 

‎src/core/providers/ogr/qgsgeopackageproviderconnection.cpp

Lines changed: 79 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,9 @@
1313
* (at your option) any later version. *
1414
* *
1515
***************************************************************************/
16+
17+
#include <sqlite3.h>
18+
1619
#include "qgsgeopackageproviderconnection.h"
1720
#include "qgsogrdbconnection.h"
1821
#include "qgssettings.h"
@@ -25,6 +28,7 @@
2528

2629
#include <QTextCodec>
2730

31+
2832
QgsGeoPackageProviderConnection::QgsGeoPackageProviderConnection( const QString &name )
2933
: QgsAbstractDatabaseProviderConnection( name )
3034
{
@@ -278,8 +282,9 @@ QList<QgsGeoPackageProviderConnection::TableProperty> QgsGeoPackageProviderConne
278282
}
279283

280284
QgsGeoPackageProviderConnection::TableProperty property;
281-
property.setTableName( row.at( 0 ).toString() );
282-
property.setPrimaryKeyColumns( { QStringLiteral( "fid" ) } );
285+
const QString tableName { row.at( 0 ).toString() };
286+
property.setTableName( tableName );
287+
property.setPrimaryKeyColumns( { primaryKeyColumnName( tableName ) } );
283288
property.setGeometryColumnCount( 0 );
284289
static const QStringList aspatialTypes = { QStringLiteral( "attributes" ), QStringLiteral( "aspatial" ) };
285290
const QString dataType = row.at( 1 ).toString();
@@ -405,10 +410,25 @@ QgsAbstractDatabaseProviderConnection::QueryResult QgsGeoPackageProviderConnecti
405410
if ( fet.reset( OGR_L_GetNextFeature( ogrLayer ) ), fet )
406411
{
407412

413+
// pk column name
414+
QString pkColumnName;
415+
408416
QgsFields fields { QgsOgrUtils::readOgrFields( fet.get(), QTextCodec::codecForName( "UTF-8" ) ) };
409417

410-
// pk column name, hardcoded to "fid" (FIXME)
411-
QString pkColumnName { QStringLiteral( "fid" ) };
418+
// We try to guess the table name from the FROM clause
419+
thread_local const QRegularExpression tableNameRegexp { QStringLiteral( R"re((?<=from|join)\s+(\w+)|"([^"]+)")re" ), QRegularExpression::PatternOption::CaseInsensitiveOption };
420+
const auto match { tableNameRegexp.match( sql ) };
421+
if ( match.hasMatch() )
422+
{
423+
pkColumnName = primaryKeyColumnName( match.captured( match.lastCapturedIndex() ) );
424+
}
425+
426+
// default to "fid"
427+
if ( pkColumnName.isEmpty() )
428+
{
429+
pkColumnName = QStringLiteral( "fid" );
430+
}
431+
412432
// geom column name
413433
QString geomColumnName;
414434

@@ -424,15 +444,17 @@ QgsAbstractDatabaseProviderConnection::QueryResult QgsGeoPackageProviderConnecti
424444
geomColumnName = OGR_GFld_GetNameRef( geomFldDef );
425445
}
426446
}
427-
428447
}
429448

430-
// May need to prepend PK and append geometries to the columns
431-
thread_local const QRegularExpression pkRegExp { QStringLiteral( R"(^select\s+(\*|%1)[,\s+](.*)from)" ).arg( pkColumnName ), QRegularExpression::PatternOption::CaseInsensitiveOption };
432-
if ( pkRegExp.match( sql.trimmed() ).hasMatch() )
449+
// May need to prepend PK and append geometry to the columns
450+
if ( ! pkColumnName.isEmpty() )
433451
{
434-
iterator->setPrimaryKeyColumnName( pkColumnName );
435-
results.appendColumn( pkColumnName );
452+
const QRegularExpression pkRegExp { QStringLiteral( R"(^select\s+(\*|%1)[,\s+](.*)from)" ).arg( pkColumnName ), QRegularExpression::PatternOption::CaseInsensitiveOption };
453+
if ( pkRegExp.match( sql.trimmed() ).hasMatch() )
454+
{
455+
iterator->setPrimaryKeyColumnName( pkColumnName );
456+
results.appendColumn( pkColumnName );
457+
}
436458
}
437459

438460
// Add other fields
@@ -487,6 +509,47 @@ QgsAbstractDatabaseProviderConnection::QueryResult QgsGeoPackageProviderConnecti
487509
return QgsAbstractDatabaseProviderConnection::QueryResult();
488510
}
489511

512+
QString QgsGeoPackageProviderConnection::primaryKeyColumnName( const QString &table ) const
513+
{
514+
QString pkName;
515+
516+
sqlite3_database_unique_ptr sqliteHandle;
517+
if ( SQLITE_OK == sqliteHandle.open_v2( uri(), SQLITE_OPEN_READONLY, nullptr ) )
518+
{
519+
char *errMsg;
520+
521+
const QString sql { QStringLiteral( "PRAGMA table_info(%1)" )
522+
.arg( QgsSqliteUtils::quotedString( table ) )};
523+
524+
std::vector<std::string> rows;
525+
auto cb = [ ](
526+
void *data /* Data provided in the 4th argument of sqlite3_exec() */,
527+
int /* The number of columns in row */,
528+
char **argv /* An array of strings representing fields in the row */,
529+
char ** /* An array of strings representing column names */ ) -> int
530+
{
531+
if ( std::string( argv[5] ).compare( "1" ) == 0 )
532+
static_cast<std::vector<std::string>*>( data )->push_back( argv[1] );
533+
return 0;
534+
};
535+
536+
// Columns 'cid', 'name', 'type', 'notnull', 'dflt_value', 'pk']
537+
const int ret = sqlite3_exec( sqliteHandle.get(), sql.toUtf8(), cb, ( void * )&rows, &errMsg );
538+
539+
if ( errMsg )
540+
{
541+
sqlite3_free( errMsg );
542+
}
543+
544+
if ( SQLITE_OK == ret && rows.size() > 0 )
545+
{
546+
pkName = QString::fromStdString( rows[0] );
547+
}
548+
}
549+
550+
return pkName;
551+
}
552+
490553
QVariantList QgsGeoPackageProviderResultIterator::nextRowPrivate()
491554
{
492555
const QVariantList currentRow = mNextRow;
@@ -585,9 +648,12 @@ QgsFields QgsGeoPackageProviderConnection::fields( const QString &schema, const
585648
// Get fields from layer
586649
QgsFields fieldList;
587650

588-
// Prepend PK "fid" hardcoded (FIXME): there might be a way to get the PK name here
589-
// but there is probably no way for the general execSql case.
590-
fieldList.append( QgsField{ QStringLiteral( "fid" ), QVariant::LongLong } );
651+
const QString pkname { primaryKeyColumnName( table ) };
652+
653+
if ( ! pkname.isEmpty() )
654+
{
655+
fieldList.append( QgsField{ pkname, QVariant::LongLong } );
656+
}
591657

592658
QgsVectorLayer::LayerOptions options { false, true };
593659
options.skipCrsValidation = true;

‎src/core/providers/ogr/qgsgeopackageproviderconnection.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,8 @@ class QgsGeoPackageProviderConnection : public QgsAbstractDatabaseProviderConnec
8888
void setDefaultCapabilities();
8989
//! Use GDAL to execute SQL
9090
QueryResult executeGdalSqlPrivate( const QString &sql, QgsFeedback *feedback = nullptr ) const;
91+
//! Returns PK name for table
92+
QString primaryKeyColumnName( const QString &table ) const;
9193

9294

9395
};

‎src/providers/spatialite/qgsspatialiteproviderconnection.cpp

Lines changed: 55 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -476,28 +476,63 @@ QgsAbstractDatabaseProviderConnection::QueryResult QgsSpatiaLiteProviderConnecti
476476
if ( fet.reset( OGR_L_GetNextFeature( ogrLayer ) ), fet )
477477
{
478478

479-
QgsFields fields { QgsOgrUtils::readOgrFields( fet.get(), QTextCodec::codecForName( "UTF-8" ) ) };
480-
iterator->setFields( fields );
479+
const QgsFields fields { QgsOgrUtils::readOgrFields( fet.get(), QTextCodec::codecForName( "UTF-8" ) ) };
480+
481+
// geom column name
482+
QString geomColumnName;
483+
484+
OGRFeatureDefnH featureDef = OGR_F_GetDefnRef( fet.get() );
485+
486+
if ( featureDef )
487+
{
488+
if ( OGR_F_GetGeomFieldCount( fet.get() ) > 0 )
489+
{
490+
OGRGeomFieldDefnH geomFldDef { OGR_F_GetGeomFieldDefnRef( fet.get(), 0 ) };
491+
if ( geomFldDef )
492+
{
493+
geomColumnName = OGR_GFld_GetNameRef( geomFldDef );
494+
}
495+
}
496+
}
481497

498+
// Add other fields
482499
for ( const auto &f : std::as_const( fields ) )
483500
{
484501
results.appendColumn( f.name() );
485502
}
503+
504+
// Append geom
505+
if ( ! geomColumnName.isEmpty() )
506+
{
507+
results.appendColumn( geomColumnName );
508+
iterator->setGeometryColumnName( geomColumnName );
509+
}
510+
511+
iterator->setFields( fields );
486512
}
487513

488514
// Check for errors
489-
errCause = CPLGetLastErrorMsg( );
515+
if ( CE_Failure == CPLGetLastErrorType() || CE_Fatal == CPLGetLastErrorType() )
516+
{
517+
errCause = CPLGetLastErrorMsg( );
518+
}
490519

491520
if ( ! errCause.isEmpty() )
492521
{
493-
throw QgsProviderConnectionException( QObject::tr( "Error executing SQL %1: %2" ).arg( sql, errCause ) );
522+
throw QgsProviderConnectionException( QObject::tr( "Error executing SQL statement %1: %2" ).arg( sql, errCause ) );
494523
}
495524

496525
OGR_L_ResetReading( ogrLayer );
497526
iterator->nextRow();
498527
return results;
499528
}
500-
errCause = CPLGetLastErrorMsg( );
529+
530+
// Check for errors
531+
if ( CE_Failure == CPLGetLastErrorType() || CE_Fatal == CPLGetLastErrorType() )
532+
{
533+
errCause = CPLGetLastErrorMsg( );
534+
}
535+
501536
}
502537
else
503538
{
@@ -518,6 +553,7 @@ void QgsSpatialiteProviderResultIterator::setFields( const QgsFields &fields )
518553
mFields = fields;
519554
}
520555

556+
521557
QgsSpatialiteProviderResultIterator::~QgsSpatialiteProviderResultIterator()
522558
{
523559
if ( mHDS )
@@ -549,6 +585,13 @@ QVariantList QgsSpatialiteProviderResultIterator::nextRowInternal()
549585
{
550586
row.push_back( attribute );
551587
}
588+
589+
// Geom goes last
590+
if ( ! mGeometryColumnName.isEmpty( ) )
591+
{
592+
row.push_back( f.geometry().asWkb() );
593+
}
594+
552595
}
553596
else // Fallback to strings
554597
{
@@ -568,6 +611,13 @@ QVariantList QgsSpatialiteProviderResultIterator::nextRowInternal()
568611
return row;
569612
}
570613

614+
615+
void QgsSpatialiteProviderResultIterator::setGeometryColumnName( const QString &geometryColumnName )
616+
{
617+
mGeometryColumnName = geometryColumnName;
618+
}
619+
620+
571621
bool QgsSpatialiteProviderResultIterator::hasNextRowPrivate() const
572622
{
573623
return ! mNextRow.isEmpty();

‎src/providers/spatialite/qgsspatialiteproviderconnection.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,12 +34,15 @@ struct QgsSpatialiteProviderResultIterator: public QgsAbstractDatabaseProviderCo
3434

3535
void setFields( const QgsFields &fields );
3636

37+
void setGeometryColumnName( const QString &geometryColumnName );
38+
3739
private:
3840

3941
gdal::ogr_datasource_unique_ptr mHDS;
4042
OGRLayerH mOgrLayer;
4143
QgsFields mFields;
4244
QVariantList mNextRow;
45+
QString mGeometryColumnName;
4346

4447
QVariantList nextRowPrivate() override;
4548
bool hasNextRowPrivate() const override;

‎tests/src/python/test_qgsproviderconnection_spatialite.py

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -157,6 +157,29 @@ def test_create_vector_layer(self):
157157
self.assertEqual(len(features), 1)
158158
self.assertEqual(features[0].attributes(), [8, 'Sülfeld'])
159159

160+
def test_execute_sql_pk_geoms(self):
161+
"""OGR hides fid and geom from attributes, check if we can still get them"""
162+
163+
md = QgsProviderRegistry.instance().providerMetadata('spatialite')
164+
conn = md.createConnection(self.uri, {})
165+
166+
# Check errors
167+
with self.assertRaises(QgsProviderConnectionException):
168+
sql = 'SELECT not_exists, name, geom FROM cdb_lines WHERE name LIKE \'S%\' LIMIT 2'
169+
results = conn.executeSql(sql)
170+
171+
sql = 'SELECT fid, name, geom FROM cdb_lines WHERE name LIKE \'S%\' LIMIT 2'
172+
results = conn.executeSql(sql)
173+
self.assertEqual(results[0][:2], [8, 'Sülfeld'])
174+
self.assertEqual(results[1][:2], [16, 'Steimker Berg'])
175+
self.assertEqual(results[0][2][:20], b'\x01\x03\x00\x00\x00\x01\x00\x00\x00/\x00\x00\x00\xf6\x88\x16Y\xad\xb2"')
176+
self.assertEqual(results[1][2][:20], b'\x01\x03\x00\x00\x00\x01\x00\x00\x00F\x00\x00\x00 \xc1\x9f\xda\xb4\xfb"')
177+
178+
sql = 'SELECT name, st_astext(geom) FROM cdb_lines WHERE name LIKE \'S%\' LIMIT 2'
179+
results = conn.executeSql(sql)
180+
self.assertEqual(results[0], ['Sülfeld',
181+
'POLYGON((612694.674 5807839.658, 612668.715 5808176.815, 612547.354 5808414.452, 612509.527 5808425.73, 612522.932 5808473.02, 612407.901 5808519.082, 612505.836 5808632.763, 612463.449 5808781.115, 612433.57 5808819.061, 612422.685 5808980.281999, 612473.423 5808995.424999, 612333.856 5809647.731, 612307.316 5809781.446, 612267.099 5809852.803, 612308.221 5810040.995, 613920.397 5811079.478, 613947.16 5811129.3, 614022.726 5811154.456, 614058.436 5811260.36, 614194.037 5811331.972, 614307.176 5811360.06, 614343.842 5811323.238, 614443.449 5811363.03, 614526.199 5811059.031, 614417.83 5811057.603, 614787.296 5809648.422, 614772.062 5809583.246, 614981.93 5809245.35, 614811.885 5809138.271, 615063.452 5809100.954, 615215.476 5809029.413, 615469.441 5808883.282, 615569.846 5808829.522, 615577.239 5808806.242, 615392.964 5808736.873, 615306.34 5808662.171, 615335.445 5808290.588, 615312.192 5808290.397, 614890.582 5808077.956, 615018.854 5807799.895, 614837.326 5807688.363, 614435.698 5807646.847, 614126.351 5807661.841, 613555.813 5807814.801, 612826.66 5807964.828, 612830.113 5807856.315, 612694.674 5807839.658))'])
182+
160183

161184
if __name__ == '__main__':
162185
unittest.main()

0 commit comments

Comments
 (0)
Please sign in to comment.