Difference between revisions of "Using OpenKM with other databases"

Revision as of 14:36, 15 November 2011

By default, OpenKM uses an embedded database called HSQLDB. This database is integrated into JBoss and offers good performance and low hardware requirements. But sometimes it is not enough for some users who need OpenKM to be deployed within their IT structure, which may be based on another database vendor.

Starting with OpenKM 5, you can create the databases automatically by configuring the hibernate.dialect and hibernate.hbm2ddl properties in OpenKM.cfg.

hibernate.dialect=org.hibernate.dialect.HSQLDialect
hibernate.hbm2ddl=create

Once the tables are created, change the hibernate.hbm2ddl property from create to none. Also, a couple of new configuration properties are of interest:

hibernate.show_sql=false
hibernate.statistics=true

With these properties you can configure if you want to see the SQL sentences generated by Hibernate, and enable Hibernate statistics. Only meaningful for development and fine tuning.

Generating database script

If you want to see the table creation script, create, compile and run this piece of code:

import java.io.File;

import org.hibernate.cfg.Configuration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class Test {
  public static void main(String[] args) throws Exception {
    Configuration cfg = new Configuration();
    cfg.configure(new File("/path/to/hibernate.cfg.xml"));
    SchemaExport se = new SchemaExport(cfg);
    se.setOutputFile("/path/to/schema.sql");
    se.setFormat(true);
    se.create(false, false);
  }
}

This configuration property should be set before the database creation. Once the database has been initialized don't modify it because you can damage your installation. If your OpenKM installation has been already configured with another database (default one is an embedded one called HSQLDB) you can't switch to another database simply by changing this property.

Here we will discuss the changes needed to run OpenKM with these databases:

• MySQL
• PostgreSQL
• MS SQLServer
• Oracle

See also:

• How To Generate DDL Scripts from Hibernate