Spells usable by all classes

alberto667 · #1 04-29-2016, 04:25 PM

Hello everyone,

I am looking for tips or hints to mod the 'spells_new' entries so any class could use any spell.
So far i found this query that works, it changes lvl255 restriction to the lvl i want by columns (lvl50 for mage spells for example here) :

update spells_new set classes(13)=50 where classes(13)>253;

..Now i would like to go farther and for each spell ID to check the minimum lvl rec from classes1 to classes16 colums and apply that level to the rest of the classes.

Not sure if it's clear, english is not my native tongue.

Thanks for your help ladies and gentlemen

Zaela_S · #2 04-29-2016, 07:32 PM

The way the table is set up make this a bit of a pain...

Code:

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
	spell_id 	INT,
	level		INT
);

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;
CREATE TEMPORARY TABLE temp_spells_classes_min (
	spell_id 	INT PRIMARY KEY,
	level		INT
);

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes1 FROM spells_new WHERE classes1 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes2 FROM spells_new WHERE classes2 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes3 FROM spells_new WHERE classes3 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes4 FROM spells_new WHERE classes4 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes5 FROM spells_new WHERE classes5 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes6 FROM spells_new WHERE classes6 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes7 FROM spells_new WHERE classes7 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes8 FROM spells_new WHERE classes8 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes9 FROM spells_new WHERE classes9 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes10 FROM spells_new WHERE classes10 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes11 FROM spells_new WHERE classes11 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes12 FROM spells_new WHERE classes12 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes13 FROM spells_new WHERE classes13 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes14 FROM spells_new WHERE classes14 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes15 FROM spells_new WHERE classes15 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes16 FROM spells_new WHERE classes16 < 253;

INSERT INTO temp_spells_classes_min (spell_id, level)
SELECT spell_id, MIN(level)
FROM temp_spells_classes
GROUP BY spell_id;

UPDATE spells_new s
JOIN temp_spells_classes_min t ON s.id = t.spell_id
SET
	classes1 = t.level,
	classes2 = t.level,
	classes3 = t.level,
	classes4 = t.level,
	classes5 = t.level,
	classes6 = t.level,
	classes7 = t.level,
	classes8 = t.level,
	classes9 = t.level,
	classes10 = t.level,
	classes11 = t.level,
	classes12 = t.level,
	classes13 = t.level,
	classes14 = t.level,
	classes15 = t.level,
	classes16 = t.level;
	
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;

Should put the above into a script (e.g. "example.sql") and run it with SOURCE (e.g. SOURCE "path/to/example.sql").

Back up spells_new first!!

alberto667 · #3 04-30-2016, 11:41 AM

Thanks so much Zaela_S, that's exactly what I was looking for .. It works perfectly.

I was trying to figure it out by myself but I am still in the process of learning the more complex SQL functions, and as you said the way the table is set up is a pita...I now understand that the use of temp table was the key here...

Thanks again for your help

alberto667 · #4 05-02-2016, 03:21 PM

After testing some more,I found that bard's songs could cause bugs ("you missed a note" instead of "your spell fizzle"), not sure for tanks tomes but better remove it too ..
Also when you put 65535 as item.class for spells scroll it shows ingame on it with all classes and a bunch of 255 for those that can't use it.

The code would then be for mage for instance ( Run this before Zaela_S's code) :

Code:

update items, spells_new set classes=classes+4096 WHERE
items.scrolleffect = spells_new.id AND
items.classes <> 4096 AND
spells_new.classes13 = 255 AND
items.`Name` LIKE '%spell:%'

Change the classe number from spells_new and the classe number for items from this chart to suit your needs :

Classes(number) in spells_new :
2 Cleric
3 Paladin
4 Ranger
5 Shadowknight
6 Druid
10 Shaman
11 Necromancer
12 Wizard
13 Magician
14 Enchanter
15 Beastlord

Classes in items :
1 Warrior
2 Cleric
4 Paladin
8 Ranger
16 Shadowknight
32 Druid
64 Monk
128 Bard
256 Rogue
512 Shaman
1024 Necromancer
2048 Wizard
4096 Magician
8192 Enchanter
16384 Beastlord

Then you can mod the classes in spells_new

Code:

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
	spell_id 	INT,
	level		INT
);

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;
CREATE TEMPORARY TABLE temp_spells_classes_min (
	spell_id 	INT PRIMARY KEY,
	level		INT
);

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes2 FROM spells_new WHERE classes2 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes3 FROM spells_new WHERE classes3 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes4 FROM spells_new WHERE classes4 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes5 FROM spells_new WHERE classes5 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes6 FROM spells_new WHERE classes6 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes10 FROM spells_new WHERE classes10 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes11 FROM spells_new WHERE classes11 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes12 FROM spells_new WHERE classes12 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes13 FROM spells_new WHERE classes13 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes14 FROM spells_new WHERE classes14 < 253;

