# 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. mode standard # Start from a pristine server reset-server statement ok CREATE SCHEMA a1 statement ok CREATE SCHEMA b1 statement error db error: ERROR: schema 'b1' already exists CREATE SCHEMA B1 query TT SELECT database_id, name FROM mz_schemas WHERE id LIKE 'u%'; ---- u1 a1 u1 b1 u1 public statement ok CREATE TABLE b1.t (x int) query TT SHOW CREATE TABLE b1.t ---- materialize.b1.t CREATE TABLE materialize.b1.t (x pg_catalog.int4); statement ok INSERT INTO b1.t VALUES (1), (2), (3) query I valuesort SELECT x FROM b1.t ---- 1 2 3 statement error db error: ERROR: schema 'a1' already exists ALTER SCHEMA b1 RENAME TO a1 statement error db error: ERROR: unacceptable schema name 'mz_special' ALTER SCHEMA b1 RENAME TO mz_special statement ok ALTER SCHEMA b1 RENAME TO b2 query TTT SELECT event_type, object_type, details FROM mz_audit_events WHERE event_type = 'alter' AND object_type = 'schema'; ---- alter schema {"database_name":"materialize","id":"u10","new_name":"b2","old_name":"b1"} query I valuesort SELECT x FROM b2.t ---- 1 2 3 mode cockroach query TT rowsort SELECT database_id, name FROM mz_schemas WHERE id LIKE 'u%'; ---- u1 a1 u1 b2 u1 public mode standard query TT SHOW CREATE TABLE b2.t; ---- materialize.b2.t CREATE TABLE materialize.b2.t (x pg_catalog.int4); statement ok CREATE SCHEMA friend; statement ok CREATE VIEW friend.v1 AS SELECT x FROM b2.t; query I valuesort SELECT * FROM friend.v1; ---- 1 2 3 query TT SHOW CREATE VIEW friend.v1; ---- materialize.friend.v1 CREATE VIEW materialize.friend.v1 AS SELECT x FROM materialize.b2.t; statement ok ALTER SCHEMA b2 RENAME TO b3; query I valuesort SELECT * FROM friend.v1; ---- 1 2 3 query TT SHOW CREATE VIEW friend.v1; ---- materialize.friend.v1 CREATE VIEW materialize.friend.v1 AS SELECT x FROM materialize.b3.t; statement ok CREATE SCHEMA grand_friend; statement ok CREATE MATERIALIZED VIEW grand_friend.mv1 AS SELECT x FROM friend.v1; query I valuesort SELECT x FROM grand_friend.mv1; ---- 1 2 3 statement ok ALTER SCHEMA friend RENAME TO enemy; statement error db error: ERROR: unknown schema 'friend' SELECT * FROM friend.v1; query TT SHOW CREATE TABLE b3.t; ---- materialize.b3.t CREATE TABLE materialize.b3.t (x pg_catalog.int4); query TT SHOW CREATE VIEW enemy.v1; ---- materialize.enemy.v1 CREATE VIEW materialize.enemy.v1 AS SELECT x FROM materialize.b3.t; query TT SHOW CREATE MATERIALIZED VIEW grand_friend.mv1; ---- materialize.grand_friend.mv1 CREATE MATERIALIZED VIEW materialize.grand_friend.mv1⏎ IN CLUSTER quickstart⏎ WITH (REFRESH = ON COMMIT)⏎ AS SELECT x FROM materialize.enemy.v1; statement ok CREATE TABLE a1.t (y text); statement ok INSERT INTO a1.t VALUES ('foo'), ('bar'); query TT SHOW CREATE TABLE a1.t; ---- materialize.a1.t CREATE TABLE materialize.a1.t (y pg_catalog.text); statement ok CREATE VIEW enemy.v2 AS (SELECT * FROM a1.t, b3.t); query TI SELECT * FROM enemy.v2; ---- bar 1 bar 2 bar 3 foo 1 foo 2 foo 3 query TT SHOW CREATE VIEW enemy.v2; ---- materialize.enemy.v2 CREATE VIEW materialize.enemy.v2 AS SELECT * FROM materialize.a1.t, materialize.b3.t; statement ok CREATE INDEX enemy_v2_idx ON enemy.v2 (y, x); query T SELECT name FROM mz_indexes WHERE name LIKE 'enemy%'; ---- enemy_v2_idx query TT SHOW CREATE INDEX enemy.enemy_v2_idx ---- materialize.enemy.enemy_v2_idx CREATE INDEX enemy_v2_idx IN CLUSTER quickstart ON materialize.enemy.v2 (y, x); statement ok ALTER SCHEMA b3 RENAME TO b4; query TT SHOW CREATE VIEW enemy.v2; ---- materialize.enemy.v2 CREATE VIEW materialize.enemy.v2 AS SELECT * FROM materialize.a1.t, materialize.b4.t; statement ok ALTER SCHEMA enemy RENAME TO friend_again; query TT SHOW CREATE VIEW friend_again.v2; ---- materialize.friend_again.v2 CREATE VIEW materialize.friend_again.v2 AS SELECT * FROM materialize.a1.t, materialize.b4.t; query TT SHOW CREATE INDEX friend_again.enemy_v2_idx ---- materialize.friend_again.enemy_v2_idx CREATE INDEX enemy_v2_idx IN CLUSTER quickstart ON materialize.friend_again.v2 (y, x); statement ok CREATE TABLE grand_friend.t1 (keys text); statement ok INSERT INTO grand_friend.t1 VALUES ('foo'), ('baz'); statement ok CREATE SCHEMA c1; query TT valuesort SELECT database_id, name FROM mz_schemas WHERE id LIKE 'u%'; ---- u1 a1 u1 b4 u1 c1 u1 public u1 friend_again u1 grand_friend statement ok CREATE VIEW c1.keys (aux_key) AS VALUES ('apple'), ('orange'), ('banana'); statement ok CREATE VIEW c1.v1 AS (SELECT y FROM friend_again.v2 JOIN grand_friend.t1 ON materialize.friend_again.v2.y = materialize.grand_friend.t1.keys UNION ALL SELECT aux_key FROM c1.keys, b4.t); query T valuesort SELECT * FROM c1.v1; ---- foo foo foo apple apple apple banana banana banana orange orange orange query TT SHOW CREATE VIEW c1.v1; ---- materialize.c1.v1 CREATE VIEW⏎ materialize.c1.v1⏎ AS⏎ SELECT y⏎ FROM⏎ materialize.friend_again.v2⏎ JOIN⏎ materialize.grand_friend.t1⏎ ON materialize.friend_again.v2.y = materialize.grand_friend.t1.keys⏎ UNION ALL SELECT aux_key FROM materialize.c1.keys, materialize.b4.t; statement ok ALTER SCHEMA c1 RENAME TO c2; statement ok ALTER SCHEMA grand_friend RENAME TO grand_acquaintance; query TT SHOW CREATE VIEW c2.v1; ---- materialize.c2.v1 CREATE VIEW⏎ materialize.c2.v1⏎ AS⏎ SELECT y⏎ FROM⏎ materialize.friend_again.v2⏎ JOIN⏎ materialize.grand_acquaintance.t1⏎ ON materialize.friend_again.v2.y = materialize.grand_acquaintance.t1.keys⏎ UNION ALL SELECT aux_key FROM materialize.c2.keys, materialize.b4.t; statement ok CREATE TABLE c2.c2 (ts int); query TT SHOW CREATE TABLE c2.c2; ---- materialize.c2.c2 CREATE TABLE materialize.c2.c2 (ts pg_catalog.int4); statement ok ALTER SCHEMA c2 RENAME TO c3; query TT SHOW CREATE TABLE c3.c2; ---- materialize.c3.c2 CREATE TABLE materialize.c3.c2 (ts pg_catalog.int4); # Renaming system owned schemas is not allowed. statement error db error: ERROR: must be owner of SCHEMA materialize.public ALTER SCHEMA public RENAME TO public_other; statement error db error: ERROR: renaming the mz_catalog schema is not supported ALTER SCHEMA mz_catalog RENAME TO mz_catalog_other; statement error db error: ERROR: renaming the mz_internal schema is not supported ALTER SCHEMA mz_internal RENAME TO mz_internal_other; statement error db error: ERROR: renaming the information_schema schema is not supported ALTER SCHEMA information_schema RENAME TO information_schema_other; # Fully qualified columns names are renamed. statement ok CREATE SCHEMA d; statement ok CREATE VIEW d.values (x, y, z) AS VALUES (1, 'foo', 100), (2, 'bar', 200), (3, 'baz', 300); statement ok CREATE VIEW d.qualified_columns AS ( SELECT materialize.d.values.x, materialize.d.values.y, z FROM d.values ); query TT SHOW CREATE VIEW d.qualified_columns; ---- materialize.d.qualified_columns CREATE VIEW⏎ materialize.d.qualified_columns⏎ AS SELECT materialize.d.values.x, materialize.d.values.y, z FROM materialize.d.values; mode cockroach query ITI rowsort SELECT * FROM d.qualified_columns; ---- 1 foo 100 2 bar 200 3 baz 300 mode standard statement ok ALTER SCHEMA d RENAME TO d_renamed; query TT SHOW CREATE VIEW d_renamed.qualified_columns; ---- materialize.d_renamed.qualified_columns CREATE VIEW⏎ materialize.d_renamed.qualified_columns⏎ AS⏎ SELECT materialize.d_renamed.values.x, materialize.d_renamed.values.y, z⏎ FROM materialize.d_renamed.values; query ITI SELECT * FROM d_renamed.qualified_columns LIMIT 1; ---- 1 foo 100 # Renaming and capitalization. statement ok CREATE SCHEMA "case"; statement ok CREATE VIEW "case".case AS VALUES (1); statement ok CREATE VIEW d_renamed.case (case) AS ( SELECT * FROM "case".case ); query TT SHOW CREATE VIEW d_renamed."case"; ---- materialize.d_renamed.case CREATE VIEW materialize.d_renamed.case (case) AS SELECT * FROM materialize.case.case; statement ok CREATE SCHEMA "CASE"; statement ok CREATE VIEW "CASE".case AS VALUES (3); statement ok ALTER SCHEMA "case" RENAME TO "cAsE"; query I SELECT * FROM "CASE".case; ---- 3 query I SELECT * FROM "cAsE".case; ---- 1 query TT SHOW CREATE VIEW "cAsE".case; ---- materialize.cAsE.case CREATE VIEW materialize."cAsE".case AS VALUES (1); query TT SHOW CREATE VIEW d_renamed.case; ---- materialize.d_renamed.case CREATE VIEW materialize.d_renamed.case (case) AS SELECT * FROM materialize."cAsE".case; statement ok CREATE SCHEMA j; statement ok CREATE VIEW j.l AS VALUES (202); statement ok CREATE DATABASE j; statement ok CREATE SCHEMA j.k; statement ok CREATE VIEW j.k.l AS VALUES (101); query TT SHOW CREATE VIEW j.k.l; ---- j.k.l CREATE VIEW j.k.l AS VALUES (101); query I SELECT * FROM j.k.l; ---- 101 statement ok ALTER SCHEMA j RENAME TO j_other; query TT SHOW CREATE VIEW j.k.l; ---- j.k.l CREATE VIEW j.k.l AS VALUES (101); query I SELECT * FROM j.k.l; ---- 101 query I SELECT * FROM j_other.l; ---- 202 # Temporary schemas. statement error db error: ERROR: cannot rename schemas in the ambient database: "mz_temp" ALTER SCHEMA mz_temp RENAME TO other_name; simple conn=mz_system,user=mz_system ALTER SCHEMA mz_temp RENAME TO other_name; ---- db error: ERROR: cannot rename schemas in the ambient database: "mz_temp" # Schemas that do not exist. statement error unknown schema 'does_not_exist' ALTER SCHEMA does_not_exist RENAME TO other_does_not_exist; # The mz_system user should be able to rename the public schema. statement ok CREATE TABLE t1 (bar int); statement ok INSERT INTO t1 VALUES (100), (200), (300); simple conn=mz_system,user=mz_system ALTER SCHEMA public RENAME TO public_renamed; ---- COMPLETE 0 # Search path is still set to "public" which no longer exists. # # Note: this follows Postgres behavior. statement error db error: ERROR: unknown catalog item 't1' SELECT bar FROM t1; query T SHOW search_path; ---- public statement ok SET SCHEMA TO public_renamed; query I SELECT bar FROM t1 ORDER BY bar ASC; ---- 100 200 300 # Renaming schemas across databases. statement ok CREATE DATABASE a; statement ok CREATE SCHEMA a.foo; statement ok CREATE TABLE a.foo.t1 (x int); statement ok INSERT INTO a.foo.t1 VALUES (4), (5), (6); statement ok CREATE DATABASE b; statement ok CREATE SCHEMA b.foo; statement ok CREATE TABLE b.foo.t1 (y int); statement ok INSERT INTO b.foo.t1 VALUES (1), (2), (3); statement ok CREATE DATABASE c; statement ok CREATE SCHEMA c.foo; statement ok CREATE VIEW c.foo.v1 AS ( SELECT x, y FROM a.foo.t1, b.foo.t1 ); query TT SHOW CREATE VIEW c.foo.v1; ---- c.foo.v1 CREATE VIEW c.foo.v1 AS SELECT x, y FROM a.foo.t1, b.foo.t1; statement ok ALTER SCHEMA b.foo RENAME TO bbb; query TT SHOW CREATE VIEW c.foo.v1; ---- c.foo.v1 CREATE VIEW c.foo.v1 AS SELECT x, y FROM a.foo.t1, b.bbb.t1; statement ok ALTER SCHEMA c.foo RENAME TO ccc; query TT SHOW CREATE VIEW c.ccc.v1; ---- c.ccc.v1 CREATE VIEW c.ccc.v1 AS SELECT x, y FROM a.foo.t1, b.bbb.t1; # Ambiguously refer to a schema. statement ok CREATE SCHEMA amb; statement ok CREATE TABLE amb.t1 (x int); statement ok CREATE DATABASE d; statement ok CREATE SCHEMA d.amb; statement ok CREATE TABLE d.amb.t1 (y int); statement ok CREATE VIEW d.amb.v1 AS SELECT amb.t1.x FROM amb.t1, d.amb.t1; query TT SHOW CREATE VIEW d.amb.v1; ---- d.amb.v1 CREATE VIEW d.amb.v1 AS SELECT amb.t1.x FROM materialize.amb.t1, d.amb.t1; statement error db error: ERROR: renaming conflict: in d\.amb\.v1, which uses d\.amb, ambiguous reference to schema named amb ALTER SCHEMA d.amb RENAME TO this_rename_will_fail; # Test subsource renames statement ok CREATE SOURCE s FROM LOAD GENERATOR AUCTION; statement ok CREATE TABLE accounts FROM SOURCE s (REFERENCE accounts); statement ok CREATE TABLE auctions FROM SOURCE s (REFERENCE auctions); statement ok CREATE TABLE bids FROM SOURCE s (REFERENCE bids); statement ok CREATE TABLE organizations FROM SOURCE s (REFERENCE organizations); statement ok CREATE TABLE users FROM SOURCE s (REFERENCE users); statement ok ALTER TABLE users RENAME TO userz statement ok SELECT * FROM userz LIMIT 0 statement ok CREATE TABLE non_temp_base(a INT); statement ok CREATE TEMPORARY VIEW temp_view AS SELECT * FROM non_temp_base; query TT SHOW CREATE VIEW temp_view; ---- mz_temp.temp_view CREATE TEMPORARY VIEW mz_temp.temp_view AS SELECT * FROM materialize.public_renamed.non_temp_base; statement ok ALTER TABLE non_temp_base RENAME TO non_temp_table; query TT SHOW CREATE VIEW temp_view; ---- mz_temp.temp_view CREATE TEMPORARY VIEW mz_temp.temp_view AS SELECT * FROM materialize.public_renamed.non_temp_table;