SQL Inside Rules

OpenRules allows you to migrate SQL queries to business rules. A simple example of how to do it is described here. Below we describe the migration of a more complex SQL query to business rules. This example deals with the classic MySQL Sample Database that has the following organization:

The following SQL query

is supposed to find customers with unpaid orders.

The selection criteria from this SQL query can go to the OpenRules table of the type “DataSQL“:

Let’s assume that we want our decision model to produce certain alerts for the “essential” unpaid order based on the Total Ordered Amount and Unpaid Amount. For example, we may generate different alerts under the conditions specified the following business rules:

This decision table should be applied to every unpaid order received from a database using the DataSQL “UnpaidOrders”. To use the defined above “UnpaidOrders” as a data source for regular business decision tables, we need to define the corresponding business concepts in the Glossary:

Note that we added another business concepts “Stats” with decision variables Unpaid Amount, Unpaid Ratio, and the array of Alerts that will be used in the decision table “”(used in the table “AddAlert”) should be defined in the regular business glossary:””DefineAlerts”:

This table iterates over UnpaidOrders using the SQL query defines in the DataSQL table “UnpaidOrders”. For each selected “UnpaidOrder” this table calculates decision variables “Unpaid Ratio” and “Unpaid Amount”, and then executes the above table “AddAlert”.

This way our decision model will produce alerts for all unpaid orders that satisfy the above conditions the table “AddAlert”. The alerts will be generated by calling the corresponding Excel tables alert1, alert2, or alert3. These methods can send emails or do something else, e.g. call ant 3rd party method defined in Excel or in Java. In this implementation our alert methods simply produce different alert messages that will be added to the array “Alerts”:

We may execute our decision model using the standard file “test.bat“. It will read directly from the database and will produce the following alerts:

Note. Before running “test.bat” you need to make sure that the database is open by doing the following: 1) in the Windows Start menu type ‘services’ in the search bar; 2) Scroll down to find ‘MariaDB’, right-click on it, and choose ‘Start the service’.

This way we clearly separated the selection logic that is DB-specific and business logic that can be easily adjusted by business analysts and will stay the same when a database is changed. This approach allows you to add more complex business logic by simply changing the decision tables without any changes in DataSQL.

If you want to add more business decision variables, you can make the proper changes in the business glossary and rules, and without any additional coding your decision model will be ready to execute advanced business logic.

If you need to use additional variables from the database, you may adjust your selection criteria only in the tables DataSQL and the Glossary, and your decision model will work with an updated database. In all cases, no additional programming is required.

If you want to invoke this decision model from your Java you can use the standard OpenRules Java API. To be able to connect to your database you need to setup 3 Environment variables: “openrules.sql.url”, “openrules.sql.user”, and “openrules.sql.password”. For example, in this example these variable are defined in the file “project.properties” and you can use this following Java test to run the decision model:

You also may deploy this model as a REST service and test it from POSTMAN.

This example shows that without sacrificing power of SQL OpenRules allows you to naturally integrate business rules with a relational database.  Download Rule DB and run and analyze the example “SQLInsideRules” and other examples.