Hi 2009/4/5 Lars Helge Øverland <larshe...@gmail.com>: > > > 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.
That's good news. BTW I forgot to attach the patch. I think there's still a few things to do around the jdbc module for derby, but that looks like mostly cut and paste stuff (unless my sources are not quite up to date). Otherwise I think we're nearly there ... Cheers Bob > Great work, will test things with derby and see how it goes here as well. > >
=== modified file 'dhis-2/dhis-i18n/dhis-i18n-translationstore-hibernate/src/main/resources/org/hisp/dhis/i18n/hibernate/Translation.hbm.xml' --- dhis-2/dhis-i18n/dhis-i18n-translationstore-hibernate/src/main/resources/org/hisp/dhis/i18n/hibernate/Translation.hbm.xml 2009-03-14 06:52:24 +0000 +++ dhis-2/dhis-i18n/dhis-i18n-translationstore-hibernate/src/main/resources/org/hisp/dhis/i18n/hibernate/Translation.hbm.xml 2009-04-04 15:44:32 +0000 @@ -3,7 +3,7 @@ "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd";> <hibernate-mapping> - <class name="org.hisp.dhis.i18n.Translation" table="translation"> + <class name="org.hisp.dhis.i18n.Translation" table="`translation`"> <cache usage="read-write"/> @@ -17,4 +17,4 @@ <property name="value" column="value" type="text" not-null="true"/> </class> -</hibernate-mapping> \ No newline at end of file +</hibernate-mapping> === modified file 'dhis-2/dhis-options/src/main/resources/org/hisp/dhis/option/setting/hibernate/SystemSetting.hbm.xml' --- dhis-2/dhis-options/src/main/resources/org/hisp/dhis/option/setting/hibernate/SystemSetting.hbm.xml 2009-03-03 16:46:36 +0000 +++ dhis-2/dhis-options/src/main/resources/org/hisp/dhis/option/setting/hibernate/SystemSetting.hbm.xml 2009-04-04 20:28:43 +0000 @@ -16,7 +16,7 @@ <column name="name" not-null="true" unique="true"/> </property> - <property name="value" type="serializable" length="65535"/> + <property name="value" type="serializable" length="32672"/> </class> -</hibernate-mapping> \ No newline at end of file +</hibernate-mapping> === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/dataset/hibernate/DataEntryForm.hbm.xml' --- dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/dataset/hibernate/DataEntryForm.hbm.xml 2009-03-03 16:46:36 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/dataset/hibernate/DataEntryForm.hbm.xml 2009-04-04 20:29:35 +0000 @@ -12,6 +12,6 @@ <many-to-one name="dataSet" class="org.hisp.dhis.dataset.DataSet" column="datasetid" foreign-key="fk_dataentryform_datasetid" unique="true"/> - <property name="htmlCode" column="htmlcode" length="10000000"/> + <property name="htmlCode" column="htmlcode" length="32672"/> </class> </hibernate-mapping> === modified file 'dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/indicator/hibernate/Indicator.hbm.xml' --- dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/indicator/hibernate/Indicator.hbm.xml 2009-03-03 16:46:36 +0000 +++ dhis-2/dhis-services/dhis-service-core/src/main/resources/org/hisp/dhis/indicator/hibernate/Indicator.hbm.xml 2009-04-04 17:14:30 +0000 @@ -4,7 +4,7 @@ "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd";> <hibernate-mapping> - <class name="org.hisp.dhis.indicator.Indicator" table="indicator"> + <class name="org.hisp.dhis.indicator.Indicator" table="`indicator`"> <cache usage="read-write"/> === modified file 'dhis-2/dhis-services/dhis-service-importexport/src/main/resources/org/hisp/dhis/importexport/hibernate/ImportObject.hbm.xml' --- dhis-2/dhis-services/dhis-service-importexport/src/main/resources/org/hisp/dhis/importexport/hibernate/ImportObject.hbm.xml 2009-03-03 16:46:36 +0000 +++ dhis-2/dhis-services/dhis-service-importexport/src/main/resources/org/hisp/dhis/importexport/hibernate/ImportObject.hbm.xml 2009-04-04 20:29:11 +0000 @@ -17,9 +17,9 @@ <property name="groupMemberType" column="groupmembertype"/> - <property name="object" column="object" type="serializable" length="65535"/> + <property name="object" column="object" type="serializable" length="32672"/> - <property name="compareObject" column="compareobject" type="serializable" length="65535"/> + <property name="compareObject" column="compareobject" type="serializable" length="32672"/> </class> === modified file 'dhis-2/dhis-services/dhis-service-reporting/src/main/resources/org/hisp/dhis/document/hibernate/Document.hbm.xml' --- dhis-2/dhis-services/dhis-service-reporting/src/main/resources/org/hisp/dhis/document/hibernate/Document.hbm.xml 2009-03-06 12:05:23 +0000 +++ dhis-2/dhis-services/dhis-service-reporting/src/main/resources/org/hisp/dhis/document/hibernate/Document.hbm.xml 2009-04-04 20:58:59 +0000 @@ -14,7 +14,7 @@ <property name="url" not-null="true" type="text"/> - <property name="external"/> + <property name="external" column="external_column"/> </class> -</hibernate-mapping> \ No newline at end of file +</hibernate-mapping> === modified file 'dhis-2/dhis-services/dhis-service-user-hibernate/src/main/resources/org/hisp/dhis/user/hibernate/UserSetting.hbm.xml' --- dhis-2/dhis-services/dhis-service-user-hibernate/src/main/resources/org/hisp/dhis/user/hibernate/UserSetting.hbm.xml 2009-03-03 16:46:36 +0000 +++ dhis-2/dhis-services/dhis-service-user-hibernate/src/main/resources/org/hisp/dhis/user/hibernate/UserSetting.hbm.xml 2009-04-04 20:31:41 +0000 @@ -13,7 +13,7 @@ <key-property name="name" column="name"/> </composite-id> - <property name="value" type="serializable" length="65535"/> + <property name="value" type="serializable" length="32672"/> </class> </hibernate-mapping>
_______________________________________________ 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
