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 <larshe...@gmail.com>: > > > 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... > > > > _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp