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 19 # There is nothing to test if REINDEX is disable for this build. 20 # 21 ifcapable {!reindex} { 22 finish_test 23 return 24 } 25 26 # Basic sanity checks. 27 # 28 do_test reindex-1.1 { 29 execsql { 30 CREATE TABLE t1(a,b); 31 INSERT INTO t1 VALUES(1,2); 32 INSERT INTO t1 VALUES(3,4); 33 CREATE INDEX i1 ON t1(a); 34 REINDEX; 35 } 36 } {} 37 integrity_check reindex-1.2 38 do_test reindex-1.3 { 39 execsql { 40 REINDEX t1; 41 } 42 } {} 43 integrity_check reindex-1.4 44 do_test reindex-1.5 { 45 execsql { 46 REINDEX i1; 47 } 48 } {} 49 integrity_check reindex-1.6 50 do_test reindex-1.7 { 51 execsql { 52 REINDEX main.t1; 53 } 54 } {} 55 do_test reindex-1.8 { 56 execsql { 57 REINDEX main.i1; 58 } 59 } {} 60 do_test reindex-1.9 { 61 catchsql { 62 REINDEX bogus 63 } 64 } {1 {unable to identify the object to be reindexed}} 65 66 # Set up a table for testing that includes several different collating 67 # sequences including some that we can modify. 68 # 69 do_test reindex-2.1 { 70 proc c1 {a b} { 71 return [expr {-[string compare $a $b]}] 72 } 73 proc c2 {a b} { 74 return [expr {-[string compare [string tolower $a] [string tolower $b]]}] 75 } 76 db collate c1 c1 77 db collate c2 c2 78 execsql { 79 CREATE TABLE t2( 80 a TEXT PRIMARY KEY COLLATE c1, 81 b TEXT UNIQUE COLLATE c2, 82 c TEXT COLLATE nocase, 83 d TEST COLLATE binary 84 ); 85 INSERT INTO t2 VALUES('abc','abc','abc','abc'); 86 INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD'); 87 INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd'); 88 INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE'); 89 SELECT a FROM t2 ORDER BY a; 90 } 91 } {bcd abc BCDE ABCD} 92 do_test reindex-2.2 { 93 execsql { 94 SELECT b FROM t2 ORDER BY b; 95 } 96 } {BCDE bcd ABCD abc} 97 do_test reindex-2.3 { 98 execsql { 99 SELECT c FROM t2 ORDER BY c; 100 } 101 } {abc ABCD bcd BCDE} 102 do_test reindex-2.4 { 103 execsql { 104 SELECT d FROM t2 ORDER BY d; 105 } 106 } {ABCD BCDE abc bcd} 107 108 # Change a collating sequence function. Verify that REINDEX rebuilds 109 # the index. 110 # 111 do_test reindex-2.5 { 112 proc c1 {a b} { 113 return [string compare $a $b] 114 } 115 execsql { 116 SELECT a FROM t2 ORDER BY a; 117 } 118 } {bcd abc BCDE ABCD} 119 ifcapable {integrityck} { 120 do_test reindex-2.5.1 { 121 string equal ok [execsql {PRAGMA integrity_check}] 122 } {0} 123 } 124 do_test reindex-2.6 { 125 execsql { 126 REINDEX c2; 127 SELECT a FROM t2 ORDER BY a; 128 } 129 } {bcd abc BCDE ABCD} 130 do_test reindex-2.7 { 131 execsql { 132 REINDEX t1; 133 SELECT a FROM t2 ORDER BY a; 134 } 135 } {bcd abc BCDE ABCD} 136 do_test reindex-2.8 { 137 execsql { 138 REINDEX c1; 139 SELECT a FROM t2 ORDER BY a; 140 } 141 } {ABCD BCDE abc bcd} 142 integrity_check reindex-2.8.1 143 144 # Try to REINDEX an index for which the collation sequence is not available. 145 # 146 do_test reindex-3.1 { 147 sqlite3 db2 test.db 148 catchsql { 149 REINDEX c1; 150 } db2 151 } {1 {no such collation sequence: c1}} 152 do_test reindex-3.2 { 153 proc need_collate {collation} { 154 db2 collate c1 c1 155 } 156 db2 collation_needed need_collate 157 catchsql { 158 REINDEX c1; 159 } db2 160 } {0 {}} 161 do_test reindex-3.3 { 162 catchsql { 163 REINDEX; 164 } db2 165 } {1 {no such collation sequence: c2}} 166 167 do_test reindex-3.99 { 168 db2 close 169 } {} 170 171 finish_test 172
