Introduction
As YAWL uses hibernate as persistence layer it supports every database that Hibernate supports. (see here) All you need to do is to configure the database in the hibernate.properties file of the three web applications "resourceService", "yawl" and "workletService". This is briefly explained in the YAWL user manual section 2.4.4. In the following sections we provide detailed information to get YAWL working with different database management systems.
Databases
Regardless the database you'd like to use, there is one (since YAWL 3) configuration file you have to adjust:
-
\engine\apache-tomcat-x.x.xx\yawllib\hibernate.properties
Apache Derby
The YAWL4Study edition is pre-configured to use the embedded database Apache Derby. Although this database provides full support for YAWL it is generally not recommended to be used in a production system. For development or evaluation use it is sufficient. The database is located in the directory \engine\databases of your YAWL4Study installation. Details on building the correct connection url for Apache Derby can be found in the Derby documentation.
hibernate.properties:
## Apache Derby hibernate.dialect=org.hibernate.dialect.DerbyDialect hibernate.connection.url=jdbc:derby://localhost/C:/Users/UserXY/YAWL4Study-2.2.01/engine/databases/yawldb;create=true hibernate.connection.driver_class=org.apache.derby.jdbc.ClientDriver
PostgreSQL
PostgreSQL is the default database of the YAWL enterprise edition. Therefore the easiest way to use PostgreSQL is to install the enterprise edition of YAWL and you will be asked for details about your PostgreSQL server during installation. But if you already installed YAWL4Study, you have to adjust your hibernate.properties to the following configuration. Details on building the correct connection url for PostgreSQL can be found in the PostgreSQL documentation.
hibernate.properties:
## PostgreSQL hibernate.dialect org.hibernate.dialect.PostgreSQLDialect hibernate.connection.driver_class org.postgresql.Driver hibernate.connection.url jdbc:postgresql:yawl hibernate.connection.username yawl hibernate.connection.password yawl
MySQL
To use MySQL you should install the YAWL enterprise edition, as MySQL is a configuration option during the installation. If you'd like to switch to MySQL later on, then adjust the hibernate.properties files as follows and add the correct MySQL JDBC Driver to the lib directory of YAWLs Apache Tomcat. Details on building the correct connection url for MySQL can be found in the MySQL documentation. You may want to choose the MySQL5InnoDBDialect instead of the usual MySQlDialect if you want YAWL to generate InnoDB tables. (Read more about InnoDB)
MySQL JDBC Driver:
- Download JDBC Driver from http://dev.mysql.com/downloads/connector/j/
- Copy mysql-connector-java-5.x.xx-bin.jar to \engine\apache-tomcat-6.0.18\lib
hibernate.properties:
## MySQL #hibernate.dialect org.hibernate.dialect.MySQL5InnoDBDialect hibernate.dialect org.hibernate.dialect.MySQLDialect hibernate.connection.driver_class com.mysql.jdbc.Driver hibernate.connection.url jdbc:mysql:///yawl hibernate.connection.username root hibernate.connection.password root
MSSQL (SQL Server 2005/2008)
To use MSSQL you need to adjust the hibernate.properties files as follow and add the correct MSSQL JDBC Driver to the lib directory of YAWLs Apache Tomcat. Please note, the Microsoft JDBC Driver is not Open Source and there are other implementations available. (For Example jDTS) Details on building the correct connection url for MSSQL can be found in the MSSQL documentation.
MSSQL JDBC Driver:
- Download JDBC Driver from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=21599
- Copy sqljdbc4.jar to \engine\apache-tomcat-6.0.18\lib
hibernate.properties:
## MS SQL Server hibernate.connection.driver_class com.microsoft.sqlserver.jdbc.SQLServerDriver hibernate.connection.url jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=yawl;user=yawl;password=yawl;SelectMethod=cursor hibernate.dialect org.hibernate.dialect.SQLServerDialect hibernate.connection.username yawl hibernate.connection.password yawl
YAWL ignores hibernate properties
I am using YAWL4Study 2.3 on an Ubuntu Linux.
I set up a postgres database with a database called 'yawl' and a user 'yawl' with password 'yawl' who owns the yawl database and has administration rights.
Then I modified the three hibernate properties files like this:
**********************************************************
## PostgreSQL
hibernate.dialect org.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class org.postgresql.Driver
hibernate.connection.url jdbc:postgresql:yawl
hibernate.connection.username yawl
hibernate.connection.password yawl
## H2
#hibernate.dialect=org.hibernate.dialect.H2Dialect
#hibernate.connection.driver_class=org.h2.Driver
#hibernate.connection.url=jdbc:h2:/home/ahense/YAWL4Study-2.3final/engine/databases/yawldb;MVCC=TRUE
**********************************************************
After restarting the engine, YAWL keeps using the file-based database and does not create any tables in postgres.
What am I missing here?
It should work
I tried to run with all 'hibernate.properties' files modified like in your configuration. YAWL creates the tables in PostgreSQL automatically and is running fine. Can you check the console output of YAWL (or log file if used as service) for lines like the following and post them.
It should work but it doesn't
Thank you. I have tried with several fresh installations of YAWL 2.3: it keeps using the file for persistence and ignores postgres. Even renaming the three hibernate.property files didn't work.
I have tried it with YAWL 2.2: it used postgres right away after changing the hibernate.property files accordingly.
I have looked at all the files in the logs directory of tomcat but there are no lines like the ones you posted. The catalina.out looks like this:
Maybe the log4j warnings give some hints?
The log4j warnings are not
The log4j warnings are not about the issue. Unfortunately I can't test with Linux at the moment. Recheck if there is a file like "postgresql-9.0-801.jdbc4.jar" in the "engine/apache-tomcat-6.0.18/lib" of YAWL.
Please attach all log files (I just enabled "attaching files in comments"), that includes log files in "engine/apache-tomcat-6.0.18/bin" and "engine/apache-tomcat-6.0.18/logs". Probably it is best to upload the whole, zipped, YAWL directory and I will have a look.
Here are the logs
The postgresql-9.0-801.jdbc4.jar is present. I attached the logs here.
Unfortunately the logs did
Unfortunately the logs did not contain any information about the database connection.
Another file to be adjusted
I am also using YAWL4Study 2.3.5 on an Ubuntu Linux and faced the same problem.
After adjusting the configuration file \engine\apache-tomcat-6.0.18\webapps\documentStore\WEB-INF\classes\hibernate.properties as mentioned above everything worked fine for me.
I can confirm that adjusting
I can confirm that adjusting the configuration file \engine\apache-tomcat-6.0.18\webapps\documentStore\WEB-INF\classes\hibernate.properties is indeed needed and shoud be added as a required step in this tutorial.
Remove the h2 connection parameters completely
i also faced the same issue. i sorted it by removing the other connections for the database and placed the connection to my database alone in hibernate.properties.
Configure YAWL to use Microsoft MSSQL
I'm using YAWL 4 with MSSQL 2008. All work fine aside handling YDocument. Im using YAWL with the data store service. By default the yDoc column in the YDocument table is defined as varbinary(255).
It can be extended to 8000 (maximum size allowed by MSSQL) adapting the function fixH2BinarySize in DocumentSore.java to work also for MSSQL db.
To handle bigger file I had to modify the methods getDocument and PutDocument to store and retrive document directly in/from the filesystem.
Please contact me if you solved the same problem in other ways.