Add SpatialIndexExists and DeleteSpatialIndex capabilities for postgr…

…es connection
qgis · Mar 17, 2020 · 066cc10 · 066cc10
1 parent 4edc7d1
commit 066cc10
Show file tree

Hide file tree

Showing 2 changed files with 57 additions and 1 deletion.
diff --git a/src/providers/postgres/qgspostgresproviderconnection.cpp b/src/providers/postgres/qgspostgresproviderconnection.cpp
@@ -63,7 +63,9 @@ void QgsPostgresProviderConnection::setDefaultCapabilities()
     Capability::Schemas,
     Capability::Spatial,
     Capability::TableExists,
-    Capability::CreateSpatialIndex
+    Capability::CreateSpatialIndex,
+    Capability::SpatialIndexExists,
+    Capability::DeleteSpatialIndex
   };
 }
 
@@ -338,6 +340,58 @@ void QgsPostgresProviderConnection::createSpatialIndex( const QString &schema, c
                     QgsPostgresConn::quotedIdentifier( options.geometryColumnName ) ) );
 }
 
+bool QgsPostgresProviderConnection::spatialIndexExists( const QString &schema, const QString &name, const QString &geometryColumn ) const
+{
+  checkCapability( Capability::SpatialIndexExists );
+
+  const QList<QVariantList> res = executeSql( QStringLiteral( R"""(SELECT COUNT(*)
+                                                              FROM pg_class t, pg_class i, pg_namespace ns, pg_index ix, pg_attribute a
+                                                              WHERE
+                                                                  t.oid=ix.indrelid
+                                                                  AND t.relnamespace=ns.oid
+                                                                  AND i.oid=ix.indexrelid
+                                                                  AND a.attrelid=t.oid
+                                                                  AND a.attnum=ANY(ix.indkey)
+                                                                  AND t.relkind='r'
+                                                                  AND ns.nspname=%1
+                                                                  AND t.relname=%2
+                                                                  AND a.attname=%3;
+                                                              )""" ).arg(
+                                    QgsPostgresConn::quotedValue( schema ),
+                                    QgsPostgresConn::quotedValue( name ),
+                                    QgsPostgresConn::quotedValue( geometryColumn ) ) );
+  return !res.isEmpty() && !res.at( 0 ).isEmpty() && res.at( 0 ).at( 0 ).toBool();
+}
+
+void QgsPostgresProviderConnection::deleteSpatialIndex( const QString &schema, const QString &name, const QString &geometryColumn ) const
+{
+  checkCapability( Capability::DeleteSpatialIndex );
+
+  const QList<QVariantList> res = executeSql( QStringLiteral( R"""(SELECT i.relname
+                                                                FROM pg_class t, pg_class i, pg_namespace ns, pg_index ix, pg_attribute a
+                                                                WHERE
+                                                                    t.oid=ix.indrelid
+                                                                    AND t.relnamespace=ns.oid
+                                                                    AND i.oid=ix.indexrelid
+                                                                    AND a.attrelid=t.oid
+                                                                    AND a.attnum=ANY(ix.indkey)
+                                                                    AND t.relkind='r'
+                                                                    AND ns.nspname=%1
+                                                                    AND t.relname=%2
+                                                                    AND a.attname=%3;
+                                                                )""" ).arg(
+                                    QgsPostgresConn::quotedValue( schema ),
+                                    QgsPostgresConn::quotedValue( name ),
+                                    QgsPostgresConn::quotedValue( geometryColumn ) ) );
+  if ( res.isEmpty() )
+    throw QgsProviderConnectionException( QObject::tr( "No spatial index exists for %1.%2" ).arg( schema, name ) );
+
+  const QString indexName = res.at( 0 ).at( 0 ).toString();
+
+  executeSql( QStringLiteral( "DROP INDEX %1.%2" ).arg( QgsPostgresConn::quotedIdentifier( schema ),
+              QgsPostgresConn::quotedIdentifier( indexName ) ) );
+}
+
 QList<QgsPostgresProviderConnection::TableProperty> QgsPostgresProviderConnection::tables( const QString &schema, const TableFlags &flags ) const
 {
   checkCapability( Capability::Tables );

diff --git a/src/providers/postgres/qgspostgresproviderconnection.h b/src/providers/postgres/qgspostgresproviderconnection.h
@@ -48,6 +48,8 @@ class QgsPostgresProviderConnection : public QgsAbstractDatabaseProviderConnecti
     QList<QVariantList> executeSql( const QString &sql ) const override;
     void vacuum( const QString &schema, const QString &name ) 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 = nullptr ) const override;
     QStringList schemas( ) const override;