[mssql] Fix removal of views

It was impossible to drop views as we were always using 'drop table' command. QGIS would even tell user that it was correctly removed even though nothing happened. There's some code duplication for ms sql layer info fetching, so I added "is view" detection to both places (gui widget, data items) even though it's currently only used in data items.
qgis · Oct 26, 2019 · 65fd7f2 · 65fd7f2
1 parent 67c0cfd
commit 65fd7f2
Show file tree

Hide file tree

Showing 6 changed files with 51 additions and 10 deletions.
diff --git a/src/providers/mssql/qgsmssqlconnection.cpp b/src/providers/mssql/qgsmssqlconnection.cpp
@@ -191,6 +191,33 @@ void QgsMssqlConnection::setInvalidGeometryHandlingDisabled( const QString &name
   settings.setValue( "/MSSQL/connections/" + name + "/disableInvalidGeometryHandling", disabled );
 }
 
+bool QgsMssqlConnection::dropView( 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 );
+  if ( !q.exec( QString( "DROP VIEW [%1].[%2]" ).arg( schema, table ) ) )
+  {
+    if ( errorMessage )
+      *errorMessage = q.lastError().text();
+    return false;
+  }
+
+  return true;
+}
+
 bool QgsMssqlConnection::dropTable( const QString &uri, QString *errorMessage )
 {
   QgsDataSourceUri dsUri( uri );

diff --git a/src/providers/mssql/qgsmssqlconnection.h b/src/providers/mssql/qgsmssqlconnection.h
@@ -126,6 +126,11 @@ class QgsMssqlConnection
      */
     static bool dropTable( const QString &uri, QString *errorMessage );
 
+    /**
+     * Drops the view referenced by \a uri.
+     */
+    static bool dropView( const QString &uri, QString *errorMessage );
+
     /**
      * Truncates the table referenced by \a uri.
      */

diff --git a/src/providers/mssql/qgsmssqldataitemguiprovider.cpp b/src/providers/mssql/qgsmssqldataitemguiprovider.cpp
@@ -89,21 +89,27 @@ bool QgsMssqlDataItemGuiProvider::deleteLayer( QgsLayerItem *item, QgsDataItemGu
   {
     QgsMssqlConnectionItem *connItem = qobject_cast<QgsMssqlConnectionItem *>( layerItem->parent() ? layerItem->parent()->parent() : nullptr );
     const QgsMssqlLayerProperty &layerInfo = layerItem->layerInfo();
+    QString typeName = layerInfo.isView ? tr( "View" ) : tr( "Table" );
 
-    if ( QMessageBox::question( nullptr, QObject::tr( "Delete Table" ),
+    if ( QMessageBox::question( nullptr, QObject::tr( "Delete %1" ).arg( typeName ),
                                 QObject::tr( "Are you sure you want to delete [%1].[%2]?" ).arg( layerInfo.schemaName, layerInfo.tableName ),
                                 QMessageBox::Yes | QMessageBox::No, QMessageBox::No ) != QMessageBox::Yes )
       return true;
 
     QString errCause;
-    bool res = QgsMssqlConnection::dropTable( layerItem->uri(), &errCause );
+    bool res;
+    if ( layerInfo.isView )
+      res = QgsMssqlConnection::dropView( layerItem->uri(), &errCause );
+    else
+      res = QgsMssqlConnection::dropTable( layerItem->uri(), &errCause );
+
     if ( !res )
     {
-      QMessageBox::warning( nullptr, tr( "Delete Table" ), errCause );
+      QMessageBox::warning( nullptr, tr( "Delete %1" ).arg( typeName ), errCause );
     }
     else
     {
-      QMessageBox::information( nullptr, tr( "Delete Table" ), tr( "Table deleted successfully." ) );
+      QMessageBox::information( nullptr, tr( "Delete %1" ).arg( typeName ), tr( "%1 deleted successfully." ).arg( typeName ) );
       if ( connItem )
         connItem->refresh();
     }

diff --git a/src/providers/mssql/qgsmssqldataitems.cpp b/src/providers/mssql/qgsmssqldataitems.cpp
@@ -143,16 +143,16 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
   QString query( QStringLiteral( "select " ) );
   if ( mUseGeometryColumns )
   {
-    query += QLatin1String( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type from geometry_columns" );
+    query += QLatin1String( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type, 0 from geometry_columns" );
   }
   else
   {
-    query += QLatin1String( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY' from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
+    query += QLatin1String( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY', case when sys.objects.type = 'V' then 1 else 0 end from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
   }
 
   if ( mAllowGeometrylessTables )
   {
-    query += QLatin1String( " union all select sys.schemas.name, sys.objects.name, null, null, 'NONE' from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
+    query += QLatin1String( " union all select sys.schemas.name, sys.objects.name, null, null, 'NONE', case when sys.objects.type = 'V' then 1 else 0 end from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
   }
 
   const bool disableInvalidGeometryHandling = QgsMssqlConnection::isInvalidGeometryHandlingDisabled( mName );
@@ -175,6 +175,7 @@ QVector<QgsDataItem *> QgsMssqlConnectionItem::createChildren()
       layer.geometryColName = q.value( 2 ).toString();
       layer.srid = q.value( 3 ).toString();
       layer.type = q.value( 4 ).toString();
+      layer.isView = q.value( 5 ).toBool();
       layer.pkCols = QStringList(); //TODO
       layer.isGeography = false;
 

diff --git a/src/providers/mssql/qgsmssqlsourceselect.cpp b/src/providers/mssql/qgsmssqlsourceselect.cpp
@@ -544,16 +544,16 @@ void QgsMssqlSourceSelect::btnConnect_clicked()
   QString query( QStringLiteral( "select " ) );
   if ( useGeometryColumns )
   {
-    query += QLatin1String( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type from geometry_columns" );
+    query += QLatin1String( "f_table_schema, f_table_name, f_geometry_column, srid, geometry_type, 0 from geometry_columns" );
   }
   else
   {
-    query += QLatin1String( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY' from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
+    query += QLatin1String( "sys.schemas.name, sys.objects.name, sys.columns.name, null, 'GEOMETRY', case when sys.objects.type = 'V' then 1 else 0 end from sys.columns join sys.types on sys.columns.system_type_id = sys.types.system_type_id and sys.columns.user_type_id = sys.types.user_type_id join sys.objects on sys.objects.object_id = sys.columns.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
   }
 
   if ( allowGeometrylessTables )
   {
-    query += QLatin1String( " union all select sys.schemas.name, sys.objects.name, null, null, 'NONE' from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
+    query += QLatin1String( " union all select sys.schemas.name, sys.objects.name, null, null, 'NONE', case when sys.objects.type = 'V' then 1 else 0 end from sys.objects join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id where not exists (select * from sys.columns sc1 join sys.types on sc1.system_type_id = sys.types.system_type_id where (sys.types.name = 'geometry' or sys.types.name = 'geography') and sys.objects.object_id = sc1.object_id) and (sys.objects.type = 'U' or sys.objects.type = 'V')" );
   }
 
   // issue the sql query
@@ -571,6 +571,7 @@ void QgsMssqlSourceSelect::btnConnect_clicked()
       layer.geometryColName = q.value( 2 ).toString();
       layer.srid = q.value( 3 ).toString();
       layer.type = q.value( 4 ).toString();
+      layer.isView = q.value( 5 ).toBool();
       layer.pkCols = QStringList(); //TODO
       layer.isGeography = false;
 

diff --git a/src/providers/mssql/qgsmssqltablemodel.h b/src/providers/mssql/qgsmssqltablemodel.h
@@ -34,6 +34,7 @@ struct QgsMssqlLayerProperty
   QString     srid;
   bool        isGeography;
   QString     sql;
+  bool        isView;
 };