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]>
2019-01-21 09:27 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[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 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 ` Re: [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]>
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-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 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
@ 2019-01-22 10:21 ` Khushboo Vashi <[email protected]>
2019-01-22 10:47 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" 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-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 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
2019-01-22 10:21 ` Re: [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-22 10:47 ` Akshay Joshi <[email protected]>
2019-01-22 11:58 ` Re: [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]>
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-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 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
2019-01-22 10:21 ` Re: [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-22 10:47 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
@ 2019-01-22 11:58 ` Khushboo Vashi <[email protected]>
2019-01-22 12:24 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" 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-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 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
2019-01-22 10:21 ` Re: [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-22 10:47 ` Re: [pgAdmin4][Patch] - RM 3853 - Incorrect SQL generated for create script of domain type interval with precision. ERROR: syntax error at or near "[" Akshay Joshi <[email protected]>
2019-01-22 11:58 ` Re: [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-22 12:24 ` Akshay Joshi <[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