1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
|
/**
* @file Column_test.cpp
* @ingroup tests
* @brief Test of a SQLiteCpp Column.
*
* Copyright (c) 2012-2025 Sebastien Rombauts (sebastien.rombauts@gmail.com)
*
* Distributed under the MIT License (MIT) (See accompanying file LICENSE.txt
* or copy at http://opensource.org/licenses/MIT)
*/
#include <SQLiteCpp/Database.h>
#include <SQLiteCpp/Statement.h>
#include <SQLiteCpp/Column.h>
#include <gtest/gtest.h>
#include <cstdio>
#include <stdint.h>
static void test_column_basis(bool utf16)
{
// Create a new database
SQLite::Database db(":memory:", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
EXPECT_EQ(SQLite::OK, db.getErrorCode());
EXPECT_EQ(SQLite::OK, db.getExtendedErrorCode());
if (utf16)
{
EXPECT_EQ(0, db.exec("PRAGMA encoding = 'UTF-16';"));
}
// Create a new table
EXPECT_EQ(0, db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, msg TEXT, int INTEGER, double REAL, binary BLOB, empty TEXT)"));
EXPECT_TRUE(db.tableExists("test"));
EXPECT_TRUE(db.tableExists(std::string("test")));
EXPECT_EQ(0, db.getLastInsertRowid());
// Create a first row (autoid: 1) with all kind of data and a null value
SQLite::Statement insert(db, "INSERT INTO test VALUES (NULL, 'first', -123, 0.123, ?, NULL)");
// Bind the blob value to the first parameter of the SQL query
const char buffer[] = {'b', 'l', '\0', 'b'}; // "bl\0b" : 4 char, with a null byte inside
const int size = sizeof(buffer); // size = 4
const void* blob = &buffer;
insert.bind(1, blob, size);
// Execute the one-step query to insert the row
EXPECT_EQ(1, insert.exec());
EXPECT_EQ(1, db.getLastInsertRowid());
EXPECT_EQ(1, db.getChanges());
EXPECT_EQ(1, db.getTotalChanges());
EXPECT_THROW(insert.exec(), SQLite::Exception); // exec() shall throw as it needs to be reseted
// Compile a SQL query
SQLite::Statement query(db, "SELECT * FROM test");
EXPECT_STREQ("SELECT * FROM test", query.getQuery().c_str());
EXPECT_EQ(6, query.getColumnCount ());
query.executeStep();
EXPECT_TRUE (query.hasRow());
EXPECT_FALSE(query.isDone());
// validates every variant of cast operators, and conversions of types
{
const int64_t id1 = query.getColumn(0); // operator int64_t()
const int32_t id2 = query.getColumn(0); // operator int32_t()
const int id3 = query.getColumn(0); // operator int32_t()
const int16_t id4 = query.getColumn(0); // operator int32_t()
const short id5 = query.getColumn(0); // operator int32_t()
const int8_t id6 = query.getColumn(0); // operator int32_t()
const char id7 = query.getColumn(0); // operator int32_t()
const unsigned int uint1 = query.getColumn(0); // operator unsigned int()
const uint32_t uint2 = query.getColumn(0); // operator unsigned int()
const unsigned char uint3 = query.getColumn(0); // operator unsigned char()
const unsigned short uint4 = query.getColumn(0); // operator unsigned short()
const char* ptxt = query.getColumn(1); // operator const char*()
const std::string msg = query.getColumn(1); // operator std::string() (or const char* with MSVC)
const int integer = query.getColumn(2); // operator int()
const double real = query.getColumn(3); // operator double()
const void* pblob = query.getColumn(4); // operator void*()
#if !defined(_MSC_VER) || _MSC_VER >= 1900
// This implicit cast should use operator std::string()
// but would fallback to const char* with MSVC 2010-2013 (witch does not work with the NULL char in the middle)
const std::string sblob = query.getColumn(4); // operator std::string()
#endif
const void* pempty = query.getColumn(5); // operator void*()
EXPECT_EQ(1, id1);
EXPECT_EQ(1, id2);
EXPECT_EQ(1, id3);
EXPECT_EQ(1, id4);
EXPECT_EQ(1, id5);
EXPECT_EQ(1, id6);
EXPECT_EQ(1, id7);
EXPECT_EQ(1U, uint1);
EXPECT_EQ(1U, uint2);
EXPECT_EQ(1U, uint3);
EXPECT_EQ(1U, uint4);
EXPECT_STREQ("first", ptxt);
EXPECT_EQ("first", msg);
EXPECT_EQ(-123, integer);
EXPECT_DOUBLE_EQ(0.123, real);
EXPECT_EQ(0, memcmp("bl\0b", pblob, size));
#if !defined(_MSC_VER) || _MSC_VER >= 1900
EXPECT_EQ((size_t)size, sblob.size());
EXPECT_EQ(0, memcmp("bl\0b", &sblob[0], size));
#endif
EXPECT_EQ(NULL, pempty);
}
query.reset();
query.executeStep();
// validates every variant of explicit getters
{
int64_t id = query.getColumn(0).getInt64();
const unsigned int uint1 = query.getColumn(0).getUInt();
const uint32_t uint2 = query.getColumn(0).getUInt();
const std::string msg1 = query.getColumn(1).getString();
const char* ptxt = query.getColumn(1).getText();
const std::string msg2 = query.getColumn(1).getText();
const int integer = query.getColumn(2).getInt();
const double real = query.getColumn(3).getDouble();
const void* pblob = query.getColumn(4).getBlob();
const std::string sblob = query.getColumn(4).getString();
EXPECT_EQ(1, id);
EXPECT_EQ(1U, uint1);
EXPECT_EQ(1U, uint2);
EXPECT_STREQ("first", ptxt);
EXPECT_EQ("first", msg1);
EXPECT_EQ("first", msg2);
EXPECT_EQ(-123, integer);
EXPECT_DOUBLE_EQ(0.123, real);
EXPECT_EQ(0, memcmp("bl\0b", pblob, 4));
EXPECT_EQ(0, memcmp("bl\0b", &sblob[0], 4));
}
// Validate getBytes(), getType(), isInteger(), isNull()...
EXPECT_EQ(SQLite::INTEGER, query.getColumn(0).getType());
EXPECT_EQ(true, query.getColumn(0).isInteger());
EXPECT_EQ(false, query.getColumn(0).isFloat());
EXPECT_EQ(false, query.getColumn(0).isText());
EXPECT_EQ(false, query.getColumn(0).isBlob());
EXPECT_EQ(false, query.getColumn(0).isNull());
EXPECT_STREQ("1", query.getColumn(0).getText()); // convert to TEXT via text func
EXPECT_EQ(1, query.getColumn(0).getBytes()); // size of the string "1" without the null terminator
EXPECT_EQ(SQLite::TEXT, query.getColumn(1).getType());
EXPECT_EQ(false, query.getColumn(1).isInteger());
EXPECT_EQ(false, query.getColumn(1).isFloat());
EXPECT_EQ(true, query.getColumn(1).isText());
EXPECT_EQ(false, query.getColumn(1).isBlob());
EXPECT_EQ(false, query.getColumn(1).isNull());
EXPECT_STREQ("first", query.getColumn(1).getString().c_str()); // convert to TEXT via string func
EXPECT_EQ(5, query.getColumn(1).getBytes()); // size of the string "first"
EXPECT_EQ(SQLite::INTEGER, query.getColumn(2).getType());
EXPECT_EQ(true, query.getColumn(2).isInteger());
EXPECT_EQ(false, query.getColumn(2).isFloat());
EXPECT_EQ(false, query.getColumn(2).isText());
EXPECT_EQ(false, query.getColumn(2).isBlob());
EXPECT_EQ(false, query.getColumn(2).isNull());
EXPECT_STREQ("-123", query.getColumn(2).getText()); // convert to string
EXPECT_EQ(4, query.getColumn(2).getBytes()); // size of the string "-123"
EXPECT_EQ(SQLite::FLOAT, query.getColumn(3).getType());
EXPECT_EQ(false, query.getColumn(3).isInteger());
EXPECT_EQ(true, query.getColumn(3).isFloat());
EXPECT_EQ(false, query.getColumn(3).isText());
EXPECT_EQ(false, query.getColumn(3).isBlob());
EXPECT_EQ(false, query.getColumn(3).isNull());
EXPECT_STREQ("0.123", query.getColumn(3).getText()); // convert to string
EXPECT_EQ(5, query.getColumn(3).getBytes()); // size of the string "0.123"
EXPECT_EQ(SQLite::BLOB, query.getColumn(4).getType());
EXPECT_EQ(false, query.getColumn(4).isInteger());
EXPECT_EQ(false, query.getColumn(4).isFloat());
EXPECT_EQ(false, query.getColumn(4).isText());
EXPECT_EQ(true, query.getColumn(4).isBlob());
EXPECT_EQ(false, query.getColumn(4).isNull());
EXPECT_EQ(4, query.getColumn(4).getBytes()); // size of the blob "bl\0b" with the null char
EXPECT_EQ(SQLite::Null, query.getColumn(5).getType());
EXPECT_EQ(false, query.getColumn(5).isInteger());
EXPECT_EQ(false, query.getColumn(5).isFloat());
EXPECT_EQ(false, query.getColumn(5).isText());
EXPECT_EQ(false, query.getColumn(5).isBlob());
EXPECT_EQ(true, query.getColumn(5).isNull());
EXPECT_STREQ("", query.getColumn(5).getText()); // convert to string
EXPECT_EQ(0, query.getColumn(5).getBytes()); // size of the string "" without the null terminator
query.reset();
query.executeStep();
// Use intermediate Column objects (this is not the recommended way to use the API)
{
const SQLite::Column id = query.getColumn(0);
EXPECT_EQ(1, id.getInt64());
const SQLite::Column msg = query.getColumn(1);
EXPECT_EQ("first", msg.getString());
const SQLite::Column integer = query.getColumn(2);
EXPECT_EQ(-123, integer.getInt());
const SQLite::Column dbl = query.getColumn(3);
EXPECT_DOUBLE_EQ(0.123, dbl.getDouble());
}
}
TEST(Column, basis)
{
test_column_basis(false);
}
TEST(Column, basis16)
{
test_column_basis(true);
}
TEST(Column, getName)
{
// Create a new database
SQLite::Database db(":memory:", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
EXPECT_EQ(0, db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, msg TEXT)"));
EXPECT_EQ(1, db.exec("INSERT INTO test VALUES (NULL, 'first')"));
// Compile a SQL query, using the "id" column name as-is, but aliasing the "msg" column with new name "value"
SQLite::Statement query(db, "SELECT id, msg as value FROM test");
query.executeStep();
// Show how to get the aliased names of the result columns.
const std::string name0 = query.getColumn(0).getName();
const std::string name1 = query.getColumn(1).getName();
EXPECT_EQ("id", name0);
EXPECT_EQ("value", name1);
#ifdef SQLITE_ENABLE_COLUMN_METADATA
// Show how to get origin names of the table columns from which theses result columns come from.
// Requires the SQLITE_ENABLE_COLUMN_METADATA preprocessor macro to be
// also defined at compile times of the SQLite library itself.
const std::string oname0 = query.getColumn(0).getOriginName();
const std::string oname1 = query.getColumn(1).getOriginName();
EXPECT_EQ("id", oname0);
EXPECT_EQ("msg", oname1);
#endif
}
TEST(Column, stream)
{
// Create a new database
SQLite::Database db(":memory:", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
EXPECT_EQ(0, db.exec("CREATE TABLE test (msg TEXT)"));
SQLite::Statement insert(db, "INSERT INTO test VALUES (?)");
// content to test
const char str_[] = "stringwith\0embedded";
std::string str(str_, sizeof(str_)-1);
insert.bind(1, str);
// Execute the one-step query to insert the row
EXPECT_EQ(1, insert.exec());
EXPECT_EQ(1, db.getChanges());
EXPECT_EQ(1, db.getTotalChanges());
SQLite::Statement query(db, "SELECT * FROM test");
query.executeStep();
std::stringstream ss;
ss << query.getColumn(0);
std::string content = ss.str();
EXPECT_EQ(content, str);
}
TEST(Column, shared_ptr)
{
// Create a new database
SQLite::Database db(":memory:", SQLite::OPEN_READWRITE|SQLite::OPEN_CREATE);
EXPECT_EQ(0, db.exec("CREATE TABLE test (id INTEGER PRIMARY KEY, msg TEXT)"));
EXPECT_EQ(1, db.exec("INSERT INTO test VALUES (42, 'fortytwo')"));
const char* query_str = "SELECT id, msg FROM test";
std::unique_ptr<SQLite::Statement> query{ new SQLite::Statement(db, query_str) };
query->executeStep();
auto column0 = query->getColumn(0);
auto column1 = query->getColumn(1);
query.reset();
EXPECT_EQ(42, column0.getInt());
EXPECT_STREQ("fortytwo", column1.getText());
query.reset(new SQLite::Statement(db, query_str));
query->executeStep();
column0 = query->getColumn(0);
EXPECT_EQ(true, column0.isInteger());
query->executeStep(); // query is done
// Undefined behavior
// auto x = column0.getInt();
query.reset();
// Undefined behavior
// auto x = column0.getInt();
// bool isInt = column0.isInteger();
EXPECT_STREQ("id", column0.getName());
}
|
