SQL Upgrade Tracking

After building version 0.1 of your project, your database structure might undergo a lot of changes. Once you have finalized your initial database structure, produce a "create" sql script, and place it into the 'doc' folder of your project, calling it 'myproject-01.sql'. Of course you should replace "myproject" with the name of your project.

Exclude any non-essential or sensitive data from your database creation script.

When you need to change your database structure, create a new sql script containing the required 'ALTER' statements. Name this script 'myproject-01-001.sql', and place it in a sub-folder of the 'doc' folder, named 'dbupdates'. ALTER statement.

Copy or create a symlink (or 'alias') to the "update.sh" shell script in "atk4/tools/update.sh" into the 'doc' folder. In Linux, you can do this by executing:

ln -s ../atk4/tools/update.sh .
This shell script looks for database update scripts in the "dbupdates" directory, and applies them to the database.

To apply changes to your database execute

./update.sh
from within the "doc" directory. By default, the script will look for a ~/.my.cnf file which normally contains your default mysql connection info. If that file is not present, the script will read the database username/password from your "config.php" file.

If you need to make changes to the update.sh script, copy it to the 'doc' folder, then adjust your copy.

Using MySQL Workbench

MySQL Workbench is a free and extremely powerful software for (but not limited to) designing your SQL schema. We highly recommend you use it. In your normal workflow with MySQL Workbench, you will make changes to your model, save them, then choose "Database -> Synchronize Model" from the menu. You will then click "continue" through all the steps, until you see "Preview changes to be applied".

Here you will copy-paste the generated code into a newly created dbupdates/.. sql script file. You will need to manually change references to your database, such as:

ALTER TABLE `colubris`.`task` DROP FOREIGN KEY `fk_task_screen1` ;
        
to:
ALTER TABLE `task` DROP FOREIGN KEY `fk_task_screen1` ;
        
since the database will be automatically selected by "update.sh". At this point, save the update script file and cancel MySQL Workbench's synchronize dialog. Execute "update.sh" manually. If you have already made changes to the database, you should still add them into dbupdates, but create also an empty file dbupdates/myproject-01-001.sql.ok, which will flag to the update.sh script that this specific database update has already been successfully applied.

Use doc/myproject.mwb as your mysql workbench filesave. If you are using a different modelling application, be sure to specify how to properly change your database schema from Windows, Linux and Mac computers.