C++ QueryDatabase函数代码示例

void Database::LogPlayerTSEvents(QSPlayerTSEvents_Struct* QS, uint32 items)
{
	if (items == 0)
	{
		return;
	}

	std::string query = StringFormat(
		"INSERT INTO `qs_player_ts_event_log` SET "
			"`char_id` = '%i', "
			"`zone_id` = '%i', "
			"`instance_id` = '%i', "
			"`results` = '%s', "
			"`recipe_id` = '%i', "
			"`tradeskill` = '%i', "
			"`trivial` = '%i', "
			"`chance` = '%f', "
			"`time` = now()",
			QS->charid,
			QS->zone_id,
			QS->instance_id,
			QS->results,
			QS->recipe_id,
			QS->tradeskill,
			QS->trivial,
			QS->chance);

	auto results = QueryDatabase(query);
	if (!results.Success())
	{
		Log.Out(Logs::Detail, Logs::QS_Server, "Failed TS Event Log Record Insert: %s\n%s", results.ErrorMessage().c_str(), query.c_str());
	}
}

bool Database::VerifyMailKey(std::string characterName, int IPAddress, std::string MailKey) {

	std::string query = StringFormat("SELECT `mailkey` FROM `character_` WHERE `name`='%s' LIMIT 1",
                                    characterName.c_str());
    auto results = QueryDatabase(query);
	if (!results.Success()) {
		_log(UCS__ERROR, "Error retrieving mailkey from database: %s", results.ErrorMessage().c_str());
		return false;
	}

	auto row = results.begin();

	// The key is the client's IP address (expressed as 8 hex digits) and an 8 hex digit random string generated
	// by world.
	//
	char combinedKey[17];

	if(RuleB(Chat, EnableMailKeyIPVerification) == true)
		sprintf(combinedKey, "%08X%s", IPAddress, MailKey.c_str());
	else
		sprintf(combinedKey, "%s", MailKey.c_str());

	_log(UCS__TRACE, "DB key is [%s], Client key is [%s]", row[0], combinedKey);

	return !strcmp(row[0], combinedKey);
}

void Database::SetMessageStatus(int messageNumber, int status) {

	_log(UCS__TRACE, "SetMessageStatus %i %i", messageNumber, status);

	if(status == 0) {
        std::string query = StringFormat("DELETE FROM `mail` WHERE `msgid` = %i", messageNumber);
		auto results = QueryDatabase(query);
        return;
    }

    std::string query = StringFormat("UPDATE `mail` SET `status` = %i WHERE `msgid`=%i", status, messageNumber);
    auto results = QueryDatabase(query);
	if (!results.Success())
		_log(UCS__ERROR, "Error updating status %s, %s", query.c_str(), results.ErrorMessage().c_str());

}

bool ZoneDatabase::LoadDoors(int32 iDoorCount, Door *into, const char *zone_name, int16 version) {
	Log.Out(Logs::General, Logs::Status, "Loading Doors from database...");


//	Door tmpDoor;
    std::string query = StringFormat("SELECT id, doorid, zone, name, pos_x, pos_y, pos_z, heading, "
                                    "opentype, guild, lockpick, keyitem, nokeyring, triggerdoor, triggertype, "
                                    "dest_zone, dest_instance, dest_x, dest_y, dest_z, dest_heading, "
                                    "door_param, invert_state, incline, size, client_version_mask, altkeyitem "
                                    "FROM doors WHERE zone = '%s' AND (version = %u OR version = -1) "
                                    "ORDER BY doorid asc", zone_name, version);
	auto results = QueryDatabase(query);
	if (!results.Success()){
		return false;
	}

    int32 rowIndex = 0;
    for(auto row = results.begin(); row != results.end(); ++row, ++rowIndex) {
        if(rowIndex >= iDoorCount) {
            std::cerr << "Error, Door Count of " << iDoorCount << " exceeded." << std::endl;
            break;
        }

        memset(&into[rowIndex], 0, sizeof(Door));

		into[rowIndex].db_id = atoi(row[0]);
		into[rowIndex].door_id = atoi(row[1]);

        strn0cpy(into[rowIndex].zone_name,row[2],32);
		strn0cpy(into[rowIndex].door_name,row[3],32);

		into[rowIndex].pos_x = (float)atof(row[4]);
		into[rowIndex].pos_y = (float)atof(row[5]);
		into[rowIndex].pos_z = (float)atof(row[6]);
		into[rowIndex].heading = (float)atof(row[7]);
		into[rowIndex].opentype = atoi(row[8]);
		into[rowIndex].guild_id = atoi(row[9]);
		into[rowIndex].lock_pick = atoi(row[10]);
		into[rowIndex].keyitem = atoi(row[11]);
		into[rowIndex].nokeyring = atoi(row[12]);
		into[rowIndex].trigger_door = atoi(row[13]);
		into[rowIndex].trigger_type = atoi(row[14]);

		strn0cpy(into[rowIndex].dest_zone, row[15], 32);

		into[rowIndex].dest_instance_id = atoi(row[16]);
		into[rowIndex].dest_x = (float) atof(row[17]);
		into[rowIndex].dest_y = (float) atof(row[18]);
		into[rowIndex].dest_z = (float) atof(row[19]);
		into[rowIndex].dest_heading = (float) atof(row[20]);
		into[rowIndex].door_param=atoi(row[21]);
		into[rowIndex].invert_state=atoi(row[22]);
		into[rowIndex].incline=atoi(row[23]);
		into[rowIndex].size=atoi(row[24]);
		into[rowIndex].client_version_mask = (uint32)strtoul(row[25], nullptr, 10);
		into[rowIndex].altkeyitem = atoi(row[26]);
    }

	return true;
}

//this really need to be renamed to LoadAAActions()
bool ZoneDatabase::LoadAAEffects() {
	memset(AA_Actions, 0, sizeof(AA_Actions));	//I hope the compiler is smart about this size...

	const std::string query = "SELECT aaid, rank, reuse_time, spell_id, target, "
                            "nonspell_action, nonspell_mana, nonspell_duration, "
                            "redux_aa, redux_rate, redux_aa2, redux_rate2 FROM aa_actions";
    auto results = QueryDatabase(query);
    if (!results.Success()) {
		return false;
	}

	for (auto row = results.begin(); row != results.end(); ++row) {

		int aaid = atoi(row[0]);
		int rank = atoi(row[1]);
		if (aaid < 0 || aaid >= aaHighestID || rank < 0 || rank >= MAX_AA_ACTION_RANKS)
			continue;
		AA_DBAction *caction = &AA_Actions[aaid][rank];

		caction->reuse_time = atoi(row[2]);
		caction->spell_id = atoi(row[3]);
		caction->target = (aaTargetType)atoi(row[4]);
		caction->action = (aaNonspellAction)atoi(row[5]);
		caction->mana_cost = atoi(row[6]);
		caction->duration = atoi(row[7]);
		caction->redux_aa = (aaID)atoi(row[8]);
		caction->redux_rate = atoi(row[9]);
		caction->redux_aa2 = (aaID)atoi(row[10]);
		caction->redux_rate2 = atoi(row[11]);

	}

	return true;
}

bool ZoneDatabase::LoadAAEffects2() {
	aa_effects.clear();	//start fresh

	const std::string query = "SELECT aaid, slot, effectid, base1, base2 FROM aa_effects ORDER BY aaid ASC, slot ASC";
	auto results = QueryDatabase(query);
	if (!results.Success()) {
		return false;
	}

	if (!results.RowCount()) { //no results
        Log.Out(Logs::General, Logs::Error, "Error loading AA Effects, none found in the database.");
        return false;
	}

	for (auto row = results.begin(); row != results.end(); ++row) {
		int aaid = atoi(row[0]);
		int slot = atoi(row[1]);
		int effectid = atoi(row[2]);
		int base1 = atoi(row[3]);
		int base2 = atoi(row[4]);
		aa_effects[aaid][slot].skill_id = effectid;
		aa_effects[aaid][slot].base1 = base1;
		aa_effects[aaid][slot].base2 = base2;
		aa_effects[aaid][slot].slot = slot;	//not really needed, but we'll populate it just in case
	}

	return true;
}

bool ZoneDatabase::SetTrapData(Trap* trap, bool repopnow) {

	uint32 dbid = trap->db_id;
	std::string query;
	
	if(trap->group > 0)
	{
		query = StringFormat("SELECT id, x, y, z, effect, effectvalue, effectvalue2, skill, "
			"maxzdiff, radius, chance, message, respawn_time, respawn_var, level, "
			"triggered_number, despawn_when_triggered FROM traps WHERE zone='%s' AND `group`=%d AND id != %d ORDER BY RAND() LIMIT 1", zone->GetShortName(), trap->group, dbid);
	}
	else
	{
		// We could just use the existing data here, but querying the DB is not expensive, and allows content developers to change traps without rebooting.
		query = StringFormat("SELECT id, x, y, z, effect, effectvalue, effectvalue2, skill, "
			"maxzdiff, radius, chance, message, respawn_time, respawn_var, level, "
			"triggered_number, despawn_when_triggered FROM traps WHERE zone='%s' AND id = %d", zone->GetShortName(), dbid);
	}

	auto results = QueryDatabase(query);
	if (!results.Success()) {
		return false;
	}

	for (auto row = results.begin(); row != results.end(); ++row) {

		trap->db_id = atoi(row[0]);
		trap->m_Position = glm::vec3(atof(row[1]), atof(row[2]), atof(row[3]));
		trap->effect = atoi(row[4]);
		trap->effectvalue = atoi(row[5]);
		trap->effectvalue2 = atoi(row[6]);
		trap->skill = atoi(row[7]);
		trap->maxzdiff = atof(row[8]);
		trap->radius = atof(row[9]);
		trap->chance = atoi(row[10]);
		trap->message = row[11];
		trap->respawn_time = atoi(row[12]);
		trap->respawn_var = atoi(row[13]);
		trap->level = atoi(row[14]);
		trap->triggered_number = atoi(row[15]);
		trap->despawn_when_triggered = atoi(row[16]);
		trap->CreateHiddenTrigger();

		if(repopnow)
		{
			trap->chkarea_timer.Enable();
		}
		else
		{
			trap->respawn_timer.Start((trap->respawn_time + zone->random.Int(0, trap->respawn_var)) * 1000);
		}

		if(trap->trap_id != trap->db_id)
			Log.Out(Logs::General, Logs::Traps, "Trap (%d) DBID has changed from %d to %d", trap->trap_id, dbid, trap->db_id);

		return true;
	}

	return false;
}

bool ZoneDatabase::GetWaypoints(uint32 grid, uint16 zoneid, uint32 num, wplist* wp) {

    if (wp == nullptr)
        return false;

	std::string query = StringFormat("SELECT x, y, z, pause, heading FROM grid_entries "
                                    "WHERE gridid = %i AND number = %i AND zoneid = %i", grid, num, zoneid);
    auto results = QueryDatabase(query);
    if (!results.Success()) {
        LogFile->write(EQEMuLog::Error, "Error in GetWaypoints query '%s': %s", query.c_str(), results.ErrorMessage().c_str());
        return false;
    }

	if (results.RowCount() != 1)
        return false;

    auto row = results.begin();

    wp->x = atof(row[0]);
    wp->y = atof(row[1]);
    wp->z = atof(row[2]);
    wp->pause = atoi(row[3]);
    wp->heading = atof(row[4]);

    return true;
}

// Update information about existing object in database
void ZoneDatabase::UpdateObject(uint32 id, uint32 type, uint32 icon, const Object_Struct& object, const ItemInst* inst)
{
	uint32 item_id = 0;
	int16 charges = 0;

	if (inst && inst->GetItem()) {
		item_id = inst->GetItem()->ID;
		charges = inst->GetCharges();
	}

	// SQL Escape object_name
	uint32 len = strlen(object.object_name) * 2 + 1;
	char* object_name = new char[len];
	DoEscapeString(object_name, object.object_name, strlen(object.object_name));

	// Save new record for object
	std::string query = StringFormat("UPDATE object SET "
                                    "zoneid = %i, xpos = %f, ypos = %f, zpos = %f, heading = %f, "
                                    "itemid = %i, charges = %i, objectname = '%s', type = %i, icon = %i "
                                    "WHERE id = %i",
                                    object.zone_id, object.x, object.y, object.z, object.heading,
                                    item_id, charges, object_name, type, icon, id);
    safe_delete_array(object_name);
    auto results = QueryDatabase(query);
	if (!results.Success()) {
		LogFile->write(EQEMuLog::Error, "Unable to update object: %s", results.ErrorMessage().c_str());
		return;
	}

    // Save container contents, if container
    if (inst && inst->IsType(ItemClassContainer))
        SaveWorldContainer(object.zone_id, id, inst);
}

bool WorldDatabase::LoadCharacterCreateAllocations()
{
	character_create_allocations.clear();

	std::string query = "SELECT * FROM char_create_point_allocations ORDER BY id";
	auto results = QueryDatabase(query);
	if (!results.Success())
        return false;

    for (auto row = results.begin(); row != results.end(); ++row) {
        RaceClassAllocation allocate;
		allocate.Index = atoi(row[0]);
		allocate.BaseStats[0] = atoi(row[1]);
		allocate.BaseStats[3] = atoi(row[2]);
		allocate.BaseStats[1] = atoi(row[3]);
		allocate.BaseStats[2] = atoi(row[4]);
		allocate.BaseStats[4] = atoi(row[5]);
		allocate.BaseStats[5] = atoi(row[6]);
		allocate.BaseStats[6] = atoi(row[7]);
		allocate.DefaultPointAllocation[0] = atoi(row[8]);
		allocate.DefaultPointAllocation[3] = atoi(row[9]);
		allocate.DefaultPointAllocation[1] = atoi(row[10]);
		allocate.DefaultPointAllocation[2] = atoi(row[11]);
		allocate.DefaultPointAllocation[4] = atoi(row[12]);
		allocate.DefaultPointAllocation[5] = atoi(row[13]);
		allocate.DefaultPointAllocation[6] = atoi(row[14]);

		character_create_allocations.push_back(allocate);
    }

	return true;
}

bool ZoneDatabase::GetAuraEntry(uint16 spell_id, AuraRecord &record)
{
	auto query = StringFormat("SELECT npc_type, name, spell_id, distance, aura_type, spawn_type, movement, "
				  "duration, icon, cast_time FROM auras WHERE type='%d'",
				  spell_id);

	auto results = QueryDatabase(query);
	if (!results.Success())
		return false;

	if (results.RowCount() != 1)
		return false;

	auto row = results.begin();

	record.npc_type = atoi(row[0]);
	strn0cpy(record.name, row[1], 64);
	record.spell_id = atoi(row[2]);
	record.distance = atoi(row[3]);
	record.distance *= record.distance; // so we can avoid sqrt
	record.aura_type = atoi(row[4]);
	record.spawn_type = atoi(row[5]);
	record.movement = atoi(row[6]);
	record.duration = atoi(row[7]) * 1000; // DB is in seconds
	record.icon = atoi(row[8]);
	record.cast_time = atoi(row[9]) * 1000; // DB is in seconds

	return true;
}

Ground_Spawns* ZoneDatabase::LoadGroundSpawns(uint32 zone_id, int16 version, Ground_Spawns* gs) {

	std::string query = StringFormat("SELECT max_x, max_y, max_z, "
                                    "min_x, min_y, heading, name, "
                                    "item, max_allowed, respawn_timer "
                                    "FROM ground_spawns "
                                    "WHERE zoneid = %i AND (version = %u OR version = -1) "
                                    "LIMIT 50", zone_id, version);
    auto results = QueryDatabase(query);
    if (!results.Success()) {
        std::cerr << "Error in LoadGroundSpawns query '" << query << "' " << results.ErrorMessage() << std::endl;
		return gs;
	}

	int spawnIndex=0;
    for (auto row = results.begin(); row != results.end(); ++row, ++spawnIndex) {
        gs->spawn[spawnIndex].max_x=atof(row[0]);
        gs->spawn[spawnIndex].max_y=atof(row[1]);
        gs->spawn[spawnIndex].max_z=atof(row[2]);
        gs->spawn[spawnIndex].min_x=atof(row[3]);
        gs->spawn[spawnIndex].min_y=atof(row[4]);
        gs->spawn[spawnIndex].heading=atof(row[5]);
        strcpy(gs->spawn[spawnIndex].name,row[6]);
        gs->spawn[spawnIndex].item=atoi(row[7]);
        gs->spawn[spawnIndex].max_allowed=atoi(row[8]);
        gs->spawn[spawnIndex].respawntimer=atoi(row[9]);
    }
	return gs;
}

