public inbox for [email protected]help / color / mirror / Atom feed
[pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 3+ messages / 2 participants [nested] [flat]
* [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 @ 2019-06-06 10:01 Akshay Joshi <[email protected]> 2019-06-07 10:39 ` Re: [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Akshay Joshi <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Akshay Joshi @ 2019-06-06 10:01 UTC (permalink / raw) To: pgadmin-hackers Hi Hackers, I have started adding support of PostgreSQL v12 in pgAdmin4. Attached is the patch to fix the following issues: - RM #4290: "string indices must be integers" displayed for all objects - RM #4288: View data issue. - PG v12 removes the ability to create table using WITH OIDS, so I have modified the code in GUI as well as on backend. - PG v12 removes obsolete pg_attrdef.adsrc and pg_constraint.consrc column, so fixed with appropriate functions calls. - For partitioned tables, since no storage is required for the table itself. PG v12 throws and error while specifying "TABLESPACE" in create table syntax for partitioned table, so fix that too. This is the initial patch to fix the issues that has been raised or I have found. Will continue to work on adding support for v12. Will check the release notes. -- *Thanks & Regards* *Akshay Joshi* *Sr. Software Architect* *EnterpriseDB Software India Private Limited* *Mobile: +91 976-788-8246* Attachments: [application/octet-stream] RM_4283.patch (65.5K, 3-RM_4283.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql new file mode 100644 index 00000000..b738c788 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql @@ -0,0 +1,8 @@ +SELECT + oid as conoid, conname, contype, pg_get_constraintdef(oid, true) as consrc, + connoinherit, convalidated, conislocal +FROM + pg_constraint +WHERE + conrelid={{foid}}::oid +ORDER by conname; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js index 10c0cfda..da3738a7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js @@ -342,12 +342,32 @@ define('pgadmin.node.table', [ }, cache_node: 'database', cache_level: 'database', },{ id: 'spcname', label: gettext('Tablespace'), node: 'tablespace', - type: 'text', control: 'node-list-by-name', disabled: 'inSchema', + type: 'text', control: 'node-list-by-name', mode: ['properties', 'create', 'edit'], filter: function(d) { // If tablespace name is not "pg_global" then we need to exclude them return (!(d && d.label.match(/pg_global/))); }, + deps: ['is_partitioned'], + disabled: function(m) { + if(this.node_info && 'catalog' in this.node_info) { + return true; + } + + if(!_.isUndefined(m.node_info) && !_.isUndefined(m.node_info.server) + && !_.isUndefined(m.node_info.server.version) && + m.node_info.server.version >= 120000 && + m.get('is_partitioned')) { + + setTimeout( function() { + m.set('spcname', undefined); + }, 10); + + return true; + } + + return false; + }, },{ id: 'partition', type: 'group', label: gettext('Partition'), mode: ['edit', 'create'], min_version: 100000, @@ -773,7 +793,15 @@ define('pgadmin.node.table', [ },{ id: 'relhasoids', label: gettext('Has OIDs?'), cell: 'switch', type: 'switch', mode: ['properties', 'create', 'edit'], - disabled: 'inSchema', group: gettext('advanced'), + group: gettext('advanced'), + disabled: function(m) { + if(!_.isUndefined(m.node_info) && !_.isUndefined(m.node_info.server) + && !_.isUndefined(m.node_info.server.version) && + m.node_info.server.version >= 120000) + return true; + + return m.inSchema(); + }, },{ id: 'relpersistence', label: gettext('Unlogged?'), cell: 'switch', type: 'switch', mode: ['properties', 'create', 'edit'], diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql new file mode 100644 index 00000000..09812a3b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql @@ -0,0 +1,83 @@ +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 parent_schema, + nsp.nspname as schema, + pg_get_userbyid(rel.relowner) AS relowner, rel.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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, typ.typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table, + -- Added for partition table + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE '' END) AS partition_scheme, + {% if ptid %} + (CASE WHEN rel.relispartition THEN pg_get_expr(rel.relpartbound, {{ ptid }}::oid) ELSE '' END) AS partition_value, + (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS partitioned_table_name + {% else %} + pg_get_expr(rel.relpartbound, rel.oid) 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.reloftype=typ.oid + LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid + LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid +WHERE rel.relispartition AND 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/partitions/sql/ppas/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/ppas/12_plus/properties.sql new file mode 100644 index 00000000..09812a3b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/ppas/12_plus/properties.sql @@ -0,0 +1,83 @@ +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 parent_schema, + nsp.nspname as schema, + pg_get_userbyid(rel.relowner) AS relowner, rel.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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, typ.typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table, + -- Added for partition table + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE '' END) AS partition_scheme, + {% if ptid %} + (CASE WHEN rel.relispartition THEN pg_get_expr(rel.relpartbound, {{ ptid }}::oid) ELSE '' END) AS partition_value, + (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS partitioned_table_name + {% else %} + pg_get_expr(rel.relpartbound, rel.oid) 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.reloftype=typ.oid + LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid + LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid +WHERE rel.relispartition AND 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/12_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql new file mode 100644 index 00000000..cb400542 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql @@ -0,0 +1,182 @@ +{% import 'macros/schemas/security.macros' as SECLABEL %} +{% import 'macros/schemas/privilege.macros' as PRIVILEGE %} +{% import 'macros/variable.macros' as VARIABLE %} +{% import 'columns/macros/security.macros' as COLUMN_SECLABEL %} +{% import 'columns/macros/privilege.macros' as COLUMN_PRIVILEGE %} +{% import 'tables/sql/macros/constraints.macro' as CONSTRAINTS %} +{% import 'types/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 %} +{% set with_clause = false%} +{% if data.fillfactor or data.autovacuum_custom or data.autovacuum_enabled or data.toast_autovacuum or data.toast_autovacuum_enabled or (data.autovacuum_enabled and data.vacuum_table|length > 0) or (data.toast_autovacuum_enabled and data.vacuum_toast|length > 0) %} +{% set with_clause = true%} +{% 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 c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% 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 %} +{% if c.attidentity and c.attidentity != '' %} +{% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %} +{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %} +{% if c.seqincrement is defined and c.seqcycle %} +CYCLE {% endif %}{% if c.seqincrement is defined and c.seqincrement|int(-1) > -1 %} +INCREMENT {{c.seqincrement|int}} {% endif %}{% if c.seqstart is defined and c.seqstart|int(-1) > -1%} +START {{c.seqstart|int}} {% endif %}{% if c.seqmin is defined and c.seqmin|int(-1) > -1%} +MINVALUE {{c.seqmin|int}} {% endif %}{% if c.seqmax is defined and c.seqmax|int(-1) > -1%} +MAXVALUE {{c.seqmax|int}} {% endif %}{% if c.seqcache is defined and c.seqcache|int(-1) > -1%} +CACHE {{c.seqcache|int}} {% endif %} +{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %}){% endif %} +{% endif %} +{% if not loop.last %}, +{% 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 not data.coll_inherits and (not data.spcname or (data.spcname and data.is_partitioned)) and not with_clause %};{% 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 %}){% if not data.spcname and not with_clause %};{% endif %} +{% endif %} + +{% if with_clause %} +{% set add_comma = false%} +WITH ( +{% if data.fillfactor %}{% set add_comma = true%} + FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.autovacuum_custom %} +{% if add_comma %}, +{% endif %} + autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% set add_comma = true%}{% endif %}{% if data.toast_autovacuum %} +{% if add_comma %}, +{% endif %} + 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 %} + +{% if data.spcname and not data.is_partitioned %}){% else %});{% endif %} + +{% endif %} +{### SQL for Tablespace ###} +{% if data.spcname and not data.is_partitioned %} +TABLESPACE {{ conn|qtIdent(data.spcname) }}; +{% 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/tables/sql/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql new file mode 100644 index 00000000..b0d48c61 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql @@ -0,0 +1,75 @@ +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.relkind, + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + 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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, + CASE WHEN typ.typname IS NOT NULL THEN (select quote_ident(nspname) FROM pg_namespace WHERE oid = {{scid}}::oid )||'.'||quote_ident(typ.typname) ELSE typ.typname END AS typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table + -- Added for partition table + {% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% 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.reloftype=typ.oid +WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid +AND NOT rel.relispartition +{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %} +ORDER BY rel.relname; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql new file mode 100644 index 00000000..d12a8385 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql @@ -0,0 +1,211 @@ +{% import 'macros/schemas/security.macros' as SECLABEL %} +{% import 'macros/schemas/privilege.macros' as PRIVILEGE %} +{% import 'macros/variable.macros' as VARIABLE %} +{#####################################################} +{## Rename table ##} +{#####################################################} +{% if data.name and data.name != o_data.name %} +ALTER TABLE {{conn|qtIdent(o_data.schema, o_data.name)}} + RENAME TO {{conn|qtIdent(data.name)}}; + +{% endif %} +{#####################################################} +{## Change table schema ##} +{#####################################################} +{% if data.schema and data.schema != o_data.schema %} +ALTER TABLE {{conn|qtIdent(o_data.schema, data.name)}} + SET SCHEMA {{conn|qtIdent(data.schema)}}; + +{% endif %} +{#####################################################} +{## Change table owner ##} +{#####################################################} +{% if data.relowner and data.relowner != o_data.relowner %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + OWNER TO {{conn|qtIdent(data.relowner)}}; + +{% endif %} +{#####################################################} +{## Update Inherits table definition ##} +{#####################################################} +{% if data.coll_inherits_added|length > 0 %} +{% for val in data.coll_inherits_added %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + INHERIT {{val}}; + +{% endfor %} +{% endif %} +{% if data.coll_inherits_removed|length > 0 %} +{% for val in data.coll_inherits_removed %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + NO INHERIT {{val}}; + +{% endfor %} +{% endif %} +{#####################################################} +{## Change tablespace ##} +{#####################################################} +{% if data.spcname and data.spcname != o_data.spcname %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + SET TABLESPACE {{conn|qtIdent(data.spcname)}}; + +{% endif %} +{#####################################################} +{## change fillfactore settings ##} +{#####################################################} +{% if data.fillfactor and data.fillfactor != o_data.fillfactor %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + SET (FILLFACTOR={{data.fillfactor}}); + +{% endif %} +{###############################} +{## Table AutoVacuum settings ##} +{###############################} +{% if data.vacuum_table is defined and data.vacuum_table.set_values|length > 0 %} +{% set has_vacuum_set = true %} +{% endif %} +{% if data.vacuum_table is defined and data.vacuum_table.reset_values|length > 0 %} +{% set has_vacuum_reset = true %} +{% endif %} +{% if o_data.autovacuum_custom and data.autovacuum_custom == false %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( + autovacuum_enabled, + autovacuum_analyze_scale_factor, + autovacuum_analyze_threshold, + autovacuum_freeze_max_age, + autovacuum_vacuum_cost_delay, + autovacuum_vacuum_cost_limit, + autovacuum_vacuum_scale_factor, + autovacuum_vacuum_threshold, + autovacuum_freeze_min_age, + autovacuum_freeze_table_age +); +{% else %} +{% if data.autovacuum_enabled is defined or has_vacuum_set %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET ( +{% if data.autovacuum_enabled is defined and data.autovacuum_enabled != o_data.autovacuum_enabled %} + autovacuum_enabled = {% if data.autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_set %}, +{% endif %} +{% endif %} +{% if has_vacuum_set %} +{% for opt in data.vacuum_table.set_values %}{% if opt.name and opt.value %} + {{opt.name}} = {{opt.value}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} +{% endif %} + +); +{% endif %} +{% if has_vacuum_reset %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( +{% for opt in data.vacuum_table.reset_values %}{% if opt.name %} + {{opt.name}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} + +); +{% endif %} +{% endif %} +{#####################################} +{## Toast table AutoVacuum settings ##} +{#####################################} +{% if data.vacuum_toast is defined and data.vacuum_toast.set_values|length > 0 %} +{% set has_vacuum_toast_set = true %} +{% endif %} +{% if data.vacuum_toast is defined and data.vacuum_toast.reset_values|length > 0 %} +{% set has_vacuum_toast_reset = true %} +{% endif %} +{% if o_data.toast_autovacuum and data.toast_autovacuum == false %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( + toast.autovacuum_enabled, + toast.autovacuum_freeze_max_age, + toast.autovacuum_vacuum_cost_delay, + toast.autovacuum_vacuum_cost_limit, + toast.autovacuum_vacuum_scale_factor, + toast.autovacuum_vacuum_threshold, + toast.autovacuum_freeze_min_age, + toast.autovacuum_freeze_table_age, + toast.autovacuum_analyze_threshold, + toast.autovacuum_analyze_scale_factor +); +{% else %} +{% if data.toast_autovacuum_enabled is defined or has_vacuum_toast_set %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET ( +{% if data.toast_autovacuum_enabled is defined and data.toast_autovacuum_enabled != o_data.toast_autovacuum_enabled %} + toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_toast_set %}, +{% endif %} +{% endif %} +{% if has_vacuum_toast_set %} +{% for opt in data.vacuum_toast.set_values %}{% if opt.name and opt.value %} + toast.{{opt.name}} = {{opt.value}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} +{% endif %} + +); +{% endif %} +{% if has_vacuum_toast_reset %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( +{% for opt in data.vacuum_toast.reset_values %}{% if opt.name %} + toast.{{opt.name}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} + +); +{% endif %} +{% endif %} +{#####################################################} +{## Change table comments ##} +{#####################################################} +{% if data.description is defined and data.description != o_data.description %} +COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}} + IS {{data.description|qtLiteral}}; + +{% endif %} +{#####################################################} +{## Update table Privileges ##} +{#####################################################} +{% if data.relacl %} +{% if 'deleted' in data.relacl %} +{% for priv in data.relacl.deleted %} +{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.relacl %} +{% for priv in data.relacl.changed %} +{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }} +{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }} +{% endfor %} +{% endif %} +{% if 'added' in data.relacl %} +{% for priv in data.relacl.added %} +{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }} +{% endfor %} +{% endif %} +{% endif %} +{#####################################################} +{## Update table SecurityLabel ##} +{#####################################################} +{% 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.UNSET(conn, 'TABLE', data.name, r.provider, data.schema) }} +{% endfor %} +{% endif %} +{% if 'added' in seclabels and seclabels.added|length > 0 %} +{% for r in seclabels.added %} +{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }} +{% endfor %} +{% endif %} +{% if 'changed' in seclabels and seclabels.changed|length > 0 %} +{% for r in seclabels.changed %} +{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }} +{% endfor %} +{% endif %} + +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py index f599892b..efb1986e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py @@ -35,7 +35,10 @@ class TestColumnAclSql(SQLTemplateTestBase): self.table_id, self.column_id = cursor.fetchone() def generate_sql(self, version): - template_file = self.get_template_file(version, "acl.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "columns", "sql") + template_file = self.get_template_file(version, file_path, + "acl.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -47,10 +50,3 @@ class TestColumnAclSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "columns", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py index c098605e..8da8f12e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py @@ -30,7 +30,10 @@ class TestColumnPropertiesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "properties.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "columns", "sql") + template_file = self.get_template_file(version, file_path, + "properties.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -47,10 +50,3 @@ class TestColumnPropertiesSql(SQLTemplateTestBase): self.assertEqual('some_column', first_row['name']) self.assertEqual('character varying', first_row['cltype']) self.assertEqual(3, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "columns", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py index 4b995e93..f3523591 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py @@ -30,7 +30,10 @@ class TestTablesAclSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "acl.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "acl.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -53,10 +56,3 @@ class TestTablesAclSql(SQLTemplateTestBase): self.assertEqual(['r'], new_acl_map['privileges']) self.assertEqual([False], new_acl_map['grantable']) return public_acls - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py index e14df0d6..9ef0e76f 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py @@ -28,7 +28,10 @@ class TestTablesNodeSql(SQLTemplateTestBase): pass def generate_sql(self, version): - template_file = self.get_template_file(version, "nodes.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "nodes.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id) @@ -50,10 +53,3 @@ class TestTablesNodeSql(SQLTemplateTestBase): # triggercount is sometimes returned as a string for some reason self.assertEqual(0, long(triggercount)) self.assertIsNotNone(long(has_enable_triggers)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py index f1323a3a..fc349bd3 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py @@ -44,7 +44,10 @@ class TestTablesPropertiesSql(SQLTemplateTestBase): self.assertEqual([], first_row['coll_inherits']) def generate_sql(self, version): - template_file = self.get_template_file(version, "properties.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "properties.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -66,10 +69,3 @@ class TestTablesPropertiesSql(SQLTemplateTestBase): cursor.execute("SELECT oid FROM pg_class where relname='test_table'") self.table_id = cursor.fetchone()[0] - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py index beefe1b0..1f29fad8 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py @@ -35,7 +35,10 @@ class TestTriggerGetOidSql(SQLTemplateTestBase): self.table_id, self.column_id = cursor.fetchone() def generate_sql(self, version): - template_file = self.get_template_file(version, "get_oid.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "triggers", "sql") + template_file = self.get_template_file(version, file_path, + "get_oid.sql") jinja2.filters.FILTERS['qtLiteral'] = lambda value: "NULL" template = file_as_template(template_file) @@ -47,10 +50,3 @@ class TestTriggerGetOidSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "triggers", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py index 6ce35481..92e80dc9 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py @@ -30,7 +30,10 @@ class TestTriggerNodesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "nodes.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "triggers", "sql") + template_file = self.get_template_file(version, file_path, + "nodes.sql") template = file_as_template(template_file) sql = template.render(tid=self.table_id) @@ -38,10 +41,3 @@ class TestTriggerNodesSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "triggers", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql new file mode 100644 index 00000000..0b6f71f8 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql @@ -0,0 +1,45 @@ +SELECT DISTINCT dep.deptype, dep.refclassid, cl.relkind, ad.adbin, pg_get_expr(ad.adbin, ad.adrelid) as adsrc, + CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.refobjsubid::character varying, '') + WHEN tg.oid IS NOT NULL THEN 'T'::text + WHEN ty.oid IS NOT NULL AND ty.typbasetype = 0 THEN 'y'::text + WHEN ty.oid IS NOT NULL AND ty.typbasetype != 0 THEN 'd'::text + WHEN ns.oid IS NOT NULL THEN 'n'::text + WHEN pr.oid IS NOT NULL THEN 'p'::text + WHEN la.oid IS NOT NULL THEN 'l'::text + WHEN rw.oid IS NOT NULL THEN 'R'::text + WHEN co.oid IS NOT NULL THEN 'C'::text || contype + WHEN ad.oid IS NOT NULL THEN 'A'::text + WHEN fs.oid IS NOT NULL THEN 'F'::text + WHEN fdw.oid IS NOT NULL THEN 'f'::text + ELSE '' + END AS type, + COALESCE(coc.relname, clrw.relname) AS ownertable, + CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || '.' || att.attname + ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) + END AS refname, + COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +FROM pg_depend dep +LEFT JOIN pg_class cl ON dep.refobjid=cl.oid +LEFT JOIN pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum +LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +LEFT JOIN pg_proc pr ON dep.refobjid=pr.oid +LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +LEFT JOIN pg_trigger tg ON dep.refobjid=tg.oid +LEFT JOIN pg_type ty ON dep.refobjid=ty.oid +LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +LEFT JOIN pg_constraint co ON dep.refobjid=co.oid +LEFT JOIN pg_class coc ON co.conrelid=coc.oid +LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +LEFT JOIN pg_rewrite rw ON dep.refobjid=rw.oid +LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +LEFT JOIN pg_language la ON dep.refobjid=la.oid +LEFT JOIN pg_namespace ns ON dep.refobjid=ns.oid +LEFT JOIN pg_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum +LEFT JOIN pg_foreign_server fs ON fs.oid=dep.refobjid +LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.refobjid +{{where_clause}} AND +refclassid IN ( SELECT oid FROM pg_class WHERE relname IN + ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace', + 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper')) +ORDER BY refclassid, cl.relkind diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql new file mode 100644 index 00000000..c5c87b7f --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql @@ -0,0 +1,44 @@ +SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, pg_get_expr(ad.adbin, ad.adrelid) as adsrc, + CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '') + WHEN tg.oid IS NOT NULL THEN 'T'::text + WHEN ty.oid IS NOT NULL THEN 'y'::text + WHEN ns.oid IS NOT NULL THEN 'n'::text + WHEN pr.oid IS NOT NULL THEN 'p'::text + WHEN la.oid IS NOT NULL THEN 'l'::text + WHEN rw.oid IS NOT NULL THEN 'R'::text + WHEN co.oid IS NOT NULL THEN 'C'::text || contype + WHEN ad.oid IS NOT NULL THEN 'A'::text + WHEN fs.oid IS NOT NULL THEN 'F'::text + WHEN fdw.oid IS NOT NULL THEN 'f'::text + ELSE '' + END AS type, + COALESCE(coc.relname, clrw.relname) AS ownertable, + CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || '.' || att.attname + ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) + END AS refname, + COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +FROM pg_depend dep +LEFT JOIN pg_class cl ON dep.objid=cl.oid +LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum +LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +LEFT JOIN pg_proc pr ON dep.objid=pr.oid +LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +LEFT JOIN pg_trigger tg ON dep.objid=tg.oid +LEFT JOIN pg_type ty ON dep.objid=ty.oid +LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +LEFT JOIN pg_constraint co ON dep.objid=co.oid +LEFT JOIN pg_class coc ON co.conrelid=coc.oid +LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid +LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +LEFT JOIN pg_language la ON dep.objid=la.oid +LEFT JOIN pg_namespace ns ON dep.objid=ns.oid +LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid +LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid +LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid +{{where_clause}} AND +classid IN ( SELECT oid FROM pg_class WHERE relname IN + ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace', + 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper')) +ORDER BY classid, cl.relkind diff --git a/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py b/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py index 4759101f..cf49437a 100644 --- a/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py +++ b/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py @@ -31,7 +31,10 @@ class TestDependenciesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "dependencies.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "depends", "sql") + template_file = self.get_template_file(version, file_path, + "dependencies.sql") template = file_as_template(template_file) sql = template.render( where_clause="WHERE dep.objid=%s::oid" % self.table_id) @@ -47,8 +50,3 @@ class TestDependenciesSql(SQLTemplateTestBase): self.assertEqual('n', first_row["deptype"]) self.assertEqual('public', first_row["refname"]) - - @staticmethod - def get_template_file(version, filename): - return os.path.join(os.path.dirname(__file__), "..", "templates", - "depends", "sql", version, filename) diff --git a/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py b/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py index d4b58a46..fce2e910 100644 --- a/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py +++ b/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py @@ -31,7 +31,10 @@ class TestDependentsSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "dependents.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "depends", "sql") + template_file = self.get_template_file(version, file_path, + "dependents.sql") template = file_as_template(template_file) sql = template.render( where_clause="WHERE dep.objid=%s::oid" % self.table_id) @@ -47,8 +50,3 @@ class TestDependentsSql(SQLTemplateTestBase): self.assertEqual('n', first_row["deptype"]) self.assertEqual('test_table', first_row["refname"]) - - @staticmethod - def get_template_file(version, filename): - return os.path.join(os.path.dirname(__file__), "..", "templates", - "depends", "sql", version, filename) diff --git a/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql b/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql index 580c3622..2eef803b 100644 --- a/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql +++ b/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql @@ -5,7 +5,7 @@ SELECT nsp.nspname schema_name, att.attname column_name, att.atttypid::regtype::text type_name, att.atthasdef AS has_default, - def.adsrc as default + pg_get_expr(def.adbin, def.adrelid) as default FROM pg_catalog.pg_attribute att INNER JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid @@ -26,7 +26,7 @@ SELECT nsp.nspname schema_name, att.attname column_name, att.atttypid::regtype::text type_name, att.atthasdef AS has_default, - def.adsrc as default + pg_get_expr(def.adbin, def.adrelid) as default FROM pg_catalog.pg_attribute att INNER JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py index b3b56cb0..d4b0700f 100644 --- a/web/pgadmin/tools/sqleditor/command.py +++ b/web/pgadmin/tools/sqleditor/command.py @@ -615,8 +615,14 @@ class TableCommand(GridCommand): This function checks whether the table has oids or not. """ driver = get_driver(PG_DEFAULT_DRIVER) + manager = driver.connection_manager(self.sid) + + # Remove the special behavior of OID columns from + # PostgreSQL 12 onwards, so returning False. + if manager.sversion >= 120000: + return False + if default_conn is None: - manager = driver.connection_manager(self.sid) conn = manager.connection(did=self.did, conn_id=self.conn_id) else: conn = default_conn diff --git a/web/pgadmin/utils/versioned_template_loader.py b/web/pgadmin/utils/versioned_template_loader.py index b6d00458..3b47757d 100644 --- a/web/pgadmin/utils/versioned_template_loader.py +++ b/web/pgadmin/utils/versioned_template_loader.py @@ -68,6 +68,15 @@ def get_version_mapping(template): if len(template_path_parts) == 4: _, server_type, _, _ = template_path_parts + return get_version_mapping_directories(server_type) + + +def get_version_mapping_directories(server_type): + """ + This function will return all the version mapping directories + :param server_type: + :return: + """ if server_type == 'gpdb': return ( {'name': "gpdb_5.0_plus", 'number': 80323}, @@ -75,7 +84,8 @@ def get_version_mapping(template): {'name': "default", 'number': 0} ) - return ({'name': "11_plus", 'number': 110000}, + return ({'name': "12_plus", 'number': 120000}, + {'name': "11_plus", 'number': 110000}, {'name': "10_plus", 'number': 100000}, {'name': "9.6_plus", 'number': 90600}, {'name': "9.5_plus", 'number': 90500}, diff --git a/web/regression/python_test_utils/sql_template_test_base.py b/web/regression/python_test_utils/sql_template_test_base.py index 3dbd1f18..fe0b2a2e 100644 --- a/web/regression/python_test_utils/sql_template_test_base.py +++ b/web/regression/python_test_utils/sql_template_test_base.py @@ -7,9 +7,13 @@ # ########################################################################## +import os + from pgadmin.utils.route import BaseTestGenerator from regression.python_test_utils import test_utils from pgadmin.utils.driver import DriverRegistry +from pgadmin.utils.versioned_template_loader \ + import get_version_mapping_directories DriverRegistry.load_drivers() @@ -22,7 +26,6 @@ class SQLTemplateTestBase(BaseTestGenerator): def __init__(self): super(SQLTemplateTestBase, self).__init__() self.database_name = -1 - self.versions_to_test = -1 def test_setup(self, connection, cursor): pass @@ -41,19 +44,37 @@ class SQLTemplateTestBase(BaseTestGenerator): test_utils.create_table(self.server, database_name, "test_table") self.database_name = database_name - if connection.server_version < 90100: - self.versions_to_test = ['default'] - else: - self.versions_to_test = ['9.1_plus'] - cursor = connection.cursor() self.test_setup(connection, cursor) - for version in self.versions_to_test: - sql = self.generate_sql(version) + sql = self.generate_sql(connection.server_version) + + cursor = connection.cursor() + cursor.execute(sql) + fetch_result = cursor.fetchall() + + self.assertions(fetch_result, cursor.description) + + def get_template_file(self, version, file_path, filename): + """ + This function check the specified file in the server mapping directory + and if file exists then return that path. + :param version: + :param file_path: + :param filename: + :return: + """ + # Iterate all the mapping directories and check the file is exist + # in the specified folder. If it exists then return the path. + for directory in get_version_mapping_directories(self.server['type']): + if directory['number'] > version: + continue - cursor = connection.cursor() - cursor.execute(sql) - fetch_result = cursor.fetchall() + template_path = '/'.join([ + file_path, + directory['name'], + filename + ]) - self.assertions(fetch_result, cursor.description) + if os.path.exists(template_path): + return template_path ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 2019-06-06 10:01 [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Akshay Joshi <[email protected]> @ 2019-06-07 10:39 ` Akshay Joshi <[email protected]> 2019-06-10 13:25 ` Re: [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Dave Page <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Akshay Joshi @ 2019-06-07 10:39 UTC (permalink / raw) To: pgadmin-hackers Hi Hackers, Today I have read the https://www.postgresql.org/docs/release/12.0/ release note. I have created some new feature request and fixed one issue. Following are the issues that I have fixed: - Fixed existing issue to drop/delete partitions of the partition table. - Allow foreign keys to reference partitioned tables. Created following issues, not sure we need to consider it: 1. #4333 <https://redmine.postgresql.org/issues/4333; Add support for function selectivity. 2. #4334 <https://redmine.postgresql.org/issues/4334; Add support for generated columns. 3. #4335 <https://redmine.postgresql.org/issues/4335; Add EXPLAIN option SETTINGS and SUMMARY Attached is the complete patch, ignore the previous patch. Please review it. On Thu, Jun 6, 2019 at 3:31 PM Akshay Joshi <[email protected]> wrote: > Hi Hackers, > > I have started adding support of PostgreSQL v12 in pgAdmin4. Attached is > the patch to fix the following issues: > > - RM #4290: "string indices must be integers" displayed for all objects > - RM #4288: View data issue. > - PG v12 removes the ability to create table using WITH OIDS, so I > have modified the code in GUI as well as on backend. > - PG v12 removes obsolete pg_attrdef.adsrc and pg_constraint.consrc > column, so fixed with appropriate functions calls. > - For partitioned tables, since no storage is required for the table > itself. PG v12 throws and error while specifying "TABLESPACE" in create > table syntax for partitioned table, so fix that too. > > This is the initial patch to fix the issues that has been raised or I have > found. Will continue to work on adding support for v12. Will check the > release notes. > > -- > *Thanks & Regards* > *Akshay Joshi* > > *Sr. Software Architect* > *EnterpriseDB Software India Private Limited* > *Mobile: +91 976-788-8246* > -- *Thanks & Regards* *Akshay Joshi* *Sr. Software Architect* *EnterpriseDB Software India Private Limited* *Mobile: +91 976-788-8246* Attachments: [application/octet-stream] RM_4283_v2.patch (71.0K, 3-RM_4283_v2.patch) download | inline diff: diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql new file mode 100644 index 00000000..b738c788 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/foreign_tables/templates/foreign_tables/sql/12_plus/get_constraints.sql @@ -0,0 +1,8 @@ +SELECT + oid as conoid, conname, contype, pg_get_constraintdef(oid, true) as consrc, + connoinherit, convalidated, conislocal +FROM + pg_constraint +WHERE + conrelid={{foid}}::oid +ORDER by conname; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py index 61b413ae..49ec7b4c 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/partitions/__init__.py @@ -190,7 +190,7 @@ class PartitionsView(BaseTableView, DataTypeReader, VacuumSettings): {'get': 'properties', 'delete': 'delete', 'put': 'update'}, {'get': 'list', 'post': 'create'} ], - 'delete': [{'delete': 'delete'}], + 'delete': [{'delete': 'delete'}, {'delete': 'delete'}], 'nodes': [{'get': 'nodes'}, {'get': 'nodes'}], 'children': [{'get': 'children'}], 'sql': [{'get': 'sql'}], @@ -591,7 +591,7 @@ class PartitionsView(BaseTableView, DataTypeReader, VacuumSettings): return internal_server_error(errormsg=str(e)) @BaseTableView.check_precondition - def delete(self, gid, sid, did, scid, tid, ptid): + def delete(self, gid, sid, did, scid, tid, ptid=None): """ This function will delete the table object @@ -601,31 +601,47 @@ class PartitionsView(BaseTableView, DataTypeReader, VacuumSettings): did: Database ID scid: Schema ID tid: Table ID + ptid: Partition Table ID """ - - try: - SQL = render_template( - "/".join([self.partition_template_path, 'properties.sql']), - did=did, scid=scid, tid=tid, ptid=ptid, - datlastsysoid=self.datlastsysoid + if ptid is None: + data = request.form if request.form else json.loads( + request.data, encoding='utf-8' ) - status, res = self.conn.execute_dict(SQL) - if not status: - return internal_server_error(errormsg=res) + else: + data = {'ids': [ptid]} - if not res['rows']: - return make_json_response( - success=0, - errormsg=gettext( - 'Error: Object not found.' - ), - info=gettext( - 'The specified partition could not be found.\n' - ) + try: + for ptid in data['ids']: + SQL = render_template( + "/".join([self.partition_template_path, 'properties.sql']), + did=did, scid=scid, tid=tid, ptid=ptid, + datlastsysoid=self.datlastsysoid ) + status, res = self.conn.execute_dict(SQL) + if not status: + return internal_server_error(errormsg=res) + + if not res['rows']: + return make_json_response( + success=0, + errormsg=gettext( + 'Error: Object not found.' + ), + info=gettext( + 'The specified partition could not be found.\n' + ) + ) + + status, res = super(PartitionsView, self).delete( + gid, sid, did, scid, tid, res) + + if not status: + return internal_server_error(errormsg=res) - return super(PartitionsView, self).delete( - gid, sid, did, scid, tid, res) + return make_json_response( + success=1, + info=gettext("Partition dropped") + ) except Exception as e: return internal_server_error(errormsg=str(e)) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js index 10c0cfda..da3738a7 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/static/js/table.js @@ -342,12 +342,32 @@ define('pgadmin.node.table', [ }, cache_node: 'database', cache_level: 'database', },{ id: 'spcname', label: gettext('Tablespace'), node: 'tablespace', - type: 'text', control: 'node-list-by-name', disabled: 'inSchema', + type: 'text', control: 'node-list-by-name', mode: ['properties', 'create', 'edit'], filter: function(d) { // If tablespace name is not "pg_global" then we need to exclude them return (!(d && d.label.match(/pg_global/))); }, + deps: ['is_partitioned'], + disabled: function(m) { + if(this.node_info && 'catalog' in this.node_info) { + return true; + } + + if(!_.isUndefined(m.node_info) && !_.isUndefined(m.node_info.server) + && !_.isUndefined(m.node_info.server.version) && + m.node_info.server.version >= 120000 && + m.get('is_partitioned')) { + + setTimeout( function() { + m.set('spcname', undefined); + }, 10); + + return true; + } + + return false; + }, },{ id: 'partition', type: 'group', label: gettext('Partition'), mode: ['edit', 'create'], min_version: 100000, @@ -773,7 +793,15 @@ define('pgadmin.node.table', [ },{ id: 'relhasoids', label: gettext('Has OIDs?'), cell: 'switch', type: 'switch', mode: ['properties', 'create', 'edit'], - disabled: 'inSchema', group: gettext('advanced'), + group: gettext('advanced'), + disabled: function(m) { + if(!_.isUndefined(m.node_info) && !_.isUndefined(m.node_info.server) + && !_.isUndefined(m.node_info.server.version) && + m.node_info.server.version >= 120000) + return true; + + return m.inSchema(); + }, },{ id: 'relpersistence', label: gettext('Unlogged?'), cell: 'switch', type: 'switch', mode: ['properties', 'create', 'edit'], diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql new file mode 100644 index 00000000..09812a3b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/pg/12_plus/properties.sql @@ -0,0 +1,83 @@ +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 parent_schema, + nsp.nspname as schema, + pg_get_userbyid(rel.relowner) AS relowner, rel.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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, typ.typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table, + -- Added for partition table + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE '' END) AS partition_scheme, + {% if ptid %} + (CASE WHEN rel.relispartition THEN pg_get_expr(rel.relpartbound, {{ ptid }}::oid) ELSE '' END) AS partition_value, + (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS partitioned_table_name + {% else %} + pg_get_expr(rel.relpartbound, rel.oid) 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.reloftype=typ.oid + LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid + LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid +WHERE rel.relispartition AND 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/partitions/sql/ppas/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/ppas/12_plus/properties.sql new file mode 100644 index 00000000..09812a3b --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/partitions/sql/ppas/12_plus/properties.sql @@ -0,0 +1,83 @@ +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 parent_schema, + nsp.nspname as schema, + pg_get_userbyid(rel.relowner) AS relowner, rel.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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, typ.typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table, + -- Added for partition table + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef(rel.oid::oid) ELSE '' END) AS partition_scheme, + {% if ptid %} + (CASE WHEN rel.relispartition THEN pg_get_expr(rel.relpartbound, {{ ptid }}::oid) ELSE '' END) AS partition_value, + (SELECT relname FROM pg_class WHERE oid = {{ tid }}::oid) AS partitioned_table_name + {% else %} + pg_get_expr(rel.relpartbound, rel.oid) 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.reloftype=typ.oid + LEFT JOIN pg_inherits inh ON inh.inhrelid = rel.oid + LEFT JOIN pg_namespace nsp ON rel.relnamespace = nsp.oid +WHERE rel.relispartition AND 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/12_plus/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql new file mode 100644 index 00000000..cb400542 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/create.sql @@ -0,0 +1,182 @@ +{% import 'macros/schemas/security.macros' as SECLABEL %} +{% import 'macros/schemas/privilege.macros' as PRIVILEGE %} +{% import 'macros/variable.macros' as VARIABLE %} +{% import 'columns/macros/security.macros' as COLUMN_SECLABEL %} +{% import 'columns/macros/privilege.macros' as COLUMN_PRIVILEGE %} +{% import 'tables/sql/macros/constraints.macro' as CONSTRAINTS %} +{% import 'types/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 %} +{% set with_clause = false%} +{% if data.fillfactor or data.autovacuum_custom or data.autovacuum_enabled or data.toast_autovacuum or data.toast_autovacuum_enabled or (data.autovacuum_enabled and data.vacuum_table|length > 0) or (data.toast_autovacuum_enabled and data.vacuum_toast|length > 0) %} +{% set with_clause = true%} +{% 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 c.inheritedfromtable %}-- Inherited from table {{c.inheritedfromtable}}: {% elif c.inheritedfromtype %}-- Inherited from type {{c.inheritedfromtype}}: {% 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 %} +{% if c.attidentity and c.attidentity != '' %} +{% if c.attidentity == 'a' %} GENERATED ALWAYS AS IDENTITY{% elif c.attidentity == 'd' %} GENERATED BY DEFAULT AS IDENTITY{% endif %} +{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %} ( {% endif %} +{% if c.seqincrement is defined and c.seqcycle %} +CYCLE {% endif %}{% if c.seqincrement is defined and c.seqincrement|int(-1) > -1 %} +INCREMENT {{c.seqincrement|int}} {% endif %}{% if c.seqstart is defined and c.seqstart|int(-1) > -1%} +START {{c.seqstart|int}} {% endif %}{% if c.seqmin is defined and c.seqmin|int(-1) > -1%} +MINVALUE {{c.seqmin|int}} {% endif %}{% if c.seqmax is defined and c.seqmax|int(-1) > -1%} +MAXVALUE {{c.seqmax|int}} {% endif %}{% if c.seqcache is defined and c.seqcache|int(-1) > -1%} +CACHE {{c.seqcache|int}} {% endif %} +{% if c.seqincrement or c.seqcycle or c.seqincrement or c.seqstart or c.seqmin or c.seqmax or c.seqcache %}){% endif %} +{% endif %} +{% if not loop.last %}, +{% 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 not data.coll_inherits and (not data.spcname or (data.spcname and data.is_partitioned)) and not with_clause %};{% 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 %}){% if not data.spcname and not with_clause %};{% endif %} +{% endif %} + +{% if with_clause %} +{% set add_comma = false%} +WITH ( +{% if data.fillfactor %}{% set add_comma = true%} + FILLFACTOR = {{ data.fillfactor }}{% endif %}{% if data.autovacuum_custom %} +{% if add_comma %}, +{% endif %} + autovacuum_enabled = {% if data.autovacuum_enabled %}TRUE{% else %}FALSE{% endif %}{% set add_comma = true%}{% endif %}{% if data.toast_autovacuum %} +{% if add_comma %}, +{% endif %} + 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 %} + +{% if data.spcname and not data.is_partitioned %}){% else %});{% endif %} + +{% endif %} +{### SQL for Tablespace ###} +{% if data.spcname and not data.is_partitioned %} +TABLESPACE {{ conn|qtIdent(data.spcname) }}; +{% 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/tables/sql/12_plus/get_tables_for_constraints.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/get_tables_for_constraints.sql new file mode 100644 index 00000000..99089a12 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/get_tables_for_constraints.sql @@ -0,0 +1,8 @@ +SELECT cl.oid as value, quote_ident(nspname)||'.'||quote_ident(relname) AS label +FROM pg_namespace nsp, pg_class cl +WHERE relnamespace=nsp.oid AND relkind in ('r', 'p') + AND nsp.nspname NOT LIKE E'pg\_temp\_%' + {% if not show_sysobj %} + AND (nsp.nspname NOT LIKE 'pg\_%' AND nsp.nspname NOT in ('information_schema')) + {% endif %} +ORDER BY nspname, relname diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql new file mode 100644 index 00000000..b0d48c61 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/properties.sql @@ -0,0 +1,75 @@ +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.relkind, + (CASE WHEN rel.relkind = 'p' THEN true ELSE false END) AS is_partitioned, + 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, + (CASE WHEN rel.relpersistence = 'u' THEN true ELSE false END) AS relpersistence, + substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor, + (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, rel.reloftype, + CASE WHEN typ.typname IS NOT NULL THEN (select quote_ident(nspname) FROM pg_namespace WHERE oid = {{scid}}::oid )||'.'||quote_ident(typ.typname) ELSE typ.typname END AS typname, + typ.typrelid AS typoid, + (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable, + -- Added for pgAdmin4 + (CASE WHEN array_length(rel.reloptions, 1) > 0 THEN true ELSE false END) AS autovacuum_custom, + (CASE WHEN array_length(tst.reloptions, 1) > 0 AND rel.reltoastrelid != 0 THEN true ELSE false END) AS toast_autovacuum, + + (SELECT array_agg(provider || '=' || label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS seclabels, + (CASE WHEN rel.oid <= {{ datlastsysoid}}::oid THEN true ElSE false END) AS is_sys_table + -- Added for partition table + {% if tid %}, (CASE WHEN rel.relkind = 'p' THEN pg_get_partkeydef({{ tid }}::oid) ELSE '' END) AS partition_scheme {% 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.reloftype=typ.oid +WHERE rel.relkind IN ('r','s','t','p') AND rel.relnamespace = {{ scid }}::oid +AND NOT rel.relispartition +{% if tid %} AND rel.oid = {{ tid }}::oid {% endif %} +ORDER BY rel.relname; diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql new file mode 100644 index 00000000..d12a8385 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/templates/tables/sql/12_plus/update.sql @@ -0,0 +1,211 @@ +{% import 'macros/schemas/security.macros' as SECLABEL %} +{% import 'macros/schemas/privilege.macros' as PRIVILEGE %} +{% import 'macros/variable.macros' as VARIABLE %} +{#####################################################} +{## Rename table ##} +{#####################################################} +{% if data.name and data.name != o_data.name %} +ALTER TABLE {{conn|qtIdent(o_data.schema, o_data.name)}} + RENAME TO {{conn|qtIdent(data.name)}}; + +{% endif %} +{#####################################################} +{## Change table schema ##} +{#####################################################} +{% if data.schema and data.schema != o_data.schema %} +ALTER TABLE {{conn|qtIdent(o_data.schema, data.name)}} + SET SCHEMA {{conn|qtIdent(data.schema)}}; + +{% endif %} +{#####################################################} +{## Change table owner ##} +{#####################################################} +{% if data.relowner and data.relowner != o_data.relowner %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + OWNER TO {{conn|qtIdent(data.relowner)}}; + +{% endif %} +{#####################################################} +{## Update Inherits table definition ##} +{#####################################################} +{% if data.coll_inherits_added|length > 0 %} +{% for val in data.coll_inherits_added %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + INHERIT {{val}}; + +{% endfor %} +{% endif %} +{% if data.coll_inherits_removed|length > 0 %} +{% for val in data.coll_inherits_removed %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + NO INHERIT {{val}}; + +{% endfor %} +{% endif %} +{#####################################################} +{## Change tablespace ##} +{#####################################################} +{% if data.spcname and data.spcname != o_data.spcname %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + SET TABLESPACE {{conn|qtIdent(data.spcname)}}; + +{% endif %} +{#####################################################} +{## change fillfactore settings ##} +{#####################################################} +{% if data.fillfactor and data.fillfactor != o_data.fillfactor %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} + SET (FILLFACTOR={{data.fillfactor}}); + +{% endif %} +{###############################} +{## Table AutoVacuum settings ##} +{###############################} +{% if data.vacuum_table is defined and data.vacuum_table.set_values|length > 0 %} +{% set has_vacuum_set = true %} +{% endif %} +{% if data.vacuum_table is defined and data.vacuum_table.reset_values|length > 0 %} +{% set has_vacuum_reset = true %} +{% endif %} +{% if o_data.autovacuum_custom and data.autovacuum_custom == false %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( + autovacuum_enabled, + autovacuum_analyze_scale_factor, + autovacuum_analyze_threshold, + autovacuum_freeze_max_age, + autovacuum_vacuum_cost_delay, + autovacuum_vacuum_cost_limit, + autovacuum_vacuum_scale_factor, + autovacuum_vacuum_threshold, + autovacuum_freeze_min_age, + autovacuum_freeze_table_age +); +{% else %} +{% if data.autovacuum_enabled is defined or has_vacuum_set %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET ( +{% if data.autovacuum_enabled is defined and data.autovacuum_enabled != o_data.autovacuum_enabled %} + autovacuum_enabled = {% if data.autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_set %}, +{% endif %} +{% endif %} +{% if has_vacuum_set %} +{% for opt in data.vacuum_table.set_values %}{% if opt.name and opt.value %} + {{opt.name}} = {{opt.value}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} +{% endif %} + +); +{% endif %} +{% if has_vacuum_reset %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( +{% for opt in data.vacuum_table.reset_values %}{% if opt.name %} + {{opt.name}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} + +); +{% endif %} +{% endif %} +{#####################################} +{## Toast table AutoVacuum settings ##} +{#####################################} +{% if data.vacuum_toast is defined and data.vacuum_toast.set_values|length > 0 %} +{% set has_vacuum_toast_set = true %} +{% endif %} +{% if data.vacuum_toast is defined and data.vacuum_toast.reset_values|length > 0 %} +{% set has_vacuum_toast_reset = true %} +{% endif %} +{% if o_data.toast_autovacuum and data.toast_autovacuum == false %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( + toast.autovacuum_enabled, + toast.autovacuum_freeze_max_age, + toast.autovacuum_vacuum_cost_delay, + toast.autovacuum_vacuum_cost_limit, + toast.autovacuum_vacuum_scale_factor, + toast.autovacuum_vacuum_threshold, + toast.autovacuum_freeze_min_age, + toast.autovacuum_freeze_table_age, + toast.autovacuum_analyze_threshold, + toast.autovacuum_analyze_scale_factor +); +{% else %} +{% if data.toast_autovacuum_enabled is defined or has_vacuum_toast_set %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} SET ( +{% if data.toast_autovacuum_enabled is defined and data.toast_autovacuum_enabled != o_data.toast_autovacuum_enabled %} + toast.autovacuum_enabled = {% if data.toast_autovacuum_enabled %}true{% else %}false{% endif %}{% if has_vacuum_toast_set %}, +{% endif %} +{% endif %} +{% if has_vacuum_toast_set %} +{% for opt in data.vacuum_toast.set_values %}{% if opt.name and opt.value %} + toast.{{opt.name}} = {{opt.value}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} +{% endif %} + +); +{% endif %} +{% if has_vacuum_toast_reset %} +ALTER TABLE {{conn|qtIdent(data.schema, data.name)}} RESET ( +{% for opt in data.vacuum_toast.reset_values %}{% if opt.name %} + toast.{{opt.name}}{% if not loop.last %}, +{% endif %} +{% endif %} +{% endfor %} + +); +{% endif %} +{% endif %} +{#####################################################} +{## Change table comments ##} +{#####################################################} +{% if data.description is defined and data.description != o_data.description %} +COMMENT ON TABLE {{conn|qtIdent(data.schema, data.name)}} + IS {{data.description|qtLiteral}}; + +{% endif %} +{#####################################################} +{## Update table Privileges ##} +{#####################################################} +{% if data.relacl %} +{% if 'deleted' in data.relacl %} +{% for priv in data.relacl.deleted %} +{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }} +{% endfor %} +{% endif %} +{% if 'changed' in data.relacl %} +{% for priv in data.relacl.changed %} +{{ PRIVILEGE.UNSETALL(conn, 'TABLE', priv.grantee, data.name, data.schema) }} +{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }} +{% endfor %} +{% endif %} +{% if 'added' in data.relacl %} +{% for priv in data.relacl.added %} +{{ PRIVILEGE.SET(conn, 'TABLE', priv.grantee, data.name, priv.without_grant, priv.with_grant, data.schema) }} +{% endfor %} +{% endif %} +{% endif %} +{#####################################################} +{## Update table SecurityLabel ##} +{#####################################################} +{% 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.UNSET(conn, 'TABLE', data.name, r.provider, data.schema) }} +{% endfor %} +{% endif %} +{% if 'added' in seclabels and seclabels.added|length > 0 %} +{% for r in seclabels.added %} +{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }} +{% endfor %} +{% endif %} +{% if 'changed' in seclabels and seclabels.changed|length > 0 %} +{% for r in seclabels.changed %} +{{ SECLABEL.SET(conn, 'TABLE', data.name, r.provider, r.label, data.schema) }} +{% endfor %} +{% endif %} + +{% endif %} diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py index f599892b..efb1986e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_acl_sql.py @@ -35,7 +35,10 @@ class TestColumnAclSql(SQLTemplateTestBase): self.table_id, self.column_id = cursor.fetchone() def generate_sql(self, version): - template_file = self.get_template_file(version, "acl.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "columns", "sql") + template_file = self.get_template_file(version, file_path, + "acl.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -47,10 +50,3 @@ class TestColumnAclSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "columns", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py index c098605e..8da8f12e 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_column_properties_sql.py @@ -30,7 +30,10 @@ class TestColumnPropertiesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "properties.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "columns", "sql") + template_file = self.get_template_file(version, file_path, + "properties.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -47,10 +50,3 @@ class TestColumnPropertiesSql(SQLTemplateTestBase): self.assertEqual('some_column', first_row['name']) self.assertEqual('character varying', first_row['cltype']) self.assertEqual(3, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "columns", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py index 4b995e93..f3523591 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_acl_sql.py @@ -30,7 +30,10 @@ class TestTablesAclSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "acl.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "acl.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -53,10 +56,3 @@ class TestTablesAclSql(SQLTemplateTestBase): self.assertEqual(['r'], new_acl_map['privileges']) self.assertEqual([False], new_acl_map['grantable']) return public_acls - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py index e14df0d6..9ef0e76f 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_node_sql.py @@ -28,7 +28,10 @@ class TestTablesNodeSql(SQLTemplateTestBase): pass def generate_sql(self, version): - template_file = self.get_template_file(version, "nodes.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "nodes.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id) @@ -50,10 +53,3 @@ class TestTablesNodeSql(SQLTemplateTestBase): # triggercount is sometimes returned as a string for some reason self.assertEqual(0, long(triggercount)) self.assertIsNotNone(long(has_enable_triggers)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py index f1323a3a..fc349bd3 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_tables_properties_sql.py @@ -44,7 +44,10 @@ class TestTablesPropertiesSql(SQLTemplateTestBase): self.assertEqual([], first_row['coll_inherits']) def generate_sql(self, version): - template_file = self.get_template_file(version, "properties.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "tables", "sql") + template_file = self.get_template_file(version, file_path, + "properties.sql") template = file_as_template(template_file) public_schema_id = 2200 sql = template.render(scid=public_schema_id, @@ -66,10 +69,3 @@ class TestTablesPropertiesSql(SQLTemplateTestBase): cursor.execute("SELECT oid FROM pg_class where relname='test_table'") self.table_id = cursor.fetchone()[0] - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "tables", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py index beefe1b0..1f29fad8 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_get_oid_sql.py @@ -35,7 +35,10 @@ class TestTriggerGetOidSql(SQLTemplateTestBase): self.table_id, self.column_id = cursor.fetchone() def generate_sql(self, version): - template_file = self.get_template_file(version, "get_oid.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "triggers", "sql") + template_file = self.get_template_file(version, file_path, + "get_oid.sql") jinja2.filters.FILTERS['qtLiteral'] = lambda value: "NULL" template = file_as_template(template_file) @@ -47,10 +50,3 @@ class TestTriggerGetOidSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "triggers", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py index 6ce35481..92e80dc9 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/tables/tests/test_trigger_nodes_sql.py @@ -30,7 +30,10 @@ class TestTriggerNodesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "nodes.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "triggers", "sql") + template_file = self.get_template_file(version, file_path, + "nodes.sql") template = file_as_template(template_file) sql = template.render(tid=self.table_id) @@ -38,10 +41,3 @@ class TestTriggerNodesSql(SQLTemplateTestBase): def assertions(self, fetch_result, descriptions): self.assertEqual(0, len(fetch_result)) - - @staticmethod - def get_template_file(version, filename): - return os.path.join( - os.path.dirname(__file__), "..", "templates", "triggers", "sql", - version, filename - ) diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py index 8b99a986..329acd3a 100644 --- a/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py +++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/types/tests/test_types_add.py @@ -51,7 +51,7 @@ class TypesAddTestCase(BaseTestGenerator): "typtype": "c", "typeowner": db_user, "schema": self.schema_name, - "composite": [{"member_name": "one", "type": "abstime", + "composite": [{"member_name": "one", "type": "bigint", "is_tlength": False, "is_precision": False}, {"member_name": "two", "type": "\"char\"[]", "is_tlength": False, "is_precision": False}], diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql new file mode 100644 index 00000000..0b6f71f8 --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependencies.sql @@ -0,0 +1,45 @@ +SELECT DISTINCT dep.deptype, dep.refclassid, cl.relkind, ad.adbin, pg_get_expr(ad.adbin, ad.adrelid) as adsrc, + CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.refobjsubid::character varying, '') + WHEN tg.oid IS NOT NULL THEN 'T'::text + WHEN ty.oid IS NOT NULL AND ty.typbasetype = 0 THEN 'y'::text + WHEN ty.oid IS NOT NULL AND ty.typbasetype != 0 THEN 'd'::text + WHEN ns.oid IS NOT NULL THEN 'n'::text + WHEN pr.oid IS NOT NULL THEN 'p'::text + WHEN la.oid IS NOT NULL THEN 'l'::text + WHEN rw.oid IS NOT NULL THEN 'R'::text + WHEN co.oid IS NOT NULL THEN 'C'::text || contype + WHEN ad.oid IS NOT NULL THEN 'A'::text + WHEN fs.oid IS NOT NULL THEN 'F'::text + WHEN fdw.oid IS NOT NULL THEN 'f'::text + ELSE '' + END AS type, + COALESCE(coc.relname, clrw.relname) AS ownertable, + CASE WHEN cl.relname IS NOT NULL OR att.attname IS NOT NULL THEN cl.relname || '.' || att.attname + ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) + END AS refname, + COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +FROM pg_depend dep +LEFT JOIN pg_class cl ON dep.refobjid=cl.oid +LEFT JOIN pg_attribute att ON dep.refobjid=att.attrelid AND dep.refobjsubid=att.attnum +LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +LEFT JOIN pg_proc pr ON dep.refobjid=pr.oid +LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +LEFT JOIN pg_trigger tg ON dep.refobjid=tg.oid +LEFT JOIN pg_type ty ON dep.refobjid=ty.oid +LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +LEFT JOIN pg_constraint co ON dep.refobjid=co.oid +LEFT JOIN pg_class coc ON co.conrelid=coc.oid +LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +LEFT JOIN pg_rewrite rw ON dep.refobjid=rw.oid +LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +LEFT JOIN pg_language la ON dep.refobjid=la.oid +LEFT JOIN pg_namespace ns ON dep.refobjid=ns.oid +LEFT JOIN pg_attrdef ad ON ad.adrelid=att.attrelid AND ad.adnum=att.attnum +LEFT JOIN pg_foreign_server fs ON fs.oid=dep.refobjid +LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.refobjid +{{where_clause}} AND +refclassid IN ( SELECT oid FROM pg_class WHERE relname IN + ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace', + 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper')) +ORDER BY refclassid, cl.relkind diff --git a/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql new file mode 100644 index 00000000..c5c87b7f --- /dev/null +++ b/web/pgadmin/browser/server_groups/servers/templates/depends/sql/12_plus/dependents.sql @@ -0,0 +1,44 @@ +SELECT DISTINCT dep.deptype, dep.classid, cl.relkind, ad.adbin, pg_get_expr(ad.adbin, ad.adrelid) as adsrc, + CASE WHEN cl.relkind IS NOT NULL THEN cl.relkind || COALESCE(dep.objsubid::text, '') + WHEN tg.oid IS NOT NULL THEN 'T'::text + WHEN ty.oid IS NOT NULL THEN 'y'::text + WHEN ns.oid IS NOT NULL THEN 'n'::text + WHEN pr.oid IS NOT NULL THEN 'p'::text + WHEN la.oid IS NOT NULL THEN 'l'::text + WHEN rw.oid IS NOT NULL THEN 'R'::text + WHEN co.oid IS NOT NULL THEN 'C'::text || contype + WHEN ad.oid IS NOT NULL THEN 'A'::text + WHEN fs.oid IS NOT NULL THEN 'F'::text + WHEN fdw.oid IS NOT NULL THEN 'f'::text + ELSE '' + END AS type, + COALESCE(coc.relname, clrw.relname) AS ownertable, + CASE WHEN cl.relname IS NOT NULL AND att.attname IS NOT NULL THEN cl.relname || '.' || att.attname + ELSE COALESCE(cl.relname, co.conname, pr.proname, tg.tgname, ty.typname, la.lanname, rw.rulename, ns.nspname, fs.srvname, fdw.fdwname) + END AS refname, + COALESCE(nsc.nspname, nso.nspname, nsp.nspname, nst.nspname, nsrw.nspname) AS nspname +FROM pg_depend dep +LEFT JOIN pg_class cl ON dep.objid=cl.oid +LEFT JOIN pg_attribute att ON dep.objid=att.attrelid AND dep.objsubid=att.attnum +LEFT JOIN pg_namespace nsc ON cl.relnamespace=nsc.oid +LEFT JOIN pg_proc pr ON dep.objid=pr.oid +LEFT JOIN pg_namespace nsp ON pr.pronamespace=nsp.oid +LEFT JOIN pg_trigger tg ON dep.objid=tg.oid +LEFT JOIN pg_type ty ON dep.objid=ty.oid +LEFT JOIN pg_namespace nst ON ty.typnamespace=nst.oid +LEFT JOIN pg_constraint co ON dep.objid=co.oid +LEFT JOIN pg_class coc ON co.conrelid=coc.oid +LEFT JOIN pg_namespace nso ON co.connamespace=nso.oid +LEFT JOIN pg_rewrite rw ON dep.objid=rw.oid +LEFT JOIN pg_class clrw ON clrw.oid=rw.ev_class +LEFT JOIN pg_namespace nsrw ON clrw.relnamespace=nsrw.oid +LEFT JOIN pg_language la ON dep.objid=la.oid +LEFT JOIN pg_namespace ns ON dep.objid=ns.oid +LEFT JOIN pg_attrdef ad ON ad.oid=dep.objid +LEFT JOIN pg_foreign_server fs ON fs.oid=dep.objid +LEFT JOIN pg_foreign_data_wrapper fdw ON fdw.oid=dep.objid +{{where_clause}} AND +classid IN ( SELECT oid FROM pg_class WHERE relname IN + ('pg_class', 'pg_constraint', 'pg_conversion', 'pg_language', 'pg_proc', 'pg_rewrite', 'pg_namespace', + 'pg_trigger', 'pg_type', 'pg_attrdef', 'pg_event_trigger', 'pg_foreign_server', 'pg_foreign_data_wrapper')) +ORDER BY classid, cl.relkind diff --git a/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py b/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py index 4759101f..cf49437a 100644 --- a/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py +++ b/web/pgadmin/browser/server_groups/servers/tests/test_dependencies_sql.py @@ -31,7 +31,10 @@ class TestDependenciesSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "dependencies.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "depends", "sql") + template_file = self.get_template_file(version, file_path, + "dependencies.sql") template = file_as_template(template_file) sql = template.render( where_clause="WHERE dep.objid=%s::oid" % self.table_id) @@ -47,8 +50,3 @@ class TestDependenciesSql(SQLTemplateTestBase): self.assertEqual('n', first_row["deptype"]) self.assertEqual('public', first_row["refname"]) - - @staticmethod - def get_template_file(version, filename): - return os.path.join(os.path.dirname(__file__), "..", "templates", - "depends", "sql", version, filename) diff --git a/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py b/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py index d4b58a46..fce2e910 100644 --- a/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py +++ b/web/pgadmin/browser/server_groups/servers/tests/test_dependents_sql.py @@ -31,7 +31,10 @@ class TestDependentsSql(SQLTemplateTestBase): self.table_id = cursor.fetchone()[0] def generate_sql(self, version): - template_file = self.get_template_file(version, "dependents.sql") + file_path = os.path.join(os.path.dirname(__file__), "..", "templates", + "depends", "sql") + template_file = self.get_template_file(version, file_path, + "dependents.sql") template = file_as_template(template_file) sql = template.render( where_clause="WHERE dep.objid=%s::oid" % self.table_id) @@ -47,8 +50,3 @@ class TestDependentsSql(SQLTemplateTestBase): self.assertEqual('n', first_row["deptype"]) self.assertEqual('test_table', first_row["refname"]) - - @staticmethod - def get_template_file(version, filename): - return os.path.join(os.path.dirname(__file__), "..", "templates", - "depends", "sql", version, filename) diff --git a/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql b/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql index 580c3622..2eef803b 100644 --- a/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql +++ b/web/pgadmin/misc/templates/sqlautocomplete/sql/default/columns.sql @@ -5,7 +5,7 @@ SELECT nsp.nspname schema_name, att.attname column_name, att.atttypid::regtype::text type_name, att.atthasdef AS has_default, - def.adsrc as default + pg_get_expr(def.adbin, def.adrelid) as default FROM pg_catalog.pg_attribute att INNER JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid @@ -26,7 +26,7 @@ SELECT nsp.nspname schema_name, att.attname column_name, att.atttypid::regtype::text type_name, att.atthasdef AS has_default, - def.adsrc as default + pg_get_expr(def.adbin, def.adrelid) as default FROM pg_catalog.pg_attribute att INNER JOIN pg_catalog.pg_class cls ON att.attrelid = cls.oid diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py index b3b56cb0..d4b0700f 100644 --- a/web/pgadmin/tools/sqleditor/command.py +++ b/web/pgadmin/tools/sqleditor/command.py @@ -615,8 +615,14 @@ class TableCommand(GridCommand): This function checks whether the table has oids or not. """ driver = get_driver(PG_DEFAULT_DRIVER) + manager = driver.connection_manager(self.sid) + + # Remove the special behavior of OID columns from + # PostgreSQL 12 onwards, so returning False. + if manager.sversion >= 120000: + return False + if default_conn is None: - manager = driver.connection_manager(self.sid) conn = manager.connection(did=self.did, conn_id=self.conn_id) else: conn = default_conn diff --git a/web/pgadmin/utils/versioned_template_loader.py b/web/pgadmin/utils/versioned_template_loader.py index b6d00458..3b47757d 100644 --- a/web/pgadmin/utils/versioned_template_loader.py +++ b/web/pgadmin/utils/versioned_template_loader.py @@ -68,6 +68,15 @@ def get_version_mapping(template): if len(template_path_parts) == 4: _, server_type, _, _ = template_path_parts + return get_version_mapping_directories(server_type) + + +def get_version_mapping_directories(server_type): + """ + This function will return all the version mapping directories + :param server_type: + :return: + """ if server_type == 'gpdb': return ( {'name': "gpdb_5.0_plus", 'number': 80323}, @@ -75,7 +84,8 @@ def get_version_mapping(template): {'name': "default", 'number': 0} ) - return ({'name': "11_plus", 'number': 110000}, + return ({'name': "12_plus", 'number': 120000}, + {'name': "11_plus", 'number': 110000}, {'name': "10_plus", 'number': 100000}, {'name': "9.6_plus", 'number': 90600}, {'name': "9.5_plus", 'number': 90500}, diff --git a/web/regression/python_test_utils/sql_template_test_base.py b/web/regression/python_test_utils/sql_template_test_base.py index 3dbd1f18..fe0b2a2e 100644 --- a/web/regression/python_test_utils/sql_template_test_base.py +++ b/web/regression/python_test_utils/sql_template_test_base.py @@ -7,9 +7,13 @@ # ########################################################################## +import os + from pgadmin.utils.route import BaseTestGenerator from regression.python_test_utils import test_utils from pgadmin.utils.driver import DriverRegistry +from pgadmin.utils.versioned_template_loader \ + import get_version_mapping_directories DriverRegistry.load_drivers() @@ -22,7 +26,6 @@ class SQLTemplateTestBase(BaseTestGenerator): def __init__(self): super(SQLTemplateTestBase, self).__init__() self.database_name = -1 - self.versions_to_test = -1 def test_setup(self, connection, cursor): pass @@ -41,19 +44,37 @@ class SQLTemplateTestBase(BaseTestGenerator): test_utils.create_table(self.server, database_name, "test_table") self.database_name = database_name - if connection.server_version < 90100: - self.versions_to_test = ['default'] - else: - self.versions_to_test = ['9.1_plus'] - cursor = connection.cursor() self.test_setup(connection, cursor) - for version in self.versions_to_test: - sql = self.generate_sql(version) + sql = self.generate_sql(connection.server_version) + + cursor = connection.cursor() + cursor.execute(sql) + fetch_result = cursor.fetchall() + + self.assertions(fetch_result, cursor.description) + + def get_template_file(self, version, file_path, filename): + """ + This function check the specified file in the server mapping directory + and if file exists then return that path. + :param version: + :param file_path: + :param filename: + :return: + """ + # Iterate all the mapping directories and check the file is exist + # in the specified folder. If it exists then return the path. + for directory in get_version_mapping_directories(self.server['type']): + if directory['number'] > version: + continue - cursor = connection.cursor() - cursor.execute(sql) - fetch_result = cursor.fetchall() + template_path = '/'.join([ + file_path, + directory['name'], + filename + ]) - self.assertions(fetch_result, cursor.description) + if os.path.exists(template_path): + return template_path ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 2019-06-06 10:01 [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Akshay Joshi <[email protected]> 2019-06-07 10:39 ` Re: [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Akshay Joshi <[email protected]> @ 2019-06-10 13:25 ` Dave Page <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Dave Page @ 2019-06-10 13:25 UTC (permalink / raw) To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers Thanks, applied. On Fri, Jun 7, 2019 at 11:39 AM Akshay Joshi <[email protected]> wrote: > Hi Hackers, > > Today I have read the https://www.postgresql.org/docs/release/12.0/ release > note. I have created some new feature request and fixed one issue. > Following are the issues that I have fixed: > > - Fixed existing issue to drop/delete partitions of the partition > table. > - Allow foreign keys to reference partitioned tables. > > Created following issues, not sure we need to consider it: > > 1. #4333 <https://redmine.postgresql.org/issues/4333; Add support for > function selectivity. > 2. #4334 <https://redmine.postgresql.org/issues/4334; Add support for > generated columns. > 3. #4335 <https://redmine.postgresql.org/issues/4335; Add EXPLAIN > option SETTINGS and SUMMARY > > > Attached is the complete patch, ignore the previous patch. Please review > it. > > > On Thu, Jun 6, 2019 at 3:31 PM Akshay Joshi <[email protected]> > wrote: > >> Hi Hackers, >> >> I have started adding support of PostgreSQL v12 in pgAdmin4. Attached is >> the patch to fix the following issues: >> >> - RM #4290: "string indices must be integers" displayed for all >> objects >> - RM #4288: View data issue. >> - PG v12 removes the ability to create table using WITH OIDS, so I >> have modified the code in GUI as well as on backend. >> - PG v12 removes obsolete pg_attrdef.adsrc and pg_constraint.consrc >> column, so fixed with appropriate functions calls. >> - For partitioned tables, since no storage is required for the table >> itself. PG v12 throws and error while specifying "TABLESPACE" in create >> table syntax for partitioned table, so fix that too. >> >> This is the initial patch to fix the issues that has been raised or I >> have found. Will continue to work on adding support for v12. Will check the >> release notes. >> >> -- >> *Thanks & Regards* >> *Akshay Joshi* >> >> *Sr. Software Architect* >> *EnterpriseDB Software India Private Limited* >> *Mobile: +91 976-788-8246* >> > > > -- > *Thanks & Regards* > *Akshay Joshi* > > *Sr. Software Architect* > *EnterpriseDB Software India Private Limited* > *Mobile: +91 976-788-8246* > -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-06-10 13:25 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-06-06 10:01 [pgAdmin4][Patch]: Feature #4283 pgAdmin4 support for v12 Akshay Joshi <[email protected]> 2019-06-07 10:39 ` Akshay Joshi <[email protected]> 2019-06-10 13:25 ` Dave Page <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox