Triggers in TimesTen (XLA Application)

TimesTen supports PL / SQL (procedures, functions, packages, etc.), but there is no support for triggers, because triggers adversely affect performance. But what if you need to implement trigger logic?

The answer is to write an XLA application. You can write in C or Java, anyone closer.
Below I will describe an example using java :)



The Java Developer's Guide says:

“You can use the TimesTen JMS / XLA API (JMS / XLA) to monitor TimesTen for
changes to specified tables in a local data store and receive real -time notification of these changes. One of the purposes of JMS / XLA is to provide a high-performance, asynchronous alternative to triggers. ”

Those. you can write a java application that can use the JMS / XLA API to receive messages (in asynchronous mode) about changes in TimesTen. JMS / XLA uses the JMS publish-subscribe interface to access XLA changes. Read more about JMS here (http://download.oracle.com/javaee/1.3/jms/tutorial).

Next, try to create such an application.

Initially, create objects in TimesTen.
[oracle @ tt1 xla] $ ttisql dbxla

Copyright 1996-2010, Oracle. All rights reserved.
Type? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=dbxla";
Connection successful: DSN=dbxla;UID=oracle;DataStore=/u01/app/oracle/datastore/dbxla;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1/lib/libtten.so;PermSize=32;TempSize=50;TypeMode=0;PLSQL=0;CacheGridEnable=0;
(Default setting AutoCommit=1)
Command> CREATE USER oratt IDENTIFIED BY oracle;
User created.
Command> grant create session, create table, XLA to oratt;
Command> connect "DSN=dbxla;UID=oratt;PWD=oracle;";
Connection successful: DSN=dbxla;UID=oratt;DataStore=/u01/app/oracle/datastore/dbxla;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.1/TimesTen/tt1/lib/libtten.so;PermSize=32;TempSize=50;TypeMode=0;PLSQL=0;CacheGridEnable=0;
(Default setting AutoCommit=1)
con1: Command> create table xlatest ( id   NUMBER NOT NULL PRIMARY KEY,
             >                        name VARCHAR2(100) );
con1: Command> 

Now, create a bookmark. XLA bookmarks are used to mark the reading position in transaction logs. Use this bullet bookmark to track changes in the xlatest table.
con1: Command> call ttXlaBookmarkCreate ('bookmark');
con1: Command>

Next, we determine which table changes we will observe. To do this, call the ttXlaSubscribe procedure. In this case, we will observe the changes with the xlatest table using the bookmark bookmark.
con1: Command> call ttXlaSubscribe ('xlatest', 'bookmark');
con1: Command>

Next, let's move on to setting up the application.
To connect to XLA, you need to connect to the JMS Topic, which is associated with a specific TimesTen database. The JMS / XLA configuration file provides a binding between the topic name and the database. By default, the application searches for the given file, called jmsxla.xml, in the current directory, but if desired, you can determine a different name and location for this file (see the documentation).

In this case, I use the following jmsxla.xml file:

As you can see, I linked the xlademo topic name to the dbxla database.

Now let's get down to writing java Applications.
Initially, initialize the context.
Hashtable env = new Hashtable ();
env.put (Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory");
InitialContext ic = new InitialContext (env);

Next, use the JMS connection factory to connect to the XLA. After that, we call the start () method on the connection to activate sending messages. After that, using this connection we create a session.
private javax.jms.TopicConnection connection; / ** JMS connection * /
private TopicSession session; / ** JMS session * /
...
TopicConnectionFactory connectionFactory = (TopicConnectionFactory) ic.lookup ("TopicConnectionFactory");
connection = connectionFactory.createTopicConnection ();
...
// get Session
session = connection.createTopicSession (false, Session.AUTO_ACKNOWLEDGE);
...

Also, when creating a session, you must specify the transactional nature of the session and the type of acknowledgment modes.
JMS / XLA supports three models (AUTO_ACKNOWLEDGE, DUPS_OK_ACKNOWLEDGE, CLIENT_ACKNOWLEDGE), more about models can be found in the documentation. In the example, I use the first model and set the session transaction to false.

Next, you need to decide on the mode of receiving messages. Two options are possible: synchronous and asynchronous.

In the synchronous version, messages are processed sequentially (one after another). This means that while the message is not processed, another is waiting.
For a synchronous variant, we call the start () method on the connection, to activate sending messages, create a Topic, then create a subscriber and receive messages using the receive () and receiveNoWait () methods.

connection.start();
Topic topic = session.createTopic(topicName);    
TopicSubscriber subscriber = session.createDurableSubscriber(topic, bookmark);                
..
MapMessage message = (MapMessage)subscriber.receive();
...

In asynchronous mode, you need to create a listener and process messages in it.
MyListener myListener = new MyListener (outStream);

Topic xlaTopic = session.createTopic (topic);
TopicSubscriber subscriber = session.createDurableSubscriber (xlaTopic, bookmark);
...
subscriber.setMessageListener (myListener);
connection.start ();
...

Below is an example of a class that implements synchronous mode (file DemoXLA.java).
import java.util.Enumeration;
import java.util.Hashtable;
import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Session;
import javax.jms.Topic;
import javax.jms.TopicConnectionFactory;
import javax.jms.TopicConnection;
import javax.jms.TopicSession;
import javax.jms.TopicSubscriber;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class DemoXLA {
    private TopicConnectionFactory connectionFactory;
    private TopicConnection connection;
    private TopicSession session;
    private Topic topic;
    private TopicSubscriber subscriber;
    public DemoXLA( String cf,
                    String topicName,
                    String selector) throws JMSException, NamingException {String key;
        Context messaging = getInitialContext(); // getting the context
 connectionFactory = (TopicConnectionFactory)messaging.lookup(cf);       
 connection = connectionFactory.createTopicConnection();
 connection.start();                       
 session = connection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);               
 topic = session.createTopic(topicName);    
        subscriber = session.createDurableSubscriber(topic, selector);                
        int i=0;
        while (i<10) {
            MapMessage message = (MapMessage)subscriber.receive();                       
            Enumeration e = message.getMapNames();
            while (e.hasMoreElements()) {
                key = (String)e.nextElement();
                System.out.println("[ " + key + " = " + message.getObject(key) + " ]");
            }
            System.out.println("----------------------------------------");                       
        }
        session.unsubscribe(selector);
        subscriber.close();
        session.close();
        connection.stop();
    }
    private Context getInitialContext() throws NamingException {
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory");       
        InitialContext initialContext = new InitialContext(env);
        return initialContext;
    }
    public static void main(String[] args) throws JMSException, NamingException {
        DemoXLA demo = new DemoXLA("TopicConnectionFactory", "Level2Demo", "bookmark");
    }
}

The following is an example of classes that implement asynchronous mode (MyListener.java, DemoXLA2.java).

MyListener.java
import java.util.Enumeration;
import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.Message;
import javax.jms.MessageListener;

public class MyListener implements MessageListener   {
    public MyListener() {}
    public void onMessage(Message message) {
        MapMessage mp = (MapMessage)message;
        Enumeration e;
        try {
            e = mp.getMapNames();
        } catch (JMSException s) {
            e = null;
            System.out.println("error 1");
        }
        while (e.hasMoreElements()) {
            String key = (String)e.nextElement();
            try {
                System.out.println("[ " + key + " = " + mp.getObject(key) + " ]");
            } catch (JMSException f) {
                System.out.println("error 2");
            }
        }
        System.out.println("----------------------------------------");
    }
}

DemoXLA2.java
import java.util.Hashtable;
import javax.jms.JMSException;
import javax.jms.Session;
import javax.jms.Topic;
import javax.jms.TopicConnectionFactory;
import javax.jms.TopicSession;
import javax.jms.TopicSubscriber;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class DemoXLA2 {
    private javax.jms.TopicConnectionFactory connectionFactory;
    private javax.jms.TopicConnection connection;
    private TopicSession session;
    private Topic topic;
    private TopicSubscriber subscriber;
    public DemoXLA2( String cf,
                    String topicName,
                    String selector) throws JMSException, NamingException, InterruptedException {
        Context messaging = getInitialContext();
        Object connectionFactoryObject = messaging.lookup(cf);
        connectionFactory = (TopicConnectionFactory)connectionFactoryObject;
        connection = connectionFactory.createTopicConnection();
        MyListener myListener = new MyListener();
        session = connection.createTopicSession(false, Session.AUTO_ACKNOWLEDGE);
        topic = session.createTopic(topicName);
        subscriber = session.createDurableSubscriber(topic, selector);
subscriber.setMessageListener(myListener);
        connection.start();
        Thread.sleep(60000);
        session.unsubscribe(selector);
        subscriber.close();
        session.close();
        connection.stop();
    }
    private Context getInitialContext() throws NamingException {
        Hashtable env = new Hashtable();
        env.put(Context.INITIAL_CONTEXT_FACTORY, "com.timesten.dataserver.jmsxla.SimpleInitialContextFactory");
        InitialContext initialContext = new InitialContext(env);
        return initialContext;
    }
    public static void main(String[] args) throws JMSException,
                                                  NamingException,
                                                  InterruptedException {
        DemoXLA2 demo = new DemoXLA2("TopicConnectionFactory", "xlademo", "bookmark");
    }
}

Next, run any of the examples and try to make, change and delete data from the xlatest table.

Command> insert into xlatest values (2, 'w');
1 row inserted.
Command> update xlatest set name = 'test' where id=2;
1 row updated.
Command> delete from xlatest;
1 row deleted.
Command>

Accordingly, in the application we get:

[ __TYPE = 10 ]
[ __COMMIT = true ]
[ __FIRST = true ]
[ __NULLS =  ]
[ __TBLNAME = XLATEST ]
[ __TBLOWNER = ORATT ]
[ __mver = 5621355056449191939 ]
[ __mtyp = null ]
[ ID = 2 ]
[ NAME = w ]
----------------------------------------
[ __TYPE = 11 ]
[ __COMMIT = true ]
[ __FIRST = true ]
[ __UPDCOLS = NAME ]
[ __NULLS =  ]
[ __TBLNAME = XLATEST ]
[ __TBLOWNER = ORATT ]
[ __mver = 5621355056449191942 ]
[ __mtyp = null ]
[ _ID = 2 ]
[ ID = 2 ]
[ _NAME = w ]
[ NAME = test ]
----------------------------------------
[ __TYPE = 12 ]
[ __COMMIT = true ]
[ __FIRST = true ]
[ __NULLS =  ]
[ __TBLNAME = XLATEST ]
[ __TBLOWNER = ORATT ]
[ __mver = 5621355056449191945 ]
[ __mtyp = D ]
[ ID = 2 ]
[ NAME = test ]
----------------------------------------

As you can see, we received messages about the operations with the xlatest table.
Messages have the following format:
System attributes begin with a double underscore, for example:
__TYPE - type of operation (Insert (10), Update (11), Delete (12)) other types are also possible (see the documentation). There are constants to determine the type of operation.
__COMMIT - signals the completion of the transaction (if true).
__FIRST - signals the first operation in the transaction (if true).
__TBLNAME - table name
__TBLOWNER - owner of the table
__NULLS - signals attributes that
contain null __mver and __mtyp are system attributes.
etc. (see documentation)
Attributes without underscore - table columns with specific values, for example:
[ID = 2]
[NAME = test]
Attributes starting with one underscore - old field values ​​(appear during the Update operation), for example:
[_ID = 2]
[ID = 2 ]
[_NAME = w]
[NAME = test]

Summary
Therefore, having a fairly superficial knowledge of java, you can write an XLA application that can process various messages received from TimesTen. In addition, the XLA application runs in asynchronous mode, which has virtually no effect on the performance of Oracle TimesTen.

Article author: Gennady Sigalaev