public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "["
Date: Mon, 21 Jan 2019 14:05:56 +0530
Message-ID: <CAFOhELeOFhg9izkcnWgmpXBy4nc3vCj6XNnMaJYx8Cx+c0nT8g@mail.gmail.com> (raw)
Hi,
Please find the attached patch to fix the RM #3853 - Incorrect SQL
generated for create script of domain type interval with precision. ERROR:
syntax error at or near "["
The patch includes the fix as well as the API test case to verify the
reverse Engineered SQL.
Thanks,
Khushboo
Attachments:
[application/octet-stream] RM_3853.patch (8.9K, 3-RM_3853.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/__init__.py
index 90c3cf4d..bededa90 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/__init__.py
@@ -446,7 +446,7 @@ It may have been removed by another user or moved to another schema.
typ_len = typlen[0]
typ_precision = typlen[1]
else:
- typ_len = typlen
+ typ_len = typlen[0]
typ_precision = ''
return {'typlen': typ_len, 'precision': typ_precision}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/test_domain_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/test_domain_sql.py
new file mode 100644
index 00000000..f36f4ada
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/test_domain_sql.py
@@ -0,0 +1,109 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import uuid
+import json
+import re
+
+from pgadmin.browser.server_groups.servers.databases.schemas.tests import \
+ utils as schema_utils
+from pgadmin.browser.server_groups.servers.databases.tests import utils as \
+ database_utils
+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 domain_utils
+
+
+class DomainReverseEngineeredSQLTestCase(BaseTestGenerator):
+ """ This class will verify reverse engineered sql for domain under schema node. """
+ scenarios = [
+ # Fetching default URL for domain node.
+ ('Domain Reverse Engineered SQL with char',
+ dict(url='/browser/domain/sql/',
+ domain_name='domain_get_%s' % (str(uuid.uuid4())[1:8]),
+ domain_sql='AS "char";'
+ )
+ ),
+ ('Domain Reverse Engineered SQL with Length, Precision and Default',
+ dict(url='/browser/domain/sql/',
+ domain_name='domain_get_%s' % (str(uuid.uuid4())[1:8]),
+ domain_sql='AS numeric(12,2) DEFAULT 12 NOT NULL;'
+ )
+ ),
+ ('Domain Reverse Engineered SQL with Length',
+ dict(url='/browser/domain/sql/',
+ domain_name='domain_get_%s' % (str(uuid.uuid4())[1:8]),
+ domain_sql='AS interval(6);'
+ )
+ ),
+ ]
+
+ def setUp(self):
+ self.database_info = parent_node_dict["database"][-1]
+ self.db_name = self.database_info["db_name"]
+ self.schema_info = parent_node_dict["schema"][-1]
+ self.schema_name = self.schema_info["schema_name"]
+ self.schema_id = self.schema_info["schema_id"]
+ self.domain_info = domain_utils.create_domain(self.server,
+ self.db_name,
+ self.schema_name,
+ self.schema_id,
+ self.domain_name,
+ self.domain_sql)
+
+ def runTest(self):
+ """ This function will add domain and verify the reverse engineered sql. """
+ db_id = self.database_info["db_id"]
+ server_id = self.database_info["server_id"]
+ db_con = database_utils.connect_database(self, utils.SERVER_GROUP,
+ server_id, db_id)
+ if not db_con['data']["connected"]:
+ raise Exception("Could not connect to database to get the domain.")
+
+ db_name = self.database_info["db_name"]
+ schema_response = schema_utils.verify_schemas(self.server,
+ db_name,
+ self.schema_name)
+ if not schema_response:
+ raise Exception("Could not find the schema to get the domain.")
+ domain_id = self.domain_info[0]
+
+ # Call GET API to fetch the domain sql
+ get_response = self.tester.get(
+ self.url + str(utils.SERVER_GROUP) + '/' +
+ str(server_id) + '/' +
+ str(db_id) + '/' +
+ str(self.schema_id) + '/' +
+ str(domain_id),
+ content_type='html/json')
+
+ self.assertEquals(get_response.status_code, 200)
+ orig_sql = json.loads(get_response.data)
+
+ # Replace multiple spaces with one space and check the expected sql
+ sql = re.sub('\s+', ' ', orig_sql).strip()
+ expected_sql = '-- DOMAIN: {0}.{1} -- DROP DOMAIN {0}.{1}; CREATE DOMAIN {0}.{1} {2} ' \
+ 'ALTER DOMAIN {0}.{1} OWNER TO postgres;'.format(self.schema_name, self.domain_name,
+ self.domain_sql)
+
+ self.assertEquals(sql, expected_sql)
+
+ domain_utils.delete_domain(self.server, db_name, self.schema_name, self.domain_name)
+
+ # Verify the reverse engineered sql with creating domain with the sql we get from the server
+ domain_utils.create_domain_from_sql(self.server, db_name, orig_sql)
+
+ domain_utils.delete_domain(self.server, db_name, self.schema_name, self.domain_name)
+
+ # Disconnect the database
+ database_utils.disconnect_database(self, server_id, db_id)
+
+ def tearDown(self):
+ pass
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/utils.py
index 4354eb29..fb92dfcc 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/utils.py
@@ -15,7 +15,7 @@ import traceback
from regression.python_test_utils import test_utils as utils
-def create_domain(server, db_name, schema_name, schema_id, domain_name):
+def create_domain(server, db_name, schema_name, schema_id, domain_name, domain_sql=None):
"""
This function is used to add the domain to existing schema
:param server: server details
@@ -37,8 +37,13 @@ def create_domain(server, db_name, schema_name, schema_id, domain_name):
server['host'],
server['port'])
pg_cursor = connection.cursor()
- query = 'CREATE DOMAIN ' + schema_name + '.' + domain_name + \
- ' AS character(10) DEFAULT 1'
+
+ if domain_sql is None:
+ query = 'CREATE DOMAIN ' + schema_name + '.' + domain_name + \
+ ' AS character(10) DEFAULT 1'
+ else:
+ query = 'CREATE DOMAIN ' + schema_name + '.' + domain_name + ' ' + domain_sql
+
pg_cursor.execute(query)
connection.commit()
# Get 'oid' from newly created domain
@@ -77,3 +82,51 @@ def verify_domain(server, db_name, schema_id, domain_name):
domains = pg_cursor.fetchone()
connection.close()
return domains
+
+
+def delete_domain(server, db_name, schema_name, domain_name):
+ """
+ This function deletes the domain.
+ :param server:
+ :param db_name:
+ :param schema_name:
+ :param domain_name:
+ :return:
+ """
+
+ try:
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'])
+ pg_cursor = connection.cursor()
+ pg_cursor.execute("DROP DOMAIN %s.%s" %
+ (schema_name, domain_name))
+ connection.commit()
+ connection.close()
+ except Exception:
+ traceback.print_exc(file=sys.stderr)
+
+
+def create_domain_from_sql(server, db_name, sql):
+ """
+ This function create domain from the reverse engineered sql
+ :param server:
+ :param db_name:
+ :param sql:
+ :return:
+ """
+
+ try:
+ connection = utils.get_db_connection(db_name,
+ server['username'],
+ server['db_password'],
+ server['host'],
+ server['port'])
+ pg_cursor = connection.cursor()
+ pg_cursor.execute(sql)
+ connection.commit()
+ connection.close()
+ except Exception:
+ traceback.print_exc(file=sys.stderr)
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "["
In-Reply-To: <CAFOhELeOFhg9izkcnWgmpXBy4nc3vCj6XNnMaJYx8Cx+c0nT8g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox