/ Check-in [eec7dc91]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to file e_reindex.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eec7dc9192c635335a4a9acc3669afd4b26f02d3
User & Date: dan 2010-09-25 07:51:58
Context
2010-09-25
14:13
Do not call gethostuuid() on MacOS 10.4 and earlier, since it is not supported there. check-in: 44deaaef user: drh tags: trunk
07:51
Add tests to file e_reindex.test. check-in: eec7dc91 user: dan tags: trunk
2010-09-24
19:14
Modify some evidence tags to match updated gifs. check-in: f44de500 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_reindex.test.

34
35
36
37
38
39
40


41


























































































































































































































































42
  3   "REINDEX binary"    {}
  4   "REINDEX t1"        {}
  5   "REINDEX main.t1"   {}
  4   "REINDEX i1"        {}
  5   "REINDEX main.i1"   {}
}






























































































































































































































































finish_test







>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
  3   "REINDEX binary"    {}
  4   "REINDEX t1"        {}
  5   "REINDEX main.t1"   {}
  4   "REINDEX i1"        {}
  5   "REINDEX main.i1"   {}
}

# EVIDENCE-OF: R-52173-44778 The REINDEX command is used to delete and
# recreate indices from scratch.
#
#    Test this by corrupting some database indexes, running REINDEX, and
#    observing that the corruption is gone.
#
do_execsql_test e_reindex-1.1 {
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t1 VALUES(3, 4);
  INSERT INTO t1 VALUES(5, 6);
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql = '-- ' || sql WHERE type = 'index';
} {}

db close
sqlite3 db test.db
do_execsql_test e_reindex-1.2 {
  DELETE FROM t1 WHERE a = 3;
  INSERT INTO t1 VALUES(7, 8);
  INSERT INTO t1 VALUES(9, 10);
  PRAGMA writable_schema = 1;
  UPDATE sqlite_master SET sql = substr(sql, 4) WHERE type = 'index';
} {}

db close
sqlite3 db test.db
do_execsql_test e_reindex-1.3 {
  PRAGMA integrity_check;
} [list \
  {rowid 4 missing from index i2} \
  {rowid 4 missing from index i1} \
  {rowid 5 missing from index i2} \
  {rowid 5 missing from index i1} \
  {wrong # of entries in index i2} \
  {wrong # of entries in index i1}
]

do_execsql_test e_reindex-1.4 {
  REINDEX;
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
# The remaining tests in this file focus on testing that the REINDEX 
# command reindexes the correct subset of the indexes in the database.
# They all use the following dataset.
#
db close
forcedelete test.db2
forcedelete test.db
sqlite3 db test.db

proc sort_by_length {lhs rhs} {
  set res [expr {[string length $lhs] - [string length $rhs]}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}
array set V {one 1 two 2 three 3 four 4 five 5 six 6 seven 7 eight 8}
proc sort_by_value {lhs rhs} {
  global V
  set res [expr {$V($lhs) - $V(rhs)}]
  if {$res!=0} {return $res}
  return [string compare $lhs $rhs]
}

db collate collA sort_by_length
db collate collB sort_by_value

set BY(length) {one six two five four eight seven three}
set BY(value)  {one two three four five six seven eight}

do_execsql_test e_reindex-2.0 {
  ATTACH 'test.db2' AS aux;

  CREATE TABLE t1(x);
  CREATE INDEX i1_a ON t1(x COLLATE collA);
  CREATE INDEX i1_b ON t1(x COLLATE collB);
  INSERT INTO t1 VALUES('one');
  INSERT INTO t1 VALUES('two');
  INSERT INTO t1 VALUES('three');
  INSERT INTO t1 VALUES('four');
  INSERT INTO t1 VALUES('five');
  INSERT INTO t1 VALUES('six');
  INSERT INTO t1 VALUES('seven');
  INSERT INTO t1 VALUES('eight');

  CREATE TABLE t2(x);
  CREATE INDEX i2_a ON t2(x COLLATE collA);
  CREATE INDEX i2_b ON t2(x COLLATE collB);
  INSERT INTO t2 SELECT x FROM t1;

  CREATE TABLE aux.t1(x);
  CREATE INDEX aux.i1_a ON t1(x COLLATE collA);
  CREATE INDEX aux.i1_b ON t1(x COLLATE collB);
  INSERT INTO aux.t1 SELECT x FROM main.t1;

} {}

proc test_index {tn tbl collation expected} {
  set sql "SELECT x FROM $tbl ORDER BY x COLLATE $collation"
  uplevel do_execsql_test e_reindex-2.$tn [list $sql] [list $::BY($expected)]
}

proc set_collations {a b} {
  db collate collA "sort_by_$a"
  db collate collB "sort_by_$b"
}

test_index 1.1   t1     collA   length
test_index 1.2   t1     collB   value
test_index 1.3   t2     collA   length
test_index 1.4   t2     collB   value
test_index 1.5   aux.t1 collA   length
test_index 1.6   aux.t1 collB   value


# EVIDENCE-OF: R-47362-07898 If the REINDEX keyword is not followed by a
# collation-sequence or database object identifier, then all indices in
# all attached databases are rebuilt.
#
set_collations value length
do_execsql_test e_reindex-2.2.1 "REINDEX" {}
test_index 2.2   t1     collA   value
test_index 2.3   t1     collB   length
test_index 2.4   t2     collA   value
test_index 2.5   t2     collB   length
test_index 2.6   aux.t1 collA   value
test_index 2.7   aux.t1 collB   length

# EVIDENCE-OF: R-45878-07697 If the REINDEX keyword is followed by a
# collation-sequence name, then all indices in all attached databases
# that use the named collation sequences are recreated.
#
set_collations length value
do_execsql_test e_reindex-2.3.1 "REINDEX collA" {}
test_index 3.2   t1     collA   length
test_index 3.3   t1     collB   length
test_index 3.4   t2     collA   length
test_index 3.5   t2     collB   length
test_index 3.6   aux.t1 collA   length
test_index 3.7   aux.t1 collB   length
do_execsql_test e_reindex-2.3.8 "REINDEX collB" {}
test_index 3.9   t1     collA   length
test_index 3.10  t1     collB   value
test_index 3.11  t2     collA   length
test_index 3.12  t2     collB   value
test_index 3.13  aux.t1 collA   length
test_index 3.14  aux.t1 collB   value

# EVIDENCE-OF: R-49616-30196 Or, if the argument attached to the REINDEX
# identifies a specific database table, then all indices attached to the
# database table are rebuilt.
#
set_collations value length
do_execsql_test e_reindex-2.4.1 "REINDEX t1" {}
test_index 4.2   t1     collA   value
test_index 4.3   t1     collB   length
test_index 4.4   t2     collA   length
test_index 4.5   t2     collB   value
test_index 4.6   aux.t1 collA   length
test_index 4.7   aux.t1 collB   value
do_execsql_test e_reindex-2.4.8 "REINDEX aux.t1" {}
test_index 4.9   t1     collA   value
test_index 4.10  t1     collB   length
test_index 4.11  t2     collA   length
test_index 4.12  t2     collB   value
test_index 4.13  aux.t1 collA   value
test_index 4.14  aux.t1 collB   length
do_execsql_test e_reindex-2.4.15 "REINDEX t2" {}
test_index 4.16  t1     collA   value
test_index 4.17  t1     collB   length
test_index 4.18  t2     collA   value
test_index 4.19  t2     collB   length
test_index 4.20  aux.t1 collA   value
test_index 4.21  aux.t1 collB   length

# EVIDENCE-OF: R-58823-28748 If it identifies a specific database index,
# then just that index is recreated.
#
set_collations length value
do_execsql_test e_reindex-2.5.1 "REINDEX i1_a" {}
test_index 5.2   t1     collA   length
test_index 5.3   t1     collB   length
test_index 5.4   t2     collA   value
test_index 5.5   t2     collB   length
test_index 5.6   aux.t1 collA   value
test_index 5.7   aux.t1 collB   length
do_execsql_test e_reindex-2.5.8 "REINDEX i2_b" {}
test_index 5.9   t1     collA   length
test_index 5.10  t1     collB   length
test_index 5.11  t2     collA   value
test_index 5.12  t2     collB   value
test_index 5.13  aux.t1 collA   value
test_index 5.14  aux.t1 collB   length
do_execsql_test e_reindex-2.5.15 "REINDEX aux.i1_b" {}
test_index 5.16  t1     collA   length
test_index 5.17  t1     collB   length
test_index 5.18  t2     collA   value
test_index 5.19  t2     collB   value
test_index 5.20  aux.t1 collA   value
test_index 5.21  aux.t1 collB   value
do_execsql_test e_reindex-2.5.22 "REINDEX i1_b" {}
test_index 5.23  t1     collA   length
test_index 5.24  t1     collB   value
test_index 5.25  t2     collA   value
test_index 5.26  t2     collB   value
test_index 5.27  aux.t1 collA   value
test_index 5.28  aux.t1 collB   value
do_execsql_test e_reindex-2.5.29 "REINDEX i2_a" {}
test_index 5.30  t1     collA   length
test_index 5.31  t1     collB   value
test_index 5.32  t2     collA   length
test_index 5.33  t2     collB   value
test_index 5.34  aux.t1 collA   value
test_index 5.35  aux.t1 collB   value
do_execsql_test e_reindex-2.5.36 "REINDEX aux.i1_a" {}
test_index 5.37  t1     collA   length
test_index 5.38  t1     collB   value
test_index 5.39  t2     collA   length
test_index 5.40  t2     collB   value
test_index 5.41  aux.t1 collA   length
test_index 5.42  aux.t1 collB   value

# EVIDENCE-OF: R-15639-02023 If no database-name is specified and there
# exists both a table or index and a collation sequence of the specified
# name, SQLite interprets this as a request to rebuild the indices that
# use the named collation sequence.
#
set_collations value length
do_execsql_test e_reindex-2.6.0 {
  CREATE TABLE collA(x);
  CREATE INDEX icolla_a ON collA(x COLLATE collA);
  CREATE INDEX icolla_b ON collA(x COLLATE collB);

  INSERT INTO collA SELECT x FROM t1;
} {}

test_index 6.1   collA  collA   value
test_index 6.2   collA  collB   length

set_collations length value
do_execsql_test e_reindex-2.6.3 "REINDEX collA" {}
test_index 6.4   collA  collA   length
test_index 6.5   collA  collB   length
do_execsql_test e_reindex-2.6.3 "REINDEX main.collA" {}
test_index 6.4   collA  collA   length
test_index 6.5   collA  collB   value

set_collations value length
do_execsql_test e_reindex-2.6.6 "REINDEX main.collA" {}
test_index 6.7   collA  collA   value
test_index 6.8   collA  collB   length

finish_test