Skip to content

Commit 791eb91

Browse files
nyalldawsonnyalldawson
committedDec 1, 2017
Fix OGR provider cannot create attribute or spatial indexes for
GeoPackage/SQLite layers Previously this capability was only exposed for shapefiles, but was available in the spatialite provider. We don't use that for GeoPackages, so I've ported the functionality across to the OGR provider for these data sources. Includes unit tests
·
ltr-3_40final-3_0_0
1 parent 31c79da commit 791eb91

File tree

3 files changed

+146
-19
lines changed

3 files changed

+146
-19
lines changed
 

‎src/providers/ogr/qgsogrprovider.cpp

Lines changed: 58 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1966,36 +1966,70 @@ bool QgsOgrProvider::createSpatialIndex()
19661966
if ( !doInitialActionsForEdition() )
19671967
return false;
19681968

1969-
if ( mGDALDriverName != QLatin1String( "ESRI Shapefile" ) )
1970-
return false;
1971-
19721969
QByteArray layerName = mOgrOrigLayer->name();
1970+
if ( mGDALDriverName == QLatin1String( "ESRI Shapefile" ) )
1971+
{
1972+
QByteArray sql = QByteArray( "CREATE SPATIAL INDEX ON " ) + quotedIdentifier( layerName ); // quote the layer name so spaces are handled
1973+
QgsDebugMsg( QString( "SQL: %1" ).arg( QString::fromUtf8( sql ) ) );
1974+
mOgrOrigLayer->ExecuteSQLNoReturn( sql );
19731975

1974-
QByteArray sql = QByteArray( "CREATE SPATIAL INDEX ON " ) + quotedIdentifier( layerName ); // quote the layer name so spaces are handled
1975-
QgsDebugMsg( QString( "SQL: %1" ).arg( QString::fromUtf8( sql ) ) );
1976-
mOgrOrigLayer->ExecuteSQLNoReturn( sql );
1976+
QFileInfo fi( mFilePath ); // to get the base name
1977+
//find out, if the .qix file is there
1978+
return QFileInfo::exists( fi.path().append( '/' ).append( fi.completeBaseName() ).append( ".qix" ) );
1979+
}
1980+
else if ( mGDALDriverName == QLatin1String( "GPKG" ) ||
1981+
mGDALDriverName == QLatin1String( "SQLite" ) )
1982+
{
1983+
QMutex *mutex = nullptr;
1984+
OGRLayerH layer = mOgrOrigLayer->getHandleAndMutex( mutex );
1985+
QMutexLocker locker( mutex );
1986+
1987+
QByteArray sql = QByteArray( "SELECT CreateSpatialIndex(" + quotedIdentifier( layerName ) + ","
1988+
+ quotedIdentifier( OGR_L_GetGeometryColumn( layer ) ) + ") " ); // quote the layer name so spaces are handled
1989+
mOgrOrigLayer->ExecuteSQLNoReturn( sql );
1990+
return true;
1991+
}
1992+
return false;
1993+
}
19771994

1978-
QFileInfo fi( mFilePath ); // to get the base name
1979-
//find out, if the .qix file is there
1980-
QFile indexfile( fi.path().append( '/' ).append( fi.completeBaseName() ).append( ".qix" ) );
1981-
return indexfile.exists();
1995+
QString createIndexName( QString tableName, QString field )
1996+
{
1997+
QRegularExpression safeExp( QStringLiteral( "[^a-zA-Z0-9]" ) );
1998+
tableName.replace( safeExp, QStringLiteral( "_" ) );
1999+
field.replace( safeExp, QStringLiteral( "_" ) );
2000+
return tableName + "_" + field + "_idx";
19822001
}
19832002

