RCOM Upgrades module

Easilly manage and release SQL changes (data and schema) to your production, staging, qa and dev environments. Currently supports MySQL and MariaDB.

• Use cases
• Installation
• File and folder structures
• CLI commands
• Suggested usage

Use cases

If you understand what is the purpose of this tool, Feel free to jump ahead to the Installation and Running section

Schema Upgrades

Over the life time of your product you should expect the schema of your relational DB to grow and change. You can manage this manually, but usually schema changes go hand-in-hand with code changes. It is imperative the release of the changes to qa/staging/production environment will be synced. The Upgrades system gives you that ability with a controlled way to run your schema upgrade scripts and a way to track which script ran, and at what order it ran.

Releasing of db schema changes with auto QA

It would be nice to know that the schema scripts you are about to run on a db are safe, i.e. they wont fail in the middle, and leave your system in shambles and without a simple way to role back changes. Remeber, schema changes are not part of the transaction engine relational DBs have. The Upgrades system gives a way to do a dry run of the scripts, stop the entire release process in case of an issue and give a proper report as to the nature of the issue.

Data changes

There will be many cases, when you will need to fix or add data to your system directly via a SQL statement. The Upgrades system gives you an easy way to do just that, track what was executed, when was executed and prevent multiple executing of the same script by accident. Upgrades can also manage the execution order of the data-fixes scripts.

Installation

The utility table, rcom_sql_upgrades

Upgrades needs one table in your database to be able to track which script was executed and which didn’t. The table can be created anyware, just remember to put the dbname in the config file (see section below).
The table create is:

CREATE TABLE [db name of your choosing].rcom_sql_upgrades 
( 
file_name varchar(255) CHARACTER SET utf8 NOT NULL, 
execute_order INT(11) UNSIGNED NOT NULL DEFAULT 1 COMMENT 'if file is prefixed with a number it goes here and determines execution order of sqls', 
time_runned timestamp NULL DEFAULT NULL, 
execution_status enum('pending_completion','failed','failed_in_test','completed','completed_in_test') CHARACTER SET utf8 NOT NULL DEFAULT 'pending_completion', 
error_message varchar(255) CHARACTER SET utf8 DEFAULT NULL, 
PRIMARY KEY (file_name) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

NOTICE! there is a PK on the filename. Make sure each file has a unique name. The way I prefer to do it is to prefix each file name with a number_ i.e. 01_script_toru.sql, 02_another_Scrip.sql , You can also run several files in the order you decide. More on this later on, in the receipes.

Configuration

In the configuration file, you can specify to the system where is the table that tracks the upgrades done, if and where there is a test database (a database you can run the scrips on just before running on prod, making sure they won’t crash).
The file is under the config folder and is called upgrade.py.
The file is pretty much self explanatory, the default values are:

upgrade = {   'upgrade_tracking_database': 'dhara',                 \
              'test_host':                 'localhost',             \
              'test_user':                 'root',                  \
              'test_password':             '',                      \
              'force_test':                False,                    \
              'force_schema_test':         False}

• upgrade_tracking_database database name Where the table that tracks which upgrade ran is located.
• test_* connection details to the server the upgrades script will run first. Make sure this server has the exact same schema (preferably also data) as your production system.
• force_test Each upgrade run will be forced to start with running it in test mode. Will stop the process if failed.
• force_schema_test Run the schema checker on the the test database after you ran the upgrades. Will stop the process if failed.

Steps to install

1. Select or create a database in your production environment where you will host the upgrade tracking table.
2. Create the tracking table in that database. See CREATE above.
3. Enter the database name in the config file 'upgrade_tracking_database': 'YOUR SELECTED DATABASE NAME',
4. If you have a test server, put it’s credential too in the config
5. If you want to run the schema checker after each upgrade, mark
   'force_schema_test': True
6. If you want to force to run on the test server each time before you do an upgrade, mark 'force_test':True NOTICE! you can also run the tests by specifying it in the command line. That way you can control when it runs

File and Folder structure

Folder structure

After you install Rahl Commander, you need to verify that your assets folder has a folder named upgrades and underneath it, two more folders: current and archive.

assets_____             _ archive
            |          |   
            |_upgrades_
            |          |_ current
            |
            |_...

NOTICE! When you copied the example_assets folder you also created those folders.

File structure

Filename should start with a number and right after it _.
This is how RCOM orders script execution. Example: 10_fix_city_name.sql
The content of the file must be valid SQL.
NOTICE!
In your SQL scripts, do not forget to add the database name in the right place, either as databasename.tablename
or with the use statement.

update db2.table_two
set uname = 'lilo'
where pass='multi';

use database1;
update t1
set password=1;

CLI commands

upgrade.py

This module has only one command attached to it. upgrade.py.
This command will take all the files, which have not been ran yet, in the current folder. order them lexically and run them by that order.
The command has a few switch to archive or unmark failed tickets. See below.

-v -vv -vvv -vvvv

The verbosity level of the output

– – all

Upgrade all files found, which have not ran yet.

– – limit

Limits the number of files to run –limit=3 will run the first 3 files found.

– – archive

At the end of the upgrade, will move all completed files in current folder to archive folder.

– – force_test

Will force the system to run the upgrades on the test server first. IF all tests complete (no sql crash) will continue to run them on the production servrer.

– – with_schema

Will run the schema cheker after upgrades where run. If you have – -force_test Will run it first on the test server. Failure on test server will stop the process.

– – unblock

– – unblock=file_name This will find the filename in the tracking database and remove it from there. Usefull when a file crashed and you pulled it out of the release.

– – mark_completed –mark_complete

– – mark_complete=file_name Manuall mark a filename in the current or in the tracking db as completed.