event-based notification vl web-basierte informationssysteme

Event-based NotificationEvent-based NotificationVL Web-basierte InformationssystemeVL Web-basierte Informationssysteme

Annika HinzeFreie Universität [email protected]

2

Event Notification Service Event Notification Service Motivation

you heard about: Information Retrieval : retrieval of text, images semistructured data (XML): retrieval, storage Databases: SQL querying

considered Web-Actions: storage and display of documents search for documents

3


Scenarios:

Search for certain MP3 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

4

Application?

DBMSDB

Amazon Webinterface

New book

!

Query

ResponseSource Client


Example:

Drawback: repeated search necessary delivery of old and new content

5


Idea: Event-Paradigm Actions/Messages triggered by events well-known from NSP (synch. of concurrent systems) new for Web-based systems

ApplicationDBMSDB

Amazon Webinterface

New Book!

new book

Event examples:Message arrives, DB-state changed, new MP3-File available, new book published, 101st item sold, certain point in time, ....

6


Event Notification Service

Profile

ResponseClientSource ENS

Query

Response

Event Notification Service

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

7

Event Notification Service Event Notification Service Introduction

Various Architectures (1):

solitary Service/Sourcez.B. Springer Link AlertProblems:

different Interfacesdifferent Languagesno result merging

mediating Servicee.g. Hermes-Service

ClientProfile

ResponseSource ENS

Query

Response

ClientClient

ClientProfile

ResponseENS

Query

Response

ClientClient

SourceSourceSource

8

Event Notification Service Event Notification Service Introduction

Various Architectures(2):

Distributed Servicez.B. Siena [car98]

Hierarchy/Network of Servicesz.B. Talarian Smartsockets [tal]

Profile

ResponseClient

ENS

Profi

le

Respon

se

Client

Query

ResponseSource

SourceSource

Source QueryResponse

ENS

ClientProfile

Response

ClientClient

Query

Response

SourceSourceSource

ENSQuery

Response

SourceSourceSource ENS

Pro

file

Resp

on

se

Profile

Response

ProfileResponse

9

Profile Definition LanguageProfile 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.

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

Tell me every Monday if there is a new article from colleague ABC and send me all new articles about my research topic


Content-based search in documents similar to known IR methods

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.

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

Base: Profile Definition Language

10



Notify me immediately if the temperature is more than 30°C andhumidity less than 40% in house Aat the same time.


Notify me immediately if the XYZ-shares are dropped by more then 20% or they did not changefor 20 days after beginning of May.



What happened ?

Object Event Types: new Objectchanged Objectdeleted Objectunchanged Object

Object Description before/after Event

Event Description

11

Profile Definition Language Profile Definition Language Event Types

Active object eventstate change of object or repository, observer independent

Passive object eventno state change for certain period of time, have to be

observed

Time eventclock times, dates, time interval

Event pattern (composite events)timely combinations of simple events

Event

Time E. Object E.Event Pattern

active passive

*

1

12

Profile Definition Language Profile Definition Language Object/Event Description

Identification of Objects: Strongly depends on application domain! Similar to Query Languages Examples:

Specification of name/value pairs and operators

Specification of object identifier Specification of subject / Channel (hierarchy)

Giving similar objects, similar events Information Retrieval Queries

select greenhouse where temperature > 30 and humidity < 40 select greenhouse where temperature > 30 and humidity < 40

Subscribe to stock-channelSubscribe to stock-channel

13








When did it happen ?

(implicitly) after the last notification

Before/after/at a certain date

Before/after/at the occurrence of another event

14








Where did it happen ?

Location of object

Origin of the message

Supplier

15








When to tell ?

At a certain time/time period

After n events

After/ at occurrence of another event

16








How to tell ?

Send message

Send object

Protocol/Format

17

Profile Definition Language Profile Definition Language Components

Overview of Language Components:

not all Components supported by all ENS

Observation Specification Time/Schedule Observer Selection

Additional Parameter, e.g time system accuracy, tolerances

Event Specification Event/Object Description Source Selection Time Constrains

Notification Specification Time/Schedule Content Format Protocol

18

Profile Definition Language Profile Definition Language Example: Continual Queries

Event Specification: Based on object model Attribute/value pairs Active object events,

time events, event patterns

Very complex profiles possible

Notification Spec.: At n events At certain time/period At/after another event

<CQ> ::= <Query> <TriggerCond> <StopCond>

<Query> ::= SELECT <SelectList> FROM <ObjectList> [WHERE <SearchCondition>] [GROUP BY <AttributeList>] [ORDER BY <SortSpecList>]

<TriggerCond> ::= <TimeTriCond> | <ContentTriCond>

<StopCond> ::= <Month> '-' <Day> '-' <Year>

<CQ> ::= <Query> <TriggerCond> <StopCond>

<Query> ::= SELECT <SelectList> FROM <ObjectList> [WHERE <SearchCondition>] [GROUP BY <AttributeList>] [ORDER BY <SortSpecList>]

<TriggerCond> ::= <TimeTriCond> | <ContentTriCond>

<StopCond> ::= <Month> '-' <Day> '-' <Year>

19

Profile Definition Language Profile Definition Language Beispiel: Continual Queries

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

Example 1 (Time-based trigger):Notify if weather conditions change between Savannah(Georgia) and Ford Stewart

Continual Query:

Notification-Parameter not given here,partly implicit (z.B. Author of profile = Recipient of Message)

20


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.

Example 2 (Content Trigger):Notify, if number of items in warehouse minus number of ordered items falls below a given threshold

Continual Query:

21


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.

Example 3 (Content Trigger different sources):Observe weather conditions between Savannah(Georgia) and Ford Stewart and send alternative route plans if the weather changes.

Continual Query:

22

Event Notification ServicesEvent Notification Services

Application Domains of ENS Architectures Profile components Interactions and Components of ENS Application-dependent Requirements Base technologies for Implementation

23

Event Notification Service Event Notification Service Interactions

Consumer/ClientSupplier BrokerRoles:

Object ofInterest

Unadvertise

Publish

Advertise

Active Supplier

Profile

ResponseSource ENS

Query

ResponseClient

InterestedParty

Subscribe

Unsubscribe

NotifyObserve

Filter

Queue

Transform

Object ofInterest

Anything new?

Passive Supplier

Interactions:

24

Repository

Profile

Event Notification Service Event Notification Service Components

ConsumerConsumerSupplierSupplier ENSENS

25

Repository

Profile

ENSENSSupplierSupplier

Invoker

Repository

EventMessage

Repository

InformationObject

Observer


ConsumerConsumer

26

Repository

Profile

Repository

EventMessage

ENSENSSupplierSupplier

Invoker

Repository

InformationObject

Observer


Filter

ConsumerConsumer

27


ENSENS ConsumerConsumer

Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject

Observer Filter Notifier

28

Event Notification Service Event Notification Service Components: Example

Example: Continual Queries System [liu96]

Alerting ServiceAlerting Service

Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


29


Profildefinition und Speicherung

Observer


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


Filter

Notifier


30



Observer


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


Filter

Notifier


31



Observer


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


Filter

Notifier


32



Observer


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


Filter

Notifier


33

Event Notification Service Event Notification Service Requirements

Different applications have different Requirements

Scalability regarding: #Objects #Events Notification frequency and size:

Cardinality- many-to-one for remote monitoring and control- one-to-many for software update- many-to-many for digital library

- many-to-one for remote monitoring and control- one-to-many for software update- many-to-many for digital library

- 20,000 messages / second in stock market - a few per minute in a digital library context

- 20,000 messages / second in stock market - a few per minute in a digital library context

34

Event Notification Service Event Notification Service Requirements

Reliability of Connections Guaranteed delivery

important for offline-Clients

Expressiveness of Filter Language which events are supported, which language

....

- notification storage for digital library clients- weather channel broadcasts information

- notification storage for digital library clients- weather channel broadcasts information

- articles according to subject (e.g. data bases)- similar article - shares with value > 1000$ (content-based) - shares with value change > +10%

- articles according to subject (e.g. data bases)- similar article - shares with value > 1000$ (content-based) - shares with value change > +10%

- nested, semistructured objects- time-events- composite events

- nested, semistructured objects- time-events- composite events

35

Event Notification Service Event Notification Service Systems

How to implement an ENS?

Commercial system:Pointcast, EntryPoint, TIB/Rendezvous, ...

Research Systems:Elvin[seg97], Siena[car98], OpenCQ[liu96], NiagaraCQ[che00], Gryphon[ban99], LeSubscribe[.], ...

Problems....

36


Often without active observation: EntryPoint Smartsockets Rendezvous Elvin

Exceptions: OpenCQ Hermes

Only restricted usability for passive suppliers, that do not trigger the ENS


Repository

Profile

Repository

EventMessage

Repository

Notification

Filter NotifierMessage

37


Often only routing: Notification is copy of supplier-message

no persistent messages, best effort delivery

Not appropriate for unreliable connections or offline clients


Repository

Profile

Repository

EventMessage

Repository

Notification

Filter NotifierMessage

38

Event Notification Service Event Notification Service Base Technologies

How to implement an ENS?(2)

...or Implementation based on

Channel technology Message-oriented Middleware:

Java Message Service (JMS) Corba Event Service / Notification Service

Message Queuing Systems Active Databases ....

39

BibliographyBibliography

[ban99] Banavar, G, et. Al: “An efficient multicast-protocoll for content-based publish-subscribe systems”, Conference on Distributed Systems, Texas 1999

[car98] Carzaniga,A., “Architectures for an Event Notification Service Scalable to Wide-area Networks, Politecnico di Milano, 1998, Milano, Italy

[che00] Chen, J. et al: “NiagaraCQ: A scalable ContinousQuery System for Internet Databases”, SIGMOD 2000, Dallas, Texas, May 2000

[liu99] Liu,L.; C.Pu, W.Tang, “Supporting Internet Applications Beyond Browsing: Trigger Processing and Change Notification”, 5th International Computer Science Conference, ICSC´99, Hong Kong, China, December 1999

[seg96] Bill Segall and David Arnold “Elvin has left the building: A publish/subscribe notification service with quenching” Proceedings AUUG97, Brisbane, Australia, September 1997.

[tal] Talarian Smartsockets Information Site: http://www.talarian.com/multicast_transport_protocol.html

40

Event Notification ServicesEvent Notification Services

Application Domains of ENS Architectures Profile components Interactions and Components of ENS Application-dependent Requirements Base technologies for Implementation

Channels: CDF Message-Oriented Middleware: JAVA Message Service Active Databases

41

Base Technologies Base Technologies Channel-Technology

Channel: Supplier publishes subject-based content Consumer subscribes to subject-channel Delivery of new content to consumer

Standardization Proposals : CDF by Microsoft NetCaster by Netscape

Supplier

channel

channel

Consumer

Supplierchannel

Consumer

42

Base Technologies Base Technologies Channel-Technology: CDF

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 W3C [ell97] Supported by several systems such as Internet Explorer

4.0+ and BackWeb

Appearance: As web-channel As screen-safer For mobile clients For software-download

43


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

Supplier Consumer

subscribe

cdf

44


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 Consumercdf

45


Properties: Scheduling options Log file option

<?XML VERSION="1.0" ENCODING="UTF-8"?>

<CHANNEL HREF="ChannelPage.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>

...

<?XML VERSION="1.0" ENCODING="UTF-8"?>

<CHANNEL HREF="ChannelPage.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>

...

46


Properties: Channels can be hierarchically ordered Offline/online mode and precaching, crawling options Various usage modes

...

<ITEM HREF="page1.html" PRECACHE="YES” LEVEL="0” > <TITLE>Page 1</TITLE> <ABSTRACT>...</ABSTRACT> <LOGO HREF="your.ico" STYLE="ICON"/> </ITEM>

<ITEM HREF="page2.html" PRECACHE="YES” > <TITLE>Page 2</TITLE>

<USAGE VALUE="ScreenSaver"></USAGE> </ITEM>

</CHANNEL>

...

<ITEM HREF="page1.html" PRECACHE="YES” LEVEL="0” > <TITLE>Page 1</TITLE> <ABSTRACT>...</ABSTRACT> <LOGO HREF="your.ico" STYLE="ICON"/> </ITEM>

<ITEM HREF="page2.html" PRECACHE="YES” > <TITLE>Page 2</TITLE>

<USAGE VALUE="ScreenSaver"></USAGE> </ITEM>

</CHANNEL>

47


Valuation: Very easy to implement True personalization hard to realize Smart-pull causes unnecessary network load Old and new content Notification only

Functionality for ENS CDF file as “object repository” Implements simple form of AS Recommendable for LAN-wide communication

e.g. news-server within a company


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Invoker

Repository

InformationObject


