B Application Examples

B Application Examples

This appendix describes examples of applications using th e Expression Filter.

Active Application

In an active database system, the server performs some actions when certain criteria are met. For example, an application could monitor changes to data in a database table and react to these chan ges accordingly.

Consider the Car4Sale application described in Chapter 1. In this application, the Consume r table stores the information about consumers interested in buying used cars. In addition to the Consumer table described in Chapter 1, assume that there is an Inventory table that stores information about all the used cars availabl e for sale, as defined in the following example:

CREATE TABLE Inventory (Model VARCHAR2(20), Year NUMBER, Price NUMBER, Mileage NUMBER);

Now, you can design the application such that the system reacts to any changes made to the data in the Inventory table, by de fining a row trigger on the table:

CREATE TRIGGER activechk AFTER insert OR update ON Inventory FOR E ACH ROW DECLARE cursor c1 (ditem VARCHAR2) is SELECT CId, Phone FROM Consumer WHERE EVALUATE (Interest, ditem) = 1; d item VARCHAR2(200); BEGIN ditem := Car4Sale.getVarchar(:new.Model, :new.Year, :new.Price, :new.Mileage); for cur in c1(ditem) loop DBMS_OUTPUT.PUT_LINE(' For Model '||:new.Model||' Call '||cur.CId|| ' @ '||cur.Phone); end loop ; END; /

This trigger evaluates the expressions for every row inserted (or updated) into the Inventory tabl e and prints a message if a consumer is interested in the car. An Expression Filter index on the Interest column can spe ed up the query on the Consumer table.

Batch Evaluation of Expressions

To evaluate a set of expressions for a batch of data items, you can perform a simple join of the table storing data items and the table storing express ions. You can join the Consumer table with the Inventory table to determine the interest in each car, as sh own in the following example:

SELECT DISTINCT Inventory.Model, count(*) as Demand FROM Consumer, In ventory WHERE EVALUATE (Consumer.Interest, Car4Sale.getVarchar(Inventory.Model, Inventory.Year, Inventory.Price, Inventory.Mileage)) = 1 GROUP BY Inventory.Model ORDER BY Demand DESC;

The EVALUATE operator's join semantics can also be u sed to maintain complex N-to-M (many-to-many) relationships between data stored in multiple tables.

Resource Management

Consider an application that manages IT support resources based on the responsibilities (or dutie s) and the workload of each representative. In this application, the responsibilities of the representatives are captured as expressi ons defined using variables such as the priority of the problem, organization, and the environment.

Create a table named ITResource to store information about all the available representatives, as shown in the following example:

-- Create the object type and the attribute set for ticket description --
CREATE OR REPLACE TYPE ITTicket AS OBJECT (
                     Priority       NUMBER,
                       Environment    VARCHAR2(10),
                       Organization
 VARCHAR2(10));
/
BEGIN
  DBMS_EXPFIL.CREATE_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                   from_type =&
gt; 'Yes');
END;
/

-- Table storing expressions --
CREATE TABLE ITResource (RId        NUMBER,
                         Duties     V
ARCHAR2(100));

BEGIN
  DBMS_EXPFIL.ASSIGN_ATTRIBUTE_SET(attr_set => 'ITTicket',
                                   expr_tab =>
 'ITResource',
                                   expr_col => 'Duties');
END;
/

INSERT INTO ITResource (RId, Duties) VALUES
   (1
, 'Priority <= 2 and Environment = ''NT'' and Organization =
                                                   ''Research''');

I
NSERT INTO ITResource (RId, Duties) VALUES
   (2, 'Priority = 1 and (Environment = ''UNIX'' or Environment = ''LINUX'')
        and O
rganization = ''APPS''');

Create a table named ITProblem to store the problems filed, as shown in the follow ing example:

CREATE TABLE ITProblem (PId             NUMBER,
                        Description     IT
Ticket,
                        AssignedTo      NUMBER);

The AssignedTo column in the ITProblem table stores the identifier of the representative handling the problem.

Now, use the following UPDATE statement to assign all the previously unassigned problems to capable IT representatives:

UPDATE ITProblem p SET
AssignedTo =
             (SELECT RId FROM ITResource r
              WHERE EVALUATE(r.Duties, p.Description.getVarchar()) = 1
              and rownum < 2)
    WHERE AssignedTo IS NULL;

The previous UPDATE operation can benefit from an Expressi on Filter index defined on the Duties column of the Resource table.