1#include "catch.hpp"
2#include "duckdb/main/appender.hpp"
3#include "test_helpers.hpp"
4#include "duckdb/common/types/date.hpp"
5#include "duckdb/common/types/time.hpp"
6#include "duckdb/common/types/timestamp.hpp"
7
8#include <vector>
9
10using namespace duckdb;
11using namespace std;
12
13TEST_CASE("Basic appender tests", "[appender]") {
14 unique_ptr<QueryResult> result;
15 DuckDB db(nullptr);
16 Connection con(db);
17
18 // create a table to append to
19 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
20
21 // append a bunch of values
22 {
23 Appender appender(con, "integers");
24 for (size_t i = 0; i < 2000; i++) {
25 appender.BeginRow();
26 appender.Append<int32_t>(1);
27 appender.EndRow();
28 }
29 appender.Close();
30 }
31
32 con.Query("BEGIN TRANSACTION");
33
34 // check that the values have been added to the database
35 result = con.Query("SELECT SUM(i) FROM integers");
36 REQUIRE(CHECK_COLUMN(result, 0, {2000}));
37
38 // test a rollback of the appender
39 {
40 Appender appender2(con, "integers");
41 // now append a bunch of values
42 for (size_t i = 0; i < 2000; i++) {
43 appender2.BeginRow();
44 appender2.Append<int32_t>(1);
45 appender2.EndRow();
46 }
47 appender2.Close();
48 }
49 con.Query("ROLLBACK");
50
51 // the data in the database should not be changed
52 result = con.Query("SELECT SUM(i) FROM integers");
53 REQUIRE(CHECK_COLUMN(result, 0, {2000}));
54
55 // test different types
56 REQUIRE_NO_FAIL(con.Query("CREATE TABLE vals(i TINYINT, j SMALLINT, k BIGINT, l VARCHAR, m DECIMAL)"));
57
58 // now append a bunch of values
59 {
60 Appender appender(con, "vals");
61
62 for (size_t i = 0; i < 2000; i++) {
63 appender.BeginRow();
64 appender.Append<int8_t>(1);
65 appender.Append<int16_t>(1);
66 appender.Append<int64_t>(1);
67 appender.Append<const char *>("hello");
68 appender.Append<double>(3.33);
69 appender.EndRow();
70 }
71 }
72
73 // check that the values have been added to the database
74 result = con.Query("SELECT l, SUM(k) FROM vals GROUP BY l");
75 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
76 REQUIRE(CHECK_COLUMN(result, 1, {2000}));
77
78 // now test various error conditions
79 // too few values per row
80 {
81 Appender appender(con, "integers");
82 appender.BeginRow();
83 REQUIRE_THROWS(appender.EndRow());
84 }
85 // too many values per row
86 {
87 Appender appender(con, "integers");
88 appender.BeginRow();
89 appender.Append<Value>(Value::INTEGER(2000));
90 REQUIRE_THROWS(appender.Append<Value>(Value::INTEGER(2000)));
91 }
92}
93
94TEST_CASE("Test AppendRow", "[appender]") {
95 unique_ptr<QueryResult> result;
96 DuckDB db(nullptr);
97 Connection con(db);
98
99 // create a table to append to
100 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER)"));
101
102 // append a bunch of values
103 {
104 Appender appender(con, "integers");
105 for (size_t i = 0; i < 2000; i++) {
106 appender.AppendRow(1);
107 }
108 appender.Close();
109 }
110
111 // check that the values have been added to the database
112 result = con.Query("SELECT SUM(i) FROM integers");
113 REQUIRE(CHECK_COLUMN(result, 0, {2000}));
114
115 {
116 Appender appender(con, "integers");
117 // test wrong types in append row
118 REQUIRE_THROWS(appender.AppendRow("hello"));
119 }
120
121 // test different types
122 REQUIRE_NO_FAIL(con.Query("CREATE TABLE vals(i TINYINT, j SMALLINT, k BIGINT, l VARCHAR, m DECIMAL)"));
123 // now append a bunch of values
124 {
125 Appender appender(con, "vals");
126 for (size_t i = 0; i < 2000; i++) {
127 appender.AppendRow(1, 1, 1, "hello", 3.33);
128 // append null values
129 appender.AppendRow(nullptr, nullptr, nullptr, nullptr, nullptr);
130 }
131 }
132
133 result = con.Query("SELECT COUNT(*), COUNT(i), COUNT(j), COUNT(k), COUNT(l), COUNT(m) FROM vals");
134 REQUIRE(CHECK_COLUMN(result, 0, {4000}));
135 REQUIRE(CHECK_COLUMN(result, 1, {2000}));
136 REQUIRE(CHECK_COLUMN(result, 2, {2000}));
137 REQUIRE(CHECK_COLUMN(result, 3, {2000}));
138 REQUIRE(CHECK_COLUMN(result, 4, {2000}));
139 REQUIRE(CHECK_COLUMN(result, 5, {2000}));
140
141 // check that the values have been added to the database
142 result = con.Query("SELECT l, SUM(k) FROM vals WHERE i IS NOT NULL GROUP BY l");
143 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
144 REQUIRE(CHECK_COLUMN(result, 1, {2000}));
145
146 // test dates and times
147 REQUIRE_NO_FAIL(con.Query("CREATE TABLE dates(d DATE, t TIME, ts TIMESTAMP)"));
148 // now append a bunch of values
149 {
150 Appender appender(con, "dates");
151 appender.AppendRow(Value::DATE(1992, 1, 1), Value::TIME(1, 1, 1, 0), Value::TIMESTAMP(1992, 1, 1, 1, 1, 1, 0));
152 }
153 result = con.Query("SELECT * FROM dates");
154 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 1, 1)}));
155 REQUIRE(CHECK_COLUMN(result, 1, {Value::TIME(1, 1, 1, 0)}));
156 REQUIRE(CHECK_COLUMN(result, 2, {Value::TIMESTAMP(1992, 1, 1, 1, 1, 1, 0)}));
157
158 // test dates and times without value append
159 REQUIRE_NO_FAIL(con.Query("DELETE FROM dates"));
160 // now append a bunch of values
161 {
162 Appender appender(con, "dates");
163 appender.AppendRow(Date::FromDate(1992, 1, 1), Time::FromTime(1, 1, 1, 0),
164 Timestamp::FromDatetime(Date::FromDate(1992, 1, 1), Time::FromTime(1, 1, 1, 0)));
165 }
166 result = con.Query("SELECT * FROM dates");
167 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 1, 1)}));
168 REQUIRE(CHECK_COLUMN(result, 1, {Value::TIME(1, 1, 1, 0)}));
169 REQUIRE(CHECK_COLUMN(result, 2, {Value::TIMESTAMP(1992, 1, 1, 1, 1, 1, 0)}));
170}
171
172TEST_CASE("Test incorrect usage of appender", "[appender]") {
173 unique_ptr<QueryResult> result;
174 DuckDB db(nullptr);
175 Connection con(db);
176
177 // create a table to append to
178 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER)"));
179
180 // append a bunch of values
181 {
182 Appender appender(con, "integers");
183 appender.BeginRow();
184 appender.Append<int32_t>(1);
185 // call EndRow before all rows have been appended results in an exception
186 REQUIRE_THROWS(appender.EndRow());
187 // the appender is now invalidated: anything results in an exception
188 REQUIRE_THROWS(appender.BeginRow());
189 REQUIRE_THROWS(appender.Append<int32_t>(1));
190 REQUIRE_THROWS(appender.Flush());
191 // except we can still close the appender
192 REQUIRE_NOTHROW(appender.Close());
193 }
194 {
195 Appender appender(con, "integers");
196 // flushing results in the same error
197 appender.BeginRow();
198 appender.Append<int32_t>(1);
199 REQUIRE_THROWS(appender.Flush());
200 // and also invalidates the connection
201 REQUIRE_THROWS(appender.BeginRow());
202 REQUIRE_THROWS(appender.Append<int32_t>(1));
203 REQUIRE_THROWS(appender.Flush());
204 // except we can still close the appender
205 REQUIRE_NOTHROW(appender.Close());
206 }
207 {
208 // we get the same exception when calling AppendRow with an incorrect number of arguments
209 Appender appender(con, "integers");
210 REQUIRE_THROWS(appender.AppendRow(1));
211 // and also invalidates the connection
212 REQUIRE_THROWS(appender.BeginRow());
213 REQUIRE_THROWS(appender.Append<int32_t>(1));
214 REQUIRE_THROWS(appender.Append<int32_t>(1));
215 REQUIRE_THROWS(appender.EndRow());
216 }
217 {
218 // we can flush an empty appender
219 Appender appender(con, "integers");
220 REQUIRE_NOTHROW(appender.Flush());
221 REQUIRE_NOTHROW(appender.Flush());
222 REQUIRE_NOTHROW(appender.Flush());
223 }
224}
225
226TEST_CASE("Test invalid input for appender", "[appender]") {
227 unique_ptr<QueryResult> result;
228 DuckDB db(nullptr);
229 Connection con(db);
230
231 REQUIRE_NO_FAIL(con.Query("CREATE TABLE doubles(d DOUBLE, f REAL)"));
232 {
233 // appending NAN or INF fails
234 Appender appender(con, "doubles");
235 appender.BeginRow();
236 REQUIRE_THROWS(appender.Append<double>(1e308 + 1e308));
237 }
238 {
239 // appending NAN or INF fails
240 Appender appender(con, "doubles");
241 appender.BeginRow();
242 appender.Append<double>(1);
243 REQUIRE_THROWS(appender.Append<float>(1e38f * 1e38f));
244 }
245}
246
247TEST_CASE("Test appender with quotes", "[appender]") {
248 unique_ptr<QueryResult> result;
249 DuckDB db(nullptr);
250 Connection con(db);
251
252 REQUIRE_NO_FAIL(con.Query("CREATE SCHEMA \"my_schema\""));
253 REQUIRE_NO_FAIL(con.Query("CREATE TABLE \"my_schema\".\"my_table\"(\"i\" INTEGER)"));
254
255 // append a bunch of values
256 {
257 Appender appender(con, "my_schema", "my_table");
258 appender.AppendRow(1);
259 appender.Close();
260 }
261 result = con.Query("SELECT * FROM my_schema.my_table");
262 REQUIRE(CHECK_COLUMN(result, 0, {1}));
263}
264