def _test_operations(self, md, conn):
"""Common tests"""
capabilities = conn.capabilities()
# Schema operations
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema
and
capabilities & QgsAbstractDatabaseProviderConnection.Schemas
and capabilities
& QgsAbstractDatabaseProviderConnection.DropSchema):
# Start clean
if 'myNewSchema' in conn.schemas():
conn.dropSchema('myNewSchema', True)
# Create
conn.createSchema('myNewSchema')
schemas = conn.schemas()
self.assertTrue('myNewSchema' in schemas)
# Create again
with self.assertRaises(QgsProviderConnectionException) as ex:
conn.createSchema('myNewSchema')
# Test rename
if capabilities & QgsAbstractDatabaseProviderConnection.RenameSchema:
# Rename
conn.renameSchema('myNewSchema', 'myVeryNewSchema')
schemas = conn.schemas()
self.assertTrue('myVeryNewSchema' in schemas)
self.assertFalse('myNewSchema' in schemas)
conn.renameSchema('myVeryNewSchema', 'myNewSchema')
schemas = conn.schemas()
self.assertFalse('myVeryNewSchema' in schemas)
self.assertTrue('myNewSchema' in schemas)
# Drop
conn.dropSchema('myNewSchema')
schemas = conn.schemas()
self.assertFalse('myNewSchema' in schemas)
# UTF8 schema
conn.createSchema('myUtf8\U0001f604NewSchema')
schemas = conn.schemas()
conn.dropSchema('myUtf8\U0001f604NewSchema')
schemas = conn.schemas()
self.assertFalse('myUtf8\U0001f604NewSchema' in schemas)
# Table operations
if (capabilities
& QgsAbstractDatabaseProviderConnection.CreateVectorTable
and capabilities & QgsAbstractDatabaseProviderConnection.Tables
and capabilities
& QgsAbstractDatabaseProviderConnection.DropVectorTable):
if capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema:
schema = 'myNewSchema'
conn.createSchema('myNewSchema')
else:
schema = 'public'
# Start clean
if 'myNewTable' in self._table_names(conn.tables(schema)):
conn.dropVectorTable(schema, 'myNewTable')
fields = QgsFields()
fields.append(QgsField("string_t", QVariant.String))
fields.append(QgsField("long_t", QVariant.LongLong))
fields.append(QgsField("double_t", QVariant.Double))
fields.append(QgsField("integer_t", QVariant.Int))
fields.append(QgsField("date_t", QVariant.Date))
fields.append(QgsField("datetime_t", QVariant.DateTime))
fields.append(QgsField("time_t", QVariant.Time))
options = {}
crs = QgsCoordinateReferenceSystem.fromEpsgId(3857)
typ = QgsWkbTypes.LineString
# Create
conn.createVectorTable(schema, 'myNewTable', fields, typ, crs,
True, options)
table_names = self._table_names(conn.tables(schema))
self.assertTrue('myNewTable' in table_names)
# Create UTF8 table
conn.createVectorTable(schema, 'myUtf8\U0001f604Table', fields,
typ, crs, True, options)
table_names = self._table_names(conn.tables(schema))
self.assertTrue('myNewTable' in table_names)
self.assertTrue('myUtf8\U0001f604Table' in table_names)
conn.dropVectorTable(schema, 'myUtf8\U0001f604Table')
table_names = self._table_names(conn.tables(schema))
self.assertFalse('myUtf8\U0001f604Table' in table_names)
self.assertTrue('myNewTable' in table_names)
# insert something, because otherwise MSSQL cannot guess
if self.providerKey == 'mssql':
f = QgsFeature(fields)
f.setGeometry(
QgsGeometry.fromWkt('LineString (-72.345 71.987, -80 80)'))
vl = QgsVectorLayer(conn.tableUri('myNewSchema', 'myNewTable'),
'vl', 'mssql')
vl.dataProvider().addFeatures([f])
# Check table information
table_properties = conn.tables(schema)
table_property = self._table_by_name(table_properties,
'myNewTable')
self.assertEqual(table_property.maxCoordinateDimensions(), 2)
self.assertIsNotNone(table_property)
self.assertEqual(table_property.tableName(), 'myNewTable')
self.assertEqual(table_property.geometryColumnCount(), 1)
self.assertEqual(table_property.geometryColumnTypes()[0].wkbType,
QgsWkbTypes.LineString)
cols = table_property.geometryColumnTypes()
self.assertEqual(cols[0].crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(table_property.defaultName(), 'myNewTable')
# Check aspatial tables
conn.createVectorTable(schema, 'myNewAspatialTable', fields,
QgsWkbTypes.NoGeometry, crs, True, options)
table_properties = conn.tables(
schema, QgsAbstractDatabaseProviderConnection.Aspatial)
table_property = self._table_by_name(table_properties,
'myNewAspatialTable')
self.assertIsNotNone(table_property)
self.assertEqual(table_property.maxCoordinateDimensions(), 0)
self.assertEqual(table_property.tableName(), 'myNewAspatialTable')
self.assertEqual(table_property.geometryColumnCount(), 0)
self.assertEqual(table_property.geometryColumn(), '')
self.assertEqual(table_property.defaultName(),
'myNewAspatialTable')
cols = table_property.geometryColumnTypes()
# We always return geom col types, even when there is no geometry
self.assertEqual(cols[0].wkbType, QgsWkbTypes.NoGeometry)
self.assertFalse(cols[0].crs.isValid())
self.assertFalse(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Raster)
self.assertFalse(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Vector)
self.assertTrue(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Aspatial)
# Check executeSql
has_schema = capabilities & QgsAbstractDatabaseProviderConnection.Schemas
if capabilities & QgsAbstractDatabaseProviderConnection.ExecuteSql:
if has_schema:
table = "\"%s\".\"myNewAspatialTable\"" % schema
else:
table = 'myNewAspatialTable'
# MSSQL literal syntax for UTF8 requires 'N' prefix
sql = "INSERT INTO %s (string_t, long_t, double_t, integer_t, date_t, datetime_t, time_t) VALUES (%s'QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12', '12:00:13.00')" % (
table, 'N' if self.providerKey == 'mssql' else '')
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT string_t, long_t, double_t, integer_t, date_t, datetime_t FROM %s" % table
res = conn.executeSql(sql)
# GPKG and spatialite have no type for time
self.assertEqual(res, [[
'QGIS Rocks - \U0001f604', 666, 1.234, 1234,
QtCore.QDate(2019, 7, 8),
QtCore.QDateTime(2019, 7, 8, 12, 0, 12)
]])
sql = "SELECT time_t FROM %s" % table
res = conn.executeSql(sql)
# This does not work in MSSQL and returns a QByteArray, we have no way to know that it is a time
# value and there is no way we can convert it.
if self.providerKey != 'mssql':
self.assertIn(
res, ([[QtCore.QTime(12, 0, 13)]], [['12:00:13.00']]))
sql = "DELETE FROM %s WHERE string_t = %s'QGIS Rocks - \U0001f604'" % (
table, 'N' if self.providerKey == 'mssql' else '')
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT string_t, integer_t FROM %s" % table
res = conn.executeSql(sql)
self.assertEqual(res, [])
# Check that we do NOT get the aspatial table when querying for vectors
table_names = self._table_names(
conn.tables(schema,
QgsAbstractDatabaseProviderConnection.Vector))
self.assertTrue('myNewTable' in table_names)
self.assertFalse('myNewAspatialTable' in table_names)
# Query for rasters (in qgis_test schema or no schema for GPKG, spatialite has no support)
if self.providerKey not in ('spatialite', 'mssql'):
table_properties = conn.tables(
'qgis_test', QgsAbstractDatabaseProviderConnection.Raster)
# At least one raster should be there (except for spatialite)
self.assertTrue(len(table_properties) >= 1)
table_property = table_properties[0]
self.assertTrue(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Raster)
self.assertFalse(
table_property.flags()
& QgsAbstractDatabaseProviderConnection.Vector)
self.assertFalse(
table_property.flags()
& QgsAbstractDatabaseProviderConnection.Aspatial)
if capabilities & QgsAbstractDatabaseProviderConnection.RenameVectorTable:
# Rename
conn.renameVectorTable(schema, 'myNewTable', 'myVeryNewTable')
tables = self._table_names(conn.tables(schema))
self.assertFalse('myNewTable' in tables)
self.assertTrue('myVeryNewTable' in tables)
# Rename it back
conn.renameVectorTable(schema, 'myVeryNewTable', 'myNewTable')
tables = self._table_names(conn.tables(schema))
self.assertTrue('myNewTable' in tables)
self.assertFalse('myVeryNewTable' in tables)
# Vacuum
if capabilities & QgsAbstractDatabaseProviderConnection.Vacuum:
conn.vacuum('myNewSchema', 'myNewTable')
# Spatial index
spatial_index_exists = False
# we don't initially know if a spatial index exists -- some formats may create them by default, others not
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
spatial_index_exists = conn.spatialIndexExists(
'myNewSchema', 'myNewTable', 'geom')
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
if spatial_index_exists:
conn.deleteSpatialIndex('myNewSchema', 'myNewTable',
'geom')
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(
conn.spatialIndexExists('myNewSchema', 'myNewTable',
'geom'))
if capabilities & QgsAbstractDatabaseProviderConnection.CreateSpatialIndex:
options = QgsAbstractDatabaseProviderConnection.SpatialIndexOptions(
)
options.geometryColumnName = 'geom'
conn.createSpatialIndex('myNewSchema', 'myNewTable', options)
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertTrue(
conn.spatialIndexExists('myNewSchema', 'myNewTable',
'geom'))
# now we know for certain a spatial index exists, let's retry dropping it
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
conn.deleteSpatialIndex('myNewSchema', 'myNewTable',
'geom')
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(
conn.spatialIndexExists('myNewSchema',
'myNewTable', 'geom'))
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema (should fail)
with self.assertRaises(QgsProviderConnectionException) as ex:
conn.dropSchema('myNewSchema')
# Check some column types operations
table = self._table_by_name(conn.tables(schema), 'myNewTable')
self.assertEqual(len(table.geometryColumnTypes()), 1)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Add a new (existing type)
table.addGeometryColumnType(
QgsWkbTypes.LineString,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(len(table.geometryColumnTypes()), 1)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Add a new one
table.addGeometryColumnType(
QgsWkbTypes.LineString,
QgsCoordinateReferenceSystem.fromEpsgId(4326))
self.assertEqual(len(table.geometryColumnTypes()), 2)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
ct = table.geometryColumnTypes()[1]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(4326))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Drop table
conn.dropVectorTable(schema, 'myNewTable')
conn.dropVectorTable(schema, 'myNewAspatialTable')
table_names = self._table_names(conn.tables(schema))
self.assertFalse('myNewTable' in table_names)
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema
conn.dropSchema('myNewSchema')
self.assertFalse('myNewSchema' in conn.schemas())
conns = md.connections()
self.assertTrue(
isinstance(
list(conns.values())[0],
QgsAbstractDatabaseProviderConnection))
# Remove connection
spy_deleted = QSignalSpy(md.connectionDeleted)
md.deleteConnection('qgis_test1')
self.assertEqual(list(md.connections().values()), [])
self.assertEqual(len(spy_deleted), 1)
def get_tables(schema, configuration, flags=QgsAbstractDatabaseProviderConnection.TableFlags()):
conn = md.createConnection(self.uri, configuration)
tables = conn.tables(schema, flags)
return sorted([table.tableName() for table in tables if table.tableName() in [
'DATE_TIMES', 'GENERATED_COLUMNS', 'LINE_DATA', 'OTHER_TABLE', 'POINT_DATA', 'POINT_DATA_IDENTITY', 'POLY_DATA', 'SOME_DATA', 'SOME_POLY_DATA']])
def _test_operations(self, md, conn):
"""Common tests"""
capabilities = conn.capabilities()
# Schema operations
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema
and
capabilities & QgsAbstractDatabaseProviderConnection.Schemas
and capabilities
& QgsAbstractDatabaseProviderConnection.DropSchema):
myNewSchema = self.getUniqueSchemaName('myNewSchema')
# Start clean
if myNewSchema in conn.schemas():
conn.dropSchema(myNewSchema, True)
# Create
conn.createSchema(myNewSchema)
schemas = conn.schemas()
self.assertTrue(myNewSchema in schemas)
# Create again
with self.assertRaises(QgsProviderConnectionException) as ex:
conn.createSchema(myNewSchema)
# Test rename
if capabilities & QgsAbstractDatabaseProviderConnection.RenameSchema:
# Rename
myVeryNewSchema = self.getUniqueSchemaName('myVeryNewSchema')
conn.renameSchema(myNewSchema, myVeryNewSchema)
schemas = conn.schemas()
self.assertTrue(myVeryNewSchema in schemas)
self.assertFalse(myNewSchema in schemas)
conn.renameSchema(myVeryNewSchema, myNewSchema)
schemas = conn.schemas()
self.assertFalse(myVeryNewSchema in schemas)
self.assertTrue(myNewSchema in schemas)
# Drop
conn.dropSchema(myNewSchema)
schemas = conn.schemas()
self.assertFalse(myNewSchema in schemas)
# UTF8 schema
myUtf8NewSchema = self.getUniqueSchemaName(
'myUtf8\U0001f604NewSchema')
conn.createSchema(myUtf8NewSchema)
schemas = conn.schemas()
conn.dropSchema(myUtf8NewSchema)
schemas = conn.schemas()
self.assertFalse(myUtf8NewSchema in schemas)
# Table operations
schema = None
if (capabilities
& QgsAbstractDatabaseProviderConnection.CreateVectorTable
and capabilities & QgsAbstractDatabaseProviderConnection.Tables
and capabilities
& QgsAbstractDatabaseProviderConnection.DropVectorTable):
if capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema:
schema = self.getUniqueSchemaName('myNewSchema')
conn.createSchema(schema)
elif capabilities & QgsAbstractDatabaseProviderConnection.Schemas:
schema = self.getUniqueSchemaName(self.defaultSchema)
# Start clean
if self.myNewTable in self._table_names(conn.tables(schema)):
conn.dropVectorTable(schema, self.myNewTable)
fields = QgsFields()
fields.append(QgsField("string_t", QVariant.String))
fields.append(QgsField("long_t", QVariant.LongLong))
fields.append(QgsField("double_t", QVariant.Double))
fields.append(QgsField("integer_t", QVariant.Int))
fields.append(QgsField("date_t", QVariant.Date))
fields.append(QgsField("datetime_t", QVariant.DateTime))
fields.append(QgsField("time_t", QVariant.Time))
options = {}
crs = QgsCoordinateReferenceSystem.fromEpsgId(3857)
typ = QgsWkbTypes.LineString
# Create
conn.createVectorTable(schema, self.myNewTable, fields, typ, crs,
True, options)
table_names = self._table_names(conn.tables(schema))
self.assertTrue(self.myNewTable in table_names)
# Create UTF8 table
conn.createVectorTable(schema, self.myUtf8Table, fields, typ, crs,
True, options)
table_names = self._table_names(conn.tables(schema))
self.assertTrue(self.myNewTable in table_names)
self.assertTrue(self.myUtf8Table in table_names)
conn.dropVectorTable(schema, self.myUtf8Table)
table_names = self._table_names(conn.tables(schema))
self.assertFalse(self.myUtf8Table in table_names)
self.assertTrue(self.myNewTable in table_names)
# insert something, because otherwise some databases cannot guess
if self.providerKey in ['hana', 'mssql', 'oracle']:
f = QgsFeature(fields)
f.setGeometry(
QgsGeometry.fromWkt('LineString (-72.345 71.987, -80 80)'))
vl = QgsVectorLayer(conn.tableUri(schema, self.myNewTable),
'vl', self.providerKey)
vl.dataProvider().addFeatures([f])
# Check table information
table_properties = conn.tables(schema)
table_property = self._table_by_name(table_properties,
self.myNewTable)
self.assertEqual(table_property.maxCoordinateDimensions(), 2)
self.assertIsNotNone(table_property)
self.assertEqual(table_property.tableName(), self.myNewTable)
self.assertEqual(table_property.geometryColumnCount(), 1)
self.assertEqual(table_property.geometryColumnTypes()[0].wkbType,
QgsWkbTypes.LineString)
cols = table_property.geometryColumnTypes()
self.assertEqual(cols[0].crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(table_property.defaultName(), self.myNewTable)
# Check aspatial tables
conn.createVectorTable(schema, 'myNewAspatialTable', fields,
QgsWkbTypes.NoGeometry, crs, True, options)
table_properties = conn.tables(
schema, QgsAbstractDatabaseProviderConnection.Aspatial)
table_property = self._table_by_name(table_properties,
'myNewAspatialTable')
self.assertIsNotNone(table_property)
self.assertEqual(table_property.maxCoordinateDimensions(), 0)
self.assertEqual(table_property.tableName(), 'myNewAspatialTable')
self.assertEqual(table_property.geometryColumnCount(), 0)
self.assertEqual(table_property.geometryColumn(), '')
self.assertEqual(table_property.defaultName(),
'myNewAspatialTable')
cols = table_property.geometryColumnTypes()
# We always return geom col types, even when there is no geometry
self.assertEqual(cols[0].wkbType, QgsWkbTypes.NoGeometry)
self.assertFalse(cols[0].crs.isValid())
self.assertFalse(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Raster)
self.assertFalse(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Vector)
self.assertTrue(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Aspatial)
# Check executeSql
if capabilities & QgsAbstractDatabaseProviderConnection.ExecuteSql:
if schema:
table = "\"%s\".\"myNewAspatialTable\"" % schema
else:
table = 'myNewAspatialTable'
# MSSQL literal syntax for UTF8 requires 'N' prefix
# Oracle date time definition needs some prefix
sql = "INSERT INTO %s (\"string_t\", \"long_t\", \"double_t\", \"integer_t\", \"date_t\", \"datetime_t\", \"time_t\") VALUES (%s'QGIS Rocks - \U0001f604', 666, 1.234, 1234, %s '2019-07-08', %s, '12:00:13.00')" % (
table, 'N' if self.providerKey == 'mssql' else '',
"DATE" if self.providerKey == 'oracle' else '',
"TIMESTAMP '2019-07-08 12:00:12'" if self.providerKey
== 'oracle' else "'2019-07-08T12:00:12'")
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT \"string_t\", \"long_t\", \"double_t\", \"integer_t\", \"date_t\", \"datetime_t\" FROM %s" % table
res = conn.executeSql(sql)
expected_date = QtCore.QDate(2019, 7, 8)
# GPKG and spatialite have no type for time
if self.treat_date_as_string():
expected_date = '2019-07-08'
# Oracle DATE type contains date and time and so returns a QDateTime object
elif self.providerKey == 'oracle':
expected_date = QtCore.QDateTime(QtCore.QDate(2019, 7, 8))
self.assertEqual(res, [[
'QGIS Rocks - \U0001f604', 666, 1.234, 1234, expected_date,
QtCore.QDateTime(2019, 7, 8, 12, 0, 12)
]])
# Test column names
res = conn.execSql(sql)
rows = res.rows()
self.assertEqual(rows, [[
'QGIS Rocks - \U0001f604', 666, 1.234, 1234, expected_date,
QtCore.QDateTime(2019, 7, 8, 12, 0, 12)
]])
self.assertEqual(res.columns(), [
'string_t', 'long_t', 'double_t', 'integer_t', 'date_t',
'datetime_t'
])
# Test iterator
old_rows = rows
res = conn.execSql(sql)
rows = []
self.assertTrue(res.hasNextRow())
for row in res:
rows.append(row)
self.assertEqual(rows, old_rows)
# Java style
res = conn.execSql(sql)
rows = []
self.assertTrue(res.hasNextRow())
while res.hasNextRow():
rows.append(res.nextRow())
self.assertFalse(res.hasNextRow())
# Test time_t
sql = "SELECT \"time_t\" FROM %s" % table
res = conn.executeSql(sql)
# This does not work in MSSQL and returns a QByteArray, we have no way to know that it is a time
# value and there is no way we can convert it.
if self.providerKey != 'mssql':
self.assertIn(
res, ([[QtCore.QTime(12, 0, 13)]], [['12:00:13.00']]))
sql = "DELETE FROM %s WHERE \"string_t\" = %s'QGIS Rocks - \U0001f604'" % (
table, 'N' if self.providerKey == 'mssql' else '')
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT \"string_t\", \"integer_t\" FROM %s" % table
res = conn.executeSql(sql)
self.assertEqual(res, [])
# Check that we do NOT get the aspatial table when querying for vectors
table_names = self._table_names(
conn.tables(schema,
QgsAbstractDatabaseProviderConnection.Vector))
self.assertTrue(self.myNewTable in table_names)
self.assertFalse('myNewAspatialTable' in table_names)
# Query for rasters (in qgis_test schema or no schema for GPKG, spatialite has no support)
if self.providerKey not in ('spatialite', 'mssql', 'hana',
'oracle'):
table_properties = conn.tables(
'qgis_test', QgsAbstractDatabaseProviderConnection.Raster)
# At least one raster should be there (except for spatialite)
self.assertTrue(len(table_properties) >= 1)
table_property = table_properties[0]
self.assertTrue(table_property.flags()
& QgsAbstractDatabaseProviderConnection.Raster)
self.assertFalse(
table_property.flags()
& QgsAbstractDatabaseProviderConnection.Vector)
self.assertFalse(
table_property.flags()
& QgsAbstractDatabaseProviderConnection.Aspatial)
if capabilities & QgsAbstractDatabaseProviderConnection.RenameVectorTable:
# Rename
conn.renameVectorTable(schema, self.myNewTable,
self.myVeryNewTable)
tables = self._table_names(conn.tables(schema))
self.assertFalse(self.myNewTable in tables)
self.assertTrue(self.myVeryNewTable in tables)
# Rename it back
conn.renameVectorTable(schema, self.myVeryNewTable,
self.myNewTable)
tables = self._table_names(conn.tables(schema))
self.assertTrue(self.myNewTable in tables)
self.assertFalse(self.myVeryNewTable in tables)
# Vacuum
if capabilities & QgsAbstractDatabaseProviderConnection.Vacuum:
conn.vacuum(schema, self.myNewTable)
# Spatial index
spatial_index_exists = False
# we don't initially know if a spatial index exists -- some formats may create them by default, others not
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
spatial_index_exists = conn.spatialIndexExists(
schema, self.myNewTable, self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
if spatial_index_exists:
conn.deleteSpatialIndex(schema, self.myNewTable,
self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(
conn.spatialIndexExists(schema, self.myNewTable,
self.geometryColumnName))
if capabilities & (
QgsAbstractDatabaseProviderConnection.CreateSpatialIndex |
QgsAbstractDatabaseProviderConnection.SpatialIndexExists):
options = QgsAbstractDatabaseProviderConnection.SpatialIndexOptions(
)
options.geometryColumnName = self.geometryColumnName
if not conn.spatialIndexExists(schema, self.myNewTable,
options.geometryColumnName):
conn.createSpatialIndex(schema, self.myNewTable, options)
self.assertTrue(
conn.spatialIndexExists(schema, self.myNewTable,
self.geometryColumnName))
# now we know for certain a spatial index exists, let's retry dropping it
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteSpatialIndex:
conn.deleteSpatialIndex(schema, self.myNewTable,
self.geometryColumnName)
if capabilities & QgsAbstractDatabaseProviderConnection.SpatialIndexExists:
self.assertFalse(
conn.spatialIndexExists(schema, self.myNewTable,
self.geometryColumnName))
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema (should fail)
with self.assertRaises(QgsProviderConnectionException) as ex:
conn.dropSchema(schema)
# Check some column types operations
table = self._table_by_name(conn.tables(schema), self.myNewTable)
self.assertEqual(len(table.geometryColumnTypes()), 1)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Add a new (existing type)
table.addGeometryColumnType(
QgsWkbTypes.LineString,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(len(table.geometryColumnTypes()), 1)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Add a new one
table.addGeometryColumnType(
QgsWkbTypes.LineString,
QgsCoordinateReferenceSystem.fromEpsgId(4326))
self.assertEqual(len(table.geometryColumnTypes()), 2)
ct = table.geometryColumnTypes()[0]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(3857))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
ct = table.geometryColumnTypes()[1]
self.assertEqual(ct.crs,
QgsCoordinateReferenceSystem.fromEpsgId(4326))
self.assertEqual(ct.wkbType, QgsWkbTypes.LineString)
# Check fields
fields = conn.fields(schema, self.myNewTable)
for f in [
'string_t', 'long_t', 'double_t', 'integer_t', 'date_t',
'datetime_t', 'time_t'
]:
self.assertTrue(f in fields.names())
if capabilities & QgsAbstractDatabaseProviderConnection.AddField:
field = QgsField('short_lived_field', QVariant.Int, 'integer')
conn.addField(field, schema, self.myNewTable)
fields = conn.fields(schema, self.myNewTable)
self.assertTrue('short_lived_field' in fields.names())
if capabilities & QgsAbstractDatabaseProviderConnection.DeleteField:
conn.deleteField('short_lived_field', schema,
self.myNewTable)
# This fails on Travis for spatialite, for no particular reason
if self.providerKey == 'spatialite' and not os.environ.get(
'TRAVIS', False):
fields = conn.fields(schema, self.myNewTable)
self.assertFalse('short_lived_field' in fields.names())
# Drop table
conn.dropVectorTable(schema, self.myNewTable)
conn.dropVectorTable(schema, 'myNewAspatialTable')
table_names = self._table_names(conn.tables(schema))
self.assertFalse(self.myNewTable in table_names)
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema:
# Drop schema
conn.dropSchema(schema)
self.assertFalse(schema in conn.schemas())
conns = md.connections()
self.assertTrue(
isinstance(
list(conns.values())[0],
QgsAbstractDatabaseProviderConnection))
# Remove connection
spy_deleted = QSignalSpy(md.connectionDeleted)
md.deleteConnection('qgis_test1')
self.assertEqual(list(md.connections().values()), [])
self.assertEqual(len(spy_deleted), 1)
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.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.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.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.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())
options.primaryKeyColumns = ['id']
options.geometryColumn = 'DOES_NOT_EXIST'
vl = conn.createSqlVectorLayer(options)
self.assertFalse(vl.isValid())
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())
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.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.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.isValid())
self.assertEqual(vl.geometryType(), QgsWkbTypes.PointGeometry)
features = [f for f in vl.getFeatures()]
self.assertEqual(len(features), 1)
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 processAlgorithm(self, parameters, context, feedback):
connection_name = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
# resolve connection details to uri
try:
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
except QgsProviderConnectionException:
raise QgsProcessingException(
self.tr('Could not retrieve connection details for {}').format(
connection_name))
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
overwrite = self.parameterAsBoolean(parameters, self.OVERWRITE,
context)
createIndex = self.parameterAsBoolean(parameters, self.CREATEINDEX,
context)
convertLowerCase = self.parameterAsBoolean(parameters,
self.LOWERCASE_NAMES,
context)
dropStringLength = self.parameterAsBoolean(parameters,
self.DROP_STRING_LENGTH,
context)
forceSinglePart = self.parameterAsBoolean(parameters,
self.FORCE_SINGLEPART,
context)
primaryKeyField = self.parameterAsString(parameters, self.PRIMARY_KEY,
context) or 'id'
encoding = self.parameterAsString(parameters, self.ENCODING, context)
source = self.parameterAsSource(parameters, self.INPUT, context)
if source is None:
raise QgsProcessingException(
self.invalidSourceError(parameters, self.INPUT))
table = self.parameterAsDatabaseTableName(parameters, self.TABLENAME,
context)
if table:
table.strip()
if not table or table == '':
table = source.sourceName()
table = table.replace('.', '_')
table = table.replace(' ', '')[0:62]
providerName = 'postgres'
geomColumn = self.parameterAsString(parameters, self.GEOMETRY_COLUMN,
context)
if not geomColumn:
geomColumn = 'geom'
options = {}
if overwrite:
options['overwrite'] = True
if convertLowerCase:
options['lowercaseFieldNames'] = True
geomColumn = geomColumn.lower()
if dropStringLength:
options['dropStringConstraints'] = True
if forceSinglePart:
options['forceSinglePartGeometryType'] = True
# Clear geometry column for non-geometry tables
if source.wkbType() == QgsWkbTypes.NoGeometry:
geomColumn = None
uri = QgsDataSourceUri(conn.uri())
uri.setSchema(schema)
uri.setTable(table)
uri.setKeyColumn(primaryKeyField)
uri.setGeometryColumn(geomColumn)
if encoding:
options['fileEncoding'] = encoding
exporter = QgsVectorLayerExporter(uri.uri(), providerName,
source.fields(), source.wkbType(),
source.sourceCrs(), overwrite,
options)
if exporter.errorCode() != QgsVectorLayerExporter.NoError:
raise QgsProcessingException(
self.tr('Error importing to PostGIS\n{0}').format(
exporter.errorMessage()))
features = source.getFeatures()
total = 100.0 / source.featureCount() if source.featureCount() else 0
for current, f in enumerate(features):
if feedback.isCanceled():
break
if not exporter.addFeature(f, QgsFeatureSink.FastInsert):
feedback.reportError(exporter.errorMessage())
feedback.setProgress(int(current * total))
exporter.flushBuffer()
if exporter.errorCode() != QgsVectorLayerExporter.NoError:
raise QgsProcessingException(
self.tr('Error importing to PostGIS\n{0}').format(
exporter.errorMessage()))
if geomColumn and createIndex:
try:
options = QgsAbstractDatabaseProviderConnection.SpatialIndexOptions(
)
options.geometryColumnName = geomColumn
conn.createSpatialIndex(schema, table, options)
except QgsProviderConnectionException as e:
raise QgsProcessingException(
self.tr('Error creating spatial index:\n{0}').format(e))
try:
conn.vacuum(schema, table)
except QgsProviderConnectionException as e:
feedback.reportError(
self.tr('Error vacuuming table:\n{0}').format(e))
return {}
