active database concepts
DESCRIPTION
Active database concepts. Introduction. DB. triggered action. triggering event. think of examples. Rules. specify actions that are automatically triggered by certain events generalised model: EVENT-CONDITION-ACTION (ECA) event(s): database update operation(s) - PowerPoint PPT PresentationTRANSCRIPT
Active database concepts
1
Active database concepts
Active database concepts
2
Introduction
DBtriggering event triggered action
think of examples
Active database concepts
3
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)
Active database concepts
4
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
Active database concepts
5
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 ;
Active database concepts
6
Activity
design similar rules for the following cases: updating the salary of certain employees deleting certain employees
Active database concepts
7
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?
Active database concepts
8
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
Active database concepts
9
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 ;
Active database concepts
10
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
Active database concepts
11
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
Active database concepts
12
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
Active database concepts
13
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);
Active database concepts
14
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
Active database concepts
15
transaction T
DELETE …UPDATE ……INSERT INTO rel1 VALUES (…);…SELECT …UPDATE …SELECT...
Condition evaluation and rule execution
DB
triggering event
condition
action
INSERT INTO rel1 ...
Rule R
Active database concepts
16
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)
Active database concepts
17
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
Active database concepts
18
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” */
Active database concepts
19
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
Active database concepts
20
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
Active database concepts
21
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, ...