jbpm 3.2.GA and mysql

[ghezzi]

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

[ghezzi]

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!

[kukeltje]

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?

[ghezzi]

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

[kukeltje]

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