Transcript Slides - UCLA Computer Science

CS 240A: Databases and Knowledge Bases
Introduction to Active Databases
Carlo Zaniolo
Department of Computer Science
University of California, Los Angeles
Notes From Chapter 2 of
Advanced Database Systems
by Zaniolo, Ceri, Faloutsos,
Snodgrass, Subrahmanian and
Zicari Morgan Kaufmann, 1997
Active Database Systems

An integrated facility for creating and executing
production rules from within a database system.

A typical database production rule:
when event
if condition
then action
Active Database Systems (Cont’)

Powerful and uniform mechanism for:
- Constraint enforcement
- Derived data maintenance
- Alerting
- Authorization checking
- Version management
- Resource management
- Knowledge management
Outline of Slides

Chapter 2: Syntax and Semantics
- A Relational Prototype: Starburst
- Two Relational Systems: Oracle and DB2
- Features and Products Overview
Outline of Slides (Cont’)

Chapter 3: Applications
- Applications of Active Rules
- Deriving Active Rules for Constraint Management
- Deriving Active Rules for View Maintenance
- Rules for Replication
- Rules for Workflow Management
- Business Rules

View Maintenance design using deductive rules
Outline of Slides (Cont’)

Chapter 4: Design Principles
- Properties of Active Rules and Rule
Analysis
- Rule Modularization
- Rule Debugging and Monitoring
- Rule Design: IDEA Methodology (pointer)
- Conclusion
A Relational Example: Starburst





Designed and built at IBM Almaden
Chief Engineer: Jennifer Widom
Syntax based on SQL
Semantics is set-oriented
- Triggering based on (arbitrary) sets of changes
- Actions perform (arbitrary) sets of changes
- Conditions and actions can refer to sets of
changes
Instructions: create, drop, alter, deactivate,
activate
Rule Creation
<Starburst-rule> ::= CREATE RULE <rule-name>
ON <table-name>
WHEN <triggering-operations>
[IF <SQL-predicate>]
THEN SQL-statements
[PRECEDES <rule-names>]
[FOLLOWS <rule-names>]
<triggering-operation> := INSERTED | DELETED |
UPDATED [(column-names>)]
Rule Creation (Cont’)

Triggering operations:
- inserted, deleted, updated, updated(c1,..,cn)

Condition: arbitrary SQL predicate

Actions: any database operations
insert, delete, update, select,
rollback, create table, etc.

Precedes and Follows: for rule ordering
Example Rules
Salary control rule:
CREATE RULE SalaryControl ON Emp
WHEN INSERTED, DELETED, UPDATED (Sal)
IF
(SELECT AVG (Sal) FROM Emp ) >100
THEN UPDATE Emp
SET Sal = .9 * Sal
Example Rules (Cont’)

High paid rule:
CREATE RULE HighPaid ON Emp
WHEN INSERTED
IF
EXISTS (SELECT * FROM INSERTED
WHERE Sal > 100)
THEN INSERT INTO HighPaidEmp
(SELECT * FROM INSERTED
WHERE Sal > 100)
FOLLOWS SalaryControl
____________________________________
Errata: ADS book has “FOLLOWS AvgSal”
Transition Tables

Logical tables storing changes that triggered rule

Can appear anywhere in condition and action

References restricted to triggering operations:
inserted
deleted
new-updated
old-updated
Rule Execution Semantics (1)
Rules processed at commit point of each
transaction
 Transaction's changes are initial triggering
transition
 Rules create additional transitions which may
trigger other rules or themselves
 Each rule looks at set of changes since last
considered
 When multiple rules triggered, pick one based on
partial ordering

Example of Rule Execution

Initial state:
Employee
Sal
Stefano
90
Patrick
90
Michael
110
Example of Rule Execution (Cont’)
Transaction inserts tuples:
(Rick, 150) and (John, 120)
Employee
Sal
Stefano
90
Patrick
90
Michael
110
Employee
Sal
Stefano
90
Patrick
90
Michael
110
Rick
150
John
120
Example of Rule Execution (Cont’)

Rule SalaryControl runs:
Employee
Sal
Employee
Sal
Stefano
90
Stefano
81
Patrick
90
Patrick
81
99
Michael
110
Michael
Rick
150
Rick
135
John
120
John
108
Rule Execution Semantics (2)

Rule SalaryControl runs again:
Employee
Sal
Employee
Sal
Stefano
81
Stefano
73
Patrick
81
Patrick
73
Michael
99
Michael
89
Rick
135
Rick
121
John
108
John
97
Rule Execution Semantics (2) (Cont’)
Rule Rule HighPaid runs eventually, and inserts into
HighPaid only one tuple:
Employee
Sal
Rick
121
Oracle

Supports general-purpose triggers, developed according
to preliminary documents on the SQL3 standard.

Actions contain arbitrary PL / SQL code.

Two granularities: row-level and statement-level.


