Globalization¶

Character data¶

Character data (strings) may be stored with VARCHAR(STRING), CHAR, ENUM, and they support charset and collation.

Charset(character set, codeset) controls how characters are stored (on any type of storage) as bytes, or how a series of bytes forms a character. CUBRID supports ISO-88591-1, UTF-8 and EUC-KR charsets. For UTF-8, we support only the Unicode characters up to codepoint 10FFFF (encoded on up to four bytes). For instance, the character "Ç" is encoded in codeset ISO-8859-1 using a single byte (C7), in UTF-8 is encoded with 2 bytes (C3 87), while in EUC-KR this character is not available.

Collation decides how strings compare. Most often, users require case insensitive and case sensitive comparisons. For instance, the strings "ABC" and "abc" are equal in a case insensitive collation, while in a case sensitive collation, they are not, and depending on other collation settings, the relationship can be "ABC" < "abc" , or "ABC" > "abc".

Collation means more than comparing character casing. Collation decides the relationship between two strings (greater, lower, equal), is used in string matching (LIKE), or computing boundaries in index scan.

In CUBRID, a collation implies a charset. For instance, collations "utf8_en_ci" and "iso88591_en_ci" perform case insensitive compare, but operate on different charsets. Although for ASCII range, in these particular cases the results are similar, the collation with "utf8_en_ci" is slower, since it needs to work on variable number of bytes (UTF-8 encoding).

• "'a' COLLATE iso88591_en_ci" indicates "_iso88591'a' COLLATE iso88591_en_ci".
• "'a' COLLATE utf8_en_ci" indicates "_utf8'a' COLLATE utf8_en_ci".

All string data types support precision. Special care is required with fixed characters(CHAR). The values of this types are padded to fill up the precision. For instance, inserting "abc" into a CHAR(5) column, will result in storing "abc " (2 padding spaces are added). Space (ASCII 32 decimal, Unicode 0020) is the padding character for most charsets. But, for EUC-KR charset, the padding consists of one character which is stored with two bytes (A1 A1).

Related Terms¶

• Character set : A group of encoded symbols (giving a specific number to a certain symbol)
• Collation : A set of rules for comparison of characters in the character set and for sorting data
• Locale : A set of parameters that defines any special variant preferences such as number format, calendar format (month and day in characters), date/time format, collation, and currency format depending on the operator's language and country. Locale defines the linguistic localization. Character set of locale defines how the month in characters and other data are encoded. A locale identifier consists of at least a language identifier and a region identifier, and it is expressed as language[_territory][.codeset] (For example, Australian English using UTF-8 encoding is written as en_AU.UTF-8).
• Unicode normalization : The specification by the Unicode character encoding standard where some sequences of code points represent essentially the same character. CUBRID uses Normalization Form C (NFC: codepoint is decomposed and then composed) for input and Normalization Form D (NFD: codepoint is composed and then decomposed) for output. However, CUBRID does not apply the canonical equivalence rule as an exception.

For example, canonical equivalence is applied in general NFC rule so codepoint 212A (Kelvin K) is converted to codepoint 4B (ASCII code uppercase K). Since CUBRID does not perform the conversion by using the canonical equivalence rule to make normalization algorithm quicker and easier, it does not perform reverse-conversion, too.

• Canonical equivalence : A basic equivalence between characters or sequences of characters, which cannot be visually distinguished when they are correctly rendered. For example, let's see 'Å' ('A' with an angstrom). 'Å' (Unicode U + 212B) and Latin 'A' (Unicode U + 00C5) have same A and different codepoints, however, the decomposed result is 'A' and U+030A, so it is canonical equivalence.
• Compatibility equivalence : A weaker equivalence between characters or sequences of characters that represent the same abstract character. For example, let's see number '2' (Unicode U + 0032) and superscript '²'(Unicode U + 00B2). '²' is a different format of number '2', however, it is visually distinguished and has a different meaning, so it is not canonical equivalence. When normalizing '2²' with NFC, '2²' is maintained since it uses canonical equivalence. However, with NFKC, '²' is decomposed to '2' which is compatibility equivalence and then it can be recomposed to '22'. Unicode normalization of CUBRID does not apply the compatibility equivalence rule.

For explanation on Unicode normalization, see Unicode Normalization. For more details, see http://unicode.org/reports/tr15/.

The default value of the system parameter related to Unicode normalization is unicode_input_normalization=no and unicode_output_normalization=no. For a more detailed description on parameters, see Statement/Type-Related Parameters.

Locale Attributes¶

Locale is defined by following attributes.

• Charset (codeset) : How bytes are interpreted into single characters (Unicode codepoints)
• Collations : Among all collations defined in locale of LDML(UNICODE Locale Data Markup Language) file, the last one is the default collation. Locale data may contain several collations.
• Alphabet (casing rules) : One locale data may have up 2 alphabets, one for identifier and one for user data. One locale data can have two types of alphabets.
• Calendar : Names of weekdays, months, day periods (AM/PM)
• Numbering settings : Symbols for digit grouping, monetary currency
• Text conversion data : For CSQL conversion. Option.
• Unicode normalization data : Data converted by normalizing several characters with the same shape into one based on a specified rule. After normalization, characters with the same shape will have the same code value even though the locale is different. Each locale can activate/deactivate the normalization functionality.

