def onUpdateSqlLayer(self): l = self.iface.activeLayer() if l.dataProvider().name() in ['postgres', 'spatialite', 'oracle']: table = QgsDataSourceUri(l.source()).table() if table.startswith('(') and table.endswith(')'): self.run() self.dlg.runSqlLayerWindow(l)
def onUpdateSqlLayer(self): l = self.iface.activeLayer() if l.dataProvider().name() in ['postgres', 'spatialite', 'oracle']: uri = QgsDataSourceUri(l.source()) if re.search('^\(SELECT .+ FROM .+\)$', uri.table(), re.S): self.run() self.dlg.runSqlLayerWindow(l)
def connect(self, selected, parent=None): settings = QSettings() settings.beginGroup(u"/%s/connections/%s" % (self.getSettingsKey(), selected)) if not settings.contains("database"): # non-existent entry? raise DbError('there is no defined database connection "%s".' % selected) get_value_str = lambda x: str(settings.value(x) if Utils.isSIPv2() else settings.value(x).toString()) service, host, port, database, username, password = list(map(get_value_str, ["service", "host", "port", "database", "username", "password"])) # qgis1.5 use 'savePassword' instead of 'save' setting isSave = settings.value("save") if Utils.isSIPv2() else settings.value("save").toBool() isSavePassword = settings.value("savePassword") if Utils.isSIPv2() else settings.value("savePassword").toBool() if not (isSave or isSavePassword): (password, ok) = QInputDialog.getText(parent, "Enter password", 'Enter password for connection "%s":' % selected, QLineEdit.Password) if not ok: return settings.endGroup() uri = QgsDataSourceUri() if service: uri.setConnection(service, database, username, password) else: uri.setConnection(host, port, database, username, password) return Connection(uri)
def onUpdateSqlLayer(self): l = self.iface.legendInterface().currentLayer() if l.dataProvider().name() in ["postgres", "spatialite", "oracle"]: uri = QgsDataSourceUri(l.source()) if re.search("^\(SELECT .+ FROM .+\)$", uri.table(), re.S): self.run() self.dlg.runSqlLayerWindow(l)
def _clearSslTempCertsIfAny(self, connectionInfo): # remove certs (if any) of the connectionInfo expandedUri = QgsDataSourceUri(connectionInfo) def removeCert(certFile): certFile = certFile.replace("'", "") file = QFile(certFile) # set permission to allow removing on Win. # On linux and Mac if file is set with QFile::>ReadUser # does not create problem removing certs if not file.setPermissions(QFile.WriteOwner): raise Exception('Cannot change permissions on {}: error code: {}'.format(file.fileName(), file.error())) if not file.remove(): raise Exception('Cannot remove {}: error code: {}'.format(file.fileName(), file.error())) sslCertFile = expandedUri.param("sslcert") if sslCertFile: removeCert(sslCertFile) sslKeyFile = expandedUri.param("sslkey") if sslKeyFile: removeCert(sslKeyFile) sslCAFile = expandedUri.param("sslrootcert") if sslCAFile: removeCert(sslCAFile)
def testTableDataModel(self): connection_name = 'testTableDataModel' plugin = createDbPlugin('spatialite') uri = QgsDataSourceUri() uri.setDatabase(self.test_spatialite) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('spatialite', connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] self.assertEqual(table.name, 'testlayer') model = table.tableDataModel(None) self.assertEqual(model.rowCount(), 1) self.assertEqual(model.getData(0, 0), 1) # fid wkb = model.getData(0, 1) geometry = ogr.CreateGeometryFromWkb(wkb) self.assertEqual(geometry.ExportToWkt(), 'LINESTRING (1 2,3 4)') self.assertEqual(model.getData(0, 2), 'foo') connection.remove()
def testListLayer(self): connection_name = 'testListLayer' plugin = createDbPlugin('gpkg') uri = QgsDataSourceUri() uri.setDatabase(self.test_gpkg) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('gpkg', connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] self.assertEqual(table.name, 'testLayer') info = table.info() expected_html = """<div class="section"><h2>General info</h2><div><table><tr><td>Relation type: </td><td>Table </td></tr><tr><td>Rows: </td><td>1 </td></tr></table></div></div><div class="section"><h2>GeoPackage</h2><div><table><tr><td>Column: </td><td>geom </td></tr><tr><td>Geometry: </td><td>LINESTRING </td></tr><tr><td>Dimension: </td><td>XY </td></tr><tr><td>Spatial ref: </td><td>Undefined (-1) </td></tr><tr><td>Extent: </td><td>1.00000, 2.00000 - 3.00000, 4.00000 </td></tr></table><p><warning> No spatial index defined (<a href="action:spatialindex/create">create it</a>)</p></div></div><div class="section"><h2>Fields</h2><div><table class="header"><tr><th># </th><th>Name </th><th>Type </th><th>Null </th><th>Default </th></tr><tr><td>0 </td><td class="underline">fid </td><td>INTEGER </td><td>Y </td><td> </td></tr><tr><td>1 </td><td>geom </td><td>LINESTRING </td><td>Y </td><td> </td></tr><tr><td>2 </td><td>text_field </td><td>TEXT </td><td>Y </td><td> </td></tr></table></div></div>""" # GDAL 2.2.0 expected_html_2 = """<div class="section"><h2>General info</h2><div><table><tr><td>Relation type: </td><td>Table </td></tr><tr><td>Rows: </td><td>1 </td></tr></table></div></div><div class="section"><h2>GeoPackage</h2><div><table><tr><td>Column: </td><td>geom </td></tr><tr><td>Geometry: </td><td>LINESTRING </td></tr><tr><td>Dimension: </td><td>XY </td></tr><tr><td>Spatial ref: </td><td>Undefined (-1) </td></tr><tr><td>Extent: </td><td>1.00000, 2.00000 - 3.00000, 4.00000 </td></tr></table><p><warning> No spatial index defined (<a href="action:spatialindex/create">create it</a>)</p></div></div><div class="section"><h2>Fields</h2><div><table class="header"><tr><th># </th><th>Name </th><th>Type </th><th>Null </th><th>Default </th></tr><tr><td>0 </td><td class="underline">fid </td><td>INTEGER </td><td>N </td><td> </td></tr><tr><td>1 </td><td>geom </td><td>LINESTRING </td><td>Y </td><td> </td></tr><tr><td>2 </td><td>text_field </td><td>TEXT </td><td>Y </td><td> </td></tr></table></div></div><div class="section"><h2>Triggers</h2><div><table class="header"><tr><th>Name </th><th>Function </th></tr><tr><td>trigger_insert_feature_count_testLayer (<a href="action:trigger/trigger_insert_feature_count_testLayer/delete">delete</a>) </td><td>CREATE TRIGGER "trigger_insert_feature_count_testLayer" AFTER INSERT ON "testLayer" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE table_name = 'testLayer'; END </td></tr><tr><td>trigger_delete_feature_count_testLayer (<a href="action:trigger/trigger_delete_feature_count_testLayer/delete">delete</a>) </td><td>CREATE TRIGGER "trigger_delete_feature_count_testLayer" AFTER DELETE ON "testLayer" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE table_name = 'testLayer'; END </td></tr></table></div></div>""" # GDAL 2.3.0 expected_html_3 = """<div class="section"><h2>General info</h2><div><table><tr><td>Relation type: </td><td>Table </td></tr><tr><td>Rows: </td><td>1 </td></tr></table></div></div><div class="section"><h2>GeoPackage</h2><div><table><tr><td>Column: </td><td>geom </td></tr><tr><td>Geometry: </td><td>LINESTRING </td></tr><tr><td>Dimension: </td><td>XY </td></tr><tr><td>Spatial ref: </td><td>Undefined (-1) </td></tr><tr><td>Extent: </td><td>1.00000, 2.00000 - 3.00000, 4.00000 </td></tr></table><p><warning> No spatial index defined (<a href="action:spatialindex/create">create it</a>)</p></div></div><div class="section"><h2>Fields</h2><div><table class="header"><tr><th># </th><th>Name </th><th>Type </th><th>Null </th><th>Default </th></tr><tr><td>0 </td><td class="underline">fid </td><td>INTEGER </td><td>N </td><td> </td></tr><tr><td>1 </td><td>geom </td><td>LINESTRING </td><td>Y </td><td> </td></tr><tr><td>2 </td><td>text_field </td><td>TEXT </td><td>Y </td><td> </td></tr></table></div></div><div class="section"><h2>Triggers</h2><div><table class="header"><tr><th>Name </th><th>Function </th></tr><tr><td>trigger_insert_feature_count_testLayer (<a href="action:trigger/trigger_insert_feature_count_testLayer/delete">delete</a>) </td><td>CREATE TRIGGER "trigger_insert_feature_count_testLayer" AFTER INSERT ON "testLayer" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('testLayer'); END </td></tr><tr><td>trigger_delete_feature_count_testLayer (<a href="action:trigger/trigger_delete_feature_count_testLayer/delete">delete</a>) </td><td>CREATE TRIGGER "trigger_delete_feature_count_testLayer" AFTER DELETE ON "testLayer" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('testLayer'); END </td></tr></table></div></div>""" self.assertIn(info.toHtml(), [expected_html, expected_html_2, expected_html_3]) connection.remove()
def testNonSpatial(self): connection_name = 'testNonSpatial' plugin = createDbPlugin('gpkg') uri = QgsDataSourceUri() test_gpkg = os.path.join(self.basetestpath, 'testNonSpatial.gpkg') ds = ogr.GetDriverByName('GPKG').CreateDataSource(test_gpkg) lyr = ds.CreateLayer('testNonSpatial', geom_type=ogr.wkbNone) lyr.CreateField(ogr.FieldDefn('text_field', ogr.OFTString)) f = ogr.Feature(lyr.GetLayerDefn()) f['text_field'] = 'foo' lyr.CreateFeature(f) f = None ds = None uri.setDatabase(test_gpkg) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('gpkg', connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] self.assertEqual(table.name, 'testNonSpatial') info = table.info() expected_html = """<div class="section"><h2>General info</h2><div><table><tr><td>Relation type: </td><td>Table </td></tr><tr><td>Rows: </td><td>1 </td></tr></table></div></div><div class="section"><h2>Fields</h2><div><table class="header"><tr><th># </th><th>Name </th><th>Type </th><th>Null </th><th>Default </th></tr><tr><td>0 </td><td class="underline">fid </td><td>INTEGER </td><td>Y </td><td> </td></tr><tr><td>1 </td><td>text_field </td><td>TEXT </td><td>Y </td><td> </td></tr></table></div></div>""" self.assertEqual(info.toHtml(), expected_html, info.toHtml()) connection.remove()
def testWfsSettings(self): uri = QgsDataSourceUri() QgsOwsConnection.addWfsConnectionSettings(uri, 'qgis/connections-wfs/test/') self.assertEqual(uri.param('version'), '1.1.0') self.assertEqual(uri.param('maxNumFeatures'), '47') self.assertEqual(uri.param('IgnoreAxisOrientation'), '1') self.assertEqual(uri.param('InvertAxisOrientation'), '1')
def dropMimeData(self, data, action, row, column, parent): global isImportVectorAvail if action == Qt.IgnoreAction: return True # vectors/tables to be imported must be dropped on connected db, schema or table canImportLayer = isImportVectorAvail and parent.isValid() and \ (isinstance(parent.internalPointer(), (SchemaItem, TableItem)) or (isinstance(parent.internalPointer(), ConnectionItem) and parent.internalPointer().populated)) added = 0 if data.hasUrls(): for u in data.urls(): filename = u.toLocalFile() if filename == "": continue if self.hasSpatialiteSupport: from .db_plugins.spatialite.connector import SpatiaLiteDBConnector if SpatiaLiteDBConnector.isValidDatabase(filename): # retrieve the SL plugin tree item using its path index = self._rPath2Index(["spatialite"]) if not index.isValid(): continue item = index.internalPointer() conn_name = QFileInfo(filename).fileName() uri = QgsDataSourceUri() uri.setDatabase(filename) item.getItemData().addConnection(conn_name, uri) item.changed.emit() added += 1 continue if canImportLayer: if QgsRasterLayer.isValidRasterFileName(filename): layerType = 'raster' providerKey = 'gdal' else: layerType = 'vector' providerKey = 'ogr' layerName = QFileInfo(filename).completeBaseName() if self.importLayer(layerType, providerKey, layerName, filename, parent): added += 1 if data.hasFormat(self.QGIS_URI_MIME): for uri in QgsMimeDataUtils.decodeUriList(data): if canImportLayer: if self.importLayer(uri.layerType, uri.providerKey, uri.name, uri.uri, parent): added += 1 return added > 0
def postgis_path_to_uri(path): """Convert layer path from QgsBrowserModel to full QgsDataSourceUri. :param path: The layer path from QgsBrowserModel :type path: string :returns: layer uri. :rtype: QgsDataSourceUri """ connection_name = path.split('/')[1] schema = path.split('/')[2] table_name = path.split('/')[3] settings = QSettings() key = "/PostgreSQL/connections/" + connection_name service = settings.value(key + "/service") host = settings.value(key + "/host") port = settings.value(key + "/port") if not port: port = "5432" db = settings.value(key + "/database") use_estimated_metadata = settings.value( key + "/estimatedMetadata", False, type=bool) sslmode = settings.value( key + "/sslmode", QgsDataSourceUri.SSLprefer, type=int) username = "" password = "" if settings.value(key + "/saveUsername") == "true": username = settings.value(key + "/username") if settings.value(key + "/savePassword") == "true": password = settings.value(key + "/password") # Old save setting if settings.contains(key + "/save"): username = settings.value(key + "/username") if settings.value(key + "/save") == "true": password = settings.value(key + "/password") uri = QgsDataSourceUri() if service: uri.setConnection(service, db, username, password, sslmode) else: uri.setConnection(host, port, db, username, password, sslmode) uri.setUseEstimatedMetadata(use_estimated_metadata) # Obtain the geometry column name connector = PostGisDBConnector(uri) tables = connector.getVectorTables(schema) tables = [table for table in tables if table[1] == table_name] if not tables: return None table = tables[0] geom_col = table[8] uri.setDataSource(schema, table_name, geom_col) return uri
def testExecuteRegExp(self): """This test checks for REGEXP syntax support, which is enabled in Qgis.utils' spatialite_connection()""" connection_name = 'testListLayer' plugin = createDbPlugin('spatialite') uri = QgsDataSourceUri() uri.setDatabase(self.test_spatialite) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('spatialite', connection_name) connection.connect() db = connection.database() db.connector._execute(None, 'SELECT \'ABC\' REGEXP \'[CBA]\'')
def connect(self, parent=None): conn_name = self.connectionName() settings = QgsSettings() settings.beginGroup(u"/%s/%s" % (self.connectionSettingsKey(), conn_name)) if not settings.contains("sqlitepath"): # non-existent entry? raise InvalidDataException(self.tr(u'There is no defined database connection "{0}".').format(conn_name)) database = settings.value("sqlitepath") uri = QgsDataSourceUri() uri.setDatabase(database) return self.connectToUri(uri)
def processAlgorithm(self, feedback): database = self.getParameterValue(self.DATABASE) uri = QgsDataSourceUri(database) if uri.database() is "": if "|layerid" in database: database = database[: database.find("|layerid")] uri = QgsDataSourceUri("dbname='%s'" % (database)) self.db = spatialite.GeoDB(uri) sql = self.getParameterValue(self.SQL).replace("\n", " ") try: self.db._exec_sql_and_commit(str(sql)) except spatialite.DbError as e: raise GeoAlgorithmExecutionException(self.tr("Error executing SQL:\n%s") % str(e))
def addConnectionActionSlot(self, item, action, parent, index): QApplication.restoreOverrideCursor() try: filename, selected_filter = QFileDialog.getOpenFileName(parent, "Choose SQLite/SpatiaLite file") if not filename: return finally: QApplication.setOverrideCursor(Qt.WaitCursor) conn_name = QFileInfo(filename).fileName() uri = QgsDataSourceUri() uri.setDatabase(filename) self.addConnection(conn_name, uri) index.internalPointer().itemChanged()
def processAlgorithm(self, progress): database = self.getParameterValue(self.DATABASE) uri = QgsDataSourceUri(database) if uri.database() is '': if '|layerid' in database: database = database[:database.find('|layerid')] uri = QgsDataSourceUri('dbname=\'%s\'' % (database)) self.db = spatialite.GeoDB(uri) sql = self.getParameterValue(self.SQL).replace('\n', ' ') try: self.db._exec_sql_and_commit(str(sql)) except spatialite.DbError as e: raise GeoAlgorithmExecutionException( self.tr('Error executing SQL:\n%s') % str(e))
def importLayer(self, layerType, providerKey, layerName, uriString, parent): global isImportVectorAvail if not isImportVectorAvail: return False if layerType == 'raster': return False # not implemented yet inLayer = QgsRasterLayer(uriString, layerName, providerKey) else: inLayer = QgsVectorLayer(uriString, layerName, providerKey) if not inLayer.isValid(): # invalid layer QMessageBox.warning(None, self.tr("Invalid layer"), self.tr("Unable to load the layer %s") % inLayer.name()) return False # retrieve information about the new table's db and schema outItem = parent.internalPointer() outObj = outItem.getItemData() outDb = outObj.database() outSchema = None if isinstance(outItem, SchemaItem): outSchema = outObj elif isinstance(outItem, TableItem): outSchema = outObj.schema() # toIndex will point to the parent item of the new table toIndex = parent if isinstance(toIndex.internalPointer(), TableItem): toIndex = toIndex.parent() if inLayer.type() == inLayer.VectorLayer: # create the output uri schema = outSchema.name if outDb.schemas() is not None and outSchema is not None else "" pkCol = geomCol = "" # default pk and geom field name value if providerKey in ['postgres', 'spatialite']: inUri = QgsDataSourceUri(inLayer.source()) pkCol = inUri.keyColumn() geomCol = inUri.geometryColumn() outUri = outDb.uri() outUri.setDataSource(schema, layerName, geomCol, "", pkCol) self.importVector.emit(inLayer, outDb, outUri, toIndex) return True return False
def copy(self, target_path, copied_files, keep_existent=False): """ Copy a layer to a new path and adjust its datasource. :param layer: The layer to copy :param target_path: A path to a folder into which the data will be copied :param keep_existent: if True and target file already exists, keep it as it is """ if not self.is_file: # Copy will also be called on non-file layers like WMS. In this case, just do nothing. return layer_name_suffix = '' # Shapefiles and GeoPackages have the path in the source uri_parts = self.layer.source().split('|', 1) file_path = uri_parts[0] if len(uri_parts) > 1: layer_name_suffix = uri_parts[1] # Spatialite have the path in the table part of the uri uri = QgsDataSourceUri(self.layer.dataProvider().dataSourceUri()) if os.path.isfile(file_path): source_path, file_name = os.path.split(file_path) basename, extensions = get_file_extension_group(file_name) for ext in extensions: dest_file = os.path.join(target_path, basename + ext) if os.path.exists(os.path.join(source_path, basename + ext)) and \ (keep_existent is False or not os.path.isfile(dest_file)): shutil.copy(os.path.join(source_path, basename + ext), dest_file) new_source = os.path.join(target_path, file_name) if layer_name_suffix: new_source = new_source + '|' + layer_name_suffix self._change_data_source(new_source) # Spatialite files have a uri else: file_path = uri.database() if os.path.isfile(file_path): source_path, file_name = os.path.split(file_path) basename, extensions = get_file_extension_group(file_name) for ext in extensions: dest_file = os.path.join(target_path, basename + ext) if os.path.exists(os.path.join(source_path, basename + ext)) and \ (keep_existent is False or not os.path.isfile(dest_file)): shutil.copy(os.path.join(source_path, basename + ext), dest_file) uri.setDatabase(os.path.join(target_path, file_name)) self._change_data_source(uri.uri()) return copied_files
def testCreateRenameDeleteFields(self): if not self.supportsAlterFieldDefn: return connection_name = 'testCreateRenameDeleteFields' plugin = createDbPlugin('spatialite') uri = QgsDataSourceUri() test_spatialite_new = os.path.join(self.basetestpath, 'testCreateRenameDeleteFields.spatialite') shutil.copy(self.test_spatialite, test_spatialite_new) uri.setDatabase(test_spatialite_new) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('spatialite', connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] field_before_count = len(table.fields()) field = TableField(table) field.name = 'real_field' field.dataType = 'DOUBLE' self.assertTrue(table.addField(field)) self.assertEqual(len(table.fields()), field_before_count + 1) # not supported in spatialite # self.assertTrue(field.update('real_field2', new_type_str='TEXT (30)', new_not_null=True, new_default_str='foo')) field = table.fields()[field_before_count] self.assertEqual(field.name, 'real_field') self.assertEqual(field.dataType, 'DOUBLE') # self.assertEqual(field.notNull, 1) # self.assertEqual(field.default, "'foo'") # self.assertTrue(table.deleteField(field)) # self.assertEqual(len(table.fields()), field_before_count) connection.remove()
def encode_uri(ds_uri, schema_name, project_name=None): u = QUrl() urlQuery = QUrlQuery() u.setScheme("postgresql") u.setHost(ds_uri.host()) if ds_uri.port() != '': u.setPort(int(ds_uri.port())) if ds_uri.username() != '': u.setUserName(ds_uri.username()) if ds_uri.password() != '': u.setPassword(ds_uri.password()) if ds_uri.service() != '': urlQuery.addQueryItem("service", ds_uri.service()) if ds_uri.authConfigId() != '': urlQuery.addQueryItem("authcfg", ds_uri.authConfigId()) if ds_uri.sslMode() != QgsDataSourceUri.SslPrefer: urlQuery.addQueryItem("sslmode", QgsDataSourceUri.encodeSslMode(ds_uri.sslMode())) urlQuery.addQueryItem("dbname", ds_uri.database()) urlQuery.addQueryItem("schema", schema_name) if project_name: urlQuery.addQueryItem("project", project_name) u.setQuery(urlQuery) return str(u.toEncoded(), 'utf-8')
def setUpProvider(cls, authId): cls.dbconn = 'dbname=\'qgis_test\'' if 'QGIS_PGTEST_DB' in os.environ: cls.dbconn = os.environ['QGIS_PGTEST_DB'] uri = QgsDataSourceUri() uri.setConnection("localhost", cls.port, cls.dbname, "", "", QgsDataSourceUri.SslVerifyFull, authId) uri.setKeyColumn('pk') uri.setSrid('EPSG:4326') uri.setDataSource('qgis_test', 'someData', "geom", "", "pk") provider = QgsProviderRegistry.instance().createProvider('postgres', uri.uri(False)) if provider is None: raise Exception("cannot create postgres provider") if not provider.isValid(): raise Exception("Created postgres provider is not valid: {}".format(str(provider.errors()))) # save provider config that is the way how db_manager is aware of a PG connection cls.addConnectionConfig(TEST_CONNECTION_NAME, uri)
def testCreateRenameDeleteFields(self): if not self.supportsAlterFieldDefn: return connection_name = "testCreateRenameDeleteFields" plugin = createDbPlugin("gpkg") uri = QgsDataSourceUri() test_gpkg_new = os.path.join(self.basetestpath, "testCreateRenameDeleteFields.gpkg") shutil.copy(self.test_gpkg, test_gpkg_new) uri.setDatabase(test_gpkg_new) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin("gpkg", connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] field_before_count = len(table.fields()) field = TableField(table) field.name = "real_field" field.dataType = "DOUBLE" self.assertTrue(table.addField(field)) self.assertEqual(len(table.fields()), field_before_count + 1) self.assertTrue(field.update("real_field2", new_type_str="TEXT (30)", new_not_null=True, new_default_str="foo")) field = table.fields()[field_before_count] self.assertEqual(field.name, "real_field2") self.assertEqual(field.dataType, "TEXT(30)") self.assertEqual(field.notNull, 1) self.assertEqual(field.default, "'foo'") self.assertTrue(table.deleteField(field)) self.assertEqual(len(table.fields()), field_before_count) connection.remove()
def processAlgorithm(self, parameters, context, feedback): database = self.parameterAsVectorLayer(parameters, self.DATABASE, context) databaseuri = database.dataProvider().dataSourceUri() uri = QgsDataSourceUri(databaseuri) if uri.database() is '': if '|layerid' in databaseuri: databaseuri = databaseuri[:databaseuri.find('|layerid')] uri = QgsDataSourceUri('dbname=\'%s\'' % (databaseuri)) db = spatialite.GeoDB(uri) sql = self.parameterAsString(parameters, self.SQL, context).replace('\n', ' ') try: db._exec_sql_and_commit(str(sql)) except spatialite.DbError as e: raise GeoAlgorithmExecutionException( self.tr('Error executing SQL:\n{0}').format(str(e))) return {}
def uri_from_name(conn_name): settings = QgsSettings() settings.beginGroup(u"/PostgreSQL/connections/%s" % conn_name) if not settings.contains("database"): # non-existent entry? raise QgsProcessingException(QCoreApplication.translate("PostGIS", 'There is no defined database connection "{0}".').format(conn_name)) uri = QgsDataSourceUri() settingsList = ["service", "host", "port", "database", "username", "password", "authcfg"] service, host, port, database, username, password, authcfg = [settings.value(x, "", type=str) for x in settingsList] useEstimatedMetadata = settings.value("estimatedMetadata", False, type=bool) try: sslmode = settings.value("sslmode", QgsDataSourceUri.SslPrefer, type=int) except TypeError: sslmode = QgsDataSourceUri.SslPrefer settings.endGroup() if hasattr(authcfg, 'isNull') and authcfg.isNull(): authcfg = '' if service: uri.setConnection(service, database, username, password, sslmode, authcfg) else: uri.setConnection(host, port, database, username, password, sslmode, authcfg) uri.setUseEstimatedMetadata(useEstimatedMetadata) return uri
def uri_from_name(conn_name): settings = QSettings() settings.beginGroup(u"/PostgreSQL/connections/%s" % conn_name) if not settings.contains("database"): # non-existent entry? raise DbError('There is no defined database connection "%s".' % conn_name) uri = QgsDataSourceUri() settingsList = ["service", "host", "port", "database", "username", "password", "authcfg"] service, host, port, database, username, password, authcfg = [settings.value(x, "", type=str) for x in settingsList] useEstimatedMetadata = settings.value("estimatedMetadata", False, type=bool) sslmode = settings.value("sslmode", QgsDataSourceUri.SSLprefer, type=int) settings.endGroup() if service: uri.setConnection(service, database, username, password, sslmode, authcfg) else: uri.setConnection(host, port, database, username, password, sslmode, authcfg) uri.setUseEstimatedMetadata(useEstimatedMetadata) return uri
def getConnectorFromUri(connectionParams: Dict[str,str]) -> 'db_manager.db_plugins.DBConnector': ''' Set connector property for the given database type and parameters ''' connector = None uri = QgsDataSourceUri() if connectionParams['dbType'] == 'postgis': if connectionParams['host']: uri.setConnection( connectionParams['host'], connectionParams['port'], connectionParams['dbname'], connectionParams['user'], connectionParams['password'] ) if connectionParams['service']: uri.setConnection( connectionParams['service'], connectionParams['dbname'], connectionParams['user'], connectionParams['password'] ) connector = PostGisDBConnector(uri) if connectionParams['dbType'] == 'spatialite': uri.setConnection('', '', connectionParams['dbname'], '', '') if hasSpatialiteSupport(): from db_manager.db_plugins.spatialite.connector import SpatiaLiteDBConnector connector = SpatiaLiteDBConnector(uri) return connector
def connect(self, parent=None): conn_name = self.connectionName() settings = QgsSettings() settings.beginGroup(u"/%s/%s" % (self.connectionSettingsKey(), conn_name)) if not settings.contains("database"): # non-existent entry? raise InvalidDataException(self.tr('There is no defined database connection "{0}".').format(conn_name)) from qgis.core import QgsDataSourceUri uri = QgsDataSourceUri() settingsList = ["service", "host", "port", "database", "username", "password", "authcfg"] service, host, port, database, username, password, authcfg = [settings.value(x, "", type=str) for x in settingsList] useEstimatedMetadata = settings.value("estimatedMetadata", False, type=bool) sslmode = settings.value("sslmode", QgsDataSourceUri.SslPrefer, type=int) settings.endGroup() if hasattr(authcfg, 'isNull') and authcfg.isNull(): authcfg = '' if service: uri.setConnection(service, database, username, password, sslmode, authcfg) else: uri.setConnection(host, port, database, username, password, sslmode, authcfg) uri.setUseEstimatedMetadata(useEstimatedMetadata) try: return self.connectToUri(uri) except ConnectionError: return False
def saveToPostGIS(self): dlg = PostgisTableSelector(self, self.parameter.name().lower()) dlg.exec_() if dlg.connection: self.use_temporary = False settings = QgsSettings() mySettings = '/PostgreSQL/connections/' + dlg.connection dbname = settings.value(mySettings + '/database') user = settings.value(mySettings + '/username') host = settings.value(mySettings + '/host') port = settings.value(mySettings + '/port') password = settings.value(mySettings + '/password') uri = QgsDataSourceUri() uri.setConnection(host, str(port), dbname, user, password) uri.setDataSource(dlg.schema, dlg.table, "the_geom" if isinstance(self.parameter, QgsProcessingParameterFeatureSink) and self.parameter.hasGeometry() else None) connInfo = uri.connectionInfo() (success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None) if success: QgsCredentials.instance().put(connInfo, user, passwd) self.leText.setText("postgis:" + uri.uri()) self.skipOutputChanged.emit(False) self.destinationChanged.emit()
def testRaster(self): if int(gdal.VersionInfo("VERSION_NUM")) < GDAL_COMPUTE_VERSION(2, 0, 2): return connection_name = "testRaster" plugin = createDbPlugin("gpkg") uri = QgsDataSourceUri() test_gpkg_new = os.path.join(self.basetestpath, "testRaster.gpkg") shutil.copy(self.test_gpkg, test_gpkg_new) mem_ds = gdal.GetDriverByName("MEM").Create("", 20, 20) mem_ds.SetGeoTransform([2, 0.01, 0, 49, 0, -0.01]) sr = osr.SpatialReference() sr.ImportFromEPSG(4326) mem_ds.SetProjection(sr.ExportToWkt()) mem_ds.GetRasterBand(1).Fill(255) gdal.GetDriverByName("GPKG").CreateCopy( test_gpkg_new, mem_ds, options=["APPEND_SUBDATASET=YES", "RASTER_TABLE=raster_table"] ) mem_ds = None uri.setDatabase(test_gpkg_new) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin("gpkg", connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 2) table = None for i in range(2): if tables[i].name == "raster_table": table = tables[i] break self.assertIsNotNone(table) info = table.info() expected_html = """<div class="section"><h2>General info</h2><div><table><tr><td>Relation type: </td><td>Table </td></tr><tr><td>Rows: </td><td>Unknown (<a href="action:rows/count">find out</a>) </td></tr></table></div></div><div class="section"><h2>GeoPackage</h2><div><table><tr><td>Column: </td><td> </td></tr><tr><td>Geometry: </td><td>RASTER </td></tr><tr><td>Spatial ref: </td><td>WGS 84 geodetic (4326) </td></tr><tr><td>Extent: </td><td>2.00000, 48.80000 - 2.20000, 49.00000 </td></tr></table></div></div><div class="section"><h2>Fields</h2><div><table class="header"><tr><th># </th><th>Name </th><th>Type </th><th>Null </th><th>Default </th></tr><tr><td>0 </td><td class="underline">id </td><td>INTEGER </td><td>Y </td><td> </td></tr><tr><td>1 </td><td>zoom_level </td><td>INTEGER </td><td>N </td><td> </td></tr><tr><td>2 </td><td>tile_column </td><td>INTEGER </td><td>N </td><td> </td></tr><tr><td>3 </td><td>tile_row </td><td>INTEGER </td><td>N </td><td> </td></tr><tr><td>4 </td><td>tile_data </td><td>BLOB </td><td>N </td><td> </td></tr></table></div></div><div class="section"><h2>Indexes</h2><div><table class="header"><tr><th>Name </th><th>Column(s) </th></tr><tr><td>sqlite_autoindex_raster_table_1 </td><td>zoom_level<br>tile_column<br>tile_row </td></tr></table></div></div>""" self.assertEqual(info.toHtml(), expected_html, info.toHtml()) connection.remove()
def updateInputLayer(self): if not self.reloadInputLayer() or not self.inLayer: return False # update the output table name, pk and geom column self.cboTable.setEditText(self.inLayer.name()) srcUri = QgsDataSourceUri(self.inLayer.source()) pk = srcUri.keyColumn() if srcUri.keyColumn() else self.default_pk self.editPrimaryKey.setText(pk) geom = srcUri.geometryColumn() if srcUri.geometryColumn() else self.default_geom self.editGeomColumn.setText(geom) srcCrs = self.inLayer.crs() srid = srcCrs.postgisSrid() if srcCrs.isValid() else 4326 self.editSourceSrid.setText("%s" % srid) self.editTargetSrid.setText("%s" % srid) return True
def test_table_scan(self): """Test that with use estimated metadata disabled all geometry column types can be identified, test for GH #43186 """ md = QgsProviderRegistry.instance().providerMetadata('postgres') uri = QgsDataSourceUri(self.uri) conn = md.createConnection(uri.uri(), {'estimatedMetadata': True}) sql = """ DROP TABLE IF EXISTS qgis_test.geometry_table_with_multiple_types; CREATE TABLE qgis_test.geometry_table_with_multiple_types ( id SERIAL PRIMARY KEY, geom geometry(Geometry,4326) ); """ conn.executeSql(sql) for i in range(110): sql = "INSERT INTO qgis_test.geometry_table_with_multiple_types (geom) VALUES (ST_GeomFromText('point(9 45)', 4326));" conn.executeSql(sql) for i in range(10): sql = "INSERT INTO qgis_test.geometry_table_with_multiple_types (geom) VALUES (ST_GeomFromText('linestring(9 45, 10 46)', 4326));" conn.executeSql(sql) table = conn.table('qgis_test', 'geometry_table_with_multiple_types') self.assertEqual(len(table.geometryColumnTypes()), 1) uri = QgsDataSourceUri(self.uri) uri.setUseEstimatedMetadata(False) conn = md.createConnection(uri.uri(), {'estimatedMetadata': False}) table = conn.table('qgis_test', 'geometry_table_with_multiple_types') self.assertEqual(len(table.geometryColumnTypes()), 2) # Tesf for #43199 uri.setSchema('qgis_test') uri.setTable('geometry_table_with_multiple_types') uri.setGeometryColumn('geom') uri.setWkbType(QgsWkbTypes.Point) vl = QgsVectorLayer(uri.uri(), 'points', 'postgres') self.assertTrue(vl.isValid()) self.assertEqual(vl.featureCount(), 110) uri.setGeometryColumn('geom') uri.setWkbType(QgsWkbTypes.LineString) vl = QgsVectorLayer(uri.uri(), 'lines', 'postgres') self.assertTrue(vl.isValid()) self.assertEqual(vl.featureCount(), 10)
def _getPostGISLayer(cls, type_name, layer_name=None, authcfg=None): """ PG layer factory """ if layer_name is None: layer_name = 'pg_' + type_name uri = QgsDataSourceUri() uri.setWkbType(QgsWkbTypes.Point) uri.setConnection("localhost", cls.port, cls.dbname, "", "", QgsDataSourceUri.SslVerifyFull, authcfg) uri.setKeyColumn('pk') uri.setSrid('EPSG:4326') uri.setDataSource('qgis_test', 'someData', "geom", "", "pk") # Note: do not expand here! layer = QgsVectorLayer(uri.uri(False), layer_name, 'postgres') return layer
def ogrConnectionString(uri): """Generates OGR connection sting from layer source """ ogrstr = None context = dataobjects.createContext() layer = QgsProcessingUtils.mapLayerFromString(uri, context, False) if layer is None: return '"' + uri + '"' provider = layer.dataProvider().name() if provider == 'spatialite': # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql= regex = re.compile("dbname='(.+)'") r = regex.search(str(layer.source())) ogrstr = r.groups()[0] elif provider == 'postgres': # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999 # user='******' password='******' sslmode=disable # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON # table="t4" (geom) sql= dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri()) conninfo = dsUri.connectionInfo() conn = None ok = False while not conn: try: conn = psycopg2.connect(dsUri.connectionInfo()) except psycopg2.OperationalError: (ok, user, passwd) = QgsCredentials.instance().get( conninfo, dsUri.username(), dsUri.password()) if not ok: break dsUri.setUsername(user) dsUri.setPassword(passwd) if not conn: raise RuntimeError( 'Could not connect to PostgreSQL database - check connection info' ) if ok: QgsCredentials.instance().put(conninfo, user, passwd) ogrstr = "PG:%s" % dsUri.connectionInfo() elif provider == "oracle": # OCI:user/password@host:port/service:table dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri()) ogrstr = "OCI:" if dsUri.username() != "": ogrstr += dsUri.username() if dsUri.password() != "": ogrstr += "/" + dsUri.password() delim = "@" if dsUri.host() != "": ogrstr += delim + dsUri.host() delim = "" if dsUri.port() != "" and dsUri.port() != '1521': ogrstr += ":" + dsUri.port() ogrstr += "/" if dsUri.database() != "": ogrstr += dsUri.database() elif dsUri.database() != "": ogrstr += delim + dsUri.database() if ogrstr == "OCI:": raise RuntimeError( 'Invalid oracle data source - check connection info') ogrstr += ":" if dsUri.schema() != "": ogrstr += dsUri.schema() + "." ogrstr += dsUri.table() else: ogrstr = str(layer.source()).split("|")[0] return '"' + ogrstr + '"'
def processAlgorithm(self, parameters, context, feedback): database = self.parameterAsVectorLayer(parameters, self.DATABASE, context) databaseuri = database.dataProvider().dataSourceUri() uri = QgsDataSourceUri(databaseuri) if uri.database() is '': if '|layername' in databaseuri: databaseuri = databaseuri[:databaseuri.find('|layername')] elif '|layerid' in databaseuri: databaseuri = databaseuri[:databaseuri.find('|layerid')] uri = QgsDataSourceUri('dbname=\'%s\'' % (databaseuri)) db = spatialite.GeoDB(uri) overwrite = self.parameterAsBool(parameters, self.OVERWRITE, context) createIndex = self.parameterAsBool(parameters, self.CREATEINDEX, context) convertLowerCase = self.parameterAsBool(parameters, self.LOWERCASE_NAMES, context) dropStringLength = self.parameterAsBool(parameters, self.DROP_STRING_LENGTH, context) forceSinglePart = self.parameterAsBool(parameters, self.FORCE_SINGLEPART, context) primaryKeyField = self.parameterAsString(parameters, self.PRIMARY_KEY, context) or 'id' encoding = self.parameterAsString(parameters, self.ENCODING, context) source = self.parameterAsSource(parameters, self.INPUT, context) table = self.parameterAsString(parameters, self.TABLENAME, context) if table: table.strip() if not table or table == '': table = source.sourceName() table = table.replace('.', '_') table = table.replace(' ', '').lower() providerName = 'spatialite' geomColumn = self.parameterAsString(parameters, self.GEOMETRY_COLUMN, context) if not geomColumn: geomColumn = 'geom' options = {} if overwrite: options['overwrite'] = True if convertLowerCase: options['lowercaseFieldNames'] = True geomColumn = geomColumn.lower() if dropStringLength: options['dropStringConstraints'] = True if forceSinglePart: options['forceSinglePartGeometryType'] = True # Clear geometry column for non-geometry tables if source.wkbType() == QgsWkbTypes.NoGeometry: geomColumn = None uri = db.uri uri.setDataSource('', table, geomColumn, '', primaryKeyField) if encoding: options['fileEncoding'] = encoding exporter = QgsVectorLayerExporter(uri.uri(), providerName, source.fields(), source.wkbType(), source.sourceCrs(), overwrite, options) if exporter.errorCode() != QgsVectorLayerExporter.NoError: raise QgsProcessingException( self.tr('Error importing to Spatialite\n{0}').format( exporter.errorMessage())) features = source.getFeatures() total = 100.0 / source.featureCount() if source.featureCount() else 0 for current, f in enumerate(features): if feedback.isCanceled(): break if not exporter.addFeature(f, QgsFeatureSink.FastInsert): feedback.reportError(exporter.errorMessage()) feedback.setProgress(int(current * total)) exporter.flushBuffer() if exporter.errorCode() != QgsVectorLayerExporter.NoError: raise QgsProcessingException( self.tr('Error importing to Spatialite\n{0}').format( exporter.errorMessage())) if geomColumn and createIndex: db.create_spatial_index(table, geomColumn) return {}
def testCreateRenameDeleteTable(self): connection_name = 'testCreateRenameDeleteTable' plugin = createDbPlugin('gpkg') uri = QgsDataSourceUri() test_gpkg_new = os.path.join(self.basetestpath, 'testCreateRenameDeleteTable.gpkg') shutil.copy(self.test_gpkg, test_gpkg_new) uri.setDatabase(test_gpkg_new) self.assertTrue(plugin.addConnection(connection_name, uri)) connection = createDbPlugin('gpkg', connection_name) connection.connect() db = connection.database() self.assertIsNotNone(db) tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] self.assertTrue(table.rename('newName')) self.assertEqual(table.name, 'newName') connection.reconnect() db = connection.database() tables = db.tables() self.assertEqual(len(tables), 1) table = tables[0] self.assertEqual(table.name, 'newName') fields = [] geom = ['geometry', 'POINT', 4326, 3] field1 = TableField(table) field1.name = 'fid' field1.dataType = 'INTEGER' field1.notNull = True field1.primaryKey = True field2 = TableField(table) field2.name = 'str_field' field2.dataType = 'TEXT' field2.modifier = 20 fields = [field1, field2] self.assertTrue(db.createVectorTable('newName2', fields, geom)) tables = db.tables() self.assertEqual(len(tables), 2) new_table = tables[1] self.assertEqual(new_table.name, 'newName2') fields = new_table.fields() self.assertEqual(len(fields), 3) self.assertFalse(new_table.hasSpatialIndex()) self.assertTrue(new_table.createSpatialIndex()) self.assertTrue(new_table.hasSpatialIndex()) self.assertTrue(new_table.delete()) tables = db.tables() self.assertEqual(len(tables), 1) connection.remove()
def test_configuration(self): """Test storage and retrieval for configuration parameters""" uri = ( "authcfg='test_cfg' dbname='qgis_test' username='******' password='******' dbworkspace='workspace' " "estimatedMetadata='true' host='localhost' port='1521' dboptions='test_opts' " ) md = QgsProviderRegistry.instance().providerMetadata('oracle') conn = md.createConnection(uri, { "saveUsername": True, "savePassword": True }) ds_uri = QgsDataSourceUri(conn.uri()) self.assertEqual(ds_uri.username(), 'QGIS') self.assertEqual(ds_uri.host(), 'localhost') self.assertEqual(ds_uri.port(), '1521') self.assertEqual(ds_uri.database(), 'qgis_test') self.assertTrue(ds_uri.useEstimatedMetadata()) self.assertEqual(ds_uri.password(), 'qgis') self.assertEqual(ds_uri.param('dboptions'), 'test_opts') self.assertEqual(ds_uri.param('dbworkspace'), 'workspace') conn.store('myconf') conn = md.findConnection('myconf', False) ds_uri = QgsDataSourceUri(conn.uri()) self.assertEqual(ds_uri.username(), 'QGIS') self.assertEqual(ds_uri.host(), 'localhost') self.assertEqual(ds_uri.port(), '1521') self.assertEqual(ds_uri.database(), 'qgis_test') self.assertTrue(ds_uri.useEstimatedMetadata()) self.assertEqual(ds_uri.password(), 'qgis') self.assertEqual(ds_uri.param('dboptions'), 'test_opts') self.assertEqual(ds_uri.param('dbworkspace'), 'workspace') conn.remove('myconf')