Debian Codegraph @

ext/icu/README.txt

raw
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.