[SPELLS] why they must be stored and maintained in database (almost all of them)

[Yehonal]

I have been reading many discussions about DBCs and spells with a lot of misunderstanding here and there.

So I would like to open this topic by trying to collect all the information we gained so far about the spell mechanism

1. DBC Is not a single source of truth: Blizzard has most of the spell mechanics written inside its own database, a small portion of them is extracted and converted to DBC to act as a way to instruct the client how to behave when a spell is cast (to avoid wasting network bandwidth with information that can be stored in the client instead, improving the latency as well).
   They extracted DBC files once per each client patch but, between a patch and another, the server-side spell database is possibly updated/extended with different values that are not needed by the client. Moreover, during the DBC extraction, there could be an alteration of the original values to fit the needs of the client (but not the ones of the server). Raw DBC files were initially used as a single source of truth by emulators to speed up the process of following the client progression, but using it as an immutable database for the server is totally wrong and that's why we need a table to override it (and coding them in some cases).

2. Blizzard uses spells for almost everything: Checking the sniffs and the logs from the Classic you can notice that almost all the "effect" of a script are handled by a spell (when you have to set a unit flag, reducing the life, fake death status and even selecting creatures around etc.). Back in time we used to place almost all of this logic inside the code because we had very few information about the spell_dbc structure and any tables to operate with. But this is definitely an antipattern. The only evidence of scripted spells is the ones with the "SPELL_AURA_DUMMY" attribute. But even in that case, we don't know if they are scripted with a database SAI-like system or by using pure code.

This topic could have some lacks of information, so feel free to contribute here with your comments. However, I hope that it can contribute to avoiding continuing to hardcode spells IDs within the core.

Moreover, you can extend this logic to basically everything: the code is not the place where you have to explicitly define such information (spells, areas, items etc.).

Thanks to this system implemented into AzewrothCore based on this idea, now you are not bound anymore to DBC files and we can move this hardcoded stuff to the database.

UPDATE:

Few resources to explore the spell_dbc

• ACore dbc reader: https://github.com/wowgaming/node-dbc-reader
• DBC SQL version compatible with our structure: https://github.com/wowgaming/client-data/releases/tag/dbc_sql_v1
• Viewers and editors: https://wowgaming.github.io/wiki-en/client-dev-tools.html
• Spell DBC documentation: https://wowdev.wiki/DB/Spell#3.3.5.12340

[Kitzunu]

What we need is someone to clarify how spell_dbc works, because atm there is no way I am going to tinker with 230+ columns to try and get a spell to work when I can more easily do it as a spellscript.

(Which is also why I have disabled the feature on SpellWork, because I am not going to update, from TC's 96 columns in spell_dbc, to our 230+ with different names)

[T1ti]

An issue with spell_dbc table in the DB is that it's specific to each emulator, they all have different structure and names.
While modifying the DBC will work with any emulator

This makes it harder to develop tools such as Spell Editor to use databases, because you would need to make one version of it for each emulator(this is the reason stoneharry hasn't implemented DB features to his tool)

A common effort between the emulators to standardize their column names to make it easier to develop tools would be required

[T1ti]

By the way WDBX should be avoided for spell.dbc usage, I found this issue while using it
WowDevTools/WDBXEditor#103

[Yehonal]

I think that if Keira3 implemented a page to view and edit the spell_dbc table we wouldn't need any external tool to work with a spell in our DB. Keira3 could just read the pre-filled spell_dbc table, thus you can generate any kind of query you need to change/fix/add your spell.

@FrancescoBorzi , @Helias do you think is it something that can be added in the keira3 roadmap?

[Footman]

what we need is new web tool to work with this. like you said to extend keira3 would be good approach.

[FrancescoBorzi]

yes, that's a good addition for Keira3. I will work on it, but my time is limited and I have plenty of other tasks to work on, so don't expect it too quickly.

[Yehonal]

@Kitzunu , @Stifler82 , @ErnestoGonzalezC

I've created a tool to extract SQL from DBCs. It allows you to export only the rows you need. It's maintained by us so we can properly set the schema needed for the AzerothCore database structure.

Check it out please: https://github.com/wowgaming/node-dbc-reader

Ideally, when we create a PR, we want to:

1. extract the query using that tool and set that query on top of our PR sql
2. creating the UPDATE query manually

Does it make sense for you?

[ernesto-glz]

I will be testing it during these day 👍