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')
