Merge pull request #10063 from elpaso/bugfix-22035-processing-pg-came…

…l-case-schema Bugfix 22035 processing pg camel case schema
qgis · May 23, 2019 · d16e693 · d16e693
2 parents f9810a4 + bb567cd
commit d16e693
Show file tree

Hide file tree

Showing 5 changed files with 138 additions and 11 deletions.
diff --git a/python/plugins/processing/algs/qgis/ImportIntoPostGIS.py b/python/plugins/processing/algs/qgis/ImportIntoPostGIS.py
@@ -145,7 +145,7 @@ def processAlgorithm(self, parameters, context, feedback):
         if not table or table == '':
             table = source.sourceName()
             table = table.replace('.', '_')
-        table = table.replace(' ', '').lower()[0:62]
+        table = table.replace(' ', '')[0:62]
         providerName = 'postgres'
 
         geomColumn = self.parameterAsString(parameters, self.GEOMETRY_COLUMN, context)

diff --git a/python/plugins/processing/tools/postgis.py b/python/plugins/processing/tools/postgis.py
@@ -38,6 +38,16 @@
 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
 
 
+class DbError(Exception):
+
+    def __init__(self, message, query=None):
+        self.message = str(message)
+        self.query = (str(query) if query is not None else None)
+
+    def __str__(self):
+        return 'MESSAGE: %s\nQUERY: %s' % (self.message, self.query)
+
+
 def uri_from_name(conn_name):
     settings = QgsSettings()
     settings.beginGroup(u"/PostgreSQL/connections/%s" % conn_name)
@@ -146,8 +156,8 @@ def field_def(self):
         ALTER TABLE command.
         """
 
-        data_type = (self.data_type if not self.modifier or self.modifier <
-                     0 else '%s(%d)' % (self.data_type, self.modifier))
+        data_type = (self.data_type if not self.modifier or self.modifier
+                     < 0 else '%s(%d)' % (self.data_type, self.modifier))
         txt = '%s %s %s' % (self._quote(self.name), data_type,
                             self.is_null_txt())
         if self.default and len(self.default) > 0:
@@ -711,20 +721,20 @@ def create_index(self, table, name, column, schema=None):
 
         table_name = self._table_name(schema, table)
         idx_name = self._quote(name)
-        sql = 'CREATE INDEX %s ON %s (%s)' % (idx_name, table_name,
-                                              self._quote(column))
+        sql = 'CREATE INDEX "%s" ON %s (%s)' % (idx_name, table_name,
+                                                self._quote(column))
         self._exec_sql_and_commit(sql)
 
     def create_spatial_index(self, table, schema=None, geom_column='the_geom'):
         table_name = self._table_name(schema, table)
         idx_name = self._quote(u"sidx_%s_%s" % (table, geom_column))
-        sql = 'CREATE INDEX %s ON %s USING GIST(%s)' % (idx_name, table_name,
-                                                        self._quote(geom_column))
+        sql = 'CREATE INDEX "%s" ON %s USING GIST(%s)' % (idx_name, table_name,
+                                                          self._quote(geom_column))
         self._exec_sql_and_commit(sql)
 
     def delete_index(self, name, schema=None):
         index_name = self._table_name(schema, name)
-        sql = 'DROP INDEX %s' % index_name
+        sql = 'DROP INDEX "%s"' % index_name
         self._exec_sql_and_commit(sql)
 
     def get_database_privileges(self):
@@ -821,8 +831,8 @@ def _exec_sql(self, cursor, sql):
         try:
             cursor.execute(sql)
         except psycopg2.Error as e:
-            raise QgsProcessingException(str(e) + ' QUERY: ' +
-                                         e.cursor.query.decode(e.cursor.connection.encoding))
+            raise QgsProcessingException(str(e) + ' QUERY: '
+                                         + e.cursor.query.decode(e.cursor.connection.encoding))
 
     def _exec_sql_and_commit(self, sql):
         """Tries to execute and commit some action, on error it rolls
@@ -862,7 +872,7 @@ def _table_name(self, schema, table):
         if not schema:
             return self._quote(table)
         else:
-            return u'%s.%s' % (self._quote(schema), self._quote(table))
+            return u'"%s"."%s"' % (self._quote(schema), self._quote(table))
 
 
 # For debugging / testing

diff --git a/tests/src/python/CMakeLists.txt b/tests/src/python/CMakeLists.txt
@@ -159,6 +159,7 @@ ADD_PYTHON_TEST(PyQgsProcessingRecentAlgorithmLog test_qgsprocessingrecentalgori
 ADD_PYTHON_TEST(PyQgsProcessingInPlace test_qgsprocessinginplace.py)
 ADD_PYTHON_TEST(PyQgsProcessingAlgRunner test_qgsprocessingalgrunner.py)
 ADD_PYTHON_TEST(PyQgsProcessingAlgDecorator test_processing_alg_decorator.py)
+ADD_PYTHON_TEST(PyQgsImportIntoPostGIS test_processing_importintopostgis.py)
 ADD_PYTHON_TEST(PyQgsProjectionSelectionWidgets test_qgsprojectionselectionwidgets.py)
 ADD_PYTHON_TEST(PyQgsProjectMetadata test_qgsprojectmetadata.py)
 ADD_PYTHON_TEST(PyQgsPropertyOverrideButton test_qgspropertyoverridebutton.py)

diff --git a/tests/src/python/test_processing_importintopostgis.py b/tests/src/python/test_processing_importintopostgis.py
@@ -0,0 +1,106 @@
+# -*- coding: utf-8 -*-
+"""QGIS Unit tests for Processing Export to Postgis algorithm.
+
+.. note:: This program is free software; you can redistribute it and/or modify
+it under the terms of the GNU General Public License as published by
+the Free Software Foundation; either version 2 of the License, or
+(at your option) any later version.
+"""
+__author__ = 'Alessandro Pasotti'
+__date__ = '2018-09'
+__copyright__ = 'Copyright 2019, The QGIS Project'
+
+import re
+
+from processing.core.Processing import Processing
+from processing.gui.AlgorithmExecutor import execute
+from qgis.analysis import QgsNativeAlgorithms
+from qgis.core import (QgsApplication, QgsVectorLayer,
+                       QgsGeometry, QgsProcessingContext,
+                       QgsProcessingFeedback, QgsSettings,
+                       )
+from qgis.PyQt.QtCore import QCoreApplication
+from qgis.testing import start_app, unittest
+from utilities import unitTestDataPath
+
+start_app()
+
+
+class ConsoleFeedBack(QgsProcessingFeedback):
+
+    _errors = []
+
+    def reportError(self, error, fatalError=False):
+        print(error)
+        self._errors.append(error)
+
+
+class TestExportToPostGis(unittest.TestCase):
+
+    @classmethod
+    def setUpClass(cls):
+        """Run before all tests"""
+        QCoreApplication.setOrganizationName("QGIS_Test")
+        QCoreApplication.setOrganizationDomain(
+            "QGIS_TestPyQgsExportToPostgis.com")
+        QCoreApplication.setApplicationName("QGIS_TestPyQgsExportToPostgis")
+        QgsSettings().clear()
+        Processing.initialize()
+        QgsApplication.processingRegistry().addProvider(QgsNativeAlgorithms())
+        cls.registry = QgsApplication.instance().processingRegistry()
+
+        # Create DB connection in the settings
+        settings = QgsSettings()
+        settings.beginGroup('/PostgreSQL/connections/qgis_test')
+        settings.setValue('service', 'qgis_test')
+        settings.setValue('database', 'qgis_test')
+
+    def test_import(self):
+        """Test algorithm with CamelCaseSchema"""
+
+        alg = self.registry.createAlgorithmById("qgis:importintopostgis")
+        self.assertIsNotNone(alg)
+
+        table_name = 'out_TestPyQgsExportToPostgis'
+
+        parameters = {
+            'CREATEINDEX': True,
+            'DATABASE': 'qgis_test',
+            'DROP_STRING_LENGTH': False,
+            'ENCODING': 'UTF-8',
+            'FORCE_SINGLEPART': False,
+            'GEOMETRY_COLUMN': 'geom',
+            'INPUT': unitTestDataPath() + '/points.shp',
+            'LOWERCASE_NAMES': True,
+            'OVERWRITE': True,
+            'PRIMARY_KEY': None,
+            'SCHEMA': 'CamelCaseSchema',
+            'TABLENAME': table_name
+        }
+
+        feedback = ConsoleFeedBack()
+        context = QgsProcessingContext()
+        # Note: the following returns true also in case of errors ...
+        self.assertTrue(execute(alg, parameters, context, feedback))
+        # ... so we check the log
+        self.assertEqual(feedback._errors, [])
+
+        # Check that data have been imported correctly
+        exported = QgsVectorLayer(unitTestDataPath() + '/points.shp', 'exported')
+        self.assertTrue(exported.isValid())
+        imported = QgsVectorLayer("service='qgis_test' dbname=\'qgis_test\' table=\"CamelCaseSchema\".\"%s\" (geom)" % table_name, 'imported', 'postgres')
+        self.assertTrue(imported.isValid())
+        imported_fields = [f.name() for f in imported.fields()]
+        for f in exported.fields():
+            self.assertTrue(f.name().lower() in imported_fields)
+
+        # Check data
+        imported_f = next(imported.getFeatures("class = 'Jet' AND heading = 85"))
+        self.assertTrue(imported_f.isValid())
+        exported_f = next(exported.getFeatures("class = 'Jet' AND heading = 85"))
+        self.assertTrue(exported_f.isValid())
+        self.assertEqual(exported_f.geometry().asWkt(), imported_f.geometry().asWkt())
+
+
+if __name__ == '__main__':
+    unittest.main()
diff --git a/tests/testdata/provider/testdata_pg.sql b/tests/testdata/provider/testdata_pg.sql
@@ -21,12 +21,22 @@ SET client_min_messages = warning;
 
 CREATE EXTENSION IF NOT EXISTS postgis;
 
+--- Create qgis_test schema
 DROP SCHEMA IF EXISTS qgis_test CASCADE;
 CREATE SCHEMA qgis_test;
 GRANT ALL ON SCHEMA qgis_test TO public;
 ALTER DEFAULT PRIVILEGES IN SCHEMA qgis_test GRANT ALL ON TABLES TO public;
 ALTER DEFAULT PRIVILEGES IN SCHEMA qgis_test GRANT ALL ON SEQUENCES TO public;
 
+
+--- Create "CamelCaseSchema" schema
+DROP SCHEMA IF EXISTS "CamelCaseSchema" CASCADE;
+CREATE SCHEMA "CamelCaseSchema";
+GRANT ALL ON SCHEMA "CamelCaseSchema" TO public;
+ALTER DEFAULT PRIVILEGES IN SCHEMA "CamelCaseSchema" GRANT ALL ON TABLES TO public;
+ALTER DEFAULT PRIVILEGES IN SCHEMA "CamelCaseSchema" GRANT ALL ON SEQUENCES TO public;
+
+
 SET default_tablespace = '';
 
 SET default_with_oids = false;