InterBase Collation Kits

Character Repertoire

A character repertoire is a set of characters used for a particular culture. For instance, English uses A, B, C … Z. French uses A, Á, À, B, C, Ç, D … Z.

Character Set

A character set is a collection of characters that includes at least one character repertoire. For instance, ISO Latin 1 is a character set that includes the English repertoire and the French repertoire.

Form of Use

A form of use is the mapping of a character set to a particular computer based representation. It includes the numerical value assigned to a particular character, and the way to encode the value into bits for computer use. Examples of encoding include single byte, multi-byte sequence, 16-bit value, etc.

Collation

A collation is a method of ordering sequences of characters in a culturally acceptable fashion.

Character

A character is a particular member of a character repertoire. Characters can change “form of use” (e.g.: encoding) but never meaning.

Narrow Character – NCHAR

A narrow character set represents text as a sequence of bytes, each byte representing a unique character.

typedef unsigned char NCHAR;

ISO Latin1, DOS437, and similar character sets are represented as NCHAR.

Multi-byte Character – MBCHAR

A multi-byte character set represents text as a sequence of bytes, with some byte-values indicating that one or more of the following bytes determine the next character.

typedef unsigned char MBCHAR;

SJIS, EUC-J, BIG-5, and similar character sets are represented as MBCHAR.

Wide Character – WCHAR

A method of representing a character set as a sequence of integers, in which the integer is larger than an 8-bit byte. Typically, a platform specific WCHAR implementation matches the integer size for the platform.

InterBase defines WCHAR as an unsigned short – a 16-bit value.

typedef unsigned short WCHAR;

InterBase does not fully support character sets in WCHAR format. It does not swap bytes between platforms of different integer architectures (e.g.: little-end-ian and big-end-ian platforms). This limitation can create problems when the client and server have different integer architectures.

InterBase uses WCHAR as an internal processing format – InterBase will typically convert a MBCHAR string to WCHAR for text matching functions.

UNICODE, in WCHAR format, is used internally for charset conversion functions.

Character Set

An InterBase character set consists of a SQL character set and a SQL Form of Use. For instance, the InterBase character Set ISO-8859-1 is precisely called: “The set of characters defined by ISO Standard 8859-1, encoded by the values defined in ISO standard 8859-1, having each value represented by a single 8-bit byte”.

InterBase character sets are generally defined by national standards or industry standards. Examples: the International Standards Organization defines ISO-8859-1, and Microsoft defines Windows1252.

InterBase is precise in following defined standards. Windows 1252 is a superset of ISO-8859-1 (commonly known as Latin-1). In InterBase, WIN1252 and LATIN_1 are two separate character sets because Windows 1252 defines characters in positions that Latin-1 specifically states are “not a character”.

Text Type

An InterBase text type could also be termed a “locale”. It consists of all culturally significant information about processing text. This includes: character set, representation (form of use), collation method, comparison method, and uppercasing method.

Typical “locale” information also includes such methods as conversion of numeric values to string values, time representation, and date representation. These methods and formats are NOT included in the InterBase Text Type.

Transliteration

A transliteration occurs when characters in one InterBase character set are converted to another character set. Note that this process occurs when changing from one SQL “form of use” to another. InterBase terms the conversion from Unicode in the multi-byte FSS format to Unicode in WCHAR format a transliteration. The conversion from DOS437 to ISO-8859-1 is also a transliteration.

InterBase transliterations are “precise.” InterBase will report a transliteration error if the character in the input set does not have an exact representation in the output set. None of the existing set of InterBase transliterations will transliterate a “missing character” to another character.

This restriction provides the ability to perform “round-trip conversions”. A conversion is “round-trip” if it converts from character set A to character set B, then back to A, producing the original sequence of characters in the original representation.

InterBase transliterations preserve “character fidelity”.

Custom transliterations can perform alternate conversions for “missing characters.” For instance, a custom transliteration could convert the character <ae> to the string “ae” when the character <ae> does not exist in the destination character set.

Charset

A class implementing the InterBase charset interface must provide an internal name for the character set and:

• The value for the SPACE character
• The length of the SPACE character, in bytes
• Max & min bytes per character
• A method to convert from the character set to UNICODE in WCHAR
• A method to convert from UNICODE in WCHAR to the character set

Charsets are defined by the structure charset in intlobj.h. The structures defined in intlobj.h are explained starting in Texttype objects.

Texttype

A class implementing the InterBase texttype interface must provide an internal name for the texttype and functions that:

• Calculate the key length for a given string length
• Calculate the collation key for a given string
• Compare two strings
• Convert a string to uppercase
• Convert a string to lowercase
• Convert a string to WCHAR format
• Fetch the next character in a string

