Implementation of a moderately complex data constraint business rule using RuleGen 3.0

In a previous article, I have introduced RuleGen 3.0 – a 2nd generation business rule implementation framework for the Oracle Database: https://technology.amis.nl/blog/?p=12807. RuleGen provides a solid, structured, scalable framework for database developers to implement data constraints in. This article demonstrates how a moderately complex rule is analyzed and designed and implemented with RuleGen 3.0. If you want to try out RuleGen yourself, you can download the framework under trial conditions from http://www.rulegen.com/free-download .

This article illustrates the essential steps in the business rule design and build process:

• describe the business in human readable format – preferably using names of entities and attributes or even better tables and columns
• analyze the events that potentially violate the business rule and should therefore trigger evaluation of the rule
• define the filter condition (a SQL expression) that identifies the records that should be checked for each triggering event (typically only a very small subset of the records in a table need to be explicitly validated upon an event)
• define the rule validation logic in terms of SQL – write the SQL query that should not return any records in order for the rule to be valid
• record the events, the filter conditions and the validation SQL in the RuleGen design time UI
• configure additional characteristics of the rule – such as validation time (statement or transaction), enabled status and log-setting
• generate the rule implementation code and deploy the rule to the run time environment

note that these steps only involve a minimum of programming: typically two or three relatively simple SQL queries. No PL/SQL programming is involved. No additional tools (besides the APEX UI of RuleGen or its APIs) are required.

The rule that I will implement in this article is specified in the context of a familiar data model – see the figure below:

The rule that needs to be enforced is: “Employees who are a SALESREP should not work for a department that is located in Amsterdam”. We will not be discussing the business meaning of this rule in this article – we take this rule as the outcome of careful analysis of our business. Our responsibility is to ensure that no data will enter our enterprise database that does not comply with this rule.

Describe the business in human readable format

The rule was stated in terms that are already fairly close to the data model. Perhaps one additional iteration is useful to use names of tables and columns, describing the rule as:

Employees (EMP records) with a JOB with value SALESREP should not work for a department (have a DEPTNO corresponding with DEPTNO in a DEPT record) that has the value AMSTERDAM for its LOC.

Analyze the events that potentially violate the business rule and should therefore trigger evaluation of the rule

The (data manipulation) events that may result in violation of this rule are identified as follows:

