Skip to content

Commit dff1853

Browse files
committedFeb 6, 2019
Fix bug comment on postgres and others management
1 parent 7f0ab8b commit dff1853

File tree

15 files changed

+102
-108
lines changed

15 files changed

+102
-108
lines changed
 

‎python/plugins/db_manager/db_plugins/connector.py

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -233,3 +233,8 @@ def getSqlDictionary(self):
233233

234234
def getQueryBuilderDictionary(self):
235235
return {}
236+
237+
def setField(self, fld, tablename=None, db=None):
238+
if fld is None:
239+
return
240+
return fld.name, fld.dataType, str(fld.modifier), fld.notNull, fld.default, None

‎python/plugins/db_manager/db_plugins/gpkg/connector.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -644,7 +644,7 @@ def deleteTableColumn(self, table, column):
644644
return lyr.DeleteField(idx) == 0
645645
return False
646646

647-
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
647+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
648648
if self.isGeometryColumn(table, column):
649649
return False
650650

‎python/plugins/db_manager/db_plugins/gpkg/plugin.py

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,9 @@ def toSqlLayer(self, sql, geomCol, uniqueCol, layerName="QueryLayer", layerType=
177177
vl.setSubsetString(sql)
178178
return vl
179179

180+
def searchClass(self):
181+
return "GPKGDatabase"
182+
180183

181184
class GPKGTable(Table):
182185

@@ -301,10 +304,6 @@ def __init__(self, row, table):
301304
self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
302305
self.hasDefault = self.default
303306

304-
def getComment(self):
305-
"""Returns the comment for a field"""
306-
return ''
307-
308307

309308
class GPKGTableIndex(TableIndex):
310309

‎python/plugins/db_manager/db_plugins/oracle/connector.py

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -475,16 +475,16 @@ def updateCache(self, tableList, schema=None):
475475

476476
def singleGeomTypes(self, geomtypes, srids):
477477
"""Intelligent wkbtype grouping (multi with non multi)"""
478-
if (QgsWkbTypes.Polygon in geomtypes
479-
and QgsWkbTypes.MultiPolygon in geomtypes):
478+
if (QgsWkbTypes.Polygon in geomtypes and
479+
QgsWkbTypes.MultiPolygon in geomtypes):
480480
srids.pop(geomtypes.index(QgsWkbTypes.Polygon))
481481
geomtypes.pop(geomtypes.index(QgsWkbTypes.Polygon))
482-
if (QgsWkbTypes.Point in geomtypes
483-
and QgsWkbTypes.MultiPoint in geomtypes):
482+
if (QgsWkbTypes.Point in geomtypes and
483+
QgsWkbTypes.MultiPoint in geomtypes):
484484
srids.pop(geomtypes.index(QgsWkbTypes.Point))
485485
geomtypes.pop(geomtypes.index(QgsWkbTypes.Point))
486-
if (QgsWkbTypes.LineString in geomtypes
487-
and QgsWkbTypes.MultiLineString in geomtypes):
486+
if (QgsWkbTypes.LineString in geomtypes and
487+
QgsWkbTypes.MultiLineString in geomtypes):
488488
srids.pop(geomtypes.index(QgsWkbTypes.LineString))
489489
geomtypes.pop(geomtypes.index(QgsWkbTypes.LineString))
490490
if QgsWkbTypes.Unknown in geomtypes and len(geomtypes) > 1:

‎python/plugins/db_manager/db_plugins/oracle/plugin.py

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -91,8 +91,7 @@ def connect(self, parent=None):
9191
uri = QgsDataSourceUri()
9292

9393
settingsList = ["host", "port", "database", "username", "password"]
94-
host, port, database, username, password = [
95-
settings.value(x, "", type=str) for x in settingsList]
94+
host, port, database, username, password = [settings.value(x, "", type=str) for x in settingsList]
9695

9796
# get all of the connexion options
9897

@@ -203,8 +202,7 @@ def toSqlLayer(self, sql, geomCol, uniqueCol,
203202
uri = self.uri()
204203
con = self.database().connector
205204

206-
uri.setDataSource(u"", u"({}\n)".format(
207-
sql), geomCol, filter, uniqueCol.strip(u'"'))
205+
uri.setDataSource(u"", u"({}\n)".format(sql), geomCol, filter, uniqueCol.strip(u'"'))
208206
if avoidSelectById:
209207
uri.disableSelectAtId(True)
210208
provider = self.dbplugin().providerName()
@@ -263,6 +261,9 @@ def registerDatabaseActions(self, mainWindow):
263261
mainWindow.registerAction(action, QApplication.translate(
264262
"DBManagerPlugin", "&Table"), self.emptyTableActionSlot)
265263

264+
def searchClass(self):
265+
return "ORDatabase"
266+
266267

267268
class ORSchema(Schema):
268269

@@ -524,7 +525,7 @@ def __init__(self, row, table):
524525
def type2String(self):
525526
if (u"TIMESTAMP" in self.dataType or
526527
self.dataType in [u"DATE", u"SDO_GEOMETRY",
527-
u"BINARY_FLOAT", u"BINARY_DOUBLE"]):
528+
u"BINARY_FLOAT", u"BINARY_DOUBLE"]):
528529
return u"{}".format(self.dataType)
529530
if self.charMaxLen in [None, -1]:
530531
return u"{}".format(self.dataType)
@@ -559,10 +560,6 @@ def update(self, new_name, new_type_str=None, new_not_null=None,
559560
self.table().refreshIndexes()
560561
return ret
561562

562-
def getComment(self):
563-
"""Returns the comment for a field"""
564-
return ''
565-
566563

567564
class ORTableConstraint(TableConstraint):
568565

‎python/plugins/db_manager/db_plugins/plugin.py

Lines changed: 1 addition & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1096,22 +1096,7 @@ def definition(self):
10961096
return txt
10971097

10981098
def getComment(self):
1099-
"""Returns the comment for a field"""
1100-
tab = self.table()
1101-
# SQL Query checking if a comment exists for the field
1102-
sql_cpt = "Select count(*) from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tab.name, self.name)
1103-
# SQL Query that return the comment of the field
1104-
sql = "Select pd.description from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tab.name, self.name)
1105-
c = tab.database().connector._execute(None, sql_cpt) # Execute Check query
1106-
res = tab.database().connector._fetchone(c)[0] # Store result
1107-
if res == 1:
1108-
# When a comment exists
1109-
c = tab.database().connector._execute(None, sql) # Execute query
1110-
res = tab.database().connector._fetchone(c)[0] # Store result
1111-
tab.database().connector._close_cursor(c) # Close cursor
1112-
return res # Return comment
1113-
else:
1114-
return ''
1099+
return ''
11151100

11161101
def delete(self):
11171102
return self.table().deleteField(self)

‎python/plugins/db_manager/db_plugins/postgis/connector.py

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -511,6 +511,25 @@ def getTableFields(self, table):
511511
self._close_cursor(c)
512512
return res
513513

514+
def setField(self, fld, tablename, db):
515+
if fld is None:
516+
return
517+
print (tablename)
518+
# Check with SQL query if a comment exists for the field
519+
sql_cpt = "Select count(*) from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tablename, fld.name)
520+
# Get the comment for the field with SQL Query
521+
sql = "Select pd.description from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tablename, fld.name)
522+
c = db.connector._execute(None, sql_cpt) # Execute check query
523+
res = db.connector._fetchone(c)[0] # Fetch data
524+
# Check if result is 1 then it's ok, else we don't want to get a value
525+
if res == 1:
526+
c = db.connector._execute(None, sql) # Execute query returning the comment value
527+
res2 = db.connector._fetchone(c)[0] # Fetch the comment value
528+
db.connector._close_cursor(c) # Close cursor
529+
else :
530+
res2 = None
531+
return fld.name, fld.dataType, str(fld.modifier), fld.notNull, fld.default, res2
532+
514533
def getTableIndexes(self, table):
515534
""" get info about table's indexes. ignore primary key constraint index, they get listed in constraints """
516535
schema, tablename = self.getSchemaTableName(table)
@@ -857,7 +876,7 @@ def deleteTableColumn(self, table, column):
857876
sql = u"ALTER TABLE %s DROP %s" % (self.quoteId(table), self.quoteId(column))
858877
self._execute_and_commit(sql)
859878

860-
def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None, comment=None):
879+
def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None, comment=None, test=None):
861880
if new_name is None and data_type is None and not_null is None and default is None and comment is None:
862881
return
863882

‎python/plugins/db_manager/db_plugins/postgis/plugin.py

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -181,6 +181,9 @@ def runRefreshMaterializedViewSlot(self, item, action, parent):
181181
def hasLowercaseFieldNamesOption(self):
182182
return True
183183

184+
def searchClass(self):
185+
return "PGDatabase"
186+
184187

185188
class PGSchema(Schema):
186189

@@ -396,6 +399,24 @@ def __init__(self, row, table):
396399
if con.type == TableConstraint.TypePrimaryKey and self.num in con.columns:
397400
self.primaryKey = True
398401
break
402+
403+
def getComment(self):
404+
"""Returns the comment for a field"""
405+
tab = self.table()
406+
# SQL Query checking if a comment exists for the field
407+
sql_cpt = "Select count(*) from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tab.name, self.name)
408+
# SQL Query that return the comment of the field
409+
sql = "Select pd.description from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (tab.name, self.name)
410+
c = tab.database().connector._execute(None, sql_cpt) # Execute Check query
411+
res = tab.database().connector._fetchone(c)[0] # Store result
412+
if res == 1:
413+
# When a comment exists
414+
c = tab.database().connector._execute(None, sql) # Execute query
415+
res = tab.database().connector._fetchone(c)[0] # Store result
416+
tab.database().connector._close_cursor(c) # Close cursor
417+
return res # Return comment
418+
else:
419+
return ''
399420

400421

401422
class PGTableConstraint(TableConstraint):

‎python/plugins/db_manager/db_plugins/spatialite/connector.py

Lines changed: 2 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -465,8 +465,6 @@ def deleteTable(self, table):
465465
self._execute(c, sql)
466466
self._commit()
467467

468-
return True
469-
470468
def emptyTable(self, table):
471469
""" delete all rows from table """
472470
if self.isRasterTable(table):
@@ -496,7 +494,6 @@ def renameTable(self, table, new_table):
496494
self._execute(c, sql)
497495

498496
self._commit()
499-
return True
500497

501498
def moveTable(self, table, new_table, new_schema=None):
502499
return self.renameTable(table, new_table)
@@ -574,16 +571,7 @@ def runVacuum(self):
574571
def addTableColumn(self, table, field_def):
575572
""" add a column to table """
576573
sql = u"ALTER TABLE %s ADD %s" % (self.quoteId(table), field_def)
577-
self._execute(None, sql)
578-
579-
sql = u"SELECT InvalidateLayerStatistics(%s)" % (self.quoteId(table))
580-
self._execute(None, sql)
581-
582-
sql = u"SELECT UpdateLayerStatistics(%s)" % (self.quoteId(table))
583-
self._execute(None, sql)
584-
585-
self._commit()
586-
return True
574+
self._execute_and_commit(sql)
587575

588576
def deleteTableColumn(self, table, column):
589577
""" delete column from a table """
@@ -595,7 +583,7 @@ def deleteTableColumn(self, table, column):
595583
sql = u"SELECT DiscardGeometryColumn(%s, %s)" % (self.quoteString(tablename), self.quoteString(column))
596584
self._execute_and_commit(sql)
597585

598-
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
586+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
599587
return False # column editing not supported
600588

601589
def renameTableColumn(self, table, column, new_name):

‎python/plugins/db_manager/db_plugins/spatialite/plugin.py

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -175,6 +175,9 @@ def explicitSpatialIndex(self):
175175
def spatialIndexClause(self, src_table, src_column, dest_table, dest_column):
176176
return u""" "%s".ROWID IN (\nSELECT ROWID FROM SpatialIndex WHERE f_table_name='%s' AND search_frame="%s"."%s") """ % (src_table, src_table, dest_table, dest_column)
177177

178+
def searchClass(self):
179+
return "SLDatabase"
180+
178181

179182
class SLTable(Table):
180183

@@ -294,10 +297,6 @@ def __init__(self, row, table):
294297
self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
295298
self.hasDefault = self.default
296299

297-
def getComment(self):
298-
"""Returns the comment for a field"""
299-
return ''
300-
301300

302301
class SLTableIndex(TableIndex):
303302

