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
Add a line to the bottom of the file, it is going to look similar to the following
1
9058|2014_11_17_example_update_file.sql|SHOW TABLES LIKE 'character_mercenaries'|empty|
Copied!
  • 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

1
Example: Version|Filename.sql|Query_to_Check_Condition_For_Needed_Update|match type|text to match
2
0 = Database Version
3
1 = Filename.sql
4
2 = Query_to_Check_Condition_For_Needed_Update
5
3 = Match Type - If condition from match type to Value 4 is true, update will flag for needing to be ran
6
contains = If query results contains text from 4th value
7
match = If query results matches text from 4th value
8
missing = If query result is missing text from 4th value
9
empty = If the query results in no results
10
not_empty = If the query is not empty
11
4 = Text to match
Copied!

Other Manifest Examples

Missing

1
9056|2014_11_08_RaidMembers.sql|SHOW COLUMNS FROM `raid_members` LIKE 'groupid'|missing|unsigned
Copied!
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
1
ALTER TABLE `raid_members` CHANGE COLUMN `groupid` `groupid` INT(4) UNSIGNED NOT NULL DEFAULT '0' AFTER `charid`;
Copied!

Contains

1
9055|2014_10_30_special_abilities_null.sql|SHOW COLUMNS FROM `npc_types` LIKE 'special_abilities'|contains|NO
Copied!
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
1
ALTER TABLE `merc_stats` MODIFY COLUMN `special_abilities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
2
ALTER TABLE `npc_types` MODIFY COLUMN `special_abilities` text CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
Copied!
Last modified 2yr ago