Event-based Notification VL Web-basierte Informationssysteme Annika Hinze Freie

Event-based Notification VL Web-basierte Informationssysteme Annika Hinze Freie Universität Berlin hinze@inf. fu-berlin. de

Event Notification Service Motivation 4 you heard about: - Information Retrieval : retrieval of text, images - semistructured data (XML): retrieval, storage - Databases: SQL querying 4 considered Web-Actions: - storage and display of documents - search for documents 2

Event Notification Service Motivation 4 Scenarios: - Search for certain MP 3 file -> not found -> repeat search - expect new book of author ‘King’-> search at Amazon -> not published yet -> repeat search - do we need to order items for the warehouse ? -> query stock -DB (#items in warehouse? ) -> Order if #items <=100 3

Event Notification Service Motivation New book 4 Example: Amazon Webinterface DB ? DBMS Application ! Source Query Response Client 4 Drawback: - repeated search necessary - delivery of old and new content 4

Event Notification Service Motivation 4 Idea: Event-Paradigm - Actions/Messages triggered by events - well-known from NSP (synch. of concurrent systems) - new for Web-based systems new book Amazon Webinterface DB DBMS Application New Book! 4 Event examples: Message arrives, DB-state changed, new MP 3 -File available, new book published, 101 st item sold, certain point in time, . . 5

Event Notification Service Motivation 4 Event Notification Service Source Query Response ENS Profile Response Client 4 Examples: - E-Commerce, Stock ticker, News on Demand, Data Update (z. B. web pages) Digital Libraries (z. B. Springer Link Alert, Hermes-Project) Remote Monitoring (Traffic, Buildings , Environment) Replication Communication in widely distributed systems, . . . 6

Event Notification Service Introduction 4 Various Architectures (1): - solitary Service/Source z. B. Springer Link Alert Problems: different Interfaces different Languages no result merging Source Query Response ENS Profile Response Client - mediating Service e. g. Hermes-Service Source Query Response ENS Profile Response Client 7

Event Notification Service Introduction 4 Various Architectures(2): - Distributed Service Source z. B. Siena [car 98] Client of Pr Qu er Re y sp on se ile se on sp Re ENS Profile Source y uer Q nse spo Re Response Client - Hierarchy/Network of Services Query Response ENS Source Query Response Source Profile z. B. Talarian Smartsockets [tal] ENS Profile Response Client P Re rofile spo nse Profile Response ENS 8

Profile Definition Language How to define a profile ? Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. Attribute-Value-Pairs, Boolean Operators Content-based search in documents similar to known IR methods Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. Consideration of external and/or historical information More parameters. . . 4 Base: Profile Definition Language 9

Profile Definition Language Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. What happened ? 4 Object Event Types: new Object changed Object deleted Object unchanged Object 4 Object Description before/after Event 4 Event Description 10

Profile Definition Language Event Types 4 Active object event state change of object or repository, observer independent 4 Passive object event no state change for certain period of time, have to be observed * Event 4 Time event clock times, dates, time interval 1 Time E. Object E. 4 Event pattern (composite events) timely combinations of simple events active Event Pattern passive 11

Profile Definition Language Object/Event Description Identification of Objects: 4 Strongly depends on application domain! 4 Similar to Query Languages 4 Examples: - Specification of name/value pairs and operators select greenhouse where temperature > 30 and humidity < 40 - Specification of object identifier - Specification of subject / Channel (hierarchy) Subscribe to stock-channel - Giving similar objects, similar events - Information Retrieval Queries 12

Profile Definition Language Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic When did it happen ? 4 (implicitly) after the last notification Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. 4 Before/after/at a certain date Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. 4 Before/after/at the occurrence of another event 13

Profile Definition Language Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. Where did it happen ? 4 Location of object 4 Origin of the message 4 Supplier Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. 14

Profile Definition Language Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. When to tell ? 4 At a certain time/time period 4 After n events 4 After/ at occurrence of another event 15

Profile Definition Language Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic Notify me immediately if the temperature is more than 30°C and humidity less than 40% in house A at the same time. How to tell ? 4 Send message 4 Send object 4 Protocol/Format Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not change for 20 days after beginning of May. 16

Profile Definition Language Components 4 Overview of Language Components: - Event Specification h Event/Object Description h Source Selection h Time Constrains - Notification Specification h Time/Schedule h Content h Format h Protocol - Observation Specification h Time/Schedule h Observer Selection - Additional Parameter, h e. g time system h accuracy, tolerances 4 not all Components supported by all ENS 17

Profile Definition Language 4 Event Specification: - Based on object model - Attribute/value pairs - Active object events, time events, event patterns - Very complex profiles possible 4 Notification Spec. : - At n events - At certain time/period - At/after another event Example: Continual Queries <CQ> : : = <Query> <Trigger. Cond> <Stop. Cond> <Query> : : = SELECT <Select. List> FROM <Object. List> [WHERE <Search. Condition>] [GROUP BY <Attribute. List>] [ORDER BY <Sort. Spec. List>] <Trigger. Cond> : : = <Time. Tri. Cond> | <Content. Tri. Cond> <Stop. Cond> : : = <Month> '-' <Day> '-' <Year> 18

Profile Definition Language Beispiel: Continual Queries 4 Example 1 (Time-based trigger): Notify if weather conditions change between Savannah(Georgia) and Ford Stewart 4 Continual Query: Create CQ Savanna_ wheather _watch as Query: SELECT * FROM www. wns. nova. gov WHERE location like%‘Savannah’ OR location like% ‘Fort Stewart’ ; Trigger: 20 minutes, Stop: 1 year (default). Notification-Parameter not given here, partly implicit (z. B. Author of profile = Recipient of Message) 19

Profile Definition Language Beispiel: Continual Queries 4 Example 2 (Content Trigger): Notify, if number of items in warehouse minus number of ordered items falls below a given threshold 4 Continual Query: Create CQ Inventory_monitoring as Query: SELECT item, num_stock, num_orders, threshold FROM item_inventory Trigger: num_stock + num_orders < threshold Stop: 6 month. 20

Profile Definition Language Beispiel: Continual Queries 4 Example 3 (Content Trigger different sources): Observe weather conditions between Savannah(Georgia) and Ford Stewart and send alternative route plans if the weather changes. 4 Continual Query: Create CQ Transportation Re-Planing as Query: SELECT plan_id, plan_ desc, plan_alt_routes FROM Transportation_plan WHERE plan_route like ‘Savannah to Ford Stewart’ Trigger: FROM www. wns. nova. grg WHERE location like%‘Savannah’ OR location like% ‘Fort Stewart’ ; Stop: next 3 month. 21

Event Notification Services 4 Application Domains of ENS 4 Architectures 4 Profile components 4 Interactions and Components of ENS 4 Application-dependent Requirements 4 Base technologies for Implementation 22

Event Notification Service Source Query Response ENS Interactions Profile Response Client Interactions: Passive Supplier Object of Anything new? Interest Observe Filter Active Supplier Advertise Object of Interest Subscribe Publish Notify Interested Party Unsubscribe Queue Transform Unadvertise Roles: Supplier Broker Consumer/Client 23

Event Notification Service Supplier Components ENS Consumer Profile Repository 24

Event Notification Service ENS Supplier Invoker Components Consumer Observer Information Object Event Message Profile Repository 25

Event Notification Service ENS Supplier Invoker Components Observer Consumer Filter Information Object Event Message Profile Repository 26

Event Notification Service ENS Supplier Invoker Components Observer Filter Consumer Notifier Information Object Event Message Profile Notification Repository 27

Event Notification Service Components: Example: Continual Queries System [liu 96] Alerting Service Supplier Invoker Information Object Repository Observer Event Message Filter Profile Notifier Notification Repository 28

Event Notification Service Components: Example: Continual Queries System [liu 96] Profildefinition und Speicherung Observer Filter Notifier Alerting Service Supplier Invoker Information Object Repository Observer Event Message Filter Profile Notifier Notification Repository 29

Event Notification Service Components: Example: Continual Queries System [liu 96] Profildefinition und Speicherung Observer Filter Notifier Alerting Service Supplier Invoker Information Object Repository Observer Event Message Filter Profile Notifier Notification Repository 30

Event Notification Service Components: Example: Continual Queries System [liu 96] Profildefinition und Speicherung Observer Filter Notifier Alerting Service Supplier Invoker Information Object Repository Observer Event Message Filter Profile Notifier Notification Repository 31

Event Notification Service Components: Example: Continual Queries System [liu 96] Profildefinition und Speicherung Observer Filter Notifier Alerting Service Supplier Invoker Information Object Repository Observer Event Message Filter Profile Notifier Notification Repository 32

Event Notification Service Requirements Different applications have different Requirements 4 Scalability regarding: - #Objects - #Events - Notification frequency and size: - 20, 000 messages / second in stock market - a few per minute in a digital library context 4 Cardinality - many-to-one for remote monitoring and control - one-to-many for software update - many-to-many for digital library 33

Event Notification Service Requirements 4 Reliability of Connections 4 Guaranteed delivery - important for offline-Clients - notification storage for digital library clients - weather channel broadcasts information 4 Expressiveness of Filter Language - which events are supported, which language - nested, semistructured objects - time-events - composite events 4. . - articles according to subject (e. g. data bases) - similar article - shares with value > 1000$ (content-based) - shares with value change > +10% 34

Event Notification Service Systems How to implement an ENS? Commercial system: Pointcast, Entry. Point, TIB/Rendezvous, . . . Research Systems: Elvin[seg 97], Siena[car 98], Open. CQ[liu 96], Niagara. CQ[che 00], Gryphon[ban 99], Le. Subscribe[. ], . . . Problems. . 35

Event Notification Service Systems 4 Often without active observation: - Entry. Point Smartsockets Rendezvous Elvin - Exceptions: Alerting Service Message Filter Notifier Event Message Profile Notification Repository h. Open. CQ h. Hermes Only restricted usability for passive suppliers, that do not trigger the ENS 36

Event Notification Service Systems 4 Often only routing: Notification is copy of suppliermessage 4 no persistent messages, best effort delivery Alerting Service Message Filter Notifier Event Message Profile Notification Repository Not appropriate for unreliable connections or offline clients 37

Event Notification Service Base Technologies How to implement an ENS? (2). . . or Implementation based on - Channel technology - Message-oriented Middleware: h. Java Message Service (JMS) h. Corba Event Service / Notification Service - Message Queuing Systems - Active Databases -. . 38

Bibliography [ban 99] Banavar, G, et. Al: “An efficient multicast-protocoll for contentbased publish-subscribe systems”, Conference on Distributed Systems, Texas 1999 [car 98] Carzaniga, A. , “Architectures for an Event Notification Service Scalable to Wide-area Networks, Politecnico di Milano, 1998, Milano, Italy [che 00] Chen, J. et al: “Niagara. CQ: A scalable Continous. Query System for Internet Databases”, SIGMOD 2000, Dallas, Texas, May 2000 [liu 99] Liu, L. ; C. Pu, W. Tang, “Supporting Internet Applications Beyond Browsing: Trigger Processing and Change Notification”, 5 th International Computer Science Conference, ICSC´ 99, Hong Kong, China, December 1999 [seg 96] Bill Segall and David Arnold “Elvin has left the building: A publish/subscribe notification service with quenching” Proceedings AUUG 97, Brisbane, Australia, September 1997. [tal] Talarian Smartsockets Information Site: http: //www. talarian. com/multicast_transport_protocol. html 39

Event Notification Services 4 Application Domains of ENS 4 Architectures 4 Profile components 4 Interactions and Components of ENS 4 Application-dependent Requirements 4 Base technologies for Implementation - Channels: CDF - Message-Oriented Middleware: JAVA Message Service - Active Databases 40

Base Technologies Channel-Technology 4 Channel: - Supplier publishes subject-based content - Consumer subscribes to subject-channel - Delivery of new content to consumer channel Consumer Supplier channel 4 Standardization Proposals : - CDF by Microsoft - Net. Caster by Netscape 41

Base Technologies Channel-Technology: CDF 4 Channel Definition Format (CDF) - Open industry standard for data definition of content to be pushed over the internet - Application of XML - 1997 proposed by Microsoft to W 3 C [ell 97] - Supported by several systems such as Internet Explorer 4. 0+ and Back. Web 4 Appearance: - As web-channel As screen-safer For mobile clients For software-download 42

Base Technologies Channel-Technology: CDF 4 Channel setup - Channel content defined in CDF-file Supplier provide CDF file on the web Consumer subscribe via client-software Initial download of the CDF file subscribe Supplier cdf Consumer 43

Base Technologies Channel-Technology: CDF 4 Notification sequence - Smart-pull: according to schedule download of the CDF-file - Offline mode: additionally download all new content - Notification of the consumer (e. g. icon or mail) - Presentation of the news as e. g. screensaver Supplier cdf Consumer 44

Base Technologies Channel-Technology: CDF 4 Properties: - Scheduling options - Log file option <? XML VERSION="1. 0" ENCODING="UTF-8"? > <CHANNEL HREF="Channel. Page. html” BASE="http: //. . /channel/"> <TITLE>Your Channel</TITLE> <ABSTRACT>. . </ABSTRACT> <LOGO HREF=”try. ico" STYLE="ICON"/> <SCHEDULE STARTDATE=” 2000 -01 -01"> <INTERVALTIME DAY="7" /> <EARLIESTTIME HOUR="0" /> <LATESTTIME HOUR="12" /> </SCHEDULE> . . . 45

Base Technologies Channel-Technology: CDF 4 Properties: - Channels can be hierarchically ordered - Offline/online mode and precaching, crawling options - Various usage modes. . . <ITEM HREF="page 1. html" PRECACHE="YES” LEVEL="0” > <TITLE>Page 1</TITLE> <ABSTRACT>. . . </ABSTRACT> <LOGO HREF="your. ico" STYLE="ICON"/> </ITEM> <ITEM HREF="page 2. html" PRECACHE="YES” > <TITLE>Page 2</TITLE> <USAGE VALUE="Screen. Saver"></USAGE> </ITEM> </CHANNEL> 46

Base Technologies Channel-Technology: CDF 4 Valuation: - Very easy to implement True personalization hard to realize Smart-pull causes unnecessary network load Supplier Old and new content Invoker Observer Notification only 4 Functionality for ENS X Alerting Service Filter X X Notifier Information Object Event Message Profile Notification Repository X - CDF file as “object repository” - Implements simple form of AS - Recommendable for LAN-wide communication e. g. news-server within a company 47

Base Technologies Message-Oriented Middleware 4 Message-Oriented Middleware: - Peer-to-peer distributed computing Anonymity of communicating objects Asynchronous messaging for applications Decoupling of applications - Infrastructure for hreliable hscalable hperformance-oriented distributed application networks in heterogeneous environment 48

Base Technologies JMS: Introduction 4 JMS Java Message Service: - Message-Oriented Middleware - Asynchronous communication btw. Enterprise applications - API Specification / Set of Interfaces - Vendors implement JMS (e. g. in BEA Weblogic, Oracle AQ, MQSeries IBM) - Clients (Supplier, Consumer) + Provider 49

Base Technologies 4 2 Domains: Supplier pu h blis JMS: Domain Models Point-to-Point queue deliver Consumer Publish/Subscribe Consumer sh Supplier li pub topic Consumer topic deliver Supplier topic Destination Durability/Persistence Consumers per message Point-to-Point Publish/Subscribe FIFO/Browsable Topic Hierarchy Queue implicit concept One Topic Durable. Subscription Many 50

Base Technologies <<Interface>> Connection. Factory creates JMS: Architecture <<Interface>> Connection sends Message. Producer sends to <<Interface>> Message <<Interface>> Session creates <<Interface>> creates Message. Listener creates receives notifies <<Interface>> Message. Consumer receives from Destination 51

Base Technologies <<Interface>> Connection. Factory creates <<Interface>> Connection creates <<Interface>> Message. Producer sends to JMS: Architecture <<Interface>> Session creates <<Interface>> Message <<Interface>> Destination <<Interface>> Message. Listener creates receives notifies <<Interface>> Message. Consumer receives from Alerting Service Supplier Observer Filter Client Consumer Notifier JMS Provider Event Message Profile Notification Repository Client Repository 52

Base Technologies <<Interface>> Connection. Factory creates <<Interface>> Connection creates <<Interface>> Message. Producer sends to JMS: Architecture <<Interface>> Session creates <<Interface>> Message <<Interface>> Destination <<Interface>> Message. Listener creates Profildefinition und Speicherung Observer notifies Filter receives <<Interface>> Message. Consumer Notifier receives from Alerting Service Supplier Observer Filter Consumer Notifier Event Message Profile Notification Repository 53

Base Technologies <<Interface>> Connection. Factory creates <<Interface>> Connection creates <<Interface>> Message. Producer sends to JMS: Architecture <<Interface>> Session creates <<Interface>> Message <<Interface>> Destination <<Interface>> Message. Listener creates Profildefinition und Speicherung Observer notifies Filter receives <<Interface>> Message. Consumer Notifier receives from Alerting Service Supplier Observer Filter Consumer Notifier Event Message Profile Notification Repository 54

Base Technologies <<Interface>> Connection. Factory creates <<Interface>> Connection creates <<Interface>> Message. Producer sends to JMS: Architecture <<Interface>> Session creates <<Interface>> Message <<Interface>> Destination <<Interface>> Message. Listener creates Profildefinition und Speicherung Observer notifies Filter : JMS System receives <<Interface>> Message. Consumer Notifier receives from Alerting Service Supplier Observer Filter Consumer Notifier Event Message Profile Notification Repository 55

Base Technologies <<Interface>> Connection. Factory creates <<Interface>> Connection creates <<Interface>> Message. Producer sends to JMS: Architecture <<Interface>> Session creates <<Interface>> Message <<Interface>> Destination <<Interface>> Message. Listener creates Profildefinition und Speicherung Observer notifies Filter receives <<Interface>> Message. Consumer Notifier receives from Alerting Service Supplier Observer Filter Consumer Notifier Event Message Profile Notification Repository 56

Base Technologies JMS: Message Types <<Interface>> Message <<Interface>> Bytes. Message <<Interface>> Map. Message <<Interface>> Object. Message Serialised Java Object • Header • Properties • Body <<Interface>> Stream. Message <<Interface>> Text. Message e. g. XML 57

Base Technologies JMS: Message-Example Message. Header : spec //defined by JMSDestination ="Name of the Topic” JMSDelivery. Mode ="Persistent" JMSExpiration ="Tue Mar 30 12: 06: 03 MET 2001” JMSPriority =5 JMSMessage. Id =3875784578745 JMSTime. Stamp ="Tue Jan 30 12: 06: 03 MET 2001” Properties: JMSXUser. Id ="hinze" spec // predefined by JMS_Oracle_ Delay. Time =30000 defined Author="J. Smith" specific // Vendor// Application- 58

Base Technologies JMS: Message Filtering 4 Consumer associated with Message Selector (=Profile) Message Selector: author LIKE 'Smith%’ Pub. Year > 1980 JMSPriority > 5 4 Attribute-Value Pairs (SQL 92 ? ) 4 Applied against - Message Header - Message Properties Not against Body! 59

Base Technologies JMS: Sample Code Context messaging = new Initial. Context(); topic. Connection. Factory =(Topic. Connection. Factory) messaging. lookup("Topic. Connection. Factory"); stock. Topic = (Topic) messaging. lookup("Stock. Topic"); Topic. Connection topic. Connection; topic. Connection = topic. Connection. Factory. create. Topic. Connection(); Topic. Session session; session = topic. Connection. create. Session(false, Session. AUTO_ACKNOWLEDGE)); Topic. Publisher publisher; publisher = session. create. Publisher(topic); Topic. Subscriber subscriber; subscriber = session. create. Subscriber(topic, selector); topic. Connection. start(); 60

