Skip to content

Commit aa9fe9e

Browse files
alexbruyalexbruy
authoredJun 14, 2016
Merge pull request #3118 from arnaud-morvan/processing_gdal_postgis_credentials
[processing] support postgis service parameter and credentials input
·
ltr-3_40archive/master_2
2 parents bbbc9d0 + 8ddae27 commit aa9fe9e

File tree

6 files changed

+147
-125
lines changed

6 files changed

+147
-125
lines changed
 

‎python/plugins/processing/algs/gdal/ogr2ogrtabletopostgislist.py

Lines changed: 20 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -36,6 +36,7 @@
3636
from processing.algs.gdal.GdalAlgorithm import GdalAlgorithm
3737
from processing.algs.gdal.GdalUtils import GdalUtils
3838

39+
from processing.tools.postgis import uri_from_name, GeoDB
3940
from processing.tools.system import isWindows
4041
from processing.tools.vector import ogrConnectionString, ogrLayerName
4142

@@ -64,6 +65,10 @@ class Ogr2OgrTableToPostGisList(GdalAlgorithm):
6465
PRECISION = 'PRECISION'
6566
OPTIONS = 'OPTIONS'
6667

68+
def __init__(self):
69+
GdalAlgorithm.__init__(self)
70+
self.processing = False
71+
6772
def dbConnectionNames(self):
6873
settings = QSettings()
6974
settings.beginGroup('/PostgreSQL/connections/')
@@ -112,15 +117,18 @@ def defineCharacteristics(self):
112117
self.addParameter(ParameterString(self.OPTIONS,
113118
self.tr('Additional creation options'), '', optional=True))
114119

120+
def processAlgorithm(self, progress):
121+
self.processing = True
122+
GdalAlgorithm.processAlgorithm(self, progress)
123+
self.processing = False
124+
115125
def getConsoleCommands(self):
116126
connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
117-
settings = QSettings()
118-
mySettings = '/PostgreSQL/connections/' + connection
119-
dbname = settings.value(mySettings + '/database')
120-
user = settings.value(mySettings + '/username')
121-
host = settings.value(mySettings + '/host')
122-
port = settings.value(mySettings + '/port')
123-
password = settings.value(mySettings + '/password')
127+
uri = uri_from_name(connection)
128+
if self.processing:
129+
# to get credentials input when needed
130+
uri = GeoDB(uri=uri).uri
131+
124132
inLayer = self.getParameterValue(self.INPUT_LAYER)
125133
ogrLayer = ogrConnectionString(inLayer)[1:-1]
126134
shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
@@ -150,19 +158,11 @@ def getConsoleCommands(self):
150158
arguments.append('"' + shapeEncoding + '"')
151159
arguments.append('-f')
152160
arguments.append('PostgreSQL')
153-
arguments.append('PG:"host=')
154-
arguments.append(host)
155-
arguments.append('port=')
156-
arguments.append(port)
157-
if len(dbname) > 0:
158-
arguments.append('dbname=' + dbname)
159-
if len(password) > 0:
160-
arguments.append('password=' + password)
161-
if len(schema) > 0:
162-
arguments.append('active_schema=' + schema)
163-
else:
164-
arguments.append('active_schema=public')
165-
arguments.append('user=' + user + '"')
161+
arguments.append('PG:"')
162+
for token in uri.connectionInfo(self.processing).split(' '):
163+
arguments.append(token)
164+
arguments.append('active_schema={}'.format(schema or 'public'))
165+
arguments.append('"')
166166
arguments.append(ogrLayer)
167167
arguments.append('-nlt NONE')
168168
arguments.append(ogrLayerName(inLayer))

‎python/plugins/processing/algs/gdal/ogr2ogrtopostgislist.py

Lines changed: 20 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@
3939
from processing.algs.gdal.GdalAlgorithm import GdalAlgorithm
4040
from processing.algs.gdal.GdalUtils import GdalUtils
4141

42+
from processing.tools.postgis import uri_from_name, GeoDB
4243
from processing.tools.system import isWindows
4344
from processing.tools.vector import ogrConnectionString, ogrLayerName
4445

@@ -81,6 +82,10 @@ class Ogr2OgrToPostGisList(GdalAlgorithm):
8182
PROMOTETOMULTI = 'PROMOTETOMULTI'
8283
OPTIONS = 'OPTIONS'
8384

