| 1 |
This directory contains source code for the SQLite "ICU" extension, an |
| 2 |
integration of the "International Components for Unicode" library with |
| 3 |
SQLite. Documentation follows. |
| 4 |
|
| 5 |
1. Features |
| 6 |
|
| 7 |
1.1 SQL Scalars upper() and lower() |
| 8 |
1.2 Unicode Aware LIKE Operator |
| 9 |
1.3 ICU Collation Sequences |
| 10 |
1.4 SQL REGEXP Operator |
| 11 |
|
| 12 |
2. Compilation and Usage |
| 13 |
|
| 14 |
3. Bugs, Problems and Security Issues |
| 15 |
|
| 16 |
3.1 The "case_sensitive_like" Pragma |
| 17 |
3.2 The SQLITE_MAX_LIKE_PATTERN_LENGTH Macro |
| 18 |
3.3 Collation Sequence Security Issue |
| 19 |
|
| 20 |
|
| 21 |
1. FEATURES |
| 22 |
|
| 23 |
1.1 SQL Scalars upper() and lower() |
| 24 |
|
| 25 |
SQLite's built-in implementations of these two functions only |
| 26 |
provide case mapping for the 26 letters used in the English |
| 27 |
language. The ICU based functions provided by this extension |
| 28 |
provide case mapping, where defined, for the full range of |
| 29 |
unicode characters. |
| 30 |
|
| 31 |
ICU provides two types of case mapping, "general" case mapping and |
| 32 |
"language specific". Refer to ICU documentation for the differences |
| 33 |
between the two. Specifically: |
| 34 |
|
| 35 |
http://www.icu-project.org/userguide/caseMappings.html |
| 36 |
http://www.icu-project.org/userguide/posix.html#case_mappings |
| 37 |
|
| 38 |
To utilise "general" case mapping, the upper() or lower() scalar |
| 39 |
functions are invoked with one argument: |
| 40 |
|
| 41 |
upper('abc') -> 'ABC' |
| 42 |
lower('ABC') -> 'abc' |
| 43 |
|
| 44 |
To access ICU "language specific" case mapping, upper() or lower() |
| 45 |
should be invoked with two arguments. The second argument is the name |
| 46 |
of the locale to use. Passing an empty string ("") or SQL NULL value |
| 47 |
as the second argument is the same as invoking the 1 argument version |
| 48 |
of upper() or lower(): |
| 49 |
|
| 50 |
lower('I', 'en_us') -> 'i' |
| 51 |
lower('I', 'tr_tr') -> 'ı' (small dotless i) |
| 52 |
|
| 53 |
1.2 Unicode Aware LIKE Operator |
| 54 |
|
| 55 |
Similarly to the upper() and lower() functions, the built-in SQLite LIKE |
| 56 |
operator understands case equivalence for the 26 letters of the English |
| 57 |
language alphabet. The implementation of LIKE included in this |
| 58 |
extension uses the ICU function u_foldCase() to provide case |
| 59 |
independent comparisons for the full range of unicode characters. |
| 60 |
|
| 61 |
The U_FOLD_CASE_DEFAULT flag is passed to u_foldCase(), meaning the |
| 62 |
dotless 'I' character used in the Turkish language is considered |
| 63 |
to be in the same equivalence class as the dotted 'I' character |
| 64 |
used by many languages (including English). |
| 65 |
|
| 66 |
1.3 ICU Collation Sequences |
| 67 |
|
| 68 |
A special SQL scalar function, icu_load_collation() is provided that |
| 69 |
may be used to register ICU collation sequences with SQLite. It |
| 70 |
is always called with exactly two arguments, the ICU locale |
| 71 |
identifying the collation sequence to ICU, and the name of the |
| 72 |
SQLite collation sequence to create. For example, to create an |
| 73 |
SQLite collation sequence named "turkish" using Turkish language |
| 74 |
sorting rules, the SQL statement: |
| 75 |
|
| 76 |
SELECT icu_load_collation('tr_TR', 'turkish'); |
| 77 |
|
| 78 |
Or, for Australian English: |
| 79 |
|
| 80 |
SELECT icu_load_collation('en_AU', 'australian'); |
| 81 |
|
| 82 |
The identifiers "turkish" and "australian" may then be used |
| 83 |
as collation sequence identifiers in SQL statements: |
| 84 |
|
| 85 |
CREATE TABLE aust_turkish_penpals( |
| 86 |
australian_penpal_name TEXT COLLATE australian, |
| 87 |
turkish_penpal_name TEXT COLLATE turkish |
| 88 |
); |
| 89 |
|
| 90 |
1.4 SQL REGEXP Operator |
| 91 |
|
| 92 |
This extension provides an implementation of the SQL binary |
| 93 |
comparision operator "REGEXP", based on the regular expression functions |
| 94 |
provided by the ICU library. The syntax of the operator is as described |
| 95 |
in SQLite documentation: |
| 96 |
|
| 97 |
<string> REGEXP <re-pattern> |
| 98 |
|
| 99 |
This extension uses the ICU defaults for regular expression matching |
| 100 |
behavior. Specifically, this means that: |
| 101 |
|
| 102 |
* Matching is case-sensitive, |
| 103 |
* Regular expression comments are not allowed within patterns, and |
| 104 |
* The '^' and '$' characters match the beginning and end of the |
| 105 |
<string> argument, not the beginning and end of lines within |
| 106 |
the <string> argument. |
| 107 |
|
| 108 |
Even more specifically, the value passed to the "flags" parameter |
| 109 |
of ICU C function uregex_open() is 0. |
| 110 |
|
| 111 |
|
| 112 |
2 COMPILATION AND USAGE |
| 113 |
|
| 114 |
The easiest way to compile and use the ICU extension is to build |
| 115 |
and use it as a dynamically loadable SQLite extension. To do this |
| 116 |
using gcc on *nix: |
| 117 |
|
| 118 |
gcc -fPIC -shared icu.c `pkg-config --libs --cflags icu-io` \ |
| 119 |
-o libSqliteIcu.so |
| 120 |
|
| 121 |
You may need to add "-I" flags so that gcc can find sqlite3ext.h |
| 122 |
and sqlite3.h. The resulting shared lib, libSqliteIcu.so, may be |
| 123 |
loaded into sqlite in the same way as any other dynamically loadable |
| 124 |
extension. |
| 125 |
|
| 126 |
As of version 3.48, it can be enabled in the canonical build process |
| 127 |
by passing one of --with-icu-config or --with-icu-ldflags to the |
| 128 |
configure script, optionally together with --enable-icu-collations. |
| 129 |
See the configure --help for more details. |
| 130 |
|
| 131 |
|
| 132 |
3 BUGS, PROBLEMS AND SECURITY ISSUES |
| 133 |
|
| 134 |
3.1 The "case_sensitive_like" Pragma |
| 135 |
|
| 136 |
This extension does not work well with the "case_sensitive_like" |
| 137 |
pragma. If this pragma is used before the ICU extension is loaded, |
| 138 |
then the pragma has no effect. If the pragma is used after the ICU |
| 139 |
extension is loaded, then SQLite ignores the ICU implementation and |
| 140 |
always uses the built-in LIKE operator. |
| 141 |
|
| 142 |
The ICU extension LIKE operator is always case insensitive. |
| 143 |
|
| 144 |
3.2 The SQLITE_MAX_LIKE_PATTERN_LENGTH Macro |
| 145 |
|
| 146 |
Passing very long patterns to the built-in SQLite LIKE operator can |
| 147 |
cause excessive CPU usage. To curb this problem, SQLite defines the |
| 148 |
SQLITE_MAX_LIKE_PATTERN_LENGTH macro as the maximum length of a |
| 149 |
pattern in bytes (irrespective of encoding). The default value is |
| 150 |
defined in internal header file "limits.h". |
| 151 |
|
| 152 |
The ICU extension LIKE implementation suffers from the same |
| 153 |
problem and uses the same solution. However, since the ICU extension |
| 154 |
code does not include the SQLite file "limits.h", modifying |
| 155 |
the default value therein does not affect the ICU extension. |
| 156 |
The default value of SQLITE_MAX_LIKE_PATTERN_LENGTH used by |
| 157 |
the ICU extension LIKE operator is 50000, defined in source |
| 158 |
file "icu.c". |
| 159 |
|
| 160 |
3.3 Collation Sequence Security |
| 161 |
|
| 162 |
Internally, SQLite assumes that indices stored in database files |
| 163 |
are sorted according to the collation sequence indicated by the |
| 164 |
SQL schema. Changing the definition of a collation sequence after |
| 165 |
an index has been built is therefore equivalent to database |
| 166 |
corruption. The SQLite library is well tested for robustness in |
| 167 |
the fact of database corruption. Database corruption may well |
| 168 |
lead to incorrect answers, but should not cause memory errors. |