DB-based Repositories with Version Control

The OpenRules^TM Tutorials Home TOC

User's Guide

DB-based Rules Repository with Version Control

4Database Structure
4Repository Versioning Concept
4Example "RulesRepositoryDBV"
4Maintaining Multiple Repository Versions
4Database Administration Interface

OpenRules provide a protocol "dbv://<repository:version>/<filepath>" that allows you to use standard databases to keep and maintain your business rules. This protocol is similar to "db:<filepath>" but additionally provides version control capabilities. This protocol assumes that you 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 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. You may check-in or check-out your Excel files in a way similar to standard version control systems such as Subversion and tell OpenRules engine which version of rules saved in the database you want to use.

Database Structure ►top

The "dbv://<repository:version>/<filepath>" protocol assumes that all Excel files are saved in one database table called "dbstorage" with the following structure:

revision - an integer, not null, a primary key

name - a string up to 255 characters, not null

author - a string up to 64 characters, not null

comments - a CLOB object (large objects consisting of single-byte fixed-width character data)

content - a BLOB object, a placeholder for an Excel file

deleted - a single character, not null, default is 'N'

timestamp - a timestamp, not null, default is current_timestamp.

You may initialize a repository with your own name, say "rules" and start checking in your Excel files into this repository.

Repository Versioning Concept ►top

The "dbv:" protocol supports a simple check-in/check-out mechanism that in general is similar to the Subversion check-in/check-out. Upon checking in, all files in the database are uniquely identified by an automatically assigned revision number and their names. When you check in a file that is already checked-in the previous copy is not deleted and is still available under its own revision number. The latest revision number becomes a current revision number of the repository. It is always possible to get access to the different states of repository by their revision numbers. For example,

    "dbv://rules:25/policy/Driver.xls"

provides an access to the file "/policy/Driver.xls" whose revision number is equal or less than 25. Your OpenRules Environment table may define the property "include" with the value

    "dbv://rules:/policy/Driver.xls"

In this case when a version number is missing, OpenRulesEngine will pick up the latest revision of the file with the name "/policy/Driver.xls".

Example "RulesRepositoryDBV" with rules inside Apache Derby DB   ►top

This example demonstrates how to convert a 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 "RulesRepositoryDBV" with ability to use different rules revisions. This project is included into the standard OpenRules installation. Here are the conversion steps.

Initially the project "RulesRepositoryDBV" has the same structure as the project "RulesRepository".   The Java code will not be changed. We will check-in all rules from the folder "rules" into the standard open source Java database known as Apache Derby.   Add a new folder "db" as a placeholder for a Derby database.

Add to the folder "db" the following bat-file "db.bat"

========================================================================
@echo off

cd %~dp0

set db.properties=../rules/main/db.properties

set CONFIG=../../openrules.config/lib

set CLASSPATH=%CONFIG%/openrules.dbv.jar

set CLASSPATH=%CLASSPATH%;%CONFIG%/commons-cli-1.0.jar

set CLASSPATH=%CLASSPATH%;%CONFIG%/derby.jar

java -DDB_PROPERTIES="%db.properties%" -classpath "%CLASSPATH%" com.openrules.dbv.admin.DB %*
=========================================================================

This file will be used as a command-line interface to create and administer the database.

Add to the folder "rules/main/" the following file "db.properties". It will be used as the database configuration file:
========================================================================

# OpenRules Data Source Protocol Properties

openrules.protocol.name=dbv

openrules.protocol.class=com.openrules.dbv.DbvDataSourceHandler

# DB Access Properties

db.user=embedded

db.password=none

db.url=jdbc:derby:/_openrules/openrules.examples/RulesRepositoryDBV/db/repository;create=true

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

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

db.createDDL=\

CREATE TABLE repo (\

REVISION INT NOT NULL,\

NAME VARCHAR(255) NOT NULL,\

AUTHOR VARCHAR(64) NOT NULL,\

COMMENTS CLOB,\

CONTENT BLOB,\

DELETED CHAR(1) NOT NULL DEFAULT 'N',\

TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\

PRIMARY KEY(REVISION)\

)

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

# DB Administration Properties

db.implementation.class=com.openrules.dbv.admin.impl.RepoDAOImpl

=========================================================================
The folder "../rules/main/" is a natural place for the file "db.properties" because this folder already contains the main file Main.xls used by an OpenRulesEngine.

The property "openrules.protocolname" defines the name "dbv" of this data source protocol. The property "openrules.protocol.class" defines its implementation class provided by OpenRules using openrules.dbv.jar.

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.selectDDL - defines how the repository will be created.
5) db.selectSql - shows how the db protocol will access the database.

The remaining three properties define are used by a database command-line administration interface provided by OpenRules (see below).

Switch to a command line interface and position yourself into the newly created directory "db". We will create a new repository called "rules" inside a Derby database. To do that, we execute the following command from a system console:

>db -r rules -i

First, a new instance of tne Derby database will be created in accordance with the property "db.util" from the db.properties file. In our case it is defined as:
db.url=jdbc:derby:/_openrules/openrules.examples/RulesRepositoryDBV/db/repository;create=true

Make sure that first you change this property to an absolute or relative path that corresponds to your directory structure. Thus, a subfolder "repository" will be created inside the folder "db".

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

Let's add one more file rules/Common/Version.xls to display the current repository version using the following method:

Method void displayVersion()

out("This is a DEVELOPMENT revision of the rules repository");

The main xls-file Main.xls will continue to be used outside the database serving as a OpenRules configuration file.

To check-in all Excel files (except of Main.xls) to the database we will execute the following commands:

>db -ci /Common/Version.xls -f ../rules/Common/Version.xls -u admin -m "initial check-in"
>db -ci /Common/libA/libRulesX.xls -f ../rules/Common/libA/libRulesX.xls -u admin -m "initial check-in"

>db -ci /Common/libA/libRulesY.xls -f ../rules/Common/libA/libRulesY.xls -u admin -m "initial check-in"

>db -ci /CategoryA/RulesA1.xls -f ../rules/CategoryA/RulesA1.xls -u admin -m "initial check-in"

>db -ci /CategoryA/RulesA2.xls -f ../rules/CategoryA/RulesA2.xls -u admin -m "initial check-in"

>db -ci /CategoryA/SubCategoryA1/RulesA11.xls -f ../rules/CategoryA/SubCategoryA1/RulesA11.xls -u admin -m "initial check-in"

>db -ci /CategoryA/SubCategoryA1/RulesA12.xls -f ../rules/CategoryA/SubCategoryA1/RulesA12.xls -u admin -m "initial check-in"

>db -ci /CategoryB/RulesB1.xls -f ../rules/CategoryB/RulesB1.xls -u admin -m "initial check-in"

>db -ci /CategoryB/RulesB2.xls -f ../rules/CategoryB/RulesB2.xls -u admin -m "initial check-in"

After every check-in the system will display the latest revision number like here:

>db -ci /CategoryB/RulesB2.xls -f ../rules/CategoryB/RulesB2.xls -u admin -m "initial check-in"

File ../rules/CategoryB/RulesB2.xls has been checked in rules:9:/CategoryB/Rules
B2.xls
Repository at revision 9.

If now we enter the command:

>db -l "*"

it will display something like this:

=====================================================

>db -l "*"
/Common/Version.xls r.1 2007-05-18 06:41:40
/Common/libA/libRulesX.xls r.2 2007-05-18 06:02:41
/Common/libA/libRulesY.xls r.3 2007-05-18 06:17:41
/CategoryA/RulesA1.xls r.4 2007-05-18 06:31:41
/CategoryA/RulesA2.xls r.5 2007-05-18 06:46:41
/CategoryA/SubCategoryA1/RulesA11.xls r.6 2007-05-18 06:01:42
/CategoryA/SubCategoryA1/RulesA12.xls r.7 2007-05-18 06:30:42
/CategoryB/RulesB1.xls r.8 2007-05-18 06:48:42
/CategoryB/RulesB2.xls r.9 2007-05-18 06:04:43
Repository at revision 9.

=====================================================

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 dbv://rules/

include <CategoryA/RulesA1.xls>

