From 678cade1163080263bdea9e349fbb67b7af15ea7 Mon Sep 17 00:00:00 2001 From: xjose93 Date: Tue, 16 Apr 2013 17:08:58 +0200 Subject: Core/Commands: rename guilds (.guild rename "old guildname" "new guildname") --- src/server/shared/Database/Implementation/CharacterDatabase.cpp | 2 ++ src/server/shared/Database/Implementation/CharacterDatabase.h | 1 + 2 files changed, 3 insertions(+) (limited to 'src/server/shared/Database/Implementation') diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index 2b57693db9b..f834ead6a5b 100644 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -171,6 +171,8 @@ void CharacterDatabaseConnection::DoPrepareStatements() // 0: uint32, 1: string, 2: uint32, 3: string, 4: string, 5: uint64, 6-10: uint32, 11: uint64 PrepareStatement(CHAR_INS_GUILD, "INSERT INTO guild (guildid, name, leaderguid, info, motd, createdate, EmblemStyle, EmblemColor, BorderStyle, BorderColor, BackgroundColor, BankMoney) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", CONNECTION_ASYNC); PrepareStatement(CHAR_DEL_GUILD, "DELETE FROM guild WHERE guildid = ?", CONNECTION_ASYNC); // 0: uint32 + // 0: string, 1: uint32 + PrepareStatement(CHAR_UPD_GUILD_NAME, "UPDATE guild SET name = ? WHERE guildid = ?", CONNECTION_ASYNC); // 0: uint32, 1: uint32, 2: uint8, 4: string, 5: string PrepareStatement(CHAR_INS_GUILD_MEMBER, "INSERT INTO guild_member (guildid, guid, rank, pnote, offnote) VALUES (?, ?, ?, ?, ?)", CONNECTION_ASYNC); PrepareStatement(CHAR_DEL_GUILD_MEMBER, "DELETE FROM guild_member WHERE guid = ?", CONNECTION_ASYNC); // 0: uint32 diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h index 3eb6a726007..65878b4c577 100644 --- a/src/server/shared/Database/Implementation/CharacterDatabase.h +++ b/src/server/shared/Database/Implementation/CharacterDatabase.h @@ -166,6 +166,7 @@ enum CharacterDatabaseStatements CHAR_INS_GUILD, CHAR_DEL_GUILD, + CHAR_UPD_GUILD_NAME, CHAR_INS_GUILD_MEMBER, CHAR_DEL_GUILD_MEMBER, CHAR_DEL_GUILD_MEMBERS, -- cgit v1.2.3 From 7542049eba46205572b9c9498d6891df31d0ca5b Mon Sep 17 00:00:00 2001 From: Bezo Date: Mon, 15 Apr 2013 14:56:00 +0300 Subject: [Ip2nationLock] Implement the ip2nation lock country. --- sql/base/auth_database.sql | 1 + sql/updates/auth/2013_04_22_00_auth_misc.sql | 21 ++++++++ sql/updates/world/2013_04_22_00_world_misc.sql | 7 +++ src/server/authserver/Server/AuthSocket.cpp | 40 ++++++++++++--- src/server/scripts/Commands/cs_account.cpp | 60 +++++++++++++++++++++- src/server/scripts/Commands/cs_misc.cpp | 2 +- .../Database/Implementation/LoginDatabase.cpp | 5 +- .../shared/Database/Implementation/LoginDatabase.h | 3 ++ .../Database/Implementation/WorldDatabase.cpp | 1 - .../shared/Database/Implementation/WorldDatabase.h | 1 - 10 files changed, 129 insertions(+), 12 deletions(-) create mode 100644 sql/updates/auth/2013_04_22_00_auth_misc.sql create mode 100644 sql/updates/world/2013_04_22_00_world_misc.sql (limited to 'src/server/shared/Database/Implementation') diff --git a/sql/base/auth_database.sql b/sql/base/auth_database.sql index c3752e980dd..943be73b20f 100644 --- a/sql/base/auth_database.sql +++ b/sql/base/auth_database.sql @@ -34,6 +34,7 @@ CREATE TABLE `account` ( `last_ip` varchar(15) NOT NULL DEFAULT '127.0.0.1', `failed_logins` int(10) unsigned NOT NULL DEFAULT '0', `locked` tinyint(3) unsigned NOT NULL DEFAULT '0', + `lock_country` varchar(2) NOT NULL DEFAULT '00', `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `online` tinyint(3) unsigned NOT NULL DEFAULT '0', `expansion` tinyint(3) unsigned NOT NULL DEFAULT '2', diff --git a/sql/updates/auth/2013_04_22_00_auth_misc.sql b/sql/updates/auth/2013_04_22_00_auth_misc.sql new file mode 100644 index 00000000000..508c0aab944 --- /dev/null +++ b/sql/updates/auth/2013_04_22_00_auth_misc.sql @@ -0,0 +1,21 @@ +ALTER TABLE `account` ADD COLUMN `lock_country` VARCHAR(2) NOT NULL DEFAULT '00' AFTER `locked`; + +DROP TABLE IF EXISTS ip2nation; +CREATE TABLE ip2nation ( + ip int(11) unsigned NOT NULL default '0', + country char(2) NOT NULL default '', + KEY ip (ip) +); + +DROP TABLE IF EXISTS ip2nationCountries; +CREATE TABLE ip2nationCountries ( + code varchar(4) NOT NULL default '', + iso_code_2 varchar(2) NOT NULL default '', + iso_code_3 varchar(3) default '', + iso_country varchar(255) NOT NULL default '', + country varchar(255) NOT NULL default '', + lat float NOT NULL default '0', + lon float NOT NULL default '0', + PRIMARY KEY (code), + KEY code (code) +); diff --git a/sql/updates/world/2013_04_22_00_world_misc.sql b/sql/updates/world/2013_04_22_00_world_misc.sql new file mode 100644 index 00000000000..99eee20ffef --- /dev/null +++ b/sql/updates/world/2013_04_22_00_world_misc.sql @@ -0,0 +1,7 @@ +DROP TABLE IF EXISTS ip2nation; +DROP TABLE IF EXISTS ip2nationCountries; + +DELETE FROM `command` WHERE `name` in ('account lock', 'account lock ip', 'account lock country'); +INSERT INTO `command` (`name`,`security`,`help`) VALUES +('account lock ip', 0, 'Syntax: .account lock ip [on|off]\nAllow login from account only from current used IP or remove this requirement.'), +('account lock country', 0, 'Syntax: .account lock country [on|off]\nAllow login from account only from current used Country or remove this requirement.'); diff --git a/src/server/authserver/Server/AuthSocket.cpp b/src/server/authserver/Server/AuthSocket.cpp index 32ddf029f1c..a080a038314 100644 --- a/src/server/authserver/Server/AuthSocket.cpp +++ b/src/server/authserver/Server/AuthSocket.cpp @@ -386,17 +386,45 @@ bool AuthSocket::_HandleLogonChallenge() sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account '%s' is locked to IP - '%s'", _login.c_str(), fields[3].GetCString()); sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Player address is '%s'", ip_address.c_str()); - if (strcmp(fields[3].GetCString(), ip_address.c_str())) + if (strcmp(fields[4].GetCString(), ip_address.c_str())) { sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account IP differs"); - pkt << (uint8) WOW_FAIL_SUSPENDED; + pkt << uint8(WOW_FAIL_LOCKED_ENFORCED); locked = true; } else sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account IP matches"); } else + { sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account '%s' is not locked to ip", _login.c_str()); + std::string accountCountry = fields[3].GetString(); + if (accountCountry.empty() || accountCountry == "00") + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account '%s' is not locked to country", _login.c_str()); + else if (!accountCountry.empty()) + { + uint32 ip = inet_addr(ip_address.c_str()); + EndianConvertReverse(ip); + + stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_LOGON_COUNTRY); + stmt->setUInt32(0, ip); + if (PreparedQueryResult sessionCountryQuery = LoginDatabase.Query(stmt)) + { + std::string loginCountry = (*sessionCountryQuery)[0].GetString(); + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account '%s' is locked to country: '%s' Player country is '%s'", _login.c_str(), accountCountry.c_str(), loginCountry.c_str()); + if (loginCountry != accountCountry) + { + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account country differs."); + pkt << uint8(WOW_FAIL_UNLOCKABLE_LOCK); + locked = true; + } + else + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] Account country matches"); + } + else + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[AuthChallenge] IP2NATION Table empty"); + } + } if (!locked) { @@ -426,8 +454,8 @@ bool AuthSocket::_HandleLogonChallenge() std::string rI = fields[0].GetString(); // Don't calculate (v, s) if there are already some in the database - std::string databaseV = fields[5].GetString(); - std::string databaseS = fields[6].GetString(); + std::string databaseV = fields[6].GetString(); + std::string databaseS = fields[7].GetString(); sLog->outDebug(LOG_FILTER_NETWORKIO, "database authentication values: v='%s' s='%s'", databaseV.c_str(), databaseS.c_str()); @@ -484,7 +512,7 @@ bool AuthSocket::_HandleLogonChallenge() if (securityFlags & 0x04) // Security token input pkt << uint8(1); - uint8 secLevel = fields[4].GetUInt8(); + uint8 secLevel = fields[5].GetUInt8(); _accountSecurityLevel = secLevel <= SEC_ADMINISTRATOR ? AccountTypes(secLevel) : SEC_ADMINISTRATOR; _localizationName.resize(4); @@ -498,7 +526,7 @@ bool AuthSocket::_HandleLogonChallenge() } } else //no account - pkt << (uint8)WOW_FAIL_UNKNOWN_ACCOUNT; + pkt << uint8(WOW_FAIL_UNKNOWN_ACCOUNT); } socket().send((char const*)pkt.contents(), pkt.size()); diff --git a/src/server/scripts/Commands/cs_account.cpp b/src/server/scripts/Commands/cs_account.cpp index 3953beab3da..cf2816c985e 100644 --- a/src/server/scripts/Commands/cs_account.cpp +++ b/src/server/scripts/Commands/cs_account.cpp @@ -42,13 +42,19 @@ public: { "password", SEC_CONSOLE, true, &HandleAccountSetPasswordCommand, "", NULL }, { NULL, SEC_PLAYER, false, NULL, "", NULL } }; + static ChatCommand accountLockCommandTable[] = + { + { "country", SEC_PLAYER, true, &HandleAccountLockCountryCommand, "", NULL }, + { "ip", SEC_PLAYER, true, &HandleAccountLockIpCommand, "", NULL }, + { NULL, SEC_PLAYER, false, NULL, "", NULL }, + }; static ChatCommand accountCommandTable[] = { { "addon", SEC_MODERATOR, false, &HandleAccountAddonCommand, "", NULL }, { "create", SEC_CONSOLE, true, &HandleAccountCreateCommand, "", NULL }, { "delete", SEC_CONSOLE, true, &HandleAccountDeleteCommand, "", NULL }, { "onlinelist", SEC_CONSOLE, true, &HandleAccountOnlineListCommand, "", NULL }, - { "lock", SEC_PLAYER, false, &HandleAccountLockCommand, "", NULL }, + { "lock", SEC_PLAYER, false, NULL, "", accountLockCommandTable }, { "set", SEC_ADMINISTRATOR, true, NULL, "", accountSetCommandTable }, { "password", SEC_PLAYER, false, &HandleAccountPasswordCommand, "", NULL }, { "", SEC_PLAYER, false, &HandleAccountCommand, "", NULL }, @@ -245,7 +251,57 @@ public: return true; } - static bool HandleAccountLockCommand(ChatHandler* handler, char const* args) + static bool HandleAccountLockCountryCommand(ChatHandler* handler, char const* args) + { + if (!*args) + { + handler->SendSysMessage(LANG_USE_BOL); + handler->SetSentErrorMessage(true); + return false; + } + std::string param = (char*)args; + + if (!param.empty()) + { + if (param == "on") + { + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_LOGON_COUNTRY); + uint32 ip = inet_addr(handler->GetSession()->GetRemoteAddress().c_str()); + EndianConvertReverse(ip); + stmt->setUInt32(0, ip); + PreparedQueryResult result = LoginDatabase.Query(stmt); + if (result) + { + Field* fields = result->Fetch(); + std::string country = fields[0].GetString(); + stmt = LoginDatabase.GetPreparedStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY); + stmt->setString(0, country); + stmt->setUInt32(1, handler->GetSession()->GetAccountId()); + LoginDatabase.Execute(stmt); + handler->PSendSysMessage(LANG_COMMAND_ACCLOCKLOCKED); + } + else + { + handler->PSendSysMessage("[IP2NATION] Table empty"); + sLog->outDebug(LOG_FILTER_AUTHSERVER, "[IP2NATION] Table empty"); + } + } + else if (param == "off") + { + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY); + stmt->setString(0, "00"); + stmt->setUInt32(1, handler->GetSession()->GetAccountId()); + LoginDatabase.Execute(stmt); + handler->PSendSysMessage(LANG_COMMAND_ACCLOCKUNLOCKED); + } + return true; + } + handler->SendSysMessage(LANG_USE_BOL); + handler->SetSentErrorMessage(true); + return false; + } + + static bool HandleAccountLockIpCommand(ChatHandler* handler, char const* args) { if (!*args) { diff --git a/src/server/scripts/Commands/cs_misc.cpp b/src/server/scripts/Commands/cs_misc.cpp index c6d80cea3a5..bb7d6ea9594 100644 --- a/src/server/scripts/Commands/cs_misc.cpp +++ b/src/server/scripts/Commands/cs_misc.cpp @@ -1587,7 +1587,7 @@ public: EndianConvertReverse(ip); #endif - PreparedStatement* stmt = WorldDatabase.GetPreparedStatement(WORLD_SEL_IP2NATION_COUNTRY); + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_IP2NATION_COUNTRY); stmt->setUInt32(0, ip); diff --git a/src/server/shared/Database/Implementation/LoginDatabase.cpp b/src/server/shared/Database/Implementation/LoginDatabase.cpp index a23294a038c..4b0ee041603 100644 --- a/src/server/shared/Database/Implementation/LoginDatabase.cpp +++ b/src/server/shared/Database/Implementation/LoginDatabase.cpp @@ -37,7 +37,8 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_SEL_SESSIONKEY, "SELECT a.sessionkey, a.id, aa.gmlevel FROM account a LEFT JOIN account_access aa ON (a.id = aa.id) WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_UPD_VS, "UPDATE account SET v = ?, s = ? WHERE username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_LOGONPROOF, "UPDATE account SET sessionkey = ?, last_ip = ?, last_login = NOW(), locale = ?, failed_logins = 0, os = ? WHERE username = ?", CONNECTION_ASYNC); - PrepareStatement(LOGIN_SEL_LOGONCHALLENGE, "SELECT a.sha_pass_hash, a.id, a.locked, a.last_ip, aa.gmlevel, a.v, a.s FROM account a LEFT JOIN account_access aa ON (a.id = aa.id) WHERE a.username = ?", CONNECTION_SYNCH); + PrepareStatement(LOGIN_SEL_LOGONCHALLENGE, "SELECT a.sha_pass_hash, a.id, a.locked, a.lock_country, a.last_ip, aa.gmlevel, a.v, a.s FROM account a LEFT JOIN account_access aa ON (a.id = aa.id) WHERE a.username = ?", CONNECTION_SYNCH); + PrepareStatement(LOGIN_SEL_LOGON_COUNTRY, "SELECT country FROM ip2nation WHERE ip < ? ORDER BY ip DESC LIMIT 0,1", CONNECTION_SYNCH); PrepareStatement(LOGIN_UPD_FAILEDLOGINS, "UPDATE account SET failed_logins = failed_logins + 1 WHERE username = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_FAILEDLOGINS, "SELECT id, failed_logins FROM account WHERE username = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_ACCOUNT_ID_BY_NAME, "SELECT id FROM account WHERE username = ?", CONNECTION_SYNCH); @@ -59,6 +60,7 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_INS_REALM_CHARACTERS_INIT, "INSERT INTO realmcharacters (realmid, acctid, numchars) SELECT realmlist.id, account.id, 0 FROM realmlist, account LEFT JOIN realmcharacters ON acctid=account.id WHERE acctid IS NULL", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_EXPANSION, "UPDATE account SET expansion = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_ACCOUNT_LOCK, "UPDATE account SET locked = ? WHERE id = ?", CONNECTION_ASYNC); + PrepareStatement(LOGIN_UPD_ACCOUNT_LOCK_CONTRY, "UPDATE account SET lock_country = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_INS_LOG, "INSERT INTO logs (time, realm, type, level, string) VALUES (?, ?, ?, ?, ?)", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_USERNAME, "UPDATE account SET v = 0, s = 0, username = ?, sha_pass_hash = ? WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_UPD_PASSWORD, "UPDATE account SET v = 0, s = 0, sha_pass_hash = ? WHERE id = ?", CONNECTION_ASYNC); @@ -88,6 +90,7 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_SEL_ACCOUNT_WHOIS, "SELECT username, email, last_ip FROM account WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_REALMLIST_SECURITY_LEVEL, "SELECT allowedSecurityLevel from realmlist WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_DEL_ACCOUNT, "DELETE FROM account WHERE id = ?", CONNECTION_ASYNC); + PrepareStatement(LOGIN_SEL_IP2NATION_COUNTRY, "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_ACCOUNT_ACCESS_BY_ID, "SELECT gmlevel, RealmID FROM account_access WHERE id = ? and (RealmID = ? OR RealmID = -1) ORDER BY gmlevel desc", CONNECTION_SYNCH); diff --git a/src/server/shared/Database/Implementation/LoginDatabase.h b/src/server/shared/Database/Implementation/LoginDatabase.h index 939cc4b4790..0f5a388a0b0 100644 --- a/src/server/shared/Database/Implementation/LoginDatabase.h +++ b/src/server/shared/Database/Implementation/LoginDatabase.h @@ -56,6 +56,7 @@ enum LoginDatabaseStatements LOGIN_UPD_VS, LOGIN_UPD_LOGONPROOF, LOGIN_SEL_LOGONCHALLENGE, + LOGIN_SEL_LOGON_COUNTRY, LOGIN_UPD_FAILEDLOGINS, LOGIN_SEL_FAILEDLOGINS, LOGIN_SEL_ACCOUNT_ID_BY_NAME, @@ -79,6 +80,7 @@ enum LoginDatabaseStatements LOGIN_INS_REALM_CHARACTERS_INIT, LOGIN_UPD_EXPANSION, LOGIN_UPD_ACCOUNT_LOCK, + LOGIN_UPD_ACCOUNT_LOCK_CONTRY, LOGIN_INS_LOG, LOGIN_UPD_USERNAME, LOGIN_UPD_PASSWORD, @@ -108,6 +110,7 @@ enum LoginDatabaseStatements LOGIN_SEL_ACCOUNT_WHOIS, LOGIN_SEL_REALMLIST_SECURITY_LEVEL, LOGIN_DEL_ACCOUNT, + LOGIN_SEL_IP2NATION_COUNTRY, LOGIN_SEL_ACCOUNT_ACCESS_BY_ID, LOGIN_SEL_RBAC_ACCOUNT_GROUPS, diff --git a/src/server/shared/Database/Implementation/WorldDatabase.cpp b/src/server/shared/Database/Implementation/WorldDatabase.cpp index 89f3cf8fdce..fa7818d2dca 100644 --- a/src/server/shared/Database/Implementation/WorldDatabase.cpp +++ b/src/server/shared/Database/Implementation/WorldDatabase.cpp @@ -80,7 +80,6 @@ void WorldDatabaseConnection::DoPrepareStatements() PrepareStatement(WORLD_SEL_COMMANDS, "SELECT name, security, help FROM command", CONNECTION_SYNCH); PrepareStatement(WORLD_SEL_CREATURE_TEMPLATE, "SELECT difficulty_entry_1, difficulty_entry_2, difficulty_entry_3, KillCredit1, KillCredit2, modelid1, modelid2, modelid3, modelid4, name, subname, IconName, gossip_menu_id, minlevel, maxlevel, exp, faction_A, faction_H, npcflag, speed_walk, speed_run, scale, rank, mindmg, maxdmg, dmgschool, attackpower, dmg_multiplier, baseattacktime, rangeattacktime, unit_class, unit_flags, unit_flags2, dynamicflags, family, trainer_type, trainer_spell, trainer_class, trainer_race, minrangedmg, maxrangedmg, rangedattackpower, type, type_flags, lootid, pickpocketloot, skinloot, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, spell1, spell2, spell3, spell4, spell5, spell6, spell7, spell8, PetSpellDataId, VehicleId, mingold, maxgold, AIName, MovementType, InhabitType, HoverHeight, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, mechanic_immune_mask, flags_extra, ScriptName FROM creature_template WHERE entry = ?", CONNECTION_SYNCH); PrepareStatement(WORLD_SEL_WAYPOINT_SCRIPT_BY_ID, "SELECT guid, delay, command, datalong, datalong2, dataint, x, y, z, o FROM waypoint_scripts WHERE id = ?", CONNECTION_SYNCH); - PrepareStatement(WORLD_SEL_IP2NATION_COUNTRY, "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1", CONNECTION_SYNCH); PrepareStatement(WORLD_SEL_ITEM_TEMPLATE_BY_NAME, "SELECT entry FROM item_template WHERE name = ?", CONNECTION_SYNCH); PrepareStatement(WORLD_SEL_CREATURE_BY_ID, "SELECT guid FROM creature WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(WORLD_SEL_GAMEOBJECT_NEAREST, "SELECT guid, id, position_x, position_y, position_z, map, (POW(position_x - ?, 2) + POW(position_y - ?, 2) + POW(position_z - ?, 2)) AS order_ FROM gameobject WHERE map = ? AND (POW(position_x - ?, 2) + POW(position_y - ?, 2) + POW(position_z - ?, 2)) <= ? ORDER BY order_", CONNECTION_SYNCH); diff --git a/src/server/shared/Database/Implementation/WorldDatabase.h b/src/server/shared/Database/Implementation/WorldDatabase.h index 032baf29dd9..d8c3c69dbba 100644 --- a/src/server/shared/Database/Implementation/WorldDatabase.h +++ b/src/server/shared/Database/Implementation/WorldDatabase.h @@ -100,7 +100,6 @@ enum WorldDatabaseStatements WORLD_SEL_COMMANDS, WORLD_SEL_CREATURE_TEMPLATE, WORLD_SEL_WAYPOINT_SCRIPT_BY_ID, - WORLD_SEL_IP2NATION_COUNTRY, WORLD_SEL_ITEM_TEMPLATE_BY_NAME, WORLD_SEL_CREATURE_BY_ID, WORLD_SEL_GAMEOBJECT_NEAREST, -- cgit v1.2.3 From d7e9d1bafb288596f0057fcd5517ba95981579e4 Mon Sep 17 00:00:00 2001 From: xjose93 Date: Thu, 25 Apr 2013 19:10:10 +0200 Subject: Core/World: Improvements in Autobroadcast system (dropped from world database and moved to auth database, added realmid and weight columns) --- sql/updates/auth/2013_04_25_00_auth_misc.txt | 8 +++++ sql/updates/world/2013_04_25_00_world_misc.sql | 1 + src/server/game/World/World.cpp | 41 +++++++++++++++++++--- src/server/game/World/World.h | 6 +++- .../Database/Implementation/LoginDatabase.cpp | 1 + .../shared/Database/Implementation/LoginDatabase.h | 1 + 6 files changed, 52 insertions(+), 6 deletions(-) create mode 100644 sql/updates/auth/2013_04_25_00_auth_misc.txt create mode 100644 sql/updates/world/2013_04_25_00_world_misc.sql (limited to 'src/server/shared/Database/Implementation') diff --git a/sql/updates/auth/2013_04_25_00_auth_misc.txt b/sql/updates/auth/2013_04_25_00_auth_misc.txt new file mode 100644 index 00000000000..383b19ebd55 --- /dev/null +++ b/sql/updates/auth/2013_04_25_00_auth_misc.txt @@ -0,0 +1,8 @@ +DROP TABLE IF EXISTS `autobroadcast`; +CREATE TABLE `autobroadcast` ( + `realmid` int(10) NOT NULL, + `id` int(11) NOT NULL AUTO_INCREMENT, + `weight` tinyint(3) DEFAULT 1, + `text` longtext NOT NULL, + PRIMARY KEY (`id`, `realmid`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; diff --git a/sql/updates/world/2013_04_25_00_world_misc.sql b/sql/updates/world/2013_04_25_00_world_misc.sql new file mode 100644 index 00000000000..c07a3f76b5f --- /dev/null +++ b/sql/updates/world/2013_04_25_00_world_misc.sql @@ -0,0 +1 @@ +DROP TABLE IF EXISTS `autobroadcast`; diff --git a/src/server/game/World/World.cpp b/src/server/game/World/World.cpp index a786a218dc5..144d91485b5 100644 --- a/src/server/game/World/World.cpp +++ b/src/server/game/World/World.cpp @@ -1866,12 +1866,16 @@ void World::LoadAutobroadcasts() uint32 oldMSTime = getMSTime(); m_Autobroadcasts.clear(); + m_AutobroadcastsWeights.clear(); - QueryResult result = WorldDatabase.Query("SELECT text FROM autobroadcast"); + uint32 realmId = ConfigMgr::GetIntDefault("RealmID", 0); + PreparedStatement* stmt = LoginDatabase.GetPreparedStatement(LOGIN_SEL_AUTOBROADCAST); + stmt->setInt32(0, realmId); + PreparedQueryResult result = LoginDatabase.Query(stmt); if (!result) { - sLog->outInfo(LOG_FILTER_SERVER_LOADING, ">> Loaded 0 autobroadcasts definitions. DB table `autobroadcast` is empty!"); + sLog->outInfo(LOG_FILTER_SERVER_LOADING, ">> Loaded 0 autobroadcasts definitions. DB table `autobroadcast` is empty for this realm!"); return; } @@ -1880,9 +1884,10 @@ void World::LoadAutobroadcasts() do { Field* fields = result->Fetch(); - std::string message = fields[0].GetString(); + uint8 id = fields[0].GetUInt8(); - m_Autobroadcasts.push_back(message); + m_Autobroadcasts[id] = fields[2].GetString(); + m_AutobroadcastsWeights[id] = fields[1].GetUInt8(); ++count; } while (result->NextRow()); @@ -2635,9 +2640,35 @@ void World::SendAutoBroadcast() if (m_Autobroadcasts.empty()) return; + uint32 weight = 0; + AutobroadcastsWeightMap selectionWeights; std::string msg; - msg = Trinity::Containers::SelectRandomContainerElement(m_Autobroadcasts); + for (AutobroadcastsWeightMap::const_iterator it = m_AutobroadcastsWeights.begin(); it != m_AutobroadcastsWeights.end(); ++it) + { + if (it->second) + { + weight += it->second; + selectionWeights[it->first] = it->second; + } + } + + if (weight) + { + uint32 selectedWeight = urand(0, weight - 1); + weight = 0; + for (AutobroadcastsWeightMap::const_iterator it = selectionWeights.begin(); it != selectionWeights.end(); ++it) + { + weight += it->second; + if (selectedWeight < weight) + { + msg = m_Autobroadcasts[it->first]; + break; + } + } + } + else + msg = m_Autobroadcasts[urand(0, m_Autobroadcasts.size())]; uint32 abcenter = sWorld->getIntConfig(CONFIG_AUTOBROADCAST_CENTER); diff --git a/src/server/game/World/World.h b/src/server/game/World/World.h index 00bb9e7ffb2..b303ca4bd4b 100644 --- a/src/server/game/World/World.h +++ b/src/server/game/World/World.h @@ -820,7 +820,11 @@ class World // used versions std::string m_DBVersion; - std::list m_Autobroadcasts; + typedef std::map AutobroadcastsMap; + AutobroadcastsMap m_Autobroadcasts; + + typedef std::map AutobroadcastsWeightMap; + AutobroadcastsWeightMap m_AutobroadcastsWeights; std::map _characterNameDataMap; void LoadCharacterNameData(); diff --git a/src/server/shared/Database/Implementation/LoginDatabase.cpp b/src/server/shared/Database/Implementation/LoginDatabase.cpp index 4b0ee041603..0118f637205 100644 --- a/src/server/shared/Database/Implementation/LoginDatabase.cpp +++ b/src/server/shared/Database/Implementation/LoginDatabase.cpp @@ -91,6 +91,7 @@ void LoginDatabaseConnection::DoPrepareStatements() PrepareStatement(LOGIN_SEL_REALMLIST_SECURITY_LEVEL, "SELECT allowedSecurityLevel from realmlist WHERE id = ?", CONNECTION_SYNCH); PrepareStatement(LOGIN_DEL_ACCOUNT, "DELETE FROM account WHERE id = ?", CONNECTION_ASYNC); PrepareStatement(LOGIN_SEL_IP2NATION_COUNTRY, "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1", CONNECTION_SYNCH); + PrepareStatement(LOGIN_SEL_AUTOBROADCAST, "SELECT id, weight, text FROM autobroadcast WHERE realmid = ? OR realmid = -1", CONNECTION_SYNCH); PrepareStatement(LOGIN_SEL_ACCOUNT_ACCESS_BY_ID, "SELECT gmlevel, RealmID FROM account_access WHERE id = ? and (RealmID = ? OR RealmID = -1) ORDER BY gmlevel desc", CONNECTION_SYNCH); diff --git a/src/server/shared/Database/Implementation/LoginDatabase.h b/src/server/shared/Database/Implementation/LoginDatabase.h index 0f5a388a0b0..97cf91fc178 100644 --- a/src/server/shared/Database/Implementation/LoginDatabase.h +++ b/src/server/shared/Database/Implementation/LoginDatabase.h @@ -111,6 +111,7 @@ enum LoginDatabaseStatements LOGIN_SEL_REALMLIST_SECURITY_LEVEL, LOGIN_DEL_ACCOUNT, LOGIN_SEL_IP2NATION_COUNTRY, + LOGIN_SEL_AUTOBROADCAST, LOGIN_SEL_ACCOUNT_ACCESS_BY_ID, LOGIN_SEL_RBAC_ACCOUNT_GROUPS, -- cgit v1.2.3 From f2b6b2f95e2664686e247ec0870fbb77a5770d54 Mon Sep 17 00:00:00 2001 From: xjose93 Date: Tue, 23 Apr 2013 15:33:42 +0200 Subject: Core/Commands: Improve .character rename [name], now can force rename .character rename [name] [newName] --- sql/updates/world/2013_04_29_00_world_misc.sql | 11 ++ src/server/game/Miscellaneous/Language.h | 5 +- src/server/scripts/Commands/cs_character.cpp | 121 ++++++++++++++++++--- .../Database/Implementation/CharacterDatabase.cpp | 1 + .../Database/Implementation/CharacterDatabase.h | 1 + 5 files changed, 123 insertions(+), 16 deletions(-) create mode 100644 sql/updates/world/2013_04_29_00_world_misc.sql (limited to 'src/server/shared/Database/Implementation') diff --git a/sql/updates/world/2013_04_29_00_world_misc.sql b/sql/updates/world/2013_04_29_00_world_misc.sql new file mode 100644 index 00000000000..716a77be584 --- /dev/null +++ b/sql/updates/world/2013_04_29_00_world_misc.sql @@ -0,0 +1,11 @@ +DELETE FROM `command` WHERE `name` = 'character rename'; +INSERT INTO `command` (`name`, `security`, `help`) VALUES +('character rename', 2, 'Syntax: .character rename [$name] [$newName] \n\nMark selected in game or by $name in command character for rename at next login.\n\nIf $newName then the player will be forced rename.'); + +SET @ENTRY := 98; +SET @ENTRY1 := 167; +DELETE FROM `trinity_string` WHERE `entry` IN (@ENTRY, @ENTRY+1, @ENTRY1); +INSERT INTO `trinity_string` (`entry`, `content_default`) VALUES +(@ENTRY, '\'%s\' already exists as character name, choose another one'), +(@ENTRY+1, 'Player \'%s\' forced rename to \'%s\''), +(@ENTRY1, 'This name is reserved, choose another one'); diff --git a/src/server/game/Miscellaneous/Language.h b/src/server/game/Miscellaneous/Language.h index e03adf2a4a1..eb55e44c9ee 100644 --- a/src/server/game/Miscellaneous/Language.h +++ b/src/server/game/Miscellaneous/Language.h @@ -121,7 +121,8 @@ enum TrinityStrings LANG_RBAC_LIST_PERMISSIONS_HEADER = 95, LANG_GUILD_RENAME_ALREADY_EXISTS = 96, LANG_GUILD_RENAME_DONE = 97, - // Room for more level 0 98-99 not used + LANG_RENAME_PLAYER_ALREADY_EXISTS = 98, + LANG_RENAME_PLAYER_WITH_NEW_NAME = 99, // level 1 chat LANG_GLOBAL_NOTIFY = 100, @@ -198,7 +199,7 @@ enum TrinityStrings LANG_COMMAND_TELE_NOTFOUND = 164, LANG_COMMAND_TELE_PARAMETER = 165, LANG_COMMAND_TELE_NOLOCATION = 166, - // 167 // not used + LANG_RESERVED_NAME = 167, LANG_COMMAND_TELE_LOCATION = 168, LANG_MAIL_SENT = 169, diff --git a/src/server/scripts/Commands/cs_character.cpp b/src/server/scripts/Commands/cs_character.cpp index c29b62975ab..fdcf88177a9 100644 --- a/src/server/scripts/Commands/cs_character.cpp +++ b/src/server/scripts/Commands/cs_character.cpp @@ -308,28 +308,121 @@ public: if (!handler->extractPlayerTarget((char*)args, &target, &targetGuid, &targetName)) return false; - if (target) + char const* newNameStr = strtok(NULL, " "); + + if (newNameStr) { - // check online security - if (handler->HasLowerSecurity(target, 0)) + std::string playerOldName; + std::string newName = newNameStr; + + if (target) + { + // check online security + if (handler->HasLowerSecurity(target, 0)) + return false; + + playerOldName = target->GetName(); + } + else + { + // check offline security + if (handler->HasLowerSecurity(NULL, targetGuid)) + return false; + + sObjectMgr->GetPlayerNameByGUID(targetGuid, playerOldName); + } + + if (!normalizePlayerName(newName)) + { + handler->SendSysMessage(LANG_BAD_VALUE); + handler->SetSentErrorMessage(true); + return false; + } + + if (ObjectMgr::CheckPlayerName(newName, true) != CHAR_NAME_SUCCESS) + { + handler->SendSysMessage(LANG_BAD_VALUE); + handler->SetSentErrorMessage(true); + return false; + } + + if (WorldSession* session = handler->GetSession()) + { + if (!session->HasPermission(RBAC_PERM_SKIP_CHECK_CHARACTER_CREATION_RESERVEDNAME) && sObjectMgr->IsReservedName(newName)) + { + handler->SendSysMessage(LANG_RESERVED_NAME); + handler->SetSentErrorMessage(true); + return false; + } + } + + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_SEL_CHECK_NAME); + stmt->setString(0, newName); + PreparedQueryResult result = CharacterDatabase.Query(stmt); + if (result) + { + handler->PSendSysMessage(LANG_RENAME_PLAYER_ALREADY_EXISTS, newName.c_str()); + handler->SetSentErrorMessage(true); return false; + } + + // Remove declined name from db + stmt = CharacterDatabase.GetPreparedStatement(CHAR_DEL_DECLINED_NAME); + stmt->setUInt32(0, targetGuid); + CharacterDatabase.Execute(stmt); + + if (target) + { + target->SetName(newName); + + if (WorldSession* session = target->GetSession()) + session->KickPlayer(); + } + else + { + stmt = CharacterDatabase.GetPreparedStatement(CHAR_UPD_NAME_BY_GUID); + stmt->setString(0, newName); + stmt->setUInt32(1, GUID_LOPART(targetGuid)); + CharacterDatabase.Execute(stmt); + } + + sWorld->UpdateCharacterNameData(targetGuid, newName); - handler->PSendSysMessage(LANG_RENAME_PLAYER, handler->GetNameLink(target).c_str()); - target->SetAtLoginFlag(AT_LOGIN_RENAME); + handler->PSendSysMessage(LANG_RENAME_PLAYER_WITH_NEW_NAME, playerOldName.c_str(), newName.c_str()); + + if (WorldSession* session = handler->GetSession()) + { + if (Player* player = session->GetPlayer()) + sLog->outCommand(session->GetAccountId(), "GM %s (Account: %u) forced rename %s to player %s (Account: %u)", player->GetName().c_str(), session->GetAccountId(), newName.c_str(), playerOldName.c_str(), sObjectMgr->GetPlayerAccountIdByGUID(targetGuid)); + } + else + sLog->outCommand(0, "CONSOLE forced rename '%s' to '%s' (GUID: %u)", playerOldName.c_str(), newName.c_str(), GUID_LOPART(targetGuid)); } else { - // check offline security - if (handler->HasLowerSecurity(NULL, targetGuid)) - return false; + if (target) + { + // check online security + if (handler->HasLowerSecurity(target, 0)) + return false; - std::string oldNameLink = handler->playerLink(targetName); - handler->PSendSysMessage(LANG_RENAME_PLAYER_GUID, oldNameLink.c_str(), GUID_LOPART(targetGuid)); + handler->PSendSysMessage(LANG_RENAME_PLAYER, handler->GetNameLink(target).c_str()); + target->SetAtLoginFlag(AT_LOGIN_RENAME); + } + else + { + // check offline security + if (handler->HasLowerSecurity(NULL, targetGuid)) + return false; - PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_UPD_ADD_AT_LOGIN_FLAG); - stmt->setUInt16(0, uint16(AT_LOGIN_RENAME)); - stmt->setUInt32(1, GUID_LOPART(targetGuid)); - CharacterDatabase.Execute(stmt); + std::string oldNameLink = handler->playerLink(targetName); + handler->PSendSysMessage(LANG_RENAME_PLAYER_GUID, oldNameLink.c_str(), GUID_LOPART(targetGuid)); + + PreparedStatement* stmt = CharacterDatabase.GetPreparedStatement(CHAR_UPD_ADD_AT_LOGIN_FLAG); + stmt->setUInt16(0, uint16(AT_LOGIN_RENAME)); + stmt->setUInt32(1, GUID_LOPART(targetGuid)); + CharacterDatabase.Execute(stmt); + } } return true; diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.cpp b/src/server/shared/Database/Implementation/CharacterDatabase.cpp index f834ead6a5b..2e6ab10b087 100644 --- a/src/server/shared/Database/Implementation/CharacterDatabase.cpp +++ b/src/server/shared/Database/Implementation/CharacterDatabase.cpp @@ -165,6 +165,7 @@ void CharacterDatabaseConnection::DoPrepareStatements() PrepareStatement(CHAR_SEL_MATCH_MAKER_RATING, "SELECT matchMakerRating FROM character_arena_stats WHERE guid = ? AND slot = ?", CONNECTION_SYNCH); PrepareStatement(CHAR_SEL_CHARACTER_COUNT, "SELECT account, COUNT(guid) FROM characters WHERE account = ? GROUP BY account", CONNECTION_ASYNC); PrepareStatement(CHAR_UPD_NAME, "UPDATE characters set name = ?, at_login = at_login & ~ ? WHERE guid = ?", CONNECTION_ASYNC); + PrepareStatement(CHAR_UPD_NAME_BY_GUID, "UPDATE characters SET name = ? WHERE guid = ?", CONNECTION_ASYNC); PrepareStatement(CHAR_DEL_DECLINED_NAME, "DELETE FROM character_declinedname WHERE guid = ?", CONNECTION_ASYNC); // Guild handling diff --git a/src/server/shared/Database/Implementation/CharacterDatabase.h b/src/server/shared/Database/Implementation/CharacterDatabase.h index 65878b4c577..59ac15978b6 100644 --- a/src/server/shared/Database/Implementation/CharacterDatabase.h +++ b/src/server/shared/Database/Implementation/CharacterDatabase.h @@ -161,6 +161,7 @@ enum CharacterDatabaseStatements CHAR_SEL_MATCH_MAKER_RATING, CHAR_SEL_CHARACTER_COUNT, CHAR_UPD_NAME, + CHAR_UPD_NAME_BY_GUID, CHAR_DEL_DECLINED_NAME, CHAR_SEL_CHARACTER_DATA_BY_GUID, -- cgit v1.2.3