public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [pgAdmin4][Patch]: Feature #4202 Implement new framework to test Reverse Engineering SQL
Date: Thu, 13 Jun 2019 17:22:11 +0530
Message-ID: <CANxoLDe_FGeAnGy9Vo22VBKRfd8-=yb4AbZcE4AbyR-tUnYgBg@mail.gmail.com> (raw)
Hi Hackers
I have implemented the new test framework to test the Reverse Engineering
SQL. I have integrated it as a part of API/Regression test suite. It will
work when we run all the test cases or module wise test case.
*How it works*: Attached patch contains the generic framework to read all
the JSON files from the *tests->version based (example 9.6_plus, 10_plus or
default) folder. *Run all the test scenarios present in the JSON file in
sequential order.
Format of the JSON file is mentioned in
"<path_of_source>web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/test.json"
For expected SQL we will have following two options:
- Provide the expected sql in scenario itself as parameter *"expected_sql"
: "<SQL>"*.
- Create a output file with any name in the same directory where the
JSON file resides and specify the parameter "*expected_sql_file": "<name
of the file>"*
Attached patch contains both the above mentioned examples.
Please review it.
--
*Thanks & Regards*
*Akshay Joshi*
*Sr. Software Architect*
*EnterpriseDB Software India Private Limited*
*Mobile: +91 976-788-8246*
Attachments:
[application/octet-stream] Test_Re_SQL.patch (15.2K, 3-Test_Re_SQL.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/alter_implicit_cast.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/alter_implicit_cast.sql
new file mode 100644
index 00000000..98b7e452
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/alter_implicit_cast.sql
@@ -0,0 +1,9 @@
+-- Cast: money -> bigint
+
+-- DROP CAST (money AS bigint);
+
+CREATE CAST (money AS bigint)
+ WITHOUT FUNCTION
+ AS IMPLICIT;
+
+COMMENT ON CAST (money AS bigint) IS 'Cast from money to bigint';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/create_implicit_cast.sql b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/create_implicit_cast.sql
new file mode 100644
index 00000000..68ffe500
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/create_implicit_cast.sql
@@ -0,0 +1,7 @@
+-- Cast: money -> bigint
+
+-- DROP CAST (money AS bigint);
+
+CREATE CAST (money AS bigint)
+ WITHOUT FUNCTION
+ AS IMPLICIT;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/test.json b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/test.json
new file mode 100644
index 00000000..6f41da8a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/casts/tests/default/test.json
@@ -0,0 +1,68 @@
+{
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create IMPLICIT Cast",
+ "endpoint": "NODE-cast.obj",
+ "sql_endpoint": "NODE-cast.sql_id",
+ "data": {
+ "castcontext": "IMPLICIT",
+ "encoding": "UTF8",
+ "name": "money->bigint",
+ "srctyp": "money",
+ "trgtyp": "bigint"
+ },
+ "expected_sql_file": "create_implicit_cast.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter IMPLICIT Cast",
+ "endpoint": "NODE-cast.obj_id",
+ "sql_endpoint": "NODE-cast.sql_id",
+ "data": {
+ "description": "Cast from money to bigint"
+ },
+ "expected_sql_file": "alter_implicit_cast.sql"
+ },
+ {
+ "type": "delete",
+ "name": "Drop IMPLICIT Cast",
+ "endpoint": "NODE-cast.delete_id",
+ "data": {
+ "name": "money->bigint"
+ }
+ },
+ {
+ "type": "create",
+ "name":"Create EXPLICIT Cast",
+ "endpoint": "NODE-cast.obj",
+ "sql_endpoint": "NODE-cast.sql_id",
+ "data": {
+ "castcontext": "EXPLICIT",
+ "encoding": "UTF8",
+ "name": "money->bigint",
+ "srctyp": "money",
+ "trgtyp": "bigint"
+ },
+ "expected_sql": "-- Cast: money -> bigint\n\n-- DROP CAST (money AS bigint);\n\nCREATE CAST (money AS bigint)\n\tWITHOUT FUNCTION;"
+ },
+ {
+ "type": "alter",
+ "name": "Alter EXPLICIT Cast",
+ "endpoint": "NODE-cast.obj_id",
+ "sql_endpoint": "NODE-cast.sql_id",
+ "data": {
+ "description": "Cast from money to bigint"
+ },
+ "expected_sql": "-- Cast: money -> bigint\n\n-- DROP CAST (money AS bigint);\n\nCREATE CAST (money AS bigint)\n\tWITHOUT FUNCTION;\n\nCOMMENT ON CAST (money AS bigint) IS 'Cast from money to bigint';"
+ },
+ {
+ "type": "delete",
+ "name": "Drop EXPLICIT Cast",
+ "endpoint": "NODE-cast.delete_id",
+ "data": {
+ "name": "money->bigint"
+ }
+ }
+ ]
+}
diff --git a/web/pgadmin/utils/route.py b/web/pgadmin/utils/route.py
index b206cb2e..6f5bb799 100644
--- a/web/pgadmin/utils/route.py
+++ b/web/pgadmin/utils/route.py
@@ -53,9 +53,8 @@ class TestsGeneratorRegistry(ABCMeta):
ABCMeta.__init__(cls, name, bases, d)
@classmethod
- def load_generators(cls, pkg_root, exclude_pkgs, for_modules=[]):
-
- cls.registry = dict()
+ def load_generators(cls, pkg_root, exclude_pkgs, for_modules=[],
+ set_module_list=True):
all_modules = []
@@ -68,6 +67,13 @@ class TestsGeneratorRegistry(ABCMeta):
for fmod in for_modules
if module_name.endswith(fmod)]
+ # Set the module list and exclude packages in the BaseTestGenerator
+ # for Reverse Engineer SQL test cases.
+ BaseTestGenerator.setExcludePkgs(exclude_pkgs)
+ if set_module_list is True:
+ cls.registry = dict()
+ BaseTestGenerator.setModuleList(all_modules)
+
# Check for SERVER mode
for module_name in all_modules:
try:
@@ -123,3 +129,11 @@ class BaseTestGenerator(unittest.TestCase):
@classmethod
def setTestDatabaseName(cls, database_name):
cls.test_db = database_name
+
+ @classmethod
+ def setModuleList(cls, module_list):
+ cls.module_list = module_list
+
+ @classmethod
+ def setExcludePkgs(cls, exclude_pkgs):
+ cls.exclude_pkgs = exclude_pkgs
diff --git a/web/regression/re_sql/__init__.py b/web/regression/re_sql/__init__.py
new file mode 100644
index 00000000..e69de29b
diff --git a/web/regression/re_sql/tests/__init__.py b/web/regression/re_sql/tests/__init__.py
new file mode 100644
index 00000000..e69de29b
diff --git a/web/regression/re_sql/tests/test_resql.py b/web/regression/re_sql/tests/test_resql.py
new file mode 100644
index 00000000..d7930f24
--- /dev/null
+++ b/web/regression/re_sql/tests/test_resql.py
@@ -0,0 +1,221 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2019, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import json
+import os
+
+from flask import url_for
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils as utils
+from pgadmin.browser.server_groups.servers.databases.tests import \
+ utils as database_utils
+from pgadmin.utils.versioned_template_loader import \
+ get_version_mapping_directories
+
+
+def create_resql_module_list(all_modules, exclude_pkgs):
+ """
+ This function is used to create the module list for reverse engineering
+ SQL by iterating all the modules.
+
+ :param all_modules: List of all the modules
+ :param exclude_pkgs: List of exclude packages
+ :return:
+ """
+ resql_module_list = dict()
+
+ for module in all_modules:
+ if "tests." in str(module) and not any(str(module).startswith(
+ 'pgadmin.' + str(exclude_pkg)) for exclude_pkg in exclude_pkgs
+ ):
+ complete_module_name = module.split(".test")
+ module_name_list = complete_module_name[0].split(".")
+ module_name = module_name_list[len(module_name_list) - 1]
+
+ resql_module_list[module_name] = os.path.join(*module_name_list)
+
+ return resql_module_list
+
+
+class ReverseEngineeringSQLTestCase(BaseTestGenerator):
+ """ This class will test the reverse engineering SQL"""
+
+ scenarios = [
+ ('Reverse Engineering SQL Test Cases', dict())
+ ]
+
+ def setUp(self):
+ # Get the database connection
+ self.db_con = database_utils.connect_database(
+ self, utils.SERVER_GROUP, self.server_information['server_id'],
+ self.server_information['db_id'])
+ if not self.db_con['info'] == "Database connected.":
+ raise Exception("Could not connect to database.")
+
+ # Get the application path
+ self.apppath = os.getcwd()
+
+ def runTest(self):
+ # Create the module list on which reverse engineering sql test
+ # cases will be executed.
+ resql_module_list = create_resql_module_list(
+ BaseTestGenerator.module_list, BaseTestGenerator.exclude_pkgs)
+
+ for module in resql_module_list:
+ module_path = resql_module_list[module]
+ # Get the folder name based on server version number and
+ # their existence.
+ status, self.test_folder = self.get_test_folder(module_path)
+ if not status:
+ continue
+
+ # Iterate all the files in the test folder and check for
+ # the JSON files.
+ for filename in os.listdir(self.test_folder):
+ if filename.endswith(".json"):
+ complete_file_name = os.path.join(self.test_folder,
+ filename)
+ with open(complete_file_name) as jsonfp:
+ data = json.load(jsonfp)
+ for key, scenarios in data.items():
+ self.execute_test_case(scenarios)
+
+ def tearDown(self):
+ database_utils.disconnect_database(
+ self, self.server_information['server_id'],
+ self.server_information['db_id'])
+
+ def get_url(self, endpoint, object_id=None):
+ """
+ This function is used to get the url.
+
+ :param endpoint:
+ :param object_id:
+ :return:
+ """
+ object_url = None
+ for rule in self.app.url_map.iter_rules(endpoint):
+ options = {}
+ for arg in rule.arguments:
+ if arg == 'gid':
+ options['gid'] = int(utils.SERVER_GROUP)
+ elif arg == 'sid':
+ options['sid'] = int(self.server_information['server_id'])
+ elif arg == 'did':
+ options['did'] = int(self.server_information['db_id'])
+ elif arg == 'scid':
+ options['scid'] = int(self.server_information['schema_id'])
+ else:
+ if object_id is not None:
+ options[arg] = int(object_id)
+
+ with self.app.test_request_context():
+ object_url = url_for(rule.endpoint, **options)
+
+ return object_url
+
+ def execute_test_case(self, scenarios):
+ """
+ This function will run the test cases for specific module.
+
+ :param module_name: Name of the module
+ :param scenarios: List of scenarios
+ :return:
+ """
+ object_id = None
+ for scenario in scenarios:
+ print(scenario['name'])
+
+ if 'type' in scenario and scenario['type'] == 'create':
+ # Get the url and create the specific node.
+ create_url = self.get_url(scenario['endpoint'])
+ response = self.tester.post(create_url,
+ data=json.dumps(scenario['data']),
+ content_type='html/json')
+ self.assertEquals(response.status_code, 200)
+ resp_data = json.loads(response.data.decode('utf8'))
+ object_id = resp_data['node']['_id']
+
+ # Compare the reverse engineering SQL
+ self.check_re_sql(scenario, object_id)
+ elif 'type' in scenario and scenario['type'] == 'alter':
+ # Get the url and create the specific node.
+ alter_url = self.get_url(scenario['endpoint'], object_id)
+ response = self.tester.put(alter_url,
+ data=json.dumps(scenario['data']),
+ follow_redirects=True)
+ self.assertEquals(response.status_code, 200)
+ resp_data = json.loads(response.data.decode('utf8'))
+ object_id = resp_data['node']['_id']
+
+ # Compare the reverse engineering SQL
+ self.check_re_sql(scenario, object_id)
+ elif 'type' in scenario and scenario['type'] == 'delete':
+ # Get the delete url and delete the object created above.
+ delete_url = self.get_url(scenario['endpoint'], object_id)
+ delete_response = self.tester.delete(delete_url,
+ follow_redirects=True)
+ self.assertEquals(delete_response.status_code, 200)
+
+ def get_test_folder(self, module_path):
+ """
+ This function will get the appropriate test folder based on
+ server version and their existence.
+
+ :param module_path: Path of the module to be tested.
+ :return:
+ """
+ # Join the application path and the module path
+ absolute_path = os.path.join(self.apppath, module_path)
+ # Iterate the version mapping directories.
+ for version_mapping in get_version_mapping_directories(
+ self.server['type']):
+ if version_mapping['number'] > \
+ self.server_information['server_version']:
+ continue
+
+ complete_path = os.path.join(absolute_path, 'tests',
+ version_mapping['name'])
+
+ if os.path.exists(complete_path):
+ return True, complete_path
+
+ return False, None
+
+ def check_re_sql(self, scenario, object_id):
+ """
+ This function is used to get the reverse engineering SQL.
+ :param scenario:
+ :param object_id:
+ :return:
+ """
+ sql_url = self.get_url(scenario['sql_endpoint'], object_id)
+ response = self.tester.get(sql_url)
+ self.assertEquals(response.status_code, 200)
+ resp_sql = response.data.decode('unicode_escape')
+
+ # Remove first and last double quotes
+ if resp_sql.startswith('"') and resp_sql.endswith('"'):
+ resp_sql = resp_sql[1:-1]
+
+ # Check if expected sql is given in JSON file or path of the output
+ # file is given
+ if 'expected_sql_file' in scenario:
+ output_file = os.path.join(self.test_folder,
+ scenario['expected_sql_file'])
+
+ if os.path.exists(output_file):
+ fp = open(output_file, "r")
+ # Used rstrip to remove trailing \n
+ sql = fp.read().rstrip()
+ self.assertEquals(sql, resp_sql)
+ else:
+ self.assertFalse("Expected SQL File not found")
+ elif 'expected_sql' in scenario:
+ self.assertEquals(scenario['expected_sql'], resp_sql)
diff --git a/web/regression/runtests.py b/web/regression/runtests.py
index 4f813145..f38faacd 100644
--- a/web/regression/runtests.py
+++ b/web/regression/runtests.py
@@ -259,6 +259,11 @@ def get_test_modules(arguments):
exclude_pkgs,
for_modules)
+ # Load the reverse engineering sql test module
+ TestsGeneratorRegistry.load_generators(
+ 'regression.re_sql', exclude_pkgs,
+ set_module_list=False)
+
# Sort module list so that test suite executes the test cases sequentially
module_list = TestsGeneratorRegistry.registry.items()
module_list = sorted(module_list, key=lambda module_tuple: module_tuple[0])
@@ -438,6 +443,9 @@ if __name__ == '__main__':
server['sslmode']
)
+ # Add the server version in server information
+ server_information['server_version'] = connection.server_version
+
# Drop the database if already exists.
test_utils.drop_database(connection, test_db_name)
# Create database
view thread (10+ 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]: Feature #4202 Implement new framework to test Reverse Engineering SQL
In-Reply-To: <CANxoLDe_FGeAnGy9Vo22VBKRfd8-=yb4AbZcE4AbyR-tUnYgBg@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