public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rahul Shirsat <[email protected]>
To: [email protected]
Subject: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
Date: Tue, 19 Nov 2019 12:47:16 +0530
Message-ID: <CAKtn9dOLhxuNmtPbBvbvDq29YFQ48DTXhv5QGjav7KG8Eht2SA@mail.gmail.com> (raw)
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"
- }
- }]
-}
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: [pgAdmin][RM4696] Add RE-SQL/MSQL test cases for Materialized View
In-Reply-To: <CAKtn9dOLhxuNmtPbBvbvDq29YFQ48DTXhv5QGjav7KG8Eht2SA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox