Materialize/test/sqllogictest/redacted.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# Test requires stable object IDs
reset-server

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_connection_validation_syntax TO true;
----
COMPLETE 0

statement ok
CREATE TABLE t (i INT)

query T multiline
SELECT redacted_create_sql FROM mz_tables WHERE name = 't'
----
CREATE TABLE materialize.public.t (i [s20 AS pg_catalog.int4])
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE TABLE t);
----
CREATE TABLE materialize.public.t (i pg_catalog.int4);
EOF

statement ok
CREATE CONNECTION kafka_conn TO KAFKA (BROKER 'localhost:9092', SECURITY PROTOCOL PLAINTEXT) WITH (VALIDATE = false);

query T multiline
SELECT redacted_create_sql FROM mz_connections WHERE name = 'kafka_conn'
----
CREATE CONNECTION materialize.public.kafka_conn TO KAFKA (BROKER = 'localhost:9092', SECURITY PROTOCOL = plaintext)
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE CONNECTION kafka_conn);
----
CREATE CONNECTION materialize.public.kafka_conn TO KAFKA (BROKER = 'localhost:9092', SECURITY PROTOCOL = plaintext);
EOF

query T multiline
SELECT pretty_sql(redacted_create_sql) FROM mz_connections WHERE name = 'kafka_conn'
----
CREATE CONNECTION materialize.public.kafka_conn TO KAFKA (BROKER = 'localhost:9092', SECURITY PROTOCOL = plaintext);
EOF

query T multiline
SELECT pretty_sql(create_sql) FROM (SHOW REDACTED CREATE CONNECTION kafka_conn);
----
CREATE CONNECTION materialize.public.kafka_conn TO KAFKA (BROKER = 'localhost:9092', SECURITY PROTOCOL = plaintext);
EOF

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_redacted_test_option TO true;
----
COMPLETE 0

statement ok
CREATE TABLE redactable_t (a int) WITH (RETAIN HISTORY = FOR '2s', REDACTED = 'pii');

query T multiline
SELECT redacted_create_sql FROM mz_tables WHERE name = 'redactable_t'
----
CREATE TABLE materialize.public.redactable_t (a [s20 AS pg_catalog.int4]) WITH (RETAIN HISTORY = FOR '2s', REDACTED = '<REDACTED>')
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE TABLE redactable_t);
----
CREATE TABLE materialize.public.redactable_t (a pg_catalog.int4) WITH (RETAIN HISTORY = FOR '2s', REDACTED = '<REDACTED>');
EOF

query T multiline
SELECT pretty_sql(redacted_create_sql) FROM mz_tables WHERE name = 'redactable_t'
----
CREATE TABLE materialize.public.redactable_t (a [s20 AS pg_catalog.int4]) WITH (RETAIN HISTORY = FOR '2s', REDACTED = '<REDACTED>');
EOF

query T multiline
SELECT pretty_sql(create_sql) FROM (SHOW REDACTED CREATE TABLE redactable_t)
----
CREATE TABLE materialize.public.redactable_t (a pg_catalog.int4) WITH (RETAIN HISTORY = FOR '2s', REDACTED = '<REDACTED>');
EOF

statement ok
CREATE INDEX t_idx_i ON t (i)

query T multiline
SELECT redacted_create_sql FROM mz_indexes WHERE name = 't_idx_i'
----
CREATE INDEX t_idx_i IN CLUSTER [u1] ON [u1 AS materialize.public.t] (i)
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE INDEX t_idx_i);
----
CREATE INDEX t_idx_i IN CLUSTER quickstart ON materialize.public.t (i);
EOF

statement ok
CREATE VIEW v AS SELECT 1

query T multiline
SELECT redacted_create_sql FROM mz_views WHERE name = 'v'
----
CREATE VIEW materialize.public.v AS SELECT '<REDACTED>'
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE VIEW v);
----
CREATE VIEW materialize.public.v AS SELECT '<REDACTED>';
EOF

query T multiline
SELECT pretty_sql(redacted_create_sql) FROM mz_views WHERE name = 'v'
----
CREATE VIEW materialize.public.v AS SELECT '<REDACTED>';
EOF

query T multiline
SELECT pretty_sql(create_sql) FROM (SHOW REDACTED CREATE VIEW v);
----
CREATE VIEW materialize.public.v AS SELECT '<REDACTED>';
EOF

statement ok
CREATE SOURCE s FROM LOAD GENERATOR COUNTER

# regexp_replace because local and testdrive produce different ids for an undiagnosed reason.
query T multiline
SELECT regexp_replace(redacted_create_sql, 'u[0-9]+', 'uX', 'g') FROM mz_sources WHERE name = 's'
----
CREATE SOURCE materialize.public.s IN CLUSTER [uX] FROM LOAD GENERATOR COUNTER EXPOSE PROGRESS AS [uX AS materialize.public.s_progress]
EOF

query T multiline
SELECT regexp_replace(create_sql, 'u[0-9]+', 'uX', 'g') FROM (SHOW REDACTED CREATE SOURCE s);
----
CREATE SOURCE materialize.public.s
IN CLUSTER quickstart
FROM LOAD GENERATOR COUNTER
EXPOSE PROGRESS AS materialize.public.s_progress;
EOF

query T multiline
SELECT regexp_replace(pretty_sql(redacted_create_sql), 'u[0-9]+', 'uX', 'g') FROM mz_sources WHERE name = 's'
----
CREATE SOURCE materialize.public.s
IN CLUSTER [uX]
FROM LOAD GENERATOR COUNTER
EXPOSE PROGRESS AS [uX AS materialize.public.s_progress];
EOF

query T multiline
SELECT regexp_replace(pretty_sql(create_sql), 'u[0-9]+', 'uX', 'g') FROM (SHOW REDACTED CREATE SOURCE s);
----
CREATE SOURCE materialize.public.s
IN CLUSTER quickstart
FROM LOAD GENERATOR COUNTER
EXPOSE PROGRESS AS materialize.public.s_progress;
EOF

statement ok
CREATE TYPE ty AS LIST (ELEMENT TYPE=bool)

query T multiline
SELECT redacted_create_sql FROM mz_types WHERE name = 'ty'
----
CREATE TYPE materialize.public.ty AS LIST (ELEMENT TYPE = [s6 AS pg_catalog.bool])
EOF

statement ok
CREATE MATERIALIZED VIEW mv1 AS
SELECT i+i+5 FROM t;

query T multiline
SELECT regexp_replace(redacted_create_sql, 'AS OF [0-9]+', 'AS OF xxxxxxx', 'g') FROM mz_materialized_views WHERE name = 'mv1'
----
CREATE MATERIALIZED VIEW materialize.public.mv1 IN CLUSTER [u1] WITH (REFRESH = ON COMMIT) AS SELECT i + i + '<REDACTED>' FROM [u1 AS materialize.public.t] AS OF xxxxxxx
EOF

query T multiline
SELECT create_sql FROM (SHOW REDACTED CREATE MATERIALIZED VIEW mv1);
----
CREATE MATERIALIZED VIEW materialize.public.mv1
    IN CLUSTER quickstart
    WITH (REFRESH = ON COMMIT)
    AS SELECT i + i + '<REDACTED>' FROM materialize.public.t;
EOF

query T multiline
SELECT pretty_sql(redacted_create_sql) FROM mz_materialized_views WHERE name = 'mv1'
----
CREATE MATERIALIZED VIEW materialize.public.mv1
    IN CLUSTER [u1]
    WITH (REFRESH = ON COMMIT)
    AS SELECT i + i + '<REDACTED>' FROM [u1 AS materialize.public.t];
EOF

query T multiline
SELECT pretty_sql(create_sql) FROM (SHOW REDACTED CREATE MATERIALIZED VIEW mv1);
----
CREATE MATERIALIZED VIEW materialize.public.mv1
    IN CLUSTER quickstart
    WITH (REFRESH = ON COMMIT)
    AS SELECT i + i + '<REDACTED>' FROM materialize.public.t;
EOF

query error db error: ERROR: unknown catalog item 'aaaaaaa'
SHOW REDACTED CREATE MATERIALIZED VIEW aaaaaaa;

query error db error: ERROR: materialize\.public\.v is not a materialized view
SHOW REDACTED CREATE MATERIALIZED VIEW v;

query error db error: ERROR: materialize\.public\.mv1 is not a view
SHOW REDACTED CREATE VIEW mv1;

query error Expected end of statement, found TYPE
SHOW CREATE TYPE ty;

query error Expected end of statement, found TYPE
SHOW REDACTED CREATE TYPE ty;