test/zserio/ValidationSqliteUtilTest.cpp
Line | Count | Source |
1 | | #include "gtest/gtest.h" |
2 | | |
3 | | #include <memory> |
4 | | |
5 | | #include "zserio/ValidationSqliteUtil.h" |
6 | | #include "zserio/SqliteConnection.h" |
7 | | #include "zserio/SqliteFinalizer.h" |
8 | | |
9 | | namespace zserio |
10 | | { |
11 | | |
12 | | class ValidationSqliteUtilTest : public ::testing::Test |
13 | | { |
14 | | public: |
15 | | ValidationSqliteUtilTest() : |
16 | | connection(createConnection()) |
17 | 4 | {} |
18 | | |
19 | | protected: |
20 | | using allocator_type = std::allocator<uint8_t>; |
21 | | using Util = ValidationSqliteUtil<allocator_type>; |
22 | | |
23 | | void insertRows(const std::string& tableName, uint32_t startId, uint32_t numRows) |
24 | 3 | { |
25 | 3 | Util::Statement statement( |
26 | 3 | connection.prepareStatement("INSERT INTO " + tableName + " VALUES (?, ?)")); |
27 | | |
28 | 24 | for (uint32_t i = 0; i < numRows; ++i21 ) |
29 | 21 | { |
30 | 21 | sqlite3_bind_int(statement.get(), 1, static_cast<int>(startId + i)); |
31 | 21 | sqlite3_bind_int(statement.get(), 2, static_cast<int>(i * i)); |
32 | 21 | ASSERT_EQ(SQLITE_DONE, sqlite3_step(statement.get())); |
33 | 21 | sqlite3_reset(statement.get()); |
34 | 21 | } |
35 | 3 | } |
36 | | |
37 | | using TiedColumn = std::tuple<Util::string_type, Util::string_type, bool, bool>; |
38 | | |
39 | | TiedColumn tieColumn(const Util::ColumnDescription& columnDescription) |
40 | 8 | { |
41 | 8 | return std::tie( |
42 | 8 | columnDescription.name, |
43 | 8 | columnDescription.type, |
44 | 8 | columnDescription.isNotNull, |
45 | 8 | columnDescription.isPrimaryKey); |
46 | 8 | } |
47 | | |
48 | | SqliteConnection connection; |
49 | | |
50 | | private: |
51 | | sqlite3* createConnection() |
52 | 4 | { |
53 | 4 | sqlite3* db = nullptr; |
54 | 4 | int result = sqlite3_open(IN_MEMORY_DATABASE, &db); |
55 | 4 | EXPECT_EQ(SQLITE_OK, result); |
56 | 4 | return db; |
57 | 4 | } |
58 | | |
59 | | static const char* const IN_MEMORY_DATABASE; |
60 | | }; |
61 | | |
62 | | const char* const ValidationSqliteUtilTest::IN_MEMORY_DATABASE = ":memory:"; |
63 | | |
64 | | TEST_F(ValidationSqliteUtilTest, getNumberOfTableRows) |
65 | 1 | { |
66 | 1 | ASSERT_THROW(Util::getNumberOfTableRows(connection, ""_sv, "test"_sv, allocator_type()), |
67 | 1 | SqliteException); |
68 | | |
69 | 1 | ASSERT_THROW(Util::getNumberOfTableRows(connection, "NONEXISTING"_sv, "test"_sv, allocator_type()), |
70 | 1 | SqliteException); |
71 | | |
72 | 1 | ASSERT_THROW({ |
73 | 1 | try |
74 | 1 | { |
75 | 1 | Util::getNumberOfTableRows(connection, ""_sv, "(SELECT load_extension('unknown'))"_sv, |
76 | 1 | allocator_type()); |
77 | 1 | } |
78 | 1 | catch (const SqliteException& e) |
79 | 1 | { |
80 | 1 | ASSERT_STREQ("ValidationSqliteUtils.getNumberOfTableRows: sqlite3_step() failed: SQL logic error", |
81 | 1 | e.what()); |
82 | 1 | throw; |
83 | 1 | } |
84 | 1 | }, SqliteException); |
85 | | |
86 | 1 | connection.executeUpdate("CREATE TABLE test(id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL)"); |
87 | 1 | ASSERT_EQ(0, Util::getNumberOfTableRows(connection, ""_sv, "test"_sv, allocator_type())); |
88 | | |
89 | 1 | insertRows("test", 0, 1); |
90 | 1 | ASSERT_EQ(1, Util::getNumberOfTableRows(connection, ""_sv, "test"_sv, allocator_type())); |
91 | | |
92 | 1 | insertRows("test", 1, 10); |
93 | 1 | ASSERT_EQ(11, Util::getNumberOfTableRows(connection, ""_sv, "test"_sv, allocator_type())); |
94 | | |
95 | 1 | insertRows("test", 11, 10); |
96 | 1 | ASSERT_EQ(21, Util::getNumberOfTableRows(connection, ""_sv, "test"_sv, allocator_type())); |
97 | 1 | } |
98 | | |
99 | | TEST_F(ValidationSqliteUtilTest, getTableSchema) |
100 | 1 | { |
101 | 1 | Util::TableSchema schema; |
102 | 1 | Util::getTableSchema(connection, ""_sv, "test"_sv, schema, allocator_type()); |
103 | 1 | ASSERT_TRUE(schema.empty()); |
104 | | |
105 | 1 | ASSERT_THROW(Util::getTableSchema(connection, "NONEXISTING"_sv, "test"_sv, schema, allocator_type()), |
106 | 1 | SqliteException); |
107 | | |
108 | 1 | connection.executeUpdate("CREATE TABLE test1(id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL)"); |
109 | 1 | Util::getTableSchema(connection, ""_sv, "test1"_sv, schema, allocator_type()); |
110 | 1 | ASSERT_EQ(2, schema.size()); |
111 | 1 | auto search = schema.find("id"); |
112 | 1 | ASSERT_TRUE(search != schema.end()); |
113 | 1 | ASSERT_EQ("id", search->first); |
114 | 1 | ASSERT_EQ(std::make_tuple("id", "INTEGER", true, true), tieColumn(search->second)); |
115 | 1 | search = schema.find("value"); |
116 | 1 | ASSERT_TRUE(search != schema.end()); |
117 | 1 | ASSERT_EQ("value", search->first); |
118 | 1 | ASSERT_EQ(std::make_tuple("value", "INTEGER", true, false), tieColumn(search->second)); |
119 | | |
120 | 1 | schema.clear(); |
121 | 1 | connection.executeUpdate("CREATE TABLE test2(id INTEGER PRIMARY KEY NOT NULL, text TEXT NOT NULL, " |
122 | 1 | "field BLOB)"); |
123 | 1 | Util::getTableSchema(connection, ""_sv, "test2"_sv, schema, allocator_type()); |
124 | 1 | ASSERT_EQ(3, schema.size()); |
125 | 1 | search = schema.find("id"); |
126 | 1 | ASSERT_TRUE(search != schema.end()); |
127 | 1 | ASSERT_EQ("id", search->first); |
128 | 1 | ASSERT_EQ(std::make_tuple("id", "INTEGER", true, true), tieColumn(search->second)); |
129 | 1 | search = schema.find("text"); |
130 | 1 | ASSERT_TRUE(search != schema.end()); |
131 | 1 | ASSERT_EQ("text", search->first); |
132 | 1 | ASSERT_EQ(std::make_tuple("text", "TEXT", true, false), tieColumn(search->second)); |
133 | 1 | search = schema.find("field"); |
134 | 1 | ASSERT_TRUE(search != schema.end()); |
135 | 1 | ASSERT_EQ("field", search->first); |
136 | 1 | ASSERT_EQ(std::make_tuple("field", "BLOB", false, false), tieColumn(search->second)); |
137 | | |
138 | | // multiple primary keys |
139 | 1 | schema.clear(); |
140 | 1 | connection.executeUpdate("CREATE TABLE test3(name TEXT, surname TEXT NOT NULL, field BLOB, " |
141 | 1 | "PRIMARY KEY(name, surname))"); |
142 | 1 | Util::getTableSchema(connection, ""_sv, "test3"_sv, schema, allocator_type()); |
143 | 1 | ASSERT_EQ(3, schema.size()); |
144 | 1 | search = schema.find("name"); |
145 | 1 | ASSERT_TRUE(search != schema.end()); |
146 | 1 | ASSERT_EQ("name", search->first); |
147 | 1 | ASSERT_EQ(std::make_tuple("name", "TEXT", false, true), tieColumn(search->second)); |
148 | 1 | search = schema.find("surname"); |
149 | 1 | ASSERT_TRUE(search != schema.end()); |
150 | 1 | ASSERT_EQ("surname", search->first); |
151 | 1 | ASSERT_EQ(std::make_tuple("surname", "TEXT", true, true), tieColumn(search->second)); |
152 | 1 | search = schema.find("field"); |
153 | 1 | ASSERT_TRUE(search != schema.end()); |
154 | 1 | ASSERT_EQ("field", search->first); |
155 | 1 | ASSERT_EQ(std::make_tuple("field", "BLOB", false, false), tieColumn(search->second)); |
156 | 1 | } |
157 | | |
158 | | TEST_F(ValidationSqliteUtilTest, isColumnInTable) |
159 | 1 | { |
160 | 1 | ASSERT_FALSE(Util::isColumnInTable(connection, ""_sv, "test"_sv, "hidden"_sv, allocator_type())); |
161 | | |
162 | 1 | ASSERT_FALSE(Util::isColumnInTable(connection, "NONEXISTING"_sv, "test"_sv, "hidden"_sv, allocator_type())); |
163 | | |
164 | 1 | connection.executeUpdate("CREATE TABLE test1(id INTEGER PRIMARY KEY NOT NULL, value INTEGER NOT NULL)"); |
165 | 1 | ASSERT_FALSE(Util::isColumnInTable(connection, ""_sv, "test1"_sv, "hidden"_sv, allocator_type())); |
166 | 1 | ASSERT_TRUE(Util::isColumnInTable(connection, ""_sv, "test1"_sv, "id"_sv, allocator_type())); |
167 | 1 | ASSERT_TRUE(Util::isColumnInTable(connection, ""_sv, "test1"_sv, "value"_sv, allocator_type())); |
168 | | |
169 | 1 | connection.executeUpdate("CREATE TABLE test2(id INTEGER PRIMARY KEY NOT NULL, text HIDDEN TEXT)"); |
170 | 1 | ASSERT_TRUE(Util::isColumnInTable(connection, ""_sv, "test2"_sv, "id"_sv, allocator_type())); |
171 | 1 | ASSERT_TRUE(Util::isColumnInTable(connection, ""_sv, "test2"_sv, "text"_sv, allocator_type())); |
172 | 1 | } |
173 | | |
174 | | TEST_F(ValidationSqliteUtilTest, sqliteColumnTypeName) |
175 | 1 | { |
176 | | // this test also verifies that sqlite3_column_type works as we except since we need to use it |
177 | | // in validateType* in generated sources for zserio SqlTables |
178 | | |
179 | 1 | const char* tableName = "sqliteColumnTypeTable"; |
180 | 1 | connection.executeUpdate(std::string("CREATE TABLE ") + tableName + "(id INTEGER PRIMARY KEY, " |
181 | 1 | "integerCol INTEGER, realCol REAL, textCol TEXT, blobCol BLOB)"); |
182 | | |
183 | 1 | connection.executeUpdate(std::string("INSERT INTO ") + tableName + " VALUES (0, NULL, NULL, NULL, NULL)"); |
184 | 1 | connection.executeUpdate(std::string("INSERT INTO ") + tableName + " VALUES (1, 13, 1.3, 'STRING', x'00')"); |
185 | 1 | connection.executeUpdate(std::string("INSERT INTO ") + tableName + " VALUES (2, 1.3, 'STRING', x'00', 13)"); |
186 | | |
187 | 1 | Util::Statement stmt(connection.prepareStatement(std::string("SELECT * from ") + tableName)); |
188 | | |
189 | | // first row check NULL values |
190 | 1 | ASSERT_EQ(SQLITE_ROW, sqlite3_step(stmt.get())); |
191 | 1 | int type = sqlite3_column_type(stmt.get(), 0); |
192 | 1 | ASSERT_EQ(SQLITE_INTEGER, type); |
193 | 1 | ASSERT_STREQ("INTEGER", Util::sqliteColumnTypeName(type)); |
194 | 1 | type = sqlite3_column_type(stmt.get(), 1); |
195 | 1 | ASSERT_EQ(SQLITE_NULL, type); |
196 | 1 | ASSERT_STREQ("NULL", Util::sqliteColumnTypeName(type)); |
197 | 1 | type = sqlite3_column_type(stmt.get(), 2); |
198 | 1 | ASSERT_EQ(SQLITE_NULL, type); |
199 | 1 | ASSERT_STREQ("NULL", Util::sqliteColumnTypeName(type)); |
200 | 1 | type = sqlite3_column_type(stmt.get(), 3); |
201 | 1 | ASSERT_EQ(SQLITE_NULL, type); |
202 | 1 | ASSERT_STREQ("NULL", Util::sqliteColumnTypeName(type)); |
203 | 1 | type = sqlite3_column_type(stmt.get(), 4); |
204 | 1 | ASSERT_EQ(SQLITE_NULL, type); |
205 | 1 | ASSERT_STREQ("NULL", Util::sqliteColumnTypeName(type)); |
206 | | |
207 | | // second row checks correct values |
208 | 1 | ASSERT_EQ(SQLITE_ROW, sqlite3_step(stmt.get())); |
209 | 1 | type = sqlite3_column_type(stmt.get(), 0); |
210 | 1 | ASSERT_EQ(SQLITE_INTEGER, type); |
211 | 1 | ASSERT_STREQ("INTEGER", Util::sqliteColumnTypeName(type)); |
212 | 1 | type = sqlite3_column_type(stmt.get(), 1); |
213 | 1 | ASSERT_EQ(SQLITE_INTEGER, type); |
214 | 1 | ASSERT_STREQ("INTEGER", Util::sqliteColumnTypeName(type)); |
215 | 1 | type = sqlite3_column_type(stmt.get(), 2); |
216 | 1 | ASSERT_EQ(SQLITE_FLOAT, type); |
217 | 1 | ASSERT_STREQ("REAL", Util::sqliteColumnTypeName(type)); |
218 | 1 | type = sqlite3_column_type(stmt.get(), 3); |
219 | 1 | ASSERT_EQ(SQLITE_TEXT, type); |
220 | 1 | ASSERT_STREQ("TEXT", Util::sqliteColumnTypeName(type)); |
221 | 1 | type = sqlite3_column_type(stmt.get(), 4); |
222 | 1 | ASSERT_EQ(SQLITE_BLOB, type); |
223 | 1 | ASSERT_STREQ("BLOB", Util::sqliteColumnTypeName(type)); |
224 | | |
225 | | // third row checks types mismatch - i.e. checks dynamic typing in SQLite |
226 | 1 | ASSERT_EQ(SQLITE_ROW, sqlite3_step(stmt.get())); |
227 | 1 | type = sqlite3_column_type(stmt.get(), 0); |
228 | 1 | ASSERT_EQ(SQLITE_INTEGER, type); |
229 | 1 | ASSERT_STREQ("INTEGER", Util::sqliteColumnTypeName(type)); |
230 | 1 | type = sqlite3_column_type(stmt.get(), 1); |
231 | 1 | ASSERT_EQ(SQLITE_FLOAT, type); |
232 | 1 | ASSERT_STREQ("REAL", Util::sqliteColumnTypeName(type)); |
233 | 1 | type = sqlite3_column_type(stmt.get(), 2); |
234 | 1 | ASSERT_EQ(SQLITE_TEXT, type); |
235 | 1 | ASSERT_STREQ("TEXT", Util::sqliteColumnTypeName(type)); |
236 | 1 | type = sqlite3_column_type(stmt.get(), 3); |
237 | 1 | ASSERT_EQ(SQLITE_BLOB, type); |
238 | 1 | ASSERT_STREQ("BLOB", Util::sqliteColumnTypeName(type)); |
239 | 1 | type = sqlite3_column_type(stmt.get(), 4); |
240 | 1 | ASSERT_EQ(SQLITE_INTEGER, type); |
241 | 1 | ASSERT_STREQ("INTEGER", Util::sqliteColumnTypeName(type)); |
242 | 1 | } |
243 | | |
244 | | } // namespace zserio |