Base Technologies String stock. Data; JMS: Sample Code Text. Message message; message = session. create. Text. Message(); message. set. Text(stock. Data); publisher. send(message); Map. Message message = session. create. Map. Message(); message. set. String("Name", stock. Name); message. set. Double("Value", stock. Value); publisher. send(message); 61

Base Technologies public Class Stock. Listener implements javax. jms. Message. Listener { JMS: Sample Code asynchronous void on. Message(Message message) { } } Stock. Listener my. Listener = new Stock. Listener(); subscriber. set. Message. Listener(my. Listener); Text. Message message; synchronous / polling message = (Text. Message) subscriber. receive(); 62

Base Technologies JMS: Valuation 4 Valuation: - Scalability: Implementation dependent - Cardinality: Point to Point N: 1, Pub/Sub: N: M - Guaranteed delivery possible with durable subscribers/messages - Filtering and Personalization honly messages, object observation not included h. Events observed by supplier h. Time-events/event combinations not supported hpoor filter expressiveness (simple attribute-value matching) 4 Base technology, no full ENS implementation 63

Base Technologies Active Databases: Motivation 4 Assumption: Data stored in DB 4 Architectures: New book 1. Variant DB Amazon Webinterface DBMS Application ? ! 2. Variant New book Amazon Webinterface DB DBMS Application ENS 64

