dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

2009/4/5 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.


This is great news.


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

This was actually a workaround in order to make hibernate create BLOB fields
both MySQL and Postgres. Hibernate has poor support for BLOBs. Anyway I will
have a look and see if this can be done in a more elegant way, maybe this
has been improved in later Hibernate versions as well.


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


In this case simply go for the column rename. This document thing has never
been part of a release and is something I created recently for the Sierra
Leone demo. It's only in use there as far as I know and I will manually
update it.

Great work, will test things with derby and see how it goes here as well.