gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/icu.test (about) 1 # 2007 May 1 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # 12 # $Id: icu.test,v 1.2 2008/07/12 14:52:20 drh Exp $ 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 ifcapable !icu&&!icu_collations { 19 finish_test 20 return 21 } 22 23 # Create a table to work with. 24 # 25 execsql {CREATE TABLE test1(i1 int, i2 int, r1 real, r2 real, t1 text, t2 text)} 26 execsql {INSERT INTO test1 VALUES(1,2,1.1,2.2,'hello','world')} 27 proc test_expr {name settings expr result} { 28 do_test $name [format { 29 lindex [db eval { 30 BEGIN; 31 UPDATE test1 SET %s; 32 SELECT %s FROM test1; 33 ROLLBACK; 34 }] 0 35 } $settings $expr] $result 36 } 37 38 ifcapable icu { 39 40 # Tests of the REGEXP operator. 41 # 42 test_expr icu-1.1 {i1='hello'} {i1 REGEXP 'hello'} 1 43 test_expr icu-1.2 {i1='hello'} {i1 REGEXP '.ello'} 1 44 test_expr icu-1.3 {i1='hello'} {i1 REGEXP '.ell'} 0 45 test_expr icu-1.4 {i1='hello'} {i1 REGEXP '.ell.*'} 1 46 test_expr icu-1.5 {i1=NULL} {i1 REGEXP '.ell.*'} {} 47 48 # Some non-ascii characters with defined case mappings 49 # 50 set ::EGRAVE "\xC8" 51 set ::egrave "\xE8" 52 53 set ::OGRAVE "\xD2" 54 set ::ograve "\xF2" 55 56 # That German letter that looks a bit like a B. The 57 # upper-case version of which is "SS" (two characters). 58 # 59 set ::szlig "\xDF" 60 61 # Tests of the upper()/lower() functions. 62 # 63 test_expr icu-2.1 {i1='HellO WorlD'} {upper(i1)} {HELLO WORLD} 64 test_expr icu-2.2 {i1='HellO WorlD'} {lower(i1)} {hello world} 65 test_expr icu-2.3 {i1=$::egrave} {lower(i1)} $::egrave 66 test_expr icu-2.4 {i1=$::egrave} {upper(i1)} $::EGRAVE 67 test_expr icu-2.5 {i1=$::ograve} {lower(i1)} $::ograve 68 test_expr icu-2.6 {i1=$::ograve} {upper(i1)} $::OGRAVE 69 test_expr icu-2.3 {i1=$::EGRAVE} {lower(i1)} $::egrave 70 test_expr icu-2.4 {i1=$::EGRAVE} {upper(i1)} $::EGRAVE 71 test_expr icu-2.5 {i1=$::OGRAVE} {lower(i1)} $::ograve 72 test_expr icu-2.6 {i1=$::OGRAVE} {upper(i1)} $::OGRAVE 73 74 test_expr icu-2.7 {i1=$::szlig} {upper(i1)} "SS" 75 test_expr icu-2.8 {i1='SS'} {lower(i1)} "ss" 76 77 do_execsql_test icu-2.9 { 78 SELECT upper(char(0xfb04,0xfb04,0xfb04,0xfb04)); 79 } {FFLFFLFFLFFL} 80 81 # In turkish (locale="tr_TR"), the lower case version of I 82 # is "small dotless i" (code point 0x131 (decimal 305)). 83 # 84 set ::small_dotless_i "\u0131" 85 test_expr icu-3.1 {i1='I'} {lower(i1)} "i" 86 test_expr icu-3.2 {i1='I'} {lower(i1, 'tr_tr')} $::small_dotless_i 87 test_expr icu-3.3 {i1='I'} {lower(i1, 'en_AU')} "i" 88 } 89 90 #-------------------------------------------------------------------- 91 # Test the collation sequence function. 92 # 93 do_test icu-4.1 { 94 execsql { 95 CREATE TABLE fruit(name); 96 INSERT INTO fruit VALUES('plum'); 97 INSERT INTO fruit VALUES('cherry'); 98 INSERT INTO fruit VALUES('apricot'); 99 INSERT INTO fruit VALUES('peach'); 100 INSERT INTO fruit VALUES('chokecherry'); 101 INSERT INTO fruit VALUES('yamot'); 102 } 103 } {} 104 do_test icu-4.2 { 105 execsql { 106 SELECT icu_load_collation('en_US', 'AmericanEnglish'); 107 SELECT icu_load_collation('lt_LT', 'Lithuanian'); 108 } 109 execsql { 110 SELECT name FROM fruit ORDER BY name COLLATE AmericanEnglish ASC; 111 } 112 } {apricot cherry chokecherry peach plum yamot} 113 114 115 # Test collation using Lithuanian rules. In the Lithuanian 116 # alphabet, "y" comes right after "i". 117 # 118 do_test icu-4.3 { 119 execsql { 120 SELECT name FROM fruit ORDER BY name COLLATE Lithuanian ASC; 121 } 122 } {apricot cherry chokecherry yamot peach plum} 123 124 #------------------------------------------------------------------------- 125 # Test that it is not possible to call the ICU regex() function with 126 # anything other than exactly two arguments. See also: 127 # 128 # http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/icu-regexp.patch?revision=34807&view=markup 129 # 130 ifcapable icu { 131 do_catchsql_test icu-5.1 { SELECT regexp('a[abc]c.*', 'abc') } {0 1} 132 do_catchsql_test icu-5.2 { 133 SELECT regexp('a[abc]c.*') 134 } {1 {wrong number of arguments to function regexp()}} 135 do_catchsql_test icu-5.3 { 136 SELECT regexp('a[abc]c.*', 'abc', 'c') 137 } {1 {wrong number of arguments to function regexp()}} 138 do_catchsql_test icu-5.4 { 139 SELECT 'abc' REGEXP 'a[abc]c.*' 140 } {0 1} 141 do_catchsql_test icu-5.5 {SELECT 'abc' REGEXP } {1 {incomplete input}} 142 do_catchsql_test icu-5.6 {SELECT 'abc' REGEXP, 1} {1 {near ",": syntax error}} 143 144 do_malloc_test icu-6.10 -sqlbody { 145 SELECT upper(char(0xfb04,0xdf,0xfb04,0xe8,0xfb04)); 146 } 147 } 148 149 # 2020-03-19 150 # The ESCAPE clause on LIKE takes precedence over wildcards 151 # 152 do_execsql_test idu-6.0 { 153 DROP TABLE IF EXISTS t1; 154 CREATE TABLE t1(id INTEGER PRIMARY KEY, x TEXT); 155 INSERT INTO t1 VALUES 156 (1,'abcde'), 157 (2,'abc_'), 158 (3,'abc__'), 159 (4,'abc%'), 160 (5,'abc%%'); 161 SELECT id FROM t1 WHERE x LIKE 'abc%%' ESCAPE '%'; 162 } {4} 163 do_execsql_test icu-6.1 { 164 SELECT id FROM t1 WHERE x LIKE 'abc__' ESCAPE '_'; 165 } {2} 166 167 finish_test