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