Browse DevX
Sign up for e-mail newsletters from DevX


Translating Resources Gets Simpler with the Localization Management Toolkit  : Page 2

The global reach of many applications means organizations must manage the process of translating and keeping track of thousands of resources. The Localization Management Toolkit (currently in beta form) simplifies the process.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

The Toolkit's Resource Database
Storing resource information in a database, as opposed to using many separate .resx files, can improve the overall manageability of a localized application. It can also simplify your approach for working with translators as you can easily generate reports, update several keys at the same time, or create new cultures by running T-SQL queries.

Figure 1. Database Diagram: The figure shows the tables, columns, and relationships in the Toolkit resource database.
The database used by the Toolkit was designed to serve as a central repository for an enterprise, storing the resources and cultures of all the applications within the organization. Figure 1 shows a schema diagram. The database contains the following tables:

  • Elements: Holds the key names or resources from the key value pairs. It has a foreign key from the DataSource table indicating in which resource file this key is located.
  • DataSources: Stores the names of the resource files in an application, using a foreign key ApplicationID column to link each resource file with the application that uses it.
  • Applications: Stores the names and attributes of the applications within an organization.
  • Cultures: Acts as a lookup table storing all the culture names supported by the .NET Framework.
  • ApplicationCultures: Stores all the cultures that are supported per application in the enterprise organization.
  • CultureProperties: Stores the Values from the key.value pairs (in a PropertyValue field). There should be at least one value present in this table for the invariant culture. This table allows the translators to store comments, keep track of when the PropertyValue was created by the Toolkit or when it was last updated by a translator.
  • ElementTypes: Serves as a lookup table for element types.
  • ResxVersions: Keeps track of generated resource files per culture, application, and resource file name. It has an XMLType field that stores the particular version of the resource file acting as an archive of old versions.
Assumptions, Constraints, and Triggers
This Toolkit requires a unique ElementKey—the Key that links the resource with a particular control—for each resource file. It enforces this with a unique index on the combined ElementKey and DataSourceID fields in the Elements table. It also requires resource file names to be unique within an application domain, again, enforced with a unique non-clustered index on the columns ApplicationID and DataSourceName in the DataSources table.

Given a key value or ElementKey, there is a single PropertyValue (value) per ApplicationCulture, which is a single value or translation for that key per culture supported by the application. Explained in the context of the previous example, the key Button1.Text can have only a single value for the French- Canadian Culture in a given application. Each application/culture combination must be unique in the ApplicationCultures table, enforced using a non-clustered unique index.

When deleting an element or key, the deletion must propagate to the CultureProperties table, deleting all the entries (PropertyValues) associated with the deleted ElementKey. A delete trigger in the Elements table performs this action.

USE [Localization] GO /****** Object: Trigger [delElements] Script Date: 11/07/2006 04:41:54 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [delElements] ON [dbo].[Elements] INSTEAD OF DELETE AS BEGIN DELETE FROM CultureProperties WHERE ElementID IN (SELECT ElementID FROM Deleted) DELETE FROM Elements WHERE ElementID IN (SELECT ElementID FROM Deleted) END

Inserting an element or key (ElementKey) creates a corresponding PropertyValue per culture in the CultureProperties table with a null value. The insertion stored procedure stores the given PropertyValue in the row corresponding to the invariant culture, leaving the rest of the values (for the different cultures) with the null value created with the trigger, indicating that PropertyValue is ready to be translated.

USE [Localization] GO /****** Object: Trigger [insElements] Script Date: 11/07/2006 04:44:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [insElements] ON [dbo].[Elements] FOR INSERT AS BEGIN INSERT INTO CultureProperties( ApplicationCultureID, ElementID, PropertyValue,Comments,Updated, Created) SELECT AC.ApplicationCultureID,I.ElementID,NULL,NULL, GETDATE(),GETDATE() FROM Inserted I INNER JOIN DataSources DS ON I.DataSourceID =DS.DataSourceID INNER JOIN ApplicationCultures AC ON DS.ApplicationID=AC.ApplicationID END

When a resource file is deleted from an application, the elements stored in that resource file should be deleted as well. This cascade deletion takes place with an instead of trigger in the DataSources table.

A resource administrator should be able to add and delete cultures to and from a given application. Whenever the administrator deletes a culture from an application, a trigger in the ApplicationCultures table deletes the CultureProperties records corresponding to that culture and application from the CultureProperties table:

USE [Localization] GO /****** Object: Trigger [delApplicationCultures] Script Date: 11/07/2006 04:56:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [delApplicationCultures] ON [dbo].[ApplicationCultures] INSTEAD OF DELETE AS BEGIN

Figure 2. Database Tree View: The tree view shows the columns in the ResxVersions table.
DELETE FROM CultureProperties WHERE ApplicationCultureID IN (SELECT ApplicationCultureID FROM Deleted) DELETE FROM ApplicationCultures WHERE ApplicationCultureID IN (SELECT ApplicationCultureID FROM Deleted) END
The resources archive table ResxVersions lets administrators track the different builds and resources files generated from the database.

Figure 2 shows a tree view of the database.

Figure 3. The ResxVersions Table: The figure shows the data type definitions for the columns in the ResxVersions table, including the field defined as an XML type.
XML Support is Critical for the Toolkit
SQL Server 2005's XML type lets you store XML documents in a field. The ResxVersions table in the database includes an XML Text field (see Figure 3).

Without the XML type introduced in SQL Server 2005 it would be extremely difficult to store the entire XML document defining the resources inside the database. Storing the data in an XMLType field lets the Toolkit use XQuery, T-SQL, and indexing mechanisms to search for old resource values in older versions of a file, reducing the client workload and letting SQL Server handle most of the processing. Using an XMLType field also simplifies exporting; you can export a set of resources for any particular version or build of the application you're localizing or even export a particular element key and all its versions. XQuery and T-SQL together provide a powerful tool for versioning and archiving XML data.

Stored Procedures in the Toolkit Database
The Administration Tool and Localization Web Site applications take advantage of stored procedures inside the database to add applications, update and delete elements and cultures for a particular application, and so forth.

Using the Toolkit-supplied T-SQL queries, administrators can research why translators returned a particular PropertyValue with a null, empty, or inadequate value. For example, when our company shipped a master resource file to a group of translators who were unfamiliar with the terms inside the application, they did not translate the technical error messages, assigning a "not available" (NA) tag to these property values. When the user interface of the application began to display the "N/A" strings, the supplied stored procedures simplified the process of identifying and returning for translation only those terms that had a PropertyValue equal to "N/A." Previously, we would have had to parse all the resource files or create a script to parse the XML text.

Populating the Database
To populate the database, you first use the Visual Studio 2005 Local Resource Generation Tool to generate the local resources fore each of your applications. Then you can script a code parser for any dynamically generated phrase, such as error messages, that will need to be localized. These last phrases can go into a global resource file.

With these .resx files in hand, you can use the Administration Tool to populate the database. At this beta stage, this is the only way to introduce resources into the database. We did this for simplicity and to maintain a strict procedure for the developers. You will have the option in the future to add the ability to create resources to the Web application, so that developers can manually add resources into the database, but at this point, only resources generated by the IDE Local Resource Generation Tool or Global Resource Files can populate the database.

Thanks for your registration, follow us on our social networks to keep up-to-date