• an update of the LOC[ation] value for a department that changes this value to AMSTERDAM could result in rule violation (because there can be employees with job SALESREP in that department). Delete of insert of departments cannot result in violation.
• an insert of an employee can violate the rule – when that employee is a SALESREP. Delete of an employee cannot cause the rule to be violated
• an update of an employee can be the cause of violation of the rule, but only when the JOB is updated (to be SALESREP) or when the DEPTNO is updated (transfering an employee to a different department

Define the filter condition to identify records to be checked

For each triggering event, typically only a very small subset of the records in a table need to be explicitly validated upon an event.

An update of a department needs only to result in a department to be checked when the LOC is updated and the new value is AMSTERDAM. All other Department updates can be ignored with regard to this rule. In RuleGen terms, we create a query that returns the DEPTNO of the department(s) to which this condition applies:

select new_deptno as deptno
from updated_rows
where new_loc = 'AMSTERDAM'
and old_loc!= 'AMSTERDAM'

When an employee is updated, only for the updated employee needs to be checked if the rule still holds. More specifically: only when the JOB or the DEPTNO is updated and only when the (new) JOB is SALESREP do we need to validate the rule.

select new_empno as empno
from  updated_rows
where new_job='SALESREP'
and  (old_job <>'SALESREP' or updated_deptno = 'TRUE')

An insert of an employee only requires our attention if the new employee is a SALESREP:

select empno as empno
from  inserted_rows
where job='SALESREP'

Define the rule validation logic in terms of SQL

RuleGen performs validation of a business rule by execution a SQL query that should not return any records in order for the rule to be valid. So the query should query records that do not comply with the business rule. If everything is valid, no rows are returned.

For our rule, we deal with two angles, one from the (updated) department perspective and one from new or updated employees. The query that selects employees that do not comply with the rule are selected with this query:

select 'x'
from EMP
join
DEPT
on (emp.deptno = dept.deptno)
where emp.empno = p_empno
and dept.loc = 'AMSTERDAM'

Note how we use a parameter – p_empno. This query is invoked for specific employees: only for those inserted and updated employees that the filter condition described above produces. RuleGen injects the employee numbers of those employees in this validation query.

In a similar vein, the filter condition for departments returns the departments that need to be validated – updated departments where the LOC is modified and the new value is AMSTERDAM. The DEPTNO values of the departments that the filter returns are injected in the next query that performs the validation from the department side of things:

select 'x'
from EMP
join
DEPT
on (emp.deptno = dept.deptno)
where (DEPT.DEPTNO = p_deptno )
and job = 'SALESREP'
and loc = 'AMSTERDAM'

Record the events, the filter conditions and the validation SQL in the RuleGen design time UI

The steps we have gone through so far could have been performed in the RuleGen Design Time user interface. However, the UI is a little sober and does not offer very much support to the developer. Now is good time to enter the UI and start recording the business rule and the associated event analysis as well as the filter and validation queries.

Installation of RuleGen is well described by the installation instructions. The full installation, including design time application and demo schema requires 12 steps – 11 involve running SQL scripts and one is the import of the APEX design time UI application. The entire installation took me just under 30 minutes.

Once the installation is done, there will be four schemas: design time repository, run time repository, APEX front end support and (demo) application schema. I will use the RuleGen APEX front end to record the rule:

The overview with all rule definitions is displayed:

Press the Create button to add a new rule. The Rule_Details page appears. Enter the abbreviation of the rule, a short description and the associated documentation. Then type the names of the columns that are involved in (the triggering and validation) of this rule. In our case, these are JOB and DEPTNO in table EMP and LOC in table DEPT. Press Save.

After saving the definition thusfar, RuleGen will present the list of TE-Queries. TE stands for Transition Effect. These queries select or filter the rows that trigger or require validation of the rule as a result of the DML statement against the table. RuleGen has parsed the list of involved columns. In this case, this results in two TE-Queries: one for each of the two tables involved.

The list of involved columns should also contain the columns whose value is returned by the TE-Query. Because the TE-Query will return the EMPNO value of each employee that should be checked or the DEPTNO value for each department that should be validated, I need to add these two columns to the list of Involved columns as well:

Now it is time to define the TE Queries, starting with the transition effect for DEPT. Click on the hyperlink for the TE Query for DEPT.

The Transition Effect Queries page appears. On it, we can enter three queries – one for each DML operation (Insert, Update, Delete). The result of this query is fed into the validation query as a means to select the rows to perform validation on. Only an update operation on DEPT should trigger validation of the rule – as was found in our earlier analysis. That analysis also showed that not just any update triggers validation, only an update of column LOC should have that effect. And only if the value of LOC after the update is AMSTERDAM, should the update of DEPT trigger validation of the rule. The TE query returns the values of DEPTNO for all department records for which that filter condition holds true: updated in the statement, with a changed LOC value and with AMSTERDAM as LOC value after the update.

Press Save and return to the Rule Details page.

RuleGen now lists the first (of two) CV-queries (Constraint Validation). This CV Query performs the actual rule validation, for the records returned by the Transition Effect query.

Let’s first define the Transition Effect queries for the DML operations on table EMP. Click on the TE-Query hyperlink for EMP.

The TE Queries for EMP will in this case return the empno values for those employees that require validation as a result of the transaction against EMP. That means that we also need a second CV-Query – the first one has deptno values for its input and will report on a problematic department while the EMP related TE queries will report on employees who have the wrong job or the wrong department. That means in RuleGen terms that we are dealing with different violation case. We specify this by setting the value of Violation case to a different value than its default of 1. Note: it is also possible to have the EMP TE-queries return the distinct deptno for the departments that contain the employees that require validation. In that case, we could use the same CV Query for both angles – department and employee – and work with the same value for Violation case.

The TE Query for the insert operation returns the values of empno for all inserted employees with a job of SALESREP. Only these new employee could possibly violate the rule and should therefore be returned by the query in order to have them validated.

The TE Query for update also returns empno values; in this case for employees whose job has been changed to SALESREP or who are already a SALESREP and have moved to another department. Both changes could lead to a violation of the rule.

As was concluded before – deleting an employee will never cause this rule to be violated. Hence we define no Delete TE-query.

Press Save and return to the Rule Details page.

As was expected, RuleGen now lists two CV-Queries, one for each violation case. Click on the hyperlink for the first CV-Query- the one associated with table DEPT.

We enter the page for editing the Constraint Validation Query. This query returns either no records at all – when the rule has not been violated – or it returns a single row that contains the error message associated with the rule violation(s) that this query has found.

In this case, the TE-query produced deptno values, for the departments that should be validated. These values are injected by RuleGen into the CV-query as p_deptno (derived from the name of the query column aliases in the TE-query, with p_ appended). The central piece of the CV query is the subquery that selects a row (with meaningless value ‘x’) for a department with deptno equal to p_deptno when that department is located in AMSTERDAM and found upon joining with EMP to contain SALESREPs.

The outer query selects the associated error message if the subquery returns one or more rows (regardless of the contents of those rows). This message will be returned by RuleGen at runtime to be presented to an end user or to be converted by internationalization mechanisms in the application.

Press Save and return to the Rule Details page. Click on the hyperlink for the second CV Query – the one associated with DML operations on table EMP.

This CV Query is very similar (of course) to the previous one. In this case, RuleGen injects empno values for employees that should be checked. These values are available in a variable called p_empno. The subquery returns a record when the indicated employee is a SALESREP and is found to work in a department in AMSTERDAM. If the subquery returns a row, the CV query as a whole returns a single row containing the relevant error message.

Press Save to complete the definition of the CV Query. Return to the Rule Details page.

The definition of the Rule is complete – except for optional modifications in run time properties.

Configure additional characteristics of the rule

Rules have several attributes that determine how RuleGen deals with them at run time. These attributes include the validation time: should this rule be enforced at statement level or (deferred) at transaction level. Another attribute is the status of the rule: enabled or disabled or log only. The latter status means that rules will be validated but not enforced; any rule violation is logged. And whether or not violations are written to the log history is governed by the Logging attribute.

When the relevant changes have been made to the properties, these changes can be saved. At this point – the rule is ready to be generated and deployed.

Generate the rule implementation code and deploy the rule to the run time environment

Generation of the run time code to enforce this rule is done quite simply by pressing the RuleGen button:

When the generation is complete, the code definition and the generated components can be deployed to the target database schema. Pressing the Deploy button will do the deployment immediately. Using the button marked with Export RT, the SQL scripts required for deploying the RuleGen components for this rule into a target schema are downloaded as file. The file can be used to deploy to various (DTAP) environments and can be checked in to the source code repository.

After pressing Deploy, the following message should appear, indicating a successful deployment.

At this point, the oh so important business rule that states that SALESREPs should not work out of AMSTERDAM is in force. We will not be able to transfer SALESREPs to a department located in Amsterdam, or relocate a department that has SALESREPs to AMSTERDAM or change the job for an employee working in a department in Amsterdam to SALESREP.

The Proof of the Pudding

The final step on our journey is trying out the rule in the live database. If our claim is correct, then we should not be able to get around this rule. So for example this next insert statement should fail:

insert into emp
( empno, ename, job, born, hired, sgrade, msal, username, deptno)
values
( 8000, 'Henk', 'SALESREP', to_date('15-09-1961','DD-MM-YYYY'), TRUNC(sysdate), 5, 5000, 'HENK', 13)

given the fact that department 13 is located in AMSTERDAM:

And indeed, when I try to perform this insert, I immediately get the following error message:

Let’s see if I can work around this rule. I will first insert HENK into department 15, then transfer him to department 13:

Well, what if I insert Henk as a TRAINER instead of a SALESREP? And the promote him to SALESREP?

And again, I am outsmarted by RuleGen (well, and my own rule analysis).

One final approach I can think of: update the location of department 13 – from AMSTERDAM to ROTTERDAM. Then insert HENK. Then update the location of department 13 back from ROTTERDAM to AMSTERDAM:

And fortunately, that venues is blocked off as well. It seems that with regards to his rule our database is safe, robust and of assured integrity.

One feature of RuleGen is the logging monitor. This provides insight in all rule violations that have been found (and that had their logging property set):

Well behaved application would ensure that Action and Client Info have been set; this would allow us to perform a meaningful analysis of the occurrences of rule violations and potential application design flaws that cause them.

Transaction Level validation

One last aspect of RuleGen I would like to mention in this article: transaction level validation. The default behavior for Oracle Database constraints as well as for RuleGen rules is immediate enforcement – which means: if a statement violates the rule, the statement will fail. The consequence is that a transaction that is composed of:

1. update dept: move from AMSTERDAM to ROTTERDAM
2. insert SALESREP into emp in the department just moved to ROTTERDAM

will succeed, where as the same logical transaction with these steps in a different order:

1. insert SALESREP into emp into the department about to be moved to ROTTERDAM
2. update dept: move from AMSTERDAM to ROTTERDAM

will fail.

However, logically speaking: only at transaction commit time to we really absolutely need to enforce the rules. During the transaction, a temporary violation is acceptable. We can ask RuleGen – on a rule by rule basis if we so desire – to perform validation at commit time. Let’s see this in action.

First change the execution behavior for rule DPTEMP_1:

exec sp_rgr_set_check_moment( p_rule =>'DPTEMP_1', p_moment => 'DEFERRED')

Then attempt the transaction that first does the insert into department 13 when it is still in AMSTERDAM, then the move to ROTTERDAM. And then the commit to complete the transaction:

In case you think that this opens up a way to circumvent the rule – simply set to deferred validation, insert HENK into 13 and without moving 13 from AMSTERDAM to ROTTERDAM quickly perform the commit – you are disappointed (or gladdened, depending on your intentions):

Resources

RuleGen website – where you can find a trial download of the RuleGen framework: http://www.rulegen.com/.