Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
Merge pull request #8831 from SIRS-CLS/sirs_db_manager
Add comment option on db_manager plugin postgis fields
  • Loading branch information
m-kuhn committed Jan 23, 2019
2 parents 263c4c2 + aa3679a commit daa05e5
Show file tree
Hide file tree
Showing 13 changed files with 112 additions and 56 deletions.
6 changes: 3 additions & 3 deletions python/plugins/db_manager/db_plugins/data_model.py
Expand Up @@ -259,7 +259,7 @@ def getObjectIter(self):
class TableFieldsModel(SimpleTableModel):

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

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

def append(self, fld):
data = [fld.name, fld.type2String(), not fld.notNull, fld.default2String()]
data = [fld.name, fld.type2String(), not fld.notNull, fld.default2String(), fld.getComment()]
self.appendRow(self.rowFromData(data))
row = self.rowCount() - 1
self.setData(self.index(row, 0), fld, Qt.UserRole)
Expand All @@ -290,7 +290,6 @@ def getObject(self, row):
val = self.data(self.index(row, 0), Qt.UserRole)
fld = val if val is not None else self._getNewObject()
fld.name = self.data(self.index(row, 0)) or ""

typestr = self.data(self.index(row, 1)) or ""
regex = QRegExp("([^\\(]+)\\(([^\\)]+)\\)")
startpos = regex.indexIn(typestr)
Expand All @@ -303,6 +302,7 @@ def getObject(self, row):

fld.notNull = self.data(self.index(row, 2), Qt.CheckStateRole) == Qt.Unchecked
fld.primaryKey = self.data(self.index(row, 1), Qt.UserRole)
fld.comment = self.data(self.index(row, 4), Qt.UserRole)
return fld

def getFields(self):
Expand Down
2 changes: 1 addition & 1 deletion python/plugins/db_manager/db_plugins/gpkg/connector.py
Expand Up @@ -644,7 +644,7 @@ def deleteTableColumn(self, table, column):
return lyr.DeleteField(idx) == 0
return False

def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None):
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
if self.isGeometryColumn(table, column):
return False

Expand Down
14 changes: 7 additions & 7 deletions python/plugins/db_manager/db_plugins/oracle/connector.py
Expand Up @@ -475,16 +475,16 @@ def updateCache(self, tableList, schema=None):

def singleGeomTypes(self, geomtypes, srids):
"""Intelligent wkbtype grouping (multi with non multi)"""
if (QgsWkbTypes.Polygon in geomtypes and
QgsWkbTypes.MultiPolygon in geomtypes):
if (QgsWkbTypes.Polygon in geomtypes
and QgsWkbTypes.MultiPolygon in geomtypes):
srids.pop(geomtypes.index(QgsWkbTypes.Polygon))
geomtypes.pop(geomtypes.index(QgsWkbTypes.Polygon))
if (QgsWkbTypes.Point in geomtypes and
QgsWkbTypes.MultiPoint in geomtypes):
if (QgsWkbTypes.Point in geomtypes
and QgsWkbTypes.MultiPoint in geomtypes):
srids.pop(geomtypes.index(QgsWkbTypes.Point))
geomtypes.pop(geomtypes.index(QgsWkbTypes.Point))
if (QgsWkbTypes.LineString in geomtypes and
QgsWkbTypes.MultiLineString in geomtypes):
if (QgsWkbTypes.LineString in geomtypes
and QgsWkbTypes.MultiLineString in geomtypes):
srids.pop(geomtypes.index(QgsWkbTypes.LineString))
geomtypes.pop(geomtypes.index(QgsWkbTypes.LineString))
if QgsWkbTypes.Unknown in geomtypes and len(geomtypes) > 1:
Expand Down Expand Up @@ -1336,7 +1336,7 @@ def deleteTableColumn(self, table, column):

def updateTableColumn(self, table, column, new_name=None,
data_type=None, not_null=None,
default=None):
default=None, comment=None):
"""Update properties of a column in a table."""

schema, tablename = self.getSchemaTableName(table)
Expand Down
28 changes: 24 additions & 4 deletions python/plugins/db_manager/db_plugins/plugin.py
Expand Up @@ -1095,13 +1095,31 @@ def definition(self):
txt += u" DEFAULT %s" % self.default2String()
return txt

def getComment(self):
"""Returns the comment for a field"""
tab = self.table()
# SQL Query checking if a comment exists for the field
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)
# SQL Query that return the comment of the field
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)
c = tab.database().connector._execute(None, sql_cpt) # Execute Check query
res = tab.database().connector._fetchone(c)[0] # Store result
if res == 1:
# When a comment exists
c = tab.database().connector._execute(None, sql) # Execute query
res = tab.database().connector._fetchone(c)[0] # Store result
tab.database().connector._close_cursor(c) # Close cursor
return res # Return comment
else:
return ''

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

def rename(self, new_name):
return self.update(new_name)