Collation Properties¶

A collation is an assembly of information which defines an order for characters and strings. In CUBRID, collation has the following properties.

• Strength : This is a measure of how "different" basic comparable items (characters) are. This affects selectivity. In LDML files, collation strength is configurable and has four levels. For example a Case insensitive collation should be set with level = "secondary" (2) or "primary" (1).
• Whether it supports or not expansions and contractions

Each column has a collation, so when applying LOWER(), UPPER() functions the casing rules of locale which defines the collation’s default language is used.

Depending on collation properties some CUBRID optimizations may be disabled for some collations:

• LIKE rewrite: is disabled for collations which maps several different character to the same weight (case insensitive collations for example) and for collations with expansions.
• Covering index scan: disabled for collations which maps several different character to the same weight (see Covering Index).
• Prefix index: cannot be created on columns using collation with expansions.

For more information, see Collation settings impacting CUBRID features .

Collation Naming Rules¶

The collation name in CUBRID follows the conversion:

<charset>_<lang specific>_<desc1>_<desc2>_...

• <charset>: The full charset name as used by CUBRID. iso88591, utf8, euckr.

• <lang specific>: a region/language specific. The language code is expected as two characters; en, de, es, fr, it, ja, km, ko, tr, vi, zh. "gen" if it does not address a specific language, but a more general sorting rule.

• <desc1>_<desc2>_...: They have the following meaning. Most of them apply only to LDML collations.

  • ci: case insensitive In LDML, can be obtained using the settings: strength="secondary" caseLevel="off" caseFirst="off".

  • cs: case sensitive; By default all collations are case sensitive. In LDML, can be obtained using at least: strength="tertiary".

  • bin/binary: it means that the sorting order under such collation is almost the same with the order of codepoints; If memory (byte) compare is used, then almost the same result would be obtain. Space character and EUC double-byte padding character are always sorted as zero in "bin" collation. No collations with such setting are currently configured in LDML (they are already available as built-in), but a similar one can be obtained using the maximum setting strength="quaternary" or strength="identical".

  • ai : accent insensitive; this means that 'Á' is sorted the same as 'A'. Due to particularities of the UCA based algorithms, an accent insensitive collation is also a case insensitive collation. In LDML, can be obtained using: strength="primary".

  • uca : this signals a UCA based collation; this is used only to differentiate such collations from similar built-in variants. All LDML collations are based on UCA, but in order to keep shorter names only two collations ( 'utf8_ko_cs_uca' , 'utf8_tr_cs_uca' ) have this description in their names, in order to differentiate them from 'utf8_ko_cs' and 'utf8_tr_cs' collations.

  • exp : this collations use a full-word matching/compare algorithm, contrary to the rest of collations which use character-by-character compare. This collation uses a more complex algorithm, with multiple passes which is much slower, but may prove useful for alphabetical sorts. In LDML, the Expansion needs to be explicit by adding CUBRIDExpansions="use".

  • ab : accent backwards; it is particularity of French-Canadian sorting, where level 2 of UCA (used to store accents weights) is compared from end of string towards the beginning. This collation setting can be used only when :ref`expansion` setting is also activated. The "backwards" setting allows for the following sorting:

    • Normal Accent Ordering : cote < coté < côte < côté
    • Backward Accent Ordering : cote < côte < coté < côté

  • cbm: contraction boundary match; it is a particularity of collations with Expansion and Contraction and refers to how it behaves at string matching when a Contraction is found.

    Suppose the collation has defined the Contraction "ch"; then normally, the pattern "bac" will not match the string"bachxxx" But when the collation is configured to allow "matching the characters starting a contraction", the above matching will return a positive. Only one collation is configured in this manner - 'utf8_ja_exp_cbm' - Japanese sorting requires a lot of contractions.

The collation names are not dynamically generated. They are user defined (configured in LDML), and should reflect the settings of the collation.

The name of collation influences the internal numeric id of the collation. For instance, in CUBRID only 256 collations are allowed, and the numeric IDs are assigned as:

• 0 -31 : built-in collations (for these collations the name and id are hard-coded)
• 32 - 46 : LDML collations having "gen" as "language" part
• 47 - 255: the rest of LDML collations

If you want to include all locales into the database which CUBRID provide, first, copy cubrid_locales.all.txt of $CUBRID/conf directory into cubrid_locales.txt and next, run make_locale script(in extension, Linux is .sh, Windows is .bat). For more details on make_locale script, see Step 2: Compiling Locale. If you want to include the newly added locale information into the existing database, run "cubrid synccolldb <dbname>". For more information, see Synchronization of Database Collations with System Collations.

If you include all locales defined in LDML files, CUBRID has the following collations.

On the above collations, 9 collations like iso88591_bin, iso88591_en_cs, iso88591_en_ci, utf8_bin, euckr_bin, utf8_en_cs, utf8_en_ci, utf8_tr_cs and utf8_ko_cs, are built in the CUBRID before running make_locale script.

Files For Locale Setting¶

CUBRID uses following directories and files to set the locales.

• $CUBRID/conf/cubrid_locales.txt file: A configuration file containing the list of locales to be supported
• $CUBRID/conf/cubrid_locales.all.txt file: A configuration file template with the same structure as cubrid_locales.txt. Contains the entire list of all the locales that the current version of CUBRID is capable of supporting without any efforts from the end user’s side.
• $CUBRID/locales/data directory: This contains files required to generate locale data.
• $CUBRID/locales/loclib directory: contains a C header file, locale_lib_common.h and OS dependent makefile which are used in the process of creating / generating locales shared libraries.
• $CUBRID/locales/data/ducet.txt file: Text file containing default universal collation information (codepoints, contractions and expansions, to be more specific) and their weights, as standardized by The Unicode Consortium, which is the starting point for the creation of collations. For more information, see http://unicode.org/reports/tr10/#Default_Unicode_Collation_Element_Table .
• $CUBRID/locales/data/unicodedata.txt file: Text file containing information about each Unicode codepoint regarding casing, decomposition, normalization etc. CUBRID uses this to determine casing. For more information, see http://www.ksu.ru/eng/departments/ktk/test/perl/lib/unicode/UCDFF301.html .
• $CUBRID/locales/data/ldml directory: XML files, name with the convention cubrid_<locale_name>.xml, containing locale information presented in human-readable XML format (LDML Locale Data Markup Language); a file for each of the supported language.
• $CUBRID/locales/data/codepages directory: contains codepage console conversion for single byte codepages(8859-1.txt , 8859-15.txt, 8859-9.txt) and codepage console conversion for double byte codepages(CP1258.txt , CP923.txt, CP936.txt , CP949.txt).
• $CUBRID/bin/make_locale.sh file or %CUBRID%\bin\make_locale.bat file: A script file used to generate shared libraries for locale data
• $CUBRID/lib directory: Shared libraries for generated locales will be stored here.

Step 1: Selecting a Locale¶

Configure locales to use on $CUBRID/conf/cubrid_locales.txt. You can select all or some of locales which are supported.

CUBRID supports locales as follows: en_US, de_DE, es_ES, fr_FR, it_IT, ja_JP, km_KH, ko_KR, tr_TR, vi_VN, zh_CN.

The language and country for each locale are shown in the following table.

<lang_name>  <LDML file>                                        <lib file>
ko_KR        /home/CUBRID/locales/data/ldml/cubrid_ko_KR.xml    /home/CUBRID/lib/libcubrid_ko_KR.so

Step 2: Compiling Locale¶

Once the requirements described above are met, the locales can be compiled.

Regarding the embedded locales in CUBRID, they can be used without compiling user locale library, so they can be used by skipping the step 2. But there are differences between the embedded locale and the library locale. Regarding this, refer Built-in Locale and Library Locale.

To compile the locale libraries, one must use the make_locale (.bat for Windows .sh for Linux) utility script from command console. The file is delivered in CUBRID/bin folder so it should be resolved by PATH environment variable. Here $CUBRID, $PATH are the environment variables of Linux, %CUBRID%, %PATH% are the environment variables of Windows.

Usage can be displayed by running make_locale.sh -h (make_locale /h in Windows. it requires Visual C++ 2005, 2008 or 2010 ).

make_locale.sh [options] [locale]

options ::= [-t 32|64 ] [-m debug|release]
locale ::= [de_DE|es_ES|fr_FR|it_IT|ja_JP|km_KH|ko_KR|tr_TR|vi_VN|zh_CN]

• options

  • -t : Selects 32bit or 64bit (default value: 32).
  • -m : Selects release or debug. In general, release is selected (default value: release). The debug mode is provided for developers who would like to write the locale library themselves. Selects release or debug. In general, release is selected (default value: release). The debug mode is provided for developers who would like to write the locale library themselves.

• locale : The locale name of the library to build. If locale is not specified, the build includes data from all configured locales. In this case, library file is stored in $CUBRID/lib directory with the name of libcubrid_all_locales.so (.dll for Windows).

To create user defined locale shared libraries, two choices are available:

• Creating a single lib with all locales to be supported

  make_locale.sh                         # Build and pack all locales (32/release)
  

• Creating one lib for each locale to be supported

  make_locale.sh -t 64 -m release ko_KR
  

The first choice is recommended. In this scenario, some data may be shared among locales. If you choose the first one, a lib supporting all locales has less than 15 MB; in the second one, consider for each locale library from 1 MB to more than 5 MB. Also the first one is recommended because it has no runtime overhead during restarting the servers when you choose the second one.

Step 3: Setting CUBRID to Use a Specific Locale¶

Several locales can be defined, but only one locale can be selected as the default locale, by using the CUBRID_CHARSET environment variable.

In addition to the possibility of specifying a default locale, one can override the default calendar settings with the calendar settings from another locale, using the intl_date_lang system parameter.

• CUBRID_CHARSET will be in the format: <locale_name>.[utf8 | iso] (e.g. tr_TR.utf8, en_EN.ISO, ko_KR.utf8)
• intl_date_lang : <locale_name>. The possible values for <locale_name> are listed on Step 1: Selecting a Locale.

By default, if no charset is included in CUBRID_CHARSET, the ISO charset is assumed.

Step 4: Creating a Database with the Selected Locale Setting¶

Once the CUBRID_CHARSET and intl_date_lang environment variables have been set, one can create a new database (or delete and recreate an existing one). When issuing the command "cubrid createdb <db_name>", a database will be created using the settings in the variables described above. The charset and locale name are stored in "db_root" system catalog table. Once a database is created with a language and charset, it cannot change these settings.

Step 5 (optional): Manually Verifying the Locale File¶

The contents of locales libraries  may be displayed in human readable form using the dumplocale CUBRID utility. Execute cubrid dumplocale -h to output the usage. The used syntax is as follows:

cubrid dumplocale [options] [language-string]

options ::= -i|--input-file <shared_lib>
            -d|--calendar
            -n|--numeric
            {-a |--alphabet=}{l|lower|u|upper|both}
            -c|--codepoint-order
            -w|--weight-order
            {-s|--start-value} <starting_codepoint>
            {-e|--end-value} <ending_codepoint>
            -k
            -z

language-string ::= de_DE|es_ES|fr_FR|it_IT|ja_JP|km_KH|ko_KR|tr_TR|vi_VN|zh_CN

• dumplocale: A command which dumps the contents of locale shared library previously generated using LDML input file.
• language-string: One of de_DE, es_ES, fr_FR, it_IT, ja_JP, km_KH, ko_KR, tr_TR, vi_VN, zh_CN. Configures the locale language to dump the locale shared library. If it's not set, all languages which are configured on cubrid_locales.txt are given.

The followings are [options] for cubrid dumplocale.

-i, --input-file=FILE¶

The name of the locale shared library file (< shared_lib>) created previously. It includes the directory path.

-d, --calendar¶

Dumps the calendar and date/time data. Default value: No

-n, --numeric¶

Dumps the number data. Default value: No

-a, --alphabet=l|lower|u|upper|both¶

Dumps the alphabet and case data. Default value: No

--identifier-alphabet=l|lower|u|upper¶

Dumps the alphabet and case data for the identifier. Default value: No

-c, --codepoint-order¶

Dumps the collation data sorted by the codepoint value. Default value: No (displayed data: cp, char, weight, next-cp, char and weight)

-w, --weight-order¶

Dumps the collation data sorted by the weight value. Default value: No (displayed data: weight, cp, char)

-s, --start-value=CODEPOINT¶

Specifies the dump scope. Starting codepoint for -a, --identifier-alphabet, -c, -w options. Default value: 0

-e, --end-value=CODEPOINT¶

Specifies the dump scope. Ending codepoint for -a, --identifier-alphabet, -c, -w options. Default value: Max value read from the locale shared library.

-k, --console-conversion¶

Dumps the data of console conversion. Default value: No

-z, --normalization¶

Dumps the normalization data. Default value: No

The following example shows how to dump the calendar, number formatting, alphabet and case data, alphabet and case data for the identifier, collation sorting based on the codepoint order, collation sorting based on the weight, and the data in ko_KR locale into ko_KR_dump.txt by normalizing:

% cubrid dumplocale -d -n -a both -c -w -z ko_KR > ko_KR_dump.txt

It is highly recommended to redirect the console output to a file, as it can be very big data, and seeking information could prove to be difficult.

Step 6: Starting CUBRID-Related Processes¶

All CUBRID-related processes should be started in an identical environmental setting. The CUBRID server, the broker, CAS, and CSQL should use an identical CUBRID_CHARSET setting value and the locale binary file of an identical version. Also CUBRID HA, CUBRID Shard should use the same setting. For example, in the CUBRID HA, master server, slave server and replica server should use the same environmental variable setting.

There is no check on the compatibility of the locale used by server and CAS (client) process, so the user should make sure the LDML files used are the same.

Locale library loading is one of the first steps in CUBRID start-up. Locale (collation) information is required for initializing databases structures (indexes depends on collation).

This process is performed by each CUBRID process which requires locale information: server, CAS, CSQL, createdb, copydb, unload, load DB.

The process of loading a locale library is as follows:

• If no lib path is provided, CUBRID will try to load $CUBRID/lib/libcubrid_<lang_name>.so file; if this file is not found, then CUBRID assumes all locales are found in a single library: $CUBRID/lib/libcubrid_all_locales.so.
• If suitable locale library cannot be found or any other error occurs during loading, the CUBRID process stops.
• If collations between the database and the locale library are different, the CUBRID process cannot start. To include the newly changed collations of the locale library, firstly synchronize the database collation with the system collation by running cubrid synccolldb command. Next, update from the existing database to the wanted collations of schemas and data. For more details, see Synchronization of Database Collations with System Collations.

Synchronization of Database Collations with System Collations¶

CUBRID's normal operation requires that the system collation and the database collation must be the same. The system locale means that the locale which include built-in locales and library locales created through cubrid_locales.txt(refer Locale Setting), and it includes the system collation information. The database collation information is stored on the _db_collation system catalog table.

cubrid synccolldb utility checks if the database collation is the same with the system collation, and synchronize into the system collation if they are different. However, note that this utility doesn't transform the data itself stored on the database.

This utility can be used when the existing database collation should be changed after the system locale is changed. However, there are operations which the user have to do manually.

The user should do this operations before the synchronization. These operations can be done by running CSQL with cubrid_synccolldb_<database_name>.sql file, which is created by cubrid synccolldb -c.

• change collation using ALTER TABLE .. MODIFY statement.
• remove any views, indexes, triggers or partitions containing the collation.

Run synchrization with cubrid synccolldb. After then, do the following operations.

• recreate views, indexes, triggers, or partitions
• update application statements to use new collations

This utility should work only in offline mode.

synccolldb syntax is as follows.

cubrid synccolldb [options] database_name

• cubrid: An integrated utility for the CUBRID service and database management.
• synccolldb: A command to synchronize collations of a database with collations from the system(according to contents of locales libraries and $CUBRID/conf/cubrid_locales.txt).
• database_name: A database name to be synchronized with collations from the system.

If [options] is omitted, synccolldb checks the collation differences between the system and the database, synchronize the database collation with the system collation, and create the cubrid_synccolldb_<database_name>.sql file including the queries of objects to be dropped before the synchronization.

The followings are [options] which are used on cubrid synccolldb.

-c, --check-only¶

This option prints out the collation information which is different between the database collation and the system collation.

-f, --force-only¶

This option doesn't ask when updating the database collation with the system collation.

The following shows that how it works when the system collation and the database collation are different.

Firstly, make locale library about ko_KR locale.

$ echo ko_KR > $CUBRID/conf/cubrid_locales.txt
$ make_locale.sh -t 64

Next, create the database.

$ cubrid createdb xdb --db-volume-size=20m --log-volume-size=20m

Create a schema. At this time, specify the needed collation in each table.

$ csql -S -udba xdb -i in.sql

CREATE TABLE dept(depname STRING PRIMARY KEY) COLLATE utf8_ko_cs_uca;
CREATE TABLE emp(eid INT PRIMARY KEY, depname STRING,address STRING) COLLATE utf8_ko_cs_uca;
ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY (depname) REFERENCES dept(depname);

Change the locale setting of the system. If you do not any values on cubrid_locales.txt, the database consider that only built-in locales exist

$ echo "" > $CUBRID/conf/cubrid_locales.txt

Check the difference between system and database by running cubrid synccolldb -c command.

$ cubrid synccolldb -c xdb

----------------------------------------
----------------------------------------
Collation 'utf8_ko_cs_uca' (Id: 133) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen_ci' (Id: 44) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen_ai_ci' (Id: 37) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
Collation 'utf8_gen' (Id: 32) not found in database or changed in new system configuration.
----------------------------------------
----------------------------------------
There are 4 collations in database which are not configured or are changed compared to system collations.
Synchronization of system collation into database is required.
Run 'cubrid synccolldb -f xdb'

If the indexes exist, firstly you should remove the indexes, and change the collation of each table, then recreate the indexes directly. The process to remove indexes and change the collation of tables can be executed by using cubrid_synccolldb_xdb.sql file which was created by synccolldb command. On the below example, a foreign key is the index which you should recreate.

$ cat cubrid_synccolldb_xdb.sql

ALTER TABLE [dept] COLLATE utf8_bin;
ALTER TABLE [emp] COLLATE utf8_bin;
ALTER TABLE [emp] DROP FOREIGN KEY [fk_emp_depname];
ALTER TABLE [dept] MODIFY [depname] VARCHAR(1073741823) COLLATE utf8_bin;
ALTER TABLE [emp] MODIFY [address] VARCHAR(1073741823) COLLATE utf8_bin;
ALTER TABLE [emp] MODIFY [depname] VARCHAR(1073741823) COLLATE utf8_bin;

$ csql -S -u dba -i cubrid_synccolldb_xdb.sql xdb

Removing the obsolete collations by executing the above cubrid_synccolldb_xdb.sql script file must be performed before forcing the synchronization of system collations into database.

Run cubrid synccolldb command. If the option is omitted, the message is shown to ask to run this command or not; if the -f option is given, the synchronization is run without checking message.

$ cubrid synccolldb xdb
Updating system collations may cause corruption of database. Continue (y/n) ?
Contents of '_db_collation' system table was updated with new system collations.

Recreate the dropped foreign key.

$ csql -S -u dba xdb

ALTER TABLE emp ADD CONSTRAINT FOREIGN KEY fk_emp_depname(depname) references dept(depname);

Charset and Collation of Column¶