‎python/plugins/db_manager/db_plugins/vlayers/connector.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -349,7 +349,7 @@ def addTableColumn(self, table, field_def):
349349
def deleteTableColumn(self, table, column):
350350
print("**unimplemented** deleteTableColumn")
351351

352-
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
352+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
353353
print("**unimplemented** updateTableColumn")
354354

355355
def renameTableColumn(self, table, column, new_name):

‎python/plugins/db_manager/db_plugins/vlayers/plugin.py

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -132,6 +132,9 @@ def explicitSpatialIndex(self):
132132
def spatialIndexClause(self, src_table, src_column, dest_table, dest_column):
133133
return '"%s"._search_frame_ = "%s"."%s"' % (src_table, dest_table, dest_column)
134134

135+
def searchClass(self):
136+
return "FakeDatabase"
137+
135138

136139
class LTable(Table):
137140

@@ -192,7 +195,3 @@ def __init__(self, row, table):
192195
TableField.__init__(self, table)
193196
self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
194197
self.hasDefault = self.default
195-
196-
def getComment(self):
197-
"""Returns the comment for a field"""
198-
return ''

‎python/plugins/db_manager/dlg_field_properties.py

Lines changed: 14 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,6 @@
2727
from qgis.PyQt.QtWidgets import QDialog, QMessageBox
2828

2929
from .db_plugins.plugin import TableField
30-
3130
from .ui.ui_DlgFieldProperties import Ui_DbManagerDlgFieldProperties as Ui_Dialog
3231

3332

@@ -42,37 +41,22 @@ def __init__(self, parent=None, fld=None, table=None, db=None):
4241

4342
for item in self.db.connector.fieldTypes():
4443
self.cboType.addItem(item)
45-
self.setField(self.fld)
4644

47-
self.buttonBox.accepted.connect(self.onOK)
4845

49-
def setField(self, fld):
50-
if fld is None:
51-
return
52-
self.editName.setText(fld.name)
53-
self.cboType.setEditText(fld.dataType)
54-
if fld.modifier:
55-
self.editLength.setText(str(fld.modifier))
56-
self.chkNull.setChecked(not fld.notNull)
57-
if fld.hasDefault:
58-
self.editDefault.setText(fld.default)
59-
# This is an ugly patch, but the comments PR https://github.com/qgis/QGIS/pull/8831 added
60-
# support for postgres only and broke all the others :(
61-
try:
62-
# Check with SQL query if a comment exists for the field
63-
sql_cpt = "Select count(*) from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (self.table.name, self.editName.text())
64-
# Get the comment for the field with SQL Query
65-
sql = "Select pd.description from pg_description pd, pg_class pc, pg_attribute pa where relname = '%s' and attname = '%s' and pa.attrelid = pc.oid and pd.objoid = pc.oid and pd.objsubid = pa.attnum" % (self.table.name, self.editName.text())
66-
c = self.db.connector._execute(None, sql_cpt) # Execute check query
67-
res = self.db.connector._fetchone(c)[0] # Fetch data
68-
# Check if result is 1 then it's ok, else we don't want to get a value
69-
if res == 1:
70-
c = self.db.connector._execute(None, sql) # Execute query returning the comment value
71-
res = self.db.connector._fetchone(c)[0] # Fetch the comment value
72-
self.db.connector._close_cursor(c) # Close cursor
73-
self.editCom.setText(res) # Set comment value
74-
except:
75-
self.editCom.setEnabled(False)
46+
objClass = self.db.searchClass()
47+
if objClass != "PGDatabase":
48+
self.label_6.setVisible(False)
49+
self.editCom.setVisible(False)
50+
51+
name, dataType, modifier, chkNull, hasDefault, chkCom = self.db.connector.setField(self.fld, self.table.name, self.db)
52+
self.editName.setText(name)
53+
self.cboType.setEditText(dataType)
54+
self.editLength.setText(modifier)
55+
self.chkNull.setChecked(not chkNull)
56+
self.editDefault.setText(hasDefault)
57+
self.editCom.setText(chkCom)
58+
59+
self.buttonBox.accepted.connect(self.onOK)
7660

7761
def getField(self, newCopy=False):
7862
fld = TableField(self.table) if not self.fld or newCopy else self.fld

‎python/plugins/db_manager/dlg_import_vector.py

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,8 @@
2525
from builtins import range
2626

