1#include "scoreworker.h"
2
3#include <base/dbg.h>
4#include <base/log.h>
5#include <base/mem.h>
6#include <base/str.h>
7#include <base/time.h>
8
9#include <engine/server/databases/connection.h>
10#include <engine/server/databases/connection_pool.h>
11#include <engine/server/sql_string_helpers.h>
12#include <engine/shared/config.h>
13
14#include <cmath>
15
16// "6b407e81-8b77-3e04-a207-8da17f37d000"
17// "save-no-save-id@ddnet.tw"
18static const CUuid UUID_NO_SAVE_ID =
19 {.m_aData: {0x6b, 0x40, 0x7e, 0x81, 0x8b, 0x77, 0x3e, 0x04,
20 0xa2, 0x07, 0x8d, 0xa1, 0x7f, 0x37, 0xd0, 0x00}};
21
22CScorePlayerResult::CScorePlayerResult()
23{
24 SetVariant(Variant::DIRECT);
25}
26
27void CScorePlayerResult::SetVariant(Variant v)
28{
29 m_MessageKind = v;
30 switch(v)
31 {
32 case DIRECT:
33 case ALL:
34 for(auto &aMessage : m_Data.m_aaMessages)
35 aMessage[0] = 0;
36 break;
37 case BROADCAST:
38 m_Data.m_aBroadcast[0] = 0;
39 break;
40 case MAP_VOTE:
41 m_Data.m_MapVote.m_aMap[0] = '\0';
42 m_Data.m_MapVote.m_aReason[0] = '\0';
43 m_Data.m_MapVote.m_aServer[0] = '\0';
44 break;
45 case PLAYER_INFO:
46 m_Data.m_Info.m_Birthday = 0;
47 m_Data.m_Info.m_Time.reset();
48 for(float &TimeCp : m_Data.m_Info.m_aTimeCp)
49 TimeCp = 0;
50 break;
51 case PLAYER_TIMECP:
52 m_Data.m_Info.m_aRequestedPlayer[0] = '\0';
53 m_Data.m_Info.m_Time.reset();
54 for(float &TimeCp : m_Data.m_Info.m_aTimeCp)
55 TimeCp = 0;
56 break;
57 }
58}
59
60CTeamrank::CTeamrank() :
61 m_NumNames(0)
62{
63 for(auto &aName : m_aaNames)
64 aName[0] = '\0';
65 mem_zero(block: &m_TeamId.m_aData, size: sizeof(m_TeamId));
66}
67
68bool CTeamrank::NextSqlResult(IDbConnection *pSqlServer, bool *pEnd, char *pError, int ErrorSize)
69{
70 pSqlServer->GetBlob(Col: 1, pBuffer: m_TeamId.m_aData, BufferSize: sizeof(m_TeamId.m_aData));
71 pSqlServer->GetString(Col: 2, pBuffer: m_aaNames[0], BufferSize: sizeof(m_aaNames[0]));
72 m_NumNames = 1;
73 bool End = false;
74 while(pSqlServer->Step(pEnd: &End, pError, ErrorSize) && !End)
75 {
76 CUuid TeamId;
77 pSqlServer->GetBlob(Col: 1, pBuffer: TeamId.m_aData, BufferSize: sizeof(TeamId.m_aData));
78 if(m_TeamId != TeamId)
79 {
80 *pEnd = false;
81 return true;
82 }
83 pSqlServer->GetString(Col: 2, pBuffer: m_aaNames[m_NumNames], BufferSize: sizeof(m_aaNames[m_NumNames]));
84 m_NumNames++;
85 }
86 if(!End)
87 {
88 return false;
89 }
90 *pEnd = true;
91 return true;
92}
93
94bool CTeamrank::SamePlayers(const std::vector<std::string> *pvSortedNames)
95{
96 if(pvSortedNames->size() != m_NumNames)
97 return false;
98 for(unsigned int i = 0; i < m_NumNames; i++)
99 {
100 if(str_comp(a: pvSortedNames->at(n: i).c_str(), b: m_aaNames[i]) != 0)
101 return false;
102 }
103 return true;
104}
105
106bool CTeamrank::GetSqlTop5Team(IDbConnection *pSqlServer, bool *pEnd, char *pError, int ErrorSize, char (*paMessages)[512], int *Line, int Count)
107{
108 char aTime[32];
109 int StartLine = *Line;
110 for(*Line = StartLine; *Line < StartLine + Count; (*Line)++)
111 {
112 bool Last = false;
113 float Time = pSqlServer->GetFloat(Col: 2);
114 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aTime, buffer_size: sizeof(aTime));
115 int Rank = pSqlServer->GetInt(Col: 3);
116 int TeamSize = pSqlServer->GetInt(Col: 4);
117
118 char aNames[2300] = {0};
119 for(int i = 0; i < TeamSize; i++)
120 {
121 char aName[MAX_NAME_LENGTH];
122 pSqlServer->GetString(Col: 1, pBuffer: aName, BufferSize: sizeof(aName));
123 str_append(dst&: aNames, src: aName);
124 if(i < TeamSize - 2)
125 str_append(dst&: aNames, src: ", ");
126 else if(i == TeamSize - 2)
127 str_append(dst&: aNames, src: " & ");
128 if(!pSqlServer->Step(pEnd: &Last, pError, ErrorSize))
129 {
130 return false;
131 }
132 if(Last)
133 {
134 break;
135 }
136 }
137 str_format(buffer: paMessages[*Line], buffer_size: sizeof(paMessages[*Line]), format: "%d. %s Team Time: %s",
138 Rank, aNames, aTime);
139 if(Last)
140 {
141 (*Line)++;
142 break;
143 }
144 }
145 return true;
146}
147
148bool CScoreWorker::LoadBestTime(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
149{
150 const auto *pData = dynamic_cast<const CSqlLoadBestTimeRequest *>(pGameData);
151 auto *pResult = dynamic_cast<CScoreLoadBestTimeResult *>(pGameData->m_pResult.get());
152
153 char aBuf[512];
154 // get the best time
155 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
156 format: "SELECT Time FROM %s_race WHERE Map=? ORDER BY `Time` ASC LIMIT 1",
157 pSqlServer->GetPrefix());
158 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
159 {
160 return false;
161 }
162 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
163
164 bool End;
165 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
166 {
167 return false;
168 }
169 if(!End)
170 {
171 pResult->m_CurrentRecord = pSqlServer->GetFloat(Col: 1);
172 }
173
174 return true;
175}
176
177// update stuff
178bool CScoreWorker::LoadPlayerData(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
179{
180 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
181 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
182 pResult->SetVariant(CScorePlayerResult::PLAYER_INFO);
183
184 char aBuf[1024];
185 // get best race time
186 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
187 format: "SELECT"
188 " (SELECT Time FROM %s_race WHERE Map = ? AND Name = ? ORDER BY Time ASC LIMIT 1) AS minTime, "
189 " cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, cp14, "
190 " cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, "
191 " (cp1 + cp2 + cp3 + cp4 + cp5 + cp6 + cp7 + cp8 + cp9 + cp10 + cp11 + cp12 + cp13 + cp14 + "
192 " cp15 + cp16 + cp17 + cp18 + cp19 + cp20 + cp21 + cp22 + cp23 + cp24 + cp25 > 0) AS hasCP, Time "
193 "FROM %s_race "
194 "WHERE Map = ? AND Name = ? "
195 "ORDER BY hasCP DESC, Time ASC "
196 "LIMIT 1",
197 pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
198 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
199 {
200 return false;
201 }
202
203 const char *pPlayer = pData->m_aName[0] != '\0' ? pData->m_aName : pData->m_aRequestingPlayer;
204 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
205 pSqlServer->BindString(Idx: 2, pString: pData->m_aRequestingPlayer);
206 pSqlServer->BindString(Idx: 3, pString: pData->m_aMap);
207 pSqlServer->BindString(Idx: 4, pString: pPlayer);
208
209 bool End;
210 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
211 {
212 return false;
213 }
214 if(!End)
215 {
216 if(!pSqlServer->IsNull(Col: 1))
217 {
218 // get the best time
219 float Time = pSqlServer->GetFloat(Col: 1);
220 pResult->m_Data.m_Info.m_Time = Time;
221 }
222
223 for(int i = 0; i < NUM_CHECKPOINTS; i++)
224 {
225 pResult->m_Data.m_Info.m_aTimeCp[i] = pSqlServer->GetFloat(Col: i + 2);
226 }
227 }
228
229 // birthday check
230 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
231 format: "SELECT CURRENT_TIMESTAMP AS Current, MIN(Timestamp) AS Stamp "
232 "FROM %s_race "
233 "WHERE Name = ?",
234 pSqlServer->GetPrefix());
235 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
236 {
237 return false;
238 }
239 pSqlServer->BindString(Idx: 1, pString: pData->m_aRequestingPlayer);
240
241 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
242 {
243 return false;
244 }
245 if(!End && !pSqlServer->IsNull(Col: 2))
246 {
247 char aCurrent[TIMESTAMP_STR_LENGTH];
248 pSqlServer->GetString(Col: 1, pBuffer: aCurrent, BufferSize: sizeof(aCurrent));
249 char aStamp[TIMESTAMP_STR_LENGTH];
250 pSqlServer->GetString(Col: 2, pBuffer: aStamp, BufferSize: sizeof(aStamp));
251 int CurrentYear, CurrentMonth, CurrentDay;
252 int StampYear, StampMonth, StampDay;
253 if(sscanf(s: aCurrent, format: "%d-%d-%d", &CurrentYear, &CurrentMonth, &CurrentDay) == 3 && sscanf(s: aStamp, format: "%d-%d-%d", &StampYear, &StampMonth, &StampDay) == 3 && CurrentMonth == StampMonth && CurrentDay == StampDay)
254 pResult->m_Data.m_Info.m_Birthday = CurrentYear - StampYear;
255 }
256 return true;
257}
258
259bool CScoreWorker::LoadPlayerTimeCp(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
260{
261 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
262 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
263 auto *paMessages = pResult->m_Data.m_aaMessages;
264
265 char aBuf[1024];
266 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
267 format: "SELECT"
268 " Time, cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, "
269 " cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25 "
270 "FROM %s_race "
271 "WHERE Map = ? AND Name = ? AND "
272 " (cp1 + cp2 + cp3 + cp4 + cp5 + cp6 + cp7 + cp8 + cp9 + cp10 + cp11 + cp12 + cp13 + cp14 + "
273 " cp15 + cp16 + cp17 + cp18 + cp19 + cp20 + cp21 + cp22 + cp23 + cp24 + cp25) > 0 "
274 "ORDER BY Time ASC "
275 "LIMIT 1",
276 pSqlServer->GetPrefix());
277 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
278 {
279 return false;
280 }
281
282 const char *pPlayer = pData->m_aName[0] != '\0' ? pData->m_aName : pData->m_aRequestingPlayer;
283 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
284 pSqlServer->BindString(Idx: 2, pString: pPlayer);
285
286 bool End;
287 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
288 {
289 return false;
290 }
291 if(!End)
292 {
293 pResult->SetVariant(CScorePlayerResult::PLAYER_TIMECP);
294 pResult->m_Data.m_Info.m_Time = pSqlServer->GetFloat(Col: 1);
295 for(int i = 0; i < NUM_CHECKPOINTS; i++)
296 {
297 pResult->m_Data.m_Info.m_aTimeCp[i] = pSqlServer->GetFloat(Col: i + 2);
298 }
299 str_copy(dst&: pResult->m_Data.m_Info.m_aRequestedPlayer, src: pPlayer);
300 }
301 else
302 {
303 pResult->SetVariant(CScorePlayerResult::DIRECT);
304 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]), format: "'%s' has no checkpoint times available", pPlayer);
305 }
306 return true;
307}
308
309bool CScoreWorker::MapVote(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
310{
311 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
312 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
313 auto *paMessages = pResult->m_Data.m_aaMessages;
314
315 char aFuzzyMap[128];
316 str_copy(dst&: aFuzzyMap, src: pData->m_aName);
317 sqlstr::FuzzyString(pString: aFuzzyMap, Size: sizeof(aFuzzyMap));
318
319 char aMapPrefix[128];
320 str_copy(dst&: aMapPrefix, src: pData->m_aName);
321 str_append(dst&: aMapPrefix, src: "%");
322
323 char aBuf[768];
324 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
325 format: "SELECT Map, Server "
326 "FROM %s_maps "
327 "WHERE Map LIKE %s "
328 "ORDER BY "
329 " CASE WHEN LOWER(Map) = LOWER(?) THEN 0 ELSE 1 END, "
330 " CASE WHEN Map LIKE ? THEN 0 ELSE 1 END, "
331 " LENGTH(Map), Map "
332 "LIMIT 1",
333 pSqlServer->GetPrefix(), pSqlServer->CollateNocase());
334 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
335 {
336 return false;
337 }
338 pSqlServer->BindString(Idx: 1, pString: aFuzzyMap);
339 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
340 pSqlServer->BindString(Idx: 3, pString: aMapPrefix);
341
342 bool End;
343 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
344 {
345 return false;
346 }
347 if(!End)
348 {
349 pResult->SetVariant(CScorePlayerResult::MAP_VOTE);
350 auto *pMapVote = &pResult->m_Data.m_MapVote;
351 pSqlServer->GetString(Col: 1, pBuffer: pMapVote->m_aMap, BufferSize: sizeof(pMapVote->m_aMap));
352 pSqlServer->GetString(Col: 2, pBuffer: pMapVote->m_aServer, BufferSize: sizeof(pMapVote->m_aServer));
353 str_copy(dst&: pMapVote->m_aReason, src: "/map");
354
355 for(char *p = pMapVote->m_aServer; *p; p++) // lower case server
356 *p = tolower(c: *p);
357 }
358 else
359 {
360 pResult->SetVariant(CScorePlayerResult::DIRECT);
361 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]),
362 format: "No map like \"%s\" found. "
363 "Try adding a '%%' at the start if you don't know the first character. "
364 "Example: /map %%castle for \"Out of Castle\"",
365 pData->m_aName);
366 }
367 return true;
368}
369
370bool CScoreWorker::MapInfo(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
371{
372 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
373 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
374
375 char aFuzzyMap[128];
376 str_copy(dst&: aFuzzyMap, src: pData->m_aName);
377 sqlstr::FuzzyString(pString: aFuzzyMap, Size: sizeof(aFuzzyMap));
378
379 char aMapPrefix[128];
380 str_copy(dst&: aMapPrefix, src: pData->m_aName);
381 str_append(dst&: aMapPrefix, src: "%");
382
383 char aCurrentTimestamp[512];
384 pSqlServer->ToUnixTimestamp(pTimestamp: "CURRENT_TIMESTAMP", aBuf: aCurrentTimestamp, BufferSize: sizeof(aCurrentTimestamp));
385 char aTimestamp[512];
386 pSqlServer->ToUnixTimestamp(pTimestamp: "l.Timestamp", aBuf: aTimestamp, BufferSize: sizeof(aTimestamp));
387
388 char aBuf[4096];
389 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
390 format: "SELECT l.Map, l.Server, Mapper, Points, Stars, "
391 " (SELECT COUNT(Name) FROM %s_race WHERE Map = l.Map) AS Finishes, "
392 " (SELECT COUNT(DISTINCT Name) FROM %s_race WHERE Map = l.Map) AS Finishers, "
393 " %s AS Stamp, "
394 " %s-%s AS Ago, "
395 " (SELECT MIN(Time) FROM %s_race WHERE Map = l.Map AND Name = ?) AS OwnTime "
396 "FROM ("
397 " SELECT * FROM %s_maps "
398 " WHERE Map LIKE %s "
399 " ORDER BY "
400 " CASE WHEN LOWER(Map) = LOWER(?) THEN 0 ELSE 1 END, "
401 " CASE WHEN Map LIKE ? THEN 0 ELSE 1 END, "
402 " LENGTH(Map), "
403 " Map "
404 " LIMIT 1"
405 ") as l",
406 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(),
407 aTimestamp, aCurrentTimestamp, aTimestamp,
408 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(),
409 pSqlServer->CollateNocase());
410 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
411 {
412 return false;
413 }
414 pSqlServer->BindString(Idx: 1, pString: pData->m_aRequestingPlayer);
415 pSqlServer->BindString(Idx: 2, pString: aFuzzyMap);
416 pSqlServer->BindString(Idx: 3, pString: pData->m_aName);
417 pSqlServer->BindString(Idx: 4, pString: aMapPrefix);
418
419 bool End;
420 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
421 {
422 return false;
423 }
424 if(!End)
425 {
426 char aMap[MAX_MAP_LENGTH];
427 pSqlServer->GetString(Col: 1, pBuffer: aMap, BufferSize: sizeof(aMap));
428 char aServer[32];
429 pSqlServer->GetString(Col: 2, pBuffer: aServer, BufferSize: sizeof(aServer));
430 char aMapper[128];
431 pSqlServer->GetString(Col: 3, pBuffer: aMapper, BufferSize: sizeof(aMapper));
432 int Points = pSqlServer->GetInt(Col: 4);
433 int Stars = pSqlServer->GetInt(Col: 5);
434 int Finishes = pSqlServer->GetInt(Col: 6);
435 int Finishers = pSqlServer->GetInt(Col: 7);
436 int Stamp = pSqlServer->GetInt(Col: 8);
437 int Ago = pSqlServer->GetInt(Col: 9);
438 float OwnTime = pSqlServer->GetOptionalFloat(Col: 10).value_or(u: -1.0f);
439
440 float Median = -1.0f;
441 if(Finishes > 0)
442 {
443 char aMedianBuf[256];
444 str_format(buffer: aMedianBuf, buffer_size: sizeof(aMedianBuf),
445 format: "SELECT AVG(Time) FROM ("
446 " SELECT Time FROM %s_race WHERE Map = ? ORDER BY Time LIMIT %d OFFSET %d"
447 ") AS m",
448 pSqlServer->GetPrefix(), 2 - (Finishes % 2), (Finishes - 1) / 2);
449 if(!pSqlServer->PrepareStatement(pStmt: aMedianBuf, pError, ErrorSize))
450 {
451 return false;
452 }
453 pSqlServer->BindString(Idx: 1, pString: aMap);
454 bool MedianEnd;
455 if(!pSqlServer->Step(pEnd: &MedianEnd, pError, ErrorSize))
456 {
457 return false;
458 }
459 if(!MedianEnd)
460 {
461 Median = pSqlServer->GetOptionalFloat(Col: 1).value_or(u: -1.0f);
462 }
463 }
464
465 char aAgoString[40] = "\0";
466 char aReleasedString[60] = "\0";
467 if(Stamp != 0)
468 {
469 sqlstr::AgoTimeToString(AgoTime: Ago, pAgoString: aAgoString, Size: sizeof(aAgoString));
470 str_format(buffer: aReleasedString, buffer_size: sizeof(aReleasedString), format: ", released %s ago", aAgoString);
471 }
472
473 char aMedianString[60] = "\0";
474 if(Median > 0)
475 {
476 str_time(centisecs: (int64_t)Median * 100, format: ETimeFormat::HOURS, buffer: aBuf, buffer_size: sizeof(aBuf));
477 str_format(buffer: aMedianString, buffer_size: sizeof(aMedianString), format: " in %s median", aBuf);
478 }
479
480 char aStars[20];
481 switch(Stars)
482 {
483 case 0: str_copy(dst&: aStars, src: "✰✰✰✰✰"); break;
484 case 1: str_copy(dst&: aStars, src: "★✰✰✰✰"); break;
485 case 2: str_copy(dst&: aStars, src: "★★✰✰✰"); break;
486 case 3: str_copy(dst&: aStars, src: "★★★✰✰"); break;
487 case 4: str_copy(dst&: aStars, src: "★★★★✰"); break;
488 case 5: str_copy(dst&: aStars, src: "★★★★★"); break;
489 default: aStars[0] = '\0';
490 }
491
492 char aOwnFinishesString[40] = "\0";
493 if(OwnTime > 0)
494 {
495 str_time_float(secs: OwnTime, format: ETimeFormat::HOURS_CENTISECS, buffer: aBuf, buffer_size: sizeof(aBuf));
496 str_format(buffer: aOwnFinishesString, buffer_size: sizeof(aOwnFinishesString),
497 format: ", your time: %s", aBuf);
498 }
499
500 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
501 format: "\"%s\" by %s on %s, %s, %d %s%s, %d %s by %d %s%s%s",
502 aMap, aMapper, aServer, aStars,
503 Points, Points == 1 ? "point" : "points",
504 aReleasedString,
505 Finishes, Finishes == 1 ? "finish" : "finishes",
506 Finishers, Finishers == 1 ? "tee" : "tees",
507 aMedianString, aOwnFinishesString);
508 }
509 else
510 {
511 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
512 format: "No map like \"%s\" found.", pData->m_aName);
513 }
514 return true;
515}
516
517bool CScoreWorker::SaveScore(IDbConnection *pSqlServer, const ISqlData *pGameData, Write w, char *pError, int ErrorSize)
518{
519 const auto *pData = dynamic_cast<const CSqlScoreData *>(pGameData);
520 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
521 auto *paMessages = pResult->m_Data.m_aaMessages;
522
523 char aBuf[1024];
524
525 if(w == Write::NORMAL_SUCCEEDED)
526 {
527 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
528 format: "DELETE FROM %s_race_backup WHERE GameId=? AND Name=? AND Timestamp=%s",
529 pSqlServer->GetPrefix(), pSqlServer->InsertTimestampAsUtc());
530 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
531 {
532 return false;
533 }
534 pSqlServer->BindString(Idx: 1, pString: pData->m_aGameUuid);
535 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
536 pSqlServer->BindString(Idx: 3, pString: pData->m_aTimestamp);
537 pSqlServer->Print();
538 int NumDeleted;
539 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumDeleted, pError, ErrorSize))
540 {
541 return false;
542 }
543 if(NumDeleted == 0)
544 {
545 log_warn("sql", "Rank got moved out of backup database, will show up as duplicate rank in MySQL");
546 }
547 return true;
548 }
549 if(w == Write::NORMAL_FAILED)
550 {
551 int NumUpdated;
552 // move to non-tmp table succeeded. delete from backup again
553 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
554 format: "INSERT INTO %s_race SELECT * FROM %s_race_backup WHERE GameId=? AND Name=? AND Timestamp=%s",
555 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pSqlServer->InsertTimestampAsUtc());
556 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
557 {
558 return false;
559 }
560 pSqlServer->BindString(Idx: 1, pString: pData->m_aGameUuid);
561 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
562 pSqlServer->BindString(Idx: 3, pString: pData->m_aTimestamp);
563 pSqlServer->Print();
564 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumUpdated, pError, ErrorSize))
565 {
566 return false;
567 }
568
569 // move to non-tmp table succeeded. delete from backup again
570 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
571 format: "DELETE FROM %s_race_backup WHERE GameId=? AND Name=? AND Timestamp=%s",
572 pSqlServer->GetPrefix(), pSqlServer->InsertTimestampAsUtc());
573 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
574 {
575 return false;
576 }
577 pSqlServer->BindString(Idx: 1, pString: pData->m_aGameUuid);
578 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
579 pSqlServer->BindString(Idx: 3, pString: pData->m_aTimestamp);
580 pSqlServer->Print();
581 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumUpdated, pError, ErrorSize))
582 {
583 return false;
584 }
585 if(NumUpdated == 0)
586 {
587 log_warn("sql", "Rank got moved out of backup database, will show up as duplicate rank in MySQL");
588 }
589 return true;
590 }
591
592 if(w == Write::NORMAL)
593 {
594 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
595 format: "SELECT COUNT(*) AS NumFinished FROM %s_race WHERE Map=? AND Name=? ORDER BY time ASC LIMIT 1",
596 pSqlServer->GetPrefix());
597 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
598 {
599 return false;
600 }
601 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
602 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
603
604 bool End;
605 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
606 {
607 return false;
608 }
609 int NumFinished = pSqlServer->GetInt(Col: 1);
610 if(NumFinished == 0)
611 {
612 str_format(buffer: aBuf, buffer_size: sizeof(aBuf), format: "SELECT Points FROM %s_maps WHERE Map=?", pSqlServer->GetPrefix());
613 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
614 {
615 return false;
616 }
617 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
618
619 bool End2;
620 if(!pSqlServer->Step(pEnd: &End2, pError, ErrorSize))
621 {
622 return false;
623 }
624 if(!End2)
625 {
626 int Points = pSqlServer->GetInt(Col: 1);
627 if(!pSqlServer->AddPoints(pPlayer: pData->m_aName, Points, pError, ErrorSize))
628 {
629 return false;
630 }
631 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]),
632 format: "You earned %d point%s for finishing this map!",
633 Points, Points == 1 ? "" : "s");
634 }
635 }
636 }
637
638 // save score. Can't fail, because no UNIQUE/PRIMARY KEY constrain is defined.
639 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
640 format: "%s INTO %s_race%s("
641 " Map, Name, Timestamp, Time, Server, "
642 " cp1, cp2, cp3, cp4, cp5, cp6, cp7, cp8, cp9, cp10, cp11, cp12, cp13, "
643 " cp14, cp15, cp16, cp17, cp18, cp19, cp20, cp21, cp22, cp23, cp24, cp25, "
644 " GameId, DDNet7) "
645 "VALUES (?, ?, %s, %.2f, ?, "
646 " %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, "
647 " %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, "
648 " %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, %.2f, "
649 " ?, %s)",
650 pSqlServer->InsertIgnore(), pSqlServer->GetPrefix(),
651 w == Write::NORMAL ? "" : "_backup",
652 pSqlServer->InsertTimestampAsUtc(), pData->m_Time,
653 pData->m_aCurrentTimeCp[0], pData->m_aCurrentTimeCp[1], pData->m_aCurrentTimeCp[2],
654 pData->m_aCurrentTimeCp[3], pData->m_aCurrentTimeCp[4], pData->m_aCurrentTimeCp[5],
655 pData->m_aCurrentTimeCp[6], pData->m_aCurrentTimeCp[7], pData->m_aCurrentTimeCp[8],
656 pData->m_aCurrentTimeCp[9], pData->m_aCurrentTimeCp[10], pData->m_aCurrentTimeCp[11],
657 pData->m_aCurrentTimeCp[12], pData->m_aCurrentTimeCp[13], pData->m_aCurrentTimeCp[14],
658 pData->m_aCurrentTimeCp[15], pData->m_aCurrentTimeCp[16], pData->m_aCurrentTimeCp[17],
659 pData->m_aCurrentTimeCp[18], pData->m_aCurrentTimeCp[19], pData->m_aCurrentTimeCp[20],
660 pData->m_aCurrentTimeCp[21], pData->m_aCurrentTimeCp[22], pData->m_aCurrentTimeCp[23],
661 pData->m_aCurrentTimeCp[24], pSqlServer->False());
662 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
663 {
664 return false;
665 }
666 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
667 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
668 pSqlServer->BindString(Idx: 3, pString: pData->m_aTimestamp);
669 pSqlServer->BindString(Idx: 4, pString: g_Config.m_SvSqlServerName);
670 pSqlServer->BindString(Idx: 5, pString: pData->m_aGameUuid);
671 pSqlServer->Print();
672 int NumInserted;
673 return pSqlServer->ExecuteUpdate(pNumUpdated: &NumInserted, pError, ErrorSize);
674}
675
676bool CScoreWorker::SaveTeamScore(IDbConnection *pSqlServer, const ISqlData *pGameData, Write w, char *pError, int ErrorSize)
677{
678 const auto *pData = dynamic_cast<const CSqlTeamScoreData *>(pGameData);
679
680 char aBuf[512];
681
682 if(w == Write::NORMAL_SUCCEEDED)
683 {
684 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
685 format: "DELETE FROM %s_teamrace_backup WHERE Id=?",
686 pSqlServer->GetPrefix());
687 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
688 {
689 return false;
690 }
691
692 // copy uuid, because mysql BindBlob doesn't support const buffers
693 CUuid TeamrankId = pData->m_TeamrankUuid;
694 pSqlServer->BindBlob(Idx: 1, pBlob: TeamrankId.m_aData, Size: sizeof(TeamrankId.m_aData));
695 pSqlServer->Print();
696 int NumDeleted;
697 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumDeleted, pError, ErrorSize))
698 {
699 return false;
700 }
701 if(NumDeleted == 0)
702 {
703 log_warn("sql", "Teamrank got moved out of backup database, will show up as duplicate teamrank in MySQL");
704 }
705 return true;
706 }
707 if(w == Write::NORMAL_FAILED)
708 {
709 int NumInserted;
710 CUuid TeamrankId = pData->m_TeamrankUuid;
711
712 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
713 format: "INSERT INTO %s_teamrace SELECT * FROM %s_teamrace_backup WHERE Id=?",
714 pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
715 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
716 {
717 return false;
718 }
719 pSqlServer->BindBlob(Idx: 1, pBlob: TeamrankId.m_aData, Size: sizeof(TeamrankId.m_aData));
720 pSqlServer->Print();
721 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumInserted, pError, ErrorSize))
722 {
723 return false;
724 }
725
726 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
727 format: "DELETE FROM %s_teamrace_backup WHERE Id=?",
728 pSqlServer->GetPrefix());
729 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
730 {
731 return false;
732 }
733 pSqlServer->BindBlob(Idx: 1, pBlob: TeamrankId.m_aData, Size: sizeof(TeamrankId.m_aData));
734 pSqlServer->Print();
735 return pSqlServer->ExecuteUpdate(pNumUpdated: &NumInserted, pError, ErrorSize);
736 }
737
738 if(w == Write::NORMAL)
739 {
740 // get the names sorted in a tab separated string
741 std::vector<std::string> vNames;
742 vNames.reserve(n: pData->m_Size);
743 for(unsigned int i = 0; i < pData->m_Size; i++)
744 vNames.emplace_back(args: pData->m_aaNames[i]);
745
746 std::sort(first: vNames.begin(), last: vNames.end());
747 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
748 format: "SELECT l.Id, Name, Time "
749 "FROM (" // preselect teams with first name in team
750 " SELECT ID "
751 " FROM %s_teamrace "
752 " WHERE Map = ? AND Name = ? AND DDNet7 = %s"
753 ") as l INNER JOIN %s_teamrace AS r ON l.Id = r.Id "
754 "ORDER BY l.Id, Name COLLATE %s",
755 pSqlServer->GetPrefix(), pSqlServer->False(), pSqlServer->GetPrefix(), pSqlServer->BinaryCollate());
756 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
757 {
758 return false;
759 }
760 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
761 pSqlServer->BindString(Idx: 2, pString: pData->m_aaNames[0]);
762
763 bool FoundTeam = false;
764 float Time;
765 CTeamrank Teamrank;
766 bool End;
767 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
768 {
769 return false;
770 }
771 if(!End)
772 {
773 bool SearchTeamEnd = false;
774 while(!SearchTeamEnd)
775 {
776 Time = pSqlServer->GetFloat(Col: 3);
777 if(!Teamrank.NextSqlResult(pSqlServer, pEnd: &SearchTeamEnd, pError, ErrorSize))
778 {
779 return false;
780 }
781 if(Teamrank.SamePlayers(pvSortedNames: &vNames))
782 {
783 FoundTeam = true;
784 break;
785 }
786 }
787 }
788 if(FoundTeam)
789 {
790 dbg_msg(sys: "sql", fmt: "found team rank from same team (old time: %f, new time: %f)", Time, pData->m_Time);
791 if(pData->m_Time < Time)
792 {
793 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
794 format: "UPDATE %s_teamrace SET Time=%.2f, Timestamp=%s, DDNet7=%s, GameId=? WHERE Id = ?",
795 pSqlServer->GetPrefix(), pData->m_Time, pSqlServer->InsertTimestampAsUtc(), pSqlServer->False());
796 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
797 {
798 return false;
799 }
800 pSqlServer->BindString(Idx: 1, pString: pData->m_aTimestamp);
801 pSqlServer->BindString(Idx: 2, pString: pData->m_aGameUuid);
802 pSqlServer->BindBlob(Idx: 3, pBlob: Teamrank.m_TeamId.m_aData, Size: sizeof(Teamrank.m_TeamId.m_aData));
803 pSqlServer->Print();
804 int NumUpdated;
805 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumUpdated, pError, ErrorSize))
806 {
807 return false;
808 }
809 // return error if we didn't update any rows
810 return NumUpdated != 0;
811 }
812 return true;
813 }
814 }
815
816 for(unsigned int i = 0; i < pData->m_Size; i++)
817 {
818 // if no entry found... create a new one
819 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
820 format: "%s INTO %s_teamrace%s(Map, Name, Timestamp, Time, Id, GameId, DDNet7) "
821 "VALUES (?, ?, %s, %.2f, ?, ?, %s)",
822 pSqlServer->InsertIgnore(), pSqlServer->GetPrefix(),
823 w == Write::NORMAL ? "" : "_backup",
824 pSqlServer->InsertTimestampAsUtc(), pData->m_Time, pSqlServer->False());
825 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
826 {
827 return false;
828 }
829 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
830 pSqlServer->BindString(Idx: 2, pString: pData->m_aaNames[i]);
831 pSqlServer->BindString(Idx: 3, pString: pData->m_aTimestamp);
832 // copy uuid, because mysql BindBlob doesn't support const buffers
833 CUuid TeamrankId = pData->m_TeamrankUuid;
834 pSqlServer->BindBlob(Idx: 4, pBlob: TeamrankId.m_aData, Size: sizeof(TeamrankId.m_aData));
835 pSqlServer->BindString(Idx: 5, pString: pData->m_aGameUuid);
836 pSqlServer->Print();
837 int NumInserted;
838 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumInserted, pError, ErrorSize))
839 {
840 return false;
841 }
842 }
843 return true;
844}
845
846bool CScoreWorker::ShowRank(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
847{
848 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
849 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
850
851 char aServerLike[16];
852 str_format(buffer: aServerLike, buffer_size: sizeof(aServerLike), format: "%%%s%%", pData->m_aServer);
853
854 // Counting how many players are faster than the requesting player is cheaper
855 // than ranking every player with a window function. Best holds the best time
856 // of every player, Own the best time of the requesting player.
857 char aBuf[1024];
858 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
859 format: "SELECT SUM(Best.Time < Own.Time) + 1 AS Ranking, Own.Time AS Time, "
860 " CASE WHEN COUNT(*) > 1 THEN SUM(Best.Time < Own.Time) * 1.0 / (COUNT(*) - 1) ELSE 0 END AS PercentRank "
861 "FROM ("
862 " SELECT MIN(Time) AS Time FROM %s_race WHERE Map = ? AND Server LIKE ? GROUP BY Name"
863 ") AS Best "
864 "CROSS JOIN ("
865 " SELECT MIN(Time) AS Time FROM %s_race WHERE Map = ? AND Server LIKE ? AND Name = ?"
866 ") AS Own "
867 "WHERE Own.Time IS NOT NULL "
868 "GROUP BY Own.Time",
869 pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
870
871 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
872 {
873 return false;
874 }
875 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
876 pSqlServer->BindString(Idx: 2, pString: aServerLike);
877 pSqlServer->BindString(Idx: 3, pString: pData->m_aMap);
878 pSqlServer->BindString(Idx: 4, pString: aServerLike);
879 pSqlServer->BindString(Idx: 5, pString: pData->m_aName);
880
881 bool End;
882 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
883 {
884 return false;
885 }
886
887 char aRegionalRank[16];
888 if(End)
889 {
890 str_copy(dst&: aRegionalRank, src: "unranked");
891 }
892 else
893 {
894 str_format(buffer: aRegionalRank, buffer_size: sizeof(aRegionalRank), format: "rank %d", pSqlServer->GetInt(Col: 1));
895 }
896
897 const char *pAny = "%";
898
899 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
900 {
901 return false;
902 }
903 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
904 pSqlServer->BindString(Idx: 2, pString: pAny);
905 pSqlServer->BindString(Idx: 3, pString: pData->m_aMap);
906 pSqlServer->BindString(Idx: 4, pString: pAny);
907 pSqlServer->BindString(Idx: 5, pString: pData->m_aName);
908
909 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
910 {
911 return false;
912 }
913
914 if(!End)
915 {
916 int Rank = pSqlServer->GetInt(Col: 1);
917 float Time = pSqlServer->GetFloat(Col: 2);
918 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aBuf, buffer_size: sizeof(aBuf));
919
920 if(g_Config.m_SvHideScore)
921 {
922 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
923 format: "Your time: %s", aBuf);
924 }
925 else
926 {
927 pResult->m_MessageKind = CScorePlayerResult::ALL;
928 // CEIL and FLOOR are not supported in SQLite
929 int BetterThanPercent = std::floor(x: 100.0f - 100.0f * pSqlServer->GetFloat(Col: 3));
930
931 if(str_comp_nocase(a: pData->m_aRequestingPlayer, b: pData->m_aName) == 0)
932 {
933 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
934 format: "%s - %s - better than %d%%",
935 pData->m_aName, aBuf, BetterThanPercent);
936 }
937 else
938 {
939 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
940 format: "%s - %s - better than %d%% - requested by %s",
941 pData->m_aName, aBuf, BetterThanPercent, pData->m_aRequestingPlayer);
942 }
943
944 if(g_Config.m_SvRegionalRankings)
945 {
946 str_format(buffer: pResult->m_Data.m_aaMessages[1], buffer_size: sizeof(pResult->m_Data.m_aaMessages[1]),
947 format: "Global rank %d - %s %s",
948 Rank, pData->m_aServer, aRegionalRank);
949 }
950 else
951 {
952 str_format(buffer: pResult->m_Data.m_aaMessages[1], buffer_size: sizeof(pResult->m_Data.m_aaMessages[1]),
953 format: "Global rank %d", Rank);
954 }
955 }
956 }
957 else
958 {
959 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
960 format: "%s is not ranked", pData->m_aName);
961 }
962 return true;
963}
964
965bool CScoreWorker::ShowTeamRank(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
966{
967 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
968 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
969
970 // check sort method
971 char aBuf[2400];
972
973 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
974 format: "SELECT l.Id, Name, Time, Ranking, PercentRank "
975 "FROM (" // teamrank score board
976 " SELECT RANK() OVER w AS Ranking, PERCENT_RANK() OVER w AS PercentRank, Id "
977 " FROM %s_teamrace "
978 " WHERE Map = ? "
979 " GROUP BY ID "
980 " WINDOW w AS (ORDER BY Min(Time))"
981 ") AS TeamRank INNER JOIN (" // select rank with Name in team
982 " SELECT ID "
983 " FROM %s_teamrace "
984 " WHERE Map = ? AND Name = ? "
985 " ORDER BY Time "
986 " LIMIT 1"
987 ") AS l ON TeamRank.Id = l.Id "
988 "INNER JOIN %s_teamrace AS r ON l.Id = r.Id ",
989 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
990 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
991 {
992 return false;
993 }
994 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
995 pSqlServer->BindString(Idx: 2, pString: pData->m_aMap);
996 pSqlServer->BindString(Idx: 3, pString: pData->m_aName);
997
998 bool End;
999 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1000 {
1001 return false;
1002 }
1003 if(!End)
1004 {
1005 float Time = pSqlServer->GetFloat(Col: 3);
1006 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aBuf, buffer_size: sizeof(aBuf));
1007 int Rank = pSqlServer->GetInt(Col: 4);
1008 // CEIL and FLOOR are not supported in SQLite
1009 int BetterThanPercent = std::floor(x: 100.0f - 100.0f * pSqlServer->GetFloat(Col: 5));
1010 CTeamrank Teamrank;
1011 if(!Teamrank.NextSqlResult(pSqlServer, pEnd: &End, pError, ErrorSize))
1012 {
1013 return false;
1014 }
1015
1016 char aFormattedNames[512] = "";
1017 for(unsigned int Name = 0; Name < Teamrank.m_NumNames; Name++)
1018 {
1019 str_append(dst&: aFormattedNames, src: Teamrank.m_aaNames[Name]);
1020
1021 if(Name < Teamrank.m_NumNames - 2)
1022 str_append(dst&: aFormattedNames, src: ", ");
1023 else if(Name < Teamrank.m_NumNames - 1)
1024 str_append(dst&: aFormattedNames, src: " & ");
1025 }
1026
1027 if(g_Config.m_SvHideScore)
1028 {
1029 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
1030 format: "Your team time: %s, better than %d%%", aBuf, BetterThanPercent);
1031 }
1032 else
1033 {
1034 pResult->m_MessageKind = CScorePlayerResult::ALL;
1035 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
1036 format: "%d. %s Team time: %s, better than %d%%, requested by %s",
1037 Rank, aFormattedNames, aBuf, BetterThanPercent, pData->m_aRequestingPlayer);
1038 }
1039 }
1040 else
1041 {
1042 str_format(buffer: pResult->m_Data.m_aaMessages[0], buffer_size: sizeof(pResult->m_Data.m_aaMessages[0]),
1043 format: "%s has no team ranks", pData->m_aName);
1044 }
1045 return true;
1046}
1047
1048bool CScoreWorker::ShowTop(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1049{
1050 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1051 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1052
1053 int LimitStart = std::max(a: absolute(a: pData->m_Offset) - 1, b: 0);
1054 const char *pAny = "%";
1055
1056 const bool Ascending = pData->m_Offset >= 0;
1057 char aBuf[1024];
1058 if(Ascending)
1059 {
1060 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1061 format: "SELECT Name, Time, RANK() OVER (ORDER BY Time) AS Ranking "
1062 "FROM ("
1063 " SELECT r1.Name AS Name, r1.Time AS Time "
1064 " FROM %s_race r1 "
1065 " WHERE r1.Map = ? AND r1.Server LIKE ? AND NOT EXISTS ("
1066 " SELECT 1 FROM %s_race r2 "
1067 " WHERE r2.Map = r1.Map AND r2.Name = r1.Name AND r2.Server LIKE ? "
1068 " AND (r2.Time, r2.Timestamp, r2.Server) < (r1.Time, r1.Timestamp, r1.Server)) "
1069 " ORDER BY r1.Time LIMIT %d"
1070 ") AS a "
1071 "ORDER BY Ranking ASC LIMIT %d, ?",
1072 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), LimitStart + 5, LimitStart);
1073 }
1074 else
1075 {
1076 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1077 format: "SELECT Name, Time, Ranking "
1078 "FROM ("
1079 " SELECT RANK() OVER w AS Ranking, MIN(Time) AS Time, Name "
1080 " FROM %s_race "
1081 " WHERE Map = ? "
1082 " AND Server LIKE ? "
1083 " GROUP BY Name "
1084 " WINDOW w AS (ORDER BY MIN(Time))"
1085 ") as a "
1086 "ORDER BY Ranking DESC "
1087 "LIMIT %d, ?",
1088 pSqlServer->GetPrefix(),
1089 LimitStart);
1090 }
1091
1092 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1093 {
1094 return false;
1095 }
1096 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1097 pSqlServer->BindString(Idx: 2, pString: pAny);
1098 if(Ascending)
1099 {
1100 pSqlServer->BindString(Idx: 3, pString: pAny);
1101 pSqlServer->BindInt(Idx: 4, Value: 5);
1102 }
1103 else
1104 {
1105 pSqlServer->BindInt(Idx: 3, Value: 5);
1106 }
1107
1108 // show top
1109 int Line = 0;
1110 str_copy(dst&: pResult->m_Data.m_aaMessages[Line], src: "------------ Global Top ------------");
1111 Line++;
1112
1113 char aTime[32];
1114 bool End = false;
1115
1116 while(pSqlServer->Step(pEnd: &End, pError, ErrorSize) && !End)
1117 {
1118 char aName[MAX_NAME_LENGTH];
1119 pSqlServer->GetString(Col: 1, pBuffer: aName, BufferSize: sizeof(aName));
1120 float Time = pSqlServer->GetFloat(Col: 2);
1121 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aTime, buffer_size: sizeof(aTime));
1122 int Rank = pSqlServer->GetInt(Col: 3);
1123 str_format(buffer: pResult->m_Data.m_aaMessages[Line], buffer_size: sizeof(pResult->m_Data.m_aaMessages[Line]),
1124 format: "%d. %s Time: %s", Rank, aName, aTime);
1125
1126 Line++;
1127 }
1128
1129 if(!g_Config.m_SvRegionalRankings)
1130 {
1131 str_copy(dst&: pResult->m_Data.m_aaMessages[Line], src: "-----------------------------------------");
1132 return End;
1133 }
1134
1135 char aServerLike[16];
1136 str_format(buffer: aServerLike, buffer_size: sizeof(aServerLike), format: "%%%s%%", pData->m_aServer);
1137
1138 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1139 {
1140 return false;
1141 }
1142 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1143 pSqlServer->BindString(Idx: 2, pString: aServerLike);
1144 if(Ascending)
1145 {
1146 pSqlServer->BindString(Idx: 3, pString: aServerLike);
1147 pSqlServer->BindInt(Idx: 4, Value: 3);
1148 }
1149 else
1150 {
1151 pSqlServer->BindInt(Idx: 3, Value: 3);
1152 }
1153
1154 str_format(buffer: pResult->m_Data.m_aaMessages[Line], buffer_size: sizeof(pResult->m_Data.m_aaMessages[Line]),
1155 format: "------------ %s Top ------------", pData->m_aServer);
1156 Line++;
1157
1158 // show top
1159 while(pSqlServer->Step(pEnd: &End, pError, ErrorSize) && !End)
1160 {
1161 char aName[MAX_NAME_LENGTH];
1162 pSqlServer->GetString(Col: 1, pBuffer: aName, BufferSize: sizeof(aName));
1163 float Time = pSqlServer->GetFloat(Col: 2);
1164 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aTime, buffer_size: sizeof(aTime));
1165 int Rank = pSqlServer->GetInt(Col: 3);
1166 str_format(buffer: pResult->m_Data.m_aaMessages[Line], buffer_size: sizeof(pResult->m_Data.m_aaMessages[Line]),
1167 format: "%d. %s Time: %s", Rank, aName, aTime);
1168 Line++;
1169 }
1170
1171 return End;
1172}
1173
1174bool CScoreWorker::ShowTeamTop5(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1175{
1176 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1177 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1178 auto *paMessages = pResult->m_Data.m_aaMessages;
1179
1180 int LimitStart = std::max(a: absolute(a: pData->m_Offset) - 1, b: 0);
1181 const char *pOrder = pData->m_Offset >= 0 ? "ASC" : "DESC";
1182 const char *pAny = "%";
1183
1184 // check sort method
1185 char aBuf[1024];
1186
1187 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1188 format: "SELECT Name, Time, Ranking, TeamSize "
1189 "FROM ("
1190 " SELECT TeamSize, Ranking, Id, Server "
1191 " FROM (" // teamrank score board
1192 " SELECT RANK() OVER w AS Ranking, COUNT(*) AS Teamsize, Id, Server "
1193 " FROM ("
1194 " SELECT tr.Map, tr.Time, tr.Id, ("
1195 " SELECT rr.Server FROM %s_race AS rr "
1196 " WHERE rr.Map = tr.Map AND rr.Name = tr.Name AND rr.Time = tr.Time "
1197 " LIMIT 1"
1198 " ) AS Server "
1199 " FROM %s_teamrace AS tr "
1200 " WHERE tr.Map = ? "
1201 " ) AS ll "
1202 " GROUP BY ID "
1203 " WINDOW w AS (ORDER BY Min(Time))"
1204 " ) as l1 "
1205 " WHERE Server LIKE ? "
1206 " ORDER BY Ranking %s "
1207 " LIMIT %d, ?"
1208 ") as l2 "
1209 "INNER JOIN %s_teamrace as r ON l2.Id = r.Id "
1210 "ORDER BY Ranking %s, r.Id, Name ASC",
1211 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pOrder, LimitStart, pSqlServer->GetPrefix(), pOrder);
1212 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1213 {
1214 return false;
1215 }
1216 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1217 pSqlServer->BindString(Idx: 2, pString: pAny);
1218 pSqlServer->BindInt(Idx: 3, Value: 5);
1219
1220 int Line = 0;
1221 str_copy(dst: paMessages[Line++], src: "------- Team Top 5 -------", dst_size: sizeof(paMessages[Line]));
1222
1223 bool End;
1224 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1225 {
1226 return false;
1227 }
1228 if(!End)
1229 {
1230 if(!CTeamrank::GetSqlTop5Team(pSqlServer, pEnd: &End, pError, ErrorSize, paMessages, Line: &Line, Count: 5))
1231 {
1232 return false;
1233 }
1234 }
1235
1236 if(!g_Config.m_SvRegionalRankings)
1237 {
1238 str_copy(dst&: paMessages[Line], src: "-------------------------------");
1239 return true;
1240 }
1241
1242 char aServerLike[16];
1243 str_format(buffer: aServerLike, buffer_size: sizeof(aServerLike), format: "%%%s%%", pData->m_aServer);
1244
1245 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1246 {
1247 return false;
1248 }
1249 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1250 pSqlServer->BindString(Idx: 2, pString: aServerLike);
1251 pSqlServer->BindInt(Idx: 3, Value: 3);
1252
1253 str_format(buffer: pResult->m_Data.m_aaMessages[Line], buffer_size: sizeof(pResult->m_Data.m_aaMessages[Line]),
1254 format: "----- %s Team Top -----", pData->m_aServer);
1255 Line++;
1256
1257 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1258 {
1259 return false;
1260 }
1261 if(!End)
1262 {
1263 if(!CTeamrank::GetSqlTop5Team(pSqlServer, pEnd: &End, pError, ErrorSize, paMessages, Line: &Line, Count: 3))
1264 {
1265 return false;
1266 }
1267 }
1268 return true;
1269}
1270
1271bool CScoreWorker::ShowPlayerTeamTop5(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1272{
1273 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1274 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1275 auto *paMessages = pResult->m_Data.m_aaMessages;
1276
1277 int LimitStart = std::max(a: absolute(a: pData->m_Offset) - 1, b: 0);
1278 const char *pOrder = pData->m_Offset >= 0 ? "ASC" : "DESC";
1279
1280 // check sort method
1281 char aBuf[2400];
1282
1283 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1284 format: "SELECT l.Id, Name, Time, Ranking "
1285 "FROM (" // teamrank score board
1286 " SELECT RANK() OVER w AS Ranking, Id "
1287 " FROM %s_teamrace "
1288 " WHERE Map = ? "
1289 " GROUP BY ID "
1290 " WINDOW w AS (ORDER BY Min(Time))"
1291 ") AS TeamRank INNER JOIN (" // select rank with Name in team
1292 " SELECT ID "
1293 " FROM %s_teamrace "
1294 " WHERE Map = ? AND Name = ? "
1295 " ORDER BY Time %s "
1296 " LIMIT %d, 5 "
1297 ") AS l ON TeamRank.Id = l.Id "
1298 "INNER JOIN %s_teamrace AS r ON l.Id = r.Id "
1299 "ORDER BY Time %s, l.Id, Name ASC",
1300 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pOrder, LimitStart, pSqlServer->GetPrefix(), pOrder);
1301 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1302 {
1303 return false;
1304 }
1305 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1306 pSqlServer->BindString(Idx: 2, pString: pData->m_aMap);
1307 pSqlServer->BindString(Idx: 3, pString: pData->m_aName);
1308
1309 bool End;
1310 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1311 {
1312 return false;
1313 }
1314 if(!End)
1315 {
1316 // show teamtop5
1317 int Line = 0;
1318 str_copy(dst: paMessages[Line++], src: "------- Team Top 5 -------", dst_size: sizeof(paMessages[Line]));
1319
1320 for(Line = 1; Line < 6; Line++) // print
1321 {
1322 float Time = pSqlServer->GetFloat(Col: 3);
1323 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aBuf, buffer_size: sizeof(aBuf));
1324 int Rank = pSqlServer->GetInt(Col: 4);
1325 CTeamrank Teamrank;
1326 bool Last;
1327 if(!Teamrank.NextSqlResult(pSqlServer, pEnd: &Last, pError, ErrorSize))
1328 {
1329 return false;
1330 }
1331
1332 char aFormattedNames[512] = "";
1333 for(unsigned int Name = 0; Name < Teamrank.m_NumNames; Name++)
1334 {
1335 str_append(dst&: aFormattedNames, src: Teamrank.m_aaNames[Name]);
1336
1337 if(Name < Teamrank.m_NumNames - 2)
1338 str_append(dst&: aFormattedNames, src: ", ");
1339 else if(Name < Teamrank.m_NumNames - 1)
1340 str_append(dst&: aFormattedNames, src: " & ");
1341 }
1342
1343 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]), format: "%d. %s Team Time: %s",
1344 Rank, aFormattedNames, aBuf);
1345 if(Last)
1346 {
1347 Line++;
1348 break;
1349 }
1350 }
1351 str_copy(dst&: paMessages[Line], src: "---------------------------------");
1352 }
1353 else
1354 {
1355 if(pData->m_Offset == 0)
1356 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]), format: "%s has no team ranks", pData->m_aName);
1357 else
1358 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]), format: "%s has no team ranks in the specified range", pData->m_aName);
1359 }
1360 return true;
1361}
1362
1363bool CScoreWorker::ShowTimes(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1364{
1365 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1366 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1367 auto *paMessages = pResult->m_Data.m_aaMessages;
1368
1369 int LimitStart = std::max(a: absolute(a: pData->m_Offset) - 1, b: 0);
1370 const char *pOrder = pData->m_Offset >= 0 ? "DESC" : "ASC";
1371
1372 char aCurrentTimestamp[512];
1373 pSqlServer->ToUnixTimestamp(pTimestamp: "CURRENT_TIMESTAMP", aBuf: aCurrentTimestamp, BufferSize: sizeof(aCurrentTimestamp));
1374 char aTimestamp[512];
1375 pSqlServer->ToUnixTimestamp(pTimestamp: "Timestamp", aBuf: aTimestamp, BufferSize: sizeof(aTimestamp));
1376 char aBuf[512];
1377 if(pData->m_aName[0] != '\0') // last 5 times of a player
1378 {
1379 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1380 format: "SELECT Time, (%s-%s) as Ago, %s as Stamp, Server "
1381 "FROM %s_race "
1382 "WHERE Map = ? AND Name = ? "
1383 "ORDER BY Timestamp %s "
1384 "LIMIT ?, 5",
1385 aCurrentTimestamp, aTimestamp, aTimestamp,
1386 pSqlServer->GetPrefix(), pOrder);
1387 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1388 {
1389 return false;
1390 }
1391 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1392 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
1393 pSqlServer->BindInt(Idx: 3, Value: LimitStart);
1394 }
1395 else // last 5 times of server
1396 {
1397 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1398 format: "SELECT Time, (%s-%s) as Ago, %s as Stamp, Server, Name "
1399 "FROM %s_race "
1400 "WHERE Map = ? "
1401 "ORDER BY Timestamp %s "
1402 "LIMIT ?, 5",
1403 aCurrentTimestamp, aTimestamp, aTimestamp,
1404 pSqlServer->GetPrefix(), pOrder);
1405 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1406 {
1407 return false;
1408 }
1409 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1410 pSqlServer->BindInt(Idx: 2, Value: LimitStart);
1411 }
1412
1413 // show top5
1414 bool End;
1415 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1416 {
1417 return false;
1418 }
1419 if(End)
1420 {
1421 str_copy(dst&: paMessages[0], src: "There are no times in the specified range");
1422 return true;
1423 }
1424
1425 str_copy(dst&: paMessages[0], src: "------------- Last Times -------------");
1426 int Line = 1;
1427
1428 do
1429 {
1430 float Time = pSqlServer->GetFloat(Col: 1);
1431 str_time_float(secs: Time, format: ETimeFormat::HOURS_CENTISECS, buffer: aBuf, buffer_size: sizeof(aBuf));
1432 int Ago = pSqlServer->GetInt(Col: 2);
1433 int Stamp = pSqlServer->GetInt(Col: 3);
1434 char aServer[5];
1435 pSqlServer->GetString(Col: 4, pBuffer: aServer, BufferSize: sizeof(aServer));
1436 char aServerFormatted[8] = "\0";
1437 if(str_comp(a: aServer, b: "UNK") != 0)
1438 str_format(buffer: aServerFormatted, buffer_size: sizeof(aServerFormatted), format: "[%s] ", aServer);
1439
1440 char aAgoString[40] = "\0";
1441 sqlstr::AgoTimeToString(AgoTime: Ago, pAgoString: aAgoString, Size: sizeof(aAgoString));
1442
1443 if(pData->m_aName[0] != '\0') // last 5 times of a player
1444 {
1445 if(Stamp == 0) // stamp is 00:00:00 cause it's an old entry from old times where there where no stamps yet
1446 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]),
1447 format: "%s%s, don't know how long ago", aServerFormatted, aBuf);
1448 else
1449 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]),
1450 format: "%s%s ago, %s", aServerFormatted, aAgoString, aBuf);
1451 }
1452 else // last 5 times of the server
1453 {
1454 char aName[MAX_NAME_LENGTH];
1455 pSqlServer->GetString(Col: 5, pBuffer: aName, BufferSize: sizeof(aName));
1456 if(Stamp == 0) // stamp is 00:00:00 cause it's an old entry from old times where there where no stamps yet
1457 {
1458 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]),
1459 format: "%s%s, %s, don't know when", aServerFormatted, aName, aBuf);
1460 }
1461 else
1462 {
1463 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]),
1464 format: "%s%s, %s ago, %s", aServerFormatted, aName, aAgoString, aBuf);
1465 }
1466 }
1467 Line++;
1468 } while(pSqlServer->Step(pEnd: &End, pError, ErrorSize) && !End);
1469 if(!End)
1470 {
1471 return false;
1472 }
1473 str_copy(dst&: paMessages[Line], src: "-------------------------------------------");
1474
1475 return true;
1476}
1477
1478bool CScoreWorker::ShowPoints(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1479{
1480 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1481 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1482 auto *paMessages = pResult->m_Data.m_aaMessages;
1483
1484 char aBuf[512];
1485 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1486 format: "SELECT ("
1487 " SELECT COUNT(Name) + 1 FROM %s_points WHERE Points > ("
1488 " SELECT Points FROM %s_points WHERE Name = ?"
1489 ")) as Ranking, Points, Name "
1490 "FROM %s_points WHERE Name = ?",
1491 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
1492 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1493 {
1494 return false;
1495 }
1496 pSqlServer->BindString(Idx: 1, pString: pData->m_aName);
1497 pSqlServer->BindString(Idx: 2, pString: pData->m_aName);
1498
1499 bool End;
1500 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1501 {
1502 return false;
1503 }
1504 if(!End)
1505 {
1506 int Rank = pSqlServer->GetInt(Col: 1);
1507 int Count = pSqlServer->GetInt(Col: 2);
1508 char aName[MAX_NAME_LENGTH];
1509 pSqlServer->GetString(Col: 3, pBuffer: aName, BufferSize: sizeof(aName));
1510 pResult->m_MessageKind = CScorePlayerResult::ALL;
1511 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]),
1512 format: "%d. %s Points: %d, requested by %s",
1513 Rank, aName, Count, pData->m_aRequestingPlayer);
1514 }
1515 else
1516 {
1517 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]),
1518 format: "%s has not collected any points so far", pData->m_aName);
1519 }
1520 return true;
1521}
1522
1523bool CScoreWorker::ShowTopPoints(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1524{
1525 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1526 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1527 auto *paMessages = pResult->m_Data.m_aaMessages;
1528
1529 int LimitStart = std::max(a: pData->m_Offset - 1, b: 0);
1530
1531 char aBuf[512];
1532 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1533 format: "SELECT RANK() OVER (ORDER BY a.Points DESC) as Ranking, Points, Name "
1534 "FROM ("
1535 " SELECT Points, Name "
1536 " FROM %s_points "
1537 " ORDER BY Points DESC LIMIT ?"
1538 ") as a "
1539 "ORDER BY Ranking ASC, Name ASC LIMIT ?, 5",
1540 pSqlServer->GetPrefix());
1541 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1542 {
1543 return false;
1544 }
1545 pSqlServer->BindInt(Idx: 1, Value: LimitStart + 5);
1546 pSqlServer->BindInt(Idx: 2, Value: LimitStart);
1547
1548 // show top points
1549 str_copy(dst&: paMessages[0], src: "-------- Top Points --------");
1550
1551 bool End = false;
1552 int Line = 1;
1553 while(pSqlServer->Step(pEnd: &End, pError, ErrorSize) && !End)
1554 {
1555 int Rank = pSqlServer->GetInt(Col: 1);
1556 int Points = pSqlServer->GetInt(Col: 2);
1557 char aName[MAX_NAME_LENGTH];
1558 pSqlServer->GetString(Col: 3, pBuffer: aName, BufferSize: sizeof(aName));
1559 str_format(buffer: paMessages[Line], buffer_size: sizeof(paMessages[Line]),
1560 format: "%d. %s Points: %d", Rank, aName, Points);
1561 Line++;
1562 }
1563 if(!End)
1564 {
1565 return false;
1566 }
1567 str_copy(dst&: paMessages[Line], src: "-------------------------------");
1568
1569 return true;
1570}
1571
1572bool CScoreWorker::RandomMap(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1573{
1574 const auto *pData = dynamic_cast<const CSqlRandomMapRequest *>(pGameData);
1575 auto *pResult = dynamic_cast<CScoreRandomMapResult *>(pGameData->m_pResult.get());
1576
1577 char aBuf[512];
1578 if(in_range(a: pData->m_MinStars, lower: 0, upper: 5) && in_range(a: pData->m_MaxStars, lower: 0, upper: 5))
1579 {
1580 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1581 format: "SELECT Map FROM %s_maps "
1582 "WHERE Server = ? AND Map != ? AND Stars BETWEEN ? AND ? "
1583 "ORDER BY %s LIMIT 1",
1584 pSqlServer->GetPrefix(), pSqlServer->Random());
1585 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1586 {
1587 return false;
1588 }
1589 pSqlServer->BindInt(Idx: 3, Value: pData->m_MinStars);
1590 pSqlServer->BindInt(Idx: 4, Value: pData->m_MaxStars);
1591 }
1592 else
1593 {
1594 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1595 format: "SELECT Map FROM %s_maps "
1596 "WHERE Server = ? AND Map != ? "
1597 "ORDER BY %s LIMIT 1",
1598 pSqlServer->GetPrefix(), pSqlServer->Random());
1599 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1600 {
1601 return false;
1602 }
1603 }
1604 pSqlServer->BindString(Idx: 1, pString: pData->m_aServerType);
1605 pSqlServer->BindString(Idx: 2, pString: pData->m_aCurrentMap);
1606
1607 bool End;
1608 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1609 {
1610 return false;
1611 }
1612 if(!End)
1613 {
1614 pSqlServer->GetString(Col: 1, pBuffer: pResult->m_aMap, BufferSize: sizeof(pResult->m_aMap));
1615 }
1616 else
1617 {
1618 str_copy(dst&: pResult->m_aMessage, src: "No maps found on this server!");
1619 }
1620 return true;
1621}
1622
1623bool CScoreWorker::RandomUnfinishedMap(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1624{
1625 const auto *pData = dynamic_cast<const CSqlRandomMapRequest *>(pGameData);
1626 auto *pResult = dynamic_cast<CScoreRandomMapResult *>(pGameData->m_pResult.get());
1627
1628 char aBuf[512];
1629 if(in_range(a: pData->m_MinStars, lower: 0, upper: 5) && in_range(a: pData->m_MaxStars, lower: 0, upper: 5))
1630 {
1631 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1632 format: "SELECT Map "
1633 "FROM %s_maps "
1634 "WHERE Server = ? AND Map != ? AND Stars BETWEEN ? AND ? AND Map NOT IN ("
1635 " SELECT Map "
1636 " FROM %s_race "
1637 " WHERE Name = ?"
1638 ") ORDER BY %s "
1639 "LIMIT 1",
1640 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pSqlServer->Random());
1641 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1642 {
1643 return false;
1644 }
1645 pSqlServer->BindString(Idx: 1, pString: pData->m_aServerType);
1646 pSqlServer->BindString(Idx: 2, pString: pData->m_aCurrentMap);
1647 pSqlServer->BindInt(Idx: 3, Value: pData->m_MinStars);
1648 pSqlServer->BindInt(Idx: 4, Value: pData->m_MaxStars);
1649 pSqlServer->BindString(Idx: 5, pString: pData->m_aRequestingPlayer);
1650 }
1651 else
1652 {
1653 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1654 format: "SELECT Map "
1655 "FROM %s_maps AS maps "
1656 "WHERE Server = ? AND Map != ? AND Map NOT IN ("
1657 " SELECT Map "
1658 " FROM %s_race as race "
1659 " WHERE Name = ?"
1660 ") ORDER BY %s "
1661 "LIMIT 1",
1662 pSqlServer->GetPrefix(), pSqlServer->GetPrefix(), pSqlServer->Random());
1663 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1664 {
1665 return false;
1666 }
1667 pSqlServer->BindString(Idx: 1, pString: pData->m_aServerType);
1668 pSqlServer->BindString(Idx: 2, pString: pData->m_aCurrentMap);
1669 pSqlServer->BindString(Idx: 3, pString: pData->m_aRequestingPlayer);
1670 }
1671
1672 bool End;
1673 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1674 {
1675 return false;
1676 }
1677 if(!End)
1678 {
1679 pSqlServer->GetString(Col: 1, pBuffer: pResult->m_aMap, BufferSize: sizeof(pResult->m_aMap));
1680 }
1681 else
1682 {
1683 str_format(buffer: aBuf, buffer_size: sizeof(aBuf), format: "%s has no more unfinished maps on this server!", pData->m_aRequestingPlayer);
1684 str_copy(dst&: pResult->m_aMessage, src: aBuf);
1685 }
1686 return true;
1687}
1688
1689bool CScoreWorker::SaveTeam(IDbConnection *pSqlServer, const ISqlData *pGameData, Write w, char *pError, int ErrorSize)
1690{
1691 const auto *pData = dynamic_cast<const CSqlTeamSaveData *>(pGameData);
1692 auto *pResult = dynamic_cast<CScoreSaveResult *>(pGameData->m_pResult.get());
1693
1694 if(w == Write::NORMAL_SUCCEEDED)
1695 {
1696 // write succeeded on mysql server. delete from sqlite again
1697 char aBuf[128] = {0};
1698 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1699 format: "DELETE FROM %s_saves_backup WHERE Code = ?",
1700 pSqlServer->GetPrefix());
1701 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1702 {
1703 return false;
1704 }
1705 pSqlServer->BindString(Idx: 1, pString: pData->m_aGeneratedCode);
1706 bool End;
1707 return pSqlServer->Step(pEnd: &End, pError, ErrorSize);
1708 }
1709 if(w == Write::NORMAL_FAILED)
1710 {
1711 char aBuf[256] = {0};
1712 bool End;
1713 // move to non-tmp table succeeded. delete from backup again
1714 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1715 format: "INSERT INTO %s_saves SELECT * FROM %s_saves_backup WHERE Code = ?",
1716 pSqlServer->GetPrefix(), pSqlServer->GetPrefix());
1717 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1718 {
1719 return false;
1720 }
1721 pSqlServer->BindString(Idx: 1, pString: pData->m_aGeneratedCode);
1722 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1723 {
1724 return false;
1725 }
1726
1727 // move to non-tmp table succeeded. delete from backup again
1728 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1729 format: "DELETE FROM %s_saves_backup WHERE Code = ?",
1730 pSqlServer->GetPrefix());
1731 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1732 {
1733 return false;
1734 }
1735 pSqlServer->BindString(Idx: 1, pString: pData->m_aGeneratedCode);
1736 return pSqlServer->Step(pEnd: &End, pError, ErrorSize);
1737 }
1738
1739 char aSaveId[UUID_MAXSTRSIZE];
1740 FormatUuid(Uuid: pResult->m_SaveId, pBuffer: aSaveId, BufferLength: UUID_MAXSTRSIZE);
1741
1742 char *pSaveState = pResult->m_SavedTeam.GetString();
1743 char aBuf[65536];
1744
1745 dbg_msg(sys: "score/dbg", fmt: "code=%s failure=%d", pData->m_aCode, (int)w);
1746 bool UseGeneratedCode = pData->m_aCode[0] == '\0' || w != Write::NORMAL;
1747
1748 str_copy(dst&: pResult->m_aGeneratedCode, src: pData->m_aGeneratedCode);
1749 str_copy(dst&: pResult->m_aCode, src: UseGeneratedCode ? "" : pData->m_aCode);
1750
1751 bool Retry = false;
1752 // two tries, first use the user provided code, then the autogenerated
1753 do
1754 {
1755 Retry = false;
1756 char aCode[128] = {0};
1757 if(UseGeneratedCode)
1758 str_copy(dst&: aCode, src: pData->m_aGeneratedCode);
1759 else
1760 str_copy(dst&: aCode, src: pData->m_aCode);
1761
1762 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1763 format: "%s INTO %s_saves%s(Savegame, Map, Code, Timestamp, Server, SaveId, DDNet7) "
1764 "VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?, ?, %s)",
1765 pSqlServer->InsertIgnore(), pSqlServer->GetPrefix(),
1766 w == Write::NORMAL ? "" : "_backup", pSqlServer->False());
1767 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1768 {
1769 return false;
1770 }
1771 pSqlServer->BindString(Idx: 1, pString: pSaveState);
1772 pSqlServer->BindString(Idx: 2, pString: pData->m_aMap);
1773 pSqlServer->BindString(Idx: 3, pString: aCode);
1774 pSqlServer->BindString(Idx: 4, pString: pData->m_aServer);
1775 pSqlServer->BindString(Idx: 5, pString: aSaveId);
1776 pSqlServer->Print();
1777 int NumInserted;
1778 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumInserted, pError, ErrorSize))
1779 {
1780 return false;
1781 }
1782 if(NumInserted == 1)
1783 {
1784 pResult->m_Status = CScoreSaveResult::SAVE_SUCCESS;
1785 if(UseGeneratedCode)
1786 {
1787 pResult->m_aCode[0] = '\0';
1788 }
1789 if(w != Write::NORMAL)
1790 {
1791 if(str_comp(a: pData->m_aServer, b: g_Config.m_SvSqlServerName) == 0)
1792 {
1793 pResult->m_aServer[0] = '\0';
1794 }
1795 pResult->m_Status = CScoreSaveResult::SAVE_FALLBACKFILE;
1796 }
1797 }
1798 else if(!UseGeneratedCode)
1799 {
1800 UseGeneratedCode = true;
1801 Retry = true;
1802 }
1803 } while(Retry);
1804
1805 if(
1806 pResult->m_Status != CScoreSaveResult::SAVE_SUCCESS &&
1807 pResult->m_Status != CScoreSaveResult::SAVE_WARNING &&
1808 pResult->m_Status != CScoreSaveResult::SAVE_FALLBACKFILE)
1809 {
1810 dbg_msg(sys: "sql", fmt: "ERROR: This save-code already exists");
1811 pResult->m_Status = CScoreSaveResult::SAVE_FAILED;
1812 str_copy(dst&: pResult->m_aMessage, src: "This save-code already exists");
1813 }
1814 return true;
1815}
1816
1817bool CScoreWorker::LoadTeam(IDbConnection *pSqlServer, const ISqlData *pGameData, Write w, char *pError, int ErrorSize)
1818{
1819 if(w == Write::NORMAL_SUCCEEDED || w == Write::BACKUP_FIRST)
1820 return true;
1821 const auto *pData = dynamic_cast<const CSqlTeamLoadRequest *>(pGameData);
1822 auto *pResult = dynamic_cast<CScoreSaveResult *>(pGameData->m_pResult.get());
1823 pResult->m_Status = CScoreSaveResult::LOAD_FAILED;
1824
1825 char aCurrentTimestamp[512];
1826 pSqlServer->ToUnixTimestamp(pTimestamp: "CURRENT_TIMESTAMP", aBuf: aCurrentTimestamp, BufferSize: sizeof(aCurrentTimestamp));
1827 char aTimestamp[512];
1828 pSqlServer->ToUnixTimestamp(pTimestamp: "Timestamp", aBuf: aTimestamp, BufferSize: sizeof(aTimestamp));
1829
1830 char aBuf[512];
1831 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1832 format: "SELECT Savegame, %s-%s AS Ago, SaveId "
1833 "FROM %s_saves "
1834 "where Code = ? AND Map = ? AND DDNet7 = %s",
1835 aCurrentTimestamp, aTimestamp,
1836 pSqlServer->GetPrefix(), pSqlServer->False());
1837 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1838 {
1839 return false;
1840 }
1841 pSqlServer->BindString(Idx: 1, pString: pData->m_aCode);
1842 pSqlServer->BindString(Idx: 2, pString: pData->m_aMap);
1843
1844 bool End;
1845 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1846 {
1847 return false;
1848 }
1849 if(End)
1850 {
1851 str_copy(dst&: pResult->m_aMessage, src: "No such savegame for this map");
1852 return true;
1853 }
1854
1855 pResult->m_SaveId = UUID_NO_SAVE_ID;
1856 if(!pSqlServer->IsNull(Col: 3))
1857 {
1858 char aSaveId[UUID_MAXSTRSIZE];
1859 pSqlServer->GetString(Col: 3, pBuffer: aSaveId, BufferSize: sizeof(aSaveId));
1860 if(ParseUuid(pUuid: &pResult->m_SaveId, pBuffer: aSaveId) || pResult->m_SaveId == UUID_NO_SAVE_ID)
1861 {
1862 str_copy(dst&: pResult->m_aMessage, src: "Unable to load savegame: SaveId corrupted");
1863 return true;
1864 }
1865 }
1866
1867 char aSaveString[65536];
1868 pSqlServer->GetString(Col: 1, pBuffer: aSaveString, BufferSize: sizeof(aSaveString));
1869 int Num = pResult->m_SavedTeam.FromString(pString: aSaveString);
1870
1871 if(Num != 0)
1872 {
1873 str_copy(dst&: pResult->m_aMessage, src: "Unable to load savegame: data corrupted");
1874 return true;
1875 }
1876
1877 bool Found = false;
1878 for(int i = 0; i < pResult->m_SavedTeam.GetMembersCount(); i++)
1879 {
1880 if(str_comp(a: pResult->m_SavedTeam.m_pSavedTees[i].GetName(), b: pData->m_aRequestingPlayer) == 0)
1881 {
1882 Found = true;
1883 break;
1884 }
1885 }
1886 if(!Found)
1887 {
1888 str_copy(dst: pResult->m_aMessage, src: "This save exists, but you are not part of it. "
1889 "Make sure you use the same name as you had when saving. "
1890 "If you saved with an already used code, you get a new random save code, "
1891 "check ddnet-saves.txt in config_directory.",
1892 dst_size: sizeof(pResult->m_aMessage));
1893 return true;
1894 }
1895
1896 int Since = pSqlServer->GetInt(Col: 2);
1897 if(Since < g_Config.m_SvSaveSwapGamesDelay)
1898 {
1899 str_format(buffer: pResult->m_aMessage, buffer_size: sizeof(pResult->m_aMessage),
1900 format: "You have to wait %d seconds until you can load this savegame",
1901 g_Config.m_SvSaveSwapGamesDelay - Since);
1902 return true;
1903 }
1904
1905 bool CanLoad = pResult->m_SavedTeam.MatchPlayers(
1906 paNames: pData->m_aClientNames, pClientId: pData->m_aClientId, NumPlayer: pData->m_NumPlayer,
1907 pMessage: pResult->m_aMessage, MessageLen: sizeof(pResult->m_aMessage));
1908
1909 if(!CanLoad)
1910 return true;
1911
1912 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1913 format: "DELETE FROM %s_saves "
1914 "WHERE Code = ? AND Map = ? AND SaveId %s",
1915 pSqlServer->GetPrefix(),
1916 pResult->m_SaveId != UUID_NO_SAVE_ID ? "= ?" : "IS NULL");
1917 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1918 {
1919 return false;
1920 }
1921 pSqlServer->BindString(Idx: 1, pString: pData->m_aCode);
1922 pSqlServer->BindString(Idx: 2, pString: pData->m_aMap);
1923 char aUuid[UUID_MAXSTRSIZE];
1924 if(pResult->m_SaveId != UUID_NO_SAVE_ID)
1925 {
1926 FormatUuid(Uuid: pResult->m_SaveId, pBuffer: aUuid, BufferLength: sizeof(aUuid));
1927 pSqlServer->BindString(Idx: 3, pString: aUuid);
1928 }
1929 pSqlServer->Print();
1930 int NumDeleted;
1931 if(!pSqlServer->ExecuteUpdate(pNumUpdated: &NumDeleted, pError, ErrorSize))
1932 {
1933 return false;
1934 }
1935
1936 if(NumDeleted != 1)
1937 {
1938 str_copy(dst&: pResult->m_aMessage, src: "Unable to load savegame: loaded on a different server");
1939 return true;
1940 }
1941
1942 pResult->m_Status = CScoreSaveResult::LOAD_SUCCESS;
1943 str_copy(dst&: pResult->m_aMessage, src: "Loading successfully done");
1944 return true;
1945}
1946
1947bool CScoreWorker::GetSaves(IDbConnection *pSqlServer, const ISqlData *pGameData, char *pError, int ErrorSize)
1948{
1949 const auto *pData = dynamic_cast<const CSqlPlayerRequest *>(pGameData);
1950 auto *pResult = dynamic_cast<CScorePlayerResult *>(pGameData->m_pResult.get());
1951 auto *paMessages = pResult->m_Data.m_aaMessages;
1952
1953 char aSaveLike[128] = "";
1954 str_append(dst&: aSaveLike, src: "%\n");
1955 sqlstr::EscapeLike(pDst: aSaveLike + str_length(str: aSaveLike),
1956 pSrc: pData->m_aRequestingPlayer,
1957 DstSize: sizeof(aSaveLike) - str_length(str: aSaveLike));
1958 str_append(dst&: aSaveLike, src: "\t%");
1959
1960 char aCurrentTimestamp[512];
1961 pSqlServer->ToUnixTimestamp(pTimestamp: "CURRENT_TIMESTAMP", aBuf: aCurrentTimestamp, BufferSize: sizeof(aCurrentTimestamp));
1962 char aMaxTimestamp[512];
1963 pSqlServer->ToUnixTimestamp(pTimestamp: "MAX(Timestamp)", aBuf: aMaxTimestamp, BufferSize: sizeof(aMaxTimestamp));
1964
1965 char aBuf[512];
1966 str_format(buffer: aBuf, buffer_size: sizeof(aBuf),
1967 format: "SELECT COUNT(*) AS NumSaves, %s-%s AS Ago "
1968 "FROM %s_saves "
1969 "WHERE Map = ? AND Savegame LIKE ?",
1970 aCurrentTimestamp, aMaxTimestamp,
1971 pSqlServer->GetPrefix());
1972 if(!pSqlServer->PrepareStatement(pStmt: aBuf, pError, ErrorSize))
1973 {
1974 return false;
1975 }
1976 pSqlServer->BindString(Idx: 1, pString: pData->m_aMap);
1977 pSqlServer->BindString(Idx: 2, pString: aSaveLike);
1978
1979 bool End;
1980 if(!pSqlServer->Step(pEnd: &End, pError, ErrorSize))
1981 {
1982 return false;
1983 }
1984 if(!End)
1985 {
1986 int NumSaves = pSqlServer->GetInt(Col: 1);
1987 char aLastSavedString[60] = "\0";
1988 if(!pSqlServer->IsNull(Col: 2))
1989 {
1990 int Ago = pSqlServer->GetInt(Col: 2);
1991 char aAgoString[40] = "\0";
1992 sqlstr::AgoTimeToString(AgoTime: Ago, pAgoString: aAgoString, Size: sizeof(aAgoString));
1993 str_format(buffer: aLastSavedString, buffer_size: sizeof(aLastSavedString), format: ", last saved %s ago", aAgoString);
1994 }
1995
1996 str_format(buffer: paMessages[0], buffer_size: sizeof(paMessages[0]),
1997 format: "%s has %d save%s on %s%s",
1998 pData->m_aRequestingPlayer,
1999 NumSaves, NumSaves == 1 ? "" : "s",
2000 pData->m_aMap, aLastSavedString);
2001 }
2002 return true;
2003}
2004