Spawn2* ZoneDatabase::LoadSpawn2(LinkedList<Spawn2*> &spawn2_list, uint32 spawn2id, uint32 timeleft) {

	std::string query = StringFormat("SELECT id, spawngroupID, x, y, z, heading, "
                                    "respawntime, variance, pathgrid, _condition, "
                                    "cond_value, enabled, animation FROM spawn2 "
                                    "WHERE id = %i", spawn2id);
    auto results = QueryDatabase(query);
    if (!results.Success()) {
        return nullptr;
    }

    if (results.RowCount() != 1) {
        return nullptr;
    }

	auto row = results.begin();

    bool perl_enabled = atoi(row[11]) == 1 ? true : false;

	Spawn2* newSpawn = new Spawn2(atoi(row[0]), atoi(row[1]), atof(row[2]), atof(row[3]), atof(row[4]),
                                    atof(row[5]), atoi(row[6]), atoi(row[7]), timeleft, atoi(row[8]),
                                    atoi(row[9]), atoi(row[10]), perl_enabled, (EmuAppearance)atoi(row[12]));

    spawn2_list.Insert(newSpawn);

	return newSpawn;
}

bool Database::Create_Merchant_Table()
{
	Log.Out(Logs::Detail, Logs::QS_Server, "Attempting to create merchant table.");
	std::string query = StringFormat(
		"CREATE TABLE `qs_merchant_transaction_log` ( "
		"`char_id` int(11) DEFAULT '0', "
		"`char_slot` mediumint(7) DEFAULT '0', "
		"`item_id` int(11) DEFAULT '0', "
		"`charges` mediumint(7) DEFAULT '0', "
		"`zone_id` int(11) DEFAULT '0', "
		"`merchant_id` int(11) DEFAULT '0', "
		"`merchant_pp` int(11) DEFAULT '0', "
		"`merchant_gp` int(11) DEFAULT '0', "
		"`merchant_sp` int(11) DEFAULT '0', "
		"`merchant_cp` int(11) DEFAULT '0', "
		"`merchant_items` mediumint(7) DEFAULT '0', "
		"`char_pp` int(11) DEFAULT '0', "
		"`char_gp` int(11) DEFAULT '0', "
		"`char_sp` int(11) DEFAULT '0', "
		"`char_cp` int(11) DEFAULT '0', "
		"`char_items` mediumint(7) DEFAULT '0', "
		"`time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP "
		") ENGINE = InnoDB DEFAULT CHARSET = utf8;");
	auto results = QueryDatabase(query);
	if (!results.Success())
	{
		Log.Out(Logs::General, Logs::QS_Server, "Error creating qs_merchant_transaction_log. \n%s", query.c_str());
		return false;
	}
	return true;
}

bool ZoneDatabase::GetPoweredPetEntry(const char *pet_type, int16 petpower, PetRecord *into) {
	std::string query;

	if (petpower <= 0)
		query = StringFormat("SELECT npcID, temp, petpower, petcontrol, petnaming, monsterflag, equipmentset "
							"FROM pets WHERE type='%s' AND petpower<=0", pet_type);
	else
		query = StringFormat("SELECT npcID, temp, petpower, petcontrol, petnaming, monsterflag, equipmentset "
							"FROM pets WHERE type='%s' AND petpower<=%d ORDER BY petpower DESC LIMIT 1",
							pet_type, petpower);

	auto results = QueryDatabase(query);
	if (!results.Success()) {
		return false;
	}

	if (results.RowCount() != 1)
		return false;

	auto row = results.begin();

	into->npc_type = atoi(row[0]);
	into->temporary = atoi(row[1]);
	into->petpower = atoi(row[2]);
	into->petcontrol = atoi(row[3]);
	into->petnaming = atoi(row[4]);
	into->monsterflag = atoi(row[5]);
	into->equipmentset = atoi(row[6]);

	return true;
}