API tools faq
paste
Guest User

Untitled

a guest
Aug 16th, 2020
59
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 58.63 KB | None | 0 0
raw download clone embed print report
  1. ////////////////////////////////////////////////////////////////////////
  2. // OpenTibia - an opensource roleplaying game
  3. ////////////////////////////////////////////////////////////////////////
  4. // This program is free software: you can redistribute it and/or modify
  5. // it under the terms of the GNU General Public License as published by
  6. // the Free Software Foundation, either version 3 of the License, or
  7. // (at your option) any later version.
  8. //
  9. // This program is distributed in the hope that it will be useful,
  10. // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12. // GNU General Public License for more details.
  13. //
  14. // You should have received a copy of the GNU General Public License
  15. // along with this program. If not, see <http://www.gnu.org/licenses/>.
  16. ////////////////////////////////////////////////////////////////////////
  17. #include "otpch.h"
  18. #include "enums.h"
  19. #include <iostream>
  20.  
  21. #include "databasemanager.h"
  22. #include "tools.h"
  23.  
  24. #include "configmanager.h"
  25. extern ConfigManager g_config;
  26.  
  27. bool DatabaseManager::optimizeTables()
  28. {
  29. Database* db = Database::getInstance();
  30. DBQuery query;
  31. switch(db->getDatabaseEngine())
  32. {
  33. case DATABASE_ENGINE_MYSQL:
  34. {
  35. query << "SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::SQL_DB)) << " AND `DATA_FREE` > 0;";
  36. DBResult* result;
  37. if(!(result = db->storeQuery(query.str())))
  38. break;
  39.  
  40. query.str("");
  41. do
  42. {
  43. std::cout << "> Optimizing table: " << result->getDataString("TABLE_NAME") << "... ";
  44. query << "OPTIMIZE TABLE `" << result->getDataString("TABLE_NAME") << "`;";
  45. if(db->executeQuery(query.str()))
  46. std::cout << "[success]" << std::endl;
  47. else
  48. std::cout << "[failure]" << std::endl;
  49.  
  50. query.str("");
  51. }
  52. while(result->next());
  53.  
  54. result->free();
  55. return true;
  56. }
  57.  
  58. case DATABASE_ENGINE_SQLITE:
  59. {
  60. if(!db->executeQuery("VACUUM;"))
  61. break;
  62.  
  63. std::cout << "> Optimized database." << std::endl;
  64. return true;
  65. }
  66.  
  67. case DATABASE_ENGINE_POSTGRESQL:
  68. {
  69. if(!db->executeQuery("VACUUM FULL;"))
  70. break;
  71.  
  72. std::cout << "> Optimized database." << std::endl;
  73. return true;
  74. }
  75.  
  76. default:
  77. break;
  78. }
  79.  
  80. return false;
  81. }
  82.  
  83. bool DatabaseManager::triggerExists(std::string trigger)
  84. {
  85. Database* db = Database::getInstance();
  86. DBQuery query;
  87. switch(db->getDatabaseEngine())
  88. {
  89. case DATABASE_ENGINE_SQLITE:
  90. query << "SELECT `name` FROM `sqlite_master` WHERE `type` = 'trigger' AND `name` = " << db->escapeString(trigger) << ";";
  91. break;
  92.  
  93. case DATABASE_ENGINE_MYSQL:
  94. query << "SELECT `TRIGGER_NAME` FROM `information_schema`.`triggers` WHERE `TRIGGER_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::SQL_DB)) << " AND `TRIGGER_NAME` = " << db->escapeString(trigger) << ";";
  95. break;
  96.  
  97. case DATABASE_ENGINE_POSTGRESQL:
  98. //TODO: PostgreSQL support
  99. return true;
  100.  
  101. default:
  102. return false;
  103. }
  104.  
  105. DBResult* result;
  106. if(!(result = db->storeQuery(query.str())))
  107. return false;
  108.  
  109. result->free();
  110. return true;
  111. }
  112.  
  113. bool DatabaseManager::tableExists(std::string table)
  114. {
  115. Database* db = Database::getInstance();
  116. DBQuery query;
  117. switch(db->getDatabaseEngine())
  118. {
  119. case DATABASE_ENGINE_SQLITE:
  120. query << "SELECT `name` FROM `sqlite_master` WHERE `type` = 'table' AND `name` = " << db->escapeString(table) << ";";
  121. break;
  122.  
  123. case DATABASE_ENGINE_MYSQL:
  124. query << "SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::SQL_DB)) << " AND `TABLE_NAME` = " << db->escapeString(table) << ";";
  125. break;
  126.  
  127. case DATABASE_ENGINE_POSTGRESQL:
  128. //TODO: PostgreSQL support
  129. return true;
  130.  
  131. default:
  132. return false;
  133. }
  134.  
  135. DBResult* result;
  136. if(!(result = db->storeQuery(query.str())))
  137. return false;
  138.  
  139. result->free();
  140. return true;
  141. }
  142.  
  143. bool DatabaseManager::isDatabaseSetup()
  144. {
  145. Database* db = Database::getInstance();
  146. switch(db->getDatabaseEngine())
  147. {
  148. case DATABASE_ENGINE_MYSQL:
  149. {
  150. DBQuery query;
  151. query << "SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::SQL_DB)) << ";";
  152.  
  153. DBResult* result;
  154. if(!(result = db->storeQuery(query.str())))
  155. return false;
  156.  
  157. result->free();
  158. return true;
  159. }
  160.  
  161. case DATABASE_ENGINE_SQLITE:
  162. //a pre-setup sqlite database is already included
  163. return true;
  164.  
  165. case DATABASE_ENGINE_POSTGRESQL:
  166. //TODO: PostgreSQL support
  167. return true;
  168.  
  169. default:
  170. break;
  171. }
  172.  
  173. return false;
  174. }
  175.  
  176. int32_t DatabaseManager::getDatabaseVersion()
  177. {
  178. if(!tableExists("server_config"))
  179. return 0;
  180.  
  181. int32_t value = 0;
  182. if(getDatabaseConfig("db_version", value))
  183. return value;
  184.  
  185. return 1;
  186. }
  187.  
  188. uint32_t DatabaseManager::updateDatabase()
  189. {
  190. Database* db = Database::getInstance();
  191. uint32_t version = getDatabaseVersion();
  192. if(db->getDatabaseEngine() == DATABASE_ENGINE_ODBC)
  193. return version;
  194.  
  195. if(version < 6 && db->getDatabaseEngine() == DATABASE_ENGINE_POSTGRESQL)
  196. {
  197. std::cout << "> WARNING: Couldn't update database - PostgreSQL support available since version 6, please use latest pgsql.sql schema." << std::endl;
  198. registerDatabaseConfig("db_version", 6);
  199. return 6;
  200. }
  201.  
  202. DBQuery query;
  203. switch(version)
  204. {
  205. case 0:
  206. {
  207. std::cout << "> Updating database to version: 1..." << std::endl;
  208. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  209. db->executeQuery("CREATE TABLE IF NOT EXISTS `server_config` (`config` VARCHAR(35) NOT NULL DEFAULT '', `value` INTEGER NOT NULL);");
  210. else
  211. db->executeQuery("CREATE TABLE IF NOT EXISTS `server_config` (`config` VARCHAR(35) NOT NULL DEFAULT '', `value` INT NOT NULL) ENGINE = InnoDB;");
  212.  
  213. db->executeQuery("INSERT INTO `server_config` VALUES ('db_version', 1);");
  214. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  215. {
  216. //TODO: 0.2 migration SQLite support
  217. std::cerr << "> SQLite migration from 0.2 support not available, please use latest database!" << std::endl;
  218. return 1;
  219. }
  220.  
  221. if(!tableExists("server_motd"))
  222. {
  223. //update bans table
  224. if(db->executeQuery("CREATE TABLE IF NOT EXISTS `bans2` (`id` INT UNSIGNED NOT NULL auto_increment, `type` TINYINT(1) NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban', `value` INT UNSIGNED NOT NULL COMMENT 'ip, player guid, account number', `param` INT UNSIGNED NOT NULL DEFAULT 4294967295 COMMENT 'mask', `active` TINYINT(1) NOT NULL DEFAULT TRUE, `expires` INT UNSIGNED NOT NULL, `added` INT UNSIGNED NOT NULL, `admin_id` INT UNSIGNED NOT NULL DEFAULT 0, `comment` TEXT NOT NULL, `reason` INT UNSIGNED NOT NULL DEFAULT 0, `action` INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `type` (`type`, `value`)) ENGINE = InnoDB;"))
  225. {
  226. if(DBResult* result = db->storeQuery("SELECT * FROM `bans`;"))
  227. {
  228. do
  229. {
  230. switch(result->getDataInt("type"))
  231. {
  232. case 1:
  233. query << "INSERT INTO `bans2` (`type`, `value`, `param`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`) VALUES (1, " << result->getDataInt("ip") << ", " << result->getDataInt("mask") << ", " << (result->getDataInt("time") <= time(NULL) ? 0 : 1) << ", " << result->getDataInt("time") << ", 0, " << result->getDataInt("banned_by") << ", " << db->escapeString(result->getDataString("comment")) << ", " << result->getDataInt("reason_id") << ", " << result->getDataInt("action_id") << ");";
  234. break;
  235.  
  236. case 2:
  237. query << "INSERT INTO `bans2` (`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`) VALUES (2, " << result->getDataInt("player") << ", " << (result->getDataInt("time") <= time(NULL) ? 0 : 1) << ", 0, " << result->getDataInt("time") << ", " << result->getDataInt("banned_by") << ", " << db->escapeString(result->getDataString("comment")) << ", " << result->getDataInt("reason_id") << ", " << result->getDataInt("action_id") << ");";
  238. break;
  239.  
  240. case 3:
  241. query << "INSERT INTO `bans2` (`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`) VALUES (3, " << result->getDataInt("player") << ", " << (result->getDataInt("time") <= time(NULL) ? 0 : 1) << ", " << result->getDataInt("time") << ", 0, " << result->getDataInt("banned_by") << ", " << db->escapeString(result->getDataString("comment")) << ", " << result->getDataInt("reason_id") << ", " << result->getDataInt("action_id") << ");";
  242. break;
  243.  
  244. case 4:
  245. case 5:
  246. query << "INSERT INTO `bans2` (`type`, `value`, `active`, `expires`, `added`, `admin_id`, `comment`, `reason`, `action`) VALUES (" << result->getDataInt("type") << ", " << result->getDataInt("player") << ", " << (result->getDataInt("time") <= time(NULL) ? 0 : 1) << ", 0, " << result->getDataInt("time") << ", " << result->getDataInt("banned_by") << ", " << db->escapeString(result->getDataString("comment")) << ", " << result->getDataInt("reason_id") << ", " << result->getDataInt("action_id") << ");";
  247. break;
  248.  
  249. default:
  250. break;
  251. }
  252.  
  253. if(query.str() != "")
  254. {
  255. db->executeQuery(query.str());
  256. query.str("");
  257. }
  258. }
  259. while(result->next());
  260. result->free();
  261. }
  262.  
  263. db->executeQuery("DROP TABLE `bans`;");
  264. db->executeQuery("RENAME TABLE `bans2` TO `bans`;");
  265. }
  266.  
  267. std::string queryList[] = {
  268. //create server_record table
  269. "CREATE TABLE IF NOT EXISTS `server_record` (`record` INT NOT NULL, `timestamp` BIGINT NOT NULL, PRIMARY KEY (`timestamp`)) ENGINE = InnoDB;",
  270. //create server_reports table
  271. "CREATE TABLE IF NOT EXISTS `server_reports` (`id` INT NOT NULL AUTO_INCREMENT, `player_id` INT UNSIGNED NOT NULL DEFAULT 0, `posx` INT NOT NULL DEFAULT 0, `posy` INT NOT NULL DEFAULT 0, `posz` INT NOT NULL DEFAULT 0, `timestamp` BIGINT NOT NULL DEFAULT 0, `report` TEXT NOT NULL, `reads` INT NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY (`player_id`), KEY (`reads`)) ENGINE = InnoDB;",
  272. //create server_motd table
  273. "CREATE TABLE `server_motd` (`id` INT NOT NULL AUTO_INCREMENT, `text` TEXT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;",
  274. //create global_storage table
  275. "CREATE TABLE IF NOT EXISTS `global_storage` (`key` INT UNSIGNED NOT NULL, `value` INT NOT NULL, PRIMARY KEY (`key`)) ENGINE = InnoDB;",
  276. //insert data to server_record table
  277. "INSERT INTO `server_record` VALUES (0, 0);",
  278. //insert data to server_motd table
  279. "INSERT INTO `server_motd` VALUES (1, 'Welcome to The Forgotten Server!');",
  280. //update players table
  281. "ALTER TABLE `players` ADD `balance` BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER `blessings`;",
  282. "ALTER TABLE `players` ADD `stamina` BIGINT UNSIGNED NOT NULL DEFAULT 201660000 AFTER `balance`;",
  283. "ALTER TABLE `players` ADD `loss_items` INT NOT NULL DEFAULT 10 AFTER `loss_skills`;",
  284. "ALTER TABLE `players` ADD `marriage` INT UNSIGNED NOT NULL DEFAULT 0;",
  285. "UPDATE `players` SET `loss_experience` = 10, `loss_mana` = 10, `loss_skills` = 10, `loss_items` = 10;",
  286. //update accounts table
  287. "ALTER TABLE `accounts` DROP `type`;",
  288. //update player deaths table
  289. "ALTER TABLE `player_deaths` DROP `is_player`;",
  290. //update house table
  291. "ALTER TABLE `houses` CHANGE `warnings` `warnings` INT NOT NULL DEFAULT 0;",
  292. "ALTER TABLE `houses` ADD `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0;",
  293. //update triggers
  294. "DROP TRIGGER IF EXISTS `ondelete_accounts`;",
  295. "DROP TRIGGER IF EXISTS `ondelete_players`;",
  296. "CREATE TRIGGER `ondelete_accounts` BEFORE DELETE ON `accounts` FOR EACH ROW BEGIN DELETE FROM `bans` WHERE `type` != 1 AND `type` != 2 AND `value` = OLD.`id`; END;",
  297. "CREATE TRIGGER `ondelete_players` BEFORE DELETE ON `players` FOR EACH ROW BEGIN DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`; UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`; END;"
  298. };
  299. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  300. db->executeQuery(queryList[i]);
  301. }
  302.  
  303. return 1;
  304. }
  305.  
  306. case 1:
  307. {
  308. std::cout << "> Updating database to version: 2..." << std::endl;
  309. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  310. db->executeQuery("ALTER TABLE `players` ADD `promotion` INTEGER NOT NULL DEFAULT 0;");
  311. else
  312. db->executeQuery("ALTER TABLE `players` ADD `promotion` INT NOT NULL DEFAULT 0;");
  313.  
  314. DBResult* result;
  315. if((result = db->storeQuery("SELECT `player_id`, `value` FROM `player_storage` WHERE `key` = 30018 AND `value` > 0")))
  316. {
  317. do
  318. {
  319. query << "UPDATE `players` SET `promotion` = " << result->getDataLong("value") << " WHERE `id` = " << result->getDataInt("player_id") << ";";
  320. db->executeQuery(query.str());
  321. query.str("");
  322. }
  323. while(result->next());
  324. result->free();
  325. }
  326.  
  327. db->executeQuery("DELETE FROM `player_storage` WHERE `key` = 30018;");
  328. db->executeQuery("ALTER TABLE `accounts` ADD `name` VARCHAR(32) NOT NULL DEFAULT '';");
  329. if((result = db->storeQuery("SELECT `id` FROM `accounts`;")))
  330. {
  331. do
  332. {
  333. query << "UPDATE `accounts` SET `name` = '" << result->getDataInt("id") << "' WHERE `id` = " << result->getDataInt("id") << ";";
  334. db->executeQuery(query.str());
  335. query.str("");
  336. }
  337. while(result->next());
  338. result->free();
  339. }
  340.  
  341. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  342. db->executeQuery("ALTER TABLE `players` ADD `deleted` BOOLEAN NOT NULL DEFAULT 0;");
  343. else
  344. db->executeQuery("ALTER TABLE `players` ADD `deleted` TINYINT(1) NOT NULL DEFAULT 0;");
  345.  
  346. if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  347. db->executeQuery("ALTER TABLE `player_items` CHANGE `attributes` `attributes` BLOB NOT NULL;");
  348.  
  349. registerDatabaseConfig("db_version", 2);
  350. return 2;
  351. }
  352.  
  353. case 2:
  354. {
  355. std::cout << "> Updating database to version: 3..." << std::endl;
  356. db->executeQuery("UPDATE `players` SET `vocation` = `vocation` - 4 WHERE `vocation` >= 5 AND `vocation` <= 8;");
  357.  
  358. DBResult* result;
  359. if((result = db->storeQuery("SELECT COUNT(`id`) AS `count` FROM `players` WHERE `vocation` > 4;"))
  360. && result->getDataInt("count"))
  361. {
  362. std::cout << "[Warning] There are still " << result->getDataInt("count") << " players with vocation above 4, please mind to update them manually." << std::endl;
  363. result->free();
  364. }
  365.  
  366. registerDatabaseConfig("db_version", 3);
  367. return 3;
  368. }
  369.  
  370. case 3:
  371. {
  372. std::cout << "> Updating database to version: 4..." << std::endl;
  373. db->executeQuery("ALTER TABLE `houses` ADD `name` VARCHAR(255) NOT NULL;");
  374. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  375. {
  376. std::string queryList[] = {
  377. "ALTER TABLE `houses` ADD `size` INTEGER NOT NULL DEFAULT 0;",
  378. "ALTER TABLE `houses` ADD `town` INTEGER NOT NULL DEFAULT 0;",
  379. "ALTER TABLE `houses` ADD `price` INTEGER NOT NULL DEFAULT 0;",
  380. "ALTER TABLE `houses` ADD `rent` INTEGER NOT NULL DEFAULT 0;"
  381. };
  382. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  383. db->executeQuery(queryList[i]);
  384. }
  385. else
  386. {
  387. std::string queryList[] = {
  388. "ALTER TABLE `houses` ADD `size` INT UNSIGNED NOT NULL DEFAULT 0;",
  389. "ALTER TABLE `houses` ADD `town` INT UNSIGNED NOT NULL DEFAULT 0;",
  390. "ALTER TABLE `houses` ADD `price` INT UNSIGNED NOT NULL DEFAULT 0;",
  391. "ALTER TABLE `houses` ADD `rent` INT UNSIGNED NOT NULL DEFAULT 0;"
  392. };
  393. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  394. db->executeQuery(queryList[i]);
  395. }
  396.  
  397. registerDatabaseConfig("db_version", 4);
  398. return 4;
  399. }
  400.  
  401. case 4:
  402. {
  403. std::cout << "> Updating database to version: 5..." << std::endl;
  404. db->executeQuery("ALTER TABLE `player_deaths` ADD `altkilled_by` VARCHAR(255) NOT NULL;");
  405. registerDatabaseConfig("db_version", 5);
  406. return 5;
  407. }
  408.  
  409. case 5:
  410. {
  411. std::cout << "> Updating database to version: 6..." << std::endl;
  412. switch(db->getDatabaseEngine())
  413. {
  414. case DATABASE_ENGINE_MYSQL:
  415. {
  416. std::string queryList[] = {
  417. "ALTER TABLE `global_storage` CHANGE `value` `value` VARCHAR(255) NOT NULL DEFAULT '0'",
  418. "ALTER TABLE `player_storage` CHANGE `value` `value` VARCHAR(255) NOT NULL DEFAULT '0'",
  419. "ALTER TABLE `tiles` CHANGE `x` `x` INT(5) UNSIGNED NOT NULL, CHANGE `y` `y` INT(5) UNSIGNED NOT NULL, CHANGE `z` `z` TINYINT(2) UNSIGNED NOT NULL;",
  420. "ALTER TABLE `tiles` ADD INDEX (`x`, `y`, `z`);",
  421. "ALTER TABLE `tile_items` ADD INDEX (`sid`);"
  422. };
  423. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  424. db->executeQuery(queryList[i]);
  425.  
  426. break;
  427. }
  428.  
  429. case DATABASE_ENGINE_SQLITE:
  430. {
  431. std::string queryList[] = {
  432. "ALTER TABLE `global_storage` RENAME TO `global_storage2`;",
  433. "CREATE TABLE `global_storage` (`key` INTEGER NOT NULL, `value` VARCHAR(255) NOT NULL DEFAULT '0', UNIQUE (`key`));",
  434. "INSERT INTO `global_storage` SELECT * FROM `global_storage2`;",
  435. "ALTER TABLE `player_storage` RENAME TO `player_storage2`;",
  436. "CREATE TABLE `player_storage` (`player_id` INTEGER NOT NULL, `key` INTEGER NOT NULL, `value` VARCHAR(255) NOT NULL DEFAULT '0', UNIQUE (`player_id`, `key`), FOREIGN KEY (`player_id`) REFERENCES `players` (`id`));",
  437. "INSERT INTO `player_storage` SELECT * FROM `player_storage2`;"
  438. };
  439. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  440. db->executeQuery(queryList[i]);
  441.  
  442. break;
  443. }
  444.  
  445. default:
  446. break;
  447. }
  448.  
  449. registerDatabaseConfig("db_version", 6);
  450. return 6;
  451. }
  452.  
  453. case 6:
  454. {
  455. std::cout << "> Updating database to version: 7..." << std::endl;
  456. if(g_config.getBool(ConfigManager::INGAME_GUILD_MANAGEMENT))
  457. {
  458. if(DBResult* result = db->storeQuery("SELECT `r`.`id`, `r`.`guild_id` FROM `guild_ranks` r LEFT JOIN `guilds` g ON `r`.`guild_id` = `g`.`id` WHERE `g`.`ownerid` = `g`.`id` AND `r`.`level` = 3;"))
  459. {
  460. do
  461. {
  462. query << "UPDATE `guilds`, `players` SET `guilds`.`ownerid` = `players`.`id` WHERE `guilds`.`id` = " << result->getDataInt("guild_id") << " AND `players`.`rank_id` = " << result->getDataInt("id") << ";";
  463. db->executeQuery(query.str());
  464. query.str("");
  465. }
  466. while(result->next());
  467. result->free();
  468. }
  469. }
  470.  
  471. registerDatabaseConfig("db_version", 7);
  472. return 7;
  473. }
  474.  
  475. case 7:
  476. {
  477. std::cout << "> Updating database version to: 8..." << std::endl;
  478. switch(db->getDatabaseEngine())
  479. {
  480. case DATABASE_ENGINE_MYSQL:
  481. {
  482. std::string queryList[] = {
  483. "ALTER TABLE `server_motd` CHANGE `id` `id` INT UNSIGNED NOT NULL;",
  484. "ALTER TABLE `server_motd` DROP PRIMARY KEY;",
  485. "ALTER TABLE `server_motd` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  486. "ALTER TABLE `server_motd` ADD UNIQUE (`id`, `world_id`);",
  487. "ALTER TABLE `server_record` DROP PRIMARY KEY;",
  488. "ALTER TABLE `server_record` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  489. "ALTER TABLE `server_record` ADD UNIQUE (`timestamp`, `record`, `world_id`);",
  490. "ALTER TABLE `server_reports` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  491. "ALTER TABLE `server_reports` ADD INDEX (`world_id`);",
  492. "ALTER TABLE `players` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  493. "ALTER TABLE `global_storage` DROP PRIMARY KEY;",
  494. "ALTER TABLE `global_storage` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  495. "ALTER TABLE `global_storage` ADD UNIQUE (`key`, `world_id`);",
  496. "ALTER TABLE `guilds` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  497. "ALTER TABLE `guilds` ADD UNIQUE (`name`, `world_id`);",
  498. "ALTER TABLE `house_lists` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  499. "ALTER TABLE `house_lists` ADD UNIQUE (`house_id`, `world_id`, `listid`);",
  500. "ALTER TABLE `houses` CHANGE `id` `id` INT NOT NULL;",
  501. "ALTER TABLE `houses` DROP PRIMARY KEY;",
  502. "ALTER TABLE `houses` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  503. "ALTER TABLE `houses` ADD UNIQUE (`id`, `world_id`);",
  504. "ALTER TABLE `tiles` CHANGE `id` `id` INT NOT NULL;",
  505. "ALTER TABLE `tiles` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  506. "ALTER TABLE `tiles` ADD UNIQUE (`id`, `world_id`);",
  507. "ALTER TABLE `tile_items` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  508. "ALTER TABLE `tile_items` ADD UNIQUE (`tile_id`, `world_id`, `sid`);"
  509. };
  510. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  511. db->executeQuery(queryList[i]);
  512.  
  513. break;
  514. }
  515.  
  516. case DATABASE_ENGINE_SQLITE:
  517. case DATABASE_ENGINE_POSTGRESQL:
  518. default:
  519. {
  520. //TODO
  521. break;
  522. }
  523. }
  524.  
  525. registerDatabaseConfig("db_version", 8);
  526. return 8;
  527. }
  528.  
  529. case 8:
  530. {
  531. std::cout << "> Updating database to version: 9..." << std::endl;
  532. db->executeQuery("ALTER TABLE `bans` ADD `statement` VARCHAR(255) NOT NULL;");
  533. registerDatabaseConfig("db_version", 9);
  534. return 9;
  535. }
  536.  
  537. case 9:
  538. {
  539. std::cout << "> Updating database to version: 10..." << std::endl;
  540. registerDatabaseConfig("db_version", 10);
  541. return 10;
  542. }
  543.  
  544. case 10:
  545. {
  546. std::cout << "> Updating database to version: 11..." << std::endl;
  547. db->executeQuery("ALTER TABLE `players` ADD `description` VARCHAR(255) NOT NULL DEFAULT '';");
  548. if(tableExists("map_storage"))
  549. {
  550. db->executeQuery("ALTER TABLE `map_storage` RENAME TO `house_data`;");
  551. db->executeQuery("ALTER TABLE `house_data` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 AFTER `house_id`;");
  552. }
  553. else if(!tableExists("house_storage"))
  554. {
  555. query << "CREATE TABLE `house_data` (`house_id` INT UNSIGNED NOT NULL, `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, `data` LONGBLOB NOT NULL, UNIQUE (`house_id`, `world_id`)";
  556. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  557. query << ", FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE";
  558.  
  559. query << ")";
  560. if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  561. query << " ENGINE = InnoDB";
  562.  
  563. query << ";";
  564. db->executeQuery(query.str());
  565. query.str("");
  566. }
  567. else
  568. db->executeQuery("ALTER TABLE `house_storage` RENAME TO `house_data`;");
  569.  
  570. registerDatabaseConfig("db_version", 11);
  571. return 11;
  572. }
  573.  
  574. case 11:
  575. {
  576. std::cout << "> Updating database to version: 12..." << std::endl;
  577. db->executeQuery("UPDATE `players` SET `stamina` = 151200000 WHERE `stamina` > 151200000;");
  578. db->executeQuery("UPDATE `players` SET `loss_experience` = `loss_experience` * 10, `loss_mana` = `loss_mana` * 10, `loss_skills` = `loss_skills` * 10, `loss_items` = `loss_items` * 10;");
  579. switch(db->getDatabaseEngine())
  580. {
  581. case DATABASE_ENGINE_MYSQL:
  582. {
  583. std::string queryList[] = {
  584. "ALTER TABLE `players` CHANGE `stamina` `stamina` INT NOT NULL DEFAULT 151200000;",
  585. "ALTER TABLE `players` CHANGE `loss_experience` `loss_experience` INT NOT NULL DEFAULT 100;",
  586. "ALTER TABLE `players` CHANGE `loss_mana` `loss_mana` INT NOT NULL DEFAULT 100;",
  587. "ALTER TABLE `players` CHANGE `loss_skills` `loss_skills` INT NOT NULL DEFAULT 100;",
  588. "ALTER TABLE `players` CHANGE `loss_items` `loss_items` INT NOT NULL DEFAULT 100;",
  589. "ALTER TABLE `players` ADD `loss_containers` INT NOT NULL DEFAULT 100 AFTER `loss_skills`;"
  590. };
  591. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  592. db->executeQuery(queryList[i]);
  593.  
  594. break;
  595. }
  596.  
  597. case DATABASE_ENGINE_SQLITE:
  598. case DATABASE_ENGINE_POSTGRESQL:
  599. default:
  600. {
  601. //TODO
  602. break;
  603. }
  604. }
  605.  
  606. registerDatabaseConfig("db_version", 12);
  607. return 12;
  608. }
  609.  
  610. case 12:
  611. {
  612. std::cout << "> Updating database to version: 13..." << std::endl;
  613. std::string queryList[] = {
  614. "ALTER TABLE `accounts` DROP KEY `group_id`;",
  615. "ALTER TABLE `players` DROP KEY `group_id`;",
  616. "DROP TABLE `groups`;"
  617. };
  618. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  619. db->executeQuery(queryList[i]);
  620.  
  621.  
  622. registerDatabaseConfig("db_version", 13);
  623. return 13;
  624. }
  625.  
  626. case 13:
  627. {
  628. std::cout << "> Updating database to version: 14..." << std::endl;
  629. switch(db->getDatabaseEngine())
  630. {
  631. case DATABASE_ENGINE_MYSQL:
  632. {
  633. std::string queryList[] = {
  634. "ALTER TABLE `houses` ADD `doors` INT UNSIGNED NOT NULL DEFAULT 0;",
  635. "ALTER TABLE `houses` ADD `beds` INT UNSIGNED NOT NULL DEFAULT 0;",
  636. "ALTER TABLE `houses` ADD `guild` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;"
  637. };
  638. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  639. db->executeQuery(queryList[i]);
  640.  
  641. break;
  642. }
  643.  
  644. case DATABASE_ENGINE_SQLITE:
  645. {
  646. std::string queryList[] = {
  647. "ALTER TABLE `houses` ADD `doors` INTEGER NOT NULL DEFAULT 0;",
  648. "ALTER TABLE `houses` ADD `beds` INTEGER NOT NULL DEFAULT 0;",
  649. "ALTER TABLE `houses` ADD `guild` BOOLEAN NOT NULL DEFAULT FALSE;"
  650. };
  651. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  652. db->executeQuery(queryList[i]);
  653.  
  654. break;
  655. }
  656.  
  657. case DATABASE_ENGINE_POSTGRESQL:
  658. default:
  659. {
  660. //TODO
  661. break;
  662. }
  663. }
  664.  
  665. registerDatabaseConfig("db_version", 14);
  666. return 14;
  667. }
  668.  
  669. case 14:
  670. {
  671. std::cout << "> Updating database to version: 15..." << std::endl;
  672. db->executeQuery("DROP TABLE `player_deaths`;"); //no support for moving, sorry!
  673. switch(db->getDatabaseEngine())
  674. {
  675. case DATABASE_ENGINE_MYSQL:
  676. {
  677. std::string queryList[] = {
  678. "CREATE TABLE `player_deaths`\
  679. (\
  680. `id` INT NOT NULL AUTO_INCREMENT,\
  681. `player_id` INT NOT NULL,\
  682. `date` BIGINT UNSIGNED NOT NULL,\
  683. `level` INT UNSIGNED NOT NULL,\
  684. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,\
  685. PRIMARY KEY(`id`),\
  686. INDEX(`date`)\
  687. ) ENGINE = InnoDB;",
  688. "CREATE TABLE `killers`\
  689. (\
  690. `id` INT NOT NULL AUTO_INCREMENT,\
  691. `death_id` INT NOT NULL,\
  692. `final_hit` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,\
  693. PRIMARY KEY(`id`),\
  694. FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`) ON DELETE CASCADE\
  695. ) ENGINE = InnoDB;",
  696. "CREATE TABLE `player_killers`\
  697. (\
  698. `kill_id` INT NOT NULL,\
  699. `player_id` INT NOT NULL,\
  700. FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE,\
  701. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE\
  702. ) ENGINE = InnoDB;",
  703. "CREATE TABLE `environment_killers`\
  704. (\
  705. `kill_id` INT NOT NULL,\
  706. `name` VARCHAR(255) NOT NULL,\
  707. FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE\
  708. ) ENGINE = InnoDB;"
  709. };
  710. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  711. db->executeQuery(queryList[i]);
  712.  
  713. break;
  714. }
  715.  
  716. case DATABASE_ENGINE_SQLITE:
  717. {
  718. std::string queryList[] = {
  719. "CREATE TABLE `player_deaths` (\
  720. `id` INTEGER PRIMARY KEY,\
  721. `player_id` INTEGER NOT NULL,\
  722. `date` INTEGER NOT NULL,\
  723. `level` INTEGER NOT NULL,\
  724. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  725. );",
  726. "CREATE TABLE `killers` (\
  727. `id` INTEGER PRIMARY KEY,\
  728. `death_id` INTEGER NOT NULL,\
  729. `final_hit` BOOLEAN NOT NULL DEFAULT FALSE,\
  730. FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`)\
  731. );",
  732. "CREATE TABLE `player_killers` (\
  733. `kill_id` INTEGER NOT NULL,\
  734. `player_id` INTEGER NOT NULL,\
  735. FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`),\
  736. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  737. );",
  738. "CREATE TABLE `environment_killers` (\
  739. `kill_id` INTEGER NOT NULL,\
  740. `name` VARCHAR(255) NOT NULL,\
  741. FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`)\
  742. );"
  743. };
  744. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  745. db->executeQuery(queryList[i]);
  746.  
  747. break;
  748. }
  749.  
  750. case DATABASE_ENGINE_POSTGRESQL:
  751. default:
  752. {
  753. //TODO
  754. break;
  755. }
  756. }
  757.  
  758. registerDatabaseConfig("db_version", 15);
  759. return 15;
  760. }
  761.  
  762. case 15:
  763. {
  764. std::cout << "> Updating database to version: 16..." << std::endl;
  765. switch(db->getDatabaseEngine())
  766. {
  767. case DATABASE_ENGINE_MYSQL:
  768. {
  769. std::string queryList[] = {
  770. "ALTER TABLE `players` DROP `redskull`;",
  771. "ALTER TABLE `players` CHANGE `redskulltime` `redskulltime` INT NOT NULL DEFAULT 0;",
  772. "ALTER TABLE `killers` ADD `unjustified` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;",
  773. "UPDATE `players` SET `redskulltime` = 0;"
  774. };
  775. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  776. db->executeQuery(queryList[i]);
  777.  
  778. break;
  779. }
  780.  
  781. case DATABASE_ENGINE_SQLITE:
  782. {
  783. std::string queryList[] = {
  784. //we cannot DROP redskull, and redskulltime is already INTEGER
  785. "ALTER TABLE `killers` ADD `unjustified` BOOLEAN NOT NULL DEFAULT FALSE;",
  786. "UPDATE `players` SET `redskulltime` = 0;"
  787. };
  788. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  789. db->executeQuery(queryList[i]);
  790.  
  791. break;
  792. }
  793.  
  794. case DATABASE_ENGINE_POSTGRESQL:
  795. default:
  796. {
  797. //TODO
  798. break;
  799. }
  800. }
  801.  
  802. registerDatabaseConfig("db_version", 16);
  803. return 16;
  804. }
  805.  
  806. case 16:
  807. {
  808. std::cout << "> Updating database to version: 17..." << std::endl;
  809. switch(db->getDatabaseEngine())
  810. {
  811. case DATABASE_ENGINE_MYSQL:
  812. db->executeQuery("CREATE TABLE IF NOT EXISTS `player_namelocks`\
  813. (\
  814. `player_id` INT NOT NULL DEFAULT 0,\
  815. `name` VARCHAR(255) NOT NULL,\
  816. `new_name` VARCHAR(255) NOT NULL,\
  817. `date` BIGINT NOT NULL DEFAULT 0,\
  818. KEY (`player_id`),\
  819. FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE\
  820. ) ENGINE = InnoDB;");
  821.  
  822. break;
  823. case DATABASE_ENGINE_SQLITE:
  824. db->executeQuery("CREATE TABLE IF NOT EXISTS `player_namelocks` (\
  825. `player_id` INTEGER NOT NULL,\
  826. `name` VARCHAR(255) NOT NULL,\
  827. `new_name` VARCHAR(255) NOT NULL,\
  828. `date` INTEGER NOT NULL DEFAULT 0,\
  829. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  830. );");
  831. break;
  832. default:
  833. //TODO
  834. break;
  835. }
  836.  
  837. registerDatabaseConfig("db_version", 17);
  838. return 17;
  839. }
  840.  
  841. case 17:
  842. {
  843. std::cout << "> Updating database to version: 18..." << std::endl;
  844. switch(db->getDatabaseEngine())
  845. {
  846. case DATABASE_ENGINE_MYSQL:
  847. {
  848. std::string queryList[] = {
  849. "ALTER TABLE `player_depotitems` DROP KEY `player_id`;",
  850. "ALTER TABLE `player_depotitems` DROP `depot_id`;",
  851. "ALTER TABLE `player_depotitems` ADD KEY (`player_id`);",
  852. "ALTER TABLE `house_data` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  853. "ALTER TABLE `house_lists` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  854. "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE;",
  855. "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE;",
  856. "ALTER TABLE `tiles` ADD `house_id` INT UNSIGNED NOT NULL AFTER `world_id`;",
  857. "ALTER TABLE `tiles` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  858. "ALTER TABLE `houses` ADD `clear` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;"
  859. };
  860. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  861. db->executeQuery(queryList[i]);
  862.  
  863. break;
  864. }
  865.  
  866. case DATABASE_ENGINE_SQLITE:
  867. {
  868. std::string queryList[] = {
  869. "ALTER TABLE `house_data` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  870. "ALTER TABLE `house_lists` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  871. "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`player_id`) REFERENCES `players`(`id`);",
  872. "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`);",
  873. "ALTER TABLE `tiles` ADD `house_id` INTEGER NOT NULL;",
  874. "ALTER TABLE `tiles` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  875. "ALTER TABLE `houses` ADD `clear` BOOLEAN NOT NULL DEFAULT FALSE;"
  876. };
  877. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  878. db->executeQuery(queryList[i]);
  879.  
  880. break;
  881. }
  882.  
  883. default:
  884. //TODO
  885. break;
  886. }
  887.  
  888. registerDatabaseConfig("db_version", 18);
  889. return 18;
  890. }
  891.  
  892. case 18:
  893. {
  894. std::cout << "> Updating database to version: 19..." << std::endl;
  895. switch(db->getDatabaseEngine())
  896. {
  897. case DATABASE_ENGINE_MYSQL:
  898. {
  899. std::string queryList[] = {
  900. "ALTER TABLE `houses` ADD `tiles` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `beds`;",
  901. "CREATE TABLE `house_auctions`\
  902. (\
  903. `house_id` INT UNSIGNED NOT NULL,\
  904. `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,\
  905. `player_id` INT NOT NULL,\
  906. `bid` INT UNSIGNED NOT NULL DEFAULT 0,\
  907. `limit` INT UNSIGNED NOT NULL DEFAULT 0,\
  908. `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,\
  909. UNIQUE (`house_id`, `world_id`),\
  910. FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE,\
  911. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE\
  912. ) ENGINE = InnoDB;"
  913. };
  914. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  915. db->executeQuery(queryList[i]);
  916.  
  917. break;
  918. }
  919.  
  920. case DATABASE_ENGINE_SQLITE:
  921. {
  922. std::string queryList[] = {
  923. "ALTER TABLE `houses` ADD `tiles` INTEGER NOT NULL DEFAULT 0;",
  924. "CREATE TABLE `house_auctions` (\
  925. `house_id` INTEGER NOT NULL,\
  926. `world_id` INTEGER NOT NULL DEFAULT 0,\
  927. `player_id` INTEGER NOT NULL,\
  928. `bid` INTEGER NOT NULL DEFAULT 0,\
  929. `limit` INTEGER NOT NULL DEFAULT 0,\
  930. `endtime` INTEGER NOT NULL DEFAULT 0,\
  931. UNIQUE (`house_id`, `world_id`),\
  932. FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses` (`id`, `world_id`)\
  933. FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  934. );"
  935. };
  936. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  937. db->executeQuery(queryList[i]);
  938.  
  939. break;
  940. }
  941.  
  942. default:
  943. //TODO
  944. break;
  945. }
  946.  
  947. registerDatabaseConfig("db_version", 19);
  948. return 19;
  949. }
  950.  
  951. case 19:
  952. {
  953. std::cout << "> Updating database to version: 20..." << std::endl;
  954. switch(db->getDatabaseEngine())
  955. {
  956. case DATABASE_ENGINE_MYSQL:
  957. {
  958. std::string queryList[] = {
  959. "ALTER TABLE `players` CHANGE `redskulltime` `skulltime` INT NOT NULL DEFAULT 0;",
  960. "ALTER TABLE `players` ADD `skull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER `save`;"
  961. };
  962. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  963. db->executeQuery(queryList[i]);
  964.  
  965. break;
  966. }
  967.  
  968. case DATABASE_ENGINE_SQLITE:
  969. {
  970. std::string queryList[] = {
  971. "ALTER TABLE `players` ADD `skulltime` INTEGER NOT NULL DEFAULT 0;",
  972. "ALTER TABLE `players` ADD `skull` INTEGER NOT NULL DEFAULT 0;",
  973. "UPDATE `players` SET `skulltime` = `redskulltime`, `redskulltime` = 0;"
  974. };
  975. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  976. db->executeQuery(queryList[i]);
  977.  
  978. break;
  979. }
  980.  
  981. default:
  982. //TODO
  983. break;
  984. }
  985.  
  986.  
  987. registerDatabaseConfig("db_version", 20);
  988. return 20;
  989. }
  990.  
  991. case 20:
  992. {
  993. std::cout << "> Updating database to version: 21..." << std::endl;
  994. std::string queryList[] = {
  995. "UPDATE `bans` SET `type` = 3 WHERE `type` = 5;",
  996. "UPDATE `bans` SET `param` = 2 WHERE `type` = 2;",
  997. "UPDATE `bans` SET `param` = 0 WHERE `type` IN (3,4);"
  998. };
  999. for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); i++)
  1000. db->executeQuery(queryList[i]);
  1001.  
  1002. registerDatabaseConfig("db_version", 21);
  1003. return 21;
  1004. }
  1005.  
  1006. case 21:
  1007. {
  1008. std::cout << "> Updating database to version: 22..." << std::endl;
  1009. switch(db->getDatabaseEngine())
  1010. {
  1011. case DATABASE_ENGINE_MYSQL:
  1012. db->executeQuery("CREATE TABLE `account_viplist` (`account_id` INT NOT NULL, `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0, `player_id` INT NOT NULL, KEY (`account_id`), KEY (`player_id`), KEY (`world_id`), UNIQUE (`account_id`, `player_id`), FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE, FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE) ENGINE = InnoDB;");
  1013. break;
  1014. case DATABASE_ENGINE_SQLITE:
  1015. db->executeQuery("CREATE TABLE `account_viplist` (`account_id` INTEGER NOT NULL, `world_id` INTEGER NOT NULL DEFAULT 0, `player_id` INTEGER NOT NULL, UNIQUE (`account_id`, `player_id`), FOREIGN KEY `account_id` REFERENCES `accounts` (`id`), FOREIGN KEY `player_id` REFERENCES `players` (`id`));");
  1016. break;
  1017. default:
  1018. break;
  1019. }
  1020.  
  1021. registerDatabaseConfig("db_version", 22);
  1022. return 22;
  1023. }
  1024.  
  1025. case 22:
  1026. {
  1027. std::cout << "> Updating database to version 23..." << std::endl;
  1028. if(g_config.getBool(ConfigManager::ACCOUNT_MANAGER))
  1029. {
  1030. query << "SELECT `id`, `key` FROM `accounts` WHERE `key` ";
  1031. if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  1032. query << "NOT LIKE";
  1033. else
  1034. query << "!=";
  1035.  
  1036. query << " '0';";
  1037. if(DBResult* result = db->storeQuery(query.str()))
  1038. {
  1039. query.str("");
  1040. do
  1041. {
  1042. std::string key = result->getDataString("key");
  1043. _encrypt(key, false);
  1044.  
  1045. query << "UPDATE `accounts` SET `key` = " << db->escapeString(key) << " WHERE `id` = " << result->getDataInt("id") << db->getUpdateLimiter();
  1046. db->executeQuery(query.str());
  1047. query.str("");
  1048. }
  1049. while(result->next());
  1050. result->free();
  1051. }
  1052. }
  1053.  
  1054. query << "DELETE FROM `server_config` WHERE `config` " << db->getStringComparison() << "'password_type';";
  1055. db->executeQuery(query.str());
  1056. query.str("");
  1057.  
  1058. registerDatabaseConfig("encryption", g_config.getNumber(ConfigManager::ENCRYPTION));
  1059. registerDatabaseConfig("db_version", 23);
  1060. return 23;
  1061. }
  1062.  
  1063. default:
  1064. break;
  1065. }
  1066.  
  1067. return 0;
  1068. }
  1069.  
  1070. bool DatabaseManager::getDatabaseConfig(std::string config, int32_t &value)
  1071. {
  1072. value = 0;
  1073.  
  1074. Database* db = Database::getInstance();
  1075. DBResult* result;
  1076.  
  1077. DBQuery query;
  1078. query << "SELECT `value` FROM `server_config` WHERE `config` = " << db->escapeString(config) << ";";
  1079. if(!(result = db->storeQuery(query.str())))
  1080. return false;
  1081.  
  1082. value = result->getDataInt("value");
  1083. result->free();
  1084. return true;
  1085. }
  1086.  
  1087. void DatabaseManager::registerDatabaseConfig(std::string config, int32_t value)
  1088. {
  1089. Database* db = Database::getInstance();
  1090. DBQuery query;
  1091.  
  1092. int32_t tmp = 0;
  1093. if(!getDatabaseConfig(config, tmp))
  1094. query << "INSERT INTO `server_config` VALUES (" << db->escapeString(config) << ", " << value << ");";
  1095. else
  1096. query << "UPDATE `server_config` SET `value` = " << value << " WHERE `config` = " << db->escapeString(config) << ";";
  1097.  
  1098. db->executeQuery(query.str());
  1099. }
  1100.  
  1101. void DatabaseManager::checkEncryption()
  1102. {
  1103. Encryption_t newValue = (Encryption_t)g_config.getNumber(ConfigManager::ENCRYPTION);
  1104. int32_t value = (int32_t)ENCRYPTION_PLAIN;
  1105. if(getDatabaseConfig("encryption", value))
  1106. {
  1107. if(newValue != (Encryption_t)value)
  1108. {
  1109. switch(newValue)
  1110. {
  1111. case ENCRYPTION_MD5:
  1112. {
  1113. if((Encryption_t)value != ENCRYPTION_PLAIN)
  1114. {
  1115. std::cout << "> WARNING: You cannot change the encryption to MD5, change it back in config.lua to \"sha1\"." << std::endl;
  1116. return;
  1117. }
  1118.  
  1119. Database* db = Database::getInstance();
  1120. DBQuery query;
  1121. if(db->getDatabaseEngine() != DATABASE_ENGINE_MYSQL && db->getDatabaseEngine() != DATABASE_ENGINE_POSTGRESQL)
  1122. {
  1123. if(DBResult* result = db->storeQuery("SELECT `id`, `password`, `key` FROM `accounts`;"))
  1124. {
  1125. do
  1126. {
  1127. query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToMD5(result->getDataString("password"), false)) << ", `key` = " << db->escapeString(transformToMD5(result->getDataString("key"), false)) << " WHERE `id` = " << result->getDataInt("id") << ";";
  1128. db->executeQuery(query.str());
  1129. }
  1130. while(result->next());
  1131. result->free();
  1132. }
  1133. }
  1134. else
  1135. db->executeQuery("UPDATE `accounts` SET `password` = MD5(`password`), `key` = MD5(`key`);");
  1136.  
  1137. registerDatabaseConfig("encryption", (int32_t)newValue);
  1138. std::cout << "> Encryption updated to MD5." << std::endl;
  1139. break;
  1140. }
  1141.  
  1142. case ENCRYPTION_SHA1:
  1143. {
  1144. if((Encryption_t)value != ENCRYPTION_PLAIN)
  1145. {
  1146. std::cout << "> WARNING: You cannot change the encryption to SHA1, change it back in config.lua to \"md5\"." << std::endl;
  1147. return;
  1148. }
  1149.  
  1150. Database* db = Database::getInstance();
  1151. DBQuery query;
  1152. if(db->getDatabaseEngine() != DATABASE_ENGINE_MYSQL && db->getDatabaseEngine() != DATABASE_ENGINE_POSTGRESQL)
  1153. {
  1154. if(DBResult* result = db->storeQuery("SELECT `id`, `password`, `key` FROM `accounts`;"))
  1155. {
  1156. do
  1157. {
  1158. query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA1(result->getDataString("password"), false)) << ", `key` = " << db->escapeString(transformToSHA1(result->getDataString("key"), false)) << " WHERE `id` = " << result->getDataInt("id") << ";";
  1159. db->executeQuery(query.str());
  1160. }
  1161. while(result->next());
  1162. result->free();
  1163. }
  1164. }
  1165. else
  1166. db->executeQuery("UPDATE `accounts` SET `password` = SHA1(`password`), `key` = SHA1(`key`);");
  1167.  
  1168. registerDatabaseConfig("encryption", (int32_t)newValue);
  1169. std::cout << "> Encryption set to SHA1." << std::endl;
  1170. break;
  1171. }
  1172.  
  1173. default:
  1174. {
  1175. std::cout << "> WARNING: You cannot switch from hashed passwords to plain text, change back the passwordType in config.lua to the passwordType you were previously using." << std::endl;
  1176. break;
  1177. }
  1178. }
  1179. }
  1180. }
  1181. else
  1182. {
  1183. registerDatabaseConfig("encryption", (int32_t)newValue);
  1184. if(g_config.getBool(ConfigManager::ACCOUNT_MANAGER))
  1185. {
  1186. switch(newValue)
  1187. {
  1188. case ENCRYPTION_MD5:
  1189. {
  1190. Database* db = Database::getInstance();
  1191. DBQuery query;
  1192. query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToMD5("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1193. db->executeQuery(query.str());
  1194. break;
  1195. }
  1196.  
  1197. case ENCRYPTION_SHA1:
  1198. {
  1199. Database* db = Database::getInstance();
  1200. DBQuery query;
  1201. query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA1("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1202. db->executeQuery(query.str());
  1203. break;
  1204. }
  1205.  
  1206. default:
  1207. break;
  1208. }
  1209. }
  1210. }
  1211. }
  1212.  
  1213. void DatabaseManager::checkTriggers()
  1214. {
  1215. Database* db = Database::getInstance();
  1216. switch(db->getDatabaseEngine())
  1217. {
  1218. case DATABASE_ENGINE_MYSQL:
  1219. {
  1220. std::string triggerName[] =
  1221. {
  1222. "ondelete_accounts",
  1223. "oncreate_guilds",
  1224. "ondelete_guilds",
  1225. "oncreate_players",
  1226. "ondelete_players",
  1227. };
  1228.  
  1229. std::string triggerStatement[] =
  1230. {
  1231. "CREATE TRIGGER `ondelete_accounts` BEFORE DELETE ON `accounts` FOR EACH ROW BEGIN DELETE FROM `bans` WHERE `type` NOT IN(1, 2) AND `value` = OLD.`id`; END;",
  1232. "CREATE TRIGGER `oncreate_guilds` AFTER INSERT ON `guilds` FOR EACH ROW BEGIN INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Leader', 3, NEW.`id`); INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Vice-Leader', 2, NEW.`id`); INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES ('Member', 1, NEW.`id`); END;",
  1233. "CREATE TRIGGER `ondelete_guilds` BEFORE DELETE ON `guilds` FOR EACH ROW BEGIN UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`); END;",
  1234. "CREATE TRIGGER `oncreate_players` AFTER INSERT ON `players` FOR EACH ROW BEGIN INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10); INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10); END;",
  1235. "CREATE TRIGGER `ondelete_players` BEFORE DELETE ON `players` FOR EACH ROW BEGIN DELETE FROM `bans` WHERE `type` = 2 AND `value` = OLD.`id`; UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`; END;"
  1236. };
  1237.  
  1238. DBQuery query;
  1239. for(uint32_t i = 0; i < sizeof(triggerName) / sizeof(std::string); i++)
  1240. {
  1241. if(!triggerExists(triggerName[i]))
  1242. {
  1243. std::cout << "> Trigger: " << triggerName[i] << " does not exist, creating it..." << std::endl;
  1244. db->executeQuery(triggerStatement[i]);
  1245. }
  1246. }
  1247.  
  1248. break;
  1249. }
  1250.  
  1251. case DATABASE_ENGINE_SQLITE:
  1252. {
  1253. std::string triggerName[] =
  1254. {
  1255. "oncreate_guilds",
  1256. "oncreate_players",
  1257. "ondelete_accounts",
  1258. "ondelete_players",
  1259. "ondelete_guilds",
  1260. "oninsert_players",
  1261. "onupdate_players",
  1262. "oninsert_guilds",
  1263. "onupdate_guilds",
  1264. "ondelete_houses",
  1265. "ondelete_tiles",
  1266. "oninsert_guild_ranks",
  1267. "onupdate_guild_ranks",
  1268. "oninsert_house_lists",
  1269. "onupdate_house_lists",
  1270. "oninsert_player_depotitems",
  1271. "onupdate_player_depotitems",
  1272. "oninsert_player_skills",
  1273. "onupdate_player_skills",
  1274. "oninsert_player_storage",
  1275. "onupdate_player_storage",
  1276. "oninsert_player_viplist",
  1277. "onupdate_player_viplist",
  1278. "oninsert_account_viplist",
  1279. "onupdate_account_viplist",
  1280. "oninsert_tile_items",
  1281. "onupdate_tile_items",
  1282. "oninsert_player_spells",
  1283. "onupdate_player_spells",
  1284. "oninsert_player_deaths",
  1285. "onupdate_player_deaths",
  1286. "oninsert_killers",
  1287. "onupdate_killers",
  1288. "oninsert_environment_killers",
  1289. "onupdate_environment_killers",
  1290. "oninsert_player_killers",
  1291. "onupdate_player_killers"
  1292. };
  1293.  
  1294. std::string triggerStatement[] =
  1295. {
  1296. "CREATE TRIGGER `oncreate_guilds` \
  1297. AFTER INSERT ON `guilds` \
  1298. BEGIN \
  1299. INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Leader`, 3, NEW.`id`);\
  1300. INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Vice-Leader`, 2, NEW.`id`);\
  1301. INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Member`, 1, NEW.`id`);\
  1302. END;",
  1303.  
  1304. "CREATE TRIGGER `oncreate_players`\
  1305. AFTER INSERT\
  1306. ON `players`\
  1307. BEGIN\
  1308. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);\
  1309. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);\
  1310. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);\
  1311. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);\
  1312. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);\
  1313. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);\
  1314. INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);\
  1315. END;",
  1316. "CREATE TRIGGER `ondelete_accounts`\
  1317. BEFORE DELETE\
  1318. ON `accounts`\
  1319. FOR EACH ROW\
  1320. BEGIN\
  1321. DELETE FROM `players` WHERE `account_id` = OLD.`id`;\
  1322. DELETE FROM `account_viplist` WHERE `account_id` = OLD.`id`;\
  1323. DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;\
  1324. END;",
  1325.  
  1326. "CREATE TRIGGER `ondelete_players`\
  1327. BEFORE DELETE\
  1328. ON `players`\
  1329. FOR EACH ROW\
  1330. BEGIN\
  1331. SELECT RAISE(ROLLBACK, 'DELETE on table `players` violates foreign: `ownerid` from table `guilds`')\
  1332. WHERE (SELECT `id` FROM `guilds` WHERE `ownerid` = OLD.`id`) IS NOT NULL;\
  1333. \
  1334. DELETE FROM `account_viplist` WHERE `player_id` = OLD.`id`;\
  1335. DELETE FROM `player_viplist` WHERE `player_id` = OLD.`id` OR `vip_id` = OLD.`id`;\
  1336. DELETE FROM `player_storage` WHERE `player_id` = OLD.`id`;\
  1337. DELETE FROM `player_skills` WHERE `player_id` = OLD.`id`;\
  1338. DELETE FROM `player_items` WHERE `player_id` = OLD.`id`;\
  1339. DELETE FROM `player_depotitems` WHERE `player_id` = OLD.`id`;\
  1340. DELETE FROM `player_spells` WHERE `player_id` = OLD.`id`;\
  1341. DELETE FROM `player_killers` WHERE `player_id` = OLD.`id`;\
  1342. DELETE FROM `player_deaths` WHERE `player_id` = OLD.`id`;\
  1343. DELETE FROM `guild_invites` WHERE `player_id` = OLD.`id`;\
  1344. DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;\
  1345. UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;\
  1346. END;",
  1347. "CREATE TRIGGER `ondelete_guilds`\
  1348. BEFORE DELETE\
  1349. ON `guilds`\
  1350. FOR EACH ROW\
  1351. BEGIN\
  1352. UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);\
  1353. DELETE FROM `guild_ranks` WHERE `guild_id` = OLD.`id`;\
  1354. DELETE FROM `guild_invites` WHERE `guild_id` = OLD.`id`;\
  1355. END;",
  1356.  
  1357. "CREATE TRIGGER `oninsert_players` BEFORE INSERT ON `players` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `players` violates foreign: `account_id`') WHERE NEW.`account_id` IS NULL OR (SELECT `id` FROM `accounts` WHERE `id` = NEW.`account_id`) IS NULL; END;",
  1358. "CREATE TRIGGER `onupdate_players` BEFORE UPDATE ON `players` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `players` violates foreign: `account_id`') WHERE NEW.`account_id` IS NULL OR (SELECT `id` FROM `accounts` WHERE `id` = NEW.`account_id`) IS NULL; END;",
  1359.  
  1360. "CREATE TRIGGER `oninsert_guilds` BEFORE INSERT ON `guilds` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `guilds` violates foreign: `ownerid`') WHERE NEW.`ownerid` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`ownerid`) IS NULL; END;",
  1361. "CREATE TRIGGER `onupdate_guilds` BEFORE UPDATE ON `guilds` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `guilds` violates foreign: `ownerid`') WHERE NEW.`ownerid` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`ownerid`) IS NULL; END;",
  1362.  
  1363. "CREATE TRIGGER `ondelete_houses` BEFORE DELETE ON `houses` FOR EACH ROW BEGIN DELETE FROM `house_lists` WHERE `house_id` = OLD.`id`; END;",
  1364. "CREATE TRIGGER `ondelete_tiles` BEFORE DELETE ON `tiles` FOR EACH ROW BEGIN DELETE FROM `tile_items` WHERE `tile_id` = OLD.`id`; END;",
  1365.  
  1366. "CREATE TRIGGER `oninsert_guild_ranks` BEFORE INSERT ON `guild_ranks` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `guild_ranks` violates foreign: `guild_id`') WHERE NEW.`guild_id` IS NULL OR (SELECT `id` FROM `guilds` WHERE `id` = NEW.`guild_id`) IS NULL; END;",
  1367. "CREATE TRIGGER `onupdate_guild_ranks` BEFORE UPDATE ON `guild_ranks` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `guild_ranks` violates foreign: `guild_id`') WHERE NEW.`guild_id` IS NULL OR (SELECT `id` FROM `guilds` WHERE `id` = NEW.`guild_id`) IS NULL; END;",
  1368.  
  1369. "CREATE TRIGGER `oninsert_house_lists` BEFORE INSERT ON `house_lists` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `house_lists` violates foreign: `house_id`') WHERE NEW.`house_id` IS NULL OR (SELECT `id` FROM `houses` WHERE `id` = NEW.`house_id`) IS NULL; END;",
  1370. "CREATE TRIGGER `onupdate_house_lists` BEFORE UPDATE ON `house_lists` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `house_lists` violates foreign: `house_id`') WHERE NEW.`house_id` IS NULL OR (SELECT `id` FROM `houses` WHERE `id` = NEW.`house_id`) IS NULL; END;",
  1371.  
  1372. "CREATE TRIGGER `oninsert_player_depotitems` BEFORE INSERT ON `player_depotitems` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_depotitems` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1373. "CREATE TRIGGER `onupdate_player_depotitems` BEFORE UPDATE ON `player_depotitems` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_depotitems` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1374.  
  1375. "CREATE TRIGGER `oninsert_player_skills` BEFORE INSERT ON `player_skills` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_skills` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1376. "CREATE TRIGGER `onupdate_player_skills` BEFORE UPDATE ON `player_skills` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_skills` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1377.  
  1378. "CREATE TRIGGER `oninsert_player_storage` BEFORE INSERT ON `player_storage` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_storage` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1379. "CREATE TRIGGER `onupdate_player_storage` BEFORE UPDATE ON `player_storage` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_storage` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1380.  
  1381. "CREATE TRIGGER `oninsert_player_viplist` BEFORE INSERT ON `player_viplist` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_viplist` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; SELECT RAISE(ROLLBACK, 'INSERT on table `player_viplist` violates foreign: `vip_id`') WHERE NEW.`vip_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`vip_id`) IS NULL; END;",
  1382. "CREATE TRIGGER `onupdate_player_viplist` BEFORE UPDATE ON `player_viplist` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_viplist` violates foreign: `vip_id`') WHERE NEW.`vip_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`vip_id`) IS NULL; END;",
  1383.  
  1384. "CREATE TRIGGER `oninsert_account_viplist` BEFORE INSERT ON `account_viplist` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `account_viplist` violates foreign: `account_id`') WHERE NEW.`account_id` IS NULL OR (SELECT `id` FROM `accounts` WHERE `id` = NEW.`account_id`) IS NULL; SELECT RAISE(ROLLBACK, 'INSERT on table `account_viplist` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1385. "CREATE TRIGGER `onupdate_account_viplist` BEFORE UPDATE ON `account_viplist` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `account_viplist` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1386.  
  1387. "CREATE TRIGGER `oninsert_tile_items` BEFORE INSERT ON `tile_items` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `tile_items` violates foreign: `tile_id`') WHERE NEW.`tile_id` IS NULL OR (SELECT `id` FROM `tiles` WHERE `id` = NEW.`tile_id`) IS NULL; END;",
  1388. "CREATE TRIGGER `onupdate_tile_items` BEFORE UPDATE ON `tile_items` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `tile_items` violates foreign: `tile_id`') WHERE NEW.`tile_id` IS NULL OR (SELECT `id` FROM `tiles` WHERE `id` = NEW.`tile_id`) IS NULL; END;",
  1389.  
  1390. "CREATE TRIGGER `oninsert_player_spells` BEFORE INSERT ON `player_spells` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_spells` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1391. "CREATE TRIGGER `onupdate_player_spells` BEFORE UPDATE ON `player_spells` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_spells` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1392. "CREATE TRIGGER `oninsert_player_deaths` BEFORE INSERT ON `player_deaths` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_deaths` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1393. "CREATE TRIGGER `onupdate_player_deaths` BEFORE UPDATE ON `player_deaths` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_deaths` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; END;",
  1394.  
  1395. "CREATE TRIGGER `oninsert_killers` BEFORE INSERT ON `killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `killers` violates foreign: `death_id`') WHERE NEW.`death_id` IS NULL OR (SELECT `id` FROM `player_deaths` WHERE `id` = NEW.`death_id`) IS NULL; END;",
  1396. "CREATE TRIGGER `onupdate_killers` BEFORE UPDATE ON `killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `killers` violates foreign: `death_id`') WHERE NEW.`death_id` IS NULL OR (SELECT `id` FROM `player_deaths` WHERE `id` = NEW.`death_id`) IS NULL; END;",
  1397. "CREATE TRIGGER `oninsert_environment_killers` BEFORE INSERT ON `environment_killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `enviroment_killers` violates foreign: `kill_id`') WHERE NEW.`kill_id` IS NULL OR (SELECT `id` FROM `killers` WHERE `id` = NEW.`kill_id`) IS NULL; END;",
  1398. "CREATE TRIGGER `onupdate_environment_killers` BEFORE UPDATE ON `environment_killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `enviroment_killers` violates foreign: `kill_id`') WHERE NEW.`kill_id` IS NULL OR (SELECT `id` FROM `killers` WHERE `id` = NEW.`kill_id`) IS NULL; END;",
  1399. "CREATE TRIGGER `oninsert_player_killers` BEFORE INSERT ON `player_killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'INSERT on table `player_killers` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; SELECT RAISE(ROLLBACK, 'INSERT on table `player_killers` violates foreign: `kill_id`') WHERE NEW.`kill_id` IS NULL OR (SELECT `id` FROM `killers` WHERE `id` = NEW.`kill_id`) IS NULL; END;",
  1400. "CREATE TRIGGER `onupdate_player_killers` BEFORE UPDATE ON `player_killers` FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'UPDATE on table `player_killers` violates foreign: `player_id`') WHERE NEW.`player_id` IS NULL OR (SELECT `id` FROM `players` WHERE `id` = NEW.`player_id`) IS NULL; SELECT RAISE(ROLLBACK, 'UPDATE on table `killers` violates foreign: `kill_id`') WHERE NEW.`kill_id` IS NULL OR (SELECT `id` FROM `killers` WHERE `id` = NEW.`kill_id`) IS NULL; END;"
  1401. };
  1402.  
  1403. DBQuery query;
  1404. for(uint32_t i = 0; i < sizeof(triggerName) / sizeof(std::string); i++)
  1405. {
  1406. if(!triggerExists(triggerName[i]))
  1407. {
  1408. std::cout << "> Trigger: " << triggerName[i] << " does not exist, creating it..." << std::endl;
  1409. db->executeQuery(triggerStatement[i]);
  1410. }
  1411. }
  1412.  
  1413. break;
  1414. }
  1415.  
  1416. case DATABASE_ENGINE_POSTGRESQL:
  1417. //TODO: PostgreSQL support
  1418. break;
  1419.  
  1420. default:
  1421. break;
  1422. }
  1423. }
  1424.  
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!