In addition: is there a specific set of privileges that the user a client uses to connect to the JBPM database is running as must have?
I can see that I can have hibernate create the objects in the schema automatically - fine. I can then reverse engineer the database structure into DDL. Fine.
The database user you would need to run as in order for hibernate to create the table structure would require the access to create tables and other objects in the specified schema... ie: probably the schema owner. Most security minded folk wouldn't be comfortable running a client application to be connecting to the database as that user, however. Presumably the runtime user could simply have read and write access to the tables instead? Any advice?
As far as I know jBPM does not ship with out of the box DDLs for various data bases.
An option to get DDL is to use hibernate tools (look at JBoss Tools) to generate DDL files for entities that jBPM relies on, same goes for human task server. Once you have DDL, DBA can proceed with schema creation. I believe that regular CRUD access would be most likely enough on runtime to operate.
I would have to say that although i have got this stuff all working with Oracle now - reliance on JPA and persistence units for the database creation is just a hideous nightmare.
The fact that you have to trawl into various jar files to modify persistence.xml files and mappings if your database platform varies from the provided h2 database is a real headache. Even just changing the database host for different environments is non-trivial - particularly if you want your application and the JBPM toolset to point to a persistent database.
If you are planning on using Maven for your build process, and plan on running jbpm inside a container, be prepared for significant packaging issues!
The provided hibernate mappings are not compatible with Hibernate 4 and Oracle. The Maven dependencies for 5.2.0.Final are not compatible with JBoss AS7. Virtually every step of what should have been a simple process has been time consuming and frustrating.
If you are looking for an 'install and use' component in your system to provide BPMN workflow capability - i definitely wouldn't recommend JBPM5 at this stage. I would be looking at Bonita or something more mature.
Unfortunately, we had this part of the technology dictated to us for a proof-of-concept. I will certainly have a reasonable amount to say in the lessons learned phase of the POC.
I also wonder what the upgrade path is going to be for these components? If DDL was provided, at least you could assess if there were any structural changes - and plan data migrations to the new strucutre. As far as I can tell, the process currently would be to run the jbpm componets with hibernate auto create set to 'create' or 'update', assess if the resulting schema differs from the previous one - and then worry about how you get your data from the old one to the new one.
Cameron, thanks a lot for your valuable feedback, in fact I believe that thanks to this kind of opinion and concrete problems jBPM will become more mature soon as well. I think that significant step has been taken with 5.3 to provide smooth integration with JBoss AS7 and hibernate 4 and prior to that hibernate 4 (JPA2) was not supported at all. I am not saying that we are all good but it is step in right direction and will are going to continue to improve in this area as well.
I would like to encourage you to create jira for things that you would like to see in the upcoming release as that help us to prioritize work and we appreciate community feedback and involvement.