<CategoryA/RulesA2.xls>

<CategoryB/RulesB1.xls>

<CategoryB/RulesB2.xls>

<Common/Version.xls>

<Common/libA/libRulesX.xls>

<Common/libA/libRulesY.xls>

import.java myjava.package1.*

import.static com.openrules.tools.Methods

Now, we can execute the rules directly from the database by double-clicking on compile.bat and run.bat. The results will be displayed as:

[java] ============================================
[java] OpenRulesEngine: file:rules/main/Main.xls
[java] ============================================
[java] This is a DEVELOPMENT revision of the rules repository
[java] Execute RulesA1
[java] Execute RulesA11
[java] Execute LibRulesX
[java] Sat May 19 09:57:38 BST 2007
[java] Execute RulesA2
[java] ============================================

Maintaining Multiple Repository Versions    ►top

You may use revisions to maintain different version of your rules repositories. Let's consider the following scenario using a rules repository we created above as an example. The current state of this repository will always represent the latest rules development version. At certain point of development we may decide to label the current state of repository as a release with some arbitrary name. For example, let's label the current state of our rules repository as "Alpha Release". Remember the last revision number was 9. For the testing purposes let's modify the file /Common/Version.xls to display the words "Alpha Release" instead of development. We can check-out this file and make the following changes in it:

Method void displayVersion()

out("This is a ALPHA RELEASE of the rules repository - revision 10");

You may wonder why we use revision 10 instead of 9. Because when we check-in back the modified Version.xls it will make the repository revision equal to 9+1=10. Here are the results of this check-in:
>db -ci /Common/Version.xls -f ../rules/Common/Version.xls -u admin -m "ALPHA RELEASE"
File ../rules/Common/Version.xls has been checked in rules:10:/Common/Version.xls
Repository at revision 10.

To run OpenRulesEngine against this particular Aplha Release (revision 10) of our rules repository, we have to modify the OpenRules configuration file Main.xls as follows:

Environment

datasource classpath:db.properties

include.path dbv://rules:10/

include <CategoryA/RulesA1.xls>

<CategoryA/RulesA2.xls>

<CategoryB/RulesB1.xls>

<CategoryB/RulesB2.xls>

<Common/Version.xls>

<Common/libA/libRulesX.xls>

<Common/libA/libRulesY.xls>

import.java myjava.package1.*

import.static com.openrules.tools.Methods

Revision 10 is ALPHA RELEASE

Please pay attention that we only changed "dbv://rules/" to "dbv://rules:10/". We also added an optional comment under the Environment table.
If you double-click to the run.bat now, it will produce the following results:

[java] ============================================
[java] OpenRulesEngine: file:rules/main/Main.xls
[java] ============================================
[java] This is a APLHA RELEASE of the rules repository - revision 10
[java] Execute RulesA1
[java] Execute RulesA11
[java] Execute LibRulesX
[java] Sat May 19 09:57:38 BST 2007
[java] Execute RulesA2
[java] ============================================

How to run OpenRulesEngine against different versions of the rules repository at the same time? It is a matter of where you want to place different versions of your Main.xls file. For example, let's place a new Main.xls (for the Alpha Release) in the folder "rules/main.alpha" while the folder "rules/main" will continue to keep our old Main.xls (without any revision number). Directing the OpenRulesEngine to one Main.xls or another will execute different versions. For example, let's change the main Java module of this particular project Appl.java to make it execute OpenRulesEngine first for the DEVELOPMENT and then for the ALPHA releases. Here is a modified code of Appl.java:

public static void main(String[] args) {

    runRuleEngine("file:rules/main/Main.xls");

    runRuleEngine("file:rules/main.alpha/Main.xls");

}

public static void runRuleEngine(String mainXlsFile) {

    OpenRulesEngine engine = new OpenRulesEngine(mainXlsFile);

    System.out.println(

    "\n============================================\n" +

    "OpenRulesEngine: " + mainXlsFile +

    "\n============================================\n");

    Appl appl = new Appl();

    Object[] objects = new Object[] { appl };

    String methodName = "main";

    engine.run(methodName,objects);

    System.out.println(

    "\n============================================\n");

}

