I forgot to mention that the doc. attached to the original message that
describes DbChangeNotification technique using triggers - unfortunately
it's no longer on Oracle site, fortunately Google cached it.
On 8/20/2010 4:29 PM, Andrei Ilitchev wrote:
You can use triggers in Oracle db that would send JMS messages (using
AQ) which Eclipselink-using application could receive and invalidate
the altered object - that will cause Eclipselink next time the object
is accessed directly to refresh it from the db.
Example of implementing this strategy could be found in
eclipselink.extension.oracle.test\src\org\eclipse\persistence\testing\tests\dbchangenotification
- though it would not run standalone, but only in TestingBrowser
(Eclipselink testing framework).
Moreover, Google search for DbChangeNotification brought
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm
-
apparently there is a (relatively new) feature in Oracle jdbc driver
for that.
I didn't investigate in any depth, but at the first glance it seems
similar to the above mentioned technique.
Good luck,
Andrei
On 8/20/2010 3:41 PM, José Arcángel Salazar Delgado wrote:
Hello folks.
What is the best way to track Database modifications with eclipselink?
thanks for the help.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Database Change Notification and TopLink
Cache Invalidation
|
How-To Document
January 2006
After reading this How-To document, you will be able to:
Software Requirements
-
Oracle Database 10g, or higher.
-
Oracle TopLink
10g Release 3 (10.1.3), available from OTN.
-
The Java
source code for the TopLink
Cache Invalidation component.
Conventions
The following conventions are used in this document:
Introduction
This How-To document provides information on a TopLink
stale data problem and a two-part solution to address the stale data
problem.
TopLink
Stale Data Problem
A common procedure among database applications is to cache data
during application operation. Sometimes, this can result in some cached
application data becoming stale as another application may change the
data in the database, thus rendering the cached data obsolete. This
could happen without the cached application knowing about it.
This How-To document provides an outline of the required setup
procedures using the TopLink
Employee example to illustrate.
Database Change Notification and Application Cache Invalidation
Solution
A simple two-part solution can be used to minimize, and in some
cases eliminate, occurrences of the stale data problem as follows:
-
Database change notification: whereby the database notifies
applications about changed data.
-
Application cache invalidation: whereby the notified
application marks the corresponding cached data as invalid. This
signals to the application that the data needs to be re-read into the
cached application from the database.
Guidelines to which a proper database change notification solution
should adhere include:
-
The method of providing notification should not be
application-dependent and should allow for multiple applications to use
it simultaneously.
-
Notification should be transaction-based: this implies that
the notification is not issued until the change to the database is
committed.
-
Some applications, including TopLink
use the cache not only to store existing database data, but also to
write new data into the database. It is necessary for applications with
write capability to have an option not to be notified of database
changes caused by its write operations.
Note:
If notification messages are sent too frequently, the performance of
the database and notified application(s) operation may be severely
degraded. |
Notification and TopLink
Cache Invalidation Solution
This section provides information on database change notification
and TopLink
cache invalidation solution.
Database Change Notification
Oracle Database 10g and subsequent
versions use triggers and Java Message Service (JMS) messaging
implemented by Oracle Advanced Queuing (AQ) as follows:
-
On update or deletion of a database row, a trigger creates a
message that contains the name of the table and the row's primary key
value(s) and places the message into the queue.
-
After a transaction is committed to the database, the
corresponding JMS message is delivered to all subscribers
(applications).
TopLink
Cache Invalidation
An application enabled by TopLink
extracts the table name and the primary key value(s) from the JMS
message and invalidates the corresponding object in the TopLink
cache. Note that TopLink
10g Release 3 (10.1.3) or higher is
required.
Database Setup
This section provides setup information for the Oracle database
Users, Advanced Queue, and Triggers.
Users
-
User dbuser contains the tables that will send
change notification messages.
-
An application that initiated a database change should not
receive notification of this change. One method of enforcing this is to
have different applications use different user names. For example, app1 ,
app2 ,.... The different user names would not use
their default schemas but would access the dbuser schema
instead.
-
User aqadm contains the queue through which
notification messages are sent.
The following procedure assumes that the dbuser user
already exists and that the TopLink
Employee example is set up and populated in its default schema.
-
Connect as a DBA:
CONNECT SYSTEM/MANAGER
-
Enable the dbuser to enqueue (send) the messages
into any queue:
EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => ENQUEUE_ANY',grantee
=> 'dbuser',admin_option => FALSE);
-
Allow the dbuser to call the enqueue method in
PL/SQL:
GRANT AQ_USER_ROLE, EXECUTE ON DBMS_AQ TO dbuser;
-
Create the app user:
CREATE USER app IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY
TABLESPACE temp;
-
Grant the app user access to all database
objects in dbuser schema:
GRANT SELECT, INSERT, UPDATE, DELETE ON dbuser.address to app;
...
-
Enable the app user to dequeue (receive) the
messages from any queue:
EXEC DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege => 'DEQUEUE_ANY',
grantee => 'app', admin_option => FALSE);
-
Allow the app user to dequeue JMS messages in a
Java application:
GRANT AQ_USER_ROLE, EXECUTE ON DBMS_AQIN, EXECUTE ON DBMS_AQJMS TO app;
-
Create aqadm - the user to own the queue:
CREATE USER aqadm IDENTIFIED BY password DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT,RESOURCE,AQ_ADMINISTRATOR_ROLE TO aqadm;
Advanced Queue
The notify_queue allows for multiple consumers
(applications that receive messages) and sends Oracle's implementations
of JMS messages.
-
Connect as a queue owner:
CONNECT aqadm/aqadm
-
Create and start the queue:
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'notify_queue_table',
multiple_consumers => TRUE, queue payload_type => 'SYQ.AQ$_JMS_TEXT_
MESSAGE');
EXEC DBMS_AQADM.CREATE_QUEUE (queue_name => 'notify_queue',queue_table =>
'notify_queue_table');
EXEC DBMS_AQADM.START_QUEUE (queue_name => 'notify_queue');
Alternatively, if there is only a single application to be
notified, the queue table may be created with multiple_consumers=>FALSE .
Triggers
A trigger is created for each table that will send one or more
notification messages. You can create two helper functions first to
simplify the trigger code.
-
Connect as a TopLink
user of the schema where the TopLink
Employee example is set up:
CONNECT dbuser/dbuser
-
Create the notify_make_msg function provided.
This function creates the message and populates it with the application
ID (app1 , app2 , ... found in USER )
and the table name:
CREATE OR REPLACE FUNCTION notify_make_msg (table_name VARCHAR2) RETURN
SYS.AQ$_JMS_TEXT_MESSAGE AS msg SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
msg.set_string_property('APP', USER);
RETURN msg;
END;
-
Create the notify_enqueue procedure provided.
This procedure sends the message to notify_queue . TopLink
throws an exception, but the message may be ignored if there are no
recipients to receive it:
CREATE OR REPLACE PROCEDURE notify_enqueue (msg SYS.AQ$_JMS_TEXT_
MESSAGE) AS queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
msg_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
msg_id RAW(16);
no_recipients_for_message EXCEPTION;
PRAGMA EXCEPTION_INIT(no_recipients_for_message, -24033);
BEGIN
DBMS_AQ.ENQUEUE(queue_name => 'notify_enqueue', enqueue_options =>
queue_options, message_properties => msg_properties, payload => msg,
msgid => msg_id);
EXCEPTION
WHEN no_recipients_for_message THEN NULL;-- ignore
END;
-
Create a trigger for the employee table:
CREATE OR REPLACE TRIGGER notify_employee AFTER UPDATE OR DELETE ON
employee FOR EACH ROW DECLARE msg SYS.AQ$_JMS_TEXT_MESSAGE;
BEGIN
--a single call per trigger, pass the table name
msg := NOTIFY_MAKE_MSG('EMPLOYEE');
--a call for every primary key field
msg.set_double_property('EMP_ID', :old.EMP_ID);
NOTIFY_ENQUEUE(msg);
END;
If there is only a single application that requires
notification, the trigger need not enqueue the message in the event
that it was caused by the application:
IF USER = 'app' THEN
RETURN;
END IF;
After the transaction is committed, the corresponding JMS message
is delivered to all database subscribers.
Application Setup
This section provides information on how to connect the
application enabled by TopLink
and information on JMS and TopLink
cache invalidation.
Connecting the Application
The application enabled by TopLink
must be amended to connect using the app user. However,
it will work with database objects in the dbuser schema
by setting the user to dbuser via either the preLogin()
event handler in Example:
preLogin event handler or the postLogin() event
handler in Example:
postLogin event handler.
preLogin event handler
//The pre-login method
public void preLogin(SessionEvent event){
event.getSession().getLogin().setTableQualifier("dbuser");
}
postLogin event handler
//The post-login method
public void postLogin(SessionEvent event){
event.getSession().executeNonSelectingCall(new SQLCall("ALTER SESSION SET CURRENT_SCHEMA = dbuser"));
}
JMS
An application obtains ConnectionFactory and Destination
by invoking a Java Naming and Directory Interface (JNDI) lookup. The
code example in Example:
How to obtain ConnectionFactory and Destination using the oracle.jms
package illustrates how ConnectionFactory and Destination
may be obtained with the use of the oracle.jms package.
Note: the condition defined in the selector string
filters out messages generated by the app user.
How to obtain ConnectionFactory and
Destination using the oracle.jms package
import javax.jms.*;
import oracle.jms.*;
String url = ""
String user = "app";
String password = "app";
String selector = "(APP IS NULL) OR (APP <> " + "'" + user + "')";
// multiple_consumers => TRUE case:
TopicConnectionFactory factory = AQjmsFactory.getTopicConnectionFactory(url,null);
TopicConnection conn = factory.createTopicConnection(user, password);
TopicSession topicSession = conn.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);
Topic topic = ((AQjmsSession) topicSession).getTopic("aqadm", "notify_queue");
Subscriber subscriber = topicSession.createSubscriber(topic, selector, false);
….
// multiple_consumers => FALSE case:
QueueConnectionFactory factory = AQjmsFactory.getQueueConnectionFactory(url,null);
QueueConnection conn = factory.createQueueConnection(user, password);
QueueSession queueSession = conn.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
Queue queue = ((AQjmsSession) queueSession).getQueue("aqadm", "notify_queue");
QueueReceiver receiver= queueSession.createReceiver(queue, selector);
….
TopLink
Cache Invalidation
To process change notification messages, an application enabled
with TopLink
must prepare two maps: the first maps a table name to a class, the
second maps table names to a vector of primary key fields' names.
When a notification message is received, the application:
-
Extracts the table name from it.
-
Searches the maps for the corresponding class and primary key
fields' names.
-
Extracts the primary key value from the message.
-
Invalidates the object in the TopLink
cache.
A sample CacheInvalidator Java class file that does all of the
above may be downloaded
here.
Oracle TopLink,
10g Release 3 (10.1.3)
The Programs (which include both the software and documentation)
contain proprietary information; they are provided under a license
agreement containing restrictions on use and disclosure and are also
protected by copyright, patent, and other intellectual and industrial
property laws. Reverse engineering, disassembly, or decompilation of
the Programs, except to the extent required to obtain interoperability
with other independently created software or as specified by law, is
prohibited.
The information contained in this document is subject to change
without notice. If you find any problems in the documentation, please
report them to us in writing. This document is not warranted to be
error-free. Except as may be expressly permitted in your license
agreement for these Programs, no part of these Programs may be
reproduced or transmitted in any form or by any means, electronic or
mechanical, for any purpose.
If the Programs are delivered to the United States Government or
anyone licensing or using the Programs on behalf of the United States
Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related
documentation and technical data delivered to U.S. Government customers
are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and
agency-specific supplemental regulations. As such, use, duplication,
disclosure, modification, and adaptation of the Programs, including
documentation and technical data, shall be subject to the licensing
restrictions set forth in the applicable Oracle license agreement, and,
to the extent applicable, the additional rights set forth in FAR
52.227-19, Commercial Computer Software—Restricted Rights (June 1987).
Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065
The Programs are not intended for use in any nuclear, aviation,
mass transit, medical, or other inherently dangerous applications. It
shall be the licensee's responsibility to take all appropriate
fail-safe, backup, redundancy and other measures to ensure the safe use
of such applications if the Programs are used for such purposes, and we
disclaim liability for any damages caused by such use of the Programs.
Oracle, JD Edwards and PeopleSoft are registered trademarks of
Oracle Corporation and/or its affiliates. Other names may be trademarks
of their respective owners.
The Programs may provide links to Web sites and access to content,
products, and services from third parties. Oracle is not responsible
for the availability of, or any content provided on, third-party Web
sites. You bear all risks associated with the use of such content. If
you choose to purchase any products or services from a third party, the
relationship is directly between you and the third party. Oracle is not
responsible for: (a) the quality of third-party products or services;
or (b) fulfilling any of the terms of the agreement with the third
party, including delivery of products or services and warranty
obligations related to purchased products or services. Oracle is not
responsible for any loss or damage of any sort that you may incur from
dealing with any third party.
Alpha and Beta Draft documentation are considered to be in
prerelease status. This documentation is intended for demonstration and
preliminary use only. We expect that you may encounter some errors,
ranging from typographical errors to data inaccuracies. This
documentation is subject to change without notice, and it may not be
specific to the hardware on which you are using the software. Please be
advised that prerelease documentation in not warranted in any manner,
for any purpose, and we will not be responsible for any loss, costs, or
damages incurred due to the use of this documentation.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
|