Materialize/test/sqllogictest/cockroach/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/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 a

statement error pgcode 42P04 database "a" already exists
CREATE DATABASE a

statement ok
CREATE DATABASE IF NOT EXISTS a

statement error empty database name
CREATE DATABASE ""

query T colnames
SHOW DATABASES
----
database_name
a
materialize
postgres
system
test

query T colnames
SHOW SCHEMAS FROM a
----
schema_name
crdb_internal
information_schema
pg_catalog
public

statement ok
CREATE DATABASE b TEMPLATE=template0

statement error unsupported template: nope
CREATE DATABASE c TEMPLATE=NOPE

statement error unsupported template: nope
CREATE DATABASE IF NOT EXISTS c TEMPLATE=NOPE

statement ok
CREATE DATABASE b2 ENCODING='UTF8'

statement error unsupported encoding: NOPE
CREATE DATABASE c ENCODING='NOPE'

statement error unsupported encoding: NOPE
CREATE DATABASE IF NOT EXISTS c ENCODING='NOPE'

statement ok
CREATE DATABASE b3 LC_COLLATE='C.UTF-8'

statement error unsupported collation: NOPE
CREATE DATABASE c LC_COLLATE='NOPE'

statement error unsupported collation: NOPE
CREATE DATABASE IF NOT EXISTS c LC_COLLATE='NOPE'

statement ok
CREATE DATABASE b4 LC_CTYPE='C.UTF-8'

statement error unsupported character classification: NOPE
CREATE DATABASE c LC_CTYPE='NOPE'

statement error unsupported character classification: NOPE
CREATE DATABASE IF NOT EXISTS c LC_CTYPE='NOPE'

statement ok
CREATE DATABASE b5 TEMPLATE=template0 ENCODING='UTF8' LC_COLLATE='C.UTF-8' LC_CTYPE='C.UTF-8'

statement ok
CREATE DATABASE b6 TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C.UTF-8' LC_CTYPE 'C.UTF-8'

statement ok
CREATE DATABASE c

query T
SHOW DATABASES
----
a
b
b2
b3
b4
b5
b6
c
materialize
postgres
system
test

statement ok
CREATE TABLE b.a (id INT PRIMARY KEY)

statement ok
INSERT INTO b.a VALUES (3),(7),(2)

query I rowsort
SELECT * FROM b.a
----
2
3
7

statement error database "b" is not empty
DROP DATABASE b RESTRICT

statement ok
DROP DATABASE b CASCADE

statement error pgcode 42P01 relation "b.a" does not exist
SELECT * FROM b.a

statement error database "b" does not exist
DROP DATABASE b

statement ok
DROP DATABASE IF EXISTS b

statement ok
DROP DATABASE b2 CASCADE;
  DROP DATABASE b3 CASCADE;
  DROP DATABASE b4 CASCADE;
  DROP DATABASE b5 CASCADE;
  DROP DATABASE b6 CASCADE

statement error empty database name
DROP DATABASE ""

query T colnames
SHOW DATABASES
----
database_name
a
c
materialize
postgres
system
test

statement ok
CREATE DATABASE b

statement error pgcode 42P01 relation "b.a" does not exist
SELECT * FROM b.a

statement ok
CREATE TABLE b.a (id INT PRIMARY KEY)

query I
SELECT * FROM b.a
----

user testuser

statement error only superusers are allowed to CREATE DATABASE
CREATE DATABASE privs

user root

statement ok
CREATE DATABASE privs

user testuser

statement error user testuser does not have DROP privilege on database privs
DROP DATABASE privs CASCADE

user root

statement ok
GRANT DROP ON DATABASE privs TO testuser

user testuser

statement ok
DROP DATABASE privs CASCADE