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("Shutdown with running transaction", "[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);"));
21
22 // we start a transaction, but shutdown the DB before committing
23 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
24 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (22, 23);"));
25 }
26 // reload the database from disk
27 for (idx_t i = 0; i < 2; i++) {
28 DuckDB db(storage_database, config.get());
29 Connection con(db);
30 result = con.Query("SELECT * FROM test ORDER BY a");
31 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
32 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
33 }
34 DeleteDatabase(storage_database);
35}
36
37TEST_CASE("UNIQUE INDEX after shutdown", "[storage]") {
38 unique_ptr<QueryResult> result;
39 auto storage_database = TestCreatePath("storage_test");
40 auto config = GetTestConfig();
41
42 // make sure the database does not exist
43 DeleteDatabase(storage_database);
44 {
45 // create a database and insert values
46 DuckDB db(storage_database, config.get());
47 Connection con(db);
48 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER PRIMARY KEY, b INTEGER);"));
49 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22);"));
50 }
51 // reload the database from disk
52 for (idx_t i = 0; i < 2; i++) {
53 DuckDB db(storage_database, config.get());
54 Connection con(db);
55
56 result = con.Query("SELECT * FROM test ORDER BY a");
57 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
58 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
59
60 REQUIRE_FAIL(con.Query("INSERT INTO test VALUES (11, 24)"));
61 }
62 DeleteDatabase(storage_database);
63}
64
65TEST_CASE("CREATE INDEX statement after shutdown", "[storage]") {
66 unique_ptr<QueryResult> result;
67 auto storage_database = TestCreatePath("storage_test");
68 auto config = GetTestConfig();
69
70 // make sure the database does not exist
71 DeleteDatabase(storage_database);
72 {
73 // create a database and insert values
74 DuckDB db(storage_database, config.get());
75 Connection con(db);
76 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
77 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (13, 22);"));
78 }
79 // reload the database from disk
80 for (idx_t i = 0; i < 2; i++) {
81 DuckDB db(storage_database, config.get());
82 Connection con(db);
83
84 result = con.Query("SELECT * FROM test ORDER BY a");
85 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
86 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
87
88 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 24)"));
89
90 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test using art(a)"));
91
92 result = con.Query("SELECT a, b FROM test WHERE a=11 ORDER BY b");
93 REQUIRE(CHECK_COLUMN(result, 0, {11, 11}));
94 REQUIRE(CHECK_COLUMN(result, 1, {22, 24}));
95
96 result = con.Query("SELECT a, b FROM test WHERE a>11 ORDER BY b");
97 REQUIRE(CHECK_COLUMN(result, 0, {13}));
98 REQUIRE(CHECK_COLUMN(result, 1, {22}));
99
100 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=11 AND b=24"));
101
102 result = con.Query("SELECT * FROM test ORDER BY a");
103 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
104 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
105 }
106 // now with updates
107 for (idx_t i = 0; i < 2; i++) {
108 DuckDB db(storage_database, config.get());
109 Connection con(db);
110
111 result = con.Query("SELECT * FROM test ORDER BY a");
112 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
113 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
114
115 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 24)"));
116
117 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i_index ON test using art(a)"));
118
119 result = con.Query("SELECT a, b FROM test WHERE a=11 ORDER BY b");
120 REQUIRE(CHECK_COLUMN(result, 0, {11, 11}));
121 REQUIRE(CHECK_COLUMN(result, 1, {22, 24}));
122
123 result = con.Query("SELECT a, b FROM test WHERE a>11 ORDER BY b");
124 REQUIRE(CHECK_COLUMN(result, 0, {13}));
125 REQUIRE(CHECK_COLUMN(result, 1, {22}));
126
127 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=11 AND b=22"));
128 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=22 WHERE a=11"));
129
130 result = con.Query("SELECT * FROM test ORDER BY a");
131 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
132 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
133 }
134 DeleteDatabase(storage_database);
135}
136