"Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

Ideally, you would sort based on the value returned by getName() -- i.e., best match for Context.getLocale().  That may be difficult to do quickly, though.

-Burke

On May 27, 2010, at 11:57 AM, Lu Zhuangwei wrote:

Hi, Burke

Thanks for you reply. Yep, you are right, perform the sorting on the results within Java, looks like feasible, if we aren't very concern on the search speed here.

Hmm, I mainly don't how to deal with such a case:

Now, I call EncounterService.findEncounterTypes("Favorite") and suppose in database there are two following records:

encounterType1.name = "Favorite Style"; //has not been localized

encounterType2.name = "i18n:v1;unlocalized:Favorite Color;en_UK:Favourite Colour;fr:Couleur préférée;"; //has been localized

So, EncounterService.findEncounterTypes("Favorite") will return a list contains both encounterType1 and encounterType2. But I don't know we should compare which part of name of these two encounterTypes to do order by? Is it to compare "Favorite Style"(value of name for unlocalized record) with "Favorite Color"(unlocalized value of name for localized record) for order by?

If so, then after I changed encounterType2.name to be "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur préférée;", should we compare "Favorite Style" with "Favourite Colour" to do order by (that is in "en_UK") ?

So, I think there is a little complicated for order by name column.

Thanks,

Lu

2010/5/27 Burke Mamlin <[hidden email]>

Lu,

Aside from having a database-level function to parse out the locale-specific name from a localized string in the database -- which we've talked about making as a convenience for people working in SQL, but have avoided requiring for the API to avoid database dependency -- I'm not sure how you could reliably sort by name across localized values.  You might be able to use a CASE statement to return either the value (when not localized) or the unlocalized entry for values starting with "i18n:v1", but parsing out a locale-specific value could be tough.

The alternative is to perform the sorting on the results within Java.

-Burke

On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:

Hi, All

I find that in method "EncounterService.findEncounterTypes(String name)", it will do a fuzzy query by "name" column and return a list of EncounterType. So in this method, it has called such a statement "criteria.addOrder(Order.asc("name"))".

But, now in my GSoC project "Localization Tools", I will add localization support for "name" column in db table "encounter_type". Once complete this localization support, I think values in "name" column will have two use cases: (1) for those EncounterTypes hasn't used localization tools, the value will remain as before, such as "Favourite Color"; (2) for those EncounterTypes has used localization tools, the value will be like such a form "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".

So I am not sure whether "order by name asc" is still acceptable? Because, for all EncounterTypes which has used localization tools, the values of their "name" column in db always begin with "i18n:v1;"

If not acceptable, how to order by name column now? Any good suggestion? I also think maybe "order by name column" is not very important here?

Thanks,

Lu

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

I understand that databases are designed to have well optimized and fast sorting algorithms,  so doing an ‘order by’ in your query turns out to be always faster, and then have the results put into a list from the result set to maintain the ordering, and this explains why hibernate always returns lists as opposed to other data structures that donot maintain  ordering.

 

Wyclif

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Lu Zhuangwei
Sent: Thursday, May 27, 2010 11:58
To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

Hi, Burke

 

Thanks for you reply. Yep, you are right, perform the sorting on the results within Java, looks like feasible, if we aren't very concern on the search speed here.

 

Hmm, I mainly don't how to deal with such a case:

 

Now, I call EncounterService.findEncounterTypes("Favorite") and suppose in database there are two following records:

 

encounterType1.name = "Favorite Style"; //has not been localized

encounterType2.name = "i18n:v1;unlocalized:Favorite Color;en_UK:Favourite Colour;fr:Couleur préférée;"; //has been localized

 

So, EncounterService.findEncounterTypes("Favorite") will return a list contains both encounterType1 and encounterType2. But I don't know we should compare which part of name of these two encounterTypes to do order by? Is it to compare "Favorite Style"(value of name for unlocalized record) with "Favorite Color"(unlocalized value of name for localized record) for order by?

 

If so, then after I changed encounterType2.name to be "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur préférée;", should we compare "Favorite Style" with "Favourite Colour" to do order by (that is in "en_UK") ?

 

So, I think there is a little complicated for order by name column.

 

 

Thanks,

Lu

 

2010/5/27 Burke Mamlin <[hidden email]>

Lu,

 

