2009/4/5 Bob Jolliffe <bobjolli...@gmail.com> > 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.
_______________________________________________ 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