Two types of immediate consideration: before and
after.
Therefore: 4 Combinations: BEFORE ROW
BEFORE STATEMENT
AFTER ROW
AFTER STATEMENT
Trigger Processing
1. Execute the BEFORE STATEMENT trigger.
2. For each row affected:
(a) Execute the BEFORE ROW trigger.
(b) Lock and change the row.
(c) Perform row-level referential integrity and
assertion checking.
(d) Execute the AFTER ROW trigger.
3. Perform statement-level referential integrity and
assertion checking.
4. Execute the AFTER STATEMENT trigger.
Syntax
<Oracle-trigger> ::= CREATE TRIGGER <trigger-name>
{BEFORE | AFTER} <trigger-events>
ON <table-name>
[[REFERENCING <references> ]
FOR EACH ROW
[ WHEN ( <condition> ) ]] <PL/SQL block>
<trigger event> ::= INSERT | DELETE | UPDATE
[ OF <column-names> ]
<reference> ::=
OLD AS <old-value-tuple-name> |
NEW AS <new-value-tuple-name>
Example Trigger in Oracle:
Reorder Rule
CREATE TRIGGER Reorder
AFTER UPDATE OF PartOnHand ON Inventory
WHEN (New.PartOnHand < New.ReorderPoint
FOR EACH ROW
DECLARE NUMBER X
BEGIN
SELECT COUNT(*) INTO X FROM PendingOrders
PL/SQL
WHERE Part = New.Part;
IF X=0 THEN
INSERT INTO PendingOrders
VALUES (New.Part, New.OrderQuantity, SYSDATE)
END IF;
END;
Example of Execution



Initial state of Inventory:
Part
PartOnHand
ReorderPoint
ReorderQuantity
1
2
3
200
780
450
150
500
400
100
200
120
PendingOrders is initially empty
Transaction(executed on October 10, 1996):
T1 : UPDATE Inventory
SET PartOnHand = PartOnHand - 70
WHERE Part = 1
PartOnHand for Part 1 is changed to 130 < ReorderPoint, thus
Reorder is triggered: it finds PendingOrders empty and thus inserts there
the tuple (1,100,1996-10-10)
Example of execution (Cont’)


Part
PartOnHand
ReorderPoint
ReorderQuantity
1
2
3
130
780
450
150
500
400
100
200
120
Another transaction (executed on the same day)
T2 : UPDATE Inventory
SET PartOnHand = PartOnHand - 60
WHERE Part >= 1
The trigger is executed upon all the tuples, and the
condition holds for parts 1 and 3, but a new order is issued
for part 3 only—this causes the insertion into
PendingOrders of tuple (3,120,1996-10-10)
DB2

Triggers for DB2 Common Servers defined at the
IBM Almaden Research center in 1996.

Influential on the SQL3 standard.

As in Oracle: either BEFORE or AFTER their
event, and either a row- or a statement-level
granularity.
DB2 : Syntax
<DB2-trigger> :: = CREATE TRIGGER <trigger-name>
{BEFORE | AFTER} <trigger-event>
ON <table-name>
[ REFERENCING <references> ]
FOR EACH {ROW | STATEMENT}
WHEN ( <SQL-condition> )
<SQL-procedure-statements>
<trigger-event> :: = INSERT | DELETE | UPDATE
[ ON <column-names> ]
<reference> :: =
OLD AS <old-value-tuple-name> |
NEW AS new-value-tuple-name |
OLDTABLE AS old-value-table-name |
NEWTABLE AS new-value-table-name
The Before Trigger
CREATE TRIGGER emp_trig
NO CASCADE BEFORE UPDATE OF salary ON emp
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW
WHEN (newrow.salary >1.5*oldrow.salary)
SET newrow.salary = 1.5*oldrow.salary;
Only three kinds of statement are allowed with before rules:
SET to modify the values of NEW
2. SIGNAL will nullify the action but not the transaction
3. VALUES used to call a function.
1.
For instance instead of SET we could have :
BEGIN ATOMIC
VALUES(log(‘excessive raise’, CURRENT TIMESTAMP, oldrow.empno));
SIGNAL SQLSTATE ‘70011’ (‘excessive raise for oldrow.empno’)
END ATOMIC
Semantics of DB2 Triggers

Before-triggers:

Used to detect error conditions and to condition input values
(assign values to NEW transition variables).

Read the database state prior to any modification made by the
event.

Cannot modify the database by using UPDATE, DELETE, and
INSERT statements (so they do not activate other triggers).

Several triggers (with either row- or statement-level
granularity) can monitor the same event.

A system-determined total order takes into account the
triggers’ definition time; row- and statement-level triggers
are intertwined in the total order.
Semantics of DB2 Triggers (Cont’)

General trigger processing algorithm after statement A :
1. Suspend the execution of A, and save its working storage
on a stack.
2. Compute transition values (OLD and NEW) relative to
event .
3. Consider and execute all before-triggers relative to event E,
possibly changing the NEW transition values.
4. Apply NEW transition values to the database, thus making
the state change associated to event E effective.
5. Consider and execute all after-triggers relative to event E.
If any of them contains an action AI that activates other
triggers, then invoke this processing procedure recursively
for AI .
6. Pop from the stack the working storage for A and continue
its evaluation.
Semantics of DB2 Triggers (Cont’)
Revised trigger processing with integrity checking:
4 Apply the NEW transition values to the database, thus making
the state change associated to event E effective. For each
integrity constraint IC violated by the current state change, let
Aj be the compensating action (if any) specified with IC, then
[a.] Compute the transition values (OLD and NEW) relative to Aj .
[b.] Execute the before-triggers relative to Aj ,, possibly changing
the NEW transition values.
[c.] Apply NEW transition values to the database, thus making the
state change associated to Aj effective.
[d.] Push all after-triggers relative to action into a queue of
suspended triggers.
Until a quiescent point is reached where all the integrity constraints
violated in the course of the computation are compensated.
Examples of Triggers on
Part(PartNum, Supplier, Cost)
Supplier rule:
CREATE TRIGGER OneSupplier
BEFORE UPDATE OF Supplier
ON Part
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Supplier IS NULL)
SIGNAL SQLSTATE '70005'
('Cannot change supplier
to NULL')
Audit rule:
CREATE TRIGGER Audit
AFTER UPDATE ON Part
REFERENCING OLDTABLE
AS OT
FOR EACH STATEMENT
INSERT INTO AuditSupplier
VALUES(USER,
CURRENTDATE, (SELECT
COUNT(*) FROM OT))
Example (cont.)


SIGNAL: rolls back the effect of a statement, while letting
the transaction proceed.
SIGNAL and SET are often used with before triggers.
Part
PartNo
1
2
3
4
Supplier
Jones
Taylor
HDD
Jones
Distributor
Cost
150
500
400
800
FOREIGN KEY (Supplier)
REFERENCES Distributor
ON DELETE SET DEFAULT
Supplier
Jones
Taylor
HDD
State
City
Palo Alto CA
Newark NJ
Atlanta
GE
Say that the
default value
is HDD …
Example (cont.)
Part
PartNo
1
2
3
4
Supplier
Jones
Taylor
HDD
Jones
Cost
150
500
400
800
Distributor
Supplier
State
City
Jones
Palo Alto CA
Taylor
Newark NJ
HDD
Atlanta
GE
FOREIGN KEY (Supplier)
REFERENCES Distributor
ON DELETE SET DEFAULT (i.e. HDD
)
CREATE TRIGGER OneSupplier
BEFORE UPDATE OF Supplier ON
Part
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Supplier IS NULL)
SIGNAL SQLSTATE ‘70005 (…)
CREATE TRIGGER Audit
AFTER UPDATE ON Part
REFERENCING OLDTABLE AS OT
FOR EACH STATEMENT
INSERT INTO AuditSupplier VALUES(USER,
CURRENTDATE, (SELECT COUNT(*) FROM OT))
DELETE FROM Distributor WHERE State =`CA’
Execution
1.
Event:
DELETE FROM Distributor
WHERE State =`CA’
2.
No rule is specified for this event, but the FK constraint updates
Jones with HDD in the first and last tuple of Part the OLD and NEW
tables for Part are set accordingly.
3.
The before rule is evaluated on the updates of point 2, but it is not
fired since the WHEN condition fails.
4.
The updates of point 2 are written onto the actual table Distributor
(well, the in-memory copy, that will be then written to disk at
transaction commit time).
5.
It is now the turn of the after rules. The AuditSupplier rule is
triggered, which adds to the Audit table the tuple:
(Bill, 1996-10-10, 2)
Question--homework
What happens if we change the FK corrective action
to:
FOREIGN KEY (Supplier)
REFERENCES Distributor
ON DELETE SET NULL
Given the DB below, What happen when the user issues the following:
DELETE FROM Distributor WHERE State =`CA’
Part
PartNo
1
2
3
4
Supplier
Jones
Taylor
HDD
Jones
Cost
150
500
400
800
Distributor
Supplier
State
City
Jones
Palo Alto CA
Taylor
Newark NJ
HDD
Atlanta
GE
FOREIGN KEY (Supplier)
REFERENCES Distributor
ON DELETE SET NULL
CREATE TRIGGER OneSupplier
BEFORE UPDATE OF Supplier
ON Part
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.Supplier IS NULL)
SIGNAL SQLSTATE …
CREATE TRIGGER Audit
AFTER UPDATE ON Part
REFERENCING OLDTABLE AS OT
FOR EACH STATEMENT
INSERT INTO AuditSupplier VALUES(USER,
CURRENTDATE, (SELECT COUNT(*) FROM OT))
Taxonomy of ECAs

Events and Conditions: DB changes, Retrievals, Time
related events, composite events, application defined …

Actions: DB changes, messages, stored procedures,
grant privileges, activate rules, arbitrary programs

Consideration and Execution:

Immediate Before

Immediate After

Deferred (till the end of transaction)

Detached (in another transaction)

Granularity: row or statement

SQL:1999 attempts to put a limit on these many
alternatives. Still semantics is very complex.
Logical Complexity

The XCON experience: large rules sets supporting
a complex application---unmanageable.

Use ECAs only in rather simple and well
understood applications (Ch 3)

Static analysis to predict and limit dynamic
interactions between rules and recursive triggering
(Ch 4).