Aside from having a database-level function to parse out the locale-specific name from a localized string in the database -- which we've talked about making as a convenience for people working in SQL, but have avoided requiring for the API to avoid database dependency -- I'm not sure how you could reliably sort by name across localized values.  You might be able to use a CASE statement to return either the value (when not localized) or the unlocalized entry for values starting with "i18n:v1", but parsing out a locale-specific value could be tough.

 

The alternative is to perform the sorting on the results within Java.

 

-Burke

 

On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:

Hi, All

 

I find that in method "EncounterService.findEncounterTypes(String name)", it will do a fuzzy query by "name" column and return a list of EncounterType. So in this method, it has called such a statement "criteria.addOrder(Order.asc("name"))".

 

But, now in my GSoC project "Localization Tools", I will add localization support for "name" column in db table "encounter_type". Once complete this localization support, I think values in "name" column will have two use cases: (1) for those EncounterTypes hasn't used localization tools, the value will remain as before, such as "Favourite Color"; (2) for those EncounterTypes has used localization tools, the value will be like such a form "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".

 

So I am not sure whether "order by name asc" is still acceptable? Because, for all EncounterTypes which has used localization tools, the values of their "name" column in db always begin with "i18n:v1;"

 

If not acceptable, how to order by name column now? Any good suggestion? I also think maybe "order by name column" is not very important here?

 

 

Thanks,

Lu

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

The LocalizedString datatype is by definition unnormalized and carries all the burdens of storing multiple values in a single field.  Perhaps it makes sense not to use it, but to add a new table EncounterTypeLocalization (encountertypeid, locale, name) so that the old EncounterType becomes the default and we can select and sort normally.  We can expect <200 EncounterTypes and <10 locales per EncounterType, it's not a big burden.  Not elegant but formally correct (@ Tammy).

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Wyclif Luyima
Sent: Thursday, May 27, 2010 2:40 PM

To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

I understand that databases are designed to have well optimized and fast sorting algorithms,  so doing an ‘order by’ in your query turns out to be always faster, and then have the results put into a list from the result set to maintain the ordering, and this explains why hibernate always returns lists as opposed to other data structures that donot maintain  ordering.

 

Wyclif

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Lu Zhuangwei
Sent: Thursday, May 27, 2010 11:58
To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

Hi, Burke

 

Thanks for you reply. Yep, you are right, perform the sorting on the results within Java, looks like feasible, if we aren't very concern on the search speed here.

 

Hmm, I mainly don't how to deal with such a case:

 

Now, I call EncounterService.findEncounterTypes("Favorite") and suppose in database there are two following records:

 

encounterType1.name = "Favorite Style"; //has not been localized

encounterType2.name = "i18n:v1;unlocalized:Favorite Color;en_UK:Favourite Colour;fr:Couleur préférée;"; //has been localized

 

So, EncounterService.findEncounterTypes("Favorite") will return a list contains both encounterType1 and encounterType2. But I don't know we should compare which part of name of these two encounterTypes to do order by? Is it to compare "Favorite Style"(value of name for unlocalized record) with "Favorite Color"(unlocalized value of name for localized record) for order by?

 

If so, then after I changed encounterType2.name to be "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur préférée;", should we compare "Favorite Style" with "Favourite Colour" to do order by (that is in "en_UK") ?

 

So, I think there is a little complicated for order by name column.

 

 

Thanks,

Lu

 

2010/5/27 Burke Mamlin <[hidden email]>

Lu,

 

Aside from having a database-level function to parse out the locale-specific name from a localized string in the database -- which we've talked about making as a convenience for people working in SQL, but have avoided requiring for the API to avoid database dependency -- I'm not sure how you could reliably sort by name across localized values.  You might be able to use a CASE statement to return either the value (when not localized) or the unlocalized entry for values starting with "i18n:v1", but parsing out a locale-specific value could be tough.

 

The alternative is to perform the sorting on the results within Java.

 

-Burke

 

On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:

 

Hi, All

 

I find that in method "EncounterService.findEncounterTypes(String name)", it will do a fuzzy query by "name" column and return a list of EncounterType. So in this method, it has called such a statement "criteria.addOrder(Order.asc("name"))".

 

But, now in my GSoC project "Localization Tools", I will add localization support for "name" column in db table "encounter_type". Once complete this localization support, I think values in "name" column will have two use cases: (1) for those EncounterTypes hasn't used localization tools, the value will remain as before, such as "Favourite Color"; (2) for those EncounterTypes has used localization tools, the value will be like such a form "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".

 

