drop_database

# LogicTest: default

statement ok
CREATE DATABASE "foo-bar"

query T
SHOW DATABASES
----
default
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
----
default
postgres
system
test

statement ok
CREATE DATABASE "foo bar"

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

statement ok
DROP DATABASE "foo bar" CASCADE

query T
SHOW DATABASES
----
default
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
default
postgres
system
test

statement ok
DROP DATABASE d1 CASCADE

query T
SHOW DATABASES
----
d2
default
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
----
default
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. #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
----
default
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