Charset and Collation apply to string data types: VARCHAR (STRING), CHAR and ENUM . By default, all string data types inherit the default database collation and character set, but CUBRID supports two modifiers which affect collation and character set.

coll_id  coll_name        charset_name    is_builtin  has_expansions  contractions  uca_strength
================================================================================================
0        'iso88591_bin'   'iso88591'     'Yes'        'No'            0             'Not applicable'
1        'utf8_bin'       'utf8'         'Yes'        'No'            0             'Not applicable'
2        'iso88591_en_cs' 'iso88591'     'Yes'        'No'            0             'Not applicable'
3        'iso88591_en_ci' 'iso88591'     'Yes'        'No'            0             'Not applicable'
4        'utf8_en_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
5        'utf8_en_ci'     'utf8'         'Yes'        'No'            0             'Not applicable'
6        'utf8_tr_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
7        'utf8_ko_cs'     'utf8'         'Yes'        'No'            0             'Not applicable'
8        'euckr_bin'      'euckr'        'Yes'        'No'            0             'Not applicable'

<data_type> ::= <column_type> [<charset_modifier_clause>] [<collation_modifier_clause>]

<charset_modifier_clause> ::= {CHARACTER_SET | CHARSET} {<char_string_literal> | <identifier> }

<collation_modifier_clause> ::= {COLLATE } {<char_string_literal> | <identifier> }

CREATE TABLE t1 (s1 VARCHAR (100) CHARSET utf8);

ALTER TABLE t1 CHANGE s1 c1 CHAR(10) COLLATE utf8_en_cs;

SELECT CAST (c1 as VARCHAR(5) COLLATE 'iso88591_en_ci') FROM t1 ORDER BY 1;

SELECT c1 FROM t1 ORDER BY CAST (c1 as VARCHAR(5) COLLATE iso88591_en_ci);

CREATE TABLE t (s1 STRING COLLATE utf8_en_cs, s2 STRING COLLATE utf8_tr_cs);

-- insert values into both columns

SELECT s1, s2 FROM t WHERE s1 > s2;

Charset and Collation of Tables¶

The charset and the collation can be specified after the table creation syntax.

CREATE TABLE table_name ( column_list )  [CHARSET charset_name] [COLLATE collation_name]

If the charset and the collation of a column are omitted, the charset and the collation of a table is used. If the charset and the collation of a table are omitted, the charset and the collation of a system is used.

The following shows how to specify the collation on the table.

CREATE TABLE tbl(i1 INTEGER, s STRING) CHARSET utf8 COLLATE utf8_en_cs;

Charset and Collation of String Literals¶

The charset and the collation of a string literal are determined based on the following priority.

1. Charset Introducer introducer or COLLATE modifier of string literal
2. The charset and the collation defined by the SET NAMES Statement
3. System charset and collation(Default collation set by the charset and the CUBRID_CHARSET environment variable)

SET NAMES [ charset_name ] [ COLLATE collation_name]

SELECT 'a';

SET NAMES utf8;
SELECT 'a';

[charset_introducer]'constant-string' [ COLLATE collation_name ]

SELECT 'cubrid';
SELECT _utf8'cubrid';
SELECT _utf8'cubrid' COLLATE utf8_en_cs;

SET NAMES utf8 COLLATE utf8_en_ci;
SELECT 'a' COLLATE utf8_en_cs;

Charset and Collation of Expressions¶

The charset and collation of expression's result are inferred from charset and collation of arguments in the expression. Collation inference in CUBRID is based on coercibility. For more information, see How to Determine Collation among Columns with Different Collation.

All string matching function(LIKE, REPLACE, INSTR, POSITION, LOCATE, SUBSTRING_INDEX, FIND_IN_SET, etc) and comparison operators(<, >, =, etc) take collation into account.

Charset and Collation of System Data¶

The system charset is taken from CUBRID_CHARSET environment variable. The system collation is always the binary collation (<charset>_bin) of system charset. CUBRID supports three charset(iso88591, euckr, utf8), and accordingly three system collations.

Impact of CUBRID_CHARSET¶

The locale part of CUBRID_CHARSET controls:

• character supported in identifiers and casing rules (called "alphabet")
• default locale for date - string conversion functions
• default locale for number - string conversion functions
• console conversion in CSQL

CREATE TABLE ÈABC;

String literal input and output¶

String literals data may be entered to CUBRID by various ways:

• API interface (CCI)
• language dependent interface - JDBC, Perl driver, etc.
• CSQL - command line from console or input file

When receiving character data through drivers, CUBRID cannot be aware of the charset of those strings. All text data contained between quotes (string literals) are handled by CUBRID as raw bytes; the charset meta-information must be provided by client. CUBRID provides a way for the client to instruct it about which type of encoding is using for its character data. This is done with the SET NAMES statement or with charset introducer.

<consoleconversion type="ISO88599" windows_codepage="28599" linux_charset="iso88599,ISO_8859-9,ISO8859-9,ISO-8859-9">

Contraction and Expansion of Collation¶

