Localized Data

Localized data means maintaining different translations of textual data and automatically fetching the translations matching the users’ preferred language with per-row fallback to default languages, if the required translations aren’t available. Language codes are in ISO 639-1 format.

Find a working sample at https://github.com/SAP-samples/cloud-cap-samples/tree/master/packages/bookshop.

Content

• Declaring Localized Data
• Behind the Scenes
  • Base Entities Stay Intact
  • Optimization for SAP HANA
• Pseudo var $user.locale
  • Determining $user.locale from Inbound Requests
  • Programmatic Access to $user.locale
  • Propagating $user.locale to Databases
• Reading Localized Data
  • In Agnostic Code
  • For End Users
  • For Translation UIs
• Serving Localized Data
  • localized. Helper Views
  • Read Operations
  • Write Operations
  • Update Operations
  • Delete Operations
• Nested Localized Data

Declaring Localized Data

Use the localized modifier to mark entity elements that require translated texts.

entity Books {
  key ID       : UUID;
      title    : localized String;
      descr    : localized String;
      price    : Decimal;
      currency : Currency;
}

Behind the Scenes

The cds compiler automatically unfolds the above definition as follows:

First, a separate _texts entity is added to hold translated texts:

define entity Books_texts @(cds.autoexpose) {
  key locale : String(5);
  key ID : UUID; //= source's primary key
   title : String;
   descr : String;
}

Second, the source entity is extended with associations to _texts:

extend entity Books with {
  texts : Composition of many Books_texts on texts.ID=ID;
  localized : Association to Books_texts on localized.ID=ID
    and localized.locale = $user.locale;
}

Third, views are added to easily read localized texts with fallback:

define entity localized.Books as SELECT from Books {*,
  coalesce (localized.title, title) as title,
  coalesce (localized.descr, descr) as descr
};

Please note, that SQLite doesn’t support locale like SAP HANA does. For SQLite we need to generate additional views for different languages. Currently we generate those views for the locales ‘de’ and ‘fr’ and the default locale is handled as ‘en’.

Hint for development: The Books entity should contain the english text and the Books_texts should contain the ‘de’ and ‘fr’ texts. This applies only when testing with SQLite. This means that localized.Books contains english texts, localized.de.Books contains german texts, localized.fr.Books contains french texts.

Base Entities Stay Intact

In contrast to similar strategies, all texts aren’t externalized but the original texts are kept in the source entity. This saves one join when reading localized texts with fallback to the original ones.

Optimization for SAP HANA

The actual implementation of localized.Books varies. For example, unions are used instead of coalesce because SAP HANA doesn’t support fuzzy search on calculated fields:

define view localized.Books as (
  SELECT from Books {*, localized.{ title, descr }}
   where localized.locale is not null
  UNION ALL SELECT from Books {*, title, descr} where ID not in (
     SELECT from Books_texts {ID} where locale = $user.locale
   )
);

Pseudo var $user.locale

As shown in the second step, the pseudo variable $user.locale is used to refer to the user’s preferred locale and join matching translations from _texts tables. This pseudo variable allows expressing such queries in a database-independent way, which is realized in the service runtimes as follows:

Determining $user.locale from Inbound Requests

The user’s preferred locale is determined from request parameters, user settings, or the accept-language header of inbound requests as explained in the Localization guide.

Programmatic Access to $user.locale

The resulting normalized locale is available programmatically, in your event handlers.

• Node.js: req.user.locale
• Java: context.getParameterInfo().getLocale()

Propagating $user.locale to Databases

Finally, the normalized locale is propagated to underlying databases using session variables, that is, $user.locale translates to session_context('locale') in native SQL of SAP HANA and most databases.

Not all databases support session variables. For example, for sqlite we currently would just create stand-in views for selected languages. With that, the APIs are kept stable but have restricted feature support.

Reading Localized Data

Given the asserted unfolding and user locales propagated to the database, you can read localized data as follows:

In Agnostic Code

Read original texts, that is, the ones in the originally created data entry:

SELECT ID, title, descr from Books

For End Users

Reading texts for end users uses the localized association, which requires prior propagation of $user.locale to the underlying database.

Read localized texts in the user’s preferred language:

SELECT ID, localized.{ title, descr } from Books

Read localized texts with fallback to the texts in originally inserted language:

SELECT ID, title, descr from localized.Books

For Translation UIs