85+
def __init__(self):
86+
GdalAlgorithm.__init__(self)
87+
self.processing = False
88+
8489
def dbConnectionNames(self):
8590
settings = QSettings()
8691
settings.beginGroup('/PostgreSQL/connections/')
@@ -156,15 +161,18 @@ def defineCharacteristics(self):
156161
self.addParameter(ParameterString(self.OPTIONS,
157162
self.tr('Additional creation options'), '', optional=True))
158163

164+
def processAlgorithm(self, progress):
165+
self.processing = True
166+
GdalAlgorithm.processAlgorithm(self, progress)
167+
self.processing = False
168+
159169
def getConsoleCommands(self):
160170
connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
161-
settings = QSettings()
162-
mySettings = '/PostgreSQL/connections/' + connection
163-
dbname = settings.value(mySettings + '/database')
164-
user = settings.value(mySettings + '/username')
165-
host = settings.value(mySettings + '/host')
166-
port = settings.value(mySettings + '/port')
167-
password = settings.value(mySettings + '/password')
171+
uri = uri_from_name(connection)
172+
if self.processing:
173+
# to get credentials input when needed
174+
uri = GeoDB(uri=uri).uri
175+
168176
inLayer = self.getParameterValue(self.INPUT_LAYER)
169177
ogrLayer = ogrConnectionString(inLayer)[1:-1]
170178
shapeEncoding = self.getParameterValue(self.SHAPE_ENCODING)
@@ -208,17 +216,11 @@ def getConsoleCommands(self):
208216
arguments.append('"' + shapeEncoding + '"')
209217
arguments.append('-f')
210218
arguments.append('PostgreSQL')
211-
arguments.append('PG:"host=' + host)
212-
arguments.append('port=' + port)
213-
if len(dbname) > 0:
214-
arguments.append('dbname=' + dbname)
215-
if len(password) > 0:
216-
arguments.append('password=' + password)
217-
if len(schema) > 0:
218-
arguments.append('active_schema=' + schema)
219-
else:
220-
arguments.append('active_schema=public')
221-
arguments.append('user=' + user + '"')
219+
arguments.append('PG:"')
220+
for token in uri.connectionInfo(self.processing).split(' '):
221+
arguments.append(token)
222+
arguments.append('active_schema={}'.format(schema or 'public'))
223+
arguments.append('"')
222224
arguments.append(dimstring)
223225
arguments.append(ogrLayer)
224226
arguments.append(ogrLayerName(inLayer))

‎python/plugins/processing/algs/qgis/ImportIntoPostGIS.py

Lines changed: 3 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,8 @@ def defineCharacteristics(self):
8686

8787
def processAlgorithm(self, progress):
8888
connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)]
89+
db = postgis.GeoDB.from_name(connection)
90+
8991
schema = self.getParameterValue(self.SCHEMA)
9092
overwrite = self.getParameterValue(self.OVERWRITE)
9193
createIndex = self.getParameterValue(self.CREATEINDEX)
@@ -94,17 +96,6 @@ def processAlgorithm(self, progress):
9496
forceSinglePart = self.getParameterValue(self.FORCE_SINGLEPART)
9597
primaryKeyField = self.getParameterValue(self.PRIMARY_KEY)
9698
encoding = self.getParameterValue(self.ENCODING)
97-
settings = QSettings()
98-
mySettings = '/PostgreSQL/connections/' + connection
99-
try:
100-
database = settings.value(mySettings + '/database')
101-
username = settings.value(mySettings + '/username')
102-
host = settings.value(mySettings + '/host')
103-
port = settings.value(mySettings + '/port', type=int)
104-
password = settings.value(mySettings + '/password')
105-
except Exception as e:
106-
raise GeoAlgorithmExecutionException(
107-
self.tr('Wrong database connection name: %s' % connection))
10899

109100
layerUri = self.getParameterValue(self.INPUT)
110101
layer = dataobjects.getObjectFromUri(layerUri)
@@ -115,13 +106,6 @@ def processAlgorithm(self, progress):
115106
table = table.replace(' ', '').lower()[0:62]
116107
providerName = 'postgres'
117108

118-
try:
119-
db = postgis.GeoDB(host=host, port=port, dbname=database,
120-
user=username, passwd=password)
121-
except postgis.DbError as e:
122-
raise GeoAlgorithmExecutionException(
123-
self.tr("Couldn't connect to database:\n%s") % unicode(e))
124-
125109
geomColumn = self.getParameterValue(self.GEOMETRY_COLUMN)
126110
if not geomColumn:
127111
geomColumn = 'the_geom'
@@ -141,8 +125,7 @@ def processAlgorithm(self, progress):
141125
if not layer.hasGeometryType():
142126
geomColumn = None
143127