CUBRID supports contraction and expansion for collation. Contraction and expansion are available for UTF-8 charset collation. You can see the contraction and expansion of collation in the collation setting in the LDML file. Using contraction and expansion affects the size of locale data (shared library) and server performance.

Operations Requiring Collation and Charset¶

Charset

Charset information is required for functions which use character primitives. There are exceptions : OCTET_LENGTH() and BIT_LENGTH() do not require charset internally to return the length in bytes and bits. However, for the same glyph (character symbol) stored in different charset, they return different values:

CREATE TABLE t (s_iso STRING CHARSET iso88591, s_utf8 STRING CHARSET utf8);
SET NAMES iso88591;
INSERT INTO t VALUES('È','È');

-- the first returnes 1, while the second does 2
SELECT OCTET_LENGTH(s_iso), OCTET_LENGTH(s_utf8) FROM t;

The previous example should be run from console (or a client) with ISO-8859-1 charset.

Collation

Collation is required in functions and operators which involves a comparison between two strings or matching two strings. These includes functions like : STRCMP(), POSITION(), LIKE condition, and operators (<,= , >=, etc.). Also clauses like ORDER BY, GROUP BY and aggregates(MIN(), MAX(), GROUP_CONCAT()) use collation.

Also, collation is considered in UPPER() and LOWER() functions, in the following manner:

• Each collation has a default (parent) locale.
• UPPER and LOWER functions are performed using the user alphabet of the default locale of the collation.

For most collations, the default locale is obvious (is embedded in the name):

• utf8_tr_cs → tr_TR.utf8
• iso88591_en_ci → en_US (ISO-8859-1 charset)

The binary collations have the following default locales:

• iso88591_bin → en_US (ISO-8859-1 charset)
• utf8_bin (en_US.utf8 - built-in locale - and handles ASCII characters only)
• euckr_bin (ko_KR.euckr - built-in locale - and handles ASCII characters only)

There are some generic collations available in LDML. These collations have as default locale, the locale in which they are first found. The order of loading is the locales order from $CUBRID/conf/cubrid_locales.txt. Assuming the default order (alphabetical), the default locale for all generic LDML collations is de_DE (German).

Charset conversion

For the three charsets supported by CUBRID the conversion rules are:

• euckr to/from utf8 not allowed

• iso88591 to utf8 - byte conversion - it is the only transition where all characters in the source can be encoded in the destination.

• iso88591 to euckr - not allowed (some characters cannot be encoded in EUC-KR)

• utf8 and euckr to iso88591 - byte reinterpret; When value (to be converted) has domain with infinite precision, the size of storage remains the same, but precision increases.

  Note

  In 9.0 version, when value has domain with finite precision, the precision is kept resulting in data truncation. In 9.1 version, CUBRID no longer truncates the data to keep precision; in cases where precision is not enforced, CUBRID will extend the precision in order to keep data integrity.

Collation settings impacting CUBRID features¶

LIKE Conditional Optimization

The LIKE conditional expression compares patterns between string data, and returns TRUE if a string whose pattern matches the search word is found.

As already proven above, when using a "collation without expansion support", each codepoint will receive a single integer value, representing its weight in the comparison process. This weight value is computed based on collation settings (strength, casing etc.). Due to the fact that characters can always be regarded as single entities, trying to match a string with a pattern using the LIKE predicate is equivalent to checking if the string can be found in a certain range of strings. For example in order to process a predicate such as ''s LIKE 'abc%' '', CUBRID will first rewrite it as a range restriction for the string "s". "s LIKE 'abc%'" means that "s" must start with the string "abc". In terms of string comparison, this is equivalent, in expansion-free collations, with "s" being greater than "abc", but smaller than its successor (using the English alphabet, the successor of "abc" would be "abd").

s LIKE 'abc%' → s ≥ 'abc' AND s < 'abd' (if using strictly the English alphabet)

This way, the actual interpretation of LIKE is replaced with simple comparisons, but "Collations with expansion support" behave differently. As described above, if a collation supporting expansions is used, single weight values are no longer calculated for each codepoint based on DUCET, but the information from their corresponding collation element list is stored with original values (even though it is compressed). To compare strings when using such a collation means comparing the concatenated lists of collation elements for each codepoint or expansion, level by level. For more information about comparing strings on the collation with expansion, see Expansion.

If the LIKE predicate rewrite method is kept the same as in a collation with no expansion support as above example, the comparison result can be wrong. To ensure the right query result, the LIKE predicate rewrite method is ran differently as the below example. That is, the LIKE predicate is added as a filter to exclude the wrong data which can be added in a collation with expansion.

s LIKE 'abc%' → s ≥ 'abc' AND s < 'abd' and s LIKE 'abc%' (if using strictly the English alphabet)

Prefix Index and Collation Expansion

A prefix index can be created on the collation without expansion; however, it cannot be created on the column which has the collation with expansion.

CREATE TABLE tbl (col1 VARCHAR(200) COLLATE utf8_ja_exp);
CREATE INDEX idx_tbl_col1 on tbl(col1(5));

