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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19# There is nothing to test if REINDEX is disable for this build. 20# 21ifcapable {!reindex} { 22 finish_test 23 return 24} 25 26# Basic sanity checks. 27# 28do_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} {} 37integrity_check reindex-1.2 38do_test reindex-1.3 { 39 execsql { 40 REINDEX t1; 41 } 42} {} 43integrity_check reindex-1.4 44do_test reindex-1.5 { 45 execsql { 46 REINDEX i1; 47 } 48} {} 49integrity_check reindex-1.6 50do_test reindex-1.7 { 51 execsql { 52 REINDEX main.t1; 53 } 54} {} 55do_test reindex-1.8 { 56 execsql { 57 REINDEX main.i1; 58 } 59} {} 60do_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# 69do_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} 92do_test reindex-2.2 { 93 execsql { 94 SELECT b FROM t2 ORDER BY b; 95 } 96} {BCDE bcd ABCD abc} 97do_test reindex-2.3 { 98 execsql { 99 SELECT c FROM t2 ORDER BY c; 100 } 101} {abc ABCD bcd BCDE} 102do_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# 111do_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} 119ifcapable {integrityck} { 120 do_test reindex-2.5.1 { 121 string equal ok [execsql {PRAGMA integrity_check}] 122 } {0} 123} 124do_test reindex-2.6 { 125 execsql { 126 REINDEX c2; 127 SELECT a FROM t2 ORDER BY a; 128 } 129} {bcd abc BCDE ABCD} 130do_test reindex-2.7 { 131 execsql { 132 REINDEX t1; 133 SELECT a FROM t2 ORDER BY a; 134 } 135} {bcd abc BCDE ABCD} 136do_test reindex-2.8 { 137 execsql { 138 REINDEX c1; 139 SELECT a FROM t2 ORDER BY a; 140 } 141} {ABCD BCDE abc bcd} 142integrity_check reindex-2.8.1 143 144# Try to REINDEX an index for which the collation sequence is not available. 145# 146do_test reindex-3.1 { 147 sqlite3 db2 test.db 148 catchsql { 149 REINDEX c1; 150 } db2 151} {1 {no such collation sequence: c1}} 152do_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 {}} 161do_test reindex-3.3 { 162 catchsql { 163 REINDEX; 164 } db2 165} {1 {no such collation sequence: c2}} 166 167do_test reindex-3.99 { 168 db2 close 169} {} 170 171finish_test 172