Skip to content

Commit

Permalink
Implement spatial index capabilities
Browse files Browse the repository at this point in the history
  • Loading branch information
@troopa81 @nyalldawson
troopa81 authored and nyalldawson committed Jan 29, 2021
1 parent eb6e893 commit 9d60102
Show file tree
Hide file tree
Showing 7 changed files with 172 additions and 75 deletions.
74 changes: 74 additions & 0 deletions src/providers/oracle/qgsoracleconn.cpp
View file
Expand Up @@ -985,6 +985,80 @@ QList<QgsVectorDataProvider::NativeType> QgsOracleConn::nativeTypes()
<< QgsVectorDataProvider::NativeType( tr( "Date & Time" ), "TIMESTAMP(6)", QVariant::DateTime, 38, 38, 6, 6 );
}

QString QgsOracleConn::getSpatialIndexName( const QString &ownerName, const QString &tableName, const QString &geometryColumn, bool &isValid )
{
QString name;

QSqlQuery qry( mDatabase );
if ( exec( qry, QString( "SELECT i.index_name,i.domidx_opstatus"
" FROM all_indexes i"
" JOIN all_ind_columns c ON i.owner=c.index_owner AND i.index_name=c.index_name AND c.column_name=?"
" WHERE i.table_owner=? AND i.table_name=? AND i.ityp_owner='MDSYS' AND i.ityp_name='SPATIAL_INDEX'" ),
QVariantList() << geometryColumn << ownerName << tableName ) )
{
if ( qry.next() )
{
name = qry.value( 0 ).toString();
if ( qry.value( 1 ).toString() != "VALID" )
{
QgsMessageLog::logMessage( tr( "Invalid spatial index %1 on column %2.%3.%4 found - expect poor performance." )
.arg( name )
.arg( ownerName )
.arg( tableName )
.arg( geometryColumn ),
tr( "Oracle" ) );
isValid = false;
}
else
{
QgsDebugMsgLevel( QStringLiteral( "Valid spatial index %1 found" ).arg( name ), 2 );
isValid = true;
}
}
}
else
{
QgsMessageLog::logMessage( tr( "Probing for spatial index on column %1.%2.%3 failed [%4]" )
.arg( ownerName )
.arg( tableName )
.arg( geometryColumn )
.arg( qry.lastError().text() ),
tr( "Oracle" ) );

isValid = false;
}

return name;
}

QString QgsOracleConn::createSpatialIndex( const QString &ownerName, const QString &tableName, const QString &geometryColumn )
{
QSqlQuery qry( mDatabase );

int n = 0;
if ( exec( qry, QString( "SELECT coalesce(substr(max(index_name),10),'0') FROM all_indexes WHERE index_name LIKE 'QGIS_IDX_%' ESCAPE '#' ORDER BY index_name" ), QVariantList() ) &&
qry.next() )
{
n = qry.value( 0 ).toInt() + 1;
}

if ( !exec( qry, QString( "CREATE INDEX QGIS_IDX_%1 ON %2.%3(%4) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARALLEL" )
.arg( n, 10, 10, QChar( '0' ) )
.arg( quotedIdentifier( ownerName ) )
.arg( quotedIdentifier( tableName ) )
.arg( quotedIdentifier( geometryColumn ) ), QVariantList() ) )
{
QgsMessageLog::logMessage( tr( "Creation spatial index failed.\nSQL: %1\nError: %2" )
.arg( qry.lastQuery() )
.arg( qry.lastError().text() ),
tr( "Oracle" ) );
return QString();
}

return QString( "QGIS_IDX_%1" ).arg( n, 10, 10, QChar( '0' ) );
}


QgsPoolOracleConn::QgsPoolOracleConn( const QString &connInfo )
: mConn( QgsOracleConnPool::instance()->acquireConnection( connInfo ) )
{
Expand Down
18 changes: 18 additions & 0 deletions src/providers/oracle/qgsoracleconn.h
View file
Expand Up @@ -206,6 +206,24 @@ class QgsOracleConn : public QObject
*/
QList<QgsVectorDataProvider::NativeType> nativeTypes();

/**
* Returns spatial index name for column \a geometryColumn in table \a tableName from
* schema/user \a ownerName.
* Returns an empty string if there is no spatial index
* \a isValid is updated with TRUE if the returned index is valid
* \since QGIS 3.18
*/
QString getSpatialIndexName( const QString &ownerName, const QString &tableName, const QString &geometryColumn, bool &isValid );

/**
* Create a spatial index for for column \a geometryColumn in table \a tableName from
* schema/user \a ownerName.
* Returns created index name. An empty string is returned if the creation has failed.
* \note We assume that the sdo_geom_metadata table is already correctly populated before creating
* the index. If not, the index creation would failed.
*/
QString createSpatialIndex( const QString &ownerName, const QString &tableName, const QString &geometryColumn );

static const int sGeomTypeSelectLimit;

static QgsWkbTypes::Type wkbTypeFromDatabase( int gtype );
Expand Down
61 changes: 5 additions & 56 deletions src/providers/oracle/qgsoracleprovider.cpp
View file
Expand Up @@ -674,42 +674,7 @@ bool QgsOracleProvider::loadFields()
}

