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.

[AndMetal]

Originally Posted by AndMetal

Next step, creating a query to migrate from the indexes to the skill IDs so we don't have to do it by hand.

SET @row = 0;
UPDATE altadv_vars a SET prereq_skill = COALESCE((SELECT p.skill_id FROM (SELECT a2.skill_id, @row := @row + 1 AS prereq_index_num FROM altadv_vars a2) AS p WHERE p.prereq_index_num = a.prereq_skill), 0) WHERE prereq_skill < 1000000;

I also had to make a slight change to the query so that it doesn't fubar archetype & class requirements (4294967295 & 4294967294):

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), a.prereq_skill) AS prereq_skill_index

Because of the database changes needed, I'm going to hold off on committing this for now until everyone feels comfortable with making the switch.

[trevius]

I am going to test this out on my server the next time I update to the SVN (probably this weekend). Figured it was worth asking the status of the possibility of getting this committed if it continues to test well. Does anyone have issues with this being committed as long as it works as intended? I really think it will make adding new SQL for AAs considerably easier.

Cavedude is right that we should all be working off the same table when creating or fixing AAs. But, with this code, we could be working from completely different tables and it shouldn't make any difference.

[AndMetal]

Originally Posted by AndMetal

Because of the database changes needed, I'm going to hold off on committing this for now until everyone feels comfortable with making the switch.

Has anyone had a chance to work with this yet? If so, I think we might be able to start adding more missing AAs.

[trevius]

I would love to see this get implemented. I think cavedude should have the final word, since the PEQ database is the most important thing to have adjusted for this change. Once we have a good table to work from that has these modifications in it, it should make adding AAs quite a bit easier. Then, new AA SQL updates could be added to the /sql/svn directory when doing SVN commits just like any other SQL changes.

I think this just got forgotten about after the new AA additions on the SVN died down. I forget if I tested this or not yet. I would test it, but I don't really have much extra free time lately with all of the other stuff I have going :P

[cavedude]

Yeah, I've forgotten about this thread as well. Let me give the code a try, and work out a query to migrate the current AAs to the new system. If all goes well, I don't see a reason not to commit it.

[AndMetal]

Originally Posted by cavedude

Let me give the code a try, and work out a query to migrate the current AAs to the new system.

This is the final migration query, which worked for me when I tested it out a while ago:

Originally Posted by AndMetal

SET @row = 0;
UPDATE altadv_vars a SET prereq_skill = COALESCE((SELECT p.skill_id FROM (SELECT a2.skill_id, @row := @row + 1 AS prereq_index_num FROM altadv_vars a2) AS p WHERE p.prereq_index_num = a.prereq_skill), 0) WHERE prereq_skill < 1000000;

To clarify, it worked great for me when I worked on it a few months ago, but because of the need to make 1 migration going forward (and everything could get VERY confusing if you run it multiple times), I didn't want to force everyone into it right away.

I think the only thing I didn't do was combine the classes & berserker columns in the db, then separate them in the code (easy enough to do).

[trevius]

I don't think we need to combine the classes and berserker columns. They are sent to the client that way, so it doesn't hurt having 1 extra field to fill out. It wouldn't hurt to do it, but there isn't really much point to do so. Even on EQLive, it is still sent that same way for some reason. They are probably to lazy to do it :P

EDIT: Woah, I am developer now, lol! Be afraid!