JBossMQ JDBC State Manager

This service stores the state in a database.

Default Configuration

  <mbean code="org.jboss.mq.sm.jdbc.JDBCStateManager"
           name="jboss.mq:service=StateManager">
    <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=DefaultDS</depends>
    <attribute name="SqlProperties">
      CREATE_TABLES_ON_STARTUP = TRUE
      CREATE_USER_TABLE = CREATE TABLE JMS_USERS (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, \
                                                 CLIENTID VARCHAR(128), PRIMARY KEY(USERID))
      CREATE_ROLE_TABLE = CREATE TABLE JMS_ROLES (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, \
                                                 PRIMARY KEY(USERID, ROLEID))
      CREATE_SUBSCRIPTION_TABLE = CREATE TABLE JMS_SUBSCRIPTIONS (CLIENTID VARCHAR(128) NOT NULL, \
                                                 SUBNAME VARCHAR(128) NOT NULL, TOPIC VARCHAR(255) NOT NULL, \
                                                 SELECTOR VARCHAR(255), PRIMARY KEY(CLIENTID, SUBNAME))
      GET_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
      LOCK_SUBSCRIPTION = SELECT TOPIC, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
      GET_SUBSCRIPTIONS_FOR_TOPIC = SELECT CLIENTID, SUBNAME, SELECTOR FROM JMS_SUBSCRIPTIONS WHERE TOPIC=?
      INSERT_SUBSCRIPTION = INSERT INTO JMS_SUBSCRIPTIONS (CLIENTID, SUBNAME, TOPIC, SELECTOR) VALUES(?,?,?,?)
      UPDATE_SUBSCRIPTION = UPDATE JMS_SUBSCRIPTIONS SET TOPIC=?, SELECTOR=? WHERE CLIENTID=? AND SUBNAME=?
      REMOVE_SUBSCRIPTION = DELETE FROM JMS_SUBSCRIPTIONS WHERE CLIENTID=? AND SUBNAME=?
      GET_USER_BY_CLIENTID = SELECT USERID, PASSWD, CLIENTID FROM JMS_USERS WHERE CLIENTID=?
      GET_USER = SELECT PASSWD, CLIENTID FROM JMS_USERS WHERE USERID=?
      POPULATE.TABLES.01 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('guest', 'guest')
      POPULATE.TABLES.02 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('j2ee', 'j2ee')
      POPULATE.TABLES.03 = INSERT INTO JMS_USERS (USERID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
      POPULATE.TABLES.04 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('nobody', 'nobody')
      POPULATE.TABLES.05 = INSERT INTO JMS_USERS (USERID, PASSWD) VALUES ('dynsub', 'dynsub')
      POPULATE.TABLES.06 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('guest','guest')
      POPULATE.TABLES.07 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('j2ee','guest')
      POPULATE.TABLES.08 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('john','guest')
      POPULATE.TABLES.09 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('subscriber','john')
      POPULATE.TABLES.10 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','john')
      POPULATE.TABLES.11 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('publisher','dynsub')
      POPULATE.TABLES.12 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','john')
      POPULATE.TABLES.13 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('durpublisher','dynsub')
      POPULATE.TABLES.14 = INSERT INTO JMS_ROLES (ROLEID, USERID) VALUES ('noacc','nobody')
    </attribute>
  </mbean>

Configurable Attributes

• ConnectionManager - the ObjectName of the ConnectionManager that controls the DataSource is deployed

• SQLProperties - the sql statements used by the service

SQLProperties

• CREATE_TABLES_ON_STARTUP - whether to create tables - also whether to popultate tables

• CREATE_USER_TABLE - the sql to create the user table

• CREATE_ROLE_TABLE - the sql to create the role table

• CREATE_SUBSCRIPTION_TABLE - the sql to create the subscription table

• GET_SUBSCRIPTION - the sql to retrieve a topic and selector for a client/subscription name

• LOCK_SUBSCRIPTION - the sql to retrieve a topic and selector for a client/subscription name add FOR UPDATE when not using hsqldb

• GET_SUBSRIPTIONS_FOR_TOPIC - retrieve the client id, subscription name and selector for each subscription on a topic

• INSERT_SUBSCRIPTION - add a subscription

• UPDATE_SUBSCRIPTION - change a subscription

• REMOVE_SUBSCRIPTION - remove a subscription

• GET_USER_BY_CLIENTID - locate a user for a given client id

• GET_USER - retrieve a user

• POPULATE.TABLES - used to the populate for the testsuite - these queries are only run when the tables are first created - the queries can be safely removed but you will probably need POPULATE.TABLES.06 to create the guest role.