Requested Change to the prereq_skill field in altadv_vars table

[trevius]

Well, after failing to properly add some new AAs and accidentally breaking many AAs so they couldn't be trained due to Requirement issues, I finally figured out how the prereq_skill field works in the altadv_vars table. The problem with it is that currently if you add any AAs to that table, you have to update every AA that uses a requirement higher than the line number that the AA you added is in.

AAs are ordered by the skill_id field. And, the prereq_skill field actually uses the line number in the table, not any actual field data from the table. So, if you add an AA with skill_id of 225 and it actually goes in at line 100 in the table, then in order to set another AA to require that one, you must set the prereq_skill field to 100 in the AA you want to require the one you added before.

The problem with this is that there are no line number in the table, so to find out what to set the prereq_skill field to, you have to COUNT each row until you get to the one you want it to require and that is the number.

An even worse problem with this system is that if you add any new AAs in the table, it will bump the line numbers on any AAs that come after it, which throws off the prereq_skill field for anything requiring an AA higher than the one you added. This means that anytime you add an AA, you have to increment all prereq_skills higher than the one you added by 1 for them to align properly again.

If possible, I propose a change to this system. Either add in an incrementing ID number field that gets added to for each new AA, or probably the best solution is to set the prereq_skill field to look for the skill_id instead of the line number.

[ChaosSlayer]

i second that
ANother thing. IF any of the AA actualy just a reference to some sort of Spell effect in a spell file, those spell ID# should not be hardcoded. Instead they should be put into a separate table where each such AA can be assigned a spell ID#, and later can be changed to something else by choice wihout affecting any of the server hardcode

[KLS]

SET @idindex = 0;
SELECT skill_id, name, (@idindex:=@idindex+1) FROM altadv_vars ORDER BY skill_id ASC;

Works on 5.0 not sure about other mysql vers.

[AndMetal]

Originally Posted by ChaosSlayer

IF any of the AA actualy just a reference to some sort of Spell effect in a spell file, those spell ID# should not be hardcoded. Instead they should be put into a separate table where each such AA can be assigned a spell ID#, and later can be changed to something else by choice wihout affecting any of the server hardcode

You mean like the aa_effects table?

[MNWatchdog]

Wow..gross.

KLS, yes, there may be a way to fix it with a SQL command, but doesnt mean it shouldnt be done better in the first place.

Plus, having to execute that SQL command every time you want to modify the AA table is just poor implimentation.

[trevius]

Ya, it is unfortunate, but it looks like the indexing thing is mandated by the client, not something the emu team decided to put in for fun :P

AndMetal has already made a suggestion of a work-around for it here:

http://www.eqemulator.net/forums/showthread.php?t=26638

[ChaosSlayer]

Originally Posted by AndMetal

You mean like the aa_effects table?

I must have been looking at the wrong table lol.

so if I change price for AA - will that actualy work?
Cuase I tried giving some AA (like Innate Agilty) more ranks but produced some weird errors/effects with client

[trevius]

If you want to add more ranks, you would need to add more ranks in the source code as well. There is no way around that. The actual effects that the AAs have all need to be hard coded into the source in appropriate areas. There is no way to put that stuff into a table. The only rank editing you can do from a table is lowering it. You can adjust the cost of the AAs, the requirements for AAs, and even the classes that can get AAs from the tables, but that is about it without going into the source and making changes.

[AndMetal]

Originally Posted by MNWatchdog

Wow..gross.

KLS, yes, there may be a way to fix it with a SQL command, but doesnt mean it shouldnt be done better in the first place.

Plus, having to execute that SQL command every time you want to modify the AA table is just poor implimentation.

I would recommend reading my post in the Development::Development forum, also mentioned by trevius.

Basically, this isn't something we do, it's something the client does: the client counts them in the order it receives them, and uses the count as an ID. Since we don't ORDER BY when we send the AA info, that's why it ends up putting the newest AAs at the end of the pack.

All the query KLS mentions does is sets an arbitrary variable (idindex) in the database & adds 1 to it when it gets a result. This allows us to determine what the row "number" would be, which corresponds to the ID the client uses.

A query like this

Originally Posted by AndMetal

SET @row = 0;
SELECT 
	a.cost, 
	a.max_level, 
	a.hotkey_sid, 
	a.hotkey_sid2, 
	a.title_sid, 
	a.desc_sid, 
	a.type, 
	COALESCE(
		(
		SELECT 
			prereq_index_num 
		FROM 
			(
			SELECT 
				@row := @row + 1 AS prereq_index_num 
			FROM 
				altadv_vars
			) AS prereq_conv 
		WHERE 
			prereq_skill = prereq_index_num
		), 
		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 
;

should get us around that issue, which is part of my proposed solution listed in the other thread.

Originally Posted by ChaosSlayer

I must have been looking at the wrong table lol.

so if I change price for AA - will that actualy work?
Cuase I tried giving some AA (like Innate Agilty) more ranks but produced some weird errors/effects with client

There's actually several reasons why modifying Innate Agility wouldn't work.

Right now, aa_effects is only used to let the client know about additional effects, such as additional stats. It's like a spell, using an effect ID & base/base2 values. Unfortunately, it's only used client-side at this point. I've been working some on making it used by server-side calculations (see SVN Revision 79), but a lot of stuff has to be changed in the AA calculations throughout the rest of the source to not look at the AA value, and use values from aabonuses instead. Not only that, but I'm concerned that the current functions that I do have in the source may cause an issue with the database based on the amount of queries needed to pull the info from the database (1600+ each time we have to calculate stats).

Even if you didn't really mess with the aa_effects table, you could still calculate it server side. However, if you increase the max_level in altadv_vars, the ID it uses is then going to overwrite whatever is above it. Specifically, its skill_id is 12 with 1 point, 16 with 5 points. Make it 6 points, the ID becomes 17, which is being used by Innate Dexterity, so it wouldn't work.

The bottom line is you would need to create a new AA with a skill_id higher than any currently used (somewhere above 1627, which is Selo's Enduring Cadence). However, you would still need to update the dbstr_us.txt file for the Titanium client to display info about the AA.