1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "test_helpers.hpp"
4
5using namespace duckdb;
6using namespace std;
7
8TEST_CASE("Test storage of alter table rename column", "[storage]") {
9 unique_ptr<QueryResult> result;
10 auto storage_database = TestCreatePath("storage_test");
11 auto config = GetTestConfig();
12
13 // make sure the database does not exist
14 DeleteDatabase(storage_database);
15 {
16 // create a database and insert values
17 DuckDB db(storage_database, config.get());
18 Connection con(db);
19 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
20 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
21 for (idx_t i = 0; i < 2; i++) {
22 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
23 result = con.Query("SELECT a FROM test ORDER BY a");
24 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
25
26 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test RENAME COLUMN a TO k"));
27
28 result = con.Query("SELECT k FROM test ORDER BY k");
29 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
30 REQUIRE_NO_FAIL(con.Query(i == 0 ? "ROLLBACK" : "COMMIT"));
31 }
32 }
33 // reload the database from disk
34 for (idx_t i = 0; i < 2; i++) {
35 DuckDB db(storage_database, config.get());
36 Connection con(db);
37 result = con.Query("SELECT k FROM test ORDER BY k");
38 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
39 REQUIRE_FAIL(con.Query("SELECT a FROM test"));
40 }
41 DeleteDatabase(storage_database);
42}
43
44TEST_CASE("Test storage of alter table add column", "[storage]") {
45 unique_ptr<QueryResult> result;
46 auto storage_database = TestCreatePath("storage_test");
47 auto config = GetTestConfig();
48
49 // make sure the database does not exist
50 DeleteDatabase(storage_database);
51 {
52 // create a database and insert values
53 DuckDB db(storage_database, config.get());
54 Connection con(db);
55 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
56 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
57 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER DEFAULT 2"));
58
59 result = con.Query("SELECT k FROM test ORDER BY k");
60 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 2}));
61 }
62 // reload the database from disk
63 for (idx_t i = 0; i < 2; i++) {
64 DuckDB db(storage_database, config.get());
65 Connection con(db);
66 result = con.Query("SELECT k FROM test ORDER BY k");
67 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 2}));
68 }
69 DeleteDatabase(storage_database);
70}
71
72TEST_CASE("Add column to persistent table", "[storage]") {
73 unique_ptr<QueryResult> result;
74 auto storage_database = TestCreatePath("storage_test");
75 auto config = GetTestConfig();
76
77 // make sure the database does not exist
78 DeleteDatabase(storage_database);
79 {
80 // create a database and insert values
81 DuckDB db(storage_database, config.get());
82 Connection con(db);
83 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
84 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
85 }
86 // reload and alter
87 {
88 DuckDB db(storage_database, config.get());
89 Connection con(db);
90
91 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ADD COLUMN k INTEGER DEFAULT 2"));
92
93 result = con.Query("SELECT k FROM test ORDER BY k");
94 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 2}));
95 }
96 // now reload
97 for (idx_t i = 0; i < 2; i++) {
98 DuckDB db(storage_database, config.get());
99 Connection con(db);
100
101 result = con.Query("SELECT k FROM test ORDER BY k");
102 REQUIRE(CHECK_COLUMN(result, 0, {2, 2, 2}));
103 }
104 DeleteDatabase(storage_database);
105}
106
107TEST_CASE("Remove column from persistent table", "[storage]") {
108 unique_ptr<QueryResult> result;
109 auto storage_database = TestCreatePath("storage_test");
110 auto config = GetTestConfig();
111
112 // make sure the database does not exist
113 DeleteDatabase(storage_database);
114 {
115 // create a database and insert values
116 DuckDB db(storage_database, config.get());
117 Connection con(db);
118 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
119 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
120 }
121 // reload and alter
122 {
123 DuckDB db(storage_database, config.get());
124 Connection con(db);
125
126 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test DROP COLUMN b"));
127
128 result = con.Query("SELECT * FROM test ORDER BY 1");
129 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
130 REQUIRE(result->names.size() == 1);
131 }
132 // now reload
133 for (idx_t i = 0; i < 2; i++) {
134 DuckDB db(storage_database, config.get());
135 Connection con(db);
136
137 result = con.Query("SELECT * FROM test ORDER BY 1");
138 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
139 REQUIRE(result->names.size() == 1);
140 }
141 DeleteDatabase(storage_database);
142}
143
144TEST_CASE("Alter column type of persistent table", "[storage]") {
145 unique_ptr<QueryResult> result;
146 auto storage_database = TestCreatePath("storage_test");
147 auto config = GetTestConfig();
148
149 // make sure the database does not exist
150 DeleteDatabase(storage_database);
151 {
152 // create a database and insert values
153 DuckDB db(storage_database, config.get());
154 Connection con(db);
155 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
156 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)"));
157 }
158 // reload and alter
159 {
160 DuckDB db(storage_database, config.get());
161 Connection con(db);
162
163 REQUIRE_NO_FAIL(con.Query("ALTER TABLE test ALTER b TYPE VARCHAR"));
164
165 result = con.Query("SELECT * FROM test ORDER BY 1");
166 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
167 REQUIRE(CHECK_COLUMN(result, 1, {"22", "21", "22"}));
168 REQUIRE(result->names.size() == 2);
169 }
170 // now reload
171 for (idx_t i = 0; i < 2; i++) {
172 DuckDB db(storage_database, config.get());
173 Connection con(db);
174
175 result = con.Query("SELECT * FROM test ORDER BY 1");
176 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
177 REQUIRE(CHECK_COLUMN(result, 1, {"22", "21", "22"}));
178 REQUIRE(result->names.size() == 2);
179 }
180 DeleteDatabase(storage_database);
181}
182