5 Replies Latest reply on Aug 18, 2006 5:20 PM by Ovidiu Feodorov

    SQLServer configuration file?

    David Sanders Newbie

      Does anyone know when a MS SQLServer configuration file will be available? Has anyone successfuly configured JBoss Messaging for MSSQL?

        • 1. Re: SQLServer configuration file?
          Ovidiu Feodorov Master


          As far as I know, we don't have a MSSQL instance to test the configuration with, but I might be wrong, I am asking around.

          • 2. Re: SQLServer configuration file?
            Tim Fox Master

            I think this is partially done.

            Luc has volunteered to finish it off.

            http://jira.jboss.com/jira/browse/JBMESSAGING-502

            • 3. Re: SQLServer configuration file?
              David Howe Newbie

              Have you tried using the Sybase persistence service as a starting point. If the SQL is not that complicated (e.g. use of functions) then MSSQL might be close enough to Sybase for this to work. I've had a quick look in the Sybase persistence service, and the SQL is pretty straightforward. I don't have a SQL server instance to try it against though.

              • 4. Re: SQLServer configuration file?
                Chip Schoch Novice

                Here is one that works:

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

                <!--
                Orcle persistence deployment descriptor.

                $Id: oracle-persistence-service.xml 1174 2006-08-02 14:14:32Z timfox $
                -->



                <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 INTEGER, MESSAGEID INTEGER, TRANSACTIONID INTEGER NULL, STATE CHAR(1) NULL, ORD INTEGER NULL, DELIVERYCOUNT INTEGER NULL, RELIABLE CHAR(1) NULL, LOADED CHAR(1) NULL, 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, RELIABLE 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 <> '+'
                UPDATE_RELIABLE_REFS_NOT_LOADED=UPDATE JMS_MESSAGE_REFERENCE SET LOADED='N' WHERE CHANNELID=?
                SELECT_MIN_ORDERING=SELECT MIN(ORD) FROM JMS_MESSAGE_REFERENCE WHERE CHANNELID=? AND STATE <> '+' AND LOADED = 'N'
                DELETE_UNRELIABLE_REFS=DELETE FROM JMS_MESSAGE_REFERENCE WHERE RELIABLE = 'N'
                CREATE_MESSAGE=CREATE TABLE JMS_MESSAGE (MESSAGEID INTEGER, RELIABLE CHAR(1) NULL, EXPIRATION INTEGER NULL, TIMESTAMP NUMERIC(20,0) NULL, PRIORITY INTEGER NULL, COREHEADERS IMAGE NULL, PAYLOAD IMAGE NULL, CHANNELCOUNT INTEGER NULL, TYPE INTEGER NULL, JMSTYPE VARCHAR(255) NULL, CORRELATIONID VARCHAR(255) NULL, CORRELATIONID_BYTES VARBINARY(254) NULL, DESTINATION_ID INTEGER NULL, REPLYTO_ID INTEGER NULL, JMSPROPERTIES IMAGE NULL, 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) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                UPDATE_MESSAGE_CHANNELCOUNT=UPDATE JMS_MESSAGE SET CHANNELCOUNT=? WHERE MESSAGEID=?
                DELETE_MESSAGE=DELETE FROM JMS_MESSAGE WHERE MESSAGEID=?
                MESSAGEID_COLUMN=MESSAGEID
                <!--
                UPDATE_UNRELIABLE_CHANNELCOUNT=UPDATE JMS_MESSAGE M SET M.CHANNELCOUNT = M.CHANNELCOUNT - 1 WHERE M.MESSAGEID IN (SELECT MR.MESSAGEID FROM JMS_MESSAGE_REFERENCE MR WHERE MR.RELIABLE = 'N' AND MR.CHANNELID = ?)
                -->
                UPDATE_UNRELIABLE_CHANNELCOUNT=UPDATE JMS_MESSAGE SET CHANNELCOUNT = CHANNELCOUNT - 1 WHERE MESSAGEID IN (SELECT MR.MESSAGEID FROM JMS_MESSAGE_REFERENCE MR WHERE MR.RELIABLE = 'N' AND MR.CHANNELID = ?)
                DELETE_UNREFFED_MESSAGES=DELETE FROM JMS_MESSAGE WHERE CHANNELCOUNT = 0
                CREATE_TRANSACTION=CREATE TABLE JMS_TRANSACTION (TRANSACTIONID INTEGER, BRANCH_QUAL VARBINARY(254) NULL, FORMAT_ID INTEGER NULL, GLOBAL_TXID VARBINARY(254) NULL, 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 INTEGER NULL, 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
                SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNELID) FROM JMS_MESSAGE_REFERENCE
                ]]>


                500


                <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) NULL, 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 INTEGER, TYPE CHAR(1) NULL, JMS_DEST_NAME VARCHAR(1024) NULL, JMS_SUB_NAME VARCHAR(1024) NULL, CLIENT_ID VARCHAR(128) NULL, SELECTOR VARCHAR(1024) NULL, NO_LOCAL CHAR(1) NULL, 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=?
                ]]>


                • 5. Re: SQLServer configuration file?
                  Ovidiu Feodorov Master

                   

                  chipschoch wrote:

                  Here is one that works:

                  ....


                  Was this consistently tested? If you're using it and you don't have any problems, I could add it to the example directory that ships with the release (plus the usual disclaimers, until we test it ourselves).