INSERT INTO temp_spells_classes (spell_id, level)
SELECT id, classes15 FROM spells_new WHERE classes15 < 253;

INSERT INTO temp_spells_classes_min (spell_id, level)
SELECT spell_id, MIN(level)
FROM temp_spells_classes
GROUP BY spell_id;

UPDATE spells_new s
JOIN temp_spells_classes_min t ON s.id = t.spell_id
SET
	classes2 = t.level,
	classes3 = t.level,
	classes4 = t.level,
	classes5 = t.level,
	classes6 = t.level,
	classes10 = t.level,
	classes11 = t.level,
	classes12 = t.level,
	classes13 = t.level,
	classes14 = t.level,
	classes15 = t.level;

	
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_min;

Thanks again for the insight Zaela_S, and I hope it will help people like it helped me

**Uleat** · #5 05-02-2016, 04:16 PM

Quote:

After testing some more,I found that bard's songs could cause bugs ("you missed a note" instead of "your spell fizzle"), not sure for tanks tomes but better remove it too ..

Not exactly sure what you mean by this statement.

Quote:

Also when you put 65535 as item.class for spells scroll it shows ingame on it with all classes and a bunch of 255 for those that can't use it.

`item`.`classes` is a bitmask of the allowed classes and not an actual class value.

By changing `item`.`class` .. you are altering what classes can 'access' the item..but, not what classes can use the spell. This would explain the '255'
values you are seeing and is not unexpected.

To properly create a bitmask (in this case,) you would take the class id and subtract 1 (since it is a 1-based index,) then left bit-shift 1 by that
amount. Finally, you would bitwise-or each value to attain the final result.

Code:

int final_bitmask = 0;
int warrior_id = 1, warrior_bit = 0;
int paladin_id = 3, paladin_bit = 0;
int shadowknight_id = 5, shadowknight_bit = 0;

warrior_bit = (1 << (warrior_id - 1)); // 1 << 0 = 1
paladin_bit = (1 << (paladin_id - 1)); // 1 << 2 = 4
shadowknight_bit = (1 << (shadowknight_bit - 1)); // 1 << 4 = 16

final_bitmask = (warrior_bit | paladin_bit | shadowknight_bit); // 1 | 4 | 16 = 21

[(final_bitmask == 21) = true]

Code:

update items, spells_new set classes=classes+4096 WHERE
items.scrolleffect = spells_new.id AND
items.classes <> 4096 AND
spells_new.classes13 = 255 AND
items.`Name` LIKE '%spell:%'

Scenario:

`item`.`classes` = 4097 (warrior and magician)

[update triggered because `classes` > 4096]
4097 + 4096 = 8193 (warrior and enchanter)

Also, by setting the item to be usable by class '4096' where `classes13` = 255, you only allow them access to the item, not the spell.

You would need to change `classes13`to a usable level for them to be able to use the actual spell and not just the item.

alberto667 · #6 05-03-2016, 02:15 PM

Sorry it was not very clear, read a foreign language is one thing, writing is another

To sum up, when i tested the code Zaela_S shared, it was what I was looking for ( a way to make spells available to other classes at the appropriate level )
But once in game I had a couple issues like 'you missed a note' as fizzle message when casting spells instead of 'your spell fizzles' ( I thing i read somewhere bard songs could cause that bug ), as well as pet's focus effects not working anymore, so i removed all non caster classes from Zaela's code.
Then it was perfect.

When it came time to change scrolls in game to be writable by all classes, I just put 65535 in the item table for spell scrolls. It worked but you could see all the classes in the description of the scroll with those who cant use it, like war(255),rog(255) etc..

For cosmetic purposes I went with that (bruteforce) method to add the class bitmask, 4096 for mage for example, excluding the spells mages already known and also excluding the spells mages shared with other classes ( like invisibility ) because when I added the bitmask the way i did it messed up those spells.

That solution worked for me, hence the excitement to share it ( I bet you guys write miles of lines of code in a finger snape

), but I couldn't figure out how the class bitmask worked .. Thanks for the help on that Uleat !

Nydosa · #7 04-14-2018, 02:03 AM

Sorry to bump an old thread... Does anyone have an idea if a sql query could be written to update the scroll/"Spell:" in 'items' by simply reading the data out of 'spells_new'?

I'm having trouble with reading from one table into the other. It seems like the code in this thread was close to doing that...

I'm assuming you'd have to build a sum under the logic of items.classes = (if spells_new.classes11 < 255, add 1024) #necromancer all the way through each class?

