1 |
|
|
#ifndef ZSERIO_VALIDATION_SQLITE_UTIL_H_INC |
2 |
|
|
#define ZSERIO_VALIDATION_SQLITE_UTIL_H_INC |
3 |
|
|
|
4 |
|
|
#include <map> |
5 |
|
|
|
6 |
|
|
#include "zserio/RebindAlloc.h" |
7 |
|
|
#include "zserio/String.h" |
8 |
|
|
#include "zserio/StringView.h" |
9 |
|
|
#include "zserio/SqliteConnection.h" |
10 |
|
|
#include "zserio/SqliteFinalizer.h" |
11 |
|
|
|
12 |
|
|
namespace zserio |
13 |
|
|
{ |
14 |
|
|
|
15 |
|
|
/** Sqlite utility for validation. */ |
16 |
|
|
template <typename ALLOC> |
17 |
|
|
struct ValidationSqliteUtil |
18 |
|
|
{ |
19 |
|
|
using string_type = string<ALLOC>; |
20 |
|
|
using Statement = std::unique_ptr<sqlite3_stmt, SqliteFinalizer>; |
21 |
|
|
|
22 |
|
|
/** |
23 |
|
|
* Description of a single column. |
24 |
|
|
*/ |
25 |
|
24 |
struct ColumnDescription |
26 |
|
|
{ |
27 |
|
|
string_type name; /**< Column name. */ |
28 |
|
|
string_type type; /**< Column SQLite data type ("INTEGER", "REAL", "TEXT" or "BLOB"). */ |
29 |
|
|
bool isNotNull; /**< Is true if the column has "NOT NULL" constraint. */ |
30 |
|
|
bool isPrimaryKey; /**< Is true if the column is primary key. */ |
31 |
|
|
}; |
32 |
|
|
|
33 |
|
|
using TableSchema = std::map< |
34 |
|
|
string_type, ColumnDescription, |
35 |
|
|
std::less<string_type>, RebindAlloc<ALLOC, std::pair<const string_type, ColumnDescription>>>; |
36 |
|
|
|
37 |
|
|
/** |
38 |
|
|
* Gets number of rows in the given SQLite table. |
39 |
|
|
* |
40 |
|
|
* \param connection Database connection to use. |
41 |
|
|
* \param attachedDbName Attached database name if table is relocated in different database. |
42 |
|
|
* \param tableName Name of the table to get column types of. |
43 |
|
|
* \param allocator Allocator to use for the query string composition. |
44 |
|
|
* |
45 |
|
|
* \return Number of rows. |
46 |
|
|
* |
47 |
|
|
* \throw SqliteException if the table does not exist. |
48 |
|
|
*/ |
49 |
|
7 |
static size_t getNumberOfTableRows(SqliteConnection& connection, StringView attachedDbName, |
50 |
|
|
StringView tableName, const ALLOC& allocator) |
51 |
|
|
{ |
52 |
✓✗ |
14 |
string_type sqlQuery(allocator); |
53 |
✓✗ |
7 |
sqlQuery += "SELECT count(*) FROM "; |
54 |
✓✓ |
7 |
if (!attachedDbName.empty()) |
55 |
|
|
{ |
56 |
✓✗ |
1 |
sqlQuery += attachedDbName; |
57 |
✓✗ |
1 |
sqlQuery += "."; |
58 |
|
|
} |
59 |
✓✗ |
7 |
sqlQuery += tableName; |
60 |
|
|
|
61 |
✓✓ |
14 |
Statement statement(connection.prepareStatement(sqlQuery)); |
62 |
✓✗ |
5 |
const int result = sqlite3_step(statement.get()); |
63 |
✓✓ |
5 |
if (result != SQLITE_ROW) |
64 |
|
|
{ |
65 |
✓✗✓✗
|
2 |
throw SqliteException("ValidationSqliteUtils.getNumberOfTableRows: sqlite3_step() failed: ") << |
66 |
|
2 |
SqliteErrorCode(result); |
67 |
|
|
} |
68 |
|
|
|
69 |
✓✗ |
8 |
return static_cast<size_t>(sqlite3_column_int64(statement.get(), 0)); |
70 |
|
|
} |
71 |
|
|
|
72 |
|
|
/** |
73 |
|
|
* Gets a map of column names to column description for given SQLite table. |
74 |
|
|
* |
75 |
|
|
* \param connection Database connection to use. |
76 |
|
|
* \param attachedDbName Attached database name if table is relocated in different database. |
77 |
|
|
* \param tableName Name of the table to get column types of. |
78 |
|
|
* \param tableSchema Schema to fill. |
79 |
|
|
* \param allocator Allocator to use for the query string composition. |
80 |
|
|
*/ |
81 |
|
5 |
static void getTableSchema(SqliteConnection& connection, StringView attachedDbName, |
82 |
|
|
StringView tableName, TableSchema& tableSchema, const ALLOC& allocator) |
83 |
|
|
{ |
84 |
✓✗ |
10 |
string_type sqlQuery(allocator); |
85 |
✓✗ |
5 |
sqlQuery += "PRAGMA "; |
86 |
✓✓ |
5 |
if (!attachedDbName.empty()) |
87 |
|
|
{ |
88 |
✓✗ |
1 |
sqlQuery += attachedDbName; |
89 |
✓✗ |
1 |
sqlQuery += "."; |
90 |
|
|
} |
91 |
✓✗ |
5 |
sqlQuery += "table_info("; |
92 |
✓✗ |
5 |
sqlQuery += tableName; |
93 |
✓✗ |
5 |
sqlQuery += ")"; |
94 |
|
|
|
95 |
✓✓ |
10 |
Statement statement(connection.prepareStatement(sqlQuery)); |
96 |
|
|
|
97 |
|
4 |
int result = SQLITE_OK; |
98 |
✓✗✓✓
|
20 |
while ((result = sqlite3_step(statement.get())) == SQLITE_ROW) |
99 |
|
|
{ |
100 |
✓✗ |
8 |
const char* columnName = reinterpret_cast<const char*>(sqlite3_column_text(statement.get(), 1)); |
101 |
✓✗ |
8 |
const char* columnType = reinterpret_cast<const char*>(sqlite3_column_text(statement.get(), 2)); |
102 |
✓✗✓✗ ✓✗✓✗
|
24 |
tableSchema.emplace(string_type(columnName, allocator), |
103 |
|
|
ColumnDescription |
104 |
|
|
{ |
105 |
|
|
string_type(columnName, allocator), |
106 |
|
|
string_type(columnType, allocator), |
107 |
✓✗ |
8 |
sqlite3_column_int(statement.get(), 3) != 0, // is not null |
108 |
✓✗ |
8 |
sqlite3_column_int(statement.get(), 5) != 0 // is primary key |
109 |
|
|
}); |
110 |
|
|
} |
111 |
|
|
|
112 |
✗✓ |
4 |
if (result != SQLITE_DONE) |
113 |
|
|
{ |
114 |
|
|
throw SqliteException("ValidationSqliteUtils.getTableSchema: sqlite3_step() failed: ") << |
115 |
|
|
SqliteErrorCode(result); |
116 |
|
|
} |
117 |
|
4 |
} |
118 |
|
|
|
119 |
|
|
/** |
120 |
|
|
* Check if the column is present in the given SQLite table. Note that this method also detect |
121 |
|
|
* hidden SQLite columns, which are not visible using standard PRAGMA table_info query. |
122 |
|
|
* |
123 |
|
|
* \param connection Database connection to use. |
124 |
|
|
* \param attachedDbName Attached database name if table is relocated in different database. |
125 |
|
|
* \param tableName Name of the table to get column types of. |
126 |
|
|
* \param columnName Name of the column to check. |
127 |
|
|
* \param allocator Allocator to use for the query string composition. |
128 |
|
|
* |
129 |
|
|
* \return Returns true if the column is present in the table, even if the column is hidden. Otherwise |
130 |
|
|
* returns false. |
131 |
|
|
*/ |
132 |
|
7 |
static bool isColumnInTable(SqliteConnection& connection, StringView attachedDbName, |
133 |
|
|
StringView tableName, StringView columnName, const ALLOC& allocator) |
134 |
|
|
{ |
135 |
|
|
// try select to check if hidden column exists |
136 |
✓✗ |
14 |
string_type sqlQuery(allocator); |
137 |
✓✗ |
7 |
sqlQuery += "SELECT "; |
138 |
✓✗ |
7 |
sqlQuery += columnName; |
139 |
✓✗ |
7 |
sqlQuery += " FROM "; |
140 |
✓✓ |
7 |
if (!attachedDbName.empty()) |
141 |
|
|
{ |
142 |
✓✗ |
1 |
sqlQuery += attachedDbName; |
143 |
✓✗ |
1 |
sqlQuery += "."; |
144 |
|
|
} |
145 |
✓✗ |
7 |
sqlQuery += tableName; |
146 |
✓✗ |
7 |
sqlQuery += " LIMIT 0"; |
147 |
|
|
|
148 |
|
|
try |
149 |
|
|
{ |
150 |
✓✓✗✓
|
14 |
Statement statement(connection.prepareStatement(sqlQuery)); |
151 |
✓✗ |
4 |
return sqlite3_step(statement.get()) == SQLITE_DONE; |
152 |
|
|
} |
153 |
✓✗ |
6 |
catch (const SqliteException&) |
154 |
|
|
{ |
155 |
|
3 |
return false; |
156 |
|
|
} |
157 |
|
|
} |
158 |
|
|
|
159 |
|
|
/** |
160 |
|
|
* Gets name of the given SQLite column type. |
161 |
|
|
* |
162 |
|
|
* \param columnType SQLite column type. |
163 |
|
|
* |
164 |
|
|
* \return SQLite column type name. |
165 |
|
|
*/ |
166 |
|
15 |
static const char* sqliteColumnTypeName(int columnType) |
167 |
|
|
{ |
168 |
✓✓✓✓ ✓ |
15 |
switch (columnType) |
169 |
|
|
{ |
170 |
|
|
case SQLITE_INTEGER: |
171 |
|
5 |
return "INTEGER"; |
172 |
|
|
case SQLITE_FLOAT: |
173 |
|
2 |
return "REAL"; |
174 |
|
|
case SQLITE_TEXT: |
175 |
|
2 |
return "TEXT"; |
176 |
|
|
case SQLITE_BLOB: |
177 |
|
2 |
return "BLOB"; |
178 |
|
|
default: |
179 |
|
4 |
return "NULL"; |
180 |
|
|
} |
181 |
|
|
} |
182 |
|
|
}; |
183 |
|
|
|
184 |
|
|
} // namespace zserio |
185 |
|
|
|
186 |
|
|
#endif // ZSERIO_VALIDATION_SQLITE_UTIL_H_INC |