public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" 6+ messages / 2 participants [nested] [flat]
* [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-21 08:35 Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Khushboo Vashi @ 2019-01-21 08:35 UTC (permalink / raw) To: pgadmin-hackers 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) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-21 09:27 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Akshay Joshi @ 2019-01-21 09:27 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Hi Khushboo Can you please fix the following and send the patch again - PEP8 issue - Test cases is failing with Python 3.5. On Mon, Jan 21, 2019 at 2:06 PM Khushboo Vashi < [email protected]> wrote: > 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 > -- *Akshay Joshi* *Sr. Software Architect * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-22 10:21 Khushboo Vashi <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Khushboo Vashi @ 2019-01-22 10:21 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers Hi Akshay, Please find the attached updated patch. On Mon, Jan 21, 2019 at 2:57 PM Akshay Joshi <[email protected]> wrote: > Hi Khushboo > > Can you please fix the following and send the patch again > > - PEP8 issue > > Fixed > > - Test cases is failing with Python 3.5. > > I have tested with Python 3.7 and it is working fine. Please give me the log to reproduce the issue. > > On Mon, Jan 21, 2019 at 2:06 PM Khushboo Vashi < > [email protected]> wrote: > >> 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 >> > > > Thanks, Khushboo > -- > *Akshay Joshi* > > *Sr. Software Architect * > > > > *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* > Attachments: [application/octet-stream] RM_3853_v1.patch (9.2K, 3-RM_3853_v1.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..0f022f1c --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/test_domain_sql.py @@ -0,0 +1,117 @@ +########################################################################## +# +# 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..3bd9a27f 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,8 @@ 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 +38,14 @@ 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 +84,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) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-22 10:47 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Akshay Joshi @ 2019-01-22 10:47 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Hi Khushboo On Tue, Jan 22, 2019 at 3:51 PM Khushboo Vashi < [email protected]> wrote: > Hi Akshay, > > Please find the attached updated patch. > > On Mon, Jan 21, 2019 at 2:57 PM Akshay Joshi < > [email protected]> wrote: > >> Hi Khushboo >> >> Can you please fix the following and send the patch again >> >> - PEP8 issue >> >> Fixed > >> >> - Test cases is failing with Python 3.5. >> >> I have tested with Python 3.7 and it is working fine. Please give me the > log to reproduce the issue. > I have tested it with Python 3.5 with PG10 and 11. Attached is the screenshot. > >> On Mon, Jan 21, 2019 at 2:06 PM Khushboo Vashi < >> [email protected]> wrote: >> >>> 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 >>> >> >> >> Thanks, > Khushboo > >> -- >> *Akshay Joshi* >> >> *Sr. Software Architect * >> >> >> >> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >> > -- *Akshay Joshi* *Sr. Software Architect * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* Attachments: [image/png] TestCase_Error.png (279.6K, 3-TestCase_Error.png) download | view image ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-22 11:58 Khushboo Vashi <[email protected]> parent: Akshay Joshi <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Khushboo Vashi @ 2019-01-22 11:58 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers Hi, Please find the attached updated patch. On Tue, Jan 22, 2019 at 4:17 PM Akshay Joshi <[email protected]> wrote: > Hi Khushboo > > On Tue, Jan 22, 2019 at 3:51 PM Khushboo Vashi < > [email protected]> wrote: > >> Hi Akshay, >> >> Please find the attached updated patch. >> >> On Mon, Jan 21, 2019 at 2:57 PM Akshay Joshi < >> [email protected]> wrote: >> >>> Hi Khushboo >>> >>> Can you please fix the following and send the patch again >>> >>> - PEP8 issue >>> >>> Fixed >> >>> >>> - Test cases is failing with Python 3.5. >>> >>> I have tested with Python 3.7 and it is working fine. Please give me the >> log to reproduce the issue. >> > > I have tested it with Python 3.5 with PG10 and 11. Attached is the > screenshot. > >> >>> Fixed. > On Mon, Jan 21, 2019 at 2:06 PM Khushboo Vashi < >>> [email protected]> wrote: >>> >>>> 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 >>>> >>> >>> >>> Thanks, >> Khushboo >> >>> -- >>> *Akshay Joshi* >>> >>> *Sr. Software Architect * >>> >>> >>> >>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>> >> > > -- > *Akshay Joshi* > > *Sr. Software Architect * > > > > *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* > Attachments: [application/octet-stream] RM_3853_v2.patch (9.2K, 3-RM_3853_v2.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..0d60db3a --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/domains/tests/test_domain_sql.py @@ -0,0 +1,117 @@ +########################################################################## +# +# 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.decode('utf-8')) + + # 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..3bd9a27f 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,8 @@ 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 +38,14 @@ 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 +84,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) ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" @ 2019-01-22 12:24 Akshay Joshi <[email protected]> parent: Khushboo Vashi <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Akshay Joshi @ 2019-01-22 12:24 UTC (permalink / raw) To: Khushboo Vashi <[email protected]>; +Cc: pgadmin-hackers Thanks patch applied On Tue, Jan 22, 2019 at 5:28 PM Khushboo Vashi < [email protected]> wrote: > Hi, > > Please find the attached updated patch. > > On Tue, Jan 22, 2019 at 4:17 PM Akshay Joshi < > [email protected]> wrote: > >> Hi Khushboo >> >> On Tue, Jan 22, 2019 at 3:51 PM Khushboo Vashi < >> [email protected]> wrote: >> >>> Hi Akshay, >>> >>> Please find the attached updated patch. >>> >>> On Mon, Jan 21, 2019 at 2:57 PM Akshay Joshi < >>> [email protected]> wrote: >>> >>>> Hi Khushboo >>>> >>>> Can you please fix the following and send the patch again >>>> >>>> - PEP8 issue >>>> >>>> Fixed >>> >>>> >>>> - Test cases is failing with Python 3.5. >>>> >>>> I have tested with Python 3.7 and it is working fine. Please give me >>> the log to reproduce the issue. >>> >> >> I have tested it with Python 3.5 with PG10 and 11. Attached is the >> screenshot. >> >>> >>>> Fixed. > >> On Mon, Jan 21, 2019 at 2:06 PM Khushboo Vashi < >>>> [email protected]> wrote: >>>> >>>>> 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 >>>>> >>>> >>>> >>>> Thanks, >>> Khushboo >>> >>>> -- >>>> *Akshay Joshi* >>>> >>>> *Sr. Software Architect * >>>> >>>> >>>> >>>> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >>>> >>> >> >> -- >> *Akshay Joshi* >> >> *Sr. Software Architect * >> >> >> >> *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* >> > -- *Akshay Joshi* *Sr. Software Architect * *Phone: +91 20-3058-9517Mobile: +91 976-788-8246* ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-01-22 12:24 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-01-21 08:35 [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Khushboo Vashi <[email protected]> 2019-01-21 09:27 ` Akshay Joshi <[email protected]> 2019-01-22 10:21 ` Khushboo Vashi <[email protected]> 2019-01-22 10:47 ` Akshay Joshi <[email protected]> 2019-01-22 11:58 ` Khushboo Vashi <[email protected]> 2019-01-22 12:24 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox