Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Track Database modifications.
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
--- Begin Message ---
  • From: <Saved by Windows Internet Explorer 8>
  • Date: Fri, 20 Aug 2010 16:13:29 -0400
Title: Database Change Notification and TopLink Cache Invalidation How-To Document

This is Google's cache of http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbChangeNotification/index.htm. It is a snapshot of the page as it appeared on Aug 3, 2010 22:11:00 GMT. The current page could have changed in the meantime. Learn more

These search terms are highlighted: toplink These terms only appear in links pointing to this page: dbchangenotification  

Database Change Notification and TopLink Cache Invalidation

How-To Document

January 2006

After reading this How-To document, you will be able to:

  • Implement database change notification.

  • Implement TopLink application cache invalidation.

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:

  • <ORACLE_HOME> – the directory in which you installed TopLink.

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.

  1. Connect as a DBA: 

    CONNECT SYSTEM/MANAGER

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

  3. Allow the dbuser to call the enqueue method in PL/SQL: 

    GRANT AQ_USER_ROLE, EXECUTE ON DBMS_AQ TO dbuser;

  4. Create the app user: 

    CREATE USER app IDENTIFIED BY password DEFAULT TABLESPACE users TEMPORARY
    TABLESPACE temp;

  5. Grant the app user access to all database objects in dbuser schema: 

    GRANT SELECT, INSERT, UPDATE, DELETE ON dbuser.address to app;
...

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

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

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

  1. Connect as a queue owner: 

    CONNECT aqadm/aqadm

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

  1. Connect as a TopLink user of the schema where the TopLink Employee example is set up: 

    CONNECT dbuser/dbuser

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

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

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

References

Oracle TopLink Cache Invalidation How-To document

Oracle9i SQL Reference. Release 2 (9.2)

Oracle9i PL/SQL User's Guide and Reference. Release 2 (9.2)

Oracle9i Application Developer's Guide - Advanced Queuing. Release 2 (9.2)

Oracle9i Supplied PL/SQL Packages and Types Reference. Release 2 (9.2)

Oracle9i Supplied Java Packages Reference Release 2 (9.2)

Sun Java JMS Docs Web site

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.


--- End Message ---

Back to the top