public inbox for [email protected]
help / color / mirror / Atom feedFrom: Khushboo Vashi <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: pgadmin-hackers <[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 "["
Date: Tue, 22 Jan 2019 17:28:16 +0530
Message-ID: <CAFOhELd=tqo1sbZZ+CcdEDtiL7W4g8M-F0aRi7fXnmQ2siAvRA@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDeora2r22qMDUpDkH9B5wcBcZ93iYKEfUaUryTENUN-6A@mail.gmail.com>
References: <CAFOhELeOFhg9izkcnWgmpXBy4nc3vCj6XNnMaJYx8Cx+c0nT8g@mail.gmail.com>
<CANxoLDcxvRCXg_eDiaRBc+b_kT7nyJBFJFvdELm+3B5qgiRixQ@mail.gmail.com>
<CAFOhELcmBf+D=T-3mcjc2RYJiNFjLdat6W2RVhkz=19y9uAnew@mail.gmail.com>
<CANxoLDeora2r22qMDUpDkH9B5wcBcZ93iYKEfUaUryTENUN-6A@mail.gmail.com>
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)
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], [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: <CAFOhELd=tqo1sbZZ+CcdEDtiL7W4g8M-F0aRi7fXnmQ2siAvRA@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