5 Replies Latest reply on Oct 8, 2008 8:19 AM by Ronald van Kuijk

    Switching the Database Backend and jBPM 3.2.3

    Daniel Rowley Newbie

      G'day,

      I'm from a large government department in Australia, that is currently evaluating JBoss jBPM, through the construction of a prototype using a three-tier architecture (presentation, business, and data).

      We are using jBPM 3.2.3, and have come across some problems, at the data layer of our architecture, while referring to Chapter 8 of the JBoss jBPM jPDL 3.2 User Guide; very early days.

      http://docs.jboss.org/jbpm/v3/userguide/thejbpmdatabase.html#d0e2578

      In Chapter 8, it reads:

      "Making the default webapp talk to the correct datasource is again not very difficult. The first step in doing this is simply locate the 'jboss-service.xml' file in the folder '${JBPM_SDK_HOME}/jbpm-server/server/jbpm/deploy/jbpm.sar/META-INF'. Change the contents of this file with the contents of the listing below. An attentive reader will notice that the only difference is an exchange of the token 'DefaultDS' by 'JbpmDS'."

      However, in our installation of jBPM 3.2.3, we cannot find the folder:

      '${JBPM_SDK_HOME}/jbpm-server/server/jbpm/deploy/jbpm.sar'.

      Is Chapter 8 out of sync with 3.2.3 or have I done something incorrectly?

      Thanks heaps,

      Dan Rowley

        • 1. Re: Switching the Database Backend and jBPM 3.2.3
          Ronald van Kuijk Master

          First http://docs.jboss.org/jbpm/v3.2/userguide/html/ is better to be used. Docs cleaning up is currently going on

          Besides that:

          Is Chapter 8 out of sync with 3.2.3 or have I done something incorrectly?
          Yes, even in the 3.2 docs

          Better to read:

          http://wiki.jboss.org/wiki/JbpmContributedDocs (the 3.2.2. getting started), chapter 3.5

          http://wiki.jboss.org/wiki/JbpmDbCompatibility


          • 2. Re: Switching the Database Backend and jBPM 3.2.3
            Daniel Rowley Newbie

            Thank you for your time, kukeltje; I ended up using pages 68-71 of the Business Processing Modelling using jBPM 3.2.2 Guide, available via those links that you had provided. I am currently migrating jBPM to a PostgreSQL database, and have made some clarifications to the Guide, which I'll make available on the wiki once I organise access to it; I'm pretty sure that migrating jBPM to a database other than the in-built one (Hypersonic) is a pretty common task.

            One thing that wasn't too clear, and something I believe is preventing a log in to the jBPM-console using the new database, is how to populate the JBPM_ID_GROUP, JBPM_ID_USER, and JBPM_MEMBERSHIP tables (logically, not technically). From what I understand, a user who wants to, at least, log into the console needs, at least, a 'user' role. While I understand the (primary key-foreign key) relationships between the tables, it was unclear to me as to:

            - the purpose of the TYPE_ field of the JBPM_ID_GROUP.

            Any chance you could make clear as to whether or not values in that table field could be preventing valid logins to the console?

            I understand that one of the steps involved in migrating jBPM to another database, involves editing the <application-policy> of the login-config.xml (found in <JBPM_JPDL_HOME>/server/server/jbpm/conf/). Instructions for that step read:

            "... So before creating a datasource make sure that the datasource has been deployed on the server and the jndi has got registered to the server and is specified appropriately with the login-config.xml Otherwise most of the time a login violation occurs at the jBPM console due to inappropriate jndi specification."

            Am I correct in understanding that to mean that my new database should be up and running on the server, before I mess around with login-config.xml? I have a feeling I've misinterpreted that instruction.

            Below is a quotation of my login-config.xml.

            Any help you could provide, would be much appreciated.


            <application-policy name = "jbpm">
            <authentication>
            <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
            flag="required">
            <module-option name="dsJndiName">java:/JbpmDSpostgresql</module-option>
            <module-option name="principalsQuery">
            SELECT PASSWORD_ FROM JBPM_ID_USER WHERE NAME_=?
            </module-option>
            <module-option name="rolesQuery">
            SELECT g.NAME_ ,'Roles'
            FROM JBPM_ID_USER u,
            JBPM_ID_MEMBERSHIP m,
            JBPM_ID_GROUP g
            WHERE g.TYPE_='security-role'
            AND m.GROUP_ = g.ID_
            AND m.USER_ = u.ID_
            AND u.NAME_=?
            </module-option>
            </login-module>
            </authentication>
            </application-policy>


            Kindest regards,

            Dan Rowley

            • 3. Re: Switching the Database Backend and jBPM 3.2.3
              Daniel Rowley Newbie

              My jbpm-ds.xml file also contains:

              <datasources>
              
               <local-tx-datasource>
               <jndi-name>JbpmDSpostgresql</jndi-name>
               <connection-url>jdbc:postgresql://localhost:5432/jBPMdb</connection-url>
               <driver-class>org.postgresql.Driver</driver-class>
               <user-name>postgres</user-name>
               <password>admin</password>
               <metadata>
               <type-mapping>PostgreSQL 8.3</type-mapping>
               </metadata>
               </local-tx-datasource>
              
              </datasources>



              "danrowley" wrote:
              Thank you for your time, kukeltje; I ended up using pages 68-71 of the Business Processing Modelling using jBPM 3.2.2 Guide, available via those links that you had provided. I am currently migrating jBPM to a PostgreSQL database, and have made some clarifications to the Guide, which I'll make available on the wiki once I organise access to it; I'm pretty sure that migrating jBPM to a database other than the in-built one (Hypersonic) is a pretty common task.

              One thing that wasn't too clear, and something I believe is preventing a log in to the jBPM-console using the new database, is how to populate the JBPM_ID_GROUP, JBPM_ID_USER, and JBPM_MEMBERSHIP tables (logically, not technically). From what I understand, a user who wants to, at least, log into the console needs, at least, a 'user' role. While I understand the (primary key-foreign key) relationships between the tables, it was unclear to me as to:

              - the purpose of the TYPE_ field of the JBPM_ID_GROUP.

              Any chance you could make clear as to whether or not values in that table field could be preventing valid logins to the console?

              I understand that one of the steps involved in migrating jBPM to another database, involves editing the <application-policy> of the login-config.xml (found in <JBPM_JPDL_HOME>/server/server/jbpm/conf/). Instructions for that step read:

              "... So before creating a datasource make sure that the datasource has been deployed on the server and the jndi has got registered to the server and is specified appropriately with the login-config.xml Otherwise most of the time a login violation occurs at the jBPM console due to inappropriate jndi specification."

              Am I correct in understanding that to mean that my new database should be up and running on the server, before I mess around with login-config.xml? I have a feeling I've misinterpreted that instruction.

              Below is a quotation of my login-config.xml.

              Any help you could provide, would be much appreciated.


              <application-policy name = "jbpm">
              <authentication>
              <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
              flag="required">
              <module-option name="dsJndiName">java:/JbpmDSpostgresql</module-option>
              <module-option name="principalsQuery">
              SELECT PASSWORD_ FROM JBPM_ID_USER WHERE NAME_=?
              </module-option>
              <module-option name="rolesQuery">
              SELECT g.NAME_ ,'Roles'
              FROM JBPM_ID_USER u,
              JBPM_ID_MEMBERSHIP m,
              JBPM_ID_GROUP g
              WHERE g.TYPE_='security-role'
              AND m.GROUP_ = g.ID_
              AND m.USER_ = u.ID_
              AND u.NAME_=?
              </module-option>
              </login-module>
              </authentication>
              </application-policy>


              Kindest regards,

              Dan Rowley


              • 4. Re: Switching the Database Backend and jBPM 3.2.3
                Daniel Rowley Newbie

                After inspecting the SELECT statement in my login-config.xml, I realised that the TYPE_ field of the JBPM_ID_GROUP should contain values of "security-role".

                I'll make this explicit in the Wiki contribution I'll be making soon.

                I hope this has helped others,

                Dan Rowley

                "danrowley" wrote:
                My jbpm-ds.xml file also contains:

                <datasources>
                
                 <local-tx-datasource>
                 <jndi-name>JbpmDSpostgresql</jndi-name>
                 <connection-url>jdbc:postgresql://localhost:5432/jBPMdb</connection-url>
                 <driver-class>org.postgresql.Driver</driver-class>
                 <user-name>postgres</user-name>
                 <password>admin</password>
                 <metadata>
                 <type-mapping>PostgreSQL 8.3</type-mapping>
                 </metadata>
                 </local-tx-datasource>
                
                </datasources>



                "danrowley" wrote:
                Thank you for your time, kukeltje; I ended up using pages 68-71 of the Business Processing Modelling using jBPM 3.2.2 Guide, available via those links that you had provided. I am currently migrating jBPM to a PostgreSQL database, and have made some clarifications to the Guide, which I'll make available on the wiki once I organise access to it; I'm pretty sure that migrating jBPM to a database other than the in-built one (Hypersonic) is a pretty common task.

                One thing that wasn't too clear, and something I believe is preventing a log in to the jBPM-console using the new database, is how to populate the JBPM_ID_GROUP, JBPM_ID_USER, and JBPM_MEMBERSHIP tables (logically, not technically). From what I understand, a user who wants to, at least, log into the console needs, at least, a 'user' role. While I understand the (primary key-foreign key) relationships between the tables, it was unclear to me as to:

                - the purpose of the TYPE_ field of the JBPM_ID_GROUP.

                Any chance you could make clear as to whether or not values in that table field could be preventing valid logins to the console?

                I understand that one of the steps involved in migrating jBPM to another database, involves editing the <application-policy> of the login-config.xml (found in <JBPM_JPDL_HOME>/server/server/jbpm/conf/). Instructions for that step read:

                "... So before creating a datasource make sure that the datasource has been deployed on the server and the jndi has got registered to the server and is specified appropriately with the login-config.xml Otherwise most of the time a login violation occurs at the jBPM console due to inappropriate jndi specification."

                Am I correct in understanding that to mean that my new database should be up and running on the server, before I mess around with login-config.xml? I have a feeling I've misinterpreted that instruction.

                Below is a quotation of my login-config.xml.

                Any help you could provide, would be much appreciated.


                <application-policy name = "jbpm">
                <authentication>
                <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
                flag="required">
                <module-option name="dsJndiName">java:/JbpmDSpostgresql</module-option>
                <module-option name="principalsQuery">
                SELECT PASSWORD_ FROM JBPM_ID_USER WHERE NAME_=?
                </module-option>
                <module-option name="rolesQuery">
                SELECT g.NAME_ ,'Roles'
                FROM JBPM_ID_USER u,
                JBPM_ID_MEMBERSHIP m,
                JBPM_ID_GROUP g
                WHERE g.TYPE_='security-role'
                AND m.GROUP_ = g.ID_
                AND m.USER_ = u.ID_
                AND u.NAME_=?
                </module-option>
                </login-module>
                </authentication>
                </application-policy>


                Kindest regards,

                Dan Rowley


                • 5. Re: Switching the Database Backend and jBPM 3.2.3
                  Ronald van Kuijk Master

                  regarding the roles... there were already topics in this forum, articles in the wiki and in this getting started link I mailed (afaik)