RAHL COMMANDER for SQL

A command line tool to simplify the managment of DB objects and scripts in your project.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Dependencies

1. Python >= 3.3
2. Python mysql.connector
3. Mysql >= 5.1

Currently supports objects for

1. Mysql

Currently supports auto completion for

1. PHP + Eclipse

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Description

Many projects requires the usage of Stored Procedurs, Triggers, SQL Functions, Views, various scripts and Schema Checking/validations.
Rahl Commander (rcom) provides facilities and structure to manage all those pieces of code used in your project.

rcom will do the following for you:

1. Build for you all or some of the objects into the DB of your choice.
2. Will generate auto completion files for your IDE (how nice it is to get auto completion for a stored procedure in your PHP, heh?).
3. Will give easy cleanup of the DB of all or some objects (Great for use in release scritps).

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Naming Conventions

• APPDIR is where you checked out Rahl Commander.
• ASSETS is the assets folder, right under APPDIR.
• BIN is the bin folder right under APPDIR
• CONFIG is APPDIR/config/__init__.py
• IGNOREDIR is APPDIR/config/ignore_list.py

Installation

(I assume you have Python and the necessary mysql connector).
Currently supporting only manual (very simple) installation.

1. Checkout/export the project where ever you want. This folder becomes APPDIR.
2. Check your ASSETS folder has the following subfolders: autocompletion, functions, scripts, sp, triggers, views
3. Copy directory cp -r example_config config
4. Open CONFIG/__init__.py and make sure the DB credentials are correct
5. If not existing, create DB folders. For example, if u have a database named proddb and it has stored procedures and triggers, go to ASSETS/sp and create folder proddb. Then go to folder ASSETS/triggers and create folder proddb. So you end with ASSETS/sp/proddb and ASSETS/sp/triggers. (Check the examle_assets structure, which is setup for DBs dhara and dhara_views).
6. Run the test.py --all under the BIN to see all is good.

You Are Done!

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Examples: CD to bin

1. build all elements python build.py --all

2. drop all elements -> THIS WILL DROP JUST THE OBJECTS WHO ARE IN A FILE. IF u need a full clean, run the cleaner tool python drop.py --all

3. build all stored procedures python build.py -s

4. drop all triggers in DB dhara python drop.py -t -ddhara

5. Clean all objects in all the databases in assets python cleaner.py --all

RCOM upgrade table structure for mysql

CREATE TABLE `rcom_sql_upgrades` (
 `file_name` varchar(255) 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') NOT NULL DEFAULT 'pending_completion',
 `error_message` varchar(2000) DEFAULT NULL,
 PRIMARY KEY (`file_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

RCOM upgrade table structure for mssql

CREATE TABLE dbo.rcom_sql_upgrades (
 file_name varchar(255) NOT NULL,
 execute_order INT NOT NULL DEFAULT  1,
 time_runned DATETIME NULL,
 execution_status VARCHAR(20) NOT NULL CHECK (execution_status IN ('pending_completion','failed','failed_in_test','completed','completed_in_test')) DEFAULT 'pending_completion',
 error_message varchar(2000) DEFAULT NULL,
 PRIMARY KEY (file_name) WITH (IGNORE_DUP_KEY = ON)
)