def _test_operations(self, md, conn):
"""Common tests"""
capabilities = conn.capabilities()
# Schema operations
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema and
capabilities & QgsAbstractDatabaseProviderConnection.Schemas and
capabilities & QgsAbstractDatabaseProviderConnection.RenameSchema and
capabilities & QgsAbstractDatabaseProviderConnection.DropSchema):
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema and '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')
# Rename
conn.renameSchema('myNewSchema', 'myVeryNewSchema')
schemas = conn.schemas()
self.assertTrue('myVeryNewSchema' in schemas)
self.assertFalse('myNewSchema' in schemas)
# Drop
conn.dropSchema('myVeryNewSchema')
schemas = conn.schemas()
self.assertFalse('myVeryNewSchema' in schemas)
# Table operations
if (capabilities & QgsAbstractDatabaseProviderConnection.CreateVectorTable and
capabilities & QgsAbstractDatabaseProviderConnection.Tables and
capabilities & QgsAbstractDatabaseProviderConnection.RenameVectorTable and
capabilities & QgsAbstractDatabaseProviderConnection.DropVectorTable):
if capabilities & QgsAbstractDatabaseProviderConnection.DropSchema and 'myNewSchema' in conn.schemas():
conn.dropSchema('myNewSchema', True)
if capabilities & QgsAbstractDatabaseProviderConnection.CreateSchema:
schema = 'myNewSchema'
conn.createSchema('myNewSchema')
else:
schema = 'public'
if 'myNewTable' in self._table_names(conn.tables(schema)):
conn.dropVectorTable(schema, 'myNewTable')
fields = QgsFields()
fields.append(QgsField("string", QVariant.String))
fields.append(QgsField("long", QVariant.LongLong))
fields.append(QgsField("double", QVariant.Double))
fields.append(QgsField("integer", QVariant.Int))
fields.append(QgsField("date", QVariant.Date))
fields.append(QgsField("datetime", QVariant.DateTime))
fields.append(QgsField("time", 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)
# 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)
self.assertEqual(table_property.geometryColumnTypes()[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')
self.assertEqual(table_property.geometryColumnTypes()[0].wkbType, QgsWkbTypes.NoGeometry)
self.assertFalse(table_property.geometryColumnTypes()[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'
sql = "INSERT INTO %s (string, long, double, integer, date, datetime, time) VALUES ('QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12', '12:00:13.00')" % table
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT string, long, double, integer, date, datetime FROM %s" % table
res = conn.executeSql(sql)
# GPKG has no type for time and spatialite has no support for dates and time ...
if self.providerKey == 'spatialite':
self.assertEqual(res, [['QGIS Rocks - \U0001f604', 666, 1.234, 1234, '2019-07-08', '2019-07-08T12:00:12']])
else:
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 FROM %s" % table
res = conn.executeSql(sql)
self.assertIn(res, ([[QtCore.QTime(12, 0, 13)]], [['12:00:13.00']]))
sql = "DELETE FROM %s WHERE string = 'QGIS Rocks - \U0001f604'" % table
res = conn.executeSql(sql)
self.assertEqual(res, [])
sql = "SELECT string, integer 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 != 'spatialite':
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)
# Rename
conn.renameVectorTable(schema, 'myNewTable', 'myVeryNewTable')
tables = self._table_names(conn.tables(schema))
self.assertFalse('myNewTable' in tables)
self.assertTrue('myVeryNewTable' in tables)
# Vacuum
if capabilities & QgsAbstractDatabaseProviderConnection.Vacuum:
conn.vacuum('myNewSchema', 'myVeryNewTable')
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), 'myVeryNewTable')
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, 'myVeryNewTable')
conn.dropVectorTable(schema, 'myNewAspatialTable')
table_names = self._table_names(conn.tables(schema))
self.assertFalse('myVeryNewTable' 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
md.deleteConnection('qgis_test1')
self.assertEqual(list(md.connections().values()), [])
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)
row_count = res.rowCount()
# Some providers do not support rowCount and return -1
if row_count != -1:
self.assertEqual(row_count, 1)
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'
])
self.assertEqual(res.fetchedRowCount(), 1)
# 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 don't
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_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 has no type for time and spatialite has no support for dates and time ...
if self.providerKey == 'spatialite':
self.assertEqual(res, [[
'QGIS Rocks - \U0001f604', 666, 1.234, 1234,
'2019-07-08', '2019-07-08T12:00:12'
]])
else:
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)
