[FEATURE] Delete SpatiaLite tables from browser

qgis · Nov 17, 2011 · 4f9fa31 · 4f9fa31
1 parent 57ea79f
commit 4f9fa31
Show file tree

Hide file tree

Showing 4 changed files with 100 additions and 7 deletions.
diff --git a/src/providers/spatialite/qgsspatialitedataitems.cpp b/src/providers/spatialite/qgsspatialitedataitems.cpp
@@ -12,6 +12,43 @@
 #include <QMessageBox>
 #include <QSettings>
 
+QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QString& errCause );
+
+QgsSLLayerItem::QgsSLLayerItem( QgsDataItem* parent, QString name, QString path, QString uri, LayerType layerType )
+    : QgsLayerItem( parent, name, path, uri, layerType, "spatialite" )
+{
+  mPopulated = true; // no children are expected
+}
+
+QList<QAction*> QgsSLLayerItem::actions()
+{
+  QList<QAction*> lst;
+
+  QAction* actionDeleteLayer = new QAction( tr( "Delete layer" ), this );
+  connect( actionDeleteLayer, SIGNAL( triggered() ), this, SLOT( deleteLayer() ) );
+  lst.append( actionDeleteLayer );
+
+  return lst;
+}
+
+void QgsSLLayerItem::deleteLayer()
+{
+  QgsDataSourceURI uri( mUri );
+  QString errCause;
+  bool res = ::deleteLayer( uri.database(), uri.table(), errCause );
+  if ( !res )
+  {
+    QMessageBox::warning( 0, tr( "Delete layer" ), errCause );
+  }
+  else
+  {
+    QMessageBox::information( 0, tr( "Delete layer" ), tr( "Layer deleted successfully." ) );
+    mParent->refresh();
+  }
+}
+
+// ------
+
 QgsSLConnectionItem::QgsSLConnectionItem( QgsDataItem* parent, QString name, QString path )
     : QgsDataCollectionItem( parent, name, path )
 {

diff --git a/src/providers/spatialite/qgsspatialitedataitems.h b/src/providers/spatialite/qgsspatialitedataitems.h
@@ -5,12 +5,14 @@
 
 class QgsSLLayerItem : public QgsLayerItem
 {
+    Q_OBJECT
   public:
-    QgsSLLayerItem( QgsDataItem* parent, QString name, QString path, QString uri, LayerType layerType )
-        : QgsLayerItem( parent, name, path, uri, layerType, "spatialite" )
-    {
-      mPopulated = true; // no children are expected
-    }
+    QgsSLLayerItem( QgsDataItem* parent, QString name, QString path, QString uri, LayerType layerType );
+
+    QList<QAction*> actions();
+
+  public slots:
+    void deleteLayer();
 };
 
 class QgsSLConnectionItem : public QgsDataCollectionItem

diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -4988,3 +4988,56 @@ QGISEXTERN bool createDb( const QString& dbPath, QString& errCause )
 
   return init_res;
 }
+
+// -------------
+
+QGISEXTERN bool deleteLayer( const QString& dbPath, const QString& tableName, QString& errCause )
+{
+  QgsDebugMsg( "deleting layer " + tableName );
+
+  spatialite_init( 0 );
+  QgsSpatiaLiteProvider::SqliteHandles* hndl = QgsSpatiaLiteProvider::SqliteHandles::openDb( dbPath );
+  if ( !hndl )
+  {
+    errCause = QObject::tr( "Connection to database failed" );
+    return false;
+  }
+  sqlite3* sqlite_handle = hndl->handle();
+
+  // drop the table
+
+  QString sql = QString( "DROP TABLE " ) + QgsSpatiaLiteProvider::quotedIdentifier( tableName );
+  QgsDebugMsg( sql );
+  char *errMsg = NULL;
+  int ret = sqlite3_exec( sqlite_handle, sql.toUtf8().constData(), NULL, NULL, &errMsg );
+  if ( ret != SQLITE_OK )
+  {
+    errCause = QObject::tr( "Unable to delete table %1:\n" ).arg( tableName );
+    errCause += QString::fromUtf8( errMsg );
+    sqlite3_free( errMsg );
+    QgsSpatiaLiteProvider::SqliteHandles::closeDb( hndl );
+    return false;
+  }
+
+  // remove table from geometry columns
+  sql = QString( "DELETE FROM geometry_columns WHERE f_table_name = %1" )
+        .arg( QgsSpatiaLiteProvider::quotedValue( tableName ) );
+  ret = sqlite3_exec( sqlite_handle, sql.toUtf8().constData(), NULL, NULL, NULL );
+  if ( ret != SQLITE_OK )
+  {
+    QgsDebugMsg( "sqlite error: " + QString::fromUtf8( sqlite3_errmsg( sqlite_handle ) ) );
+  }
+
+  // TODO: remove spatial indexes?
+
+  // run VACUUM to free unused space and compact the database
+  ret = sqlite3_exec( sqlite_handle, "VACUUM", NULL, NULL, &errMsg );
+  if ( ret != SQLITE_OK )
+  {
+    QgsDebugMsg( "Failed to run VACUUM after deleting table on database " + dbPath );
+  }
+
+  QgsSpatiaLiteProvider::SqliteHandles::closeDb( hndl );
+
+  return true;
+}
diff --git a/src/providers/spatialite/qgsspatialiteprovider.h b/src/providers/spatialite/qgsspatialiteprovider.h
@@ -390,8 +390,6 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
     */
     //void sqliteOpen();
     void closeDb();
-    static QString quotedIdentifier( QString id );
-    static QString quotedValue( QString value );
     bool checkLayerType();
     bool getGeometryDetails();
     bool getTableGeometryDetails();
@@ -445,6 +443,9 @@ class QgsSpatiaLiteProvider: public QgsVectorDataProvider
     };
 
   public:
+    static QString quotedIdentifier( QString id );
+    static QString quotedValue( QString value );
+
     class SqliteHandles
     {
         //