if ( !mGeometryColumn.isEmpty() )
{
if ( exec( qry, QString( "SELECT i.index_name,i.domidx_opstatus"
" FROM all_indexes i"
" JOIN all_ind_columns c ON i.owner=c.index_owner AND i.index_name=c.index_name AND c.column_name=?"
" WHERE i.table_owner=? AND i.table_name=? AND i.ityp_owner='MDSYS' AND i.ityp_name='SPATIAL_INDEX'" ),
QVariantList() << mGeometryColumn << mOwnerName << mTableName ) )
{
if ( qry.next() )
{
mSpatialIndexName = qry.value( 0 ).toString();
if ( qry.value( 1 ).toString() != "VALID" )
{
QgsMessageLog::logMessage( tr( "Invalid spatial index %1 on column %2.%3.%4 found - expect poor performance." )
.arg( mSpatialIndexName )
.arg( mOwnerName )
.arg( mTableName )
.arg( mGeometryColumn ),
tr( "Oracle" ) );
}
else
{
QgsDebugMsgLevel( QStringLiteral( "Valid spatial index %1 found" ).arg( mSpatialIndexName ), 2 );
mHasSpatialIndex = true;
}
}
}
else
{
QgsMessageLog::logMessage( tr( "Probing for spatial index on column %1.%2.%3 failed [%4]" )
.arg( mOwnerName )
.arg( mTableName )
.arg( mGeometryColumn )
.arg( qry.lastError().text() ),
tr( "Oracle" ) );
}
}
mSpatialIndexName = conn->getSpatialIndexName( mOwnerName, mTableName, mGeometryColumn, mHasSpatialIndex );

mEnabledCapabilities |= QgsVectorDataProvider::CreateSpatialIndex;
}
Expand Down Expand Up @@ -2787,27 +2752,11 @@ bool QgsOracleProvider::createSpatialIndex()

if ( !mHasSpatialIndex )
{
int n = 0;
if ( exec( qry, QString( "SELECT coalesce(substr(max(index_name),10),'0') FROM all_indexes WHERE index_name LIKE 'QGIS_IDX_%' ESCAPE '#' ORDER BY index_name" ), QVariantList() ) &&
qry.next() )
{
n = qry.value( 0 ).toInt() + 1;
}

if ( !exec( qry, QString( "CREATE INDEX QGIS_IDX_%1 ON %2.%3(%4) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARALLEL" )
.arg( n, 10, 10, QChar( '0' ) )
.arg( quotedIdentifier( mOwnerName ) )
.arg( quotedIdentifier( mTableName ) )
.arg( quotedIdentifier( mGeometryColumn ) ), QVariantList() ) )
{
QgsMessageLog::logMessage( tr( "Creation spatial index failed.\nSQL: %1\nError: %2" )
.arg( qry.lastQuery() )
.arg( qry.lastError().text() ),
tr( "Oracle" ) );
const QString spatialIndexName = conn->createSpatialIndex( mOwnerName, mTableName, mGeometryColumn );
if ( spatialIndexName.isEmpty() )
return false;
}

mSpatialIndexName = QString( "QGIS_IDX_%1" ).arg( n, 10, 10, QChar( '0' ) );
else
mSpatialIndexName = spatialIndexName;
}
else
{
Expand Down
50 changes: 50 additions & 0 deletions src/providers/oracle/qgsoracleproviderconnection.cpp
View file
Expand Up @@ -29,6 +29,7 @@ const QStringList CONFIGURATION_PARAMETERS
QStringLiteral( "geometryColumnsOnly" ),
QStringLiteral( "allowGeometrylessTables" ),
QStringLiteral( "disableInvalidGeometryHandling" ),
QStringLiteral( "onlyExistingTypes" ),
QStringLiteral( "saveUsername" ),
QStringLiteral( "savePassword" ),
};
Expand Down Expand Up @@ -370,3 +371,52 @@ void QgsOracleProviderConnection::renameVectorTable( const QString &schema, cons
executeSqlPrivate( QStringLiteral( "UPDATE user_sdo_geom_metadata SET TABLE_NAME = '%1' where TABLE_NAME = '%2'" )
.arg( newName, name ) );
}

