1#include "connection.h"
2
3#include <base/dbg.h>
4#include <base/log.h>
5#include <base/mem.h>
6#include <base/str.h>
7
8#include <sqlite3.h>
9
10#include <algorithm>
11#include <atomic>
12
13class CSqliteConnection : public IDbConnection
14{
15public:
16 CSqliteConnection(const char *pFilename, bool Setup);
17 ~CSqliteConnection() override;
18 void Print(const char *pMode) override;
19
20 const char *BinaryCollate() const override { return "BINARY"; }
21 void ToUnixTimestamp(const char *pTimestamp, char *aBuf, unsigned int BufferSize) override;
22 const char *InsertTimestampAsUtc() const override { return "DATETIME(?, 'utc')"; }
23 const char *CollateNocase() const override { return "? COLLATE NOCASE"; }
24 const char *InsertIgnore() const override { return "INSERT OR IGNORE"; }
25 const char *Random() const override { return "RANDOM()"; }
26 // Since SQLite 3.23.0 true/false literals are recognized, but still cleaner to use 1/0, because:
27 // > For compatibility, if there exist columns named "true" or "false", then
28 // > the identifiers refer to the columns rather than Boolean constants.
29 const char *False() const override { return "0"; }
30 const char *True() const override { return "1"; }
31
32 bool Connect(char *pError, int ErrorSize) override;
33 void Disconnect() override;
34
35 bool PrepareStatement(const char *pStmt, char *pError, int ErrorSize) override;
36
37 void BindString(int Idx, const char *pString) override;
38 void BindBlob(int Idx, unsigned char *pBlob, int Size) override;
39 void BindInt(int Idx, int Value) override;
40 void BindInt64(int Idx, int64_t Value) override;
41 void BindFloat(int Idx, float Value) override;
42 void BindNull(int Idx) override;
43
44 void Print() override;
45 bool Step(bool *pEnd, char *pError, int ErrorSize) override;
46 bool ExecuteUpdate(int *pNumUpdated, char *pError, int ErrorSize) override;
47
48 bool IsNull(int Col) override;
49 float GetFloat(int Col) override;
50 int GetInt(int Col) override;
51 int64_t GetInt64(int Col) override;
52 void GetString(int Col, char *pBuffer, int BufferSize) override;
53 // passing a negative buffer size is undefined behavior
54 int GetBlob(int Col, unsigned char *pBuffer, int BufferSize) override;
55
56 bool AddPoints(const char *pPlayer, int Points, char *pError, int ErrorSize) override;
57
58 // fail safe
59 bool CreateFailsafeTables();
60
61private:
62 // copy of config vars
63 char m_aFilename[IO_MAX_PATH_LENGTH];
64 bool m_Setup;
65
66 sqlite3 *m_pDb;
67 sqlite3_stmt *m_pStmt;
68 bool m_Done; // no more rows available for Step
69 // returns false, if the query succeeded
70 bool Execute(const char *pQuery, char *pError, int ErrorSize);
71 // returns true on failure
72 bool ConnectImpl(char *pError, int ErrorSize);
73
74 // returns true if an error was formatted
75 bool FormatError(int Result, char *pError, int ErrorSize);
76 void AssertNoError(int Result);
77
78 std::atomic_bool m_InUse;
79};
80
81CSqliteConnection::CSqliteConnection(const char *pFilename, bool Setup) :
82 IDbConnection("record"),
83 m_Setup(Setup),
84 m_pDb(nullptr),
85 m_pStmt(nullptr),
86 m_Done(true),
87 m_InUse(false)
88{
89 str_copy(dst&: m_aFilename, src: pFilename);
90}
91
92CSqliteConnection::~CSqliteConnection()
93{
94 if(m_pStmt != nullptr)
95 sqlite3_finalize(pStmt: m_pStmt);
96 sqlite3_close(m_pDb);
97 m_pDb = nullptr;
98}
99
100void CSqliteConnection::Print(const char *pMode)
101{
102 log_info("server",
103 "SQLite-%s: DB: '%s'",
104 pMode, m_aFilename);
105}
106
107void CSqliteConnection::ToUnixTimestamp(const char *pTimestamp, char *aBuf, unsigned int BufferSize)
108{
109 str_format(buffer: aBuf, buffer_size: BufferSize, format: "strftime('%%s', %s)", pTimestamp);
110}
111
112bool CSqliteConnection::Connect(char *pError, int ErrorSize)
113{
114 if(m_InUse.exchange(i: true))
115 {
116 dbg_assert_failed("Tried connecting while the connection is in use");
117 }
118 if(!ConnectImpl(pError, ErrorSize))
119 {
120 m_InUse.store(i: false);
121 return false;
122 }
123 return true;
124}
125
126bool CSqliteConnection::ConnectImpl(char *pError, int ErrorSize)
127{
128 if(m_pDb != nullptr)
129 {
130 return true;
131 }
132
133 if(sqlite3_libversion_number() < 3025000)
134 {
135 dbg_msg(sys: "sql", fmt: "SQLite version %s is not supported, use at least version 3.25.0", sqlite3_libversion());
136 }
137
138 int Result = sqlite3_open(filename: m_aFilename, ppDb: &m_pDb);
139 if(Result != SQLITE_OK)
140 {
141 str_format(buffer: pError, buffer_size: ErrorSize, format: "Can't open sqlite database: '%s'", sqlite3_errmsg(m_pDb));
142 return false;
143 }
144
145 // wait for database to unlock so we don't have to handle SQLITE_BUSY errors
146 sqlite3_busy_timeout(m_pDb, ms: -1);
147
148 if(m_Setup)
149 {
150 if(!Execute(pQuery: "PRAGMA journal_mode=WAL", pError, ErrorSize))
151 return false;
152 char aBuf[1024];
153 FormatCreateRace(aBuf, BufferSize: sizeof(aBuf), /* Backup */ false);
154 if(!Execute(pQuery: aBuf, pError, ErrorSize))
155 return false;
156 FormatCreateTeamrace(aBuf, BufferSize: sizeof(aBuf), pIdType: "BLOB", /* Backup */ false);
157 if(!Execute(pQuery: aBuf, pError, ErrorSize))
158 return false;
159 FormatCreateMaps(aBuf, BufferSize: sizeof(aBuf));
160 if(!Execute(pQuery: aBuf, pError, ErrorSize))
161 return false;
162 FormatCreateSaves(aBuf, BufferSize: sizeof(aBuf), /* Backup */ false);
163 if(!Execute(pQuery: aBuf, pError, ErrorSize))
164 return false;
165 FormatCreatePoints(aBuf, BufferSize: sizeof(aBuf));
166 if(!Execute(pQuery: aBuf, pError, ErrorSize))
167 return false;
168
169 FormatCreateRace(aBuf, BufferSize: sizeof(aBuf), /* Backup */ true);
170 if(!Execute(pQuery: aBuf, pError, ErrorSize))
171 return false;
172 FormatCreateTeamrace(aBuf, BufferSize: sizeof(aBuf), pIdType: "BLOB", /* Backup */ true);
173 if(!Execute(pQuery: aBuf, pError, ErrorSize))
174 return false;
175 FormatCreateSaves(aBuf, BufferSize: sizeof(aBuf), /* Backup */ true);
176 if(!Execute(pQuery: aBuf, pError, ErrorSize))
177 return false;
178 m_Setup = false;
179 }
180 return true;
181}
182
183void CSqliteConnection::Disconnect()
184{
185 if(m_pStmt != nullptr)
186 sqlite3_finalize(pStmt: m_pStmt);
187 m_pStmt = nullptr;
188 m_InUse.store(i: false);
189}
190
191bool CSqliteConnection::PrepareStatement(const char *pStmt, char *pError, int ErrorSize)
192{
193 if(m_pStmt != nullptr)
194 sqlite3_finalize(pStmt: m_pStmt);
195 m_pStmt = nullptr;
196 int Result = sqlite3_prepare_v2(
197 db: m_pDb,
198 zSql: pStmt,
199 nByte: -1, // pStmt can be any length
200 ppStmt: &m_pStmt,
201 pzTail: nullptr);
202 if(FormatError(Result, pError, ErrorSize))
203 {
204 return false;
205 }
206 m_Done = false;
207 return true;
208}
209
210void CSqliteConnection::BindString(int Idx, const char *pString)
211{
212 int Result = sqlite3_bind_text(m_pStmt, Idx, pString, -1, nullptr);
213 AssertNoError(Result);
214 m_Done = false;
215}
216
217void CSqliteConnection::BindBlob(int Idx, unsigned char *pBlob, int Size)
218{
219 int Result = sqlite3_bind_blob(m_pStmt, Idx, pBlob, n: Size, nullptr);
220 AssertNoError(Result);
221 m_Done = false;
222}
223
224void CSqliteConnection::BindInt(int Idx, int Value)
225{
226 int Result = sqlite3_bind_int(m_pStmt, Idx, Value);
227 AssertNoError(Result);
228 m_Done = false;
229}
230
231void CSqliteConnection::BindInt64(int Idx, int64_t Value)
232{
233 int Result = sqlite3_bind_int64(m_pStmt, Idx, Value);
234 AssertNoError(Result);
235 m_Done = false;
236}
237
238void CSqliteConnection::BindFloat(int Idx, float Value)
239{
240 int Result = sqlite3_bind_double(m_pStmt, Idx, (double)Value);
241 AssertNoError(Result);
242 m_Done = false;
243}
244
245void CSqliteConnection::BindNull(int Idx)
246{
247 int Result = sqlite3_bind_null(m_pStmt, Idx);
248 AssertNoError(Result);
249 m_Done = false;
250}
251
252// Keep support for SQLite < 3.14 on older Linux distributions
253// MinGW does not support weak attribute: https://sourceware.org/bugzilla/show_bug.cgi?id=9687
254#if !defined(__MINGW32__)
255[[gnu::weak]] extern char *sqlite3_expanded_sql(sqlite3_stmt *pStmt); // NOLINT(readability-redundant-declaration)
256#endif
257
258void CSqliteConnection::Print()
259{
260 if(m_pStmt != nullptr
261#if !defined(__MINGW32__)
262 && sqlite3_expanded_sql != nullptr
263#endif
264 )
265 {
266 char *pExpandedStmt = sqlite3_expanded_sql(pStmt: m_pStmt);
267 dbg_msg(sys: "sql", fmt: "SQLite statement: %s", pExpandedStmt);
268 sqlite3_free(pExpandedStmt);
269 }
270}
271
272bool CSqliteConnection::Step(bool *pEnd, char *pError, int ErrorSize)
273{
274 if(m_Done)
275 {
276 *pEnd = true;
277 return true;
278 }
279 int Result = sqlite3_step(m_pStmt);
280 if(Result == SQLITE_ROW)
281 {
282 *pEnd = false;
283 return true;
284 }
285 else if(Result == SQLITE_DONE)
286 {
287 m_Done = true;
288 *pEnd = true;
289 return true;
290 }
291 else
292 {
293 if(FormatError(Result, pError, ErrorSize))
294 {
295 return false;
296 }
297 }
298 *pEnd = true;
299 return true;
300}
301
302bool CSqliteConnection::ExecuteUpdate(int *pNumUpdated, char *pError, int ErrorSize)
303{
304 bool End;
305 if(!Step(pEnd: &End, pError, ErrorSize))
306 {
307 return false;
308 }
309 *pNumUpdated = sqlite3_changes(m_pDb);
310 return true;
311}
312
313bool CSqliteConnection::IsNull(int Col)
314{
315 return sqlite3_column_type(m_pStmt, iCol: Col - 1) == SQLITE_NULL;
316}
317
318float CSqliteConnection::GetFloat(int Col)
319{
320 return (float)sqlite3_column_double(m_pStmt, iCol: Col - 1);
321}
322
323int CSqliteConnection::GetInt(int Col)
324{
325 return sqlite3_column_int(m_pStmt, iCol: Col - 1);
326}
327
328int64_t CSqliteConnection::GetInt64(int Col)
329{
330 return sqlite3_column_int64(m_pStmt, iCol: Col - 1);
331}
332
333void CSqliteConnection::GetString(int Col, char *pBuffer, int BufferSize)
334{
335 str_copy(dst: pBuffer, src: (const char *)sqlite3_column_text(m_pStmt, iCol: Col - 1), dst_size: BufferSize);
336}
337
338int CSqliteConnection::GetBlob(int Col, unsigned char *pBuffer, int BufferSize)
339{
340 int Size = sqlite3_column_bytes(m_pStmt, iCol: Col - 1);
341 Size = std::min(a: Size, b: BufferSize);
342 mem_copy(dest: pBuffer, source: sqlite3_column_blob(m_pStmt, iCol: Col - 1), size: Size);
343 return Size;
344}
345
346bool CSqliteConnection::Execute(const char *pQuery, char *pError, int ErrorSize)
347{
348 char *pErrorMsg;
349 int Result = sqlite3_exec(m_pDb, sql: pQuery, callback: nullptr, nullptr, errmsg: &pErrorMsg);
350 if(Result != SQLITE_OK)
351 {
352 str_format(buffer: pError, buffer_size: ErrorSize, format: "error executing query: '%s'", pErrorMsg);
353 sqlite3_free(pErrorMsg);
354 return false;
355 }
356 return true;
357}
358
359bool CSqliteConnection::FormatError(int Result, char *pError, int ErrorSize)
360{
361 if(Result != SQLITE_OK)
362 {
363 str_copy(dst: pError, src: sqlite3_errmsg(m_pDb), dst_size: ErrorSize);
364 return true;
365 }
366 return false;
367}
368
369void CSqliteConnection::AssertNoError(int Result)
370{
371 char aBuf[128];
372 if(FormatError(Result, pError: aBuf, ErrorSize: sizeof(aBuf)))
373 {
374 dbg_msg(sys: "sqlite", fmt: "unexpected sqlite error: %s", aBuf);
375 dbg_assert(0, "sqlite error");
376 }
377}
378
379bool CSqliteConnection::AddPoints(const char *pPlayer, int Points, char *pError, int ErrorSize)
380{
381 char aBuf[512];
382 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
383 format: "INSERT INTO %s_points(Name, Points) "
384 "VALUES (?, ?) "
385 "ON CONFLICT(Name) DO UPDATE SET Points=Points+?",
386 GetPrefix());
387 if(!PrepareStatement(pStmt: aBuf, pError, ErrorSize))
388 {
389 return false;
390 }
391 BindString(Idx: 1, pString: pPlayer);
392 BindInt(Idx: 2, Value: Points);
393 BindInt(Idx: 3, Value: Points);
394 bool End;
395 return Step(pEnd: &End, pError, ErrorSize);
396}
397
398std::unique_ptr<IDbConnection> CreateSqliteConnection(const char *pFilename, bool Setup)
399{
400 return std::make_unique<CSqliteConnection>(args&: pFilename, args&: Setup);
401}
402