Base Technologies Active Databases: Motivation 4 Idea: Active Databases (ADB) New book DB a. DBMS Application 4 ADB: - Relational or object oriented data base - observe system state - Triggers actions in reaction on (system)events 65

Base Technologies Active Databases 4 Conceivable events: - Database state transitions - Temporal events - Abstract or external events 4 Examples: - Relational: (Sybase, Ingres) - Object-relational: Postgres - Object-oriented: Sentinel, Ode [agr 89], Samos[dit 00] 4 Simple triggers also in SQL 3 (Oracle) 66

Base Technologies Active Databases 4 Common argumentation: - central management of semantic in DB optimization of processing higher DBMS functionality support of time-dependent requirements 4 Common applications: - monitoring of integrity constraints access control service for derived data within database (view update) but also: trigger of external actions (notify administrator about weird actions on data) 67

Base Technologies Active Databases 4 Extension of triggers: active database rules 4 ADB rules defined as ECA-rules: - Event - Condition - Action When If Then Attributes <event expression > <condition expression> <action> {priority, . . . } 4 Different implementations - Sybase: event and action part - Postgres: separate event, condition, action parts - Sentinel: separate event, condition, action parts 68

Base Technologies 4 Trigger in SQL 3: - simplified integrity rules - simple consitions - Active Databases create trigger Rule. Name on insert|delete|update on Relation. Name [references[old as Old. Name , new as New. Name ]] when Condition for each row|statement SQLStatement called on insert/update/delete on specified relation references: binds variables to old/new tupels of a relation for each row: activates action for all selected tupels for each statement: activates action once for each condition 69

