When I was confronted with the possibilities of Rulegen it made me wonder: How can these rules
be 
tested. I have some experience using CodeTester but as the name says, it’s a code tester where rulegen builds rules in the database that can actually only be tested using SQL statements (insert, update, delete).
When I still want to use CodeTester to perform the actual testing I need to encapsulate the insert, update and delete statements in simple procedures. I don’t want to have all the values for the record as parameters, but I figured I can create a record based on the table layout in which I can put all my values. That way I can populate this record in the pre-execution code of the test and then have the insert (or update or delete) be called in the test itself. I have build my test set around a simple table and a simple rule to be evaluated.
The rule to be enforced is: We only allow a single PRESIDENT in the table at a certain time.
Consider the following table:
I built this rule in the RuleGen Front-End:
Details of rule ONEPRESIDENT
| Rule: |
ONEPRESIDENT |
| Description (short): |
Just one president allowed in the entire table… |
| Documentation: |
We don’t want to have more than one president in the emp table. We can have less than one president, but no more than that. This means we have to check when inserting or updating rows but not when deleting them. |
| Involved columns: |
DEMO.EMP.JOB |
Transition Effect Queries for Rule ONEPRESIDENT
| Description: |
Just one president allowed in the entire table… |
| Violation case: |
1 |
| Insert TE-query: |
select distinct ‘x’ as NO_PARAMS
from inserted_rows
where job = ‘PRESIDENT’ |
| Update TE-query: |
select distinct ‘x’ as NO_PARAMS
from updated_rows
where new_job=’PRESIDENT’
and old_job<>’PRESIDENT |
| Delete TE-query: |
select distinct ‘x’ as NO_PARAMS
from deleted_rows
where 0=1 |
Constraint Validation Query for Rule ONEPRESIDENT
| Description: |
Just one president allowed in the entire table… |
| Violation case: |
1 |
| Constraint validation query: |
select ‘At most one PRESIDENT allowed (found ‘||to_char(num_presidents)||’).’ as msg
from (select count(*) as num_presidents
from emp
where job=’PRESIDENT’)
where num_presidents > 1 |
I need to create a package which holds a record based on this table:
TYPE EMP_rt IS RECORD(
EMPNO EMP.EMPNO%type,
ENAME EMP.ENAME%type,
JOB EMP.JOB%type,
MGR EMP.MGR%type,
HIREDATE EMP.HIREDATE%type,
SAL EMP.SAL%type,
COMM EMP.COMM%type,
DEPTNO EMP.DEPTNO%type
);
And I need to create some procedures to perform the DML for me:
PROCEDURE ins;
PROCEDURE upd;
PROCEDURE del;
And the implementation of these procedures:
PROCEDURE ins
IS
BEGIN
— insert the values in the rec record into the table
INSERT INTO EMP (
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
) VALUES (
rec.EMPNO,
rec.ENAME,
rec.JOB,
rec.MGR,
rec.HIREDATE,
rec.SAL,
rec.COMM,
rec.DEPTNO
);
END ins;
PROCEDURE upd
IS
BEGIN
— update the values in the table with the values in the rec record
— based on the primary key
UPDATE EMP
SET
EMPNO = rec.EMPNO,
ENAME = rec.ENAME,
JOB = rec.JOB,
MGR = rec.MGR,
HIREDATE = rec.HIREDATE,
SAL = rec.SAL,
COMM = rec.COMM,
DEPTNO = rec.DEPTNO
WHERE
EMPNO = rec.EMPNO
;
END upd;
PROCEDURE del
IS
BEGIN
— remove a record from the table
— based on the primary key
DELETE FROM EMP
WHERE
EMPNO = rec.EMPNO
;
END del;
Now I can create a couple of tests in CodeTester (click the link to see the pre-execution code to be added in CodeTester):
- ins
[One President] Insert that violates the rule
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’PRESIDENT’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
emp_ctp.rec.ename := ‘BAREL’;
emp_ctp.rec.job := ‘PRESIDENT’;
emp_ctp.rec.mgr := null;
emp_ctp.rec.hiredate := to_date(’01-01-2008′,’MM-DD-YYYY’);
emp_ctp.rec.sal := 5000;
emp_ctp.rec.comm := null;
emp_ctp.rec.deptno := 10;
[One President] Insert that works
— Pre-execution code
— empty the table
delete from emp;
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
emp_ctp.rec.ename := ‘BAREL’;
emp_ctp.rec.job := ‘PRESIDENT’;
emp_ctp.rec.mgr := null;
emp_ctp.rec.hiredate := to_date(’01-01-2008′,’MM-DD-YYYY’);
emp_ctp.rec.sal := 5000;
emp_ctp.rec.comm := null;
emp_ctp.rec.deptno := 10;
- upd
[One President] Update that fails (promote to president)
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘BAREL’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’PRESIDENT’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
emp_ctp.rec.ename := ‘BAREL’;
emp_ctp.rec.job := ‘PRESIDENT’;
emp_ctp.rec.mgr := null;
emp_ctp.rec.hiredate := to_date(’01-01-2008′,’MM-DD-YYYY’);
emp_ctp.rec.sal := 5000;
emp_ctp.rec.comm := null;
emp_ctp.rec.deptno := 10;
[One President] Update that works (demote the president)
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘BAREL’,’PRESIDENT’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
emp_ctp.rec.ename := ‘BAREL’;
emp_ctp.rec.job := ‘MANAGER’;
emp_ctp.rec.mgr := null;
emp_ctp.rec.hiredate := to_date(’01-01-2008′,’MM-DD-YYYY’);
emp_ctp.rec.sal := 5000;
emp_ctp.rec.comm := null;
emp_ctp.rec.deptno := 10;
[One President] Update that works (promote to president)
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘BAREL’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
emp_ctp.rec.ename := ‘BAREL’;
emp_ctp.rec.job := ‘PRESIDENT’;
emp_ctp.rec.mgr := null;
emp_ctp.rec.hiredate := to_date(’01-01-2008′,’MM-DD-YYYY’);
emp_ctp.rec.sal := 5000;
emp_ctp.rec.comm := null;
emp_ctp.rec.deptno := 10;
- del
[One President] Delete that works (remove the manager)
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘BAREL’,’PRESIDENT’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 2;
[One President] Delete that works (remove the president)
— Pre-execution code
— empty the table
delete from emp;
— insert a president
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, ‘BAREL’,’PRESIDENT’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2, ‘KOPPELAARS’,’MANAGER’,NULL,TO_DATE (’01-01-2008′, ‘MM-DD-YYYY’),
5000,NULL,10);
— setup a record in the codetester package
emp_ctp.rec.empno := 1;
Of course I can create the code by hand every time I need it, but I don’t like doing the same thing over and over again. That is where CodeGen comes into play. Using this generation tool I can easily create the packages based on the table structure. (the ctp extension in the package name is for Code Tester Package)
script to create the package header:
CREATE OR REPLACE PACKAGE [objname]_ctp
IS
— Author : [thisuser]
— Created : [rightnow]
— Purpose : CodeTester Package for [objname]
— Public type declarations
TYPE [objname]_rt IS RECORD(
[foreach]col[between],
[colname] [objname].[colname]%type
[endforeach]
);
— Public variable declarations
rec [objname]_rt;
— Public function and procedure declarations
PROCEDURE ins;
PROCEDURE upd;
PROCEDURE del;
END [objname]_ctp;
script to create the package body:
CREATE OR REPLACE PACKAGE BODY [objname]_ctp
IS
— Function and procedure implementations
PROCEDURE initialization
IS
BEGIN
NULL;
END initialization;
PROCEDURE ins
IS
BEGIN
— insert the values in the rec record into the table
INSERT INTO [objname] (
[foreach]col[between],
[colname]
[endforeach]
) VALUES (
[foreach]col[between],
rec.[colname]
[endforeach]
);
END ins;
PROCEDURE upd
IS
BEGIN
— update the values in the table with the values in the rec record
— based on the primary key
UPDATE [objname]
SET
[foreach]col[between],
[colname] = rec.[colname]
[endforeach]
WHERE
[foreach]pkycol[between],
[colname] = rec.[colname]
[endforeach]
;
END upd;
PROCEDURE del
IS
BEGIN
— remove a record from the table
— based on the primary key
DELETE FROM [objname]
WHERE
[foreach]pkycol[between],
[colname] = rec.[colname]
[endforeach]
;
END del;
BEGIN
initialization;
END [objname]_ctp;
The full (generated) source code for the package is:
Header:
CREATE OR REPLACE PACKAGE EMP_ctp
IS
— Author : DEMO
— Created : May 31, 2008 9:18:50
— Purpose : CodeTester Package for EMP
— Public type declarations
TYPE EMP_rt IS RECORD(
EMPNO EMP.EMPNO%type,
ENAME EMP.ENAME%type,
JOB EMP.JOB%type,
MGR EMP.MGR%type,
HIREDATE EMP.HIREDATE%type,
SAL EMP.SAL%type,
COMM EMP.COMM%type,
DEPTNO EMP.DEPTNO%type
);
— Public variable declarations
rec EMP_rt;
— Public function and procedure declarations
PROCEDURE ins;
PROCEDURE upd;
PROCEDURE del;
END EMP_ctp;
Body:
CREATE OR REPLACE PACKAGE BODY EMP_ctp
IS
— Function and procedure implementations
PROCEDURE initialization
IS
BEGIN
NULL;
END initialization;
PROCEDURE ins
IS
BEGIN
— insert the values in the rec record into the table
INSERT INTO EMP (
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
) VALUES (
rec.EMPNO,
rec.ENAME,
rec.JOB,
rec.MGR,
rec.HIREDATE,
rec.SAL,
rec.COMM,
rec.DEPTNO
);
END ins;
PROCEDURE upd
IS
BEGIN
— update the values in the table with the values in the rec record
— based on the primary key
UPDATE EMP
SET
EMPNO = rec.EMPNO,
ENAME = rec.ENAME,
JOB = rec.JOB,
MGR = rec.MGR,
HIREDATE = rec.HIREDATE,
SAL = rec.SAL,
COMM = rec.COMM,
DEPTNO = rec.DEPTNO
WHERE
EMPNO = rec.EMPNO
;
END upd;
PROCEDURE del
IS
BEGIN
— remove a record from the table
— based on the primary key
DELETE FROM EMP
WHERE
EMPNO = rec.EMPNO
;
END del;
BEGIN
initialization;
END EMP_ctp;
After running the tests I get this result:
This means that RuleGen rules can be tested using CodeTester to run the tests and CodeGen to create a simple package around the table being tested. It will take some time to build all your business rules into the database, but like Toon says: That’s where they are supposed to be. It’s probably the only constant factor in a system. And, if you build the rules at table level, then it doesn’t matter what front-end you build against it and what tool you are using for this.It will also take some time to build all the test cases, but at least the will be repeatable. Please note that all other rules must be valid to get a correct result. Try to test only one rule at a time. If you add a rule to the RuleGen repository, it is possible that you have to change some of your existing test cases to reflect this change.
be 
tested. I have some experience using CodeTester but as the name says, it’s a code tester where rulegen builds rules in the database that can actually only be tested using SQL statements (insert, update, delete).
Nice writeup! A thought that crossed my mind was: could you use the TE-queries to somehow (further) generate the insert, update, delete testers (two each) that should fail or succeed?
Probably too difficult…
Toon, You should be very cautious about using the code you need to test to generate the test-code. It is very easy to duplicate a mistake in the logic used when coding the TE query into the code that should verify its correctness – thereby completely defeating the purpose. Perhaps the framework for testing could be generated, but only the generic elements in it (ins,upd, del procedures – that CodeGen helps with). The logic in the test should be hand coded from the same functional specification that was used to create the BR implementation.
Lucas