Find an alternative to storing localised fields as an XML in a DB column

• Type: Feature Request
• Status: Closed
• Priority: Minor
• Resolution: Duplicate
• Affects Version/s: None
• Fix Version/s: None
• Component/s: Content Display Widgets > Language widget, Environments, Environments > Databases, Portal Services > Localization, Web Content > Web Content Administration
• Labels:
  • SME-should-7.2

Currently several of Liferay's services are storing localized fields as XML String in a DB column. Some "famous" ones are:

• JournalArticle.title
• DDMStructure.name
• AssetEntry.description

This is causing severe issues like:

• Unable to order lists by these fields like:
  • Order articles by title or name (feature requests in LPS-52293 and LPS-54460)
  • Show web content types ordered by name when you click on add web content (see attached image order_add_web_content.png)
  • So we can't order by name, we remove the possibility to order (LPS-54461)

• Unable to perform sql queries where we need to compare by these fields. Probably for this reason we generate so many search indexes and we delegate the queries to index search instead of database search. In fact the following properties set to false are not working properly:

  asset.publisher.search.with.index=false
  journal.articles.search.with.index=false
  

• The size for these fields is usually not enough causing errors saving records which exceed the limit (see LPS-40069, LPS-53164, LPS-34209, LPS-39668, LPS-44439, LPS-54312 for example)

• This is even worse for Oracle when there is a size limit for Varchar (4000) or for DB2 where we have to define the page size in advance. So finally we have to convert these fields into CLOBs and this breaks some queries since this kind of fields can't be part of some SQL clauses like DISTINCT or GROUP (see LPS-47261 for example)

• With this kind of fields we are increasing the size of the database and search indexes since we need to store a whole XML with useless information.

Technical note
It's important to take into account for the future solution that changing localized fields from XML to JSON would be just a patch since we only save characters, not the rest of the issues.