public inbox for [email protected]
help / color / mirror / Atom feed[pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
5+ messages / 2 participants
[nested] [flat]
* [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
@ 2019-11-19 07:17 Rahul Shirsat <[email protected]>
2019-11-19 12:57 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Rahul Shirsat @ 2019-11-19 07:17 UTC (permalink / raw)
To: pgadmin-hackers
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.
Attachments:
[application/octet-stream] RM4696.patch (65.4K, 3-RM4696.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/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/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..2d10f9c7f 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,22 @@
--- 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_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_enabled = false
+)
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 'comment2';
+
+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_table_toast_data.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_toast_data.sql
new file mode 100644
index 000000000..5d4f5fc24
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_toast_data.sql
@@ -0,0 +1,30 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_analyze_threshold = 30,
+ autovacuum_freeze_max_age = 199999999,
+ autovacuum_vacuum_cost_delay = 3,
+ autovacuum_vacuum_cost_limit = 10,
+ autovacuum_vacuum_scale_factor = 0.3,
+ autovacuum_vacuum_threshold = 40,
+ autovacuum_freeze_min_age = 49000000,
+ autovacuum_freeze_table_age = 140000000,
+ autovacuum_enabled = true
+)
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment2';
+
+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_add_table_toast_data_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_toast_data_msql.sql
new file mode 100644
index 000000000..7d9eb7186
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_mview_add_table_toast_data_msql.sql
@@ -0,0 +1,11 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_analyze_threshold = 30,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_freeze_max_age = 199999999,
+ autovacuum_vacuum_cost_delay = 3,
+ autovacuum_vacuum_cost_limit = 10,
+ autovacuum_vacuum_scale_factor = 0.3,
+ autovacuum_vacuum_threshold = 40,
+ autovacuum_freeze_min_age = 49000000,
+ autovacuum_freeze_table_age = 140000000
+);
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..5d771b200
--- /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,21 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO postgres;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment2';
+
+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_view_add_drop_all_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_drop_all_priv.sql
new file mode 100644
index 000000000..0a0dd7766
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_drop_all_priv.sql
@@ -0,0 +1,16 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ 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 INSERT, TRUNCATE, REFERENCES, TRIGGER 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_drop_all_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_drop_all_priv_msql.sql
new file mode 100644
index 000000000..fb54bfa28
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/alter_view_add_drop_all_priv_msql.sql
@@ -0,0 +1,7 @@
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
+GRANT INSERT, TRUNCATE, REFERENCES, TRIGGER ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
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.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view.sql
index a1901611a..e051a6812 100644
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view.sql
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view.sql
@@ -3,15 +3,14 @@
-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
-WITH (
- check_option=local
-) AS
+ AS
SELECT test_view_table.col1
FROM test_view_table;
ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
OWNER TO postgres;
COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
- IS 'Testcomment';
+ IS 'comment1';
GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO postgres;
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
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..6cb73563d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/create_view_msql.sql
@@ -0,0 +1,11 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ AS
+select * from test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO postgres;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'comment1';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
+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/test_mview.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_mview.json
index 72eb226f3..df33d9ef4 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,247 @@
{
- "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",
+ "data": {
+ "oid": 17869,
+ "fillfactor": "17",
+ "comment": "comment2",
+ "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"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Remove all privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "data": {
+ "oid": 17869,
+ "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 (add table & toast data in Parameter)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "data": {
+ "oid": 17869,
+ "autovacuum_enabled": true,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_threshold",
+ "value": 30
+ },
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": 0.2
+ },
+ {
+ "name": "autovacuum_freeze_max_age",
+ "value": 199999999
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay",
+ "value": 3
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit",
+ "value": 10
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor",
+ "value": 0.3
+ },
+ {
+ "name": "autovacuum_vacuum_threshold",
+ "value": 40
+ },
+ {
+ "name": "autovacuum_freeze_min_age",
+ "value": 49000000
+ },
+ {
+ "name": "autovacuum_freeze_table_age",
+ "value": 140000000
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_add_table_toast_data.sql",
+ "expected_msql_file": "alter_mview_add_table_toast_data_msql.sql"
+ },
+ {
+ "type": "delete",
+ "name": "Drop Materialised Views",
+ "endpoint": "NODE-mview.obj_id"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_view.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_view.json
new file mode 100644
index 000000000..229d4495b
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/test_view.json
@@ -0,0 +1,212 @@
+{
+ "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",
+ "data": {
+ "schema": "public",
+ "owner": "postgres",
+ "datacl": [
+ {
+ "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
+ }
+ ]
+ },
+ {
+ "grantee": "postgres",
+ "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
+ }
+ ]
+ }
+ ],
+ "seclabels": [],
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "comment": "comment1",
+ "definition": "select * from test_view_table;",
+ "check_option": "no"
+ },
+ "expected_sql_file": "create_view.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (Add/Delete security permissions)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "data": {
+ "security_barrier": false,
+ "comment": "Testcomment-updated",
+ "datacl": {
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "postgres",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "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
+ }
+ ]
+ }
+ ],
+ "deleted": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "postgres",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "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_view_add_drop_all_priv.sql",
+ "expected_msql_file": "alter_view_add_drop_all_priv_msql.sql"
+ },
+ {
+ "type": "delete",
+ "name": "Drop View",
+ "endpoint": "NODE-view.obj_id",
+ "data": {
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "owner": "postgres",
+ "schema": "public"
+ }
+ },
+ {
+ "type": "delete",
+ "name": "Drop Table for view",
+ "endpoint": "NODE-table.obj_id",
+ "data": {
+ "name": "test_view_table",
+ "owner": "postgres",
+ "schema": "public"
+ }
+ }
+ ]
+}
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
deleted file mode 100644
index 864ae34d1..000000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/pg/9.4_plus/tests.json
+++ /dev/null
@@ -1,78 +0,0 @@
-{
- "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",
- "data": {
- "definition": "select col1 from test_view_table;",
- "name": "testview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public",
- "check_option": "local",
- "security_barrier": false,
- "comment":"Testcomment",
- "datacl":[{"grantee":"postgres", "grantor":"postgres", "privileges":[{"privilege_type": "a", "privilege": true,
- "with_grant":false}]}]
- },
- "expected_sql_file": "create_view.sql"
- },
- {
- "type": "alter",
- "name": "Alter View",
- "endpoint": "NODE-view.obj_id",
- "sql_endpoint": "NODE-view.sql_id",
- "data": {
- "definition": "select col1 from test_view_table;",
- "name": "testview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public",
- "check_option": "cascaded",
- "security_barrier": true,
- "comment":"Testcomment-updated",
- "datacl":[{"grantee":"postgres", "grantor":"postgres", "privileges":[{"privilege_type": "a", "privilege": true,
- "with_grant":false}]}]
- },
- "expected_sql_file": "alter_view.sql"
- },
- {
- "type": "delete",
- "name": "Drop View",
- "endpoint": "NODE-view.obj_id",
- "data": {
- "name": "testview_$%{}[]()&*^!@\"'`\\/#",
- "owner": "postgres",
- "schema": "public"
- }
- },
- {
- "type": "delete",
- "name": "Drop Table for view",
- "endpoint": "NODE-table.obj_id",
- "data": {
- "name": "test_view_table",
- "owner": "postgres",
- "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..006f53b7f 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,22 @@
--- 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_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_enabled = false
+)
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 'comment2';
+
+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_table_toast_data.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_toast_data.sql
new file mode 100644
index 000000000..652094b98
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_toast_data.sql
@@ -0,0 +1,30 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_analyze_threshold = 30,
+ autovacuum_freeze_max_age = 199999999,
+ autovacuum_vacuum_cost_delay = 3,
+ autovacuum_vacuum_cost_limit = 10,
+ autovacuum_vacuum_scale_factor = 0.3,
+ autovacuum_vacuum_threshold = 40,
+ autovacuum_freeze_min_age = 49000000,
+ autovacuum_freeze_table_age = 140000000,
+ autovacuum_enabled = true
+)
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment2';
+
+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_table_toast_data_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_toast_data_msql.sql
new file mode 100644
index 000000000..7d9eb7186
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_mview_add_table_toast_data_msql.sql
@@ -0,0 +1,11 @@
+ALTER MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#" SET(
+ autovacuum_analyze_threshold = 30,
+ autovacuum_analyze_scale_factor = 0.2,
+ autovacuum_freeze_max_age = 199999999,
+ autovacuum_vacuum_cost_delay = 3,
+ autovacuum_vacuum_cost_limit = 10,
+ autovacuum_vacuum_scale_factor = 0.3,
+ autovacuum_vacuum_threshold = 40,
+ autovacuum_freeze_min_age = 49000000,
+ autovacuum_freeze_table_age = 140000000
+);
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..463418135
--- /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,21 @@
+-- View: public."testmview_$%{}[]()&*^!/@`#"
+
+-- DROP MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#";
+
+CREATE MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+WITH (
+ FILLFACTOR = 17,
+ autovacuum_enabled = false
+)
+TABLESPACE pg_default
+AS
+ SELECT 1
+WITH NO DATA;
+
+ALTER TABLE public."testmview_$%{}[]()&*^!/@`#"
+ OWNER TO enterprisedb;
+
+COMMENT ON MATERIALIZED VIEW public."testmview_$%{}[]()&*^!/@`#"
+ IS 'comment2';
+
+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_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql
deleted file mode 100644
index 6d33e6400..000000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view.sql
+++ /dev/null
@@ -1,18 +0,0 @@
--- View: public."testview1_$%{}[]()&*^!@""'`\/#"
-
--- DROP VIEW public."testview1_$%{}[]()&*^!@""'`\/#";
-
-CREATE OR REPLACE VIEW public."testview1_$%{}[]()&*^!@""'`\/#"
-WITH (
- check_option=cascaded,
- security_barrier=true
-) AS
- SELECT test_view_table.col1
- FROM test_view_table;
-
-ALTER TABLE public."testview1_$%{}[]()&*^!@""'`\/#"
- OWNER TO enterprisedb;
-COMMENT ON VIEW public."testview1_$%{}[]()&*^!@""'`\/#"
- IS 'Testcomment-updated';
-
-GRANT ALL ON TABLE public."testview1_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_drop_all_priv.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_drop_all_priv.sql
new file mode 100644
index 000000000..b8aa1a269
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_drop_all_priv.sql
@@ -0,0 +1,16 @@
+-- View: public."testview_$%{}[]()&*^!@""'`\/#"
+
+-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
+
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ 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 TRIGGER, REFERENCES, TRUNCATE, INSERT 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_drop_all_priv_msql.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_drop_all_priv_msql.sql
new file mode 100644
index 000000000..503b3935d
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/alter_view_add_drop_all_priv_msql.sql
@@ -0,0 +1,7 @@
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'Testcomment-updated';
+
+GRANT ALL ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO enterprisedb;
+GRANT INSERT, TRUNCATE, REFERENCES, TRIGGER ON TABLE public."testview_$%{}[]()&*^!@""'`\/#" TO PUBLIC;
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..ee5220e13 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,17 @@
--- 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';
+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/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..3d14688a2
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_mview_msql.sql
@@ -0,0 +1,13 @@
+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/create_view.sql b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view.sql
index 37a68a320..54c3d5db6 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
@@ -3,19 +3,14 @@
-- DROP VIEW public."testview_$%{}[]()&*^!@""'`\/#";
CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
-WITH (
- check_option=local
-) AS
+ AS
SELECT test_view_table.col1
FROM test_view_table;
ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
OWNER TO enterprisedb;
COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
- IS 'Testcomment';
+ IS 'comment1';
+GRANT ALL 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/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..d309d5ccf
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/create_view_msql.sql
@@ -0,0 +1,11 @@
+CREATE OR REPLACE VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ AS
+select * from test_view_table;
+
+ALTER TABLE public."testview_$%{}[]()&*^!@""'`\/#"
+ OWNER TO enterprisedb;
+COMMENT ON VIEW public."testview_$%{}[]()&*^!@""'`\/#"
+ IS 'comment1';
+
+GRANT ALL 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/test_mview.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_mview.json
index 25df33592..0d2b91f8b 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,330 @@
{
- "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": [
+ {
+ "grantee": "enterprisedb",
+ "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
+ }
+ ]
+ }
+ ],
+ "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",
+ "data": {
+ "oid": 17869,
+ "fillfactor": "17",
+ "comment": "comment2",
+ "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
+ }
+ ]
+ },
+ {
+ "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"
+ },
+ {
+ "type": "alter",
+ "name": "Alter Materialised Views (Remove all privileges)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "data": {
+ "oid": 17869,
+ "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 (add table & toast data in Parameter)",
+ "endpoint": "NODE-mview.obj_id",
+ "sql_endpoint": "NODE-mview.sql_id",
+ "data": {
+ "oid": 17869,
+ "autovacuum_enabled": true,
+ "vacuum_table": {
+ "changed": [
+ {
+ "name": "autovacuum_analyze_threshold",
+ "value": 30
+ },
+ {
+ "name": "autovacuum_analyze_scale_factor",
+ "value": 0.2
+ },
+ {
+ "name": "autovacuum_freeze_max_age",
+ "value": 199999999
+ },
+ {
+ "name": "autovacuum_vacuum_cost_delay",
+ "value": 3
+ },
+ {
+ "name": "autovacuum_vacuum_cost_limit",
+ "value": 10
+ },
+ {
+ "name": "autovacuum_vacuum_scale_factor",
+ "value": 0.3
+ },
+ {
+ "name": "autovacuum_vacuum_threshold",
+ "value": 40
+ },
+ {
+ "name": "autovacuum_freeze_min_age",
+ "value": 49000000
+ },
+ {
+ "name": "autovacuum_freeze_table_age",
+ "value": 140000000
+ }
+ ]
+ }
+ },
+ "expected_sql_file": "alter_mview_add_table_toast_data.sql",
+ "expected_msql_file": "alter_mview_add_table_toast_data_msql.sql"
+ },
+ {
+ "type": "delete",
+ "name": "Drop Materialised Views",
+ "endpoint": "NODE-mview.obj_id"
+ }
+ ]
+}
diff --git a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_view.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_view.json
new file mode 100644
index 000000000..0859a9504
--- /dev/null
+++ b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/test_view.json
@@ -0,0 +1,212 @@
+{
+ "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",
+ "data": {
+ "schema": "public",
+ "owner": "enterprisedb",
+ "datacl": [
+ {
+ "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
+ }
+ ]
+ },
+ {
+ "grantee": "enterprisedb",
+ "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
+ }
+ ]
+ }
+ ],
+ "seclabels": [],
+ "name": "testview_$%{}[]()&*^!@\"'`\\/#",
+ "comment": "comment1",
+ "definition": "select * from test_view_table;",
+ "check_option": "no"
+ },
+ "expected_sql_file": "create_view.sql"
+ },
+ {
+ "type": "alter",
+ "name": "Alter View (Add/Delete security permissions)",
+ "endpoint": "NODE-view.obj_id",
+ "sql_endpoint": "NODE-view.sql_id",
+ "data": {
+ "security_barrier": false,
+ "comment": "Testcomment-updated",
+ "datacl": {
+ "added": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "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
+ }
+ ]
+ }
+ ],
+ "deleted": [
+ {
+ "grantee": "PUBLIC",
+ "grantor": "enterprisedb",
+ "privileges": [
+ {
+ "privilege_type": "a",
+ "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_view_add_drop_all_priv.sql",
+ "expected_msql_file": "alter_view_add_drop_all_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/tests.json b/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json
deleted file mode 100644
index ce9aa5d65..000000000
--- a/web/pgadmin/browser/server_groups/servers/databases/schemas/views/tests/ppas/9.4_plus/tests.json
+++ /dev/null
@@ -1,76 +0,0 @@
-{
- "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",
- "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"
- },
- "expected_sql_file": "create_view.sql"
- },
- {
- "type": "alter",
- "name": "Alter View",
- "endpoint": "NODE-view.obj_id",
- "sql_endpoint": "NODE-view.sql_id",
- "data": {
- "definition": "select col1 from test_view_table;",
- "name": "testview1_$%{}[]()&*^!@\"'`\\/#",
- "owner": "enterprisedb",
- "schema": "public",
- "check_option": "cascaded",
- "security_barrier": true,
- "comment":"Testcomment-updated",
- "datacl":[{"grantee":"enterprisedb", "grantor":"enterprisedb", "privileges":[{"privilege_type": "a", "privilege": false,
- "with_grant":false}]}]
- },
- "expected_sql_file": "alter_view.sql"
- },
- {
- "type": "delete",
- "name": "Drop View",
- "endpoint": "NODE-view.obj_id",
- "data": {
- "name": "testview1_$%{}[]()&*^!@\"'`\\/#",
- "owner": "enterprisedb",
- "schema": "public"
- }
- },
- {
- "type": "delete",
- "name": "Drop Table for Views",
- "endpoint": "NODE-table.obj_id",
- "data": {
- "name": "test_view_table",
- "owner": "enterprisedb",
- "schema": "public"
- }
- }]
-}
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
2019-11-19 07:17 [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
@ 2019-11-19 12:57 ` Akshay Joshi <[email protected]>
2019-11-21 07:38 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Akshay Joshi @ 2019-11-19 12:57 UTC (permalink / raw)
To: Rahul Shirsat <[email protected]>; +Cc: pgadmin-hackers
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*
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
2019-11-19 07:17 [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
2019-11-19 12:57 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
@ 2019-11-21 07:38 ` Akshay Joshi <[email protected]>
2019-11-22 19:07 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Akshay Joshi @ 2019-11-21 07:38 UTC (permalink / raw)
To: Rahul Shirsat <[email protected]>; +Cc: pgadmin-hackers
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*
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
2019-11-19 07:17 [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
2019-11-19 12:57 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
2019-11-21 07:38 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
@ 2019-11-22 19:07 ` Rahul Shirsat <[email protected]>
2019-11-25 05:41 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Rahul Shirsat @ 2019-11-22 19:07 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: pgadmin-hackers
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"
}
- }]
+ }
+ ]
}
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
2019-11-19 07:17 [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
2019-11-19 12:57 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
2019-11-21 07:38 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Akshay Joshi <[email protected]>
2019-11-22 19:07 ` Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
@ 2019-11-25 05:41 ` Akshay Joshi <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Akshay Joshi @ 2019-11-25 05:41 UTC (permalink / raw)
To: Rahul Shirsat <[email protected]>; +Cc: pgadmin-hackers
Thanks, patch applied.
On Sat, Nov 23, 2019 at 12:37 AM Rahul Shirsat <
[email protected]> wrote:
> 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.
>
--
*Thanks & Regards*
*Akshay Joshi*
*Sr. Software Architect*
*EnterpriseDB Software India Private Limited*
*Mobile: +91 976-788-8246*
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-11-25 05:41 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-11-19 07:17 [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View Rahul Shirsat <[email protected]>
2019-11-19 12:57 ` Akshay Joshi <[email protected]>
2019-11-21 07:38 ` Akshay Joshi <[email protected]>
2019-11-22 19:07 ` Rahul Shirsat <[email protected]>
2019-11-25 05:41 ` Akshay Joshi <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox