# 2004 November 5 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # This file implements tests for the REINDEX command. # # $Id: reindex.test,v 1.3 2005/01/27 00:22:04 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # There is nothing to test if REINDEX is disable for this build. # ifcapable {!reindex} { finish_test return } # Basic sanity checks. # do_test reindex-1.1 { execsql { CREATE TABLE t1(a,b); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(3,4); CREATE INDEX i1 ON t1(a); REINDEX; } } {} integrity_check reindex-1.2 do_test reindex-1.3 { execsql { REINDEX t1; } } {} integrity_check reindex-1.4 do_test reindex-1.5 { execsql { REINDEX i1; } } {} integrity_check reindex-1.6 do_test reindex-1.7 { execsql { REINDEX main.t1; } } {} do_test reindex-1.8 { execsql { REINDEX main.i1; } } {} do_test reindex-1.9 { catchsql { REINDEX bogus } } {1 {unable to identify the object to be reindexed}} # Set up a table for testing that includes several different collating # sequences including some that we can modify. # do_test reindex-2.1 { proc c1 {a b} { return [expr {-[string compare $a $b]}] } proc c2 {a b} { return [expr {-[string compare [string tolower $a] [string tolower $b]]}] } db collate c1 c1 db collate c2 c2 execsql { CREATE TABLE t2( a TEXT PRIMARY KEY COLLATE c1, b TEXT UNIQUE COLLATE c2, c TEXT COLLATE nocase, d TEST COLLATE binary ); INSERT INTO t2 VALUES('abc','abc','abc','abc'); INSERT INTO t2 VALUES('ABCD','ABCD','ABCD','ABCD'); INSERT INTO t2 VALUES('bcd','bcd','bcd','bcd'); INSERT INTO t2 VALUES('BCDE','BCDE','BCDE','BCDE'); SELECT a FROM t2 ORDER BY a; } } {bcd abc BCDE ABCD} do_test reindex-2.2 { execsql { SELECT b FROM t2 ORDER BY b; } } {BCDE bcd ABCD abc} do_test reindex-2.3 { execsql { SELECT c FROM t2 ORDER BY c; } } {abc ABCD bcd BCDE} do_test reindex-2.4 { execsql { SELECT d FROM t2 ORDER BY d; } } {ABCD BCDE abc bcd} # Change a collating sequence function. Verify that REINDEX rebuilds # the index. # do_test reindex-2.5 { proc c1 {a b} { return [string compare $a $b] } execsql { SELECT a FROM t2 ORDER BY a; } } {bcd abc BCDE ABCD} ifcapable {integrityck} { do_test reindex-2.5.1 { string equal ok [execsql {PRAGMA integrity_check}] } {0} } do_test reindex-2.6 { execsql { REINDEX c2; SELECT a FROM t2 ORDER BY a; } } {bcd abc BCDE ABCD} do_test reindex-2.7 { execsql { REINDEX t1; SELECT a FROM t2 ORDER BY a; } } {bcd abc BCDE ABCD} do_test reindex-2.8 { execsql { REINDEX c1; SELECT a FROM t2 ORDER BY a; } } {ABCD BCDE abc bcd} integrity_check reindex-2.8.1 # Try to REINDEX an index for which the collation sequence is not available. # do_test reindex-3.1 { sqlite3 db2 test.db catchsql { REINDEX c1; } db2 } {1 {no such collation sequence: c1}} do_test reindex-3.2 { proc need_collate {collation} { db2 collate c1 c1 } db2 collation_needed need_collate catchsql { REINDEX c1; } db2 } {0 {}} do_test reindex-3.3 { catchsql { REINDEX; } db2 } {1 {no such collation sequence: c2}} do_test reindex-3.99 { db2 close } {} finish_test