XmlBlaster Logo

REQUIREMENT

contrib.dbwatcher

XmlBlaster Logo


Type NEW
Priority HIGH
Status CLOSED
Topic DbWatcher polls a database for changes and publishes those to xmlBlaster.
Des
cription

DbWatcher overview

DbWatcher is a little framework of plugins which detects changes on a database table, queries the changes, formats them as a XML message and publishes them to xmBlaster. DbWatcher is ready to go, you only set some configuration variables in xmlBlasterPlugins.xml and on next xmlBlaster startup the configured database table is observed.
DbWatcher is extendable through a set of plugin interfaces. For standard cases we provide useful plugins.

DbWatcher may be useful when running xmlBlaster to integrate legacy applications where often many code segments update a database and other code segments or foreign applications need to know about the changes.

With the delivered JDBC ResultSet to XML converter plugin a typical database change message looks like this: 

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>INSERT</command>
  <ident>EDDI</ident>
  <colname type='DATE' nullable='0'>DATUM</colname>
  <colname type='NUMBER' precision='11' scale='0' signed='false'>
              CPU</colname>
  <colname type='VARCHAR2' precision='20' readOnly='true'>COL1</colname>
  <colname table='OMZ' schema='AA' catalog='CAT' type='VARCHAR2'
              precision='10'>ICAO_ID</colname>
 </desc>
 <row num='0'>
  <col name='DATUM'>2005-01-05 15:52:06.0</col>
  <col name='CPU'>238333</col>
  <col name='COL1'><![CDATA[<Bla]]></col>
  <col name='ICAO_ID'>EDDI</col>
  <attr name='LR'>SRANIL</attr>
  <attr name='SUBNET_ID'>TCP</attr>
 </row>
 <row num='1'>
  <col name='DATUM'>2005-01-05 15:52:07.0</col>
  <col name='CPU'>238340</col>
  <col name='COL1' encoding='base64'>QmxdXT5CbA==</col>
  <col name='ICAO_ID'>EDDI</col>
 </row>
</sql>

The generated message has a header tag <desc> which contains the meta information of the delivered data. This is self explaining, the <ident> tag delivers the changed value of the database table column ICAO_ID which caused the change message.
The <col> tags deliver the current columns value of a database table row. If they contain characters which harm XML wellformedness they are enclosed with a CDATA section. On seldom cases where this protection is not sufficient the column is BASE64 encoded.
Additionally, you can configure a transformer which does some use case specific transformations on the data an delivers the result with <attr> tags.

Now that you know what DbWatcher can do for you we should look into its design.

DbWatcher design

DbWatcher consists of a set of Interfaces which allows any thinkable customization. It is easily possible to run DbWatcher outside from xmlBlaster as a standalone program or even without any dependency on xmlBlaster.

  1. I_ChangePublisher Into which data sink to send the data?
    This is the MoM interface and we provide a XmlBlasterPublisher plugin. You can easily write a JMS plugin to send the changes to a JMS provider or a file system plugin to dump the message on harddisk.
  2. I_DataConverter How shall the message be formatted?
    We provide a ResultSetToXmlConverter plugin which produces the above shown XML message.
  3. I_AttributeTransformer Are there any additional attributes to be added to the XML dump?
    Here we provide nothing, you can add one yourself if you need to add specific <attr> tags. This possibility is offered by our ResultSetToXmlConverter to extend the XML message.
  4. I_ChangeDetector How do we detect changes on the databse
    We provide two plugins here.
    The MD5 checksum plugin MD5ChangeDetector.java queries the database and remembers the MD5 checksum of the ResultSet. On next query the MD5 is compared to the last one and if it has changed a message is published. This detection works only satisfying if the table does not contain too much data.
    The timestamp based plugin TimestampChangeDetector.java accesses on each poll all newer database rows and publishes them. This is only possible if the database table contains a column (or compound columns) which contains for example a DATE or TIMESTAMP information or any other column which is known to change.
    For more details consult the Javadoc description of the plugins.
  5. I_DbPool How to access the database?
    We provide a pooled database access plugin DbPool.java for performant DB access, it is based on xmlBlasters db-pool implementation. Again, you can replace this by your own existing implementation if needed.
  6. I_Info How to pass the configuration to DbWatcher?
    We provide a simple plugin based on java.utils.prefs, with this you can run DbWatcher as a standalone program, totally independent from xmlBlaster.
    Further we provide a wrapper to the xmlBlaster configuration framework Global which allows us to run DbWatcher as a native plugin of xmlBlaster.

