bool DatabaseManager::triggerExists(const std::string& triggerName)
{
Database* db = Database::getInstance();
DBQuery query;
switch(db->getDatabaseEngine())
{
case DATABASE_ENGINE_MYSQL:
query << "SELECT `name` FROM `sqlite_master` WHERE `type` = 'trigger' AND `name` = " << db->escapeString(triggerName) << ";";
break;
case DATABASE_ENGINE_SQLITE:
query << "SELECT `TRIGGER_NAME` FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::SQLITE_DB)) << " AND `TRIGGER_NAME` = " << db->escapeString(triggerName) << ";";
break;
default:
return false;
}
DBResult* result = db->storeQuery(query.str());
if(!result)
return false;
db->freeResult(result);
return true;
}
bool IOGuild::updateWar(War_t& war)
{
Database* db = Database::getInstance();
DBResult* result;
DBQuery query;
query << "SELECT `g`.`name` AS `guild_name`, `e`.`name` AS `enemy_name`, `w`.* FROM `guild_wars` w LEFT JOIN `guilds` g ON `w`.`guild_id` = `g`.`id` LEFT JOIN `guilds` e ON `w`.`enemy_id` = `e`.`id` WHERE `w`.`id` = " << war.war;
if(!(result = db->storeQuery(query.str())))
return false;
war.ids[WAR_GUILD] = result->getDataInt("guild_id");
war.ids[WAR_ENEMY] = result->getDataInt("enemy_id");
war.names[WAR_GUILD] = result->getDataString("guild_name");
war.names[WAR_ENEMY] = result->getDataString("enemy_name");
war.frags[WAR_GUILD] = result->getDataInt("guild_kills");
war.frags[WAR_ENEMY] = result->getDataInt("enemy_kills");
war.frags[war.type]++;
war.limit = result->getDataInt("frags");
war.payment = result->getDataInt("payment");
result->free();
if(war.frags[WAR_GUILD] >= war.limit || war.frags[WAR_ENEMY] >= war.limit)
{
Scheduler::getInstance().addEvent(createSchedulerTask(1000,
boost::bind(&IOGuild::finishWar, this, war, true)));
return true;
}
query.str("");
query << "UPDATE `guild_wars` SET `guild_kills` = " << war.frags[WAR_GUILD] << ", `enemy_kills` = " << war.frags[WAR_ENEMY] << " WHERE `id` = " << war.war;
return db->query(query.str());
}
bool IOMapSerialize::saveMapBinaryTileBased(Map*)
{
Database* db = Database::getInstance();
//Start the transaction
DBTransaction transaction(db);
if(!transaction.begin())
return false;
DBQuery query;
query << "DELETE FROM `tile_store` WHERE `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID);
if(!db->query(query.str()))
return false;
DBInsert stmt(db);
stmt.setQuery("INSERT INTO `tile_store` (`house_id`, `world_id`, `data`) VALUES ");
for(HouseMap::iterator it = Houses::getInstance()->getHouseBegin(); it != Houses::getInstance()->getHouseEnd(); ++it)
saveHouseBinaryTileBased(db, stmt, it->second);
query.str("");
if(!stmt.execute())
return false;
//End the transaction
return transaction.commit();
}
bool IOGuild::revokeInvite(uint32_t guild, uint32_t guid)
{
Database* db = Database::getInstance();
DBQuery query;
query << "DELETE FROM `guild_invites` WHERE `player_id` = " << guid << " AND `guild_id` = " << guild;
return db->query(query.str());
}
bool IOGuild::setGuildNick(uint32_t guid, const std::string& nick)
{
Database* db = Database::getInstance();
DBQuery query;
query << "UPDATE `players` SET `guildnick` = " << db->escapeString(nick) << " WHERE `id` = " << guid << db->getUpdateLimiter();
return db->query(query.str());
}
Account IOAccount::loadAccount(uint32_t accno)
{
Account acc;
Database* db = Database::instance();
DBQuery query;
DBResult* result;
query << "SELECT `id`, `password`, `premend`, `warnings` FROM `accounts` WHERE `id` = " << accno;
if((result = db->storeQuery(query.str()))){
acc.accnumber = result->getDataInt("id");
acc.password = result->getDataString("password");
acc.premEnd = result->getDataInt("premend");
acc.warnings = result->getDataInt("warnings");
db->freeResult(result);
query.str("");
query << "SELECT `name` FROM `players` WHERE `account_id` = " << accno;
if((result = db->storeQuery(query.str()))){
do {
std::string ss = result->getDataString("name");
acc.charList.push_back(ss.c_str());
} while(result->next());
acc.charList.sort();
db->freeResult(result);
}
}
return acc;
}
bool IOGuild::changeRank(uint32_t guild, const std::string& oldName, const std::string& newName)
{
Database* db = Database::getInstance();
DBResult* result;
DBQuery query;
query << "SELECT `id` FROM `guild_ranks` WHERE `guild_id` = " << guild << " AND `name` " << db->getStringComparer() << db->escapeString(oldName) << " LIMIT 1";
if(!(result = db->storeQuery(query.str())))
return false;
const uint32_t id = result->getDataInt("id");
result->free();
query.str("");
query << "UPDATE `guild_ranks` SET `name` = " << db->escapeString(newName) << " WHERE `id` = " << id << db->getUpdateLimiter();
if(!db->query(query.str()))
return false;
for(AutoList<Player>::iterator it = Player::autoList.begin(); it != Player::autoList.end(); ++it)
{
if(it->second->getRankId() == id)
it->second->setRankName(newName);
}
return true;
}
MarketOfferList IOMarket::getActiveOffers(MarketAction_t action, uint16_t itemId)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `id`, `player_id`, `amount`, `price`, `created`, `anonymous` FROM `market_offers` WHERE `sale` = "
<< action << " AND `itemtype` = " << itemId << " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << ";";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return MarketOfferList();
MarketOfferList offerList;
do
{
MarketOffer offer;
offer.amount = result->getDataInt("amount");
offer.price = result->getDataInt("price");
offer.timestamp = result->getDataInt("created") + g_config.getNumber(ConfigManager::MARKET_OFFER_DURATION);
offer.counter = result->getDataInt("id") & 0xFFFF;
if(!result->getDataInt("anonymous"))
{
IOLoginData::getInstance()->getNameByGuid(result->getDataInt("player_id"), offer.playerName);
if(offer.playerName.empty())
offer.playerName = "Anonymous";
}
else
offer.playerName = "Anonymous";
offerList.push_back(offer);
}
while(result->next());
result->free();
return offerList;
}
void IOMarket::clearOldHistory()
{
DBQuery query;
query << "DELETE FROM `market_history` WHERE `inserted` <= " << (time(NULL) - g_config.getNumber(ConfigManager::MARKET_OFFER_DURATION)) << " AND `world_id` = "
<< g_config.getNumber(ConfigManager::WORLD_ID) << ";";
Database::getInstance()->query(query.str());
}
void IOMarket::acceptOffer(uint32_t offerId, uint16_t amount)
{
DBQuery query;
query << "UPDATE `market_offers` SET `amount` = `amount` - " << amount << " WHERE `id` = " << offerId << " AND `world_id` = "
<< g_config.getNumber(ConfigManager::WORLD_ID) << ";";
Database::getInstance()->query(query.str());
}
void IOMarket::appendHistory(uint32_t playerId, MarketAction_t type, uint16_t itemId, uint16_t amount, uint32_t price, time_t timestamp, MarketOfferState_t state)
{
DBQuery query;
query << "INSERT INTO `market_history` (`player_id`, `world_id`, `sale`, `itemtype`, `amount`, `price`, `expires_at`, `inserted`, `state`) VALUES "
<< "(" << playerId << ", " << g_config.getNumber(ConfigManager::WORLD_ID) << ", " << type << ", " << itemId << ", " << amount << ", " << price << ", " << timestamp << ", " << time(NULL) << ", " << state << ");";
Database::getInstance()->query(query.str());
}
MarketOfferEx IOMarket::getOfferById(uint32_t id)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `id`, `sale`, `itemtype`, `amount`, `created`, `price`, `player_id`, `anonymous` FROM `market_offers` WHERE `id` = " << id
<< " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << ";";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return MarketOfferEx();
MarketOfferEx offer;
offer.type = (MarketAction_t)result->getDataInt("sale");
offer.amount = result->getDataInt("amount");
offer.counter = result->getDataInt("id") & 0xFFFF;
offer.timestamp = result->getDataInt("created");
offer.price = result->getDataInt("price");
offer.itemId = result->getDataInt("itemtype");
int32_t playerId = result->getDataInt("player_id");
offer.playerId = playerId;
if(!result->getDataInt("anonymous"))
{
IOLoginData::getInstance()->getNameByGuid(playerId, offer.playerName);
if(offer.playerName.empty())
offer.playerName = "Anonymous";
}
else
offer.playerName = "Anonymous";
result->free();
return offer;
}
ExpiredMarketOfferList IOMarket::getExpiredOffers(MarketAction_t action)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `id`, `amount`, `price`, `itemtype`, `player_id` FROM `market_offers` WHERE `sale` = " << action << " AND `created` <= "
<< (time(NULL) - g_config.getNumber(ConfigManager::MARKET_OFFER_DURATION)) << " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << ";";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return ExpiredMarketOfferList();
ExpiredMarketOfferList offerList;
do
{
ExpiredMarketOffer offer;
offer.id = result->getDataInt("id");
offer.amount = result->getDataInt("amount");
offer.price = result->getDataInt("price");
offer.itemId = result->getDataInt("itemtype");
offer.playerId = result->getDataInt("player_id");
offerList.push_back(offer);
}
while(result->next());
result->free();
return offerList;
}
bool IOGuild::setMotd(uint32_t guildId, std::string newMotd)
{
Database* db = Database::getInstance();
DBQuery query;
query << "UPDATE `guilds` SET `motd` = " << db->escapeString(newMotd) << " WHERE `id` = " << guildId;
return db->executeQuery(query.str());
}
bool IOGuild::canLeaveWar(uint32_t guildId)
{
if(!isInWar(guildId))
return true;
Database* db = Database::getInstance();
DBQuery query;
DBResult* result;
bool returnVal = true;
query << "SELECT `started` FROM `guild_wars` WHERE `guild1` = " << guildId << " AND `ended` = 0 AND `status` = 1;";
if((result = db->storeQuery(query.str())))
{
do {
if((result->getDataLong("started") + (86400 * 4)) >= time(NULL))
returnVal = false;
} while (result->next());
db->freeResult(result);
}
query.str("");
query << "SELECT `started` FROM `guild_wars` WHERE `guild2` = " << guildId << " AND `ended` = 0 AND `status` = 1;";
if((result = db->storeQuery(query.str())))
{
do {
if((result->getDataLong("started") + (86400 * 4)) >= time(NULL))
returnVal = false;
} while (result->next());
db->freeResult(result);
}
return returnVal;
}
void IOMarket::updateStatistics()
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `sale`, `itemtype`, COUNT(`price`) AS `num`, MIN(`price`) AS `min`, MAX(`price`) AS `max`, SUM(`price`) AS `sum` FROM `market_history` WHERE `state` = "
<< OFFERSTATE_ACCEPTED << " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << " GROUP BY `itemtype`, `sale`;";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return;
do
{
MarketStatistics* statistics;
if(result->getDataInt("sale") == MARKETACTION_BUY)
statistics = &purchaseStatistics[result->getDataInt("itemtype")];
else
statistics = &saleStatistics[result->getDataInt("itemtype")];
statistics->numTransactions = result->getDataInt("num");
statistics->lowestPrice = result->getDataInt("min");
statistics->totalPrice = result->getDataLong("sum");
statistics->highestPrice = result->getDataInt("max");
}
while(result->next());
result->free();
}
const PlayerGroup* IOPlayer::getPlayerGroup(uint32_t groupid)
{
PlayerGroupMap::const_iterator it = playerGroupMap.find(groupid);
if(it != playerGroupMap.end()){
return it->second;
}
else{
Database* db = Database::instance();
DBQuery query;
DBResult* result;
query << "SELECT * FROM `groups` WHERE `id`= " << groupid;
if((result = db->storeQuery(query.str()))){
PlayerGroup* group = new PlayerGroup;
group->m_name = result->getDataString("name");
group->m_flags = result->getDataLong("flags");
group->m_access = result->getDataInt("access");
group->m_maxDepotItems = result->getDataInt("maxdepotitems");
group->m_maxVip = result->getDataInt("maxviplist");
playerGroupMap[groupid] = group;
db->freeResult(result);
return group;
}
}
return NULL;
}
MarketOfferList IOMarket::getOwnOffers(MarketAction_t action, uint32_t playerId)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `id`, `amount`, `price`, `created`, `anonymous`, `itemtype` FROM `market_offers` WHERE `player_id` = "
<< playerId << " AND `sale` = " << action << " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << ";";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return MarketOfferList();
MarketOfferList offerList;
do
{
MarketOffer offer;
offer.amount = result->getDataInt("amount");
offer.price = result->getDataInt("price");
offer.timestamp = result->getDataInt("created") + g_config.getNumber(ConfigManager::MARKET_OFFER_DURATION);
offer.counter = result->getDataInt("id") & 0xFFFF;
offer.itemId = result->getDataInt("itemtype");
offerList.push_back(offer);
}
while(result->next());
result->free();
return offerList;
}
bool BanManager::isIpBanished(uint32_t clientip, uint32_t mask /*= 0xFFFFFFFF*/) const
{
if(clientip == 0) return false;
Database* db = Database::instance();
DBQuery query;
query <<
"SELECT "
"COUNT(*) AS `count` "
"FROM "
"`bans` "
"WHERE "
"`type` = " << BAN_IPADDRESS << " AND "
"((" << clientip << " & " << mask << " & `param`) = (`value` & `param` & " << mask << ")) AND "
"`active` = 1 AND "
"(`expires` >= " << std::time(NULL) << " OR `expires` <= 0)";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return false;
int t = result->getDataInt("count");
db->freeResult(result);
return t > 0;
}
HistoryMarketOfferList IOMarket::getOwnHistory(MarketAction_t action, uint32_t playerId)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `id`, `itemtype`, `amount`, `price`, `expires_at`, `state` FROM `market_history` WHERE `player_id` = "
<< playerId << " AND `sale` = " << action << " AND `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID) << ";";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return HistoryMarketOfferList();
HistoryMarketOfferList offerList;
do
{
HistoryMarketOffer offer;
offer.itemId = result->getDataInt("itemtype");
offer.amount = result->getDataInt("amount");
offer.price = result->getDataInt("price");
offer.timestamp = result->getDataInt("expires_at");
MarketOfferState_t offerState = (MarketOfferState_t)result->getDataInt("state");
if(offerState == OFFERSTATE_ACCEPTEDEX)
offerState = OFFERSTATE_ACCEPTED;
offer.state = offerState;
offerList.push_back(offer);
}
while(result->next());
result->free();
return offerList;
}
bool IOGuild::invitePlayer(uint32_t guild, uint32_t guid)
{
Database* db = Database::getInstance();
DBQuery query;
query << "INSERT INTO `guild_invites` (`player_id`, `guild_id`) VALUES ('" << guid << "', '" << guild << "')";
return db->query(query.str());
}
bool BanManager::isPlayerBanished(const uint32_t& playerId) const
{
Database* db = Database::instance();
DBQuery query;
query <<
"SELECT "
"COUNT(*) AS `count` "
"FROM "
"`bans` "
"WHERE "
"`type` = " << BAN_PLAYER << " AND "
"`value` = " << playerId << " AND "
"`active` = 1 AND "
"(`expires` >= " << std::time(NULL) << " OR `expires` <= 0)";
DBResult* result;
if (!(result = db->storeQuery(query.str())))
{
return false;
}
int t = result->getDataInt("count");
db->freeResult(result);
return t > 0;
}
bool IOGuild::updateOwnerId(uint32_t guild, uint32_t guid)
{
Database* db = Database::getInstance();
DBQuery query;
query << "UPDATE `guilds` SET `ownerid` = " << guid << " WHERE `id` = " << guild << db->getUpdateLimiter();
return db->query(query.str());
}
bool BanManager::removeNotations(const uint32_t& accno) const
{
Database* db = Database::instance();
DBQuery query;
query << "UPDATE `bans` SET `active` = 0 WHERE `type` = " << BAN_NOTATION << " AND `value` = " << accno << " AND `active` = 1";
return db->executeQuery(query.str());
}
bool IOGuild::setMotd(uint32_t guild, const std::string& newMessage)
{
Database* db = Database::getInstance();
DBQuery query;
query << "UPDATE `guilds` SET `motd` = " << db->escapeString(newMessage) << " WHERE `id` = " << guild << db->getUpdateLimiter();
return db->query(query.str());
}
bool IOGuild::changeRankName(std::string oldRankName, std::string newRankName, uint32_t guildId)
{
Database* db = Database::getInstance();
DBQuery query;
query << "SELECT `name` FROM `guild_ranks` WHERE `name` " << db->getStringComparer() << db->escapePatternString(newRankName) << " AND `guild_id` = " << guildId << " LIMIT 1;";
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return false;
db->freeResult(result);
query.str("");
query << "UPDATE `guild_ranks` SET `name` = " << db->escapeString(newRankName) << " WHERE `name` " << db->getStringComparer() << db->escapePatternString(oldRankName) << " AND `guild_id` = " << guildId << db->getUpdateLimiter();
if(!db->executeQuery(query.str()))
return false;
toLowerCaseString(oldRankName);
for(AutoList<Player>::listiterator it = Player::listPlayer.list.begin(); it != Player::listPlayer.list.end(); ++it)
{
if((*it).second->getGuildId() == guildId && asLowerCaseString((*it).second->getGuildRank()) == oldRankName)
(*it).second->setGuildRank(newRankName);
}
return true;
}
bool IOMapSerialize::saveMapRelational(Map*)
{
Database* db = Database::getInstance();
//Start the transaction
DBTransaction trans(db);
if(!trans.begin())
return false;
//clear old tile data
DBQuery query;
query << "DELETE FROM `tile_items` WHERE `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID);
if(!db->query(query.str()))
return false;
query.str("");
query << "DELETE FROM `tiles` WHERE `world_id` = " << g_config.getNumber(ConfigManager::WORLD_ID);
if(!db->query(query.str()))
return false;
uint32_t tileId = 0;
for(HouseMap::iterator it = Houses::getInstance()->getHouseBegin(); it != Houses::getInstance()->getHouseEnd(); ++it)
saveHouseRelational(db, it->second, tileId);
//End the transaction
return trans.commit();
}
GuildWarList IOGuild::getWarList(uint32_t guildId)
{
GuildWarList guildWarList;
Database* db = Database::getInstance();
DBQuery query;
DBResult* result;
query << "SELECT `guild1` FROM `guild_wars` WHERE `guild2` = " << guildId << " AND `ended` = 0 AND `status` = 1;";
if((result = db->storeQuery(query.str())))
{
do {
guildWarList.push_back(result->getDataInt("guild1"));
} while (result->next());
db->freeResult(result);
}
query.str("");
query << "SELECT `guild2` FROM `guild_wars` WHERE `guild1` = " << guildId << " AND `ended` = 0 AND `status` = 1;";
if((result = db->storeQuery(query.str())))
{
do {
guildWarList.push_back(result->getDataInt("guild2"));
} while (result->next());
db->freeResult(result);
}
return guildWarList;
}
bool IOMapSerialize::updateAuctions()
{
Database* db = Database::getInstance();
DBQuery query;
time_t now = time(NULL);
query << "SELECT `house_id`, `player_id`, `bid` FROM `house_auctions` WHERE `endtime` < " << now;
DBResult* result;
if(!(result = db->storeQuery(query.str())))
return true;
bool success = true;
House* house = NULL;
do
{
query.str("");
query << "DELETE FROM `house_auctions` WHERE `house_id` = " << result->getDataInt("house_id");
if(!(house = Houses::getInstance()->getHouse(result->getDataInt(
"house_id"))) || !db->query(query.str()))
{
success = false;
continue;
}
house->setOwner(result->getDataInt("player_id"));
Houses::getInstance()->payHouse(house, now, result->getDataInt("bid"));
}
while(result->next());
result->free();
return success;
}
bool DatabaseManager::isDatabaseSetup()
{
Database* db = Database::getInstance();
DBQuery query;
switch(db->getDatabaseEngine())
{
case DATABASE_ENGINE_MYSQL:
{
query << "SELECT `TABLE_NAME` FROM `information_schema`.`tables` WHERE `TABLE_SCHEMA` = " << db->escapeString(g_config.getString(ConfigManager::MYSQL_DB)) << ";";
break;
}
case DATABASE_ENGINE_SQLITE:
{
query.str("SELECT `name` FROM `sqlite_master` WHERE `type` = 'table';");
break;
}
default:
return false;
}
DBResult* result = db->storeQuery(query.str());
if(!result)
return false;
db->freeResult(result);
return true;
}
