Getting HSQLDB to work with Ametys

kenjoey · 04/07/2013 01:35:56

Hi,

I like HSQLDB and want to use it with Ametys. I created a database using the attached SQL script. I configured CMS to use the HSQLDB database. But when I tried to create a new user, Ametys gives me "Error: Server failed (code 500)".

How do I set up HSQLDB database for use with Ametys?

Ken

More information on the error follows:

Could not create a validated object, cause: ValidateObject failed

java.util.NoSuchElementException: Could not create a validated object, cause: ValidateObject failed
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1191)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
at org.ametys.runtime.datasource.ConnectionHelper.getConnection(ConnectionHelper.java:98)
at org.ametys.runtime.plugins.core.user.jdbc.ModifiableJdbcUsersManager.add(ModifiableJdbcUsersManager.java:153)
at org.ametys.runtime.plugins.core.user.ui.actions.EditAction.act(EditAction.java:110)

kenjoey · 04/07/2013 01:48:05

I don't know why attachements are not showing up. So I pasted the SQL script at the following link.

I hope somebody try it and see if it works with Ametys

Ken

Nicolas Gavalda · 04/07/2013 14:35:23

Hi, Ametys currently supports only MySQL, Oracle, Derby and Postgres DBMS. Whereas you could easily adapt the table creation scripts, some parts of the CMS use DBMS-specific code, and there's very little chance all of it will work with HSQLDB.

The problem here is that the CMS uses DBCP for connection pooling, and the connection testing query doesn't seem to work with HSQLDB.

Apache Derby is fully compatible with Ametys, and seems to cover the same features as HSQLDB (embedded or server mode). Is there a specific reason for you to use HSQLDB or could you consider using Derby instead?

You still can use HSQLDB as a storage engine for the repository, as jackrabbit is compatible with it. There's absolutely no need to use the same database engine for the jackrabbit storage and for the CMS.

kenjoey · 04/07/2013 19:54:27

Hi, Nicolas

The reason why I want to use HSQLDB instead is that I believe HSQLDB is a bit quicker than Derby and more lightweight than other databases.

As far as I know, HSQLDB is compatible with Apache Commons DBCP library. Is there a way to configure DBCP for CMS?

Ken

kenjoey · 05/07/2013 10:25:49

Hi,

I succeeded in making CMS work with HSQLDB. To allow Ametys to handle HSQLDB database, I hacked two files in the Ametys codes: DataSourceExtensionPoint.java and ConnectionHelper.java. Here are the patches.

I hope somebody review the patches, test them and accept them into the upstream source.

Ken

Nicolas Gavalda · 05/07/2013 11:20:16

HSQLDB is compatible with DBCP, but the way we use it unfortunately prevent them to work together: we provide a "validation query" to DBCP, depending on the JDBC driver in use. Currently this validation query is hardcoded, and cover only the DMBS we support (it's in the DataSourceExtensionPoint class, if you want to have a look).

Even if we made connection pooling work (by adding a validation query compatible with HSQLDB, or by providing a way to define custom validation queries in a configuration file, for instance), there would be many parts of Ametys which would malfunction, or even not work at all.

As I said, some features of the CMS (including low-level ones, such as group or rights management) use DMBS-specific code, and such specific code for HSQLDB hasn't been written yet. If you want, you can open a new feature ticket in our issue tracker () to request this, and, if you have the time and interest to implement this feature yourself, I can give you some pointers on where to begin.

Nicolas Gavalda · 05/07/2013 11:33:46

Okay, sorry, I hadn't seen your previous message with the patches.
I encourage you to open the feature request ticket in our issue tracker, and attach your patches to it. However, there will be a bit more work to do to adapt the DBMS-specific code I was talking about, but nothing impossible

kenjoey · 05/07/2013 12:04:59

Okay, I'll open a feature request ticket and attach my patches.

I found that my patches only fix validation queries for HSQLDB. There are more work to be done with group and rights management.

Nicolas Gavalda · 05/07/2013 12:31:41

The exact DMBS-specific code can be found:

kenjoey · 25/02/2014 07:53:43

Hi, I'm back.
I am trying to create a HSQLDB database manually, but I came across a question. Does Ametys expect table names to be all capital or case sensitive? HSQLDB treats all unquoted identifiers as capital, so I have to use quotes to make HSQLDB use case-sensitive names. For example,

CREATE TABLE Users (
login varchar(64) PRIMARY KEY NOT NULL,
firstname varchar(64) default NULL,
lastname varchar(64) NOT NULL,
email varchar(64) DEFAULT NULL
);

This command creates a table

Nicolas Gavalda · 03/03/2014 17:01:16

In its default database Users implementation, Ametys uses unquoted lowercase columns in its queries.

Then, it depends on the JDBC driver you use: for instance, Oracle creates all its identifiers (table name, column name, and so on) uppercase but is not sensitive to the case in the queries.

If the HSQLDB JDBC driver is case-sensitive, you should use quoted identifiers in your creation script to make them lowercase. The alternative would be to declare a specific JdbcUsersManager which would work on capital columns (it's easily done, it is only XML configuration).

Forum Ametys

#1 04/07/2013 01:35:56

Getting HSQLDB to work with Ametys

#2 04/07/2013 01:48:05

Re: Getting HSQLDB to work with Ametys

#3 04/07/2013 14:35:23

Re: Getting HSQLDB to work with Ametys

#4 04/07/2013 19:54:27

Re: Getting HSQLDB to work with Ametys

#5 05/07/2013 10:25:49

Re: Getting HSQLDB to work with Ametys

#6 05/07/2013 11:20:16

Re: Getting HSQLDB to work with Ametys

#7 05/07/2013 11:33:46

Re: Getting HSQLDB to work with Ametys

#8 05/07/2013 12:04:59

Re: Getting HSQLDB to work with Ametys

#9 05/07/2013 12:31:41

Re: Getting HSQLDB to work with Ametys

#10 25/02/2014 07:53:43

Re: Getting HSQLDB to work with Ametys

#11 03/03/2014 17:01:16

Re: Getting HSQLDB to work with Ametys

Board footer