[mssql] Add layer styles tests, minor fixes for layer style handling

qgis · Jan 24, 2022 · 9515695 · 9515695
1 parent 39f3040
commit 9515695
Show file tree

Hide file tree

Showing 3 changed files with 145 additions and 4 deletions.
diff --git a/src/providers/mssql/qgsmssqlprovider.cpp b/src/providers/mssql/qgsmssqlprovider.cpp
@@ -2661,6 +2661,7 @@ bool QgsMssqlProviderMetadata::saveStyle( const QString &uri,
 
 QString QgsMssqlProviderMetadata::loadStyle( const QString &uri, QString &errCause )
 {
+  errCause.clear();
   const QgsDataSourceUri dsUri( uri );
   // connect to database
   std::shared_ptr<QgsMssqlDatabase> db = QgsMssqlDatabase::connectDb( dsUri.service(), dsUri.host(), dsUri.database(), dsUri.username(), dsUri.password() );
@@ -2669,11 +2670,27 @@ QString QgsMssqlProviderMetadata::loadStyle( const QString &uri, QString &errCau
   {
     QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
     QgsDebugMsg( db->errorText() );
+    errCause = tr( "Cannot connect to database: %1" ).arg( db->errorText() );
     return QString();
   }
 
   QSqlQuery query = QSqlQuery( db->db() );
   query.setForwardOnly( true );
+  if ( !query.exec( QStringLiteral( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= N'layer_styles'" ) ) )
+  {
+    errCause = tr( "Could not check if layer_styles table exists: %1" ).arg( query.lastError().text() );
+    return QString();
+  }
+  if ( query.isActive() && query.next() && query.value( 0 ).toInt() == 0 )
+  {
+    // no layer_styles table
+    errCause = tr( "Style does not exist" );
+    query.finish();
+    return QString();
+  }
+
+  query.clear();
+  query.setForwardOnly( true );
 
   const QString selectQmlQuery = QString( "SELECT top 1 styleQML"
                                           " FROM layer_styles"
@@ -2733,7 +2750,7 @@ int QgsMssqlProviderMetadata::listStyles( const QString &uri,
   }
   if ( query.isActive() && query.next() && query.value( 0 ).toInt() == 0 )
   {
-    QgsDebugMsg( QObject::tr( "No styles available on DB, or there is an error connecting to the database." ) );
+    QgsDebugMsgLevel( QObject::tr( "No styles available on DB" ), 2 );
     return -1;
   }
 
@@ -2802,12 +2819,29 @@ QString QgsMssqlProviderMetadata::getStyleById( const QString &uri, const QStrin
   {
     QgsDebugMsg( QStringLiteral( "Error connecting to database" ) );
     QgsDebugMsg( db->errorText() );
+    errCause = tr( "Cannot connect to database: %1" ).arg( db->errorText() );
     return QString();
   }
 
   QSqlQuery query = QSqlQuery( db->db() );
   query.setForwardOnly( true );
 
+  if ( !query.exec( QStringLiteral( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= N'layer_styles'" ) ) )
+  {
+    errCause = tr( "Could not check if layer_styles table exists: %1" ).arg( query.lastError().text() );
+    return QString();
+  }
+  if ( query.isActive() && query.next() && query.value( 0 ).toInt() == 0 )
+  {
+    // no layer_styles table
+    errCause = tr( "Style does not exist" );
+    query.finish();
+    return QString();
+  }
+
+  query.clear();
+  query.setForwardOnly( true );
+
   QString style;
   const QString selectQmlQuery = QStringLiteral( "SELECT styleQml FROM layer_styles WHERE id=%1" ).arg( QgsMssqlProvider::quotedValue( styleId ) );
   const bool queryOk = query.exec( selectQmlQuery );
@@ -2817,7 +2851,11 @@ QString QgsMssqlProviderMetadata::getStyleById( const QString &uri, const QStrin
     errCause = query.lastError().text();
     return QString();
   }
-  while ( query.next() )
+  if ( !query.next() )
+  {
+    errCause = tr( "Style does not exist" );
+  }
+  else
   {
     style = query.value( 0 ).toString();
   }

diff --git a/tests/src/python/test_provider_mssql.py b/tests/src/python/test_provider_mssql.py
@@ -32,7 +32,7 @@
                        QgsCoordinateReferenceSystem,
                        QgsDataProvider)
 
-from qgis.PyQt.QtCore import QDate, QTime, QDateTime, QVariant
+from qgis.PyQt.QtCore import QDate, QTime, QDateTime, QVariant, QDir
 from utilities import unitTestDataPath
 from qgis.testing import start_app, unittest
 from providertestbase import ProviderTestCase
@@ -398,6 +398,109 @@ def testCurveGeometries(self):
             self.assertEqual(result_geoms, [t])
             self.execSQLCommand('DROP TABLE IF EXISTS [qgis_test].[new_table_curvegeom_{}]'.format(str(idx)))
 
+    def testStyle(self):
+        self.execSQLCommand('DROP TABLE IF EXISTS layer_styles')
+
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', 'not valid', '')
+        self.assertFalse(res)
+        self.assertTrue(err)
+
+        vl = self.getSource()
+        self.assertTrue(vl.isValid())
+        self.assertTrue(
+            vl.dataProvider().isSaveAndLoadStyleToDatabaseSupported())
+
+        # table layer_styles does not exist
+
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), '')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'a style')
+        self.assertFalse(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, -1)
+        self.assertEqual(idlist, [])
+        self.assertEqual(namelist, [])
+        self.assertEqual(desclist, [])
+        self.assertFalse(errmsg)
+
+        qml, errmsg = vl.getStyleFromDatabase("1")
+        self.assertFalse(qml)
+        self.assertTrue(errmsg)
+
+        mFilePath = QDir.toNativeSeparators(
+            '%s/symbol_layer/%s.qml' % (unitTestDataPath(), "singleSymbol"))
+        status = vl.loadNamedStyle(mFilePath)
+        self.assertTrue(status)
+
+        # The style is saved as non-default
+        errorMsg = vl.saveStyleToDatabase(
+            "by day", "faded greens and elegant patterns", False, "")
+        self.assertFalse(errorMsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), '')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'a style')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'by day')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        # the style id should be "1", not "by day"
+        qml, errmsg = vl.getStyleFromDatabase("by day")
+        self.assertFalse(qml)
+        self.assertTrue(errmsg)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 1)
+        self.assertFalse(errmsg)
+        self.assertEqual(idlist, ["1"])
+        self.assertEqual(namelist, ["by day"])
+        self.assertEqual(desclist, ["faded greens and elegant patterns"])
+
+        qml, errmsg = vl.getStyleFromDatabase("100")
+        self.assertFalse(qml)
+        self.assertTrue(errmsg)
+
+        qml, errmsg = vl.getStyleFromDatabase("1")
+        self.assertTrue(qml.startswith('<!DOCTYPE qgis'), qml)
+        self.assertFalse(errmsg)
+
+        # We save now the style again twice but with one as default
+        errorMsg = vl.saveStyleToDatabase(
+            "related style", "faded greens and elegant patterns", False, "")
+        self.assertFalse(errorMsg)
+        errorMsg = vl.saveStyleToDatabase(
+            "default style", "faded greens and elegant patterns", True, "")
+        self.assertFalse(errorMsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), '')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'a style')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'default style')
+        self.assertTrue(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'related style')
+        self.assertTrue(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('mssql', vl.source(), 'by day')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 3)
+        self.assertFalse(errmsg)
+        self.assertCountEqual(idlist, ["1", "2", "3"])
+        self.assertCountEqual(namelist, ["default style", "related style", "by day"])
+        self.assertCountEqual(desclist, ["faded greens and elegant patterns"] * 3)
+
     def testInsertPolygonInMultiPolygon(self):
         layer = QgsVectorLayer("MultiPolygon?crs=epsg:4326&field=id:integer", "addfeat", "memory")
         pr = layer.dataProvider()

diff --git a/tests/src/python/test_provider_postgres.py b/tests/src/python/test_provider_postgres.py
@@ -1882,7 +1882,7 @@ def testStyle(self):
             vl.dataProvider().isSaveAndLoadStyleToDatabaseSupported())
         self.assertTrue(vl.dataProvider().isDeleteStyleFromDatabaseSupported())
 
-        # table layer_styles does not exit
+        # table layer_styles does not exist
         related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
         self.assertEqual(related_count, -1)
         self.assertEqual(idlist, [])