Testing Rulegen using Codetester

June 3, 2008 Database, Oracle, PL/SQL No Comments

When I was presented with the possibilities of Rulegen it made me wonder: How can these rulescodetest-logo be RuleGen_Logotested. Rulegen is a tool to create rules in the database by just typing in some SQL queries. Rulegen will then use this input to generate a couple of triggers and a package to enforce these rules. This way the rules will be enforced in the database, regardless of what front-end is being used to edit the data. It’s a bit like the CDM Ruleframe that is available with Oracle Designer. But, back to the question at hand: 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_Details

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 Rule_Details_Transition_Effect_Queries

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 Rule_Details_Constraint_Validation_Query

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;

 Code Tester Dashboard Now I can create a couple of testCode Tester Customization Codes in CodeTester (this is 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 Code Tester Builder
      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

Related Posts

About The Author

Patrick Barel

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog http://blog.bar-solutions.com/.