Forum Ametys

Forum de la communauté Ametys

You are not logged in.

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Getting HSQLDB to work with Ametys

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)

Offline

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Re: Getting HSQLDB to work with Ametys

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

http://pastebin.com/1XBbkGC6

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

Ken

Offline

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

Nicolas Gavalda
Expert Team
From: Toulouse
Registered: 10/08/2010
Posts: 32

Re: Getting HSQLDB to work with Ametys

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.

Offline

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Re: Getting HSQLDB to work with Ametys

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

Offline

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Re: Getting HSQLDB to work with Ametys

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.

http://pastebin.com/gtHJdv6V
http://pastebin.com/v3hwM7qZ

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

Ken

Offline

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

Nicolas Gavalda
Expert Team
From: Toulouse
Registered: 10/08/2010
Posts: 32

Re: Getting HSQLDB to work with Ametys

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 (https://issues.ametys.org) 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.

Offline

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

Nicolas Gavalda
Expert Team
From: Toulouse
Registered: 10/08/2010
Posts: 32

Re: Getting HSQLDB to work with Ametys

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  smile

Offline

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Re: Getting HSQLDB to work with Ametys

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.

Offline

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

Nicolas Gavalda
Expert Team
From: Toulouse
Registered: 10/08/2010
Posts: 32

Re: Getting HSQLDB to work with Ametys

The exact DMBS-specific code can be found:

  • in the Runtime, in users, groups, rights and user preference management

  • in the Forms plugin (which is optional), to store and retrieve user submissions

  • in the Survey plugin (which is optional), to store and retrieve user submissions

Offline

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

kenjoey
Guest
Registered: 16/06/2013
Posts: 12

Re: Getting HSQLDB to work with Ametys

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

Offline

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

Nicolas Gavalda
Expert Team
From: Toulouse
Registered: 10/08/2010
Posts: 32

Re: Getting HSQLDB to work with Ametys

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).

Offline

Board footer

Powered by FluxBB