Any help would be appreciated!

Thank you

Nydosa · #8 04-14-2018, 05:50 PM

Almost there! Looking for some coding experts...

I have the following code based on Zaela's above... this reads from spells_new table and creates the correct classes bitmask is the items table for all spell scrolls.

I confirmed the temp table operates properly, the problem is in updating the items table via JOIN. This code I have below causes HeidiSQL to hang and then ultimately crash SQL. Am I missing something that is causing this?

Code:

UPDATE items s
JOIN temp_spells_classes_sum t ON s.scrolleffect = t.spell_id
SET	classes = t.classes_id
WHERE s.scrolltype = '7'
;

The entire script

Code:

DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
CREATE TEMPORARY TABLE temp_spells_classes (
	spell_id 	INT,
	level1		INT DEFAULT '0',
	level2		INT DEFAULT '0',
	level3		INT DEFAULT '0',
	level4		INT DEFAULT '0',
	level5		INT DEFAULT '0',
	level6		INT DEFAULT '0',
	level7		INT DEFAULT '0',
	level8		INT DEFAULT '0',
	level9		INT DEFAULT '0',
	level10		INT DEFAULT '0',
	level11		INT DEFAULT '0',
	level12		INT DEFAULT '0',
	level13		INT DEFAULT '0',
	level14		INT DEFAULT '0',
	level15		INT DEFAULT '0',
	level16		INT DEFAULT '0'
);


DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_sum;
CREATE TEMPORARY TABLE temp_spells_classes_sum (
     spell_id MEDIUMINT NOT NULL,
     classes_id MEDIUMINT NOT NULL
);

INSERT INTO temp_spells_classes (spell_id, level1)
SELECT id, classes1/classes1*1 FROM spells_new WHERE classes1 < 253;

INSERT INTO temp_spells_classes (spell_id, level2)
SELECT id, classes2/classes2*2 FROM spells_new WHERE classes2 < 253;

INSERT INTO temp_spells_classes (spell_id, level3)
SELECT id, classes3/classes3*4 FROM spells_new WHERE classes3 < 253;

INSERT INTO temp_spells_classes (spell_id, level4)
SELECT id, classes4/classes4*8 FROM spells_new WHERE classes4 < 253;

INSERT INTO temp_spells_classes (spell_id, level5)
SELECT id, classes5/classes5*16 FROM spells_new WHERE classes5 < 253;

INSERT INTO temp_spells_classes (spell_id, level6)
SELECT id, classes6/classes6*32 FROM spells_new WHERE classes6 < 253;

INSERT INTO temp_spells_classes (spell_id, level7)
SELECT id, classes7/classes7*64 FROM spells_new WHERE classes7 < 253;

INSERT INTO temp_spells_classes (spell_id, level8)
SELECT id, classes8/classes8*128 FROM spells_new WHERE classes8 < 253;

INSERT INTO temp_spells_classes (spell_id, level9)
SELECT id, classes9/classes9*256 FROM spells_new WHERE classes9 < 253;

INSERT INTO temp_spells_classes (spell_id, level10)
SELECT id, classes10/classes10*512 FROM spells_new WHERE classes10 < 253;

INSERT INTO temp_spells_classes (spell_id, level11)
SELECT id, classes11/classes11*1024 FROM spells_new WHERE classes11 < 253;

INSERT INTO temp_spells_classes (spell_id, level12)
SELECT id, classes12/classes12*2048 FROM spells_new WHERE classes12 < 253;

INSERT INTO temp_spells_classes (spell_id, level13)
SELECT id, classes13/classes13*4096 FROM spells_new WHERE classes13 < 253;

INSERT INTO temp_spells_classes (spell_id, level14)
SELECT id, classes14/classes14*8192 FROM spells_new WHERE classes14 < 253;

INSERT INTO temp_spells_classes (spell_id, level15)
SELECT id, classes15/classes15*16384 FROM spells_new WHERE classes15 < 253;

INSERT INTO temp_spells_classes (spell_id, level16)
SELECT id, classes16/classes16*32768 FROM spells_new WHERE classes16 < 253;

INSERT INTO temp_spells_classes_sum (spell_id, classes_id)
SELECT spell_id, SUM(level1+level2+level3+level4+level5+level6+level7+level8+level9+level10+level11+level12+level13+level14+level15+level16)
FROM temp_spells_classes
GROUP BY spell_id;

UPDATE items s
JOIN temp_spells_classes_sum t ON s.scrolleffect = t.spell_id
SET	classes = t.classes_id
WHERE s.scrolltype = '7'
;
	
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes;
DROP TEMPORARY TABLE IF EXISTS temp_spells_classes_sum;