LobbyDatabase.cpp 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531
  1. /*
  2. * LobbyServer.cpp, part of VCMI engine
  3. *
  4. * Authors: listed in file AUTHORS in main folder
  5. *
  6. * License: GNU General Public License v2.0 or later
  7. * Full text of license available in license.txt file, in main folder
  8. *
  9. */
  10. #include "StdInc.h"
  11. #include "LobbyDatabase.h"
  12. #include "SQLiteConnection.h"
  13. void LobbyDatabase::createTables()
  14. {
  15. static const std::string createChatMessages = R"(
  16. CREATE TABLE IF NOT EXISTS chatMessages (
  17. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  18. senderName TEXT,
  19. roomType TEXT,
  20. messageText TEXT,
  21. creationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
  22. );
  23. )";
  24. static const std::string createTableGameRooms = R"(
  25. CREATE TABLE IF NOT EXISTS gameRooms (
  26. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  27. roomID TEXT,
  28. hostAccountID TEXT,
  29. status INTEGER NOT NULL DEFAULT 0,
  30. playerLimit INTEGER NOT NULL,
  31. creationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
  32. );
  33. )";
  34. static const std::string createTableGameRoomPlayers = R"(
  35. CREATE TABLE IF NOT EXISTS gameRoomPlayers (
  36. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  37. roomID TEXT,
  38. accountID TEXT
  39. );
  40. )";
  41. static const std::string createTableAccounts = R"(
  42. CREATE TABLE IF NOT EXISTS accounts (
  43. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  44. accountID TEXT,
  45. displayName TEXT,
  46. online INTEGER NOT NULL,
  47. lastLoginTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  48. creationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
  49. );
  50. )";
  51. static const std::string createTableAccountCookies = R"(
  52. CREATE TABLE IF NOT EXISTS accountCookies (
  53. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  54. accountID TEXT,
  55. cookieUUID TEXT,
  56. creationTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
  57. );
  58. )";
  59. static const std::string createTableGameRoomInvites = R"(
  60. CREATE TABLE IF NOT EXISTS gameRoomInvites (
  61. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  62. roomID TEXT,
  63. accountID TEXT
  64. );
  65. )";
  66. database->prepare(createChatMessages)->execute();
  67. database->prepare(createTableGameRoomPlayers)->execute();
  68. database->prepare(createTableGameRooms)->execute();
  69. database->prepare(createTableAccounts)->execute();
  70. database->prepare(createTableAccountCookies)->execute();
  71. database->prepare(createTableGameRoomInvites)->execute();
  72. }
  73. void LobbyDatabase::prepareStatements()
  74. {
  75. // INSERT INTO
  76. static const std::string insertChatMessageText = R"(
  77. INSERT INTO chatMessages(senderName, messageText) VALUES( ?, ?);
  78. )";
  79. static const std::string insertAccountText = R"(
  80. INSERT INTO accounts(accountID, displayName, online) VALUES(?,?,0);
  81. )";
  82. static const std::string insertAccessCookieText = R"(
  83. INSERT INTO accountCookies(accountID, cookieUUID) VALUES(?,?);
  84. )";
  85. static const std::string insertGameRoomText = R"(
  86. INSERT INTO gameRooms(roomID, hostAccountID, status, playerLimit) VALUES(?, ?, 0, 8);
  87. )";
  88. static const std::string insertGameRoomPlayersText = R"(
  89. INSERT INTO gameRoomPlayers(roomID, accountID) VALUES(?,?);
  90. )";
  91. static const std::string insertGameRoomInvitesText = R"(
  92. INSERT INTO gameRoomInvites(roomID, accountID) VALUES(?,?);
  93. )";
  94. // DELETE FROM
  95. static const std::string deleteGameRoomPlayersText = R"(
  96. DELETE FROM gameRoomPlayers WHERE roomID = ? AND accountID = ?
  97. )";
  98. static const std::string deleteGameRoomInvitesText = R"(
  99. DELETE FROM gameRoomInvites WHERE roomID = ? AND accountID = ?
  100. )";
  101. // UPDATE
  102. static const std::string setAccountOnlineText = R"(
  103. UPDATE accounts
  104. SET online = ?
  105. WHERE accountID = ?
  106. )";
  107. static const std::string setGameRoomStatusText = R"(
  108. UPDATE gameRooms
  109. SET status = ?
  110. WHERE roomID = ?
  111. )";
  112. static const std::string setGameRoomPlayerLimitText = R"(
  113. UPDATE gameRooms
  114. SET playerLimit = ?
  115. WHERE roomID = ?
  116. )";
  117. // SELECT FROM
  118. static const std::string getRecentMessageHistoryText = R"(
  119. SELECT senderName, displayName, messageText, strftime('%s',CURRENT_TIMESTAMP)- strftime('%s',cm.creationTime) AS secondsElapsed
  120. FROM chatMessages cm
  121. LEFT JOIN accounts on accountID = senderName
  122. WHERE secondsElapsed < 60*60*18
  123. ORDER BY cm.creationTime DESC
  124. LIMIT 100
  125. )";
  126. static const std::string getIdleGameRoomText = R"(
  127. SELECT roomID
  128. FROM gameRooms
  129. WHERE hostAccountID = ? AND status = 0
  130. LIMIT 1
  131. )";
  132. static const std::string getGameRoomStatusText = R"(
  133. SELECT status
  134. FROM gameRooms
  135. WHERE roomID = ?
  136. )";
  137. static const std::string getAccountGameRoomText = R"(
  138. SELECT grp.roomID
  139. FROM gameRoomPlayers grp
  140. LEFT JOIN gameRooms gr ON gr.roomID = grp.roomID
  141. WHERE accountID = ? AND status IN (1, 2)
  142. LIMIT 1
  143. )";
  144. static const std::string getActiveAccountsText = R"(
  145. SELECT accountID, displayName
  146. FROM accounts
  147. WHERE online = 1
  148. )";
  149. static const std::string getActiveGameRoomsText = R"(
  150. SELECT roomID, hostAccountID, displayName, status, playerLimit
  151. FROM gameRooms
  152. LEFT JOIN accounts ON hostAccountID = accountID
  153. WHERE status = 1
  154. )";
  155. static const std::string countRoomUsedSlotsText = R"(
  156. SELECT COUNT(accountID)
  157. FROM gameRoomPlayers
  158. WHERE roomID = ?
  159. )";
  160. static const std::string countRoomTotalSlotsText = R"(
  161. SELECT playerLimit
  162. FROM gameRooms
  163. WHERE roomID = ?
  164. )";
  165. static const std::string getAccountDisplayNameText = R"(
  166. SELECT displayName
  167. FROM accounts
  168. WHERE accountID = ?
  169. )";
  170. static const std::string isAccountCookieValidText = R"(
  171. SELECT COUNT(accountID)
  172. FROM accountCookies
  173. WHERE accountID = ? AND cookieUUID = ? AND strftime('%s',CURRENT_TIMESTAMP)- strftime('%s',creationTime) < ?
  174. )";
  175. static const std::string isGameRoomCookieValidText = R"(
  176. SELECT COUNT(roomID)
  177. FROM gameRooms
  178. LEFT JOIN accountCookies ON accountCookies.accountID = gameRooms.hostAccountID
  179. WHERE roomID = ? AND cookieUUID = ? AND strftime('%s',CURRENT_TIMESTAMP)- strftime('%s',creationTime) < ?
  180. )";
  181. static const std::string isPlayerInGameRoomText = R"(
  182. SELECT COUNT(accountID)
  183. FROM gameRoomPlayers grp
  184. LEFT JOIN gameRooms gr ON gr.roomID = grp.roomID
  185. WHERE accountID = ? AND grp.roomID = ? AND status IN (1, 2)
  186. )";
  187. static const std::string isPlayerInAnyGameRoomText = R"(
  188. SELECT COUNT(accountID)
  189. FROM gameRoomPlayers grp
  190. LEFT JOIN gameRooms gr ON gr.roomID = grp.roomID
  191. WHERE accountID = ? AND status IN (1, 2)
  192. )";
  193. static const std::string isAccountIDExistsText = R"(
  194. SELECT COUNT(accountID)
  195. FROM accounts
  196. WHERE accountID = ?
  197. )";
  198. static const std::string isAccountNameExistsText = R"(
  199. SELECT COUNT(displayName)
  200. FROM accounts
  201. WHERE displayName = ?
  202. )";
  203. insertChatMessageStatement = database->prepare(insertChatMessageText);
  204. insertAccountStatement = database->prepare(insertAccountText);
  205. insertAccessCookieStatement = database->prepare(insertAccessCookieText);
  206. insertGameRoomStatement = database->prepare(insertGameRoomText);
  207. insertGameRoomPlayersStatement = database->prepare(insertGameRoomPlayersText);
  208. insertGameRoomInvitesStatement = database->prepare(insertGameRoomInvitesText);
  209. deleteGameRoomPlayersStatement = database->prepare(deleteGameRoomPlayersText);
  210. deleteGameRoomInvitesStatement = database->prepare(deleteGameRoomInvitesText);
  211. setAccountOnlineStatement = database->prepare(setAccountOnlineText);
  212. setGameRoomStatusStatement = database->prepare(setGameRoomStatusText);
  213. setGameRoomPlayerLimitStatement = database->prepare(setGameRoomPlayerLimitText);
  214. getRecentMessageHistoryStatement = database->prepare(getRecentMessageHistoryText);
  215. getIdleGameRoomStatement = database->prepare(getIdleGameRoomText);
  216. getGameRoomStatusStatement = database->prepare(getGameRoomStatusText);
  217. getAccountGameRoomStatement = database->prepare(getAccountGameRoomText);
  218. getActiveAccountsStatement = database->prepare(getActiveAccountsText);
  219. getActiveGameRoomsStatement = database->prepare(getActiveGameRoomsText);
  220. getAccountDisplayNameStatement = database->prepare(getAccountDisplayNameText);
  221. countRoomUsedSlotsStatement = database->prepare(countRoomUsedSlotsText);
  222. countRoomTotalSlotsStatement = database->prepare(countRoomTotalSlotsText);
  223. isAccountCookieValidStatement = database->prepare(isAccountCookieValidText);
  224. isPlayerInGameRoomStatement = database->prepare(isPlayerInGameRoomText);
  225. isPlayerInAnyGameRoomStatement = database->prepare(isPlayerInAnyGameRoomText);
  226. isAccountIDExistsStatement = database->prepare(isAccountIDExistsText);
  227. isAccountNameExistsStatement = database->prepare(isAccountNameExistsText);
  228. }
  229. LobbyDatabase::~LobbyDatabase() = default;
  230. LobbyDatabase::LobbyDatabase(const boost::filesystem::path & databasePath)
  231. {
  232. database = SQLiteInstance::open(databasePath, true);
  233. createTables();
  234. prepareStatements();
  235. }
  236. void LobbyDatabase::insertChatMessage(const std::string & sender, const std::string & roomType, const std::string & roomName, const std::string & messageText)
  237. {
  238. insertChatMessageStatement->executeOnce(sender, messageText);
  239. }
  240. bool LobbyDatabase::isPlayerInGameRoom(const std::string & accountID)
  241. {
  242. bool result = false;
  243. isPlayerInAnyGameRoomStatement->setBinds(accountID);
  244. if(isPlayerInAnyGameRoomStatement->execute())
  245. isPlayerInAnyGameRoomStatement->getColumns(result);
  246. isPlayerInAnyGameRoomStatement->reset();
  247. return result;
  248. }
  249. bool LobbyDatabase::isPlayerInGameRoom(const std::string & accountID, const std::string & roomID)
  250. {
  251. bool result = false;
  252. isPlayerInGameRoomStatement->setBinds(accountID, roomID);
  253. if(isPlayerInGameRoomStatement->execute())
  254. isPlayerInGameRoomStatement->getColumns(result);
  255. isPlayerInGameRoomStatement->reset();
  256. return result;
  257. }
  258. std::vector<LobbyChatMessage> LobbyDatabase::getRecentMessageHistory()
  259. {
  260. std::vector<LobbyChatMessage> result;
  261. while(getRecentMessageHistoryStatement->execute())
  262. {
  263. LobbyChatMessage message;
  264. getRecentMessageHistoryStatement->getColumns(message.accountID, message.displayName, message.messageText, message.age);
  265. result.push_back(message);
  266. }
  267. getRecentMessageHistoryStatement->reset();
  268. return result;
  269. }
  270. void LobbyDatabase::setAccountOnline(const std::string & accountID, bool isOnline)
  271. {
  272. setAccountOnlineStatement->executeOnce(isOnline ? 1 : 0, accountID);
  273. }
  274. void LobbyDatabase::setGameRoomStatus(const std::string & roomID, LobbyRoomState roomStatus)
  275. {
  276. setGameRoomStatusStatement->executeOnce(vstd::to_underlying(roomStatus), roomID);
  277. }
  278. void LobbyDatabase::setGameRoomPlayerLimit(const std::string & roomID, uint32_t playerLimit)
  279. {
  280. setGameRoomPlayerLimitStatement->executeOnce(playerLimit, roomID);
  281. }
  282. void LobbyDatabase::insertPlayerIntoGameRoom(const std::string & accountID, const std::string & roomID)
  283. {
  284. insertGameRoomPlayersStatement->executeOnce(roomID, accountID);
  285. }
  286. void LobbyDatabase::deletePlayerFromGameRoom(const std::string & accountID, const std::string & roomID)
  287. {
  288. deleteGameRoomPlayersStatement->executeOnce(roomID, accountID);
  289. }
  290. void LobbyDatabase::deleteGameRoomInvite(const std::string & targetAccountID, const std::string & roomID)
  291. {
  292. deleteGameRoomInvitesStatement->executeOnce(roomID, targetAccountID);
  293. }
  294. void LobbyDatabase::insertGameRoomInvite(const std::string & targetAccountID, const std::string & roomID)
  295. {
  296. insertGameRoomInvitesStatement->executeOnce(roomID, targetAccountID);
  297. }
  298. void LobbyDatabase::insertGameRoom(const std::string & roomID, const std::string & hostAccountID)
  299. {
  300. insertGameRoomStatement->executeOnce(roomID, hostAccountID);
  301. }
  302. void LobbyDatabase::insertAccount(const std::string & accountID, const std::string & displayName)
  303. {
  304. insertAccountStatement->executeOnce(accountID, displayName);
  305. }
  306. void LobbyDatabase::insertAccessCookie(const std::string & accountID, const std::string & accessCookieUUID)
  307. {
  308. insertAccessCookieStatement->executeOnce(accountID, accessCookieUUID);
  309. }
  310. void LobbyDatabase::updateAccessCookie(const std::string & accountID, const std::string & accessCookieUUID) {}
  311. void LobbyDatabase::updateAccountLoginTime(const std::string & accountID) {}
  312. void LobbyDatabase::updateActiveAccount(const std::string & accountID, bool isActive) {}
  313. std::string LobbyDatabase::getAccountDisplayName(const std::string & accountID)
  314. {
  315. std::string result;
  316. getAccountDisplayNameStatement->setBinds(accountID);
  317. if(getAccountDisplayNameStatement->execute())
  318. getAccountDisplayNameStatement->getColumns(result);
  319. getAccountDisplayNameStatement->reset();
  320. return result;
  321. }
  322. LobbyCookieStatus LobbyDatabase::getGameRoomCookieStatus(const std::string & accountID, const std::string & accessCookieUUID, std::chrono::seconds cookieLifetime)
  323. {
  324. return {};
  325. }
  326. LobbyCookieStatus LobbyDatabase::getAccountCookieStatus(const std::string & accountID, const std::string & accessCookieUUID, std::chrono::seconds cookieLifetime)
  327. {
  328. bool result = false;
  329. isAccountCookieValidStatement->setBinds(accountID, accessCookieUUID, cookieLifetime.count());
  330. if(isAccountCookieValidStatement->execute())
  331. isAccountCookieValidStatement->getColumns(result);
  332. isAccountCookieValidStatement->reset();
  333. return result ? LobbyCookieStatus::VALID : LobbyCookieStatus::INVALID;
  334. }
  335. LobbyInviteStatus LobbyDatabase::getAccountInviteStatus(const std::string & accountID, const std::string & roomID)
  336. {
  337. return {};
  338. }
  339. LobbyRoomState LobbyDatabase::getGameRoomStatus(const std::string & roomID)
  340. {
  341. int result = -1;
  342. getGameRoomStatusStatement->setBinds(roomID);
  343. if(getGameRoomStatusStatement->execute())
  344. getGameRoomStatusStatement->getColumns(result);
  345. getGameRoomStatusStatement->reset();
  346. if (result != -1)
  347. return static_cast<LobbyRoomState>(result);
  348. return LobbyRoomState::CLOSED;
  349. }
  350. uint32_t LobbyDatabase::getGameRoomFreeSlots(const std::string & roomID)
  351. {
  352. uint32_t usedSlots = 0;
  353. uint32_t totalSlots = 0;
  354. countRoomUsedSlotsStatement->setBinds(roomID);
  355. if(countRoomUsedSlotsStatement->execute())
  356. countRoomUsedSlotsStatement->getColumns(usedSlots);
  357. countRoomUsedSlotsStatement->reset();
  358. countRoomTotalSlotsStatement->setBinds(roomID);
  359. if(countRoomTotalSlotsStatement->execute())
  360. countRoomTotalSlotsStatement->getColumns(totalSlots);
  361. countRoomTotalSlotsStatement->reset();
  362. if (totalSlots > usedSlots)
  363. return totalSlots - usedSlots;
  364. return 0;
  365. }
  366. bool LobbyDatabase::isAccountNameExists(const std::string & displayName)
  367. {
  368. bool result = false;
  369. isAccountNameExistsStatement->setBinds(displayName);
  370. if(isAccountNameExistsStatement->execute())
  371. isAccountNameExistsStatement->getColumns(result);
  372. isAccountNameExistsStatement->reset();
  373. return result;
  374. }
  375. bool LobbyDatabase::isAccountIDExists(const std::string & accountID)
  376. {
  377. bool result = false;
  378. isAccountIDExistsStatement->setBinds(accountID);
  379. if(isAccountIDExistsStatement->execute())
  380. isAccountIDExistsStatement->getColumns(result);
  381. isAccountIDExistsStatement->reset();
  382. return result;
  383. }
  384. std::vector<LobbyGameRoom> LobbyDatabase::getActiveGameRooms()
  385. {
  386. std::vector<LobbyGameRoom> result;
  387. while(getActiveGameRoomsStatement->execute())
  388. {
  389. LobbyGameRoom entry;
  390. getActiveGameRoomsStatement->getColumns(entry.roomID, entry.hostAccountID, entry.hostAccountDisplayName, entry.roomStatus, entry.playersLimit);
  391. result.push_back(entry);
  392. }
  393. getActiveGameRoomsStatement->reset();
  394. for (auto & room : result)
  395. {
  396. countRoomUsedSlotsStatement->setBinds(room.roomID);
  397. if(countRoomUsedSlotsStatement->execute())
  398. countRoomUsedSlotsStatement->getColumns(room.playersCount);
  399. countRoomUsedSlotsStatement->reset();
  400. }
  401. return result;
  402. }
  403. std::vector<LobbyAccount> LobbyDatabase::getActiveAccounts()
  404. {
  405. std::vector<LobbyAccount> result;
  406. while(getActiveAccountsStatement->execute())
  407. {
  408. LobbyAccount entry;
  409. getActiveAccountsStatement->getColumns(entry.accountID, entry.displayName);
  410. result.push_back(entry);
  411. }
  412. getActiveAccountsStatement->reset();
  413. return result;
  414. }
  415. std::string LobbyDatabase::getIdleGameRoom(const std::string & hostAccountID)
  416. {
  417. std::string result;
  418. getIdleGameRoomStatement->setBinds(hostAccountID);
  419. if(getIdleGameRoomStatement->execute())
  420. getIdleGameRoomStatement->getColumns(result);
  421. getIdleGameRoomStatement->reset();
  422. return result;
  423. }
  424. std::string LobbyDatabase::getAccountGameRoom(const std::string & accountID)
  425. {
  426. std::string result;
  427. getAccountGameRoomStatement->setBinds(accountID);
  428. if(getAccountGameRoomStatement->execute())
  429. getAccountGameRoomStatement->getColumns(result);
  430. getAccountGameRoomStatement->reset();
  431. return result;
  432. }