1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test RENAME TABLE single transaction", "[alter]") {
8 DuckDB db(nullptr);
9 Connection con(db);
10 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl(i INTEGER)"));
11 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl VALUES (999), (100)"));
12
13 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
14 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl RENAME TO tbl2"));
15 REQUIRE_NO_FAIL(con.Query("SELECT * FROM tbl2"));
16 REQUIRE_FAIL(con.Query("SELECT * FROM tbl"));
17 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
18 REQUIRE_NO_FAIL(con.Query("SELECT * FROM tbl;"));
19 REQUIRE_FAIL(con.Query("SELECT * FROM tbl2"));
20 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION;"));
21 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl RENAME TO tbl2"));
22 REQUIRE_NO_FAIL(con.Query("COMMIT"));
23 REQUIRE_NO_FAIL(con.Query("SELECT * FROM tbl2"));
24 REQUIRE_FAIL(con.Query("SELECT * FROM tbl"));
25
26 auto result = con.Query("SELECT * FROM tbl2");
27 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
28}
29
30TEST_CASE("Test RENAME TABLE two parallel transactions", "[alter]") {
31 DuckDB db(nullptr);
32 Connection con1(db);
33 Connection con2(db);
34 REQUIRE_NO_FAIL(con1.Query("CREATE TABLE tbl(i INTEGER)"));
35 REQUIRE_NO_FAIL(con1.Query("INSERT INTO tbl VALUES (999), (100)"));
36
37 REQUIRE_NO_FAIL(con1.Query("BEGIN TRANSACTION"));
38 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
39 REQUIRE_NO_FAIL(con1.Query("ALTER TABLE tbl RENAME TO tbl2"));
40
41 REQUIRE_NO_FAIL(con1.Query("SELECT * FROM tbl2"));
42 REQUIRE_FAIL(con1.Query("SELECT * FROM tbl"));
43
44 REQUIRE_NO_FAIL(con2.Query("SELECT * FROM tbl"));
45 REQUIRE_FAIL(con2.Query("SELECT * FROM tbl2"));
46
47 REQUIRE_NO_FAIL(con1.Query("COMMIT"));
48 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
49
50 REQUIRE_FAIL(con1.Query("SELECT * FROM tbl"));
51 REQUIRE_FAIL(con2.Query("SELECT * FROM tbl"));
52
53 auto result = con1.Query("SELECT * FROM tbl2");
54 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
55
56 result = con2.Query("SELECT * FROM tbl2");
57 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
58}
59
60TEST_CASE("Test RENAME TABLE four table rename and four parallel transactions", "[alter]") {
61 DuckDB db(nullptr);
62 Connection con(db);
63 Connection c1(db);
64 Connection c2(db);
65 Connection c3(db);
66
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl1(i INTEGER)"));
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl1 VALUES (999), (100)"));
69
70 // rename chain
71 // c1 starts a transaction now
72 REQUIRE_NO_FAIL(c1.Query("BEGIN TRANSACTION"));
73 // rename in con, c1 should still see "tbl1"
74 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl1 RENAME TO tbl2"));
75
76 // c2 starts a transaction now
77 REQUIRE_NO_FAIL(c2.Query("BEGIN TRANSACTION"));
78 // rename in con, c2 should still see "tbl2"
79 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl2 RENAME TO tbl3"));
80
81 // c3 starts a transaction now
82 REQUIRE_NO_FAIL(c3.Query("BEGIN TRANSACTION"));
83 // rename in con, c3 should still see "tbl3"
84 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl3 RENAME TO tbl4"));
85
86 // c1 sees ONLY tbl1
87 REQUIRE_NO_FAIL(c1.Query("SELECT * FROM tbl1"));
88 REQUIRE_FAIL(c1.Query("SELECT * FROM tbl2"));
89 REQUIRE_FAIL(c1.Query("SELECT * FROM tbl3"));
90 REQUIRE_FAIL(c1.Query("SELECT * FROM tbl4"));
91 auto result = c1.Query("SELECT * FROM tbl1");
92 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
93
94 // c2 sees ONLY tbl2
95 REQUIRE_FAIL(c2.Query("SELECT * FROM tbl1"));
96 REQUIRE_NO_FAIL(c2.Query("SELECT * FROM tbl2"));
97 REQUIRE_FAIL(c2.Query("SELECT * FROM tbl3"));
98 REQUIRE_FAIL(c2.Query("SELECT * FROM tbl4"));
99 result = c2.Query("SELECT * FROM tbl2");
100 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
101
102 // c3 sees ONLY tbl3
103 REQUIRE_FAIL(c3.Query("SELECT * FROM tbl1"));
104 REQUIRE_FAIL(c3.Query("SELECT * FROM tbl2"));
105 REQUIRE_NO_FAIL(c3.Query("SELECT * FROM tbl3"));
106 REQUIRE_FAIL(c3.Query("SELECT * FROM tbl4"));
107 result = c3.Query("SELECT * FROM tbl3");
108 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
109
110 // con sees ONLY tbl4
111 REQUIRE_FAIL(con.Query("SELECT * FROM tbl1"));
112 REQUIRE_FAIL(con.Query("SELECT * FROM tbl2"));
113 REQUIRE_FAIL(con.Query("SELECT * FROM tbl3"));
114 REQUIRE_NO_FAIL(con.Query("SELECT * FROM tbl4"));
115 result = con.Query("SELECT * FROM tbl4");
116 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
117}
118
119TEST_CASE("Test RENAME TABLE with a view as entry", "[alter]") {
120 DuckDB db(nullptr);
121 Connection con(db);
122 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl(i INTEGER)"));
123 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl VALUES (999), (100)"));
124
125 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT * FROM tbl"));
126 REQUIRE_FAIL(con.Query("ALTER TABLE v1 RENAME TO v2"));
127 auto result = con.Query("SELECT * FROM v1");
128 REQUIRE(CHECK_COLUMN(result, 0, {999, 100}));
129}
130
131TEST_CASE("Test RENAME TABLE: table does not exist and rename to an already existing table", "[alter]") {
132 DuckDB db(nullptr);
133 Connection con(db);
134 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl(i INTEGER)"));
135 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl2(i INTEGER)"));
136 // Renaming a non existing table
137 REQUIRE_FAIL(con.Query("ALTER TABLE non_table RENAME TO tbl"));
138 // rename to an already existing table
139 REQUIRE_FAIL(con.Query("ALTER TABLE tbl2 RENAME TO tbl"));
140}
141
142TEST_CASE("Test RENAME TABLE with constraints", "[alter]") {
143 DuckDB db(nullptr);
144 Connection con(db);
145 // create a table with a check constraint
146 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tbl(i INTEGER PRIMARY KEY, j INTEGER CHECK(j < 10))"));
147
148 // check primary key constrain
149 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl VALUES (999, 4), (1000, 5)"));
150 REQUIRE_FAIL(con.Query("INSERT INTO tbl VALUES (999, 4), (1000, 5)"));
151
152 // check value constrain (j < 10)
153 REQUIRE_NO_FAIL(con.Query("INSERT INTO tbl VALUES (9999, 0), (10000, 1)"));
154 REQUIRE_FAIL(con.Query("INSERT INTO tbl VALUES (777, 10), (888, 10)"));
155
156 auto result = con.Query("SELECT * FROM tbl");
157 REQUIRE(CHECK_COLUMN(result, 0, {999, 1000, 9999, 10000}));
158 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 0, 1}));
159
160 REQUIRE_NO_FAIL(con.Query("ALTER TABLE tbl RENAME TO new_tbl"));
161 // insert two conflicting pairs at the same time
162 REQUIRE_FAIL(con.Query("INSERT INTO new_tbl VALUES (999, 0), (1000, 1)"));
163 // insert two conflicting pairs at the same time
164 REQUIRE_FAIL(con.Query("INSERT INTO new_tbl VALUES (9999, 0), (10000, 1)"));
165
166 // insert values out of range constrain
167 REQUIRE_FAIL(con.Query("INSERT INTO new_tbl VALUES (1, 10), (2, 999)"));
168 REQUIRE_NO_FAIL(con.Query("INSERT INTO new_tbl VALUES (66, 6), (55, 5)"));
169}
170