gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/reindex.test (about) 1 # 2004 November 5 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 # This file implements regression tests for SQLite library. 12 # This file implements tests for the REINDEX command. 13 # 14 # $Id: reindex.test,v 1.4 2008/07/12 14:52:20 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix reindex 19 20 # There is nothing to test if REINDEX is disable for this build. 21 # 22 ifcapable {!reindex} { 23 finish_test 24 return 25 } 26 27 # Basic sanity checks. 28 # 29 do_test reindex-1.1 { 30 execsql { 31 CREATE TABLE t1(a,b); 32 INSERT INTO t1 VALUES(1,2); 33 INSERT INTO t1 VALUES(3,4); 34 CREATE INDEX i1 ON t1(a); 35 REINDEX; 36 } 37 } {} 38 integrity_check reindex-1.2 39 do_test reindex-1.3 { 40 execsql { 41 REINDEX t1; 42 } 43 } {} 44 integrity_check reindex-1.4 45 do_test reindex-1.5 { 46 execsql { 47 REINDEX i1; 48 } 49 } {} 50 integrity_check reindex-1.6 51 do_test reindex-1.7 { 52 execsql { 53 REINDEX main.t1; 54 } 55 } {} 56 do_test reindex-1.8 { 57 execsql { 58 REINDEX main.i1; 59 } 60 } {} 61 do_test reindex-1.9 { 62 catchsql { 63 REINDEX bogus 64 } 65 } {1 {unable to identify the object to be reindexed}} 66 67 # Set up a table for testing that includes several different collating 68 # sequences including some that we can modify. 69 # 70 do_test reindex-2.1 { 71 proc c1 {a b} { 72 return [expr {-[string compare $a $b]}] 73 } 74 proc c2 {a b} { 75 return [expr {-[string compare [string tolower $a] [string tolower $b]]}] 76 } 77 db collate c1 c1 78 db collate c2 c2 79 execsql { 80 CREATE TABLE t2( 81 a TEXT PRIMARY KEY COLLATE c1, 82 b TEXT UNIQUE COLLATE c2, 83 c TEXT COLLATE nocase, 84 d TEST COLLATE binary 85 ); 86 INSERT INTO t2 VALUES('abc','abc','abc','abc'); 87 INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD'); 88 INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd'); 89 INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE'); 90 SELECT a FROM t2 ORDER BY a; 91 } 92 } {bcd abc BCDE ABCD} 93 do_test reindex-2.2 { 94 execsql { 95 SELECT b FROM t2 ORDER BY b; 96 } 97 } {BCDE bcd ABCD abc} 98 do_test reindex-2.3 { 99 execsql { 100 SELECT c FROM t2 ORDER BY c; 101 } 102 } {abc ABCD bcd BCDE} 103 do_test reindex-2.4 { 104 execsql { 105 SELECT d FROM t2 ORDER BY d; 106 } 107 } {ABCD BCDE abc bcd} 108 109 # Change a collating sequence function. Verify that REINDEX rebuilds 110 # the index. 111 # 112 do_test reindex-2.5 { 113 proc c1 {a b} { 114 return [string compare $a $b] 115 } 116 execsql { 117 SELECT a FROM t2 ORDER BY a; 118 } 119 } {bcd abc BCDE ABCD} 120 ifcapable {integrityck} { 121 do_test reindex-2.5.1 { 122 string equal ok [execsql {PRAGMA integrity_check}] 123 } {0} 124 } 125 do_test reindex-2.6 { 126 execsql { 127 REINDEX c2; 128 SELECT a FROM t2 ORDER BY a; 129 } 130 } {bcd abc BCDE ABCD} 131 do_test reindex-2.7 { 132 execsql { 133 REINDEX t1; 134 SELECT a FROM t2 ORDER BY a; 135 } 136 } {bcd abc BCDE ABCD} 137 do_test reindex-2.8 { 138 execsql { 139 REINDEX c1; 140 SELECT a FROM t2 ORDER BY a; 141 } 142 } {ABCD BCDE abc bcd} 143 integrity_check reindex-2.8.1 144 145 # Try to REINDEX an index for which the collation sequence is not available. 146 # 147 do_test reindex-3.1 { 148 sqlite3 db2 test.db 149 catchsql { 150 REINDEX c1; 151 } db2 152 } {1 {no such collation sequence: c1}} 153 do_test reindex-3.2 { 154 proc need_collate {collation} { 155 db2 collate c1 c1 156 } 157 db2 collation_needed need_collate 158 catchsql { 159 REINDEX c1; 160 } db2 161 } {0 {}} 162 do_test reindex-3.3 { 163 catchsql { 164 REINDEX; 165 } db2 166 } {1 {no such collation sequence: c2}} 167 168 do_test reindex-3.99 { 169 db2 close 170 } {} 171 172 #------------------------------------------------------------------------- 173 foreach {tn wo} {1 "" 2 "WITHOUT ROWID"} { 174 reset_db 175 eval [string map [list %without_rowid% $wo] { 176 do_execsql_test 4.$tn.0 { 177 CREATE TABLE t0 ( 178 c0 INTEGER PRIMARY KEY DESC, 179 c1 UNIQUE DEFAULT NULL 180 ) %without_rowid% ; 181 INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5); 182 SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1; 183 } {1 2 3 4 5} 184 185 do_execsql_test 4.$tn.1 { 186 REINDEX; 187 } 188 189 do_execsql_test 4.$tn.2 { 190 SELECT c0 FROM t0 WHERE c1 IS NULL ORDER BY 1; 191 } {1 2 3 4 5} 192 193 do_execsql_test 4.$tn.3 { 194 SELECT c0 FROM t0 WHERE c1 IS NULL AND c0 IN (1,2,3,4,5); 195 } {1 2 3 4 5} 196 197 do_execsql_test 4.$tn.4 { 198 PRAGMA integrity_check; 199 } {ok} 200 }] 201 } 202 203 204 205 finish_test