X XX

X

48

Base TechnologiesBase Technologies Message-Oriented Middleware

Message-Oriented Middleware:

Peer-to-peer distributed computing Anonymity of communicating objects Asynchronous messaging for applications Decoupling of applications

Infrastructure for reliable scalable performance-oriented

distributed application networks in heterogeneous environment

49

Base TechnologiesBase Technologies JMS: Introduction

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

50

Base TechnologiesBase Technologies JMS: Domain Models

2 Domains: Point-to-Point

Supplier

queue

queue

Consumerpublish

deliver

Supplierqueue

Consumer

deliver

Publish/Subscribe

Supplier

topic

topic

Consumerpublish

Supplier

topic

Consumertopic

topic

Point-to-Point Publish/SubscribeDestination Queue Topic

Durability/Persistence implicit conceptDurableSubscriptionConsumers per message One Many

FIFO/Browsable Topic Hierarchy

51

Base TechnologiesBase Technologies JMS: Architecture

ConnectionFactory<<Interface>>

Connection<<Interface>> Session

<<Interface>>

Message<<Interface>>

MessageListener<<Interface>>creates

creates

MessageProducer<<Interface>>

creates

sendsMessageConsumer

<<Interface>>

Destination<<Interface>>

sends to receives from

creates

creates

receives

notifies

52




<<Interface>>



creates


creates


<<Interface>>



creates

creates

receives

notifies

Alerting ServiceAlerting ServiceConsumerConsumer

Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplierObserver Filter Notifier

JMS ProviderJMS ProviderClientClient ClientClient

53




<<Interface>>



creates


creates


<<Interface>>



creates

creates

receives


Repository

Profile

Repository

EventMessage

Repository

Notification



Observer

Filter

Notifier

notifies

54




<<Interface>>



creates


creates


<<Interface>>



creates

creates

receives


Repository

Profile

Repository

EventMessage

Repository

Notification



Observer

Filter

Notifier

notifies

55




<<Interface>>



creates


creates


<<Interface>>



creates

creates

receives


Repository

Profile

Repository

EventMessage

Repository

Notification



Observer

Filter : JMS System

Notifier

notifies

56




<<Interface>>



creates


creates


<<Interface>>



creates

creates

receives


Repository

Profile

Repository

EventMessage

Repository

Notification



Observer

Filter

Notifier

notifies

57

Base TechnologiesBase Technologies JMS: Message Types


BytesMessage<<Interface>>

MapMessage<<Interface>>

ObjectMessage<<Interface>>

StreamMessage<<Interface>>

TextMessage<<Interface>>

Serialised Java Object

e.g. XML

• Header• Properties• Body

58

Base TechnologiesBase Technologies JMS: Message-Example

MessageHeader: //defined by JMS spec

JMSDestination="Name of the Topic”

JMSDeliveryMode="Persistent"

JMSExpiration="Tue Mar 30 12:06:03 MET 2001”

JMSPriority=5

JMSMessageId=3875784578745

JMSTimeStamp="Tue Jan 30 12:06:03 MET 2001”

Properties:

JMSXUserId="hinze" // predefined by JMS spec

JMS_Oracle_DelayTime=30000 // Vendor-defined

Author="J.Smith" // Application-specific

PubYear=1987

Body: e.g. article fulltext

59

Base TechnologiesBase Technologies JMS: Message Filtering

Consumer associated with Message Selector (=Profile)

Attribute-Value Pairs (SQL 92 ?) Applied against

Message Header Message PropertiesNot against Body!

Message Selector:

author LIKE 'Smith%’

PubYear > 1980

JMSPriority > 5

60

Base TechnologiesBase Technologies JMS: Sample Code

Context messaging = new InitialContext();

topicConnectionFactory =(TopicConnectionFactory)messaging.lookup("TopicConnectionFactory");

stockTopic = (Topic) messaging.lookup("StockTopic");

TopicConnection topicConnection;

topicConnection = topicConnectionFactory.createTopicConnection();

TopicSession session;

session = topicConnection.createSession(false,Session.AUTO_ACKNOWLEDGE));

TopicPublisher publisher;

publisher = session.createPublisher(topic);

TopicSubscriber subscriber;

subscriber = session.createSubscriber(topic, selector);

topicConnection.start();

61


String stockData;

TextMessage message;

message = session.createTextMessage();

