Database Schema Migrations¶
Adding New Required Schema Migrations¶
- It's extremely simple
- When you add your .sql file, you still will add it as traditionally to our regular path
utils/sql/git/required
- Github Link
Changes in the Source¶
- You need to increment a define in the source that dictates what database version the source SHOULD be running at
Location common/version.h
The database version will need to match the manifest entry you have added, more on that in a moment
CURRENT_BINARY_DATABASE_VERSION = 9058
The Manifest¶
- The manifest is stored always on Github and contains all the definitions and logic for determining if a database needs to update
utils/sql/db_update_manifest.txt
- Manifest Link
Add a line to the bottom of the file, it is going to look similar to the following
9058|2014_11_17_example_update_file.sql|SHOW TABLES LIKE 'character_mercenaries'|empty|
- This example would then have users run
2014_11_17_example_update_file.sql
when they don't have the 'character_mercenaries' table because of the empty condition - That's it! As far as what is needed from a developer to have the server run the migration, that is all you need to do
Manifest Conditions¶
Example: Version|Filename.sql|Query_to_Check_Condition_For_Needed_Update|match type|text to match
0 = Database Version
1 = Filename.sql
2 = Query_to_Check_Condition_For_Needed_Update
3 = Match Type - If condition from match type to Value 4 is true, update will flag for needing to be ran
contains = If query results contains text from 4th value
match = If query results matches text from 4th value
missing = If query result is missing text from 4th value
empty = If the query results in no results
not_empty = If the query is not empty
4 = Text to match
Other Manifest Examples¶
Missing¶
9056|2014_11_08_RaidMembers.sql|SHOW COLUMNS FROM `raid_members` LIKE 'groupid'|missing|unsigned
This entry is looking for what the column looks like in raid_members and to see if it is an unsigned integer
The match type is missing
, so I'm looking to see if the word 'unsigned' is missing from the table. In this case if unsigned was missing, we need to run this update because that is what the update did
If Missing If the table is missing the column, it will run the SQL file specified above 2014_11_08_RaidMembers.sql
ALTER TABLE `raid_members` CHANGE COLUMN `groupid` `groupid` INT(4) UNSIGNED NOT NULL DEFAULT '0' AFTER `charid`;
Contains¶
9055|2014_10_30_special_abilities_null.sql|SHOW COLUMNS FROM `npc_types` LIKE 'special_abilities'|contains|NO
This entry is looking for what the column looks like in npc_types, column 'special_abilities' to see if it contains the word 'NO' (If special_abilities is nullable), which if you look at the SQL result at the given time before this update is applied
You will see the data about column regarding NULL, defined as NO. Which means the field can't be null, we want it to be able to be null because it causes issues with later MySQL versions, which is why this update was made
Given the condition is true, the following runs
ALTER TABLE `merc_stats` MODIFY COLUMN `special_abilities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `npc_types` MODIFY COLUMN `special_abilities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;