2727
from qgis.PyQt.QtCore import Qt, QFileInfo
28-
from qgis.PyQt.QtWidgets import QDialog, QFileDialog, QMessageBox
28+
from qgis.PyQt.QtWidgets import QDialog, QFileDialog, QMessageBox, QApplication
29+
from qgis.PyQt.QtGui import QCursor
2930

3031
from qgis.core import (QgsDataSourceUri,
3132
QgsVectorLayer,
@@ -51,6 +52,11 @@ def __init__(self, inLayer, outDb, outUri, parent=None):
5152
self.outUri = outUri
5253
self.setupUi(self)
5354

55+
objClass = self.db.searchClass()
56+
if objClass != "PGDatabase":
57+
self.chkCom.setVisible(False)
58+
self.editCom.setVisible(False)
59+
5460
self.default_pk = "id"
5561
self.default_geom = "geom"
5662

@@ -368,9 +374,9 @@ def accept(self):
368374
self.db.connector.createSpatialIndex((schema, table), geom)
369375

370376
# add comment on table
371-
if self.chkCom.isEnabled() and self.chkCom.isChecked():
377+
if self.chkCom.isEnabled() and self.chkCom.isChecked() and objClass == "PGDatabase":
372378
# using connector executing COMMENT ON TABLE query (with editCome.text() value)
373-
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}E\''.format(schema, table, self.editCom.text()))
379+
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}\''.format(schema, table, self.editCom.text()))
374380

375381
self.db.connection().reconnect()
376382
self.db.refresh()

‎python/plugins/db_manager/dlg_table_properties.py

Lines changed: 7 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -29,7 +29,7 @@
2929
from qgis.utils import OverrideCursor
3030

3131
from .db_plugins.data_model import TableFieldsModel, TableConstraintsModel, TableIndexesModel
32-
from .db_plugins.plugin import BaseError, DbError
32+
from .db_plugins.plugin import BaseError
3333
from .dlg_db_error import DlgDbError
3434

3535
from .dlg_field_properties import DlgFieldProperties
@@ -50,6 +50,10 @@ def __init__(self, table, parent=None):
5050

5151
self.db = self.table.database()
5252

53+
objClass = self.db.searchClass()
54+
if objClass != "PGDatabase":
55+
self.tabs.removeTab(3)
56+
5357
m = TableFieldsModel(self)
5458
self.viewFields.setModel(m)
5559

@@ -333,37 +337,25 @@ def deleteIndex(self):
333337
DlgDbError.showError(e, self)
334338

335339
def createComment(self):
336-
"""Adds a comment to the selected table"""
337-
340+
#Function that add a comment to the selected table
338341
try:
339342
#Using the db connector, executing de SQL query Comment on table
340343
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}\';'.format(self.table.schema().name, self.table.name, self.viewComment.text()))
341344
except DbError as e:
342345
DlgDbError.showError(e, self)
343346
return
344-
except Exception as e:
345-
# This is an ugly patch, but the comments PR https://github.com/qgis/QGIS/pull/8831 added
346-
# support for postgres only and broke all the others :(
347-
QMessageBox.information(self, self.tr("Add comment"), self.tr("Comments are not supported for this database."))
348-
return
349347
self.refresh()
350348
#Display successful message
351349
QMessageBox.information(self, self.tr("Add comment"), self.tr("Table successfully commented"))
352350

353351
def deleteComment(self):
354-
"""Drops the comment on the selected table"""
355-
352+
#Function that drop the comment to the selected table
356353
try:
357354
#Using the db connector, executing de SQL query Comment on table using the NULL definition
358355
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS NULL;'.format(self.table.schema().name, self.table.name))
359356
except DbError as e:
360357
DlgDbError.showError(e, self)
361358
return
362-
except Exception as e:
363-
# This is an ugly patch, but the comments PR https://github.com/qgis/QGIS/pull/8831 added
364-
# support for postgres only and broke all the others :(
365-
QMessageBox.information(self, self.tr("Add comment"), self.tr("Comments are not supported for this database."))
366-
return
367359
self.refresh()
368360
#Refresh line edit, put a void comment
369361
self.viewComment.setText('')

0 commit comments

Comments
 (0)
Please sign in to comment.