Texttypes are defined by the structure texttype in intlobj.h.

Conversion

A class implementing the InterBase conversion interface must provide an internal name for the conversion and a function to convert FROM a charset TO another charset.

Conversions are defined by the structure csconvert in intlobj.h

Character Set Identifiers

Character set identifiers range from 0 to 255. The range from 250 to 255 is reserved for user implementations. All numbers below 250 must be registered with InterBase support to prevent conflicts with other vendors of InterBase international objects.

typedef unsigned char   CHARSETID;

CHARSET ID 127 is reserved for internal InterBase use.

Texttype identifiers

A texttype is identified by a 16-bit value. The most-significant byte is the collation identifier (COLLATIONID). The least significant byte is the character set identifier (CHARSETID).

Collation IDs range from 0 to 254. Collation identifiers from 250 to 254 are reserved for user-defined collations. Value 255 is not supported. If you use a collation identifier below 250, you must register it with InterBase support to avoid conflicts with other vendors of InterBase international objects. By convention Collation ID 0 is the default collation for a given character set. The default collation for a character set is binary collation, where characters are ordered by the binary value specified by their form of use.

typedef unsigned char   COLLATIONID;
typedef unsigned short  TEXTTYPEID;

#define GET_CHARSETID(t)     ((t) & 0xFF)
#define GET_COLLATIONID(t)   (((t) >> 8) & 0xFF)

Collation identifiers are unique within a character set. Texttype ID’s are unique across the database.

Symbolic Names for Collation ID’s

Collation names are also defined in a system table. Retrieving them requires a more complicated query

SELECT RDB$COLLATION_NAME, RDB$COLLATION_ID,
RDB$CHARACTER_SET_ID,
RDB$COLLATION_ID * 256 + RDB$CHARACTER_SET_ID
AS TEXTTYPEID
FROM RDB$COLLATIONS;

RDB$COLLATIONS stores the collation name, the collation identifier, and the character set identifier for each supported collation. The TEXTTYPEID is calculated from the COLLATION_ID and the CHARACTER_SET_ID.

COLLATION_NAME must be unique in a database. Each texttype has a unique collation name. Aliases are not supported for collation names.

Naming Conventions

Collation names often follow the convention XX_yy.