message.setText(stockData);

publisher.send(message);

MapMessage message;

message = session.createMapMessage();

message.setString("Name", stockName);

message.setDouble("Value", stockValue);

publisher.send(message);

TextMessage

MapMessage

62


public Class StockListener implements javax.jms.MessageListener {

void onMessage(Message message) {

}

}

StockListener myListener = new StockListener();

subscriber.setMessageListener(myListener);

TextMessage message;

message = (TextMessage) subscriber.receive();

asynchronous

synchronous / polling

63

Base TechnologiesBase Technologies JMS: Valuation

Valuation: Scalability: Implementation dependent Cardinality: Point to Point N:1, Pub/Sub: N:M Guaranteed delivery possible with durable

subscribers/messages Filtering and Personalization

only messages, object observation not included Events observed by supplier Time-events/event combinations not supported poor filter expressiveness (simple attribute-value matching)

Base technology, no full ENS implementation

64

Base TechnologiesBase Technologies Active Databases: Motivation

Assumption: Data stored in DB Architectures:

Application?

DBMSDB

Amazon Webinterface

New book

!

1.Variant

New book2.Variant

DBMSDB Application

Amazon Webinterface

ENS

65

Base TechnologiesBase Technologies Active Databases: Motivation

Idea: Active Databases (ADB)

ADB: Relational or object oriented data base observe system state Triggers actions in reaction on (system)events

New book

aDBMSDBApplication

66

Base Technologies Base Technologies Active Databases

Conceivable events: Database state transitions Temporal events Abstract or external events

Examples: Relational: (Sybase, Ingres) Object-relational: Postgres Object-oriented: Sentinel, Ode [agr89], Samos[dit00]

Simple triggers also in SQL3 (Oracle)

67


Common argumentation: central management of semantic in DB optimization of processing higher DBMS functionality support of time-dependent requirements

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)

68


Extension of triggers: active database rules ADB rules defined as ECA-rules:

Event - Condition - Action

Different implementations Sybase: event and action part Postgres: separate event, condition, action parts Sentinel: separate event, condition, action parts

When <event expression >If <condition expression>Then <action>Attributes {priority,...}

When <event expression >If <condition expression>Then <action>Attributes {priority,...}

69


Trigger in SQL 3: simplified integrity rules simple consitions

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

create trigger RuleName

on insert|delete|update on RelationName [references[old as OldName,

new as NewName]] when Condition for each row|statement SQLStatement

create trigger RuleName

on insert|delete|update on RelationName [references[old as OldName,

new as NewName]] when Condition for each row|statement SQLStatement

70


Example: count number of inserted books

create trigger BookCountPlus on insert on Book referencing old as Old new as New update BookCount set New.Number = Old.Number+1 for each row

create trigger BookCountMinus ...

set New.Number = Old.Number-1

create trigger BookCountPlus on insert on Book referencing old as Old new as New update BookCount set New.Number = Old.Number+1 for each row

create trigger BookCountMinus ...

set New.Number = Old.Number-1

71


Example: check that the budget holds just once per update

create trigger BudgetTest

before update on Salary for each statement when (SumSalary > 200.000) signal „Budget overflow!“

create trigger BudgetTest

before update on Salary for each statement when (SumSalary > 200.000) signal „Budget overflow!“

72

Base Technologies Base Technologies Trigger in Oracle

Trigger in Oracle8i: similar to stored procedures written in PL/SQL, Java (stored internally), or C (stored externally)

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

73


Trigger in Oracle8i:

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;

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;

74

DECLARE – OptionalVariables, cursors, user-defined exceptions

BEGIN – MandatorySQL statementsPL/SQL statements

EXCEPTION – OptionalActions to perform whenerrors occur

END; – Mandatory


DECLARE v_variable VARCHAR2(5);BEGIN SELECT column_name INTO v_variable FROM table_name;EXCEPTION WHEN exception_name THEN ...END;

DECLARE v_variable VARCHAR2(5);BEGIN SELECT column_name INTO v_variable FROM table_name;EXCEPTION WHEN exception_name THEN ...END;

PL/SQL Block Structure:

Example:

PL/SQL variable

Bind variable: declared in host environment (e.g.SQL*Plus),reference in PL/SQL: :variable

75


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

76


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

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 ;

77


Example of a Referential Integrity Trigger

