Using collations to select case insensitive with FrontBase and significantly speed up queries

By: Tom Hume,July 18, 2001

Contributor: Eike Dierks

Introduction

Queries against database string fields frequently need to be performed case insensitive or against international character sets. This tech note seeks to demonstrate the use of case insensitive queries using collations in FrontBase.

There are two ways that this can be achieved. The default for WebObjects and most other SQL implementations, for example, is to compare the respective field UPPER() with the uppercased search string. However, the obvious disadvantage is that this function disables usage of indices in the database and thereby makes queries extremely slow. It effectively forces unnecessary full table scans.

Frontbase, fully incorporating the SQL92 standard, offers a much more efficient way to handle these types of queries using collations. Using collations you can define equivalency classes of characters. You can, for example, make lower and uppercase letters compare equal. You can also use collations to ignore differences in punctuations or to ease search for diacritic characters e.g. umlauts etc.

Queries based on collations take full advantage of the database indices, thereby speeding up queries.

How to create & use collations in Frontbase:

A default collation file for case insensitivity is already included with Frontbase in <install_path>/Frontbase/Collations/CaseInsensitive.coll1. You can modify it or build your own with the FBUnicodeManager Tool. An example of creating a case sensitive collation is outlined in our Users Guide.

The following example uses FBManager to demonstrate collations but these can be created just as easily using FBWebManager or the SQL92 command line tool. More details about the SQL92 way can be found in the FrontBase Users Guide (Chapter 12), which can be obtained from http://www.frontbase.com.

Firstly you need to import the collation file into the database:

• Open FBManager
• Select the 'Editor' tab from the management window which opens automatically when you start FBManager
• Select your 'Schemata'
• Go to Management->Create->Collation
• Assign a name to the new collation e.g. CaseInsensitive
• Select a file e.g. <install_path>/Frontbase/Collations/CaseInsensitive.coll1
• You should now see the Collation in your Schema Browser in the 'Editor' tab
• Commit (Cmnd+s) to save the changes

You now need to assign the collation to the data definition of the respective column/s:

• Select the required table in the 'Editor' tab and open the 'Definition editor' - either using Shift+Cmnd+O or directly from the 'Management' menu
• select the respective indexed column from the definition editor (single click) and open the 'Column inspector' from the management window
• Select your collation from the collation drop-down menu
• Commit to save the changes
• Close the windows (Cmnd+W)

Notes for WebObjects programming

You have to make sure that your queries use "like" instead of "caseInsensitiveLike" for queries. If you use caseInsensitiveLike (the default for WODisplayGroup) WebObjects still tries to mimic case insensitive matching by performing UPPER() rendering the above changes useless.

The easiest way to enable "like" is to use:

[displayGroup setDefaultStringMatchOperator:@"like"];

or

[WODisplayGroup setGlobalDefaultStringMatchOperator:@"like"];

However beware of matches performed in memory i.e. on cached tables, as WebObjects does not know about the collation in the database.

You can also configure the stringMatch operator for the WODisplayGroup with the queryOperator dictionary. Please note that this disables the defaultStringMatchFormat: for these fields i.e. you have to append a * to the query field manually if you want partial matches.

The effect of using collations in this way is unbelievable on performance. Queries that used to do full table scans because of using UPPER() now use indices and return immediately instead of taking seconds or longer.