public inbox for [email protected]
help / color / mirror / Atom feedFrom: Joao Pedro De Almeida Pereira <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: [patch] Dependents and Dependencies in GreenPlum
Date: Fri, 21 Apr 2017 15:42:33 -0400
Message-ID: <CAE+jjam5JoMt9+Ue-O_qpgEJO_wAGJfGoaLCJg-1VzVc+AqB_w@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi Hackers,
We found out that when you are connected to a GreenPlum database and try to
get Dependents and Dependencies of an object the application was returning
a SQL error.
This patch splits the SQL query used to retrieve the Dependents,
Dependencies, and Roles SQL file into multiple versioned files.
Add Unit Tests for each file.
Also added __init__.py files to other test directories to run the tests in
them.
Add ORDER BY into Copy Selection Feature test to ensure the results are
retrieved always in the same order
Renamed the Scenario of the xss_checks_pgadmin_debugger_test and skip it
for versions less than 9.1
Thanks
Joao & Sarah
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] 0001-Fix-error-while-checking-Dependents-and-Dependencies.patch (35.1K, 3-0001-Fix-error-while-checking-Dependents-and-Dependencies.patch)
download | inline diff:
From 0c21ff7a81b4421856a1bf1c7a495b55bd3ad1a6 Mon Sep 17 00:00:00 2001
From: Joao Pereira and Oliver Switzer <[email protected]>
Date: Tue, 18 Apr 2017 16:08:28 -0400
Subject: [PATCH] Fix error while checking Dependents and Dependencies of
object in GreenPlum: - Make SQL template UT visible to runtest - Add SQL to
retrieve dependencies for GreenPlum - Extract Role Dependencies from single
dependents file - Split template for dependents SQL - Add 'SET LOCAL
join_collapse_limit=8;' to dependency/dependents to make them faster. Only
caveat is that this will remain unchanged for the session - Add more
expectations to role dependencies - Specify order of query in
copy_selected_query_results_feature_test to make tests pass - GreenPlum
and Postgres order these results differently by default without an order
clause specified. - Skip XSS test for GreenPlum
---
.../tables/templates/column/sql/__init__.py | 6 ++
.../tables/templates/column/sql/tests/__init__.py | 6 ++
.../templates/column/sql/tests/test_column_acl.py | 4 ++
.../column/sql/tests/test_column_properties.py | 4 ++
.../schemas/tables/templates/trigger/__init__.py | 6 ++
.../trigger/sql/tests/test_trigger_get_oid.py | 4 +-
.../trigger/sql/tests/test_trigger_nodes.py | 3 +
.../servers/templates/depends/__init__.py | 6 ++
.../depends/sql/9.1_plus/dependencies.sql | 45 +++++++++++++++
.../templates/depends/sql/9.1_plus/dependents.sql | 44 +++++++++++++++
.../servers/templates/depends/sql/__init__.py | 6 ++
.../templates/depends/sql/default/dependencies.sql | 42 ++++++++++++++
.../templates/depends/sql/default/dependents.sql | 66 +---------------------
.../depends/sql/default/role_dependencies.sql | 4 ++
.../templates/depends/sql/tests/__init__.py | 6 ++
.../depends/sql/tests/test_dependencies.py | 58 +++++++++++++++++++
.../templates/depends/sql/tests/test_dependents.py | 58 +++++++++++++++++++
.../depends/sql/tests/test_role_dependencies.py | 54 ++++++++++++++++++
web/pgadmin/browser/utils.py | 10 ++--
.../copy_selected_query_results_feature_test.py | 2 +-
.../xss_checks_pgadmin_debugger_test.py | 6 +-
21 files changed, 369 insertions(+), 71 deletions(-)
create mode 100644 web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependencies.sql
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependents.sql
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependencies.sql
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/role_dependencies.sql
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/__init__.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependencies.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependents.py
create mode 100644 web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_role_dependencies.py
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/__init__.py
new file mode 100644
index 00000000..9e55d2b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/__init__.py
new file mode 100644
index 00000000..9e55d2b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_acl.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_acl.py
index 7be945c7..68bccfc8 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_acl.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_acl.py
@@ -13,6 +13,10 @@ if sys.version_info[0] >= 3:
class TestColumnAcl(BaseTestGenerator):
+ scenarios = [
+ # Fetching default URL for schema node.
+ ('Test Column ACL SQL file', dict())
+ ]
def runTest(self):
""" When there are no permissions on the column, it returns an empty result """
with test_utils.Database(self.server) as (connection, database_name):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_properties.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_properties.py
index b1984bda..3c3457b6 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_properties.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/column/sql/tests/test_column_properties.py
@@ -22,6 +22,10 @@ if sys.version_info[0] >= 3:
class TestColumnProperties(BaseTestGenerator):
+ scenarios = [
+ # Fetching default URL for schema node.
+ ('Test Column Properties SQL file', dict())
+ ]
def runTest(self):
""" This tests that column properties are returned"""
with test_utils.Database(self.server) as (connection, database_name):
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/__init__.py
new file mode 100644
index 00000000..9e55d2b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_get_oid.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_get_oid.py
index f2b637ee..25ce845d 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_get_oid.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_get_oid.py
@@ -24,8 +24,10 @@ if sys.version_info[0] >= 3:
class TestTriggerGetOid(BaseTestGenerator):
+ scenarios = [
+ ('Test Trigger to retrieve OID SQL file', dict())
+ ]
def runTest(self):
- """ When there are no permissions on the column, it returns an empty result """
with test_utils.Database(self.server) as (connection, database_name):
test_utils.create_table(self.server, database_name, "test_table")
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_nodes.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_nodes.py
index b23bf60a..b82d85d4 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_nodes.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/trigger/sql/tests/test_trigger_nodes.py
@@ -22,6 +22,9 @@ if sys.version_info[0] >= 3:
class TestTriggerNodes(BaseTestGenerator):
+ scenarios = [
+ ('Test Trigger Nodes SQL file', dict())
+ ]
def runTest(self):
""" When there are no triggers, it returns an empty result """
with test_utils.Database(self.server) as (connection, database_name):
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/__init__.py b/web/pgadmin/browser/server_groups/servers/templates/depends/__init__.py
new file mode 100644
index 00000000..9e55d2b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependencies.sql
new file mode 100644
index 00000000..e5bb8cb6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependencies.sql
@@ -0,0 +1,45 @@
+SELECT DISTINCT dep.deptype, dep.refclassid, cl.relkind, ad.adbin, ad.adsrc,
+ CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.refobjsubid::character varying, '')
+ WHEN tg.oid IS NOT NULL THEN 'T'::text
+ WHEN ty.oid IS NOT NULL AND ty.typbasetype = 0 THEN 'y'::text
+ WHEN ty.oid IS NOT NULL AND ty.typbasetype != 0 THEN 'd'::text
+ WHEN ns.oid IS NOT NULL THEN 'n'::text
+ WHEN pr.oid IS NOT NULL THEN 'p'::text
+ WHEN la.oid IS NOT NULL THEN 'l'::text
+ WHEN rw.oid IS NOT NULL THEN 'R'::text
+ WHEN co.oid IS NOT NULL THEN 'C'::text || contype
+ WHEN ad.oid IS NOT NULL THEN 'A'::text
+ WHEN fs.oid IS NOT NULL THEN 'F'::text
+ WHEN fdw.oid IS NOT NULL THEN 'f'::text
+ ELSE ''
+ END AS type,
+ COALESCE(coc.relname, clrw.relname) AS ownertable,
+ CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
+ ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname)
+ END AS refname,
+ COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
+FROM pg_depend dep
+LEFT JOIN pg_class cl ON dep.refobjid=cl.oid
+LEFT JOIN pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
+LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
+LEFT JOIN pg_proc pr ON dep.refobjid=pr.oid
+LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
+LEFT JOIN pg_trigger tg ON dep.refobjid=tg.oid
+LEFT JOIN pg_type ty ON dep.refobjid=ty.oid
+LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
+LEFT JOIN pg_constraint co ON dep.refobjid=co.oid
+LEFT JOIN pg_class coc ON co.conrelid=coc.oid
+LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
+LEFT JOIN pg_rewrite rw ON dep.refobjid=rw.oid
+LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
+LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
+LEFT JOIN pg_language la ON dep.refobjid=la.oid
+LEFT JOIN pg_namespace ns ON dep.refobjid=ns.oid
+LEFT JOIN pg_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum
+LEFT JOIN pg_foreign_server fs ON fs.oid=dep.refobjid
+LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.refobjid
+{{where_clause}} AND
+refclassid IN ( SELECT oid FROM pg_class WHERE relname IN
+ ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace',
+ 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
+ORDER BY refclassid, cl.relkind
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependents.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependents.sql
new file mode 100644
index 00000000..1281672f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/9.1_plus/dependents.sql
@@ -0,0 +1,44 @@
+SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc,
+ CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '')
+ WHEN tg.oid IS NOT NULL THEN 'T'::text
+ WHEN ty.oid IS NOT NULL THEN 'y'::text
+ WHEN ns.oid IS NOT NULL THEN 'n'::text
+ WHEN pr.oid IS NOT NULL THEN 'p'::text
+ WHEN la.oid IS NOT NULL THEN 'l'::text
+ WHEN rw.oid IS NOT NULL THEN 'R'::text
+ WHEN co.oid IS NOT NULL THEN 'C'::text || contype
+ WHEN ad.oid IS NOT NULL THEN 'A'::text
+ WHEN fs.oid IS NOT NULL THEN 'F'::text
+ WHEN fdw.oid IS NOT NULL THEN 'f'::text
+ ELSE ''
+ END AS type,
+ COALESCE(coc.relname, clrw.relname) AS ownertable,
+ CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
+ ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname)
+ END AS refname,
+ COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
+FROM pg_depend dep
+LEFT JOIN pg_class cl ON dep.objid=cl.oid
+LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum
+LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
+LEFT JOIN pg_proc pr ON dep.objid=pr.oid
+LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
+LEFT JOIN pg_trigger tg ON dep.objid=tg.oid
+LEFT JOIN pg_type ty ON dep.objid=ty.oid
+LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
+LEFT JOIN pg_constraint co ON dep.objid=co.oid
+LEFT JOIN pg_class coc ON co.conrelid=coc.oid
+LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
+LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid
+LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
+LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
+LEFT JOIN pg_language la ON dep.objid=la.oid
+LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
+LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid
+LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid
+LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid
+{{where_clause}} AND
+classid IN ( SELECT oid FROM pg_class WHERE relname IN
+ ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace',
+ 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
+ORDER BY classid, cl.relkind
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/__init__.py b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/__init__.py
new file mode 100644
index 00000000..9e55d2b2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependencies.sql
new file mode 100644
index 00000000..aae5982e
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependencies.sql
@@ -0,0 +1,42 @@
+SET LOCAL join_collapse_limit=8;
+SELECT DISTINCT dep.deptype, dep.refclassid, cl.relkind, ad.adbin, ad.adsrc,
+ CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.refobjsubid::character varying, '')
+ WHEN tg.oid IS NOT NULL THEN 'T'::text
+ WHEN ty.oid IS NOT NULL AND ty.typbasetype = 0 THEN 'y'::text
+ WHEN ty.oid IS NOT NULL AND ty.typbasetype != 0 THEN 'd'::text
+ WHEN ns.oid IS NOT NULL THEN 'n'::text
+ WHEN pr.oid IS NOT NULL THEN 'p'::text
+ WHEN la.oid IS NOT NULL THEN 'l'::text
+ WHEN rw.oid IS NOT NULL THEN 'R'::text
+ WHEN co.oid IS NOT NULL THEN 'C'::text || contype
+ WHEN ad.oid IS NOT NULL THEN 'A'::text
+ ELSE ''
+ END AS type,
+ COALESCE(coc.relname, clrw.relname) AS ownertable,
+ CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
+ ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname)
+ END AS refname,
+ COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
+FROM pg_depend dep
+LEFT JOIN pg_class cl ON dep.refobjid=cl.oid
+LEFT JOIN pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
+LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
+LEFT JOIN pg_proc pr ON dep.refobjid=pr.oid
+LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
+LEFT JOIN pg_trigger tg ON dep.refobjid=tg.oid
+LEFT JOIN pg_type ty ON dep.refobjid=ty.oid
+LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
+LEFT JOIN pg_constraint co ON dep.refobjid=co.oid
+LEFT JOIN pg_class coc ON co.conrelid=coc.oid
+LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
+LEFT JOIN pg_rewrite rw ON dep.refobjid=rw.oid
+LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
+LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
+LEFT JOIN pg_language la ON dep.refobjid=la.oid
+LEFT JOIN pg_namespace ns ON dep.refobjid=ns.oid
+LEFT JOIN pg_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum
+{{where_clause}} AND
+refclassid IN ( SELECT oid FROM pg_class WHERE relname IN
+ ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace',
+ 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
+ORDER BY refclassid, cl.relkind
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependents.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependents.sql
index e2ba3d6b..2fe2ef3e 100644
--- a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependents.sql
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/dependents.sql
@@ -1,59 +1,4 @@
-{% if fetch_dependencies %}
-SELECT DISTINCT dep.deptype, dep.refclassid, cl.relkind, ad.adbin, ad.adsrc,
- CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.refobjsubid::character varying, '')
- WHEN tg.oid IS NOT NULL THEN 'T'::text
- WHEN ty.oid IS NOT NULL AND ty.typbasetype = 0 THEN 'y'::text
- WHEN ty.oid IS NOT NULL AND ty.typbasetype != 0 THEN 'd'::text
- WHEN ns.oid IS NOT NULL THEN 'n'::text
- WHEN pr.oid IS NOT NULL THEN 'p'::text
- WHEN la.oid IS NOT NULL THEN 'l'::text
- WHEN rw.oid IS NOT NULL THEN 'R'::text
- WHEN co.oid IS NOT NULL THEN 'C'::text || contype
- WHEN ad.oid IS NOT NULL THEN 'A'::text
- WHEN fs.oid IS NOT NULL THEN 'F'::text
- WHEN fdw.oid IS NOT NULL THEN 'f'::text
- ELSE ''
- END AS type,
- COALESCE(coc.relname, clrw.relname) AS ownertable,
- CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
- ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname)
- END AS refname,
- COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
-FROM pg_depend dep
-LEFT JOIN pg_class cl ON dep.refobjid=cl.oid
-LEFT JOIN pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum
-LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid
-LEFT JOIN pg_proc pr ON dep.refobjid=pr.oid
-LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid
-LEFT JOIN pg_trigger tg ON dep.refobjid=tg.oid
-LEFT JOIN pg_type ty ON dep.refobjid=ty.oid
-LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid
-LEFT JOIN pg_constraint co ON dep.refobjid=co.oid
-LEFT JOIN pg_class coc ON co.conrelid=coc.oid
-LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid
-LEFT JOIN pg_rewrite rw ON dep.refobjid=rw.oid
-LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class
-LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
-LEFT JOIN pg_language la ON dep.refobjid=la.oid
-LEFT JOIN pg_namespace ns ON dep.refobjid=ns.oid
-LEFT JOIN pg_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum
-LEFT JOIN pg_foreign_server fs ON fs.oid=dep.refobjid
-LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.refobjid
-{{where_clause}} AND
-refclassid IN ( SELECT oid FROM pg_class WHERE relname IN
- ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace',
- 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
-ORDER BY refclassid, cl.relkind
-{% endif %}
-
-{% if fetch_role_dependencies %}
-SELECT rolname AS refname, refclassid, deptype
-FROM pg_shdepend dep
-LEFT JOIN pg_roles r ON refclassid=1260 AND refobjid=r.oid
-{{where_clause}} ORDER BY 1
-{% endif %}
-
-{% if fetch_dependents %}
+SET LOCAL join_collapse_limit=8;
SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc,
CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '')
WHEN tg.oid IS NOT NULL THEN 'T'::text
@@ -64,13 +9,11 @@ SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, ad.adsrc,
WHEN rw.oid IS NOT NULL THEN 'R'::text
WHEN co.oid IS NOT NULL THEN 'C'::text || contype
WHEN ad.oid IS NOT NULL THEN 'A'::text
- WHEN fs.oid IS NOT NULL THEN 'F'::text
- WHEN fdw.oid IS NOT NULL THEN 'f'::text
ELSE ''
END AS type,
COALESCE(coc.relname, clrw.relname) AS ownertable,
CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || '.' || att.attname
- ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname)
+ ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname)
END AS refname,
COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname
FROM pg_depend dep
@@ -91,11 +34,8 @@ LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid
LEFT JOIN pg_language la ON dep.objid=la.oid
LEFT JOIN pg_namespace ns ON dep.objid=ns.oid
LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid
-LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid
-LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid
{{where_clause}} AND
classid IN ( SELECT oid FROM pg_class WHERE relname IN
('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace',
'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
-ORDER BY classid, cl.relkind
-{% endif %}
+ORDER BY classid, cl.relkind;
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/role_dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/role_dependencies.sql
new file mode 100644
index 00000000..25fc3f85
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/default/role_dependencies.sql
@@ -0,0 +1,4 @@
+SELECT rolname AS refname, refclassid, deptype
+FROM pg_shdepend dep
+LEFT JOIN pg_roles r ON refclassid=1260 AND refobjid=r.oid
+{{where_clause}} ORDER BY 1
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/__init__.py b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/__init__.py
new file mode 100644
index 00000000..f64f8973
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/__init__.py
@@ -0,0 +1,6 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependencies.py b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependencies.py
new file mode 100644
index 00000000..ce1f97d9
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependencies.py
@@ -0,0 +1,58 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+import os
+import sys
+
+from pgadmin.utils.driver import DriverRegistry
+from regression.python_test_utils.template_helper import file_as_template
+
+DriverRegistry.load_drivers()
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils
+
+if sys.version_info[0] >= 3:
+ long = int
+
+
+class TestDependencies(BaseTestGenerator):
+ scenarios = [
+ # Fetching default URL for schema node.
+ ('Test dependencies SQL file', dict())
+ ]
+ def runTest(self):
+ """ When there are no permissions on the column, it returns an empty result """
+ with test_utils.Database(self.server) as (connection, database_name):
+ test_utils.create_table(self.server, database_name, "test_table")
+
+ cursor = connection.cursor()
+ cursor.execute("SELECT pg_class.oid as table_id "
+ "FROM pg_class "
+ "where pg_class.relname='test_table'")
+ table_id = cursor.fetchone()
+
+ if connection.server_version < 90100:
+ self.versions_to_test = ['default']
+ else:
+ self.versions_to_test = ['9.1_plus']
+
+ for version in self.versions_to_test:
+ template_file = os.path.join(os.path.dirname(__file__), "..", version, "dependencies.sql")
+ template = file_as_template(template_file)
+
+ sql = template.render(where_clause="WHERE dep.objid=%s::oid" % table_id)
+
+ cursor = connection.cursor()
+ cursor.execute(sql)
+ fetch_result = cursor.fetchall()
+ self.assertEqual(1, len(fetch_result))
+
+ first_row = {}
+ for index, description in enumerate(cursor.description):
+ first_row[description.name] = fetch_result[0][index]
+
+ self.assertEqual('n', first_row["deptype"])
+ self.assertEqual('public', first_row["refname"])
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependents.py b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependents.py
new file mode 100644
index 00000000..0bd4c960
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_dependents.py
@@ -0,0 +1,58 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+import os
+import sys
+
+from pgadmin.utils.driver import DriverRegistry
+from regression.python_test_utils.template_helper import file_as_template
+
+DriverRegistry.load_drivers()
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils
+
+if sys.version_info[0] >= 3:
+ long = int
+
+
+class TestDependencies(BaseTestGenerator):
+ scenarios = [
+ # Fetching default URL for schema node.
+ ('Test dependencies SQL file', dict())
+ ]
+ def runTest(self):
+ """ When there are no permissions on the column, it returns an empty result """
+ with test_utils.Database(self.server) as (connection, database_name):
+ test_utils.create_table(self.server, database_name, "test_table")
+
+ cursor = connection.cursor()
+ cursor.execute("SELECT pg_class.oid as table_id "
+ "FROM pg_class "
+ "where pg_class.relname='test_table'")
+ table_id = cursor.fetchone()
+
+ if connection.server_version < 90100:
+ self.versions_to_test = ['default']
+ else:
+ self.versions_to_test = ['9.1_plus']
+
+ for version in self.versions_to_test:
+ template_file = os.path.join(os.path.dirname(__file__), "..", version, "dependents.sql")
+ template = file_as_template(template_file)
+
+ sql = template.render(where_clause="WHERE dep.objid=%s::oid" % table_id)
+
+ cursor = connection.cursor()
+ cursor.execute(sql)
+ fetch_result = cursor.fetchall()
+ self.assertEqual(1, len(fetch_result))
+
+ first_row = {}
+ for index, description in enumerate(cursor.description):
+ first_row[description.name] = fetch_result[0][index]
+
+ self.assertEqual('n', first_row["deptype"])
+ self.assertEqual('test_table', first_row["refname"])
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_role_dependencies.py b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_role_dependencies.py
new file mode 100644
index 00000000..7c9a0b03
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/tests/test_role_dependencies.py
@@ -0,0 +1,54 @@
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+import os
+import sys
+
+from pgadmin.utils.driver import DriverRegistry
+from regression.python_test_utils.template_helper import file_as_template
+
+DriverRegistry.load_drivers()
+from pgadmin.utils.route import BaseTestGenerator
+from regression.python_test_utils import test_utils
+
+if sys.version_info[0] >= 3:
+ long = int
+
+
+class TestRoleDependencies(BaseTestGenerator):
+ scenarios = [
+ # Fetching default URL for schema node.
+ ('Test Role Dependencies SQL file', dict())
+ ]
+ def runTest(self):
+ """ When there are no Role Dependencies on the table, it returns an empty result """
+ with test_utils.Database(self.server) as (connection, database_name):
+ test_utils.create_table(self.server, database_name, "test_table")
+
+ cursor = connection.cursor()
+ cursor.execute("SELECT pg_class.oid as table_id "
+ "FROM pg_class "
+ "WHERE pg_class.relname='test_table'")
+ table_id = cursor.fetchone()
+
+ self.versions_to_test = ['default']
+
+ for version in self.versions_to_test:
+ template_file = os.path.join(os.path.dirname(__file__), "..", version, "role_dependencies.sql")
+ template = file_as_template(template_file)
+
+ sql = template.render(where_clause="WHERE dep.objid=%s::oid" % table_id)
+
+ cursor = connection.cursor()
+ cursor.execute(sql)
+ fetch_result = cursor.fetchall()
+ self.assertEqual(1, len(fetch_result))
+
+ first_row = {}
+ for index, description in enumerate(cursor.description):
+ first_row[description.name] = fetch_result[0][index]
+
+ self.assertEqual('o', first_row["deptype"])
diff --git a/web/pgadmin/browser/utils.py b/web/pgadmin/browser/utils.py
index d2899875..708bfdfb 100644
--- a/web/pgadmin/browser/utils.py
+++ b/web/pgadmin/browser/utils.py
@@ -353,15 +353,15 @@ class PGChildNodeView(NodeView):
else:
where_clause = where
- query = render_template("/".join([sql_path, 'dependents.sql']),
- fetch_dependencies=True, where_clause=where_clause)
+ query = render_template("/".join([sql_path, 'dependencies.sql']),
+ where_clause=where_clause)
# fetch the dependency for the selected object
dependencies = self.__fetch_dependency(conn, query)
# fetch role dependencies
if where_clause.find('subid') < 0:
- sql = render_template("/".join([sql_path, 'dependents.sql']),
- fetch_role_dependencies=True, where_clause=where_clause)
+ sql = render_template("/".join([sql_path, 'role_dependencies.sql']),
+ where_clause=where_clause)
status, result = conn.execute_dict(sql)
if not status:
@@ -402,7 +402,7 @@ class PGChildNodeView(NodeView):
where_clause = where
query = render_template("/".join([sql_path, 'dependents.sql']),
- fetch_dependents=True, where_clause=where_clause)
+ where_clause=where_clause)
# fetch the dependency for the selected object
dependents = self.__fetch_dependency(conn, query)
diff --git a/web/pgadmin/feature_tests/copy_selected_query_results_feature_test.py b/web/pgadmin/feature_tests/copy_selected_query_results_feature_test.py
index 223579ee..bda9de22 100644
--- a/web/pgadmin/feature_tests/copy_selected_query_results_feature_test.py
+++ b/web/pgadmin/feature_tests/copy_selected_query_results_feature_test.py
@@ -37,7 +37,7 @@ class CopySelectedQueryResultsFeatureTest(BaseFeatureTest):
self.page.find_by_partial_link_text("Query Tool").click()
self.page.click_tab('Query-1')
time.sleep(5)
- ActionChains(self.page.driver).send_keys("SELECT * FROM test_table").perform()
+ ActionChains(self.page.driver).send_keys("SELECT * FROM test_table ORDER BY some_column").perform()
self.page.driver.switch_to_frame(self.page.driver.find_element_by_tag_name("iframe"))
self.page.find_by_id("btn-flash").click()
diff --git a/web/pgadmin/feature_tests/xss_checks_pgadmin_debugger_test.py b/web/pgadmin/feature_tests/xss_checks_pgadmin_debugger_test.py
index 8b018fed..17bc7bf5 100644
--- a/web/pgadmin/feature_tests/xss_checks_pgadmin_debugger_test.py
+++ b/web/pgadmin/feature_tests/xss_checks_pgadmin_debugger_test.py
@@ -16,10 +16,14 @@ class CheckDebuggerForXssFeatureTest(BaseFeatureTest):
"""Tests to check if Debugger is vulnerable to XSS."""
scenarios = [
- ("Test table DDL generation", dict())
+ ("Tests to check if Debugger is vulnerable to XSS", dict())
]
def before(self):
+ with test_utils.Database(self.server) as (connection, _):
+ if connection.server_version < 90100:
+ self.skipTest("Functions tree node is not present in pgAdmin below PG v9.1")
+
# Some test function is needed for debugger
test_utils.create_debug_function(self.server, "postgres",
"test_function")
--
2.12.0
view thread (27+ 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: [patch] Dependents and Dependencies in GreenPlum
In-Reply-To: <CAE+jjam5JoMt9+Ue-O_qpgEJO_wAGJfGoaLCJg-1VzVc+AqB_w@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