So I am not sure whether "order by name asc" is still acceptable? Because, for all EncounterTypes which has used localization tools, the values of their "name" column in db always begin with "i18n:v1;"

 

If not acceptable, how to order by name column now? Any good suggestion? I also think maybe "order by name column" is not very important here?

 

 

Thanks,

Lu

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

If we have <200 encounter types, then doing the proper name sorting in Java (as Burke says, by getName()) will not be noticeably slow.

-Darius

2010/5/27 Friedman, Roger (CDC/OID/NCHHSTP) (CTR) <[hidden email]>

The LocalizedString datatype is by definition unnormalized and carries all the burdens of storing multiple values in a single field.  Perhaps it makes sense not to use it, but to add a new table EncounterTypeLocalization (encountertypeid, locale, name) so that the old EncounterType becomes the default and we can select and sort normally.  We can expect <200 EncounterTypes and <10 locales per EncounterType, it's not a big burden.  Not elegant but formally correct (@ Tammy).

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Wyclif Luyima
Sent: Thursday, May 27, 2010 2:40 PM

To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

I understand that databases are designed to have well optimized and fast sorting algorithms,  so doing an ‘order by’ in your query turns out to be always faster, and then have the results put into a list from the result set to maintain the ordering, and this explains why hibernate always returns lists as opposed to other data structures that donot maintain  ordering.

 

Wyclif

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Lu Zhuangwei
Sent: Thursday, May 27, 2010 11:58
To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

Hi, Burke

 

Thanks for you reply. Yep, you are right, perform the sorting on the results within Java, looks like feasible, if we aren't very concern on the search speed here.

 

Hmm, I mainly don't how to deal with such a case:

 

Now, I call EncounterService.findEncounterTypes("Favorite") and suppose in database there are two following records:

 

encounterType1.name = "Favorite Style"; //has not been localized

encounterType2.name = "i18n:v1;unlocalized:Favorite Color;en_UK:Favourite Colour;fr:Couleur préférée;"; //has been localized

 

So, EncounterService.findEncounterTypes("Favorite") will return a list contains both encounterType1 and encounterType2. But I don't know we should compare which part of name of these two encounterTypes to do order by? Is it to compare "Favorite Style"(value of name for unlocalized record) with "Favorite Color"(unlocalized value of name for localized record) for order by?

 

If so, then after I changed encounterType2.name to be "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur préférée;", should we compare "Favorite Style" with "Favourite Colour" to do order by (that is in "en_UK") ?

 

So, I think there is a little complicated for order by name column.

 

 

Thanks,

Lu

 

2010/5/27 Burke Mamlin <[hidden email]>

Lu,

 

Aside from having a database-level function to parse out the locale-specific name from a localized string in the database -- which we've talked about making as a convenience for people working in SQL, but have avoided requiring for the API to avoid database dependency -- I'm not sure how you could reliably sort by name across localized values.  You might be able to use a CASE statement to return either the value (when not localized) or the unlocalized entry for values starting with "i18n:v1", but parsing out a locale-specific value could be tough.

 

The alternative is to perform the sorting on the results within Java.

 

-Burke

 

On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:

 

Hi, All

 

I find that in method "EncounterService.findEncounterTypes(String name)", it will do a fuzzy query by "name" column and return a list of EncounterType. So in this method, it has called such a statement "criteria.addOrder(Order.asc("name"))".

 

But, now in my GSoC project "Localization Tools", I will add localization support for "name" column in db table "encounter_type". Once complete this localization support, I think values in "name" column will have two use cases: (1) for those EncounterTypes hasn't used localization tools, the value will remain as before, such as "Favourite Color"; (2) for those EncounterTypes has used localization tools, the value will be like such a form "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".

 

So I am not sure whether "order by name asc" is still acceptable? Because, for all EncounterTypes which has used localization tools, the values of their "name" column in db always begin with "i18n:v1;"

 

If not acceptable, how to order by name column now? Any good suggestion? I also think maybe "order by name column" is not very important here?

 

 

Thanks,

Lu

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

Agreed, its probably sub 1000 items and you won't see any significant
performance hit.  I say this because half of the cohort builder queries
do logic in java and they only really slow down when you get up into the
thousands of patients coming back.