CREATE OR REPLACE TRIGGER CUSTOMER_DEL_CHECK

BEFORE DELETE ON CUSTOMER FOR EACH ROW 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;

CREATE OR REPLACE TRIGGER CUSTOMER_DEL_CHECK

BEFORE DELETE ON CUSTOMER FOR EACH ROW 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;

Non-PL/SQL variable

78


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;

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;

79


Triggers vs. integrity constraints:

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

Reason: integrity constrains are “all SQL” - easier, less errors triggers are more complex to evaluate better performance since better optimization

80


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;

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;

81


Compiling Triggers: compilation different to PL/SQL blocks

PL/SQL block is compiled each time loaded into memory: 1.Syntax checking + parse tree generation 2.Semantic checking: Type checking etc. on the parse tree 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)

82


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

Recompiling Triggers manually: ALTER TRIGGER statement

ALTER TRIGGER trigger_name COMPILE

83


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_tabFOR EACH ROWBEGIN INSERT INTO Emp_tab@Remote -- <- compilation fails here when VALUES ('x'); -- dblink is inaccessibleEXCEPTION WHEN OTHERS THEN INSERT INTO Emp_log VALUES ('x');END;

CREATE OR REPLACE TRIGGER Example

AFTER INSERT ON Emp_tabFOR EACH ROWBEGIN INSERT INTO Emp_tab@Remote -- <- compilation fails here when VALUES ('x'); -- dblink is inaccessibleEXCEPTION WHEN OTHERS THEN INSERT INTO Emp_log VALUES ('x');END;

84


Modifying Triggers replace with new definition, or drop and rerun create trigger

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

ALTER TRIGGER trigger_name DISABLE | ENABLE

Disable or Re-enable a database trigger:

ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

Disable or Re-enable all triggers for a table:

85


Trigger publish events to applications:

support of database events: DML on tables, system events on Database and schema

users specify procedure to be run when the event occurs

uses Advanced Queueing publish/subscribe engine(based on JMS)

86


Drawback of Oracle/SQL3 Triggers: no time events restricted abstractions, definition complicated, error source difficult validation SQL3: 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

87


Repository

Profile

Repository

EventMessage

Repository

Notification

SupplierSupplier

Repository

InformationObject

Filter Notifier

Functionality for ENS: Object/Event/Profile/Notification Repository Trigger for AS (active observer on suppliers side)


Valuation (DB with SQL:99 Triggers) Often no sophisticated profiles possible No event patterns, events from different sources Restricted actions

Use as simple ENSor in ENS-Application:

88


„real“ active Databases

Basic ECA-Rule:

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. [dit00]

DEFINE RULE rule_nameON event_clauseIF cond_clauseDO action clauseexecution constraints

DEFINE RULE rule_nameON event_clauseIF cond_clauseDO action clauseexecution constraints

89

DEFINE EVENT EventInv_BankAccountTransferTIMES (3, Kunde.AccountInsert):SAME OBJECT

DEFINE RULE EventInv_BankAccountTransfer ON EventInv_BankAccountTransfer DO { S = BankAccount.saldo + Inv_Account1.saldo + Inv_Account1.saldo; Insert_1 = (S* BankAccount.ratio * 0.01)- BankAccount.saldo; Insert_2 = (S* Inv_Account1.ratio * 0.01) - Inv_Account1.saldo; Insert_3 = (S* Inv_Account2.ratio * 0.01) - Inv_Account2.saldo;

AccountInsert(Insert_1); Inv_Account1Insert(Insert_2); Inv_Account1Insert(Insert_3);

}

DEFINE EVENT EventInv_BankAccountTransferTIMES (3, Kunde.AccountInsert):SAME OBJECT

DEFINE RULE EventInv_BankAccountTransfer ON EventInv_BankAccountTransfer DO { S = BankAccount.saldo + Inv_Account1.saldo + Inv_Account1.saldo; Insert_1 = (S* BankAccount.ratio * 0.01)- BankAccount.saldo; Insert_2 = (S* Inv_Account1.ratio * 0.01) - Inv_Account1.saldo; Insert_3 = (S* Inv_Account2.ratio * 0.01) - Inv_Account2.saldo;

AccountInsert(Insert_1); Inv_Account1Insert(Insert_2); Inv_Account1Insert(Insert_3);

}


Example:After 3 inserts on the same account transfer the money

according defined strategy to 3 (depot) accounts

90


Management of rules: define parts separately

delete rule or parts

switch status of rule

e.g disable rule for bulk load or backup

DELETE RULE rule_nameDELETE EVENT event_nameDELETE CONDITION cond_nameDELETE ACTION actions_name

DELETE RULE rule_nameDELETE EVENT event_nameDELETE CONDITION cond_nameDELETE ACTION actions_name

DEFINE EVENT event_nameDEFINE CONDITION cond_nameDEFINE ACTION actions_name

DEFINE EVENT event_nameDEFINE CONDITION cond_nameDEFINE ACTION actions_name

DISABLE RULE rule_nameENABLE RULE rule_nameDISABLE RULE rule_nameENABLE RULE rule_name

DISABLE RULE R1EVERY WEEK [Fr 18:00, Mo 08:00]

DISABLE RULE R1EVERY WEEK [Fr 18:00, Mo 08:00]

91

Base Technologies Base Technologies Active Databases vs Triggers

Difference to triggers ?

Complex event definitions (e.g. on several tables) time events composite events external events conflict resolution strategy various coupling modes ...

92

Base Technologies Base Technologies Active Databases: Event Specification

Event Specification Event class/type: rules define event types Event Instance:

actual occurrence of event 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

93


Primitive Events: DB events = begin or end of an DB operation execution DB

(select, insert, delete update in relational DBMS)

DBMS events = begin or end of operations on DB(transaction start, end, abort; user login, ...)

Time events = absolute, periodically repeated or relative definition of point in time

Abstract events = external events in system applications, defined by identifier, explicitly announced to the DB

DEFINE EVENT E1 BEFORE INSERT

DEFINE EVENT E2 BankAccountInsert.COMMIT_TA

DEFINE EVENT E3 EVERY 10 DAYS 16:15

DECLARE AccountCheckRAISE AccountCheck

94


Composite events:based on primitive events, defined by event algebra

Disjunction E = E1 | E2 : E1 or E2 occurs, E.time=min(E1.time or E2.time)

Sequence E = E1 ; E2 : E2 occurs after E1 (E1.time < E2.time), E.time=E2.time

Konjunction E= E1 , E2 : E1 and E2 occur, E.time = max(E1,E2)

Negation NOT E : E did not occur within interval [t_start,t_end], E.time=t_end

Reduction: *E, *E WITHIN I, TIMES(n,E), TIMES(n_min,n_max,E) WITHIN I

95


Semantics of Event Order:

one trace for DBMS / transaction / user ? Rule execution needs clear semantic State: rule executed differently in different aDBMS

Rule: „fire if e1;e2“ Traces:

e1 e3 e2 - fire ?e1 e1 e2 - fire once or twice ?e1 e2 e2 - fire once or twice ?e1 e2 e1 e2 - fire two or three times ?

Rule: „fire if e1;e2“ Traces:

e1 e3 e2 - fire ?e1 e1 e2 - fire once or twice ?e1 e2 e2 - fire once or twice ?e1 e2 e1 e2 - fire two or three times ?

96

Base Technologies Base Technologies Active Databases: Condition and Action

Condition: predicate over database state defined as SQL-query, WHERE-clause, method or

application-procedure 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)

97

Base Technologies Base Technologies Active Databases: Condition and Action

Example:

DEFINE EVENT EventAccountInsert Kunde.AccountInsert

DEFINE EVENT EventSavings( NOT EventAccountInsert

WITHIN [EventAccountInsert, EventAccountInsert+ 1 MONTH]): SAME OBJECT

DEFINE RULE SavingsProblemsON EventSavingsDO { alerter (“Warning: You have not saved much”)

}

DEFINE EVENT EventAccountInsert Kunde.AccountInsert

DEFINE EVENT EventSavings( NOT EventAccountInsert

WITHIN [EventAccountInsert, EventAccountInsert+ 1 MONTH]): SAME OBJECT

DEFINE RULE SavingsProblemsON EventSavingsDO { 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.

98

Base Technologies Base Technologies Active Databases: Rule execution

Active vs. Deductive databases[wid93]: 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)

ECA: describe actions triggered by events

orthogonal concepts possible implementation within single system possible use of ECA to implement deductive rules:DEFINE RULE Rule1

