Skip to content

Commit daa05e5

Browse files
m-kuhnm-kuhn
authoredJan 23, 2019
Merge pull request #8831 from SIRS-CLS/sirs_db_manager
Add comment option on db_manager plugin postgis fields
·
ltr-3_40final-3_6_0
2 parents 263c4c2 + aa3679a commit daa05e5

File tree

13 files changed

+112
-56
lines changed

13 files changed

+112
-56
lines changed
 

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

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -259,7 +259,7 @@ def getObjectIter(self):
259259
class TableFieldsModel(SimpleTableModel):
260260

261261
def __init__(self, parent, editable=False):
262-
SimpleTableModel.__init__(self, ['Name', 'Type', 'Null', 'Default'], editable, parent)
262+
SimpleTableModel.__init__(self, ['Name', 'Type', 'Null', 'Default', 'Comment'], editable, parent)
263263

264264
def headerData(self, section, orientation, role):
265265
if orientation == Qt.Vertical and role == Qt.DisplayRole:
@@ -273,7 +273,7 @@ def flags(self, index):
273273
return flags
274274

275275
def append(self, fld):
276-
data = [fld.name, fld.type2String(), not fld.notNull, fld.default2String()]
276+
data = [fld.name, fld.type2String(), not fld.notNull, fld.default2String(), fld.getComment()]
277277
self.appendRow(self.rowFromData(data))
278278
row = self.rowCount() - 1
279279
self.setData(self.index(row, 0), fld, Qt.UserRole)
@@ -290,7 +290,6 @@ def getObject(self, row):
290290
val = self.data(self.index(row, 0), Qt.UserRole)
291291
fld = val if val is not None else self._getNewObject()
292292
fld.name = self.data(self.index(row, 0)) or ""
293-
294293
typestr = self.data(self.index(row, 1)) or ""
295294
regex = QRegExp("([^\\(]+)\\(([^\\)]+)\\)")
296295
startpos = regex.indexIn(typestr)
@@ -303,6 +302,7 @@ def getObject(self, row):
303302

304303
fld.notNull = self.data(self.index(row, 2), Qt.CheckStateRole) == Qt.Unchecked
305304
fld.primaryKey = self.data(self.index(row, 1), Qt.UserRole)
305+
fld.comment = self.data(self.index(row, 4), Qt.UserRole)
306306
return fld
307307

308308
def getFields(self):

‎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):
647+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
648648
if self.isGeometryColumn(table, column):
649649
return False
650650

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

Lines changed: 7 additions & 7 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 and
479-
QgsWkbTypes.MultiPolygon in geomtypes):
478+
if (QgsWkbTypes.Polygon in geomtypes
479+
and QgsWkbTypes.MultiPolygon in geomtypes):
480480
srids.pop(geomtypes.index(QgsWkbTypes.Polygon))
481481
geomtypes.pop(geomtypes.index(QgsWkbTypes.Polygon))
482-
if (QgsWkbTypes.Point in geomtypes and
483-
QgsWkbTypes.MultiPoint in geomtypes):
482+
if (QgsWkbTypes.Point in geomtypes
483+
and QgsWkbTypes.MultiPoint in geomtypes):
484484
srids.pop(geomtypes.index(QgsWkbTypes.Point))
485485
geomtypes.pop(geomtypes.index(QgsWkbTypes.Point))
486-
if (QgsWkbTypes.LineString in geomtypes and
487-
QgsWkbTypes.MultiLineString in geomtypes):
486+
if (QgsWkbTypes.LineString in geomtypes
487+
and 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:
@@ -1336,7 +1336,7 @@ def deleteTableColumn(self, table, column):
13361336

13371337
def updateTableColumn(self, table, column, new_name=None,
13381338
data_type=None, not_null=None,
1339-
default=None):
1339+
default=None, comment=None):
13401340
"""Update properties of a column in a table."""
13411341

13421342
schema, tablename = self.getSchemaTableName(table)

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

Lines changed: 24 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1095,13 +1095,31 @@ def definition(self):
10951095
txt += u" DEFAULT %s" % self.default2String()
10961096
return txt
10971097

1098+
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 ''
1115+
10981116
def delete(self):
10991117
return self.table().deleteField(self)
11001118

11011119
def rename(self, new_name):
11021120
return self.update(new_name)
11031121

1104-
def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None):
1122+
def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None, new_comment=None):
11051123
self.table().aboutToChange.emit()
11061124
if self.name == new_name:
11071125
new_name = None
@@ -1111,10 +1129,12 @@ def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str
11111129
new_not_null = None
11121130
if self.default2String() == new_default_str:
11131131
new_default_str = None
1114-
1132+
if self.comment == new_comment:
1133+
# Update also a new_comment
1134+
new_comment = None
11151135
ret = self.table().database().connector.updateTableColumn((self.table().schemaName(), self.table().name),
1116-
self.name, new_name, new_type_str, new_not_null,
1117-
new_default_str)
1136+
self.name, new_name, new_type_str,
1137+
new_not_null, new_default_str, new_comment)
11181138
if ret is not False:
11191139
self.table().refreshFields()
11201140
return ret

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