ERROR: before ' ; '
Prefix index is not allowed on attribute 'col1' (has collation with expansions).

Index Covering

Covering index scan is query optimization, in which if all values in query can be computed using only the values found in the index, without requiring additional row lookup in heap file. For more information, see Covering Index.

In the collation without casing, for two strings values, 'abc' and 'ABC', only one value is stored in the index(this is either 'abc' or 'ABC' depending which one was inserted first). As a result, the incorrect result may happen when at least two different strings produce the same sort key in a given collation. For this reason, for all UTF-8 collations with strength level less than 4 (quaternary), the index covering query optimization is disabled.

This is controlled by strength="tertiary/quaternary" in <strength> tag of collation definition in LDML. It should be considered to set this level as maximum strength, because the quaternary strength level requires not only more memory space and bigger size of the shared library file, but also string-comparison time.

For more information about collations, see Collation.

Summary of CUBRID Features for Each Collation

Collation	LIKE condition kept after rewrite to range	Allows index covering	Allows prefix index
iso88591_bin	No	Yes	Yes
iso88591_en_cs	No	Yes	Yes
iso88591_en_ci	Yes	No	Yes
utf8_bin	No	Yes	Yes
euckr_bin	No	Yes	Yes
utf8_en_cs	No	Yes	Yes
utf8_en_ci	Yes	No	Yes
utf8_tr_cs	No	Yes	Yes
utf8_ko_cs	No	Yes	Yes
utf8_gen	No	Yes	Yes
utf8_gen_ai_ci	Yes	No	Yes
utf8_gen_ci	Yes	No	Yes
utf8_de_exp_ai_ci	Yes	No	No
utf8_de_exp	Yes	No	No
utf8_es_cs	No	Yes	Yes
utf8_fr_exp_ab	Yes	No	No
utf8_ja_exp	Yes	No	No
utf8_ja_exp_cbm	Yes	No	No
utf8_km_exp	Yes	No	No
utf8_ko_cs_uca	No	Yes	Yes
utf8_tr_cs_uca	No	Yes	Yes
utf8_vi_cs	No	Yes	Yes

Viewing Collation Information¶

To view the collation information, use CHARSET(), COLLATION() and COERCIBILITY() functions.

The information of the database collation can be shown on db_collation system view or SHOW COLLATION.

CUBRID_CHARSET¶

• by default, use CUBRID_CHARSET=en_US; this gives best performance.
• using UTF-8 locale will increase storage requirement of fixed char(CHAR) by 4 times; using EUC-KR increases storage 3 times.
• if user string literals have different charset and collation from system, query strings will grow as the string literals are decorated with them.
• if localized (non-ASCII) characters will be used for identifiers, then use an .utf8 locale
• once established the UTF-8 charset for CUBRID_CHARSET, it is best to use a LDML locale (this ensures that identifier names containing most Unicode characters are correctly cased).
• setting a locale affects also conversion functions(intl_date_lang, intl_number_lang).
• when you set CUBRID_CHARSET, there should be no concern on charset and collation of string-literals or user tables columns; all of them can be changed at run-time (with CAST() in queries) or ALTER .. CHANGE for a permanent change.

CHAR and VARCHAR¶

• generally, use VARCHAR if there are large variations in actual number of characters in user data.
• CHAR type is fixed length type. Therefore, Even if you store only English character in CHAR type, it requires 4 bytes storage in UTF-8 and 3 bytes in EUC-KR.
• the precision of columns refers to the number of characters (glyphs).
• after choosing precision, charset and collation should be set according to most used scenarios.

Choosing charset¶

• even if your text contains non-ASCII character, use utf8 or euckr charsets only if application requires character counting, inserting, replacing
• for CHAR data, the main concern should storage requirement (4x or utf8, 3x for euckr)
• for both CHAR and VARCHAR data, there is some overhead when inserting/updating data: counting the precision (number of characters) of each instance is more consuming for non-ISO charsets.
• in queries, charset of expressions may be converted using CAST() operator.

Choosing collation¶

• if no collation dependent operations are performed (string searching, sorting, comparisons, casing), than choose binary collation for that charset
• collation may be easily overridden using CAST() operator, and COLLATE modifier (in 9.1 version) if charset is unchanged between original charset of expression and the new collation.
• collation controls also the casing rules of strings
• collations with expansions are slower, but are more flexible and they perform whole-word sorting

Normalization¶

• if your client applications send text data to CUBRID in decomposed form, then configure unicode_input_normalization = yes, so that CUBRID re-composes it and handles it in composed form
• if your client "knows" to handle data only in decomposed form, than set unicode_output_normalization = yes, so that CUBRID always sends in decomposed form.
• if the client "knows" both forms, then leave unicode_output_normalization = no

CAST vs COLLATE¶

• when building statements, the CAST() operator is more costly than COLLATE modifier (even more when charset conversion from ISO-88591-1 to UTF-8 is implied)
• COLLATE modifier does not add an additional execution operator; using COLLATE modifier should enhance execution speed over using CAST() operator.
• COLLATE modifier can be used only when charset is not changed