Debian Codegraph @

ext/intck/intck2.test

raw
1 # 2024 Feb 19
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 # The focus of this file is testing the incremental integrity check
13 # (intck) extension.
14 #
15
16 source [file join [file dirname [info script]] intck_common.tcl]
17 set testprefix intck2
18 return_if_no_intck
19
20
21 do_execsql_test 1.0 {
22 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT);
23 INSERT INTO t1 VALUES(1, 'one');
24 INSERT INTO t1 VALUES(2, 'two');
25 INSERT INTO t1 VALUES(3, 'three');
26 CREATE INDEX i1 ON t1(b);
27 }
28
29 proc imposter_edit {obj create sql} {
30 sqlite3 xdb test.db
31 set pgno [xdb one {SELECT rootpage FROM sqlite_schema WHERE name=$obj}]
32 xdb eval {PRAGMA Writable_schema=ON}
33
34 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER xdb main 1 $pgno
35 xdb eval $create
36 sqlite3_test_control SQLITE_TESTCTRL_IMPOSTER xdb main 0 0
37 xdb eval $sql
38 xdb close
39 }
40
41 imposter_edit i1 {
42 CREATE TABLE imp(b, a, PRIMARY KEY(b)) WITHOUT ROWID;
43 } {
44 DELETE FROM imp WHERE b='two';
45 INSERT INTO imp(b, a) VALUES('four', 4);
46 }
47
48 do_intck_test 1.1 {
49 {surplus entry ('four',4) in index i1}
50 {entry ('two',2) missing from index i1}
51 }
52
53 #-------------------------------------------------------------------------
54 reset_db
55 do_execsql_test 2.0 {
56 CREATE TABLE x1(a, b, "c d");
57 CREATE INDEX x1a ON x1(a COLLATE nocase DESC , b ASC);
58 CREATE INDEX x1b ON x1( a || b || ' "''" ' COLLATE binary ASC );
59 CREATE INDEX x1c ON x1( format('%s', a)ASC, format('%d', "c d" ) );
60 INSERT INTO x1 VALUES('one', 2, 3);
61 INSERT INTO x1 VALUES('One', 4, 5);
62 INSERT INTO x1 VALUES('ONE', 6, 7);
63 INSERT INTO x1 VALUES(NULL, NULL, NULL);
64 }
65
66 do_intck_test 2.1 {}
67
68 imposter_edit x1 {
69 CREATE TABLE imp(a, b, c);
70 } {
71 DELETE FROM imp WHERE c=7;
72 }
73 do_intck_test 2.2 {
74 {surplus entry ('ONE',6,3) in index x1a}
75 {surplus entry ('ONE6 "''" ',3) in index x1b}
76 {surplus entry ('ONE','7',3) in index x1c}
77 }
78
79 #-------------------------------------------------------------------------
80 reset_db
81 do_execsql_test 3.0 {
82 CREATE TABLE x1(a, b, c);
83 CREATE INDEX x1all ON x1(a DESC, b ASC, c DESC);
84 INSERT INTO x1 VALUES(2, 1, 2);
85 INSERT INTO x1 VALUES(2, 1, 1);
86 INSERT INTO x1 VALUES(2, 2, 2);
87 INSERT INTO x1 VALUES(2, 2, 1);
88 INSERT INTO x1 VALUES(1, 1, 2);
89 INSERT INTO x1 VALUES(1, 1, 1);
90 INSERT INTO x1 VALUES(1, 2, 2);
91 INSERT INTO x1 VALUES(1, 2, 1);
92 }
93
94 do_intck_test 3.1 {
95 }
96
97 imposter_edit x1 {
98 CREATE TABLE imp(a, b, c);
99 } {
100 DELETE FROM imp WHERE 1;
101 }
102
103 db close
104 sqlite3 db test.db
105
106 do_intck_test 3.2 {
107 {surplus entry (2,1,2,1) in index x1all}
108 {surplus entry (2,1,1,2) in index x1all}
109 {surplus entry (2,2,2,3) in index x1all}
110 {surplus entry (2,2,1,4) in index x1all}
111 {surplus entry (1,1,2,5) in index x1all}
112 {surplus entry (1,1,1,6) in index x1all}
113 {surplus entry (1,2,2,7) in index x1all}
114 {surplus entry (1,2,1,8) in index x1all}
115 }
116
117 do_execsql_test 3.3 {
118 DELETE FROM x1;
119 INSERT INTO x1 VALUES(NULL, NULL, NULL);
120 INSERT INTO x1 VALUES(NULL, NULL, NULL);
121 INSERT INTO x1 VALUES(NULL, NULL, NULL);
122 INSERT INTO x1 VALUES(NULL, NULL, NULL);
123 }
124
125 do_intck_test 3.4 {
126 }
127
128 imposter_edit x1 {
129 CREATE TABLE imp(a, b, c);
130 } {
131 DELETE FROM imp WHERE 1;
132 INSERT INTO imp(rowid) VALUES(-123);
133 INSERT INTO imp(rowid) VALUES(456);
134 }
135
136 db close
137 sqlite3 db test.db
138
139 do_intck_test 3.5 {
140 {entry (NULL,NULL,NULL,-123) missing from index x1all}
141 {entry (NULL,NULL,NULL,456) missing from index x1all}
142 {surplus entry (NULL,NULL,NULL,1) in index x1all}
143 {surplus entry (NULL,NULL,NULL,2) in index x1all}
144 {surplus entry (NULL,NULL,NULL,3) in index x1all}
145 {surplus entry (NULL,NULL,NULL,4) in index x1all}
146 }
147
148 reset_db
149
150 do_execsql_test 3.6 {
151 CREATE TABLE w1(a PRIMARY KEY, b, c);
152 INSERT INTO w1 VALUES(1.0, NULL, NULL);
153 INSERT INTO w1 VALUES(33.0, NULL, NULL);
154 INSERT INTO w1 VALUES(100.0, NULL, NULL);
155 CREATE INDEX w1bc ON w1(b, c);
156 }
157
158 do_intck_test 3.7 {
159 }
160
161 imposter_edit w1 {
162 CREATE TABLE imp(a, b, c);
163 } {
164 DELETE FROM imp WHERE a=33;
165 INSERT INTO imp(a) VALUES(1234.5);
166 INSERT INTO imp(a) VALUES(-1234.5);
167 }
168
169 do_intck_test 3.8 {
170 {surplus entry (33.0,2) in index sqlite_autoindex_w1_1}
171 {entry (1234.5,4) missing from index sqlite_autoindex_w1_1}
172 {entry (NULL,NULL,4) missing from index w1bc}
173 {entry (-1234.5,5) missing from index sqlite_autoindex_w1_1}
174 {entry (NULL,NULL,5) missing from index w1bc}
175 {surplus entry (NULL,NULL,2) in index w1bc}
176 }
177
178 finish_test