Thoughts on Fixing the Prereq AA issue

**AndMetal** · #1 10-27-2008, 06:50 PM

Quote:

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.

Code:

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):

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), 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** · #2 11-10-2008, 11:04 PM

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** · #3 02-02-2009, 04:43 AM

Quote:

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** · #4 02-10-2009, 08:32 PM

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** · #5 02-11-2009, 01:09 AM

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** · #6 02-11-2009, 04:10 PM

Quote:

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:

Quote:

Originally Posted by AndMetal

Code:

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** · #7 02-11-2009, 07:43 PM

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!

**cavedude** · #8 02-12-2009, 02:08 PM

Quote:

Originally Posted by trevius

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

Oh crap, I meant to put you in the noob group. My finger musta slipped. Oh well.