Translation UIs would read and write texts in all languages, independent from the current user’s preferred one. They use the to-many texts association, which is independent from $user.locale.

Read texts in different translations:

SELECT ID, texts[locale='fr'].{ title, descr } from Books

Read texts in all translations:

SELECT ID, texts { locale, title, descr } from Books

Serving Localized Data

The generic handlers of the service runtimes automatically serve read requests from localized views. Users see all texts in their preferred language or the fallback language.

For example, given this service definition:

using { Books } from './books';
service CatalogService {
  entity BooksList as projection on Books { ID, title, price };
  entity BooksDetails as projection on Books;
}

localized. Helper Views

For each exposed entity in a service definition, and all intermediate views, a corresponding localized. entity is created. It has the same query clauses and all annotations, except for the from clause being redirected to the underlying entity’s localized. counterpart.

using { localized.Books } from './books_localized';

entity localized.CatalogService.BooksList as
SELECT from localized.Books { ID, title, price };

entity localized.CatalogService.BooksDetails as
SELECT from localized.Books;

Note that these localized. entities aren’t exposed through OData, though.

Read Operations

The generic handlers in the service framework will automatically redirect all incoming read requests to the localized. helper views, unless in SAP Fiori Draft mode. A corresponding implementation in Node.js looks like the following:

// Interecpt all read requests to localized entities
module.exports = (srv)=>{
  const entities = srv.entities
  for (let each in entities)
    if (_is_localized(entities[each]))
      srv.before ('READ', each, _read_from_localized_entity)
}

// Before handler to redirect read requests to localized. views ...
function _read_from_localized_entity (req) {
  if (!_is_draft(req)) { //... unless in a Fiori draft operation
    const {SELECT} = req.query, entity = req.target.name
    SELECT.from.ref[0] = `localized.${entity}`
  }
}

// Localized entities have an association named 'localized'
const _is_localized = (entity) => 'localized' in entity.elements

Write Operations

Since the corresponding text table is linked through composition, you can use deep inserts or upserts to fill in language-specific texts.

POST <your_service_url>/Entity
Content-Type: application/json

{
  "name": "Some name",
  "description": "Some description",
  "texts": [ {"name": "Ein Name", "description": "Eine Beschreibung", "locale": "de"} ]
}

If you wish to add a language-specific text to an existing entity, perform a POST request to the text table of the entity through navigation.

POST <your_service_url>/Entity(<entity_key>)/texts
Content-Type: application/json

{
  {"name": "Ein Name", "description": "Eine Beschreibung", "locale": "de"}
}

Update Operations

To update the language-specific texts of an entity along with the default fallback text, you can perform a deep update as a PUT or PATCH request to the entity through navigation.

PUT/PATCH <your_service_url>/Entity(<entity_key>)
Content-Type: application/json

{
  "name": "Some new name",
  "description": "Some new description",
  "texts": [ {"name": "Ein neuer Name", "description": "Eine neue Beschreibung", "locale": "de"} ]
}

To update a single language-specific text field, perform a PUT or a PATCH request to the entity’s text field via navigation.

PUT/PATCH <your_service_url>/Entity(<entity_key>)/texts(ID=<entity_key>,locale='<locale>')/<field_name>
Content-Type: application/json

{
  {"name": "Ein neuer Name"} ]
}

Delete Operations

To delete a locale’s language-specific texts of an entity, perform a DELETE request to the entity’s texts table through navigation. Specify the entity’s key and the locale you wish to delete.

DELETE <your_service_url>/Entity(<entity_key>)/texts(ID=<entity_key>,locale='<locale>')

Nested Localized Data

The definition of books has an element currency, which is effectively an association to code list entity sap.common.Currencies, which in turn has localized texts. Find the respective definitions in the reference docs for @sap/cds/common, in the section on Common Code Lists.

Upon unfolding, all associations to other entities with localized texts are automatically redirected as follows:

entity localized.Currencies as SELECT from Currencies c {* /*...*/};
entity localized.Books as SELECT from Books p mixin {
  // assocuation is redirected to localized.Currencies
  country : Association to localized.Currencies on country = p.country;
} into {* /*...*/};

Given that, nested localized data can be easily read with independent fallback logic:

SELECT from localized.Books {
  ID, title, descr,
  currency.name as currency
} where title like '%pen%' or currency.name like '%land%'

In the result sets for this query, values for title, descr as well as currency name are localized.