Base Technologies Active Databases 4 Example: - count number of inserted books create trigger Book. Count. Plus on insert on Book referencing old as Old new as New update Book. Count set New. Number = Old. Number+1 for each row create trigger Book. Count. Minus. . . set New. Number = Old. Number-1 70

Base Technologies Active Databases 4 Example: - check that the budget holds - just once per update create trigger Budget. Test before update on Salary for each statement when ( Sum. Salary > 200. 000) signal „Budget overflow!“ 71

Base Technologies Trigger in Oracle 4 Trigger in Oracle 8 i: - similar to stored procedures - written in PL/SQL, Java (stored internally), or C (stored externally) 4 Events: - DML statements that modify data in a table (INSERT, UPDATE, or DELETE) - DDL statements - System events (startup, shutdown, and error messages) - User events such as logon and logoff 72

Base Technologies Trigger in Oracle 4 Trigger in Oracle 8 i: - Syntax: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER|INSTEAD OF} {INSERT|DELETE|UPDATE [OF column_list]}… [[REFERENCING correlation_name ] FOR EACH ROW [WHEN ( condition )]] DECLARE declarations BEGIN PL/SQL code END; 73

Base Technologies PL/SQL Block Structure: Example: DECLARE v_variable VARCHAR 2(5); BEGIN SELECT column_name INTO v_variable FROM table_name; EXCEPTION WHEN exception_name THEN. . . END; Trigger in Oracle DECLARE – Optional Variables, cursors, userdefined exceptions BEGIN – Mandatory SQL statements PL/SQL statements EXCEPTION – Optional Actions to perform when errors occur END; – Mandatory PL/SQL variable Bind variable: declared in host environment (e. g. SQL*Plus), reference in PL/SQL: : variable 74

