If you are not confident with the SQL language, we suggest to read this tutorial before starting.
Also remember to:
always use UPDATE in order to change the value of fields of existing rows
whenever possible, try to make your query re-executable (the same query can run twice without error) e.g. deleting before inserting
surround any table or field name with
backticks, and string values with
single quotes, example:
UPDATE `table_name` SET `field_I_want_to_change` = 'new string value' WHERE `entry` = 10 ;
Compact code helps to keep the size of our SQL update files small, so installing/updating the ACDB will be faster.
INSERT INTO `table_1` VALUES (1000, ...); INSERT INTO `table_1` VALUES (2000, ...); INSERT INTO `table_1` VALUES (3000, ...);
INSERT INTO `table_1` VALUES (1000, ...), (2000, ...), (3000, ...);
DELETE FROM `table_1` WHERE `entry` = 1000; DELETE FROM `table_1` WHERE `entry` = 2000; DELETE FROM `table_1` WHERE `entry` = 3000;
DELETE FROM `table_1` WHERE `entry` IN (1000, 2000, 3000);
UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 1000; UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 2000; UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` = 3000;
UPDATE `table_1` SET `field_1` = 'someValue' WHERE `entry` IN (1000, 2000, 3000);
SQL variables help to make IDs/GUIDs more manageable, use them when needed:
SET @FREE_GUID:=145211; DELETE FROM `creature` WHERE `guid` BETWEEN @FREE_GUID AND @FREE_GUID+5; INSERT INTO `creature` VALUES (@FREE_GUID+0, 1420, 530, 6785.898, -7607.692, 128.1121, 3.815103, 120, 0), (@FREE_GUID+1, 1420, 530, 6753.482, -7647.198, 128.3187, 3.793595, 120, 0), (@FREE_GUID+2, 2914, 530, 6830.517, -7396.761, 46.36444, 2.204267, 120, 0), (@FREE_GUID+3, 2914, 530, 6967.708, -7464.932, 47.05861, 1.433785, 120, 0), (@FREE_GUID+4, 2914, 530, 6764.093, -7363.276, 50.46708, 2.048597, 120, 0), (@FREE_GUID+5, 2914, 530, 6703.647, -7402.308, 51.60884, 5.743487, 120, 0);
For flags (2^) columns, when you remove or add a flag, it is better not to override the existing value as flags are combined values.
For example, given a flag with value
128, this is how it would be to add, remove and invert it:
-- ADD AN EXTRA FLAG (|) UPDATE `table_1` SET `field_1` = `field_1` | 128 WHERE `entry` = 1000; -- REMOVE AN EXTRA FLAG (& ~) UPDATE `table_1` SET `field_1` = `field_1` & ~128 WHERE `entry` = 1000; -- INVERT A FLAG (if present = removed, if absent = added) (^) UPDATE `table_1` SET `field_1` = `field_1` ^ 128 WHERE `entry` = 1000;
This way, you will make sure that your query will only affect that specific flag, leaving all the other flags unchanged.
This kind of procedure is pretty simple and allow not only developers, but also testers, to avoid multiple import of the same queries.
Always make sure you are up to date with the main repository by quickly doing this beforehand: Update and sync your fork.
Then you need to create a new branch which will hold your SQL Update file:
>## Pick our main branch so other branches can be ## up to date when being created based on master. git checkout master ## Create a new branch and checkout to start working on it git checkout -b this_will_fix_that_problem
Now that we are ready, generate the update file by:
Inside your local repository, you should have a folder in the path data/sql/updates. There we will find many pending folders:
The one you pick depends on the Databases that needs correction. For example, our query will update
creature.npcflags and, for this reason, we will create an update SQL file inside pending_db_world due to the table belonging to the world database.
>UPDATE `creature_template` SET `npc_flag` = 128 WHERE `entry`= 1234;
git bash here (right click on the folder) to open up the git console and execute the shell script by typing ./create_sql.sh.
Don't close this console yet so we can use it to commit and push to our remote later on.
On Unix/Linux/OSX: run it from the terminal directly or with "bash create_sql.sh" or execute it with a double click.
You can open it and add/type your queries into it.
On Windows: Remember when we said not to close the terminal on windows? Now you only need to type the following commands into the terminal and you will be ready to open a pull request with your fix.
>## Make sure you have your branch with the new fix checked out. ## Select all your modified files git add . ## Commit your changes (you can simply type "git commit -v" too) git commit -v -m "Commit message here" ## origin = your git remote (the url of your fork) ## Don't need to type git push origin this_will_fix_that_problem ## Because origin is our default remote and the branch will be the current one git push
This feature grants you ( dev / tester / user ) to: