public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahul Shirsat <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
Date: Sat, 23 Nov 2019 00:37:15 +0530
Message-ID: <CAKtn9dNz1ozbJWzJw_qVLx3QAxcNSjCCJWqB8HW3Qn5Hooz-pA@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDeCm9b2u=G5+eJU6NJQDpa0AJGFzmRw6iTfVf6ZAHBXMQ@mail.gmail.com>
References: <CAKtn9dOLhxuNmtPbBvbvDq29YFQ48DTXhv5QGjav7KG8Eht2SA@mail.gmail.com>
<CANxoLDc9-1X-cZo0FSzNhBG9m2s-HO0rKzsR5RS3kYPmBBgwow@mail.gmail.com>
<CANxoLDeCm9b2u=G5+eJU6NJQDpa0AJGFzmRw6iTfVf6ZAHBXMQ@mail.gmail.com>
Akshay,
Please find the correction done in this updated patch based on your review
comments.
Regards,
Rahul Shirsat.
On Thu, Nov 21, 2019 at 1:08 PM Akshay Joshi <[email protected]>
wrote:
> Hi Rahul
>
> Some more review comments:
>
> - Don't create new file test_view.json as I tested and committed the
> old code.
> - Remove hardcoded "oid: 17869" from JSON file.
> - MSQL endpoints and file name missing in some test cases.
> - Cover all the controls in create and alter as much as possible.
>
>
> On Tue, Nov 19, 2019 at 6:27 PM Akshay Joshi <
> [email protected]> wrote:
>
>> Hi Rahul
>>
>> Test cases failing on all EPAS servers. Please fix and send the patch
>> again.
>>
>> On Tue, Nov 19, 2019 at 12:47 PM Rahul Shirsat <
>> [email protected]> wrote:
>>
>>> Hi Hackers,
>>>
>>> I have attached the patch for RE-SQL/MSQL test cases for View as well as
>>> Materialized View.
>>>
>>> --
>>> *Rahul Shirsat*
>>> Software Engineer | EnterpriseDB Corporation.
>>>
>>
>>
>> --
>> *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*
>
--
*Rahul Shirsat*
Software Engineer | EnterpriseDB Corporation.
Attachments:
[application/octet-stream] RM4696_v2.patch (112.6K, 3-RM4696_v2.patch)
download | inline diff:
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.3_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.3_plus/sql/create.sql
index 785a406ed..877ba2596 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.3_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.3_plus/sql/create.sql
@@ -10,14 +10,13 @@ CREATE MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }}
{% if(data.fillfactor or data['vacuum_data']|length > 0) %}
WITH (
{% if data.fillfactor %}
- FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\r' }}{% endif %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\n' }}{% endif %}
{% endif %}
{% for field in data['vacuum_data'] %}
{% if field.value is defined and field.value != '' and field.value != none %}
{% if loop.index > 1 %},
-{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}
-{% endfor %}{{ '\r' }}
-)
+{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}{% endfor %}
+{{ '\n' }})
{% endif %}
{% if data.spcname %}TABLESPACE {{ data.spcname }}
{% endif %}AS
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/create.sql
index 785a406ed..877ba2596 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/create.sql
@@ -10,14 +10,13 @@ CREATE MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }}
{% if(data.fillfactor or data['vacuum_data']|length > 0) %}
WITH (
{% if data.fillfactor %}
- FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\r' }}{% endif %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\n' }}{% endif %}
{% endif %}
{% for field in data['vacuum_data'] %}
{% if field.value is defined and field.value != '' and field.value != none %}
{% if loop.index > 1 %},
-{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}
-{% endfor %}{{ '\r' }}
-)
+{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}{% endfor %}
+{{ '\n' }})
{% endif %}
{% if data.spcname %}TABLESPACE {{ data.spcname }}
{% endif %}AS
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/update.sql
index e7ab7ab07..f243c4ab8 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/9.4_plus/sql/update.sql
@@ -29,12 +29,9 @@ DROP MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }};
CREATE MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
{% if data.fillfactor or (data['vacuum_data']['changed']|length > 0 ) %}
WITH(
-{% if data.fillfactor %}
- FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\r' }}
-{% endif %}
+{% if data.fillfactor %} FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\n' }} {% endif %}
{% if data['vacuum_data']['changed']|length > 0 %}
-{% for field in data['vacuum_data']['changed'] %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% for field in data['vacuum_data']['changed'] %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{{ '\n' }}{% endif %}
{% endfor %}
{% endif %}
)
@@ -96,8 +93,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
{% if('vacuum_toast' in data and data['vacuum_toast']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_toast']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{{ '\n' }}{% endif %}
{% endif %}
{% endfor %}
);
@@ -124,9 +120,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
{% if('vacuum_table' in data and data['vacuum_table']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_table']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
-{% endif %}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{% endif %}
{% endfor %}
);
@@ -138,8 +132,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% if data['vacuum_data']['reset']|length == 0 and
data['vacuum_data']['changed']|length == 0 and data['settings']|length > 0 %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} SET(
-{% for field in data['settings'] %}
- {{ field }} = {{ data['settings'][field]|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% for field in data['settings'] %} {{ field }} = {{ data['settings'][field]|lower }}{% if not loop.last %},{{ '\n' }}{% endif %}
{% endfor %}
);
@@ -147,17 +140,13 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} SET(
{% if(data['vacuum_data']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_data']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
-{% endif %}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\n' }}{% endif %}
{% endfor %}
);
{% endif %}
{% if data['vacuum_data']['reset']|length > 0 %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
-{% for field in data['vacuum_data']['reset'] %}
- {{ field.name }}{% if not loop.last %},{% endif %}{{ '\r' }}
-{% endfor %}
+{% for field in data['vacuum_data']['reset'] %} {{ field.name }}{% if not loop.last %},{% endif %}{{ '\n' }}{% endfor %}
);
{% endif %}
{% endif %}{# ===== End check for custom autovacuum ===== #}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/acl.sql
index 6c18f409e..841d74982 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/acl.sql
@@ -38,4 +38,5 @@ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
GROUP BY
g.rolname,
gt.rolname
+ORDER BY grantee
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/create.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/create.sql
index 785a406ed..877ba2596 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/create.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/create.sql
@@ -10,14 +10,13 @@ CREATE MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }}
{% if(data.fillfactor or data['vacuum_data']|length > 0) %}
WITH (
{% if data.fillfactor %}
- FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\r' }}{% endif %}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['autovacuum_enabled'] or data['toast_autovacuum_enabled'] or data['vacuum_data']|length > 0 %},{{ '\n' }}{% endif %}
{% endif %}
{% for field in data['vacuum_data'] %}
{% if field.value is defined and field.value != '' and field.value != none %}
{% if loop.index > 1 %},
-{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}
-{% endfor %}{{ '\r' }}
-)
+{% endif %} {{ field.name }} = {{ field.value|lower }}{% endif %}{% endfor %}
+{{ '\n' }})
{% endif %}
{% if data.spcname %}TABLESPACE {{ data.spcname }}
{% endif %}AS
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/update.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/update.sql
index e7ab7ab07..2b4cc9327 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/update.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/9.3_plus/sql/update.sql
@@ -30,11 +30,10 @@ CREATE MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }}
{% if data.fillfactor or (data['vacuum_data']['changed']|length > 0 ) %}
WITH(
{% if data.fillfactor %}
- FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\r' }}
+ FILLFACTOR = {{ data.fillfactor }}{% if data['vacuum_data']['changed']|length > 0 %},{% endif %}{{ '\n' }}
{% endif %}
{% if data['vacuum_data']['changed']|length > 0 %}
-{% for field in data['vacuum_data']['changed'] %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% for field in data['vacuum_data']['changed'] %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\n' }}
{% endfor %}
{% endif %}
)
@@ -96,8 +95,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
{% if('vacuum_toast' in data and data['vacuum_toast']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_toast']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{{ '\n' }}{% endif %}
{% endif %}
{% endfor %}
);
@@ -124,8 +122,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
{% if('vacuum_table' in data and data['vacuum_table']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_table']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}
{% endif %}
{% endfor %}
);
@@ -138,8 +135,7 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% if data['vacuum_data']['reset']|length == 0 and
data['vacuum_data']['changed']|length == 0 and data['settings']|length > 0 %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} SET(
-{% for field in data['settings'] %}
- {{ field }} = {{ data['settings'][field]|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
+{% for field in data['settings'] %} {{ field }} = {{ data['settings'][field]|lower }}{% if not loop.last %},{{ '\n' }}{% endif %}
{% endfor %}
);
@@ -147,17 +143,13 @@ ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} SET(
{% if(data['vacuum_data']['changed']|length > 0) %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(data.schema, data.name) }} SET(
{% for field in data['vacuum_data']['changed'] %}
-{% if field.value != None %}
- {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\r' }}
-{% endif %}
+{% if field.value != None %} {{ field.name }} = {{ field.value|lower }}{% if not loop.last %},{% endif %}{{ '\n' }}{% endif %}
{% endfor %}
);
{% endif %}
{% if data['vacuum_data']['reset']|length > 0 %}
ALTER MATERIALIZED VIEW {{ conn|qtIdent(view_schema, view_name) }} RESET(
-{% for field in data['vacuum_data']['reset'] %}
- {{ field.name }}{% if not loop.last %},{% endif %}{{ '\r' }}
-{% endfor %}
+{% for field in data['vacuum_data']['reset'] %} {{ field.name }}{% if not loop.last %},{% endif %}{{ '\n' }}{% endfor %}
);
{% endif %}
{% endif %}{# ===== End check for custom autovacuum ===== #}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/ppas/9.4_plus/sql/acl.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/ppas/9.4_plus/sql/acl.sql
index 59665e5d5..089ad804a 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/ppas/9.4_plus/sql/acl.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/ppas/9.4_plus/sql/acl.sql
@@ -36,4 +36,5 @@ LEFT JOIN pg_catalog.pg_roles gt ON (d.grantee = gt.oid)
GROUP BY
g.rolname,
gt.rolname
+ORDER BY grantee
{% endif %}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview.sql
index 45a44ad08..952a10046 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview.sql
@@ -1,13 +1,18 @@
--- View: public."testmview_$%{}[]()&*^!@""'`\/#"
+-- View: public."testmview_$%{}[]()&*^!/@`#"
--- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#";
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
-CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
TABLESPACE pg_default
AS
- SELECT test_mview_table.col1
- FROM test_mview_table
-WITH DATA;
+ SELECT 1
+WITH NO DATA;
-ALTER TABLE public."testmview_$%{}[]()&*^!@""'`\/#"
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
OWNER TO postgres;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO postgres;
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor.sql
new file mode 100644
index 000000000..03197d009
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor_msql.sql
new file mode 100644
index 000000000..0c51d157a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_fillfactor_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+SET(
+ FILLFACTOR = 18
+);
+
+REFRESH MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" WITH NO DATA;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter.sql
new file mode 100644
index 000000000..9a60bd524
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter_msql.sql
new file mode 100644
index 000000000..737ffaa32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_parameter_msql.sql
@@ -0,0 +1,4 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition.sql
new file mode 100644
index 000000000..481879f70
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition.sql
@@ -0,0 +1,12 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition_msql.sql
new file mode 100644
index 000000000..2c1b637f4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_definition_msql.sql
@@ -0,0 +1,4 @@
+DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ AS
+SELECT 12
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv.sql
new file mode 100644
index 000000000..8e3bd1bc7
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv_msql.sql
new file mode 100644
index 000000000..3bfd1b9fb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_drop_all_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_msql.sql
new file mode 100644
index 000000000..2740b1969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_msql.sql
@@ -0,0 +1 @@
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter.sql
new file mode 100644
index 000000000..03197d009
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter_msql.sql
new file mode 100644
index 000000000..8aed82923
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_remove_table_parameter_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_enabled = false
+);
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" RESET(
+ autovacuum_analyze_scale_factor
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view.sql
index 47542ab90..2588f4194 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view.sql
@@ -16,5 +16,3 @@ COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
IS 'Testcomment-updated';
GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
-
-
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv.sql
new file mode 100644
index 000000000..b1e028996
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv.sql
@@ -0,0 +1,19 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
+GRANT SELECT, UPDATE, DELETE ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv_msql.sql
new file mode 100644
index 000000000..83f974b32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_some_priv_msql.sql
@@ -0,0 +1 @@
+GRANT SELECT, UPDATE, DELETE ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition.sql
new file mode 100644
index 000000000..2588f4194
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition_msql.sql
new file mode 100644
index 000000000..84f01f995
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_definition_msql.sql
@@ -0,0 +1,4 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ WITH (check_option=cascaded, security_barrier=true)
+ AS
+ SELECT * FROM test_view_table;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv.sql
new file mode 100644
index 000000000..2588f4194
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv_msql.sql
new file mode 100644
index 000000000..69c9ea663
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_delete_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_msql.sql
new file mode 100644
index 000000000..da5669912
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_msql.sql
@@ -0,0 +1,7 @@
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (security_barrier=true);
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (check_option=cascaded);
+
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview.sql
index 6e9e86ddc..069be3c0f 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview.sql
@@ -1,17 +1,15 @@
--- View: public."testmview_$%{}[]()&*^!@""'`\/#"
+-- View: public."testmview_$%{}[]()&*^!/@`#"
--- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#";
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
-CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
TABLESPACE pg_default
AS
- SELECT test_mview_table.col1
- FROM test_mview_table
+ SELECT 1
WITH NO DATA;
-ALTER TABLE public."testmview_$%{}[]()&*^!@""'`\/#"
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
OWNER TO postgres;
-COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
- IS 'Testcomment';
-
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview_msql.sql
new file mode 100644
index 000000000..511738896
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_mview_msql.sql
@@ -0,0 +1,11 @@
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view_msql.sql
new file mode 100644
index 000000000..a5c129a00
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view_msql.sql
@@ -0,0 +1,12 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=local
+) AS
+select col1 from test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment';
+
+GRANT INSERT ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_mview.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_mview.json
index 72eb226f3..335b1fed9 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_mview.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_mview.json
@@ -1,87 +1,256 @@
{
- "scenarios": [{
- "type": "create",
- "name": "Create Table for MViews",
- "endpoint": "NODE-table.obj",
- "sql_endpoint": "NODE-table.sql_id",
- "data": {
- "name": "test_mview_table",
- "check_constraint": [],
- "coll_inherits": "[]",
- "columns": [{
- "name": "col1",
- "cltype": "integer"
- }],
- "schema": "public"
- }
- },
- {
- "type": "create",
- "name": "Create MView",
- "endpoint": "NODE-mview.obj",
- "sql_endpoint": "NODE-mview.sql_id",
- "data": {
- "definition": "select col1 from test_mview_table;",
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public",
- "comment": "Testcomment",
- "autovacuum_enabled": false,
- "datacl": [],
- "seclabels": [],
- "toast_autovacuum_enabled": false,
- "vacuum_table": [{
- "name": "autovacuum_analyze_scale_factor"
- }],
- "vacuum_toast": [{
- "name": "autovacuum_freeze_max_age"
- }]
- },
- "expected_sql_file": "create_mview.sql"
- },
- {
- "type": "alter",
- "name": "Alter MView",
- "endpoint": "NODE-mview.obj_id",
- "sql_endpoint": "NODE-mview.sql_id",
- "data": {
- "definition": "select col1 from test_mview_table;",
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public",
- "comment": "Testcomment",
- "autovacuum_enabled": false,
- "datacl": [],
- "seclabels": [],
- "toast_autovacuum_enabled": false,
- "vacuum_table": [{
- "name": "autovacuum_analyze_scale_factor"
- }],
- "vacuum_toast": [{
- "name": "autovacuum_freeze_max_age"
- }]
- },
- "expected_sql_file": "alter_mview.sql"
- },
- {
- "type": "delete",
- "name": "Drop MView",
- "endpoint": "NODE-mview.obj_id",
- "data": {
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public"
- }
- },
- {
- "type": "delete",
- "name": "Drop Table for mview",
- "endpoint": "NODE-table.obj_id",
- "data": {
- "name": "test_mview_table",
- "owner": "postgres",
- "schema": "public"
- }
- }
- ]
-}
\ No newline at end of file
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Materialised Views",
+ "endpoint": "NODE-mview.obj",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql",
+ "data": {
+ "spcname": "pg_default",
+ "toast_autovacuum_enabled": false,
+ "autovacuum_enabled": false,
+ "schema": "public",
+ "owner": "postgres",
+ "vacuum_table": [
+ {
+ "name": "autovacuum_analyze_scale_factor"
+ },
+ {
+ "name": "autovacuum_analyze_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "vacuum_toast": [
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "datacl": [],
+ "seclabels": [],
+ "name": "testmview_$%{}[]()&*^!/@`#",
+ "comment": "comment1",
+ "definition": "SELECT 1"
+ },
+ "expected_sql_file": "create_mview.sql",
+ "expected_msql_file": "create_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Adding privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "postgres",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview.sql",
+ "expected_msql_file": "alter_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Remove all privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "deleted": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "postgres",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_drop_all_priv.sql",
+ "expected_msql_file": "alter_mview_drop_all_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (change definition)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "definition": "SELECT 12;"
+ },
+ "expected_sql_file": "alter_mview_definition.sql",
+ "expected_msql_file": "alter_mview_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Fillfactor)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "fillfactor": "18",
+ "with_data": false
+ },
+ "expected_sql_file": "alter_mview_add_fillfactor.sql",
+ "expected_msql_file": "alter_mview_add_fillfactor_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (add table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": true,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": 0.2
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_add_table_parameter.sql",
+ "expected_msql_file": "alter_mview_add_table_parameter_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (remove table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": false,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": null
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_remove_table_parameter.sql",
+ "expected_msql_file": "alter_mview_remove_table_parameter_msql.sql"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/tests.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/tests.json
index 864ae34d1..95f02e749 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/tests.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/tests.json
@@ -23,6 +23,7 @@
"name": "Create View",
"endpoint": "NODE-view.obj",
"sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql",
"data": {
"definition": "select col1 from test_view_table;",
"name": "testview_$%{}[]()&*^!@\"'`\\/#",
@@ -34,15 +35,16 @@
"datacl":[{"grantee":"postgres", "grantor":"postgres", "privileges":[{"privilege_type": "a", "privilege": true,
"with_grant":false}]}]
},
- "expected_sql_file": "create_view.sql"
+ "expected_sql_file": "create_view.sql",
+ "expected_msql_file": "create_view_msql.sql"
},
{
"type": "alter",
"name": "Alter View",
"endpoint": "NODE-view.obj_id",
"sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
"data": {
- "definition": "select col1 from test_view_table;",
"name": "testview_$%{}[]()&*^!@\"'`\\/#",
"owner": "postgres",
"schema": "public",
@@ -52,7 +54,105 @@
"datacl":[{"grantee":"postgres", "grantor":"postgres", "privileges":[{"privilege_type": "a", "privilege": true,
"with_grant":false}]}]
},
- "expected_sql_file": "alter_view.sql"
+ "expected_sql_file": "alter_view.sql",
+ "expected_msql_file": "alter_view_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (changing code)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "definition": "SELECT * FROM test_view_table;"
+ },
+ "expected_sql_file": "alter_view_definition.sql",
+ "expected_msql_file": "alter_view_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (adding privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "postgres",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":{
+ "added":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"postgres",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_add_some_priv.sql",
+ "expected_msql_file": "alter_view_add_some_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (deleting privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "postgres",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":
+ {
+ "deleted":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"postgres",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_delete_priv.sql",
+ "expected_msql_file": "alter_view_delete_priv_msql.sql"
},
{
"type": "delete",
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview.sql
new file mode 100644
index 000000000..b55878d2c
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview.sql
@@ -0,0 +1,18 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor.sql
new file mode 100644
index 000000000..be0d59bea
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor_msql.sql
new file mode 100644
index 000000000..0c51d157a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_fillfactor_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+SET(
+ FILLFACTOR = 18
+);
+
+REFRESH MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" WITH NO DATA;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter.sql
new file mode 100644
index 000000000..ee5956d1a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter_msql.sql
new file mode 100644
index 000000000..737ffaa32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_add_table_parameter_msql.sql
@@ -0,0 +1,4 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition.sql
new file mode 100644
index 000000000..41f75fbe5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition.sql
@@ -0,0 +1,12 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition_msql.sql
new file mode 100644
index 000000000..2c1b637f4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_definition_msql.sql
@@ -0,0 +1,4 @@
+DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ AS
+SELECT 12
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv.sql
new file mode 100644
index 000000000..ee5220e13
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv_msql.sql
new file mode 100644
index 000000000..3bfd1b9fb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_drop_all_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_msql.sql
new file mode 100644
index 000000000..2740b1969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_msql.sql
@@ -0,0 +1 @@
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter.sql
new file mode 100644
index 000000000..be0d59bea
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter_msql.sql
new file mode 100644
index 000000000..8aed82923
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_mview_remove_table_parameter_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_enabled = false
+);
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" RESET(
+ autovacuum_analyze_scale_factor
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view.sql
new file mode 100644
index 000000000..62ce17449
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv.sql
new file mode 100644
index 000000000..8256dc599
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv.sql
@@ -0,0 +1,19 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT DELETE, UPDATE, SELECT ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv_msql.sql
new file mode 100644
index 000000000..83f974b32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_add_some_priv_msql.sql
@@ -0,0 +1 @@
+GRANT SELECT, UPDATE, DELETE ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition.sql
new file mode 100644
index 000000000..62ce17449
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition_msql.sql
new file mode 100644
index 000000000..84f01f995
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_definition_msql.sql
@@ -0,0 +1,4 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ WITH (check_option=cascaded, security_barrier=true)
+ AS
+ SELECT * FROM test_view_table;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv.sql
new file mode 100644
index 000000000..62ce17449
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv_msql.sql
new file mode 100644
index 000000000..69c9ea663
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_delete_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_msql.sql
new file mode 100644
index 000000000..da5669912
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/alter_view_msql.sql
@@ -0,0 +1,7 @@
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (security_barrier=true);
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (check_option=cascaded);
+
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview.sql
new file mode 100644
index 000000000..121113526
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview.sql
@@ -0,0 +1,15 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview_msql.sql
new file mode 100644
index 000000000..324235f89
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_mview_msql.sql
@@ -0,0 +1,11 @@
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view.sql
new file mode 100644
index 000000000..555658907
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view.sql
@@ -0,0 +1,17 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=local
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view_msql.sql
new file mode 100644
index 000000000..7cd95ed2a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/create_view_msql.sql
@@ -0,0 +1,12 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=local
+) AS
+select col1 from test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment';
+
+GRANT INSERT ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/test_mview.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/test_mview.json
new file mode 100644
index 000000000..b945afacb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/test_mview.json
@@ -0,0 +1,256 @@
+{
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Materialised Views",
+ "endpoint": "NODE-mview.obj",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql",
+ "data": {
+ "spcname": "pg_default",
+ "toast_autovacuum_enabled": false,
+ "autovacuum_enabled": false,
+ "schema": "public",
+ "owner": "enterprisedb",
+ "vacuum_table": [
+ {
+ "name": "autovacuum_analyze_scale_factor"
+ },
+ {
+ "name": "autovacuum_analyze_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "vacuum_toast": [
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "datacl": [],
+ "seclabels": [],
+ "name": "testmview_$%{}[]()&*^!/@`#",
+ "comment": "comment1",
+ "definition": "SELECT 1"
+ },
+ "expected_sql_file": "create_mview.sql",
+ "expected_msql_file": "create_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Adding privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview.sql",
+ "expected_msql_file": "alter_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Remove all privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "deleted": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_drop_all_priv.sql",
+ "expected_msql_file": "alter_mview_drop_all_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (change definition)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "definition": "SELECT 12;"
+ },
+ "expected_sql_file": "alter_mview_definition.sql",
+ "expected_msql_file": "alter_mview_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Fillfactor)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "fillfactor": "18",
+ "with_data": false
+ },
+ "expected_sql_file": "alter_mview_add_fillfactor.sql",
+ "expected_msql_file": "alter_mview_add_fillfactor_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (add table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": true,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": 0.2
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_add_table_parameter.sql",
+ "expected_msql_file": "alter_mview_add_table_parameter_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (remove table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": false,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": null
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_remove_table_parameter.sql",
+ "expected_msql_file": "alter_mview_remove_table_parameter_msql.sql"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/tests.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/tests.json
new file mode 100644
index 000000000..9bcb973d6
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/11_plus/tests.json
@@ -0,0 +1,178 @@
+{
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Table for Views",
+ "endpoint": "NODE-table.obj",
+ "sql_endpoint": "NODE-table.sql_id",
+ "data": {
+ "name": "test_view_table",
+ "check_constraint": [],
+ "coll_inherits": "[]",
+ "columns": [
+ {
+ "name": "col1",
+ "cltype": "integer"
+ }
+ ],
+ "schema": "public"
+ }
+ },
+ {
+ "type": "create",
+ "name": "Create View",
+ "endpoint": "NODE-view.obj",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql",
+ "data": {
+ "definition": "select col1 from test_view_table;",
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "local",
+ "security_barrier": false,
+ "comment":"Testcomment",
+ "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": true,
+ "with_grant":false}]}]
+ },
+ "expected_sql_file": "create_view.sql",
+ "expected_msql_file": "create_view_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": true,
+ "with_grant":false}]}]
+ },
+ "expected_sql_file": "alter_view.sql",
+ "expected_msql_file": "alter_view_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (changing code)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "definition": "SELECT * FROM test_view_table;"
+ },
+ "expected_sql_file": "alter_view_definition.sql",
+ "expected_msql_file": "alter_view_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (adding privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":{
+ "added":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"enterprisedb",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_add_some_priv.sql",
+ "expected_msql_file": "alter_view_add_some_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (deleting privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":
+ {
+ "deleted":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"enterprisedb",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_delete_priv.sql",
+ "expected_msql_file": "alter_view_delete_priv_msql.sql"
+ },
+ {
+ "type": "delete",
+ "name": "Drop View",
+ "endpoint": "NODE-view.obj_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public"
+ }
+ },
+ {
+ "type": "delete",
+ "name": "Drop Table for view",
+ "endpoint": "NODE-table.obj_id",
+ "data": {
+ "name": "test_view_table",
+ "owner": "enterprisedb",
+ "schema": "public"
+ }
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview.sql
index fad1f9e31..b55878d2c 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview.sql
@@ -1,13 +1,18 @@
--- View: public."testmview_$%{}[]()&*^!@""'`\/#"
+-- View: public."testmview_$%{}[]()&*^!/@`#"
--- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#";
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
-CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
TABLESPACE pg_default
AS
- SELECT test_mview_table.col1
- FROM test_mview_table
-WITH DATA;
+ SELECT 1
+WITH NO DATA;
-ALTER TABLE public."testmview_$%{}[]()&*^!@""'`\/#"
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor.sql
new file mode 100644
index 000000000..be0d59bea
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor_msql.sql
new file mode 100644
index 000000000..0c51d157a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_fillfactor_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+SET(
+ FILLFACTOR = 18
+);
+
+REFRESH MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" WITH NO DATA;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter.sql
new file mode 100644
index 000000000..ee5956d1a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter_msql.sql
new file mode 100644
index 000000000..737ffaa32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_parameter_msql.sql
@@ -0,0 +1,4 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_enabled = true
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition.sql
new file mode 100644
index 000000000..41f75fbe5
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition.sql
@@ -0,0 +1,12 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition_msql.sql
new file mode 100644
index 000000000..2c1b637f4
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_definition_msql.sql
@@ -0,0 +1,4 @@
+DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ AS
+SELECT 12
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv.sql
new file mode 100644
index 000000000..ee5220e13
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv.sql
@@ -0,0 +1,17 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv_msql.sql
new file mode 100644
index 000000000..3bfd1b9fb
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_drop_all_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_msql.sql
new file mode 100644
index 000000000..2740b1969
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_msql.sql
@@ -0,0 +1 @@
+GRANT ALL ON TABLE public."testmview_$%{}[]()&*^!/@`#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter.sql
new file mode 100644
index 000000000..be0d59bea
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter.sql
@@ -0,0 +1,16 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 18,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 12
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter_msql.sql
new file mode 100644
index 000000000..8aed82923
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_remove_table_parameter_msql.sql
@@ -0,0 +1,6 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_enabled = false
+);
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" RESET(
+ autovacuum_analyze_scale_factor
+);
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql
index 6d33e6400..62ce17449 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql
@@ -1,8 +1,8 @@
--- View: public."testview1_$%{}[]()&*^!@""'`\/#"
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
--- DROP VIEW public."testview1_$%{}[]()&*^!@""'`\/#";
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
-CREATE OR REPLACE VIEW public."testview1_$%{}[]()&*^!@""'`\/#"
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
WITH (
check_option=cascaded,
security_barrier=true
@@ -10,9 +10,9 @@ WITH (
SELECT test_view_table.col1
FROM test_view_table;
-ALTER TABLE public."testview1_$%{}[]()&*^!@""'`\/#"
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
OWNER TO enterprisedb;
-COMMENT ON VIEW public."testview1_$%{}[]()&*^!@""'`\/#"
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
IS 'Testcomment-updated';
-GRANT ALL ON TABLE public."testview1_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv.sql
new file mode 100644
index 000000000..b4f9cd21d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv.sql
@@ -0,0 +1,19 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT SELECT, UPDATE, DELETE ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv_msql.sql
new file mode 100644
index 000000000..83f974b32
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_some_priv_msql.sql
@@ -0,0 +1 @@
+GRANT SELECT, UPDATE, DELETE ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition.sql
new file mode 100644
index 000000000..62ce17449
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition_msql.sql
new file mode 100644
index 000000000..84f01f995
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_definition_msql.sql
@@ -0,0 +1,4 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ WITH (check_option=cascaded, security_barrier=true)
+ AS
+ SELECT * FROM test_view_table;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv.sql
new file mode 100644
index 000000000..62ce17449
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv.sql
@@ -0,0 +1,18 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=cascaded,
+ security_barrier=true
+) AS
+ SELECT test_view_table.col1
+ FROM test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv_msql.sql
new file mode 100644
index 000000000..69c9ea663
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_delete_priv_msql.sql
@@ -0,0 +1 @@
+REVOKE ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" FROM PUBLIC;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_msql.sql
new file mode 100644
index 000000000..da5669912
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_msql.sql
@@ -0,0 +1,7 @@
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (security_barrier=true);
+ALTER VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ SET (check_option=cascaded);
+
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview.sql
index e497dc37e..121113526 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview.sql
@@ -1,17 +1,15 @@
--- View: public."testmview_$%{}[]()&*^!@""'`\/#"
+-- View: public."testmview_$%{}[]()&*^!/@`#"
--- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#";
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
-CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
TABLESPACE pg_default
AS
- SELECT test_mview_table.col1
- FROM test_mview_table
+ SELECT 1
WITH NO DATA;
-ALTER TABLE public."testmview_$%{}[]()&*^!@""'`\/#"
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
OWNER TO enterprisedb;
-COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!@""'`\/#"
- IS 'Testcomment';
-
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview_msql.sql
new file mode 100644
index 000000000..324235f89
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview_msql.sql
@@ -0,0 +1,11 @@
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+TABLESPACE pg_default
+AS
+SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment1';
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view.sql
index 37a68a320..555658907 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view.sql
@@ -15,7 +15,3 @@ COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
IS 'Testcomment';
GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
-
-
-
-
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view_msql.sql
new file mode 100644
index 000000000..7cd95ed2a
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view_msql.sql
@@ -0,0 +1,12 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+WITH (
+ check_option=local
+) AS
+select col1 from test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment';
+
+GRANT INSERT ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_mview.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_mview.json
index 25df33592..b945afacb 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_mview.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_mview.json
@@ -1,87 +1,256 @@
{
- "scenarios": [{
- "type": "create",
- "name": "Create Table for MViews",
- "endpoint": "NODE-table.obj",
- "sql_endpoint": "NODE-table.sql_id",
- "data": {
- "name": "test_mview_table",
- "check_constraint": [],
- "coll_inherits": "[]",
- "columns": [{
- "name": "col1",
- "cltype": "integer"
- }],
- "schema": "public"
- }
- },
- {
- "type": "create",
- "name": "Create MView",
- "endpoint": "NODE-mview.obj",
- "sql_endpoint": "NODE-mview.sql_id",
- "data": {
- "definition": "select col1 from test_mview_table;",
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "enterprisedb",
- "schema": "public",
- "comment": "Testcomment",
- "autovacuum_enabled": false,
- "datacl": [],
- "seclabels": [],
- "toast_autovacuum_enabled": false,
- "vacuum_table": [{
- "name": "autovacuum_analyze_scale_factor"
- }],
- "vacuum_toast": [{
- "name": "autovacuum_freeze_max_age"
- }]
- },
- "expected_sql_file": "create_mview.sql"
- },
- {
- "type": "alter",
- "name": "Alter MView",
- "endpoint": "NODE-mview.obj_id",
- "sql_endpoint": "NODE-mview.sql_id",
- "data": {
- "definition": "select col1 from test_mview_table;",
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "enterprisedb",
- "schema": "public",
- "comment": "Testcomment",
- "autovacuum_enabled": false,
- "datacl": [],
- "seclabels": [],
- "toast_autovacuum_enabled": false,
- "vacuum_table": [{
- "name": "autovacuum_analyze_scale_factor"
- }],
- "vacuum_toast": [{
- "name": "autovacuum_freeze_max_age"
- }]
- },
- "expected_sql_file": "alter_mview.sql"
- },
- {
- "type": "delete",
- "name": "Drop MView",
- "endpoint": "NODE-mview.obj_id",
- "data": {
- "name": "testmview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "enterprisedb",
- "schema": "public"
- }
- },
- {
- "type": "delete",
- "name": "Drop Table for mview",
- "endpoint": "NODE-table.obj_id",
- "data": {
- "name": "test_mview_table",
- "owner": "postgres",
- "schema": "public"
- }
- }
- ]
-}
\ No newline at end of file
+ "scenarios": [
+ {
+ "type": "create",
+ "name": "Create Materialised Views",
+ "endpoint": "NODE-mview.obj",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql",
+ "data": {
+ "spcname": "pg_default",
+ "toast_autovacuum_enabled": false,
+ "autovacuum_enabled": false,
+ "schema": "public",
+ "owner": "enterprisedb",
+ "vacuum_table": [
+ {
+ "name": "autovacuum_analyze_scale_factor"
+ },
+ {
+ "name": "autovacuum_analyze_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "vacuum_toast": [
+ {
+ "name": "autovacuum_freeze_max_age"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay"
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit"
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor"
+ },
+ {
+ "name": "autovacuum_vacuum_threshold"
+ },
+ {
+ "name": "autovacuum_freeze_min_age"
+ },
+ {
+ "name": "autovacuum_freeze_table_age"
+ }
+ ],
+ "datacl": [],
+ "seclabels": [],
+ "name": "testmview_$%{}[]()&*^!/@`#",
+ "comment": "comment1",
+ "definition": "SELECT 1"
+ },
+ "expected_sql_file": "create_mview.sql",
+ "expected_msql_file": "create_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Adding privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview.sql",
+ "expected_msql_file": "alter_mview_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Remove all privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "datacl": {
+ "deleted": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "r",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "w",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "d",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "D",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "x",
+ "privilege": true,
+ "with_grant": false
+ },
+ {
+ "privilege_type": "t",
+ "privilege": true,
+ "with_grant": false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_drop_all_priv.sql",
+ "expected_msql_file": "alter_mview_drop_all_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (change definition)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "definition": "SELECT 12;"
+ },
+ "expected_sql_file": "alter_mview_definition.sql",
+ "expected_msql_file": "alter_mview_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Fillfactor)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "fillfactor": "18",
+ "with_data": false
+ },
+ "expected_sql_file": "alter_mview_add_fillfactor.sql",
+ "expected_msql_file": "alter_mview_add_fillfactor_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (add table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": true,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": 0.2
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_add_table_parameter.sql",
+ "expected_msql_file": "alter_mview_add_table_parameter_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (remove table parameters)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "msql_endpoint": "NODE-mview.msql_id",
+ "data": {
+ "autovacuum_custom": true,
+ "autovacuum_enabled": false,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": null
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_remove_table_parameter.sql",
+ "expected_msql_file": "alter_mview_remove_table_parameter_msql.sql"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json
index ce9aa5d65..9bcb973d6 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json
@@ -1,18 +1,19 @@
{
- "scenarios": [{
+ "scenarios": [
+ {
"type": "create",
"name": "Create Table for Views",
"endpoint": "NODE-table.obj",
"sql_endpoint": "NODE-table.sql_id",
- "data":{
- "name":"test_view_table",
+ "data": {
+ "name": "test_view_table",
"check_constraint": [],
"coll_inherits": "[]",
"columns": [
- {
- "name": "col1",
- "cltype": "integer"
- }
+ {
+ "name": "col1",
+ "cltype": "integer"
+ }
],
"schema": "public"
}
@@ -22,55 +23,156 @@
"name": "Create View",
"endpoint": "NODE-view.obj",
"sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql",
"data": {
- "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": true, "with_grant":
- false}]}],
"definition": "select col1 from test_view_table;",
"name": "testview_$%{}[]()&*^!@\"'`\\/#",
"owner": "enterprisedb",
"schema": "public",
"check_option": "local",
"security_barrier": false,
- "comment":"Testcomment"
+ "comment":"Testcomment",
+ "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": true,
+ "with_grant":false}]}]
},
- "expected_sql_file": "create_view.sql"
+ "expected_sql_file": "create_view.sql",
+ "expected_msql_file": "create_view_msql.sql"
},
{
"type": "alter",
"name": "Alter View",
"endpoint": "NODE-view.obj_id",
"sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
"data": {
- "definition": "select col1 from test_view_table;",
- "name": "testview1_$%{}[]()&*^!@\"'`\\/#",
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
"owner": "enterprisedb",
"schema": "public",
"check_option": "cascaded",
"security_barrier": true,
"comment":"Testcomment-updated",
- "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": false,
+ "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": true,
"with_grant":false}]}]
},
- "expected_sql_file": "alter_view.sql"
+ "expected_sql_file": "alter_view.sql",
+ "expected_msql_file": "alter_view_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (changing code)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "definition": "SELECT * FROM test_view_table;"
+ },
+ "expected_sql_file": "alter_view_definition.sql",
+ "expected_msql_file": "alter_view_definition_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (adding privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":{
+ "added":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"enterprisedb",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_add_some_priv.sql",
+ "expected_msql_file": "alter_view_add_some_priv_msql.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (deleting privileges)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "msql_endpoint": "NODE-view.msql_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "enterprisedb",
+ "schema": "public",
+ "check_option": "cascaded",
+ "security_barrier": true,
+ "comment":"Testcomment-updated",
+ "datacl":
+ {
+ "deleted":[
+ {
+ "grantee":"PUBLIC",
+ "grantor":"enterprisedb",
+ "privileges":[
+ {
+ "privilege_type":"r",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"w",
+ "privilege":true,
+ "with_grant":false
+ },
+ {
+ "privilege_type":"d",
+ "privilege":true,
+ "with_grant":false
+ }
+ ]
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_view_delete_priv.sql",
+ "expected_msql_file": "alter_view_delete_priv_msql.sql"
},
{
"type": "delete",
"name": "Drop View",
"endpoint": "NODE-view.obj_id",
"data": {
- "name": "testview1_$%{}[]()&*^!@\"'`\\/#",
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
"owner": "enterprisedb",
"schema": "public"
}
},
{
"type": "delete",
- "name": "Drop Table for Views",
+ "name": "Drop Table for view",
"endpoint": "NODE-table.obj_id",
"data": {
"name": "test_view_table",
"owner": "enterprisedb",
"schema": "public"
}
- }]
+ }
+ ]
}
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
In-Reply-To: <CAKtn9dNz1ozbJWzJw_qVLx3QAxcNSjCCJWqB8HW3Qn5Hooz-pA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox