After working with the query a little bit, I figured out I was comparing index to index rather than skill_id to skill_id, so nothing was actually happening. I just had to change the prereq_skill_index part:
Code:
COALESCE((SELECT prereq_index_num FROM (SELECT skill_id, @row := @row + 1 AS prereq_index_num FROM altadv_vars) AS p WHERE p.skill_id = a.prereq_skill), 0) AS prereq_skill_index
So, the code is working as I expected now:
Code:
Index: Z:/svn/EQEmuServer/zone/AA.cpp
===================================================================
--- Z:/svn/EQEmuServer/zone/AA.cpp (revision 158)
+++ Z:/svn/EQEmuServer/zone/AA.cpp (working copy)
@@ -1233,58 +1233,96 @@
SendAA_Struct* ZoneDatabase::GetAASkillVars(int32 skill_id)
{
char errbuf[MYSQL_ERRMSG_SIZE];
- char *query = 0;
- MYSQL_RES *result;
- MYSQL_ROW row;
+ char *query = 0;
SendAA_Struct* sendaa = NULL;
uchar* buffer;
- if (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)) {
- 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]);
+ if (RunQuery(query, MakeAnyLenString(&query, "SET @row = 0"), errbuf)) { //initialize "row" variable in database for next query
+ query = 0; //reset for next query
+ MYSQL_RES *result; //we don't really need these unless we get to this point, so why bother?
+ MYSQL_ROW row;
+
+ if (RunQuery(query, MakeAnyLenString(&query,
+ "SELECT "
+ "a.cost, "
+ "a.max_level, "
+ "a.hotkey_sid, "
+ "a.hotkey_sid2, "
+ "a.title_sid, "
+ "a.desc_sid, "
+ "a.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 "
+ "p.prereq_index_num "
+ "FROM "
+ "("
+ "SELECT "
+ "a2.skill_id, "
+ "@row := @row + 1 AS prereq_index_num "
+ "FROM "
+ "altadv_vars a2"
+ ") AS p "
+ "WHERE "
+ "p.skill_id = a.prereq_skill"
+ "), "
+ "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 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);
}
- mysql_free_result(result);
} else {
LogFile->write(EQEMuLog::Error, "Error in GetAASkillVars '%s': %s", query, errbuf);
safe_delete_array(query);
Next step, creating a query to migrate from the indexes to the skill IDs so we don't have to do it by hand.