Transcript Active database concepts

Active database concepts
Active database concepts
1
Active database concepts
Introduction
triggering event
DB
triggered action
think of examples
2
Active database concepts
Rules
 specify actions that are automatically triggered by certain
events
 generalised model: EVENT-CONDITION-ACTION (ECA)
 event(s): database update operation(s)
 condition: determines whether the rule is to be executed after the
event occurred
 action: action to be taken
• SQL command(s)
• external program
 rules are sometimes called triggers
• however, some systems make distinctions between them (Postgres)
3
Active database concepts
Example - pseudocode
consider the following relations:
Employee ( Id, Name, Address, …, Salary, Dept, …)
Department (Dept, Name, …, Tot_Sal, …)
“Tot_Sal” represents the salary of all the employees
in the department
4
Active database concepts
Insert new employee
CREATE RULE
Totsal1
AFTER INSERT ON Employee
FOR EACH ROW
WHEN ( NEW.Dept IS NOT NULL)
UPDATE Department
SET
Tot_Sal = Tot_Sal + NEW.Salary
WHERE Dept = NEW.Dept ;
5
Active database concepts
Activity
 design similar rules for the following cases:
 updating the salary of certain employees
 deleting certain employees
6
Active database concepts
Observation
 the situation in the previous example should not
occur in an “industrial” database
 the total salary per department can be computed from the
individual salary of each employee, therefore should not be
explicitly represented
 the example was used for didactic reasons
 however, could you think of real life situations where such a
design would be useful?
7
Active database concepts
Example - more realistic
Employee ( Id, Name, …, Salary, Manager, …)
(1)if the salary of the manager is increased then
increase the salary of all the employees that are
supervised by this manager with 10% of the
manager’s increase
(2)if the salary of an employee is increased by more
than 10% inform the general manager
8
Active database concepts
Increase salary
CREATE RULE
IncreaseSal
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
UPDATE Employee
SET
Salary =
Salary + (NEW.Salary - OLD.Salary) * 0.1
WHERE Manager = NEW.Id ;
9
Active database concepts
Inform general manager
CREATE RULE
InformSal
AFTER UPDATE OF Salary ON Employee
FOR EACH ROW
WHEN ( NEW.Salary - OLD.Salary) / OLD.Salary > 0.1
Inform_general_manager(NEW.Name, NEW.Salary);
--the action is an external procedure
10
Active database concepts
Issues about active databases
 general model (remember): ECA
 event
• in general INSERT, UPDATE, DELETE
• can be more specific (see “update of salary on employee”)
 condition
• could be empty when solely the event triggers
• row-level rule: the rule is triggered for each tuple for which the
condition is satisfied
• statement-level rule: the rule is triggered only once for each
triggering statement
 action
• SQL statement or external procedure
11
Active database concepts
Row level and statement-level rule
 Safe_box(ID, Name, …, Value, …)
 statement level
• inform the manager whenever items are removed from the safe
 tuple level
• inform the manager for every item more expensive than £1000
removed from the safe
12
Active database concepts
Statement vs row level rules
--statement level
CREATE RULE
InformManager
BEFORE DELETE ON Safe-box
Inform_general_manager();
--row level
CREATE RULE
InformManager
BEFORE DELETE ON Safe-box
FOR EACH ROW
WHEN OLD.Value > 1000
Inform_general_manager(OLD.Id, OLD.Value);
13
Active database concepts
Further issues about active databases
 activate / deactivate rules + sets of rules
 allows for better customisation
 rule consideration - condition evaluation
• see diagram next slide
 immediate: evaluated as part of the triggering transaction (before,
after or instead of executing the triggering event)
 deferred: evaluated at the end of the triggering transaction
 detached: evaluated as a separate transaction
 executing the rule action
 immediate, deferred or detached
 problems
 determining consistency and termination
14
Active database concepts
Condition evaluation and rule execution
Rule R
action
condition
transaction T
triggering event
DELETE …
UPDATE …
…
INSERT INTO rel1 VALUES (…);
…
SELECT …
UPDATE …
SELECT
...
INSERT INTO rel1 ...
DB
15
Active database concepts
Rules and SQL
 SQL2 does not implement rules
 foreign key rules can be regarded as elements of active
databases
 SQL3 will implement rules
 syntax very similar to the pseudocode used so far (which, in
turn, is very similar to the Oracle syntax)
16
Active database concepts
Active databases in Postgres
 rules
 allow the definition of extra or alternate actions on updates
 triggers
 allow the association of user supplied procedures (functions)
with database events
17
Active database concepts
Rules in Postgres
CREATE RULE <name> AS
ON <event> TO <object>
[ WHERE <condition> ]
DO [ INSTEAD ] [ <action> | NOTHING ]
<event> ::= SELECT | UPDATE | INSERT | DELETE
<object> ::= <table> | <table>.<column>
<condition> ::= <SQL statement> /* “new” and “old” */
<action> ::= <SQL statement> /* “new” and “old” */
18
Active database concepts
Foreign key integrity with rules in Postgres
CREATE RULE FK_integrity_delete AS
ON delete TO items
WHERE
exists (select * from items_order where
items_order.items_id = old.items_id)
DO INSTEAD NOTHING;
 the rules mechanism is used to implement the view system
 for further details refer to the lab handouts
19
Active database concepts
Triggers in Postgres
CREATE TRIGGER <name>
{ BEFORE | AFTER } { <event> [OR <event> ...] } ON <table>
FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE <function> ( <arguments> )
<event> ::= UPDATE | INSERT | DELETE
<function> ::= <user supplied function>
statement triggers are not (yet) implemented
20
Active database concepts
Applications for active databases
 notification of the occurrence of certain conditions in
the database
 enforcement of integrity constraints
 for business rules
 automatic maintenance of derived data
 e.g. snapshots (materialised views), replicated tables, ...
21