[spatialite] Unit tests, minor fixes for style handling

qgis · Jan 24, 2022 · 1872e4b · 1872e4b
1 parent 77bc195
commit 1872e4b
Show file tree

Hide file tree

Showing 3 changed files with 205 additions and 5 deletions.
diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -3783,6 +3783,11 @@ bool QgsSpatiaLiteProvider::isValid() const
   return mValid;
 }
 
+bool QgsSpatiaLiteProvider::isSaveAndLoadStyleToDatabaseSupported() const
+{
+  return mValid;
+}
+
 QString QgsSpatiaLiteProvider::name() const
 {
   return SPATIALITE_KEY;
@@ -6058,6 +6063,26 @@ bool QgsSpatiaLiteProviderMetadata::styleExists( const QString &uri, const QStri
 
   char **results = nullptr;
 
+  // check if layer_styles table exists
+  QString countIfExist = QStringLiteral( "SELECT 1 FROM sqlite_master WHERE type='table' AND name='layer_styles';" );
+
+  int rows = 0;
+  int columns = 0;
+  char *errMsg = nullptr;
+  int ret = sqlite3_get_table( sqliteHandle, countIfExist.toUtf8().constData(), &results, &rows, &columns, &errMsg );
+  if ( SQLITE_OK != ret )
+  {
+    QgsSqliteHandle::closeDb( handle );
+    QgsMessageLog::logMessage( QObject::tr( "Error executing query: %1" ).arg( countIfExist ) );
+    errorCause = QObject::tr( "Error looking for style. The query was logged" );
+    return false;
+  }
+  if ( rows == 0 )
+  {
+    // layer_styles table does not exist
+    return false;
+  }
+
   const QString checkQuery = QString( "SELECT styleName"
                                       " FROM layer_styles"
                                       " WHERE f_table_schema %1"
@@ -6069,10 +6094,8 @@ bool QgsSpatiaLiteProviderMetadata::styleExists( const QString &uri, const QStri
                              .arg( QgsSqliteUtils::quotedString( dsUri.geometryColumn() ) )
                              .arg( QgsSqliteUtils::quotedString( styleId.isEmpty() ? dsUri.table() : styleId ) );
 
-  int rows = 0;
-  int columns = 0;
-  char *errMsg = nullptr;
-  const int ret = sqlite3_get_table( sqliteHandle, checkQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
+
+  ret = sqlite3_get_table( sqliteHandle, checkQuery.toUtf8().constData(), &results, &rows, &columns, &errMsg );
 
   QString sqlError;
   if ( errMsg )

diff --git a/src/providers/spatialite/qgsspatialiteprovider.h b/src/providers/spatialite/qgsspatialiteprovider.h
@@ -114,7 +114,7 @@ class QgsSpatiaLiteProvider final: public QgsVectorDataProvider
                                        QgsFeedback *feedback = nullptr ) const override;
 
     bool isValid() const override;
-    bool isSaveAndLoadStyleToDatabaseSupported() const override { return true; }
+    bool isSaveAndLoadStyleToDatabaseSupported() const override;
     bool addFeatures( QgsFeatureList &flist, QgsFeatureSink::Flags flags = QgsFeatureSink::Flags() ) override;
     bool deleteFeatures( const QgsFeatureIds &id ) override;
     bool truncate() override;

diff --git a/tests/src/python/test_provider_spatialite.py b/tests/src/python/test_provider_spatialite.py
@@ -1082,6 +1082,183 @@ def testLoadStyle(self):
         err, ok = vl.loadDefaultStyle()
         self.assertTrue(ok)
 
+    def testStyleStorage(self):
+
+        # First test with invalid URI
+        vl = QgsVectorLayer('/idont/exist.sqlite', 'test', 'spatialite')
+
+        self.assertFalse(vl.dataProvider().isSaveAndLoadStyleToDatabaseSupported())
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', '/idont/exist.sqlite', '')
+        self.assertFalse(res)
+        self.assertTrue(err)
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', '/idont/exist.sqlite', 'a style')
+        self.assertFalse(res)
+        self.assertTrue(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, -1)
+        self.assertEqual(idlist, [])
+        self.assertEqual(namelist, [])
+        self.assertEqual(desclist, [])
+        self.assertTrue(errmsg)
+
+        qml, errmsg = vl.getStyleFromDatabase("1")
+        self.assertFalse(qml)
+        self.assertTrue(errmsg)
+
+        qml, success = vl.loadNamedStyle('/idont/exist.sqlite')
+        self.assertFalse(success)
+
+        errorMsg = vl.saveStyleToDatabase("name", "description", False, "")
+        self.assertTrue(errorMsg)
+
+        # create test db
+        dbname = os.path.join(tempfile.gettempdir(), "test_stylehandling.sqlite")
+        if os.path.exists(dbname):
+            os.remove(dbname)
+        con = spatialite_connect(dbname, isolation_level=None)
+        cur = con.cursor()
+        cur.execute("BEGIN")
+        sql = "SELECT InitSpatialMetadata()"
+        cur.execute(sql)
+
+        # simple table with primary key
+        sql = "CREATE TABLE test_pg (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
+        cur.execute(sql)
+
+        sql = "SELECT AddGeometryColumn('test_pg', 'geometry', 4326, 'POLYGON', 'XY')"
+        cur.execute(sql)
+
+        sql = "INSERT INTO test_pg (id, name, geometry) "
+        sql += "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
+        cur.execute(sql)
+
+        cur.execute("COMMIT")
+        con.close()
+
+        testPath = "dbname=%s table='test_pg' (geometry) key='id'" % dbname
+        vl = QgsVectorLayer(testPath, 'test', 'spatialite')
+        self.assertTrue(vl.isValid())
+
+        self.assertTrue(vl.dataProvider().isSaveAndLoadStyleToDatabaseSupported())
+
+        # style tables don't exist yet
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), '')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'a style')
+        self.assertFalse(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 0)
+        self.assertEqual(idlist, [])
+        self.assertEqual(namelist, [])
+        self.assertEqual(desclist, [])
+        self.assertTrue(errmsg)
+
+        qml, errmsg = vl.getStyleFromDatabase("not_existing")
+        self.assertFalse(qml)
+        self.assertTrue(errmsg)
+
+        qml, success = vl.loadNamedStyle('{}|layerid=0'.format(dbname))
+        self.assertFalse(success)
+
+        errorMsg = vl.saveStyleToDatabase("name", "description", False, "")
+        self.assertEqual(errorMsg, "")
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), '')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'a style')
+        self.assertFalse(res)
+        self.assertFalse(err)
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        qml, errmsg = vl.getStyleFromDatabase("not_existing")
+        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, ['name'])
+        self.assertEqual(desclist, ['description'])
+
+        qml, errmsg = vl.getStyleFromDatabase("100")
+        self.assertEqual(qml, "")
+        self.assertNotEqual(errmsg, "")
+
+        qml, errmsg = vl.getStyleFromDatabase("1")
+        self.assertTrue(qml.startswith('<!DOCTYPE qgis'), qml)
+        self.assertFalse(errmsg)
+
+        # Try overwrite it but simulate answer no
+        settings = QgsSettings()
+        settings.setValue("/qgis/overwriteStyle", False)
+        errorMsg = vl.saveStyleToDatabase("name", "description_bis", False, "")
+        self.assertTrue(errorMsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 1)
+        self.assertFalse(errmsg)
+        self.assertEqual(idlist, ['1'])
+        self.assertEqual(namelist, ['name'])
+        self.assertEqual(desclist, ['description'])
+
+        # Try overwrite it and simulate answer yes
+        settings = QgsSettings()
+        settings.setValue("/qgis/overwriteStyle", True)
+        errorMsg = vl.saveStyleToDatabase("name", "description_bis", False, "")
+        self.assertFalse(errorMsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 1)
+        self.assertFalse(errmsg)
+        self.assertEqual(idlist, ['1'])
+        self.assertEqual(namelist, ['name'])
+        self.assertEqual(desclist, ['description_bis'])
+
+        errorMsg = vl.saveStyleToDatabase("name_test2", "description_test2", True, "")
+        self.assertFalse(errmsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name_test2')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        errorMsg = vl.saveStyleToDatabase("name2", "description2", True, "")
+        self.assertFalse(errmsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name2')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        errorMsg = vl.saveStyleToDatabase("name3", "description3", True, "")
+        self.assertFalse(errmsg)
+
+        res, err = QgsProviderRegistry.instance().styleExists('spatialite', vl.source(), 'name3')
+        self.assertTrue(res)
+        self.assertFalse(err)
+
+        related_count, idlist, namelist, desclist, errmsg = vl.listStylesInDatabase()
+        self.assertEqual(related_count, 4)
+        self.assertFalse(errmsg)
+        self.assertCountEqual(idlist, ['1', '3', '4', '2'])
+        self.assertCountEqual(namelist, ['name', 'name2', 'name3', 'name_test2'])
+        self.assertCountEqual(desclist, ['description_bis', 'description2', 'description3', 'description_test2'])
+
     def _aliased_sql_helper(self, dbname):
         queries = (
             '(SELECT * FROM (SELECT * from \\"some view\\"))',