Something that always bothered me was the way in which 90% of database caching is performed, for most places the database is naturally considered a bottleneck and typically a cache is built to avoid multiple redundant calls. Now this is fine as such, but it starts to break down when the underlying data is changed, the typical responses to this appear to be to have some badly built admin code, have the cache timeout very fast, or to just live with stale data.

As part of a recent project that was the removal of the proprietary BroadVision content management system, I realised very early on that it would be a very quick performance win to put a cache between the application, and the database content. However, I have previously bore the pain with and the application had thousands of tiny caches, many of which could not even be flushed. This lead to situations where the business would demand (what is in their parlance) a "Content-promotion", but would have to wait, sometimes up to several hours, to see the changes. All the tables involved are naturally, all very high on selects, this meant that any overhead of clever cache control methods would not be a major concern given the fact that the tables were written to only very occasionally

Thinking about this further I came upon the idea, that it would be great if the database could tell the application it has changed. With this in place I was also able to think about caching content in the application, essentially indefinitely, since any change in the DB would propagate a flush to the application. This is essentially turning the caching problem around, instead of the application guessing, the application is told

Looking around I discovered that all the major DBMS providers (except MySql :S) provide enough glue to make this a reality. Whilst the implementation details differ between vendors, the idea stays the same, so with careful coding this is no more platform dependent than say, 3-4 classes.

Settling on Oracle (which was (and probably still is)) the DBMS of choice for this particular system, I discovered the server supports a little known feature called "Oracle Advanced Queues"; these have been in the software since Oracle 8 and continue to be supported and used (even in version 11, which coincidentally builds a system called "Database Change notification" on AQ).

Advanced Queues are basically a message orientated middleware built straight into the database software, and should you have such a need they can be used directly as a message backbone. However in my case I simply wanted the database to fire a message from a trigger, with the application (being Java based) reading the message.

So anyhow, enough talking, this is how I made our caches smart

To start with you will need to setup oracle advanced queues:
1 EXEC dbms_aqadm.create_queue_table(queue_table=>'content_queue', queue_payload_type=>'sys.aq$_jms_text_message', multiple_consumers=>TRUE);
2 EXEC dbms_aqadm.create_queue(queue_name=>'cnt_queue', queue_table=>'content_queue');
3 EXEC dbms_aqadm.start_queue(queue_name=>'cnt_queue');
4 commit;
 The ddms_aqadm package is the Advanced-Queue Admin package that contains all the operations required to manage, create and destroy queues in the database. The commands issued above do the following things:

  1. Create the queue table that oracle uses to store information about the queue, the queue table in our case is specified as being multiple_consumer capable (as such it communicates using JMS topics rather than destinations). This is done purely to ensure that we can connect multiple instances of the application to the same queue.
    We are also telling oracle that the payload for this queue is a JMS text message, the rational is purely because this made my life easier than setting up all the required database and JMS configuration for full object support.
  2. The named queue (oracle terminology), or topic (JMS terminology) is created inside the database, and is bound to the given queue_table
  3. After which we get to start the queue !
You will need to grant access to the dbms_aq package to the user that will execute the procedure, your friendly DBA can help you with this, the code you will want will (probably) be 1 GRANT EXECUTE ON DBMS_AQ TO SOME_USER;
 From here you will need to put together a PL/SQL procedure that can be fired from a trigger to call the advanced queue, the procedures task is to create the JMS text message that will eventually be delivered to the JMS listener. Now as I said above, I am using JMS text messages simply because I makes the configuration a lot easier, and because it is the simplest path to my goal.  1 CREATE OR REPLACE PROCEDURE PROC_CACHE_CTRL_NOTIFICATION(TABLE_NAME IN VARCHAR) AS
 2   msg SYS.AQ$_JMS_TEXT_MESSAGE;
 3   queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
 4   msg_props DBMS_AQ.MESSAGE_PROPERTIES_T;
 5   msg_id RAW(16);
 6   no_consumers_error EXCEPTION;
 7   PRAGMA EXCEPTION_INIT(no_consumers_error, -24033);
 8 BEGIN
 9   -- Ensure what is sent will be a JMS message
10   msg := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT();
11
12   msg.set_text('TABLE_NAME=' || TABLE_NAME);
13   DBMS_AQ.ENQUEUE( queue_name => 'cnt_queue'
14                  , enqueue_options => queue_options
15                  , message_properties => msg_props
16                  , payload => msg
17                  , msgid => msg_id);
18   -- Without the following, the procedure will die if noone
19   -- Is listening for cache control
20   EXCEPTION
21     WHEN no_consumers_error
22     THEN
23         -- Output it in case, but otherwise swallow
24         DBMS_OUTPUT.PUT_LINE('No interested parties are listening to cache-control messages');
25 END;
26 /
 Stick a trigger on any table that you need to send a notification from, I would suggest making the trigger an AFTER Statement type of trigger, as this will prevent you sending a ridiculous amount of messages, and will also reduce the chance of the trigger interfering in the real commit. There is probably a more intelligent way of setting up such triggers, however in reality its not a lot of code 1 CREATE OR REPLACE TRIGGER TRGR_SOME_CONTENT
2 AFTER DELETE OR INSERT OR UPDATE
3 ON SOME_CONTENT
4 CALL PROC_CACHE_CTRL_NOTIFICATION('SOME_CONTENT');
 Now you are all done with the oracle configuration, lets recap. You basically have a standard oracle queue in the DBMS, this queue is configured to send to multiple consumers, and delivers JMS text messages, along side this queue you have a PL/SQL procedure that sends the table name (or really any text prefix with TableName=) to interested clients. With all the oracle side done, we know need to rig up the client (the java part).
