xref: /sqlite-3.40.0/test/index4.test (revision f9c8ce3c) 
1# 2011 July 9
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
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18set testprefix index4
19
20do_execsql_test 1.1 {
21  BEGIN;
22    CREATE TABLE t1(x);
23    INSERT INTO t1 VALUES(randomblob(102));
24    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --     2
25    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --     4
26    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --     8
27    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --    16
28    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --    32
29    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --    64
30    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --   128
31    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --   256
32    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --   512
33    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --  1024
34    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --  2048
35    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --  4096
36    INSERT INTO t1 SELECT randomblob(102) FROM t1;     --  8192
37    INSERT INTO t1 SELECT randomblob(102) FROM t1;     -- 16384
38    INSERT INTO t1 SELECT randomblob(102) FROM t1;     -- 32768
39    INSERT INTO t1 SELECT randomblob(102) FROM t1;     -- 65536
40  COMMIT;
41}
42
43do_execsql_test 1.2 {
44  CREATE INDEX i1 ON t1(x);
45}
46do_execsql_test 1.3 {
47  PRAGMA integrity_check
48} {ok}
49
50# The same test again - this time with limited memory.
51#
52ifcapable memorymanage {
53  set soft_limit [sqlite3_soft_heap_limit 50000]
54
55  db close
56  sqlite3 db test.db
57
58  do_execsql_test 1.4 {
59    PRAGMA cache_size = 10;
60    CREATE INDEX i2 ON t1(x);
61  }
62  do_execsql_test 1.5 {
63    PRAGMA integrity_check
64  } {ok}
65
66  sqlite3_soft_heap_limit $soft_limit
67}
68
69
70do_execsql_test 1.6 {
71  BEGIN;
72    DROP TABLE t1;
73    CREATE TABLE t1(x);
74    INSERT INTO t1 VALUES('a');
75    INSERT INTO t1 VALUES('b');
76    INSERT INTO t1 VALUES('c');
77    INSERT INTO t1 VALUES('d');
78    INSERT INTO t1 VALUES('e');
79    INSERT INTO t1 VALUES('f');
80    INSERT INTO t1 VALUES('g');
81    INSERT INTO t1 VALUES(NULL);
82    INSERT INTO t1 SELECT randomblob(1202) FROM t1;     --    16
83    INSERT INTO t1 SELECT randomblob(2202) FROM t1;     --    32
84    INSERT INTO t1 SELECT randomblob(3202) FROM t1;     --    64
85    INSERT INTO t1 SELECT randomblob(4202) FROM t1;     --   128
86    INSERT INTO t1 SELECT randomblob(5202) FROM t1;     --   256
87  COMMIT;
88  CREATE INDEX i1 ON t1(x);
89  PRAGMA integrity_check
90} {ok}
91
92do_execsql_test 1.7 {
93  BEGIN;
94    DROP TABLE t1;
95    CREATE TABLE t1(x);
96    INSERT INTO t1 VALUES('a');
97  COMMIT;
98  CREATE INDEX i1 ON t1(x);
99  PRAGMA integrity_check
100} {ok}
101
102do_execsql_test 1.8 {
103  BEGIN;
104    DROP TABLE t1;
105    CREATE TABLE t1(x);
106  COMMIT;
107  CREATE INDEX i1 ON t1(x);
108  PRAGMA integrity_check
109} {ok}
110
111do_execsql_test 2.1 {
112  BEGIN;
113    CREATE TABLE t2(x);
114    INSERT INTO t2 VALUES(14);
115    INSERT INTO t2 VALUES(35);
116    INSERT INTO t2 VALUES(15);
117    INSERT INTO t2 VALUES(35);
118    INSERT INTO t2 VALUES(16);
119  COMMIT;
120}
121do_catchsql_test 2.2 {
122  CREATE UNIQUE INDEX i3 ON t2(x);
123} {1 {UNIQUE constraint failed: t2.x}}
124
125
126finish_test
127