ON query(p(X)) IF true DO query (q(X,Y)DEFINE RULE Rule2

ON answer(q(X,Y)) IF true DO query(s(Y))

99

Base Technologies Base Technologies Active Databases: Rule execution

Rule execution: Model:

activity 1: infinite loop for event detection and rule triggering activity 2:

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

if triggered rule

choose rule R

evaluate condition C of R

if C true, do action A of R

100

Base Technologies Base Technologies Active Databases: Conflict Resolving

Conflict resolving [dia97]:

Conflict: More than one rule for one event fundamental to controlling of aDBMS strongly influences result

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

101


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

Strategies: random rule order order by criteria such as creation time, ... execution priority defined by user concept-based rules: (different priority levels based on

functionality)

State: no flexible conflict resolution mechanisms

102


Example: execution priority defined by user: supported in most aDBMS for each rule, defined when created

Problems: different priority criteria by authors

Example: integrity constraints

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

Rule: if constraint violated reject update Criterion: order based on complexity of constraint (performance!)Problem: user-dependency, multi-user environment problematic

103


Coupling modes: ECA-rules and Transactions

Rule execution aTA=rTA

Event receive commit execute commit

Event

aTA

Rule execution rTAcreate transaction

contextual coupling (decoupled)

temporal coupling (deferred)

Event

Rule execution aTA=rTA

temporal coupling (immediate)

104


Coupling modes: E-C / C-A (any combination possible)

Action

immediate/deferred

Condition evaluation

deferred/decoupled


Action

deferred/decoupled


Action

105

Base Technologies Base Technologies Active Databases: Execution Modes

Execution modescascading rule execution:

When is the new rule executed ?

iterative execution: complete action insert new rules (according priority) in list of “waiting” rules

recursive execution: interrupt action execute all immediate rules

events occur during action and trigger new rules

R1

E1

R1

R3

E2

R4

R1

E1

R1

R3

E2

R4

106

Base Technologies Base Technologies Active Databases: Execution Modes

Cascading Rules Problem: no termination of recursive rule triggering

avoid during rule definition complicated, needs methodology models: finite state machine, petri-nets and tool-support, e.g. rule analyzer

use system-internal restrictions syntactical rule restriction, abort if #cycles > threshold

R1

E1

R1

R3

E2

R4

R5

E4

R6

R7

E5

R8

107


Optimization of rules Situation: temporally persistent rules, evaluated many

times Problem:

potentially large set of predefined queries possible overhead on every event

Approaches: conventional query optimization grouping of rules (multiple query optimization) materializing intermediate results rule buffering strategies for real-time applications parallelism

108


Evaluation Criteria of aDBMS [cha93]: Rule expressiveness

Supported events, Event operators

Execution semantics Coupling modes, Cascaded rule execution, Multiple rule

execution, Priorities,

Optimization of rules Architectural approach

Postgres - 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

Postgres - 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

109

Base Technologie 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

RDBMS: restricted events (only DB operations) different approaches for rule execution specialized for integrity constrains, view update, ...

ODBMS: most concepts first proposed in HiPAC project [day88] support of special events in oo-context method events, internal and external rules for classes Encapsulation and Inheritance for rules

110


Do we need all that active features within the DB ?New trend: unbundling [gep98] Idea:

DBMS with simple basic functionality additional features in separate cooperating components,

e.g Event/Reaction-Service possible interaction of DBMS and service:

Event/ReactionService

DBMS

DB

Eventoccures

Executeaction

Evaluate condition

result

Event-ActionServive

(extension of ENS)

111

BibliographyBibliography

[cha93] Chakravarthy: “A comparative evaluation of active relation databases”, TechRep: UF-CIS-TR-93-002, University of Florida, 1993

[day88] Dayal et al: “The HiPAC Project: Combining Active Databases and Timing Constrains”, ACM Sigmod Record 17(1), March 1988

[dit00] Dittrich & Gatziu: “Aktive Datenbanksysteme”, dpunkt.verlag, 2000

[gep98] Geppart and Dittrich: “Bundling. A new construction paradigm for persistent systems ?”, Networking and IS Journal 1(1), June 1898

[wid93] 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)

112

Next semester (SS01) Seminar about event-based web-applications (and XML)

Next Wednesday Prof.Schweppe: web and replication

Now Kilian Lenz: Talk about RSS

event-based notification vl web-basierte informationssysteme

Documents