Thoughts on Fixing the Prereq AA issue

[AndMetal]

Originally Posted by KLS

It doesn't fix any sort of real problem it just makes you lazy bastards happy. =p

Originally Posted by trevius

And what is so bad about being lazy?! :P I like to think of it as more efficient. Who wants to do things the hard way?

I thought that's what coding was all about...

[trevius]

Finally broke down and downloaded to PEQ DB and found out I was missing about 60 AAs! That is both awesome and sad at the same time lol.

[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.

[cavedude]

Originally Posted by trevius

Finally broke down and downloaded to PEQ DB and found out I was missing about 60 AAs! That is both awesome and sad at the same time lol.

A bit of warning, many of them may not work. I have fixed most of them since, but there is still some tweaking to be done. I figure I'll get everything as clean as possible before letting other cooks into the kitchen. PEQ CVS will be updated as well as the code's SVN when that happens.

[AndMetal]

Originally Posted by cavedude

I figure I'll get everything as clean as possible before letting other cooks into the kitchen. PEQ CVS will be updated as well as the code's SVN when that happens.

I don't think I follow...

[cavedude]

I just meant I am going to get the AA tables as correct as possible before releasing them so other people can work with them. It's silly to have 4 or 5 different people working with AAs and not even working off a single DB.