19842003
bool QgsOgrProvider::createAttributeIndex( int field )
19852004
{
2005+
if ( field < 0 || field >= mAttributeFields.count() )
2006+
return false;
2007+
19862008
if ( !doInitialActionsForEdition() )
19872009
return false;
19882010

19892011
QByteArray quotedLayerName = quotedIdentifier( mOgrOrigLayer->name() );
1990-
QByteArray dropSql = "DROP INDEX ON " + quotedLayerName;
1991-
mOgrOrigLayer->ExecuteSQLNoReturn( dropSql );
1992-
QByteArray createSql = "CREATE INDEX ON " + quotedLayerName + " USING " + textEncoding()->fromUnicode( fields().at( field ).name() );
1993-
mOgrOrigLayer->ExecuteSQLNoReturn( createSql );
2012+
if ( mGDALDriverName == QLatin1String( "GPKG" ) ||
2013+
mGDALDriverName == QLatin1String( "SQLite" ) )
2014+
{
2015+
QString indexName = createIndexName( mOgrOrigLayer->name(), fields().at( field ).name() );
2016+
QByteArray createSql = "CREATE INDEX IF NOT EXISTS " + textEncoding()->fromUnicode( indexName ) + " ON " + quotedLayerName + " (" + textEncoding()->fromUnicode( fields().at( field ).name() ) + ")";
2017+
mOgrOrigLayer->ExecuteSQLNoReturn( createSql );
2018+
return true;
2019+
}
2020+
else
2021+
{
2022+
QByteArray dropSql = "DROP INDEX ON " + quotedLayerName;
2023+
mOgrOrigLayer->ExecuteSQLNoReturn( dropSql );
2024+
QByteArray createSql = "CREATE INDEX ON " + quotedLayerName + " USING " + textEncoding()->fromUnicode( fields().at( field ).name() );
2025+
mOgrOrigLayer->ExecuteSQLNoReturn( createSql );
19942026

1995-
QFileInfo fi( mFilePath ); // to get the base name
1996-
//find out, if the .idm file is there
1997-
QFile indexfile( fi.path().append( '/' ).append( fi.completeBaseName() ).append( ".idm" ) );
1998-
return indexfile.exists();
2027+
QFileInfo fi( mFilePath ); // to get the base name
2028+
//find out, if the .idm/.ind file is there
2029+
QString idmFile( fi.path().append( '/' ).append( fi.completeBaseName() ).append( ".idm" ) );
2030+
QString indFile( fi.path().append( '/' ).append( fi.completeBaseName() ).append( ".ind" ) );
2031+
return QFile::exists( idmFile ) || QFile::exists( indFile );
2032+
}
19992033
}
20002034

20012035
bool QgsOgrProvider::deleteFeatures( const QgsFeatureIds &id )
@@ -2241,6 +2275,12 @@ void QgsOgrProvider::computeCapabilities()
22412275
ability &= ~( AddAttributes | DeleteFeatures );
22422276
}
22432277
}
2278+
else if ( mGDALDriverName == QLatin1String( "GPKG" ) ||
2279+
mGDALDriverName == QLatin1String( "SQLite" ) )
2280+
{
2281+
ability |= CreateSpatialIndex;
2282+
ability |= CreateAttributeIndex;
2283+
}
22442284

22452285
/* Curve geometries are available in some drivers starting with GDAL 2.0 */
22462286
if ( mOgrLayer->TestCapability( "CurveGeometries" ) )

‎tests/src/python/test_provider_ogr_gpkg.py

Lines changed: 60 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,11 @@
3434
QgsPointXY,
3535
QgsProject,
3636
QgsWkbTypes,
37-
QgsDataProvider)
37+
QgsDataProvider,
38+
QgsVectorDataProvider)
3839
from qgis.PyQt.QtCore import QCoreApplication, QVariant
3940
from qgis.testing import start_app, unittest
41+
from qgis.utils import spatialite_connect
4042

4143

4244
def GDAL_COMPUTE_VERSION(maj, min, rev):
@@ -771,6 +773,63 @@ def test_SplitFeature(self):
771773
self.assertEqual([f for f in layer.getFeatures()][0].geometry().asWkt(), 'Polygon ((0.5 0, 0.5 1, 1 1, 1 0, 0.5 0))')
772774
self.assertEqual([f for f in layer.getFeatures()][1].geometry().asWkt(), 'Polygon ((0.5 1, 0.5 0, 0 0, 0 1, 0.5 1))')
773775

