Re: pgAdmin 4 commit: Support for external tables in GPDB. Fixes #3168

Hi Joao,

I ran the testsuite in windows 10 with Python 3.4 and it fails for external
tables. Linux it is working fine. Let me know if I miss anything.

Please check the below logs.

python runtests.py --pkg browser --exclude feature_tests

########

======================================================================
ERROR: runTest
(pgadmin.browser.server_groups.servers.databases.schemas.tables.tests.test_template_create.TestTemplateCreate)
When rendering GreenPlum 5.3 template, when no distribution is present,
when no primary key is present, it returns "DISTRIBUTED RANDOMLY"
----------------------------------------------------------------------
Traceback (most recent call last):
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\schemas\tables\tests\test_template_create.py",
line 99, in runTest
self.template_path, **self.input_parameters)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 133, in render_template
return
_render(ctx.app.jinja_env.get_or_select_template(template_name_or_list),
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 830, in get_or_select_template
return self.get_template(template_name_or_list, parent, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 791, in get_template
return self._load_template(name, self.make_globals(globals))
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 765, in _load_template
template = self.loader.load(self, name, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\loaders.py",
line 113, in load
source, filename, uptodate = self.get_source(environment, name)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 57, in get_source
return self._get_source_fast(environment, template)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 85, in _get_source_fast
raise TemplateNotFound(template)
jinja2.exceptions.TemplateNotFound: table\sql\gpdb_5.0_plus\create.sql

======================================================================
ERROR: runTest
(pgadmin.browser.server_groups.servers.databases.schemas.tables.tests.test_template_create.TestTemplateCreate)
When rendering GreenPlum 5.3 template, when no distribution is present,
when primary key is present, it returns "DISTRIBUTED BY (attr_primary_key)"
----------------------------------------------------------------------
Traceback (most recent call last):
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\schemas\tables\tests\test_template_create.py",
line 99, in runTest
self.template_path, **self.input_parameters)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 133, in render_template
return
_render(ctx.app.jinja_env.get_or_select_template(template_name_or_list),
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 830, in get_or_select_template
return self.get_template(template_name_or_list, parent, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 791, in get_template
return self._load_template(name, self.make_globals(globals))
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 765, in _load_template
template = self.loader.load(self, name, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\loaders.py",
line 113, in load
source, filename, uptodate = self.get_source(environment, name)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 57, in get_source
return self._get_source_fast(environment, template)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 85, in _get_source_fast
raise TemplateNotFound(template)
jinja2.exceptions.TemplateNotFound: table\sql\gpdb_5.0_plus\create.sql

======================================================================
ERROR: runTest
(pgadmin.browser.server_groups.servers.databases.schemas.tables.tests.test_template_create.TestTemplateCreate)
When rendering GreenPlum 5.3 template, when distribution is present, it
returns "DISTRIBUTED BY (attr1, attr2, attr4)"
----------------------------------------------------------------------
Traceback (most recent call last):
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\schemas\tables\tests\test_template_create.py",
line 99, in runTest
self.template_path, **self.input_parameters)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 133, in render_template
return
_render(ctx.app.jinja_env.get_or_select_template(template_name_or_list),
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 830, in get_or_select_template
return self.get_template(template_name_or_list, parent, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 791, in get_template
return self._load_template(name, self.make_globals(globals))
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\environment.py",
line 765, in _load_template
template = self.loader.load(self, name, globals)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\jinja2\loaders.py",
line 113, in load
source, filename, uptodate = self.get_source(environment, name)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 57, in get_source
return self._get_source_fast(environment, template)
File
"C:\Projects\venv_pgadmin4_py_3_4\lib\site-packages\flask\templating.py",
line 85, in _get_source_fast
raise TemplateNotFound(template)
jinja2.exceptions.TemplateNotFound: table\sql\gpdb_5.0_plus\create.sql

======================================================================
ERROR: runTest
(pgadmin.browser.tests.test_change_password.ChangePasswordTestCase)
TestCase for Changing Valid_Password
----------------------------------------------------------------------
Traceback (most recent call last):
File
"C:\Projects\pgadmin4\web\pgadmin\browser\tests\test_change_password.py",
line 91, in runTest
user_id = json.loads(response.data.decode('utf-8'))['id']
KeyError: 'id'

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#nodes When retrieving the nodes and the database does not have external
tables, it return no child nodes and status 200
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 314, in runTest
self.__test_nodes()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 367, in __test_nodes
self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template('sql/#gpdb#80323#/list.sql')
Actual call: render_template('sql/#gpdb#80323#\\list.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#nodes When retrieving the nodes and an error happens while executing the
query, it return an internal server error and status 500
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 314, in runTest
self.__test_nodes()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 367, in __test_nodes
self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template('sql/#gpdb#80323#/list.sql')
Actual call: render_template('sql/#gpdb#80323#\\list.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#nodes When retrieving the nodes and the database has 2 external tables, it
return 2 child nodes and status 200
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 314, in runTest
self.__test_nodes()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 367, in __test_nodes
self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template('sql/#gpdb#80323#/list.sql')
Actual call: render_template('sql/#gpdb#80323#\\list.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#node When retrieving the information about 1 external table and an error
happens while executing the query, it return an internal server error and
status 500
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 316, in runTest
self.__test_node()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 393, in __test_node
**self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#/node.sql')
Actual call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#\\node.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#node When retrieving the information about 1 external table and table does
not exist, it return an error message and status 404
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 316, in runTest
self.__test_node()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 393, in __test_node
**self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#/node.sql')
Actual call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#\\node.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#nodes When retrieving the information about 1 external table and the table
exists, it return external node information and status 200
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 316, in runTest
self.__test_node()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 393, in __test_node
**self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#/node.sql')
Actual call: render_template(external_table_id=11,
template_name_or_list='sql/#gpdb#80323#\\node.sql')

======================================================================
FAIL: runTest
(pgadmin.browser.server_groups.servers.databases.external_tables.tests.test_external_tables_view.TestExternalTablesView)
#properties When retrieving the properties of a external table and the
table exists, it return the properties and status 200
----------------------------------------------------------------------
Traceback (most recent call last):
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 320, in runTest
self.__test_properties()
File "C:\Python34\Lib\unittest\mock.py", line 1142, in patched
return func(*args, **keywargs)
File
"C:\Projects\pgadmin4\web\pgadmin\browser\server_groups\servers\databases\external_tables\tests\test_external_tables_view.py",
line 419, in __test_properties
**self.expect_render_template_called_with
File "C:\Python34\Lib\unittest\mock.py", line 777, in assert_called_with
raise AssertionError(_error_message()) from cause
AssertionError: Expected call:
render_template(template_name_or_list='sql/#gpdb#80323#/get_table_information.sql',
table_oid=11)
Actual call:
render_template(template_name_or_list='sql/#gpdb#80323#\\get_table_information.sql',
table_oid=11)

----------------------------------------------------------------------
Ran 256 tests in 77.123s

FAILED (failures=7, errors=4, skipped=25)

======================================================================
Test Result Summary
======================================================================

PostgreSQL 9.6:

234 tests passed
3 tests failed:
TestExternalTablesView (#node When retrieving the
information about 1 external table and table does not exist, it return an
error message and status 404,
#node When retrieving the
information about 1 external table and an error happens while executing the
query, it return an internal server error and status 500,
#nodes When retrieving the nodes
and the database has 2 external tables, it return 2 child nodes and status
200,
#nodes When retrieving the
information about 1 external table and the table exists, it return external
node information and status 200,
#nodes When retrieving the nodes
and an error happens while executing the query, it return an internal
server error and status 500,
#properties When retrieving the
properties of a external table and the table exists, it return the
properties and status 200,
#nodes When retrieving the nodes
and the database does not have external tables, it return no child nodes
and status 200)
ChangePasswordTestCase (TestCase for Changing
Valid_Password)
TestTemplateCreate (When rendering GreenPlum 5.3 template,
when no distribution is present, when no primary key is present, it returns
"DISTRIBUTED RANDOMLY",
When rendering GreenPlum 5.3
template, when distribution is present, it returns "DISTRIBUTED BY (attr1,
attr2, attr4)",
When rendering GreenPlum 5.3
template, when no distribution is present, when primary key is present, it
returns "DISTRIBUTED BY (attr_primary_key)")
19 tests skipped:
ResourceGroupsGetTestCase (Get resource groups)
TableSpaceDeleteTestCase (Check Tablespace Node)
ResourceGroupsDeleteTestCase (Delete resource groups)
SynonymDeleteTestCase (Fetch synonym Node URL)
TestSSLConnection (Test for SSL connection)
PackageAddTestCase (Fetch Package Node URL)
SynonymPutTestCase (Fetch synonym Node URL)
TablespaceGetTestCase (Check Tablespace Node)
SynonymAddTestCase (Default Node URL)
PackagePutTestCase (Fetch Package Node URL)
TableAddTestCase (Create Range partitioned table with 2
partitions,
Create List partitioned table with
2 partitions)
PackageGetTestCase (Fetch Package Node URL)
PackageDeleteTestCase (Fetch Package Node URL)
TableSpaceAddTestCase (Check Tablespace Node)
ResourceGroupsAddTestCase (Add resource groups)
SynonymGetTestCase (Fetch synonym Node URL)
ResourceGroupsPutTestCase (Put resource groups)
TableSpaceUpdateTestCase (Check Tablespace Node)
TableUpdateTestCase (Attach partition to existing range
partitioned table,
Detach partition from existing
range partitioned table,
Create partitions of existing range
partitioned table,
Detach partition from existing list
partitioned table,
Create partitions of existing list
partitioned table,
Attach partition to existing list
partitioned table)

======================================================================

########

Thanks,
Neel Patel

On Fri, Mar 2, 2018 at 10:19 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Support for external tables in GPDB. Fixes #3168
>
> Branch
> ------
> master
>
> Details
> -------
> https://git.postgresql.org/gitweb?p=pgadmin4.git;a=commitdiff;h=
> 427314cfdfeb96c3a7835eab5a4d638903bc6dc3
> Author: Joao Pedro De Almeida Pereira <jdealmeidapereira(at)pivotal(dot)io>
>
> Modified Files
> --------------
> .../servers/databases/external_tables/__init__.py | 275 +++++++++++++
> .../databases/external_tables/actions/__init__.py | 0
> .../external_tables/actions/get_all_nodes.py | 4 +
> .../databases/external_tables/mapping_utils.py | 165 ++++++++
> .../databases/external_tables/properties.py | 78 ++++
> .../external_tables/reverse_engineer_ddl.py | 69 ++++
> .../static/img/coll-external_table.svg | 1 +
> .../external_tables/static/img/external_table.svg | 1 +
> .../templates/sql/gpdb_5.0_plus/create.sql | 60 +++
> .../templates/sql/gpdb_5.0_plus/get_columns.sql | 12 +
> .../sql/gpdb_5.0_plus/get_table_information.sql | 22 ++
> .../templates/sql/gpdb_5.0_plus/list.sql | 6 +
> .../templates/sql/gpdb_5.0_plus/node.sql | 5 +
> .../databases/external_tables/tests/__init__.py | 0
> .../tests/test_external_tables_module.py | 99 +++++
> .../tests/test_external_tables_view.py | 428
> +++++++++++++++++++++
> .../external_tables/tests/test_mapping_utils.py | 375
> ++++++++++++++++++
> .../external_tables/tests/test_properties.py | 156 ++++++++
> .../tests/test_reverse_engineer_ddl.py | 261 +++++++++++++
> .../tests/test_sql_template_create_integration.py | 0
> .../templates/table/sql/gpdb_5.0_plus/nodes.sql | 1 +
> web/pgadmin/static/bundle/browser.js | 1 +
> .../databases/external_tables/external_tables.js | 88 +++++
> .../servers/databases/external_tables/index.js | 18 +
> web/pgadmin/tools/sqleditor/__init__.py | 1 +
> .../external_tables/external_tables_spec.js | 56 +++
> web/webpack.config.js | 9 +
> web/webpack.shim.js | 3 +
> 28 files changed, 2194 insertions(+)
>
>