Fix bug comment on postgres and others management

qgis · Feb 6, 2019 · dff1853 · dff1853
1 parent 7f0ab8b
commit dff1853
Show file tree

Hide file tree

Showing 15 changed files with 102 additions and 108 deletions.
diff --git a/python/plugins/db_manager/db_plugins/connector.py b/python/plugins/db_manager/db_plugins/connector.py
@@ -233,3 +233,8 @@ def getSqlDictionary(self):
 
     def getQueryBuilderDictionary(self):
         return {}
+
+    def setField(self, fld, tablename=None, db=None):
+        if fld is None:
+            return
+        return fld.name, fld.dataType, str(fld.modifier), fld.notNull, fld.default, None
diff --git a/python/plugins/db_manager/db_plugins/gpkg/connector.py b/python/plugins/db_manager/db_plugins/gpkg/connector.py
@@ -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, new_comment=None):
+    def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
         if self.isGeometryColumn(table, column):
             return False
 

diff --git a/python/plugins/db_manager/db_plugins/gpkg/plugin.py b/python/plugins/db_manager/db_plugins/gpkg/plugin.py
@@ -177,6 +177,9 @@ def toSqlLayer(self, sql, geomCol, uniqueCol, layerName="QueryLayer", layerType=
         vl.setSubsetString(sql)
         return vl
 
+    def searchClass(self):
+        return "GPKGDatabase"
+
 
 class GPKGTable(Table):
 
@@ -301,10 +304,6 @@ def __init__(self, row, table):
         self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
         self.hasDefault = self.default
 
-    def getComment(self):
-        """Returns the comment for a field"""
-        return ''
-
 
 class GPKGTableIndex(TableIndex):
 

diff --git a/python/plugins/db_manager/db_plugins/oracle/connector.py b/python/plugins/db_manager/db_plugins/oracle/connector.py
@@ -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:

diff --git a/python/plugins/db_manager/db_plugins/oracle/plugin.py b/python/plugins/db_manager/db_plugins/oracle/plugin.py
@@ -91,8 +91,7 @@ def connect(self, parent=None):
         uri = QgsDataSourceUri()
 
         settingsList = ["host", "port", "database", "username", "password"]
-        host, port, database, username, password = [
-            settings.value(x, "", type=str) for x in settingsList]
+        host, port, database, username, password = [settings.value(x, "", type=str) for x in settingsList]
 
         # get all of the connexion options
 
@@ -203,8 +202,7 @@ def toSqlLayer(self, sql, geomCol, uniqueCol,
         uri = self.uri()
         con = self.database().connector
 
-        uri.setDataSource(u"", u"({}\n)".format(
-            sql), geomCol, filter, uniqueCol.strip(u'"'))
+        uri.setDataSource(u"", u"({}\n)".format(sql), geomCol, filter, uniqueCol.strip(u'"'))
         if avoidSelectById:
             uri.disableSelectAtId(True)
         provider = self.dbplugin().providerName()
@@ -263,6 +261,9 @@ def registerDatabaseActions(self, mainWindow):
         mainWindow.registerAction(action, QApplication.translate(
             "DBManagerPlugin", "&Table"), self.emptyTableActionSlot)
 
+    def searchClass(self):
+        return "ORDatabase"
+
 
 class ORSchema(Schema):
 
@@ -524,7 +525,7 @@ def __init__(self, row, table):
     def type2String(self):
         if (u"TIMESTAMP" in self.dataType or
             self.dataType in [u"DATE", u"SDO_GEOMETRY",
-                                 u"BINARY_FLOAT", u"BINARY_DOUBLE"]):
+                              u"BINARY_FLOAT", u"BINARY_DOUBLE"]):
             return u"{}".format(self.dataType)
         if self.charMaxLen in [None, -1]:
             return u"{}".format(self.dataType)
