API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 1st, 2012
126
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C++ 73.15 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::clog << "> Optimizing table: " << result->getDataString("TABLE_NAME") << "... ";
  44.                 query << "OPTIMIZE TABLE `" << result->getDataString("TABLE_NAME") << "`;";
  45.                 if(db->query(query.str()))
  46.                     std::clog << "[success]" << std::endl;
  47.                 else
  48.                     std::clog << "[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->query("VACUUM;"))
  61.                 break;
  62.  
  63.             std::clog << "> Optimized database." << std::endl;
  64.             return true;
  65.         }
  66.  
  67.         case DATABASE_ENGINE_POSTGRESQL:
  68.         {
  69.             if(!db->query("VACUUM FULL;"))
  70.                 break;
  71.  
  72.             std::clog << "> 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.     int32_t version = getDatabaseVersion();
  192.     if(version < 0)
  193.         return 0;
  194.  
  195.     if(version < 6 && db->getDatabaseEngine() == DATABASE_ENGINE_POSTGRESQL)
  196.     {
  197.         std::clog << "> 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::clog << "> Updating database to version: 1..." << std::endl;
  208.             if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  209.                 db->query("CREATE TABLE IF NOT EXISTS `server_config` (`config` VARCHAR(35) NOT NULL DEFAULT '', `value` INTEGER NOT NULL);");
  210.             else
  211.                 db->query("CREATE TABLE IF NOT EXISTS `server_config` (`config` VARCHAR(35) NOT NULL DEFAULT '', `value` INT NOT NULL) ENGINE = InnoDB;");
  212.  
  213.             db->query("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->query("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.                     query << "SELECT * FROM `bans`;";
  227.                     if(DBResult* result = db->storeQuery(query.str()))
  228.                     {
  229.                         do
  230.                         {
  231.                             switch(result->getDataInt("type"))
  232.                             {
  233.                                 case 1:
  234.                                     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") << ");";
  235.                                     break;
  236.  
  237.                                 case 2:
  238.                                     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") << ");";
  239.                                     break;
  240.  
  241.                                 case 3:
  242.                                     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") << ");";
  243.                                     break;
  244.  
  245.                                 case 4:
  246.                                 case 5:
  247.                                     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") << ");";
  248.                                     break;
  249.  
  250.                                 default:
  251.                                     break;
  252.                             }
  253.  
  254.                             if(query.str() != "")
  255.                             {
  256.                                 db->query(query.str());
  257.                                 query.str("");
  258.                             }
  259.                         }
  260.                         while(result->next());
  261.                         result->free();
  262.                     }
  263.  
  264.                     db->query("DROP TABLE `bans`;");
  265.                     db->query("RENAME TABLE `bans2` TO `bans`;");
  266.                 }
  267.  
  268.                 std::string queryList[] = {
  269.                     //create server_record table
  270.                     "CREATE TABLE IF NOT EXISTS `server_record` (`record` INT NOT NULL, `timestamp` BIGINT NOT NULL, PRIMARY KEY (`timestamp`)) ENGINE = InnoDB;",
  271.                     //create server_reports table
  272.                     "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;",
  273.                     //create server_motd table
  274.                     "CREATE TABLE `server_motd` (`id` INT NOT NULL AUTO_INCREMENT, `text` TEXT NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB;",
  275.                     //create global_storage table
  276.                     "CREATE TABLE IF NOT EXISTS `global_storage` (`key` INT UNSIGNED NOT NULL, `value` INT NOT NULL, PRIMARY KEY (`key`)) ENGINE = InnoDB;",
  277.                     //insert data to server_record table
  278.                     "INSERT INTO `server_record` VALUES (0, 0);",
  279.                     //insert data to server_motd table
  280.                     "INSERT INTO `server_motd` VALUES (1, 'Welcome to The Forgotten Server!');",
  281.                     //update players table
  282.                     "ALTER TABLE `players` ADD `balance` BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER `blessings`;",
  283.                     "ALTER TABLE `players` ADD `stamina` BIGINT UNSIGNED NOT NULL DEFAULT 201660000 AFTER `balance`;",
  284.                     "ALTER TABLE `players` ADD `loss_items` INT NOT NULL DEFAULT 10 AFTER `loss_skills`;",
  285.                     "ALTER TABLE `players` ADD `marriage` INT UNSIGNED NOT NULL DEFAULT 0;",
  286.                     "UPDATE `players` SET `loss_experience` = 10, `loss_mana` = 10, `loss_skills` = 10, `loss_items` = 10;",
  287.                     //update accounts table
  288.                     "ALTER TABLE `accounts` DROP `type`;",
  289.                     //update player deaths table
  290.                     "ALTER TABLE `player_deaths` DROP `is_player`;",
  291.                     //update house table
  292.                     "ALTER TABLE `houses` CHANGE `warnings` `warnings` INT NOT NULL DEFAULT 0;",
  293.                     "ALTER TABLE `houses` ADD `lastwarning` INT UNSIGNED NOT NULL DEFAULT 0;",
  294.                     //update triggers
  295.                     "DROP TRIGGER IF EXISTS `ondelete_accounts`;",
  296.                     "DROP TRIGGER IF EXISTS `ondelete_players`;",
  297.                     "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;",
  298.                     "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;"
  299.                 };
  300.                 for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  301.                     db->query(queryList[i]);
  302.             }
  303.  
  304.             return 1;
  305.         }
  306.  
  307.         case 1:
  308.         {
  309.             std::clog << "> Updating database to version: 2..." << std::endl;
  310.             if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  311.                 db->query("ALTER TABLE `players` ADD `promotion` INTEGER NOT NULL DEFAULT 0;");
  312.             else
  313.                 db->query("ALTER TABLE `players` ADD `promotion` INT NOT NULL DEFAULT 0;");
  314.  
  315.             DBResult* result;
  316.             query << "SELECT `player_id`, `value` FROM `player_storage` WHERE `key` = 30018 AND `value` > 0";
  317.             if((result = db->storeQuery(query.str())))
  318.             {
  319.                 do
  320.                 {
  321.                     query << "UPDATE `players` SET `promotion` = " << result->getDataLong("value") << " WHERE `id` = " << result->getDataInt("player_id") << ";";
  322.                     db->query(query.str());
  323.                     query.str("");
  324.                 }
  325.                 while(result->next());
  326.                 result->free();
  327.             }
  328.  
  329.             db->query("DELETE FROM `player_storage` WHERE `key` = 30018;");
  330.             db->query("ALTER TABLE `accounts` ADD `name` VARCHAR(32) NOT NULL DEFAULT '';");
  331.  
  332.             query << "SELECT `id` FROM `accounts`;";
  333.             if((result = db->storeQuery(query.str())))
  334.             {
  335.                 do
  336.                 {
  337.                     query << "UPDATE `accounts` SET `name` = '" << result->getDataInt("id") << "' WHERE `id` = " << result->getDataInt("id") << ";";
  338.                     db->query(query.str());
  339.                     query.str("");
  340.                 }
  341.                 while(result->next());
  342.                 result->free();
  343.             }
  344.  
  345.             if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  346.                 db->query("ALTER TABLE `players` ADD `deleted` INTEGER NOT NULL DEFAULT 0;");
  347.             else
  348.                 db->query("ALTER TABLE `players` ADD `deleted` INT NOT NULL DEFAULT 0;");
  349.  
  350.             if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  351.                 db->query("ALTER TABLE `player_items` CHANGE `attributes` `attributes` BLOB NOT NULL;");
  352.  
  353.             registerDatabaseConfig("db_version", 2);
  354.             return 2;
  355.         }
  356.  
  357.         case 2:
  358.         {
  359.             std::clog << "> Updating database to version: 3..." << std::endl;
  360.             db->query("UPDATE `players` SET `vocation` = `vocation` - 4 WHERE `vocation` >= 5 AND `vocation` <= 8;");
  361.  
  362.             DBResult* result;
  363.             query << "SELECT COUNT(`id`) AS `count` FROM `players` WHERE `vocation` > 4;";
  364.             if((result = db->storeQuery(query.str()))
  365.                 && result->getDataInt("count"))
  366.             {
  367.                 std::clog << "[Warning] There are still " << result->getDataInt("count") << " players with vocation above 4, please mind to update them manually." << std::endl;
  368.                 result->free();
  369.             }
  370.  
  371.             registerDatabaseConfig("db_version", 3);
  372.             return 3;
  373.         }
  374.  
  375.         case 3:
  376.         {
  377.             std::clog << "> Updating database to version: 4..." << std::endl;
  378.             db->query("ALTER TABLE `houses` ADD `name` VARCHAR(255) NOT NULL;");
  379.             if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  380.             {
  381.                 std::string queryList[] = {
  382.                     "ALTER TABLE `houses` ADD `size` INTEGER NOT NULL DEFAULT 0;",
  383.                     "ALTER TABLE `houses` ADD `town` INTEGER NOT NULL DEFAULT 0;",
  384.                     "ALTER TABLE `houses` ADD `price` INTEGER NOT NULL DEFAULT 0;",
  385.                     "ALTER TABLE `houses` ADD `rent` INTEGER NOT NULL DEFAULT 0;"
  386.                 };
  387.                 for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  388.                     db->query(queryList[i]);
  389.             }
  390.             else
  391.             {
  392.                 std::string queryList[] = {
  393.                     "ALTER TABLE `houses` ADD `size` INT UNSIGNED NOT NULL DEFAULT 0;",
  394.                     "ALTER TABLE `houses` ADD `town` INT UNSIGNED NOT NULL DEFAULT 0;",
  395.                     "ALTER TABLE `houses` ADD `price` INT UNSIGNED NOT NULL DEFAULT 0;",
  396.                     "ALTER TABLE `houses` ADD `rent` INT UNSIGNED NOT NULL DEFAULT 0;"
  397.                 };
  398.                 for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  399.                     db->query(queryList[i]);
  400.             }
  401.  
  402.             registerDatabaseConfig("db_version", 4);
  403.             return 4;
  404.         }
  405.  
  406.         case 4:
  407.         {
  408.             std::clog << "> Updating database to version: 5..." << std::endl;
  409.             db->query("ALTER TABLE `player_deaths` ADD `altkilled_by` VARCHAR(255) NOT NULL;");
  410.             registerDatabaseConfig("db_version", 5);
  411.             return 5;
  412.         }
  413.  
  414.         case 5:
  415.         {
  416.             std::clog << "> Updating database to version: 6..." << std::endl;
  417.             switch(db->getDatabaseEngine())
  418.             {
  419.                 case DATABASE_ENGINE_MYSQL:
  420.                 {
  421.                     std::string queryList[] = {
  422.                         "ALTER TABLE `global_storage` CHANGE `value` `value` VARCHAR(255) NOT NULL DEFAULT '0'",
  423.                         "ALTER TABLE `player_storage` CHANGE `value` `value` VARCHAR(255) NOT NULL DEFAULT '0'",
  424.                         "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;",
  425.                         "ALTER TABLE `tiles` ADD INDEX (`x`, `y`, `z`);",
  426.                         "ALTER TABLE `tile_items` ADD INDEX (`sid`);"
  427.                     };
  428.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  429.                         db->query(queryList[i]);
  430.  
  431.                     break;
  432.                 }
  433.  
  434.                 case DATABASE_ENGINE_SQLITE:
  435.                 {
  436.                     std::string queryList[] = {
  437.                         "ALTER TABLE `global_storage` RENAME TO `global_storage2`;",
  438.                         "CREATE TABLE `global_storage` (`key` INTEGER NOT NULL, `value` VARCHAR(255) NOT NULL DEFAULT '0', UNIQUE (`key`));",
  439.                         "INSERT INTO `global_storage` SELECT * FROM `global_storage2`;",
  440.                         "ALTER TABLE `player_storage` RENAME TO `player_storage2`;",
  441.                         "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`));",
  442.                         "INSERT INTO `player_storage` SELECT * FROM `player_storage2`;"
  443.                     };
  444.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  445.                         db->query(queryList[i]);
  446.  
  447.                     break;
  448.                 }
  449.  
  450.                 default:
  451.                     break;
  452.             }
  453.  
  454.             registerDatabaseConfig("db_version", 6);
  455.             return 6;
  456.         }
  457.  
  458.         case 6:
  459.         {
  460.             std::clog << "> Updating database to version: 7..." << std::endl;
  461.             if(g_config.getBool(ConfigManager::INGAME_GUILD_MANAGEMENT))
  462.             {
  463.                 query << "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;";
  464.                 if(DBResult* result = db->storeQuery(query.str()))
  465.                 {
  466.                     do
  467.                     {
  468.                         query << "UPDATE `guilds`, `players` SET `guilds`.`ownerid` = `players`.`id` WHERE `guilds`.`id` = " << result->getDataInt("guild_id") << " AND `players`.`rank_id` = " << result->getDataInt("id") << ";";
  469.                         db->query(query.str());
  470.                         query.str("");
  471.                     }
  472.                     while(result->next());
  473.                     result->free();
  474.                 }
  475.             }
  476.  
  477.             registerDatabaseConfig("db_version", 7);
  478.             return 7;
  479.         }
  480.  
  481.         case 7:
  482.         {
  483.             std::clog << "> Updating database version to: 8..." << std::endl;
  484.             switch(db->getDatabaseEngine())
  485.             {
  486.                 case DATABASE_ENGINE_MYSQL:
  487.                 {
  488.                     std::string queryList[] = {
  489.                         "ALTER TABLE `server_motd` CHANGE `id` `id` INT UNSIGNED NOT NULL;",
  490.                         "ALTER TABLE `server_motd` DROP PRIMARY KEY;",
  491.                         "ALTER TABLE `server_motd` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  492.                         "ALTER TABLE `server_motd` ADD UNIQUE (`id`, `world_id`);",
  493.                         "ALTER TABLE `server_record` DROP PRIMARY KEY;",
  494.                         "ALTER TABLE `server_record` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  495.                         "ALTER TABLE `server_record` ADD UNIQUE (`timestamp`, `record`, `world_id`);",
  496.                         "ALTER TABLE `server_reports` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  497.                         "ALTER TABLE `server_reports` ADD INDEX (`world_id`);",
  498.                         "ALTER TABLE `players` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  499.                         "ALTER TABLE `global_storage` DROP PRIMARY KEY;",
  500.                         "ALTER TABLE `global_storage` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  501.                         "ALTER TABLE `global_storage` ADD UNIQUE (`key`, `world_id`);",
  502.                         "ALTER TABLE `guilds` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  503.                         "ALTER TABLE `guilds` ADD UNIQUE (`name`, `world_id`);",
  504.                         "ALTER TABLE `house_lists` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  505.                         "ALTER TABLE `house_lists` ADD UNIQUE (`house_id`, `world_id`, `listid`);",
  506.                         "ALTER TABLE `houses` CHANGE `id` `id` INT NOT NULL;",
  507.                         "ALTER TABLE `houses` DROP PRIMARY KEY;",
  508.                         "ALTER TABLE `houses` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  509.                         "ALTER TABLE `houses` ADD UNIQUE (`id`, `world_id`);",
  510.                         "ALTER TABLE `tiles` CHANGE `id` `id` INT NOT NULL;",
  511.                         "ALTER TABLE `tiles` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  512.                         "ALTER TABLE `tiles` ADD UNIQUE (`id`, `world_id`);",
  513.                         "ALTER TABLE `tile_items` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0;",
  514.                         "ALTER TABLE `tile_items` ADD UNIQUE (`tile_id`, `world_id`, `sid`);"
  515.                     };
  516.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  517.                         db->query(queryList[i]);
  518.  
  519.                     break;
  520.                 }
  521.  
  522.                 case DATABASE_ENGINE_SQLITE:
  523.                 case DATABASE_ENGINE_POSTGRESQL:
  524.                 default:
  525.                 {
  526.                     //TODO
  527.                     break;
  528.                 }
  529.             }
  530.  
  531.             registerDatabaseConfig("db_version", 8);
  532.             return 8;
  533.         }
  534.  
  535.         case 8:
  536.         {
  537.             std::clog << "> Updating database to version: 9..." << std::endl;
  538.             db->query("ALTER TABLE `bans` ADD `statement` VARCHAR(255) NOT NULL;");
  539.             registerDatabaseConfig("db_version", 9);
  540.             return 9;
  541.         }
  542.  
  543.         case 9:
  544.         {
  545.             std::clog << "> Updating database to version: 10..." << std::endl;
  546.             registerDatabaseConfig("db_version", 10);
  547.             return 10;
  548.         }
  549.  
  550.         case 10:
  551.         {
  552.             std::clog << "> Updating database to version: 11..." << std::endl;
  553.             db->query("ALTER TABLE `players` ADD `description` VARCHAR(255) NOT NULL DEFAULT '';");
  554.             if(tableExists("map_storage"))
  555.             {
  556.                 db->query("ALTER TABLE `map_storage` RENAME TO `house_data`;");
  557.                 db->query("ALTER TABLE `house_data` ADD `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0 AFTER `house_id`;");
  558.             }
  559.             else if(!tableExists("house_storage"))
  560.             {
  561.                 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`)";
  562.                 if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  563.                     query << ", FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE";
  564.  
  565.                 query << ")";
  566.                 if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  567.                     query << " ENGINE = InnoDB";
  568.  
  569.                 query << ";";
  570.                 db->query(query.str());
  571.                 query.str("");
  572.             }
  573.             else
  574.                 db->query("ALTER TABLE `house_storage` RENAME TO `house_data`;");
  575.  
  576.             registerDatabaseConfig("db_version", 11);
  577.             return 11;
  578.         }
  579.  
  580.         case 11:
  581.         {
  582.             std::clog << "> Updating database to version: 12..." << std::endl;
  583.             db->query("UPDATE `players` SET `stamina` = 151200000 WHERE `stamina` > 151200000;");
  584.             db->query("UPDATE `players` SET `loss_experience` = `loss_experience` * 10, `loss_mana` = `loss_mana` * 10, `loss_skills` = `loss_skills` * 10, `loss_items` = `loss_items` * 10;");
  585.             switch(db->getDatabaseEngine())
  586.             {
  587.                 case DATABASE_ENGINE_MYSQL:
  588.                 {
  589.                     std::string queryList[] = {
  590.                         "ALTER TABLE `players` CHANGE `stamina` `stamina` INT NOT NULL DEFAULT 151200000;",
  591.                         "ALTER TABLE `players` CHANGE `loss_experience` `loss_experience` INT NOT NULL DEFAULT 100;",
  592.                         "ALTER TABLE `players` CHANGE `loss_mana` `loss_mana` INT NOT NULL DEFAULT 100;",
  593.                         "ALTER TABLE `players` CHANGE `loss_skills` `loss_skills` INT NOT NULL DEFAULT 100;",
  594.                         "ALTER TABLE `players` CHANGE `loss_items` `loss_items` INT NOT NULL DEFAULT 100;",
  595.                         "ALTER TABLE `players` ADD `loss_containers` INT NOT NULL DEFAULT 100 AFTER `loss_skills`;"
  596.                     };
  597.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  598.                         db->query(queryList[i]);
  599.  
  600.                     break;
  601.                 }
  602.  
  603.                 case DATABASE_ENGINE_SQLITE:
  604.                 case DATABASE_ENGINE_POSTGRESQL:
  605.                 default:
  606.                 {
  607.                     //TODO
  608.                     break;
  609.                 }
  610.             }
  611.  
  612.             registerDatabaseConfig("db_version", 12);
  613.             return 12;
  614.         }
  615.  
  616.         case 12:
  617.         {
  618.             std::clog << "> Updating database to version: 13..." << std::endl;
  619.             std::string queryList[] = {
  620.                 "ALTER TABLE `accounts` DROP KEY `group_id`;",
  621.                 "ALTER TABLE `players` DROP KEY `group_id`;",
  622.                 "DROP TABLE `groups`;"
  623.             };
  624.             for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  625.                 db->query(queryList[i]);
  626.  
  627.             registerDatabaseConfig("db_version", 13);
  628.             return 13;
  629.         }
  630.  
  631.         case 13:
  632.         {
  633.             std::clog << "> Updating database to version: 14..." << std::endl;
  634.             switch(db->getDatabaseEngine())
  635.             {
  636.                 case DATABASE_ENGINE_MYSQL:
  637.                 {
  638.                     std::string queryList[] = {
  639.                         "ALTER TABLE `houses` ADD `doors` INT UNSIGNED NOT NULL DEFAULT 0;",
  640.                         "ALTER TABLE `houses` ADD `beds` INT UNSIGNED NOT NULL DEFAULT 0;",
  641.                         "ALTER TABLE `houses` ADD `guild` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;"
  642.                     };
  643.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  644.                         db->query(queryList[i]);
  645.  
  646.                     break;
  647.                 }
  648.  
  649.                 case DATABASE_ENGINE_SQLITE:
  650.                 {
  651.                     std::string queryList[] = {
  652.                         "ALTER TABLE `houses` ADD `doors` INTEGER NOT NULL DEFAULT 0;",
  653.                         "ALTER TABLE `houses` ADD `beds` INTEGER NOT NULL DEFAULT 0;",
  654.                         "ALTER TABLE `houses` ADD `guild` BOOLEAN NOT NULL DEFAULT FALSE;"
  655.                     };
  656.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  657.                         db->query(queryList[i]);
  658.  
  659.                     break;
  660.                 }
  661.  
  662.                 case DATABASE_ENGINE_POSTGRESQL:
  663.                 default:
  664.                 {
  665.                     //TODO
  666.                     break;
  667.                 }
  668.             }
  669.  
  670.             registerDatabaseConfig("db_version", 14);
  671.             return 14;
  672.         }
  673.  
  674.         case 14:
  675.         {
  676.             std::clog << "> Updating database to version: 15..." << std::endl;
  677.             db->query("DROP TABLE `player_deaths`;"); //no support for moving, sorry!
  678.             switch(db->getDatabaseEngine())
  679.             {
  680.                 case DATABASE_ENGINE_MYSQL:
  681.                 {
  682.                     std::string queryList[] = {
  683. "CREATE TABLE `player_deaths`\
  684. (\
  685.     `id` INT NOT NULL AUTO_INCREMENT,\
  686.     `player_id` INT NOT NULL,\
  687.     `date` BIGINT UNSIGNED NOT NULL,\
  688.     `level` INT UNSIGNED NOT NULL,\
  689.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,\
  690.     PRIMARY KEY(`id`),\
  691.     INDEX(`date`)\
  692. ) ENGINE = InnoDB;",
  693. "CREATE TABLE `killers`\
  694. (\
  695.     `id` INT NOT NULL AUTO_INCREMENT,\
  696.     `death_id` INT NOT NULL,\
  697.     `final_hit` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE,\
  698.     PRIMARY KEY(`id`),\
  699.     FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`) ON DELETE CASCADE\
  700. ) ENGINE = InnoDB;",
  701. "CREATE TABLE `player_killers`\
  702. (\
  703.     `kill_id` INT NOT NULL,\
  704.     `player_id` INT NOT NULL,\
  705.     FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE,\
  706.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE\
  707. ) ENGINE = InnoDB;",
  708. "CREATE TABLE `environment_killers`\
  709. (\
  710.     `kill_id` INT NOT NULL,\
  711.     `name` VARCHAR(255) NOT NULL,\
  712.     FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`) ON DELETE CASCADE\
  713. ) ENGINE = InnoDB;"
  714.                     };
  715.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  716.                         db->query(queryList[i]);
  717.  
  718.                     break;
  719.                 }
  720.  
  721.                 case DATABASE_ENGINE_SQLITE:
  722.                 {
  723.                     std::string queryList[] = {
  724. "CREATE TABLE `player_deaths` (\
  725.     `id` INTEGER PRIMARY KEY,\
  726.     `player_id` INTEGER NOT NULL,\
  727.     `date` INTEGER NOT NULL,\
  728.     `level` INTEGER NOT NULL,\
  729.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  730. );",
  731. "CREATE TABLE `killers` (\
  732.     `id` INTEGER PRIMARY KEY,\
  733.     `death_id` INTEGER NOT NULL,\
  734.     `final_hit` BOOLEAN NOT NULL DEFAULT FALSE,\
  735.     FOREIGN KEY (`death_id`) REFERENCES `player_deaths` (`id`)\
  736. );",
  737. "CREATE TABLE `player_killers` (\
  738.     `kill_id` INTEGER NOT NULL,\
  739.     `player_id` INTEGER NOT NULL,\
  740.     FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`),\
  741.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  742. );",
  743. "CREATE TABLE `environment_killers` (\
  744.     `kill_id` INTEGER NOT NULL,\
  745.     `name` VARCHAR(255) NOT NULL,\
  746.     FOREIGN KEY (`kill_id`) REFERENCES `killers` (`id`)\
  747. );"
  748.                     };
  749.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  750.                         db->query(queryList[i]);
  751.  
  752.                     break;
  753.                 }
  754.  
  755.                 case DATABASE_ENGINE_POSTGRESQL:
  756.                 default:
  757.                 {
  758.                     //TODO
  759.                     break;
  760.                 }
  761.             }
  762.  
  763.             registerDatabaseConfig("db_version", 15);
  764.             return 15;
  765.         }
  766.  
  767.         case 15:
  768.         {
  769.             std::clog << "> Updating database to version: 16..." << std::endl;
  770.             switch(db->getDatabaseEngine())
  771.             {
  772.                 case DATABASE_ENGINE_MYSQL:
  773.                 {
  774.                     std::string queryList[] = {
  775.                         "ALTER TABLE `players` DROP `redskull`;",
  776.                         "ALTER TABLE `players` CHANGE `redskulltime` `redskulltime` INT NOT NULL DEFAULT 0;",
  777.                         "ALTER TABLE `killers` ADD `unjustified` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;",
  778.                         "UPDATE `players` SET `redskulltime` = 0;"
  779.                     };
  780.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  781.                         db->query(queryList[i]);
  782.  
  783.                     break;
  784.                 }
  785.  
  786.                 case DATABASE_ENGINE_SQLITE:
  787.                 {
  788.                     std::string queryList[] = {
  789.                         //we cannot DROP redskull, and redskulltime is already INTEGER
  790.                         "ALTER TABLE `killers` ADD `unjustified` BOOLEAN NOT NULL DEFAULT FALSE;",
  791.                         "UPDATE `players` SET `redskulltime` = 0;"
  792.                     };
  793.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  794.                         db->query(queryList[i]);
  795.  
  796.                     break;
  797.                 }
  798.  
  799.                 case DATABASE_ENGINE_POSTGRESQL:
  800.                 default:
  801.                 {
  802.                     //TODO
  803.                     break;
  804.                 }
  805.             }
  806.  
  807.             registerDatabaseConfig("db_version", 16);
  808.             return 16;
  809.         }
  810.  
  811.         case 16:
  812.         {
  813.             std::clog << "> Updating database to version: 17..." << std::endl;
  814.             switch(db->getDatabaseEngine())
  815.             {
  816.                 case DATABASE_ENGINE_MYSQL:
  817.                     db->query("CREATE TABLE IF NOT EXISTS `player_namelocks`\
  818. (\
  819.     `player_id` INT NOT NULL DEFAULT 0,\
  820.     `name` VARCHAR(255) NOT NULL,\
  821.     `new_name` VARCHAR(255) NOT NULL,\
  822.     `date` BIGINT NOT NULL DEFAULT 0,\
  823.     KEY (`player_id`),\
  824.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE\
  825. ) ENGINE = InnoDB;");
  826.  
  827.                     break;
  828.                 case DATABASE_ENGINE_SQLITE:
  829.                     db->query("CREATE TABLE IF NOT EXISTS `player_namelocks` (\
  830.     `player_id` INTEGER NOT NULL,\
  831.     `name` VARCHAR(255) NOT NULL,\
  832.     `new_name` VARCHAR(255) NOT NULL,\
  833.     `date` INTEGER NOT NULL DEFAULT 0,\
  834.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  835. );");
  836.                     break;
  837.                 default:
  838.                     //TODO
  839.                     break;
  840.             }
  841.  
  842.             registerDatabaseConfig("db_version", 17);
  843.             return 17;
  844.         }
  845.  
  846.         case 17:
  847.         {
  848.             std::clog << "> Updating database to version: 18..." << std::endl;
  849.             switch(db->getDatabaseEngine())
  850.             {
  851.                 case DATABASE_ENGINE_MYSQL:
  852.                 {
  853.                     std::string queryList[] = {
  854.                         "ALTER TABLE `player_depotitems` DROP KEY `player_id`;",
  855.                         "ALTER TABLE `player_depotitems` DROP `depot_id`;",
  856.                         "ALTER TABLE `player_depotitems` ADD KEY (`player_id`);",
  857.                         "ALTER TABLE `house_data` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  858.                         "ALTER TABLE `house_lists` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  859.                         "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE;",
  860.                         "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE;",
  861.                         "ALTER TABLE `tiles` ADD `house_id` INT UNSIGNED NOT NULL AFTER `world_id`;",
  862.                         "ALTER TABLE `tiles` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE;",
  863.                         "ALTER TABLE `houses` ADD `clear` TINYINT(1) UNSIGNED NOT NULL DEFAULT FALSE;"
  864.                     };
  865.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  866.                         db->query(queryList[i]);
  867.  
  868.                     break;
  869.                 }
  870.  
  871.                 case DATABASE_ENGINE_SQLITE:
  872.                 {
  873.                     std::string queryList[] = {
  874.                         "ALTER TABLE `house_data` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  875.                         "ALTER TABLE `house_lists` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  876.                         "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`player_id`) REFERENCES `players`(`id`);",
  877.                         "ALTER TABLE `guild_invites` ADD FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`);",
  878.                         "ALTER TABLE `tiles` ADD `house_id` INTEGER NOT NULL;",
  879.                         "ALTER TABLE `tiles` ADD FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`);",
  880.                         "ALTER TABLE `houses` ADD `clear` BOOLEAN NOT NULL DEFAULT FALSE;"
  881.                     };
  882.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  883.                         db->query(queryList[i]);
  884.  
  885.                     break;
  886.                 }
  887.  
  888.                 default:
  889.                     //TODO
  890.                     break;
  891.             }
  892.  
  893.             registerDatabaseConfig("db_version", 18);
  894.             return 18;
  895.         }
  896.  
  897.         case 18:
  898.         {
  899.             std::clog << "> Updating database to version: 19..." << std::endl;
  900.             switch(db->getDatabaseEngine())
  901.             {
  902.                 case DATABASE_ENGINE_MYSQL:
  903.                 {
  904.                     std::string queryList[] = {
  905.                         "ALTER TABLE `houses` ADD `tiles` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `beds`;",
  906.                         "CREATE TABLE `house_auctions`\
  907. (\
  908.     `house_id` INT UNSIGNED NOT NULL,\
  909.     `world_id` TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,\
  910.     `player_id` INT NOT NULL,\
  911.     `bid` INT UNSIGNED NOT NULL DEFAULT 0,\
  912.     `limit` INT UNSIGNED NOT NULL DEFAULT 0,\
  913.     `endtime` BIGINT UNSIGNED NOT NULL DEFAULT 0,\
  914.     UNIQUE (`house_id`, `world_id`),\
  915.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses`(`id`, `world_id`) ON DELETE CASCADE,\
  916.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE\
  917. ) ENGINE = InnoDB;"
  918.                     };
  919.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  920.                         db->query(queryList[i]);
  921.  
  922.                     break;
  923.                 }
  924.  
  925.                 case DATABASE_ENGINE_SQLITE:
  926.                 {
  927.                     std::string queryList[] = {
  928.                         "ALTER TABLE `houses` ADD `tiles` INTEGER NOT NULL DEFAULT 0;",
  929.                         "CREATE TABLE `house_auctions` (\
  930.     `house_id` INTEGER NOT NULL,\
  931.     `world_id` INTEGER NOT NULL DEFAULT 0,\
  932.     `player_id` INTEGER NOT NULL,\
  933.     `bid` INTEGER NOT NULL DEFAULT 0,\
  934.     `limit` INTEGER NOT NULL DEFAULT 0,\
  935.     `endtime` INTEGER NOT NULL DEFAULT 0,\
  936.     UNIQUE (`house_id`, `world_id`),\
  937.     FOREIGN KEY (`house_id`, `world_id`) REFERENCES `houses` (`id`, `world_id`)\
  938.     FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  939. );"
  940.                     };
  941.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  942.                         db->query(queryList[i]);
  943.  
  944.                     break;
  945.                 }
  946.  
  947.                 default:
  948.                     //TODO
  949.                     break;
  950.             }
  951.  
  952.             registerDatabaseConfig("db_version", 19);
  953.             return 19;
  954.         }
  955.  
  956.         case 19:
  957.         {
  958.             std::clog << "> Updating database to version: 20..." << std::endl;
  959.             switch(db->getDatabaseEngine())
  960.             {
  961.                 case DATABASE_ENGINE_MYSQL:
  962.                 {
  963.                     std::string queryList[] = {
  964.                         "ALTER TABLE `players` CHANGE `redskulltime` `skulltime` INT NOT NULL DEFAULT 0;",
  965.                         "ALTER TABLE `players` ADD `skull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER `save`;"
  966.                     };
  967.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  968.                         db->query(queryList[i]);
  969.  
  970.                     break;
  971.                 }
  972.  
  973.                 case DATABASE_ENGINE_SQLITE:
  974.                 {
  975.                     std::string queryList[] = {
  976.                         "ALTER TABLE `players` ADD `skulltime` INTEGER NOT NULL DEFAULT 0;",
  977.                         "ALTER TABLE `players` ADD `skull` INTEGER NOT NULL DEFAULT 0;",
  978.                         "UPDATE `players` SET `skulltime` = `redskulltime`, `redskulltime` = 0;"
  979.                     };
  980.                     for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  981.                         db->query(queryList[i]);
  982.  
  983.                     break;
  984.                 }
  985.  
  986.                 default:
  987.                     //TODO
  988.                     break;
  989.             }
  990.  
  991.  
  992.             registerDatabaseConfig("db_version", 20);
  993.             return 20;
  994.         }
  995.  
  996.         case 20:
  997.         {
  998.             std::clog << "> Updating database to version: 21..." << std::endl;
  999.             std::string queryList[] = {
  1000.                 "UPDATE `bans` SET `type` = 3 WHERE `type` = 5;",
  1001.                 "UPDATE `bans` SET `param` = 2 WHERE `type` = 2;",
  1002.                 "UPDATE `bans` SET `param` = 0 WHERE `type` IN (3,4);"
  1003.             };
  1004.             for(uint32_t i = 0; i < sizeof(queryList) / sizeof(std::string); ++i)
  1005.                 db->query(queryList[i]);
  1006.  
  1007.             registerDatabaseConfig("db_version", 21);
  1008.             return 21;
  1009.         }
  1010.  
  1011.         case 21:
  1012.         {
  1013.             std::clog << "> Updating database to version: 22..." << std::endl;
  1014.             switch(db->getDatabaseEngine())
  1015.             {
  1016.                 case DATABASE_ENGINE_MYSQL:
  1017.                     db->query("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;");
  1018.                     break;
  1019.                 case DATABASE_ENGINE_SQLITE:
  1020.                     db->query("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`));");
  1021.                     break;
  1022.                 default:
  1023.                     break;
  1024.             }
  1025.  
  1026.             registerDatabaseConfig("db_version", 22);
  1027.             return 22;
  1028.         }
  1029.  
  1030.         case 22:
  1031.         {
  1032.             std::clog << "> Updating database to version 23..." << std::endl;
  1033.             if(g_config.getBool(ConfigManager::ACCOUNT_MANAGER))
  1034.             {
  1035.                 query << "SELECT `id`, `key` FROM `accounts` WHERE `key` ";
  1036.                 if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  1037.                     query << "NOT LIKE";
  1038.                 else
  1039.                     query << "!=";
  1040.  
  1041.                 query << " '0';";
  1042.                 if(DBResult* result = db->storeQuery(query.str()))
  1043.                 {
  1044.                     do
  1045.                     {
  1046.                         std::string key = result->getDataString("key");
  1047.                         _encrypt(key, false);
  1048.  
  1049.                         query.str("");
  1050.                         query << "UPDATE `accounts` SET `key` = " << db->escapeString(key) << " WHERE `id` = " << result->getDataInt("id") << db->getUpdateLimiter();
  1051.                         db->query(query.str());
  1052.                     }
  1053.                     while(result->next());
  1054.                     result->free();
  1055.                 }
  1056.             }
  1057.  
  1058.             query.str("");
  1059.             query << "DELETE FROM `server_config` WHERE `config` " << db->getStringComparer() << "'password_type';";
  1060.             db->query(query.str());
  1061.  
  1062.             registerDatabaseConfig("encryption", g_config.getNumber(ConfigManager::ENCRYPTION));
  1063.             registerDatabaseConfig("db_version", 23);
  1064.             return 23;
  1065.         }
  1066.  
  1067.         case 23:
  1068.         {
  1069.             std::clog << "> Updating database to version 24..." << std::endl;
  1070.             query << "ALTER TABLE `guilds` ADD `checkdata` ";
  1071.             if(db->getDatabaseEngine() == DATABASE_ENGINE_SQLITE)
  1072.                 query << "INTEGER NOT NULL;";
  1073.             else
  1074.                 query << "INT NOT NULL AFTER `creationdata`;";
  1075.  
  1076.             db->query(query.str());
  1077.             query.str("");
  1078.             registerDatabaseConfig("db_version", 24);
  1079.             return 24;
  1080.         }
  1081.  
  1082.         case 24:
  1083.         {
  1084.             std::clog << "> Updating database to version 25..." << std::endl;
  1085.             switch(db->getDatabaseEngine())
  1086.             {
  1087.                 case DATABASE_ENGINE_SQLITE:
  1088.                 {
  1089.                     query << "DROP TABLE `server_config`;";
  1090.                     db->query(query.str());
  1091.  
  1092.                     query << "CREATE TABLE `server_config` (`config` VARCHAR(35) NOT NULL DEFAULT '', `value` VARCHAR(255) NOT NULL DEFAULT '', UNIQUE (`config`));";
  1093.                     registerDatabaseConfig("encryption", g_config.getNumber(ConfigManager::ENCRYPTION));
  1094.                     break;
  1095.                 }
  1096.  
  1097.                 case DATABASE_ENGINE_MYSQL:
  1098.                 {
  1099.                     query << "ALTER TABLE `server_config` CHANGE `value` `value` VARCHAR(255) NOT NULL DEFAULT '';";
  1100.                     break;
  1101.                 }
  1102.  
  1103.                 default:
  1104.                     break;
  1105.             }
  1106.  
  1107.             db->query(query.str());
  1108.             query.str("");
  1109.  
  1110.             registerDatabaseConfig("db_version", 25);
  1111.             return 25;
  1112.         }
  1113.  
  1114.         case 25:
  1115.         {
  1116.             std::clog << "> Updating database to version 26..." << std::endl;
  1117.             switch(db->getDatabaseEngine())
  1118.             {
  1119.                 case DATABASE_ENGINE_SQLITE:
  1120.                 {
  1121.                     query << "ALTER TABLE `accounts` ADD `salt` VARCHAR(40) NOT NULL DEFAULT '';";
  1122.                     break;
  1123.                 }
  1124.  
  1125.                 case DATABASE_ENGINE_MYSQL:
  1126.                 {
  1127.                     query << "ALTER TABLE `accounts` ADD `salt` VARCHAR(40) NOT NULL DEFAULT '' AFTER `password`;";
  1128.                     break;
  1129.                 }
  1130.  
  1131.                 default:
  1132.                     break;
  1133.             }
  1134.  
  1135.             db->query(query.str());
  1136.             query.str("");
  1137.  
  1138.             registerDatabaseConfig("db_version", 26);
  1139.             return 26;
  1140.         }
  1141.  
  1142.         case 26:
  1143.         {
  1144.             std::clog << "> Updating database to version 27..." << std::endl;
  1145.             if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  1146.             {
  1147.                 query << "ALTER TABLE `player_storage` CHANGE `key` `key` VARCHAR(32) NOT NULL DEFAULT '0'";
  1148.                 db->query(query.str());
  1149.                 query.str("");
  1150.  
  1151.                 query << "ALTER TABLE `global_storage` CHANGE `key` `key` VARCHAR(32) NOT NULL DEFAULT '0'";
  1152.                 db->query(query.str());
  1153.                 query.str("");
  1154.             }
  1155.  
  1156.             registerDatabaseConfig("db_version", 27);
  1157.             return 27;
  1158.         }
  1159.  
  1160.         case 27:
  1161.         {
  1162.             std::clog << "> Updating database to version 28..." << std::endl;
  1163.             if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  1164.             {
  1165.                 query << "ALTER TABLE `players` ADD `currmount` INT NOT NULL DEFAULT 0 AFTER `lookaddons`;";
  1166.                 db->query(query.str());
  1167.                 query.str("");
  1168.             }
  1169.  
  1170.             registerDatabaseConfig("db_version", 28);
  1171.             return 28;
  1172.         }
  1173.  
  1174.         case 28:
  1175.         {
  1176.             std::clog << "> Updating database to version 29..." << std::endl;
  1177.             switch(db->getDatabaseEngine())
  1178.             {
  1179.                 case DATABASE_ENGINE_SQLITE:
  1180.                 {
  1181.                     query << "ALTER TABLE `players` ADD `lookmount` INT NOT NULL DEFAULT 0;";
  1182.                     break;
  1183.                 }
  1184.  
  1185.                 case DATABASE_ENGINE_MYSQL:
  1186.                 {
  1187.                     query << "ALTER TABLE `players` CHANGE `currmount` `lookmount` INT NOT NULL DEFAULT 0";
  1188.                     break;
  1189.                 }
  1190.  
  1191.                 default:
  1192.                     break;
  1193.             }
  1194.  
  1195.             db->query(query.str());
  1196.             query.str("");
  1197.  
  1198.             registerDatabaseConfig("db_version", 29);
  1199.             return 29;
  1200.         }
  1201.  
  1202.         case 29:
  1203.         {
  1204.             std::clog << "> Updating database to version 30..." << std::endl;
  1205.             switch(db->getDatabaseEngine())
  1206.             {
  1207.                 case DATABASE_ENGINE_SQLITE:
  1208.                 {
  1209.                     query << "CREATE TABLE `tile_store` ( `house_id` INTEGER NOT NULL, `world_id` INTEGER NOT NULL DEFAULT 0, `data` LONGBLOB NOT NULL, FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) );";
  1210.                     break;
  1211.                 }
  1212.  
  1213.                 case DATABASE_ENGINE_MYSQL:
  1214.                 {
  1215.                     query << "CREATE TABLE `tile_store` ( `house_id` INT UNSIGNED NOT NULL, `world_id` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0, `data` LONGBLOB NOT NULL, FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE ) ENGINE = InnoDB;";
  1216.                     break;
  1217.                 }
  1218.  
  1219.                 default:
  1220.                     break;
  1221.             }
  1222.  
  1223.             db->query(query.str());
  1224.             query.str("");
  1225.  
  1226.             registerDatabaseConfig("db_version", 30);
  1227.             return 30;
  1228.         }
  1229.  
  1230.         case 30:
  1231.         {
  1232.             std::clog << "> Updating database to version 31..." << std::endl;
  1233.             switch(db->getDatabaseEngine())
  1234.             {
  1235.                 case DATABASE_ENGINE_SQLITE:
  1236.                 {
  1237.                     query << "ALTER TABLE `players` ADD `pvp_blessing` BOOLEAN NOT NULL DEFAULT FALSE;";
  1238.                     break;
  1239.                 }
  1240.  
  1241.                 case DATABASE_ENGINE_MYSQL:
  1242.                 {
  1243.                     query << "ALTER TABLE `players` ADD `pvp_blessing` TINYINT(1) NOT NULL DEFAULT 0 AFTER `blessings`;";
  1244.                     break;
  1245.                 }
  1246.  
  1247.                 default:
  1248.                     break;
  1249.             }
  1250.  
  1251.             db->query(query.str());
  1252.             query.str("");
  1253.  
  1254.             registerDatabaseConfig("db_version", 31);
  1255.             return 31;
  1256.         }
  1257.  
  1258.         case 31:
  1259.         {
  1260.             std::clog << "> Updating database to version 32..." << std::endl;
  1261.             if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  1262.             {
  1263.                 query << "CREATE TABLE IF NOT EXISTS `player_statements`\
  1264. (\
  1265.     `id` INT NOT NULL AUTO_INCREMENT,\
  1266.     `player_id` INT NOT NULL,\
  1267.     `channel_id` INT NOT NULL DEFAULT 0,\
  1268.     `text` VARCHAR (255) NOT NULL,\
  1269.     `date` BIGINT NOT NULL DEFAULT 0,\
  1270.     PRIMARY KEY (`id`), KEY (`player_id`), KEY (`channel_id`),\
  1271.     FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE\
  1272. ) ENGINE = InnoDB;";
  1273.  
  1274.                 db->query(query.str());
  1275.                 query.str("");
  1276.  
  1277.                 query << "CREATE TABLE IF NOT EXISTS `guild_wars`\
  1278. (\
  1279.     `id` INT NOT NULL AUTO_INCREMENT,\
  1280.     `guild_id` INT NOT NULL,\
  1281.     `enemy_id` INT NOT NULL,\
  1282.     `begin` BIGINT NOT NULL DEFAULT 0,\
  1283.     `end` BIGINT NOT NULL DEFAULT 0,\
  1284.     `frags` INT UNSIGNED NOT NULL DEFAULT 0,\
  1285.     `payment` BIGINT UNSIGNED NOT NULL DEFAULT 0,\
  1286.     `guild_kills` INT UNSIGNED NOT NULL DEFAULT 0,\
  1287.     `enemy_kills` INT UNSIGNED NOT NULL DEFAULT 0,\
  1288.     `status` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,\
  1289.     PRIMARY KEY (`id`), KEY `status` (`status`),\
  1290.     KEY `guild_id` (`guild_id`), KEY `enemy_id` (`enemy_id`),\
  1291.     FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,\
  1292.     FOREIGN KEY (`enemy_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE\
  1293. ) ENGINE=InnoDB;";
  1294.  
  1295.                 db->query(query.str());
  1296.                 query.str("");
  1297.  
  1298.                 query << "CREATE TABLE IF NOT EXISTS `guild_kills`\
  1299. (\
  1300.     `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,\
  1301.     `guild_id` INT NOT NULL,\
  1302.     `war_id` INT NOT NULL,\
  1303.     `death_id` INT NOT NULL,\
  1304.     FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE,\
  1305.     FOREIGN KEY (`war_id`) REFERENCES `guild_wars`(`id`) ON DELETE CASCADE,\
  1306.     FOREIGN KEY (`death_id`) REFERENCES `player_deaths`(`id`) ON DELETE CASCADE\
  1307. ) ENGINE = InnoDB;";
  1308.  
  1309.                 db->query(query.str());
  1310.                 query.str("");
  1311.  
  1312.                 query << "ALTER TABLE `killers` ADD `war` INT NOT NULL DEFAULT 0;";
  1313.                 db->query(query.str());
  1314.                 query.str("");
  1315.  
  1316.                 query << "ALTER TABLE `guilds` ADD `balance` BIGINT UNSIGNED NOT NULL AFTER `motd`;";
  1317.                 db->query(query.str());
  1318.                 query.str("");
  1319.             }
  1320.  
  1321.             registerDatabaseConfig("db_version", 32);
  1322.             return 32;
  1323.         }
  1324.  
  1325.         case 32:
  1326.         {
  1327.             std::clog << "> Updating database to version 33..." << std::endl;
  1328.             if(db->getDatabaseEngine() == DATABASE_ENGINE_MYSQL)
  1329.             {
  1330.                 query << "ALTER TABLE `bans` DROP `reason`;";
  1331.                 db->query(query.str());
  1332.                 query.str("");
  1333.  
  1334.                 query << "ALTER TABLE `bans` DROP `action`;";
  1335.                 db->query(query.str());
  1336.                 query.str("");
  1337.  
  1338.                 query << "ALTER TABLE `bans` DROP `statement`;";
  1339.                 db->query(query.str());
  1340.                 query.str("");
  1341.             }
  1342.  
  1343.             registerDatabaseConfig("db_version", 33);
  1344.             return 33;
  1345.         }
  1346.  
  1347.         case 33:
  1348.         {
  1349.             std::clog << "> Updating database to version 34..." << std::endl;
  1350.             switch(db->getDatabaseEngine())
  1351.             {
  1352.                 case DATABASE_ENGINE_MYSQL:
  1353.                 {
  1354.                     db->query("CREATE TABLE `market_offers` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `player_id` INT NOT NULL, `sale` TINYINT(1) NOT NULL DEFAULT 0, `itemtype` INT UNSIGNED NOT NULL, `amount` SMALLINT UNSIGNED NOT NULL, `created` BIGINT UNSIGNED NOT NULL, `anonymous` TINYINT(1) NOT NULL DEFAULT 0, `price` INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY(`sale`, `itemtype`), KEY(`created`), FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE) ENGINE = InnoDB;");
  1355.                     db->query("CREATE TABLE `market_history` (`id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `player_id` INT NOT NULL, `sale` TINYINT(1) NOT NULL DEFAULT 0, `itemtype` INT UNSIGNED NOT NULL, `amount` SMALLINT UNSIGNED NOT NULL, `price` INT UNSIGNED NOT NULL DEFAULT 0, `expires_at` BIGINT UNSIGNED NOT NULL, `inserted` BIGINT UNSIGNED NOT NULL, `state` TINYINT(1) UNSIGNED NOT NULL, PRIMARY KEY(`id`), KEY(`player_id`, `sale`), FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE) ENGINE = InnoDB;");
  1356.                     break;
  1357.                 }
  1358.  
  1359.                 case DATABASE_ENGINE_SQLITE:
  1360.                 {
  1361.                     db->query("CREATE TABLE `market_offers` (`id` INTEGER PRIMARY KEY NOT NULL, `player_id` INTEGER NOT NULL, `sale` BOOLEAN NOT NULL DEFAULT 0, `itemtype` UNSIGNED INTEGER NOT NULL, `amount` UNSIGNED INTEGER NOT NULL, `created` UNSIGNED INTEGER NOT NULL, `anonymous` BOOLEAN NOT NULL DEFAULT 0, `price` UNSIGNED INTEGER NOT NULL DEFAULT 0, FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE);");
  1362.                     db->query("CREATE INDEX market_offers_idx ON market_offers(created);");
  1363.                     db->query("CREATE INDEX market_offers_idx2 ON market_offers(sale, itemtype);");
  1364.                     db->query("CREATE TABLE `market_history` (`id` INTEGER PRIMARY KEY NOT NULL, `player_id` INTEGER NOT NULL, `sale` BOOLEAN NOT NULL DEFAULT 0, `itemtype` UNSIGNED INTEGER NOT NULL, `amount` UNSIGNED INTEGER NOT NULL, `price` UNSIGNED INTEGER NOT NULL DEFAULT 0, `expires_at` UNSIGNED INTEGER NOT NULL, `inserted` UNSIGNED INTEGER NOT NULL, `state` UNSIGNED INTEGER NOT NULL, FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE);");
  1365.                     db->query("CREATE INDEX market_history_idx ON market_history(player_id, sale);");
  1366.                     break;
  1367.                 }
  1368.  
  1369.                 default: break;
  1370.             }
  1371.  
  1372.             registerDatabaseConfig("db_version", 34);
  1373.             return 34;
  1374.         }
  1375.        
  1376.         case 34:
  1377.         {
  1378.             std::clog << "> Updating database to version 35..." << std::endl;
  1379.             switch(db->getDatabaseEngine())
  1380.             {
  1381.                 case DATABASE_ENGINE_SQLITE:
  1382.                 {
  1383.                     db->query("CREATE TABLE IF NOT EXISTS `guild_wars` (\
  1384.                         `id` INTEGER NOT NULL,\
  1385.                         `guild_id` INT NOT NULL,\
  1386.                         `enemy_id` INT NOT NULL,\
  1387.                         `begin` BIGINT NOT NULL DEFAULT '0',\
  1388.                         `end` BIGINT NOT NULL DEFAULT '0',\
  1389.                         `frags` INT NOT NULL DEFAULT '0',\
  1390.                         `payment` BIGINT NOT NULL DEFAULT '0',\
  1391.                         `guild_kills` INT NOT NULL DEFAULT '0',\
  1392.                         `enemy_kills` INT NOT NULL DEFAULT '0',\
  1393.                         `status` TINYINT(1) NOT NULL DEFAULT '0',\
  1394.                         PRIMARY KEY (`id`),\
  1395.                         FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`),\
  1396.                         FOREIGN KEY (`enemy_id`) REFERENCES `guilds`(`id`)\
  1397.                         );");
  1398.  
  1399.                     db->query("CREATE TABLE IF NOT EXISTS `guild_kills` (\
  1400.                         `id` INT NOT NULL PRIMARY KEY,\
  1401.                         `guild_id` INT NOT NULL,\
  1402.                         `war_id` INT NOT NULL,\
  1403.                         `death_id` INT NOT NULL\
  1404.                     );");
  1405.                    
  1406.                     db->query("CREATE TABLE IF NOT EXISTS `player_statements` (\
  1407.                         `id` INTEGER PRIMARY KEY,\
  1408.                         `player_id` INTEGER NOT NULL,\
  1409.                         `channel_id` INTEGER NOT NULL DEFAULT `0`,\
  1410.                         `text` VARCHAR (255) NOT NULL,\
  1411.                         `date` INTEGER NOT NULL DEFAULT `0`,\
  1412.                         FOREIGN KEY (`player_id`) REFERENCES `players` (`id`)\
  1413.                         );");
  1414.  
  1415.                     db->query("ALTER TABLE `guilds` ADD `balance` BIGINT NOT NULL DEFAULT '0';");
  1416.                     db->query("ALTER TABLE `killers` ADD `war` BIGINT NOT NULL DEFAULT 0;");
  1417.                     break;
  1418.                 }
  1419.  
  1420.                 default: break;
  1421.             }
  1422.  
  1423.             registerDatabaseConfig("db_version", 35);
  1424.             return 35;
  1425.         }
  1426.  
  1427.         default:
  1428.             break;
  1429.     }
  1430.  
  1431.     return 0;
  1432. }
  1433.  
  1434. bool DatabaseManager::getDatabaseConfig(std::string config, int32_t &value)
  1435. {
  1436.     value = 0;
  1437.  
  1438.     Database* db = Database::getInstance();
  1439.     DBResult* result;
  1440.  
  1441.     DBQuery query;
  1442.     query << "SELECT `value` FROM `server_config` WHERE `config` = " << db->escapeString(config) << ";";
  1443.     if(!(result = db->storeQuery(query.str())))
  1444.         return false;
  1445.  
  1446.     value = atoi(result->getDataString("value").c_str());
  1447.     result->free();
  1448.     return true;
  1449. }
  1450.  
  1451. bool DatabaseManager::getDatabaseConfig(std::string config, std::string &value)
  1452. {
  1453.     value = "";
  1454.  
  1455.     Database* db = Database::getInstance();
  1456.     DBResult* result;
  1457.  
  1458.     DBQuery query;
  1459.     query << "SELECT `value` FROM `server_config` WHERE `config` = " << db->escapeString(config) << ";";
  1460.     if(!(result = db->storeQuery(query.str())))
  1461.         return false;
  1462.  
  1463.     value = result->getDataString("value");
  1464.     result->free();
  1465.     return true;
  1466. }
  1467.  
  1468. void DatabaseManager::registerDatabaseConfig(std::string config, int32_t value)
  1469. {
  1470.     Database* db = Database::getInstance();
  1471.     DBQuery query;
  1472.  
  1473.     int32_t tmp = 0;
  1474.     if(!getDatabaseConfig(config, tmp))
  1475.         query << "INSERT INTO `server_config` VALUES (" << db->escapeString(config) << ", '" << value << "');";
  1476.     else
  1477.         query << "UPDATE `server_config` SET `value` = '" << value << "' WHERE `config` = " << db->escapeString(config) << ";";
  1478.  
  1479.     db->query(query.str());
  1480. }
  1481.  
  1482. void DatabaseManager::registerDatabaseConfig(std::string config, std::string value)
  1483. {
  1484.     Database* db = Database::getInstance();
  1485.     DBQuery query;
  1486.  
  1487.     std::string tmp;
  1488.     if(!getDatabaseConfig(config, tmp))
  1489.         query << "INSERT INTO `server_config` VALUES (" << db->escapeString(config) << ", " << db->escapeString(value) << ");";
  1490.     else
  1491.         query << "UPDATE `server_config` SET `value` = " << db->escapeString(value) << " WHERE `config` = " << db->escapeString(config) << ";";
  1492.  
  1493.     db->query(query.str());
  1494. }
  1495.  
  1496. void DatabaseManager::checkEncryption()
  1497. {
  1498.     Encryption_t newValue = (Encryption_t)g_config.getNumber(ConfigManager::ENCRYPTION);
  1499.     int32_t value = (int32_t)ENCRYPTION_PLAIN;
  1500.     if(getDatabaseConfig("encryption", value))
  1501.     {
  1502.         if(newValue != (Encryption_t)value)
  1503.         {
  1504.             switch(newValue)
  1505.             {
  1506.                 case ENCRYPTION_MD5:
  1507.                 {
  1508.                     if((Encryption_t)value != ENCRYPTION_PLAIN)
  1509.                     {
  1510.                         std::clog << "> WARNING: You cannot change the encryption to MD5, change it back in config.lua." << std::endl;
  1511.                         return;
  1512.                     }
  1513.  
  1514.                     Database* db = Database::getInstance();
  1515.                     DBQuery query;
  1516.                     if(db->getDatabaseEngine() != DATABASE_ENGINE_MYSQL && db->getDatabaseEngine() != DATABASE_ENGINE_POSTGRESQL)
  1517.                     {
  1518.                         query << "SELECT `id`, `password`, `key` FROM `accounts`;";
  1519.                         if(DBResult* result = db->storeQuery(query.str()))
  1520.                         {
  1521.                             do
  1522.                             {
  1523.                                 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") << ";";
  1524.                                 db->query(query.str());
  1525.                             }
  1526.                             while(result->next());
  1527.                             result->free();
  1528.                         }
  1529.                     }
  1530.                     else
  1531.                         db->query("UPDATE `accounts` SET `password` = MD5(`password`), `key` = MD5(`key`);");
  1532.  
  1533.                     registerDatabaseConfig("encryption", (int32_t)newValue);
  1534.                     std::clog << "> Encryption updated to MD5." << std::endl;
  1535.                     break;
  1536.                 }
  1537.  
  1538.                 case ENCRYPTION_SHA1:
  1539.                 {
  1540.                     if((Encryption_t)value != ENCRYPTION_PLAIN)
  1541.                     {
  1542.                         std::clog << "> WARNING: You cannot change the encryption to SHA1, change it back in config.lua." << std::endl;
  1543.                         return;
  1544.                     }
  1545.  
  1546.                     Database* db = Database::getInstance();
  1547.                     DBQuery query;
  1548.                     if(db->getDatabaseEngine() != DATABASE_ENGINE_MYSQL && db->getDatabaseEngine() != DATABASE_ENGINE_POSTGRESQL)
  1549.                     {
  1550.                         query << "SELECT `id`, `password`, `key` FROM `accounts`;";
  1551.                         if(DBResult* result = db->storeQuery(query.str()))
  1552.                         {
  1553.                             do
  1554.                             {
  1555.                                 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") << ";";
  1556.                                 db->query(query.str());
  1557.                             }
  1558.                             while(result->next());
  1559.                             result->free();
  1560.                         }
  1561.                     }
  1562.                     else
  1563.                         db->query("UPDATE `accounts` SET `password` = SHA1(`password`), `key` = SHA1(`key`);");
  1564.  
  1565.                     registerDatabaseConfig("encryption", (int32_t)newValue);
  1566.                     std::clog << "> Encryption set to SHA1." << std::endl;
  1567.                     break;
  1568.                 }
  1569.  
  1570.                 case ENCRYPTION_SHA256:
  1571.                 {
  1572.                     if((Encryption_t)value != ENCRYPTION_PLAIN)
  1573.                     {
  1574.                         std::clog << "> WARNING: You cannot change the encryption to SHA256, change it back in config.lua." << std::endl;
  1575.                         return;
  1576.                     }
  1577.  
  1578.                     Database* db = Database::getInstance();
  1579.                     DBQuery query;
  1580.  
  1581.                     query << "SELECT `id`, `password`, `key` FROM `accounts`;";
  1582.                     if(DBResult* result = db->storeQuery(query.str()))
  1583.                     {
  1584.                         do
  1585.                         {
  1586.                             query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA256(result->getDataString("password"), false)) << ", `key` = " << db->escapeString(transformToSHA256(result->getDataString("key"), false)) << " WHERE `id` = " << result->getDataInt("id") << ";";
  1587.                             db->query(query.str());
  1588.                         }
  1589.                         while(result->next());
  1590.                         result->free();
  1591.                     }
  1592.  
  1593.                     registerDatabaseConfig("encryption", (int32_t)newValue);
  1594.                     std::clog << "> Encryption set to SHA256." << std::endl;
  1595.                     break;
  1596.                 }
  1597.  
  1598.                 case ENCRYPTION_SHA512:
  1599.                 {
  1600.                     if((Encryption_t)value != ENCRYPTION_PLAIN)
  1601.                     {
  1602.                         std::clog << "> WARNING: You cannot change the encryption to SHA512, change it back in config.lua." << std::endl;
  1603.                         return;
  1604.                     }
  1605.  
  1606.                     Database* db = Database::getInstance();
  1607.                     DBQuery query;
  1608.  
  1609.                     query << "SELECT `id`, `password`, `key` FROM `accounts`;";
  1610.                     if(DBResult* result = db->storeQuery(query.str()))
  1611.                     {
  1612.                         do
  1613.                         {
  1614.                             query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA512(result->getDataString("password"), false)) << ", `key` = " << db->escapeString(transformToSHA512(result->getDataString("key"), false)) << " WHERE `id` = " << result->getDataInt("id") << ";";
  1615.                             db->query(query.str());
  1616.                         }
  1617.                         while(result->next());
  1618.                         result->free();
  1619.                     }
  1620.  
  1621.                     registerDatabaseConfig("encryption", (int32_t)newValue);
  1622.                     std::clog << "> Encryption set to SHA512." << std::endl;
  1623.                     break;
  1624.                 }
  1625.  
  1626.                 default:
  1627.                 {
  1628.                     std::clog << "> 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;
  1629.                     break;
  1630.                 }
  1631.             }
  1632.         }
  1633.     }
  1634.     else
  1635.     {
  1636.         registerDatabaseConfig("encryption", (int32_t)newValue);
  1637.         if(g_config.getBool(ConfigManager::ACCOUNT_MANAGER))
  1638.         {
  1639.             switch(newValue)
  1640.             {
  1641.                 case ENCRYPTION_MD5:
  1642.                 {
  1643.                     Database* db = Database::getInstance();
  1644.                     DBQuery query;
  1645.                     query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToMD5("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1646.                     db->query(query.str());
  1647.                     break;
  1648.                 }
  1649.  
  1650.                 case ENCRYPTION_SHA1:
  1651.                 {
  1652.                     Database* db = Database::getInstance();
  1653.                     DBQuery query;
  1654.                     query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA1("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1655.                     db->query(query.str());
  1656.                     break;
  1657.                 }
  1658.  
  1659.                 case ENCRYPTION_SHA256:
  1660.                 {
  1661.                     Database* db = Database::getInstance();
  1662.                     DBQuery query;
  1663.                     query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA256("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1664.                     db->query(query.str());
  1665.                     break;
  1666.                 }
  1667.  
  1668.                 case ENCRYPTION_SHA512:
  1669.                 {
  1670.                     Database* db = Database::getInstance();
  1671.                     DBQuery query;
  1672.                     query << "UPDATE `accounts` SET `password` = " << db->escapeString(transformToSHA512("1", false)) << " WHERE `id` = 1 AND `password` = '1';";
  1673.                     db->query(query.str());
  1674.                     break;
  1675.                 }
  1676.  
  1677.                 default:
  1678.                     break;
  1679.             }
  1680.         }
  1681.     }
  1682. }
  1683.  
  1684. void DatabaseManager::checkTriggers()
  1685. {
  1686.     Database* db = Database::getInstance();
  1687.     switch(db->getDatabaseEngine())
  1688.     {
  1689.         case DATABASE_ENGINE_MYSQL:
  1690.         {
  1691.             std::string triggerName[] =
  1692.             {
  1693.                 "ondelete_accounts",
  1694.                 "oncreate_guilds",
  1695.                 "ondelete_guilds",
  1696.                 "oncreate_players",
  1697.                 "ondelete_players",
  1698.             };
  1699.  
  1700.             std::string triggerStatement[] =
  1701.             {
  1702.                 "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;",
  1703.                 "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;",
  1704.                 "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;",
  1705.                 "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;",
  1706.                 "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;"
  1707.             };
  1708.  
  1709.             DBQuery query;
  1710.             for(uint32_t i = 0; i < sizeof(triggerName) / sizeof(std::string); ++i)
  1711.             {
  1712.                 if(!triggerExists(triggerName[i]))
  1713.                 {
  1714.                     std::clog << "> Trigger: " << triggerName[i] << " does not exist, creating it..." << std::endl;
  1715.                     db->query(triggerStatement[i]);
  1716.                 }
  1717.             }
  1718.  
  1719.             break;
  1720.         }
  1721.  
  1722.         case DATABASE_ENGINE_SQLITE:
  1723.         {
  1724.             std::string triggerName[] =
  1725.             {
  1726.                 "oncreate_guilds",
  1727.                 "oncreate_players",
  1728.                 "ondelete_accounts",
  1729.                 "ondelete_players",
  1730.                 "ondelete_guilds",
  1731.                 "oninsert_players",
  1732.                 "onupdate_players",
  1733.                 "oninsert_guilds",
  1734.                 "onupdate_guilds",
  1735.                 "ondelete_houses",
  1736.                 "ondelete_tiles",
  1737.                 "oninsert_guild_ranks",
  1738.                 "onupdate_guild_ranks",
  1739.                 "oninsert_house_lists",
  1740.                 "onupdate_house_lists",
  1741.                 "oninsert_player_depotitems",
  1742.                 "onupdate_player_depotitems",
  1743.                 "oninsert_player_skills",
  1744.                 "onupdate_player_skills",
  1745.                 "oninsert_player_storage",
  1746.                 "onupdate_player_storage",
  1747.                 "oninsert_player_viplist",
  1748.                 "onupdate_player_viplist",
  1749.                 "oninsert_account_viplist",
  1750.                 "onupdate_account_viplist",
  1751.                 "oninsert_tile_items",
  1752.                 "onupdate_tile_items",
  1753.                 "oninsert_player_spells",
  1754.                 "onupdate_player_spells",
  1755.                 "oninsert_player_deaths",
  1756.                 "onupdate_player_deaths",
  1757.                 "oninsert_killers",
  1758.                 "onupdate_killers",
  1759.                 "oninsert_environment_killers",
  1760.                 "onupdate_environment_killers",
  1761.                 "oninsert_player_killers",
  1762.                 "onupdate_player_killers"
  1763.             };
  1764.  
  1765.             std::string triggerStatement[] =
  1766.             {
  1767.                 "CREATE TRIGGER `oncreate_guilds` \
  1768. AFTER INSERT ON `guilds` \
  1769. BEGIN \
  1770.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Leader`, 3, NEW.`id`);\
  1771.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Vice-Leader`, 2, NEW.`id`);\
  1772.     INSERT INTO `guild_ranks` (`name`, `level`, `guild_id`) VALUES (`Member`, 1, NEW.`id`);\
  1773. END;",
  1774.  
  1775.                 "CREATE TRIGGER `oncreate_players`\
  1776. AFTER INSERT\
  1777. ON `players`\
  1778. BEGIN\
  1779.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 0, 10);\
  1780.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 1, 10);\
  1781.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 2, 10);\
  1782.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 3, 10);\
  1783.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 4, 10);\
  1784.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 5, 10);\
  1785.     INSERT INTO `player_skills` (`player_id`, `skillid`, `value`) VALUES (NEW.`id`, 6, 10);\
  1786. END;",
  1787.                 "CREATE TRIGGER `ondelete_accounts`\
  1788. BEFORE DELETE\
  1789. ON `accounts`\
  1790. FOR EACH ROW\
  1791. BEGIN\
  1792.     DELETE FROM `players` WHERE `account_id` = OLD.`id`;\
  1793.     DELETE FROM `account_viplist` WHERE `account_id` = OLD.`id`;\
  1794.     DELETE FROM `bans` WHERE `type` IN (3, 4) AND `value` = OLD.`id`;\
  1795. END;",
  1796.  
  1797.                 "CREATE TRIGGER `ondelete_players`\
  1798. BEFORE DELETE\
  1799. ON `players`\
  1800. FOR EACH ROW\
  1801. BEGIN\
  1802.     SELECT RAISE(ROLLBACK, 'DELETE on table `players` violates foreign: `ownerid` from table `guilds`')\
  1803.     WHERE (SELECT `id` FROM `guilds` WHERE `ownerid` = OLD.`id`) IS NOT NULL;\
  1804. \
  1805.     DELETE FROM `account_viplist` WHERE `player_id` = OLD.`id`;\
  1806.     DELETE FROM `player_viplist` WHERE `player_id` = OLD.`id` OR `vip_id` = OLD.`id`;\
  1807.     DELETE FROM `player_storage` WHERE `player_id` = OLD.`id`;\
  1808.     DELETE FROM `player_skills` WHERE `player_id` = OLD.`id`;\
  1809.     DELETE FROM `player_items` WHERE `player_id` = OLD.`id`;\
  1810.     DELETE FROM `player_depotitems` WHERE `player_id` = OLD.`id`;\
  1811.     DELETE FROM `player_spells` WHERE `player_id` = OLD.`id`;\
  1812.     DELETE FROM `player_killers` WHERE `player_id` = OLD.`id`;\
  1813.     DELETE FROM `player_deaths` WHERE `player_id` = OLD.`id`;\
  1814.     DELETE FROM `guild_invites` WHERE `player_id` = OLD.`id`;\
  1815.     DELETE FROM `bans` WHERE `type` IN (2, 5) AND `value` = OLD.`id`;\
  1816.     UPDATE `houses` SET `owner` = 0 WHERE `owner` = OLD.`id`;\
  1817. END;",
  1818.                 "CREATE TRIGGER `ondelete_guilds`\
  1819. BEFORE DELETE\
  1820. ON `guilds`\
  1821. FOR EACH ROW\
  1822. BEGIN\
  1823.     UPDATE `players` SET `guildnick` = '', `rank_id` = 0 WHERE `rank_id` IN (SELECT `id` FROM `guild_ranks` WHERE `guild_id` = OLD.`id`);\
  1824.     DELETE FROM `guild_ranks` WHERE `guild_id` = OLD.`id`;\
  1825.     DELETE FROM `guild_invites` WHERE `guild_id` = OLD.`id`;\
  1826. END;",
  1827.  
  1828.                 "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;",
  1829.                 "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;",
  1830.  
  1831.                 "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;",
  1832.                 "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;",
  1833.  
  1834.                 "CREATE TRIGGER `ondelete_houses` BEFORE DELETE ON `houses` FOR EACH ROW BEGIN DELETE FROM `house_lists` WHERE `house_id` = OLD.`id`; END;",
  1835.                 "CREATE TRIGGER `ondelete_tiles` BEFORE DELETE ON `tiles` FOR EACH ROW BEGIN DELETE FROM `tile_items` WHERE `tile_id` = OLD.`id`; END;",
  1836.  
  1837.                 "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;",
  1838.                 "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;",
  1839.  
  1840.                 "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;",
  1841.                 "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;",
  1842.  
  1843.                 "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;",
  1844.                 "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;",
  1845.  
  1846.                 "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;",
  1847.                 "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;",
  1848.  
  1849.                 "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;",
  1850.                 "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;",
  1851.  
  1852.                 "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;",
  1853.                 "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;",
  1854.  
  1855.                 "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;",
  1856.                 "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;",
  1857.  
  1858.                 "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;",
  1859.                 "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;",
  1860.  
  1861.                 "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;",
  1862.                 "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;",
  1863.                 "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;",
  1864.                 "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;",
  1865.  
  1866.                 "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;",
  1867.                 "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;",
  1868.                 "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;",
  1869.                 "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;",
  1870.                 "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;",
  1871.                 "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;"
  1872.             };
  1873.  
  1874.             DBQuery query;
  1875.             for(uint32_t i = 0; i < sizeof(triggerName) / sizeof(std::string); ++i)
  1876.             {
  1877.                 if(!triggerExists(triggerName[i]))
  1878.                 {
  1879.                     std::clog << "> Trigger: " << triggerName[i] << " does not exist, creating it..." << std::endl;
  1880.                     db->query(triggerStatement[i]);
  1881.                 }
  1882.             }
  1883.  
  1884.             break;
  1885.         }
  1886.  
  1887.         case DATABASE_ENGINE_POSTGRESQL:
  1888.             //TODO: PostgreSQL support
  1889.             break;
  1890.  
  1891.         default:
  1892.             break;
  1893.     }
  1894. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!