azerothcore
Progression-System Rework
How the system currently works:
Each applied SQL gets saved to the database with a hash value. If the SQL file gets changed and thus the hash changes as well, the SQL file will get re-applied.
This often brings problems when updating files in the progression-system.
For example: We update a file in the 10-19 bracket which removes NPCs, but don't update the SQL which would respawn them in the 20-29 bracket. The server will now re-apply the first SQL file, deleting the NPCs again but not re-apply the other SQL to spawn them in again. This leads to all sorts of weird issues where you need to double check every SQL you change for possible regressions.
This also leads to some developers making new SQL files instead of updating old ones, which severely impact the readability of the whole module. For example: certain brackets now have 2 or more SQL files which spawn/despawn NPCs because its too much of a hassle to update both and deal with possible weird behaviours.
What we should do:
There are several ways which the system could be reworked. From internal talks the general opinion seems to be that the system should apply every SQL from 0-10 up to the currently selected bracket on every startup and get rid of the hash checks. This would impact startup times of the server slightly but since all SQL files SHOULD be re-applicable this would result in no more weird behaviours when updating.
If whoever gets to work on this has a different opinion about this though, or a different idea how the problems could be fixed feel free to propose those ideas first before working on a solution.
So don't update/change old, already applied SQL files and create the new ones . If you want this module to be automatic, there is no other way.
If the module decides that an old file is required to be applied from the existing logic, all files from that file's bracket PLUS all later brackets up to the current conf setting should be applied.
If the module decides that an old file is required to be applied from the existing logic, all files from that file's bracket PLUS all later brackets up to the current conf setting should be applied.
This could also work and potentially needs less work to implement.
I believe this association of what's later and what is earlier doesn't exist, it goes solely by the order the files are added and file names by alphabetic order
Earlier and later refers to the bracket id. Not the file creation.
Example: If I change a file in bracket 5, it currently re-applie only that one file. If I also apply all other files from bracket 5 and all files from bracket 6 through 10, the server will be at bracket 10.
In fact, I don't like the current module very much. I prefer the vmangos progressive system. Both create and item can have multiple lines of the same ID. finally, there is a version ID corresponding to different versions. You can call the corresponding create or item ID when the server is started by setting the version in worldserver config
Because of the nature of this module there are different complexities to take in consideration:
1. We should re-execute these queries every time because an AC update could alter some values of the database by removing the changes that are part of this module.
Example:
We have this query UPDATE `item_template` SET `RequiredLevel` = 40 WHERE `entry` = 50250; -- love rocket that set the required level of this item to 40. An AC update could set the RequiredLevel to something different (maybe to fix an issue with that value) and we will lost this change forever if we do not re-apply these files every time. Also keeping track of every AC updates is not practically possible, hence all the queries of this module should be re-executed to keep the data consistent.
2. These files should contain queries that can be re-executed 100%
Example:
UPDATE fieldA = fieldA + 2 FROM tableA WHERE guid = 1
This kind of UPDATE can't be reapplied because doesn't use constant values
3. It's also important that the old SQL are updated if AC changes the structure of the tables
This implies that the latest commit of this repo can be always only applied on the latest version of AC
Because of the points above, the only possibility is the following IMHO - We should always re-apply ALL the SQL every time we need (possibly on a clean database from time to time). It also means that all the queries here should be reviewed 1 by 1 to ensure they can be reapplied and are not dangerous
@Yehonal should not be hard to build some automated check in the CI which always applies all SQL queries of this module on latest AC, so we find any issues related to point 3. in advance.
Because of the nature of this module there are different complexities to take in consideration:
1. We should re-execute these queries every time because an AC update could alter some values of the database by removing the changes that are part of this module.
There's also another complexity I forgot to mention:
We have this QUERY that is executed at the phase 0:
UPDATE `creature` SET `phaseMask` = 2 WHERE `id1` IN (
33993, -- Emalon
35013, -- Koralon
38433); -- Toravon
and then we have this query that restores the original value during the phase 80
-- 80 level range - Tier 10 & Wrathful Gladiator
-- restore Toravon at Vaulth of Archavon
UPDATE `creature` SET `phaseMask` = 1 WHERE `id` = 38433;
The problem with the last query is that you do not know at that time if the phaseMask = 1 is the right value to set, because in the meantime on AC that value could have been changed to fix maybe a wrong phaseMask (just an example)
Hence the 'restore' queries are quite complex to maintain and we should instead find a way to retrieve the 'current' value from a clean AC database by maybe doing a SELECT + UPDATE from a clean copy of the AC one
@Yehonal
Because of the nature of this module there are different complexities to take in consideration:
1. We should re-execute these queries every time because an AC update could alter some values of the database by removing the changes that are part of this module.
There's also another complexity I forgot to mention:
We have this QUERY that is executed at the phase 0:
UPDATE `creature` SET `phaseMask` = 2 WHERE `id1` IN ( 33993, -- Emalon 35013, -- Koralon 38433); -- Toravonand then we have this query that restores the original value during the phase 80
-- 80 level range - Tier 10 & Wrathful Gladiator -- restore Toravon at Vaulth of Archavon UPDATE `creature` SET `phaseMask` = 1 WHERE `id` = 38433;The problem with the last query is that you do not know at that time if the
phaseMask= 1 is the right value to set, because in the meantime on AC that value could have been changed to fix maybe a wrong phaseMask (just an example)Hence the 'restore' queries are quite complex to maintain and we should instead find a way to retrieve the 'current' value from a clean AC database by maybe doing a SELECT + UPDATE from a clean copy of the AC one
@Yehonal we should then play with flags this way: https://www.azerothcore.org/wiki/sql-standards#flags-amp-bits
@Yehonal we should then play with flags this way: https://www.azerothcore.org/wiki/sql-standards#flags-amp-bits
Indeed, this can be a working alternative, although there are situations where this is not really possible (e.g. when you have to increase a non-bitmask value and then restore the original one in a later phase). Restoring should not be done with arbitrary values.
So you are saying mod-prog should run a secondary world database just for ac stock so it can get the proper values from that database instead when restoring ?
So you are saying mod-prog should run a secondary world database just for ac stock so it can get the proper values from that database instead when restoring ?
In the best scenario, yes.
For now, the highest priority is making sure that all the queries can be reapplied. The improvement to the restoring phase can be done in a second moment
So you are saying mod-prog should run a secondary world database just for ac stock so it can get the proper values from that database instead when restoring ?
In the best scenario, yes.
For now, the highest priority is making sure that all the queries can be reapplied. The improvement to the restoring phase can be done in a second moment
done https://github.com/azerothcore/mod-progression-system/pull/225
@Hacki95 can you please create and prioritise tasks for the above? e.g.
build some automated check the CI which always applies all SQL queries of this module on latest AC
For the SQL reapplying part, it can be easily achieved by preventing the updater from logging the module SQL files, that way, it will always think they're new files and will always execute them