Active database concepts

1. Active database concepts

2. Introduction DB triggered action triggering event think of examples

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)

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

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 ;

6. Activity • design similar rules for the following cases: • updating the salary of certain employees • deleting certain employees

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?

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

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 ;

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

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

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

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);

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

15. Condition evaluation and rule execution Rule R action condition triggering event INSERT INTO rel1 ... transaction T DELETE … UPDATE … … INSERT INTO rel1 VALUES (…); … SELECT … UPDATE … SELECT ... DB

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)

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

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” */

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

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

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, ...