Rem Rem $Header: ruldemo.sql 15-nov-2004.09:08:27 ayalaman Exp $ Rem Rem ruldemo.sql Rem Rem Copyright (c) 2004, Oracle. All rights reserved. Rem Rem NAME Rem ruldemo.sql - Rules Manager demo script Rem Rem DESCRIPTION Rem This script demonstrates the use of Rules Manager in Rem a few configurations. See the Application Developer's Rem Guide - Rules Manager and Expression Filter for Rem additional information. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem ayalaman 11/15/04 - XPath expression example and rule descriptions Rem ayalaman 11/02/04 - replace homeland security with law enforcement Rem ayalaman 10/07/04 - ayalaman_bug-3922526 Rem ayalaman 09/29/04 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 connect / as sysdba; set serveroutput on; drop user ruldemo cascade; create user ruldemo identified by ruldemo; grant connect, resource to ruldemo; grant create view to ruldemo; grant execute on dbms_lock to ruldemo; connect ruldemo/ruldemo; set echo on; set pagesize 150; column mesg format a78; column attime format a30; column rlm$rulecond format a95; column rlm$ruleid format a5; /***************************************************************** The tests in this file are organized as follows: I. Application demonstrating the various steps in a Rules Manager application. II. Application demonstrating the use of Table aliases and DML (Insert) Events for rule management. III. Application demonstrating the use of ADD_EVENT API and Rule class results view ******************************************************************/ set linesize 110; --- --- I. Application demostrating the various steps in a Rules --- Manager application. --- --- The following script uses Law Enforcement application to demonstrate --- the use of Rules Manager. In this application, rules are defined to --- raise security alerts, place a person on the watch list, etc based --- on certain criteria. For this purpose, some real-world events such as --- Bank Transactions, Transportation and Field Reports are used --- to describe the criteria. --- create table messagequeue (attime timestamp, mesg varchar2(4000)); --- --- 1. Create the basic types that represent the primitive event --- structures --- create or replace type BankTransaction as object (subjectId NUMBER, --- refer to entity such as personnel --- could be SSN tranType VARCHAR2(30), --- DEPOSIT / TRANSFER / WITHDRAW amount NUMBER, --- fundFrom VARCHAR2(30)); --- location from which it is transfered / create or replace type Transportation as object (subjectId NUMBER, vesselType VARCHAR2(30), --- TRUCK / CAR / PLANE / TRAIN locFrom VARCHAR2(30), --- starting location locTo VARCHAR2(30), --- ending location startDate DATE, --- start date endDate DATE); --- end date of / create or replace type FieldReport as object (subjectId NUMBER, rptType VARCHAR2(30), --- Tel call / Meeting / Bg Check whoWith NUMBER, --- identifier of the person the subject --- is in touch with rptOrg VARCHAR2(30), --- Organization reporting it rptReg VARCHAR2(30), --- Region rptBody sys.XMLType); --- the actual report / --- --- 2. Create a composite event type that consists of these basic types. --- create or replace type LawEnforcement as object (bank BankTransaction, transport Transportation, fldrpt FieldReport); / --- --- 3. Create a repository for the rules defined on the composite event --- structure --- BEGIN DBMS_RLMGR.CREATE_RULE_CLASS( rule_class => 'LawEnforcementRC', event_struct => 'LawEnforcement', action_cbk => 'LawEnforcementCBK', actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)', rslt_viewnm => 'MatchedCriteria', rlcls_prop => ''); END; / --- The above steps create a relational table, LawEnforcementRC, which acts --- as the repository for the rules in this application. This table has --- a set of pre-defined columns to store the Rule identifiers, Rule --- conditions and the descriptions. In addition to these columns, this --- rule class table is defined with two columns, actionType and --- actionParam, as specified through the actpref_spec argument. These --- columns capture the type of action that should be carried for each --- rule. desc LawEnforcementRC; --- The above steps also creates the skeleton for an action callback --- procedure with the specified name. select text from user_source where name = 'LAWENFORCEMENTCBK' order by line; --- --- 4. Implement the callback procedure to perform appropriate action --- for each matching rule, based on the event instances that --- matched the rule and the action preferences associated with --- the rule. --- CREATE OR REPLACE PROCEDURE LAWENFORCEMENTCBK ( bank banktransaction, transport transportation, fldrpt fieldreport, rlm$rule LawEnforcementRC%ROWTYPE) IS mesg VARCHAR2(4000); msgl VARCHAR2(100); begin msgl := 'Rule '||rlm$rule.rlm$ruleid||' matched following primitive events'; dbms_output.put_line(msgl); mesg := msgl||chr(10); if (bank is not null) then msgl := '->Bank Transaction by subject ('||bank.subjectId||') of type ['||bank.tranType||']'; dbms_output.put_line(msgl); mesg := mesg||msgl||chr(10); end if; if (transport is not null) then msgl := '->Transportation by subject('||transport.subjectId||') use vessel ['||transport.vesselType||']'; dbms_output.put_line(msgl); mesg := mesg||msgl||chr(10); end if; if (fldrpt is not null) then msgl := '->Field report refer to('||fldrpt.subjectId||' and '||fldrpt.whowith||')'; dbms_output.put_line(msgl); mesg := mesg||msgl||chr(10); end if; msgl := '=>Recommended Action : Action Type ['||rlm$rule.actionType|| '] Action Parameter ['||rlm$rule.actionParam||']'; dbms_output.put_line(msgl||chr(10)); mesg := mesg||msgl||chr(10); insert into messagequeue values (systimestamp, mesg); end; / show errors; --- --- 5. The rules defined in the rule class can make use of user-defined --- functions in the database schema. The following commands create --- some dummy functions that are later used in the rule conditions. --- -- -- For the value of the region passed in, query the restricted -- areas table and return 1 if the current region is a restricted -- area. CREATE OR REPLACE FUNCTION IsRestrictedArea(region VARCHAR2) RETURN NUMBER IS BEGIN -- User can expand this function and implement a logic that -- relies on other relational tables. RETURN 1; END; / -- -- Check if the subject chosen is on the watch list -- CREATE OR REPLACE FUNCTION OnWatchList(subject NUMBER) RETURN NUMBER IS BEGIN -- User can expand this function and implement a logic that -- relies on other relational tables. RETURN 1; END; / -- -- Verify if two parties are associates. Return 1 is the two -- subjects passed in are associates according to the registry. -- CREATE OR REPLACE FUNCTION AreAssociates(subjectA NUMBER, subjectB NUMBER) RETURN NUMBER IS BEGIN -- User can expand this function and implement a logic that -- relies on other relational tables. RETURN 1; END; / -- Add all three user-defined functions to composite event -- "LawEnforcement", so that these functions can be used -- in the corresponding rule conditions. EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'OnWatchList'); EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'IsRestrictedArea'); EXEC DBMS_RLMGR.ADD_FUNCTIONS('LawEnforcement', 'AreAssociates'); --- --- 6. Define the rules that suggest some actions --- --- Rule : Add a person to the NYPD watch list if he recieves a money --- transfer for more than 10000 dollars and if he rents a truck, --- one-way to one of the restricted areas. Note that the join --- predicate is specified at the rule class elevel. --- INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond, rlm$ruledesc) VALUES ('1', 'ADD2WATCHLIST','NYPD', ' tranType = ''TRANSFER'' AND amount > 10000 AND fundFrom != ''usa'' vesselType = ''TRUCK'' AND locFrom != locTo AND IsRestrictedArea(locTo)=1 ', 'Add a person to the NYPD watch list if he recieves a money transfer for more than 10000 dollars and if he rents a truck one-way to one of the restricted areas'); --- --- Rule : Add a person to the NYPD watch list if 2 out of the following 3 --- conditions are met : --- * The person get a money transfer for over 10000 dollars from outside USA --- * He rented a truck, one-way, into one of the restricted areas and --- * He had a phone conversation with a person alreadt on the watch list. --- --- The following rule demonstrates the use of ANY where a rule condition is --- considered true is m out of n events are detected. --- INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond, rlm$ruledesc) VALUES ('2', 'ADD2WATCHLIST','NYPD', ' tranType = ''TRANSFER'' AND amount > 10000 AND fundFrom != ''usa'' vesselType = ''TRUCK'' AND locFrom != locTo AND IsRestrictedArea(locTo)=1 rptType = ''TELCALL'' AND OnWatchList(whoWith) = 1 ', 'Add a person to the NYPD watch list if 2 out of the 3 specified conditions are met'); --- --- Rule : Start a background check on a person if he receives a large --- sum of money from outside USA, he rents a truck one-way into one of --- the restricted areas and there is no field report with his background --- information. --- --- The following rule demonstrates the use of negation where a rule condition --- is considered true if some of the specified events are detected and the --- other events are not detected --- INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond, rlm$ruledesc) VALUES ('3','STARTBACKGROUNDCHECK','RENTAL_DESTINATION', ' tranType = ''TRANSFER'' AND amount > 10000 AND fundFrom != ''USA'' vesselType=''TRUCK'' AND locFrom != locTo AND IsRestrictedArea(locTo)=1 rptType = ''BG Check'' ', 'Start a background check on a person if he receives a large sum of money from outside USA, he rents a truck one-way into one of restricted areas and there is no field report with his background information'); --- Rule : If a subject received over 10000 dollars from outside US, --- he rented a truck for one direction into a restricted area and a --- field report saying that the subject was never arrested before was --- not submitted within "certain" (0.001 fraction of a day; could be --- days. But seconds are used to demonstrate the use of deadline) --- period, add the destination of the truck to high-risk areas --- --- --- This rule demonstrates the use of Negation with a deadline. --- INSERT INTO LawEnforcementRC (rlm$ruleid, actionType, actionParam, rlm$rulecond, rlm$ruledesc) VALUES ('4','ADD2HIGH_RISK_AREA','RENTAL_DESTINATION', ' tranType = ''TRANSFER'' AND amount > 10000 AND fundFrom != ''usa'' vesselType = ''TRUCK'' AND locFrom != locTo AND IsRestrictedArea(locTo)=1 rptType = ''BACKGROUNDCHECK'' and extract(rptBody, ''/history/arrests[@number=0]'') is not null ', 'If a subject received over 10000 dollars from outside US, he rented a truck for one direction into a restricted area and a field report saying that the subject was never arrested before was not submitted within "certain" period, add the destination of the truck to high-risk areas'); commit; --- Browse the rules --- select rlm$ruleid, rlm$rulecond from LawEnforcementRC order by 1; --- --- 7. Process the rules for the primitive events --- set serveroutput on size 10000; BEGIN dbms_rlmgr.process_rules ( rule_class => 'LawEnforcementRC', event_inst => sys.anydata.convertobject( fieldreport(123302122, 'TELCALL',123302123, 'NSA', 'NE', null))); END; / --- The following event partially matches some of the rules defined above --- BEGIN dbms_rlmgr.process_rules ( rule_class => 'LawEnforcementRC', event_inst => sys.anydata.convertobject( banktransaction(123302122, 'TRANSFER', 100000, 'USSR'))); END; / --- This event in combination with the other event evaluates some of the --- rules to true and thus calls the action call-back procedure. BEGIN dbms_rlmgr.process_rules ( rule_class => 'LawEnforcementRC', event_inst => sys.anydata.convertobject( transportation(123302122, 'TRUCK', 'WIS', 'MD', sysdate, sysdate + 7))); END; / select mesg from messagequeue order by attime; truncate table messagequeue; --- Let us sleep past the deadline for rule 5. The scheduler process picks up --- this rule and executes its action. The result is a new message in the --- message queue. exec dbms_lock.sleep(180); --- The action is executed for the rule 5 after the deadline time is elapsed. select mesg from messagequeue; exec dbms_rlmgr.drop_rule_class('LawEnforcementRC'); exec dbms_rlmgr.drop_event_struct('LawEnforcement'); drop type LawEnforcement; exec dbms_rlmgr.drop_event_struct('BankTransaction'); drop type BankTransaction; exec dbms_rlmgr.drop_event_struct('Transportation'); drop type Transportation; exec dbms_rlmgr.drop_event_struct('FieldReport'); drop type fieldreport; /*************************************************************************/ --- --- II. Application demonstrating the use of Table aliases and --- DML (Insert) Events for rule management. --- --- Order Management application to demonstrate the use of Table --- Aliases and DMLEVENTS with rules. -- column attime format a30; column mesg format a50; set echo on; set pagesize 150; set linesize 150; --- --- The following script uses Order Management application to demonstrate --- the use of Rules Manager for the (event) data that is stored in --- Relational tables. --- --- --- Let us consider 3 relational tables that store the information --- about the Purchase Orders, Shipping information, and Payment --- information. --- create table PurchaseOrders (orderId NUMBER, custId NUMBER, itemId NUMBER, itemType VARCHAR2(30), quantity NUMBER, shipBy DATE); create table ShipmentInfo (orderId NUMBER, destState VARCHAR2(2), address VARCHAR2(50), shipTime DATE, shipType VARCHAR2(10)); create table PaymentInfo (orderId NUMBER, payType VARCHAR2(10), -- Credit Card / Check -- amountPaid NUMBER, pymtTime DATE, billState VARCHAR2(2)); --- --- 1. Create the event structure. --- The event structures that refer to existing table using --- table alias constructs cannot be created from object types. --- Instead, such event structures should be modeled as --- Expression Filter attribute sets. --- begin DBMS_RLMGR.CREATE_EVENT_STRUCT (event_struct => 'OrderMgmt'); DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE( event_struct => 'OrderMgmt', attr_name => 'po', tab_alias => RLM$TABLE_ALIAS('PurchaseOrders')); DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE( event_struct => 'OrderMgmt', attr_name => 'si', tab_alias => RLM$TABLE_ALIAS('ShipmentInfo')); DBMS_RLMGR.ADD_ELEMENTARY_ATTRIBUTE( event_struct => 'OrderMgmt', attr_name => 'py', tab_alias => RLM$TABLE_ALIAS('PaymentInfo')); end; / --- --- 2. Create the rule class (repository for rules) for the --- OrderMgmt composite event. Also specify the DMLEVENTS property --- to process the rules for each Inserted row into the (event) data --- tabled. --- BEGIN DBMS_RLMGR.CREATE_RULE_CLASS ( rule_class => 'OrderMgmtRC', event_struct => 'OrderMgmt', action_cbk => 'OrderMgmtCBK', actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)', rslt_viewnm => 'MatchingOrders', rlcls_prop => ''); END; / desc OrderMgmtCBK; --- --- 3. Implement the action callback procedure to --- CREATE OR REPLACE PROCEDURE OrderMgmtCBK ( po ROWID, -- rowid from the PurchaseOrders table si ROWID, -- rowid from the ShipmentInfo table py ROWID, -- rowid from the PaymentInfo table rlm$rule OrderMgmtRC%ROWTYPE) IS ordId NUMBER; msg VARCHAR2(2000); begin -- the rowid arguments represent the primitive events that are -- rows inserted into the corresponding tables. Use the rowids -- to fetch necessary values. if (po is not null) then select orderId into ordId from PurchaseOrders where rowid = po; elsif (si is not null) then select orderId into ordId from ShipmentInfo where rowid = si; elsif (py is not null) then select orderId into ordId from PaymentInfo where rowid = py; end if; msg := 'Order number: '||ordId||' Matched rule: ' ||rlm$rule.rlm$ruleid||chr(10)|| '-> Recommended Action : '||chr(10)|| ' Action Type ['||rlm$rule.actionType|| ']'||chr(10)||' Action Parameter ['|| rlm$rule.actionParam||']'; dbms_output.put_line (msg||chr(10)); end; / --- --- 4. Add User-Defined functions that may be useful in rule --- conditions. --- create or replace function getCustType(custId number) return VARCHAR2 is begin -- the actual function implementation can rely on other -- relational tables to derive the customer type information return 'GOLD'; end; / exec DBMS_RLMGR.ADD_FUNCTIONS('OrderMgmt','getCustType'); --- --- 4. Add some rules --- --- Rule : If the order is for more than 100 routers and the --- payment is received as a check, contact the customer --- to update on the status of the order. --- Note that the join predicate across event types is specified --- at the rule class level --- INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond) VALUES (1, 'CALL_CUSTOMER','UPDATE_ORDER_STATUS', ' itemType = ''ROUTER'' and quantity > 100 payType = ''CHECK'' '); --- Rule : If the order is placed by a GOLD customer, --- and the items are shipped before receiving a payment, --- adjust the customer's credit. INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond) VALUES (2, 'UPDATE_CUST_PROFILE', 'DECR_AVAILABLE_CREDIT', ' getCustType(custid) = ''GOLD'' '); --- Rule : If the order is place by a Gold customer and the item --- item is shipped within 1 day prior to the shipby date, --- increment the Quality of service statistics. --- INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$rulecond) VALUES (3, 'UPDATE_STATISTICS', 'INCREMENT QOS', ' getCustType(custid) = ''GOLD'' '); commit; set serveroutput on; insert into PurchaseOrders (orderId, custId, itemId, itemType, quantity, shipBy) values (1, 123, 234, 'ROUTER', 120, '01-OCT-2004'); insert into ShipmentInfo (orderId, deststate, address, shipTime, shipType) values (1, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air'); insert into PaymentInfo (orderId, paytype, amountpaid, pymttime, billstate) values (1, 'CHECK', 100000, '30-SEP-2004', 'CA'); commit; --- --- III. Application demonstrating the use of ADD_EVENT API and --- Rule class results view --- --- Now let us consider a similar application without the use of --- DMLEVENTS. This implies that the user explicitly invokes the --- rules manager APIs to process the rules for some data stored --- in relational tables. --- --- We can share the same event structure for another rule class. --- BEGIN DBMS_RLMGR.CREATE_RULE_CLASS ( rule_class => 'OrderMgmtRC2', event_struct => 'OrderMgmt', action_cbk => 'OrderMgmtCBK2', actprf_spec => 'actionType VARCHAR2(40), actionParam VARCHAR2(100)', rslt_viewnm => 'MatchingOrders2', rlcls_prop => ''); END; / --- Implement the action callback procedure -- CREATE OR REPLACE PROCEDURE OrderMgmtCBK2 ( po ROWID, -- rowid from the PurchaseOrders table si ROWID, -- rowid from the ShipmentInfo table py ROWID, -- rowid from the PaymentInfo table rlm$rule OrderMgmtRC2%ROWTYPE) IS ordId NUMBER; msg VARCHAR2(2000); begin -- the rowid argument represent the primitive events that are -- rows inseted into the corresponding tables. Use the rowids -- to fetch necessary values. if (po is not null) then select orderId into ordId from PurchaseOrders where rowid = po; elsif (si is not null) then select orderId into ordId from ShipmentInfo where rowid = si; elsif (py is not null) then select orderId into ordId from PaymentInfo where rowid = py; end if; msg := 'Order number: '||ordId||' Matched rule: ' ||rlm$rule.rlm$ruleid||chr(10)|| '-> Recommended Action : '||chr(10)|| ' Action Type ['||rlm$rule.actionType|| ']'||chr(10)||' Action Parameter ['|| rlm$rule.actionParam||']'; dbms_output.put_line (msg||chr(10)); end; / --- insert the same set of rules into the new rule class. insert into OrderMgmtRC2 (select * from OrderMgmtRC); delete from OrderMgmtRC; commit; --- Since DML events are not configured for this rule class, --- the application has to explicitly process rules for the --- rows in the data table. The rowids of the rows are used --- as references to the events. var datarid varchar2(40); insert into PurchaseOrders (orderId, custId, itemId, itemType, quantity, shipBy) values (2, 123, 234, 'ROUTER', 120, '01-OCT-2004') returning rowid into :datarid; BEGIN dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2', event_type => 'PurchaseOrders', event_inst => :datarid); END; / insert into ShipmentInfo (orderId, deststate, address, shipTime, shipType) values (2, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air') returning rowid into :datarid; BEGIN dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2', event_type => 'ShipmentInfo', event_inst => :datarid); END; / insert into PaymentInfo (orderId, paytype, amountpaid, pymttime, billstate) values (2, 'CHECK', 100000, '30-SEP-2004', 'CA') returning rowid into :datarid; BEGIN dbms_rlmgr.process_rules (rule_class => 'OrderMgmtRC2', event_type => 'PaymentInfo', event_inst => :datarid); END; / -- -- Now try the session oriented evaluation of rules where the -- results from matching rules are available in the results view -- to be queried. -- set linesize 80; desc MatchingOrders2; select count(*) from MatchingOrders2; insert into PurchaseOrders (orderId, custId, itemId, itemType, quantity, shipBy) values (3, 123, 234, 'ROUTER', 120, '01-OCT-2004') returning rowid into :datarid; --- Use ADD_EVENT API in the place of PROCESS_RULES --- BEGIN dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2', event_type => 'PurchaseOrders', event_inst => :datarid); END; / insert into ShipmentInfo (orderId, deststate, address, shipTime, shipType) values (3, 'CA','1 Main street, San Jose','29-SEP-2004','1 Day Air') returning rowid into :datarid; BEGIN dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2', event_type => 'ShipmentInfo', event_inst => :datarid); END; / insert into PaymentInfo (orderId, paytype, amountpaid, pymttime, billstate) values (3, 'CHECK', 100000, '30-SEP-2004', 'CA') returning rowid into :datarid; BEGIN dbms_rlmgr.add_event (rule_class => 'OrderMgmtRC2', event_type => 'PaymentInfo', event_inst => :datarid); END; / --- Since the event structure is configired with table aliases, --- the events are represented using the rowids from the --- corresponding tables. column rlm$ruleid format a7; column actiontype format a25; column actionparam format a25; select po, si, py, rlm$ruleid, actionType, actionParam from MatchingOrders2 order by 4; select (select orderId from purchaseOrders where rowid = po) as OrderId, rlm$ruleid, actionType, actionParam from MatchingOrders2 order by 2; connect / as sysdba; drop user ruldemo cascade; exit;