4 Replies Latest reply on Jun 25, 2007 7:13 AM by Ronald van Kuijk

    jbpm 3.2.GA and mysql

    ghezzi Newbie

      Hello,

      I've downloaded the starter kit 3.2.GA

      I was able to run it on my localhost but i need to make it run on
      a jboss 4.0.5GA with mysql which will be my production
      environment.

      I read this thread http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4031188#4031188
      but i can't understand how to migrate the hypersonic db to mysql

      I get the jbpm-jpdl-3.2.GA\db\jbpm.jpdl.mysql.sql
      and run it on the mysql (btw it need at list ; on all lines)
      but some tables are missing (at least the JBPM_ID_USER )
      and throws some errors

      Furthermore the scripts assumes that the db is already there and
      that it just need to be patched

      I can't believe that the distribution doesn't have a mysql scripts
      to create from scratch a correct db

      I've already jump around docs,wiki and forum without
      any success.

      Thanks in advance

        • 1. Re: jbpm 3.2.GA and mysql
          ghezzi Newbie

          I was able to make it run
          using this approach:

          - execute in mysql the incomplete jbpm.jpdl.mysql.sql provided in the starter kit
          - manually create the missing tables

          create table JBPM_ID_GROUP (
          ID_ bigint not null auto_increment,
           CLASS_ char( 1) not null,
           NAME_ varchar(255),
           TYPE_ varchar(255),
           PARENT_ bigint,
           primary key (ID_));
          create table JBPM_ID_MEMBERSHIP (
          ID_ bigint not null auto_increment,
           CLASS_ char(1) not null,
           NAME_ varchar(255),
           ROLE_ varchar(255),
           USER_ bigint,
           GROUP_ bigint,
           primary key ( ID_));
          create table JBPM_ID_PERMISSIONS (
           ENTITY_ bigint not null,
           CLASS_ varchar(255),
           NAME_ varchar(255),
           ACTION_ varchar(255));
          create table JBPM_ID_USER (
          ID_ bigint not null auto_increment,
           CLASS_ char(1 ) not null,
           NAME_ varchar(255),
           EMAIL_ varchar(255),
           PASSWORD_ varchar(255),
           primary key (ID_));
          alter table JBPM_ID_GROUP add constraint FK_ID_GRP_PARENT foreign key (PARENT_) references JBPM_ID_GROUP;
          alter table JBPM_ID_MEMBERSHIP add constraint FK_ID_MEMSHIP_GRP foreign key (GROUP_) references JBPM_ID_GROUP;
          alter table JBPM_ID_MEMBERSHIP add constraint FK_ID_MEMSHIP_USR foreign key (USER_) references JBPM_ID_USER;


          - manually insert the missing data

          INSERT INTO JBPM_ID_GROUP VALUES(1,'G','participant','security-role',NULL);
           INSERT INTO JBPM_ID_GROUP VALUES(2,'G','administrator','security-role',NULL);
           INSERT INTO JBPM_ID_GROUP VALUES(3,'G','hr','organisation',NULL);
           INSERT INTO JBPM_ID_GROUP VALUES(4,'G','sales','organisation',NULL);
           INSERT INTO JBPM_ID_GROUP VALUES(5,'G','manager','security-role',NULL);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(1,'M',NULL,NULL,2,2);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(2,'M',NULL,NULL,3,1);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(3,'M',NULL,NULL,3,3);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(4,'M',NULL,NULL,2,3);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(5,'M',NULL,NULL,1,4);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(6,'M',NULL,'boss',2,4);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(7,'M',NULL,NULL,2,5);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(8,'M',NULL,NULL,2,1);
           INSERT INTO JBPM_ID_MEMBERSHIP VALUES(9,'M',NULL,NULL,1,1);
           INSERT INTO JBPM_ID_USER VALUES(1,'U','cookie monster','[EMAIL PROTECTED]','cookie monster');
           INSERT INTO JBPM_ID_USER VALUES(2,'U','ernie','[EMAIL PROTECTED]','ernie');
           INSERT INTO JBPM_ID_USER VALUES(3,'U','bert','[EMAIL PROTECTED]','bert');
           INSERT INTO JBPM_ID_USER VALUES(4,'U','grover','[EMAIL PROTECTED]','grover');


          - open the jbpm-enterprise.ear
          open the jbpm-configs.tar in it and change the hibernate-cfg.xml
          for customizing the dialect mysql

          - change in jboss the login-config.xml module
          to include the authentication versus the jbpm db
           <application-policy name = "jbpm">
           <authentication>
           <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
           flag="required">
           <module-option name="dsJndiName">java:/JbpmDS</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>
          


          security domain should be jbpm as verified in jboss-web.xml of the jbpm-console.war


          ....now its running...

          hope this helps some other unlucky guy who is working with the
          unsable documentation of jbpm!

          • 2. Re: jbpm 3.2.GA and mysql
            Ronald van Kuijk Master

            sorry, but 90% of what you describe here is in the wiki

            on this page there is a nice link to jBPM 3.2 on Tomcat 6.0 with MySQL or Hypersonic

            The only difference is your addition to the jboss login stuff...

            And that could have been answered with a small question in this forum....

            Btw, care to change/adapt/expand/.... the wiki pages?

            • 3. Re: jbpm 3.2.GA and mysql
              ghezzi Newbie

              it could be ....but it's very diffucult
              to understand that instruction to setup mysql are
              under "jbpm on tomcat" while i'm installing on jboss....

              Anyway thanks for the suggestion

              • 4. Re: jbpm 3.2.GA and mysql
                Ronald van Kuijk Master

                Maybe we should restructure the wiki data a little... care to help out?