Lines changed: 8 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -857,8 +857,8 @@ def deleteTableColumn(self, table, column):
857857
sql = u"ALTER TABLE %s DROP %s" % (self.quoteId(table), self.quoteId(column))
858858
self._execute_and_commit(sql)
859859

860-
def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None):
861-
if new_name is None and data_type is None and not_null is None and default is None:
860+
def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None, comment=None):
861+
if new_name is None and data_type is None and not_null is None and default is None and comment is None:
862862
return
863863

864864
c = self._get_cursor()
@@ -895,6 +895,12 @@ def updateTableColumn(self, table, column, new_name=None, data_type=None, not_nu
895895
self.quoteString(new_name), schema_where, self.quoteString(tablename), self.quoteString(column))
896896
self._execute(c, sql)
897897

898+
# comment the column
899+
if comment is not None:
900+
schema, tablename = self.getSchemaTableName(table)
901+
sql = u"COMMENT ON COLUMN %s.%s.%s is '%s'" % (schema, tablename, column, comment)
902+
self._execute(c, sql)
903+
898904
self._commit()
899905

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

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

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -110,8 +110,8 @@ def generalInfo(self):
110110
if not self.table.isView:
111111
if self.table.rowCount is not None:
112112
if abs(self.table.estimatedRowCount - self.table.rowCount) > 1 and \
113-
(self.table.estimatedRowCount > 2 * self.table.rowCount or
114-
self.table.rowCount > 2 * self.table.estimatedRowCount):
113+
(self.table.estimatedRowCount > 2 * self.table.rowCount
114+
or self.table.rowCount > 2 * self.table.estimatedRowCount):
115115
ret.append(HtmlParagraph(QApplication.translate("DBManagerPlugin",
116116
"<warning> There's a significant difference between estimated and real row count. "
117117
'Consider running <a href="action:vacuumanalyze/run">VACUUM ANALYZE</a>.')))
@@ -162,7 +162,7 @@ def fieldsDetails(self):
162162
header = (
163163
"#", QApplication.translate("DBManagerPlugin", "Name"), QApplication.translate("DBManagerPlugin", "Type"),
164164
QApplication.translate("DBManagerPlugin", "Length"), QApplication.translate("DBManagerPlugin", "Null"),
165-
QApplication.translate("DBManagerPlugin", "Default"))
165+
QApplication.translate("DBManagerPlugin", "Default"), QApplication.translate("DBManagerPlugin", "Comment"))
166166
tbl.append(HtmlTableHeader(header))
167167

168168
# add table contents
@@ -174,7 +174,7 @@ def fieldsDetails(self):
174174
attrs = {"class": "underline"} if fld.primaryKey else None
175175
name = HtmlTableCol(fld.name, attrs)
176176

177-
tbl.append((fld.num, name, fld.type2String(), char_max_len, is_null_txt, fld.default2String()))
177+
tbl.append((fld.num, name, fld.type2String(), char_max_len, is_null_txt, fld.default2String(), fld.getComment()))
178178

179179
return HtmlTable(tbl, {"class": "header"})
180180

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

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -595,9 +595,7 @@ def deleteTableColumn(self, table, column):
595595
sql = u"SELECT DiscardGeometryColumn(%s, %s)" % (self.quoteString(tablename), self.quoteString(column))
596596
self._execute_and_commit(sql)
597597

598-
return True
599-
600-
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None):
598+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
601599
return False # column editing not supported
602600

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

‎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):
352+
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
353353
print("**unimplemented** updateTableColumn")
354354

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

‎python/plugins/db_manager/dlg_field_properties.py

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -56,6 +56,18 @@ def setField(self, fld):
5656
self.chkNull.setChecked(not fld.notNull)
5757
if fld.hasDefault:
5858
self.editDefault.setText(fld.default)
59+
# Check with SQL query if a comment exists for the field
60+
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())
61+
# Get the comment for the field with SQL Query
62+
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())
63+
c = self.db.connector._execute(None, sql_cpt) # Execute check query
64+
res = self.db.connector._fetchone(c)[0] # Fetch data
65+
# Check if result is 1 then it's ok, else we don't want to get a value
66+
if res == 1:
67+
c = self.db.connector._execute(None, sql) # Execute query returning the comment value
68+
res = self.db.connector._fetchone(c)[0] # Fetch the comment value
69+
self.db.connector._close_cursor(c) # Close cursor
70+
self.editCom.setText(res) # Set comment value
5971

6072
def getField(self, newCopy=False):
6173
fld = TableField(self.table) if not self.fld or newCopy else self.fld
@@ -64,6 +76,8 @@ def getField(self, newCopy=False):
6476
fld.notNull = not self.chkNull.isChecked()
6577
fld.default = self.editDefault.text()
6678
fld.hasDefault = fld.default != ""
79+
# Get the comment from the LineEdit
80+
fld.comment = self.editCom.text()
6781
try:
6882
modifier = int(self.editLength.text())
6983
except ValueError:

‎python/plugins/db_manager/dlg_table_properties.py

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -162,7 +162,7 @@ def editColumn(self):
162162
with OverrideCursor(Qt.WaitCursor):
163163
self.aboutToChangeTable.emit()
164164
try:
165-
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String())
165+
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String(), new_fld.comment)
166166
self.refresh()
167167
except BaseError as e:
168168
DlgDbError.showError(e, self)

‎python/plugins/db_manager/ui/DlgFieldProperties.ui

100644100755
Lines changed: 40 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -16,24 +16,34 @@
1616
<layout class="QVBoxLayout">
1717
<item>
1818
<layout class="QGridLayout">
19-
<item row="0" column="0">
20-
<widget class="QLabel" name="label">
19+
<item row="3" column="0">
20+
<widget class="QLabel" name="label_3">
2121
<property name="text">
22-
<string>Name</string>
22+
<string>Can be NULL</string>
2323
</property>
2424
</widget>
2525
</item>
26-
<item row="0" column="1">
27-
<widget class="QLineEdit" name="editName">
26+
<item row="3" column="1">
27+
<widget class="QCheckBox" name="chkNull">
2828
<property name="text">
2929
<string notr="true"/>
3030
</property>
31+
<property name="checked">
32+
<bool>true</bool>
33+
</property>
3134
</widget>
3235
</item>
33-
<item row="1" column="0">
34-
<widget class="QLabel" name="label_2">
36+
<item row="4" column="0">
37+
<widget class="QLabel" name="label_4">
3538
<property name="text">
36-
<string>Type</string>
39+
<string>Default value expression</string>
40+
</property>
41+
</widget>
42+
</item>
43+
<item row="0" column="0">
44+
<widget class="QLabel" name="label">
45+
<property name="text">
46+
<string>Name</string>
3747
</property>
3848
</widget>
3949
</item>
@@ -47,28 +57,18 @@
4757
</property>
4858
</widget>
4959
</item>
50-
<item row="3" column="0">
51-
<widget class="QLabel" name="label_3">
60+
<item row="1" column="0">
61+
<widget class="QLabel" name="label_2">
5262
<property name="text">
53-
<string>Can be NULL</string>
63+
<string>Type</string>
5464
</property>
5565
</widget>
5666
</item>
57-
<item row="3" column="1">
58-
<widget class="QCheckBox" name="chkNull">
67+
<item row="0" column="1">
68+
<widget class="QLineEdit" name="editName">
5969
<property name="text">
6070
<string notr="true"/>
6171
</property>
62-
<property name="checked">
63-
<bool>true</bool>
64-
</property>
65-
</widget>
66-
</item>
67-
<item row="4" column="0">
68-
<widget class="QLabel" name="label_4">
69-
<property name="text">
70-
<string>Default value expression</string>
71-
</property>
7272
</widget>
7373
</item>
7474
<item row="4" column="1">
@@ -95,6 +95,23 @@
9595
</property>
9696
</widget>
9797
</item>
98+
<item row="6" column="1">
99+
<widget class="QLineEdit" name="editCom">
100+
<property name="toolTip">
101+
<string>&lt;html&gt;&lt;head/&gt;&lt;body&gt;&lt;p&gt;Properly quoted PostgreSQL expression (e.g. &lt;code&gt;4&lt;/code&gt;, &lt;code&gt;'text'&lt;/code&gt; or &lt;code&gt;nextval('foo_id_seq')&lt;/code&gt;&lt;br/&gt;&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;</string>
102+
</property>
103+
<property name="text">
104+
<string notr="true"/>
105+
</property>
106+
</widget>
107+
</item>
108+
<item row="6" column="0">
109+
<widget class="QLabel" name="label_6">
110+
<property name="text">
111+
<string>Comment</string>
112+
</property>
113+
</widget>
114+
</item>
98115
</layout>
99116
</item>
100117
<item>

‎python/plugins/db_manager/ui/DlgImportVector.ui

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -432,16 +432,17 @@
432432
<connection>
433433
<sender>chkCom</sender>
434434
<signal>toggled(bool)</signal>
435-
<receiver>editComment</receiver>
435+
<receiver>editCom</receiver>
436436
<slot>setEnabled(bool)</slot>
437437
<hints>
438438
<hint type="sourcelabel">
439-
<x>64</x>
440-
<y>549</y>
439+
<x>105</x>
440+
<y>557</y>
441441
</hint>
442442
<hint type="destinationlabel">
443-
<x>215</x>
444-
<y>552</y>
443+
<x>211</x>
444+
<y>553</y>
445+
445446
</hint>
446447
</hints>
447448
</connection>

‎python/plugins/db_manager/ui/DlgTableProperties.ui

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6,8 +6,8 @@
66
<rect>
77
<x>0</x>
88
<y>0</y>
9-
<width>547</width>
10-
<height>435</height>
9+
<width>885</width>
10+
<height>470</height>
1111
</rect>
1212
</property>
1313
<property name="windowTitle">

0 commit comments

Comments
 (0)
Please sign in to comment.