Materialize/test/sqllogictest/cockroach/drop_database.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/drop_database
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

# not supported yet
halt

mode cockroach

statement ok
CREATE DATABASE "foo-bar"

query T
SHOW DATABASES
----
materialize
foo-bar
postgres
system
test

statement ok
CREATE TABLE "foo-bar".t(x INT)

statement error database.*is not empty and RESTRICT was specified
DROP DATABASE "foo-bar" RESTRICT

statement ok
DROP DATABASE "foo-bar" CASCADE

query TTT
SELECT name, database_name, state FROM crdb_internal.tables WHERE name = 't'
----
t  [53]  DROP

query T
SHOW DATABASES
----
materialize
postgres
system
test

query TT
SELECT status, running_status FROM [SHOW JOBS]
----
running  waiting for GC TTL

statement ok
CREATE DATABASE "foo bar"

query T
SHOW DATABASES
----
materialize
foo bar
postgres
system
test

statement ok
DROP DATABASE "foo bar" CASCADE

query T
SHOW DATABASES
----
materialize
postgres
system
test

statement ok
CREATE DATABASE d1

statement ok
CREATE DATABASE d2

statement ok
CREATE TABLE d1.t1 (k STRING PRIMARY KEY, v STRING)

statement OK
CREATE TABLE d2.t1 (k STRING PRIMARY KEY, v STRING)

statement ok
CREATE VIEW d1.v1 AS SELECT k,v FROM d1.t1

statement ok
CREATE VIEW d1.v2 AS SELECT k,v FROM d1.v1

statement ok
CREATE VIEW d2.v1 AS SELECT k,v FROM d2.t1

statement ok
CREATE VIEW d2.v2 AS SELECT k,v FROM d1.t1

statement ok
CREATE VIEW d2.v3 AS SELECT k,v FROM d1.v2

statement ok
CREATE VIEW d2.v4 AS SELECT count(*) FROM d1.t1 as x JOIN d2.t1 as y ON x.k = y.k

statement ok
GRANT ALL ON DATABASE d1 TO testuser

statement ok
GRANT ALL ON d1.t1 TO testuser

statement ok
GRANT ALL ON d1.v1 TO testuser

statement ok
GRANT ALL ON d1.v2 TO testuser

statement ok
GRANT ALL ON d2.v2 TO testuser

statement ok
GRANT ALL ON d2.v3 TO testuser

user testuser

statement error user testuser does not have DROP privilege on relation v4
DROP DATABASE d1 CASCADE

user root

query TT
SELECT * FROM d1.v2
----

query TT
SELECT * FROM d2.v1
----

query TT
SELECT * FROM d2.v2
----

query TT
SELECT * FROM d2.v3
----

query I
SELECT * FROM d2.v4
----
0

query T
SHOW DATABASES
----
d1
d2
materialize
postgres
system
test

statement ok
DROP DATABASE d1 CASCADE

query T
SHOW DATABASES
----
d2
materialize
postgres
system
test

query error pgcode 42P01 relation "d1.v2" does not exist
SELECT * FROM d1.v2

query error pgcode 42P01 relation "d2.v2" does not exist
SELECT * FROM d2.v2

query error pgcode 42P01 relation "d2.v3" does not exist
SELECT * FROM d2.v3

query error pgcode 42P01 relation "d2.v4" does not exist
SELECT * FROM d2.v4

query TT
SELECT * FROM d2.v1
----

statement ok
DROP DATABASE d2 CASCADE

query T
SHOW DATABASES
----
materialize
postgres
system
test

query error pgcode 42P01 relation "d2.v1" does not exist
SELECT * FROM d2.v1

## drop a database containing tables with foreign key constraints, e.g. materialize#8497

statement ok
CREATE DATABASE constraint_db

statement ok
CREATE TABLE constraint_db.t1 (
  p FLOAT PRIMARY KEY,
  a INT UNIQUE CHECK (a > 4),
  CONSTRAINT c2 CHECK (a < 99)
)

statement ok
CREATE TABLE constraint_db.t2 (
    t1_ID INT,
    CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a),
    INDEX (t1_ID)
)

statement ok
DROP DATABASE constraint_db CASCADE

query T
SHOW DATABASES
----
materialize
postgres
system
test

query error pgcode 42P01 relation "constraint_db.t1" does not exist
SELECT * FROM constraint_db.t1

# Check that the default option is CASCADE, but that safe_updates blocks it

statement ok
CREATE DATABASE foo; CREATE TABLE foo.bar(x INT);

statement ok
SET sql_safe_updates = TRUE;

statement error DROP DATABASE on current database
DROP DATABASE test

statement error DROP DATABASE on non-empty database without explicit CASCADE
DROP DATABASE foo

statement ok
SET sql_safe_updates = FALSE; DROP DATABASE foo

# Check that the default databases can be dropped and re-created like any other.
statement OK
DROP DATABASE materialize; DROP DATABASE postgres

statement ok
CREATE DATABASE materialize; CREATE DATABASE postgres