The OpenRulesTM Tutorials         Home  TOC   PREV TOP NEXT User's Guide
Divider

Simple DB-based Rules Repository

4Database Structure
 4Example "HelloJavaDB"
4Database Configuration
4Database Administration Interface
4Example "RulesRepositoryDB"

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:

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.

  1. 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.
     
  2. 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:

     >db -i
     
  3. We added a database configuration file db.properties into the folder "rules/main" that already contains the main file HelloCustomer.xls.
     
  4. 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".
     
  5. You always may check the content of your database by command:

    >db -l "*"

    In our case this command will display:

    List for '*'
    /HelloRules.xls
    /HelloMethods.xls
     
  6. 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". 
     

  7. 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

openrules.protocol.name=db

openrules.protocol.class=com.openrules.db.DbDataSourceHandler
 

# DB Access Properties

db.user=embedded

db.password=none

db.url=jdbc:derby:C:/_openrules/openrules.examples/HelloJavaDB/db/dbstorage;create=true

db.driver=org.apache.derby.jdbc.EmbeddedDriver

db.selectSql=select content from dbstorage where name = ?
 

# Optional DB Administration Properties

db.statementfactory.class=com.openrules.jdbc.StatementFactoryDerbyEmbedded

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.

  1. 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.
     
  2. 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):

     >db -i
     
  3. Add a database configuration file db.properties into the folder "rules/main" that already contains the main file Main.xls.
     
  4. 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
    rules/CategoryA/RulesA1.xls
    rules/CategoryA/RulesA2.xls
     rules/CategoryA1/RulesA1.xls
    rules/CategoryA1/SubCategoryA1/RulesA11.xls
    rules/CategoryA1/SubCategoryA1/RulesA12.xls
    rules/CategoryB/RulesB1.xls
    rules/CategoryB/RulesB2.xls
    rules/Common/libA/libRulesX.xls
    rules/Common/libA/libRulesY.xls

    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 '*'
    /examples/RulesRepositoryDB/rules/CategoryA/RulesA1.xls
    /examples/RulesRepositoryDB/rules/CategoryA/RulesA2.xls
    /examples/RulesRepositoryDB/rules/CategoryA/SubCategoryA1/RulesA11.xls
    /examples/RulesRepositoryDB/rules/CategoryA/SubCategoryA1/RulesA12.xls
    /examples/RulesRepositoryDB/rules/CategoryB/RulesB1.xls
    /examples/RulesRepositoryDB/rules/CategoryB/RulesB2.xls
    /examples/RulesRepositoryDB/rules/Common/libA/libRulesX.xls
    /examples/RulesRepositoryDB/rules/Common/libA/libRulesY.xls
    =====================================================
     

  5. 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:

    Environment
    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

     

  6. 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:
     

    db.url=jdbc:derby:C:/_openrules/openrules.examples/RulesRepositoryDB/db/dbstorage;create=true

    Make sure that you change this property to an absolute or relative path that corresponds to your directory structure.
     

  7. Now, we can execute the rules directly from the database by double-clicking on compile.bat and run.bat.

 

►top

Divider