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())
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 toMapLayer(self):
from qgis.core import QgsRasterLayer, QgsContrastEnhancement, QgsDataSourceUri, QgsCredentials
rl = QgsRasterLayer(self.gdalUri(), self.name)
if not rl.isValid():
err = rl.error().summary()
uri = QgsDataSourceUri(self.database().uri())
conninfo = uri.connectionInfo(False)
username = uri.username()
password = uri.password()
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(
conninfo, username, password, err)
if ok:
uri.setUsername(username)
uri.setPassword(password)
rl = QgsRasterLayer(self.gdalUri(uri), self.name)
if rl.isValid():
break
if rl.isValid():
rl.setContrastEnhancement(
QgsContrastEnhancement.StretchToMinimumMaximum)
return rl
def getDBLayerConnection(layer):
provider = layer.providerType()
uri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
if provider == 'spatialite':
path = uri.database()
connection_object = getSpatialiteConnection(path)
elif provider == 'postgres':
connection_object = pgsql.connect(uri.connectionInfo().encode('utf-8'))
else:
connection_object = None
return connection_object
def get_credentials(self,
host,
port,
database,
username=None,
password=None,
message=None):
"""
Runs a QgsCredentials instance to input database credentials
Parameters
----------
host : str
Database host string
port : int or str
Database port number
database : str
Database name
username : str
Database username
password : str
Database password
message : str
Message to show on the credentials dialog window
Returns
----------
str
'true', 'false' or 'exit' depending on the outcome
errorMessage : str or None
Database error message when the connection failed
"""
uri = QgsDataSourceUri()
uri.setConnection(host, port, database, username, password)
connInfo = uri.connectionInfo()
errorMessage = None
# Pops up a Credentials dialog, ok returns True if 'ok' on the dialog is pressed
(ok, user,
passwd) = QgsCredentials.instance().get(connInfo, username, password,
message)
if ok:
self.username = user
self.password = passwd
# check if connection works otherwise return 'false' and the error message
try:
self.check_connection()
return 'true', errorMessage
except cora.DatabaseError as e:
errorObj, = e.args
errorMessage = errorObj.message
return 'false', errorMessage
else:
return 'exit', errorMessage
def inject_custom_viewer_into_form(dialog, layer, feature):
"""Ass a custom viewer button on a form if needed.
Used by the "relational mode" """
if feature.attributes() == []:
# don't do anything if we don't have a feature
return
tab = dialog.findChildren(QTabWidget)[0]
if tab.count() == 3 and tab.tabText(2) == "Custom viewer":
# already there
return
xpath = no_ns(layer.customProperty("xpath", ""))
viewer = None
for viewer_cls, filter in get_custom_viewers().values():
tag = viewer_cls.xml_tag()
# remove namespace from tag
tag = tag[tag.find("}") + 1:]
if tag == xpath:
# found the viewer
viewer = viewer_cls
break
if viewer is None:
return
# get current id
pkid = layer.customProperty("pkid")
id = feature[pkid]
# get db connection settings
if '.sqlite' in layer.source():
provider = "SQLite"
schema = ""
db_uri, layer_name = layer.source().split("|")
layer_name = layer_name.split('=')[1]
else:
provider = "PostgreSQL"
ds = QgsDataSourceUri(layer.source())
db_uri = "PG:" + ds.connectionInfo()
layer_name = ds.table()
schema = ds.schema()
w = viewer_cls.init_from_db(db_uri, provider, schema, layer_name, pkid, id,
tab)
def on_tab_changed(index):
if index == 2:
w.resize(400, 400)
# create a new tab
tab.addTab(w, viewer_cls.icon(), "Custom viewer")
tab.currentChanged.connect(on_tab_changed)
def getDBConnection(self, connectionDict, user, passwd):
host = connectionDict.get('servidor')
port = connectionDict.get('porta')
dbName = connectionDict.get('nome')
if self.conn and self.conn.host() == host and self.conn.port == port and self.conn.database() == dbName:
return self.conn
else:
uri = QgsDataSourceUri()
uri.setConnection(host, port, dbName, user, passwd)
connInfo = uri.connectionInfo()
instance = QgsCredentials.instance()
instance.put(connInfo, user, passwd)
self.conn = uri
return self.conn
def getDbConnection(self):
# http://pyqgis.org/blog/2013/04/11/creating-a-postgresql-connection-from-a-qgis-layer-datasource/
# get the active layer
dbConn = None
#layer = self.iface.activeLayer() # TODO: use a layer know to be using the database
# get the underlying data provider
provider = self.linkLayer.dataProvider()
if provider.name() == 'postgres':
# get the URI containing the connection parameters
uri = QgsDataSourceUri(provider.dataSourceUri())
QgsMessageLog.logMessage(
"In captureGPSFeatures::getDbConnection. db URI :" + uri.uri(),
tag="TOMs panel")
dbConn = psycopg2.connect(uri.connectionInfo())
return dbConn
def doSelDanglingEdges(self):
toolname = "SelectDanglingEdges"
# check that a layer is selected
layer = self.iface.activeLayer()
if not layer:
QMessageBox.information(None, toolname, "A topology edge layer must be selected")
return
# check that the selected layer is a postgis one
if layer.providerType() != 'postgres':
QMessageBox.information(None, toolname, "A PostGIS layer must be selected")
return
uri = QgsDataSourceUri(layer.source())
# get the layer schema
toponame = str(uri.schema())
if not toponame:
QMessageBox.information(None, toolname, "Layer " + layer.name() + " doesn't look like a topology edge layer.\n(no schema set in datasource)")
return;
edge_id_fno = layer.dataProvider().fieldNameIndex('edge_id')
if ( edge_id_fno < 0 ):
QMessageBox.information(None, toolname, "Layer " + layer.name() + " does not have an 'edge_id' field (not a topology edge layer?)")
return
# find dangling edges
conn = psycopg2.connect( str(uri.connectionInfo()) )
cur = conn.cursor()
#cur.execute('SELECT edge_id FROM "' + toponame + '".edge_data LIMIT 1')
cur.execute('''
select distinct (array_agg(e.edge_id))[1] eid
from "''' + toponame + '''".edge_data e, "''' + toponame + '''".node n
where ( n.node_id = e.start_node or n.node_id = e.end_node )
group by n.node_id
having count(e.edge_id) = 1''')
fids = [r[0] for r in cur.fetchall()]
cur.close()
conn.close()
layer.select( fids )
def processAlgorithm(self, parameters, context, feedback):
if Qgis.QGIS_VERSION_INT >= 31400:
connection = self.parameterAsConnectionName(parameters, self.DATABASE, context)
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
else:
connection = self.parameterAsString(parameters, self.DATABASE, context)
schema = self.parameterAsString(parameters, self.SCHEMA, context)
# Get connection info
feedback.pushInfo("## CONNEXION A LA BASE DE DONNEES ##")
metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
connection = metadata.findConnection(connection)
uri = QgsDataSourceUri(connection.uri())
connection_info = uri.connectionInfo()
is_host = uri.host() != ""
if is_host:
feedback.pushInfo("Connexion établie via l'hôte")
else:
feedback.pushInfo("Connexion établie via le service")
tables_name = [
"repere", "poi_tourisme", "poi_service", "portion", "itineraire",
"liaison", "segment", "v_portion", "v_itineraire", "etape", "element",
"statut_segment_val", "amenagement_segment_val", "amenagement_type_segment_val"
]
# Get available layers
self.available_layers = {}
feedback.pushInfo("")
feedback.pushInfo("## LISTE DES COUCHES A METTRE A JOUR ##")
for layer in context.project().mapLayers().values():
if layer.type() == QgsMapLayerType.VectorLayer and \
layer.dataProvider().name() == 'postgres':
l_uri = layer.dataProvider().uri()
table_name = l_uri.table()
if l_uri.connectionInfo() == connection_info and \
l_uri.schema() == schema and \
table_name in tables_name:
self.available_layers[table_name] = layer.id()
feedback.pushInfo("// {}".format(layer.name()))
return {self.OUTPUT_MSG: '', self.OUTPUT: list(self.available_layers.values())}
def saveToPostGIS(self):
dlg = PostgisTableSelector(self, self.output.name.lower())
dlg.exec_()
if dlg.connection:
settings = QSettings()
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 self.output.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())
def getUserCredentials(self, lyr):
"""
Gets user credentials to acess the database
"""
dataSourceUri = QgsDataSourceUri(lyr.dataProvider().dataSourceUri())
if dataSourceUri.host() == '':
return (None, None)
if dataSourceUri.password() != '':
return (dataSourceUri.username(), dataSourceUri.password())
connInfo = dataSourceUri.connectionInfo()
(success, user, passwd ) = QgsCredentials.instance().get(connInfo, dataSourceUri.username(), None)
# Put the credentials back (for yourself and the provider), as QGIS removes it when you "get" it
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
else:
return (None, None)
return (user, passwd)
def createConnectionFromTransactionsGroup(self, db_connection):
# Check for QGis project permits to use transactions group.
activated = False
try:
activated = QgsProject.instance().autoTransaction()
except:
print(
"QGis project doesn't provide QgsProject::autoTransaction() method."
)
return None
if not activated:
print("This QGis project has disabled transactions group.")
return None
# Get transactions group.
# Pg plugin allows only 'postgres' provider.
providerKey = "postgres"
# Database string. Removing user.
sourceUri = QgsDataSourceUri(db_connection)
sourceUri.setUsername("")
uriStr = sourceUri.connectionInfo()
try:
print("Getting transactions group for provider ", providerKey,
" and database connection: ", uriStr)
return QgsProject.instance().transactionGroup(providerKey, uriStr)
except:
print("QgsProject::transactionGroup(): unavailable feature.")
return None
return None
def toMapLayer(self):
from qgis.core import QgsRasterLayer, QgsContrastEnhancement, QgsDataSourceUri, QgsCredentials
rl = QgsRasterLayer(self.gdalUri(), self.name)
if not rl.isValid():
err = rl.error().summary()
uri = QgsDataSourceUri(self.database().uri())
conninfo = uri.connectionInfo(False)
username = uri.username()
password = uri.password()
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if ok:
uri.setUsername(username)
uri.setPassword(password)
rl = QgsRasterLayer(self.gdalUri(uri), self.name)
if rl.isValid():
break
if rl.isValid():
rl.setContrastEnhancement(QgsContrastEnhancement.StretchToMinimumMaximum)
return rl
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 __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 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 + '"'
class SMLSurveyor(object):
def __init__(self, iface):
# save reference to the QGIS interface
self.iface = iface
# get plugin directory
self.plugin_dir = os.path.dirname(os.path.realpath(__file__))
self.uri = None
self.connection = None
self.crs = None
self.database = None
self.datetime = datetime.now()
self.required_layers = []
# 1. beacons
# 2. parcels
self.required_layers.append(
RequiredLayer('Beacon', 'Beacons', 'beacons', 'gid', 'points'))
self.required_layers.append(
RequiredLayer('Parcel', 'Parcels', 'parcels', 'parcel_id',
'polygons'))
def initGui(self):
""" Initialize gui
"""
# create plugin toolbar
self.create_plugin_toolbar()
def unload(self):
""" Uninitialize gui
"""
# remove plugin toolbar
self.remove_plugin_toolbar()
# remove layers
self.refresh_layers()
for l in self.required_layers:
if bool(l.layer):
QgsProject.instance().removeMapLayers([l.layer.id()])
def create_plugin_toolbar(self):
""" Create plugin toolbar to house buttons
"""
# create plugin toolbar
self.plugin_toolbar = QToolBar('Parcel Plugin')
self.plugin_toolbar.setObjectName('Parcel Plugin')
# create Database Selection button
self.select_database_action = QAction(
QIcon(os.path.join(self.plugin_dir, "images", "database.png")),
"Select Database Connection", self.iface.mainWindow())
self.select_database_action.setWhatsThis("Select database connection")
self.select_database_action.setStatusTip("Select database connection")
self.select_database_action.triggered.connect(
self.manage_database_connection)
# create Beardist button
self.bearing_distance_action = QAction(
QIcon(os.path.join(self.plugin_dir, "images", "beardist.png")),
"Manage Bearings and Distances", self.iface.mainWindow())
self.bearing_distance_action.setWhatsThis(
"Manage bearings and distances")
self.bearing_distance_action.setStatusTip(
"Manage bearings and distances")
self.bearing_distance_action.triggered.connect(
self.manage_bearing_distance)
# create Beacons button
self.beacons_action = QAction(
QIcon(os.path.join(self.plugin_dir, "images", "beacon.gif")),
"Manage Beacons", self.iface.mainWindow())
self.beacons_action.setWhatsThis("Manage beacons")
self.beacons_action.setStatusTip("Manage beacons")
self.beacons_action.triggered.connect(self.manage_beacons)
# create Parcels button
self.parcels_action = QAction(
QIcon(os.path.join(self.plugin_dir, "images", "parcel.png")),
"Manage Parcels", self.iface.mainWindow())
self.parcels_action.setWhatsThis("Manage parcels")
self.parcels_action.setStatusTip("Manage parcels")
self.parcels_action.triggered.connect(self.manage_parcels)
# populate plugin toolbar
self.plugin_toolbar.addAction(self.select_database_action)
self.plugin_toolbar.addAction(self.bearing_distance_action)
self.plugin_toolbar.addAction(self.beacons_action)
self.plugin_toolbar.addAction(self.parcels_action)
# add plugin toolbar to gui
self.iface.mainWindow().addToolBar(self.plugin_toolbar)
def remove_plugin_toolbar(self):
""" Remove plugin toolbar which houses buttons
"""
# remove app toolbar from gui
if hasattr(self, "pluginToolBar"):
self.iface.mainWindow().removeToolBar(self.plugin_toolbar)
self.plugin_toolbar.hide()
def set_database_connection(self, connection=None, crs=None):
""" Create a database connection
"""
# fetch settings
settings_plugin = QSettings()
settings_postgis = QSettings()
settings_plugin.beginGroup('CoGo Plugin')
settings_postgis.beginGroup('PostgreSQL/connections')
self.connection = connection
if not bool(self.connection):
# fetch pre-chosen database connection
self.connection = settings_plugin.value("DatabaseConnection", None)
# check if still exists
if bool(self.connection):
if self.connection not in settings_postgis.childGroups():
settings_plugin.setValue("DatabaseConnection", "")
self.connection = None
# fetch from user if necessary
if not bool(self.connection):
dialog = DatabaseConnectionDialog()
dialog.show()
if bool(dialog.exec_()):
self.connection = dialog.get_database_connection()
if dialog.get_crs():
self.crs = QgsCoordinateReferenceSystem(
dialog.get_crs().get('auth_id'))
settings_plugin.setValue("DatabaseConnection", self.connection)
# validate database connection
if bool(self.connection):
db_service = settings_postgis.value(self.connection + '/service')
db_host = settings_postgis.value(self.connection + '/host')
db_port = settings_postgis.value(self.connection + '/port')
db_name = settings_postgis.value(self.connection + '/database')
db_username = settings_postgis.value(self.connection + '/username')
db_password = settings_postgis.value(self.connection + '/password')
max_attempts = 3
self.uri = QgsDataSourceUri()
self.uri.setConnection(db_host, db_port, db_name, db_username,
db_password)
if db_username and db_password:
for i in range(max_attempts):
error_message = self.connect_to_db(db_service, db_host,
db_port, db_name,
db_username,
db_password)
if error_message:
ok, db_username, db_password = (
QgsCredentials.instance().get(
self.uri.connectionInfo(), db_username,
db_password, error_message))
if not ok:
break
else:
break
else:
msg = "Please enter the username and password."
for i in range(max_attempts):
ok, db_username, db_password = (
QgsCredentials.instance().get(
self.uri.connectionInfo(), db_username,
db_password, msg))
if not ok:
break
error_message = self.connect_to_db(db_service, db_host,
db_port, db_name,
db_username,
db_password)
if not error_message:
break
settings_plugin.endGroup()
settings_postgis.endGroup()
def connect_to_db(self, service, host, port, name, username, password):
username.replace(" ", "")
password.replace(" ", "")
try:
self.database = database.Manager({
"CONNECTION": self.connection,
"SERVICE": service,
"HOST": host,
"NAME": name,
"PORT": port,
"USER": username,
"PASSWORD": password
})
self.uri.setConnection(host, port, name, username, password)
self.datetime = datetime.now()
return None
except Exception as e:
self.database = None
msg = "Invalid username and password."
return msg
def refresh_layers(self):
""" Ensure all required layers exist
"""
if bool(self.database):
# Comment out below section because connection name is better
# to be a group name than crs name IMHO.
# # first, we need to check the layer group for the crs used by
# # current database
# query = "SELECT Find_SRID('public', 'beacons', 'the_geom');"
# self.database.connect(self.database.parameters)
# cursor = self.database.cursor
# cursor.execute(query)
# crs_id = int(cursor.fetchall()[0][0])
# del cursor
# group_name = None
# for key, value in crs_options.iteritems():
# if value == crs_id:
# group_name = key
group_name = self.connection
root = QgsProject.instance().layerTreeRoot()
target_group = root.findGroup(group_name)
if not bool(target_group):
target_group = root.insertGroup(0, group_name)
target_group.setItemVisibilityChecked(Qt.Checked)
for required_layer in reversed(self.required_layers):
for layer_node in target_group.findLayers():
layer = layer_node.layer()
if required_layer.name_plural.lower() == \
layer.name().lower():
target_group.removeLayer(layer)
for required_layer in self.required_layers:
self.uri.setDataSource(required_layer.schema,
required_layer.table,
required_layer.geometry_column, '',
required_layer.primary_key)
added_layer = QgsVectorLayer(self.uri.uri(),
required_layer.name_plural,
"postgres")
QgsProject.instance().addMapLayer(added_layer, False)
target_group.addLayer(added_layer)
for layer_node in target_group.findLayers():
layer = layer_node.layer()
if required_layer.name_plural == layer.name():
required_layer.layer = layer
layer_node.setItemVisibilityChecked(Qt.Checked)
if self.crs:
layer.setCrs(self.crs)
self.iface.zoomToActiveLayer()
def manage_beacons(self):
""" Portal which enables the management of beacons
"""
if self.datetime.date() != datetime.now().date():
self.database = None
if self.database is None:
self.set_database_connection()
if self.database is None:
return
BeaconManager(self.iface, self.database, self.required_layers)
validate_plugin_actions(self, self.database)
self.iface.mapCanvas().refresh()
def manage_parcels(self):
""" Portal which enables the management of parcels
"""
if self.datetime.date() != datetime.now().date():
self.database = None
if self.database is None:
self.set_database_connection()
if self.database is None:
return
ParcelManager(self.iface, self.database, self.required_layers)
validate_plugin_actions(self, self.database)
self.iface.mapCanvas().refresh()
def manage_bearing_distance(self):
""" Portal which enables the management of
bearings and distances
"""
if self.datetime.date() != datetime.now().date():
self.database = None
if self.database is None:
self.set_database_connection()
if self.database is None:
return
result = validate_plugin_actions(self, self.database)
if not result:
QMessageBox.warning(
None, "SML Surveyor",
("No Beacons available in the table. "
"Please use Beacon Manager tool to create a Beacon."))
else:
BearDistManager(self.iface, self.database, self.required_layers)
self.iface.mapCanvas().refresh()
def manage_database_connection(self):
""" Action to select the db connection to work with.
"""
database_manager = DatabaseManager()
connection = database_manager.get_current_connection()
crs = database_manager.get_current_crs()
if connection:
self.set_database_connection(connection=connection, crs=crs)
self.refresh_layers()
if self.database:
validate_plugin_actions(self, self.database)
def doRemoveNode(self):
toolname = "RemoveNode"
# check that a layer is selected
layer = self.iface.mapCanvas().currentLayer()
if not layer:
QMessageBox.information(None, toolname, "A topology node layer must be selected")
return
# check that the selected layer is a postgis one
if layer.providerType() != 'postgres':
QMessageBox.information(None, toolname, "A PostGIS layer must be selected")
return
uri = QgsDataSourceUri(layer.source())
# get the layer schema
toponame = str(uri.schema())
if not toponame:
QMessageBox.information(None, toolname, "Layer " + layer.name() + " doesn't look like a topology node layer.\n(no schema set in datasource)")
return;
node_id_fno = layer.dataProvider().fieldNameIndex('node_id')
if ( node_id_fno < 0 ):
QMessageBox.information(None, toolname, "Layer " + layer.name() + " does not have an 'node_id' field (not a topology node layer?)")
return
# get the selected features
errors = []
selected = layer.selectedFeatures()
if not selected:
QMessageBox.information(None, toolname, "Select the node(s) you want to remove")
return
msgBar = self.iface.messageBar()
pb = QProgressBar( msgBar )
msgBar.pushWidget( pb, Qgis.Info, 5 )
pb.setRange( 0, len(selected) )
pb.setValue( 0 )
conn = psycopg2.connect( str(uri.connectionInfo()) )
for feature in selected:
pb.setValue(pb.value()+1)
# get its node_id
node_id = getIntAttributeByIndex(feature, node_id_fno)
try:
cur = conn.cursor()
cur.execute("SELECT abs((getnodeedges(%s, %s)).edge)", (toponame, node_id))
if cur.rowcount == 2:
(edge1_id) = cur.fetchone()
(edge2_id) = cur.fetchone()
cur.close()
if edge1_id != edge2_id:
cur = conn.cursor()
cur.execute("SELECT ST_ModEdgeHeal(%s, %s, %s)", (toponame, edge1_id, edge2_id))
conn.commit()
cur.close()
else:
errors.append("Node " + str(node_id) + " is the only node in a ring, cannot be removed")
elif cur.rowcount == 0:
cur.close()
cur = conn.cursor()
cur.execute("SELECT ST_RemIsoNode(%s, %s)", (toponame, node_id))
conn.commit()
cur.close()
else:
cur.close()
errors.append("Node " + str(node_id) + " is not of degree 2 nor isolated")
except psycopg2.Error as e:
errors.append("Removing node " + str(node_id) + ":\n" + str(e))
conn.commit()
cur.close()
continue
conn.close()
removed = len(selected) - len(errors)
report = "Removed " + str(removed) + " nodes over " + str(len(selected)) + " selected\n"
if errors:
report += "\nFailures ("
if len(errors) > 5:
report += "first 5 of "
report += str(len(errors)) + "):\n\n" + "\n".join(errors[:5])
QMessageBox.information(None, toolname, report)
layer.removeSelection()
self.iface.mapCanvas().refresh()
def connect(self, parent=None):
conn_name = self.connectionName()
settings = QSettings()
settings.beginGroup(u"/{0}/{1}".format(
self.connectionSettingsKey(), conn_name))
if not settings.contains("database"): # non-existent entry?
raise InvalidDataException(
self.tr('There is no defined database connection "{}".'.format(
conn_name)))
from qgis.core import QgsDataSourceUri
uri = QgsDataSourceUri()
settingsList = ["host", "port", "database", "username", "password"]
host, port, database, username, password = [settings.value(x, "", type=str) for x in settingsList]
# get all of the connexion options
useEstimatedMetadata = settings.value(
"estimatedMetadata", False, type=bool)
uri.setParam('userTablesOnly', str(
settings.value("userTablesOnly", False, type=bool)))
uri.setParam('geometryColumnsOnly', str(
settings.value("geometryColumnsOnly", False, type=bool)))
uri.setParam('allowGeometrylessTables', str(
settings.value("allowGeometrylessTables", False, type=bool)))
uri.setParam('onlyExistingTypes', str(
settings.value("onlyExistingTypes", False, type=bool)))
uri.setParam('includeGeoAttributes', str(
settings.value("includeGeoAttributes", False, type=bool)))
settings.endGroup()
uri.setConnection(host, port, database, username, password)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError as e:
err = str(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username, password) = QgsCredentials.instance().get(
uri.connectionInfo(False), username, password, err)
if not ok:
return False
uri.setConnection(host, port, database, username, password)
try:
self.connectToUri(uri)
except ConnectionError as e:
if i == max_attempts - 1: # failed the last attempt
raise e
err = str(e)
continue
QgsCredentials.instance().put(
uri.connectionInfo(False), username, password)
return True
return False
class GeoDB(object):
@classmethod
def from_name(cls, conn_name):
uri = uri_from_name(conn_name)
return cls(uri=uri)
def __init__(self, host=None, port=None, dbname=None, user=None,
passwd=None, service=None, uri=None):
# Regular expression for identifiers without need to quote them
self.re_ident_ok = re.compile(r"^\w+$")
port = str(port)
if uri:
self.uri = uri
else:
self.uri = QgsDataSourceUri()
if service:
self.uri.setConnection(service, dbname, user, passwd)
else:
self.uri.setConnection(host, port, dbname, user, passwd)
conninfo = self.uri.connectionInfo(False)
err = None
for i in range(4):
expandedConnInfo = self.uri.connectionInfo(True)
try:
self.con = psycopg2.connect(expandedConnInfo)
if err is not None:
QgsCredentials.instance().put(conninfo,
self.uri.username(),
self.uri.password())
break
except psycopg2.OperationalError as e:
if i == 3:
raise QgsProcessingException(str(e))
err = str(e)
user = self.uri.username()
password = self.uri.password()
(ok, user, password) = QgsCredentials.instance().get(conninfo,
user,
password,
err)
if not ok:
raise QgsProcessingException(QCoreApplication.translate("PostGIS", 'Action canceled by user'))
if user:
self.uri.setUsername(user)
if password:
self.uri.setPassword(password)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.has_postgis = self.check_postgis()
def get_info(self):
c = self.con.cursor()
self._exec_sql(c, 'SELECT version()')
return c.fetchone()[0]
def check_postgis(self):
"""Check whether postgis_version is present in catalog.
"""
c = self.con.cursor()
self._exec_sql(c,
"SELECT COUNT(*) FROM pg_proc WHERE proname = 'postgis_version'")
return c.fetchone()[0] > 0
def get_postgis_info(self):
"""Returns tuple about PostGIS support:
- lib version
- installed scripts version
- released scripts version
- geos version
- proj version
- whether uses stats
"""
c = self.con.cursor()
self._exec_sql(c,
'SELECT postgis_lib_version(), postgis_scripts_installed(), \
postgis_scripts_released(), postgis_geos_version(), \
postgis_proj_version(), postgis_uses_stats()')
return c.fetchone()
def list_schemas(self):
"""Get list of schemas in tuples: (oid, name, owner, perms).
"""
c = self.con.cursor()
sql = "SELECT oid, nspname, pg_get_userbyid(nspowner), nspacl \
FROM pg_namespace \
WHERE nspname !~ '^pg_' AND nspname != 'information_schema'"
self._exec_sql(c, sql)
return c.fetchall()
def list_geotables(self, schema=None):
"""Get list of tables with schemas, whether user has privileges,
whether table has geometry column(s) etc.
Geometry_columns:
- f_table_schema
- f_table_name
- f_geometry_column
- coord_dimension
- srid
- type
"""
c = self.con.cursor()
if schema:
schema_where = " AND nspname = '%s' " % self._quote_unicode(schema)
else:
schema_where = \
" AND (nspname != 'information_schema' AND nspname !~ 'pg_') "
# LEFT OUTER JOIN: like LEFT JOIN but if there are more matches,
# for join, all are used (not only one)
# First find out whether PostGIS is enabled
if not self.has_postgis:
# Get all tables and views
sql = """SELECT pg_class.relname, pg_namespace.nspname,
pg_class.relkind, pg_get_userbyid(relowner),
reltuples, relpages, NULL, NULL, NULL, NULL
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relkind IN ('v', 'r', 'm', 'p')""" \
+ schema_where + 'ORDER BY nspname, relname'
else:
# Discovery of all tables and whether they contain a
# geometry column
sql = """SELECT pg_class.relname, pg_namespace.nspname,
pg_class.relkind, pg_get_userbyid(relowner),
reltuples, relpages, pg_attribute.attname,
pg_attribute.atttypid::regtype, NULL, NULL
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN pg_attribute ON
pg_attribute.attrelid = pg_class.oid AND
(pg_attribute.atttypid = 'geometry'::regtype
OR pg_attribute.atttypid IN
(SELECT oid FROM pg_type
WHERE typbasetype='geometry'::regtype))
WHERE pg_class.relkind IN ('v', 'r', 'm', 'p') """ \
+ schema_where + 'ORDER BY nspname, relname, attname'
self._exec_sql(c, sql)
items = c.fetchall()
# Get geometry info from geometry_columns if exists
if self.has_postgis:
sql = """SELECT relname, nspname, relkind,
pg_get_userbyid(relowner), reltuples, relpages,
geometry_columns.f_geometry_column,
geometry_columns.type,
geometry_columns.coord_dimension,
geometry_columns.srid
FROM pg_class
JOIN pg_namespace ON relnamespace=pg_namespace.oid
LEFT OUTER JOIN geometry_columns ON
relname=f_table_name AND nspname=f_table_schema
WHERE relkind IN ('r','v','m','p') """ \
+ schema_where + 'ORDER BY nspname, relname, \
f_geometry_column'
self._exec_sql(c, sql)
# Merge geometry info to "items"
for (i, geo_item) in enumerate(c.fetchall()):
if geo_item[7]:
items[i] = geo_item
return items
def get_table_rows(self, table, schema=None):
c = self.con.cursor()
self._exec_sql(c, 'SELECT COUNT(*) FROM %s' % self._table_name(schema,
table))
return c.fetchone()[0]
def get_table_fields(self, table, schema=None):
"""Return list of columns in table"""
c = self.con.cursor()
schema_where = (" AND nspname='%s' "
% self._quote_unicode(schema) if schema is not None else ''
)
sql = """SELECT a.attnum AS ordinal_position,
a.attname AS column_name,
t.typname AS data_type,
a.attlen AS char_max_len,
a.atttypmod AS modifier,
a.attnotnull AS notnull,
a.atthasdef AS hasdefault,
adef.adsrc AS default_value
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
LEFT JOIN pg_attrdef adef ON adef.adrelid = a.attrelid
AND adef.adnum = a.attnum
WHERE
c.relname = '%s' %s AND
a.attnum > 0
ORDER BY a.attnum""" \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
attrs = []
for row in c.fetchall():
attrs.append(TableAttribute(row))
return attrs
def get_table_indexes(self, table, schema=None):
"""Get info about table's indexes. ignore primary key and unique
index, they get listed in constraints.
"""
c = self.con.cursor()
schema_where = (" AND nspname='%s' "
% self._quote_unicode(schema) if schema is not None else ''
)
sql = """SELECT relname, indkey
FROM pg_class, pg_index
WHERE pg_class.oid = pg_index.indexrelid AND pg_class.oid IN (
SELECT indexrelid
FROM pg_index, pg_class
JOIN pg_namespace nsp ON pg_class.relnamespace = nsp.oid
WHERE pg_class.relname='%s' %s AND
pg_class.oid=pg_index.indrelid
AND indisunique != 't' AND indisprimary != 't' )""" \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
indexes = []
for row in c.fetchall():
indexes.append(TableIndex(row))
return indexes
def get_table_constraints(self, table, schema=None):
c = self.con.cursor()
schema_where = (" AND nspname='%s' "
% self._quote_unicode(schema) if schema is not None else ''
)
sql = """SELECT c.conname, c.contype, c.condeferrable, c.condeferred,
array_to_string(c.conkey, ' '), c.consrc, t2.relname,
c.confupdtype, c.confdeltype, c.confmatchtype,
array_to_string(c.confkey, ' ')
FROM pg_constraint c
LEFT JOIN pg_class t ON c.conrelid = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
JOIN pg_namespace nsp ON t.relnamespace = nsp.oid
WHERE t.relname = '%s' %s """ \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
constrs = []
for row in c.fetchall():
constrs.append(TableConstraint(row))
return constrs
def get_view_definition(self, view, schema=None):
"""Returns definition of the view."""
schema_where = (" AND nspname='%s' "
% self._quote_unicode(schema) if schema is not None else ''
)
sql = """SELECT pg_get_viewdef(c.oid)
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE relname='%s' %s AND relkind IN ('v','m')""" \
% (self._quote_unicode(view), schema_where)
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()[0]
def add_geometry_column(self, table, geom_type, schema=None,
geom_column='the_geom', srid=-1, dim=2):
# Use schema if explicitly specified
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT AddGeometryColumn(%s'%s', '%s', %d, '%s', %d)" % (
schema_part,
self._quote_unicode(table),
self._quote_unicode(geom_column),
srid,
self._quote_unicode(geom_type),
dim,
)
self._exec_sql_and_commit(sql)
def delete_geometry_column(self, table, geom_column, schema=None):
"""Use PostGIS function to delete geometry column correctly."""
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT DropGeometryColumn(%s'%s', '%s')" % (schema_part,
self._quote_unicode(table), self._quote_unicode(geom_column))
self._exec_sql_and_commit(sql)
def delete_geometry_table(self, table, schema=None):
"""Delete table with one or more geometries using PostGIS function."""
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT DropGeometryTable(%s'%s')" % (schema_part,
self._quote_unicode(table))
self._exec_sql_and_commit(sql)
def create_table(self, table, fields, pkey=None, schema=None):
"""Create ordinary table.
'fields' is array containing instances of TableField
'pkey' contains name of column to be used as primary key
"""
if len(fields) == 0:
return False
table_name = self._table_name(schema, table)
sql = 'CREATE TABLE %s (%s' % (table_name, fields[0].field_def())
for field in fields[1:]:
sql += ', %s' % field.field_def()
if pkey:
sql += ', PRIMARY KEY (%s)' % self._quote(pkey)
sql += ')'
self._exec_sql_and_commit(sql)
return True
def delete_table(self, table, schema=None):
"""Delete table from the database."""
table_name = self._table_name(schema, table)
sql = 'DROP TABLE %s' % table_name
self._exec_sql_and_commit(sql)
def empty_table(self, table, schema=None):
"""Delete all rows from table."""
table_name = self._table_name(schema, table)
sql = 'DELETE FROM %s' % table_name
self._exec_sql_and_commit(sql)
def rename_table(self, table, new_table, schema=None):
"""Rename a table in database."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s RENAME TO %s' % (table_name,
self._quote(new_table))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_table_name='%s' \
WHERE f_table_name='%s'" \
% (self._quote_unicode(new_table), self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote_unicode(schema)
self._exec_sql_and_commit(sql)
def create_view(self, name, query, schema=None):
view_name = self._table_name(schema, name)
sql = 'CREATE VIEW %s AS %s' % (view_name, query)
self._exec_sql_and_commit(sql)
def delete_view(self, name, schema=None):
view_name = self._table_name(schema, name)
sql = 'DROP VIEW %s' % view_name
self._exec_sql_and_commit(sql)
def rename_view(self, name, new_name, schema=None):
"""Rename view in database."""
self.rename_table(name, new_name, schema)
def create_schema(self, schema):
"""Create a new empty schema in database."""
sql = 'CREATE SCHEMA %s' % self._quote(schema)
self._exec_sql_and_commit(sql)
def delete_schema(self, schema):
"""Drop (empty) schema from database."""
sql = 'DROP SCHEMA %s' % self._quote(schema)
self._exec_sql_and_commit(sql)
def rename_schema(self, schema, new_schema):
"""Rename a schema in database."""
sql = 'ALTER SCHEMA %s RENAME TO %s' % (self._quote(schema),
self._quote(new_schema))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = \
"UPDATE geometry_columns SET f_table_schema='%s' \
WHERE f_table_schema='%s'" \
% (self._quote_unicode(new_schema), self._quote_unicode(schema))
self._exec_sql_and_commit(sql)
def table_add_column(self, table, field, schema=None):
"""Add a column to table (passed as TableField instance)."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD %s' % (table_name, field.field_def())
self._exec_sql_and_commit(sql)
def table_delete_column(self, table, field, schema=None):
"""Delete column from a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s DROP %s' % (table_name, self._quote(field))
self._exec_sql_and_commit(sql)
def table_column_rename(self, table, name, new_name, schema=None):
"""Rename column in a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s RENAME %s TO %s' % (table_name,
self._quote(name), self._quote(new_name))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_geometry_column='%s' \
WHERE f_geometry_column='%s' AND f_table_name='%s'" \
% (self._quote_unicode(new_name), self._quote_unicode(name),
self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote(schema)
self._exec_sql_and_commit(sql)
def table_column_set_type(self, table, column, data_type, schema=None):
"""Change column type."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ALTER %s TYPE %s' % (table_name,
self._quote(column), data_type)
self._exec_sql_and_commit(sql)
def table_column_set_default(self, table, column, default, schema=None):
"""Change column's default value.
If default=None drop default value.
"""
table_name = self._table_name(schema, table)
if default:
sql = 'ALTER TABLE %s ALTER %s SET DEFAULT %s' % (table_name,
self._quote(column), default)
else:
sql = 'ALTER TABLE %s ALTER %s DROP DEFAULT' % (table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def table_column_set_null(self, table, column, is_null, schema=None):
"""Change whether column can contain null values."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ALTER %s ' % (table_name, self._quote(column))
if is_null:
sql += 'DROP NOT NULL'
else:
sql += 'SET NOT NULL'
self._exec_sql_and_commit(sql)
def table_add_primary_key(self, table, column, schema=None):
"""Add a primery key (with one column) to a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def table_add_unique_constraint(self, table, column, schema=None):
"""Add a unique constraint to a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD UNIQUE (%s)' % (table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def table_delete_constraint(self, table, constraint, schema=None):
"""Delete constraint in a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s DROP CONSTRAINT %s' % (table_name,
self._quote(constraint))
self._exec_sql_and_commit(sql)
def table_move_to_schema(self, table, new_schema, schema=None):
if new_schema == schema:
return
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s SET SCHEMA %s' % (table_name,
self._quote(new_schema))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_table_schema='%s' \
WHERE f_table_name='%s'" \
% (self._quote_unicode(new_schema), self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote_unicode(schema)
self._exec_sql_and_commit(sql)
def create_index(self, table, name, column, schema=None):
"""Create index on one column using default options."""
table_name = self._table_name(schema, table)
idx_name = self._quote(name)
sql = 'CREATE INDEX %s ON %s (%s)' % (idx_name, table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def create_spatial_index(self, table, schema=None, geom_column='the_geom'):
table_name = self._table_name(schema, table)
idx_name = self._quote(u"sidx_%s_%s" % (table, geom_column))
sql = 'CREATE INDEX %s ON %s USING GIST(%s)' % (idx_name, table_name,
self._quote(geom_column))
self._exec_sql_and_commit(sql)
def delete_index(self, name, schema=None):
index_name = self._table_name(schema, name)
sql = 'DROP INDEX %s' % index_name
self._exec_sql_and_commit(sql)
def get_database_privileges(self):
"""DB privileges: (can create schemas, can create temp. tables).
"""
sql = "SELECT has_database_privilege('%(d)s', 'CREATE'), \
has_database_privilege('%(d)s', 'TEMP')" \
% {'d': self._quote_unicode(self.uri.database())}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def get_schema_privileges(self, schema):
"""Schema privileges: (can create new objects, can access objects
in schema)."""
sql = "SELECT has_schema_privilege('%(s)s', 'CREATE'), \
has_schema_privilege('%(s)s', 'USAGE')" \
% {'s': self._quote_unicode(schema)}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def get_table_privileges(self, table, schema=None):
"""Table privileges: (select, insert, update, delete).
"""
t = self._table_name(schema, table)
sql = """SELECT has_table_privilege('%(t)s', 'SELECT'),
has_table_privilege('%(t)s', 'INSERT'),
has_table_privilege('%(t)s', 'UPDATE'),
has_table_privilege('%(t)s', 'DELETE')""" \
% {'t': self._quote_unicode(t)}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def vacuum_analyze(self, table, schema=None):
"""Run VACUUM ANALYZE on a table."""
t = self._table_name(schema, table)
# VACUUM ANALYZE must be run outside transaction block - we
# have to change isolation level
self.con.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self.con.cursor()
self._exec_sql(c, 'VACUUM ANALYZE %s' % t)
self.con.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
def sr_info_for_srid(self, srid):
if not self.has_postgis:
return 'Unknown'
try:
c = self.con.cursor()
self._exec_sql(c,
"SELECT srtext FROM spatial_ref_sys WHERE srid = '%d'"
% srid)
srtext = c.fetchone()[0]
# Try to extract just SR name (should be quoted in double
# quotes)
x = re.search('"([^"]+)"', srtext)
if x is not None:
srtext = x.group()
return srtext
except DbError:
return 'Unknown'
def insert_table_row(self, table, values, schema=None, cursor=None):
"""Insert a row with specified values to a table.
If a cursor is specified, it doesn't commit (expecting that
there will be more inserts) otherwise it commits immediately.
"""
t = self._table_name(schema, table)
sql = ''
for value in values:
# TODO: quote values?
if sql:
sql += ', '
sql += value
sql = 'INSERT INTO %s VALUES (%s)' % (t, sql)
if cursor:
self._exec_sql(cursor, sql)
else:
self._exec_sql_and_commit(sql)
def _exec_sql(self, cursor, sql):
try:
cursor.execute(sql)
except psycopg2.Error as e:
raise QgsProcessingException(str(e) + ' QUERY: ' +
e.cursor.query.decode(e.cursor.connection.encoding))
def _exec_sql_and_commit(self, sql):
"""Tries to execute and commit some action, on error it rolls
back the change.
"""
try:
c = self.con.cursor()
self._exec_sql(c, sql)
self.con.commit()
except DbError:
self.con.rollback()
raise
def _quote(self, identifier):
"""Quote identifier if needed."""
# Make sure it's python unicode string
identifier = str(identifier)
# Is it needed to quote the identifier?
if self.re_ident_ok.match(identifier) is not None:
return identifier
# It's needed - let's quote it (and double the double-quotes)
return u'"%s"' % identifier.replace('"', '""')
def _quote_unicode(self, txt):
"""Make the string safe - replace ' with ''.
"""
# make sure it's python unicode string
txt = str(txt)
return txt.replace("'", "''")
def _table_name(self, schema, table):
if not schema:
return self._quote(table)
else:
return u'%s.%s' % (self._quote(schema), self._quote(table))
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 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 doDropOrphanedTopoGeoms(self):
toolname = "CollectTopogeoms"
# check that a layer is selected
layer = self.iface.mapCanvas().currentLayer()
if not layer:
QMessageBox.information(None, toolname, "A TopoGeom layer must be selected")
return
# check that the selected layer is a postgis one
if layer.providerType() != 'postgres':
QMessageBox.information(None, toolname, "A PostGIS layer must be selected")
return
uri = QgsDataSourceUri( layer.source() )
# get the layer schema
schema = str(uri.schema())
if not schema:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no schema set in datasource " + str(uri.uri()) + ")")
return;
# get the layer table
table = str(uri.table())
if not table:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no table set in datasource)")
return;
# get the layer column
col = str(uri.geometryColumn())
if not col:
QMessageBox.information(None, toolname, "Selected layer must be a table, not a view\n"
"(no column set in datasource)")
return;
layername = '"' + schema + '"."' + table + '"."' + col +'"'
try:
conn = psycopg2.connect( str(uri.connectionInfo()) )
cur = conn.cursor()
# get the layer topology
cur.execute("SELECT t.name, l.layer_id FROM topology.topology t, topology.layer l WHERE l.schema_name = %s AND l.table_name = %s AND l.feature_column = %s AND t.id = l.topology_id", (schema, table, col))
if cur.rowcount == 1:
(toponame, layer_id) = cur.fetchone()
# delete orphaned geoms...
cur.execute('DELETE FROM "' + toponame +
'"."relation" WHERE layer_id = %s AND topogeo_id NOT IN ( SELECT id("'
+ col + '") FROM "' + schema + '"."' + table + '")', (str(layer_id),))
QMessageBox.information(None, toolname, str(cur.rowcount)
+ ' orphaned topogeometry objects removed from layer ' + layername)
conn.commit()
else:
QMessageBox.information(None, toolname, layername + ' is not a topology layer (not registered in topology.layer table)')
cur.close()
conn.close()
except psycopg2.Error as e:
QMessageBox.information(None, toolname, "ERROR: " + str(e))
return
#QMessageBox.information(None, toolname, "Not implemeted yet, but we know the layer is " + schema + "." + table + "." + col + ", the topology name is " + toponame + " and layer_id is " + str(layer_id))
self.iface.mapCanvas().refresh()
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())
db.setPort(int(uri.port()))
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"] = passwd
db_set["password"] = uname
else:
return
self.command.run_it(self.filename, db_set, settings["db_type"])
self.accept()
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 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 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 get_credentials(self,
host,
port,
database,
username=None,
password=None,
message=None):
"""
Runs a QgsCredentials instance to ask for database credentials.
The credentials are tested in a QgisBackend instance using
test_connection(). Returns \'true\' or \'false\' depending on if the connection
works. cx_Oracle errors will be caught and the error message returned.
If the QgsCredentials dialog is canceled/escaped return \'exit\'.
Parameters
----------
host : str
Oracle database host name
port : str, int
Oracle database port number
database : str
Oracle database service name
username : str or None, optional
Oracle database user (defaults to None)
password : str or None, optional
Oracle database password (defaults to None)
message : str or None, optional
Message to be shown in the credentials dialog, usually the errormessage
from a previous connection attempt
Returns
----------
str
\'true\' if connection valid
\'false\' if connection invalid
\'exit\' if dialog is canceled
qb : QgisBackend object
QgisBackend with the database properties initialized
errorMessage : str
Oracle Database connection error message
"""
uri = QgsDataSourceUri()
# assign this information before you query the QgsCredentials data store
uri.setConnection(host, port, database, username, password)
connInfo = uri.connectionInfo()
(success, user,
passwd) = QgsCredentials.instance().get(connInfo, username, password,
message)
qb = None
errorMessage = None
if success:
try:
qb = qgis_backend.QgisBackend(host=host,
port=port,
database=database,
username=user,
password=passwd)
qb.check_connection()
return 'true', qb, errorMessage
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
errorMessage = errorObj.message
return 'false', qb, errorMessage
else:
return 'exit', qb, errorMessage
def connect(self, parent=None):
conn_name = self.connectionName()
settings = QgsSettings()
settings.beginGroup(u"/{0}/{1}".format(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 = ["host", "port", "database", "username", "password"]
host, port, database, username, password = [
settings.value(x, "", type=str) for x in settingsList
]
# get all of the connexion options
useEstimatedMetadata = settings.value("estimatedMetadata",
False,
type=bool)
uri.setParam('userTablesOnly',
str(settings.value("userTablesOnly", False, type=bool)))
uri.setParam(
'geometryColumnsOnly',
str(settings.value("geometryColumnsOnly", False, type=bool)))
uri.setParam(
'allowGeometrylessTables',
str(settings.value("allowGeometrylessTables", False, type=bool)))
uri.setParam(
'onlyExistingTypes',
str(settings.value("onlyExistingTypes", False, type=bool)))
uri.setParam(
'includeGeoAttributes',
str(settings.value("includeGeoAttributes", False, type=bool)))
settings.endGroup()
uri.setConnection(host, port, database, username, password)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError as e:
err = str(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username, password) = QgsCredentials.instance().get(
uri.connectionInfo(False), username, password, err)
if not ok:
return False
uri.setConnection(host, port, database, username, password)
try:
self.connectToUri(uri)
except ConnectionError as e:
if i == max_attempts - 1: # failed the last attempt
raise e
err = str(e)
continue
QgsCredentials.instance().put(uri.connectionInfo(False), username,
password)
return True
return False
def processAlgorithm(self, parameters, context, feedback):
msg = ""
output_layers = []
layers_name = [
"repere", "poi_tourisme", "poi_service", "liaison", "segment"
]
layers_v_name = ["v_portion", "v_itineraire"]
tables_name = [
"element", "etape", "portion", "itineraire", "statut_segment_val",
"amenagement_segment_val", "amenagement_type_segment_val"
]
layers_to_load = layers_name + layers_v_name + tables_name
if Qgis.QGIS_VERSION_INT >= 31400:
connection = self.parameterAsConnectionName(
parameters, self.DATABASE, context)
schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
else:
connection = self.parameterAsString(parameters, self.DATABASE,
context)
schema = self.parameterAsString(parameters, self.SCHEMA, context)
feedback.pushInfo("## CONNEXION A LA BASE DE DONNEES ##")
metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
connection = metadata.findConnection(connection)
uri = QgsDataSourceUri(connection.uri())
connection_info = uri.connectionInfo()
is_host = uri.host() != ""
if is_host:
feedback.pushInfo("Connexion établie via l'hote")
else:
feedback.pushInfo("Connexion établie via le service")
feedback.pushInfo("")
feedback.pushInfo("## LISTE DES COUCHES A CHARGER ##")
for layer in context.project().mapLayers().values():
if layer.type() == QgsMapLayerType.VectorLayer and \
layer.dataProvider().name() == 'postgres':
l_uri = layer.dataProvider().uri()
table_name = l_uri.table()
if l_uri.connectionInfo() == connection_info and \
l_uri.schema() == schema and \
table_name in layers_to_load:
layers_to_load.remove(table_name)
feedback.pushInfo("")
feedback.pushInfo("## CHARGEMENT DES COUCHES ##")
# add vector
for x in layers_to_load:
if x in layers_name:
result = self.initLayer(context, uri, schema, x, "geom", "")
elif x in layers_v_name:
if x == "v_portion":
pkey = "id_portion"
if x == "v_itineraire":
pkey = "id_itineraire"
result = self.initLayer(context, uri, schema, x, "geom", "",
pkey)
elif x in tables_name:
result = self.initLayer(context, uri, schema, x, None, "")
if not result:
feedback.reportError("La couche '" + x +
"' ne peut pas être chargée")
else:
output_layers.append(result.id())
# add raster
raster = self.parameterAsBool(parameters, self.RASTER, context)
if raster:
if not context.project().mapLayersByName("OpenStreetMap"):
url_with_params = (
'type=xyz&url=http://tile.openstreetmap.org/%7Bz%7D/%7Bx%7D/%7By%7D.png&'
'zmax=19&zmin=0&crs=EPSG3857')
result = self.XYZ(context, url_with_params, 'OpenStreetMap')
output_layers.append(result.id())
output_len = len(output_layers)
if output_len == 1:
msg = '{} couche chargée'.format(output_len)
elif output_len:
msg = '{} couches chargées'.format(output_len)
else:
msg = 'Aucunes couches chargées'
return {self.OUTPUT_MSG: msg, self.OUTPUT: output_layers}
class GeoDB(object):
@classmethod
def from_name(cls, conn_name):
uri = uri_from_name(conn_name)
return cls(uri=uri)
def __init__(self,
host=None,
port=None,
dbname=None,
user=None,
passwd=None,
service=None,
uri=None):
# Regular expression for identifiers without need to quote them
self.re_ident_ok = re.compile(r"^\w+$")
port = str(port)
if uri:
self.uri = uri
else:
self.uri = QgsDataSourceUri()
if service:
self.uri.setConnection(service, dbname, user, passwd)
else:
self.uri.setConnection(host, port, dbname, user, passwd)
conninfo = self.uri.connectionInfo(False)
err = None
for i in range(4):
expandedConnInfo = self.uri.connectionInfo(True)
try:
self.con = psycopg2.connect(expandedConnInfo)
if err is not None:
QgsCredentials.instance().put(conninfo,
self.uri.username(),
self.uri.password())
break
except psycopg2.OperationalError as e:
if i == 3:
raise DbError(str(e))
err = str(e)
user = self.uri.username()
password = self.uri.password()
(ok, user, password) = QgsCredentials.instance().get(
conninfo, user, password, err)
if not ok:
raise DbError(
QCoreApplication.translate("PostGIS",
'Action canceled by user'))
if user:
self.uri.setUsername(user)
if password:
self.uri.setPassword(password)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.has_postgis = self.check_postgis()
def get_info(self):
c = self.con.cursor()
self._exec_sql(c, 'SELECT version()')
return c.fetchone()[0]
def check_postgis(self):
"""Check whether postgis_version is present in catalog.
"""
c = self.con.cursor()
self._exec_sql(
c,
"SELECT COUNT(*) FROM pg_proc WHERE proname = 'postgis_version'")
return c.fetchone()[0] > 0
def get_postgis_info(self):
"""Returns tuple about PostGIS support:
- lib version
- installed scripts version
- released scripts version
- geos version
- proj version
- whether uses stats
"""
c = self.con.cursor()
self._exec_sql(
c, 'SELECT postgis_lib_version(), postgis_scripts_installed(), \
postgis_scripts_released(), postgis_geos_version(), \
postgis_proj_version(), postgis_uses_stats()')
return c.fetchone()
def list_schemas(self):
"""Get list of schemas in tuples: (oid, name, owner, perms).
"""
c = self.con.cursor()
sql = "SELECT oid, nspname, pg_get_userbyid(nspowner), nspacl \
FROM pg_namespace \
WHERE nspname !~ '^pg_' AND nspname != 'information_schema'"
self._exec_sql(c, sql)
return c.fetchall()
def list_geotables(self, schema=None):
"""Get list of tables with schemas, whether user has privileges,
whether table has geometry column(s) etc.
Geometry_columns:
- f_table_schema
- f_table_name
- f_geometry_column
- coord_dimension
- srid
- type
"""
c = self.con.cursor()
if schema:
schema_where = " AND nspname = '%s' " % self._quote_unicode(schema)
else:
schema_where = \
" AND (nspname != 'information_schema' AND nspname !~ 'pg_') "
# LEFT OUTER JOIN: like LEFT JOIN but if there are more matches,
# for join, all are used (not only one)
# First find out whether PostGIS is enabled
if not self.has_postgis:
# Get all tables and views
sql = """SELECT pg_class.relname, pg_namespace.nspname,
pg_class.relkind, pg_get_userbyid(relowner),
reltuples, relpages, NULL, NULL, NULL, NULL
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_class.relkind IN ('v', 'r')""" \
+ schema_where + 'ORDER BY nspname, relname'
else:
# Discovery of all tables and whether they contain a
# geometry column
sql = """SELECT pg_class.relname, pg_namespace.nspname,
pg_class.relkind, pg_get_userbyid(relowner),
reltuples, relpages, pg_attribute.attname,
pg_attribute.atttypid::regtype, NULL, NULL
FROM pg_class
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN pg_attribute ON
pg_attribute.attrelid = pg_class.oid AND
(pg_attribute.atttypid = 'geometry'::regtype
OR pg_attribute.atttypid IN
(SELECT oid FROM pg_type
WHERE typbasetype='geometry'::regtype))
WHERE pg_class.relkind IN ('v', 'r') """ \
+ schema_where + 'ORDER BY nspname, relname, attname'
self._exec_sql(c, sql)
items = c.fetchall()
# Get geometry info from geometry_columns if exists
if self.has_postgis:
sql = """SELECT relname, nspname, relkind,
pg_get_userbyid(relowner), reltuples, relpages,
geometry_columns.f_geometry_column,
geometry_columns.type,
geometry_columns.coord_dimension,
geometry_columns.srid
FROM pg_class
JOIN pg_namespace ON relnamespace=pg_namespace.oid
LEFT OUTER JOIN geometry_columns ON
relname=f_table_name AND nspname=f_table_schema
WHERE (relkind = 'r' or relkind='v') """ \
+ schema_where + 'ORDER BY nspname, relname, \
f_geometry_column'
self._exec_sql(c, sql)
# Merge geometry info to "items"
for (i, geo_item) in enumerate(c.fetchall()):
if geo_item[7]:
items[i] = geo_item
return items
def get_table_rows(self, table, schema=None):
c = self.con.cursor()
self._exec_sql(
c, 'SELECT COUNT(*) FROM %s' % self._table_name(schema, table))
return c.fetchone()[0]
def get_table_fields(self, table, schema=None):
"""Return list of columns in table"""
c = self.con.cursor()
schema_where = (" AND nspname='%s' " % self._quote_unicode(schema)
if schema is not None else '')
sql = """SELECT a.attnum AS ordinal_position,
a.attname AS column_name,
t.typname AS data_type,
a.attlen AS char_max_len,
a.atttypmod AS modifier,
a.attnotnull AS notnull,
a.atthasdef AS hasdefault,
adef.adsrc AS default_value
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
LEFT JOIN pg_attrdef adef ON adef.adrelid = a.attrelid
AND adef.adnum = a.attnum
WHERE
c.relname = '%s' %s AND
a.attnum > 0
ORDER BY a.attnum""" \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
attrs = []
for row in c.fetchall():
attrs.append(TableAttribute(row))
return attrs
def get_table_indexes(self, table, schema=None):
"""Get info about table's indexes. ignore primary key and unique
index, they get listed in constraints.
"""
c = self.con.cursor()
schema_where = (" AND nspname='%s' " % self._quote_unicode(schema)
if schema is not None else '')
sql = """SELECT relname, indkey
FROM pg_class, pg_index
WHERE pg_class.oid = pg_index.indexrelid AND pg_class.oid IN (
SELECT indexrelid
FROM pg_index, pg_class
JOIN pg_namespace nsp ON pg_class.relnamespace = nsp.oid
WHERE pg_class.relname='%s' %s AND
pg_class.oid=pg_index.indrelid
AND indisunique != 't' AND indisprimary != 't' )""" \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
indexes = []
for row in c.fetchall():
indexes.append(TableIndex(row))
return indexes
def get_table_constraints(self, table, schema=None):
c = self.con.cursor()
schema_where = (" AND nspname='%s' " % self._quote_unicode(schema)
if schema is not None else '')
sql = """SELECT c.conname, c.contype, c.condeferrable, c.condeferred,
array_to_string(c.conkey, ' '), c.consrc, t2.relname,
c.confupdtype, c.confdeltype, c.confmatchtype,
array_to_string(c.confkey, ' ')
FROM pg_constraint c
LEFT JOIN pg_class t ON c.conrelid = t.oid
LEFT JOIN pg_class t2 ON c.confrelid = t2.oid
JOIN pg_namespace nsp ON t.relnamespace = nsp.oid
WHERE t.relname = '%s' %s """ \
% (self._quote_unicode(table), schema_where)
self._exec_sql(c, sql)
constrs = []
for row in c.fetchall():
constrs.append(TableConstraint(row))
return constrs
def get_view_definition(self, view, schema=None):
"""Returns definition of the view."""
schema_where = (" AND nspname='%s' " % self._quote_unicode(schema)
if schema is not None else '')
sql = """SELECT pg_get_viewdef(c.oid)
FROM pg_class c
JOIN pg_namespace nsp ON c.relnamespace = nsp.oid
WHERE relname='%s' %s AND relkind='v'""" \
% (self._quote_unicode(view), schema_where)
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()[0]
def add_geometry_column(self,
table,
geom_type,
schema=None,
geom_column='the_geom',
srid=-1,
dim=2):
# Use schema if explicitly specified
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT AddGeometryColumn(%s'%s', '%s', %d, '%s', %d)" % (
schema_part,
self._quote_unicode(table),
self._quote_unicode(geom_column),
srid,
self._quote_unicode(geom_type),
dim,
)
self._exec_sql_and_commit(sql)
def delete_geometry_column(self, table, geom_column, schema=None):
"""Use PostGIS function to delete geometry column correctly."""
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT DropGeometryColumn(%s'%s', '%s')" % (
schema_part, self._quote_unicode(table),
self._quote_unicode(geom_column))
self._exec_sql_and_commit(sql)
def delete_geometry_table(self, table, schema=None):
"""Delete table with one or more geometries using PostGIS function."""
if schema:
schema_part = "'%s', " % self._quote_unicode(schema)
else:
schema_part = ''
sql = "SELECT DropGeometryTable(%s'%s')" % (schema_part,
self._quote_unicode(table))
self._exec_sql_and_commit(sql)
def create_table(self, table, fields, pkey=None, schema=None):
"""Create ordinary table.
'fields' is array containing instances of TableField
'pkey' contains name of column to be used as primary key
"""
if len(fields) == 0:
return False
table_name = self._table_name(schema, table)
sql = 'CREATE TABLE %s (%s' % (table_name, fields[0].field_def())
for field in fields[1:]:
sql += ', %s' % field.field_def()
if pkey:
sql += ', PRIMARY KEY (%s)' % self._quote(pkey)
sql += ')'
self._exec_sql_and_commit(sql)
return True
def delete_table(self, table, schema=None):
"""Delete table from the database."""
table_name = self._table_name(schema, table)
sql = 'DROP TABLE %s' % table_name
self._exec_sql_and_commit(sql)
def empty_table(self, table, schema=None):
"""Delete all rows from table."""
table_name = self._table_name(schema, table)
sql = 'DELETE FROM %s' % table_name
self._exec_sql_and_commit(sql)
def rename_table(self, table, new_table, schema=None):
"""Rename a table in database."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s RENAME TO %s' % (table_name,
self._quote(new_table))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_table_name='%s' \
WHERE f_table_name='%s'" \
% (self._quote_unicode(new_table), self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote_unicode(schema)
self._exec_sql_and_commit(sql)
def create_view(self, name, query, schema=None):
view_name = self._table_name(schema, name)
sql = 'CREATE VIEW %s AS %s' % (view_name, query)
self._exec_sql_and_commit(sql)
def delete_view(self, name, schema=None):
view_name = self._table_name(schema, name)
sql = 'DROP VIEW %s' % view_name
self._exec_sql_and_commit(sql)
def rename_view(self, name, new_name, schema=None):
"""Rename view in database."""
self.rename_table(name, new_name, schema)
def create_schema(self, schema):
"""Create a new empty schema in database."""
sql = 'CREATE SCHEMA %s' % self._quote(schema)
self._exec_sql_and_commit(sql)
def delete_schema(self, schema):
"""Drop (empty) schema from database."""
sql = 'DROP SCHEMA %s' % self._quote(schema)
self._exec_sql_and_commit(sql)
def rename_schema(self, schema, new_schema):
"""Rename a schema in database."""
sql = 'ALTER SCHEMA %s RENAME TO %s' % (self._quote(schema),
self._quote(new_schema))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = \
"UPDATE geometry_columns SET f_table_schema='%s' \
WHERE f_table_schema='%s'" \
% (self._quote_unicode(new_schema), self._quote_unicode(schema))
self._exec_sql_and_commit(sql)
def table_add_column(self, table, field, schema=None):
"""Add a column to table (passed as TableField instance)."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD %s' % (table_name, field.field_def())
self._exec_sql_and_commit(sql)
def table_delete_column(self, table, field, schema=None):
"""Delete column from a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s DROP %s' % (table_name, self._quote(field))
self._exec_sql_and_commit(sql)
def table_column_rename(self, table, name, new_name, schema=None):
"""Rename column in a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s RENAME %s TO %s' % (
table_name, self._quote(name), self._quote(new_name))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_geometry_column='%s' \
WHERE f_geometry_column='%s' AND f_table_name='%s'" \
% (self._quote_unicode(new_name), self._quote_unicode(name),
self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote(schema)
self._exec_sql_and_commit(sql)
def table_column_set_type(self, table, column, data_type, schema=None):
"""Change column type."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ALTER %s TYPE %s' % (
table_name, self._quote(column), data_type)
self._exec_sql_and_commit(sql)
def table_column_set_default(self, table, column, default, schema=None):
"""Change column's default value.
If default=None drop default value.
"""
table_name = self._table_name(schema, table)
if default:
sql = 'ALTER TABLE %s ALTER %s SET DEFAULT %s' % (
table_name, self._quote(column), default)
else:
sql = 'ALTER TABLE %s ALTER %s DROP DEFAULT' % (
table_name, self._quote(column))
self._exec_sql_and_commit(sql)
def table_column_set_null(self, table, column, is_null, schema=None):
"""Change whether column can contain null values."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ALTER %s ' % (table_name, self._quote(column))
if is_null:
sql += 'DROP NOT NULL'
else:
sql += 'SET NOT NULL'
self._exec_sql_and_commit(sql)
def table_add_primary_key(self, table, column, schema=None):
"""Add a primery key (with one column) to a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def table_add_unique_constraint(self, table, column, schema=None):
"""Add a unique constraint to a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s ADD UNIQUE (%s)' % (table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def table_delete_constraint(self, table, constraint, schema=None):
"""Delete constraint in a table."""
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s DROP CONSTRAINT %s' % (table_name,
self._quote(constraint))
self._exec_sql_and_commit(sql)
def table_move_to_schema(self, table, new_schema, schema=None):
if new_schema == schema:
return
table_name = self._table_name(schema, table)
sql = 'ALTER TABLE %s SET SCHEMA %s' % (table_name,
self._quote(new_schema))
self._exec_sql_and_commit(sql)
# Update geometry_columns if PostGIS is enabled
if self.has_postgis:
sql = "UPDATE geometry_columns SET f_table_schema='%s' \
WHERE f_table_name='%s'" \
% (self._quote_unicode(new_schema), self._quote_unicode(table))
if schema is not None:
sql += " AND f_table_schema='%s'" % self._quote_unicode(schema)
self._exec_sql_and_commit(sql)
def create_index(self, table, name, column, schema=None):
"""Create index on one column using default options."""
table_name = self._table_name(schema, table)
idx_name = self._quote(name)
sql = 'CREATE INDEX %s ON %s (%s)' % (idx_name, table_name,
self._quote(column))
self._exec_sql_and_commit(sql)
def create_spatial_index(self, table, schema=None, geom_column='the_geom'):
table_name = self._table_name(schema, table)
idx_name = self._quote(u"sidx_%s_%s" % (table, geom_column))
sql = 'CREATE INDEX %s ON %s USING GIST(%s)' % (
idx_name, table_name, self._quote(geom_column))
self._exec_sql_and_commit(sql)
def delete_index(self, name, schema=None):
index_name = self._table_name(schema, name)
sql = 'DROP INDEX %s' % index_name
self._exec_sql_and_commit(sql)
def get_database_privileges(self):
"""DB privileges: (can create schemas, can create temp. tables).
"""
sql = "SELECT has_database_privilege('%(d)s', 'CREATE'), \
has_database_privilege('%(d)s', 'TEMP')" \
% {'d': self._quote_unicode(self.dbname)}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def get_schema_privileges(self, schema):
"""Schema privileges: (can create new objects, can access objects
in schema)."""
sql = "SELECT has_schema_privilege('%(s)s', 'CREATE'), \
has_schema_privilege('%(s)s', 'USAGE')" \
% {'s': self._quote_unicode(schema)}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def get_table_privileges(self, table, schema=None):
"""Table privileges: (select, insert, update, delete).
"""
t = self._table_name(schema, table)
sql = """SELECT has_table_privilege('%(t)s', 'SELECT'),
has_table_privilege('%(t)s', 'INSERT'),
has_table_privilege('%(t)s', 'UPDATE'),
has_table_privilege('%(t)s', 'DELETE')""" \
% {'t': self._quote_unicode(t)}
c = self.con.cursor()
self._exec_sql(c, sql)
return c.fetchone()
def vacuum_analyze(self, table, schema=None):
"""Run VACUUM ANALYZE on a table."""
t = self._table_name(schema, table)
# VACUUM ANALYZE must be run outside transaction block - we
# have to change isolation level
self.con.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self.con.cursor()
self._exec_sql(c, 'VACUUM ANALYZE %s' % t)
self.con.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
def sr_info_for_srid(self, srid):
if not self.has_postgis:
return 'Unknown'
try:
c = self.con.cursor()
self._exec_sql(
c,
"SELECT srtext FROM spatial_ref_sys WHERE srid = '%d'" % srid)
srtext = c.fetchone()[0]
# Try to extract just SR name (should be quoted in double
# quotes)
x = re.search('"([^"]+)"', srtext)
if x is not None:
srtext = x.group()
return srtext
except DbError:
return 'Unknown'
def insert_table_row(self, table, values, schema=None, cursor=None):
"""Insert a row with specified values to a table.
If a cursor is specified, it doesn't commit (expecting that
there will be more inserts) otherwise it commits immediately.
"""
t = self._table_name(schema, table)
sql = ''
for value in values:
# TODO: quote values?
if sql:
sql += ', '
sql += value
sql = 'INSERT INTO %s VALUES (%s)' % (t, sql)
if cursor:
self._exec_sql(cursor, sql)
else:
self._exec_sql_and_commit(sql)
def _exec_sql(self, cursor, sql):
try:
cursor.execute(sql)
except psycopg2.Error as e:
raise DbError(str(e),
e.cursor.query.decode(e.cursor.connection.encoding))
def _exec_sql_and_commit(self, sql):
"""Tries to execute and commit some action, on error it rolls
back the change.
"""
try:
c = self.con.cursor()
self._exec_sql(c, sql)
self.con.commit()
except DbError:
self.con.rollback()
raise
def _quote(self, identifier):
"""Quote identifier if needed."""
# Make sure it's python unicode string
identifier = str(identifier)
# Is it needed to quote the identifier?
if self.re_ident_ok.match(identifier) is not None:
return identifier
# It's needed - let's quote it (and double the double-quotes)
return u'"%s"' % identifier.replace('"', '""')
def _quote_unicode(self, txt):
"""Make the string safe - replace ' with ''.
"""
# make sure it's python unicode string
txt = str(txt)
return txt.replace("'", "''")
def _table_name(self, schema, table):
if not schema:
return self._quote(table)
else:
return u'%s.%s' % (self._quote(schema), self._quote(table))
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 + '"'
