Table of Contents
Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages, e.g. C/C++. An example of an enum type might be the days of the week, months of year, status of bug, …
Advantage of enums is that internally values are stored as numbers, but user get/set and see string values.
Values of Enum
- An enumeration value must be a quoted string literal; it may not be an expression.
- If Field of type Enum declared as Nullable, then the NULL value is a legal value.
- Values are case-sensitive.
Indexes of Enum Values
- The term “index” here refers only to position within the list of enumeration values. It has nothing to do with table indexes.
- Values from the list of enum elements are numbered beginning with 1.
- The index of the NULL value is NULL.
- Wrong values assigned into Enum field, get 0 index value and are displayed as empty string ''.
Ordering of Values
The ordering of the values in an enum type is the order in which the values were listed when the type was created. In other words, ENUM values are sorted according to their index numbers, but not according to string values.
For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a').
All standard comparison operators and related aggregate functions are supported for enums. For example:
SELECT * FROM T1 WHERE fldDay > 'Sunday' AND fldDay < 'Saturday' ORDER BY fldDay
- You can use ORDER BY CONCAT(fld) to make sure that the field is sorted lexically rather than by index number.
Since Valentina automatically convert types in SQL expressions, you will not get error if you will try compare two fields of different enum types.
Range of Type
Valentina DB allows you to choose the size of enum.
Enum8 can handle up to 255 values. Enum16 - up to 65535.
As you can see, on default, Valentina tends to use one byte (while e.g. mySQL uses 2 bytes, and PostgreSQL uses 4 bytes always). We are skeptical about need to waste space for such enums. It is hard to image enum with few thousands elements. In reality we use enums for types with quite small number of elements. Besides, in Valentina usage of one byte values gives additional benefits in indexing comparing to 2/4 byte values.
Flags for Enum Field
This field can be declared as Nullable. In this case a special bitmap is associated with this field. The NULL value is a legal value and the default value is NULL.
This field can have flag fIndexed.
This field can have flag fUnique, although there is no many sense to specify an ENUM field to be unique.
Valentina database has unique feature for ENUM type. It allows you to define a few localized versions of the same logical values of enum type.
We consider this as very important feature for DBMS Server that has multi-language clients. Really, what sense for a Japan or Russian user see enum strings on English? Zero sense. This means that for such projects ENUM type becomes useless. Developer will start to use some INT field type and implement own maps to strings.
With Valentina ENUMs developer can avoid this job. Let's consider how this works.
You can define few locales right in the CREATE TYPE command or you can add them later using ALTER TYPE command. Please note, that the first set has English strings always and is required.
-- with some additional locales: -- CREATE TYPE DayOfWeek AS ENUM8 ( 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' ), 'ru':( 'Воскресенье', 'Понедельник', 'Вторник', 'Среда', 'Четверг', 'Пятница', 'Суббота' ), 'de':( 'Sonntag', 'Montag', 'Dienstag', 'Mittwoch', 'Donnerstag', 'Freitag', 'Samstag' )
RULE: Each localized set of enum values must have the same number of items and in the same order as in the English set.
Its okay to add more locales to an ENUM type later or even at runtime, because we think that original creator of type may don't know some languages, so this can do somebody else later. To add s localized set you can use ALTER TYPE command:
ALTER TYPE DayOfWeek ADD VALUES 'ru':( 'Воскресенье', 'Понедельник', 'Вторник', 'Среда', 'Четверг', 'Пятница', 'Суббота' );
RULE: Normal operations always get/set values in English. To get/set values in some locale you need specify that.
This rule resolves a problem that with an ENUM type can work:
- a) CODE, which should be stable and not depend on locales of users.
- b) GUI, which should usually display localised versions of values.
SELECT convert_enum_value_to( 'DayOfWeek', f1, 'de' ) FROM T1;
INSERT INTO T1(f1) VALUES ( convert_enum_value_from( 'DayOfWeek', f1, 'de' ) );
Enum types are created using the CREATE TYPE SQL command. For example:
CREATE TYPE DayOfWeek AS ENUM8( 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' );
Once created, the enum type can be used in table and function definitions much like any other type. Note, that you get and set values of an ENUM field as string values in single quotes.
CREATE TABLE T1( fldDay DayOfWeek ); INSERT INTO T1( fldDay ) VALUES( 'Friday' ); SELECT * FROM T1; -------- fldDay -------- Friday
- Duplicate values in the definition cause an error.
Alter Enum Type
- You can modify existed ENUM type using ALTER TYPE SQL command.
- Valentina allows you renamed enum type, append one or few values, because this not affects existed data, change string literal of enum value because this also not affect existed data.
- Valentina do not allow you drop some value of enum type or change their order, because this can affect existed data.
Convert to/from Enum Type
- You can convert existed numeric field into ENUM type, if all values of this numeric field fit range of your enum. In case error happens during convert of type, the original field will not be changed.
- You can convert ENUM field into numeric column also. It is obvious that in this case the numeric column will get values that correspond to indexes of enum values.
Drop Enum Type
- You can drop an user defined type using the DROP TYPE SQL command.
- If this TYPE is used by any Table or Procedire, then Valentina will refuse to drop type. So it works in RESTRICT mode.
GUI Tools and others need to be able to see what types exists in the database. For this you can use SHOW TYPES SQL command.
- SQL Standard do not have enum types.
- PostgreSQL was used as basis for Valentina SQL to work with enum types. But we still have few differences from Postgre:
- Valentina uses 1 or 2 bytes. PostgreSQL uses 4 bytes always.
- PostgreSQL do not converts types implicitly, but Valentina DB do as well as mySQL.
- PostgreSQL literals for enum values must be less than NAMEDATALEN bytes long (64 in a standard PostgreSQL build). There is no such limit for Valentina DB.
- mySQL 5.6 yet do not have support of ENUM type using CREATE TYPE command. MySQL have limited implementation of ENUM, when you specify it inside of CREATE TABLE command. Also, MySQL always use 2 bytes for enum values, but Valentina uses 1 byte on default, and can use 2 bytes. All rest is very similar technically because Valentina and mySQL do implicit type conversion. Since mySQL do not support work with types as separate object of db schema, it also do not have such commands as ALTER TYPE and SHOW TYPES.
- Valentina SQL for CREATE/DROP TYPE commands supports “OR REPLACE” and “IF NOT EXISTS” modifiers, like for any other Valentina SQL command. PostgreSQL 9.x have “IF EXISTS” for DROP TYPE only. mySQL 5.6 do not have this feature.