12 Replies Latest reply on Apr 19, 2006 5:08 AM by timfox

    jboss-service.xml for Postgresql implementation

    robb.greathouse

      There are some minor alterations that must be done to deploy JBoss Messaging on Postgresql.

      If you simple replace the existing deploy/jms-messaging.sar/META-INF/jboss-service.xml


      <?xml version="1.0" encoding="UTF-8"?>

      <!--
      The JBoss Messaging service deployment descriptor.

      $Id: jboss-messaging-service.xml,v 1.51 2006/03/31 21:20:09 timfox Exp $
      -->



      <loader-repository>jboss.messaging:loader=ScopedLoaderRepository
      <loader-repository-config>java2ParentDelegation=false</loader-repository-config>
      </loader-repository>

      <mbean code="org.jboss.remoting.transport.Connector"
      xmbean-dd="org/jboss/remoting/transport/Connector.xml"
      name="jboss.messaging:service=Connector,transport=socket"
      display-name="Socket transport Connector">



      org.jboss.jms.server.remoting.JMSWireFormat
      org.jboss.jms.server.remoting.JMSWireFormat
      jboss
      jms
      false
      0
      ${jboss.bind.address}
      4457


      org.jboss.jms.server.remoting.JMSServerInvocationHandler



      jboss.messaging:service=NetworkRegistry


      <!-- TODO: Do I need this> -->


      <!-- The Server Peer -->
      <mbean code="org.jboss.jms.server.ServerPeer"
      name="jboss.messaging:service=ServerPeer"
      xmbean-dd="xmdesc/ServerPeer-xmbean.xml">


      <!-- ServerPeerID -->

      <!-- DefaultQueueJNDIContext -->

      <!-- DefaultTopicJNDIContext -->



      <depends optional-attribute-name="Connector">jboss.messaging:service=Connector,transport=socket
      <depends optional-attribute-name="ThreadPool">jboss.messaging:service=ThreadPool
      <depends optional-attribute-name="PersistenceManager">jboss.messaging:service=PersistenceManager
      <depends optional-attribute-name="MessageStore">jboss.messaging:service=MessageStore
      <depends optional-attribute-name="ChannelMapper">jboss.messaging:service=ChannelMapper

      <!-- Set to -1 to completely disable client leasing -->
      20000
      java:/jaas/messaging







      <!-- Plug-ins -->

      <mbean code="org.jboss.jms.server.plugin.DefaultThreadPool"
      name="jboss.messaging:service=ThreadPool"
      xmbean-dd="xmdesc/DefaultThreadPool-xmbean.xml">

      <!-- the default pool size -->





      Postgres pm config

      FIXME - This is temporary so servicecontainer can be used with mysql

      <mbean code="org.jboss.messaging.core.plugin.JDBCPersistenceManager"
      name="jboss.messaging:service=PersistenceManager"
      xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
      jboss.jca:service=DataSourceBinding,name=DefaultDS
      <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager
      <depends optional-attribute-name="ChannelMapper">jboss.messaging:service=ChannelMapper
      java:/DefaultDS
      true
      true
      <![CDATA[
      CREATE_MESSAGE_REF=CREATE TABLE JMS_MESSAGE_REFERENCE (CHANNELID BIGINT, MESSAGEID BIGINT, TRANSACTIONID BIGINT, STATE CHAR(1), ORD BIGINT, DELIVERYCOUNT INTEGER, RELIABLE CHAR(1), LOADED CHAR(1), PRIMARY KEY(CHANNELID, MESSAGEID))
      CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JMS_MESSAGE_REF_TX ON JMS_MESSAGE_REFERENCE (TRANSACTIONID)
      CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JMS_MESSAGE_REF_ORD ON JMS_MESSAGE_REFERENCE (ORD)
      CREATE_IDX_MESSAGE_REF_MESSAGEID=CREATE INDEX JMS_MESSAGE_REF_MESSAGEID ON JMS_MESSAGE_REFERENCE (MESSAGEID)
      CREATE_IDX_MESSAGE_REF_LOADED=CREATE INDEX JMS_MESSAGE_REF_LOADED ON JMS_MESSAGE_REFERENCE (LOADED)
      CREATE_IDX_MESSAGE_REF_RELIABLE=CREATE INDEX JMS_MESSAGE_REF_RELIABLE ON JMS_MESSAGE_REFERENCE (RELIABLE)
      INSERT_MESSAGE_REF=INSERT INTO JMS_MESSAGE_REFERENCE (CHANNELID, MESSAGEID, TRANSACTIONID, STATE, ORD, DELIVERYCOUNT, RELIABLE, LOADED) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      DELETE_MESSAGE_REF=DELETE FROM JMS_MESSAGE_REFERENCE WHERE MESSAGEID=? AND CHANNELID=? AND STATE='C'
      UPDATE_MESSAGE_REF=UPDATE JMS_MESSAGE_REFERENCE SET TRANSACTIONID=?, STATE='-' WHERE MESSAGEID=? AND CHANNELID=? AND STATE='C'
      UPDATE_MESSAGE_REF_NOT_LOADED=UPDATE JMS_MESSAGE_REFERENCE SET LOADED='N' WHERE MESSAGEID=? AND CHANNELID=?
      COMMIT_MESSAGE_REF1=UPDATE JMS_MESSAGE_REFERENCE SET STATE='C', TRANSACTIONID = NULL WHERE TRANSACTIONID=? AND STATE='+'
      COMMIT_MESSAGE_REF2=DELETE FROM JMS_MESSAGE_REFERENCE WHERE TRANSACTIONID=? AND STATE='-'
      ROLLBACK_MESSAGE_REF1=DELETE FROM JMS_MESSAGE_REFERENCE WHERE TRANSACTIONID=? AND STATE='+'
      ROLLBACK_MESSAGE_REF2=UPDATE JMS_MESSAGE_REFERENCE SET STATE='C', TRANSACTIONID = NULL WHERE TRANSACTIONID=? AND STATE='-'
      LOAD_REF_INFO=SELECT MESSAGEID, ORD, DELIVERYCOUNT FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID=? AND STATE <> '+' AND LOADED = 'N' AND ORD BETWEEN ? AND ? ORDER BY ORD
      SELECT_COUNT_REFS=SELECT COUNT(MESSAGEID) FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID=? AND STATE <> '+' AND LOADED='N'
      UPDATE_RELIABLE_REFS=UPDATE JMS_MESSAGE_REFERENCE SET LOADED='Y' WHERE ORD BETWEEN ? AND ? AND CHANNELID=? AND RELIABLE='Y' AND STATE <> '+'
      DELETE_CHANNEL_MESSAGE_REFS=DELETE FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID=?
      REMOVE_ALL_NONRELIABLE_REFS=DELETE FROM JMS_MESSAGE_REFERENCE WHERE RELIABLE='N'
      UPDATE_ALL_RELIABLE_REFS=UPDATE JMS_MESSAGE_REFERENCE SET LOADED='N'
      SELECT_MIN_ORDERING=SELECT MIN(ORD) FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID=? AND STATE <> '+' AND LOADED = 'N'
      DELETE_NON_DURABLE=DELETE FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID NOT IN (SELECT ID FROM JMS_CHANNEL_MAPPING)
      CREATE_MESSAGE=CREATE TABLE JMS_MESSAGE (MESSAGEID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY int2, COREHEADERS BYTEA, PAYLOAD BYTEA, CHANNELCOUNT INTEGER, TYPE int2, JMSTYPE VARCHAR(255), CORRELATIONID VARCHAR(255), CORRELATIONID_BYTES BYTEA, DESTINATION_ID BIGINT, REPLYTO_ID BIGINT, JMSPROPERTIES BYTEA, REFERENCECOUNT int2, PRIMARY KEY (MESSAGEID))
      LOAD_MESSAGES=SELECT MESSAGEID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, COREHEADERS, PAYLOAD, CHANNELCOUNT, TYPE, JMSTYPE, CORRELATIONID, CORRELATIONID_BYTES, DESTINATION_ID, REPLYTO_ID, JMSPROPERTIES FROM JMS_MESSAGE
      INSERT_MESSAGE=INSERT INTO JMS_MESSAGE (MESSAGEID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, COREHEADERS, PAYLOAD, CHANNELCOUNT, TYPE, JMSTYPE, CORRELATIONID, CORRELATIONID_BYTES, DESTINATION_ID, REPLYTO_ID, JMSPROPERTIES, REFERENCECOUNT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      UPDATE_MESSAGE_CHANNEL_COUNT=UPDATE JMS_MESSAGE SET CHANNELCOUNT=? WHERE MESSAGEID=?
      DELETE_MESSAGE=DELETE FROM JMS_MESSAGE WHERE MESSAGEID=?
      MESSAGE_ID_COLUMN=MESSAGEID
      REMOVE_ALL_NONRELIABLE_MSGS=DELETE FROM JMS_MESSAGE WHERE RELIABLE='N'
      DELETE_ALL_MESSAGES=DELETE FROM JMS_MESSAGE
      CREATE_TRANSACTION=CREATE TABLE JMS_TRANSACTION (TRANSACTIONID BIGINT, BRANCH_QUAL BYTEA, FORMAT_ID INTEGER, GLOBAL_TXID BYTEA, PRIMARY KEY (TRANSACTIONID))
      INSERT_TRANSACTION=INSERT INTO JMS_TRANSACTION (TRANSACTIONID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?)
      DELETE_TRANSACTION=DELETE FROM JMS_TRANSACTION WHERE TRANSACTIONID = ?
      SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTIONID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JMS_TRANSACTION
      DELETE_ALL_TRANSACTIONS=DELETE FROM JMS_TRANSACTION
      CREATE_COUNTER=CREATE TABLE JMS_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME))
      UPDATE_COUNTER=UPDATE JMS_COUNTER SET NEXT_ID = ? WHERE NAME=?
      SELECT_COUNTER=SELECT NEXT_ID FROM JMS_COUNTER WHERE NAME=?
      INSERT_COUNTER=INSERT INTO JMS_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
      DELETE_ALL_COUNTERS=DELETE FROM JMS_COUNTER
      ]]>

      500





      <mbean code="org.jboss.messaging.core.plugin.SimpleMessageStore"
      name="jboss.messaging:service=MessageStore"
      xmbean-dd="xmdesc/SimpleMessageStore-xmbean.xml">

      <!-- StoreID -->






      Postgres Channel Mapper config

      <mbean code="org.jboss.jms.server.plugin.JDBCChannelMapper"
      name="jboss.messaging:service=ChannelMapper"
      xmbean-dd="xmdesc/JDBCChannelMapper-xmbean.xml">
      jboss.jca:service=DataSourceBinding,name=DefaultDS
      <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager
      java:/DefaultDS
      <![CDATA[
      CREATE_USER_TABLE=CREATE TABLE JMS_USER (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USERID))
      CREATE_ROLE_TABLE=CREATE TABLE JMS_ROLE (ROLEID VARCHAR(32) NOT NULL, USERID VARCHAR(32) NOT NULL, PRIMARY KEY(USERID, ROLEID))
      SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JMS_USER WHERE USERID=?
      CREATE_MAPPING_TABLE=CREATE TABLE JMS_CHANNEL_MAPPING (ID BIGINT, TYPE CHAR(1), JMS_DEST_NAME VARCHAR(1024), JMS_SUB_NAME VARCHAR(1024), CLIENT_ID VARCHAR(128), SELECTOR VARCHAR(1024), NO_LOCAL CHAR(1), PRIMARY KEY(ID))
      INSERT_MAPPING=INSERT INTO JMS_CHANNEL_MAPPING (ID, TYPE, JMS_DEST_NAME, JMS_SUB_NAME, CLIENT_ID, SELECTOR, NO_LOCAL) VALUES (?, ?, ?, ?, ?, ?, ?)
      DELETE_MAPPING=DELETE FROM JMS_CHANNEL_MAPPING WHERE ID = ?
      SELECT_ID_FOR_DESTINATION=SELECT ID FROM JMS_CHANNEL_MAPPING WHERE TYPE=? AND JMS_DEST_NAME=?
      SELECT_DURABLE_SUB=SELECT JMS_DEST_NAME, ID, SELECTOR, NO_LOCAL FROM JMS_CHANNEL_MAPPING WHERE CLIENT_ID=? AND JMS_SUB_NAME=?
      SELECT_SUBSCRIPTIONS_FOR_TOPIC=SELECT ID, CLIENT_ID, JMS_SUB_NAME, SELECTOR, NO_LOCAL FROM JMS_CHANNEL_MAPPING WHERE TYPE='D' AND JMS_DEST_NAME=?
      ]]>


      <!-- ConnectionFactories -->

      <mbean code="org.jboss.jms.server.connectionfactory.ConnectionFactory"
      name="jboss.messaging.destination:service=ConnectionFactory"
      xmbean-dd="xmdesc/ConnectionFactory-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      /ConnectionFactory
      /XAConnectionFactory
      java:/ConnectionFactory
      java:/XAConnectionFactory




      <!-- Test destinations -->

      <mbean code="org.jboss.jms.server.destination.Topic"
      name="jboss.messaging.destination:service=Topic,name=testTopic"
      xmbean-dd="xmdesc/Topic-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer









      <mbean code="org.jboss.jms.server.destination.Topic"
      name="jboss.messaging.destination:service=Topic,name=securedTopic"
      xmbean-dd="xmdesc/Topic-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer







      <mbean code="org.jboss.jms.server.destination.Topic"
      name="jboss.messaging.destination:service=Topic,name=testDurableTopic"
      xmbean-dd="xmdesc/Topic-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer









      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=testQueue"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer









      <!-- We need this to deploy MDBs -->

      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=DLQ"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <!-- Example queues -->

      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=A"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=B"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=C"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=D"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <mbean code="org.jboss.jms.server.destination.Queue"
      name="jboss.messaging.destination:service=Queue,name=ex"
      xmbean-dd="xmdesc/Queue-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer


      <mbean code="org.jboss.jms.server.destination.Topic"
      name="jboss.messaging.destination:service=Queue,name=openTopic"
      xmbean-dd="xmdesc/Topic-xmbean.xml">
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer







        • 1. Re: jboss-service.xml for Postgresql implementation
          timfox

          Great thanks Robb.

          Now we have database configurations for Oracle, MySQL, Postgressql and HSQL (default).

          Can you add in jms/docs/examples/config with the other configs?

          Thanks.

          • 2. Re: jboss-service.xml for Postgresql implementation
            timfox

            We should get the MSSQL one done ASAP too.

            • 3. Re: jboss-service.xml for Postgresql implementation
              timfox

              BTW having the config in jboss-service.xml is a temporary measure so we can run the test suite against different databases, and will disappear when http://jira.jboss.com/jira/browse/JBMESSAGING-289 is done.

              • 4. Re: jboss-service.xml for Postgresql implementation
                ovidiu.feodorov

                Thanks Robb

                Please send the cofiguration file to me by e-mail and I will check it in.

                • 5. Re: jboss-service.xml for Postgresql implementation
                  timfox

                  We need to make sure the test suite runs against it before including it in the distribution

                  • 6. Re: jboss-service.xml for Postgresql implementation
                    ovidiu.feodorov

                    Yes.
                    How about a stress test matrix?

                    Postgres and MySQL are easy to install, and I guess we can have an Oracle instance too ...

                    Time to raise the hardware problem.

                    • 7. Re: jboss-service.xml for Postgresql implementation
                      timfox

                      All the current configs (including oracle) have been tested.

                      If we want to include PostgreSQL it needs to be tested too.

                      I'll do this this weekend, it is not a big deal.

                      • 8. Re: jboss-service.xml for Postgresql implementation
                        timfox

                        Agreed, we should have a database matrix against which we continuously test.

                        Databases should include Oracle, MSSQL, MySQL, PostgreSQL, DB2, Sybase, Derby at the least.

                        This is going to be a fair effort to set-up.

                        Hmmm I wonder if the Hibernate guys have such a set-up we can borrow?.....

                        For now I am just testing manually which is time consuming.

                        • 9. Re: jboss-service.xml for Postgresql implementation

                          Wouldn't a better solution be to have db version of the JDBCChannelMapper & JDBCPersistanceManager is seperate files as they are in the docs/examples/config and merge them at build time using a build property to select which db version to include. Also I think it would be useful to include the copying the datasource config (<db-version>-ds.xml) as part of the deploy. Even using a different Datasource JNDI for each differnt DB rather than having to replace the DefaultDS.

                          Just a thought...... I happy to get this working and submit a patch to see you think this a good idea.....

                          • 10. Re: jboss-service.xml for Postgresql implementation
                            timfox

                            This will soon be in managed separately (like JBossMQ)

                            Actually, if you look in the getting started guide there is a paragraph that explains this:


                            In JBoss Messaging 1.0 most of those services are configurable via mbean definitions which are <emphasis role="bold">all</emphasis> located in <literal>jboss-messaging.sar/META-INF/jboss-service.xml</literal>. Very soon, configurations will be broken down into separate xml files in the ditribution, making them easier to manage.


                            • 11. Re: jboss-service.xml for Postgresql implementation

                              I noticed in the mysql & oracle *-jdbcchannelmapper-service.xml and *-jdbcpersistencemanager-service.xml files that the include the following

                               <loader-repository>jboss.messaging:loader=ScopedLoaderRepository
                               <loader-repository-config>java2ParentDelegation=false</loader-repository-config>
                               </loader-repository>
                              


                              I working on http://jira.jboss.org/jira/browse/JBMESSAGING-350 and in creating the PostgreSQL config was wonder if loader-repository was included for any particular reason.

                              Also should I add this config to the src/etc/server/default/deployjboss-messaging-service.xml the same as the mysql & oracle configs so PostgreSQL can be tested with the servicecontainer as well.

                              I am doing it locally so I can run the tests against Postgres but should I include it in my patch? I guess it would mean including the datasource config and JDBC drivers. How is this currently manage with MySQL and Oracle?

                              • 12. Re: jboss-service.xml for Postgresql implementation
                                timfox

                                Hi Aaron-

                                "apwalker" wrote:
                                I noticed in the mysql & oracle *-jdbcchannelmapper-service.xml and *-jdbcpersistencemanager-service.xml files that the include the following

                                 <loader-repository>jboss.messaging:loader=ScopedLoaderRepository
                                 <loader-repository-config>java2ParentDelegation=false</loader-repository-config>
                                 </loader-repository>
                                


                                I working on http://jira.jboss.org/jira/browse/JBMESSAGING-350 and in creating the PostgreSQL config was wonder if loader-repository was included for any particular reason.



                                This is necessary since JBossMessaging is deployed in Jboss4.x as a scoped deployment. The persistence manager and channel mapper need to use the same class loading domain so they can find the classes.



                                Also should I add this config to the src/etc/server/default/deployjboss-messaging-service.xml the same as the mysql & oracle configs so PostgreSQL can be tested with the servicecontainer as well.



                                Yes please, that would be great. As already mentioned, eventually the services will be configured in their own files which will make them easier to swap around. But until then please do it in the same way as other pm configs.



                                I am doing it locally so I can run the tests against Postgres but should I include it in my patch? I guess it would mean including the datasource config and JDBC drivers. How is this currently manage with MySQL and Oracle?


                                There is a task to make this simpler (http://jira.jboss.com/jira/browse/JBMESSAGING-289) but until then, the way to do it is as follows:

                                In ServiceContainer.java, add a new static constant for POSTGRESQL, in the same way as already exists for oracle, mysql, hsq, and set the DB constant to that value.

                                Then, also in ServiceContainer.java, in the startManagedConnectionFactory method, add a new block for postgres with the appropriate params.

                                Then just add the postgres driver to the classpath in build.xml, runtest, and startrmiserver (do not commit the driver jar in CVS!), and then you should be able to run the test suite against postgres.

                                Before creating the patch please make sure you set the DB constant back to HSQL, since that is the default for testing.

                                Thanks