The javadoc of each interface contains enough information to assist you writing your own customized plugin.

Database change detection

The most difficult point is how to get a change event when a foreign application changes data on the database.

There are following ways to detect DB changes, in future we could add such additional detection plugins:

  1. Changes are delivered by a MOM

    No DbWatcher is needed: This is the proper approach. As legacy systems have various channels which manipulate the DB directly it is no solution for older systems.

  2. Modify/Creation timestamp column

    A trigger adds the timestamp to each table
    -> This changes the DB schema
    This is supported by our TimestampChangeDetector.java plugin.

  3. Sequence of primary key

    Tables which contain a growing sequence of keys may use this to detect inserts
    -> Failes for 'update'

  4. Using a LogMiner

    -> Is database specific

  5. Audit tables (with trigger)

    Need to start database in audit mode -> How does it work in a portable way?

  6. Having a trigger for each table

    The trigger notifies a stored procedure which sends a notify message over xmlBlaster to DbWatcher to poll again.
    -> This needs to change the DB -> What if foreign database? http://www.oracle.com/technology/oramag/oracle/03-jan/o13java.html
    This is supported by our XmlBlasterPublisher.java plugin.

  7. Flashback queries

    -> http://www.oracle.com/technology/products/oracle9i/daily/apr18.html

  8. Use ora_rowscan (since Oracle 10g, similar for MS-SQLServer)

    Example for Oracle (http://www.remote-dba.net/10g_26.htm):
    select scn_to_timestamp(ora_rowscn) FROM A where ora_rowscn IS NOT null;
    Allows to retrieve the rowchange timestamps, but is of limited use as it is a timestamp of the table and not for each row. Like this we know the table has changed but we don't know which row.
    SQLServer uses the column type rowVersion.
    -> Missing for Oracle 9 and other DBs, Oracle 10g and MSQLServer support it.

  9. Using a customized select with MD5

    Query everything and remember the last state with MD5. On change send the complete data again. This is supported by our MD5ChangeDetector.java plugin.

Configuration hint

To configure your own poller you can take the simple standalone example client xmlBlaster/src/java/org/xmlBlaster/contrib/dbwatcher/Example.java adjust your query configuration and start it interactively. The testlog.properties in the same directory switches on fine logging and you can manually see what happens.

Logging hint

DbWatcher is using the JDK 1.4 logging framework. When running natively as xmlBlaster plugin this logging output is redirected to xmlBlaster utils logging. To switch on detailed logging, add those lines to xmlBlaster.properties:

logging/org.xmlBlaster.contrib.dbwatcher.convert.ResultSetToXmlConverter FINE
logging/org.xmlBlaster.contrib.db.DbPool FINE
logging/org.xmlBlaster.contrib.dbwatcher.detector.MD5ChangeDetector FINE
logging/org.xmlBlaster.contrib.dbwatcher.detector.AlertScheduler FINE
logging/org.xmlBlaster.contrib.dbwatcher.detector.TimestampChangeDetector FINE
logging/org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin FINE
logging/org.xmlBlaster.contrib.dbwatcher.mom.XmlBlasterPublisher FINE
logging/org.xmlBlaster.contrib.dbwatcher.DbWatcher FINE

Limitations

Every change detector has pro and cons and limitations, the following table gives an overview for the currently supplied detector plugins.

DB statement MD5ChangeDetector TimestampChangeDetector
CREATE OK OK
INSERT OK OK (reported as UPDATE)
UPDATE OK OK
DELETE OK (see mom.eraseOnDelete setting) Not detected
DROP OK (see mom.eraseOnDrop setting) OK (see mom.eraseOnDrop setting)
Comment Not suitable for huge tables as the complete table is scanned. Works with any table. Suitable for tables which huge amount of rows, does not detect row deletes. Needs a timestamp column (or an ascending counter) in the observed table.
Example
any

Timestamp based polling example

Lets create a database table TEST_POLL which contains a TIMESTAMP column TS (tested on Oracle): 

set autocommit on;

CREATE TABLE TEST_POLL (COLOR VARCHAR(10), TS TIMESTAMP, CAR VARCHAR(10));

INSERT INTO TEST_POLL VALUES ('green', CURRENT_TIMESTAMP, 'Fiat');
INSERT INTO TEST_POLL VALUES ('red', CURRENT_TIMESTAMP, 'Lada');
INSERT INTO TEST_POLL VALUES ('blue', CURRENT_TIMESTAMP, 'BMW');
INSERT INTO TEST_POLL VALUES ('pink', CURRENT_TIMESTAMP, 'Fiat');
INSERT INTO TEST_POLL VALUES ('white', CURRENT_TIMESTAMP, 'Fiat');

SELECT * FROM TEST_POLL ORDER BY CAR;

Now we configure a xmlBlaster native DbWatcher plugin in xmlBlasterPlugins.xml 


<plugin id='DbWatcherPlugin.testPoll' 
           className='org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin'>
   <attribute id='jdbc.drivers'>oracle.jdbc.driver.OracleDriver</attribute>
   <attribute id='db.url'>jdbc:oracle:thin:@localhost:1521:orcl</attribute>
   <attribute id='db.user'>system</attribute>
   <attribute id='db.password'>secret</attribute>
   <attribute id='mom.loginName'>DbWatcherPlugin.testPoll/1</attribute>
   <attribute id='mom.topicName'>db.test_poll.event.${groupColValue}</attribute>
   <attribute id='alertScheduler.pollInterval'>10000</attribute>
   <attribute id='changeDetector.class'>
      org.xmlBlaster.contrib.dbwatcher.detector.TimestampChangeDetector
   </attribute>
   <attribute id='changeDetector.groupColName'>CAR</attribute>
   <attribute id='changeDetector.detectStatement'>
      SELECT MAX(TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF')) from TEST_POLL
   </attribute>
   <attribute id='db.queryMeatStatement'>
      SELECT * FROM TEST_POLL WHERE TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF') > '${oldTimestamp}' ORDER BY CAR
   </attribute>
   <action do='LOAD' onStartupRunlevel='9' sequence='6'
              onFail='resource.configuration.pluginFailed'/>
   <action do='STOP' onShutdownRunlevel='6' sequence='5'/>
</plugin>

  • We register the plugin with the plugin tag and give it a unique name (gray).
  • We need to setup the database access (blue).
  • We need to setup the xmlBlaster access (green).
  • The plugin is configured to poll every 10 seconds with alertScheduler.pollInterval=10000 (orange).
  • In this case we want a separate topic for each CAR entry (changeDetector.groupColName=CAR), we detect a change on table TEST_POLL with the query SELECT MAX(TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF')) from TEST_POLL and all changed rows since the last query are processed with SELECT * FROM TEST_POLL WHERE TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF') > '${oldTimestamp}' ORDER BY CAR and published to topics depending on the current car, for example db.test_poll.event.Fiat (red).
  • NOTE: The ORDER BY CAR is a must to get events for each car change.

To see what messages are published start the server and a client
java org.xmlBlaster.Main -logging/org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin FINE
java javaclients.HelloWorldSubscribe -xpath //key -maxContentLength 1000
and press one time enter to subscribe to all messages.

These are the three messages you receive on startup:

---------------------------------------
<key oid='db.test_poll.event.BMW'/>

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>UPDATE</command>
  <ident>BMW</ident>
  <colname type='VARCHAR2' precision='10'>COLOR</colname>
  <colname type='TIMESTAMP' scale='1'>TS</colname>
  <colname type='VARCHAR2' precision='10'>CAR</colname>
 </desc>
 <row num='0'>
  <col name='COLOR'>blue</col>
  <col name='TS'>2005-1-11.15.15. 30. 566126000</col>
  <col name='CAR'>BMW</col>
 </row>
</sql>

---------------------------------------
<key oid='db.test_poll.event.Fiat'/>

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>UPDATE</command>
  <ident>Fiat</ident>
  <colname type='VARCHAR2' precision='10'>COLOR</colname>
  <colname type='TIMESTAMP' scale='1'>TS</colname>
  <colname type='VARCHAR2' precision='10'>CAR</colname>
 </desc>
 <row num='0'>
  <col name='COLOR'>green</col>
  <col name='TS'>2005-1-11.11.53. 34. 263515000</col>
  <col name='CAR'>Fiat</col>
 </row>
 <row num='1'>
  <col name='COLOR'>pink</col>
  <col name='TS'>2005-1-11.11.53. 34. 447328000</col>
  <col name='CAR'>Fiat</col>
 </row>
 <row num='2'>
  <col name='COLOR'>white</col>
  <col name='TS'>2005-1-11.11.53. 34. 456153000</col>
  <col name='CAR'>Fiat</col>
 </row>
</sql>

---------------------------------------
<key oid='db.test_poll.event.Lada'/>

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>UPDATE</command>
  <ident>Lada</ident>
  <colname type='VARCHAR2' precision='10'>COLOR</colname>
  <colname type='TIMESTAMP' scale='1'>TS</colname>
  <colname type='VARCHAR2' precision='10'>CAR</colname>
 </desc>
 <row num='0'>
  <col name='COLOR'>red</col>
  <col name='TS'>2005-1-11.11.53. 34. 425024000</col>
  <col name='CAR'>Lada</col>
 </row>
</sql>

Now to verify the detection, we update one table row

set autocommit on;
UPDATE TEST_POLL SET COLOR='yellow', TS=CURRENT_TIMESTAMP WHERE CAR='BMW';

and your subscriber should receive this message

<key oid='db.change.BMW'/>

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>UPDATE</command>
  <ident>BMW</ident>
  <colname type='VARCHAR2' precision='10'>COLOR</colname>
  <colname type='TIMESTAMP' scale='1'>TS</colname>
  <colname type='VARCHAR2' precision='10'>CAR</colname>
 </desc>
 <row num='0'>
  <col name='COLOR'>yellow</col>
  <col name='TS'>2005-1-11.15.52. 1. 204668000</col>
  <col name='CAR'>BMW</col>
 </row>
</sql>
Example
any

MD5 based polling example

The same table used in the previous example can be polled with MD5 comparison as well: 

<plugin id='DbWatcherPlugin.testPoll_MD5' 
           className='org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin'>
   <attribute id='jdbc.drivers'>oracle.jdbc.driver.OracleDriver</attribute>
   <attribute id='db.url'>jdbc:oracle:thin:@localhost:1521:orcl</attribute>
   <attribute id='db.user'>system</attribute>
   <attribute id='db.password'>secret</attribute>
   <attribute id='mom.loginName'>DbWatcherPlugin.testPoll/2</attribute>
   <attribute id='mom.topicName'>db.test_poll.event.${groupColValue}</attribute>
   <attribute id='alertScheduler.pollInterval'>2000</attribute>
   <attribute id='changeDetector.class'>
      org.xmlBlaster.contrib.dbwatcher.detector.MD5ChangeDetector
   </attribute>
   <attribute id='changeDetector.groupColName'>CAR</attribute>
   <attribute id='changeDetector.detectStatement'>
      SELECT color, car from TEST_POLL order by CAR
   </attribute>
      <attribute id='db.queryMeatStatement'>
      SELECT * FROM TEST_POLL WHERE CAR = '${groupColValue}'
   </attribute>
   <action do='LOAD' onStartupRunlevel='9' sequence='6'
              onFail='resource.configuration.pluginFailed'/>
   <action do='STOP' onShutdownRunlevel='6' sequence='5'/>
</plugin>

The only difference to the timestamp based example is that we can detect DELETE statements: 

set autocommit on;
DELETE FROM TEST_POLL WHERE CAR='BMW';

and your subscriber should receive this message

<key oid='db.test_poll.event.BMW'/>

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>DELETE</command>
  <ident>BMW</ident>
 </desc>
</sql>
 </content>
Example
any

Trigger the polling with xmlBlaster messages

The alert scheduler plugin typically polls for changes on the database. There is also support to poll the database after getting a kick by a xmlBlaster message send by some client. Here is an example setup: 

<plugin id='DbWatcherPlugin.testPoll-MsgTriggered' 
           className='org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin'>
   <attribute id='jdbc.drivers'>oracle.jdbc.driver.OracleDriver</attribute>
   <attribute id='db.url'>jdbc:oracle:thin:@localhost:1521:orcl</attribute>
   <attribute id='db.user'>system</attribute>
   <attribute id='db.password'>secret</attribute>
   <attribute id='db.queryMeatStatement'>
      SELECT * FROM TEST_POLL WHERE TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF') > '${oldTimestamp}' ORDER BY CAR
   </attribute>
   <attribute id='mom.loginName'>DbWatcherPlugin.testPoll/1</attribute>
   <attribute id='mom.topicName'>db.test_poll.event.${groupColValue}</attribute>
   <attribute id='alertProducer.class'>org.xmlBlaster.contrib.dbwatcher.mom.XmlBlasterPublisher</attribute>
   <attribute id='mom.alertSubscribeKey'><key oid='checkDb'/></attribute>
   <attribute id='mom.alertSubscribeQos'><qos/></attribute>
   <attribute id='changeDetector.class'>
      org.xmlBlaster.contrib.dbwatcher.detector.TimestampChangeDetector
   </attribute>
   <attribute id='changeDetector.groupColName'>CAR</attribute>
   <attribute id='changeDetector.detectStatement'>
      SELECT MAX(TO_CHAR(TS, 'YYYY-MM-DD HH24:MI:SSXFF')) from TEST_POLL
   </attribute>
   <action do='LOAD' onStartupRunlevel='9' sequence='6'
              onFail='resource.configuration.pluginFailed'/>
   <action do='STOP' onShutdownRunlevel='6' sequence='5'/>
</plugin>

You can now test a poll with a command line publish, first start a subscriber so we can see what happens:

java javaclients.HelloWorldSubscribe -xpath //key -session.name subscriber/1 -maxContentLength 1000

and then publish an alert message which triggers a database poll:

java javaclients.HelloWorldPublish -oid checkDb -numPublish 10 -erase false

An additional feature of the TimestampChangeDetector plugin allows you to send the oldTimestamp and the change detector will send all messages newer than your supplied timestamp, for example:

java javaclients.HelloWorldPublish -oid checkDb -clientProperty[oldTimestamp] "2005-02-01 11:59:24.639575" -erase false

You can also misuse this feature to send an oldTimestamp far in the future and like this set the DbWatcher on standby.
Example
any

Example for table creation change event message

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>CREATE</command>
  <colname type='NUMBER' precision='10' scale='3'>COLKEY</colname>
  <colname type='VARCHAR2' precision='20'>COL1</colname>
  <colname type='NUMBER' precision='12'>COL2</colname>
  <colname type='VARCHAR2' precision='10'>ICAO_ID</colname>
 </desc>
</sql>

Example for change event message

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>INSERT</command>
  <ident>EDDI</ident>
  <colname type='DATE' nullable='0'>DATUM</colname>
  <colname type='NUMBER' precision='11' scale='0'
              signed='false'>CPU</colname>
  <colname type='VARCHAR2' precision='20' readOnly='true'>COL1</colname>
  <colname table='OMZ' schema='AA' catalog='CAT' type='VARCHAR2'
              precision='10'>ICAO_ID</colname>
 </desc>
 <row num='0'>
  <col name='DATUM'>2005-01-05 15:52:06.0</col>
  <col name='CPU'>238333</col>
  <col name='COL1'><![CDATA[<Bla]]></col>
  <col name='ICAO_ID'>EDDI</col>
  <attr name='LR'>SRANIL</attr>
  <attr name='SUBNET_ID'>TCP</attr>
 </row>
 <row num='1'>
  <col name='DATUM'>2005-01-05 15:52:07.0</col>
  <col name='CPU'>238340</col>
  <col name='COL1' encoding='base64'>QmxdXT5CbA==</col>
  <col name='ICAO_ID'>EDDI</col>
 </row>
 <row num='2'>
  <col name='DATUM'>2005-01-05 15:52:08.0</col>
  <col name='CPU'>238343</col>
  <col name='COL1'>BOO</col>
  <col name='ICAO_ID'>EDDI</col>
  <attr name='SUBNET_ID'>X25</attr>
 </row>
</sql>

Data which would break the xml of being well-formed are enclosed with a CDATA section, data which contains a CDATA end-token is delivered base64 encoded.

Example for a deleted entry message

The topic name specifies the entry, for example 'db.change.event.EDDI', the content contains a DELETE command: 

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
   <desc>
   <command>DELETE</command>
   <ident>EDDI</ident>
   </desc>
</sql>

Use the mom.eraseOnDelete=true configuration to delete the topic on a table row DELETE event.

Example for a drop entry message

<?xml version='1.0' encoding='UTF-8' ?>
<sql>
 <desc>
  <command>DROP</command>
  <ident>EDDI</ident>
 </desc>
</sql>

Use the mom.eraseOnDrop=true configuration to delete the topic on a table DROP event.
Example
any

Example for Microsoft SQLServer with rowversion column

<plugin id='DbWatcherPlugin.testTS' 
         className='org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin'>
   <attribute id='jdbc.drivers'>com.microsoft.jdbc.sqlserver.SQLServerDriver</attribute>
   <attribute id='db.url'>
      jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=mydb
   </attribute>
   <attribute id='db.user'>sa</attribute>
   <attribute id='db.password'>secret</attribute>
   <attribute id='mom.loginName'>DbWatcherPlugin.testPoll/1</attribute>
   <attribute id='mom.topicName'>db.test_poll.event</attribute>

   <attribute id='alertScheduler.pollInterval'>500</attribute>
   <attribute id='changeDetector.class'>
      org.xmlBlaster.contrib.dbwatcher.detector.TimestampChangeDetector
   </attribute>

   <!-- timestamp variable, we want to get each row seperately -->
   <attribute id='changeDetector.groupColName'>polterTS</attribute>

   <!-- check if there was any change -->
   <attribute id='changeDetector.detectStatement'>
      SELECT MAX(CAST(polterTS as datetime)) from polter
   </attribute>

   <!-- each result row will be one explicit publish -->
   <attribute id='db.queryMeatStatement'>
      SELECT * FROM  polter WHERE CAST(polterTS as datetime) > '${oldTimestamp}' ORDER BY polterTS
   </attribute>
   <attribute id='mom.eraseOnDelete'>true</attribute>
   <attribute id='mom.eraseOnDrop'>true</attribute>
   <action do='LOAD' onStartupRunlevel='6' sequence='6'
               onFail='resource.configuration.pluginFailed'/>
   <action do='STOP' onShutdownRunlevel='5' sequence='6'/>
</plugin>

The polterTS column is of type rowVersion. You can add such a column with this command: 

alter table [xxx].[polter] add polterTS rowversion
Example
any

Sequence diagram showing the usage sequence of the plugins:
Configure

The plugin is configured in the xmlBlasterPlugins.xml configuration file. If you run DbWatcher outside of xmlBlaster the I_Info interface must return those settings.

Id (name) Type Manda tory Default value Comment
dbPool.class String yes org.xmlBlaster.contrib.db.DbPool Configures your implementation of interface I_DbPool.
converter.class String yes org.xmlBlaster.contrib.dbwatcher. convert.ResultSetToXmlConverter Configures your implementation of interface I_DataConverter.
mom.class String yes org.xmlBlaster.contrib.dbwatcher. mom.XmlBlasterPublisher Configures your implementation of interface I_ChangePublisher
changeDetector.class String yes org.xmlBlaster.contrib.dbwatcher. detector.MD5ChangeDetector Configures your implementation of interface I_ChangeDetector
alertProducer.class String yes org.xmlBlaster.contrib.dbwatcher. detector.AlertScheduler Configures your implementation of interface I_AlertProducer Here you can configure multiple classes with a comma separated list. Each of them can trigger an new check in parallel, for example alertProducer.class= org.xmlBlaster.contrib.dbwatcher. mom.XmlBlasterPublisher, org.xmlBlaster.contrib.dbwatcher. detector.AlertSchedulery will check regularly via the AlertScheduler and on message via XmlBlasterPublisher.
transformer.class String no yes If not empty or null the specified plugin implementing I_AttributeTransformer is loaded. This plugin is called once for each xml dump and adds <attr> tags as returned by the plugin. This feature is available when using ResultSetToXmlConverter.
jdbc.drivers String yes null For example oracle.jdbc.driver.OracleDriver, the database specific driver jar file which contains the above class must be in the class path.
db.url String yes null For example jdbc:oracle:thin:@localhost:1521:orcl.
db.user String yes null The database user login name.
db.password String yes null The database password.
mom.connectQos String no null If you need to specify more complex connection behavior you can do it here. Note however that this is a plugin which per default uses the local protocol.
mom.loginName String yes '_' + id of plugin If the id of the plugin is DbWatcher1, then the default loginName is _DbWatcher1. If you specify a connectQos, then this attribute is ignored.
mom.password String yes null This is the password to be used to authenticate the loginName. If you specify a connectQos, then this attribute is ignored.
mom.topicName String (yes) db.change.event.${groupColValue} Mandatory unless publishKey defined. If both are specified this attribute is ignored and a warning is logged. This is the name of the topic on which to publish (the oid).
If you have specified a groupColName a message for every change of this column in the result set triggers a message, the ${} token is replaced by the current columns value.
mom.publishKey String (no) null Optional unless topicName has not been specified in which case it is mandatory. If you need to specify a more complex key for the publishing (for example to make it searcheable) of the messages you can specify it here. Note that you can only specify one single key which will be used for all messages published by this plugin. If you need to differentiate you can use more plugins.
mom.publishQos String no null If you need to specify a more complex qos for the publishing you can specify it here. Note that you can only specify one single qos which will be used for all messages published by this plugin. If you need to differentiate you can use more plugins.
mom.eraseOnDrop String no false When a table drop is detected, shall we erase the topic in xmlBlaster or shall we just send a message containing the DROP command?
mom.eraseOnDelete String no false When a table row delete is detected, shall we erase the topic in xmlBlaster or shall we just send a message containing the DELETE command?
mom.alertSubscribeKey String no null The XmlBlasterPubliser plugin implements I_AlertProducer.java as well and can be configured to listen on a topic. When a message arrives there we trigger a new database poll.
mom.alertSubscribeQos String no null QoS for the subscription.
alertScheduler.pollInterval long no 2000 The time in milliseconds the AlertScheduler plugin sleeps between each poll sweep.
db.queryMeatStatement String no null If given this statement is used to gather the information which is placed into the message.
The statement needs to be ordered by the configured changeDetector.groupColName to be able to send a message on each columns change. If changeDetector.groupColName is null, any statement is possible.
changeDetector. groupColName String no null This is null or a valid column name of the database table observed. If configured the query statemts should be order by the same table column so that on each change in the result set a message is sent.
changeDetector. detectStatement String yes null Depending on the changeDetector.class configured you have to provide a useful SQL statement which detects changes on the observed database. For the MD5 checksum plugin it should contain all columns which are observed. For the Timestamp plugin you need to query the max timestamp of the observed table.
changeDetector. timestampColNum String no 1 For TimestampChangeDetector plugin only: The JDBC index (starts with 1) of the column containing the max(timestamp) column of the changeDetector.detectStatement. This usually is always 1 and you don't need to configure anything here.
changeDetector. MINSTR String no 2005-11-25 12:48:00.000 This setting defaults to " ", like this all SQL data in the observed table are initially delivered on DbWatcher startup. If you set this to a minimal value of your timestamp column only the changes bigger detected after a new xmlBlaster restart.
There is a special token ${currentTime} which sets this value to the current system time. If you specify as mentionned it will give you the milliseconds elapsed since January 1, 1970 GMT. You can also format it according to the syntax specified by the java.text.SimpleDateFormat format. An example would be: 
${currentTime}=yyyy-MM-dd HH:mm:ss.0
which would result in a string like:
2005-11-25 12:48:01.0
changeDetector. ignoreExistingDataOnStartup String no false This setting defaults to false, like this all SQL data in the observed table are initially delivered on DbWatcher startup. If you set this to true only the future changes are detected after a new xmlBlaster restart.
changeDetector. persist boolean false false This setting defaults to false. If set to true, a DbInfo instance will persist the timestamp to the database. The configuration of the database is taken from the configuration of the queues. It is currently not possible to choose another persitent location. If this flag is set, the changeDetector.MINSTR value specified in the configuration is overwritten.
converter.rootName String no sql For ResultSetToXmlConverter plugin only: Name of the XML root tag.
converter.addMeta String no true For ResultSetToXmlConverter plugin only: If false suppress meta information, the CREATE statement however will always transport the meta tags.
converter.postStatement String no null For ResultSetToXmlConverter plugin only: You can give a SQL statement which will be executed after a message has been successfully published (and only then). It is run inside the same transaction as the db.queryMeatStatement.
charSet String no UTF-8 The charset used.

The plugin is configured in the xmlBlasterPlugins.xml configuration file. Here an example where most properties are used: 


  <xmlBlaster>
    ...

  <plugin id='DbWatcherPlugin.MD5'
          className='org.xmlBlaster.contrib.dbwatcher.plugin.DbWatcherPlugin'>
     <attribute id='jdbc.drivers'>oracle.jdbc.driver.OracleDriver</attribute>
     <attribute id='db.url'>jdbc:oracle:thin:@localhost:1521:orcl</attribute>
     <attribute id='db.user'>system</attribute>
     <attribute id='db.password'>secret</attribute>
     <attribute id='db.queryMeatStatement'></attribute>
     <attribute id='mom.topicName'>db.change.event.${groupColValue}</attribute>
     <attribute id='mom.loginName'>dbWatcher/1</attribute>
     <attribute id='mom.password'>secret</attribute>
     <attribute id='mom.alertSubscribeKey'><key oid=''/></attribute>
     <attribute id='mom.alertSubscribeQos'><qos/></attribute>
     <attribute id='changeDetector.class'>
        org.xmlBlaster.contrib.dbwatcher.detector.MD5ChangeDetector
     </attribute>
     <attribute id='alertScheduler.pollInterval'>5000</attribute>
     <attribute id='changeDetector.groupColName'>ICAO_ID</attribute>
     <attribute id='changeDetector.detectStatement'>
        SELECT col1, col2, ICAO_ID FROM TEST_POLL ORDER BY ICAO_ID
     </attribute>
     <attribute id='converter.class'>
        org.xmlBlaster.contrib.dbwatcher.convert.ResultSetToXmlConverter
     </attribute>
     <attribute id='transformer.class'></attribute>
     <action do='LOAD' onStartupRunlevel='9' sequence='6'
             onFail='resource.configuration.pluginFailed'/>
     <action do='STOP' onShutdownRunlevel='6' sequence='5'/>
   </plugin>
   ...

 </xmlBlaster>

Note:

Variables like ${db.user} are replaced on startup when the plugin is loaded. You could add db.user=system into xmlBlaster.properties or on command line and ${db.user} would be replaced to system.

Now we face the problem that we need to set ${...} constructs which we want to pass through to the DbWatcher sub-plugins like MD5ChangeDetector. On plugin load they can't be resolved and an exception is thrown. To escape such variable please write $_{...}, for example $_{changeDetector.detectStatement}, and they will be passed through as is. The currently available sub-plugins themselves are capable to replace such constructs as well.
For most variables we have coded a work around in XmlBlasterPlugin.java but if you add own sub-plugins the work around does not detect it and you need to use $_ (or in fact any other construct like $[..] if only your sub-plugin can handle it).

Needed class files

The complete DbWatcher code resides under xmlBlaster/src/java/org/xmlBlaster/contrib, after a build all it is compiled as well and available in xmlBlaster.jar.

You can manually distribute it with

    cd xmlBlaster/build.tmp/classes
    jar cvf dbwatcher.jar org/xmlBlaster/contrib

NOTE: Configuration parameters are specified on command line (-someValue 17) or in the xmlBlaster.properties file (someValue=17). See requirement "util.property" for details.
Columns named Impl tells you if the feature is implemented.
Columns named Hot tells you if the configuration is changeable in hot operation.

Todo

Port testsuite to run with HSQLDB and MS-SQLServer, currently the SQL statements from the testsuite are Oracle specific, currently you need a running Oracle, a running xmlBlaster and start the tests separately:

java -Ddb.password=secret junit.swingui.TestRunner -noloading org.xmlBlaster.test.contrib.dbwatcher.TestResultSetToXmlConverter

java -Ddb.password=secret junit.swingui.TestRunner -noloading org.xmlBlaster.test.contrib.dbwatcher.TestTimestamp

Add other I_ChangeDetector plugins which scale better for tables with huge amount of entries.

See API org.xmlBlaster.contrib.dbwatcher.DbWatcher
See API org.xmlBlaster.contrib.dbwatcher.detector.MD5ChangeDetector
See API org.xmlBlaster.contrib.dbwatcher.detector.TimestampChangeDetector
See REQ engine.runlevel
See TEST org.xmlBlaster.test.contrib.dbwatcher.TestTimestamp
See TEST org.xmlBlaster.test.contrib.dbwatcher.TestResultSetToXmlConverter

This page is generated from the requirement XML file xmlBlaster/doc/requirements/contrib.dbwatcher.xml

Back to overview