Skip to content

Commit 6b98d8d

Browse files
author
corentin.falcone
committedJan 10, 2019
Add comment on field | DbManager
·
ltr-3_40final-3_6_0
1 parent 805646d commit 6b98d8d

File tree

7 files changed

+99
-74
lines changed

7 files changed

+99
-74
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/plugin.py

Lines changed: 25 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1101,7 +1101,27 @@ def delete(self):
11011101
def rename(self, new_name):
11021102
return self.update(new_name)
11031103

1104-
def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None):
1104+
def getComment(self):
1105+
# Function returning the comment of a field if exists
1106+
tab = self.table()
1107+
#SQL Query checking if a comment exists for the field
1108+
sql_cpt = u"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)
1109+
#SQL Query that return the comment of the field
1110+
sql = u"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)
1111+
c = tab.database().connector._execute(None, sql_cpt)# Execute Check query
1112+
res = tab.database().connector._fetchone(c)[0] #Store result
1113+
if res == 1:
1114+
# When a comment exists
1115+
c = tab.database().connector._execute(None, sql) #Execute query
1116+
res = tab.database().connector._fetchone(c)[0] #Store result
1117+
tab.database().connector._close_cursor(c) #Close cursor
1118+
return res #Return comment
1119+
else:
1120+
return '' #Return void string
1121+
1122+
1123+
1124+
def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None, new_comment=None):
11051125
self.table().aboutToChange.emit()
11061126
if self.name == new_name:
11071127
new_name = None
@@ -1111,10 +1131,12 @@ def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str
11111131
new_not_null = None
11121132
if self.default2String() == new_default_str:
11131133
new_default_str = None
1114-
1134+
if self.comment == new_comment:
1135+
# Update also a new_comment
1136+
new_comment = None
11151137
ret = self.table().database().connector.updateTableColumn((self.table().schemaName(), self.table().name),
11161138
self.name, new_name, new_type_str, new_not_null,
1117-
new_default_str)
1139+
new_default_str, new_comment)
11181140
if ret is not False:
11191141
self.table().refreshFields()
11201142
return ret

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

Lines changed: 9 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,13 @@ 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+
# Add the comment on the field
901+
schema, tablename = self.getSchemaTableName(table)
902+
sql = u"COMMENT ON COLUMN %s.%s.%s is '%s'" % (schema, tablename, column, comment)
903+
self._execute(c, sql)
904+
898905
self._commit()
899906

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

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -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/dlg_field_properties.py

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
__date__ = 'April 2012'
2323
__copyright__ = '(C) 2012, Giuseppe Sucameli'
2424
# This will get replaced with a git SHA1 when you do a git archive
25-
__revision__ = '$Format:%H$'
25+
__revision__ = '2f64a3c4e74022c5555ee19862f034ffacc2b5fe'
2626

2727
from qgis.PyQt.QtWidgets import QDialog, QMessageBox
2828

@@ -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 = u"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 = u"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 & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -59,10 +59,6 @@ def __init__(self, table, parent=None):
5959
m = TableIndexesModel(self)
6060
self.viewIndexes.setModel(m)
6161

62-
#Display comment in line edit
63-
m = self.table.comment
64-
self.viewComment.setText(m)
65-
6662
self.btnAddColumn.clicked.connect(self.addColumn)
6763
self.btnAddGeometryColumn.clicked.connect(self.addGeometryColumn)
6864
self.btnEditColumn.clicked.connect(self.editColumn)
@@ -75,11 +71,6 @@ def __init__(self, table, parent=None):
7571
self.btnAddSpatialIndex.clicked.connect(self.createSpatialIndex)
7672
self.btnDeleteIndex.clicked.connect(self.deleteIndex)
7773

78-
#Connect button add Comment to function
79-
self.btnAddComment.clicked.connect(self.createComment)
80-
#Connect button delete Comment to function
81-
self.btnDeleteComment.clicked.connect(self.deleteComment)
82-
8374
self.refresh()
8475