def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None):
def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str=None, new_comment=None):
self.table().aboutToChange.emit()
if self.name == new_name:
new_name = None
Expand All @@ -1111,10 +1129,12 @@ def update(self, new_name, new_type_str=None, new_not_null=None, new_default_str
new_not_null = None
if self.default2String() == new_default_str:
new_default_str = None

if self.comment == new_comment:
# Update also a new_comment
new_comment = None
ret = self.table().database().connector.updateTableColumn((self.table().schemaName(), self.table().name),
self.name, new_name, new_type_str, new_not_null,
new_default_str)
self.name, new_name, new_type_str,
new_not_null, new_default_str, new_comment)
if ret is not False:
self.table().refreshFields()
return ret
Expand Down
10 changes: 8 additions & 2 deletions python/plugins/db_manager/db_plugins/postgis/connector.py
Expand Up @@ -857,8 +857,8 @@ def deleteTableColumn(self, table, column):
sql = u"ALTER TABLE %s DROP %s" % (self.quoteId(table), self.quoteId(column))
self._execute_and_commit(sql)

def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None):
if new_name is None and data_type is None and not_null is None and default is None:
def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None, comment=None):
if new_name is None and data_type is None and not_null is None and default is None and comment is None:
return

c = self._get_cursor()
Expand Down Expand Up @@ -895,6 +895,12 @@ def updateTableColumn(self, table, column, new_name=None, data_type=None, not_nu
self.quoteString(new_name), schema_where, self.quoteString(tablename), self.quoteString(column))
self._execute(c, sql)

# comment the column
if comment is not None:
schema, tablename = self.getSchemaTableName(table)
sql = u"COMMENT ON COLUMN %s.%s.%s is '%s'" % (schema, tablename, column, comment)
self._execute(c, sql)

self._commit()

def renameTableColumn(self, table, column, new_name):
Expand Down
8 changes: 4 additions & 4 deletions python/plugins/db_manager/db_plugins/postgis/info_model.py
Expand Up @@ -110,8 +110,8 @@ def generalInfo(self):
if not self.table.isView:
if self.table.rowCount is not None:
if abs(self.table.estimatedRowCount - self.table.rowCount) > 1 and \
(self.table.estimatedRowCount > 2 * self.table.rowCount or
self.table.rowCount > 2 * self.table.estimatedRowCount):
(self.table.estimatedRowCount > 2 * self.table.rowCount
or self.table.rowCount > 2 * self.table.estimatedRowCount):
ret.append(HtmlParagraph(QApplication.translate("DBManagerPlugin",
"<warning> There's a significant difference between estimated and real row count. "
'Consider running <a href="action:vacuumanalyze/run">VACUUM ANALYZE</a>.')))
Expand Down Expand Up @@ -162,7 +162,7 @@ def fieldsDetails(self):
header = (
"#", QApplication.translate("DBManagerPlugin", "Name"), QApplication.translate("DBManagerPlugin", "Type"),
QApplication.translate("DBManagerPlugin", "Length"), QApplication.translate("DBManagerPlugin", "Null"),
QApplication.translate("DBManagerPlugin", "Default"))
QApplication.translate("DBManagerPlugin", "Default"), QApplication.translate("DBManagerPlugin", "Comment"))
tbl.append(HtmlTableHeader(header))

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

tbl.append((fld.num, name, fld.type2String(), char_max_len, is_null_txt, fld.default2String()))
tbl.append((fld.num, name, fld.type2String(), char_max_len, is_null_txt, fld.default2String(), fld.getComment()))

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

Expand Down
4 changes: 1 addition & 3 deletions python/plugins/db_manager/db_plugins/spatialite/connector.py
Expand Up @@ -595,9 +595,7 @@ def deleteTableColumn(self, table, column):
sql = u"SELECT DiscardGeometryColumn(%s, %s)" % (self.quoteString(tablename), self.quoteString(column))
self._execute_and_commit(sql)

return True

def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None):
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
return False # column editing not supported

def renameTableColumn(self, table, column, new_name):
Expand Down
2 changes: 1 addition & 1 deletion python/plugins/db_manager/db_plugins/vlayers/connector.py
Expand Up @@ -349,7 +349,7 @@ def addTableColumn(self, table, field_def):
def deleteTableColumn(self, table, column):
print("**unimplemented** deleteTableColumn")

def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None):
def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
print("**unimplemented** updateTableColumn")

def renameTableColumn(self, table, column, new_name):
Expand Down
14 changes: 14 additions & 0 deletions python/plugins/db_manager/dlg_field_properties.py
Expand Up @@ -56,6 +56,18 @@ def setField(self, fld):
self.chkNull.setChecked(not fld.notNull)
if fld.hasDefault:
self.editDefault.setText(fld.default)
# Check with SQL query if a comment exists for the field
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())
# Get the comment for the field with SQL Query
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())
c = self.db.connector._execute(None, sql_cpt) # Execute check query
res = self.db.connector._fetchone(c)[0] # Fetch data
# Check if result is 1 then it's ok, else we don't want to get a value
if res == 1:
c = self.db.connector._execute(None, sql) # Execute query returning the comment value
res = self.db.connector._fetchone(c)[0] # Fetch the comment value
self.db.connector._close_cursor(c) # Close cursor
self.editCom.setText(res) # Set comment value

