def setupUi(self, Form):
Form.setObjectName("Form")
Form.resize(400, 67)
self.gridLayout = QtWidgets.QGridLayout(Form)
self.gridLayout.setObjectName("gridLayout")
self.label_start = QtWidgets.QLabel(Form)
self.label_start.setObjectName("label_start")
self.gridLayout.addWidget(self.label_start, 0, 0, 1, 1)
self.label_end = QtWidgets.QLabel(Form)
self.label_end.setObjectName("label_end")
self.gridLayout.addWidget(self.label_end, 0, 1, 1, 1)
self.date_start = gui.QgsDateTimeEdit(Form)
self.date_start.setMinimumDateTime(QtCore.QDateTime(QtCore.QDate(1970, 1, 1), QtCore.QTime(0, 0, 0)))
self.date_start.setMinimumDate(QtCore.QDate(1970, 1, 1))
self.date_start.setCurrentSection(QtWidgets.QDateTimeEdit.YearSection)
self.date_start.setTimeSpec(QtCore.Qt.UTC)
self.date_start.setObjectName("date_start")
self.gridLayout.addWidget(self.date_start, 1, 0, 1, 1)
self.date_end = gui.QgsDateTimeEdit(Form)
self.date_end.setMinimumDateTime(QtCore.QDateTime(QtCore.QDate(1970, 1, 1), QtCore.QTime(0, 0, 0)))
self.date_end.setMinimumDate(QtCore.QDate(1970, 1, 1))
self.date_end.setCurrentSection(QtWidgets.QDateTimeEdit.YearSection)
self.date_end.setObjectName("date_end")
self.gridLayout.addWidget(self.date_end, 1, 1, 1, 1)
self.date_start.clear()
self.date_end.clear()
self.retranslateUi(Form)
QtCore.QMetaObject.connectSlotsByName(Form)
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)
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.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)
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()
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'
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()), [])
),
pytest.param(
geonode_v3.GeonodeApiClientVersion_3_4_0,
models.GeonodeApiSearchFilters(keyword="fake-keyword"),
"page=1&page_size=10&filter%7Bkeywords.name.icontains%7D=fake-keyword",
),
pytest.param(
geonode_v3.GeonodeApiClientVersion_3_4_0,
models.GeonodeApiSearchFilters(
topic_category=models.IsoTopicCategory.biota),
"page=1&page_size=10&filter%7Bcategory.identifier%7D=biota",
),
pytest.param(
geonode_v3.GeonodeApiClientVersion_3_4_0,
models.GeonodeApiSearchFilters(
temporal_extent_start=QtCore.QDateTime(2021, 7, 31, 10, 22)),
"page=1&page_size=10&filter%7Btemporal_extent_start.gte%7D=2021-07-31T10:22:00",
),
pytest.param(
geonode_v3.GeonodeApiClientVersion_3_4_0,
models.GeonodeApiSearchFilters(
temporal_extent_end=QtCore.QDateTime(2021, 7, 31, 10, 22)),
"page=1&page_size=10&filter%7Btemporal_extent_end.lte%7D=2021-07-31T10:22:00",
),
pytest.param(
geonode_v3.GeonodeApiClientVersion_3_4_0,
models.GeonodeApiSearchFilters(
publication_date_start=QtCore.QDateTime(2021, 7, 31, 10, 22)),
"page=1&page_size=10&filter%7Bdate.gte%7D=2021-07-31T10:22:00",
),
pytest.param(
