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("Use sequences over different runs", "[storage]") {
9 auto config = GetTestConfig();
10 unique_ptr<QueryResult> result;
11 auto storage_database = TestCreatePath("storage_test");
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 SEQUENCE seq;"));
20 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq_cycle INCREMENT 1 MAXVALUE 3 START 2 CYCLE;"));
21 result = con.Query("SELECT nextval('seq')");
22 REQUIRE(CHECK_COLUMN(result, 0, {1}));
23 result = con.Query("SELECT nextval('seq_cycle')");
24 REQUIRE(CHECK_COLUMN(result, 0, {2}));
25 }
26 // reload the database from disk twice
27 {
28 DuckDB db(storage_database, config.get());
29 Connection con(db);
30 }
31 {
32 DuckDB db(storage_database, config.get());
33 Connection con(db);
34 result = con.Query("SELECT nextval('seq')");
35 REQUIRE(CHECK_COLUMN(result, 0, {2}));
36 result = con.Query("SELECT nextval('seq_cycle')");
37 REQUIRE(CHECK_COLUMN(result, 0, {3}));
38 }
39 // reload again
40 {
41 DuckDB db(storage_database, config.get());
42 Connection con(db);
43 result = con.Query("SELECT nextval('seq'), nextval('seq');");
44 REQUIRE(CHECK_COLUMN(result, 0, {3}));
45 REQUIRE(CHECK_COLUMN(result, 1, {4}));
46 result = con.Query("SELECT nextval('seq_cycle')");
47 REQUIRE(CHECK_COLUMN(result, 0, {1}));
48
49 // drop sequence
50 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
51 }
52 {
53 // reload
54 DuckDB db(storage_database, config.get());
55 Connection con(db);
56 // the sequence is gone now
57 REQUIRE_FAIL(con.Query("SELECT nextval('seq')"));
58 // the other sequence is still there
59 REQUIRE_NO_FAIL(con.Query("SELECT nextval('seq_cycle')"));
60 }
61 DeleteDatabase(storage_database);
62}
63
64TEST_CASE("Use sequences over different runs without checkpointing", "[storage]") {
65 unique_ptr<QueryResult> result;
66 auto storage_database = TestCreatePath("storage_test");
67
68 // make sure the database does not exist
69 DeleteDatabase(storage_database);
70 {
71 // create a database and insert values
72 DuckDB db(storage_database);
73 Connection con(db);
74 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
75 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq_cycle INCREMENT 1 MAXVALUE 3 START 2 CYCLE;"));
76 result = con.Query("SELECT nextval('seq')");
77 REQUIRE(CHECK_COLUMN(result, 0, {1}));
78 result = con.Query("SELECT nextval('seq_cycle')");
79 REQUIRE(CHECK_COLUMN(result, 0, {2}));
80 }
81 // reload the database from disk twice
82 {
83 DuckDB db(storage_database);
84 Connection con(db);
85 }
86 {
87 DuckDB db(storage_database);
88 Connection con(db);
89 result = con.Query("SELECT nextval('seq')");
90 REQUIRE(CHECK_COLUMN(result, 0, {2}));
91 result = con.Query("SELECT nextval('seq_cycle')");
92 REQUIRE(CHECK_COLUMN(result, 0, {3}));
93 }
94 // reload again
95 {
96 DuckDB db(storage_database);
97 Connection con(db);
98 result = con.Query("SELECT nextval('seq'), nextval('seq');");
99 REQUIRE(CHECK_COLUMN(result, 0, {3}));
100 REQUIRE(CHECK_COLUMN(result, 1, {4}));
101 result = con.Query("SELECT nextval('seq_cycle')");
102 REQUIRE(CHECK_COLUMN(result, 0, {1}));
103
104 // drop sequence
105 REQUIRE_NO_FAIL(con.Query("DROP SEQUENCE seq;"));
106 }
107 {
108 // reload
109 DuckDB db(storage_database);
110 Connection con(db);
111 // the sequence is gone now
112 REQUIRE_FAIL(con.Query("SELECT nextval('seq')"));
113 // the other sequence is still there
114 REQUIRE_NO_FAIL(con.Query("SELECT nextval('seq_cycle')"));
115 }
116 DeleteDatabase(storage_database);
117}
118
119TEST_CASE("Use sequences with uncommited transaction", "[storage]") {
120 auto config = GetTestConfig();
121 unique_ptr<QueryResult> result;
122 auto storage_database = TestCreatePath("storage_test");
123
124 // make sure the database does not exist
125 DeleteDatabase(storage_database);
126 {
127 // create a database and insert values
128 DuckDB db(storage_database, config.get());
129 Connection con(db);
130 Connection con2(db);
131 REQUIRE_NO_FAIL(con.Query("CREATE SEQUENCE seq;"));
132 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
133 result = con2.Query("SELECT nextval('seq')");
134 REQUIRE(CHECK_COLUMN(result, 0, {1}));
135 result = con.Query("SELECT nextval('seq')");
136 REQUIRE(CHECK_COLUMN(result, 0, {2}));
137 }
138 // reload the database from disk
139 {
140 DuckDB db(storage_database, config.get());
141 Connection con(db);
142 result = con.Query("SELECT nextval('seq')");
143 REQUIRE(CHECK_COLUMN(result, 0, {3}));
144 }
145 // reload again
146 {
147 DuckDB db(storage_database, config.get());
148 Connection con(db);
149 result = con.Query("SELECT nextval('seq'), nextval('seq');");
150 REQUIRE(CHECK_COLUMN(result, 0, {4}));
151 REQUIRE(CHECK_COLUMN(result, 1, {5}));
152 }
153 DeleteDatabase(storage_database);
154}
155