I did this with spring, this isn't for any religion or spring worship, but simply because it proves simpler to hookup than if I were to use, for example, a full fat Message Driven Bean.
Given that my mechanism used Oracle advanced queue, and that these are created in a slightly none standard way using oracle specific code, the first part is to create a factory which returns JMSConnections to the oracle advanced queue
 1 public class OracleAQTopicConnectionFactory {
 2
 3     private DataSource dataSource;
 4
 5     public ConnectionFactory createConnectionFactory() throws Exception {
 6         return oracle.jms.AQjmsFactory.getTopicConnectionFactory(dataSource);
 7     }
 8
 9     public void setDataSource(DataSource dataSource) {
10         this.dataSource = dataSource;
11     }
12 }
 It should be noticed that the above code takes a dataSource (it should be an oracle datasource naturally), from which it is able to get a reference to the queue that you want to play with
Next up we need to give spring (or whatever JMS framework you choose to use) a JMS Destination. In my case because I am using the multiple consumers model I will give it a Topic that hooks Oracle and the Java JMS together. Naturally being the design pattern trigger-happy language java is this is yet another factory *Sigh*  1 import org.springframework.beans.factory.FactoryBean;
 2 import oracle.jms.AQjmsSession;
 3 import javax.jms.TopicConnectionFactory;
 4
 5 public class OracleAQTopicDestinationFactory implements FactoryBean {
 6
 7     private TopicConnectionFactory connectionFactory;
 8     private String queueUser;
 9     private String queueName;
10
11     public Object getObject() throws Exception {
12         AQjmsSession session = (AQjmsSession) connectionFactory.createTopicConnection().createTopicSession(true, 0);
13         return session.getTopic(queueUser, queueName);
14     }
15
16     public Class getObjectType() {
17         return javax.jms.Topic.class;
18     }
19
20     public boolean isSingleton() {
21         return false;
22     }
23
24     public void setConnectionFactory(TopicConnectionFactory connectionFactory) {
25         this.connectionFactory = connectionFactory;
26     }
27
28     public void setQueueUser(String queueUser) {
29         this.queueUser = queueUser;
30     }
31
32     public void setQueueName(String queueName) {
33         this.queueName = queueName;
34     }
35 }
 This factory is responsible for getting spring a Topic on which spring itself will wire up a DefaultMessageListener
Next up we need some actual application code to run when the JMS message is delivered; in my case this is done with yet another classes  1 public class OracleNotificationListener implements MessageListener {
 2     
 3     private static final Logger log = Logger.getLogger(OracleNotificationListener.class);
 4
 5     private CacheManager cacheManager = null;
 6
 7     public void onMessage(Message message) {
 8         try {
 9             String text = ((TextMessage)message).getText();
10             // Presently we only work with cache clears
11             String[] tokens = text.split("Cache-Clear:");
12             String tableName = tokens[1].split("TABLE_NAME=")[1];
13             if (log.isInfoEnabled()) {
14                 log.info("About to clear the content cache for the datatype=" + tableName);
15             }
16             this.cacheManager.invalidateDataType(tableName);
17         } catch(Throwable t) {
18             log.fatal("Error ", t);
19         }
20     }
21
22     public CacheManager getCacheManager() { return cacheManager; }
23
24     public void setCacheManager(CacheManager cacheManager) { this.cacheManager = cacheManager; }
25
26 }
 I won't bore anyone with details about how the cache manager (its a very small internal wrapper around ehcache). Suffice it to say that when I send the table name to the CacheManager it forces the relevant data to be removed from the cache.
Finally, spring being spring, java being java and the sky being blue, we are forced to program (yes program) the following XML to glue it all together
 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"
 3         "http://www.springframework.org/dtd/spring-beans.dtd">
 4 <beans>
 5
 6     <bean id="cacheManager" class="com.opodo.framework.content.caching.CacheManager" />
 7
 8     <bean id="oracleAQJMSListener" class="com.opodo.framework.content.OracleNotificationListener">
 9         <property name="cacheManager" ref="cacheManager"/>
10     </bean>
11
12     <bean id="oracleAqConnFactory"
13           factory-bean="oracleAqConnectionFactoryHandler" factory-method="createConnectionFactory" />
14
15     <bean id="oracleAqConnectionFactoryHandler" 
class="com.opodo.framework.content.OracleAQTopicConnectionFactory">
16         <property name="dataSource" ref="dataSource" />
17     </bean>
18
19     <bean id="cnt_queue" class="com.opodo.framework.content.OracleAQTopicDestinationFactory">
20         <property name="connectionFactory" ref="oracleAqConnFactory" />
21         <property name="queueName" value="not" />
22         <property name="queueUser" value="telling" />
23     </bean>
24
25     <bean id="oracleAqJMSQueue" class="org.springframework.jms.listener.DefaultMessageListenerContainer">
26         <property name="connectionFactory" ref="oracleAqConnFactory"/>
27         <property name="destination" ref="cnt_queue"/>
28         <property name="messageListener" ref="oracleAQJMSListener" />
29         <property name="pubSubDomain" value="true" />
30     </bean>
31
32 </beans>

With all of that done I now enjoy database assisted caching
Comments ? want to tell me I am mad, I dont run blog software and so you are forced to email me (I know the utter indignity, worse still I probaly wont even get around to reading emails that look boring (the horror the horror !)), still if you want get me at:
gbowyer-This_Should_be_an_obvious-Spamblock-@freeshell.org