def getField(self, newCopy=False):
fld = TableField(self.table) if not self.fld or newCopy else self.fld
Expand All @@ -64,6 +76,8 @@ def getField(self, newCopy=False):
fld.notNull = not self.chkNull.isChecked()
fld.default = self.editDefault.text()
fld.hasDefault = fld.default != ""
# Get the comment from the LineEdit
fld.comment = self.editCom.text()
try:
modifier = int(self.editLength.text())
except ValueError:
Expand Down
2 changes: 1 addition & 1 deletion python/plugins/db_manager/dlg_table_properties.py
Expand Up @@ -162,7 +162,7 @@ def editColumn(self):
with OverrideCursor(Qt.WaitCursor):
self.aboutToChangeTable.emit()
try:
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String())
fld.update(new_fld.name, new_fld.type2String(), new_fld.notNull, new_fld.default2String(), new_fld.comment)
self.refresh()
except BaseError as e:
DlgDbError.showError(e, self)
Expand Down
63 changes: 40 additions & 23 deletions python/plugins/db_manager/ui/DlgFieldProperties.ui 100644 → 100755
Expand Up @@ -16,24 +16,34 @@
<layout class="QVBoxLayout">
<item>
<layout class="QGridLayout">
<item row="0" column="0">
<widget class="QLabel" name="label">
<item row="3" column="0">
<widget class="QLabel" name="label_3">
<property name="text">
<string>Name</string>
<string>Can be NULL</string>
</property>
</widget>
</item>
<item row="0" column="1">
<widget class="QLineEdit" name="editName">
<item row="3" column="1">
<widget class="QCheckBox" name="chkNull">
<property name="text">
<string notr="true"/>
</property>
<property name="checked">
<bool>true</bool>
</property>
</widget>
</item>
<item row="1" column="0">
<widget class="QLabel" name="label_2">
<item row="4" column="0">
<widget class="QLabel" name="label_4">
<property name="text">
<string>Type</string>
<string>Default value expression</string>
</property>
</widget>
</item>
<item row="0" column="0">
<widget class="QLabel" name="label">
<property name="text">
<string>Name</string>
</property>
</widget>
</item>
Expand All @@ -47,28 +57,18 @@
</property>
</widget>
</item>
<item row="3" column="0">
<widget class="QLabel" name="label_3">
<item row="1" column="0">
<widget class="QLabel" name="label_2">
<property name="text">
<string>Can be NULL</string>
<string>Type</string>
</property>
</widget>
</item>
<item row="3" column="1">
<widget class="QCheckBox" name="chkNull">
<item row="0" column="1">
<widget class="QLineEdit" name="editName">
<property name="text">
<string notr="true"/>
</property>
<property name="checked">
<bool>true</bool>
</property>
</widget>
</item>
<item row="4" column="0">
<widget class="QLabel" name="label_4">
<property name="text">
<string>Default value expression</string>
</property>
</widget>
</item>
<item row="4" column="1">
Expand All @@ -95,6 +95,23 @@
</property>
</widget>
</item>
<item row="6" column="1">
<widget class="QLineEdit" name="editCom">
<property name="toolTip">
<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>
</property>
<property name="text">
<string notr="true"/>
</property>
</widget>
</item>
<item row="6" column="0">
<widget class="QLabel" name="label_6">
<property name="text">
<string>Comment</string>
</property>
</widget>
</item>
</layout>
</item>
<item>
Expand Down
11 changes: 6 additions & 5 deletions python/plugins/db_manager/ui/DlgImportVector.ui
Expand Up @@ -432,16 +432,17 @@
<connection>
<sender>chkCom</sender>
<signal>toggled(bool)</signal>
<receiver>editComment</receiver>
<receiver>editCom</receiver>
<slot>setEnabled(bool)</slot>
<hints>
<hint type="sourcelabel">
<x>64</x>
<y>549</y>
<x>105</x>
<y>557</y>
</hint>
<hint type="destinationlabel">
<x>215</x>
<y>552</y>
<x>211</x>
<y>553</y>

</hint>
</hints>
</connection>
Expand Down
4 changes: 2 additions & 2 deletions python/plugins/db_manager/ui/DlgTableProperties.ui
Expand Up @@ -6,8 +6,8 @@
<rect>
<x>0</x>
<y>0</y>
<width>547</width>
<height>435</height>
<width>885</width>
<height>470</height>
</rect>
</property>
<property name="windowTitle">
Expand Down

0 comments on commit daa05e5

Please sign in to comment.