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
Cooking In BINARY 