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