Dear All:
I make some improvements to pgadmin4, In order to support Greenplum6, and
fix some incompatibility issues with Greenplum5.
Source code: https://github.com/cobolbaby/pgadmin4
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py
index 69d917d..b90bf88 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/__init__.py
@@ -11,20 +11,22 @@ import re
from functools import wraps
import simplejson as json
-from flask import render_template, request, jsonify, current_app
+from flask import current_app, jsonify, render_template, request
from flask_babelex import gettext
import pgadmin.browser.server_groups.servers as servers
from config import PG_DEFAULT_DRIVER
from pgadmin.browser.collection import CollectionNodeModule, PGChildModule
-from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
- parse_priv_to_db
+from pgadmin.browser.server_groups.servers.utils import (parse_priv_from_db,
+ parse_priv_to_db)
from pgadmin.browser.utils import PGChildNodeView
-from pgadmin.utils.ajax import make_json_response, internal_server_error, \
- make_response as ajax_response, gone, bad_request
-from pgadmin.utils.driver import get_driver
-from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry
from pgadmin.model import Database
+from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry
+from pgadmin.utils.ajax import (bad_request, gone, internal_server_error,
+ make_json_response)
+from pgadmin.utils.ajax import make_response as ajax_response
+from pgadmin.utils.driver import get_driver
+
"""
This module is responsible for generating two nodes
@@ -266,7 +268,7 @@ class SchemaView(PGChildNodeView):
"""
Returns the template path for GreenPlum servers.
"""
- return '#gpdb#{0}#'.format(ver)
+ return 'gpdb/#gpdb#{0}#'.format(ver)
def format_request_acls(self, data, modified=False, specific=None):
acls = {}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
index a05d915..ede5040 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/__init__.py
@@ -16,23 +16,22 @@ import traceback
from functools import wraps
import simplejson as json
-from flask import render_template, request, jsonify, \
- current_app
+from flask import current_app, jsonify, render_template, request
from flask_babelex import gettext
import pgadmin.browser.server_groups.servers.databases as databases
from config import PG_DEFAULT_DRIVER
-from pgadmin.browser.server_groups.servers.databases.schemas.utils import \
- SchemaChildModule, DataTypeReader
-from pgadmin.browser.server_groups.servers.databases.utils import \
- parse_sec_labels_from_db, parse_variables_from_db
-from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
- parse_priv_to_db
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import (
+ DataTypeReader, SchemaChildModule)
+from pgadmin.browser.server_groups.servers.databases.utils import (
+ parse_sec_labels_from_db, parse_variables_from_db)
+from pgadmin.browser.server_groups.servers.utils import (parse_priv_from_db,
+ parse_priv_to_db)
from pgadmin.browser.utils import PGChildNodeView
from pgadmin.tools.schema_diff.compare import SchemaDiffObjectCompare
from pgadmin.tools.schema_diff.node_registry import SchemaDiffRegistry
-from pgadmin.utils.ajax import make_json_response, internal_server_error, \
- make_response as ajax_response, gone
+from pgadmin.utils.ajax import gone, internal_server_error, make_json_response
+from pgadmin.utils.ajax import make_response as ajax_response
from pgadmin.utils.driver import get_driver
@@ -361,12 +360,11 @@ class FunctionView(PGChildNodeView, DataTypeReader, SchemaDiffObjectCompare):
template_initial = 'trigger_functions'
# Set the template path for the SQL scripts
- self.sql_template_path = "/".join([
- template_initial,
- self.manager.server_type,
- 'sql',
- '#{0}#'
- ]).format(self.manager.version)
+ self.sql_template_path = template_initial + (
+ "/{0}/sql/#{0}#{1}#".format(self.manager.server_type, self.manager.version) if
+ self.manager.server_type == 'gpdb' else
+ "/{0}/sql/#{1}#".format(self.manager.server_type, self.manager.version)
+ )
return f(*args, **kwargs)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/6_plus/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/6_plus/get_definition.sql
new file mode 100644
index 0000000..63a53d4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/6_plus/get_definition.sql
@@ -0,0 +1,17 @@
+SELECT proretset, prosrc, probin,
+ pg_catalog.pg_get_function_arguments(pg_proc.oid) AS funcargs,
+ pg_catalog.pg_get_function_identity_arguments(pg_proc.oid) AS funciargs,
+ pg_catalog.pg_get_function_result(pg_proc.oid) AS funcresult,
+ proiswindow, provolatile, proisstrict, prosecdef,
+ proconfig, procost, prorows, prodataaccess,
+ 'a' as proexeclocation,
+ (SELECT lanname FROM pg_catalog.pg_language WHERE pg_proc.oid = prolang) as lanname,
+ COALESCE(pg_catalog.pg_get_function_identity_arguments(pg_proc.oid), '') AS func_with_identity_arguments,
+ nspname,
+ proname,
+ COALESCE(pg_catalog.pg_get_function_arguments(pg_proc.oid), '') AS func_args
+FROM pg_catalog.pg_proc
+ JOIN pg_namespace nsp ON nsp.oid=pg_proc.pronamespace
+WHERE proisagg = FALSE
+ AND pronamespace = {{scid}}::oid
+ AND pg_proc.oid = {{fnid}}::oid;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/default/get_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/default/get_definition.sql
index 699fc96..7cebe1b 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/default/get_definition.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/functions/templates/functions/gpdb/sql/default/get_definition.sql
@@ -2,7 +2,7 @@ SELECT proretset, prosrc, probin,
pg_catalog.pg_get_function_arguments(pg_proc.oid) AS funcargs,
pg_catalog.pg_get_function_identity_arguments(pg_proc.oid) AS funciargs,
pg_catalog.pg_get_function_result(pg_proc.oid) AS funcresult,
- proiswin, provolatile, proisstrict, prosecdef,
+ proiswin as proiswindow, provolatile, proisstrict, prosecdef,
proconfig, procost, prorows, prodataaccess,
'a' as proexeclocation,
(SELECT lanname FROM pg_catalog.pg_language WHERE pg_proc.oid = prolang) as lanname,
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py
index 5f2aa3f..2dc7d27 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/__init__.py
@@ -9,21 +9,22 @@
"""Implements Foreign key constraint Node"""
-import simplejson as json
from functools import wraps
-import pgadmin.browser.server_groups.servers.databases as database
-from flask import render_template, request, jsonify
+import simplejson as json
+from flask import jsonify, render_template, request
from flask_babelex import gettext
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.type import ConstraintRegistry, ConstraintTypeModule
+
+import pgadmin.browser.server_groups.servers.databases as database
+from config import PG_DEFAULT_DRIVER
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.foreign_key import \
+ utils as fkey_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.type import (
+ ConstraintRegistry, ConstraintTypeModule)
from pgadmin.browser.utils import PGChildNodeView
-from pgadmin.utils.ajax import make_json_response, internal_server_error, \
- make_response as ajax_response, gone
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.foreign_key import utils as fkey_utils
+from pgadmin.utils.ajax import gone, internal_server_error, make_json_response
+from pgadmin.utils.ajax import make_response as ajax_response
from pgadmin.utils.driver import get_driver
-from config import PG_DEFAULT_DRIVER
FOREIGN_KEY_NOT_FOUND = gettext("Could not find the foreign key.")
@@ -184,7 +185,7 @@ class ForeignKeyConstraintView(PGChildNodeView):
"""
node_type = 'foreign_key'
- FOREIGN_KEY_PATH = 'foreign_key/sql/#{0}#'
+ FOREIGN_KEY_PATH = 'foreign_key/sql/#{0}#{1}#'
parent_ids = [
{'type': 'int', 'id': 'gid'},
@@ -233,8 +234,8 @@ class ForeignKeyConstraintView(PGChildNodeView):
self.manager.db_info[kwargs['did']]['datlastsysoid'] \
if self.manager.db_info is not None and \
kwargs['did'] in self.manager.db_info else 0
- self.template_path = self.FOREIGN_KEY_PATH.format(
- self.manager.version)
+ self.template_path = self.FOREIGN_KEY_PATH.format(self.manager.server_type,
+ self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, kwargs['tid'])
@@ -338,8 +339,8 @@ class ForeignKeyConstraintView(PGChildNodeView):
"""
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
self.conn = self.manager.connection(did=did)
- self.template_path = self.FOREIGN_KEY_PATH.format(
- self.manager.version)
+ self.template_path = self.FOREIGN_KEY_PATH.format(self.manager.server_type,
+ self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, tid)
@@ -449,7 +450,8 @@ class ForeignKeyConstraintView(PGChildNodeView):
"""
self.manager = get_driver(PG_DEFAULT_DRIVER).connection_manager(sid)
self.conn = self.manager.connection(did=did)
- self.template_path = self.FOREIGN_KEY_PATH.format(self.manager.version)
+ self.template_path = self.FOREIGN_KEY_PATH.format(self.manager.server_type,
+ self.manager.version)
# We need parent's name eg table name and schema name
schema, table = fkey_utils.get_parent(self.conn, tid)
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/utils.py
index c122a4b..5b4a4d0 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/constraints/foreign_key/utils.py
@@ -9,11 +9,13 @@
""" Implements Utility class for Foreign Keys. """
+from functools import wraps
+
from flask import render_template
from flask_babelex import gettext as _
+
from pgadmin.utils.ajax import internal_server_error
-from pgadmin.utils.exception import ObjectGone, ExecuteError
-from functools import wraps
+from pgadmin.utils.exception import ExecuteError, ObjectGone
FKEY_PROPERTIES_SQL = 'properties.sql'
@@ -29,8 +31,8 @@ def get_template_path(f):
# Here args[0] will hold the connection object
conn_obj = args[0]
if 'template_path' not in kwargs:
- kwargs['template_path'] = 'foreign_key/sql/#{0}#'.format(
- conn_obj.manager.version)
+ kwargs['template_path'] = 'foreign_key/sql/#{0}#{1}#'.format(conn_obj.manager.server_type,
+ conn_obj.manager.version)
return f(*args, **kwargs)
return wrap
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/gpdb_5.0_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/gpdb_5.0_plus/properties.sql
new file mode 100644
index 0000000..6837520
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/foreign_key/sql/gpdb_5.0_plus/properties.sql
@@ -0,0 +1,32 @@
+SELECT
+ FALSE as convalidated,
+ ct.oid,
+ conname as name,
+ condeferrable,
+ condeferred,
+ confupdtype,
+ confdeltype,
+ CASE confmatchtype
+ WHEN 's' THEN FALSE
+ WHEN 'f' THEN TRUE
+ END AS confmatchtype,
+ conkey,
+ confkey,
+ confrelid,
+ nl.nspname as fknsp,
+ cl.relname as fktab,
+ nr.nspname as refnsp,
+ cr.relname as reftab,
+ description as comment
+FROM pg_constraint ct
+JOIN pg_class cl ON cl.oid=conrelid
+JOIN pg_namespace nl ON nl.oid=cl.relnamespace
+JOIN pg_class cr ON cr.oid=confrelid
+JOIN pg_namespace nr ON nr.oid=cr.relnamespace
+LEFT OUTER JOIN pg_description des ON (des.objoid=ct.oid AND des.classoid='pg_constraint'::regclass)
+WHERE contype='f' AND
+conrelid = {{tid}}::oid
+{% if cid %}
+AND ct.oid = {{cid}}::oid
+{% endif %}
+ORDER BY conname
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/5_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/5_plus/create.sql
new file mode 100644
index 0000000..e69de29
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/nodes.sql
new file mode 100644
index 0000000..6b5b4ff
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/nodes.sql
@@ -0,0 +1,39 @@
+SELECT
+ table_class.oid,
+ partitions.partitiontablename AS name,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = table_class.oid AND tgisinternal = FALSE) AS triggercount,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = table_class.oid AND tgisinternal = FALSE AND tgenabled = 'O') AS has_enable_triggers,
+ partitions.partitionboundary AS partition_value,
+ partitions.partitionschemaname AS schema_id,
+ schema_name,
+ CASE WHEN sub_partitions.n > 0
+ THEN TRUE
+ ELSE FALSE END is_partitioned,
+ '' AS partition_scheme
+FROM
+ (SELECT
+ table_class.relnamespace,
+ nsp.nspname AS schema_name,
+ partitions.partitiontablename,
+ partitions.partitionboundary,
+ partitions.partitionschemaname
+ FROM pg_class table_class
+ INNER JOIN pg_partitions partitions
+ ON (relname = tablename AND parentpartitiontablename IS NULL) OR relname = parentpartitiontablename
+ LEFT JOIN pg_namespace nsp ON table_class.relnamespace = nsp.oid
+ WHERE
+ {% if ptid %} table_class.oid = {{ ptid }}::OID {% endif %}
+ {% if not ptid %} table_class.oid = {{ tid }}::OID {% endif %}
+ ) AS partitions
+ LEFT JOIN (SELECT
+ parentpartitiontablename,
+ count(*) AS n
+ FROM pg_partitions
+ GROUP BY parentpartitiontablename) sub_partitions
+ ON partitions.partitiontablename = sub_partitions.parentpartitiontablename
+ LEFT JOIN pg_class table_class ON partitions.relnamespace = table_class.relnamespace AND partitions.partitiontablename = table_class.relname
+ORDER BY partitions.partitiontablename;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/properties.sql
new file mode 100644
index 0000000..4911469
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/gpdb/6_plus/properties.sql
@@ -0,0 +1,82 @@
+SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
+ (CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as parent_schema,
+ nsp.nspname as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids,
+ (CASE WHEN partitions.number_of_partitions > 0 THEN true ELSE false END) AS relispartition,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*)
+ FROM pg_trigger
+ WHERE tgrelid = rel.oid AND tgisinternal = FALSE) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ false AS relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)'))::BOOL AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, rel.reltype, typ.typname,
+ typ.typrelid AS typoid,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
+ -- Added for pgAdmin4
+ NULL AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table,
+ -- Added for partition table
+ (CASE WHEN partitions.number_of_partitions > 0 THEN true ELSE false END) AS is_partitioned,
+ '' AS partition_scheme,
+ {% if ptid %}
+ (CASE WHEN partitions.number_of_partitions > 0 THEN partitions.expression ELSE '' END) AS partition_value,
+ (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS partitioned_table_name
+ {% else %}
+ partitions.expression AS partition_value
+ {% endif %}
+
+FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+ LEFT JOIN pg_type typ ON rel.reltype=typ.oid
+ LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid
+ LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid
+ LEFT JOIN (SELECT tablename, partitionboundary as expression, count(*) number_of_partitions FROM pg_partitions GROUP BY tablename, expression) partitions ON rel.relname = tablename
+WHERE inh.inhparent = {{ tid }}::oid
+{% if ptid %} AND rel.oid = {{ ptid }}::oid {% endif %}
+ORDER BY rel.relname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/gpdb_6.0_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/gpdb_6.0_plus/properties.sql
new file mode 100644
index 0000000..f7d0448
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/gpdb_6.0_plus/properties.sql
@@ -0,0 +1,83 @@
+SELECT *,
+ (CASE when pre_coll_inherits is NULL then ARRAY[]::varchar[] else pre_coll_inherits END) as coll_inherits
+ {% if tid %}, (CASE WHEN is_partitioned THEN (SELECT substring(pg_get_partition_def({{ tid }}::oid, true) from 14)) ELSE '' END) AS partition_scheme {% endif %}
+FROM (
+ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS spcoid,rel.relacl AS relacl_str,
+ (CASE WHEN length(spc.spcname::text) > 0 THEN spc.spcname ELSE
+ (SELECT sp.spcname FROM pg_database dtb
+ JOIN pg_tablespace sp ON dtb.dattablespace=sp.oid
+ WHERE dtb.oid = {{ did }}::oid)
+ END) as spcname,
+ (select nspname FROM pg_namespace WHERE oid = {{scid}}::oid ) as schema,
+ pg_get_userbyid(rel.relowner) AS relowner, rel.relhasoids,
+ rel.relhassubclass, rel.reltuples::bigint, des.description, con.conname, con.conkey,
+ EXISTS(select 1 FROM pg_trigger
+ JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'
+ JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
+ WHERE tgrelid=rel.oid) AS isrepl,
+ (SELECT count(*) FROM pg_trigger WHERE tgrelid=rel.oid) AS triggercount,
+ (SELECT ARRAY(SELECT CASE WHEN (nspname NOT LIKE 'pg\_%') THEN
+ quote_ident(nspname)||'.'||quote_ident(c.relname)
+ ELSE quote_ident(c.relname) END AS inherited_tables
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid ORDER BY inhseqno)) AS pre_coll_inherits,
+ (SELECT count(*)
+ FROM pg_inherits i
+ JOIN pg_class c ON c.oid = i.inhparent
+ JOIN pg_namespace n ON n.oid=c.relnamespace
+ WHERE i.inhrelid = rel.oid) AS inherited_tables_cnt,
+ false AS relpersistence,
+ substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'compresslevel=([0-9]*)') AS compresslevel,
+ substring(array_to_string(rel.reloptions, ',') FROM 'blocksize=([0-9]*)') AS blocksize,
+ substring(array_to_string(rel.reloptions, ',') FROM 'orientation=(row|column)') AS orientation,
+ substring(array_to_string(rel.reloptions, ',') FROM 'appendonly=(true|false)')::boolean AS appendonly,
+ substring(array_to_string(rel.reloptions, ',') FROM 'compresstype=(zlib|quicklz|rle_type|none)') AS compresstype,
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS autovacuum_enabled,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_vacuum_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS autovacuum_analyze_scale_factor,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age,
+ substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') = 'true')
+ THEN true ELSE false END) AS toast_autovacuum_enabled,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_vacuum_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.]?[0-9]*)') AS toast_autovacuum_analyze_scale_factor,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age,
+ substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age,
+ array_to_string(rel.reloptions, ',') AS table_vacuum_settings_str,
+ array_to_string(tst.reloptions, ',') AS toast_table_vacuum_settings_str,
+ rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions, NULL AS reloftype, typ.typname AS typname,
+ typ.typrelid AS typoid,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable,
+ ARRAY[]::varchar[] AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table,
+
+ gdp.distkey::smallint[] AS distribution,
+ (CASE WHEN (SELECT count(*) from pg_partition where parrelid = rel.oid) > 0 THEN true ELSE false END) AS is_partitioned
+
+
+ FROM pg_class rel
+ LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace
+ LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)
+ LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'
+ LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid
+ LEFT OUTER JOIN gp_distribution_policy gdp ON gdp.localoid = rel.oid
+ LEFT OUTER JOIN pg_type typ ON typ.oid = rel.reltype
+
+ WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = {{ scid }}
+ {% if tid %} AND rel.oid = {{ tid }}::oid {% endif %}
+) AS TableInformation
+ ORDER BY name
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
index 1b90f8c..a95cd9f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/utils.py
@@ -9,42 +9,42 @@
""" Implements Utility class for Table and Partitioned Table. """
-import re
import copy
+import re
from functools import wraps
+
import simplejson as json
-from flask import render_template, jsonify, request
+from flask import jsonify, render_template, request
from flask_babelex import gettext
-from pgadmin.browser.server_groups.servers.databases.schemas\
- .tables.base_partition_table import BasePartitionTable
-from pgadmin.utils.ajax import make_json_response, internal_server_error, \
- gone, make_response as ajax_response
-from pgadmin.browser.server_groups.servers.databases.schemas.utils \
- import DataTypeReader, parse_rule_definition
-from pgadmin.browser.server_groups.servers.utils import parse_priv_from_db, \
- parse_priv_to_db
+from config import PG_DEFAULT_DRIVER
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.base_partition_table import \
+ BasePartitionTable
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.columns import \
+ utils as column_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.compound_triggers import \
+ utils as compound_trigger_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.check_constraint import \
+ utils as check_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.exclusion_constraint import \
+ utils as exclusion_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.foreign_key import \
+ utils as fkey_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.constraints.index_constraint import \
+ utils as idxcons_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.row_security_policies import \
+ utils as row_security_policies_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.tables.triggers import \
+ utils as trigger_utils
+from pgadmin.browser.server_groups.servers.databases.schemas.utils import (
+ DataTypeReader, parse_rule_definition)
+from pgadmin.browser.server_groups.servers.utils import (parse_priv_from_db,
+ parse_priv_to_db)
from pgadmin.browser.utils import PGChildNodeView
+from pgadmin.utils.ajax import gone, internal_server_error, make_json_response
+from pgadmin.utils.ajax import make_response as ajax_response
from pgadmin.utils.compile_template_name import compile_template_path
from pgadmin.utils.driver import get_driver
-from config import PG_DEFAULT_DRIVER
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- columns import utils as column_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.foreign_key import utils as fkey_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.check_constraint import utils as check_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.exclusion_constraint import utils as exclusion_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- constraints.index_constraint import utils as idxcons_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- triggers import utils as trigger_utils
-from pgadmin.browser.server_groups.servers.databases.schemas.tables.\
- compound_triggers import utils as compound_trigger_utils
-from pgadmin.browser.server_groups.servers.databases.schemas. \
- tables.row_security_policies import \
- utils as row_security_policies_utils
class BaseTableView(PGChildNodeView, BasePartitionTable):
@@ -682,53 +682,53 @@ class BaseTableView(PGChildNodeView, BasePartitionTable):
part_data['partition_value'] = row['partition_value']
part_data['is_partitioned'] = row['is_partitioned']
part_data['partition_scheme'] = row['partition_scheme']
- part_data['description'] = row['description']
- part_data['relowner'] = row['relowner']
+ part_data['description'] = row.get('description')
+ part_data['relowner'] = row.get('relowner')
self.update_autovacuum_properties(row)
- part_data['fillfactor'] = row['fillfactor']
- part_data['autovacuum_custom'] = row['autovacuum_custom']
- part_data['autovacuum_enabled'] = row['autovacuum_enabled']
+ part_data['fillfactor'] = row.get('fillfactor')
+ part_data['autovacuum_custom'] = row.get('autovacuum_custom')
+ part_data['autovacuum_enabled'] = row.get('autovacuum_enabled')
part_data['autovacuum_vacuum_threshold'] = \
- row['autovacuum_vacuum_threshold']
+ row.get('autovacuum_vacuum_threshold')
part_data['autovacuum_vacuum_scale_factor'] = \
- row['autovacuum_vacuum_scale_factor']
+ row.get('autovacuum_vacuum_scale_factor')
part_data['autovacuum_analyze_threshold'] = \
- row['autovacuum_analyze_threshold']
+ row.get('autovacuum_analyze_threshold')
part_data['autovacuum_analyze_scale_factor'] = \
- row['autovacuum_analyze_scale_factor']
+ row.get('autovacuum_analyze_scale_factor')
part_data['autovacuum_vacuum_cost_delay'] = \
- row['autovacuum_vacuum_cost_delay']
+ row.get('autovacuum_vacuum_cost_delay')
part_data['autovacuum_vacuum_cost_limit'] = \
- row['autovacuum_vacuum_cost_limit']
+ row.get('autovacuum_vacuum_cost_limit')
part_data['autovacuum_freeze_min_age'] = \
- row['autovacuum_freeze_min_age']
+ row.get('autovacuum_freeze_min_age')
part_data['autovacuum_freeze_max_age'] = \
- row['autovacuum_freeze_max_age']
+ row.get('autovacuum_freeze_max_age')
part_data['autovacuum_freeze_table_age'] = \
- row['autovacuum_freeze_table_age']
- part_data['toast_autovacuum'] = row['toast_autovacuum']
+ row.get('autovacuum_freeze_table_age')
+ part_data['toast_autovacuum'] = row.get('toast_autovacuum')
part_data['toast_autovacuum_enabled'] = \
- row['toast_autovacuum_enabled']
+ row.get('toast_autovacuum_enabled')
part_data['toast_autovacuum_vacuum_threshold'] = \
- row['toast_autovacuum_vacuum_threshold']
+ row.get('toast_autovacuum_vacuum_threshold')
part_data['toast_autovacuum_vacuum_scale_factor'] = \
- row['toast_autovacuum_vacuum_scale_factor']
+ row.get('toast_autovacuum_vacuum_scale_factor')
part_data['toast_autovacuum_analyze_threshold'] = \
- row['toast_autovacuum_analyze_threshold']
+ row.get('toast_autovacuum_analyze_threshold')
part_data['toast_autovacuum_analyze_scale_factor'] = \
- row['toast_autovacuum_analyze_scale_factor']
+ row.get('toast_autovacuum_analyze_scale_factor')
part_data['toast_autovacuum_vacuum_cost_delay'] = \
- row['toast_autovacuum_vacuum_cost_delay']
+ row.get('toast_autovacuum_vacuum_cost_delay')
part_data['toast_autovacuum_vacuum_cost_limit'] = \
- row['toast_autovacuum_vacuum_cost_limit']
+ row.get('toast_autovacuum_vacuum_cost_limit')
part_data['toast_autovacuum_freeze_min_age'] = \
- row['toast_autovacuum_freeze_min_age']
+ row.get('toast_autovacuum_freeze_min_age')
part_data['toast_autovacuum_freeze_max_age'] = \
- row['toast_autovacuum_freeze_max_age']
+ row.get('toast_autovacuum_freeze_max_age')
part_data['toast_autovacuum_freeze_table_age'] = \
- row['toast_autovacuum_freeze_table_age']
+ row.get('toast_autovacuum_freeze_table_age')
# We will add Auto vacuum defaults with out result for grid
part_data['vacuum_table'] = \
@@ -1817,34 +1817,33 @@ class BaseTableView(PGChildNodeView, BasePartitionTable):
# x: No set, t: true, f: false
if res is not None:
res['autovacuum_enabled'] = 'x' \
- if res['autovacuum_enabled'] is None else \
+ if res.get('autovacuum_enabled') is None else \
{True: 't', False: 'f'}[res['autovacuum_enabled']]
res['toast_autovacuum_enabled'] = 'x' \
- if res['toast_autovacuum_enabled'] is None else \
- {True: 't', False: 'f'}[
- res['toast_autovacuum_enabled']]
+ if res.get('toast_autovacuum_enabled') is None else \
+ {True: 't', False: 'f'}[res['toast_autovacuum_enabled']]
# Enable custom autovaccum only if one of the options is set
# or autovacuum is set
res['autovacuum_custom'] = any([
- res['autovacuum_vacuum_threshold'],
- res['autovacuum_vacuum_scale_factor'],
- res['autovacuum_analyze_threshold'],
- res['autovacuum_analyze_scale_factor'],
- res['autovacuum_vacuum_cost_delay'],
- res['autovacuum_vacuum_cost_limit'],
- res['autovacuum_freeze_min_age'],
- res['autovacuum_freeze_max_age'],
- res['autovacuum_freeze_table_age']]) or \
- res['autovacuum_enabled'] in ('t', 'f')
+ res.get('autovacuum_vacuum_threshold'),
+ res.get('autovacuum_vacuum_scale_factor'),
+ res.get('autovacuum_analyze_threshold'),
+ res.get('autovacuum_analyze_scale_factor'),
+ res.get('autovacuum_vacuum_cost_delay'),
+ res.get('autovacuum_vacuum_cost_limit'),
+ res.get('autovacuum_freeze_min_age'),
+ res.get('autovacuum_freeze_max_age'),
+ res.get('autovacuum_freeze_table_age')]) or \
+ res.get('autovacuum_enabled') in ('t', 'f')
res['toast_autovacuum'] = any([
- res['toast_autovacuum_vacuum_threshold'],
- res['toast_autovacuum_vacuum_scale_factor'],
- res['toast_autovacuum_analyze_threshold'],
- res['toast_autovacuum_analyze_scale_factor'],
- res['toast_autovacuum_vacuum_cost_delay'],
- res['toast_autovacuum_vacuum_cost_limit'],
- res['toast_autovacuum_freeze_min_age'],
- res['toast_autovacuum_freeze_max_age'],
- res['toast_autovacuum_freeze_table_age']]) or \
- res['toast_autovacuum_enabled'] in ('t', 'f')
+ res.get('toast_autovacuum_vacuum_threshold'),
+ res.get('toast_autovacuum_vacuum_scale_factor'),
+ res.get('toast_autovacuum_analyze_threshold'),
+ res.get('toast_autovacuum_analyze_scale_factor'),
+ res.get('toast_autovacuum_vacuum_cost_delay'),
+ res.get('toast_autovacuum_vacuum_cost_limit'),
+ res.get('toast_autovacuum_freeze_min_age'),
+ res.get('toast_autovacuum_freeze_max_age'),
+ res.get('toast_autovacuum_freeze_table_age')]) or \
+ res.get('toast_autovacuum_enabled') in ('t', 'f')
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/allowed_privs.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/allowed_privs.json
new file mode 100644
index 0000000..0c1784a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/allowed_privs.json
@@ -0,0 +1,30 @@
+{# List of allowed privileges for PostgreSQL 9.2 or later #}
+{#
+ Format for allowed privileges is:
+ "acl_col": {
+ "type": "name",
+ "acl": [...]
+ }
+#}
+{
+ "nspacl": {
+ "type": "SCHEMA",
+ "acl": ["C", "U"]
+ },
+ "deftblacl": {
+ "type": "TABLE",
+ "acl": ["r", "a", "w", "d", "D", "x", "t"]
+ },
+ "defseqacl": {
+ "type": "SEQUENCE",
+ "acl": ["U", "r", "w"]
+ },
+ "deffuncacl": {
+ "type": "FUNCTION",
+ "acl": ["X"]
+ },
+ "deftypeacl": {
+ "type": "TYPE",
+ "acl": ["U"]
+ }
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/acl.sql
new file mode 100644
index 0000000..48dcf1f
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/acl.sql
@@ -0,0 +1,7 @@
+SELECT
+ 'nspacl' AS deftype,
+ 'PUBLIC' AS grantee,
+ NULL AS grantor,
+ NULL AS privileges,
+ NULL AS grantable
+LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/create.sql
new file mode 100644
index 0000000..2016209
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/create.sql
@@ -0,0 +1,40 @@
+{% import 'macros/security.macros' as SECLABEL %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %}
+{% if data.name %}
+CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %}
+
+ AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}{% endif %};
+{# Alter the comment/description #}
+{% if data.description %}
+
+COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
+ IS {{ data.description|qtLiteral }};
+{% endif %}
+{# ACL for the schema #}
+{% if data.nspacl %}
+{% for priv in data.nspacl %}
+
+{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %}
+{% endif %}
+{# Default privileges on tables #}
+{% for defacl, type in [
+ ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'),
+ ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')]
+%}
+{% if data[defacl] %}{% set acl = data[defacl] %}
+{% for priv in acl %}
+
+{{ DEFAULT_PRIVILEGE.SET(
+ conn, 'SCHEMA', data.name, type, priv.grantee,
+ priv.without_grant, priv.with_grant
+ ) }}{% endfor %}
+{% endif %}
+{% endfor %}
+{# Security Labels on schema #}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% for r in data.seclabels %}
+
+{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/defacl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/defacl.sql
new file mode 100644
index 0000000..191e40c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/defacl.sql
@@ -0,0 +1,7 @@
+SELECT
+ '' AS deftype,
+ '' AS grantee,
+ '' AS grantor,
+ '' AS grantor,
+ '' AS privileges,
+ '' AS grantable
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/delete.sql
new file mode 100644
index 0000000..74e9126
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/delete.sql
@@ -0,0 +1 @@
+DROP SCHEMA {{ conn|qtIdent(name) }} {% if cascade %}CASCADE{%endif%};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/get_name.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/get_name.sql
new file mode 100644
index 0000000..3c5187d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/get_name.sql
@@ -0,0 +1 @@
+SELECT nsp.nspname FROM pg_namespace nsp WHERE nsp.oid = {{ scid|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/is_catalog.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/is_catalog.sql
new file mode 100644
index 0000000..9386acb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/is_catalog.sql
@@ -0,0 +1,9 @@
+{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
+SELECT
+ nsp.nspname as schema_name,
+ {{ CATALOGS.LIST('nsp') }} AS is_catalog,
+ {{ CATALOGS.DB_SUPPORT('nsp') }} AS db_support
+FROM
+ pg_catalog.pg_namespace nsp
+WHERE
+ nsp.oid = {{ scid|qtLiteral }}::OID;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/nodes.sql
new file mode 100644
index 0000000..d1a19d1
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/nodes.sql
@@ -0,0 +1,20 @@
+{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
+SELECT
+ nsp.oid,
+ nsp.nspname as name,
+ has_schema_privilege(nsp.oid, 'CREATE') as can_create,
+ has_schema_privilege(nsp.oid, 'USAGE') as has_usage
+FROM
+ pg_namespace nsp
+WHERE
+ {% if scid %}
+ nsp.oid={{scid}}::oid AND
+ {% else %}
+ {% if not show_sysobj %}
+ nspname NOT LIKE 'pg\_%' AND
+ {% endif %}
+ {% endif %}
+ NOT (
+{{ CATALOGS.LIST('nsp') }}
+ )
+ORDER BY nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/oid.sql
new file mode 100644
index 0000000..c5329b3
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/oid.sql
@@ -0,0 +1 @@
+SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = {{ schema|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/properties.sql
new file mode 100644
index 0000000..f6c2d92
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/properties.sql
@@ -0,0 +1,39 @@
+{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
+SELECT
+ CASE
+ WHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1
+ WHEN (nspname LIKE E'pg\\_%') THEN 0
+ ELSE 3 END AS nsptyp,
+ nsp.nspname AS name,
+ nsp.oid,
+ array_to_string(nsp.nspacl::text[], ', ') as acl,
+ r.rolname AS namespaceowner, description,
+ has_schema_privilege(nsp.oid, 'CREATE') AS can_create,
+ CASE
+ WHEN nspname LIKE E'pg\\_%' THEN true
+ ELSE false END AS is_sys_object,
+ {### Default ACL for Tables ###}
+ '' AS tblacl,
+ {### Default ACL for Sequnces ###}
+ '' AS seqacl,
+ {### Default ACL for Functions ###}
+ '' AS funcacl,
+ {### Default ACL for Type ###}
+ '' AS typeacl
+FROM
+ pg_namespace nsp
+ LEFT OUTER JOIN pg_description des ON
+ (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
+ LEFT JOIN pg_roles r ON (r.oid = nsp.nspowner)
+WHERE
+ {% if scid %}
+ nsp.oid={{scid}}::oid AND
+ {% else %}
+ {% if not show_sysobj %}
+ nspname NOT LIKE E'pg\\_%' AND
+ {% endif %}
+ {% endif %}
+ NOT (
+{{ CATALOGS.LIST('nsp') }}
+ )
+ORDER BY 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/update.sql
new file mode 100644
index 0000000..47f6e13
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb/5_plus/sql/update.sql
@@ -0,0 +1,83 @@
+{% import 'macros/security.macros' as SECLABEL %}
+{% import 'macros/privilege.macros' as PRIVILEGE %}
+{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %}
+{# Rename the schema #}
+{% if data.name and data.name != o_data.name %}
+ALTER SCHEMA {{ conn|qtIdent(o_data.name) }}
+ RENAME TO {{ conn|qtIdent(data.name) }};
+
+{% endif %}
+{# Change the owner #}
+{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
+ALTER SCHEMA {{ conn|qtIdent(data.name) }}
+ OWNER TO {{ conn|qtIdent(data.namespaceowner) }};
+
+{% endif %}
+{# Update the comments/description #}
+{% if data.description is defined and data.description != o_data.description %}
+COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
+ IS {{ data.description|qtLiteral }};
+
+{% endif %}
+{# Change the privileges #}
+{% if data.nspacl %}
+{% if 'deleted' in data.nspacl %}
+{% for priv in data.nspacl.deleted %}
+{{ PRIVILEGE.RESETALL(conn, 'SCHEMA', priv.grantee, data.name) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in data.nspacl %}
+{% for priv in data.nspacl.changed %}
+{{ PRIVILEGE.RESETALL(conn, 'SCHEMA', priv.grantee, data.name) }}
+{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in data.nspacl %}
+{% for priv in data.nspacl.added %}
+{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{# Change the default privileges #}
+{% for defacl, type in [
+ ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'),
+ ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')]
+%}
+{% if data[defacl] %}{% set acl = data[defacl] %}
+{% if 'deleted' in acl %}
+{% for priv in acl.deleted %}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in acl %}
+{% for priv in acl.changed %}
+{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in acl %}
+{% for priv in acl.added %}
+{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{% endif %}
+{% endfor %}
+{# Change the security labels #}
+{% if data.seclabels and data.seclabels|length > 0 %}
+{% set seclabels = data.seclabels %}
+{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
+{% for r in seclabels.deleted %}
+{{ SECLABEL.DROP(conn, 'SCHEMA', data.name, r.provider) }}
+{% endfor %}
+{% endif %}
+{% if 'added' in seclabels and seclabels.added|length > 0 %}
+{% for r in seclabels.added %}
+{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
+{% for r in seclabels.changed %}
+{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/allowed_privs.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/allowed_privs.json
deleted file mode 100644
index 0c1784a..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/allowed_privs.json
+++ /dev/null
@@ -1,30 +0,0 @@
-{# List of allowed privileges for PostgreSQL 9.2 or later #}
-{#
- Format for allowed privileges is:
- "acl_col": {
- "type": "name",
- "acl": [...]
- }
-#}
-{
- "nspacl": {
- "type": "SCHEMA",
- "acl": ["C", "U"]
- },
- "deftblacl": {
- "type": "TABLE",
- "acl": ["r", "a", "w", "d", "D", "x", "t"]
- },
- "defseqacl": {
- "type": "SEQUENCE",
- "acl": ["U", "r", "w"]
- },
- "deffuncacl": {
- "type": "FUNCTION",
- "acl": ["X"]
- },
- "deftypeacl": {
- "type": "TYPE",
- "acl": ["U"]
- }
-}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/acl.sql
deleted file mode 100644
index 48dcf1f..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/acl.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-SELECT
- 'nspacl' AS deftype,
- 'PUBLIC' AS grantee,
- NULL AS grantor,
- NULL AS privileges,
- NULL AS grantable
-LIMIT 0;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/create.sql
deleted file mode 100644
index 2016209..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/create.sql
+++ /dev/null
@@ -1,40 +0,0 @@
-{% import 'macros/security.macros' as SECLABEL %}
-{% import 'macros/privilege.macros' as PRIVILEGE %}
-{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %}
-{% if data.name %}
-CREATE SCHEMA {{ conn|qtIdent(data.name) }}{% if data.namespaceowner %}
-
- AUTHORIZATION {{ conn|qtIdent(data.namespaceowner) }}{% endif %}{% endif %};
-{# Alter the comment/description #}
-{% if data.description %}
-
-COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
- IS {{ data.description|qtLiteral }};
-{% endif %}
-{# ACL for the schema #}
-{% if data.nspacl %}
-{% for priv in data.nspacl %}
-
-{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}{% endfor %}
-{% endif %}
-{# Default privileges on tables #}
-{% for defacl, type in [
- ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'),
- ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')]
-%}
-{% if data[defacl] %}{% set acl = data[defacl] %}
-{% for priv in acl %}
-
-{{ DEFAULT_PRIVILEGE.SET(
- conn, 'SCHEMA', data.name, type, priv.grantee,
- priv.without_grant, priv.with_grant
- ) }}{% endfor %}
-{% endif %}
-{% endfor %}
-{# Security Labels on schema #}
-{% if data.seclabels and data.seclabels|length > 0 %}
-{% for r in data.seclabels %}
-
-{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
-{% endfor %}
-{% endif %}
\ No newline at end of file
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/defacl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/defacl.sql
deleted file mode 100644
index 191e40c..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/defacl.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-SELECT
- '' AS deftype,
- '' AS grantee,
- '' AS grantor,
- '' AS grantor,
- '' AS privileges,
- '' AS grantable
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/delete.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/delete.sql
deleted file mode 100644
index 74e9126..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/delete.sql
+++ /dev/null
@@ -1 +0,0 @@
-DROP SCHEMA {{ conn|qtIdent(name) }} {% if cascade %}CASCADE{%endif%};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/get_name.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/get_name.sql
deleted file mode 100644
index 3c5187d..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/get_name.sql
+++ /dev/null
@@ -1 +0,0 @@
-SELECT nsp.nspname FROM pg_namespace nsp WHERE nsp.oid = {{ scid|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/is_catalog.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/is_catalog.sql
deleted file mode 100644
index 9386acb..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/is_catalog.sql
+++ /dev/null
@@ -1,9 +0,0 @@
-{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
-SELECT
- nsp.nspname as schema_name,
- {{ CATALOGS.LIST('nsp') }} AS is_catalog,
- {{ CATALOGS.DB_SUPPORT('nsp') }} AS db_support
-FROM
- pg_catalog.pg_namespace nsp
-WHERE
- nsp.oid = {{ scid|qtLiteral }}::OID;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/nodes.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/nodes.sql
deleted file mode 100644
index d1a19d1..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/nodes.sql
+++ /dev/null
@@ -1,20 +0,0 @@
-{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
-SELECT
- nsp.oid,
- nsp.nspname as name,
- has_schema_privilege(nsp.oid, 'CREATE') as can_create,
- has_schema_privilege(nsp.oid, 'USAGE') as has_usage
-FROM
- pg_namespace nsp
-WHERE
- {% if scid %}
- nsp.oid={{scid}}::oid AND
- {% else %}
- {% if not show_sysobj %}
- nspname NOT LIKE 'pg\_%' AND
- {% endif %}
- {% endif %}
- NOT (
-{{ CATALOGS.LIST('nsp') }}
- )
-ORDER BY nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/oid.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/oid.sql
deleted file mode 100644
index c5329b3..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/oid.sql
+++ /dev/null
@@ -1 +0,0 @@
-SELECT nsp.oid FROM pg_namespace nsp WHERE nsp.nspname = {{ schema|qtLiteral }};
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/properties.sql
deleted file mode 100644
index f6c2d92..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/properties.sql
+++ /dev/null
@@ -1,39 +0,0 @@
-{% import 'catalog/pg/macros/catalogs.sql' as CATALOGS %}
-SELECT
- CASE
- WHEN (nspname LIKE E'pg\\_temp\\_%') THEN 1
- WHEN (nspname LIKE E'pg\\_%') THEN 0
- ELSE 3 END AS nsptyp,
- nsp.nspname AS name,
- nsp.oid,
- array_to_string(nsp.nspacl::text[], ', ') as acl,
- r.rolname AS namespaceowner, description,
- has_schema_privilege(nsp.oid, 'CREATE') AS can_create,
- CASE
- WHEN nspname LIKE E'pg\\_%' THEN true
- ELSE false END AS is_sys_object,
- {### Default ACL for Tables ###}
- '' AS tblacl,
- {### Default ACL for Sequnces ###}
- '' AS seqacl,
- {### Default ACL for Functions ###}
- '' AS funcacl,
- {### Default ACL for Type ###}
- '' AS typeacl
-FROM
- pg_namespace nsp
- LEFT OUTER JOIN pg_description des ON
- (des.objoid=nsp.oid AND des.classoid='pg_namespace'::regclass)
- LEFT JOIN pg_roles r ON (r.oid = nsp.nspowner)
-WHERE
- {% if scid %}
- nsp.oid={{scid}}::oid AND
- {% else %}
- {% if not show_sysobj %}
- nspname NOT LIKE E'pg\\_%' AND
- {% endif %}
- {% endif %}
- NOT (
-{{ CATALOGS.LIST('nsp') }}
- )
-ORDER BY 1, nspname;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/update.sql
deleted file mode 100644
index 47f6e13..0000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/templates/schemas/gpdb_5.0_plus/sql/update.sql
+++ /dev/null
@@ -1,83 +0,0 @@
-{% import 'macros/security.macros' as SECLABEL %}
-{% import 'macros/privilege.macros' as PRIVILEGE %}
-{% import 'macros/default_privilege.macros' as DEFAULT_PRIVILEGE %}
-{# Rename the schema #}
-{% if data.name and data.name != o_data.name %}
-ALTER SCHEMA {{ conn|qtIdent(o_data.name) }}
- RENAME TO {{ conn|qtIdent(data.name) }};
-
-{% endif %}
-{# Change the owner #}
-{% if data.namespaceowner and data.namespaceowner != o_data.namespaceowner %}
-ALTER SCHEMA {{ conn|qtIdent(data.name) }}
- OWNER TO {{ conn|qtIdent(data.namespaceowner) }};
-
-{% endif %}
-{# Update the comments/description #}
-{% if data.description is defined and data.description != o_data.description %}
-COMMENT ON SCHEMA {{ conn|qtIdent(data.name) }}
- IS {{ data.description|qtLiteral }};
-
-{% endif %}
-{# Change the privileges #}
-{% if data.nspacl %}
-{% if 'deleted' in data.nspacl %}
-{% for priv in data.nspacl.deleted %}
-{{ PRIVILEGE.RESETALL(conn, 'SCHEMA', priv.grantee, data.name) }}
-{% endfor %}
-{% endif %}
-{% if 'changed' in data.nspacl %}
-{% for priv in data.nspacl.changed %}
-{{ PRIVILEGE.RESETALL(conn, 'SCHEMA', priv.grantee, data.name) }}
-{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
-{% endfor %}
-{% endif %}
-{% if 'added' in data.nspacl %}
-{% for priv in data.nspacl.added %}
-{{ PRIVILEGE.APPLY(conn, 'SCHEMA', priv.grantee, data.name, priv.without_grant, priv.with_grant) }}
-{% endfor %}
-{% endif %}
-{% endif %}
-{# Change the default privileges #}
-{% for defacl, type in [
- ('deftblacl', 'TABLES'), ('defseqacl', 'SEQUENCES'),
- ('deffuncacl', 'FUNCTIONS'), ('deftypeacl', 'TYPES')]
-%}
-{% if data[defacl] %}{% set acl = data[defacl] %}
-{% if 'deleted' in acl %}
-{% for priv in acl.deleted %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
-{% endfor %}
-{% endif %}
-{% if 'changed' in acl %}
-{% for priv in acl.changed %}
-{{ DEFAULT_PRIVILEGE.UNSET(conn, 'SCHEMA', data.name, type, priv.grantee) }}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
-{% endfor %}
-{% endif %}
-{% if 'added' in acl %}
-{% for priv in acl.added %}
-{{ DEFAULT_PRIVILEGE.SET(conn,'SCHEMA', data.name, type, priv.grantee, priv.without_grant, priv.with_grant) }}
-{% endfor %}
-{% endif %}
-{% endif %}
-{% endfor %}
-{# Change the security labels #}
-{% if data.seclabels and data.seclabels|length > 0 %}
-{% set seclabels = data.seclabels %}
-{% if 'deleted' in seclabels and seclabels.deleted|length > 0 %}
-{% for r in seclabels.deleted %}
-{{ SECLABEL.DROP(conn, 'SCHEMA', data.name, r.provider) }}
-{% endfor %}
-{% endif %}
-{% if 'added' in seclabels and seclabels.added|length > 0 %}
-{% for r in seclabels.added %}
-{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
-{% endfor %}
-{% endif %}
-{% if 'changed' in seclabels and seclabels.changed|length > 0 %}
-{% for r in seclabels.changed %}
-{{ SECLABEL.APPLY(conn, 'SCHEMA', data.name, r.provider, r.label) }}
-{% endfor %}
-{% endif %}
-{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependencies.sql
index 81bb2a6..8a816ca 100644
--- a/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependencies.sql
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependencies.sql
@@ -1,32 +1,23 @@
-SELECT DISTINCT dep.deptype, dep.refclassid, dep.refobjid, cl.relkind, ad.adbin, ad.adsrc,
- CASE WHEN cl.relkind IS NOT NULL THEN CASE WHEN cl.relkind = 'r' THEN cl.relkind || COALESCE(dep.refobjsubid::text, '') ELSE cl.relkind END
- WHEN tg.oid IS NOT NULL THEN 'Tr'::text
- WHEN ty.oid IS NOT NULL THEN CASE WHEN ty.typtype = 'd' THEN 'd'::text ELSE 'Ty'::text END
+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 AND (prtyp.typname = 'trigger' OR prtyp.typname = 'event_trigger') THEN 'Pt'::text
- WHEN pr.oid IS NOT NULL THEN 'Pf'::text
+ WHEN pr.oid IS NOT NULL AND prtyp.typname = 'trigger' THEN 't'::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 'Rl'::text
- WHEN co.oid IS NOT NULL THEN CASE WHEN co.contypid > 0 THEN 'Cd' ELSE 'C'::text || contype END
+ 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 'Fs'::text
- WHEN fdw.oid IS NOT NULL THEN 'Fw'::text
- WHEN col.oid IS NOT NULL THEN 'Co'::text
- WHEN ftsc.oid IS NOT NULL THEN 'Fc'::text
- WHEN ftsp.oid IS NOT NULL THEN 'Fp'::text
- WHEN ftsd.oid IS NOT NULL THEN 'Fd'::text
- WHEN ftst.oid IS NOT NULL THEN 'Ft'::text
- WHEN ext.oid IS NOT NULL THEN 'Ex'::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 || COALESCE('.' || att.attname, '')
- ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname,
- fs.srvname, fdw.fdwname, col.collname, ftsc.cfgname, ftsd.dictname, ftsp.prsname,
- ftst.tmplname, ext.extname)
+ 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, colns.nspname, ftscns.nspname,
- ftsdns.nspname, ftspns.nspname, ftstns.nspname) AS nspname,
+ COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname,
CASE WHEN inhits.inhparent IS NOT NULL THEN '1' ELSE '0' END AS is_inherits,
CASE WHEN inhed.inhparent IS NOT NULL THEN '1' ELSE '0' END AS is_inherited
FROM pg_depend dep
@@ -47,39 +38,11 @@ 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
LEFT JOIN pg_type prtyp ON prtyp.oid = pr.prorettype
LEFT JOIN pg_inherits inhits ON (inhits.inhrelid=dep.refobjid)
LEFT JOIN pg_inherits inhed ON (inhed.inhparent=dep.refobjid)
-LEFT JOIN pg_collation col ON col.oid=dep.refobjid
-LEFT JOIN pg_namespace colns ON col.collnamespace=colns.oid
-LEFT JOIN pg_ts_config ftsc ON ftsc.oid=dep.refobjid
-LEFT JOIN pg_namespace ftscns ON ftsc.cfgnamespace=ftscns.oid
-LEFT JOIN pg_ts_dict ftsd ON ftsd.oid=dep.refobjid
-LEFT JOIN pg_namespace ftsdns ON ftsd.dictnamespace=ftsdns.oid
-LEFT JOIN pg_ts_parser ftsp ON ftsp.oid=dep.refobjid
-LEFT JOIN pg_namespace ftspns ON ftsp.prsnamespace=ftspns.oid
-LEFT JOIN pg_ts_template ftst ON ftst.oid=dep.refobjid
-LEFT JOIN pg_namespace ftstns ON ftst.tmplnamespace=ftstns.oid
-LEFT JOIN pg_extension ext ON ext.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_foreign_server', 'pg_foreign_data_wrapper',
- 'pg_collation', 'pg_ts_config', 'pg_ts_dict', 'pg_ts_parser', 'pg_ts_template', 'pg_extension'))
-UNION
-SELECT DISTINCT dep.deptype, dep.refclassid, dep.refobjid, cl.relkind, ad.adbin, ad.adsrc,
- CASE WHEN cl.relkind IS NOT NULL THEN CASE WHEN cl.relkind = 'r' THEN cl.relkind || COALESCE(dep.refobjsubid::text, '') ELSE cl.relkind END
- ELSE '' END AS type,
- NULL AS ownertable,
- CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || COALESCE('.' || att.attname, '')
- ELSE cl.relname END AS refname,
- nsc.nspname AS nspname, '0' AS is_inherits, '0' AS is_inherited
-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_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum
-WHERE dep.objid IN (SELECT oid FROM pg_rewrite WHERE ev_class={{object_id}}) AND cl.relkind not in ('v', 'm')
-ORDER BY refclassid, relkind
+ 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper'))
+ORDER BY refclassid, cl.relkind
diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependents.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependents.sql
index 71b0a1e..f8a1bee 100644
--- a/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependents.sql
+++ b/web/pgadmin/browser/server_groups/servers/templates/depends/gpdb/5_plus/dependents.sql
@@ -1,32 +1,22 @@
+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 CASE WHEN cl.relkind = 'r' THEN cl.relkind || COALESCE(dep.objsubid::text, '') ELSE cl.relkind END
- WHEN tg.oid IS NOT NULL THEN 'Tr'::text
- WHEN ty.oid IS NOT NULL THEN CASE WHEN ty.typtype = 'd' THEN 'd'::text ELSE 'Ty'::text END
+ 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 AND (prtyp.typname = 'trigger' OR prtyp.typname = 'event_trigger') THEN 'Pt'::text
- WHEN pr.oid IS NOT NULL THEN 'Pf'::text
+ WHEN pr.oid IS NOT NULL AND prtyp.typname = 'trigger' THEN 't'::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 'Rl'::text
- WHEN co.oid IS NOT NULL THEN CASE WHEN co.contypid > 0 THEN 'Cd' ELSE 'C'::text || contype END
+ 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 'Fs'::text
- WHEN fdw.oid IS NOT NULL THEN 'Fw'::text
- WHEN col.oid IS NOT NULL THEN 'Co'::text
- WHEN ftsc.oid IS NOT NULL THEN 'Fc'::text
- WHEN ftsp.oid IS NOT NULL THEN 'Fp'::text
- WHEN ftsd.oid IS NOT NULL THEN 'Fd'::text
- WHEN ftst.oid IS NOT NULL THEN 'Ft'::text
- WHEN ext.oid IS NOT NULL THEN 'Ex'::text
- ELSE ''
+ 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 || COALESCE('.' || att.attname, '')
- ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname,
- fs.srvname, fdw.fdwname, col.collname, ftsc.cfgname, ftsd.dictname, ftsp.prsname,
- ftst.tmplname, ext.extname)
+ 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, colns.nspname, ftscns.nspname,
- ftsdns.nspname, ftspns.nspname, ftstns.nspname) AS nspname,
+ COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname,
CASE WHEN inhits.inhparent IS NOT NULL THEN '1' ELSE '0' END AS is_inherits,
CASE WHEN inhed.inhparent IS NOT NULL THEN '1' ELSE '0' END AS is_inherited
FROM pg_depend dep
@@ -47,25 +37,11 @@ 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
LEFT JOIN pg_type prtyp ON prtyp.oid = pr.prorettype
LEFT JOIN pg_inherits inhits ON (inhits.inhrelid=dep.objid)
LEFT JOIN pg_inherits inhed ON (inhed.inhparent=dep.objid)
-LEFT JOIN pg_collation col ON col.oid=dep.objid
-LEFT JOIN pg_namespace colns ON col.collnamespace=colns.oid
-LEFT JOIN pg_ts_config ftsc ON ftsc.oid=dep.objid
-LEFT JOIN pg_namespace ftscns ON ftsc.cfgnamespace=ftscns.oid
-LEFT JOIN pg_ts_dict ftsd ON ftsd.oid=dep.objid
-LEFT JOIN pg_namespace ftsdns ON ftsd.dictnamespace=ftsdns.oid
-LEFT JOIN pg_ts_parser ftsp ON ftsp.oid=dep.objid
-LEFT JOIN pg_namespace ftspns ON ftsp.prsnamespace=ftspns.oid
-LEFT JOIN pg_ts_template ftst ON ftst.oid=dep.objid
-LEFT JOIN pg_namespace ftstns ON ftst.tmplnamespace=ftstns.oid
-LEFT JOIN pg_extension ext ON ext.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_foreign_server', 'pg_foreign_data_wrapper',
- 'pg_collation', 'pg_ts_config', 'pg_ts_dict', 'pg_ts_parser', 'pg_ts_template', 'pg_extension'))
-ORDER BY classid, cl.relkind
+ '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/utils.py b/web/pgadmin/browser/utils.py
index 91a7119..a570743 100644
--- a/web/pgadmin/browser/utils.py
+++ b/web/pgadmin/browser/utils.py
@@ -12,15 +12,15 @@
from abc import abstractmethod
import flask
-from flask import render_template, current_app
-from flask.views import View, MethodViewType, with_metaclass
+from flask import current_app, render_template
+from flask.views import MethodViewType, View, with_metaclass
from flask_babelex import gettext
from config import PG_DEFAULT_DRIVER
-from pgadmin.utils.ajax import make_json_response, precondition_required,\
- internal_server_error
-from pgadmin.utils.exception import ConnectionLost, SSHTunnelConnectionLost,\
- CryptKeyMissing
+from pgadmin.utils.ajax import (internal_server_error, make_json_response,
+ precondition_required)
+from pgadmin.utils.exception import (ConnectionLost, CryptKeyMissing,
+ SSHTunnelConnectionLost)
def underscore_escape(text):
@@ -471,9 +471,12 @@ class PGChildNodeView(NodeView):
"""
# Set the sql_path
- sql_path = 'depends/{0}/#{1}#'.format(
- conn.manager.server_type, conn.manager.version)
-
+ sql_path = 'depends/' + (
+ "{0}/#{0}#{1}#".format(conn.manager.server_type, conn.manager.version) if
+ conn.manager.server_type == 'gpdb' else
+ "{0}/#{1}#".format(conn.manager.server_type, conn.manager.version)
+ )
+
if where is None:
where_clause = "WHERE dep.objid={0}::oid".format(object_id)
else:
@@ -527,8 +530,11 @@ class PGChildNodeView(NodeView):
Returns: Dictionary of dependents for the selected node.
"""
# Set the sql_path
- sql_path = 'depends/{0}/#{1}#'.format(
- conn.manager.server_type, conn.manager.version)
+ sql_path = 'depends/' + (
+ "{0}/#{0}#{1}#".format(conn.manager.server_type, conn.manager.version) if
+ conn.manager.server_type == 'gpdb' else
+ "{0}/#{1}#".format(conn.manager.server_type, conn.manager.version)
+ )
if where is None:
where_clause = "WHERE dep.refobjid={0}::oid".format(object_id)
diff --git a/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/activity.sql b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/activity.sql
new file mode 100644
index 0000000..f8a255e
--- /dev/null
+++ b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/activity.sql
@@ -0,0 +1,19 @@
+/*pga4dash*/
+SELECT
+ pid,
+ datname,
+ usename,
+ application_name,
+ client_addr,
+ to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start,
+ state,
+ CASE WHEN waiting THEN '{{ _('yes') }}' ELSE '{{ _('no') }}' END AS waiting,
+ waiting_reason AS wait_event,
+ query,
+ to_char(state_change, 'YYYY-MM-DD HH24:MI:SS TZ') AS state_change,
+ to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start
+FROM
+ pg_stat_activity
+{% if did %}WHERE
+ datid = {{ did }} {% endif %}
+ORDER BY pid
\ No newline at end of file
diff --git a/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/dashboard_stats.sql b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/dashboard_stats.sql
new file mode 100644
index 0000000..e0c4079
--- /dev/null
+++ b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_6.0_plus/dashboard_stats.sql
@@ -0,0 +1,57 @@
+/*pga4dash*/
+{% set add_union = false %}
+{% if 'session_stats' in chart_names %}
+{% set add_union = true %}
+SELECT 'session_stats' AS chart_name, row_to_json(t) AS chart_data
+FROM (SELECT
+ (SELECT count(*) FROM pg_stat_activity{% if did %} WHERE datid = {{ did }} {% endif %}) AS "{{ _('Total') }}",
+ (SELECT count(*) FROM pg_stat_activity WHERE state = 'active'{% if did %} AND datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Active') }}",
+ (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle'{% if did %} AND datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Idle') }}"
+) t
+{% endif %}
+{% if add_union and 'tps_stats' in chart_names %}
+UNION ALL
+{% endif %}
+{% if 'tps_stats' in chart_names %}
+{% set add_union = true %}
+SELECT 'tps_stats' AS chart_name, row_to_json(t) AS chart_data
+FROM (SELECT
+ (SELECT sum(xact_commit) + sum(xact_rollback) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Transactions') }}",
+ (SELECT sum(xact_commit) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Commits') }}",
+ (SELECT sum(xact_rollback) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Rollbacks') }}"
+) t
+{% endif %}
+{% if add_union and 'ti_stats' in chart_names %}
+UNION ALL
+{% endif %}
+{% if 'ti_stats' in chart_names %}
+{% set add_union = true %}
+SELECT 'ti_stats' AS chart_name, row_to_json(t) AS chart_data
+FROM (SELECT
+ (SELECT sum(tup_inserted) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Inserts') }}",
+ (SELECT sum(tup_updated) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Updates') }}",
+ (SELECT sum(tup_deleted) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Deletes') }}"
+) t
+{% endif %}
+{% if add_union and 'to_stats' in chart_names %}
+UNION ALL
+{% endif %}
+{% if 'to_stats' in chart_names %}
+{% set add_union = true %}
+SELECT 'to_stats' AS chart_name, row_to_json(t) AS chart_data
+FROM (SELECT
+ (SELECT sum(tup_fetched) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Fetched') }}",
+ (SELECT sum(tup_returned) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Returned') }}"
+) t
+{% endif %}
+{% if add_union and 'bio_stats' in chart_names %}
+UNION ALL
+{% endif %}
+{% if 'bio_stats' in chart_names %}
+{% set add_union = true %}
+SELECT 'bio_stats' AS chart_name, row_to_json(t) AS chart_data
+FROM (SELECT
+ (SELECT sum(blks_read) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Reads') }}",
+ (SELECT sum(blks_hit) FROM pg_stat_database{% if did %} WHERE datname = (SELECT datname FROM pg_database WHERE oid = {{ did }}){% endif %}) AS "{{ _('Hits') }}"
+) t
+{% endif %}
diff --git a/web/pgadmin/utils/versioned_template_loader.py b/web/pgadmin/utils/versioned_template_loader.py
index bc32b36..6e1ed1e 100644
--- a/web/pgadmin/utils/versioned_template_loader.py
+++ b/web/pgadmin/utils/versioned_template_loader.py
@@ -12,6 +12,21 @@ from jinja2 import TemplateNotFound
class VersionedTemplateLoader(DispatchingJinjaLoader):
def get_source(self, environment, template):
+ '''
+ Should support the following template string format:
+
+ - connect/sql/9.0_plus/check_recovery.sql <= connect/sql/#xxx#/check_recovery.sql
+ - dashboard/sql/9.6_plus/activity.sql <= dashboard/sql/#xxx#/activity.sql
+ - dashboard/sql/gpdb_6.0_plus/activity.sql <= dashboard/sql/#xxx#xxx#/activity.sql
+ - table/sql/9.6_plus/properties.sql <= table/sql/#xxx#/properties.sql
+ - table/sql/gpdb_5.0_plus/properties.sql <= table/sql/#xxx#xxx#/properties.sql
+ - depends/pg/9.6_plus/role_dependencies.sql <= depends/xxx/#xxx#xxx#/role_dependencies.sql
+ - depends/gpdb/5_plus/role_dependencies.sql <= depends/xxx/#xxx#xxx#/role_dependencies.sql
+ - function/gpdb/sql/5_plus/get_definition.sql <= function/###/sql/#xxx#xxx#/get_definition.sql
+ - function/pg/sql/10_plus/get_definition.sql <= function/###/sql/#xxx#xxx#/get_definition.sql
+ - schemas/gpdb/5_plus/sql/acl.sql <= schemas/xxx/#xxx#xxx#/sql/acl.sql
+ - schemas/pg/9.6_plus/sql/acl.sql <= schemas/xxx/#xxx#/sql/acl.sql
+ '''
specified_version_number, exists = parse_version(template)
if not exists:
return super(VersionedTemplateLoader, self).get_source(
@@ -79,7 +94,9 @@ def get_version_mapping_directories(server_type):
"""
if server_type == 'gpdb':
return (
+ {'name': "gpdb_6.0_plus", 'number': 90424},
{'name': "gpdb_5.0_plus", 'number': 80323},
+ {'name': "6_plus", 'number': 90424},
{'name': "5_plus", 'number': 80323},
{'name': "default", 'number': 0}
)