I suggest leaving the sort in the database as is for all data that is
not localized (like it is now) and you can do java sorting if anything
is localized.

Ben

On 05/27/2010 06:58 PM, Darius Jazayeri wrote:
> If we have <200 encounter types, then doing the proper name sorting in
> Java (as Burke says, by getName()) will not be noticeably slow.
>
> -Darius
>
> 2010/5/27 Friedman, Roger (CDC/OID/NCHHSTP) (CTR) <[hidden email]

> <mailto:[hidden email]>>

>
>     The LocalizedString datatype is by definition unnormalized and
>     carries all the burdens of storing multiple values in a single
>     field. Perhaps it makes sense not to use it, but to add a new table
>     EncounterTypeLocalization (encountertypeid, locale, name) so that
>     the old EncounterType becomes the default and we can select and sort
>     normally. We can expect <200 EncounterTypes and <10 locales per
>     EncounterType, it's not a big burden. Not elegant but formally
>     correct (@ Tammy).
>
>     *From:* [hidden email] <mailto:[hidden email]>

>     [mailto:[hidden email] <mailto:[hidden email]>] *On Behalf Of

>     *Wyclif Luyima
>     *Sent:* Thursday, May 27, 2010 2:40 PM
>
>
>     *To:* [hidden email]

>     <mailto:[hidden email]>

>     *Subject:* Re: [OPENMRS-DEV] "Localization Tools" project : How to
>     order by "name" column of db table "encounter_type" in sql query
>
>     I understand that databases are designed to have well optimized and
>     fast sorting algorithms, so doing an ‘order by’ in your query turns
>     out to be always faster, and then have the results put into a list
>     from the result set to maintain the ordering, and this explains why
>     hibernate always returns lists as opposed to other data structures
>     that donot maintain ordering.
>
>     Wyclif
>
>     *From:* [hidden email] <mailto:[hidden email]>

>     [mailto:[hidden email] <mailto:[hidden email]>] *On Behalf Of *Lu

>     Zhuangwei
>     *Sent:* Thursday, May 27, 2010 11:58
>     *To:* [hidden email]

>     <mailto:[hidden email]>

>     *Subject:* Re: [OPENMRS-DEV] "Localization Tools" project : How to
>     order by "name" column of db table "encounter_type" in sql query
>
>     Hi, Burke
>
>     Thanks for you reply. Yep, you are right, perform the sorting on the
>     results within Java, looks like feasible, if we aren't very concern
>     on the search speed here.
>
>     Hmm, I mainly don't how to deal with such a case:
>
>     Now, I call EncounterService.findEncounterTypes("Favorite") and
>     suppose in database there are two following records:
>
>     encounterType1.name = "Favorite Style"; *//has not been localized*
>
>     encounterType2.name = "i18n:v1;unlocalized:Favorite
>     Color;en_UK:Favourite Colour;fr:Couleur préférée;"; *//has been
>     localized*
>
>     So, EncounterService.findEncounterTypes("Favorite") will return a
>     list contains both encounterType1 and encounterType2. But I don't
>     know we should compare which part of name of these two
>     encounterTypes to do order by? Is it to compare "Favorite
>     Style"(value of name for unlocalized record) with "Favorite
>     Color"(unlocalized value of name for localized record) for order by?
>
>     If so, then after I changed encounterType2.name to be
>     "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur
>     préférée;", should we compare "Favorite Style" with "Favourite
>     Colour" to do order by (that is in "en_UK") ?
>
>     So, I think there is a little complicated for order by name column.
>
>     Thanks,
>
>     Lu
>
>     2010/5/27 Burke Mamlin <[hidden email]

>     <mailto:[hidden email]>>

>
>     Lu,
>
>     Aside from having a database-level function to parse out the
>     locale-specific name from a localized string in the database --
>     which we've talked about making as a convenience for people working
>     in SQL, but have avoided requiring for the API to avoid database
>     dependency -- I'm not sure how you could reliably sort by name
>     across localized values. You might be able to use a CASE statement
>     to return either the value (when not localized) or the unlocalized
>     entry for values starting with "i18n:v1", but parsing out a
>     locale-specific value could be tough.
>
>     The alternative is to perform the sorting on the results within Java.
>
>     -Burke
>
>     On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:
>
>     Hi, All
>
>     I find that in method "EncounterService.findEncounterTypes(String
>     name)", it will do a fuzzy query by "name" column and return a list
>     of EncounterType. So in this method, it has called such a statement
>     "criteria.addOrder(Order.asc("name"))".
>
>     But, now in my GSoC project "Localization Tools

