[mssql] Add browser actions for deleting and truncating tables

Because we can no longer overwrite tables directly using drag and drop (refs #16805), add some explicit actions which allow dropping existing tables (after user confirmation, of course!)
qgis · Oct 8, 2018 · 302b449 · 302b449
1 parent 745495f
commit 302b449
Show file tree

Hide file tree

Showing 4 changed files with 131 additions and 1 deletion.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -18,8 +18,11 @@
 #include "qgsmssqlconnection.h"
 #include "qgslogger.h"
 #include "qgssettings.h"
+#include "qgsdatasourceuri.h"
 #include <QSqlDatabase>
 #include <QThread>
+#include <QSqlError>
+#include <QSqlQuery>
 
 int QgsMssqlConnection::sConnectionId = 0;
 
@@ -154,6 +157,67 @@ void QgsMssqlConnection::setInvalidGeometryHandlingDisabled( const QString &name
   settings.setValue( "/MSSQL/connections/" + name + "/disableInvalidGeometryHandling", disabled );
 }
 
+bool QgsMssqlConnection::dropTable( const QString &uri, QString *errorMessage )
+{
+  QgsDataSourceUri dsUri( uri );
+
+  // connect to database
+  QSqlDatabase db = getDatabase( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
+  const QString schema = dsUri.schema();
+  const QString table = dsUri.table();
+
+  if ( !openDatabase( db ) )
+  {
+    if ( errorMessage )
+      *errorMessage = db.lastError().text();
+    return false;
+  }
+
+  QSqlQuery q = QSqlQuery( db );
+  q.setForwardOnly( true );
+  const QString sql = QString( "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[%1].[%2]') AND type in (N'U')) DROP TABLE [%1].[%2]\n"
+                               "DELETE FROM geometry_columns WHERE f_table_schema = '%1' AND f_table_name = '%2'" )
+                      .arg( schema,
+                            table );
+  if ( !q.exec( sql ) )
+  {
+    if ( errorMessage )
+      *errorMessage = q.lastError().text();
+    return false;
+  }
+
+  return true;
+}
+
+bool QgsMssqlConnection::truncateTable( const QString &uri, QString *errorMessage )
+{
+  QgsDataSourceUri dsUri( uri );
+
+  // connect to database
+  QSqlDatabase db = getDatabase( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
+  const QString schema = dsUri.schema();
+  const QString table = dsUri.table();
+
+  if ( !openDatabase( db ) )
+  {
+    if ( errorMessage )
+      *errorMessage = db.lastError().text();
+    return false;
+  }
+
+  QSqlQuery q = QSqlQuery( db );
+  q.setForwardOnly( true );
+  const QString sql = QStringLiteral( "TRUNCATE TABLE [%1].[%2]" ).arg( schema, table );
+  if ( !q.exec( sql ) )
+  {
+    if ( errorMessage )
+      *errorMessage = q.lastError().text();
+    return false;
+  }
+
+  return true;
+}
+
 QString QgsMssqlConnection::dbConnectionName( const QString &name )
 {
   // Starting with Qt 5.11, sharing the same connection between threads is not allowed.

diff --git a/src/providers/mssql/qgsmssqlconnection.h b/src/providers/mssql/qgsmssqlconnection.h
@@ -118,6 +118,16 @@ class QgsMssqlConnection
      */
     static void setInvalidGeometryHandlingDisabled( const QString &name, bool disabled );
 
+    /**
+     * Drops the table referenced by \a uri.
+     */
+    static bool dropTable( const QString &uri, QString *errorMessage );
+
+    /**
+     * Truncates the table referenced by \a uri.
+     */
+    static bool truncateTable( const QString &uri, QString *errorMessage );
+
   private:
 
     /**

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -487,6 +487,60 @@ QgsMssqlLayerItem::QgsMssqlLayerItem( QgsDataItem *parent, const QString &name,
   setState( Populated );
 }
 
+QList<QAction *> QgsMssqlLayerItem::actions( QWidget *actionParent )
+{
+  QgsMssqlConnectionItem *connItem = qobject_cast<QgsMssqlConnectionItem *>( parent() ? parent()->parent() : nullptr );
+
+  QList<QAction *> lst;
+
+  // delete
+  QAction *actionDeleteLayer = new QAction( tr( "Delete Table" ), actionParent );
+  connect( actionDeleteLayer, &QAction::triggered, this, [ = ]
+  {
+    if ( QMessageBox::question( nullptr, QObject::tr( "Delete Table" ),
+                                QObject::tr( "Are you sure you want to delete [%1].[%2]?" ).arg( mLayerProperty.schemaName, mLayerProperty.tableName ),
+                                QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
+      return;
+
+    QString errCause;
+    bool res = QgsMssqlConnection::dropTable( mUri, &errCause );
+    if ( !res )
+    {
+      QMessageBox::warning( nullptr, tr( "Delete Table" ), errCause );
+    }
+    else
+    {
+      QMessageBox::information( nullptr, tr( "Delete Table" ), tr( "Table deleted successfully." ) );
+      if ( connItem )
+        connItem->refresh();
+    }
+  } );
+  lst.append( actionDeleteLayer );
+
+  // truncate
+  QAction *actionTruncateLayer = new QAction( tr( "Truncate Table" ), actionParent );
+  connect( actionTruncateLayer, &QAction::triggered, this, [ = ]
+  {
+    if ( QMessageBox::question( nullptr, QObject::tr( "Truncate Table" ),
+                                QObject::tr( "Are you sure you want to truncate [%1].[%2]?\n\nThis will delete all data within the table." ).arg( mLayerProperty.schemaName, mLayerProperty.tableName ),
+                                QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
+      return;
+
+    QString errCause;
+    bool res = QgsMssqlConnection::truncateTable( mUri, &errCause );
+    if ( !res )
+    {
+      QMessageBox::warning( nullptr, tr( "Truncate Table" ), errCause );
+    }
+    else
+    {
+      QMessageBox::information( nullptr, tr( "Truncate Table" ), tr( "Table truncated successfully." ) );
+    }
+  } );
+  lst.append( actionTruncateLayer );
+  return lst;
+}
+
 QgsMssqlLayerItem *QgsMssqlLayerItem::createClone()
 {
   return new QgsMssqlLayerItem( mParent, mName, mPath, mLayerType, mLayerProperty );

diff --git a/src/providers/mssql/qgsmssqldataitems.h b/src/providers/mssql/qgsmssqldataitems.h
@@ -126,7 +126,9 @@ class QgsMssqlLayerItem : public QgsLayerItem
 
   public:
     QgsMssqlLayerItem( QgsDataItem *parent, const QString &name, const QString &path, QgsLayerItem::LayerType layerType, const QgsMssqlLayerProperty &layerProperties );
-
+#ifdef HAVE_GUI
+    QList<QAction *> actions( QWidget *parent ) override;
+#endif
     QString createUri();
 
     QgsMssqlLayerItem *createClone();