Base Technologies Trigger in Oracle 4 Trigger Applications: - Integrity Test Referential Integrity Test Event Logging User Auditing Maintain table replicas gather statistics modify tables according to DML statements against views security authorization publish events to applications 75

Base Technologies Trigger in Oracle 4 Example of an Entity Integrity Trigger CREATE OR REPLACE TRIGGER CUSTOMER_GET_KEY BEFORE INSERT ON CUSTOMER FOR EACH ROW DECLARE NEW_ID NUMBER; BEGIN SELECT MAX(CUSTOMER_ID_NO) INTO NEW_ID FROM CUSTOMER; NEW. CUSTOMER_ID_NO : = NEW_ID + 1; END CUSTOMER_GET_KEY; CREATE OR REPLACE TRIGGER customer_name_upper BEFORE INSERT OR UPDATE OF name ON customer FOR EACH ROW BEGIN : new. name : = UPPER(: new. name) ; END ; 76

Base Technologies Trigger in Oracle 4 Example of a Referential Integrity Trigger CREATE OR REPLACE TRIGGER CUSTOMER_DEL_CHECK BEFORE DELETE ON CUSTOMER FOR EACH ROW Non-PL/SQL variable DECLARE CUSTOMER_COUNT NUMBER; BEGIN SELECT COUNT(CUSTOMER_ID_NO) INTO CUSTOMER_COUNT FROM SALE WHERE CUSTOMER_ID_NO = : OLD. CUSTOMER_ID_NO; IF (CUSTOMER_COUNT > 0) THEN RAISE_APPLICATION_ERROR(-20000, 'Cannot delete customer because it has '|| TO_CHAR(CUSTOMER_COUNT, '99999') || ' Sales. '); END IF; END CUSTOMER_DEL_CHECK; 77

