Ladies and Gentlemen!
The first time in the world! 🙂
Localizable Enum Type in DBMS!
We already many months have working ENUM type in 5.0 branch of Valentina. Let me remind that ENUM type is not from SQL standard, so different DBs implement it in different way if at all implement. We have implemented it using CREATE TYPE command of SQL Standard. And we have implement ENUM in way similar to PostgreSQL, because it is the most correct: you just CREATE TYPE ENUM once and later using it in all places of your database.
mySQL, in contrast, defines ENUM as part of a particular Table, right in the CREATE TABLE command. This is not good of course, because then you cannot use this type in other tables or for variables of Stored Procedures.
CREATE TABLE sizes (
name ENUM('small', 'medium', 'large')
);
It is interesting that such mature database as Oracle do not have ENUM type.
All these existed implementations have one big problem from our point of view: such enum types contains string values of only one language. Below we will describe our solution.
One of key points is to understand that DBMS is not a programming language, where enum is used to make code more readable. For a programming language it is enough to have enum with English values:
enum{
sunday=1,
monday,
...
};
For databases we use ENUM type as optimization of storage to save on disk numeric values of 1 or 2 bytes size instead of strings 10-20 bytes. And we still must be able DISPLAY string values to user.
PROBLEM comes when we have a DB Server which is access by clients that use different human languages (English, German, Russian, Japan). Do you see any sense to show English ‘monday’ to a Japan user? No sense of course. And ENUM types that you can found now in existed DBMS cannot help you resolve this task (as far as we know).
As we see, the only way for developer is to implement some maps in his PHP/NET/Java/… code. But then ENUM types loose any sense. Developer will start to use just a BYTE type instead.
SOLUTION should be of course ENUM type, which allows to define few sets of values for required locales.
CREATE TYPE T_WEEKDAYS AS ENUM16 ( 'sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday' ),
'Russian':( 'воскресенье', 'понедельник', 'вторник', 'среда', 'четверг', 'пятница', 'суббота' );
We have develop such SQL extensions also for ALTER TYPE command, to be able change one existed value in any locale, add or drop the whole locale set, add new values into all existed locales at once. All these changes in the ENUM type should keep its invariant to be valid: all existed locales of an ENUM type should have the same number of values.
Usage of Localized Values
To not bring chaos with new locales, we have formulate the following rules:
- On default English locale works always, everywhere.
- To get localized version of a string value, developer should call special API method VEnum.Value( ‘locale_name’ ). Syntax can differ of course for different programming languages.
- TODO: to support this feature in Table Editor and SQL Editors, Valentina Studio should provide a menu with list of available Locales for Table/Cursor that contains one or few ENUM fields.