>     <http://openmrs.org/wiki/Localization_Tools>", I will add

>     localization support for "name" column in db table "encounter_type".
>     Once complete this localization support, I think values in "name"
>     column will have two use cases: (1) for those EncounterTypes hasn't
>     used localization tools, the value will remain as before, such as
>     "Favourite Color"; (2) for those EncounterTypes has used
>     localization tools, the value will be like such a form
>     "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".
>
>     So I am not sure whether "order by name asc" is still acceptable?
>     Because, for all EncounterTypes which has used localization tools,
>     the values of their "name" column in db always begin with "i18n:v1;"
>
>     If not acceptable, how to order by name column now? Any good
>     suggestion? I also think maybe "order by name column" is not very
>     important here?
>
>     Thanks,
>
>     Lu
>
>
>
>     --
>     Zhuangwei Lu(陆庄伟)
>     Mobile: +86 151-0103-7842

>     MSN: [hidden email] <mailto:[hidden email]>

>     Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)
>
>     ------------------------------------------------------------------------
>
>     Click here to unsubscribe

>     <mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l>

>     from OpenMRS Developers' mailing list
>
>
>
>
>     --
>     Zhuangwei Lu(陆庄伟)
>     Mobile: +86 151-0103-7842

>     MSN: [hidden email] <mailto:[hidden email]>

>     Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)
>
>     ------------------------------------------------------------------------
>
>     Click here to unsubscribe

>     <mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l>

>     from OpenMRS Developers' mailing list
>
>     ------------------------------------------------------------------------
>
>     Click here to unsubscribe

>     <mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l>

>     from OpenMRS Developers' mailing list
>
>     ------------------------------------------------------------------------
>     Click here to unsubscribe

>     <mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l>

>     from OpenMRS Developers' mailing list
>
>
> ------------------------------------------------------------------------
> Click here to unsubscribe

> <mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l> from
> OpenMRS Developers' mailing list

_________________________________________

To unsubscribe from OpenMRS Developers' mailing list, send an e-mail to [hidden email] with "SIGNOFF openmrs-devel-l" in the  body (not the subject) of your e-mail.

[mailto:[hidden email]?body=SIGNOFF%20openmrs-devel-l]

Hi, Lu

 

If you have already discussed this out, then by all means carry on.

 

However, this is at least the second time I have heard arguments like "can avoid adding many new tables like XXXLocalization".  What is so bad about that?  True, had we thought about translating encountertype in the original design, we might have come up with a more elegant solution that covered all the translation use cases, but we didn't.  So why aren't we going with a standard, normalized approach here?  Surely keeping the data model diagram tidy is not a sufficient reason.

 

I think many programmers think about things as an object model, they like to see the relationship between their object and problem domain, they like to abstract rather than to enumerate.  But I don't know how far you can go with this methodology when you are dealing with a computational, non-problem-domain issue like internationalization or security.  In this case, we are dealing with normalization, database managers are highly optimized to deal with normalized tables for good and sufficient reasons, we know that the problem has been well-considered and has a theoretical underpinning, whereas with our roll-your-own solution, we make it impossible to use our database directly with SQL and open ourselves up to unanticipated use cases not realizable within it (although I must say you are doing an excellent job of considering all aspects of the problem).

 

Good luck, Roger 

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Lu Zhuangwei
Sent: Thursday, May 27, 2010 11:11 PM

To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

Hi, Roger

 

Thanks for your good suggestion. Hmm, just in my rough understand, I think storing multiple values in a single field can avoid adding many new tables like XXXLocalization for each Metadata Class(e.g., EncounterType.class). In "Localization Tools" project, its final aim is to support localization for most Openmrs Metadata classes, not limit to only EncounterType. Such as OrderType, ConceptClass, etc.

 

In fact, in our dev team, we have talked a lot about how to design for this project, and I think current solution(storing multiple values in a single field) is the final one.

 

FYI: There are some links to the call notes about proposed designs of this project, if you want to know, you can see them in here:http://openmrs.org/wiki/Localization_Tools#Links

 

 

 

Thanks,

Lu

 

