Quote:
Originally Posted by seveianrex
The client interprets the AA Index which is why we store that value in the DB. We pull the value for each AA from the DB and send it to the client in the process ZoneDatabase:GetAASkillVars() (aa.cpp).
|
That's what it looks like, since all we do with it is put it into the OP_SendAATable packet & send that off to the client. As a result, the client gets it in the same order we're getting it from the DB
Code:
RunQuery(query, MakeAnyLenString(&query, "SELECT cost, max_level, hotkey_sid, hotkey_sid2, "
"title_sid, desc_sid, type, prereq_skill, prereq_minpoints, spell_type, spell_refresh, "
"classes, berserker,spellid,class_type,name,cost_inc"
" FROM altadv_vars WHERE skill_id=%i", skill_id), errbuf, &result)
which is sorted by the index number since we don't have a SORT BY clause.
I'm wondering if we can do all of this with a modified DB query (using some sort of subquery probably) that translates a prereq_skill as a skill_id to the index #?
After working over this for a bit (with some
inspiration), here's the query I came up with:
Code:
SET @row = 0;
SELECT
a.cost,
a.max_level,
a.hotkey_sid,
a.hotkey_sid2,
a.title_sid,
a.desc_sid,
a.type,
COALESCE(
(
SELECT
prereq_index_num
FROM
(
SELECT
@row := @row + 1 AS prereq_index_num
FROM
altadv_vars
) AS prereq_conv
WHERE
prereq_skill = prereq_index_num
),
0) AS prereq_skill_index,
a.prereq_minpoints,
a.spell_type,
a.spell_refresh,
a.classes,
a.berserker,
a.spellid,
a.class_type,
a.name,
a.cost_inc
FROM
altadv_vars a
;
which you actually have to execute as this for it to work properly:
Code:
SET @row = 0;
SELECT a.cost, a.max_level, a.hotkey_sid, a.hotkey_sid2, a.title_sid, a.desc_sid, a.type, COALESCE((SELECT prereq_index_num FROM (SELECT @row := @row + 1 AS prereq_index_num FROM altadv_vars) AS prereq_conv WHERE prereq_skill = prereq_index_num), 0) AS prereq_skill_index, a.prereq_minpoints, a.spell_type, a.spell_refresh, a.classes, a.berserker, a.spellid, a.class_type, a.name, a.cost_inc FROM altadv_vars a;
The only problem I've run into with the query is being able to manipulate the derived column, prereq_skill_index, in the where clause:
Code:
mysql> SELECT a.cost, a.max_level, a.hotkey_sid, a.hotkey_sid2, a.title_sid, a.desc_sid, a.type, COALESCE((SELECT prereq_index_num FROM (SELECT @row := @row + 1 AS prereq_index_num FROM altadv_vars) AS prereq_conv WHERE prereq_skill = prereq_index_num), 0) AS prereq_skill_index, a.prereq_minpoints, a.spell_type, a.spell_refresh, a.classes, a.berserker, a.spellid, a.class_type, a.name, a.cost_inc FROM altadv_vars a WHERE prereq_index_num > 0;
ERROR 1054 (42S22): Unknown column 'prereq_index_num' in 'where clause'
However, at this point, we don't really need to do anything with it, and even if we did, we should be able to utilize the still existing prereq_skill column in the altadv_vars table.
We could definitely iterate through the possible AA IDs, but I think this would be a lot cleaner & less resource intensive (relatively, since we're only talking about a few hundred to a few thousand loops, depending on how quickly we find our match).
So, if we decide to go this route, we can do this:
in zone/AA.cpp, around line 1241 in ZoneDatabase::GetAASkillVars(), change
Code:
if (RunQuery(query, MakeAnyLenString(&query, "SET @row = 0"), errbuf, &result)) { //initialize "row" variable in database for next query
query = 0; //reset for next query
if (RunQuery(query, MakeAnyLenString(&query,
"SELECT cost, "
"max_level, "
"hotkey_sid, "
"hotkey_sid2, "
"title_sid, "
"desc_sid, "
"type, "
"COALESCE(" //so we can return 0 if it's null
"(" //this is our derived table that has the row # that we can SELECT from, because the client is stupid
"SELECT "
"prereq_index_num "
"FROM "
"("
"SELECT "
"@row := @row + 1 AS prereq_index_num "
"FROM "
"altadv_vars"
") AS prereq_conv "
"WHERE "
"prereq_skill = prereq_index_num"
"), "
"0) AS prereq_skill_index, "
"prereq_minpoints, "
"spell_type, "
"spell_refresh, "
"classes, "
"berserker, "
"spellid, "
"class_type, "
"name, "
"cost_inc "
" FROM altadv_vars WHERE skill_id=%i", skill_id), errbuf, &result)) {
safe_delete_array(query);
if (mysql_num_rows(result) == 1) {
int total_abilities = GetTotalAALevels(skill_id);
int totalsize = total_abilities * sizeof(AA_Ability) + sizeof(SendAA_Struct);
buffer = new uchar[totalsize];
memset(buffer,0,totalsize);
sendaa = (SendAA_Struct*)buffer;
row = mysql_fetch_row(result);
//ATOI IS NOT UNISGNED LONG-SAFE!!!
sendaa->cost = atoul(row[0]);
sendaa->cost2 = sendaa->cost;
sendaa->max_level = atoul(row[1]);
sendaa->hotkey_sid = atoul(row[2]);
sendaa->id = skill_id;
sendaa->hotkey_sid2 = atoul(row[3]);
sendaa->title_sid = atoul(row[4]);
sendaa->desc_sid = atoul(row[5]);
sendaa->type = atoul(row[6]);
sendaa->prereq_skill = atoul(row[7]);
sendaa->prereq_minpoints = atoul(row[8]);
sendaa->spell_type = atoul(row[9]);
sendaa->spell_refresh = atoul(row[10]);
sendaa->classes = atoul(row[11]);
sendaa->berserker = atoul(row[12]);
sendaa->last_id = 0xFFFFFFFF;
sendaa->current_level=1;
sendaa->spellid = atoul(row[13]);
sendaa->class_type = atoul(row[14]);
strcpy(sendaa->name,row[15]);
sendaa->total_abilities=total_abilities;
if(sendaa->max_level > 1)
sendaa->next_id=skill_id+1;
else
sendaa->next_id=0xFFFFFFFF;
sendaa->cost_inc = atoi(row[16]);
}
mysql_free_result(result);
} else {
LogFile->write(EQEMuLog::Error, "Error in GetAASkillVars '%s': %s", query, errbuf);
safe_delete_array(query);
}
} else {
LogFile->write(EQEMuLog::Error, "Error in GetAASkillVars '%s': %s", query, errbuf);
safe_delete_array(query);
}
On a side note, I think this may also be a good opportunity to merge the classes & berserker columns in the database, since they can be OR'd together, and separate them out in the same function.
Any feedback?
Threaded Mode