Base Technologies Trigger in Oracle 4 Example of a Referential Integrity Trigger (exeption!) CREATE OR REPLACE TRIGGER pos_cust_bal BEFORE INSERT OR UPDATE ON cust FOR EACH ROW DECLARE neg_bal_error EXCEPTION; BEGIN IF : new. balance < 0 THEN RAISE neg_bal_error ; END IF; EXCEPTION WHEN neg_bal_error THEN RAISE_APPLICATION_ERROR (-20001, ‘Negative Balance not allowed. ’); END; 78

Base Technologies Trigger in Oracle Triggers vs. integrity constraints: 4 Oracle recommends to use triggers only to enforce: - referential integrity when child and parent tables are on different nodes of a distributed database - complex business rules not definable using integrity constraints 4 Reason: - integrity constrains are “all SQL” - easier, less errors - triggers are more complex to evaluate - better performance since better optimization 79

Base Technologies Trigger in Oracle 4 Example of User Auditing Trigger CREATE OR REPLACE TRIGGER audit_ cust_trigger BEFORE UPDATE ON cust FOR EACH ROW WHEN (new. balance <> old. balance) BEGIN INSERT INTO audit_ cust VALUES (: old. cust_no, : old. name, : old. address, : old. balance, sysdate ); END; 80

Base Technologies Trigger in Oracle 4 Compiling Triggers: - compilation different to PL/SQL blocks - PL/SQL block is compiled each time loaded into memory: h 1. Syntax checking + parse tree generation h 2. Semantic checking: Type checking etc. on the parse tree h 3. Code generation(pcode) - Triggers fully compiled at creation time (pcode then stored in the data dictionary) - trigger firing: no opening of cursor, but direct execution - errors during compilation do not stop trigger creation (trigger firing fails + calling action fails) 81

Base Technologies Trigger in Oracle 4 Trigger Dependencies: - trigger become invalid if depended-on object is modified - depended-on objects: stored procedure or function called from the trigger body, other functions or packages - invalid triggers are recompiled when next invoked - if recompilation fails (object dropped) trigger becomes VALID WITH ERRORS 4 Recompiling Triggers - manually: ALTER TRIGGER statement ALTER TRIGGER trigger_name COMPILE 82

Base Technologies Trigger in Oracle 4 Triggers for remote sites: - compiles at creation time - fails if remote site not available at execution time CREATE OR REPLACE TRIGGER Example AFTER INSERT ON Emp_tab FOR EACH ROW BEGIN INSERT INTO Emp_tab@Remote -- <- compilation fails here when VALUES ('x'); -- dblink is inaccessible EXCEPTION WHEN OTHERS THEN INSERT INTO Emp_log VALUES ('x'); END; 83

Base Technologies Trigger in Oracle 4 Modifying Triggers - replace with new definition, or - drop and rerun create trigger 4 Enabling and Disabling Triggers - two modes: enables (default) / disabled - Enabled: trigger executes its body if triggering statement is entered and trigger restriction evaluates to TRUE. - Disabled: trigger does not execute trigger body Disable or Re-enable a database trigger: ALTER TRIGGER trigger_name DISABLE | ENABLE Disable or Re-enable all triggers for a table: ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS 84

Base Technologies Trigger in Oracle 4 Trigger publish events to applications: - support of database events: h. DML on tables, hsystem events on Database and schema - users specify procedure to be run when the event occurs - uses Advanced Queueing publish/subscribe engine (based on JMS) 85

Base Technologies Trigger in Oracle 4 Drawback of Oracle/SQL 3 Triggers: - no time events restricted abstractions, definition complicated, error source difficult validation SQL 3: definition for single operation leads to multiple definition (test for budget on update AND insert necessary) no deferred execution possible cascading triggers, cycles possible SQL: 99 only primitive events on single tables SQL: 99 no events based on select-operations SQL: 99 conflict resolution problematic 86

Base Technologies Active Databases 4 Valuation (DB with SQL: 99 Triggers) - Often no sophisticated profiles possible - No event patterns, events from different sources - Restricted actions Alerting Service Supplier - Use as simple ENS or in ENS-Application: Filter Notifier Information Object Repository Event Message Profile Notification Repository 4 Functionality for ENS: - Object/Event/Profile/Notification Repository - Trigger for AS (active observer on suppliers side) 87

Base Technologies Active Databases 4 „real“ active Databases A DBS is called active, if it is, additionally to common DBMS-functionality, capable to detect defined situations within the database (and outside) and to trigger defined reactions. [dit 00] 4 Basic ECA-Rule: DEFINE RULE rule_name ON event_clause IF cond_clause DO action clause execution constraints 88

Base Technologies 4 Example: Active Databases After 3 inserts on the same account transfer the money according defined strategy to 3 (depot) accounts DEFINE EVENT Event. Inv _Bank. Account. Transfer TIMES (3, Kunde. Account. Insert ): SAME OBJECT DEFINE RULE Event. Inv _Bank. Account. Transfer ON Event. Inv _Bank. Account. Transfer DO { S = Bank. Account. saldo + Inv_Account 1. saldo ; Insert_1 = (S* Bank. Account. ratio * 0. 01)- Bank. Account. saldo ; Insert_2 = (S* Inv_Account 1. ratio * 0. 01) - Inv_Account 1. saldo ; Insert_3 = (S* Inv_Account 2. ratio * 0. 01) - Inv_Account 2. saldo ; Account. Insert (Insert_1); Inv_Account 1 Insert(Insert_2); Inv_Account 1 Insert(Insert_3); } 89

