|The OpenRulesTM Tutorials Home TOC||User's Guide|
Simple DB-based Rules Repository
4Database Administration Interface
OpenRules provide a simple protocol "db:<filepath>" that allows you to use standard databases to keep and maintain your business rules. To do this, you may place your Excel files with OpenRules tables into any relational database as Blob objects (binary large objects). OpenRules provides a direct access to Excel files saved in a database without necessity to download them into a file system. This protocol supports one database table in which all Excel files are saved as Blob objects with unique keys that usually correspond to relative paths of these files in a file system.
Database Structure ►top
The "db:<filepath>" protocol assumes that all Excel files are saved in one database table called "dbstorage" with the following structure:
- name - a primary key up to 255 characters
- content - a BLOB object
For example, if your Excel files were initially located in the folder "rules/include/" you may copy them to the database using keys such as "/rules/include/FileName.xls". After that, you do not have to change a structure of your OpenRules Environment table - just use the property "include.path" with the value "db:/rules/include/". All included Excel files that were described in the property "include" as "<FileName.xls>" would be directly available to OpenRulesEngine.
Example "HelloJavaDB" with rules inside Apache Derby DB ►top
This example demonstrates how to convert a basic rules project "HelloJava" to work with rules placed into the standard open source Java database known as Apache Derby. This project is included into the standard OpenRules installation under the name "HelloJavaDB". It has exactly the same structure as HelloJava - even the Java code was not changed. We have only added a new folder "db" in which we created a Derby database as a placeholder for rules previously kept in the folder "rules". The folder "db" initially contains only two bat-files: db.bat and loadall.bat that are used to create and administer the database from a command-line interface. Here what we did with HelloJava project and what you may do with your own rule projects to move their repositories to a database.
- Eclipse project HelloJavaDB has one additional library in its Java Build Path: it refers to derby.jar that we included in the updated openrules.config/lib.
- We created an instance of Derby database "dbstorage" inside the folder "db". To do this, we launched a console window (command prompt), navigated to the /db directory, and executed the following command:
- We added a database configuration file db.properties into the folder "rules/main" that already contains the main file HelloCustomer.xls.
- This particular project contains only 3 Excel files:
1) rules/main/HelloCustomer.xls the main file for a rules engine to start with. It contains only one Environment table
2) rules/include/HelloRules.xls: greeting and salutation rules
3) rules/include/HelloMethod.xls: a table with a method that calls the rules from HelloRules.xls.
The main xls-file HelloCustomer.xls continues to be used outside the database serving as an OpenRules configuration file. To upload two other Excel files to the database we executed the following commands:
>db -u /HelloRules.xls -f ../rules/include/HelloRules.xls
>db -u /HelloMethods.xls -f ../rules/include/HelloMethods.xls
Here "-u" stands for "upload", the string "/HelloRules.xls" is a database key for the rules that were uploaded from the local file "../rules/include/HelloRules.xls".
- You always may check the content of your database by command:
>db -l "*"
In our case this command will display:
List for '*'
- To inform OpenRulesEngine that now it should look rather to the database than to a local file system, we only have to change slightly the main file rules/main/HelloCustomer.xls. It used to contain the following Environment table:
Environment include.path ../include/ include <HelloRules.xls> <HelloMethods.xls> import.java hello.* import.static com.openrules.tools.Methods
The modified Environment table will look like here:
Environment datasource classpath:db.properties include.path db:/ include <HelloMethods.xls> <HelloRules.xls> import.java hello.* import.static com.openrules.tools.Methods
Please, note that we did not changed the structure of the project that potentially may involve a lot of changes. We only added a new data source that is our database configuration file "db.properties" and changed the property "include.path" that now is based on the protocol "db:". In this case we expect that the file "db.properties" is located in the classpath. However, we also could use any other standard protocol to locate this file, for example "file:rules/main/db.properties".
- Now, we can execute the rules directly from the database using the same Java program RunHelloCustomer, for example by double-clicking on run.bat.
Database Configuration File "db.properties" ►top
The configuration file "db.properties" is used to define a concrete database configuration. Its location is specified in the OpenRules main xls-file inside an Environment table using the property "datasource". Here is an example of the db configuration file for the HelloJavaDB project:
# OpenRules Data Source Protocol Properties
# DB Access Properties
db.selectSql=select content from dbstorage where name = ?
# Optional DB Administration Properties
db.createDDL=CREATE TABLE dbstorage (NAME VARCHAR(255) NOT NULL,CONTENT BLOB,PRIMARY KEY(NAME))
db.insertSql=insert into dbstorage(name,content) values(?,?)
db.listSql=select name from dbstorage where name like ?
db.deleteSql=delete from dbstorage where name like ?
The property "openrules.protocol.name" defines the name of the simple protocol provided by OpenRules with its implementation class described by the property "openrules.protocol.class". Both these properties could be customized.
To access the database we have to specify 5 properties:
1) db.user - a user name (here is "embedded" is the default name for Apache Derby).
2) db.password - a user password
3) db.url - defines a physical location of the database. You should make sure that this location corresponds to your file structure (!)
4) db.driver - defines a JDBC driver
5) db.selectSql - shows how the db protocol will access the database.
Other parameters are optional. However, if you use a database command-line administration interface provided by OpenRules (see examples above), then these 5 properties should be present.
Simple Database Administration Interface ►top
You may use any DB administration interface to create a table "dbstorage" inside your database and to upload Excel files into this table. However, for your convenience OpenRules provide a simple command-line interface for the DB administration. Here is the list of the available commands and options:
Option "Initialize": -i or --init
Usage: db -i
This option initializes a data storage. Warning: existing data will be destroyed
Option "Upload File": -u or --up
Usage: db -u <key> -f <local-file-path>
This option uploads the content of <local-file-path> into the database
using the <key>. If <local-file-path> is a folder all files and subfolders
this folder will be uploaded by adding their relative paths to the <key>.
Example: db -u /insurance/policy/DriverDiscountRules.xls -f ../rules/insurance/policy/DriverDiscountRules.xls
Option "Upload Directory": -dir or ----from-dir
Usage: db -u <key> -dir <local-dir-path>
This option recursively uploads the entire content of the directory <local-dir-path>
into the database using the <key>.
Example: db -u /hello/rules/include/ -f ../rules/include/
Option "Download": -d or --down
Usage: db -u <key>
This option downloads data from database using this <key>
Example: db -d /insurance/policy/DriverDiscountRules.xls -f c:/temp/DriverDiscountRules.xls
This command will download file DriverDiscountRules.xls into the directory c:/temp.
Option "Remove": -rm or --remove
Usage: db -rm "<mask>"
This option removes data from database using this <mask>. The mask can include
wildcards '*' and '?'
Example: db -rm "*" will remove all uploaded files from the database
Option "List": -l or --list
Usage: db -l
This option lists all keys in the storage. You may use a mask to list only
that satisfy the optional mask. The mask can include wildcards '*' and '?'.
Example: db -l "*" will display all uploaded files
Option "File": -f or --file
Usage: db -f <local-file-path> ...
This option is used with options -u and -d.
Option "Help": -h or --help
Usage: db -h
This option displays a list of all options.
Example "RulesRepositoryDB" with rules inside Java Derby DB ►top
This example demonstrates how to convert a more complex rules hierarchy from a file system to a database. We will take the directory "rules" from a standard OpenRules example "RulesRepository" as a basis and will convert it to the new project "RulesRepositoryDB". Here are the major conversion steps.
- Eclipse project RulesRepositoryDB has one additional library in its Java Build Path: it refers to derby.jar that is included in the updated openrules.config/lib.
- Create an instance of Derby database "dbstorage" inside the folder "db". To do this, we executed the following command from a system console (being inside the db directory):
- Add a database configuration file db.properties into the folder "rules/main" that already contains the main file Main.xls.
- This project contains many Excel files:
rules/main/Main.xls the main file for a rules engine to start with. It contains only the Environment table
The main xls-file Main.xls will continue to be used outside the database serving as a OpenRules configuration file. To upload all other Excel files to the database we may execute many command like this one:
>db -u /examples/RulesRepositoryDB/CategoryA/RulesA1.xls -f ../rules/CategoryA/RulesA1.xls
or alternatively we may execute only one command:
>db -u /examples/RulesRepositoryDB/rules -f ../rules
to copy all files and subfolder starting from ../rules folder into the database. All files from the directory "../rules" will be uploaded. The proper keys will be created automatically. If now we enter the command:
>db -l "*"
it will display:
List for '*'
- To inform OpenRulesEngine that now it should look rather to the database than to a local file system, we only have to change slightly the main file rules/main/Main.xls. It used to contain the following Environment table:
Environment include.path ../ include <CategoryA/RulesA1.xls> <CategoryA/RulesA2.xls> <CategoryB/RulesB1.xls> <CategoryB/RulesB2.xls> <Common/libA/libRulesX.xls> <Common/libA/libRulesY.xls> import.java myjava.package1.* import.static com.openrules.tools.Methods
The modified Environment table will look like here:
datasource classpath:db.properties include.path db:/examples/RulesRepositoryDB/rules/ include <CategoryA/RulesA1.xls> <CategoryA/RulesA2.xls> <CategoryB/RulesB1.xls> <CategoryB/RulesB2.xls> <Common/libA/libRulesX.xls> <Common/libA/libRulesY.xls> import.java myjava.package1.* import.static com.openrules.tools.Methods
- The db configuration file rules/main/db.properties will be exactly the same as above with one exception - a physical location of the database file:
Make sure that you change this property to an absolute or relative path that corresponds to your directory structure.
- Now, we can execute the rules directly from the database by double-clicking on compile.bat and run.bat.