def test_create_vector_layer(self):
"""Test query layers"""
md = QgsProviderRegistry.instance().providerMetadata('spatialite')
conn = md.createConnection(self.uri, {})
options = QgsAbstractDatabaseProviderConnection.SqlVectorLayerOptions()
options.sql = 'SELECT fid, name, geom FROM cdb_lines WHERE name LIKE \'S%\' LIMIT 2'
options.geometryColumn = 'geom'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PolygonGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 2)
self.assertEqual(features[0].attributes(), [8, 'Sülfeld'])
options.filter = 'name == \'Sülfeld\''
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
# Test flags
self.assertTrue(vl.vectorLayerTypeFlags()
& Qgis.VectorLayerTypeFlag.SqlQuery)
self.assertEqual(vl.geometryType(), QgsWkbTypes.PolygonGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
self.assertEqual(features[0].attributes(), [8, 'Sülfeld'])
def testCreateSqlVectorLayer(self):
"""Test vector layer creation from SQL query"""
md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
conn = md.createConnection(self.uri, {})
if not conn.capabilities() & QgsAbstractDatabaseProviderConnection.SqlLayers:
print(f"FIXME: {self.providerKey} data provider does not support query layers!")
return
schema = getattr(self, 'sqlVectorLayerSchema', None)
if schema is None:
print(f"FIXME: {self.providerKey} data provider test case does not define self.sqlVectorLayerSchema for query layers test!")
return
table = getattr(self, 'sqlVectorLayerTable', None)
if table is None:
print(f"FIXME: {self.providerKey} data provider test case does not define self.sqlVectorLayerTable for query layers test!")
return
sql_layer_capabilities = conn.sqlLayerDefinitionCapabilities()
# Try a simple select first
table_info = conn.table(schema, table)
options = QgsAbstractDatabaseProviderConnection.SqlVectorLayerOptions()
options.layerName = 'My SQL Layer'
# Some providers do not support schema
if schema != '':
options.sql = f'SELECT * FROM "{table_info.schema()}"."{table_info.tableName()}"'
else:
options.sql = f'SELECT * FROM "{table_info.tableName()}"'
options.geometryColumn = table_info.geometryColumn()
options.primaryKeyColumns = table_info.primaryKeyColumns()
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isSqlQuery())
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSpatial())
self.assertEqual(vl.name(), options.layerName)
# Test that a database connection can be retrieved from an existing layer
vlconn = QgsMapLayerUtils.databaseConnection(vl)
self.assertIsNotNone(vlconn)
self.assertEqual(vlconn.uri(), conn.uri())
# Some providers can also create SQL layer without an explicit PK
if sql_layer_capabilities & Qgis.SqlLayerDefinitionCapability.PrimaryKeys:
options.primaryKeyColumns = []
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isSqlQuery())
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSpatial())
# Some providers can also create SQL layer without an explicit geometry column
if sql_layer_capabilities & Qgis.SqlLayerDefinitionCapability.GeometryColumn:
options.primaryKeyColumns = table_info.primaryKeyColumns()
options.geometryColumn = ''
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
# This may fail for OGR where the provider is smart enough to guess the geometry column
if self.providerKey != 'ogr':
self.assertFalse(vl.isSpatial())
# Some providers can also create SQL layer with filters
if sql_layer_capabilities & Qgis.SqlLayerDefinitionCapability.SubsetStringFilter:
options.primaryKeyColumns = table_info.primaryKeyColumns()
options.geometryColumn = table_info.geometryColumn()
options.filter = f'"{options.primaryKeyColumns[0]}" > 0'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertTrue(vl.isSpatial())
def test_create_vector_layer(self):
"""Test query layers"""
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(self.uri, {})
sql = """
DROP TABLE IF EXISTS qgis_test.query_layer1;
CREATE TABLE qgis_test.query_layer1 (
id SERIAL PRIMARY KEY,
geom geometry(POINT,4326)
);
INSERT INTO qgis_test.query_layer1 (id, geom) VALUES (221, ST_GeomFromText('point(9 45)', 4326));
INSERT INTO qgis_test.query_layer1 (id, geom) VALUES (201, ST_GeomFromText('point(9.5 45.5)', 4326));
"""
conn.executeSql(sql)
options = QgsAbstractDatabaseProviderConnection.SqlVectorLayerOptions()
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 WHERE id < 200 LIMIT 2'
options.primaryKeyColumns = ['id']
options.geometryColumn = 'geom'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
# Test flags
self.assertTrue(vl.vectorLayerTypeFlags() & Qgis.VectorLayerTypeFlag.SqlQuery)
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 0)
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 WHERE id > 200 LIMIT 2'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 2)
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 WHERE id > 210 LIMIT 2'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 LIMIT 2'
options.filter = 'id > 210'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
# Wrong calls
options.primaryKeyColumns = ['DOES_NOT_EXIST']
vl = conn.createSqlVectorLayer(options)
self.assertFalse(vl.isValid())
self.assertFalse(vl.vectorLayerTypeFlags() & Qgis.VectorLayerTypeFlag.SqlQuery)
self.assertFalse(vl.isSqlQuery())
options.primaryKeyColumns = ['id']
options.geometryColumn = 'DOES_NOT_EXIST'
vl = conn.createSqlVectorLayer(options)
self.assertFalse(vl.isValid())
self.assertFalse(vl.isSqlQuery())
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 WHERE id > 210 LIMIT 2'
options.primaryKeyColumns = []
options.geometryColumn = ''
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
# No geometry and no PK, aspatial layer
options.sql = 'SELECT id, geom FROM qgis_test.query_layer1 WHERE id > 210 LIMIT 2'
options.primaryKeyColumns = []
options.geometryColumn = ''
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
self.assertNotEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
# Composite keys
sql = """
DROP TABLE IF EXISTS qgis_test.query_layer2;
CREATE TABLE qgis_test.query_layer2 (
id SERIAL,
id2 SERIAL,
geom geometry(POINT,4326),
PRIMARY KEY(id, id2)
);
INSERT INTO qgis_test.query_layer2 (id, id2, geom) VALUES (101, 101, ST_GeomFromText('point(9 45)', 4326));
INSERT INTO qgis_test.query_layer2 (id, id2, geom) VALUES (201, 201, ST_GeomFromText('point(9.5 45.5)', 4326));
"""
conn.executeSql(sql)
options = QgsAbstractDatabaseProviderConnection.SqlVectorLayerOptions()
options.sql = 'SELECT id, id2, geom FROM qgis_test.query_layer2 ORDER BY id ASC LIMIT 1'
options.primaryKeyColumns = ['id', 'id2']
options.geometryColumn = 'geom'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isValid())
self.assertTrue(vl.isSqlQuery())
# Test flags
self.assertTrue(vl.vectorLayerTypeFlags() & Qgis.VectorLayerTypeFlag.SqlQuery)
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
# No PKs
options.primaryKeyColumns = []
options.geometryColumn = 'geom'
vl = conn.createSqlVectorLayer(options)
self.assertTrue(vl.isSqlQuery())
self.assertTrue(vl.isValid())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
