Thoughts on Fixing the Prereq AA issue

[AndMetal]

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:

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:

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.