If you double-click to the run.bat now, it will produce the following results:

[java] ============================================
[java] OpenRulesEngine: file:rules/main/Main.xls
[java] ============================================
[java] This is a DEVELOPMENT revision of the rules repository
[java] Execute RulesA1
[java] Execute RulesA11
[java] Execute LibRulesX
[java] Sat May 19 09:57:38 BST 2007
[java] Execute RulesA2
[java] ============================================
.....
[java] ============================================
[java] OpenRulesEngine: file:rules/main.alpha/Main.xls
[java] ============================================
[java] This is a APLHA RELEASE of the rules repository - revision 10
[java] Execute RulesA1
[java] Execute RulesA11
[java] Execute LibRulesX
[java] Sat May 19 09:57:38 BST 2007
[java] Execute RulesA2
[java] ============================================

Database Administration Interface    ►top

OpenRules provide a command-line interface for the DB administration using the "dbv:" protocol.   You may see concrete examples above. Here is the list of the available commands and options:

Option "Initialize": -i or --init

Usage: db -i   or db -r <repo> -i

This option initializes a repository <repo>. By default the bat-file db.bat is using "-r rules", so the option -r <repo> may be omitted. Warning: Existing data will be destroyed

Example: db -i

Option "Check-In": -ci or --checkin

Usage: db -ci <repo-path> -f <local-file-path> [-u <user>] [-m <message>]

This option checks in (uploads) the content of <local-file-path> into the database

using the <repo-path> as its name. The file will be given a new revision name that is equal to (the current repository revision + 1). The user name and message (if any) will be attached to this revision. If <user> is not specified, the option will use the name specified by the property "db.user" inside the file "db.properties".

Example: db -ci /Common/libA/libRulesX.xls -f ../rules/Common/libA/libRulesX.xls -u admin -m "initial check-in"

Option "Check-Out": -co or --checkout

Usage: db -co <repo-path> -f <local-file-path> [-rev <revision-number>]

This option checks out (downloads) data from the database repository "rules" using this <repo-path> and <revision-number> into the <localfile-path>. If revision is omitted the latest revision will be used.

Example: db -co /Common/libA/libRulesX.xls -f ../rules/Common/libA/libRulesX.xls -rev 10

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 the repository using this <mask>. The mask can include

wildcards '*' and '?'

Example: db -rm "*" will remove all files from the rules repository

Option "List": -l or --list

Usage: db -l <mask> [-rev <number>]

This option lists items checked-in to the rules repository. The mask is used to specify different items in the repository. The mask can include wildcards '*' and '?'.

Example: db -l "*" -rev 9 will display all checked-in files with a revision number 9 or under.

Option "History": -y or --history

Usage: db -y <repo-path>

This option display detailed information about all revisions for the selected <repo-path>.

Example: db -y /Common/Version.xls will display all revisions of the file /Common/Version.xls

Option "Revision": -rev or --revision

  Usage: -rev <number>

  This option is used with option -co.

Option "File": -f or --file

  Usage: -f <local-file-path>

  This option is used with options -ci and -co.

Option "Help": -h or --help

  Usage: db -h

  This option displays a list of all options.

►top

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

Environment
datasource	classpath:db.properties
include.path	dbv://rules/
include	<CategoryA/RulesA1.xls>
	<CategoryA/RulesA2.xls>
	<CategoryB/RulesB1.xls>
	<CategoryB/RulesB2.xls>
	<Common/Version.xls>
	<Common/libA/libRulesX.xls>
	<Common/libA/libRulesY.xls>
import.java	myjava.package1.*
import.static	com.openrules.tools.Methods

Environment
datasource	classpath:db.properties
include.path	dbv://rules:10/
include	<CategoryA/RulesA1.xls>
	<CategoryA/RulesA2.xls>
	<CategoryB/RulesB1.xls>
	<CategoryB/RulesB2.xls>
	<Common/Version.xls>
	<Common/libA/libRulesX.xls>
	<Common/libA/libRulesY.xls>
import.java	myjava.package1.*
import.static	com.openrules.tools.Methods

Revision 10 is ALPHA RELEASE