RCOM – How to use the DB Objects Builder

Today, we will learn how to use DOBER (Db Objects BuildER). DOBER is the module in Rahl Commander which provides the utilities to build, drop and total cleanup of db objects in your tracked databases. Db objects are Stored procedure, Views, Functions and Triggers. The common denominator is “code which is saved and run inside the database server”.
We will cover the following topics:

  • Folder strucutre for DOBER
  • DOBER related commands/utilities
    • build.py
    • drop.py
    • clean.py

Folder strucutre for DOBER

The DOBR module uses four folders in your assets folder. sp, views, triggers, functions. Check example_assets folder.
Objects you do not plan to use (you dont have triggers in your system, for example), no need to create their respective subfolders. In which case the test.py script will raise an error, but it can be ignored (see Install tutorial).
If you copied the example_assets folder as a template, make sure to delete anything under those four folders.
The first level of subfolders under sp, views, triggers, functions is the database name the objects will be built under.
IMPORTANT! The folder name must match exactly your database name. Under the database name folder you can use what ever structure of folders and subfolders you like. RCOM will read all folders recursivly. You can have all your files in the top directory, or build a tree of sub-folder that correlates to your app logic.
The object file names can be anything (the actual name for the object is in the code, inside the file). File names must end with .sql.

Examples:

For the examples I will have the following two stored procedures and two views.
(We will assume the tables they reference all exists).

 -- SP mark_soldier_alive.sql 
CREATE PROCEDURE mark_soldier_alive (IN in_soldier_id INT)
BEGIN
	UPDATE common_soldiers
        SET status = 'alive'
        WHERE soldier_id = in_soldier_id;
END


 -- SP kill_a_soldier.sql 
CREATE PROCEDURE kill_a_soldier (IN in_soldier_id INT)
BEGIN
	UPDATE common_soldiers
        SET status = 'fakak'
        WHERE soldier_id = in_soldier_id;
END


 -- VIEW all_live_soldiers.sql 
CREATE VIEW all_live_soldiers_v  
AS 
SELECT * FROM dhara.soldiers WHERE status='alive';
-- VIEW all_dead_soldiers.sql CREATE VIEW all_dead_soldiers_v AS SELECT * FROM dhara.soldiers WHERE status='fakak';

NOTICE! The code for the stored procedure does not end with a ;
; is used to separate SQL commands inside the stored procedure. The same holds for functions and triggers code.

NOTICE! There is no db name in the declaration of the object CREATE VIEW all_live_soldiers_v (same for any object). The db name is derived from the top folder name. In the example below, we can see the objects will be created respectivly in db dhara and in db dhara_vw.

The assets file folders to support this can look like so:

   assets_folder
                |_ 
                |  sp
                |       |_
                |          dhara
                |               |_
                |               |  mark_soldier_alive.sql
                |               |_
                |                  kill_a_soldier.sql
                |_         
                   views
                        |_
                           dhara_vw
                                |
                                |_
                                |  all_live_soldiers.sql
                                |_
                                   all_dead_soldiers.sql
                           

General system parameters

Verbosity parameter

As a parameter to each command you can send the verbosity level ofthe output you wish. There is also a default verbosity level that can be configured in the config files.
-vvvv is the higher level of debug output.
-v is the lowest level. Will output only fatal errors.

Assets location parameter

The location of the assets folder can be configured in the config but it can also be overriden by using the following

python command.py -a /path/to/assets/folder
python command.py --assets=/path/to/assets/folder

Server parameter

The DB server can be defined in the config file, but it can also be overriden via the
–server parameter

python command.py --server='username:password@host'

DOBER related commands/utilities

DOBER comes with three utilities to manage db objects. Below I describe this three utilities and the various options they can be run with.
All the commands for RCOM reside under the bin folder.
Every command is being run like so:

cd rcom/bin
python ./comandname.py options

Your python command line exe must be Python version 3.3 or greater.

drop.py

The drop utility is used to remove from the database server objects. The drop command will drop only tracked objects. Tracked objects are objects that exists in the assets folder. An object that does not have corresponding file in the assets folder can’t be dropped, even if the database this object is in, exists inside the assets folder.
Parameters:

-v -vv -vvv -vvvv
The verbosity level
-s
Drops only stored procedures
-t
Drops only triggers
-w
Drops only views
-f
Drops only functions
– – all
Drops Stored Procedures and Views and Triggers And Functions
-a or – – assets
alternate assets folder location
– – server
alternate server credentials and location

build.py

The build utility is used to build objects into the database server. The build command will build only tracked objects. Tracked objects are objects that exists in the assets folder. An object that does not have corresponding file in the assets folder can’t be built.
NOTICE! The build command first runs the drop command with the same params you send it
Parameters:

-v -vv -vvv -vvvv
The verbosity level
-s
Builds only stored procedures
-t
Builds only triggers
-w
Builds only views
-f
Builds only functions
– – all
Builds Stored Procedures and Views and Triggers And Functions
-a or – – assets
alternate assets folder location
– – server
alternate server credentials and location

clean.py

The clean utility is used to remove from the database server ALL objects in the tracked databases. The clean command will drop tracked and untracked objects, but only in the tracked databases. Tracked objects are objects that exists in the assets folder. An object that does not have corresponding file in the assets folder is untracked object. A Tracked Database is a databases which it’s folder exists inside the assets folder.
Parameters:

-v -vv -vvv -vvvv
The verbosity level
-s
Removes all stored procedures in the tracked databases
-t
Removes all triggers in the tracked databases
-w
Removes all views in the tracked databases
-f
Removes all functions in the tracked databases
– – all
Removes all in the tracked databases: Stored Procedures and Views and Triggers And Functions
-a or – – assets
alternate assets folder location
– – server
alternate server credentials and location

NOTICE! throughout the life cycle of an application, you might remove DB objects from your code base. To make sure this reflects in the database, you must use the clean command, as the deleted object is no longer considered tracked (you removed it’s file from the assets folder).

QUESTIONS? FEEDBACK? All are welcome, post as a comment.

NEXT: How to integrate the autocompletion piece into your php project

FIN

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close