Coverage Report

Created: 2023-12-13 14:58

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