dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Hi

It seems there is light at the end of tunnel regarding derby.

There were three problems with derby:
1.  the tables indicator and translation using SQL keywords
2.  the length of some varchar fields being beyond derby's max
3.  a field 'external' in the document table also being an sql keyword.

I've pretty much managed to resolve these (with varying consequences).
 Because this is a sensitive part of the application and we are close
to release I don't dare commit anything at this stage.  I've attached
a diff of what I have been trying.

1.  we can use keywords for table names, though its best avoided.
Different DBMS handle the escaping issue slightly differently but
fortunately hibernate is smart enough to have an interoperable
workaround - backticks.  In the mapping file(s) I refer to the table
names as "`indicator`" and "`translation`" and suddenly derby is
happy.

2.  derby's maximum varchar size is 32672.  There are a few points
where we call for 65535 and even 10000000 (for the htmlcode of
DataEntryForm).  This last number is pretty enormous.  Mysql has a
maximum row size of 65k so it wouldn't honour this size anyway.  The
way I see it 32k is a pretty big chunk of text and is probably
adequate.  If there is no known use cases where people are storing
documents larger than this, then I think its probably safe enough to
reduce it.

3.  this last one is a bit tricky.  Theoretically (according to
hibernate docs) the backtick should work for property as well as table
names ie. referring to  <property name="`external`"/>.  For some
reason this wasn't working for me.  Of course hibernate is smart
enough to be able to name a property different to the underlying table
column name,  So I keep the property 'external' but the underlying
field is named external_column.  Now derby is happy, but obviously
this has consequences.  As long as the app goes through the hibernate
layer then the change is transparent.Which is fine for virgin new
derby (or other) databases.  But it breaks existing databases which is
not good.  We'll need to think more about this one.  Ideally get
hibernate+derby to do what they are supposed to do with backticks or
take the plunge and rename this field throughout - including
migratiojn scripts for existing data.

Let me know what you think.  From what Knut has been saying it might
be worth a little pain to get derby support.

Cheers
Bob


2009/4/3 Lars Helge Øverland <larshelge@xxxxxxxxx>:
>
>
> I have looked at different alternatives for the embedded database in the
> lite version.
>
> - H2 is not working out so well. It seems the Hibernate H2Dialect is buggy.
> The H2 people tried to submit a patch but it didn't work so well. It's all
> explained here:
> http://opensource.atlassian.com/projects/hibernate/browse/HHH-3401 .
>
> What is weird is that all our unit tests run fine on H2 but I guess we
> haven't covered it all. I get problems e.g. on import for calculated data
> elements, which is mapped as a subclass of DataElement in Hibernate.
>
> - Derby embedded looks as a good alternative, but unfortunately crashes at
> database creation time because "indicator" and "translation" (which are
> database table names in DHIS 2) are reserved words in Derby (doh). I don't
> feel like starting to rename the tables, any other suggestions?
>
> - HSQLDB works fine with Hibernate, but the current 1.8 version does not
> support the multiple insert SQL syntax, which is used heavily in the import
> and datamart process. According to the roadmap this will be supported in the
> 1.9 version. The 1.9 Alpha 2 version was released on April 1, but it is not
> present in the maven repo and we shouldn't base ourselves on an alpha. Also,
> I don't feel like going for this one after reading this:
>
> "SQLDB has two main table types used for durable read-write data storage.
> The default MEMORY type stores all data changes to the disk in the form of a
> SQL script. During engine start up, these commands are executed and data is
> reconstructed into the memory. While this behavior is not suitable for very
> large tables, it provides highly regarded performance benefits and is easy
> to debug."
>
> Please help...
>
>
>
>