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 processAction(self, task): """ :type task: QUrl """ self.__mCurrentUrl = task taskMap = self.__parseTask(task) if taskMap[u"action"] == u"showText": QApplication.setOverrideCursor(QtCore.Qt.WaitCursor) t = QtCore.QTime() t.start() html = self.__documentContent(taskMap, self.ExportFormat.RichText) qDebug("Total time elapsed: {} ms".format(t.elapsed())) QApplication.restoreOverrideCursor() self.setHtml(html) record = HistoryRecord() record.html = html record.parIds = self.__mDocumentBuilder.currentParIds() record.budIds = self.__mDocumentBuilder.currentBudIds() record.definitionPoint = self.__mDocumentBuilder.currentDefinitionPoint( ) self.updateHistory.emit(record) elif taskMap[u"action"] == u"selectInMap": self.showParcely.emit(taskMap[u'ids'].split(u',')) elif taskMap[u"action"] == u"switchPanel": if taskMap[u"panel"] == u"import": self.switchToPanelImport.emit() elif taskMap[u"panel"] == u"search": self.switchToPanelSearch.emit(int(taskMap[u'type'])) elif taskMap[u"panel"] == u"changes": self.switchToPanelChanges.emit() self.setHtml(self.__mCurrentRecord.html) else: qDebug("..Jina akce")
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()), [])