144-
uri = QgsDataSourceURI()
145-
uri.setConnection(host, unicode(port), database, username, password)
128+
uri = db.uri
146129
if primaryKeyField:
147130
uri.setDataSource(schema, table, geomColumn, '', primaryKeyField)
148131
else:

‎python/plugins/processing/algs/qgis/PostGISExecuteSQL.py

Lines changed: 1 addition & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -46,24 +46,7 @@ def defineCharacteristics(self):
4646

4747
def processAlgorithm(self, progress):
4848
connection = self.getParameterValue(self.DATABASE)
49-
settings = QSettings()
50-
mySettings = '/PostgreSQL/connections/' + connection
51-
try:
52-
database = settings.value(mySettings + '/database')
53-
username = settings.value(mySettings + '/username')
54-
host = settings.value(mySettings + '/host')
55-
port = settings.value(mySettings + '/port', type=int)
56-
password = settings.value(mySettings + '/password')
57-
except Exception as e:
58-
raise GeoAlgorithmExecutionException(
59-
self.tr('Wrong database connection name: %s' % connection))
60-
try:
61-
self.db = postgis.GeoDB(host=host, port=port,
62-
dbname=database, user=username, passwd=password)
63-
except postgis.DbError as e:
64-
raise GeoAlgorithmExecutionException(
65-
self.tr("Couldn't connect to database:\n%s") % unicode(e))
66-
49+
self.db = postgis.GeoDB.from_name(connection)
6750
sql = self.getParameterValue(self.SQL).replace('\n', ' ')
6851
try:
6952
self.db._exec_sql_and_commit(unicode(sql))

‎python/plugins/processing/gui/PostgisTableSelector.py

Lines changed: 7 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -100,23 +100,10 @@ def __init__(self, connection):
100100
def populateSchemas(self):
101101
if self.childCount() != 0:
102102
return
103-
settings = QSettings()
104-
connSettings = '/PostgreSQL/connections/' + self.connection
105-
database = settings.value(connSettings + '/database')
106-
user = settings.value(connSettings + '/username')
107-
host = settings.value(connSettings + '/host')
108-
port = settings.value(connSettings + '/port')
109-
passwd = settings.value(connSettings + '/password')
110-
uri = QgsDataSourceURI()
111-
uri.setConnection(host, str(port), database, user, passwd)
112-
connInfo = uri.connectionInfo()
113-
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
114-
if success:
115-
QgsCredentials.instance().put(connInfo, user, passwd)
116-
geodb = GeoDB(host, int(port), database, user, passwd)
117-
schemas = geodb.list_schemas()
118-
for oid, name, owner, perms in schemas:
119-
item = QTreeWidgetItem()
120-
item.setText(0, name)
121-
item.setIcon(0, self.schemaIcon)
122-
self.addChild(item)
103+
geodb = GeoDB.from_name(self.connection)
104+
schemas = geodb.list_schemas()
105+
for oid, name, owner, perms in schemas:
106+
item = QTreeWidgetItem()
107+
item.setText(0, name)
108+
item.setIcon(0, self.schemaIcon)
109+
self.addChild(item)

‎python/plugins/processing/tools/postgis.py

Lines changed: 96 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -29,10 +29,41 @@
2929
import psycopg2.extensions # For isolation levels
3030
import re
3131

32+
from qgis.PyQt.QtCore import QSettings
33+
from qgis.core import QgsDataSourceURI, QgsCredentials
34+
35+
3236
# Use unicode!
3337
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
3438

3539

40+
def uri_from_name(conn_name):
41+
settings = QSettings()
42+
settings.beginGroup(u"/PostgreSQL/connections/%s" % conn_name)
43+
44+
if not settings.contains("database"): # non-existent entry?
45+
raise DbError('There is no defined database connection "%s".' % conn_name)
46+
47+
uri = QgsDataSourceURI()
48+
49+
settingsList = ["service", "host", "port", "database", "username", "password", "authcfg"]
50+
service, host, port, database, username, password, authcfg = [settings.value(x, "", type=str) for x in settingsList]
51+
52+
useEstimatedMetadata = settings.value("estimatedMetadata", False, type=bool)
53+
sslmode = settings.value("sslmode", QgsDataSourceURI.SSLprefer, type=int)
54+
55+
settings.endGroup()
56+
57+
if service:
58+
uri.setConnection(service, database, username, password, sslmode, authcfg)
59+
else:
60+
uri.setConnection(host, port, database, username, password, sslmode, authcfg)
61+
62+
uri.setUseEstimatedMetadata(useEstimatedMetadata)
63+
64+
return uri
65+
66+
3667
class TableAttribute:
3768

