After focusing on feature completeness and ironing out the inevitable bugs the time has come to see if [fleXive]‘s core is really as portable to other database backends than MySQL as intended and designed.
The idea was to use a native Java database for the (expected) small footprint and to be able to provide a platform independent installer. The current installer is only available for Windows and contains an embedded MySQL 5.0 installation. Another reason – as Daniel mentioned in his blog entry about Maven – was to be able to download and distribute [fleXive] using the Maven 2.0 buildsystem and be able to include a database which is easy to run, install and maintain.
Why H2? Well initially I wanted to use Apache Derby / JavaDB since I already embedded it in other customer projects but since I never worked with H2 and only heard great things about it I wanted to give it a try and see how things work out.
To make a long story short: it worked out great! I am impressed by the performance H2 has to offer and the compatibility to the MySQL dialect, although I stumbled across a few issues and gotcha’s:
- We use the id and version of a content instance as the primary key and rely on references to the id. Creating foreign keys in MySQL that way works without any problems but H2 insists to create a unique index for the id column in addition to the combined primary key (id, version) as well. It can be argued that this behaviour is correct but for [fleXive] it completely broke the versioning concept since now only one version per instance is allowed or an exception will be thrown because the silently introduced unique index constraint is violated. There is no real workaround that I could figure out and I had to disable the foreign key constraint for this references which of course makes it hard to recommend H2 for production use on [fleXive].
- H2 has a weird sense on when to use parentheses and when not. Take this example:
insert into ... (select ... from)will throw an error while the same statement without the parentheses works like a charm:insert into ... select ... from… maybe a bug in the parser? - Usage of variables: We use a variable in search queries to determine the rowcount. This variable is initialized using the syntax
set @rownum=0;and then incrementing it in a select statement using@rownum:=@rownum+1. The first gotcha is that assignments with := do not work, but they do without the colon. This seemed to work well, but somehow the ordering was no longer correct. After some searching the (rather simple but not too intuitive) solution was to use the SET function:SET(@rownum, @rownum+1)instead. - Query timeouts:
statement.setQueryTimeout(15/*seconds*/);is not working and will prevent the query from being executed (at least for me). - The (lack of) row locks: If I had one wish for free this would be the one I have for H2
. Using the (experimental) MVCC locking I could work around this issue but it is still not a perfect solution. - Order By: Works well in most cases like in subqueries in the select clause or the main where clause. There are however some serious issues if used in a subquery in the main queries where clause: the query parser just wont compile the query.
- If using H2′s webfrontend and you want to remove a database called flexive all databases that begin with the term flexive and exist in the same directory are removed as well! Like: flexiveTest, flexiveConfiguration, … a bit annoying
Aside from the pitfalls and gotcha’s mentioned above, porting was straight forward and done in less time than I expected. I had to write some (Java) stored procedures to create some needed compatibility functions when dealing with Dates and Timestamps or recoding MySQL’s handy CONCAT_WS function but no real issues there at all.
H2 is an excellent pure Java database that still has some minor rough edges but can definitely be recommended! The small footprint of about 1MB is amazing considering it contains a complete webserver and user interface. It is overall very fast – for small amounts of data it even beats MySQL, although storing blob’s take a bit too long for my liking.
Now back to updating the [fleXive] documentation on how to use H2 …
“id and version of a content instance as the primary key and rely on references to the id”
This is expected behavior, you would also get an exception on PostgreSQL, Derby, and HSQLDB (I didn’t test other databases).
drop table parent;
drop table child;
create table parent(a int, b int, primary key(a, b));
insert into parent values(1, 1);
insert into parent values(1, 2);
create table child(c int, foreign key(c) references parent(a));
H2: Unique index or primary key violation: CONSTRAINT_INDEX_8 ON
PUBLIC.PARENT(A)
PostgreSQL: ERROR: there is no unique constraint matching given keys
for referenced table “parent” 42830/0
Derby: Constraint ‘SQL081208162825440′ is invalid: there is no unique
or primary key constraint on table ‘”SA”.”PARENT”‘ that matches the
number and types of the columns in the foreign key.
HSQLDB: Primary or unique constraint required on main table
Comment by Thomas Mueller — December 10, 2008 @ 22:32
>insert into … (select … from) will throw an error
>@rownum:=@rownum+1
Those two features are now implemented and will be available in the next release (1.1.105 and newer).
> Query timeouts: statement.setQueryTimeout(15/*seconds*/); is not working
I can’t reproduce this problem. What version of H2 do you use? Maybe
an old version of H2 is in the classpath?
> Order By: … issues if used in a subquery in the main queries where clause: the query parser just wont compile the query.
I can’t reproduce the problem, but I remember there were problems so
time ago. How does the query look like exactly?
> webfrontend … remove a database called flexive all databases that begin with the term flexive and exist in the same directory are removed as well
I can’t reproduce this problem with the current version.
Comment by Thomas Mueller — December 10, 2008 @ 22:34
Hi Thomas,
thanks a lot for the time and effort you put into answering and actually already fixing these issues!
Concerning “id and version of a content instance as the primary key and rely on
references to the id”:
I was afraid that this is the expected and correct behaviour
Do you have any suggestions how to reference an id where the version does not matter using some kind of foreign key constraint?
I can not reference a specific version since it may be removed but the reference is still valid as long as *any* version exists … a bit tricky I am afraid.
>> Query timeouts: statement.setQueryTimeout(15/*seconds*/); is not working
>
> I can’t reproduce this problem. What version of H2 do you use? Maybe
> an old version of H2 is in the classpath?
I use version 1.1.103 (2008-11-07) and there is only this one version in the classpath, I double checked.
If I set timeouts to 15 (seconds, I tried 15000 as well guessing it could be milliseconds) I get this exception cause:
“Caused by: org.h2.jdbc.JdbcSQLException: Statement was canceled or the session timed out [90051-103]”
>
> > Order By: … issues if used in a subquery in the main queries where clause: the query parser just wont compile the query.
>
> I can’t reproduce the problem, but I remember there were problems so
> time ago. How does the query look like exactly?
I’m sorry I can not give you a simpler and “cleaner” query, but this one is generated from out parser and the offending order by is commented out:
SELECT
filter.rownr,filter.id,filter.ver,filter.created_by ,filter.ID prop_0_0
,filter.VER prop_0_1
,filter.ID prop_1_0
,(SELECT sub.FTEXT1024 FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=101 AND (sub.lang=1 OR sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 ) prop_2_0
,concat(filter.xpathPref,(SELECT sub.XPATHMULT FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=101 AND (sub.lang=1 OR sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 )) propX_2_1
,filter.prop_3_0
,concat(filter.xpathPref,(SELECT sub.XPATHMULT FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=105 AND (sub.lang=1 OR sub.ismldef=true) ORDER BY sub.ismldef LIMIT 1 )) propX_3_1
FROM (SELECT SET(@rownr,@rownr+1) rownr, * FROM (select filter.id,filter.ver,filter.created_by,concat(concat(concat(concat(concat(t.name,’[@pk='),filter.id),'.'),filter.ver),']‘) xpathPref ,(SELECT sub.FINT FROM FX_CONTENT_DATA sub WHERE sub.id=filter.id AND sub.ver=filter.ver AND TPROP=105 AND (sub.lang=1 OR sub.ismldef=true) /*ORDER BY sub.ismldef LIMIT 1*/ ) prop_3_0
,null
FROM FXS_SEARCHCACHE_MEMORY filter, FXS_TYPEDEF t WHERE search_id=410 AND filter.tdef=t.id AND filter.TDEF=6 ORDER BY 5 desc)) filter ORDER BY 1
>
> > webfrontend … remove a database called flexive all databases that begin with the term flexive and exist in the same directory are removed as well
>
> I can’t reproduce this problem with the current version.
With version 1.1.103 and removing “flexive” and having flexiveTest and flexiveConfiguration the command line you print in the webfrontend is
java -cp h2.jar org.h2.tools.DeleteDbFiles -dir “~” -db “flexive”
and I get this result which removes “flexive” and “flexiveTest”:
Processed: /home/mplesser/flexiveTest.lobs.db/245.t879.lob.db
Processed: /home/mplesser/flexiveTest.lobs.db/158.t879.lob.db
…..
Processed: /home/mplesser/flexiveTest.lobs.db/98.t1077.lob.db
Processed: /home/mplesser/flexive.index.db
Processed: /home/mplesser/flexive.6.log.db
Processed: /home/mplesser/flexive.data.db
Processed: /home/mplesser/flexive.lobs.db/83.t205.lob.db
…
Processed: /home/mplesser/flexive.lobs.db/120.t205.lob.db
Processed: /home/mplesser/flexive.lobs.db
Please let me know if I can further assist you in looking at these issues.
I could help you to run and setup the flexive testcases to reproduce this easily.
Cheers and thanks,
Markus
(this is a crosspost from the H2 mailinglist)
Comment by Markus Plesser — December 11, 2008 @ 08:42