Fix views query tables in spatialite

There is still an old issue: views are only available through DB manager. Fixes #20674 - again - Life is really simple, but we insist on making it complicated. Confucius
qgis · Dec 18, 2018 · 07d9d1d · 07d9d1d
1 parent 13de14a
commit 07d9d1d
Show file tree

Hide file tree

Showing 3 changed files with 24 additions and 7 deletions.
diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -4666,18 +4666,28 @@ bool QgsSpatiaLiteProvider::checkLayerType()
                                      .arg( sql ), tr( "SpatiaLite" ), Qgis::MessageLevel::Warning );
         }
 
-        QString pk { QStringLiteral( "%1.%2" ).arg( quotedIdentifier( alias ) ).arg( pks.first() ) };
-        QString newSql( mQuery.replace( injectionRe,
-                                        QStringLiteral( R"re(SELECT %1.%2, \1)re" )
-                                        .arg( quotedIdentifier( tableIdentifier ) )
-                                        .arg( pks.first() ) ) );
-        sql = QStringLiteral( "SELECT %1 FROM %2 LIMIT 1" ).arg( pk ).arg( newSql );
+        // Try first without any injection or manipulation
+        sql = QStringLiteral( "SELECT %1, %2 FROM %3 LIMIT 1" ).arg( quotedIdentifier( pks.first( ) ), quotedIdentifier( mGeometryColumn ), mQuery );
         ret = sqlite3_get_table( mSqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
         if ( ret == SQLITE_OK && rows == 1 )
         {
-          mQuery = newSql;
           mPrimaryKey = pks.first( );
         }
+        else // if that does not work, try injection with table name/alias
+        {
+          QString pk { QStringLiteral( "%1.%2" ).arg( quotedIdentifier( alias ) ).arg( pks.first() ) };
+          QString newSql( mQuery.replace( injectionRe,
+                                          QStringLiteral( R"re(SELECT %1.%2, \1)re" )
+                                          .arg( quotedIdentifier( tableIdentifier ) )
+                                          .arg( pks.first() ) ) );
+          sql = QStringLiteral( "SELECT %1 FROM %2 LIMIT 1" ).arg( pk ).arg( newSql );
+          ret = sqlite3_get_table( mSqliteHandle, sql.toUtf8().constData(), &results, &rows, &columns, &errMsg );
+          if ( ret == SQLITE_OK && rows == 1 )
+          {
+            mQuery = newSql;
+            mPrimaryKey = pks.first( );
+          }
+        }
       }
 
       // If there is still no primary key, check if we can get use the ROWID from the table that provides the geometry

diff --git a/tests/src/python/test_provider_spatialite.py b/tests/src/python/test_provider_spatialite.py
@@ -791,6 +791,8 @@ def testLoadStyle(self):
 
     def _aliased_sql_helper(self, dbname):
         queries = (
+            '(SELECT * FROM (SELECT * from \\"some view\\"))',
+            '(SELECT * FROM \\"some view\\")',
             '(select sd.* from somedata as sd left join somedata as sd2 on ( sd2.name = sd.name ))',
             '(select sd.* from \\"somedata\\" as sd left join \\"somedata\\" as sd2 on ( sd2.name = sd.name ))',
             "(SELECT * FROM somedata as my_alias1\n)",
@@ -814,6 +816,8 @@ def _aliased_sql_helper(self, dbname):
         for sql in queries:
             vl = QgsVectorLayer('dbname=\'{}\' table="{}" (geom) sql='.format(dbname, sql), 'test', 'spatialite')
             self.assertTrue(vl.isValid(), 'dbname: {} - sql: {}'.format(dbname, sql))
+            self.assertTrue(vl.featureCount() > 1)
+            self.assertTrue(vl.isSpatial())
 
     def testPkLessQuery(self):
         """Test if features in queries with/without pk can be retrieved by id"""
@@ -855,6 +859,9 @@ def _make_table(table_name):
         _make_table("somedata")
         _make_table("some data")
 
+        sql = "CREATE VIEW \"some view\" AS SELECT * FROM \"somedata\""
+        cur.execute(sql)
+
         cur.execute("COMMIT")
         con.close()
 

diff --git a/tests/testdata/provider/spatialite.db b/tests/testdata/provider/spatialite.db