void QgsOracleProviderConnection::createSpatialIndex( const QString &schema, const QString &name, const QgsOracleProviderConnection::SpatialIndexOptions &options ) const
{
checkCapability( Capability::CreateSpatialIndex );

QgsDataSourceUri dsUri( uri() );
QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
QgsOracleConn *conn = pconn.get();
if ( !conn )
{
throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );
}

const QString indexName = conn->createSpatialIndex( schema, name, options.geometryColumnName );
if ( indexName.isEmpty() )
throw QgsProviderConnectionException( QObject::tr( "Failed to create spatial index for %1.%2(%3)" ).arg( schema, name, options.geometryColumnName ) );
}

void QgsOracleProviderConnection::deleteSpatialIndex( const QString &schema, const QString &name, const QString &geometryColumn ) const
{
QgsDataSourceUri dsUri( uri() );
QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
QgsOracleConn *conn = pconn.get();
if ( !conn )
throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );

bool isValid;
QString indexName = conn->getSpatialIndexName( schema, name, geometryColumn, isValid );

if ( indexName.isEmpty() )
throw QgsProviderConnectionException( QObject::tr( "No spatial index exists for %1.%2(%3)" ).arg( schema, name, geometryColumn ) );

executeSqlPrivate( QStringLiteral( "DROP INDEX %1" ).arg( indexName ) );
}

bool QgsOracleProviderConnection::spatialIndexExists( const QString &schema, const QString &name, const QString &geometryColumn ) const
{
checkCapability( Capability::SpatialIndexExists );

QgsDataSourceUri dsUri( uri() );
QgsPoolOracleConn pconn( dsUri.connectionInfo( false ) );
QgsOracleConn *conn = pconn.get();
if ( !conn )
throw QgsProviderConnectionException( QObject::tr( "Connection failed: %1" ).arg( uri() ) );

bool isValid;
conn->getSpatialIndexName( schema, name, geometryColumn, isValid );
return isValid;
}
3 changes: 3 additions & 0 deletions src/providers/oracle/qgsoracleproviderconnection.h
View file
Expand Up @@ -62,6 +62,9 @@ class QgsOracleProviderConnection : public QgsAbstractDatabaseProviderConnection
void renameVectorTable( const QString &schema, const QString &name, const QString &newName ) const override;

QgsAbstractDatabaseProviderConnection::QueryResult execSql( const QString &sql, QgsFeedback *feedback = nullptr ) const override;
void createSpatialIndex( const QString &schema, const QString &name, const QgsAbstractDatabaseProviderConnection::SpatialIndexOptions &options = QgsAbstractDatabaseProviderConnection::SpatialIndexOptions() ) const override;
bool spatialIndexExists( const QString &schema, const QString &name, const QString &geometryColumn ) const override;
void deleteSpatialIndex( const QString &schema, const QString &name, const QString &geometryColumn ) const override;
QList<QgsAbstractDatabaseProviderConnection::TableProperty> tables( const QString &schema,
const TableFlags &flags = TableFlags() ) const override;
void store( const QString &name ) const override;
Expand Down
37 changes: 19 additions & 18 deletions tests/src/python/test_qgsproviderconnection_base.py
View file
Expand Up @@ -54,6 +54,7 @@ class TestPyQgsProviderConnectionBase():
myNewTable = 'myNewTable'
myVeryNewTable = 'myVeryNewTable'
myUtf8Table = 'myUtf8\U0001f604Table'
geometryColumnName = 'geom'

@classmethod
def setUpClass(cls):
Expand Down Expand Up @@ -324,38 +325,38 @@ def _test_operations(self, md, conn):

# Vacuum
if capabilities & QgsAbstractDatabaseProviderConnection.Vacuum:
conn.vacuum('myNewSchema', self.myNewTable)
conn.vacuum(schema, self.myNewTable)

