1: # 2005 January 11
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. The
12: # focus of this file is testing the CREATE INDEX statement.
13: #
14: # $Id: index2.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: # Create a table with a large number of columns
20: #
21: do_test index2-1.1 {
22: set sql {CREATE TABLE t1(}
23: for {set i 1} {$i<1000} {incr i} {
24: append sql "c$i,"
25: }
26: append sql "c1000);"
27: execsql $sql
28: } {}
29: do_test index2-1.2 {
30: set sql {INSERT INTO t1 VALUES(}
31: for {set i 1} {$i<1000} {incr i} {
32: append sql $i,
33: }
34: append sql {1000);}
35: execsql $sql
36: } {}
37: do_test index2-1.3 {
38: execsql {SELECT c123 FROM t1}
39: } 123
40: do_test index2-1.4 {
41: execsql BEGIN
42: for {set j 1} {$j<=100} {incr j} {
43: set sql {INSERT INTO t1 VALUES(}
44: for {set i 1} {$i<1000} {incr i} {
45: append sql [expr {$j*10000+$i}],
46: }
47: append sql "[expr {$j*10000+1000}]);"
48: execsql $sql
49: }
50: execsql COMMIT
51: execsql {SELECT count(*) FROM t1}
52: } 101
53: do_test index2-1.5 {
54: execsql {SELECT round(sum(c1000)) FROM t1}
55: } {50601000.0}
56:
57: # Create indices with many columns
58: #
59: do_test index2-2.1 {
60: set sql "CREATE INDEX t1i1 ON t1("
61: for {set i 1} {$i<1000} {incr i} {
62: append sql c$i,
63: }
64: append sql c1000)
65: execsql $sql
66: } {}
67: do_test index2-2.2 {
68: ifcapable explain {
69: execsql {EXPLAIN SELECT c9 FROM t1 ORDER BY c1, c2, c3, c4, c5}
70: }
71: execsql {SELECT c9 FROM t1 ORDER BY c1, c2, c3, c4, c5, c6 LIMIT 5}
72: } {9 10009 20009 30009 40009}
73:
74: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>