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')
def replacePostgresqlDatasource(self, connection_name_central, datasource,
layername):
# Get uri from connection names
status_central, uri_central, error_message_central = getUriFromConnectionName(
connection_name_central, False)
if not status_central or not uri_central:
m = error_message_central
return False, m
# Check if layer datasource and connection datasource have common data
uri_datasource = QgsDataSourceUri(datasource)
match_error = []
if uri_datasource.service() != uri_central.service():
match_error.append('\n service ({} != {})'.format(
uri_datasource.service(), uri_central.service()))
if uri_datasource.host() != uri_central.host():
match_error.append('\n host ({} != {})'.format(
uri_datasource.host(), uri_central.host()))
if uri_datasource.port() != uri_central.port():
match_error.append('\n port ({} != {})'.format(
uri_datasource.port(), uri_central.port()))
if uri_datasource.database() != uri_central.database():
match_error.append('\n database ({} != {})'.format(
uri_datasource.database(), uri_central.database()))
if uri_datasource.username() != uri_central.username():
match_error.append('\n username ({} != {})'.format(
uri_datasource.username(), uri_central.username()))
if match_error:
m = tr(
'Central database and layer connection parameters do not match'
)
m += '.\n' + tr('Layer') + ' "{}":'.format(layername)
m += ', '.join(match_error)
return False, m
# Build central and clone datasource components to search & replace
uris = {'central': {}, 'clone': {}}
if uri_central.service():
uris['central'] = "service='%s'" % uri_central.service()
else:
uris[
'central'] = "dbname='{}' host={} port={} user='******'( password='******')?".format(
uri_central.database(), uri_central.host(),
uri_central.port())
# hard coded datasource for the Android PostgreSQL device database
# we cannot use service, because QField cannot use a service defined inside Termux
uris['clone'] = "dbname='gis' host=localhost user='******' password='******'"
# TODO: Since QField 1.8.0 - Selma, it is now possible: replace with a service !
# Replace with regex
regex = re.compile(uris['central'], re.IGNORECASE)
datasource = regex.sub(uris['clone'], datasource)
return datasource, 'Success'
def setUpClass(cls):
# Make sure there are no other loaders
del os.environ['QGIS_SERVER_LANDING_PAGE_PROJECTS_DIRECTORIES']
cls.pg_conn = os.environ.get('QGIS_SERVER_LANDING_PAGE_PG_TEST', False)
if not cls.pg_conn: # default
cls.pg_conn = "host=localhost port=5432 schema=public"
# Use QGIS API to create the test data
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(cls.pg_conn, {})
conn.executeSql('DROP DATABASE landing_page_test')
conn.executeSql('CREATE DATABASE landing_page_test')
# Add DB to conn string
cls.pg_conn = "dbname=landing_page_test " + cls.pg_conn
conn = md.createConnection(cls.pg_conn, {})
conn.executeSql(
open(
os.path.join(os.path.dirname(__file__),
'landing_page_test.sql'), 'rt').read())
uri = QgsDataSourceUri(cls.pg_conn)
cls.pg_storage_conn = "postgresql://{host}:{port}?sslmode=disable&dbname=landing_page_test&schema=public".format(
host=uri.host(), port=uri.port())
os.environ[
'QGIS_SERVER_LANDING_PAGE_PROJECTS_PG_CONNECTIONS'] = cls.pg_storage_conn
cls.server = QgsServer()
cls.api = LandingPageApiLoader(cls.server.serverInterface())
def show_dbcon_activelayer(self):
try:
# rdbms = self.helper.importModule('mActionGTOrdbms').run( 0, self, {}, True)
layer = self.iface.activeLayer()
provider = layer.dataProvider()
provider_name = provider.name()
uri = QgsDataSourceUri(provider.dataSourceUri())
text = "Layer <{0}> connection info:\n\n".format(layer.name())
text = text + "provider name: " + provider_name + "\n"
text = text + "host: " + str(uri.host()) + "\n"
text = text + "database name: " + str(uri.database()) + "\n"
text = text + "port: " + str(uri.port()) + "\n"
text = text + "user name: " + str(uri.username()) + "\n"
text = text + "user password: "******"procedure")
# db.setHostName(uri.host())
# db.setDatabaseName(uri.database())
# try: # uri.port could be ''
# db.setPort(int(uri.port()))
# except Exception as e:
# self.info.err(e, 'Invalid port number')
# db.setUserName(uri.username())
# db.setPassword(uri.password())
# text = rdbms.con_info()
self.info.msg(text, parent=self.iface.mainWindow())
except Exception as e:
self.info.err(e)
def getParametersFromLyr(self, dbName):
for lyr in self.iface.mapCanvas().layers():
if isinstance(lyr, QgsVectorLayer):
candidateUri = QgsDataSourceUri(
lyr.dataProvider().dataSourceUri())
if candidateUri.database() == dbName or \
os.path.splitext(os.path.basename(candidateUri.uri().split('|')[0]))[0] == dbName:
currLyr = lyr
break
dbParameters = dict()
if currLyr.providerType() == 'postgres':
dbParameters['host'] = candidateUri.host()
dbParameters['port'] = candidateUri.port()
dbParameters['user'] = candidateUri.username()
dbParameters['password'] = candidateUri.password()
return dbParameters, DsgEnums.DriverPostGIS
elif currLyr.providerType() == 'spatialite':
dbParameters['dbPath'] = candidateUri.database()
return dbParameters, DsgEnums.DriverSpatiaLite
elif currLyr.providerType() == 'ogr':
# geopackage provider type is ogr
dbParameters['dbPath'] = candidateUri.database()
return dbParameters, DsgEnums.DriverGeopackage
else:
raise Exception(
self.tr('Feature only implemented for PostGIS and Spatialite'))
def test_foreign_table_server(self):
"""Test foreign table with server"""
md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
conn = md.createConnection(self.uri, {})
uri = QgsDataSourceUri(conn.uri())
host = uri.host()
port = uri.port()
user = uri.username()
dbname = uri.database()
password = uri.password()
service = uri.service()
foreign_table_definition = """
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER IF NOT EXISTS postgres_fdw_test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (service '{service}', dbname '{dbname}', host '{host}', port '{port}');
DROP SCHEMA IF EXISTS foreign_schema CASCADE;
CREATE SCHEMA IF NOT EXISTS foreign_schema;
CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER SERVER postgres_fdw_test_server OPTIONS (user '{user}', password '{password}');
IMPORT FOREIGN SCHEMA qgis_test LIMIT TO ( "someData" )
FROM SERVER postgres_fdw_test_server
INTO foreign_schema;
""".format(host=host, user=user, port=port, dbname=dbname, password=password, service=service)
conn.executeSql(foreign_table_definition)
self.assertEquals(conn.tables('foreign_schema', QgsAbstractDatabaseProviderConnection.Foreign)[0].tableName(), 'someData')
def getPostgisConnectionInfo(layer):
info = dict()
if layer:
provider = layer.dataProvider()
if provider.name() == 'postgres':
uri = QgsDataSourceUri(provider.dataSourceUri())
info['service'] = uri.service()
info['host'] = uri.host()
info['port'] = uri.port()
info['dbname'] = uri.database()
(success, username,
passwd) = QgsCredentials.instance().get(uri.connectionInfo(),
None, None)
if success:
info['user'] = username # uri.username()
info['password'] = passwd # uri.password()
connection_settings = getPostgisConnectionSettings()
for connection in connection_settings:
if (connection['database'] == info['dbname']) \
and (connection['host'] == info['host']) \
and (connection['port'] == info['port']) \
and (connection['service'] == info['service']):
info['name'] = connection['name']
break
return info
def publishLayer(self, layer, fields=None):
if layer.type() == layer.VectorLayer:
if layer.featureCount() == 0:
self.logError(
"Layer contains zero features and cannot be published")
return
if layer.dataProvider().name(
) == "postgres" and self.useOriginalDataSource:
from .postgis import PostgisServer
uri = QgsDataSourceUri(layer.source())
db = PostgisServer("temp", uri.authConfigId(), uri.host(),
uri.port(), uri.schema(), uri.database())
self._publishVectorLayerFromPostgis(layer, db)
elif self.storage in [
self.FILE_BASED, self.POSTGIS_MANAGED_BY_GEOSERVER
]:
if layer.source() not in self._exportedLayers:
if self.storage == self.POSTGIS_MANAGED_BY_GEOSERVER:
path = exportLayer(layer,
fields,
toShapefile=True,
force=True,
log=self)
basename = os.path.splitext(path)[0]
zipfilename = basename + ".zip"
with ZipFile(zipfilename, 'w') as z:
for ext in [".shp", ".shx", ".prj", ".dbf"]:
filetozip = basename + ext
z.write(filetozip,
arcname=os.path.basename(filetozip))
self._exportedLayers[layer.source()] = zipfilename
else:
path = exportLayer(layer, fields, log=self)
self._exportedLayers[layer.source()] = path
filename = self._exportedLayers[layer.source()]
if self.storage == self.FILE_BASED:
self._publishVectorLayerFromFile(layer, filename)
else:
self._publishVectorLayerFromFileToPostgis(layer, filename)
elif self.storage == self.POSTGIS_MANAGED_BY_BRIDGE:
try:
from .servers import allServers
db = allServers()[self.postgisdb]
except KeyError:
raise Exception(
QCoreApplication.translate(
"GeocatBridge",
"Cannot find the selected PostGIS database"))
db.importLayer(layer, fields)
self._publishVectorLayerFromPostgis(layer, db)
elif layer.type() == layer.RasterLayer:
if layer.source() not in self._exportedLayers:
path = exportLayer(layer, fields, log=self)
self._exportedLayers[layer.source()] = path
filename = self._exportedLayers[layer.source()]
self._publishRasterLayer(filename, layer.name())
self._clearCache()
def processAlgorithm(self, parameters, context, feedback):
# Retrieving parameters
if qgs_version < 31400:
connection_name = self.parameterAsString(parameters, self.DATABASE,
context)
db = postgis.GeoDB.from_name(connection_name)
uri = db.uri
schema = self.parameterAsString(parameters, self.SCHEMA, context)
else:
connection_name = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
uri = QgsDataSourceUri(conn.uri())
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
shapec = self.parameterAsFile(parameters, self.SHAPEFILE_CLIP, context)
shapef = parameters[self.SHAPEFILE_FOLDER]
# Execute ogr2ogr only in case the shape is Valid or no shape was provided
shapec_qgs = QgsVectorLayer(shapec, 'test_valid', 'ogr')
if shapec_qgs.isValid() or shapec == '':
feedback.pushInfo("Valid shape")
else:
raise QgsProcessingException("Invalid shape")
# Text for ogr2ogr
if shapec == '':
target_ogr = (
'ogr2ogr -fieldTypeToString IntegerList,Integer64List,RealList,StringList '
'-lco ENCODING=UTF-8 -f "ESRI Shapefile" {} -overwrite '
).format(shapef)
else:
target_ogr = (
'ogr2ogr -fieldTypeToString IntegerList,Integer64List,RealList,StringList '
'-lco ENCODING=UTF-8 -f "ESRI Shapefile" {} -clipsrc {} -overwrite '
).format(shapef, shapec)
source_ogr = 'PG:"host={} dbname={} schemas={} port={} user={} password={}" '.format(
uri.host(), uri.database(), schema, uri.port(), uri.username(),
uri.password())
string_ogr = target_ogr + source_ogr
feedback.pushInfo('Text for ogr2ogr = ' + string_ogr)
# Export schema to shapefile
try:
process = subprocess.run(string_ogr,
shell=True,
check=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
except subprocess.CalledProcessError as e:
raise QgsProcessingException(str(e.stderr.decode('utf-8')))
return {'Result': 'Exported'}
def fromOtherLayerUri(uri: QgsDataSourceUri):
histoEventUri = QgsDataSourceUri()
histoEventUri.setConnection(uri.host(), uri.port(), uri.database(),
uri.username(), uri.password(),
uri.sslMode(), uri.authConfigId())
histoEventUri.setDataSource(uri.schema(), "histo_event", None, None,
"id")
return HistoEvent(histoEventUri)
def processAlgorithm(self, parameters, context, feedback):
# Retrieving parameters
if qgs_version < 31400:
connection_name = self.parameterAsString(parameters, self.DATABASE,
context)
db = postgis.GeoDB.from_name(connection_name)
uri = db.uri
schema = self.parameterAsString(parameters, self.SCHEMA, context)
else:
connection_name = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
uri = QgsDataSourceUri(conn.uri())
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
shape = self.parameterAsFile(parameters, self.SHAPEFILE, context)
geopackage = parameters[self.GEOPACKAGE]
# Execute ogr2ogr only in case the shape is Valid or no shape was provided
shape_qgs = QgsVectorLayer(shape, 'test_valid', 'ogr')
# Test is shape is valid
if shape_qgs.isValid() or shape == '':
feedback.pushInfo("Valid shape")
else:
raise QgsProcessingException("Invalid shape")
# Text for ogr2ogr
if shape == '':
target_ogr = "ogr2ogr -f GPKG {} -overwrite {} ".format(
geopackage, shape)
else:
target_ogr = "ogr2ogr -f GPKG {} -overwrite -clipsrc {} ".format(
geopackage, shape)
source_ogr = 'PG:"host={} dbname={} schemas={} port={} user={} password={}" '.format(
uri.host(), uri.database(), schema, uri.port(), uri.username(),
uri.password())
string_ogr = target_ogr + source_ogr
feedback.pushInfo('Text for ogr2ogr = ' + string_ogr)
# Export schema to geopackage
try:
process = subprocess.run(string_ogr,
shell=True,
check=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE)
except subprocess.CalledProcessError as e:
raise QgsProcessingException(str(e.stderr.decode('utf-8')))
return {'Result': 'Exported'}
def createConnection(uri):
ds_uri = QgsDataSourceUri(uri)
conn = dbapi.connect(address=ds_uri.host(),
port=ds_uri.port(),
user=ds_uri.username(),
password=ds_uri.password(),
ENCRYPT=True,
sslValidateCertificate=False,
CHAR_AS_UTF8=1)
conn.setautocommit(False)
return conn
def uri(self, value):
data_source_uri = QgsDataSourceUri(value)
self._dict_conn_params = {
'host': data_source_uri.host(),
'port': data_source_uri.port(),
'username': data_source_uri.username(),
'password': data_source_uri.password(),
'database': data_source_uri.database(),
'schema': self.schema
}
self._uri = value
def is_ladm_layer(self, layer):
result = False
if layer.dataProvider().name() == PGConnector._PROVIDER_NAME:
layer_uri = layer.dataProvider().uri()
db_uri = QgsDataSourceUri(self._uri)
result = (layer_uri.schema() == self.schema
and layer_uri.database() == db_uri.database()
and layer_uri.host() == db_uri.host()
and layer_uri.port() == db_uri.port()
and layer_uri.username() == db_uri.username()
and layer_uri.password() == db_uri.password())
return result
def publishLayer(self, layer, fields=None):
lyr_title, safe_name = lyr_utils.getLayerTitleAndName(layer)
if layer.type() == layer.VectorLayer:
if layer.featureCount() == 0:
self.logError(f"Layer '{lyr_title}' contains zero features and cannot be published")
return
if layer.dataProvider().name() == "postgres" and self.useOriginalDataSource:
try:
from geocatbridge.servers.models.postgis import PostgisServer
except (ImportError, ModuleNotFoundError):
raise Exception(self.translate(getAppName(), "Cannot find or import PostgisServer class"))
else:
uri = QgsDataSourceUri(layer.source())
db = PostgisServer(
"temp", uri.authConfigId(), uri.host(), uri.port(), uri.schema(), uri.database())
self._publishVectorLayerFromPostgis(layer, db)
elif self.storage != GeoserverStorage.POSTGIS_BRIDGE:
src_path, src_name, src_ext = lyr_utils.getLayerSourceInfo(layer)
filename = self._exported_layers.get(src_path)
if not filename:
if self.storage == GeoserverStorage.POSTGIS_GEOSERVER:
shp_name = exportLayer(layer, fields, to_shapefile=True, force=True, logger=self)
basename = os.path.splitext(shp_name)[0]
filename = basename + ".zip"
with ZipFile(filename, 'w') as z:
for ext in (".shp", ".shx", ".prj", ".dbf"):
filetozip = basename + ext
z.write(filetozip, arcname=os.path.basename(filetozip))
else:
filename = exportLayer(layer, fields, logger=self)
self._exported_layers[src_path] = filename
if self.storage == GeoserverStorage.FILE_BASED:
self._publishVectorLayerFromFile(layer, filename)
else:
self._publishVectorLayerFromFileToPostgis(layer, filename)
elif self.storage == GeoserverStorage.POSTGIS_BRIDGE:
db = manager.getServer(self.postgisdb)
if not db:
raise Exception(self.translate(getAppName(), "Cannot find the selected PostGIS database"))
db.importLayer(layer, fields)
self._publishVectorLayerFromPostgis(layer, db)
elif layer.type() == layer.RasterLayer:
if layer.source() not in self._exported_layers:
path = exportLayer(layer, fields, logger=self)
self._exported_layers[layer.source()] = path
filename = self._exported_layers[layer.source()]
self._publishRasterLayer(filename, safe_name)
self._clearCache()
def checkLoaded(self, name):
"""
Checks if the layers is already loaded in the QGIS' TOC
:param name:
:param loadedLayers:
:return:
"""
loaded = None
for ll in self.iface.mapCanvas().layers():
if ll.name() == name:
candidateUri = QgsDataSourceUri(
ll.dataProvider().dataSourceUri())
if self.host == candidateUri.host(
) and self.database == candidateUri.database(
) and self.port == int(candidateUri.port()):
return ll
return loaded
def update_yaml_config(self, db, config_path):
text = ''
qgs_uri = QgsDataSourceUri(db.uri)
with open(os.path.join(config_path, 'config_template.yaml')) as f:
text = f.read()
text = text.format('{}',
DB_USER=qgs_uri.username(),
DB_PASSWORD=qgs_uri.password(),
DB_HOST=qgs_uri.host(),
DB_PORT=qgs_uri.port(),
DB_NAME=qgs_uri.database())
new_file_path = os.path.join(
config_path, self.get_tmp_filename('yaml_config', 'yaml'))
with open(new_file_path, 'w') as new_yaml:
new_yaml.write(text)
return new_file_path
def addConnectionConfig(cls, conn_name, uri):
"""Necessary to allow db_manager to have the list of connections get from settings."""
uri = QgsDataSourceUri(uri)
settings = QgsSettings()
baseKey = "/PostgreSQL/connections/"
baseKey += conn_name
settings.setValue(baseKey + "/service", uri.service())
settings.setValue(baseKey + "/host", uri.host())
settings.setValue(baseKey + "/port", uri.port())
settings.setValue(baseKey + "/database", uri.database())
if uri.username():
settings.setValue(baseKey + "/username", uri.username())
if uri.password():
settings.setValue(baseKey + "/password", uri.password())
if uri.authConfigId():
settings.setValue(baseKey + "/authcfg", uri.authConfigId())
if uri.sslMode():
settings.setValue(baseKey + "/sslmode", uri.sslMode())
def addConnectionConfig(cls, conn_name, uri):
"""Necessary to allow db_manager to have the list of connections get from settings."""
uri = QgsDataSourceUri(uri)
settings = QgsSettings()
baseKey = "/PostgreSQL/connections/"
baseKey += conn_name
settings.setValue(baseKey + "/service", uri.service())
settings.setValue(baseKey + "/host", uri.host())
settings.setValue(baseKey + "/port", uri.port())
settings.setValue(baseKey + "/database", uri.database())
if uri.username():
settings.setValue(baseKey + "/username", uri.username())
if uri.password():
settings.setValue(baseKey + "/password", uri.password())
if uri.authConfigId():
settings.setValue(baseKey + "/authcfg", uri.authConfigId())
if uri.sslMode():
settings.setValue(baseKey + "/sslmode", uri.sslMode())
def getDBEncoding(layerProvider):
dbConnection = QgsDataSourceUri(layerProvider.dataSourceUri())
db = QSqlDatabase.addDatabase("QPSQL", "WPSClient")
db.setHostName(dbConnection.host())
db.setDatabaseName(dbConnection.database())
db.setUserName(dbConnection.username())
db.setPassword(dbConnection.password())
db.setPort(int(dbConnection.port()))
db.open()
query = "select pg_encoding_to_char(encoding) as encoding "
query += "from pg_catalog.pg_database "
query += "where datname = '" + dbConnection.database() + "' "
result = QSqlQuery(query, db)
result.first()
encoding = pystring(result.value(0))
db.close()
return encoding
def getConnectionParameterFromDbLayer(layer: QgsMapLayer) -> Dict[str,str]:
'''
Get connection parameters
from the layer datasource
'''
connectionParams = None
if layer.providerType() == 'postgres':
dbType = 'postgis'
else:
dbType = 'spatialite'
src = layer.source()
try:
uri = QgsDataSourceUri(src)
except:
uri = QgsDataSourceURI(src)
# TODO Use immutable namedtuple
connectionParams = {
'service' : uri.service(),
'dbname' : uri.database(),
'host' : uri.host(),
'port': uri.port(),
'user' : uri.username(),
'password': uri.password(),
'sslmode' : uri.sslMode(),
'key': uri.keyColumn(),
'estimatedmetadata' : str(uri.useEstimatedMetadata()),
'checkPrimaryKeyUnicity' : '',
'srid' : uri.srid(),
'type': uri.wkbType(),
'schema': uri.schema(),
'table' : uri.table(),
'geocol' : uri.geometryColumn(),
'sql' : uri.sql(),
'dbType': dbType
}
return connectionParams
def ogrConnectionStringAndFormat(uri, context):
"""Generates OGR connection string and format string from layer source
Returned values are a tuple of the connection string and format string
"""
ogrstr = None
format = None
layer = QgsProcessingUtils.mapLayerFromString(uri, context, False)
if layer is None:
path, ext = os.path.splitext(uri)
format = QgsVectorFileWriter.driverForExtension(ext)
return uri, '"' + format + '"'
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]
format = 'SQLite'
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()
format = 'PostgreSQL'
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()
format = 'OCI'
else:
ogrstr = str(layer.source()).split("|")[0]
path, ext = os.path.splitext(ogrstr)
format = QgsVectorFileWriter.driverForExtension(ext)
return ogrstr, '"' + format + '"'
def ogrConnectionStringAndFormatFromLayer(layer):
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]
format = 'SQLite'
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()
format = 'PostgreSQL'
elif provider == 'mssql':
#'dbname=\'db_name\' host=myHost estimatedmetadata=true
# srid=27700 type=MultiPolygon table="dbo"."my_table"
# #(Shape) sql='
dsUri = layer.dataProvider().uri()
ogrstr = 'MSSQL:'
ogrstr += 'database={0};'.format(dsUri.database())
ogrstr += 'server={0};'.format(dsUri.host())
if dsUri.username() != "":
ogrstr += 'uid={0};'.format(dsUri.username())
else:
ogrstr += 'trusted_connection=yes;'
if dsUri.password() != '':
ogrstr += 'pwd={0};'.format(dsUri.password())
ogrstr += 'tables={0}'.format(dsUri.table())
format = 'MSSQL'
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()
format = 'OCI'
else:
ogrstr = str(layer.source()).split("|")[0]
path, ext = os.path.splitext(ogrstr)
format = QgsVectorFileWriter.driverForExtension(ext)
return ogrstr, '"' + format + '"'
def run(self):
"""Roda método que faz todo o trabalho real"""
"""Executa tarefas antes de apertar o OK da janela do Complemento"""
# Pega camada ativa na legenda
camadaAtiva = self.iface.activeLayer()
# Se não tiver nenhuma camada ativa selecionada (se não tiver nenhum dataProvider) acusa erro
try:
provedor = camadaAtiva.dataProvider()
except:
QMessageBox.information(self.iface.mainWindow(), 'Aviso',
'Selecione alguma camada')
return
# Se o dataProvider da camada não for Postgres também acusa erro
if provedor.name() != 'postgres':
QMessageBox.information(
self.iface.mainWindow(), 'Aviso',
'Selecione uma camada com conexao PostGIS')
return
# Cria mesma URI da camada ativa
uri_camadaAtiva = QgsDataSourceUri(
camadaAtiva.dataProvider().dataSourceUri())
print(uri_camadaAtiva)
# Pega tabela selecionada para ser usada como dado da consulta
tabela = uri_camadaAtiva.table()
esquema = uri_camadaAtiva.schema()
print('Tabela = ', uri_camadaAtiva.table())
print('Esquema = ', uri_camadaAtiva.schema())
# Estabelece nomes a serem usados na cláusula WHERE da consulta
ultima_letra_geom = tabela[-1]
dic_geom = {'a': 'poligono', 'p': 'ponto', 'l': 'linha'}
sigla_classe = tabela[0:-2]
print('sigla_classe = ', sigla_classe)
# Lista de camadas presente na legenda
camadasAbertas = QgsProject.instance().layerTreeRoot().layerOrder()
print(camadasAbertas)
# Percorre camadas na legenda para determinar se existe a mesma classe da camada selecionada em outra escala.
# Caso ela exista a URI dela será adicionada
# para InfoUriCamadasOutraEscala e o objeto da camada será adicionado para CamadasOutraEscala
InfoUriCamadasOutraEscala = []
CamadasOutraEscala = []
for camadaLegenda in camadasAbertas:
try:
uri_camadaLegenda = QgsDataSourceUri(
camadaLegenda.dataProvider().dataSourceUri())
# Testa para ver se é mesma camada só que em outra escala - Não está funcionando ainda
# Condição antiga
#if uri_camadaAtiva.table()[0:-2:] == uri_camadaLegenda.table()[0:-2:] and uri_camadaAtiva.schema() != uri_camadaLegenda.schema():
# Condição nova
if uri_camadaAtiva.table()[0:3] == uri_camadaLegenda.table(
)[0:3] and uri_camadaAtiva.schema(
) != uri_camadaLegenda.schema():
# print 'Tabela_selecao: ', uri.table(), ' Esquema_selecao: ', uri.schema(), ' diferente de Tabela_legenda: ', uri_camadaLegenda.table(), ' Esquema_legenda: ', uri_camadaLegenda.schema()
#QMessageBox.information(self.iface.mainWindow(), 'Aviso', 'Tem mesma(s) camada(s) em escala diferente')
if uri_camadaLegenda not in InfoUriCamadasOutraEscala:
# print 'Vou adicionar camada'
InfoUriCamadasOutraEscala.append(uri_camadaLegenda)
CamadasOutraEscala.append(camadaLegenda)
else:
# print 'Não vou adicionar camada'
pass
except:
pass
print('Info outras escalas = ',
[uri.uri() for uri in InfoUriCamadasOutraEscala])
print('Camadas outras escalas = ', CamadasOutraEscala)
mensagem_uri = ''
for uri in InfoUriCamadasOutraEscala:
#print uri
mensagem_uri = mensagem_uri + 'tabela: ' + uri.table(
) + '; esquema: ' + uri.schema() + '\n'
QMessageBox.information(
self.iface.mainWindow(), 'Aviso',
'Tem mesma(s) camada(s) em escala diferente: \n' + mensagem_uri)
# Cria uma conexão ao Postgres através de QSqlDatabase - QPSQL se refere ao Postgres
db = QSqlDatabase.addDatabase("QPSQL")
# Dados da conexão são os mesmos da camada ativa (mesmo servidor, mesmo banco e mesma porta)
db.setHostName(uri_camadaAtiva.host())
db.setDatabaseName(uri_camadaAtiva.database())
db.setPort(int(uri_camadaAtiva.port()))
# Pega nome e senha de usuários nas configurações do plugin
usuario = QSettings().value(SETTINGS_NAME + "/usuario",
DEFAULT_USUARIO)
senha = QSettings().value(SETTINGS_NAME + "/senha", DEFAULT_SENHA)
print('usuario = ', usuario)
# Estabelece usuário e senha para conexão com o banco
db.setUserName(usuario)
db.setPassword(senha)
# Feições selecionadas
selecao = camadaAtiva.selectedFeatures()
selecao_outras_camadas = [
camada.selectedFeatures() for camada in CamadasOutraEscala
]
# cria lista do id_objeto das feições selecionadas de todas as classes iguais de escalas diferentes presentes na legenda
lista_id_objeto = [feicao['id'] for feicao in selecao]
lista_id_objeto_outras_camadas = [[feicao['id'] for feicao in camada]
for camada in selecao_outras_camadas]
# converte elementos da lista de id_objeto em string para usá-los na consulta
lista_id_objeto = [str(el) for el in lista_id_objeto]
lista_id_objeto_string = ','.join(lista_id_objeto)
lista_id_objeto_outras_camadas = [[
str(subel) for subel in el
] for el in lista_id_objeto_outras_camadas]
# Abre conexão ao banco de dados para adicionar os esquemas que têm tabelas espaciais contidos lá
if db.open():
# Tenho que rever essa consulta
string_consulta = "SELECT id_objeto_producao, id_nomebngb, nm_geografico_producao, cd_validacao_operador, tx_apontamento_ng, dsc_solucao FROM bngb_interface.t_interface WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
"AND nm_esquema_base = '" + esquema + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"' "
#print "string consulta exibir comboBox = ", string_consulta
msg_add = ""
for indice, uri in enumerate(InfoUriCamadasOutraEscala):
# Verifica se a lista de id_objeto contém algum valor (alguma seleção)
if lista_id_objeto_outras_camadas[indice]:
msg_add = msg_add + "UNION SELECT id_objeto_producao, id_nomebngb, nm_geografico_producao, cd_validacao_operador, tx_apontamento_ng, dsc_solucao FROM bngb_interface.t_interface WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
"AND nm_esquema_base = '" + uri.schema() + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"' "
string_consulta = string_consulta + msg_add
#print string_consulta
#print string_consulta
consulta = db.exec_(
string_consulta) #IN (" + lista_id_objeto_string + ")")
resultado_consulta = []
# Adiciona resultados da consulta (id_objeto_producao, id_nomebngb, nm_geografico_producao)
# na lista Python para posterior adição à comboBox
while consulta.next():
record = consulta.record()
#print 'Comecando'
# Pega o id_objeto_producao (na tabela t_interface) correspondente ao id_objeto das feições selecionadas
id_objeto_producao = str(
record.field('id_objeto_producao').value())
# id_objeto_producao = record.field('id_objeto_producao').value() # retorna int
print("id_objeto_producao =", id_objeto_producao,
type(id_objeto_producao))
#print id_objeto_producao, ' - tipo ', type(id_objeto_producao)
# Pega id_nomebngb
id_nomebngb = str(record.field('id_nomebngb').value())
print("id_nomebngb =", id_nomebngb, type(id_nomebngb))
#print id_nomebngb, ' - tipo ', type(id_nomebngb)
# Pega nm_geografico_producao
nome_bngb = str(record.field('nm_geografico_producao').value())
print("nm_geografico_producao =", nome_bngb, type(nome_bngb))
# Pega cd_validacao_operador
cd_validacao_operador = str(
record.field('cd_validacao_operador').value())
print("cd_validacao_operador =", cd_validacao_operador,
type(cd_validacao_operador))
# Pega tx_apontamento_crng
tx_apontamento_crng = str(
record.field('tx_apontamento_ng').value())
print("tx_apontamento_crng =", tx_apontamento_crng,
type(tx_apontamento_crng))
# Pega dsc_solucao
dsc_solucao = str(record.field('dsc_solucao').value())
print("dsc_solucao =", dsc_solucao, type(dsc_solucao))
# Para PyQt4
'''
# Pega nm_geografico_producao
if isinstance(record.field('nm_geografico_producao').value(), QPyNullVariant):
nome_bngb = str(record.field('nm_geografico_producao').value())
#print nome_bngb, ' - tipo1 ', type(nome_bngb)
else:
nome_bngb = record.field('nm_geografico_producao').value()
#print nome_bngb, ' - tipo1 ', type(nome_bngb)
cd_validacao_operador = str(record.field('cd_validacao_operador').value());
if isinstance(record.field('tx_apontamento_crng').value(), QPyNullVariant):
tx_apontamento_crng = str(record.field('tx_apontamento_crng').value())
#print nome_bngb, ' - tipo1 ', type(nome_bngb)
else:
tx_apontamento_crng = record.field('tx_apontamento_crng').value()
#print nome_bngb, ' - tipo1 ', type(nome_bngb)
if isinstance(record.field('dsc_solucao').value(), QPyNullVariant):
dsc_solucao = str(record.field('dsc_solucao').value())
else:
dsc_solucao = record.field('dsc_solucao').value()
'''
# Tupla a ser adicionada para a lista que será posteriormente adicionada à ComboBox
tupla_add = (id_objeto_producao, id_nomebngb, nome_bngb,
cd_validacao_operador, tx_apontamento_crng,
dsc_solucao)
resultado_consulta.append(tupla_add)
#print 'Resultado da consulta = ', resultado_consulta
# Parei aqui. Até aqui tudo bem, resultado_consulta guarda a tupla (id_objeto_producao, id_nomebngb, nome_bngb) da t_interface referente a todos os registros selecionados nas diversas camadas da legenda que se referem a uma classe em escalas distintas
# Cria lista de strings a partir de resultado_consulta para adição à ComboBox
resultado_consulta_string = []
for el in resultado_consulta:
item_comboBox = el[0] + '\ ' + el[1] + '\ ' + el[
2] + '\ ' + el[3] + '\ ' + el[4] + '\ ' + el[5]
resultado_consulta_string.append(item_comboBox)
#print resultado_consulta_string
# Limpa e adiciona elementos na Combo Box (caixa de opção)
self.dlg.comboBox.clear()
self.dlg.comboBox.addItems(resultado_consulta_string)
# Indica na caixa de diálogo uma linha para mostrar a camada que está sendo editada na t_interface
self.dlg.lineEdit.clear()
self.dlg.lineEdit.setText(uri_camadaAtiva.schema() + '.' +
uri_camadaAtiva.table())
else:
QMessageBox.information(self.iface.mainWindow(), 'Aviso',
'Conexao rejeitada')
return
# show the dialog
self.dlg.show()
# Run the dialog event loop
result = self.dlg.exec_()
# See if OK was pressed
if result:
# Pega índice e tupla (id_objeto_producao, id_nomebngb, nm_geografico_producao) escolhidos no ComboBox
indice = self.dlg.comboBox.currentIndex()
stringSelecionada = resultado_consulta_string[indice]
#print 'String Selecionada = ', stringSelecionada
# Transforma opção selecionada na ComboBox em lista
listastringSelecionada = stringSelecionada.split("\ ")
#print 'Lista selecionada =', listastringSelecionada
#print 'Lista selecionada[2] =', listastringSelecionada[2], 'tipo = ', type(listastringSelecionada[2])
listastringSelecionada[2] = listastringSelecionada[2].replace(
"'", "''")
listastringSelecionada[4] = listastringSelecionada[4].replace(
"'", "''")
listastringSelecionada[5] = listastringSelecionada[5].replace(
"'", "''")
#print listastringSelecionada[4]
#print 'Lista selecionada[2] depois =', listastringSelecionada[2], 'tipo = ', type(listastringSelecionada[2])
#print
#print
#print
# Estabelece string para consulta na Camada Ativa (nm_geografico_producao e tx_apontamento_crng podem ser nulos)
if listastringSelecionada[2] == 'NULL':
string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
3] + "', id_nomebngb = " + listastringSelecionada[
1] + ", nm_geografico_producao = " + listastringSelecionada[
2]
#print "UPDATE referente à camada ativa =", string_consulta
else:
string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
3] + "', id_nomebngb = " + listastringSelecionada[
1] + ", nm_geografico_producao = '" + listastringSelecionada[
2] + "'"
#print "UPDATE referente à camada ativa =", string_consulta
if listastringSelecionada[5] == 'NULL':
adiciona_dsc = ", dsc_solucao = " + listastringSelecionada[5]
else:
adiciona_dsc = ", dsc_solucao = '" + listastringSelecionada[
5] + "'"
if listastringSelecionada[4] == 'NULL':
string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = " + listastringSelecionada[4] + \
" WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
"AND nm_esquema_base = '" + esquema + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
#print u"UPDATE referente à camada ativa =", string_consulta
else:
string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = '" + listastringSelecionada[4] + \
"' WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
"AND nm_esquema_base = '" + esquema + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
#print u"UPDATE referente à camada ativa =", string_consulta
# Executa atualização (update)
db.exec_(string_consulta)
# Percorre a mesma classe nas outras escalas
for indice, uri in enumerate(InfoUriCamadasOutraEscala):
# Verifica se a lista de id_objeto contém algum valor (alguma seleção)
if lista_id_objeto_outras_camadas[indice]:
if listastringSelecionada[2] == 'NULL':
string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
3] + "', id_nomebngb = " + listastringSelecionada[
1] + ", nm_geografico_producao = " + listastringSelecionada[
2]
#print "UPDATE referente à camada auxiliar = ", string_consulta
else:
string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
3] + "', id_nomebngb = " + listastringSelecionada[
1] + ", nm_geografico_producao = '" + listastringSelecionada[
2] + "'"
#print "UPDATE referente à camada auxiliar = ", string_consulta
if listastringSelecionada[4] == 'NULL':
string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = " + listastringSelecionada[4] + \
" WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
"AND nm_esquema_base = '" + uri.schema() + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
#print u"UPDATE referente à camada auxiliar =", string_consulta
else:
string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = '" + listastringSelecionada[4] + \
"' WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
"AND nm_esquema_base = '" + uri.schema() + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
#print u"UPDATE referente à camada auxiliar =", string_consulta
# Executa atualização (update)
db.exec_(string_consulta)
def processAlgorithm(self, parameters, context, feedback):
"""
Retrieving parameters
an URL example is 'http://localhost:8080/geoserver/rest/workspaces/cite/datastores/Publicacao/featuretypes'
"""
# Retrieving parameters
if qgs_version < 31400:
connection_name = self.parameterAsString(parameters, self.DATABASE,
context)
db = postgis.GeoDB.from_name(connection_name)
uri = db.uri
schema = self.parameterAsString(parameters, self.SCHEMA, context)
else:
connection_name = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
uri = QgsDataSourceUri(conn.uri())
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
url = parameters[self.URL]
headers = {'Content-type': 'text/xml'}
user = parameters[self.USER]
password = parameters[self.PASSWORD]
prefix = parameters[self.PREFIX]
# Debugging info
feedback.pushInfo('Dados de conexão')
feedback.pushInfo('url = ' + url)
feedback.pushInfo('user = '******'password = '******'prefix = ' + prefix)
feedback.pushInfo('')
# URI and connection
uri = db.uri
con = psycopg2.connect(user=uri.username(),
password=uri.password(),
host=uri.host(),
port=uri.port(),
database=uri.database())
feedback.pushInfo('')
feedback.pushInfo('Connection = ' + str(con) + '\n')
with con:
select_schema_tables = "SELECT table_name FROM information_schema.tables " \
"WHERE table_schema = '{}'".format(schema)
cur = con.cursor()
cur.execute(select_schema_tables)
rows = cur.fetchall()
schema_tables = [table[0] for table in rows]
feedback.pushInfo('Schema Tables = ' + str(schema_tables) + '\n')
# Nomenclatura Geoserver
dicio = {'P': '(Ponto)', 'L': '(Linha)', 'A': '(Área)'}
# Names are with underline and with first letter in uppercase. The name of the project goes first.
names1 = [
item[item.find('_') + 1:].replace('_',
' ').title().replace(' ', '_')
for item in schema_tables
]
names2 = [prefix + '_' + item for item in names1]
titles = [
prefix + ' ' + item.replace('_', ' ')[:-1] +
dicio[item[-1]] if item[-1] in 'PLA' else prefix + ' ' +
item.replace('_', ' ') for item in names1
]
# Dicionário de categorias EDGV 3
dicio_cat = {
'ENC': 'Energia e Comunicações',
'ECO': 'Estrutura Econômica',
'HID': 'Hidrografia',
'LML': 'Limites e Localidades',
'PTO': 'Pontos de Referência',
'REL': 'Relevo',
'SAB': 'Saneamento Básico',
'TRA': 'Sistema de Transporte',
'AER': 'Subsistema Aeroportuário',
'DUT': 'Subsistema Dutos',
'FER': 'Subsistema Ferroviário',
'HDV': 'Subsistema Rodoviário',
'ROD': 'Subsistema Rodoviário',
'VEG': 'Vegetação',
'VER': 'Área Verde',
'CBGE':
'Classes Base do Mapeamento Topográfico em Grandes Escalas',
'LAZ': 'Cultura e Lazer',
'EDF': 'Edificações',
'EMU': 'Estrutura de Mobilidade Urbana'
}
feedback.pushInfo(
'Payloads dará informação sobre a nomenclatura exibida pelo Geoserver'
)
payloads = []
for i in range(len(names1)):
a = ("""<featureType>
<name>""" + names2[i] + """</name>
<nativeName>""" + schema_tables[i] + """</nativeName>
<abstract>""" + "Camada representando a classe [" +
names1[i][:names1[i].rfind('_')] +
"] de primitiva geométrica [" + names1[i][-1] + ':' +
dicio[names1[i][-1]][1:-1] + "] da categoria [" +
schema_tables[i][:schema_tables[i].find('_')].upper() + ':' +
dicio_cat[schema_tables[i][:schema_tables[i].find('_')].upper(
)] + "] da EDGV versão [3.0] "
"para o projeto [" + prefix +
"] da instituição/provedor [IBGE/Cartografia]." +
"""</abstract>
<title>""" + titles[i] + """</title>
<enabled>true</enabled>
<advertised>false</advertised>
</featureType>""")
payloads.append(a)
feedback.pushInfo('payload = ' + a)
feedback.pushInfo('')
# Teste de publicação
'''
feedback.pushInfo('Teste de Publicação')
feedback.pushInfo('payloadTeste = ' + payloads[21])
try:
resp = requests.post(url, auth=(user, password), data=payloads[21].encode('utf-8'),headers=headers)
feedback.pushInfo('resp = ' + resp.text)
except:
pass
'''
# Publicação
feedback.pushInfo('')
for i, payload in enumerate(payloads):
try:
resp = requests.post(url,
auth=(user, password),
data=payload.encode('utf-8'),
headers=headers)
if resp.text == '':
feedback.pushInfo("Camada publicada foi " + names2[i])
else:
feedback.pushInfo("Erro na publicação: " + resp.text)
except:
pass
'''
resp = requests.post(url, auth=(user, password), data=payloads[21],headers=headers) # está funcionando
feedback.pushInfo('resp = ' + resp.text)
'''
return {'Result': 'Layers Published'}
def read_form(self):
"""Extracts all inputs from the dialog form and provides run_task when succesful.
The selected_layer object is used to find the connection info to the Oracle
database. If the database connection is there but either the username or
password is missing get_credentials() will be called until valid credentials
are entered or the task is canceled.
"""
filter_on_height = self.dlg.cb_filterOnHeight.isChecked()
filter_on_volumetric_weight = self.dlg.cb_filterOnVolumetricWeight.isChecked(
)
selected_layer = self.dlg.cmb_layers.currentLayer()
trx_bool = self.dlg.cb_TriaxiaalProeven.isChecked()
sdp_bool = self.dlg.cb_SamendrukkingProeven.isChecked()
output_location = self.dlg.fileWidget.filePath()
output_name = self.dlg.le_outputName.text()
args = {
'selected_layer': selected_layer,
'output_location': output_location,
'output_name': output_name,
'trx_bool': trx_bool,
'sdp_bool': sdp_bool
}
# General Asserts
assert isinstance(
selected_layer, QgsVectorLayer
), 'De geselecteerde laag \'{}\' is geen vector laag.'.format(
selected_layer.name())
assert output_name, 'Het veld \'uitvoernaam\' mag niet leeg zijn.'
assert output_location, 'Het veld \'uitvoermap\' mag niet leeg zijn.'
if trx_bool:
# TRX Asserts
assert any([
self.dlg.cb_CU.isChecked(),
self.dlg.cb_CD.isChecked(),
self.dlg.cb_UU.isChecked()
]), 'Een van de drie Proeftypes moet aangekruisd worden.'
proef_types = []
if self.dlg.cb_CU.isChecked():
proef_types.append('CU')
if self.dlg.cb_CD.isChecked():
proef_types.append('CD')
if self.dlg.cb_UU.isChecked():
proef_types.append('UU')
args['proef_types'] = proef_types
args['ea'] = self.dlg.sb_strain.value()
args['save_plot'] = self.dlg.cb_savePlot.isChecked()
if self.dlg.le_vg_trx.text():
volG_trx = self.dlg.le_vg_trx.text().strip('[').strip(
']').split(',')
volG_trx = [float(x) for x in volG_trx]
volG_trx.sort()
if len(volG_trx) < 2:
self.iface.messageBar().pushMessage(
"Warning",
'Maar 1 volumegewicht interval voor triaxiaalproeven is gegeven, het interval wordt automatisch gegenereerd.',
level=1,
duration=5)
volG_trx = None
else:
volG_trx = None
args['volG_trx'] = volG_trx
if sdp_bool:
if self.dlg.le_vg_sdp.text():
volG_sdp = self.dlg.le_vg_sdp.text().strip('[').strip(
']').split(',')
volG_sdp = [float(x) for x in volG_sdp]
volG_sdp.sort()
if len(volG_sdp) < 2:
self.iface.messageBar().pushMessage(
"Warning",
'Maar 1 volumegewicht interval voor samendrukkingsproeven is gegeven, het interval wordt automatisch gegenereerd.',
level=1,
duration=5)
volG_sdp = None
else:
volG_sdp = None
args['volG_sdp'] = volG_sdp
if filter_on_height:
args['maxH'] = self.dlg.sb_maxHeight.value()
args['minH'] = self.dlg.sb_minHeight.value()
assert args['maxH'] > args[
'minH'], 'Maximum hoogte moet hoger zijn dan minimum hoogte.'
if filter_on_volumetric_weight:
args['maxVg'] = self.dlg.sb_maxVolumetricWeight.value()
args['minVg'] = self.dlg.sb_minVolumetricWeight.value()
assert args['maxVg'] > args[
'minVg'], 'Maximum volumegewicht moet hoger zijn dan het minimum volumegewicht.'
source = selected_layer.source()
uri = QgsDataSourceUri(source)
savedUsername = uri.hasParam('username')
savedPassword = uri.hasParam('password')
host = uri.host()
port = uri.port()
database = uri.database()
username = uri.username()
password = uri.password()
errorMessage = None
if savedUsername is True and savedPassword is True:
try:
qb = qgis_backend.QgisBackend(host=host,
port=port,
database=database,
username=username,
password=password)
qb.check_connection()
args['qb'] = qb
self.run_task(args)
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
errorMessage = errorObj.message
suc = 'false'
while suc == 'false':
suc, qb, errorMessage = self.get_credentials(
host,
port,
database,
username=username,
password=password,
message=errorMessage)
if suc == 'exit':
pass
elif suc == 'true':
args['qb'] = qb
self.run_task(args)
else:
suc, qb, errorMessage = self.get_credentials(host,
port,
database,
username=username,
password=password)
while suc == 'false':
suc, qb, message = self.get_credentials(host,
port,
database,
message=errorMessage)
if suc == 'exit':
pass
elif suc == 'true':
args['qb'] = qb
self.run_task(args)
def __init__(self,
destination,
encoding,
fields,
geometryType,
crs,
options=None):
self.destination = destination
self.isNotFileBased = False
self.layer = None
self.writer = None
if encoding is None:
settings = QSettings()
encoding = settings.value('/Processing/encoding',
'System',
type=str)
if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsWkbTypes.displayString(geometryType) + "?uuid=" + str(
uuid.uuid4())
if crs.isValid():
uri += '&crs=' + crs.authid()
fieldsdesc = []
for f in fields:
qgsfield = _toQgsField(f)
fieldsdesc.append(
'field=%s:%s' %
(qgsfield.name(),
TYPE_MAP_MEMORY_LAYER.get(qgsfield.type(), "string")))
if fieldsdesc:
uri += '&' + '&'.join(fieldsdesc)
self.layer = QgsVectorLayer(uri, self.destination, 'memory')
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceUri(
self.destination[len(self.POSTGIS_LAYER_PREFIX):])
connInfo = uri.connectionInfo()
(success, user,
passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
else:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database")
# fix_print_with_import
print(uri.uri())
try:
db = postgis.GeoDB(host=uri.host(),
port=int(uri.port()),
dbname=uri.database(),
user=user,
passwd=passwd)
except postgis.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(str(sql))
except postgis.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output PostGIS table:\n%s' % e.message)
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join(
'%s %s' %
(f.name(), TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("CREATE TABLE %s.%s (%s)" %
(uri.schema(), uri.table().lower(), fieldsdesc))
if geometryType != QgsWkbTypes.NullGeometry:
_runSQL(
"SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)"
.format(table=uri.table().lower(),
schema=uri.schema(),
srid=crs.authid().split(":")[-1],
typmod=QgsWkbTypes.displayString(
geometryType).upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.SPATIALITE_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceUri(
self.destination[len(self.SPATIALITE_LAYER_PREFIX):])
# fix_print_with_import
print(uri.uri())
try:
db = spatialite.GeoDB(uri=uri)
except spatialite.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(str(sql))
except spatialite.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output Spatialite table:\n%s' % str(e))
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join(
'%s %s' %
(f.name(), TYPE_MAP_SPATIALITE_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("DROP TABLE IF EXISTS %s" % uri.table().lower())
_runSQL("CREATE TABLE %s (%s)" % (uri.table().lower(), fieldsdesc))
if geometryType != QgsWkbTypes.NullGeometry:
_runSQL(
"SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)"
.format(table=uri.table().lower(),
srid=crs.authid().split(":")[-1],
typmod=QgsWkbTypes.displayString(
geometryType).upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
self.writer = self.layer.dataProvider()
else:
formats = QgsVectorFileWriter.supportedFiltersAndFormats()
OGRCodes = {}
for (key, value) in list(formats.items()):
extension = str(key)
extension = extension[extension.find('*.') + 2:]
extension = extension[:extension.find(' ')]
OGRCodes[extension] = value
OGRCodes['dbf'] = "DBF file"
extension = self.destination[self.destination.rfind('.') + 1:]
if extension not in OGRCodes:
extension = 'shp'
self.destination = self.destination + '.shp'
if geometryType == QgsWkbTypes.NoGeometry:
if extension == 'shp':
extension = 'dbf'
self.destination = self.destination[:self.destination.
rfind('.')] + '.dbf'
if extension not in self.nogeometry_extensions:
raise GeoAlgorithmExecutionException(
"Unsupported format for tables with no geometry")
qgsfields = QgsFields()
for field in fields:
qgsfields.append(_toQgsField(field))
# use default dataset/layer options
dataset_options = QgsVectorFileWriter.defaultDatasetOptions(
OGRCodes[extension])
layer_options = QgsVectorFileWriter.defaultLayerOptions(
OGRCodes[extension])
self.writer = QgsVectorFileWriter(self.destination, encoding,
qgsfields, geometryType, crs,
OGRCodes[extension],
dataset_options, layer_options)
def __init__(self, destination, encoding, fields, geometryType,
crs, options=None):
self.destination = destination
self.isNotFileBased = False
self.layer = None
self.writer = None
if encoding is None:
settings = QSettings()
encoding = settings.value('/Processing/encoding', 'System', str)
if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsWkbTypes.displayString(geometryType) + "?uuid=" + str(uuid.uuid4())
if crs.isValid():
uri += '&crs=' + crs.authid()
fieldsdesc = []
for f in fields:
qgsfield = _toQgsField(f)
fieldsdesc.append('field=%s:%s' % (qgsfield.name(),
TYPE_MAP_MEMORY_LAYER.get(qgsfield.type(), "string")))
if fieldsdesc:
uri += '&' + '&'.join(fieldsdesc)
self.layer = QgsVectorLayer(uri, self.destination, 'memory')
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceUri(self.destination[len(self.POSTGIS_LAYER_PREFIX):])
connInfo = uri.connectionInfo()
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
else:
raise GeoAlgorithmExecutionException("Couldn't connect to database")
try:
db = postgis.GeoDB(host=uri.host(), port=int(uri.port()),
dbname=uri.database(), user=user, passwd=passwd)
except postgis.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(str(sql))
except postgis.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output PostGIS table:\n%s' % e.message)
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join('%s %s' % (f.name(),
TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("CREATE TABLE %s.%s (%s)" % (uri.schema(), uri.table().lower(), fieldsdesc))
if geometryType != QgsWkbTypes.NullGeometry:
_runSQL("SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
table=uri.table().lower(), schema=uri.schema(), srid=crs.authid().split(":")[-1],
typmod=QgsWkbTypes.displayString(geometryType).upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
self.writer = self.layer.dataProvider()
elif self.destination.startswith(self.SPATIALITE_LAYER_PREFIX):
self.isNotFileBased = True
uri = QgsDataSourceUri(self.destination[len(self.SPATIALITE_LAYER_PREFIX):])
try:
db = spatialite.GeoDB(uri=uri)
except spatialite.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(str(sql))
except spatialite.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output Spatialite table:\n%s' % str(e))
fields = [_toQgsField(f) for f in fields]
fieldsdesc = ",".join('%s %s' % (f.name(),
TYPE_MAP_SPATIALITE_LAYER.get(f.type(), "VARCHAR"))
for f in fields)
_runSQL("DROP TABLE IF EXISTS %s" % uri.table().lower())
_runSQL("CREATE TABLE %s (%s)" % (uri.table().lower(), fieldsdesc))
if geometryType != QgsWkbTypes.NullGeometry:
_runSQL("SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
table=uri.table().lower(), srid=crs.authid().split(":")[-1],
typmod=QgsWkbTypes.displayString(geometryType).upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
self.writer = self.layer.dataProvider()
else:
formats = QgsVectorFileWriter.supportedFiltersAndFormats()
OGRCodes = {}
for (key, value) in list(formats.items()):
extension = str(key)
extension = extension[extension.find('*.') + 2:]
extension = extension[:extension.find(' ')]
OGRCodes[extension] = value
OGRCodes['dbf'] = "DBF file"
extension = self.destination[self.destination.rfind('.') + 1:]
if extension not in OGRCodes:
extension = 'shp'
self.destination = self.destination + '.shp'
if geometryType == QgsWkbTypes.NoGeometry:
if extension == 'shp':
extension = 'dbf'
self.destination = self.destination[:self.destination.rfind('.')] + '.dbf'
if extension not in self.nogeometry_extensions:
raise GeoAlgorithmExecutionException(
"Unsupported format for tables with no geometry")
qgsfields = QgsFields()
for field in fields:
qgsfields.append(_toQgsField(field))
# use default dataset/layer options
dataset_options = QgsVectorFileWriter.defaultDatasetOptions(OGRCodes[extension])
layer_options = QgsVectorFileWriter.defaultLayerOptions(OGRCodes[extension])
self.writer = QgsVectorFileWriter(self.destination, encoding,
qgsfields, geometryType, crs, OGRCodes[extension],
dataset_options, layer_options)
def ogrConnectionString(uri):
"""Generates OGR connection sting from layer source
"""
ogrstr = None
layer = dataobjects.getObjectFromUri(uri, 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):
# Retrieving parameters
if qgs_version < 31400:
connection_name = self.parameterAsString(parameters, self.DATABASE,
context)
db = postgis.GeoDB.from_name(connection_name)
uri = db.uri
schema = self.parameterAsString(parameters, self.SCHEMA, context)
else:
connection_name = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
uri = QgsDataSourceUri(conn.uri())
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
shape = self.parameterAsFile(parameters, self.SHAPEFILE, context)
geopackage = parameters[self.GEOPACKAGE]
# Execute ogr2ogr only in case the shape is Valid or no shape was provided
shape_qgs = QgsVectorLayer(shape, 'test_valid', 'ogr')
# Test is shape is valid
if shape_qgs.isValid() or shape == '':
feedback.pushInfo("Valid shape")
else:
raise QgsProcessingException("Invalid shape")
# Text for ogr2ogr
extension = shape_qgs.extent()
if shape == '':
target_ogr = "ogr2ogr -f GPKG {} -overwrite -forceNullable {} ".format(
geopackage, shape)
else:
target_ogr = "ogr2ogr -f GPKG {} -overwrite -forceNullable -spat {} {} {} {} ".format(
geopackage, str(extension.xMinimum()),
str(extension.yMinimum()), str(extension.xMaximum()),
str(extension.yMaximum()))
source_ogr = 'PG:"host={} dbname={} schemas={} port={} user={} password={}" '.format(
uri.host(), uri.database(), schema, uri.port(), uri.username(),
uri.password())
string_ogr = target_ogr + source_ogr
feedback.pushInfo('Text for ogr2ogr = ' + string_ogr)
# Export schema to geopackage
try:
processo = subprocess.run(string_ogr,
shell=True,
check=True,
stdout=subprocess.PIPE,
stderr=subprocess.PIPE,
stdin=subprocess.DEVNULL)
except subprocess.CalledProcessError as e:
raise QgsProcessingException(str(e.stderr.decode('utf-8')))
# Connect with PostGIS database
con = psycopg2.connect(user=uri.username(),
password=uri.password(),
host=uri.host(),
port=uri.port(),
database=uri.database())
feedback.pushInfo('Uri = ' + str(uri))
feedback.pushInfo('Uri text = ' + uri.uri())
feedback.pushInfo('Connection = ' + str(con))
feedback.pushInfo('')
# Query columns with array type
with con:
select_schema_tables = "SELECT table_name FROM information_schema.tables " \
"WHERE table_type = '{}' AND table_schema = '{}'".format('BASE TABLE', schema)
feedback.pushInfo('consulta = ' + select_schema_tables)
cur = con.cursor()
cur.execute(select_schema_tables)
rows = cur.fetchall()
schema_tables = [table[0] for table in rows]
feedback.pushInfo('schema tables = ' + str(schema_tables))
feedback.pushInfo('')
dict_table_arrcolumns = dict()
feedback.pushInfo(
'Loop para gerar dicionário. Uma lista contendo nome das colunas ARRAY para cada tabela'
)
for table in schema_tables:
select_columns = "SELECT column_name FROM information_schema.columns " \
"WHERE data_type = 'ARRAY' AND table_schema = '{}' AND table_name = '{}'".format(schema, table)
feedback.pushInfo('consulta para tabela ' + table + ': ' +
select_columns)
cur.execute(select_columns)
rows = cur.fetchall()
table_arrcolumns = [table[0] for table in rows]
if table_arrcolumns != []:
dict_table_arrcolumns[table] = table_arrcolumns
feedback.pushInfo('dicionario tabelas - colunas tipo array = ' +
str(dict_table_arrcolumns))
feedback.pushInfo('')
cur.close()
con.close()
# =============================================================================
# # Connect with Geopackage to UPDATE columns of array type
# feedback.pushInfo('Connecting to Geopackage to update array columns')
# with lite.connect(geopackage) as con:
# feedback.pushInfo('Con = ' + str(con))
#
# # Create cursor
# cur = con.cursor()
#
# # Loop in dictionary of table:array columns
# for table, arrcolumn in dict_table_arrcolumns.items():
# for column in arrcolumn:
# # Select query = """SELECT tipoproduto, '{' || substr(replace(tipoproduto, ')', '}'), 4) FROM {}""".format(table)
# update_query = """UPDATE {} SET {} = '{{' || substr(replace({}, ')', '}}'), 4)""".format(table, column, column)
# feedback.pushInfo('Update query = ' + update_query)
# cur.execute(update_query)
# con.commit()
# =============================================================================
# UPDATE columns of array type inside Geopackage using PyQGIS
for table, arrcolumn in dict_table_arrcolumns.items():
uri_geopackage = geopackage + '|layername=' + table
source = QgsVectorLayer(uri_geopackage, 'geopackage_layer', 'ogr')
if not source.isValid():
raise QgsProcessingException('Source layer not valid')
features = source.getFeatures()
for f in features:
fid = f.id()
for column in arrcolumn:
fvalue = f[column]
if isinstance(fvalue, QVariant):
continue
mfvalue = '{' + fvalue.replace(')', '}')[3:]
findex = source.fields().indexFromName(column)
attr = {findex: mfvalue}
source.dataProvider().changeAttributeValues({fid: attr})
return {'Result': 'Exported'}
def on_accept(self):
"""Accept and run the Command.run_it method."""
db_key = self.database_combo.currentText()
settings = self.databases[db_key]
db_set = settings["db_settings"]
if settings["db_type"] == "spatialite":
pass
else: # postgres
successful_connection = False
uname = db_set["username"]
passwd = db_set["password"]
msg = "Log in"
while not successful_connection:
uri = QgsDataSourceUri()
uri.setConnection(
db_set["host"],
db_set["port"],
db_set["database"],
db_set["username"],
db_set["password"],
)
# try to connect
# create a PostgreSQL connection using QSqlDatabase
db = QSqlDatabase.addDatabase("QPSQL")
# check to see if it is valid
db.setHostName(uri.host())
db.setDatabaseName(uri.database())
try:
# port can be an empty string, e.g. for spatialite db's
db.setPort(int(uri.port()))
except ValueError:
# TODO: I've seen this uri.port() handling before in some
# other file, this can probably be refactored.
pass
db.setUserName(uri.username())
db.setPassword(uri.password())
# open (create) the connection
if db.open():
successful_connection = True
break
else:
# todo - provide feedback what is wrong
pass
connInfo = uri.connectionInfo()
(success, uname, passwd) = QgsCredentialDialog.instance().get(
connInfo, uname, passwd, msg)
if success:
db_set["username"] = uname
db_set["password"] = passwd
else:
return
checks = []
if self.check_manhole_indicator.isChecked():
checks.append("manhole_indicator")
if self.check_pipe_friction.isChecked():
checks.append("pipe_friction")
if self.check_manhole_area.isChecked():
checks.append("manhole_area")
self.command.run_it(
checks,
self.check_only_empty_fields.isChecked(),
db_set,
settings["db_type"],
)
self.accept()
def ogrConnectionStringAndFormatFromLayer(layer):
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]
format = 'SQLite'
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()
format = 'PostgreSQL'
elif provider == 'mssql':
#'dbname=\'db_name\' host=myHost estimatedmetadata=true
# srid=27700 type=MultiPolygon table="dbo"."my_table"
# #(Shape) sql='
dsUri = layer.dataProvider().uri()
ogrstr = 'MSSQL:'
ogrstr += 'database={0};'.format(dsUri.database())
ogrstr += 'server={0};'.format(dsUri.host())
if dsUri.username() != "":
ogrstr += 'uid={0};'.format(dsUri.username())
else:
ogrstr += 'trusted_connection=yes;'
if dsUri.password() != '':
ogrstr += 'pwd={0};'.format(dsUri.password())
ogrstr += 'tables={0}'.format(dsUri.table())
format = 'MSSQL'
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()
format = 'OCI'
else:
ogrstr = str(layer.source()).split("|")[0]
path, ext = os.path.splitext(ogrstr)
format = QgsVectorFileWriter.driverForExtension(ext)
return ogrstr, '"' + format + '"'
def ogrConnectionStringAndFormat(uri, context):
"""Generates OGR connection string and format string from layer source
Returned values are a tuple of the connection string and format string
"""
ogrstr = None
format = None
layer = QgsProcessingUtils.mapLayerFromString(uri, context, False)
if layer is None:
path, ext = os.path.splitext(uri)
format = QgsVectorFileWriter.driverForExtension(ext)
return '"' + uri + '"', '"' + format + '"'
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]
format = 'SQLite'
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()
format = 'PostgreSQL'
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()
format = 'OCI'
else:
ogrstr = str(layer.source()).split("|")[0]
path, ext = os.path.splitext(ogrstr)
format = QgsVectorFileWriter.driverForExtension(ext)
return '"' + ogrstr + '"', '"' + format + '"'
def ogrConnectionString(uri):
"""Generates OGR connection sting from layer source
"""
ogrstr = None
layer = dataobjects.getObjectFromUri(uri, 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):
# Dummy function to enable running an alg inside an alg
def no_post_process(alg, context, feedback):
pass
# Retrieving parameters
if qgs_version < 31400:
connection_name = self.parameterAsString(parameters, self.DATABASE, context)
db = postgis.GeoDB.from_name(connection_name)
uri = db.uri
schema = self.parameterAsString(parameters, self.SCHEMA, context)
else:
connection_name = self.parameterAsConnectionName(parameters, self.DATABASE, context)
md = QgsProviderRegistry.instance().providerMetadata('postgres')
conn = md.createConnection(connection_name)
uri = QgsDataSourceUri(conn.uri())
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
geopackage = parameters[self.GEOPACKAGE]
# Debugging info
'''
feedback.pushInfo('Input parameters:')
feedback.pushInfo('connection = ' + connection)
feedback.pushInfo('db = ' + str(db))
feedback.pushInfo('schema = ' + schema)
feedback.pushInfo('geopackage = ' + geopackage)
feedback.pushInfo('')
'''
# Raise error if reamb isn't in schema name
if not 'reamb' in schema:
raise QgsProcessingException('A palavra reamb precisa fazer parte do nome do esquema')
# Connect with Geopackage
feedback.pushInfo('Listing non-empty layers from geopackage')
with lite.connect(geopackage) as con:
feedback.pushInfo('Con = ' + str(con))
layers_import = [] # will store the non-empty tables
# Create cursor
cur = con.cursor()
# Fetch layer names
cur.execute("SELECT table_name FROM gpkg_geometry_columns")
rows = cur.fetchall()
layer_names = [camada[0] for camada in rows]
feedback.pushInfo('Layers = ' + str(layer_names))
# Append non-empty geometry layers to list
for layer in layer_names:
# Count rows
cur.execute("SELECT COUNT(1) FROM {}".format(layer))
rows = cur.fetchall()
rows_count = rows[0][0]
#feedback.pushInfo('Rows = ' + str(rows_count))
# Append to list
if rows_count > 0:
#feedback.pushInfo('Table non-empty = ' + str(rows_count))
layers_import.append(layer)
feedback.pushInfo('Non-empty tables = ' + str(layers_import))
feedback.pushInfo('')
# Connect with PostGIS database
con = psycopg2.connect(user = uri.username(), password = uri.password(),
host = uri.host(), port = uri.port(), database = uri.database())
feedback.pushInfo('Uri = ' + str(uri))
feedback.pushInfo('Uri text = ' + uri.uri())
feedback.pushInfo('Connection = ' + str(con))
# Clean PostGIS schema if marked
#cleanSchema = self.parameterAsBool(parameters, self.CLEAN_SCHEMA, context)
cleanSchema = False
if cleanSchema:
with con:
select_schema_tables = "SELECT table_name FROM information_schema.tables " \
"WHERE table_type = '{}' AND table_schema = '{}'".format('BASE TABLE', schema)
cur = con.cursor()
cur.execute(select_schema_tables)
rows = cur.fetchall()
schema_tables = [table[0] for table in rows]
for table in schema_tables:
feedback.pushInfo("Deleting from {}.{}".format(schema, table))
cur.execute("DELETE FROM {}.{}".format(schema, table))
con.commit()
cur.close()
con.close()
feedback.pushInfo('')
# =============================================================================
# # Testing
# nome = 'cbge_trecho_arruamento_l'
# # QGIS Vector Layer from geopackage layer
# uri_geopackage = geopackage + '|layername=' + nome
# vlayer = QgsVectorLayer(uri_geopackage, 'geopackage_layer', 'ogr')
#
# # Use database table as QGIS Vector Layer
# uri_tabela = uri
# uri_tabela.setDataSource(schema, nome, 'geom')
# uri_tabela.setWkbType(vlayer.wkbType())
# uri_tabela.setSrid(str(vlayer.sourceCrs().postgisSrid()))
# target = QgsVectorLayer(uri_tabela.uri(), 'teste', 'postgres')
# feedback.pushInfo(uri_tabela.uri())
# feedback.pushInfo('Validade = ' + str(target.isValid()))
#
#
# processing.run("script:appendfeaturestolayer", {'SOURCE_LAYER':vlayer, 'TARGET_LAYER':target, 'ACTION_ON_DUPLICATE':0}, context=context, feedback=feedback, onFinish=no_post_process)
# =============================================================================
# Import layers
for layer in layers_import:
feedback.pushInfo("Importing {}.{}".format(schema, layer))
# QGIS Vector Layer from source
uri_geopackage = geopackage + '|layername=' + layer
source = QgsVectorLayer(uri_geopackage, 'geopackage_layer', 'ogr')
if not source.isValid():
raise QgsProcessingException('Source layer not valid')
# QGIS Vector Layer from target
uri_table = uri
uri_table.setDataSource(schema, layer, 'geom')
uri_table.setWkbType(source.wkbType())
uri_table.setSrid(str(source.sourceCrs().postgisSrid()))
target = QgsVectorLayer(uri_table.uri(), 'schema_table', 'postgres')
if not target.isValid():
raise QgsProcessingException('Target layer not valid')
# Run QGIS script for importing
processing.run("publibase:appendfeaturestolayer", {'SOURCE_LAYER':source, 'TARGET_LAYER':target, 'ACTION_ON_DUPLICATE':0}, context=context, feedback=feedback, onFinish=no_post_process)
feedback.pushInfo('')
return {'Result':'Layers imported'}
