Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Allow to open a spatialite table without primary key
  • Loading branch information
Hugo Mercier committed May 4, 2015
1 parent 72eb1aa commit fd6dc0d
Show file tree
Hide file tree
Showing 4 changed files with 31 additions and 17 deletions.
22 changes: 17 additions & 5 deletions src/providers/spatialite/qgsspatialitefeatureiterator.cpp
Expand Up @@ -31,6 +31,8 @@ QgsSpatiaLiteFeatureIterator::QgsSpatiaLiteFeatureIterator( QgsSpatiaLiteFeature
mHandle = QgsSpatiaLiteConnPool::instance()->acquireConnection( mSource->mSqlitePath );

mFetchGeometry = !mSource->mGeometryColumn.isNull() && !( mRequest.flags() & QgsFeatureRequest::NoGeometry );
mHasPrimaryKey = !mSource->mPrimaryKey.isEmpty();
mRowNumber = 0;

QString whereClause;
if ( request.filterType() == QgsFeatureRequest::FilterRect && !mSource->mGeometryColumn.isNull() )
Expand Down Expand Up @@ -108,6 +110,7 @@ bool QgsSpatiaLiteFeatureIterator::rewind()

if ( sqlite3_reset( sqliteStatement ) == SQLITE_OK )
{
mRowNumber = 0;
return true;
}
else
Expand Down Expand Up @@ -143,7 +146,7 @@ bool QgsSpatiaLiteFeatureIterator::prepareStatement( QString whereClause )
{
try
{
QString sql = QString( "SELECT %1" ).arg( quotedPrimaryKey() );
QString sql = QString( "SELECT %1" ).arg( mHasPrimaryKey ? quotedPrimaryKey() : "0" );
int colIdx = 1; // column 0 is primary key

if ( mRequest.flags() & QgsFeatureRequest::SubsetOfAttributes )
Expand Down Expand Up @@ -320,10 +323,19 @@ bool QgsSpatiaLiteFeatureIterator::getFeature( sqlite3_stmt *stmt, QgsFeature &f
{
if ( ic == 0 )
{
// first column always contains the ROWID (or the primary key)
QgsFeatureId fid = sqlite3_column_int64( stmt, ic );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 3 );
feature.setFeatureId( fid );
if ( mHasPrimaryKey )
{
// first column always contains the ROWID (or the primary key)
QgsFeatureId fid = sqlite3_column_int64( stmt, ic );
QgsDebugMsgLevel( QString( "fid=%1" ).arg( fid ), 3 );
feature.setFeatureId( fid );
}
else
{
// autoincrement a row number
mRowNumber++;
feature.setFeatureId( mRowNumber );
}
}
else if ( mFetchGeometry && ic == mGeomColIdx )
{
Expand Down
3 changes: 3 additions & 0 deletions src/providers/spatialite/qgsspatialitefeatureiterator.h
Expand Up @@ -93,6 +93,9 @@ class QgsSpatiaLiteFeatureIterator : public QgsAbstractFeatureIteratorFromSource

//! Set to true, if geometry is in the requested columns
bool mFetchGeometry;

bool mHasPrimaryKey;
QgsFeatureId mRowNumber;
};

#endif // QGSSPATIALITEFEATUREITERATOR_H
2 changes: 1 addition & 1 deletion src/providers/spatialite/qgsspatialiteprovider.cpp
Expand Up @@ -497,7 +497,7 @@ QgsSpatiaLiteProvider::QgsSpatiaLiteProvider( QString const &uri )
closeDb();
return;
}
enabledCapabilities = QgsVectorDataProvider::SelectAtId | QgsVectorDataProvider::SelectGeometryAtId;
enabledCapabilities = mPrimaryKey.isEmpty() ? 0 : (QgsVectorDataProvider::SelectAtId | QgsVectorDataProvider::SelectGeometryAtId);
if (( mTableBased || mViewBased ) && !mReadOnly )
{
// enabling editing only for Tables [excluding Views and VirtualShapes]
Expand Down
21 changes: 10 additions & 11 deletions tests/src/python/test_qgsspatialiteprovider.py
Expand Up @@ -76,10 +76,10 @@ def setUpClass(cls):
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))"
sql += "VALUES (11, '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))"
sql += "VALUES (21, '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'
Expand Down Expand Up @@ -150,32 +150,31 @@ 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
# the id will be autoincremented
l = QgsVectorLayer("dbname=%s table='(select * from test_q)' (geometry)" % self.dbname, "test_pg_query1", "spatialite")
assert(l.isValid())
# the id() is not consistent
# the id() is autoincremented
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)
assert(sum_id1 == 3) # 1+2
assert(sum_id2 == 32) # 11 + 21

# 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)
assert(sum_id1 == 32)
assert(sum_id2 == 32)

# 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)
assert(sum_id1 == 32)
assert(sum_id2 == 32)

def test_case(self):
"""Test case sensitivity issues"""
Expand Down

0 comments on commit fd6dc0d

Please sign in to comment.