@@ -559,10 +560,6 @@ def update(self, new_name, new_type_str=None, new_not_null=None,
             self.table().refreshIndexes()
         return ret
 
-    def getComment(self):
-        """Returns the comment for a field"""
-        return ''
-
 
 class ORTableConstraint(TableConstraint):
 

diff --git a/python/plugins/db_manager/db_plugins/plugin.py b/python/plugins/db_manager/db_plugins/plugin.py
@@ -1096,22 +1096,7 @@ def definition(self):
         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 ''
+        return ''
 
     def delete(self):
         return self.table().deleteField(self)

diff --git a/python/plugins/db_manager/db_plugins/postgis/connector.py b/python/plugins/db_manager/db_plugins/postgis/connector.py
@@ -511,6 +511,25 @@ def getTableFields(self, table):
         self._close_cursor(c)
         return res
 
+    def setField(self, fld, tablename, db):
+        if fld is None:
+            return
+        print (tablename)
+        # 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" % (tablename, fld.name)
+        # 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" % (tablename, fld.name)
+        c = db.connector._execute(None, sql_cpt) # Execute check query
+        res = 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 = db.connector._execute(None, sql) # Execute query returning the comment value
+            res2 = db.connector._fetchone(c)[0] # Fetch the comment value
+            db.connector._close_cursor(c) # Close cursor
+        else :
+            res2 = None
+        return fld.name, fld.dataType, str(fld.modifier), fld.notNull, fld.default, res2
+
     def getTableIndexes(self, table):
         """ get info about table's indexes. ignore primary key constraint index, they get listed in constraints """
         schema, tablename = self.getSchemaTableName(table)
@@ -857,7 +876,7 @@ 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, comment=None):
+    def updateTableColumn(self, table, column, new_name=None, data_type=None, not_null=None, default=None, comment=None, test=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
 

diff --git a/python/plugins/db_manager/db_plugins/postgis/plugin.py b/python/plugins/db_manager/db_plugins/postgis/plugin.py
@@ -181,6 +181,9 @@ def runRefreshMaterializedViewSlot(self, item, action, parent):
     def hasLowercaseFieldNamesOption(self):
         return True
 
+    def searchClass(self):
+        return "PGDatabase"
+
 
 class PGSchema(Schema):
 
@@ -396,6 +399,24 @@ def __init__(self, row, table):
             if con.type == TableConstraint.TypePrimaryKey and self.num in con.columns:
                 self.primaryKey = True
                 break
+
+    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 ''
 
 
 class PGTableConstraint(TableConstraint):

diff --git a/python/plugins/db_manager/db_plugins/spatialite/connector.py b/python/plugins/db_manager/db_plugins/spatialite/connector.py
@@ -465,8 +465,6 @@ def deleteTable(self, table):
         self._execute(c, sql)
         self._commit()
 
-        return True
-
     def emptyTable(self, table):
         """ delete all rows from table """
         if self.isRasterTable(table):
@@ -496,7 +494,6 @@ def renameTable(self, table, new_table):
             self._execute(c, sql)
 
         self._commit()
-        return True
 
     def moveTable(self, table, new_table, new_schema=None):
         return self.renameTable(table, new_table)
@@ -574,16 +571,7 @@ def runVacuum(self):
     def addTableColumn(self, table, field_def):
         """ add a column to table """
         sql = u"ALTER TABLE %s ADD %s" % (self.quoteId(table), field_def)
-        self._execute(None, sql)
-
-        sql = u"SELECT InvalidateLayerStatistics(%s)" % (self.quoteId(table))
-        self._execute(None, sql)
-
-        sql = u"SELECT UpdateLayerStatistics(%s)" % (self.quoteId(table))
-        self._execute(None, sql)
-
-        self._commit()
-        return True
+        self._execute_and_commit(sql)
 
     def deleteTableColumn(self, table, column):
         """ delete column from a table """
@@ -595,7 +583,7 @@ def deleteTableColumn(self, table, column):
         sql = u"SELECT DiscardGeometryColumn(%s, %s)" % (self.quoteString(tablename), self.quoteString(column))
         self._execute_and_commit(sql)
 
-    def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, new_comment=None):
+    def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
         return False  # column editing not supported
 
     def renameTableColumn(self, table, column, new_name):

diff --git a/python/plugins/db_manager/db_plugins/spatialite/plugin.py b/python/plugins/db_manager/db_plugins/spatialite/plugin.py
@@ -175,6 +175,9 @@ def explicitSpatialIndex(self):
     def spatialIndexClause(self, src_table, src_column, dest_table, dest_column):
         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)
 
+    def searchClass(self):
+        return "SLDatabase"
+
 
 class SLTable(Table):
 
@@ -294,10 +297,6 @@ def __init__(self, row, table):
         self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
         self.hasDefault = self.default
 
-    def getComment(self):
-        """Returns the comment for a field"""
-        return ''
-
 
 class SLTableIndex(TableIndex):
 

diff --git a/python/plugins/db_manager/db_plugins/vlayers/connector.py b/python/plugins/db_manager/db_plugins/vlayers/connector.py
@@ -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, new_comment=None):
+    def updateTableColumn(self, table, column, new_name, new_data_type=None, new_not_null=None, new_default=None, comment=None):
         print("**unimplemented** updateTableColumn")
 
     def renameTableColumn(self, table, column, new_name):