Base Technologies Active Databases 4 Management of rules: - define parts separately - delete rule or parts DEFINE EVENT event_name DEFINE CONDITION cond_name DEFINE ACTION actions_name DELETE RULE rule_name EVENT event_name CONDITION cond_name ACTION actions_name DISABLE RULE rule_name - switch status of rule ENABLE RULE rule_name e. g disable rule for bulk load or backup DISABLE RULE R 1 EVERY WEEK [Fr 18: 00, Mo 08: 00] 90

Base Technologies Active Databases vs Triggers Difference to triggers ? 4 Complex event definitions (e. g. on several tables) 4 time events 4 composite events 4 external events 4 conflict resolution strategy 4 various coupling modes 4. . . 91

Base Technologies Active Databases: Event Specification 4 Event class/type: rules define event types 4 Event Instance: - actual occurrence of event class - specification describes event (what happened) - has occurrence time (when) Event = (event specification, event time) Note: - recorded occurrence time depends on system clock occurrence time describes time of event detection explicit (what and when) vs. implicit (what) event description primitive or composed events 92

Base Technologies Active Databases: Event Specification Primitive Events: - DB events = begin or end of an DB operation execution DB (select, insert, delete update in relational DBMS) DEFINE EVENT E 1 BEFORE INSERT - DBMS events = begin or end of operations on DB (transaction start, end, abort; user login, . . . ) DEFINE EVENT E 2 Bank. Account. Insert. COMMIT_TA - Time events = absolute, periodically repeated or relative definition of point in time DEFINE EVENT E 3 EVERY 10 DAYS 16: 15 - Abstract events = external events in system applications, defined by identifier, explicitly announced to the DB DECLARE Account. Check RAISE Account. Check 93

Base Technologies Active Databases: Event Specification Composite events: based on primitive events, defined by event algebra 4 Disjunction E = E 1 | E 2 : - E 1 or E 2 occurs, E. time=min(E 1. time or E 2. time) 4 Sequence E = E 1 ; E 2 : - E 2 occurs after E 1 (E 1. time < E 2. time), E. time=E 2. time 4 Konjunction E= E 1 , E 2 : - E 1 and E 2 occur, E. time = max(E 1, E 2) 4 Negation NOT E : - E did not occur within interval [t_start, t_end], E. time=t_end 4 Reduction: - *E, *E WITHIN I, TIMES(n, E), - TIMES(n_min, n_max, E) WITHIN I 94

Base Technologies Active Databases: Event Specification Semantics of Event Order: Rule: „fire if e 1; e 2“ Traces: e 1 e 3 e 2 e 1 e 2 e 1 e 2 - fire ? - fire once or twice ? - fire two or three times ? 4 one trace for DBMS / transaction / user ? 4 Rule execution needs clear semantic 4 State: rule executed differently in different a. DBMS 95

Base Technologies Active Databases: Condition and Action Condition: - predicate over database state - defined as SQL-query, WHERE-clause, method or applicationprocedure - reference to old and new state Action: - program part with operations on DB and/or other operations DB operations (e. g. update, insert, select, delete), DBMS operations (e. g. abort transaction) stored procedures method call or application-operations alternative (do instead action_clause ) rule operations (e. g. definition, change, activation of rules) 96

Base Technologies Active Databases: Condition and Action Example: DEFINE EVENT Event. Account. Insert Kunde. Account. Insert DEFINE EVENT Event. Savings ( NOT Event. Account. Insert WITHIN [ Event. Account. Insert , Event. Account. Insert + 1 MONTH]): SAME OBJECT DEFINE RULE Savings. Problems ON Event. Savings DO { alerter (“Warning: You have not saved much”) } If after the last account insert nothing has been inserted for one month the customer is to be notified personally. 97