3869
def __init__(self, row):
@@ -100,7 +131,10 @@ def __str__(self):
100131
return unicode(self).encode('utf-8')
101132

102133
def __unicode__(self):
103-
return u'MESSAGE: %s\nQUERY: %s' % (self.message, self.query)
134+
text = u'MESSAGE: %s' % self.message
135+
if self.query:
136+
text += u'\nQUERY: %s' % self.query
137+
return text
104138

105139

106140
class TableField:
@@ -138,41 +172,74 @@ def _quote(self, ident):
138172

139173
class GeoDB:
140174

175+
@classmethod
176+
def from_name(cls, conn_name):
177+
uri = uri_from_name(conn_name)
178+
return cls(uri=uri)
179+
141180
def __init__(self, host=None, port=None, dbname=None, user=None,
142-
passwd=None):
181+
passwd=None, service=None, uri=None):
143182
# Regular expression for identifiers without need to quote them
144183
self.re_ident_ok = re.compile(r"^\w+$")
145184

146-
self.host = host
147-
self.port = port
148-
self.dbname = dbname
149-
self.user = user
150-
self.passwd = passwd
151-
152-
if self.dbname == '' or self.dbname is None:
153-
self.dbname = self.user
154-
155-
try:
156-
self.con = psycopg2.connect(self.con_info())
157-
except psycopg2.OperationalError as e:
158-
raise DbError(unicode(e))
185+
if uri:
186+
self.uri = uri
187+
else:
188+
self.uri = QgsDataSourceURI()
189+
if service:
190+
self.uri.setConnection(service, dbname, user, passwd)
191+
else:
192+
self.uri.setConnection(host, port, dbname, user, passwd)
193+
194+
conninfo = self.uri.connectionInfo(False)
195+
err = None
196+
for i in range(4):
197+
expandedConnInfo = uri.connectionInfo(True)
198+
try:
199+
self.con = psycopg2.connect(expandedConnInfo.encode('utf-8'))
200+
if err is not None:
201+
QgsCredentials.instance().put(conninfo,
202+
self.uri.username(),
203+
self.uri.password())
204+
break
205+
except psycopg2.OperationalError as e:
206+
if i == 3:
207+
raise DbError(unicode(e))
208+
209+
err = unicode(e)
210+
user = self.uri.username()
211+
password = self.uri.password()
212+
(ok, user, password) = QgsCredentials.instance().get(conninfo,
213+
user,
214+
password,
215+
err)
216+
if not ok:
217+
raise DbError(u'Action cancelled by user')
218+
if user:
219+
self.uri.setUsername(user)
220+
if password:
221+
self.uri.setPassword(password)
222+
finally:
223+
# remove certs (if any) of the expanded connectionInfo
224+
expandedUri = QgsDataSourceURI(expandedConnInfo)
225+
226+
sslCertFile = expandedUri.param("sslcert")
227+
if sslCertFile:
228+
sslCertFile = sslCertFile.replace("'", "")
229+
os.remove(sslCertFile)
230+
231+
sslKeyFile = expandedUri.param("sslkey")
232+
if sslKeyFile:
233+
sslKeyFile = sslKeyFile.replace("'", "")
234+
os.remove(sslKeyFile)
235+
236+
sslCAFile = expandedUri.param("sslrootcert")
237+
if sslCAFile:
238+
sslCAFile = sslCAFile.replace("'", "")
239+
os.remove(sslCAFile)
159240

160241
self.has_postgis = self.check_postgis()
161242

162-
def con_info(self):
163-
con_str = ''
164-
if self.host:
165-
con_str += "host='%s' " % self.host
166-
if self.port:
167-
con_str += 'port=%d ' % self.port
168-
if self.dbname:
169-
con_str += "dbname='%s' " % self.dbname
170-
if self.user:
171-
con_str += "user='%s' " % self.user
172-
if self.passwd:
173-
con_str += "password='%s' " % self.passwd
174-
return con_str
175-
176243
def get_info(self):
177244
c = self.con.cursor()
178245
self._exec_sql(c, 'SELECT version()')

0 commit comments

Comments
 (0)
Please sign in to comment.