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