Base Technologies Active Databases: Rule execution Active vs. Deductive databases[wid 93]: 4 deductive DB: - stores facts and rules - rules support derivation of “new” facts from stored facts - rule example: p(X) <- q(x, Y), s(Y) (p is derived by q and s) 4 ECA: - describe actions triggered by events 4 orthogonal concepts 4 possible implementation within single system 4 possible use of ECA to implement deductive rules: DEFINE RULE Rule 1 ON query(p(X)) IF true DO query (q(X, Y) DEFINE RULE Rule 2 ON answer(q(X, Y)) IF true DO query(s(Y)) 98

Base Technologies Active Databases: Rule execution: 4 Model: - activity 1: infinite loop for event detection and rule triggering - activity 2: if triggered rule choose rule R evaluate condition C of R if C true, do action A of R 4 Semantic of execution (directions): - granularity: for each tupel, operation, transaction, . . . element or set-based execution (e. g. each row) conflict resolution coupling modes iterative or recursive execution cascading rules 99

Base Technologies Active Databases: Conflict Resolving Conflict resolving [dia 97]: 4 Conflict: More than one rule for one event - fundamental to controlling of a. DBMS - strongly influences result 4 Problems: - rule order influences final DB state, - rule order impact on performance Goal : ensuring confluent rule sets = order of firing without impact on final DB state 100

Base Technologies Active Databases: Conflict Resolving 4 Clear Conflict resolution mechanism needed: - large # rules -> complex and complicated interactions - large # users to create rules: need for guidelines and control - varying applications impose distinct strategies for conflict resolution 4 Strategies: - random rule order by criteria such as creation time, . . . execution priority defined by user concept-based rules: (different priority levels based on functionality) 4 State: no flexible conflict resolution mechanisms 101

Base Technologies Active Databases: Conflict Resolving 4 Example: execution priority defined by user: - supported in most a. DBMS - for each rule, defined when created 4 Problems: - different priority criteria by authors Example: integrity constraints Rule: if constraint violated reject update Criterion: order based on complexity of constraint (performance!) Problem: user-dependency, multi-user environment problematic - Per-rule priority mechanism too low-level for large rule sets - definition at rule-creation time: parameters sometimes known at execution time, e. g. workload in real-time DB decisive for rule-order 102

Base Technologies Active Databases Coupling modes: ECA-rules and Transactions - temporal coupling (immediate) Event a. TA=r. TA Rule execution - temporal coupling (deferred) Event receive commit execute commit Rule execution a. TA=r. TA - contextual coupling (decoupled) Event a. TA create transaction Rule execution r. TA 103

Base Technologies Active Databases Coupling modes: E-C / C-A (any combination possible) immediate/deferred Condition evaluation Action deferred/decoupled Condition evaluation Action 104

Base Technologies Active Databases: Execution Modes Execution modes cascading rule execution: events occur during action and trigger new rules E 1 When is the new rule executed ? R 1 E 2 4 iterative execution: R 3 R 4 - complete action - insert new rules (according priority) in list of “waiting” rules E 1 4 recursive execution: - interrupt action - execute all immediate rules R 1 E 2 R 3 R 4 105

Base Technologies Active Databases: Execution Modes Cascading Rules 4 Problem: no termination of recursive rule triggering E 1 4 avoid during rule definition - R 1 complicated, needs methodology models: finite state machine, petri-nets and tool-support, e. g. rule analyzer E 2 R 3 R 4 E 4 R 5 4 use system-internal restrictions - syntactical rule restriction, - abort if #cycles > threshold R 1 R 6 E 5 R 7 R 8 106

Base Technologies Active Databases Optimization of rules 4 Situation: temporally persistent rules, evaluated many times 4 Problem: - potentially large set of predefined queries - possible overhead on every event 4 Approaches: - conventional query optimization grouping of rules (multiple query optimization) materializing intermediate results rule buffering strategies for real-time applications parallelism 107

Base Technologies Active Databases Evaluation Criteria of a. DBMS [cha 93]: 4 Rule expressiveness - Supported events, Event operators 4 Execution semantics - Coupling modes, Cascaded rule execution, Multiple rule execution, Priorities, Postgres 4 Optimization of rules 4 Architectural approach - database events: retrieve, replace, delete, append, new, old - temporal events: time and date - disjunction operator - immediate reaction on event - cascaded rule execution supported - multiple rule execution with user defined priorities - rule optimization (e. g. query rewrite) - object-relational architecture 108

Base Technologie ADBMS: Developement and current State Do we need all that active features within the DB ? - Start 1953: trigger model for System R (first RDBS) - 1982: term „active DB“ introduced for system with automatic view update 4 RDBMS: - restricted events (only DB operations) - different approaches for rule execution - specialized for integrity constrains, view update, . . . 4 ODBMS: - most concepts first proposed in Hi. PAC project [day 88] support of special events in oo-context method events, internal and external rules for classes Encapsulation and Inheritance for rules 109

Base Technologies Active Databases Do we need all that active features within the DB ? New trend: unbundling [gep 98] 4 Idea: - DBMS with simple basic functionality - additional features in separate cooperating components, e. g Event/Reaction-Service - possible interaction of DBMS and service: Event-Action Servive Event occures DBMS DB Evaluate condition result Execute action (extension of ENS) Event/Reaction Service 110

Bibliography [cha 93] Chakravarthy: “A comparative evaluation of active relation databases”, Tech. Rep: UF-CIS-TR-93 -002, University of Florida, 1993 [day 88] Dayal et al: “The Hi. PAC Project: Combining Active Databases and Timing Constrains”, ACM Sigmod Record 17(1), March 1988 [dit 00] Dittrich & Gatziu: “Aktive Datenbanksysteme”, dpunkt. verlag, 2000 [gep 98] Geppart and Dittrich: “Bundling. A new construction paradigm for persistent systems ? ”, Networking and IS Journal 1(1), June 1898 [wid 93] Widom: “Deductive and active Databases: Two paradigms or end of Spectrum? ”, Proc. Int. WS on Rule in DBS, Edinburg, Scottland, 1993 Good starting points: Active Database Central: http: //www. ida. his. se/ ida/adc/ Active DBMS Manifesto: ACT-NET Consortium. The Active Database Management System Manifesto: A Rulebase of ADBMS Features. ACM Sigmod Record 25(3): 40 -49, 1996. (see reader) 111

4 Next semester (SS 01) - Seminar about event-based web-applications (and XML) 4 Next Wednesday - Prof. Schweppe: web and replication 4 Now - Kilian Lenz: Talk about RSS 112