8576
def refresh(self):
@@ -162,7 +153,7 @@ def editColumn(self):
162153
with OverrideCursor(Qt.WaitCursor):
163154
self.aboutToChangeTable.emit()
164155
try:
165-
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String())
156+
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String(), new_fld.comment)
166157
self.refresh()
167158
except BaseError as e:
168159
DlgDbError.showError(e, self)
@@ -331,29 +322,3 @@ def deleteIndex(self):
331322
self.refresh()
332323
except BaseError as e:
333324
DlgDbError.showError(e, self)
334-
335-
def createComment(self):
336-
#Function that add a comment to the selected table
337-
try:
338-
#Using the db connector, executing de SQL query Comment on table
339-
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}\';'.format(self.table.schema().name, self.table.name, self.viewComment.text()))
340-
except DbError as e:
341-
DlgDbError.showError(e, self)
342-
return
343-
self.refresh()
344-
#Display successful message
345-
QMessageBox.information(self, self.tr("Add comment"), self.tr("Table successfully commented"))
346-
347-
def deleteComment(self):
348-
#Function that drop the comment to the selected table
349-
try:
350-
#Using the db connector, executing de SQL query Comment on table using the NULL definition
351-
self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS NULL;'.format(self.table.schema().name, self.table.name))
352-
except DbError as e:
353-
DlgDbError.showError(e, self)
354-
return
355-
self.refresh()
356-
#Refresh line edit, put a void comment
357-
self.viewComment.setText('')
358-
#Display successful message
359-
QMessageBox.information(self, self.tr("Delete comment"), self.tr("Comment deleted"))

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

100644100755
Lines changed: 44 additions & 27 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>354</width>
10-
<height>247</height>
9+
<width>569</width>
10+
<height>308</height>
1111
</rect>
1212
</property>
1313
<property name="windowTitle">
@@ -23,20 +23,13 @@
2323
</property>
2424
</widget>
2525
</item>
26-
<item row="0" column="1">
27-
<widget class="QLineEdit" name="editName">
26+
<item row="2" column="1">
27+
<widget class="QLineEdit" name="editLength">
2828
<property name="text">
2929
<string notr="true"/>
3030
</property>
3131
</widget>
3232
</item>
33-
<item row="1" column="0">
34-
<widget class="QLabel" name="label_2">
35-
<property name="text">
36-
<string>Type</string>
37-
</property>
38-
</widget>
39-
</item>
4033
<item row="1" column="1">
4134
<widget class="QComboBox" name="cboType">
4235
<property name="editable">
@@ -47,10 +40,10 @@
4740
</property>
4841
</widget>
4942
</item>
50-
<item row="3" column="0">
51-
<widget class="QLabel" name="label_3">
43+
<item row="4" column="0">
44+
<widget class="QLabel" name="label_4">
5245
<property name="text">
53-
<string>Can be NULL</string>
46+
<string>Default value expression</string>
5447
</property>
5548
</widget>
5649
</item>
@@ -64,10 +57,38 @@
6457
</property>
6558
</widget>
6659
</item>
67-
<item row="4" column="0">
68-
<widget class="QLabel" name="label_4">
60+
<item row="0" column="1">
61+
<widget class="QLineEdit" name="editName">
6962
<property name="text">
70-
<string>Default value expression</string>
63+
<string notr="true"/>
64+
</property>
65+
</widget>
66+
</item>
67+
<item row="2" column="0">
68+
<widget class="QLabel" name="label_5">
69+
<property name="text">
70+
<string>Length</string>
71+
</property>
72+
</widget>
73+
</item>
74+
<item row="1" column="0">
75+
<widget class="QLabel" name="label_2">
76+
<property name="text">
77+
<string>Type</string>
78+
</property>
79+
</widget>
80+
</item>
81+
<item row="3" column="0">
82+
<widget class="QLabel" name="label_3">
83+
<property name="text">
84+
<string>Can be NULL</string>
85+
</property>
86+
</widget>
87+
</item>
88+
<item row="5" column="0">
89+
<widget class="QLabel" name="label_6">
90+
<property name="text">
91+
<string>Comment</string>
7192
</property>
7293
</widget>
7394
</item>
@@ -81,15 +102,11 @@
81102
</property>
82103
</widget>
83104
</item>
84-
<item row="2" column="0">
85-
<widget class="QLabel" name="label_5">
86-
<property name="text">
87-
<string>Length</string>
105+
<item row="5" column="1">
106+
<widget class="QLineEdit" name="editCom">
107+
<property name="toolTip">
108+
<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>
88109
</property>
89-
</widget>
90-
</item>
91-
<item row="2" column="1">
92-
<widget class="QLineEdit" name="editLength">
93110
<property name="text">
94111
<string notr="true"/>
95112
</property>
@@ -126,8 +143,8 @@
126143
<slot>reject()</slot>
127144
<hints>
128145
<hint type="sourcelabel">
129-
<x>316</x>
130-
<y>260</y>
146+
<x>325</x>
147+
<y>456</y>
131148
</hint>
132149
<hint type="destinationlabel">
133150
<x>286</x>

0 commit comments

Comments
 (0)
Please sign in to comment.