776+
def testCreateAttributeIndex(self):
777+
tmpfile = os.path.join(self.basetestpath, 'testGeopackageAttributeIndex.gpkg')
778+
ds = ogr.GetDriverByName('GPKG').CreateDataSource(tmpfile)
779+
lyr = ds.CreateLayer('test', geom_type=ogr.wkbPolygon)
780+
lyr.CreateField(ogr.FieldDefn('str_field', ogr.OFTString))
781+
lyr.CreateField(ogr.FieldDefn('str_field2', ogr.OFTString))
782+
f = None
783+
ds = None
784+
785+
vl = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test", 'test', u'ogr')
786+
self.assertTrue(vl.isValid())
787+
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateAttributeIndex)
788+
self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
789+
self.assertFalse(vl.dataProvider().createAttributeIndex(100))
790+
self.assertTrue(vl.dataProvider().createAttributeIndex(1))
791+
792+
con = spatialite_connect(tmpfile, isolation_level=None)
793+
cur = con.cursor()
794+
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test'")
795+
res = [row for row in rs]
796+
self.assertEqual(len(res), 1)
797+
index_name = res[0][1]
798+
rs = cur.execute("PRAGMA index_info({})".format(index_name))
799+
res = [row for row in rs]
800+
self.assertEqual(len(res), 1)
801+
self.assertEqual(res[0][2], 'str_field')
802+
803+
# second index
804+
self.assertTrue(vl.dataProvider().createAttributeIndex(2))
805+
rs = cur.execute("SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='test'")
806+
res = [row for row in rs]
807+
self.assertEqual(len(res), 2)
808+
indexed_columns = []
809+
for row in res:
810+
index_name = row[1]
811+
rs = cur.execute("PRAGMA index_info({})".format(index_name))
812+
res = [row for row in rs]
813+
self.assertEqual(len(res), 1)
814+
indexed_columns.append(res[0][2])
815+
816+
self.assertCountEqual(indexed_columns, ['str_field', 'str_field2'])
817+
con.close()
818+
819+
def testCreateSpatialIndex(self):
820+
tmpfile = os.path.join(self.basetestpath, 'testGeopackageSpatialIndex.gpkg')
821+
ds = ogr.GetDriverByName('GPKG').CreateDataSource(tmpfile)
822+
lyr = ds.CreateLayer('test', geom_type=ogr.wkbPolygon)
823+
lyr.CreateField(ogr.FieldDefn('str_field', ogr.OFTString))
824+
lyr.CreateField(ogr.FieldDefn('str_field2', ogr.OFTString))
825+
f = None
826+
ds = None
827+
828+
vl = QgsVectorLayer(u'{}'.format(tmpfile) + "|layername=" + "test", 'test', u'ogr')
829+
self.assertTrue(vl.isValid())
830+
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateSpatialIndex)
831+
self.assertTrue(vl.dataProvider().createSpatialIndex())
832+
774833

775834
if __name__ == '__main__':
776835
unittest.main()

‎tests/src/python/test_provider_shapefile.py

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -584,6 +584,34 @@ def testOpenWithFilter(self):
584584
# force close of data provider
585585
vl.setDataSource('', 'test', 'ogr')
586586

587+
def testCreateAttributeIndex(self):
588+
tmpdir = tempfile.mkdtemp()
589+
self.dirs_to_cleanup.append(tmpdir)
590+
srcpath = os.path.join(TEST_DATA_DIR, 'provider')
591+
for file in glob.glob(os.path.join(srcpath, 'shapefile.*')):
592+
shutil.copy(os.path.join(srcpath, file), tmpdir)
593+
datasource = os.path.join(tmpdir, 'shapefile.shp')
594+
595+
vl = QgsVectorLayer('{}|layerid=0'.format(datasource), 'test', 'ogr')
596+
self.assertTrue(vl.isValid())
597+
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateAttributeIndex)
598+
self.assertFalse(vl.dataProvider().createAttributeIndex(-1))
599+
self.assertFalse(vl.dataProvider().createAttributeIndex(100))
600+
self.assertTrue(vl.dataProvider().createAttributeIndex(1))
601+
602+
def testCreateSpatialIndex(self):
603+
tmpdir = tempfile.mkdtemp()
604+
self.dirs_to_cleanup.append(tmpdir)
605+
srcpath = os.path.join(TEST_DATA_DIR, 'provider')
606+
for file in glob.glob(os.path.join(srcpath, 'shapefile.*')):
607+
shutil.copy(os.path.join(srcpath, file), tmpdir)
608+
datasource = os.path.join(tmpdir, 'shapefile.shp')
609+
610+
vl = QgsVectorLayer('{}|layerid=0'.format(datasource), 'test', 'ogr')
611+
self.assertTrue(vl.isValid())
612+
self.assertTrue(vl.dataProvider().capabilities() & QgsVectorDataProvider.CreateSpatialIndex)
613+
self.assertTrue(vl.dataProvider().createSpatialIndex())
614+
587615

588616
if __name__ == '__main__':
589617
unittest.main()

0 commit comments

Comments
 (0)
Please sign in to comment.