diff --git a/python/plugins/db_manager/db_plugins/vlayers/plugin.py b/python/plugins/db_manager/db_plugins/vlayers/plugin.py
@@ -132,6 +132,9 @@ def explicitSpatialIndex(self):
     def spatialIndexClause(self, src_table, src_column, dest_table, dest_column):
         return '"%s"._search_frame_ = "%s"."%s"' % (src_table, dest_table, dest_column)
 
+    def searchClass(self):
+        return "FakeDatabase"
+
 
 class LTable(Table):
 
@@ -192,7 +195,3 @@ def __init__(self, row, table):
         TableField.__init__(self, table)
         self.num, self.name, self.dataType, self.notNull, self.default, self.primaryKey = row
         self.hasDefault = self.default
-
-    def getComment(self):
-        """Returns the comment for a field"""
-        return ''
diff --git a/python/plugins/db_manager/dlg_field_properties.py b/python/plugins/db_manager/dlg_field_properties.py
@@ -27,7 +27,6 @@
 from qgis.PyQt.QtWidgets import QDialog, QMessageBox
 
 from .db_plugins.plugin import TableField
-
 from .ui.ui_DlgFieldProperties import Ui_DbManagerDlgFieldProperties as Ui_Dialog
 
 
@@ -42,37 +41,22 @@ def __init__(self, parent=None, fld=None, table=None, db=None):
 
         for item in self.db.connector.fieldTypes():
             self.cboType.addItem(item)
-        self.setField(self.fld)
 
-        self.buttonBox.accepted.connect(self.onOK)
 
-    def setField(self, fld):
-        if fld is None:
-            return
-        self.editName.setText(fld.name)
-        self.cboType.setEditText(fld.dataType)
-        if fld.modifier:
-            self.editLength.setText(str(fld.modifier))
-        self.chkNull.setChecked(not fld.notNull)
-        if fld.hasDefault:
-            self.editDefault.setText(fld.default)
-        # This is an ugly patch, but the comments PR https://github.com/qgis/QGIS/pull/8831 added
-        # support for postgres only and broke all the others :(
-        try:
-            # 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
-        except:
-            self.editCom.setEnabled(False)
+        objClass = self.db.searchClass()
+        if objClass != "PGDatabase":
+            self.label_6.setVisible(False)
+            self.editCom.setVisible(False)
+
+        name, dataType, modifier, chkNull, hasDefault, chkCom = self.db.connector.setField(self.fld, self.table.name, self.db)
+        self.editName.setText(name)
+        self.cboType.setEditText(dataType)
+        self.editLength.setText(modifier)
+        self.chkNull.setChecked(not chkNull)
+        self.editDefault.setText(hasDefault)
+        self.editCom.setText(chkCom)
+
+        self.buttonBox.accepted.connect(self.onOK)
 
     def getField(self, newCopy=False):
         fld = TableField(self.table) if not self.fld or newCopy else self.fld

diff --git a/python/plugins/db_manager/dlg_import_vector.py b/python/plugins/db_manager/dlg_import_vector.py
@@ -25,7 +25,8 @@
 from builtins import range
 
 from qgis.PyQt.QtCore import Qt, QFileInfo
-from qgis.PyQt.QtWidgets import QDialog, QFileDialog, QMessageBox
+from qgis.PyQt.QtWidgets import QDialog, QFileDialog, QMessageBox, QApplication
+from qgis.PyQt.QtGui import QCursor
 
 from qgis.core import (QgsDataSourceUri,
                        QgsVectorLayer,
@@ -51,6 +52,11 @@ def __init__(self, inLayer, outDb, outUri, parent=None):
         self.outUri = outUri
         self.setupUi(self)
 
+        objClass = self.db.searchClass()
+        if objClass != "PGDatabase":
+            self.chkCom.setVisible(False)
+            self.editCom.setVisible(False)
+
         self.default_pk = "id"
         self.default_geom = "geom"
 
@@ -368,9 +374,9 @@ def accept(self):
             self.db.connector.createSpatialIndex((schema, table), geom)
 
         # add comment on table
-        if self.chkCom.isEnabled() and self.chkCom.isChecked():
+        if self.chkCom.isEnabled() and self.chkCom.isChecked() and objClass == "PGDatabase":
             # using connector executing COMMENT ON TABLE query (with editCome.text() value)
-            self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}E\''.format(schema, table, self.editCom.text()))
+            self.db.connector._execute(None, 'COMMENT ON TABLE "{0}"."{1}" IS E\'{2}\''.format(schema, table, self.editCom.text()))
 
         self.db.connection().reconnect()
         self.db.refresh()