dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Hi

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

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>