| Re: [eclipselink-users] Track Database modifications. |
> 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. > Thanks for the answer, but this is not exactly what I want. I only need to know who do the change and what did he change. Also, I need an eclipselink only solution (im using Derby to develop a DB2 to deploy). I read something about History Policy, but de documentation is small. someone can clarify that? > 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\tes > > ts\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 > > > > ------------------------------------------------------------------------ > > > > Subject: > > Database Change Notification and TopLink Cache Invalidation How-To > > Document > > From: > > <Saved by Windows Internet Explorer 8> > > Date: > > Fri, 20 Aug 2010 16:13:29 -0400 > > > > > > This is Google's cache of > > http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbCh > > angeNotification/index.htm. It is a snapshot of the page as it appeared > > on Aug 3, 2010 22:11:00 GMT. The current page > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbC > > hangeNotification/index.htm> could have changed in the meantime. Learn > > more > > <http://www.google.com/intl/en/help/features_list.html#cached> > > > > Text-only version > > <http://webcache.googleusercontent.com/search?q=cache:PaXfY6MWyRUJ:www.or > > acle.com/technology/products/ias/toplink/technical/tips/DbChangeNotificat > > ion/index.htm+TopLink+DbChangeNotification&hl=en&gl=us&strip=1> 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 > > <http://www.oracle.com/technology/software/products/ias/index.html> > > . > > > > * > > > > The Java source code > > <http://www.oracle.com/technology/products/ias/toplink/technical/ti > > ps/DbChangeNotification/CacheInvalidator.zip> 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 > > > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbC > > hangeNotification/index.htm#CJHDDDIE> or the |postLogin()| event handler > > in Example: postLogin event handler > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/Db > > ChangeNotification/index.htm#CJHFDDHG>. > > > > > > 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 > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbC > > hangeNotification/index.htm#CJHJFCBD> 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 = "jdbc:oracle:thin:@localhost:1521:orcl" > > 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 > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/DbC > > hangeNotification/CacheInvalidator.zip>. > > > > References > > > > Oracle *TopLink* Cache Invalidation How-To document > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/cac > > heInvalidation/index.htm> > > > > > > Oracle9i SQL Reference. Release 2 (9.2) > > <http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/toc.htm> > > > > Oracle9i PL/SQL User's Guide and Reference. Release 2 (9.2) > > <http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/toc.htm> > > > > Oracle9i Application Developer's Guide - Advanced Queuing. Release 2 > > (9.2) > > <http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96587/toc.htm> > > > > Oracle9i Supplied PL/SQL Packages and Types Reference. Release 2 (9.2) > > <http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/toc.htm> > > > > Oracle9i Supplied Java Packages Reference Release 2 (9.2) > > <http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96609/toc.htm> > > > > Sun Java JMS Docs Web site <http://java.sun.com/products/jms/docs.html> > > || > > > > ------------------------------------------------------------------------ > > > > 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. > > > > ------------------------------------------------------------------------ > > Copyright © 2006, Oracle. All rights reserved. > > <http://www.oracle.com/technology/products/ias/toplink/technical/tips/sup > > port/html/cpyr.htm> > > > > > > ------------------------------------------------------------------------ > > > > _______________________________________________ > > eclipselink-users mailing list > > eclipselink-users@xxxxxxxxxxx > > https://dev.eclipse.org/mailman/listinfo/eclipselink-users -- Ing. José Arcángel Salazar Delgado Net Trace: www.nettracemexico.com Av 27 #6 Col. Venustiano Carranza. Boca del río, Ver. Tel. Oficina 9-27-54-78
Attachment:
signature.asc
Description: This is a digitally signed message part.