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