github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/drop_database (about)

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE DATABASE "foo-bar"
     5  
     6  query T
     7  SHOW DATABASES
     8  ----
     9  defaultdb
    10  foo-bar
    11  postgres
    12  system
    13  test
    14  
    15  statement ok
    16  CREATE TABLE "foo-bar".t(x INT)
    17  
    18  statement error database.*is not empty and RESTRICT was specified
    19  DROP DATABASE "foo-bar" RESTRICT
    20  
    21  statement ok
    22  DROP DATABASE "foo-bar" CASCADE
    23  
    24  query TTT
    25  SELECT name, database_name, state FROM crdb_internal.tables WHERE name = 't'
    26  ----
    27  t  [53]  DROP
    28  
    29  query T
    30  SHOW DATABASES
    31  ----
    32  defaultdb
    33  postgres
    34  system
    35  test
    36  
    37  query TT
    38  SELECT job_type, status FROM [SHOW JOBS]
    39  ----
    40  SCHEMA CHANGE       succeeded
    41  SCHEMA CHANGE GC    running
    42  
    43  statement ok
    44  CREATE DATABASE "foo bar"
    45  
    46  query T
    47  SHOW DATABASES
    48  ----
    49  defaultdb
    50  foo bar
    51  postgres
    52  system
    53  test
    54  
    55  statement ok
    56  DROP DATABASE "foo bar" CASCADE
    57  
    58  query T
    59  SHOW DATABASES
    60  ----
    61  defaultdb
    62  postgres
    63  system
    64  test
    65  
    66  statement ok
    67  CREATE DATABASE d1
    68  
    69  statement ok
    70  CREATE DATABASE d2
    71  
    72  statement ok
    73  CREATE TABLE d1.t1 (k STRING PRIMARY KEY, v STRING)
    74  
    75  statement OK
    76  CREATE TABLE d2.t1 (k STRING PRIMARY KEY, v STRING)
    77  
    78  statement ok
    79  CREATE VIEW d1.v1 AS SELECT k,v FROM d1.t1
    80  
    81  statement ok
    82  CREATE VIEW d1.v2 AS SELECT k,v FROM d1.v1
    83  
    84  statement ok
    85  CREATE VIEW d2.v1 AS SELECT k,v FROM d2.t1
    86  
    87  statement ok
    88  CREATE VIEW d2.v2 AS SELECT k,v FROM d1.t1
    89  
    90  statement ok
    91  CREATE VIEW d2.v3 AS SELECT k,v FROM d1.v2
    92  
    93  statement ok
    94  CREATE VIEW d2.v4 AS SELECT count(*) FROM d1.t1 as x JOIN d2.t1 as y ON x.k = y.k
    95  
    96  statement ok
    97  GRANT ALL ON DATABASE d1 TO testuser
    98  
    99  statement ok
   100  GRANT ALL ON d1.t1 TO testuser
   101  
   102  statement ok
   103  GRANT ALL ON d1.v1 TO testuser
   104  
   105  statement ok
   106  GRANT ALL ON d1.v2 TO testuser
   107  
   108  statement ok
   109  GRANT ALL ON d2.v2 TO testuser
   110  
   111  statement ok
   112  GRANT ALL ON d2.v3 TO testuser
   113  
   114  user testuser
   115  
   116  statement error user testuser does not have DROP privilege on relation v4
   117  DROP DATABASE d1 CASCADE
   118  
   119  user root
   120  
   121  query TT
   122  SELECT * FROM d1.v2
   123  ----
   124  
   125  query TT
   126  SELECT * FROM d2.v1
   127  ----
   128  
   129  query TT
   130  SELECT * FROM d2.v2
   131  ----
   132  
   133  query TT
   134  SELECT * FROM d2.v3
   135  ----
   136  
   137  query I
   138  SELECT * FROM d2.v4
   139  ----
   140  0
   141  
   142  query T
   143  SHOW DATABASES
   144  ----
   145  d1
   146  d2
   147  defaultdb
   148  postgres
   149  system
   150  test
   151  
   152  statement ok
   153  DROP DATABASE d1 CASCADE
   154  
   155  query T
   156  SHOW DATABASES
   157  ----
   158  d2
   159  defaultdb
   160  postgres
   161  system
   162  test
   163  
   164  query error pgcode 42P01 relation "d1.v2" does not exist
   165  SELECT * FROM d1.v2
   166  
   167  query error pgcode 42P01 relation "d2.v2" does not exist
   168  SELECT * FROM d2.v2
   169  
   170  query error pgcode 42P01 relation "d2.v3" does not exist
   171  SELECT * FROM d2.v3
   172  
   173  query error pgcode 42P01 relation "d2.v4" does not exist
   174  SELECT * FROM d2.v4
   175  
   176  query TT
   177  SELECT * FROM d2.v1
   178  ----
   179  
   180  statement ok
   181  DROP DATABASE d2 CASCADE
   182  
   183  query T
   184  SHOW DATABASES
   185  ----
   186  defaultdb
   187  postgres
   188  system
   189  test
   190  
   191  query error pgcode 42P01 relation "d2.v1" does not exist
   192  SELECT * FROM d2.v1
   193  
   194  ## drop a database containing tables with foreign key constraints, e.g. #8497
   195  
   196  statement ok
   197  CREATE DATABASE constraint_db
   198  
   199  statement ok
   200  CREATE TABLE constraint_db.t1 (
   201    p FLOAT PRIMARY KEY,
   202    a INT UNIQUE CHECK (a > 4),
   203    CONSTRAINT c2 CHECK (a < 99)
   204  )
   205  
   206  statement ok
   207  CREATE TABLE constraint_db.t2 (
   208      t1_ID INT,
   209      CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a),
   210      INDEX (t1_ID)
   211  )
   212  
   213  statement ok
   214  DROP DATABASE constraint_db CASCADE
   215  
   216  query T
   217  SHOW DATABASES
   218  ----
   219  defaultdb
   220  postgres
   221  system
   222  test
   223  
   224  query error pgcode 42P01 relation "constraint_db.t1" does not exist
   225  SELECT * FROM constraint_db.t1
   226  
   227  # Check that the default option is CASCADE, but that safe_updates blocks it
   228  
   229  statement ok
   230  CREATE DATABASE foo; CREATE TABLE foo.bar(x INT);
   231  
   232  statement ok
   233  SET sql_safe_updates = TRUE;
   234  
   235  statement error DROP DATABASE on current database
   236  DROP DATABASE test
   237  
   238  statement error DROP DATABASE on non-empty database without explicit CASCADE
   239  DROP DATABASE foo
   240  
   241  statement ok
   242  SET sql_safe_updates = FALSE; DROP DATABASE foo
   243  
   244  # Check that the default databases can be dropped and re-created like any other.
   245  statement OK
   246  DROP DATABASE defaultdb; DROP DATABASE postgres
   247  
   248  statement ok
   249  CREATE DATABASE defaultdb; CREATE DATABASE postgres
   250  
   251  # Test that an empty database doesn't get a GC job.
   252  statement ok
   253  CREATE DATABASE empty
   254  
   255  statement ok
   256  DROP DATABASE empty
   257  
   258  query TT
   259  SELECT job_type, status FROM [SHOW JOBS] WHERE description LIKE '%empty%'
   260  ----
   261  SCHEMA CHANGE       succeeded