# 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.4 2008/07/12 14:52:20 drh 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