# Spatial index
spatial_index_exists = False
# we don't initially know if a spatial index exists -- some formats may create them by default, others not
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
spatial_index_exists = conn.spatialIndexExists('myNewSchema', self.myNewTable, 'geom')
spatial_index_exists = conn.spatialIndexExists(schema, self.myNewTable, self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
if spatial_index_exists:
conn.deleteSpatialIndex('myNewSchema', self.myNewTable, 'geom')
conn.deleteSpatialIndex(schema, self.myNewTable, self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(conn.spatialIndexExists('myNewSchema', self.myNewTable, 'geom'))
self.assertFalse(conn.spatialIndexExists(schema, self.myNewTable, self.geometryColumnName))

if capabilities & (QgsAbstractDatabaseProviderConnection.CreateSpatialIndex | QgsAbstractDatabaseProviderConnection.SpatialIndexExists):
options = QgsAbstractDatabaseProviderConnection.SpatialIndexOptions()
options.geometryColumnName = 'geom'
options.geometryColumnName = self.geometryColumnName

if not conn.spatialIndexExists('myNewSchema', self.myNewTable, options.geometryColumnName):
conn.createSpatialIndex('myNewSchema', self.myNewTable, options)
if not conn.spatialIndexExists(schema, self.myNewTable, options.geometryColumnName):
conn.createSpatialIndex(schema, self.myNewTable, options)

self.assertTrue(conn.spatialIndexExists('myNewSchema', 'myNewTable', 'geom'))
self.assertTrue(conn.spatialIndexExists(schema, self.myNewTable, self.geometryColumnName))

# now we know for certain a spatial index exists, let's retry dropping it
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
conn.deleteSpatialIndex('myNewSchema', self.myNewTable, 'geom')
conn.deleteSpatialIndex(schema, self.myNewTable, self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(conn.spatialIndexExists('myNewSchema', self.myNewTable, 'geom'))
self.assertFalse(conn.spatialIndexExists(schema, self.myNewTable, self.geometryColumnName))

if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema (should fail)
with self.assertRaises(QgsProviderConnectionException) as ex:
conn.dropSchema('myNewSchema')
conn.dropSchema(schema)

# Check some column types operations
table = self._table_by_name(conn.tables(schema), self.myNewTable)
Expand All @@ -380,21 +381,21 @@ def _test_operations(self, md, conn):
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)

# Check fields
fields = conn.fields('myNewSchema', self.myNewTable)
fields = conn.fields(schema, self.myNewTable)
for f in ['string_t', 'long_t', 'double_t', 'integer_t', 'date_t', 'datetime_t', 'time_t']:
self.assertTrue(f in fields.names())

if capabilities & QgsAbstractDatabaseProviderConnection.AddField:
field = QgsField('short_lived_field', QVariant.Int, 'integer')
conn.addField(field, 'myNewSchema', self.myNewTable)
fields = conn.fields('myNewSchema', self.myNewTable)
conn.addField(field, schema, self.myNewTable)
fields = conn.fields(schema, self.myNewTable)
self.assertTrue('short_lived_field' in fields.names())

if capabilities & QgsAbstractDatabaseProviderConnection.DeleteField:
conn.deleteField('short_lived_field', 'myNewSchema', self.myNewTable)
conn.deleteField('short_lived_field', schema, self.myNewTable)
# This fails on Travis for spatialite, for no particular reason
if self.providerKey == 'spatialite' and not os.environ.get('TRAVIS', False):
fields = conn.fields('myNewSchema', self.myNewTable)
fields = conn.fields(schema, self.myNewTable)
self.assertFalse('short_lived_field' in fields.names())

# Drop table
Expand All @@ -405,8 +406,8 @@ def _test_operations(self, md, conn):

if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema
conn.dropSchema('myNewSchema')
self.assertFalse('myNewSchema' in conn.schemas())
conn.dropSchema(schema)
self.assertFalse(schema in conn.schemas())

conns = md.connections()
self.assertTrue(isinstance(list(conns.values())[0], QgsAbstractDatabaseProviderConnection))
Expand Down
4 changes: 3 additions & 1 deletion tests/src/python/test_qgsproviderconnection_oracle.py
View file
Expand Up @@ -34,14 +34,16 @@ class TestPyQgsProviderConnectionOracle(unittest.TestCase, TestPyQgsProviderConn

# there is no service for oracle provider test so we need to save user and password
# to keep them when storing/loading connections in parent class _test_save_load method
configuration = {"saveUsername": True, "savePassword": True, "geometryColumnsOnly": True}
configuration = {"saveUsername": True, "savePassword": True, "geometryColumnsOnly": True,
"onlyExistingTypes": True}

defaultSchema = 'QGIS'

# need to override this because tables with geometries need to be uppercase
myNewTable = 'MYNEWTABLE'
myVeryNewTable = 'MYVERYNEWTABLE'
myUtf8Table = 'MYUTF8\U0001F604TABLE'
geometryColumnName = 'GEOM'

@classmethod
def setUpClass(cls):
Expand Down

0 comments on commit 9d60102

Please sign in to comment.