[postgres] fix domain not in public schema

fix #18053 see PR #6304
qgis · Feb 11, 2018 · 1f051ff · 1f051ff
1 parent c34f23c
commit 1f051ff
Show file tree

Hide file tree

Showing 5 changed files with 92 additions and 3 deletions.
diff --git a/src/providers/postgres/qgspostgresprovider.cpp b/src/providers/postgres/qgspostgresprovider.cpp
@@ -1582,6 +1582,9 @@ void QgsPostgresProvider::enumValues( int index, QStringList& enumList )
   QString fieldName = mAttributeFields.at( index ).name();
   QString typeName = mAttributeFields.at( index ).typeName();
 
+  // Remove schema extension from typeName
+  typeName.remove( QRegExp( "^([^.]+\\.)+" ) );
+
   //is type an enum?
   QString typeSql = QString( "SELECT typtype FROM pg_type WHERE typname=%1" ).arg( quotedValue( typeName ) );
   QgsPostgresResult typeRes( connectionRO()->PQexec( typeSql ) );
@@ -1634,12 +1637,22 @@ bool QgsPostgresProvider::parseDomainCheckConstraint( QStringList& enumValues, c
   enumValues.clear();
 
   //is it a domain type with a check constraint?
-  QString domainSql = QString( "SELECT domain_name FROM information_schema.columns WHERE table_name=%1 AND column_name=%2" ).arg( quotedValue( mTableName ), quotedValue( attributeName ) );
+  QString domainSql = QString( "SELECT domain_name, domain_schema FROM information_schema.columns WHERE table_name=%1 AND column_name=%2" ).arg( quotedValue( mTableName ), quotedValue( attributeName ) );
   QgsPostgresResult domainResult( connectionRO()->PQexec( domainSql ) );
-  if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 )
+  if ( domainResult.PQresultStatus() == PGRES_TUPLES_OK && domainResult.PQntuples() > 0 && !domainResult.PQgetvalue( 0, 0 ).isNull() )
   {
     //a domain type
-    QString domainCheckDefinitionSql = QString( "SELECT consrc FROM pg_constraint WHERE conname=(SELECT constraint_name FROM information_schema.domain_constraints WHERE domain_name=%1)" ).arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) );
+    QString domainCheckDefinitionSql = QString( ""
+                                       "SELECT consrc FROM pg_constraint "
+                                       "  WHERE contypid =("
+                                       "    SELECT oid FROM pg_type "
+                                       "      WHERE typname = %1 "
+                                       "      AND typnamespace =("
+                                       "        SELECT oid FROM pg_namespace WHERE nspname = %2"
+                                       "      )"
+                                       "    )" )
+                                       .arg( quotedValue( domainResult.PQgetvalue( 0, 0 ) ) )
+                                       .arg( quotedValue( domainResult.PQgetvalue( 0, 1 ) ) );
     QgsPostgresResult domainCheckRes( connectionRO()->PQexec( domainCheckDefinitionSql ) );
     if ( domainCheckRes.PQresultStatus() == PGRES_TUPLES_OK && domainCheckRes.PQntuples() > 0 )
     {

diff --git a/tests/src/python/CMakeLists.txt b/tests/src/python/CMakeLists.txt
@@ -74,6 +74,7 @@ ADD_PYTHON_TEST(PyQgsPalLabelingComposer test_qgspallabeling_composer.py)
 ADD_PYTHON_TEST(PyQgsPalLabelingPlacement test_qgspallabeling_placement.py)
 ADD_PYTHON_TEST(PyQgsPanelWidget test_qgspanelwidget.py)
 ADD_PYTHON_TEST(PyQgsPoint test_qgspoint.py)
+ADD_PYTHON_TEST(PyQgsPostgresDomain test_qgspostgresdomain.py)
 ADD_PYTHON_TEST(PyQgsRangeWidgets test_qgsrangewidgets.py)
 ADD_PYTHON_TEST(PyQgsRasterFileWriter test_qgsrasterfilewriter.py)
 ADD_PYTHON_TEST(PyQgsRasterLayer test_qgsrasterlayer.py)

diff --git a/tests/src/python/test_qgspostgresdomain.py b/tests/src/python/test_qgspostgresdomain.py
@@ -0,0 +1,49 @@
+# -*- coding: utf-8 -*-
+"""QGIS Unit tests for Postgres domains.
+
+.. 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__ = 'Denis Rouzaud'
+__date__ = '10/02/2018'
+__copyright__ = 'Copyright 2018, The QGIS Project'
+# This will get replaced with a git SHA1 when you do a git archive
+__revision__ = '$Format:%H$'
+
+import qgis  # NOQA
+
+import os
+
+from qgis.core import QgsVectorLayer, QgsProject
+
+from qgis.testing import start_app, unittest
+
+start_app()
+
+
+class TestQgsPostgresDomain(unittest.TestCase):
+
+    @classmethod
+    def setUpClass(cls):
+        """
+        Setup the involved layer
+        :return:
+        """
+        cls.dbconn = 'service=\'qgis_test\''
+        if 'QGIS_PGTEST_DB' in os.environ:
+            cls.dbconn = os.environ['QGIS_PGTEST_DB']
+        # Create test layer
+        cls.vl = QgsVectorLayer(cls.dbconn + ' sslmode=disable key=\'pk\' table="qgis_test"."colors" sql=', 'colors', 'postgres')
+
+        QgsProject.instance().addMapLayer(cls.vl)
+
+    def test_postgres_domain(self):
+        self.assertEqual(self.vl.dataProvider().enumValues(1), ['red', 'green', 'blue'])
+        self.assertEqual(self.vl.dataProvider().enumValues(2), ['yellow', 'cyan', 'magenta'])
+        self.assertEqual(self.vl.dataProvider().enumValues(3), ['Alchemilla', 'Alstroemeria', 'Alyssum'])
+
+
+if __name__ == '__main__':
+    unittest.main()
diff --git a/tests/testdata/provider/testdata_pg.sh b/tests/testdata/provider/testdata_pg.sh
@@ -5,6 +5,7 @@ SCRIPTS="
   tests/testdata/provider/testdata_pg_reltests.sql
   tests/testdata/provider/testdata_pg_vectorjoin.sql
   tests/testdata/provider/testdata_pg_raster.sql
+  tests/testdata/provider/testdata_pg_domain.sql
 "
 
 createdb qgis_test || exit 1

diff --git a/tests/testdata/provider/testdata_pg_domain.sql b/tests/testdata/provider/testdata_pg_domain.sql
@@ -0,0 +1,25 @@
+
+
+
+CREATE DOMAIN public.colordomain
+  AS text
+  COLLATE pg_catalog."default"
+  CONSTRAINT domainconstraint CHECK (VALUE = ANY (ARRAY['red'::text, 'green'::text, 'blue'::text]));
+
+CREATE DOMAIN qgis_test.colordomain
+  AS text
+  COLLATE pg_catalog."default"
+  CONSTRAINT domainconstraint CHECK (VALUE = ANY (ARRAY['yellow'::text, 'cyan'::text, 'magenta'::text]));
+
+CREATE DOMAIN qgis_test.flowerdomain
+  AS text
+  COLLATE pg_catalog."default"
+  CONSTRAINT domainconstraint CHECK (VALUE = ANY (ARRAY['Alchemilla'::text, 'Alstroemeria'::text, 'Alyssum'::text]));
+
+CREATE TABLE qgis_test.colors
+(
+id SERIAL NOT NULL,
+color_public colordomain,
+color_qgis qgis_test.colordomain,
+flower qgis_test.flowerdomain
+)