Merge pull request #1900 from mhugo/fix_spatialite2

[spatialite] Bug fixes
qgis · Feb 14, 2015 · 553abbd · 553abbd
2 parents f69e0bb + 221ba9c
commit 553abbd
Show file tree

Hide file tree

Showing 2 changed files with 69 additions and 5 deletions.
diff --git a/src/providers/spatialite/qgsspatialiteprovider.cpp b/src/providers/spatialite/qgsspatialiteprovider.cpp
@@ -425,7 +425,7 @@ QgsSpatiaLiteProvider::QgsSpatiaLiteProvider( QString const &uri )
 
   // parsing members from the uri structure
   mTableName = anUri.table();
-  mGeometryColumn = anUri.geometryColumn();
+  mGeometryColumn = anUri.geometryColumn().toLower();
   mSqlitePath = anUri.database();
   mSubsetString = anUri.sql();
   mPrimaryKey = anUri.keyColumn();
@@ -612,7 +612,7 @@ void QgsSpatiaLiteProvider::loadFieldsAbstractInterface( gaiaVectorLayerPtr lyr
   while ( fld )
   {
     QString name = QString::fromUtf8( fld->AttributeFieldName );
-    if ( name != mGeometryColumn )
+    if ( name.toLower() != mGeometryColumn )
     {
       const char *type = "TEXT";
       QVariant::Type fieldType = QVariant::String; // default: SQLITE_TEXT
@@ -746,7 +746,7 @@ void QgsSpatiaLiteProvider::loadFields()
           QgsDebugMsg( "found primaryKey " + name );
         }
 
-        if ( name != mGeometryColumn )
+        if ( name.toLower() != mGeometryColumn )
         {
           // for sure any SQLite value can be represented as SQLITE_TEXT
           QVariant::Type fieldType = QVariant::String;
@@ -813,7 +813,7 @@ void QgsSpatiaLiteProvider::loadFields()
           QgsDebugMsg( "found primaryKey " + name );
         }
 
-        if ( name != mGeometryColumn )
+        if ( name.toLower() != mGeometryColumn )
         {
           // for sure any SQLite value can be represented as SQLITE_TEXT
           QVariant::Type fieldType = QVariant::String;
@@ -4145,7 +4145,7 @@ bool QgsSpatiaLiteProvider::checkLayerType()
 
   QString sql;
 
-  if ( mGeometryColumn.isEmpty() )
+  if ( mGeometryColumn.isEmpty() && !(mQuery.startsWith( "(" ) && mQuery.endsWith( ")" )) )
   {
     // checking if is a non-spatial table
     sql = QString( "SELECT type FROM sqlite_master "

diff --git a/tests/src/python/test_qgsspatialiteprovider.py b/tests/src/python/test_qgsspatialiteprovider.py
@@ -70,6 +70,30 @@ def setUpClass(cls):
         sql +=    "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
         cur.execute(sql)
 
+        # simple table with primary key
+        sql = "CREATE TABLE test_q (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
+        cur.execute(sql)
+        sql = "SELECT AddGeometryColumn('test_q', 'geometry', 4326, 'POLYGON', 'XY')"
+        cur.execute(sql)
+        sql = "INSERT INTO test_q (id, name, geometry) "
+        sql +=    "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
+        cur.execute(sql)
+        sql = "INSERT INTO test_q (id, name, geometry) "
+        sql +=    "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
+        cur.execute(sql)
+
+        # simple table with a geometry column named 'Geometry'
+        sql = "CREATE TABLE test_n (Id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL)"
+        cur.execute(sql)
+        sql = "SELECT AddGeometryColumn('test_n', 'Geometry', 4326, 'POLYGON', 'XY')"
+        cur.execute(sql)
+        sql = "INSERT INTO test_n (id, name, geometry) "
+        sql +=    "VALUES (1, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
+        cur.execute(sql)
+        sql = "INSERT INTO test_n (id, name, geometry) "
+        sql +=    "VALUES (2, 'toto', GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))', 4326))"
+        cur.execute(sql)
+
         cur.execute( "COMMIT" )
         con.close()
 
@@ -122,5 +146,45 @@ def xtest_SplitFeatureWithFailedCommit(self):
                 for c1, c2 in zip(p1, p2):
                     c1 == c2 or die("polygon has been altered by failed edition")
 
+    def test_queries(self):
+        """Test loading of query-based layers"""
+
+        # a query with a geometry, but no unique id
+        # this allows to load a query without unique id
+        # however, functions relying on such a unique id would fail
+        l = QgsVectorLayer("dbname=%s table='(select * from test_q)' (geometry)" % self.dbname, "test_pg_query1", "spatialite")
+        assert(l.isValid())
+        # the id() is not consistent
+        sum_id1 = sum(f.id() for f in l.getFeatures())
+        # the attribute 'id' works
+        sum_id2 = sum(f.attributes()[0] for f in l.getFeatures())
+        assert(sum_id1 == 0)
+        assert(sum_id2 == 3)
+
+        # and now with an id declared
+        l = QgsVectorLayer("dbname=%s table='(select * from test_q)' (geometry) key='id'" % self.dbname, "test_pg_query1", "spatialite")
+        assert(l.isValid())
+        sum_id1 = sum(f.id() for f in l.getFeatures())
+        sum_id2 = sum(f.attributes()[0] for f in l.getFeatures())
+        assert(sum_id1 == 3)
+        assert(sum_id2 == 3)
+
+        # a query, but no geometry
+        l = QgsVectorLayer("dbname=%s table='(select id,name from test_q)' key='id'" % self.dbname, "test_pg_query1", "spatialite")
+        assert(l.isValid())
+        sum_id1 = sum(f.id() for f in l.getFeatures())
+        sum_id2 = sum(f.attributes()[0] for f in l.getFeatures())
+        assert(sum_id1 == 3)
+        assert(sum_id2 == 3)
+
+    def test_case(self):
+        """Test case sensitivity issues"""
+        l = QgsVectorLayer("dbname=%s table='test_n' (geometry) key='id'" % self.dbname, "test_n1", "spatialite")
+        assert(l.isValid())
+        assert(l.dataProvider().fields().count() == 2)
+        fields = [f.name() for f in l.dataProvider().fields()]
+        assert('Geometry' not in fields)
+
+
 if __name__ == '__main__':
     unittest.main()