• XX is the two-letter Language code, per ISO Standard 639 (ftp://ftp.std.com/obi/Standards/ISO/ISO_639).
• yy is the two-letter Country code, per ISO Standard 3166 (http://www.niso.org/3166.html).

Thus, DE_DE is the collation name for German as used in Germany. FR_FR is French as used in France, and FR_CA is French as used in Canada.

InterBase collations that emulate Paradox or dBASE collations have names like “PDOXxxxx”, “PXWxxx” or “DBxxxx”. The names should match the names used in Paradox and dBASE.

A special collation exists for each character set. This collation has the same name as the character set and implements binary collation for the character set. Binary collation sorts the character set by the numeric codes used to represent the characters.

Character set names follow the standard that defines them (for example: WIN1252, DOS437). Character set alias names support the differences in these names between platforms (for example: WIN1252, WIN_1252).

GDSINTL Libraries

Gdsintl and gdsintl2 are platform-dependant shared-object libraries. The suffix for the filename is platform dependant.

The implementation of the library is platform-dependant. Libraries must be recompiled when moved to a new platform.

Some platforms do not support shared-object libraries. Novell NLM is an example. For those platforms, the entire gdsintl library is linked with the engine. User-defined international objects cannot be added to InterBase on platforms that do not support shared-object libraries.

The InterBase installation procedure places gdsintl in the intl, bin or lib directory in the InterBase root directory. gdsintl2 must be stored in the same directory as gdsintl. The location of the gdsintl library is dependant on the platform and InterBase version.

Single Entry Point

Each international library has a single entry point, which is LD_lookup for gdsintl, and LD_lookup2 for gdsintl. Aside from the difference in entry point names, the libraries are functionally identical.

Historical note: Early versions of IBM’s AIX platform supported only one entry point in shared-object libraries. That limitation is the reason for limiting the lookup function to the single entry point for InterBase international libraries.

Historical note: The Apollo platform required that identifiers be unique across all shared-object libraries loaded by a program. That limitation is the reason for requiring the different entry point names in the two libraries.

The LD2_lookup Function

The lookup function for the gdsintl2 library is passed the following:

• Type of object to lookup
• Numerical identifier to lookup

It returns

• 1 if object was not found
• 0 if object was found and a pointer to a function used to initialize the object

USHORT LD2_lookup (
USHORT        objtype,
FPTR_SHORT    *fun,
SSHORT        parm1,
SSHORT        parm2)

Objtype is one of type_texttype, type_charset, or type_csconvert. Any other input is an error.

An example LD2_lookup function is provided in ld2.c

Functions to Perform Operations on Text

The first parameter of all functions implemented within the texttype is a pointer to the data structure initialized by the initialization function. This pointer provides access to the data tables: texttype_collation_table, texttype_toupper_table, etc. Functions can be shared among different texttypes implemented in the same gdsintl2 shared library. The result of the function is determined by the data tables, which are specific to a texttype.

The comments in lc_example.c provide more detail on each function.

Collation Keys

A collation key maps a string with a non-default collation to a set of bytes that can be compared with unsigned-byte comparison preserving the culturally correct collation order. InterBase stores collation keys in indexes. Index keys are always compared with unsigned byte comparisons, starting with the high order byte and continuing until a difference or the end of the key is found. When creating an index key, InterBase transforms values that do not produce correct ordering using unsigned byte comparisons to values that do.

The mapping from a string to a collation key can be irreversible. The original string is never recreated from the collation key.

When writing a textype_fn_string_to_key function, the resulting key must consist of unsigned bytes.

In a binary collation, the collation key is identical to the string.

Collation Keys versus Comparison

A texttype must provide functions for string comparison and creating collation keys. String comparison is used for equality and inequality Boolean evaluations. Collation keys are used in indexes. Comparing strings and comparing collation keys must produce identical results.

The two functions have a significant difference. Index keys have a fixed maximum length, currently 254 bytes. String comparison must be able to handle strings of unlimited length.

Ignore Trailing Spaces

SQL requires that trailing spaces be ignored when comparing two strings of different lengths. It is important that both the key creation function and the compare function ignore trailing spaces on strings.

Monocase operations

A texttype must contain functions to implement the SQL UPPER(x) and LOWER(x) functions. The result of UPPER & LOWER is culturally specific, and the rules are determined by the collation sequence defined with the column.

The to_upper and to_lower functions can be implemented using data tables or directly in code.

InterBase does not currently support the SQL LOWER(x) function, however it is necessary to implement it in each defined texttype.

Form of Use Changes

A texttype contains functions for changing the “form of use” of a character string from NCHAR or MBCHAR to WCHAR. These functions, strictly speaking, belong with the charset object. A design decision in the Internationalization architecture placed them with texttype.

Text Matching Functions

Entries exist in texttype for custom implementation of SQL LIKE, CONTAINS, and GDML MATCHING functions. The InterBase engine will fill these entries with internal function pointers after the texttype initialization function is called. Your driver should not initialize these function pointers.

Debugging a Collation Driver

Debugging a collation driver should be done before making it into a GdsIntl2 driver. While the collation driver is a standalone entity, it can print the collation keys it creates to check for correctness.

After installing the driver, sort and index several tables, compare indexed and non-indexed strings and perform UPPER() operations to ensure the driver is working properly.

The example test file is cs_example.sql.

Example

The example texttype driver lc_example.c implements a simple version of English Title Sort Order. When titles are sorted, articles such as “The”, “An”, or “A” at the beginning of the title are ignored. Thus “The Wind and the Lion” sorts as if it were written “Wind and the Lion”.

In this driver, the Collation Key created is simply the input string, minus any articles at the beginning of the string. This collation does not change the default ordering of accented characters or upper and lower case characters.

Conversion API

The conversion object has a single method, the function that converts from one character set to another.

static USHORT     conversion_function (
CSCONVERT   obj,
BYTE       *dest_ptr,
USHORT      dest_len,
BYTE       *src_ptr,
USHORT      src_len,
SSHORT     *err_code,
USHORT     *err_position);

Returns: The length of the converted string, in bytes.

If dest_ptr is NULL, then the routine returns a maximum estimate of the space required for the output string if an input string of src_len bytes is converted.

Error Returns from Conversion Function

The conversion function can return errors to the engine. Generally the engine will report these to the user as a “transliteration error”. The error code is returned via *err_code. The position in the input string where the error was detected is returned via *err_position.

Addressed by FROM charsetid & TO charsetid

Unlike character sets and collations, conversions have no symbolic names. A conversion is identified by the charsetID is it converting from and the charsetID it is converting to. An InterBase aficionado will recognize the similarity to blob filters.

If no direct conversion object has been created to convert from charset A to charset B, the engine converts charset A to UNICODE and then UNICODE to charset B.

Example

In the sample code provided, cv_example.c is an example of a conversion object. Conversion functions are generally table driven. . The example contains several different functions. The functions are code, and produce a specific conversion when the the data table for that conversion is given.

For conversion tables, check files at www.unicode.org

Two Conversion Objects

Every charset must contain two conversion definitions: a conversion from Unicode to the charset and a conversion to Unicode from the charset. All InterBase character sets must be able to convert themselves to and from Unicode.

When the engine cannot find a direct conversion between two character sets, it uses the converters here. Unicode is the intermediate conversion step.

One Texttype Object

The SQL standard requires that all character sets have a default collation. By convention in InterBase, the default collation for a character set has the same name as the character set. It performs a collation in binary, or codepoint order.

The character set default texttype object is loaded when the character set is loaded. It must be defined separately in the gdsintl2 library.

The SPACE character

The SQL standard requires that all character sets define a “space character”. This character is used to pad strings assigned to CHAR(n) columns to the length of the column. Trailing space characters are ignored in all string comparison operations.

The overwhelming majority of modern character sets use 0x20 or 0x0020 for the space character. InterBase character sets follow that rule, except for character set OCTETS. OCTETS instantiates binary byte data, rather than character data, and uses 0x00 for the space character.

Example

The example character set in cs_example.c is identical to the InterBase provided ISO_LATIN_1 character set.

The critical structures in a character set definition are the tables that map the character set to and from Unicode. InterBase’s mapping tables were derived from the tables provided by Unicode, Inc. at their website - www.unicode.org

Backup/Restore

InterBase will backup and restore user defined character sets and collations, and data in columns defined on them without problems.

If you restore a backup file with that includes user defined international objects on a server other than the one on which it was backed up, you must ensure that the character sets and collations referenced in the backup file exist on the destination server. The CHARSETIDs and COLLATIONIDs must be identical on the two servers.

Testing a GDSINTL2

When you test a new GDSINTL2, you must be aware that the engine does not release a shared library until the engine itself is unloaded. Each time you update your gdsintl2, you must shut down the server, install the new gdsintl2, and reload the server.

Additional Shared Libraries

The gdsintl2 shared-image can not require any shared libraries that are not already loaded by InterBase. In particular, a gdsintl2 shared-library built with Borland C++Builder will require VCL libraries that InterBase does not load. You can build the library using C++Builder command line tools; just don't build the DLL within the visual environment.

If the gdsintl2 shared library references shared libraries that were not loaded during server startup, the engine will fail to load the gdsintl2 shared-library. The error message for this is cryptic: “Implementation of text subtype %d not located.” Because the gdsintl2 library is optional, the engine does not consider its absence an error. The error occurs when the engine attempts to interpret data stored in a format defined in gdsintl2.

Platform Provided Internationalization functions

Many platforms provide internationalization libraries that perform the functions needed in gdsintl2. For instance, JAVA provides the Collator class; Windows provides CompareString and similar functions.

InterBase recommends against using a platform provided Internationalization library as it may:

• be platform specific. Moving the database to another platform will require re-implementation of the gdsintl2 library and may result in a different behavior if the two platform implementations are not precisely identical.
• be OS version specific. Updating your OS may install a new version of the library – which may perform differently. As the key values are stored in InterBase indices your database will not operate correctly until all indices are rebuilt.
• limit input string size. InterBase requires that comparison of Collation Keys and String Comparison return identical results – String Comparison would not be able to use the Collation Key function if it has a limit on input string size.
• not be thread safe. Many platforms use the process-wide locale setting for the international library. The texttype implementation would then have to set the process locale before each text operation. This may result in other, unintended, differences in InterBase behavior.
• have poor performance. An OS collation key function that loads data tables from disk each time will greatly reduce database performance.
• require a library not loaded by the InterBase server If the library isn’t already loaded by the InterBase server, your gdsintl2 library will not be able to access the OS provided functions.

Signed and Unsigned values

A CHARSETID and TEXTTYPEID are unsigned values. However, the LD_lookup interface between the engine and the gdsintl library is defined as a signed short. You must use a C cast to refer to the value as an unsigned short inside of your gdsintl2 module.

The error given when the engine is unable to locate a texttype is “Implementation of text subtype %d not located.” – the TEXTTYPEID / CHARSETID value is printed as a signed value instead of unsigned.

InterBase Source Files

The files provided in the InterBase collation kit were derived from InterBase source files. InterBase source files are best viewed with tab-stops set to 4.

InterBase was written in Kernighan &Richie C, and gradually moved toward ANSI C. Some inconsistencies remain; for example, some function definitions have prototypes, some do not.