Dealing with Spell files for Titanium clients

[jsr]

Adding this here as I couldn't find a list of redundant spells via google, so put this together. Hope someone finds it useful.

----------

Titanium only supports spell ID's up to 9999. This can be a pain in the you know what if you're trying to add new spells, especially if you're working from a newer client file (SOD has spells up to ~22k)

You can use this query to identify duplicated spells in the database which are candidates for deletion.

create table anotherdatabase.duplicate_spells
select subtable.identical_to, subtable.times_duplicated, spells_new.* FROM
spells_new INNER JOIN
(
select count(id) as times_duplicated,
min(id) as identical_to,
spells_new.`name`,
spells_new.buffduration,
spells_new.buffdurationformula,
spells_new.cast_time,
spells_new.recovery_time,
spells_new.mana,
spells_new.teleport_zone,
spells_new.effect_base_value1,
spells_new.effect_base_value2,
spells_new.effect_base_value3,
spells_new.effect_base_value4,
spells_new.effect_base_value5,
spells_new.effect_base_value6,
spells_new.effect_base_value7,
spells_new.effect_base_value8,
spells_new.effect_base_value9,
spells_new.effect_base_value10,
spells_new.effect_base_value11,
spells_new.effect_base_value12,
spells_new.effect_limit_value1,
spells_new.effect_limit_value2,
spells_new.effect_limit_value3,
spells_new.effect_limit_value4,
spells_new.effect_limit_value5,
spells_new.effect_limit_value6,
spells_new.effect_limit_value7,
spells_new.effect_limit_value8,
spells_new.effect_limit_value9,
spells_new.effect_limit_value10,
spells_new.effect_limit_value11,
spells_new.effect_limit_value12,
spells_new.max1,
spells_new.max2,
spells_new.max3,
spells_new.max4,
spells_new.max5,
spells_new.max6,
spells_new.max7,
spells_new.max8,
spells_new.max9,
spells_new.max10,
spells_new.max11,
spells_new.max12,
spells_new.formula1,
spells_new.formula2,
spells_new.formula3,
spells_new.formula4,
spells_new.formula5,
spells_new.formula7,
spells_new.formula6,
spells_new.formula8,
spells_new.formula9,
spells_new.formula10,
spells_new.formula11,
spells_new.formula12,
spells_new.effectid1,
spells_new.effectid2,
spells_new.effectid3,
spells_new.effectid4,
spells_new.effectid5,
spells_new.effectid6,
spells_new.effectid7,
spells_new.effectid8,
spells_new.effectid9,
spells_new.effectid10,
spells_new.effectid11,
spells_new.effectid12
FROM
spells_new
group by
spells_new.`name`,
spells_new.buffduration,
spells_new.buffdurationformula,
spells_new.cast_time,
spells_new.recovery_time,
spells_new.mana,
spells_new.teleport_zone,
spells_new.effect_base_value1,
spells_new.effect_base_value2,
spells_new.effect_base_value3,
spells_new.effect_base_value4,
spells_new.effect_base_value5,
spells_new.effect_base_value6,
spells_new.effect_base_value7,
spells_new.effect_base_value8,
spells_new.effect_base_value9,
spells_new.effect_base_value10,
spells_new.effect_base_value11,
spells_new.effect_base_value12,
spells_new.effect_limit_value1,
spells_new.effect_limit_value2,
spells_new.effect_limit_value3,
spells_new.effect_limit_value4,
spells_new.effect_limit_value5,
spells_new.effect_limit_value6,
spells_new.effect_limit_value7,
spells_new.effect_limit_value8,
spells_new.effect_limit_value9,
spells_new.effect_limit_value10,
spells_new.effect_limit_value11,
spells_new.effect_limit_value12,
spells_new.max1,
spells_new.max2,
spells_new.max3,
spells_new.max4,
spells_new.max5,
spells_new.max6,
spells_new.max7,
spells_new.max8,
spells_new.max9,
spells_new.max10,
spells_new.max11,
spells_new.max12,
spells_new.formula1,
spells_new.formula2,
spells_new.formula3,
spells_new.formula4,
spells_new.formula5,
spells_new.formula7,
spells_new.formula6,
spells_new.formula8,
spells_new.formula9,
spells_new.formula10,
spells_new.formula11,
spells_new.formula12,
spells_new.effectid1,
spells_new.effectid2,
spells_new.effectid3,
spells_new.effectid4,
spells_new.effectid5,
spells_new.effectid6,
spells_new.effectid7,
spells_new.effectid8,
spells_new.effectid9,
spells_new.effectid10,
spells_new.effectid11,
spells_new.effectid12
) as subtable
ON
spells_new.`name` = subtable.`name`
where times_duplicated > 1
and identical_to <> id
and spells_new.id < 10000
order by times_duplicated desc, identical_to, spells_new.name

The first line creates a new table (in this case it's a different database for data that isn't being used by a live server). The rest of the query identifies the duplicates and puts them into the new table, along with some extra info
- times duplicated = how many times the spell appears to be duplicated
- identical to = the ID for the first instance of the spell

A spell is considered a duplicate by this query if;
- Names match
- Effect types, values, maximums, and formulas match
- Cast times, Recovery times, and mana costs match
- Teleport zone matches (summoned horses use this field to determine type)
- You can add other fields as you see fit.

There are over 3000 duplicates in the original SOD spell file, some are just placeholders but many are just simple duplicates.

Once you identify a likely spell for deletion, cross reference the spell's ID against the items table (clickeffect, worneffect, focuseffect, and proceffect) and the npc_spells_entries table (spellid) to see if it is in use by an item or NPC. If it is not you can safely delete or modify the duplicate spell.

If the spell is in use you have the option to repoint the NPC/Item to the first instance of the spell. After which you can safely delete or modify the duplicate.

You can run delete queries with a join on the table you've created, and a join on the items/npc_spells_entries table.

Note that the original query can return some false positives, so it's essential to check items and NPC tables before deciding a spell can be deleted or modified. If you want to avoid any false positives, include every field except ID in both the select and group by statements (but expect false negatives).

**Always backup your table before running batch queries.**

-Jsr