Hi Hackers!
This patch enables Greenplum users to see the same charts on the dashboard
as postgres users. It also adds some additional information to the DDL that
is Greenplum specific and necessary to create a new table.
Thanks!
Sarah
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
index e22e54cf..76f2b2d3 100644
---
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/__init__.py
@@ -1216,8 +1216,13 @@ class TableView(BaseTableView, DataTypeReader,
VacuumSettings):
"""
main_sql = []
+ if self.manager.gpdbversion != 0:
+ template_path = 'table/sql/#{0}#'.format(self.manager.gpdbversion)
+ else:
+ template_path = self.table_template_path
+
SQL = render_template(
- "/".join([self.table_template_path, 'properties.sql']),
+ "/".join([template_path, 'properties.sql']),
did=did, scid=scid, tid=tid,
datlastsysoid=self.datlastsysoid
)
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/create.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/create.sql
new file mode 100644
index 00000000..9bcfd598
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/create.sql
@@ -0,0 +1,168 @@
+{% import 'macros/schemas/security.macros' as SECLABEL %}
+{% import 'macros/schemas/privilege.macros' as PRIVILEGE %}
+{% import 'macros/variable.macros' as VARIABLE %}
+{% import 'column/macros/security.macros' as COLUMN_SECLABEL %}
+{% import 'column/macros/privilege.macros' as COLUMN_PRIVILEGE %}
+{% import 'table/sql/macros/constraints.macro' as CONSTRAINTS %}
+{% import 'type/macros/get_full_type_sql_format.macros' as GET_TYPE %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE STARTS HERE ======#}
+{#===========================================#}
+{#
+ If user has not provided any details but only name then
+ add empty bracket with table name
+#}
+{% set empty_bracket = ""%}
+{% if data.coll_inherits|length == 0 and data.columns|length == 0 and not
data.typname and not data.like_relation and data.primary_key|length == 0 and
data.unique_constraint|length == 0 and data.foreign_key|length == 0 and
data.check_constraint|length == 0 and data.exclude_constraint|length == 0 %}
+{% set empty_bracket = "\n(\n)"%}
+{% endif %}
+CREATE {% if data.relpersistence %}UNLOGGED {% endif %}TABLE
{{conn|qtIdent(data.schema, data.name)}}{{empty_bracket}}
+{% if data.typname %}
+ OF {{ data.typname }}
+{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or
data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+(
+{% endif %}
+{% if data.like_relation %}
+ LIKE {{ data.like_relation }}{% if data.like_default_value %}
+
+ INCLUDING DEFAULTS{% endif %}{% if data.like_constraints %}
+
+ INCLUDING CONSTRAINTS{% endif %}{% if data.like_indexes %}
+
+ INCLUDING INDEXES{% endif %}{% if data.like_storage %}
+
+ INCLUDING STORAGE{% endif %}{% if data.like_comments %}
+
+ INCLUDING COMMENTS{% endif %}{% if data.columns|length > 0 %},
+{% endif %}
+
+{% endif %}
+{### Add columns ###}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.name and c.cltype %}
+{% if loop.index != 1 %},
+{% endif %}
+ {{conn|qtIdent(c.name)}} {% if is_sql %}{{c.displaytypname}}{% else %}{{
GET_TYPE.CREATE_TYPE_SQL(conn, c.cltype, c.attlen, c.attprecision,
c.hasSqrBracket) }}{% endif %}{% if c.collspcname %} COLLATE
{{c.collspcname}}{% endif %}{% if c.attnotnull %} NOT NULL{% endif %}{% if
c.defval %} DEFAULT {{c.defval}}{% endif %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{# Macro to render for constraints #}
+{% if data.primary_key|length > 0 %}{% if data.columns|length > 0 %},{% endif
%}
+{{CONSTRAINTS.PRIMARY_KEY(conn, data.primary_key[0])}}{% endif %}{% if
data.unique_constraint|length > 0 %}{% if data.columns|length > 0 or
data.primary_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.UNIQUE(conn, data.unique_constraint)}}{% endif %}{% if
data.foreign_key|length > 0 %}{% if data.columns|length > 0 or
data.primary_key|length > 0 or data.unique_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.FOREIGN_KEY(conn, data.foreign_key)}}{% endif %}{% if
data.check_constraint|length > 0 %}{% if data.columns|length > 0 or
data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 %},{% endif %}
+{{CONSTRAINTS.CHECK(conn, data.check_constraint)}}{% endif %}{% if
data.exclude_constraint|length > 0 %}{% if data.columns|length > 0 or
data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 %},{% endif %}
+{{CONSTRAINTS.EXCLUDE(conn, data.exclude_constraint)}}{% endif %}
+{% if data.like_relation or data.coll_inherits or data.columns|length > 0 or
data.primary_key|length > 0 or data.unique_constraint|length > 0 or
data.foreign_key|length > 0 or data.check_constraint|length > 0 or
data.exclude_constraint|length > 0 %}
+
+){% endif %}{% if data.relkind is defined and data.relkind == 'p' %} PARTITION
BY {{ data.partition_scheme }} {% endif %}
+
+{### If we are inheriting it from another table(s) ###}
+{% if data.coll_inherits %}
+ INHERITS ({% for val in data.coll_inherits %}{% if loop.index != 1 %}, {%
endif %}{{val}}{% endfor %})
+{% endif %}
+WITH (
+ OIDS = {% if data.relhasoids %}TRUE{% else %}FALSE{% endif %}{% if
data.fillfactor %},
+ FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.appendonly %},
+ APPENDONLY = TRUE{% endif %}{% if data.compresslevel %},
+ COMPRESSLEVEL = {{ data.compresslevel }}{% endif %}{% if data.blocksize %},
+ BLOCKSIZE = {{ data.blocksize }}{% endif %}{% if data.orientation %},
+ ORIENTATION = {{ data.orientation.upper() }}{% endif %}{% if
data.compresstype %},
+ COMPRESSTYPE = {{ data.compresstype.upper() }}{% endif %}{% if
data.autovacuum_custom %},
+ autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{%
endif %}{% endif %}{% if data.toast_autovacuum %},
+ toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}TRUE{%
else %}FALSE{% endif %}
+{% endif %}{% if data.autovacuum_enabled and data.vacuum_table|length > 0 %}
+
+{% for opt in data.vacuum_table %}{% if opt.name and opt.value %}
+,
+ {{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}{% if data.toast_autovacuum_enabled and
data.vacuum_toast|length > 0 %}
+{% for opt in data.vacuum_toast %}{% if opt.name and opt.value %}
+,
+ toast.{{opt.name}} = {{opt.value}}{% endif %}
+{% endfor %}{% endif %}
+
+)
+{### SQL for Tablespace ###}
+{% if data.spcname %}
+TABLESPACE {{ conn|qtIdent(data.spcname) }}
+{% endif %}
+{### SQL for Distribution ###}
+{% if data.distribution %}
+DISTRIBUTED BY ({% for attrnum in data.distribution %}{% if loop.index != 1
%}, {% endif %}{{ data.columns[attrnum-1].name }}{% endfor %});
+{% else %}
+DISTRIBUTED RANDOMLY;
+{% endif %}
+
+{### Alter SQL for Owner ###}
+{% if data.relowner %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ OWNER to {{conn|qtIdent(data.relowner)}};
+{% endif %}
+{### Security Labels on Table ###}
+{% if data.seclabels and data.seclabels|length > 0 %}
+
+{% for r in data.seclabels %}
+{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }}
+{% endfor %}
+{% endif %}
+{### ACL on Table ###}
+{% if data.relacl %}
+{% for priv in data.relacl %}
+{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant,
priv.with_grant, data.schema) }}
+{% endfor %}
+{% endif %}
+{### SQL for COMMENT ###}
+{% if data.description %}
+COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}}
+ IS {{data.description|qtLiteral}};
+{% endif %}
+{#===========================================#}
+{#====== MAIN TABLE TEMPLATE ENDS HERE ======#}
+{#===========================================#}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES STARTS HERE #}
+{#===========================================#}
+{% if data.columns and data.columns|length > 0 %}
+{% for c in data.columns %}
+{% if c.description %}
+
+COMMENT ON COLUMN {{conn|qtIdent(data.schema, data.name, c.name)}}
+ IS {{c.description|qtLiteral}};
+{% endif %}
+{### Add variables to column ###}
+{% if c.attoptions and c.attoptions|length > 0 %}
+
+ALTER TABLE {{conn|qtIdent(data.schema, data.name)}}
+ {{ VARIABLE.SET(conn, 'COLUMN', c.name, c.attoptions) }}
+{% endif %}
+{### ACL ###}
+{% if c.attacl and c.attacl|length > 0 %}
+
+{% for priv in c.attacl %}
+ {{ COLUMN_PRIVILEGE.APPLY(conn, data.schema, data.name, c.name,
priv.grantee, priv.without_grant, priv.with_grant) }}
+{% endfor %}
+{% endif %}
+{### Security Lables ###}
+{% if c.seclabels and c.seclabels|length > 0 %}
+
+{% for r in c.seclabels %}
+{{ COLUMN_SECLABEL.APPLY(conn, 'COLUMN',data.schema, data.name, c.name,
r.provider, r.label) }}
+{% endfor %}
+{% endif %}
+{% endfor %}
+{% endif %}
+{#===========================================#}
+{# COLUMN SPECIFIC TEMPLATES ENDS HERE #}
+{#===========================================#}
+{#======================================#}
+{# CONSTRAINTS SPECIFIC TEMPLATES #}
+{#======================================#}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name,
data.primary_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name,
data.unique_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name,
data.foreign_key)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name,
data.check_constraint)}}
+{{CONSTRAINTS.CONSTRAINT_COMMENTS(conn, data.schema, data.name,
data.exclude_constraint)}}
diff --git
a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql
new file mode 100644
index 00000000..83176a45
--- /dev/null
+++
b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/table/sql/gpdb_5.0_plus/properties.sql
@@ -0,0 +1,82 @@
+SELECT *,
+ (CASE when pre_coll_inherits is NULL then ARRAY[]::varchar[] else
pre_coll_inherits END) as coll_inherits
+FROM (
+ SELECT rel.oid, rel.relname AS name, rel.reltablespace AS
spcoid,rel.relacl AS relacl_str,
+ (CASE WHEN length(spc.spcname) > 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, 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 E'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, NULL AS typname,
+ (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS
hastoasttable,
+ -- Added for pgAdmin4
+ (CASE WHEN (substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean THEN true ELSE false END) AS
autovacuum_custom,
+ (CASE WHEN (substring(array_to_string(tst.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)'))::boolean AND rel.reltoastrelid != 0 THEN
true ELSE false END) AS toast_autovacuum,
+
+ ARRAY[]::varchar[] AS seclabels,
+ (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE
false END) AS is_sys_table,
+
+ gdp.attrnums AS distribution
+
+ 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
+
+ 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 1ac35054..1fa18e13 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
@@ -857,13 +857,18 @@ class BaseTableView(PGChildNodeView):
if 'relacl' in data:
data['relacl'] = parse_priv_to_db(data['relacl'], self.acl)
+ if self.manager.gpdbversion != 0:
+ template_path = 'table/sql/#{0}#'.format(self.manager.gpdbversion)
+ else:
+ template_path = self.table_template_path
+
# if table is partitions then
if 'relispartition' in data and data['relispartition']:
table_sql = render_template("/".join([self.partition_template_path,
'create.sql']),
data=data, conn=self.conn)
else:
- table_sql = render_template("/".join([self.table_template_path,
+ table_sql = render_template("/".join([template_path,
'create.sql']),
data=data, conn=self.conn, is_sql=True)
diff --git a/web/pgadmin/dashboard/__init__.py
b/web/pgadmin/dashboard/__init__.py
index 8ebab16c..c2890840 100644
--- a/web/pgadmin/dashboard/__init__.py
+++ b/web/pgadmin/dashboard/__init__.py
@@ -217,9 +217,11 @@ def check_precondition(f):
# Set template path for sql scripts
g.server_type = g.manager.server_type
g.version = g.manager.version
-
- g.template_path = 'dashboard/sql/#{0}#'.format(g.version)
-
+ g.gpdbversion = g.manager.gpdbversion
+ if g.gpdbversion <> 0:
+ g.template_path = 'dashboard/sql/#{0}#'.format(g.gpdbversion)
+ else:
+ g.template_path = 'dashboard/sql/#{0}#'.format(g.version)
return f(*args, **kwargs)
return wrap
diff --git
a/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/activity.sql
b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/activity.sql
new file mode 100644
index 00000000..1a0a0243
--- /dev/null
+++ b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/activity.sql
@@ -0,0 +1,14 @@
+SELECT
+ procpid AS pid,
+ datname,
+ usename,
+ application_name,
+ client_addr,
+ to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start,
+ CASE WHEN current_query LIKE '<IDLE>%' THEN 'idle' ELSE 'active' END AS
state,
+ CASE WHEN waiting THEN '{{ _('yes') }}' ELSE '{{ _('no') }}' END AS waiting
+FROM
+ pg_stat_activity
+{% if did %}WHERE
+ datid = {{ did }} {% endif %}
+ORDER BY pid
diff --git
a/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/locks.sql
b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/locks.sql
new file mode 100644
index 00000000..511838d7
--- /dev/null
+++ b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/locks.sql
@@ -0,0 +1,22 @@
+SELECT
+ pid,
+ locktype,
+ datname,
+ relation::regclass,
+ page,
+ tuple,
+ virtualxid
+ transactionid,
+ classid::regclass,
+ objid,
+ objsubid,
+ virtualtransaction,
+ mode,
+ granted
+FROM
+ pg_locks l
+ LEFT OUTER JOIN pg_database d ON (l.database = d.oid)
+{% if did %}WHERE
+ database = {{ did }}{% endif %}
+ORDER BY
+ pid, locktype
diff --git
a/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/session_stats.sql
b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/session_stats.sql
new file mode 100644
index 00000000..533c445b
--- /dev/null
+++
b/web/pgadmin/dashboard/templates/dashboard/sql/gpdb_5.0_plus/session_stats.sql
@@ -0,0 +1,4 @@
+SELECT
+ (SELECT count(*) FROM pg_stat_activity{% if did %} WHERE datid = {{ did }}
{% endif %}) AS "{{ _('Total') }}",
+ (SELECT count(*) FROM pg_stat_activity WHERE current_query NOT LIKE
'<IDLE>%'{% if did %} AND datid = {{ did }} {% endif %}) AS "{{ _('Active')
}}",
+ (SELECT count(*) FROM pg_stat_activity WHERE current_query LIKE '<IDLE>%'{%
if did %} AND datid = {{ did }} {% endif %}) AS "{{ _('Idle') }}"
diff --git a/web/pgadmin/utils/driver/psycopg2/__init__.py
b/web/pgadmin/utils/driver/psycopg2/__init__.py
index 7cda5eff..97285796 100644
--- a/web/pgadmin/utils/driver/psycopg2/__init__.py
+++ b/web/pgadmin/utils/driver/psycopg2/__init__.py
@@ -27,7 +27,7 @@ from flask_babel import gettext
from flask_security import current_user
from pgadmin.utils.crypto import decrypt
from psycopg2.extensions import adapt, encodings
-
+from pgadmin.utils.gpdb_version import get_gpdb_version
import config
from pgadmin.model import Server, User
from pgadmin.utils.exception import ConnectionLost
@@ -483,7 +483,7 @@ Failed to fetch the version information on the established
connection to the dat
row = cur.fetchmany(1)[0]
mgr.ver = row['version']
mgr.sversion = self.conn.server_version
-
+ mgr.gpdbversion = get_gpdb_version(mgr.ver)
status = _execute(cur, """
SELECT
db.oid as did, db.datname, db.datallowconn,
pg_encoding_to_char(db.encoding) AS serverencoding,
diff --git a/web/pgadmin/utils/gpdb_version.py
b/web/pgadmin/utils/gpdb_version.py
new file mode 100644
index 00000000..dc191577
--- /dev/null
+++ b/web/pgadmin/utils/gpdb_version.py
@@ -0,0 +1,19 @@
+import re
+
+def get_gpdb_version(version_string):
+ gpdb_version = 0
+ if version_string is None:
+ return gpdb_version
+ parts = re.search('Greenplum Database (\d+\.\d+\.\d+)\W', version_string)
+ if parts and parts.group(1):
+ version_string = parts.group(1)
+ else:
+ return gpdb_version
+
+ subversions = version_string.split(".")
+ try:
+ gpdb_version = int('%d%02d%02d' % (int(subversions[0]),
int(subversions[1]), int(subversions[2])))
+ except:
+ pass
+
+ return gpdb_version
diff --git
a/web/pgadmin/utils/tests/templates/some_feature/sql/default/some_more_action_with_default.sql
b/web/pgadmin/utils/tests/templates/some_feature/sql/default/some_more_action_with_default.sql
new file mode 100644
index 00000000..45bdb3b8
--- /dev/null
+++
b/web/pgadmin/utils/tests/templates/some_feature/sql/default/some_more_action_with_default.sql
@@ -0,0 +1 @@
+Some more default SQL
diff --git
a/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_default.sql
b/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_default.sql
new file mode 100644
index 00000000..f4ba6e6b
--- /dev/null
+++
b/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_default.sql
@@ -0,0 +1 @@
+Some more default SQL for GPDB
diff --git
a/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_gpdb_5_0.sql
b/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_gpdb_5_0.sql
new file mode 100644
index 00000000..e131e3fd
--- /dev/null
+++
b/web/pgadmin/utils/tests/templates/some_feature/sql/gpdb_5.0_plus/some_action_with_gpdb_5_0.sql
@@ -0,0 +1 @@
+Some default SQL for GPDB
diff --git a/web/pgadmin/utils/tests/test_gpdb_version.py
b/web/pgadmin/utils/tests/test_gpdb_version.py
new file mode 100644
index 00000000..2b49a1be
--- /dev/null
+++ b/web/pgadmin/utils/tests/test_gpdb_version.py
@@ -0,0 +1,76 @@
+##########################################################################
+#
+# pgAdmin 4 - PostgreSQL Tools
+#
+# Copyright (C) 2013 - 2017, The pgAdmin Development Team
+# This software is released under the PostgreSQL Licence
+#
+##########################################################################
+
+import os
+
+from pgadmin.utils.gpdb_version import get_gpdb_version
+
+from pgadmin.utils.route import BaseTestGenerator
+
+
+class TestGPDBVesionLoader(BaseTestGenerator):
+ scenarios = [
+ ("GPDB 5", dict(scenario=1)),
+ ("Postgres only", dict(scenario=2)),
+ ("GPDB no beta", dict(scenario=3)),
+ ("Wrong GPDB", dict(scenario=4)),
+ ("None GPDB", dict(scenario=5)),
+ ("Empty GPDB", dict(scenario=6))
+ ]
+
+ def runTest(self):
+ if self.scenario == 1:
+ self.test_gpdb_5_version()
+ if self.scenario == 2:
+ self.test_postgres_version()
+ if self.scenario == 3:
+ self.test_gpdb_nobeta_version()
+ if self.scenario == 4:
+ self.test_wrong_gpdb_version()
+ if self.scenario == 5:
+ self.test_none_gpdb_version()
+ if self.scenario == 6:
+ self.test_empty_gpdb_version()
+
+ def test_gpdb_5_version(self):
+ version_string = "PostgreSQL 8.3.23 (Greenplum Database 5.1.0-beta.2
build commit:7256defe7f6dcf1ff81f1b5c6f62e49388edf1fb) on x86_64-pc-linux-gnu,
compiled by GCC gcc (GCC) 6.2.0 compiled on Jun 24 2017 21:16:56"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 50100)
+
+ def test_postgres_version(self):
+ version_string = "PostgreSQL 8.3.23 on x86_64-pc-linux-gnu, compiled
by GCC gcc (GCC) 6.2.0 compiled on Jun 24 2017 21:16:56"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 0)
+
+ def test_gpdb_nobeta_version(self):
+ version_string = "PostgreSQL 8.3.23 (Greenplum Database 5.0.30 build
commit:7256defe7f6dcf1ff81f1b5c6f62e49388edf1fb) on x86_64-pc-linux-gnu,
compiled by GCC gcc (GCC) 6.2.0 compiled on Jun 24 2017 21:16:56"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 50030)
+
+ def test_wrong_gpdb_version(self):
+ version_string = "PostgreSQL 8.3.23 (Greenplum Database 5.0.3aa0 build
commit:7256defe7f6dcf1ff81f1b5c6f62e49388edf1fb) on x86_64-pc-linux-gnu,
compiled by GCC gcc (GCC) 6.2.0 compiled on Jun 24 2017 21:16:56"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 0)
+
+ def test_none_gpdb_version(self):
+ version_string = None
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 0)
+
+ def test_empty_gpdb_version(self):
+ version_string = ""
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ parsed_result = get_gpdb_version(version_string)
+ self.assertEqual(parsed_result, 0)
+
diff --git a/web/pgadmin/utils/tests/test_versioned_template_loader.py
b/web/pgadmin/utils/tests/test_versioned_template_loader.py
index a0d97899..6704b380 100644
--- a/web/pgadmin/utils/tests/test_versioned_template_loader.py
+++ b/web/pgadmin/utils/tests/test_versioned_template_loader.py
@@ -23,7 +23,10 @@ class TestVersionedTemplateLoader(BaseTestGenerator):
("Render a version 9.1 template when it is present", dict(scenario=2)),
("Render a version 9.2 template when request for a higher version",
dict(scenario=3)),
("Render default version when version 9.0 was requested and only 9.1
and 9.2 are present", dict(scenario=4)),
- ("Raise error when version is smaller than available templates",
dict(scenario=5))
+ ("Raise error when version is smaller than available templates",
dict(scenario=5)),
+ ("Render a version GPDB 5.0 template when it is present",
dict(scenario=6)),
+ ("Render a version GPDB 5.0 template when it is in default",
dict(scenario=7)),
+ ("Raise error when version is gpdb but template does not exist",
dict(scenario=8))
]
def setUp(self):
@@ -40,6 +43,12 @@ class TestVersionedTemplateLoader(BaseTestGenerator):
self.test_get_source_when_version_is_9_0_and_there_are_templates_for_9_1_and_9_2_returns_default_template()
if self.scenario == 5:
self.test_raise_not_found_exception_when_postgres_version_less_than_all_available_sql_templates()
+ if self.scenario == 6:
+
self.test_get_source_when_the_version_is_gpdb_5_0_returns_gpdb_5_0_template()
+ if self.scenario == 7:
+
self.test_get_source_when_the_version_is_gpdb_5_0_returns_default_template()
+ if self.scenario == 8:
+
self.test_raise_not_found_exception_when_the_version_is_gpdb_template_not_exist()
def test_get_source_returns_a_template(self):
expected_content = "Some SQL" \
@@ -86,6 +95,31 @@ class TestVersionedTemplateLoader(BaseTestGenerator):
except TemplateNotFound:
return
+ def
test_get_source_when_the_version_is_gpdb_5_0_returns_gpdb_5_0_template(self):
+ expected_content = "Some default SQL for GPDB\n"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ sql_path = os.path.join("some_feature", "sql", "gpdb_5.0_plus",
"some_action_with_gpdb_5_0.sql")
+ content, filename, up_to_dateness = self.loader.get_source(None,
"some_feature/sql/#50000#/some_action_with_gpdb_5_0.sql")
+
+ self.assertEqual(expected_content, str(content).replace("\r", ""))
+ self.assertIn(sql_path, filename)
+
+ def
test_get_source_when_the_version_is_gpdb_5_0_returns_default_template(self):
+ expected_content = "Some more default SQL\n"
+ # For cross platform we join the SQL path (This solves the slashes
issue)
+ sql_path = os.path.join("some_feature", "sql", "default",
"some_more_action_with_default.sql")
+ content, filename, up_to_dateness = self.loader.get_source(None,
"some_feature/sql/#50000#/some_more_action_with_default.sql")
+
+ self.assertEqual(expected_content, str(content).replace("\r", ""))
+ self.assertIn(sql_path, filename)
+
+ def
test_raise_not_found_exception_when_the_version_is_gpdb_template_not_exist(self):
+ try:
+ self.loader.get_source(None,
"some_feature/sql/#50100#/some_action.sql")
+ self.fail("No exception raised")
+ except TemplateNotFound:
+ return
+
class FakeApp(Flask):
def __init__(self):
diff --git a/web/pgadmin/utils/versioned_template_loader.py
b/web/pgadmin/utils/versioned_template_loader.py
index d8cc1a11..55e8fcba 100644
--- a/web/pgadmin/utils/versioned_template_loader.py
+++ b/web/pgadmin/utils/versioned_template_loader.py
@@ -12,10 +12,11 @@ from jinja2 import TemplateNotFound
class VersionedTemplateLoader(DispatchingJinjaLoader):
+
def get_source(self, environment, template):
template_path_parts = template.split("#", 2)
- server_versions = (
+ postgres_versions = (
{'name': "10_plus", 'number': 100000},
{'name': "9.6_plus", 'number': 90600},
{'name': "9.5_plus", 'number': 90500},
@@ -27,12 +28,20 @@ class VersionedTemplateLoader(DispatchingJinjaLoader):
{'name': "default", 'number': 0}
)
+ gpdb_version = (
+ {'name': "gpdb_5.0_plus", 'number': 50000},
+ {'name': "default", 'number': 0}
+ )
+
if len(template_path_parts) == 1:
return super(VersionedTemplateLoader,
self).get_source(environment, template)
else:
- for server_version in server_versions:
- path_start, specified_version_number, file_name =
template_path_parts
+ path_start, specified_version_number, file_name =
template_path_parts
+ server_versions = postgres_versions
+ if int(specified_version_number) < 90000:
+ server_versions = gpdb_version
+ for server_version in server_versions:
if server_version['number'] > int(specified_version_number):
continue
@@ -41,4 +50,4 @@ class VersionedTemplateLoader(DispatchingJinjaLoader):
return super(VersionedTemplateLoader,
self).get_source(environment, template_path)
except TemplateNotFound:
continue
- raise TemplateNotFound(template)
\ No newline at end of file
+ raise TemplateNotFound(template)
