SQL Server Modeling Services Localization Support
[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]
Enterprises are increasingly expanding globally, and the SQL Server Modeling Services must be able to localize for different language users. The System.Globalization schema provides views and functions that aid in the localization process. There are two types of localizable Modeling Services items: strings and resources. Localizable strings include display names for tools, whereas resources include localizable binary items such as icons or bitmaps. Localizable items are storable and accessible through the updatable views [System.Globalization].[Strings] and [System.Globalization].[Resources]. The actual locale identifiers are listed in the [System.Globalization].[Locales] view. This view’s Id field is of type nvarchar(85) and contains the RFC 3066 locale identifier, such as en-US.
Localization Example
To illustrate the concept, the following table shows a simple example of two rows in the [System.Globalization].[Strings] view that localize the string "Person" into English and French.
| Id | Locale | String | Folder |
|---|---|---|---|
50001000-7009-409d-912c-034c45415000 |
En |
Person |
33 |
50001000-7009-409d-912c-034c45415000 |
Fr |
Personne |
33 |
The Id column is a text field of type nvarchar(2083). In order to insure a unique identifier for the string, you can convert a GUID value into its string form as is done in this example. The same string identifier is repeated once for each localized version of the string. A unique localized string consists of a combination of the Id column, the Locale column, and the Folder column. To use this localized string, a Modeling Services table could include an nvarchar(2083) column that refers to localized strings in the [System.Globalization].[Strings] view. The table could also use the [System.Globalization].[StringId] data type, which is equivalent to nvarchar(2083). In this example, a value of 50001000-7009-409d-912c-034c45415000 would point to the localized Person string.
When querying Modeling Services tables and views with localized strings, the actual string is returned by joining the target StringId field to the Id field in the Strings view. You must also provide the desired Folder and locale in the where-clause. The following query shows this.
use Repository
go
select SO.Id, SO.Name, SO.Description,
S.String as LocalizedDescription
from [Repository.Item].[SecuredOperations] SO
inner join [System.Globalization].[Strings] S on S.Id = SO.Description
where S.Folder = [Repository.Item].[PathsFolder](N'Repository') and
S.Locale = N'en-US'
The preceding query displays a localized listing of possible security operations in the Modeling Services database, such as the read operation or the update operation. This queries the [Repository.Item].[SecuredOperations] view. It joins this view's Description column with the Id in the [System.Globalization].[Strings] view. The where-clause filters the results to strings localized in English-United States. It also only returns strings that are in the Repository Folder. The [System.Globalization].[Strings] view contains the localized string names in its String field.
By specifying en-US locale, the preceding simple query assumes that there are localized strings specifically for English-United States. In some situations, there might be a common English string that is associated with the language-neutral locale, en. Most queries need to default to the language-neutral version of the localized string if the more specific locale were not mapped to the desired string identifier. This introduces added complexity to queries that directly join with the [System.Globalization].[Strings] view. Note that this complexity applies to localized resources in the [System.Globalization].[Resources] view as well.
Localization Functions
Modeling Services provides several functions to reduce the complexity of working with localized strings and resources. The following table describes the functions in the System.Globalization schema.
| Function | Description |
|---|---|
Returns the localized resource for the given locale identifier and Folder identifier. |
|
Returns the localized string for the given locale identifier and Folder identifier. |
|
Returns the Locale identifier, such as |
|
Returns the localized resource for the current database session given the Folder identifier. |
|
Returns the localized string for the current database session given the Folder identifier. |
Note
The SET_LANGUAGE command can be used to change the current session’s language. This would change the strings returned from the session-based localization functions above, assuming that there were localized versions of the strings for the chosen language.
Consider the previous query on the [Repository.Item].[SecuredOperations] view. The following query uses the [System.Globalization].[LocalesString] function to simplify the previous query.
use Repository
go
select SO.Id, SO.Name, SO.Description,
[System.Globalization].[LocalesString]
(SO.Description, [Repository.Item].[PathsFolder](N'Repository'), 'en-US')
from [Repository.Item].[SecuredOperations] SO
In this example, the [System.Globalization].[LocalesString] function returns a localized string given the following three parameters:
A string identifier,
SO.Description.A Folder identifier to the Repository Folder returned from the [Repository.Item].[PathsFolder] function.
A locale identifier,
en-US.
In addition to eliminating the need to join with the [System.Globalization].[Strings] view directly, the function also assists in finding the best possible localized match. If the function cannot find a matching string with the given locale, en-US, it will attempt to find a matching string with the language-neutral locale en.
The session-based functions in the System.Globalization schema further simplify returning localized strings within the context of a given database session. The [System.Globalization].[SessionsString] function returns a localized string for the locale associated with the current database session. The [System.Globalization].[SessionsResource] performs the same function for localized resources. For an English user, the following query returns a list of secured operations that are localized in English.
use Repository
go
select SO.Id, SO.Name, SO.Description,
[System.Globalization].[SessionsString] (SO.Description, [Repository.Item].[PathsFolder](N'Repository'))
from [Repository.Item].[SecuredOperations] SO
The locale identifier is not passed to the [System.Globalization].[SessionsString] function; it is determined by the language identifier of the current database session.
See Also
Concepts
SQL Server Modeling Services Features
Localization Design Pattern
Localization Tasks (Modeling Services)