Rahl Commandr – About

Capabilities

The Rahl Commander (or RCOM for short) system was developed to help my teams manage database objects and dependencies. Currently supports MySQL only. System features include the following: (Each feature get’s it’s own tutorial as a separte post):

• Manage source code for Stored Procedures, SQL functions, Table Views and Triggers.
• Manage build and tear-down of the above DB objects in the environment of your choice.
• Apply and test off-application constraints and schema rules.
• Auto generate auto completion files to be able to reference Stored Procedures within your IDE of choice.
• Manage sql changes/schema upgrades during the lifecycles of the application.

Why developed it

As most PHP systems out there, The system I was writing at the time was doing mostly reading from DB, formatting the text and displaying it on the browser. Or, capturing form data and saving it to the DB.

Most common actions we did where to run a query, taking the result and applying the result to another query. Sometimes those chains would be even longer and could consists of 4-5 queries. There was no point in the PHP code in between those queries. The data was already validated when initially inserted and there was no need for formatting (fetching Foreign Keys).

Really glueing queries in PHP made no sense. Hence I decided to use Stored Procedures (SP). Once My team started on it, we hit the same road blocks everybody else doing SP is familiar with:

• How do you source controll SP code.
• How do you make sure the database server is in sync with your current SP code.
• How to easilly compile/re-compile the entire project, or just sub sections of it (in case you want to release just what changed).
• Is there a way for me to get code completion for Stored Procedures in my PHP IDE?

Rahl Commander, which is named in memory of Lord Richard (Cypher) Rahl, was my solution for those problems.

The Schema Checker

If your system is big enough, and it handles data which you need to track changes in each record, you probably have an audit database(s). We will call it deltadb.
You probably also have some type of archiving DB where you exile problematic records into, instead of deleteting them.
Those most common two scenarios require you to have a set of databases where their structure must mirror your application databases. Mirroring, but with small differences. For example, your delta table might have some extra meta data fields:

1. The date the change was created
2. By whom (user id) was the data changed
3. Why was the data changed (a free text for a comment)

How would you enforce integrity of your tables, when you modify the structure of a table in your app databse?
You must remember to do the same actions on the delta and archive databases…ah … trust humans to remember.
Rahl Commander provides a mechanizem to test and locate not-in-sync tables between your various databases. Supprisingly enough, we call it the SCHEMA CHECKER.

Manage sql changes/schema upgrades

This piece, or similar, is used by any team developing on top of a any sql database. DB schemas evolve over time, some features requires elaborate sql scripts to be run in order. Migrating to new places might require running all historical changes in a certain order. Rahl Commander has a module to handle such tasks. The upgrades module is fast evolving to support more use cases as they come. Arguably, this is the most widely usefull tool in Rahl Commander.

Technology

Rahl Commander is written in Python 3.4. It is (currently) working with MySQL.
It is written such you can use it as a manual command line tool, or integrate it into your tool chain in your deployment/DevOps processes.

What’s next?

In the next series of posts I will show the following subjects:

1. Install and configure Rahl Commander.
2. How to build/drop/clean your db objects.
3. How to integrate the autocompletion piece into your php project.
4. Tutorial on the Schema Checker rule syntax and usage.
5. How to use the Upgrades module to handle schema changes and other sql scripts over time.