Estimation by Monday

I've looked at HSQLDB and H2 as we discussed and while H2 is starting to be more popular and some sources mention it as more "stable", while HSQL offers the possibility to have stored procedures in plain SQL (a custom dialect of SQL but it is still SQL) and it is not forcing us to create stored procedures in Java (but allows us to do so). But HSQLDB has a few issues:

• it is not possible to modify data within a function which may return a simple value
• it is possible to modify data only within a procedure but it cannot be called with ResultSet rs = ps.executeQuery() but instead the following construction must be used:

ps.execute();
if (ps.getMoreResults()) {
    ResultSet rs = ps.getResultSet();
}

On the other hand, H2 requires us to create stored procedures (aliases) as Java static methods or using Java embedded within SQL query and then allows us to call them with SQL as the only method to create stored procedures. Stored procedure creation with embedded java looks like that:

create alias ensureJid as $$
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.stream.Collectors;
@CODE
ResultSet ensureJid(final Connection conn, String _user_id) throws SQLException {
  PreparedStatement ps = conn.prepareStatement("insert into test (user_id) select * from ( values(?)) where not exists (select 1 from test where user_id = ?)");
  ps.setString(1, _user_id);
  ps.setString(2, _user_id);
  ps.execute();
  ps = conn.prepareStatement("select uid from test where user_id = ?");
  ps.setString(1, _user_id);
  return ps.executeQuery();
}
$$
```
and while it is inconvinient (creation of stored procedure as java embedded in SQL or as static methods (like in case of DerbyDB)) it allows us to use `ResultSet rs = ps.executeQuery()` as we are using for other database is our repository implementations.

As for accessing information schema (details about current schema), both databases allow us to do so. In both cases, we have support for `CREATE IF NOT EXISTS` and `DROP IF EXISTS`, which is the thing which we missed in DerbyDB.

To be honest, both implementations have some pros and cons and it is very difficult to decide on one of them.

**HSQLDB** allows the creation of stored procedures in SQL, but they return data in a different way which would force us to modify a lot of code. (another exception within the code to support HSQLDB - just as we had exceptions for DerbyDB).
**H2** forces us to have stored procedures in Java (or Java embedded in SQL) - similar to DerbyDB but returns data as other databases do.

HSQLDB has one advantage, that it allows us to create blocks within the stored procedure or function and catch SQL exceptions using handlers. However, the same can be achieved with H2 using `try {} catch {}` block within stored procedure java code.
Both databases support locks with MVCC, so there should be no issues with blocking (deadlocks on the database level), but we may expect and need to handle race conditions as only single query are atomic.

I've checked if I can use multiple prepared statements created on the same connection from multiple threads and in both cases it worked just fine, same as `insert into ... select ...` queries - there were no collisions during execution of that query while it sometimes collided on MySQL and SQLServer.

Either way, whatever we would chose we would need **around 40h** to migrate schema and stored procedures to the new dialect.

%kobit %wojtek What do you think? Should we migrate? and if so, what we should aim at? HSQLDB or H2? Both have some pros and cons...

One more thing to consider is that H2 supports clustering in some way, so it could be useful if we would like to create a cluster from Tigase IoT local hubs...

Andrzej Wójcik wrote:

%kobit %wojtek What do you think? Should we migrate? and if so, what we should aim at? HSQLDB or H2? Both have some pros and cons...

HSQLDB seems to be better, except for the special handling of retrieving results. We've been doing it already for Derby (and to some extend MS SQL) so maybe it would be better option in the end (having non-java stored procedures kept in mind)?

At this point, I would say that each of the embedded databases has pros and cons and there is no database about which I would say that it is the best of them.

Does any of the databases have limit on multiple connections (i.e. with Derby it's only possible to make one connection, if used in embedded mode).

%wojtek I think that HSQLDB and H2 do not have other limits than the memory required to keep this connection.

Ok, if this is 40h or so, it's a significant task. So, if Wojciech can work on this right now (if he does not have any other urgent work) then I am OK with him starting the work. Otherwise, if Andrzej has to work on it, I prefer we go Beta first and then work on the new DB support.

Also, if I had to decide which DB to use, I would prefer a solution which allows us to have little or not all DB specific code inside Tigase.

%kobit In this case, it looks like you prefer H2 as it allows us to use almost unmodified Tigase with stored procedures written in Java embedded in SQL, is that correct?

As for the previous comment, I'm assigning this task to Wojciech, as I have a lot of things to complete before the "beta" stage.

Andrzej Wójcik wrote:

%kobit In this case, it looks like you prefer H2 as it allows us to use almost unmodified Tigase with stored procedures written in Java embedded in SQL, is that correct?

Ok.

As for the previous comment, I'm assigning this task to Wojciech, as I have a lot of things to complete before the "beta" stage.

Ok.