2010/5/28 Friedman, Roger (CDC/OID/NCHHSTP) (CTR) <[hidden email]>

The LocalizedString datatype is by definition unnormalized and carries all the burdens of storing multiple values in a single field.  Perhaps it makes sense not to use it, but to add a new table EncounterTypeLocalization (encountertypeid, locale, name) so that the old EncounterType becomes the default and we can select and sort normally.  We can expect <200 EncounterTypes and <10 locales per EncounterType, it's not a big burden.  Not elegant but formally correct (@ Tammy).

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Wyclif Luyima
Sent: Thursday, May 27, 2010 2:40 PM

To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

I understand that databases are designed to have well optimized and fast sorting algorithms,  so doing an ‘order by’ in your query turns out to be always faster, and then have the results put into a list from the result set to maintain the ordering, and this explains why hibernate always returns lists as opposed to other data structures that donot maintain  ordering.

 

Wyclif

 

From: [hidden email] [mailto:[hidden email]] On Behalf Of Lu Zhuangwei
Sent: Thursday, May 27, 2010 11:58
To: [hidden email]
Subject: Re: [OPENMRS-DEV] "Localization Tools" project : How to order by "name" column of db table "encounter_type" in sql query

 

Hi, Burke

 

Thanks for you reply. Yep, you are right, perform the sorting on the results within Java, looks like feasible, if we aren't very concern on the search speed here.

 

Hmm, I mainly don't how to deal with such a case:

 

Now, I call EncounterService.findEncounterTypes("Favorite") and suppose in database there are two following records:

 

encounterType1.name = "Favorite Style"; //has not been localized

encounterType2.name = "i18n:v1;unlocalized:Favorite Color;en_UK:Favourite Colour;fr:Couleur préférée;"; //has been localized

 

So, EncounterService.findEncounterTypes("Favorite") will return a list contains both encounterType1 and encounterType2. But I don't know we should compare which part of name of these two encounterTypes to do order by? Is it to compare "Favorite Style"(value of name for unlocalized record) with "Favorite Color"(unlocalized value of name for localized record) for order by?

 

If so, then after I changed encounterType2.name to be "i18n:v1;unlocalized:Color;en_UK:Favourite Colour;fr:Couleur préférée;", should we compare "Favorite Style" with "Favourite Colour" to do order by (that is in "en_UK") ?

 

So, I think there is a little complicated for order by name column.

 

 

Thanks,

Lu

 

2010/5/27 Burke Mamlin <[hidden email]>

Lu,

 

Aside from having a database-level function to parse out the locale-specific name from a localized string in the database -- which we've talked about making as a convenience for people working in SQL, but have avoided requiring for the API to avoid database dependency -- I'm not sure how you could reliably sort by name across localized values.  You might be able to use a CASE statement to return either the value (when not localized) or the unlocalized entry for values starting with "i18n:v1", but parsing out a locale-specific value could be tough.

 

The alternative is to perform the sorting on the results within Java.

 

-Burke

 

On May 27, 2010, at 1:34 AM, Lu Zhuangwei wrote:

 

Hi, All

 

I find that in method "EncounterService.findEncounterTypes(String name)", it will do a fuzzy query by "name" column and return a list of EncounterType. So in this method, it has called such a statement "criteria.addOrder(Order.asc("name"))".

 

But, now in my GSoC project "Localization Tools", I will add localization support for "name" column in db table "encounter_type". Once complete this localization support, I think values in "name" column will have two use cases: (1) for those EncounterTypes hasn't used localization tools, the value will remain as before, such as "Favourite Color"; (2) for those EncounterTypes has used localization tools, the value will be like such a form "i18n:v1;unlocalized:Hello;es:Hola;sw:Jamboo;".

 

So I am not sure whether "order by name asc" is still acceptable? Because, for all EncounterTypes which has used localization tools, the values of their "name" column in db always begin with "i18n:v1;"

 

If not acceptable, how to order by name column now? Any good suggestion? I also think maybe "order by name column" is not very important here?

 

 

Thanks,

Lu

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list

--
Zhuangwei Lu(陆庄伟)
Mobile: +86 151-0103-7842
MSN: [hidden email]
Institute Of Software Chinese Academy Of Sciences(中国科学院软件研究所)

---

[hidden email] from OpenMRS Developers' mailing list

---

[hidden email] from OpenMRS Developers' mailing list