Subject: [pgAdmin][RM5007] Index should be visible using unique constraints

From: Ganesh Jaybhay <[email-masked]>
Date: Mon, 13 Apr 2020 17:51:41 +0530

Hi Hackers,
Please find the attached patch with below fixes:

- Index dropdown should have existing indexes while creating unique
constraint
- On selecting index from index dropdown, other fields( should be empty and
disabled (Applicable for Primary and unique key constraint)
- Added test cases

Please review.

Regards,
Ganesh Jaybhay
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/primary_key.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/primary_key.js
index 8126184..5ff6b30 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/primary_key.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/primary_key.js
@@ -368,6 +368,14 @@ define('pgadmin.node.primary_key', [
                 Backform.MultiSelectAjaxControl.prototype.remove.apply(this, arguments);
               }
             },
+            render: function() {
+              var index = this.model.get('index');
+              if(!_.isUndefined(index) && index != '') {
+                var col = this.model.get('columns');
+                col.reset([], {silent: true});
+              }
+              return Backform.Select2Control.prototype.render.apply(this, arguments);
+            },
           }),
           deps: ['index'], node: 'column',
           model: pgBrowser.Node.Model.extend({
@@ -408,8 +416,6 @@ define('pgadmin.node.primary_key', [
             if(_.isUndefined(index) || index == '') {
               return false;
             } else {
-              var col = m.get('columns');
-              col.reset();
               return true;
             }
           },
@@ -507,8 +513,9 @@ define('pgadmin.node.primary_key', [
             if(_.isUndefined(index) || index == '') {
               return false;
             } else {
-              var col = m.get('columns');
-              col.reset();
+              setTimeout(function(){
+                m.set('include', []);
+              },10);
               return true;
             }
           },
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/unique_constraint.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/unique_constraint.js
index cc7472e..c23fc05 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/unique_constraint.js
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/static/js/unique_constraint.js
@@ -354,6 +354,14 @@ define('pgadmin.node.unique_constraint', [
                 Backform.MultiSelectAjaxControl.prototype.remove.apply(this, arguments);
               }
             },
+            render: function() {
+              var index = this.model.get('index');
+              if(!_.isUndefined(index) && index != '') {
+                var col = this.model.get('columns');
+                col.reset([], {silent: true});
+              }
+              return Backform.Select2Control.prototype.render.apply(this, arguments);
+            },
           }),
           deps: ['index'], node: 'column',
           model: pgBrowser.Node.Model.extend({
@@ -394,8 +402,6 @@ define('pgadmin.node.unique_constraint', [
             if(_.isUndefined(index) || index == '') {
               return false;
             } else {
-              var col = m.get('columns');
-              col.reset();
               return true;
             }
           },
@@ -493,8 +499,9 @@ define('pgadmin.node.unique_constraint', [
             if(_.isUndefined(index) || index == '') {
               return false;
             } else {
-              var col = m.get('columns');
-              col.reset();
+              setTimeout(function(){
+                m.set('include', []);
+              },10);
               return true;
             }
           },
@@ -527,11 +534,7 @@ define('pgadmin.node.unique_constraint', [
           type: 'text', group: gettext('Definition'),
           control: Backform.NodeListByNameControl.extend({
             initialize:function() {
-              if (_.isUndefined(this.model.top)) {
-                Backform.NodeListByNameControl.prototype.initialize.apply(this,arguments);
-              } else {
-                Backform.Control.prototype.initialize.apply(this,arguments);
-              }
+              Backform.NodeListByNameControl.prototype.initialize.apply(this,arguments);
             },
           }),
           select2:{allowClear:true}, node: 'index',
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/test_index_constraint_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/test_index_constraint_add.py
index 3cd6b3b..f127063 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/test_index_constraint_add.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/test_index_constraint_add.py
@@ -19,6 +19,7 @@ from pgadmin.browser.server_groups.servers.databases.tests import utils as \
 from pgadmin.utils.route import BaseTestGenerator
 from regression import parent_node_dict
 from regression.python_test_utils import test_utils as utils
+from . import utils as index_constraint_utils

 class IndexConstraintAddTestCase(BaseTestGenerator):
@@ -84,3 +85,80 @@ class IndexConstraintAddTestCase(BaseTestGenerator):
     def tearDownClass(cls):
         # Disconnect the database
         database_utils.disconnect_database(cls, cls.server_id, cls.db_id)
+
+
+class ConstraintsUsingIndexAddTestCase(BaseTestGenerator):
+    """This class will add the constraint(primary key or unique key) to the
+    table column using newly created index"""
+    skip_on_database = ['gpdb']
+
+    primary_key_name = "test_primarykey_add_%s" % (str(uuid.uuid4())[1:8])
+    primary_index_name = "test_primaryindex_add_%s" % (str(uuid.uuid4())[1:8])
+    primary_key_data = {
+        "name": primary_key_name,
+        "spcname": "pg_default",
+        "columns": [],
+        "index": primary_index_name
+    }
+
+    unique_key_name = "test_uniquekey_add_%s" % (str(uuid.uuid4())[1:8])
+    unique_index_name = "test_uniqueindex_add_%s" % (str(uuid.uuid4())[1:8])
+    unique_key_data = {
+        "name": unique_key_name,
+        "spcname": "pg_default",
+        "columns": [],
+        "index": unique_index_name
+    }
+
+    scenarios = [
+        ('Add primary Key constraint to table using index',
+         dict(url='/browser/primary_key/obj/', data=primary_key_data)),
+        ('Add unique Key constraint to table using index',
+         dict(url='/browser/unique_constraint/obj/', data=unique_key_data))
+    ]
+
+    @classmethod
+    def setUpClass(cls):
+        cls.db_name = parent_node_dict["database"][-1]["db_name"]
+        schema_info = parent_node_dict["schema"][-1]
+        cls.server_id = schema_info["server_id"]
+        cls.db_id = schema_info["db_id"]
+        db_con = database_utils.connect_database(cls, utils.SERVER_GROUP,
+                                                 cls.server_id, cls.db_id)
+        if not db_con['data']["connected"]:
+            raise Exception("Could not connect to database to add a "
+                            "constraint using index.")
+        cls.schema_id = schema_info["schema_id"]
+        cls.schema_name = schema_info["schema_name"]
+        schema_response = schema_utils.verify_schemas(cls.server,
+                                                      cls.db_name,
+                                                      cls.schema_name)
+        if not schema_response:
+            raise Exception("Could not find the schema to add a index "
+                            "constraint(primary key or unique key).")
+        cls.table_name = "table_constraint_%s" % (str(uuid.uuid4())[1:8])
+        cls.table_id = tables_utils.create_table(cls.server,
+                                                 cls.db_name,
+                                                 cls.schema_name,
+                                                 cls.table_name)
+
+    def runTest(self):
+        """This function will add index constraint(primary key or unique key)
+        to table column."""
+        self.index_id = \
+            index_constraint_utils.create_unique_index(
+                self.server, self.db_name, self.schema_name, self.table_name,
+                self.data["index"], "name")
+
+        response = self.tester.post(
+            self.url + str(utils.SERVER_GROUP) + '/' +
+            str(self.server_id) + '/' + str(self.db_id) +
+            '/' + str(self.schema_id) + '/' + str(self.table_id) + '/',
+            data=json.dumps(self.data),
+            content_type='html/json')
+        self.assertEquals(response.status_code, 200)
+
+    @classmethod
+    def tearDownClass(cls):
+        # Disconnect the database
+        database_utils.disconnect_database(cls, cls.server_id, cls.db_id)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/utils.py
index d175de5..11dafce 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/index_constraint/tests/utils.py
@@ -88,3 +88,40 @@ def verify_index_constraint(server, db_name, table_name):
         return index_constraint
     except Exception:
         traceback.print_exc(file=sys.stderr)
+
+
+def create_unique_index(server, db_name, schema_name, table_name,
+                        index_name, column_name):
+    """
+    This function creates a unique index for provided table.
+    :param server: server details
+    :type server: dict
+    :param db_name: database name
+    :type db_name: str
+    :param schema_name: schema name
+    :type schema_name: str
+    :param table_name: table name
+    :type table_name: str
+    :param index_name: index name
+    :type index_name: str
+    :param column_name: column on which index to be created
+    :type column_name: str
+    """
+    try:
+        connection = utils.get_db_connection(db_name,
+                                             server['username'],
+                                             server['db_password'],
+                                             server['host'],
+                                             server['port'],
+                                             server['sslmode'])
+        old_isolation_level = connection.isolation_level
+        connection.set_isolation_level(0)
+        pg_cursor = connection.cursor()
+        query = "CREATE UNIQUE INDEX CONCURRENTLY %s ON %s.%s (%s)" % \
+                (index_name, schema_name, table_name, column_name)
+        pg_cursor.execute(query)
+        connection.set_isolation_level(old_isolation_level)
+        connection.commit()
+        connection.close()
+    except Exception:
+        traceback.print_exc(file=sys.stderr)

---

From: Akshay Joshi <[email-masked]>
Date: Tue, 14 Apr 2020 14:09:42 +0530

Thanks, patch applied.

On Mon, Apr 13, 2020 at 5:51 PM Ganesh Jaybhay <
[email-masked]> wrote:

> - On selecting index from index dropdown, other fields( should be empty
> and disabled (Applicable for Primary and unique key constraint)
> - Added test cases
>
> Please review.
>
> Regards,
> Ganesh Jaybhay
>

-- 
*Thanks & Regards*
*Akshay Joshi*

*Sr. Software Architect*
*EnterpriseDB Software India Private Limited*
*Mobile: +91 976-788-8246*