xref: /sqlite-3.40.0/ext/rbu/rbuvacuum4.test (revision 42e84ae1) 
1# 2019 Jan 3
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#
12# This file contains tests for the RBU module. More specifically, it
13# contains tests to ensure that the sqlite3rbu_vacuum() API works as
14# expected.
15#
16
17source [file join [file dirname [info script]] rbu_common.tcl]
18set testprefix rbuvacuum4
19
20set step 1
21
22do_execsql_test 1.0 {
23  CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID;
24  INSERT INTO t1 VALUES(1, 2, 3);
25  INSERT INTO t1 VALUES(4, 5, 6);
26  INSERT INTO t1 VALUES(7, 8, 9);
27}
28do_rbu_vacuum_test 1.1 1
29
30#-------------------------------------------------------------------------
31reset_db
32
33do_execsql_test 2.0 {
34  CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
35  INSERT INTO t1 VALUES(1, 2, 3);
36  INSERT INTO t1 VALUES(4, 5, 6);
37  INSERT INTO t1 VALUES(7, 8, 9);
38}
39do_rbu_vacuum_test 2.1 1
40do_execsql_test 2.2 {
41  SELECT * FROM t1;
42} {1 2 3 4 5 6 7 8 9}
43
44#-------------------------------------------------------------------------
45reset_db
46
47do_execsql_test 3.0 {
48  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
49  CREATE INDEX i1 oN t1(b, c);
50  INSERT INTO t1 VALUES(1, 2, 3);
51  INSERT INTO t1 VALUES(4, 5, 6);
52  INSERT INTO t1 VALUES(7, 8, 9);
53
54  CREATE TABLE t2(a, b, c INTEGER, PRIMARY KEY(c));
55  CREATE INDEX i2 oN t2(b, a);
56  INSERT INTO t2 VALUES('a', 'b', -1);
57  INSERT INTO t2 VALUES('c', 'd', -2);
58  INSERT INTO t2 VALUES('e', 'f', -3);
59}
60
61do_rbu_vacuum_test 3.1 1
62
63do_execsql_test 3.2 {
64  SELECT * FROM t1;
65  SELECT * FROM t2;
66} {1 2 3 4 5 6 7 8 9 e f -3 c d -2 a b -1}
67
68#-------------------------------------------------------------------------
69reset_db
70do_execsql_test 4.0 {
71  CREATE TABLE x1(a, b, c, d, PRIMARY KEY(c, b)) WITHOUT ROWID;
72  INSERT INTO x1 VALUES(1, 1, 1, 1);
73  INSERT INTO x1 VALUES(1, 1, 2, 1);
74  INSERT INTO x1 VALUES(1, 2, 2, 1);
75
76  INSERT INTO x1 VALUES(NULL, 2, 3, NULL);
77  INSERT INTO x1 VALUES(NULL, 2, 4, NULL);
78  INSERT INTO x1 VALUES(NULL, 2, 5, NULL);
79
80  CREATE INDEX x1ad ON x1(d, a);
81  CREATE INDEX x1null ON x1(d, a) WHERE d>15;
82}
83
84do_rbu_vacuum_test 4.1.1 1
85
86do_execsql_test 4.2 {
87  SELECT count(*) fROM x1
88} 6
89
90do_rbu_vacuum_test 4.1.2 0
91
92#-------------------------------------------------------------------------
93reset_db
94do_execsql_test 5.0 {
95  CREATE TABLE "a b c"(a, "b b" PRIMARY KEY, "c c");
96  CREATE INDEX abc1 ON "a b c"(a, "c c");
97
98  INSERT INTO "a b c" VALUES(NULL, 'a', NULL);
99  INSERT INTO "a b c" VALUES(NULL, 'b', NULL);
100  INSERT INTO "a b c" VALUES(NULL, 'c', NULL);
101
102  INSERT INTO "a b c" VALUES(1, 2, 3);
103  INSERT INTO "a b c" VALUES(3, 9, 1);
104  INSERT INTO "a b c" VALUES('aaa', 'bbb', 'ccc');
105
106  CREATE INDEX abc2 ON "a b c"("c c" DESC, a);
107
108  CREATE TABLE x(a);
109  INSERT INTO x VALUES('a'), ('b'), ('d');
110  CREATE UNIQUE INDEX y ON x(a);
111}
112
113do_rbu_vacuum_test 5.1 1
114
115finish_test
116
117