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)